• 数据库系统原理与应用教程（072）—— MySQL 练习题：操作题 121-130（十六）：综合练习

    数据库系统原理与应用教程（072）—— MySQL 练习题：操作题 121-130（十六）：综合练习

    121、分组查询

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

    /*
drop table if exists  `salaries` ; 
drop table if exists  titles;
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','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');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','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

    员工职称表：titles，表中数据如下：

    mysql> select * from titles;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  10003 | Senior Engineer | 2001-12-01 | 9999-01-01 |
|  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
|  10006 | Senior Engineer | 2001-08-02 | 9999-01-01 |
|  10007 | Senior Staff    | 1996-02-11 | 9999-01-01 |
+--------+-----------------+------------+------------+
5 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

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

    mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 1986-06-26 | 9999-01-01 |
|  10003 |  43311 | 2001-12-01 | 9999-01-01 |
|  10004 |  74057 | 1995-12-01 | 9999-01-01 |
|  10006 |  43311 | 2001-08-02 | 9999-01-01 |
|  10007 |  88070 | 2002-02-07 | 9999-01-01 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    【问题】查询各个 title 类型对应员工的平均工资。显示 title 以及平均工资，并且以平均工资升序排序，查询结果如下：

    titleavg(s.salary)
    Senior Engineer62409.2500
    Senior Staff88070.0000

    解答：

    /*
select t.title, avg(s.salary)
from titles t join salaries s on t.emp_no = s.emp_no
group by t.title
order by 2;
*/
mysql> select t.title, avg(s.salary)
    -> from titles t join salaries s on t.emp_no = s.emp_no
    -> group by t.title
    -> order by 2;
+-----------------+---------------+
| title           | avg(s.salary) |
+-----------------+---------------+
| Senior Engineer |    62409.2500 |
| Senior Staff    |    88070.0000 |
+-----------------+---------------+
2 rows in set (0.00 sec)

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

    122、子查询（1）

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

    /*
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,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');
*/

    • 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 |  88958 | 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

    【问题】查询薪水第二多的员工（如有重复则全部取出）的 emp_no 以及其对应的薪水 salary，并按 emp_no 升序排序。查询结果如下：

    emp_nosalary
    1000272527

    解答：

    /*
select emp_no, salary from salaries where salary in
(select salary from (select salary from salaries order by salary desc limit 1,1) a)
order by emp_no;
*/
mysql> select emp_no, salary from salaries where salary in
    -> (select salary from (select salary from salaries order by salary desc limit 1,1) a);
+--------+--------+
| emp_no | salary |
+--------+--------+
|  10002 |  72527 |
+--------+--------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    123、子查询（2）

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

    /*
drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
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`));
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 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 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');
*/

    • 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

    员工表：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

    薪水表：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 |
+--------+--------+------------+------------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    【问题】查询薪水排名第二多的员工编号 emp_no、薪水 salary、last_name 以及 first_name，查询结果如下：

    emp_nosalarylast_namefirst_name
    1000474057KoblickChirstian
    /*
select e.emp_no, s.salary, e.last_name, e.first_name
from employees e join salaries s on e.emp_no = s.emp_no
where s.salary in
(select max(salary) from salaries where salary < (select max(salary) from salaries));
*/
mysql> select e.emp_no, s.salary, e.last_name, e.first_name
    -> from employees e join salaries s on e.emp_no = s.emp_no
    -> where s.salary in
    -> (select max(salary) from salaries where salary < (select max(salary) from salaries));
+--------+--------+-----------+------------+
| emp_no | salary | last_name | first_name |
+--------+--------+-----------+------------+
|  10004 |  74057 | Koblick   | Chirstian  |
+--------+--------+-----------+------------+
1 row in set (0.04 sec)

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

    124、连接查询（1）

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

    /*
drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
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 departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
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','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');
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
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33

    员工表：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

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

    mysql> select * from departments;
+---------+-----------------+
| dept_no | dept_name       |
+---------+-----------------+
| d001    | Marketing       |
| d002    | Finance         |
| d003    | Human Resources |
+---------+-----------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    部门员工关系表：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    | 1990-08-05 | 9999-01-01 |
+--------+---------+------------+------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    【问题】查询所有员工的 last_name 和 first_name 以及对应的 dept_name，包括暂时没有分配部门的员工，查询结果如下：

    last_namefirst_namedept_name
    FacelloGeorgiMarketing
    SimmelBezalelMarketing
    BamfordPartoFinance
    KoblickChirstianNULL

    解答：

    /*
select e.last_name, e.first_name, d.dept_name
from dept_emp de join departments d on de.dept_no = d.dept_no
     right join employees e on e.emp_no = de.emp_no;
*/
mysql> select e.last_name, e.first_name, d.dept_name
    -> from dept_emp de join departments d on de.dept_no = d.dept_no
    ->      right join employees e on e.emp_no = de.emp_no;
