一.基本查询回顾(新增子查询)

//1.查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们姓名首字母为‘J’
select * from emp where (sal>500 or job='MANAGER') and left(ename, 1)='J';//2.按照部门号升序而雇员工资降序排序
select * from emp order by deptno asc, sal desc;//3.使用年薪进行降序排序
select ename, sal, 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 * from emp where sal > (select avg(sal) from emp); //子查询//6.显示每个部门的平均工资和最高工资
select deptno, format(avg(sal), 2) as 平均工资, format(max(sal), 2) as 最高工资 from emp group by deptno;//7.显示平均工资低于2000的部门号和它的平均工资
select deptno, avg(sal) as 平均工资 from emp group by deptno having 平均工资<2000;//8.显示每种岗位的雇员总数和平均工资
select job, count(*) as 雇员总数, format(avg(sal), 2) as 平均工资 from emp group by job;

二.多表查询

//1.显示雇员名，雇员工资，和雇员所在部门的名字
select ename, sal, dname from emp, dept where emp.deptno=dept.deptno; 
//在emp和dept作笛卡尔积得到的表基础上，进行where筛选,由于两个表中的部门号字段重名，所以要用表名区分//2.显示部门号为10号的部门名,员工名和工资
select dname, ename, sal from emp, dept where emp.deptno=dept.deptno emp.deptno=10;//3.显示各个员工的姓名，工资和工资级别
select ename, sal, grade from emp, salgrade where sal between losal and hisal;

三.自连接

一个表自己和自己做笛卡尔积，叫做表的自连接

//1.找到员工FORD的领导的编号和姓名
//方案一：子查询
select ename, empno from emp where empno=(select mgr from emp where ename='FORD');//方案二：自连接
select t2.ename, t2.empno from emp as t1, emp as t2 
where t1.ename='FORD' and t1.mgr=t2.empno;  //必须对两个表重命名，否则名字会冲突

四.子查询

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

1.单列单行子查询

返回一个条目的子查询

//显示和SMITH在同一部门的员工
select * from emp where deptno=(select deptno from emp where ename='SMITH');

2.单列多行子查询——三个关键字

返回多个条目的子查询

//查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不含10自己的
select ename, job, sal, deptno from emp 
where job in (select distinct job from emp where deptno=10) and deptno!=10; //in关键字//显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号
//解法一：
select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno=30);//解法二：
select ename, sal, deptno from emp 
where sal > all (select distinct sal from emp where deptno=30); //all关键字//显示工资比部门30的任意员工的工资高的员工姓名，工资和部门号(包含自己30部门自己的员工)
select ename, sal, deptno from emp 
where sal > any (select sal from emp where deptno=30); //any关键字

3.多列子查询

返回多列数据的子查询

//查询和SMITH的部门和岗位完全相同的所有雇员，不包括本人
select * from emp 
where (deptno, job) = (select deptno, job from emp where ename='SMITH') and ename != 'SMITH'

目前的子查询全部都在 where 子句中，充当筛选条件。
但是任何时刻，查询出来的临时结构，也是表结构，所以也可以对子查询的结果再次查询！！！

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

//1.显示每个高于自己部门平均工资的员工的姓名，部门，工资和平均工资
select ename, deptno, sal, 部门平均工资 from emp, 
(select deptno, avg(sal) as 部门平均工资 from emp group by deptno) as tmp 
where emp.deptno=tmp.deptno and sal > tmp.部门平均工资;//2.查找每个部门工资最高的人的姓名，工资，部门，最高工资
select ename, sal, t1.deptno, 部门最高工资 from emp as t1,
(select deptno, max(sal) as 部门最高工资 from emp group by deptno) as t2
where t1.deptno=t2.deptno and t1.sal=t2.部门最高工资;//3.显示每个部门的信息（部门名，编号，地址）和人员数量
select dname, t1.deptno, loc, dept_num from dept as t1,
(select deptno, count(*) as dept_num from emp group by deptno) as t2
where t1.deptno=t2.deptno;

小结：
解决多表查询的本质：想办法将多表转化为单表。所以 MySQL 中，所有 select 的问题全部可以转化为单表问题

五.合并查询

合并多个 select 的执行结果(前提是查出的表的结构相同)，可以使用集合操作符 union，union all
union:取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
union all:取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行

//将工资大于25000或职位是MANAGER的人找出来
select ename, sal, job from emp where sal > 2500 union
select ename, sal, jog from emp where job='MANAGER';

六.总结

本节重点掌握：

1. 笛卡尔积 + 条件筛选解决多表查询问题
2. 子查询的结果实质是一张表，可供 in，all，any 等关键字作条件判断，还可以被用来 select 查询