數(shù)據(jù)庫(kù)原理-第七章(數(shù)據(jù)完整性)

數(shù)據(jù)庫(kù)系統(tǒng)概論AnIntroductiontoDatabaseSystem數(shù)據(jù)庫(kù)的完整性AnIntroductiontoDatabaseSystem數(shù)據(jù)庫(kù)完整性數(shù)據(jù)庫(kù)的完整性數(shù)據(jù)的正確性和相容性數(shù)據(jù)的完整性和安全性是兩個(gè)不同概念數(shù)據(jù)的完整性防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫(kù)中存在不正確的數(shù)據(jù)防范對(duì)象：不合語(yǔ)義的、不正確的數(shù)據(jù)數(shù)據(jù)的安全性保護(hù)數(shù)據(jù)庫(kù)防止惡意的破壞和非法的存取防范對(duì)象：非法用戶(hù)和非法操作AnIntroductiontoDatabaseSystem數(shù)據(jù)庫(kù)完整性(續(xù))為維護(hù)數(shù)據(jù)庫(kù)的完整性，DBMS必須：1.提供定義完整性約束條件的機(jī)制2.提供完整性檢查的方法3.違約處理AnIntroductiontoDatabaseSystem實(shí)體完整性實(shí)體完整性定義實(shí)體完整性檢查和違約處理AnIntroductiontoDatabaseSystem實(shí)體完整性定義關(guān)系模型的實(shí)體完整性CREATETABLE中用PRIMARYKEY定義單屬性構(gòu)成的碼有兩種說(shuō)明方法定義為列級(jí)約束條件定義為表級(jí)約束條件對(duì)多個(gè)屬性構(gòu)成的碼只有一種說(shuō)明方法定義為表級(jí)約束條件

AnIntroductiontoDatabaseSystem實(shí)體完整性定義(續(xù))［例1］將Student表中的Sno屬性定義為碼

(1)在列級(jí)定義主碼

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20));AnIntroductiontoDatabaseSystem實(shí)體完整性定義(續(xù))(2)在表級(jí)定義主碼

CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，

PRIMARYKEY(Sno));AnIntroductiontoDatabaseSystem實(shí)體完整性定義(續(xù))［例2］將SC表中的Sno，Cno屬性組定義為碼

CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)/*只能在表級(jí)定義主碼*/);AnIntroductiontoDatabaseSystem實(shí)體完整性檢查和違約處理插入或?qū)χ鞔a列進(jìn)行更新操作時(shí)，RDBMS按照實(shí)體完整性規(guī)則自動(dòng)進(jìn)行檢查。包括：1.檢查主碼值是否唯一，如果不唯一則拒絕插入或修改2.檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕插入或修改AnIntroductiontoDatabaseSystem實(shí)體完整性檢查和違約處理(續(xù))檢查記錄中主碼值是否唯一的一種方法是進(jìn)行全表掃描AnIntroductiontoDatabaseSystem實(shí)體完整性檢查和違約處理(續(xù))索引

AnIntroductiontoDatabaseSystem參照完整性定義關(guān)系模型的參照完整性定義在CREATETABLE中用FOREIGNKEY短語(yǔ)定義哪些列為外碼用REFERENCES短語(yǔ)指明這些外碼參照哪些表的主碼AnIntroductiontoDatabaseSystem參照完整性定義(續(xù))例如，關(guān)系SC中一個(gè)元組表示一個(gè)學(xué)生選修的某門(mén)課程的成績(jī)，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼［例3］定義SC中的參照完整性CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，/*在表級(jí)定義實(shí)體完整性*/

FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*在表級(jí)定義參照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表級(jí)定義參照完整性*/);AnIntroductiontoDatabaseSystem參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性插入元組拒絕可能破壞參照完整性修改外碼值拒絕刪除元組可能破壞參照完整性拒絕/級(jí)連刪除/設(shè)置為空值修改主碼值可能破壞參照完整性拒絕/級(jí)連修改/設(shè)置為空值A(chǔ)nIntroductiontoDatabaseSystem違約處理參照完整性違約處理1.拒絕(NOACTION)執(zhí)行默認(rèn)策略2.級(jí)聯(lián)(CASCADE)操作3.設(shè)置為空值（SET-NULL）對(duì)于參照完整性，除了應(yīng)該定義外碼，還應(yīng)定義外碼列是否允許空值A(chǔ)nIntroductiontoDatabaseSystem違約處理(續(xù))［例4］顯式說(shuō)明參照完整性的違約處理示例CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY（Sno，Cno）， FOREIGNKEY(Sno)REFERENCESStudent(Sno) ONDELETECASCADE/*級(jí)聯(lián)刪除SC表中相應(yīng)的元組*/ONUPDATECASCADE，/*級(jí)聯(lián)更新SC表中相應(yīng)的元組*/FOREIGNKEY(Cno)REFERENCESCourse(Cno) ONDELETENOACTION /*當(dāng)刪除course表中的元組造成了與SC表不一致時(shí)拒絕刪除*/ONUPDATECASCADE /*當(dāng)更新course表中的cno時(shí)，級(jí)聯(lián)更新SC表中相應(yīng)的元組*/)；AnIntroductiontoDatabaseSystem用戶(hù)定義的完整性用戶(hù)定義的完整性就是針對(duì)某一具體應(yīng)用的數(shù)據(jù)必須滿(mǎn)足的語(yǔ)義要求RDBMS提供，而不必由應(yīng)用程序承擔(dān)AnIntroductiontoDatabaseSystem屬性上的約束條件的定義CREATETABLE時(shí)定義列值非空（NOTNULL）列值唯一（UNIQUE）檢查列值是否滿(mǎn)足一個(gè)布爾表達(dá)式（CHECK）AnIntroductiontoDatabaseSystem屬性上的約束條件的定義(續(xù))1.不允許取空值

［例5］在定義SC表時(shí)，說(shuō)明Sno、Cno、Grade屬性不允許取空值。CREATETABLESC（SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL，

GradeSMALLINTNOTNULL，

PRIMARYKEY(Sno，Cno)，/*如果在表級(jí)定義實(shí)體完整性，隱含了Sno，Cno不允許取空值，則在列級(jí)不允許取空值的定義就不必寫(xiě)了*/）；AnIntroductiontoDatabaseSystem屬性上的約束條件的定義(續(xù))2.列值唯一［例6］建立部門(mén)表DEPT，要求部門(mén)名稱(chēng)Dname列取值唯一，部門(mén)編號(hào)Deptno列為主碼CREATETABLEDEPT(DeptnoNUMERIC(2)，DnameCHAR(9)UNIQUE，/*要求Dname列值唯一*/LocationCHAR(10)，PRIMARYKEY(Deptno))；AnIntroductiontoDatabaseSystem屬性上的約束條件的定義(續(xù))3.用CHECK短語(yǔ)指定列值應(yīng)該滿(mǎn)足的條件［例7］Student表的Ssex只允許取“男”或“女”。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(8)NOTNULL，SsexCHAR(2)CHECK(SsexIN(‘男’，‘女’))，/*性別屬性Ssex只允許取'男'或'女'*/SageSMALLINT，SdeptCHAR(20));AnIntroductiontoDatabaseSystem屬性上的約束條件檢查和違約處理插入元組或修改屬性的值時(shí)，RDBMS檢查屬性上的約束條件是否被滿(mǎn)足如果不滿(mǎn)足則操作被拒絕執(zhí)行AnIntroductiontoDatabaseSystem元組上的約束條件的定義在CREATETABLE時(shí)可以用CHECK短語(yǔ)定義元組上的約束條件，即元組級(jí)的限制同屬性值限制相比，元組級(jí)的限制可以設(shè)置不同屬性之間的取值的相互約束條件

AnIntroductiontoDatabaseSystem元組上的約束條件的定義(續(xù))［例9］當(dāng)學(xué)生的性別是男時(shí)，其名字不能以Ms.打頭。

CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(8)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，PRIMARYKEY(Sno)，

CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%')/*定義了元組中Sname和Ssex兩個(gè)屬性值之間的約束條件*/)；性別是女性的元組都能通過(guò)該項(xiàng)檢查，因?yàn)镾sex=‘女’成立；當(dāng)性別是男性時(shí)，要通過(guò)檢查則名字一定不能以Ms.打頭AnIntroductiontoDatabaseSystem元組上的約束條件檢查和違約處理插入元組或修改屬性的值時(shí)，RDBMS檢查元組上的約束條件是否被滿(mǎn)足如果不滿(mǎn)足則操作被拒絕執(zhí)行

AnIntroductiontoDatabaseSystem完整性約束命名子句CONSTRAINT約束CONSTRAINT<完整性約束條件名>［PRIMARYKEY短語(yǔ)|FOREIGNKEY短語(yǔ)|CHECK短語(yǔ)］AnIntroductiontoDatabaseSystem完整性約束命名子句(續(xù))［例10］建立學(xué)生登記表Student，要求學(xué)號(hào)在90000~99999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。CREATETABLEStudent(SnoNUMERIC(6)

CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，SnameCHAR(20)

CONSTRAINTC2NOTNULL，

SageNUMERIC(3)

CONSTRAINTC3CHECK(Sage<30)，

SsexCHAR(2)

CONSTRAINTC4CHECK(SsexIN('男'，'女'))，CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5個(gè)約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個(gè)列級(jí)約束。AnIntroductiontoDatabaseSystem完整性約束命名子句(續(xù))2.修改表中的完整性限制使用ALTERTABLE語(yǔ)句修改表中的完整性限制AnIntroductiontoDatabaseSystem完整性約束命名子句(續(xù))［例13］修改表Student中的約束條件，要求學(xué)號(hào)改為在900000~999999之間，年齡由小于30改為小于40可以先刪除原來(lái)的約束條件，再增加新的約束條件

ALTERTABLEStudentDROPCONSTRAINTC1;

ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，

ALTERTABLEStudentDROPCONSTRAINTC3;

ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage<40)；AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程創(chuàng)建一個(gè)存儲(chǔ)過(guò)程語(yǔ)法：語(yǔ)法CREATEPROCEDURE<procedure_name>[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

ASsql_statement[...n]

AnIntroductiontoDatabaseSystem定義存儲(chǔ)過(guò)程的幾種方式A.使用帶有復(fù)雜SELECT語(yǔ)句的簡(jiǎn)單過(guò)程下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中返回所有作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程不使用任何參數(shù)。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname='au_info_all'ANDtype='P')DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idGOAnIntroductiontoDatabaseSystemB.使用帶有參數(shù)的簡(jiǎn)單過(guò)程下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程接受與傳遞的參數(shù)精確匹配的值。CREATEPROCEDUREau_info@lastnamevarchar(40),@firstnamevarchar(20)ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fname=@firstnameANDau_lname=@lastnameGO定義存儲(chǔ)過(guò)程的幾種方式EXECUTEau_info'Dull','Ann'AnIntroductiontoDatabaseSystemC.使用帶有通配符參數(shù)的簡(jiǎn)單過(guò)程下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程對(duì)傳遞的參數(shù)進(jìn)行模式匹配，如果沒(méi)有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。CREATEPROCEDUREau_info2@lastnamevarchar(30)='D%',@firstnamevarchar(18)='%'ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fnameLIKE@firstnameANDau_lnameLIKE@lastnameGO定義存儲(chǔ)過(guò)程的幾種方式AnIntroductiontoDatabaseSystemD.使用OUTPUT參數(shù)OUTPUT參數(shù)允許外部過(guò)程、批處理或多條Transact-SQL語(yǔ)句訪問(wèn)在過(guò)程執(zhí)行期間設(shè)置的某個(gè)值。下面的示例創(chuàng)建一個(gè)存儲(chǔ)過(guò)程(titles_sum)，并使用一個(gè)可選的輸入?yún)?shù)和一個(gè)輸出參數(shù)。首先，創(chuàng)建過(guò)程：CREATEPROCEDUREtitles_sum@@SUMintOUTPUTASSELECT@@SUM=SUM(price)FROMtitlesWHEREtitleLIKE@@TITLEGO接下來(lái)，將該OUTPUT參數(shù)用于控制流語(yǔ)言。說(shuō)明

