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

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

2、果作為外外部主查查詢的查查找條件件。38.1.1子子查詢詢的類型型在子查詢詢中可以以使用兩兩種比較較操作符符單單行操作作符和多多行操作作符。單行操作作符：例例如=、=、=、!=。多行操作作符：例例如ALL、ANY、IN、EXISTS?？梢园炎幼硬樵兎址譃閮煞N種類型：單行子查查詢和多多行子查查詢。另外，子子查詢還還有下面面3種子子類型，這3種種子類型型可以返返回一行行或多行行查詢結(jié)結(jié)果。多列子查查詢：向向外部的的SQL語句返返回多列列。關(guān)聯(lián)子查查詢：引引用外部部的SQL語句句中的一一列或多多列。在在關(guān)聯(lián)子子查詢中中，可以以使用EXISTS和和NOT EXISTS操作作符。嵌套子查查詢：在在子查詢

3、詢中包含含有子查查詢。指定子查查詢時(shí)，需要注注意以下下幾點(diǎn)：子查詢需需要使用用括號(hào)() 括起起來。子查詢要要放在比比較操作作符的右右邊。當(dāng)子查詢詢的返回回值是一一個(gè)集合合而不是是一個(gè)值值時(shí)，不不能使用用單行操操作符，而必須須根據(jù)需需要使用用ANY、IN、ALL或EXISTS等等操作符符。48.1.2在在WHERE子句中中使用子子查詢1使用用子查詢詢在WHERE子子句中使使用子查查詢的一一般用法法形式如如下：SELECTcolumn_listFROMtable_name WHERE expressionoperator(SELECTcolumn_nameFROMtable_name WHERE

4、conditionGROUPBYexpHAVING having);其中，在在外部SELECT語語句的WHERE子句句中，expression用來指指定一個(gè)個(gè)表達(dá)式式，也可可以是表表中的一一列；operator可以以是單行行和多行行操作符符；()中的內(nèi)內(nèi)容表示示子查詢詢內(nèi)容。58.1.2在在WHERE子句中中使用子子查詢2不能使使用ORDERBY子句在子查詢詢的SELECT語句中，可以使使用FROM子句、WHERE子句、GROUPBY子句和HAVING子句等，但是有有些情況況下不能能使用ORDERBY子句，例例如在WHERE子句中使使用子查查詢時(shí)，子查詢詢語句中中就不能能使用ORDERBY子句

5、。68.1.3在在HAVING子句句中使用用子查詢詢在SELECT語句中中使用HAVING子子句，可可以實(shí)現(xiàn)現(xiàn)對數(shù)據(jù)據(jù)進(jìn)行分分組過濾濾。在HAVING子子句中，如果使使用子查查詢，那那么就可可以實(shí)現(xiàn)現(xiàn)對子查查詢返回回的結(jié)果果根據(jù)分分組進(jìn)行行過濾。【例8.3】對scott用用戶的emp表表進(jìn)行檢檢索，在在HAVING子句中中使用子子查詢。獲取哪哪些部門門的員工工平均工工資小于于全體員員工的平平均工資資。具體體如下：SQL SELECTdeptno,AVG(sal)FROM scott.empGROUPBYdeptno2HAVINGAVG(sal)、 SELECTempno, ename ,sal

