第15课：方便的子查询及EXISTS

本节要点

使用子查询作为计算字段
使用子查询过滤数据（IN）
使用子查询过滤数据（EXISTS）

使用子查询作为计算字段

假如有一张老师表teacher(teacher_id,teacher_name,gender)和一张学生表student(student_id,student_name,gender,birth_day,age,class_id,score,teacher_id)，考虑一下下面这个查询需求：

（1）、如何同时查询出学生编号、学生姓名、老师编号、老师姓名？

因为学生表中只有老师编号，没有老师姓名，而老师姓名保存在另一个老师表中。那这个查询需求，就需要用到两张表。

对于这个查询需求，就可以使用到我们这节课讲解的子查询。先来看下这个查询需求，SQL语句应该怎么写：

SELECT
student_id,
student_name,
teacher_id,
(
    SELECT teacher_name
    FROM teacher
    WHERE teacher.teacher_id = student.teacher_id
)
FROM student;

在这个SQL语句里，有两个SELECT，也就是说，在一个SELECT语句里嵌套了另一个SELECT语句。这就是子查询，是子查询作为SELECT的一个计算字段的写法。

在子查询的内部，使用了student表名来引用外部student表的字段，使用teacher表名来引用teacher表的字段。

其实，我们可以简化一些，为这两张表各取一个别名，那么在使用的时候，就可以使用表别名来引用表中的字段。就像下面这个SQL：

SELECT
student_id,
student_name,
teacher_id,
(
    SELECT teacher_name
    FROM teacher b
    WHERE b.teacher_id = a.teacher_id
)
FROM student a;

这个SQL语句与上面的SQL语句是等价的。

这里同时使用到了student表中的teacher_id字段和teacher表中的teacher_id字段，为了明确指定到底是哪个表的teacher_id字段，所以使用了表别名。

其实，如果在SQL语句中，对于使用的字段没有歧义，也就是说，没有同时使用到两个表的相同字段，表别名也是可以省略的。

另外，我们假设一下，如果teacher表中，相同的teacher_id具有多条记录，那么对于同一个学生表中的teacher_id的值，可能会在子查询中查到多条记录，这时候应该返回哪一条呢？

实际上，如果发生这样的情况，执行的时候是会报错的：SQL执行错误 #1242 从数据库的响应 Subquery returns more than 1 row。

使用子查询过滤数据（IN）

再考虑下这个查询需求：

（1）、如何获取姓牛的老师教了哪些学生？

一般来说，可能需要分两步来实现。

第1步：从老师表中查出所有姓牛的老师。

SELECT
    teacher_id,teacher_name
FROM teacher
WHERE teacher_name like '牛%’;

第2步：使用IN操作符，将姓牛老师的编号列出来，然后从学生表中查出需要的数据。

SELECT
    student_id,student_name
FROM student
WHERE teacher_id IN ('T0010','T0011');

一般情况下，分两步来写，也能得到我们想要的结果。可是也存在两个问题。

问题一：如果姓牛的老师太多（比如超过了500人），如果将所有老师的编号都列出来，那SQL语句就太长了；

问题二：这样写，也需要两步来实现我们的查询需求，实现起来太复杂了；

那么，可不可以使用一句简单的SQL，就搞定这个查询需求呢？

实际上，我们知道，在IN操作符后面的括号里面，是明确列出了一个姓牛老师的老师编号的集合，而上面的第１步的SELECT语句，也是返回了一个相同的集合。

那么，也就可以使用SELECT语句替换掉IN操作符后面括号里的内容，像下面这样：

SELECT
    student_id,student_name
FROM student
WHERE teacher_id IN ( SELECT teacher_id FROM teacher
    WHERE teacher_name like '牛%'
);

这是使用子查询配合IN操作符来过滤数据的一种写法。

是不是比前面的两步操作要简单的多了。

使用子查询过滤数据（EXISTS）

我们还可以使用子查询配合EXISTS关键字来实现上面的需求：

SELECT
    student_id,student_name
FROM student a
WHERE EXISTS ( SELECT 1 FROM teacher b
    WHERE a.teacher_id = b.teacher_id
    AND b.teacher_name like '牛%'
);

EXISTS是存在的意思。从WHERE后面的子查询来看，也就是说，存在一个学生的老师编号，在老师表里，对应的老师姓牛。

那既然有存在的写法，当然也有不存在的写法。

（1）、如何获取除姓牛的老师之外的其他老师教了哪些学生？

SELECT
    student_id,student_name
FROM student a
WHERE NOT EXISTS ( SELECT 1 FROM teacher b
    WHERE a.teacher_id = b.teacher_id
    AND b.teacher_name like '牛%'
);

写起来也很简单，就是在EXISTS前面加上了一个NOT，用来表示否定后面的条件。

课后习题：

1、对于第6课课后习题中的论坛注册用户表，以及如下文章表和用户文章记录表，请编写SQL完成以下查询。

文章表	文章编号
	文章标题
	文章分类	主要分：财经、科技、体育、游戏、健康五大类
	文章内容
	文章作者用户编号	来源于注册用户表中的用户编号
	发表时间
	访问次数
	评论次数
用户访问的文章记录表	文章编号
	访问用户编号
	访问日期
	访问时间
	IP地址
	浏览器类型	主要有：IE、FIREFOX、CHROME、360、QQ、傲游、其他

（1）、查询访问过财经类文章的所有用户。

（2）、分组统计每个文章分类 的总访问次数及评论次数。

（3）、查询每个文章分类下访问次数最多的文章。

（4）、查询2018年游戏分类每个省份的用户访问次数。

（5）、查询2018年每个月每种类型的浏览器访问科技类文章的次数。

（6）、查询2018年以来，一天24小时，每个小时的平均访问用户数及访问次数。

（7）、使用子查询语句，查询访问文章数超过10篇的用户信息。

2、对于学生表中的所有同学的成绩，如何查出比该班平均成绩高的学生信息？（使用子查询实现）（题目来源：@Yeeshine）

【版权说明：仅允许非商业转载且请注明出处：Mac私塾 网址：http://macsishu.com】