2023/1/10數據庫系統概論AnIntroductiontoDatabaseSystem第三章關系數據庫標準語言SQL(續1）2023/1/103.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢3.3.6小結2023/1/103.3.3連接查詢同時涉及多個表的查詢稱為連接查詢用來連接兩個表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>

比較運算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>2023/1/10連接查詢（續）連接字段連接謂詞中的列名稱為連接字段連接條件中的各連接字段類型必須是可比的,但不必是相同的2023/1/10連接操作的執行過程嵌套循環法(NESTED-LOOP)首先在表1中找到第一個元組,然后從頭開始掃描表2,逐一查找滿足連接件的元組,找到后就將表1中的第一個元組與該元組拼接起來,形成結果表中一個元組。表2全部查找完后,再找表1中第二個元組,然后再從頭開始掃描表2,逐一查找滿足連接條件的元組,找到后就將表1中的第二個元組與該元組拼接起來,形成結果表中一個元組。重復上述操作,直到表1中的全部元組都處理完畢2023/1/10排序合并法(SORT-MERGE)常用于=連接首先按連接屬性對表1和表2排序對表1的第一個元組,從頭開始掃描表2,順序查找滿足連接條件的元組,找到后就將表1中的第一個元組與該元組拼接起來,形成結果表中一個元組。當遇到表2中第一條大于表1連接字段值的元組時,對表2的查詢不再繼續2023/1/10排序合并法找到表1的第二條元組,然后從剛才的中斷點處繼續順序掃描表2,查找滿足連接條件的元組,找到后就將表1中的第一個元組與該元組拼接起來,形成結果表中一個元組。直接遇到表2中大于表1連接字段值的元組時,對表2的查詢不再繼續重復上述操作,直到表1或表2中的全部元組都處理完畢為止2023/1/10索引連接(INDEX-JOIN)對表2按連接字段建立索引對表1中的每個元組,依次根據其連接字段值查詢表2的索引,從中找到滿足條件的元組,找到后就將表1中的第一個元組與該元組拼接起來,形成結果表中一個元組2023/1/10連接查詢（續）SQL中連接查詢的主要類型

廣義笛卡爾積 等值連接(含自然連接)

非等值連接查詢 自身連接查詢 外連接查詢 復合條件連接查詢2023/1/10一、廣義笛卡爾積不帶連接謂詞的連接很少使用例：

SELECTStudent.*,SC.*FROMStudent,SC2023/1/10二、等值與非等值連接查詢等值連接、自然連接、非等值連接[例32]查詢每個學生及其選修課程的情況。2023/1/10等值連接連接運算符為=的連接操作

[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名屬性時,都必須加表名前綴。引用唯一屬性名時可以加也可以省略表名前綴。2023/1/10等值連接假設Student表、SC表分別有下列數據：

Student表SnoSnameSsexSageSdept95001

李勇

男20CS95002

劉晨

女19IS95003

王敏

女18MA95004

張立

男19IS2023/1/10等值連接SC表SnoCnoGrade95001192950012859500195002950023238890802023/1/10等值連接SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno；或SELECTStudent.*,SC.*FROMStudentinnerjoinSConStudent.Sno=SC.Sno2023/1/10等值連接結果表

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

95001李勇男20 CS 9500119295001李勇男20 CS 9500128595001李勇男20 CS 9500138895002劉晨女19 IS 9500229095002劉晨女19 IS 95002380

2023/1/10自然連接等值連接的一種特殊情況,把目標列中重復的屬性列去掉。[例33]對[例32]用自然連接完成。

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno；或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentinnerjoinSConStudent.Sno=SC.Sno2023/1/10非等值連接查詢連接運算符不是=的連接操作

[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>比較運算符：>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

2023/1/10三、自身連接一個表與其自己進行連接,稱為表的自身連接需要給表起別名以示區別由于所有屬性名都是同名屬性,因此必須使用別名前綴2023/1/10自身連接（續）[例34]查詢每一門課的間接先修課（即先修課的先修課）SELECTo,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=o；或SELECTo,SECOND.CpnoFROMCourseFIRSTinnerjoinCourseSECOND onFIRST.Cpno=o2023/1/10自身連接（續）FIRST表（Course表）

CnoCnameCpnoCcredit1數據庫542數學

23信息系統144操作系統635數據結構746數據處理

27PASCAL語言642023/1/10自身連接（續）SECOND表（Course表）

CnoCnameCpnoCcredit1數據庫542數學

23信息系統144操作系統635數據結構746數據處理

27PASCAL語言642023/1/10自身連接（續）查詢結果

173556

cnocpno2023/1/10四、外連接（OuterJoin）外連接與普通連接的區別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體,將主體表中不滿足連接條件的元組一并輸出2023/1/10外連接（續）

[例33]查詢每個學生及其選修課程的情況包括沒有選修課程的學生----用外連接操作SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentleftjoinSConStudent.Sno=SC.Sno或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentrightjoinSConStudent.Sno=SC.Sno或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentfulljoinSConStudent.Sno=SC.Sno2023/1/10外連接（續）結果：

Student.Sno SnameSsexSageSdeptCnoGrade

95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002劉晨女19IS29095002劉晨女19IS38095003王敏女18MA95004張立男19IS2023/1/10外連接（續）非主體表有一“萬能”的虛行,該行全部由空值組成虛行可以和主體表中所有不滿足連接條件的元組進行連接由于虛行各列全部是空值,因此與虛行連接的結果中,來自非主體表的屬性值全部是空值2023/1/10五、復合條件連接有多個連接條件時,稱為復合條件連接[例35]查詢選修2號課程且成績在90分以上的所有學生的學號、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.Sno AND o=‘2’ AND SC.Grade>90；或SELECT Student.Sno,student.SnameFROMStudentinnerjoinSConStudent.Sno=SC.Snowhere o=2ANDSC.Grade>902023/1/10多表連接[例36]查詢每個學生的學號、姓名、選修的課程名及成績。SELECT Student.Sno,Sname,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno=SC.Snoando=o；或SELECT Student.Sno,Sname,Cname,GradeFROMStudentINNERJOINscONStudent.sno=SC.snoINNERJOINCourseONo=o

2023/1/103.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢3.3.6小結2023/1/103.3.4嵌套查詢嵌套查詢概述嵌套查詢分類嵌套查詢求解方法引出子查詢的謂詞

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

2023/1/10嵌套查詢(續)

SELECTSname 外層查詢/父查詢

FROMStudentWHERESnoIN(SELECTSno內層查詢/子查詢

FROMSCWHERECno=‘2’)等價于SELECTSnameFROMStudentINNERJOINSCONStudent.Sno=SC.SnoWHERECno='2'2023/1/10嵌套查詢(續)子查詢的限制不能使用ORDERBY子句層層嵌套方式反映了SQL語言的結構化有些嵌套查詢可以用連接運算替代2023/1/10嵌套查詢分類不相關子查詢子查詢的查詢條件不依賴于父查詢相關子查詢子查詢的查詢條件依賴于父查詢2023/1/10嵌套查詢求解方法不相關子查詢是由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解,子查詢的結果用于建立其父查詢的查找條件。2023/1/10嵌套查詢求解方法（續）相關子查詢首先取外層查詢中表的第一個元組,根據它與內層查詢相關的屬性值處理內層查詢,若WHERE子句返回值為真,則取此元組放入結果表；然后再取外層表的下一個元組；重復這一過程,直至外層表全部檢查完為止。2023/1/10引出子查詢的謂詞帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢2023/1/10一、帶有IN謂詞的子查詢[例37]查詢與“劉晨”在同一個系學習的學生。

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

SELECTSdeptFROMStudentWHERESname='劉晨'； 結果為：

Sdept IS2023/1/10帶有IN謂詞的子查詢（續）②查找所有在IS系學習的學生。

SELECTSno,Sname,SdeptFROMStudentWHERESdept='IS'；結果為：SnoSnameSdept95001劉晨IS95004張立IS2023/1/10

構造嵌套查詢將第一步查詢嵌入到第二步查詢的條件中

SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘

劉晨’)；此查詢為不相關子查詢。DBMS求解該查詢時也是分步去做的。2023/1/10帶有IN謂詞的子查詢（續）

用自身連接完成本查詢要求SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1innerjoinStudentS2onS1.Sdept=S2.SdeptWHERES2.Sname='劉晨'；2023/1/10帶有IN謂詞的子查詢（續）父查詢和子查詢中的表均可以定義別名

SELECTSno,Sname,SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=‘

劉晨’)；2023/1/10帶有IN謂詞的子查詢（續）[例38]查詢選修了課程名為“信息系統”的學生學號和姓名

SELECTSno,Sname③最后在Student關系中

FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno②然后在SC關系中找出選

FROMSC修了3號課程的學生學號

WHERECnoIN(SELECTCno①首先在Course關系中找出“信

FROMCourse息系統”的課程號,結果為3號

WHERECname=‘信息系統’));2023/1/10帶有IN謂詞的子查詢（續）結果：

