數(shù)據(jù)庫技術(shù)及應用 課件 陳翠松 項目8、9 設(shè)置數(shù)據(jù)完整性與索引、多表查詢應用

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

出生日期nativePlaceVARCHAR(10)NULL

籍貫phoneVARCHAR(13)NOTNULL

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

工作職責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è)置默認值約束、可空約束birthdayDATENULL,nativePlaceVARCHAR(10)NULL,phoneVARCHAR(13)NOTNULL,/*設(shè)置表級數(shù)據(jù)完整性*/PRIMARYKEY(userId), --設(shè)置主鍵約束、非空約束UNIQUE(userName), --設(shè)置唯一約束CHECK(fkTitleIN('實習研究員','助理研究員','副研究員','研究員')),--設(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）刪除默認值約束，操作方法如下：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; --刪除民族的默認值約束ALTERTABLEuserMODIFYuserIdSMALLINT; --刪除非空約束ALTERTABLEuserMODIFYuserNameVARCHAR(8); --刪除非空約束ALTERTABLEuserMODIFYfkTitleVARCHAR(10); --刪除非空約束ALTERTABLEuserMODIFYgenderVARCHAR(2); --刪除非空約束ALTERTABLEuserMODIFYphoneVARCHAR(13); --刪除非空約束DESCuser; --查看表結(jié)構(gòu)一、設(shè)置數(shù)據(jù)完整性知識儲備5、設(shè)置表的自動添加字段1）設(shè)置自動添加字段在數(shù)據(jù)庫應用中，系統(tǒng)會自動生成字段的主鍵值，即自動編號或自動添加字段。自動編號可用AUTO_INCREMENT關(guān)鍵字設(shè)置，設(shè)置自動編號的字段必須是int、tinyint、smallint等整數(shù)類型。在默認情況下，自動編號初始值和增量都是1，并且不管是否刪除記錄，每次都是上次最大值加1。設(shè)置自動編號的語法格式如下：CREATETABLE[IFNOTEXISTS]表名(字段名字段類型AUTO_INCREMENT字段完整性約束[,字段名字段類型字段完整性約束,…]);一、設(shè)置數(shù)據(jù)完整性知識儲備5、設(shè)置表的自動添加字段2）設(shè)置字段備注通過設(shè)置字段備注，可以幫助閱讀者了解字段的含義。特別是當文檔資料不慎丟失，沒有辦法查詢數(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ù)庫服務器后，先選中指定數(shù)據(jù)庫，再執(zhí)行以下操作。（1）選中工作人員表user并進入設(shè)計模式，單擊“字段”選項卡，選定字段后右擊，在快捷菜單中選擇“主鍵”命令，設(shè)置表的主鍵。（2）選中工作人員表user并進入設(shè)計模式，單擊“字段”選項卡，選定字段后，在字段列表下方的“默認：”框中輸入默認值。（3）選中工作計劃參與人員表participant并進入設(shè)計模式，單擊“外鍵”選項卡，顯示所有外鍵，選擇外鍵列表上方的“添加外鍵”命令，輸入外鍵名，選擇從表及從表字段、主表及主表主鍵等，保存外鍵，如圖8-5所示。選中指定外鍵，選擇“刪除外鍵”命令可刪除外鍵。一、設(shè)置數(shù)據(jù)完整性知識儲備（4）設(shè)置選項。選中工作計劃參與人員表participant并進入設(shè)計模式，單擊“選項”選項卡，可設(shè)置引擎、字符集、自動遞增的增長值等信息，如圖8-6所示。一、設(shè)置數(shù)據(jù)完整性任務實施任務8-1創(chuàng)建完善的地區(qū)表area，并導入數(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ù)完整性任務實施任務8-2創(chuàng)建完善的民俗表folk，并導入數(shù)據(jù)。字段名數(shù)據(jù)類型是否為空約束說明idINTNOTNULL主鍵，自動增加記錄編號fkAreaNumberCHAR(6)NOTNULL外鍵地區(qū)編號folkNameVARCHAR(30)NOTNULL

民俗名稱folkIntroductionVARCHAR(1000)NULL

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

姓名profileVARCHAR(1000)NULL

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

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

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

出生日期nativePlaceVARCHAR(10)NULL

籍貫phoneVARCHAR(13)NOTNULL

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

工作職責requirementVARCHAR(1000)NULL

工作要求remarksVARCHAR(500)NULL

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

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

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

備注02設(shè)置索引二、設(shè)置索引任務分析任務編號任務內(nèi)容任務8-9為工作人員表user的用戶名字段userName建立唯一索引iuserName，按照升序排列。任務8-10為工作計劃參與人員表participant的工作計劃編號字段planId創(chuàng)建普通索引iplanId，按照降序排列粵文創(chuàng)數(shù)據(jù)庫中部分表的數(shù)據(jù)量比較大，小王覺得系統(tǒng)反應太慢，他想通過索引來提高查詢效率。小王對粵文創(chuàng)進行分析后得到的任務清單如下。二、設(shè)置索引知識儲備1、初識索引在關(guān)系數(shù)據(jù)庫中，索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，由數(shù)據(jù)表中的一列或多列組合而成，可以用來快速查詢數(shù)據(jù)表中有某個特定值的記錄。索引的作用相當于圖書的目錄，利用目錄能提高圖書查詢速度，而利用索引能提高在數(shù)據(jù)表中查詢數(shù)據(jù)的速度。使用索引不僅能大大加快數(shù)據(jù)的查詢速度，還能保證數(shù)據(jù)的唯一性（唯一索引），在實現(xiàn)數(shù)據(jù)參照完整性時，可以加速數(shù)據(jù)表與數(shù)據(jù)表之間的連接操作。但創(chuàng)建和維護索引需要耗費時間，并且隨著數(shù)據(jù)量的增加，所耗費的時間也會增加。索引不僅需要占用磁盤空間，還需要時間動態(tài)維護索引，所以索引并不是越多越好，而是需要根據(jù)項目實際科學、合理地規(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)建對應的索引。（2）高頻使用的連接字段應該創(chuàng)建索引。（3）高頻使用的條件字段應該創(chuàng)建索引。（4）索引應該建立在取值廣泛的字段上，如性別字段一般只有兩三個值，不適合創(chuàng)建索引。（5）索引應該建立在值內(nèi)容不多的小字段上，對于內(nèi)容比較多的文本字段甚至超長字段，不需要創(chuàng)建索引。（6）頻繁進行數(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索引類型選擇，如果指定索引類型表示普通索引，那么默認為普通索引。一個表可以創(chuàng)建多個索引，但表中的索引名稱必須是唯一的。表名指定要創(chuàng)建索引的表，索引不能單獨存在，一定要依附某個表。字段列名即索引字段；長度為可選項，指定使用字段的前l(fā)ength個字符來創(chuàng)建索引，一般省略；ASC指定索引按照升序來排列，系統(tǒng)默認為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服務器，先選中指定的數(shù)據(jù)庫，再選中工作人員表user并進入設(shè)計模式，單擊“索引”選項卡，顯示所有索引。（1）單擊“添加索引”按鈕，輸入索引名，指定索引字段、索引類型等信息，保存索引，如圖8-7所示。（2）選中索引后單擊“刪除索引”按鈕可以刪除索引。二、設(shè)置索引任務實施任務8-9為工作人員表user的用戶名字段userName建立唯一索引iuserName，按照升序排列。任務8-10為工作計劃參與人員表participant的工作計劃編號字段planId創(chuàng)建普通索引iplanId，按照降序排列。03鞏固與小結(jié)三、鞏固與小結(jié)任務分析（1）數(shù)據(jù)完整性的內(nèi)涵。（2）6種數(shù)據(jù)完整性約束：主鍵約束、外鍵約束、唯一約束、檢查約束、非空約束和默認值約束。（3）一般單字段的約束常用字段級約束，多字段的約束常用表級約束，可以在定義表結(jié)構(gòu)時設(shè)置約束，也可以在修改表時設(shè)置約束。（4）約束的刪除方法。（5）索引的內(nèi)涵及分類。（6）索引的創(chuàng)建方法和管理方法。04任務訓練四、任務訓練任務分析（1）先創(chuàng)建用戶表gkeodm_user，再輸入記錄，并且輸入的記錄要不少于5條。用戶表gkeodm_user的結(jié)構(gòu)如表8-12所示。字段名數(shù)據(jù)類型是否為空約束說明userIdBIGINT(20)否主鍵用戶編號userNameVARCHAR(30)否默認值為空字符用戶名passwordVARCHAR(100)否默認值為空字符登錄密碼userTypeINT(11)否0表示普通用戶，1表示管理員，默認值為0用戶類型lastLoginTimeBIGINT(20)否默認值為0最后登錄時間（毫秒）enabledINT(11)否0表示可用，1表禁用，默認值為0是否禁用四、任務訓練任務分析（2）先創(chuàng)建餐桌表gkeodm_table，再輸入記錄，并且輸入的記錄要不少于5條。餐桌表gkeodm_table的結(jié)構(gòu)如表8-13所示。字段名數(shù)據(jù)類型是否為空約束說明idBIGINT(20)否主鍵編號tableNameVARCHAR(20)否

餐桌名稱capacityINT(11)否默認值為0容納人數(shù)四、任務訓練任務分析（3）先創(chuàng)建菜品分類表gkeodm_category，再輸入記錄，并且輸入的記錄要不少于5條。菜品分類表gkeodm_category的結(jié)構(gòu)如表8-14所示。字段名數(shù)據(jù)類型是否為空約束說明idBIGINT(20)否主鍵分類編號nameVARCHAR(30)否唯一索引，默認值為空字符分類名稱createDateDATE否

分類創(chuàng)建時間userIdBIGINT(20)否外鍵創(chuàng)建人編號picVARCHAR(100)是默認值為空字符圖標地址四、任務訓練任務分析（4）先創(chuàng)建菜品表gkeodm_food，再輸入記錄，并且輸入的記錄要不少于5條。菜品表gkeodm_food的結(jié)構(gòu)如表8-15所示。字段名數(shù)據(jù)類型是否為空約束說明idBIGINT(20)否主鍵菜品編號nameVARCHAR(30)否唯一索引菜品名稱labelINT否1表示健身，2表示減肥，3表示補腎，4表示去火，5表示活血，6表示補水，默認值為1菜品標簽descriptionVARCHAR(255)是（不超200字）菜品詳情描述createDateDATE否

菜品創(chuàng)建時間userIdBIGINT(20)否外鍵創(chuàng)建人編號deletedINT(11)否0表示可用，1表示已刪除，默認值為0刪除標識categoryIdBIGINT(20)否外鍵所屬分類編號picVARCHAR(100)是默認值為空字符菜品圖片地址priceINT(11)否默認值為0菜品價格四、任務訓練任務分析（5）先創(chuàng)建訂單表gkeodm_order，再輸入記錄，并且輸入的記錄要不少于5條。訂單表gkeodm_order的結(jié)構(gòu)如表8-16所示。字段名數(shù)據(jù)類型是否為空約束說明idBIGINT(20)否主鍵訂單編號tableNumINT(11)否外鍵餐桌序號createDateDATE否

訂單創(chuàng)建時間userIdBIGINT(20)否外鍵創(chuàng)建人編號dinerVARCHAR(10)是

訂餐人telVARCHAR(20)否

聯(lián)系電話dinnerTimeVARCHAR(20)是

用餐時間priceINT(11)否計算列，默認值為-1訂單總價statusINT(11)否0表示待付款，1表示已付款，2表示已取消，默認值為0訂單狀態(tài)四、任務訓練任務分析（6）先創(chuàng)建訂單詳情表gkeodm_orderDetail，再輸入記錄，并且輸入的記錄要不少于5條。訂單詳情表gkeodm_orderDetail的結(jié)構(gòu)如表8-17所示。字段名數(shù)據(jù)類型是否為空約束說明idBIGINT(20)否主鍵編號orderIdBIGINT(20)否外鍵訂單編號foodIdBIGINT(20)否外鍵菜品編號numINT(11)否默認值為0菜品數(shù)量感謝您的觀看數(shù)據(jù)庫技術(shù)及應用項目9多表查詢應用很多時候，查詢的數(shù)據(jù)在同一個表中，所以查詢比較簡單。當用戶需求比較復雜時，一個表的數(shù)據(jù)解決不了問題，因此需要從多個表中獲取數(shù)據(jù)，此時可以利用多表查詢和子查詢來完成特定任務。工作情境目錄01使用連接查詢02使用子查詢03鞏固與小結(jié)04任務訓練01使用連接查詢一、使用連接查詢?nèi)蝿辗治鲂⊥醢l(fā)現(xiàn)，一個數(shù)據(jù)表中的數(shù)據(jù)有時無法滿足用戶的需求，需要同時從多個表中選擇數(shù)據(jù)才能滿足用戶的需求。例如，要查詢各個地方的名人，但名人表celebrity中只有地區(qū)編號，沒有地區(qū)名稱，要確認地區(qū)名稱需要使用地區(qū)表area中的數(shù)據(jù)，此時需要2個表中的數(shù)據(jù)。小王決定采用連接查詢來實現(xiàn)多表查詢。小王對粵文創(chuàng)進行分析后得到的任務清單如下。任務編號任務內(nèi)容任務9-1查詢廣州有哪些民俗，并顯示id、chineseName和folkName信息任務9-2查詢中山有哪些名人，并顯示id、chineseName和celebrityName信息任務9-3查詢東莞有哪些城市名譽，并顯示id、chineseName和honoraryTitle信息任務9-4查詢工作計劃參與人員表participant，并顯示id、planName、userName、duty、requirement和remarks信息任務9-5查詢工作計劃表plan，并顯示planId、planName、planMaker、releaseTime、auditTime、startTime、endTime和planContent信息，其中planMaker用來顯示姓名而不是工號任務9-6查詢還沒有分配工作的工作人員一、使用連接查詢知識儲備1、使用WHERE關(guān)鍵字實現(xiàn)多表查詢使用WHERE關(guān)鍵字實現(xiàn)多表查詢，語法格式如下：SELECT字段列表FROM表名列表WHERE條件表達式需要說明以下幾點。在表名列表中，允許有多個表，但各個表名之間需要用逗號隔開。要把兩個表連接起來，需要找到兩個表中相同意義的字段作為連接條件，如果兩個字段名相同，那么必須在字段名前加表名和“.”，明確指出該字段來自哪個表。多個表要設(shè)置多個連接條件。條件表達式不僅可以包括連接條件，還可以同時包括其他條件，中間用邏輯運算符連接。一、使用連接查詢知識儲備示例9-1查詢廣東各個地區(qū)的名人，顯示id、chineseName和celebrityName信息。一、使用連接查詢知識儲備分析：id和celebrityName來自名人表celebrity，chineseName來自地區(qū)表area，兩個表的連接條件是地區(qū)編號相同，即areaNumber=fkAreaNumber。查詢的結(jié)果與數(shù)據(jù)源有關(guān)，可能會查到數(shù)據(jù)，也可能查不到數(shù)據(jù)，但一定要確保查詢命令正確。在操作前，可查詢相關(guān)表中的數(shù)據(jù)，根據(jù)連接條件進行分析，估計有哪些結(jié)果數(shù)據(jù)，并與顯示數(shù)據(jù)進行對比，判斷查詢結(jié)果是否正確。名人表celebrity和地區(qū)表area中都有不少數(shù)據(jù)，結(jié)果數(shù)據(jù)比較多，分析比較麻煩。但在企業(yè)實際工作中，各個表中的數(shù)據(jù)都是比較多的，所以應習慣面對大量的數(shù)據(jù)處理。程序代碼如下：SELECT*FROMcelebrity;SELECT*FROMarea;SELECTid,chineseName,celebrityNameFROMcelebrity,areaWHEREareaNumber=fkAreaNumber;一、使用連接查詢知識儲備示例9-2查詢惠州有哪些名人，并顯示id、chineseName和celebrityName信息。一、使用連接查詢知識儲備分析：在示例9-1的基礎(chǔ)上，增加一個查詢條件，即查詢惠州的名人，只在WHERE中添加這條件即可，兩個條件要同時滿足，并用AND連接。程序代碼如下：SELECTid,chineseName,celebrityNameFROMcelebrity,areaWHEREareaNumber=fkAreaNumberANDchineseName="惠州";一、使用連接查詢知識儲備示例9-3查詢id、planName、userName、duty和requirement信息。一、使用連接查詢知識儲備分析：planName字段來自工作計劃表plan，userName來自工作人員表user，id、duty和requirement來自工作計劃參與人員表participant，因此該查詢需要3個表的數(shù)據(jù)，要設(shè)置表與表之間的連接條件，工作人員表user與工作計劃參與人員表participant之間有相同的字段userId，可以作為兩個表的連接條件；工作計劃表plan與工作計劃參與人員表participant之間有相同的字段planId，可以作為兩表連接條件。程序代碼如下：SELECTid,planName,userName,duty,requirementFROMuser,plan,participantWHEREuser.userId=participant.userIdANDplan.planId=participant.planId;一、使用連接查詢知識儲備2、多表連接查詢1）交叉連接使用WHERE關(guān)鍵字實現(xiàn)多表查詢時，可以省略條件表達式，即沒有連接條件，此時又稱為交叉連接。交叉連接是指沒有設(shè)置連接條件的多表查詢，從每個表中各取一條記錄組新的記錄，因此交叉連接的結(jié)果數(shù)據(jù)量非常大。交叉連接的語法格式如下：SELECT字段列表FROM表名列表一、使用連接查詢知識儲備2、多表連接查詢示例9-4新建表T1，該表包含3個CHAR(2)字段，分別為A、B和C；新建表T2，該表包含3個CHAR(2)字段，分別為A、E和F，每個表各輸入3條記錄，交叉連接的信息如圖9-1所示。一、使用連接查詢知識儲備2、多表連接查詢分析：表T1和表T2各有3個字段，交叉連接后共有6個字段。在表T1中取第1條記錄，與表T2中的3條記錄分別組合，可以產(chǎn)生3條新記錄；在表T1中取第2條記錄，與表T2中的3條記錄分別組合，又可以產(chǎn)生3條新記錄；在表T1中取第3條記錄，與表T2中的3條記錄分別組合，又可產(chǎn)生3條新記錄。最終得到9條記錄。程序代碼如下：CREATETABLET1(ACHAR(2),BCHAR(2),CCHAR(2));INSERTINTOT1(A,B,C)VALUES("X","1","1"),("Y","1v,"1"),("Z","1","1");CREATETABLET2(ACHAR(2),ECHAR(2),FCHAR(2));INSERTINTOT2(A,E,F)VALUES("X","2","2"),("Y","2","2"),("T","2","2");SELECT*FROMT1;SELECT*FROMT2;SELECT*FROMT1,T2;一、使用連接查詢知識儲備2、多表連接查詢示例9-5用交叉連接實現(xiàn)示例9-1，即用交叉連接顯示id、chineseName和celebrityName信息。一、使用連接查詢知識儲備2、多表連接查詢分析：當名人表celebrity中有228條記錄，地區(qū)表area中有21條記錄時，交叉連接的結(jié)果為4788（228×21）條記錄，數(shù)據(jù)量比較大，運行時展示數(shù)據(jù)的時間比較長，需要耐心等待。程序代碼如下：SELECT*FROMcelebrity;SELECT*FROMarea;SELECTid,chineseName,celebrityNameFROMcelebrity,area;一、使用連接查詢知識儲備2、多表連接查詢2）內(nèi)連接交叉連接產(chǎn)生的數(shù)據(jù)量非常大，因此需要設(shè)置條件過濾部分數(shù)據(jù)，即連接條件。設(shè)置了連接條件的交叉連接稱為內(nèi)連接。如果連接條件運算符是等于號，那么稱為等值連接，否則稱為非等值連接。在等值連接的基礎(chǔ)上去除重復字段稱為自然連接。在一般情況下，內(nèi)連接一般是指等值連接。內(nèi)連接的語法格式如下：SELECT字段列表FROM表1[INNER]JOIN表2ON表1.字段名=表2.字段名[JOIN表3ON表1.字段名|表2.字段名=表3.字段名…]一、使用連接查詢知識儲備2、多表連接查詢示例9-6通過內(nèi)連接查詢表T1和表T2的信息。分析：在如圖9-1所示的交叉連接的結(jié)果中，查找表T1的字段A和表T2的字段A的相同值，發(fā)現(xiàn)第1行的兩個值都是X，第5行的兩個值都是Y，其他都不相同，即結(jié)果為第1行和5行數(shù)據(jù)。內(nèi)連接相當于找兩個表指定字段的交集，如圖9-3所示。程序代碼如下：SELECT*FROMT1JOINT2ONT1.A=T2.A;一、使用連接查詢知識儲備2、多表連接查詢示例9-7用內(nèi)連接實現(xiàn)示例9-1，即使用內(nèi)連接顯示id、chineseName和celebrityName信息。分析：將WHERE實現(xiàn)的多表查詢轉(zhuǎn)換為通過內(nèi)連接方式實現(xiàn)，只需要將連接條件換一下位置即可。程序代碼如下：SELECTid,chineseName,celebrityNameFROMcelebrityJOINareaONareaNumber=fkAreaNumber;示例9-8用內(nèi)連接查詢佛山有哪些名人，并顯示id、chineseName和celebrityName信息。程序代碼如下：SELECTid,chineseName,celebrityNameFROMcelebrityJOINareaONareaNumber=fkAreaNumberWHEREchineseName="佛山";示例9-9用內(nèi)連接實現(xiàn)示例9-3，并查詢id、planName、userName、duty和requirement信息。程序代碼如下：SELECTid,planName,username,duty,requirementFROMparticipantJOINuserONuser.userId=participant.userIdJOINplanONplan.planId=participant.planId;一、使用連接查詢知識儲備2、多表連接查詢3）外連接在圖9-3中，除了交集，表T1還有1條獨有記錄，即字段A值為Z對應的記錄；同樣，表T2也有1條獨有記錄，即字段A值為T對應的記錄。外連接是對內(nèi)連接的結(jié)果進行擴展，如果連接時T1表在左邊而T2表在右邊，那么內(nèi)連接擴展表T1的獨有記錄稱為左外連接，內(nèi)連接擴展表T2的獨有記錄稱為右外連接，內(nèi)連接擴展到表T1和表T2的所有獨有記錄稱為全連接。MySQL暫不支持全連接。左外連接的語法格式如下：SELECT字段列表FROM表1LEFT[JOIN]表2ON表1.字段名=表2.字段名;右外連接的語法格式如下：SELECT字段列表FROM表1RIGHT[JOIN]表2ON表1.字段名=表2.字段名;一、使用連接查詢知識儲備2、多表連接查詢示例9-10通過左外連接查詢表T1和表T2的信息。程序代碼如下：SELECT*FROMT1LEFTJOINT2ONT1.A=T2.A;運行結(jié)果如圖9-4所示。示例9-11通過右外連接查詢表T1和表T2的信息。程序代碼如下：SELECT*FROMT1RIGHTJOINT2ONT1.A=T2.A;運行結(jié)果如圖9-5所示。一、使用連接查詢知識儲備2、多表連接查詢示例9-12查詢還沒輸入名人信息的地區(qū)，并顯示areaNumber和chineseName。一、使用連接查詢知識儲備2、多表連接查詢分析：先通過左外連接將地區(qū)表area和名人表celebrity連起來，得到地區(qū)表area中的所有數(shù)據(jù)，如果某地區(qū)編號在名人表celebrity中也存在，那么名人表celebrity也會顯示對應地區(qū)編號的信息；如果某地區(qū)編號在名人表celebrity中不存在，那么系統(tǒng)顯示NULL，即該地區(qū)還沒有輸入名人信息。因此，可以將celebrity.fkAreaNumberISNULL作為查詢條件。程序代碼如下：SELECTareaNumber,chineseNameFROMareaLEFTJOINcelebrityONarea.areaNumber=celebrity.fkAreaNumberWHEREcelebrity.fkAreaNumberISNULL;一、使用連接查詢知識儲備2、多表連接查詢示例9-13顯示名人表celebrity中地區(qū)編碼錯誤的記錄。一、使用連接查詢知識儲備2、多表連接查詢分析：先通過右外連接將地區(qū)表area和名人表celebrity連起來，得到名人表celebrity中的所有數(shù)據(jù)。如果某地區(qū)編號在地區(qū)表area中也存在，就會顯示地區(qū)表area中該地區(qū)編號對應的信息；如果某地區(qū)編號在地區(qū)表area中不存在，那么系統(tǒng)顯示NULL，表示該地區(qū)在名人表celebrity中存在，但在地區(qū)表area中不存在，即該記錄為名人表celebrity中錯誤的地區(qū)編碼。因此，可以將area.areaNumberISNULL作為查詢條件。程序代碼如下：SELECTcelebrity.*FROMareaRIGHTJOINcelebrityONarea.areaNumber=celebrity.fkAreaNumberWHEREarea.areaNumberISNULL;一、使用連接查詢知識儲備3、設(shè)置表的別名在查詢時，可以為表設(shè)置別名，特別是當表名比較長時，設(shè)置簡短的別名能提高操作效率。在定義了表的別名之后，在需要指定表名的地方都可以使用表的別名。設(shè)置表的別名的語法格式如下：SELECT字段列表FROM表1[AS]表別名1LEFT|RIGHT|JOIN表2[AS]表別名2ON表1.字段名=表2.字段名;示例9-14通過別名實現(xiàn)示例9-9，并查詢id、planName、userName、duty和requirement信息。程序代碼如下：SELECTid,planName,userName,duty,requirementFROMparticipantaJOINuserbONa.userId=b.userIdJOINplancONa.planId=c.planId;一、使用連接查詢知識儲備4、聯(lián)合查詢聯(lián)合查詢是指將兩個或多個查詢結(jié)果合并在一起顯示。其語法格式如下：SELECT字段列表1FROM表1WHERE條件1UNIONSELECT字段列表2FROM表2WHERE條件2;示例9-15小王想去深圳（地區(qū)編碼為5840）調(diào)研，打算把深訓的民俗、名人和城市榮譽列在一張清單上。程序代碼如下：SELECTfkAreaNumberAS地區(qū),folkNameAS調(diào)研內(nèi)容FROMfolkWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,celebrityNameFROMcelebrityWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,honoraryTitlefolkNameFROMhonorWHEREfkAreaNumber="5840";示例9-16優(yōu)化示例9-15，增加一列，用來顯示調(diào)研內(nèi)容的類型，如民俗、名人和城市榮譽等。程序代碼如下：SELECTfkAreaNumberAS地區(qū),folkNameAS調(diào)研內(nèi)容,"民俗"AS類型FROMfolkWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,celebrityName,"名人"FROMcelebrityWHEREfkAreaNumber="5840"UNIONSELECTfkAreaNumber,honoraryTitle,"城市榮譽"FROMhonorWHEREfkAreaNumber="5840";一、使用連接查詢?nèi)蝿諏嵤┤蝿?-1查詢廣州有哪些民俗，并顯示id、chineseName和folkName信息。任務9-2查詢中山有哪些名人，并顯示id、chineseName和celebrityName信息。任務9-3查詢東莞有哪些城市名譽，并顯示id、chineseName和honoraryTitle信息。任務9-4查詢工作計劃參與人員表participant，并顯示id、planName、userName、duty、requirement和remarks信息。任務9-5查詢工作計劃表plan，并顯示planId、planName、planMaker、releaseTime、auditTime、startTime、endTime和planContent信息，其中planMaker用來顯示姓名而不是工號。任務9-6查詢還沒有分配工作的工作人員。02使用子查詢二、使用子查詢?nèi)蝿辗治龀诉B接查詢，小王發(fā)現(xiàn)還