數(shù)據(jù)庫(kù)技術(shù)：SQL語(yǔ)言

1、1SQL的形式嵌入式Main().exec sql begin declare section;char co10;int id;exec sql end declare section;exec sql select company_name from customer where id = :id into co;23.1.3 SQL 語(yǔ)言特點(diǎn) 一體化 集DDL，DML，DCL于一體 單一的結(jié)構(gòu)-關(guān)系，帶來(lái)了數(shù)據(jù)操作符的統(tǒng)一 面向集合的操作方式 一次一集合 高度非過(guò)程化 用戶只需提出“做什么”，無(wú)須告訴“怎么做”，不必了解存取路徑 兩種使用方式，統(tǒng)一的語(yǔ)法結(jié)構(gòu) SQL既是自含式語(yǔ)言（用戶使

2、用），又是嵌入式語(yǔ)言（程序員使用） 語(yǔ)言簡(jiǎn)潔，易學(xué)易用33.1.3 SQL 語(yǔ)言特點(diǎn)SQL功能功能操作符操作符數(shù)據(jù)查詢數(shù)據(jù)查詢SELECT數(shù)據(jù)定義數(shù)據(jù)定義CREATE，ALTER，DROP數(shù)據(jù)操縱數(shù)據(jù)操縱INSERT，UPDATE，DELETE數(shù)據(jù)控制數(shù)據(jù)控制GRANT，REVOKE43.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.1 數(shù)據(jù)類型（SQL 92） char（n）：）：固定長(zhǎng)度的字符串 varchar（n）：）：可變長(zhǎng)字符串 int：整數(shù) smallint：小整數(shù)類型 numeric（p，d）：）：定點(diǎn)數(shù)，小數(shù)點(diǎn)左邊p位，右邊q位 real：浮點(diǎn)數(shù) double precision：雙精

