本节要点
使用子查询作为计算字段
使用子查询过滤数据(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)