MYSQL学习笔记(七)：新年第一篇之子查询

前言：

祝大家新年快乐 🎆🎆🎆🎆🎆🎆
学习和使用数据库可以说是程序员必须具备能力，这里将更新关于MYSQL的使用讲解，大概应该会更新30篇+，涵盖入门、进阶、高级(一些原理分析);
这一篇是子查询简介，案例有点小小难度；
虽然MYSQL命令很多，但是自己去多敲一点，到后面忘记了，查一下就可以回忆起来使用了；
这一系列也是本人学习MYSQL做的笔记，也是为了方便后面忘记查询；
参考资料：尚硅谷、黑马、csdn和知乎博客；
欢迎收藏 + 关注，本人将会持续更新。
文章目录
- 子查询
- - 子查询概念
  - - 举个例子
    - 子查询规范
    - 子查询分类
  - 标量子查询
  - 单行子查询
  - 列子查询(多行子查询)
  - - IN操作符
    - ANY操作符
    - ALL操作符
  - 表子查询
  - 在HAVING子句中使用子查询
  - 在SELECT字句中使用子查询
- 其他语句
- - WITH字句
  - EXISTS/NOT EXISTS

子查询

子查询概念

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。

在特定情况下，一个查询语句的条件需要另一个查询语句来获取，称为内层查询，内层查询语句的查询结果，可以为外层查询语句提供查询条件。

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

举个例子

查询公司之中工资最低的员工信息
- 工资最低是多少？
- 工资最低的员工是谁？

sql>mysql">SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);

这个案例中，很明显要先找到最低工资数，然后才能通过条件查询进行后面操作，而先找到最低工资，就是标量子查询。

子查询规范

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

子查询分类

根据子查询返回的数据分类：

标量子查询(scalar subquery)：返回1行1列一个值
行子查询(row subquery)：返回的结果集是 1 行 N 列
列子查询(column subquery)：返回的结果集是 N 行 1列
表子查询(table subquery)：返回的结果集是 N 行 N 列

简单来说，子查询可以返回一个标量(就一个值)、一个行、一个列或一个表。

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

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

使用子查询给大家给出几点建议：

子查询出现在WHERE子句中：此时子查询返回的结果一般都是单列单行、单行多列、多行单列；
子查询出现在HAVING子句中：此时子查询返回的都是单行单列数据，同时为了使用统计函数操作；
子查询出现在FROM子句中：此时子查询返回的结果图一般都是多行多列，即相当于表。

标量子查询

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

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

sql>mysql">SELECT * FROM emp WHERE sal<(SELECT sal FROM emp WHERE eanme='ALLEN');

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

sql>mysql">SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

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

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

单行子查询

子查询返回的是单行多列的数据，就是一条记录；
注意：由于子查询是查询一个结果作为主查询的条件，所以在子查询中，要标明查询字段，不可用*。

查询与SCOTT从事统一工作且工资相同的员工信息
- 从事统一工作且工资相同，这里有两个条件，如果要用标量子查询，那么需要对emp表进行查询两次，但是，单行子查询只需要查询一次就可；

sql>mysql">SELECT * FROM emp e WHERE (e.job,e.sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT')
AND ename<>'SCOTT';sql>mysql> SELECT * FROM emp e WHERE (e.job,e.sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT')-> AND ename<>'SCOTT';
+-------+-------+---------+------+------------+------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+---------+------+------------+------+------+--------+
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000 | NULL |     20 |
+-------+-------+---------+------+------------+------+------+--------+
1 row in set (0.00 sec)

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

sql>mysql">SELECT * FROM emp e WHERE (e.job,e.mgr)=(SELECT job,mgr FROM emp WHERE empno=7566)
AND e.empno<>7566;sql>mysql> SELECT * FROM emp e WHERE (e.job,e.mgr)=(SELECT job,mgr FROM emp WHERE empno=7566) AND e.empno<>7566;
+-------+-------+---------+------+------------+------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+---------+------+------------+------+------+--------+
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL |     10 |
+-------+-------+---------+------+------------+------+------+--------+
2 rows in set (0.00 sec)

列子查询(多行子查询)

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

由于是一列数据，故不能直接像单行自查寻那样，在使用多行子查询需要使用多行比较操作符：

操作符	含义
IN	等于列表中的任意一个，相当于属于这个集合
ANY	只需要满足集合一个即可，需要和单行比较操作符一起使用(>、<、=、<>…)，与子查询结果中任何一个值比较，一个成立
ALL	需要满足集合所有条件，需要和单行比较操作符一起使用(>、<、=、<>…)，和子查询返回的所有值比较，同时成立
SOME	实际上是ANY的别名，作用相同，一般用ANY

IN操作符

IN 运算符用来判断表达式的值是否位于给出的列表中；如果是，返回值为 1，否则返回值为 0。

NOT IN（不是NO）的作用和 IN 恰好相反，NOT IN 用来判断表达式的值是否不存在于给出的列表中；如果不是，返回值为 1，否则返回值为 0。

案例：

查询出与每个部门中最低工资相同的员工信息
- 按照部门分组，统计每个部门的最低工资
- 根据最低工资查询出员工信息

sql>mysql">SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno)
AND deptno IS NOT NULL;sql>mysql> select * from emp e where e.sal in (select min(sal) from emp group by deptno) and e.deptno is not null;
+-------+--------+-------+------+------------+------+------+--------+
| empno | ename  | job   | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7900 | JAMES  | CLERK | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+-------+------+------------+------+------+--------+
3 rows in set (0.00 sec)

ANY操作符

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

查询工资比管理工资都要高的员工信息
- 查找出每个管理的薪资
- 每个员工的薪资与每个管理的薪资比较，只需要满足高于一个管理工资即可

sql>mysql">SELECT * FROM emp WHERE sal > ANY(
SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno);  # 这里是min，满足一个管理

