基本概念
子查询定义
- 子查询指的是在嵌入在其他 sql 语句中的 select 语句,又称为内部查询、嵌套查询
- 子查询,又叫内部查询,相对于内部查询,包含内部查询的就称为外部查询
- 内部查询只能是 select 语句且可以包括任何子句
- 外部查询可以是 select,insert,update,delete,set 或 do
子查询的位置
- select 中,from 后,where 中
- group by 和order by 中也可以,但无实用意义
子查询分类
- 单行单列:返回的是一个具体列的内容,可以理解为一个单值数据
- 多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围
- 单行多列:返回一行数据中多个列的内容
- 多行多列:查询返回的结果是一张临时表
单行单列(where中)
显示与smith同一部门的所有员工信息,不包括smith
mysql> select * from emp where deptno=(select deptno from emp where ename='smith') and ename <> 'smith';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
多行单列(where中)
in 关键字
//查询和部门10的工作相同的雇员的信息,但是不含10自己的
mysql> select * from emp where job in (select distinct job from emp where deptno=10) and deptno <> 10;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+-------+---------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)
all 关键字
<>all
:等价于not in
(但是=all
并不等价于in
)>all
:比子查询中最大的值还要大(还包含了>=all
)<all
:比子查询中最小的值还要小(还包含了<=all
)
//显示工资比部门30的所有员工的工资高的员工信息
mysql> select * from emp where sal > all(select sal from emp where deptno=30);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.45 sec)
mysql> select * from emp where sal > (select max(sal) from emp where deptno=30);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)
any关键字
=any
:表示与子查询中的每个元素进行比较,功能与in类似(然而<>any
不等价于not in
)>any
:比子查询中返回结果的最小的要大(还包含了>=any
)<any
:比子查询中返回结果的最大的要小(还包含了<=any
)
//显示工资比部门30的任一员工的工资高的员工信息
mysql> select * from emp where sal > any(select sal from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)
mysql> select * from emp where sal > (select min(sal) from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)
单行多列(where中)
查询和 allen 部门 岗位完全相同的雇员信息
//标准写法
mysql> select * from emp where (deptno,job) = (select deptno,job from emp where ename='allen');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)
//自己写的 比较low
mysql> select * from emp where deptno = (select deptno from emp where ename='allen') and job = (select job from emp where ename='allen');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.01 sec)
在from中使用子查询
核心思想就是把子查询的结果当作一个临时表来使用
显示高于 自己 部门平均工资的员工的信息
//先获取各个部门的平均工资
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2443.750000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
//将上一步的结果当作临时表 进行多表查询
mysql> select * from emp,(select deptno,avg(sal) from emp group by deptno) temp where emp.deptno = temp.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | avg(sal) |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | 2443.750000 |
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | 1566.666667 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | 2443.750000 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | 1566.666667 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | 1566.666667 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | 2916.666667 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | 2443.750000 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | 2916.666667 |
| 7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 | NULL | 30 | 30 | 1566.666667 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | 1566.666667 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | 2443.750000 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | 2916.666667 |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+-------------+
13 rows in set (0.00 sec)
//筛选结果
//where中不能有聚合函数 需要给聚合函数一个别名再使用
mysql> select * from emp,(select deptno,avg(sal) from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal>temp.avg(sal);
ERROR 1630 (42000): FUNCTION temp.avg does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
mysql> select * from emp,(select deptno,avg(sal) myavg from emp group by deptno) temp where emp.deptno = temp.deptno and emp.sal>temp.myavg;
+-------+-------+-----------+------+------------+---------+--------+--------+--------+-------------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | myavg |
+-------+-------+-----------+------+------------+---------+--------+--------+--------+-------------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | 2443.750000 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | 1566.666667 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | 2443.750000 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | 2916.666667 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | 2443.750000 |
+-------+-------+-----------+------+------------+---------+--------+--------+--------+-------------+
6 rows in set (0.00 sec)
//方法二
mysql> select * from emp where deptno=10;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp e1 where e1.sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno);
+-------+-------+-----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+--------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+--------+--------+
6 rows in set (0.00 sec)
查找每个部门工资最高的人的资料
//方法一
mysql> select max(sal),deptno from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
| 3000.00 | 20 |
| 2850.00 | 30 |
+----------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp,(select max(sal) mymax,deptno from emp group by deptno) temp where emp.deptno=temp.deptno and emp.sal=temp.mymax;
+-------+-------+-----------+------+------------+---------+------+--------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | mymax | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+---------+--------+
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 2850.00 | 30 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 3000.00 | 20 |
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 5000.00 | 10 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 3000.00 | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+---------+--------+
4 rows in set (0.00 sec)
//方法二
mysql> select * from emp order by deptno,sal desc;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7844 | iurner | salesman | 7698 | 1981-09-08 | 1500.00 | NULL | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
mysql> select * from (select * from emp order by deptno,sal desc) temp group by deptno;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | king | president | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
//错误示例 先group by 然后 order by xxxxxx错的
mysql> select * from emp group by deptno order by deptno desc,sal desc;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)
//原因
1.order by 的列,必须是出现在group by 子句里的列
2.order by 要 放在 group by的 后面
查看部门信息和人员数量
mysql> select deptno,count(empno) from emp group by deptno;
+--------+--------------+
| deptno | count(empno) |
+--------+--------------+
| 10 | 3 |
| 20 | 4 |
| 30 | 6 |
+--------+--------------+
3 rows in set (0.00 sec)
//方法一: from子查询,先查各个部门人数,再作为临时表进行多表查询
mysql> select * from dept,(select deptno,count(empno) from emp group by deptno) temp where dept.deptno=temp.deptno;
+--------+------------+----------+--------+--------------+
| deptno | dname | loc | deptno | count(empno) |
+--------+------------+----------+--------+--------------+
| 10 | accounting | new york | 10 | 3 |
| 20 | research | dallas | 20 | 4 |
| 30 | sales | chicago | 30 | 6 |
+--------+------------+----------+--------+--------------+
3 rows in set (0.00 sec)
//方法二:多表查询,筛选 分组 取值
mysql> select dept.deptno,dname,loc,count(*) from dept,emp where dept.deptno=emp.deptno group by deptno;
+--------+------------+----------+--------------+
| deptno | dname | loc | count(*) |
+--------+------------+----------+--------------+
| 10 | accounting | new york | 3 |
| 20 | research | dallas | 4 |
| 30 | sales | chicago | 6 |
+--------+------------+----------+--------------+
3 rows in set (0.00 sec)