SQL語言多表查詢、分組統(tǒng)計、子查詢、數(shù)據(jù)表的更新操作、事務處理

1、SQL 語言多表查詢、分組統(tǒng)計、子查詢、數(shù)據(jù)表的更新操作、事務處理3.1、多表查詢3.1.1、基本語法但是在多表查詢之前首先必須處理一個問題：例如：現(xiàn)在求出雇員表中的總記錄數(shù)(14條記錄)SELECTCOUNT(*)FROMemp;例如：現(xiàn)在求出部門表的總記錄數(shù)(4條記錄)SELECTCOUNT(*)FROMdept;所謂的多表查詢就是直接在FROM語句之后加入若干張表，下面將emp和dept表進行多表查詢SELECT*FROMemp,dept;以上確實完成了兩張表的聯(lián)合查詢，但是查詢出來的結果是56條記錄。部門表的記錄總數(shù)*雇員表的記錄總數(shù)=56條記錄。那么這樣的結果在數(shù)據(jù)庫中就稱為笛卡爾積

2、。對于這樣的結果明顯不是最終查詢者需要返回的結果，應該想辦法去掉笛卡爾積。所以如果要使用多表查詢，則必須按照以下的語句形式進行編寫：SELECT字段FROM表1,表2WHERE將兩張表的關聯(lián)字段進行比較，去掉笛卡爾積以emp和dept表為例1、雇員表結構：No.字段名稱字段類型字段作用EMPNONUMBER(4)表示的是雇員編號，長度為四位的整數(shù)ENAMEVARCHAR2(10)雇員的姓名，使用字符串表示，字符串的長度最大為10JOBVARCHAR2(9)工作，字符串表示，最大長度為9MGRNUMBER(4)雇員的直接上級領導編號HIREDATEDATE雇傭日期SALNUMBER(7,2)工資

3、，工資長度一共是7位，其中整數(shù)占5位，小數(shù)占2位COMMNUMBER(7,2)獎金(傭金)DEPTNONUMBER(2)部門編號2、部門表結構：No.字段名稱字段類型字段作用DEPTNONUMBER(2)雇員編號DNAMEVARCHAR2(14)部門名稱LOCVARCHAR2(13)部門位置兩張表中都存在deptno字段，一般在數(shù)據(jù)庫建表的時候都會把關聯(lián)字段的名稱統(tǒng)一。例如：使用關聯(lián)字段消除掉之前多表查詢的迪卡爾積。SELECT*FROMemp,deptWHEREemp.deptno=dept.deptno;但是如果一直按照以上的格式編寫的話，對于表名稱太長的情況時，如果在訪問字段前還是使用表

4、名稱的形式，會很麻煩。所以一般可以為一張表起一個別名。修改以上的范例：SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;例如：要求查詢出雇員的編號、雇員的姓名、工資、部門的名稱及位置SELECTe.empno,e.ename,e.sal,d.dname,d.locFROMempe,deptdWHEREe.deptno=d.deptno;例如：要求查詢出每個雇員的姓名、工作、雇員工資、領導姓名、領導工資?如果要找到領導信息，則肯定需要emp表?如果要找到雇員信息，則肯定需要emp表?消除笛卡爾積的條件：雇員的管理者的編號=雇員中的雇員編號SELECTe.en

5、ame雇員女4名，e.job雇員工作，e.sal雇員工資,m.ename領導姓名,m.sal領導工資FROMempe,empmWHEREe.mgr=m.empno;可以發(fā)現(xiàn)，本次查詢是本表關聯(lián)本表，那么這樣的查詢稱為自身關聯(lián)查詢。例如：要求查詢出每個雇員的姓名、工作、雇員工資、部門名稱、領導姓名、領導工資?如果要找到領導信息，則肯定需要emp表?如果要找到雇員信息，則肯定需要emp表?如果要找到部門信息，則肯定需要dept表SELECTe.ename雇員女4名,e.job雇員工作，e.sal雇員工資,m.ename領導姓名,m.sal領導工資,d.dname部門名稱FROMempe,empm,

