項目三數(shù)據(jù)表的創(chuàng)建與管理任務(wù)3.1數(shù)據(jù)完整性任務(wù)3.2創(chuàng)建表結(jié)構(gòu)任務(wù)3.3修改數(shù)據(jù)表結(jié)構(gòu)123主要知識點數(shù)據(jù)完整性及實現(xiàn)方法運用SSMS創(chuàng)建表結(jié)構(gòu)運用命令方式管理表結(jié)構(gòu)表的修改與維護(hù)學(xué)習(xí)目標(biāo)數(shù)據(jù)完整性類型數(shù)據(jù)完整性實現(xiàn)方法創(chuàng)建表修改表刪除表維護(hù)表結(jié)構(gòu)任務(wù)3.1數(shù)據(jù)完整性3.1.1

數(shù)據(jù)完整性類型3.1.2數(shù)據(jù)完整性約束實現(xiàn)數(shù)據(jù)完整性3.1.1

數(shù)據(jù)完整性類型數(shù)據(jù)完整性包括域完整性、實體完整性、參照完整性、自定義完整性。數(shù)據(jù)表，是數(shù)據(jù)庫的最主要組成成分，由若干欄目（列或字段）和若干行組成，每一行稱為一條記錄。所有列全部加起來組成表結(jié)構(gòu)，表就是由結(jié)構(gòu)和記錄兩部分組成的。每個欄目均需要設(shè)置其名稱（即列名、字段名）、數(shù)據(jù)類型、長度、約束，列名必須符合標(biāo)識符的要求，數(shù)據(jù)類型由系統(tǒng)規(guī)定，長度是一個整數(shù)，表示這個列最大可以輸入多少個字符，而約束是對這個列的值設(shè)置的限制條件，稱為域完整性約束。各個字段之間可能存在的關(guān)聯(lián)關(guān)系，稱為實體完整性約束。表與表之間也可能存在的相互關(guān)系，稱為參照完整性約束。任務(wù)3.1數(shù)據(jù)完整性3.1.1

數(shù)據(jù)完整性類型數(shù)據(jù)完整性包括4種類型：實體完整性、域完整性、參照完整性、自定義完整性。域完整性：作用的對象是列（字段），是給指定列的輸入設(shè)置有效性約束條件。強(qiáng)制域有效性的方法有：限制類型(通過數(shù)據(jù)類型)、格式(通過CHECK約束和規(guī)則)或可能值的范圍(通過FOREIGNKEY約束、CHECK約束、DEFAULT定義、NOTNULL定義和規(guī)則)。實體完整性：作用的對象是行（記錄），強(qiáng)制表的標(biāo)識符列或主鍵的完整性（通過UNIQUE約束、PRIMARYKEY約束或IDENTITY屬性約束）。參照完整性：作用的對象是關(guān)系（表）。在輸人或刪除記錄時，參照完整性保持表之間已定義的關(guān)系?；谧颖硗怄I與主表主鍵之間或子表外鍵與主表唯一鍵之間的關(guān)系(通過FOREIGNKEY和CHECK約束)。用戶定義完整性：用戶可以定義不屬于其他任何完整性分類的特定業(yè)務(wù)規(guī)則，作用的對象可以是列，也可以是記錄或表。所有的完整性類型都支持用戶定義完整性，如CREATETABLE中的所有列級和表級約束、存儲過程和觸發(fā)器。任務(wù)3.1數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)增加約束的命令格式是：

addconstraint約束名約束內(nèi)容約束名的通用格式是：約束類型_列名，如：主鍵約束PK_，唯一性約束UQ_，外鍵約束FK_，檢查約束CK_，默認(rèn)值約束DF_等。以libsys數(shù)據(jù)庫的表bookinfo（BookID，BookName，BookType，Writer，Publisher，PublishDate，Price，BuyDate，BuyCount，AbleCount，Remark）為例，實現(xiàn)數(shù)據(jù)完整性約束。任務(wù)3.1數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)1、實體完整性約束的實現(xiàn)

