第七章高級查詢本章目標(biāo)多表查詢連接條件和連接類型連接查詢使用SQL/92語法子查詢分頁查詢多表查詢不同的數(shù)據(jù)存儲在不同的表中，通常要查詢多張表才能找到需要的數(shù)據(jù)語法：SELECT…FROM表1[別名],[表2[別名],]WHERE條件..;連接兩張表的查詢可以在FROM后面加兩張表，然后再指定兩張表的關(guān)聯(lián)關(guān)系首先指定查詢的兩張表，如下FROMEMP,DEPT再通過WHERE條件指定兩表的關(guān)系WHEREEMP.DEPTNO=DEPT.DEPTNOANDEMP.EMPNO=7369;說明通常情況下，是一個表的主鍵與另外一個表的外鍵相匹配；再指定員工的編號為7369，即EMPNO=7369表的別名EMP表和DEPT表相關(guān)字段會用在SELECT語句及WHERE子句上，可以給表起別名，提高代碼可讀性、降低書寫難度例如，改寫查詢語句SELECT*FROMEMPe,DEPTdWHEREe.DEPTNO=d.DEPTNO;笛卡爾乘積如果查詢兩張表，并且沒有定義連接條件，那么查詢的結(jié)果集是兩表相乘的結(jié)果，這樣的情況稱之為笛卡爾乘積笛卡爾乘積會產(chǎn)生很多行結(jié)果。比如，第一張表的行數(shù)為50行，第二張表為100行，那么笛卡爾乘積產(chǎn)生的結(jié)果為5000行。因?yàn)榈谝粡埍淼拿織l記錄會對應(yīng)第二張表的100條記錄，即50*100=5000例如SELECTe.empno,e.ename,d.deptnoFROMempe,deptd;此時雇員表emp有14條記錄，dept有4條記錄，查詢后有56條記錄，所有為了消除笛卡爾乘積，我們在做多表查詢的時候，要加上連接條件。練習(xí)要求查詢出每一個雇員的編號、姓名、工資、部門名稱、工資等級第一步：確定所需要的數(shù)據(jù)表EMP表:雇員的編號、姓名、工資DEPT表：部門名稱SALGRADE表：工資等級第二步：確定關(guān)聯(lián)條件雇員和部門：e.deptno=d.deptno雇員和員工等級：e.salbetweensalgrade.losalandsalgrade.hisal;練習(xí)第三步：查詢出每一個雇員的編號、姓名、工資、selecte.empno,e.ename,e.salfromEMPe;第四步：引入部門表，同時增加一個消除笛卡爾積的條件selecte.empno,e.ename,e.sal,d.dnamefromEMPe,DEPTdwheree.deptno=d.deptno;第五步：引入工資等級表，繼續(xù)增加消除笛卡爾積的條件selecte.empno,e.ename,e.sal,d.dname,s.gradefromEMPe,DEPTd,SALGRADEswheree.deptno=d.deptnoande.salbetweens.losalandhisal;總結(jié)多表查詢WHERE時，連接次數(shù)=查詢時連接表的數(shù)量-1連接條件和連接類型常見的有兩類連接條件等值連接：一般使用等于號（=）操作符非等值連接：使用除了等于以外的運(yùn)算符，一般使用<>、>、<、<=、>=、LIKE、IN、BETWEEN等運(yùn)算符常見的三種連接類型內(nèi)連接內(nèi)連接返回的行只有滿足連接條件才會返回。如果連接條件的列中有NULL值，那么該行則不會返回Innerjoin外連接外連接返回的行滿足連接條件，也包括在連接條件的列包含空值的行Leftouterjoin,rightouterjoin自連接連接的表為同一張表自連接示例自連接：例如要求查詢出員工及他的上級。

