1第8章子查詢與高級查詢本章要點：掌握在WHERE子句中運用子查詢。掌握在HAVING子句中運用子查詢。熟練掌握運用IN、ANY和ALL操作符實現子查詢。熟練掌握關聯子查詢。熟練掌握嵌套子查詢。掌握簡單銜接。熟練掌握多個表之間的內銜接。熟練掌握多個表之間的外銜接。了解多個表之間的交叉銜接。掌握運用集合操作符實現集合查詢。28.1子查詢在外部的SELECT、UPDATE或DELETE語句內部運用SELECT語句，這個內部SELECT語句稱為子查詢(Subquery)。運用子查詢，主要是將子查詢的結果作為外部主查詢的查找條件。38.1.1子查詢的類型在子查詢中可以運用兩種比較操作符——單行操作符和多行操作符。單行操作符：例如=、>、>=、<、<=、<>、!=。多行操作符：例如ALL、ANY、IN、EXISTS?？梢园炎硬樵兎譃閮煞N類型：單行子查詢和多行子查詢。另外，子查詢還有下面3種子類型，這3種子類型可以前往一行或多行查詢結果。多列子查詢：向外部的SQL語句前往多列。關聯子查詢：援用外部的SQL語句中的一列或多列。在關聯子查詢中，可以運用EXISTS和NOTEXISTS操作符。嵌套子查詢：在子查詢中包含有子查詢。指定子查詢時，需求留意以下幾點：子查詢需求運用括號()括起來。子查詢要放在比較操作符的右邊。當子查詢的前往值是一個集合而不是一個值時，不能運用單行操作符，而必需根據需求運用ANY、IN、ALL或EXISTS等操作符。48.1.2在WHERE子句中運用子查詢1．運用子查詢在WHERE子句中運用子查詢的普通用法方式如下：SELECTcolumn_listFROMtable_nameWHEREexpressionoperator( SELECTcolumn_nameFROMtable_nameWHEREcondition GROUPBYexpHAVINGhaving);其中，在外部SELECT語句的WHERE子句中，expression用來指定一個表達式，也可以是表中的一列；operator可以是單行和多行操作符；()中的內容表示子查詢內容。58.1.2在WHERE子句中運用子查詢2．不能運用ORDERBY子句在子查詢的SELECT語句中，可以運用FROM子句、WHERE子句、GROUPBY子句和HAVING子句等，但是有些情況下不能運用ORDERBY子句，例如在WHERE子句中運用子查詢時，子查詢語句中就不能運用ORDERBY子句。68.1.3在HAVING子句中運用子查詢在SELECT語句中運用HAVING子句，可以實現對數據進展分組過濾。在HAVING子句中，假設運用子查詢，那么就可以實現對子查詢前往的結果根據分組進展過濾?！纠?.3】對scott用戶的emp表進展檢索，在HAVING子句中運用子查詢。獲取哪些部門的員工平均工資小于全體員工的平均工資。詳細如下：SQL>SELECTdeptno,AVG(sal)FROMscott.empGROUPBYdeptno2HAVINGAVG(sal)<(3 SELECTAVG(sal)FROMscott.emp);DEPTNO AVG(SAL)---------- -------------30 1566.6666778.1.4運用IN操作符實現指定匹配查詢1．運用IN操作符IN操作符用來檢查在一個值列表中能否包含指定的值。這個值列表可以是子查詢的前往結果。2．運用NOTIN操作符NOTIN操作符用來檢查在一個值列表中能否不包含指定的值，NOTIN執行的操作正好與IN在邏輯上相反。3．常見的操作錯誤多行子查詢可以前往多行記錄，假設接納子查詢結果的操作符是單行操作符，那么在執行語句時，能夠會出現錯誤提示。88.1.5運用ANY操作符實現恣意匹配查詢在進展多行子查詢時，運用ANY操作符，用來將一個值與一個列表中的一切值進展比較，這個值只需求匹配列表中的一個值即可，然后將滿足條件的數據前往。其中，值列表可以是子查詢的前往結果。在運用ANY操作符之前，必需運用一個單行操作符，例如=、>、<、<=等?！纠?.7】對scott用戶的emp表進展操作，獲得工資大于恣意一個部門的平均工資的員工信息，如下：參見教材P17698.1.6運用ALL操作符實現全部匹配查詢在進展子查詢時，運用ALL操作符，用來將一個值與一個列表中的一切值進展比較，這個值需求匹配列表中的一切值，然后將滿足條件的數據前往。其中，值列表可以是子查詢的前往結果。在運用ALL操作符之前，必需運用一個單行操作符，例如=、>、<、<=等?！纠?.8】對scott用戶的emp表進展操作，獲得工資大于一切部門的平均工資的員工信息，如下：參見教材P176108.1.7實現多列子查詢多列子查詢那么是指前往多列數據的子查詢語句。當多列子查詢前往單行數據時，在WHERE子句中可以運用單行操作符；前往多行數據時，在WHERE子句中必需運用多行操作符。運用子查詢比較多個列的數據時，可以運用下面兩種方式。成對比較：要求多個列的數據必需同時匹配。非成對比較：經過指定銜接關鍵字，例如AND或OR等，指定多個列的數據能否必需同時匹配。假設運用AND關鍵字，表示同時匹配，這樣就可以實現與成對比較同樣的結果；假設運用OR關鍵字，表示不用同時匹配。118.1.7實現多列子查詢【例8.9】對scott用戶的emp表進展操作，運用成對比較的方式，獲得每個部門中工資最低的員工信息，如下：參見教材P177【例8.10】運用非成對比較的方式，并且指定AND關鍵字，獲得每個部門中工資最低的員工信息，如下：參見教材P177128.1.8實現關聯子查詢關聯子查詢會援用外部查詢中的一列或多列，這種子查詢之所以被稱為關聯子查詢，是由于它確實與外部語句相關?！纠?.11】查詢各個部門中，哪些員工的工資低于其所在部門的平均工資，如下：參見教材P178138.1.8實現關聯子查詢1．運用EXISTS操作符在關聯子查詢中可以運用EXISTS或NOTEXISTS操作符。其中，EXISTS操作符用于檢查子查詢所前往的行能否存在，它可以在非關聯子查詢中運用，但是更常用于關聯子查詢。2．運用NOTEXISTS操作符在執行的操作邏輯上，NOTEXISTS操作符的作用與EXISTS操作符相反。在需求檢查數據行中能否不存在子查詢前往的結果時，就可以運用NOTEXISTS。 148.1.8實現關聯子查詢3．EXISTS與IN的比較在運用NOTEXISTS和NOTIN時，假設一個值列表中包含有空值，NOTEXISTS前往TRUE；而NOTIN那么前往FALSE?！纠?.14】運用NOTEXISTS操作符，檢索最高指點人員的有關信息，也就是該員工沒有上級指點，對應的mgr列的值為NULL。詳細如下：參見教材P179【例8.15】運用NOTIN操作符，重寫上述語句，如下：參見教材P180158.1.9實現嵌套子查詢所謂嵌套子查詢，是指在子查詢內部運用其他子查詢。嵌套子查詢的嵌套層次最多為255層。大多數情況下，嵌套子查詢都在外層子查詢的WHERE子句中?！纠?.16】知任務地點在NEWYORK和CHICAGO的兩個部門，要求根據這兩個部門的平均工資中的最大值，獲取工資大于這個最大值的員工信息，如下：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 20168.1.10在UPDATE和DELETE語句中運用子查詢1．在UPDATE語句中運用子查詢在UPDATE語句中運用子查詢，可以將子查詢前往的結果賦值給需求更新的列?！纠?.17】將員工編號為7839的員工的工資設置為平均工資，如下：SQL>UPDATEscott.empSETsal=(2 SELECTAVG(sal)FROMscott.emp)3WHEREempno=7839;已更新1行。 2．在DELETE語句中運用子查詢在DELETE語句中運用子查詢，可以根據子查詢前往的結果刪除指定的行。【例8.18】刪除任務地點在NEWYORK的一切員工信息，如下：SQL>DELETEFROMscott.empWHEREdeptnoIN(2 SELECTdeptnoFROMscott.deptWHEREloc='NEWYORK');已刪除3行。178.2實驗指點——獲取借閱次數最多的前5本圖書實驗指點8-1：獲取借閱次數最多的前5本圖書由于需求顯示圖書信息，所以從圖書表book中進展檢索；然后結合借書表lend，獲得每本書的借閱情況；最后按照每本書的借閱次數降序陳列，獲得前5本圖書信息即可。詳細如下：參見教材P182上述語句中，在最外層SELECT語句的WHERE子句中運用了子查詢，并且運用IN操作符指定匹配查詢；第3行的SELECT語句中，在FROM子句中運用子查詢，經過子查詢獲得分組和排序之后的bookid；最后在WHERE子句中指定rownum列小于等于5，指定排序之后的前5行記錄。188.3高級查詢檢索數據時，經過各個表之間共同列的關聯性，可以查詢存放在多個表中的不同實體的信息。假設在查詢時需求對多個表進展操作，并且指定多個表的銜接關系，那么該查詢就稱為高級查詢，也可以稱為銜接查詢。198.3.1運用等號(=)實現多個表的簡單銜接在銜接查詢中，假設僅僅經過SELECT子句和FROM子句銜接多個表，那么查詢的結果將是一個經過笛卡兒積所生成的表?！纠?.19】運用SELECT子句和FROM子句，從scott用戶的emp表和dept表中檢索數據，假設不指定檢索條件，將得到56行記錄。詳細如下：參見教材P183由于scott.emp表中有14行記錄，scott.dept表中有4行記錄，所以笛卡兒積所生成的表一共有56(14*4=56)行記錄。208.3.2運用表的別名設置表的別名，只需求在FROM子句中援用該表時，將表別名跟在表的實踐稱號后面即可。表別名和表的實踐稱號之間運用空格進展分隔?！纠?.21】運用表別名的方式，重寫例8.20中所示的語句。為scott.emp表設置表別名為e，為scott.dept表設置表別名為d。語句如下：SQL>SELECTempno,ename,sal,e.deptno,d.deptno,dname2FROMscott.empe,scott.deptd3WHEREe.deptno=d.deptno;218.3.3運用INNERJOIN實現多個表的內銜接在FROM子句中，運用JOIN銜接的語法方式如下：FROMjoin_table1join_typejoin_table2[ON(join_condition)] [join_type...ONjoin_condition,...]語法闡明如下。join_table1、join_table2：參與銜接操作的表名。join_type：銜接類型，銜接類型有INNERJOIN(內銜接)、OUTERJOIN(外銜接)和CROSSJOIN(交叉銜接)。join_condition：銜接條件，由被銜接表中的列和比較運算符、邏輯運算符等構成?？梢赃\用多組join_type…ONjoin_condition…子句，實現多個表的銜接。228.3.3運用INNERJOIN實現多個表的內銜接1．等值銜接所謂等值銜接，是指在銜接條件中運用等于(=)運算符比較被銜接的值，也就是經過相等的列值銜接起來的查詢。2．不等銜接所謂不等銜接，就是在銜接條件中運用除等號(=)外的其他比較運算符，構成非等值銜接查詢?？梢赃\用的比較運算符包括：>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)、!=(不等于)、LIKE、IN和BETWEEN等。238.3.3運用INNERJOIN實現多個表的內銜接3．自然銜接自然銜接(NATURALJOIN)是在兩個表中尋覓列名和數據類型都一樣的字段，經過一樣的字段將兩個表銜接在一同，并前往一切符合條件的結果。4．運用USING關鍵字簡化銜接SQL/92規范可以運用USING關鍵字來簡化銜接查詢，但是只需在查詢滿足下面兩個條件時，才干運用USING關鍵字進展簡化：查詢必需是等值銜接。等值銜接中的列必需具有一樣的稱號和數據類型。248.3.3運用INNERJOIN實現多個表的內銜接【例8.25】運用USING關鍵字，重寫例8.22中的語句，如下：參見教材P187運用USING關鍵字簡化銜接時，需求留意以下幾點。(1) 運用emp表和dept表中的deptno列進展銜接時，在USING子句和SELECT子句中，都不能為deptno列指定表名或表別名。(2) 假設在銜接查詢時運用了兩個表中一樣的多個列，那么就可以在USING子句中指定多個列名，方式如下：參見教材P187(3) 假設對多個表進展檢索，那么就必需多次運用USING關鍵字進展指定，方式如下：參見教材P187258.3.4運用OUTERJOIN實現多個表的外銜接對于外銜接，Oracle中可以運用加號(+)來表示，也可以運用LEFT、RIGHT和FULLOUTERJOIN關鍵字。外銜接可以分為下面這3類：左外銜接(LEFTOUTERJOIN或LEFTJOIN)右外銜接(RIGHTOUTERJOIN或RIGHTJOIN)全外銜接(FULLOUTERJOIN或FULLJOIN)268.3.4運用OUTERJOIN實現多個表的外銜接1．左外銜接左外銜接是在檢索結果中除了顯示滿足銜接條件的行外，還顯示JOIN關鍵字左側表中一切滿足檢索條件的行。2．右外銜接右外銜接是在結果中除了顯示滿足銜接條件的行外，還顯示JOIN右側表中一切滿足檢索條件的行。3．全外銜接全外銜接是在結果中除了顯示滿足銜接條件的行外，還顯示JOIN兩側表中一切滿足檢索條件的行。278.3.5運用CROSSJOIN實現交叉銜接運用CROSSJOIN關鍵字，可以實現兩個表的交叉銜接，所得到的結果將是這兩個表中各行數據的一切組合，即這兩個表一切數據行的笛卡兒積。交叉銜接與簡單銜接操作非常類似，不同的是，運用交叉銜接時，在FROM子句中多個表名之間不是用逗號，而是運用CROSSJOIN關鍵字隔開。另外，在交叉銜接中不需求運用關鍵字ON限定銜接條件，但是可以添加WHERE子句設置銜接條件?！纠?.29】運用交叉銜接，查詢emp表和dept表中，部門編號為10的員工信息和部門信息，如下：參見教材P190288.3.6運用UNION操作符獲取兩個結果集的并集運用UNION操作符的語法如下：select_statementUNION[ALL]select_statement [UNION[ALL]select_statement][...]其中，select_statement是查詢的SELECT語句；ALL選項表示將一切行合并到結果集中，不指定該項，那么只保管反復行中的一行。UNION操作符含義如圖8-1所示。298.3.6運用UNION操作符獲取兩個結果集的并集【例8.30】運用UNIONALL操作符，對scott用戶的emp表進展操作，獲得員工編號大于7800或者所在部門編號為10的員工信息。運用ORDERBY語句將結果集按照deptno列升序陳列輸出。詳細如下：參見教材P191【例8.31】運用UNION操作符，但是不指定ALL關鍵字，獲得員工編號大于7800或者所在部門編號為10的員工信息。詳細如下：參見教材P191308.3.7運用INTERSECT操作符獲取兩個結果集的交集運用INTERSECT操作符，獲取結果集的公共行，也稱為獲取結果集的交集。INTERSECT操作符的運用語法同UNION，只是操作符不再是UNION[ALL]，而是INTERSECT。INTERSECT操作符的含義如圖8-2所示。318.3.8運用MINUS操作符獲取兩個結果集的差集SQL