学习和巩固mysql的经典练习题目

一 做题目需要的准备

下面的题目都是建立在下面三张表的基础上的

分别为emp部门表,dept部门表,以及salgrade工资等级表;

mysql> select * from 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-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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

emp 员工表中各个字段的介绍
empno 员工编号
ename 员工姓名
job 员工工作职位
mgr 员工上级领导的员工编号
hiredate 员工的入职时间
sal 员工的工资
comm 员工的奖金
deptno 员工所在部门的编号

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10

dept 部门表的字段介绍
deptno 部门编号
dname 部门的名字
loc 部门地点

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

salgrade工资等级表的字段介绍
grade 工资等级
losal 该工资的最低区间
hisal 该工资的最高区间

二 题目

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

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

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

4、不准用组函数（Max ），取得最高薪水

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

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

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

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

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

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

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

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

13、面试题：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

三 题目解析

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

第一步,查询出每一个部门的最高工资

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

mysql> select deptno,max(sal) as maxsal from emp group by deptno;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

然后可以将上面查询的数据当成一张临时的表
和emp进行连接查询出最高工资对应的名字

mysql> 
select ename,t.deptno,maxsal 
from emp  e 
join 
(select deptno,max(sal) as maxsal from emp group by deptno)
as t 
on  e.deptno = t.deptno and e.sal = t.maxsal;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

总结:在分组之后,select后端只能跟分组字段或者分组函数

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

先找出每个部门的平均薪水,将该表看成一张临时表

select deptno,avg(sal) avgsal from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

select ename,sal 
	from emp e 
	join 
	(select deptno,avg(sal) avgsal from emp group by deptno) 
	as t 
	on e.deptno = t.deptno and sal > avgsal;
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

注意:平均的薪水等级和平均薪水的等级是不一样的

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。
平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

平均的薪水等级
涉及到两张表emp和salgrade

连接条件emp.sal between losal and hisal

select e.ename,e.deptno,s.grade from emp e join salgrade s on  e.sal between s.losal and s.hisal;
+--------+--------+-------+
| ename  | deptno | grade |
+--------+--------+-------+
| SMITH  |     20 |     1 |
| ALLEN  |     30 |     3 |
| WARD   |     30 |     2 |
| JONES  |     20 |     4 |
| MARTIN |     30 |     2 |
| BLAKE  |     30 |     4 |
| CLARK  |     10 |     4 |
| SCOTT  |     20 |     4 |
| KING   |     10 |     5 |
| TURNER |     30 |     3 |
| ADAMS  |     20 |     1 |
| JAMES  |     30 |     1 |
| FORD   |     20 |     4 |
| MILLER |     10 |     2 |
+--------+--------+-------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

然后再求每个部门的平均薪水水平(下面的方式使用的是将上面的查询结果看成是一张临时表)

select t.deptno,avg(grade) from (select e.ename,e.deptno,s.grade from emp e join salgrade s on  e.sal between s.losal and s.hisal) as t group by deptno
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
|     10 |     3.6667 |
|     20 |     2.8000 |
|     30 |     2.5000 |
+--------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

不将第一步的结果看成是一张临时表,而是将查询出来的内容直接通过部门编号进行分组,直接一步到位,比将查询结果看成是一张临时表更加的简便。

select e.deptno,avg(s.grade) 
from emp e 
join salgrade s 
on  e.sal between s.losal and s.hisal 
 group by e.deptno;
 +--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

平均薪水的等级
先计算平均薪水,然后计算

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

然后计算平均薪水等级

select t.deptno,s.grade from salgrade s join (select deptno,avg(sal) avgsal from emp group by deptno)  t on t.avgsal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     30 |     3 |
|     10 |     4 |
|     20 |     4 |
+--------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

发现和第一种方式有点不一样

注意:不是所有的情况都是使用临时表的方式,一定要具体情况具体分析

4、不准用组函数（Max ），取得最高薪水

方式一
直接使用排序

