MySQL补充概念

MySQL补充概念

本篇补充一些sql语句的一些概念，包括视图、触发器、事务、存储过程、函数、流程控制、索引概念及结构、慢查询优化。。。

视图

视图是将一条查询sql语句的表结果保存下来方便下次查看。

create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

语法如上，可总结为：

create view 视图名 as 查询sql语句

关于视图需要注意：

• 视图的表只能用来查询不能做其他增删改操作

  因为视图的数据来自于真实的表

• 由于视图的操作与真实表不一致，但是在终端中又看起来像一张表，所以不建议频繁使用造成操作上的混淆，或者对视图的命名应该有一套规范方便与正常的表做区分。

修改休止符

关键字：delimiter

可以通过delimiter修改sql语句的休止符;变成其他符号

delimiter $$   # 修改$$为休止符
select * from a_table$$  # 等同于原本的select * from a_table；
delimiter ;  # 将休止符改回；

这个操作可以应用于以下的功能。

存储过程

类似于python中的自定义的函数，可以将一段sql语句打包封装为一个‘存储过程’，以便下次使用。关键字：procedure

# 无参函数
delimiter $$
create procedure p1()  # 创建存储过程
begin  # 子代码开始
	select * from cmd;  # 因为修改了休止符所以这里sql语句没有结束
end $$  # 子代码结束并以$$结束
delimiter ;  # 改回原本的休止符
call p1()  # 调用

# 有参函数
delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * 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 pro1;
查看所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;
"""

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

函数

类似于python中的内置函数，即sql本身提供的可调用的方法。

以下介绍了处理字符的四种方法和一些处理时间格式数据的方法

可以看成是python中的内置函数

"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;
# 更多日期处理相关函数 
	adddate	增加一个日期 
	addtime	增加一个时间
	datediff计算两个日期差值

触发器*

达到某个条件之后自动触发执行的一段sql。关键字：trigger
在MySQL中更加详细的说明是触发器:针对表继续增、删、改操作能够自动触发。
主要有六种情况:增前、增后、删前、删后、改前、改后。

这个可以联系python中的魔法方法（双下方法）进行理解。

• 创建触发器的sql

  create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
  begin
  	sql语句
  end
  

• 命名规律

  tri_before_insert_t1
  tri_after_delete_t2
  tri_after_update_t2

  tri缩写_前后_操作_针对的表

• 应用举例

  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;
  

• 查看和删除trigger的sql

  # 查看所有的触发器
  show triggers;
  # 删除触发器
  drop trigger tri_after_insert_cmd;
  

事务**

事务的四大特性（ACID）

• A:原子性（atomatic）

  事务中的各项操作是不可分割的整体，要么同时成功要么同时失败

• C:一致性（consistent）
  使数据库从一个一致性状态变到另一个一致性状态

• I:隔离性（isolation）
  多个事务之间彼此不干扰

• D:持久性（durable）
  也称永久性（permanence）指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

1. 准备表

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

2. 开启事务,进行一定的操作

   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元
   

   开启事务后，所有的数据操作实际上是对内存中的数据进行操作，并不会对硬盘中的数据进行实际操作。

3. 回滚rollback和确认commit

   rollback;
   commit;
   

   执行rollback是将硬盘中的数据重新拿一份覆盖掉内存中的数据，这样之前做到操作就都不作数了。

   执行commit是将内存中的数据刷到硬盘上，之前所有的操作也就都生效了

4. 设置回滚点savepoint

   savepoint sp01;  # 当进行到某一步，设置一个回滚点
   rollback sp01; # 指定回滚点进行回滚
   

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

InnoDB支持所有隔离级别

​ set transaction isolation level 级别

1. read uncommitted(未提交读)
   事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"

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

3. repeatable read(可重复读) # MySQL默认隔离级别
   能够解决"脏读"问题，但是无法解决"幻读"
   所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题

4. serializable(可串行读)
   强制事务串行执行，很少使用该级别

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

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间)

每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较。

例如

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

username	create_version	delete_version
leethon	1

可以看到，我们在content列插入了这条数据，在create_version这列存储了1，1是这次插入操作的事务id。

然后我们将leethon修改为lee，实际存储是这样的

username	create_version	delete_version
leethon	1	2
lee	2

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

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

username	create_version	delete_version
lee	2	3

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

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

流程控制

向大部分语言一样，sql也支持分支结构和循环结构的流程控制。

# 分支结构
declare i int default 0;
IF i = 1 THEN
	SELECT 1;
ELSEIF i = 2 THEN
	SELECT 2;
ELSE
	SELECT 7;
END IF;

# 循环结构
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
	SELECT num ;
	SET num = num + 1 ;
END WHILE ;

索引

索引相关概念

索引是帮助我们快速获取数据的方法，索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

mysql中的索引

• primary key 主键
• unique key
• index key

这三种键都带有键属性，也就是索引属性，都可以加快字段数据的搜索，其中主键是帮助我们快速搜索所有记录的索引键。注，外键并不属于索引。

这些结合原理会更好理解

索引底层机制

建立索引会采取各种各样的策略，其中很多采取B+树的构造策略。

B+树是基于B树做优化的结构，它们都是有序的多叉树，可以通过当前遍历的节点找到目标节点处于那一部分，从而缩短搜索的路程。

B树的根节点、树枝节点、叶子节点等所有节点都会存储记录的全貌，而B＋树则将这些节点都只存储到叶子节点中，根节点和树枝节点只存储索引键值，并且B+树的叶子节点间也有指向相邻记录的指针，方便按顺序索引。

一些小概念

聚集索引与辅助索引

• 聚集索引就是通过B+树的根节点、树枝节点存储主键，叶子节点存储记录的所有数据。
• 辅助索引就是通过B+树的根节点、树枝节点存储其他键，叶子节点存储对应的主键。

所以我们通过辅助索引的键字段寻找不同字段的时候，实际上是先通过辅助索引找到对应的主键，然后在通过聚集索引找到对应的记录，再取出相应字段。

这个过程也称为非覆盖索引。

慢查询优化

explain sql语句的方式检测sql语句的扫描效率，分为7个等级

1）index 我们只要避免是最差的index等级就可以了。
2）range
3）ref
4）eq_ref
5）const
6）system
7）null