MySQL入门5（复合查询）

聚合函数
面试题：SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having >select > distinct > order by > limit

一、基本查询

1. 显示工资最高的员工的名字和工作岗位：
   （1）select ename, job from EMP where sal = (select max(sal) from EMP);
   （2）select ename , job from emp order by sal desc limit 1;
2. 显示每个部门的平均工资和最高工资：select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;

二、多表查询

就是将多张表通过一个共同的属性连接起来，比如select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;他们公共的部分是部门号，通过部门号将俩张表连接起来

三、自连接

是指在同一张表连接查询
比如：显示员工FORD的上级领导的编号和姓名（mgr是员工编号，empno是领导的编号）
（1）select leader.empno,leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename='FORD';使用表的别名来查询
（2）select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');

四、子查询

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

1. 单行子查询：
   返回一条记录的子查询
   比如：显示SMITH同一部门的员工：select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');
2. 多行子查询
   返回多条记录的子查询
   （1）in关键字
   查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10 ) and deptno<>10;
   （2）all关键字
   显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号select ename, sal, deptno from EMP where sal > all(select sal from EMP where deptno=30); select ename, sal, deptno from EMP where sal > (select max(sal) from emp where deptno=30);
   （3）any关键字
   显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno=30);
3. 多列子查询
   单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
   查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
   （1）select ename,job,deptno from emp where job =(select job from emp where ename ='smith') and deptno =(select deptno from emp where ename='smith') and ename<>'smith';（2）select job,deptno ,ename from emp where (job,deptno)=(select job,deptno from emp where ename='smith') and ename<>'smith';
4. 将子查询当做临时表
   例1：显示每个部门的信息（部门名，编号，地址）和人员数量
   （1）使用多表select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) '部门人数' from EMP, DEPT where EMP.deptno=DEPT.deptno group by DEPT.deptno;
   （2）使用子查询创造临时表select dept.dname,dept.loc,dept.deptno ,a.t from dept,(select count(*) t ,deptno from emp group by deptno) a where dept.deptno=a.deptno; ，先找出emp中各部门的人数然后和dept表中的deptno做等
   例2：查找每个部门工资最高的人的姓名、工资、部门、最高工资：select EMP.ename, EMP.sal, EMP.deptno, ms from EMP, (select max(sal) ms, deptno from EMP group by deptno) tmp where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;
   例3： 获取各个部门的平均工资，将其看作临时表，显示高于自己部门平均工资的员工的姓名、部门、工资、平均工资：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;
5. 合并查询
   为了合并多个select的执行结果
   （1）union：会自动去掉结果集中的重复行，用于取得两个结果集的并集。
   比如：select ename, sal, job from EMP where sal>2500 union select ename, sal, job from EMP where job='MANAGER';
   （2）union all：不会去掉结果集中的重复行，用于取得两个结果集的并集。
   比如：select ename, sal, job from EMP where sal>2500 union all select ename, sal, job from EMP where job='MANAGER';