数据库系统原理与应用教程（073）—— MySQL 练习题：操作题 131-140（十七）：综合练习

131、子查询（1）

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

/*
drop table if exists  film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');
*/
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

电影信息表：film，表中数据如下：

mysql> select * from film;
+---------+------------------+------------------------------------------------------+
| film_id | title            | description                                          |
+---------+------------------+------------------------------------------------------+
|       1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must 
                               Battle a Teacher in The Canadian Rockies             |
|       2 | ACE GOLDFINGER   | A Astounding Epistle of a Database Administrator And 
                               a Explorer who must Find a Car in Ancient China     |
|       3 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who 
                               must Sink a Lumberjack in A Baloon Factory           |
+---------+------------------+------------------------------------------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

类别表：category，表中数据如下：

mysql> select * from category;
+-------------+-------------+---------------------+
| category_id | name        | last_update         |
+-------------+-------------+---------------------+
|           1 | Action      | 2006-02-14 20:46:27 |
|           2 | Animation   | 2006-02-14 20:46:27 |
|           3 | Children    | 2006-02-14 20:46:27 |
|           4 | Classics    | 2006-02-14 20:46:27 |
|           5 | Comedy      | 2006-02-14 20:46:27 |
|           6 | Documentary | 2006-02-14 20:46:27 |
|           7 | Drama       | 2006-02-14 20:46:27 |
|           8 | Family      | 2006-02-14 20:46:27 |
|           9 | Foreign     | 2006-02-14 20:46:27 |
|          10 | Games       | 2006-02-14 20:46:27 |
|          11 | Horror      | 2006-02-14 20:46:27 |
+-------------+-------------+---------------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

电影分类表：film_category，表中数据如下：

mysql> select * from film_category;
+---------+-------------+---------------------+
| film_id | category_id | last_update         |
+---------+-------------+---------------------+
|       1 |           6 | 2006-02-14 21:07:09 |
|       2 |          11 | 2006-02-14 21:07:09 |
+---------+-------------+---------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

【问题】查询没有分类的电影id 以及电影名称。

解答：

/*
select film_id, title from film where film_id not in
(select film_id from film_category);
*/
mysql> select film_id, title from film where film_id not in
    -> (select film_id from film_category);
+---------+------------------+
| film_id | title            |
+---------+------------------+
|       3 | ADAPTATION HOLES |
+---------+------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

132、子查询（2）

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

/*

drop table if exists   film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');
*/
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

电影信息表：film，表中数据如下：

mysql> select * from film;
+---------+------------------+------------------------------------------------------+
| film_id | title            | description                                          |
+---------+------------------+------------------------------------------------------+
|       1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must 
                               Battle a Teacher in The Canadian Rockies             |
|       2 | ACE GOLDFINGER   | A Astounding Epistle of a Database Administrator And 
                               a Explorer who must Find a Car in Ancient China     |
|       3 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who 
                               must Sink a Lumberjack in A Baloon Factory           |
+---------+------------------+------------------------------------------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

类别表：category，表中数据如下：

mysql> select * from category;
+-------------+-------------+---------------------+
| category_id | name        | last_update         |
+-------------+-------------+---------------------+
|           1 | Action      | 2006-02-14 20:46:27 |
|           2 | Animation   | 2006-02-14 20:46:27 |
|           3 | Children    | 2006-02-14 20:46:27 |
|           4 | Classics    | 2006-02-14 20:46:27 |
|           5 | Comedy      | 2006-02-14 20:46:27 |
|           6 | Documentary | 2006-02-14 20:46:27 |
+-------------+-------------+---------------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

电影分类表：film_category，表中数据如下：

mysql> select * from film_category;
+---------+-------------+---------------------+
| film_id | category_id | last_update         |
+---------+-------------+---------------------+
|       1 |           1 | 2006-02-14 21:07:09 |
|       2 |           1 | 2006-02-14 21:07:09 |
|       3 |           6 | 2006-02-14 21:07:09 |
+---------+-------------+---------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

【问题】查询属于 Action 分类的所有电影对应的 title、description。

解答：

/*
select title, description from film
where film_id in (select film_id from film_category where category_id in
                 (select category_id from category where name = 'Action')
);
*/
mysql> select title, description from film
    -> where film_id in (select film_id from film_category where category_id in
    ->                  (select category_id from category where name = 'Action')
    -> );