SELECTe.enameAS員工,b.enameAS領(lǐng)導(dǎo)FROMempeLEFTJOINempbONe.mgr=b.empnoORDERBYb.ename;外連接:可以查詢出即使列中包含NULL值的數(shù)據(jù)也可以查詢出來外部連接運(yùn)算符(+)。在使用(+)的字段，可能包含NULL值例如：查詢出部門的員工的情況（列出所有部門）SELECTe.ename,d.dnameFROMempe,deptdWHEREd.deptno=e.deptno(+)ORDERBYd.dname;外連接分類外連接可以分為左外連接右外連接左外連接右外連接現(xiàn)有有以下查詢語句SELECT...FROMtable1,table2...其中table1.column1和table2.column2為連接條件假設(shè)1，table1表包含了某行的column1列的值為NULL左外連接的WHERE條件寫作WHEREtable1.column1=table2.column2(+);假設(shè)2，table2表包含了某行的column2列的值為NULL右外連接的WHERE條件寫作WHEREtable1.column1(+)=table2.column2;外連接使用左外連接：例如：查詢出部門的員工的情況（顯示所有部門）SELECTe.ename,d.dnameFROMempe,deptdWHEREd.deptno=e.deptno(+)ORDERBYd.dname;右外連接：例如：查詢出所有的員工的部門情況（顯示了所有員工）SELECTe.ename,d.dnameFROMempe,deptdWHEREd.deptno(+)=e.deptnoORDERBYd.dname;(+)外連接的局限性只能一側(cè)使用外連接運(yùn)算符(+)，否則將提示錯誤不可以在使用外連接的情況下同時使用IN運(yùn)算符(+)是Oracle所獨(dú)有的，其他數(shù)據(jù)庫不適用連接查詢使用SQL/92語法SQL/92語法中是使用INNERJOIN和ON來完成內(nèi)連接的例如SELECTe.ename,d.dnameFROMempeINNERJOINdeptdONe.deptno=d.deptnoORDERBYe.ename;使用USINGSQL/92可以進(jìn)一步簡化連接條件，但有以下限制查詢必須是等值連接連接的兩張表的關(guān)聯(lián)字段必須同名例如，以下查詢就使用USING替代了ON關(guān)鍵字SELECTe.ename,d.dnameFROMempeINNERJOINdeptdUSING(deptno);注意：使用USING后，select子句的列部分不能有限定詞USING子句中的列名也不能加限定詞多表查詢使用SQL/92語法要求查詢出每一個雇員的編號、姓名、工資、部門名稱、工資等級 selecte.empno,e.ename,e.sal,d.dname,s.gradefromEMPe

INNERJOINdeptdUSING(deptno)

INNERJOINSALGRADEsone.salbetweens.losalandhisal;外連接使用SQL/92語法之前外連接查詢使用(+)運(yùn)算符，這是Oracle語法SQL/92使用的語法如下FROMtable1{LEFT|RIGHT|FULL}OUTERJOINtable2說明table1和table2是要連接查詢的表LEFT：表明是左外連接RIGHT：表明是右外連接FULL：表明是完全外連接。完全外連接會返回table和table2中所有的行，包括連接列是NULL值的情況。外連接使用SQL/92語法左外連接：例如：查詢出部門的員工的情況（顯示所有部門）SELECTe.ename,d.dnameFROMdeptdleftjoinempeusing(deptno);右外連接：例如：查詢出所有的員工的部門情況（顯示了所有員工）SELECTe.ename,d.dnameFROMemperightjoindeptdusing(deptno);完全外連接返回連接的所有表的行，包括NULL值例如：查詢出所有的員工的部門情況SELECTe.ename,d.dnameFROMempefulljoindeptdusing(deptno);SQL/92語法產(chǎn)生笛卡爾乘積需要使用CROSSJOIN關(guān)鍵字例如:查詢EMP表和DEPT表SELECT*FROMEMPCROSSJOINDEPT;子查詢及分類子查詢是嵌入到另一個SELECT語句中的一個SELECT語句。通過使用子查詢，可以使用簡單的語句組成強(qiáng)大的語句。當(dāng)需要從表中選擇行，而選擇條件卻取決于該表自身中的數(shù)據(jù)時，子查詢非常有用。子查詢主要分為以下兩種基本類型單行子查詢多行子查詢另外，還有以下3種類型多列子查詢關(guān)聯(lián)子查詢嵌套子查詢單行子查詢單行子查詢內(nèi)部SELECT語句給外部的SELECT語句返回0行或1行?？梢栽赟ELECT語句后面的WHERE、HAVING、FROM后面放置單行子查詢在WHERE子句中使用子查詢將另外一個查詢作為WHERE子句的子查詢。在單行子查詢還可以使用其他比較運(yùn)算符，如<>、<、>、<=和>=例如，WHERE子句中使用>，以及子查詢中使用AVG()聚集函數(shù) SELECTEMPNO,EnameFROMEMP WHERESAL>(SELECTAVG(SAL)FROMEMP);HAVING是在分組統(tǒng)計(jì)后用于過濾行，同樣在HAVING子句后面可以跟子查詢。單行子查詢將返回結(jié)果用于HAVING子句過濾分組統(tǒng)計(jì)的行在FROM子句中的子查詢將子查詢放置于FROM子句之后為其提供數(shù)據(jù)。例如SELECTempno,ename,dnameFROM(SELECTe.empno,e.ename,d.dnameFROMEMPe,DEPTd wheree.deptno=d.deptno);使用單行子查詢常見錯誤如果子查詢返回的結(jié)果是多行，而其使用的是比較運(yùn)算符，將會出現(xiàn)錯誤子查詢不能包含ORDERBY子句，排序必須在父查詢中進(jìn)行多行子查詢多行子查詢內(nèi)部SELECT語句給外部的SELECT語句返回1行或多行。在父查詢中可以使用IN、ANY或ALL操作符例如：使用in操作符

