day49——视图、触发器、事务...
今日内容
- 视图
- 触发器
- 事务
- 存储过程
- 内置函数
- 流程控制
- 索引理论
除了事务需要掌握,其他了解即可
一、视图
-
什么是视图
试图就是将查询得到的虚拟表保存下来,后期需要重复操作的表的时候直接使用试图即可,试图的本质也是表。
-
如何操作
固定语法:
create view 表名 as 虚拟表查询sql语句
具体操作:
create veiw teacher2course as select * from teacher inner join course on teahcer.tid=course.theacher_id;
-
注意:
1) 创建视图 在硬盘上只会有表结构文件,而没有表数据文件(真正的数据还是来源于之前的表)
2) 创建视图一般是用来查询,里面的数据最好不要进行修改,如果修改原来的表的数据也会跟着改变,会影响原来的表。
-
视图的使用频率
视图的使用频率不高,几乎不怎么用,因为视图创建多了,表不好维护。
二、触发器
-
什么是触发器
满足对表进行增、删、该、查的情况下,自动触发的功能
-
作用
使用触发器能帮助我们实现监控,记录日志...
-
触发器触发的六种情况
增前 增后 改前 改后 删前 删后(查询不考虑)
-
基本语法
伪代码:
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin sql语句 end
ps:触发器的名字应见名知意
-
具体使用
# 针对增 create trigger tri_before_insert_t1 before insert on t1 for each row begin sql语句 end create trigger tri_after_insert_t1 after insert on t1 for each row begin sql语句 end """针对删除和修改 书写格式一致""" ps:修改MySQL默认的语句结束符 只作用于当前窗口 delimiter $$ 将默认的结束符号由;改为$$ delimiter ; # 案例 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 ); """ 当cmd表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据 NEW指代的就是一条条数据对象 """ delimiter $$ create trigger tri_after_insert_cmd after insert on cmd for each row begin if NEW.success = 'no' then insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time); end if; end $$ delimiter ; # 朝cmd表插入数据 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('jason','0755','ls -l /etc',NOW(),'yes'), ('jason','0755','cat /etc/passwd',NOW(),'no'), ('jason','0755','useradd xxx',NOW(),'no'), ('jason','0755','ps aux',NOW(),'yes'); # 删除触发器 drop trigger tri_after_insert_cmd;
针对修改和删除,书写格式和增一致
三、 事务
-
何为事务
包含多个sql语句的操作序列,这些sql语句要么都执行,要么都不执行。
-
事务的作用
保证数据操作的安全性
例:
用户A支付宝转给用户B100元,用户A账号上减掉100元,这个时候网络出现问题,用户B的账号上并没有加100元,转账出错;将用户A减钱和用户B加钱做成事务,只有在A减钱且B加钱的时候,转账才能成功,否则两者都不执行,避免出错。
-
事务的四大特性ACID
1) 原子性A(Atomicity):
事务为一个不可分割的整体,包含多个操作,要么同时成功,要么同时失败。
2) 一致性C(consistency):
事务必须 是数据库从一个一致性的状态变为另一个 一致性的状态,一致性和 原子性密切相关。
3) 隔离性I(Isolation):
每个事件的执行都不受其他事件的影响
(即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的,并发执行的事务之间也是互相不干扰的)
4) 持久性(Durability)D:
持久性也叫永久性,即事务一但提交成功那么永久有效,接下来的其他操作或者故障对其都不会有任何的影响。
-
如何用事务
关键字:
# 开启事务 start tansaction # 回滚 (回到事务执行之前的状态) rollback # 确认 (确认后无法回滚) commit
事务模拟转账:
# 创建表用于存储用户和对应金额 create table user( id int primary key auto_increment, name char (16), balance int); # 插入数据(用户名、余额) insert into user(name,balance) values ('jason',1000), ('egon',1000), ('tank',1000); # 开启事务 start transaction; # 事务所包含的多条语句 update user set balance=900 where name='jason'; update user set balance=1010 where name='egon'; update user set balance=1100 where name='tank'; # 确认 commit; # 验证 select balance from user; ''' +---------+ | balance | +---------+ | 900 | | 1010 | | 1100 |'''
-
总结
当你向让多条sql语句保持一致性时,即它们同时成功或者同时失败,就可以使用事务,事务可以保证操作数据的安全。
-
四、存储过程
存储过程好比与python的自定义函数,存储过程内部包含了一系列可以执行的sql语句(函数体代码),存储过程存放在MySQL服务端中(函数存放在名称空间中),可以直接调用存储过程触发内部的sql语句的执行(函数加括号调用),并且创建存储过程时可以指定形参(定义函数指定形参)。
-
基本使用:
# 伪代码 create procedure 存储过程的名字(形参1,形参2,...) begin sql语句 end # 调用 call 存储过程的名字();
-
三种开发模型
1) 第一种
应用程序:程序员写代码开发
MySQL:提前写好存储过程, 供应用程序使用
好处:开发效率提高了, 执行效率也提高了
缺点:考虑到人为因素,跨部门沟通的问题,后续的存储过程的扩展性差
2) 第二种
应用程序:程序员写代码开发之外 设计到数据库操作也自己动手写
优点:扩展性很高
缺点:开发效率降低;编写sql语句太过繁琐 而且后续还需要考虑sql优化的问题3) 第二种
应用程序:只写程序代码 不写sql语句 基于别人写好的操作MySQL的python框架直接调用操作即可 ORM框架
优点:开发效率比上面两种情况都要高
缺点:语句的扩展性差 可能会出现效率低下的问题 -
存储过程具体演示
delimiter $$ create procedure p1( in m int, # 只进不出 m不能返回出去 in n int, out res int, #该形参可以返回出去 ) begin select tname from teacher where tid>m and tid<n; set res=666; #将变量修改,用来表示当前的存储过程代码确实执行了 end $$ delimiter ; # 针对形参res 不能直接传数据,应该传一个变量名 # 定义变量 set @ret = 10; select @ ret;
-
pymysql 模块中调用存储过程
import pymysql conn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'day48', charset = 'utf8', autocommit = True ) cursor = conn.cursor(pymysql.cursors.DictCursor) # 调用存储过程 cursor.callproc('p1',(1,5,10)) """ @_p1_0=1 @_p1_1=5 @_p1_2=10 """ # print(cursor.fetchall()) cursor.execute('select @_p1_2;') print(cursor.fetchall())
-
五、 函数
上面提到存储过程类似于自定义函数,那么MySQL中的函数即相当于python中内置函数
#('jason','0755','ls -l /etc',NOW(),'yes') Now为函数
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
六、流程控制
# if 判断
delimiter //
create procedure proc_if ()
begin
declare i int default 0;
if i = 1 then
select 1;
elseif i =2 then
select 2;
else
select 7;
end if;
end //
delimiter ;
# while 循环
delimiter //
create procedure pro_while ()
begin
declare num int;
set num =0;
while num<10 do
select
num;
set num = num+1;
end while;
delimiter ;
七、索引
ps:数据都是存在与硬盘上的,查询数据不可避免的需要进行IO操作
索引:就是一种数据结构,类似于书的目录。意味着以后在查询数据的应该先找目录再找数据,而不是一页一页的翻书,从而提升查询速度降低IO操作
索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构
- primary key
- unique key
- index key
注意foreign key不是用来加速查询用的是用于表与表之间建立关系的,不在我们的而研究范围之内,上面的三种key,前面两种除了可以增加查询速度之外各自还具有约束条件,而最后一种index key没有任何的约束条件,只是用来帮助你快速查询数据
本质
通过不断的缩小想要的数据范围筛选出最终的结果,同时将随机事件(一页一页的翻)变成顺序事件(先找目录、找数据),也就是说有了索引机制,我们可以总是用一种固定的方式查找数据
一张表中可以有多个索引(多个目录)
索引虽然能够帮助你加快查询速度但是也有缺点
"""
1 当表中有大量数据存在的前提下 创建索引速度会很慢
2 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
"""
索引不要随意的创建!!!
b+树
"""
只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据 仅仅是用来指路的
树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
一个磁盘块存储是有限制的
为什么建议你将id字段作为索引
占得空间少 一个磁盘块能够存储的数据多
那么就降低了树的高度 从而减少查询次数
"""
聚集索引(主键primary key)
"""
聚集索引指的就是主键
Innodb 只有两个文件 直接将主键存放在了idb表中
MyIsam 三个文件 单独将索引存在一个文件
"""
辅助索引(unique , index)
查询数据的时候不可能一直使用到主键,也有可能会用到name,password等其他字段那么这个时候你是没有办法利用聚集索引。这个时候你就可以根据情况给其他字段设置辅助索引(也是一个b+树)
"""
叶子节点存放的是数据对应的主键值
先按照辅助索引拿到数据的主键值
之后还是需要去主键的聚集索引里面查询数据
"""
覆盖索引
在辅助索引的叶子节点就已经拿到了需要的数据
# 给name设置辅助索引
select name from user where name='jason';
# 非覆盖索引
select age from user where name='jason';
测试索引是否有效
**准备**
```mysql
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程,实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
```
``` mysql
# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;
# 给id做一个主键
alter table s1 add primary key(id); # 速度很慢
select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason' # 速度仍然很慢
"""
范围问题
"""
# 并不是加了索引,以后查询的时候按照这个字段速度就一定快
select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;
alter table s1 drop primary key; # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason'; # 又慢了
create index idx_name on s1(name); # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason' # 仍然很慢!!!
"""
再来看b+树的原理,数据需要区分度比较高,而我们这张表全是jason,根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';
# 这个会很快,我就是一根棍,第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性
# 区分度低的字段不能建索引
drop index idx_name on s1;
# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3; # 快了
select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算
drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作,mysql会从左到右先找区分度比较高的索引字段,先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速
drop index idx_name on s1;
# 给name,gender这种区分度不高的字段加上索引并不难加快查询速度
create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多,然后还要去比较其他字段
drop index idx_id on s1
create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉
```
#### 联合索引
```mysql
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
# 如果上述四个字段区分度都很高,那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3;
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3;
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3;
# 带来的问题是所有的字段都建了索引然而都没有用到,还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id); # 最左匹配原则,区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快
```
慢查询日志
设定一个时间检测所有超出该时间的sql语句,然后针对性的进行优化!