07--视图、触发器、存储过程、函数、流程控制
一、视图
视图就是通过查询一张虚拟表，然后保存下来，下次直接使用
1、为什么要用视图
如果要频繁的操作一张虚拟表（拼表组成的），你就可以制作成视图，后续直接操作
2、如何操作
#固定语法
create view 表名 as 虚拟表的查询sql语法
#创建视图
create view new_emp as select name,age,post from employee where salary > 10000;
# 查看视图
show create view new_emp;
select * from new_emp;
# 修改视图
alter view new_emp as select name,age from employee where id < 10;
#删除视图
drop view new_emp;
3、注意
1、创建视图在硬盘上只会有表结构 没有表数据（数据还是来自之前的表）
2、视图一般只用来查询，里面的数据不要修改，否则会影响真正的表
# 视图使用频率不高，因为创建了很多视图，会造成表的不好维护
二、触发器
在满足对表数据进行【增、删、改】操作时前后的行为（6种情况），自动触发的功能,注意：没有查询
使用触发器可以帮助我们实现监控、日志
# 删除不能使用触发器
# 语法结构
create trigger 触发器名字 /before/after insert/update/delete on 表名
for each row
begin
sql语句
end
#具体使用，触发器的名字 我们要做到见名之意
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN #增加
drop trigger tri_after_insert_cmd; #删除
# ps:修改Mysql默认语句结束符
delimiter $$ 将默认的结束符合改为$$
# delimiter ; 就可以改回来的
=======================================================
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
#可以说MySQL创建以下六种触发器：
1.插入前 # before insert
create trigger tri_before_insert_tb1 before insert on tb1 for each row
begin
...
end
1.删除前 # before delete
create trigger tri_before_delete_tb1 before delete on tb1 for each row
begin
...
end
3.更新前 # before uptate
create trigger tri_before_update_tb1 before update on tb1 for each row
begin
...
end
4.插入后 # after insert
create trigger tri_after_insert_tb1 after insert on tb1 for each row
begin
...
end
5.删除后 # after delete
create trigger tri_after_deletet_tb1 after delete on tb1 for each row
begin
...
end
6.更新后 # after update
create trigger tri_after_update_tb1 after update on tb1 for each row
begin
...
end
1、实例
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
# 创建触发器
delimiter // #声明改sql语句的结束符号，也可以换成别的符号,如:$$
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #等值判断只有一个等号
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
END IF ; #必须加分号
END//
delimiter ; #改回sql语句的结束符号为分号
# 表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');
#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd | err_time |
+----+-----------------+---------------------+
| 1 | cat /etc/passwd | 2021-07-04 10:02:08 |
| 2 | useradd xxx | 2021-07-04 10:02:08 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)
# 删除触发器
drop trigger tri_after_insert_cmd;
三、存储过程
1.介绍
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql，类似与python自定义函数
1）使用存储过程的优点：
#1. 用于替代程序写的SQL语句，实现程序与sql解耦
#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大
2）存储过程的缺点：
#1. 程序员扩展功能不方便
3）补充：程序与数据库结合使用的三种方式
#方式一：
MySQL：存储过程
程序：调用存储过程
#方式二：
MySQL：
程序：纯SQL语句
#方式三：
MySQL:
程序：类和对象，即ORM（本质还是纯SQL语句）
2.基本使用
create procedure 存储过程的名字（形参1，形参2，形参3...）
begin
sql代码
end
# 调用
call 存储过程的名字（）
开发模式介绍
# 程序员 数据库管理者
应用程序 存储过程
应用程序+sql语句
应用程序+orm框架
# IO多路复用
https://www.cnblogs.com/linhaifeng/articles/7495918.html
三种开发模型
# 第一种
应用程序：程序员写代码开发
mysql：提前编写好存储过程 ，供应用程序调用
优点：开发效率提升了，执行效率上去了
缺点：考虑到人为元素、跨不忙沟通的问题，后续存储过程的跨扩展性差
# 第二种
应用程序：程序员写代码开发之外，设计到数据库操作也自己手写
优点：存储过程的跨扩展性很高
缺点：编写sql语句太多频繁，后续还需要考虑sql优化的问题
# 第三种
应用程序：只写程序代码，不写sql语句，基于别人写好的mysql的python框架直接调用即可
#框架 半成品 ORM跨架
优点： 开发效率比上面两种效率高
缺点：语句扩展性差，可能出现效率低下爱的问题
# 第一种基本不用，一般用第三种，出现效率问题在手动写sql
3.存储过程具体演示
对于存储过程，可以接收参数，其参数有三类：
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
delimiter //
create procedure proc_name(
in m int, # 只进不出，m不能返回出去
in n int
out res int, # 该形参可以返回回去
)
begin
select tname from teacher where tid>m and tid<n;
set res=666 #将res变量修改，用来标识当前的存储过程代码确实执行了
end //
delimiter ;
#针对形参res，不能直接传数据，应该定义一个变量名
set @ret= 10；
#查看变量对应的值
select @ret;
#执行存储过程
-- 无参数
call proc_name()
-- 有参数，全in
call proc_name(1,2)
-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)
4.删除存储过程
drop procedure proc_name;
四、函数
#！！！注意！！！
#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#函数只能在sql语句中使用，不能独立调用
#若要想在begin...end...中写sql，请用存储过程
存储过程是自定义函数，而函数是内置函数
#1、 准备表和记录
mysql> create table blog(
-> id int primary key auto_increment,
-> name char(30),
-> sub_time datetime
-> );
mysql> insert into blog(name,sub_time)
-> values
-> ('第1篇','2015-03-01 11:31:21'),
-> ('第2篇','2015-03-11 16:31:21'),
-> ('第3篇','2016-07-01 10:21:31'),
-> ('第4篇','2016-07-22 09:23:21'),
-> ('第5篇','2016-07-23 10:11:11'),
-> ('第6篇','2016-07-25 11:21:31'),
-> ('第7篇','2017-03-01 15:33:21'),
-> ('第8篇','2017-03-01 17:32:21'),
-> ('第9篇','2017-03-01 18:31:21');
#2. 提取sub_time字段的值，按照格式后的结果即"年月"来分组 date_format函数:格式化时间
mysql> select date_format(sub_time,"%Y-%m"),count(id) from blog group by date_format((sub_time,"%Y-%m");
#结果
+-------------------------------+-----------+
| date_format(sub_time,"%Y-%m") | count(id) |
+-------------------------------+-----------+
| 2015-03 | 2 |
| 2016-07 | 4 |
| 2017-03 | 3 |
+-------------------------------+-----------+
3 rows in set (0.00 sec
1.自定义函数
delimiter //
create function f1( #创建函数
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
delimiter //
create function f5(
i int
)
returns int
begin
declare res int default 0;
if i = 10 then
set res=100;
elseif i = 20 then
set res=200;
elseif i = 30 then
set res=300;
else
set res=400;
end if;
return res;
end //
delimiter ;
2.删除函数
drop function func_name;
3.执行函数
# 获取返回值
select UPPER('egon') into @res;
SELECT @res;
# 在查询中使用
select f1(11,nid) ,name from tb2;
五、流程控制
# 条件语句 if判断
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0; #先声明一个变量，i为整形
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
# while循环语句
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
call proc_while();
