【⑦MySQL】· 一文了解四大子查询

待我称王封你为后i 2023-10-12 18:28 84阅读 0赞

#### 前言 ####

> **✨欢迎来到[小K][K]的[MySQL专栏][MySQL]，本节将为大家带来MySQL标量/单行子查询、列子/表子查询的讲解**✨

#### 目录 ####

*   *   *  前言
         *  一、子查询概念
         *  二、标量/单行子查询、列子/表子查询
         *  三、总结

#### 一、子查询概念 ####

> **子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从`MySQL 4.1`开始引入；在特定情况下，一个查询语句的条件需要另一个查询语句来获取，内层查询(`inner query`）语句的查询结果，可以为外层查询(`outer query`）语句提供查询条件。**

内层查询即子查询，外层查询即主查询，只是叫法不同而已

✨**查询需求：**

> 查询公司中工资最低的员工信息
> 
> **工资最低是多少？**

SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);

✨**子查询规范：**

*  子查询必须放在小括号中
 *  子查询一般放在比较操作符的右边，以增强代码可读性
 *  子查询可以出现在几乎所有的`SELECT`字句中（如：`SELECT、FROM、WHERE、ORDER BY、HAVING子句`）

✨**子查询分类：**

1.  根据子查询返回的数据分类：
    
     *  **标量子查询(scalar subquery)**：返回1行1列一个值
     *  **行子查询(row subquery)**：返回的结果集是 1 行 N 列
     *  **列子查询(column subquery)**：返回的结果集是 N 行 1列
     *  **表子查询(table subquery)**：返回的结果集是 N 行 N 列
        
        > 子查询可以返回一个标量(就一个值)、一个行、一个列或一个表，这些子查询分别称之为标量、行、列和表子查询。
2.  **根据子查询和主查询之间是否有条件关联分类：**
    
     *  相关子查询：两个查询之间有一定的条件关联(相互联系)
     *  不相关子查询：两个查询之间没有条件关联(相互独立)
3.  为了方便，对于在何处使用子查询给大家给出几点个人建议：
    
    >  *  子查询出现在**WHERE**子句中：此时子查询返回的结果一般都是单列单行、单行多列、多行单列
    >  *  子查询出现在**HAVING**子句中：此时子查询返回的都是单行单列数据，同时为了使用统计函数操作
    >  *  子查询出现在**FROM**子句中：此时子查询返回的结果图一般都是多行多列，可以按照一张数据表(临时表)的形式操作

#### 二、标量/单行子查询、列子/表子查询 ####

✨✨**标量子查询**

> 子查询返回的是单行单列的数据，就是一个值

*  查询出基本工资比ALLEN低的全部员工信息

SELECT * FROM emp WHERE sal<(SELECT sal FROM emp WHERE eanme='ALLEN');

*  查询基本工资高于公司平均工资的全部员工信息

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

*  查询出与ALLEN从事同一工作，并且基本工资高于员工编号为7521的全部员工信息

SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='ALLEN') 
    AND sal>(SELECT sal FROM emp WHERE empno=7521)
    #把ALLEN自己去掉
    AND ename<>'ALLEN';

✨效果如下：

![在这里插入图片描述][2265468439e74f53b6c398e5b4640cde.png_pic_center]

✨✨**单行子查询**

> 子查询返回的是单行多列的数据，就是一条记录

*  查询与SCOTT从事统一工作且工资相同的员工信息

SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT')
    AND ename<>'SCOTT';

*  查询与员工编号为7566从事统一工作且领导相同的全部员工信息

SELECT * FROM emp WHERE (job,mgr)=(SELECT job,mgr FROM emp WHERE empno=7566)
    AND emp<>7566;

✨效果如下：  
![在这里插入图片描述][c0d67ca3c4c44f25bb3e7b1d2930de80.png_pic_center]

✨✨**列子查询（多行子查询）**

> 子查询返回的是多行单列的数据，就是一列数据。多行子查询也称为集合比较子查询，

在使用多行子查询需要使用多行比较操作符：

<table> 
 <thead> 
  <tr> 
   <th>操作符</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>IN</td> 
   <td>等于列表中的任意一个</td> 
  </tr> 
  <tr> 
   <td>ANY</td> 
   <td>需要和单行比较操作符一起使用(&gt;、&lt;、=、&lt;&gt;…)，与子查询结果中任何一个值比较，一个成立</td> 
  </tr> 
  <tr> 
   <td>ALL</td> 
   <td>需要和单行比较操作符一起使用(&gt;、&lt;、=、&lt;&gt;…)，和子查询返回的所有值比较，同时成立</td> 
  </tr> 
  <tr> 
   <td>SOME</td> 
   <td>实际上是ANY的别名，作用相同，一般用ANY</td> 
  </tr> 
 </tbody> 
</table>

