ch3關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL

1、數(shù)據(jù)庫系統(tǒng)原理第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQLSQLAn Introduction to Database System學(xué)習(xí)要點SQL概述概述數(shù)據(jù)定義數(shù)據(jù)定義數(shù)據(jù)查詢數(shù)據(jù)查詢數(shù)據(jù)更新數(shù)據(jù)更新數(shù)據(jù)控制數(shù)據(jù)控制嵌入式嵌入式SQL學(xué)習(xí)要求熟練掌握應(yīng)用SQL的數(shù)據(jù)操作熟悉SQL的數(shù)據(jù)控制熟悉SQL嵌入式編程3.1 SQL (Structured Query Language)概述SQL的特點 綜合統(tǒng)一 高度非過程化 面向集合的操作方式 以同一種語法結(jié)構(gòu)提供多種使用方式 語言簡潔，易學(xué)易用SQL支持關(guān)系數(shù)據(jù)庫三級模式結(jié)構(gòu)SQL視圖視圖2視圖視圖1基本表基本表2基本表基本表1基本表

2、基本表3基本表基本表4存儲文件存儲文件2存儲文件存儲文件1外模式外模式模模 式式內(nèi)模式內(nèi)模式SQL的基本概念基本表n本身獨立存在的表nSQL中一個關(guān)系就對應(yīng)一個基本表n一個(或多個)基本表對應(yīng)一個存儲文件n一個表可以帶若干索引存儲文件n邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式n物理結(jié)構(gòu)是任意的，對用戶透明視圖n從一個或幾個基本表導(dǎo)出的表n數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)n視圖是一個虛表n用戶可以在視圖上再定義視圖數(shù)據(jù)庫實例：學(xué)生-課程 學(xué)號學(xué)號Sno姓姓 名名Sname性別性別Ssex年年 齡齡Sage系系Sdept年級年級SGStudent表Course表課程號課程號Cno課程名課程名

3、Cname先行課先行課Cpno學(xué)分學(xué)分Ccredit學(xué)學(xué) 號號Sno 課程號課程號 Cno 成績成績 GradeSC表3.2 數(shù)據(jù)定義 SQL的數(shù)據(jù)定義功能:表定義、視圖和索引的定義 操作對象操作方式創(chuàng)建刪除修改表CREATE TABLEDROP TABLEALTER TABLE視圖CREATE VIEWDROP VIEW索引CREATE INDEXDROP INDEX基本表的定義一、定義基本表一、定義基本表CREATE TABLE （ ， ， ）； 如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。 SQL2000 數(shù)據(jù)類型SQL中域的概念用

4、數(shù)據(jù)類型來實現(xiàn)定義表的屬性時 需要指明其數(shù)據(jù)類型及長度 選用哪種數(shù)據(jù)類型 n取值范圍 n要做哪些運算 類型標(biāo)識說明Boolean布爾型，取true、falseByte“字節(jié)”數(shù)據(jù)類型，取值(0.255)Integer“整數(shù)”數(shù)據(jù)類型，16位(2字節(jié))Long“長整型”數(shù)據(jù)類型，32位Single“單精度浮點型”數(shù)據(jù)類型Double“雙精度浮點型”數(shù)據(jù)類型Currency“貨幣”數(shù)據(jù)類型Decimal“十進(jìn)制”數(shù)據(jù)類型，12字節(jié)Date“日期”數(shù)據(jù)類型Char“字符”數(shù)據(jù)類型Oracle 數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型含義含義CHAR(n)長度為長度為n的定長字符串的定長字符串VARCHAR(n)最大

5、長度為最大長度為n的變長字符串的變長字符串INT長整數(shù)（也可以寫作長整數(shù)（也可以寫作INTEGER）SMALLINT短整數(shù)短整數(shù)NUMERIC(p，d)定點數(shù)，由定點數(shù)，由p位數(shù)字（不包括符號、小數(shù)點）組成，小數(shù)后面有位數(shù)字（不包括符號、小數(shù)點）組成，小數(shù)后面有d位數(shù)字位數(shù)字REAL取決于機(jī)器精度的浮點數(shù)取決于機(jī)器精度的浮點數(shù)Double Precision取決于機(jī)器精度的雙精度浮點數(shù)取決于機(jī)器精度的雙精度浮點數(shù)FLOAT(n)浮點數(shù)，精度至少為浮點數(shù)，精度至少為n位數(shù)字位數(shù)字DATE日期，包含年、月、日，格式為日期，包含年、月、日，格式為YYYY-MM-DDTIME時間，包含一日的時、分、秒

