關(guān)系數(shù)據(jù)庫標準語言SQL(續(xù)1).ppt

1、An Introduction to Database System,重慶工商大學,數(shù)據(jù)庫系統(tǒng)概論 An Introduction to Database System 第三章 關(guān)系數(shù)據(jù)庫標準語言SQL (續(xù)1,An Introduction to Database System,3.3 查 詢,3.3.1 概述 3.3.2 單表查詢 3.3.3 連接查詢 3.3.4 嵌套查詢 3.3.5 集合查詢 3.3.6 小結(jié),An Introduction to Database System,3.3.3 連接查詢,同時涉及多個表的查詢稱為連接查詢 用來連接兩個表的條件稱為連接條件或連接謂詞 一般格式

2、： . . 比較運算符：=、=、. BETWEEN . AND,An Introduction to Database System,連接查詢 （續(xù),連接字段 連接謂詞中的列名稱為連接字段 連接條件中的各連接字段類型必須是可比的，但不必是相同的,An Introduction to Database System,連接操作的執(zhí)行過程,嵌套循環(huán)法(NESTED-LOOP) 首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。 表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接

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

4、點處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續(xù) 重復上述操作，直到表1或表2中的全部元組都處理完畢為止,An Introduction to Database System,索引連接(INDEX-JOIN,對表2按連接字段建立索引 對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組,An Introduction to Database System,連接查詢 （續(xù),SQ

5、L中連接查詢的主要類型 廣義笛卡爾積 等值連接(含自然連接) 非等值連接查詢 自身連接查詢 外連接查詢 復合條件連接查詢,An Introduction to Database System,一、廣義笛卡爾積,不帶連接謂詞的連接 很少使用 例： SELECT Student.* , SC.* FROM Student, SC,An Introduction to Database System,二、等值與非等值連接查詢,等值連接、自然連接、非等值連接 例32 查詢每個學生及其選修課程的情況。 SELECT Student.*，SC.* FROM Student，SC WHERE Student

6、.Sno = SC.Sno,An Introduction to Database System,等值連接,連接運算符為 = 的連接操作 . = . 任何子句中引用表1和表2中同名屬性時，都必須加表名前綴。引用唯一屬性名時可以加也可以省略表名前綴,An Introduction to Database System,等值連接,假設(shè)Student表、SC表分別有下列數(shù)據(jù)： Student表,An Introduction to Database System,等值連接,SC表,An Introduction to Database System,等值連接,結(jié)果表 Student.Sno Snam

7、e Ssex Sage Sdept SC.Sno Cno Grade 95001 李勇 男 20 CS95001 1 92 95001 李勇 男 20CS95001 2 85 95001 李勇 男 20CS95001 3 88 95002 劉晨 女 19IS 95002 2 90 95002 劉晨 女 19IS95002 3 80,An Introduction to Database System,自然連接,等值連接的一種特殊情況，把目標列中重復的屬性列去掉。 例33 對例32用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage， Sdept，Cno，Gra

8、de FROM Student，SC WHERE Student.Sno = SC.Sno,An Introduction to Database System,非等值連接查詢,連接運算符 不是 = 的連接操作 . 比較運算符：、=、. BETWEEN . AND,An Introduction to Database System,三、自身連接,一個表與其自己進行連接，稱為表的自身連接 需要給表起別名以示區(qū)別 由于所有屬性名都是同名屬性，因此必須使用別名前綴,An Introduction to Database System,自身連接（續(xù),例34 查詢每一門課的間接先修課（即先修課的先修課

9、） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno,An Introduction to Database System,自身連接（續(xù),FIRST表（Course表,An Introduction to Database System,自身連接（續(xù),SECOND表（Course表,An Introduction to Database System,自身連接（續(xù),查詢結(jié)果 1 7 3 5 5 6,An Introduction to Database Syste

10、m,四、外連接（Outer Join,外連接與普通連接的區(qū)別 普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出,An Introduction to Database System,外連接（續(xù),例 33 查詢每個學生及其選修課程的情況包括沒有選修課程的學生-用外連接操作 SELECT Student.Sno，Sname，Ssex， Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno(*,An Introduction to Database System,外連接

11、（續(xù),結(jié)果： Student.Sno Sname Ssex Sage Sdept Cno Grade 95001 李勇 男 20 CS 1 92 95001 李勇 男 20 CS 2 85 95001 李勇 男 20 CS 3 88 95002 劉晨 女 19 IS 2 90 95002 劉晨 女 19 IS 3 80 95003 王敏 女 18 MA 95004 張立 男 19 IS,An Introduction to Database System,外連接（續(xù),在表名后面加外連接操作符(*)或(+)指定非主體表 非主體表有一“萬能”的虛行，該行全部由空值組成 虛行可以和主體表中所有不滿足

12、連接條件的元組進行連接 由于虛行各列全部是空值，因此與虛行連接的結(jié)果中，來自非主體表的屬性值全部是空值,An Introduction to Database System,外連接（續(xù),左外連接 外連接符出現(xiàn)在連接條件的左邊 右外連接 外連接符出現(xiàn)在連接條件的右邊,An Introduction to Database System,五、復合條件連接,WHERE子句中含多個連接條件時，稱為復合條件連接 例35查詢選修2號課程且成績在90分以上的所有學生的 學號、姓名 SELECT Student.Sno, student.Sname FROM Student, SC WHERE Student

13、.Sno = SC.Sno AND /* 連接謂詞*/ SC.Cno= 2 AND /* 其他限定條件 */ SC.Grade 90； /* 其他限定條件 *,An Introduction to Database System,多表連接,例36 查詢每個學生的學號、姓名、選修的課程名及成績。 SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno； 結(jié)果： Student.Sno Sname Cname Grade 95001

14、李勇 數(shù)據(jù)庫 92 95001 李勇 數(shù)學 85 95001 李勇 信息系統(tǒng) 88 95002 劉晨 數(shù)學 90 95002 劉晨 信息系統(tǒng) 80,An Introduction to Database System,3.3 查 詢,3.3.1 概述 3.3.2 單表查詢 3.3.3 連接查詢 3.3.4 嵌套查詢 3.3.5 集合查詢 3.3.6 小結(jié),An Introduction to Database System,3.3.4 嵌套查詢,嵌套查詢概述 嵌套查詢分類 嵌套查詢求解方法 引出子查詢的謂詞,An Introduction to Database System,嵌套查詢(續(xù),嵌

15、套查詢概述 一個SELECT-FROM-WHERE語句稱為一個查詢塊 將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢,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語言的結(jié)構(gòu)化 有些嵌套

16、查詢可以用連接運算替代,An Introduction to Database System,嵌套查詢分類,不相關(guān)子查詢 子查詢的查詢條件不依賴于父查詢 相關(guān)子查詢 子查詢的查詢條件依賴于父查詢,An Introduction to Database System,嵌套查詢求解方法,不相關(guān)子查詢 是由里向外逐層處理。即每個子查詢在 上一級查詢處理之前求解，子查詢的結(jié)果 用于建立其父查詢的查找條件,An Introduction to Database System,嵌套查詢求解方法（續(xù),相關(guān)子查詢 首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值

17、為真，則取此元組放入結(jié)果表； 然后再取外層表的下一個元組； 重復這一過程，直至外層表全部檢查完為止,An Introduction to Database System,引出子查詢的謂詞,帶有IN謂詞的子查詢 帶有比較運算符的子查詢 帶有ANY或ALL謂詞的子查詢 帶有EXISTS謂詞的子查詢,An Introduction to Database System,一、帶有IN謂詞的子查詢,例37 查詢與“劉晨”在同一個系學習的學生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 劉晨 ； 結(jié)果為： Sdept IS

18、,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù),查找所有在IS系學習的學生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 結(jié)果為： Sno Sname Sdept 95001 劉晨 IS 95004 張立 IS,An Introduction to Database System,構(gòu)造嵌套查詢,將第一步查詢嵌入到第二步查詢的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Studen

19、t WHERE Sname= 劉晨 )； 此查詢?yōu)椴幌嚓P(guān)子查詢。DBMS求解該查詢時也是分步 去做的,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù),用自身連接完成本查詢要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 劉晨,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù),父查詢和子查詢中的表均可以定義別名 SELECT Sno，Sname，Sd

20、ept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 劉晨,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù),例38查詢選修了課程名為“信息系統(tǒng)”的學生學號和姓名 SELECT Sno，Sname 最后在Student關(guān)系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC關(guān)系中找出選 FROM SC 修了3號課程的學生學號 WHERE Cno IN (SELECT Cno

21、首先在Course關(guān)系中找出“信 FROM Course 息系統(tǒng)”的課程號，結(jié)果為3號 WHERE Cname= 信息系統(tǒng),An Introduction to Database System,帶有IN謂詞的子查詢（續(xù),結(jié)果： Sno Sname - - 95001 李勇 95002 劉晨,An Introduction to Database System,帶有IN謂詞的子查詢（續(xù),用連接查詢 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Cours

22、e.Cname=信息系統(tǒng),An Introduction to Database System,二、帶有比較運算符的子查詢,當能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，）。 與ANY或ALL謂詞配合使用,An Introduction to Database System,帶有比較運算符的子查詢（續(xù),例：假設(shè)一個學生只可能在一個系學習，并且必須屬于一個系，則在例37可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = SELECT Sdept FROM Student WHERE Sname= 劉晨,An Int

23、roduction to Database System,帶有比較運算符的子查詢（續(xù),子查詢一定要跟在比較符之后 錯誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept,An Introduction to Database System,三、帶有ANY或ALL謂詞的子查詢,謂詞語義 ANY：任意一個值 ALL：所有值,An Introduction to Database System,帶有ANY或ALL謂詞的子查詢（續(xù),需要配合使用比較運算符

24、 ANY大于子查詢結(jié)果中的某個值 ALL大于子查詢結(jié)果中的所有值 = ANY大于等于子查詢結(jié)果中的某個值 = ALL大于等于子查詢結(jié)果中的所有值 ）ANY不等于子查詢結(jié)果中的某個值 !=（或）ALL不等于子查詢結(jié)果中的任何一個值,An Introduction to Database System,帶有ANY或ALL謂詞的子查詢（續(xù),例39 查詢其他系中比信息系任意一個(其中某一個)學生年齡小的學生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage IS ; /* 注意這是父查詢塊中的條件 *,An Introduction to Database

25、System,帶有ANY或ALL謂詞的子查詢（續(xù),結(jié)果 Sname Sage 王敏 18 執(zhí)行過程 1.DBMS執(zhí)行此查詢時，首先處理子查詢，找出 IS系中所有學生的年齡，構(gòu)成一個集合(19，18) 2. 處理父查詢，找所有不是IS系且年齡小于 19 或 18的學生,An Introduction to Database System,帶有ANY或ALL謂詞的子查詢（續(xù),ANY和ALL謂詞有時可以用集函數(shù)實現(xiàn) ANY與ALL與集函數(shù)的對應關(guān)系,An Introduction to Database System,帶有ANY或ALL謂詞的子查詢（續(xù),用集函數(shù)實現(xiàn)子查詢通常比直接用ANY或ALL查

26、詢效率要高，因為前者通常能夠減少比較次數(shù),An Introduction to Database System,帶有ANY或ALL謂詞的子查詢（續(xù),例39：用集函數(shù)實現(xiàn)例39 SELECT Sname，Sage FROM Student WHERE Sage IS,An Introduction to Database System,帶有ANY或ALL謂詞的子查詢（續(xù),例40 查詢其他系中比信息系所有學生年齡都小的學生姓名及年齡。 方法一：用ALL謂詞 SELECT Sname，Sage FROM Student WHERE Sage IS ; 查詢結(jié)果為空表,An Introduction

27、to Database System,帶有ANY或ALL謂詞的子查詢（續(xù),方法二：用集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage IS,An Introduction to Database System,四、帶有EXISTS謂詞的子查詢,1. EXISTS謂詞 2. NOT EXISTS謂詞 3. 不同形式的查詢間的替換 4. 相關(guān)子查詢的效率 5. 用EXISTS/NOT EXISTS實現(xiàn)全稱量詞 6. 用EXISTS/NOT EXISTS實現(xiàn)邏輯蘊函,An Introduction to Database System,帶有EXISTS謂詞的

28、子查詢(續(xù),1. EXISTS謂詞 存在量詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。 若內(nèi)層查詢結(jié)果非空，則返回真值 若內(nèi)層查詢結(jié)果為空，則返回假值 由EXISTS引出的子查詢，其目標列表達式通常都用* ，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義 2. NOT EXISTS謂詞,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),思路分析： 本查詢涉及Student和SC關(guān)系。 在Student中依次取每個元組的Sno值，用此值去檢查SC關(guān)系。 若SC中存在這樣的

29、元組，其Sno值等于此Student.Sno值，并且其Cno= 1，則取此Student.Sname送入結(jié)果關(guān)系,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),例41 查詢所有選修了1號課程的學生姓名。 用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相關(guān)子查詢*/ WHERE Sno=Student.Sno AND Cno= 1 )； 求解過程,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),用連接

30、運算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),例42 查詢沒有選修1號課程的學生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)； 此例用連接運算難于實現(xiàn),An Introduction to Database System,帶有EXISTS謂詞的