+------------------+--------------------------------------------------------------+
| title            | description                                                  |
+------------------+--------------------------------------------------------------+
| ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle 
                     a Teacher in The Canadian Rockies     |
| ACE GOLDFINGER   | A Astounding Epistle of a Database Administrator And a Explorer 
                     who must Find a Car in Ancient China |
+------------------+--------------------------------------------------------------+
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

133、字符型函数的使用

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

/*
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');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

【问题】将 employees 表的所有员工的 last_name 和 first_name 拼接起来作为 Name，中间以一个空格区分。查询结果如下：

Name
Facello Georgi
Simmel Bezalel
Bamford Parto
Koblick Chirstian
Maliniak Kyoichi
Preusig Anneke
Zielinski Tzvetan
Kalloufi Saniya
Peac Sumant
Piveteau Duangkaew
Sluis Mary

解答：

mysql> select concat(last_name,' ',first_name) name from employees;
+--------------------+
| name               |
+--------------------+
| Facello Georgi     |
| Simmel Bezalel     |
| Bamford Parto      |
| Koblick Chirstian  |
| Maliniak Kyoichi   |
| Preusig Anneke     |
| Zielinski Tzvetan  |
| Kalloufi Saniya    |
| Peac Sumant        |
| Piveteau Duangkaew |
| Sluis Mary         |
+--------------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

134、查询时强制使用索引

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

/*
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`));
create index idx_emp_no on salaries(emp_no);
INSERT INTO salaries VALUES(10005,78228,'1989-09-12','1990-09-12');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
*/
1
2
3
4
5
6
7
8
9
10
11
12

【问题】查询 emp_no 为10005 的记录，强制使用索引 idx_emp_no。

解答：

mysql> explain SELECT * FROM salaries FORCE INDEX (idx_emp_no) 
       WHERE emp_no='10005'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ref
possible_keys: idx_emp_no
          key: idx_emp_no
      key_len: 4
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

135、删除重复记录

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

/*
drop table if exists titles_test;
CREATE TABLE titles_test (
   id int(11) not null primary key,
   emp_no  int(11) NOT NULL,
   title  varchar(50) NOT NULL,
   from_date  date NOT NULL,
   to_date  date DEFAULT NULL);

insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
*/

mysql> select * from titles_test;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  10004 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  5 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  6 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  7 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
+----+--------+-----------------+------------+------------+
7 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

【问题】删除 emp_no 重复的记录，只保留最小的 id 对应的记录。查询结果如下：

+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  10004 | Senior Engineer | 1995-12-03 | 9999-01-01 |
+----+--------+-----------------+------------+------------+
1
2
3
4
5
6
7
8

解答：

/*
DELETE FROM titles_test WHERE id NOT IN 
(SELECT MIN(id) FROM titles_test GROUP BY emp_no);
*/
mysql> DELETE FROM titles_test WHERE id NOT IN 
    -> (SELECT MIN(id) FROM titles_test GROUP BY emp_no);
ERROR 1093 (HY000): You can't specify target table 'titles_test' for update in FROM clause'
-- 出错，mysql 的 delete from 语句的子查询中不能出现要删除的表名。

mysql> create table titles_test_bak select * FROM titles_test WHERE id IN 
    -> (SELECT MIN(id) FROM titles_test GROUP BY emp_no);
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> truncate table titles_test;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into titles_test select * from titles_test_bak;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from titles_test;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  10004 | Senior Engineer | 1995-12-03 | 9999-01-01 |
+----+--------+-----------------+------------+------------+
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
20
21
22
23
24
25
26
27
28
29
30
31
32

136、使用 replace into 命令更新数据

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

/*
drop table if exists titles_test;
CREATE TABLE titles_test (
   id int(11) not null primary key,
   emp_no  int(11) NOT NULL,
   title  varchar(50) NOT NULL,
   from_date  date NOT NULL,
   to_date  date DEFAULT NULL);

insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
*/
mysql> select * from titles_test;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  10004 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  5 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  6 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  7 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
+----+--------+-----------------+------------+------------+
7 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

