《復(fù)雜SQL語(yǔ)句》PPT課件.ppt

1、復(fù)雜SQL語(yǔ)句,1,2,3,4,較復(fù)雜查詢,多表查詢,分組及函數(shù),子查詢,目 錄,使用選擇限定記錄,在上面的例子中，假定你想要顯示部門(mén)10的所有員工，這種方式是基于WHERE子句的SQL命令。,使用where子句限定返回的記錄,WHERE子句在FROM子句后面 Condition:由列名表達(dá)式，常量和比較操作符組成。,SELECT DISTINCT * , column alias , FROM table WHERE condition(s);,使用WHERE語(yǔ)句,上面例子中返回job=CLERK的所有員工的name , job和deptno 注意：字符的大小寫(xiě)是敏感的。,SQL SELEC

2、T ename, job , deptno FROM emp WHERE job =CLERK; ENAME JOB DEPTNO - - - KING CLERK 30 BLAKE CLERK 20 TURNER CLERK 10 14 rows selected。,字符串和日期,在WHERE子句中的字符串和日期必須用單引號(hào)括起來(lái)，所有的字符是大小寫(xiě)敏感的。 Oracle存儲(chǔ)日期是以內(nèi)定的格式存放，它們代表世紀(jì)、年、月、日、小時(shí)、分鐘和秒，缺省顯示的日期格式是DD-MON-YY，也可能是其他格式。 為了避免日期字段查詢條件不同格式下可能造成的錯(cuò)誤，通常用TO_DATE函數(shù)來(lái)進(jìn)行轉(zhuǎn)換。,日期類(lèi)

3、型查詢條件舉例,上面第二個(gè)例子使用了TO_DATE函數(shù)，就不再受日期格式的影響了。否則同樣的查詢，同樣的表數(shù)據(jù)，不同的系統(tǒng)日期格式會(huì)導(dǎo)致結(jié)果不正確。,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=03-DEC-81; ENAME HIREDATE - - JAMES 03-DEC-81 FORD 03-DEC-81 SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19811203,YYYYMMDD); ENAME HIREDATE - - JAMES 03-DEC-81 F

4、ORD 03-DEC-81,常用比較運(yùn)算符,使用BETWEEN,SQL SELECT ename , sal FROM emp WHERE sal BETWEEN 1000 AND 1500; ENAME SAL - - KING 1250 BLAKE 1500 CLERK 1250 JONES 1300,注意：BETWEEN后面要先寫(xiě)低值，后寫(xiě)高值,使用IN運(yùn)算符,SQL SELECT empno, ename , sal, mgr FROM emp WHERE mgr IN(7902, 7566, 7788); EMPNO ENAME SAL MGR - - 7902 KING 1250

5、7566 7369 BLAKE 1500 7902 7788 CLARK 1250 7566 7876 JONES 1300 7788,使用LIKE運(yùn)算符,使用LIKE運(yùn)算符執(zhí)行通配查詢 查詢條件可包含文字字符或數(shù)字 %可表示零或多個(gè)字符 _可表示一個(gè)字符,SQL SELECT ename FROM emp WHERE ename LIKE S%; ENAME - SMITH SCOTT,用LIKE和ESCAPE來(lái)查找包含特殊字符的數(shù)據(jù),例如如果想查找表EMP中ENAME包含下劃線_的數(shù)據(jù)，就需要用到ESCAPE選項(xiàng)，否則查詢結(jié)果不準(zhǔn)確。 ESCAPE后面單引號(hào)內(nèi)只能有一個(gè)字符，表示前面的LI

6、KE條件中這個(gè)字符后面的第一個(gè)字符當(dāng)作普通字符處理,SQL SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE %_% ESCAPE ; EMPNO ENAME - - 9999 FOR_TEST,使用IS NULL,查詢包含空值的記錄,SQL SELECT ename , mgr FROM emp WHERE mgr IS NULL; ENAME MGR KING,邏輯運(yùn)算符,優(yōu)先級(jí)次序： 1 所有的比較運(yùn)算 2 NOT 3 AND 4 OR 括號(hào)將跨越所有優(yōu)先級(jí),使用AND運(yùn)算符,AND需要條件都滿足,SQL SELECT empno,ename,jo

