必知必会

练习数据-NBA (opens new window) 练习数据-王者荣耀 (opens new window)

TIP

命名规范建议：

• 数据库名、表名、字段名全部小写
• 关键字和函数名称全部大写
• SQL 语句必须以分号结尾

虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行，但是数据库名、表名和字段名在 Linux MySQL 环境下是严格区分大小写的，因此建议统一这些字段的命名规则，比如全部采用小写的方式。同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

执行过程

-- SELECT 的执行顺序
-- SELECT 查询时的两个顺序
-- 1. 关键字的顺序不能颠倒：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
-- 2. SELECT 语句的执行顺序
FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

-- 例如：
SELECT DISTINCT player_id, player_name, count(*) as num #顺序5
FROM player JOIN team ON player.team_id = team.team_id #顺序1
WHERE height > 1.80 #顺序2
GROUP BY player.team_id #顺序3
HAVING num > 2 #顺序4
ORDER BY num DESC #顺序6
LIMIT 2 #顺序7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

• FROM 阶段，如果是多张表联查还会经历下面的几个步骤：
  • 1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
  • 2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
  • 3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
• WHERE 阶段：在 FROM 阶段我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。
• GROUP 和 HAVING 阶段：在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。（聚集函数的计算在 GROUP BY 之后 HAVING 之前）
• SELECT 和 DISTINCT 阶段：当我们完成了条件筛选部分之后，就可以筛选表中提取的字段。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。
• ORDER BY 阶段：当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，得到虚拟表 vt6。
• LIMIT 阶段：在 vt6 的基础上，取出指定行的记录，得到最终的结果，对应的是虚拟表 vt7。

比较运算符	含义
=	等于
<	小于
>	大于
!= 或 <>	不等于
<= 或 !>	小于等于（不大于）
>= 或 !<	大于等于（不小于）
BETWEEN	在指定的两个数值之间
IS NULL	为空值
IS NOT NULL	不为空值

逻辑运算符	含义
AND	并且
OR	或者
NOT	非（否定）
IN	在指定条件范围内

当 WHERE 子句中同时存在 AND 和 OR 的时候，AND 执行的优先级会更高，也就是说 SQL 会优先处理 AND 操作符，然后再处理 OR 操作符

WARNING

通配符：% 匹配零个或多个任意字符（除了 NULL），_ 匹配一个任意字符

尽量少用通配符，因为它需要消耗数据库更长的时间来进行匹配，即使对 LIKE 检索的字段进行了索引，索引的价值也可能会失效。

如果要让索引生效，那么 LIKE 后面就不能以 % 开头，比如使用 LIKE '%太%' 或 LIKE '%太' 的时候就会对全表进行扫描。

如果使用 LIKE '太%'，同时检索的字段进行了索引的时候，则不会进行全表扫描。

TIP

在 WHERE 子句中可以通过建立索引的方式进行 SQL 优化，但是如果对索引字段进行了函数处理，或者使用了 <>，!= 或 NULL 判断等，都会造成索引失效。

• 1、不要在 WHERE 子句后面对字段做函数处理，同时也避免对索引字段进行数据类型转换
• 2、避免在索引字段上使用 <>、!= 以及对字段进行 NULL 判断（包括 IS NULL，IS NOT NULL）
• 3、在索引字段后，慎用 IN 和 NOT IN，如果是连续的数值，可以考虑用 BETWEEN 进行替换

TIP

ORDER BY 字段增加索引原因：

在 MySQL 中，支持两种排序方式：FileSort 和 Index 排序。Index 排序的效率更高。

Index 排序：索引可以保证数据的有序性，因此不需要再进行排序。

FileSort 排序：一般在内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序，效率较低。

所以使用 ORDER BY 子句时，应该尽量使用 Index 排序，避免使用 FileSort 排序。 当然具体优化器是否采用索引进行排序，你可以使用 explain 来进行执行计划的查看。

优化建议：

• 1、SQL中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。 一般情况下，优化器会帮我们进行更好的选择，当然我们也需要建立合理的索引。
• 2、尽量 Using Index 完成 ORDER BY 排序。 如果 WHERE 和 ORDER BY 相同列就使用单索引列；如果不同使用联合索引。
• 3、无法 Using Index 时，对 FileSort 方式进行调优。

提升 SELECT 查询效率： 约束返回结果的数量；指定筛选条件进行过滤，过滤可以筛选符合条件的结果，并进行返回，减少不必要的数据行；避免全表扫描

• 1. 如果我们不需要把所有列都检索出来，还是先指定出所需的列名，因为写清列名，可以减少数据表查询的网络传输量，而且在实际的工作中往往不需要全部的列名。
• 2. 约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
• 3. 在 WHERE 及 ORDER BY 涉及到的列上增加索引，避免全表扫描

DISTINCT

• DISTINCT 需要放到所有列名的前面
• DISTINCT 是对后面所有列名的组合进行去重
• 对数据行中不同的取值进行聚集：先用 DISTINCT 函数取不同的数据，然后再使用聚集函数
• 一般在使用 MAX 和 MIN 函数统计数据行的时候，不需要再额外使用 DISTINCT，因为使用 DISTINCT 和全部数据行进行最大值、最小值的统计结果是相等的

GROUP BY HAVING

• 如果字段为 NULL，也会被列为一个分组
• 使用多个字段进行分组，相当于把这些字段可能出现的所有的取值情况都进行分组

WHERE 与 HAVING

• HAVING 的作用和 WHERE 一样都是起到过滤的作用。 WHERE 用于数据行的条件过滤，HAVING 用于分组过滤。
• 对于分组的筛选，一定要用 HAVING 而不是 WHERE。另外，HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，都可以使用 HAVING 对分组进行筛选。

ORDER BY

• 排序的列名：ORDER BY 后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列的值相同的时候，再按照第二列进行排序，以此类推。
• 排序的顺序：ORDER BY 后面可以注明排序规则，ASC 代表递增排序，DESC 代表递减排序。如果没有注明排序规则，默认情况下是按照 ASC 递增排序。我们很容易理解 ORDER BY 对数值类型字段的排序规则，但如果排序字段类型为文本数据，就需要参考数据库的设置方式了，这样才能判断 A 是在 B 之前，还是在 B 之后。比如使用 MySQL 在创建字段的时候设置为 BINARY 属性，就代表区分大小写。
• 非选择列排序：ORDER BY 可以使用非选择列进行排序，所以即使在 SELECT 后面没有这个列名，同样可以放到 ORDER BY 后面进行排序。
• ORDER BY 的位置：ORDER BY 通常位于 SELECT 语句的最后一条子句，否则会报错。