【问题】将 id = 5 以及 emp_no = 10001 的行数据替换成 id = 5 以及 emp_no = 10005，其他数据保持不变。必须使用replace 实现，不能使用 update 。

解答：

/*
replace into titles_test 
select 5,10005,title,from_date,to_date 
from titles_test where id=5 and emp_no=10001;
*/
mysql> replace into titles_test 
    -> select 5,10005,title,from_date,to_date 
    -> from titles_test where id=5 and emp_no=10001;
Query OK, 2 rows affected (0.02 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> select * from titles_test;
+----+--------+-----------------+------------+------------+
| id | emp_no | title           | from_date  | to_date    |
+----+--------+-----------------+------------+------------+
|  1 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  2 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  3 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  4 |  10004 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  5 |  10005 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  6 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  7 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
+----+--------+-----------------+------------+------------+
7 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

137、字符型函数的使用

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

/*
drop table if exists strings;
CREATE TABLE strings(
   id int(5)  NOT NULL PRIMARY KEY,
   string  varchar(200) NOT NULL
 );
insert into strings values
(1, '10,A,B,郑州,河南'),
(2, 'A,B,C,D,新乡，河南'),
(3, '中国河南新乡A,11,B,C,D,E');
*/

mysql> select * from strings;
+----+--------------------------------+
| id | string                         |
+----+--------------------------------+
|  1 | 10,A,B,郑州,河南               |
|  2 | A,B,C,D,新乡，河南             |
|  3 | 中国河南新乡A,11,B,C,D,E       |
+----+--------------------------------+
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
21

【问题】统计每个字符串中逗号出现的次数 cnt。查询结果如下：

id	cnt
1	4
2	4
3	5

解答：

/*
select id, char_length(string)-char_length(replace(string,',','')) cnt
from strings;
*/
mysql> select id, char_length(string)-char_length(replace(string,',','')) cnt
    -> from strings;
+----+------+
| id | cnt  |
+----+------+
|  1 |    4 |
|  2 |    4 |
|  3 |    5 |
+----+------+
3 rows in set (0.05 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

138、子查询

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

/*
drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` float(11,3) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
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,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
*/

mysql> select * from salaries;
+--------+-----------+------------+------------+
| emp_no | salary    | from_date  | to_date    |
+--------+-----------+------------+------------+
|  10001 | 85097.000 | 2001-06-22 | 2002-06-22 |
|  10001 | 88958.000 | 2002-06-22 | 9999-01-01 |
|  10002 | 72527.000 | 2001-08-02 | 9999-01-01 |
|  10003 | 43699.000 | 2000-12-01 | 2001-12-01 |
|  10003 | 43311.000 | 2001-12-01 | 9999-01-01 |
|  10004 | 70698.000 | 2000-11-27 | 2001-11-27 |
|  10004 | 74057.000 | 2001-11-27 | 9999-01-01 |
+--------+-----------+------------+------------+
7 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

【问题】查询排除在职（to_date = ‘9999-01-01’）员工的最大、最小 salary 之后，其他的在职员工的平均工资 avg_salary。查询结果如下：

avg_salary
73292

解答：

/*
select avg(salary) avg_salary 
from salaries where to_date = '9999-01-01' and salary not in
(select max(salary) from salaries where to_date = '9999-01-01')
and salary not in
(select min(salary) from salaries where to_date = '9999-01-01');
*/
mysql> select avg(salary) avg_salary 
    -> from salaries where to_date = '9999-01-01' and salary not in
    -> (select max(salary) from salaries where to_date = '9999-01-01')
    -> and salary not in
    -> (select min(salary) from salaries where to_date = '9999-01-01');
+---------------+
| avg_salary    |
+---------------+
| 73292.0000000 |
+---------------+
1 row in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

139、exits 子查询

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

/*
drop table if exists employees;
drop table if exists dept_emp;
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 `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`));
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');
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,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10008,'d005','1998-03-11','2000-07-31');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d005','1996-11-24','2000-06-26');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','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
40

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 | d004    | 1995-12-03 | 9999-01-01 |
|  10004 | d004    | 1986-12-01 | 9999-01-01 |
|  10005 | d003    | 1989-09-12 | 9999-01-01 |
|  10006 | d002    | 1990-08-05 | 9999-01-01 |
|  10007 | d005    | 1989-02-10 | 9999-01-01 |
|  10008 | d005    | 1998-03-11 | 2000-07-31 |
|  10009 | d006    | 1985-02-18 | 9999-01-01 |
|  10010 | d005    | 1996-11-24 | 2000-06-26 |
|  10010 | d006    | 2000-06-26 | 9999-01-01 |
+--------+---------+------------+------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

【问题】使用含有关键字 exists 查找未分配具体部门的员工的所有信息。查询结果如下：

emp_no	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22

解答：

/*
select * from employees where not exists
(select * from dept_emp where emp_no = employees.emp_no);
*/
mysql> select * from employees where not exists
    -> (select * from dept_emp where emp_no = employees.emp_no);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10011 | 1953-11-07 | Mary       | Sluis     | F      | 1990-01-22 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12

140、case 函数的用法

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

/*
drop table if exists  `employees` ; 
drop table if exists  emp_bonus; 
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 emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

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 emp_bonus values
(10001, '2010-01-01',1),
(10002, '2010-10-01',2);

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 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');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

员工表：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 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

员工奖金表：emp_bonus，表中数据如下：

mysql> select * from emp_bonus;
+--------+---------------------+-------+
| emp_no | recevied            | btype |
+--------+---------------------+-------+
|  10001 | 2010-01-01 00:00:00 |     1 |
|  10002 | 2010-10-01 00:00:00 |     2 |
+--------+---------------------+-------+
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 |  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 |
|  10002 |  72527 | 1997-08-03 | 1998-08-03 |
|  10002 |  72527 | 1998-08-03 | 1999-08-03 |
|  10002 |  72527 | 1999-08-03 | 2000-08-02 |
|  10002 |  72527 | 2000-08-02 | 2001-08-02 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
+--------+--------+------------+------------+
23 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

【问题】emp_bonus 表中 btype 取值为 1，表示奖金为薪水 salary 的 10%，btype 为 2 表示奖金为薪水的 20%，其他类型奖金均为薪水的 30%。to_date = ‘9999-01-01’ 表示当前薪水。

查询 emp_no，first_name，last_name，奖金类型 btype、对应的当前薪水情况（salary）以及奖金金额（bonus）。bonus 结果保留一位小数，输出结果按 emp_no 升序排序。查询结果如下：

emp_no	first_name	last_name	btype	salary	bonus
10001	Georgi	Facello	1	88958	8895.8000
10002	Bezalel	Simmel	2	72527	14505.4000

解答：

/*
select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
       round((case btype when 1 then salary * 0.1 when 2 then salary * 0.2 else  salary * 0.3 end),1) bonus
from employees e join emp_bonus eb on e.emp_no = eb.emp_no
join salaries s on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
order by e.emp_no;
*/
mysql> select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
    ->        (case btype when 1 then salary * 0.1 when 2 then salary * 0.2 else  salary * 0.3 end) bonus
    -> from employees e join emp_bonus eb on e.emp_no = eb.emp_no
    -> join salaries s on e.emp_no = s.emp_no
    -> where s.to_date = '9999-01-01';
+--------+------------+-----------+-------+--------+---------+
| emp_no | first_name | last_name | btype | salary | bonus   |
+--------+------------+-----------+-------+--------+---------+
|  10001 | Georgi     | Facello   |     1 |  88958 |  8895.8 |
|  10002 | Bezalel    | Simmel    |     2 |  72527 | 14505.4 |
+--------+------------+-----------+-------+--------+---------+
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

相关阅读:
人人商城app禁用
 栈、队列、矩阵的总结
 开源进击未来：开放原子开源大赛苏州站获奖名单公布（4月17-18日）
Play Beyond：Sui让优秀的游戏变得更好
 Linux安装DMETL4
Spring Boot RestController接口如何输出到终端
 语文课内外杂志语文课内外杂志社语文课内外杂志社2022年第14期目录
 day06-前后端项目上传到gitee、后端多方式登录接口、发送短信功能、发送短信封装、短信验证码接口、短信登录接口
 使用html+css实现一个静态页面【传统文化茶带音乐6页】HTML学生个人网站作业设计
 计算机毕业设计node.js+vue+Element企业员工信息管理系统
原文地址：https://blog.csdn.net/weixin_44377973/article/details/126112267