多表查询的需求
查询杜子腾的各科成绩
首先查询 学生的ID,再根据id 去查询各科成绩
mysql> SELECT number FROM student_info WHERE name = '杜子腾';
SELECT * FROM student_score WHERE number = 20200803;
将两句合并
mysql> SELECT * FROM student_score \
WHERE number = (SELECT number FROM student_info WHERE name = '杜子腾');
所有的子查询都必须用小括号扩起来，否则是非法的
标量子查询
标量子查询单纯的代表一个值，由标量子查询作为的操作数组成的搜索条件只要符合表达语法就可以
如上述的例子就是标量子查询
mysql> SELECT * FROM student_score \
WHERE number = (SELECT number FROM student_info WHERE name = '杜子腾');
mysql> SELECT * FROM student_score \
WHERE number > (SELECT number FROM student_info WHERE name = '杜子腾');
列子查询
列子查询，就是我们的子查询语句的结果是一列
mysql> SELECT number FROM student_info WHERE major = '计算机科学与工程';
+----------+
| number |
+----------+
| 20180101 |
| 20180102 |
+----------+
正常查询
mysql> SELECT * FROM student_score WHERE number IN (20180101, 20180102);
使用子查询
SELECT * FROM student_score \
WHERE number \
IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
行子查询
子查询结果是一行记录
SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母猪的产后护理' FROM student_info LIMIT 1);
+----------+-----------------------+-------+
| number | subject | score |
+----------+-----------------------+-------+
| 20180104 | 母猪的产后护理 | 55 |
+----------+-----------------------+-------+
表子查询
mysql> SELECT * FROM student_score \
WHERE (number, subject) IN \
(SELECT number, '母猪的产后护理' FROM student_info WHERE major = '计算机科学与工程');
EXISTS和NOT EXISTS子查询
有时候，外层查询并不关心子查询的具体结果，只关心是否查到了数据
SELECT * FROM student_score \
WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
Empty set (0.00 sec)
# 显然，并没有20180108学号的学生
不相关子查询和相关子查询
# 不相关子查询，子查询可以独立运行
mysql> SELECT * FROM student_score \
WHERE number = (SELECT number FROM student_info WHERE name = '杜琦燕');
# 相关子查询, 子查询有用到外层查询的数据
mysql> SELECT number, name, id_number, major FROM student_info \
WHERE EXISTS \
(SELECT * FROM student_score WHERE student_score.number = student_info.number);
对同一个表的子查询
mysql> SELECT * FROM student_score \
WHERE subject = '母猪的产后护理' \
AND score > (SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理');
111
