触发器、视图、函数和存储过程
触发器
1.触发器的概念
触发器用来在某些操作时,“自动”执行一些操作。当insert delete update设置触发器之后,执行insert delete update操作就会自动触发设置的内容。
注:一张表最大能有6个触发器(32,三种操作两种时间(before|after))
2.定义触发器
语法:
create trigger 触发器名 before|after 事件 on 表名 for each row 触发器语句;
触发器名建议为trigger_xxx,这样便于区分,触发器名不能重复。
before|after 代表触发器语句执行时间,如果是before,就是在insert|delete|update操作之前执行触发器语句;after就是之后。
事件就是insert delete update操作中的一个。
for each row 是代表任何记录执行对应操作都会触发器。
触发器语句就是触发器触发时要执行的语句。
3.多个触发语句
需要将多条语句包含在begin end中
语法:
CREATE TRIGGER 触发器名
before|after
insert|update|delete
ON 表名
FOR EACH ROW
BEGIN
触发语句1;触发语句2;...触发语句n;
END;
MySQL中;默认是语句结束符,在命令行使用时有问题
命令行使用多个触发语句时应当先修改语句结束符($$), delimiter修改语句结束符
4.MySQL查看触发器
show triggers; //查看所有的触发器
select * from triggers; //在information_schema中查看triggers表 PS:加where条件进行条件查询
show create trigger 触发器名; //查看触发器的创建语句 PS:删除触发器:drop trigger 触发器名;
5.MySQL触发器中的新旧记录
触发器主要事件是三种:
insert:新增记录,没有旧纪录;
delete:删除旧纪录,没有新纪录;
update:有变更后的新纪录,也有被变更的旧纪录;
可以使用old | new来引用这些记录
在for each row后(即触发语句)
PS:这时不是多条触发语句,不用begin…end;
MySQL视图
1.基本概念
视图是存储在数据库中的查询的sql 语句 ----本质是查询语句的结果,存入一个临时空间
是一个虚拟表,其内容由查询定义 ----视图在数据库中没有原本的物理存储,只是相当于临时表
对其中所引用的基础表来说,MySQL视图的作用类似于筛选 ----可以是一个表或多个表
视图的优点:
简单化,数据所见即所得
安全性,用户只能查询或修改他们所能见到得到的数据
逻辑独立性,可以屏蔽真实表结构变化带来的影响
视图的缺点:
性能差,修改不方便
2.视图的作用:
①提高重用性:需要频繁查询的复杂语句,使用视图可以相当于将语句存为临时表,调用时,只需要从视图的位置查询即可
②对外接口稳定:当数据库底层表发生改变时,原本的表可能不存在,使用视图则不存在此问题
③提高安全性:只开放特定字段给外部接口
④目的更明确,数据更清晰
3.视图的创建与使用
临时表语法:
insert into 表名[字段列表] select 语句;
创建视图语法:
{ CREATE|REPLACE } [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名 [{属性列名}] AS SELECT_STATEMENT(查询语句)
[WITH [CASCADED | LOCAL |] CHECK OPTION]
流程控制
1.顺序结构:
程序从上而下依次执行
2.分支结构:程序从两条或多条语句中选择一条去执行
2.1. if结构
语法
if 条件1 then 语句1;
elseif 条件2 then 语句2;
else 语句n;
if函数:
语法:select if(表达式1,表达式2,表达式3)
执行顺序:如果表达式1 满足,则返回表达式2的值,否则返回表达式3的值
2.2.case结构
2.2.1 类似于Java中的switch,一般用于实现等值判断
case 变量|表达式|字段
when 要判断的值 then 返回的值1//或语句1;
when 要判断的值 then 返回的值2//或语句2;
...
else 要返回的值n//或语句n;
end //case;
2.2.2 类似于Java中的多重if,一般用于实现区间判断
case
when 要判断的条件1 then 返回的值1//或语句1;
when 要判断的条件2 then 返回的值2//或语句2;
...
else 要返回的值n//或语句n;
end //case;
3.循环结构:
程序在满足一定条件的基础上,重复执行一段代码
存储过程与UDF函数
1.异同点
存储过程与函数的相同点:
都是一系列sql语句的集合,调用时一次性执行这些sql语句,与java中方法的概念类似
都是对MySQL功能的扩展
存储过程与函数的不同点:
存储过程没有返回值,函数有一个返回值
存储过程注重的是执行过程,函数注重的是返回值
2.语法
存储过程语法:
CREATE [or replace] procedure `存储过程名` (参数列表)
begin
sql语句
end;
UDF语法:
create function 函数名([参数列表]) returns 数据类型
begin
sql语句;
return 值;
end;