frm mysql触发器_MySQL：视图、触发器、存储过程、事务

视图：

视图，虚拟表

创建虚拟表：

# 语法：

#create view 虚拟表名称 as虚拟表;create view course_and_teacher as select * from course inner join teacher on course.teacher_id =teacher.tid;

# 虚拟表在硬盘上存储时，只有 表结构， 没有 表数据 那张表，即 只有 course_and_teacher.frm 这个文件；因为虚拟表的数据来自于其它表

# 创建的虚拟表可以直接使用：select * fromcourse_and_teacher;

# 每次使用 course_and_teacher 这张表时，都会触发select * from course inner join teacher on course.teacher_id =teacher.tid;

# 虚拟表不建议使用，因为 如果 你在数据库建了大量的视图，不利用扩展(数据库可能经常需要扩展；即强耦合)；所以尽量用原生的 SQL

# 另外，视图是用来查询的，不能用来修改(不要修改视图中的记录)

# 修改视图：

# 语法： alertview 视图名称 asSQL语句;

alertview course_and_teacher as select * from course where cid>3;

# 删除视图：

# 语法：drop view视图名称;drop view course_and_teacher;

触发器：

一碰就动；使用触发器可以定制用户对表 【增、删、改】操作时前后的行为(注意：没有查询)

# 一、创建触发器：

# 针对insert 可以是 before insert(对于每一行，在 insert 行为之前，去触发一个 begin.. end 之间的 SQL语句)， 也可以是 after insert (对于每一行，在 insert 行为之后，去触发一个 begin.. end之间的 SQL语句)

# 语法(插入前)：

#create trigger 触发器名称 before insert on 表名 foreach row

#begin ... end# 插入前：create trigger tri_before_insert_tb1 before insert on tb1 foreach rowbegin...end# 插入后：create trigger tri_after_insert_tb1 after insert on tb1 foreach rowbegin...end# 删除前：create trigger tri_before_delete_tb1 before delete on tb1 foreach rowbegin...end# 删除后：create trigger tri_after_delete_tb1 after delete on tb1 foreach rowbegin...end# 更新前：create trigger tri_before_update_tb1 before update on tb1 foreach rowbegin...end# 更新后：create trigger tri_after_update_tb1 after update on tb1 foreach rowbegin...end

示例：

# 准备表：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// # delimiter 用于声明 SQL语句的结束符 是 “//”，而不是 “;”create trigger tri_after_insert_cmd after insert on cmd foreach rowBEGIN# 触发器提供了两个对象：NEW 和 OLD；NEW代表新增的记录，OLD代表以前老的记录(插入行为没有 OLD 一说，因为插入的永远都是 新的；修改的情况下，才有老的记录，同 NEW 也能派上用场)if NEW.success = 'no' THEN# 等值判断只有一个等号； NEW.success 表示 NEW对象的 success 属性insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time); # 必须加分号end if; # 必须加分号END// # 此处的 “//” 表示 触发器定义完了

delimiter ; # 重新声明 SQL语句 的结束符 为 “;”

# NEW 表示即将插入的数据行，OLD 表示即将删除的数据行

# 触发器是 MySQL 的一个内置功能；我们也可以在 应用程序级别 自己去实现 触发器的功能，如利用 python 代码自己去实现

# 建议在 应用程序级别去实现 触发器的功能；方便以后扩展功能

# 使用触发器： 触发器无法由用户直接调用，而是由于对 表的 【增、删、改】 操作被动引发的

# 删除触发器：drop triggertri_after_insert_cmd;

# 查看触发器：

show triggers;

存储过程：

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

优点：1. 用于替代程序写的SQL语句，实现程序与sql解耦2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

缺点：

程序扩展功能不方便

示例：

# 调用存储过程

# MySQL中调用：

call p1();

# python中调用：cursor.callproc('p1') # 用 cursor 去调用 callproc('存储过程的名称') 方法

# 如果 存储过程中是查询语句，利用相应的方法去获取相应的结果：print(cursor.fetchall())

