五一了, 2020过去近乎一半了, 疫情原因, 哪都没去, 其实与其出去玩, 不如呆着学习, 终身学习, 学无止境, 气有浩然, 这是我从上大学开始一直刻在脑海的训诫. 都说今年很艰难, 回头一想, 其实对于我来说, 根本就没啥影响, 我们做 IT 类工作, 只要一台电脑就可以, 而且我一直在工作, 从2月10号就复工, 没有远程, 一直在现场. 尤其是 刚过去的 黑色 4月, 从我的下班打卡时间就知道, 被客户折磨得多惨. 不过也是学习过程, 写了很多的 sql, 以前 用 Python, 就是 万物皆对象, 现在用 sql , 就是万物皆 SQL.
劳动节, 体力就免了, 脑力还是不能停的. 虽然这个五一, 我应该是不找小伙伴了, 但大学舍友结婚和初高中同学也结婚, 这个凑份子还是少不了, 其实更多是羡慕吧. 好想有个对象, 不然也不会每天都看看代码, 写写sql 来麻痹自己..
继续练习吧, 耐心和恒心, 总会获得回报的.
表关系
似乎, 这些练习也快要写完了, 事实证明, 工作中其实用到的也是这些, 字段筛选, 表格连接, 分组聚合, 行列转换等的功能, 写了很多之后, 真的感受到, 万物皆SQL .
需求 01
查询 选修 "仲尼" 老师所教授课程 的学生中 成绩最高的学生姓名, 和成绩
分析
这个例子蛮好, 成功将 4张表都串联到一起了.
先从 teacher 中, 查出 "仲尼" 老师 教师 id; 再 通过 教师 id, 从 course 表中, 查到其教授的课程 (这里是 1:1 的); 再从 score 表中, 查出选该课程的学生 id , 成绩, 同时 inner join 从 student 出姓名..
以前我是不太会, 现在这种sql 就是, 信手拈来呀.
现在整个暴力版的, 就是将涉及的表, 都先给他一顿爆连, 拼张大表, 然后字段要啥取啥.
select
b.*,
c.*,
d.*
from teacher as a
inner join course as b
on a.t_id = b.t_id
inner join score as c
on b.c_id = c.c_id
inner join student as d
on c.s_id = d.s_id
这样先来全部暴力敲一遍
+------+--------+------+--------+------+------+------+-------+------+-----------+------------+--------+
| t_id | t_name | c_id | c_name | t_id | s_id | c_id | score | s_id | s_name | birth_date | gender |
+------+--------+------+--------+------+------+------+-------+------+-----------+------------+--------+
| 0002 | 仲尼 | 0001 | 语文 | 0002 | 0001 | 0001 | 80 | 0001 | 王二 | 1989-01-01 | 男 |
| 0001 | 欧拉 | 0002 | 数学 | 0001 | 0001 | 0002 | 90 | 0001 | 王二 | 1989-01-01 | 男 |
| 0003 | NULL | 0003 | 英语 | 0003 | 0001 | 0003 | 99 | 0001 | 王二 | 1989-01-01 | 男 |
| 0001 | 欧拉 | 0002 | 数学 | 0001 | 0002 | 0002 | 60 | 0002 | 星落 | 1990-12-21 | 女 |
| 0003 | NULL | 0003 | 英语 | 0003 | 0002 | 0003 | 80 | 0002 | 星落 | 1990-12-21 | 女 |
| 0002 | 仲尼 | 0001 | 语文 | 0002 | 0003 | 0001 | 80 | 0003 | 胡小适 | 1991-12-21 | 男 |
| 0001 | 欧拉 | 0002 | 数学 | 0001 | 0003 | 0002 | 80 | 0003 | 胡小适 | 1991-12-21 | 男 |
| 0003 | NULL | 0003 | 英语 | 0003 | 0003 | 0003 | 80 | 0003 | 胡小适 | 1991-12-21 | 男 |
+------+--------+------+--------+------+------+------+-------+------+-----------+------------+--------+
8 rows in set (0.00 sec)
拼完再按条件过滤出需要的来.
select
b.c_name,
c.score,
d.s_name
from teacher as a
inner join course as b
on a.t_id = b.t_id
inner join score as c
on b.c_id = c.c_id
inner join student as d
on c.s_id = d.s_id
-- 过滤条件写在这里
where a.t_name = '仲尼'
order by c.score desc limit 0, 1
+--------+-------+--------+
| c_name | score | s_name |
+--------+-------+--------+
| 语文 | 80 | 王二 |
+--------+-------+--------+
1 row in set (0.00 sec)
果然王二很厉害呀.
不想写子查询了, 这写起来就有些烦, 之前写过一次, 就不想在写了, 工作中如有很长的 sql , 我就会考虑是否要建一个视图 view 来简化 sql 的编写, 毕竟, 真的太长了.
不行, 还是要严格要求自己, 不能总是和自己妥协.
-- 教师id
(
select t_id from teacher where t_name = '仲尼'
)
+------+
| t_id |
+------+
| 0002 |
+------+
1 row in set (0.00 sec)
-- 课程 id
select c_id from course
where t_id =
(select t_id from teacher where t_name = '仲尼')
+------+
| c_id |
+------+
| 0001 |
+------+
1 row in set (0.00 sec)
-- 学生 id 和 课程成绩
select
s_id,
score
from score
where c_id = (
select c_id from course
where t_id =
(select t_id from teacher where t_name = '仲尼')
)
+------+-------+
| s_id | score |
+------+-------+
| 0001 | 80 |
| 0003 | 80 |
+------+-------+
2 rows in set (0.00 sec)
最后再根据 s_id 从 student 中给 join 进来, 这一坨查询集作为一个, 套娃表, b表, 与 student 做内连接
select
b.s_id as 学号,
b.s_name as 姓名,
a.score as 成绩
from (
select
s_id,
score
from score
where c_id = (
select c_id from course
where t_id =
(select t_id from teacher where t_name = '仲尼')
)
) as a
inner join student as b
on a.s_id = b.s_id
order by score desc
limit 0, 1 -- 游标0开始, 取1条
+--------+--------+--------+
| 学号 | 姓名 | 成绩 |
+--------+--------+--------+
| 0001 | 王二 | 80 |
+--------+--------+--------+
1 row in set (0.00 sec)
小结
- 多表查询的, 对于 子查询和 join 需要配合起来用, 尤其是中间逻辑要全部理清楚
- sql 中间查询集 其实跟用 DataFrame 是一样的, 也是在不断返回自身数据结构的过程
- sql 练习非常必要, 这绝对是入坑一时爽, 一直蹲坑一直爽, sql 即将成为我第二大武器