Sno Sname---------95001李勇

95002劉晨2023/1/10帶有IN謂詞的子查詢（續）用連接查詢SELECTStudent.Sno,SnameFROMStudentINNERJOINSC ONStudent.Sno=SC.Sno INNERJOINCourse ONo=oWHEREame='信息系統'；2023/1/10二、帶有比較運算符的子查詢

當能確切知道內層查詢返回單值時,可用比較運算符（>,<,=,>=,<=,!=或<>）。與ANY或ALL謂詞配合使用2023/1/10帶有比較運算符的子查詢（續）例：假設一個學生只可能在一個系學習,并且必須屬于一個系,則在[例37]可以用=代替IN

：

SELECTSno,Sname,SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname='劉晨'；2023/1/10帶有比較運算符的子查詢（續）

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

錯誤的例子：

SELECTSno,Sname,SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘

劉晨’

)=Sdept；2023/1/10三、帶有ANY或ALL謂詞的子查詢謂詞語義ANY：任意一個值ALL：所有值2023/1/10帶有ANY或ALL謂詞的子查詢（續）需要配合使用比較運算符>ANY 大于子查詢結果中的某個值

>ALL 大于子查詢結果中的所有值<ANY 小于子查詢結果中的某個值<ALL 小于子查詢結果中的所有值>=ANY 大于等于子查詢結果中的某個值>=ALL 大于等于子查詢結果中的所有值<=ANY 小于等于子查詢結果中的某個值<=ALL 小于等于子查詢結果中的所有值=ANY 等于子查詢結果中的某個值=ALL 等于子查詢結果中的所有值（通常沒有實際意義）!=（或<>）ANY 不等于子查詢結果中的某個值!=（或<>）ALL 不等于子查詢結果中的任何一個值2023/1/10帶有ANY或ALL謂詞的子查詢（續）[例39]查詢其他系中比信息系任意一個(其中某一個)學生年齡小的學生姓名和年齡