OUTPUT變量必須在創(chuàng)建表和使用該變量時(shí)都進(jìn)行定義。參數(shù)名和變量名不一定要匹配，不過(guò)數(shù)據(jù)類(lèi)型和參數(shù)位置必須匹配（除非使用@@SUM=variable形式）。DECLARE@@TOTALCOSTintEXECUTEtitles_sum@@TOTALCOSTOUTPUTIF@@TOTALCOST<200BEGINPRINT‘總價(jià)少于200元！'ENDELSERTRIM(CAST(@@TOTALCOSTASvarchar(20)))定義存儲(chǔ)過(guò)程的幾種方式AnIntroductiontoDatabaseSystem基本編程技術(shù)局部變量聲明：所有在存儲(chǔ)過(guò)程中的局部變量都必須在使用之前聲明，并且名稱(chēng)必須唯一；變量的適用范圍是從聲明開(kāi)始到過(guò)程語(yǔ)句結(jié)束的所有代碼；存儲(chǔ)過(guò)程中可以適用（begin….end）語(yǔ)句來(lái)嵌套塊，但是對(duì)變量的作用范圍沒(méi)有影響。Declare@<變量名><數(shù)據(jù)類(lèi)型>例如：declare@rowCntint賦值語(yǔ)句1、可以使用set語(yǔ)句對(duì)一個(gè)變量或參數(shù)賦值set@rowCnt=102、可以使用select語(yǔ)句對(duì)多個(gè)變量賦值（主要用于對(duì)表中數(shù)據(jù)列）CreateprocedureGet_discount@custIDint,@Namevarchar(20),@DiscountintAsselect@Name=name,@discount=discountFromcustomerwhereCustId=@custIdAnIntroductiontoDatabaseSystem顯示消息Print語(yǔ)句語(yǔ)序返回任何字符表達(dá)式，包括文字、字符參數(shù)和變量Print@rowCnt條件執(zhí)行：if….elseif(@discount>90and@discount<100)beginprint‘普通會(huì)員’endelseif(@discount>70and@discount<80)beginprint‘高級(jí)會(huì)員’end基本編程技術(shù)AnIntroductiontoDatabaseSystemWhile循環(huán)createproceduretestasdeclare@countint,declare@limitintset@count=0set@limit=10while(@count<@limit)beginprint‘循環(huán)繼續(xù)！’

set@count=@count+1end基本編程技術(shù)AnIntroductiontoDatabaseSystem使用游標(biāo)關(guān)系數(shù)據(jù)庫(kù)中的操作會(huì)對(duì)整個(gè)行集產(chǎn)生影響。由SELECT語(yǔ)句返回的行集包括所有滿(mǎn)足該語(yǔ)句WHERE子句中條件的行。由語(yǔ)句所返回的這一完整的行集被稱(chēng)為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機(jī)應(yīng)用程序，并不總能將整個(gè)結(jié)果集作為一個(gè)單元來(lái)有效地處理。這些應(yīng)用程序需要一種機(jī)制以便每次處理一行或一部分行。游標(biāo)就是提供這種機(jī)制的結(jié)果集擴(kuò)展。AnIntroductiontoDatabaseSystem1、聲明游標(biāo)2、打開(kāi)游標(biāo)3、從游標(biāo)中提取行，有選擇的修改或者刪除所選擇的行4、關(guān)閉游標(biāo)5、釋放游標(biāo)使用游標(biāo)的步驟AnIntroductiontoDatabaseSystem聲明游標(biāo)DECLAREcursor_nameCURSOR

