Mysql 34道经典试题答案及解析

最新推荐文章于 2024-03-26 22:33:48 发布

哈希马普

最新推荐文章于 2024-03-26 22:33:48 发布

阅读量2.2k

点赞数 6

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/zhuyacheneagle/article/details/108030137

版权

mysql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

首先初始化部门表、薪资等级表、员工表数据：

drop table if exists dept;
drop table if exists salgrade;
drop table if exists emp;
 
create table dept(
		deptno int(10) primary key,
		dname varchar(14),
		loc varchar(13)
		);
		
create table salgrade(
		grade int(11),
		losal int(11),
		hisal int(11)
		);
		
create table emp(
		empno int(4) primary key,
		ename varchar(10),
		job varchar(9),
		mgr int(4),
		hiredate date,
		sal double(7,2),
		comm double(7,2),
		deptno int(2)
		);
		
insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESEARCHING','DALLAS');
insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');
 
insert into salgrade(grade,losal,hisal) values(1,700,1200);
insert into salgrade(grade,losal,hisal) values(2,1201,1400);
insert into salgrade(grade,losal,hisal) values(3,1401,2000);
insert into salgrade(grade,losal,hisal) values(4,2001,3000);
insert into salgrade(grade,losal,hisal) values(5,3001,5000);
 
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7369,'SIMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);
	
select * from dept;
select * from salgrade;
select * from emp;

第1题：取得每个部门最高薪水的人员名称

思路：
1、取得每个部门最高薪水的人员

 select max(sal),ename from emp group by deptno;

±---------±-------+
| max(sal) | ename |
±---------±-------+
| 5000.00 | CLARK |
| 3000.00 | SIMITH |
| 2850.00 | ALLEN |
±---------±-------+
2、但每个部门最高薪水的可能有多个人员，所以还得查员工表中同为该部门最高薪水的人员

elect e.sal,e.ename,e.deptno from emp e join ( select max(sal) sal,ename
,deptno from emp group by deptno) me on e.sal=me.sal and e.deptno=me.deptno;

±--------±------±-------+
| sal | ename | deptno |
±--------±------±-------+
| 2850.00 | BLAKE | 30 |
| 3000.00 | SCOTT | 20 |
| 5000.00 | KING | 10 |
| 3000.00 | FORD | 20 |
±--------±------±-------+

第2题：哪些人的薪水在部门平均薪水之上

思路：
1、求部门平均薪水

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

2、哪些人的薪水在其部门大于刚求的部门平均薪水

select e.ename,e.sal,e.deptno from emp e join (select avg(sal) sal,ename,
deptno from emp group by deptno) ae on e.deptno = ae.deptno where e.sal>ae.sal;

±------±--------±-------+
| ename | sal | deptno |
±------±--------±-------+
| KING | 5000.00 | 10 |
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| FORD | 3000.00 | 20 |
| ALLEN | 1600.00 | 30 |
| BLAKE | 2850.00 | 30 |
±------±--------±-------+

第3题：取得每个部门中所有人的平均薪水等级！

3.1 取得部门中所有人的平均薪水的等级

思路：
1、取得每个部门所有人的平均薪水

select avg(sal), deptno,ename from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+

2、取得每个部门中所有人的平均薪水等级，上面的表与等级表关联，使用between and求得上表中薪资在等级表中对应的等级。

select t1.avgsal, t1.deptno, salgrade.grade from (select avg(sal) avgsal,
 deptno from emp group by deptno)as t1 join salgrade where t1.avgsal between sal
grade.losal and hisal;
+-------------+--------+-------+
| avgsal      | deptno | grade |
+-------------+--------+-------+
| 2916.666667 |     10 |     4 |
| 2175.000000 |     20 |     4 |
| 1566.666667 |     30 |     3 |
+-------------+--------+-------+

3.2 取得部门中所有人的薪水等级平均值

思路：
1、取得所有人的薪水等级

