上機3-完整性約束2及數(shù)據(jù)更新

上機3:完整性約束2與數(shù)據(jù)更新實例5-16(按實例要求補充列級check語句)/*示例1：定義數(shù)值列的CHECK約束。一個CHECK約束可以有多個條件組成，如Discount列。*/IF(OBJECT_ID('myOrderItems')ISNOTNULL)DROPTABLEmyOrderItemsGOCREATETABLEmyOrderItems(OrderIDintNOTNULLREFERENCESOrders(OrderID),ProductIDintNOTNULLREFERENCESProducts(ProductID),UnitPricemoney,Quantitysmallint,Discountreal,CONSTRAINTPK_myOrderItemsPRIMARYKEY(OrderIDASC,ProductIDASC))5.3.4CHECK檢驗性約束實例5-16(按實例要求補充列級check語句)/*示例2：帶有字符驗證模式的CHECK約束。EmployeeID和Zip的值必須遵循一個給定的模式，而Birthday的值應該小于系統(tǒng)當前日期（GetDate()）。*/IF(OBJECT_ID('myEmployees')ISNOTNULL)DROPTABLEmyEmployeesGOCREATETABLEmyEmployees(EmployeeIDnchar(7)PRIMARYKEY,FirstNamenvarchar(15),LastNamenvarchar(20),BirthDatedatetime,Addressnvarchar(40),Citynvarchar(20),Statenchar(2),Zipnchar(6))5.3.4CHECK檢驗性約束實例5-17(按實例要求補充表級check語句)

/*在建表最后一列之后定義表級CHECK約束，因為這時所有的列都已經(jīng)定義。*/IF(OBJECT_ID('myEmployees')ISNOTNULL)

DROPTABLEmyEmployeesGOCREATETABLEmyEmployees(EmployeeIDnchar(7)PRIMARYKEY,FirstNamenvarchar(15)NOTNULL,LastNamenvarchar(20)NOTNULL,BirthDatedatetimeCONSTRAINTCK_myEmployees_birthdate

CHECK(BirthDate<GetDate()),HireDatedatetime)GO5.3.4CHECK檢驗性約束實例5-17/*通過ALTERTABLE語句增加表級CHECK約束。*/5.3.4CHECK檢驗性約束實例5-18在創(chuàng)建表時使用DEFAULT缺省約束。(按實例要求補充列級Default語句)

IF(OBJECT_ID('myEmployees')ISNOTNULL)DROPTABLEmyEmployeesGOCREATETABLEmyEmployees(EmployeeIDnchar(7)NOTNULLPRIMARYKEY,FirstNamenvarchar(15)NOTNULL,LastNamenvarchar(20)NOTNULL,Gendernvarchar(10)CHECK(GenderIN('Male','Female')),BirthDatedatetime,HireDatedatetime,Addressvarchar(40),Postalcodenchar(6))GO5.3.5DEFAULT缺省約束實例5-18（續(xù)）通過ALTERTABLE語句增加表級Default約束5.3.5DEFAULT缺省約束實例5-21(按實例要求插入完整的一行,表中每一列都賦值)IF(OBJECT_ID('myEmployees')ISNOTNULL)

DROPTABLEmyEmployees

GOCREATETABLEmyEmployees(EmployeeIDintIDENTITY(1,1),FirstNamenvarchar(10),Minitchar(1),LastNamenvarchar(20),BirthDatedatetime,AgeASyear(GetDate())-year(birthdate))GO5.4.1插入數(shù)據(jù)實例5-22（指定賦值列的列表，向表中插入一行，并使用默認值defaultvalues）IF(OBJECT_ID('myEmployees')ISNOTNULL)DROPTABLEmyEmployeesGOCREATETABLEmyEmployees(EmployeeIDintIDENTITY(1,1),FirstNamenvarchar(10),Minitchar(1)DEFAULT'O',LastNamenvarchar(20),BirthDatedatetime,AgeASYear(GetDate())-Year(BirthDate))GO5.4.1插入數(shù)據(jù)實例5-24使用UPDATE更新所有的行或修改部分滿足條件的行。一條UPDATE語句中可以同時修改多個列，但SET子句只有一個，列與列之間用逗號隔開。

本實例第一條UPDATE語句根據(jù)出生日期（BirthDate）計算每個員工的年齡（Age），同時將每個員工的薪資（Salary）設置為$25000。5.4.2修改數(shù)據(jù)實例5-24（使用UPDATE更新所有的行）IF(OBJECT_ID('myEmployees')ISNOTNULL)DROPTABLEmyEmployeesGOCREATETABLEmyEmployees(EmpIDIntIDENTITY(1,1)PRIMARYKEY,Namenvarchar(30),BirthDateDatetime,AgeInt,HireDateDatetime,SalaryIntDEFAULT(0))GO5.4.2修改數(shù)據(jù)實例5-24（續(xù)2）INSERTINTOmyEmployees(Name,BirthDate,HireDate)VALUES('Janet','1973-8-9','1999-4-1')INSERTINTOmyEmployees(Name,BirthDate,HireDate)VALUES('Andrew','1955-3-4','1993-9-17')INSERTINTOmyEmployees(Name,BirthDate,HireDate)VALUES('Robert','1960-5-29','1994-1-2')INSERTINTOmyEmployees(Name,BirthDate,HireDate)VALUES('Martin','1966-1-27','1994-11-15')/*計算所有記錄中的Age值，并設置每人的薪資為$25000。*/

5.4.2修改數(shù)據(jù)實例5-24（使用UPDATE更新部分滿足條件的行。）/*50歲及以上的員工額外再增加$1000薪資。*/

5.4.2修改數(shù)據(jù)實例5-25IF(OBJECT_ID('myEmployees')ISNOTNULL)DROPTABLEmyEmployees;GOCREATETABLEmyEmployees(EmpIDIntIDENTITY(1,1)PRIMARYKEY,Namenvarchar(30),BirthDateDatetime,HireDateDatetime)GOINSERTINTOmyEmployees(Name,BirthDate,HireDate)VALUES('Cramer','1973-8-30','1999-4-01')INSERTINTOmyEmployees(Name,BirthDate,HireDate)VALUES('Andrew','1965-3-4','1991-10-2')INSERTINTOmyEmployees(Name,BirthDate,HireDate)VALUES('Robert','1960-5-29','1994-1-2')5.4.3刪除數(shù)據(jù)