数据库技术基础

信息技术工程系 软件工程硕士 陶冠华

Database

绪论 数据库相关概念、DBMS及其安装

（略）

第一讲 Windows 下 MariaDB 数据库管理系统的安装

第二讲 Linux下 MariaDB 数据库管理系统的安装

第三讲 Windows 下服务的管理和配置

1、图形界面管理

按 Windows徽标键+R 键，打开“运行”对话框，输入 services.msc后回车，在出现的服务管理窗口中进行操作。

2、命令行界面管理

按 Windows徽标键+R 键，打开“运行”对话框，输入 cmd后回车，可打开命令窗口。但有的命令需要管理员权限，因此需要以管理员身份打开 CMD。

（1）查看 MariaDB 服务

sc query mariadb / sc queryex mariadb 用于查看MariaDB服务器运行状态。

sc qc mariadb 用于查看MariaDB服务器配置

（2） 查看 MariaDB 服务网络监听端口

netstat -aon | findstr "mariadb服务的进程ID"

（3）启动和停止 MariaDB 服务

sc start mariadb用于启动 MariaDB 服务。

sc stop mariadb用于启动 MariaDB 服务。

（4）修改 MariaDB 的启动类型

sc config mariadb start= < auto | delayed-auto | demand | disabled >用于修改 MariaDB 服务的启动类型。

• auto: 自动，随操作系统启动而启动
• delayed-auto: 自动延迟，所有服务启动后若干时间（默认2分钟，可配置）再启动
• demand: 手动（需要时再启动）
• disabled: 禁用

3、MariaDB 服务的配置（含客户端工具配置）

MariaDB 的程序

（1）配置文件

用mysql --help 查看。

Default options are read from the following files in the given order:

C:\WINDOWS\my.ini

C:\WINDOWS\my.cnf

C:\my.ini

C:\my.cnf

C:\Program Files\MariaDB 10.7\my.ini

C:\Program Files\MariaDB 10.7\my.cnf

C:\Program Files\MariaDB 10.7\data\my.ini

C:\Program Files\MariaDB 10.7\data\my.cnf

配置文件的读取顺序，后面的相同配置会覆盖前面的。

（2）配置项

用命令名 --help查看可用配置项。

用 命令名 --print-defaults 查看当前默认配置。

（3）配置组

[mysqld]

# 配置服务器

[client]

# 配置所有客户端

[mysql]

# 配置 mysql

[mysqldump]

# 配置 mysqldump

...

第四讲 Linux 下 MariaDB 服务的管理与配置

（一）MariaDB服务的管理

1、查看 MariaDB 服务的状态和启动方式

sudo systemctl status mariadb

2、改变服务的启动方式

（1） 服务随操作系统启动而启动

sudo systemctl enable mariadb 

（2）手动启动

sudo systemctl disable mariadb

3、启动服务

sudo systemctl start mariadb

4、停止服务

sudo systemctl stop mariadb

5、重启服务

sudo systemctl restart mariadb

（二）MariaDB 服务的配置

1、配置文件的位置

用：

命令 --help | more 

进行查看。

2、服务器与客户端的配置

Linux 系统下的服务器和客户端配置与 Windows 下相同，不再赘述。

第五讲、 MariaDB 常用客户端程序的使用

（一）mysqladmin

mysqladmin 是用于管理服务器的一个客户端程序。此命令的格式是：

mysqladmin [各种选项] 命令 ...

常用选项：

# 服务器（默认：本机）
-h主机IP或域名  或者： --host=主机IP或域名  
# 端口（默认：3306）
-P端口号    或者： --port=端口号
# 登录用户（默认：当前操作系统用户）
-u用户名    或者： --user=用户名
# 密码（忽略选项不用密码，忽略密码会在回车后提问）
-p[密码]      或者： --password[=密码]
#### 以上选项通用于各客户端程序，是登录选项

常用命令：

ping: 测试服务器的连通性（服务器是否运行中）
version: 查看服务器版本号
status: 查看服务器状态
password: 修改用户密码
create: 创建数据库
drop: 删除数据库
...

举例：

查看实作视频。

（二）mysqlshow

mysqlshow 是用于显示 MariaDB数据库服务器中数据库对象结构的客户端程序。

用法：

mysqlshow [各种选项] [数据库名 [表名 [列名]]]

常用选项：

登录选项与 mysqladmin 一致。

举例：【查看视频】

（三）mysqldump

mysqldump 是用于备份数据库的客户端程序。

用法：

mysqldump [各种选项] 数据库名 [表格列表] // 备份中不含数据库信息，只有表信息
或     mysqldump [各种选项] --databases 数据库1 [数据库2 数据库3...]
或     mysqldump [各种选项] --all-databases
或     mysqldump [各种选项] --system=[SYSTEMOPTIONS]] 

举例：【查看视频】

（四）mysql

mysql 是用于与服务器交互的通用客户端程序。

mysql [各种选项] [数据库名]
或：
mysql [各种选项] < sql脚本（备份文件等） // 可用于恢复备份

举例：【查看视频】

第六讲 mysql 客户端的使用及获取帮助

（一）客户端操作命令

（二）获取服务器端帮助

第七讲 案例一：员工信息查询与维护

本案例是基于单表的一个案例，主要学习信息的查询、统计、计算和维护。

预备工作：将数据库导入服务器

mysql -uroot -p --default-character-set=utf8mb4 < d:\sql\direct_db1.sql

（一）表结构

1、 ER图和数据字典

(1) ER图（Entity-Relationship）

（2）数据字典

（二）查看表结构

1、 列出服务器中的数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| direct_db1         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)

2、进入数据库

MariaDB [(none)]> use direct_db1;
Database changed
MariaDB [direct_db1]>

3、列出数据库中的表格和视图

MariaDB [direct_db1]> show full tables;
+----------------------+------------+
| Tables_in_direct_db1 | Table_type |
+----------------------+------------+
| employees            | BASE TABLE |
+----------------------+------------+
1 row in set (0.001 sec)
MariaDB [direct_db1]>

4、 查看表结构