select ename,sal from emp order by sal desc limit 1; 
或者
select ename,sal from emp order by sal desc limit 0,1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

方式二
使用表的自连接的方式

select 
	sal 
from  
	emp 
where 
	sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

这题中使用自连接方式主要是为了练习使用自连接的的方法,当然在本题中不建议使用自连接

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

方式一:使用排序的方式
实现思路:先求出每个部门的平均薪水,然后进行排序

select deptno,avg(sal) as avgsal  from emp group by deptno order by  deptno limit 1; 
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

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

上题中已经知道了最高部门的部门编号,将上题中的查询结果当成是一张临时的表和dept表进行连接即可

select 
	deptno,avg(sal) as avgsal
 from 
	emp
 group by deptno
 order by 
	avgsal desc 
 limit 
	1;				
	+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

select 
	d.dname,avg(sal) as avgsal
 from 
	emp e 
  join  
  	dept d 
  on e.deptno = d.deptno 					
 group by d.dname 
 order by 
	avgsal desc 
 limit 
	1;				
	+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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

实现思路:首先求出每一个部门的平均薪水作为以一张表并通过排序的方式找出等级最低的一张临时表中然后和dept进行连接查询

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

select deptno,avg(sal) as salavg from emp group by deptno;
+--------+-------------+
| deptno | salavg      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

第二步 找出每一个部门的平均薪水的等级

select t.*,s.grade from (select d.dname,avg(sal) as salavg from emp e join dept d on d.deptno = e.deptno group by d.dname) t join salgrade s on t.salavg between s.losal and s.hisal;
+------------+-------------+-------+
| dname      | salavg      | grade |
+------------+-------------+-------+
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
| RESEARCH   | 2175.000000 |     4 |
+------------+-------------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

第三步取出等级最低的
首先需要知道的是平均薪水最低的平均薪水等级也必然最低,但是一个等级可能对应多个薪水。因此可以先找出最低的平均薪水所对应的薪水等级。
找出最低的平均薪水

select avg(sal) salavg from emp group by deptno order by salavg limit 1;
+-------------+
| salavg      |
+-------------+
| 1566.666667 |
+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

查询该平均薪水对应的等级

select grade from salgrade where (select avg(sal) salavg from emp group by deptno order by salavg limit 1) between losal and hisal;
+-------+
| grade |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

第四步
将第三步的结果作为第二步查询结果的条件即可

select t.*,s.grade from (select d.dname,avg(sal) as salavg from emp e join dept d on d.deptno = e.deptno group by d.dname) t join salgrade s on t.salavg between s.losal and s.hisal where s.grade = (select grade from salgrade where (select avg(sal) salavg from emp group by deptno order by salavg limit 1) between losal and hisal);
+-------+-------------+-------+
| dname | salavg      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

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

题目分析:可以肯定的是在mrg的编号都是领导,并且比普通员工最高工资还要高的一定是领导

本题涉及到一张表emp
实现思路:
首先找出所有领导的编号,不在领导的编号内的就是普通员工,然后得出普通员工中的最高薪资,然后在emp表中查询出所有比该最高工资高的名单

第一步.查询出所有领导的编号

 select distinct mgr from emp where mgr is not null
 +------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

第二步得出普通员工的最高工资

select max(sal) salmax from emp where  EMPNO not in ( select distinct mgr from emp where mgr is not null);
+---------+
| salmax  |
+---------+
| 1600.00 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

第三步,得出emp表中所有比普通员工最高工资还高的数据

select ename,sal from emp where sal > (select max(sal) salmax from emp where  EMPNO not in ( select distinct mgr from emp where mgr is not null));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

**总结:**在本题中需注意的是not in在使用的时候,后面的小括号中记得排除掉null,不然就会出现问题。

9、取得薪水最高的前五名员工(单表)

本题涉及到一张表emp

select ename,sal from emp order by sal desc limit 0,5;
或者写成,将前面0省略
select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

