數據庫系統(tǒng)概論PPT教程第三章 關系數據庫標準語言SQL(續(xù)1)

1、an introduction to database system 數據庫系統(tǒng)概論數據庫系統(tǒng)概論 an introduction to database system 第三章第三章 關系數據庫標準語言關系數據庫標準語言sql sql (續(xù)續(xù)1） an introduction to database system 3.4 數據查詢數據查詢 v3.4.1 單表查詢單表查詢 v3.4.2 連接查詢連接查詢 v3.4.3 嵌套查詢嵌套查詢 v3.4.4 集合查詢集合查詢 v3.4.5 select語句的一般形式語句的一般形式 an introduction to database system 3

2、.4.2 連接查詢連接查詢 v 連接查詢：同時涉及多個表的查詢 v 連接條件或連接謂詞：用來連接兩個表的條件 一般格式： n . . n . between . and . v 連接字段：連接謂詞中的列名稱 n 連接條件中的各連接字段類型必須是可比的，但名字不必是相同的 an introduction to database system 連接操作的執(zhí)行過程連接操作的執(zhí)行過程 v 嵌套循環(huán)法(nested-loop) 首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找 滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼 接起來，形成結果表中一個元組。 表2全部查找完后，再找表1中

3、第二個元組，然后再從頭開始掃描 表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個 元組與該元組拼接起來，形成結果表中一個元組。 重復上述操作，直到表1中的全部元組都處理完畢 an introduction to database system 排序合并法排序合并法(sort-merge) 常用于=連接 首先按連接屬性對表1和表2排序 對表1的第一個元組，從頭開始掃描表2，順序查找滿足 連接條件的元組，找到后就將表1中的第一個元組與該 元組拼接起來，形成結果表中一個元組。當遇到表2中 第一條大于表1連接字段值的元組時，對表2的查詢不再 繼續(xù) an introduction to dat

4、abase system 排序合并法排序合并法 找到表1的第二條元組，然后從剛才的中斷點處繼續(xù)順 序掃描表2，查找滿足連接條件的元組，找到后就將表1 中的第一個元組與該元組拼接起來，形成結果表中一個 元組。直接遇到表2中大于表1連接字段值的元組時，對 表2的查詢不再繼續(xù) 重復上述操作，直到表1或表2中的全部元組都處理完畢 為止 an introduction to database system 索引連接索引連接(index-join) 對表2按連接字段建立索引 對表1中的每個元組，依次根據其連接字段值查詢 表2的索引，從中找到滿足條件的元組，找到后就 將表1中的第一個元組與該元組拼接起來，形

5、成結 果表中一個元組 an introduction to database system 連接查詢（續(xù)）連接查詢（續(xù)） 一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復合條件連接 an introduction to database system 一、等值與非等值連接查詢一、等值與非等值連接查詢 v 等值連接：連接運算符為= 例33 查詢每個學生及其選修課程的情況 select student.*，sc.* from student，sc where student.sno = sc.sno； an introduction to database system 等值與非等值連接

6、查詢（續(xù)）等值與非等值連接查詢（續(xù)） student.snosnamessexsagesdeptsc.snocnograde 200215121李勇男20cs200215121192 200215121李勇男20cs200215121285 200215121李勇男20cs200215121388 200215122劉晨女19cs200215122290 200215122劉晨女19cs200215122380 查詢結果：查詢結果： an introduction to database system 等值與非等值連接查詢（續(xù)）等值與非等值連接查詢（續(xù)） v自然連接： 例34 對例33用自然連

7、接完成。 select student.sno，sname，ssex，sage，sdept，cno，grade from student，sc where student.sno = sc.sno； an introduction to database system 連接查詢（續(xù)）連接查詢（續(xù)） 一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復合條件連接 an introduction to database system 二、自身連接二、自身連接 v 自身連接：一個表與其自己進行連接 v 需要給表起別名以示區(qū)別 v 由于所有屬性名都是同名屬性，因此必須使用別名前綴 例35查詢每一

8、門課的間接先修課（即先修課的先修課） select first.cno，second.cpno from course first，course second where first.cpno = second.cno； an introduction to database system 自身連接（續(xù)）自身連接（續(xù)） first表（course表） cno cnamecpnoccredit 1數據庫數據庫 5 4 2數學數學 2 3信息系統(tǒng)信息系統(tǒng) 1 4 4操作系統(tǒng)操作系統(tǒng) 6 3 5數據結構數據結構 7 4 6數據處理數據處理 2 7pascal語言語言 6 4 an introduct

9、ion to database system 自身連接（續(xù)）自身連接（續(xù)） cno cnamecpnoccredit 1數據庫數據庫 5 4 2數學數學 2 3信息系統(tǒng)信息系統(tǒng) 1 4 4操作系統(tǒng)操作系統(tǒng) 6 3 5數據結構數據結構 7 4 6數據處理數據處理 2 7pascal語言語言 6 4 second表（course表） an introduction to database system 自身連接（續(xù)）自身連接（續(xù)） 查詢結果： cnopcno 17 35 56 an introduction to database system 連接查詢（續(xù)）連接查詢（續(xù)） 一、等值與非等值連接查

10、詢 二、自身連接 三、外連接 四、復合條件連接 an introduction to database system 三、外連接三、外連接 v 外連接與普通連接的區(qū)別 普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的 元組一并輸出 例 36 改寫例33 select student.sno，sname，ssex，sage，sdept，cno，grade from student left out join sc on (student.sno=sc.sno)； an introduction to database system 外連接（續(xù)）外連接

11、（續(xù)） 執(zhí)行結果：執(zhí)行結果： student.snosnamessexsagesdeptcnograde 200215121李勇男20cs192 200215121李勇男20cs285 200215121李勇男20cs388 200215122劉晨女19cs290 200215122劉晨女19cs380 200215123王敏女18manullnull 200215125張立男19isnullnull an introduction to database system 外連接（續(xù)）外連接（續(xù)） v 左外連接 列出左邊關系（如本例student）中所有的元組 v 右外連接 列出右邊關系中所有的

12、元組 an introduction to database system 連接查詢（續(xù)）連接查詢（續(xù)） 一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復合條件連接 an introduction to database system 四、復合條件連接四、復合條件連接 v復合條件連接：where子句中含多個連接條件 例37查詢選修2號課程且成績在90分以上的所有學生 select student.sno, sname from student, sc where student.sno = sc.sno and /* 連接謂詞*/ sc.cno= 2 and sc.grade 90；

13、 /* 其他限定條件 */ an introduction to database system 復合條件連接（續(xù)）復合條件連接（續(xù)） 例38查詢每個學生的學號、姓名、選修的課程名及成績 select student.sno，sname，cname，grade from student，sc，course /*多表連接多表連接*/ where student.sno = sc.sno and sc.cno = course.cno； an introduction to database system 3.4 數據查詢數據查詢 v3.4.1 單表查詢單表查詢 v3.4.2 連接查詢連接查詢 v

14、3.4.3 嵌套查詢嵌套查詢 v3.4.4 集合查詢集合查詢 v3.4.5 select語句的一般形式語句的一般形式 an introduction to database system 嵌套查詢嵌套查詢(續(xù)續(xù)) v嵌套查詢概述 一個select-from-where語句稱為一個查詢塊查詢塊 將一個查詢塊嵌套在另一個查詢塊的where子句 或having短語的條件中的查詢稱為嵌套查詢嵌套查詢 an introduction to database system 嵌套查詢嵌套查詢(續(xù)續(xù)) select sname /*外層查詢/父查詢*/ from student where sno in （s

15、elect sno /*內層查詢/子查詢*/ from sc where cno= 2 ）； an introduction to database system 嵌套查詢嵌套查詢(續(xù)續(xù)) 子查詢的限制 不能使用order by子句 層層嵌套方式反映了 sql語言的結構化 有些嵌套查詢可以用連接運算替代 an introduction to database system 嵌套查詢求解方法嵌套查詢求解方法 v不相關子查詢： 子查詢的查詢條件不依賴于父查詢 n由里向外 逐層處理。即每個子查詢在上一級查詢處理 之前求解，子查詢的結果用于建立其父查詢的查找條 件。 an introduction t

16、o database system 嵌套查詢求解方法（續(xù)）嵌套查詢求解方法（續(xù)） v相關子查詢：子查詢的查詢條件依賴于父查詢 首先取外層查詢中表的第一個元組，根據它與內層查 詢相關的屬性值處理內層查詢，若where子句返回值 為真，則取此元組放入結果表 然后再取外層表的下一個元組 重復這一過程，直至外層表全部檢查完為止 an introduction to database system 3.4.3 嵌套查詢嵌套查詢 一、帶有in謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有any（some）或all謂詞的子查詢 四、 帶有exists謂詞的子查詢 an introduction to

17、 database system 一、帶有一、帶有in謂詞的子查詢謂詞的子查詢 例39 查詢與“劉晨”在同一個系學習的學生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 select sdept from student where sname= 劉晨 ； 結果為： cs an introduction to database system 帶有帶有in謂詞的子查詢（續(xù)）謂詞的子查詢（續(xù)） 查找所有在is系學習的學生。 select sno，sname，sdept from student where sdept= cs ； 結果為： snosnamesdept 200215121李勇cs

18、 200215122劉晨cs an introduction to database system 帶有帶有in謂詞的子查詢（續(xù)）謂詞的子查詢（續(xù)） 將第一步查詢嵌入到第二步查詢的條件中 select sno，sname，sdept from student where sdept in (select sdept from student where sname= 劉晨 )； 此查詢?yōu)椴幌嚓P子查詢。 an introduction to database system 帶有帶有in謂詞的子查詢（續(xù)）謂詞的子查詢（續(xù)） 用自身連接完成例39查詢要求 select s1.sno，s1.sname

