SQL（三）数据库查询例题

1.习题

1.1数据表

• 员工表tb_emp

• 部门表tb_dept

1.2题目练习

-- 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.查询月薪Top3的员工姓名和月薪
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.查询每个部门月薪排前2名的员工姓名、月薪和部门编号
-- 方法一
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，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;