+-----------+------------+-----------+
| last_name | first_name | dept_name |
+-----------+------------+-----------+
| Facello   | Georgi     | Marketing |
| Simmel    | Bezalel    | Marketing |
| Bamford   | Parto      | Finance   |
| 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
    • 16
    • 17

    125、连接查询（2）

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

    /*
drop table if exists  `employees` ; 
drop table if exists  `salaries` ;
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`));
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 employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
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,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
*/

    • 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      | 2001-06-22 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1999-08-03 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

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

    mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 1999-08-03 | 2000-08-02 |
|  10002 |  72527 | 2000-08-02 | 2001-08-02 |
+--------+--------+------------+------------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    【问题】查询在职员工自入职以来的薪水涨幅情况，给出在职员工编号 emp_no 以及其对应的薪水涨幅 growth，并按照growth 进行升序排列，查询结果如下：

    emp_nogrowth
    100013861

    解答：

    /*
select e.emp_no, a.salary - s.salary growth
from employees e join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date
join (select emp_no, salary from salaries where to_date = '9999-01-01') a
on s.emp_no = a.emp_no order by growth;
*/
mysql> select e.emp_no, a.salary - s.salary growth
    -> from employees e join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date
    -> join (select emp_no, salary from salaries where to_date = '9999-01-01') a
    -> on s.emp_no = a.emp_no order by growth;
+--------+--------+
| emp_no | growth |
+--------+--------+
|  10001 |   3861 |
+--------+--------+
1 row in set (0.00 sec)

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

    126、连接查询（3）

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

    /*
drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
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 departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','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,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','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','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
    • 27
    • 28
    • 29
    • 30

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

    mysql> select * from departments;
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d001    | Marketing |
| d002    | Finance   |
+---------+-----------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    部门员工关系表：dept_emp，表中数据如下：

    mysql> select * from dept_emp;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 2001-06-22 | 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 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 1996-08-03 | 9999-01-01 |
|  10003 |  32323 | 1996-08-03 | 9999-01-01 |
+--------+--------+------------+------------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    【问题】统计各个部门的工资记录数，给出部门编码（dept_no），部门名称（dept_name）以及部门在 salaries 表里面有多少条记录（sum），按照 dept_no 升序排序，查询结果如下：

    dept_nodept_namesum
    d001Marketing3
    d002Finance1

    解答：

    /*
select d.dept_no, d.dept_name, count(*) sum
from salaries s join dept_emp de on s.emp_no = de.emp_no
join departments d on de.dept_no = d.dept_no
group by d.dept_no, d.dept_name
order by d.dept_no;
*/
mysql> select d.dept_no, d.dept_name, count(*) sum
    -> from salaries s join dept_emp de on s.emp_no = de.emp_no
    -> join departments d on de.dept_no = d.dept_no
    -> group by d.dept_no, d.dept_name
    -> order by d.dept_no;
+---------+-----------+-----+
| dept_no | dept_name | sum |
+---------+-----------+-----+
| d001    | Marketing |   3 |
| d002    | Finance   |   1 |
+---------+-----------+-----+
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

    127、查询数据排名

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

    /*
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,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,72527,'2001-12-01','9999-01-01');
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    薪水表：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 |  72527 | 2001-12-01 | 9999-01-01 |
+--------+--------+------------+------------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    【问题】对所有员工的薪水按照 salary 降序进行排名，如果 salary 相同，再按照 emp_no 升序排列，查询结果如下：

    emp_nosalaryt_rank
    10001889581
    10002725272
    10004725272
    10003433113

    解答：

    /*
select emp_no, salary, 
       @rank := @rank + (CASE WHEN @salary = salary THEN 0 WHEN @salary := salary THEN 1 END) t_rank 
from salaries,(select @rank := 0) a,(select @salary := null) b
order by salary desc, emp_no;
*/
mysql> select emp_no, salary, 
    ->        @rank := @rank + (CASE WHEN @salary = salary THEN 0 WHEN @salary := salary THEN 1 END) t_rank 
    -> from salaries,(select @rank := 0) a,(select @salary := null) b
    -> order by salary desc, emp_no;
