首页 > 数据库技术 > 详细

SQL 日常练习 (十七)

时间:2020-05-02 09:06:42      阅读:54      评论:0      收藏:0      [点我收藏+]

五一了, 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 即将成为我第二大武器

SQL 日常练习 (十七)

原文:https://www.cnblogs.com/chenjieyouge/p/12815612.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!