《數(shù)據(jù)庫(kù)系統(tǒng)原理》PPT課件

2020/10/5,數(shù)據(jù)庫(kù)系統(tǒng)原理,第5章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL,5.1 SQL概述及特點(diǎn) 5.2 數(shù)據(jù)定義語(yǔ)句 5.3 數(shù)據(jù)查詢語(yǔ)句 5.4 數(shù)據(jù)更新語(yǔ)句 5.5 嵌入式SQL 5.6 數(shù)據(jù)控制機(jī)制和語(yǔ)句,5.1 SQL概述及特點(diǎn),1. SQL的主要功能(1) 數(shù)據(jù)定義功能 定義關(guān)系數(shù)據(jù)庫(kù)的模式、外模式和內(nèi)模式，以實(shí)現(xiàn)對(duì)基本表、視圖以及索引文件的定義、修改和刪除等操作。(2) 數(shù)據(jù)操縱功能 包括數(shù)據(jù)查詢和數(shù)據(jù)更新兩種數(shù)據(jù)操作語(yǔ)句：數(shù)據(jù)查詢指對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)查詢、統(tǒng)計(jì)、分組、排序操作；數(shù)據(jù)更新指數(shù)據(jù)的插入、刪除、修改等數(shù)據(jù)維護(hù)操作。(3) 數(shù)據(jù)控制功能 通過(guò)對(duì)數(shù)據(jù)庫(kù)用戶的授權(quán)和收權(quán)命令來(lái)實(shí)現(xiàn)有關(guān)數(shù)據(jù)的存取控制，以保證數(shù)據(jù)庫(kù)的安全性。,2. SQL的特點(diǎn),(1) SQL具有自含式和嵌入式兩種形式。(2) SQL具有語(yǔ)言簡(jiǎn)潔、易學(xué)易用的特點(diǎn)。(3) SQL支持三級(jí)模式結(jié)構(gòu)。 全體基本表構(gòu)成了數(shù)據(jù)庫(kù)的模式。 視圖和部分基本表構(gòu)成了數(shù)據(jù)庫(kù)的外模式。 數(shù)據(jù)庫(kù)的存儲(chǔ)文件和它們的索引文件構(gòu)成了關(guān)系數(shù)據(jù)庫(kù)的內(nèi)模式。,SQL數(shù)據(jù)庫(kù)的體系結(jié)構(gòu),5.2 數(shù) 據(jù) 定 義,5.2 數(shù)據(jù)定義語(yǔ)句,5.2.1 基本表的定義和維護(hù)1. 定義基本表定義基本表語(yǔ)句的一般格式為： CREATE TABLE 庫(kù)名表名( 列名數(shù)據(jù)類型列級(jí)完整性約束條件 ， 列名數(shù)據(jù)類型列級(jí)完整性約束條件 ，n ，表級(jí)完整性約束條件 ，n )；,(1) SQL支持的數(shù)據(jù)類型,(2) 列級(jí)完整性的約束條件,針對(duì)屬性值設(shè)置的限制條件。 1) NOT NULL或NULL約束。NOT NULL約束不允許字段值為空，而NULL約束允許字段值為空。 2) UNIQUE約束。惟一性約束，即不允許列中出現(xiàn)重復(fù)的屬性值。 3) DEFAULT約束。默認(rèn)值約束。 DEFAULT約束名默認(rèn)值FOR列名 4) CHECK約束。檢查約束。CONSTRAINT約束名CHECK (約束條件表達(dá)式),(3) 表級(jí)完整性約束條件,涉及到關(guān)系中多個(gè)列的限制條件。1) UNIQUE約束。惟一性約束。2) PRIMARY KEY約束。定義主碼，保證惟一性和非空性。 CONTRAINT約束名PRIMARY KEY CLUSTERED (列組)3) FOREIGN KEY約束。用于定義參照完整性。 CONTRAINT約束名FOREIGN KEY(外碼) REFERENCES被參照表名(與外碼對(duì)應(yīng)的主碼名),CREATE TABLE 學(xué)生(學(xué)號(hào) CHAR(5) NOT NULL UNIQUE， 姓名 CHAR(8) NOT NULL，年齡 SMALLINT， 性別 CHAR(2)，所在系 CHAR(20)， DEFAULT C1 20 FOR 年齡， CONSTRAINT C2 CHECK(性別 IN (男，女)； CREATE TABLE 課程(課程號(hào) CHAR(5) PRIMARY KEY， 課程名 CHAR(20)，先行課 CHAR(5)； CREATE TABLE 選課(學(xué)號(hào) CHAR(5)，課程號(hào) CHAR(5)， 成績(jī) SMALLINT， CONSTRAINT C3 CHECK(成績(jī) BETWEEN 0 AND 100)， CONSTRAINT C4 PRIMARY KEY(學(xué)號(hào)，課程號(hào))， CONSTRAINT C5 FOREIGN KEY(學(xué)號(hào)) REFERENCES 學(xué)生(學(xué)號(hào))， CONSTRAINT C6 FOREIGN KEY(課程號(hào)) REFERENCES 課程(課程號(hào))；,【例5-1】,建立基本表： 學(xué)生(學(xué)號(hào)，姓名，年齡，性別，所在系)； 課程(課程號(hào)，課程名，先行課)； 選課(學(xué)號(hào)，課程號(hào)，成績(jī)).,2. 修改基本表和刪除基本表,ALTER TABLE表名 ADD(新列名數(shù)據(jù)類型完整性約束 ，n) DROP完整性約束名 MODIFY(列名數(shù)據(jù)類型，n)；(1) 使用ADD子句增加新列【例5-2】向課程表中增加“學(xué)時(shí)”字段。 ALTER TABLE 課程 ADD 學(xué)時(shí) SMALLINT；(2) 使用MODIFY子句修改列的原定義(3) 使用DROP子句刪除指定的完整性約束條件【例5-3】刪除學(xué)生表中對(duì)年齡的默認(rèn)值的定義。 ALTER TABLE 學(xué)生 DROP C1；刪除基本表的一般格式為： DROP TABLE表名；,5.2.2 索引的定義和維護(hù),1. 索引的作用1) 使用索引可以明顯地加快數(shù)據(jù)查詢的速度。2) 使用索引可保證數(shù)據(jù)的惟一性。3) 使用索引可以加快連接速度。 2. 建立索引的原則1) 索引的建立和維護(hù)由DBA和DBMS完成。2) 大表應(yīng)當(dāng)建索引，小表則不必建索引。3) 對(duì)于一個(gè)基本表，不要建立過(guò)多的索引。4) 根據(jù)查詢要求建索引。,3. 建立和刪除索引的格式,建立格式為：CREATE UNIQUE CLUSTER INDEX索引名 ON表名(列名次序，列名次序)； 刪除索引格式為： DROP INDEX 索引名； 【例5-4】為學(xué)生_課程數(shù)據(jù)庫(kù)中的學(xué)生、課程和選課三個(gè)表建立索引。其中，學(xué)生表按學(xué)號(hào)升序建立索引；課程表按課程號(hào)升序建惟一索引；選課表按學(xué)號(hào)升序和課程號(hào)降序建惟一索引。CREATE UNIQUE INDEX 學(xué)號(hào) ON 學(xué)生(學(xué)號(hào))；CREATE UNIQUE INDEX 課程號(hào) ON 課程(課程號(hào))；CREATE UNIQUE INDEX 選課號(hào) ON 選課(學(xué)號(hào) ASC，課程號(hào) DESC)；,5.2.3 視圖的定義和維護(hù),1. 視圖的優(yōu)點(diǎn)1) 視圖能夠簡(jiǎn)化用戶的操作。2) 視圖機(jī)制可以使用戶以不同的方式看待同一數(shù)據(jù)。3) 視圖對(duì)數(shù)據(jù)庫(kù)的重構(gòu)提供了一定程度的邏輯獨(dú)立性。4) 視圖可以對(duì)機(jī)密的數(shù)據(jù)提供安全保護(hù)。 2. 視圖定義的格式一般格式為： CREATE VIEW視圖名(列名組) AS子查詢 WITH CHECK OPTION；,視圖舉例,【例5-6】建立計(jì)算機(jī)系學(xué)生的視圖。 CREATE VIEW 計(jì)算機(jī)系學(xué)生 AS SELECT 學(xué)號(hào)，姓名，年齡 FROM 學(xué)生 WHERE 所在系=計(jì)算機(jī)系； 【例5-7】由學(xué)生、課程和選課三個(gè)表，定義一個(gè)計(jì)算機(jī)系的學(xué)生成績(jī)視圖，其屬性包括學(xué)號(hào)、姓名、課程名和成績(jī)。 CREATE VIEW 學(xué)生成績(jī)(學(xué)號(hào)，姓名，課程名，成績(jī))AS SELECT 學(xué)生.學(xué)號(hào)，學(xué)生.姓名，課程.課程名，選課.成績(jī) FORM 學(xué)生，課程，選課 WHERE 學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào) AND 程.課程名=選課.課程號(hào) AND 學(xué)生.所在系=計(jì)算機(jī)系；,3. 視圖的刪除 、查詢和維護(hù),視圖刪除語(yǔ)句的一般格式為： DROP VIEW視圖名； 視圖可以和基本表一樣被查詢，其使用方法與基本表相同，但利用視圖進(jìn)行數(shù)據(jù)增、刪、改操作，會(huì)受到一定的限制。,5.3 數(shù)據(jù)查詢語(yǔ)句,5.3.1 數(shù)據(jù)查詢的基本語(yǔ)法 1. SELECT語(yǔ)句的語(yǔ)法 SELECT目標(biāo)列組 FROM數(shù)據(jù)源 WHERE元組選擇條件 GROUP BY分列組HAVING 組選擇條件 ORDER BY排序列1排序要求1 ，n；,語(yǔ)法說(shuō)明,(1) SELECT子句：指明目標(biāo)列（字段、表達(dá)式、函數(shù)表達(dá)式、常量）?；颈碇邢嗤牧忻硎緸椋罕砻?列名 (2) FROM子句：指明數(shù)據(jù)源。表間用“，”分割。數(shù)據(jù)源不在當(dāng)前數(shù)據(jù)庫(kù)中，使用“數(shù)據(jù)庫(kù)名.表名”表示。一表多用，用別名標(biāo)識(shí)。定義表別名：表名別名 (3) WHERE子句：元組選擇條件。 (4) GROUP BY子句：結(jié)果集分組。當(dāng)目標(biāo)列中有統(tǒng)計(jì)函數(shù)，則統(tǒng)計(jì)為分組統(tǒng)計(jì)，否則為對(duì)整個(gè)結(jié)果集統(tǒng)計(jì)。子句后帶上HAVING子句表達(dá)組選擇條件（帶函數(shù)的表達(dá)式）。 (5) ORDER BY子句：排序。當(dāng)排序要求為ASC時(shí)升序排序；排序要求為DESC時(shí)降序排列。,2. SELECT語(yǔ)句的操作符,(1) 算術(shù)操作符+（加號(hào)）、（減號(hào)）、*（乘號(hào)）和 /（除號(hào)）。 (2) 比較操作符=（等于）、（大于）、=（大于等于）、!=（不等于）、（小于大于）、!（不大于）和 !<（不小于），共9種操作符。,(3) 邏輯操作符,(4) 組合查詢操作符和其他SQL操作符,查詢1組合操作符查詢21) UNION：并查詢，并在結(jié)果集中去掉重復(fù)行。2) MINUS：差查詢操作。3) INTERSECT：交查詢操作。4) *：取全部字段。格式為： * 或表名.*5) ALL：全部。保留重復(fù)值（有統(tǒng)計(jì)函數(shù)時(shí)要求計(jì)算重復(fù)值）。 格式為： ALL字段或 ALL字段組6) DISTINCT：去掉重復(fù)值。在結(jié)果集中去掉重復(fù)值，或在統(tǒng)計(jì)函數(shù)中不計(jì)重復(fù)值。 格式為： DISTINCT字段或 DISTINCT字段組,5.3.2 數(shù)據(jù)查詢實(shí)例,學(xué)生課程庫(kù)結(jié)構(gòu)為：學(xué)生(學(xué)號(hào)，姓名，年齡，所在系)； 課程(課程號(hào)，課程名，先行課)； 選課(學(xué)號(hào)，課程號(hào)，成績(jī)). 1. 簡(jiǎn)單查詢：查詢過(guò)程中只涉及到一個(gè)表的查詢語(yǔ)句?！纠?-9】求數(shù)學(xué)系學(xué)生的學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào)，姓名 FROM 學(xué)生 WHERE 所在系=數(shù)學(xué)系；【例5-10】求選修了課程的學(xué)生學(xué)號(hào)。 SELECT DISTINCT 學(xué)號(hào) FROM 選課；,例子,【例5-11】求選修C1課程的學(xué)生學(xué)號(hào)和成績(jī)，并要求對(duì)查詢結(jié)果按成績(jī)的降序排列，如果成績(jī)相同則按學(xué)號(hào)的升序排列。 SELECT 學(xué)號(hào)，成績(jī) FROM 選課 WHERE 課程號(hào)=C1 ORDER BY 成績(jī) DESC，學(xué)號(hào) ASC； 【例5-12】求選修課程C1且成績(jī)?cè)?090之間的學(xué)生學(xué)號(hào)和成績(jī)，并將成績(jī)乘以系數(shù)0.8輸出。 SELECT 學(xué)號(hào)，成績(jī)*0.8 FROM 選課 WHERE 課程號(hào) = C1 AND 成績(jī) BETWEEN 80 AND 90；,例子,【例5-13】求數(shù)學(xué)系或計(jì)算機(jī)系姓張的學(xué)生的信息。 SELECT * FROM 學(xué)生 WHERE 所在系 IN ( 數(shù)學(xué)系，計(jì)算機(jī)系 ) AND 姓名 LIKE 張%； 【例5-14】求缺少了成績(jī)的學(xué)生的學(xué)號(hào)和課程號(hào)。 SELECT 學(xué)號(hào)，課程號(hào) FROM 選課 WHERE 成績(jī) IS NULL；,2. 連接查詢,連接查詢中的連接條件通過(guò)WHERE子句表達(dá)，連接條件和元組選擇條件之間用AND（與）操作符銜接。 (1) 等值連接和非等值連接 表名1.列名1比較運(yùn)算符表名2.列名2比較運(yùn)算符：=、=、<= 和!=；列名稱為連接字段?！纠?-15】查詢每個(gè)學(xué)生的情況以及他(她)所選修的課程。 SELECT 學(xué)生.*，選課.* FROM 學(xué)生，選課 WHERE 學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào)；,例子,【例5-16】求學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)。 SELECT 學(xué)生.學(xué)號(hào)，姓名，課程名，成績(jī) FROM 學(xué)生，課程，選課 WHERE 學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào) AND 課程.課程號(hào)=選課.課程號(hào)； 【例5-17】求選修C1課程且成績(jī)?yōu)?0分以上的學(xué)生學(xué)號(hào)、姓名及成績(jī)。 SELECT 學(xué)生.學(xué)號(hào)，姓名，成績(jī) FROM 學(xué)生，選課 WHERE 學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào) AND 課程號(hào)=C1AND 成績(jī)90；,例子,(2) 自身連接例如，課程表中的先行課是在上學(xué)期應(yīng)開設(shè)的，先行課的先行課，即間接先行課應(yīng)提前一學(xué)年開設(shè)。如果求查詢某門課的間接先行課或全部課程的間接先行課，就需要對(duì)課程表進(jìn)行自身連接。,課程的先行關(guān)系鏈為：C5C4C3C2C1， 課程的間接關(guān)系鏈為：C5C3C1。,A,B,結(jié)果,【例5-18】,查詢每一門課的間接先行課。 SELECT A.課程號(hào)，A.課程名，B.先行課 FROM 課程 A，課程 B WHERE A.先行課=B.課程號(hào),(3) 外部連接,左外部連接操作是在結(jié)果集中保留連接表達(dá)式左表中的非匹配記錄；右外部連接操作是在結(jié)果集中保留連接表達(dá)式右表中的非匹配記錄。外部連接符號(hào)為“*=”，右外部連接符號(hào)為“=*”。外部連接中不匹配的分量用NULL表示。,職工表 部門表,連接的結(jié)果集,內(nèi)連接的結(jié)果集,左外部連接的結(jié)果集,內(nèi)連接： SELECT 職工.*，部門名稱，電話 FROM 職工，部門 WHERE 職工.所在部門= 部門.部門號(hào)； 左外部連接： SELECT 職工.*，部門名稱，電話 FROM 職工，部門 WHERE 職工.所在部門*= 部門.部門號(hào)； 右外部連接： SELECT 職工.*，部門名稱，電話 FROM 職工，部門 WHERE 職工.所在部門 =*部門.部門號(hào)；,【例5-19】,用SQL表達(dá)職工和部門之間的內(nèi)連接、左外部連接和右外部連接的語(yǔ)句,3. 嵌套查詢,使用IN操作符的嵌套查詢【例5-20】求選修了高等數(shù)學(xué)的學(xué)生學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào)，姓名 FROM 學(xué)生 WHERE 學(xué)號(hào) IN ( SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào) IN ( SELECT 課程號(hào) FROM 課程 WHERE 課程名=高等數(shù)學(xué) )；該題也可以使用下面的連接查詢表達(dá)。 SELECT 學(xué)生.學(xué)號(hào)，姓名 FROM 學(xué)生，課程，選課 WHERE 學(xué)生.學(xué)號(hào)=課程.學(xué)號(hào) AND 課程.課程號(hào)=選課.課程號(hào) AND 課程.課程名=高等數(shù)學(xué)；,(2) 使用比較符的嵌套查詢,【例5-21】求C1課程的成績(jī)高于張三的學(xué)生學(xué)號(hào)和成績(jī)。 SELECT 學(xué)號(hào)，成績(jī) FROM 選課 WHERE 課程號(hào)=C1 AND 成績(jī) ( SELEC 成績(jī) FROM 選課 WHERE 課程號(hào)=C1AND 學(xué)號(hào)= (SELECT 學(xué)號(hào) FROM 學(xué)生 WHERE 姓名=張三)；,(3) 使用ANY或ALL操作符的嵌套查詢,格式為：字段比較符ANY|ALL子查詢,例子,【例5-22】求其他系中比計(jì)算機(jī)系某一學(xué)生年齡小的學(xué)生。 SELECT * FROM 學(xué)生 WHERE 年齡 計(jì)算機(jī)系； 【例5-23】求其他系中比計(jì)算機(jī)系學(xué)生年齡都小的學(xué)生。 SELECT * FROM 學(xué)生 WHERE 年齡 計(jì)算機(jī)系；,(4) 使用EXISTS操作符的嵌套查詢,【例5-24】求選修了C2課程的學(xué)生姓名。 SELECT 姓名 FROM 學(xué)生 WHERE EXISTS (SELECT * FROM 選課 WHERE 學(xué)生.學(xué)號(hào)=學(xué)號(hào) AND 課程號(hào)=C2)； 【例5-25】求沒有選修C2課程的學(xué)生姓名。 SELECT 姓名 FROM 學(xué)生 WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學(xué)生.學(xué)號(hào)=學(xué)號(hào) AND 課程號(hào)=C2)；,例子,【例5-26】查詢選修了全部課程的學(xué)生的姓名。 SELECT 姓名 FROM 學(xué)生 WHERE NOT EXISTS (SELECT * FROM 課程 WHERE NOT EXISTS (SELECT * FROM 選課 WHERE 學(xué)生.學(xué)號(hào)=學(xué)號(hào) AND 課程.課程號(hào)=課程號(hào))；,例子,【例5-27】求至少選修了學(xué)號(hào)為“S2”的學(xué)生所選修的全部課程的學(xué)生學(xué)號(hào)和姓名。 SELECT 學(xué)號(hào)，姓名 FROM 學(xué)生 WHERE NOT EXISTS (SELECT * FROM 選課 選課1 WHERE 選課1.學(xué)號(hào)=S2 AND NOT EXISTS (SELECT * FROM 選課 選課2 WHERE 學(xué)生.學(xué)號(hào)=選課2.學(xué)號(hào) AND 選課2 .課程號(hào)=選課1.課程號(hào))；,4. 組合查詢,【例5-28】求選修了C1課程或選修了C2課程的學(xué)生學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C1 UNION SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C2 【例5-29】求選修C1課程，并且也選修C2課程的學(xué)生學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C1 INTERSECT SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C2；,【例5-30】求選修了C1課程但沒有選修C2課程的學(xué)生學(xué)號(hào)。,SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C1 MINUS SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=C2；本例也可以用下面的EXISTS嵌套查詢表示。 SELECT 學(xué)號(hào) FROM 選課 選課1 WHERE 課程號(hào)=C1 AND NOT EXISTS (SELECT 學(xué)號(hào) FROM 選課 選課2 WHERE 選課1.學(xué)號(hào)=選課2.學(xué)號(hào) AND 選課2.課程號(hào)=C2)；,5. 使用分組和SQL函數(shù)查詢,【例5-31】求學(xué)生的總?cè)藬?shù)。 SELECT COUNT (*) FROM 學(xué)生；,例子,【例5-32】求選修了課程的學(xué)生人數(shù)。 SELECT COUNT(DISTINCT 學(xué)號(hào)) FROM 選課； 【例5-33】求課程和選修該課程的人數(shù)。 SELECT 課程號(hào)，COUNT(學(xué)號(hào)) FROM 選課 GROUP BY 課程號(hào)； 【例5-34】求選修課超過(guò)3門課的學(xué)生學(xué)號(hào)。 SELECT 學(xué)號(hào) FROM 選課 GROUP BY 學(xué)號(hào) HAVING COUNT(*)3；,5.4 數(shù)據(jù)更新語(yǔ)句,5.4.1 數(shù)據(jù)插入語(yǔ)句1. 使用常量插入單個(gè)元組格式為： INSERT INTO表名(屬性列1，屬性列2) VALUES (常量1，常量2)；,例子,【例5-35】將一個(gè)新學(xué)生記錄(學(xué)號(hào)：98010，姓名：張三，年齡：20，所在系：計(jì)算機(jī)系 )插入到學(xué)生表中。INSERTINTO 學(xué)生VALUES (98010，張三，20，計(jì)算機(jī)系)； 【例5-36】插入一條選課記錄(學(xué)號(hào)：98011，課程號(hào)：C10，成績(jī)不詳)。 INSERT INTO 選課 (學(xué)號(hào)，課程號(hào)) VALUES (98011，C10)；,2. 在表中插入子查詢的結(jié)果集,INSERT INTO表名(屬性列1，屬性列2) 子查詢；【例5-37】求每個(gè)系學(xué)生的平均年齡，把結(jié)果存入數(shù)據(jù)庫(kù)中。 CREATE TABLE 系平均年齡 (系名稱CHAR(20)， 平均年齡SMALLINT)； INSERT INTO 系平均年齡 SELECT 所在系，AVG(ALL年齡) FROM 學(xué)生 GROUP BY 所在系；,5.4.2 數(shù)據(jù)修改語(yǔ)句,UPDATE表名 SET列名=表達(dá)式，列名=表達(dá)式，n WHERE條件；【例5-38】將學(xué)生表中全部學(xué)生的年齡加上2歲。 UPDATE 學(xué)生 SET 年齡=年齡+2；【例5-39】將選課表中的數(shù)據(jù)庫(kù)課程的成績(jī)乘以1.2。 UPDATE 選課 SET 成績(jī)= 成績(jī)*1.2 WHERE 課程號(hào)= (SELECT 課程號(hào) FROM 課程 WHERE 課程名= 數(shù)據(jù)庫(kù) )；,5.4.3 數(shù)據(jù)刪除語(yǔ)句,DELETE FROM表名 WHERE條件；【例5-40】刪除藝術(shù)系的學(xué)生記錄及選課記錄。 DELETE FROM 選課 WHERE 學(xué)號(hào) IN (SELECT 學(xué)號(hào) FROM 學(xué)生 WHERE 所在系=藝術(shù)系)； DELETE FROM 學(xué)生 WHERE 所在系=藝術(shù)系；,5.5 嵌入式SQL,5.5.1 嵌入式SQL的特點(diǎn)1. 嵌入式SQL應(yīng)注意的問題1) SQL和主語(yǔ)言的配合問題。2) 合理選擇主語(yǔ)言。2. SQL嵌入主語(yǔ)言時(shí)必須解決的三個(gè)問題(1) 如何區(qū)別SQL和主語(yǔ)言(2) 使數(shù)據(jù)庫(kù)的工作單元與程序工作單元之間能夠通信1) 主語(yǔ)言通過(guò)主變量向SQL語(yǔ)句提供參數(shù)。 2) SQL語(yǔ)句的當(dāng)前工作狀態(tài)和運(yùn)行環(huán)境數(shù)據(jù)要返饋給應(yīng)用程序。(3) 使用游標(biāo)解決SQL一次一集合的操作與主語(yǔ)言一次一記錄操作的矛盾,5.5.2 不用游標(biāo)的SQL語(yǔ)句,1. 幾種不需要使用游標(biāo)的SQL語(yǔ)句(1) 用于說(shuō)明主變量的說(shuō)明性語(yǔ)句 SQL的說(shuō)明性語(yǔ)句主要有兩條： EXEC SQL BEGIN DECLARE SECTION； EXEC SQL END DECLARE SECTION；(2) 數(shù)據(jù)定義和數(shù)據(jù)控制語(yǔ)句(3) 查詢結(jié)果為單記錄的查詢語(yǔ)句(4) 數(shù)據(jù)的插入語(yǔ)句和某些數(shù)據(jù)刪除、修改語(yǔ)句獨(dú)立的數(shù)據(jù)刪除和修改語(yǔ)句不需要使用游標(biāo)；與查詢語(yǔ)句配合，刪除或修改查詢到的當(dāng)前記錄的操作，與游標(biāo)有關(guān)。,2. 不用游標(biāo)的查詢語(yǔ)句,EXEC SQL SELECT ALL|DISTINCT目標(biāo)列表達(dá)式，n INTO主變量指示變量，n FROM表名或視圖名，n WHERE條件表達(dá)式；1) 在語(yǔ)句開始前要加EXEC SQL前綴。2) 該查詢語(yǔ)句中又?jǐn)U充了INTO子句。3) 在WHERE子句的條件表達(dá)式中可以使用主變量。4) 由于查詢的結(jié)果集中只有一條記錄，該語(yǔ)句中不必有排序和分組子句。5) INTO子句中的主變量后面跟有指示變量時(shí)：結(jié)果列值為NULL，指示變量為負(fù)值，結(jié)果列不向該主變量賦值；傳遞正常，指示變量的值為0；主變量寬度不夠，則指示變量的值為數(shù)據(jù)截?cái)嗲暗膶挾取?例子,【例5-41】查詢學(xué)號(hào)為主變量givesno、課號(hào)為主變量givecno的值的學(xué)生選課記錄。 EXEC SQL SELECT 學(xué)號(hào)，課程號(hào)，成績(jī) INTO :Sno，:Cno，:grade :gradeid FROM 選課 WHERE 學(xué)號(hào)= :givesno AND 課程號(hào)= :givecno；3. 不用游標(biāo)的數(shù)據(jù)維護(hù)語(yǔ)句(1) 不用游標(biāo)的數(shù)據(jù)刪除語(yǔ)句【例5-42】刪除學(xué)號(hào)由主變量Sno決定的學(xué)生記錄。 EXEC SQL DELETE FROM 學(xué)生 WHERE 學(xué)號(hào)= :Sno；,(2) 不用游標(biāo)的數(shù)據(jù)修改語(yǔ)句,【例5-43】將計(jì)算機(jī)系所有學(xué)生的年齡都加上主變量Raise。 EXEC SQL UPDATE 學(xué)生 SET 年齡=年齡+:Raise WHERE 所在系=計(jì)算機(jī)系； 【例5-44】將計(jì)算機(jī)系學(xué)生的年齡置空。 Raiseid=-1 EXEC SQL UPDATE 學(xué)生 SET 年齡=年齡+:Raise :Raiseid； (3) 不用游標(biāo)的數(shù)據(jù)插入語(yǔ)句 【例5-45】將學(xué)號(hào)為主變量Sno、課程號(hào)為Cno的選課記錄，插到庫(kù)中。 EXEC SQL INSERT INTO 選課 VALUES (:Sno， :Cno)；,5.5.3 使用游標(biāo)的SQL,(1) 定義游標(biāo)命令 EXEC SQL DECLARE游標(biāo)名CURSOR FOR子查詢 FOR UPDATE OF字段名1，n；刪除和修改數(shù)據(jù)的語(yǔ)句中，WHERE為： WHERE CURRENT OF游標(biāo)名【例5-46】定義按主變量DEPT查詢系里學(xué)生的游標(biāo)。 EXEC SQL DECLARE SX CURSOR FOR SELECT * FROM 學(xué)生 WHERE 所在系= :DEPT；,(2) 打開、推進(jìn)和關(guān)閉游標(biāo)命令,EXEC SQL OPEN 游標(biāo)名； 執(zhí)行對(duì)應(yīng)的查詢語(yǔ)句，并將游標(biāo)指向結(jié)果集的第一條記錄前。打開的游標(biāo)處于活動(dòng)狀態(tài)，可以被推進(jìn)。 EXEC SQL FETCH游標(biāo)名INTO主變量組； 將游標(biāo)下移一行，讀出當(dāng)前的記錄，將當(dāng)前記錄的各數(shù)據(jù)項(xiàng)值放到INTO后的主變量組中。 EXEC SQL CLOSE 游標(biāo)名； 【例5-47】打開SX游標(biāo)。 DEPT= 計(jì)算機(jī)系 EXEC SQL OPEN SX； 【例5-48】將打開的指向系的游標(biāo)向前推進(jìn)。 EXEC SQL FETCH SX INTO :Sno，:Sname，:Sage，:Sdept；,5.6 數(shù)據(jù)控制機(jī)制和語(yǔ)句,5.6.1 數(shù)據(jù)控制機(jī)制 1. 授權(quán)定義 具有授權(quán)資格的用戶，如DBA或DBO，通過(guò)數(shù)據(jù)控制語(yǔ)言DCL，將授權(quán)決定告知數(shù)據(jù)庫(kù)管理系統(tǒng)。 2. 存權(quán)處理 DBMS把授權(quán)的結(jié)果編譯后存入數(shù)據(jù)字典中。 3. 查權(quán)操作 當(dāng)提出操作請(qǐng)求時(shí)，系統(tǒng)在數(shù)據(jù)字典中查找該用戶的數(shù)據(jù)操作權(quán)限，當(dāng)擁有該操作權(quán)時(shí)執(zhí)行其操作，否則系統(tǒng)將拒絕其操作。,5.6.2 數(shù)據(jù)控制語(yǔ)句,1. 授權(quán)語(yǔ)句 GRANT系統(tǒng)特權(quán)組To用戶組| PUBLIC WITH GRANT OPTION；1) PUBLIC指數(shù)據(jù)庫(kù)的所有用戶。2) WITH GRANT OPTION:獲得權(quán)限的用戶可以把該權(quán)限再授予別的用戶。 GRANT ALL PRIVILIGES|對(duì)象特權(quán)組ON對(duì)象名 TO用戶組| PUBLIC WITH GRANT OPTION；1) ALL PRIVILIGES指所有的對(duì)象特權(quán)。2) 對(duì)象名指操作的對(duì)象標(biāo)識(shí)，如表名、視圖名和過(guò)程名等。,例子,【例5-51】把修改學(xué)生學(xué)號(hào)和查詢學(xué)生表的權(quán)力授予用戶王平。 GRANT UPDATE(學(xué)號(hào))，SELECT ON 學(xué)生 TO 王平； 【例5-52】把建立數(shù)據(jù)庫(kù)和備份數(shù)據(jù)庫(kù)的權(quán)力賦給用戶王平。 GRANT CREATE DATABASE， BACKUP DATABASE TO 王平；,2. 收權(quán)語(yǔ)句,REVOKE語(yǔ)句的一般格式為： REVOKE權(quán)限組|ALL PRIVILIGES ON對(duì)象名 FROM用戶名組| PUBLIC；其中：ON子句用于指定被收回特權(quán)的對(duì)象；ALL PRIVILIGES指收回所有特權(quán)；PUBLIC指所有用戶 【例5-53】將用戶王平的可以在學(xué)生表中修改學(xué)生學(xué)號(hào)的權(quán)利收回。 REVOKE UPDATE(學(xué)號(hào)) ON 學(xué)生 FROM 王平；,3. 拒絕訪問語(yǔ)句,拒絕訪問語(yǔ)句的一般格式為： DENY ALL PRIVILIGES | 權(quán)限組 ON 對(duì)象名 TO 用戶組 | PUBLIC；其中：ON子句用于說(shuō)明對(duì)象特權(quán)的對(duì)象名；對(duì)象名指的是表名、視圖名、視圖和表的字段名或者過(guò)程名。,本章結(jié)束，謝謝,