• 员工表tb_emp

• 部门表tb_dept

-- 1.查询员工及其主管姓名 -- 自连:自连:自连:自连: select t1.ename as 员工姓名,   t2.ename as 主管姓名 from tb_emp as t1    left join tb_emp as t2             on t1.mgr=t2.eno;               -- 2.查询月薪最高的员工姓名和月薪 -- 方法一(不推荐，因为有些人的数据可能是相同的，但只能返回一个） select ename,sal from tb_emp order by sal desc limit 1;  -- 方法二 select enmae,sal from tb_emp where sal=(select max(sal) from tb_emp);  -- 方法三：all/any select ename,sal from tb_emp where sal >= all(select sal       from tb_emp);  -- 方法四：计数法 select ename,sal from tb_emp t1 where (select count(*)    from tb_emp as t2         where t2.sal > t1.sal)=0;          -- 方法五:存在性判断 select ename,sal from tb_emp as t1 where not exists (select 'x'  #可以检查任何常量，效率更高      from tb_emp as t2                     where t2.sal > t1.sal);                      -- 3.查询月薪Top员工姓名及月薪 select ename,sal from tb_emp t1 where (select count(*)    from tb_emp as t2         where t2.sal > t1.sal)<3;         -- 4.查询员工姓名和年薪(年薪)=(sal comm)*13) select ename as 姓名,(sal coalesce(comm,0))*13 as 年薪 from tb_emp order by 年薪 desc;  -- 5.查询部门编号和人数 select dno as 部门编号,count(*) as 人数 from tb_emp group by dno;  -- 6.查询部门人数超过5人的部门编号和人数 select dno as 部门编号,count(*) as 人数 from tb_emp group by dno having 人数>5;   -- 7.查询各部门的名称和人数(各部门外部连接) -- 方法一 select dname as 部门名称,count(eno) as 人数 #不能用count（*），因为有些部门没有人，但有记录 from tb_dept     left join tb_emp             on tb_dept.dno=tb_emp.dno group by dname;  -- 方法二 select dname as 部门名称,coalesce(total,0) as 人数 from tb_dept as t1   left join (      select dno,count(*) as total      from tb_emp      group by dno) as t2   on t1.dno=t2.dno;  -- 8.查询月薪超过平均月薪的员工姓名和月薪 select ename as 姓名,sal as 月薪 from tb_emp where sal > (select avg(sal)     from tb_emp);      -- 9.查询月薪超过其部门平均月薪的员工姓名、部门编号和月薪 select ename as 姓名,dno as 部门编号,sal as 月薪 from tb_emp as t1     natural join (select dno,avg(sal) as avg_sal        from tb_emp as t2        group by dno) as temp where sal>avg_sal;  -- 10.查询月薪最高的人的姓名、月薪和部门名称 -- 方法一 select ename as 姓名,sal as 月薪,dname as 部门名称 from tb_dept as t1 natural join tb_emp as t2    natural join (select dno,max(sal) as max_sal        from tb_emp        group by dno) as t3 where sal=max_sal;  -- 方法二:使用元组，元组内的东西要一一对应 select ename,sal,dname from tb_dept as t1 natural join tb_emp as t2 where (dno,sal) in (select dno,max(sal)       from tb_emp      group by dno);  # in与not in效率太低，存在性判断可以使用 -- 11.查询主管的姓名和职位 -- 方法一 select ename as 主管姓名,job as 职位 from tb_emp where eno  in ( select distinct mgr      from tb_emp                 where mgr is not null);  -- 方法二 select ename as 主管姓名,job as 职位 from tb_emp as t1 where exists (select 'x'      from tb_emp as t2                     where t1.eno=t2.mgr);                          -- 12.查询普通员工(不是主管)的姓名和职位 -- 方法一 select ename as 普通员工姓名，job as 职位 from tb_emp where eno not in ( select distinct mgr          from tb_emp                      where mgr is not null);  -- 方法二 select ename as 主管姓名,job as 职位 from tb_emp as t1 where not exists (select 'x'      from tb_emp as t2                     where t1.eno=t2.mgr);                       -- 13.查询各部门月薪前两名的员工姓名、月薪和部门号 -- 方法一 select ename,sal,t1.dno from tb_emp as t1 where (select count(*)   from tb_emp as t2         where t2.dno=t1.dno and t2.sal > t1.sal )<2 order by dno asc,sal desc;  -- 方法二 select ename,   sal,         dno from (select ename,   sal,         dno,         rank() over (partition by dno order by sal desc) as rn #partition by也是分组，不过其分组后不会对数据的条数做出group by一样的改变，相当于只分类。         from tb_emp) as tmp where rn <= 2;  -- 14.查询月薪排名4~6名员工排名、姓名和月薪 -- mysql8.0以前 select 排名、姓名、月薪 from ( select     (@a:=@a 1) 排名,    ename as 姓名,    sal as 月薪   from     tb_emp,             (SELECT @a:=0) as t   order by sal desc) as temp limit 3,3;  -- mysql8.0 以后：窗口函数，性能低，但有用 select * from (select ename as 姓名,     sal as 月薪,                 dense_rank() over (order by sal desc) as 排名1 #dense_rank()排名，排名相同，后续排序依次顺序(1、2、3、4、5、6)                 -- rank() over (order by sal desc) as 排名2 #rank()排名相同后，跳过后一个排名(1、2、3、4、5、6）                 from tb_emp) as tmp where 排名1 between 4 and 6;