數(shù)據(jù)庫原理及應(yīng)用-第4章-SQL語言

1、數(shù)據(jù)庫原理與應(yīng)數(shù)據(jù)庫原理與應(yīng)用用第第4 4章章 SQLSQL語言語言SQL( Structured Query Language)結(jié)構(gòu)化查詢語言結(jié)構(gòu)化查詢語言 已成為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言支持關(guān)系系統(tǒng)Oracle、Informix、Sybase、MS SQL、DB2、FoxPro、Access等SQL語言概述DDL(Data Definition Language)數(shù)據(jù)定義語言數(shù)據(jù)定義語言 主要定義數(shù)據(jù)庫的邏輯結(jié)構(gòu)：基本表、視圖和索引。DML(Data Manipulation Language) 數(shù)據(jù)操縱語言數(shù)據(jù)操縱語言 查詢和更新(插入、刪除和修改) DCL (Data Control La

2、nguage)數(shù)據(jù)庫控制語言數(shù)據(jù)庫控制語言 授權(quán)、完整性規(guī)則的描述和事務(wù)控制。嵌入式嵌入式SQL 規(guī)定了SQL語句在宿主語言的程序中使用規(guī)則。SQL語言的組成1. 綜合統(tǒng)一查詢、操縱、定義、控制操作功能一體化語言風(fēng)格統(tǒng)一，數(shù)據(jù)操作符統(tǒng)一性2. 高度非過程化用戶只需提出“做什么”，而不必指明“怎么做”隱蔽數(shù)據(jù)存取路徑3. 面向集合的操作方式操作的對象和結(jié)果都是元組的集合（關(guān)系）SQL語言的特點(diǎn)4. 以同一種語法結(jié)構(gòu)提供兩種使用方式自含式語言，DBMS中獨(dú)立使用，是針對DB的所有用戶 嵌入式語言，嵌入到宿主語言中使用，針對應(yīng)用程序員 兩種類型的語法結(jié)構(gòu)基本一致5. 語言簡潔，易學(xué)易用類似于英語的自

3、然語言操作謂詞少（核心功能只用9個(gè)動(dòng)詞）SQL功能動(dòng)詞 數(shù)據(jù)查詢 數(shù)據(jù)定義 數(shù)據(jù)操縱 數(shù)據(jù)控制SELECTCREATE，DROP，ALTERINSERT，UPDATE，DELETEGRANT，REVOKESQL支持關(guān)系數(shù)據(jù)庫的三層模式基本表基本表T1視圖視圖V1基本表基本表T3基本表基本表T4存儲文件存儲文件1存儲文件存儲文件2基本表基本表T2視圖視圖V2SQL用戶用戶外模式外模式模式模式內(nèi)模式內(nèi)模式 基本表基本表（base table)：數(shù)據(jù)庫中實(shí)際獨(dú)立存在的表（關(guān)系）存儲在實(shí)際的文件中 存儲文件存儲文件存儲基本表的數(shù)據(jù) 視圖（視圖（view)是一個(gè)虛擬表從基本表或其他視圖中導(dǎo)出的表沒有實(shí)際

4、的存儲位置數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，數(shù)據(jù)仍然存放在導(dǎo)出視圖的基本表中Transact-SQL語言 結(jié)構(gòu)化查詢語言結(jié)構(gòu)化查詢語言SQL(Structured Query Language) SQL語言現(xiàn)在已成為關(guān)系型數(shù)據(jù)庫環(huán)境下的標(biāo)準(zhǔn)查詢語言。 Transact-SQL語言語言微軟公司在SQL標(biāo)準(zhǔn)的基礎(chǔ)上做了大幅度擴(kuò)充，并將SQL Server使用的SQL語言稱為Transact-SQL語言語言。 Transact-SQL命令格式的約定在Transact-SQL語言的命令格式中， 括起來的內(nèi)容表示是可選的；，n表示重復(fù)前面的內(nèi)容；括起來表示在實(shí)際編寫語句時(shí)，用相應(yīng)的內(nèi)容替代

5、; 括起來表示是必選的；類似A|B的格式：表示A和B只能選擇一個(gè)，不能同時(shí)都選。 系統(tǒng)數(shù)據(jù)類型系統(tǒng)數(shù)據(jù)類型 SQL Server預(yù)先定義好的，可以直接使用。數(shù)據(jù)類型1整型數(shù)據(jù)類型整型數(shù)據(jù)類型 int（integer）：）：4個(gè)字節(jié)個(gè)字節(jié)系統(tǒng)數(shù)據(jù)類型系統(tǒng)數(shù)據(jù)類型 SQL Server預(yù)先定義好的，可以直接使用。數(shù)據(jù)類型2 2字符數(shù)據(jù)類型字符數(shù)據(jù)類型用來存儲各種字母、數(shù)字符號和特殊符號。在使用時(shí)需要在其前后加上英文單引號或者雙引號。 （1）Char：占用1個(gè)字節(jié)。定義：charchar（n n） n的取值為18000。默認(rèn)n的值為1（2）Varchar：可以存儲長達(dá)8000個(gè)字符的可變長度字符串，