10、取得薪水最高的第六到第十名员工(单表)

select ename,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

11、取得最后入职的 5 名员工(单表)

分析:需要注意的是日期也可以降序，升序。

select ename,hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD   | 1981-12-03 |
| JAMES  | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

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

本题涉及到两张表emp和salgrade
首先查询出每一个员工的薪水等级
然后通过薪水等级进行分组
最后进行统计

第一步

select e.ename,s.grade from emp e join salgrade s on e.sal between losal and hisal;
+--------+-------+
| ename  | grade |
+--------+-------+
| SMITH  |     1 |
| ALLEN  |     3 |
| WARD   |     2 |
| JONES  |     4 |
| MARTIN |     2 |
| BLAKE  |     4 |
| CLARK  |     4 |
| SCOTT  |     4 |
| KING   |     5 |
| TURNER |     3 |
| ADAMS  |     1 |
| JAMES  |     1 |
| FORD   |     4 |
| MILLER |     2 |
+--------+-------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

通过薪水等级进行分组统计每组的数据

select t.grade,count(*) from (select e.ename,s.grade from emp e join salgrade s on e.sal between losal and hisal) t group by t.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

上面的写法比较啰嗦,可以用下面的写法,直接一步到位不需要使用临时表

select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

13、面试题：(有一定的难度)

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

第一题:

14、列出所有员工及领导的姓名(较难)

使用的是emp表,将一张表当成两张表使用,一张当作员工表,一张当作领导表

select a.ename '员工',b.ename '领导' from emp a join emp b on  a.mgr = b.empno;
+--------+-------+
| 员工      | 领导    |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

上面写的有问题,没有将领导为空的情况考虑进去,要使用外连接,将做员工表中存在而在领导表中不存在的数据也要考虑进去。

select a.ename '员工',b.ename '领导' from emp a left join emp b on  a.mgr = b.empno;
+--------+-------+
| 员工      | 领导    |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

两张表emp和dept,emp使用了两次
在上题的基础进行
将一张当成两张表使用
首先查出早于上机的所有的员工

select a.ename '员工',a.empno '员工编号' from emp a join emp b on a.mgr = b.empno where a.hiredate<b.hiredate; 
1

然后通过和dept连接得出部门名字

select a.ename '员工',a.empno '员工编号',d.dname '部门名' from emp a join emp b on a.mgr = b.empno where a.hiredate<b.hiredate join dept d on a.deptno = d.deptno; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join dept d on a.deptno = d.deptno' at line 1
1
2

上面的写法感觉没有问题,但是报错,经过分析发现上面的写法存在问题,

select a.ename '员工',a.hiredate '入职时间',a.empno '员工编号',d.dname '部门名',b.ename '领导',b.hiredate '领导入职时间' from emp a join emp b on a.mgr = b.empno join dept d on a.deptno = d.deptno where a.hiredate<b.hiredate; 
+-------+------------+----------+------------+-------+--------------+
| 员工     | 入职时间         | 员工编号        | 部门名           | 领导    | 领导入职时间         |
+-------+------------+----------+------------+-------+--------------+
| CLARK | 1981-06-09 |     7782 | ACCOUNTING | KING  | 1981-11-17   |
| SMITH | 1980-12-17 |     7369 | RESEARCH   | FORD  | 1981-12-03   |
| JONES | 1981-04-02 |     7566 | RESEARCH   | KING  | 1981-11-17   |
| ALLEN | 1981-02-20 |     7499 | SALES      | BLAKE | 1981-05-01   |
| WARD  | 1981-02-22 |     7521 | SALES      | BLAKE | 1981-05-01   |
| BLAKE | 1981-05-01 |     7698 | SALES      | KING  | 1981-11-17   |
+-------+------------+----------+------------+-------+--------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

另一种比较啰嗦的写法如下,通过构建一张临时表的方式实现

