第11讲数据完整性

1、2021-11-251回顾回顾索引索引索引的作用是索引的作用是提高查询速度提高查询速度，但索引，但索引会降低更新速度会降低更新速度。聚集索引与非聚集索引，唯一索引与非唯一索引，单列聚集索引与非聚集索引，唯一索引与非唯一索引，单列索引与复合索引，主键索引。索引与复合索引，主键索引。视图视图视图是一个虚表，只存储视图是一个虚表，只存储查询定义查询定义，不存储数据。，不存储数据。在视图上可以进行增、删、改、查。增删改等操作有一在视图上可以进行增、删、改、查。增删改等操作有一定限制。定限制。数据库原理与应用数据库原理与应用第第11讲讲 数据完整性数据完整性2021-11-253提纲提纲重点重点分析数据

2、完整性要求分析数据完整性要求使用约束来强制完整性使用约束来强制完整性难点：难点：外键约束与检查约束外键约束与检查约束1.数据完整性概述数据完整性概述关键词：实体完整、参照完整、自定义完整关键词：实体完整、参照完整、自定义完整.2021-11-255n 数据完整性有数据完整性有3种类型种类型 实体完整性实体完整性(Entity Integrity)、 参照完整性参照完整性(Referential Integrity)、 用户定义的完整性用户定义的完整性(User-defined Integrity)。2021-11-256n 在在SQL Server中保障数据完整性的办法中保障数据完整性的办法规

3、则规则(Rule)默认默认(Default)-略略约束约束(Constraint)-重点学习重点学习触发器触发器(Trigger) -单独的章节介绍单独的章节介绍2021-11-257n规则规则(Rule)p规则规则(Rule)只能通过只能通过T_sql语句建，不能使用管理平台工具建。语句建，不能使用管理平台工具建。规则是单独存储的独立的数据库对象，可以多次被绑定。规则是单独存储的独立的数据库对象，可以多次被绑定。p语法如下语法如下：1.CREATE RULE语句用于在当前数据库中创建规则，其语法格式如语句用于在当前数据库中创建规则，其语法格式如下：下：CREATE RULE rule_nam

4、e AS condition_expression2.使用使用sp_helptext 系统存储过程可以查看规则的文本信息。系统存储过程可以查看规则的文本信息。sp_helptext （规则（规则/黙认值黙认值/触发器触发器/未加密的存储过程或视图）未加密的存储过程或视图）3.使用使用sp_bindrule绑定规则绑定规则sp_bindrule 规则名规则名,表名表名.字段名字段名4.使用使用sp_unbindrule解除规则解除规则sp_unbindrule employee.hire_date5.删除规则删除规则。DROP RULE hire_date_rule2021-11-258规则在管

5、理台查看。规则在管理台查看。2021-11-259n 约束约束(Constraint)p约束定义了一列数据或一行数据表的的限制条件。使约束定义了一列数据或一行数据表的的限制条件。使用约束用约束优先于优先于使用使用触发器、规则和默认值触发器、规则和默认值。p约束在表定义中声明。约束在表定义中声明。n SQL Server中有中有6种约束：种约束：p主键约束主键约束 p外键约束外键约束p惟一性约束惟一性约束p检查约束检查约束p默认约束默认约束p非空值约束非空值约束2021-11-2510工号工号商品号商品号数量数量销售日期销售日期工号工号姓名姓名性别性别身份证号身份证号 生日生日入职日期入职日期移

6、动电话移动电话商品号商品号商品名商品名单位单位单价单价员工表员工表销售表销售表商品表商品表数据完整性要求：数据完整性要求：（1）实体完整，不能有重复行，用）实体完整，不能有重复行，用主键约束主键约束，员工表中的身份证不能出现，员工表中的身份证不能出现同号，用同号，用唯一约束唯一约束。（2）参照完整，销售表中的工号取值必须来自员工表，商品号取值必须来）参照完整，销售表中的工号取值必须来自员工表，商品号取值必须来自商品表，用自商品表，用外键约束外键约束（3）姓名不能为空，用）姓名不能为空，用非空约束非空约束。（4）性别只能取）性别只能取男男或或女女，入职日期不能比生日大，入职日期不能比生日大18年