MariaDB [direct_db1]> desc employees;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name          | varchar(100)        | NO   |     | NULL    |                |
| gender        | char(1)             | NO   |     | NULL    |                |
| birth_date    | date                | NO   |     | NULL    |                |
| education     | varchar(50)         | NO   |     | NULL    |                |
| employed_date | date                | NO   |     | NULL    |                |
| department    | varchar(255)        | YES  |     | NULL    |                |
| salary        | decimal(10,2)       | NO   |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.007 sec)

第八讲 案例一：员工信息查询与维护（续一）

（二）查看数据

先按上一讲进入 direct_db1 数据库。

SQL 语句不区分大小写，大小写是为了清晰起见。

列名和表名用反引号括起来，目的是避免与系统关键字冲突，同时也是为了清晰起见。如果明确没有冲突，也可不用括起来。

1、查看所有列的所有数据

MariaDB [direct_db1]> SELECT * FROM `employees`;

2、查看某些列的所有数据

MariaDB [direct_db1]> SELECT `id`, `name`, `gender`, `birth_date` FROM `employees`;

3、查看某列或多列组合的唯一值

MariaDB [direct_db1]> SELECT DISTINT `department` FROM `employees`;
-- 多列组合
MariaDB [direct_db1]> SELECT DISTINT `department`,`gender` FROM `employees`;

4、给列重命名

只是给结果集的列名重新命名，而不是对表的列重新命名。

SQL 语句只有碰到结束符号（此时是英文分号 ; ）才会将语句送往服务器。因此，为清晰起见，一条语句可分为多行来写。

MariaDB [direct_db1]> SELECT
    -> `id` AS `编号`, `name` AS `姓名`, `gender` AS `性别`, `birth_date` AS `出生日期`
    -> FROM `employees`
    -> ;

5、 限制返回结果集的行数

限制前30行：

MariaDB [direct_db1]> SELECT * FROM `employees` LIMIT 30;

跳过100行后取30行：

MariaDB [direct_db1]> SELECT * FROM `employees` LIMIT 100, 30;
-- 或者
MariaDB [direct_db1]> SELECT * FROM `employees` LIMIT 30 OFFSET 100;

6、给结果集排序

SELECT MariaDB [direct_db1]> SELECT * FROM `employees`
    -> ORDER BY `salary` ASC;    -- 以月薪升序排序。默认升序排序（可省略）：ASC；降序：DESC

7、给汉字列排序

SELECT MariaDB [direct_db1]> SELECT * FROM `employees`
    -> ORDER BY `name`;  -- 升序排序，不会按汉语拼音排序。 

SELECT MariaDB [direct_db1]> SELECT * FROM `employees`
    -> ORDER BY CONVERT(`name` USING GBK);  -- 升序排序，按汉语拼音排序。 

8、多列排序

按部门中文拼音升序排序，部门相同再按月薪降序排序（超过两列均以此类推）。

MariaDB [direct_db1]> SELECT * FROM `employees`
    -> ORDER BY CONVERT(`department` USING GBK), `salary` DESC;

第九讲 案例一：员工信息查询与维护（续二）

（三）按条件查看数据

按条件查看数据，是指我们只需要那些符合一定条件的数据，而不是表里的所有数据。这些条件诸如：

“月薪大于某个数值的所有员工”、“月薪在某个范围的所有员工”、“年龄超过50的女性员工”等等。

我们用比较运算来确定对某列数据的要求（如果同时对多列数据有要求，需要用逻辑运算来组合这些要求），从而形成获取数据的条件。

参照: 附录二 MySQL/MariaDB 比较运算与逻辑运算

条件用 WHERE 条件表达式子句（SQL中完成一定功能的语句部分）来表达。

1、月薪查询（数值查询）

-- 月薪在5000元及以上的全体员工并按月薪从高到低进行结果排序
SELECT * FROM `employees` WHERE `salary`>=5000 ORDER BY `salary` DESC;
-- 月薪在5000到8000之间（不含5000和8000）的员工并按月薪从高到低进行结果排序
SELECT * FROM `employees` 
WHERE `salary`>5000 AND `salary`<8000 
ORDER BY `salary` DESC;
-- 月薪在5000到8000之间（含5000和8000）的员工并按月薪从高到低进行结果排序
SELECT * FROM `employees` 
WHERE `salary`>=5000 AND `salary`<=8000 
ORDER BY `salary` DESC;
-- 或者
SELECT * FROM `employees` 
WHERE `salary` BETWEEN 5000 AND 8000 
ORDER BY `salary` DESC;

2、姓名查询（字符串查询）

-- 查找姓名为“宋琴”的员工。
SELECT * FROM `employees` WHERE `name`='宋琴';
-- 查找所有“张”姓员工
SELECT * FROM `employees` WHERE `name` LIKE '张%';
-- 查找以“明”字结尾的所有员工
SELECT * FROM `employees` WHERE `name` LIKE '%明';
-- 查找名字包含“丹”字的全体员工
SELECT * FROM `employees` WHERE `name` LIKE '%丹%';

3、出生日期相关查询（日期查询）

(1) 函数：TIMESTAMPDIFF 介绍（Timestamp Difference）

用法：TIMESTAMPDIFF(度量单位，日期1，日期2)

功能：计算两个日期之间的间隔，度量单位由第一个参数决定。

度量单位包括：MICROSECOND（毫秒）、SECOND（秒）、MINUTE（分）、HOUR（小时）、DAY（天）、WEEK（周）、MONTH（月）、QUARTER（季度）、YEAR（年）。

如果日期1<日期2，结果为正，反之为负。

(2) 函数：NOW 和 CURDATE （Current Date）介绍

用法：NOW() -- 返回当前日期和时间

​ CURDATE() -- 返回当前日期

-- 找出所有于1980年及以后出生的所有员工并按出生日期从低到高排序
SELECT * FROM `employees` WHERE `birth_date`>='1980-01-01' ORDER BY `birth_date` ASC;
-- 日期用字符串表示法表示，推荐标准格式为：YYYY-MM-DD
-- 日期可以和数值一样做各种比较运算
-- 找出年龄在30岁到40岁（含30岁和40岁）之间的员工并按年龄从低到高排序
SELECT *, TIMESTAMPDIFF(YEAR,`birth_date`, CURDATE()) AS `age`
FROM `employees`
WHERE TIMESTAMPDIFF(YEAR,`birth_date`, CURDATE()) BETWEEN 30 AND 40  -- WHERE字句中不能用别名 age
ORDER BY `age` ASC; -- 别名只能用在 ORDER BY, GROUP BY 及 HAVING 子句中

