1、第8章 子查詢與高級(jí)查詢,本章要點(diǎn)： 掌握在WHERE子句中使用子查詢。 掌握在HAVING子句中使用子查詢。 熟練掌握使用IN、ANY和ALL操作符實(shí)現(xiàn)子查詢。 熟練掌握關(guān)聯(lián)子查詢。 熟練掌握嵌套子查詢。 掌握簡(jiǎn)單連接。 熟練掌握多個(gè)表之間的內(nèi)連接。 熟練掌握多個(gè)表之間的外連接。 了解多個(gè)表之間的交叉連接。 掌握使用集合操作符實(shí)現(xiàn)集合查詢。,8.1 子 查 詢,在外部的SELECT、UPDATE或DELETE語句內(nèi)部使用SELECT語句，這個(gè)內(nèi)部SELECT語句稱為子查詢(Subquery)。 使用子查詢，主要是將子查詢的結(jié)果作為外部主查詢的查找條件。,8.1.1 子查詢的類型,在子查詢中可

2、以使用兩種比較操作符單行操作符和多行操作符。 單行操作符：例如=、=、!=。 多行操作符：例如ALL、ANY、IN、EXISTS。 可以把子查詢分為兩種類型： 單行子查詢和多行子查詢。 另外，子查詢還有下面3種子類型，這3種子類型可以返回一行或多行查詢結(jié)果。 多列子查詢：向外部的SQL語句返回多列。 關(guān)聯(lián)子查詢：引用外部的SQL語句中的一列或多列。在關(guān)聯(lián)子查詢中，可以使用EXISTS和NOT EXISTS操作符。 嵌套子查詢：在子查詢中包含有子查詢。 指定子查詢時(shí)，需要注意以下幾點(diǎn)： 子查詢需要使用括號(hào) ( ) 括起來。 子查詢要放在比較操作符的右邊。 當(dāng)子查詢的返回值是一個(gè)集合而不是一個(gè)值時(shí)

3、，不能使用單行操作符，而必須根據(jù)需要使用ANY、IN、ALL或EXISTS等操作符。,8.1.2 在WHERE子句中使用子查詢,1使用子查詢 在WHERE子句中使用子查詢的一般用法形式如下： SELECT column_list FROM table_name WHERE expression operator ( SELECT column_name FROM table_name WHERE condition GROUP BY exp HAVING having); 其中，在外部SELECT語句的WHERE子句中，expression用來指定一個(gè)表達(dá)式，也可以是表中的一列；operato

4、r可以是單行和多行操作符；()中的內(nèi)容表示子查詢內(nèi)容。,8.1.2 在WHERE子句中使用子查詢,2不能使用ORDER BY子句 在子查詢的SELECT語句中，可以使用FROM子句、WHERE子句、GROUP BY子句和HAVING子句等，但是有些情況下不能使用ORDER BY子句，例如在WHERE子句中使用子查詢時(shí)，子查詢語句中就不能使用ORDER BY子句。,8.1.3 在HAVING子句中使用子查詢,在SELECT語句中使用HAVING子句，可以實(shí)現(xiàn)對(duì)數(shù)據(jù)進(jìn)行分組過濾。在HAVING子句中，如果使用子查詢，那么就可以實(shí)現(xiàn)對(duì)子查詢返回的結(jié)果根據(jù)分組進(jìn)行過濾。 【例8.3】對(duì)scott用戶的

5、emp表進(jìn)行檢索，在HAVING子句中使用子查詢。獲取哪些部門的員工平均工資小于全體員工的平均工資。具體如下： SQL SELECT deptno , AVG(sal) FROM scott.emp GROUP BY deptno 2 HAVING AVG(sal) ( 3 SELECT AVG(sal) FROM scott.emp ); DEPTNO AVG(SAL) - - 30 1566.66667,8.1.4 使用IN操作符實(shí)現(xiàn)指定匹配查詢,1使用IN操作符 IN操作符用來檢查在一個(gè)值列表中是否包含指定的值。這個(gè)值列表可以是子查詢的返回結(jié)果。 2使用NOT IN操作符 NOT IN操

6、作符用來檢查在一個(gè)值列表中是否不包含指定的值，NOT IN執(zhí)行的操作正好與IN在邏輯上相反。 3常見的操作錯(cuò)誤 多行子查詢可以返回多行記錄，如果接收子查詢結(jié)果的操作符是單行操作符，那么在執(zhí)行語句時(shí)，可能會(huì)出現(xiàn)錯(cuò)誤提示。,8.1.5 使用ANY操作符實(shí)現(xiàn)任意匹配查詢,在進(jìn)行多行子查詢時(shí)，使用ANY操作符，用來將一個(gè)值與一個(gè)列表中的所有值進(jìn)行比較，這個(gè)值只需要匹配列表中的一個(gè)值即可，然后將滿足條件的數(shù)據(jù)返回。其中，值列表可以是子查詢的返回結(jié)果。 在使用ANY操作符之前，必須使用一個(gè)單行操作符，例如=、=等。 【例8.7】對(duì)scott用戶的emp表進(jìn)行操作，獲得工資大于任意一個(gè)部門的平均工資的員工信

