二. 数据库完整性

数据库的完整性是指数据库数据的正确性和相容性。正确性是指数据库数据符合现实世界语义且反映当前的实际状况。相容性是指数据库同一对象在不同关系中的数据是相同的，一致的。例如：学生的学号必须唯一，学生的性别只能是男或女，百分制的课程成绩取值范围是$0\sim100$，学生所选的课程必须是学校开设的课程，学生所在的院系必须是学校已经成立的院系等。

1. 实体完整性

关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。对于单个属性的约束说明有两种：表级约束和列级约束。

表级约束：在所有属性定义完后定义。列级约束：在每列数据类型之后定义。

用PRIMARY KEY定义了关系的主码后，每次进行插入或更新操作数据库管理系统将按照实体完整性规则自动进行检查。检查包括：

• 检查主码值是否唯一，如果不唯一则拒绝插入或修改
• 检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

检查主码值是否唯一的一种方法是进行全表扫描，这样十分耗时。为了提高效率，数据库一般都会在主码上自动建立一个索引，如下图所示的$B^+$树索引，通过索引对主码进行标记，这样查找效率大大提高。例如：新纪录主码值为$25$，数据库只需要搜索标记蓝色的部分，发现标记中没有新主码值$25$，便可以插入。

对于一个表中有多个主键，则只能定义在表级约束上；如果只有一个主键，则可以定义在表级约束上也可以在列级约束上。

#学生选课表
CREATE TABLE SC
(Sno CHAR(8),
 Cno CHAR(5),
 Grade SMALLINT,
 Semester CHAR(5),
 Teachingclass CHAR(8),
 PRIMARY KEY (Sno,Cno) #多个主键要定义在表级约束上
);

2. 参照完整性

关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些为外码，用REFERENCES短语指明外码参照哪些表的主键。参照完整性只能定义在表级约束条件上。外键可以取空值，不为空的话必须是已经存在的某个主键的值。

参照完整性语法格式如下：

FOREIGN KEY(外键名) REFERENCE 参照表(参照列)

定义学生选课表

#学生选课表
CREATE TABLE SC
(Sno CHAR(8),
 Cno CHAR(5),
 Grade SMALLINT,
 Semester CHAR(5),
 Teachingclass CHAR(8),
 PRIMARY KEY (Sno,Cno),
 FOREIGN KEY (Sno) REFERENCES Student(Sno),
 FOREIGN KEY (Cno)REFERENCES Course(Cno)
);

参照完整性约束将两个表中的相应元组对应联系起来。因此，对被参照表进行更新(增、删、改)操作时有可能会破坏参照完整性，必须进行检查以保证这两个表的相容性。

可能破坏参照完整性的情况及违约处理如下：

注意：MySQL中违约处理只有：拒绝和级联两种。

拒绝执行：不允许进行该操作，该策略一般设置为默认策略。

级联操作：当删除或修改被参照表(student表)的一个元组，导致参照表(sc表)不一致时，删除或修改参照表中所有导致不一致的元组。

3. MySQL的级联操作

在定义参照完整性时，可以通过以下语句进行关联多张表：

ON DELETE CASCADE  #级联删除
ON UPDATE CASCADE  #级联更新

完整的语法是：

#学生选课表
CREATE TABLE SC
(Sno CHAR(8),
 Cno CHAR(5),
 Grade SMALLINT,
 Semester CHAR(5),
 Teachingclass CHAR(8),
 PRIMARY KEY (Sno,Cno),
 FOREIGN KEY (Sno) REFERENCES Student(Sno)
 ON DELETE CASCADE 
 ON UPDATE CASCADE, #当student的sno与sc的sno不一致时,级联删除或更新sc表
 FOREIGN KEY (Cno)REFERENCES Course(Cno)
 ON DELETE CASCADE 
 ON UPDATE CASCADE  #当student的cno与sc的cno不一致时,级联删除或更新sc表
);

如果建表之后添加级联如下：

alter table sc add foreign key(sno) references student(sno) ON DELETE CASCADE

删除级联

SHOW CREATE TABLE purchase #先查看创表语句查询指定外键的默认名
ALTER TABLE purchase DROP FOREIGN KEY purchase_ibfk_4 #这里删除的是外键buyer_id,默认名是purchase_ibfk_4

4. 用户定义完整性

在CREATE TABLE中定义属性的同时，可以根据应用要求定义属性上的约束，即属性值限制，包括：

1. 列值非空(NOT NULL)
2. 列值唯一(UNIQUE)
3. 检查列值是否满足一个条件表达式(CHECK短语)

CHECK短语限定了一个列属性的范围

create table stu5(
  sno char(8)primary key,
  sname char(20) unique not null,
  ssex char(2) check (ssex in ('男','女'))
);

限定ssex的取值只能为男或女。

也可以定义在元组上：当学生的性别是男时，不能以Ms.开头

create table stu5(
  sno char(8)primary key,
  sname char(20) unique not null,
  ssex char(2),
  check (ssex='女' OR sname NOT LIKE 'Ms.%')
);

5. 完整性约束命名子句

以上的完整性约束都在CREATE TABLE中定义的，MySQL还可以在CREATE TABLE语句中提供完整性约束命名子句CONSTRAINT，用来对完整性约束命名，从而可以灵活地增加，删除一个完整性约束。

命令格式如下：

