SQL单⾏函数和多⾏函数
单⾏函数和多⾏函数⽰意图：
单⾏函数分为五种类型：字符函数、数值函数、⽇期函数、转换函数、通⽤函数
单⾏函数：
[sql]
1. --⼤⼩写控制函数
2. select lower('Hello World') 转⼩写, upper('Hello World') 转⼤写 from dual;
3. --initcap: ⾸字母⼤写
4. select initcap('hello world') ⾸字符⼤写 from dual;
5.
6. --字符控制函数
7. -- concat: 字符连接函数, 等同于 ||
8. select concat('Hello',' World') from dual;
9. --substr：求母串中的某个⼦串
10. select substr('Hello World',3) from dual;
11. select substr('Hello World',3,4) from dual;
12. --length和lengthb: 字符数和字节数
13. select length('China') 字符数, lengthb('China') 字节数 from dual;
14. --instr：在母串中，查⼦串的位置
15. select instr('Hello World','ll') from dual;
16. --lpad,rpad: 左右填充，将abcd⽤*填充到10位
17. select lpad('abcd',10,'*') 左填充, rpad('abcd',10,'*') 右填充 from dual;
18. --trim: 去掉字符串前后指定的字符
19. select trim('H' from 'Hello WorldH') from dual;
20. --replace：字符串替换函数
21. select replace('Hello Wordl','l','*') from dual;
22.
23. --数字函数
24. select round(45.926,2) 四舍五⼊, trunc(45.926,2) 截断 ,mod(1600,300) 求于 from dual;
25. --ROUND函数
26. select round(45.923,0) 整数位, round(45.923,-1) ⼗位,round(45.923,-2) 百位 from dual;
27.
28. --⽇期函数
c++trunc函数29. --显⽰当前⽇期
30. select sysdate from dual;
31. --显⽰时间部分
32. select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
33. --显⽰昨天，今天和明天，加减数字仍未⽇期
34. select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;
35. --两个⽇期相减，结果为相差的天数，查询员⼯信息，显⽰员⼯⼯龄。两个⽇期不能相加
36. select empno,ename, sysdate-hiredate 天 from emp;
37. --查询员⼯信息，显⽰员⼯⼯龄，分别按照天，星期，⽉显⽰
38. select empno,ename,sysdate-hiredate 天,(sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 ⽉ from emp;
39. --months_between：两个⽇期相差的⽉数
40. select (sysdate-hiredate)/30 ⽅式⼀, months_between(sysdate,hiredate) ⽅式⼆ from emp;
41. --add_months：在指定⽇期上加上若⼲个⽉
42. select add_months(sysdate,1) 下个⽉, add_months(sysdate,123) "123个⽉后" from dual
43. --last_day: 某个⽇期当⽉的最后⼀天
44. select last_day(sysdate) from dual;
45. --next_day：下周六
46. select next_day(sysdate,'星期五') from dual;
47. --对⽇期进⾏四舍五⼊
48. select round(sysdate,'MONTH') ⽉,round(sysdate,'YEAR') from dual;
49. --对⽇期进⾏截断
50. select trunc(sysdate,'MONTH') ⽉,trunc(sysdate,'YEAR') from dual;
51. --⽇期格式
52. select * from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');
53. -- 查询当前⽇期：显⽰: 2011-09-17 15:12:15今天是星期六
54. select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
55. --查询员⼯信息，显⽰员⼯的编号，姓名，⽉薪，要求有货币代码(L)，千位符(,)，⼩数点(.),
56. select empno,ename,to_char(sal,'L9,999.99') from emp;
[sql]
1. --通⽤函数
2. --nvl(exp1,exp2)：当exp1为空时，返回exp2
3. --nvl2(exp1,exp2,exp3)：当exp1为空时，返回exp3；否则返回exp2
4. select ename,sal*12+nvl2(comm,comm,0) 年收⼊ from emp;
5. --NULLIF (expr1, expr2)，如果expr1=expr2，返回null；否则，返回expr1
6. select nullif('abc','abc') from dual;
7. select nullif('abc','abcaa') from dual;
8. --COALESCE ：到参数列表中，第⼀个不为空的值
9. select ename,comm,sal,COALESCE(comm,sal) from emp;
10. --给员⼯涨⼯资，根据职位涨，总裁涨1000，经理涨600 其他⼈员涨400
11. select ename,job,sal 涨前⼯资, case job when 'PRESIDENT' then sal+1000
12. when 'MANAGER' then sal+600
13. else sal+400
14. end 涨后⼯资
15. from emp;
16.
17. select ename,job,sal 涨前⼯资, decode(job,'PRESIDENT',sal+1000,
18. 'MANAGER',sal+600,
19. sal+400) 涨后⼯资
20. from emp;
(五).转换函数(Conversion Functions) 转换函数将指定字符从⼀种类型转换为另⼀种，通常这类函数遵循如下惯例：函数名称后⾯跟着待转换类型以及输出类型。
1、TO_CHAR() 本函数⼜可以分三⼩类，分别是 转换字符->字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型；例如：SELECT TO_CHAR('AABBCC') FROM DUAL;
转换时间->字符TO_CHAR(d[,fmt])：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
转换数值->字符TO_CHAR(n[,fmt])：将指定数值n按照指定格式fmt转换为varchar2类型并返回；例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为⽇期类型，如果fmt参数不为空，则按照fmt中指定格式进⾏转换。注意这⾥的fmt参数。如果ftm为'J'则表⽰按照公元制(Julian day)转换，c则必须为⼤于0并⼩于5373484的正整数。例如： SELECT TO_DATE(2454336, 'J') FROM DUAL; SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
为什么公元制的话，c的值必须不⼤于5373484呢？因为Oracle的DATE类型的取值范围是公元前4712年1⽉1⽇⾄公元9999年12⽉31⽇。看看下⾯这个语句： SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;
3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成⼀个增强型的if else，只不过它并不通过多⾏语句，⽽是在⼀个函数内实现if else的功能。 exp做为初始参数。s做为对⽐值，相同则返回r，如果s有多
个，则持续遍历所有s，直到某个条件为真为⽌，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前⾯的对⽐也都没有为真，则返回空。毫⽆疑问，decode是个⾮常重要的函数，在实现⾏转列等功能时都会⽤到，需要牢记和熟练使⽤。
例如：select decode('a2','a1','true1','a2','true2','default') from dual;
2、GREATEST(n1,n2,...n) 返回序列中的最⼤值例如：SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;
3、LEAST() 返回序列中的最⼩值例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;
4、NULLIF(c1,c2) Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END 例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;
5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会⾃动将c2转换为c1的类型。例如：SELECT NVL(null, '12') FROM DUAL;
6、NVL2(c1,c2,c3) ⼤家可能都⽤到nvl，但你⽤过nvl2吗？如果c1⾮空则返回c2，如果c1为空则返回c3 例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;
7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应⽤于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by ⼦句共同使⽤。
多⾏函数
和单⾏函数相⽐，oracle提供了丰富的基于组的，多⾏的函数。这些函数能在select或select的having⼦句中使⽤，当⽤于select⼦串时常常都和GROUP BY⼀起使⽤。多⾏函数分为接收多个输⼊，返回⼀个输出。
组函数：
[sql]
1. --求员⼯的⼯资总和
2. select sum(sal) from emp;
3. --求个数
4. select count(*) from emp;
5. --求平均⼯资
6. select sum(sal)/count(*) ⽅式⼀, avg(sal) ⽅式⼆ from emp;
7. --关于空值：组函数会⾃动滤空
8. select count(*), count(comm) from emp;
9. --max和min：求最⾼⼯资和最低⼯资
10. select max(sal) 最⾼⼯资,min(sal) 最低⼯资 from emp;
11. --分组数据：求各个部门的平均⼯资
12. select deptno,avg(sal) from emp group by deptno;
13. --group by作⽤于多列：按部门，不同的⼯种，统计平均⼯资
14. --group by作⽤于多列：先按照第⼀列分组；如果相同，再按照第⼆列分组
15. select deptno,job,avg(sal) from emp group by deptno,job;
16. --：求部门的平均⼯资⼤于2000的部门
17. select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
18. --group by的增强
19. select deptno,job,sum(sal) from emp group by rollup(deptno,job);
20. --不同的deptno空两⾏/取消设置
21. break on deptno skip 2/break on null
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论