包括PRIMARYKEY（主鍵約束）、UNIQUE（唯一性約束）和IDENTITY（標(biāo)識列約束）等。（1）PRIMARYKEY主鍵約束的實現(xiàn)。SSMS實現(xiàn)：在表的設(shè)計窗口中，選擇主鍵列，單擊快捷菜單【設(shè)置主鍵】即可。任務(wù)3.1數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)1、實體完整性約束的實現(xiàn)

任務(wù)3.1用命令實現(xiàn)先定義一個約束PK_bookid，命令是：addconstrainPK_bookidprimarykeybookid，將以上命令加進(jìn)建表命令的后面。也可以在建立列bookid的命令中添加primarykey或者constrainPK_bookidprimarykey。如：createtablebookinfo(Bookidchar(20)constraintPK_bookidprimarykey,

--這樣也可以：Bookidchar(20)primarykey,……)如果要設(shè)置BookName和Writer兩列均為主鍵，則命令是：addconstraintPK_BookName_Writerprimarykey(BookName,Writer)數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)1、實體完整性約束的實現(xiàn)

任務(wù)3.1（2）UNIQUE唯一性約束的實現(xiàn)用SSMS實現(xiàn)：任務(wù)：BookID為唯一性約束在表設(shè)計器中，選擇【索引和鍵】，打開“索引/鍵”對話框，點擊【添加】按鈕，創(chuàng)建索引IX_bookinfo，選擇bookid列，將類型修改為“是唯一的”，關(guān)閉窗口后保存修改。數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)1、實體完整性約束的實現(xiàn)

任務(wù)3.1（2）UNIQUE唯一性約束的實現(xiàn)用命令實現(xiàn)：任務(wù)：BookID為唯一性約束用命令為表bookinfo創(chuàng)建“UNIQUE約束”：altertablebookinfoaddconstraintUQ_bookidunique(bookid)或者在建表時直接給bookid列增加UNIQUE約束：createtablebookinfo(

Bookidchar(20)constraintUQ_bookidunique(bookid),--Bookidchar(20)unique,……)數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)1、實體完整性約束的實現(xiàn)

任務(wù)3.1（3）標(biāo)識列約束的實現(xiàn)標(biāo)識列（identity）就是給表輸入記錄時此列自動編號，即流水號，默認(rèn)從1開始編號，稱為標(biāo)識種子，增量為1，標(biāo)識列要求是整數(shù)，通常為int類型，標(biāo)識列的值不能手動輸入，不能為空，不能修改。任務(wù)：給表bookinfo增加一列no，要求自動編號。用SSMS實現(xiàn)的方法：打開表設(shè)計窗口，選擇no列，在底部的列屬性窗格中，雙擊【標(biāo)識規(guī)范】欄的【是標(biāo)識】，即可在“是”與“否”之間進(jìn)行切換。數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)1、實體完整性約束的實現(xiàn)

任務(wù)3.1（3）標(biāo)識列約束的實現(xiàn)用命令實現(xiàn)的方法：createtablebookinfo(nointidentity(1,1),……)總結(jié)：建立了約束后，如果要在SSMS窗口中以交互方式刪除約束，方法與建立時相同，如果用命令實現(xiàn)，命令是dropconstraint約束名。數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)2、域完整性約束的實現(xiàn)

任務(wù)3.1（2）默認(rèn)值約束的實現(xiàn)假設(shè)要將bookinfo表的出版日期列publishdate的默認(rèn)值設(shè)為系統(tǒng)日期，對應(yīng)的系統(tǒng)函數(shù)是getdate()，處理方法是：用SSMS窗口：打開bookinfo表設(shè)計窗口，選擇列publishdate，在下方的列屬性窗格中，將屬性“默認(rèn)值或綁定”設(shè)為getdate()。用命令：建表窗口中，給列publishdate增加默認(rèn)值約束defaultgetdate()，即：PublishdatedatetimenullconstraintDF_publishdatedefaultgetdate()或者Publishdatedatetimenulldefaultgetdate()或者Publishdatedatetimenulldefault(getdate())數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)2、域完整性約束的實現(xiàn)

