會計學(xué)1AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL續(xù)3.4數(shù)據(jù)查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般形式

第1頁/共79頁3.4.2連接查詢連接查詢：同時涉及多個表的查詢連接條件或連接謂詞：用來連接兩個表的條件 一般格式：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>連接字段：連接謂詞中的列名稱連接條件中的各連接字段類型必須是可比的，但名字不必是相同的第2頁/共79頁連接操作的執(zhí)行過程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢

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

第5頁/共79頁索引連接(INDEX-JOIN)對表2按連接字段建立索引對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組第6頁/共79頁連接查詢（續(xù)）一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接第7頁/共79頁一、等值與非等值連接查詢等值連接：連接運算符為=[例33]查詢每個學(xué)生及其選修課程的情況

SELECTStudent.*，SC.* FROMStudent，SC WHEREStudent.Sno=SC.Sno；第8頁/共79頁等值與非等值連接查詢（續(xù)）Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122劉晨女19CS200215122290200215122劉晨女19CS200215122380查詢結(jié)果：第9頁/共79頁等值與非等值連接查詢（續(xù)）自然連接：[例34]對[例33]用自然連接完成。

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；第10頁/共79頁連接查詢（續(xù)）一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接第11頁/共79頁二、自身連接自身連接：一個表與其自己進行連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴[例35]查詢每一門課的間接先修課（即先修課的先修課）

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；第12頁/共79頁自身連接（續(xù)）FIRST表（Course表）

CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語言64第13頁/共79頁自身連接（續(xù)）CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語言64SECOND表（Course表）第14頁/共79頁自身連接（續(xù)）查詢結(jié)果：CnoPcno173556第15頁/共79頁連接查詢（續(xù)）一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接第16頁/共79頁三、外連接外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出[例36]改寫[例33]SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno)；

第17頁/共79頁外連接（續(xù)）執(zhí)行結(jié)果：Student.SnoSnameSsexSageSdeptCnoGrade200215121李勇男20CS192200215121李勇男20CS285200215121李勇男20CS388200215122劉晨女19CS290200215122劉晨女19CS380200215123王敏女18MANULLNULL200215125張立男19ISNULLNULL第18頁/共79頁外連接（續(xù)）

左外連接列出左邊關(guān)系（如本例Student）中所有的元組右外連接列出右邊關(guān)系中所有的元組第19頁/共79頁連接查詢（續(xù)）一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接第20頁/共79頁四、復(fù)合條件連接復(fù)合條件連接：WHERE子句中含多個連接條件[例37]查詢選修2號課程且成績在90分以上的所有學(xué)生

SELECTStudent.Sno,Sname FROMStudent,SC WHEREStudent.Sno=SC.SnoAND/*連接謂詞*/SC.Cno=‘2’ANDSC.Grade>90；

/*其他限定條件*/第21頁/共79頁復(fù)合條件連接（續(xù)）[例38]查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績

SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，Course/*多表連接*/WHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；

第22頁/共79頁3.4數(shù)據(jù)查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般形式

第23頁/共79頁嵌套查詢(續(xù))嵌套查詢概述一個SELECT-FROM-WHERE語句稱為一個查詢塊將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢

第24頁/共79頁嵌套查詢(續(xù))SELECTSname /*外層查詢/父查詢*/FROMStudentWHERESnoIN

（SELECTSno/*內(nèi)層查詢/子查詢*/FROMSCWHERECno='2'）；第25頁/共79頁嵌套查詢(續(xù))子查詢的限制不能使用ORDERBY子句層層嵌套方式反映了SQL語言的結(jié)構(gòu)化有些嵌套查詢可以用連接運算替代第26頁/共79頁嵌套查詢求解方法不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。第27頁/共79頁嵌套查詢求解方法（續(xù)）相關(guān)子查詢：子查詢的查詢條件依賴于父查詢首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表然后再取外層表的下一個元組重復(fù)這一過程，直至外層表全部檢查完為止第28頁/共79頁3.4.3嵌套查詢

一、帶有IN謂詞的子查詢

二、帶有比較運算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢第29頁/共79頁一、帶有IN謂詞的子查詢[例39]查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。

此查詢要求可以分步來完成①確定“劉晨”所在系名