6、deptdWHEREe.mgr=m.empnoANDe.deptno=d.deptno;注意：在以上的查詢中性能如何？思考：現(xiàn)在要求查詢出每個雇員的姓名、工資、部門名稱，雇員的工資及在公司的工資等級、領導的姓名、領導的工資及工資在公司的工資等級。salgrade:工資等級表No.字段名稱字段類型字段作用ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$GRADENUMBER等級編號ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$LOSALNUMBER此等級的最彳氐工資ered_e848cd87-bb17-4aad-ae8d-

7、c3b2f80b4039$HISALNUMBER此等級的最高工資通過sal指定losal和hisal的范圍來去除笛卡爾積?部門表?領導表：empSELECTe.ename,e.sal,d.dname,e.sal,s.grade,m.ename,m.sal,sm.gradeFROMempe,deptd,empm,salgrades,salgradesmWHEREe.salBETWEENs.losalANDs.hisalANDe.deptno=d.deptnoANDm.salBETWEENsm.losalANDsm.hisalANDe.mgr=m.empno;進一步擴展：在查詢完的工資等級進行顯示

8、的修改，修改要求如下：“E等工資”“D等工資”“C等工資”“B等工資”“A等工資”DECODE函數(shù)SELECTe.ename,e.sal,d.dname,e.sal,DECODE（s.grade,1,E等工資,2,D等工資,3,C等工資,4,B等工資,5,A等工資），m.ename,m.sal,DECODE（sm.grade,1,E等工資,2,D等工資,3,C等工資,4,B等工資,5,A等工資）FROMempe,deptd,empm,salgrades,salgradesmWHEREe.salBETWEENs.losalANDs.hisalANDe.deptno=d.deptnoANDm.sa

9、lBETWEENsm.losalANDsm.hisalANDe.mgr=m.empno;ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$.1.2、SQL:1999語法（了解）在SQL語句中提供了另外一套對與表關聯(lián)查詢的支持語法：SELECTtable1.column,table2.columnFROMtable1CROSSJOINtable2|NATURALJOINtable2|JOINtable2USING(column_name)|JOINtable2ON(table1.column_name=table2.column_name)|LEFT|RIGHT

10、|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name);交叉連接(CROSSJOIN):專門產生笛卡爾積SELECT*FROMempCROSSJOINdept;自然連接(NATUALJOIN):自動進行關聯(lián)字段的匹配SELECT*FROMempNATURALJOINdept;ON子句；由用戶自己指定關聯(lián)的條件SELECT*FROMempJOINdeptON(emp.deptno=dept.deptno);USING子句：直接指定關聯(lián)的條件SELECT*FROMempJOINdeptUSING(deptno);ered_e848

11、cd87-bb17-4aad-ae8d-c3b2f80b4039$.1.3、左、右連接現(xiàn)在先查詢出全部雇員和部門的信息SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;查詢結果：以上的結果中顯示了三個部門，但是實際上部門有四個select*fromdept;那么為什么之前列出的所有部門中沒有40部門，因為在雇員表中沒有一個雇員屬于40部門。因為匹配的結果都是以雇員為主。以等號左邊為準，實際上以上的多表查詢，可以直接通過以下的形式表現(xiàn)：SELECT*FROMempe,deptdWHEREe.deptno=d.deptno(+);也就是說，現(xiàn)在的代碼以等號的左

12、邊為準，所以此連接稱為左連接，如果現(xiàn)在以等號的右邊為準呢？那么就稱為右連接：SELECT*FROMempe,deptdWHEREe.deptno(+)=d.deptno;從程序運行結果可以發(fā)現(xiàn)，40部門出現(xiàn)了，那么意味著現(xiàn)在的匹配條件以等號右邊為準，所以是右連接?？梢园l(fā)現(xiàn)一個規(guī)律:?(+)在等號的左邊屬于右連接?(+)在等號的右邊屬于左連接同樣SQL:1999語法也對左、右連接有所支持(了解)例如：實現(xiàn)左連接SELECT*FROMempLEFTOUTERJOINdeptON(emp.deptno=dept.deptno);例如：實現(xiàn)右連接SELECT*FROMempRIGHTOUTERJOIN

