SQL--嵌套查詢(xún)集合查詢(xún)

1、3.3.4 嵌套查詢(xún) 4嵌套查詢(xún)概述 4嵌套查詢(xún)分類(lèi) 4嵌套查詢(xún)求解方法 4引出子查詢(xún)的謂詞 嵌套查詢(xún)概述 4嵌套查詢(xún)一個(gè)SELECT-FROM-WHERE語(yǔ)句稱(chēng)為一個(gè)查 詢(xún)塊 將一個(gè)查詢(xún)塊嵌套在另一個(gè)查詢(xún)塊的WHERE子句或 HAVING短語(yǔ)的條件中的查詢(xún)稱(chēng)為嵌套查詢(xún) SELECT Sname外層查詢(xún)/父查詢(xún) FROM Student WHERE Sno IN （SELECT Sno 內(nèi)層查詢(xún)/子查詢(xún) FROM SC WHERE Cno= 2 ）； 子查詢(xún)的限制:不能使用不能使用ORDER BY子句子句 層層嵌套方式反映了 SQL語(yǔ)言的結(jié)構(gòu)化 有些嵌套查詢(xún)可以用連接運(yùn)算替代 嵌套查詢(xún)分類(lèi)及求

2、解方法 4不相關(guān)子查詢(xún): 子查詢(xún)的查詢(xún)條件不依賴(lài)于父查詢(xún)是由里向外逐層處 理。即每個(gè)子查詢(xún)?cè)谏弦患?jí)查詢(xún)處理之前求解，子查 詢(xún)的結(jié)果用于建立其父查詢(xún)的查找條件。 4相關(guān)子查詢(xún):子查詢(xún)的查詢(xún)條件依賴(lài)于父查詢(xún) 首先取外層查詢(xún)中表的第一個(gè)元組，根據(jù)它與內(nèi)層 查詢(xún)相關(guān)的屬性值處理內(nèi)層查詢(xún)，若WHERE子句 返回值為真，則取此元組放入結(jié)果表； 然后再取外層表的下一個(gè)元組； 重復(fù)這一過(guò)程，直至外層表全部檢查完為止。 引出子查詢(xún)的謂詞 4帶有IN謂詞的子查詢(xún) 4帶有比較運(yùn)算符的子查詢(xún) 4帶有ANY或ALL謂詞的子查詢(xún) 4帶有EXISTS謂詞的子查詢(xún) 一、帶有IN謂詞的子查詢(xún) 例37 查詢(xún)與“劉晨”在同一個(gè)系學(xué)

3、習(xí)的學(xué)生。 此查詢(xún)要求可以分步來(lái)完成 確定“劉晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 劉晨 ；結(jié)果為： Sdept IS 查找所有在IS系學(xué)習(xí)的學(xué)生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 結(jié)果為：Sno Sname Sdept 95001 劉晨 IS 95004 張立 IS 構(gòu)造嵌套查詢(xún) 將第一步查詢(xún)嵌入到第二步查詢(xún)的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Stud

4、ent WHERE Sname= 劉晨 )； 此查詢(xún)?yōu)椴幌嚓P(guān)子查詢(xún)。DBMS求解該查詢(xún)時(shí)也是分步去做的。 用自身連接完成本查詢(xún)要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 劉晨； 帶有IN謂詞的子查詢(xún)（續(xù)） 父查詢(xún)和子查詢(xún)中的表均可以定義別名 SELECT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sn

5、ame= 劉晨 )； 帶有IN謂詞的子查詢(xún)（續(xù)） 例38查詢(xún)選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名 SELECT Sno，Sname 最后在Student關(guān)系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC關(guān)系中找出選 FROM SC 修了3號(hào)課程的學(xué)生學(xué)號(hào) WHERE Cno IN (SELECT Cno 首先在Course關(guān)系中找出“信 FROM Course 息系統(tǒng)”的課程號(hào)，結(jié)果為3號(hào) WHERE Cname= 信息系統(tǒng)); 結(jié)果： Sno Sname - - 95001 李勇 95002 劉晨 帶有IN謂詞的子查詢(xún)

6、（續(xù)） 用連接查詢(xún) SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系統(tǒng)； 二、帶有比較運(yùn)算符的子查詢(xún) 當(dāng)能確切知道內(nèi)層查詢(xún)返回單值時(shí)，可用比較運(yùn)算符 （，=，=，!=或）。 與ANY或ALL謂詞配合使用 例：假設(shè)一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，并且必須屬于一個(gè)系，則 在例37可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = SELECT Sdept F