select*fromemp wheredeptnoin(selectdeptnofromdept);

同樣可以使用NOTIN檢查一個值是否不在指定的列表中?？梢允褂肁NY操作符比較列表中的任意一個值。必須把=，<>，<，>，<=，>=操作符放置在ANY之前例如，使用ANY查詢?nèi)我鉂M足工資低于最低檔工資的員工信息SELECTempno,enameFROMempWHEREsal<ANY(SELECTsalFROMemp);多列子查詢子查詢還可以包含多列例如，通過多列子查詢查詢出每個部門員工的最低工資，然后再查詢出滿足這些條件的商品信息

SELECTdeptno,salFROMEMP WHERE(deptno,sal) IN(SELECTdeptno,MIN(sal)FROMempGROUPBYdeptno);關(guān)聯(lián)子查詢子查詢引用與父查詢相關(guān)的一列或多列子查詢需要借助父查詢，而父查詢也需要借助子查詢子查詢需要為父查詢查詢出的每一行運(yùn)行一次例如：查詢出每個部門中，高出本部門平均工資的員工的雇員號和姓名

SELECTempno,ename,sal,deptno FROMempouter WHEREsal> (SELECTAVG(sal)FROMEmpinnerWHEREinner.deptno=outer.deptno);可以使用EXISTS操作符檢查是否存在由子查詢返回的行例如，查詢emp表中可以管理別的員工的員工 SELECTempno,enameFROMempouter WHEREEXISTS (SELECTempnoFROMempinnerWHEREinner.mgr=outer.empno);關(guān)聯(lián)子查詢中使用NOTEXISTS例如，查詢不在部門10的員工信息注意子查詢中的1，由于只關(guān)心子查詢是否返回TRUE值，使用1可以提高查詢的效率；EXISTS子查詢效率高于IN子查詢SELECTempno,ename,deptnofromempewherenotexists(select1fromdeptdwheree.deptno=d.deptnoandd.deptno=10);SQL提供嵌套子查詢機(jī)制，即在一個查詢中嵌套另一個

select

-

from

-

where查詢語句.子查詢中嵌套子查詢，最多可以嵌套255層例如:查詢工資高于SMITH工資的所有員工select*fromempwheresal>(selectsalfromempwhereenAme='SMITH')可以使用all關(guān)鍵詞例如：查詢工資高于所有部門的平均工資的員工select*fromempwheresal>all(selectavg(sal)fromempgroupbydeptno)--查詢工資高于任何部門的平均工資的員工嵌套子查詢UPDATE和DELETE使用子查詢-1可以將子查詢用在UPDATE和DELETE語句中可以在UPDATE語句中，使用單行子查詢返回的結(jié)果給列賦值例如，將empno為4的員工工資進(jìn)行更新UPDATEempSETsal=(SELECTAVG(hisal)FROMsalgrade)WHEREemployee_id=4;UPDATE和DELETE使用子查詢-2可以利用子查詢返回的結(jié)果給DELETE語句的WHERE條件進(jìn)行DELETE操作例如，刪除工資高于平均工資的員工DELETEFROMempWHEREsal>(SELECTAVG(hisal)FROMsalgrade);示例刪除表中重復(fù)的行deletefromempWhereempnoin(selectidfromempgroupbyempnohavingcount(empno)>=2);