第三章：關系數據庫標準語言SQL本章學習內容SQL概述、數據庫的體系結構創建及管理數據庫管理數據表管理表數據數據庫單表查詢數據庫綜合查詢視圖的創建及刪除上次課主要內容插入數據修改數據刪除數據數據查詢（單表查詢）1、對列的查詢選擇一個表中指定的列選擇全部列修改查詢結果中的列標題替換查詢結果中的數據查詢經過計算的值2、對行的查詢消除重復行限制結果集的返回行數查詢滿足條件的行邏輯運算符比較運算符指定范圍確定集合字符匹配空值比較數據查詢（單表查詢）3、對查詢結果排序4、使用聚合函數5、對查詢結果分組6、使用HAVING子句7、COMPUTE子句8、使用查詢結果來創建新表本次課學習內容（多表查詢）連接查詢語法結構連接查詢分類嵌套查詢集合查詢一、連接查詢若一個查詢同時涉及兩個或兩個以上的表，則稱之為連接查詢。連接查詢有兩大類表現形式。一類是符合SQL標準連接謂詞表示形式，另一類是T-SQL擴展的使用關鍵字JOIN的表示形式。 1）等值和非等值連接 2）自身連接 3）以JOIN關鍵字指定的外連接AnIntroductiontoDatabaseSystem連接操作的執行過程嵌套循環法(NESTED-LOOP)首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結果表中一個元組。重復上述操作，直到表1中的全部元組都處理完畢

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

一般格式：

