1、數據庫系統概論 An Introduction to Database System 第三章 關系數據庫標準語言SQL (續(xù)1）,1查詢選修7號課程學生的學號和成績，按成績降序排列 2查詢選修表的所有信息，按課程號升序，課程成績降序排列 3查詢選修了課程的學生學號 4查詢計算機系年齡在20歲以下的學生姓名。 5查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。,復習單表查詢1,1查詢選修7號課程學生的學號和成績，按成績降序排列 SELECT Sno, Grade FROM SC WHERE Cno=7 ORDER BY Grade DESC 2查詢選修表的所有信息，按課

2、程號升序，課程成績降序排列 SELECT Cno, Sno, Grade FROM SC ORDER BY Cno, Grade DESC 3查詢選修了課程的學生學號 SELECT Sno FROM SC； 4查詢計算機系年齡在20歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20； 5查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS ),查詢名字中第2個字為陽字的學生的姓名和學號。

3、 查詢所有不姓劉的學生姓名。 查詢缺少成績的學生的學號和相應的課程號。 查所有有成績的學生學號和課程號。 查詢計算機系年齡在20歲以下的學生姓名。,復習單表查詢2,查詢名字中第2個字為陽字的學生的姓名和學號。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _陽%； 查詢所有不姓劉的學生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 劉%； 查詢缺少成績的學生的學號和相應的課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL 4 查所有

4、有成績的學生學號和課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL； 5 查詢計算機系年齡在20歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；,復習單表查詢3,1、找出全體男生的總人數 2、找出選修了課程的學生總人數 3、找出7號課程的最高分、最低分和平均分 4、找出各個系的學生人數 5、找出各個系男女學生的人數,6、找出選修了3門以上課程的學生的學號 7、找出門門課程的成績在80分以上的學生的學號 8、找出均分90分且沒有課程成績80分的學生的學號 9、找出至

5、少有2門課程不及格的學生的學號,1找出全體男生的總人數 SELECT COUNT(*) FROM S WHERE Ssex=男 2找出選修了課程的學生總人數 SELECT COUNT(DISTINCT Sno) FROM SC 3找出7號課程的最高分、最低分和平均分 SELECT MAX(Grade), MIN(Grade), AVG(Grade) FROM SC WHERE Cno=7,4找出各個系的學生人數 SELECT Sdept, COUNT(*) FROM S GROUP BY Sdept 5找出各個系男女學生的人數 SELECT Sdept, Ssex, COUNT(*) FROM

6、 S GROUP BY Sdept, Ssex,6找出選修了3門以上課程的學生的學號 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)3 7找出門門課程的成績在80分以上的學生的學號 SELECT Sno FROM SC GROUP BY Sno HAVING MIN(Grade)80,8找出均分90分且沒有課程成績90 AND MIN(Grade)=80 9找出至少有2門課程不及格的學生的學號 SELECT Sno FROM SC WHERE Grade=2,3.4 數據查詢,3.4.1 單表查詢 3.4.2 連接查詢 3.4.3 嵌套查詢

7、3.4.4 集合查詢 3.4.5 Select語句的一般形式,3.4.2 連接查詢,連接查詢：同時涉及多個表的查詢 連接條件或連接謂詞：用來連接兩個表的條件 一般格式： . . . BETWEEN . AND .,3.4.2 連接查詢(1) 舉例,找出選修了“數據結構”課程的學生學號及成績 SELECT Sno, Grade FROM SC, C WHERE SC.Cno=C.Cno AND Cname=數據結構 用SQL92中的連接格式可寫為： SELECT Sno, Grade FROM SC JOIN C ON SC.Cno=C.Cno WHERE Cname=數據結構,3.4.2 連接

8、查詢(2) 舉例,找出選修了“數據結構”課程的學生姓名及成績 SELECT Sname, Grade FROM S, SC, C WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND Cname=數據結構 用SQL92中的連接格式可寫為： SELECT Sname, Grade FROM S JOIN SC ON S.Sno=SC.Sno JOIN C ON SC.Cno=C.Cno WHERE Cname=數據結構,SELECT FROM WHERE語句稱為查詢塊 嵌套查詢：將查詢塊嵌套在另一個查詢塊的WHERE 子句或HAVING 短語中 意義： 體現編程“結構化

9、”的含義 可以用多個簡單查詢進行復雜的查詢,3.4.3 嵌套查詢,3.4.3 嵌套查詢,找出選修了1號課程的學生姓名及性別 SELECT Sname, Ssex FROM S WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno=1 ) 說明： 嵌套查詢的執(zhí)行過程是由里向外進行 子查詢中不能使用ORDER BY子句 理論上用子查詢的速度要快,外層查詢 (父查詢),內層查詢 (子查詢),例39 查詢與“劉晨”在同一個系學習的學生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 劉晨