3、度浮點(diǎn)數(shù) date：日期（年、月、日） time：時(shí)間（小時(shí)、分、秒） interval：兩個(gè)date或time類型數(shù)據(jù)之間的差53.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.2 基本表的定義 2. 基本表定義格式（SQL 92） 表名（ 列名 數(shù)據(jù)類型 缺省值 ，列名 數(shù)據(jù)類型 缺省值 ，（列名 ，列名 ） ， （列名 ，列名 ） 表名 （列名 ，列名 ） ，（條件）6基本表的定義示例 IIICREATE TABLE SC(S# CHAR（4）, C# CHAR（4）, GRADE SAMLLINT, PRIMARY KEY (S#，C#), FOREIGN KEY (S#)REFERENCES

4、 S(S#), FOREIGN KEY (C#)REFERENCES C(S#), CHECK(GRADE IS NULL) OR GRADE BETWEEN 0 AND 100)選課關(guān)系SC： 學(xué)生編號(hào)S#, char 4; 課程編號(hào)C#, char 4, 成績(jī)Grade smallint; 主關(guān)鍵字：S#,C#; 外鍵: 關(guān)系S的屬性S#, 關(guān)系C的屬性C#； 約束：0成績(jī) 0) )83.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.2 基本表的定義 3. 修改基本表定義（ALTER） 格式 表名 子句增加新列子句刪除列，約束 子句修改列定義 示例ALTER TABLE Prof ADD Loca

5、tion char30 ALTER TABLE Prof DROP Location93.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.2 基本表的定義 4. 刪除基本表定義（DROP） 格式 表名 示例DROP TABLE DEPT 說(shuō)明 撤消基本表后，基本表的定義、表中數(shù)據(jù)、索引、以及由此表導(dǎo)出的視圖的定義都被刪除103.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.3 索引的定義 2. 索引定義格式 索引名 表名 (列名 / , 列名/)unique（distinct）：）：唯一性索引，不允許表中不同的行在索引列上取相同值。若已有相同值存在，則系統(tǒng)給出相關(guān)信息，不建此索引。系統(tǒng)并拒絕違背唯一性的插入、更

6、新cluster：聚集索引，表中元組按索引項(xiàng)的值排序并物理地聚集在一起。一個(gè)基本表上只能建一個(gè)聚集索引asc/desc：索引表中索引值的排序次序，缺省為asc113.2 SQL數(shù)據(jù)定義功能(續(xù))CREATE INDEX 示例示例A. 使用簡(jiǎn)單索引下面的示例為 students 表的 s_id 列創(chuàng)建索引。 CREATE INDEX s_id_ind ON students (s_id)B. 使用唯一聚集索引下面的示例為 emp_pay 表的 employeeID 列創(chuàng)建索引，并且強(qiáng)制唯一性。因?yàn)橹付?CLUSTER 子句，所以該索引將對(duì)磁盤(pán)上的數(shù)據(jù)進(jìn)行物理排序。 CREATE TABLE e

7、mp_pay ( employeeID int NOT NULL, base_pay money NOT NULL, commission decimal(2, 2) NOT NULL ) CREATE UNIQUE CLUSTER INDEX employeeID_ind ON emp_pay (employeeID)123.2 SQL數(shù)據(jù)定義功能(續(xù))C. 使用簡(jiǎn)單組合索引下面的示例為 order_emp 表的 orderID 列和 employeeID 列創(chuàng)建索引。 CREATE TABLE order_emp ( orderID int IDENTITY(1000, 1), emplo

8、yeeID int NOT NULL, orderdate datetime NOT NULL DEFAULT GETDATE(), orderamount money NOT NULL ) CREATE INDEX emp_order_ind ON order_emp (orderID, employeeID)133.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.3 索引的定義 3. 索引的刪除 格式 索引名143.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.3 索引的定義n索引的有關(guān)說(shuō)明 可以動(dòng)態(tài)地定義索引，即可以隨時(shí)建立和刪除索引 不允許用戶在數(shù)據(jù)操作中引用索引。索引如何使用完全由系統(tǒng)決定 應(yīng)該在使用

9、頻率高的、經(jīng)常用于連接的列上建索引 一個(gè)表上可建多個(gè)索引。索引可以提高查詢效率，但索引過(guò)多耗費(fèi)空間，且降低了插入、刪除、更新的效率153.2 SQL數(shù)據(jù)定義功能(續(xù))n3.2.4 數(shù)據(jù)庫(kù)的建立與撤消 有的DBMS支持多庫(kù)。 通常，數(shù)據(jù)庫(kù)由包含數(shù)據(jù)的表集合和其它對(duì)象（如視圖、索引、存儲(chǔ)過(guò)程和觸發(fā)器）組成。 建立一個(gè)新數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù)名 撤消一個(gè)數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù)名 指定當(dāng)前數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù)名 數(shù)據(jù)庫(kù)名163.3.1 概述SELECT從數(shù)據(jù)庫(kù)中檢索行，并允許從一個(gè)或多個(gè)表中選擇一個(gè)或多個(gè)行或列。雖然 SELECT 語(yǔ)句的完整語(yǔ)法較復(fù)雜，但是其主要的子句可歸納如下：SELECT select_list IN