SELECTSname,SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='IS')

ANDSdept<>'IS';/*注意這是父查詢塊中的條件*/2023/1/10帶有ANY或ALL謂詞的子查詢（續）結果

Sname Sage

王敏18執行過程1.DBMS執行此查詢時,首先處理子查詢,找出

IS系中所有學生的年齡,構成一個集合(19,18)2.處理父查詢,找所有不是IS系且年齡小于

19或18的學生2023/1/10帶有ANY或ALL謂詞的子查詢（續）ANY和ALL謂詞有時可以用集函數實現ANY與ALL與集函數的對應關系

=

<>或!=

<<=>>=ANY

IN

--

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

NOTIN

<MIN<=MIN>MAX>=MAX2023/1/10帶有ANY或ALL謂詞的子查詢（續）用集函數實現子查詢通常比直接用ANY或ALL查詢效率要高,因為前者通常能夠減少比較次數2023/1/10帶有ANY或ALL謂詞的子查詢（續）[例39']：用集函數實現[例39]

SELECTSname,SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS';2023/1/10帶有ANY或ALL謂詞的子查詢（續）[例40]查詢其他系中比信息系所有學生年齡都小的學生姓名及年齡。方法一：用ALL謂詞

SELECTSname,SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS’;查詢結果為空表。2023/1/10帶有ANY或ALL謂詞的子查詢（續）

方法二：用集函數

SELECTSname,SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;2023/1/10四、帶有EXISTS謂詞的子查詢1.EXISTS謂詞2.NOTEXISTS謂詞3.不同形式的查詢間的替換4.相關子查詢的效率5.用EXISTS/NOTEXISTS實現全稱量詞6.用EXISTS/NOTEXISTS實現邏輯蘊函2023/1/10帶有EXISTS謂詞的子查詢(續）1.EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數據,只產生邏輯真值“true”或邏輯假值“false”。若內層查詢結果非空,則返回真值若內層查詢結果為空,則返回假值由EXISTS引出的子查詢,其目標列表達式通常都用*,因為帶EXISTS的子查詢只返回真值或假值,給出列名無實際意義2.NOTEXISTS謂詞2023/1/10帶有EXISTS謂詞的子查詢(續）[例41]查詢所有選修了1號課程的學生姓名。

思路分析：本查詢涉及Student和SC關系。在Student中依次取每個元組的Sno值,用此值去檢查SC關系。若SC中存在這樣的元組,其Sno值等于此Student.Sno值,并且其Cno='1',則取此Student.Sname送入結果關系。2023/1/10帶有EXISTS謂詞的子查詢(續）[例41]查詢所有選修了1號課程的學生姓名。用嵌套查詢

SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相關子查詢*/WHERESno=Student.SnoAND Cno='1')；2023/1/10帶有EXISTS謂詞的子查詢(續）用連接運算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND o='1';2023/1/10帶有EXISTS謂詞的子查詢(續）[例42]查詢沒有選修1號課程的學生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.Sno ANDCno='1')；此例用連接運算難于實現

2023/1/10帶有EXISTS謂詞的子查詢(續）3.不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換。

2023/1/10帶有EXISTS謂詞的子查詢(續）例：[例37]查詢與“劉晨”在同一個系學習的學生?？梢杂脦XISTS謂詞的子查詢替換：

SELECTSno,Sname,Sdept FROMStudentS1 WHEREEXISTS (SELECT* FROMStudentS2 WHERES2.Sdept=S1.SdeptAND S2.Sname='劉晨')；2023/1/10帶有EXISTS謂詞的子查詢(續）5.用EXISTS/NOTEXISTS實現全稱量詞SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞：

(x)P≡

(x(P))2023/1/10帶有EXISTS謂詞的子查詢(續）[例43]查詢選修了全部課程的學生姓名。SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=o）；2023/1/10帶有EXISTS謂詞的子查詢(續）

6.用EXISTS/NOTEXISTS實現邏輯蘊函SQL語言中沒有蘊函(Implication)邏輯運算可以利用謂詞演算將邏輯蘊函謂詞等價轉換為：

pq≡p∨q2023/1/10帶有EXISTS謂詞的子查詢(續）

[例44]查詢至少選修了學生95002選修的全部課程的學生號碼。解題思路：用邏輯蘊函表達：查詢學號為x的學生,對所有的課程y,只要95002學生選修了課程y,則x也選修了y。形式化表示： 用P表示謂詞“學生95002選修了課程y”

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

則上述查詢為:(y)pq2023/1/10帶有EXISTS謂詞的子查詢(續）等價變換：

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

y(p∧q)變換后語義：不存在這樣的課程y,學生95002選修了y,而學生x沒有選。2023/1/10帶有EXISTS謂詞的子查詢(續）

用NOTEXISTS謂詞表示：SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='95002'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDo))；2023/1/103.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢3.3.6小結2023/1/103.3.5集合查詢標準SQL直接支持的集合操作種類并操作(UNION)一般商用數據庫支持的集合操作種類并操作(UNION)交操作(INTERSECT)差操作(MINUS)2023/1/101．并操作形式

<查詢塊> UNION <查詢塊>參加UNION操作的各結果表的列數必須相同；對應項的數據類型也必須相同2023/1/10并操作（續）[例45]查詢計算機科學系的學生及年齡不大于19歲的學生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；2023/1/10并操作（續）方法二：

SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；2023/1/10并操作（續）[例46]查詢選修了課程1或者選修了課程2的學生。方法一：

SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；2023/1/10并操作（續）方法二：

SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；2023/1/10并操作（續）[例47]設數據庫中有一教師表Teacher(Tno,Tname,...)。查詢學校中所有師生的姓名。

SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;2023/1/102．交操作標準SQL中沒有提供集合交操作,但可用其他方法間接實現。2023/1/102．交操作[例48]查詢計算機科學系的學生與年齡不大于19歲的學生的交集本例實際上就是查詢計算機科學系中年齡不大于19歲的學生

SELECT*FROMStudentWHERESdept='CS'ANDSage<=19；2023/1/10交操作（續）[例49]查詢選修課程1的學生集合與選修課程2的學生集合的交集本例實際上是查詢既選修了課程1又選修了課程2的學生

SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2')；2023/1/10交操作（續）[例50]查詢學生姓名與教師姓名的交集本例實際上是查詢學校中與教師同名的學生姓名

SELECTDISTINCTSnameFROMStudentWHERESnameIN (SELECTTnameFROMTeacher);2023/1/103．差操作

標準SQL中沒有提供集合差操作,但可用其他方法間接實現。2023/1/103．差操作[例51]查詢計算機科學系的學生與年齡不大于19歲的學生的差集。本例實際上是查詢計算機科學系中年齡大于19歲的學生

SELECT*FROMStudentWHERESdept='CS'ANDSage>19；2023/1/10差操作（續）[例52]查詢學生姓名與教師姓名的差集本例實際上是查詢學校中未與教師同名的學生姓名

SELECTDISTINCTSnameFROMStudentWHERESnameNOTIN (SELECTTname FROMTeacher);2023/1/104.對集合操作結果的排序ORDERBY子句只能用于對最終查詢結果排序,不能對中間結果排序任何情況下,ORDERBY子句只能出現在最后對集合操作結果排序