select deptno,grade from emp join salgrade salg on emp.sal between salg.l
osal and hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     20 |     1 |
|     30 |     3 |
|     30 |     2 |
|     20 |     4 |
|     30 |     2 |
|     30 |     4 |
|     10 |     4 |
|     20 |     4 |
|     10 |     5 |
|     30 |     3 |
|     20 |     1 |
|     30 |     1 |
|     20 |     4 |
|     10 |     2 |
+--------+-------+

2、取得部门中所有人的薪水等级平均值

mysql> select emp.deptno,avg(grade) from emp join salgrade salg on emp.sal betwe
en salg.losal and hisal group by deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
|     10 |     3.6667 |
|     20 |     2.8000 |
|     30 |     2.5000 |
+--------+------------+

第4题：不准用组函数（max），取得最高薪水（给出两种解决方案）

第一种：思路：排序limit1

select * from emp order by sal desc limit 0,1;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

第二种：思路：自连接

1、自连接后查出较小的薪水

mysql> select distinct(a1.sal) from emp a1 join emp a2 on a1.sal<a2.sal;
+---------+
| sal     |
+---------+
|  800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+

那么不在这个较小的薪水范围内的就是最高的薪水

select e.sal from emp e where e.sal not in ( select distinct(a1.sal) from
 emp a1 join emp a2 on a1.sal<a2.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.06 sec)

第5题：取得平均薪水最高的部门的部门编号(可能有一样的最后高薪水部门存在)

思路：1、取得各个部门平均薪水

select avg(sal) ,deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+

2、取得平均薪水最高的部门的部门编号，这里使用having过滤那些等于最高薪水的，因为不止一个部门等于最高薪水

select avg(e.sal) asal,e.deptno from emp e group by deptno having asal =(
select max(a.asal) from  (select avg(sal) asal ,deptno from emp  
group by deptno) as a ) ;
+-------------+--------+
| asal        | deptno |
+-------------+--------+
| 2916.666667 |     10 |
+-------------+--------+

第6题：取得平均薪水最高的部门的部门名称

思路：在第5题基础上再join部门表获取部门名称

mysql> select avg(e.sal)asal, e.deptno, dname from emp e join dept d on e.deptno
 =d.deptno group by deptno having asal=(select max(a.asal) from (select avg(sal)
 asal, e.deptno from emp e group by deptno) as a);
+-------------+--------+------------+
| asal        | deptno | dname      |
+-------------+--------+------------+
| 2916.666667 |     10 | ACCOUNTING |
+-------------+--------+------------+

第7题：求平均薪水的等级最低的部门的部门名称

思路：
1、求每个部门的平均薪水

mysql> select t.asal, s.grade from ( select avg(sal) asal from emp group by dept
no) as t join salgrade s on t.asal between s.losal and s.hisal;
+-------------+-------+
| asal        | grade |
+-------------+-------+
| 2916.666667 |     4 |
| 2175.000000 |     4 |
| 1566.666667 |     3 |
+-------------+-------+

2、求上表等级最低的

mysql> select min(tt.grade),tt.deptno from (select t.asal, s.grade,t.deptno from
 ( select avg(sal) asal,e.deptno from emp e group by deptno) as t join salgrade
s on t.asal between s.losal and s.hisal)as tt;
+---------------+--------+
| min(tt.grade) | deptno |
+---------------+--------+
|             3 |     10 |
+---------------+--------+

3、求上表最低的部门的名称

mysql> select min(tt.grade),tt.deptno,d.dname from (select t.asal, s.grade,t.dep
tno from ( select avg(sal) asal,e.deptno from emp e group by deptno) as t join s
algrade s on t.asal between s.losal and s.hisal)as tt join dept d on tt.deptno=d
.deptno;
+---------------+--------+-------+
| min(tt.grade) | deptno | dname |
+---------------+--------+-------+
|             3 |     30 | SALES |
+---------------+--------+-------+

第8题：取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名 !

思路：
1、查询在mgr上出现的：