4、涉及多字段的复杂查询

（1）找出月薪在5000（含）到6000（含）的女性员工，按月薪低到高排序。

SELECT * FROM `employees`
WHERE `salary` BETWEEN 5000 AND 6000 AND `gender`='女'
ORDER BY `salary` ASC;

（2）找出“财务室”和“人事处”的女性员工。

SELECT * FROM `employees`
WHERE `department` IN ('财务室','人事处') AND `gender`='女'
ORDER BY `department`;

（3）找出不在“财务室”、“人事处”和“营销部”的女性员工。

SELECT * FROM `employees`
WHERE `department` NOT IN ('财务室','人事处','营销部') AND `gender`='女'
ORDER BY `department`;

（3）找出月薪低于3000的员工或者月薪在1000以下的且年龄在50岁及以上的女性员工。

SELECT * FROM `employees`
WHERE (`salary`<3000) OR (`salary`<10000 AND TIMESTAMPDIFF(YEAR,`birth_date`, CURDATE())>=50
       AND `gender`='女')
ORDER BY `salary` ASC;

（4）找出月薪低于3000或者月薪高于5000，并且性别为女性或者年龄在45岁以上的男性员工。

SELECT *, TIMESTAMPDIFF(YEAR,`birth_date`,CURDATE()) AS `age` FROM `employees`
WHERE (`salary`<3000 OR `salary`>5000) AND 
      (`gender`='女' OR TIMESTAMPDIFF(YEAR,`birth_date`,CURDATE())>45 AND `gender`='男')
ORDER BY `gender`, `age`, `salary`;

第十讲 案例一：员工信息查询与维护（续三）

（四）统计和计算

1、统计记录数

-- 统计表中的记录总数
SELECT COUNT(*) FROM `employees`;
-- 统计表中部门列中不为 NULL 的记录数
SELECT COUNT(`department`) FROM `employees`;
-- 统计表中部门列不为 NULL 的具有不同的值的记录数
SELECT COUNT(DISTINCT `department`) FROM `employees`;
-- 按条件统计表中记录数
    SELECT COUNT(*) FROM `employees` WHERE `salary`>10000;

2、计算表中月薪总额、平均月薪（保留两们小数）

-- sum: 总和
-- avg: average, 平均值
-- round: （为方便计算和表达）取数字的近似值。默认取整，或保留第二个参数指定的小数位数。
SELECT SUM(`salary`) AS `月薪总额`, ROUND(AVG(`salary`),2) AS `平均月薪` FROM `employees`;

3、取出月薪的最大值和最小值

SELECT MAX(`salary`) AS `最高月薪`, MIN(`salary`) AS `最低月薪` FROM `employees`;

4、分组统计

-- 统计出各部门的员工人数（单列分组统计）
SELECT `department` AS `部门`, COUNT(*) AS `员工数`
FROM `employees`
GROUP BY `department`;
-- 统计出各部门的男女员工人数（多列分组统计）
SELECT `department` AS `部门`, `gender` AS `性别`, COUNT(*) AS `员工数`
FROM `employees`
GROUP BY `department`,`gender`;
-- 统计出各部门年龄在40岁以上的员工人数（带条件的统计）
SELECT `department` AS `部门`, MIN(TIMESTAMPDIFF(YEAR,`birth_date`,CURDATE())) AS `起始年龄`,
       COUNT(*) AS `员工数`
FROM `employees`
WHERE TIMESTAMPDIFF(YEAR,`birth_date`,CURDATE())>40
GROUP BY `department`;
-- 显示人数在30人及以上的部门和人数（对统计结果做条件过滤）
SELECT `department` AS `部门`, COUNT(*) AS `员工数`
FROM `employees`
GROUP BY `department`
HAVING `员工数`>=30;
-- 按出生年代分组统计最高月薪、最低月薪和平均月薪
SELECT  YEAR(`birth_date`) AS `YEAR`, MAX(`salary`), MIN(`salary`), ROUND(AVG(`salary`),2)
FROM `employees`
GROUP BY `YEAR`;
-- 以每10岁为一个年龄段，分组统计各年龄段的最高月薪、最低月薪和平均月薪
-- 比如年龄为：20+（20~29），30+，40+ ...
-- CONCAT(arg1,arg2,arg3...) 将传入的多个数据（自动转换为字符串）作字符串连接
-- CONCAT(20,' years ', 'old') => '20 years old'
-- 如果 arg 是整数 a 和 整数 a+1 之间的一个小数（小数部分不为零）：
-- FLOOR(arg) 得到 a。FLOOR(2.999) => 2
-- CEIL(arg) 得到 a+1。CEIL(6.0001) => 7
-- 20~29 => 20  
-- floor(20/10) => floor(2.0) => 2 => 2 * 10 => 20
-- floor(29/10) => floor(2.9) => 2 => 2 * 10 => 20
SELECT 
    CONCAT(FLOOR(TIMESTAMPDIFF(YEAR,`birth_date`,CURDATE())/10)*10, '+') AS `age`,
    MAX(`salary`), MIN(`salary`), ROUND(AVG(`salary`),2)
FROM `employees`
GROUP BY `age`;

第十一讲 案例一：员工信息查询与维护（续四）

（五）子查询

子查询，是“寄生”于一条查询语句中的完整的查询语句，其结果用于主查询语句中。子查询需要用圆括号括起来。查询可以多层嵌套，内层查询是外层查询的子查询。

1、子查询应用于结果列

例：显示员工的所有信息，并包括其所在部门的最高工资和最低工资。

SELECT *,
    (SELECT MAX(`b`.`salary`) FROM `employees` `b` WHERE `department`=`a`.`department`) AS `部门最高工资`,
     (SELECT MIN(`b`.`salary`) FROM `employees` `b` WHERE `department`=`a`.`department`) AS `部门最低工资`
FROM `employees` `a`
LIMIT 10;

由于子查询与主查询记录相关（判断依据：子查询不能独立执行），对每一行数据，都要执行两次子查询，查询效率低，不适合需要返回大量行的结果的查询。