7、ROM Student WHERE Sname= 劉晨 ； 帶有比較運(yùn)算符的子查詢(xún)（續(xù)） 子查詢(xún)一定要跟在比較符之后子查詢(xún)一定要跟在比較符之后 錯(cuò)誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept； 三、帶有ANY或ALL謂詞的子查詢(xún) 謂詞語(yǔ)義 ANY：任意一個(gè)值 ALL：所有值 需要配合使用比較運(yùn)算符需要配合使用比較運(yùn)算符 ANY大于子查詢(xún)結(jié)果中的某個(gè)值大于子查詢(xún)結(jié)果中的某個(gè)值 ALL大于子查詢(xún)結(jié)果中的所有值大于子查詢(xún)結(jié)果中的所有值 ANY小

8、于子查詢(xún)結(jié)果中的某個(gè)值小于子查詢(xún)結(jié)果中的某個(gè)值 = ANY大于等于子查詢(xún)結(jié)果中的某個(gè)值大于等于子查詢(xún)結(jié)果中的某個(gè)值 = ALL大于等于子查詢(xún)結(jié)果中的所有值大于等于子查詢(xún)結(jié)果中的所有值 = ANY小于等于子查詢(xún)結(jié)果中的某個(gè)值小于等于子查詢(xún)結(jié)果中的某個(gè)值 = ALL小于等于子查詢(xún)結(jié)果中的所有值小于等于子查詢(xún)結(jié)果中的所有值 = ANY等于子查詢(xún)結(jié)果中的某個(gè)值等于子查詢(xún)結(jié)果中的某個(gè)值 =ALL等于子查詢(xún)結(jié)果中的所有值（通常沒(méi)有實(shí)際意義）等于子查詢(xún)結(jié)果中的所有值（通常沒(méi)有實(shí)際意義） !=（或（或）ANY不等于子查詢(xún)結(jié)果中的某個(gè)值不等于子查詢(xún)結(jié)果中的某個(gè)值 !=（或（或）ALL不等于子查詢(xún)結(jié)果中的任何一個(gè)

9、值不等于子查詢(xún)結(jié)果中的任何一個(gè)值 帶有ANY或ALL謂詞的子查詢(xún)（續(xù)） 例39 查詢(xún)其他系中比信息系任意一個(gè)(其中某一個(gè))學(xué)生 年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; /* 注意這是父查詢(xún)塊中的條件 */ 帶有ANY或ALL謂詞的子查詢(xún)（續(xù)） 結(jié)果 Sname Sage 王敏 18 執(zhí)行過(guò)程 1.DBMS執(zhí)行此查詢(xún)時(shí)，首先處理子查詢(xún)，找出 IS系中所 有學(xué)生的年齡，構(gòu)成一個(gè)集合(19，18) 2. 處理

10、父查詢(xún)，找所有不是IS系且年齡小于19 或 18的學(xué)生 帶有ANY或ALL謂詞的子查詢(xún)（續(xù)） ANY和ALL謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn) ANY與ALL與集函數(shù)集函數(shù)的對(duì)應(yīng)關(guān)系 = 或或!= = ANY IN - MAXMIN= MIN ALL - NOT IN MIN MAX = MAX 用集函數(shù)實(shí)現(xiàn)子查詢(xún)通常比直接用ANY或ALL查詢(xún)效 率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)減少比較次數(shù) 帶有ANY或ALL謂詞的子查詢(xún)（續(xù)） 例39：用集函數(shù)實(shí)現(xiàn)例39查詢(xún)其他系中比信息系任意 一個(gè)(其中某一個(gè))學(xué)生年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE

11、Sage (SELECT MAX(Sage) FROM Student WHERE Sdept= IS ) AND Sdept IS ; 帶有ANY或ALL謂詞的子查詢(xún)（續(xù)） 例40 查詢(xún)其他系中比信息系所有學(xué)生年齡都小的學(xué)生 姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; 查詢(xún)結(jié)果為空表。 方法二：用集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage (SE

12、LECT MIN(Sage) FROM Student WHERE Sdept= IS ) AND Sdept IS ; 四、帶有EXISTS謂詞的子查詢(xún) 1. EXISTS謂詞 2. NOT EXISTS謂詞 3. 不同形式的查詢(xún)間的替換 4. 相關(guān)子查詢(xún)的效率 5. 用EXISTS/NOT EXISTS實(shí)現(xiàn)全稱(chēng)量詞 6. 用EXISTS/NOT EXISTS實(shí)現(xiàn)邏輯蘊(yùn)函 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 1. EXISTS謂詞 存在量詞 帶有EXISTS謂詞的子查詢(xún)不返回任何數(shù)據(jù)，只產(chǎn)生邏輯 真值“true”或邏輯假值“false”。 若內(nèi)層查詢(xún)結(jié)果非空，則返回真值 若內(nèi)層查詢(xún)結(jié)果為空，

