查询简介(五)之子查询

小孩子 我们都是小青蛙 2018-05-23

点击蓝字,关注我们


关注

阅读正文之前一定要仔细阅读下边这些建议:

1. 最好使用电脑观看。

2. 如果你非要使用手机观看,那请把字体调整到最小,这样观看效果会好一些。

3. 碎片化阅读并不会得到真正的知识提升,要想有提升还得找张书桌认认真真看一会书,或者我们公众号的文章

4. 如果觉得不错,各位帮着转发转发,如果觉得有问题或者写的哪不清晰,务必私聊我~

5. 本公众号的文章都是需要被系统性学习的,在阅读本篇文章前最好已经阅读过下边几篇文章,要不然可能会有阅读不畅的体验:


多表查询的需求

上边介绍的查询语句都是作用于单个表的,但是有时候会有从多个表中查询数据的需求,比如我们想查一下名叫'杜琦燕'的学生的各科成绩该怎么办呢?我们只能先从student_info表中根据名称找到对应的学生学号,然后再通过学号到student_score表中找着对应的成绩信息,所以我们只能写成这样的两个查询:

标量子查询

我们回过头再仔细看看这两条查询语句,第二条查询语句的搜索条件其实是用到了第一条查询语句的查询结果,所以为了方便,我们可以把它们合并到一条语句中,就像这样:

我们把第二条查询语句用小括号()扩起来直接放到了第一条的搜索条件处,这样就起到了合并两条查询语句的作用。小括号中的查询语句也被称为子查询或者内层查询,使用内层查询的结果作为搜索条件的查询称为外层查询。如果你在一个查询语句中需要用到更多的表的话,那么在一个子查询中可以继续嵌套另一个子查询,在执行查询时,将按照从内到外的顺序依次执行这些查询。

在这个例子中的子查询的结果只有一个值(也就是'杜琦燕'的学号),这种子查询称之为标量子查询。正因为标量子查询单纯的代表一个值,所以它可以作为表达式的操作数来参与运算,它除了用在外层查询的搜索条件以外,也可以作为查询对象放在查询列表处,比如这样:

mysql> SELECT (SELECT number FROM student_info WHERE name = '杜琦燕') AS 学号;
+----------+
| 学号     |
+----------+
| 20180102 |
+----------+
1 row in set (0.00 sec)
mysql>

因为标量子查询单纯的代表一个值,所以外层查询的搜索条件想怎么写就怎么写喽,只要符合布尔表达式的语法就可以,比方说我们来查询学号大于'杜琦燕'的学号的学生成绩,可以这么写:

这样查出来的成绩记录中的学号都是大于'杜琦燕'的学号。

IN和NOT IN子查询

有时候子查询的结果并不是单纯的一个值,而是多个值,比如我们想查询'计算机科学与工程'专业的学生的成绩,那我们可以拆成下边两个查询:

很显然对于子查询结果是多个值的情况,需要使用IN这种多值匹配操作符,所以我们可以把上边两个语句合并成这样:

NOT ININ的使用差不多,就不赘述了。

多列子查询

上边例子中出现的子查询的结果集里只有一个列,其实子查询的结果集也可以有多个列的,只不过在子查询的查询列表和外层查询WHERE子句的搜索条件要匹配!比如我们可以这么写:

