文章目录
题1
- 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
- 查询同时存在" 01 “课程和” 02 "课程的情况
- 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
- 查询不存在" 01 “课程但存在” 02 "课程的情况
解释
题1.1:查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
首先明确我们需要查询的表是成绩表(SC
)的信息。
最简单的查询是课程01和课程02的信息有哪些,例如查询课程01
mysql> SELECT * FROM SC WHERE SC.CId="01";
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 01 | 80.0 |
| 02 | 01 | 70.0 |
| 03 | 01 | 80.0 |
| 04 | 01 | 50.0 |
| 05 | 01 | 76.0 |
| 06 | 01 | 31.0 |
+------+------+-------+
6 rows in set (0.01 sec)
其实就是通过SC表得到2张子表,笛卡儿积的方法合并两张表,然后通过WHERE
条件进行筛选
mysql> SELECT * FROM (SELECT * FROM SC WHERE SC.CId="01") AS biao01,(SELECT * FROM SC WHERE SC.CId="02") AS biao02 WHERE biao01.score > biao02.score AND biao01.SId = biao02.SId;
+------+------+-------+------+------+-------+
| SId | CId | score | SId | CId | score |
+------+------+-------+------+------+-------+
| 02 | 01 | 70.0 | 02 | 02 | 60.0 |
| 04 | 01 | 50.0 | 04