1、2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,1,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,3.1 SQL概述 3.2 學(xué)生-課程數(shù)據(jù)庫 3.3 數(shù)據(jù)查詢 3.4 數(shù)據(jù)定義 3.5 數(shù)據(jù)更新 3.6 視圖 ,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,2,3.1 SQL概述,SQL（Structured Query Language） 是結(jié)構(gòu)化查詢語言。它是用于對存放在計算機(jī)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行組織、管理和檢索的工具，是一種用于和關(guān)系數(shù)據(jù)庫進(jìn)行交互通訊的計算機(jī)語言。 SQL 語言是一個唯一通過了國際標(biāo)準(zhǔn)的關(guān)系數(shù)據(jù)庫語言，該語言得到各大數(shù)據(jù)庫廠商的支持，在各種DBMS產(chǎn)品中都能使用。,2

2、020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,3,SQL語言的發(fā)展及標(biāo)準(zhǔn)化,1974年由Boyce和Chamberlin提出。 1986年 美國國家標(biāo)準(zhǔn)局ANSI 公布了SQL語言標(biāo)準(zhǔn)SQL86，隨后ISO通過了該標(biāo)準(zhǔn)。 1989年 ISO公布了SQL89，加進(jìn)了完整性等內(nèi)容。 1992年 ISO公布了SQL92，即 SQL2，分初級、中級、完全三級，加進(jìn) BIT 數(shù)據(jù)類型，支持對遠(yuǎn)程數(shù)據(jù)庫的訪問。 1999年 ISO發(fā)布了標(biāo)準(zhǔn)化文件ISO/IEC9075：數(shù)據(jù)庫語言SQL(1999)，即SQL3，增加了面向?qū)ο蠊δ?，支持用戶自定義函數(shù)。 2003年 ISO發(fā)布了SQL2003。,202

3、0/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,4,SQL的主要特點(diǎn),集DDL、DML、DCL為一體（一體化） 高度非過程化 面向集合的操作方式 兩種使用方式（自含式和嵌入式），統(tǒng)一的語法結(jié)構(gòu) 語言簡捷，功能豐富，易學(xué)易用,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,5,SQL的功能,數(shù)據(jù)定義 CREATE、ALTER、DROP 數(shù)據(jù)查詢 SELECT 數(shù)據(jù)更新 INSERT、UPDATE、DELETE 數(shù)據(jù)控制 COMMIT、ROLLBACK GRANT、REVOKE、DENY,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,6,SQL的基本概念,SQL支持關(guān)系數(shù)據(jù)庫三級模式

4、結(jié)構(gòu),2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,7,SQL的基本概念（續(xù)）,基本表 本身獨(dú)立存在的表 SQL中一個關(guān)系就對應(yīng)一個基本表 一個(或多個)基本表對應(yīng)一個存儲文件 一個表可以帶若干索引 存儲文件 邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式 物理結(jié)構(gòu)是任意的，對用戶透明 視圖 從一個或幾個基本表導(dǎo)出的表 數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù) 視圖是一個虛表 用戶可以在視圖上再定義視圖,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,8,3.2 學(xué)生-課程數(shù)據(jù)庫,學(xué)生-課程模式 S-T : 學(xué)生表： Student ( Sno,Sname,Ssex,Sage,Sdept

5、 ) 課程表： Course ( Cno,Cname,Cpno,Ccredit ) 學(xué)生選課表： SC ( Sno,Cno,Grade ),2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,9,Student表,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,10,Course表,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,11,SC表,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,12,3.4 SQL的數(shù)據(jù)查詢,【語句格式】 SELECT ALL | DISTINCT select_list INTO new_table_name FROM table_sourc

6、e WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC COMPUTE clause,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,13,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,14,3.3.1 簡單查詢,目標(biāo)列查詢 條件查詢 排序查詢,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,15,簡單查詢1：目標(biāo)列查詢,語法 SELECT ALL | DISTINCT select_list FROM

