29个查询题（搬运总结）

特谢：
https://blog.csdn.net/qq_42764468/article/details/98072747

以下题目操作的数据库Scott的三个表，如下：
dept部门表
±------------±--------------±----------------+
| DEPTNO | DNAME | LOC |
| 部门编号 | 部门名称| 部门位置 |
±-------±-------------------±----------------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
±-------±------------------±----------------+

emp雇员信息表
±----------±-----------±-----------±-------±-----------------±--------±------------±------------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|员工编号|员工姓名 | 职位 | 经理 | 入职时间 | 工资 | 奖金 | 部门编号 |
±------±----------±-------------±-------±---------------±---------±----------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
±------±-----------±------------±-------±---------------±------------±---------±-------+
salgrade工资等级表
±------±------±------+
| GRADE | LOSAL | HISAL |
| 等级 |最低工资|最高工资|
±------±------±-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+

1.取得平均薪水最高的部门的部门编号
第一种方法：

select * from (select avg(sal) as avg_sal,deptno from emp group by deptno) as T2 where avg_sal in (select max(avg_sal) as avg_sal from (select avg(sal) as avg_sal,deptno from emp group by deptno) as T1);

第二种方法：

select deptno
from emp
group by deptno
having 
avg(sal) =
(select max(avg_sal) from
(select avg(sal) avg_sal from emp group by deptno)t
)

2.取得平均薪水最高的部门的部门名称

select d.dname,avg(e.sal) as avgsal
from emp e
join dept d
on e.deptno=d.deptno
group by d.dname
having avg(e.sal)=(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);

3.求平均薪水的等级最低的部门的部门名称

第一步：取得每一个部门的平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;

第二步：取得平均薪水的最大值
mysql> select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;

第三步：将第一步与第二步联合（单表查询时使用having或者where）
	将第二步计算的结果子查询当做一个条件
	mysql> select deptno,avg(sal) avgsal
    -> from emp
    -> group by deptno
    -> having
    -> avgsal = (select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);

4.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

第一步：先找出所有的领导，然后用not in排除领导即为普通员工
mysql> select distinct  a.empno,a.ename from emp a join emp b where a.empno=b.mgr;
mysql> select ename,sal from emp where ename not in( select distinct a.ename from emp a jo
in emp b where a.empno=b.mgr);

第二步：找出普通员工的最高薪水
mysql> select ename,max(sal)  from emp where ename not in( select distinct a.ename from em
p a join emp b where a.empno=b.mgr) ;

第三步：找出比普通员工最高薪水高的领导员工名称
mysql> select ename,sal from emp where sal>( select max(sal)  from emp where ename not in(
 select distinct a.ename from emp a join emp b where a.empno=b.mgr));

5.取得薪水最高的前五名员工

mysql> select ename,sal from emp order by sal desc limit 5;

6.取得薪水最高的第六到第十名员工

mysql> select ename,sal from emp order by sal desc limit 5,5;

7.取得最后入职的5名员工

select ename ,hiredate from emp order by hiredate desc limit 5;

8.取得每个薪水等级有多少员工

mysql> select s.grade,count(s.grade) from emp e join salgrade s on e.sal between losal and
 hisal group by s.grade;

9.列出所有员工及领导的姓名

mysql> select a.ename empname,b.ename leadername from emp a join emp b on a.mgr=b.empno;

10.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

第一步：列出所有受雇日期早于其直接上级的所有员工的编号，姓名
mysql> select a.empno,a.ename,a.hiredate,b.empno,b.ename,b.hiredate from emp a join emp b
on a.mgr=b.empno  where a.hiredate<b.hiredate;

第二步：列出所有受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
mysql> select a.empno,a.ename,d.dname from emp a join emp b on a.mgr=b.empno join dept d o
n a.deptno=d.deptno where a.hiredate<b.hiredate;

11.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

mysql> select e.*,d.dname from emp e right join dept d on e.deptno = d.deptno;

12.列出至少有5个员工的所有部门

既然有数字就应该想到count()函数，先分组，分组之后再过滤
mysql> select deptno 
from emp 
group by deptno
having count(*)>=5;
mysql> select d.* 
from emp e 
join dept d
 on d.deptno=e.deptno
  group by deptno having count(*)>=5;

13.列出薪金比"SMITH"多的所有员工信息.

mysql> select * from emp where sal>(select sal from emp where ename='SIMITH');

14.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

mysql> select e.ename,d.dname ,e.deptno from emp e join dept d on e.deptno =d.deptno  wher
e e.job="CLERK";
mysql> select deptno,count(*)  totalemp from emp group by deptno;
mysql> select t.ename,t.dname,t1.totalemp from (select e.ename,d.dname ,e.deptno from emp
e join dept d on e.deptno =d.deptno  where e.job="CLERK") t join (select deptno,count(*)
totalemp from emp group by deptno) t1 on t.deptno=t1.deptno;

15.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
注意：这里要求部门所有人中，最低薪金大于1500，然后查询雇员人数
第一种