10、TO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC 可以在查詢之間使用 UNION 運(yùn)算符，以將查詢的結(jié)果組合成單個(gè)結(jié)果集。行條件子句分組子句組條件子句排序子句173.3.2 簡(jiǎn)單查詢（續(xù)）n1. SQL數(shù)據(jù)查詢基本結(jié)構(gòu) SELECT A1 , A2 , , AnFROM R1 , R2 , , RmWHERE P A1 , A2 , , An(p(R1 R2

11、Rm)183.3.2 簡(jiǎn)單查詢（續(xù)）n示例 Ex: 查詢所有老師的姓名 SELECT pname FROM Prof pname(Prof) 193.3.2 簡(jiǎn)單查詢（續(xù)）n2. SELECT子句 目標(biāo)列形式 列名，列名，* ，算術(shù)表達(dá)式，聚集函數(shù)，算術(shù)表達(dá)式，聚集函數(shù)Ex 1: 查詢所有老師的證件號(hào)，姓名，年齡SELECT p#, pname, age FROM ProfEx 2: 查詢所有老師的信息SELECT * FROM ProfEx 3: 查詢所有院系的信息SELECT * FROM Dept203.3.2 簡(jiǎn)單查詢（續(xù)）n2. SELECT子句 目標(biāo)列形式 列名，列名，* ，算術(shù)表

12、達(dá)式，聚集函數(shù)，算術(shù)表達(dá)式，聚集函數(shù)Ex 4 : 查詢所有老師的姓名及稅后工資額 SELECT pname，sal * 0.9 FROM Prof Ex 5: 查詢所有老師的平均工資 SELECT AVG(sal) FROM Prof 213.3.2 簡(jiǎn)單查詢（續(xù)）n3. 重復(fù)元組的處理 語(yǔ)法約束 缺省為保留重復(fù)元組，也可用關(guān)鍵字ALL顯式指明。 若要去掉重復(fù)元組，可用關(guān)鍵字DISTINCT指明Ex: 查詢所有選修課程的學(xué)生 SELECT DISTINCT sno FROM SC223.3.2 簡(jiǎn)單查詢（續(xù)）n4. FROM 子句 Ex: 列出教授“哲學(xué)”課程的老師的教工號(hào)及姓名 SELECT

13、 Prof. pno , Prof.pname FROM Prof , PC , Course WHERE Prof.pno = PC.pnoAND PC.Cno = CoAND Came = “哲學(xué)”233.3.2 簡(jiǎn)單查詢（續(xù)）n5. WHERE 子句 語(yǔ)法成分 比較運(yùn)算符 、 、 、=、 邏輯運(yùn)算符AND，OR，NOT BETWEEN 條件 判斷表達(dá)式的值是否在某范圍內(nèi)243.3.2 簡(jiǎn)單查詢（續(xù)）n5. WHERE 子句 Ex 1: 查詢工資低于2000的老師的姓名、工資、系別 SELECTpname , sal , dname FROM Prof , Dept WHERE sal 2

14、000 AND Prof.dno = Dept.dnoEx 2: 查詢工資在1000-2000之間的老師姓名 SELECT pname FROM Prof WHERE sal BETWEEN 1000 AND 2000253.3.2 簡(jiǎn)單查詢（續(xù)）n6. 更名運(yùn)算 格式 old_name new_name為關(guān)系和屬性重新命名，可出現(xiàn)在SELECT和FROM子句中注：ASAS可選 屬性更名 Ex1 :給出所有老師的姓名、所納稅額及稅后工資額 SELECT pname，sal0.05 AS taxi， sal * 0.95 AS incoming FROM Prof263.3.2 簡(jiǎn)單查詢（續(xù)）n

15、7. 字符串操作 語(yǔ)法格式 列名 NOT LIKE ESCAPE 找出滿足給定匹配條件的字符串 匹配規(guī)則 “%” 匹配零個(gè)或多個(gè)字符 “_” 匹配任意單個(gè)字符 Escape 定義轉(zhuǎn)義字符，以去掉特殊字符的特定含義，使其被作為普通字符看待 如escape “”，定義 作為轉(zhuǎn)義字符，則可用%去匹配%，用_去匹配_273.3.2 簡(jiǎn)單查詢（續(xù)）n7. 字符串操作 Ex 3: 列出名稱中含有4個(gè)字符以上，且倒數(shù)第3個(gè)字符是d，倒數(shù)第2個(gè)字符是_的系的所有信息 SELECT *FROM ProfWHERE pname LIKE “% d ”283.3.2 簡(jiǎn)單查詢（續(xù)）n8. 元組顯示順序 命令 ORD

16、ER BY 列名 ASC | DESCn Ex 1: 按系名升序列出老師姓名，所在系名，同一系中老師按姓名降序排列 SELECT dname, pname FROM Prof, Dept WHERE Prof.d# = Dept.d# ORDER BY dname ASC，pname DESC293.3.2 簡(jiǎn)單查詢（續(xù)）n9. 分組與聚集函數(shù) 聚集函數(shù) COUNT (DISTINCT A) / COUNT(DISTINCT*) SUM (DISTINCT A) AVG (DISTINCT A) MAX (A) MIN (A)303.3.2 簡(jiǎn)單查詢（續(xù)）n9.分組與聚集函數(shù) 分組命令GROU

17、P BY 列名 HAVING 條件表達(dá)式 GROUP BY + Group-list 將表中的元組按指定列上值相等的原則分組，然后在每一分組上使用聚集函數(shù)，得到單一值 HAVING + Group-qualification 對(duì)分組進(jìn)行選擇，只將聚集函數(shù)作用到滿足條件的分組上31nHavingn可以針對(duì)聚集函數(shù)的結(jié)果值進(jìn)行篩選，它是作用于分組計(jì)算的結(jié)果集n跟在Group By子句的后面32例：列出具有兩門(mén)（含）以上不及格的學(xué)生的學(xué)號(hào)、不及格的課目數(shù)。 Select s#,count(s#) From SC Where score =90 Group By s# Having count(s#)

18、 = 2333.3.2 簡(jiǎn)單查詢（續(xù)）n10. 空值 格式 IS NOT NULL 測(cè)試指定列的值是否為空值 = NULL - Ex 1: 找出年齡值為空的老師姓名 SELECT pname FROM Prof WHERE age IS NULL 34n注意事項(xiàng) 除is not null之外，空值不滿足任何查找條件 如果null參與算術(shù)運(yùn)算，則該算術(shù)表達(dá)式的值為null 如果null參與比較運(yùn)算，則結(jié)果可視為false。在SQL-92中可看成unknown 如果null參與聚集運(yùn)算，則除count(*)之外其它聚集函數(shù)都忽略null 353.3.3 嵌套查詢n1. 概念n2. 集合成員資格 (

19、IN)n3. 集合之間的比較(比較運(yùn)算符)n4. 相關(guān)嵌套查詢(EXISTS)363.3.3 嵌套查詢(續(xù))n2. 集合成員資格 (IN) 格式 表達(dá)式表達(dá)式 （子查詢）子查詢） 判斷表達(dá)式的值是否在子查詢的結(jié)果中n示例 列出張軍和王紅同學(xué)的所有信息 select * from S where SNAME in （“張軍”，“王紅”）37 列出選修了001號(hào)和002號(hào)課程的學(xué)生的學(xué)號(hào) select SNO from SC where SC.CNO = 001 and SNO in （select SNO from SC where CNO = 002）38 SELECT Sno，Sname F

20、ROM Student WHERE Sno IN (SELECT SnO FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname 信息系統(tǒng))393.3.3 嵌套查詢(續(xù))n3. 集合之間的比較 帶有比較運(yùn)算符的子查詢 帶有比較運(yùn)算符的子查詢是指父查詢與子查詢之間用比較運(yùn)算符進(jìn)行連接。當(dāng)用戶能確切知道內(nèi)層查詢返回的是單值時(shí)，可以用、!或等比較運(yùn)算符。403.3.3 嵌套查詢(續(xù))n3. 集合之間的比較 帶ANY或ALL謂詞的子查詢 表達(dá)式表達(dá)式 比較運(yùn)算符比較運(yùn)算符 （子查詢）子查詢） 表達(dá)式的值至少與子查詢結(jié)果中的一個(gè)值相比滿足比較運(yùn)算

21、符 表達(dá)式表達(dá)式 比較運(yùn)算符比較運(yùn)算符 （子查詢）子查詢） 表達(dá)式的值與子查詢結(jié)果中的所有的值相比都滿足比較運(yùn)算符413.3.3 嵌套查詢(續(xù)) Exists + 子查詢可用以測(cè)試該子查詢的結(jié)果是否有元組 帶有Exists的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生 True/False 當(dāng)子查詢的結(jié)果集含有元組時(shí)Exists為T(mén)rue 當(dāng)子查詢的結(jié)果集不含有任何元組時(shí)Exists為False 由于不關(guān)心子查詢的具體內(nèi)容，因此用 Select *42 SELECT Sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno

22、 AND Cno=1)；Student(sno,sname,ssex,sage,sdept)Course(cno,cname,cpno,ccredit)Sc(sno,cno,grade)43select sname from student where not exists (select * from sc where sno = student.sno and cno = 03 )例：查詢沒(méi)有選修課程代號(hào)為03的學(xué)生姓名443.3.3 嵌套查詢(續(xù))n4. 相關(guān)嵌套查詢(EXISTS) IN和EXISTS的比較IN后的子查詢與外層查詢無(wú)關(guān)，每個(gè)子查詢執(zhí)行一次，而EXISTS后的子查詢與外層