19、，s1.sdept from student s1，student s2 where s1.sdept = s2.sdept and s2.sname = 劉晨； an introduction to database system 帶有帶有in謂詞的子查詢（續(xù)）謂詞的子查詢（續(xù)） 例40查詢選修了課程名為“信息系統(tǒng)”的學生學號和姓名 select sno，sname 最后在student關系中 from student 取出sno和sname where sno in (select sno 然后在sc關系中找出選 from sc 修了3號課程的學生學號 where cno in (sele

20、ct cno 首先在course關系中找出 from course “信息系統(tǒng)”的課程號，為3 號 where cname= 信息系統(tǒng) ) ); an introduction to database system 帶有帶有in謂詞的子查詢（續(xù)）謂詞的子查詢（續(xù)） 用連接查詢實現例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 嵌

21、套查詢嵌套查詢 一、帶有in謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有any（some）或all謂詞的子查詢 四、 帶有exists謂詞的子查詢 an introduction to database system 二、帶有比較運算符的子查詢二、帶有比較運算符的子查詢 v 當能確切知道內層查詢返回單值時，可用比較運 算符（，=，=，!=或）。 v與any或all謂詞配合使用 an introduction to database system 帶有比較運算符的子查詢（續(xù)）帶有比較運算符的子查詢（續(xù)） 例：假設一個學生只可能在一個系學習，并且必須屬于一個 系，則在例39可以用 = 代

