數(shù)據(jù)庫系統(tǒng)概論王珊五版十PPT課件

1、.,數(shù)據(jù)庫原理第三章：關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,授課教師：姜姍,.,1.自身連接,自身連接：連接操作不僅可以在兩個表之間進(jìn)行，也可以是一個表與其自己進(jìn)行連接，稱為表的自身連接。由于所有屬性名都是同名屬性，因此必須使用別名前綴。需要給表起別名以示區(qū)別。,.,例1：查詢每一門課的間接先修課（即先修課的先修課）first表（course表）second表（course表）,.,完成上述查詢的sql語句為：SELECTo,second.cpnoFROMcoursefirst,coursesecondWHEREfirst.cpno=o,.,例2：查詢和“李勇”一個院系的其他學(xué)生的基本情況a表（stude

2、nt表）b表（student表）,.,例2：查詢和“李勇”一個院系的學(xué)生的基本情況SELECTb.*FROMStudenta,studentbWHEREa.Sname=李勇anda.Sdept=b.Sdeptandb.Sname李勇,.,使用JOIN關(guān)鍵字實現(xiàn)表的連接,在SELECT語句的FROM子句中，通過指定不同類型的JOIN關(guān)鍵字可以實現(xiàn)不同的表的連接方式，而在ON關(guān)鍵字后指定連接條件。,.,基本連接語法如下：SELECTcolumn_listFROMjoin_tableJOIN_TYPEjoin_tableON(join_condition)說明如下。join_table：指出參與連接

3、操作的表名。JOIN_TYPE為連接類型，可分為3種：內(nèi)部連接、外部連接和交叉連接。,.,1.內(nèi)部連接,內(nèi)部連接INNERJOIN內(nèi)部連接是使用比較運算符比較要連接列中的值的連接，下面通過實例說明其使用。例：從student和sc表中查詢每個學(xué)生的各門選修課成績信息，要求返回的結(jié)果中包含學(xué)生的學(xué)號、姓名、性別、選修課程序號及成績。Selecta.sno,sname,ssex,cno,gradeFromstudentainnerjoinscbOna.sno=b.sno,.,練習(xí)：查詢所有考試成績及格的學(xué)生的成績信息，結(jié)果中包含學(xué)生的學(xué)號、姓名、性別、選修課程編號、成績，并按照成績進(jìn)行降序排列。,

4、Selecta.sno,sname,ssex,o,gradeFromstudentainnerjoinscbOna.sno=b.snoandGrade=60orderbyb.Grade,.,2.外連接,在通常的連接操作中，只有滿足連接條件的行才能作為結(jié)果輸出，但有些情況下，也需要輸出其他相關(guān)選項，這就用到了外連接。例3：查詢每個學(xué)生及其選修課程的情況。,.,例3：查詢每個學(xué)生及其選修課程的情況。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno參見P82，查詢結(jié)果中沒有20

5、0215123和200215125這兩個學(xué)生的信息。,.,SQL擴展了以JOIN關(guān)鍵字指定連接的表達(dá)式，使表的連接運算能力有了增強（關(guān)鍵字Outer可省略）。外連接可分為左連接（LEFT）、右連接（RIGHT）和全連接（FULL）三種：,.,R,S,外連接,左外連接,右外連接,.,左外連接格式：SELECT,FROMLEFTOUTERJOINON右外連接格式：SELECT,FROMRIGHTOUTERJOINON外連接格式：SELECT,FROMFULLOUTERJOINON,.,例1：查詢所有學(xué)生基本情況及他們選修課程的成績（包括沒選修任何課的學(xué)生）。Selectstudent.*,grad

6、eFromstudentleftouterjoinscOn（student.sno=sc.sno）,Selectstudent.*,gradeFromstudentleftjoinscOn（student.sno=sc.sno）,.,例2：查詢所有選修課程的學(xué)生的學(xué)號、姓名、課程號及成績。Selectstudent.sno,sname,cno,gradeFromstudentrightouterjoinscOn（student.sno=sc.sno）,Selectstudent.sno,sname,cno,gradeFromstudentrightjoinscOn（student.sno=sc

