【MySQL】复合查询

1 🍑基本查询回顾🍑

• 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J；

• 按照部门号升序而雇员的工资降序排序；

• 使用年薪进行降序排序；

• 显示工资最高的员工的名字和工作岗位；

注意上面我们用的是子查询的方式。

• 显示每个部门的平均工资和最高工资；

• 显示平均工资低于2000的部门号和它的平均工资；

• 显示每种岗位的雇员总数，平均工资；

2 🍑多表查询🍑

实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表emp,dept,salgrade来演示如何进行多表查询。
案例：

• 显示雇员名、雇员工资以及所在部门的名字;

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

这里简单解释一下原理，当我们进行多表查询时，是拿着一张表中的数据向另外一张表中所有数据进行排列组合的：（笛卡尔积）
但是我们要排除明显错误的数据比如emp.deptno!=dept.deptno的数据要排除掉。

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

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

3 🍑自连接🍑

自连接是指在同一张表连接查询。
案例：

• 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号）；

1. 使用子查询

mysql> select empno,ename from emp where empno=(select mgr from emp where ename="FORD");

2. 使用自连接
在使用自连接时一定要重命名；

mysql> select leader.empno,leader.ename from emp leader,emp worker 
    -> where leader.empno=worker.mgr and worker.ename="FORD";

4 🍑子查询🍑

4.1 🍎单行子查询🍎

返回一行记录的子查询。

• 显示SMITH同一部门的员工；

mysql> select ename,deptno from emp where deptno=(select deptno from emp where ename="SMITH");

4.2 🍎多行子查询🍎

返回多行记录的子查询。

4.2.1 🍋in 关键字🍋

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

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

4.2.2 🍋all 关键字🍋

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

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

这里换言之使用all关键字就是比30号部门最高工资都要高的人。

4.2.3 🍋any 关键字🍋

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

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

这里换言之使用any关键字就是比30号部门最低工资都要高的人。

4.3 🍎多列子查询🍎

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

• 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人；

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

4.4 🍎在from子句中使用子查询🍎

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
案例：

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

mysql> select emp.ename, emp.deptno, emp.sal, tmp.myavg from emp,
    -> (select deptno, avg(sal) myavg from emp group by deptno) tmp
    -> where emp.sal>tmp.myavg and emp.deptno=tmp.deptno;

• 查找每个部门工资最高的人的姓名、工资、部门、最高工资；

mysql> select emp.ename,emp.sal,emp.deptno,maxsal from emp,
    -> (select deptno,max(sal) maxsal from emp group by deptno) tmp
    -> where tmp.deptno=emp.deptno and emp.sal=tmp.maxsal;

4.5 🍎合并查询🍎

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

4.5.1 🍋union🍋

该操作符用于取得两个结果集的并集,当使用该操作符时，会自动去掉结果集中的重复行。

案例：

• 将工资大于2500或职位是MANAGER的人找出来;

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

这里补充一点，就算在这里我们不用 union而是直接用or连接两个条件也会得到去重结果。

4.5.2 🍋union all🍋

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：

• 将工资大于2500或职位是MANAGER的人找出来;

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