交通信息系統(tǒng)第八章結(jié)構(gòu)化查詢語言SQL2

1、1 主要內(nèi)容主要內(nèi)容vSQLSQL概述概述 vSQLSQL的數(shù)據(jù)定義的數(shù)據(jù)定義vSQLSQL的數(shù)據(jù)查詢的數(shù)據(jù)查詢 v數(shù)據(jù)更新數(shù)據(jù)更新 v嵌入式嵌入式SQL SQL vTransact SQLTransact SQL語言概述語言概述 24.3 SQL的數(shù)據(jù)查詢的數(shù)據(jù)查詢 主要內(nèi)容主要內(nèi)容 SELECTSELECT語句格式語句格式 單表查詢單表查詢 聚集函數(shù)聚集函數(shù) 數(shù)據(jù)分組數(shù)據(jù)分組 多表查詢多表查詢 集合操作集合操作3學(xué)號學(xué)號SNO姓名姓名SNAME性別性別SEX年齡年齡AGE所在系所在系SDEPT200915121200915122200915123200915125李小勇李小勇劉劉 晨晨王洪

2、敏王洪敏張張 力力MFFM20191819CSCSMAIS學(xué)生表學(xué)生表：S(S(SNOSNO,SNAME,SEX,AGE,SDEPT) ,SNAME,SEX,AGE,SDEPT) 課程表：課程表：C(C(CNOCNO,CNAME,CPNO,CCREDIT) ,CNAME,CPNO,CCREDIT) 學(xué)生選課表學(xué)生選課表：SC(SC(SNO,CNOSNO,CNO,GRADE) ,GRADE) 例例4.14.1 定義一個定義一個教務(wù)管理數(shù)據(jù)庫教務(wù)管理數(shù)據(jù)庫JWGLJWGL，它包含，它包含3 3個表：個表：4課程號課程號CNO課程名課程名CNAME先修課先修課CPNO學(xué)分學(xué)分CCREDIT12345

3、67數(shù)據(jù)庫數(shù)據(jù)庫數(shù)學(xué)數(shù)學(xué)信息系統(tǒng)信息系統(tǒng)操作系統(tǒng)操作系統(tǒng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理數(shù)據(jù)處理C語言語言516764243424課程表實例課程表實例學(xué)號學(xué)號SNO課程號課程號CNO成績成績GRADE2009151212009151212009151212009151222009151221232392858890805 4.3.1 SELECT 4.3.1 SELECT語句語句格式格式1 1、SELECTSELECTFROMFROMWHEREWHERE句型句型在關(guān)系代數(shù)中最常用的式子是下列表達(dá)式：在關(guān)系代數(shù)中最常用的式子是下列表達(dá)式： A1,An(F(R1Rm) 這里這里R1、Rm為關(guān)系，為關(guān)系，F(xiàn)

4、是公式，是公式，A1、An為屬性。為屬性。針對上述表達(dá)式，針對上述表達(dá)式，SQL為此設(shè)計了為此設(shè)計了SELECTFROMWHERE句型：句型：SELECT A1，AnFROM R1，RmWHERE F 這個句型是從關(guān)系代數(shù)表達(dá)式演變來的，但這個句型是從關(guān)系代數(shù)表達(dá)式演變來的，但WHERE子句中的條件表達(dá)子句中的條件表達(dá)式式F要比關(guān)系代數(shù)中公式更靈活要比關(guān)系代數(shù)中公式更靈活。6v在在WHERE子句的條件表達(dá)式子句的條件表達(dá)式F中可使用下列運算符中可使用下列運算符n算術(shù)比較運算符：算術(shù)比較運算符：、 、 = 、 、 !=n邏輯運算符：邏輯運算符：AND、OR、NOTn集合成員資格運算符：集合成員資

5、格運算符：IN、NOT INn謂詞：謂詞：EXISTS、ALL、SOME、UNIQUEn聚合函數(shù)：聚合函數(shù)：AVG、MIN、MAX、SUM、COUNTnF中運算對象還可以是另一個中運算對象還可以是另一個SELECT語句，即語句，即SELECT句型可以嵌套句型可以嵌套2 2、SELECTSELECT語句完整的句法如下：語句完整的句法如下： SELECTSELECT 目標(biāo)表的列名或列表達(dá)式序列目標(biāo)表的列名或列表達(dá)式序列 FROMFROM 基本表名和（或）視圖序列基本表名和（或）視圖序列 WHEREWHERE 行條件表達(dá)式行條件表達(dá)式 GROUP BYGROUP BY 列名序列列名序列 HAVING

6、 HAVING 組條件表達(dá)式組條件表達(dá)式 ORDER BYORDER BY 列名列名 ASC|DESC ASC|DESC ， 主語句主語句SELECT-FROM-WHERE的含義是：的含義是： 根據(jù)根據(jù)WHERE子句的條件表達(dá)式，從子句的條件表達(dá)式，從FROM子句指定的基本表子句指定的基本表或視圖中找出滿足條件的元組，再按或視圖中找出滿足條件的元組，再按SELECT子句中的目標(biāo)列表達(dá)子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。式，選出元組中的屬性值形成結(jié)果表。 8整個語句的執(zhí)行過程如下：整個語句的執(zhí)行過程如下：(1) (1) 讀取讀取FROMFROM子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾

7、積操作。子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。(2) (2) 選取滿足選取滿足WHEREWHERE子句中給出的條件表達(dá)式的元組。子句中給出的條件表達(dá)式的元組。(3) (3) 按按GROUPGROUP子句中指定列的值分組，同時提取滿足子句中指定列的值分組，同時提取滿足HAVINGHAVING子句中子句中組條件表達(dá)式的那些組。組條件表達(dá)式的那些組。(4) (4) 按按SELECTSELECT子句中給出的列名或列表達(dá)式求值輸出。子句中給出的列名或列表達(dá)式求值輸出。(5) ORDER(5) ORDER子句對輸出的目標(biāo)表進(jìn)行排序，按附加說明子句對輸出的目標(biāo)表進(jìn)行排序，按附加說明ASCASC升序排列

8、升序排列，或按，或按DESCDESC降序排列。降序排列。9根據(jù)查詢條件不同，可分為：根據(jù)查詢條件不同，可分為：v單表查詢單表查詢v連接查詢連接查詢v嵌套查詢嵌套查詢v集合查詢集合查詢104.3.2 單表查詢單表查詢查詢僅涉及一個表，是一種最簡單的查詢操作。查詢僅涉及一個表，是一種最簡單的查詢操作。一、選擇表中的若干列一、選擇表中的若干列二、選擇表中的若干元組二、選擇表中的若干元組三、對查詢結(jié)果排序三、對查詢結(jié)果排序四、使用聚合函數(shù)四、使用聚合函數(shù)五、對查詢結(jié)果分組五、對查詢結(jié)果分組 111、查詢表中的若干列、查詢表中的若干列 查詢指定列查詢指定列 在很多情況下，用戶只對表中的一部分屬性列感興趣

9、在很多情況下，用戶只對表中的一部分屬性列感興趣，這時可以通過在，這時可以通過在SELECT子句的子句的中指中指定要查詢的屬性列。定要查詢的屬性列。 例例4.21 查詢?nèi)w學(xué)生的學(xué)號與姓名。查詢?nèi)w學(xué)生的學(xué)號與姓名。 SELECT SNO,SNAME FROM S; 執(zhí)行過程：從執(zhí)行過程：從S表中取出一個元組，再取出該元組在屬表中取出一個元組，再取出該元組在屬性性SNO和和SNAME上的值，形成一個新的元組作為輸出。上的值，形成一個新的元組作為輸出。對對S表中的所有元組做相同的處理，最后形成一個結(jié)果關(guān)表中的所有元組做相同的處理，最后形成一個結(jié)果關(guān)系作為輸出。系作為輸出。12 查詢?nèi)苛胁樵內(nèi)苛?/p>

