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

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