CONSTRAINT <完整性约束名> <完整性约束>

其中<完整性约束>包括：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK短语等。

• 列级上创建(8.0可以正常使用)

  create table s1
      (sno char(8) constraint c1 check(sno between '00000000' and '299999999'),
      ssex char(8) constraint c2 check(ssex in('男','女'))
      );
  

• 表级约束条件

  create table s2
      (sno char(8),
      ssex char(2),
      constraint c3 check(sno between '00000000' and '299999999'),
      constraint c4 check(ssex in('男','女'))
      );
  

• 修改基本表方式添加

  alter table 表名 add constraint 约束名 约束条件;
  alter table student add constraint c5 check(ssex in('男','女'));
  

• 删除完整性约束子句

  alter table 表名 drop constraint 约束名;
  alter table student drop constraint c5;
  

• 修改完整性约束子句

  先删除，后新建。

6. 触发器

触发器一经定义就被保存在数据库服务器中，任何用户对表的更新操作均由服务器自动激活相应的触发器。数据库关系系统核心层进行集中的完整性控制。

触发器语法如下：

CREATE TRIGGER 触发器名称 触发时间 触发事件 ON 表名 FOR EACH ROW
BEGIN
    语句主体;
END;

触发器名称：触发器名可以是模式名，同一模式下的触发器必须是唯一的，并且触发器名和表名必须在同一模式下。

触发时间：AFTER/BEFORE是触发时机。AFTER表示在触发事件的执行操作之后激活触发器。BEFORE则表示在触发事件的操作执行之前激活触发器。

触发事件：触发事件可以是INSERT、DELETE或UPDATE，也可以是几个事件的组合。如INSERT or DELETE、INSERT and DELETE等，还可以是UPDATE<触发列,...>，即进一步指明修改哪些列时激活触发器。

表名：触发器只能定义在基本表上，不能用在视图上。当基本表的数据发生变化时，将激活定义在该表上相应的触发事件的触发器。

表名后面是触发器类型：即行级触发器FOR EACH ROW和语句级触发器FOR EACH STATEMENT。行级触发器：即执行UPDATE后影响多少行就触发多少次。语句级触发器：执行UPDATE后触发的动作体将执行一次。

注意如果触发行动体执行失败，激活触发器的事件(即对数据库的更新操作)就会终止执行，触发器的目标或触发器可能影响的其他对象不发生任何改变。

触发动作体时，过程体会产生NEW和OLD两种引用，分别代表表更新前的值和表更新后的值。更详细情况如下：

创建触发器t1，要求删除sc表中的某个学号时，对应的将student表中的学号也删除

create trigger t1 after delete on sc for each row
     delete from student where sno=old.sno;
#运行以下语句可以触发触发器
delete from sc where sno='20180005';

创建触发器t2，当对sc表中的成绩进行更新时，如果更新后的成绩比原来增加了$10\%$，就将结果写入sc1表中：

#创建sc1表
create table sc1(sno char(8),cno char(6),oldgrade int,newgrade int);
#创建触发器
create trigger t2 after update on sc for each row
    begin
        if new.grade>=1.1*old.grade
           then insert into sc1 values(new.sno,new.cno,old.grade,new.grade);
        end if;
    end;
#运行以下语句可以触发触发器
update sc set grade=90 where grade=80;

创建触发器t3，将每次对student表的插入操作所增加后的学生个数记录到表stu1(number int)中

#创建stu1表
create table stu1(number int);   
#创建触发器
create trigger t3 after insert on student for each row
  insert into stu1 select count(sno) from student;
#运行以下语句可以触发触发器
insert into student(sno) values('20180009');

创建before行级触发器t4，要求如果教授的工资低于4000，则把它改为4000

#创建teacher表
create table teacher
    (eno char(8),ename char(6),job char(6),sal numeric(7,2));
#创建触发器
create trigger t4 before update on teacher for each row
    begin 
      if new.job='教授' and new.sal<4000 then set new.sal=4000;
      end if;
    end;
#运行以下语句可以触发触发器
insert into teacher values('0045636','987','教授','2999');

删除触发器语句：

DROP TRIGGER 触发器名
DROP TRIGGER t4

三. 授权与收权

• 创建用户

  create user 用户名 identified(身份,凭证) by 密码;
  create user u1@'localhost' identified by 'root';
  

  查看用户名

  select user from mysql.user;
  

• 授权

  grant 权限 on 对象类型 表名[列名] to 用户[with grant option];
  #给u1用户授权student表的查询和插入操作
  grant select on table student to u1@'localhost';
  grant insert on table student to u1@'localhost';
  

  查看用户的权限

  show grants for u1@'localhost';
  

  将用户u2对学生表的查询权限给用户u3

  create user u2@'localhost' identified by 'root';
  create user u3@'localhost' identified by 'root';
  #授权
  grant select on student to u2@'localhost' with grant option;
  

  WITH GRANT OPTION这个选项表示该用户可以将自己拥有的权限授权给别人。

  切换到用户u2

  #将u2查询权限给u3
  grant select on student to u3@'localhost'
  

• 收回权限

  revoke 权限 on 对象类型 表名 from 用户;
  revoke select on table student from u3@'localhost';
  

• 数据库角色

  create role 角色名;
  grant 权限 on 表名 to 角色名;
  revoke 权限 on 表名 from 角色名;