mysql视图执行原理_mysql之视图，存储过程，触发器，事务

视图

视图是一个虚拟表(非真实存在)，其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用。

1.创建视图

创建视图语法

CREATE VIEW 视图名称 AS SQL语句

准备数据和表

-- ========================

-- -- 创建部门表

create table dep(

id int primary key auto_increment,

name char(32)

);

-- 创建用户表

create table user(

id int primary key auto_increment,

name char(32),

dep_id int,

foreign key(dep_id) references dep(id)

);

-- 插数据

insert into dep(name) values('技术部'),('销售'),('财务部');

insert into user(name,dep_id) values ('egon',1),

('alex',2),

('jing',3);

创建视图

create view user_dep_view as select dep_id,dep.name as dep_name, user.name as user_name from dep inner join user on user.dep_id=dep.id

查询视图

--对于单表创建的视图来说是可以修改的，并且原来表的也就更改了。

create view dep_view as select * from dep where id=3;select * fromdep_view;update dep_view set name='综合部' where id=3;commit;select * fromdep;insert into dep_view values(4,'人文部');commit;select * fromdep;delete from dep_view where id=3;commit;[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`testmysql`.`user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES`dep` (`id`))--对于多表联合创建的视图是不可以修改的

insert into user_dep_view VALUES (5,'egon','人文部'); --会报错[Err] 1394 - Can not insert into join view 'testmysql.user_dep_view'without fields listDELETE from user_dep_view where dep_id = 1; --会报错[Err] 1395 - Can not delete from join view 'testmysql.user_dep_view'

2.修改视图

语法：ALTER VIEW 视图名称 AS SQL语句

alter view dep_view as select * from dep where id=4;

select * from dep_view;

3.删除视图

语法：DROP VIEW 视图名称

drop view dep_view;

select * FROM dep_view;

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

-- 触发器：某种程序触发了工具的运行

-- 触发器不能主动调用，只有触发了某种行为才会调用触发器的执行

-- 插入一条记录就触发一次

--创建语法

create

trigger trigger_name

trigger_time trigger_event

on tbl_name for each row

triggrr_body #主体，就是在触发器里干什么事

trigger_time:{before | after}

trigger_event:{insert | update |detele}

# 插入前

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

准备表

-- # 2.准备表

-- #第一步：准备表

create table cmd_log(

id int primary key auto_increment,

cmd_name char(64), #命令的名字

sub_time datetime, #提交时间

user_name char(32), #是哪个用户过来执行这个命令

is_success enum('yes','no') #命令是否执行成功

);

create table err_log(

id int primary key auto_increment,

cname char(64), #命令的名字

stime datetime #提交时间

);

创建触发器

-- #创建触发器(向err_log表里插入最新的记录)

delimiter $$

create

trigger tri_after_inser_cmd_log

after insert

on cmd_log for each row

BEGIN

if new.is_success = 'no' then

insert into err_log(cname,stime) VALUES(new.cmd_name,new.sub_time);

end if; #记得加分号,mysql一加分号代表结束,那么就得声明一下

END $$

delimiter ; #还原的最原始的状态

-- #创建触发器(向err_log表里插入最旧的记录)

delimiter $$

create

trigger tri_after_del_cmd_log

after delete

on cmd_log for each row

BEGIN

if old.is_success = 'no' then

insert into err_log(cname,stime) VALUES(old.cmd_name,old.sub_time);

end if; #记得加分号,mysql一加分号代表结束,那么就得声明一下

END $$

delimiter ; #还原的最原始的状态

语法小知识：

-- 触发器的两个关键字：new ,old

-- new :表示新的记录

-- old:表示旧的那条记录

-- 什么情况下才往里面插记录

-- 当命令输入错误的时候就把错误的记录插入到err_log表中

-- delimiter 详解

-- 其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。

-- 默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

DELIMITER $$

DROP TRIGGER IF EXISTS `updateegopriceondelete`$$

CREATE

TRIGGER `updateegopriceondelete` AFTER DELETE ON `customerinfo`

FOR EACH ROW BEGIN

DELETE FROM egoprice WHERE customerId=OLD.customerId;

END$$

DELIMITER

-- 其中DELIMITER 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";".

-- 详细解释:

-- 其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。

-- 默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，

-- 那么回车后，mysql将会执行该命令。如输入下面的语句

mysql> select * from test_table;

-- 然后回车，那么MySQL将立即执行该语句。

-- 但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号。

测试

# 测试

insert into cmd_log(cmd_name,sub_time,user_name,is_success) values

('ls -l /etc | grep *.conf',now(),'root','no'),

('ps aux |grep mysqld',now(),'root','yes'),

('cat /etc/passwd |grep root',now(),'root','yes'),

('netstat -tunalp |grep 3306',now(),'egon','no');

commit;

delete from cmd_log where is_success = 'yes';

delete from cmd_log where is_success = 'no';

commit;

select * from err_log;

删除触发器

drop trigger tri_after_insert_cmd;

事务

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

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。一个支持事务(Transaction)的数据库，

必须要具有这四种特性，否则在事务过程(Transaction processing)当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

create table t_user(

id int primary key auto_increment,

name char(32),

balance int

);

insert into t_user(name,balance)

values

('yy',1000),

('xx',1000),

('zz',1000);

mysql> select * from t_user;

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

| id | name | balance |

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

| 1 | yy | 1000 |

| 2 | xx | 1000 |

| 3 | zz | 1000 |

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

#原子操作

start transaction;

update t_user set balance=900 where name='yy'; #买支付100元

update t_user set balance=1010 where name='xx'; #中介拿走10元

update t_user set balance=1090 where name='zz'; #卖家拿到90元

commit;

mysql> select * from t_user;

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

| id | name | balance |

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

| 1 | yy | 900 |

| 2 | xx | 1010 |

| 3 | zz | 1090 |

#恢复表数据

truncate table t_user;

insert into t_user(name,balance)

values

('yy',1000),

('xx',1000),

('zz',1000);

mysql> select * from t_user;

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

| id | name | balance |

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

| 1 | yy | 1000 |

| 2 | xx | 1000 |

| 3 | zz | 1000 |

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

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

start transaction;

update t_user set balance=900 where name='yy'; #买支付100元

update t_user set balance=1010 where name='xx'; #中介拿走10元

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

rollback;

commit;

mysql> select * from t_user;

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

| id | name | balance |

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

| 1 | yy | 1000 |

| 2 | xx | 1000 |

| 3 | zz | 1000 |

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

在存储过程中使用事务

delimiter $$

create procedure p5(

out p_return_code tinyint

)

begin

declare exit handler for sqlexception

begin

set p_return_code = 1;

rollback;

end;

declare exit handler for sqlwarning

begin

set p_return_code = 2;

rollback;

end;

start transaction ;

DELETE FROM tb1; #执行失败

insert into t_user values('zz',1000);

commit;

-- SUCCESS

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

end $$

delimiter ;

调用

#在mysql中调用存储过程

set @res=123;

call p5(@res);

select @res;

#在python中调用

# 有参数存储过程

cursor.callproc('p5', args=('123',)) # 等价于cursor.execute("call p1()")

# 获取执行完存储的参数,参数@开头

cursor.execute("select @_p5_0;") # @p2_0代表第一个参数，即返回值

row_1 = cursor.fetchone()

print(row_1)

# Warning: (1146, "Table 'testmysql.tb1' doesn't exist")

# self._do_get_result()

# {'@_p5_0': 1}

存储过程

一 存储过程介绍

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

使用存储过程的优点：

#1. 用于替代程序写的SQL语句，实现程序与sql解耦

#2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

使用存储过程的缺点：

#1. 程序员扩展功能不方便

二 创建简单存储过程(无参)

delimiter $$

create procedure p6()

BEGIN

INSERT into test1(name,grade) values('egon4',100);

commit;

END $$

delimiter ;

#在mysql中调用存储过程

call p6()

#在python中基于pymysql调用

cursor.callproc('p6')

print(cursor.fetchall())

三 创建存储过程(有参)

对于存储过程，可以接收参数，其参数有三类：

#in 仅用于传入参数用

#out 仅用于返回值用

#inout 既可以传入又可以当作返回值

传入参数

create procedure p7(in_grade int)

BEGIN

select * from test1 where grade < in_grade;

END $$

delimiter ;

#在mysql中调用存储过程

call p7(100) ;

#在python中基于pymysql调用

cursor.callproc('p7',(100,))

print(cursor.fetchall())

out：返回值

delimiter $$

create procedure p8(in in_grade int,out res int)

BEGIN

select * from test1 where grade < in_grade;

set res=1;

END $$

delimiter ;

#在mysql中调用存储过程

set @res=0; #0代表假(执行失败)，1代表真(执行成功)

call p8(3,@res);

select @res;

# 在python中基于pymysql调用

cursor.callproc('p8', (100, 0)) # 0相当于set @res=0

print(cursor.fetchall()) # 查询select的查询结果

cursor.execute('select @_p8_0,@_p8_1;') # @p8_0代表第一个参数，@p8_1代表第二个参数，即返回值

print(cursor.fetchall())

inout:既可以传入又可以返回

delimiter $$

create procedure p9(inout inout_grade int)

BEGIN

select * from test1 where grade < inout_grade;

set inout_grade=60;

END $$

delimiter ;

#在mysql中调用存储过程

set @x=100;

call p9(@x);

select @x;

# 在python中基于pymysql调用

cursor.callproc('p9', (100,)) # 0相当于set @res=0

print(cursor.fetchall()) # 查询select的查询结果

cursor.execute('select @_p9_0;') # @p9_0代表第一个参数，即返回值

print(cursor.fetchall())

四 执行存储过程

-- 无参数

call proc_name()

-- 有参数，全in

call proc_name(1,2)

-- 有参数，有in，out，inout

set @t1=0;

set @t2=3;

call proc_name(1,2,@t1,@t2)

使用pymysql执行

#!/usr/bin/env python

# -*- coding:utf-8 -*-

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

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

# 执行存储过程

cursor.callproc('p1', args=(1, 22, 3, 4))

# 获取执行完存储的参数

cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")

result = cursor.fetchall()

conn.commit()

cursor.close()

conn.close()

print(result)

五 删除存储过程

drop procedure proc_name;