2、子查询应用到 WHERE 子句

（1）例：显示工资高于部门平均工资（含）的员工信息。

SELECT *,
     ROUND((SELECT AVG(`b`.`salary`) FROM `employees` `b` WHERE `department`=`a`.`department`),2) AS `部门平均工资`
FROM `employees` `a`
WHERE `a`.`salary`>=(SELECT AVG(`b`.`salary`) FROM `employees` `b` WHERE `department`=`a`.`department`);

同样，由于子查询与主查询记录相关（判断依据：子查询不能独立执行），对每一行数据，都要执行两次子查询（一次在结果列，一次在 WHERE 子句中），查询效率低，不适合需要返回大量行的结果的查询。

（2）例：显示姓名包含'明'字的所有员工信息。

SELECT * FROM `employees` 
WHERE id in (SELECT `id` FROM `employees` WHERE `name` LIKE '%明%' );
-- 上例仅用于说明与主查询无关的子查询的应用，其实相当于：
SELECT * FROM `employees` WHERE `name` LIKE '%明%';

此例中的子查询与主查询记录不相关（子查询可独立执行），因此不会对查询的总体效率产生显著影响。

第十二讲 案例一：员工信息查询与维护（续五）

（六）数据维护

数据维护是指数据的插入、修改和删除等。

1、插入数据

插入数据的语法：

INSERT INTO 表名 [(列名1，列名2，...列名n)]
    VALUES（值1，值2，...值n）[,(值1，值2，...值n),...];
-- 或者从另一表获取记录并插入到当前表(对应列应当具有相同数据类型和宽度)
INSERT INTO 表名 [(列名1，列名2，...列名n)]
    SELECT 列名_1，列名_2, ... 列名_3 
    FROM 另一表名
    [WHERE] ...;

如果要将数据插入到所有列，可省略列名列表。
插入的数据可以不需要包括所有列，但必须包含那些需要数据的列。如何确定必须需要数据的列？可用以下原则确定：

• 不允许为空（NULL）的列
• 且缺省值为 NULL 的（或无缺省值定义的）列
• 且无自动增长（AUTO_INCREMENT）特性的列

MariaDB [direct_db1]> desc employees;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name          | varchar(100)        | NO   |     | NULL    |                |
| gender        | char(1)             | NO   |     | NULL    |                |
| birth_date    | date                | NO   |     | NULL    |                |
| education     | varchar(50)         | NO   |     | NULL    |                |
| employed_date | date                | NO   |     | NULL    |                |
| department    | varchar(255)        | YES  |     | NULL    |                |
| salary        | decimal(10,2)       | NO   |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.007 sec)

不符合以上条件的列只有 id 和 department。
注意： 对具有 AUTO_INCREMENT 特性的列，可插入 NULL 值，由其自行决定自己的取值。

以下例子添加两条数据到 employees 表。

INSERT INTO `employees`
VALUES
(NULL,'张琦','男','1990-02-03','本科','2021-10-12',NULL,3500.00),
(NULL,'王瑶琚','女','1996-11-15','本科','2021-10-12',NULL,3000.00);

2、修改数据

修改数据的语法是：

UPDATE 表名 SET 列名1=值1 [,列名2=值2 [,...]]
[WHERE 条件] 

以下语句将所有部门未知的员工添加到“产品部”。

UPDATE `employees` SET `department`='产品部' WHERE department IS NULL;

3、删除数据

删除数据的语法是：

DELETE FROM 表名 [WHERE 条件]

注意： 不带条件的删除将删除表中所有数据！
以下语句将删除 employees 表中编号为235的员工记录。

DELETE FROM `employees` WHERE id=235;

第十三讲 案例二：部门-员工信息查询与维护

（一）准备工作

1、导入数据库

将案例二的备份文件导入数据库服务器

mysql -uroot -p --default-character-set=utf8 < direct_db2.sql

2、查看表结构

用 mysql 客户端登录数据库服务器，进入 direct_db2 数据库。

mysql -uroot -p direct_db2

然后用以下命令查看数据库中的表。

show tables;

针对每张表，用：desc 表名; 和 show create table 表名; 查看表的定义。

（二）城市-宾客关联表讲解

表的关联：一张表需要参照其它表才能获得完整信息，则这两张表就是关联的。我们先来看以下两张表的记录。

宾客表（guests）：

MariaDB [direct_db2]> select * from guests;
+----+--------------------+--------+--------------+
| id | sid                | name   | from_city_id |
+----+--------------------+--------+--------------+
|  1 | 530199198703210028 | 王小妮 |            4 |
|  2 | 510298199012120087 | 张燕   |            4 |
|  3 | 109877199910100136 | 李志强 |            3 |
|  4 | 230987198901030358 | 周超   |            1 |
|  5 | 310987199703280824 | 吴倩倩 |            1 |
|  6 | 651263199901300871 | 郑志   |         NULL |
|  7 | 538922200110220116 | 蒋嘉乐 |            5 |
+----+--------------------+--------+--------------+
7 rows in set (0.001 sec)
MariaDB [direct_db2]>

在以上的宾客表中，最后一列 from_city_id 从字面上看是“来自城市编号”，应该用于表明宾客来自于哪个城市，但数据是城市的编号，我们无法从编号得知宾客到底来自哪个城市。好在我们有一张关于“城市”的表。

城市表（cities）：

MariaDB [direct_db2]> select * from cities;
+----+------+
| id | city |
+----+------+
|  1 | 上海 |
|  2 | 北京 |
|  3 | 广州 |
|  4 | 成都 |
|  5 | 昆明 |
+----+------+
5 rows in set (0.024 sec)
MariaDB [direct_db2]>

结合两张表，我们显然可知道各个宾客来自哪个城市了，除了第6号宾客“郑志”。如：1号宾客来自于4号城市“成都”，3号宾客来自于3号城市“广州”等。
为了证实我们的结论——即宾客表(guests)的from_city_id参照了城市表(cities)的id，我们来查看两张表的详细定义。

MariaDB [direct_db2]> show create table guests\G
*************************** 1. row ***************************
       Table: guests