10、 將表中的所有屬性列都選出來，可以有兩種方法。一將表中的所有屬性列都選出來，可以有兩種方法。一種方法就是在種方法就是在SELECTSELECT關(guān)鍵字后面列出所有列名；另一種方關(guān)鍵字后面列出所有列名；另一種方法是如果列的顯示順序與其在基表中的順序相同，也可以法是如果列的顯示順序與其在基表中的順序相同，也可以簡單地將簡單地將 指定為指定為“* *”。 例例4.23 4.23 查詢?nèi)w學(xué)生的詳細(xì)記錄。查詢?nèi)w學(xué)生的詳細(xì)記錄。 SELECT SELECT * * FROM S; FROM S; 等價于：等價于： SELECT SNO,SNAME,SEX,AGE,SDEPT SELECT SNO,SNA

11、ME,SEX,AGE,SDEPT FROM S; FROM S; 13 (3) 查詢經(jīng)過計算的值查詢經(jīng)過計算的值 SELECTSELECT子句的子句的 不僅可以是表中的屬性列，也可不僅可以是表中的屬性列，也可以是表達(dá)式。以是表達(dá)式。 例例4.24 4.24 查詢?nèi)w學(xué)生的姓名及其出生年份。查詢?nèi)w學(xué)生的姓名及其出生年份。 SELECT SNAME,2009-AGESELECT SNAME,2009-AGE FROM S; FROM S; 輸出結(jié)果：輸出結(jié)果： Sname Sname _ _ 李小勇李小勇 19891989 劉晨劉晨 19901990 王名王名 19911991 張立張立 199

12、0 1990 142、選擇表中的若干元組、選擇表中的若干元組 消除取值重復(fù)的行消除取值重復(fù)的行 兩個本來并不完全相同的元組，投影到指定的某些列上后，可能變成兩個本來并不完全相同的元組，投影到指定的某些列上后，可能變成相同的行了，可以用相同的行了，可以用DISTINCT取消它們。取消它們。 例例4.26 查詢選修了課程的學(xué)生學(xué)號。查詢選修了課程的學(xué)生學(xué)號。 SELECT SNO FROM SC; 該查詢結(jié)果里包含了許多重復(fù)的行。如果想去掉結(jié)果表中的重復(fù)行，該查詢結(jié)果里包含了許多重復(fù)的行。如果想去掉結(jié)果表中的重復(fù)行，必須指定必須指定DISTINCT關(guān)鍵詞：關(guān)鍵詞： SELECT DISTINCT

13、SNO FROM SC; 15(1) SELECT Sno(1) SELECT Sno FROM SC; FROM SC;或或( (默認(rèn)默認(rèn) ALL)ALL) SELECT ALL Sno SELECT ALL Sno FROM SC; FROM SC; 結(jié)果：結(jié)果： Sno Sno - - 95001 95001 95001 95001 95001 95001 95002 95002 95002 95002 (2) SELECT DISTINCT Sno(2) SELECT DISTINCT Sno FROM SC; FROM SC; 結(jié)果：結(jié)果： Sno Sno - - 95001 950

14、01 95002 95002 16 查詢滿足條件的元組查詢滿足條件的元組查詢滿足指定條件的元組可以通過查詢滿足指定條件的元組可以通過WHERE子句實現(xiàn)。子句實現(xiàn)。查查 詢詢 條條 件件謂謂 詞詞比較比較確定范圍確定范圍確定集合確定集合字符匹配字符匹配空空 值值多重條件（邏輯運算）多重條件（邏輯運算）= =， ， =，=，!=!=，!，!=20 AND AGE =23；19確定集合確定集合 謂詞謂詞IN可以用來查找屬性值屬于指定集合的元組。可以用來查找屬性值屬于指定集合的元組。 例例4.32 查詢計算機科學(xué)系（查詢計算機科學(xué)系（CS）、數(shù)學(xué)系（）、數(shù)學(xué)系（MA）和信息系（）和信息系（IS）學(xué)）學(xué)

