06 -07 MySQL使用 七(视图 存储过程 触发器）

视图（view）

1、查询结果的快照
2、简化查询操作（后续需要查询以前查询过的内容，可以直接简单的
查看视图，无须再次写复杂的SQL语句）
3、通过视图可以将用户的的访问权限限制到某些指定的列上

（如限制查询table权限，允许查看某视图）

例如：

create view vw_emp_dept as select eno,ename,dname from tb_emp t1 inner 
join tb_dept t2 on t1.eno = t2.dno;

将查询员工编号，姓名，部门名称的结果创建为名为 vw_emp_dept 的视图，方
便后续再次快速查询

– 查询视图

select ename,dname from vw_emp_dept ; 
注： 多列的视图可以只查询 其中的 几列 ，且 不报错 ； 

– 删除视图

drop view vw_emp_dept ; 

存储过程

1、存储过程是提前存在数据库中的，已经编译好的，经过优化的SQL语句，是
二进制的东西；
2、直接调用存储过程比直接写sql语句性能要好；
3、即一个存储过程名字 代表一段sql语句；

– 重新定义定界符 $$ （因为之前是 ；）

 delimiter $$ 

将定界符改为$$,之前默认是；

– 创建存储过程

create procedure sp_dept_avg_sal ( [in] deptno int ,out avgsal float)
begin
	select avg(sal) into avgsal from tb_emp where dno = deptno;
end$$

解释如下：
1、(括号的参数为 输入参数 和输出参数，输入参数前可以不用写in，默认为in）
1-2 ：
返回参数可以改为

（deptno int , avgsal decimal（6,1）） 
意为 返回值总共为6位有效数字，小数点后1位；

2、存储过程不会返回东西

3、将查询部门平均工资的语句创建为一个存储过程；

– 将定界符改为 ；

delimiter ；

– 删除存储过程

drop  procedure sp_dept_avg_sal ; 

– 调用存储过程

call sp_dept_acgsal(20,@a) ;

将存储过程返回的结果命名为变量a，并且前边+@
（sql中的变量命名比python多@）

– 查看存储过程结果（查询变量值）

select @a from dual ; 

触发器

在执行增删 改操作时可以触发其他的级联操作，但是有可能导致’锁表’现象，实际开发中应该尽量避免使用触发器；

在修改表内容时：
如 update tb_dept set dno 10 where dno 11 ;
在部门表中修改部门编号10为11，但是此操作无法完成，因为部门编号 已经
被引用于 员工表，一旦修改部门表中dno，员工表中的dno也乱了(两个表之间有外键约束）；

为了完成上述操作(级联操作)：
（但是最好的选择是 什么都不写，默认不让删改外键约束因素）

方法一：

触发器，但是其效果差，会引起锁表，不用；

方法二：
修改创建外键约束时的SQL语句

原：
alter table tb_emp add constraint fk_emp_dno foreign key (dno)
references tb_dept(dno) ;
默认不让删，也不让改，所以只执行改删语句就报错 ；

修改： 为了实现级联操作

alter table tb_emp drop foreign key fk_emp_dno ;

alter table tb_emp  add constraint fk_emp_dno foreign key (dno) 
references tb_dept(dno)  on delete  restrict/cascade / set null  on update restrict ; 

rerstrict : 不允许操作 ；
cascad: 级联 （删除部门的时候员工表中有该部门的员工也会被删除）；
set null ：修改table tb_dept 中的dno 时将 员工表中的dno设置为 null，但是前提是 dno的字符类型设置不是 not null ；

外键约束时 加上述语句后可执行下边语句无碍；

alter table tb_dept set dno = 11 where dno = 10 ;

delete from tb_dept where dno = 10 ;