【⑧MySQL进阶】：子查询与HAVING/SELECT的完美结合

以你之姓@ 2023-10-12 18:28 117阅读 0赞

#### 前言 ####

> **✨欢迎来到[小K][K]的[MySQL专栏][MySQL]，本节将为大家带来MySQL子查询在`HAVING/SELECT`字句中使用、及相关子查询和`WITH/EXISTS`字句的讲解**✨

#### 目录 ####

*   *   *  前言
         *  一、在HAVING/SELECT字句中使用子查询
         *  二、相关子查询
         *  三、WITH/EXISTS、NOT EXISTS字句
         *  四、总结

#### 一、在HAVING/SELECT字句中使用子查询 ####

✨✨**HAVING字句**

*  查询部门编号、员工人数、平均工资，并且要求这些部门的平均工资高于公司平均薪资。

SELECT deptno,COUNT(deptno) cnt,AVG(sal) avgsal 
    FROM emp 
    GROUP BY deptno
    HAVING avgsal>
    (
    	SELECT AVG(sal) FROM emp
    );

*  查询出所有部门中平均工资最高的部门名称及平均工资

SELECT e.deptno,d.dname,ROUND(AVG(sal),2) avgsal
    FROM emp e,dept d
    WHERE e.deptno=d.deptno
    GROUP BY e.deptno
    HAVING avgsal>
    (
        #查询出所有部门平均工资中最高的薪资
    	 SELECT MAX(avgsal) FROM 
    		(SELECT AVG(sal) avgsal FROM emp GROUP BY deptno) AS temp
    )

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

✨✨**SELECT字句**

*  查询出公司每个部门的编号、名称、位置、部门人数、平均工资

#1多表查询
    SELECT d.deptno,d.dname,d.loc,COUNT(e.deptno),AVG(e.sal)
    FROM emp e,dept d
    WHERE e.deptno=d.deptno
    GROUP BY e.deptno;
    #2
    SELECT d.deptno,d.dname,d.loc,temp.cnt,temp.avgsal
    FROM dept d,(SELECT deptno,COUNT(deptno) cnt,AVG(sal) avgsal FROM emp GROUP BY deptno) temp
    WHERE d.deptno=temp.deptno;
    #3 关联子查询
    SELECT d.deptno,d.dname,d.loc,
    (SELECT COUNT(deptno) FROM emp WHERE deptno=d.deptno GROUP BY deptno) cnt,
    (SELECT AVG(sal) FROM emp WHERE deptno=d.deptno GROUP BY deptno) avgsal
    FROM dept d;

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

#### 二、相关子查询 ####

✨如果子查询的执行依赖外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就成为关联子查询。相关子查询按照一行接一行的顺序指针，主查询的每一行都指向一次子查询。

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

✨**查询需求**

*  查询员工中工资大于本部门平均工资的员工的部门编号、姓名、薪资

SELECT e.deptno,e.ename,e.sal
    FROM emp e
    WHERE e.sal>(SELECT AVG(sal) FROM emp WHERE deptno=e.deptno );

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

#### 三、WITH/EXISTS、NOT EXISTS字句 ####

✨✨**WITH字句**

*  查询每个部门的编号、名称、位置、部门平均工资、人数

-- 多表查询
    SELECT d.deptno,d.dname,d.loc,AVG(e.sal) avgsal ,COUNT(e.deptno) cnt
    FROM dept d,emp e
    WHERE d.deptno=e.deptno
    GROUP BY e.deptno;
    
    -- 子查询
    SELECT d.deptno,d.dname,d.loc,temp.avgsal,temp.cnt
    FROM dept d,(
    							SELECT deptno,AVG(sal) avgsal,COUNT(deptno) cnt
    							FROM emp
    							GROUP BY deptno
    						)temp
    WHERE d.deptno=temp.deptno;
    
    -- 使用with
    WITH temp AS(
    							SELECT deptno,AVG(sal) avgsal,COUNT(deptno) cnt
    							FROM emp
    							GROUP BY deptno
    						)
    SELECT d.deptno,d.dname,d.loc,temp.avgsal,temp.cnt
    FROM dept d,temp
    WHERE d.deptno=temp.deptno;