15、生的姓名和性別。生的姓名和性別。 SELECT SNAME,SEX FROM S WHERE SDEPT IN(CS,MA,IS); 與與IN相對的謂詞是相對的謂詞是NOT IN，用于查找屬性值不屬于指定集合的元組。，用于查找屬性值不屬于指定集合的元組。 例例4.33 查詢既不是計算機科學(xué)系、數(shù)學(xué)系，也不是信息系的學(xué)生的姓查詢既不是計算機科學(xué)系、數(shù)學(xué)系，也不是信息系的學(xué)生的姓名和性別。名和性別。 SELECT SNAME,SEX FROM S WHERE SDEPT NOT IN(CS,MA,IS); 20字符匹配謂詞字符匹配謂詞LIKE可以用來進(jìn)行字符串的匹配。其一般語法格式如下：可以用來進(jìn)

16、行字符串的匹配。其一般語法格式如下： NOT LIKE ESCAPE其含義是查找指定的屬性列值與其含義是查找指定的屬性列值與相匹配的元組。相匹配的元組。可以是一個完整的字符串，也可以含有通配符可以是一個完整的字符串，也可以含有通配符%和和_。其中：。其中： %（百分號）（百分號）代表任意長度（長度可以為代表任意長度（長度可以為0）的字符串。）的字符串。例如例如a%b表示以表示以a開頭，以開頭，以b結(jié)尾的任意長度的字符串。如結(jié)尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串。等都滿足該匹配串。_（下劃線）（下劃線）代表任意單個字符。例如代表任意單個字符。例如a_b表示以表示以a開

17、頭，以開頭，以b結(jié)尾的長結(jié)尾的長度為度為3的任意字符串。如的任意字符串。如acb，afb等都滿足該匹配串。等都滿足該匹配串。21例例4.34 查詢學(xué)號為查詢學(xué)號為200915121的學(xué)生的詳細(xì)情況。的學(xué)生的詳細(xì)情況。 SELECT * FROM S WHERE SNO LIKE 200915121; 等價于：等價于： SELECT * FROM S WHERE SNO=200915121;如果如果LIKE后面的匹配串中不含通配符，則可以用后面的匹配串中不含通配符，則可以用=（等于）運算符取代（等于）運算符取代LIKE謂詞，用謂詞，用!=或或（不等于）運算符取代（不等于）運算符取代NOT LIK

18、E謂詞。謂詞。例例4.35 查詢所有姓劉的學(xué)生的姓名、學(xué)號和性別。查詢所有姓劉的學(xué)生的姓名、學(xué)號和性別。 SELECT SNAME,SNO,SEX FROM S WHERE SNAME LIKE 劉劉%; 22 涉及空值的查詢例涉及空值的查詢例例例4.41 某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號?？荚嚦煽?。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。 SELECT SNO,CNO FROM SC WHERE GRADE IS NULL; /* 分?jǐn)?shù)分?jǐn)?shù)GRADE是空值是空值 */

19、多重條件查詢多重條件查詢可用邏輯運算符可用邏輯運算符AND和和OR來聯(lián)結(jié)多個查詢條件。來聯(lián)結(jié)多個查詢條件。AND的優(yōu)先級高于的優(yōu)先級高于OR，但可以用括號改變優(yōu)先級。，但可以用括號改變優(yōu)先級。例例4.43 查詢計算機科學(xué)系年齡在查詢計算機科學(xué)系年齡在20歲以下的學(xué)生姓名。歲以下的學(xué)生姓名。 SELECT SNAME FROM S WHERE SDEPT=CS AND AGE20; 233、ORDER BY子句（子句（對查詢結(jié)果排序?qū)Σ樵兘Y(jié)果排序）使用使用ORDER BY子句子句v 可以按一個或多個屬性列排序可以按一個或多個屬性列排序v 升序：升序：ASC；降序：；降序：DESC；缺省值為升序；

20、缺省值為升序當(dāng)排序列含空值時當(dāng)排序列含空值時vASC：排序列為空值的元組最后顯示：排序列為空值的元組最后顯示vDESC：排序列為空值的元組最先顯示：排序列為空值的元組最先顯示24 例例4.44 查詢選修了查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)的降序排列。果按分?jǐn)?shù)的降序排列。 SELECT SNO,GRADE FROM SC WHERE CNO=3 ORDER BY GRADE DESC; Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55

21、查詢結(jié)果查詢結(jié)果25 聚合函數(shù)是涉及整個關(guān)系的另一類運算操作聚合函數(shù)是涉及整個關(guān)系的另一類運算操作, ,通過聚合函通過聚合函數(shù)，可以把某一列中的值形成單個值。數(shù)，可以把某一列中的值形成單個值。 5類主要聚合函數(shù)：類主要聚合函數(shù)：n計數(shù)計數(shù)COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）n計算總和計算總和SUM（DISTINCT|ALL ）n 計算平均值計算平均值A(chǔ)VG（DISTINCT|ALL ）4.3.3 聚合函數(shù)聚合函數(shù)26n求最大值求最大值MAX（DISTINCT|ALL ）n求最小值求最小值 MIN（DISTINCT|ALL ）DISTINCT短語：在

22、計算時要取消指定列中的重復(fù)值短語：在計算時要取消指定列中的重復(fù)值A(chǔ)LL短語：不取消重復(fù)值短語：不取消重復(fù)值A(chǔ)LL為缺省值為缺省值27例例4.47 查詢選修了課程的學(xué)生人數(shù)。查詢選修了課程的學(xué)生人數(shù)。 SELECT COUNT(DISTINCT SNO) FROM SC;學(xué)生每選修一門課，在學(xué)生每選修一門課，在SC中都有一條相應(yīng)的記錄。一個學(xué)生要選修多中都有一條相應(yīng)的記錄。一個學(xué)生要選修多門課程，為避免重復(fù)計算學(xué)生人數(shù)，必須在門課程，為避免重復(fù)計算學(xué)生人數(shù)，必須在COUNT函數(shù)中用函數(shù)中用DISTINCT短短語。語。例例4.48 計算選修計算選修l號課程的學(xué)生平均成績。號課程的學(xué)生平均成績。 S

