SQL进阶

徵羽宫的个人博客 / 686 / 4 / 创建于 5个月前 / 更新于 5个月前

首先，先说结论，Sql 进阶最重要的一点就是理解 SQL是一种面向集合的编程语言

SQL是一种面向集合语言

很多人不会写 SQL 或者写的不好的主要原因就是没有认识到这一点，用传统的编程语言的思维去理解 SQL, 把数据表想象成一个 Excel 或者 多维数组，简单的查询通过获取想象中 Excel 中的某些行解决问题，复杂的查询总是希望能够先把自己脑海中的多个 Excel 合并到一个 Excel 然后再取出其中的某些行。一旦要合并的 Excel 较多，或者压根不能合并，就变成了无解的问题。还有人抱怨 Sql 没有 变量 和 循环 ，摇摇头然后把结果集的处理交给自己熟悉的编程语言。

之所以有上面的误区，是因为大部分人先掌握了一门语言后会产生一种惯性思维，会用已有的编程语言的思维方式来思考新的问题。对于 SQL 这种面向集合的完全不同的编程语言必须抛开已有编程语言的认知去了解它。

比如我们之前把 JOIN 称为表连接，现在我们把它想象成集合的关系，比如 INNER JOIN 是求两个集合的交集，LEFT JOIN 是左边集合加右面集合的一部分，UNION 是求两个集合的并集，当然 table 和 子查询的结果, 我们可以把它们看成一个又一个的集合。整个查询过程变成通过集合运算获取结果集的一个过程。

思路转换过来，很多以前 SQL 看似没法解决的问题就变得可以解决了。

举个例子：
假设你现在一家水果摊上班(不要考虑水果摊养不养的起程序员)，老板说今天公司开业大酬宾(也不要考虑水果摊算不算公司)，凡是今天进店的顾客都可以得到随机赠送的水果两个，你需要写一个随机生成两个水果组合的新功能来实现老板的愿望。

显然这个功能很简单，就是先得到水果摊所有水果组成的集合的笛卡尔积，然后每次从笛卡尔积中随机抽取一个即可。以前你可能是先从数据库查出来所有的水果。然后用编程语言写一个求笛卡尔积的算法, 这个算法显然不会太简单 O(n^2)。现在你知道 SQL 是一个 面向集合 的编程语言，集合语言求笛卡尔积可能会很简单。事实也确实是这样：

// 我这里用用户表代替水果表进行测试 
SELECT `A`.`name`, `B`.`name` FROM
(SELECT `id`, `name` FROM `users` LIMIT 50 OFFSET 0) AS `A`,
(SELECT `id`, `name` FROM `users` LIMIT 50 OFFSET 0) AS `B`;

可以看到我模拟了50种水果， 2500种可能，只用了一行代码加 25ms 就得到了结果。

有没有感觉很神奇？然而SQL 能做的显然还有更多。比如前几天论坛里一个求交集的SQL问题问答：请教一个sql语句,我实现这个目前需要查两次和求交集才能取到,如何使... ，最佳答案直接 INNER JOIN, 我的答案没求交集，也能得到结果。这就是SQL语言的魅力。

SQL中没有顺序

首先考虑下面这个问题
在不考虑缓存的情况下，下面那一条语句的查询速度比较快？

SELECT * FROM `users` WHERE `id` IN (1, 2, 3, 4, 5, 6, 7, 8 ,9, 10);
SELECT * FROM `users` WHERE `id` IN (100001, 100002, 100003, 100004, 100005, 100006, 100007, 100008 , 100009, 100010);

答案是一样快，我在一张 50万条数据的用户表中进行了测试，都是8ms左右，没有区别。原因也显而易见，因为 SQL 作为面向集合的语言，这两条语句都是从集合中获取集合的一部分，没有本质区别，再往底层讲你可以从索引上理解这个问题。总之它是一个集合，而集合是没有顺序的，不是你想象中的一个有顺序的 Excel 或者 多维数组。

虽然集合没有顺序，但并不是说结果集是没有顺序的。SQL 在进行一些操作时可能会在内部进行排序。这种排序行为就会消耗服务器性能和时间，所以了解那些可能会引起内部排序的操作是有意义的。常见的会引起SQL内部对集合进行排序的操作有：

GROUP BY
ORDER BY
各种聚合函数，比如 SUM AVG 等等， SQL 为了避免重复计算会在底层先进行排序。
DISTINCT，同样为了避免重复计算。
集合运算符比如 JOIN EXCEPT 等等，这里 SQL 是为了避免重复的集合元素所以需要进行排序

SQL中的层级

考虑下面这个问题

下面这条查询重名用户的SQL语句有什么问题？

SELECT `id`, `name`, count(`name`) as count FROM `users` GROUP BY `name` LIMIT 200 OFFSET 0;

可能有的人觉得没问题，有的人在自己的数据库跑了一下发现没问题，也有的人跑了一下发现报下面的错。

 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

翻译过来，意思就是说 id 字段不依赖于 GROUP BY 中的列，不符合 only_full_group_by 这个 sql mode。

为什么会出现这种情况呢？问题的关键在于 GROUP BY 之后得到的是分组(聚合)后的数据，原来操作的是行，现在操作的是集合。比如说行是0阶的话，那么集合就是1阶，它俩完全不在一个层级。不同的层级之间等级森严，不能相提并论。

了解了层级的概念后，我们再来说 WHERE 和 HAVING 的区别。

有使用过这两个关键字的小伙伴可能说的上来， WHERE 一般写在 GROUP BY 之前，HAVING 一般写在 GROUP BY 之后。

比如下面这样：

SELECT `name`, count(`name`) as count FROM `users` WHERE `id` < 10000 GROUP BY `name` HAVING count(`name`) > 0 ORDER BY count(name) DESC;

在了解了 SQL 的层级之后，我们就会对它俩的区别有更深刻的了解，即：
它俩一个操作的是行这种 0阶的对象，另一个操作的是集合这样 1阶的对象。

SQL中的子查询

我们已经了解了 子查询 和 table 都是集合，所以可以像操作 table 一样操作 子查询 比如可以写在 FROM 后面，可以和其他的 table 和 子查询 进行各种集合运算等等。但是需要注意的是 table 是有索引的， 子查询 得到的是新的集合没有索引. 子查询 的好处在于它可以在集合运算前控制集合的大小，但是并不能保证 子查询 之间的集合运算效率必定比 table 之间更好。

然后说到子查询，子查询有一个 EXISTS 特别好用, 它可以用来判断一个集合是否有元素；

SELECT * FROM users WHERE EXISTS (SELECT * FROM `components`) LIMIT 20 OFFSET 0;
SELECT * FROM users as A WHERE EXISTS (SELECT * FROM users WHERE users.id = A.id AND users.id = 1) AND id IN  (1, 2) LIMIT 20 OFFSET 0;