python学习Day49
Day 49
今日内容概要
- 视图
- 触发器
- 事务
- MVCC多版本并发控制
- 存储过程
- 内置函数
- 流程函数
- 索引
- 索引底层原理
- 慢查询优化
今日内容详细
1.视图
'SQL语句的执行结果大多数是一张虚拟表 我们可以基于该表做其他操作'
如果虚拟表(连表操作出来的一张表)需要频繁使用可以保存起来，这就称为"视图"(本质就是一张虚拟表)
创建视图：
create view 视图名 as SQL语句;
create view st as select * from teacher inner join course on teacher.tid = course.teacher_id;
删除视图：
drop view st;
"""
1.在硬盘中视图只有表结构文件，没有表数据文件。
2.视图多了会浪费内存空间
3.当连表的表修改里面数据时,视图表中的数据也会跟着修改，同理修改视图表,连表的表也会变动，一般尽量不要改视图中的数据
4.视图表中不能增、删数据
总结:视图能尽量少用就尽量少用,当视图多的时候会分不清哪个是真正的表
2.触发器
针对表数据的增、改、删自动触发的功能(新增前、新增后、修改前、修改后、删除前、删除后)
"""
语法结构：
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
"""
注意：触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符
所以为了能够完整的写出触发器的代码 需要在开头临时修改SQL语句默认的结束符,结尾再改回去
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') #命令是否执行成功
);
create table errlog (
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);
#创建一个触发器来判断当新插入cmd的数据为'no'时,则在errlog表中插入该输入
delimiter $$ # 将结束符临时换成$$
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
('kevin','0755','ls -l /etc',NOW(),'yes'),
('kevin','0755','cat /etc/passwd',NOW(),'no'),
('kevin','0755','useradd xxx',NOW(),'no'),
('kevin','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;
3.事务
start transaction；
1.什么是事物：
开启一个事物可以包含多条sql语句，这些sql语句要么同时成功要么一个都别成功,这是事物的原子性特点。
2.事物的作用：
保证了数据操作的数据安全性，比如A银行给B银行转账,当A转账成功并扣款后,B断网了没收到转账信息,那这笔钱就不见了。所以为了解决该问题,当A扣钱时B必须加钱,如果没加则回退。
3.事务的四大特征(ACID)
A:原子性 最明显的特点，多条sql语句要么同时成功要么同时失败
C:一致性 一个事物执行完,另一个事物必须保持一致,不能你成功我失败
I:隔离性 一个事物内部的操作数据对并发的事物是隔离的不互相干扰
D:持久性 一个事物一但提交，对数据库的改变就是永久性的不能有其他修改操作
案例：
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
# 开启事务操作(在修改操作前)
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 可回到修改操作的状态
rollback;
# 如果用了rollback后需要重新修改操作,则要再开启事物操作。
# 当确定数据不再变动后输入commit二次确认可以把数据真正刷新到硬盘。
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
rollback;
else:
commit;
扩展知识点(重要)
MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
事务处理中有几个关键词汇会反复出现
事务(transaction)
回退(rollback)
二次确认提交(commit)
保留点(savepoint)
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
创建占位符可以使用savepoint
savepoint sp01;
回退到占位符地址
rollback to sp01;
# 保留点在执行rollback或者commit之后自动释放
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
【脏读】简单可以理解为开启事物修改数据后没有二次确认,但是又有另一个事务来获取数据,获取到的就是内存里已经修改过的数据而不是原本应该在硬盘里的真实数据。
2.read committed(提交读或不可重复度)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
也就是说开启事物修改数据后没有二次确认，但是又有另一个事物来获取数据，获取到的就是修改前硬盘里的数据，但是底层会有一个问题：当保存到硬盘时保存的是谁的数据？不过mysql底层会有一个标志位,标志执行到哪一步
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行。
也就是假如一个事物有5步操作第一步时读取数据有三条，第4步操作发现变成四条数据。
InnoDB和XtraDB通过'多版本并发控制'(MVCC)及'间隙锁策略'解决该问题
4.serializable(可串行读)
强制多个事务串行执行,排队执行。效率太低很少使用该级别
事务日志可以帮助提高事务的效率
存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘
3.1.MVCC多版本并发控制
MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较
例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
username create_version delete_version
jason 1
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
username create_version delete_version
jason 1 2
jason01 2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
username create_version delete_version
jason01 2 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""
4.存储过程
存储过程：
类似于python中的自定义函数，可以自己写一些方法后续可以反复调用
delimiter 临时结束符
create procedure 起方法名(参数,参数)
begin
sql语句;
end 临时结束符
delimiter ;
#调用存储的语句
call 方法名字();
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname from cmd where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
"""
查看存储过程具体信息
show create procedure p1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure p1;
"""
5.内置函数
内置函数只可以在sql语句中使用
"ps:可以通过 help 函数名 查看帮助信息!"
# 1.移除指定字符
Trim、LTrim、RTrim
select trim(' abc '); 去掉空格
# 2.大小写转换
Lower、Upper
select upper('abc'); 转换成大写
# 3.获取左右起始指定个数字符
Left、Right
select left('abcde',2); 切两个字符
# 4.返回读音相似值(对英文效果)
Soundex
select * from t1 where soundex(name)=soundex('jason');
eg:
客户表中有一个顾客登记的用户名为J.Lee
如果输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
select * from t1 where soundex(name)=soundex('J.Lee');
# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
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');#把时间字段截取成年月格式
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
6.流程函数
# 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 proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
7.索引
什么是索引：
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能
总结：加快对数据的查找
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
primary key #主键 非空且唯一
unique key #唯一键 唯一
index key #索引键 无约束条件
上述的三种键在数据查询的时候使用都可以加快查询的速度
primary key、unique key除了可以加快数据查询还有额外的限制
index key只能加快数据查询 无其他限制(一般额外加的都是这个)
索引加快数据查询的含义：
索引的存在可以加快数据的查询 但是不能过多会减慢数据的增删
索引的相关概念
1.聚集索引(primary key)
指的是表的主键,innodb引擎规定一张表必须要有主键
特点：叶子结点放的一条条完整的记录
2.辅助索引(unique,index)
查询数据的时候不可能都是用id作为筛选条件,也可能会用name，pwd等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(辅助索引中并不存放任何数据,只有要查找的主键值,根据主键值去主目录中才可以拿到真正的数据)
数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据
3.覆盖索引
只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select name from user where name='jason';
4.非覆盖索引
虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
select age from user where name='jason';
7.1.索引底层原理
树:是一种数据结构 主要用于优化加快数据查询的操作
二叉树：两个分支
B树(B-树)：(不合理)
叶子节点可以有多个分支 其他节点最多只能两个分支
所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
B+树：
只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
B*树：
在树节点添加了通往其他节点的通道 减少查询次数
7.2.慢查询优化
explain select * from 表名 where 条件;
常见的索引扫描类型:
1）index
2）range(最少要到达的级别)
3）ref
4）eq_ref
5）const
6）system
7）null
从上到下，性能从最差到最好，我们认为至少要达到range级别
作业
1.整理今日内容及博客
2.查阅博客及资料研究多种基于索引查询但是无法提升查询速度的SQL操作(重要)、数据结构之各种树
3.MySQL周总结、预习前端内容
浙公网安备 33010602011771号