數(shù)據(jù)庫原理與應(yīng)用：第9章 嵌套查詢

數(shù)據(jù)庫原理與應(yīng)用

第9章查詢

9.1單表查詢

9.2連接查詢

9.3嵌套查詢9.3嵌套查詢嵌套查詢概述嵌套查詢分類嵌套查詢求解方法引出子查詢的謂詞

嵌套查詢(續(xù))嵌套查詢概述一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句的條件中的查詢稱為嵌套查詢，此查詢塊稱為子查詢或內(nèi)層查詢，包含子查詢的查詢塊稱為父查詢或外層查詢。

嵌套查詢(續(xù))

SELECTsno,sname /*外層查詢/父查詢*/FROMStudentWHEREsnoIN

(SELECTsno/*內(nèi)層查詢/子查詢*/FROMscoreWHEREcno='3-105')嵌套查詢(續(xù))子查詢不能使用ORDERBY子句有些嵌套查詢可以用連接查詢替代嵌套查詢分類不相關(guān)子查詢子查詢的查詢條件不依賴于父查詢，子查詢的查詢條件沒用引用父查詢表中的屬性值。相關(guān)子查詢子查詢的查詢條件依賴于父查詢，子查詢的查詢條件引用了父查詢表中的屬性值。嵌套查詢求解方法不相關(guān)子查詢是由里向外逐層處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查詢條件。嵌套查詢求解方法（續(xù)）相關(guān)子查詢首先取外層查詢中表的第一個(gè)元組，把與內(nèi)層查詢相關(guān)的屬性值代入并處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再取外層表的下一個(gè)元組；重復(fù)這一過程，直至外層表全部檢查完為止。帶有IN謂詞的子查詢帶有比較運(yùn)算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢一、帶有IN謂詞的子查詢[例37]查詢與“王芳”在同一個(gè)班學(xué)習(xí)的學(xué)生。此查詢要求可以分步來完成：①確定“王芳”所在班

SELECTclassFROMstudentWHEREsname='王芳'

結(jié)果為：帶有IN謂詞的子查詢（續(xù)）②查找所有在95031學(xué)習(xí)的學(xué)生。

SELECT*FROMstudentWHEREclass='95031'結(jié)果為：

構(gòu)造嵌套查詢將第一步查詢嵌入到第二步查詢的條件中

SELECT*FROMstudentWHEREclassIN(SELECTclassFROMstudentWHEREsname='王芳')此查詢?yōu)椴幌嚓P(guān)子查詢。DBMS求解該查詢時(shí)也是分步去做的。帶有IN謂詞的子查詢（續(xù)）

用自身連接完成本查詢要求

SELECTS1.*FROMstudentS1,studentS2WHERES1.Sdept=S2.SdeptAND

S2.sname='王芳'帶有IN謂詞的子查詢（續(xù)）父查詢和子查詢中的表均可以定義別名

SELECT*FROMstudentS1WHERES1.classIN(SELECTclassFROMstudentS2WHERES2.sname='王芳')帶有IN謂詞的子查詢（續(xù)）[例38]查詢選修了課程名為“操作系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名

SELECTstudent.sno,sname

/*③最后在student關(guān)系中取出sno和sname*/FROMstudentWHEREsnoIN(SELECTsno

/*②然后在score關(guān)系中找出選修了‘3-245'號(hào)課程的學(xué)生學(xué)號(hào)('103','105','109')*/FROMscoreWHEREcnoIN(SELECTcno

/*①首先在course關(guān)系中找出“操作系統(tǒng)”的課程號(hào)，結(jié)果為'3-245'*/FROMcourseWHEREcname='操作系統(tǒng)'))帶有IN謂詞的子查詢（續(xù)）結(jié)果：帶有IN謂詞的子查詢（續(xù)）用連接查詢

SELECTstudent.sno,snameFROMstudent,score,courseWHEREstudent.sno=score.snoAND

o=oAND

ame='操作系統(tǒng)'二、帶有比較運(yùn)算符的子查詢當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（>，<，=，>=，<=，!=或<>）。與ANY或ALL謂詞配合使用帶有比較運(yùn)算符的子查詢（續(xù)）例：假設(shè)一個(gè)學(xué)生只可能在一個(gè)班學(xué)習(xí)，則在[例37]可以用=代替IN

：

SELECT*FROMstudentWHEREclass=(SELECTclassFROMstudentWHEREsname='王芳')三、帶有ANY或ALL謂詞的子查詢ANY：任意一個(gè)ALL：所有帶有ANY或ALL謂詞的子查詢（續(xù)）需要配合使用比較運(yùn)算符>ANY 大于子查詢結(jié)果中的某個(gè)值

>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個(gè)值<ALL 小于子查詢結(jié)果中的所有值>=ANY 大于等于子查詢結(jié)果中的某個(gè)值>=ALL 大于等于子查詢結(jié)果中的所有值<=ANY 小于等于子查詢結(jié)果中的某個(gè)值<=ALL 小于等于子查詢結(jié)果中的所有值=ANY 等于子查詢結(jié)果中的某個(gè)值=ALL 等于子查詢結(jié)果中的所有值（沒有實(shí)際意義）!=（或<>）ANY不等于子查詢結(jié)果中的某個(gè)值（沒有實(shí)際意義）!=（或<>）ALL不等于子查詢結(jié)果中的任何一個(gè)值帶有ANY或ALL謂詞的子查詢（續(xù)）[例39]查詢其他班中比95031任意一個(gè)(其中某一個(gè))學(xué)生后出生的學(xué)生姓名和出生日期

