【半夜学习MySQL】复合查询（含多表查询、自连接、单行/多行子查询、多列子查询、合并查询等详解）

🏠关于专栏：半夜学习MySQL专栏用于记录MySQL数据相关内容。
🎯每天努力一点点，技术变化看得见

---

回顾基本查询

下面使用几个案例，一起回顾之前文章所介绍的基本查询↓↓↓
案例1： 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名售资委大写的J

select * from emp where (sal>500 or job='MANAGER') and substring(ename,1,1)='J';

案例2： 按照部门号升序而雇员工资降序排列

select * from emp order by deptno asc, sal desc;

案例3： 对年薪进行降序排列

select ename, sal*12+ifnull(comm,0) as '年薪' from emp order by '年薪' desc;

案例4： 显示工资最高的员工的名字和工作岗位

select ename, job from emp where sal=(select max(sal) from emp);

案例5： 显示工资高于平均工资的员工信息

select ename, sal from emp where sal>(select avg(sal) from emp);

案例6： 显示每个部门的平均工资和最高工资

select deptno, avg(sal), max(sal) from emp group by deptno;

案例7： 显示平均工资低于2000的部门号和它的平均工资

select deptno, avg(sal) as avgsal from emp group by deptno having avgsal<2000;

案例8： 显示每种岗位的雇员总数，平均工资

select count(*) as '雇员总数', format(avg(sal), 2) as '平均工资' from emp group by job;

回顾完的这些查询操作，都是对一张表进行查询，但在实际开发中是远远不够的。下面我们就一起来了解学习以下复合查询。

多表查询

实际开发中的数据往往来自不同的表，所以需要多表查询。这里介绍多表查询使用的oracle9i自带的scott库下的emp、dept、salegrade表。先看一下这三张表吧↓↓↓

多表查询通过案例的方式进行介绍

案例1： 显示雇员名、雇员工资及其所在部门的名字。
☆ps：雇员名、雇员工资来自emp表，而部门名字在dept表中。我们可以尝试让emp表和dept表组合。

select * from emp, dept;

上述组合中：
Ⅰ 从第一张表中选出第一条巨鹿和第二个表的所有集合进行组合；
Ⅱ 然后从第一张表中取第二条数据，和第二张表中的所有记录组合
Ⅲ 不加过来条件，得到的上图结果称为笛卡尔积

但上图中那么多记录，我们只需要emp表中的dept等于dept表中的deptno字段的记录

select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno=dept.deptno;

案例2： 显示部门号为10的部门名、员工名和工资

select dname, ename, sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;

案例3： 显示各个员工的姓名、工资及工资级别

select ename, sal, grade from emp, salgrade where emp.sal between losal and hisal;

自连接

上面介绍的是多张表的连接操作，那能否实现一张表实现自己和自己连接呢？这就是自连接。下面图演示的就是dept表自身和自身的连接↓↓↓

案例： 显示员工FORD的上级领导的编号和姓名（emp表中mgr表示的是领导的编号）
●方法1：使用子查询的方式

select empno, ename from emp where empno=(select mgr from emp where ename='FORD');

●使用多表查询（自连接查询）

select leader.empno, leader.ename from emp leader, emp worker where leader.empno=worker.mgr and worker.ename='FORD';

子查询

子查询是嵌入到其他sql语句中的select查询语句，也叫做嵌套查询。下文对子查询的多种情况做出介绍↓↓↓

单行子查询

子查询语句返回一行记录的查询，称为单行子查询
示例： 显示SMITH同一部门的员工
☆思路：要知道与SMITH同部门的员工，就要先知道SMITH位于哪个部门↓↓↓

select deptno from emp where ename='SMITH';

由上可知SMITH位于20号部门，下面可以找出20号部门的所有员工↓↓↓

select * from emp where deptno=20;

将第一个查询结果嵌入第二个查询的where子句中，这就构成嵌套查询语句↓↓↓

select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查询

如果子查询返回的结果多条记录，该子查询称为多行子查询。