select[all|distinct]<目標列表達式>[,<目標列表達式>]…from<表名1>[,<表名2>]…[where<條件表達式>]Where子句中用來連接兩個表的條件稱為連接條件或連接謂詞。一般格式為： [<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>1、等值、非等值及復合條件連接SELECT*FROMStudent,scWHEREStudent.Sno=sc.snoAnIntroductiontoDatabaseSystem等值與非等值連接查詢（續）Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122劉晨女19CS200215122290200215122劉晨女19CS200215122380查詢結果：例1：查詢選修課程號為2的學生姓名；

SELECTsname FROMStudent,sc WHEREStudent.Sno=sc.snoando='2'例2：查詢學號為’200215122’的學生的姓名、院系、課程號及成績；

SELECTsname,sdept,cno,grade FROMStudent,SC WHEREStudent.Sno='200215122'andStudent.Sno=SC.Sno例3：查詢每個學生的學號、姓名、院系及選修課程的課程號、課程名和課程成績；

SELECTStudent.sno,o,cname,grade FROMStudent,course,SC WHEREStudent.Sno=SC.Snoando例4：查詢選修課程名為“數據庫”的學生的學號、姓名及成績，并按照成績降序排序；

SELECTStudent.sno,sname,grade FROMStudent,course,SC WHEREStudent.Sno=SC.Snoandoandame='數據庫' orderbysc.gradedesc例5：查詢選修2號課程且成績在90分以上的所有學生 SELECTStudent.Sno,Sname FROMStudent,SC WHEREStudent.Sno=SC.SnoAND SC.Cno='2'ANDSC.Grade>90例6：查詢被選修課程的課程號、課程名、學生個數及平均成績；

SELECTo,cname,count(sno),avg(grade) FROMcourse,SC WHEREo GROUPBYo,cname例7：查詢選修課學分在3分以上的的學生的學號、姓名、課程號、課程名、學分及成績；

SELECTo,cname,ccredit,grade FROMStudent,course,SC WHEREStudent.Sno=SC.Snoando andccredit>=3使用表的別名該查詢在表中出現同名字段學號sno和課程號cno，為了區分字段，加上了表名，整個查詢命令顯得十分冗長。為了簡潔起見，用戶可以采用為表指定別名的方法。例1：查詢學號為’2005001’的學生的姓名、院系、課程號及成績；

T-SQL語句可為：

SELECTa.sname,a.sdept,o,b.grade FROMStudenta,SCb WHEREa.Sno='200515001'anda.Sno=b.Sno例2：查詢每個學生的學號、姓名、院系及選修課程的課程號、課程名和課程成績；

SELECTame,SC.grade FROMStudent,course,SC WHEREStudent.Sno=SC.SnoandoT-SQL語句可為：

SELECTa.sno,ame,c.grade FROMStudenta,courseb,SCc WHEREa.Sno=c.Snoando2、自身連接自身連接：一個表與其自己進行連接；需要給表起別名以示區別；由于所有屬性名都是同名屬性，因此必須使用別名前綴。例1：查詢和“李勇”一個院系的其他學生的基本情況

SELECT* FROMStudenta,studentb WHEREa.Sname='李勇'anda.Sdept=b.Sdept

例2：查詢在同一個系的學生的基本情況 SELECT* FROMStudenta

,studentb WHEREa.sno<>b.sno

anda.Sdept=b.Sdept

例3：查詢年齡相同的學生的基本情況

例4：查詢選修課程一樣且成績相同的學生的基本情況

SELECTdistincto,b.gradeFROMstudenta,scb,sccWHEREoandb.grade=c.gradeandb.Sno<>c.Snoandb.sno=a.snoSELECT*FROMStudenta,studentbWHEREa.Sno<>b.Snoanda.Sage=b.Sage3、外連接在通常的連接操作中，只有滿足連接條件的行才能作為結果輸出，但有些情況下，也需要輸出其他相關選項，這就用到了外連接。T-SQL擴展了以JOIN關鍵字指定連接的表達式，使表的連接運算能力有了增強（關鍵字Outer可省略）。外連接可分為左連接（LEFT）、右連接（RIGHT）和全連接（FULL）三種：例1：查詢所有學生基本情況及他們選修課程的成績（包括沒選修任何課的學生）；

Selectstudent.*,grade

Fromstudentleftouterjoinsc

Onstudent.sno

=sc.sno例2：查詢所有選修課程的學生的學號、姓名、課程號及成績；

Selectstudent.sno,sname,cno,grade

Fromstudentrightouterjoinsc

Onstudent.sno

=sc.sno例3：查詢所有課程的課程號、課程名及對應成績（包括未選修的課程）

Selecto,cname,grade Fromcoursefulljoinsc Ono=o二、嵌套查詢T-SQL允許SELECT多層嵌套使用，即一個子查詢中還可以嵌套子子查詢，用來表示復雜的查詢，從而增強SQL的查詢能力。以這種層層嵌套的方式來構造查詢語句正是SQL中“結構化”的含義所在。子查詢的限制不能使用ORDERBY子句在where子句或having子句所表示的條件中，可以使用另一個查詢的結果（即一個查詢塊）作為條件的一部分，這種將一個查詢塊嵌套在另一個查詢塊的where子句或having子句的條件中的查詢稱為嵌套查詢。子查詢通常與IN

、比較運算符及EXISTS謂詞結合使用。AnIntroductiontoDatabaseSystem嵌套查詢求解方法不相關子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。AnIntroductiontoDatabaseSystem嵌套查詢求解方法（續）相關子查詢：子查詢的查詢條件依賴于父查詢首先取外層查詢中表的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，若WHERE子句返回值為真，則取此元組放入結果表然后再取外層表的下一個元組重復這一過程，直至外層表全部檢查完為止1、帶有In謂詞的子查詢在嵌套查詢中，子查詢的結果往往是一個集合，所以IN是嵌套查詢中最常用的謂詞。IN子查詢用于進行一個給定值是否在子查詢結果集中的判斷。語法格式為：expression[not]in（subquery）例1：查詢選修課程號為2的學生姓名；SELECTsnameFROMStudent，scWHEREStudent.Sno=SC.Snoando=‘2’SELECTSnameFROMStudentWHERESnoIN (SELECTSnoFROMSC WHERECno='2')步驟：先找出2號課程對應的學號，再通過學號找學生姓名。例2：查詢和“李勇”一個院系的學生的基本情況

SELECTa.*FROMStudenta，studentbWHEREa.Sname=‘李勇’anda.Ssept=b.SseptSELECT*FROMStudentWHERESdeptIN (SELECTSdeptFROMStudent WHEREsname='李勇')步驟：先找出李勇對應的院系，再通過院系找其余學生。例2：查詢和“李勇”一個院系的學生的基本情況

將第一步查詢嵌入到第二步查詢的條件中SELECT*FROMStudentWHERESdeptIN (SELECTSdeptFROMStudent WHEREsname='李勇')

此查詢要求可以分步來完成

①確定“劉晨”所在系名

SELECTSdeptFROMStudentWHERESname='李勇'； 結果為：CS②查找所有在IS系學習的學生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='CS'；此查詢為不相關子查詢。例3：查詢沒有選修課程的學生的基本情況；

SELECT*FROMStudentWHERESnonotin (SELECTsnoFROMsc)例4：查詢至少有一個成績在80分以上的學生的基本情況；

SELECT*FROMStudentWHERESnoin (SELECTsnoFROMsc WHEREgrade>=80)例5：查詢沒有選修“數據庫”課程的學生的基本情況

；SELECT*FROMStudentWHERESnonotin(SELECTsnoFROMsc WHEREcnoin (SELECTcnofromcourse WHEREcname='數據庫'))首先查詢出數據庫的課程號其次查詢出選了數據庫的學號AnIntroductiontoDatabaseSystem練習1、查詢選修了課程名為“信息系統”的學生學號和姓名

SELECTSno，Sname③最后在Student關系中

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

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

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

FROMCourse“信息系統”的課程號，為3號

WHERECname=‘信息系統’

));AnIntroductiontoDatabaseSystem練習用連接查詢實現SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統’；2、帶有比較運算符的子查詢格式為：