select t.ename,t.empno,d.dname from (select a.ename ename,a.empno empno,a.deptno deptno from emp a join emp b on a.mgr = b.empno where a.hiredate<b.hiredate) t join dept d on t.deptno = d.deptno; 
+-------+-------+------------+
| ename | empno | dname      |
+-------+-------+------------+
| CLARK |  7782 | ACCOUNTING |
| SMITH |  7369 | RESEARCH   |
| JONES |  7566 | RESEARCH   |
| ALLEN |  7499 | SALES      |
| WARD  |  7521 | SALES      |
| BLAKE |  7698 | SALES      |
+-------+-------+------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

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

双表emp表和dept
如果这个时候直接使用两个表进行连接

select e.*,d.dname from emp e join dept d on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

通过上面的第一条sql语句的查询结果可以知道,只显示了三个部门,二总共有4个部门,有一个部门没有员工,所以显然不符合题目的要求。

在这个时候想到了另外一种连接方式,就是外连接,外连接的特点就是除了将次要表中的数据查询出来之外,主表中的数据不会丢失

select e.*,d.dname from emp e right join dept d on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 | RESEARCH   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
15 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

从结果可以看出来,最后一个部门OPERATIONS 的信息为空,没有任何员工,但是该部门存在。

17、列出至少有 5 个员工的所有部门(单表)

select deptno from emp group by deptno having count(*)>=5;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

如果想让最后的信息更加的详细,可以和dept进行连接查询

select e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno group by deptno having count(*)>=5;
+--------+----------+
| deptno | dname    |
+--------+----------+
|     20 | RESEARCH |
|     30 | SALES    |
+--------+----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

18、列出薪金比"SMITH" 多的所有员工信息(单表)

先得出SMITH工资

select sal from emp where  ename = 'SMITH';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

select * from emp where sal > (select sal from emp where  ename = 'SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |    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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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

第一次做的时候没有直接做出来
没有想到可以两个分支都单独查询,然后通过连接的方式进行查询

首先查询出所有的办事员所在的部门的部门名字和部门编号

select e.ename,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK';
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| MILLER |     10 | ACCOUNTING |
| SMITH  |     20 | RESEARCH   |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
+--------+--------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

查询每一个部门的人数

select deptno,count(*) deptmentCount from emp group by deptno;
+--------+---------------+
| deptno | deptmentCount |
+--------+---------------+
|     10 |             3 |
|     20 |             5 |
|     30 |             6 |
+--------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

将上面查询出来的临时的表进行连接查询

select  t1.ename,t1.deptno,t1.dname,t2.deptmentCount  from  
(select e.ename,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'CLERK') t1 
join 
(select deptno,count(*) deptmentCount from emp group by deptno) t2 
on t1.deptno = t2.deptno;
+--------+--------+------------+---------------+
| ename  | deptno | dname      | deptmentCount |
+--------+--------+------------+---------------+
| MILLER |     10 | ACCOUNTING |             3 |
| SMITH  |     20 | RESEARCH   |             5 |
| ADAMS  |     20 | RESEARCH   |             5 |
| JAMES  |     30 | SALES      |             6 |
+--------+--------+------------+---------------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

按照工作岗位进行分组得出工作岗位的大于1500的

select job,count(*) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

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

先查出sales部门的部门编号

select deptno from dept where dname = 'SALES';
+--------+
| deptno |
+--------+
|     30 |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

select e.ename from emp e where deptno = (select deptno from dept where dname = 'SALES');
或者
select e.ename from emp e where deptno in (select deptno from dept where dname = 'SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

需要连接三张表

select 
	e.ename '员工',d.dname,l.ename '领导',s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
left join
	emp l
on
	e.mgr = l.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp);
-----+------------+-------+-------+
| 员工   | dname     | 领导   | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH   | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| CLARK | ACCOUNTING | KING  |     4 |
| SCOTT | RESEARCH   | JONES |     4 |
| KING  | ACCOUNTING | NULL  |     5 |
| FORD  | RESEARCH   | JONES |     4 |
+-------+------------+-------+-------+
6 rows in set (0.00 sec)	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

注意,上面有一个地方需要使用外连接,因为上面说了查出所有的员工,没有上级领导也要查询出来,

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

先查出从事的工作

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';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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

找出30中所有员工的薪金

select sal from emp where deptno = 30;
1

select ename,sal from emp where sal in (select sal from emp where deptno = 30) and deptno <> 30;
Empty set (0.00 sec)
1
2

更加严谨的答案

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

Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

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

先求出30部门的最高薪金

select sal from emp where deptno = 30 order by sal desc limit 1;
1

select e.ename,d.dname,e.sal from emp e join dept d on e.deptno = d.deptno where sal > (select sal from emp where deptno = 30 order by sal desc limit 1) and e.deptno <> 30;
+-------+------------+---------+
| ename | dname      | sal     |
+-------+------------+---------+
| JONES | RESEARCH   | 2975.00 |
| SCOTT | RESEARCH   | 3000.00 |
| KING  | ACCOUNTING | 5000.00 |
| FORD  | RESEARCH   | 3000.00 |
+-------+------------+---------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

在我第一次写的答案中存在问题,不需要写条件 e.deptno <> 30;

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 where deptno = 30);
1
2
3
4
5
6
7
8
9
10

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限(涉及到外连接,较难)

