mysql高级：触发器、事务、存储过程、调用存储过程

 本文目录:

一、触发器

二、pymysql事务测试

三、存储过程

四、pymysql调用存储过程

 

 

一、触发器

　　 在某个时间发生了某个事件时  会自动触发一段sql语句

　　

　　create trigger cmd_insert_triger before insert on cmd for each row
    　　 begin
    　　 if new.success = "no" then
   　　  insert into errlog values(null,new.cmd,new.sub_time);
   　　  end if;
  　　   end

　　asdsadsadsadsadsadsd     ;
　　delimiter //

　　;

 

二、pymysql事务测试

　事务*****
    是逻辑上的一组sql语句   他们要么都成功 要么都失败  今后只要执行sql就会有事务
    start transaction
    rollback  出现错误则执行回滚
    commit  没有错误则提交

　四个特性    原子性 一致性 隔离性 持久性
    四个隔离级别  读未提交  读已提交  可重复读  序列化执行(串行)

import pymysql

conn = pymysql.connect(
    user="root",
    password="root",
    database="day48",
    charset="utf8"
)

cursor = conn.cursor(pymysql.cursors.DictCursor)
# cursor.execute("delete from account where id =1")
# conn.commit()

sql = "update account set money = money - 100    where id = 2"
sq2 = "update account set moneys = money + 100    where id = 3"
try:
    cursor.execute(sql)
    cursor.execute(sq2)
    conn.commit()
    print("提交了!")
except:
    print("回滚了!")
    conn.rollback()


# 把你需要放在同一事务的sql执行 放在try中  最后加上commit
# 如果捕获到异常则执行rollback

# 在mysql客户端中 如果遇到了sql语句异常 能回滚吗?


"""
脏读?
    读取到另一个事务未提交的数据
不可重复读 
    一个事务在查询 一个在update   第一次查 和第二次查有可能数据不一样
幻读  
    一个事务在查 另一个insert 或 delete 第一次查 和第二次查有可能记录不一样

"""

# 什么是事务？
# 逻辑上的一组操作

# 事务的特点？
# 原子性
# 一致性
# 隔离性
# 持久性

# 什么时候使用事务？
# 当需要保证一堆sql 要么都成功 要么都失败时

 

三、存储过程

1.数据库操作全都放到mysql中,应用程序不需要编写sql语句 直接调存储过程
优点:
    应用程序开发者,工作量降低,
    提高程序的执行效率 因为网络io减少了
缺点:学习成本高,扩展性 维护性差
     部门间沟通成本


2.应用程序完全自己编写sql语句
优点:
    扩展性 维护性高
    部门间沟通成本
缺点:工作量大,sql语句的优化需要应用程序开发者完成

今后常用的
3.使用ORM(对象关系映射 )框架  可以直接使用面向对象的方式完成对数据库的CRUD
    简单的说就是帮你封装了sql语句的生成
优点:
不需要写sql语句,开发效率高
不需要考虑sql优化问题

缺点:执行效率比第二种方式略低 完全可以忽略


 delimiter //
 create procedure p1(in start int,in stop int,out res int)
    begin
    select *from student where id >= start and id <= stop;
    select *from student;
    set res = 1;
    end//
 delimiter ;


delimiter //
create PROCEDURE p5(OUT p_return_code tinyint)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        set p_return_code = 1;
        rollback;
    END;
    # exit 也可以换成continue 表示发送异常时继续执行
    DECLARE exit handler for sqlwarning
    BEGIN
        set p_return_code = 2;
        rollback;
    END;
    START TRANSACTION;
    update account set money = money - 1000 where id = 1;
    update account set money = money - 1000 where id = 1; # moneys字段导致异常
    set p_return_code = 0; #0代表执行成功
    COMMIT;
END //
delimiter ;

 

 

四、pymysql调用存储过程

import pymysql

conn = pymysql.connect(
    user="root",
    password="root",
    database="day48",
    charset="utf8"
)

cursor = conn.cursor(pymysql.cursors.DictCursor)

cursor.callproc("p1",(2,5,1)) #pymysql 会给参数全都创建对应的变量
# 命名方式 @_p1_0  @_p1_1 @_p1_2
print(cursor.fetchall()) # 如果过程中包含多个查询语句  得到的是第一条查询语句的结果

cursor.execute("select @_p1_2")
print(cursor.fetchone())


i = 0

 

五、备份与恢复

    mysqldump -u -p (库名 [表名] | --all--databases --databases 库名1 库名2) > 文件路径
    恢复
    1.mysql -u -p < 文件路径
    2.source 文件路径