Create Table: CREATE TABLE `guests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sid` char(18) NOT NULL,
  `name` varchar(50) NOT NULL,
  `from_city_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sid` (`sid`),
  KEY `from_city_id` (`from_city_id`),
  CONSTRAINT `guests_ibfk_1` FOREIGN KEY (`from_city_id`) REFERENCES `cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.000 sec)
MariaDB [direct_db2]>

这是表“guests”的创建信息（竖排）。“Table” 列说明了表的名称 “guests”，“Create Table” 列的内容是创建 “guests” 表的 SQL 语句。我们把创建 “guests” 表的 SQL 语句抽取出来：

CREATE TABLE `guests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, -- 大整型无符号 不为空 自动增长
  `sid` char(18) NOT NULL, -- 定长字符串（18） 不为空
  `name` varchar(50) NOT NULL, -- 变长字符串 不为空
  `from_city_id` bigint(20) unsigned DEFAULT NULL, -- 大整型无符号 缺省值为null
  PRIMARY KEY (`id`), -- 主键索引
  UNIQUE KEY `sid` (`sid`), -- 唯一索引
  KEY `from_city_id` (`from_city_id`),  -- 普通索引
  CONSTRAINT `guests_ibfk_1` FOREIGN KEY (`from_city_id`) REFERENCES `cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE -- 外键约束
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

创建表的语法参见MariaDB说明文档。
创建表的语句可将主干抽取如下：

CREATE TABLE `guests`(逗号分隔的列定义，索引定义，约束定义...) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

其中的列定义：

列名 数据类型和规模 [可否为空] [缺省值] [自动增长] [约束]...,

索引定义：

索引类型 [索引名] （索引列1，索引列2...）

约束定义：

CONSTRAINT [约束名] CHECK (表达式)

我们来看看外键约束定义：

CONSTRAINT `guests_ibfk_1` FOREIGN KEY (`from_city_id`) REFERENCES `cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE -- 外键约束
-- 约束 `guests_ibfk_1` 外键 (`from_city_id`) 参照 `cities` (`id`) 在删除（参照的主表记录）时设置空值（SET NULL）在更新（参照的主表记录的id）时级联更新（CASCADE）

其中提到了guests表的from_city_id是外键，参照了cities表的id（主键）。并且：

• 当被参照的主表cities中的记录被删除时，将外键设置成空值（因为失去了原有的参照记录）；
• 当被参照的主表cities中的记录的主键id被修改时，将外键级联修改到新的id值。

以下是两表对应的实体关系图：

关系解读：
一个城市可有多名来宾，一名宾客来自一个城市。城市与宾客的关系是：一对多的关系。

（三）关联表数据的获取

宾客表（guests）：

MariaDB [direct_db2]> select * from guests;
+----+--------------------+--------+--------------+
| id | sid                | name   | from_city_id |
+----+--------------------+--------+--------------+
|  1 | 530199198703210028 | 王小妮 |            4 |
|  2 | 510298199012120087 | 张燕   |            4 |
|  3 | 109877199910100136 | 李志强 |            3 |
|  4 | 230987198901030358 | 周超   |            1 |
|  5 | 310987199703280824 | 吴倩倩 |            1 |
|  6 | 651263199901300871 | 郑志   |         NULL |
|  7 | 538922200110220116 | 蒋嘉乐 |            5 |
+----+--------------------+--------+--------------+
7 rows in set (0.001 sec)
MariaDB [direct_db2]>

城市表（cities）：

MariaDB [direct_db2]> select * from cities;
+----+------+
| id | city |
+----+------+
|  1 | 上海 |
|  2 | 北京 |
|  3 | 广州 |
|  4 | 成都 |
|  5 | 昆明 |
+----+------+
5 rows in set (0.024 sec)
MariaDB [direct_db2]>

仔细观察两张表的记录，会发现：

• 宾客表 guests 中有的记录的外键值是 NULL，即：我们不知道其来自哪里；如表中第 6 号宾客。
• 城市表 cities 中有的城市主键 id 值并未在宾客表中出现，即：没有宾客来自此城市。如表中第 2 号城市。

用图示表示如下：

下面，我们分四种情况来获取关联的记录：

• 内联查询：查交集部分记录（图中两圆的交集部分）
• 强调宾客表的所有记录：查落在蓝色圆圈内的记录（含交集部分）
• 强调城市表的所有记录：查落在淡黄色圆圈内的记录（含交集部分）
• 强调两表的所有记录：查两圆内的所有记录（含交集部分）

1、内联查询：查交集部分记录（图中两圆的交集部分）

结果预期：结果集应仅包含所有在交集内出现的宾客和城市记录（外部的宾客：郑治 被排除，外部的城市：北京 被排除）。

SELECT `guests`.*, `cities`.*              -- 列名前加表名限定
FROM `guests` INNER JOIN `cities`          -- 两表内联，次序不论
ON `guests`.`from_city_id`=`cities`.`id`   -- 联接条件 外键=主键
;
+----+--------------------+--------+--------------+----+------+
| id | sid                | name   | from_city_id | id | city |
+----+--------------------+--------+--------------+----+------+
|  1 | 530199198703210028 | 王小妮 |            4 |  4 | 成都 |
|  2 | 510298199012120087 | 张燕   |            4 |  4 | 成都 |
|  3 | 109877199910100136 | 李志强 |            3 |  3 | 广州 |
|  4 | 230987198901030358 | 周超   |            1 |  1 | 上海 |
|  5 | 310987199703280824 | 吴倩倩 |            1 |  1 | 上海 |
|  7 | 538922200110220116 | 蒋嘉乐 |            5 |  5 | 昆明 |
+----+--------------------+--------+--------------+----+------+
6 rows in set (0.002 sec)

要点：

• 因为涉及两张表，列名要用 `表名`.`列名` 的形式加以区分（事实上，如果列名只在一张表中出现，可以不加表名进行限制，但加上可增加可读性）；
• FROM 子句中要将两张表用 INNER JOIN 联接，次序不论；
• ON 子句说明联接条件，后面跟 外键=主键（或主键=外键）。

如果要在联接查询的基础上过滤结果集，可在基本内联查询的后面，用条件对结果集进行过滤并进行排序、限定记录范围和记录数等。
如：

