【數(shù)據(jù)庫系統(tǒng)概論】關(guān)系數(shù)據(jù)庫標準語言——SQL

1、第五章 關(guān)系數(shù)據(jù)庫標準語言SQL SQL（Structured Query Language）是1974年由Boyce和Chamberlin提出的。在IBM公司San Jose Research Laboratory 研制的System R上實現(xiàn)了這種語言。由于它功能豐富、使用方式靈活、語言簡潔易學等突出優(yōu)點，在計算機工業(yè)界和計算機用戶中倍受歡迎并深深扎根。1986年10月，美國國家標準局（ANSI）的數(shù)據(jù)庫委員會X3H2批準了SQL作為關(guān)系數(shù)據(jù)庫語言的美國標準。同年公布了標準SQL文本。此后不久，國際標準化組織(ISO)也作出了同樣的決定。,本章介紹SQL語言, 并進一步討論關(guān)系數(shù)據(jù)庫的基本

2、概念. SQL標準文本和實施的SQL語言相比,后者常常作了許多必要的擴充, 因此我們選用IBM公司在DB2上的SQL語言.它基本上和標準SQL一致,而在對關(guān)系模型的符合程度上比標準SQL更好.,5.1 SQL 概貌及其特點 SQL(Structured Query Language)的英文名稱是結(jié)構(gòu)查詢語言。實際上它的功能包括查詢(Query),操縱(Manipulation),定義(Definition)和控制(Control)四個方面,是一個綜合的、通用的、功能極強的關(guān)系數(shù)據(jù)庫語言。 一、SQL的主要特點,1. 一體化的特點 2. 兩種使用方式,統(tǒng)一的語法結(jié)構(gòu) 3. 高度非過程化 4. 語

3、言簡潔,易學易用,(1).數(shù)據(jù)庫查詢 SELECT (2). 數(shù)據(jù)定義 CREATE, DROP (3). 數(shù)據(jù)操縱 INSERT, UPDATE, DELETE (5). 數(shù)據(jù)控制 GRANT, REVOKE,二. SQL語言支持關(guān)系數(shù)據(jù)庫三級模式結(jié)構(gòu)(圖5-1),SQL,View V1,View V2,Base table B1,Base table B2,Base table B3,Base table B4,Stored file S1,Stored file S2,Stored file S3,Stored file S4,外模式,模式,內(nèi)模式,用戶,圖 5-1,5.2 SQL 數(shù)據(jù)

4、定義功能 SQL的數(shù)據(jù)定義功能包括三部分:定義基本表,定義視圖和定義索引.他們是: CREATE TABLE CREATE VIEW CREATE INDEX DROP TABLE DROP VIEW DROP INDEX ALTER TABLE,5.2.1 基本表定義和修改 一. 定義基本表的語句 1. 格式: CREATE TABLE 表名(列名1 類型NOT NULL ,列名1 類型NOT NULL) 其它參數(shù);,2.幾點說明 (1).任選項“其它參數(shù)”是與物理存儲有關(guān)的參數(shù),隨具體系統(tǒng)而異. (2). DB2支持的數(shù)據(jù)類型 . INTEGER 全字長(31 bits)的二進制整數(shù). .

