网站建设定制开发MySQL基础完结篇【第七篇】| 34道练习题

✅作者简介：网站建设定制开发大家好我是@网站建设定制开发每天都要敲代码，网站建设定制开发一位材料转码农的选手，网站建设定制开发希望一起努力，一起进步！
📃个人主页：
🔥系列专栏：
💬网站建设定制开发推荐一款模拟面试、刷题神器，网站建设定制开发从基础到大厂面试题 👉

目录

1. 网站建设定制开发取得每个部门最高薪水网站建设定制开发的人员名称

第一步：网站建设定制开发先按照部门分组，网站建设定制开发找到每个部门网站建设定制开发的最高薪水

  1. select deptno,max(sal) as maxsal from emp group by deptno;
  2. -- 网站建设定制开发养成好习惯，给max(sal)网站建设定制开发定义一个别名，便于操作；不定义下面直接t.max(sal)会报错 

第二步：将以上的查询结果当做一张临时表t，t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal

  1. select e.ename,t.*
  2. from (select deptno,max(sal) as maxsal from emp group by deptno)  t
  3. join emp e
  4. on e.deptno  = t.deptno and  e.sal = t.maxsal;

2. 哪些人的薪水在部门的平均薪水之上

第一步：按照部门分组，求出每个部门的平均薪水

  1. select deptno,avg(sal) as avgsal 
  2. from emp 
  3. group by deptno;

第二步：将以上查询结果当做t表，t和emp表连接；条件：部门编号相同，并且emp的sal大于t表的avgsal 

  1. select e.ename,e.sal,t.*
  2. from (select deptno,avg(sal) as avgsal from emp group by deptno) t
  3. join emp e
  4. on e.deptno = t.deptno and sal > t.avgsal;

3. 取得部门中（所有人的）平均的薪水等级

第一步：先获得每个人的薪水等级

  1. select e.ename,e.sal,e.deptno,s.grade
  2. from emp e
  3. join salgrade s
  4. on e.sal between s.losal and hisal;

第二步：根据以上结果进行分组，然后求平均值

  1. select e.deptno,avg(s.grade)
  2. from emp e
  3. join salgrade s
  4. on e.sal between s.losal and s.hisal
  5. group by e.deptno;

4. 用不同的方法，求最高薪水

第一种方法：max组函数

select max(sal) maxsal from emp;

第二种方法：先降序排，然后使用limit取第一个

 select sal from emp order by sal desc limit 1;

第三种方法：使用自连接

（1）先使用自连接求出一个范围数据，这堆数据里不包括除最大值，其它都包括

  1. select distinct a.sal 
  2. from emp a
  3. join emp b
  4. on a.sal < b.sal;
  5. -- 最大值5000不小于任何值，不会被列出来；其它数据都会被列出来

 

 （2）使用子查询

  1. select sal 
  2. from emp 
  3. where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal );

第四种方法：常识思维

  1. select sal from emp where mgr is null;
  2. -- 工资最高，肯定是老板，没有上级领导

5. 取得平均薪水最高的部门的部门编号

第一种方法：降序排，然后limit截取第一个

 第一步：先求出每个部门的平均薪水

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

第二步： 降序选第一个

  1. select deptno,avg(sal) avgsal 
  2. from emp 
  3. group by deptno 
  4. order by  avgsal desc 
  5. limit 1;

第二种方法： 使用max

  第一步：先求出每个部门的平均薪水

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

第二步：找出以上结果中avgsal最大的值

  1. select max(t.avgsal) maxavgsal
  2. from  (select deptno,avg(sal) avgsal from emp group by deptno) t

 第三步：联合使用，第一步和第二步结合，显示平均薪水中最大的值

  1. select deptno,avg(sal) avgsal 
  2. from emp 
  3. group by deptno 
  4. having avgsal = (select max(t.avgsal) maxavgsal from  (select deptno,avg(sal) avgsal from emp group by deptno) t);

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

 第一步：先求出每个部门的平均薪水，用部门名称dname分组

  1. select d.dname,avg(e.sal) avgsal 
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno
  5. group by d.dname ; --根据部门名称分类

第二步： 降序选第一个 

  1. select d.dname,avg(e.sal) avgsal 
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno
  5. group by d.dname -- 根据姓名进行排序
  6. order by  avgsal desc 
  7. limit 1;

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

第一步：找出每个部门的平均薪水

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

 第二步：找出部门的平均薪水等级 

  1. select t.*,s.grade 
  2. from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
  3. join salgrade s
  4. on t.avgsal between s.losal and s.hisal;