FORselect_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]列1：createprocedurelistcustasdeclare@custIdintdeclare@cusNamevarchar(30)declareCustCursorCursorForselectcustId,NamefromcustomerorderbycustIdforreadonlyopencustCursorwhile(0=0)beginfetchnextfromcustCursorinto@custId,@custNameif(@@fetch_status<>0)break/*0表示成功，-1表示沒(méi)有未讀的行，-2表示行已被刪除print‘客戶(hù)編號(hào)：’+@custId’+’---’+’客戶(hù)姓名：’+@cusNameendClosecustCursoDeallocatecustCursorAnIntroductiontoDatabaseSystem游標(biāo)的類(lèi)型1、可滾動(dòng)游標(biāo)：可以在Cursor關(guān)鍵字前加Scroll關(guān)鍵字declarecustCursorScrollCursorforselectcustId,NamefromcustomerorderbycustIdforreadonlyNext下一行Prior前一行First第一行Last最后一行AbsolutenN>0定位到從開(kāi)始的第n行N=0沒(méi)有返回的行N<0定位到末尾前的第n行RelativenN<-1定位到當(dāng)前行前的第n行N=-1同prior相同N=0定位到當(dāng)前行（重讀）N=1與next相同N>1定位到當(dāng)前行之后的第n行AnIntroductiontoDatabaseSystemDeclare@custIdintDeclare@discountdec(5,3)DeclarecustCursorCursorforselectcustId,discountfromcustomerorderbycustIdforupdateofdiscountOpencustCursor游標(biāo)的類(lèi)型—可更新游標(biāo)While(0=0)BeginfetchnextfromcustCursorinto@custId,@discountif(@@fetch_Status=0)beginif(@discount=0.0)begindeletefromcustomerwherecurrentofCustCursorendelseif(@discount>0.5)beginupdatecustomersetdiscount=0.5wherecurrentofcustCursorendendEndClosecustCursorDeallocatecustCursorAnIntroductiontoDatabaseSystem觸發(fā)器觸發(fā)器（Trigger）是用戶(hù)定義在關(guān)系表上的一類(lèi)由事件驅(qū)動(dòng)的特殊過(guò)程由服務(wù)器自動(dòng)激活可以進(jìn)行更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力

AnIntroductiontoDatabaseSystem定義觸發(fā)器CREATETRIGGER語(yǔ)法格式

CREATETRIGGER<觸發(fā)器名>

{BEFORE|AFTER}觸發(fā)事件ON<表名>FOREACH{ROW|STATEMENT}{WHEN<觸發(fā)條件>}

<觸發(fā)動(dòng)作體>

AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))定義觸發(fā)器的語(yǔ)法說(shuō)明:1.創(chuàng)建者：表的擁有者2.觸發(fā)器名3.表名：觸發(fā)器的目標(biāo)表4.觸發(fā)事件：INSERT、DELETE、UPDATE5.觸發(fā)器類(lèi)型行級(jí)觸發(fā)器（FOREACHROW）語(yǔ)句級(jí)觸發(fā)器（FOREACHSTATEMENT）AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))例如,假設(shè)在［例11］的TEACHER表上創(chuàng)建了一個(gè)AFTERUPDATE觸發(fā)器。如果表TEACHER有1000行，執(zhí)行如下語(yǔ)句：UPDATETEACHERSETDeptno=5;

如果該觸發(fā)器為語(yǔ)句級(jí)觸發(fā)器，那么執(zhí)行完該語(yǔ)句后，觸發(fā)動(dòng)作只發(fā)生一次如果是行級(jí)觸發(fā)器，觸發(fā)動(dòng)作將執(zhí)行1000次AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))6.觸發(fā)條件觸發(fā)條件為真省略WHEN觸發(fā)條件7.觸發(fā)動(dòng)作體觸發(fā)動(dòng)作體可以是一個(gè)匿名PL/SQL過(guò)程塊也可以是對(duì)已創(chuàng)建存儲(chǔ)過(guò)程的調(diào)用AnIntroductiontoDatabaseSystem定義觸發(fā)器(續(xù))［例18］定義一個(gè)BEFORE行級(jí)觸發(fā)器，為教師表Teacher定義完整性規(guī)則“教授的工資不得低于4000元，如果低于4000元，自動(dòng)改為4000元”。CREATETRIGGERInsert_Or_Update_Salforupdate,insertas declare@enonumeric(4)declare@salnumeric(7,2)select@eno=eno,@sal=salfrom