超详细SQL常用函数总结

1、超详细SQL常用函数总结SQLOracleF#J#Sql总结之Sql-常用函数一、字符串函数是oracle使用最广泛的一种函数(表是参考sql查询介绍(二中的表.A、LOWER(参数:把参数变成小写例如:查询名称为scott的员工信息(不区分大小写的查询SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where lower(ename='scott'输出的结果是:EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7788

2、SCOTT ANALYST 7566 1982-12-9 3000.00 20B、UPPER(参数:把参数变成大写例如:查询名称为scott的员工信息(不区分大小写的查询SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where upper(ename='scott'输出的结果是:EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20C、I

3、NITCAP(参数:把参数的首字母大写例如:查找员工编号是7788的信息,他的名字显示成首字母大写SQL> select empno,initcap(ename,job,mgr,hiredate,sal,comm,deptno from emp where empno='7788'显示结果是:EMPNO INITCAP(ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7788 Scott ANALYST 7566 1982-12-9 3000.00 20D、LENGTH(参数:返回参数的长度例如:输出员工编号是77

4、88的job的长度SQL> select length(job as "7788的job的length" from emp where empno=7788;显示的结果如下:7788的job的length-7E、CONCAT(参数1,参数2:把参数1和参数2连接起来例如:把员工7788的工资显示成:薪水+奖金SQL> select empno,ename,job,mgr,hiredate,concat(sal,comm as "工资",deptno from emp where empno='7788'显示的结果如下:EMPN

5、O ENAME JOB MGR HIREDATE 工资DEPTNO- - - - - - -7788 SCOTT ANALYST 7566 1982-12-9 3000 20F、SUBSTR(参数,开始,截取数目:截取参数例如:输出员工编号是7788的job的后三位SQL> select empno,ename,job,substr(job,length(job-2,3 as "job的后三位" from emp where empno=7788; 或:select empno,ename,job,substr(job,length(job-2 as "jo

6、b的后三位" from emp where empno=7788;显示的结果如下:EMPNO ENAME JOB job的后三位- - - -7788 SCOTT ANALYST YSTG、INSTR(参数,字母:返回字母首次出现的位置例如:查找用户7788的名字中T出现的位置SQL> select empno,ename,instr(ename,'T' as "T首次出现的位置" from emp where empno=7788;显示的结果如下:EMPNO ENAME T首次出现的位置- - -7788 SCOTT 4H、LPAD(参数,

7、长度,在前补齐参数字母:向左补齐例如:查找用户是7788的薪水,如果不够4位的话,前面补0SQL> select empno,ename,sal,lpad(sal,6,'0' as "薪水是6位的格式" from emp where empno=7788;显示的结果如下:EMPNO ENAME SAL 薪水是6位的格式- - - -7788 SCOTT 3000.00 003000I、RPAD(参数,长度,在后补齐参数字母:向右补齐例如:查找用户是7788的薪水,如果不够4位的话,前面补0SQL> select empno,ename,sal,r

8、pad(sal,6,'0' as "薪水是6位的格式" from emp where empno=7788;显示的结果如下:EMPNO ENAME SAL 薪水是6位的格式- - - -7788 SCOTT 3000.00 300000J、TRIM(both|leadingtrailing|字母from 参数:从参数中去掉指定的字母例如:查找员工编号是7788的去掉“T”后的员工姓名SQL> select empno,ename,trim('T' from ename as "去掉T后" from emp where

9、 empno=7788; 或SQL> select empno,ename,trim(both 'T' from ename as "去掉T后" from emp where empno=7788;显示的结果如下:EMPNO ENAME 去掉T后- - -7788 SCOTT SCO例如:查找员工编号是7788的去掉左边的“T”后的员工姓名SQL> select empno,ename,trim(leading 'T' from ename as "去掉T后" from emp where empno=7788

10、;或:SQL> select empno,ename,ltrim(ename,'T' as "去掉T后" from emp where empno=7788;显示的结果如下:EMPNO ENAME 去掉T后- - -7788 SCOTT SCOTT例如:查找员工编号是7788的去掉右边的“T”后的员工姓名SQL> select empno,ename,trim(trailing 'T' from ename as "去掉T后" from emp where empno=7788;或:SQL> select

11、 empno,ename,rtrim(ename,'T' as "去掉T后" from emp where empno=7788;显示的结果如下:EMPNO ENAME 去掉T后- - -7788 SCOTT SCOK、REPLACE:(参数,参数,参数: 第一个参数操作数,第二是要查找的字符,第三个是替换的字符,如果没有第三个就删除查找的字符。例如:把编号7788的员工姓名中的T换成LSQL> select empno,ename,replace(ename,'T','L' as "替换后" fro

12、m emp where empno=7788;显示的结果如下:EMPNO ENAME 替换后- - -7788 SCOTT SCOLL二、数字函数A、ROUND(参数: 四舍五入例如:查找12.89的四舍五入SQL> select round(12.89 from dual;显示的结果如下:ROUND(12.89-13B、TRUNC(参数1,参数2: 截断,第一个参数是要操作的数,第二个参数可有可无,如果没有的话,截断的是整数部分,如果参数2是正数的话,截取小数点右边的参数2个数,如果是负数的话,截取小数点左边的“参数2”个数例如:采用默认的方式SQL> select trunc(

13、12.89 from dual;显示的结果:TRUNC(12.89-12例如:第二个参数是正数的时候SQL> select trunc(12.89,1 from dual;显示的结果:TRUNC(12.89,1-12.8例如:第二个参数是负数的时候SQL> select trunc(12.89,-1 from dual;显示的结果:TRUNC(12.89,-1-10C、MOD(参数1,参数2: 求参数1除以参数2后的余数例如:100除以30的余数SQL> select mod(100,30 from dual;显示的结果:MOD(100,30-10D、ABS(参数:求参数的绝

14、对值例如:求-10 的绝对值SQL> select abs(-10 from dual;显示的结果:ABS(-10-10E、CEIL(参数:返回大于或等于参数的最小整数SQL> select ceil(-10.23 from dual;显示的结果:CEIL(-10.23-10F、FLOOR(参数:返回小于或等于参数的最大整数例如:求-10.23的向下取整SQL> select floor(-10.23 from dual;显示的结果:FLOOR(-10.23-11G、SQRT(参数:返回参数的平方根负数无意义。例如:求4的平方根SQL> select sqrt(4 fr

15、om dual;显示的结果:SQRT(4-2日期函数:Oracle 中的日期型数据实际含有两个值: 日期和时间。默认的日期格式是DD-MON-RR.日期时间函数用来返回当前系统的日期和时间、以及对日期和时间类型的数据进行处理运算。A、sysdate(;获取系统的当前日期例如:SQL> select sysdate from dual;显示的结果是:SYSDATE-2011-4-8 13B、current_timestamp(;获取当前的时间和日期值例如:SQL> select current_timestamp from dual;显示的结果是:CURRENT_TIMESTAMP-

16、C、add_months(date,count:在指定的日期上增加count个月例如:输出当前时间的加上3个月的后的时间SQL> select add_months(sysdate,3 from dual;显示结果:ADD_MONTHS(SYSDATE,3-2011-7-8 13:43:06D、last_day(date;返回日期date所在月的最后一天例如:SQL> select sysdate,last_day(sysdate from dual;显示结果:SYSDATE LAST_DAY(SYSDATE- -2011-4-8 13 2011-4-30 13:51:2E、mon

17、ths_between(date1,dates;返回date1到date2之间间隔多少个月例如:SQL> select sysdate,months_between(sysdate,add_months(sysdate,3 from dual;显示结果:SYSDATE MONTHS_BETWEEN(SYSDATE,ADD_MON- -2011-4-8 13 -3F、new_time(date,'this','other'将时间date从this时区转换成other时区例如:SQL> select sysdate,new_time(sysdate,&#

18、39;GMT','AST' from dual;显示结果:SYSDATE NEW_TIME(SYSDATE,'GMT','AST'- -2011-4-8 13 2011-4-8 9:G、next_day(day,'week'返回指定日期或最后一的第一个星期几的日期,这里day为星期几例如:SQL> select sysdate,next_day(sysdate,'星期五' from dual;显示结果:SYSDATE NEXT_DAY(SYSDATE,'星期五'- -2011-4-8

19、13 2011-4-15 13:53:10H、round(参数,截取类型:日期的四舍五入例如:四舍五入当前时间年SQL> select sysdate,round(sysdate,'yyyy' from dual;显示结果:SYSDATE ROUND(SYSDATE,'YYYY'- -2011-4-9 22 2011-1-1例如:四舍五入当前时间月SQL> select sysdate,round(sysdate,'mm' from dual;显示结果:SYSDATE ROUND(SYSDATE,'MM'- -2011

20、-4-9 22 2011-4-1例如:四舍五入当前时间日SQL> select sysdate,round(sysdate,'dd' from dual;显示的结果:SYSDATE ROUND(SYSDATE,'DD'- -2011-4-9 22 2011-4-10I、to_char(参数,转换的类型:日期的截取例如:截取当前时间的年SQL> select sysdate,to_char(sysdate,'yyyy' from dual;显示结果:SYSDATE TO_CHAR(SYSDATE,'YYYY'- -201

21、1-4-9 22 2011例如:截取当前时间的月SQL> select sysdate,to_char(sysdate,'mm' from dual;显示结果:SYSDATE TO_CHAR(SYSDATE,'MM'- -2011-4-9 22 04例如:截取当前时间的日SQL> select sysdate,to_char(sysdate,'dd' from dual;显示结果:SYSDATE TO_CHAR(SYSDATE,'DD'- -2011-4-9 22 09日期的数学运算:在日期上加上或减去一个数字结果仍为

22、日期。两个日期相减返回日期之间相差的天数。可以用数字除24来向日期中加上或减去小时通用函数这些函数适用于任何数据类型,同时也适用于空值:A、NVL (expr1, expr2:如果expr1为空的话,显示expr2例如:查看emp如奖金comm为空的话,替换成0SQL> select empno,ename,job,mgr,hiredate,sal,nvl(comm,0,deptno from emp; 显示结果:EMPNO ENAME JOB MGR HIREDATE SAL NVL(COMM,0 DEPTNO - - - - - - - -7369 SMITH CLERK 7902

23、1980-12-17 800.00 0 207499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300 307521 WARD SALESMAN 7698 1981-2-22 1250.00 500 307566 JONES MANAGER 7839 1981-4-2 2975.00 0 207654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400 307698 BLAKE MANAGER 7839 1981-5-1 2850.00 0 307782 CLARK MANAGER 7839 1981-6-9 2450.00

24、 0 107788 SCOTT ANALYST 7566 1982-12-9 3000.00 0 207839 KING PRESIDENT 1981-11-17 5000.00 0 107844 TURNER SALESMAN 7698 1981-9-8 1500.00 0 307876 ADAMS CLERK 7788 1983-1-12 1100.00 0 207900 JAMES CLERK 7698 1981-12-3 950.00 0 307902 FORD ANALYST 7566 1981-12-3 3000.00 0 207934 MILLER CLERK 7782 1982

25、-1-23 1300.00 0 1014 rows selectedB、NVL2 (expr1, expr2, expr3:如果expr1为不空的话,显示expr2,如果expr1为空的话,显示expr3例如:查找员工的编号是7521的员工的工资,如果奖金(comm不为空的话,显示工资加奖金SQL> select sal,nvl2(comm,sal+comm,sal from emp where empno=7521;显示结果:SAL NVL2(COMM,SAL+COMM,SAL- -1250.00 1750C、NULLIF (expr1, expr2:expr1与expr2相等返回NU

26、LL,不等返回expr1例如:查看员工7788的薪水和奖金,如果薪水和奖金相等的话返回null,否则返回工资SQL> select sal,comm,nullif(sal,comm from emp where empno=7788;显示结果:SAL COMM NULLIF(SAL,COMM- - -3000.00 3000D、COALESCE (expr1, expr2, ., exprn:如果第一个表达式为空,则返回下一个表达式。COALESCE 与NVL 相比的优点在于COALESCE 可以同时处理交替的多个值例如:在emp表中测试的为了测试,向emp表中添加一条记录:SQL>

27、; insert into emp(empno,ename,deptno values(7978,'guo',10;测试语句:SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno,coalesce(comm,sal,1000 as "测试结果" from emp;显示结果:EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 测试结果- - - - - - - - -7369 SMITH CLERK 7902 1980-12-17 800.00 20 800

28、7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 3007521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 5007566 JONES MANAGER 7839 1981-4-2 2975.00 20 29757654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 1400 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 2850 7782 CLARK MANAGER 7839 1981-6-9

29、 2450.00 10 2450 7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 30007839 KING PRESIDENT 1981-11-17 5000.00 10 50007844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 0 7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 11007900 JAMES CLERK 7698 1981-12-3 950.00 30 9507902 FORD ANALYST 7566 1981-12-3 3000.00 20

30、 30007934 MILLER CLERK 7782 1982-1-23 1300.00 10 13007978 guo 10 100015 rows selected组合函数:分组函数作用于一组数据,并对一组数据返回一个值。A、AVG(字段名:求该字段的平均值例如:求出emp表中工资的平均值SQL> select avg(nvl(sal,0 from emp;显示结果:AVG(NVL(SAL,0-1935B、COUNT(字段名:求该字段中的总记录例如:查询emp表中有几条记录SQL> select count(* from emp;显示结果:COUNT(*-15C、MAX(字段

31、名:求该字段的最大值例如:求出emp表中的最高的工资的员工SQL> select max(nvl(sal,0 from emp;显示结果:MAX(NVL(SAL,0-5000D、MIN(字段名:求该字段的最小值例如:求出emp表中的最低的工资的员工SQL> select min(nvl(sal,0 from emp;显示结果:MIN(NVL(SAL,0-E、SUM(字段名:求该字段的和例如:求emp一个需要发多少工资SQL> select sum(sal+sum(comm as "总工资" from emp;显示结果:MIN(NVL(SAL,0-非法使用组

32、函数A、所用包含于SELECT 列表中,而未包含于组函数中的列都必须包含于GROUP BY 子句中。例如:SQL> select empno,count(job from emp;正确写法如下:SQL> select empno,count(job from emp group by empno;B、不能在WHERE 子句中使用组函数(注意。例如:SQL> select deptno from emp where count(job>0 group by deptno;备注:ORA-00934: 此处不允许使用分组函数group by 语句如果在查询的过程中需要按某一列

33、的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。注意:group by子句一定要与分组函数结合使用,否则没有意义。求出每个部门的员工的数SQL> select count(* from emp group by deptno;显示的结果:COUNT(*-654求每个部门的中员工的平均工资SQL> select avg(nvl(sal,0 from emp group by deptno;显示的结果:AVG(NVL(SAL,0-1566.666666666621752187.5Having子

34、句HAVING 子句对GROUP BY 子句设置条件的方式与WHERE 子句和SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组 操作之后应用。 HAVING 语法与 WHERE 语法类似， 但 HAVING 可以包含聚合函数。 HAVING 子句可以引用选择列表中出现的任意项。 备注：having 子句通常与 group by 子句结合使用 语法： SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAV

35、ING group_condition ORDER BY column; A、查询部门的员工人数大于五部门编号 SQL> select deptno,count(* from emp group by deptno having count(*>5; 显示结果： DEPTNO COUNT(* - -30 6 备注：分组函数可以嵌套 Order by 语句 ORDER BY 子句在 SELECT 语句的结尾。使用 ORDER BY 子句排序：ASC（ascend）: 升序； DESC（descend）: 降序。默认的是 ASC 升序 查询员工信息按照部门的编号进行升序排列 SQL&g

36、t; select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by empno; 或： SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by empnoasc; 显示的结果： EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO - - - - - - - -7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN

37、 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 198

38、2-12-9 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 7900 JAMES 7902 FORD 7934 MILLER 7978 guo CLERK 7698 1981-12-3 ANALYST 7566 1981-12-3 CLERK 7782 1982-1-23 950.00 3000.00 1300.00 10 30 20 10 15 r

39、ows selected 查询员工信息按照部门的编号进行降序排列 SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by empnodesc; 显示的结果： EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO - - - - - - - -7978 guo 10 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 7844 TURNER SALESMAN 7698 1981-9-8