SELECTSdeptFROMStudentWHERESname='劉晨'； 結(jié)果為：CS第30頁/共79頁帶有IN謂詞的子查詢（續(xù)）②查找所有在IS系學(xué)習(xí)的學(xué)生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='CS'；結(jié)果為：SnoSnameSdept200215121李勇CS200215122劉晨CS第31頁/共79頁帶有IN謂詞的子查詢（續(xù)）將第一步查詢嵌入到第二步查詢的條件中

SELECTSno，Sname，Sdept FROMStudent WHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；此查詢?yōu)椴幌嚓P(guān)子查詢。第32頁/共79頁帶有IN謂詞的子查詢（續(xù)）

用自身連接完成[例39]查詢要求

SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='劉晨'；第33頁/共79頁帶有IN謂詞的子查詢（續(xù)）[例40]查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名

SELECTSno，Sname③最后在Student關(guān)系中

FROMStudent取出Sno和Sname WHERESnoIN(SELECTSno②然后在SC關(guān)系中找出選

FROMSC修了3號課程的學(xué)生學(xué)號

WHERECnoIN(SELECTCno①首先在Course關(guān)系中找出

FROMCourse“信息系統(tǒng)”的課程號，為3號

WHERECname=‘信息系統(tǒng)’

));第34頁/共79頁帶有IN謂詞的子查詢（續(xù)）用連接查詢實現(xiàn)[例40]

SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統(tǒng)’；第35頁/共79頁3.4.3嵌套查詢

一、帶有IN謂詞的子查詢二、帶有比較運算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢第36頁/共79頁二、帶有比較運算符的子查詢

當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（>，<，=，>=，<=，!=或<>）。與ANY或ALL謂詞配合使用第37頁/共79頁帶有比較運算符的子查詢（續(xù)）例：假設(shè)一個學(xué)生只可能在一個系學(xué)習(xí)，并且必須屬于一個系，則在[例39]可以用=代替IN

：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；第38頁/共79頁帶有比較運算符的子查詢（續(xù)）

子查詢一定要跟在比較符之后

錯誤的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘劉晨’)=Sdept；第39頁/共79頁帶有比較運算符的子查詢（續(xù)）［例41］找出每個學(xué)生超過他選修課程平均成績的課程號。

SELECTSno，CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade) FROMSCyWHEREy.Sno=x.Sno);相關(guān)子查詢第40頁/共79頁帶有比較運算符的子查詢（續(xù)）可能的執(zhí)行過程：

1.從外層查詢中取出SC的一個元組x，將元組x的Sno值（200215121）傳送給內(nèi)層查詢。

SELECTAVG(Grade)FROMSCyWHEREy.Sno='200215121';2.執(zhí)行內(nèi)層查詢，得到值88（近似值），用該值代替內(nèi)層查詢，得到外層查詢：

SELECTSno，CnoFROMSCxWHEREGrade>=88；第41頁/共79頁帶有比較運算符的子查詢（續(xù)）3.執(zhí)行這個查詢，得到（200215121，1）（200215121，3）4.外層查詢?nèi)〕鱿乱粋€元組重復(fù)做上述1至3步驟，直到外層的SC元組全部處理完畢。結(jié)果為:

（200215121，1）（200215121，3）（200215122，2）第42頁/共79頁3.4.3嵌套查詢

一、帶有IN謂詞的子查詢二、帶有比較運算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢第43頁/共79頁三、帶有ANY（SOME）或ALL謂詞的子查詢

謂詞語義ANY：任意一個值A(chǔ)LL：所有值第44頁/共79頁帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）需要配合使用比較運算符>ANY 大于子查詢結(jié)果中的某個值

>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個值<ALL 小于子查詢結(jié)果中的所有值>=ANY 大于等于子查詢結(jié)果中的某個值>=ALL 大于等于子查詢結(jié)果中的所有值<=ANY 小于等于子查詢結(jié)果中的某個值<=ALL 小于等于子查詢結(jié)果中的所有值=ANY 等于子查詢結(jié)果中的某個值=ALL 等于子查詢結(jié)果中的所有值（通常沒有實際意義）!=（或<>）ANY 不等于子查詢結(jié)果中的某個值!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個值第45頁/共79頁帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）[例42]查詢其他系中比計算機科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡

