空間數(shù)據(jù)庫課件：第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3-2

1、第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖3.6 數(shù)據(jù)控制3.3 查 詢 3.3.1 概述3.3.2 單表查詢3.3.3 連接查詢3.3.4 嵌套查詢3.3.5 集合查詢3.3.6 小結(jié) 3.3.3 連接查詢 同時(shí)涉及多個(gè)表的查詢稱為連接查詢用來連接兩個(gè)表的條件稱為連接條件或連接謂詞 一般格式：. . 比較運(yùn)算符：=、=、=、!=. BETWEEN . AND .連接字段連接謂詞中的列名稱為連接字段連接條件中的各連接字段類型必須是可比的，但不必是相同的連接操作的執(zhí)行過程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個(gè)元組

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

3、續(xù)找到表1的第二條元組，然后從剛才的中斷點(diǎn)處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。直接遇到表2中大于表1連接字段值的元組時(shí)，對(duì)表2的查詢不再繼續(xù)重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止 索引連接(INDEX-JOIN)對(duì)表2按連接字段建立索引對(duì)表1中的每個(gè)元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組 3.3.3 連接查詢SQL中連接查詢的主要類型 廣義笛卡爾積 等值連接(含自然連接) 非等值連接查詢 自身連接查詢 外連接查

4、詢 復(fù)合條件連接查詢一、廣義笛卡爾積 不帶連接謂詞的連接 很少使用例： SELECT Student.* , SC.* FROM Student, SC二、等值與非等值連接查詢 等值連接、自然連接、非等值連接例33 查詢每個(gè)學(xué)生及其選修課程的情況。 SELECT Student.*，SC.* FROM Student，SCWHERE Student.Sno = SC.Sno；連接運(yùn)算符為 = 的連接操作為等值連接 . = .任何子句中引用表1和表2中同名屬性時(shí)，都必須加表名前綴。引用唯一屬性名時(shí)可以加也可以省略表名前綴。等值連接查詢結(jié)果：Student.SnoSnameSsexSageSdep

5、tSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122劉晨女19CS200215122290200215122劉晨女19CS200215122380自然連接等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。例34 對(duì)例33用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；非等值連

6、接查詢連接運(yùn)算符不是 = 的連接操作 .比較運(yùn)算符：、=、=、!=. BETWEEN . AND . 三、自身連接 一個(gè)表與其自己進(jìn)行連接，稱為表的自身連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴例35 查詢每一門課的間接先修課（即先修課的先修課） SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno； 自身連接（續(xù)）查詢過程： 四、外連接（Outer Join） 外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定

7、表為連接主體，將主體表中不滿足連接條件的元組一并輸出 例 36 查詢每個(gè)學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生-用外連接操作SELECT Student.Sno，Sname，Ssex， Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno(*)； 或SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)；外連接（續(xù)） Student.SnoSnameSsexSag

8、eSdeptCnoGrade200215121李勇男20CS192200215121李勇男20CS285200215121李勇男20CS388200215122劉晨女19CS290200215122劉晨女19CS380200215123王敏女18MANULLNULL200215125張立男19ISNULLNULL外連接（續(xù)） 在表名后面加外連接操作符(*)或(+)指定非主體表非主體表有一“萬能”的虛行，該行全部由空值組成虛行可以和主體表中所有不滿足連接條件的元組進(jìn)行連接由于虛行各列全部是空值，因此與虛行連接的結(jié)果中，來自非主體表的屬性值全部是空值 左外連接 列出左邊關(guān)系中所有元組 右外連接 列

9、出右邊關(guān)系中所有元組 五、復(fù)合條件連接WHERE子句中含多個(gè)連接條件時(shí)，稱為復(fù)合條件連接例37查詢選修2號(hào)課程且成績?cè)?0分以上的所有學(xué)生的學(xué)號(hào)、姓名SELECT Student.Sno, student.SnameFROM Student, SCWHERE Student.Sno = SC.Sno AND /* 連接謂詞*/ SC.Cno= 2 AND /* 其他限定條件 */ SC.Grade 90； /* 其他限定條件 */多表連接例38 查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績。 SELECT Student.Sno，Sname，Cname，Grade FROM Student，S

10、C，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；結(jié)果：Student.Sno Sname Cname Grade 200215121 李勇 數(shù)據(jù)庫 92 200215121 李勇 數(shù)學(xué) 85 200215121 李勇 信息系統(tǒng) 88 200215122 劉晨 數(shù)學(xué) 90 200215122 劉晨 信息系統(tǒng) 80 3.3 查 詢 3.3.1 概述3.3.2 單表查詢3.3.3 連接查詢3.3.4 嵌套查詢3.3.5 集合查詢3.3.6 小結(jié) 嵌套查詢(續(xù))嵌套查詢概述一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊將

