MySQL子查询详解

后端 潘老师 3个月前 (12-09) 77 ℃ (0) 扫码查看

本文主要讲解关于MySQL子查询详解相关内容,让我们来一起学习下吧!

​概念

子查询指一个查询语句嵌套在另一个查询语句内部的查询。很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算得出一个数据结果,然后与这个数据结果(可能是某个标量,也可能是某个集合)进行比较。

例:想查询工资比BLACK的工资高的员工的信息

SELECT ename , sal 
FROM emp
WHERE sal > (
            SELECT sal 
            FROM emp 
            WHERE ename = 'BLAKE'
            );

外部的查询称为外查询(或主查询),内部的查询称为内查询(或子查询)。

注:

  • 子查询在主查询之前执行完成。
  • 子查询的结果被主查询使用。
  • 子查询要包含在括号内。
  • 将子查询放在比较条件的右侧(建议)。
  • 单行操作符对应单行子查询,多行操作符对应多行子查询。

子查询的分类:

角度一:单行子查询、多行子查询

单行子查询返回一条记录,多行子查询返回多条记录。

角度二:相关子查询、不相关子查询(或关联子查询和不关联子查询)(即从内查询是否执行多次的角度)

  •  子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件执行,那么这样的子查询称为不相关子查询。
  • 如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询。

单行子查询:

单行比较操作符:

  • 子查询可以放在任何可以用“值”表示的位置。比如WHERE、HAVING里的表达式中,SELECT中的字段中,CASE表达式中等等。
  • 子查询只返回一个结果值,不会返回行。
  • 子查询查询不到结果则返回NULL。
  • 多行子查询的结果不能放在单行比较操作符后使用。

例,在CASE表达式中使用单行子查询:

SELECT empno , ename , CASE deptno
WHEN (
      SELECT deptno
      FROM dept
      WHERE loc = 'NEW YORK'
     ) 
THEN 'AAA'
ELSE 'BBB'
END 'location' #别名
FROM emp;

多行子查询:

多行子查询也称为集合比较子查询,返回多行,使用多行比较操作符。

多行比较操作符:

可以将多行子查询的结果当作一张表使用,但是,必须要给此表取别名。

例如:

SELECT MIN(sal_avg)
FROM (
      SELECT AVG(sal) sal_avg
      FROM emp
      GROUP BY deptno
     ) das; -- das即为此表的别名。

多行子查询中的空值问题:

如果多行子查询的结果的其中一条中出现NULL,用NOT IN加此子查询的结果为空,因为NOT IN实际上会用<>来与每个结果比较,若结果中有空值那么 <>NULL的结果就是NULL,正确的做法应该是将多行子查询的结果中的NULL值过滤掉。

例:

SELECT ename
FROM emp
WHERE NOT IN (
              SELECT mgr
              FROM emp
              WHERE mgr IS NOT NULL
             );

相关子查询:

如果相关子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重写计算一次,这样的子查询就是关联子查询。

相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。

例,查询员工中工资大于本部门平均工资的员工的姓名、薪资、和部门号(使用相关子查询):

SELECT ename , sal , deptno
FROM emp e1
WHERE sal > (
             SELECT AVG(sal)
             FROM emp e2
             WHERE deptno = e1.deptno   #子查询要与主查询做相关联,即主查询中的每一行的执行都要重新执行一次子查询。
            );

在SELECT中除了GROUP BY和LIMIT之外,其他的位置都可以使用子查询。

EXISTS与NOT EXISTS

关联子查询通常也会和EXISTS操作符一起来使用,用来检查子查询中是否存在行。

每一行执行时:

如果在子查询的结果中不存在行,返回FALSE。

如果在子查询的结果中存在行,返回TRUE。

返回FALSE即主查询中的相应的行不会成为主查询的结果,返回TRUE即主查询中的相应的行会成为主查询的结果。

NOT EXISTS表示如果在子查询的结果中不存在行,则返回TRUE,否则返回FALSE(与EXISTS相反)。

EXISTS后的子查询并不会返回行,只会返回TRUE或FALSE。

例:

SELECT empno , ename , job , deptno
FROM emp e1
WHERE EXISTS (
              SELECT *
              FROM emp e2
              WHERE e1.empno = e2.mgr
              );

当员工是属于管理者时,主查询才会接收到子查询返回的TRUE。

相关子查询也可以使用到更新、删除操作中,称为相关更新、相关删除。

多层自查询的三种经典的查询方式:

例,查询公司中平均工资最高的工作职位:

方式一:

步骤:

①    先求各部门的平均工资

②    再到①中的数据中求出最高的平均工资

③    再根据②中的数据对应出最高的平均工资对应的工作职位

SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) = (
                   SELECT MAX(avg_sal)
                   FROM (
                         SELECT AVG(sal) avg_sal
                         FROM emp
                         GROUP BY job
                         ) avg_tab
                  );

方式二:

比起方式一,方式二直接 <= ALL的方式将求最大的平均工资的步骤隐藏在比较操作符中,减少了子查询的次数。

SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) >= ALL (
                        SELECT AVG(sal)
                        FROM emp
                        GROUP BY job    
                        );

方式三:

方式三使用的是先降序排列,再用分页LIMIT的方式,只取第一个记录,取得的即是最高的平均工资,也减少了子查询的次数。

SELECT job
FROM emp
GROUP BY job
HAVING AVG(sal) = (
                   SELECT AVG(sal) avg_sal
                   FROM emp
                   GROUP BY job
                   ORDER BY avg_sal DESC
                   LIMIT 0,1
                  );

以上就是关于MySQL子查询详解相关的全部内容,希望对你有帮助。欢迎持续关注潘子夜个人博客(www.panziye.com),学习愉快哦!


版权声明:本站文章,如无说明,均为本站原创,转载请注明文章来源。如有侵权,请联系博主删除。
本文链接:https://www.panziye.com/back/12331.html
喜欢 (0)
请潘老师喝杯Coffee吧!】
分享 (0)
用户头像
发表我的评论
取消评论
表情 贴图 签到 代码

Hi,您需要填写昵称和邮箱!

  • 昵称【必填】
  • 邮箱【必填】
  • 网址【可选】