SELECTSname，SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='CS')

ANDSdept<>‘CS';/*父查詢塊中的條件*/第46頁/共79頁帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）結(jié)果：

執(zhí)行過程：

1.RDBMS執(zhí)行此查詢時，首先處理子查詢，找出

CS系中所有學(xué)生的年齡，構(gòu)成一個集合(20，19)2.處理父查詢，找所有不是CS系且年齡小于

20或19的學(xué)生SnameSage王敏18張立19第47頁/共79頁帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）用聚集函數(shù)實現(xiàn)[例42]

SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept=‘CS')ANDSdept<>'CS’;第48頁/共79頁帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）[例43]查詢其他系中比計算機科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS')ANDSdept<>'CS’;第49頁/共79頁帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）

方法二：用聚集函數(shù)

SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='CS')ANDSdept<>'CS’;第50頁/共79頁帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）表3.5ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價轉(zhuǎn)換關(guān)系

=

<>或!=<<=>>=ANY

IN

--

<MAX<=MAX>MIN>=MINALL--

NOTIN

<MIN<=MIN>MAX>=MAX第51頁/共79頁3.4.3嵌套查詢

一、帶有IN謂詞的子查詢二、帶有比較運算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢第52頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）1.EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值由EXISTS引出的子查詢，其目標(biāo)列表達式通常都用*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義2.NOTEXISTS謂詞若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值第53頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）[例44]查詢所有選修了1號課程的學(xué)生姓名。

思路分析：本查詢涉及Student和SC關(guān)系在Student中依次取每個元組的Sno值，用此值去檢查SC關(guān)系若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno='1'，則取此Student.Sname送入結(jié)果關(guān)系第54頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）用嵌套查詢

SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；

第55頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）用連接運算

SELECTSname FROMStudent,SC WHEREStudent.Sno=SC.SnoANDSC.Cno='1';第56頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）[例45]查詢沒有選修1號課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；第57頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）

不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換用EXISTS/NOTEXISTS實現(xiàn)全稱量詞(難點)SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞：

(x)P≡(x(P))

第58頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）例：[例39]查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生?？梢杂脦XISTS謂詞的子查詢替換：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS

(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=‘劉晨’)；第59頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）[例46]查詢選修了全部課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno

））；第60頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）

用EXISTS/NOTEXISTS實現(xiàn)邏輯蘊函(難點)SQL語言中沒有蘊函(Implication)邏輯運算可以利用謂詞演算將邏輯蘊函謂詞等價轉(zhuǎn)換為：

pq≡p∨q第61頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）[例47]查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號碼。解題思路：用邏輯蘊函表達：查詢學(xué)號為x的學(xué)生，對所有的課程y，只要200215122學(xué)生選修了課程y，則x也選修了y。形式化表示： 用P表示謂詞“學(xué)生200215122選修了課程y”

用q表示謂詞“學(xué)生x選修了課程y”

則上述查詢?yōu)?(y)pq

第62頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）等價變換：

(y)pq≡(y((pq))≡(y((p∨q)))≡

y(p∧q)變換后語義：不存在這樣的課程y，學(xué)生200215122選修了y，而學(xué)生x沒有選。第63頁/共79頁帶有EXISTS謂詞的子查詢(續(xù)）

用NOTEXISTS謂詞表示：

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='200215122'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；第64頁/共79頁3.4數(shù)據(jù)查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般形式第65頁/共79頁3.4.4集合查詢集合操作的種類并操作UNION交操作INTERSECT差操作EXCEPT參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對應(yīng)項的數(shù)據(jù)類型也必須相同第66頁/共79頁集合查詢（續(xù)）[例48]查詢計算機科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；UNION：將多個查詢結(jié)果合并起來時，系統(tǒng)自動去掉重復(fù)元組。UNIONALL：將多個查詢結(jié)果合并起來時，保留重復(fù)元組第67頁/共79頁集合查詢（續(xù)）方法二：

SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；第68頁/共79頁集合查詢（續(xù)）[例49]查詢選修了課程1或者選修了課程2的學(xué)生。

SELECTSnoFROMSCWHERE