MySQL——我的学习笔记

---

前言

学习MySQL的第三天，希望用一篇学习笔记来记录MySQL的基础语法并加入自己的理解，后续会不定期补充和更新。既能满足自己回看复习，也可供大家批评指正，以作为一个交流平台。

学习来源：【戴师兄】全网最良心的数据分析自学攻略！它来了！数据分析零基础新手入门教程ExcelSQLTableauPythonPowerBI全套课程合集学习资源路径方法！

给戴师兄打个广告，本篇文章的所有学习内容基本来源于戴师兄的学习笔记。

---

本文暂时包括查询语句的基础语句和高级语句。

SQL查询语句语法结构和运行顺序

语法结构：select–from–where–group by–having–order by–limit
运行顺序：from–where–group by–having–order by–limit–select

一、基础语句

1.select&from

select name, continent, population
from world

在world表格中查询名字、大洲、人口信息。

select * 
from world

显示world表格全部信息

select name as 国家, continent 大洲, population 人口 
from world

对字段名重命名(可加as可不加)

select distinct continent
from world

去重

select distinct continent, name
from world

在select查询语句中distinct关键字只能用在第一个所查列名之前

select name, gdp, population, gdp/population 人均gdp 
from world

select关键字可以直接进行计算，在两个列名间插入一个运算符构建计算字段即可。
算式所涉及的字段必须是表格中包含的，或者算式本身可以独立运算。

2.where

where 表达式 限定查询行必须满足的条件
where 核心子句是可选项，使用该子句是为了通过表达式筛选出符合查询条件的行数据

运算符	描述
= / > / < / !=	等于 / 大于 / 小于 / 不等于
between…and…	区间内（包含端点）
in / not in	条件范围筛选 / 不在范围内
is null / is not null	空值 / 不为空值
and / or / not	与 / 或 / 非

通配符（结合like）	描述
_	匹配单个字符
%	匹配0至若干个字符

通常，与运算优先于或运算。或者，可以在限定语句中加括号表示先后顺序。

select name 人口至少两亿的国家,gdp/population 人均 
from world
where population>=200000000

查询人口至少2亿的国家名和人均gdp

where name=Germany

查询德国的XX

where name in('Sweden', 'Norway', 'Denmark')

查询瑞典、挪威和丹麦的XX

where area between 250000 and 300000

面积在250000和300000之间

like和通配符

where name like 'C%ia'

以C开头以ia结尾（%也有可能指0个字符）

where name like ‘_t%’

第二个字符为t

where name like '%a%a%a%'and area>600000

and连接条件

3.order by

asc升序、desc降序。默认升序。

order by yr desc,winner asc

先按yr主排序，后按winner次排序

4.limit

limit [偏移量,] 行数
偏移量可选，行数必选。
偏移量为X的时候，表示从第x+1个开始；行数表示输出多少行。

5.聚合函数&group by

聚合函数	说明
AVG()	返回某列的均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列的和

group by 字段名：依据哪个字段分组聚合

eg：查询2013至2015年每年每个科目的获奖人数，结果按年份从大到小，人数从大到小排序

select yr, subject, count(winner)   //每年->每个科目->获奖者
from nobel
where yr between 2013 and 2015
group by yr, subject     //先按年归类，再按科目归类
order by yr desc, count (winner) desc  //先按年份降序，再按获奖人数降序

6.having&简单运行原理

限定分组聚合后的查询行必须满足的条件（对于group by后的列名，起到替代where的作用）

having与where的区别：
having是在分组后对数据进行过滤，where实在分组前对数据进行过滤；
having后面可以使用聚合函数，where后面不可以使用聚合函数。

7.部分常见函数

数学函数：

round(x,y)
四舍五入函数。对x值进行四舍五入，精确到小数点后y位

字符串函数：

concat(s1,s2,...)
连接字符串函数。返回s1，s2…的连接值，有参数为null时返回null
eg：查询首都和名称，其中首都需是国家名称的扩展
不能用capital like %name%,因为name被识别成字符串，应该为where capital like concat('%',name,'%')

