mysql之存储过程 | 函数及流程控制

存储过程 📶

定义：相当于python中的自定义函数 自己写一个功能
关键字：procedure
格式 ：create procedure 名字(参数)
       begin
    	   功能体代码块
       end 
调用：call 存储过程名字

1、无参数存储过程
delimiter $$    # 修改默认结束符号
create procedure p1()   # 创建p1存储过程
begin
	select * from user;  # 查看user表数据
end $$
delimiter ;    # 修改回默认结束符

call p1()   # 调用

2、有参存储过程
delimiter $$
create procedure p2(
    in m int,    # in表示则个参数必须只能是传入不能被返回出去 int表示在传值时只能传int类型
    in n int,
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from user where id > m and id < n;
    set res=0;   # 用来标志存储过程是否执行(后面演示)
end $$
delimiter ;

# 针对res需要提前定义
set @res=10;   # 定义  # 指定res=10
select @res;   # 查看
call p1(1,5,@res)   # 调用
select @res    # 查看

查看存储过程具体信息:
	show create procedure 存储过程名字;
查看所有存储过程：
	show procedure status;
删除存储过程
	drop procedure 存储过程名字;

#  使用pymysql来操作存储过程
import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',   # 本地回环地址
    port = 3306,     # mysql固定端口号
    user = 'root',   # 用户名
    passwd = '123',  # 密码
    db = 'db6',      # 指定库
    charset='utf8',  # 指定字符编码
    autocommit = True  # 二次确认
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 创建游标
cursor.callproc('p2',(1,3,10))   # 调用存储过程固定语法callproc(库名,参数)
print(cursor.fetchall())  # 查看结果

函数 🔍

#  相当于python中的内置函数，系统自带的，作者编写好的功能
注意 ：注意与存储过程的区别，mysql内置的函数只能在sql语句中使用
补充 ：可以通过 (help 函数名) 的方式查看帮助信息

1、移除指定字符(相当于python的.strip())
Trim  ：移除左右两边指定字符
LTrim ：移除左侧指定字符
RTrim ：移除右侧指定字符

2、大小写转换
Lower ：全部转换为小写
upper ：全部转换为大写

# 应用于图片验证码 ：图片验证码为大写和小写结合，但是我们通常全部输入小写或者全部输入大写就可以，这就应用到了用户输入后，全部转换为大写/小写在匹配。

3、获取左右起始指定个数字符
left : 从左起始
rigth: 从右起始

4、返回读音相似的值(只对英文效果)
soundex

5、日期格式：date_format
%Y:年
%m:月
%d:日
%H:时
%M:分
%S:秒
%X:时分秒

案例：

# 在mysql中表示时间格式尽量采用2022-1-1的形式
create table blog(
    id int primary key auto_increment,
    name char(32),
    sub_time datetime
);
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');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');  # group by分组时间格式为年月分组并且count计数

补充：

date ：年月日
where date(sub_time) = '2015-03-01'
year ：年份
where year(sub_time) = 2016
month : 月份
where month(sub_time) = 07

adddate 增加一个日期
addtime 增加一个时间
datediff  计算两个日期的差值

流程控制 ☄️

# 用于写存储过程中等

# if 判断
if i = 1 then
	select 1;
elseif i = 2 then
	select 2;
else
	select 7;
end if;

# while循环
set num = 0;
while num < 10 do
	select num;
	set num = num + 1;
end while;
# 循环10次结束