網(wǎng)頁制作基礎(chǔ)教程 （Dreamweaver CC）（第3版）課件 項目8 設(shè)置數(shù)據(jù)完整性與索引

數(shù)據(jù)庫技術(shù)及應(yīng)用授課人：陳翠松項目8設(shè)置數(shù)據(jù)完整性與索引有人向小王反饋，在工作計劃參與人員表中，有一個工作人員不是自己單位的員工。小王經(jīng)過思考后發(fā)現(xiàn)，工作計劃參與人員表中的人員一定要來自工作人員表，這樣可以避免出現(xiàn)以上問題，他計劃通過數(shù)據(jù)完整性來確保數(shù)據(jù)一致，通過索引來提升查詢效率。工作情境目錄01設(shè)置數(shù)據(jù)完整性02設(shè)置索引03鞏固與小結(jié)04任務(wù)訓(xùn)練01設(shè)置數(shù)據(jù)完整性一、設(shè)置數(shù)據(jù)完整性任務(wù)分析任務(wù)編號任務(wù)內(nèi)容任務(wù)8-1創(chuàng)建完善的地區(qū)表area，并導(dǎo)入數(shù)據(jù)任務(wù)8-2創(chuàng)建完善的民俗表folk，并導(dǎo)入數(shù)據(jù)任務(wù)8-3創(chuàng)建完善的名人表celebrity，并導(dǎo)入數(shù)據(jù)任務(wù)8-4創(chuàng)建完善的榮譽(yù)表honor，并導(dǎo)入數(shù)據(jù)任務(wù)8-5創(chuàng)建完善的工作人員表user，并確保表中的記錄不少于5條任務(wù)8-6創(chuàng)建完善的工作計劃表plan，并確保表中的記錄不少于5條任務(wù)8-7創(chuàng)建完善的工作計劃參與人員表participant，并確保表中的記錄不少于5條任務(wù)8-8創(chuàng)建完善的工作計劃項目表planforproject，并確保表中的記錄不少于5條小王發(fā)現(xiàn)數(shù)據(jù)庫的問題越來越多，如表中有相同數(shù)據(jù)，有些數(shù)據(jù)明顯是錯誤的，因此必須通過數(shù)據(jù)完整性來確保數(shù)據(jù)的一致性、完整性。小王對粵文創(chuàng)進(jìn)行分析后得到的任務(wù)清單如下。一、設(shè)置數(shù)據(jù)完整性知識儲備1、初識數(shù)據(jù)完整性數(shù)據(jù)完整性是指對表中數(shù)據(jù)的一種約束，不僅能夠幫助數(shù)據(jù)庫管理員更好地管理數(shù)據(jù)庫，還能夠確保數(shù)據(jù)庫中數(shù)據(jù)的正確性和有效性。數(shù)據(jù)完整性任何時候都可以實施，但對已有數(shù)據(jù)的表實施數(shù)據(jù)完整性時，需要先檢查表中的數(shù)據(jù)是否滿足所實施的完整性，只有表中的數(shù)據(jù)滿足所實施的完整性，才能實施成功。因此，在設(shè)計表時，應(yīng)充分考慮數(shù)據(jù)完整性約束，在輸入數(shù)據(jù)前應(yīng)完成數(shù)據(jù)完整性約束的設(shè)置，確保數(shù)據(jù)庫中的數(shù)據(jù)準(zhǔn)確、一致，從而減少錯誤。可以通過CREATETABLE或ALTERTABLE語句來實現(xiàn)數(shù)據(jù)完整性約束。MySQL主要支持以下6種約束。一、設(shè)置數(shù)據(jù)完整性知識儲備1、初識數(shù)據(jù)完整性1）主鍵約束主鍵是表中的一個或一組特殊字段，該字段或字段組能唯一標(biāo)識該表中的每條記錄。在一般情況下，要求每個表設(shè)置一個主鍵。主鍵約束是通過主鍵來約束數(shù)據(jù)的，是使用最頻繁的約束。主鍵可以分為單字段主鍵和多字段聯(lián)合主鍵。通過PRIMARYKEY關(guān)鍵字來指定主鍵。在使用主鍵時應(yīng)注意以下幾點。（1）每個表最多只能定義一個主鍵。（2）主鍵值必須唯一標(biāo)識表中的每條記錄，并且不能為NULL。（3）一個字段只能在主鍵字段或主鍵字段組中出現(xiàn)一次。（4）聯(lián)合主鍵不能包含不必要的字段，即聯(lián)合主鍵減少一個字段就不能成為主健。一、設(shè)置數(shù)據(jù)完整性知識儲備1、初識數(shù)據(jù)完整性2）外鍵約束外鍵一般是針對兩個表名而言的，其中一個表是引用表（即從表或子表），另一個表是被引用表（即主表或父表）。引用表某字段的值必須參照被引用表的主鍵字段值，引用表的字段稱為外鍵，即外鍵的值必須參照主表的主鍵值，通過外鍵能確保兩個表或多個表數(shù)據(jù)之間的一致性。外鍵約束就是通過外鍵來約束數(shù)據(jù)的，一般使用FOREIGNKEY關(guān)鍵字。使用外鍵時應(yīng)注意以下幾點。（1）在創(chuàng)建外鍵時，主表必須已經(jīng)存在于數(shù)據(jù)庫中，即先建主表再建從表。（2）主表和從表是同一個表稱為自參照完整性，該表稱為自參照表。（3）主表必須定義主鍵，并且被引用的字段一定是主鍵或候選鍵。（4）外鍵值只允許空值NULL或指定主鍵中的值，不允許出現(xiàn)其他值。（5）外鍵中字段的數(shù)目，必須和主表的主鍵中字段的數(shù)目一致。（6）外鍵中字段的數(shù)據(jù)類型，必須和主表的主鍵中對應(yīng)字段的數(shù)據(jù)類型一致。（7）一個表可以有一個或多個外鍵。（8）當(dāng)主表的值正在被從表引用時，要先刪除從表對應(yīng)的記錄，再刪除主表中對應(yīng)的記錄，如果直接刪除主表中對應(yīng)的記錄就會出錯，即先刪從表數(shù)據(jù)再刪除主表數(shù)據(jù)。一、設(shè)置數(shù)據(jù)完整性知識儲備1、初識數(shù)據(jù)完整性3）唯一約束唯一約束是指所有記錄中指定字段或字段組的值不能重復(fù)出現(xiàn)，因此，唯一約束與主鍵約束相似。但唯一約束允許有空值，主鍵約束不允許有空值。另外，唯一約束在一個表中可以有多個，主鍵約束每個表中只能有一個?？梢酝ㄟ^UNIQUE關(guān)鍵字來設(shè)置唯一約束。4）檢查約束檢查約束是用來檢查數(shù)據(jù)表中指定字段的值是否有效的一種手段。因此，在輸入字段值時，不僅要受數(shù)據(jù)類型的限制，還要受其他約束?？梢酝ㄟ^CHECK關(guān)鍵字來設(shè)置檢查約束。在MySQL5.7可以使用檢查約束，但其對數(shù)據(jù)驗證沒有任何作用，添加數(shù)據(jù)時也沒有任何錯誤或警告。但，MySQL8.0支持檢查約束。一、設(shè)置數(shù)據(jù)完整性知識儲備1、初識數(shù)據(jù)完整性5）非空約束非空約束是指字段的值不能為空。對于使用了非空約束的字段，如果用戶在添加數(shù)據(jù)時沒有指定值，數(shù)據(jù)庫系統(tǒng)就會報錯。因此，在插入數(shù)據(jù)時，一定要仔細(xì)查看表的結(jié)構(gòu)，了解哪些字段為非空字段。對于非空字段，在開發(fā)前臺界面時，通過一些標(biāo)號（如“*”）來提醒用戶輸入值，以減少數(shù)據(jù)庫操作錯誤。系統(tǒng)默認(rèn)字段可以為空。要設(shè)置非空約束，可以通過NOTNULL關(guān)鍵字來實現(xiàn)。6）默認(rèn)值約束默認(rèn)值約束也稱為缺省值約束，用來約束當(dāng)數(shù)據(jù)表中的某個字段不輸入值時，自動為其添加一個已經(jīng)設(shè)置好的值。對于非空約束的字段，通過設(shè)置默認(rèn)值還可以減少錯誤。在設(shè)置默認(rèn)值時，從原則上來說范圍內(nèi)的任意值都可以，但建議將該字段最可能的值作為默認(rèn)值，這樣可以大大減少工作量?？梢酝ㄟ^DEFAULT關(guān)鍵字來設(shè)置默認(rèn)值約束。一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性數(shù)據(jù)的完整性約束可以在字段級設(shè)置，也可以在表級設(shè)置。字段級數(shù)據(jù)完整性約束只對指定字段有意義，只影響單個字段。表級數(shù)據(jù)完整性約束可以跨字段設(shè)置，同時影響多個字段。因此，對多個字段同時設(shè)置某個數(shù)據(jù)完整性約束時，只能使用表級數(shù)據(jù)完整性約束設(shè)置。在一般情況下，單個字段的數(shù)據(jù)完整性設(shè)置采用字段級，非空約束、默認(rèn)值約束、檢查約束一般只對單個字段有效。多個字段的數(shù)據(jù)完整性采用表級設(shè)置。可以在定義表結(jié)構(gòu)時，添加數(shù)據(jù)完整性設(shè)置，但表名已經(jīng)存在，所以只能在修改表時設(shè)置完整性約束。當(dāng)然，如果表中沒有數(shù)據(jù)或數(shù)據(jù)比較少，那么可以先刪除原來的表，再重新創(chuàng)建表結(jié)結(jié)構(gòu)。一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性1）在創(chuàng)建表時設(shè)置約束創(chuàng)建字段級數(shù)據(jù)完整性約束，可以在字段數(shù)據(jù)類型后面添加數(shù)據(jù)完整性約束，具體方法如下：CREATETABLE[IFNOTEXISTS]表名(字段名字段類型字段完整性約束[,字段名字段類型字段完整性約束,…]);需要說明以下幾點。[IFNOTEXISTS]：在創(chuàng)建表時，先檢查表是否已經(jīng)存在，如果表不存在就創(chuàng)建新表，如果當(dāng)前數(shù)據(jù)庫中已存在同名數(shù)據(jù)表，雖然不會提示出錯，但會忽略本次操作，數(shù)據(jù)庫的表是原來的表，而不是新建的表。設(shè)置主鍵約束、非空約束和唯一約束，操作簡單，只要把相應(yīng)的關(guān)鍵字寫在字段類型的后面即可。設(shè)置默認(rèn)值約束的格式為“DEFAULT值”，其中“值”可以根據(jù)字段的數(shù)據(jù)類型確定，可以是數(shù)字，也可以字符，字符要加引號（單引號或雙引號都可以）。設(shè)置檢查約束的格式為“CHECK(字段名關(guān)系運算符值[[邏輯運算符字段名關(guān)系運算符值]…])”，也可以使用“CHECK(字段名IN(值列表))”，表示可取值列表中任意一個值。一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性示例8-1完成工作人員表user的完整性約束設(shè)置。工作人員表user的結(jié)構(gòu)如表8-1所示。字段名數(shù)據(jù)類型是否為空約束說明userIdSMALLINTNOTNULL主鍵工號userNameVARCHAR(8)NOTNULL唯一姓名fkTitleVARCHAR(10)NOTNULL實習(xí)研究員、助理研究員、副研究員、研究員職稱genderVARCHAR(2)NOTNULL男、女性別nationVARCHAR(10)NULL默認(rèn)漢族民族birthdayDATENULL