6、，格式為時間，包含一日的時、分、秒，格式為HH:MM:SS完整性約束Null | Not Null 空值(非空)約束Primary Key 主鍵約束Foreign Key 外鍵約束Unique 惟一約束Check 檢查約束Default 默認(rèn)約束建立學(xué)生表Student學(xué)號是主碼。 CREATE TABLE Student (Sno CHAR(8) PRIMARY KEY， /* 列級完整性約束條件*/ Sname CHAR(20) ， Ssex CHAR(2)， Sage SMALLINT， Sdept CHAR(20) )； 主碼主碼建立課程表Course課程號為主碼,Cpno為先行課程號

7、,同一關(guān)系形成參照關(guān)系 CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY， Cname CHAR(40)， Cpno CHAR(4) ， Ccredit SMALLINT， FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); Cpno是外碼是外碼 被參照表是被參照表是Course被參照列是被參照列是Cno被參照建立選課表SC(學(xué)號,課程號)為主碼CREATE TABLE SC (Sno CHAR(9)， Cno CHAR(4)， Grade SMALLINT， PRIMARY KEY (Sno，Cno)， /* 主

8、碼由兩個屬性構(gòu)成，必須作為表級完整性進(jìn)行定義*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)，/*表級完整性約束條件，Sno是外碼，被參照表是Student */ FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表級完整性約束條件， Cno是外碼，被參照表是Course*/); 修改基本表ALTER TABLE ADD 完整性約束 DROP ALTER COLUMN ；例：向Student表增加“年級”列，其數(shù)據(jù)類型字符型。ALTER TABLE Student ADD SG char(2)； 增加課程名稱必須取唯

9、一值的約束條件。 ALTER TABLE Course ADD UNIQUE(Cname);刪除基本表 DROP TABLE RESTRICT| CASCADE；nRESTRICT：刪除表是有限制的。欲刪除的基本表不能被其他表的約束所引用如果存在依賴該表的對象，則此表不能被刪除nCASCADE：刪除該表沒有限制。在刪除基本表的同時，相關(guān)的依賴對象一起刪除 索引的建立與刪除建立索引的目的：加快查詢速度誰可以建立索引nDBA 或 表的屬主（即建立表的人）nDBMS一般會自動建立以下列上的索引 PRIMARY KEY UNIQUE誰維護(hù)索引 DBMS自動完成 使用索引 DBMS自動選擇是否使用索引以

10、及使用哪些索引索 引RDBMS中索引一般采用B+樹、HASH索引來實現(xiàn)nB+樹索引具有動態(tài)平衡的優(yōu)點 nHASH索引具有查找速度快的特點采用B+樹，還是HASH索引 則由具體的RDBMS來決定索引是關(guān)系數(shù)據(jù)庫的內(nèi)部實現(xiàn)技術(shù)，屬于內(nèi)模式的范疇 CREATE INDEX語句定義索引時，可以定義索引是唯一索引、非唯一索引或聚簇索引 建立索引 語句格式CREATE UNIQUE CLUSTER INDEX ON (, )；例：在Course表的Cname（課程名）列上建立一個聚簇索引 CREATE CLUSTER INDEX Corcname ON Course(Cname)；SC表按學(xué)號升序和課程號

11、降序建唯一索引CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；刪除索引 DROP INDEX ；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。例15 刪除Course表的Corcname索引 DROP INDEX Corcname ；視 圖視圖的特點虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表只存放視圖的定義，不存放視圖對應(yīng)的數(shù)據(jù)基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變視圖的作用簡化用戶的操作提供多種界面 保持?jǐn)?shù)據(jù)邏輯獨立性 提供安全保護(hù)清晰的表達(dá)查詢建立視圖語句格式 CREATE VIEW ( ，) AS WITH CHEC

12、K OPTION；組成視圖的屬性列名：全部省略或全部指定子查詢不允許含有ORDER BY子句和DISTINCT短語RDBMS執(zhí)行CREATE VIEW語句時只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對視圖查詢時，按視圖的定義從基本表中將數(shù)據(jù)查出。建立S_C視圖為便于生成學(xué)生課程成績表（姓名，課程名，成績）： CREATE VIEW S_C AS SELECT Sname，Cname，Grade FROM Student,Course,SC WHERE Student.Sno=SC.Sno and Course.Cno=SC.Cno WITH CHECK OPTION；建立視

13、圖例:建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時仍需保證該視圖只有信息系的學(xué)生 。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；對IS_Student視圖的更新操作：修改操作：自動加上Sdept= IS的條件刪除操作：自動加上Sdept= IS的條件插入操作：自動檢查Sdept屬性值是否為IS n如果不是，則拒絕該插入操作如果沒有提供Sdept屬性值，則自動定義Sdept為IS刪除視圖語句的格式：DROP VIEW ；n該語句從數(shù)據(jù)字典中刪除指