7、息，如下： 參見教材P176,8.1.6 使用ALL操作符實(shí)現(xiàn)全部匹配查詢,在進(jìn)行子查詢時(shí)，使用ALL操作符，用來將一個(gè)值與一個(gè)列表中的所有值進(jìn)行比較，這個(gè)值需要匹配列表中的所有值，然后將滿足條件的數(shù)據(jù)返回。其中，值列表可以是子查詢的返回結(jié)果。 在使用ALL操作符之前，必須使用一個(gè)單行操作符，例如=、=等。 【例8.8】對(duì)scott用戶的emp表進(jìn)行操作，獲得工資大于所有部門的平均工資的員工信息，如下： 參見教材P176,8.1.7 實(shí)現(xiàn)多列子查詢,多列子查詢則是指返回多列數(shù)據(jù)的子查詢語句。當(dāng)多列子查詢返回單行數(shù)據(jù)時(shí)，在WHERE子句中可以使用單行操作符；返回多行數(shù)據(jù)時(shí)，在WHERE子句中必須

8、使用多行操作符。 使用子查詢比較多個(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í)匹配。,8.1.7 實(shí)現(xiàn)多列子查詢,【例8.9】對(duì)scott用戶的emp表進(jìn)行操作，使用成對(duì)比較的方式，獲得每個(gè)部門中工資最低的員工信息，如下： 參見教材P177 【例8.10】使用非成對(duì)比較的方式，并且指定AND關(guān)鍵字，獲得每個(gè)部門中工資最低的員工信息，如下： 參見教材P177,8.1.8 實(shí)

9、現(xiàn)關(guān)聯(lián)子查詢,關(guān)聯(lián)子查詢會(huì)引用外部查詢中的一列或多列，這種子查詢之所以被稱為關(guān)聯(lián)子查詢，是因?yàn)樗拇_與外部語句相關(guān)。 【例8.11】查詢各個(gè)部門中，哪些員工的工資低于其所在部門的平均工資，如下： 參見教材P178,8.1.8 實(shí)現(xiàn)關(guān)聯(lián)子查詢,1使用EXISTS操作符 在關(guān)聯(lián)子查詢中可以使用EXISTS或NOT EXISTS操作符。其中，EXISTS操作符用于檢查子查詢所返回的行是否存在，它可以在非關(guān)聯(lián)子查詢中使用，但是更常用于關(guān)聯(lián)子查詢。 2使用NOT EXISTS操作符 在執(zhí)行的操作邏輯上，NOT EXISTS操作符的作用與EXISTS操作符相反。在需要檢查數(shù)據(jù)行中是否不存在子查詢返回的結(jié)果

10、時(shí)，就可以使用NOT EXISTS。,8.1.8 實(shí)現(xiàn)關(guān)聯(lián)子查詢,3EXISTS與IN的比較 在使用NOT EXISTS和NOT IN時(shí)，如果一個(gè)值列表中包含有空值，NOT EXISTS返回TRUE；而NOT IN則返回FALSE。 【例8.14】使用NOT EXISTS操作符，檢索最高領(lǐng)導(dǎo)人員的有關(guān)信息，也就是該員工沒有上級(jí)領(lǐng)導(dǎo)，對(duì)應(yīng)的mgr列的值為NULL。具體如下： 參見教材P179 【例8.15】使用NOT IN操作符，重寫上述語句，如下： 參見教材P180,8.1.9 實(shí)現(xiàn)嵌套子查詢,所謂嵌套子查詢，是指在子查詢內(nèi)部使用其他子查詢。嵌套子查詢的嵌套層次最多為255層。大多數(shù)情況下，嵌

