SQL--嵌套查詢集合查詢

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

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

3、習(xí)的學(xué)生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 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)造嵌套查詢 將第一步查詢嵌入到第二步查詢的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Stud

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

5、ame= 劉晨 )； 帶有IN謂詞的子查詢（續(xù)） 例38查詢選修了課程名為“信息系統(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謂詞的子查詢

6、（續(xù)） 用連接查詢 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系統(tǒng)； 二、帶有比較運(yùn)算符的子查詢 當(dāng)能確切知道內(nèi)層查詢返回單值時(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ù)） 子查詢一定要跟在比較符之后子查詢一定要跟在比較符之后 錯(cuò)誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept； 三、帶有ANY或ALL謂詞的子查詢 謂詞語義 ANY：任意一個(gè)值 ALL：所有值 需要配合使用比較運(yùn)算符需要配合使用比較運(yùn)算符 ANY大于子查詢結(jié)果中的某個(gè)值大于子查詢結(jié)果中的某個(gè)值 ALL大于子查詢結(jié)果中的所有值大于子查詢結(jié)果中的所有值 ANY小

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

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

10、父查詢，找所有不是IS系且年齡小于19 或 18的學(xué)生 帶有ANY或ALL謂詞的子查詢（續(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)子查詢通常比直接用ANY或ALL查詢效 率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)減少比較次數(shù) 帶有ANY或ALL謂詞的子查詢（續(xù)） 例39：用集函數(shù)實(shí)現(xiàn)例39查詢其他系中比信息系任意 一個(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ù)） 例40 查詢其他系中比信息系所有學(xué)生年齡都小的學(xué)生 姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; 查詢結(jié)果為空表。 方法二：用集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage (SE

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

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

14、這樣的元組，其Sno值等于此Student.Sno值，并 且其Cno= 1，則取此Student.Sname送入結(jié)果關(guān)系。 帶有EXISTS謂詞的子查詢(續(xù)） 用連接運(yùn)算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1; 例42 查詢沒有選修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謂詞的子查詢

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

16、EXISTS/NOT EXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn)) SQL語言中沒有全稱量詞 （For all） 可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存 在量詞的謂詞： (x)P ( x( P) 帶有EXISTS謂詞的子查詢(續(xù)） 例43 查詢選修了全部課程的學(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ù)） 6. 用E

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

18、義：不存在這樣的課程y，學(xué)生95002選修了y，而學(xué)生x 沒有選。 帶有EXISTS謂詞的子查詢(續(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 查 詢 3.3.1 概述 3.3.2 單表查詢 3.3.3 連接查詢 3.3.4 嵌套查詢 3.3.5 集

19、合查詢 3.3.6 小結(jié) 3.3.5 集合查詢 標(biāo)準(zhǔn)SQL直接支持的集合操作種類 并操作(UNION) 一般商用數(shù)據(jù)庫(kù)支持的集合操作種類 并操作(UNION) 交操作(INTERSECT) 差操作(MINUS) 1 并操作 4形式 UNION 參加UNION操作的各結(jié)果表的列數(shù)必須相同列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的 數(shù)據(jù)類型也必須相同 并操作（續(xù)） 例45 查詢計(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 查詢選修了課程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,.)。查 詢學(xué)校中所有師生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher; new 2 交操作 標(biāo)準(zhǔn)SQL中沒有提供集合交操作，但可用其他方法間接實(shí) 現(xiàn)。 例48 查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué) 生的交集 本例實(shí)際上就是查詢計(jì)算機(jī)科學(xué)系中年齡不大于19歲的 學(xué)生 SELECT * FROM Student WHERE Sdept= CS AND Sage