Linux 必掌握的 SQL 命令
在本系列教程中，目前我们使用平面文本文件来存储数据。平面文本文件可能适合相对较少的数据，但它们对存储大量数据或查询该数据没有多大帮助。多年来，为该目的开发了多种数据库，包括分层和网络数据库，但现在最常见的是关系数据库。关系数据库基于 E. F. Codd 的想法，E. F. Codd 效力于 IBM®，他在 1970 年发表了原创性论文 “一种针对大型共享数据银行的关系数据模型”。如今有多种关系数据库系统，包括商用产品（比如 IBM DB2®、IBM Informix® 和 Oracle Database）和开源项目（比如 MySQL、PostgreSQL SQLite 和 MariaDB[MySQL 的一个分支]）。关系数据库使用 SQL 作为数据定义和查询语言。
要从本系列教程中获得最大收获，您应该拥有 Linux 的基本知识和一个正常工作的 Linux 系统，您可以在这个系统上实践本教程中涵盖的命令。您应该熟悉 GNU 和 UNIX® 命令。有时，一个程序的不同版本会以不同方式格式化输出，所以您的结果可能并不总是与这里给出的清单完全一样。
对于本教程，您需要一个数据库（比如 MariaDB）和您的数据库的文档。
本教程中的 SQL 示例在很大程度上独立于发行版和数据库。大部分示例都使用了含 4.2.6 内核的 Fedora 23 上的开源 MariaDB 10.0.21 版。我还提供了一些使用 IBM DB2 Express-C 10.5 版的示例，该版本是 IBM DB2 数据服务器在含 2.6.32 内核的 CentOS 6.7 上的一个免费社区版本。通过比较这些示例，您可以了解您在使用多种数据库系统时可能遇到的差别。如果您需要实现可移植的数据库程序或脚本，则要求您对 ISO/ANSI SQL 标准的了解比我在这里介绍的多一些。
一些数据库操作命令是特定于数据库的。SQL 语法中也存在一些小差异，尤其是对于非标准扩展。在必要时，请参阅您使用的数据库的文档。
关系数据库由一组表 组成。可以将表中的每行 数据视为一条记录，表的每列 对应于相应行的记录中的字段。一列中的数据都具有相同的类型，比如字符、整数、日期或二进制数据（比如图像）。通过以这种方式使用结构化数据，您可以构造有益的查询，比如 “找到在某个日期后招聘的所有员工” 或 “找到所有大于 0.25 英亩的地块”。
关系数据库中的数据可能在每行有一个唯一值，比如员工记录中的员工 ID，市政土地数据库中的地块 ID，或者计算机系统中的用户 ID。如果是这样，您可以为该列创建一个索引，可以将一个这样的列指定为该表的主键。键和索引可帮助数据库引擎加速数据的检索。
当您安装数据库时，您通常会安装一个数据库服务器。您可能还会安装一个客户端，或者可能通过编写的应用程序或命令行访问该服务器。服务器通常作为后台进程运行，您的客户端或应用程序通常通过 TCP/IP 连接来连接它。您必须采取相应的措施来保护安装，预防您不想支持的访问。
许多 Linux 发行版包含 MariaDB、SQLite、PostgreSQL 和 MySQL 的包。对于本教程，我主要使用了 MariaDB。请参阅 “包含 MariaDB 的发行版” 或使用适合您的发行版的包管理工具来检查已为您的发行版打包了哪些数据库。MariaDB 是 MySQL 的一个开源分支，所以 mysql 是许多命令名称的一部分。
这里提供一些简单技巧，帮助您在打包了 MariaDB 的发行版上运行它。我在本教程中使用了 Fedora 23。首先安装 mariadb 和 mariadb-server 包，它们会引入所需的其他一些包。
接下来，采用拥有 root 用户权限的用户身份运行 mysql_secure_installation 命令。通过运行此命令，您可以：
为一个 root 数据库用户设置一个密码
删除最初安装用于测试的匿名用户
确保该 root 用户仅能从本地系统登录
删除测试数据库（可选）
重新加载特权表，让之前的更改立即生效
数据库 root 用户不是系统 root 用户，不应拥有相同的密码。如果您需要执行更改，可以重新运行 mysql_secure_installation。
对于本教程，我使用了来自 GitHub 的 test_db 包中包含的 employee 示例数据库。清单 1 给出了我将该数据库安装在我的系统上所用的步骤。
[ian@attic-f23 ~]$ unzip -q test_db-master.zip [ian@attic-f23 ~]$ cd test_db-master [ian@attic-f23 test_db-master]$ mysql -u root -p (< )employees .sql Enter password: INFO CREATING DATABASE STRUCTURE INFO storage engine: InnoDB INFO LOADING departments INFO LOADING employees INFO LOADING dept_emp INFO LOADING dept_manager INFO LOADING titles INFO LOADING salaries data_load_time_diff NULL.
清单 1 中使用的默认 InnoDB 引擎适合本教程的目的。
现在 MariaDB 已安装在您的系统上，您可以启动它来看看您拥有哪些数据库。清单 2 使用 mysqlshow 命令来显示我已安装的数据库。-u 选项指定数据库 root 用户，-p 选项告诉 mysqlshow 提示您输入您在运行 mysql_secure_installation 命令时定义的密码。
[ian@attic-f23 ~]$ mysqlshow -u root -p Enter password: +--------------------+ | Databases | +--------------------+ | employees | | information_schema | | mysql | | performance_schema | | test | +--------------------+
可以看到我有 5 个数据库：我刚刚创建的 employees 数据库，我没有创建的 test 数据库，以及其他 3 个数据库。数据库程序通常包含多个数据库来描述该数据库本身，您可以在 清单 2 中看到它们。
mysqlshow 命令是一个快速列出数据库、表和列信息的便捷工具。MariaDB（和 MySQL）包含一个类似于数据库 shell 的交互式命令行接口 (CLI) 程序，名为 mysql。DB2 还有一个 CLI 程序，名为 db2。与 bash 等 shell 一样，您可以将一个命令传递到任意一个数据库 shell，也可以运行一个包含许多命令的交互式会话。清单 3 通过带 -e 选项的 mysql 命令执行单个数据库命令来显示数据库信息。
[ian@attic-f23 ~]$ mysql -u root -p -e "show databases" Enter password: +--------------------+ | Database | +--------------------+ | employees | | information_schema | | mysql | | performance_schema | | test | +--------------------+
如果您还安装了 DB2 Express-C，那么您已经创建了一个名为 db2inst1 的用户（默认用户）来管理该数据库。清单 4 展示了如何使用 db2 命令获取 DB2 数据库的相应信息。
[ian@attic4-cent ~]$ db2 list database directory System Database Directory Number of entries in the directory = 1 Database 1 entry: Database alias = SAMPLE Database name = SAMPLE Local database directory = /home/db2inst1 Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number =
在从数据库提取信息之前，您需要知道数据库中有什么。在 清单 5 中，您可以了解如何：
启动 mysql 交互式数据库 shell，并使用您创建的 root ID 连接到 employees 数据库
使用 show tables 命令查看 employees 数据库中包含哪些表
使用 describe 命令查看 employees 数据库中的 employees 表中包含哪些列
[ian@attic-f23 ~]$ mysql -u root -p employees
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.0.21-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [employees]> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
MariaDB [employees]> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
employees 表中的每个字段（或列）有多个属性：
Field：列的名称。
Type：列的数据类型。许多数据类型都具有最大长度限制。例如，int(11) 指定一个可包含 11 位数的整数，varchar(16) 指定包含最多 16 字节数据的可变长度字符串。请参阅 “SQL 数据类型” 部分，了解有关数据类型的更多信息。
Null：指定是否允许该列拥有 null（空）值。
Key：如果该列是一个键，则表示键类型。主 (PRI) 键必须包含非 null 的唯一值。
Default：指定如果添加一条记录且没有为该列提供数据，则在该列中放入什么样的默认值。
Extra：指定额外属性（例如 auto_increment，它用于创建唯一递增编号，比如序列号）。
如果您在 shell 中需要获得帮助，可以使用 help（或 ?）命令。清单 6 给出了 describe 命令的帮助输出。
MariaDB [employees]> ? describe
Name: 'DESCRIBE'
Description:
Syntax:
{DESCRIBE | DESC} tbl_name [col_name | wild]
DESCRIBE provides information about the columns in a table. It is a
shortcut for SHOW COLUMNS FROM. These statements also display
information for views. (See [HELP SHOW COLUMNS].)
col_name can be a column name, or a string containing the SQL "%" and
"_" wildcard characters to obtain output only for the columns with
names matching the string. There is no need to enclose the string
within quotation marks unless it contains spaces or other special
characters.
MariaDB> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
The description for SHOW COLUMNS provides more information about the
output columns (see [HELP SHOW COLUMNS]).
URL: https://mariadb.com/kb/en/describe/
请注意，该帮助以全大写字母形式显示 DESCRIBE。通常，SQL 命令名称是不区分大小写的。其他对象（比如数据库、表或列）的名称是否区分大小写取决于您的数据库程序和运行它的平台。请参见 参考资料，了解有关 MariaDB 的更多信息。
您已在 清单 5 中看到了 SQL 数据类型的示例。典型的 SQL 数据类型大体分为 4 类：
String 数据类型存储固定长度或可变长度的字符或二进制串，以及大对象。示例包括 CHAR(8)、VARCHAR(240)、BINARY(12)、VARBINARY(500) 和 BLOB(200000)。
Numeric 数据类型存储定点数或浮点数。示例包括 SMALLINT(16 位）、INT 或 INTEGER（32 位）、BIGINT（64 位）、FLOAT（单精度浮点）、DOUBLE（双精度浮点）和 DECIMAL（一个包含小数点的压缩十进制数）。整数数据可以是有符号或无符号的。
Boolean 数据类型存储 TRUE 或 FALSE 值。
Date 和 time 值存储可用于比较的日期和时间。示例包括 DATE 和 TIME。
这些示例并不详尽，而且不同的数据库程序可能会扩展它们。例如，DB2 支持使用 CLOB 存储字符大对象，使用 DBCLOB 存储包含双字节字符数据的大对象。MariaDB 支持使用 TINYINT 存储 1 字节整数和使用 ENUM 存储枚举数据，您在 清单 5 中的 gender 字段中已看到。
备注：在 SQL 中使用枚举数据类型存在一定的争议。更传统的方法是使用具有外键 的参考表。可以在网络上搜索来进一步了解对数据库中的枚举数据类型的争议。
您现在已经看到了数据库、表和表中的数据类型的示例。下一步是获取表中的数据。可以使用 SELECT 语句检索数据。可以选择表中的所有数据，或者选择特定列的数据。清单 7 显示了 departments 表中的数据，以及如何通过 SELECT * 选择其中的所有数据，然后如何仅选择部门名称。
MariaDB [employees]> describe departments; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | dept_no | char(4) | NO | PRI | NULL | | | dept_name | varchar(40) | NO | UNI | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) MariaDB [employees]> select * from departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 9 rows in set (0.00 sec) MariaDB [employees]> select dept_name from departments; +--------------------+ | dept_name | +--------------------+ | Customer Service | | Development | | Finance | | Human Resources | | Marketing | | Production | | Quality Management | | Research | | Sales | +--------------------+ 9 rows in set (0.00 sec)
选择 departments 表中的所有数据时，像 清单 7 中一样，输出会按部门名称排序。如果您希望根据一个或多个列中的值对输出数据进行排序，可以使用 ORDER BY 子句。指定一个用逗号分隔的列组成的列表。也可以指定 ASC 来按升序进行排序（默认），或者指定 DESC 来按照降序进行排序。清单 8 展示了如何按 dept_no 进行降序排序。
MariaDB [employees]> select dept_name,dept_no from departments order by dept_no desc; +--------------------+---------+ | dept_name | dept_no | +--------------------+---------+ | Customer Service | d009 | | Research | d008 | | Sales | d007 | | Quality Management | d006 | | Development | d005 | | Production | d004 | | Human Resources | d003 | | Finance | d002 | | Marketing | d001 | +--------------------+---------+ 9 rows in set (0.00 sec)
可以使用 WHERE 子句选择要显示哪些数据。可以比较各列，或者将列值与某个值比较。该值（称为标量）可以是一个常数（必须包含在引号内）或一个标量函数的结果，比如今天的日期。可使用布尔 AND、OR 和 NOT 运算符来定义更复杂的条件。清单 9 给出了一些使用 departments 表的示例。
MariaDB [employees]> select * from departments where dept_no > 'd007';
+---------+------------------+
| dept_no | dept_name |
+---------+------------------+
| d008 | Research |
| d009 | Customer Service |
+---------+------------------+
2 rows in set (0.00 sec)
MariaDB [employees]> select * from departments
-> WHERE dept_name = 'Customer Service' OR (
-> dept_no > 'd002' AND dept_no < = "d006" -> );
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d003 | Human Resources |
| d004 | Production |
| d006 | Quality Management |
+---------+--------------------+
5 rows in set (0.00 sec)
请注意，在 清单 9 中，来自更复杂的 WHERE 子句的输出没有排序。使用 ORDER 子句来采用您想要的排序方式。另请注意，您可以将 SQL 命令输入在多行上。MariaDB 在第二行和后续行上提供了一个 -> 提示符。我在后续示例中删除了这些辅助提示符，以便您可以更轻松地将该命令直接复制并粘贴到自己的系统中。
您还可以使用 LIKE 来执行模式匹配和直接比较。大多数 SQL 数据库都支持以下两种通配符：
% 匹配任意多个字符，包括空字符串。
_ 匹配任何单一字符。
清单 10 展示了如何查找所有包含字符串 es 的部门名称。
MariaDB [employees]> select * from departments WHERE dept_name LIKE '%es%'; +---------+-----------------+ | dept_no | dept_name | +---------+-----------------+ | d003 | Human Resources | | d008 | Research | | d007 | Sales | +---------+-----------------+ 3 rows in set (0.00 sec)v
一些数据库（包括 MariaDB）支持正则表达式模式匹配。请参阅您数据库的文档，了解关于这个主题的更多信息。
您已经了解了一些简单的比较运算符。SQL 还有一些函数可供使用，它们包括：
COUNT：统计返回的行数。
DISTINCT：仅选择不同的值。
MAX 和 MIN：选择一对值中的最大值或最小值。
NOW：返回当前的日期和时间。
DATEDIFF：将两个日期相减，返回它们之间相隔的天数。
DAY：返回给定日期是星期几。
LEAST：找到一组值中最小的一个值。
还有其他许多这样的函数，所以请参阅您的文档来了解它们。
清单 11 展示了如何统计两个不同表中的行数。
MariaDB [employees]> # How many employees? MariaDB [employees]> select count(*) from employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.08 sec) MariaDB [employees]> # How many managers? MariaDB [employees]> select count(*) from dept_manager; +----------+ | count(*) | +----------+ | 24 | +----------+ 1 row in set (0.00 sec)
清单 12 展示了如何使用 DISTINCT 函数来查看一些经理是否与其他经理在同一日期招聘的。
MariaDB [employees]> describe dept_manager; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | emp_no | int(11) | NO | PRI | NULL | | | dept_no | char(4) | NO | PRI | NULL | | | from_date | date | NO | | NULL | | | to_date | date | NO | | NULL | | +-----------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) MariaDB [employees]> select DISTINCT(from_date) from dept_manager order by from_date; +------------+ | from_date | +------------+ | 1985-01-01 | | 1988-09-09 | | 1988-10-17 | | 1989-05-06 | | 1989-12-17 | | 1991-03-07 | | 1991-04-08 | | 1991-09-12 | | 1991-10-01 | | 1992-03-21 | | 1992-04-25 | | 1992-08-02 | | 1992-09-08 | | 1994-06-28 | | 1996-01-03 | | 1996-08-30 | +------------+ 16 rows in set (0.00 sec)
仅返回了 16 行。您可以组合 COUNT 和 DISTINCT 来获得此数字，但您在 清单 12 中也有实际的开始日期。您对多个经理开始上任的日期还一无所知。
假设您想找到某位经理上任了多长时间。可以计算 from_date 与 to_date 的差，但是如何将此运算应用于目前正在任职的经理？表通常使用某种标记来表示当前日期，该日期也许是一个 NULL 值，也许是未来的一个日期。dept_manager 表使用一个未来的日期实现此目的。to_date 晚于今日表示该经理仍在任职。可使用 NOW 函数获得当前的时间戳，或者使用 CURDATE 函数仅获取当前日期。清单 13 展示了一种查找每位经理任职多少天的方式。
MariaDB [employees]> # Show marker for managers still managing
MariaDB [employees]> select max(to_date) from dept_manager;
+--------------+
| max(to_date) |
+--------------+
| 9999-01-01 |
+--------------+
1 row in set (0.00 sec)
MariaDB [employees]> # Calculate duration of management in days
MariaDB [employees]> select emp_no,
datediff(least(to_date, curdate()),from_date)
from dept_manager;
+--------+-----------------------------------------------+
| emp_no | datediff(least(to_date, curdate()),from_date) |
+--------+-----------------------------------------------+
| 110022 | 2464 |
| 110039 | 8869 |
| 110085 | 1811 |
| 110114 | 9522 |
| 110183 | 2636 |
| 110228 | 8697 |
| 110303 | 1347 |
| 110344 | 1423 |
| 110386 | 1489 |
| 110420 | 7074 |
| 110511 | 2671 |
| 110567 | 8662 |
| 110725 | 1586 |
| 110765 | 859 |
| 110800 | 1020 |
| 110854 | 7868 |
| 111035 | 2256 |
| 111133 | 9077 |
| 111400 | 2288 |
| 111534 | 9045 |
| 111692 | 1385 |
| 111784 | 1422 |
| 111877 | 1212 |
| 111939 | 7314 |
+--------+-----------------------------------------------+
24 rows in set (0.00 sec)
当您拥有长表达式时，比如 datediff(least(to_date,curdate()),from_date)，您可能希望缩短列标题或为该表达式提供一个别名，因为您计划在查询中的其他地方使用它，例如在 WHERE 子句中。清单 14 展示了如何使用别名来提供更短的标题，查找所有现任经理任职了多少年。
MariaDB [employees]> select emp_no AS Employee,
(datediff(least(to_date, curdate()),from_date)/365.25) as Years
from dept_manager where to_date > curdate();
+----------+---------+
| Employee | Years |
+----------+---------+
| 110039 | 24.2820 |
| 110114 | 26.0698 |
| 110228 | 23.8111 |
| 110420 | 19.3676 |
| 110567 | 23.7153 |
| 110854 | 21.5414 |
| 111133 | 24.8515 |
| 111534 | 24.7639 |
| 111939 | 20.0246 |
+----------+---------+
9 rows in set (0.00 sec)
有时您想汇总来自某个表的信息。例如，您想知道在每个 10000 美元薪资区间内有多少员工。为此，可以使用 GROUP BY 子句对您的数据进行分组。清单 15 展示了如何做。
MariaDB [employees]> describe salaries;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| salary | int(11) | NO | | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [employees]> select salary DIV 10000 as 10K, count(*) as Number
from salaries where to_date > curdate() group by 10K;
+------+--------+
| 10K | Number |
+------+--------+
| 3 | 85 |
| 4 | 20220 |
| 5 | 44666 |
| 6 | 56236 |
| 7 | 49128 |
| 8 | 32351 |
| 9 | 19939 |
| 10 | 10611 |
| 11 | 4729 |
| 12 | 1645 |
| 13 | 421 |
| 14 | 78 |
| 15 | 15 |
+------+--------+
13 rows in set (1.42 sec)
在 清单 15 中，Number 列中的值是从聚合数据计算出来的。假设您仅对该范围中拥有 5,000 或更少员工的薪资范围感兴趣。您的第一个想法可能是使用 WHERE 子句，但您不能使用它获得计算为聚合数据的中间结果数据。您需要使用 HAVING 子句，将中间结果限制到一个具有特定条件或条件组合的子集。清单 16 展示了如何查找该范围中拥有 5,000 或更少员工的薪资范围。
MariaDB [employees]> select salary DIV 10000 as 10K, count(*) as Number
from salaries where to_date > curdate() group by 10K
HAVING Number < = 5000;
+------+--------+
| 10K | Number |
+------+--------+
| 3 | 85 |
| 11 | 4729 |
| 12 | 1645 |
| 13 | 421 |
| 14 | 78 |
| 15 | 15 |
+------+--------+
6 rows in set (1.45 sec)
目前为止，本教程中的示例都使用了单个表。salaries 表和 dept_manager 表包含员工编号，但不含姓名或其他员工信息。员工信息保存在 employees 表中。通过将数据保存在单独的表中，SQL 设计可以消除（或者至少减少）数据存储冗余，以及在一个位置更新数据而不在另一个位置更新的相关风险。
在您想提取信息时，比如所有现任经理的姓名和性别，您需要从 dept_manager 表和 employees 表获取此信息。出于这个目的，您使用 JOIN 子句和一个指定联接 (join) 条件的条件表达式。最常见的是，您将两个表联接在单个字段上，该字段值在两个表中相同，例如，dept_manager 表和 employees 表中都包含的 emp_no 字段。
清单 17 展示了如何使用 JOIN 查找所有现任经理的姓名和性别。（请注意，我们使用了别名 e 和 m 来表示 dept_manager 表和 employees 表。）
MariaDB [employees]> SELECT e.first_name, e.last_name, e.gender
FROM employees as e JOIN dept_manager as m
ON e.emp_no = m.emp_no
WHERE m.to_date > now()
ORDER BY e.last_name;
+------------+------------+--------+
| first_name | last_name | gender |
+------------+------------+--------+
| Leon | DasSarma | F |
| Oscar | Ghazalie | M |
| Hilary | Kambil | F |
| Isamu | Legleitner | F |
| Vishwani | Minakawa | M |
| Dung | Pesch | M |
| Karsten | Sigstam | F |
| Yuchang | Weedman | M |
| Hauke | Zhang | M |
+------------+------------+--------+
9 rows in set (0.00 sec)
如果您想要使用薪资而不是性别作为输出列，必须将 salaries 表与其他两个表联接，如 清单 18 所示。（我添加了一些括号，我发现括号对构造复杂查询通常很有用。）
MariaDB [employees]> SELECT e.first_name, e.last_name, s.salary
FROM (employees as e JOIN dept_manager as m ON e.emp_no = m.emp_no)
JOIN salaries as s on e.emp_no = s.emp_no
WHERE m.to_date > now() AND s.to_date > now()
ORDER BY e.last_name;
+------------+------------+--------+
| first_name | last_name | salary |
+------------+------------+--------+
| Leon | DasSarma | 74510 |
| Oscar | Ghazalie | 56654 |
| Hilary | Kambil | 79393 |
| Isamu | Legleitner | 83457 |
| Vishwani | Minakawa | 106491 |
| Dung | Pesch | 72876 |
| Karsten | Sigstam | 65400 |
| Yuchang | Weedman | 58745 |
| Hauke | Zhang | 101987 |
+------------+------------+--------+
9 rows in set (0.00 sec)
清单 17 和 清单 18 中的 JOIN 示例称为内部 联接：它们从两个表中查找与联接条件匹配的行。另外 3 种常见的联接是：
LEFT JOIN 查找左侧表中的所有行和右侧表中与该联接条件匹配的行。
RIGHT JOIN 查找右侧表中的所有行和左侧表中与该联接条件匹配的行。
OUTER JOIN 组合 LEFT JOIN 和 RIGHT JOIN 的结果。许多数据库（包括 IBM DB2）将此称为 FULL OUTER JOIN。
INNER JOIN 最常用，而且是没有指定联接类型时的默认选择。清单 19 演示了如何使用 LEFT JOIN 来显示选定的员工，如果他们恰好是经理，则显示他们的 from_date 和 to_date。在这个示例中，我还使用了 LIMIT 值将输出限制为最多 15 行。
MariaDB [employees]> SELECT e.emp_no, e.first_name, e.last_name, s.salary, m.from_date, m.to_date
FROM (employees as e LEFT JOIN dept_manager as m ON e.emp_no = m.emp_no)
JOIN salaries as s on e.emp_no = s.emp_no AND s.to_date > now()
WHERE e.last_name LIKE 'Kambi%' AND e.first_name > 'G'
ORDER BY e.last_name, e.first_name limit 15;
+--------+------------+-----------+--------+------------+------------+
| emp_no | first_name | last_name | salary | from_date | to_date |
+--------+------------+-----------+--------+------------+------------+
| 431582 | Gaurav | Kambil | 118128 | NULL | NULL |
| 252478 | Gaurav | Kambil | 69516 | NULL | NULL |
| 487991 | Gift | Kambil | 115960 | NULL | NULL |
| 204311 | Gil | Kambil | 96756 | NULL | NULL |
| 416604 | Gonzalo | Kambil | 80009 | NULL | NULL |
| 236164 | Hausi | Kambil | 66130 | NULL | NULL |
| 412003 | Hausi | Kambil | 83213 | NULL | NULL |
| 111534 | Hilary | Kambil | 79393 | 1991-04-08 | 9999-01-01 |
| 295702 | Huei | Kambil | 49498 | NULL | NULL |
| 77408 | Idoia | Kambil | 67122 | NULL | NULL |
| 271049 | Jianhao | Kambil | 58393 | NULL | NULL |
| 216820 | JiYoung | Kambil | 87541 | NULL | NULL |
| 206261 | Jongsuk | Kambil | 78396 | NULL | NULL |
| 250164 | Josyula | Kambil | 98835 | NULL | NULL |
| 289558 | Jouko | Kambil | 51393 | NULL | NULL |
+--------+------------+-----------+--------+------------+------------+
15 rows in set (0.14 sec)
有时您仅对查询中的部分数据感兴趣，希望仅操作这部分数据。出于此目的，您可以使用子选择（也称为子查询），它实质上是 SELECT 中的另一个 SELECT。您还可以在 FROM、WHERE 或 HAVING 子句中使用子选择。有时通过子选择完成的工作也可以通过 JOIN 完成。考虑到如此多的选择性，我将展示两个示例来让您了解这些。
employees 数据库中的 titles 表显示了一位员工已担任的职位。一些员工担任了多个职位。清单 20 展示了如何找到 3 位员工担任的职位。请注意，我们使用了 IN 运算符来从一个集合中选择结果，使用了 BETWEEN 运算符来选择两个值之间的员工数。
MariaDB [employees]> describe titles; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | emp_no | int(11) | NO | PRI | NULL | | | title | varchar(50) | NO | PRI | NULL | | | from_date | date | NO | PRI | NULL | | | to_date | date | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) MariaDB [employees]> select * from titles where emp_no IN (10001, 10004, 499666); +--------+--------------------+------------+------------+ | emp_no | title | from_date | to_date | +--------+--------------------+------------+------------+ | 10001 | Senior Engineer | 1986-06-26 | 9999-01-01 | | 10004 | Engineer | 1986-12-01 | 1995-12-01 | | 10004 | Senior Engineer | 1995-12-01 | 9999-01-01 | | 499666 | Assistant Engineer | 1987-10-18 | 1994-10-18 | | 499666 | Engineer | 1994-10-18 | 2001-10-18 | | 499666 | Senior Engineer | 2001-10-18 | 9999-01-01 | +--------+--------------------+------------+------------+ 6 rows in set (0.00 sec) MariaDB [employees]> select * from titles where emp_no BETWEEN 10001 AND 10004; +--------+-----------------+------------+------------+ | 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 | | 10004 | Engineer | 1986-12-01 | 1995-12-01 | | 10004 | Senior Engineer | 1995-12-01 | 9999-01-01 | +--------+-----------------+------------+------------+ 5 rows in set (0.01 sec)
您已经知道如何使用 GROUP BY 和 COUNT 来查找每位员工担任过多少个职位。如果只想知道多少员工担任过一个职位，多少员工担任过两个职位等，该如何做？ 清单 21 展示了如何构造一个查询来查找每位员工担任的职位数，然后如何使用此查询作为一个查询的子选择，回答多少员工担任过一个职位，多少员工担任过两个职位等问题。我将这个子查询命名为 subq。该子查询只返回一列：jobs。主要查询使用限定名称 subq.jobs 来引用此列。
MariaDB [employees]> SELECT count(*) as jobs from titles group by emp_no limit 5;
+------+
| jobs |
+------+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
+------+
5 rows in set (0.00 sec)
MariaDB [employees]> SELECT jobs as '# jobs', count(subq.jobs) as '# employees'
FROM (
SELECT count(*) as jobs from titles group by emp_no
) subq
group by jobs;
+--------+-------------+
| # jobs | # employees |
+--------+-------------+
| 1 | 159754 |
| 2 | 137256 |
| 3 | 3014 |
+--------+-------------+
3 rows in set (0.31 sec)
现在假设您想知道公司中薪资最高的 10 位员工。清单 22 展示了另一个返回此信息的子选择。
MariaDB [employees]> SELECT e.emp_no, e.last_name, e.first_name,
(
SELECT MAX(salary) FROM salaries s
WHERE e.emp_no = s.emp_no
) max_sal
FROM employees e ORDER BY max_sal desc limit 10;
+--------+-----------+------------+---------+
| emp_no | last_name | first_name | max_sal |
+--------+-----------+------------+---------+
| 43624 | Pesch | Tokuyasu | 158220 |
| 254466 | Mukaidono | Honesty | 156286 |
| 47978 | Whitcomb | Xiahua | 155709 |
| 253939 | Luders | Sanjai | 155513 |
| 109334 | Alameldin | Tsutomu | 155377 |
| 80823 | Baca | Willard | 154459 |
| 493158 | Meriste | Lidong | 154376 |
| 205000 | Griswold | Charmane | 153715 |
| 266526 | Chenoweth | Weijing | 152710 |
| 237542 | Hatcliff | Weicheng | 152687 |
+--------+-----------+------------+---------+
10 rows in set (2.66 sec)
我提到过您有时可以使用 JOIN 完成子选择的工作。清单 23 展示了如何使用 JOIN 查找薪资最高的 10 位员工。您可以注意到，此查询的执行速度比 清单 22 的子选择更快。一般而言，如果注重性能，首先应该选择使用联接而不是子选择。性能差异取决于您的数据库引擎。
MariaDB [employees]> SELECT e.emp_no, e.last_name, e.first_name, max(s.salary) as max_sal
FROM employees e JOIN salaries s
ON e.emp_no = s.emp_no
GROUP BY e.emp_no ORDER BY max_sal desc limit 10;
+--------+-----------+------------+---------+
| emp_no | last_name | first_name | max_sal |
+--------+-----------+------------+---------+
| 43624 | Pesch | Tokuyasu | 158220 |
| 254466 | Mukaidono | Honesty | 156286 |
| 47978 | Whitcomb | Xiahua | 155709 |
| 253939 | Luders | Sanjai | 155513 |
| 109334 | Alameldin | Tsutomu | 155377 |
| 80823 | Baca | Willard | 154459 |
| 493158 | Meriste | Lidong | 154376 |
| 205000 | Griswold | Charmane | 153715 |
| 266526 | Chenoweth | Weijing | 152710 |
| 237542 | Hatcliff | Weicheng | 152687 |
+--------+-----------+------------+---------+
10 rows in set (2.16 sec)
在 “了解表和列” 部分，我提到过 SQL 中的枚举类型存在一些争议。employees 表中的 gender 列是一个包含值 M 和 F 的 ENUM。这些值看起来像字符，但实际上它们在内部被存储为整数。它们依据其内部数值表示而存储在一个 ORDER BY 子句中，这可能带来令人惊讶的结果。清单 24 展示了按性别排序的前 5 位员工。
MariaDB [employees]> SELECT * FROM (
SELECT * FROM employees LIMIT 5
) x ORDER BY x.gender;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 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 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
您可能会奇怪地看到 M 值在 F 值之前列出。如果想按字母顺序进行排列，可在 ORDERBY 子句中使用 CAST 或 CONVERT。例如：
ORDER BY CAST(x.gender AS CHAR)
您已经学习了表和如何了解它们的结构。您还学习了如何创建 SQL 查询，以便从一个或多个表中的数据中寻找答案。在本教程的剩余部分中，我将展示如何创建和操作表，如何插入、更新和删除表中的数据。
假设您决定为员工性别创建一个参考表，而不是使用枚举。您决定提取性别的首字母，也就是英文字母 M 或 F。您还想要包含完整单词 MALE 或 FEMALE 的另一列。第一步是使用 CREATE TABLE 创建该表。CREATE TABLE 的基本形式提供了一个表名称，后跟一个列、索引和约束列表。清单 25 展示了如何创建包含两个列的 gender 表，一个列是表示主键的字符，另一个列是一个最多 20 个字符的可变字符字段。
MariaDB [employees]> CREATE TABLE gender (
code CHAR(1) NOT NULL,
gender VARCHAR(20) NOT NULL,
PRIMARY KEY (code)
);
Query OK, 0 rows affected (0.23 sec)
MariaDB [employees]> describe gender;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| code | char(1) | NO | PRI | NULL | |
| gender | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
现在您有一个空表，您需要在其中填入数据。使用 INSERT 命令加载一行或多行数据。您指定想要在其中插入数据的列的列表，然后指定一个或多个匹配值列表。清单 26 展示了如何添加您计划的两行。
MariaDB [employees]> INSERT INTO gender (code, gender)
VALUES
('F', 'FEMALE'),
('M', 'MALE');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [employees]> select * from gender;
+------+--------+
| code | gender |
+------+--------+
| F | FEMALE |
| M | MALE |
+------+--------+
2 rows in set (0.00 sec)
接下来假设您认为一些员工可能需要在您知道其性别之前添加到数据库中，所以您决定为 UNASSIGNED 性别添加第三种可能性。清单 27 展示了另一种插入数据的方式：通过使用 SET 子句。
MariaDB [employees]> INSERT INTO gender SET code='U', gender='UNSIGNED'; Query OK, 1 row affected (0.04 sec) MariaDB [employees]> select * from gender; +------+----------+ | code | gender | +------+----------+ | F | FEMALE | | M | MALE | | U | UNSIGNED | +------+----------+ 3 rows in set (0.00 sec)
我使用的示例数据库只有两个 gender 值。假设您的公司采用了一种性别多样性策略，您需要为具有多样性性别的员工提供额外的值。使用 VALUES 选项或 SET 选项和 INSERT INTO 向您的表中插入更多行。
由于录入错误，我本来打算插入 UNASSIGNED，但插入了 UNSIGNED。使用 UPDATE 命令修复此错误，如 清单 28 所示。
MariaDB [employees]> UPDATE gender SET gender='UNASSIGNED' WHERE code='U'; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [employees]> select * from gender; +------+------------+ | code | gender | +------+------------+ | F | FEMALE | | M | MALE | | U | UNASSIGNED | +------+------------+ 3 rows in set (0.00 sec)
现在人力资源经理告诉您，该公司始终在将新员工添加到数据库之前就知道该员工的性别，所以您需要删除表示未分配性别的条目。使用 DELETE 命令，如 清单 29 所示。
MariaDB [employees]> DELETE FROM gender WHERE code='U'; Query OK, 1 row affected (0.04 sec) MariaDB [employees]> select * from gender; +------+--------+ | code | gender | +------+--------+ | F | FEMALE | | M | MALE | +------+--------+ 2 rows in set (0.00 sec)
有时您希望更改一个表。您可以执行的部分操作包括：
重命名一列。
添加一个新列。
删除一列。
更改一列的数据类型。
更改一列上的约束。
清单 30 展示了如何修改 gender 表来添加一个新列 gender_fr，表示 gender 列的法语版本，加载新的法语数据，然后将现有的 gender 列重命名为 gender_en。
MariaDB [employees]> ALTER TABLE gender ADD COLUMN gender_fr VARCHAR(20) AFTER gender; Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [employees]> UPDATE gender SET gender_fr='MASCULIN' WHERE code='M'; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [employees]> UPDATE gender SET gender_fr='FÉMININ' WHERE code='F'; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [employees]> ALTER TABLE gender CHANGE COLUMN gender gender_en VARCHAR(20); Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [employees]> select * from gender; +------+-----------+-----------+ | code | gender_en | gender_fr | +------+-----------+-----------+ | F | FEMALE | FÉMININ | | M | MALE | MASCULIN | +------+-----------+-----------+ 2 rows in set (0.00 sec)
您还可以创建一个表并填入来自一个 SELECT 的数据。清单 31 展示了一个创建女性员工表的简单示例。
MariaDB [employees]> create table female_employees
select * from employees where gender='F';
Query OK, 120051 rows affected (3.22 sec)
Records: 120051 Duplicates: 0 Warnings: 0
当您以这种方式创建表时，新表可能未继承来源表的所有属性。比较 清单 32 中的表描述。您可以注意到，female_employees 表没有键。
MariaDB [employees]> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
MariaDB [employees]> describe female_employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
如果您使用现有数据来创建表，还需要考虑如何计划未来将该数据保存在两个同步的表中。在这个示例中，您可能想要一个 VIEW，它使您能够像表一样使用 SELECT 的结果。数据保留在一个或多个基础表中，而且仅需要更新一个副本。清单 33 展示了如何创建男性员工的视图。请注意视图名称与 SELECT 之间的单词 AS。
MariaDB [employees]> CREATE VIEW male_employees AS
select * from employees where gender='M';
Query OK, 0 rows affected (0.05 sec)
MariaDB [employees]> select * from male_employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 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 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
如果您想知道数据库中的哪些表是真正的视图，一个命令可以帮助您。不同的数据库使用不同的命令。在 MariaDB 中，使用：
show full tables
创建表时，您可以组合您的列规范和 SELECT 的结果。作为最后一个创建表的示例， 清单 34 展示了创建一种包含使用 CHAR(1) 字段而不是枚举值来表 gender 的 employees 表的副本的方式，以及如何使用 IF 语句设置此值。
MariaDB [employees]> CREATE TABLE employees_new (
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),
hire_date date NOT NULL,
PRIMARY KEY (emp_no)
) select
emp_no, birth_date, first_name, last_name,
IF(gender = 'M', 'M', 'F') as gender,
hire_date from employees;
Query OK, 300024 rows affected (5.92 sec)
Records: 300024 Duplicates: 0 Warnings: 0
MariaDB [employees]> describe employees_new;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | char(1) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
外键的创建和维护不属于这篇介绍性教程的讨论范围。但是，您仍然可以使用 JOIN 联接 employees_new 和 gender 表来执行查询，比如我在 清单 35 中演示的查询。
MariaDB [employees]> select e.emp_no, e.first_name, e.last_name, e.gender,
g.gender_en, g.gender_fr
from employees_new e join gender g on e.gender=g.code
limit 5;
+--------+------------+-----------+--------+-----------+-----------+
| emp_no | first_name | last_name | gender | gender_en | gender_fr |
+--------+------------+-----------+--------+-----------+-----------+
| 10001 | Georgi | Facello | M | MALE | MASCULIN |
| 10002 | Bezalel | Simmel | F | FEMALE | FÉMININ |
| 10003 | Parto | Bamford | M | MALE | MASCULIN |
| 10004 | Chirstian | Koblick | M | MALE | MASCULIN |
| 10005 | Kyoichi | Maliniak | M | MALE | MASCULIN |
+--------+------------+-----------+--------+-----------+-----------+
5 rows in set (0.00 sec)
我展示了如何使用 DELETE 从表中删除行。如果您想要删除一列而不是一行，则需要使用 ALTER TABLE。例如，如果您希望从 female_employees 表删除 gender 列，可以使用 清单 36 中演示的命令。
MariaDB [employees]> ALTER TABLE female_employees DROP COLUMN gender; Query OK, 0 rows affected (4.32 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [employees]> describe female_employees; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | emp_no | int(11) | NO | | NULL | | | birth_date | date | NO | | NULL | | | first_name | varchar(14) | NO | | NULL | | | last_name | varchar(16) | NO | | NULL | | | hire_date | date | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
要丢弃 female_employees 表和 male_employees 视图，可以使用 DROP TABLE 和 DROP VIEW 命令，如 清单 37 所示。
MariaDB [employees]> drop table female_employees; Query OK, 0 rows affected (0.14 sec) MariaDB [employees]> drop view male_employees; Query OK, 0 rows affected (0.00 sec)
如果您想删除整个 employees 数据库和它的所有表，可以使用 DROP DATABASE 命令，如 清单 38 所示。
MariaDB [employees]> drop database employees; Query OK, 10 rows affected (0.92 sec)
对 SQL 的简要介绍到此就结束了。我只简单介绍了一个大型项目的很少的一部分，您可以使用本教程作为起点来进一步探索。
免费提供最新Linux技术教程书 籍，为开源技术爱好者努力做得更多更好：https://www.linuxprobe.com/
