第3章數(shù)據(jù)庫關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL(連接查詢)

3.4.2連接查詢連接查詢：同時(shí)涉及多個(gè)表的查詢連接條件或連接謂詞：用來連接兩個(gè)表的條件 一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>連接字段：連接謂詞中的列名稱連接條件中的各連接字段類型必須是可比的，但名字不必是相同的一、等值與非等值連接查詢等值連接：連接運(yùn)算符為=[例33]查詢每個(gè)學(xué)生及其選修課程的情況

Student表學(xué)號(hào)Sno姓名Sname性別

Ssex年齡

Sage所在系

Sdept200215121200215122200215123200515125李勇劉晨王敏張立男女女男20191819CSCSMAIS學(xué)號(hào)Sno

課程號(hào)

Cno

成績(jī)

Grade200215121200215121200215121200215122200215122123239285889080SC表查詢每個(gè)學(xué)生及其選修課程的情況SELECTStudent.*，SC.* FROMStudent，SC WHEREStudent.Sno=SC.Sno；等值與非等值連接查詢（續(xù)）Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122劉晨女19CS200215122290200215122劉晨女19CS200215122380查詢結(jié)果：嵌套循環(huán)連接算法等值與非等值連接查詢（續(xù)）自然連接：去掉目標(biāo)列中重復(fù)的屬性列[例34]對(duì)[例33]用自然連接完成。

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；二、自身連接自身連接：一個(gè)表與其自己進(jìn)行連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴[例35]查詢每一門課的間接先修課（即先修課的先修課）

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；自身連接（續(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語言64CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)

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

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

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

左外連接列出左邊關(guān)系（如本例Student）中所有的元組右外連接列出右邊關(guān)系中所有的元組四、復(fù)合條件連接復(fù)合條件連接：WHERE子句中含多個(gè)連接條件[例37]查詢選修2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生

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

/*其他限定條件*/執(zhí)行過程:

先從SC中挑選出Cno='2'并且Grade>90的元組形成一個(gè)中間關(guān)系

再和Student中滿足連接條件的元組進(jìn)行連接得到最終的結(jié)果關(guān)系復(fù)合條件連接（續(xù)）[例38]查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)

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

嵌套查詢嵌套查詢概述一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢

嵌套查詢(續(xù))SELECTSname /*外層查詢/父查詢*/FROMStudentWHERESnoIN

（SELECTSno/*內(nèi)層查詢/子查詢*/FROMSCWHERECno='2'）；嵌套查詢(續(xù))子查詢的限制不能使用ORDERBY子句層層嵌套方式反映了SQL語言的結(jié)構(gòu)化有些嵌套查詢可以用連接運(yùn)算替代3.4.3嵌套查詢

一、帶有IN謂詞的子查詢

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

此查詢可以分步來完成

學(xué)號(hào)Sno姓名Sname性別

Ssex年齡

Sage所在系

Sdept200215121200215122200215123200515125李勇劉晨王敏張立男女女男20191819CSCSMAIS①確定“劉晨”所在系名

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

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

SELECTSno，Sname，Sdept FROMStudent WHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；此查詢?yōu)椴幌嚓P(guān)子查詢。嵌套查詢求解方法不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個(gè)子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。帶有IN謂詞的子查詢（續(xù)）

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

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

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

SELECTSno，Sname FROMStudent WHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=‘信息系統(tǒng)’

));①首先在Course關(guān)系中找出“信息系統(tǒng)”的課程號(hào)，為3號(hào)②然后在SC關(guān)系中找出選修了3號(hào)課程的學(xué)生學(xué)號(hào)③最后在Student關(guān)系中取出Sno和Sname帶有IN謂詞的子查詢（續(xù)）用連接查詢實(shí)現(xiàn)[例40]

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

一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢二、帶有比較運(yùn)算符的子查詢

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

：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；帶有比較運(yùn)算符的子查詢（續(xù)）［例41］找出每個(gè)學(xué)生超過他選修課程平均成績(jī)的課程號(hào)。SELECTSno,CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno)相關(guān)子查詢帶有比較運(yùn)算符的子查詢（續(xù)）可能的執(zhí)行過程：

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

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

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

（201215121，1）（201215121，3）（201215122，2）嵌套查詢求解方法（續(xù)）相關(guān)子查詢：子查詢的查詢條件依賴于父查詢首先取外層查詢中表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表然后再取外層表的下一個(gè)元組重復(fù)這一過程，直至外層表全部檢查完為止3.4.3嵌套查詢

一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢

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

>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個(gè)值<ALL 小于子查詢結(jié)果中的所有值>=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ì)算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡

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

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

執(zhí)行過程：

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

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

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

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

SELECTSname,SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS')ANDSdept<>'CS'帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）方法二：用聚集函數(shù)

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