7、b,sal FROM emp WHERE sal= 1100 AND job=CLERK； EMPNO ENAME JOB SAL - - 7369 BLAKE CLERK 1300 7788 CLARK CLERK 1250,使用OR運(yùn)算符,OR需要滿足條件之一即可,SQL SELECT empno,ename,job,sal FROM emp WHERE sal= 1100 OR job=CLERK； EMPNO ENAME JOB SAL - - 7369 BLAKE CLERK 1300 7788 CLARK CLERK 1250 7839 KING PERSIDENT 5000 76

8、45 MARTIN MANAGER 1050,使用NOT運(yùn)算符,SQL SELECT ename ,job FROM emp WHERE job NOT IN(CLERK,MANAGER,ANALYST); ENAME JOB - - KING PERSIDENT MARTIN SALESMAN WARD SALESMAN,ORDER BY 語(yǔ)句,在缺省情況下，查詢返回的結(jié)果是沒(méi)被排序的。使用ORDER BY子可將記錄排序。ORDER BY 子句放在最后。ASC表示升序排序，DESC表示降序排序，缺省為ASC,SELECT expr FROM table WHERE condition ORD

9、ER BY column , expr ASC | DESC,降序排列,SQL select ename,hiredate from emp order by hiredate desc; ENAME HIREDATE - - FOR_TEST ADAMS 12-JAN-83 SCOTT 09-DEC-82 MILLER 23-JAN-82 JAMES 03-DEC-81 SMITH 17-DEC-80 15 rows selected.,使用列別名排序,SQL select empno,ename,sal*12 annual from emp order by annual; EMPNO E

10、NAME ANNUAL - - - 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7521 WARD 15000 7654 MARTIN 15000 7934 MILLER 15600 7844 TURNER 18000 15 rows selected.,按照多個(gè)列排序,SQL SELECT ename,sal,deptno FROM EMP ORDER BY deptno,sal DESC; ENAME SAL DEPTNO - - - KING 5000 10 CLARK 2450 10 MILLER 1300 10 SCOTT 30

11、00 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 20 . 15 rows selected.,1,2,3,4,較復(fù)雜查詢,多表查詢,分組及函數(shù),子查詢,目 錄,從多個(gè)表中獲取數(shù)據(jù),有時(shí)候你需要從多個(gè)表中獲得數(shù)據(jù)。在上面的例子中，報(bào)告顯示的數(shù)據(jù)取自兩個(gè)表。 EMPNO存在于EMP表中，DEPTNO在EMP和DEPT表中都有，LOC存在于DEPT表中為了生成上面的報(bào)告，你需要將表EMP和DEPT連起來(lái)，從兩個(gè)中獲取數(shù)據(jù)。,定義連接,當(dāng)從兩個(gè)以上的表中獲取數(shù)據(jù)時(shí)，就要使用連接條件。一個(gè)表中的記錄可以根據(jù)兩個(gè)表的相同列和另一個(gè)表的記錄相連接。兩表中的相同列一般是

12、主鍵和外鍵列。 為了能顯示兩個(gè)或多個(gè)表中的數(shù)據(jù)，在WHERE子句中需要設(shè)簡(jiǎn)單的連接條件。語(yǔ)法如下： table.column 指定取數(shù)據(jù)的表和它的列 table.column1 = table2.column2 將表連接起來(lái)的條件 當(dāng)寫(xiě)一個(gè)有連接的SELECT命令時(shí)，為了避免同樣的不同表具有同樣的列名，應(yīng)該在列前加表的名字或者表的別名。 當(dāng)在表中有相同的列名時(shí)，并且這個(gè)列作為顯示內(nèi)容或者查詢條件，必須在列名前加表名或表的別名作為前綴。 如果要將n個(gè)表連起來(lái)，你必須指定n-1個(gè)連接條件。因此連接4個(gè)表需要有3個(gè)連接條件。如果你的表有組合主鍵，此規(guī)則可能不適用，此時(shí)多一條記錄需要多個(gè)列唯一標(biāo)識(shí)。,