7、，移动电年，移动电话必须为话必须为“1？”，用，用检查约束检查约束。（5）当性别没有输入值时，默认为）当性别没有输入值时，默认为男男,当单位没有输入值时，默认为当单位没有输入值时，默认为个个用用默认约束默认约束。n分析实例分析实例2021-11-2511案例表案例表2. 通过创建约束来强制完整性通过创建约束来强制完整性关键词：可以在每列上定义（称为列约束关键词：可以在每列上定义（称为列约束)，也可以在所有的列之后定义也可以在所有的列之后定义(称为表约束称为表约束).2021-11-2513说明：说明：1. 创建的所有约束都有约束名、约束类型和约束对象。创建的所有约束都有约束名、约束类型和约束对

8、象。2. 约束名可以显式指出，如约束名可以显式指出，如“CONSTRAINT 约束名约束名”，如果没有显式指出，系统会自动生成一个约束名。如果没有显式指出，系统会自动生成一个约束名。3. 如果一个约束只涉及一个列，可以在如果一个约束只涉及一个列，可以在列的定义中创建列的定义中创建该约束该约束。一个约束涉及一个或多个列，则。一个约束涉及一个或多个列，则约束都可以约束都可以在所有列之后创建。在所有列之后创建。案例案例2021-11-25148.4.2 8.4.2 外键约束外键约束例：关系图例：关系图课堂小练课堂小练如下图建表，表名：如下图建表，表名：student，注意表中约束：，注意表中约束：1

9、、主键约束、主键约束2、姓名不为空、姓名不为空3、性别、性别 默认为默认为男男4、身份证号唯一，长度为、身份证号唯一，长度为185、手机约束：、手机约束：11位，如：位，如：10-90-90-90-90-90-90-90-90-90-92021-11-25153. 综合练综合练-课后体会课后体会2021-11-2517如下表如下表学号学号课程号课程号成绩成绩学号学号 姓名姓名性别性别身份证号身份证号 生日生日手机号手机号课程号课程号课程名课程名学分学分学生表学生表课程表课程表成绩表成绩表2021-11-2518-创建数据库创建数据库Create Database TEACHON PRIMARY

