MySQL补充知识及索引

昨日内容回顾

• 多表查询的关键字

  inner join...on...

  left join...on....

  right join...on...

• SQL关键字补充

  concat/concat_ws/add/change/modify

• 多表查询实操方式

  1. 观察表的相互联系，确定需要操作的表；
  2. 根据目标条件分析操作顺序，逐步操作。

• pymysql模块

  操作步骤：连接服务端→生成游标→发送指令→接收结果

  须记忆相关代码。

今日内容概要

• SQL注入问题
• 视图
• 触发器
• 存储过程
• 流程控制
• 内置函数
• 索引与慢查询优化

今日内容详细

SQL注入问题

该问题出现的主要原因是在向MySQL服务端传入数据时，数据本身中含有特殊符号，导致数据本身偏离了起原有的含义，而变成了MySQL中特有的语句。

要解决SQL的注入问题，则需要对传送的数据进行限制，使传入的数据不会与SQL语句冲突。

使用pymysql模块传输SQL语句时，避免手动拼接数据，而是先使用占位符占位，在调用execute方法时再传入相关数据，由execute方法自动对应可避免SQL注入问题。

视图

当需要多次使用某次数据查询的结果时，可以将该结果保存为视图，之后使用时调用该视图即可。

创建视图的语句如下

create view 视图名 as 某次数据查询的结果;

视图内的数据无法被更改，仅供查看及调用。

在使用cmd窗口操作MySQL时，尽量不推荐使用视图，因为创建的视图会跟原本的数据一同展示在database下，容易造成表的混淆。

在使用可视化软件操作MySQL时，视图会与原数据表分开展示。

触发器

触发器(trigger)即满足一定条件后一段自动触发的代码。MySQL中常见的触发器一般为修改操作前后。

创建触发器的语句如下

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

触发器命名需要遵守一定的规范，即 tri_before_insert_t

触发器中的SQL语句需要使用分号，因此需要临时改变SQL语句的结束符。临时修改结束符使用delimiter关键字。

事务

事务的四大特性

mysql中事务是一连串操作的合称，事务具有四个属性ACID：

A：Atomicity，原子性，即事务内的操作是不可分割的，所有事务必须同时成功或失败。

C：Consistency，一致性，即事务的结果必须时数据库由一个一致性状态变为另一个一致性状态。

I：Isolation，隔离性，即不同的事务之间是互不干扰的，即一个事务的运行不会影响另一事务的运行。

D：Duration，永久性，即事务对数据库造成的影响是永久的，不可逆的。

事务的相关关键字

start transaction 开始事务操作。

rollback 返回当前事务操作前的数据库状态。

commit 确认事务操作完成，在commit之前，对数据库的操作都储存在内存中，并未修改硬盘数据。

savepoint 存档点，规定rollback的节点，事务commit之后存档点会被删除。

事务的隔离级别

在InnoDB中事务有四个隔离级别：

read uncommitted(可读取未提交数据)，该隔离级别隔离程度较低，一个事务在执行过程中，另一个事务可调用该事务正在操作的数据，造成"脏读"现象。

read committed(可读取已提交数据)，多数数据库系统的默认隔离级别，即一个事务在执行过程中数据不可被调用，只有执行完成后数据才可被调用。

repeatable read(可重复读取)，一个事务在执行过程当中，多次读取同一数据，而且得到相同的结果，即为可重复读，这是MySQL默认的隔离级别。若在该事务未操作数据库的时间内，另一事务对该数据进行了修改，下次读取时，得到了不同的结果，则出现所谓的"幻读"。InnoDB通过采取多版本并发控制(MVCC, Multi-verson Concurrency Control)和间隙锁解决"幻读"问题。

serializable(可串行读)，即将所有事务必须串行执行，很少使用。

存储过程

存储过程(procedure)是封装的自定义的固定操作，定义procedure与定义trigger相似，区别是需要手动调用。

# 无参版本
delimiter $$
create procedure p1()
begin
	select * from cmd;
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  查看

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

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

流程控制

MySQL中可以实现流程控制，使用方法例：

# 分支结构
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中内置函数可以通过使用 help 函数名查看其解释说明即使用范例。简单内置函数有：

Trim/Ltrim/Rtrim 移除首尾/左/右指定字符

Upper/Lower 大写/小写转换

Left/Right 获取从左/右开始指定个数字符

Soundex 查询发音(英语)相似字符

date_format 识别日期格式

索引与慢查询优化

索引的相关概念

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

数据库中索引也称为键，常见的键分为3种，主键(primary key)、特征值键(unique key)、索引键(index key)。

主键索引为聚集索引，其余两种索引为非聚集索引，非聚集索引实质还是要依赖聚集索引。

索引的优劣势

优势：加快数据索引速度。

劣势：占用物理内存空间，降低数据修改速度。

索引的数据结构

索引底层是树形结构，树是计算机底层的数据结构。

常见的树有二叉树、b树、b+树、b*树。

二叉树即每个节点只能向下做两个分支的树，b树即每个节点所能储存的数据量是固定的。

b+树与b*树仅有最下层节点才储存真正的数据，上层节点仅储存索引数据。

特点是b+树在最底层有指向其他同级节点的指针，b*树在每一级节点都有指向其他同级节点的指针。

慢索引优化

使用explain关键字可查看当前SQL语句的索引速度级别，由慢到快依次为：

1）index 尽量避免
2）range 一般要求
3）ref
4）eq_ref
5）const
6）system
7）null