第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操作符后面的括号里面,是明确列出了一个姓牛老师的老师编号的集合,而上面的第1步的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】
  1. Hey,

    It was nice speaking to you the other day, this is the service I was telling you about that helped us boost our ROI almost 2000%

    Its a company called Lifemail.studio sorry it took so long to get back to you. They allow you to send any email doesn’t matter what.

    We dealt with a guy named Michael, he was friendly and got us setup really quickly.

    Regards,
    Giselle

发表评论

邮箱地址不会被公开。

Captcha Code