13、deptON(emp.deptno=dept.deptno);ered_e848cd87-bb17-4aad-ae8d-c3b2f80b4039$.2.2、分組在SQL語句中如果要想實現(xiàn)對數(shù)據(jù)的分組統(tǒng)計，則必須使用GROUPBY子句，此時，完整的SQL語法如下：SELECTcolumn|*FROMtablelals1,table2als2WHEREconditionsGROUPBYcolumnORDERBYcolumn例如：求出每個部門的雇員數(shù)量?應該按照部門編號進行分組SELECTdeptno,COUNT(*)FROMempGROUPBYdeptno;例如：求出每個部門的平均工資SELECT

14、deptno,AVG(sal)FROMempGROUPBYdeptno;以上已經完成了分組，但是對于分組統(tǒng)計中，是不能出現(xiàn)分組條件之外的字段。例如：有以下一段SQL語句SELECTdeptno,AVG(sal),enameFROMempGROUPBYdeptno;一旦執(zhí)行之后出現(xiàn)以下的錯誤提示：SELECTdeptno,AVG(sal),ename第1行出現(xiàn)錯誤：ORA-00979:不是GROUPBY表達式對于分組函數(shù)而言，可以單獨使用，如果要連同其他字段一起查詢，此字段必須是分組的字段，而且此語句之中必須存在groupby子句：SELECTdeptno,COUNT(*)FROMemp;出現(xiàn)以

15、下錯誤：SELECTdeptno,COUNT(*)第1行出現(xiàn)錯誤：ORA-00937:不是單組分組函數(shù)而如果只寫一個分組函數(shù)，則不會有任何的問題。例如：要求顯示出平均工資大于2000的部門編號及平均工資SELECTdeptno,AVG(sal)FROMempWHEREAVG(sal)2000GROUPBYdeptno;語句執(zhí)行后出現(xiàn)以下的錯誤：WHEREAVG(sal)2000第3行出現(xiàn)錯誤：在WHERE語句之中是不能出現(xiàn)分組函數(shù)的。只要是分組條件且此條件中要使用分組函數(shù)，就必須在HAVING子句之中編寫，與WHERE子句的功能一樣，只是HAVING必須寫在GROUP之后，沒有GROUPBY絕

16、對不能出現(xiàn)HAVING。SELECTdeptno,AVG(sal)FROMempGROUPBYdeptnoHAVINGAVG(sal)2000;完整的語句格式：SELECTcolumn|*FROMtable1als1,table2als2WHEREconditionsGROUPBYcolumnHAVING分組條件ORDERBYcolumn例如：顯示非銷售人員的工作名稱以及從事同一工作雇員的月工資的總和，并且要滿足從事相同工作雇員的月工資合計大于5000,輸出結果按月工資的合計升序排列。1、顯示全部非銷售人員的信息(限定條件，需要在WHERE子句中編寫)SELECT*FROMempWHEREjo

17、bSALESMAN;2、按雇員的工作分組，分組之后可以求出工資的總和SELECTjob,SUM(sal)FROMempWHEREjobSALESMANGROUPBYjob;3、對分組的條件進行過濾，求出月工資的總和大于5000SELECTjob,SUM(sal)FROMempWHEREjobSALESMANGROUPBYjobHAVINGSUM(sal)5000;4、按照工資的總和進行排序SELECTjob,SUM(sal)suFROMempWHEREjobSALESMANGROUPBYjobHAVINGSUM(sal)5000ORDERBYsu;注意：分組函數(shù)本身是允許嵌套的，但是，嵌套之后