*  **IN操作符**
    
    IN 运算符用来判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。
    
    NOT IN 的作用和 IN 恰好相反，NOT IN 用来判断表达式的值是否不存在于给出的列表中；如果不是，返回值为 1，否则返回值为 0。
    
     *  查询出与每个部门中最低工资相同的员工信息
        
         *  按照部门分组，统计每个部门的最低工资
         *  根据最低工资查询出员工信息

SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno)
    AND deptno IS NOT NULL;

*  **ANY操作符**
    
    ANY关键字是一个MySQL运算符，如果子查询条件中ANY的比较结果为TRUE，则它会**返回布尔值TRUE** 。
    
     *  查询工资比任何管理工资都要高的员工信息
        
         *  查找出每个管理的薪资
         *  每个员工的薪资与每个管理的薪资比较

SELECT * FROM emp WHERE sal >ANY(
    SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno);

*  ALL操作符
    
    ALL关键字是一个MySQL运算符，如果子查询条件中ALL的比较结果为TRUE，则它会**返回布尔值TRUE** 。
    
     *  **案例同ANY操作符**

✨**效果如下：**

![在这里插入图片描述][86ee39ee2409491bbac49b2976d338a8.png_pic_center]

✨✨**表子查询**

> 子查询返回的是多行多列的数据，就是一个表格  
> 必须使用 IN、ANY 和 ALL 操作符对子查询返回的结果进行比较

✨综合练习：

*  在emp表中，得到与10号部门任何一个员工入职年份和领导相同的员工信息(用在where子句中)
    
     *  需要用到`DATE_FORMAT(hiredate,'%Y')`将入职日期转换为年份

SELECT * FROM emp
    WHERE (DATE_FORMAT(hiredate,'%Y'),mgr) IN (SELECT DATE_FORMAT(hiredate,'%Y') hiryear,mgr FROM emp WHERE deptno=10);

![在这里插入图片描述][10e3db5d31eb43d090343596f02dae4f.png_pic_center]

*  查询出每个部门的编号、名称、位置、部门人数、平均工资（用在from子句中）
    
     *  以前学的多表联合查询——-emp、dept
     *  用子查询联合查询

SELECT d.deptno,d.dname,d.loc,COUNT(e.deptno),ROUND(AVG(sal),2) FROM
    dept d LEFT JOIN emp e
    ON e.deptno=d.deptno
    GROUP BY d.deptno,d.dname,d.loc;
    
    SELECT dept.deptno,dept.dname,dept.loc,d.count,d.avgsal FROM dept LEFT JOIN 
    (SELECT deptno,COUNT(*) count,AVG(sal) avgsal  FROM emp GROUP BY deptno) d
    ON dept.deptno=d.deptno;

![在这里插入图片描述][64b3737118f149edb106ac16ccc55b7f.png_pic_center]

*  查询出所有在’SALES’部门工作的员工编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资。(where和from子句同时使用)

#1
    SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,minsal,maxsal,e.deptno  
    FROM emp e JOIN 
    (SELECT deptno,MIN(sal) minsal,MAX(sal) maxsal FROM emp GROUP BY deptno) td
    ON e.deptno=td.deptno AND e.deptno=(SELECT deptno FROM dept WHERE dname='SALES');
    
    #2
    SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,minsal,maxsal,e.deptno  
    FROM emp e JOIN 
    (SELECT deptno,MIN(sal) minsal,MAX(sal) maxsal FROM emp GROUP BY deptno
    HAVING deptno=(SELECT deptno FROM dept WHERE dname='SALES')) td
    ON e.deptno=td.deptno;

![在这里插入图片描述][f5aeac83e00f43dbb6fa07ed08840e3a.png_pic_center]

*  查询出比‘ALLEN’或‘CLACRK’薪资多的所有员工的编号、姓名、基本工资、部门名称、领导姓名、部门人数。