7、.sno）,.,4.多表連接,多表連接：兩個以上的表進(jìn)行連接例3.54查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,Course/*多表連接*/WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;,.,3.4.3嵌套查詢,SQL允許SELECT多層嵌套使用，即一個子查詢中還可以嵌套子查詢，用來表示復(fù)雜的查詢，從而增強SQL的查詢能力。以這種層層嵌套的方式來構(gòu)造查詢語句正是SQL中“結(jié)構(gòu)化”的含義所在。,.,在where子句或having子句所表示的條件中，可以使

8、用另一個查詢的結(jié)果（即一個查詢塊：一個SELECT-FROM-WHERE語句稱為一個查詢塊）作為條件的一部分，這種將一個查詢塊嵌套在另一個查詢塊的where子句或having子句的條件中的查詢稱為嵌套查詢。例如：SELECTsnameFROMStudent上層模塊：父查詢，外層查詢WHERESnoin（SELECTsnoFROMsc下層模塊：子查詢，內(nèi)層查詢WHEREcno=2）;,.,Sql語言允許有多層嵌套查詢。即一個子查詢中還允許嵌套其他子查詢。需要特別指出的是子查詢的select語句中不能使用orderby子句，orderby子句只能對最終查詢結(jié)果排序。子查詢通常與IN、比較運算符及E

9、XISTS謂詞結(jié)合使用。,.,1、帶有In謂詞的子查詢,在嵌套查詢中，子查詢的結(jié)果往往是一個集合，所以謂詞IN是嵌套查詢中最常用的謂詞。IN子查詢用于進(jìn)行一個給定值是否在子查詢結(jié)果集中的判斷。,.,例1：查詢與“劉晨”在一個系學(xué)習(xí)的學(xué)生。,SELECTb.*FROMStudenta,studentbWHEREa.Sname=劉晨anda.Sdept=b.Sdeptandb.Sname劉晨,SELECT*FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHEREsname=劉晨)andSname劉晨,步驟：先找出劉晨對應(yīng)的院系，再通過院系找其余學(xué)生。,

10、.,例2：查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名。SELECTsno,snameFROMStudentWHEREsnoIN(SELECTsnoFROMscWHEREcnoin（SELECTcnoFROMcourseWHEREcname=信息系統(tǒng)）),.,上述查詢可以用連接查詢實現(xiàn)：SELECTstudent.sno,snameFROMStudent,sc,courseWHEREStudent.sno=o=ame=信息系統(tǒng),.,練習(xí)：1、查詢選修課程號為2的學(xué)生姓名；2、查詢沒有選修課程的學(xué)生的基本情況；3、查詢至少有一個成績在80分以上的學(xué)生的基本情況；4、查詢沒有選修“數(shù)據(jù)庫”課程的

