1.函数
MySQL中提供了很多函数，为我们的SQL操作提供便利，例如：
count(id), max(id),min(id),avg(id),reverse(name) ,concat(name,name), NOW(), DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s') ,sleep(1)
更多函数：https://dev.mysql.com/doc/refman/5.7/en/functions.html
当然，MySQL中也支持让你去自定义函数。
-
创建函数
delimiter $$ create function f1( i1 int, i2 int) returns int BEGIN declare num int; declare maxId int; select max(id) from big into maxId; set num = i1 + i2 + maxId; return(num); END $$ delimiter ; -
执行函数
select f1(11,22); select f1(11,id),name from d1; -
删除函数
drop function f1;
2. 存储过程
存储过程，是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
-
创建存储过程
delimiter $$ create procedure p1() BEGIN select * from d1; END $$ delimiter ; -
执行存储过程
call p1();#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程 cursor.callproc('p1') result = cursor.fetchall() cursor.close() conn.close() print(result) -
删除存储过程
drop procedure proc_name;
2.1 参数类型
存储过程的参数可以有如下三种：
- in，仅用于传入参数用
- out，仅用于返回值用
- inout，既可以传入又可以当作返回值
delimiter $$
create procedure p2(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end $$
delimiter ;
set @t1 =4;
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p2',args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
result = cursor.fetchall()
# {"@_p2_0":11 }
cursor.close()
conn.close()
print(result)
2.2 返回值 & 结果集
delimiter $$
create procedure p3(
in n1 int,
inout n2 int,
out n3 int
)
begin
set n2 = n1 + 100;
set n3 = n2 + n1 + 100;
select * from d1; -- 结果集
end $$
delimiter ;
set @t1 =4;
set @t2 = 0;
CALL p3 (1,@t1, @t2);
SELECT @t1,@t2;
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p3',args=(22, 3, 4))
table = cursor.fetchall() # 得到执行存储过中的结果集
# 获取执行完存储的参数
cursor.execute("select @_p3_0,@_p3_1,@_p3_2")
rets = cursor.fetchall()
cursor.close()
conn.close()
print(table)
print(rets)
2.3 事务 & 异常
事务，成功都成功，失败都失败。
delimiter $$
create PROCEDURE p4(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION; -- 开启事务
delete from d1;
insert into tb(name)values('seven');
COMMIT; -- 提交事务
-- SUCCESS
set p_return_code = 0;
END $$
delimiter ;
set @ret =100;
CALL p4(@ret);
SELECT @ret;
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p4',args=(100))
# 获取执行完存储的参数
cursor.execute("select @_p4_0")
rets = cursor.fetchall()
cursor.close()
conn.close()
print(table)
print(rets)
2.4 游标
delimiter $$
create procedure p5()
begin
declare sid int;
declare sname varchar(50);
declare done int default false;
declare my_cursor CURSOR FOR select id,name from d1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into sid,sname;
IF done then
leave xxoo;
END IF;
insert into t1(name) values(sname);
end loop xxoo;
close my_cursor;
end $$
delimiter ;
call p5();
3.视图
视图其实是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
SELECT
*
FROM
(SELECT nid,name FROM tb1 WHERE nid > 2) AS A
WHERE
A.name > 'alex';
-
创建视图
create view v1 as select id,name from d1 where id > 1; -
使用视图
select * from v1; -- select * from (select id,name from d1 where id > 1) as v1; -
删除视图
drop view v1; -
修改视图
alter view v1 as SQL语句
注意：基于视图只能查询，针对视图不能执行 增加、修改、删除。 如果源表发生变化，视图表也会发生变化。
4.触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
DROP TRIGGER tri_after_insert_tb1;
示例：
-
在 t1 表中插入数据之前，先在 t2 表中插入一行数据。
delimiter $$ CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN -- NEW.id NEW.name NEW.email -- INSERT INTO t2 (name) VALUES(); IF NEW.name = 'alex' THEN INSERT INTO t2 (name) VALUES(NEW.id); END IF; END $$ delimiter ;insert into t1(id,name,email)values(1,"alex","xxx@qq.com") -
在t1表中删除数据之后，再在t2表中插入一行数据。
delimiter $$ CREATE TRIGGER tri_after_insert_t1 AFTER DELETE ON t1 FOR EACH ROW BEGIN IF OLD.name = 'alex' THEN INSERT INTO t2 (name) VALUES(OLD.id); END IF; END $$ delimiter ;
特别的：NEW表示新数据，OLD表示原来的数据。
总结
对于Python开发人员，其实在开发过程中触发器、视图、存储过程用的很少（以前搞C#经常写存储过程），最常用的其实就是正确的使用索引以及常见的函数。
- 索引，加速查找 & 约束。
- innodb和myisam的区别，聚簇索引 和 非聚簇索引。
- 常见的索引：主键、唯一、普通。
- 命中索引
- 执行计划
- 函数，提供了一些常见操作 & 配合SQL语句，执行后返回结果。
- 存储过程，一个SQL语句的集合，可以出发复杂的情况，最终可以返回结果 + 数据集。
- 视图，一个虚拟的表。
- 触发器，在表中数据行执行前后自定义一些操作。
