王明昌博客外键
定义:如果某一实体的某个字段指向另一个主体的主键,就称为外键.
- 被指向的实体称为主实体(父实体)
- 负责指向的实体,称为从实体(子实体)
==只有InnoDB类型的表才可以使用外键==，mysql默认是MyISAM，这种类型不支持外键约束
外键的好处:
|
1 2 |
可以使得两张表关联，保证数据的一致性和实现一些级联操作 |
外键的作用:
|
1 2 |
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！ |
- 指定外键关键字： foreign key(列名)
- 引用外键关键字： references <外键表名>(外键列名)
创建外键的前提
- 两个表必须是innodb表类型
- 使用在外键关系的域必须为索引型
- 使用在外键关系的域必须与数据类型相似
事件触发限制
on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
创建表时指定外键 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，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
删除视图
|
1 2 |
drop view view_stu1; |
存储过程
修改MySQL定界符
delimiter 定界符[后面无 ; ]
存储过程是==主动调用==的，且功能比触发器更加强大，触发器是某件事触发后自动调用；
存储过程的优点：
- 增强SQL语句的功能和灵活性
- 实现较快的执行速度
- 减少网络流量
创建
create procedure pr_name(a int)
begin
//代码块
end
变量
1．定义变量
DECLARE my_sql INT DEFAULT 10;
2．为变量赋值
SET my_sql=30;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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; |
调用：
|
1 2 3 4 |
set @a = 10; set @b = 20; call pr_add(@a, @b); |
控制语句
IF语句：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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语句：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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语句：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
存储过程弊端
不同数据库语法差别很大 ,移植困难，换了数据库，需要重新编写；
不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；
删除存储过程
|
1 2 |
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;
创建触发器
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 触发器名;
