mysql的事务处理 存储过程 触发器_mysql之视图、触发器、事务和存储过程

一、视图

1、什么是视图

视图就是通过查询得到的一张虚拟表，然后保存下来，下次直接使用即可

2、为什么要用视图

如果要频繁的使用一张虚拟表，可以不用重复查询

3、如何使用视图

create view teacher2course as

select * from teacher inner joincourseon teacher.tid = course.teacher_id

强调：

1、在硬盘中，视图只有表结构，没有表数据文件

2、视图通常是用于插叙，尽量不要修改视图中的数据

二、触发器

1、什么是触发器

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

2、为何要用触发器

触发器专门针对我们某一张表数据增insert、删delete、改update的行为，这类

行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码

3、创建触发器语法

#针对插入create trigger tri_after_insert t1 after insert on 表名 foreach rowbeginsql代码...end

create trigger tri_before_insert t1 before insert on 表名 foreach rowbeginsql代码...end#针对删除create trigger tri_after_insert t1 after delete on 表名 foreach rowbeginsql代码...end

create trigger tri_before_insert t1 before delete on 表名 foreach rowbeginsql代码...end#针对修改create trigger tri_after_insert t1 after delete on 表名 foreach rowbeginsql代码...end

create trigger tri_before_insert t1 before delete on 表名 foreach rowbeginsql代码...end#针对修改create trigger tri_after_insert t1 after update on 表名 foreach rowbeginsql代码...end

create trigger tri_before_insert t1 before update on 表名 foreach rowbeginsql代码...end

4、案例

CREATE TABLEcmd (

idINT PRIMARY KEYauto_increment,USER CHAR (32),

privCHAR (10),

cmdCHAR (64),

sub_timedatetime, #提交时间

success enum ('yes', 'no') #0代表执行失败

);CREATE TABLEerrlog (

idINT PRIMARY KEYauto_increment,

err_cmdCHAR (64),

err_timedatetime);

delimiter $$create trigger tri_after_insert_cmd after insert on cmd foreach rowbegin

if new.success = 'no' then

insert into errorlog(err_cmd,err_time) values(new.cmd,new.sub_time);end if;end$$

delimiter ;

三、事务

1、什么是事务

开启一个事务可以包含一些sql语句，这些语句要么同时成功，

要么一个都别想成功，这个特性称为事务的原子性

2、事务的作用

用于设计转账接口的时候使用

3、如何使用

create table user(

idint primary keyauto_increment,

namechar(32)

balanceint);insert into user(name,balance) values('wsb',1000),

('egon',1000),

('ysb',1000);

#得到的结果如下

mysql> select * from user;+----+------+---------+

| id | name | balance |

+----+------+---------+

| 1 | wsb | 1000 |

| 2 | egon | 1000 |

| 3 | ysb | 1000 |

+----+------+---------+

#原子操作

starttransaction;update user set balance=900 where name='wsb';update user set balance=1010 where name='egon';update user set balance=1090 where name='ysb';commit;

#得到的结果如下：

mysql> select * from user;+----+------+---------+

| id | name | balance |

+----+------+---------+

| 1 | wsb | 900 |

| 2 | egon | 1010 |

| 3 | ysb | 1090 |

+----+------+---------+

#出现异常，回滚到原始状态

starttransaction;update user set balance=900 where name='wsb'; #买支付100元update user set balance=1010 where name='egon'; #中介拿走10元

uppdateuser set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到

#rollback之前的结果

mysql> select * from user;+----+------+---------+

| id | name | balance |

+----+------+---------+

| 1 | wsb | 900 |

| 2 | egon | 1010 |

| 3 | ysb | 1090 |

+----+------+---------+

rollback;

#rollback之后的结果

mysql> select * from user;+----+------+---------+

| id | name | balance |

+----+------+---------+

| 1 | wsb | 1000 |

| 2 | egon | 1000 |

| 3 | ysb | 1000 |

+----+------+---------+

commit;

在pymysql中使用的伪代码

try:update user set balance=900 where name='wsb'; #买支付100元update user set balance=1010 where name='egon'; #中介拿走10元update user set balance=1090 where name='ysb'; #卖家拿到90元except异常：rollback;else:commit

四、存储过程

1、什么是存储过程

存储过程包含了一系列可执行的sql语句，存储过程中存放于mysql中，通过调用它的名字可以

执行器内部的一堆sql

2、三种开发模型

1、

应用程序：只需要开发应用程序的逻辑

mysql：编写好存储过程，以供应用程序使用

优点：开发效率，执行效率高

缺点：考虑到人为因素，两个部门的协同效率不会很高，跨部门沟通困难，从而导致其扩展性差

2、

应用程序：除了开发应用程序的逻辑，还需要编写原生的sql

mysql：

优点：比方式1扩展性高(非技术性)

缺点：

1、开发效率，执行效率都不如方式1

2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题

3、

应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，即ORM

mysql：

优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处

缺点：执行效率甚至赶不上方式2

4、创建存储过程

delimiter $$create procedurep1(in m int,in n int,

out resint)begin

select tname from teacher where tid > m and tid

delimiter ;1、直接在mysql中调用set @res = 10call p1(2,4,@res);

#查看结果select @res;2、在python程序中调用

import pymysql

conn=pymysql.connect(

host='127.0.0.1',

port=3306,user='root',

password='123',

charset='utf8',database='db42')cursor = conn.cursor(pymysql.cursors.DictCursor)cursor.callproc('p1',(2,4,10)) #@_p1_0=2,@_p1_1=4,@_p1_2=10

print(cursor.fetchall())cursor.excute('select @_p1_2;')print(cursor.fetchall())cursor.close()

conn.close()

#存储过程和事务的连用

delimiter//

create PROCEDUREp5(

OUT p_return_codetinyint)BEGIN

DECLARE exit handler forsqlexceptionBEGIN

--ERROR

set p_return_code = 1;rollback;END;DECLARE exit handler forsqlwarningBEGIN

--WARNING

set p_return_code = 2;rollback;END;

STARTTRANSACTION;update user set balance=900 where id =1;update user123 set balance=1010 where id = 2;update user set balance=1090 where id =3;COMMIT;--SUCCESS

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

delimiter//

create PROCEDUREp6(

OUT p_return_codetinyint)BEGIN

DECLARE exit handler forsqlexceptionBEGIN

--ERROR

set p_return_code = 1;rollback;END;DECLARE exit handler forsqlwarningBEGIN

--WARNING

set p_return_code = 2;rollback;END;

STARTTRANSACTION;update user set balance=900 where id =1;update user set balance=1010 where id = 2;update user set balance=1090 where id =3;COMMIT;--SUCCESS

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

#在pymysql中调用

import pymysql

conn=pymysql.connect(

host='127.0.0.1',

port=3306,user='root',

password='123',

charset='utf8',database='db42')cursor = conn.cursor(pymysql.cursors.DictCursor)cursor.callproc('p5',(100,)) @_p5_0 = 100

cursor.excute('select @_p5_0')print(cursor.fetchall())cursor.close()

conn.close()