22、替in ： select sno，sname，sdept from student where sdept = (select sdept from student where sname= 劉晨)； an introduction to database system 帶有比較運算符的子查詢（續(xù)）帶有比較運算符的子查詢（續(xù)） 子查詢一定要跟在比較符之后 錯誤錯誤的例子： select sno，sname，sdept from student where ( select sdept from student where sname= 劉晨 ) = sdept； an introduction

23、 to database system 帶有比較運算符的子查詢（續(xù)）帶有比較運算符的子查詢（續(xù)） 例41找出每個學生超過他選修課程平均成績的課程號。 select sno， cno from sc x where grade =(select avg(grade) from sc y where y.sno=x.sno); 相關子查詢相關子查詢 an introduction to database system 帶有比較運算符的子查詢（續(xù)）帶有比較運算符的子查詢（續(xù)） v 可能的執(zhí)行過程：可能的執(zhí)行過程： 1. 從外層查詢中取出sc的一個元組x，將元組x的sno值 （200215121）傳送

24、給內層查詢。 select avg(grade) from sc y where y.sno=200215121; 2. 執(zhí)行內層查詢，得到值88（近似值），用該值代替內層查 詢，得到外層查詢： select sno， cno from sc x where grade =88； an introduction to database system 帶有比較運算符的子查詢（續(xù)）帶有比較運算符的子查詢（續(xù)） 3. 執(zhí)行這個查詢，得到 （200215121，1） （200215121，3） 4.外層查詢取出下一個元組重復做上述1至3步驟，直到外層 的sc元組全部處理完畢。結果為: （2002151