23、ELECT AVG(GRADE) FROM SC WHERE CNO=1;例例4.49 查詢選修查詢選修l號課程的學(xué)生最高分?jǐn)?shù)。號課程的學(xué)生最高分?jǐn)?shù)。 SELECT MAX(GRADE) FROM SC WHER CNO=1; 28使用使用GROUP BY子句分組子句分組 細(xì)化聚合函數(shù)的作用對象細(xì)化聚合函數(shù)的作用對象n 未對查詢結(jié)果分組，聚合函數(shù)將作用于整個查詢結(jié)果未對查詢結(jié)果分組，聚合函數(shù)將作用于整個查詢結(jié)果n 對查詢結(jié)果分組后，聚合函數(shù)將分別作用于每個組對查詢結(jié)果分組后，聚合函數(shù)將分別作用于每個組 4.3.4 對查詢結(jié)果分組對查詢結(jié)果分組 29這三個數(shù)取平均值第4-6分?jǐn)?shù)取平均值最后三個數(shù)

24、取平均值有一個學(xué)員參加考試?yán)?，求每門課的平均成績。例如，求每門課的平均成績。30分組查詢分組查詢GROUP BYSELECT CourseID, AVG(Score) AS 課程平均成績FROM ScoreGROUP BY CourseID31 例例4.51 求各個課程號及相應(yīng)的選課人數(shù)。求各個課程號及相應(yīng)的選課人數(shù)。 SELECT CNO,COUNT(SNO) FROM SC GROUP BY CNO; 該語句對查詢結(jié)果按該語句對查詢結(jié)果按CNO的值分組，所有具有相同的值分組，所有具有相同CNO值的元組為一組，然后對每一組作用聚合函數(shù)值的元組為一組，然后對每一組作用聚合函數(shù)COUNT計算，

25、以求得該組的學(xué)生人數(shù)。計算，以求得該組的學(xué)生人數(shù)。 結(jié)果 Cno COUNT(Sno)1 22 2 34 3 44 4 33 5 4832注意：注意：vGROUP BY子句的作用對象是查詢的中間結(jié)果表子句的作用對象是查詢的中間結(jié)果表v分組方法：按指定的一列或多列值分組，值相等的為一組分組方法：按指定的一列或多列值分組，值相等的為一組v使用使用GROUP BY子句后，子句后，SELECT子句的列名列表中只能子句的列名列表中只能出現(xiàn)分組屬性和聚合函數(shù)。出現(xiàn)分組屬性和聚合函數(shù)。33v使用使用HAVING短語篩選最終輸出結(jié)果。短語篩選最終輸出結(jié)果。例例 查詢選修了查詢選修了3門以上課程的學(xué)生學(xué)號。門以

26、上課程的學(xué)生學(xué)號。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3；34例例 查詢有查詢有3門以上課程是門以上課程是90分以上的學(xué)生的學(xué)號及（分以上的學(xué)生的學(xué)號及（90分以上分以上的）課程數(shù)。的）課程數(shù)。 SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3;35使用使用HAVING短語篩選最終輸出結(jié)果短語篩選最終輸出結(jié)果v只有滿足只有滿足HAVING短語指定條件的組才輸出。短語指定條件的組才輸出。vHAVING短語與短語與WHERE子句的區(qū)別：作

