第讲数据完整性总结

2020/1/211回顾索引索引的作用是提高查询速度，但索引会降低更新速度。聚集索引与非聚集索引，唯一索引与非唯一索引，单列索引与复合索引，主键索引。视图视图是一个虚表，只存储查询定义，不存储数据。在视图上可以进行增、删、改、查。增删改等操作有一定限制。数据库原理与应用第11讲数据完整性2020/1/213提纲重点分析数据完整性要求使用约束来强制完整性难点：外键约束与检查约束1.数据完整性概述关键词：实体完整、参照完整、自定义完整.2020/1/215数据完整性有3种类型实体完整性(EntityIntegrity)、参照完整性(ReferentialIntegrity)、用户定义的完整性(User-definedIntegrity)。2020/1/216在SQLServer中保障数据完整性的办法规则(Rule)默认(Default)-------略约束(Constraint)-----重点学习触发器(Trigger)------单独的章节介绍2020/1/217规则(Rule)规则(Rule)只能通过T_sql语句建，不能使用管理平台工具建。规则是单独存储的独立的数据库对象，可以多次被绑定。语法如下：1.CREATERULE语句用于在当前数据库中创建规则，其语法格式如下：CREATERULErule_nameAScondition_expression2.使用sp_helptext系统存储过程可以查看规则的文本信息。sp_helptext（规则/黙认值/触发器/未加密的存储过程或视图）3.使用sp_bindrule绑定规则sp_bindrule规则名,‘表名.字段名‘4.使用sp_unbindrule解除规则sp_unbindrule'employee.hire_date'5.删除规则。DROPRULEhire_date_rule2020/1/218规则在管理台查看。2020/1/219约束(Constraint)约束定义了一列数据或一行数据表的的限制条件。使用约束优先于使用触发器、规则和默认值。约束在表定义中声明。SQLServer中有6种约束：主键约束外键约束惟一性约束检查约束默认约束非空值约束2020/1/2110工号商品号数量销售日期工号姓名性别身份证号生日入职日期移动电话商品号商品名单位单价员工表销售表商品表数据完整性要求：（1）实体完整，不能有重复行，用主键约束，员工表中的身份证不能出现同号，用唯一约束。（2）参照完整，销售表中的工号取值必须来自员工表，商品号取值必须来自商品表，用外键约束（3）姓名不能为空，用非空约束。（4）性别只能取‘男’或‘女’，入职日期不能比生日大18年，移动电话必须为“1？？？？？？？？？”，用检查约束。（5）当性别没有输入值时，默认为‘男’,当单位没有输入值时，默认为‘个’用默认约束。分析实例2020/1/2111案例表2.通过创建约束来强制完整性关键词：可以在每列上定义（称为列约束)，也可以在所有的列之后定义(称为表约束).2020/1/2113说明：1.创建的所有约束都有约束名、约束类型和约束对象。2.约束名可以显式指出，如“CONSTRAINT约束名”，如果没有显式指出，系统会自动生成一个约束名。3.如果一个约束只涉及一个列，可以在列的定义中创建该约束。一个约束涉及一个或多个列，则约束都可以在所有列之后创建。案例2020/1/21148.4.2外键约束例：关系图课堂小练如下图建表，表名：student，注意表中约束：1、主键约束2、姓名不为空3、性别默认为‘男’4、身份证号唯一，长度为185、手机约束：11位，如：'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘2020/1/21153.综合练---课后体会2020/1/2117如下表学号课程号成绩学号姓名性别身份证号生日手机号课程号课程名学分学生表课程表成绩表2020/1/2118--创建数据库CreateDatabaseTEACHONPRIMARY(NAME=Teach_dat,FILENAME='D:\YZ\TEACH.mdf')LOGON(NAME=Teach_log,FILENAME='D:\YZ\TEACH.ldf')gouseteachgo2020/1/2119--创建数据表CREATETABLE学生表(学号char(10)PrimaryKey,姓名nchar(4)NOTNULL,性别nchar(1)NOTNULLDEFAULT'男',生日DateTime,身份证号char(18)UNIQUE,手机char(11),CHECK(性别='男'or性别='女'),CHECK(手机Like'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))go2020/1/2120CREATETABLE课程表(课程号char(6)PRIMARYKEYNONCLUSTERED,课程名nchar(10)NOTNULLUNIQUE,学分intNOTNULL)goCREATETABLE成绩表(学号char(10)NOTNULLFOREIGNKEYREFERENCES学生表(学号),课程号char(6)NOTNULLFOREIGNKEYREFERENCES课程表(课程号),成绩intDEFAULT(0),PRIMARYKEYNONCLUSTERED(学号,课程号))go2020/1/2121--添加数据INSERTINTO学生表(学号,姓名,性别,生日,身份证号,手机)VALUES('1000000001','一','男','1981-1-1','422121198101010001','13926190001')INSERTINTO学生表(学号,姓名,性别,生日,身份证号,手机)VALUES('1000000002','二','男','1981-1-1','422121198101010002','13926190002')INSERTINTO学生表(学号,姓名,性别,生日,身份证号,手机)VALUES('1000000003','三','男','1981-1-1','422121198101010003','13926190003')goINSERTINTO课程表(课程号,课程名,学分)VALUES('NN0001','计算机网络',4)INSERTINTO课程表(课程号,课程名,学分)VALUES('NN0002','数据库原理与应用',4)INSERTINTO课程表(课程号,课程名,学分)VALUES('NN0003','计算机高级语言',4)goINSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000001','NN0001',65)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000002','NN0001',74)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000003','NN0001',53)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000001','NN0002',77)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000002','NN0002',82)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000003','NN0002',75)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000001','NN0003',81)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000002','NN0003',91)INSERTINTO成绩表(学号,课程号,成绩)VALUES('1000000003','NN0003',66)go2020/1/2122--创建视图CREATEVIEW课程平均成绩视图asSELECT课程名,AVG(b.成绩)as平均成绩FROM课程表aJOIN成绩表bona.课程号=b.课程号GROUPBYa.课程名goCREATEVIEW学生修读学分视图asSELECTa.姓名,SUM(c.学分)as修读学分,sum(casewhenb.成绩60thenc.学分else0end)as已获学分FROM学生表aFULLJOIN成绩表bona.学号=b.学号FULLJOIN课程表conb.课程号=c.课程号GROUPBYa.学号,a.姓名go2020/1/2123CREATEVIEW平均成绩前两名视图asSELECTTOP2姓名,AVG(成绩)as平均成绩FROM学生表aFULLJOIN成绩表bona.学号=b.学号GROUPBYa.姓名goCREATEVIEW平均成绩不及格的课程视图asSELECTa.课程号,a.课程名,AVG(b.成绩)as平均成绩FROM课程表aFULLJOIN成绩表bona.课程号=b.课程号GROUPBYa.课程号,a.课程名HAVINGAVG(b.成绩)60go2020/1/2124总结1.数据完整性有3种类型。实体完整性、参照完整性和用户定义的完整性。2.约束是SQLServer提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件。3.在SQLServer中有6种约束：非空值约束、主键约束、外键约束、唯一性约束、检查约束和默认约束。小课内容2020/1/21261.在管理台实现约束在大课的综合案例的基础上，学习用管理平台和SQL两种方法创建约束主键约束外键约束检查约束默认约束非空值约束惟一性约束2020/1/21272.《作业二》点拨2020/1/21283.实验准备40分钟（1）请先附加数据库：studentsdb（2）完成WOED中的实验。实验11：数据完整性.doc2020/1/21294.期中测试测试请课后预习，在小课上测试，请自带A4作业纸一张。