第6章高級查詢

第6章高級查詢本章概述在第一章介紹設(shè)計數(shù)據(jù)庫時，曾經(jīng)介紹過為了數(shù)據(jù)庫的規(guī)范化，將數(shù)據(jù)表進(jìn)行了分隔，這雖然損壞了信息的完整性，但消除了數(shù)據(jù)冗余、添入異常和刪除異常。在查詢數(shù)據(jù)時，為了獲取完整的信息就要將多個表連接起來。這也是關(guān)系數(shù)據(jù)庫的一個重要特性——表之間存關(guān)系。這種關(guān)系可以將兩個表的數(shù)據(jù)聯(lián)系在一起。多表查詢就是根據(jù)這種關(guān)系，實現(xiàn)從多個表中獲取數(shù)據(jù)還原信息。本章學(xué)習(xí)目標(biāo)理解什么是連接操作如何進(jìn)行簡單的連接操作使用表別名限定列掌握如何進(jìn)行內(nèi)連接、外連接和交叉連接理解結(jié)果集的并、交和差運算創(chuàng)建返回多行的子查詢創(chuàng)建返回單值的子查詢掌握嵌套子查詢的使用本章學(xué)習(xí)要點掌握在WHERE子句中使用子查詢。掌握在HAVING子句中使用子查詢。熟練掌握使用IN、ANY和ALL操作符實現(xiàn)子查詢。熟練掌握關(guān)聯(lián)子查詢。熟練掌握嵌套子查詢。掌握簡單連接。熟練掌握多個表之間的內(nèi)連接。熟練掌握多個表之間的外連接。了解多個表之間的交叉連接。掌握使用集合操作符實現(xiàn)集合查詢。本章內(nèi)容6.1子查詢6.2實驗演示——獲取工作經(jīng)歷次數(shù)最多的前5個人6.3高級查詢6.1子查詢在外部的SELECT、UPDATE或DELETE語句內(nèi)部使用SELECT語句，這個內(nèi)部SELECT語句稱為子查詢(Subquery)。使用子查詢，主要是將子查詢的結(jié)果作為外部主查詢的查找條件。6.1子查詢6.1.1子查詢的類型6.1.2在WHERE子句中使用子查詢6.1.3在HAVING子句中使用子查詢6.1.4使用IN操作符實現(xiàn)指定匹配查詢6.1.5使用ANY操作符實現(xiàn)任意匹配查詢6.1.6使用ALL操作符實現(xiàn)全部匹配查詢6.1.7實現(xiàn)多列子查詢6.1.8實現(xiàn)關(guān)聯(lián)子查詢6.1.9實現(xiàn)嵌套子查詢6.1.10在UPDATE和DELETE語句中使用子查詢在子查詢中可以使用兩種比較操作符——單行操作符和多行操作符。單行操作符：例如=、>、>=、<、<=、<>、!=。多行操作符：例如ALL、ANY、IN、EXISTS?？梢园炎硬樵兎譃閮煞N類型：單行子查詢和多行子查詢。另外，子查詢還有下面3種子類型，這3種子類型可以返回一行或多行查詢結(jié)果。多列子查詢：向外部的SQL語句返回多列。關(guān)聯(lián)子查詢：引用外部的SQL語句中的一列或多列。在關(guān)聯(lián)子查詢中，可以使用EXISTS和NOTEXISTS操作符。嵌套子查詢：在子查詢中包含有子查詢。指定子查詢時，需要注意以下幾點：子查詢需要使用括號()括起來。子查詢要放在比較操作符的右邊。當(dāng)子查詢的返回值是一個集合而不是一個值時，不能使用單行操作符，而必須根據(jù)需要使用ANY、IN、ALL或EXISTS等操作符。6.1.1子查詢的類型6.1.2在WHERE子句中使用子查詢1．使用子查詢在WHERE子句中使用子查詢的一般用法形式如下：SELECTcolumn_listFROMtable_nameWHEREexpressionoperator( SELECTcolumn_nameFROMtable_nameWHEREcondition GROUPBYexpHAVINGhaving);其中，在外部SELECT語句的WHERE子句中，expression用來指定一個表達(dá)式，也可以是表中的一列；operator可以是單行和多行操作符；()中的內(nèi)容表示子查詢內(nèi)容。6.1.2在WHERE子句中使用子查詢2．不能使用ORDERBY子句在子查詢的SELECT語句中，可以使用FROM子句、WHERE子句、GROUPBY子句和HAVING子句等，但是有些情況下不能使用ORDERBY子句，例如在WHERE子句中使用子查詢時，子查詢語句中就不能使用ORDERBY子句。如果確實需要使用ORDERBY子句對結(jié)果進(jìn)行排序，可以在外部查詢語句中使用該子句。6.1.3在HAVING子句中使用子查詢在SELECT語句中使用HAVING子句，可以實現(xiàn)對數(shù)據(jù)進(jìn)行分組過濾。在HAVING子句中，如果使用子查詢，那么就可以實現(xiàn)對子查詢返回的結(jié)果根據(jù)分組進(jìn)行過濾?！纠繉cott用戶的emp表進(jìn)行檢索，在HAVING子句中使用子查詢。獲取哪些部門的員工平均工資小于全體員工的平均工資。具體如下：SELECTdeptno,AVG(sal)FROMscott.empGROUPBYdeptnoHAVINGAVG(sal)<( SELECTAVG(sal)FROMscott.emp);6.1.4使用IN操作符實現(xiàn)指定匹配查詢1．使用IN操作符IN操作符用來檢查在一個值列表中是否包含指定的值。這個值列表可以是子查詢的返回結(jié)果。2．使用NOTIN操作符NOTIN操作符用來檢查在一個值列表中是否不包含指定的值，NOTIN執(zhí)行的操作正好與IN在邏輯上相反。3．常見的操作錯誤多行子查詢可以返回多行記錄，如果接收子查詢結(jié)果的操作符是單行操作符，那么在執(zhí)行語句時，可能會出現(xiàn)錯誤提示。6.1.5使用ANY操作符實現(xiàn)任意匹配查詢在進(jìn)行多行子查詢時，使用ANY操作符，用來將一個值與一個列表中的所有值進(jìn)行比較，這個值只需要匹配列表中的一個值即可，然后將滿足條件的數(shù)據(jù)返回。其中，值列表可以是子查詢的返回結(jié)果。在使用ANY操作符之前，必須使用一個單行操作符，例如=、>、<、<=等?！纠繉cott用戶的emp表進(jìn)行操作，獲得工資大于任意一個部門的平均工資的員工信息6.1.6使用ALL操作符實現(xiàn)全部匹配查詢在進(jìn)行子查詢時，使用ALL操作符，用來將一個值與一個列表中的所有值進(jìn)行比較，這個值需要匹配列表中的所有值，然后將滿足條件的數(shù)據(jù)返回。其中，值列表可以是子查詢的返回結(jié)果。在使用ALL操作符之前，必須使用一個單行操作符，例如=、>、<、<=等?！纠繉cott用戶的emp表進(jìn)行操作，獲得工資大于所有部門的平均工資的員工信息6.1.7實現(xiàn)多列子查詢多列子查詢則是指返回多列數(shù)據(jù)的子查詢語句。當(dāng)多列子查詢返回單行數(shù)據(jù)時，在WHERE子句中可以使用單行操作符；返回多行數(shù)據(jù)時，在WHERE子句中必須使用多行操作符。使用子查詢比較多個列的數(shù)據(jù)時，可以使用下面兩種方式。成對比較：要求多個列的數(shù)據(jù)必須同時匹配。非成對比較：通過指定連接關(guān)鍵字，例如AND或OR等，指定多個列的數(shù)據(jù)是否必須同時匹配。如果使用AND關(guān)鍵字，表示同時匹配，這樣就可以實現(xiàn)與成對比較同樣的結(jié)果；如果使用OR關(guān)鍵字，表示不必同時匹配。6.1.7實現(xiàn)多列子查詢【例】對scott用戶的emp表進(jìn)行操作，使用成對比較的方式，獲得每個部門中工資最低的員工信息.【例】使用非成對比較的方式，并且指定AND關(guān)鍵字，獲得每個部門中工資最低的員工信息.6.1.8實現(xiàn)關(guān)聯(lián)子查詢關(guān)聯(lián)子查詢會引用外部查詢中的一列或多列，這種子查詢之所以被稱為關(guān)聯(lián)子查詢，是因為它的確與外部語句相關(guān)?！纠坎樵兏鱾€部門中，哪些員工的工資低于其所在部門的平均工資.6.1.8實現(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ù)據(jù)行中是否不存在子查詢返回的結(jié)果時，就可以使用NOTEXISTS。 6.1.8實現(xiàn)關(guān)聯(lián)子查詢3．EXISTS與IN的比較在使用NOTEXISTS和NOTIN時，如果一個值列表中包含有空值，NOTEXISTS返回TRUE；而NOTIN則返回FALSE?！纠渴褂肗OTEXISTS操作符，檢索最高領(lǐng)導(dǎo)人員的有關(guān)信息，也就是該員工沒有上級領(lǐng)導(dǎo)，對應(yīng)的mgr列的值為NULL?！纠渴褂肗OTIN操作符，重寫上述語句.(沒有實現(xiàn)NOTEXISTS的功能)6.1.9實現(xiàn)嵌套子查詢所謂嵌套子查詢，是指在子查詢內(nèi)部使用其他子查詢。嵌套子查詢的嵌套層次最多為255層。大多數(shù)情況下，嵌套子查詢都在外層子查詢的WHERE子句中。【例】已知工作地點在NEWYORK和CHICAGO的兩個部門，要求根據(jù)這兩個部門的平均工資中的最大值，獲取工資大于這個最大值的員工信息，如下：SELECTempno,ename,sal,deptnoFROMscott.empWHEREsal>( SELECTMAX(AVG(sal))FROMscott.empWHEREdeptnoIN(

SELECTdeptnoFROMscott.dept WHERElocIN('NEWYORK','CHICAGO')) GROUPBYdeptno);6.1.10在UPDATE和DELETE語句中使用子查詢1．在UPDATE語句中使用子查詢在UPDATE語句中使用子查詢，可以將子查詢返回的結(jié)果賦值給需要更新的列?！纠繉T工編號為7839的員工的工資設(shè)置為平均工資，如下：UPDATEscott.empSETsal=( SELECTAVG(sal)FROMscott.emp)WHEREempno=7839; 2．在DELETE語句中使用子查詢在DELETE語句中使用子查詢，可以根據(jù)子查詢返回的結(jié)果刪除指定的行?！纠縿h除工作地點在NEWYORK的所有員工信息，如下：DELETEFROMscott.empWHEREdeptnoIN( SELECTdeptnoFROMscott.deptWHEREloc='NEWYORK');6.2實驗演示——獲取工作經(jīng)歷次數(shù)最多的前5個人實驗演示：從hr.emloyees和job_history表查詢先從表employees中進(jìn)行檢索基本信息；然后結(jié)合工作經(jīng)歷表jot_history，獲得每個人的工作經(jīng)歷；最后按照每個人的工作次數(shù)降序排列，獲得前5個人的信息即可。具體如下：Select*fromemployeeswhereemployee_idin(selectemployee_idfrom(selectemployee_idfromjob_historygroupbyemployee_idorderbycount(employee_id)desc)whererownum<=5);上述語句中，在最外層SELECT語句的WHERE子句中使用了子查詢，并且使用IN操作符指定匹配查詢；第2層的SELECT語句中，在FROM子句中使用子查詢，通過子查詢獲得分組和排序之后的employee_id；最后在WHERE子句中指定rownum列小于等于5，指定排序之后的前5行記錄。6.3高級查詢檢索數(shù)據(jù)時，通過各個表之間共同列的關(guān)聯(lián)性，可以查詢存放在多個表中的不同實體的信息。如果在查詢時需要對多個表進(jìn)行操作，并且指定多個表的連接關(guān)系，則該查詢就稱為高級查詢，也可以稱為連接查詢。6.3.1使用等號(=)實現(xiàn)多個表的簡單連接在連接查詢中，如果僅僅通過SELECT子句和FROM子句連接多個表，那么查詢的結(jié)果將是一個通過笛卡兒積所生成的表?！纠渴褂肧ELECT子句和FROM子句，從scott用戶的emp表和dept表中檢索數(shù)據(jù)，如果不指定檢索條件，將得到56行記錄。由于scott.emp表中有14行記錄，scott.dept表中有4行記錄，所以笛卡兒積所生成的表一共有56(14*4=56)行記錄。重寫上例，添加WHERE子句指定檢索條件，實現(xiàn)簡單連接。6.3.2使用表的別名設(shè)置表的別名，只需要在FROM子句中引用該表時，將表別名跟在表的實際名稱后面即可。表別名和表的實際名稱之間使用空格進(jìn)行分隔。【例】使用表別名的方式，重寫前例中所示的語句。為scott.emp表設(shè)置表別名為e，為scott.dept表設(shè)置表別名為d。語句如下：SELECTempno,ename,sal,e.deptno,d.deptno,dnameFROMscott.empe,scott.deptdWHEREe.deptno=d.deptno;6.3.3使用INNERJOIN實現(xiàn)多個表的內(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：連接條件，由被連接表中的列和比較運算符、邏輯運算符等構(gòu)成?？梢允褂枚嘟Mjoin_type…ONjoin_condition…子句，實現(xiàn)多個表的連接。6.3.3使用INNERJOIN實現(xiàn)多個表的內(nèi)連接1．等值連接所謂等值連接，是指在連接條件中使用等于(=)運算符比較被連接的值，也就是通過相等的列值連接起來的查詢。例：使用INNERJOIN連接兩個不同的表emp和dept，ON來設(shè)置連接條件，使用WHERE子句限制查詢范圍，檢索ACCOUNTING部門的員工信息。6.3.3使用INNERJOIN實現(xiàn)多個表的內(nèi)連接2．不等連接所謂不等連接，就是在連接條件中使用除等號(=)外的其他比較運算符，構(gòu)成非等值連接查詢?？梢允褂玫谋容^運算符包括：>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)、!=(不等于)、LIKE、IN和BETWEEN等。通過emp和salgrade表，查詢員工的工資等級。6.3.3使用INNERJOIN實現(xiàn)多個表的內(nèi)連接3．自然連接（在等值連接中把目標(biāo)列中重復(fù)的屬性列去掉）自然連接(NATURALJOIN)是在兩個表中尋找列名和數(shù)據(jù)類型都相同的字段，通過相同的字段將兩個表連接在一起，并返回所有符合條件的結(jié)果。例:使用INNERJOIN連接兩個不同的表emp和dept，ON來設(shè)置連接條件，使用WHERE子句限制查詢范圍，檢索ACCOUNTING部門的員工信息。6.3.3使用INNERJOIN實現(xiàn)多個表的內(nèi)連接4．使用USING關(guān)鍵字簡化連接SQL/92標(biāo)準(zhǔn)可以使用USING關(guān)鍵字來簡化連接查詢，但是只有在查詢滿足下面兩個條件時，才能使用USING關(guān)鍵字進(jìn)行簡化：查詢必須是等值連接。等值連接中的列必須具有相同的名稱和數(shù)據(jù)類型。6.3.3使用INNERJOIN實現(xiàn)多個表的內(nèi)連接【例】使用USING關(guān)鍵字，重寫前例中的語句.使用INNERJOIN連接兩個不同的表emp和dept，ON來設(shè)置連接條件，使用WHERE子句限制查詢范圍，檢索ACCOUNTING部門的員工信息。使用USING關(guān)鍵字簡化連接時，需要注意以下幾點。(1) 使用emp表和dept表中的deptno列進(jìn)行連接時，在USING子句和SELECT子句中，都不能為deptno列指定表名或表別名。(2) 如果在連接查詢時使用了兩個表中相同的多個列，那么就可以在USING子句中指定多個列名.(3) 如果對多個表進(jìn)行檢索，那么就必須多次使用USING關(guān)鍵字進(jìn)行指定.6.3.4使用OUTERJOIN實現(xiàn)多個表的外連接對于外連接，Oracle中可以使用加號(+)來表示，也可以使用LEFT、RIGHT和FULLOUTERJOIN關(guān)鍵字。外連接可以分為下面這3類：左外連接(LEFTOUTERJOIN或LEFTJOIN)右外連接(RIGHTOUTERJOIN或RIGHTJOIN)全外連接(FULLOUTERJOIN或FULLJOIN)6.3.4使用OUTERJOIN實現(xiàn)多個表的外連接1．左外連接左外連接是在檢索結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN關(guān)鍵字左側(cè)表中所有滿足檢索條件的行。使用左外連接，檢索emp和salgrade表，獲得員工的工資等級。為了顯示效果，先向emp表插入一行sal小于700的記錄。使用左連接查詢：Selecte.empno,e.ename,e.sal,d.gradefromscott.empeLEFTOUTERJOINscott.salgradedONe.salBETWEENd.losalandd.hisal等價于：Selecte.empno,e.ename,e.sal,d.gradefromscott.empe,scott.salgradedwheree.salBETWEENd.losalandd.hisal(+)6.3.4使用OUTERJOIN實現(xiàn)多個表的外連接2．右外連接右外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN右側(cè)表中所有滿足檢索條件的行。使用右外連接，檢索emp和dept表所包含的部門編號。Selectdistincte.deptno,d.deptnofromscott.empeRIGHTOUTERJOINscott.deptdONe.deptno=d.deptno等價于：Selectdistincte.deptno,d.deptnofromscott.empe,scott.deptdwheree.deptno=d.deptno(+)6.3.4使用OUTERJOIN實現(xiàn)多個表的外連接3．全外連接全外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN兩側(cè)表中所有滿足檢索條件的行。如：使用全外連接，檢索emp和dept表中所包含的部門編號Selectdistincte.deptno,d.deptnofromscott.empeFULLOUTERJOINscott.deptdONe.deptno=d.deptno6.3.5使用CROSSJOIN實現(xiàn)交叉連接使用CROSSJOIN關(guān)鍵字，可以實現(xiàn)兩個表的交叉連接，所得到的結(jié)果將是這兩個表中各行數(shù)據(jù)的所有組合，即這兩個表所有數(shù)據(jù)行的笛卡兒積。交叉連接與簡單連接操作非常相似，不同的是，使用交叉連接時，在FROM子句中多個表名之間不是用逗號，而是使用CROSSJOIN關(guān)鍵字隔開。另外，在交叉連接中不需要使用關(guān)鍵字ON限定連接條件，但是可