mysql> select distinct(mgr) from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+

2、查询普通员工（员工代码没有在mgr上出现的）

 mysql> select * from emp where empno not in ( select distinct(mgr) from emp);
Empty set (0.00 sec)

查出来的是空，这是因为not in 不会自动忽略空值，in会自动忽略空值,not in 里面的参数值的关系值都是and，in里面的参数值的关系都是or，如果有null值，它们就会运算，有null参与的数学运算结果都为null，所以这里要过滤掉null的情况。

mysql> select * from emp where empno not in ( select distinct(mgr) from emp wher
e mgr is not null);
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SIMITH | 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 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

3、求出普通员工薪资最高的

mysql> select max(sal) from emp where empno not in ( select distinct(mgr) from e
mp where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+

4、取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名

mysql> select ename from emp where sal > (select max(sal) from emp where empno n
ot in (select distinct(mgr) from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

第9题：取得薪水最高的前五名员工

mysql> select empno,ename,sal from emp order by sal desc limit 0,5;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7839 | KING  | 5000.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
+-------+-------+---------+

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

mysql> select empno,ename,sal from emp order by sal desc limit 5,5;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7782 | CLARK  | 2450.00 |
|  7499 | ALLEN  | 1600.00 |
|  7844 | TURNER | 1500.00 |
|  7934 | MILLER | 1300.00 |
|  7654 | MARTIN | 1250.00 |
+-------+--------+---------+

第11题：取得最后入职的5名员工

mysql> select empno,ename,hiredate from emp order by hiredate desc limit 0,5;
+-------+--------+------------+
| empno | ename  | hiredate   |
+-------+--------+------------+
|  7876 | ADAMS  | 1987-05-23 |
|  7788 | SCOTT  | 1987-04-19 |
|  7934 | MILLER | 1982-01-23 |
|  7902 | FORD   | 1981-12-03 |
|  7900 | JAMES  | 1981-12-03 |
+-------+--------+------------+

第12题：取得每个薪水等级有多少员工

思路：
1、求出各个员工所在薪资等级

mysql> select empno,ename,sal,grade from emp join salgrade on sal between losal
and hisal;
+-------+--------+---------+-------+
| empno | ename  | sal     | grade |
+-------+--------+---------+-------+
|  7369 | SIMITH |  800.00 |     1 |
|  7499 | ALLEN  | 1600.00 |     3 |
|  7521 | WARD   | 1250.00 |     2 |
|  7566 | JONES  | 2975.00 |     4 |
|  7654 | MARTIN | 1250.00 |     2 |
|  7698 | BLAKE  | 2850.00 |     4 |
|  7782 | CLARK  | 2450.00 |     4 |
|  7788 | SCOTT  | 3000.00 |     4 |
|  7839 | KING   | 5000.00 |     5 |
|  7844 | TURNER | 1500.00 |     3 |
|  7876 | ADAMS  | 1100.00 |     1 |
|  7900 | JAMES  |  950.00 |     1 |
|  7902 | FORD   | 3000.00 |     4 |
|  7934 | MILLER | 1300.00 |     2 |
+-------+--------+---------+-------+

2、分组count

mysql> select count(empno),grade from emp join salgrade on sal between losal and
 hisal group by grade;
+--------------+-------+
| count(empno) | grade |
+--------------+-------+
|            3 |     1 |
|            3 |     2 |
|            2 |     3 |
|            5 |     4 |
|            1 |     5 |
+--------------+-------+

第13题：

有三个表s(学生表)，c(课程表)，sc(学生选课表)
S（SNo, SName）代表（学号, 姓名）
C（CNo, CName, CTeacher）代表（课号, 课名, 老师）
SC（SNo, CNo, Score）代表（学号, 课号, 成绩）

首先初始化数据：
drop table if exists s;
drop table if exists c;
drop table if exists sc;
 
create table s(
		sno int(10) primary key auto_increment,
		sname varchar(32)
		);
		
create table c(
		cno int(10) primary key auto_increment,
		cname varchar(32),
		cteacher varchar(32)
		);
// sc表中学生与课程是多对多关系，主键设成谁（复合主键(sno,cno)）	
// sno和cno也必须来自学生表和课程表，因此他们是主键的同时也是外键
// 一个表只能有一个主键但是可以有多个外键


create table sc(
		sno int(10),
  	    cno int(10),
		scgrade double(3,1),
		constraint sc_sno_cno_pk primary key(sno,cno), 
		constraint sc_sno_fk foreign key(sno) references s(sno),
		constraint sc_cno_fk foreign key(cno) references c(cno)
		);
		
insert into s(sname) values('张三');
insert into s(sname) values('李四');
insert into s(sname) values('王五');
insert into s(sname) values('赵六');
 
insert into c(cname,cteacher) values('java','王老师');
insert into c(cname,cteacher) values('C++','张老师');
insert into c(cname,cteacher) values('C#','李老师');
insert into c(cname,cteacher) values('mysql','周老师');
insert into c(cname,cteacher) values('oracle','黎明');
 
insert into sc(sno,cno,scgrade) values(1,1,30);
insert into sc(sno,cno,scgrade) values(1,2,50);
insert into sc(sno,cno,scgrade) values(1,3,80);
insert into sc(sno,cno,scgrade) values(1,4,80);
insert into sc(sno,cno,scgrade) values(1,5,70);
insert into sc(sno,cno,scgrade) values(2,2,80);
insert into sc(sno,cno,scgrade) values(2,3,50);
insert into sc(sno,cno,scgrade) values(2,4,70);
insert into sc(sno,cno,scgrade) values(2,5,80);
insert into sc(sno,cno,scgrade) values(3,1,60);
insert into sc(sno,cno,scgrade) values(3,2,70);
insert into sc(sno,cno,scgrade) values(3,3,60);
insert into sc(sno,cno,scgrade) values(4,3,50);
insert into sc(sno,cno,scgrade) values(4,5,40);
	
select * from s;
select * from c;
select * from sc;

问题：

1、找出没选过”黎明”老师的所有学生姓名 !

思路：简单子语句嵌套查询，首先找出选过”黎明”老师的所有学生编号

mysql>  select * from sc where sc.cno =(select c.cno from c where c.cteacher = '
黎明');
+-----+-----+---------+
| sno | cno | scgrade |
+-----+-----+---------+
|   3 |   5 |      52 |
|   4 |   5 |      40 |
+-----+-----+---------+

然后找出没选过”黎明”老师的所有学生姓名

mysql> select sname from s where s.sno not in ( select sc.sno from sc where sc.c
no =(select c.cno from c where c.cteacher = '黎明'));
+-------+
| sname |
+-------+
| a     |
| b     |
+-------+

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

思路：1、列出2门以上（含2门）不及格学生编号

mysql> select  count(cno) count,sno from sc where scgrade<60  group by sno havin
g count>1;
+-------+-----+
| count | sno |
+-------+-----+
|     3 |   1 |
+-------+-----+

2、顺便求出其平均分

mysql> select  count(cno) count,sno,avg(scgrade) from sc where scgrade<60  group
 by sno having count>1;
+-------+-----+--------------+
| count | sno | avg(scgrade) |
+-------+-----+--------------+
|     3 |   1 |      50.0000 |
+-------+-----+--------------+

3、s和上表关联求出姓名

mysql> select sname,t.avg from s join (select count(cno) count,sno,avg(scgrade)
avg from sc where scgrade<60 group by sno having count>1)t on s.sno = t.sno;
+-------+---------+
| sname | avg     |
+-------+---------+
| a     | 50.0000 |
+-------+---------+

3、既学过1号课程也学过2号课程的学生 (高频)

解法1思路：1、找出既学过1号课程也学过2号课程的学生编号

mysql> select count(cno) count,sno from sc where cno in (1,2) group by sno having count>1;
+-------+-----+
| count | sno |
+-------+-----+
|     2 |   1 |
|     2 |   3 |
+-------+-----+

2、求出学生姓名

mysql> select s.sno,s.sname from s join (select count(cno) count,sno from sc whe
re cno in (1,2) group by sno having count>1)t on s.sno =t.sno;
+-----+-------+
| sno | sname |
+-----+-------+
|   1 | a     |
|   3 | c     |
+-----+-------+

第14题：列出所有员工及领导的名字(重点“所有”)

解法思路：因为KING是大BOSS，他的上级是null，使用JOIN的话会漏掉他，所以使用left join

mysql> select e1.ename,e1.empno,e1.mgr,e2.ename from emp e1 left join emp e2 on
e1.mgr=e2.empno;
+--------+-------+------+-------+
| ename  | empno | mgr  | ename |
+--------+-------+------+-------+
| SIMITH |  7369 | 7902 | FORD  |
| ALLEN  |  7499 | 7698 | BLAKE |
| WARD   |  7521 | 7698 | BLAKE |
| JONES  |  7566 | 7839 | KING  |
| MARTIN |  7654 | 7698 | BLAKE |
| BLAKE  |  7698 | 7839 | KING  |
| CLARK  |  7782 | 7839 | KING  |
| SCOTT  |  7788 | 7566 | JONES |
| KING   |  7839 | NULL | NULL  |
| TURNER |  7844 | 7698 | BLAKE |
| ADAMS  |  7876 | 7788 | SCOTT |
| JAMES  |  7900 | 7698 | BLAKE |
| FORD   |  7902 | 7566 | JONES |
| MILLER |  7934 | 7782 | CLARK |
+--------+-------+------+-------+

第15题：列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称(三表连接)

解题思路：
1、子查询出日期早于其直接上级的所有员工编号、姓名

mysql> select e1.ename,e1.empno,e1.mgr,e2.ename,e1.deptno from emp e1 join emp e
2 on e1.mgr=e2.empno and e1.hiredate<e2.hiredate;
+--------+-------+------+-------+--------+
| ename  | empno | mgr  | ename | deptno |
+--------+-------+------+-------+--------+
| SIMITH |  7369 | 7902 | FORD  |     20 |
| ALLEN  |  7499 | 7698 | BLAKE |     30 |
| WARD   |  7521 | 7698 | BLAKE |     30 |
| JONES  |  7566 | 7839 | KING  |     20 |
| BLAKE  |  7698 | 7839 | KING  |     30 |
| CLARK  |  7782 | 7839 | KING  |     10 |
+--------+-------+------+-------+--------+

2、上面表作为临时表join on部门表

mysql> select t.tname,t.empno,d.dname from (select e1.ename tname,e1.empno,e1.mg
r,e2.ename,e1.deptno from emp e1 join emp e2 on e1.mgr=e2.empno and e1.hiredate<
e2.hiredate) t join dept d on t.deptno=d.deptno;
+--------+-------+-------------+
| tname  | empno | dname       |
+--------+-------+-------------+
| SIMITH |  7369 | RESEARCHING |
| ALLEN  |  7499 | SALES       |
| WARD   |  7521 | SALES       |
| JONES  |  7566 | RESEARCHING |
| BLAKE  |  7698 | SALES       |
| CLARK  |  7782 | ACCOUNTING  |
+--------+-------+-------------+

第16题：列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门(外连接)

解题思路：部门表left join 员工表

mysql> select dept.deptno, dept.dname,e.empno, e.ename from dept left join emp
e  on dept.deptno=e.deptno;
+--------+-------------+-------+--------+
| deptno | dname       | empno | ename  |
+--------+-------------+-------+--------+
|     20 | RESEARCHING |  7369 | SIMITH |
|     30 | SALES       |  7499 | ALLEN  |
|     30 | SALES       |  7521 | WARD   |
|     20 | RESEARCHING |  7566 | JONES  |
|     30 | SALES       |  7654 | MARTIN |
|     30 | SALES       |  7698 | BLAKE  |
|     10 | ACCOUNTING  |  7782 | CLARK  |
|     20 | RESEARCHING |  7788 | SCOTT  |
|     10 | ACCOUNTING  |  7839 | KING   |
|     30 | SALES       |  7844 | TURNER |
|     20 | RESEARCHING |  7876 | ADAMS  |
|     30 | SALES       |  7900 | JAMES  |
|     20 | RESEARCHING |  7902 | FORD   |
|     10 | ACCOUNTING  |  7934 | MILLER |
|     40 | OPERATIONS  |  NULL | NULL   |
+--------+-------------+-------+--------+

第17题：列出至少有5个员工的所有部门

count后使用having进行过滤

mysql> select count(1) as count,deptno from emp group by deptno having count>4;
+-------+--------+
| count | deptno |
+-------+--------+
|     5 |     20 |
|     6 |     30 |
+-------+--------+

第18题：列出薪水比"SIMITH"多的所有员工信息

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

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  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-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 |    NULL |     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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

第19题：列出所有"CLERK"（办事员）的姓名及其部门名称，部门人数

解决思路：
1、列出所有"CLERK"（办事员）的姓名及其部门名称

mysql> select e.ename, e.deptno,d.dname from emp e  join dept d on e.deptno=d.de
ptno and e.job='CLERK';
+--------+--------+-------------+
| ename  | deptno | dname       |
+--------+--------+-------------+
| SIMITH |     20 | RESEARCHING |
| ADAMS  |     20 | RESEARCHING |
| JAMES  |     30 | SALES       |
| MILLER |     10 | ACCOUNTING  |
+--------+--------+-------------+

2、求出各个部门人数

mysql> select count(1) count,deptno from emp group by deptno;
+-------+--------+
| count | deptno |
+-------+--------+
|     3 |     10 |
|     5 |     20 |
|     6 |     30 |
+-------+--------+

3、上面两个表联表查询

mysql> select t.ename, t.dname,a.count from (select e.ename, e.deptno,d.dname fr
om emp e  join dept d on e.deptno=d.deptno and e.job='CLERK')t join ( select cou
nt(1) count,deptno from emp group by deptno) a on t.deptno=a.deptno;
+--------+-------------+-------+
| ename  | dname       | count |
+--------+-------------+-------+
| SIMITH | RESEARCHING |     5 |
| ADAMS  | RESEARCHING |     5 |
| JAMES  | SALES       |     6 |
| MILLER | ACCOUNTING  |     3 |
+--------+-------------+-------+

第20题：列出最低薪水大于1500的各种工作以及从事此工作的全部雇员人数！

mysql> select e.job, min(e.sal) as minsal, count(e.ename) as total
    -> from emp e
    -> group by e.job having minsal > 1500;
+-----------+---------+-------+
| job       | minsal  | total |
+-----------+---------+-------+
| ANALYST   | 3000.00 |     2 |
| MANAGER   | 2450.00 |     3 |
| PRESIDENT | 5000.00 |     1 |
+-----------+---------+-------+

第21题：列出在部门"SALES"（销售部）工作的员工姓名，假定不知道销售部门的部门编号

mysql> select e.ename from emp e,dept d where e.deptno=d.deptno and d.dname='SAL
ES';
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

第22题：列出薪资高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级(四表连接)

mysql> select t.empno, t.ename, t.deptno,d.dname,t.mgr,ee.ename upename,s.grade
from (select empno,ename,deptno,mgr,sal from emp a where a.sal> (select avg(sal)
 avg from emp))t join dept d on t.deptno=d.deptno join salgrade s on t.sal betwe
en s.losal and s.hisal left join emp ee on t.mgr=ee.empno;
+-------+-------+--------+-------------+------+---------+-------+
| empno | ename | deptno | dname       | mgr  | upename | grade |
+-------+-------+--------+-------------+------+---------+-------+
|  7566 | JONES |     20 | RESEARCHING | 7839 | KING    |     4 |
|  7698 | BLAKE |     30 | SALES       | 7839 | KING    |     4 |
|  7782 | CLARK |     10 | ACCOUNTING  | 7839 | KING    |     4 |
|  7788 | SCOTT |     20 | RESEARCHING | 7566 | JONES   |     4 |
|  7839 | KING  |     10 | ACCOUNTING  | NULL | NULL    |     5 |
|  7902 | FORD  |     20 | RESEARCHING | 7566 | JONES   |     4 |
+-------+-------+--------+-------------+------+---------+-------+

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

解题思路：join on 对中间结果过滤，where对最后结果过滤

mysql> select t.empno,t.ename,t.deptno,d.dname from (select empno, ename,deptno
from emp e where job=(select job from emp where ename='SCOTT'))t join dept d on
t.deptno=d.deptno;
+-------+-------+--------+-------------+
| empno | ename | deptno | dname       |
+-------+-------+--------+-------------+
|  7788 | SCOTT |     20 | RESEARCHING |
|  7902 | FORD  |     20 | RESEARCHING |
+-------+-------+--------+-------------+

第24题：列出薪资等于部门30中员工的薪资的其他部门员工的姓名和薪资

解题思路：使用in，在某个范围内

mysql> select * from emp e where e.sal in (select distinct sal from emp e where
e.deptno=30) and e.deptno!=30;
Empty set (0.00 sec)

第25题：列出薪资高于在部门30工作的所有员工的薪资的员工姓名、薪资和部门名称

mysql> select e.ename,e.sal,e.deptno,d.dname from emp e join dept d on e.deptno=
d.deptno where sal>(select max(sal) from emp where deptno ='30');
+-------+---------+--------+-------------+
| ename | sal     | deptno | dname       |
+-------+---------+--------+-------------+
| JONES | 2975.00 |     20 | RESEARCHING |
| SCOTT | 3000.00 |     20 | RESEARCHING |
| KING  | 5000.00 |     10 | ACCOUNTING  |
| FORD  | 3000.00 |     20 | RESEARCHING |
+-------+---------+--------+-------------+

第26题：列出在每个部门工作的员工数量，平均薪资和平均服务期限（to_days）（now()）!

解题思路：to_days函数：距离公元元年多少天

mysql> select deptno,count(empno), avg(sal),(to_days (now())-to_days( hiredate))
/365 from emp group by deptno;
+--------+--------------+-------------+-----------------------------------------
-+
| deptno | count(empno) | avg(sal)    | (to_days (now())-to_days( hiredate))/365
 |
+--------+--------------+-------------+-----------------------------------------
-+
|     10 |            3 | 2916.666667 |                                  39.2740
 |
|     20 |            5 | 2175.000000 |                                  39.7507
 |
|     30 |            6 | 1566.666667 |                                  39.5726
 |
+--------+--------------+-------------+-----------------------------------------

第27题：列出所有员工的姓名、部门名称和工资!

解题思路：列出所有的时候，注意使用left join和right join ，防止为NULL的情况。

mysql> select e.ename,d.dname, e.sal from emp e right join dept d on e.deptno=d.
deptno;
+--------+-------------+---------+
| ename  | dname       | sal     |
+--------+-------------+---------+
| SIMITH | RESEARCHING |  800.00 |
| ALLEN  | SALES       | 1600.00 |
| WARD   | SALES       | 1250.00 |
| JONES  | RESEARCHING | 2975.00 |
| MARTIN | SALES       | 1250.00 |
| BLAKE  | SALES       | 2850.00 |
| CLARK  | ACCOUNTING  | 2450.00 |
| SCOTT  | RESEARCHING | 3000.00 |
| KING   | ACCOUNTING  | 5000.00 |
| TURNER | SALES       | 1500.00 |
| ADAMS  | RESEARCHING | 1100.00 |
| JAMES  | SALES       |  950.00 |
| FORD   | RESEARCHING | 3000.00 |
| MILLER | ACCOUNTING  | 1300.00 |
| NULL   | OPERATIONS  |    NULL |
+--------+-------------+---------+

第28题：列出所有部门的详细信息和人数

mysql> select d.*,count(e.empno) from dept d left join emp e on d.deptno=e.deptn
o group by d.deptno;
+--------+-------------+----------+----------------+
| deptno | dname       | loc      | count(e.empno) |
+--------+-------------+----------+----------------+
|     10 | ACCOUNTING  | NEW YORK |              3 |
|     20 | RESEARCHING | DALLAS   |              5 |
|     30 | SALES       | CHICAGO  |              6 |
|     40 | OPERATIONS  | BOSTON   |              0 |
+--------+-------------+----------+----------------+

第29题：列出各种工作的最低工资以及从事此工作的雇员姓名!

mysql> select minsal ,e.ename from emp e join (select min(sal) minsal,ename,job
 from emp group by job)t on e.job =t.job where e.sal=t.minsal;
+---------+--------+
| minsal  | ename  |
+---------+--------+
|  800.00 | SIMITH |
| 1250.00 | WARD   |
| 1250.00 | MARTIN |
| 2450.00 | CLARK  |
| 3000.00 | SCOTT  |
| 5000.00 | KING   |
| 3000.00 | FORD   |
+---------+--------+

第30题：列出各个部门manager的最低薪资

mysql> select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+

第31题：列出所有员工的年工资，按年薪从低到高排序(ifnull)

ifnull() 函数用于判断第一个表达式是否为 NULL，如为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
comm是提成

mysql> select ename,(12*sal+ifnull(comm,0)) from emp order by sal asc;
+--------+-------------------------+
| ename  | (12*sal+ifnull(comm,0)) |
+--------+-------------------------+
| SIMITH |                 9600.00 |
| JAMES  |                11400.00 |
| ADAMS  |                13200.00 |
| WARD   |                15500.00 |
| MARTIN |                16400.00 |
| MILLER |                15600.00 |
| TURNER |                18000.00 |
| ALLEN  |                19500.00 |
| CLARK  |                29400.00 |
| BLAKE  |                34200.00 |
| JONES  |                35700.00 |
| FORD   |                36000.00 |
| SCOTT  |                36000.00 |
| KING   |                60000.00 |
+--------+-------------------------+

第32题：求出员工领导的薪水超过3000的员工名称和领导名称

mysql> select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno
where e2.sal>3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING  |
| BLAKE | KING  |
| CLARK | KING  |
+-------+-------+

第33题：求部门名称中带s字符的部门员工的工资合计、部门人数(like 与通配符)

mysql> select sum(sal), count(1),d.dname from emp e join dept d on e.deptno=d.de
ptno where d.dname like '%s%' group by d.deptno;
+----------+----------+-------------+
| sum(sal) | count(1) | dname       |
+----------+----------+-------------+
| 10875.00 |        5 | RESEARCHING |
|  9400.00 |        6 | SALES       |
+----------+----------+-------------+

第34题：给任职日期超过30年的员工加薪10%(select 是查询，这个是修改update)

为了不影响原表，做了备份表emp1

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

mysql> select * from emp1 where (to_days(now())-to_days(hiredate)/365)>30;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SIMITH | CLERK     | 7902 | 1980-12-17 |  880.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1760.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1375.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 3272.50 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1375.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 3135.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2695.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3300.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5500.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1650.00 |    NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1210.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 1045.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3300.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1430.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

题目很多，但是做完还是很有收获的~

哈希马普

关注

6
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
Mysql 34道经典试题答案及解析

首先初始化数据：drop table if exists dept;drop table if exists salgrade;drop table if exists emp; create table dept( deptno int(10) primary key, dname varchar(14), loc varchar(13) ); create table salgrade( grade int(11), losal int(11), hisal
复制链接

扫一扫