在本题中需要考虑的问题是,有的部门是没有员工,但是也需要考虑进去,但是使用内连接,就会导致部门的丢失,需要让部门表为主表

select d.deptno,avg(sal),count(*) from emp e right join dept d  on e.deptno = d.deptno group by e.deptno; 
+--------+-------------+----------+
| deptno | avg(sal)    | count(*) |
+--------+-------------+----------+
|     40 |        NULL |        1 |
|     10 | 2916.666667 |        3 |
|     20 | 2175.000000 |        5 |
|     30 | 1566.666667 |        6 |
+--------+-------------+----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

上面我的解答存在问题

没有员工的部门，部门人数是0

select 
	d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;
	+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | avgservicetime |
+--------+--------+-------------+----------------+
|     10 |      3 | 2916.666667 |        40.3333 |
|     20 |      5 | 2175.000000 |        38.4000 |
|     30 |      6 | 1566.666667 |        40.5000 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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

timestampdiff(YEAR, hiredate, now())

间隔类型：
	SECOND   秒，
	MINUTE   分钟，
	HOUR   小时，
	DAY   天，
	WEEK   星期
	MONTH   月，
	QUARTER   季度，
	YEAR   年
1
2
3
4
5
6
7
8
9
10
11

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

select e.ename,d.dname,e.sal from emp e join dept d  on e.deptno = d.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

select d.*,count(e.ename) ecount from dept d left join emp e on 	d.deptno = e.deptno group by d.deptno;
+--------+------------+----------+--------+
| DEPTNO | DNAME      | LOC      | ecount |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |      3 |
|     20 | RESEARCH   | DALLAS   |      5 |
|     30 | SALES      | CHICAGO  |      6 |
|     40 | OPERATIONS | BOSTON   |      0 |
+--------+------------+----------+--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

根详细的写法

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

+--------+------------+----------+----------------+
| deptno | dname      | loc      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

29、列出各种工作的最低工资及从事此工作的雇员姓名(单表)

select ename,min(sal),job from emp group by job;
+-------+----------+-----------+
| ename | min(sal) | job       |
+-------+----------+-----------+
| SCOTT |  3000.00 | ANALYST   |
| SMITH |   800.00 | CLERK     |
| JONES |  2450.00 | MANAGER   |
| KING  |  5000.00 | PRESIDENT |
| ALLEN |  1250.00 | SALESMAN  |
+-------+----------+-----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

上面的是我第一遍的写法,虽然看上去没有问题,但是实际上存在很大的问题,使用了分组函数,并没有以ename进行分组,但却将其写上去了,很明显是不正确的写法

首先找出各种工作的最低工资

