數(shù)據(jù)庫系統(tǒng)概論P(yáng)PT教程第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL(續(xù)1)

1、an introduction to database system 數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)庫系統(tǒng)概論 an introduction to database system 第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言sql sql (續(xù)續(xù)1） an introduction to database system 3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢 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中的第一個元組與該元組拼 接起來，形成結(jié)果表中一個元組。 表2全部查找完后，再找表1中

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

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

5、成結(jié) 果表中一個元組 an introduction to database system 連接查詢（續(xù)）連接查詢（續(xù)） 一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接 an introduction to database system 一、等值與非等值連接查詢一、等值與非等值連接查詢 v 等值連接：連接運(yùn)算符為= 例33 查詢每個學(xué)生及其選修課程的情況 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 查詢結(jié)果：查詢結(jié)果： 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ù)） 一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接 an introduction to database system 二、自身連接二、自身連接 v 自身連接：一個表與其自己進(jìn)行連接 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數(shù)據(jù)庫數(shù)據(jù)庫 5 4 2數(shù)學(xué)數(shù)學(xué) 2 3信息系統(tǒng)信息系統(tǒng) 1 4 4操作系統(tǒng)操作系統(tǒng) 6 3 5數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu) 7 4 6數(shù)據(jù)處理數(shù)據(jù)處理 2 7pascal語言語言 6 4 an introduct

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

10、詢 二、自身連接 三、外連接 四、復(fù)合條件連接 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í)行結(jié)果：執(zhí)行結(jié)果： student.snosnamessexsagesdeptcnograde 200215121李勇男20cs192 200215121李勇男20cs285 200215121李勇男20cs388 200215122劉晨女19cs290 200215122劉晨女19cs380 200215123王敏女18manullnull 200215125張立男19isnullnull an introduction to database system 外連接（續(xù)）外連接（續(xù)） v 左外連接 列出左邊關(guān)系（如本例student）中所有的元組 v 右外連接 列出右邊關(guān)系中所有的

12、元組 an introduction to database system 連接查詢（續(xù)）連接查詢（續(xù)） 一、等值與非等值連接查詢 二、自身連接 三、外連接 四、復(fù)合條件連接 an introduction to database system 四、復(fù)合條件連接四、復(fù)合條件連接 v復(fù)合條件連接：where子句中含多個連接條件 例37查詢選修2號課程且成績在90分以上的所有學(xué)生 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 復(fù)合條件連接（續(xù)）復(fù)合條件連接（續(xù)） 例38查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績 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 數(shù)據(jù)查詢數(shù)據(jù)查詢 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 /*內(nèi)層查詢/子查詢*/ from sc where cno= 2 ）； an introduction to database system 嵌套查詢嵌套查詢(續(xù)續(xù)) 子查詢的限制 不能使用order by子句 層層嵌套方式反映了 sql語言的結(jié)構(gòu)化 有些嵌套查詢可以用連接運(yùn)算替代 an introduction to database system 嵌套查詢求解方法嵌套查詢求解方法 v不相關(guān)子查詢： 子查詢的查詢條件不依賴于父查詢 n由里向外 逐層處理。即每個子查詢在上一級查詢處理 之前求解，子查詢的結(jié)果用于建立其父查詢的查找條 件。 an introduction t

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

17、 database system 一、帶有一、帶有in謂詞的子查詢謂詞的子查詢 例39 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 select sdept from student where sname= 劉晨 ； 結(jié)果為： cs an introduction to database system 帶有帶有in謂詞的子查詢（續(xù)）謂詞的子查詢（續(xù)） 查找所有在is系學(xué)習(xí)的學(xué)生。 select sno，sname，sdept from student where sdept= cs ； 結(jié)果為： 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(guān)子查詢。 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)”的學(xué)生學(xué)號和姓名 select sno，sname 最后在student關(guān)系中 from student 取出sno和sname where sno in (select sno 然后在sc關(guān)系中找出選 from sc 修了3號課程的學(xué)生學(xué)號 where cno in (sele

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

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

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

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

24、給內(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 introduction to database system 帶有比較運(yùn)算符的子查詢（續(xù)）帶有比較運(yùn)算符的子查詢（續(xù)） 3. 執(zhí)行這個查詢，得到 （200215121，1） （200215121，3） 4.外層查詢?nèi)〕鱿乱粋€元組重復(fù)做上述1至3步驟，直到外層 的sc元組全部處理完畢。結(jié)果為: （2002151

25、21，1） （200215121，3） （200215122，2） an introduction to database system 3.4.3 嵌套查詢嵌套查詢 一、帶有in謂詞的子查詢 二、 帶有比較運(yùn)算符的子查詢 三、 帶有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ù)） 需要配合使用比較運(yùn)算符 any大于子查詢結(jié)果中的某個值 all大于子查詢結(jié)果中的所有值 any小于子查詢結(jié)果中的某個值 = any大于等于子查詢結(jié)果中的某個值 = all大于等于子查詢結(jié)果中的所有值 = any小于等于子查詢結(jié)果中的某個值 = all小于等于子查詢結(jié)果中的所有值 = any等于子查詢結(jié)果中的某個值 =all等于子查詢結(jié)果中的所有值（通常沒有實際意義） !=（或）any不等于子查詢結(jié)果中的某個值 !=（或）all不等于子查詢結(jié)果中的任何一個值 an introduction to database system 帶有帶有any（

27、some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 例42 查詢其他系中比計算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓 名和年齡 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ù)） 結(jié)果： 執(zhí)行過程： 1.rdbms執(zhí)行此查詢時，首先處理子查詢，找出 cs系中所有學(xué)生