11、一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢SELECT Sname外層查詢/父查詢 FROM Student WHERE Sno IN （SELECT Sno 內(nèi)層查詢/子查詢 FROM SC WHERE Cno= 2 ）； 嵌套查詢(續(xù))子查詢的限制不能使用ORDER BY子句,只能對(duì)最終結(jié)果排序?qū)訉忧短追绞椒从沉?SQL語言的結(jié)構(gòu)化有些嵌套查詢可以用連接運(yùn)算替代分類:不相關(guān)子查詢子查詢的查詢條件不依賴于父查詢相關(guān)子查詢子查詢的查詢條件依賴于父查詢嵌套查詢求解方法不相關(guān)子查詢是由里向外逐層處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用

12、于建立其父查詢的查找條件。相關(guān)子查詢首先取外層查詢中表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再取外層表的下一個(gè)元組；重復(fù)這一過程，直至外層表全部檢查完為止。舉 例查詢沒有選修課程的所有學(xué)生的學(xué)號(hào)和姓名Select s#,snameFrom studentWhere s# NOT IN ( select distinct s# From sc)子查詢返回選修了課程的學(xué)生學(xué)號(hào)集合，它與外層的查詢無依賴關(guān)系，可以單獨(dú)執(zhí)行無關(guān)子查詢一般與IN一起使用，用于返回一個(gè)值列表查詢選修了課程的學(xué)生學(xué)號(hào)和姓名Select s#, sname

13、 From studentWhere EXISTS (Select * From sc Where sc.s# = student.s#)相關(guān)子查詢不可單獨(dú)執(zhí)行，依賴于外層查詢EXISTS(子查詢)：當(dāng)子查詢返回結(jié)果非空時(shí)為真，否則為假執(zhí)行分析：對(duì)于student的每一行，根據(jù)該行的s#去sc中查找有無匹配記錄引出子查詢的謂詞帶有IN謂詞的子查詢帶有比較運(yùn)算符的子查詢帶有ANY(SOME)或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢一、帶有IN謂詞的子查詢例39 查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 SELECT Sdept FROM Stu

14、dent WHERE Sname= 劉晨 ；結(jié)果為： Sdept IS 查找所有在IS系學(xué)習(xí)的學(xué)生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 結(jié)果為： Sno Sname Sdept 95001 劉晨 IS 95004 張立 IS 構(gòu)造嵌套查詢將第一步查詢嵌入到第二步查詢的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 劉晨 )；此查詢?yōu)椴幌嚓P(guān)子查詢。DBMS求解該查詢時(shí)也是分步去做的。

15、 用自身連接完成本查詢要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 劉晨；帶有IN謂詞的子查詢（續(xù)）父查詢和子查詢中的表均可以定義別名 SELECT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 劉晨 )；帶有IN謂詞的子查詢（續(xù)）例40查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名 SELE

16、CT Sno，Sname 最后在Student關(guān)系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC關(guān)系中找出選 FROM SC 修了3號(hào)課程的學(xué)生學(xué)號(hào) WHERE Cno IN (SELECT Cno 首先在Course關(guān)系中找出“信 FROM Course 息系統(tǒng)”的課程號(hào)，結(jié)果為3號(hào) WHERE Cname= 信息系統(tǒng));帶有IN謂詞的子查詢（續(xù)）結(jié)果：Sno Sname - - 200215121 李勇 200215122 劉晨帶有IN謂詞的子查詢（續(xù)）用連接查詢 SELECT Sno，Sname FROM Student

17、，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系統(tǒng)；二、帶有比較運(yùn)算符的子查詢當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（，=，=，!=或）。與ANY或ALL謂詞配合使用例：假設(shè)一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，并且必須屬于一個(gè)系，則在例37可以用 = 代替IN ：SELECT Sno，Sname，SdeptFROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 劉晨 )；帶有比較運(yùn)算符的子查詢（續(xù)） 子

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

19、值（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 Grade =88； 帶有比較運(yùn)算符的子查詢（續(xù)）3. 執(zhí)行這個(gè)查詢，得到 （200215121，1） （200215121，3） 4.外層查詢?nèi)〕鱿乱粋€(gè)元組重復(fù)做上述1至3步驟，直到外層的SC元組全部處理完畢。結(jié)果為: （200215121，1） （200215121，3） （200215122，2）三、帶有AN

20、Y(SOME)或ALL謂詞的子查詢謂詞語義ANY：任意一個(gè)值A(chǔ)LL：所有值需要配合使用比較運(yùn)算符 ANY大于子查詢結(jié)果中的某個(gè)值 ALL大于子查詢結(jié)果中的所有值 ANY小于子查詢結(jié)果中的某個(gè)值 = ANY大于等于子查詢結(jié)果中的某個(gè)值 = ALL大于等于子查詢結(jié)果中的所有值= ANY小于等于子查詢結(jié)果中的某個(gè)值 = ALL小于等于子查詢結(jié)果中的所有值= ANY等于子查詢結(jié)果中的某個(gè)值 =ALL等于子查詢結(jié)果中的所有值（通常沒有實(shí)際意義）!=（或）ANY不等于子查詢結(jié)果中的某個(gè)值!=（或）ALL不等于子查詢結(jié)果中的任何一個(gè)值帶有ANY(SOME)或ALL謂詞的子查詢（續(xù)）例42 查詢其他系中比計(jì)算