第三步：选出最低的薪水

select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;

第四步：根据最低薪水得出部门名称；有可能不止一个数据，所以不能直接先升序排然后limit 

  1. select t.*,s.grade 
  2. from (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
  3. join salgrade s
  4. on t.avgsal between s.losal and s.hisal
  5. where e.sal = (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1);

8. 取得比普通员工的最高薪水还要高的领导人姓名

第一步：找出普通员工的最高薪资

  1. -- 编号没有出现在mgr中的一定就是普通员工
  2. select max(sal) 
  3. from emp 
  4. where empno not in(select distinct mgr from emp where mgr is not null);
  5. -- not in 后面一定要手动排除null，不要最后结果是null

第二步：找出高于1600的 

  1. select ename,sal from emp where sal > (select max(sal) from emp 
  2. where empno not in(select distinct mgr from emp where mgr is not null));

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

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

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

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

11. 得最后入职的 5 名员工

  1. select ename,hiredate from emp order by hiredate desc limit 5;
  2. -- 日期也可以降序，升序

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

第一步：找出每个员工的薪水等级

  1. select e.ename,e.sal,s.grade 
  2. from emp e
  3. join salgrade s 
  4. on e.sal between s.losal and hisal;

 第二步：分组count

  1. select s.grade,count(*)
  2. from emp e
  3. join salgrade s 
  4. on e.sal between s.losal and hisal
  5. group by s.grade;

13. 面试题：

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
（1）找出没选过“黎明”老师的所有学生姓名。
（2）列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
（3）即学过 1 号课程又学过 2 号课所有学生的姓名。

（1）找出没选过“黎明”老师的所有学生姓名

第一步：先根据姓名找出“黎明”老师的课号cno

select cno from c where cteacher=“黎明”;

第二步：根据课号cno，找出选的学生的学号sno

select sno from sc where cno = (select cno from c where cteacher=“黎明”);

第三步：根据学号sno找出学生姓名

select sname from s where sno not in (select sno from sc where cno = (select cno from c where cteacher=“黎明”));

（2）列出 2 门以上（含2 门）不及格学生姓名及平均成绩

  1. select s.sname,avg(sc.scgrade) avggrade 
  2. from s 
  3. right join sc 
  4. on s.sno = sc.sno 
  5. where sc.scgrade < 60 
  6. group by s.sname 
  7. having count(s.sname) >= 2;

（3）即学过 1 号课程又学过 2 号课所有学生的姓名

第一步：选出学过1号课程的学生

 select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 1;

第一步：选出学过2号课程的学生

select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 2;

第三步：选出即学过 1 号课程又学过 2 号课所有学生的姓名

  1. select t1.sname from (select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno =   1) t1
  2.   join (select s.sname,sc.cno from s join sc on s.sno = sc.sno where sc.cno = 2) t2 on   t1.sname = t2.sname;

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

  1. -- 使用自连接
  2. select a.ename '员工', b.ename '领导'
  3. from emp a
  4. left join emp b
  5. on a.mgr = b.empno;

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

  1. select a.empno,a.ename '员工',a.deptno,a.hiredate,b.empno,b.ename '领导',b.deptno,b.hiredate,d.dname
  2. from emp a
  3. join emp b
  4. on a.mgr = b.empno --直接上级
  5. join dept d
  6. on a.deptno = d.deptno --根据条件输出对应的d.name
  7. where a.hiredate < b.hiredate;

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

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

 

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

select deptno from emp group by deptno having count(*)>=5;

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

第一步：先查出smith的薪资

 select e.sal from emp e where e.ename = 'smith';

第二步：找出薪资大于800的

select ename,sal  from emp where sal > (select e.sal from emp e where e.ename = 'smith');

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

第一步：找出clerk(办事员)的姓名和部门名称

  1. select e.ename,d.dname,d.deptno
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno
  5. where  job = 'CLERK';

 

第二步：分组，统计每个部门的人数

  1. select deptno,count(*) as deptcount
  2. from emp e
  3. group by deptno;

 第三步：两张表进行链接

  1. select t1.*,t2.deptcount
  2. from (select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno
  3. where  job = 'CLERK') t1
  4. join (select deptno,count(*) as deptcount from emp e group by deptno) t2
  5. on t1.deptno = t2.deptno;

20. 列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

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

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

第一步：利用部门sales得到部门编号

select deptno from dept where dname = 'sales';

第二步：在通过部门编号得到员工姓名

select ename from emp where deptno = (select deptno from dept where dname = 'sales');

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

第一步：找出公司平均薪金

select avg(sal) from emp;

第二步：找出所有工资>2073.214286的员工 

  1. select e1.ename '员工',d.dname,e2.ename '领导',s.grade
  2. from emp e1
  3. join dept d
  4. on e1.deptno = d.deptno
  5. left join emp e2
  6. on e1.mgr = e2.deptno
  7. join salgrade s
  8. on e1.sal between s.losal and hisal
  9. where e1.sal > (select avg(sal) from emp);

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

第一步：找出scott从事的工作

select job from emp where ename = 'SCOTT';

第二步： 找出相同工作的所有员工及部门名称

  1. select e.ename,d.dname
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno
  5. where job = (select job from emp where ename = 'SCOTT')
  6. and ename <> 'SCOTT'; --排除本身自己

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

 第一步：找出部门30的薪资集合

select distinct sal from emp where deptno = 30;

第二步：找出薪资属于上述集合，但部门不是30的

  1. select ename,sal 
  2. from emp 
  3. where sal in (select distinct sal from emp where deptno = 30) 
  4. and deptno <> 30; 

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

第一步：找出30部门的最高薪资

select max(sal) from emp where deptno = 30;

 第二步：找出薪资>2850.00的员工

  1. select e.ename,e.sal,d.dname 
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno
  5. where e.sal > (select max(sal) from emp where deptno = 30);

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

注：没有员工的部门，使用ifnull函数，部门人数是0

第一步：找出员工数量和平均工资

  1. select d.dname,count(e.ename),ifnull(avg(e.sal),0)
  2. from emp e
  3. right join dept d
  4. on e.deptno = d.deptno
  5. group by d.dname;

第二步：加上计算平均服务期限

 在mysql当中怎么计算两个日期的“年差”，差了多少年？
    TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

    间隔类型：
        SECOND   秒，
        MINUTE   分钟，
        HOUR   小时，
        DAY   天，
        WEEK   星期
        MONTH   月，
        QUARTER   季度，
        YEAR   年

  1. select d.dname,count(e.ename),ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
  2. from emp e
  3. right join dept d
  4. on e.deptno = d.deptno
  5. group by d.dname;

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

  1. select e.ename,d.dname,e.sal
  2. from emp e
  3. join dept d
  4. on e.deptno = d.deptno; 

 

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

  1. select d.* ,count(e.ename)
  2. from emp e
  3. right join dept d
  4. on e.deptno = d.deptno
  5. group by d.deptno,d.dname,d.loc;

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

第一步：找出各种工作的最低工资

  1. select job,min(sal) as minsal 
  2. from emp
  3. group  by job;

第二步：把上述结果当成t表，进行表连接

  1. select e.ename,t.*
  2. from emp e
  3. join (select job,min(sal) as minsal from emp group  by job) t
  4. on e.job = t.job and e.sal = t.minsal;

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

  1. select deptno,min(sal)
  2. from emp
  3. where job = 'MANAGER'
  4. group by deptno;

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

  1. select ename,(sal+ifnull(comm,0)) * 12 as yearsal 
  2. from emp 
  3. order by yearsal asc;

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

  1. select a.ename as '员工', b.ename as '领导'
  2. from emp a
  3. join emp b
  4. on a.mgr = b.empno
  5. where b.sal > 3000;

33. 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数

  1. select d.deptno,d.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename)
  2. from emp e
  3. right join dept d
  4. on e.deptno = d.deptno
  5. where d.dname like '%S%'
  6. group by d.deptno,d.dname;

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

  1. update emp set sal = sal*1.1 where (timestampdiff(YEAR,hiredate,now()) >30 );
  2. select * from emp;

结束语

今天的分享就到这里啦！快快通过下方链接注册加入刷题大军吧！各种大厂面试真题在等你哦！
💬刷题神器，从基础到大厂面试题👉

网站建设定制开发 软件系统开发定制 定制软件开发 软件开发定制 定制app开发 app开发定制 app开发定制公司 电商商城定制开发 定制小程序开发 定制开发小程序 客户管理系统开发定制 定制网站 定制开发 crm开发定制 开发公司 小程序开发定制 定制软件 收款定制开发 企业网站定制开发 定制化开发 android系统定制开发 定制小程序开发费用 定制设计 专注app软件定制开发 软件开发定制定制 知名网站建设定制 软件定制开发供应商 应用系统定制开发 软件系统定制开发 企业管理系统定制开发 系统定制开发