mysql查询进阶——员工表与部门表连接查询

如下是员工表与部门表的表内容。

查询月薪最高的员工姓名和月薪（子查询）

select ename, sal from tb_emp 
where sal = (
	select max(sal) from tb_emp
);
-- 如果月薪最高的员工有多个，则结果不正确。
-- select ename, sal from tb_emp order by sal desc limit 1;

查询员工的姓名和年薪((月薪+补贴)*13)
有的员工没有补贴，且值为null,因此需要对null做处理，如果值为空，则设置为0。
若直接将空值拿来运算，任何运算方式得到的都将是空值，相当于布尔值的假。

select ename, (sal + ifnull(comm, 0)) * 13 as annsal 
from tb_emp order by annsal desc;

查询所有部门的名称和人数
运维部门没有员工，查询所有部门需要外连接查询。
使用where或者內连接无法查到没有员工的部门。

select dname as 部门名称, ifnull(total, 0) as 总人数 
from tb_dept t1 left outer join 
(select dno, count(eno) as total from tb_emp group by dno) t2 
on t1.dno = t2.dno;

查询月薪最高的员工(Boss除外)的姓名和月薪
判断值是否为null，不能用等于，要用is。

select ename, sal from tb_emp 
where sal = (
	select max(sal) from tb_emp where mgr is not null
);

查询薪水超过平均薪水的员工的姓名和月薪

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

查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪

select ename, t1.dno, sal from tb_emp t1 inner join 
(select dno, avg(sal) as avgsal from tb_emp group by dno) t2
on t1.dno = t2.dno where sal > avgsal;

查询部门中薪水最高的人姓名、月薪和所在部门名称

-- 写法一
select ename, sal, dname from tb_dept t3 inner join
(select ename, sal, t1.dno from tb_emp t1 inner join 
(select dno, max(sal) as maxsal from tb_emp group by dno) t2 
on t1.dno = t2.dno where sal = maxsal) t4 
on t3.dno = t4.dno;
-- 写法二
select ename, sal, dname from tb_emp t1, 
(select dno, max(sal) as maxsal from tb_emp group by dno) t2, 
tb_dept t3 where t1.dno = t3.dno and t1.dno = t2.dno 
and sal = maxsal;

查询主管的姓名和职位
distinct 和 in 都是对mysql性能影响较大的语句，可以使用存在性判断（exists）取代去重（distinct）和集合运算（in)，见方法二

select ename, job from tb_emp where eno in (
	select distinct mgr from tb_emp where mgr is not null
);
-- 方法二
select ename, job from tb_emp t1 where exists 
(select 'x' from tb_emp t2 where t1.eno=t2.mgr);

查询月薪排名4~6名的员工排名、姓名和月薪
mysql没有专门的排名方法，需要手动创建变量使其自增，
若在order by sal desc后面直接执行limit 3,3; 那么排名将会变成1，2，3

select rn, ename, sal from 
(select @a:=@a+1 as rn, ename, sal 
from tb_emp t1, (select @a:=0) t2 
order by sal desc) t3 where rn between 4 and 6;