10、 (NAME=Teach_dat, FILENAME=D:YZTEACH.mdf)LOG ON(NAME=Teach_log, FILENAME=D:YZTEACH.ldf)gouse teachgo2021-11-2519-创建数据表创建数据表CREATE TABLE 学生表学生表(学号学号 char(10) Primary Key, 姓名姓名 nchar(4) NOT NULL, 性别性别 nchar(1) NOT NULL DEFAULT 男男,生日生日 DateTime, 身份证号身份证号 char(18) UNIQUE,手机手机 char(11),CHECK (性别性别=男男 or

11、性别性别=女女),CHECK (手机手机 Like 10-90-90-90-90-90-90-90-90-90-9)go2021-11-2520CREATE TABLE 课程表课程表(课程号课程号 char(6) PRIMARY KEY NONCLUSTERED, 课程名课程名 nchar(10) NOT NULL UNIQUE, 学分学分 int NOT NULL)goCREATE TABLE 成绩表成绩表(学号学号 char(10) NOT NULL FOREIGN KEY REFERENCES 学生表学生表(学号学号), 课程号课程号 char(6) NOT NULL FOREIGN K

12、EY REFERENCES 课程表课程表(课程号课程号), 成绩成绩 int DEFAULT(0),PRIMARY KEY NONCLUSTERED (学号学号,课程号课程号)go2021-11-2521-添加数据添加数据INSERT INTO 学生表学生表(学号学号,姓名姓名,性别性别,生日生日,身份证号身份证号,手机手机)VALUES (1000000001,一一,男男, 1981-1-113926190001)INSERT INTO 学生表学生表(学号学号,姓名姓名,性别性别,生日生日,身份证号身份证号,手机手机)VALUES (100000000

13、2,二二,男男, 1981-1-113926190002)INSERT INTO 学生表学生表(学号学号,姓名姓名,性别性别,生日生日,身份证号身份证号,手机手机)VALUES (1000000003,三三, 男男,1981-1-113926190003)goINSERT INTO 课程表课程表(课程号课程号,课程名课程名,学分学分) VALUES(NN0001,计算机网络计算机网络,4)INSERT INTO 课程表课程表(课程号课程号,课程名课程名,学分学分) VALUES(NN0002,数据库原理与应用数据库

14、原理与应用,4)INSERT INTO 课程表课程表(课程号课程号,课程名课程名,学分学分) VALUES(NN0003,计算机高级语言计算机高级语言,4)goINSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (1000000001,NN0001, 65)INSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (1000000002,NN0001,74 )INSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (1000000003,NN0001, 53)INSERT INTO 成绩表成绩表(

15、学号学号,课程号课程号,成绩成绩) VALUES (1000000001,NN0002, 77)INSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (1000000002,NN0002, 82)INSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (1000000003,NN0002,75 )INSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (1000000001,NN0003,81 )INSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (100000

16、0002,NN0003, 91)INSERT INTO 成绩表成绩表(学号学号,课程号课程号,成绩成绩) VALUES (1000000003,NN0003,66 )go2021-11-2522-创建视图创建视图CREATE VIEW 课程平均成绩视图课程平均成绩视图 asSELECT 课程名课程名,AVG(b.成绩成绩) as 平均成绩平均成绩FROM 课程表课程表 a JOIN 成绩表成绩表 b on a.课程号课程号=b.课程号课程号GROUP BY a.课程名课程名goCREATE VIEW 学生修读学分视图学生修读学分视图 asSELECT a.姓名姓名,SUM(c.学分学分) as

17、 修读学分修读学分, sum(case when b.成成绩绩60 then c.学分学分 else 0 end) as 已获学分已获学分 FROM 学生表学生表 a FULL JOIN 成绩表成绩表 b on a.学号学号=b.学号学号 FULL JOIN 课程表课程表 c on b.课程号课程号=c.课程号课程号GROUP BY a.学号学号,a.姓名姓名go2021-11-2523CREATE VIEW 平均成绩前两名视图平均成绩前两名视图 asSELECT TOP 2 姓名姓名,AVG(成绩成绩) as 平均成绩平均成绩FROM 学生表学生表 a FULL JOIN 成绩表成绩表 b

18、on a.学号学号=b.学号学号GROUP BY a.姓名姓名goCREATE VIEW 平均成绩不及格的课程视图平均成绩不及格的课程视图 asSELECT a.课程号课程号,a.课程名课程名,AVG(b.成绩成绩) as 平均成绩平均成绩FROM 课程表课程表 a FULL JOIN 成绩表成绩表 b on a.课程号课程号=b.课程号课程号GROUP BY a.课程号课程号,a.课程名课程名HAVING AVG(b.成绩成绩) 60go2021-11-2524总结总结1. 数据完整性有数据完整性有3种类型。实体完整性、参照完整性和种类型。实体完整性、参照完整性和用户定义的完整性。用户定义的完整性。2. 约束是约束是SQL Server提供的自动保持数据库完整性的提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的一种方法，定义了可输入表或表的单个列中的数据的限制条件。限制条件。3. 在在SQL Server中有中有6种约束：非空值约束、主键约束、种约束：非空值约束、主键约束、外键约束、唯一性约束、检查约束和默认约束。外键约束、唯一性约束、检查约束和默认约束。 小课内容小课内容2021-11-25261. 在管理台实现约束在管理台实现约束在大课的综合案例的基础上，学习用管理平台和在