select  job,min(sal) from emp group by job;
1

select e.ename,t.salmax,t.job from emp e join (select  job,min(sal) salmax
 from emp group by job) t on e.job = t.job;
 +--------+---------+-----------+
| ename  | salmax  | job       |
+--------+---------+-----------+
| SMITH  |  800.00 | CLERK     |
| ALLEN  | 1250.00 | SALESMAN  |
| WARD   | 1250.00 | SALESMAN  |
| JONES  | 2450.00 | MANAGER   |
| MARTIN | 1250.00 | SALESMAN  |
| BLAKE  | 2450.00 | MANAGER   |
| CLARK  | 2450.00 | MANAGER   |
| SCOTT  | 3000.00 | ANALYST   |
| KING   | 5000.00 | PRESIDENT |
| TURNER | 1250.00 | SALESMAN  |
| ADAMS  |  800.00 | CLERK     |
| JAMES  |  800.00 | CLERK     |
| FORD   | 3000.00 | ANALYST   |
| MILLER |  800.00 | CLERK     |
+--------+---------+-----------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

上面我的写法中掉了一个判断条件

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

+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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

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

+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

注意的是年薪=薪金加奖金

select ename,(sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

select a.ename '员工',b.ename,b.sal '领导' from emp a join emp b on a.mgr = b.empno where b.sal > 3000;
+-------+-------+---------+
| 员工     | ename | 领导      |
+-------+-------+---------+
| JONES | KING  | 5000.00 |
| BLAKE | KING  | 5000.00 |
| CLARK | KING  | 5000.00 |
+-------+-------+---------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

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

select d.dname,ifnull(sum(e.sal),0),count(e.ename) from dept  d left join
 emp e on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno; 
 +------------+----------------------+----------------+
| dname      | ifnull(sum(e.sal),0) | count(e.ename) |
+------------+----------------------+----------------+
| RESEARCH   |             10875.00 |              5 |
| SALES      |              9400.00 |              6 |
| OPERATIONS |                 0.00 |              0 |
+------------+----------------------+----------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

更详细的写法

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

+--------+------------+---------+----------------+----------+
| deptno | dname      | loc     | count(e.ename) | sumsal   |
+--------+------------+---------+----------------+----------+
|     20 | RESEARCH   | DALLAS  |              5 | 10875.00 |
|     30 | SALES      | CHICAGO |              6 |  9400.00 |
|     40 | OPERATIONS | BOSTON  |              0 |     0.00 |
+--------+------------+---------+----------------+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

34、给任职日期超过 30 年的员工加薪 10%.(单表)

就加这一次发工资

select  ename,(sal*0.1+sal) sal from emp where timestampdiff(YEAR, hiredate, now())>30;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  880.00 |
| ALLEN  | 1760.00 |
| WARD   | 1375.00 |
| JONES  | 3272.50 |
| MARTIN | 1375.00 |
| BLAKE  | 3135.00 |
| CLARK  | 2695.00 |
| SCOTT  | 3300.00 |
| KING   | 5500.00 |
| TURNER | 1650.00 |
| ADAMS  | 1210.00 |
| JAMES  | 1045.00 |
| FORD   | 3300.00 |
| MILLER | 1430.00 |
+--------+---------+
14 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

永久加工资

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

四 数据库源文件

下面是三个数据库的源文件
emp表

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `emp` WRITE;
INSERT INTO `emp` VALUES (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-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);
UNLOCK TABLES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

dept表

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `dept` WRITE;
/*!40000 ALTER TABLE `dept` DISABLE KEYS */;
INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
UNLOCK TABLES;
1
2
3
4
5
6
7
8
9
10
11

salgrade

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `GRADE` int(11) DEFAULT NULL,
  `LOSAL` int(11) DEFAULT NULL,
  `HISAL` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `salgrade` WRITE;
INSERT INTO `salgrade` VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
UNLOCK TABLES;
1
2
3
4
5
6
7
8
9