1、AnIntroductiontoDatabaseSystem,數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL(續(xù)1）,AnIntroductiontoDatabaseSystem,3.4數(shù)據(jù)查詢,3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般形式,AnIntroductiontoDatabaseSystem,3.4.2連接查詢,連接查詢：同時(shí)涉及多個(gè)表的查詢連接條件或連接謂詞：用來連接兩個(gè)表的條件一般格式：.BETWEEN.AND.連接字段：連接謂詞中的列名稱連接條件中的各連接字

2、段類型必須是可比的，但名字不必是相同的,AnIntroductiontoDatabaseSystem,連接操作的執(zhí)行過程,嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個(gè)元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。表2全部查找完后，再找表1中第二個(gè)元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢,AnIntroductiontoDatabaseSystem,排序合并法(SORT-MERG

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

4、ntroductiontoDatabaseSystem,索引連接(INDEX-JOIN),對(duì)表2按連接字段建立索引對(duì)表1中的每個(gè)元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組,AnIntroductiontoDatabaseSystem,連接查詢（續(xù)）,一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接,AnIntroductiontoDatabaseSystem,一、等值與非等值連接查詢,等值連接：連接運(yùn)算符為=例33查詢每個(gè)學(xué)生及其選修課程的情況SELECTStudent.*，SC.*FROM

5、Student，SCWHEREStudent.Sno=SC.Sno；,AnIntroductiontoDatabaseSystem,等值與非等值連接查詢（續(xù)）,查詢結(jié)果：,AnIntroductiontoDatabaseSystem,等值與非等值連接查詢（續(xù)）,自然連接：例34對(duì)例33用自然連接完成。SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；,AnIntroductiontoDatabaseSystem,連接查詢（續(xù)）,一、等值與非等值連接查詢二、自身連接三、外連

6、接四、復(fù)合條件連接,AnIntroductiontoDatabaseSystem,二、自身連接,自身連接：一個(gè)表與其自己進(jìn)行連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴例35查詢每一門課的間接先修課（即先修課的先修課）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；,AnIntroductiontoDatabaseSystem,自身連接（續(xù)）,FIRST表（Course表）,AnIntroductiontoDatabaseSystem,自身連接（續(xù)）

7、,SECOND表（Course表）,AnIntroductiontoDatabaseSystem,自身連接（續(xù)）,查詢結(jié)果：,AnIntroductiontoDatabaseSystem,連接查詢（續(xù)）,一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接,AnIntroductiontoDatabaseSystem,三、外連接,外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出例36改寫例33SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStude

8、ntLEFTOUTJOINSCON(Student.Sno=SC.Sno)；,AnIntroductiontoDatabaseSystem,外連接（續(xù)）,執(zhí)行結(jié)果：,AnIntroductiontoDatabaseSystem,外連接（續(xù)）,左外連接列出左邊關(guān)系（如本例Student）中所有的元組右外連接列出右邊關(guān)系中所有的元組,AnIntroductiontoDatabaseSystem,連接查詢（續(xù)）,一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接,AnIntroductiontoDatabaseSystem,四、復(fù)合條件連接,復(fù)合條件連接：WHERE子句中含多個(gè)連接條件例

9、37查詢選修2號(hào)課程且成績?cè)?0分以上的所有學(xué)生SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*連接謂詞*/SC.Cno=2ANDSC.Grade90；/*其他限定條件*/,AnIntroductiontoDatabaseSystem,復(fù)合條件連接（續(xù)）,例38查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，Course/*多表連接*/WHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cn

10、o；,AnIntroductiontoDatabaseSystem,3.4數(shù)據(jù)查詢,3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般形式,AnIntroductiontoDatabaseSystem,嵌套查詢(續(xù)),嵌套查詢概述一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢,AnIntroductiontoDatabaseSystem,嵌套查詢(續(xù)),SELECTSname/*外層查詢/父查詢*/FROMStudentWHERESnoIN（

11、SELECTSno/*內(nèi)層查詢/子查詢*/FROMSCWHERECno=2）；,AnIntroductiontoDatabaseSystem,嵌套查詢(續(xù)),子查詢的限制不能使用ORDERBY子句層層嵌套方式反映了SQL語言的結(jié)構(gòu)化有些嵌套查詢可以用連接運(yùn)算替代,AnIntroductiontoDatabaseSystem,嵌套查詢求解方法,不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。,AnIntroductiontoDatabaseSystem,嵌套查詢求解方法（續(xù)）,相關(guān)子查詢：子查詢的查詢條件

12、依賴于父查詢首先取外層查詢中表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表然后再取外層表的下一個(gè)元組重復(fù)這一過程，直至外層表全部檢查完為止,AnIntroductiontoDatabaseSystem,3.4.3嵌套查詢,一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)в蠥NY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢,AnIntroductiontoDatabaseSystem,一、帶有IN謂詞的子查詢,例39查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。此查詢要求可以分步來完成確定“劉晨”所在系名SELECTS