6、 ,deptno FROMscott.emp2WHEREsal(3SELECT MAX(AVG(sal)FROMscott.empWHEREdeptno IN (4SELECTdeptnoFROMscott.dept5WHERE locIN(NEWYORK, CHICAGO)6GROUPBYdeptno );EMPNOENAMESALDEPTNO-7566JONES2975207788SCOTT3000207839KING5000107902FORD300020168.1.10在在UPDATE和和DELETE語句中中使用子子查詢1在UPDATE語語句中使使用子查查詢在UPDATE語句中中使用子

7、子查詢，可以將將子查詢詢返回的的結(jié)果賦賦值給需需要更新新的列?！纠?.17】將員工編編號(hào)為7839的員工工的工資資設(shè)置為為平均工工資，如如下：SQL UPDATEscott.empSET sal= (2SELECT AVG(sal) FROMscott.emp )3WHEREempno= 7839;已更新1行行。2在DELETE語語句中使使用子查查詢在DELETE語句中中使用子子查詢，可以根根據(jù)子查查詢返回回的結(jié)果果刪除指指定的行行?！纠?.18】刪除工作作地點(diǎn)在在NEW YORK的的所有員員工信息息，如下下：SQL DELETEFROM scott.empWHEREdeptnoIN(2SEL

8、ECT deptnoFROM scott.deptWHEREloc =NEWYORK);已刪除3行。178.2實(shí)驗(yàn)指導(dǎo)導(dǎo)獲取借閱閱次數(shù)最最多的前前5本圖書實(shí)驗(yàn)指導(dǎo)導(dǎo)8-1：獲取取借閱次次數(shù)最多多的前5本圖書書由于需要要顯示圖圖書信息息，所以以從圖書書表book中中進(jìn)行檢檢索；然然后結(jié)合合借書表表lend，獲獲得每本本書的借借閱情況況；最后后按照每每本書的的借閱次次數(shù)降序序排列，獲得前前5本圖圖書信息息即可。具體如如下：參見教材材P182上述語句句中，在在最外層層SELECT語句的的WHERE子子句中使使用了子子查詢，并且使使用IN操作符符指定匹匹配查詢詢；第3行的SELECT語語句中，在FRO

9、M子子句中使使用子查查詢，通通過子查查詢獲得得分組和和排序之之后的bookid；最后在在WHERE子子句中指指定rownum列小小于等于于5，指指定排序序之后的的前5行行記錄。188.3高高級級查查 詢檢索數(shù)據(jù)據(jù)時(shí)，通通過各個(gè)個(gè)表之間間共同列列的關(guān)聯(lián)聯(lián)性，可可以查詢詢存放在在多個(gè)表表中的不不同實(shí)體體的信息息。如果在查查詢時(shí)需需要對多多個(gè)表進(jìn)進(jìn)行操作作，并且且指定多多個(gè)表的的連接關(guān)關(guān)系，則則該查詢詢就稱為為高級查查詢，也也可以稱稱為連接接查詢。198.3.1使使用等等號(hào)(=)實(shí)現(xiàn)現(xiàn)多個(gè)表表的簡單單連接在連接查查詢中，如果僅僅僅通過過SELECT子句和和FROM子句句連接多多個(gè)表，那么查查詢的結(jié)結(jié)

10、果將是是一個(gè)通通過笛卡卡兒積所所生成的的表?！纠?.19】使用SELECT子句句和FROM子子句，從從scott用用戶的emp表表和dept表表中檢索索數(shù)據(jù)，如果不不指定檢檢索條件件，將得得到56行記錄錄。具體體如下：參見教材材P183由于scott.emp表中中有14行記錄錄，scott.dept表表中有4行記錄錄，所以以笛卡兒兒積所生生成的表表一共有有56(14*4 =56)行記錄錄。208.3.2使使用表表的別名名設(shè)置表的的別名，只需要要在FROM子子句中引引用該表表時(shí)，將將表別名名跟在表表的實(shí)際際名稱后后面即可可。表別別名和表表的實(shí)際際名稱之之間使用用空格進(jìn)進(jìn)行分隔隔?！纠?.21】使

11、用表別別名的方方式，重重寫例8.20中所示示的語句句。為scott.emp表表設(shè)置表表別名為為e，為為scott.dept表設(shè)設(shè)置表別別名為d。語句句如下：SQL SELECTempno, ename,sal,e.deptno, d.deptno ,dname2FROM scott.empe ,scott.deptd3WHEREe.deptno= d.deptno;218.3.3使使用INNERJOIN實(shí)現(xiàn)多多個(gè)表的的內(nèi)連接接在FROM子句句中，使使用JOIN連連接的語語法形式式如下：FROM join_table1 join_typejoin_table2 ON (join_conditio

12、n) join_type.ONjoin_condition,.語法說明明如下。join_table1、join_table2：參參與連接接操作的的表名。join_type：連接類類型，連連接類型型有INNER JOIN(內(nèi)連接接)、OUTERJOIN(外連連接)和和CROSSJOIN(交交叉連接接)。join_condition：連連接條件件，由被被連接表表中的列列和比較較運(yùn)算符符、邏輯輯運(yùn)算符符等構(gòu)成成。可以以使用多多組join_type ON join_condition 子子句，實(shí)實(shí)現(xiàn)多個(gè)個(gè)表的連連接。228.3.3使使用INNERJOIN實(shí)現(xiàn)多多個(gè)表的的內(nèi)連接接1等值值連接所謂等值值連

13、接，是指在在連接條條件中使使用等于于(=)運(yùn)算符符比較被被連接的的值，也也就是通通過相等等的列值值連接起起來的查查詢。2不等等連接所謂不等等連接，就是在在連接條條件中使使用除等等號(hào)(=)外的的其他比比較運(yùn)算算符，構(gòu)構(gòu)成非等等值連接接查詢?？梢允故褂玫谋缺容^運(yùn)算算符包括括：(大于)、=(大于于等于)、=(小于于等于)、(不等等于)、!=(不等于于)、LIKE、IN和BETWEEN等等。238.3.3使使用INNERJOIN實(shí)現(xiàn)多多個(gè)表的的內(nèi)連接接3自然然連接自然連接接(NATURALJOIN)是是在兩個(gè)個(gè)表中尋尋找列名名和數(shù)據(jù)據(jù)類型都都相同的的字段，通過相相同的字字段將兩兩個(gè)表連連接在一一起，并

14、并返回所所有符合合條件的的結(jié)果。4使用用USING關(guān)關(guān)鍵字簡簡化連接接SQL/92標(biāo)標(biāo)準(zhǔn)可以以使用USING關(guān)鍵鍵字來簡簡化連接接查詢，但是只只有在查查詢滿足足下面兩兩個(gè)條件件時(shí)，才才能使用用USING關(guān)關(guān)鍵字進(jìn)進(jìn)行簡化化：查詢必須須是等值值連接。等值連接接中的列列必須具具有相同同的名稱稱和數(shù)據(jù)據(jù)類型。248.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

15、子句和和SELECT子句中中，都不不能為deptno列列指定表表名或表表別名。(2)如如果在在連接查查詢時(shí)使使用了兩兩個(gè)表中中相同的的多個(gè)列列，那么么就可以以在USING子句中中指定多多個(gè)列名名，形式式如下：參見教材材P187(3)如如果對對多個(gè)表表進(jìn)行檢檢索，那那么就必必須多次次使用USING關(guān)鍵鍵字進(jìn)行行指定，形式如如下：參見教材材P187258.3.4使使用OUTERJOIN實(shí)現(xiàn)多多個(gè)表的的外連接接對于外連連接，Oracle中中可以使使用加號(hào)號(hào)(+)來表示示，也可可以使用用LEFT、RIGHT和FULL OUTER JOIN關(guān)關(guān)鍵字。外連接可可以分為為下面這這3類：左外連接接(LEFTO

16、UTERJOIN或LEFT JOIN)右外連接接(RIGHT OUTER JOIN或或RIGHTJOIN)全外連接接(FULLOUTERJOIN或FULL JOIN)268.3.4使使用OUTERJOIN實(shí)現(xiàn)多多個(gè)表的的外連接接1左外外連接左外連接接是在檢檢索結(jié)果果中除了了顯示滿滿足連接接條件的的行外，還顯示示JOIN關(guān)鍵鍵字左側(cè)側(cè)表中所所有滿足足檢索條條件的行行。2右外外連接右外連接接是在結(jié)結(jié)果中除除了顯示示滿足連連接條件件的行外外，還顯顯示JOIN右右側(cè)表中中所有滿滿足檢索索條件的的行。3全外外連接全外連接接是在結(jié)結(jié)果中除除了顯示示滿足連連接條件件的行外外，還顯顯示JOIN兩兩側(cè)表中中所有

17、滿滿足檢索索條件的的行。278.3.5使使用CROSSJOIN實(shí)現(xiàn)交交叉連接接使用CROSS JOIN關(guān)關(guān)鍵字，可以實(shí)實(shí)現(xiàn)兩個(gè)個(gè)表的交交叉連接接，所得得到的結(jié)結(jié)果將是是這兩個(gè)個(gè)表中各各行數(shù)據(jù)據(jù)的所有有組合，即這兩兩個(gè)表所所有數(shù)據(jù)據(jù)行的笛笛卡兒積積。交叉連接接與簡單單連接操操作非常常相似，不同的的是，使使用交叉叉連接時(shí)時(shí)，在FROM子句中中多個(gè)表表名之間間不是用用逗號(hào)，而是使使用CROSS JOIN關(guān)關(guān)鍵字隔隔開。另另外，在在交叉連連接中不不需要使使用關(guān)鍵鍵字ON限定連連接條件件，但是是可以添添加WHERE子句設(shè)設(shè)置連接接條件?！纠?.29】使用交叉叉連接，查詢emp表表和dept表表中，部部門

18、編號(hào)號(hào)為10的員工工信息和和部門信信息，如如下：參見教材材P190288.3.6使使用UNION操作作符獲取取兩個(gè)結(jié)結(jié)果集的的并集使用UNION操作符符的語法法如下：select_statement UNION ALL select_statementUNIONALLselect_statement .其中，select_statement是查詢詢的SELECT語句句；ALL選項(xiàng)項(xiàng)表示將將所有行行合并到到結(jié)果集集中，不不指定該該項(xiàng)，則則只保留留重復(fù)行行中的一一行。UNION操作作符含義義如圖8-1所所示。298.3.6使使用UNION操作作符獲取取兩個(gè)結(jié)結(jié)果集的的并集【例8.30】使用UNIO

19、N ALL操作作符，對對scott用用戶的emp表表進(jìn)行操操作，獲獲得員工工編號(hào)大大于7800或或者所在在部門編編號(hào)為10的員員工信息息。使用用ORDERBY語語句將結(jié)結(jié)果集按按照deptno列升升序排列列輸出。具體如如下：參見教材材P191【例8.31】使用UNION操作符符，但是是不指定定ALL關(guān)鍵字字，獲得得員工編編號(hào)大于于7800或者者所在部部門編號(hào)號(hào)為10的員工工信息。具體如如下：參見教材材P191308.3.7使使用INTERSECT操操作符獲獲取兩個(gè)個(gè)結(jié)果集集的交集集使用INTERSECT操作作符，獲獲取結(jié)果果集的公公共行，也稱為為獲取結(jié)結(jié)果集的的交集。INTERSECT操作符符的使用用語法同同UNION，只是操操作符不不再是UNIONALL，而而是INTERSECT。INTERSECT操操作符的的含義如如圖8-2所示示。318.3.8使使用MINUS操作作符獲取取兩個(gè)結(jié)結(jié)果集的的差集SQL語語言中的的MINUS集集合運(yùn)算算，表示示獲得給給定集合合之間的的差異，也就意意味著所所得到的的結(jié)果集集中，其其中的元元素僅存存在于前前一個(gè)集集合中，而不存存在于另另