SELECT `guests`.*, `cities`.*
FROM `guests` INNER JOIN `cities`          -- 两表内联
ON `guests`.`from_city_id`=`cities`.`id`   -- 联接条件 外键=主键
WHERE `guests`.`sid` like '53%'
ORDER BY CONVERT(`guests`.`name` USING GBK) ASC
;
+----+--------------------+--------+--------------+----+------+
| id | sid                | name   | from_city_id | id | city |
+----+--------------------+--------+--------------+----+------+
|  7 | 538922200110220116 | 蒋嘉乐 |            5 |  5 | 昆明 |
|  1 | 530199198703210028 | 王小妮 |            4 |  4 | 成都 |
+----+--------------------+--------+--------------+----+------+
2 rows in set (0.001 sec)

2、强调宾客表的所有记录：查落在蓝色圆圈内的记录（含交集部分）

结果预期：结果集内应当包含所有的宾客记录和在交集内出现的城市记录（外部的北京被排除）。
以下采用简洁写法：表用短别名表示，去除反引号（因为确信没有表名或列名与系统关键字冲突）。

SELECT g.*, c.*
FROM guests g  LEFT OUTER JOIN cities c -- 或者：cities c RIGHT OUTER JOIN guests g
ON g.from_city_id=c.id
;
+----+--------------------+--------+--------------+------+------+
| id | sid                | name   | from_city_id | id   | city |
+----+--------------------+--------+--------------+------+------+
|  1 | 530199198703210028 | 王小妮 |            4 |    4 | 成都 |
|  2 | 510298199012120087 | 张燕   |            4 |    4 | 成都 |
|  3 | 109877199910100136 | 李志强 |            3 |    3 | 广州 |
|  4 | 230987198901030358 | 周超   |            1 |    1 | 上海 |
|  5 | 310987199703280824 | 吴倩倩 |            1 |    1 | 上海 |
|  6 | 651263199901300871 | 郑志   |         NULL | NULL | NULL |
|  7 | 538922200110220116 | 蒋嘉乐 |            5 |    5 | 昆明 |
+----+--------------------+--------+--------------+------+------+
7 rows in set (0.001 sec)

要点：

• 因为涉及两张表，列名要用 `表名`.`列名` 的形式加以区分（事实上，如果列名只在一张表中出现，可以不加表名进行限制，但加上可增加可读性）；
• FROM 子句中要将两张表用 LEFT OUTER JOIN 或 RIGHT OUTER JOIN 联接，次序不论，但要用 LEFT OUT JOIN 或 RIGHT OUTER JOIN 强调左边或右边表的所有记录；
• ON 子句说明联接条件，后面跟 外键=主键（或主键=外键）。

3、强调城市表的所有记录：查落在淡黄色圆圈内的记录（含交集部分）

结果预期：结果集内应当包含所有的城市记录和在交集内出现的宾客记录（外部的郑治被排除）。
以下采用简洁写法：表用短别名表示，去除反引号（因为确信没有表名或列名与系统关键字冲突）。

SELECT g.*, c.*
FROM guests g  RIGHT OUTER JOIN cities c -- 或者：cities c LEFT OUTER JOIN guests g
ON g.from_city_id=c.id
;
+------+--------------------+--------+--------------+----+------+
| id   | sid                | name   | from_city_id | id | city |
+------+--------------------+--------+--------------+----+------+
|    4 | 230987198901030358 | 周超   |            1 |  1 | 上海 |
|    5 | 310987199703280824 | 吴倩倩 |            1 |  1 | 上海 |
| NULL | NULL               | NULL   |         NULL |  2 | 北京 |
|    3 | 109877199910100136 | 李志强 |            3 |  3 | 广州 |
|    1 | 530199198703210028 | 王小妮 |            4 |  4 | 成都 |
|    2 | 510298199012120087 | 张燕   |            4 |  4 | 成都 |
|    7 | 538922200110220116 | 蒋嘉乐 |            5 |  5 | 昆明 |
+------+--------------------+--------+--------------+----+------+
7 rows in set (0.000 sec)

要点：

• 因为涉及两张表，列名要用 `表名`.`列名` 的形式加以区分（事实上，如果列名只在一张表中出现，可以不加表名进行限制，但加上可增加可读性）；
• FROM 子句中要将两张表用 LEFT OUTER JOIN 或 RIGHT OUTER JOIN 联接，次序不论，但要用 LEFT OUT JOIN 或 RIGHT OUTER JOIN 强调左边或右边表的所有记录；
• ON 子句说明联接条件，后面跟 外键=主键（或主键=外键）。

4、强调两表的所有记录：查两圆内的所有记录（含交集部分）

结果预期：所有的宾客记录和城市记录均出现。

SELECT g.*, c.*
FROM guests g  LEFT OUTER JOIN cities c
ON g.from_city_id=c.id
UNION
SELECT g.*, c.*
FROM guests g  RIGHT OUTER JOIN cities c
ON g.from_city_id=c.id
;
+------+--------------------+--------+--------------+------+------+
| id   | sid                | name   | from_city_id | id   | city |
+------+--------------------+--------+--------------+------+------+
|    1 | 530199198703210028 | 王小妮 |            4 |    4 | 成都 |
|    2 | 510298199012120087 | 张燕   |            4 |    4 | 成都 |
|    3 | 109877199910100136 | 李志强 |            3 |    3 | 广州 |
|    4 | 230987198901030358 | 周超   |            1 |    1 | 上海 |
|    5 | 310987199703280824 | 吴倩倩 |            1 |    1 | 上海 |
|    6 | 651263199901300871 | 郑志   |         NULL | NULL | NULL |
|    7 | 538922200110220116 | 蒋嘉乐 |            5 |    5 | 昆明 |
| NULL | NULL               | NULL   |         NULL |    2 | 北京 |
+------+--------------------+--------+--------------+------+------+
8 rows in set (0.003 sec)
MariaDB [direct_db2]>

要点：

• 对两表分别强调一次进行外联查询，将两次查询的结果集用 UNION 联合起来。

第十四讲 案例二：部门-员工信息查询与维护

（四）部门员工关联表的信息获取练习

1、员工表与部门表的表间关系

首先我们来看看员工表（employees）的结构。