+--------+--------+--------+
| emp_no | salary | t_rank |
+--------+--------+--------+
|  10001 |  88958 |      1 |
|  10002 |  72527 |      2 |
|  10004 |  72527 |      2 |
|  10003 |  43311 |      3 |
+--------+--------+--------+
4 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

    128、连接查询

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

    /*
drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
drop table if exists  `employees` ; 
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 `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`));
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_manager VALUES('d001',10002,'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','1996-08-03');
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','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
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    员工表：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      | 1996-08-03 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    部门员工关系表：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 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

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

    mysql> select * from dept_manager;
+---------+--------+------------+------------+
| dept_no | emp_no | from_date  | to_date    |
+---------+--------+------------+------------+
| d001    |  10002 | 1996-08-03 | 9999-01-01 |
+---------+--------+------------+------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

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

    mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 1986-06-26 | 9999-01-01 |
|  10002 |  72527 | 1996-08-03 | 9999-01-01 |
+--------+--------+------------+------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    【问题】查询所有非 manager 员工的薪水情况，显示：dept_no，emp_no 以及 salary，查询结果如下：

    dept_noemp_nosalary
    d0011000188958
    /*
select de.dept_no, e.emp_no, s.salary
from dept_emp de join employees e on de.emp_no = e.emp_no
join salaries s on e.emp_no = s.emp_no
where e.emp_no not in (select emp_no from dept_manager);
*/
mysql> select de.dept_no, e.emp_no, s.salary
    -> from dept_emp de join employees e on de.emp_no = e.emp_no
    -> join salaries s on e.emp_no = s.emp_no
    -> where e.emp_no not in (select emp_no from dept_manager);
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001    |  10001 |  88958 |
+---------+--------+--------+
1 row in set (0.00 sec)

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

    129、子查询与连接查询

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

    /*
drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
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 `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 `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_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','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
    • 27
    • 28

    部门关系表：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 |
+--------+---------+------------+------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

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

    mysql> select * from dept_manager;
+---------+--------+------------+------------+
| dept_no | emp_no | from_date  | to_date    |
+---------+--------+------------+------------+
| d001    |  10002 | 1996-08-03 | 9999-01-01 |
+---------+--------+------------+------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

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

    mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 1996-08-03 | 9999-01-01 |
+--------+--------+------------+------------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    【问题】查询员工的的薪水比其 manager 当前薪水还高的相关信息，显示员工的 emp_no，对应的 manager 的manager_no，该员工当前的薪水 emp_salary，该员工对应的 manager 当前的薪水 manager_salary。查询结果如下：

    emp_nomanager_noemp_salarymanager_salary
    10001100028895872527

    解答：

    /*
select emp.emp_no, manager.emp_no, emp.emp_salary, manager.manager_salary
from (select de.emp_no, de.dept_no, s.salary emp_salary
     from dept_emp de join salaries s on de.emp_no = s.emp_no) emp join
     (select dm.dept_no, dm.emp_no, s.salary manager_salary 
      from dept_manager dm join salaries s on dm.emp_no = s.emp_no) manager
     on emp.dept_no = manager.dept_no
where emp.emp_salary > manager.manager_salary;
*/
mysql> select emp.emp_no, manager.emp_no, emp.emp_salary, manager.manager_salary
    -> from (select de.emp_no, de.dept_no, s.salary emp_salary
    ->      from dept_emp de join salaries s on de.emp_no = s.emp_no) emp join
    ->      (select dm.dept_no, dm.emp_no, s.salary manager_salary 
    ->       from dept_manager dm join salaries s on dm.emp_no = s.emp_no) manager
    ->      on emp.dept_no = manager.dept_no
    -> where emp.emp_salary > manager.manager_salary;
+--------+--------+------------+----------------+
| emp_no | emp_no | emp_salary | manager_salary |
+--------+--------+------------+----------------+
|  10001 |  10002 |      88958 |          72527 |
+--------+--------+------------+----------------+
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
    • 20
    • 21
    • 22

    130、连接查询与分组

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

    /*
drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  titles ;
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
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 titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','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
    • 27
    • 28

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

    mysql> select * from departments;
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d001    | Marketing |
| d002    | Finance   |
+---------+-----------+
2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    部门员工关系表：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

    职称表：titles，表中数据如下：

    mysql> select * from titles;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
+--------+-----------------+------------+------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    【问题】汇总各个部门当前员工的 title 类型的分配数目，查询结果包括：部门编号（dept_no），dept_name，部门下所有的员工的 title 以及该类型 title 对应的数目 count，结果按照 dept_no 升序排序，dept_no 一样的再按 title 升序排序。查询结果如下：

    dept_nodept_nametitlecount
    d001MarketingSenior Engineer1
    d001MarketingStaff1
    d002FinanceSenior Engineer1

    解答：

    /*
select d.dept_no, d.dept_name, t.title, count(*) count
from dept_emp de join departments d on de.dept_no = d.dept_no
join titles t on de.emp_no = t.emp_no
group by d.dept_no, d.dept_name, t.title
order by d.dept_no, title;
*/
mysql> select d.dept_no, d.dept_name, t.title, count(*) count
    -> from dept_emp de join departments d on de.dept_no = d.dept_no
    -> join titles t on de.emp_no = t.emp_no
    -> group by d.dept_no, d.dept_name, t.title
    -> order by d.dept_no, title;
+---------+-----------+-----------------+-------+
| dept_no | dept_name | title           | count |
+---------+-----------+-----------------+-------+
| d001    | Marketing | Senior Engineer |     1 |
| d001    | Marketing | Staff           |     1 |
| d002    | Finance   | Senior Engineer |     1 |
+---------+-----------+-----------------+-------+
3 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
  • 相关阅读:
    万字总结：分布式系统的 38 个知识点
    【Linux】内核同步
    【手撕AHB-APB Bridge】~ AHB地址总线的低两位为什么不用来表示地址呢？
    230921整合支付宝支付
    29.5.2 备份数据
    22、7大参数自定义线程池（核心线程数，最大核心线程数。。。拒绝策略（4种））
    使用TypeScript和jsdom库实现自动化数据抓取
    Linux-CPU之平均负载
    基于Android的校园社团管理
    16种最好的方法，你可以推动过去的拖延
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126104186