触发器、视图和存储过程

一、触发器

1、概念

触发器用来在某些操作时，“自动”执行一些操作。 当insert、delete、update设置触发器之后，执行insert、delete、update操作就会自动触发设置的内容（一张表最大能有6个触发器）。

2、语法

create trigger 触发器名 before|after 事件 on 表名

for each row 触发器语句；

触发器名建议为trigger_xxx，这样便于区分，触发器名不能重复。

before|after 代表触发器语句执行时间，如果是before,就是在insert|delete|update操作之前执行触发器语句；after就是之后。

事件就是insert delete update操作中的一个。

for each row 是代表任何记录执行对应操作都会触发器。

触发器语句就是触发器触发时要执行的语句。

多个触发语句，需要将多条语句包含在begin end中，语法如下：

CREATE TRIGGER 触发器名
before|after
insert|update|delete
ON 表名
FOR EACH ROW
BEGIN
触发语句1;触发语句2;...触发语句n;
END;

3、常用命令

删除触发器：drop trigger 触发器名

查看触发器：show triggers

SELECT * FROM information_schema.`triggers`

修改结束语句： delimiter $$（以$$为结束语句）

delimiter；（恢复以 ；结尾）

二、MySQL视图

1、概念

视图是存储在数据库中的查询的sql 语句 ，本质是查询语句的结果；

存入一个临时空间 是一个虚拟表，其内容由查询定义 ，视图在数据库中没有原本的物理存储，只是相当于临时表；

对其中所引用的基础表来说，MySQL视图的作用类似于筛选 可以是一个表或多个表。

2、视图的优缺点及作用

视图的优点：

简单化，数据所见即所得 安全性

用户只能查询或修改他们所能见到得到的数据 逻辑独立性

可以屏蔽真实表结构变化带来的影响

视图的缺点：

性能差，修改不方便

视图的作用

作用1：提高重用性。需要频繁查询的复杂语句，使用视图可以相当于将语句存为临时表，调用时，只需要从视图的位置查询即可

作用2：对外接口稳定。当数据库底层表发生改变时，原本的表可能不存在，使用视图则不存在此问题

作用3：提高安全性。只开放特定字段给外部接口

作用4：目的更明确，数据更清晰

3、语法

CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名 [{属性列名}] AS SELECT_STATEMENT（查询语句）
[WITH [CASCADED | LOCAL |] CHECK OPTION]

ALGORITHM:视图算法（1、默认；2、合并（视图和外层语句合并后执行）；3、结果存入临时表）

#查询获取所有学员对应所有学科的成绩表，形成视图
#在学生表和年级表形成视图，展示学生id，学生姓名，年纪id，年级名称
CREATE VIEW s_g AS 
SELECT StudentNo,StudentName,s.GradeId,GradeName FROM student s
JOIN grade g ON s.GradeId=g.GradeId;
#在成绩表、学生表和学科表形成视图，展示学生姓名，学科名称，成绩
CREATE VIEW s_r_sj AS
SELECT StudentName 姓名,SubjectName 学科,score 成绩 FROM student s
JOIN result r ON r.stuId=s.StudentNo
JOIN `subject` sj ON r.SubjectNo=sj.SubjectNo;
#合并上述两个需求，在需求二的基础上增加年级名称
CREATE VIEW s_r_sj AS
SELECT StudentName 姓名,SubjectName 学科,score 成绩 FROM student s
JOIN result r ON r.stuId=s.StudentNo
JOIN `subject` sj ON r.SubjectNo=sj.SubjectNo

三、存储过程与UDF函数

1、 存储过程语法 

CREATE procedure `存储过程名` (参数列表) 
begin     
sql语句 
end;

示例1

#存储过程（存过）
DELIMITER $$
CREATE PROCEDURE getName(IN stu_id INT(11),OUT stu_Name VARCHAR(50))
BEGIN
SELECT StudentName FROM student WHERE StudentNo=stu_id INTO stu_Name;
SELECT stu_Name;
END$$
DELIMITER ;
CALL getName(4,@a);
SELECT @a;

2、UDF语法

create function 函数名([参数列表]) returns 数据类型
begin
sql语句;
return 值;
end;

示例2

#UDF语法
DELIMITER $$
CREATE FUNCTION getName(stu_id INT(11))RETURNS VARCHAR(50)
BEGIN DECLARE stu_name VARCHAR(50);
SELECT StudentName FROM student WHERE StudentNo=stu_id INTO stu_Name;
RETURN stu_name;
END $$
DELIMITER ;
SELECT getName(4);