11、套子查詢都在外層子查詢的WHERE子句中。 【例8.16】已知工作地點(diǎn)在NEW YORK和CHICAGO的兩個(gè)部門，要求根據(jù)這兩個(gè)部門的平均工資中的最大值，獲取工資大于這個(gè)最大值的員工信息，如下： SQL SELECT empno , ename , sal , deptno FROM scott.emp 2 WHERE sal ( 3 SELECT MAX(AVG(sal) FROM scott.emp WHERE deptno IN ( 4 SELECT deptno FROM scott.dept 5 WHERE loc IN (NEW YORK , CHICAGO) ) 6 GROUP

12、 BY deptno ); EMPNOENAME SAL DEPTNO - 7566JONES 2975 20 7788SCOTT 3000 20 7839KING 5000 10 7902FORD 3000 20,8.1.10 在UPDATE和DELETE語句中使用子查詢,1在UPDATE語句中使用子查詢 在UPDATE語句中使用子查詢，可以將子查詢返回的結(jié)果賦值給需要更新的列。 【例8.17】將員工編號(hào)為7839的員工的工資設(shè)置為平均工資，如下： SQL UPDATE scott.emp SET sal = ( 2 SELECT AVG(sal) FROM scott.emp ) 3 WH

13、ERE empno = 7839 ; 已更新 1 行。 2在DELETE語句中使用子查詢 在DELETE語句中使用子查詢，可以根據(jù)子查詢返回的結(jié)果刪除指定的行。 【例8.18】刪除工作地點(diǎn)在NEW YORK的所有員工信息，如下： SQL DELETE FROM scott.emp WHERE deptno IN( 2 SELECT deptno FROM scott.dept WHERE loc = NEW YORK); 已刪除3行。,8.2 實(shí)驗(yàn)指導(dǎo)獲取借閱次數(shù)最多的前5本圖書,實(shí)驗(yàn)指導(dǎo)8-1：獲取借閱次數(shù)最多的前5本圖書 由于需要顯示圖書信息，所以從圖書表book中進(jìn)行檢索；然后結(jié)合借書表

14、lend，獲得每本書的借閱情況；最后按照每本書的借閱次數(shù)降序排列，獲得前5本圖書信息即可。具體如下： 參見教材P182 上述語句中，在最外層SELECT語句的WHERE子句中使用了子查詢，并且使用IN操作符指定匹配查詢；第3行的SELECT語句中，在FROM子句中使用子查詢，通過子查詢獲得分組和排序之后的bookid；最后在WHERE子句中指定rownum列小于等于5，指定排序之后的前5行記錄。,8.3 高 級(jí) 查 詢,檢索數(shù)據(jù)時(shí)，通過各個(gè)表之間共同列的關(guān)聯(lián)性，可以查詢存放在多個(gè)表中的不同實(shí)體的信息。 如果在查詢時(shí)需要對(duì)多個(gè)表進(jìn)行操作，并且指定多個(gè)表的連接關(guān)系，則該查詢就稱為高級(jí)查詢，也可以稱

15、為連接查詢。,8.3.1 使用等號(hào)(=)實(shí)現(xiàn)多個(gè)表的簡(jiǎn)單連接,在連接查詢中，如果僅僅通過SELECT子句和FROM子句連接多個(gè)表，那么查詢的結(jié)果將是一個(gè)通過笛卡兒積所生成的表。 【例8.19】使用SELECT子句和FROM子句，從scott用戶的emp表和dept表中檢索數(shù)據(jù)，如果不指定檢索條件，將得到56行記錄。具體如下： 參見教材P183 由于scott.emp表中有14行記錄，scott.dept表中有4行記錄，所以笛卡兒積所生成的表一共有56(14 * 4 = 56)行記錄。,8.3.2 使用表的別名,設(shè)置表的別名，只需要在FROM子句中引用該表時(shí)，將表別名跟在表的實(shí)際名稱后面即可。表

16、別名和表的實(shí)際名稱之間使用空格進(jìn)行分隔。 【例8.21】使用表別名的方式，重寫例8.20中所示的語句。為scott.emp表設(shè)置表別名為e，為scott.dept表設(shè)置表別名為d。語句如下： SQL SELECT empno , ename, sal , e.deptno , d.deptno , dname 2 FROM scott.emp e , scott.dept d 3 WHERE e.deptno = d.deptno;,8.3.3 使用INNER JOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接,在FROM子句中，使用JOIN連接的語法形式如下： FROM join_table1 join_type

17、join_table2 ON ( join_condition ) join_type . ON join_condition , . 語法說明如下。 join_table1、join_table2：參與連接操作的表名。 join_type：連接類型，連接類型有INNER JOIN(內(nèi)連接)、OUTER JOIN(外連接)和CROSS JOIN(交叉連接)。 join_condition：連接條件，由被連接表中的列和比較運(yùn)算符、邏輯運(yùn)算符等構(gòu)成?？梢允褂枚嘟Mjoin_type ON join_condition 子句，實(shí)現(xiàn)多個(gè)表的連接。,8.3.3 使用INNER JOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接,

18、1等值連接 所謂等值連接，是指在連接條件中使用等于(=)運(yùn)算符比較被連接的值，也就是通過相等的列值連接起來的查詢。 2不等連接 所謂不等連接，就是在連接條件中使用除等號(hào)(=)外的其他比較運(yùn)算符，構(gòu)成非等值連接查詢?？梢允褂玫谋容^運(yùn)算符包括：(大于)、=(大于等于)、(不等于)、!=(不等于)、LIKE、IN和BETWEEN等。,8.3.3 使用INNER JOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接,3自然連接 自然連接(NATURAL JOIN)是在兩個(gè)表中尋找列名和數(shù)據(jù)類型都相同的字段，通過相同的字段將兩個(gè)表連接在一起，并返回所有符合條件的結(jié)果。 4使用USING關(guān)鍵字簡(jiǎn)化連接 SQL/92標(biāo)準(zhǔn)可以使用US

19、ING關(guān)鍵字來簡(jiǎn)化連接查詢，但是只有在查詢滿足下面兩個(gè)條件時(shí)，才能使用USING關(guān)鍵字進(jìn)行簡(jiǎn)化： 查詢必須是等值連接。 等值連接中的列必須具有相同的名稱和數(shù)據(jù)類型。,8.3.3 使用INNER JOIN實(shí)現(xiàn)多個(gè)表的內(nèi)連接,【例8.25】使用USING關(guān)鍵字，重寫例8.22中的語句，如下： 參見教材P187 使用USING關(guān)鍵字簡(jiǎn)化連接時(shí)，需要注意以下幾點(diǎn)。 (1)使用emp表和dept表中的deptno列進(jìn)行連接時(shí)，在USING子句和SELECT子句中，都不能為deptno列指定表名或表別名。 (2)如果在連接查詢時(shí)使用了兩個(gè)表中相同的多個(gè)列，那么就可以在USING子句中指定多個(gè)列名，形式如下

20、： 參見教材P187 (3)如果對(duì)多個(gè)表進(jìn)行檢索，那么就必須多次使用USING關(guān)鍵字進(jìn)行指定，形式如下： 參見教材P187,8.3.4 使用OUTER JOIN實(shí)現(xiàn)多個(gè)表的外連接,對(duì)于外連接，Oracle中可以使用加號(hào)(+)來表示，也可以使用LEFT、RIGHT和FULL OUTER JOIN關(guān)鍵字。 外連接可以分為下面這3類： 左外連接(LEFT OUTER JOIN或LEFT JOIN) 右外連接(RIGHT OUTER JOIN或RIGHT JOIN) 全外連接(FULL OUTER JOIN或FULL JOIN),8.3.4 使用OUTER JOIN實(shí)現(xiàn)多個(gè)表的外連接,1左外連接 左外

21、連接是在檢索結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN關(guān)鍵字左側(cè)表中所有滿足檢索條件的行。 2右外連接 右外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN右側(cè)表中所有滿足檢索條件的行。 3全外連接 全外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN兩側(cè)表中所有滿足檢索條件的行。,8.3.5 使用CROSS JOIN實(shí)現(xiàn)交叉連接,使用CROSS JOIN關(guān)鍵字，可以實(shí)現(xiàn)兩個(gè)表的交叉連接，所得到的結(jié)果將是這兩個(gè)表中各行數(shù)據(jù)的所有組合，即這兩個(gè)表所有數(shù)據(jù)行的笛卡兒積。 交叉連接與簡(jiǎn)單連接操作非常相似，不同的是，使用交叉連接時(shí)，在FROM子句中多個(gè)表名之間不是用逗號(hào)，而是使用

22、CROSS JOIN關(guān)鍵字隔開。另外，在交叉連接中不需要使用關(guān)鍵字ON限定連接條件，但是可以添加WHERE子句設(shè)置連接條件。 【例8.29】使用交叉連接，查詢emp表和dept表中，部門編號(hào)為10的員工信息和部門信息，如下： 參見教材P190,8.3.6 使用UNION操作符獲取兩個(gè)結(jié)果集的并集,使用UNION操作符的語法如下： select_statement UNION ALL select_statement UNION ALL select_statement . 其中，select_statement是查詢的SELECT語句；ALL選項(xiàng)表示將所有行合并到結(jié)果集中，不指定該項(xiàng)，則只保留

23、重復(fù)行中的一行。UNION操作符含義如圖8-1所示。,8.3.6 使用UNION操作符獲取兩個(gè)結(jié)果集的并集,【例8.30】使用UNION ALL操作符，對(duì)scott用戶的emp表進(jìn)行操作，獲得員工編號(hào)大于7800或者所在部門編號(hào)為10的員工信息。使用ORDER BY語句將結(jié)果集按照deptno列升序排列輸出。具體如下： 參見教材P191 【例8.31】使用UNION操作符，但是不指定ALL關(guān)鍵字，獲得員工編號(hào)大于7800或者所在部門編號(hào)為10的員工信息。具體如下： 參見教材P191,8.3.7 使用INTERSECT操作符獲取兩個(gè)結(jié)果集的交集,使用INTERSECT操作符，獲取結(jié)果集的公共行，也稱為獲取結(jié)果集的交集。INTERSECT操作符的使用語法同UNION