7、 table_source 目標(biāo)列select_list的形式 屬性名（列名） * 屬性和常數(shù)組成的算術(shù)表達(dá)式,字符串常數(shù) 別名 DISTINCT 屬性名,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,16,查詢指定列,例1 查詢?nèi)w學(xué)生的學(xué)號與姓名。 SELECT Sno, Sname FROM Student; 例2 查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。 SELECT Sname, Sno, Sdept FROM Student;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,17,查詢?nèi)苛?方法1：在SELECT關(guān)鍵字后面列出所有列名 方法2：將指定為 * 例3 查詢?nèi)w

8、學(xué)生的詳細(xì)記錄。,SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; 或 SELECT * FROM Student;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,18,例4 查詢?nèi)w學(xué)生的姓名及其出生年份。,查詢經(jīng)過計算的值,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,19,查詢經(jīng)過計算的值（續(xù)）,例5 查詢?nèi)w學(xué)生的姓名、出生年份和所在系，要求用小寫字母表示所有系名。,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,20,使用列別名改變查詢結(jié)果的列標(biāo)題,SELECT Sname 姓名, 2009-Sage 出生年份, IS

9、LOWER(Sdept) 系部 FROM Student; 輸出結(jié)果： 姓名 出生年份 系部 - - - 李勇 1989 cs 劉晨 1990 is 王敏 1991 ma 張立 1990 is,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,21,消除取值重復(fù)的行,如果沒有指定DISTINCT關(guān)鍵詞，則缺省為ALL。 例6 查詢選修了課程的學(xué)生學(xué)號。 SELECT Sno FROM SC; 等價于： SELECT ALL Sno FROM SC;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,22,消除取值重復(fù)的行（續(xù)）,指定DISTINCT關(guān)鍵詞，去掉結(jié)果表中重復(fù)的行。 SEL

10、ECT DISTINCT Sno FROM SC；,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,23,簡單查詢2：條件查詢（WHERE子句）,語法 SELECT ALL|DISTINCT select_list FROM table_source WHERE search_condition 功能 比較測試（=，=，!，!） 復(fù)合條件測試（NOT、AND、OR） 范圍測試（BETWEENAND、NOT BETWEENAND ） 集合測試（IN、NOT IN） 模式匹配測試（LIKE、NOT LIKE） 空值測試（IS NULL、IS NOT NULL）,2020/6/30,第三章 關(guān)

11、系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,24,(1) 比較測試,例7查詢計算機(jī)科學(xué)系全體學(xué)生的名單。 SELECT Sname FROM Student WHERE Sdept= CS ; 例8查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。 SELECT Sname, Sage FROM Student WHERE Sage 20 ; 例9查詢考試成績有不及格的學(xué)生的學(xué)號。 SELECT DISTINCT Sno FROM SC WHERE Grade，=，）。 若內(nèi)層查詢返回多值時，可與ANY或ALL謂詞配合使用。,例41找出每個學(xué)生超過他選修課程平均成績的課程號。,相關(guān)子查詢,SELECT Sno, Cno

12、 FROM SC x WHERE Grade = (SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,59,3. 帶有ANY或ALL謂詞的子查詢,謂詞語義：ANY 某個值；ALL 所有值 需要配合使用比較運(yùn)算符 ANY 大于子查詢結(jié)果中的某個值 ALL 大于子查詢結(jié)果中的所有值 = ANY 大于等于子查詢結(jié)果中的某個值 = ALL 大于等于子查詢結(jié)果中的所有值 ）ANY 不等于子查詢結(jié)果中的某個值 !=（或）ALL 不等于子查詢結(jié)果中的任何一個值,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言S

13、QL,60,例42 查詢其他系中比計算機(jī)科學(xué)系某一學(xué)生年齡小的學(xué)生姓名和年齡。 SELECT Sname, Sage FROM Student WHERE Sage CS ; /*父查詢塊中的條件 */,用集合函數(shù)實(shí)現(xiàn)： SELECT Sname，Sage FROM Student WHERE Sage CS ;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,61,例43 查詢其他系中比計算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。 SELECT Sname, Sage FROM Student WHERE Sage CS ;,用集合函數(shù)實(shí)現(xiàn)： SELECT Sname, Sage F

14、ROM Student WHERE Sage CS ;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,62,4. 帶有EXISTS謂詞的子查詢,1） EXISTS謂詞 存在量詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值 若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值 由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用* ，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無實(shí)際意義。 2） NOT EXISTS謂詞 若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值 若內(nèi)層

15、查詢結(jié)果為空，則外層的WHERE子句返回真值,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,63,例44查詢所有選修了1號課程的學(xué)生姓名。 用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1) ; 用連接查詢 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,64,帶有EXISTS謂詞的子查詢(續(xù)),不同形

