数据库系统原理与应用教程（071）—— MySQL 练习题：操作题 110-120（十五）：综合练习

110、限定查询的记录数（1）

员工表：employees，表结构和数据如下：

/*
drop table if exists  `employees` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
*/
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
|  10011 | 1953-11-07 | Mary       | Sluis     | F      | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
11 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
30
31
32
33
34
35
36
37
38
39

【问题】查询最晚入职员工的所有信息，查询结果如下：

员工表：employees，表结构和数据如下：

解答：

mysql> select * from employees order by hire_date desc limit 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

111、限定查询的记录数（2）

员工表：employees，表结构和数据如下：

/*
drop table if exists  `employees` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');
*/
mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
|  10011 | 1953-11-07 | Mary       | Sluis     | F      | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
11 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
30
31
32
33
34
35
36
37
38
39

【问题】查询入职时间排名倒数第三（如果第三名有重复，则全部取出）的员工所有信息，查询结果如下：

解答：

/*
select * from employees where emp_no in 
(select emp_no from (select emp_no from employees order by hire_date limit 2,1) a);
*/
mysql> select * from employees where emp_no in 
    -> (select emp_no from (select emp_no from employees order by hire_date limit 2,1) a);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

112、连接查询（1）

该题目使用的表和数据如下：

/*
drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
*/
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

薪水表：salaries，表中数据如下：

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
|  10003 |  43311 | 2001-12-01 | 9999-01-01 |
|  10004 |  74057 | 2001-11-27 | 9999-01-01 |
|  10005 |  94692 | 2001-09-09 | 9999-01-01 |
|  10006 |  43311 | 2001-08-02 | 9999-01-01 |
|  10007 |  88070 | 2002-02-07 | 9999-01-01 |
+--------+--------+------------+------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

部门表：dept_manager，表中数据如下：

mysql> select * from dept_manager;
+---------+--------+------------+
| dept_no | emp_no | to_date    |
+---------+--------+------------+
| d001    |  10002 | 9999-01-01 |
| d004    |  10004 | 9999-01-01 |
| d003    |  10005 | 9999-01-01 |
| d002    |  10006 | 9999-01-01 |
+---------+--------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

【问题】查询各个部门当前领导的薪水详情以及其对应的部门编号dept_no，输出结果以 salaries.emp_no 升序排序，查询结果如下：

解答：

/*
select s.emp_no, s.salary, s.from_date, s.to_date, d.dept_no
from dept_manager d join salaries s on d.emp_no = s.emp_no
order by s.emp_no;
*/
mysql> select s.emp_no, s.salary, s.from_date, s.to_date, d.dept_no
    -> from dept_manager d join salaries s on d.emp_no = s.emp_no
    -> order by s.emp_no;
+--------+--------+------------+------------+---------+
| emp_no | salary | from_date  | to_date    | dept_no |
+--------+--------+------------+------------+---------+
|  10002 |  72527 | 2001-08-02 | 9999-01-01 | d001    |
|  10004 |  74057 | 2001-11-27 | 9999-01-01 | d004    |
|  10005 |  94692 | 2001-09-09 | 9999-01-01 | d003    |
|  10006 |  43311 | 2001-08-02 | 9999-01-01 | d002    |
+--------+--------+------------+------------+---------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

113、连接查询（2）

该题目使用的表和数据如下：

/*
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

员工表：employees，表中数据如下：

mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

部门表：dept_emp，表中数据如下：

mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 1986-06-26 | 9999-01-01 |
|  10002 | d002    | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

【问题】查询所有已经分配部门的员工的 last_name 和 first_name 以及 dept_no，未分配部门的员工不显示，查询结果如下：

解答：

/*
select e.last_name, e.first_name, d.dept_no
from employees e join dept_emp d on e.emp_no = d.emp_no;
*/
mysql> select e.last_name, e.first_name, d.dept_no
    -> from employees e join dept_emp d on e.emp_no = d.emp_no;
+-----------+------------+---------+
| last_name | first_name | dept_no |
+-----------+------------+---------+
| Facello   | Georgi     | d001    |
| Simmel    | Bezalel    | d002    |
+-----------+------------+---------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

114、连接查询（3）

该题目使用的表和数据如下：

/*
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

员工表：employees，表中数据如下：

mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
+--------+------------+------------+-----------+--------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

部门表：dept_emp，表中数据如下：

mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 1986-06-26 | 9999-01-01 |
|  10002 | d002    | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

【问题】查询所有已经分配部门的员工（包括暂时没有分配具体部门的员工）的 last_name 和 first_name 以及 dept_no，查询结果如下：

/*
select e.last_name, e.first_name, d.dept_no
from employees e left join dept_emp d on e.emp_no = d.emp_no;
*/
mysql> select e.last_name, e.first_name, d.dept_no
    -> from employees e left join dept_emp d on e.emp_no = d.emp_no;
+-----------+------------+---------+
| last_name | first_name | dept_no |
+-----------+------------+---------+
| Facello   | Georgi     | d001    |
| Simmel    | Bezalel    | d002    |
| Bamford   | Parto      | NULL    |
| Koblick   | Chirstian  | NULL    |
+-----------+------------+---------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

115、分组查询

该题目使用的表和数据如下：

/*
drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
*/
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

薪水表：salaries，表中数据如下：

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 1996-08-03 | 1997-08-03 |
+--------+--------+------------+------------+
18 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

【问题】查询薪水记录超过 15 条的员工号 emp_no 以及其对应的记录次数t，查询结果如下：

解答：

