1/283數據庫系統原理ThePrincipleofDatabaseSystem

第三章關系數據庫標準語言SQL黃穗副教授主講暨南大學信息學院計算機系2/283【教學目標】

了解SQL語言產生以前的問題。了解SQL的特點。掌握標準SQL語言的格式與功能。學會使用SQL。

【教學重點】SQL的查詢、控制、更新語句使用方法。視圖的定義與應用

【教學難點】如何根據實際需要靈活選擇適當的SQL語句并完成相應功能。3/283第一節、SQL概述【教學目標】了解SQL產生與演變的歷史了解SQL的特點學習掌握SQL的基本概念4/283SQL概述：

SQL以前—各種模式采用不同的語言（外模式-高級語言/專門語言、模式-C語言、內模式-匯編語言）。系統兼容性差、難移植。

SQL的版本-86版、89版、92版、99版、XMLSQL、SparkSQL。不斷增加新語句，向基于內容查詢方面發展。

SQL實現的差異—T-SQL（MSSQLServer/Sybase)、PL/SQL(Oracle)、ISQL(InterBase)。主要體現在擴展語句和函數功能方面。只有通過原生(Original)接口才能充分支持，ODBC/JDBC接口支持不足。5/283SQL的特點三大功能的統一—指DDL、DML、DCL都由SQL完成。解決系統互操作、移植、更新問題，充分體現兩個獨立性。1.綜合統一2.高度非過程化3.面向集合的操作方式4.以同一種語法結構提供兩種使用方法5.語言簡潔，易學易用6/2835.語言簡捷，易學易用7/283第二節、SQL的數據定義【教學目標】了解數據定義的內容與格式掌握基本表的創建與修改語句理解索引概念并學會建立索引8/283

數據定義的內容9/283

定義語句的格式CREATETABLE<表名>

（<列名><數據類型>[<列級完整性約束條件>][，<列名><數據類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件10/283[例1]建立一個“學生”表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學號不能為空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))；11/283常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束PRIMARYKEY與

UNIQUE的區別？[例2]建立一個“學生選課”表SC，它由學號Sno、課程號Cno，修課成績Grade組成，其中(Sno,Cno)為主碼。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno));12/283三、刪除基本表

DROPTABLE<表名>;

基本表刪除。數據、表上的索引都刪除，表上的視圖往往仍然保留，但無法引用。刪除基本表時，系統會從數據字典中刪去有關該基本表及其索引的描述。[例5]刪除Student表

DROPTABLE

Student;13/283二、修改基本表ALTERTABLE<表名>[ADD<新列名><數據類型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數據類型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件MODIFY子句：用于修改列名和數據類型14/283例題[例2]向Student表增加“入學時間”列，其數據類型為日期型。

ALTERTABLEStudentADDScomeDATE；不論基本表中原來是否已有數據，新增加的列一律為空值。

15/283刪除屬性列

直接/間接刪除把表中要保留的列及其內容復制到一個新表中刪除原表再將新表重命名為原表名直接刪除屬性列:(新)例：ALTERTABLEStudentDropScome；16/283[例3]將年齡的數據類型改為半字長整數。

ALTERTABLEStudentMODIFYSageSMALLINT；注：修改原有的列定義有可能會破壞已有數據[例4]刪除學生姓名必須取唯一值的約束。

ALTERTABLEStudentDROPUNIQUE(Sname)；17/283

建立與刪除索引建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主（即建立表的人）根據需要建立有些DBMS自動建立以下列上的索引

PRIMARYKEYUNIQUE維護索引

DBMS自動完成

（早期dBase、FoxPro等除外）使用索引

DBMS自動選擇是否使用索引以及使用哪些索引18/283

索引概念—索引與基表的區別。建立索引的目的(排序與定位)。索引的代價。索引的維護。

建立索引—語句格式（注意索引名與索引目標對應）。UNIQUE、CLUSTER、DESC參數的意義與使用。

刪除索引—減少系統維護時間。索引實例—建立、刪除索引的例子。19/283一、建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個索引值只對應唯一的數據記錄CLUSTER表示要建立的索引是聚簇索引20/283[例6]為學生-課程數據庫中的Student，Course，SC三個表建立索引。其中Student表按學號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學號升序和課程號降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

唯一值索引對于已含重復值的屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復值。這相當于增加了一個UNIQUE約束21/283聚簇索引建立聚簇索引后，基表中數據也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項順序與表中記錄的物理順序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一個聚簇索引，而且Student表中的記錄將按照Sname值的升序存放

22/283在一個基本表上最多只能建立一個聚簇索引聚簇索引的用途：對于某些類型的查詢，可以提高查詢效率聚簇索引的適用范圍很少對基表進行增刪操作很少對其中的變長列進行修改操作二、刪除索引DROPINDEX<索引名>；刪除索引時，系統會從數據字典中刪去有關該索引的描述。[例7]刪除Student表的Stusname索引。

DROPINDEXStusname；23/283第三節、SQL的查詢語句【教學目標】了解查詢語句的內容與格式掌握單表與多表查詢掌握嵌套集函數的使用24/283語句格式SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GroupBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；25/283SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象(基本表或視圖)WHERE子句：指定查詢條件

GROUPBY子句：對查詢結果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用集函數。HAVING短語：篩選出只有滿足指定條件的組ORDERBY子句：對查詢結果表按指定列值的升序或降序排序26/283示例數據庫學生-課程數據庫學生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit)