16、式的查詢間的替換 一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換。 所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換。 用EXISTS / NOT EXISTS實(shí)現(xiàn)全稱量詞 (難點(diǎn)) SQL語言中沒有全稱量詞 （For all） 可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞： (x)P ( x( P),2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,65,例46 查詢選修了全部課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS ( SELECT

17、* FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = Course.Cno ) ) ;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,66,3.3.5 集合查詢,集合操作的種類 并操作 UNION 交操作 INTERSECT（SQLServer不支持） 差操作 EXCEPT （SQLServer不支持） 參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對應(yīng)項的數(shù)據(jù)類型也必須相同,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,67,并操作 UNION,例48 查詢計算機(jī)科學(xué)系

18、的學(xué)生及年齡不大于19歲的學(xué)生。 SELECT * FROM Student WHERE Sdept= CS SELECT * FROM Student WHERE Sage=19;,SELECT * FROM Student WHERE Sdept= CS OR Sage=19 ;,UNION,UNION：將多個查詢結(jié)果合并起來時，系統(tǒng)自動去掉重復(fù)元組。 UNION ALL：將多個查詢結(jié)果合并起來時，保留重復(fù)元組。,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,68,例49 查詢選修了課程1或者選修了課程2的學(xué)生。 SELECT Sno FROM SC WHERE Cno = 1 U

19、NION SELECT Sno FROM SC WHERE Cno = 2 ;,SELECT Sno FROM SC WHERE Cno = 1 OR Cno = 2 ;,SELECT DISTINCT Sno FROM SC WHERE Cno = 1 OR Cno = 2;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,69,交操作 INTERSECT,例50 查詢計算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集 SELECT * FROM Student WHERE Sdept=CS INTERSECT SELECT * FROM Student WHERE Sage=19;,S

20、ELECT * FROM Student WHERE Sdept= CS AND Sage80;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,108,視圖的修改和刪除,修改視圖 ALTER VIEW view_name(column ,.n) AS Select_statement WITH CHECK OPTION 刪除視圖 DROP VIEW view_name,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,109,三、查詢視圖,系統(tǒng)執(zhí)行視圖查詢時首先把它轉(zhuǎn)換成等價的對基本表的查詢，然后執(zhí)行修改了的查詢。即當(dāng)查詢是對視圖時，系統(tǒng)首先從數(shù)據(jù)字典中取出該視圖的定義，然后把定義

21、中的子查詢和視圖查詢語句結(jié)合起來，形成一個修正的查詢語句。這一轉(zhuǎn)換過程稱為視圖消解（View Resolution）。,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,110,在S_4視圖中查詢平均成績在90分以上的學(xué)生學(xué)號和平均成績,在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。,視圖消解（查詢）,SELECT Sno,Sage FROM S_3 WHERE Sage =90;,CREATE VIEW S_3 AS SELECT Sno,Sname,Sage FROM Student WHERE Ssex=女;,CREATE VIEW S_4 (學(xué)號,平均分) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;,2020/6/30,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,111,四、更新視圖,視圖更新方法（INSERT、DELETE、UPDATE） 對視圖的更新最終要轉(zhuǎn)換成對基本表的更新。 但是，在關(guān)系數(shù)據(jù)庫中，并非所有的視圖都是可更新的，也就是說，有些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對基本表的更新。 對于一個可更新的視圖來說，D