1、An Introduction to Database System,關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL,An Introduction to Database System,3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語(yǔ)句的一般形式,An Introduction to Database System,3.4.2 連接查詢,連接查詢：同時(shí)涉及多個(gè)表的查詢 連接條件或連接謂詞：用來(lái)連接兩個(gè)表的條件 一般格式： . . . BETWEEN . AND . 連接字段：連接謂詞中的列名稱 連接條件中的各連接字段類型必須是可比

2、的，但名字不必是相同的,An Introduction to Database System,連接操作的執(zhí)行過(guò)程,嵌套循環(huán)法(NESTED-LOOP) 首先在表1中找到第一個(gè)元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。 表2全部查找完后，再找表1中第二個(gè)元組，然后再?gòu)念^開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。 重復(fù)上述操作，直到表1中的全部元組都處理完畢,An Introduction to Database System,排序合并法(SORT-M

3、ERGE),常用于=連接 首先按連接屬性對(duì)表1和表2排序 對(duì)表1的第一個(gè)元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時(shí)，對(duì)表2的查詢不再繼續(xù),An Introduction to Database System,排序合并法,找到表1的第二條元組，然后從剛才的中斷點(diǎn)處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。直接遇到表2中大于表1連接字段值的元組時(shí)，對(duì)表2的查詢不再繼續(xù) 重復(fù)上述操作，直到表1或表2中的全部元組都

4、處理完畢為止,An Introduction to Database System,索引連接(INDEX-JOIN),對(duì)表2按連接字段建立索引 對(duì)表1中的每個(gè)元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組,An Introduction to Database System,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,An Introduction to Database System,一、等值與非等值連接查詢,等值連接：連接運(yùn)算符為= 例33 查詢每個(gè)學(xué)生及其選修課

5、程的情況 SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；,An Introduction to Database System,等值與非等值連接查詢（續(xù)）,查詢結(jié)果：,An Introduction to Database System,等值與非等值連接查詢（續(xù)）,自然連接： 例34 對(duì)例33用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；,An Intr

6、oduction to Database System,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,An Introduction to Database System,二、自身連接,自身連接：一個(gè)表與其自己進(jìn)行連接 需要給表起別名以示區(qū)別 由于所有屬性名都是同名屬性，因此必須使用別名前綴 例35查詢每一門課的間接先修課（即先修課的先修課） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；,An Introduction

7、 to Database System,自身連接（續(xù)）,FIRST表（Course表）,An Introduction to Database System,自身連接（續(xù)）,SECOND表（Course表）,An Introduction to Database System,自身連接（續(xù)）,查詢結(jié)果：,An Introduction to Database System,SELECT temp1.Cno, temp2.Cpno FROM (SELECT * FROM Course) as temp1, (SELECT * FROM Course) as temp2 WHERE temp1.C

8、pno=temp2.Cno and temp2.Cpno is NOT NULL,An Introduction to Database System,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,An Introduction to Database System,三、外連接,外連接與普通連接的區(qū)別 普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出 例 36 改寫例33 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student

9、 LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)；,An Introduction to Database System,外連接（續(xù)）,執(zhí)行結(jié)果：,An Introduction to Database System,外連接（續(xù)）,左外連接 列出左邊關(guān)系（如本例Student）中所有的元組 右外連接 列出右邊關(guān)系中所有的元組,An Introduction to Database System,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接,An Introduction to Database System,四、復(fù)合條件

10、連接,復(fù)合條件連接：WHERE子句中含多個(gè)連接條件 例37查詢選修2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 連接謂詞*/ SC.Cno= 2 AND SC.Grade 90； /* 其他限定條件 */,An Introduction to Database System,復(fù)合條件連接（續(xù)）,例38查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī) SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，

11、Course /*多表連接*/ WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；,An Introduction to Database System,3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語(yǔ)句的一般形式,An Introduction to Database System,嵌套查詢(續(xù)),嵌套查詢概述 一個(gè)SELECT-FROM-WHERE語(yǔ)句稱為一個(gè)查詢塊 將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢稱為嵌套查詢