replace(s,s1,s2)
替换函数。在s中，所有s1用s2替代

left(s,n)
right(s,n)
substring(s,n,len)
截取字符串一部分的函数。分别表示s左起n个字符 / 右起n个字符 / 第n位起len个字符。
注意： n为负时指倒数第n个开始，没有len时默认取到尾。

数据类型转换函数：

cast(x as type)。
转换数据类型的函数。将一个类型的x值转换为另一类型

日期时间函数：

year(date)
month(date)
day(date)
获取年月日的函数

date_add(date,interval expr type)
date_sub(date,interval expr type)
对指定起始时间进行加减操作

datediff(date1,date2)
计算两个日期之间间隔的天数

date_format(date,format)
将日期和时间格式化

timestampdiff(interval, datetime1,datetime2)
返回（时间2-时间1）的时间差，结果单位由interval参数给出。

条件判断函数：

if(expr,v1,v2)
true返回v1，false返回v2

case expr when v1 then r1 [when v2 then r2] ...[else rn] end
如果表达式expr和v1相同则返回r1，和v2相同则返回r2…否则rn，后面记得加end

case when v1 then r1 [when v2 then r2]...[else rn] end
如果v1为真则返回r1…

图片来源：SQL语句中CASE WHEN的使用实例

二、高级语句

1.窗口函数

窗口函数结构示例如下：

窗口函数()over([partition by 字段名] [order by 字段名 asc | desc])

分别由窗口函数、分区依据和排序依据组成。
分区依据是可选项，当输出的长度为一个总排序时，不用分区，分区依据可以不写。

窗口函数分为排序窗口函数和偏移分析函数。

排序窗口函数：
rank()over()
若存在相同位次，会跳过之后的位次。例如，有3条排在第1位时，排序为：1，1，1，4（常用）
dense_rank()over()
若存在相同位次，不会跳过之后的位次。例如，有3条排在第1位时，排序为：1，1，1，2（连续）
row_number()over()
这个函数赋予唯一的连续位次。例如，有3条排在第1位时，排序为：1，2，3，4（不重复）

偏移分析函数： 用于统计分组内往上第n行值，如果往上没有数据了则会输出null
lag(col,n,default)over()
col：列名；
n：往上第n行（默认为1）；
default：指碰到null默认多少，不设的话输出null。有时令default为col，则往上没取到就取自身。

2.表连接

内连接：
from表1 (inner) join 表2 on 表1.字段名=表2.字段名
意为：根据表1和表2中的一对指代目标相同的字段名（连接键）把表1和表2拼接起来。inner可省略。

但有时连接键为null的数据行会被忽略，因此要用到左连接和右连接。
左连接：
from表1 left join 表2 on 表1.字段名=表2.字段名
右连接：
from表1 right join 表2 on 表1.字段名=表2.字段名
左连接的作用：保留左表的所有数据行（包括连接键为null的数据行）

以下情况是等价的：
from A left join B <===> from B right join A

如果出现来自不同表中字段同名的情况，需要指定字段名来自哪个表

表.字段名

3.子查询

子查询本身就是一段完整的查询语句，然后用括号英文括号() 包裹嵌套在主查询语句中，子查询可以多层嵌套；
最常用的子查询运用在from和where子句中。

子表查询可以将子表后加上as XX，直接定义为新表的意思，（记得加上括号），以在外部嵌套的时候直接通过表名.字段名来指代。

三、八股提纲

1.索引

索引分类

按数据结构分类

1. B+索引
2. Hash索引
3. 全文索引

为什么用B+？

• 相比哈希，有提供范围搜索的功能；
• 相比红黑树和二叉搜索树，层数更低，磁盘调用次数更少；
• 相比B树，非叶子结点不存储数据，可以存放更多的指针；双链表可以范围查找

按物理存储分类

1. 聚簇索引：直接用主键搜索到全部数据
2. 非聚簇索引（二次索引）：先用键值查找到主键（如果搜索到的东西满足需要，则称覆盖索引），再用主键查找到全部数据（回表）