=

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

IN

--

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

NOTIN

<MIN<=MIN>MAX>=MAX例如：=ANY等價(jià)于IN謂詞3.4.3嵌套查詢

一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢帶有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的子查詢只返回真值或假值，給出列名無實(shí)際意義2.NOTEXISTS謂詞若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值帶有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)系相關(guān)子查詢帶有EXISTS謂詞的子查詢(續(xù)）用嵌套查詢

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

帶有EXISTS謂詞的子查詢(續(xù)）用連接運(yùn)算???

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

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

不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換帶有EXISTS謂詞的子查詢(續(xù)）例：[例39]查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生?？梢杂脦XISTS謂詞的子查詢替換：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS

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

SELECTSnameFROMStudentWHERENOTEXISTS------------------------條件①（SELECT*FROMCourseWHERENOTEXISTS-------------條件②

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

））；內(nèi)①內(nèi)②記錄假設(shè)假設(shè)全部課程為001，002有兩名學(xué)生選課：

20010101選課為001，002

20010102選課為001依據(jù)相關(guān)子查詢的執(zhí)行方式（由里向外）如下表學(xué)號(hào)全部課程內(nèi)②條件②內(nèi)①條件①是否取值20010101(選001,002）001非空F002非空F空T是20010102（(選001）001非空F002空T非空F否3.4.4集合查詢集合操作的種類并操作UNION交操作INTERSECT差操作EXCEPT參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同集合查詢（續(xù)）[例48]查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；UNION：將多個(gè)查詢結(jié)果合并起來時(shí)，系統(tǒng)自動(dòng)去掉重復(fù)元組。UNIONALL：將多個(gè)查詢結(jié)果合并起來時(shí)，保留重復(fù)元組

集合查詢（續(xù)）[例49]查詢選修了課程1或者選修了課程2的學(xué)生。

SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；集合查詢（續(xù)）[例50]查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集SELECT*FROMStudentWHERESdept='CS'INTERSECTSELECT*FROMStudentWHERESage<=19

SELECT*FROMStudentWHERESdept='CS'Andexists(SELECT*FROMStudentWHERESage<=19)集合查詢（續(xù)）[例50]實(shí)際上就是查詢計(jì)算機(jī)科學(xué)系中年齡不大于19歲的學(xué)生

SELECT* FROMStudent WHERESdept='CS'ANDSage<=19；[例51]查詢既選修了課程1又選修了課程2的學(xué)生SELECTSno FROMSC WHERECno=‘1’

INTERSECT SELECTSno FROMSC WHERECno=‘2’;集合查詢（續(xù)）[例51]查詢選修課程1的學(xué)生集合與選修課程2的學(xué)生集合的交集SELECTSnoFROMSCWHERECno='001'ANDSnoIN(SELECTSnoFROMSCWHERECno='002')或SELECTSnoFROMSCWHERECno='001'ANDexists(SELECT*FROMSCWHERECno='002')集合查詢（續(xù)）[例52]查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的差集。即查詢計(jì)算機(jī)系中年齡大于19歲的學(xué)生

SELECT*FROMStudentWHERESdept='CS'

EXCEPTSELECT*FROMStudentWHERESage<=19;集合查詢（續(xù)）[例52]查詢計(jì)算機(jī)科學(xué)系中年齡大于19歲的學(xué)生

SELECT*FROMStudentWHERESdept='CS'ANDSage>19；基于派生表的查詢子查詢不僅可以出現(xiàn)在WHERE子句中，還可以出現(xiàn)在FROM子句中，這時(shí)子查詢生成的臨時(shí)派生表（DerivedTable）成為主查詢的查詢對(duì)象

基于派生表的查詢[例]找出每個(gè)學(xué)生超過他自己選修課程平均成績(jī)的課程號(hào)

SELECTSno,Cno

FROMSC,(SELECTSno,Avg(Grade)

FROMSC

GROUPBYSno)

ASAvg_sc(avg_sno,avg_grade)

WHERESC.Sno=Avg_sc.avg_sno

andSC.Grade>=Avg_sc.avg_grade

基于派生表的查詢?nèi)绻硬樵冎袥]有聚集函數(shù)，派生表可以不指定屬性列，子查詢SELECT子句后面的列名為其缺省屬性。

[例]查詢所有選修了1號(hào)課程的學(xué)生姓名

SELECTSname

FROMStudent,

(SELECTSnoFROMSCWHERECno='1') ASSC1

WHEREStudent.Sno=SC1.Sno;通過FROM子句生成派生表時(shí)，AS可省略，但必須指定別名SELECT語句的一般格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[別名][，<目標(biāo)列表達(dá)式>[別名]]…FROM

<表名或視圖名>[別名][，<表名或視圖名>[別名]]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING

<條件表達(dá)式>