子查詢與高級(jí)查詢

2第8章子查詢與高級(jí)查詢本章要點(diǎn)：掌握在WHERE子句中使用子查詢。掌握在HAVING子句中使用子查詢。熟練掌握使用IN、ANY和ALL操作符實(shí)現(xiàn)子查詢。熟練掌握關(guān)聯(lián)子查詢。熟練掌握嵌套子查詢。掌握簡單連接。熟練掌握多個(gè)表之間的內(nèi)連接。熟練掌握多個(gè)表之間的外連接。了解多個(gè)表之間的交叉連接。掌握使用集合操作符實(shí)現(xiàn)集合查詢。38.1子查詢?cè)谕獠康腟ELECT、UPDATE或DELETE語句內(nèi)部使用SELECT語句，這個(gè)內(nèi)部SELECT語句稱為子查詢(Subquery)。使用子查詢，主要是將子查詢的結(jié)果作為外部主查詢的查找條件。48.1.1子查詢的類型在子查詢中可以使用兩種比較操作符——單行操作符和多行操作符。單行操作符：例如=、>、>=、<、<=、<>、!=。多行操作符：例如ALL、ANY、IN、EXISTS?？梢园炎硬樵兎譃閮煞N類型：單行子查詢和多行子查詢。另外，子查詢還有下面3種子類型，這3種子類型可以返回一行或多行查詢結(jié)果。多列子查詢：向外部的SQL語句返回多列。關(guān)聯(lián)子查詢：引用外部的SQL語句中的一列或多列。在關(guān)聯(lián)子查詢中，可以使用EXISTS和NOTEXISTS操作符。嵌套子查詢：在子查詢中包含有子查詢。指定子查詢時(shí)，需要注意以下幾點(diǎn)：子查詢需要使用括號(hào)()括起來。子查詢要放在比較操作符的右邊。當(dāng)子查詢的返回值是一個(gè)集合而不是一個(gè)值時(shí)，不能使用單行操作符，而必須根據(jù)需要使用ANY、IN、ALL或EXISTS等操作符。58.1.2在WHERE子句中使用子查詢1．使用子查詢?cè)赪HERE子句中使用子查詢的一般用法形式如下：SELECTcolumn_listFROMtable_nameWHEREexpressionoperator( SELECTcolumn_nameFROMtable_nameWHEREcondition GROUPBYexpHAVINGhaving);其中，在外部SELECT語句的WHERE子句中，expression用來指定一個(gè)表達(dá)式，也可以是表中的一列；operator可以是單行和多行操作符；()中的內(nèi)容表示子查詢內(nèi)容。68.1.2在WHERE子句中使用子查詢2．不能使用ORDERBY子句在子查詢的SELECT語句中，可以使用FROM子句、WHERE子句、GROUPBY子句和HAVING子句等，但是有些情況下不能使用ORDERBY子句，例如在WHERE子句中使用子查詢時(shí)，子查詢語句中就不能使用ORDERBY子句。78.1.3在HAVING子句中使用子查詢?cè)赟ELECT語句中使用HAVING子句，可以實(shí)現(xiàn)對(duì)數(shù)據(jù)進(jìn)行分組過濾。在HAVING子句中，如果使用子查詢，那么就可以實(shí)現(xiàn)對(duì)子查詢返回的結(jié)果根據(jù)分組進(jìn)行過濾?！纠?.3】對(duì)scott用戶的emp表進(jìn)行檢索，在HAVING子句中使用子查詢。獲取哪些部門的員工平均工資小于全體員工的平均工資。具體如下：SQL>SELECTdeptno,AVG(sal)FROMscott.empGROUPBYdeptno2HAVINGAVG(sal)<(3 SELECTAVG(sal)FROMscott.emp);DEPTNO AVG(SAL)---------- -------------30 1566.6666788.1.4使用IN操作符實(shí)現(xiàn)指定匹配查詢1．使用IN操作符IN操作符用來檢查在一個(gè)值列表中是否包含指定的值。這個(gè)值列表可以是子查詢的返回結(jié)果。2．使用NOTIN操作符NOTIN操作符用來檢查在一個(gè)值列表中是否不包含指定的值，NOTIN執(zhí)行的操作正好與IN在邏輯上相反。3．常見的操作錯(cuò)誤多行子查詢可以返回多行記錄，如果接收子查詢結(jié)果的操作符是單行操作符，那么在執(zhí)行語句時(shí)，可能會(huì)出現(xiàn)錯(cuò)誤提示。98.1.5使用ANY操作符實(shí)現(xiàn)任意匹配查詢?cè)谶M(jìn)行多行子查詢時(shí)，使用ANY操作符，用來將一個(gè)值與一個(gè)列表中的所有值進(jìn)行比較，這個(gè)值只需要匹配列表中的一個(gè)值即可，然后將滿足條件的數(shù)據(jù)返回。其中，值列表可以是子查詢的返回結(jié)果。在使用ANY操作符之前，必須使用一個(gè)單行操作符，例如=、>、<、<=等。【例8.7】對(duì)scott用戶的emp表進(jìn)行操作，獲得工資大于任意一個(gè)部門的平均工資的員工信息，如下：參見教材P176108.1.6使用ALL操作符實(shí)現(xiàn)全部匹配查詢?cè)谶M(jìn)行子查詢時(shí)，使用ALL操作符，用來將一個(gè)值與一個(gè)列表中的所有值進(jìn)行比較，這個(gè)值需要匹配列表中的所有值，然后將滿足條件的數(shù)據(jù)返回。其中，值列表可以是子查詢的返回結(jié)果。在使用ALL操作符之前，必須使用一個(gè)單行操作符，例如=、>、<、<=等?！纠?.8】對(duì)scott用戶的emp表進(jìn)行操作，獲得工資大于所有部門的平均工資的員工信息，如下：參見教材P176118.1.7實(shí)現(xiàn)多列子查詢多列子查詢則是指返回多列數(shù)據(jù)的子查詢語句。當(dāng)多列子查詢返回單行數(shù)據(jù)時(shí)，在WHERE子句中可以使用單行操作符；返回多行數(shù)據(jù)時(shí)，在WHERE子句中必須使用多行操作符。使用子查詢比較多個(gè)列的數(shù)據(jù)時(shí)，可以使用下面兩種方式。成對(duì)比較：要求多個(gè)列的數(shù)據(jù)必須同時(shí)匹配。非成對(duì)比較：通過指定連接關(guān)鍵字，例如AND或OR等，指定多個(gè)列的數(shù)據(jù)是否必須同時(shí)匹配。如果使用AND關(guān)鍵字，表示同時(shí)匹配，這樣就可以實(shí)現(xiàn)與成對(duì)比較同樣的結(jié)果；如果使用OR關(guān)鍵字，表示不必同時(shí)匹配。128.1.7實(shí)現(xiàn)多列子查詢【例8.9】對(duì)scott用戶的emp表進(jìn)行操作，使用成對(duì)比較的方式，獲得每個(gè)部門中工資最低的員工信息，如下：參見教材P177【例8.10】使用非成對(duì)比較的方式，并且指定AND關(guān)鍵字，獲得每個(gè)部門中工資最低的員工信息，如下：參見教材P177138.1.8實(shí)現(xiàn)關(guān)聯(lián)子查詢關(guān)聯(lián)子查詢會(huì)引用外部查詢中的一列或多列，這種子查詢之所以被稱為關(guān)聯(lián)子查詢，是因?yàn)樗拇_與外部語句相關(guān)。【例8.11】查詢各個(gè)部門中，哪些員工的工資低于其所在部門的平均工資，如下：參見教材P178148.1.8實(shí)現(xiàn)關(guān)聯(lián)子查詢1．使用EXISTS操作符在關(guān)聯(lián)子查詢中可以使用EXISTS或NOTEXISTS操作符。其中，EXISTS操作符用于檢查子查詢所返回的行是否存在，它可以在非關(guān)聯(lián)子查詢中使用，但是更常用于關(guān)聯(lián)子查詢。2．使用NOTEXISTS操作符在執(zhí)行的操作邏輯上，NOTEXISTS操作符的作用與EXISTS操作符相反。在需要檢查數(shù)據(jù)行中是否不存在子查詢返回的結(jié)果時(shí)，就可以使用NOTEXISTS。 158.1.8實(shí)現(xiàn)關(guān)聯(lián)子查詢3．EXISTS與IN的比較在使用NOTEXISTS和NOTIN時(shí)，如果一個(gè)值列表中包含有空值，NOTEXISTS返回TRUE；而NOTIN則返回FALSE?！纠?.14】使用NOTEXISTS操作符，檢索最高領(lǐng)導(dǎo)人員的有關(guān)信息，也就是該員工沒有上級(jí)領(lǐng)導(dǎo)，對(duì)應(yīng)的mgr列的值為NULL。具體如下：參見教材P179【例8.15】使用NOTIN操作符，重寫上述語句，如下：參見教材P180168.1.9實(shí)現(xiàn)嵌套子查詢所謂嵌套子查詢，是指在子查詢內(nèi)部使用其他子查詢。嵌套子查詢的嵌套層次最多為255層。大多數(shù)情況下，嵌套子查詢都在外層子查詢的WHERE子句中。【例8.16】已知工作地點(diǎn)在NEWYORK和CHICAGO的兩個(gè)部門，要求根據(jù)這兩個(gè)部門的平均工資中的最大值，獲取工資大于這個(gè)最大值的員工信息，如下：SQL>SELECTempno,ename,sal,deptnoFROMscott.emp2WHEREsal>(3 SELECTMAX(AVG(sal))FROMscott.empWHEREdeptnoIN(4 SELECTdeptnoFROMscott.dept5 WHERElocIN('NEWYORK','CHICAGO'))6 GROUPBYdeptno);EMPNO ENAME SAL DEPTNO---------- ---------- ---------- ----------7566 JONES 2975 207788 SCOTT 3000 207839 KING 5000 107902 FORD 3000 20178.1.10在UPDATE和DELETE語句中使用子查詢1．在UPDATE語句中使用子查詢?cè)赨PDATE語句中使用子查詢，可以將子查詢返回的結(jié)果賦值給需要更新的列?！纠?.17】將員工編號(hào)為7839的員工的工資設(shè)置為平均工資，如下：SQL>UPDATEscott.empSETsal=(2 SELECTAVG(sal)FROMscott.emp)3WHEREempno=7839;已更新1行。 2．在DELETE語句中使用子查詢?cè)贒ELETE語句中使用子查詢，可以根據(jù)子查詢返回的結(jié)果刪除指定的行。【例8.18】刪除工作地點(diǎn)在NEWYORK的所有員工信息，如下：SQL>DELETEFROMscott.empWHEREdeptnoIN(2 SELECTdeptnoFROMscott.deptWHEREloc='NEWYORK');已刪除3行。188.2實(shí)驗(yàn)指導(dǎo)——獲取借閱次數(shù)最多的前5本圖書實(shí)驗(yàn)指導(dǎo)8-1：獲取借閱次數(shù)最多的前5本圖書由于需要顯示圖書信息，所以從圖書表book中進(jìn)行檢索；然后結(jié)合借書表lend，獲得每本書的借閱情況；最后按照每本書的借閱次數(shù)降序排列，獲得前5本圖書信息即可。具體如下：參見教材P182上述語句中，在最外層SELECT語句的WHERE子句中使用了子查詢，并且使用IN操作符指定匹配查詢；第3行的SELECT語句中，在FROM子句中使用子查詢，通過子查詢獲得分組和排序之后的bookid；最后在WHERE子句中指定rownum列小于等于5，指定排序之后的前5行記錄。補(bǔ)充在select子句中使用子查詢顯示每個(gè)員工和企業(yè)平均工資之間的差距selectempno,ename,sal,sal-(selectavg(sal)fromscott.emp)saldifffromscott.emp在from子句中使用子查詢查詢各個(gè)部門中，那些員工的工資低于其所在部門的平均工資selecta.empno,a.ename,a.sal,a.deptnofromscott.empa,(selectdeptno,avg(sal)avgsalfromscott.empgroupbyscott.emp.deptno)bwherea.deptno=b.deptnoanda.sal<b.avgsal19練習(xí)背景：P106圖書管理系統(tǒng)出版時(shí)間最早的圖書名稱和圖書編號(hào)價(jià)格次高的圖書名稱和圖書編號(hào)每類圖書中價(jià)格最低的圖書的圖書名稱和圖書編號(hào)那類圖書中包含的圖書最多。20練習(xí)出版時(shí)間最早的圖書名稱和圖書編號(hào)selectbooknumber,booknamefrombookwherebook.booktime=(selectmin(booktime)frombook)價(jià)格次高的圖書名稱和圖書編號(hào)selectbooknumber,booknamefrombookwherebook.bookprice=(selectmax(bookprice)frombookwherebookprice<(selectmax(bookprice)frombook))21練習(xí)每類圖書中價(jià)格最低的圖書的圖書名稱和圖書編號(hào)selecta.booknumber,a.booknamefrombooka,(selecttypeid,min(bookprice)minbookpricefrombookgroupbybook.typeid)bwherea.typeid=b.typeidanda.bookprice=b.minbookprice那類圖書中包含的圖書最多。selecttypeidfrom(selecttypeid,count(*)frombookgroupbybook.typeidorderbycount(*)desc)whererownum=122238.3高級(jí)查詢檢索數(shù)據(jù)時(shí)，通過各個(gè)表之間共同列的關(guān)聯(lián)性，可以查詢存放在多個(gè)表中的不同實(shí)體的信息。如果在查詢時(shí)需要對(duì)多個(gè)表進(jìn)行操作，并且指定多個(gè)表的連接關(guān)系，則該查詢就稱為高級(jí)查詢，也可以稱為連接查詢。248.3.1使用等號(hào)(=)實(shí)現(xiàn)多個(gè)表的簡單連接在連接查詢中，如果僅僅通過SELECT子句和FROM子句連接多個(gè)表，那么查詢的結(jié)果將是一個(gè)通過笛卡兒積所生成的表?！纠?.19】使用SELECT子句和FROM子句，從scott用戶的emp表和dept表中檢索數(shù)據(jù)，如果不指定檢索條件，將得到56行記錄。具體如下：參見教材P183由于scott.emp表中有14行記錄，scott.dept表中有4行記錄，所以笛卡兒積所生成的表一共有56(14*4=56)行記錄。258.3.2使用表的別名設(shè)置表的別名，只需要在FROM子句中引用該表時(shí)，將表別名跟在表的實(shí)際名稱后面即可。表別名和表的實(shí)際名稱之間使用空格進(jìn)行分隔。【例8.21】使用表別名的方式，重寫例8.20中所示的語句。為scott.emp表設(shè)置表別名為e，為scott.dept表設(shè)置表別名為d。語句如下：SQL>SELECTempno,ename,sal,e.deptno,d.deptno,dname2FROMscott.empe,scott.deptd3WHEREe.deptno=d.deptno;268.3.3使用INNERJOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接在FROM子句中，使用JOIN連接的語法形式如下：FROMjoin_table1join_typejoin_table2[ON(join_condition)] [join_type...ONjoin_condition,...]語法說明如下。join_table1、join_table2：參與連接操作的表名。join_type：連接類型，連接類型有INNERJOIN(內(nèi)連接)、OUTERJOIN(外連接)和CROSSJOIN(交叉連接)。join_condition：連接條件，由被連接表中的列和比較運(yùn)算符、邏輯運(yùn)算符等構(gòu)成?？梢允褂枚嘟Mjoin_type…ONjoin_condition…子句，實(shí)現(xiàn)多個(gè)表的連接。278.3.3使用INNERJOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接1．等值連接所謂等值連接，是指在連接條件中使用等于(=)運(yùn)算符比較被連接的值，也就是通過相等的列值連接起來的查詢。2．不等連接所謂不等連接，就是在連接條件中使用除等號(hào)(=)外的其他比較運(yùn)算符，構(gòu)成非等值連接查詢?？梢允褂玫谋容^運(yùn)算符包括：>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)、!=(不等于)、LIKE、IN和BETWEEN等。288.3.3使用INNERJOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接3．自然連接自然連接(NATURALJOIN)是在兩個(gè)表中尋找列名和數(shù)據(jù)類型都相同的字段，通過相同的字段將兩個(gè)表連接在一起，并返回所有符合條件的結(jié)果。4．使用USING關(guān)鍵字簡化連接SQL/92標(biāo)準(zhǔn)可以使用USING關(guān)鍵字來簡化連接查詢，但是只有在查詢滿足下面兩個(gè)條件時(shí)，才能使用USING關(guān)鍵字進(jìn)行簡化：查詢必須是等值連接。等值連接中的列必須具有相同的名稱和數(shù)據(jù)類型。298.3.3使用INNERJOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接【例8.25】使用USING關(guān)鍵字，重寫例8.22中的語句，如下：參見教材P187使用USING關(guān)鍵字簡化連接時(shí)，需要注意以下幾點(diǎn)。(1) 使用emp表和dept表中的deptno列進(jìn)行連接時(shí)，在USING子句和SELECT子句中，都不能為deptno列指定表名或表別名。(2) 如果在連接查詢時(shí)使用了兩個(gè)表中相同的多個(gè)列，那么就可以在USING子句中指定多個(gè)列名，形式如下：參見教材P187(3) 如果對(duì)多個(gè)表進(jìn)行檢索，那么就必須多次使用USING關(guān)鍵字進(jìn)行指定，形式如下：參見教材P187308.3.4使用OUTERJOIN實(shí)現(xiàn)多個(gè)表的外連接對(duì)于外連接，Oracle中可以使用加號(hào)(+)來表示，也可以使用LEFT、RIGHT和FULLOUTERJOIN關(guān)鍵字。外連接可以分為下面這3類：左外連接(LEFTOUTERJOIN或LEFTJOIN)右外連接(RIGHTOUTERJOIN或RIGHTJOIN)全外連接(FULLOUTERJOIN或FULLJOIN)318.3.4使用OUTERJOIN實(shí)現(xiàn)多個(gè)表的外連接1．左外連接左外連接是在檢索結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN關(guān)鍵字左側(cè)表中所有滿足檢索條件的行。2．右外連接右外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN右側(cè)表中所有滿足檢索條件的行。3．全外連接全外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN兩側(cè)表中所有滿足檢索條件的行。328.3.5使用CROSSJOIN實(shí)現(xiàn)交叉連接使用CROSSJOIN關(guān)鍵字，可以實(shí)現(xiàn)兩個(gè)表的交叉連接，所得到的結(jié)果將是這兩個(gè)表中各行數(shù)據(jù)的所有組合，即這兩個(gè)表所有數(shù)據(jù)行的笛卡兒積。交叉連接與簡單連接操作非常相似，不同的是，使用交叉連接時(shí)，在FROM子句中多個(gè)表名之間不是用逗號(hào)，而是使用CROSSJOIN關(guān)鍵字隔開。另外，在交叉連接中不需要使用關(guān)鍵字ON限定連接條件，但是可以添加WHERE子句設(shè)置連接條件?！纠?.29】使用交叉連接，查詢emp表和dept表中，部門編號(hào)為10的員工信息和部門信息，如下：參見教材P190338.3.6使用UNION操作符獲取兩個(gè)結(jié)果集的并集使用UNION操作符的語法如下：select_statementUNION[ALL]select_statement [UNION[ALL]select_statement][...]其中，select_statement是查詢的SELECT語句；ALL選項(xiàng)表示將所有行合并到結(jié)果集中，不指定該項(xiàng)，則只保留重復(fù)行中的一行。UNION操作符含義如圖8-1所示。348.3.6使用UNION操作符獲取兩個(gè)結(jié)果集的并集【例8.30】使用UNIONALL操作符，對(duì)scott用戶的emp表進(jìn)行操作，獲得員工編號(hào)大于7800或者所在部門編號(hào)為10的員工信息。使用ORDERBY語句將結(jié)果集按照deptno列升序排列輸出。具體如下：參見教材P191【例8.31】使用UNION操作符，但是不指定ALL關(guān)鍵字，獲得員工編號(hào)大于7800或者所在部門編號(hào)為10的員工信息。具體如下：參見教材P191358.3.7使用INTERSECT操作符獲取兩個(gè)結(jié)果集的交集使用INTERSECT操作符，獲取結(jié)果集的公共行，也稱為獲取結(jié)果集的交集。INTERSECT操作符的使用語法