出生日期nativePlaceVARCHAR(10)NULL

籍貫phoneVARCHAR(13)NOTNULL

手機(jī)號一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性程序代碼如下：/*通過創(chuàng)建表來設(shè)置數(shù)據(jù)完整性*/CREATETABLEuser(userIdSMALLINTNOTNULLPRIMARYKEY, --設(shè)置主鍵約束、非空約束userNameVARCHAR(8)NOTNULLUNIQUE, --設(shè)置非空約束、唯一約束fkTitleVARCHAR(10)NOTNULLCHECK(fkTitle='實習(xí)研究員'ORfkTitle='助理研究員'ORfkTitle='副研究員'ORfkTitle='研究員'), --設(shè)置檢查約束、非空約束genderVARCHAR(2)NOTNULLCHECK(gender='男'ORgender='女'),--設(shè)置檢查約束、非空約束nationVARCHAR(10)NULLDEFAULT'漢族', --設(shè)置默認(rèn)值約束、可空約束birthdayDATENULL,--設(shè)置可空約束nativePlaceVARCHAR(10)NULL, --設(shè)置可空約束phoneVARCHAR(13)NOTNULL); --設(shè)置非空約束DESCuser;--查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性需要說明以下幾點。當(dāng)命令代碼比較長時，可以分行書寫，但一定不要寫錯。在MySQL客戶端，按Enter鍵不能再返回上一行修改命令，只能先放棄本次創(chuàng)建操作，再重新創(chuàng)建。userId字段的非空約束可省略，因為主鍵KEY約束已隱含非空約束。所有可空約束即NULL約束都可以省略，系統(tǒng)默認(rèn)為NULL。上述代碼中的“fkTitle='實習(xí)研究員'ORfkTitle='助理研究員'ORfkTitle='副研究員'ORfkTitle='研究員'”也可寫成“fkTitleIN('實習(xí)研究員','助理研究員','副研究員','研究員'),”，這樣更簡潔。如果表中已有數(shù)據(jù)，那么建議使用修改表的方式設(shè)置約束，這樣也可新建一個練習(xí)數(shù)據(jù)庫。一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性2）在修改表時設(shè)置約束當(dāng)表中已有數(shù)據(jù)時，最好使用修改表的方式設(shè)置約束。主鍵約束、外鍵約束、檢查約束的設(shè)置方法如下：ALTERTABLE<表名>ADD[CONSTRAINT約束名]PRIMARYKEY(字段列表)|FOREIGNKEY(字段名)REFERENCES主表名稱(字段名)|CHECK(字段名關(guān)系運算符值[[邏輯運算符字段名關(guān)系運算符值]…])|(字段名IN(值列表));默認(rèn)值約束的設(shè)置方法如下：ALTERTABLE<表名>ALTER字段名SETDEFAULT默認(rèn)值;非空約束的設(shè)置方法如下：ALTERTABLE<表名>MODIFY字段名字段類型NOTNULL;一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性示例8-2通過修改表的方式完成工作人員表user的完整性約束設(shè)置。一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性程序代碼如下：/*創(chuàng)建沒有約束的工作人員表user*/CREATETABLEuser(userIdSMALLINT,userNameVARCHAR(8),fkTitleVARCHAR(10),genderVARCHAR(2),nationVARCHAR(10),birthdayDATE,nativePlaceVARCHAR(10),phoneVARCHAR(13));/*添加約束*/ALTERTABLEuserADDCONSTRAINTpk_userIdPRIMARYKEY(userId);--設(shè)置主鍵約束、非空約束ALTERTABLEuserMODIFYuserNameVARCHAR(8)NOTNULL; --設(shè)置非空約束ALTERTABLEuserADDCONSTRAINTuni_userNameUNIQUE(userName); --設(shè)置唯一約束ALTERTABLEuserMODIFYfkTitleVARCHAR(10)NOTNULL; --設(shè)置非空約束ALTERTABLEuserMODIFYphoneVARCHAR(13)NOTNULL; --設(shè)置非空約束ALTERTABLEuserADDCONSTRAINTchk_fkTitleCHECK(fkTitleIN('實習(xí)研究員','助理研究員','副研究員','研究員')); --設(shè)置檢查約束ALTERTABLEuserADDCONSTRAINTchk_genderCHECK(gender='男'ORgender='女');--設(shè)置檢查約束ALTERTABLEuserALTERnationSETDEFAULT'漢族';DESCuser;--查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性示例8-3通過修改表的方式完成工作計劃參與人員表participant的完整性約束設(shè)置。工作計劃參與人員表participant的結(jié)構(gòu)如表8-2所示。字段名數(shù)據(jù)類型是否為空約束說明idIntNOTNULL主鍵記錄編號planIdIntNOTNULL

