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

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

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

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

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

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

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

7、c3b2f80b4039$HISALNUMBER此等級(jí)的最高工資通過sal指定losal和hisal的范圍來去除笛卡爾積?部門表?領(lǐng)導(dǎo)表：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;進(jìn)一步擴(kuò)展：在查詢完的工資等級(jí)進(jìn)行顯示

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語句中提供了另外一套對(duì)與表關(guān)聯(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):專門產(chǎn)生笛卡爾積SELECT*FROMempCROSSJOINdept;自然連接(NATUALJOIN):自動(dòng)進(jìn)行關(guān)聯(lián)字段的匹配SELECT*FROMempNATURALJOINdept;ON子句；由用戶自己指定關(guān)聯(lián)的條件SELECT*FROMempJOINdeptON(emp.deptno=dept.deptno);USING子句：直接指定關(guān)聯(lián)的條件SELECT*FROMempJOINdeptUSING(deptno);ered_e848

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

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

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

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

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

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

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

18、是不能出現(xiàn)分組條件的。例如：求出平均工資最高的部門A、錯(cuò)誤的代碼SELECTdeptno,MAX(AVG(sal)FROMempGROUPBYdeptno;實(shí)際上以上的代碼使用了分組函數(shù)的嵌套。B、正確的代碼SELECTMAX(AVG(sal)FROMempGROUPBYdeptno;原則：當(dāng)列中存在重復(fù)的內(nèi)容時(shí)，才可以進(jìn)行分組。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表別名對(duì)于自查詢而言又分為三類：?單列子查詢：返回的結(jié)果是一行一列?單行子查詢：一行多列，例如：一條完整的記錄?多行子查詢：返回多條記錄例如

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

21、T*FROMempWHEREsal=(SELECTMIN(sal)FROMemp);例如：查詢出工資高于公司平均工資的雇員信息SELECT*FROMempWHEREsal(SELECTAVG(sal)FROMemp);思考：要求查詢出：部門名稱，部門的雇員數(shù)，部門的平均工資，部門中最低收入的雇員的姓名。部門中最低收入、雇員數(shù)、平均工資，按部門分組SELECTdeptno,MIN(sal)fROMempGROUPBYdeptno;?多表關(guān)聯(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語句之后，實(shí)際上此查詢就是表示出了一張臨時(shí)表，臨時(shí)表的訪問要有別名。例如：查詢平均工資最底的工作名稱及其平均工資SELECTjob,AVG(sal)FROMempGROUPBYjobHAVINGAVG(sal)=(SELECTMIN(AVG(sal)FROMempGROUPBYjob);在子查詢中還提供

23、了以下的比較運(yùn)算符：?IN:指定一個(gè)范圍的內(nèi)容?ANY?ALLIN操作符例如：要求查詢出各個(gè)部門最低工資的雇員信息?每個(gè)部門的最低工資是多個(gè)，之后根據(jù)工資查詢的時(shí)候?qū)嶋H上就是應(yīng)該指定出一個(gè)具體的范圍。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);實(shí)際上ANY是要與里面的每一個(gè)內(nèi)容進(jìn)行比較的。3、ALL操作符?ALL:比最小的值要小SELECT*FROMempWHEREsalALL:比最大的值要大SELECT*FROMempWHEREsalALL(SELECTMIN(sal)FROMempGROUPBYdeptno);以上實(shí)際都是針對(duì)于多行子查詢的應(yīng)用。多列子查詢：一次性返回多個(gè)列，如果要想比較，則必須多個(gè)列一起比較SELECT*FROMempWHERE(sal,NVL(comm,0

25、)IN(SELECTsal,NVL(comm,0)FROMempWHEREdeptno=20);4、查詢練習(xí)?求出那個(gè)部門的雇員數(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、列出所有員工的姓名及其直接上級(jí)的姓名。SELECTe.ename,m.enameFROMempe,empmWHEREe.mgr=m.empno;SELECTename,(SELECTenameFROMempWHEREempno=e.mgr)FROMempe;4、列出受雇日期早于其直接上級(jí)的所有員工的編號(hào)，姓名，部門名稱。?列出受雇日期早于其直接上級(jí)的所有員工的編號(hào)，姓名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”(銷售部)工作的員工的姓名，假定不知道銷售部的部門編號(hào)。SELECT*FROMempWHEREdeptno=(SELECTdeptnoFROMdeptWHEREdnameLIKE%SALES%);9、列出薪金高于公司平均薪金的所有員工，所在部門，上級(jí)領(lǐng)導(dǎo)，公司的工資

28、等級(jí)。?求出工資高于平均工資的雇員姓名，部門編號(hào)SELECTe.ename,e.deptnoFROMempeWHEREsal(SELECTAVG(sal)FROMemp);?求出部門的名稱及上級(jí)領(lǐng)導(dǎo)信息SELECTe.ename,e.deptno,d.dname,m.enameFROMempe,deptd,empmWHEREe.sal(SELECTAVG(sal)FROMemp)ANDd.deptno=e.deptnoANDe.mgr=m.empno;?工資的等級(jí)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