新知识讲解

第一部分： 数据库完整性

一、参照完整性的定义与实现

案例：给student表定义一个外键，引用的是dept表的dno主键

create table student(
    sno int primary key,
    sname varchar(20) not null,
    sage int,
    sdept int,
    foteign key(sdept) references dept(dno)
);

• 删除外键

alter table tb_name drop foreign key 外键名;

• 修改表的时候定义外键

alter table student add foreign key(sdept) references dept(dno);

定义外键需要遵守的规则：

• 1、被参照的关系已经存在
• 2、被参照的关系必须定义主键
• 3、外键中的列的数据类型必须与被参照关系的主键的对应的列的数据类型相同
• 4、外键列的数目必须与被参照关系中的主键列的数目相同
• 5、主键的值不能为空，但是外键的值可以为空
• 6、必须在被参照关系表名后指定参照的列名或者列名组合

二、用户完整性的定义与实现

用户完整性约束是指非空约束、check约束、触发器

• 非空约束：not null
• check约束(没什么用，mysql已经忽略)： check(score >= 0 and score <=100)

举例: 注意check在SQL不生效，若要约束，需要在业务进行约束

create table score(
    sno int not null,
    cno int not null,
    score int check(score>=0 and score <= 100),
    primary key(sno)
);

插入数据:

insert into score value(1,1,101);

三、命名完整性约束

给完整性约束命名，在定义约束的时候给约束指定名字；

create table tb_name(,,..,constraint[symbol] foreign key(外键名) references tb_name(列名);

注意：定义的外键名不能加单引号

四、更新完整性约束

先删除，再定义同名的完整性约束

alter table tb_name drop primary key;
alter table tb_name drop foreign key  外键名;

alter table tb_name add primary key(列名);
alter table tb_name constraint [symbol] foreign key(列名) references tb_name(列名);

第二部分：触发器

一、触发器的定义

是用户定义在表上的一类基于事件驱动的数据库对象;是一种保证数据库完整性的方法。

当触发器定义好之后，无需用户调用，只需要在表上出现指定事件，就会自动调用该对象，即表的操作事件触发表上的触发器的执行。

二、创建触发器

• 1、基本语法

  create trigger trigger_name trigger_time tirgger_event on table_name for each row trigger statement
  

  • trigger_name 触发器名字
  • trigger_time 触发器执行的时间
  • tirgger_event 触发
  • for each row 每一行数据改变的时候都要激活触发器
  • trigger statement 触发器的程序体，可以是一条SQL语句，如果是多条语句放在begin...end中

• 2、案例：在数据库mysql_test中的customer表中创建一个触发器，用于每次向customer表中插入一行数据的时候，将用户变量str值加1

  create trigger tri_customer_insert after insert on mysql_test.customer for each row set @str=@str+1;
  

  查看@str值select @str;

• 3、触发器创建的注意事项：

  • 触发器在创建的时候，每个表的每个时间只能创建一个触发器，所以每个表最多可以创建6个触发器
    • before insert
    • after insert
    • before update
    • after update
    • before delete
    • after delete
  • 创建触发器时，单个触发器不能同时与多个表和多个时间关联

三、删除触发器

基本语法:

drop trigger [if exists] [schema_name] tri_name;

举例:

drop trigger tri_customer_insert;
-- 删除后查看所以触发器
show triggers;

四、使用触发器

• 1、insert 触发器 创建一个触发器，往表中加入一条数据记录的同时往另一张表中也添加一条记录，如customer表和customer_copy;

  create trigger tri_customer_insert
  after insert on customer
  for each row
  insert into customer_copy
  set cust_id = new.cust_id,
      cust_name = new.cust_name,
      cust_sex = new.cust_sex,
      cust_addr = new.cust_addr;
  

  创建触发器后，再往customer插入一条数据，此时customer_copy也会插入相同的数据

  insert into customer values (0, '触发器2', 'F', '火星');
  

  • 使用的注意事项:
    • （1）在触发器代码内，可以使用一个名为new的虚拟表，访问被插入行的数据记录；
    • （2）在before insert 触发器中，new的值也可以被更新，即：运行更改被插入的值；
    • （3）auto_increment列，new 在insert 之前是0，在insert之后包含的值是自动生成的值。

• 2、update 触发器 修改一条客户的记录,设置该用户的用户名为“小仙女”。

  create trigger tri_cust_update 
  before update on customer 
  for each row 
  set new.cust_name = '小仙女';
  

  创建触发器之后更新

  update customer set cust_name = 'hi' where cust_id = 5;
  

  • update触发器使用的注意事项:
    • （1）update触发器的代码内，可以使用一个叫new的表记录更新的值，old表记录以前的值
    • （2）在before update 触发器中，new的值也可以被更新，允许更改将要用于update的值
    • （3）old虚拟表只能读，不能写。

• 3、delete 触发器 创建一个触发器，在删除customer表中记录的时候，删除另一个表中相同的记录

  create trigger tri_customer_delete
  after delete on customer
  for each row delete
  from customer_copy where cust_id = old.cust_id;
  

  创建触发器之后删除

  delete from customer where  cust_id = 9;
  

  • update触发器使用的注意事项:
    • （1）delete触发器的代码内，可以使用一个叫old的表来访问删除的行的信息；
    • （2）old表的值只能读不能写。