Oracle -- 常用函数

不念不忘少年蓝@ 2022-05-17 11:29 661阅读 0赞

一、字符函数

1. lower(char)　upper(char)

  1. lower(char) --将字符串转化为小写的格式
  2. upper(char) --将字符串转化为大写的格式

2. length(char)

  1. length(char) --返回字符串的长度
  2. 例: select * from emp where length(ename) = 5;

3. replace(char, search_string, replace_string)

  1. replace(char, search_string, replace_string)     --替换字符串 
  2.   char -等待替换的字符串  search_string -搜索需要替换的字符串  replace_string -替换字符串
  4. select replace(ename,'.net','Java') from emp;  --显示所有员工的姓名，用"Java"替换所有".net"

4. substr(char, m, n)

  1. substr(char, m, n)    substr(某个列名里的String,开始位置,截取长度)
  2.                 m代表第几个位置(从1开始),n代表取n个字符的意思    m为负数,则表示从右边开始数第几个位置        
  3. 示例：
  4. select substr('hello world', 2) FROM DUAL;          --结果：ello world
  5. select substr('hello world', -2) FROM DUAL;         --结果：ld
  6. select substr('This is a test', 6, 2)               --结果：is
  7. select substr('This is a test', -3, 3)              --结果：est

5. instr( string1, string2, start_position,nth_appearance )
　　string1：源字符串，要在此字符串中查找；
　　string2：要在string1中查找的字符串；
　　start_position：代表从string1的哪个位置开始查找，此参数可选，如果省略则默认为1。字符串索引从1开始，如果此参数为正，从左到右开始检索；如果此参数为负，则从右到左检索；
　　nth_appearance：代表要查找第几次出现的string2，此参数可选，如果省略默认为1，不能为负。

  1. instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）
  3. select instr('syranmo','s') from dual;        -- 返回 1, 位置索引从1开始
  4. select instr('syranmo','ra') from dual;       -- 返回 3 
  5. select instr('syran mo','at',1,2) from dual;  -- 返回 0, 没有找到返回值是0
  7. --由于ra只出现2次,而start_position为3,即结果返回0
  8. select instr('oracle traning', 'ra', 1, 3) from dual;

6. substr 和 instr

  1. substr和instr结合使用来实现截取字符串中 特定字符前后的字符串
  3. --截取"hello,world"字符串中','分隔符之前的字符串 结果：hello
  4. select substr('hello,world', 1, instr('hello,world', ',')-1) from dual;
  6. --截取"hello,world"字符串中','分隔符之后的字符串 结果：world 
  7. select substr('hello,world', instr('hello,world', ',')+1) from dual;
  9. -- 截取"hello,world,HH"字符串中第1次出现的','字符和第2次出现的','字符之间的字符串 结果：world
  10. select substr('hello,world,HH', instr('hello,world,HH', ',',1)+1, instr('hello,world,HH', ',', 2)-1) from dual;

7. Concat 和 ‘||’

  1. --concat只能连接两个字符串，连接多个需要嵌套调用不方便
  2. SQL> select concat('aa','bb') from dual;
  4. --||直接连接多个字符串
  5. SQL> select 'aa'||'bb'||'cc' from dual;

二、数学函数

1. round(n, m) 保留几位小数(四舍五入)
　　如果省掉m，则四舍五入到整数；如果m是正数，则四舍五入到小数点的m位后；如果m是负数，则四舍五入到小数点的m位前。

  1. select round(23.75123) from dual;       --返回24 
  2. select round(23.75123, -1) from dual;   --返回20 
  3. select round(23.75123, 1) from dual;    --返回23.8 
  4. select round(23.75123, 2) from dual;    --返回23.75

2. trunc(n, m) —对数字和日期进行操作

trunc(number):

  • 截取数字(几位小数),无需四舍五入；
  • 如果省掉m，就截去小数部分；
  • 如果m是正数就截取到小数点的m位后；

  • 如果m是负数，则截取到小数点的前m位。

    select trunc(23.75123) from dual; —返回23
    select trunc(23.75123, -1) from dual; —返回20
    select trunc(23.75123, 1) from dual; —返回23.7
    select trunc(23.75123, 2) from dual; —返回23.75