MariaDB [direct_db2]> desc employees;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name          | varchar(100)        | NO   |     | NULL    |                |
| gender        | char(1)             | NO   |     | NULL    |                |
| birth_date    | date                | NO   |     | NULL    |                |
| education     | varchar(50)         | NO   |     | NULL    |                |
| employed_date | date                | NO   |     | NULL    |                |
| salary        | decimal(10,2)       | NO   |     | NULL    |                |
| department_id | bigint(20) unsigned | YES  | MUL | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.013 sec)

以下是部门表（departments）的结构。

MariaDB [direct_db2]> desc departments;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(100)        | NO   | UNI | NULL    |                |
| location  | varchar(100)        | NO   |     | NULL    |                |
| telephone | varchar(50)         | NO   |     |         |                |
| email     | varchar(100)        | NO   |     |         |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.007 sec)

以下是员工表（employees）的详细定义。

MariaDB [direct_db2]> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gender` char(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `birth_date` date NOT NULL,
  `education` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `employed_date` date NOT NULL,
  `salary` decimal(10,2) NOT NULL,
  `department_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department_id` (`department_id`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=234 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.006 sec)

第15行说明了员工表（employees）的外键 department_id 参照了部门表（departments）的主键 id。

2、查询举例

（1）获取那些尚未归到某部门的员工
a）连接查询的确定
员工尚未归到某个部门，意味着其外键 department_id 是空值（null）。因为不需要部门信息，也就不需要连接查询。
b）查询条件
员工的部门编号为空值（null）。
c）其它需求
无。

SELECT * FROM `employees` WHERE `department_id` IS NULL;
+-----+--------+--------+------------+-----------+---------------+---------+---------------+
| id  | name   | gender | birth_date | education | employed_date | salary  | department_id |
+-----+--------+--------+------------+-----------+---------------+---------+---------------+
| 113 | 顾桂花 | 女     | 1981-12-23 | 硕士      | 2021-01-01    | 7284.00 |          NULL |
| 191 | 张丽   | 女     | 1981-12-10 | 硕士      | 2021-01-26    | 9242.00 |          NULL |
+-----+--------+--------+------------+-----------+---------------+---------+---------------+
2 rows in set (0.001 sec)

（2）获取那些尚未有员工的部门
a）连接查询的确定
尚未有员工，不需要员工信息，因此不需要连接查询。
b）查询条件
部门编号与当前部门编号相同的员工人数为0，需要在 WHERE 子句中判断（用子查询）。
c）其它需求
无。

SELECT * FROM `departments` `d`
WHERE (SELECT COUNT(*) FROM `employees` `e` WHERE`e`.`department_id`=`d`.`id`)=0;
+----+--------+----------+---------------+----------------------+
| id | name   | location | telephone     | email                |
+----+--------+----------+---------------+----------------------+
|  3 | 售后部 | 1511     | 0809-29871088 | service@starcomp.com |
|  7 | 公关部 | 1508     | 0809-29871098 | pubrela@starcomp.com |
+----+--------+----------+---------------+----------------------+
2 rows in set (0.001 sec)

（3）获取所有员工的 id, name, gender, birth_date, education, department（部门名称）
a）连接查询的确定
因为需要员工信息和部门信息，需要两表连接查询。又因为需要所有员工信息，因此应当采用偏向员工表的外部连接。
b）查询条件
无。
c）其它需求
为便于查看记录，结果集将按部门名称升序、员工编号升序的顺序进行排序。

SELECT `e`.`id`, `e`.`name`, `e`.`gender`, `e`.`birth_date`, `e`.`education`, `d`.`name` AS `department`
FROM `employees` `e` LEFT OUTER JOIN `departments` `d` -- 员工表在左，因此 LEFT OUTER JOIN
ON `e`.`department_id`=`d`.`id`
ORDER BY CONVERT(`department` USING GBK), `e`.`id`;
+-----+--------+--------+------------+-----------+------------+
| id  | name   | gender | birth_date | education | department |
+-----+--------+--------+------------+-----------+------------+
| 113 | 顾桂花 | 女     | 1981-12-23 | 硕士      | NULL       |
| 191 | 张丽   | 女     | 1981-12-10 | 硕士      | NULL       |
|   4 | 景华   | 男     | 1978-01-06 | 硕士      | 财务室     |
|  35 | 银馨予 | 女     | 1986-12-05 | 本科      | 财务室     |
|  76 | 项志强 | 男     | 1989-05-20 | 硕士      | 财务室     |
|  77 | 司楠   | 男     | 1990-06-05 | 博士      | 财务室     |
...
| 228 | 窦伦   | 男     | 1975-06-07 | 硕士      | 营销部     |
| 229 | 赖桂花 | 女     | 1985-03-22 | 本科      | 营销部     |
| 230 | 植淑珍 | 女     | 1983-07-22 | 本科      | 营销部     |
| 231 | 常雪   | 女     | 1991-05-31 | 本科      | 营销部     |
| 232 | 苏兰英 | 女     | 1982-08-20 | 本科      | 营销部     |
+-----+--------+--------+------------+-----------+------------+
233 rows in set (0.001 sec)

（4）获取所有的部门及其员工信息（id, name, gender, birth_date, education）
a）连接查询的确定
因为需要来自两表的信息，需要连接查询。又因为需要所有部门，应当采用偏向部门表的外部连接。
b）查询条件
无。
c）其它需求
为便于查看结果，结果集将按部门编号升序排序。

SELECT `d`.`id`, `d`.`name`, `e`.`id` AS `eid`, `e`.`name` AS `ename`, `e`.`gender`, `e`.`birth_date`, `e`.`education`
FROM `departments` `d` LEFT OUTER JOIN `employees` `e` -- 部门表在左，因此 LEFT OUTER JOIN
ON `d`.`id`=`e`.`department_id`
ORDER BY `d`.`id`;
+----+--------+------+--------+--------+------------+-----------+
| id | name   | eid  | ename  | gender | birth_date | education |
+----+--------+------+--------+--------+------------+-----------+
|  1 | 设计部 |    1 | 萧正诚 | 男     | 1990-03-07 | 本科      |
|  1 | 设计部 |    6 | 陆桂香 | 女     | 1988-02-14 | 本科      |
|  1 | 设计部 |   11 | 刘丹丹 | 女     | 1977-04-04 | 本科      |
...
|  2 | 人事处 |  227 | 明洪   | 男     | 1977-07-26 | 本科      |
|  3 | 售后部 | NULL | NULL   | NULL   | NULL       | NULL      |
...
|  6 | 产品部 |  212 | 唐雪梅 | 女     | 1983-12-05 | 硕士      |
|  6 | 产品部 |  213 | 匡智敏 | 男     | 1974-09-03 | 硕士      |
|  6 | 产品部 |  224 | 金爱华 | 女     | 1976-08-07 | 本科      |
|  6 | 产品部 |  225 | 武松   | 男     | 1984-02-29 | 硕士      |
|  7 | 公关部 | NULL | NULL   | NULL   | NULL       | NULL      |
+----+--------+------+--------+--------+------------+-----------+
233 rows in set (0.001 sec)

（5）列出部门和员工，包括无员工的部门和无部门的员工
部门信息包括：id, name
员工信息包括：id, name, gender, birth_date, education
a）连接查询的确定
需要所有部门和所有员工的对应记录，因此应当采用全连接（实现：对员工表和部门表分别偏向一次进行外连接，将结果联合起来）。
b）查询条件
无。
c）其它需求
无。

SELECT `d`.`id`,`d`.`name`, `e`.`id` AS `eid`, `e`.`name` AS `ename`, `e`.`gender`, `e`.`birth_date`, `e`.`education`
FROM `departments` `d` LEFT OUTER JOIN `employees` `e`
ON `d`.`id`=`e`.`department_id`
UNION
SELECT `d`.`id`,`d`.`name`, `e`.`id` AS `eid`, `e`.`name` AS `ename`, `e`.`gender`, `e`.`birth_date`, `e`.`education`
FROM `departments` `d` RIGHT OUTER JOIN `employees` `e`
ON `d`.`id`=`e`.`department_id`
;
+------+--------+------+--------+--------+------------+-----------+
| id   | name   | eid  | ename  | gender | birth_date | education |
+------+--------+------+--------+--------+------------+-----------+
|    6 | 产品部 |   26 | 翟红   | 女     | 1975-05-08 | 硕士      |
|    6 | 产品部 |   36 | 路宁   | 男     | 1981-12-14 | 本科      |
|    6 | 产品部 |   43 | 古娟   | 女     | 1973-01-29 | 本科      |
...
|    2 | 人事处 |  227 | 明洪   | 男     | 1977-07-26 | 本科      |
|    7 | 公关部 | NULL | NULL   | NULL   | NULL       | NULL      |
|    3 | 售后部 | NULL | NULL   | NULL   | NULL       | NULL      |
|    4 | 营销部 |    3 | 申丽华 | 女     | 1978-04-22 | 本科      |
...
|    5 | 财务室 |  233 | 洪志明 | 男     | 1991-07-12 | 本科      |
| NULL | NULL   |  113 | 顾桂花 | 女     | 1981-12-23 | 硕士      |
| NULL | NULL   |  191 | 张丽   | 女     | 1981-12-10 | 硕士      |
+------+--------+------+--------+--------+------------+-----------+
235 rows in set (0.001 sec)

（6）列出部门和员工，包括无员工的部门和无部门的员工，结果按部门编号和员工编号进行升序排序
部门信息包括：id, name
员工信息包括：id, name, gender, birth_date, education
a）连接查询的确定
需要所有部门和所有员工的对应记录，因此应当采用全连接（实现：对员工表和部门表分别偏向一次进行外连接，将结果联合起来）。
b）查询条件
无。
c）其它需求
结果集按部门编号（departmemts.id）和员工编号（employees.id）进行升序排序。

如果需要对全连接的结果进行过滤、排序、限定记录数等，需要采取以下形式：

SELECT 列名列表 
FROM (全连接查询) 结果集别名
[WHERE ...]
[ORDER BY ...]
[LIMIT ...]

以下查询将全连接的结果集按部门编号（departmemts.id）和员工编号（employees.id）进行排序：

SELECT * FROM (
    SELECT `d`.`id`,`d`.`name`, `e`.`id` AS `eid`, `e`.`name` AS `ename`, `e`.`gender`, `e`.`birth_date`, `e`.`education`
    FROM `departments` `d` LEFT OUTER JOIN `employees` `e`
    ON `d`.`id`=`e`.`department_id`
    UNION
    SELECT `d`.`id`,`d`.`name`, `e`.`id` AS `eid`, `e`.`name` AS `ename`, `e`.`gender`, `e`.`birth_date`, `e`.`education`
    FROM `departments` `d` RIGHT OUTER JOIN `employees` `e`
    ON `d`.`id`=`e`.`department_id`
) `a`
ORDER BY `id`,`eid`;
+------+--------+------+--------+--------+------------+-----------+
| id   | name   | eid  | ename  | gender | birth_date | education |
+------+--------+------+--------+--------+------------+-----------+
| NULL | NULL   |  113 | 顾桂花 | 女     | 1981-12-23 | 硕士      |
| NULL | NULL   |  191 | 张丽   | 女     | 1981-12-10 | 硕士      |
|    1 | 设计部 |    1 | 萧正诚 | 男     | 1990-03-07 | 本科      |
|    1 | 设计部 |    6 | 陆桂香 | 女     | 1988-02-14 | 本科      |
|    1 | 设计部 |   11 | 刘丹丹 | 女     | 1977-04-04 | 本科      |
...
|    2 | 人事处 |  227 | 明洪   | 男     | 1977-07-26 | 本科      |
|    3 | 售后部 | NULL | NULL   | NULL   | NULL       | NULL      |
|    4 | 营销部 |    3 | 申丽华 | 女     | 1978-04-22 | 本科      |
|    4 | 营销部 |    5 | 房捷   | 男     | 1979-05-10 | 博士      |
...
|    6 | 产品部 |  213 | 匡智敏 | 男     | 1974-09-03 | 硕士      |
|    6 | 产品部 |  224 | 金爱华 | 女     | 1976-08-07 | 本科      |
|    6 | 产品部 |  225 | 武松   | 男     | 1984-02-29 | 硕士      |
|    7 | 公关部 | NULL | NULL   | NULL   | NULL       | NULL      |
+------+--------+------+--------+--------+------------+-----------+
235 rows in set (0.002 sec)