ALL操作符

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

查询工资比任何管理工资都要高的员工信息

sql>mysql">SELECT * FROM emp WHERE sal > ALL(
SELECT MAX(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno);   # 这里是max，满足所有

表子查询

子查询返回的是多行多列的数据，就是一个表格

必须、必须使用 IN、ANY 和 ALL 操作符对子查询返回的结果进行比较

在emp表中，得到与10号部门任何一个员工入职日期和领导相同的员工信息(用在where子句中)

sql>mysql">SELECT * FROM emp
WHERE (DATE_FORMAT(hiredate,'%Y'),mgr) IN (SELECT DATE_FORMAT(hiredate,'%Y') hiryear,mgr FROM emp WHERE deptno=10);sql>mysql> select * from emp where (date_format(hiredate, '%Y'), mgr) in (select date_format(hiredate, '%Y') hirdate, mgr fr
om emp where deptno=10);
+-------+--------+---------+------+------------+------+------+--------+
| empno | ename  | job     | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+---------+------+------------+------+------+--------+
|  7566 | JONES  | MANAGER | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7698 | BLAKE  | MANAGER | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7934 | MILLER | CLERK   | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+---------+------+------------+------+------+--------+
4 rows in set (0.00 sec)

查询出每个部门的编号、名称、位置、部门人数、平均工资（用在from子句中）

sql>mysql">#以前学的多表联合查询
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;#用子查询联合查询
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;#with
WITH e AS (SELECT deptno, COUNT(ename) 人数, AVG(sal) 平均工资 FROM emp GROUP BY deptno)
SELECT d.deptno, d.dname, d.loc, e.`人数`, e.`平均工资` FROM 
dept d LEFT JOIN e ON
d.deptno = e.deptno;

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

sql>mysql">#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');

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

sql>mysql">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');

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

sql>mysql">#隐式方式
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) tempWHERE 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.deptnoAND job='MANAGER';

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

sql>mysql">#隐式方式
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;/*
注意：AVG(TIMESTAMPDIFF(MONTH,hiredate,CURDATE())/12) 计算工作平均年限，计算公式：(当前时间 - 入职时间) / 12
*/#显示方式
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;

本人还是喜欢隐式的方法，因为这样简单，也直观。

在HAVING子句中使用子查询

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

sql>mysql">SELECT deptno,COUNT(deptno) cnt,AVG(sal) avgsal 
FROM emp 
GROUP BY deptno
HAVING avgsal>
(SELECT AVG(sal) FROM emp
);# 注意：HAVING e.sal>(SELECT AVG(sal) FROM emp); 这样不是高于平均工资

查询出所有部门中平均工资最高的部门名称及平均工资
- 第一步：查询部门名称，平均工资
- 第二步：在分组中用having，查询平均工资醉倒

sql>mysql">SELECT e.deptno,d.dname,AVG(e.sal) 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
)

在SELECT字句中使用子查询

select中使用，就是

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

sql>mysql">#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;

其他语句

WITH字句

WITH语句就是相当于是将子查询结果作为一张表，且先查询出来。

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

sql>mysql">-- 多表查询
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) cntFROM empGROUP BY deptno)temp
WHERE d.deptno=temp.deptno;-- 使用with
WITH temp AS(SELECT deptno,AVG(sal) avgsal,COUNT(deptno) cntFROM empGROUP BY deptno)
SELECT d.deptno,d.dname,d.loc,temp.avgsal,temp.cnt
FROM dept d,temp
WHERE d.deptno=temp.deptno;

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

sql>mysql">-- 相关子查询
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;

EXISTS/NOT EXISTS

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

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

sql>mysql">-- 多表查询
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);

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

sql>mysql">-- 多表查询
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);