《西華師范大學(xué)計(jì)算機(jī)學(xué)院.ppt》由會(huì)員分享，可在線閱讀，更多相關(guān)《西華師范大學(xué)計(jì)算機(jī)學(xué)院.ppt（52頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。
1、西華師范大學(xué)計(jì)算機(jī)學(xué)院,第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL (續(xù)2）,第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL,3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖 3.6 數(shù)據(jù)控制 3.7 嵌入式SQL 3.8 小結(jié),3.6 數(shù)據(jù)控制,概述 3.6.1 授權(quán) 3.6.2 收回權(quán)限 小結(jié),概述,數(shù)據(jù)控制亦稱為數(shù)據(jù)保護(hù)，包括數(shù)據(jù)的： 安全性控制 完整性控制 并發(fā)控制 恢復(fù),SQL語(yǔ)言的數(shù)據(jù)控制功能,SQL語(yǔ)言提供了數(shù)據(jù)控制功能，能夠在一定程度上保證數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全性、完整性，并提供了一定的并發(fā)控制及恢復(fù)能力。,1. 完整性,數(shù)據(jù)庫(kù)系統(tǒng)必須保證數(shù)據(jù)庫(kù)中的數(shù)據(jù)是完整的。在更新數(shù)
2、據(jù)庫(kù)時(shí)，關(guān)系中不能出現(xiàn)不符合完整性要求的元組，這樣才能給用戶提供正確、有效的信息。實(shí)現(xiàn)這一目的的最直接方法，是要求用戶在編寫數(shù)據(jù)庫(kù)應(yīng)用程序時(shí)，對(duì)每個(gè)插入、刪除、修改操作，都加入必要的完整性檢查代碼。,實(shí)體完整性和主碼 在SQL中，實(shí)體完整性是通過(guò)主碼（Primary Key）來(lái)實(shí)現(xiàn)的。一旦某個(gè)屬性或?qū)傩越M被定義為主碼，該主碼的每個(gè)屬性就不能為空值，并且在關(guān)系中不能出現(xiàn)主碼值完全相同的兩個(gè)元組。主碼可在定義關(guān)系的CREATE TABLE語(yǔ)句中使用PRIMARY KEY關(guān)鍵字加以定義。有兩種定義主碼的方法，一種是在屬性后增加關(guān)鍵字，另一種是在屬性表中加入額外的定義主碼的子句： PRIMARY
3、KEY(主碼屬性名表),例 在employee表中說(shuō)明eno為主碼，有兩種方法： 1)使eno為主碼 CREATE TABLE employee (eno char(4) PRIMARY KEY, name varchar(8), sex char(2), age integer, dno char(2));,2)單獨(dú)說(shuō)明主碼 CREATE TABLE employee (eno char(4), name varchar(8), sex char(2), age integer, dno char(2), PRIMARY KEY(eno));,如果關(guān)系的主碼只含有
4、單個(gè)屬性，上面的兩種方法都可以使用。如果主碼由兩個(gè)或兩個(gè)以上屬性組成，只能使用第2)種方法。如itememp表的主碼由ino和eno組成，則只能采用第2)種方法，PRIMARY KEY子句應(yīng)說(shuō)明成： PRIMARY KEY(ino，eno),SQL中，并沒(méi)有強(qiáng)制規(guī)定必須為每個(gè)關(guān)系指定主碼，但為每個(gè)關(guān)系指定主碼通常更好一些。除了主碼，SQL中使用關(guān)鍵字UNIQUE，說(shuō)明該屬性（或?qū)傩越M）的值不能重復(fù)出現(xiàn)。但說(shuō)明為UNIQUE的屬性可以定義為空值，與主碼又有所不同。并且一個(gè)表中只能有一個(gè)主碼，但可以有多個(gè)“UNIQUE”說(shuō)明。 例 說(shuō)明employee表中職工不能重名，有兩種方法： (1
5、)在name屬性后面加上UNIQUE說(shuō)明： name varchar(8) UNIQUE， (2)在屬性列表后使用UNIQUE說(shuō)明： UNIQUE（name）,外部碼約束的說(shuō)明 說(shuō)明外部碼的方法有兩種： (1)在該屬性的說(shuō)明（屬性名、類型）后直接加上關(guān)鍵字REFERENCES，后跟對(duì)應(yīng)表的主碼說(shuō)明。格式為 REFERENCES () 其中表名為參照關(guān)系名，屬性名為參照關(guān)系的主碼。 (2)在CREATE TABLE語(yǔ)句的屬性清單后，加上外部碼的說(shuō)明子句，格式為 FOREIGN KEY REFERENCES (） 其中，屬性名表中的屬性可以多于一個(gè)，但必須前后
6、對(duì)應(yīng)。,例 說(shuō)明employee表中dno為外部碼，參照關(guān)系為department。兩種方法分別如下： 1) CREATE TABLE employee (eno char(4) PRIMARY KEY, name verchar(8), sex char(2), age integer, dno char(2) REFERENCES department(dno));,2) CREATE TABLE employee (eno char(4) PRIMARY KEY, name varchar(8), sex char(2), age i
7、nteger, dno char(2), FOREIGN KEY dno REFERENCES department(dno)); 注意，在說(shuō)明dno為employee的外部碼時(shí)，department關(guān)系中的dno必須已被說(shuō)明為主碼。,用戶自定義完整性約束 用戶自定義完整性約束取決于應(yīng)用環(huán)境的需要，因此不同數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的自定義完整性要求是千差萬(wàn)別的，有些較簡(jiǎn)單，有些則較復(fù)雜。SQL中提供了非空約束、對(duì)屬性的CHECK約束、對(duì)元組的CHECK約束、觸發(fā)器等來(lái)實(shí)現(xiàn)用戶的各種完整性要求。,一、基于屬性的CHECK約束 使用CHECK（檢查）子句可保證屬性值滿足某些前提條件。CH
8、ECK子句的一般格式為 CHECK 屬性的CHECK約束既可跟在屬性的定義后，也可在定義語(yǔ)句中另增一子句加以說(shuō)明。,例：規(guī)定employee表中屬性age的值不能小于18、大于65。只需將age屬性說(shuō)明為如下的形式： age integer CHECK(age=18 AND age=18 AND age<=65) CHECK還可以模擬枚舉類型，例如，可在CREATE TABLE語(yǔ)句中用以下子句說(shuō)明屬性sex的取值只能取“男”或“女”： Sex char(2) CHECK(sex IN(男，女)),例如 CREATE TABLE publishers ( pub_i
9、d char(4) NOT NULL CHECK (pub_id IN (1389, 0736, 0877, 1622, 1756) OR pub_id LIKE 990-90-9), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT(USA) ),二、基于元組的約束 對(duì)表內(nèi)元組說(shuō)明約束時(shí)，在CREATE TABLE語(yǔ)句中的屬性表、主碼、外部碼的說(shuō)明之后加上CHECK子句。每當(dāng)對(duì)元組進(jìn)行插入或修改操作時(shí)，都要對(duì)CHECK子句的條件表達(dá)
10、式求值，如果條件為假，違背了約束，系統(tǒng)將拒絕該插入或修改操作。,例： 工資表salary的定義： CREATE TABLE salary (Eno char(4) PRIMARY KEY, Basepay decimal(7，2)， Service decimal(7，2)， Price decimal(7，2)， Rest decimal(7，2)， Insure decimal(7，2)， Fund decimal(7，2)， CHECK(Insure+Fund
11、需要在定義約束時(shí)對(duì)約束進(jìn)行命名，在約束前加上關(guān)鍵字CONSTRAINT和該約束的名稱。,例如說(shuō)明employee表中的主碼和外部碼時(shí)，分別命名為PK-employee和FK-employee： Eno char(4) CONSTRAINT PK-employee PRIMARY KEY， dno char(4) CONSTRAINT FK-employee FOREIGN KEY REFERENCES department(dno) 上例中的約束可以在定義時(shí)命名： CONSTRAINT RightSalary CHECK(Insure+Fund
12、例 刪除employee表中的外部碼約束FK-employee： ALTER TABLE employee DROP CONSTRAINT FK-employee； 例： 修改對(duì)Salary的約束RightSalary中的表達(dá)式為： Insure+Fund
13、+Fund
14、或?qū)傩越M的修改為觸發(fā)條件。事件的觸發(fā)還有兩個(gè)相關(guān)的時(shí)間：Before和After。Before觸發(fā)器是在事件發(fā)生之前觸發(fā)，After觸發(fā)器是在事件發(fā)生之后觸發(fā)。,觸發(fā)動(dòng)作實(shí)際上是一系列SQL語(yǔ)句，可以有兩種方式： (1)對(duì)被事件影響的每一行（FOR EACH ROW）每一元組執(zhí)行觸發(fā)過(guò)程，稱為行級(jí)觸發(fā)器。 (2)對(duì)整個(gè)事件只執(zhí)行一次觸發(fā)過(guò)程（FOR EACH STATEMENT），稱為語(yǔ)句級(jí)觸發(fā)器。該方式是觸發(fā)器的默認(rèn)方式。,SQL中的存儲(chǔ)過(guò)程,存儲(chǔ)過(guò)程類似于高級(jí)語(yǔ)言中的函數(shù)，同觸發(fā)器一樣，由一組SQL語(yǔ)句組成，預(yù)先編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，可供應(yīng)用程序多次調(diào)用，類似于高級(jí)語(yǔ)言中的函數(shù)。使用存儲(chǔ)過(guò)
15、程既能方便軟件開(kāi)發(fā)，又減少了解釋執(zhí)行SQL語(yǔ)句時(shí)句法分析和查詢優(yōu)化的時(shí)間，提高了效率。 如在客戶/服務(wù)器體系結(jié)構(gòu)下，應(yīng)用程序（客戶端）只需向服務(wù)器發(fā)出一次請(qǐng)求調(diào)用一個(gè)存儲(chǔ)過(guò)程，服務(wù)器上就可一次執(zhí)行一批SQL命令，中間結(jié)果不用送回客戶端，大大降低了網(wǎng)絡(luò)流量和服務(wù)器的開(kāi)銷。,2. 并發(fā)控制,并發(fā)控制: 當(dāng)多個(gè)用戶并發(fā)地對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作時(shí)，對(duì)他們加以控制、協(xié)調(diào)，以保證并發(fā)操作正確執(zhí)行，保持?jǐn)?shù)據(jù)庫(kù)的一致性。 SQL語(yǔ)言并發(fā)控制能力： 提供事務(wù)、事務(wù)開(kāi)始、事務(wù)結(jié)束、提交等概念 所謂事務(wù)，就是用戶定義的一個(gè)數(shù)據(jù)庫(kù)操作序列，是一個(gè)不可分割的工作單位。不論有無(wú)故障，數(shù)據(jù)庫(kù)系統(tǒng)必須保證事務(wù)的正確執(zhí)行或者執(zhí)行整個(gè)
16、事務(wù)或者屬于該事務(wù)的操作一個(gè)也不執(zhí)行。SQL的一個(gè)事務(wù)，可以是一條SQL語(yǔ)句、一組SQL語(yǔ)句或整個(gè)程序。一般地，一個(gè)程序中包含有多個(gè)事務(wù)。,3. 恢復(fù),恢復(fù): 當(dāng)發(fā)生各種類型的故障導(dǎo)致數(shù)據(jù)庫(kù)處于不一致?tīng)顟B(tài)時(shí)，將數(shù)據(jù)庫(kù)恢復(fù)到一致?tīng)顟B(tài)的功能。 SQL語(yǔ)言恢復(fù)功能： 提供事務(wù)回滾、重做等概念 （UNDO、REDO）,4. 安全性,安全性：保護(hù)數(shù)據(jù)庫(kù)，防止不合法的使用所造成的數(shù)據(jù)泄露和破壞。 保證數(shù)據(jù)安全性的主要措施 存取控制：控制用戶只能存取他有權(quán)存取的數(shù)據(jù) 規(guī)定不同用戶對(duì)于不同數(shù)據(jù)對(duì)象所允許執(zhí)行的操作,DBMS實(shí)現(xiàn)數(shù)據(jù)安全性保護(hù)的過(guò)程,用戶或DBA把授權(quán)決定告知系統(tǒng) SQL的GRANT和R
17、EVOKE DBMS把授權(quán)的結(jié)果存入數(shù)據(jù)字典 當(dāng)用戶提出操作請(qǐng)求時(shí)，DBMS根據(jù)授權(quán)定義進(jìn)行檢查，以決定是否執(zhí)行操作請(qǐng)求,安全性(續(xù)),誰(shuí)定義？ DBA和表的建立者（即表的屬主） 如何定義？ SQL語(yǔ)句： GRANT REVOKE,3.6.1 授 權(quán),GRANT語(yǔ)句的一般格式： GRANT ,... ON TO ,... WITH GRANT OPTION; 誰(shuí)定義？DBA和表的建立者（即表的屬主） REVOKE功能：將對(duì)指定操作對(duì)象的指定操作權(quán)限授予指定的用戶。,(1) 操作權(quán)限,(2) 用戶的權(quán)限,建表（CREATETAB）的權(quán)限:屬于DBA DBA授予--普通用戶
18、基本表或視圖的屬主擁有對(duì)該表或視圖的一切操作權(quán)限 接受權(quán)限的用戶: 一個(gè)或多個(gè)具體用戶 PUBLIC（全體用戶）,(4) WITH GRANT OPTION子句,指定了WITH GRANT OPTION子句: 獲得某種權(quán)限的用戶還可以把這種權(quán)限再授予別的用戶。 沒(méi)有指定WITH GRANT OPTION子句: 獲得某種權(quán)限的用戶只能使用該權(quán)限，不能傳播該權(quán)限,例題,例1 把查詢Student表權(quán)限授給用戶U1 GRANT SELECT ON TABLE Student TO U1;,,例題（續(xù)）,例2 把對(duì)Student表和Course表的全部權(quán)限授予用戶U2和U3 GRANT ALL
19、 PRIVILIGES ON TABLE Student, Course TO U2, U3;,例4,例題（續(xù)）,例3 把對(duì)表SC的查詢權(quán)限授予所有用戶 GRANT SELECT ON TABLE SC TO PUBLIC;,,例題（續(xù)）,例4 把查詢Student表和修改學(xué)生學(xué)號(hào)的權(quán)限授給用戶U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;,,例題（續(xù)）,例5 把對(duì)表SC的INSERT權(quán)限授予U5用戶，并允許他再將此權(quán)限授予其他用戶 GRANT INSERT ON TABLE SC TO U5 WITH GRANT O
20、PTION;,傳播權(quán)限,執(zhí)行例5后，U5不僅擁有了對(duì)表SC的INSERT權(quán)限， 還可以傳播此權(quán)限： GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION; 同樣，U6還可以將此權(quán)限授予U7： GRANT INSERT ON TABLE SC TO U7; 但U7不能再傳播此權(quán)限。 U5-- U6-- U7,例題（續(xù)）,例6 DBA把在數(shù)據(jù)庫(kù)S_C中建立表的權(quán)限授予用戶U8 GRANT CREATETAB ON DATABASE S_C TO U8;,例題,下面的語(yǔ)句將把對(duì)Northwind數(shù)據(jù)庫(kù)中customers表的查詢權(quán)限賦予用戶test：
21、 use northwind grant select on customers to test 可以用以下語(yǔ)句查詢表的存取權(quán)限 exec sp_helprotect customers,3.6 數(shù)據(jù)控制,概述 3.6.1 授權(quán) 3.6.2 收回權(quán)限 小結(jié),SQL收回權(quán)限的功能,REVOKE語(yǔ)句的一般格式為： REVOKE ,... ON FROM ,...; 功能：從指定用戶那里收回對(duì)指定對(duì)象的指定權(quán)限,例題,例7 把用戶U4修改學(xué)生學(xué)號(hào)的權(quán)限收回 REVOKE UPDATE(Sno) ON TABLE Student FROM U4;,例題（續(xù)）,例8 收回所有用戶對(duì)表SC的查詢權(quán)
22、限 REVOKE SELECT ON TABLE SC FROM PUBLIC;,例題（續(xù)）,例9 把用戶U5對(duì)SC表的INSERT權(quán)限收回 REVOKE INSERT ON TABLE SC FROM U5;,例題,下面的語(yǔ)句將收回用戶test對(duì)Northwind數(shù)據(jù)庫(kù)中customers表的查詢權(quán)限： use northwind revoke select on customers to test,權(quán)限的級(jí)聯(lián)回收,系統(tǒng)將收回直接或間接從U5處獲得的對(duì)SC 表的INSERT權(quán)限: --U5-- U6-- U7 收回U5、U6、U7獲得的對(duì)SC表的INSERT 權(quán)限: <--U5<-- U6<-- U7,小結(jié):SQL靈活的授權(quán)機(jī)制,DBA擁有對(duì)數(shù)據(jù)庫(kù)中所有對(duì)象的所有權(quán)限，并可以根據(jù)應(yīng)用的需要將不同的權(quán)限授予不同的用戶。 用戶對(duì)自己建立的基本表和視圖擁有全部的操作權(quán)限，并且可以用GRANT語(yǔ)句把其中某些權(quán)限授予其他用戶。 被授權(quán)的用戶如果有“繼續(xù)授權(quán)”的許可，還可以把獲得的權(quán)限再授予其他用戶。 所有授予出去的權(quán)力在必要時(shí)又都可以用REVOKE語(yǔ)句收回。,
西華師范大學(xué)計(jì)算機(jī)學(xué)院.ppt