學生選課表：SC(Sno，Cno，Grade)27/283單表查詢

查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對查詢結果排序四、使用集函數五、對查詢結果分組

28/283查詢指定列

[例1]查詢全體學生的學號與姓名。SELECTSno，SnameFROMStudent；

[例2]查詢全體學生的姓名、學號、所在系。SELECTSname，Sno，SdeptFROMStudent；[例3]查詢全體學生的詳細記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；29/283

查詢經過計算的值SELECT子句的<目標列表達式>為表達式算術表達式字符串常量函數列別名等30/283[例4]查全體學生的姓名及其出生年份。SELECTSname，2000-SageFROMStudent；

輸出結果：

Sname2000-Sage----------------------

李勇1976

劉晨1977

王名1978

張立197831/283[例5]查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname，'YearofBirth:'，2000-Sage，

ISLOWER(Sdept)FROMStudent；

32/283輸出結果：

Sname'YearofBirth:'2000-SageISLOWER(Sdept)----------------------------------------------

李勇YearofBirth:1976cs

劉晨YearofBirth:1977is

王名YearofBirth:1978ma

張立YearofBirth:1977is33/283[例5.1]使用列別名改變查詢結果的列標題SELECTSnameNAME，'YearofBirth:’

BIRTH，

2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；輸出結果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------

李勇YearofBirth:1976cs

劉晨YearofBirth:1977is

王名YearofBirth:1978ma

張立YearofBirth:1977is34/283二、選擇表中的若干元組消除取值重復的行查詢滿足條件的元組35/2831.消除取值重復的行在SELECT子句中使用DISTINCT短語假設SC表中有下列數據

SnoCnoGrade---------------------950011929500128595001388950022909500238036/283ALL與

DISTINCT

[例6]查詢選修了課程的學生學號。(1)SELECTSnoFROMSC;

或(默認ALL)SELECTALLSnoFROMSC;

結果：Sno-------950019500195001950029500237/283注意DISTINCT短語的作用范圍是所有目標列例：查詢選修課程的各種成績錯誤的寫法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正確的寫法

SELECTDISTINCTCno，GradeFROMSC;

38/2832.查詢滿足條件的元組39/283(1)比較大小在WHERE子句的<比較條件>中使用比較運算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運算符NOT+比較運算符[例8]查詢所有年齡在20歲以下的學生姓名及其年齡。

SELECTSname，Sage

FROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；

40/283(2)確定范圍使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例10]查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

41/283[例11]查詢年齡不在20~23歲之間的學生姓名、系別和年齡。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

42/283(3)確定集合使用謂詞IN<值表>,NOTIN<值表>

<值表>：用逗號分隔的一組取值[例12]查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');43/283(3)確定集合[例13]查詢既不是信息系、數學系，也不是計算機科學系的學生的姓名和性別。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');44/283(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串當匹配模板為固定字符串時，可以用=運算符取代LIKE謂詞用!=或<>運算符取代NOTLIKE謂詞45/283通配符%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)代表任意單個字符例：a_b表示以a開頭，以b結尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串46/283ESCAPE短語：當用戶要查詢的字符串本身就含有%或_時，要使用ESCAPE'<換碼字符>'短語對通配符進行轉義。47/2831)匹配模板為固定字符串[例14]查詢學號為95001的學生的詳細情況。

SELECT*FROMStudentWHERESnoLIKE'95001'；等價于：

SELECT*FROMStudentWHERESno='95001'；48/2832)匹配模板為含通配符的字符串[例15]查詢所有姓劉學生的姓名、學號和性別。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；49/283[例16]查詢姓"歐陽"且全名為三個漢字的學生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE‘歐陽__’；[例17]查詢名字中第2個字為"陽"字的學生的姓名和學號。