31、子查詢(續(xù),3. 不同形式的查詢間的替換 一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換 所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),例：例37查詢與“劉晨”在同一個系學習的學生?？梢杂脦XISTS謂詞的子查詢替換： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS SELECT * FROM Student S2 WHERE S2.Sdept =

32、S1.Sdept AND S2.Sname = 劉晨,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),5.用EXISTS/NOT EXISTS實現(xiàn)全稱量詞(難點) SQL語言中沒有全稱量詞 （For all） 可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞： (x)P ( x( P,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),例43 查詢選修了全部課程的學生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * F

33、ROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),6. 用EXISTS/NOT EXISTS實現(xiàn)邏輯蘊函(難點) SQL語言中沒有蘊函(Implication)邏輯運算 可以利用謂詞演算將邏輯蘊函謂詞等價轉(zhuǎn)換為： p q pq,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),例44 查詢至少選修了學生95002

34、選修的全部課程的學生號碼。 解題思路： 用邏輯蘊函表達：查詢學號為x的學生，對所有的課程y，只要95002學生選修了課程y，則x也選修了y。 形式化表示： 用P表示謂詞 “學生95002選修了課程y” 用q表示謂詞 “學生x選修了課程y” 則上述查詢?yōu)? (y) p q,An Introduction to Database System,帶有EXISTS謂詞的子查詢(續(xù),等價變換： (y)p q (y (p q ) (y ( p q) y(pq) 變換后語義：不存在這樣的課程y，學生95002選修了y，而學生x沒有選,An Introduction to Database System,帶有

35、EXISTS謂詞的子查詢(續(xù),用NOT EXISTS謂詞表示： SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno,An Introduction to Database System,3.3 查 詢,3.3.1 概述 3.3.2 單表查詢 3.3.3 連接查詢 3.3.4 嵌套查詢 3.3.5 集合查詢

36、3.3.6 小結(jié),An Introduction to Database System,3.3.5 集合查詢,標準SQL直接支持的集合操作種類 并操作(UNION) 一般商用數(shù)據(jù)庫支持的集合操作種類 并操作(UNION) 交操作(INTERSECT) 差操作(MINUS,An Introduction to Database System,1 并操作,形式 UNION 參加UNION操作的各結(jié)果表的列數(shù)必須相同；對應項的數(shù)據(jù)類型也必須相同,An Introduction to Database System,并操作（續(xù),例45 查詢計算機科學系的學生及年齡不大于19歲的學生。 方法一： SEL

37、ECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19,An Introduction to Database System,并操作（續(xù),方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19,An Introduction to Database System,并操作（續(xù),例46 查詢選修了課程1或者選修了課程2的學生。 方法一： SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT Sno

38、 FROM SC WHERE Cno= 2,An Introduction to Database System,并操作（續(xù),方法二： SELECT DISTINCT Sno FROM SC WHERE Cno= 1 OR Cno= 2,An Introduction to Database System,并操作（續(xù),例47 設(shè)數(shù)據(jù)庫中有一教師表Teacher(Tno, Tname,.)。查詢學校中所有師生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher,new,An Introduction to Database

39、System,2 交操作,標準SQL中沒有提供集合交操作，但可用其他方法間接實現(xiàn),An Introduction to Database System,2 交操作,例48 查詢計算機科學系的學生與年齡不大于19歲的學生的交集 本例實際上就是查詢計算機科學系中年齡不大于19歲的學生 SELECT * FROM Student WHERE Sdept= CS AND Sage=19,An Introduction to Database System,交操作（續(xù),例49 查詢選修課程1的學生集合與選修課程2的學生集合的交集 本例實際上是查詢既選修了課程1又選修了課程2的學生 SELECT Sno

40、FROM SC WHERE Cno= 1 AND Sno IN (SELECT Sno FROM SC WHERE Cno= 2,An Introduction to Database System,交操作（續(xù),例50 查詢學生姓名與教師姓名的交集 本例實際上是查詢學校中與教師同名的學生姓名 SELECT DISTINCT Sname FROM Student WHERE Sname IN (SELECT Tname FROM Teacher,new,An Introduction to Database System,3 差操作,標準SQL中沒有提供集合差操作，但可用其他方法間接實現(xiàn),An Introduction to Database System,3 差操作,例51 查詢計算機科學系的學生與年齡不大于19歲的學生的差集。 本例實際上是查詢計算機科學系中年齡大于19歲的學生 SELECT * FROM Student WHERE Sdept= CS AND Sage19,An Introduction to Database System,差操作（續(xù),例52 查詢學生姓名與教師