23、查詢有關(guān)，需要執(zhí)行多次。45n命令n集合并：unionn集合交：intersectn集合差：except 提示 集合操作自動(dòng)去除重復(fù)元組，如果要保留重復(fù)元組的話，必須用all關(guān)鍵詞指明3.3.4 集合查詢463.3.4 集合查詢n示例 求選修了001或（且）002號(hào)課程的學(xué)生號(hào)(select SNO from SC where CNO = 001)union (intersect)(select SNO from SC where CNO = 002)47 求選修了001和002號(hào)而沒(méi)有選003號(hào)課程的學(xué)生號(hào)(select SNO from SC where CNO = 001 or CNO

24、= 002 )except(select SNO from SC where CNO = 003)483.3.5 視圖n1. 視圖的基本概念 創(chuàng)建視圖 CREATE VIEW view_name AS WITH CHECK OPTION視圖的屬性名缺省為子查詢結(jié)果中的屬性名，也可以顯式指明。 with check option指明當(dāng)對(duì)視圖進(jìn)行insert，update時(shí)，要檢查是否滿足視圖定義中的條件。撤銷視圖 DROP VIEW view_name 49例：計(jì)算機(jī)系的花名冊(cè)Create View CS_Stu asSelect sno,sname,ssexFrom StudentWhere

