上机3-完整性约束2及数据更新

上机3:完整性约束2与数据更新实例5-16(按实例要求补充列级check语句)/*示例1：定义数值列的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的值应该小于系统当前日期（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约束，因为这时所有的列都已经定义。*/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在创建表时使用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（续）通过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插入数据实例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插入数据实例5-24使用UPDATE更新所有的行或修改部分满足条件的行。一条UPDATE语句中可以同时修改多个列，但SET子句只有一个，列与列之间用逗号隔开。

本实例第一条UPDATE语句根据出生日期（BirthDate）计算每个员工的年龄（Age），同时将每个员工的薪资（Salary）设置为$25000。5.4.2修改数据实例5-24（使用UPDATE更新所有的行）IF(OBJECT_ID('myEmployees')ISNOTNULL)DROPTABLEmyEmployeesGOCREATETABLEmyEmployees(EmpIDIntIDENTITY(1,1)PRIMARYKEY,Namenvarchar(30),BirthDateDatetime,AgeInt,HireDateDatetime,SalaryIntDEFAULT(0))GO5.4.2修改数据实例5-24（续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修改数据实例5-24（使用UPDATE更新部分满足条件的行。）/*50岁及以上的员工额外再增加$1000薪资。*/

5.4.2修改数据实例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删除数据