● in关键字：查询和10号部门的工作岗位相同的雇员的名字、岗位、工资、部门号，但是不包含10号部门员工

select ename, job, sal, deptno from emp where job in (select job from emp where deptno=10) and deptno<>10;

●all关键字：显示工资部门30的所有员工的工资都高的员工的姓名、工资和部门号

select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30);

★ps：上述all子句，等同于select max(sal) from emp where depth=30

●any关键字：显示工资比30号部门的任意员工高的员工的姓名、工资和部门号（不包含30号部门的员工）

select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30) and deptno<>30;

★ps：上述any子句，等同于select min(sal) from emp where depth=30

多列子查询

单行子查询是指子查询结果只返回单列、单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的。而多列子查询则是指查询返回多个列数据的子查询语句。

案例： 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMTH本人

select * from emp where (deptno, job) = (select deptno, job from emp where ename='SMITH') and ename<>'SMITH';

★ps：使用多列子查询，需要保证判断条件左右两侧列数相同，且列名顺序相同。

在from子句中使用子查询

子查询语句出现在from子句中，这里可以使用一个数据查询的技巧，即把子查询当作一个临时表使用。

案例1： 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
☆思路：要求高于部门平均工资的信息，首先就需要先查询各个部门的平均工资是多少。

select deptno, avg(sal) from emp group by deptno;

☆思路：让emp表的每条记录和上述子查询结果做笛卡儿积，使用where限定每条记录后面跟的平均工资是该员工所处部门的平均工资

select * from emp, (select deptno, avg(sal) from emp group by deptno) avgtable where emp.deptno = avgtable.deptno;

☆思路：最后使用where条件限定当前行中的sal要高于平均工资

select * from emp, (select deptno, avg(sal) as agvsal from emp group by deptno) avgtable where emp.deptno = avgtable.deptno and sal > agvsal;

案例2： 查找各个部门工资最高的人的姓名、工资、部门和最高工资
☆思路：首先需要找出各个部门的最高工资

select deptno, max(sal) from emp group by deptno;

☆ps：让emp表和上述子查询做笛卡儿积，并使用where条件限定只显示与emp表当前行记录的部门的平均工资。

select * from emp, (select deptno, max(sal) from emp group by deptno) maxsal where emp.deptno=maxsal.deptno;

☆思路：最后只要挑选出等于emp表中sal等于最高工资的行即可。

select * from emp, (select deptno, max(sal) ms from emp group by deptno) maxsal where emp.deptno=maxsal.deptno and sal=ms;

案例3： 显示各个部门的信息（部门名、编号、地址）和人员数量
●方法1：使用多表查询

select dept.dname, dept.deptno, dept.loc, count(*) as 'personNum' from emp, dept where emp.deptno=dept.deptno group by dept.deptno, dept.dname, dept.loc;

★ps：由于使用group by的查询语句，只能显示出现group by中的列字段、聚合函数。故这里将不需要进行排序的deptno.dname,、dept.loc一并放入了group by语句中

●方法2：使用子查询

select dept.dname, dept.deptno, dept.loc, cp.personNum from dept, (select deptno, count(*) personNum from emp group by deptno) as cp where dept.deptno=cp.deptno;

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符union和union all

●union
该操作符用于取得两个结果集的并集，它会自动去掉结果集中的重复记录。

案例： 将工资大于2500或职位为MANAGER的人显示出来

select * from emp where sal > 2500;
select * from emp where job='MANAGER';
select * from emp where sal > 2500 union select * from emp where job='MANAGER';

上述结果与select * from emp where sal>2500 or job='MANAGER';效果相同↓↓↓

●union all
该操作符用于取两个结果集的并集，但它并不会去除重复行↓↓↓

select * from emp where sal > 2500 union all select * from emp where job='MANAGER';

★ps：由于union all不会去除重复行，故上面结果中BLAKE、JONES出现了两次。

🎈欢迎进入半夜学习MySQL专栏，查看更多文章。
如果上述内容有任何问题，欢迎在下方留言区指正b(￣▽￣)d