SELECTSname，SnoFROMStudentWHERESnameLIKE'__陽%'；50/283匹配模板為含通配符的字符串（續）[例18]查詢所有不姓劉的學生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'劉%'；51/2833)使用換碼字符將通配符轉義為普通字符

[例19]查詢DB_Design課程的課程號和學分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE'\'52/283使用換碼字符將通配符轉義為普通字符(續)[例20]查詢以"DB_"開頭，且倒數第3個字符為i的課程的詳細情況。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；53/283(5)涉及空值的查詢

使用謂詞ISNULL或ISNOTNULL

“ISNULL”

不能用“=NULL”

代替[例21]某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應的課程號。

SELECTSno，CnoFROMSCWHEREGradeISNULL；54/283[例22]查所有有成績的學生學號和課程號。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；55/283(6)多重條件查詢用邏輯運算符AND和OR來聯結多個查詢條件

AND的優先級高于OR

可以用括號改變優先級可用來實現多種其他謂詞

[NOT]IN[NOT]BETWEEN…AND…56/283[例23]查詢計算機系年齡在20歲以下的學生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；57/283改寫[例12][例12]查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改寫為：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；58/283改寫[例10][例10]查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改寫為：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；59/283三、對查詢結果排序

使用ORDERBY子句可以按一個或多個屬性列排序升序：ASC；降序：DESC；缺省值為升序當排序列含空值時ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示60/283[例24]查詢選修了3號課程的學生的學號及其成績，查詢結果按分數降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；61/283查詢結果

SnoGrade--------------950109502495007929500382950108295009759501461950025562/283[例25]查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；63/283四、使用集函數5類主要集函數計數COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計算總和SUM（[DISTINCT|ALL]<列名>） 計算平均值AVG（[DISTINCT|ALL]<列名>）64/283求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短語：在計算時要取消指定列中的重復值ALL短語：不取消重復值ALL為缺省值65/283[例26]查詢學生總人數。

SELECTCOUNT(*)FROMStudent；

[例27]查詢選修了課程的學生人數。

SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重復計算學生人數66/283[例28]計算1號課程的學生平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'；

[例29]查詢選修1號課程的學生最高分數。

SELECTMAX(Grade)FROMSCWHERCno='1'；67/283五、對查詢結果分組使用GROUPBY子句分組 細化集函數的作用對象未對查詢結果分組，集函數將作用于整個查詢結果對查詢結果分組后，集函數將分別作用于每個組68/283使用GROUPBY子句分組[例30]求各個課程號及相應的選課人數。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

結果

CnoCOUNT(Sno)

122

234 344 433 54869/283GROUPBY子句的作用對象是查詢的中間結果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現分組屬性和集函數

70/283使用HAVING短語篩選最終輸出結果[例31]查詢選修了3門以上課程的學生學號。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

71/283[例32]查詢有3門以上課程是90分以上的學生的學號及（90分以上的）課程數

SELECTSno,COUNT(*)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(*)>=3;

72/283使用HAVING短語篩選最終輸出結果只有滿足HAVING短語指定條件的組才輸出HAVING短語與WHERE子句的區別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語作用于組，從中選擇滿足條件的組。

73/283連接查詢同時涉及多個表的查詢稱為連接查詢用來連接兩個表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>

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

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

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

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

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

Student表SnoSnameSsexSageSdept95001

李勇

男20CS95002

劉晨

女19IS95003

王敏

女18MA95004

張立

男19IS84/283等值連接SC表SnoCnoGrade950011929500128595001950029500232388908085/283等值連接結果表

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

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

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

SELECTStudent.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；87/283非等值連接查詢連接運算符不是=的連接操作

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

88/283三、自身連接一個表與其自己進行連接，稱為表的自身連接需要給表起別名以示區別由于所有屬性名都是同名屬性，因此必須使用別名前綴89/283[例34]查詢每一門課的間接先修課（即先修課的先修課）

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；90/283FIRST表（Course表）

CnoCnameCpnoCcredit1數據庫542數學

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

27PASCAL語言6491/283SECOND表（Course表）

CnoCnameCpnoCcredit1數據庫542數學

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

27PASCAL語言6492/283查詢結果

173556

cnocpno93/283四、外連接（OuterJoin）外連接與普通連接的區別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出94/283

[例33]查詢每個學生及其選修課程的情況包括沒有選修課程的學生----用外連接操作SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno(*)；95/283結果：

Student.Sno SnameSsexSageSdeptCnoGrade

