sql_数据库__第六章_SQL_Server_2008数据表管理2

SQLServer数据库程序设计授课教师：姜姗数据库中的数据现实世界的反映，数据库的设计必须能够满足现实情况的实现，即满足现实商业规则的要求，这也就是数据完整性的要求。在数据库管理系统中，约束是保证数据库中的数据完整性的重要方法。6.3完整性与约束2．FOREIGNKEY约束外键（FOREIGNKEY）用于建立和加强两个表（主表与从表）的一列或多列数据之间的链接，当数据添加、修改或删除时，通过外键约束保证两个表之间数据的一致性。定义表之间的参照完整性是先定义主表的主键，再对从表定义外键约束。FOREIGNKEY约束要求列中的每个值在所引用的表中对应的被引用列中都存在，同时FOREIGNKEY约束只能引用在所引用的表中是PRIMARYKEY或UNIQUE约束的列，或所引用的表中在UNIQUEINDEX内的被引用列。使用表设计器创建FOREIGNKEY约束打开需要建立外键的表设计器，在需要设置外键的列上单击鼠标右键选择【关系】命令。打开【外键关系】对话框，单击表和列规范后面的…按钮，设置外键关系。使用数据库关系图建立外键使用T-SQL语句创建FOREIGNKEY约束创建外键约束的语法形式如下：[CONSTRAINTconstraint_name][FOREIGNKEY]REFERENCESreferenced_table_name(column_name)[([,…n])]参数说明如下：referenced_table_name是FOREIGNKEY约束引用的表的名称。column_name是FOREIGNKEY约束所引用的表中的某列。【例6】：建立一个新的stu_info表，指定“学号”为主键，“班级编号”为外键，与class表中的“班级编号”列关联。Createtablestu_info(学号char(10)NOTNULL,姓名char(8)NOTNULL,出生年月datetimeNULL,性别char(2)NOTNULL,地址char(20),班级编号char(4)ConstraintPK_学号primarykey（学号），ConstraintFK_班级编号foreignkey（班级编号）Referencesclass(班级编号)）也可写成Createtablestu_info(学号char(10)NOTNULLPRIMARYKEY,姓名char(8)NOTNULL,出生年月datetimeNULL,性别char(2)NOTNULL,地址char(20),班级编号char(4)referencesclass）注：此法只能用于列名相同时使用【例7】：在stu_info表中sclass列上添加一个外键，名称为FK_bno，与class表中的bno列关联。Altertablestu_infoAddconstraintFK_bnoForeignkey(sclass)referencesclass(bno)3．UNIQUE约束UNIQUE约束用于确保表中某个列或某些列（非主键列）没有相同的列值。与PRIMARYKEY约束类似，UNIQUE约束也强制唯一性，但UNIQUE约束用于非主键的一列或多列组合，而且一个表中可以定义多个UNIQUE约束，另外UNIQUE约束可以用于定义允许空值的列。例如在课程表（course_info）中已经定义“课程号”作为主键，而现在对于“课程名”也不允许出现重复，就可以通过设置“课程名”为UNIQUE约束来确保其唯一性。使用表设计器创建UNIQUE约束在表设计器需要加入UNIQUE约束的列上单击鼠标右键选择【索引/键】选项。使用T-SQL语句创建UNIQUE约束创建唯一性约束的语法形式如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]其中，CLUSTERED|NONCLUSTERED表示所创建的UNIQUE约束是聚集索引还是非聚集索引，默认为NONCLUSTERED非聚集索引。【例8】：将course表中cname列设置成为唯一列。AltertablecourseAddconstraintix_courseunique(cname)4．CHECK约束CHECK约束用于限制输入到一列或多列的值的范围，从逻辑表达式判断数据的有效性，也就是一个列的输入内容必须满足CHECK约束的条件，否则，数据无法正常输入，从而强制数据的域完整性。例如在学生成绩表（stu_grade）中的“成绩”来讲，应该保证在0~100之间，又如在课程信息表（course_info）中的“学时”来讲，应该保证在0~80之间，而只用int数据类型是无法实现的，可以通过CHECK约束来完成。使用表设计器创建CHECK约束使用T-SQL语句创建CHECK约束创建检查约束的语法形式如下：[CONSTRAINTconstraint_name]CHECK(check_expression)其中，check_expression为约束范围表达式。【例8】：新建“成绩”表，要求成绩的值在0~100之间Createtable成绩(编号char(10)NOTNULL,课程编号char（4）references课程表，学号char（10）references学生信息，姓名char（8）NOTNULL,成绩intConstraintPK_学号primarykey（编号），ConstraintCK_成绩CHECK(成绩between0and100)）成绩intCHECK(成绩between0and100)5．DEFAULT约束若将表中某列定义了DEFAULT约束后，用户在插入新的数据行时，如果没有为该列指定数据，那么系统将默认值赋给该列，当然该默认值也可以是空值（NULL）。例如，假设学生信息表（stu_info）中的同学绝大多数都来自于“信息学院”，就可以通过设置“系别”字段的DEFALUT约束来实现，简化用户的输入。使用表设计器创建DEFAULT约束在表设计器中，选择需要设置DEFAULT值的列，在下面“列属性”的“默认值或绑定”栏中输入默认值，然后单击工具栏中的“保存”按钮，即完成DEFAULT约束的创建。使用T-SQL语句创建DEFAULT约束创建默认值约束的语法形式如下：[CONSTRAINTconstraint_name]DEFAULTconstraint_expression[withVALUES]其中，constraint_expression为默认值。【例9】将表student的电话号码增加默认值‘0371-00000000’.altertablestudentadddefault'0371-00000000'for电话号码启用、禁用约束启用、禁用约束的语法格式如下：{CHECKINOCHECK}CONSTRAINT{ALL|constraint_name[,…n]}此语句只能与外键和CHECK约束一起使用，无法禁用DEFAULT、PRIMARY和UNIQUE约束。【例10】禁用表student中的CK_ssex约束。ALTERTABLEstudentNOCHECKCONSTRAINTCK_ssex若要禁用所有约束则NOCHECKCONSTRAINTALL【例11】启用表student中的CK_ssex约束。ALTERTABLEstudentCHECKCONSTRAINTCK_ssex删除约束删除约束的语法形式如下：DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}【例12】将表student的CK_ssex约束删除AltertablestudentdropCK_ssex创建表的目在于利用表进行数据的存储和管理。对数据进行管理的前提是数据的存储，向表中添加数据，没有数据的表是没有任何实际意义的；添加完成后，用户也可以根据自己的需要对表中数据进行修改和删除。在SQLServer2008中，对于数据的管理包括插入、修改和删除，通过ManagementStudio来操作，也可以利用SQL语句来实现。6.5管理数据表1．利用对象资源管理器插入表数据（1）启动SQLServerManagementStudio。（2）展开SQLServer实例，选择“表”，单击鼠标右键，然后从弹出的快捷菜单中选择“编辑前200行”命令。（3）在表窗口中，显示出当前表中数据，单击表格中最后一行，填写相应数据信息。界面方式插入数据表插入一个元组基本格式INSERTINTO表名[(列名[,列名…])]VALUES(expression[,表达式……])参数说明：Table_name:表名Column_name:列名Expression:对应字段的值或表达式，字符和日期型需要加单引号。命令方式插入数据表若数据表结构为student(sno,sname,ssex,sage)插入一个完整的元组INSERTINTOStudentVALUES(‘200215121’,‘李勇’,‘男’,20)插入一个不完整的元组INSERTINTOStudent(Sno,Sname)VALUES(‘200215122’,‘刘晨')新插入记录应与表结构定义匹配；列名项数与提供值的数目应匹配；可以指定列值为Null；INTO子句中没有出现的属性列，新记录在这些列上将取空值（Null）或默认值；如果INTO子句中没有指明任何列名，则新插人的记录必须在每个属性列上均有值，且顺序应与表中属性列顺序一致。插入数据表注意事项向Student表中插入一条记录（’200501‘，’李勇‘）练习1．利用对象资源管理器修改表数据利用对象资源管理器修改表数据，与插入表数据操作类似。修改数据表基本格式Updatetable_nameSet列名1=表达式[,列名2=表达式]…[[FROM表名]where逻辑表达式]说明：–一次可以更新多个属性的值；–更新的条件可以与其他的表相关（使用FROM指定）；–如果省略where语句，则表示要修改表中的所有记录。命令方式修改数据表三种修改方式1.修改某一个元组的值2.修改多个元组的值3.带子查询的修改语句在修改之前建议先查看表记录（Select*fromtable）查看数据表Student所有记录SELECT*FROMStudent查看数据表Student中字段学号、姓名的所有记录SELECTSno，SnameFROMStudent给学号为200215122的学生年龄增加1岁UPDATEStudentSETSage=Sage+1WHERESno=’200215122‘给CS系的男同学年龄增加1岁UPDATEStudentSETSage=Sage+1WHERESdept=’CS‘andSsex=’男‘给所有课程的学分提高1分UPDATECourseSETCcredit=Ccredit+1将学号为200515121同学的姓名，性别，年龄分别设置为（张三，男，20）UPDATEStudentSET姓名=‘张三’，性别=‘男’，年龄=20WHERESno=‘200515121’给CS系的学生的选课成绩增加10分UPDATESCSETGrade=Grade+10WHERESno=(SELECTSnoFROMStudentWHEREStudent.Sdept=‘CS’)1.将数据表student中学号为200215121的记录的性别及所在系的字段值改为（女，IS）；2.将数据表SC中所有记录的成绩的字段值增加10％；3.将数据表Student中姓名中姓“王”的记录年龄增加1；4.将表SC中选修课程号为1的记录的成绩字段值增加10，其他记录的成绩字段值增加5；练习1．利用对象资源管理器删除表数据在需要删除的记录前点击鼠标右键选择【删除】删除数据库基本格式Deletefromtable_name[where逻