mysql 触发器很慢_mysql之视图、触发器、事物、存储过程、函数、流程控制、索引与慢查优化...

数据库之视图、触发器、事物、存储过程、函数、流程控制、索引与慢查优化

一、视图

灵魂三问

1.什么是视图?

将sql语句的查询得到的虚拟表称为视图,视图可以保存下来,下次直接使用这个视图,

2.为什么要用视图?

如果我们频繁使用这个查询结果,可以直接调用视图,这样可以节省重复拼接的时间

3.如何使用视图?

创建视图语法:

create view 视图名 as sql语句

举例:

create view teacher2course as

select * from teacher inner join course on teacher.tid = course.teacher_id;

注意:

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

2.视图通常是用来查询的,尽量不要修改视图中的数据

drop view teacher2course;

思考:开发过程中会不会去使用视图？

不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能

二、触发器

1.什么是触发器

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器，指的就是达到某个条件就会自动执行某个代码

2.为什么要用触发器

触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码

3.如何使用触发器

创建触发器语法:

关键字: trigger

create trigger 触发器的名字 before/after insert/updata/delete on 表名 for each row

begin

sql代码...

end

触发器命名规律

tri_before_insert_t1

# 针对插入

create trigger tri_after_insert_t1 after insert on 表名 for each row

begin

sql代码。。。

end

create trigger tri_befor_insert_t2 before insert on 表名 for each row

begin

sql代码。。。

end

# 针对删除

create trigger tri_after_delete_t1 after delete on 表名 for each row

begin

sql代码。。。

end

create trigger tri_before_delete_t2 before delete on 表名 for each row

begin

sql代码。。。

end

# 针对修改

create trigger tri_after_update_t1 after update on 表名 for each row

begin

sql代码。。。

end

create trigger tri_before_update_t2 before update on 表名 for each row

begin

sql代码。。。

end

举例:

CREATE TABLE cmd (

id INT PRIMARY KEY auto_increment,

USER CHAR (32),

priv CHAR (10),

cmd CHAR (64),

sub_time datetime, #提交时间

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

);

CREATE TABLE errlog (

id INT PRIMARY KEY auto_increment,

err_cmd CHAR (64),

err_time datetime

);

# 知识点补充:

sql语句默认是以分号结束

但是你可以修改结束符

delimiter $$ 是临时修改 只在当前库有效 并且当前窗口一旦关闭再次进入失效

delimiter $$ # 将mysql默认的结束符由;换成$$

create trigger tri_after_insert_cmd after insert on cmd for each row

begin

if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象

insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);

end if;

end $$

delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志

INSERT INTO cmd (

USER,

priv,

cmd,

sub_time,

success

)

VALUES

('egon','0755','ls -l /etc',NOW(),'yes'),

('egon','0755','cat /etc/passwd',NOW(),'no'),

('egon','0755','useradd xxx',NOW(),'no'),

('egon','0755','ps aux',NOW(),'yes');

# 查询errlog表记录

select * from errlog;

# 删除触发器

drop trigger tri_after_insert_cmd;

三、事物

1.什么是事物

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

要么一个都别想成功，称之为事务的原子性(不可分割性)

2.为什么要用事物

保证了对数据操作的安全性,要么操作都成功,要么操作都失败

案例:用交行的卡操作建行ATM机给工商的账户转钱

事物支持回滚操作:

一旦数据操作的不符合预期 可以回滚到上一个状态

开启事务

start transaction;

回滚

rollback

确认

commit

即事物在确认(commit)前可以进行回滚操作

事物的四大特性:

原子性、一致性、隔离性、持久性

简称ACID特性

原子性(atomicity):

一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性(consistency):

事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性(isolation):

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性(durability):

持久性也称永久性(permanence)，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

3.如何使用事物

create table user(

id int primary key auto_increment,

name char(32),

balance int

);

insert into user(name,balance)

values

('wsb',1000),

('egon',1000),

('ysb',1000);

# 修改数据之前先开启事务操作

start transaction;

# 修改操作

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元

# 回滚到上一个状态

rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘

commit;

"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

# 站在python代码的角度，应该实现的伪代码逻辑，

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操作的人也能够通过调用该存储过程来操作数据库

关键字：procedure

2.为什么要使用