超詳細SQL常用函數(shù)總結

1、超詳細SQL常用函數(shù)總結SQLOracleF#J#Sql總結之Sql-常用函數(shù)一、字符串函數(shù)是oracle使用最廣泛的一種函數(shù)(表是參考sql查詢介紹(二中的表.A、LOWER(參數(shù):把參數(shù)變成小寫例如:查詢名稱為scott的員工信息(不區(qū)分大小寫的查詢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(參數(shù):把參數(shù)變成大寫例如:查詢名稱為scott的員工信息(不區(qū)分大小寫的查詢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(參數(shù):把參數(shù)的首字母大寫例如:查找員工編號是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(參數(shù):返回參數(shù)的長度例如:輸出員工編號是77

4、88的job的長度SQL> select length(job as "7788的job的length" from emp where empno=7788;顯示的結果如下:7788的job的length-7E、CONCAT(參數(shù)1,參數(shù)2:把參數(shù)1和參數(shù)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(參數(shù),開始,截取數(shù)目:截取參數(shù)例如:輸出員工編號是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(參數(shù),字母:返回字母首次出現(xiàn)的位置例如:查找用戶7788的名字中T出現(xiàn)的位置SQL> select empno,ename,instr(ename,'T' as "T首次出現(xiàn)的位置" from emp where empno=7788;顯示的結果如下:EMPNO ENAME T首次出現(xiàn)的位置- - -7788 SCOTT 4H、LPAD(參數(shù),

7、長度,在前補齊參數(shù)字母:向左補齊例如:查找用戶是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(參數(shù),長度,在后補齊參數(shù)字母:向右補齊例如:查找用戶是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 參數(shù):從參數(shù)中去掉指定的字母例如:查找員工編號是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:(參數(shù),參數(shù),參數(shù): 第一個參數(shù)操作數(shù),第二是要查找的字符,第三個是替換的字符,如果沒有第三個就刪除查找的字符。例如:把編號7788的員工姓名中的T換成LSQL> select empno,ename,replace(ename,'T','L' as "替換后" fro

12、m emp where empno=7788;顯示的結果如下:EMPNO ENAME 替換后- - -7788 SCOTT SCOLL二、數(shù)字函數(shù)A、ROUND(參數(shù): 四舍五入例如:查找12.89的四舍五入SQL> select round(12.89 from dual;顯示的結果如下:ROUND(12.89-13B、TRUNC(參數(shù)1,參數(shù)2: 截斷,第一個參數(shù)是要操作的數(shù),第二個參數(shù)可有可無,如果沒有的話,截斷的是整數(shù)部分,如果參數(shù)2是正數(shù)的話,截取小數(shù)點右邊的參數(shù)2個數(shù),如果是負數(shù)的話,截取小數(shù)點左邊的“參數(shù)2”個數(shù)例如:采用默認的方式SQL> select trunc(

13、12.89 from dual;顯示的結果:TRUNC(12.89-12例如:第二個參數(shù)是正數(shù)的時候SQL> select trunc(12.89,1 from dual;顯示的結果:TRUNC(12.89,1-12.8例如:第二個參數(shù)是負數(shù)的時候SQL> select trunc(12.89,-1 from dual;顯示的結果:TRUNC(12.89,-1-10C、MOD(參數(shù)1,參數(shù)2: 求參數(shù)1除以參數(shù)2后的余數(shù)例如:100除以30的余數(shù)SQL> select mod(100,30 from dual;顯示的結果:MOD(100,30-10D、ABS(參數(shù):求參數(shù)的絕

14、對值例如:求-10 的絕對值SQL> select abs(-10 from dual;顯示的結果:ABS(-10-10E、CEIL(參數(shù):返回大于或等于參數(shù)的最小整數(shù)SQL> select ceil(-10.23 from dual;顯示的結果:CEIL(-10.23-10F、FLOOR(參數(shù):返回小于或等于參數(shù)的最大整數(shù)例如:求-10.23的向下取整SQL> select floor(-10.23 from dual;顯示的結果:FLOOR(-10.23-11G、SQRT(參數(shù):返回參數(shù)的平方根負數(shù)無意義。例如:求4的平方根SQL> select sqrt(4 fr

15、om dual;顯示的結果:SQRT(4-2日期函數(shù):Oracle 中的日期型數(shù)據(jù)實際含有兩個值: 日期和時間。默認的日期格式是DD-MON-RR.日期時間函數(shù)用來返回當前系統(tǒng)的日期和時間、以及對日期和時間類型的數(shù)據(jù)進行處理運算。A、sysdate(;獲取系統(tǒng)的當前日期例如: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時區(qū)轉換成other時區(qū)例如: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(參數(shù),截取類型:日期的四舍五入例如:四舍五入當前時間年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(參數(shù),轉換的類型:日期的截取例如:截取當前時間的年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日期的數(shù)學運算:在日期上加上或減去一個數(shù)字結果仍為

22、日期。兩個日期相減返回日期之間相差的天數(shù)。可以用數(shù)字除24來向日期中加上或減去小時通用函數(shù)這些函數(shù)適用于任何數(shù)據(jù)類型,同時也適用于空值: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 相比的優(yōu)點在于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組合函數(shù):分組函數(shù)作用于一組數(shù)據(jù),并對一組數(shù)據(jù)返回一個值。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一個需要發(fā)多少工資SQL> select sum(sal+sum(comm as "總工資" from emp;顯示結果:MIN(NVL(SAL,0-非法使用組

32、函數(shù)A、所用包含于SELECT 列表中,而未包含于組函數(shù)中的列都必須包含于GROUP BY 子句中。例如:SQL> select empno,count(job from emp;正確寫法如下:SQL> select empno,count(job from emp group by empno;B、不能在WHERE 子句中使用組函數(shù)(注意。例如:SQL> select deptno from emp where count(job>0 group by deptno;備注:ORA-00934: 此處不允許使用分組函數(shù)group by 語句如果在查詢的過程中需要按某一列

33、的值進行分組,以統(tǒng)計該組內數(shù)據(jù)的信息時,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。注意:group by子句一定要與分組函數(shù)結合使用,否則沒有意義。求出每個部門的員工的數(shù)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 可以包含聚合函數(shù)。 HAVING 子句可以引用選擇列表中出現(xiàn)的任意項。 備注：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、查詢部門的員工人數(shù)大于五部門編號 SQL> select deptno,count(* from emp group by deptno having count(*>5; 顯示結果： DEPTNO COUNT(* - -30 6 備注：分組函數(shù)可以嵌套 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