任務(wù)3.1（3）檢查約束的實現(xiàn)CHECK檢查約束通過限制輸入到列中的值來強(qiáng)制域的完整性。格式是：check邏輯表達(dá)式。任務(wù)：將readerinfo表ReaderAge列的取值范圍限制在18～60歲之間，表示式為：checkReaderAge>=18andReaderAge<=60。用SSMS實現(xiàn)：單擊工具欄中的【管理CHECK約束】按鈕，打開CHECK約束對話框。單擊【添加】按鈕，在名稱輸入框中把默認(rèn)名改為CK_ReaderAge，在表達(dá)式右端輸入表達(dá)式：checkReaderAgebetween18and60。數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)2、域完整性約束的實現(xiàn)

任務(wù)3.1（3）檢查約束的實現(xiàn)任務(wù)：將readerinfo表ReaderAge列的取值范圍限制在18～60歲之間，表示式為：checkReaderAge>=18andReaderAge<=60。用命令實現(xiàn)：如果作為表定義的一部分在創(chuàng)建表時創(chuàng)建檢查約束，命令是：ReaderAgeintconstraintCK_ReaderAgecheckReaderAge>=18andReaderAge<=60

或者ReaderAgeintcheckReaderAgebetween18and60也可以添加到現(xiàn)有表中。如：AddconstraintCK_ReaderAgecheckReaderAge>=18andReaderAge<=60數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)3、參照完整性約束的實現(xiàn)

任務(wù)3.1參照完整性指的是表與表之間存在的相互依賴性，如數(shù)據(jù)庫libsys中，有3張表，即bookinfo圖書信息表、readerinfo讀者信息表和borrowinfo借閱情況表，borrowinfo記錄的信息是哪些讀者在什么時候借閱了哪些書，因此borrowinfo表的readerID列（表示讀者號）來自于borrowinfo表中的readerID，bookid列（表示書號）來源于bookinfo表的bookid列，用外鍵約束（foreignkey）實現(xiàn)，bookinfo和readerinfo稱為主鍵表，borrowinfo稱為外鍵表。用SSMS實現(xiàn)：用表設(shè)計器打開外鍵表borrowinfo，單擊工具欄中的【關(guān)系】按鈕，打開【外鍵關(guān)系】對話框。單擊【添加】按鈕，添加一個關(guān)系，單擊【表和列規(guī)范】右端的按鈕，打開【表和列】對話框。主鍵表選擇表BookInfo，主鍵為字段BookID，外鍵表選擇borrowinfo，外鍵為字段BookID，點擊【確定】，返回關(guān)系對話框，將標(biāo)識名稱修改為FK_borrowinf_BookInfo_BookID。同樣的方法設(shè)置外鍵Readerid。數(shù)據(jù)完整性3.1.2

數(shù)據(jù)完整性約束的實現(xiàn)3、參照完整性約束的實現(xiàn)

任務(wù)3.1參照完整性指的是表與表之間存在的相互依賴性，如數(shù)據(jù)庫libsys中，有3張表，即bookinfo圖書信息表、readerinfo讀者信息表和borrowinfo借閱情況表，borrowinfo記錄的信息是哪些讀者在什么時候借閱了哪些書，因此borrowinfo表的readerID列（表示讀者號）來自于borrowinfo表中的readerID，bookid列（表示書號）來源于bookinfo表的bookid列，用外鍵約束（foreignkey）實現(xiàn)，bookinfo和readerinfo稱為主鍵表，borrowinfo稱為外鍵表。用命令實現(xiàn)：在建立外鍵表borrowinfo的命令中，設(shè)置列ReaderID時，增加外鍵約束，命令是：ReaderIDchar(10)primarykeyforeignkeyreferencesReaderInfo(ReaderID)或者ReaderIDchar(10)primarykeyconstraintFK_BorrowInfo_ReaderInfo_ReaderIDforeignkeyreferencesReaderInfo(ReaderID)2.同樣建立外鍵表borrowinfo的外鍵BookID的命令是：BookIDchar(10)primarykeyforeignkeyreferencesBookInfo(BookID)或者ReaderIDchar(10)primarykeyconstraintFK_BorrowInfo_BookInfo_BookIDforeignkeyreferencesBookInfo(BookID)創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)3.2.1

用SSMS創(chuàng)建表結(jié)構(gòu)例3-1利用SSMS給libsys添加讀者信息表readerinfo。

任務(wù)3.2【數(shù)據(jù)庫】-【libsys】-【表】，右擊“表”，-【新建】-【表】。設(shè)置各列的列名、數(shù)據(jù)類型、允許Null值設(shè)置主鍵保存表創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)3.2.2