13、笛卡爾結(jié)果,笛卡爾結(jié)果 笛卡爾結(jié)果形成于： -連接條件被省略 -連接條件無(wú)效 -第一個(gè)表的所有記錄連接到第二個(gè)表的所有記錄 一個(gè)笛卡爾結(jié)果趨于產(chǎn)生一個(gè)巨大的記錄數(shù) ，通常沒(méi)有意義。為了避免笛卡爾結(jié)果我們要在WHERE子句中使用有效連接,笛卡爾結(jié)果舉例,SELECT * FROM EMP,DEPT;,連接類(lèi)型,有兩種主要的連接條件 等值連接 非等值連接 其它的連接方式包括 多連接 自連接 定置運(yùn)算符,什么是等值連接,就是兩個(gè)表連接的WHERE條件是一個(gè)表的列等于另外一個(gè)表的列。通常情況下，這種連接是主鍵和外鍵的連接。,使用等值連接獲取記錄舉例,因?yàn)镈EPTNO列在EMP和DEPT中都存在，因此需

14、要在這個(gè)列前面加上表名，否則Oracle認(rèn)為有歧義，語(yǔ)句無(wú)法執(zhí)行,SQL SELECT ENAME,EMP.DEPTNO,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; ENAME DEPTNO DNAME - - - MILLER 10 ACCOUNTING KING 10 ACCOUNTING CLARK 10 ACCOUNTING FORD 20 RESEARCH ADAMS 20 RESEARCH SCOTT 20 RESEARCH ,額外的條件,除了連接條件，可能還有額外的查詢條件。例如，顯示員工King的員工號(hào)、各字、部門(mén)號(hào)和部門(mén)

15、位置，這時(shí)在WHERE子句中需要設(shè)定一個(gè)額外的條件。,SQLSELECT empno, ename, emp.deptno, Loc FROM emp, dept WHERE emp.deptno=dept.deptno AND INITCAP(ename)=King; EMPNO ENAME DEPTNO LOC - - - - 7839 KING 10 NEW YORK,使用表的別名,表的別名 使用表名限定列名可能會(huì)很浪費(fèi)時(shí)間，尤其是當(dāng)表名特別長(zhǎng)，這時(shí)你可以使用表的別名。使用表的別名會(huì)減少程序代碼，因此占用較少的內(nèi)存。注意，表的別名是在FROM子句中指定的。 表別名規(guī)則： 表的別名最長(zhǎng)為3

16、0個(gè)字符，但通常以短字符為佳 表的別名最好有一定的含義 表的別名只在當(dāng)前的SELECT語(yǔ)句有效 如果在FROM子句定義了表的別名，在SELECT子句中必須用它來(lái)替代表名。,SQLSELECT empno, ename, e.deptno, Loc FROM emp e, dept d WHERE e.deptno=d.deptno AND INITCAP(ename)=King; EMPNO ENAME DEPTNO LOC - - - - 7839 KING 10 NEW YORK,非等值連接,在EMP表和SALGRADE表中，沒(méi)有直接的對(duì)應(yīng)列，它們之間的關(guān)系是EMP的SAL列的值在SALG

17、RADE表的LOSAL和HISAL列之間，它們是不等值連接。,非等值連接舉例,SQL SELECT e.sal,e.ename,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; SAL ENAME GRADE - - - 5000 KING 5 3000 SCOTT 4 3000 FORD 4 2975 JONES 4 2850 BLAKE 4 2450 CLARK 4 1600 ALLEN 3 1500 TURNER 3 1300 MILLER 2 .,外連接,使用外部連接，返回連接兩邊有一邊為NUL

18、L的記錄 外連接運(yùn)算符是加號(hào)(+) 外連接運(yùn)算符(+)可以加在左邊, 也可以加在右邊，但不能兩邊同時(shí)加外連接 有(+)的一邊表示這邊的值要么等于另外一邊，要么為NULL 從9i開(kāi)始，SQL支持ANSI SQL，也就是支持LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN,SQL SELECT table.column,table.column FROM table1,table2 WHERE table1.column(+)=table2.column;,SQL SELECT table.column,table.column FROM table

19、1,table2 WHERE table1.column = table2.column(+);,外連接舉例1：,這個(gè)例子(+)在e.deptno這邊，意味著e.deptno可以是NULL,SQL SELECT ename,e.deptno E.DEPTNO,d.deptno D.DEPTNO,d.dname FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO; ENAME E.DEPTNO D.DEPTNO DNAME - - - - SMITH 20 20 RESEARCH ALLEN 30 30 SALES . FORD 20 20 RESEARCH