6、和char類型不同varchar類型根據(jù)輸入數(shù)據(jù)的實(shí)際長度而變化。其定義形式為：varchar（n） 數(shù)據(jù)數(shù)據(jù)定義語言定義語言 DDLDDL(Data Definition Language)(Data Definition Language)DDL包括定義基本表、視圖和索引對象定義方式表 tabel create table視圖 viewcreate view索引 indexcreate index學(xué)生-課程數(shù)據(jù)庫Student(Sno,Sname,Ssex,Sage, Sphone)Course(Cno,Cname)SC(Sno,Cno,Grade)后續(xù)示例用到的數(shù)據(jù)庫模式語句格式：cre

7、ate tabel ( 列級完整性約束條件 , 列級完整性約束條件 , );數(shù)據(jù)數(shù)據(jù)定義語言定義語言 DDLDDL(Data Definition Language)(Data Definition Language)定義基本表：約束是SQL Server提供的自動(dòng)保持?jǐn)?shù)據(jù)庫完整性的一種方法 。約束約束 constraintconstraint主鍵約束主鍵約束 primary key主鍵能夠唯一地確定表中的每一條記錄，主鍵不能取空值。主鍵約束可以保證實(shí)體的完整性。 唯一性約束唯一性約束 unique唯一性約束用于指定一個(gè)或多個(gè)列的組合值具有唯一性，以防止在列中輸入重復(fù)的值。使用唯一性約束的字段

8、允許為空值允許為空值；一個(gè)表中可以允許有多個(gè)唯一性約束；可以把唯一性約束定義在多個(gè)字段上；約束是SQL Server提供的自動(dòng)保持?jǐn)?shù)據(jù)庫完整性的一種方法 。約束約束 constraintconstraint外鍵約束外鍵約束 references 外鍵約束主要用來維護(hù)兩個(gè)表之間數(shù)據(jù)的一致性。n外鍵約束提供了字段參照完整性；n外鍵從句中的字段數(shù)目和每個(gè)字段指定的數(shù)據(jù)類型都必須和REFERENCES從句中的字段相匹配；n參考同一表中的字段時(shí)，必須只使用REFERENCES子句，不能使用外鍵子句；建立學(xué)生表student，由學(xué)號、姓名、性別、年齡、系別5個(gè)屬性組成.CREATE TABLE stude

9、nt (Sno char(8) primary key, Sname char(20) not null , Ssex char(1) default 男, Sage int check(Sage=0 and Sage =120), Sphone char(15) unique ) 語句格式ALTER TABLE ADD 完整性約束 DROP MODIFY 修改內(nèi)容增加新列和新的完整性約束條件（ADD子句） 刪除指定的完整性約束條件（DROP子句） 修改原有的列定義 （ MODIFY子句）修改基本表 ALTER TABLE查詢selectSELECT語句對基本表（關(guān)系）和視圖（虛表）進(jìn)行數(shù)據(jù)查

10、詢它不改變數(shù)據(jù)本身。查詢主要有：單表查詢連接查詢嵌套查詢集合查詢SELECT語句一般格式：SELECT ALL|DISTINCT ,FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESCSELECT 指明要檢索的數(shù)據(jù)的列 FROM指明從哪(幾)個(gè)表中進(jìn)行檢索 WHERE指明返回?cái)?shù)據(jù)必須滿足的標(biāo)準(zhǔn) GROUP BY 指明返回的列數(shù)據(jù)通過某些條件來形成組 HAVING 指明返回的集合必須滿足的標(biāo)準(zhǔn)，它必須與GROUP BY配合使用 ORDER BY 指明返回的行的排序規(guī)則 單表查詢1、選擇表中的若干列（投影）SELECT2、選擇表中的若干行（選?。￤HERE3

11、、對查詢結(jié)果排序 ORDER BY4、集函數(shù)查詢5、對查詢結(jié)果分組 GROUP BY選擇表中的若干列1）查詢指定的列1. 查詢?nèi)w學(xué)生的學(xué)號與姓名 SELECT Sno, Sname FROM Student2. 查詢學(xué)生姓名、學(xué)號和性別 SELECT Sname, Sno, Ssex FROM Student 2）查詢?nèi)苛?. 查詢?nèi)w學(xué)生的詳細(xì)記錄 SELECT * FROM Student 2. 顯示指定字段輸出次序SELECT Sno, Sphone, Sname, Sage, Ssex FROM Student3）查詢經(jīng)過計(jì)算的值1. 查詢?nèi)w學(xué)生的姓名及其出生年份 SELECT

12、Sname,2012-Sage FROM Student4）列標(biāo)題使用別名1.查詢?nèi)w學(xué)生的姓名、出生年份，并使用別名 SELECT Sname Name, 2012-Sage AS Birthday FROM Student1）消除重復(fù)的行：DISTINCT1. 查詢所有選修過課的學(xué)生的學(xué)號 SELECT Sno FROM SC 等價(jià)于： SELECT ALL Sno FROM SC結(jié)果中保留了重復(fù)的行 SELECT DISTINCT Sno FROM SC結(jié)果中消除了重復(fù)的行選擇表中的若干行2）查詢滿足條件的元組通過WHERE子句實(shí)現(xiàn)常用的查詢條件查詢條件謂 詞比較=, , =, =, !