13、deptFROMStudentWHERESname=劉晨；結(jié)果為：CS,AnIntroductiontoDatabaseSystem,帶有IN謂詞的子查詢（續(xù)）,查找所有在IS系學(xué)習(xí)的學(xué)生。SELECTSno，Sname，SdeptFROMStudentWHERESdept=CS；結(jié)果為：,AnIntroductiontoDatabaseSystem,帶有IN謂詞的子查詢（續(xù)）,將第一步查詢嵌入到第二步查詢的條件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=劉晨)；此查詢?yōu)椴幌嚓P(guān)子查

14、詢。,AnIntroductiontoDatabaseSystem,帶有IN謂詞的子查詢（續(xù)）,用自身連接完成例39查詢要求SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=劉晨；,AnIntroductiontoDatabaseSystem,帶有IN謂詞的子查詢（續(xù)）,例40查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名SELECTSno，Sname最后在Student關(guān)系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno然后在SC關(guān)

15、系中找出選FROMSC修了3號(hào)課程的學(xué)生學(xué)號(hào)WHERECnoIN(SELECTCno首先在Course關(guān)系中找出FROMCourse“信息系統(tǒng)”的課程號(hào)，為3號(hào)WHERECname=信息系統(tǒng));,AnIntroductiontoDatabaseSystem,帶有IN謂詞的子查詢（續(xù)）,用連接查詢實(shí)現(xiàn)例40SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系統(tǒng)；,AnIntroductiontoDatabaseSystem,3.4.3嵌套查詢,一、帶有I

16、N謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢,AnIntroductiontoDatabaseSystem,二、帶有比較運(yùn)算符的子查詢,當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（，=，）。與ANY或ALL謂詞配合使用,AnIntroductiontoDatabaseSystem,帶有比較運(yùn)算符的子查詢（續(xù)）,例：假設(shè)一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，并且必須屬于一個(gè)系，則在例39可以用=代替IN：SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStud

17、entWHERESname=劉晨)；,AnIntroductiontoDatabaseSystem,帶有比較運(yùn)算符的子查詢（續(xù)）,子查詢一定要跟在比較符之后錯(cuò)誤的例子：SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=劉晨)=Sdept；,AnIntroductiontoDatabaseSystem,帶有比較運(yùn)算符的子查詢（續(xù)）,例41找出每個(gè)學(xué)生超過他選修課程平均成績的課程號(hào)。SELECTSno，CnoFROMSCxWHEREGrade=(SELECTAVG(Grade)FROMSCyWHEREy.S

18、no=x.Sno);,相關(guān)子查詢,AnIntroductiontoDatabaseSystem,帶有比較運(yùn)算符的子查詢（續(xù)）,可能的執(zhí)行過程：1.從外層查詢中取出SC的一個(gè)元組x，將元組x的Sno值（200215121）傳送給內(nèi)層查詢。SELECTAVG(Grade)FROMSCyWHEREy.Sno=200215121;2.執(zhí)行內(nèi)層查詢，得到值88（近似值），用該值代替內(nèi)層查詢，得到外層查詢：SELECTSno，CnoFROMSCxWHEREGrade=88；,AnIntroductiontoDatabaseSystem,帶有比較運(yùn)算符的子查詢（續(xù)）,3.執(zhí)行這個(gè)查詢，得到（20021512

19、1，1）（200215121，3）4.外層查詢?nèi)〕鱿乱粋€(gè)元組重復(fù)做上述1至3步驟，直到外層的SC元組全部處理完畢。結(jié)果為:（200215121，1）（200215121，3）（200215122，2）,AnIntroductiontoDatabaseSystem,3.4.3嵌套查詢,一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢,AnIntroductiontoDatabaseSystem,三、帶有ANY（SOME）或ALL謂詞的子查詢,謂詞語義ANY：任意一個(gè)值A(chǔ)LL：所有值,AnIntroductiontoD