/*
select emp_no, count(*) t
from salaries group by emp_no having t > 15;
*/
mysql> select emp_no, count(*) t
    -> from salaries group by emp_no having t > 15;
+--------+----+
| emp_no | t  |
+--------+----+
|  10001 | 17 |
+--------+----+
1 row in set (0.03 sec)
1
2
3
4
5
6
7
8
9
10
11
12

116、消除重复元组

该题目使用的表和数据如下：

/*
drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,72527,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
*/
1
2
3
4
5
6
7
8
9
10
11
12

薪水表：salaries，表中数据如下：

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  72527 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
|  10003 |  43311 | 2001-12-01 | 9999-01-01 |
+--------+--------+------------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

【问题】查询所有员工具体的薪水（salary）情况（相同的薪水只显示一次），并按照薪水降序显示，查询结果如下：

解答：

mysql> select distinct salary from salaries order by salary desc;
+--------+
| salary |
+--------+
|  72527 |
|  43311 |
+--------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

117、子查询（1）

该题目使用的表和数据如下：

/*
drop table if exists  `dept_manager` ; 
drop table if exists  `employees` ; 
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

员工表：employees，表中数据如下：

mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
+--------+------------+------------+-----------+--------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

部门领导表：dept_manager，表中数据如下：

mysql> select * from dept_manager;
+---------+--------+------------+------------+
| dept_no | emp_no | from_date  | to_date    |
+---------+--------+------------+------------+
| d001    |  10002 | 1996-08-03 | 9999-01-01 |
| d002    |  10003 | 1990-08-05 | 9999-01-01 |
+---------+--------+------------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

【问题】查询所有非部门领导的员工emp_no，查询结果如下：

解答：

mysql> select emp_no from employees where emp_no not in (select emp_no from dept_manager);
+--------+
| emp_no |
+--------+
|  10001 |
+--------+
1 row in set (0.08 sec)
1
2
3
4
5
6
7

118、连接查询

该题目使用的表和数据如下：

/*
drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

员工表；dept_emp，表中数据如下：

mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 1986-06-26 | 9999-01-01 |
|  10002 | d001    | 1996-08-03 | 9999-01-01 |
|  10003 | d002    | 1995-12-03 | 9999-01-01 |
+--------+---------+------------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

部门经理表：dept_manager，表中数据如下：

mysql> select * from dept_manager;
+---------+--------+------------+------------+
| dept_no | emp_no | from_date  | to_date    |
+---------+--------+------------+------------+
| d001    |  10002 | 1996-08-03 | 9999-01-01 |
| d002    |  10003 | 1990-08-05 | 9999-01-01 |
+---------+--------+------------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

【问题】获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，查询结果如下：

解答：

/*
select de.emp_no, dm.emp_no manager 
from dept_emp de join dept_manager dm on de.dept_no = dm.dept_no
where de.emp_no not in (select emp_no from dept_manager);
*/
mysql> select de.emp_no, dm.emp_no manager 
    -> from dept_emp de join dept_manager dm on de.dept_no = dm.dept_no
    -> where de.emp_no not in (select emp_no from dept_manager);
+--------+---------+
| emp_no | manager |
+--------+---------+
|  10001 |   10002 |
+--------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

119、分组查询/子查询

该题目使用的表和数据如下：

/*
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

员工表：dept_emp，表中数据如下：

mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 1986-06-26 | 9999-01-01 |
|  10002 | d001    | 1996-08-03 | 9999-01-01 |
|  10003 | d002    | 1996-08-03 | 9999-01-01 |
+--------+---------+------------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

薪水表：salaries，表中数据如下：

mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
|  10003 |  92527 | 2001-08-02 | 9999-01-01 |
+--------+--------+------------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

【问题】查询每个部门中当前员工薪水最高的相关信息，显示：dept_no，emp_no 以及其对应的 salary，按照部门编号dept_no 升序排列，查询结果如下：

解答：

/*
select a.dept_no, emp_no, a.maxSalary
from salaries s join
(select d.dept_no, max(salary) maxSalary
from salaries s join dept_emp d on s.emp_no = d.emp_no
group by d.dept_no) a on s.salary = a.maxSalary
order by a.dept_no;
*/
mysql> select a.dept_no, emp_no, a.maxSalary
    -> from salaries s join
    -> (select d.dept_no, max(salary) maxSalary
    -> from salaries s join dept_emp d on s.emp_no = d.emp_no
    -> group by d.dept_no) a on s.salary = a.maxSalary
    -> order by a.dept_no;
+---------+--------+-----------+
| dept_no | emp_no | maxSalary |
+---------+--------+-----------+
| d001    |  10001 |     88958 |
| d002    |  10003 |     92527 |
+---------+--------+-----------+
2 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

120、构造查询条件

该题目使用的表和数据如下：

/*
drop table if exists  `employees` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Bezalel','Mary','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1953-11-07','Mary','Sluis','F','1990-01-22');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

员工表：employees，表中数据如下：

mysql> select * from employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Bezalel    | Mary      | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1953-11-07 | Mary       | Sluis     | F      | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

【问题】查询 employees 表所有 emp_no 为奇数，且 last_name 不为 Mary 的员工信息，并按照 hire_date 降序排列，查询结果如下：

解答：

/*
select * from employees 
where mod(emp_no, 2) = 1 and last_name <> 'Mary'
order by hire_date desc;
*/
mysql> select * from employees 
    -> where mod(emp_no, 2) = 1 and last_name <> 'Mary'
    -> order by hire_date desc;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10005 | 1953-11-07 | Mary       | Sluis     | F      | 1990-01-22 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15