7 存储过程、内置函数
一 存储过程:
存储过程就类似于python中的自定义函数
它的内部包含了一系列可以执行的sql语句,存储过程存放于MySQL服务端中,你可以直接通过调用存储过程触发内部sql语句的执行
1 基本使用
create procedure 存储过程的名字(形参1,形参2,...)
begin
sql代码
end
2 调用
call 存储过程的名字();
3 三种开发模型
第一种
应用程序:程序员写代码开发
MySQL:提前编写好存储过程,供应用程序调用
好处:开发效率提升了 执行效率也上去了
缺点:考虑到认为元素、跨部门沟通的问题 后续的存储过程的扩展性差
第二种
应用程序:程序员写代码开发之外 设计到数据库操作也自己动手写
优点: 扩展性很高
缺点:
开发效率降低
编写sql语句太过繁琐 而且后续还需要考虑sql优化的问题
第三种
应用程序:只写程序代码 不写sql语句 基于别人写好的操作MySQL的python框架直接调用操作即可, ORM框架
优点:开发效率比上面两种情况都要高
缺点:语句的扩展性差 可能会出现效率低下的问题
第一种基本不用。一般都是第三种,出现效率问题再动手写sql
4 存储过程具体演示
(1)、无参存储过程
delimiter $$
create procedure p1()
begin
select * from user;
end $$
delimiter ;
# 调用
call p1()
(2)、有参存储过程
delimiter $$
create procedure p1(
in m int, # 只进不出 m不能返回出去
in n int,
out res int # 该形参可以返回出去
)
begin
select * from student where sid>m and sid<n;
set res=666; # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;
调用
call 存储过程的名字();
# 针对形参res 不能直接传数据 应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;
call p1(1,5,@ret);
select @ret;
5 在pymysql模块中如何调用存储过程呢?
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'day48',
charset = 'utf8',
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())
6 删除存储过程
DROP PROCEDURE 过程名
DROP PROCEDURE p1;
二 内置函数
跟存储过程是有区别的,存储过程是自定义函数,函数就类似于是内置函数
1.移除指定字符
Trim、LTrim、RTrim
2.大小写转换
Lower、Upper
3.获取左右起始指定个数字符
Left、Right
4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
5.日期格式:date_format
在MySQL中表示时间格式尽量采用2022-11-11形式
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');
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff计算两个日期差值