1、第三章第三章 SQLSQL補充內(nèi)容補充內(nèi)容 -數(shù)據(jù)庫的完整性數(shù)據(jù)庫的完整性SQL中的完整性約束完整性約束的目的是保證用戶對數(shù)據(jù)庫進行修改時不會破壞數(shù)據(jù)的一致性（防止對數(shù)據(jù)的意外破壞）。注意：數(shù)據(jù)的完整性與安全性的概念區(qū)別。前者是為了防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)（防止數(shù)據(jù)垃圾），后者是防止對數(shù)據(jù)的非法存取。完整性約束條件 NOT NULL 約束 缺省值 UNIQUE 約束 PRIMARY KEY 約束 FOREIGN KEY 約束 CHECK 約束NOT NULL 約束 限定了表的某一列中必須有值；缺省值 在數(shù)據(jù)錄入時，若沒有輸入數(shù)據(jù)，數(shù)據(jù)庫自動輸入一個預先規(guī)定的值；UNIQUE 約束 該列

2、的值在表中不能重復出現(xiàn)（可以有空值）；PRIMARY KEY 約束 用來唯一標識表中的一行（不能有空值），必須是唯一的列（應選擇一般不會改變、短的和數(shù)值型的列）；CHECK 約束 根據(jù)邏輯表達式來限定值域；實體完整性實體完整性 例1： 將student表中的Sno屬性定義為碼 CREATE TABLE student（sno char(9) primary key, sname char(20) not null, sex char(2),）CREATE TABLE student（sno char(9) primary key, sname char(20) not null, sex ch