5、 SMALLINT 半字長(15 bits)的二進制整數(shù) . DECIMAL(p,q) 壓縮十進制數(shù),共p位,小數(shù)點后有q位. . FLOAT 雙字長的浮點數(shù). . CHAR(n) 長度為n的定長字符串. . VARCHAR(n) 變長字符串,最大長度為n. (3). SQL 支持空值的概念,3.舉例: CHEATE TABLE S(S# CHAR(3) NOT NULL, SN CHAR(15) , SD CHAR(15) , SA SMALLINT);,二. 修改基本表定義的語句 1. 格式: ALTER TABLE 表名 ADD 列名 類型; 2.舉例 ALTER TABLE S ADD

6、 SEX SMALLINT;,三. 刪除基本表的語句 1. 格式: DROP TABLE 表名; 2.舉例 ALTER TABLE S ;,5.2.2 索引的建立和刪除 通常索引的建立和刪除由DBA(Dada Base Administrator)或表的主人負責.,一.建立索引的語句 1.格式 CREATE UNIQUE INDEX 索引名 ON 基本表名(列名次序,列名次序) 其它參數(shù);,2.舉例: CREATE UNIQUE INDEX XSNO ON S(S#) CREATE UNIQUE INDEX XCNO ON C(C#) CREATE UNIQUE INDEX XSC ON SC

7、(S# ASC,C# DESC),二.刪除索引的語句 1.格式 DROP INDEX 索引名; 2.舉例 DROP INDEX XSNO;,5.2.3 小 結(jié) 在關(guān)系數(shù)據(jù)庫中允許DBA隨時使用SQL數(shù)據(jù)定義語句來定義基本表、索引和視圖，系統(tǒng)十分方便靈活。 在非關(guān)系系統(tǒng)中，數(shù)據(jù)庫的模式一經(jīng)建立再要加入新的記錄型或修改老的記錄型是十分費事的工作。 而象DB2這樣的SQL關(guān)系數(shù)據(jù)庫系統(tǒng)，增刪改基本表、索引和視圖都不必停止系統(tǒng)的其它處理，不會影響其它用戶對數(shù)據(jù)庫的正常使用。,5.3 SQL 數(shù)據(jù)操縱功能 SQL的數(shù)據(jù)操縱功能包括SELECT, INSERT, DELETE,和UPDATE 四個語句,即

8、檢索和更新(包括增、刪、改)兩部分功能.,5.3.1 SQL 查詢語句 1. 查詢語句的格式 SELECT 目表列 FROM 基本表(或視圖) WHERE 條件表達式 GROUP BY 列名1HAVING 內(nèi)部函數(shù)表達式 ORDER BY 列名2 ;,ASC DESC,2. 功能,5.3.1.1 簡單查詢 例1. 求數(shù)學系學生的學號、姓名。 SELECT S#,SN FROM S WHERE SD=MA;,例2. 求選修了課程的學生的學號。 SELECT DISTINCT S# FROM SC;,例3. 求全體學生的詳細信息。 SELECT * FROM S;,例4. 查詢經(jīng)過計算的值。求學生

9、學號和出生年份. SELECT S#,2000-SA FROM S;,例5. 要求排序的查詢. 求選修C1課程的學生的學號和得分,結(jié)果按分數(shù)降序排列。 SELECT S#,G FROM SC WHERE C#=C1 ORDER BY G DESC;,例6. 使用BETWEEN的查詢. 求年齡在20歲與22歲之間(包括20歲和22歲)的學生學號和年齡. SELECT S#,SA FROM S WHERE SA BETWEEN 20AND 22;,例7. 使用IN的查詢. 求在下列各系的學生：MA(數(shù)學系),CS(計算機科學系). SELECT * FROM S WHERE SD IN(MA,CS

10、);,謂詞IN實際上是一系列謂詞OR的縮寫.等價的查詢?yōu)? SELECT * FROM S WHERE SD=MA OR SD=CS;,求不是數(shù)學系、計算機科學系的學生。 SELECT * FROM S WHERE SD NOT IN(MA,CS);,例8. 使用LIKE的查詢. 求姓名是以字母D打頭的學生。 SELECT * FROM S WHERE SN LIKE D%;,LIKE 謂詞的一般形式為: 列名 LIKE 字符串常數(shù) *說明: 1.列名的類型必須是字符串或變長字符串. 2.字符串常數(shù)中字符定含義: . 字符“_”,表示任意的單個字符; . 字符“%”,表示任意字符串(長度可以為

11、零); . 所有其它字符只代表自己.,例8. 涉及空值NULL的查詢. 求缺少學習成績的學生學號合課程號。 SELECT S#,C# FROM SC WHERE G IS NULL;,涉及空值的謂詞的一般形式是: 列名 IS NOT NULL. 注意: 不能寫成: 列名=NULL 或 列名=NULL,5.3.1.2 連接查詢 定義: 若查詢涉及兩個以上的表,則稱之為連接查詢.,例10. 求學生以及他選修課程的課程號碼和成績。 SELECT S.*, SC.* FROM S, SC WHERE S.S# =SC.S#;,幾點說明: (1). 連接字段的類型必須是可比的,但不必相同.多數(shù)是相同的.

12、 (2). 連接謂詞中的比較符可以是: =, , =, =, =六種. (3). 等值連接與自然連接的概念.,例11. 自然連接查詢. SELECT S.S#, S.SN,S.SD,S.SA,SC.C#,SC.G FROM S, SC WHERE S.S# =SC.S#;,若字段名在各表中是唯一的,可以把字段名前的表名去掉,否則必須加上表名作為前綴,以免引起混淆.例11可寫成:,SELECT S.S#, SN,SD,SA,C#,G FROM S, SC WHERE S.S# =SC.S#;,例12. 帶有其他條件的連接查詢. 求選修C1課程且成績?yōu)锽以上的學生及成績.,SELECT S.S#,

13、 SN,SD,SA,G FROM S, SC WHERE S.S# =SC.S# AND SC.C# =C1 AND (SC.G=A OR SC.G=B);,例13. 求每一門課程的間接先行課(即先行課的先行課).,SELECT FIRST.C# SECOND.PC# FROM C FIRST C SECOND WHERE FIRST.PC# =SECOND.C# ; 此例中的FIRST和SECOND為引入的別名;,例14. 求選修了課程名為J的學生的學號和姓名. SELECT S#, SN FROM S, SC WHERE S# IN SELECT S# FROM SC WHERE C# I

14、N SELECT C# FROM C WHERE CN=J;,5.3.1.3 嵌 套 查 詢 嵌套查詢亦稱為子查詢.嵌套查詢是指一個SELECT-FROM-WHERE查詢塊嵌入另一個查詢塊之中.SQL中允許多層嵌套.,例15. 使用存在量詞EXISTS 和 NOT EXISTS的嵌套查詢. 求選學了C2課程的學生姓名.,SELECT SN FROM S WHERE EXISTS (SELECT * FROM SC WHERE S# =SC.S# AND C# =C2);,例16.使用NOT EXISTS的嵌套查詢.求不選修C3課程的學生姓名. SELECT SN FROM S WHERE NO

15、T EXISTS (SELECT * FROM SC WHERE S# =SC.S# AND C# =C3);,例17. 求選修了全部課程的學生姓名.,SELECT SN FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE S# =S.S# AND C# =C.C#);,例19. 使用UNION 的查詢. 求計算機科學系的學生以及年齡小于18歲的學生. SELECT * FROM S WHERE SD=CS SELECT * FROM S WHERE SA18;,5.3.1.4 庫

16、函 數(shù) SQL提供了6種庫函數(shù).其功能如下: COUNT 對一列中的值計算個數(shù) COUNT(*) 計算記錄個數(shù) SUM 求某一列值的總和 AVG 求某一列值的平均值 MAX 求某一列值中的最大值 MIN 求某一列值中的最小值,例20. 在SELECT子句中使用庫函數(shù). (1). 求學生總?cè)藬?shù). SELECT COUNT(*) FROM S;,(2). 求選修了課程的學生人數(shù). SELECT COUNT(DISTINCT S#) FROM SC;,(3). 求計算機科學系學生的平均年齡. SELECT AVG(SA) FROM S WHERE SD=CS;,例 21. GROUP BY 的使用

17、求課程號及選修該課程的學生人數(shù). SELECT C#, COUNT(S#) FROM SC GROUP BY C# ;,例 22. HAVING 的使用 求選修課程超過3門的學生學號. SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)3 ;,5.3.2 SQL 更新語句 SQL更新語句包括修改、刪除和插入三類語句。,一、修改(UPDATE)(也稱為更新) 1. 語句的一般格式: UPDATE 表名 SET 字段=表達式,字段=表達式 WHERE 謂詞; 2. 功能 3. 舉例:,例23. 單記錄修改.把學生S1的姓名改為WANG PING. UPDAT

18、E S SET SN=WANG PING WHERE S#=S1;,例24 多記錄修改. 把所有學生的年齡加1. UPDATE S SET SA=SA+1;,例25 具有子查詢的更新.把數(shù)學系全體學生的成績置零. UPDATE S SET G=0 WHERE MA= (SELECT SD FROM S WHERE S.S# =SC.S#);,例26. 多個表的更新. UPDATE S SET S# =S9 WHERE S# = S2; UPDATE SC SET S# =S9 WHERE S# = S2;,二、刪除語句(DELETE) 1. 語句的一般格式: DELETE FROM 表名 WH

19、ERE 謂詞; 2. 功能 3. 舉例:,例27. 單記錄刪除.把學生S9刪除. DELETE FROM S WHERE S# =S9; 執(zhí)行刪除操作也可能產(chǎn)生破壞完整性的情況.,例28 多記錄刪除.刪除所有的學生選課記錄. DELETE FROM SC;,例29. 帶有子查詢的刪除.刪除計算機科學系全體學生的選課記錄. DELETE FROM SC WHERE CS = (SELECT SD FROM S WHERE S.S# =SC.S#);,三、插入語句(INSERT) 1. 語句的一般格式: INSERT INTO 表名(字段名,字段名) VALUES (常量,常量); 或 INSER

20、T INTO 表名(字段名,字段名) 子查詢; 2. 功能 3. 舉例:,例30 單記錄插入.把一個新學生:S10(學號),Zhang Ming(姓名), IS(信息系),18(年齡),插入表中. INSERT INTO S VALUES (S10,Zhang Ming,IS,18);,例31. 插入一個選課記錄(S15,C10,A) INSERT INTO SC(S#,C#,G) VALUES (S15,C10,A);,例32. 多記錄插入. 對每個系,求學生的平均年齡,并把結(jié)果插入數(shù)據(jù)庫中.,CREATE TABEL DEPTAGE (SDEPT CHAR(5),AVGAGE SMALLI

21、NT); INSERT INTO DEPTAGE (SDEPT,AVGAGE) SELECT SD,AVG(SA) FROM S GROUP BY SD;,5.4 視 圖 視圖(View)是從一個或幾個基本表(或視圖)導(dǎo)出的表.,5.4.1 視圖的定義 1. SQL建立視圖的語句格式: CREATE VIEW 視圖名(字段名,字段名) AS 子查詢 WITH CHECK OPTION;,2.舉例: 例1. 建立計算機科學系的學生視圖. CREATE VIEW CS_S AS SELECTS#,SN,SA FROM S WHERE SD=CS;,例2. 把學生的學號及它的平均成績定義為一個視圖.

22、 CREATE VIEW S_G(S#,GAVG) AS SELECTS#,AVG(G) FROM SC GROUP BY S# ;,3.刪除視圖的語句格式 DROP VIEW 視圖名; 例如: DROP VIEW CS_S;,注意:視圖的刪除就是從數(shù)據(jù)字典中刪除.由此視圖導(dǎo)出的其它視圖也將自動刪除. 若導(dǎo)出此視圖的基本表刪除了,則此視圖也將自動刪除.,5.4.2 視圖的查詢 視圖定義后,用戶可以如同基本表那樣對視圖查詢.,舉例: SELECT S#,SA FROM CS_S WHERE SA20;,SELECT S#,SA FROM S WHERE SD=CS AND SA20;,系統(tǒng)執(zhí)行此

23、查詢時首先把它轉(zhuǎn)換成等價的對基本表的查詢.然后執(zhí)行修改了的查詢.本例的修正后的查詢語句為:,5.4.3 視圖的更新 對視圖的更新最終要轉(zhuǎn)換成對基本表的更新. 1. 修 改,UPDATE CS_S SET SN=WANG PING WHERE S# =S1;,將轉(zhuǎn)換成對基本表S的更新: UPDATE S SET SN=WANG PING WHERE SD=CS AND S# =S1;,2. 插入 INSERT INTO CS_S VALUES (S12, Yan Xi,19);,轉(zhuǎn)換成對基本表的插入 INSERT INTO S VALUES (S12, Yan Xi,CS,19);,在關(guān)系數(shù)據(jù)庫

24、中,并非所有的視圖都是可更新的.如: UPDATE S_G SET GAVG=90 WHERE S# =S1;,課間休息,注意時間,3. 視圖分類: (1). 行列子集視圖(可更新的視圖). (2). 理論上可更新的視圖. (3). 不可更新的視圖,4. 在BD2中,只有從單個基本表導(dǎo)出的視圖才允許對它進行更新操作,并且具有以下限制: P136-137 共7條. 不可更新的視圖與不允許更新的視圖的概念,5.4.4 視圖的優(yōu)點 1. 視圖對于數(shù)據(jù)庫的重構(gòu)造提供了一定程度的邏輯獨立性.,關(guān)系數(shù)據(jù)庫中，數(shù)據(jù)庫的重構(gòu)造是不可避免的。最常見的情況是把一個表垂直地分為兩個以上的表。例如把學生關(guān)系 S(S#

25、，SN，SD，SA) 分為： SX(S#,SN,SA) SY(S#,SD),這時原表S是SX,SY的自然連接的結(jié)果,因此可以建立一個視圖S: CREATE VIEW S(S#,SN,SD,SA) AS SELECT SX.S#,SX.SN,SY.SD,SX.SA FROM SX,SY WHERE SX.S# =SY.S#;,2.簡化了用戶觀點 3. 視圖機制使不同的用戶能以不同的方式看待同一數(shù)據(jù)庫. 4.視圖機制對機密數(shù)據(jù)提供了自動的安全保護功能,5.5 SQL 數(shù)據(jù)控制功能 SQL 數(shù)據(jù)控制功能是指控制用戶對數(shù)據(jù)的存取權(quán)力. 一.DB2中授權(quán)語句的一般格式為:,GRANT 權(quán)力,權(quán)力ON 對

26、象類型 對象名 TO 用戶,用戶 WITH GRANT OPTION; 對不同類型的操作對象可有不同的操作權(quán)力,見表5-2,任選項WITH GRANT OPTION的作用是使獲得某種權(quán)力的用戶可以把權(quán)力再授予別的用戶.,二.舉例 例1. 把修改學生學號和查詢S表的權(quán)力授給王平. GRANT UPDATE(S#),SELECT ON TABLE S TO WANGPING;,例2. 把對表S,C,SC的查詢、修改插入和刪除等全部權(quán)力授予張勇、楊斌。 GRANT ALL PRIVILIGES ON TABLE S，C，SC TO ZHANG YON，YANBIN;,例3. 把對表C的查詢權(quán)力授予所

27、有用戶. GRANT SELECT ON TABLE C TO PUBLIC;,例4. 把在數(shù)據(jù)庫S-C中建立表的權(quán)力授予用戶杜明. GRANT CREATETAB ON DATABASE S-C TO DUMING;,例5. 把對表S的查詢權(quán)力授予用戶U2，并給U2有再授權(quán)的權(quán)力。 GRANT SELECT ON TABLE S TO U2 WITH GRANT OPTION；,例6. U2把查詢表S的權(quán)力授給用戶U3. GRANT SELECT ON TABLE S TO U3;,三.授予的權(quán)力可以用REVOKE語句收回，格式為： REVOKE 權(quán)力，權(quán)力ON 對象類型 對象名FROM 用

28、戶,用戶,例7. 把用戶王平修改學生學號的權(quán)力收回. REVOKE UPDATE(S#) ON TABLE S FROM WANGPING;,例8. 把用戶U2查詢S表的權(quán)力收回. REVOKE SELECT ON TABLE S FROM U2;,5.6 嵌入式 SQL 把SQL 嵌入主語言使用時必須解決三個問題: 1.區(qū)分SQL語句與主語言語句. 2.數(shù)據(jù)庫工作單元和程序工作單元之間的通信. 3.一個SQL語句原則上可產(chǎn)生或處理一組記錄,而主語言一次只能處理一個記錄,為此必須協(xié)調(diào)兩種處理方式.,5.6.1 不用游標的DML語句 一. 查詢結(jié)果為單記錄的SELECT語句 1.格式:(主語言為PL/1) EXEC SQL SELECT 目標列 INTO 主變量空值標志列 FROM 基本表(或視圖) WHERE 條件表達式 ;,2.舉例 例1. 根據(jù)主變量GIVENS#的值找到該學生的信息. EXEC SQL SELECT SN,SD,SA INTO :SNAME,:DEPT,:AGE FROM S WHERE S# =:GIVENS#;,二.