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

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