【Mysql】第八章 复合查询-重点（多表查询+子查询+合并查询union）

---

一、多表查询

雇员信息表中包含三张表，分别是员工表（emp）、部门表（dept）和工资等级表（salgrade）。

员工表（emp）中包含如下字段：

• 雇员编号（empno）。
• 雇员姓名（ename）。
• 雇员职位（job）。
• 雇员领导编号（mgr）。
• 雇佣时间（hiredate）。
• 工资月薪（sal）。
• 奖金（comm）。
• 部门编号（deptno）。

部门表（dept）中包含如下字段：

• 部门编号（deptno）。
• 部门名称（dname）。
• 部门所在地点（loc）。

工资等级表（salgrade）中包含如下字段：

• 等级（grade）。
• 此等级最低工资（losal）。
• 此等级最高工资（hisal）。

select * from 表1,表2...

上面查询会将表1，表2关联起来，如果表1有3行数据，表2有4行数据，那么多表查询就有3*4=12行数据。（本质是穷举）

正常来讲如果表1有列deptno，表2有列deptno，那么两个列应该是要相等的，而下面这样不相等了

解决办法如下，表名.列名指明是哪个表的列名

select * from emp,dept where emp.deptno = dept.deptno;//这样筛选出两个表中deptno列相等的数据

显示部门号为10的部门名、员工名和员工工资

select dname, ename, sal from emp, dept where emp.deptno = dept.deptno and emp.deptno = 10;//部门deptno要一样才有效

显示各个员工的姓名，工资，及工资级别

select ename, sal, grade from emp, salgrade where sal > losal and sal <hisal;//工资要在losal到hisal区间才有效

二、自连接

在通过一张表查询

显示员工FORD的上级领导的编号和姓名

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

子查询

1.单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

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

2.多行子查询

返回多行记录的子查询，记得select选中的和要比较的是同一个数据

in:查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

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

• 首先select job from emp where deptno = 10找到工作岗位等于10的工作
• job in…相当于job =…

all:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

all和in都一样

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

• select sal from emp where deptno=30找到部门30中的所有工资
• sal > all…相当于sal要大于…中的工资

any：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

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

• select sal from emp where deptno=30找到部门30中的所有工资
• sal > any…相当于sal要大于…中的任意一个人的工资

3.多列子查询

返回多列数据的子查询

显示和SMITH的部门和岗位完全相同的员工，不包含SMITH本人

select ename, deptno, job from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH') and ename != 'SMITH';

记得select选中的和要比较的是同一个数据，返回多条数据，用括号包起来比较

4.在from子句中使用子查询

子查询语句出现from子句中，其查询结果将会被当作一个临时表使用

显示高于每个自己部门平均工资的员工的姓名、部门、工资和部门的平均工资

1.每个部门的平均工资

select deptno, avg(sal) 平均工资 from emp group by deptno;

2.将emp和每个部门的平均工资做笛卡尔积，每个部门平均工资重命名为tmp

select * from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) tmp;

3.在emp和tmp两个表中，筛选两个表部门相等，员工工资大于平均工资

select * from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal > tmp.平均工资;

select * from emp, (select deptno, avg(sal) from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal > tmp.avg(sal);//错误写法，不能写tmp.avg(sal)，.后面要写成汉字

显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资

1.将表emp按照deptno分组后选择deptno和最高工资

select deptno , max(sal) 最高工资 from emp group by deptno

2.将emp和每个部门最高工资做积，每个部门最高工资重命名为tmp

select ename, sal, emp.deptno from emp, (select deptno , max(sal) 最高工资 from emp group by deptno) tmp

3.在emp和tmp两个表中，筛选两个表部门相等，员工工资等于最高工资

select ename, sal, emp.deptno from emp, (select deptno , max(sal) 最高工资 from emp group by deptno) tmp where emp.deptno = tmp.deptno and sal = 最高工资;

select ename, sal, emp.deptno from emp, (select deptno , max(sal) 最高工资 from emp group by deptno) tmp where emp.deptno = tmp.deptno and sal = max(sal);//错误写法，不能写 sal = max(sal)，s

显示每个部门的部门名、部门编号、所在地址和人员数量

1.在表emp中将列deptno分类，统计人员数量

select deptno, count(*) 人员数量 from emp group by deptno

2.将表emp和人员数量做积，每个部门人员数量重命名为tmp

select * from dept, (select deptno, count(*) 人员数量 from emp group by deptno) tmp ;

3.在dept和tmp两个表中，筛选部门名、部门编号、所在地址和人员数量

select dname, dept.deptno, loc, 人员数量 from dept, (select deptno, count(*) 人员数量 from emp group by deptno) tmp where dept.deptno = tmp.deptno;

四、合并查询-union和union all

将多个查询结果进行合并

• union用于取得两个查询结果的并集，union会自动去掉结果集中的重复行。
• union all也用于取得两个查询结果的并集，但union all不会去掉结果集中的重复行。

显示工资大于2500或职位是MANAGER的员工

select sal, job from emp where sal > 2500 or job = 'MANAGER';

用union,会对合并后的结果进行去重

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

用union all,不会对合并后的结果进行去重

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

注意：待合并的两个查询结果的列的数量必须一致，否则无法合并

---