Day 48 MySQL

Day 48 MySQL

1、视图

SQL语句 的执行结果是 一张虚拟表 我们可以基于该表做其他操作
如果这张表虚拟表 需要频繁使用 那么为了方便可以将虚拟表保存起来 保存起来之后 就称之为视图 （本质就是一张表）

create view 视图 as SQL 语句:

create view teacher2 course as select * from teacher inner join course on teacher.tid = course.teacher_id;
1
2
3

1. 在硬盘中，视图只有表结构文件，没有表数据文件
2. 视图通常用于查询，经量不要修改视图中的数据

总结：视图能尽量少用就经量少用

2、触发器

针对表数据的增、改、删 自动触发的功能（增前、增后、改前、改后、删前、删后）

语法结构

						#前 后              增 改 删          对该表做触发器
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin
	sql 语句  #执行 取决于上述 表 是否做了 上述操作
end
1
2
3
4

注意触发器内部的 SQL 语句需要用到分号 凡是分号又是SQL 语句默认的结束符
所以 为了能够 完整的写出触发器 的代码 需要临时 修改 SQL语句 默认的结束符

delimiter $$
编写需要用到分号的各种语句
delimiter ;
1
2
3

案例

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
);

#触发器
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
    ('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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

3、事物

事物的四大特性（ACID）

• A：原子性
  • 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• C：一致性
  • 事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括两方面的内容，分别是约束一致性和数据一致性。
• I：隔离性
  • 隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。
• D：持久性
  • 持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，并不会被回滚，后续的操作或故障不应该对其有任何影响，。

3.1、实操 演示

数据准备

create table user(
	id int primary key auto_increment,
    name char(32),
    balance int
);

insert into user(name,balance)
values
('jaosn',1000),
('kevin',1000),
('tank',1000);
1
2
3
4
5
6
7
8
9
10
11

修改 数据之前 先开启事务所

start transaction;
1

修改操作

update user set balance=900 where name = 'jaosn'; #买支付100元
update user set balance = 1010 where name = 'kevin'; # 中介拿走10元
update user int balance = 1090 where name = 'tank'; #卖家拿到90 元
1
2
3

回滚到上一个状态

rollback;
1

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

commit;
1

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;
1
2
3
4
5
6
7
8

3.2、事物扩展知识点（重要）

MySQL提供两种 事务类型存储引擎INNoDB 和NDB cluster 及第三方XtraDB、PBXT

事物处理种有几个关键词会反复出现

事物（transaction）
回退（rollback）
提交（commit）
保留点（savepoint）
为了支持回退部分事物处理，必须能在事物处理块种合适的位置放置占位符，这样 如果需要回退可以回退到某个占位符（保留点）
创建占位符可以使用savepoint
savepoint sp01;

回退到占位符地址
rollback to sp01;

保留点在执行rollback 或者 commit之后自动释放

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB 支持所有隔离界别

set transaction isolation leval 级别
1

read uncomitted（未提交读） 【脏读】
事物中的修改即使没有提交，对其他事物也都是可见的，事物可以读取未提交的数据，这一现象也称之位脏读

read committed（提交读） 【不可重复读】
大多数 数据库系统默认的隔离级别
一个事物从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做不可重复读

repeatable read（可重复读） MySQL默认隔离界别
它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
能够解决“脏读”问题，但是无法解读“幻读”

phantom Read幻读 现象
所谓幻读指的是当某个事物在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生换行，InnoDB和XtraDB通过多版本并发控制（MVCC）及间隙繁琐策略解决该问题

serializable（可串行读）
强制事务串行执行，很少使用该级别

事务日志可以帮助提高事务的效率
存储引擎在修改表的数据时 只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，>而不用每次都将修改的数据本身持久到硬盘
事务日志采用的是追加方式 因此写日志操作 是磁盘上一小块区域内的顺序IO而不像随机IO需要在>磁盘的多个地方 移动磁头所采用的事务日志的方式相对来说要快的多
事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是 这样实>现的，通常称之位预写式日志 修改数据需要写两次磁盘

3.3、MVCC多版本并发控制

MVCC只能在 read committed（提交读）、repeatable read（可重复读）两种隔离级别下工作，其他两个不兼容（read uncommitted：总是读取最新 serializable：所有的行都加锁）

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间（或删除时间）本质是系统版本号

一个列保存了行的过期时间（或删除时间）本质是系统版本号
每开始一个新的事物版本号都会自动递增，事物开始时刻的系统版本号会作为事物的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条 数据的时候，我们默认事物id为1，实际是这样存储的

可以看到我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事物id
然后我们呢将jason修改为jason01，实际存储是这样的

可以看到，update的时候，会先将之前的数据delete_version 标记为当前新的事物id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事物id

当我们删除数据的时候，实际存储是这样的

由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：

1. 当事物id要大于或等于当前的create_version 值，这表示在事物开始签这行数据已经存在了。
2. 当前事务id要小于delete_version 值，这表示在事务开始之后这行记录才被删除。

4、存储过程

类似于python中的自定义函数

语法格式

delmiter 临时结束符
create procedure 名字(参数，参数)
begin
	sql语句;
end 临时结束符
delimiter ；
1
2
3
4
5
6

delimiter $$  #临时修改 结束符
create procedure p1(
	in m int, in  # in表示这个参数必须只能传入不能返回出去
    in n int， in 
    out res int  # out表示这个参数可以被返回出去，还有一个inot 表示即可传入也可以被返回出去
)
begin
	select tanme from teacher where tid > m and tid < n;
	set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ; #改回结束符  

#针对 res 需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1.5,@res) 调用
select @res 查看

查看存储过程具体信息
	show create procedure pro1;
查看你所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

5、内置函数

可以通过 help 函数名 查看帮助信息

移除指定字符

select Trim()  # 默认移除两边空额

select LTrim  # 默认移除右边空格

select RTrim # 默认移除左边空格
1
2
3
4
5

大小写转换

select Lower 全部变小写

select Upper 全部变大写
1
2
3

获取左右起始 指定个数字符

select Left 从左边开始获取指定个数字符

select Right  从右边开始 获取指定个数字符
1
2
3

返回读音相似值（对英文效果）

where Soundex(name) = Soundex('')  # 获取发音相似的数据
1

• 用户 输入错误名字 可以尝试使用soundex 匹配发音来查找类似的数据

5.1、日期格式 ：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
	('第一篇','2015-03-01 11:31:21'),
	('第二篇','2015-03-11 16:31:21'),
	('第三篇','2016-07-01 10:21:31'),
	('第四篇','2016-07-01 10:21:31'),
	('第五篇','2016-07-23 10:11:11'),
	('第六篇','2016-07-25 11:21:31'),
	('第八篇','2017-03-01 17:32:21'),
	('第九篇','2018-03-01 18:31:21');

select date_format(sub_time,'%Y-%m'),count(id)from blog group by date_format(sub_time,'%Y-%m'); #按年月分组 获取 年月 的文章id计数

# 按年月日 筛选 获取
select * from blog where Date(sub_time) = '2015-03-01';

#按年月筛选
select * from blog where Year(sub_time)=2016 AND Month(sub_time)=07;

# 跟多 日期 处理拓展
adddate		#增加一个日志
addtime		#增加一个时间
datediff	#计算两个日期差值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

6、流程控制

if条件语句

delimiter //  #临时修改结束符
create procedure proce_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 ;  #改回结束符
1
2
3
4
5
6
7
8
9
10
11
12
13

while 循环

delimiter //
create procedure proc_while()
begin
	declare num int;
	set num = 0;
	whele num < 10 do
		select
			num ;
		set num = num + 1;
	end whtle ;
	
end //
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13

7、索引

索引 就好比一本书的目录，它能让你更块的找到自己想要的内容
让获取的数据更有目的性，从而提高数据库检索数据的性能

索引在MySQL中也叫做键 是存储引擎用于快速找到记录的一种数据结构

• primary key 主键索引
• unique key 唯一索引
• index key 普通索引

上述的三种键 在数据查询的时候使用都可以加快查询的速度
primary key unique key 除了可以加快数据的查询还有额外的限制
index key 只能加快数据查询 本身没有任何的额外限制

真正理解索引 加快数据查询的含义
索引的存在可以加快数据的查询 但是会减慢数据的增删

索引相关概念
基于课上讲解自行总结

7.1、索引底层原理

树：是一种数据结构 主要用于数据查询的操作

二叉树：两个分支

B树（B-树）、B+树、B*树

• B树：

  • 除了叶子节点可以有多个分支 其他节点最多只能两个分支
  • 所有节点都可以直接存放完整数据（每一个数据块是有固定大小的）

• B+树：

  • 只有叶子节点存放真正的数据 其他节点只存主键值（辅助索引值）叶子节点也存在通道
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ANX3urZV-1661071361903)(C:\Users\82576\AppData\Roaming\Typora\typora-user-images\image-20220821151720073.png)]

• B*树：

  • 在树节点添加了通往其他节点的通道 减少查询次数

• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v3IbOAU8-1661071361905)(C:\Users\82576\AppData\Roaming\Typora\typora-user-images\image-20220821151500293.png)]

8、慢查询优化

explain 查看SQL语句 等级
索引扫描等级

1. index
2. range
3. ref
4. eq_ref
5. const
6. system
7. null

从上到下，性能从最差到最好，我们认为只好啊要达到range级别

使用方式

 explain select name from d5 where id=1;
1

全盘扫描：在explain 语句结果为ALL

什么时候出现全盘扫描

1. 业务确实需要获取所有数据
2. 不走索引 导致了全盘扫描
   1. 没有索引
   2. 索引创建有问题
   3. 语句有问题

生产中，MySQL在使用全表扫描时的性能是极差的，所以MySQL尽量避免出现全表扫描