計劃編號userIdSMALLINTNOTNULL外鍵工號dutyVARCHAR(1000)NOTNULL

工作職責(zé)requirementVARCHAR(1000)NULL

工作要求remarksVARCHAR(500)NULL

備注一、設(shè)置數(shù)據(jù)完整性知識儲備2、設(shè)置字段級數(shù)據(jù)完整性/*創(chuàng)建沒有約束的工作計劃參與人員表participant*/CREATETABLEparticipant(idInt,planIdInt,userIdSMALLINT,dutyVARCHAR(1000),requirementVARCHAR(1000),remarksVARCHAR(500));/*添加約束*/ALTERTABLEparticipantADDCONSTRAINTpk_idPRIMARYKEY(id);--設(shè)置主鍵約束、非空約束ALTERTABLEparticipantMODIFYplanIdIntNOTNULL; --設(shè)置非空約束ALTERTABLEparticipantMODIFYuserIdSMALLINTNOTNULL; --設(shè)置非空約束ALTERTABLEparticipantMODIFYdutyVARCHAR(1000)NOTNULL; --設(shè)置非空約束ALTERTABLEparticipantADDCONSTRAINTfk_userIdFOREIGNKEY(userId)REFERENCESuser(userId); --設(shè)置外鍵約束DESCparticipant;