*  查询每个部门工资最高的员工编号、姓名、职位、雇佣日期、工资、部门编号、部门名称，显示的结果按照部门编号进行排序

-- 相关子查询
    SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,e.deptno,d.dname
    FROM emp e,dept d
    WHERE e.deptno=d.deptno
    AND e.sal=(SELECT MAX(sal) FROM emp WHERE deptno=e.deptno)
    ORDER BY e.deptno;
    -- 表子查询
    SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,e.deptno,d.dname
    FROM emp e,dept d,(SELECT deptno,MAX(sal) maxsal FROM emp GROUP BY deptno) temp
    WHERE e.deptno=d.deptno
    AND e.sal=temp.maxsal
    AND e.deptno = temp.deptno
    ORDER BY e.deptno;

![在这里插入图片描述][2b8c5048dcaa44f0b95d6715df5dbf0f.png_pic_center]  
![在这里插入图片描述][76fcb5fdfc874b5eaa074ffa665c564f.png_pic_center]

✨✨**EXISTS/NOT EXISTS字句**

在SQL中提供了一个exixts结构用于判断子查询是否有数据返回。如果子查询中有数据返回，exists结构返回true，否则返回false。

*  查询公司管理者的编号、姓名、工作、部门编号

-- 多表查询
    SELECT DISTINCT e.empno,e.ename,e.job,e.deptno
    FROM emp e JOIN emp mgr
    ON e.empno=mgr.mgr;
    -- 使用EXISTS
    SELECT e.empno,e.ename,e.job,e.deptno
    FROM emp e
    WHERE EXISTS (SELECT * FROM emp WHERE e.empno=mgr);

*  查询部门表中，不存在于员工表中的部门信息

-- 多表查询
    SELECT e.deptno,d.deptno,d.dname,d.loc
    FROM emp e RIGHT JOIN dept d
    ON e.deptno=d.deptno
    WHERE e.deptno IS NULL;
    
    -- 使用EXISTS
    SELECT d.deptno,d.dname,d.loc
    FROM  dept d
    WHERE NOT EXISTS (SELECT deptno FROM emp WHERE deptno=d.deptno);

![在这里插入图片描述][3b3df1406a8e405aaa64cbabd967f1a9.png_pic_center]  
![在这里插入图片描述][a478e29bbe1e44d497d12a28b36ef4ec.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
[b9fa8b41c73c476daec51d367f2bbee8.png_pic_center]: https://img-blog.csdnimg.cn/b9fa8b41c73c476daec51d367f2bbee8.png#pic_center
[c484f3166c554c4d8ae2ec14d47a44ec.png_pic_center]: https://img-blog.csdnimg.cn/c484f3166c554c4d8ae2ec14d47a44ec.png#pic_center
[a5ee1e6e66ef491d9ba061f8a274d596.png_pic_center]: https://img-blog.csdnimg.cn/a5ee1e6e66ef491d9ba061f8a274d596.png#pic_center
[84f22018d837471a8abac9feadd935c0.png_pic_center]: https://img-blog.csdnimg.cn/84f22018d837471a8abac9feadd935c0.png#pic_center
[2b8c5048dcaa44f0b95d6715df5dbf0f.png_pic_center]: https://img-blog.csdnimg.cn/2b8c5048dcaa44f0b95d6715df5dbf0f.png#pic_center
[76fcb5fdfc874b5eaa074ffa665c564f.png_pic_center]: https://img-blog.csdnimg.cn/76fcb5fdfc874b5eaa074ffa665c564f.png#pic_center
[3b3df1406a8e405aaa64cbabd967f1a9.png_pic_center]: https://img-blog.csdnimg.cn/3b3df1406a8e405aaa64cbabd967f1a9.png#pic_center
[a478e29bbe1e44d497d12a28b36ef4ec.png_pic_center]: https://img-blog.csdnimg.cn/a478e29bbe1e44d497d12a28b36ef4ec.png#pic_center