27、用對象不同子句的區(qū)別：作用對象不同nWHERE子句作用于基表或視圖，從中選擇滿足條件的元子句作用于基表或視圖，從中選擇滿足條件的元組。組。nHAVING短語作用于組，從中選擇滿足條件的組短語作用于組，從中選擇滿足條件的組。 WHEREGROUP BYHAVING36在多表查詢中，如果要引用不同關(guān)系中的同名屬性，則需要在屬性名在多表查詢中，如果要引用不同關(guān)系中的同名屬性，則需要在屬性名前加關(guān)系名，即用前加關(guān)系名，即用“關(guān)系名關(guān)系名.屬性名屬性名”的形式表示，以便區(qū)分。的形式表示，以便區(qū)分。1、連接查詢、連接查詢主要類型：主要類型：n 廣義笛卡爾積廣義笛卡爾積n 等值連接等值連接(含自然連接）與含

28、自然連接）與 非等值連接查詢非等值連接查詢n 自身連接查詢自身連接查詢n 外連接查詢外連接查詢n 復(fù)合條件連接查詢復(fù)合條件連接查詢4.3.5 多表查詢多表查詢37廣義笛卡爾積廣義笛卡爾積 v不帶連接謂詞的連接不帶連接謂詞的連接v 很少使用很少使用例：例： SELECT Student.* , SC.* FROM Student, SC38 等值與非等值連接查詢等值與非等值連接查詢 連接查詢的連接查詢的WHERE子句中用來連接兩個表的條件稱為子句中用來連接兩個表的條件稱為連接條件或連接謂詞，連接條件或連接謂詞，其一般格式為：其一般格式為： . .其中比較運算符主要有：其中比較運算符主要有：=、=

29、、=、!=（或（或）等。）等。 此外連接謂詞還可以使用下面形式：此外連接謂詞還可以使用下面形式： . BETWEEN . AND . 39 例例4.53 查詢每個學(xué)生及其選修課程的情況。查詢每個學(xué)生及其選修課程的情況。 學(xué)生情況存放在學(xué)生情況存放在S表中，學(xué)生選課情況存放在表中，學(xué)生選課情況存放在SC表中，所表中，所以本查詢實際上涉及以本查詢實際上涉及S與與SC兩個表。這兩個表之間的聯(lián)系是通兩個表。這兩個表之間的聯(lián)系是通過公共屬性過公共屬性SNO實現(xiàn)的。實現(xiàn)的。 SELECT S.*,SC.* FROM S,SC WHERE S.SNO=SC.SNO; /* 將將S與與SC中同一學(xué)生的元組連接

30、起來中同一學(xué)生的元組連接起來 */ 執(zhí)行步驟：首先在表S中找到第一個元組，然后從頭開始掃描SC表，逐一查找與S第一個元組的SNO相等的SC元組，找到后就將S中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。SC全部查找完后，再找S中第二個元組，然后再從頭開始掃描SC，逐一查找滿足連接條件的元組，找到后就將S中的第二個元組與該元組拼接起來，再形成結(jié)果表中一個元組。重復(fù)上述操作，直到S中的全部元組都處理完畢為止。這就是循環(huán)嵌套算法的基本思想 。40(2) 自身連接自身連接 連接操作不僅可以在兩個表之間進(jìn)行，也可以是一個表與其自己進(jìn)行連接操作不僅可以在兩個表之間進(jìn)行，也可以是一個表與其自己進(jìn)行連

31、接，稱為表的連接，稱為表的自身連接自身連接。例例4.55 查詢每一門課的間接先修課（即先修課的先修課）。查詢每一門課的間接先修課（即先修課的先修課）。在在C表中，只有每門課的直接先修課信息，而沒有先修課的先修課。要表中，只有每門課的直接先修課信息，而沒有先修課的先修課。要得到這個信息，必須先對一門課找到其先修課，再按此先修課的課程號，得到這個信息，必須先對一門課找到其先修課，再按此先修課的課程號，查找它的先修課程。這就將查找它的先修課程。這就將C表與其自身連接。為此，要為表與其自身連接。為此，要為C表取兩個別名表取兩個別名，一個是，一個是FIRST，另一個是，另一個是SECOND。該查詢的該查

32、詢的SQL語句為：語句為： SELECT FIRST.CNO,SECOND.CPNO FROM C FIRST,C SECOND WHERE FIRST.CPNO=SECOND.CNO; 41(3) 外連接外連接v外連接與普通連接的區(qū)別外連接與普通連接的區(qū)別n普通連接操作只輸出滿足連接條件的元組普通連接操作只輸出滿足連接條件的元組n外連接操作以指定表為連接主體，將主體表中不滿足外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出連接條件的元組一并輸出v外連接外連接左外連接左外連接(LEFT JOIN)右外連接右外連接(RIGHT JOIN)完整外連接完整外連接(FULL JOI

33、N)42例例4.56 查詢每個學(xué)生及其選修課程的情況。查詢每個學(xué)生及其選修課程的情況。 SELECT S.SNO,SNAME,SEX,AGE,SDEPT,CNO,GRAGE FROM S LEFT JOIN SC ON(S.SNO=SC.SNO);v例子：例子： - a表 id name b表 id job parent_id 1 張3 1 23 1 2 李四 2 34 2 3 王武 3 34 4 a.id同parent_id 存在關(guān)系 v左連接 select a.*,b.* from a left join b on a.id=b.parent_id 結(jié)果是 1 張3 1 23 1 2 李四

34、 2 34 2 3 王武 null 43v右連接 select a.*,b.* from a right join b on a.id=b.parent_id 結(jié)果是 1 張3 1 23 1 2 李四 2 34 2 null 3 34 4 44v 完全連接 select a.*,b.* from a full join b on a.id=b.parent_id 結(jié)果是 1 張3 1 23 1 2 李四 2 34 2 null 3 34 4 3 王武 null4546(4) 復(fù)合條件連接復(fù)合條件連接WHERE子句中可以有多個連接條件，稱為復(fù)合條件連接。子句中可以有多個連接條件，稱為復(fù)合條件連接

35、。例例4.57 查詢選修查詢選修2號課程且成績在號課程且成績在90分以上的所有學(xué)生的學(xué)號和姓名。分以上的所有學(xué)生的學(xué)號和姓名。 SELECT S.SNO,SNAME FROM S,SC WHERE S.SNO=SC.SNO AND SC.CNO=2 AND SC.GRADE90; 該查詢的一種優(yōu)化（高效）的執(zhí)行過程是先從該查詢的一種優(yōu)化（高效）的執(zhí)行過程是先從SC中挑選出中挑選出CNO=2并并且且GRADE90的元組形成一個中間關(guān)系，再和的元組形成一個中間關(guān)系，再和S中滿足連接條件的元組進(jìn)中滿足連接條件的元組進(jìn)行連接得到最終的結(jié)果關(guān)系。行連接得到最終的結(jié)果關(guān)系。 2、嵌套查詢、嵌套查詢 在在S

36、QL語言中，一個語言中，一個SELECTFROMWHERE語句稱為語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子子句或句或HAVING短語的條件中的查詢稱為短語的條件中的查詢稱為嵌套查詢（嵌套查詢（nested query）。 帶有帶有IN謂詞的子查詢謂詞的子查詢 例例4.59 查詢與查詢與“劉晨劉晨”在同一個系學(xué)習(xí)的學(xué)生。在同一個系學(xué)習(xí)的學(xué)生。先分步來完成此查詢，然后再構(gòu)造嵌套查詢。先分步來完成此查詢，然后再構(gòu)造嵌套查詢。 確定確定“劉晨劉晨”所在系名所在系名 SELECT SDEPT FROM S WHERE SNAME=劉晨劉

37、晨;結(jié)果為：結(jié)果為：CS 查找所有在查找所有在CS系學(xué)習(xí)的學(xué)生。系學(xué)習(xí)的學(xué)生。 SELECT SNO,SNAME,SDEPT FROM S WHERE SDEPT=CS; 48將第一步查詢嵌人到第二步查詢的條件中，嵌套查詢?nèi)缦拢簩⒌谝徊讲樵兦度说降诙讲樵兊臈l件中，嵌套查詢?nèi)缦拢?SELECT SNO,SNAME,SDEPT FROM S WHERE SDEPT IN (SELECT SDEPT FROM S WHERE SNAME=劉晨劉晨 ); 本例中，子查詢的查詢條件不依賴于父查詢，稱為本例中，子查詢的查詢條件不依賴于父查詢，稱為不相關(guān)子不相關(guān)子查詢查詢。一種求解方法是由里向外處理，即先

