,數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem,xx大學(xué)信息學(xué)院,第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL（續(xù)1）,第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,3.1SQL概述3.2學(xué)生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結(jié),3.4數(shù)據(jù)查詢,3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5基于派生表的查詢3.4.5Select語句的一般形式,3.4.2連接查詢,連接查詢：同時涉及兩個以上的表的查詢連接條件或連接謂詞：用來連接兩個表的條件一般格式：.BETWEEN.AND.連接字段：連接謂詞中的列名稱連接條件中的各連接字段類型必須是可比的，但名字不必相同,連接查詢（續(xù)）,1.等值與非等值連接查詢2.自身連接3.外連接4.多表連接,1.等值與非等值連接查詢,等值連接：連接運算符為=例3.49查詢每個學(xué)生及其選修課程的情況SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;,等值與非等值連接查詢（續(xù)）,查詢結(jié)果：,連接操作的執(zhí)行過程,（1）嵌套循環(huán)法（NESTED-LOOP）首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢注：連接操作的執(zhí)行過程，在第九章關(guān)系查詢處理和查詢優(yōu)化中將比較詳細(xì)地講解，在愛課程網(wǎng)9.1節(jié)中還有連接操作的實現(xiàn)的動畫。這里只是先簡單介紹一下。,連接操作的執(zhí)行過程（續(xù)）,（2）排序合并法（SORT-MERGE）常用于=連接首先按連接屬性對表1和表2排序?qū)Ρ?的第一個元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時，對表2的查詢不再繼續(xù),連接操作的執(zhí)行過程（續(xù)）,（2）排序合并法（續(xù)）找到表1的第二條元組，然后從剛才的中斷點處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續(xù)重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止,連接操作的執(zhí)行過程（續(xù)）,（3）索引連接（INDEX-JOIN）對表2按連接字段建立索引對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組,等值與非等值連接查詢（續(xù)）,自然連接例3.50對例3.49用自然連接完成。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;,等值與非等值連接查詢（續(xù)）,一條SQL語句可以同時完成選擇和連接查詢，這時WHERE子句是由連接謂詞和選擇謂詞組成的復(fù)合條件。例3.51查詢選修2號課程且成績在90分以上的所有學(xué)生的學(xué)號和姓名。SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2ANDSC.Grade90;執(zhí)行過程:先從SC中挑選出Cno=2并且Grade90的元組形成一個中間關(guān)系再和Student中滿足連接條件的元組進(jìn)行連接得到最終的結(jié)果關(guān)系,連接查詢（續(xù)）,1.等值與非等值連接查詢2.自身連接3.外連接4.多表連接,2.自身連接,自身連接：一個表與其自己進(jìn)行連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴例3.52查詢每一門課的間接先修課（即先修課的先修課）SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;,自身連接（續(xù)）,FIRST表（Course表）SECOND表（Course表）,自身連接（續(xù)）,查詢結(jié)果：,連接查詢（續(xù)）,1.等值與非等值連接查詢2.自身連接3.外連接4.多表連接,3.外連接,外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出左外連接列出左邊關(guān)系中所有的元組右外連接列出右邊關(guān)系中所有的元組,外連接（續(xù)）,例3.53改寫例3.49SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno);,外連接（續(xù)）,執(zhí)行結(jié)果：,連接查詢（續(xù)）,1.等值與非等值連接查詢2.自身連接3.外連接4.多表連接,4.多表連接,多表連接：兩個以上的表進(jìn)行連接例3.54查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,Course/*多表連接*/WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;,3.4數(shù)據(jù)查詢,3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5基于派生表的查詢3.4.5Select語句的一般形式,嵌套查詢（續(xù)）,嵌套查詢概述一個SELECT-FROM-WHERE語句稱為一個查詢塊將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢SELECTSname/*外層查詢/父查詢*/FROMStudentWHERESnoIN(SELECTSno/*內(nèi)層查詢/子查詢*/FROMSCWHERECno=2);,嵌套查詢（續(xù)）,上層的查詢塊稱為外層查詢或父查詢下層查詢塊稱為內(nèi)層查詢或子查詢SQL語言允許多層嵌套查詢即一個子查詢中還可以嵌套其他子查詢子查詢的限制不能使用ORDERBY子句,嵌套查詢求解方法,不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。,嵌套查詢求解方法（續(xù)）,相關(guān)子查詢：子查詢的查詢條件依賴于父查詢首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表然后再取外層表的下一個元組重復(fù)這一過程，直至外層表全部檢查完為止,3.4.3嵌套查詢,1.帶有IN謂詞的子查詢2.帶有比較運算符的子查詢3.帶有ANY（SOME）或ALL謂詞的子查詢4.帶有EXISTS謂詞的子查詢,1.帶有IN謂詞的子查詢,例3.55查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。此查詢要求可以分步來完成確定“劉晨”所在系名SELECTSdeptFROMStudentWHERESname=劉晨;結(jié)果為：CS,帶有IN謂詞的子查詢（續(xù)）,查找所有在CS系學(xué)習(xí)的學(xué)生。SELECTSno,Sname,SdeptFROMStudentWHERESdept=CS;結(jié)果為：,帶有IN謂詞的子查詢（續(xù)）,將第一步查詢嵌入到第二步查詢的條件中SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=劉晨);此查詢?yōu)椴幌嚓P(guān)子查詢。,帶有IN謂詞的子查詢（續(xù)）,用自身連接完成例3.55查詢要求SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=劉晨;,帶有IN謂詞的子查詢（續(xù)）,例3.56查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名SELECTSno,Sname最后在Student關(guān)系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno然后在SC關(guān)系中找出選FROMSC修了3號課程的學(xué)生學(xué)號WHERECnoIN(SELECTCno首先在Course關(guān)系中找出FROMCourse“信息系統(tǒng)”的課程號，為3號WHERECname=信息系統(tǒng));,帶有IN謂詞的子查詢（續(xù)）,用連接查詢實現(xiàn)例3.56：SELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系統(tǒng);,3.4.3嵌套查詢,1.帶有IN謂詞的子查詢2.帶有比較運算符的子查詢3.帶有ANY（SOME）或ALL謂詞的子查詢4.帶有EXISTS謂詞的子查詢,2.帶有比較運算符的子查詢,當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，）。在例3.55中，由于一個學(xué)生只可能在一個系學(xué)習(xí)，則可以用=代替IN：SELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=劉晨);,帶有比較運算符的子查詢（續(xù)）,例3.57找出每個學(xué)生超過他選修課程平均成績的課程號。SELECTSno,CnoFROMSCxWHEREGrade=(SELECTAVG（Grade）FROMSCyWHEREy.Sno=x.Sno);,相關(guān)子查詢,帶有比較運算符的子查詢（續(xù)）,可能的執(zhí)行過程從外層查詢中取出SC的一個元組x，將元組x的Sno值（201215121）傳送給內(nèi)層查詢。SELECTAVG(Grade)FROMSCyWHEREy.Sno=201215121;,帶有比較運算符的子查詢（續(xù)）,可能的執(zhí)行過程（續(xù)）執(zhí)行內(nèi)層查詢，得到值88（近似值），用該值代替內(nèi)層查詢，得到外層查詢：SELECTSno,CnoFROMSCxWHEREGrade=88;,帶有比較運算符的子查詢（續(xù)）,可能的執(zhí)行過程（續(xù)）執(zhí)行這個查詢，得到（201215121,1）（201215121,3）然后外層查詢?nèi)〕鱿乱粋€元組重復(fù)做上述至步驟，直到外層的SC元組全部處理完畢。結(jié)果為:（201215121,1）（201215121,3）（201215122,2）,3.4.3嵌套查詢,1.帶有IN謂詞的子查詢2.帶有比較運算符的子查詢3.帶有ANY（SOME）或ALL謂詞的子查詢4.帶有EXISTS謂詞的子查詢,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,使用ANY或ALL謂詞時必須同時使用比較運算語義為：ANY大于子查詢結(jié)果中的某個值A(chǔ)LL大于子查詢結(jié)果中的所有值=ANY大于等于子查詢結(jié)果中的某個值=ALL大于等于子查詢結(jié)果中的所有值,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,使用ANY或ALL謂詞時必須同時使用比較運算語義為（續(xù)）ANY不等于子查詢結(jié)果中的某個值!=（或）ALL不等于子查詢結(jié)果中的任何一個值,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,例3.58查詢非計算機(jī)科學(xué)系中比計算機(jī)科學(xué)系任意一個學(xué)生年齡小的學(xué)生姓名和年齡SELECTSname,SageFROMStudentWHERESageCS;/*父查詢塊中的條件*/,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,結(jié)果：執(zhí)行過程：（1）首先處理子查詢，找出CS系中所有學(xué)生的年齡，構(gòu)成一個集合（20,19）（2）處理父查詢，找所有不是CS系且年齡小于20或19的學(xué)生,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,用聚集函數(shù)實現(xiàn)例3.58SELECTSname,SageFROMStudentWHERESageCS;,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,例3.59查詢非計算機(jī)科學(xué)系中比計算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞SELECTSname,SageFROMStudentWHERESageCS;,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,方法二：用聚集函數(shù)SELECTSname,SageFROMStudentWHERESageCS;,帶有ANY（SOME）或ALL謂詞的子查詢（續(xù)）,表3.7ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價轉(zhuǎn)換關(guān)系,3.4.3嵌套查詢,1.帶有IN謂詞的子查詢2.帶有比較運算符的子查詢3.帶有ANY（SOME）或ALL謂詞的子查詢4.帶有EXISTS謂詞的子查詢,帶有EXISTS謂詞的子查詢,EXISTS謂詞存在量詞帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義。,NOTEXISTS謂詞若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值,帶有EXISTS謂詞的子查詢（續(xù)）,帶有EXISTS謂詞的子查詢（續(xù)）,例3.60查詢所有選修了1號課程的學(xué)生姓名。思路分析：本查詢涉及Student和SC關(guān)系在Student中依次取每個元組的Sno值，用此值去檢查SC表若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno=1，則取此Student.Sname送入結(jié)果表SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1);,帶有EXISTS謂詞的子查詢（續(xù)）,例3.61查詢沒有選修1號課程的學(xué)生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1);,帶有EXISTS謂詞的子查詢（續(xù)）,不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換用EXISTS/NOTEXISTS實現(xiàn)全稱量詞（難點）SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞：（x）P（x（P）,帶有EXISTS謂詞的子查詢（續(xù)）,例3.55查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生?？梢杂脦XISTS謂詞的子查詢替換：SELECTSno,Sname,SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=劉晨);,帶有EXISTS謂詞的子查詢（續(xù)）,例3.62查詢選修了全部課程的學(xué)生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno);參見愛課程網(wǎng)數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)查詢節(jié)動畫EXISTS子查詢,帶有EXISTS謂詞的子查詢（續(xù)）,用EXISTS/NOTEXISTS實現(xiàn)邏輯蘊(yùn)涵（難點）SQL語言中沒有蘊(yùn)涵（Implication）邏輯運算可以利用謂詞演算將邏輯蘊(yùn)涵謂詞等價轉(zhuǎn)換為：pqpq,帶有EXISTS謂詞的子查詢（續(xù)）,例3.63查詢至少選修了學(xué)生201215122選修的全部課程的學(xué)生號碼。解題思路：用邏輯蘊(yùn)涵表達(dá)：查詢學(xué)號為x的學(xué)生，對所有的課程y，只要201215122學(xué)生選修了課程y，則x也選修了y。形式化表示：用P表示謂詞“學(xué)生201215122選修了課程y”用q表示謂詞“學(xué)生x選修了課程y”則上述查詢?yōu)?（y）pq,帶有EXISTS謂詞的子查詢（續(xù)）,等價變換：（y）pq（y（pq）（y（pq）y（pq）變換后語義：不存在這樣的課程y，學(xué)生201215122選修了y，而學(xué)生x沒有選。,帶有EXISTS謂詞的子查詢（續(xù)）,用NOTEXISTS謂詞表示：SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=201215122ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno);,3.4數(shù)據(jù)查詢,3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5基于派生表的查詢3.4.5Select語句的一般形式,3.4.4集合查詢,集合操作的種類并操作UNION交操作INTERSECT差操作EXCEPT參加集合操作的各查詢結(jié)果的列數(shù)必須相同;對應(yīng)項的數(shù)據(jù)類型也必須相同,集合查詢（續(xù)）,例3.64查詢計算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19;UNION：將多個查詢結(jié)果合并起來時，系統(tǒng)自動去掉重復(fù)元組UNIONALL：將多個查詢結(jié)果合并起來時，保留重復(fù)元組,集合查詢（續(xù)）,例3.65查詢選修了課程1或者選修了課程2的學(xué)生。SELECTSnoFROMSCWHERECno=1UNIONSELECTSnoFROMSCWHERECno=2;,集合查詢（續(xù)）,例3.66查詢計算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集。SELECT*FROMStudentWHERE