SQL 日常练习 (十四)

最近的项目都比较忙, 没太有时间来做练习, 不过 sql 这块, 还是始终要保持良好的手感, 我已经渐渐感觉到, 随着写得越来越多, 当然不只是在这里, 更多是在工作中, 不过涉及信息安全不能共享. 结论是, sql 写好了, 能够解决一大堆的问题哦. 今天被客户恶心到了, 要给台账建一个表, 做一个填报 ... 100来个字段, 尤其是在给表字段取名的时候, 我感觉, 我的英文词汇量,配不上辣么多的字段....纯手打的方式, 我也渐渐喜欢了, 比起智能提示, 不如记事本来直接写 sql 得了. 加完班, 基础的 sql 还是继续练起来呀, 继续网上搬搬砖, 写几句.

表关系

!

需求 01

查询平均成绩大于等于 85 的 所有学生的学号, 姓名, 平均成绩

分析

这就跟直接的那个用 max(case when ..) 列字段 横向展开是一样的呀, 平均成绩.. 聚合, 必然要做 group by

直接来看看, 平均分 >= 85 是哪些人.

select 
  s_id,
  avg(score) as avg_score
from score
group by s_id
having avg_score >= 85

+------+-----------+
| s_id | avg_score |
+------+-----------+
| 0001 |   89.6667 |
+------+-----------+
1 row in set (0.01 sec)

有了 s_id, 匹上姓名即可呀. inner join 即可

select 
  a.s_id,
  b.s_name,
  avg(a.score) as avg_score
from score as a

-- 根据 s_id 匹配上 s_name
inner join student as b 
  on a.s_id = b.s_id

group by a.s_id, b.s_name
  having avg_score >= 85

+------+--------+-----------+
| s_id | s_name | avg_score |
+------+--------+-----------+
| 0001 | 王二   |   89.6667 |
+------+--------+-----------+
1 row in set (0.00 sec)

是蛮简单的. 当然也可能是我最近业务熟悉比较多一点, 因此写起来 sql 也就更顺畅一些了呀.

需求 02

查询 每门课程的平均成绩, 按平均成绩 升序排列, 平均成绩相同, 按课程号 降序排列

分析

核心点是 group by 和 order by . 其实也是非常简单的一个练手.

select

  a.c_id,
  b.c_name,
  avg(a.score) as avg_score

from score as a

-- 匹配上课程名称
inner join course as b
  on a.c_id = b.c_id 

group by a.c_id, b.c_name

order by avg_score asc, a.c_id desc

| c_id | c_name | avg_score |
+------+--------+-----------+
| 0002 | 数学   |   76.6667 |
| 0001 | 语文   |   80.0000 |
| 0003 | 英语   |   86.3333 |
+------+--------+-----------+
3 rows in set (0.00 sec)

需求 03

查询课程名称为 "数学", 且分数低于 90 分的学生姓名, 分数

分析

这种毫无难度的, 直接秒杀它. 哦, 也不能说毫无难度, 起码它是要涉及 score, course, student 三张表的哦

select
  c.s_id,
  c.s_name,
  a.score as 数学成绩
from score as a 

-- 匹配上课程名称
inner join course as b 
  on a.c_id = b.c_id 

-- 匹配上学生姓名
inner join student as c 
  on a.s_id = c.s_id

where (a.score < 90) and (b.c_name = '数学')

+------+-----------+--------------+
| s_id | s_name    | 数学成绩     |
+------+-----------+--------------+
| 0002 | 星落      |           60 |
| 0003 | 胡小适    |           80 |
+------+-----------+--------------+
2 rows in set (0.00 sec)

小结

• 日常联系 group by 和 order by 这些常用的 (只要涉及聚合, 必然 group by )
• Join 即表之间的关联上, 或者说拼接上, 脑海有画面, 基本就成功了, Join 真的太强大
• sql 不断练习才能掌握, 这是一个必须要走过的路程, 在路上, 我开始加速了.