20、 MILLER 10 10 ACCOUNTING 40 OPERATIONS 15 rows selected.,外連接舉例2,這個(gè)例子(+)在d.deptno這邊，意味著d.deptno可以是NULL,SQL SELECT ename,e.deptno E.DEPTNO,d.deptno D.DEPTNO,d.dname FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+); ENAME E.DEPTNO D.DEPTNO DNAME - - - - MILLER 10 10 ACCOUNTING KING 10 10 ACCOUNTING . ALLEN

21、 30 30 SALES wzh 90 FOR_TEST 90 16 rows selected.,外連接舉例3,從9i開(kāi)始，可以用ANSI SQL語(yǔ)法來(lái)寫(xiě)外連接，這樣也提供了一個(gè)以前的(+)不能實(shí)現(xiàn)的功能：全外連接,SQL SELECT E.ENAME,E.DEPTNO E.DEPTNO, D.DEPTNO D.DEPTNO,D.DNAME FROM EMP E FULL OUTER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO); ENAME E.DEPTNO D.DEPTNO DNAME - - - - MILLER 10 10 ACCOUNTING KING 10

22、 10 ACCOUNTING . WARD 30 30 SALES ALLEN 30 30 SALES wzh 90 FOR_TEST 90 40 OPERATIONS 17 rows selected.,表的自連接,有的時(shí)候，需要對(duì)表進(jìn)行自連接。例如上圖所示，EMP表中的MGR列的代表員工的經(jīng)理的員工編號(hào)，所以要想顯示出每個(gè)員工的經(jīng)理就要對(duì)EMP進(jìn)行自連接，用MGR=EMPNO。實(shí)際上，自連接只是等連接（等外連接）的一個(gè)特例，同一張表用不同的別名，區(qū)別成了不同的表。,表自連接舉例,上面這個(gè)例子相當(dāng)一個(gè)等外連接。如果員工有經(jīng)理，則顯示出員工為誰(shuí)工作（打工者），否則顯示員工為自己干活（老板）,S

23、QL SELECT E.ENAME | works for | NVL(M.ENAME,himself) RELATIONS FROM EMP E,EMP M WHERE E.MGR=M.EMPNO(+); RELATIONS - FORD works for JONES . JONES works for KING SMITH works for FORD KING works for himself 14 rows selected.,1,2,3,4,較復(fù)雜查詢,多表查詢,分組及函數(shù),子查詢,目 錄,產(chǎn)生數(shù)據(jù)組：GROUP BY子句,GROUP BY子句 使用GROUP BY 子句將一個(gè)表

24、分成許多小組，并對(duì)每一個(gè)小組返回一個(gè)計(jì)算值。 Group_by_expression：指定按什么列分組 規(guī)則： 在SELECT子句中，如果使用分組函數(shù)，不能對(duì)GROUP BY子句中指定的列使用分組函數(shù)。 使用WHERE子句，可預(yù)先排除某些記錄 在GROUP BY 子句中必須有表中的列 在GROUP BY子句中不能使用列的別名 缺省情況下在GROUP BY子句中的列以升序排，你可以使用order by子句 改變它。,SELECT column, group_function FROM table WHEREcondition GROUP BY group_by_expression ORDER

25、BY column;,使用GROUP BY舉例,注意，不是分組的列不能出現(xiàn)在SELECT后面。否則會(huì)提示：ORA-00979: not a GROUP BY expression。組函數(shù)也不能出現(xiàn)在WHERE子句中，否則會(huì)提示ORA-00934: group function is not allowed here,SQL SELECT DEPTNO, SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO SUM(SAL) MAX(SAL) MIN(SAL) AVG(SAL) - - - - - 10 8750