在这个例子中的子查询的查询列表是number, '母猪的产后护理'number是列名,'母猪的产后护理'是一个常数,所以在外层查询的WHERE子句的搜索条件里也需要两个表达式,例子中使用的是`(number, subject)。需要注意的是,WHERE子句的搜索条件中多个表达式需要用小括号()括住

EXISTS和NOT EXISTS子查询

有时候外层查询并不关心子查询中的结果是什么,而只关心子查询的结果是不是为空,这时可以用到下边这两个操作符:

我们来举个例子:

mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20180108);
Empty set (0.00 sec)

mysql>

这个例子的子查询的意思是在student_info表中查找学号为20180108的学生信息,很显然并没有学号为20180108的学生,所以子查询没有结果,EXISTS表达式的结果为FALSE,所以外层查询也就不查了,直接返回了一个Empty set,表示没有结果。你可以自己试一下NOT EXISTS的使用。

相关子查询

前边介绍的子查询和外层查询都没有依赖关系,也就是说可以独立运行完子查询得到结果之后,再拿结果作为外层查询的搜索条件去执行外层查询,这种子查询称为不相关子查询,比如下边这个查询:

子查询中只用到了student_info表而没有使用到student_score表,这就是一种典型的不相关子查询

而有时候我们需要在子查询的语句中引用到外层查询的值,这样的话子查询就不能当作一个独立的语句去执行,这种子查询方式称为相关子查询。比方说我们想查看一些学生的基本信息,但是前提是这些学生有成绩记录,那可以这么写:

首先需要注意的是,student_infostudent_score表里都有number列,所以在子查询的WHERE语句中书写number = number会造成二义性,也就是让服务器懵逼,不知道这个number列到底是哪个表的,所以为了区分,在列名前边加上了表名,并用点.连接起来,这种显式的将列所属的表名书写出来的名称称为该列的全限定名。所以上边子查询的WHERE语句中用了列的全限定名:student_score.number = student_info.number

这条查询语句可以分成这么两部分来理解

  • 我们要查询学生的一些基本信息。

  • 这些学生必须符合这样的条件:必须在student_score表中有记录

所以这个例子中的相关子查询的查询过程是这样的:

  1. 先执行外层查询获得到student_info表的第一条记录,发现它的number值是20180101。把20180101当作参数传入到它的子查询,此时子查询的使意思判断student_score表的number字段是否有20180101这个值存在,子查询的结果是该值存在,所以整个EXISTS表达式的值为TRUE,那么student_info表的第一条记录可以被加入到结果集。

  2. 再执行外层查询获得到student_info表的第二条记录,发现它的number值是20180102,与上边的步骤相同,student_info表的第二条记录也可以被加入到结果集。

  3. 与上边类似,student_info表的第三条记录也可以被加入到结果集。

  4. 与上边类似,student_info表的第四条记录也可以被加入到结果集。

  5. 再执行外层查询获得到student_info表的第五条记录,发现它的number值是20180105,把20180105当作参数传入到它的子查询,此时子查询的使意思判断student_score表的number字段是否有20180105这个值存在,子查询的结果是该值不存在,所以整个EXISTS表达式的值为FALSE,那么student_info表的第五条记录就不被加入结果集中。

  6. 与上边类似,student_info表的第六条记录也不被加入结果集中。

  7. student_info表没有更多的记录了,结束查询。

所以最后的查询结果是上边的4条记录。如果你觉得相关子查询还是有点儿绕的话,那就返回去再重新看几遍这个查询过程。需要注意的是,相关子查询通常都用在EXISTSNOT EXISTS子查询里,用于匹配在某个表里查找在另一个表里有匹配行或者没有匹配行的行

对同一个表的子查询

其实不只是在查询多表的时候会用到子查询,在单个表中有时也会用到子查询。比方说我们想看看在student_score表的'母猪的产后护理'这门课的成绩中,有哪些超过了平均分的记录,脑子中第一印象是这么写:

mysql> SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > AVG(score);
ERROR 1111 (HY000): Invalid use of group function
mysql>

需要特别特别特别注意的是:聚集函数不能放到WHERE子句中!!!因为WHERE子句是针对每一条记录来进行过滤的,而聚集函数只能用于过滤分组。所以如果我们想实现上边的需求,就需要搞一个student_score表的副本,就相当于有了两个student_score表,在一个表上使用聚集函数统计,统计完了之后拿着统计结果再到另一个表中进行过滤,这个过程可以这么写:

我们使用子查询先统计出了'母猪的产后护理'这门课的平均分,然后再到外层查询中使用这个平均分作为过滤条件来查找大于平均分的记录。这样看上去student_score表就像是有了一个副本一样~

子查询注意事项

  1. 标量子查询的结果只有一个值,如果结果有多个值的话会报错,可以在查询语句末尾使用LIMIT 1来保证只有一条结果。

  2. 子查询的查询列表和外层查询WHERE子句的搜索条件要匹配!如果外层查询的WHERE子句中的表达式个数大于1个,需要用小括号()扩起来。

  3. 在引用的列可能出现二义性时,必须使用列的全限定名(也就是用一个点.分隔的表名和列名)来注明该列所属的表。

  4. 相关子查询是涉及外部查询的子查询,一般用在EXISTSNOT EXISTS子查询里。

题外话

写文章挺累的,有时候你觉得阅读挺流畅的,那其实是背后无数次修改的结果。如果你觉得不错请帮忙转发一下,万分感谢~



    本站仅按申请收录文章,版权归原作者所有
    如若侵权,请联系本站删除
    觉得不错,分享给更多人看到
    我们都是小青蛙 热门文章:

    java并发编程之原子性操作    阅读/点赞 : 0/0

    我们都是小青蛙,呱呱呱呱呱    阅读/点赞 : 0/0

    活跃性(死锁、饥饿、活锁)    阅读/点赞 : 0/0

    InnoDB空间文件布局的基础知识    阅读/点赞 : 0/0

    指令重排序    阅读/点赞 : 0/0

    InnoDB的Buffer Pool简介    阅读/点赞 : 0/0

    一些比较重要的数字电路模块    阅读/点赞 : 0/0

    《UNIX环境高级编程》书籍推荐    阅读/点赞 : 0/0

    InnoDB中的B+树索引结构    阅读/点赞 : 0/0

    InnoDB索引页面的物理结构    阅读/点赞 : 0/0

    我们都是小青蛙 微信二维码

    我们都是小青蛙 微信二维码