expression{<<==>>=!=}{allany}（subquery）說明：

Expression為要進行比較的表達式

Subquery是子查詢

allany是對比較運算的限制ANY：任意一個值ALL：所有值需要配合使用比較運算符>ANY 大于子查詢結果中的某個值

>ALL 大于子查詢結果中的所有值<ANY 小于子查詢結果中的某個值

<ALL 小于子查詢結果中的所有值>=ANY 大于等于子查詢結果中的某個值

>=ALL 大于等于子查詢結果中的所有值<=ANY 小于等于子查詢結果中的某個值

<=ALL 小于等于子查詢結果中的所有值=ANY 等于子查詢結果中的某個值

=ALL 等于子查詢結果中的所有值（通常沒實際意義）!=（或<>）ANY不等于子查詢結果中的某個值!=（或<>）ALL 不等于子查詢結果中的任何一個值AnIntroductiontoDatabaseSystem帶有比較運算符的子查詢（續）例：假設一個學生只可能在一個系學習，并且必須屬于一個系，則可以用=代替IN

：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；AnIntroductiontoDatabaseSystem帶有比較運算符的子查詢（續）

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

錯誤的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘劉晨’)=Sdept例1：查詢和“李勇”不在一個院系的學生的基本情況；SELECT*FROMStudentWHERESage> (SELECTavg(sage)FROMstudent)例2：查找查詢年齡高于平均年齡的學生的基本信息；SELECT*FROM StudentWHERESdept<> (SELECTSdeptFROMStudent WHEREsname='李勇')例3：查詢其他系中比CS系某一學生年齡小的學生姓名和年齡；

SELECTSname,SageFROMStudentWHERESage<ANY(SELECTSage FROMStudent WHERESdept='CS')ANDSdept<>'CS'SELECTSname,SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='CS')ANDSdept<>'CS'AnIntroductiontoDatabaseSystem帶有ANY（SOME）或ALL謂詞的子查詢（續）結果：

執行過程：

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

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

20或19的學生SnameSage王敏18張立19AnIntroductiontoDatabaseSystem帶有比較運算符的子查詢（續）找出每個學生超過他選修課程平均成績的課程號。

SELECTSno，CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade) FROMSCyWHEREy.Sno=x.Sno);相關子查詢1）非相關子查詢是獨立于外部查詢的子查詢，子查詢總共執行一次，執行完畢后將值傳遞給外部查詢。2）相關子查詢的執行依賴于外部查詢的數據，外部查詢執行一行，子查詢就執行一次。AnIntroductiontoDatabaseSystem帶有比較運算符的子查詢（續）可能的執行過程：

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

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

SELECTSno，CnoFROMSCxWHEREGrade>=88；AnIntroductiontoDatabaseSystem帶有比較運算符的子查詢（續）3.執行這個查詢，得到（200215121，1）（200215121，3）4.外層查詢取出下一個元組重復做上述1至3步驟，直到外層的SC元組全部處理完畢。結果為:

（200215121，1）（200215121，3）（200215122，2）3、帶有EXISTS謂詞的子查詢帶有EXISTS謂詞的子查詢不返回任何數據，只產生邏輯真值“true”或邏輯假值“false”。若內層查詢結果非空，則外層的WHERE子句返回真值若內層查詢結果為空，則外層的WHERE子句返回假值由EXISTS引出的子查詢，其目標列表達式通常都用*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義。例1：查詢參加選修的學生信息

SELECT*FROMstudent WHEREEXISTS (SELECT*FROMsc WHEREstudent.sno=sc.sno)例2：查詢沒有參加選修的學生信息

SELECT*FROMstudent WHERENOTEXISTS (SELECT*FROMsc WHEREstudent.sno=sc.sno)相關子查詢一種通俗的可以理解為：將外查詢表的每一行，代入內查詢作為檢驗，如果內查詢返回的結果取非空值，則EXISTS子句返回TRUE，這一行可作為外查詢的結果行，否則不能作為結果。分析器先找到關鍵字SELECT，然后跳到FROM關鍵字將STUDENT表導入內存，并通過指針找到第一條記錄，接著找到WHERE關鍵字計算它的條件表達式，如果為真那么把這條記錄裝到一個虛表當中，指針再指向下一條記錄。如果為假那么指針直接指向下一條記錄，而不進行其它操作。一直檢索完整個表，并把檢索出來的虛擬表返回給用戶。EXISTS是條件表達式的一部分，它也有一個返回值(true或false)。例3：查詢沒有選修1號課程的學生姓名；SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')代入子查詢語句檢驗思路分析：本查詢涉及Student和SC關系在Student中依次取每個元組的Sno值，用此值去檢查SC關系若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno='1'，則取此Student.Sname送入結果關系例3：查詢沒有選修1號課程的學生姓名；SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')SELECTSnameFROMStudentWHERESnoNOTIN (SELECTSnoFROMSC WHERECno='1')EXISTS與IN的使用效率的問題，通常情況下采用exists要比in效率高，因為IN不走索引，但要看實際情況具體使用：

IN適合于外表大而內表小的情況；EXISTS適合于外表小而內表大的情況。代入子查詢語句檢驗例4：查詢選修了全部課程的學生的學號和姓名；可理解為：不存在任意一門課程是該學生沒有選修的，顯示該學生姓名；可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞：即：(x)P≡(x(P))Selectsno,snameFromstudentWherenotexists (select*fromcourse wherenotexists (select*fromsc wheresno=student.snoando))返回該學生是否有沒選的課程練習1、查詢與“劉晨”在同一個系學習的學生。可以用帶EXISTS謂詞的子查詢替換：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS

(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=‘劉晨’)；練習SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='200215122'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))查詢至少選修了學生200215122選修的全部課程的學生號碼。SC表學號Sno

課程號

Cno

成績

Grade200215121200215121200215121200215122200215122123239285889080SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='200215122'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))外層查詢SELECTDISTINCTSnoFROMSCSCX外層查詢：SELECTDISTINCTSnoFROMSCSCX學號Sno

課程號

Cno

成績

Grade200215121200215121200215121200215122200215122123239285889080Sno200215121200215122結果集1？外層查詢：SELECTDISTINCTSnoFROMSCSCX如果要放入結果集需要where子句返回真WHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='200215122'AND即Exists返回假中層查詢：SELECT*FROMSCSCYWHERESCY.Sno='200215122'AND將第一條學號代入中層查詢判斷Sno200215121200215122Sno

CnoGrade200215122290200215122380？結果集2即子查詢結果集為空SELECT*FROMSCSCYWHERESCY.Sno='200215122'AND NOTEXISTS (SELECT* FROMSCSCZ WHERESCZ.Sno=SCX.SnoAND

SCZ.Cno=SCY.Cno))內層查詢學號Sno

課程號

Cno

成績

Grade200215121200215121200215121200215122200215122123239285889080Sno200215121200215122Sno

CnoGrade200215122290200215122380結果集3有記錄返回真結果集2？？SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='200215122'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))結果集2為空返回假Sno200215121200215122結果集1如果改為SCY.Sno=‘200215121’呢？三、集合查詢集合操作的種類并操作UNION---or交操作INTERSECT---and差操作EXCEPT參加集合操作的各查詢結果的列數必須相同；對應項的數據類型也必須相同

例1：查詢CS系的學生及年齡不大于19歲的學生SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19UNION：將多個查詢結果合并起來時，系統