14、定的視圖定義n如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除 n刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROP VIEW語句刪除 DROP VIEW IS_ Student CASCADE;3.3 數(shù)據(jù)查詢語句格式 SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； 目標(biāo)列表達(dá)式查詢指定列例1 查詢?nèi)w學(xué)生的學(xué)號與姓名及性別。SELECT Sno，Sname，Ssex FROM Student；選出所有屬性列：n在SELECT關(guān)鍵字后面列

15、出所有列名 n將指定為 *例2 查詢?nèi)w學(xué)生的詳細(xì)記錄。SELECT Sno，Sname，Ssex，Sage，Sdept ，SGFROM Student； 或SELECT *FROM Student； 目標(biāo)列表達(dá)式SELECT子句的可以為：n算術(shù)表達(dá)式n字符串常量n函數(shù)n列別名 例3 查06級全體學(xué)生的姓名及其出生年份。SELECT Sname，2006-Sage /*假定當(dāng)年的年份為2006年*/FROM Student；例4 查詢?nèi)w學(xué)生的姓名、出生年份和所有系，用小寫字母表示所有系名SELECT Sname，Year of Birth: ，2004-Sage，ISLOWER(Sdept)

16、FROM Student；輸出結(jié)果：輸出結(jié)果： Sname Year of Birth: 2004-Sage ISLOWER(Sdept)單表查詢-使用列別名改變查詢結(jié)果使用列別名改變查詢結(jié)果的列標(biāo)題: SELECT Sname as NAME，Year of Birth: as BIRTH， 2000-Sage as BIRTHDAY，LOWER(Sdept) as DEPARTMENTFROM Student；輸出結(jié)果： NAME BIRTH BIRTHDAY DEPARTMENT - - - -格式：SELECT 字段名 as 字段別名 from table單表查詢-選擇表中若干元組Al

17、l顯示所有滿足查詢條件元組 如果沒有指定DISTINCT關(guān)鍵詞，則缺省為ALL 指定DISTINCT關(guān)鍵詞，去掉表中重復(fù)的行單表查詢條件查詢查 詢 條 件謂 詞比 較=，=，=，!=，!，!；NOT+上述比較運算符確定范圍BETWEEN AND，NOT BETWEEN AND確定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重條件（邏輯運算） AND，OR，NOT表3.4 常用的查詢條件條件查詢比較大小例5 查詢信息科學(xué)系全體學(xué)生的名單。 SELECT Sname FROM Student WHERE Sdept=IS； 例6 查詢考試成

18、績有不及格的學(xué)生的學(xué)號。 SELECT DISTINCT Sno FROM SC WHERE Grade60； 條件查詢確定范圍謂詞謂詞: BETWEEN AND NOT BETWEEN AND 例7 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的 姓名、系別和年齡 SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23； 例8 查詢年齡不在2023歲之間的學(xué)生姓名、系別和年齡 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23

19、； 條件查詢確定集合謂詞：謂詞：IN , NOT IN 例9 查詢信息系（IS）、數(shù)學(xué)系（MA）和計算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );例10 查詢既不是信息系、數(shù)學(xué)系，也不是計算機(jī)科學(xué)系的學(xué)生的姓名和性別。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，MA，CS );條件查詢字符匹配謂詞：謂詞： NOT LIKE ESCAPE 1) 匹配串為固定字符串例11 查詢學(xué)號為200215121的學(xué)生的詳細(xì)情況。 SELECT

20、 * FROM Student WHERE Sno LIKE 200215121；等價于： SELECT * FROM Student WHERE Sno = 200215121 ； 2) 匹配串為含通配符的字符串 例12 查詢所有姓劉學(xué)生的姓名、學(xué)號和性別。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 劉%； 例13 查詢姓歐陽且全名為三個漢字的學(xué)生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽_； 例15 查詢所有不姓劉的學(xué)生姓名。 SELECT Sname，Sno，Ssex

21、FROM Student WHERE Sname NOT LIKE 劉%；可代替任意字符串_可代替任意單個字符條件查詢字符匹配3) 使用換碼字符將通配符轉(zhuǎn)義為普通字符 例16 查詢DB_Design課程的課程號和學(xué)分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE ； 例17 查詢以DB_開頭，且倒數(shù)第3個字符為 i的課程的詳細(xì)情況。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ； ESCAPE 表示表示“ ” 為換碼字符為換碼字符 條件查詢空值的

22、查詢n謂詞：謂詞： IS NULL 或 IS NOT NULLn “IS” 不能用 “=” 代替 例18 某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒 有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL例19 查所有有成績的學(xué)生學(xué)號和課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；條件查詢多重條件查詢邏輯運算符：AND和 OR來聯(lián)結(jié)多個查詢條件w AND的優(yōu)先級高于ORw 可以用括號改變優(yōu)先級 例20 查詢計算機(jī)系年齡在20歲以下的學(xué)生姓名。

