MySQL的33道sql语句练习题

╰+哭是因爲堅強的太久メ 2022-11-25 04:13 140阅读 0赞

**MySQL的33道练习题：**

三张表作为测试数据。  
部门表dept：deptno（部门编号）, dname（部门名称）, loc（部门位置）  
员工表emp：empno（员工编号）, ename（姓名）, job（岗位）, mgr（领导编号）, hiredate（雇用日期）, sal（月薪）, comm（奖金）, deptno（部门编号）  
工资等级表salgrade：grade（等级）, lowsal（最低工资）, highsal（最高工资）

下面给出测试的数据库脚本内容：  
新建一个文本文件，后缀改成“.sql”，复制以下内容即可。

/* SQLyog Community v13.1.6 (64 bit) MySQL - 5.5.56 : Database - study_test ********************************************************************* */
    
    /*!40101 SET NAMES utf8 */;
    
    /*!40101 SET SQL_MODE=''*/;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    CREATE DATABASE /*!32312 IF NOT EXISTS*/`study_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `study_test`;
    
    /*Table structure for table `dept` */
    
    DROP TABLE IF EXISTS `dept`;
    
    CREATE TABLE `dept` (
      `deptno` int(2) NOT NULL,
      `dname` varchar(14) DEFAULT 'null',
      `loc` varchar(50) DEFAULT 'null',
      PRIMARY KEY (`deptno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `dept` */
    
    insert  into `dept`(`deptno`,`dname`,`loc`) values 
    (10,'Accounting','New York'),
    (20,'Research','Dallas'),
    (30,'Sales','Chicago'),
    (40,'Operations','Boston');
    
    /*Table structure for table `emp` */
    
    DROP TABLE IF EXISTS `emp`;
    
    CREATE TABLE `emp` (
      `empno` int(4) NOT NULL,
      `ename` varchar(10) DEFAULT 'null',
      `job` varchar(10) DEFAULT 'null',
      `mgr` int(4) DEFAULT NULL,
      `hiredate` date DEFAULT NULL,
      `sal` double(7,2) DEFAULT NULL,
      `commm` double(7,2) DEFAULT NULL,
      `deptno` int(2) DEFAULT NULL,
      PRIMARY KEY (`empno`),
      KEY `deptno` (`deptno`),
      CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `emp` */
    
    insert  into `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`commm`,`deptno`) values 
    (7369,'Smith','Clerk',7902,'1980-12-17',800.00,NULL,20),
    (7499,'Allen','Salesman',7968,'1981-02-20',1600.00,300.00,30),
    (7521,'Ward','Salesman',7968,'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-04-19',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-05-23',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);
    
    /*Table structure for table `salgrade` */
    
    DROP TABLE IF EXISTS `salgrade`;
    
    CREATE TABLE `salgrade` (
      `grade` int(11) NOT NULL AUTO_INCREMENT,
      `lowsal` int(11) DEFAULT NULL,
      `highsal` int(11) DEFAULT NULL,
      PRIMARY KEY (`grade`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    /*Data for the table `salgrade` */
    
    insert  into `salgrade`(`grade`,`lowsal`,`highsal`) values 
    (1,700,1200),
    (2,1201,1400),
    (3,1401,2000),
    (4,2001,3000),
    (5,3001,9999);
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

--------------------

1、取得每个部门最高薪水的人员名称。

思路：先找出每个部门的最高薪水，再和emp表连接。  
select e.ename, e.deptno, e.sal  
from emp e  
join (select deptno, max(sal) maxsal from emp group by deptno) t  
on e.deptno = t.deptno and e.sal = t.maxsal;

--------------------

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

思路：先找出每个部门的平均薪水，再和emp表连接。  
select e.ename, e.sal, e.deptno, t.avgsal  
from emp e  
join (select deptno, avg(sal) avgsal from emp group by deptno) t  
on e.deptno = t.deptno and e.sal > t.avgsal;

--------------------

3、取得每个部门平均的薪水等级。

思路：先找出每个部门的平均薪水，再和salgrade表连接。  
select e.deptno, avg(s.grade)  
from emp e  
join salgrade s  
on e.sal between s.lowsal and s.highsal  
group by e.deptno;

--------------------

4、不使用分组函数max的情况下，取得员工的最高薪水。

思路1：直接使用分组函数max是最快的，但题目不允许。  
select max(sal) from emp;

思路2：从emp表中获取每个人的薪水sal，再按照sal降序，取第一个即可。  
select ename, sal from emp  
order by sal desc limit 1;

思路3：使用自连接。  
select sal from emp where sal not in(  
select distinct e1.sal  
from emp e1  
join emp e2  
on e1.sal < e2.sal);

--------------------

5、取得平均薪水最高的部门的部门编号（至少给出2种写法）

思路1：先获取每个部门的平均薪水，作为临时表，再找出平均薪水最高的部门。  
select t.deptno, max(t.avgsal)  
from (  
select deptno, avg(sal) avgsal  
from emp  
group by deptno) t;

思路2：先找出每个部门的平均薪水，按照平均薪水降序，取第一个即可。  
select deptno, avg(sal) avgsal  
from emp  
group by deptno  
order by avgsal desc  
limit 1;

--------------------

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

思路1：先获取平均薪水最高的部门编号，再和部门表连接。  
select d.dname  
from (select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1) t  
join dept d  
on t.deptno = d.deptno;

思路2：先获取平均薪水最高的部门编号，作为条件去查询dept表。  
select dname  
from dept  
where deptno = (select deptno from emp group by deptno order by avg(sal) desc limit 1);

--------------------

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

提示：这道题用limit的做法都是错误的。

思路：先获取每个部门的平均薪水，再和salgrade连接找出对应薪水等级，再和dept连接找出部门名称，最后按照最低等级升序。  
select d.dname  
from (select deptno, avg(sal) avgsal from emp group by deptno) t  
join salgrade s  
on t.avgsal between s.lowsal and s.highsal  
join dept d  
on t.deptno = d.deptno  
order by min(s.grade) asc;

--------------------

8、取得比普通员工中最高薪水还要高的领导人的姓名。  
（其中普通员工是：员工编号没有在mgr上出现过的员工）

首先理解一下这句话：比普通员工的最高薪水的人一定是领导人。

思路：先找出普通员工，再找出他们之间的最高薪水maxsal，再查询emp表，工资大于maxsal就是属于领导人。  
select ename from emp  
where sal > (select max(sal) from emp where empno in (select empno from emp where empno not in (select distinct mgr from emp where mgr is not null)));

--------------------

9、取得薪水最高的前5名员工。

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

--------------------

10、取得薪水最高的第6到第10的员工。

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

--------------------

11、取得最后入职的5名员工。

（这道题告诉我们，日期类型date也能升序降序排列）

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

--------------------

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

思路：先要知道有哪些薪水等级，再连接emp表，按照等级分组。  
select s.grade, count(s.grade)  
from emp e  
join salgrade s  
on e.sal between s.lowsal and s.highsal  
group by s.grade  
order by s.grade

--------------------

13、列出所有员工及其领导的姓名。

思路：两张emp表自连，使用外连接方式。  
select e1.ename ‘员工’, e2.ename ‘领导’  
from emp e1  
left join emp e2  
on e1.mgr = e2.empno;

--------------------

14、列出受雇日期早于其上级领导的所有员工的编号，姓名，部门名称。

思路：使用emp自连接找出受雇日期比其领导早的员工，再连接dept表。  
select e1.empno, e1.ename, d.dname  
from emp e1  
join emp e2  
on e1.mgr = e2.empno and e1.hiredate < e2.hiredate  
join dept d  
on e1.deptno = d.deptno;

--------------------

15、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。

思路：使用外连接  
select d.dname, e.\*  
from emp e  
right join dept d  
on e.deptno = d.deptno;

--------------------

16、列出至少有5个员工的所有部门。

思路：查询emp表，按照部门编号分组计数，筛选出>=5的。  
select deptno from emp  
group by deptno  
having count(\*) >= 5;

--------------------

17、列出月薪比“Smith”多的员工信息。

思路：先获取Smith的月薪，作为条件查询emp表。  
select \* from emp  
where sal > (select sal from emp where ename = ‘Smith’);

--------------------

18、列出所有“Clerk”办事员的姓名及其部门名称，部门的所有人数。

思路：先找出emp中所有的办事员，再和dept表连接，然后获取所有部门的人数，作为临时表t，继续连接即可。  
select e.ename, d.dname, t.cnt  
from emp e  
join dept d  
on e.deptno = d.deptno  
join (select deptno, count(\*) cnt from emp group by deptno) t  
on e.deptno = t.deptno  
where e.job = ‘Clerk’;

--------------------

19、列出最低月薪大于1500的各种工作及从事此工作的全部人数。

思路：先按照工作分组，找出最低月薪大于1500的，再计数即可。  
select job, count(\*)  
from emp  
group by job having min(sal) > 1500

--------------------

20、列出在部门“Sales”工作的员工姓名。

思路：先获取到“Sales”的部门编号，作为条件筛选员工。  
select ename from emp where deptno =  
(select deptno from dept where dname = ‘Sales’);

--------------------

21、列出月薪高于公司平均月薪的员工，及其所在部门，上级领导，工资等级。

思路：先得到公司平均月薪，作为条件查询出工资比这还高的员工信息，再和emp外连接获取上级领导，和salgrade连接获取工资等级。  
select e.ename, e.deptno, e1.ename, s.grade  
from emp e  
left join emp e1  
on e.mgr = e1.empno  
join salgrade s  
on e.sal between s.lowsal and s.highsal  
where e.sal > (select avg(sal) from emp);

--------------------

22、列出与“Scott”员工从事相同工作 的员工及其所在部门名称。

思路：先获取“Scott”的工作岗位，作为条件筛选员工，再和dept表连接，获取部门名称。  
select e.ename, e.job, d.dname  
from emp e  
join dept d  
on e.deptno = d.deptno  
where e.job = (select job from emp where ename = ‘Scott’) and e.ename != ‘Scott’;

--------------------

23、列出月薪 等于部门30中员工的月薪 的其他部门员工 的姓名及月薪。

思路：先获取30部门员工的工资表，再获取其他部门的员工。  
select e.ename, e.sal from emp e  
where e.sal in (select sal from emp where deptno = 30) and e.deptno != 30;

--------------------

24、列出月薪高于部门30工作的所有员工的月薪的员工姓名，月薪，部门名称。

思路：先找出30部门人员最高的月薪，作为条件，让emp和dept连接即可。  
select e.ename, e.sal, d.dname  
from emp e  
join dept d  
on e.deptno = d.deptno  
where e.sal > (select max(sal) from emp group by deptno having deptno = 30);

--------------------

25、列出在每个部门工作的员工数量，平均工资，平均服务期限。

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

例如距离现在差了多少年：TimeStampDiff(year, hiredate, now())

间隔类型有：second秒，minute分，hour时，day天，week周，month月，quarter季度，year年。

思路：使用外连接。  
select d.deptno, count(e.ename) ‘人数’, ifnull(avg(e.sal), 0) ‘平均月薪’, ifnull(avg(timestampdiff(year, e.hiredate, now())), 0) ‘平均服务年数’  
from emp e  
right join dept d  
on e.deptno = d.deptno  
group by d.deptno;

--------------------

26、列出所有员工的姓名、部门名称和工资。

思路：就是普通的内连接。  
select e.ename, d.dname, e.sal  
from emp e  
join dept d  
on e.deptno = d.deptno;

--------------------

27、列出所有部门的详细信息和人数。

思路：使用外连接，按照dept所有信息分组，计数员工即可。  
select d.\*, count(e.empno)  
from emp e  
right join dept d  
on e.deptno = d.deptno  
group by d.deptno, d.dname, d.loc;

--------------------

28、列出各种工作的最低工资，及那个员工的姓名。

思路：先按照工作分组，获取其最低工资，作为临时表，再和emp表连接获取员工姓名。  
select t.\*, e.ename  
from (select job, min(sal) minsal from emp group by job) t  
join emp e  
on t.job = e.job and t.minsal = e.sal;

--------------------

29、列出各个部门的Manager（领导者）的最低月薪。

思路：先用“Manager”过滤，再按照部门分组，获取其最低月薪即可。  
select deptno, min(sal)  
from emp  
where job = ‘Manager’  
group by deptno;

--------------------

30、列出所有员工的年薪，按照年薪从低到高排序。

思路：要考虑奖金问题。  
select ename, (sal + ifnull(comm, 0))\*12 yearsal  
from emp  
order by yearsal asc;

--------------------

31、求出员工其领导的月薪超过3000的员工与领导名称。

思路：使用自连接。  
select e1.ename ‘员工’, e2.ename ‘领导’  
from emp e1  
join emp e2  
on e1.mgr = e2.empno  
where e2.sal > 3000;

--------------------

32、求出部门名称中，带“S”字符的部门员工的工资合计，部门人数。

思路：使用外连接，按照部门分组，再计数即可。  
select d.deptno, d.dname, ifnull(sum(e.sal), 0) ‘工资合计’, count(e.empno) ‘部门人数’  
from emp e  
right join dept d  
on e.deptno = d.deptno  
where d.dname like ‘%s%’  
group by d.deptno, d.dname;

--------------------

33、给任职日期超过30年的员工加薪10%。

思路：update emp set sal=sal\*1.1 where 任职日期 > 30年。  
update emp  
set sal = sal \* 1.1  
where timestampdiff(year, hiredate, now()) > 30;

--------------------