12、,An Introduction to Database System,嵌套查詢(續(xù)),SELECT Sname /*外層查詢/父查詢*/ FROM Student WHERE Sno IN （SELECT Sno /*內(nèi)層查詢/子查詢*/ FROM SC WHERE Cno= 2 ）；,An Introduction to Database System,嵌套查詢(續(xù)),子查詢的限制 不能使用ORDER BY子句 層層嵌套方式反映了 SQL語(yǔ)言的結(jié)構(gòu)化 有些嵌套查詢可以用連接運(yùn)算替代,An Introduction to Database System,嵌套查詢求解方法,不相關(guān)子查詢： 子查

13、詢的查詢條件不依賴于父查詢 由里向外 逐層處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。,An Introduction to Database System,嵌套查詢求解方法（續(xù)）,相關(guān)子查詢：子查詢的查詢條件依賴于父查詢 首先取外層查詢中表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表 然后再取外層表的下一個(gè)元組 重復(fù)這一過(guò)程，直至外層表全部檢查完為止,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運(yùn)算符的子查

14、詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introduction to Database System,一、帶有IN謂詞的子查詢,例39 查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。 此查詢要求可以分步來(lái)完成 確定“劉晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 劉晨 ； 結(jié)果為： CS,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù)）, 查找所有在CS系學(xué)習(xí)的學(xué)生。 SELECT Sno，Sname，Sdept FROM Student WHERE S

15、dept= CS ； 結(jié)果為：,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù)）,將第一步查詢嵌入到第二步查詢的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 劉晨 )； 此查詢?yōu)椴幌嚓P(guān)子查詢。,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù)）,用自身連接完成例39查詢要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Studen

16、t S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 劉晨；,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù)）,例40查詢選修了課程名為“信息系統(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 Cours

17、e “信息系統(tǒng)”的課程號(hào)，為3號(hào) WHERE Cname= 信息系統(tǒng) ) );,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù)）,用連接查詢實(shí)現(xiàn)例40 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系統(tǒng)；,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運(yùn)算符的子查詢 三、 帶有ANY（SOM

18、E）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introduction to Database System,二、帶有比較運(yùn)算符的子查詢,當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（，=，）。 與ANY或ALL謂詞配合使用,An Introduction to Database System,帶有比較運(yùn)算符的子查詢（續(xù)）,例：假設(shè)一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，并且必須屬于一個(gè)系，則在例39可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WH

19、ERE Sname= 劉晨)；,An Introduction to Database System,帶有比較運(yùn)算符的子查詢（續(xù)）,子查詢一定要跟在比較符之后 錯(cuò)誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept；,An Introduction to Database System,帶有比較運(yùn)算符的子查詢（續(xù)）,例41找出每個(gè)學(xué)生超過(guò)他選修課程平均成績(jī)的課程號(hào)。 SELECT Sno， Cno FROM SC x WHERE Grade =(

20、SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);,相關(guān)子查詢,An Introduction to Database System,帶有比較運(yùn)算符的子查詢（續(xù)）,可能的執(zhí)行過(guò)程： 1. 從外層查詢中取出SC的一個(gè)元組x，將元組x的Sno值（200215121）傳送給內(nèi)層查詢。 SELECT AVG(Grade) FROM SC y WHERE y.Sno=200215121; 2. 執(zhí)行內(nèi)層查詢，得到值88（近似值），用該值代替內(nèi)層查詢，得到外層查詢： SELECT Sno， Cno FROM SC x WHERE Grade =88；,An In

21、troduction to Database System,帶有比較運(yùn)算符的子查詢（續(xù)）,3. 執(zhí)行這個(gè)查詢，得到 （200215121，1） （200215121，3） 4.外層查詢?nèi)〕鱿乱粋€(gè)元組重復(fù)做上述1至3步驟，直到外層的SC元組全部處理完畢。結(jié)果為: （200215121，1） （200215121，3） （200215122，2）,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運(yùn)算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introdu

22、ction to Database System,三、帶有ANY（SOME）或ALL謂詞的子查詢,謂詞語(yǔ)義 ANY：任意一個(gè)值 ALL：所有值,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,需要配合使用比較運(yùn)算符 ANY大于子查詢結(jié)果中的某個(gè)值 ALL大于子查詢結(jié)果中的所有值 = ANY大于等于子查詢結(jié)果中的某個(gè)值 = ALL大于等于子查詢結(jié)果中的所有值 ）ANY不等于子查詢結(jié)果中的某個(gè)值 !=（或）ALL不等于子查詢結(jié)果中的任何一個(gè)值,An Introduction to Database System,帶有ANY（

