MySQL之视图、触发器、存储过程、函数和流程控制

合集 - MySQL数据库炒年糕(16)

1.MySQL之初识数据库2024-03-032.MySQL之MySQL数据库安装2024-03-033.MySQL之SQL语句+严格模式2024-03-034.MySQL之存储引擎2024-03-035.MySQL之基本数据类型2024-03-036.MySQL之约束条件2024-03-037.MySQL之外键介绍2024-03-038.MySQL之修改表和复制表2024-03-039.MySQL之筛选和过滤条件2024-03-0310.MySQL之多表操作2024-03-0311.MySQL之pymysql模块2024-03-03

12.MySQL之视图、触发器、存储过程、函数和流程控制2024-03-03

13.MySQL之事务2024-03-0414.MySQL之索引2024-03-0415.MySQL补充：数据库的三大范式2024-03-1816.补充 windows系统安装多个MySQL版本2024-04-11

收起

视图

• 什么是视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用
其实视图也是表

• 为什么要用视图

如果要频繁的操作一张虚拟表（拼表组成的），你就可以制作成视图，后续直接操作

• 如何操作

# 固定语法
create view 表名 as 虚拟表的查询SQL语句
 
# 具体操作
create view teacher2course as
select * from teacher inner join course
on teacher.id = course.teacher_id;

• 注意

1. 创建视图在硬盘上只会有表结构，没有表数据(数据还是来自于之前的表)
2. 视图一般只用来查询，里面的数据不要继续修改，可能会影响真正的表

• 视图到底使用频率高不高？

不高，当你创建了很多视图之后，会造成表的不好维护
了解即可，基本不用！！！

触发器

在满足对表数据进行增、删、改的情况下，自动触发的功能。

使用触发器可以帮助我们实现监控、日志、自动处理异常等等。

触发器可以在六种情况下自动触发，增前、增后、删前、删后、改前、改后

基本语法结构

create trigger 触发器的名字 before/after insert/update/delete on 表的名字
for each row
begin
	SQL语句
end
 
# 具体使用，针对触发器的名字，我们通常需要做到见名知意
# 针对增
create trigger tri_before_insert_t1 before insert on t1
for each row
begin
	SQL语句
end
create trigger tri_after_insert_t1 after insert on t1
for each row
begin
	SQL语句
end
 
# 针对删和针对改，与上述格式一致
 
ps：修改MySQL默认的语句结束符,只作用于当前窗口
	delimiter $$ 将默认的结束符号由;变成$$
	delimiter ;
	
# 案例
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
);
 
# 需求
当cmd表中的记录success字段是no，那么就触发触发器的执行去errlog表中插入数据
# NEW指代的就是一条条数据对象
delimiter $$ 
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 ;
 
# 朝cmd表插入数据
insert into cmd(user,priv,cmd,sub_time,success) 
values
	('xiao','0123','ls -l /etc',NOW(),'yes'),
	('xiao','0123','cat /etc/passwd',NOW(),'no'),
	('xiao','0123','useradd xxx',NOW(),'no'),
	('xiao','0123','ps aux',NOW(),'yes');
	
# 模拟日志功能结果
select * from cmd;
+----+------+------+-----------------+---------------------+---------+
| id | user | priv | cmd             | sub_time            | success |
+----+------+------+-----------------+---------------------+---------+
|  1 | xiao | 0123 | ls -l /etc      | 2024-01-29 16:15:31 | yes     |
|  2 | xiao | 0123 | cat /etc/passwd | 2024-01-29 16:15:31 | no      |
|  3 | xiao | 0123 | useradd xxx     | 2024-01-29 16:15:31 | no      |
|  4 | xiao | 0123 | ps aux          | 2024-01-29 16:15:31 | yes     |
+----+------+------+-----------------+---------------------+---------+
select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2024-01-29 16:15:31 |
|  2 | useradd xxx     | 2024-01-29 16:15:31 |
+----+-----------------+---------------------+
 
# 删除触发器
drop trigger tri_after_insert_cmd;

存储过程

存储过程就类似于python中的自定义函数

它的内部包含了一系列可执行的SQL语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部SQL语句的执行

基本使用

create procedure 存储过程的名字(形参1，形参2，....)
begin 
	sql代码
end
 
# 调用
call 存储过程的名字();

三种开发模式

• 第一种

应用程序：程序员写代码开发
MySQL：提前编写好存储过程，供应用程序调用

好处：开发效率提升了，执行效率也上去了
缺点：考虑到人为因素，跨部门沟通的问题，后续的存储过程的扩展性差

• 第二种

应用程序：程序员自己写代码开发之外，涉及到数据库操作也自己动手写

好处：扩展性很高
缺点：开发效率降低，编写SQL语句太过繁琐，而且后续还需要考虑SQL优化的问题

• 第三种

应用程序：只写程序代码，不写SQL语句，基于别人写好的操作MySQL的python的框架直接调用操作即可。

优点：开发效率比上面两种都高
缺点：语句的扩展性差，可能会出现效率低下的问题

总结

第一种基本不用，一般都是第三种，出现效率问题再动手写sql

存储过程具体演示

delimiter $$
create procedure p1(
	in m int,  # in 表示只进不出，m不能返回出去
    in n int,
    out res int  # out表示该形参可以返回出去
)
begin 
	select name from teacher where id>m and id<n;
	set res=777;  # 将res变量修改，用来标识当前的存储过程代码确实执行了
end $$
delimiter ;
 
call p1(1,2,5);  # 报错，ERROR 1414 (42000): OUT or INOUT argument 3 for routine day45.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
# 针对形参res，不能直接传数据，应该先传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;
# 再次调用
call p1(1,2,@ret);

在pymysql模块中如何调用存储过程呢？

import pymysql
 
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='111111',
    database='day45',
    charset='utf8',  # 编码千万不要加-
    autocommit=True  # 自动提交
)  # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1', (1, 2, 5))
"""
@_p1_0=1
@_p1_1=2
@_p1_2=5
"""
print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())  # [{'@_p1_2': 777}]

函数

跟存储过程是有区别的，存储过程是自定义函数，函数就类似于内置函数。

# 示例：
create table blog(
	id int primary key auto_increment,
    name char(32),
    sub_time datetime
);
 
insert into blog(name,sub_time)
values
	('第1篇','2015-02-01 11:31:22'),
	('第2篇','2015-05-06 14:51:12'),
	('第3篇','2016-02-01 09:31:53'),
	('第4篇','2016-02-01 19:23:45'),
	('第5篇','2016-02-01 04:54:14'),
	('第6篇','2017-02-01 21:52:56'),
	('第7篇','2017-02-01 22:08:32'),
	('第8篇','2018-02-01 09:05:32'),
	('第9篇','2018-02-01 08:18:22');
 
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

流程控制

# if 判断
delimiter //
create procedure proc_if()
begin
	declare i int default 0;
	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 ;