21、機(jī)系任意一個(gè)(其中某一個(gè))學(xué)生年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS ; /* 注意這是父查詢塊中的條件 */結(jié)果Sname Sage 王敏 18 張立 19執(zhí)行過程1.DBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出 IS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(19，18)2. 處理父查詢，找所有不是IS系且年齡小于 19 或 18的學(xué)生帶有ANY或ALL謂詞的子查詢（續(xù)）ANY和ALL謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn)ANY

22、與ALL與集函數(shù)的對(duì)應(yīng)關(guān)系用集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)例42：用集函數(shù)實(shí)現(xiàn)例42 SELECT Sname，Sage FROM Student WHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept= CS ) AND Sdept CS ; = 或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX帶有ANY或ALL謂詞的子查詢（續(xù)）例43 查詢其他系中比計(jì)算機(jī)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞 SELECT Sname

23、，Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept= IS ) AND Sdept IS ; 方法二：用集函數(shù) SELECT Sname，Sage FROM Student WHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept= IS ) AND Sdept IS ;四、帶有EXISTS謂詞的子查詢1. EXISTS謂詞2. NOT EXISTS謂詞3. 不同形式的查詢間的替換4. 用EXISTS/NOT EXISTS實(shí)現(xiàn)全稱量詞帶有EXISTS謂詞

24、的子查詢(續(xù)）1. EXISTS謂詞存在量詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則返回真值若內(nèi)層查詢結(jié)果為空，則返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用* ，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無實(shí)際意義2. NOT EXISTS謂詞帶有EXISTS謂詞的子查詢(續(xù)）例44 查詢所有選修了1號(hào)課程的學(xué)生姓名。 思路分析： 本查詢涉及Student和SC關(guān)系。 在Student中依次取每個(gè)元組的Sno值，用此值去檢查SC關(guān)系。 若SC中存在這樣的元組，其Sno值等于此Student

25、.Sno值，并且其Cno= 1，則取此Student.Sname送入結(jié)果關(guān)系。用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC /*相關(guān)子查詢*/ WHERE Sno=Student.Sno AND Cno= 1)；用連接運(yùn)算SELECT Sname FROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno= 1;帶有EXISTS謂詞的子查詢(續(xù)）例45 查詢沒有選修1號(hào)課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (

26、SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；此例用單一連接運(yùn)算難于實(shí)現(xiàn) 帶有EXISTS謂詞的子查詢(續(xù)）3. 不同形式的查詢間的替換一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換。例：例39查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生?？梢杂脦XISTS謂詞的子查詢替換： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS （SELECT * FROM Stud

27、ent S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 劉晨 ）； 帶有EXISTS謂詞的子查詢(續(xù)）4.用EXISTS/NOT EXISTS實(shí)現(xiàn)全稱量詞SQL語言中沒有全稱量詞 （For all）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞： (x)P ( x( P) 例46 查詢選修了全部課程的學(xué)生姓名。SELECT Sname FROM Student 不存在這樣的一門課程，這個(gè)學(xué)生沒有選修。 WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC

28、 WHERE Sno= Student.Sno AND Cno= Course.Cno)；帶有EXISTS謂詞的子查詢(續(xù)） 例47查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號(hào)碼。解題思路：變換后語義：不存在這樣的課程y，學(xué)生200215122選修了y，而學(xué)生x沒有選。用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 S

29、CZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；3.3 查 詢 3.3.1 概述3.3.2 單表查詢3.3.3 連接查詢3.3.4 嵌套查詢3.3.5 集合查詢3.3.6 小結(jié) 3.4.4 集合查詢集合操作的種類并操作UNION交操作INTERSECT差操作EXCEPT參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同 集合查詢（續(xù)）例48 查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。方法一：SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19；

30、UNION：將多個(gè)查詢結(jié)果合并起來時(shí)，系統(tǒng)自動(dòng)去掉重復(fù)元組。UNION ALL：將多個(gè)查詢結(jié)果合并起來時(shí)，保留重復(fù)元組 方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；集合查詢（續(xù)）例49 查詢選修了課程1或者選修了課程2的學(xué)生。 SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT Sno FROM SC WHERE Cno= 2 ；集合查詢（續(xù)）例50 查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集SELECT * FROM StudentWHERE Sdept=CS INTERSECTSELECT * FROM StudentWHERE Sage=19 例50 實(shí)際上就是查詢計(jì)算機(jī)科學(xué)系中年齡不大于19歲的學(xué)生SELECT * FROM Student WHERE Sdept= CS AND Sage=19；集合查詢（續(xù)）例51 查詢選修課程1的學(xué)生集合與選修課程2的學(xué)生集合的交集 SELE