存储过程、触发器、视图55

存储过程 

什么是存储过程？
1.简单点来说，就是为了以后的使用，存储的一条或多条的mysql语句集合；
2.存储过程是一组为了完成特定功能的sql语句集，经过编译之后存储在数据库中，需要时直接调用；
3.存储过程就像函数定义一样。

为什么要使用存储过程？
优点：
·将复杂的sql语句进行封装，简化复杂操作；
·保证了数据的完整性，防止错误；
·简单的变动只要改变存储过程的代码即可；
·提高性能。使用存储过程比使用单条sql语句要快（预先编译）。

缺点：
·存储过程的编写比单条sql语句的编写复杂；
·可能还没有创建存储过程的权限。

个人观点：
·业务逻辑不要封装在数据库里面，应当由应用程序（JAVA，Python，PHP）处理；
·让数据库做它擅长做的事，减少数据库资源和性能的消耗；
·维护困难，大量的业务逻辑封装在存储过程中，造成业务逻辑很难剥离。动A影响B；
·人员招聘困难，因为既懂存储过程，又懂业务的人很少。

在电信，银行业，金融以及国企都普遍使用存储过程来熟悉业务，但是在互联网行业很少。


怎么用？

创建存储过程
\d //  
-- 更改mysql默认的语句结尾符; ，为//
create procedure p1()
begin
set @i = 10;
while @i < 90 do
insert into users values(null,concat('user:',@i),@i+10,0);
set @i = @i + 1;
end while;
end;
//


执行存储过程
call p1() //

查看创建存储过程
show create procedure p1\G

删除存储过程
drop procedure p1 //




触发器
--创建触发器
create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt

-- trigger_name:触发器的名字
-- trigger_time:出发时间（before or after）
-- trigger_event:触发事件（insert update delete）
-- tbl_name:操作的表名称
-- trigger_stmt:触发SQL语句

-- 查看所有的触发器
		

-- 删除触发器
drop trigger trigger_name;



Demo:生成一个 删除触发器， 每次在users表中删除数据，都会在另一个表中自动备份。
-- 创建一个备份表
create table del_users like users;

-- 创建触发器
\d //
create trigger delusers before delete on users for each row
begin  
insert into del_users values(old.id,old.name,old.age,old.account);
end;
//

-- 删除users中的数据，触发触发器
delete from table users where id = 1;
-- 结果
select * from users;
+----+--------+------+---------+
| id | name   | age  | account |
+----+--------+------+---------+
|  2 | 李四    |   25 |     100 |
|  3 | 王五    |   18 |       0 |
+----+--------+------+---------+
2 rows in set (0.00 sec)

select * from del_users;
+----+--------+------+---------+
| id | name   | age  | account |
+----+--------+------+---------+
|  1 | 张三    |   20 |    1000 |
+----+--------+------+---------+
1 row in set (0.00 sec)

练习题
-- 1.创建⼀个表, users_count ⾥⾯有⼀个 num的字段 初始值为0或者是你当前users表中的count
-- 2,给users表创建⼀个触发器
-- 当给users表中执⾏insert添加数据之后,就让users_count⾥⾯num+1,
-- 当users表中的数据删除时,就让users_count⾥⾯num-1,
-- 想要统计users表中的数据总数时,直接查看 users_count





-- 1.创建表users_count
create table `users_count`(
`num` int(255) default null
)engine=innodb default charset=utf8mb4;
insert into users_count values(2);

-- 2.创建触发器
\d //
create trigger d_users before delete on users for each row
begin
update users_count set num = num - 1;
end;
//

create trigger i_users before insert on users for each row
begin
update users_count set num = num + 1;
end;
//
-- 测试
insert into users values(null, '赵五',25,0);
delete from users where id = 2;


视图

视图是‘虚拟的表’

视图的作用
1.简化SQL语句的编写。在复杂的编写之后，可以方便的重用它，而不用知道具体的编写细节；
2.保护数据。可以给用户授权表指定部分的权限，而不是整个表的访问权；
3.更改数据格式和表示。视图可以返回和底层表格式和表示不同的表；
4.注意：视图不能有索引，也不能有关联的触发器和默认值。
视图的基础语法

-- 创建视图
create view v_users as 
	select name, age from users;

-- 视图的使用
select * from v_users;
+--------+------+
| name   | age  |
+--------+------+
| 王五   |   18 |
| 赵五   |   25 |
+--------+------+
2 rows in set (0.00 sec)
-- 视图创建之后就相当于是一张‘虚拟的’表

-- 视图的帮助信息
? view;
   ALTER VIEW
   CREATE VIEW
   DROP VIEW

-- 修改视图
alter view v_users as 
	select name,account from users;
select * from v_users;
+--------+---------+
| name   | account |
+--------+---------+
| 王五   |       0 |
| 赵五   |       0 |
+--------+---------+
2 rows in set (0.00 sec)

-- 查看管理视图
show tables;
show table status where comment = 'view';


-- 删除视图v_users
drop view v_users;