用CREATETABLE命令創(chuàng)建表結(jié)構(gòu)建立表的命令格式是：

任務(wù)3.2CREATETABLE表名(列名類型(長度)｛列約束｝[,…n][表約束])例3-2利用SQL命令給libsys數(shù)據(jù)庫添加讀者信息表ReaderInfo，要求如圖：

USElibsysGO--建立ReaderInfo表的結(jié)構(gòu)CREATETABLEReaderInfo(

ReaderIDchar(10)PRIMARYKEY,

ReaderNamechar(10)NOTNULL,

ReaderSexchar(2)NOTNULL,

ReaderAgeintNULL,

Departmentvarchar(30)NOTNULL,

ReaderTypechar(10)NOTNULL,

StartDatedatetimeNOTNULL,

Mobilevarchar(12)NULL,

Emailvarchar(40)NULL,

Memoryvarchar(50)NULL)GO創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)3.2.3

創(chuàng)建帶完整性約束的表任務(wù)3.2例3-4：建立一個工人信息表，包括編號、姓名、職務(wù)。要求主鍵為編號姓名具有唯一性，用constraint定義約束。createtable職工信息表(編號char(10),姓名char(8),職務(wù)char(12),constraintpk_職工信息表_編號primarykey(編號),constraintuq_職工信息表_姓名unique(姓名)）創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)3.2.3

創(chuàng)建帶完整性約束的表任務(wù)3.2例3-5：建立一個職員基本信息表baseinfo，包括編號no、姓名name、性別sex、電話tele、Email要求：性別只接受男和女，默認(rèn)為男，電話必須是8位數(shù)字，Email中必須含有@符號。createtablebaseinfo(nochar(10),namechar(8),sexchar(2)default'男',constraintchk_sexcheck(sex='男'orsex='女'),telechar(8),constraintCK_telecheck(telelike'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),Emailvarchar(30),constraintck_Emailcheck(Emaillike'%@%'))創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)3.2.3

創(chuàng)建帶完整性約束的表任務(wù)3.2例3-6外鍵的使用，用SQL命令為數(shù)據(jù)庫scoresys建立表Score，如表。其中SID字段是主鍵，也是外鍵約束，來源于Student表的SID字段，CourseID字段是主鍵，也是外鍵約束，來源于Course表的CourseID字段。USEscoresysGOCREATETABLEScore(SIDchar(12)foreignkeyreferencesstudent(SID),CourseIDchar(10)foreignkeyreferencescourse(CourseID),ExamTimedatetimeNOTNULL,Markdecimal(4,1)NULL,ExamPlacevarchar(20)NULL,Memoryvarchar(20)NULL,PRIMARYKEY(SID,CourseID,ExamTime))GO修改數(shù)據(jù)表結(jié)構(gòu)3.3.1

用SSMS修改表結(jié)構(gòu)任務(wù)3.3右擊表名，在彈出的快捷菜單中，執(zhí)行【設(shè)計】即可打開表結(jié)構(gòu)，與建立表時的顯示內(nèi)容相同，可以直接修改。修改完成后，單擊【保存】按鈕或者關(guān)閉標(biāo)簽頁面使修改生效。如果表中已經(jīng)輸入了記錄，則修改結(jié)構(gòu)時，可能會造成數(shù)據(jù)的損壞或者丟失，需要謹(jǐn)慎操作。比如某列的數(shù)據(jù)類型發(fā)生改變，則這列的數(shù)據(jù)可能會丟失，如果某列的長度由長變短，則原來記錄中超出此列現(xiàn)有長度的數(shù)據(jù)將會被自動截掉，且不可恢復(fù)。修改數(shù)據(jù)表結(jié)構(gòu)3.3.２

用命令修改表結(jié)構(gòu)任務(wù)3.3修改表結(jié)構(gòu)的命令是ALTERTABLE。ALTERTABLE表名{ALTERCOLUMN列名類型[列約束]ADD列名類型[列約束]ADDCONSTRAINT約束名約束內(nèi)容DROPCOLUMN列名[,…n]}例3-7假設(shè)已有數(shù)據(jù)庫factory，