数据库-外键+视图+存储过程+触发器

外键

定义:如果某一实体的某个字段指向另一个主体的主键,就称为外键.

• 被指向的实体称为主实体(父实体)
• 负责指向的实体,称为从实体(子实体)

==只有InnoDB类型的表才可以使用外键==，mysql默认是MyISAM，这种类型不支持外键约束 外键的好处:

可以使得两张表关联，保证数据的一致性和实现一些级联操作

外键的作用:

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！

• 指定外键关键字： foreign key(列名)
• 引用外键关键字： references <外键表名>(外键列名)

创建外键的前提

• 两个表必须是innodb表类型
• 使用在外键关系的域必须为索引型
• 使用在外键关系的域必须与数据类型相似

事件触发限制

on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action

创建表时指定外键
create table user_info(
    id int not null auto_increment PRIMARY KEY,
    cid int unsigned not null,
    INDEX index_cid(cid),
    FOREIGN KEY(cid) references user(id) ON DELETE CASCADE ON UPDATE CASCADE
)engine=innodb;


添加外键
alter table 从表名 add foreign key(cid) references user (id) on delete set null;
# 在删除外键时，将从表的外键值设置为null 



删除外键
alter table 从表名 drop foreign key 外键名称;

视图

为什么使用视图

1.安全性。一般是这样做的:创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定。这样的方式是使用到了一个特性：grant语句可以针对视图进行授予权限。 2.查询性能提高。 3.有灵活性的功能需求后，需要改动表的结构而导致工作量比较大。那么可以使用虚拟表的形式达到少修改的效果。 4.复杂的查询需求。可以进行问题分解，然后将创建多个视图获取数据。将视图联合起来就能得到需要的结果了。

视图的工作机制

当调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。

create view 视图名(id,name,age) as
select
cid,cname.cage
from
cangle;
-------------
CREATE VIEW view_stu1 (sid,sname,class_name) AS
SELECT
s.id,s.`name`,c.class_name
FROM
student as s,class AS c
WHERE
s.id=c.id

删除视图

drop view view_stu1;

存储过程

修改MySQL定界符

delimiter 定界符[后面无 ; ] 存储过程是==主动调用==的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

存储过程的优点：

1. 增强SQL语句的功能和灵活性
2. 实现较快的执行速度
3. 减少网络流量

创建

create procedure pr_name(a int) begin //代码块 end

变量

1．定义变量 DECLARE my_sql INT DEFAULT 10; 2．为变量赋值 SET my_sql=30;

create procedure pr_add
(
   a int,
   b int
)
begin
   declare c int;
   if a is null then
      set a = 0;
   end if;
   if b is null then
      set b = 0;
   end if;
   set c = a + b;
   select c as sum;
end;

调用：

set @a = 10;
set @b = 20;
call pr_add(@a, @b);

控制语句

IF语句：

CREATE PROCEDURE proc_if( type int)
BEGIN
    DECLARE c varchar(500);
    IF type = 0 THEN
        set c = 'param is 0';
    ELSEIF type = 1 THEN
        set c = 'param is 1';
    ELSE
        set c = 'param is others, not 0 or 1';
    END IF;
    select c;
END

CASE语句：

CREATE PROCEDURE proc_case( type int)
BEGIN
    DECLARE c varchar(500);
    CASE type                       
    WHEN 0 THEN                     
        set c = 'param is 0';           
    WHEN 1 THEN                 
        set c = 'param is 1';
    ELSE
        set c = 'param is others, not 0 or 1';
    END CASE;
    select c;
END

循环while语句：

CREATE PROCEDURE proc_while ( n int)
BEGIN
    DECLARE i int;
    DECLARE s int;
    SET i = 0;
    SET s = 0;
    WHILE i <= n DO
        set s = s + i;
        set i = i + 1;
    END WHILE;
    SELECT s;
END

存储过程弊端

不同数据库语法差别很大 ,移植困难，换了数据库，需要重新编写； 不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；

删除存储过程

drop procedure 存储过程名

触发器

CREATE TRIGGER <触发器名称> --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. BEFORE | AFTER --触发器有执行的时间设置：可以设置为事件发生前或后。 INSERT | UPDATE | DELETE --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。 ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。 FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。 触发器SQL语句 --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。

查看触发器

show triggers;

创建触发器

CREATE TRIGGER t_afterinsert_on_tab1
AFTER INSERT on tab1
FOR EACH ROW
BEGIN
#作用：增加tab1表记录后自动将记录增加到tab2表中
    insert into tab2(tab2_id) values(new.tab1_id);
END;
INSERT INTO tab1(tab1_id) values('0001');
SELECT *FROM tab1;
SELECT *FROM tab2;
DELETE from tab1 where tab1_id='0001';
DELETE from tab2 where tab2_id='0001';

#作用：增加tab1表记录后自动将记录增加到tab2表中
    #insert into tab2(tab2_id) values(new.tab1_id);
#作用: 删除tab1表记录后自动将tab2表中对应的记录删去
    #delete from tab2 where tab2_id=old.tab1_id;

删除触发器

drop trigger 触发器名;