1、1,數(shù)據(jù)庫原理 Principle of Database Lecture 4 （續(xù)） 第三章 SQL語言(1),2,3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語句的一般形式,3,3.4.2 連接查詢 （P100）,連接查詢：同時涉及多個表的查詢 連接條件或連接謂詞：用來連接兩個表的條件 一般格式： . . . BETWEEN . AND . 連接條件中的各連接字段類型必須是可比的，但名字不必是相同的,4,連接操作的執(zhí)行過程,嵌套循環(huán)法(NESTED-LOOP) 首先在表1中找到第一個元組，然后從頭開始掃描

2、表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。 表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結果表中一個元組。 重復上述操作，直到表1中的全部元組都處理完畢,5,索引連接(INDEX-JOIN),對表2按連接字段建立索引 對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組,6,連接查詢（續(xù)）,一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復

3、合條件連接,7,一、等值與非等值連接查詢,等值連接：連接運算符為= 例33 查詢每個學生及其選修課程的情況 SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；,8,等值與非等值連接查詢（續(xù)）,查詢結果：,9,等值與非等值連接查詢（續(xù)）,自然連接： 例34 對例33用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；,10,連接查詢（續(xù)）,一、等值與非等值連接查詢

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

5、二、自身連接 三、外連接 四、復合條件連接,16,三、外連接,外連接與普通連接的區(qū)別 普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出 例 36 改寫例33 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)；,17,外連接（續(xù)）,執(zhí)行結果：,18,外連接（續(xù)）,左外連接 列出左邊關系（如本例Student）中所有的元組 右外連接 列出右邊關系中所有的元組,19,連接查詢（續(xù)）,一

6、、等值與非等值連接查詢 二、自身連接 三、外連接 四、復合條件連接,20,四、復合條件連接,復合條件連接：WHERE子句中含多個連接條件 例37查詢選修2號課程且成績在90分以上的所有學生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 連接謂詞*/ SC.Cno= 2 AND SC.Grade 90； /* 其他限定條件 */,21,復合條件連接（續(xù)）,例38查詢每個學生的學號、姓名、選修的課程名及成績 SELECT Student.Sno，Sname，Cname，Grade FROM

7、Student，SC，Course /*多表連接*/ WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；,22,3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語句的一般形式,23,嵌套查詢(續(xù)),嵌套查詢概述 一個SELECT-FROM-WHERE語句稱為一個查詢塊 將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢,24,嵌套查詢(續(xù)),SELECT Sname /*外層查詢/父查詢*/ FROM Student WH

8、ERE Sno IN （SELECT Sno /*內(nèi)層查詢/子查詢*/ FROM SC WHERE Cno= 2 ）；,25,嵌套查詢(續(xù)),子查詢的限制 不能使用ORDER BY子句，只對最終結果排序 層層嵌套方式反映了 SQL語言的結構化 有些嵌套查詢可以用連接運算替代,26,嵌套查詢求解方法,不相關子查詢： 子查詢的查詢條件不依賴于父查詢 由里向外 逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。,27,嵌套查詢求解方法（續(xù)）,相關子查詢：子查詢的查詢條件依賴于父查詢 首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關的屬性值處理內(nèi)層查詢，若WH

9、ERE子句返回值為真，則取此元組放入結果表 然后再取外層表的下一個元組 重復這一過程，直至外層表全部檢查完為止,28,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,29,一、帶有IN謂詞的子查詢,例39 查詢與“劉晨”在同一個系學習的學生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 劉晨 ； 結果為： CS,30,帶有IN謂詞的子查詢（續(xù)）, 查找所有在IS系學習的學生。 SELECT Sno，

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

11、 S2.Sdept AND S2.Sname = 劉晨；,33,帶有IN謂詞的子查詢（續(xù)）,例40查詢選修了課程名為“信息系統(tǒng)”的學生學號和姓名 SELECT Sno，Sname 最后在Student關系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC關系中找出選 FROM SC 修了3號課程的學生學號 WHERE Cno IN (SELECT Cno 首先在Course關系中找出 FROM Course “信息系統(tǒng)”的課程號，為3號 WHERE Cname= 信息系統(tǒng) ) );,34,帶有IN謂詞的子查詢（續(xù)）,用連接查詢實現(xiàn)

12、例40 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系統(tǒng)；,35,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,36,二、帶有比較運算符的子查詢,當能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，）。 與ANY或ALL謂詞配合使用,37,帶有比較運算符的子查詢（續(xù)）,例：假設一個學生只可能在一個系學習

13、，并且必須屬于一個系，則在例39可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 劉晨)；,38,帶有比較運算符的子查詢（續(xù)）,子查詢一定要跟在比較符之后 錯誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept；,39,帶有比較運算符的子查詢（續(xù)）,例41找出每個學生超過他選修課程平均成績的課

14、程號。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);,相關子查詢,40,帶有比較運算符的子查詢（續(xù)）,可能的執(zhí)行過程： 1. 從外層查詢中取出SC的一個元組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 Grad

15、e =88；,41,帶有比較運算符的子查詢（續(xù)）,3. 執(zhí)行這個查詢，得到 （200215121，1） （200215121，3） 4.外層查詢?nèi)〕鱿乱粋€元組重復做上述1至3步驟，直到外層的SC元組全部處理完畢。結果為: （200215121，1） （200215121，3） （200215122，2）,42,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,43,三、帶有ANY（SOME）或ALL謂詞的子查詢,謂詞語義 ANY：任意一個值 ALL：所有值,44,帶有ANY（SOM