95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002劉晨女19IS29095002劉晨女19IS38095003王敏女18MA95004張立男19IS96/283在表名后面加外連接操作符(*)或(+)指定非主體表非主體表有一“萬能”的虛行，該行全部由空值組成虛行可以和主體表中所有不滿足連接條件的元組進行連接由于虛行各列全部是空值，因此與虛行連接的結果中，來自非主體表的屬性值全部是空值97/283

左外連接

外連接符出現在連接條件的左邊 右外連接

外連接符出現在連接條件的右邊

98/283五、復合條件連接WHERE子句中含多個連接條件時，稱為復合條件連接[例35]查詢選修2號課程且成績在90分以上的所有學生的學號、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*連接謂詞*/SC.Cno='2'AND/*其他限定條件*/SC.Grade>90；

/*其他限定條件*/99/283多表連接[例36]查詢每個學生的學號、姓名、選修的課程名及成績。

SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；

結果：

Student.SnoSnameCnameGrade 95001李勇數據庫9295001李勇數學8595001李勇信息系統8895002劉晨數學9095002劉晨信息系統80100/283嵌套查詢嵌套查詢概述嵌套查詢分類嵌套查詢求解方法引出子查詢的謂詞

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

102/283

SELECTSname 外層查詢/父查詢

FROMStudentWHERESnoIN

（SELECTSno內層查詢/子查詢

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

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

SELECTSdeptFROMStudentWHERESname='劉晨'； 結果為：

Sdept IS109/283②查找所有在IS系學習的學生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；結果為：SnoSnameSdept95001劉晨IS95004張立IS110/283

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

SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘

劉晨’)；此查詢為不相關子查詢。DBMS求解該查詢時也是分步去做的。111/283

用自身連接完成本查詢要求

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

S2.Sname='劉晨'；112/283父查詢和子查詢中的表均可以定義別名

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

劉晨’)；113/283[例38]查詢選修了課程名為“信息系統”的學生學號和姓名

SELECTSno，Sname③最后在Student關系中

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

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

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

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

WHERECname=‘信息系統’));114/283結果：

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

95002劉晨115/283用連接查詢

SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統’；116/283二、帶有比較運算符的子查詢

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

：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname='劉晨'；118/283

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

錯誤的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘

劉晨’

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

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

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

ANDSdept<>'IS';/*注意這是父查詢塊中的條件*/122/283結果

Sname Sage

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

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

19或18的學生123/283ANY和ALL謂詞有時可以用集函數實現ANY與ALL與集函數的對應關系

=

<>或!=

<<=>>=ANY

IN

--

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

NOTIN

<MIN<=MIN>MAX>=MAX124/283用集函數實現子查詢通常比直接用ANY或ALL查詢效率要高，因為前者通常能夠減少比較次數125/283[例39']：用集函數實現[例39]

SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;126/283[例40]查詢其他系中比信息系所有學生年齡都小的學生姓名及年齡。方法一：用ALL謂詞

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS’;查詢結果為空表。127/283

方法二：用集函數

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

帶有EXISTS謂詞的子查詢不返回任何數據，只產生邏輯真值“true”或邏輯假值“false”。若內層查詢結果非空，則返回真值若內層查詢結果為空，則返回假值由EXISTS引出的子查詢，其目標列表達式通常都用*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義2.NOTEXISTS謂詞130/283

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

SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相關子查詢*/WHERESno=Student.SnoAND Cno='1')；求解過程132/283用連接運算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND SC.Cno='1';133/283[例42]查詢沒有選修1號課程的學生姓名。

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

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

135/283例：[例37]查詢與“劉晨”在同一個系學習的學生?？梢杂脦XISTS謂詞的子查詢替換：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS

SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname='劉晨'；136/2835.用EXISTS/NOTEXISTS實現全稱量詞(難點)SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞：

(x)P≡

(x(P))137/283[例43]查詢選修了全部課程的學生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno）；138/283

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

pq≡p∨q139/283

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

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

則上述查詢為:(y)pq140/283等價變換：

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

y(p∧q)變換后語義：不存在這樣的課程y，學生95002選修了y，而學生x沒有選。141/283用NOTEXISTS謂詞表示：

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='95002'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；142/283集合查詢標準SQL直接支持的集合操作種類并操作(UNION)一般商用數據庫支持的集合操作種類并操作(UNION)交操作(INTERSECT)差操作(MINUS)143/2831．并操作形式

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

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；145/283方法二：

SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；146/283[例46]查詢選修了課程1或者選修了課程2的學生。方法一：

SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；147/283方法二：

SE