23、SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；可用來實現(xiàn)多種其他謂詞w NOT INw NOT BETWEEN AND SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS )可改寫為：SELECT Sname，Ssex FROM Student WHERE Sdept= IS OR Sdept= MA OR Sdept= CS ；：ORDER BY子句 ORDER BY子句n可以按一個或多個屬性列排序n升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時nASC：排序

24、列為空值的元組最后顯示nDESC：排序列為空值的元組最先顯示 例21 查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC；例22 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC；聚集函數(shù) 聚集函數(shù)：n計數(shù)COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）n計算總和SUM（DISTINCT|ALL ）n 計算平

25、均值A(chǔ)VG（DISTINCT|ALL ）n最大最小值 MAX（DISTINCT|ALL ） MIN（DISTINCT|ALL ）例23 查詢學(xué)生總?cè)藬?shù)。SELECT COUNT(*) FROM Student； 例24 查詢選修了課程的學(xué)生人數(shù)。SELECT COUNT(DISTINCT Sno) FROM SC；例25 計算1號課程的學(xué)生平均成績。SELECT AVG(Grade) FROM SC WHERE Cno= 1 ；例26 查詢選修1號課程的學(xué)生最高分。SELECT MAX(Grade) FROM SC WHERE Cno= 1 ；例27 查詢學(xué)生200215012選修課程的總學(xué)分

26、數(shù)。SELECT SUM(Ccredit) FROM SC,Course WHER Sno=200215012 AND SC.Cno=Course.Cno; GROUP BY子句 GROUP BY子句分組： 細(xì)化聚集函數(shù)的作用對象n未對查詢結(jié)果分組，聚集函數(shù)將作用于整個查詢結(jié)果n對查詢結(jié)果分組后，聚集函數(shù)將分別作用于每個組 n作用對象是查詢的中間結(jié)果表n按指定的一列或多列值分組，值相等的為一組例28 求各個課程號及相應(yīng)的選課人數(shù)。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；HAVING子句HAVING短語與WHERE子句的區(qū)別：n作用對象不同nWHE

27、RE子句作用于基表或視圖，從中選擇滿足條件的元組nHAVING短語作用于組，從中選擇滿足條件的組。例29 查詢選修了3門以上課程的學(xué)生學(xué)號。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3; 多表查詢 連接查詢 連接查詢：同時涉及多個表的查詢連接條件或連接謂詞：用來連接兩個表的條件 一般格式：n. .n. BETWEEN . AND .連接字段：連接謂詞中的列名稱n連接條件中的各連接字段類型必須是可比的，但名字不必是相同的連接操作嵌套循環(huán)法(NESTED-LOOP)n1、表1中取出一個元組，n2、掃描表2直至全部查找結(jié)束，找出滿足連接件的元組與

28、表1中取出元組進(jìn)行拼接形成結(jié)果元組。n3、若表1中還有未取出元組，從表1中取下一個元組，返回2n4、否則結(jié)束連接操作排序合并法(SORT-MERGE)n1、按連接屬性對表1和表2排序n2、從表1取出一個元組，n3、若該元組連接字段小于等于表2的連接字段值，則掃描表2直至全部查找結(jié)束，找出滿足連接件的元組與表1中取出元組進(jìn)行拼接形成結(jié)果元組。n4、否則若表1還有未取出元組，從表1取出下一個元組轉(zhuǎn)3n5、否則結(jié)束連接操作索引連接(INDEX-JOIN)n對表2按連接字段建立索引n對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接

29、起來，形成結(jié)果表中一個元組 等值與非等值連接查詢 等值連接：連接運算符為=例30 查詢每個學(xué)生及其選修課程的情況SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；自然連接：例31 對例30用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade等值連接條件SnoSnameSsexSa

30、geSdeptCnoGrade若查詢列在多個表中同名，則必須指明多表查詢自身連接 自身連接：一個表與其自己進(jìn)行連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴例32 查找008號課程的先行課程名。 SELECT b.Cno,a.Cname from Course a,Course b where a.Cno=b.Cpno and b.Cno=008 多表查詢外連接外連接與普通連接的區(qū)別n普通連接操作只輸出滿足連接條件的元組n外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出 左外連接n列出左邊關(guān)系（如本例Student）中所有的元組 右外連接n列出右

31、邊關(guān)系中所有的元組 FROM 主表 LEFT OUTER JOIN 表2 ON FROM 表1 RIGHT OUTER JOIN 主表 ON 多表查詢復(fù)合條件連接復(fù)合條件連接：WHERE子句中含多個連接條件例33 查詢計算機(jī)科學(xué)與技術(shù)系（CS）選修了005號課程且成績大于90分的學(xué)生的姓名、學(xué)號及其年齡。 Select Sname, SC. Sno, Sage from Student, SC where Sdept = CS and Cno=005 and grade90 and Student. Sno= SC. Sno例34查詢所有姓“王”的學(xué)生所有成績，輸出其姓名、課程名、成績。 Se