26、 5000 1300 2916.66667 20 10875 3000 800 2175 30 9400 2850 950 1566.66667,多列分組舉例,有時(shí)你可能需要在組中再分組，上面例子中顯示每個(gè)部門(mén)中不同頭銜的工資和與平均工資。 此時(shí)EMP表首先以部門(mén)分組，然后按頭銜分組,SQL SELECT DEPTNO,JOB,SUM(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB; DEPTNO JOB SUM(SAL) AVG(SAL) - - - - 10 CLERK 1300 1300 10 MANAGER 2450 2450 10 PRESIDE

27、NT 5000 5000 20 CLERK 1900 950 20 ANALYST 6000 3000 20 MANAGER 2975 2975 30 CLERK 950 950 30 MANAGER 2850 2850 30 SALESMAN 5600 1400,什么是分組函數(shù),和單行函數(shù)不同，分組函數(shù)作用于一組記錄，每一組返回一個(gè)結(jié)果。這些組可能是整個(gè)表，也可能是由GROUP BY子句將表分成的多個(gè)組。,主要的分組函數(shù),COUNT(*/DISTINCE/ALLexpr) 返回記錄數(shù)，這里expr賦非空值，*表示所有被選擇的記錄，包括重復(fù)記錄和空值。 MAX(DISTNCT/ALLexpr)

28、：表達(dá)式的最大值，忽略空值 MIN(DISTNCT/ALLexpr)：表達(dá)式的最小值，忽略空值 AVG(DISTNCT/ALLexpr)：平均值，忽略空值 STDDEVDISTINCT/ALLX)：返回標(biāo)準(zhǔn)差，忽略空值 SUM(DIXNTICT/ALLn)：求和，忽略空值 VARIANCE(DISTINCT/ALLX)：返回統(tǒng)計(jì)方差 這些分組函數(shù)中,COUNT是不計(jì)算NULL值的，其它函數(shù)忽略NULL值。,使用分組函數(shù)舉例1：,SQL SELECT SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP; SUM(SAL) MAX(SAL) MIN(SAL)

29、AVG(SAL) - - - - 29025 5000 800 2073.21429,使用分組函數(shù)舉例2：,SQL SELECT COUNT(*) FROM EMP; COUNT(*) - 16 SQL SELECT COUNT(EMPNO) FROM EMP; COUNT(EMPNO) - 16 SQL SELECT COUNT(MGR) FROM EMP; COUNT(MGR) - 13,使用HAVING子句限定分組函數(shù)結(jié)果值,前面我們講過(guò)，分組函數(shù)不能寫(xiě)在WHERE子句中，如果要對(duì)分組函數(shù)結(jié)果值進(jìn)行限定，可以用HAVING子句,SQL SELECT DEPTNO,JOB,SUM(SAL)

30、,AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB HAVING AVG(SAL)2000; DEPTNO JOB SUM(SAL) AVG(SAL) - - - - 10 MANAGER 2450 2450 10 PRESIDENT 5000 5000 20 ANALYST 6000 3000 20 MANAGER 2975 2975 30 MANAGER 2850 2850,SQL函數(shù),SQL函數(shù) 有兩種不同的SQL函數(shù) 單行函數(shù) 多行函數(shù) 單行函數(shù) 這些函數(shù)僅作用于單行記錄，并對(duì)每行記錄返回一個(gè)值，有許多不同類(lèi)型的單行函數(shù)，常用的類(lèi)型有： 字符函數(shù) 數(shù)字函數(shù) 日

31、期函數(shù) 轉(zhuǎn)換函數(shù) 多行函數(shù) 這些函數(shù)作用于記錄組，每組記錄返回一個(gè)結(jié)果。,單行函數(shù),單行函數(shù) 單行函數(shù)操作數(shù)據(jù)項(xiàng)，它們接收一個(gè)或多個(gè)參數(shù)，并對(duì)查詢出的每一條記錄返回一個(gè)值。參數(shù)可以是： 用戶提供的常量 一個(gè)列名 一個(gè)表達(dá)式 單行函數(shù)的特性 它們作用于查詢的每一條記錄 每條記錄返回一個(gè)結(jié)果 它們可返回一個(gè)不同于它所參照的數(shù)據(jù)類(lèi)型 它們可嵌入到SELECT ，WHERE 和ORDER BY子句。,字符串函數(shù),字符函數(shù)被分為： 大小寫(xiě)轉(zhuǎn)換函數(shù) 字符處理函數(shù) LOWER(column | expression):將字符轉(zhuǎn)換為小寫(xiě) UPPER(column | expression):將字符轉(zhuǎn)換不大寫(xiě)

