大数据——MySQL触发器、视图、函数和存储过程

触发器

什么是MySQL的触发器

触发器用来在某些操作时，“自动”执行一些操作。
当insert delete update设置触发器之后，执行insert delete update操作就会自动触发设
置的内容。

注意：

定义触发器

语法：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中

注意：MySQL中;默认是语句结束符，在命令行使用时有问题
命令行使用多个触发语句时应当先修改语句结束符($$)

MySQL查看触发器

show triggers;
查看所有的触发器
select * from triggers;
在information_schema中查看triggers表 
PS:加where条件进行条件查询
show create trigger 触发器名;
查看触发器的创建语句
PS:删除触发器：drop trigger 触发器名;

MySQL触发器中的新旧记录

触发器主要事件是三种：
insert：新增记录，没有旧纪录；
delete：删除旧纪录，没有新纪录；
update：有变更后的新纪录，也有被变更的旧纪录；
可以使用old|new来引用这些记录
在for each row后(即触发语句)
PS:这时不是多条触发语句，不用begin...end;

示例一：需求说明
建立一张学员操作记录表grade_his
要求在grade表的基础上对增删改的操作记录至年级操作记录表
即在grade表插入时，在grade_his表中插入1条记录，数据为操作的动作（insert|update|delete），原记录数，操作完成后的记录数
以上的这些需求请使用触发器完成
语句展示：

CREATE TABLE grade_his(
	dong VARCHAR(20),
	old_his INT(10),
	new_his INT(10)
);
CREATE TRIGGER trig_c_insert AFTER INSERT ON grade FOR EACH ROW
INSERT INTO grade_his(dong,old_his,new_his)
VALUES('insert',(SELECT COUNT(1) FROM student)-1,
(SELECT COUNT(1) FROM student))

INSERT INTO grade(GradeID,GradeName)VALUES(5,'大五')
SELECT * FROM grade;
SELECT * FROM grade_his;

CREATE TRIGGER trig_c_update AFTER UPDATE ON grade FOR EACH ROW
INSERT INTO grade_his(dong,old_his,new_his)
VALUES('update',(SELECT COUNT(1) FROM student),
(SELECT COUNT(1) FROM student))
UPDATE grade SET GradeName='大六' WHERE GradeID=5

DROP TRIGGER trig_c_delete
CREATE TRIGGER trig_c_delete AFTER DELETE ON grade FOR EACH ROW
INSERT INTO grade_his(dong,old_his,new_his)
VALUES('delete',(SELECT COUNT(1) FROM student),
(SELECT COUNT(1) FROM student)-1)
 DELETE FROM grade WHERE GradeID=5
 
REATE TRIGGER trig_c_insert BEFORE INSERT ON grade FOR EACH ROW
INSERT INTO grade_his(dong,old_his,new_his)
VALUES('insert',(SELECT COUNT(1) FROM student),
(SELECT COUNT(1) FROM student)+1)

CREATE TRIGGER trig_c_update BEFORE UPDATE ON grade FOR EACH ROW
INSERT INTO grade_his(dong,old_his,new_his)
VALUES('update',(SELECT COUNT(1) FROM student),
(SELECT COUNT(1) FROM student))
 
CREATE TRIGGER trig_c_delete BEFORE DELETE ON grade FOR EACH ROW
INSERT INTO grade_his(dong,old_his,new_his)
VALUES('delete',(SELECT COUNT(1) FROM student),
(SELECT COUNT(1) FROM student)-1)

结果展示：

MySQL视图

什么是视图

视图是存储在数据库中的查询的sql 语句
本质是查询语句的结果，存入一个临时空间
是一个虚拟表，其内容由查询定义
视图在数据库中没有原本的物理存储，只是相当于临时表
对其中所引用的基础表来说，MySQL视图的作用类似于筛选
可以是一个表或多个表
视图的优点：
简单化，数据所见即所得
安全性，用户只能查询或修改他们所能见到得到的数据
逻辑独立性，可以屏蔽真实表结构变化带来的影响
视图的缺点：
性能差，修改不方便

视图的作用

作用1：提高重用性

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

作用2：对外接口稳定

当数据库底层表发生改变时，原本的表可能不存在，使用视图则不存在此问题

作用3：提高安全性

只开放特定字段给外部接口

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

视图的创建与使用

创建语法
语法结构：
{ CREATE|REPLACE } [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名 [{属性列名}] AS SELECT_STATEMENT（查询语句）
[WITH [CASCADED | LOCAL |] CHECK OPTION]
ALGORITHM:视图算法（1、默认；2、合并（视图和外层语句合并后执行）；3、结果
存入临时表）
视图的使用：与查询表的语句类似

示例二：需求说明
在学生表和年级表形成视图，展示学生id，学生姓名，年纪id，年级名称
语句展示：

CREATE VIEW v_student_grade AS SELECT s.StudentNo,s.StudentName,
s.GradeID FROM student s JOIN grade g ON s.GradeId=g.GradeID
SELECT * FROM v_student_grade

结果展示：

示例三：需求说明
在成绩表、学生表和学科表形成视图，展示学生姓名，学科名称，成绩、年级名称
语句展示：

CREATE VIEW v_student_subject_reault_gradeid AS SELECT s.StudentName,
sub.SubjectName,r.score,s.GradeID FROM student s JOIN
result r ON s.StudentNo=r.stu_id JOIN `subject` sub ON 
sub.SubjectINO=r.sub_no
SELECT * FROM v_student_subject_reault_gradeid

结果展示：

存储过程与UDF函数

存储过程与函数的相同点：
都是一系列sql语句的集合，调用时一次性执行这些sql语句，与java中方法的概念类似
都是对MySQL功能的扩展
存储过程与函数的不同点：
存储过程没有返回值，函数有一个返回值
存储过程注重的是执行过程，函数注重的是返回值

存储过程的定义

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

调用：call getInfo(“黄老师”,@grade_id)

UDF自定义函数的定义

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