25、21，1） （200215121，3） （200215122，2） an introduction to database system 3.4.3 嵌套查詢嵌套查詢 一、帶有in謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有any（some）或all謂詞的子查詢 四、 帶有exists謂詞的子查詢 an introduction to database system 三、帶有三、帶有any（some）或）或all謂詞的子查詢謂詞的子查詢 謂詞語義 any：某一個（一些）值 all：所有值 an introduction to database system 帶有帶有any（some）

26、或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 需要配合使用比較運算符 any大于子查詢結果中的某個值 all大于子查詢結果中的所有值 any小于子查詢結果中的某個值 = any大于等于子查詢結果中的某個值 = all大于等于子查詢結果中的所有值 = any小于等于子查詢結果中的某個值 = all小于等于子查詢結果中的所有值 = any等于子查詢結果中的某個值 =all等于子查詢結果中的所有值（通常沒有實際意義） !=（或）any不等于子查詢結果中的某個值 !=（或）all不等于子查詢結果中的任何一個值 an introduction to database system 帶有帶有any（

27、some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 例42 查詢其他系中比計算機科學某一學生年齡小的學生姓 名和年齡 select sname，sage from student where sage any (select sage from student where sdept= cs ) and sdept cs ; /*父查詢塊中的條件 */ an introduction to database system 帶有帶有any（some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 結果： 執(zhí)行過程： 1.rdbms執(zhí)行此查詢時，首先處理子查詢，找出 cs系中所有學生

28、的年齡，構成一個集合(20，19) 2. 處理父查詢，找所有不是cs系且年齡小于 20 或 19的學生 snamesage 王敏18 張立19 an introduction to database system 帶有帶有any（some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 用聚集函數實現例42 select sname，sage from student where sage (select max(sage) from student where sdept= cs ) and sdept cs ; an introduction to database system 帶有

29、帶有any（some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 例43 查詢其他系中比計算機科學系所有學生年齡都小 的學生姓名及年齡。 方法一：用all謂詞 select sname，sage from student where sage all (select sage from student where sdept= cs ) and sdept cs ; an introduction to database system 帶有帶有any（some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 方法二：用聚集函數 select sname，sage from stud

30、ent where sage (select min(sage) from student where sdept= cs ) and sdept cs ; an introduction to database system 帶有帶有any（some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 表3.5 any（或some），all謂詞與聚集函數、in謂詞的等價轉換關系 = 或或!= = any in - maxmin= min all - not in min max = max an introduction to database system 3.4.3 嵌套查詢嵌套查詢 一

