[转帖]分享6个SQL小技巧

分享,sql,技巧 · 浏览次数 : 0

小编点评

**简介** SQL是一种用于数据库操作的语言，它允许您通过查询数据库中的数据来获取想要的信息。SQL的语法相对简单，并且很多编程语言都支持SQL。 **一些基本语法** * `SELECT` 语句用于指定要查询的数据。 * `FROM` 语句用于指定要查询的表。 * `WHERE` 语句用于筛选数据。 * `ORDER BY` 语句用于排序数据。 **一些常用函数** * `COUNT()` 函数用于统计数据中的重复值。 * `SUM()` 函数用于统计数据中的总和。 * `MAX()` 函数用于返回数据中的最大值。 * `MIN()` 函数用于返回数据中的最小值。 * `WHERE` 子句用于筛选数据。 **一些常见的SQL语句** * `SELECT * FROM table_name WHERE id = 1` 获取表中 id = 1 的记录。 * `SELECT count(*) FROM table_name` 获取表中所有记录的数量。 * `SELECT MAX(score) FROM table_name` 获取表中分数最高的记录。 * `SELECT * FROM table_name WHERE course_id = ' Maths ' AND score > (SELECT max(score) FROM table_name WHERE course_id = ' Maths ')` 获取每个学科分数最高的那条记录。 * `SELECT * FROM table_name ORDER BY score DESC LIMIT 5` 获取表中所有分数大于平均分的5个记录。 **LATERAL JOIN** LATERAL JOIN 是 SQL 中一种新语法，它可以用于在关联表中进行过滤。例如，以下语句使用 LATERAL JOIN 来连接两个表，其中 `student` 表中的 `student_id` 和 `course` 表中的 `course_id` 之间建立关联： ```sql SELECT * FROM student s LEFT JOIN course c ON s.student_id = c.student_id; ``` **其他技巧** 除了上述内容之外，还有许多其他技巧可以用于提高 SQL 的性能，例如： * 使用索引来加快查询速度。 * 使用分组和聚合来进行数据聚合。 * 使用事务来确保数据的一致性。 * 使用缓存来减少数据库访问时间。

正文

https://www.jianshu.com/p/2fcf0a4e83b7

简介

经常有小哥发出疑问，SQL还能这么写？我经常笑着回应，SQL确实可以这么写。其实SQL学起来简单，用起来也简单，但它还是能写出很多变化，这些变化读懂它不难，但要自己Get到这些变化，可能需要想一会或在网上找一会。

各种join

关于join的介绍，比较流行的就是这张图了，如下：

简单的解释如下：

join：内联接，也可写成inner join，取两表关联字段相交的那部分数据。
left join：左外联接，也可写成left outer join，取左表数据，若关联不到右表，右表为空。
right join：右外联接，也可写成right outer join，取右表数据，若关联不到左表，左表为空。
full join：全联接，也可写成full outer join，取左表和右表中所有数据。

但注意上图，里面还有几个Key is null的情况，它可以将两表相交的那部分数据排除掉！
也正是因为这个特性，一种很常见的SQL技巧是，用left join可替换not exists、not in等相关子查询，如下：

select * from tableA A 
where not exists (select 1 from tableB B where B.Key=A.Key)

-- 使用left join的等价写法
select * from tableA A 
left join tableB B on B.Key=A.Key where B.Key is null

也比较好理解，只有当左表的数据在右表中不存在时，B.Key is null才成立。

查询各类别最大的那条数据

比如在学籍管理系统中，有一类很常见的需求，查询每学科分数最高的那条数据，有如下几种写法：

select * from stu_score s 
where s.course_id in ('Maths','English') 
and s.score = (select max(score) from stu_score s1 where s1.course_id = s.course_id)

比较好理解，考分最高其实就是过滤出分数等于最大分数的记录。

在不能使用子查询的场景下，也可转换成join，如下：

select * from stu_score s 
left join stu_score s1 on s1.course_id = s.course_id and s1.score > s.score
where s.course_id in ('Maths','English') and s1.id is null

这和前面用left join改写not exists类似，通过s1.id is null过滤出left join关联条件不满足时的数据，什么情况left join关联条件不满足呢，当s表记录是分数最大的那条记录时，s1.score > s.score条件自然就不成立了，所以它过滤出来的数据，就是学科中分数最大的那条记录。

一直以来，我看到SQL的join的条件大都是a.field=b.field这种形式，导致我以为join只能写等值条件，实际上，join条件和where中一样，支持>、<、like、in甚至是exists子查询等条件，大家也一定不要忽视了这一点。

上面场景还有一种写法，就是使用group by先把各学科最大分算出来，然后再关联出相应数据，如下：

select * from
(select s.course_id,max(s.score) max_score stu_score s where s.course_id in ('Maths','English') group by s.course_id) sm
join stu_score s1 on s1.course_id = sm.course_id and s1.score=sm.max_score

查询各类别top n数据

比如在学籍管理系统中，查询每学科分数前5的记录，类似这种需求也很常见，比较简单明了的写法如下：

select * from stu_score s 
where s.course_id in ('Maths','English') 
and (select count(*) from stu_score s1 where s1.course_id = s.course_id and s1.score > s.score) < 5

很显然，第5名只有4个学生比它分数高，第4名只有3个学生比它分数高，依此类推。

LATERAL join

MySQL8为join提供了一个新的语法LATERAL，使得被关联表B在联接前可以先根据关联表A的字段过滤一下，然后再进行关联。

这个新的语法，可以非常简单的解决上面top n的场景，如下：

select * from stu_course c 
join LATERAL (select * from stu_score s where c.course_id = s.course_id order by s.score desc limit 5) s1 on c.course_id = s1.course_id
where c.course_name in ('数学','英语')

如上，每个学科查询出它的前5名记录，然后再关联起来。

统计多个数量

使用count(*)可以统计数量，但有些场景想统计多个数量，如统计1天内单量、1周内单量、1月内单量。

用count(*)的话，需要扫描3次表，如下：

select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 DAY)
union all
select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 WEEK)
union all
select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 MONTH)

其实扫描一次表也可以实现，用sum来代替count即可，如下：

select sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 DAY)), 1, 0) day_order_cnt,
sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 WEEK)), 1, 0) week_order_cnt,
sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 MONTH)), 1, 0) month_order_cnt
from order where add_time > DATE_SUB(now(), INTERVAL 1 MONTH)

IF是mysql的逻辑判断函数，当其第一个参数为true时，返回第二个参数值，即1，否则返回第三个参数值0，然后再使用sum加起来，就是各条件为true的数量了。

数据对比

有时，我们需要对比两个表的数据是否一致，最简单的方法，就是在两边查询出结果集，然后逐行逐字段对比。

但是这样对比的效率比较低下，因为它要两个表的数据全都查出来，其实我们不一定非要都查出来，只要计算出一个hash值，然后对比hash值即可，如下：

select BIT_XOR(CRC32(CONCAT(ifnull(column1,''),ifnull(column2,'')))) as checksum 
from table_name where add_time > '2020-02-20' and add_time < '2020-02-21';

先使用CONCAT将要对比的列连接起来，然后使用CRC32或MD5计算hash值，最后使用聚合函数BIT_XOR将多行hash值异或合并为一个hash值。

这个查询最终只会返回1条hash值，查询数据量大大减少了，数据对比效率就上去了。

总结

SQL看起来简单，其实有很多细节与技巧，如果你也有其它技巧，欢迎留言分享讨论😃