--查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備3、設(shè)置表級數(shù)據(jù)完整性1）在創(chuàng)建表時設(shè)置約束創(chuàng)建表級數(shù)據(jù)完整性（字段的定義與表級數(shù)據(jù)完整性的定義要分開設(shè)置），語法格式如下：CREATETABLE[IFNOTEXISTS]表名(字段名字段類型字段完整性約束[,字段名字段類型字段完整性約束,…][,表完整性約束…]);需要說明以下幾點。字段級數(shù)據(jù)完整性可以與表級數(shù)據(jù)完整性同時設(shè)置。表級數(shù)據(jù)完整性的定義不能放在字段定義中，需要單獨定義。表級數(shù)據(jù)完整性定義可以在特定字段定義后設(shè)置，也可以在完成所有字段定義后再設(shè)置所有表級數(shù)據(jù)完整性。設(shè)置主鍵約束可以用“[CONSTRAINT約束名]PRIMARYKEY(字段列表)”，設(shè)置唯一約束可以用“[CONSTRAINT約束名]UNIQUE(字段列表)”。兩者的字段列表可以是一個字段也可以是多個字段。設(shè)置檢查約束的格式為“[CONSTRAINT約束名]CHECK(字段名關(guān)系運算符值[[邏輯運算符字段名關(guān)系運算符值]…])”，也可以使用“CHECK(字段名IN(值列表)”，表示可取值列表中任意一個值。設(shè)置外鍵約束的格式為“[CONSTRAINT約束名]FOREIGNKEY(字段名)REFERENCES主表名(主鍵字段)”。一、設(shè)置數(shù)據(jù)完整性知識儲備示例8-4通過表級數(shù)據(jù)完整性設(shè)置，完成工作人員表user的完整性約束設(shè)置。一、設(shè)置數(shù)據(jù)完整性知識儲備程序代碼如下：USEGDCI/*通過創(chuàng)建表來設(shè)置數(shù)據(jù)完整性*/CREATETABLEuser(/*定義字段和設(shè)置字段級數(shù)據(jù)完整性*/userIdSMALLINTNOTNULL,userNameVARCHAR(8)NOTNULL,fkTitleVARCHAR(10)NOTNULL,genderVARCHAR(2)NOTNULL,nationVARCHAR(10)NULLDEFAULT'漢族',--設(shè)置默認(rèn)值約束、可空約束birthdayDATENULL,nativePlaceVARCHAR(10)NULL,phoneVARCHAR(13)NOTNULL,/*設(shè)置表級數(shù)據(jù)完整性*/PRIMARYKEY(userId), --設(shè)置主鍵約束、非空約束UNIQUE(userName), --設(shè)置唯一約束CHECK(fkTitleIN('實習(xí)研究員','助理研究員','副研究員','研究員')),--設(shè)置檢查約束、非空約束CHECK(gender='男'ORgender='女') --設(shè)置檢查約束、非空約束);DESCuser; --查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備示例8-5通過表級數(shù)據(jù)完整性設(shè)置，完成工作計劃參與人員表participant的完整性約束設(shè)置。一、設(shè)置數(shù)據(jù)完整性知識儲備程序代碼如下：USEGDCI/*通過創(chuàng)建表來設(shè)置數(shù)據(jù)完整性*/CREATETABLEparticipant(/*定義字段和設(shè)置字段級約束*/idInt,planIdINTNOTNULL, --設(shè)置非空約束userIdSMALLINTNOTNULL, --設(shè)置非空約束dutyVARCHAR(1000)NOTNULL, --設(shè)置非空約束requirementVARCHAR(1000),remarksVARCHAR(500),/*設(shè)置表級約束*/PRIMARYKEY(id), --設(shè)置主鍵約束CONSTRAINTfk_userIdFOREIGNKEY(userId)REFERENCESuser(userId)--設(shè)置外鍵約束);DESCparticipant; --查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備4、刪除數(shù)據(jù)完整性設(shè)置在刪除數(shù)據(jù)完整性約束時，可能需要通過約束名來識別約束?？梢酝ㄟ^SHOWCREATETABLE語句來查詢表中所有字段和約束的設(shè)置情況。在設(shè)置約束時，如果沒有指定約束名，很多時候系統(tǒng)就會自動生成一個約束名。（1）刪除主鍵約束，操作方法如下：ALTERTABLE<表名>DROPPRIMARYKEY;（2）刪除外鍵約束，操作方法如下：ALTERTABLE<表名>DROPFOREIGNKEY約束名;或ALTERTABLE<表名>DROPCONSTRAINT約束名;（3）刪除非空約束，操作方法如下：ALTERTABLE<表名>MODIFY字段名字段類型[NULL];（4）刪除唯一約束，操作方法如下：ALTERTABLE<表名>DROPCONSTRAINT約束名;（5）刪除默認(rèn)值約束，操作方法如下：ALTERTABLE<表名>MODIFY字段名字段類型;或者：ALTERTABLE<表名>ALTERCOLUMN字段名DROPDEFAULT;（6）刪除檢查約束，操作方法如下：ALTERTABLE<表名>DROPCONSTRAINT約束名;一、設(shè)置數(shù)據(jù)完整性知識儲備示例8-6刪除工作計劃參與人員表participant中的完整性約束設(shè)置。一、設(shè)置數(shù)據(jù)完整性知識儲備程序代碼如下：USEGDCISHOWCREATETABLEuser; --查看表中的約束名ALTERTABLEuserDROPPRIMARYKEY; --刪除主鍵約束ALTERTABLEuserDROPCONSTRAINTusername; --刪除姓名的唯一約束ALTERTABLEuserDROPCONSTRAINTuser_chk_1; --刪除職稱的檢查約束ALTERTABLEuserDROPCONSTRAINTuser_chk_2; --刪除性別的檢查約束ALTERTABLEuserALTERCOLUMNnationDROPDEFAULT; --刪除民族的默認(rèn)值約束ALTERTABLEuserMODIFYuserIdSMALLINT; --刪除非空約束ALTERTABLEuserMODIFYuserNameVARCHAR(8); --刪除非空約束ALTERTABLEuserMODIFYfkTitleVARCHAR(10); --刪除非空約束ALTERTABLEuserMODIFYgenderVARCHAR(2); --刪除非空約束ALTERTABLEuserMODIFYphoneVARCHAR(13); --刪除非空約束DESCuser; --查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備5、設(shè)置表的自動添加字段1）設(shè)置自動添加字段在數(shù)據(jù)庫應(yīng)用中，系統(tǒng)會自動生成字段的主鍵值，即自動編號或自動添加字段。自動編號可用AUTO_INCREMENT關(guān)鍵字設(shè)置，設(shè)置自動編號的字段必須是int、tinyint、smallint等整數(shù)類型。在默認(rèn)情況下，自動編號初始值和增量都是1，并且不管是否刪除記錄，每次都是上次最大值加1。設(shè)置自動編號的語法格式如下：CREATETABLE[IFNOTEXISTS]表名(字段名字段類型AUTO_INCREMENT字段完整性約束[,字段名字段類型字段完整性約束,…]);一、設(shè)置數(shù)據(jù)完整性知識儲備5、設(shè)置表的自動添加字段2）設(shè)置字段備注通過設(shè)置字段備注，可以幫助閱讀者了解字段的含義。特別是當(dāng)文檔資料不慎丟失，沒有辦法查詢數(shù)據(jù)字典時，單憑字段名不一定能很好地理解字段內(nèi)涵，此時設(shè)置字段備注是一種很好的方法。設(shè)置備注的語法格式如下：CREATETABLE[IFNOTEXISTS]表名(字段名字段類型字段完整性約束COMMENT'字段備注'[,字段名字段類型字段完整性約束COMMENT'字段備注',…]);一、設(shè)置數(shù)據(jù)完整性知識儲備示例8-7完成新工作計劃參與人員表participantn的完整性約束設(shè)置。新工作計劃參與人員表participantn的結(jié)構(gòu)如表8-3所示。字段名數(shù)據(jù)類型是否為空約束說明idINTNOTNULL主鍵、自動增加記錄編號remarksVARCHAR(500)NULL

備注一、設(shè)置數(shù)據(jù)完整性知識儲備程序代碼如下：CREATETABLEparticipantn(idINTAUTO_INCREMENTPRIMARYKEYCOMMENT'記錄編號',--設(shè)置自動增加、帶備注等remarksVARCHAR(500)COMMENT'備注'--帶備注);DESCparticipantn;--查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備6、通過Navicat管理數(shù)據(jù)完整性啟動Navicat，連接數(shù)據(jù)庫服務(wù)器后，先選中指定數(shù)據(jù)庫，再執(zhí)行以下操作。（1）選中工作人員表user并進(jìn)入設(shè)計模式，單擊“字段”選項卡，選定字段后右擊，在快捷菜單中選擇“主鍵”命令，設(shè)置表的主鍵。（2）選中工作人員表user并進(jìn)入設(shè)計模式，單擊“字段”選項卡，選定字段后，在字段列表下方的“默認(rèn)：”框中輸入默認(rèn)值。（3）選中工作計劃參與人員表participant并進(jìn)入設(shè)計模式，單擊“外鍵”選項卡，顯示所有外鍵，選擇外鍵列表上方的“添加外鍵”命令，輸入外鍵名，選擇從表及從表字段、主表及主表主鍵等，保存外鍵，如圖8-5所示。選中指定外鍵，選擇“刪除外鍵”命令可刪除外鍵。一、設(shè)置數(shù)據(jù)完整性知識儲備（4）設(shè)置選項。選中工作計劃參與人員表participant并進(jìn)入設(shè)計模式，單擊“選項”選項卡，可設(shè)置引擎、字符集、自動遞增的增長值等信息，如圖8-6所示。一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-1創(chuàng)建完善的地區(qū)表area，并導(dǎo)入數(shù)據(jù)。字段名數(shù)據(jù)類型是否為空約束說明areaNumberCHAR(6)NOTNULL主鍵地區(qū)編號chineseNameVARCHAR(10)NOTNULL

中文名foreignNameVARCHAR(40)NULL

外文名aliasVARCHAR(40)NULL

別名geographicalPositionVARCHAR(40)NULL

地理位置areaDECIMAL(9,2)NOTNULL

面積populationSizeINTNOTNULL

人口數(shù)量areaCodeCHAR(4)NOTNULL

電話區(qū)號licensePlateCodeCHAR(4)NOTNULL

車牌代碼一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-2創(chuàng)建完善的民俗表folk，并導(dǎo)入數(shù)據(jù)。字段名數(shù)據(jù)類型是否為空約束說明idINTNOTNULL主鍵，自動增加記錄編號fkAreaNumberCHAR(6)NOTNULL外鍵地區(qū)編號folkNameVARCHAR(30)NOTNULL

民俗名稱folkIntroductionVARCHAR(1000)NULL

民俗介紹一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-3創(chuàng)建完善的名人表celebrity，并導(dǎo)入數(shù)據(jù)。字段名數(shù)據(jù)類型是否為空約束說明idINTNOTNULL主鍵，自動增加記錄編號fkAreaNumberCHAR(6)NOTNULL外鍵地區(qū)編號celebrityNameVARCHAR(8)NOTNULL

姓名profileVARCHAR(1000)NULL

人物簡介一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-4創(chuàng)建完善的榮譽(yù)表honor，并導(dǎo)入數(shù)據(jù)。字段名數(shù)據(jù)類型是否為空約束說明idINTNOTNULL主鍵，自動增加記錄編號fkAreaNumberCHAR(6)NOTNULL外鍵地區(qū)編號honoraryTitleVARCHAR(200)NOTNULL

榮譽(yù)稱號一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-5創(chuàng)建完善的工作人員表user，并確保表中的記錄不少于5條。字段名數(shù)據(jù)類型是否為空約束說明userIdSMALLINTNOTNULL主鍵，自動增加工號userNameVARCHAR(8)NOTNULL

姓名fkTitleVARCHAR(10)NOTNULL實習(xí)研究員、助理研究員、副研究員、研究員職稱genderVARCHAR(2)NOTNULL男、女性別nationVARCHAR(10)NULL默認(rèn)漢族民族birthdayDATENULL

出生日期nativePlaceVARCHAR(10)NULL

籍貫phoneVARCHAR(13)NOTNULL

手機(jī)號一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-6創(chuàng)建完善的工作計劃表plan，并確保表中的記錄不少于5條。字段名數(shù)據(jù)類型是否為空約束說明planIdINTNOTNULL主鍵，自動增加計劃編號planNameVARCHAR(60)NOTNULL

計劃名稱planMakerSMALLINTNOTNULL外鍵制訂者工號releaseTimeDATENOTNULL

發(fā)布時間planReviewerSMALLINTNOTNULL外鍵審核者工號auditTimeDATENULL

審核時間startTimeDATENULL

計劃開始時間endTimeDATENULL

計劃結(jié)束時間planContentVARCHAR(1000)NOTNULL

計劃內(nèi)容一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-7創(chuàng)建完善的工作計劃參與人員表participant，并確保表中的記錄不少于5條。字段名數(shù)據(jù)類型是否為空約束說明idINTNOTNULL主鍵，自動增加記錄編號planIdINTNOTNULL外鍵計劃編號userIdSMALLINTNOTNULL外鍵工號dutyVARCHAR(1000)NOTNULL

工作職責(zé)requirementVARCHAR(1000)NULL

工作要求remarksVARCHAR(500)NULL

備注一、設(shè)置數(shù)據(jù)完整性任務(wù)實施任務(wù)8-8創(chuàng)建完善的工作計劃項目表planforproject，并確保表中的記錄不少于5條。字段名數(shù)據(jù)類型是否為空約束說明idINTNOTNULL主鍵，自動增加記錄編號planIdINTNOTNULL外鍵計劃編號projectIdINTNOTNULL

活動項目編號，可以是民俗記錄編號、名人記錄編號或榮譽(yù)記錄編號typeINTNOTNULL

活動類型：0表示民俗類型，1表示名人類型，2表示城市榮譽(yù)remarksVARCHAR(500)NULL

備注02設(shè)置索引二、設(shè)置索引任務(wù)分析任務(wù)編號任務(wù)內(nèi)容任務(wù)8-9為工作人員表user的用戶名字段userName建立唯一索引iuserName，按照升序排列。任務(wù)8-10為工作計劃參與人員表participant的工作計劃編號字段planId創(chuàng)建普通索引iplanId，按照降序排列粵文創(chuàng)數(shù)據(jù)庫中部分表的數(shù)據(jù)量比較大，小王覺得系統(tǒng)反應(yīng)太慢，他想通過索引來提高查詢效率。小王對粵文創(chuàng)進(jìn)行分析后得到的任務(wù)清單如下。二、設(shè)置索引知識儲備1、初識索引在關(guān)系數(shù)據(jù)庫中，索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，由數(shù)據(jù)表中的一列或多列組合而成，可以用來快速查詢數(shù)據(jù)表中有某個特定值的記錄。索引的作用相當(dāng)于圖書的目錄，利用目錄能提高圖書查詢速度，而利用索引能提高在數(shù)據(jù)表中查詢數(shù)據(jù)的速度。使用索引不僅能大大加快數(shù)據(jù)的查詢速度，還能保證數(shù)據(jù)的唯一性（唯一索引），在實現(xiàn)數(shù)據(jù)參照完整性時，可以加速數(shù)據(jù)表與數(shù)據(jù)表之間的連接操作。但創(chuàng)建和維護(hù)索引需要耗費時間，并且隨著數(shù)據(jù)量的增加，所耗費的時間也會增加。索引不僅需要占用磁盤空間，還需要時間動態(tài)維護(hù)索引，所以索引并不是越多越好，而是需要根據(jù)項目實際科學(xué)、合理地規(guī)劃索引。二、設(shè)置索引知識儲備1、初識索引1）索引的分類（1）普通索引：由KEY或INDEX定義的索引，是MySQL中的基本索引類型，可以創(chuàng)建在任何數(shù)據(jù)類型中。（2）唯一索引：添加關(guān)鍵字UNIQUE的索引，該索引所在字段的值必須是唯一的。（3）全文索引：添加關(guān)鍵字FULLTEXT的索引，該索引是一種特殊類型的索引，查找文本中的關(guān)鍵詞，而不是直接比較索引中的值。全文索引適用于MATCHAGAINST操作，而不是普通的WHERE操作。全文索引支持各種字符內(nèi)容的搜索，包括char、varchar和text類型，也支持自然語言搜索和布爾搜索。（4）空間索引：添加關(guān)鍵字SPATIAL的索引，該索引是由SPATIAL定義的，只能創(chuàng)建在空間數(shù)據(jù)類型的字段上。MyISAM表支持空間索引，可以用作地理數(shù)據(jù)存儲?？臻g索引無須前綴查詢。二、設(shè)置索引知識儲備1、初識索引2）創(chuàng)建索引的注意事項（1）表的主鍵約束、唯一約束和外鍵約束必須有索引，在設(shè)置數(shù)據(jù)完整性約束時系統(tǒng)自動創(chuàng)建對應(yīng)的索引。（2）高頻使用的連接字段應(yīng)該創(chuàng)建索引。（3）高頻使用的條件字段應(yīng)該創(chuàng)建索引。（4）索引應(yīng)該建立在取值廣泛的字段上，如性別字段一般只有兩三個值，不適合創(chuàng)建索引。（5）索引應(yīng)該建立在值內(nèi)容不多的小字段上，對于內(nèi)容比較多的文本字段甚至超長字段，不需要創(chuàng)建索引。（6）頻繁進(jìn)行數(shù)據(jù)操作的表不需要創(chuàng)建太多的索引，單個表中索引的數(shù)量最好不要超過5個。二、設(shè)置索引知識儲備2、創(chuàng)建索引1）直接創(chuàng)建索引創(chuàng)建索引使用CREATEINDEX語句，語法格式如下：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX<索引名>ON<表名>(<字段名>[<長度>][ASC|DESC]);需要說明以下幾點。UNIQUE、FULLTEXT、SPATIAL索引類型選擇，如果指定索引類型表示普通索引，那么默認(rèn)為普通索引。一個表可以創(chuàng)建多個索引，但表中的索引名稱必須是唯一的。表名指定要創(chuàng)建索引的表，索引不能單獨存在，一定要依附某個表。字段列名即索引字段；長度為可選項，指定使用字段的前l(fā)ength個字符來創(chuàng)建索引，一般省略；ASC指定索引按照升序來排列，系統(tǒng)默認(rèn)為ASC；DESC指定索引按照降序來排列。二、設(shè)置索引知識儲備2、創(chuàng)建索引示例8-8新建表T(IDINT,NCHAR(5))，并為N創(chuàng)建唯一索引iname，按照升序排列。二、設(shè)置索引知識儲備2、創(chuàng)建索引程序代碼如下：DROPTABLEIFEXISTST;CREATETABLET(IDINT,NCHAR(5));CREATEUNIQUEINDEXinameONT(N);二、設(shè)置索引知識儲備2、創(chuàng)建索引2）在創(chuàng)建表時創(chuàng)建索引在創(chuàng)建表時創(chuàng)建索引，語法格式如下：CREATETABLE表名(字段定義,[UNIQUE|FULLTEXT|SPATIAL]INDEX<索引名>(<列名>[<長度>][ASC|DESC]));示例8-9新建表T(IDINT,NCHAR(5))，并為N創(chuàng)建普通索引iname，按照降序排列。程序代碼如下：DROPTABLEIFEXISTST;CREATETABLET(IDINT,NCHAR(5),INDEXiname(NDESC));二、設(shè)置索引知識儲備2、創(chuàng)建索引3）在修改表時創(chuàng)建索引在修改表時也可創(chuàng)建索引，語法格式如下：ALTERTABLE表名ADD索引類型INDEX[索引名](<字段名>[<長度>][ASC|DESC]);示例8-10先新建表T(IDINT,NCHAR(5))，再修改表，并為字段N創(chuàng)建唯一索引iname，按照降序排列。程序代碼如下：DROPTABLEIFEXISTST;CREATETABLET(IDINT,NCHAR(5));ALTERTABLETADDUNIQUEINDEXiname(NDESC);二、設(shè)置索引知識儲備3、管理索引1）查看索引可以使用SHOWINDEX語句查看索引，語法格式如下：SHOWINDEXFROM<表名>;2）刪除索引可以使用DROPINDEX語句刪除索引，語法格式如下：DROPINDEX索引名ON表名;示例8-11查看表T的索引，先刪除索引，再刪除該表。程序代碼如下：SHOWINDEXFROMT;DROPINDEXinameONT;DROPTABLET;二、設(shè)置索引知識儲備4、通過Navicat操作索引啟動Navicat，連接MySQL服務(wù)器，先選中指定的數(shù)據(jù)庫，再選中工作人員表user并進(jìn)入設(shè)計模式，單擊“索引”選項卡，顯示所有索引。（1）單擊“添加索引”按鈕，輸入索引名，指定索引字段、索引類型等信息，保存索引，如圖8-7所示。（2）選中索引后單擊“刪除索引”按鈕可以刪除索引。二、設(shè)置索引任務(wù)實施任務(wù)8-9為工作人員表user的用戶名字段userName建立唯一索引iuserName，按照升序排列。任務(wù)8-10為工作計劃參與人員表participant的工作計劃編號字段planId創(chuàng)建普通索引iplanId，按照降序排列。03鞏固與小結(jié)三、鞏固與小結(jié)任務(wù)分析（1）數(shù)據(jù)完整性的內(nèi)涵。（2）6種數(shù)據(jù)完整性約束：主鍵約束、外鍵約束、唯一約束、檢查約束、非空約束和默認(rèn)值約束。（3）一般單字段的約束常用字段級約束，多字段的約束常用表級約束，可以在定義表結(jié)構(gòu)時設(shè)置約束，也可以在修改表時設(shè)置約束。（4）約束的刪除方法。（5）索引的內(nèi)涵及分類。（6）索引的創(chuàng)建方法和管理方法。04任務(wù)訓(xùn)練四、任務(wù)訓(xùn)練任務(wù)分析（1）先創(chuàng)建用戶表gkeodm_user，