數(shù)據(jù)庫系統(tǒng)原理與設計.ppt

第3章 SQL語言,數(shù)據(jù)庫系統(tǒng)原理與設計,第3章 SQL語言,學習目標 SQL(Structured Query Language，結構化查詢語言)是關系數(shù)據(jù)庫的標準語言 幾乎所有的關系型數(shù)據(jù)庫管理系統(tǒng)均采用SQL語言標準 教學目標主要有兩個 要求讀者掌握對數(shù)據(jù)庫的基本操作，并了解數(shù)據(jù)庫管理系統(tǒng)的基本功能 要求讀者熟練掌握SQL查詢語句，并運用SQL語句完成對數(shù)據(jù)庫的操作,第3章 SQL語言,學習方法 要求讀者結合課堂講授的知識，強化上機實訓，把課堂上學過的有關概念進行實訓，以便加深理解，達到學習目標。 學習指南 重點：3.2、3.3、3.4、3.5、3.8和3.9節(jié) 難點：3.3、3.4和3.5節(jié),第3章 SQL語言,本章導讀 SQL查詢語句對關系代數(shù)運算做了哪些擴展？ 連接查詢包括哪些？它們分別用于什么地方？ 相關子查詢與非相關子查詢的概念。 在使用分組聚合查詢時需要注意的地方。 如何理解存在量詞以及存在量詞在SQL查詢中的重要地位。 如何理解查詢表的概念，查詢表與子查詢有何異同點？ 在SQL的DDL語句中如何實現(xiàn)完整性約束？實現(xiàn)完整性約束的方法有哪些？ 如何將數(shù)據(jù)庫的對象(如基本表、索引)建立在特定的物理文件上？ 將數(shù)據(jù)庫對象建立在特定的物理文件上，其目的是什么？ 建立視圖的目的是什么？視圖主要用于什么操作？,目 錄,嵌套子查詢,SQL概述,簡單查詢,連接查詢,聚合查詢,集合運算,SQL查詢一般格式,SQL數(shù)據(jù)定義語言,SQL數(shù)據(jù)更新語言,視圖,3.1 SQL概述,SQL語言于1974年由Boyce等提出，并于19751979年在IBM公司研制的System R數(shù)據(jù)庫管理系統(tǒng)上實現(xiàn)，現(xiàn)已成為國際標準。 很多數(shù)據(jù)庫廠商都對SQL語句進行了再開發(fā)和擴展 標準SQL命令包括 查詢SELECT 插入INSERT 更新UPDATE 刪除DELETE 創(chuàng)建CREATE 對象刪除DROP 可被用來完成幾乎所有的數(shù)據(jù)庫操作,3.1 SQL概述,3.1.1 SQL發(fā)展 3.1.2 SQL特點 3.1.3 SQL查詢基本概念,3.1.1 SQL發(fā)展,SQL-86：第一個SQL標準，由美國國家標準局(American National Standard Institute，簡稱ANSI)公布，1987年國際標準化組織(International Organization for Standardization，簡稱ISO)通過。該標準也稱為SQL-1 SQL-92：在1992年，由ISO和ANSI對SQL-86進行重新修訂，該標準也稱為SQL-2 SQL-99：在1999年，該版本在SQL-2的基礎上，擴展了諸多功能，包括遞歸、觸發(fā)、面向對象技術等。該標準也稱為SQL-3 SQL-2003：該標準是最新的標準，也稱SQL-4，于2003年發(fā)布,3.1.1 SQL發(fā)展,SQL語言由4部分組成 數(shù)據(jù)定義語言DDL(Data Definition Language) 定義數(shù)據(jù)庫的邏輯結構，包括數(shù)據(jù)庫、基本表、視圖和索引等，擴展DDL還支持存儲過程、函數(shù)、對象、觸發(fā)器等的定義 DDL包括3類語言，即定義、修改和刪除 數(shù)據(jù)操縱語言DML(Data Manipuplation Language) 對數(shù)據(jù)庫的數(shù)據(jù)進行檢索和更新，其中更新操作包括插入、刪除和修改數(shù)據(jù) 數(shù)據(jù)控制語言DCL(Data Control Language) 對數(shù)據(jù)庫的對象進行授權、用戶維護(包括創(chuàng)建、修改和刪除)、完整性規(guī)則定義和事務定義等 其它 主要是嵌入式SQL語言和動態(tài)SQL語言的定義，規(guī)定了SQL語言在宿主語言中使用的規(guī)則 擴展SQL還包括數(shù)據(jù)庫數(shù)據(jù)的重新組織、備份與恢復等功能,3.1.2 SQL特點,綜合統(tǒng)一 集數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML、數(shù)據(jù)控制語言DCL的功能于一體 高度非過程化 面向集合的操作方式 采用集合操作方式，其操作對象、查找結果都是元組的集合 同一種語法結構提供兩種使用方式 SQL語言既是自含式語言，又是嵌入式語言。在兩種不同的使用方式下，其語法結構基本上是一致的 語言簡潔，易學易用 SQL語言的動詞非常少，主要包括： 數(shù)據(jù)查詢 SELECT； 數(shù)據(jù)定義 CREATE、DROP、ALTER； 數(shù)據(jù)操縱 INSERT、UPDATE、DELETE； 數(shù)據(jù)控制 GRANT、REVOKE,3.1.3 SQL查詢基本概念,SQL語言支持三級模式結構，外模式對應視圖和部分基本表，模式對應基本表，內(nèi)模式對應存儲文件,3.1.3 SQL查詢基本概念,基本表 數(shù)據(jù)庫中獨立存在的表稱為基本表 在SQL中一個關系對應一個基本表 一個(或多個)基本表對應一個存儲文件 一個表可以帶若干索引 索引存放在存儲文件中 視圖 指從一個或幾個基本表(或視圖)導出的表，是虛表 只存放視圖的定義而不存放對應數(shù)據(jù) 查詢表 指查詢結果對應的表 存儲文件 指數(shù)據(jù)庫中存放關系的物理文件,3.7 SQL查詢一般格式 P106,SELECT共有6個子句，其中SELECT和FROM是必須的，其它是可選項，必須嚴格按照如下順序排列： SELECT ALL | DISTINCT AS , AS , . FROM AS , AS , . WHERE GROUP BY , , . HAVING ORDER BY ASC | DESC , ASC | DESC, . ,3.7 SQL查詢一般格式,其中： (1) 可以是下面的可選格式： .*, ., , (2) FROM子句指定查詢所涉及的表、視圖或查詢表。為操作方便，常給表取一個別名，稱為元組變量 (3) WHERE子句給出查詢的條件，隨后的中可以使用下面的謂詞運算符： 比較運算符：，=，!=； 邏輯運算符：AND，OR，NOT； 范圍運算符：NOT BETWEEN.AND； 集合運算符：NOT IN； 空值運算符：IS NOT null； 字符匹配運算符：NOT LIKE； 存在量詞運算符：NOT EXISTS。 在中可以包含子查詢，但不可以直接使用聚合函數(shù)，若要使用聚合函數(shù)，必須引出一個子查詢，如例3.52所示。,目 錄,嵌套子查詢,SQL概述,簡單查詢,連接查詢,聚合查詢,集合運算,SQL查詢一般格式,SQL數(shù)據(jù)定義語言,SQL數(shù)據(jù)更新語言,視圖,3.2 簡單查詢,本章所用的數(shù)據(jù)庫為學生成績管理數(shù)據(jù)庫ScoreDB，其數(shù)據(jù)庫模式如圖3-2、3-3、3-4、3-5所示,3.2 簡單查詢,3.2 簡單查詢,3.2 簡單查詢,3.2 簡單查詢,3.2.1 投影運算 3.2.2 選擇運算 3.2.3 排序運算 3.2.4 查詢表,3.2.1 投影運算 P76,SQL基本結構包括3個子句： SELECT子句 對應投影運算，指定查詢結果中所需要的屬性或表達式 FROM子句 對應笛卡爾積，給出查詢所涉及的表，表可以是基本表、視圖或查詢表 WHERE子句 對應選擇運算，指定查詢結果元組所需要滿足的選擇條件 SELECT和FROM是必須的，其他是可選的,3.2.1 投影運算,基本語法為： SELECT A1, A2, ., An FROM R1, R2, ., Rm WHERE P A1, A2, ., An代表需要查找的屬性或表達式 R1, R2, ., Rm代表查詢所涉及的表 P代表謂詞(即選擇條件)，如果省略WHERE子句，表示P為真 SQL的查詢結果中允許包含重復元組 SQL執(zhí)行過程： 首先對R1, R2, ., Rm執(zhí)行笛卡爾積 然后在笛卡爾積中選擇使得謂詞P為真的記錄 再在A1, A2, ., An屬性列中進行投影運算，不消除重復元組 如需消除重復元組，必須使用關鍵字DISTINCT,剛才描述的SQL查詢執(zhí)行過程只是邏輯上的，在具體執(zhí)行時會進行優(yōu)化處理，查詢優(yōu)化的內(nèi)容詳見第7章。,最簡單的查詢,查看全校班級的所有信息 SELECT * FROM Class *表示所有屬性，按照表定義時的順序顯示所有屬性,3.2.1 投影運算,查詢指定列 選取表中的全部列或指定列，通過SELECT確定要查詢的屬性 例3.1 查詢所有班級的班級編號、班級名稱和所屬學院 SELECT classNo, className, institute FROM Class 該查詢的執(zhí)行過程是： 從Class表中依次取出每個元組 對每個元組僅選取classNo、className和institute三個屬性的值，形成一個新元組 最后將這些新元組組織為一個結果關系輸出 該查詢的結果如圖3-10所示,練習,查詢學生的學號、姓名、所屬班級 查詢顯示所有學生的學號，姓名，年齡,3.2.1 投影運算,消除重復元組 需要消除重復元組，使用DISTINCT關鍵字 例3.2 查詢所有學院的名稱。 SELECT institute FROM Class 上述查詢不消除重復元組，其查詢結果如圖3-11所示 消除重復元組，查詢結果如圖3-12所示 SELECT DISTINCT institute FROM Class,圖3-11,圖3-12,3.2.1 投影運算,給屬性列取別名 可為屬性列取一個便于理解的列名，如用中文來顯示列名 為屬性列取別名特別適合經(jīng)過計算的列 例3.4 查詢所有班級的所屬學院、班級編號和班級名稱，要求用中文顯示列名 SELECT institute 所屬學院, classNo 班級編號, className 班級名稱 FROM Class 查詢結果如圖3-13所示。該查詢可使用AS關鍵字取別名： SELECT institute AS 所屬學院, classNo AS 班級編號, className AS 班級名稱 FROM Class,3.2.1 投影運算,查詢經(jīng)過計算的列 可使用屬性、常數(shù)、函數(shù)和表達式 例3.5 查詢每個班級編號、班級名稱以及該班級現(xiàn)在為幾年級，并將班級編號中大寫字母改為小寫字母輸出 SELECT LCase(classNo) AS 班級編號, className, year(date() - grade AS 年級 FROM Class 函數(shù)lcase()將大寫字母改為小寫字母 函數(shù)date()獲取當前系統(tǒng)的日期 函數(shù)year()提取日期中的年份 查詢結果如圖3-14所示,3.2.2 選擇運算(關注WHERE子句),WHERE子句可實現(xiàn)關系代數(shù)中的選擇運算 WHERE常用的查詢條件有： 比較運算：、=、(或!=) 范圍查詢：BETWEEN.AND 集合查詢：IN 空值查詢：IS null 字符匹配查詢：LIKE 邏輯查詢：AND、OR、NOT,3.2.2 選擇運算,比較運算 使用比較運算符、=、(或!=) 例3.6 查詢2007級的班級編號、班級名稱和所屬學院。 SELECT classNo, className, institute FROM Class WHERE grade=2007 其查詢結果如圖3-15所示,3.2.2 選擇運算,該查詢的執(zhí)行過程可能有多種方法： 全表掃描法 依次取出Class表中的每個元組 判斷該元組的grade屬性值是否等于2007 若是則將該元組的班級編號、班級名稱和所屬學院屬性取出，形成一個新元組 最后將所有新元組組織為一個結果關系輸出 該方法適用于小表，或者該表未在grade屬性列上建索引 索引搜索法 如果該表在grade屬性列上建有索引，且滿足條件的記錄不多，則可使用索引搜索法來檢索數(shù)據(jù) 具體使用何種方法由數(shù)據(jù)庫管理系統(tǒng)的查詢優(yōu)化器來選擇，詳見第8章內(nèi)容,3.2.2 選擇運算,例3.7 在學生Student表中查詢年齡大于或等于19歲的同學學號、姓名和出生日期。 SELECT studentNo, studentName, birthday FROM Student WHERE year(getdate() - year(birthday)=19,3.2.2 選擇運算,范圍查詢 BETWEEN.AND用于查詢屬性值在某一個范圍內(nèi)的元組 NOT BETWEEN.AND用于查詢屬性值不在某一個范圍內(nèi)的元組 BETWEEN后是屬性的下限值，AND后是屬性的上限值 例3.8 在選課Score表中查詢成績在8090分之間的同學學號、課程號和相應成績 SELECT studentNo, courseNo, score FROM Score WHERE score BETWEEN 80 AND 90 該查詢也可以使用邏輯運算AND實現(xiàn)，見例3.22,3.2.2 選擇運算,例3.9 在選課Score表中查詢成績不在8090分之間的同學學號、課程號和相應成績。 SELECT studentNo, courseNo, score FROM Score WHERE score NOT BETWEEN 80 AND 90 該查詢也可以使用邏輯運算OR實現(xiàn)，見例3.23,3.2.2 選擇運算,集合查詢 IN用于查詢屬性值在某個集合內(nèi)的元組 NOT IN用于查詢屬性值不在某個集合內(nèi)的元組 IN后面是集合，可以是具體的集合，也可以是查詢出來的元組集合(該部分內(nèi)容詳見3.4節(jié)的內(nèi)容)。 例3.10 在選課Score表中查詢選修了“001”、“005”或“003”課程的同學學號、課程號和相應成績。 SELECT studentNo, courseNo, score FROM Score WHERE courseNo IN (001, 005, 003) 該查詢也可以使用邏輯運算OR實現(xiàn)，見例3.19,3.2.2 選擇運算,例3.11 在學生 Student表中查詢籍貫不是“南昌”或“上?！钡耐瑢W姓名、籍貫和所屬班級編號。 SELECT studentName, native, classNo FROM Student WHERE native NOT IN (南昌, 上海) 該查詢也可以使用邏輯運算AND實現(xiàn)，見例3.21,3.2.2 選擇運算,空值查詢 空值表示未知或不確定的值，空值表示為null IS null用于查詢屬性值為空值 IS NOT null用于查詢屬性值不為空值 IS不能用“=”替代 例3.12 在課程Course表中查詢先修課程為空值的課程信息。 SELECT * FROM Course WHERE priorCourse IS NULL 查詢結果如圖3-16所示,3.2.2 選擇運算,例3.13 在課程Course表中查詢有先修課程的課程信息。 SELECT * FROM Course WHERE priorCourse IS NOT NULL,3.2.2 選擇運算,字符匹配查詢 LIKE用于字符匹配查詢，語法格式為： NOT LIKE ESCAPE 查詢的含義是： 如果在LIKE前沒有NOT，則查詢指定的屬性列值與相匹配的元組； 如果在LIKE前有NOT，則查詢指定的屬性列值不與相匹配的元組。 可以是一個具體的字符串，也可以包括通配符%和_ %表示任意長度的字符串 Ab%，表示所有以ab開頭的任意長度的字符串； Zhang%ab，表示以zhang開頭，以ab結束，中間可以是任意個字符的字符串。 符號_表示任意一個字符 Ab_，表示所有以 ab開頭的3個字符的字符串，其中第3個字符為任意字符； A_ _b表示所有以a開頭，以b 結束的4個字符的字符串，且第2、3個字符為任意字符。,3.2.2 選擇運算,例3.14 在班級Class表中查詢班級名稱中含有會計的班級信息 SELECT * FROM Class WHERE className LIKE %會計% 注意：匹配字符串必須用一對引號括起來,3.2.2 選擇運算,例3.15 在學生Student表中查詢所有姓王且全名為3個漢字的同學學號和姓名 SELECT studentNo, studentName FROM Student WHERE studentName LIKE 王_ _ 例3.16 在學生Student表中查詢名字中不含有“?！钡耐瑢W學號和姓名。 SELECT studentNo, studentName FROM Student WHERE studentName NOT LIKE %福%,3.2.2 選擇運算,例3.17 在學生Student表中查詢蒙古族的同學學號和姓名 SELECT studentNo, studentName FROM Student WHERE nation LIKE 蒙古族 注意：如果匹配字符串中不含有%和_，則LIKE與比較運算符“=”的查詢結果一樣 該查詢等價于下面的查詢： SELECT studentNo, studentName FROM Student WHERE nation=蒙古族,3.2.2 選擇運算,如果查詢字串中本身要包含%和_，必須使用“ESCAPE ”短語，對通配符進行轉義處理。 例3.18 在班級Class表中查詢班級名稱中含有“08_”符號的班級名稱 SELECT className FROM Class WHERE className LIKE %08_% ESCAPE “ESCAPE ”表示為換碼字符 緊跟在符號后的_不是通配符，而是普通的用戶要查詢的符號 查詢結果如圖3-17所示,3.2.2 選擇運算,如果將#字符作為換碼字符，則該查詢可改寫為： SELECT className FROM Class WHERE className LIKE %08#_% ESCAPE #,3.2.2 選擇運算,邏輯查詢 SQL提供AND、OR和NOT邏輯運算符分別實現(xiàn)邏輯與、邏輯或和邏輯非運算 例3.19 在選課Score表中查詢選修了“001”、“005”或“003”課程的同學學號、課程號和相應成績 SELECT studentNo, courseNo, score FROM Score WHERE courseNo=001 OR courseNo=005 OR courseNo=003 在例3.10中使用的是集合運算，本例中采用邏輯“或”運算,3.2.2 選擇運算,例3.20 在Student表中查詢1991年出生且籍貫為“漢族”的同學學號、姓名、出生日期。 SELECT studentNo, studentName, birthday FROM Student WHERE year(birthday)=1991 AND nation=漢族 注意：在邏輯運算中，不可以對同一個屬性進行邏輯“與”的等值運算 如在選課Score表中查詢同時選修了“001”和“002”課程的同學的選課信息，如下查詢是錯誤的，得不到結果： SELECT * FROM Score WHERE courseNo=001 AND courseNo=002 要實現(xiàn)該查詢，需要使用連接運算或嵌套子查詢 通過連接運算表示該查詢，參見例3.29、例3.31 通過嵌套子查詢，參見例3.39、例3.40,3.2.2 選擇運算,例3.21 在Student表中查詢籍貫不是“南昌”或“上?！钡耐瑢W姓名、籍貫和所屬班級編號。 SELECT studentName, native, classNo FROM Student WHERE native!=南昌 AND native!=上海 例3.22 在選課Score表中查詢成績在8090分之間的同學學號、課程號和相應成績。 SELECT studentNo, courseNo, score FROM Score WHERE score= 80 AND score=90,3.2.2 選擇運算,例3.23 在選課Score表中查詢成績不在8090分之間的同學學號、課程號和相應成績。 SELECT studentNo, courseNo, score FROM Score WHERE score90,3.2.3 排序運算,使用ORDER BY 子句實現(xiàn)排序運算，其語法為： ORDER BY ASC | DESC , ASC | DESC, . 其中： , , .可以是屬性、函數(shù)或表達式 缺省按升序(ASC)排序 按降序排序，必須指明DESC選項 該運算含義是： 在查詢結果中首先按的值進行排序 在值相等的情況下再按值排序 依此類推,3.2.3 排序運算,例3.24 在學生 Student表中查詢籍貫不是“南昌”或“上?！钡耐瑢W姓名、籍貫和所屬班級編號，并按籍貫的降序排序輸出。 SELECT studentName, native, classNo FROM Student WHERE native!=南昌 AND native!=上海 ORDER BY native DESC 查詢結果如圖3-18所示,3.2.3 排序運算,例3.25 在學生Student表中查詢“女”學生的學號、姓名、所屬班級編號和出生日期，并按班級編號的升序、出生日期的月份降序排序輸出。 SELECT studentNo, studentName, classNo, birthday FROM Student WHERE sex=女 ORDER BY classNo, month(birthday) DESC 其中：month()函數(shù)表示提取日期表達式的月份 查詢結果如圖3-19所示,3.2.4 查詢表,FROM子句后面可以是基本關系、視圖，還可以是查詢表 例3.26 查詢1991年出生的“女”同學基本信息。 分析：可以先將學生表中的女生記錄查詢出來，然后再對查詢表進行選擇、投影操作。 SELECT studentNo, studentName, birthday FROM (SELECT * FROM Student WHERE sex=女) AS a WHERE year(birthday)=1991 在FROM子句后是一個子查詢，表示對子查詢的查詢結果查詢表進行查詢 必須為查詢表取一個名稱(稱為元組變量)，如使用AS a取名為a FROM (SELECT * FROM Student WHERE sex=女) a 該查詢等價于下面的查詢： SELECT studentNo, studentName, birthday FROM student WHERE year(birthday)=1991 AND sex=女,目 錄,嵌套子查詢,SQL概述,簡單查詢,連接查詢,聚合查詢,集合運算,SQL查詢一般格式,SQL數(shù)據(jù)定義語言,SQL數(shù)據(jù)更新語言,視圖,3.3 連接查詢,在實際應用中，往往會涉及到多個關系的查詢，需用到連接運算或子查詢 連接運算是關系數(shù)據(jù)庫中使用最廣泛的一種運算，包括等值連接、自然連接、非等值連接、自表連接和外連接等 3.3.1 等值與非等值連接 3.3.2 自表連接 3.3.3 外連接,3.3.1 等值與非等值連接,該運算在WHERE子句中加入連接多個關系的連接條件 格式為： WHERE . . . . . 比較運算符包括： 、=、(或!=) 當比較運算符為=時，表示等值連接 其他運算為非等值連接 WHERE子句的連接謂詞中的屬性稱為連接屬性 連接屬性之間必須具有可比性,3.3.1 等值與非等值連接,等值連接 例3.27 查找會計學院全體同學的學號、姓名、籍貫、班級編號和所在班級名稱。 該查詢的結果為學號、姓名、籍貫、班級編號和班級名稱，在SELECT子句中必須包含這些屬性 由于班級名稱和所屬學院在班級表Class中，學號、姓名、籍貫、班級編號在學生表Student中，F(xiàn)ROM子句必須包含Class表和Student表 由于班級編號classNo既是班級表的主碼，也是學生表的外碼，這2個表的連接條件是claaaNo相等，在WHERE子句中必須包含連接條件Student.classNo=Class.classNo 本查詢要查詢出會計學院的學生記錄，在WHERE子句中還必須包括選擇條件institute=會計學院,3.3.1 等值與非等值連接,本查詢語句為： SELECT studentNo, studentName, native, Student.classNo, className FROM Student, Class WHERE Student.classNo=Class.classNo AND institute=會計學院 在連接操作中，如果涉及到多個表的相同屬性名，必須在相同的屬性名前加上表名加以區(qū)分 如Student.classNo、Class.classNo WHERE子句中包含兩種條件 Student.classNo=Class.classNo為連接條件 institute=會計學院 為選擇條件,3.3.1 等值與非等值連接,構造等值連接查詢語句的步驟 確定select子句 確定from子句中的表 確定連接條件(主碼與外碼的等值連接) 確定選擇條件,3.3.1 等值與非等值連接,練習 2.8(3) studentNo,studentName,sex,nation(nation!=“漢族”institute=“信息學院” （classstudent）) (4) courseNo,courseName,courseHour(term=“08092” （coursescore）) (5) (6) (7) studentNo,studentName (Student （score priorCourse=“CS012” course）) 查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應成績，并按學號排序輸出,3.3.1 等值與非等值連接,例3.28 查找選修了課程名稱為“計算機原理”的同學學號、姓名。 查詢結果為學號、姓名，在SELECT子句中必須包含這些屬性 學號和姓名在學生表中，課程名稱在課程表中，F(xiàn)ROM子句必須包含學生表Student、課程表Course 學生表與課程表之間是多對多聯(lián)系，需通過成績表轉換為兩個多對一的聯(lián)系，F(xiàn)ROM子句必須包含成績表Score 課程號既是課程表的主碼，也是成績表的外碼，這2個表的連接條件是課程號相等；學號既是學生表的主碼，也是成績表的外碼，這2個表的連接條件是學號相等。在WHERE子句中涉及三個關系的連接，其連接條件為： Course.courseNo=Score.courseNo AND Score.studentNo=Student.studentNo 查找選修“計算機原理”課程的同學，在WHERE子句中必須包括選擇條件courseName=計算機原理,3.3.1 等值與非等值連接,本查詢語句為： SELECT a.studentNo, studentName FROM Student a, Course b, Score c WHERE b.courseNo=c.courseNo AND c.studentNo=a.studentNo AND b.courseName=計算機原理 本例使用了元組變量，其連接條件為： b.courseNo=c.courseNo AND c.studentNo=a.studentNo,3.3.1 等值與非等值連接,自然連接 SQL不直接支持自然連接，完成自然連接的方法是在等值連接的基礎上消除重復列 例3.30 實現(xiàn)成績表Score和課程表Course的自然連接。 SELECT studentNo, a.courseNo, score, courseName, creditHour, courseHour, priorCourse FROM Score a, Course b WHERE a.courseNo=b.courseNo 本例課程編號在兩個關系中同時出現(xiàn)，但在SELECT子句中僅需出現(xiàn)1次，因此使用a.courseNo，也可以使用b.courseNo。其他列名是唯一的，不需要加上元組變量,3.3.2 自表連接,若某個表與自己進行連接，稱為自表連接 例3.31 查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應成績，并按學號排序輸出。 學生姓名在學生表中，F(xiàn)ROM子句必須包含學生表(取別名為a) 可以考慮兩個成績表，分別記為b和c b表用于查詢選修了編號為“001”課程的同學 c表用于查詢選修了編號為“002”課程的同學 FROM子句還必須包含兩個成績表b和c，且在WHERE子句中包含兩個選擇條件： b.courseNo=001 AND c.courseNo=002,3.3.2 自表連接,一方面，成績表b與成績表c在學號上做等值連接(自表連接)，如果連接成功，表示學生同時選修了編號為“001”和“002”的課程 另一方面，學生表與成績表b (或成績表c)在學號上做等值連接。WHERE子句包含兩個連接條件： b.studentNo=c.studentNo AND a.studentNo=b.studentNo 本查詢語句為： SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score FROM Student a, Score b, Score c WHERE b.courseNo=001 AND c.courseNo=002 AND a.studentNo=b.studentNo AND b.studentNo=c.studentNo ORDER BY a.studentNo 本查詢結果與例3.29相同 在該查詢中，F(xiàn)ROM子句后面包含了兩個參與自表連接的成績表Score，必須定義元組變量加以區(qū)分 自表連接的條件是b.studentNo=c.studentNo,3.3.2 自表連接,例3.32 在學生表Student中查找與“李宏冰”同學在同一個班的同學姓名、班級編號和出生日期。 SELECT a.studentName, a.classNo, a.birthday FROM Student a, Student b WHERE b.studentName=李宏冰 AND a.classNo=b.classNo,3.3.2 練習,練習：實驗（10）（13) 習題3.9查詢沒有借書的讀者姓名 實驗（21）查找每個人的銷售記錄，要求顯示銷售員的編號、姓名、性別、商品名稱、數(shù)量、單擊、金額、和銷售日期（如何通過查詢結果看到?jīng)]有銷售記錄的銷售員？）,3.3.3 外連接,左外連接 Select From a LEFT OUTER JOIN b ON a.屬性=b.屬性（連接條件）,目 錄,嵌套子查詢,SQL概述,簡單查詢,連接查詢,聚合查詢,集合運算,SQL查詢一般格式,SQL數(shù)據(jù)定義語言,SQL數(shù)據(jù)更新語言,視圖,3.4 嵌套子查詢,在SQL查詢中，一個SELECT-FROM-WHERE查詢語句稱為一個查詢塊 將一個查詢塊嵌入到另一個查詢塊的WHERE子句或HAVING子句(見3.5節(jié))中，稱為嵌套子查詢 子查詢的結果是集合, 因此使用子查詢是集合成員的檢查 如判斷元組是否屬于某個集合，集合的比較運算，以及測試是否為空集等 具體表現(xiàn)在如下幾個方面： 元素與集合間的屬于關系 集合之間的包含和相等關系 集合的存在關系 元素與集合元素之間的比較關系,3.4 嵌套子查詢,SQL允許多層嵌套子查詢，但在子查詢中，不允許使用ORDER BY子句，該子句僅用于最后的輸出結果排序 嵌套查詢分為相關子查詢和非相關子查詢 非相關子查詢指子查詢的結果不依賴于上層查詢 相關子查詢指當上層查詢的元組發(fā)生變化時，其子查詢必須重新執(zhí)行 3.4.1 使用IN的子查詢 3.4.2 使用比較運算符的子查詢 3.4.3 使用存在量詞EXISTS的子查詢,3.4.1 使用IN的子查詢,例3.37 查詢選修過課程的學生姓名。 本例查詢的含義是： 在學生表Student中，將學號出現(xiàn)在成績表Score中(表明該學生選修過課程)的學生姓名查詢出來 SELECT studentName FROM Student WHERE Student.studentNo IN (SELECT Score.studentNo FROM Score) 在本例中，WHERE子句用于檢測元素與集合間的屬于關系 其中Student.studentNo為元素，IN為“屬于” 嵌套語句“SELECT Score.studentNo FROM Score”的查詢結果為選修過課程的所有學生的學號集合 該嵌套SELECT語句稱為子查詢,3.4.1 使用IN的子查詢,該查詢屬于非相關子查詢，其查詢過程為： (1) 從Score表中查詢出學生的學號studentNo，構成一個中間結果關系r； (2) 從Student表中取出第一個元組t； (3) 如果元組t的studentNo屬性的值包含在中間結果關系r中（即t.studentNor），則將元組t的studentName屬性的值作為最終查詢結果關系的一個元組；否則丟棄元組t； (4) 如果Student表中還有元組，則取Student表的下一個元組t，并轉第(3)步；否則轉第(5)步； (5) 將最終結果關系顯示出來,3.4.1 使用IN的子查詢,該查詢的執(zhí)行過程可以通過圖3-23來表示,3.4.1 使用IN的子查詢,例3.38 查找選修過課程名中包含“系統(tǒng)”的課程的同學學號、姓名和班級編號。 SELECT studentNo, studentName, classNo FROM Student WHERE studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName LIKE %系統(tǒng)% ) ) WHERE子句中的IN可以實現(xiàn)多重嵌套，本例是一個三重嵌套的例子，該查詢的執(zhí)行過程可以通過圖3-24來表示,3.4.1 使用IN的子查詢,3.4.1 使用IN的子查詢,該查詢也屬于非相關子查詢 使用IN的非相關子查詢的查詢過程歸納如下： 首先執(zhí)行最底層的子查詢塊，將該子查詢塊的結果作為中間關系； 執(zhí)行上一層(即外一層)查詢塊，對于得到的每個元組，判斷該元組是否在它的子查詢結果中間關系中： 如果在，取出該元組中的相關屬性作為最終輸出結果(或該查詢塊的查詢結果中間關系)的一個元組 否則舍棄該元組 如果已經(jīng)執(zhí)行完最上層查詢塊，則將最終結果作為一個新關系輸出；否則返回第(2)步重復執(zhí)行,3.4.1 使用IN的子查詢,例3.39 查找同時選修過“計算機原理”和“高等數(shù)學”兩門課程的同學學號、姓名以及該同學所選修的所有課程的課程名和相應成績，按學號(升序)、成績(降序)排序輸出。 分析： 需查詢同時選修過“計算機原理”和“高等數(shù)學”兩門課程的同學學號、姓名以及該同學所選修的所有課程的課程名和相應成績，在SELECT子句中必須包含studentNo、studentName、courseName和score四個屬性 學號、姓名在學生表中，課程成績在成績表中，課程名在課程表中，在FROM子句中必須包含學生表、課程表和成績表，分別為這三張表取元組變量a、b、c 學生表、成績表和課程表需做連接操作，在WHERE子句中必須包含連接條件： a.studentNo=c.studentNo AND b.courseNo=c.courseNo,3.4.1 使用IN的子查詢,要查詢同時選修過“計算機原理”和“高等數(shù)學”兩門課程的同學，在WHERE子句中必須包含如下的選擇條件： 對于學生表，其學號必須是選修過“計算機原理”課程的學號，使用子查詢： a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=計算機原理 ) ) 對于學生表，其學號還必須是選修過“高等數(shù)學”課程的學號，使用子查詢： a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=高等數(shù)學 ) ) 這兩個子查詢必須同時滿足，使用AND邏輯運算符,3.4.1 使用IN的子查詢,本查詢語句為： SELECT a.studentNo, studentName, courseName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=計算機原理 ) ) AND a.studentNo IN ( SELECT studentNo FROM Score WHERE courseNo IN ( SELECT courseNo FROM Course WHERE courseName=高等數(shù)學 ) ) ORDER BY a.studentNo, score DESC,3.4.1 使用IN的子查詢,該查詢也可以表示為如下形式： SELECT a.studentNo, studentName, courseName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND a.studentNo IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=計算機原理 ) AND a.studentNo IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=高等數(shù)學 ) ORDER BY a.studentNo, score DESC,3.4.1 使用IN的子查詢,練習2.8（7）查找至少選修了一門其直接先修課程編號為CS012的課程的學生學號和姓名 實驗（12）（23）,3.4.1 使用IN的子查詢,例3.40 查找同時選修過“計算機原理”和“高等數(shù)學”兩門課程的同學學號、姓名以及所選修的這兩門課程的課程名和相應成績，按學號(升序)、成績(降序)排序輸出 分析： 只查詢該同學所選修的這兩門課程的課程名和相應成績，在WHERE子句中還必須包含選擇條件：課程名稱必須是“計算機原理”或“高等數(shù)學”，即 courseName=高等數(shù)學 OR courseName=計算機原理,3.4.1 使用IN的子查詢,本查詢語句為： SELECT a.studentNo, studentName, courseName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND a.studentNo IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=計算機原理 ) AND a.studentNo IN ( SELECT studentNo FROM Score x, Course y WHERE x.courseNo=y.courseNo AND courseName=高等數(shù)學 ) AND ( courseName=高等數(shù)學 OR courseName=計算機原理 ) ORDER BY a.studentNo, score DESC 請將例3.39、例3.40的查詢要求與查詢語句的實現(xiàn)形式與例3.29、例3.31進行比較。,3.4.2 使用比較運算符的子查詢,元素與集合元素之間還存在更為復雜的關系，如比較關系，常用到謂詞ANY(或SOME)和ALL ANY表示子查詢結果中的某個值 ALL表示子查詢結果中的所有值,3.4.2 使用比較運算符的子查詢,注意： 如果子查詢中的結果關系僅包含一個元組，則可將ALL和ANY去掉，直接使用比較運算符 ANY也可以用SOME替代,3.4.2 使用比較運算符的子查詢,例3.41 查詢所選修課程的成績大于所有“002”號課程成績的同學學號及相應課程的課程號和成績。 SELECT studentNo, courseNo, score FROM Score WHERE scoreALL ( SELECT score FROM Score WHERE courseNo=002 ),3.4.2 使用比較運算符的子查詢,例3.42 查詢年齡小于“計算機科學與技術07-01班”某個同學年齡的所有同學的學號、姓名和年齡。 SELECT studentNo, studentName, year(getdate()-year(birthday) AS age FROM Student WHERE year(getdate()-year(birthday)ANY ( SELECT year(getdate()-year(birthday) FROM Student a, Class b WHERE className=計算機科學與技術07-01班 AND a.classNo=b.classNo ) 本查詢執(zhí)行過程是： 首先執(zhí)行子查詢，找出“計算機科學與技術07-01班”同學的年齡集合 然后在Student表中將年齡小于該集合中某個同學年齡的所有同學查找出來。 在比較運算符中，=ANY等價于IN謂詞，!=ALL等價于NOT IN謂詞,3.4.3 使用存在量詞EXISTS的子查詢,SQL查詢提供量詞運算 量詞有兩種： 一是存在量詞 二是全稱量詞 在離散數(shù)學中，全稱量詞可用存在量詞替代 SQL僅提供存在量詞的運算，使用謂詞EXISTS表示 全稱量詞轉化通過NOT EXISTS謂詞來實現(xiàn) WHERE子句中的謂詞EXISTS用來判斷其后的子查詢的結果集合中是否存在元素 謂詞EXISTS大量用于相關子查詢中,3