13、則返回假值 由EXISTS引出的子查詢(xún)，其目標(biāo)列表達(dá)式通常都用* * ， 因?yàn)閹XISTS的子查詢(xún)只返回真值或假值，給出列名無(wú) 實(shí)際意義 2. NOT EXISTS謂詞 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 例41 查詢(xún)所有選修了1號(hào)課程的學(xué)生姓名。 用嵌套查詢(xún) SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相關(guān)子查詢(xún)*/ WHERE Sno=Student.Sno AND Cno= 1 )； 思路分析： 本查詢(xún)涉及Student和SC關(guān)系。 在Student中依次取每個(gè)元組的Sno值，用此值去檢查SC關(guān)系。 若SC中存在

14、這樣的元組，其Sno值等于此Student.Sno值，并 且其Cno= 1，則取此Student.Sname送入結(jié)果關(guān)系。 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 用連接運(yùn)算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1; 例42 查詢(xún)沒(méi)有選修1號(hào)課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)； 此例用連接運(yùn)算難于實(shí)現(xiàn) 帶有EXISTS謂詞的子查詢(xún)

15、(續(xù)） 3. 不同形式的查詢(xún)間的替換 一些帶EXISTS或NOT EXISTS謂詞的子查詢(xún)不能被其他形式的子查 詢(xún)等價(jià)替換 所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢(xún)都能用帶 EXISTS謂詞的子查詢(xún)等價(jià)替換。 例：例37查詢(xún)與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。可以用帶 EXISTS謂詞的子查詢(xún)替換： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 劉晨 ； 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 5.用

16、EXISTS/NOT EXISTS實(shí)現(xiàn)全稱(chēng)量詞(難點(diǎn)) SQL語(yǔ)言中沒(méi)有全稱(chēng)量詞 （For all） 可以把帶有全稱(chēng)量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存 在量詞的謂詞： (x)P ( x( P) 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 例43 查詢(xún)選修了全部課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno）； 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 6. 用E

17、XISTS/NOT EXISTS實(shí)現(xiàn)邏輯蘊(yùn)函(難點(diǎn)) SQL語(yǔ)言中沒(méi)有蘊(yùn)函(Implication)邏輯運(yùn)算 可以利用謂詞演算將邏輯蘊(yùn)函謂詞等價(jià)轉(zhuǎn)換為： p q pq 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 例44 查詢(xún)至少選修了學(xué)生95002選修的全部課程的學(xué)生號(hào)碼。 解題思路： 用邏輯蘊(yùn)函表達(dá)：查詢(xún)學(xué)號(hào)為x的學(xué)生，對(duì)所有的課程y，只要95002學(xué)生 選修了課程y，則x也選修了y。 形式化表示： 用P表示謂詞 “學(xué)生95002選修了課程y” 用q表示謂詞 “學(xué)生x選修了課程y” 則上述查詢(xún)?yōu)? (y) p q 等價(jià)變換： (y)p q (y (p q ) (y ( p q) y(pq) 變換后語(yǔ)

18、義：不存在這樣的課程y，學(xué)生95002選修了y，而學(xué)生x 沒(méi)有選。 帶有EXISTS謂詞的子查詢(xún)(續(xù)） 用NOT EXISTS謂詞表示： SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)； 3.3 查 詢(xún) 3.3.1 概述 3.3.2 單表查詢(xún) 3.3.3 連接查詢(xún) 3.3.4 嵌套查詢(xún) 3.3.5 集

19、合查詢(xún) 3.3.6 小結(jié) 3.3.5 集合查詢(xún) 標(biāo)準(zhǔn)SQL直接支持的集合操作種類(lèi) 并操作(UNION) 一般商用數(shù)據(jù)庫(kù)支持的集合操作種類(lèi) 并操作(UNION) 交操作(INTERSECT) 差操作(MINUS) 1 并操作 4形式 UNION 參加UNION操作的各結(jié)果表的列數(shù)必須相同列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的 數(shù)據(jù)類(lèi)型也必須相同 并操作（續(xù)） 例45 查詢(xún)計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生 方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19； 方法二： SELECT DI

20、STINCT * FROM Student WHERE Sdept= CS OR Sage=19； 并操作（續(xù)） 例46 查詢(xún)選修了課程1或者選修了課程2的學(xué)生。 方法一： SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT Sno FROM SC WHERE Cno= 2 ； 方法二： SELECT DISTINCT Sno FROM SC WHERE Cno= 1 OR Cno= 2 ； 并操作（續(xù)） 例47 設(shè)數(shù)據(jù)庫(kù)中有一教師表Teacher(Tno, Tname,.)。查 詢(xún)學(xué)校中所有師生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher; new 2 交操作 標(biāo)準(zhǔn)SQL中沒(méi)有提供集合交操作，但可用其他方法間接實(shí) 現(xiàn)。 例48 查詢(xún)計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué) 生的交集 本例實(shí)際上就是查詢(xún)計(jì)算機(jī)科學(xué)系中年齡不大于19歲的 學(xué)生 SELECT * FROM Student WHERE Sdept= CS AND Sage