trunc(Date):

  • trunc(sysdate，'yyyy'); --返回当前年的第一天；
  • trunc(sysdate, 'mm');//返回当前月的第一天；
  • trunc(sysdate, 'dd');//返回当前时间的年月日；
  • trunc(sysdate, 'd');//返回当前星期的第一天；

  • trunc(sysdate, 'hh');//返回当前小时。

    select trunc(sysdate) from dual —2018-12-22 今天的日期为2018-12-22
    select trunc(sysdate,’mm’) from dual —2018-12-01 返回当月第一天.
    select trunc(sysdate,’d’) from dual —2018-12-16 (星期天)返回当前星期的第一天

3. 取余、取商、取整、绝对值、与0判断函数

  1. 取余:
  2.     select mod(10,2) from dual;    –0 
  3.       select mod(10,3) from dual;    –1    
  5. 取商:
  6.     select trunc(20/3) from dual;   -6
  8. 取整:
  9.     floor(n) / trunc(n)    无需四舍五入有    
  10.     ceil(n) 四舍五入
  12. 绝对值:
  13.     abs(n) 返回数字n的绝对值
  15. sign
  16.     语法: sign(表达式);
  17.     若表达式>0则返回1,若表达式<0则返回-1,=0返回0

三、转换函数

转换函数用于将数据类型从一种转为另外一种

1. to_char( )函数

  1. 用作日期转换: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
  3.     yyyy：四位数字的年份 2004年 
  4.     mm：两位数字的月份 8 月–>08 
  5.     dd：两位数字的天 30 号–>30 
  6.     hh24： 8点–>20 
  7.     hh12：8点–>08 
  8.     mi、ss–>显示分钟\秒 
  10. 处理数字(常见)
  11.   to_char(number,'格式');
  12.   select to_char(888) from dual;   --'888'
  13.   select to_char(123,'09999')  from dual;   --'00123'
  14.   select to_char(1314.73, '9999.9') from dual;     --'1314.7'
  15.   select to_char(1314.73, '9,999.99') from dual;   --'1,314.73'
  16.   select to_char(1314.73, '$9,999.00') from dual;  --'$1,314.73'

2. to_date( )函数

  1. --将字符串转换成date类型的数据
  2. select to_date('2018-08-13 09:25:30','yyyy-mm-dd hh24:mi:ss') from dual;

3. to_number( )

  1. 将char或varchar2类型的string转换为一个number类型的数值，
  3.     select to_number('123') from dual;              --结果:123
  4.     select to_number('00123') from dual;           --结果:123
  5.     to_number和to_char恰好是两个相反的函数:
  6.     select to_char(123) from dual;     --结果:'123'
  8. 可以用来实现进制转换；16进制转换为10进制：
  9.     select to_number('19f','xxx') from dual;                       --415
  10.     select to_number('f','xx') from dual;                            --15

四、日期和时间函数

在Oracle中，系统提供了许多用于处理日期和时间的函数，通过这些函数可以实现计算需要的特定日期和时间，常用的日期和时间函数如下：

1. sysdate —返回系统当前日期

  1. select sysdate as nowtime from dual;

2. next_day()

  1. --n为1到7的数字，分別对应周日到周六 查找下一个星期几对应的日期
  2. select next_day(sysdate,n) from dual;

3. months_between(d1, d2)
　　返回d1和d2之间的数目，若d1和d2的日期都相同，或者都是该月的最后一天，则返回一个整数，否则返回的结果将包含一个小数

五、聚合函数

1. avg() - 平均值运算

  1. --查询平均成绩大于80的学生姓名和平均成绩 
  2. select A.sname,avg(B.num) from student A,score B,course C
  3.   where 1=1
  4.     and A.sid = B.student_id
  5.     and B.course_id =C.cid
  6.     and C.cname in('生物','物理','体育','美术')
  7.   group by A.sname
  8.   having avg(B.num) > 80
  10. 用avg函数进行平均运算时会忽略空值(即原始数据中如果存在空值,最终计算的平均值不对)
  11. avg(nvl(comm,0))或者sum(comm)/count(*)

2. sum() - 某列求和

  1. --求每日的销量总和和每日的销量均值
  2. select to_char(sysdate,'yyyy/mm/dd'),sum(销量),avg(销量) from table group by 日期;