18、是不能出現(xiàn)分組條件的。例如：求出平均工資最高的部門A、錯誤的代碼SELECTdeptno,MAX(AVG(sal)FROMempGROUPBYdeptno;實際上以上的代碼使用了分組函數(shù)的嵌套。B、正確的代碼SELECTMAX(AVG(sal)FROMempGROUPBYdeptno;原則：當列中存在重復的內容時，才可以進行分組。3.3、子查詢例如：要求查詢出工資比7566雇員的工資要高的全部雇員信息確定出7566雇員的工資SELECTsalFROMempWHEREempno=7566;求出工資大于7566的雇員SELECT*FROMempWHEREsal(SELECTsalFROMempWH

19、EREempno=7566);需要注意的是，子查詢可以在任意的位置上編寫：SELECT、FROM、WHERE、HAVING子查詢語法：SELECT字段,SELECTcolumn_listFROM表別名FROM表名稱,SELECTcolumn_listFROM表別名WHERE條件,SELECTcolumn_listFROM表別名GROUPBY分組條件，SELECTcolumn_listFROM表別名HAVING條件,SELECTcolumn_listFROM表別名對于自查詢而言又分為三類：?單列子查詢：返回的結果是一行一列?單行子查詢：一行多列，例如：一條完整的記錄?多行子查詢：返回多條記錄例如

20、：要求查詢出工資比7654高，同時又與7788從事同一個工作的雇員信息?查詢出7654雇員的工資SELECTsalFROMempWHEREempno=7654;?查出7788的工作SELECTjobFROMempWHEREempno=7788;將以上的兩條記錄作為查詢的條件SELECT*FROMempWHEREsal(SELECTsalFROMempWHEREempno=7654)ANDjob=(SELECTjobFROMempWHEREempno=7788);例如：要求查詢出工資最低的雇員的姓名、工作、工資求出最低的工作SELECTMIN(sal)FROMemp;以此為條件進行查詢SELEC

21、T*FROMempWHEREsal=(SELECTMIN(sal)FROMemp);例如：查詢出工資高于公司平均工資的雇員信息SELECT*FROMempWHEREsal(SELECTAVG(sal)FROMemp);思考：要求查詢出：部門名稱，部門的雇員數(shù)，部門的平均工資，部門中最低收入的雇員的姓名。部門中最低收入、雇員數(shù)、平均工資，按部門分組SELECTdeptno,MIN(sal)fROMempGROUPBYdeptno;?多表關聯(lián)及子查詢SELECTd.dname,e.ename,dm.avg,dm.min,dm.countFROMdeptd,empe,(SELECTdeptno,AV

22、G(sal)avg,MIN(sal)min,COUNT(empno)countFROMempGROUPBYdeptno)dmWHEREd.deptno=e.deptnoANDd.deptno=dm.deptnoANDe.sal=dm.min;由此可見，子查詢可以在任意的位置上出現(xiàn)，如果在FROM語句之后，實際上此查詢就是表示出了一張臨時表，臨時表的訪問要有別名。例如：查詢平均工資最底的工作名稱及其平均工資SELECTjob,AVG(sal)FROMempGROUPBYjobHAVINGAVG(sal)=(SELECTMIN(AVG(sal)FROMempGROUPBYjob);在子查詢中還提供

23、了以下的比較運算符：?IN:指定一個范圍的內容?ANY?ALLIN操作符例如：要求查詢出各個部門最低工資的雇員信息?每個部門的最低工資是多個，之后根據(jù)工資查詢的時候實際上就是應該指定出一個具體的范圍。SELECT*FROMempWHEREsalIN(SELECTMIN(sal)FROMempGROUPBYdeptno);ANY操作符：=ANY、ANY、ANY?=ANY:功能與IN一致例如：以上的程序修改為二ANYSELECT*FROMempWHEREsal=ANY(SELECTMIN(sal)FROMempGROUPBYdeptno);?ANY:比最大的值要小SELECT*FROMempWHE

24、REsalANY:比最小的值要大SELECT*FROMempWHEREsalANY(SELECTMIN(sal)FROMempGROUPBYdeptno);實際上ANY是要與里面的每一個內容進行比較的。3、ALL操作符?ALL:比最小的值要小SELECT*FROMempWHEREsalALL:比最大的值要大SELECT*FROMempWHEREsalALL(SELECTMIN(sal)FROMempGROUPBYdeptno);以上實際都是針對于多行子查詢的應用。多列子查詢：一次性返回多個列，如果要想比較，則必須多個列一起比較SELECT*FROMempWHERE(sal,NVL(comm,0

25、)IN(SELECTsal,NVL(comm,0)FROMempWHEREdeptno=20);4、查詢練習?求出那個部門的雇員數(shù)量SELECTdeptno,COUNT(empno)FROMempGROUPBYdeptno;?求出數(shù)量大于1的所有部門SELECTd.*,dc.cFROMdeptd,(SELECTdeptno,COUNT(empno)cFROMempGROUPBYdeptno)dcWHEREd.deptno=dc.deptno;2、列出薪金比“SMITH多的所有員工。SELECT*FROMempWHEREsal(SELECTsalFROMempWHEREename=SMITH);

26、3、列出所有員工的姓名及其直接上級的姓名。SELECTe.ename,m.enameFROMempe,empmWHEREe.mgr=m.empno;SELECTename,(SELECTenameFROMempWHEREempno=e.mgr)FROMempe;4、列出受雇日期早于其直接上級的所有員工的編號，姓名，部門名稱。?列出受雇日期早于其直接上級的所有員工的編號，姓名SELECTe.empno,e.enameFROMempe,empmWHEREe.mgr=m.empnoANDe.hiredatem.hiredate;?為其加入部門SELECTe.empno,e.ename,d.dname

27、FROMempe,empm,deptdWHEREe.mgr=m.empnoANDe.hiredate1500;?雇員的人數(shù)SELECTjob,COUNT(*)FROMempWHEREjobIN(SELECTjobFROMempGROUPBYjobHAVINGMIN(sal)1500)GROUPBYjob;8、列出在部門“SALES”(銷售部)工作的員工的姓名，假定不知道銷售部的部門編號。SELECT*FROMempWHEREdeptno=(SELECTdeptnoFROMdeptWHEREdnameLIKE%SALES%);9、列出薪金高于公司平均薪金的所有員工，所在部門，上級領導，公司的工資

28、等級。?求出工資高于平均工資的雇員姓名，部門編號SELECTe.ename,e.deptnoFROMempeWHEREsal(SELECTAVG(sal)FROMemp);?求出部門的名稱及上級領導信息SELECTe.ename,e.deptno,d.dname,m.enameFROMempe,deptd,empmWHEREe.sal(SELECTAVG(sal)FROMemp)ANDd.deptno=e.deptnoANDe.mgr=m.empno;?工資的等級SELECTe.ename,e.deptno,d.dname,m.ename,s.gradeFROMempe,deptd,empm,

29、salgradesWHEREe.sal(SELECTAVG(sal)FROMemp)ANDd.deptno=e.deptnoANDe.mgr=m.empnoANDe.salBETWEENs.losalANDs.hisal;10、列出與“SCOTT從事相同工作的所有員工及部門名稱。SELECTe.ename,d.dnameFROMempe,deptdWHEREe.job=(SELECTjobFROMempWHEREename=SCOTT)ANDe.deptno=d.deptnoANDe.enameSCOTT;11、列出薪金等于部門30中員工的薪金的所有員工的姓名和薪金。?多列自查詢，IN操作SELECT*FROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=30);12、列出薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金、部門名稱。?30部門的所有雇員薪金SELECTsalFROMempWHEREdeptno=30;?列出大于：ALLSELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.salALL(SELECTsalFR