32、INITCAP(column | expression):將每一個(gè)單詞的第一個(gè)字母大寫(xiě)其它小寫(xiě) CONCAT(column expression):返回第一個(gè)串接上第二個(gè)串,它的作用和| 運(yùn)算是相同的 SUBSTR(column expression,):返回從字母m開(kāi)始,有n個(gè)字符 長(zhǎng)的字符串。 LENGTH(column expression):返回字符串長(zhǎng)度 INSTR(column 1 expression.mn):返回字符串中字符的位置 LPAD(column 1 cxpression,n,string):在字符串前填補(bǔ)字符,使其長(zhǎng)度達(dá)到n。 RPAD(column 1 cxpres

33、sion,n,string):在字符串后填補(bǔ)字符,使其長(zhǎng)度達(dá)到n。,字符串函數(shù)舉例,上面的例子將ename列的首字母大寫(xiě)，其余字母小寫(xiě),SQL SELECT INITCAP(ename) FROM emp; INITCAP(EN - Smith Allen Ward ,字符串處理函數(shù)舉例,SQLSELECT ENAME,SUBSTR(ENAME,1,3) SUBSTR,RPAD(ENAME,10,*) RPAD,LENGTH(ENAME) LENGTH FROM EMP; ENAME SUBSTR RPAD LENGTH - - - - SMITH SMI SMITH* 5 ALLEN ALL

34、 ALLEN* 5 WARD WAR WARD* 4 JONES JON JONES* 5 MARTIN MAR MARTIN* 6 ,數(shù)字函數(shù),數(shù)字函數(shù)接收數(shù)字輸入返回?cái)?shù)字值 ROUND(column | expression, n):返回舍入到小數(shù)點(diǎn)右邊n位的值 TRUNC(column | expression, n):返回截?cái)嗟絥位的值 MOD(m,n):返回m和n相除后的余數(shù),使用ROUND函數(shù),上面的例子分別顯示45.923到小數(shù)點(diǎn)后兩位，個(gè)位，十位,SQL SELECT ROUND(45.923,2),ROUND(45.923,0), ROUND(45.923,-1) FROM

35、DUAL; ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) - - - 45.92 46 50,使用TRUNC函數(shù),顯示45.923到小數(shù)點(diǎn)后兩位，個(gè)位，十位,SQL SELECT TRUNC(45.923,2),TRUNC(45.923,0), TRUNC(45.923,-1) FROM DUAL; TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1) - - - 45.92 45 40,使用MOD函數(shù),這個(gè)例子計(jì)算工資除以獎(jiǎng)金后的余數(shù),SQL SELECT ename,sal ,comm,MOD(

36、sal,comm) FROM emp WHERE job = SALESMAN; ENAME SAL COMM MOD(SAL,COMM) - - - - ALLEN 1600 300 100 WARD 1250 500 250 MARTIN 1250 1400 1250 TURNER 1500 0 1500,使用日期函數(shù),Oracle的日期函數(shù) Oracle使用內(nèi)部的數(shù)字化格式存儲(chǔ)日期，它們代表世紀(jì)、年、月、日、小時(shí)、分鐘和秒. 缺省顯示的日期格式為DD-MON-YY，有效的日期在公元前4712年1月1日到公元后9999年12月31日 SYSDATE SYSDATE是一個(gè)返回當(dāng)前日期和時(shí)間的

37、日期函數(shù) DUAL DUAL是一個(gè)SYS用戶所擁有的表，所有的用戶都可以訪問(wèn)。它包括一個(gè)列DUMMY和一條記錄值為X。 例子：顯示當(dāng)前的日期 SQLSELECT SYSDATE FROM SYS.DUAL; SYSDATE - 24-MAY-07,日期運(yùn)算,日期+數(shù)字=日期 加天數(shù) 日期-數(shù)字=日期 減天數(shù) 日期-日期=數(shù)字 兩日期間的天數(shù) 日期+number/24=日期 加小時(shí) 注意：兩個(gè)日期類(lèi)型字段不能相加,日期運(yùn)算舉例,SQL SELECT ENAME,SYSDATE-HIREDATE FROM EMP; ENAME SYSDATE-HIREDATE - - SMITH 9654.549