20、atabaseSystem,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,需要配合使用比較運(yùn)算符ANY大于子查詢結(jié)果中的某個(gè)值A(chǔ)LL大于子查詢結(jié)果中的所有值=ANY大于等于子查詢結(jié)果中的某個(gè)值=ALL大于等于子查詢結(jié)果中的所有值）ANY不等于子查詢結(jié)果中的某個(gè)值!=（或）ALL不等于子查詢結(jié)果中的任何一個(gè)值,AnIntroductiontoDatabaseSystem,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,例42查詢其他系中比計(jì)算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡SELECTSname，SageFROMStudentWHERESageCS;/*父查詢塊中的條件*/,AnInt

21、roductiontoDatabaseSystem,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,結(jié)果：執(zhí)行過程：1.RDBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出CS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(20，19)2.處理父查詢，找所有不是CS系且年齡小于20或19的學(xué)生,AnIntroductiontoDatabaseSystem,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,用聚集函數(shù)實(shí)現(xiàn)例42SELECTSname，SageFROMStudentWHERESageCS;,AnIntroductiontoDatabaseSystem,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,

22、例43查詢其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞SELECTSname，SageFROMStudentWHERESageCS;,AnIntroductiontoDatabaseSystem,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,方法二：用聚集函數(shù)SELECTSname，SageFROMStudentWHERESageCS;,AnIntroductiontoDatabaseSystem,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,表3.5ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價(jià)轉(zhuǎn)換關(guān)系,AnIntroductiontoDa

23、tabaseSystem,3.4.3嵌套查詢,一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,1.EXISTS謂詞存在量詞帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出

24、列名無實(shí)際意義2.NOTEXISTS謂詞若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,例44查詢所有選修了1號(hào)課程的學(xué)生姓名。思路分析：本查詢涉及Student和SC關(guān)系在Student中依次取每個(gè)元組的Sno值，用此值去檢查SC關(guān)系若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno=1，則取此Student.Sname送入結(jié)果關(guān)系,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞

25、的子查詢(續(xù)）,用嵌套查詢SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1)；,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,用連接運(yùn)算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=1;,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,例45查詢沒有選修1號(hào)課程的學(xué)生姓名。SELECTSnameFROMStudentWHER

26、ENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1)；,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn))SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：(x)P(x(P),AnIntroductio

27、ntoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,例：例39查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。可以用帶EXISTS謂詞的子查詢替換：SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=劉晨)；,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,例46查詢選修了全部課程的學(xué)生姓名。SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCo

28、urseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno）；,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)函(難點(diǎn))SQL語言中沒有蘊(yùn)函(Implication)邏輯運(yùn)算可以利用謂詞演算將邏輯蘊(yùn)函謂詞等價(jià)轉(zhuǎn)換為：pqpq,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,例47查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號(hào)碼。解題思路：用邏輯蘊(yùn)函表達(dá)：查詢學(xué)

29、號(hào)為x的學(xué)生，對(duì)所有的課程y，只要200215122學(xué)生選修了課程y，則x也選修了y。形式化表示：用P表示謂詞“學(xué)生200215122選修了課程y”用q表示謂詞“學(xué)生x選修了課程y”則上述查詢?yōu)?(y)pq,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,等價(jià)變換：(y)pq(y(pq)(y(pq)y(pq)變換后語義：不存在這樣的課程y，學(xué)生200215122選修了y，而學(xué)生x沒有選。,AnIntroductiontoDatabaseSystem,帶有EXISTS謂詞的子查詢(續(xù)）,用NOTEXISTS謂詞表示：SELECTDISTINCTS

30、noFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=200215122ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)；,AnIntroductiontoDatabaseSystem,3.4數(shù)據(jù)查詢,3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般形式,AnIntroductiontoDatabaseSystem,3.4.4集合查詢,集合操作的種類并操作UNION交操作INTERSECT差操作E

31、XCEPT參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同,AnIntroductiontoDatabaseSystem,集合查詢（續(xù)）,例48查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。方法一：SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19；UNION：將多個(gè)查詢結(jié)果合并起來時(shí)，系統(tǒng)自動(dòng)去掉重復(fù)元組。UNIONALL：將多個(gè)查詢結(jié)果合并起來時(shí)，保留重復(fù)元組,AnIntroductiontoDatabaseSystem,集合查詢（續(xù)）,方法二：SELECTDISTINCT*FROMStudentWHERESdept=CSORSage=19；,AnIntroductiontoDatabaseSystem,集合查詢（續(xù)）,例49查詢選修了課程1或者選修了課程2的學(xué)生。SELECTSnoFROMSCWHE