SQL 强化练习 (四)

继续每日一练 sql 肯定会无敌强的后面. 恰好今天项目弄报表这块, 用了国产的 BI 工具, 帆软Report, 除了页面控件做得有些 low 外, 我感觉其他各方面都特别适合做 中国式的报表. 明天上线, 今天才开始弄填报, 要设计表字段, 全部要自己来写 sql, 恰好练习了把, 果然, sql 再数据这块, 真的是无比重要的哦.

发现这个 BI 有点好, 就是可以做模板成网页, 然后让业务自己往里导数据, 数据回写数据库, 感觉贼简单好用哦

sql 搞来搞去, 还是这些, 筛选字段, 表拼接, 分组聚合... (ps: 今天写 DDL 反复改, 差点没把领导给气死.... )

表关系

需求_01

查询 学过 "欧拉" 老师所教的所有课的学生学号, 姓名

分析

分析: 学生表 inner join 成绩表 inner join 课程表 inner join 教师表

select 
  st.s_id as "学号", 
  st.s_name as "姓名"

from student as st 
inner join score as s on st.s_id = s.s_id
inner join course as c on s.c_id = c.c_id 
inner join teacher as t on c.t_id = t.t_id

where t.t_name = "欧拉"

-- 多名学生, 可按学号排序
order by st.s_id;

+--------+-----------+
| 学号   | 姓名      |
+--------+-----------+
| 0001   | 王二      |
| 0002   | 星落      |
| 0003   | 胡小适    |
+--------+-----------+
3 rows in set (0.00 sec)

关键点还是理解表结构逻辑和, 关联关系的呀. 但是呢, 这里有个很大的问题, 关于查询效率. 这里是先把所有的表都给拼接起来嘛, 通常业务中呢, 其实每个表都是非常大的, 这样直接来 inner join 怕是要卡死. 更通常的做法是, 关联一张的时候, 就临时表安排一下, 然后慢慢拼, 先 where 过滤, 再来查询以提高效率.

需求02

查询 同时学过 0001 和 0002 号课程的学生学号, 姓名

分析

对 score 进行拆分按课号, 然后以学号为 inner join

select * from score where c_id = "0001";
+------+------+-------+
| s_id | c_id | score |
+------+------+-------+
| 0001 | 0001 |    80 |
| 0003 | 0001 |    80 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> select * from score where c_id = "0002";
+------+------+-------+
| s_id | c_id | score |
+------+------+-------+
| 0001 | 0002 |    90 |
| 0002 | 0002 |    60 |
| 0003 | 0002 |    80 |
+------+------+-------+
3 rows in set (0.00 sec)

可以看到, 二者相交的学号是 1, 3 , 其实就 inner join 就行啦.

-- 都给查出来看看
select 
  a.*,
  b.*
from

(select * from score where c_id = "0001") as a

inner join

(select * from score where c_id = "0002") as b

on a.s_id = b.s_id;

+------+------+-------+------+------+-------+
| s_id | c_id | score | s_id | c_id | score |
+------+------+-------+------+------+-------+
| 0001 | 0001 |    80 | 0001 | 0002 |    90 |
| 0003 | 0001 |    80 | 0003 | 0002 |    80 |
+------+------+-------+------+------+-------+
2 rows in set (0.00 sec)

多表关联的好处就是, 取了表的别名后, 你想要哪个就可以拿到哪个字段, 大表就是好用的呀.

只是需要学号和姓名, 那其实只查学号, in 学生表即可

select

 s_id as "学号", 
 s_name as "姓名"

from student 

where s_id in (

  select 
    a.s_id
  from

  (select s_id from score where c_id = "0001") as a

  inner join

  (select s_id from score where c_id = "0002") as b

  on a.s_id = b.s_id

);

+--------+-----------+
| 学号   | 姓名      |
+--------+-----------+
| 0001   | 王二      |
| 0003   | 胡小适    |
+--------+-----------+
2 rows in set (0.00 sec)

小结

• 多表连接查询, 其实只要搞清楚表之间的连接字段,实在不行, 都给它连上拼接一张大表, 要啥有啥.
• 从提高查询效率上考虑, 不要一下拼接出所有表, 而可选择临时表的形式, 和先给做过滤再拼接
• 灵活使用 join 和 in 即 表连接 和 子查询 的相互配合, 这样就非常厉害了哦.