基本查询回顾 

前面我们讲解的 mysql 表的查询都是一张表进行查询，在实际开发中这远远不够。

如果大家对表的基本查询还是不太了解的话，可以自行去下面链接查看：

MySQL表的基本查询-CSDN博客

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

select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%'

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

select * from emp order by deptno, sal desc;

使用年薪进行降序排序

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

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

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

显示工资高于平均工资的员工信息

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

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

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

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

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

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

select job,count(*),format(avg(sal),2) from emp group by job;

多表查询

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

案例：

显示雇佣名、雇员工资以及所在部门的名字因为上面的数据来自 emp 和 dept 表，因此要联合查询

其实我们只要emp表中的 deptno=dept 表中的 deptno 字段的记录

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

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

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

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

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

自连接 

自连接是指在同一张表连接查询

案例：

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

使用的子查询 

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

使用多表查询

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

使用到表的别名

form emp leader，emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别。

子查询  

子查询是指从嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询

单行子查询 

返回一行记录的子查询

显示 smith 同一部门的员工 

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 distinct 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);

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

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

 多列子查询

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

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

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

在 from 子句中使用子查询

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

案例：

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

select ename,deptno,sal,format(asal,2) from emp, (select avg(sal) asal, deptno dt from emp group by deptno) tmp where emp.sal>tmp.asal and emp.deptno=tmp.dt;

获取各个部门的平均工资，将其看作临时表。 

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

select emp.ename,emp.sal,emp.deptno,ms form emp,
(select max(sal) ms, deptno from emp group by deptno) tmp
where emp.deptno = tmp.deptno and emp.sal=tmp.ms;

显示每个部门的信息（部门名，编号，地址）和人员数量

方法一：使用多表

select dept.ename,dept.deptno,dept.loc,count(*) '部门人数' from emp,
dept
where emp.deptno=dept.deptno
group by dept.deptno,dept.ename,dept.loc;

方法二：使用子查询 

1. 对emp表进行人员统计
select count(*),deptno from emp group by deptno;
2. 将上面的表看作临时表
select dept.deptno,dname,mycnt,loc from dept,
(select count(*) mycnt,deptno from emp group by deptno) tmp
where dept.deptno = tmp.deptno;

合并查询

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

union 

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

案例：将工资大于2500或职位是MANAGER的人找出来

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

union all 

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

案例：将工资大于25000或职位是MANAGER的人找出来 

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