38、54 ALLEN 9589.54954 WARD 9587.54954 JONES 9548.54954 MARTIN 9369.54954 ,常見(jiàn)的日期函數(shù),日期函數(shù)使用舉例,SQL SELECT SYSDATE,ADD_MONTHS(SYSDATE,12), LAST_DAY(SYSDATE),NEXT_DAY(SYSDATE,FRIDAY) FROM DUAL; SYSDATE ADD_MONTHS LAST_DAY(S NEXT_DAY(S - - - - 2007/05/24 2008/05/24 2007/05/31 2007/05/25,轉(zhuǎn)換函數(shù),轉(zhuǎn)換函數(shù)用于數(shù)據(jù)類(lèi)型之間的轉(zhuǎn)換。

39、SQL盡可能地自動(dòng)進(jìn)行轉(zhuǎn)換，它會(huì)隱含地調(diào)用轉(zhuǎn)換函數(shù)。但是你無(wú)法對(duì)隱含調(diào)用中使用的格式指定符進(jìn)行控制，并且這會(huì)使得你的代碼很難理解。因此使用顯式轉(zhuǎn)換函數(shù)而不依賴于隱式轉(zhuǎn)換是一個(gè)很好的程序設(shè)計(jì)風(fēng)格。,轉(zhuǎn)換函數(shù),Oracle提供了3個(gè)轉(zhuǎn)換函數(shù) TO_CHAR(number | date, fmt) :將數(shù)字或日期按格式轉(zhuǎn)換成字符 TO_NUMBER(char) :將字符串轉(zhuǎn)換成數(shù)字，此字符串必須是數(shù)字 TO_DATE(CHAR, fmt) ：將字符串按指定的格式轉(zhuǎn)換成日期,使用TO_CHAR操作日期函數(shù)舉例,上面的例子中：YYYY代表4位的年份，MM代表月份，HH24代表24小時(shí)，MI代表分鐘，SS

40、代表秒。,SQL SELECT SYSDATE,TO_CHAR(SYSDATE,YYYY/MM/DD HH24:MI:SS) DETAILDATE FROM DUAL; SYSDATE DETAILDATE - - 24-MAY-07 2007/05/24 13:47:32,使用TO_DATE函數(shù)舉例,上面的例子，如果不用TO_DATE進(jìn)行轉(zhuǎn)換，會(huì)怎么樣呢？,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19820101,YYYYMMDD); ENAME HIREDATE - - SCOTT 09-DEC-82 ADAMS

41、12-JAN-83 MILLER 23-JAN-82,SQL SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=19820101; SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=19820101 * ERROR at line 1: ORA-01861: literal does not match format string,因?yàn)?9820101不是采用默認(rèn)日期格式寫(xiě)的，Oracle無(wú)法進(jìn)行隱含轉(zhuǎn)換，導(dǎo)致報(bào)錯(cuò)。因此可以看出，對(duì)于可能發(fā)生轉(zhuǎn)換的語(yǔ)句，應(yīng)該顯式指定轉(zhuǎn)換,NVL函數(shù),將空值轉(zhuǎn)換為實(shí)際的值 數(shù)據(jù)格

42、式可以是日期，字符，數(shù)字 數(shù)據(jù)類(lèi)型必須匹配,NVL(comm,0)：如果comm為空，則轉(zhuǎn)換為0 NVL(hiredate,01-JAN-97)：如果hiredate為空，則轉(zhuǎn)換為01-JAN-97 NVL(job,No Job Yet) ：如果job為空，則轉(zhuǎn)換為No Job Yet,使用NVL,SQL SELECT ename , comm, sal , sal+300,(sal*12)+NVL(comm,0) FROM emp; ENAME COMM SAL SAL+300 (SAL*12)+NVL(COMM,0) - - - - - SMITH 800 1100 9600 ALLEN 300 1600 1900 19500 WARD 500 1250 1550 15500 JONES 2975 3275 35700 MARTIN 1400 1250 1550 16400 BLAKE 2850 3150 34200 CLARK 2450 2750 29400 SCOTT 3000 3300 36000 ,1,2,3,4,較復(fù)雜查詢,多表查詢,分組及函數(shù),子查詢,目 錄,使用子查詢解決問(wèn)題,假設(shè)想知道誰(shuí)的工資