SELECTsname,sbirthdayFROMstudentWHEREsbirthday>ANY(SELECTsbirthdayFROMstudentWHEREclass='95031')

ANDclass<>'95031'

/*注意這是父查詢塊中的條件*/

帶有ANY或ALL謂詞的子查詢（續(xù)）結(jié)果執(zhí)行過程1.DBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出‘95031’班中所有學(xué)生的出生日期，構(gòu)成一個(gè)集合

(‘74-6-3’，‘75-10-2’,‘75-2-10’)。2.處理父查詢，找所有不是’95031’且出生日期大于(‘74-6-3’，‘75-10-2’,‘75-2-10’)集合中任意一個(gè)（實(shí)際上只要大于‘74-6-3’）的學(xué)生。帶有ANY或ALL謂詞的子查詢（續(xù)）ANY和ALL謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn)ANY與ALL與集函數(shù)的對(duì)應(yīng)關(guān)系

=

<>或!=

<

<=

>

>=ANY

IN

--

<MAX<=MAX>MIN>=MINALL

--

NOTIN

<MIN<=MIN>MAX>=MAX帶有ANY或ALL謂詞的子查詢（續(xù)）用集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)帶有ANY或ALL謂詞的子查詢（續(xù)）[例39']：用集函數(shù)實(shí)現(xiàn)[例39]SELECTsname,sbirthdayFROMstudentWHEREsbirthday>(SELECTMIN(sbirthday)FROMstudentWHEREclass='95031')ANDclass<>'95031'帶有ANY或ALL謂詞的子查詢（續(xù)）[例40]查詢其他系中比95031班所有學(xué)生都后出生的學(xué)生姓名及出生日期。方法一：用ALL謂詞

SELECTsname,sbirthdayFROMstudentWHEREsbirthday>ALL(SELECTsbirthdayFROMstudentWHEREclass='95031')ANDclass<>'95031'帶有ANY或ALL謂詞的子查詢（續(xù)）

方法二：用集函數(shù)

SELECTsname,sbirthdayFROMstudentWHEREsbirthday>(SELECTMAX(sbirthday)FROMstudentWHEREclass='95031')ANDclass<>'95031'四、帶有EXISTS謂詞的子查詢1.EXISTS謂詞2.NOTEXISTS謂詞3.不同形式的查詢間的替換4.用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞5.用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)函帶有EXISTS謂詞的子查詢（續(xù)）1.EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則返回真值若內(nèi)層查詢結(jié)果為空，則返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無實(shí)際意義2.NOTEXISTS謂詞帶有EXISTS謂詞的子查詢（續(xù)）[例41]查詢所有選修了3-245號(hào)課程的學(xué)生姓名。用嵌套查詢

SELECTsnameFROMstudentWHEREEXISTS(SELECT*FROMscore/*相關(guān)子查詢*/WHEREsno=student.snoAND

cno='3-245')帶有EXISTS謂詞的子查詢（續(xù)）

思路分析：本查詢涉及student和score關(guān)系。在student中依次取每個(gè)元組的sno值，用此值去檢查score關(guān)系。若score中存在這樣的元組，其sno值等于此student.sno值，并且其cno='3-105'，則取此student.sname送入結(jié)果關(guān)系。帶有EXISTS謂詞的子查詢（續(xù)）用連接運(yùn)算SELECTsnameFROMstudent,scoreWHEREstudent.sno=score.snoAND

o='3-245'帶有EXISTS謂詞的子查詢（續(xù)）[例42]查詢沒有選修3-245號(hào)課程的學(xué)生姓名。

SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMscoreWHEREsno=student.sno ANDcno='3-245')帶有EXISTS謂詞的子查詢（續(xù)）3.不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換。

帶有EXISTS謂詞的子查詢（續(xù)）例：[例37]查詢與“王芳”在同一個(gè)班學(xué)習(xí)的學(xué)生?？梢杂脦XISTS謂詞的子查詢替換：

SELECT*FROMstudentS1WHEREEXISTS(SELECT*FROMstudentS2WHERES2.class=S1.classANDS2.sname='王芳')帶有EXISTS謂詞的子查詢（續(xù)）4.用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn))SQL語言中沒有全稱量詞

（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：

(

x)P≡

(

x(

P))帶有EXISTS謂詞的子查詢（續(xù)）[例43]查詢選修了全部課程的學(xué)生姓名。

SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMcourseWHERENOTEXISTS(SELECT*FROMscoreWHEREsno=student.snoANDcno=o))帶有EXISTS謂詞的子查詢（續(xù)）

5.用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)函(難點(diǎn))SQL語言中沒有蘊(yùn)函(Implication)邏輯運(yùn)算可以利用謂詞演算將邏輯蘊(yùn)函謂詞等價(jià)轉(zhuǎn)換為：

p

q≡

p∨q帶有EXISTS謂詞的子查