11、學(xué)生的基本情況。,.,1、查詢選修課程號為2的學(xué)生姓名；,SELECTsnameFROMStudent,scWHEREStudent.Sno=SC.So=2,SELECTSnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=2),步驟：先找出2號課程對應(yīng)的學(xué)號，再通過學(xué)號找學(xué)生姓名。,.,2、查詢沒有選修課程的學(xué)生的基本情況；,SELECT*FROMStudentWHERESnonotin(SELECTsnoFROMsc),3、查詢至少有一個成績在80分以上的學(xué)生的基本情況；,SELECT*FROMStudentWHERESnoin(SELECT

12、snoFROMscWHEREgrade=80),.,4、查詢沒有選修“數(shù)據(jù)庫”課程的學(xué)生的基本情況。,SELECT*FROMStudentWHERESnonotin(SELECTsnoFROMscWHEREcnoin(SELECTcnofromcourseWHEREcname=數(shù)據(jù)庫),.,2、帶有比較運算符的子查詢,帶有比較運算符的子查詢是指父查詢與子查詢之間用比較運算符連接。當(dāng)用戶確切知道內(nèi)層查詢返回的是單值時，可以用，=，!=或等比較運算符。例如39：查詢與“劉晨”在一個系學(xué)習(xí)的學(xué)生。由于一個學(xué)生只可能在一個系學(xué)習(xí)，也就是說內(nèi)查詢的結(jié)果是一個值，因此可以用=代替inSELECT*FROM

13、StudentWHERESdeptIN（或者用=）(SELECTSdeptFROMStudentWHEREsname=劉晨)andSname劉晨,.,例1：找出一個學(xué)生超過他選修課程平均成績的課程號。x表（sc表）y表（sc表）,.,例1：找出一個學(xué)生超過他選修課程平均成績的課程號。SELECTsno,cnoFROMscxWHEREgrade=(selectavg(grade)fromscywherey.sno=x.sno),.,練習(xí)1、查詢和“李勇”不在一個院系的學(xué)生的基本情況；2、查詢年齡高于平均年齡的學(xué)生的基本信息。,.,1、查詢和“李勇”不在一個院系的學(xué)生的基本情況；,SELECT*F

14、ROMStudentWHERESage(SELECTavg(sage)FROMstudent),2、查詢年齡高于平均年齡的學(xué)生的基本信息；,SELECT*FROMStudentWHERESdept(SELECTSdeptFROMStudentWHEREsname=李勇),.,3、帶有ANY或ALL謂詞的子查詢,子查詢返回單值時可以用比較運算符，但返回多值時要用ANY或ALL謂詞修飾。而使用ANY或ALL謂詞時必須同時使用比較運算符。,.,需要配合使用比較運算符ANY大于子查詢結(jié)果中的某個值A(chǔ)LL大于子查詢結(jié)果中的所有值=ANY大于等于子查詢結(jié)果中的某個值=ALL大于等于子查詢結(jié)果中的所有值）A

15、NY不等于子查詢結(jié)果中的某個值!=（或）ALL不等于子查詢結(jié)果中的任何一個值,.,例：查詢其他系中比計算機科學(xué)系（CS）某一學(xué)生年齡小的學(xué)生姓名和年齡；,.,例1：查詢其他系中比計算機科學(xué)系（CS）某一學(xué)生年齡小的學(xué)生姓名和年齡；SELECTSname,SageFROMStudentWHERESageCS,SELECTSname,SageFROMStudentWHERESageCS,.,例2：查詢其他系中比計算機科學(xué)系（CS）所有學(xué)生年齡都小的學(xué)生姓名和年齡；SELECTSname,SageFROMStudentWHERESageCS,SELECTSname,SageFROMStudentWH

16、ERESageCS,.,4、帶有EXISTS謂詞的子查詢,EXISTS代表存在量詞。帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義。,.,例1：查詢參加選修的學(xué)生信息。SELECT*FROMstudentWHEREEXISTS(SELECT*FROMscWHEREsc.sno=student.sno),SELECT*FROMStud

17、entWHERESnoIN(SELECTSnoFROMSC),.,例2：查詢未參加選修的學(xué)生信息。SELECT*FROMstudentWHERENOTEXISTS(SELECT*FROMscWHEREsc.sno=student.sno),SELECT*FROMStudentWHERESnoNOTIN(SELECTSnoFROMSC),.,例3：查詢沒有選修1號課程的學(xué)生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1),SELECTSnameFROMStudentWHERESno

18、NOTIN(SELECTSnoFROMSCWHERECno=1),.,三、集合查詢,SELECT語句的查詢結(jié)果是元組的集合，所以多個SELECT語句的結(jié)果可進(jìn)行集合操作。集合操作主要包括：并操作UNION交操作INTERSECT差操作EXCEPT注意：參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對應(yīng)項的數(shù)據(jù)類型也必須相同。,.,例1：查詢計算機科學(xué)系（CS）的學(xué)生及年齡不大于19歲的學(xué)生。SELECTDISTINCT*FROMStudentWHERESdept=CSORSage=19,SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19,UNION：將多個查詢結(jié)果合并起來時，系統(tǒng)自動去掉重復(fù)元組。UNIONALL：將多個查詢結(jié)果合并起來時，保留重復(fù)元組，包含空值。,.,例2