mysql> select min(sal) minsal,job,count(*)  from emp group by job having minsal>1500;

第二种

select job,count(job)
from emp 
group by job
having min(sal)>1500；

错误理解示范：

select t.job,count(t.job) from (select * from emp where sal>1500) t group by t.job;

16.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号

mysql> select deptno from dept where dname="SALES";
mysql> select ename from emp where deptno =( select deptno from dept where dname="SALES");

17.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

mysql>select a.ename empname,b.ename leadername,d.dname,s.grade 
 from emp a
 left join emp b on a.mgr=b.empno 
 join dept d on a.deptno=d.deptno 
 join salgrade s on a.sal between s.losal and s.hisal 
 where a.sal>(select avg(sal) from emp);

18.列出与"SCOTT"从事相同工作的所有员工及部门名称

mysql> select job from emp where ename="SCOTT";
mysql> select ename from emp where job =(select job from emp where ename="SCOTT");
mysql> select e.ename,d.dname 
 from emp e join dept d on e.deptno=d.deptno 
 where  job =(select job from emp where ename="SCOTT");

mysql> select e.ename,d.dname 
from emp e join dept d on e.deptno=d.deptno 
where job =(select job from emp where ename="SCOTT") and e.ename!="SCOTT";

19.列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

mysql> select distinct sal from emp where deptno =30;
mysql> select ename,sal from emp 
where sal in( select distinct sal from emp where deptno=30) and deptno<>30;

20.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.

mysql> select max(sal) maxsal from emp where deptno=30;
mysql>select e.ename,e.sal,d.dname from emp e 
join dept d on e.deptno=d.deptno 
where e.sal>( select max(sal) maxsal from emp where deptno=30) and e.deptno<>30;

21.列出在每个部门工作的员工数量,平均工资和平均服务期限.

select deptno,count(*) empcount,avg(sal) avgsal from emp group by deptno;

第二步：在以上查询结果的基础上，按照d.deptno分组，按照e.ename计数
列出每个部门工作的员工数量，平均工资
select d.deptno,count(e.ename) countname,ifnull(avg(e.sal),0) avgsal
from emp e right join dept d
 on e.deptno=d.deptno
group by d.deptno;

第三步：计算每个员工的平均服务期限
mysql> select to_days(now());
mysql> select to_days(hiredate) from emp;
mysql> select (to_days(now())-to_days(hiredate))/365 from emp;

第四步：在第二步的基础上
列出每个部门工作的员工数量，平均工资和平均服务期限
mysql> select
    -> d.deptno,
    -> count(e.ename) countemp,
    -> ifnull(avg(e.sal),0) avgsal,
    -> ifnull( avg( (to_days(now())-to_days(hiredate))/365),0) avgtime
    -> from emp e
    -> right join dept d
    -> on e.deptno=d.deptno
    -> group by d.deptno;

22.列出所有员工的姓名、部门名称和工资

mysql> select e.ename,e.sal,d.dname 
from emp e join dept d on d.deptno=e.deptno;

23.列出所有部门的详细信息和人数

mysql> select d.*,count(e.ename) 
from  emp e right join dept d 
on e.deptno=d.deptno group by d.deptno;

24.列出各种工作的最低工资及从事此工作的雇员姓名

mysql> select job,min(sal) minsal from emp group by job;
mysql> select e.ename,t.job,t.minsal from emp e
    -> join (select job,min(sal) minsal from emp group by job) t
    -> on e.job=t.job and e.sal=minsal;

25.列出各个部门的MANAGER(领导)的最低薪金

select deptno,min(sal) minsal from emp  where job="MANAGER" group by deptno;

26.列出所有员工的年工资,按年薪从低到高排序

select ename,(sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;

27.求出员工领导的薪水超过3000的员工名称与领导名称

mysql> select a.ename empname,b.ename leadername
    -> from emp a join emp b on a.mgr= b.empno
    -> where b.sal>3000;

28.求出部门名称中,带’S’字符的部门员工的工资合计、部门人数
写法一：

mysql> select
    ->  d.dname,
    ->  ifnull(sum(e.sal),0) sumsal,
    ->  count(e.ename) countemp
    ->  from emp e
    ->  right join dept d
    ->  on e.deptno=d.deptno
    ->  where d.dname like '%s%'
    ->  group by d.deptno;

写法二：

mysql> select d.dname,sum(e.sal),count(e.empno)
    -> from emp e
    -> join dept d
    -> on e.deptno=d.deptno
    -> group by d.dname
    -> having d.dname like '%S%';

29.给任职日期超过30年的员工加薪10%

create table emp_bak as selecm emp;

update emp_bak set sal=sal*1.1 where (to_days(now())-to_days(hiredate))/365>30;

另一种方法;

select ename,(sal*1.1) salary_sal,timestampdiff(year,hiredate,now()) from emp where timestampdiff(year,hiredate,now()) >30;

本文地址：https://blog.csdn.net/weixin_41311528/article/details/107280611

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！