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

前言

✨���迎来到小K的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 列

             子查询可以返回一个标量(就一个值)、一个行、一个列或一个表，这些子查询分别称之为标量、行、列和表子查询。

           • 根据子查询和主查询之间是否有条件关联分类：

             • 相关子查询：两个查询之间有一定的条件关联(相互联系)
             • 不相关子查询：两个查询之间没有条件关联(相互独立)

             • 为了方便，对于在何处使用子查询给大家给出几点个人建议：

               • 子查询出现在WHERE子句中：此时子查询返回的结果一般都是单列单行、单行多列、多行单列
               • 子查询出现在HAVING子句中：此时子查询返回的都是单行单列数据，同时为了使用统计函数操作
               • 子查询出现在FROM子句中：此时子查询返回的结果图一般都是多行多列，可以按照一张数据表(临时表)的形式操作

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

        ✨✨标量子查询

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

        • 查询出基本工资比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';

            ✨效果如下：

            ✨✨单行子查询

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

            • 查询与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 emp7566;

                ✨效果如下：

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

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

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

                操作符	含义
                IN	等于列表中的任意一个
                ANY	需要和单行比较操作符一起使用(>、、ANY(SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno); ALL操作符 ALL关键字是一个MySQL运算符，如果子查询条件中ALL的比较结果为TRUE，则它会返回布尔值TRUE 。 案例同ANY操作符 ✨效果如下： ✨✨表子查询 子查询返回的是多行多列的数据，就是一个表格 必须使用 IN、ANY 和 ALL 操作符对子查询返回的结果进行比较 ✨综合练习： 在emp表中，得到与10号部门任何一个员工入职年份和领导相同的员工信息(用在where子句中) 需要用到DATE_FORMAT(hiredate,'%Y')将入职日期转换为年份 SELECT * FROM empWHERE (DATE_FORMAT(hiredate,'%Y'),mgr) IN (SELECT DATE_FORMAT(hiredate,'%Y') hiryear,mgr FROM emp WHERE deptno=10); 查询出每个部门的编号、名称、位置、部门人数、平均工资（用在from子句中） 以前学的多表联合查询——-emp、dept 用子查询联合查询 SELECT d.deptno,d.dname,d.loc,COUNT(e.deptno),ROUND(AVG(sal),2) FROMdept d LEFT JOIN emp eON e.deptno=d.deptnoGROUP 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) dON dept.deptno=d.deptno; 查询出所有在’SALES’部门工作的员工编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资。(where和from子句同时使用) #1SELECT 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) tdON e.deptno=td.deptno AND e.deptno=(SELECT deptno FROM dept WHERE dname='SALES');#2SELECT 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 deptnoHAVING deptno=(SELECT deptno FROM dept WHERE dname='SALES')) tdON e.deptno=td.deptno; 查询出比‘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) tempWHERE e.deptno=d.deptno AND e.mgr=me.empno AND temp.deptno=e.deptnoAND 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.deptnoLEFT JOIN emp me ON e.mgr=me.empnoJOIN (SELECT deptno,COUNT(deptno) count FROM emp e GROUP BY deptno) temp ON temp.deptno=e.deptnoAND e.sal >ANY(SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK'))AND e.ename NOT IN('ALLEN','CLARK'); 列出公司各个部门的经理(假设每个部门只有一个经理，job为‘MANAGER’)的姓名、薪资、部门名称、部门人数、部门平均工资。 #隐式方式SELECT e.ename,e.sal,d.dname,temp.count,temp.avgsalFROM 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.avgsalFROM 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'; 查询出所有薪资高于公司平均薪资的员工编号、姓名、基本工资、职位、雇佣日期、所在部门名称、部门位置、上级领导姓名、工资等级、部门人数、平均工资、平均服务年限。 #隐式方式SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,me.ename 领导,s.grade,temp.count,temp.avgsal,temp.avgyearFROM 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.empnoAND e.sal BETWEEN s.losal AND s.hisalAND 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.avgyearFROM 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.empnoJOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisalJOIN (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; 三、总结 ✨✨ 子查询允许结构化的查询，这样就可以把一个查询语句的每个部分隔开。 ✨✨子查询提供了另一种方法来执行有些需要复杂的join和union来实现的操作。 ✨✨在许多人看来，子查询可读性较高。 而实际上，这也是子查询的由来。