32、lect Sname, Cname, Grade from Student, SC，Course where Sname like 王% and Student. Sno= SC. Sno and course.CnoSC.Cno 多表查詢嵌套查詢嵌套查詢概述n一個SELECT-FROM-WHERE語句稱為一個查詢塊查詢塊n將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢嵌套查詢n子查詢的限制不能使用ORDER BY子句n層層嵌套方式反映了 SQL語言的結(jié)構(gòu)化n有些嵌套查詢可以用連接運算替代不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢n由里向外 逐層處

33、理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。相關(guān)子查詢：子查詢的查詢條件依賴于父查詢n首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表n然后再取外層表的下一個元組n重復(fù)這一過程，直至外層表全部檢查完為止帶有IN謂詞的子查詢例35 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。 此查詢要求可以分步來完成 確定“劉晨”所在系名 SELECT Sdept FROM Student WHERE Sname= 劉晨 ； 查找所有在IS系學(xué)習(xí)的學(xué)生。 SELECT Sno，Sname，Sdept FROM

34、 Student WHERE Sdept= CS ；將第一步查詢嵌入到第二步查詢的條件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 劉晨 )； 帶有IN謂詞的子查詢例36 查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名 SELECT Sno，Sname 最后在Student關(guān)系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC關(guān)系中找出選 FROM SC 修了P號課程的學(xué)生學(xué)號 WHERE C

35、no IN (SELECT Cno 首先在Course關(guān)系中找出 FROM Course “信息系統(tǒng)”的課程號P WHERE Cname= 信息系統(tǒng) ) );帶有比較運算符的子查詢 當(dāng)能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，=，!=或）。與ANY或ALL謂詞配合使用帶有比較運算符的子查詢例37 假設(shè)一個學(xué)生只可能在一個系學(xué)習(xí)，并且必須屬于一個系，則在例35可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept=(SELECT Sdept FROM Student WHERE Sname= 劉晨)；子查詢一定要跟在比較

36、符之后例38找出每個學(xué)生超過他選修課程平均成績的課程號。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno)相關(guān)子查詢相關(guān)子查詢 帶有ANY或ALL謂詞的子查詢 謂詞語義nANY：任意一個值nALL：所有值需要配合使用比較運算符 ANY大于子查詢結(jié)果中的某個值 ALL大于子查詢結(jié)果中的所有值 ANY小于子查詢結(jié)果中的某個值 = ANY大于等于子查詢結(jié)果中的某個值 = ALL大于等于子查詢結(jié)果中的所有值= ANY小于等于子查詢結(jié)果中的某個值 = ALL小于等于子查詢結(jié)果中的所

37、有值= ANY等于子查詢結(jié)果中的某個值 =ALL等于子查詢結(jié)果中的所有值（通常沒有實際意義）!=（或）ANY不等于子查詢結(jié)果中的某個值!=（或）ALL不等于子查詢結(jié)果中的任何一個值帶有ANY或ALL謂詞的子查詢 例39 查詢其他系中比計算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS ; /*父查詢塊中的條件 */例40 查詢其他系中比計算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。 SELECT Sn

38、ame，Sage FROM Student WHERE Sage ALL(SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS ;帶有EXISTS謂詞的子查詢1. EXISTS謂詞n存在量詞 n帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值n由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用* ，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義2. NOT EXISTS謂詞若

39、內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值帶有EXISTS謂詞的子查詢例41 查詢所有選修了1號課程的學(xué)生姓名。 思路分析：n本查詢涉及Student和SC關(guān)系n在Student中依次取每個元組的Sno值，用此值去檢查SC關(guān)系n若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno= 1，則取此Student.Sname送入結(jié)果關(guān)系 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )；

