MYSQL数据库复杂查询练习题(难度适中)

简介: MYSQL数据库复杂查询练习题(难度适中)

能坚持全部做完的都是高手

直入主题

建库建表插入数据

代码直接按顺序复制就可以

-- 建库
CREATE DATABASE `emp`;
-- 打开库
USE emp;
-- 建dept表
CREATE TABLE `dept`( `deptno` INT(2) NOT NULL, `dname` VARCHAR(14), `loc` VARCHAR(13), CONSTRAINT pk_dept PRIMARY KEY(deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 键emp表
CREATE TABLE `emp` ( `empno` int(4) NOT NULL PRIMARY KEY, `ename` VARCHAR(10), `job` VARCHAR(9), `mgr` int(4), `hiredate` DATE, `sal` float(7,2), `comm` float(7,2), `deptno` int(2), CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 建salgrade表
CREATE TABLE `salgrade` ( `grade` int, `losal` int, `hisal` int ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (5,3001,9999);

dept表:
在这里插入图片描述
emp表:
在这里插入图片描述
salgrade表:
在这里插入图片描述

测试题

1.列出与“SCOTT”从事相同工作的所有员工及部门名称,部门人数。

  1. 列出公司各个工资等级雇员的数量、平均工资。
  2. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
  3. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
  4. 列出所有员工的姓名、部门名称和工资。
  5. 列出所有部门的详细信息和部门人数。
  6. 列出各种工作的最低工资及从事此工作的雇员姓名。
  7. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
  8. 列出所有员工的年工资,所在部门名称,按年薪从低到高排序。
  9. 查出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过3000
  10. 求出部门名称中,带‘S’字符的部门员工的、工资合计、部门人数。
  11. 给任职日期超过30年或者在87年雇佣的雇员加薪,加薪原则:10部门增长10%,20部门增长20%, 30部门增长30%,依次类推。
  12. 列出至少有一个员工的所有部门的信息:
  13. 列出薪金比SMITH对的所有员工:
  14. 列出所有员工的姓名以及其直接上级的姓名:
  15. 列出受雇日期早于其直接上级的所有员工的编号、姓名,部门名称
  16. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
  17. 列出所有"CLERK(职员)"的姓名以及部门名称,部门的人数
  18. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
  19. 列出在部门"SALES"工作的员工的姓名,假定不知道销售部的部门编号
  20. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级
  21. 列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工 资。
  22. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
  23. 列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排列。
  24. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
  25. 列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门。
  26. 列出所有“CLERK”(办事员)的姓名及其部门名称,部门的人数,工资等级。
  27. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工 资。
  28. 列出在部门“SALES”(销售部)工作的员工的姓名、基本工资、雇佣日期、部门名称,假定不知道 销售部的部门编号。
  29. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级。
  30. 列出与“SCOTT”从事相同工作的所有员工及部门名称,部门人数。
  31. 查询dept表的结构
  32. 检索emp表,用is a 这个字符串来连接员工姓名和工种两个字段
  33. 检索emp表中有提成的员工姓名、月收入及提成。

答案不唯一,仅供参考

有点乱,直接粘过来的格式不一样大家将就一下,勉强还是能看清的

-- 2. 列出公司各个工资等级雇员的数量、平均工资。 show tables; select from salgrade; select s.grade,count(),avg(e.sal) from emp e left join salgrade s on e.sal between s.losal and s.hisal group by s.grade ;
-- 3. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。 select ename,sal,d.dname,d.deptno from emp e left join dept d on e.deptno = d.deptno where e.sal > (select max(sal) from emp where deptno = 30);
-- 4. 列出在每个部门工作的员工数量、平均工资和平均服务期限。 select count(*),avg(sal),avg(year(now())-year(hiredate)) from emp group by deptno;
-- 5. 列出所有员工的姓名、部门名称和工资。 SALES research accounting select e.ename,d.dname,e.sal from emp e left join dept d on d.deptno = e.deptno;
-- 6. 列出所有部门的详细信息和部门人数。 OPERATIONS select d.*,count(e.ename) from dept d left join emp e on e.deptno = d.deptno group by d.deptno;
-- 7. 列出各种工作的最低工资及从事此工作的雇员姓名。
select a.ename,t.* from emp a left join (select e.job,min(e.sal) from emp e group by e.job) t on a.job = t.job;
-- 8. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。 -- binary 实现区分大小写 -- select ename from emp where job = binary 'MANAGER'; -- select binary 'a' = 'a'; -- select binary 'a'; -- select binary 'A'; select from emp where job = binary 'MANAGER'; select a.mm,c.ename,c.job,b.dname,b.cc from (select d.deptno,min(sal) mm from emp e left join dept d on e.deptno = d.deptno where job = 'MANAGER' group by deptno) a left join (select d.deptno,d.dname,count() cc from emp e left join dept d on e.deptno = d.deptno group by d.deptno) b on a.deptno = b.deptno left join emp c on c.sal = a.mm and b.deptno = c.deptno ;
-- 9. 列出所有员工的年工资,所在部门名称,按年薪从低到高排序。 select empno,ename,sal12,d.dname from emp left join dept d on d.deptno = emp.deptno order by sal12 asc;
-- 10. 查出某个员工的上级主管及所在部门名称,并要求出这些主管中的薪水超过3000 select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno where b.sal>3000 and a.empno = 7566; select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno where b.sal>3000; select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno;
-- 11. 求出部门名称中,带‘S’字符的部门员工的、工资合计、部门人数。 select d.dname,count(),sum(e.sal) from emp e left join dept d on e.deptno = d.deptno where d.dname like '%s%' group by d.deptno; select from emp; -- 12. 给任职日期超过30年或者在87年雇佣的雇员加薪,加薪原则:10部门增长10%,20部门增长 20%,30部门增长30%,依次类推。 select empno,ename,sal,sal+sal(deptno/100) from emp where year(curdate()) - year(hiredate)>30 or year(hiredate)=2022; update emp set sal = sal+sal(deptno/100) where year(curdate()) - year(hiredate)>30 or year(hiredate)=2022; select * from emp;
-- 13. 列出至少有一个员工的所有部门的信息 select distinct d. from dept d join emp e on d.deptno = e.deptno; -- 14. 列出薪金比SMITH低的所有员工 select from emp where sal < (select sal from emp where ename = 'SMITH')
-- 15. 列出所有员工的姓名以及其直接上级的姓名: select a.empno,a.ename,b.ename from emp a left join emp b on a.mgr = b.empno;
-- 16. 列出受雇日期早于其直接上级的所有员工的编号、姓名,部门名称 select a.empno,a.ename,b.ename,d.dname from emp a left join emp b on a.mgr = b.empno and a.hiredate<b.hiredate left join dept d on d.deptno = a.deptno;
-- 17. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 select * from dept d left join emp e on d.deptno = e.deptno;
-- 18. 列出所有"CLERK(职员)"的姓名以及部门名称,部门的人数 select a.ename,a.job,b.dname,b.cc from emp a join (select d.deptno,d.dname,count(*) cc from dept d left join emp e on d.deptno = e.deptno group by d.deptno) b on b.deptno = a.deptno and a.job = 'CLERK';
-- 19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数 select job,max(sal),min(sal),avg(sal),count(*) from emp where sal>1500 group by job;
-- 20. 列出在部门"SALES"工作的员工的姓名,假定不知道销售部的部门编号 select ename from emp where deptno in (select deptno from dept where dname='sales'); select e.ename from emp e join dept d on e.deptno = d.deptno and d.dname='sales';
-- 21. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级 select a.ename,a.en,d.dname,a.sal,s.grade from (select a.deptno,a.ename,b.ename en,a.sal from emp a join emp b on a.mgr = b.empno and a.sal>(select avg(sal) from emp)) a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal; select a.ename,b.ename from emp a join emp b on a.mgr = b.empno and a.sal> (select avg(sal) from emp);
-- 22. 列出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高 工资。 select e.deptno,d.dname,avg(e.sal),max(e.sal),min(sal),count(*) from dept d join emp e on e.deptno = d.deptno group by e.deptno;
-- 23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。 select a.empno,a.ename,d.dname,b.ename from (select * from emp where sal >(select min(sal) from emp where ename in ('smith','allen'))) a left join emp b on a.mgr = b.empno left join dept d on d.deptno = a.deptno;
-- 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名,显示的结果按领导年工资的降序排 列。select a.empno,a.ename,a.sal12,b.empno,b.ename,b.sal12 from emp a left join emp b on a.mgr = b.empno order by b.sal*12;

-- 25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
select a.empno,a.ename,b.ename,d.dname from emp a left join emp b on a.mgr = b.empno and a.hiredate<b.hiredate left join dept d on d.deptno = a.deptno; 
-- 26. 列出部门名称和这些部门的员工信息(数量、平均工资),同时列出那些没有员工的部门。 select d.deptno,d.dname,count(e.ename),avg(sal) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;
 -- 27. 列出所有“CLERK”(办事员)的姓名及其部门名称,部门的人数,工资等级。 select a.ename,a.job,b.dname,b.cc from emp a join (select d.deptno,d.dname,count(*) cc from dept d left join emp e on d.deptno = e.deptno group by d.deptno) b on b.deptno = a.deptno and a.job = 'CLERK'; select e.deptno,count(e.deptno) from (select a.deptno,a.ename,d.dname,s.grade from (select deptno,ename,sal from emp where job='CLERK') a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal) aa left join emp e on aa.deptno = e.deptno group by e.deptno; select t1.*,t2.deptcount from (select d.deptno,e.ename,e.job,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal where e.job = 'CLERK') t1 join (select deptno, count(*) as deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno;
  -- 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、 平均工资。 select job,max(sal),min(sal),avg(sal),count(*) from emp where sal>1500 group by job;
   -- 29. 列出在部门“SALES”(销售部)工作的员工的姓名、基本工资、雇佣日期、部门名称,假定 不知道销售部的部门编号。 select e.ename,e.sal,e.hiredate,d.dname from emp e join dept d on d.deptno = e.deptno and d.dname='sales'; 
   -- 30. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级。 select a.ename,a.en,d.dname,a.sal,s.grade from (select a.deptno,a.ename,b.ename en,a.sal from emp a join emp b on a.mgr = b.empno and a.sal>(select avg(sal) from emp)) a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal; 
   -- 31. 列出与“SCOTT”从事相同工作的所有员工及部门名称,部门人数。 create view v1 as select b.ename,d.dname,a.cc from (select deptno,count(*) cc from emp group by deptno) a join (select ename,deptno from emp where job = (select job from emp where ename = 'scott')) b on b.deptno=a.deptno
   left join dept d on d.deptno = b.deptno; select * from v1;

-- 32. 查询dept表的结构 desc emp; describe emp; show create table emp; show columns from emp;
-- 33. 检索emp表,用is a 这个字符串来连接员工姓名和工种两个字段 is a 是oracle数据库 select concat(empno,ename,job) from emp; select concat_ws('-',empno,ename,job) from emp; select distinct job from emp; select group_concat(distinct job) from emp; select group_concat(distinct ename) from emp; select group_concat(distinct job order by job asc separator '=') from emp;
-- 34. 检索emp表中有提成的员工姓名、月收入及提成。 select ename,sal,comm from emp where comm is not null; select ename,sal,comm from emp where comm is not null and comm>0;

有疑问的评论区留言

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
SQL 缓存 监控
MySQL缓存机制:查询缓存与缓冲池优化
MySQL缓存机制是提升数据库性能的关键。本文深入解析了MySQL的缓存体系,包括已弃用的查询缓存和核心的InnoDB缓冲池,帮助理解缓存优化原理。通过合理配置,可显著提升数据库性能,甚至达到10倍以上的效果。
|
人工智能 安全 机器人
无代码革命:10分钟打造企业专属数据库查询AI机器人
随着数字化转型加速,企业对高效智能交互解决方案的需求日益增长。阿里云AppFlow推出的AI助手产品,借助创新网页集成技术,助力企业打造专业数据库查询助手。本文详细介绍通过三步流程将AI助手转化为数据库交互工具的核心优势与操作指南,包括全场景适配、智能渲染引擎及零代码配置等三大技术突破。同时提供Web集成与企业微信集成方案,帮助企业实现便捷部署与安全管理,提升内外部用户体验。
1346 12
无代码革命:10分钟打造企业专属数据库查询AI机器人
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询?
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据,传统的浅分页和深分页查询效率较低,尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法,有效提升了查询速度。最终建议根据业务需求选择合适方案:浅分页可加单列索引,深分页推荐联合索引或子查询优化,同时结合前端传递最后一条数据ID的方式实现高效翻页。
725 0
|
Cloud Native 关系型数据库 分布式数据库
|
并行计算 关系型数据库 MySQL
如何用 esProc 将数据库表转储提速查询
当数据库查询因数据量大或繁忙变慢时,可借助 esProc 将数据导出为文件进行计算,大幅提升性能。以 MySQL 的 3000 万行订单数据为例,两个典型查询分别耗时 17.69s 和 63.22s。使用 esProc 转储为二进制行存文件 (btx) 或列存文件 (ctx),结合游标过滤与并行计算,性能显著提升。例如,ctx 并行计算将原查询时间缩短至 0.566s,TopN 运算提速达 30 倍。esProc 的简洁语法和高效文件格式,特别适合历史数据的复杂分析场景。
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
储存过程(Stored Procedures) 和 函数(Functions) : 储存过程和函数允许用户编写 SQL 脚本执行复杂任务.
451 14
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
343 15
|
存储 关系型数据库 MySQL
使用命令行cmd查询MySQL表结构信息技巧分享。
掌握了这些命令和技巧,您就能快速并有效地从命令行中查询MySQL表的结构信息,进而支持数据库维护、架构审查和优化等工作。
1116 9

推荐镜像

更多