按字段特性分类

1. 主键索引
2. 唯一索引UNIQUE
3. 前缀索引
4. 普通索引

按字段个数分类

1. 单列索引
2. 联合索引

一定需要索引吗？

什么时候不需要创建索引？

1. 对不会使用WHERE,GROUP BY,ORDER BY语句查询的，不用创建
2. 字段里很多相同数据的，比如男和女
3. 数据量很少的
4. 会经常对数据进行修改的，例如电商项目里的余额

什么时候需要？

1. 经常用WHERE,GROUP BY,ORDER BY语句查询的
2. 字符有唯一性的

优化索引方法

1. 前缀索引（减少索引字段大小）
2. 覆盖索引：设为联合索引，二级索引即可查询，避免回表
3. 尽量选择递增编号作为主键
4. 选择非null值
5. 避免索引失效
    (1)模糊匹配中间或后半段
    (2)对索引做了计算、函数等操作
    (3)联合索引没有遵循最左匹配原则
    (4)使用where or时，一个条件非索引列

2.事务

事务四大特性

ACID

1. 原子性，一次事务所有操作要么都执行要么都不执行（回滚日志）
2. 一致性，事务前后，所有数据之间的数量关系保持一致（最终目的）
3. 隔离性，并发事务，不会出现数据安全问题（MVCC）
4. 持久性，数据保存在磁盘，永久保存（重做日志）

并行事务出现的问题 和 事务隔离级别

问题：

1. 脏读：读到其他事务修改后还未提交事务的数据
2. 不可重复读：在一次事务中前后读到的数据不一致
3. 幻读：在一次事务中查询符合条件的记录数量，读到的数量不一致

隔离级别

1. 读未提交：还没提交事务就能被其他事务看到最弱的，每次读到的是最近数据
2. 读已提交：提交事务后才被其他事务看到；解决了脏读问题。
3. 可重复读：进入事务时有一个快照，全程用这个数据；解决了不可重复读。
4. 串行化：事务加锁，只能串行；最高隔离级别。

InnoDB的 解决了 不可重复读 问题吗？

可重复读，采用MVCC（多版本并发控制）
绝大多数被解决。
对于快照读，select，采用MVCC保存快照
对于当前读，select...for update，采用间隙锁，禁止其他记录插入

3.锁

全局锁

用于全库逻辑备份，缺点是业务会停滞；
避免方式：在可重复读级别的前提下，开启事务自动创建Read View，备份数据库；MyISAM不支持事务，需要用到全局锁。

表级锁

表	表锁	元数据锁MDL	意向锁
分类	表共享锁、表独占锁	CRUD时加入MDL读锁；结构变更时加MDL写锁	意向共享锁，意向独占锁
特点	读读才共享	不需要显式调用，自动加	在对某些记录（行）加锁前，先对整个表加意向锁，这不会冲突；方便在加独占表锁时，不用遍历所有记录，快速判断是否有记录被加锁
缺点	颗粒度太大影响性能（InnoDB解决）	长事务持有写锁会阻塞，所以可以考虑kill

行级锁

行	Record 记录锁	Gap 间隙锁	Next-Key	插入意向锁
作用	锁一条记录	锁定一个范围（不包括边界）	锁定边界和范围	插入行级锁被阻塞时使用，表示有事务在等待
备注	只有读和读才兼容	只存在于可重复读级别，为了解决幻读；且兼容	只有读和读才兼容
释放时机	事务提交后	同	同	同

死锁

发生

两个事务都查询目标是否存在，如果不存在则插入目标；

1. 如果用select则可能重复插入相同的订单，出现幻读
2. 如果用select...for update防止幻读，但在检测是否存在时可能都阻塞

避免
当发生时，打破 (4) 循环等待 条件

1. 设置 事务等待锁 的时间上限
2. 开启死锁检测。在出现死锁时主动回滚

---

总结

对MySQL的查询语句稍做了一些了解，并做了如上总结，后期不定期进行补充和完善。