本文详细介绍了MySQL中的视图、触发器和事务。视图是虚拟表，根据SQL语句获取数据并命名，方便后续查询，但存在效率问题。创建视图使用`CREATE VIEW`，修改使用`ALTER VIEW`，删除使用`DROP VIEW`。触发器在特定操作（增、删、改）前后执行自定义行为，如在插入或删除记录时记录错误。事务确保一组SQL操作的原子性，遵循ACID原则。文章还涵盖了创建、使用和删除触发器以及事务的实例。
视图
视图是一个虚拟表(非真实存在)，其本质是【根据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;
142
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