40、帶有EXISTS謂詞的子查詢 不同形式的查詢間的替換n一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價替換n所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換 用EXISTS/NOT EXISTS實現(xiàn)全稱量詞(難點)SQL語言中沒有全稱量詞 （For all）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞： (x)P ( x( P) 帶有EXISTS謂詞的子查詢例42 查詢選修了全部課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM

41、 Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；(X)P X(P)帶有EXISTS謂詞的子查詢 用EXISTS/NOT EXISTS實現(xiàn)邏輯蘊函(難點)nSQL語言中沒有蘊函(Implication)邏輯運算n可以利用謂詞演算將邏輯蘊函謂詞等價轉(zhuǎn)換為： p q pq 帶有EXISTS謂詞的子查詢例43 查詢至少選修了學(xué)生04220139選修的全部課程的學(xué)生號碼。解題思路：n用邏輯蘊函表達(dá)：查詢學(xué)號為x的學(xué)生，對所有的課程y，只要04220139學(xué)生選修了課程y，則

42、x也選修了y。n形式化表示：用P表示謂詞 “學(xué)生04220139選修了課程y”用q表示謂詞 “學(xué)生x選修了課程y”則上述查詢?yōu)? (y) p q 帶有EXISTS謂詞的子查詢n等價變換： (y)p q (y (p q ) (y ( p q) ) y(pq)n變換后語義：不存在這樣的課程y，學(xué)生04220139選修了y，而學(xué)生x沒有選。帶有EXISTS謂詞的子查詢 n用NOT EXISTS謂詞表示： SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 04220139 AND

43、 NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；集合查詢集合操作的種類n并操作UNIONn交操作INTERSECTn差操作EXCEPT參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對應(yīng)項的數(shù)據(jù)類型也必須相同 集合查詢-并例44 查詢計算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19；nUNION：將多個查詢結(jié)果合并起來時，系統(tǒng)自動去掉重復(fù)元組。

44、nUNION ALL：將多個查詢結(jié)果合并起來時，保留重復(fù)元組 集合查詢交例45 查詢計算機(jī)科學(xué)系中年齡不大于19歲的學(xué)生SELECT *FROM StudentWHERE Sdept=CS INTERSECTSELECT *FROM StudentWHERE Sage=19 集合查詢差例46 實際上是查詢計算機(jī)科學(xué)系中年齡大于19歲的學(xué)生 SELECT * FROM Student WHERE Sdept=CS EXCEPT SELECT * FROM Student WHERE Sage =19;查詢視圖用戶角度：查詢視圖與查詢基本表相同RDBMS實現(xiàn)視圖查詢的方法n視圖消解法（View R

45、esolution）w進(jìn)行有效性檢查w轉(zhuǎn)換成等價的對基本表的查詢w執(zhí)行修正后的查詢例47 在C_S視圖中查找有成績不及格的學(xué)生課程信息。SELECT Sname，Cname，Grade FROM C_S WHERE Grade60；3.4 數(shù) 據(jù) 更 新 插入數(shù)據(jù)插入數(shù)據(jù)修改數(shù)據(jù)修改數(shù)據(jù)刪除數(shù)據(jù)刪除數(shù)據(jù) 3.4.1 插入數(shù)據(jù)兩種插入數(shù)據(jù)方式1. 插入元組2. 插入子查詢結(jié)果可以一次插入多個元組 插入元組語句格式INSERT INTO (，)VALUES ( ， )功能n將新元組插入指定表中 INTO子句n屬性列的順序可與表定義中的順序不一致n沒有指定屬性列n指定部分屬性列 VALUES子句n

46、提供的值必須與INTO子句匹配值的個數(shù)值的類型例47 將一個新學(xué)生元組（學(xué)號：04150128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲;年級：04）插入到Student表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage，SG) VALUES ( 04150128 ，陳冬，男，IS，18，04)；插入子查詢結(jié)果語句格式 INSERT INTO ( ， ) 子查詢；功能 將子查詢結(jié)果插入指定表中 INTO子句(與插入元組類似) 子查詢nSELECT子句目標(biāo)列必須與INTO子句匹配值的個數(shù)值的類型3.4.2 修改數(shù)據(jù)語句格式 UPDATE

47、SET =，= WHERE ；功能n修改指定表中滿足WHERE子句條件的元組nSET子句指定修改方式要修改的列修改后取值nWHERE子句指定要修改的元組缺省表示要修改表中的所有元組3.4.3 刪除數(shù)據(jù)語句格式 DELETE FROM WHERE ；功能n刪除指定表中滿足WHERE子句條件的元組WHERE子句n指定要刪除的元組n缺省表示要刪除表中的全部元組，表的定義仍在字典中3.5 數(shù)據(jù)控制數(shù)據(jù)控制亦稱為數(shù)據(jù)保護(hù)，包括數(shù)據(jù)的：安全性控制完整性控制并發(fā)控制恢復(fù)完整性數(shù)據(jù)庫的完整性是指數(shù)據(jù)庫中數(shù)據(jù)的正確性與相容性。SQL語言定義完整性約束條件CREATE TABLE語句ALTER TABLE語句w碼

48、w取值唯一的列w參照完整性w其他約束條件并發(fā)控制并發(fā)控制: 當(dāng)多個用戶并發(fā)地對數(shù)據(jù)庫進(jìn)行操作時，對他們加以控制、協(xié)調(diào)，以保證并發(fā)操作正確執(zhí)行，保持?jǐn)?shù)據(jù)庫的一致性。SQL語言并發(fā)控制能力： 提供事務(wù)、事務(wù)開始、事務(wù)結(jié)束、提交等概念恢復(fù)恢復(fù): 當(dāng)發(fā)生各種類型的故障導(dǎo)致數(shù)據(jù)庫處于不一致狀態(tài)時，將數(shù)據(jù)庫恢復(fù)到一致狀態(tài)的功能。SQL語言恢復(fù)功能： 提供事務(wù)回滾、重做等概念 （UNDO、REDO）安全性安全性：保護(hù)數(shù)據(jù)庫，防止不合法的使用所造成的數(shù)據(jù)泄露和破壞。保證數(shù)據(jù)安全性的主要措施存取控制：控制用戶只能存取他有權(quán)存取的數(shù)據(jù)規(guī)定不同用戶對于不同數(shù)據(jù)對象所允許執(zhí)行的操作DBMS實現(xiàn)數(shù)據(jù)安全性保護(hù)的過程用戶