3. count() - 统计结果集的行数

  1. --count(*),count(1),count(列名)效率其实没多大区别，区别在于统计null 
  2. count(*)/count(1)将返回表格中所有存在的行的总数包括值为null的行
  3. count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）

4. max() / min()

  1. -- 查询当天最大的价格
  2. select nowday,max(price) from goods group by nowday;
  4. -- 查询每个人员最小离开时间和最大离开时间
  5. select personid,max(leavetime),min(leavetime) from person_leave group by personid;

六、其他函数

1. decode函数

使用decode判断字符串是否一样，decide()函数，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。

  1. decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
  2. decode (expression, search_1, result_1, default)
  3. decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
  4. 比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；default可以不定义，则返回空值。
  6. select id,name,decode(lever,1,'教授',2,'副教授',3,'导师','讲师') position from some_tables where t.lever in (1,2,3);
  8. 逻辑如下:
  9.     if(lever==1) then return '教授'(翻译值1)
  10.     else if(lever==2) then return '副教授'(翻译值2)
  11.     else if(lever==3) then return '导师'
  12.     else return '讲师'(默认值)
  14. 使用decode实现表或试图的行转列:
  15. select name,
  16.     sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
  17.     sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
  18.     sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
  19. from student_score
  20. group by name;

2. case when then else end

  1. case具有两种格式，简单case函数和case搜索函数。
  3. --简单case函数,使用表达式确定返回值.
  4. 　　case search_expression                       --case sex
  5. 　　when expression1 then result1                --when '1' then '男'
  6. 　　when expression2 then result2                --when '2' then '女' 
  7. 　　...
  8. 　　else default_result                          --else '其他' end
  9. 　　end
  11.  --case搜索函数,使用条件确定返回值.
  12. 　　case
  13. 　　when condition1 then result1                --case when sex = '1' then '男'
  14. 　　when condistion2 then result2               --when sex = '2' then '女'
  15. 　　...
  16. 　　else default_result                         --else '其他' end
  17. 　　end
  19. 这两种方式都能实现相同的功能,简单case函数的写法相对比较简洁,但是和case索函数相比功能方面会有些限制

3. nvl() 和 nvl2()

  • nvl(value1,value2)：如果value1的值为null，那么函数返回value2，如果value1不为null，函数返回value1的值；
  • nvl2(value1,value2,value3)：如果value1的值为null，函数返回value3，否则函数返回value2。

4. union / union all

  1. union/union all 都要求两个sql查询列要相同
  3. union: 对两个结果集进行并集操作(不包括重复行),同时进行默认规则的排序；
  4. union All: 对两个结果集进行并集操作(包括重复行),不进行排序
  6. 从效率上说,union all要比union快很多,如果可以确认合并的两个结果集中不包含重复的数据,尽量使用union all,因为union需要进行排序去除重复记录,效率低

5. + 号 / left join… on…

  1. + 左右外连接,哪边有 + 号哪边就可以为空
  2. select * from a,b where a.id=b.id(+);    --(+)写在where后面,不能与or/in连用,b表是附属表
  4. left join on:
  5. select * from a left join b on a.id=b.id;    --左连接,写在 from 与where之间
  7. 效率上没区别,不过left join可读性高、功能更全面、通用性强、而且是新标准,建议使用left join

6. group by / order by

  1. group by 分组,用来统计某些字段的信息
  2. order by 排序,根据升降序显示结果集 asc升序   desc降序 
  4. 当group by和order by 一起使用时,先执行group by 在执行 order by
  6. group by 是分组,首先用group by的前提是你的select里边用了聚合函数如sum(),avg(),min(),max(),没有聚合函数用不了group by不然会报错;
  7. 其次出现在select里边的除聚合函数以外的所有字段,都必须作为group by的条件,也就是说select中的字段必须出现在group by中,group by中的分组条件不一定出现在select中

Remark：

  • 参考case when then 的两种写法
  • Oracle中保留两位小数

发表评论

表情：
评论列表 （有 0 条评论，661人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Oracle常用函数

    Oracle函数 Oracle SQL提供了用于执行特定操作的专用函数，这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracl

    男娘i男娘i/ 0 赞/ 195 阅读