38、執(zhí)行子查詢，子查詢的一種求解方法是由里向外處理，即先執(zhí)行子查詢，子查詢的結(jié)果用于建立其父查詢的查找條件。結(jié)果用于建立其父查詢的查找條件。 49本查詢涉及學(xué)號、姓名和課程名本查詢涉及學(xué)號、姓名和課程名3個屬性。學(xué)號和姓名存放個屬性。學(xué)號和姓名存放在在S表中，課程名存放在表中，課程名存放在C表中，但表中，但S與與C兩個表之間沒有直接兩個表之間沒有直接聯(lián)系，必須通過聯(lián)系，必須通過SC表建立它們二者之間的聯(lián)系。所以本查詢表建立它們二者之間的聯(lián)系。所以本查詢實際上涉及實際上涉及3個關(guān)系。個關(guān)系。 SELECT SNO,SNAME /* 外層在外層在S關(guān)系中取出關(guān)系中取出SNO和和SNAME */ FRO

39、M SWHERE SNO IN (SELECT SNO /* 在在SC關(guān)系中找出選修了關(guān)系中找出選修了3號課程的學(xué)生學(xué)號號課程的學(xué)生學(xué)號 */ FROM SC WHERE CNO IN (SELECT CNO /* 在在C關(guān)系中找出關(guān)系中找出“信息系統(tǒng)信息系統(tǒng)”的課的課 FROM C 程號，結(jié)果為程號，結(jié)果為3號號 */ WHERE CNAME=信息系統(tǒng)信息系統(tǒng) ) ); 例例4.604.60 查詢選修了課程名為查詢選修了課程名為“信息系統(tǒng)信息系統(tǒng)”的學(xué)生學(xué)號和姓的學(xué)生學(xué)號和姓名。名。50本查詢同樣可以用連接查詢實現(xiàn)：本查詢同樣可以用連接查詢實現(xiàn)： SELECT S.SNO,SNAME FRO

40、M S,SC,C WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CNAME=信息系統(tǒng)信息系統(tǒng); 說明說明: 有些嵌套查詢可以用連接運算替代，有些是不能替代的有些嵌套查詢可以用連接運算替代，有些是不能替代的。如果子查詢的查詢條件依賴于父查詢，這類子查詢稱為如果子查詢的查詢條件依賴于父查詢，這類子查詢稱為相關(guān)子查詢相關(guān)子查詢。51v當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，=，!=或或）。）。v與與ANY或或ALL謂詞配合使用。謂詞配合使用。（2 2） 帶有比較運算符的子查詢帶有比較運算符的子查詢52

41、v例如在例例如在例4.59中，由于一個學(xué)生只可能在一中，由于一個學(xué)生只可能在一個系學(xué)習(xí)，則在因此可以個系學(xué)習(xí)，則在因此可以用用 = 代替代替IN ：SELECT SNO， SNAME ， SDEPT FROM S WHERE SDEPT = （SELECT SDEPT FROM S WHERE SNAME= 劉晨劉晨 ）；）；53 注意：注意：子查詢一定要跟在比較符之后！子查詢一定要跟在比較符之后！ 錯誤的例子錯誤的例子： SELECT SNO， SNAME ， SDEPT FROM S WHERE ( SELECT SDEPT FROM S WHERE SNAME= 劉晨劉晨 ) = SDE

42、PT ；54例例4.61 找出每個學(xué)生超過他選修課程平均成績的找出每個學(xué)生超過他選修課程平均成績的課程號。課程號。 SELECT SNO,CNO FROM SC X WHERE GRADE=( SELECT AVG(GRADE) FROM SC Y WHERE Y.SNO=X.SNO); 55謂詞語義謂詞語義vANY：任意一個值：任意一個值vALL：所有值：所有值（3 3）帶有）帶有ANYANY或或ALLALL謂詞的子查詢謂詞的子查詢需要配合使用比較運算符：需要配合使用比較運算符： ANY ANY大于子查詢結(jié)果中的某個值大于子查詢結(jié)果中的某個值 ALL ALL大于子查詢結(jié)果中的所有值大于子查詢

43、結(jié)果中的所有值 ANY ANY小于子查詢結(jié)果中的某個值小于子查詢結(jié)果中的某個值 ALL= ANY= ANY大于等于子查詢結(jié)果中的某個值大于等于子查詢結(jié)果中的某個值 = ALL= ALL大于等于子查詢結(jié)果中的所有值大于等于子查詢結(jié)果中的所有值= ANY= ANY小于等于子查詢結(jié)果中的某個值小于等于子查詢結(jié)果中的某個值 = ALL= ALL小于等于子查詢結(jié)果中的所有值小于等于子查詢結(jié)果中的所有值= ANY= ANY等于子查詢結(jié)果中的某個值等于子查詢結(jié)果中的某個值 =ALL =ALL 等于子查詢結(jié)果中的所有值（沒有實際意義）等于子查詢結(jié)果中的所有值（沒有實際意義）!=!=（或（或）ANYANY 不等

44、于子查詢結(jié)果中的某個值不等于子查詢結(jié)果中的某個值!=!=（或（或）ALLALL 不等于子查詢結(jié)果中的任何一個值不等于子查詢結(jié)果中的任何一個值57例例4.62 查詢其他系中比計算機科學(xué)系某一學(xué)生年齡小的學(xué)查詢其他系中比計算機科學(xué)系某一學(xué)生年齡小的學(xué)生姓名和年齡。生姓名和年齡。 SELECT SNAME,AGE FROM S WHERE AGE ANY(SELECT AGE FROM S WHERE SDEPT=CS) AND SdeptCS; 注意這是父查詢注意這是父查詢塊中的條件塊中的條件 RDBMS RDBMS執(zhí)行此查詢時，首先處理子查詢，找出執(zhí)行此查詢時，首先處理子查詢，找出CSCS系中所