# 有参

delimiter//

create procedure p2(in n1 int,in n2 int,out res int) # 1. MySQL中的存储过程，参数必须指定 参数类型；2. 参数必须指明是用来接收值的，还是用来返回值的：in 代表传入参数， out表示返回参数，inout表示可进可出BEGIN

select * from db7.student where tid>n1 and tid

# MySQL 中调用：set @x=0# 设置变量的初始值

call p2(2,5,@x); # 传参select @x; # 查看 返回值

# python 中调用：cursor.callproc('p2',(2,5,0)) # callproc 在执行该存储过程的时候会把参数做转换： @_p2_0=2, @_p2_1=5, @_p2_2=0

# 如果 存储过程中是查询语句，利用相应的方法去获取相应的结果：print(cursor.fetchall())

# 查看返回结果cursor.execute('select @_p2_2')print(cursor.fetchone())

应用程序与数据库结合使用的三种方式：

方式一：

MySQL：编写存储过程

Python：调用存储过程

方式二：

Python：编写纯生SQL

MySQL：啥也不干

方式三：

Python：ORM

MySQL：啥也不干

事务：

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性

示例：

# 建表create table user(

idint primary keyauto_increment,

namechar(32),

balanceint);

# 插入数据insert into user(name,balance)values('wsb',1000),

('egon',1000),

('ysb',1000);

#原子操作

starttransaction; # 开启事务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元commit;

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

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; # 上述 update 操作的数据只是保存在内存中，此时 rollback 能让数据回滚到最初的状态；commit之后 rollback 就没有作用了commit; # 想让内存的数据保存到 数据库中，则需要 commitmysql> select * from user;+----+------+---------+

| id | name | balance |

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

| 1 | wsb | 1000 |

| 2 | egon | 1000 |

| 3 | ysb | 1000 |

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

rows in set (0.00 sec)

使用：

#思路：

delimiter//

create procedurep4(

out statusint)BEGIN

1. 声明如果出现异常则执行{set status = 1;rollback;

}

开始事务--由秦兵账户减去100

--方少伟账户加90

--张根账户加10

commit;

结束set status = 2;END //delimiter ;

#实现

delimiter//

create PROCEDUREp5(

OUT p_return_codetinyint)BEGIN

DECLARE exit handler forsqlexception # 声明异常处理BEGIN

--ERROR

set p_return_code = 1;rollback; # 有异常则回滚END;DECLARE exit handler forsqlwarning # 声明警告处理BEGIN

--WARNING

set p_return_code = 2;rollback; # 有警告则回滚END;

STARTTRANSACTION; # 开启事务DELETE fromtb1;insert into blog(name,sub_time) values('yyy',now());COMMIT;--SUCCESS

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

python的事务开启：

示例一：

#coding=utf-8

importpymysql#添加数据

conn= pymysql.connect(host='localhost', port=3306, user='root', passwd='', db='yyy')

cursor=conn.cursor()try:

insertSQL0="INSERT INTO ACCOUNT2 (name,balance) VALUES ('bart',1000)"insertSQL1="UPDATE account2 set balance=balance-30 WHERE name='alex'"insertSQL2="UPDATE account2 set balance=balance+30 WHERE name='ego'"cursor.execute(insertSQL0)

conn.commit()

cursor.execute(insertSQL1)raise Exception #模拟出现错误

cursor.execute(insertSQL2)

cursor.close()

conn.commit()exceptException as e:

conn.rollback()#回滚

conn.commit()

cursor.close()

conn.close()

示例二：

try:

cursor.execute(sql_1)

cursor.execute(sql_2)

cursor.execute(sql_3)exceptException as e:

connect.rollback()#事务回滚

print('事务处理失败', e)else:

connect.commit()#事务提交

print('事务处理成功', cursor.rowcount) # cursor.rowcount 是一个只读属性，返回执行execute()方法后影响的行数。#关闭连接

cursor.close()

connect.close()