3、ar(2),primary key(sno)）;實體完整性實體完整性例2： 將SC表中的sno,cno屬性組定義為碼CREATE TABLE sc(sno char(9) not null, cno char(4) not null, grade smallint,primary key(sno,cno);實體完整性實體完整性參照完整性參照完整性例3： 定義參照完整性CREATE TABLE sc(sno char(9) not null, cno char(4) not null, grade smallint,primary key(sno,cno)Foreign key(sno) ref

4、rences Student(sno),Foreign key(cno) refrences course(cno),);用戶自定義完整性用戶自定義完整性例4：定義SC表時，說明屬性Sno,Cno,Grade 不允許取空值。CREATE TABLE sc(sno char(9) not null, cno char(4) not null,Grade smallint not null,Primary key(sno,cno);用戶自定義完整性用戶自定義完整性例5:建立部門DEPT表，要求部門名稱dname列取值唯一，部門編號deptno列為主碼。Create table dept(deptn

5、o numeric(2),Dname char(9) unique,Location char(10),Primary key(deptno);用戶自定義完整性用戶自定義完整性例6:student表中的sex只能取“男”或“女”。CREATE TABLE student(sno char(9) primary key, sname char(20) not null, sex char(2) check(sex in(男,女),);用戶自定義完整性用戶自定義完整性例sc表中grade的值應該在0和100之間.CREATE TABLE sc(sno char(9) not null, cno c

6、har(4) not null,Grade smallint check(grade=0 and grade=100),Primary key(sno,cno),Foreign key(sno) references Student(sno),Foreign key(cno) references course(cno);完整性約束命名子句完整性約束命名子句1.基本語法 constraint primary key|foreign key|checkCREATE TABLE SC(Sno CHAR(9)， Cno CHAR(4) constraint c1 check(cno between

7、1 and 1000) ， Grade SMALLINT constraint c2 check(grade=0 and grade =0 and grade=100) Alter table sc drop constraint c1;觸發(fā)器觸發(fā)器觸發(fā)器（Trigger）是用戶定義在關系表上的一類由事件驅(qū)動的特殊過程。一旦定義，任何用戶對表的增、刪、改操作均由服務器自動激活相應的觸發(fā)器，在核心層進行集中的完整性控制。觸發(fā)器類似約束，但比約束更加靈活，可以實施比約束更為復雜的檢查和操作，具有更精細和更強大的數(shù)據(jù)控制能力。 定義觸發(fā)器定義觸發(fā)器 CREATE TRIGGER BEFORE/AFT

8、ER DELETE/INSERT/UPDATE OF ON FOR EACH ROW/STATEMENT BEGIN PL/SQL； END；重建表示觸發(fā)動作發(fā)生在觸發(fā)事件表示觸發(fā)動作發(fā)生在觸發(fā)事件“DELETE/INSERT/UPDATE OF ”執(zhí)行前或者后。執(zhí)行前或者后。 說明觸發(fā)器類型，說明觸發(fā)器類型，即行級觸發(fā)器或者即行級觸發(fā)器或者語句級觸發(fā)器語句級觸發(fā)器 觸發(fā)動觸發(fā)動作作為了更好地理解和使用觸發(fā)器，注意以下幾點：觸發(fā)器總是在某個數(shù)據(jù)行被刪除、插入或更新之前或者之后被調(diào)用。觸發(fā)器總是與某個數(shù)據(jù)庫表關聯(lián)的。每個數(shù)據(jù)庫表可以有一個或者多個觸發(fā)器。觸發(fā)器是作為觸發(fā)它的事務的一部分被執(zhí)行的。

9、 觸發(fā)器說明：.表的擁有者才可以在表上建立觸發(fā)器，并且一個表上只能創(chuàng)建一定數(shù)量的觸發(fā)器。.觸發(fā)器名 觸發(fā)器名可以包含模式名，也可以不包含模式名。同一模式下，觸發(fā)器名必須唯一，并且觸發(fā)器名和表名必須在同一模式下。觸發(fā)器.表名當這個表的數(shù)據(jù)發(fā)生變化時，將激活定義在該表上相應的觸發(fā)器，因此，該表也稱為觸發(fā)器的目標表。4 .觸發(fā)事件觸發(fā)事件可以是INSERT,DELETE,UPDATE,也可以是這幾個事件的組合。update后還可以of,進一步指明修改哪些列時觸發(fā)器激活例1 定義一個觸發(fā)器，如果在學生表中插入記錄，則提示“歡迎新同學”。USE STUDENTIF OBJECT_ID(welcome_s

10、tudent,TR) IS NOT NULL DROP TRIGGER welcome_student;GOCREATE TRIGGER welcome_studentON studentAFTER INSERTAS PRINT 歡迎新同學！GO如果進行插入操作如：INSERTINTO student(sno,sname)VALUES(03250106，LILING)CREATE TRIGGER welcome_studentON studentAFTER INSERT AS declare sname varchar(10)select sname=sname from insertedPR

11、INT sname+同學,歡迎你！select * from studentcreate trigger trig_stu1after delete on studentfor each rowbegin delete sc where sc.sno=:old.sno;end;在student表上定義一個刪除觸發(fā)器create trigger trig_stu1on studentfor deleteasdelete scfrom sc,deletedwhere sc.sno=deleted.snoOracleSQL Server不同平臺的差異不同平臺的差異/禁用指定的約束，下面的觸發(fā)器與sc

12、表的外鍵約束沖突 ，先禁用外鍵約束alter table sc NOCHECK constraint all/定義student表的delete觸發(fā)器IF OBJECT_ID(st,TR) IS NOT NULL DROP TRIGGER st;gocreate trigger ston studentfor deleteasdelete scfrom sc,deletedwhere sc.sno=deleted.snogo觸發(fā)器與約束沖突觸發(fā)器與約束沖突/禁用指定的約束，下面的觸發(fā)器與sc表的外鍵約束沖突 ，先禁用外鍵約束alter table sc NOCHECK constraint a

13、ll觸發(fā)器與約束沖突觸發(fā)器與約束沖突IF OBJECT_ID(st,TR) IS NOT NULL DROP TRIGGER st;/定義student表的delete觸發(fā)器create trigger ston studentfor deleteasdelete scfrom sc,deletedwhere sc.sno=deleted.snodelete from studentwhere sno=1102/啟用指定表的約束 alter table sc CHECK constraint allalter table sc NOCHECK constraint all /定義sc表的ins

14、ert，update觸發(fā)器create trigger ston scfor insert，updateasdeclare sno char(10)begin select sno = sno from inserted insert into student(sno) values(sno)endinsert into scvalues(1234,1,NULL)alter table sc CHECK constraint all 觸發(fā)器例2 :定義一個BEFORE行級觸發(fā)器，為教師表TEACHER定義完整性規(guī)則教授的工資不得低于4000元，如果低于4000元，自動改為4000元Create

15、 trigger insert_or_update_salBefore insert or update on teacherFor each rowAs begin if (new.pjob=教授and(new.sal100) begin insert into sc(sno,cno,grade) values(sno,cno,0) end Endinsert into scvalues(1105,5,900)觸發(fā)器例3：定義一個AFTER行級觸發(fā)器,當教師表TEACHER的工資發(fā)生變化后就自動在工資變化表sal_log中增加一條相應的記錄.首先建立工資變化表sal_logCreate ta

16、ble sal_log(eno numeric(4) reference teacher(eno), sal numeric(7,2),Username char(10),);觸發(fā)器Create trigger insert_salAfter insert on teacherFor each row as begin insert into sal_log values(new.eno,new.sal,current_user) end;觸發(fā)器Create trigger update_salAfter update on teacherFor each rowAs begin if (ne

17、w.salold.sal) then insert into sal_log values(new.eno,new.sal,current_user) endifEnd;激活觸發(fā)器 觸發(fā)器創(chuàng)建后就存在于數(shù)據(jù)庫表上，當觸發(fā)事件發(fā)生時，觸發(fā)器創(chuàng)建后就存在于數(shù)據(jù)庫表上，當觸發(fā)事件發(fā)生時，觸發(fā)器被激活，執(zhí)行觸發(fā)動作語句。觸發(fā)器被激活，執(zhí)行觸發(fā)動作語句。刪除觸發(fā)器DROP TRIGGER ；舉例：舉例： 刪除刪除teacher表上的觸發(fā)器表上的觸發(fā)器del_teacher。DROP TRIGGER del_teacher； 觸發(fā)器和存儲過程 當對一個表執(zhí)行特定的操作時，被調(diào)用或者被“觸發(fā)”的SQL語句稱

18、為觸發(fā)器。觸發(fā)器能夠依次調(diào)用SQL語句或者存儲過程。 觸發(fā)器是事件驅(qū)動的SQL代碼，當插入、刪除、修改指定的表和列中的數(shù)據(jù)時，這些代碼將自動執(zhí)行，因此觸發(fā)器總是與特定的數(shù)據(jù)庫表及特定的數(shù)據(jù)庫事件(如插入(Insert)、刪除(Delete)、修改(Update)等)相聯(lián)系。觸發(fā)器的優(yōu)點 觸發(fā)器不依賴于任何客戶端應用程序，也不依賴于訪問數(shù)據(jù)庫的語言。觸發(fā)器有如下優(yōu)點： 1、標準化。在整個應用上，觸發(fā)器保證了數(shù)據(jù)的完整性和一致性，一旦在表上建立了觸發(fā)器，它就存儲在數(shù)據(jù)庫中；這種方法消除了各個客戶應用程序的冗余編碼，便于規(guī)則發(fā)生變化時對編碼進行修改； 2、高效率。觸發(fā)器初始執(zhí)行后，作為編譯的代碼執(zhí)行

19、。它的運行速度快，與在客戶工作站上執(zhí)行這些代碼相比，在服務器上執(zhí)行這些代碼減少了網(wǎng)絡通訊量和網(wǎng)絡沖突；觸發(fā)器把數(shù)據(jù)完整性代碼放在服務器平臺上比放在客戶工作站上更有效； 3、安全性。觸發(fā)器運行要有表主人的授權，但是，觸發(fā)器能夠被在表中插入、刪除、修改記錄的任何一個用戶觸發(fā)。任何一個應用程序或交互式子用戶都無法避開觸發(fā)器。觸發(fā)器的功能 觸發(fā)器對于強制執(zhí)行的工作是非常有用的，它主要用于下面兩種情況： 1、保證數(shù)據(jù)的完整性和一致性。當規(guī)則太復雜，不能用數(shù)據(jù)定義語言(DDL)定義它們、這時使用觸發(fā)器非常有效； 2、實現(xiàn)數(shù)據(jù)之間邏輯聯(lián)系的業(yè)務規(guī)則。 例如：在職工表中插入一個新的職工記錄時，業(yè)務規(guī)則要求職工

20、的工作終止日期字段是一個空值。這個規(guī)則不能使用數(shù)據(jù)定義語言中CREATE TABLE語句定義，它需要一個觸發(fā)器。 對于不能由其它手段實現(xiàn)的規(guī)則可考慮使用觸發(fā)器，以此改善系統(tǒng)的效率。如：參照完整性、表或列的約束等。觸發(fā)器的類型 觸發(fā)器是根據(jù)所觸發(fā)事件的類型進行分類的。通常的RDBMS支持三種類型的觸發(fā)器： 插入觸發(fā)器 (lnsent trigger)，在表中企圖插入記錄時觸發(fā)； 刪除觸發(fā)器 (Delete trigger)，在表中企圖刪除記錄時觸發(fā)； 修改觸發(fā)器 (Update trigger)，在表中企圖修改記錄時觸發(fā)。觸發(fā)器的其他用途 觸發(fā)器的許多使用都超出了完整性維護的范圍。例如，希望知道

21、一個發(fā)出訂單的顧客是否在上月進行了足夠的交易以至滿足了公司規(guī)定的打折條件。如果是這樣，業(yè)務員必須被告之，從而通知顧客，以便達到促銷的目的。觸發(fā)器能夠產(chǎn)生事件的日志，同時支持審計和安全檢查。存儲過程 SQL提供一種模塊語言，它允許在SQL中定義過程。在SQL-92標準語言的擴展中也提供了一些過程結(jié)構，例如for、while、if-then-else和復合SQL語句。我們可以把過程存入到數(shù)據(jù)庫中，用call語句來執(zhí)行。這樣的過程也叫存儲過程(stored procedure)。存儲過程非常有用，它允許外部程序在數(shù)據(jù)庫上執(zhí)行操作，而不暴露數(shù)據(jù)庫的內(nèi)部細節(jié)。定義存儲過程1（無參數(shù)傳遞） Create

22、or replace Procedure Pro_demo1 as begin delete from teacher; end;調(diào)用存儲過程 execute Pro_demo1; 定義存儲過程2 （有參數(shù)傳遞） Create or replace Procedure Pro_demo2( v_tno in number,v_title in varchar) as Begin update teacher set title=v_title where tno=v_tno; end;調(diào)用存儲過程 execute Pro_demo2(101,教授); 存儲過程的設置大大提高了SQL語句的功能、

23、效率和靈活性，具體表現(xiàn)在：1、改善了性能。 存儲過程與原始SQL語句或批處理中的SQL語句的最大區(qū)別在于它是預先編譯好的，因此存儲過程的執(zhí)行較快。2、改善了安全性。 存儲過程簡化了安全機制，它可以簡化對某些操作的授權。例如，一個用戶不希望別人在他的表上使用SELECT語句，他可以寫一個僅可以修改這個表的某些行或列的存儲過程，然后將調(diào)用該過程的權限授給特定的用戶，這樣其它用戶就可以通過該存儲過程訪問這個表。3、減少了網(wǎng)絡通訊量。在客戶/服務器環(huán)境下，存儲過程經(jīng)過編譯和優(yōu)化后，存儲在服務器端而不是客戶端。 這樣，在網(wǎng)絡上傳輸?shù)闹皇且粋€遠程調(diào)用和最終的執(zhí)行結(jié)果，而不是一系列的SQL語句和它們的返回結(jié)

24、果，從而大大減少了網(wǎng)絡上的傳輸量，提高了系統(tǒng)性能。4、完成一些難以完成的工作。如果本地數(shù)據(jù)庫服務器和遠程數(shù)據(jù)庫服務器都允許遠程登錄，那么本地數(shù)據(jù)庫服務器 就可以執(zhí)行其它數(shù)據(jù)庫服務器上的存儲過程。例如，可以在本地服務器上寫一個觸發(fā)器， 當某一事件發(fā)生（如刪除、更新或插入）時，該觸發(fā)器執(zhí)行遠程服務器上的存儲過程。5、提高了開發(fā)者的勞動生產(chǎn)率。存儲過程消除了開發(fā)者為一個事務或一個運算法則的多次需求而編寫和測試SQL語句的重復勞動。調(diào)用一個存儲過程比寫SQL語句更快、更簡單。6、有利于專門技術的使用。技術熟練的開發(fā)者能夠集中精力，寫出復雜查詢、運算法則和事務的存儲過程。其它具有一般SQL經(jīng)驗的開發(fā)者能夠調(diào)用這些存儲過程。7、簡化了應用的維護，增加了應用的靈活性。存儲過程有助于從應用邏輯中把業(yè)務規(guī)則分離出來。當業(yè)務發(fā)生變化，需要修改業(yè)務規(guī)則時，這個變化只影響存儲過程。與改變、測試和調(diào)整一個及多個客戶的成千上萬個用戶程序相比，