13、=, , !, !, NOT+上述比較符確定范圍BETWEEN AND， NOT BETWEEN AND確定集合IN， NOT IN字符匹配LIKE，NOT LIKE空值IS NULL， IS NOT NULL多重條件AND，OR（1）比較大?。罕容^運(yùn)算符1. 查詢計(jì)算機(jī)系全體學(xué)生的名單SELECT Sname FROM Student WHERE Sdept=CS2. 查詢年齡小于20歲學(xué)生SELECT * FROM Student WHERE Sage=203. 查詢考試成績不及格學(xué)生的學(xué)號SELECT DISTINCT Sno FROM SC WHERE Grade=60 AND Gra

14、de=752. 查詢成績不在60至75之間的學(xué)生SELECT * FROM SC WHERE Grade NOT BETWEEN 60 AND 75(3)確定集合（NOT）IN1. 查詢選修了課程C1或C2或C3的學(xué)號SELECT DISTINCT Sno FROM SC WHERE Cno IN (C1,C2,C3)或：SELECT DISTINCT Sno FROM Sc WHERE Cno=C1 OR Cno=C2 OR Cno=C32. 查詢既不是計(jì)算機(jī)系，也不是數(shù)學(xué)系的學(xué)生SELECT * FROM Student WHERE Sdept NOT IN (CS,MA)(4)字符匹配（

15、模糊查詢）（NOT）LIKE語法格式： NOT LIKE ESCAPE含義：查詢指定的屬性列值與相匹配的元組。 通配符 ：%(百分號)：代表任意長度的字符串 _(下劃線)：代表任意單個(gè)字符ESCAPE短語，對通配符進(jìn)行轉(zhuǎn)義1. 查詢計(jì)算機(jī)系(CS)的學(xué)生SELECT * FROM Student WHERE Sdept LIKE CS或：SELECT * FROM Student WHERE Sdept = CS2. 查詢姓趙的學(xué)生姓名、學(xué)號和性別SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 趙%3. 查詢第二個(gè)字符為o的課程SELEC

16、T * FROM Course WHERE Cname LIKE _o%4. 查詢以DB_開頭，且倒數(shù)第2個(gè)字符為i的課程SELECT * FROM Course WHERE Cname LIKE DB_%i_ ESCAPE （5）涉及空值的查詢：IS （NOT）NULL1. 查詢?nèi)鄙俪煽兊膶W(xué)生學(xué)號和相應(yīng)課程號 SELECT Sno, Cno FROM SC WHERE Grade IS NULL2. 查詢有成績的學(xué)生學(xué)號和相應(yīng)課程號 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL（6）多重條件查詢：AND/OR1. 查詢課程號為C2且成績高于8

17、5分以上的學(xué)生SELECT * FROM SC WHERE Cno=C2 ANDGrade852. 查詢選修C1或C2且不低于70分的學(xué)生 SELECT * FROM SC WHERE (Cno=C1 OR Cno=C2) ANDGrade=70AND優(yōu)先級高于OR指定按照一個(gè)或多個(gè)屬性列的升序（ASC）或降序（DESC）重新排列查詢結(jié)果。1.查詢選修了C1的學(xué)生，成績按降序排列 SELECT * FROM SC WHERE Cno=C1 ORDER BY Grade DESC降序排列，成績?yōu)榭盏脑M最先顯示升序排列，成績?yōu)榭盏脑M最后顯示2. 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系升序排列，對同

18、一系中的學(xué)生按年齡降序排列 SELECT * FROM Student ORDER BY Sdept, Sage DESC對查詢結(jié)果排序 ORDER BYCOUNT (DISTINCT|ALL *)統(tǒng)計(jì)元組個(gè)數(shù)，用DISTINCT消除重復(fù)行COUNT (DISTINCT|ALL ) 統(tǒng)計(jì)列值計(jì)個(gè)數(shù)，用DISTINCT消除重復(fù)行SUM (DISTINCT|ALL ) 按列計(jì)算值的總和，對數(shù)值有效AVG (DISTINCT|ALL ) 按列計(jì)算平均值，對數(shù)值有效 MAX (DISTINCT|ALL )求一列值中最大值MIN (DISTINCT|ALL )求一列值中最小值 集函數(shù)查詢1. 求計(jì)算機(jī)系（CS）學(xué)生的平均年齡 SELECT AVG(Sage) FROM Student WHERE Sdept=CS2. 求95003學(xué)生的總分和平均分SELECT SUM(Grade), AVG(Grade)FROM SC WHERE Sno=950033. 求計(jì)算機(jī)系的學(xué)生總數(shù)SELECT COUNT(*) FROM Student WHERE Sdept=CS 或： SELECT COUNT(Sno) FROM Student WHERE Sdept=CS4. 查詢共有幾個(gè)系SELE