MySQL六
今日内容概要
主题:理论为主
- 视图
- 触发器
- 事务
- MVCC多版本并发控制
- 存储过程
- 内置函数
- 获取当前时间的语句
- 流程控制
- 索引
- 索引分类
- 添加索引语句
- 慢查询优化
- 索引底层原
视图
将两张经常使用的表连在一起便于查看 但表内数据只能查询无法进行其他操作
SQL语句的执行结果是一张虚拟表 我们可以基于该表做其他操作
如果这张虚拟表需要频繁使用 那么为了方便可以将虚拟表保存起来 保存起来之后就称之为"视图"(本质就是一张虚拟表)
create view 视图名 as SQL语句;
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
"""
1、在硬盘中，视图只有表结构文件，没有表数据文件
2、视图通常是用于查询，尽量不要修改视图中的数据
总结:视图能尽量少用就尽量少用
"""
触发器
针对表数据的增、改、删自动触发的功能(增前、增后、改前、改后、删前、删后)
"""
语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
"""
注意触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符
所以为了能够完整的写出触发器的代码 需要临时修改SQL语句默认的结束符
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
);
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;
事务
事务的作用保证对数据操作的安全性
事务的四大特征(ACID)
# A：原子性 最小单位不可分割
# c：一致性 事务执行完了产生的结果必须是一致的不能一个改变了 另一个没动与原子性密切相关
# I：隔离性 多个事务同时执行互相不影响
# D：持久性 事务成功了确认之后修改的数据是永久的其他的故障或操作对其没有影响
'''
ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事（transaction）
是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、
一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。在数据
库系统中，一个事务是指：由一系列数据库操作组成的一个完整的逻辑过程。例如银行转帐
，从原账户扣除金额，以及向目标账户添加金额，这两个数据库操作的总和，构成一个完整的
逻辑过程，不可拆分。这个过程被称为一个事务，具有ACID特性。ACID的概念在ISO/IEC
10026-1:1992文件的第四段内有所说明。 [1] ''
'''
1.Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不
完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务
开始前的状态，就像这个事务从来没有执行过一样。
2.Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表
示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可
以自发性地完成预定的工作。
3.Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性
可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包
括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable
read）和串行化（Serializable）。
4.Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务的开启每次都需要输入开启命令
开启 start transaction ;
回退 rollback；
确认 commit；
保留节点 savepoint 此方法很少使用 违反事物原子性和一致性
案列
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;
# 开启事务之后，只要没有执行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;
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
创建占位符可以使用savepoint
savepoint sp01;
回退到占位符地址
rollback to sp01;
# 保留点在执行rollback或者commit之后自动释放
'''在事务执行的时候未提交时改的是内存中的数据真正的数据还没有修改'''
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新
的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发
控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
事务日志可以帮助提高事务的效率
存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘
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值，这表示在事务开始之后这行记录才被删除。
"""
存储过程
类似于python中的自定义函数
delimiter 临时结束符
create procedure 名字(参数,参数)
begin
sql语句;
end 临时结束符
delimiter ;
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname 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;
"""
内置函数
"ps:可以通过help 函数名 查看帮助信息!"
# 1.移除指定字符
Trim、LTrim、RTrim
# 2.大小写转换
Lower、Upper
# 3.获取左右起始指定个数字符
Left、Right
# 4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
# 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;
3.date_format(字段名，展示的格式)%Y-%m-%d %H:%i:%S
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
获取当前时间的语句
select date_format(now(),'%Y %m %d %H:%i:%S')
select date_ format(now(),时间格式)
可以使用的格式有：
项目 Value
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值（00-31）
%e 月的天，数值（0-31）
%f 微秒
%H 小时（00-23）
%h 小时（01-12）
%I 小时（01-12）
%i 分钟，数值（00-59）
%j 年的天（001-366）
%k 小时（0-23）
%l 小时（1-12）
%M 月名
%m 月，数值（00-12）
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒（00-59）
%s 秒（00-59）
%T 时间, 24-小时（hh:mm:ss）
%U 周（00-53）星期日是一周的第一天
%u 周（00-53）星期一是一周的第一天
%V 周（01-53）星期日是一周的第一天，与 %X 使用
%v 周（01-53）星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天（0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位
mysql> select date_format(now(),'%b %d %Y %h:%i %p')
-> select date_format(now(),'%m-%d-%Y')
-> select date_format(now(),'%d-%b-%y')
-> select date_format(now(),'%d %b %Y %T:%f')
流程控制
# 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 ;
索引
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
primary key
unique key
index key
上述的三种键在数据查询的时候使用都可以加快查询的速度
primary key、unique key除了可以加快数据查询还有额外的限制
index key只能加快数据查询 本身没有任何的额外限制
真正理解索引加快数据查询的含义
索引的存在可以加快数据的查询 但是会减慢数据的增删
索引分类
#===========B+树索引（等值查询与范围查询都快）二叉树->平衡二叉树->B树->B+树
#===========HASH索引(等值查询快，范围查询慢)将数据打散再去查询
#===========FULLTEXT：全文索引 （只可以用在MyISAM引擎）通过关键字的匹配来进行查
询，类似于like的模糊匹配like + %在文本比较少时是合适的但是对于大量的文本数据检索会非
常的慢全文索引在大量的数据面前能比like快得多，但是准确度很低百度在搜索文章的时候使
用的就是全文索引，但更有可能是ES
不同的存储引擎支持的索引类型也不一样
- InnoDB存储引擎
支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索引；
- MyISAM存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
- Memory存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
因为mysql默认的存储引擎是innodb，而innodb存储引擎的索引模型/结构是B+树
添加索引语句
1、索引分类：
主键索引
普通索引*****
唯一索引
2、添加索引：
#创建索引
alter table test add index index_name(name);
#创建索引
create index index_name on test(name);
#查看索引
desc table;
#查看索引
show index from table;
#删除索引
alter table test drop key index_name;
#添加主键索引（略）
#添加唯一性索引
alter table student add unique key uni_xxx(xxx);
#查看表中数据行数
select count(*) from city;
#查看去重数据行数
select count(distinct name) from city;
慢查询优化
explain命令使用方法
mysql> explain select name,countrycode from city where id=1; 查看当前索引等级
全表扫描在explain语句结果中type为ALL
生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描
2.索引扫描
2.1 常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）nul
索引底层原理
树:是一种数据结构 主要用于优化数据查询的操作
二叉树:两个分支
B树(B-树)、B+树、B*树
B树:
除了叶子节点可以有多个分支 其他节点最多只能两个分支
所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
B+树:
只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
B*树
在树节点添加了通往其他节点的通道 减少查询次数
浙公网安备 33010602011771号