MySQL-视图，触发器，存储过程

MySQL中的特性-视图，触发器，存储过程

MySQL存储过程

应用场景

我们之前前使⽤的⼤多数SQL语句都是针对⼀个或多个表的单条语句。并⾮所有操作都这么简单，经常会有⼀个完整的操作需要多条语句才能完成。

例如以下的情形：

• 为了处理订单，需要核对以保证库存中有相应的物品。

• 如果库存有物品，需要预定以便不将它们再卖给别的⼈， 并减少可⽤的物品数量以反映正确的库存量。

• 库存中没有的物品需要订购，这需要与供应商进⾏某种交互。

• 执⾏这个处理需要针对许多表的多条MySQL语句。可能需要执⾏的具体语句及其次序也不是固定的。

思考：

那么，怎样编写此代码?可以单独编写每条语句，并根据结果有条件地执⾏另外的语句。

在每次需要这个处理时(以及每个需要它的应⽤中)都必须做这些⼯作，我们可以封装一系列SQL，方便后序使用。

什么是存储过程？

存储过程是MySQL5.X版本新增的功能，简单来说，就是为以后的使⽤⽽保存 的⼀条或多条MySQL语句的集合，有点像子查询，但是子查询只是一条SQL语句而已。

储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调⽤。

存储过程就像脚本语⾔中函数定义⼀样。

为什么要使⽤存储过程?

优点:

1. 可以把⼀些复杂的sql进⾏封装,简化复杂操作

2. 保证了数据的完整性,防⽌错误

3. 简单的变动只需要更改存储过程的代码即可

4. 提⾼性能。因为使⽤存储过程⽐使⽤单独的SQL语句要快。(预先编译)

缺点:

存储过程的编写⽐SQL语句复杂

⼀般可能还没有创建存储过程的权限,只能调⽤

个⼈观点:

业务逻辑不要封装在数据库⾥⾯,应该由应⽤程序(JAVA、Python、PHP)处理。

让数据库只做它擅⻓和必须做的，减少数据库资源和性能的消耗。

维护困难，⼤量业务逻辑封装在存储过程中，造成业务逻辑很难剥离出来。动A影响B。

⼈员也难招聘，因为既懂存储过程，⼜懂业务的⼈少。使⽤困难。

在电信、银⾏业、⾦融⽅⾯以及国企都普遍使⽤存储过程来熟悉业务逻辑，但在互联⽹中相对较少。

创建存储过程

简单案例：添加100条数据

\d //
create procedure function1()
begin;

set @i=0;

while @i<=100 do
inset into user value(null,concat('user',@i),@i,0);
set @i=@i+1;
end while;

end;
//

存储过程的设计是美好的，但是我们应该让数据库只用来存数据，让我们后端语言去写业务逻辑，所以存储过程用的人少之又少。

MySQL的触发器

应用场景

如果你想要某条语句(或某些语句)在事件发⽣时⾃动执⾏，怎么办呢?

例如:

每当增加⼀个顾客到某个数据库表时，都检查其电话号码格式是否正确;

每当订购⼀个产品时，都从库存数量中减去订购的数量;

⽆论何时删除⼀⾏，都在某个存档表中保留⼀个副本。

所有这些例⼦的共同之处是它们都需要在某个表发⽣更改时⾃动处理。这确切地说就是触发器。

触发器的定义

触发器是MySQL响应写操作(增、删、改)⽽⾃动执⾏的⼀条或⼀组定义在BEGIN和END之间的MySQL语句。

可以理解为提前定义好⼀个或⼀组操作,在指定的SQL操作前或后来触发指定的SQL⾃动执⾏，触发器就像是JavaScript中的事件⼀样。

举例:

定义⼀个update语句,在向某个表中执⾏insert添加语句时来触发执⾏,就可以使⽤触发器

触发器语法：

注意:如果触发器中SQL有语法错误,那么整个操作都会报错

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

说明：

#trigger_name：触发器名称

#trigger_time:触发时间，可取值：BEFORE或AFTER

#trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。

#tb1_name：指定在哪个表上

#trigger_stmt：触发处理SQL语句。

– 查看所有的 触发器

show triggers\G;

– 删除触发器

drop trigger trigger_name;

– 创建⼀个删除的触发器,在users表中删除数据之前,往del_users表中添加⼀个数据

– 1,复制当前的⼀个表结构

create table del_users like users;

– 2,创建 删除触发器 注意在创建删除触发器时,只能在删除之前才能获取到old(之前的)数据

\d //
create trigger deluser before delete on users for each row
begintips：

在INSERT触发器代码内，可引⽤⼀个名为NEW的虚拟表，访问被 插⼊的⾏;

在DELETE触发器代码内，可以引⽤⼀个名为OLD的虚拟表，访问被删除的⾏;

OLD中的值全都是只读的，不能更新。

在AFTER DELETE的触发器中⽆法获取OLD虚拟表

在UPDATE触发器代码中:

可以引⽤⼀个名为OLD的虚拟表访问更新以前的值

可以引⽤⼀个名为NEW的虚拟表访问新 更新的值;

触发器同样也是可以用后端语言来代替，所以使用的人并不读偶

MySQL中的视图

什么是视图？

视图是虚拟的表。与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。

视图仅仅是⽤来查看存储在别处的数据的⼀种设施或⽅法。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

在添加或更改这些表中的数据时，视图将返回改变过的数据。

因为视图不包含数据，所以每次使⽤视图时，都必须处理查询执⾏时所需的任⼀个检索。

如果你⽤多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

视图的作⽤

重用SQL
简化复杂SQL。在编写查询后，可以方便地重用它而不必知道它的基本查询细节
使用表的注册部分而不是整个表
保护数据。给以给用户授予表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
注意：视图不能有索引，可不能有关联的触发器和默认值

视图的基础语法

创建视图:

create view v_users as select id,name,age from users where age >= 25 and age
<= 35;
-- Query OK, 0 rows affected (0.00 sec)

view视图的帮助信息:
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW

查看当前库中所有的视图

show tables; --可以查看到所有的表和视图
show table status where comment='view'; --只查看当前库中的所有视图

删除视图v_t1:

mysql> drop view v_t1;