最近因为要找数据分析的实习,复习sql有一段时间了。昨天面试滴滴的实习,突然发觉自己空做了些题目,对于基础的查询语句内部执行机制并不清楚,所以在这里记录下自己的学习过程。

1 执行顺序

在sql中,基础的查询语句模板如下所示:

select 查询项1, 查询项2
from 数据表 join 数据表2
on 连接条件
where 约束条件
group by 分组项1, 分组项2
having 对聚合内容的约束
order by 排序项1 asc, 排序项2 desc
limit 3

其中,语句的执行顺序是:

image-20210417155047689

具体过程:

  1. from :建立一个虚表,关于多个表连接的情况先不考虑,这里只涉及两个表连接,from会对前面两个表执行笛卡儿积,生成虚表TV1
  2. on:在TV1中只有那些真正符合连接条件的元组会被筛选出来,生成虚表TV2。其实on中的内容写在where中是等效的,on的出现一方面更好区分表连接条件和其他的筛选条件,提高代码可读性,另一方面提高表连接的效率。(此处存疑)
  3. join:如果指定了使用了外连接,会将保留行(比如用左连接,那么左表中,没有找到匹配的行是保留行)作为外部行添加到VT2,生成VT3
  4. where:对VT2中的行按条件进行筛选,生成TV4
  5. group by:将对应项分组,生成TV5,后文会详细介绍group by背后生成Tv5的过程
  6. having:进一步对分组后的虚表TV5删去不符合的情况,得到TV6
  7. select:查询对应字段,同时计算相应的聚合函数,sum,avg等,以及distinct,得到TV7
  8. order by,limit:对字段排序,并截取对应的行,得到最终结果。limit m n从第m条记录开始,选择n条记录

参考:https://www.cnblogs.com/loong-hon/p/13470263.html

2 group by生成虚表原理

问题:

没在group by中写过的属性列,为什么不可以用select查询?

不写在group by中的列,为什么却可以查询它的聚合函数?

2.1 一个字段

下图表中,字段s_id是学生编号,c_id是课程编号,score是课程成绩。

比如对s_id分组,过程是这样的:

img

右边形成的是虚表,每一个块相当于一个虚拟的行。

比如s_id=1的那个同学,当select课程时候,因为一个id对应多个课程,当然会报错。比如第三列成绩,select avg(score)是可以的,因为与分组字段id是一一对应的。

2.2 多个字段

如果group by是分组多个字段呢?就会如下图所示,group by id, name两个字段的组合看作是一个整体:

img

类似复合主键,不同的两两组合情况对应不同的分组group

参考:

https://blog.csdn.net/u014717572/article/details/80687042

https://zhuanlan.zhihu.com/p/106767752

3 having

having语句是分组后过滤的条件,在group by之后使用,也就是如果要用having语句,必须要先有group by语句。

group by的功能是分组聚合,将多条记录变成比较少的记录,而having的功能是由多变少之后,再变少的过程。另外having后面可以跟多种运算形式,但是运算的结果只能是一个逻辑值(0或者非0的数值)

例如,查询平均成绩大于60的学生:

img

此外,having可以跟多种运算形式,但是运算结果只能是一个逻辑值(一个0或非0的数),例如

-- 查询至少两门课程及格的学生
having sum(score>=60)>=2

还不懂就点进去做一下这位优秀知乎答主的题目:MySQL查询中having语句的使用场景和用法——知乎链接

4 表连接

表连接的种类有很多,有一个经典的图解

image-20210310105906345

就像是第一节介绍语句执行顺序时描述的表连接过程,比如左上角第一个图,left join的意思是以左表为基准,两者匹配到可以保留(中间交集的部分),左表A未匹配到的行也保留(左边蓝色的部分)

此外在mysql中没有full outer join,但是可以“曲线”解决

-- full outer join同时使用左外连接和右外连接,然后两个集合取并集union
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.name = table2.name
UNION
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.name =table2.name
文章目录