28、的年齡，構(gòu)成一個集合(20，19) 2. 處理父查詢，找所有不是cs系且年齡小于 20 或 19的學(xué)生 snamesage 王敏18 張立19 an introduction to database system 帶有帶有any（some）或）或all謂詞的子查詢謂詞的子查詢 （續(xù)）（續(xù)） 用聚集函數(shù)實現(xiàn)例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 查詢其他系中比計算機(jī)科學(xué)系所有學(xué)生年齡都小 的學(xué)生姓名及年齡。 方法一：用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ù)） 方法二：用聚集函數(shù) 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謂詞與聚集函數(shù)、in謂詞的等價轉(zhuǎn)換關(guān)系 = 或或!= = any in - maxmin= min all - not in min max = max an introduction to database system 3.4.3 嵌套查詢嵌套查詢 一

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

32、返回真值或假值，給出列名無實際意義 v 2. not exists謂詞 若內(nèi)層查詢結(jié)果非空，則外層的where子句返回假值 若內(nèi)層查詢結(jié)果為空，則外層的where子句返回真值 an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例44查詢所有選修了1號課程的學(xué)生姓名。 思路分析： n本查詢涉及student和sc關(guān)系 n在student中依次取每個元組的sno值，用此值去檢查sc關(guān)系 n若sc中存在這樣的元組，其sno值等于此student.sno值，并 且其cno= 1，則取此student.sname送入結(jié)果關(guān)系 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用連接運(yù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號課程的學(xué)生姓名。 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謂詞、比較運(yùn)算符、any和all謂詞的子查詢都能用帶 exists謂詞的子查詢等價替換 v 用exists/not exists實現(xiàn)全稱量詞(難點(diǎn)) sql語言中沒有全稱量詞 （for all） 可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞： (x)p ( x( p) an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例：例39查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。 可以用帶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 查詢選修了全部課程的學(xué)生姓名。 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實現(xiàn)邏輯蘊(yùn)函(難點(diǎn)) sql語言中沒有蘊(yùn)函(implication)邏輯運(yùn)算 可以利用謂詞演算將邏輯蘊(yùn)函謂詞等價轉(zhuǎn)換為： p q pq an introduction to database system 帶有帶有exists謂詞的子查詢謂詞的子查詢(續(xù)）續(xù)） 例47查詢至少選修了學(xué)生200215122選修的全部課程的學(xué) 生號碼。 解題思路： n用邏輯蘊(yùn)函表達(dá)：查詢學(xué)號為x的學(xué)生，對所有的課程y，只要 2002

38、15122學(xué)生選修了課程y，則x也選修了y。 n形式化表示： 用p表示謂詞 “學(xué)生200215122選修了課程y” 用q表示謂詞 “學(xué)生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，學(xué)生200215122選修了 y，而學(xué)生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 數(shù)據(jù)查詢數(shù)據(jù)查詢 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參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對 應(yīng)項的數(shù)據(jù)類型也必須相同 an introduction to database system 集合查詢（續(xù)）集合查詢（續(xù)） 例48 查詢計算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 方法一： select * from student where sdept= cs union select * from student where sage=19； nunion：將多個查詢結(jié)果合并起來時，系統(tǒng)自動去掉重復(fù)元組。 nunion all：將多個查詢結(jié)果合并起來時，保留重復(fù)元組 an introduction to database s