25、sdept = CS例：列出計(jì)算機(jī)系的男生Select sno,snameFrom CS_StuWhere ssex = MStudent(sno,sname,ssex,sage,sdept)Course(cno,cname,cpno,ccredit)Sc(sno,cno,grade)503.4 SQL數(shù)據(jù)修改功能(續(xù))n1. 插入 格式 插入單個(gè)元組INSERT INTO 表名 （列名，列名VALUES (值 ，值) 插入子查詢結(jié)果 INSERT INTO 表名 （列名，列名 （子查詢）513.4 SQL數(shù)據(jù)修改功能(續(xù))n1. 插入 示例 INSERT INTO Prof VALUES (

26、 P0011, “王明”, 35, 08, 4980 ) INSERT INTO Prof (P#, PNAME, D#) VALUES ( P0012, “李明”, 03 ) 523.4 SQL數(shù)據(jù)修改功能(續(xù))n1. 插入操作 Ex 10:將平均成績(jī)大于90的學(xué)生加入到表Excellent中Excellent(s#,avg_score)SC(s#,c#,score)INSERT INTO Excellent ( s#,avg_score)SELECT s# , AVG(score)FROM SCGROUP BY s# HAVING AVG(score) 90533.4 SQL數(shù)據(jù)修改功能(

27、續(xù))n2. 刪除操作 格式DELETE FROM WHERE 從表中刪除符合條件的元組，如果沒(méi)有where語(yǔ)句，則刪除所有元組 Ex11: 刪除所有選課記錄DELETE FROM SC Ex12:刪除學(xué)號(hào)為95019的學(xué)生記錄。 DELETE FROM Student WHERE Sno95019543.4 SQL數(shù)據(jù)修改功能(續(xù))n3.更新操作 格式UPDATE 表名 SET 列名 = 表達(dá)式 | 子查詢 列名 = ，表達(dá)式 | 子查詢WHERE 條件表達(dá)式Ex 13: 老師工資上調(diào)5% UPDATE Prof SET sal = sal * 1.0555update SCset score

28、 = score * 1.05 where C# = C1 and score (select avg(score) from SC where C# = C1)當(dāng)C1課程的成績(jī)小于該課程的平均成績(jī)時(shí)，將該成績(jī)提高5%Sc(s#,c#,Score)563.4 SQL數(shù)據(jù)修改功能(續(xù))n4. 視圖更新 操作分類 INSERT, DELETE, UPDATE 特點(diǎn) 對(duì)視圖的更新，最終要轉(zhuǎn)換為對(duì)基本表的更新 View Ex 1:CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS573.4 SQL數(shù)據(jù)修改功能(續(xù))n4. 視圖更新 Ex 1: 向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄，其中學(xué)號(hào)為95029，姓名為趙新，年齡為20歲INSERTINTO IS_StudentVALUES(95029, 趙新, 20)INSERT INTO Student(sno,sname,sage,sdept)VALUES(95029, 趙新, 20, IS) 583.4 SQL數(shù)據(jù)修改功能(續(xù))n4. 視圖更新 Ex 2:刪除計(jì)算機(jī)系學(xué)生視圖CS_S中學(xué)號(hào)為95