49、或DBA把授權(quán)決定告知系統(tǒng)SQL的GRANT和REVOKEDBMS把授權(quán)的結(jié)果存入數(shù)據(jù)字典當(dāng)用戶提出操作請求時，DBMS根據(jù)授權(quán)定義進(jìn)行檢查，以決定是否執(zhí)行操作請求誰定義？ DBA和表的建立者（即表的屬主）如何定義？SQL語句： GRANT REVOKE3.5.1 授 權(quán)GRANT語句的一般格式： GRANT ,. ON TO ,. WITH GRANT OPTION;GRANT功能：將對指定操作對象的指定操作權(quán)限授予指定的用戶。操作權(quán)限 對象對象類型操 作 權(quán) 限 屬性列TABLESELECT,INSERT,UPDATEDELETE,ALL RIVIEGES 視圖TABLESELECT,IN

50、SERT,UPDATEDELETE,ALL RIVIEGES 基本表TABLESELECT,INSERT,UPDATEDELETE ALTER, INDEX,ALL PRIVIEGES 數(shù)據(jù)庫DATABASECREATETABWITH GRANT OPTION子句指定了WITH GRANT OPTION子句: 獲得某種權(quán)限的用戶還可以把這種權(quán)限再授予別的用戶。沒有指定WITH GRANT OPTION子句: 獲得某種權(quán)限的用戶只能使用該權(quán)限，不能傳播該權(quán)限授 權(quán)(實例)例48 把查詢Student表權(quán)限授給用戶U1 GRANT SELECT ON TABLE Student TO U1;例49

51、 把對Student表和Course表的全部權(quán)限授予用戶U2和U3 GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2, U3;例50 把對表SC的查詢權(quán)限授予所有用戶 GRANT SELECT ON TABLE SC TO PUBLIC;例51 把查詢Student表和修改學(xué)生學(xué)號的權(quán)限授給用戶U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;例52 把對表SC的INSERT權(quán)限授予U5用戶，并允許他再將此權(quán)限授予其他用戶 GRANT INSERT ON TABLE SC TO U5 W

52、ITH GRANT OPTION;3.5.2 收回權(quán)限REVOKE語句的一般格式為： REVOKE ,. ON FROM ,.;功能：從指定用戶那里收回對指定對象的指定權(quán)限例53 把用戶U4修改學(xué)生學(xué)號的權(quán)限收回REVOKE UPDATE(Sno) ON TABLE Student FROM U4;3 .6 嵌入式SQLSQL語言提供了兩種不同的使用方式：n交互式n嵌入式為什么要引入嵌入式SQLnSQL語言是非過程性語言n事務(wù)處理應(yīng)用需要高級語言這兩種方式細(xì)節(jié)上有差別，在程序設(shè)計的環(huán)境下，SQL語句要做某些必要的擴(kuò)充3.6.1 嵌入式SQL的處理過程主語言n嵌入式SQL是將SQL語句嵌入程序設(shè)

53、計語言中，被嵌入的程序設(shè)計語言稱為宿主語言，簡稱主語言，如C、C+、Java 。處理過程n預(yù)編譯方法為了區(qū)分SQL語句與主語言語句， 所有SQL語句必須加前綴EXEC SQL，以(;)結(jié)束: EXEC SQL ;嵌入式SQL的處理過程主語言程序 含ESQL語句RDBMS的預(yù)處理程序ESQL語句轉(zhuǎn)換為函數(shù)調(diào)用主語言編譯程序目標(biāo)語言程序ESQL基本處理過程3.6.2 嵌入式SQL語句與主語言之間的通信將SQL嵌入到高級語言中混合編程，程序中會含有兩種不同計算模型的語句nSQL語句 描述性的面向集合的語句 負(fù)責(zé)操縱數(shù)據(jù)庫n高級語言語句 過程性的面向記錄的語句 負(fù)責(zé)控制程序流程數(shù)據(jù)庫工作單元與源程序工