#隐式方式
    SELECT e.empno,e.ename,e.sal,d.dname,me.ename 领导,temp.count FROM emp e,dept d,emp me,
    (SELECT deptno,COUNT(deptno) count FROM emp e GROUP BY deptno) temp
    WHERE e.deptno=d.deptno AND e.mgr=me.empno AND temp.deptno=e.deptno
    AND e.sal >ANY(SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK'))
    AND e.ename NOT IN('ALLEN','CLARK');
    
    #显示方式
    SELECT e.empno,e.ename,e.sal,d.dname,me.ename 领导,temp.count 
    FROM emp e 
    JOIN dept d ON e.deptno=d.deptno
    LEFT JOIN emp me ON e.mgr=me.empno
    JOIN (SELECT deptno,COUNT(deptno) count FROM emp e GROUP BY deptno) temp ON temp.deptno=e.deptno
    AND e.sal >ANY(SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK'))
    AND e.ename NOT IN('ALLEN','CLARK');

![在这里插入图片描述][8e84d9c2d14c420f8f990cda2ca550c2.png_pic_center]

*  列出公司各个部门的经理(假设每个部门只有一个经理，job为‘MANAGER’)的姓名、薪资、部门名称、部门人数、部门平均工资。

#隐式方式
    SELECT e.ename,e.sal,d.dname,temp.count,temp.avgsal
    FROM emp e,dept d,(SELECT deptno, COUNT(deptno) count,AVG(sal) avgsal FROM emp GROUP BY deptno) temp
     WHERE job='MANAGER' AND e.deptno=d.deptno AND temp.deptno=e.deptno;
     
     #显示方式
    SELECT e.ename,e.sal,d.dname,temp.count,temp.avgsal
    FROM emp e 
    JOIN dept d ON e.deptno=d.deptno 
    JOIN (SELECT deptno, COUNT(deptno) count,AVG(sal) avgsal FROM emp GROUP BY deptno) temp ON temp.deptno=e.deptno
     AND job='MANAGER';

![在这里插入图片描述][a6ef374ad737422c812725fc37b8c3f1.png_pic_center]

*  查询出所有薪资高于公司平均薪资的员工编号、姓名、基本工资、职位、雇佣日期、所在部门名称、部门位置、上级领导姓名、工资等级、部门人数、平均工资、平均服务年限。

#隐式方式
    SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,me.ename 领导,s.grade,temp.count,temp.avgsal,temp.avgyear
    FROM emp e,dept d,emp me,salgrade s,(SELECT deptno,COUNT(deptno) count,AVG(sal) avgsal,AVG(TIMESTAMPDIFF(MONTH,hiredate,CURDATE())/12) avgyear FROM emp GROUP BY deptno) temp 
    WHERE e.deptno=d.deptno AND e.sal>(SELECT AVG(sal) FROM emp)
    AND e.mgr=me.empno
    AND e.sal BETWEEN s.losal AND s.hisal
    AND temp.deptno=e.deptno;
    
    #显示方式
    SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,me.ename 领导,s.grade,temp.count,temp.avgsal,temp.avgyear
    FROM emp e 
    JOIN dept d ON e.deptno=d.deptno AND e.sal>(SELECT AVG(sal) FROM emp)
    LEFT JOIN emp me ON e.mgr=me.empno
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
    JOIN (SELECT deptno,COUNT(deptno) count,AVG(sal) avgsal,AVG(TIMESTAMPDIFF(MONTH,hiredate,CURDATE())/12) avgyear FROM emp GROUP BY deptno) temp ON temp.deptno=e.deptno;

![在这里插入图片描述][829aa5ace06f4cb7bd3492e9c8b33300.png_pic_center]

#### 三、总结 ####

>  *  ✨✨ **子查询允许结构化的查询，这样就可以把一个查询语句的每个部分隔开。**
>  *  ✨✨**子查询提供了另一种方法来执行有些需要复杂的join和union来实现的操作。**
>  *  ✨✨**在许多人看来，子查询可读性较高。 而实际上，这也是子查询的由来。**

[K]: https://blog.csdn.net/qq_72157449?spm=1000.2115.3001.5343
[MySQL]: https://blog.csdn.net/qq_72157449/category_12344760.html?spm=1001.2014.3001.5482
[2265468439e74f53b6c398e5b4640cde.png_pic_center]: https://img-blog.csdnimg.cn/2265468439e74f53b6c398e5b4640cde.png#pic_center
[c0d67ca3c4c44f25bb3e7b1d2930de80.png_pic_center]: https://img-blog.csdnimg.cn/c0d67ca3c4c44f25bb3e7b1d2930de80.png#pic_center
[86ee39ee2409491bbac49b2976d338a8.png_pic_center]: https://img-blog.csdnimg.cn/86ee39ee2409491bbac49b2976d338a8.png#pic_center
[10e3db5d31eb43d090343596f02dae4f.png_pic_center]: https://img-blog.csdnimg.cn/10e3db5d31eb43d090343596f02dae4f.png#pic_center
[64b3737118f149edb106ac16ccc55b7f.png_pic_center]: https://img-blog.csdnimg.cn/64b3737118f149edb106ac16ccc55b7f.png#pic_center
[f5aeac83e00f43dbb6fa07ed08840e3a.png_pic_center]: https://img-blog.csdnimg.cn/f5aeac83e00f43dbb6fa07ed08840e3a.png#pic_center
[8e84d9c2d14c420f8f990cda2ca550c2.png_pic_center]: https://img-blog.csdnimg.cn/8e84d9c2d14c420f8f990cda2ca550c2.png#pic_center
[a6ef374ad737422c812725fc37b8c3f1.png_pic_center]: https://img-blog.csdnimg.cn/a6ef374ad737422c812725fc37b8c3f1.png#pic_center
[829aa5ace06f4cb7bd3492e9c8b33300.png_pic_center]: https://img-blog.csdnimg.cn/829aa5ace06f4cb7bd3492e9c8b33300.png#pic_center