? 缺点:执行效率低,由于需要将对象的操作转化为sql语句,且需要通过网络发送大量sql

创建存储过程

create procedure pro_name(p_Type p_name data_type)
begin
sql语句......流程控制
end

p_type 参数类型

? in 表示输入参数

? out 表示输出参数

? inout表示既能输入又能输出

p_name 参数名称

data_type 参数类型 可以是mysql支持的数据类型

案例:使用存储过程完成对student表的查询

delimiter //
create procedure p1(in m int,in n int,out res int)
begin
    select *from student where chinese > m and chinese < n;
    #select *from student where chineseXXX > m and chinese < n; 修改错误的列名以测试执行失败
    set res = 100;
end//
delimiter ;
set @res = 0;
#调用存储过程
call p1(70,80,@res);
#查看执行结果
select @res;

需要注意的是,存储过程的out类参数必须是一个变量,不能是值;

在python中调用存储过程

import  pymysql
#建立连接
conn = pymysql.connect(
    host="127.0.0.1",
    user="root",
    password="admin",
    database="db02"
)
# 获取游标
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 调用用存储过程
cursor.callproc("p1",(70,80,0)) #p1为存储过程名 会自动为为每个值设置变量,名称为 @_p1_0,@_p1_1,@_p1_2
# 提取执行结果时否有结果取决于存储过程中的sql语句
print(cursor.fetchall())
# 获取执行状态
cursor.execute("select @_p1_2")
print(cursor.fetchone())

此处pymysql会自动将参数都设置一个变量所以可以直接传入一个值,当然值如果作为输出参数的话,传入什么都无所谓!

删除存储过程

drop procedure 过程名称;

修改存储过程意义不大,不如删除重写!

查看存储过程

 #当前库所有存储过程名称
 select `name` from mysql.proc where db = 'db02' and `type` = 'PROCEDURE';
 #查看创建语句
 show create procedure p1;

存储过程中的事务应用

存储过程中支持任何的sql语句包括事务!

案例:模拟转账中发生异常,进行回滚

delimiter //
create PROCEDURE p5(OUT p_return_code tinyint)
BEGIN 
    DECLARE exit handler for sqlexception 
    BEGIN 
        # ERROR 
        set p_return_code = 1; 
        rollback; 
    END; 
    # exit 也可以换成continue 表示发生异常时继续执行
    DECLARE exit handler for sqlwarning 
    BEGIN 
        # WARNING 
        set p_return_code = 2; 
        rollback; 
    END; 

    START TRANSACTION; 
    update account set money = money - 1000 where id = 1;
    update account set moneys = money - 1000 where id = 1; # moneys字段导致异常
    COMMIT; 

    # SUCCESS 
    set p_return_code = 0; #0代表执行成功
END //
delimiter ;

#在mysql中调用存储过程
set @res=123;
call p5(@res);
select @res;

总结:抛开沟通成本,学习成本,存储过程无疑是效率最高的处理方式,面试会问,一些公司也有一些现存的存储过程,重点掌握!

五.函数

函数与python中的定义一致,不在啰嗦!

内置函数

日期相关:

字符串相关:

数字相关:

其他函数:

当然也包括之前学习的聚合函数

自定义函数

语法:

CREATE FUNCTION f_name(paramters)
returns dataType;
return value;

说明:
paramters 只能是in 输入参数 参数名 类型
必须有返回值
不能呢加begin 和end
returns 后面是返回值的类型 这里不加分号
return 后面是要返回的值

案例:
将两数相加

create function addfuntion(a int,b int)
returns int return a + b;
#执行函数
select addfuntion(1,1);

注意:

函数只能返回一个值
函数一般不涉及数据的增删改查 就是一个通用的功能
调用自定义的函数 与调用系统的一致 不需要call 使用select 可获得返回值
函数中不能使用sql语句
就像在java中不能识别sql语句一样

六.数据备份与恢复

使用mysqldump程序进行备份

mysqldump -u -p db_name [table_name,,,] > fileName.sql

可以选择要备份哪些表 如果不指定代表 全部备份

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

使用 mysql 进行恢复

1.退出数据库后

mysql -u -p < filename.sql;

2.不用退出数据库

? 2.1 创建空数据库

? 2.2选择数据库

? 2.3然后使用source filename; 来进行还原

use db1;
source /root/db1.sql

数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

七.流程控制(了解)

if语句的使用

if 条件 then
语句;
end if;
第二种 if elseif
if 条件 then
语句1;
elseif 条件 then
语句2;
else 语句3;
end if;

案例:编写过程 实现 输入一个整数type 范围 1 - 2 输出 type=1 or type=2 or type=other;