16、E）或ALL謂詞的子查詢 （續(xù)）,需要配合使用比較運算符 ANY大于子查詢結果中的某個值 ALL大于子查詢結果中的所有值 = ANY大于等于子查詢結果中的某個值 = ALL大于等于子查詢結果中的所有值 ）ANY不等于子查詢結果中的某個值 !=（或）ALL不等于子查詢結果中的任何一個值,45,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例42 查詢其他系中比計算機科學某一學生年齡小的學生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage CS ; /*父查詢塊中的條件 */,46,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,結果： 執(zhí)行

17、過程： 1.RDBMS執(zhí)行此查詢時，首先處理子查詢，找出 CS系中所有學生的年齡，構成一個集合(20，19) 2. 處理父查詢，找所有不是CS系且年齡小于 20 或 19的學生,47,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,用聚集函數(shù)實現(xiàn)例42 SELECT Sname，Sage FROM Student WHERE Sage CS ;,48,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例43 查詢其他系中比計算機科學系所有學生年齡都小的學生姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage CS ;,49

18、,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,方法二：用聚集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage CS ;,50,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,表3.5 ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價轉換關系,51,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢 四、 帶有EXISTS謂詞的子查詢,52,帶有EXISTS謂詞的子查詢(續(xù)）,1. EXISTS謂詞 存在量詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生

19、邏輯真值“true”或邏輯假值“false”。 若內(nèi)層查詢結果非空，則外層的WHERE子句返回真值 若內(nèi)層查詢結果為空，則外層的WHERE子句返回假值 由EXISTS引出的子查詢，其目標列表達式通常都用* ，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義 2. NOT EXISTS謂詞 若內(nèi)層查詢結果非空，則外層的WHERE子句返回假值 若內(nèi)層查詢結果為空，則外層的WHERE子句返回真值,53,帶有EXISTS謂詞的子查詢(續(xù)）,例44查詢所有選修了1號課程的學生姓名。 思路分析： 本查詢涉及Student和SC關系 在Student中依次取每個元組的Sno值，用此值去檢查SC

20、關系 若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno= 1，則取此Student.Sname送入結果關系,54,帶有EXISTS謂詞的子查詢(續(xù)）,用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )；,55,帶有EXISTS謂詞的子查詢(續(xù)）,用連接運算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1;,56,帶有EXISTS謂詞的

21、子查詢(續(xù)）,例45 查詢沒有選修1號課程的學生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；,57,帶有EXISTS謂詞的子查詢(續(xù)）,不同形式的查詢間的替換 一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換 所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換 用EXISTS/NOT EXISTS實現(xiàn)全稱量詞(難點) SQL語言中沒有全稱量詞 （For all）

22、可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞： (x)P ( x( P),58,帶有EXISTS謂詞的子查詢(續(xù)）,例：例39查詢與“劉晨”在同一個系學習的學生。 可以用帶EXISTS謂詞的子查詢替換： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 劉晨)；,59,帶有EXISTS謂詞的子查詢(續(xù)）,例46 查詢選修了全部課程的學生姓名。 SELECT Sname FROM Student WH

23、ERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；,找不出這樣的課程,學生沒有選修,60,帶有EXISTS謂詞的子查詢(續(xù)）,用EXISTS/NOT EXISTS實現(xiàn)邏輯蘊函(難點) SQL語言中沒有蘊函(Implication)邏輯運算 可以利用謂詞演算將邏輯蘊函謂詞等價轉換為： p q pq,61,帶有EXISTS謂詞的子查詢(續(xù)）,例47查詢至少選修了學生200215122選修的全部課程的學生號碼。 解

24、題思路： 用邏輯蘊函表達：查詢學號為x的學生，對所有的課程y，只要200215122學生選修了課程y，則x也選修了y。 形式化表示： 用P表示謂詞 “學生200215122選修了課程y” 用q表示謂詞 “學生x選修了課程y” 則上述查詢?yōu)? (y) p q,62,帶有EXISTS謂詞的子查詢(續(xù)）,等價變換： (y)p q (y (p q ) (y ( p q) ) y(pq) 變換后語義：不存在這樣的課程y，學生200215122選修了y，而學生x沒有選。,63,帶有EXISTS謂詞的子查詢(續(xù)）,用NOT EXISTS謂詞表示： SELECT DISTINCT Sno FROM SC SC

25、X WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 200215122 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；,64,3.4 數(shù)據(jù)查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢 3.4.4 集合查詢 3.4.5 Select語句的一般形式,65,3.4.4 集合查詢,集合操作的種類 并操作UNION 交操作INTERSECT 差操作EXCEPT 參加集合操作的各查詢結果的列數(shù)必須相同；對應

26、項的數(shù)據(jù)類型也必須相同,66,集合查詢（續(xù)）,例48 查詢計算機科學系的學生及年齡不大于19歲的學生。 方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19； UNION：將多個查詢結果合并起來時，系統(tǒng)自動去掉重復元組。 UNION ALL：將多個查詢結果合并起來時，保留重復元組,67,集合查詢（續(xù)）,方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；,68,集合查詢（續(xù)）,例49 查詢選修了課程1或者選修了課程2的學生。 SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT Sno FROM SC WHERE Cno= 2 ；,69