Oracle數(shù)據(jù)庫應(yīng)用教程子查詢與高級查詢

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