23、SOME）或ALL謂詞的子查詢 （續(xù)）,例42 查詢其他系中比計(jì)算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage CS ; /*父查詢塊中的條件 */,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,結(jié)果： 執(zhí)行過(guò)程： 1.RDBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出 CS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(20，19) 2. 處理父查詢，找所有不是CS系且年齡小于 20 或 19的學(xué)生,An Introduction to Database Sys

24、tem,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,用聚集函數(shù)實(shí)現(xiàn)例42 SELECT Sname，Sage FROM Student WHERE Sage CS ;,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例43 查詢其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage CS ;,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （

25、續(xù)）,方法二：用聚集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage CS ;,An Introduction to Database System,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,表3.5 ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價(jià)轉(zhuǎn)換關(guān)系,An Introduction to Database System,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運(yùn)算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,An Introduction to Data

26、base System,帶有EXISTS謂詞的子查詢(續(xù)）,1. EXISTS謂詞 存在量詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值 若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值 由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用* ，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無(wú)實(shí)際意義 2. NOT EXISTS謂詞 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值 若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值,An Introduction to Datab

27、ase System,帶有EXISTS謂詞的子查詢(續(xù)）,例44查詢所有選修了1號(hào)課程的學(xué)生姓名。 思路分析： 本查詢涉及Student和SC關(guān)系 在Student中依次取每個(gè)元組的Sno值，用此值去檢查SC關(guān)系 若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno= 1，則取此Student.Sname送入結(jié)果關(guān)系,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=S

28、tudent.Sno AND Cno= 1 )；,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,用連接運(yùn)算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1;,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,例45 查詢沒有選修1號(hào)課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno =

29、 Student.Sno AND Cno=1)；,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,不同形式的查詢間的替換 一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換 所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換 用EXISTS/NOT EXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn)) SQL語(yǔ)言中沒有全稱量詞 （For all） 可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞： (x)P ( x( P),An Introduction to Databas

30、e System,帶有EXISTS謂詞的子查詢(續(xù)）,例：例39查詢與“張三”在同一個(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 = 張三)；,S1表中給定一行,S2表中檢索所有行,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,例46 查詢選修了全部課程的學(xué)生姓名。 查詢這樣的學(xué)生，沒有一門課

31、是他不選的。 SELECT Sname FROM Student WHERE NOT EXISTS -不存在 （SELECT * FROM Course-有這樣一條記錄（有這樣一門課） -這個(gè)學(xué)生沒有選這門課（不存在這樣一門課，這個(gè)學(xué)生選了的） WHERE NOT EXISTS-不存在 (SELECT * FROM SC WHERE Sno= Student.Sno-這個(gè)學(xué)生 AND Cno= Course.Cno-這門課 ） ）；,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,用EXISTS/NOT EXISTS實(shí)現(xiàn)邏輯蘊(yùn)函(難點(diǎn))

32、 SQL語(yǔ)言中沒有蘊(yùn)函(Implication)邏輯運(yùn)算 可以利用謂詞演算將邏輯蘊(yùn)函謂詞等價(jià)轉(zhuǎn)換為： p q pq,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,例47查詢至少選修了學(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,An Introduction to Database System,帶

33、有EXISTS謂詞的子查詢(續(xù)）,等價(jià)變換： (y)p q (y (p q ) (y ( p q) ) y(pq) 變換后語(yǔ)義：不存在這樣的課程y，學(xué)生95002選修了y，而學(xué)生x沒有選。,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù)）,用NOT EXISTS謂詞表示： SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS -不存在 (SELECT * FROM SC SCY -有95002選的一門課 WHERE SCY.Sno = 95002 AND -這個(gè)學(xué)生沒有選95002選的這門課 N

34、OT EXISTS -不存在 (SELECT * FROM SC SCZ -有這樣一條選課記錄 WHERE SCZ.Sno=SCX.Sno -這個(gè)學(xué)生 AND SCZ.Cno=SCY.Cno)；-這門課,An Introduction to Database System,3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語(yǔ)句的一般形式,An Introduction to Database System,3.4.4 集合查詢,集合操作的種類 并操作UNION 交操作INTERSECT 差操作EXCEPT 參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同,An Introduction to Database System,集合查詢（續(xù)）,例48 查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19； UNION：將多個(gè)