45、有學(xué)生的系中所有學(xué)生的年齡，構(gòu)成一個集合（年齡，構(gòu)成一個集合（2020，1919）。然后處理父查詢，找所有不是）。然后處理父查詢，找所有不是CSCS系且年齡小于系且年齡小于2020或或l9l9的學(xué)生。的學(xué)生。 58ANY和和ALL謂詞有時可以用聚集函數(shù)實現(xiàn)謂詞有時可以用聚集函數(shù)實現(xiàn)ANY與與ALL與聚集函數(shù)的對應(yīng)關(guān)系與聚集函數(shù)的對應(yīng)關(guān)系 = 或或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX 用聚集函數(shù)實現(xiàn)子查詢通常比直接用用聚集函數(shù)實現(xiàn)子查詢通常比直接用ANY或或ALL查查詢效率要高，因為前者通常能夠減少比較次數(shù)。詢效率要高，因為前者通常能夠

46、減少比較次數(shù)。59【例例4.624.62】可以用聚集函數(shù)實現(xiàn)?？梢杂镁奂瘮?shù)實現(xiàn)。 SELECT SNAMESELECT SNAME，AGEAGE FROM S FROM S WHERE AGE WHERE AGE (SELECT (SELECT MAX(AGE)MAX(AGE) FROM S FROM S WHERE SDEPT= WHERE SDEPT= CS )CS ) AND SDEPT AND SDEPT CS CS ; ;60例例4.63 4.63 查詢其他系中比計算機科學(xué)系查詢其他系中比計算機科學(xué)系所有所有學(xué)生年齡學(xué)生年齡都都小小的學(xué)生姓名及年齡。的學(xué)生姓名及年齡。 用用ALL謂

47、詞謂詞SELECT SNAME，AGEFROM SWHERE AGE ALL (SELECT AGE FROM S WHERE SDEPT= CS ) AND SDEPT CS ;查詢結(jié)果為空表。查詢結(jié)果為空表。61 用聚集函數(shù)用聚集函數(shù) SELECT SNAME，AGE FROM S WHERE AGE (SELECT MIN(AGE) FROM S WHERE SDEPT = CS ) AND SDEPT CS ;62(4) (4) 帶有帶有EXISTSEXISTS謂詞的子查詢謂詞的子查詢EXISTS謂詞謂詞NOT EXISTS謂詞謂詞不同形式的查詢間的替換不同形式的查詢間的替換相關(guān)子查詢

48、的效率相關(guān)子查詢的效率用用EXISTS/NOT EXISTS實現(xiàn)全稱量詞實現(xiàn)全稱量詞用用EXISTS/NOT EXISTS實現(xiàn)邏輯蘊函實現(xiàn)邏輯蘊函63EXISTSEXISTS謂詞謂詞存在量詞存在量詞 帶有帶有EXISTSEXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值輯真值“truetrue”或邏輯假值或邏輯假值“falsefalse”。 若內(nèi)層查詢結(jié)果非空，則返回若內(nèi)層查詢結(jié)果非空，則返回真值真值 若內(nèi)層查詢結(jié)果為空，則返回若內(nèi)層查詢結(jié)果為空，則返回假值假值由由EXISTSEXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用引出的子查詢，其目標(biāo)列表達(dá)式通

49、常都用* * ，因為帶，因為帶EXISTSEXISTS的子查詢只返回真值或假值，給出列的子查詢只返回真值或假值，給出列名無實際意義。名無實際意義。NOT EXISTSNOT EXISTS謂詞謂詞64例例4.644.64 查詢所有選修了查詢所有選修了1 1號課程的學(xué)生姓名。號課程的學(xué)生姓名。 分析：分析： 本查詢涉及本查詢涉及S S和和SCSC關(guān)系。關(guān)系。 在在S S中依次取每個元組的中依次取每個元組的SNOSNO值，用此值去檢查值，用此值去檢查SCSC關(guān)系。關(guān)系。 若若SCSC中存在這樣的元組，其中存在這樣的元組，其SNOSNO值等于此值等于此S. SNOS. SNO值，并值，并且其且其CNO

50、= CNO= 1 1，則取此，則取此S.SNAMES.SNAME送入結(jié)果關(guān)系。送入結(jié)果關(guān)系。65用嵌套查詢用嵌套查詢 SELECT SNAMESELECT SNAME FROM FROM S S WHERE EXISTS WHERE EXISTS (SELECT (SELECT * * FROM SC / FROM SC /* *相關(guān)子查詢相關(guān)子查詢* */ / WHERE SNO= WHERE SNO=S.SNOS.SNO AND AND CNO= 1)CNO= 1)；66n用連接運算用連接運算SELECT SNAMESELECT SNAMEFROM S, SCFROM S, SCWHERE

51、 S.SNO =SC.SNO AND SC.CNO= 1;WHERE S.SNO =SC.SNO AND SC.CNO= 1;67例例4.65 4.65 查詢沒有選修查詢沒有選修1 1號課程的學(xué)生姓名。號課程的學(xué)生姓名。 SELECT SNAMESELECT SNAME FROM FROM S S WHERE NOT EXISTS WHERE NOT EXISTS (SELECT (SELECT * * FROM SC FROM SC WHERE SNO = WHERE SNO = S.S.SNO AND CNO=1)SNO AND CNO=1)；此例用連接運算難于實現(xiàn)此例用連接運算難于實現(xiàn)!

52、 ! 68n一些一些帶帶EXISTSEXISTS或或NOT EXISTSNOT EXISTS謂詞的子查詢不能被其他形式的子查詢謂詞的子查詢不能被其他形式的子查詢等價替換等價替換n所有所有帶帶ININ謂詞、比較運算符、謂詞、比較運算符、ANYANY和和ALLALL謂詞的子查詢都能用帶謂詞的子查詢都能用帶EXISTSEXISTS謂詞的子查詢等價替換。謂詞的子查詢等價替換。 例例4.654.65 （例（例4.594.59另解）查詢與另解）查詢與“劉晨劉晨”在同一個系學(xué)習(xí)的學(xué)生。在同一個系學(xué)習(xí)的學(xué)生。 SELECT SNO,SNAME,SDEPT SELECT SNO,SNAME,SDEPT FROM