10、； 結果為： CS,3.4.3 嵌套查詢,嵌套查詢的執(zhí)行過程是由里向外進行:, 查找所有在CS系學習的學生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= CS ； 結果為：,3.4.3 嵌套查詢,找出與“劉晨”在同一個系學習的學生的姓名、性別及年齡 SELECT Sname, Ssex, Sage FROM S,自連接時使用別名,WHERE Sdept =,嵌套查詢有時可以用連接查詢來實現 SELECT S1.Sname , S1.Ssex , S1.Sage FROM S S1 , S S2 WHERE S1.Sdept=S2.Sdept

11、 AND S2.Sname= 劉晨,( SELECT Sdept FROM S WHERE Sname= 劉晨 ),帶有IN謂詞的子查詢（續(xù)）,查詢選修了課程名為“信息系統”的學生學號和姓名 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 “信息系統”的課程號，為3號 WHERE Cname= 信息系統 ) );,復

12、合條件連接與嵌套查詢比較,復合條件連接：WHERE子句中含多個連接條件 例37查詢選修2號課程且成績在90分以上的所有學生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 連接謂詞*/ SC.Cno= 2 AND SC.Grade 90； /* 其他限定條件 */ 可以用嵌套查詢實現？,SELECT Student.Sno, Sname FROM Student WHERE Sno IN （ SELECT Sno FROM SC WHERE CNO= 2 AND Grade 90）,嵌套查

13、詢(續(xù)),子查詢的限制 不能使用ORDER BY子句 層層嵌套方式反映了 SQL語言的結構化 有些嵌套查詢可以用連接運算替代,常見錯誤詢,多表連接時，對不唯一的屬性忘記了表名前綴 SELECT Sno, Sname, Cno, Grade,WHERE條件中出現集函數（統計函數） WHERE MIN (Grade)80,非GROUP BY中的屬性出現在SELECT后面，且未用統計函數 SELECT Sno, Cno, AVG(Grade) From Sc GROUP by Sno,有HAVING短語，但沒有GROUP BY子句,HAVING中出現沒有統計函數的屬性 HAVING Cno 1 AN

14、D AVG(Grade)80,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢,二、帶有比較運算符的子查詢,當能確切知道內層查詢返回單值時，可用比較運算符（，=，）。 與ANY或ALL謂詞配合使用,帶有比較運算符的子查詢（續(xù)）,例：假設一個學生只可能在一個系學習，并且必須屬于一個系，則在例39可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 劉晨)；,帶有比較運算

15、符的子查詢（續(xù)）,子查詢一定要跟在比較符之后 錯誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept；,帶有比較運算符的子查詢（續(xù)）,例41找出每個學生超過他選修課程平均成績的課程號。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);,相關子查詢,帶有比較運算符的子查詢（續(xù)）,可能的執(zhí)行過程： 1. 從外層查詢中取出SC的

16、一個元組x，將元組x的Sno值（200215121）傳送給內層查詢。 SELECT AVG(Grade) FROM SC y WHERE y.Sno=200215121; 2. 執(zhí)行內層查詢，得到值88（近似值），用該值代替內層查詢，得到外層查詢： SELECT Sno， Cno FROM SC x WHERE Grade =88；,帶有比較運算符的子查詢（續(xù)）,3. 執(zhí)行這個查詢，得到 （200215121，1） （200215121，3） 4.外層查詢取出下一個元組重復做上述1至3步驟，直到外層的SC元組全部處理完畢。結果為: （200215121，1） （200215121，3） （20

17、0215122，2）,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME）或ALL謂詞的子查詢,三、帶有ANY（SOME）或ALL謂詞的子查詢,謂詞語義 ANY：任意一個值 ALL：所有值,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,需要配合使用比較運算符 ANY大于子查詢結果中的某個值 ALL大于子查詢結果中的所有值 = ANY大于等于子查詢結果中的某個值 = ALL大于等于子查詢結果中的所有值 ）ANY不等于子查詢結果中的某個值 !=（或）ALL不等于子查詢結果中的任何一個值,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,

18、例42 查詢其他系中比計算機科學某一學生年齡小的學生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage CS ; /*父查詢塊中的條件 */,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,結果： 執(zhí)行過程： 1.RDBMS執(zhí)行此查詢時，首先處理子查詢，找出 CS系中所有學生的年齡，構成一個集合(20，19) 2. 處理父查詢，找所有不是CS系且年齡小于 20 或 19的學生,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,用聚集函數實現例42 SELECT Sname，Sage FROM Student WHERE Sage CS ;,帶有

19、ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,例43 查詢其他系中比計算機科學系所有學生年齡都小的學生姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage CS ;,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,方法二：用聚集函數 SELECT Sname，Sage FROM Student WHERE Sage CS ;,帶有ANY（SOME）或ALL謂詞的子查詢 （續(xù)）,表3.5 ANY（或SOME），ALL謂詞與聚集函數、IN謂詞的等價轉換關系,3.4.3 嵌套查詢,一、帶有IN謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有ANY（SOME