31、、帶有in謂詞的子查詢 二、 帶有比較運算符的子查詢 三、 帶有any（some）或all謂詞的子查詢 四、 帶有exists謂詞的子查詢 an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） v 1. exists謂詞 n存在量詞 n帶有exists謂詞的子查詢不返回任何數據，只產生邏輯真值 “true”或邏輯假值“false”。 若內層查詢結果非空，則外層的where子句返回真值 若內層查詢結果為空，則外層的where子句返回假值 n由exists引出的子查詢，其目標列表達式通常都用* ，因為 帶exists的子查詢只

32、返回真值或假值，給出列名無實際意義 v 2. not exists謂詞 若內層查詢結果非空，則外層的where子句返回假值 若內層查詢結果為空，則外層的where子句返回真值 an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例44查詢所有選修了1號課程的學生姓名。 思路分析： n本查詢涉及student和sc關系 n在student中依次取每個元組的sno值，用此值去檢查sc關系 n若sc中存在這樣的元組，其sno值等于此student.sno值，并 且其cno= 1，則取此student.sname送入結果關系 a

33、n introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） n用嵌套查詢 select sname from student where exists (select * from sc where sno=student.sno and cno= 1 )； an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） n用連接運算 select sname from student, sc where student.sno=sc.sno and sc.cno= 1

34、; an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例45 查詢沒有選修1號課程的學生姓名。 select sname from student where not exists (select * from sc where sno = student.sno and cno=1)； an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） v 不同形式的查詢間的替換 n一些帶exists或not exists謂詞的子查詢不能被其他形式的子 查詢等

35、價替換 n所有帶in謂詞、比較運算符、any和all謂詞的子查詢都能用帶 exists謂詞的子查詢等價替換 v 用exists/not exists實現全稱量詞(難點) sql語言中沒有全稱量詞 （for all） 可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞： (x)p ( x( p) an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例：例39查詢與“劉晨”在同一個系學習的學生。 可以用帶exists謂詞的子查詢替換： select sno，sname，sdept from student s1 wher

36、e exists (select * from student s2 where s2.sdept = s1.sdept and s2.sname = 劉晨)； an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例46 查詢選修了全部課程的學生姓名。 select sname from student where not exists （select * from course where not exists (select * from sc where sno= student.sno and cno= cour

37、se.cno ） ）； an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 用exists/not exists實現邏輯蘊函(難點) sql語言中沒有蘊函(implication)邏輯運算 可以利用謂詞演算將邏輯蘊函謂詞等價轉換為： p q pq an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例47查詢至少選修了學生200215122選修的全部課程的學 生號碼。 解題思路： n用邏輯蘊函表達：查詢學號為x的學生，對所有的課程y，只要 2002

38、15122學生選修了課程y，則x也選修了y。 n形式化表示： 用p表示謂詞 “學生200215122選修了課程y” 用q表示謂詞 “學生x選修了課程y” 則上述查詢?yōu)? (y) p q an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） n等價變換： (y)p q (y (p q ) (y ( p q) ) y(pq) n變換后語義：不存在這樣的課程y，學生200215122選修了 y，而學生x沒有選。 an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)

39、）續(xù)） n用not exists謂詞表示： select distinct sno from sc scx 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)； an introduction to database system 3.4 數據查詢數據查詢 v3.4.1 單表查詢單表查詢 v3.4.2 連接查詢連接查詢 v3.4.3 嵌套查詢嵌套查詢 v3.4

40、.4 集合查詢集合查詢 v3.4.5 select語句的一般形式語句的一般形式 an introduction to database system 3.4.4 集合查詢集合查詢 v集合操作的種類 并操作union 交操作intersect 差操作except v參加集合操作的各查詢結果的列數必須相同；對 應項的數據類型也必須相同 an introduction to database system 集合查詢（續(xù)）集合查詢（續(xù)） 例48 查詢計算機科學系的學生及年齡不大于19歲的學生。 方法一： select * from student where sdept= cs union select * from student where sage=19； nunion：將多個查詢結果合并起來時，系統(tǒng)自動去掉重復元組。 nunion all：將多個查詢結果合并起來時，保留重復元組 an introduction to database s