54、作單元之間的通信：n1. SQL通信區(qū)向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息使主語言能夠據(jù)此控制程序流程n2. 主變量主語言向SQL語句提供參數(shù)將SQL語句查詢數(shù)據(jù)庫的結(jié)果交主語言進(jìn)一步處理n3. 游標(biāo)解決集合性操作語言與過程性操作語言的不匹配SQL通信區(qū)SQLCA： SQL Communication AreanSQLCA是一個數(shù)據(jù)結(jié)構(gòu)SQLCA的用途nSQL語句執(zhí)行后，RDBMS反饋給應(yīng)用程序信息 描述系統(tǒng)當(dāng)前工作狀態(tài) 描述運行環(huán)境n這些信息將送到SQL通信區(qū)SQLCA中n應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來執(zhí)行的語句SQLCA使用方法：n定義SQLCA 用EXEC SQL

55、 INCLUDE SQLCA定義n使用SQLCASQLCA中有一個存放每次執(zhí)行SQL語句后返回代碼的變量SQLCODE如果SQLCODE等于預(yù)定義的常量SUCCESS，則表示SQL語句成功，否則表示出錯應(yīng)用程序每執(zhí)行完一條SQL 語句之后都應(yīng)該測試一下SQLCODE的值，以了解該SQL語句執(zhí)行情況并做相應(yīng)處理主變量 主變量n嵌入式SQL語句中可以使用主語言的程序變量來輸入或輸出數(shù)據(jù)n在SQL語句中使用的主語言程序變量簡稱為主變量（Host Variable）主變量的類型n輸入主變量n輸出主變量n一個主變量有可能既是輸入主變量又是輸出主變量指示變量：主變量和指示變量在SQL語句中使用主變量和指示

56、變量的方法n1) 說明主變量和指示變量BEGIN DECLARE SECTION . (說明主變量和指示變量) .END DECLARE SECTIONn2) 使用主變量說明之后的主變量可以在SQL語句中任何一個能夠使用表達(dá)式的地方出現(xiàn)為了與數(shù)據(jù)庫對象名（表名、視圖名、列名等）區(qū)別，SQL語句中的主變量名前要加冒號（:）作為標(biāo)志n3) 使用指示變量 指示變量前也必須加冒號標(biāo)志 必須緊跟在所指主變量之后在SQL語句之外(主語言語句中)使用主變量和指示變量的方法n可以直接引用，不必加冒號游標(biāo)（cursor）nSQL語言與主語言具有不同數(shù)據(jù)處理方式nSQL語言是面向集合的，一條SQL語句原則上可以產(chǎn)

57、生或處理多條記錄n主語言是面向記錄的，一組主變量一次只能存放一條記錄n僅使用主變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求n嵌入式SQL引入了游標(biāo)的概念，用來協(xié)調(diào)這兩種不同的處理方式n游標(biāo)是系統(tǒng)為用戶開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果,每個游標(biāo)區(qū)都有一個名字,用戶可以用SQL語句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語言進(jìn)一步處理建立和關(guān)閉數(shù)據(jù)庫連接建立數(shù)據(jù)庫連接 EXEC SQL CONNECT TO target AS connection-name USER user-name; target是要連接的數(shù)據(jù)庫服務(wù)器：l常見的服務(wù)器標(biāo)識串，如: l包含服務(wù)器標(biāo)識的S

58、QL串常量 lDEFAULT connect-name是可選的連接名，連接必須是一個有效的標(biāo)識符 在整個程序內(nèi)只有一個連接時可以不指定連接名關(guān)閉數(shù)據(jù)庫連接 EXEC SQL DISCONNECT connection;程序運行過程中可以修改當(dāng)前連接 ：EXEC SQL SET CONNECTION connection-name | DEFAULT;程序?qū)嵗? 依次檢查某個系的學(xué)生記錄，交互式更新某些學(xué)生年齡。EXEC SQL BEGIN DEC LARE SECTION; /*主變量說明開始*/ char deptname64;char HSno64;char HSname64; char

59、 HSsex64;int HSage;intNEWAGE;EXEC SQL END DECLARE SECTION; /*主變量說明結(jié)束*/long SQLCODE;EXEC SQL INCLUDE sqlca; /*定義SQL通信區(qū)*/int main(void) /*C語言主程序開始*/int count = 0;char yn; /*變量yn代表yes或no*/printf(Please choose the department name(CS/MA/IS): ); scanf(%s, deptname); /*為主變量deptname賦值*/EXEC SQL CONNECT TO T

60、ESTlocalhost:54321 USER SYSTEM /MANAGER; /*連接數(shù)據(jù)庫TEST*/EXEC SQL DECLARE SX CURSOR FOR /*定義游標(biāo)*/SELECT Sno, Sname, Ssex, Sage /*SX對應(yīng)語句的執(zhí)行結(jié)果*/FROM Student WHERE SDept = :deptname;EXEC SQL OPEN SX; /*打開游標(biāo)SX便指向查詢結(jié)果的第一行*/程序?qū)嵗齠or ( ; ; ) /*用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄*/ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex,:HSage; /*推進(jìn)游標(biāo)，將當(dāng)前數(shù)據(jù)放