【MySQL】复合查询
目录
一、单表查询
1、查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
2、按照部门号升序而雇员的工资降序排序
3、使用年薪进行降序排序
4、显示工资最高的员工的名字和工作岗位
5、显示工资高于平均工资的员工信息
6、显示每个部门的平均工资和最高工资
7、显示平均工资低于2000的部门号和它的平均工资
8、显示每种岗位的雇员总数，平均工资
二、多表查询
不同的表进行复合查询
1、显示雇员名、雇员工资以及所在部门的名字
笛卡尔积
2、显示部门号为10的部门名，员工名和工资
3、显示各个员工的姓名，工资，及工资级别
自连接(相同的表进行复合查询)
一、单表查询
现在有一个雇员表(emp)。包含的字段：empno(雇员编号)、ename(雇员姓名)、job(岗位)、mgr(雇员领导的编号)、sal(月薪)、comm(年终奖)、deptno(部门)。
1、查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
select * from emp (sal>500 or job<=>'MANAGER') and ename like 'J%';//或者利用内置字符串函数筛选出满足ename条件
select * from emp (sal>500 or job='MANAGER') and substring(ename,1,1)='J';
2、按照部门号升序而雇员的工资降序排序
select ename,sal,depton from emp order by deptno asc,sal desc;
3、使用年薪进行降序排序
当一个数+null时结果是null，也就是对于如下sql语句：sal*12+comm中的comm是null因此这个表达式的值就是null。
select sal*12+comm as 年薪 form ename where ename<=>'SMITH';
因此要用到ifnull内置函数，可以避免结果为null的。
select ename,sal,comm, sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;
4、显示工资最高的员工的名字和工作岗位
select ename,job form emp where sal = (select max(sal) from emp);//或者先order by sal desc 再筛选分页,limit 1 offset 0但是这种方法只能筛选出最高工资是多少
//不适用于筛选出最高工资的人，最高工资的职位(这是因为有可能两个人是最高工资)
5、显示工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp);6、显示每个部门的平均工资和最高工资
select deptno,max(sal) 最高,format(avg(sal),2) 平均 from emp group by deptno;//format是数学内置函数，作用是格式化数据即保留几位小数
7、显示平均工资低于2000的部门号和它的平均工资
select deptno,avg(sal) avg_sal,from emp group by deptno having avg_sal<2000;//having是对分组聚合后的数据进行过滤8、显示每种岗位的雇员总数，平均工资
select job,count(*) 人数,avg(sal) 平均工资 from emp group by job;
二、多表查询
不同的表进行复合查询
这里要用到 部门表(dept)。包含的字段是：deptno(部门编号)、dname(部门名)、loc(部门地点)
1、显示雇员名、雇员工资以及所在部门的名字
由于emp表中只有部门的编号没有部门的名字，而dept表中有部门的名字，因此要想满足该需求必须要对两个表进行复合查询，此时emp就是从表，dept就是主表，emp.deptno和dept.deptno字段具有外键约束关系，下面是笛卡尔积：
笛卡尔积
将两张表整合得到笛卡尔积(也就是emp表的每条行数据都要与dept表中的所有行数据组合)。
select * from emp,dept;得到如下两表复合后的部分数据：
因此该sql语句为：
select EMP.ename,EMP.sal,DEPT.dname from EMP,DEPT where EMP.deptno<=>DEPT.deptno;
2、显示部门号为10的部门名，员工名和工资
select DEPT.dname,ename,sal from EMP,DEPT where EMP.deptno<=>DEPT.deptno and EMP.deptno=10;3、显示各个员工的姓名，工资，及工资级别
这里要用到薪资等级表(salgrade)。包含的字段：grade(薪资等级)、losal和hisal(表示薪资范围)
select ename,sal,grade from EMP,SALGRADE where emp.sal between losal and hisal;自连接(相同的表进行复合查询)
子连接是指在同一张表上连接查询
//使用子查询：
select empno,ename from EMP where empno=(select mgr from EMP where ename<=>'FORD');//使用自查询(自己和自己做笛卡尔积)
select leader.empno,leader.ename from emp worker,emp leader where worker.mgr<=>leader.empno
and worker.ename<=>'FORD';