53、 S S1 FROM S S1 WHERE EXISTS WHERE EXISTS (SELECT (SELECT * * FROM S S2 FROM S S2 WHERE S2.SDEPT=S1.SDEPT AND S2.SNAME= WHERE S2.SDEPT=S1.SDEPT AND S2.SNAME=劉晨劉晨); ); 69SQL中沒有全稱量詞（中沒有全稱量詞（FOR ALL）?？蓪㈩}目的意思轉(zhuǎn)換成等價的形）?？蓪㈩}目的意思轉(zhuǎn)換成等價的形式：查詢這樣的學(xué)生，沒有一門課程是他不選修的。式：查詢這樣的學(xué)生，沒有一門課程是他不選修的。其其SQL語句為：語句為： SELECT SNAME

54、FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXlSTS (SELECT * FROM SC WHERE SNO=S.SNO AND CNO=C.CNO); 從而用從而用EXIST/NOT EXIST來實現(xiàn)帶全稱量詞的查詢。來實現(xiàn)帶全稱量詞的查詢。 例例4.664.66 查詢選修了全部課程的學(xué)生姓名。查詢選修了全部課程的學(xué)生姓名。70本查詢可以用邏輯蘊涵來表達(dá)：查詢學(xué)號為本查詢可以用邏輯蘊涵來表達(dá)：查詢學(xué)號為x的學(xué)生，對所有的課程的學(xué)生，對所有的課程y，只要，只要200915122學(xué)生選修了課程學(xué)生選修了課程y，則，則x也選修了也選修了

55、y。形式化表示：形式化表示：用用p p表示謂詞表示謂詞 “學(xué)生學(xué)生200915122選修了課程選修了課程y y”用用q q表示謂詞表示謂詞 “學(xué)生學(xué)生x x選修了課程選修了課程y y”則上述查詢?yōu)閯t上述查詢?yōu)? (: ( y) p y) p q q 。等價變換：等價變換： ( y)p q ( y ( (p q ) ( y ( ( p q) y(p q)將語義表達(dá)為將語義表達(dá)為：不存在這樣的課程：不存在這樣的課程y，學(xué)生，學(xué)生200915122選修選修了了y，而學(xué)生，而學(xué)生x沒有選。沒有選。 例例4.674.67 查詢至少選修了學(xué)生查詢至少選修了學(xué)生200915122200915122選修的全部

56、課程的學(xué)生號碼。選修的全部課程的學(xué)生號碼。71SELECT DISTINCT SNO FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.SNO=200915122 AND NOT EXISTS ( SELECT * FROM SC SCZ WHERE SCZ.SNO=SCX.SNO AND SCZ.CNO=SCY.CNO ); 72v標(biāo)準(zhǔn)標(biāo)準(zhǔn)SQLSQL直接支持的集合操作種類直接支持的集合操作種類n并操作并操作(UNION)(UNION)v一般商用數(shù)據(jù)庫支持的集合操作種類一般商用數(shù)據(jù)庫支持的集合操作種類n并操作并操作(UN

57、ION)(UNION)n交操作交操作(INTERSECT)(INTERSECT)n差操作差操作(MINUS)(MINUS)4.3.6 集合操作集合操作73vSELECTSELECT語句的查詢結(jié)果是元組的集合，所以多個語句的查詢結(jié)果是元組的集合，所以多個SELECTSELECT語句的結(jié)果可進(jìn)行集合操作。語句的結(jié)果可進(jìn)行集合操作。 v形式形式 UNION n參加參加UNION操作的各結(jié)果表的列數(shù)必須相同；對應(yīng)項的操作的各結(jié)果表的列數(shù)必須相同；對應(yīng)項的數(shù)據(jù)類型也必須相同數(shù)據(jù)類型也必須相同74v例例4.68 查詢計算機科學(xué)系的學(xué)生及年齡不大于查詢計算機科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生歲的學(xué)生。 S

58、ELECT SELECT * * FROM S FROM S WHERE SDEPT= WHERE SDEPT=CSCS UNIONUNION SELECT SELECT * * FROM S FROM S WHERE AGE=19; WHERE AGE=19; v本查詢實際上是求計算機科學(xué)系的所有學(xué)生與年齡不大于本查詢實際上是求計算機科學(xué)系的所有學(xué)生與年齡不大于19歲的學(xué)生的并集。使歲的學(xué)生的并集。使UNION將多個查詢結(jié)果合并起來時，系將多個查詢結(jié)果合并起來時，系統(tǒng)會自動去掉重復(fù)元組。如果要保留重復(fù)元組則需要換為統(tǒng)會自動去掉重復(fù)元組。如果要保留重復(fù)元組則需要換為UNION ALL操作符。操

59、作符。 SELECT SNO SELECT SNO FROM SC FROM SC WHERE CNO=WHERE CNO=l lINTERSECTINTERSECTSELECT SNOSELECT SNOFROM SCFROM SCWHERE CNO=WHERE CNO=2 2； 本例也可以表示為本例也可以表示為: : SELECT SNO SELECT SNO FROM SC FROM SC WHERE CNO= WHERE CNO=1 1 AND SNO IN AND SNO IN ( SELECT SNO FROM SC ( SELECT SNO FROM SC WHERE CNO=

60、WHERE CNO=2 2); ); 例例4.71 4.71 查詢既選修了查詢既選修了1 1號課程又選修了號課程又選修了2 2號課程的全體學(xué)生。號課程的全體學(xué)生。就是查詢選修就是查詢選修1 1號課程的學(xué)生集合與選修號課程的學(xué)生集合與選修2 2號課程的學(xué)生集合號課程的學(xué)生集合的交集。的交集。76SELECTSELECT ALL|DISTINCT ALL|DISTINCT 別名別名 ， 別名別名 FROMFROM 別名別名 ， 別名別名 WHEREWHERE GROUP BYGROUP BY 1， . . HAVING HAVING ORDER BYORDER BY ASC|DESC 2 ASC|