「SQL 数据分析系列」2. 创建和使用数据库

写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

第二章创建和使用数据库

本章内容包括创建第一个数据库以及本书中示例所用的表和相关数据，你还将了解各种数据类型以及如何在创建表时使用它们。因为本书中的示例是针对 MySQL 数据库执行的，所以本章会偏向使用 MySQL 的特性和语法，但大多数概念对其他的数据库服务器也适用。

创建 MySQL 数据库

如果你想使用本书中示例所用的数据，你有两个选择：

• 下载并安装 MySQL server 8.0 版（或更高版本），然后从https://dev.mysql.com/doc/index-other.html下载 Sakila 示例数据库；

• 跳转到https://www.katacoda.com/mysql-db-sandbox/scenarios/mysql-sandbox访问 MySQL 沙箱，在 MySQL 实例中加载 Sakila 示例数据库。要使用这种方法，你必须新建一个 Katacoda 帐户（免费的），然后单击 Start Scenario 按钮。

如果你选择第二种方法，那么一旦启动场景，就会安装并启动 MySQL 服务器，然后加载 Sakila 模式和数据。启动以及加载完毕之后，会出现一个标准的 mysql>提示符，然后就可以开始查询示例数据库了。这当然是最简单的选择，我认为大多数读者都会选择这个方法。如果你觉得使用第二种方法很不错并且打算使用这种方法，那么可以跳到下一节了。

如果你希望拥有自己的数据副本，并且希望所做的任何更改都是永久性的，或者如果你就是想在自己的计算机上安装 MySQL 服务器，那么你可能偏向于第一个选择。你也可以选择使用托管在 Amazon Web Services 或 Google Cloud 等环境中的 MySQL 服务器。无论你是在本机下载还是在云端托管，你都需要自己执行安装和配置，这里不予赘述。一旦数据库可用（假设你已经下载并且安装和配置完毕），就需要按照以下几个步骤加载 Sakila 示例数据库。

首先，启动 mysql 命令行客户端并输入密码，然后执行以下步骤：

1. 前往 https://dev.mysql.com/doc/index-other.html 下载 Example Databases 下的“sakila database”文件。

2. 将文件放在本地目录中，例如 C:\temp\sakila-db（为下面两个步骤做准备）。

3. 键入 source c:\temp\sakila db\sakila-schema.sql，然后按 Enter 键。

4. 键入 source c:\temp\sakila db\sakila-data.sql，然后按 Enter 键。

5. 现在你应该有一个可用数据库了，其中包括了本书中示例所需的所有数据。

注意：Sakila 示例数据库由 MySQL 提供，并通过 New BSD 获得许可。Sakila 包含一个虚构的电影租赁公司的数据，并包含诸如 store, inventory, film, customer,和 payment 等表。虽然实际上的电影租赁商店基本上已经是过去时了，但是你可以想象一下，通过忽略 staff 和 address 表，将 store 重命名为 streaming_service，然后就可以把这个电影租赁公司当成电影流媒体公司了。但是，这本书中的例子并不会变，还是以电影租赁公司为例。

使用 mysql 命令行工具

除非是使用临时数据库会话（上一节中的第二个选择），否则需要启动 mysql 命令行工具才能与数据库交互。为此，你需要打开 Windows 或 Unix shell 并执行 mysql 程序。例如，如果你使用 root 帐户登录，需要执行以下操作：

mysql -u root -p;

然后你需要输入密码，登陆成功之后你会看到提示符 mysql>。要查看所有可用的数据库，可以使用以下命令：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

+--------------------+

5 rows in set (0.01 sec)

由于你将使用 Sakila 数据库，因此需要通过 use 命令指定要使用的数据库：

mysql> use sakila;

Database changed

无论何时调用 mysql 命令行工具，都可以指定要使用的用户名和数据库，如下所示：

mysql -u root -p sakila;

这样就不用在登录之后再键入 use sakila;使用数据库了。现在你已经建立了会话并指定了数据库，接下来就可以使用 SQL 语句并查看结果了。例如，如果你想知道当前日期和时间，可以使用以下查询：

mysql> SELECT now();

+---------------------+

| now() |

+---------------------+

| 2019-04-04 20:44:26 |

+---------------------+

1 row in set (0.01 sec)

函数 now()是一个内置的 MySQL 函数，返回当前日期和时间。如你所见，mysql 命令行工具将查询结果格式化为以+、-和|字符为边界的矩形。结果显示结束之后（在本例中，只有一行结果），mysql 命令行工具显示返回了多少行，以及 SQL 语句执行所用的时间。

关于从句缺失

对于某些数据库服务器，使用查询语句的时候必须包括 from 子句，并且至少要指明一个表名，否则无法正常查询，像广泛使用的 Oracle 数据库就是这样的。对于只需要调用函数的情况，Oracle 提供了一个名为 dual 的表，该表由一个名为 dummy 的列组成，该列包含一行数据。为了与 Oracle 数据库兼容，MySQL 也提供了 dual 表。因此，用于前面查询当前日期和时间的语句可以写成：

mysql> SELECT now()  FROM dual;

+---------------------+

| now() |

+---------------------+

| 2019-04-04 20:44:26 |

+---------------------+

1 row in set (0.01 sec)

如果你不使用 Oracle 并且也不需要与它兼容，那么可以完全忽略 dual 表，只使用不带有 from 子句的 select 语句即可。

使用 mysql 命令行工具后，只需键入 quit;或 exit;即可返回 Unix 或 Windows command shell。

MySQL 数据类型

一般来说，所有流行的数据库服务器都能够存储相同类型的数据，比如字符串、日期和数字。它们的不同之处通常在于一些特殊的数据类型，比如 XML 和 JSON 文档或空间数据。由于这是一本关于 SQL 的入门书，而且你使用的数据列中 98%都是简单的数据类型，因此本章仅介绍字符型、日期型和数值型的数据类型。第十八章将探讨如何使用 SQL 查询 JSON 文档。

字符型数据

字符数据可以存储为固定长度或可变长度的字符串，其不同之处在于：固定长度的字符串用空格向右填充，使得占用的字节数相同，而可变长度的字符串不需要向右填充，并且字节数是可变的。定义字符列的时候，必须指定该列能够存放字符串的最大长度。例如，如果要存储长度不超过 20 个字符的字符串，可以使用以下定义之一：

char(20) /* fixed-length */

varchar(20) /* variable-length */

char 列的最大长度当前为 255 字节，而 varchar 列的最大长度可达 65535 字节。如果你要存储更长的字符串（如电子邮件、XML 文档等），那么就需要使用文本类型（mediumtext 和 longtext），后面会介绍这些内容。一般来说，当要存储在列中的所有字符串是等长时（如州名的缩写），应该使用 char 类型。当要存储在列中的字符串长度不同时，使用 varchar 类型。char 和 varchar 在所有主流数据库服务器中的使用方法都类似。

注意：使用 Oracle 数据库的时候，varchar 的使用方式不同。Oracle 用户在定义可变长度的字符列时应使用 varchar2 类型

字符集

对于拉丁语系的语言（如英语）字符数足够少，因此只需要一个字节来存储每个字符。其他语言（如日语和韩语）则包含大量字符，每个字符需要多个字节来存储，因此这种字符集被称为多字节字符集。

MySQL 可以使用各种字符集（包括单字节和多字节）存储数据。要查看服务器所支持的字符集，可以使用 show 命令，如下所示：

mysql> SHOW CHARACTER SET;

+----------+---------------------------------+---------------------+--------+

| Charset | Description | Default collation | Maxlen |

+----------+---------------------------------+---------------------+--------+

| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |

| ascii | US ASCII | ascii_general_ci | 1 |

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| binary | Binary pseudo charset | binary | 1 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

| euckr | EUC-KR Korean | euckr_korean_ci | 2 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |

| greek | ISO 8859-7 Greek | greek_general_ci | 1 |

| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |

| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |

| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |

| latin1 | cp1252 West European | latin1_swedish_ci | 1 |

| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |

| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |

| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |

| macce | Mac Central European | macce_general_ci | 1 |

| macroman | Mac West European | macroman_general_ci | 1 |

| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |

+----------+---------------------------------+---------------------+--------+

41 rows in set (0.04 sec)

如果第四列 maxlen 中的值大于 1，则表明该字符集是多字节字符集。

在 MySQL 服务器的早期版本中，latin1 字符集被视作默认字符集，但版本 8 默认使用 utf8mb4 字符集。你可以选择为数据库中的每个字符列使用不同的字符集，甚至可以在同一个表中存储不同的字符集数据。要在定义列时选择非默认字符集，只需在类型定义后加上一个系统支持的字符集，如下所示：

varchar(20) character set latin1

MySQL 中，还可以为整个数据库设置默认字符集：

create database european_sales character set latin1;

对一本介绍性的书来说，介绍这些关于字符集的内容已经够多了，但是实际上关于国际化的主题中包含的内容更多。如果你要处理多个不熟悉的字符集，那么可能需要学习一本专业书籍，比如 Jukka Korpela 的 Unicode Explained: Internationalize Documents, Programs, and Web Sites (O’Reilly)

文本数据

如果需要存储可能超过 varchar 列的限制的数据（64KB 以上），则需要使用文本类型。

下表（2-1）显示了可用的文本类型及其最大长度：

 使用文本类型时，应注意以下几点：

• 如果装载到文本列中的数据超过该类型的最大大小，数据将被截断。

• 将数据装载到文本列中时，不会删除尾部的空格。

• 使用文本列进行排序或分组时，仅使用前 1024 个字节，但必要时可放宽该限制。

• 这些不同的文本类型是 MySQL 独有的。对于大字符数据，SQLServer 只有一种文本类型（text 类型），而 DB2 和 Oracle 使用一种称为 clob 的数据类型（Character Large Object）。

• 既然 MySQL 允许 varchar 列最多容纳 65535 字节（在版本 4 中限制为 255 字节），那么一般不需要特别使用 tinytext 或 text 类型了。

如果你要创建一个用于存储自由格式数据的列，例如一个 notes 列来存储有关客户与公司的客服部门交互的数据，那么 varchar 可能就足够了。但是，如果要存储文档，则应选择 mediumtext 或 longtext 类型。

注意：Oracle 数据库中 char 列最多容纳 2000 字节，varchar2 列最多容纳 4000 字节。对于较大的文档，可以使用 clob 类型。对于 char 和 varchar 数据，SQL Server 最多可以处理 8000 个字节，但是在定义为 varchar(max)的列中最多可以存储 2GB 的数据。

数值型数据

尽管使用叫“numeric”的数值数据类型似乎是合理的，但实际上存在好几种不同的数值数据类型，它们反映了使用数字的各种方式，如下所示：

• 某列需要指示客户订单是否已发送

 这种类型的列可以被设为 Boolean，将包含一个 0 或 1，分别表示 false 和 true。

• 系统为交易表生成的主键

 这列数据通常从 1 开始，每次自增 1，最后的数字可能会非常大。

• 顾客电子购物篮的商品编号

 这类列的值应该是一个正整数，范围从 1 到 200（假设购物狂的购物篮能容纳最多的物品数量是 200，当然也可能更大或更小）。

• 电路板钻床的位置数据

 高精度的科学或制造数据通常要求精确到小数点后 8 位。

为了处理这些类型的数据（以及更多类型的数据），MySQL 提供几种不同的数值数据类型。最常用的是用来存储整数即 integers 的类型。在指定类型的时候，还可以指定数据是 unsigned 的，以向服务器指明存储这列的所有数据都将大于或等于零。下表（2-2）展示了用于存储整数的五种不同数据类型：

当你使用其中一个整数类型创建列时，MySQL 将为存储数据分配适当大小的空间，范围从 tinyint 的一个字节到 bigint 的八个字节。因此在选择数据类型的时候，确保能够容纳你所预期的最大数据即可，以免白白浪费存储空间。

对于浮点数（如 3.1415927），可以从下表（2-3）所示的数字类型中选择：

 使用浮点类型时，可以指定精度 precision（小数点左侧到右侧允许的总位数）和有效位 scale（小数点右侧允许的位数），不过这并不是必须的。上面两个值在上表（2-3）中表示为 p 和 s。注意：为浮点类型的列指定精度和有效位的时候，如果位数超过该列定义的小数位数和精度，则存储在该列中的数据将被四舍五入。例如，定义为 float(4,2)的列总共会存储四位数字，两位在小数点左侧，两位在小数点右侧。因此，这样的列可以很好地存储 27.44 和 8.19 这两个数字，但是存储 17.8675 将被四舍五入到 17.87，存储 178.375 则会产生错误。

与整数类型一样，浮点列可以被定义为无符号的（unsigned），但这只能防止列中存放负数，而无法改变列所能存储数据的范围。

时间数据

除了字符串和数字，你肯定还会使用有关日期和时间的信息。这种类型的数据称为时间型数据，下面是数据库中有关时间型数据的一些例子：

• 预计未来发生的特定事件的日期（例如运送客户订单）；

• 客户订单的实际发货日期；

• 用户修改表中某行的日期和时间；

• 员工的出生日期；

• 与数据仓库的 yearly_sales 表中，每行对应的年份；

• 在汽车装配线上完成流水线所需的时间。

MySQL 能为上述所有情况提供合适的数据类型。MySQL 支持的时间数据类型如下表（2-4）所示：

 虽然数据库服务器能够以各种方式存储时间数据，但格式字符串（上表 2-4 的第二列）的目的是指定检索时如何显示数据，以及插入或更新时间列时需要提供日期字符串的格式。因此，如果要使用默认格式为 YYYY-MM-DD 的 date 列存储日期 2020 年 3 月 23 日则要使用字符串'2020-03-23'。第七章全面探讨了时间数据是如何构造和显示的。

datetime、timestamp 和 time 类型还允许小数点后 6 位（微秒）的小数秒，使用这些数据类型之一定义列时，可以提供 0 到 6 之间的值；例如，指定 datetime(2)将允许时间值包含百分之一秒。

注意：每个数据库服务器针对时间列允许的日期范围各不相同。Oracle 数据库接受从公元前 4712 年到公元 9999 年的日期，而 SQL Server 只能处理从公元 1753 年到公元 9999 年的日期（除非用的是 SQL Server 2008 的 datetime2 数据类型，它允许从公元 1 年到公元 9999 年的日期）。MySQL 介于 Oracle 和 SQL Server 之间，可以存储从公元 1000 年到公元 9999 年的日期。虽然对于大多数处理当前和未来事件的系统来说，这些差别可能并不重要，但存放历史时期的时候就请务必记住这些差别了。

下面介绍如何使用各种时间类型来实现前面的例子：

• 用于保存客户订单的预期未来发货日期和员工出生日期的列将使用 date 类型，因为未来发货时间精确到秒是不现实的，此外也没有必要知道一个人出生的具体时间。

• 保存客户订单实际发货时间信息的列可以用 datetime 类型，因为不仅要记录发货日期，还要记录发货时间。

• 记录用户修改表中某行的日期和时间，可以用使用 timestamp 类型。timestamp 类型与 datetime 类型（包括年、月、日、小时、分钟、秒）保存的信息相同，但是当在表中新增一行或修改一行时，MySQL 服务器将自动为 timestamp 列增加当前日期/时间信息。

• 仅包含年份数据的列将使用 year 类型。

• 保存完成任务所需时长数据的列将使用 time 类型。对于这种类型的数据，无需存储日期信息，因为你只关心完成任务所需的时/分/秒数。该类信息可以使用两个 datetime 列来存储（一个用于存放任务开始日期/时间，另一个用于存放任务完成日期/时间），两者的差值就是时长，但是很显然，使用单个 time 列会更简单。

第七章会探讨如何使用这些时间数据类型。

创建表

现在你已经了解了 MySQL 数据库中可能存储的数据类型，所以是时候看看如何在表定义中使用这些类型了。让我们先定义一个表来保存个人信息。

第一步：设计

开始设计表格之前，最好是头脑风暴一下，以确定需要包括的信息。以下是我在思考了一会儿之后想出的用于描述个人信息的项：

• 姓名

• 眼睛颜色

• 出生日期

• 地址

• 喜爱的食物

当然这些信息并不全面，但是这里已经够用了。下一步是分配列名和数据类型。下表（2-6）是我的初始设计：

name、address 和 favorite_foods 列的类型为 varchar，允许任何格式的数据条目。eye_color 列限制两个字符只应为 BR、BL 或 GR 中的一个。birth_date 列的类型为 date，因为不需要包括具体的时间信息。

第二步：完善

在第一章中，我们介绍过规范化的概念——规范化是确保数据库设计中没有重复（外键除外）或复合列的过程。再次查看 person 表中的列时，会发现以下问题：

• name 列实际上是一个由名字和姓氏组成的复合对象。

• 由于多个人可以拥有相同的姓名、眼睛颜色、出生日期等，因此 person 表中没有保证唯一性的列。

• address 列也是一个复合对象，由街道、城市、州/省、国家和邮政编码组成。

• favorite_foods 列是一个包含零个、一个或多个独立条目的列表，所以最好为这些数据创建一个单独的表，然后在其中包含 person 表的外键，这样就可以知道某个特定食物归属的人员了。

考虑到以上问题，下表（2-7）给出了 person 表的规范化版本：

既然 person 表有主键（person_id）来保证唯一性，那么下一步就是构建一个 favorite_food 表，其中包含指向 person 表的外键，如下表（2-8）所示：

person_id 和 food 列构成 favorite_food 表的主键，person_id 列也是 person 表的外键。

这些设计就够了吗？

将 favorite_foods 列从 person 表中移出绝对是个好主意，但这样就够了吗？例如，如果一个人把 pasta 列为最喜欢的食物，而另一个人却把 spaghetti 列为最喜欢的食物，它们指的都是意大利面，那该怎么办？它们是一样的吗？为了防止此问题发生，你可以让人们从列表中选择他们最喜欢的食物，在这种情况下，应该创建一个包含 food_id 和 food_name 列的 food 表，然后修改 favorite_food 包含 food 表的外键。虽然这种设计是完全规范化的，但若你只是想存储用户输入的值，那么可以保持原有的表设计。

第三步：构建 SQL 语句

现在已经完成了这两个表的设计，它们包括关于人员信息以及他们喜欢的食物的信息，下一步是生成 SQL 语句来在数据库中创建表。下面是创建 person 表的语句：

CREATE TABLE person

 (person_id SMALLINT UNSIGNED, 

fname VARCHAR(20),

 lname VARCHAR(20), 

eye_color CHAR(2), 

birth_date DATE, street VARCHAR(30), 

city VARCHAR(20), 

state VARCHAR(20), 

country VARCHAR(20), 

postal_code VARCHAR(20),

CONSTRAINT pk_person PRIMARY KEY (person_id) );

除了最后一项，这条语句中的其他所有内容应该都很好理解。在定义表时，需要告诉数据库服务器哪些列将用作表的主键，可以通过在表上创建约束（constraint）实现这一点。可以向表定义中添加多种类型的表约束。上述语句的约束是主键约束，它在 person_id 列上创建，并命名为 pk_person。

有关约束，对于 person 表而言，还有另一种类型的约束也很有用。在前面的表（2-6）中，第三列只接受特定的值（比如 eye_color 列的“BR”和“BL”），此时可以给它增加一个检查约束，用以限制该列存放的值。MySQL 允许将检查约束附加到列定义，如下所示：

eye_color CHAR(2) CHECK (eye_color IN ('BR','BL','GR')),

虽然检查约束在大多数数据库服务器上可以预期运行，但对 MySQL 服务器来说，它虽允许定义检查约束，但并不强制执行。其实 MySQL 确实有提供另一种名为 enum 的字符数据类型，它将检查约束合并到数据类型定义中。下面是用这种方法定义 eye_color 列的语句：

eye_color ENUM('BR','BL','GR'),

下面是 person 表的重定义，其中 eye_color 列以 enum 作为其数据类型：

CREATE TABLE person  

(person_id SMALLINT UNSIGNED,  fname VARCHAR(20),

 lname VARCHAR(20),  

eye_color ENUM('BR','BL','GR'),

 birth_date DATE,

 street VARCHAR(30),

 city VARCHAR(20),  

state VARCHAR(20),  

country VARCHAR(20),  

postal_code VARCHAR(20),  

CONSTRAINT pk_person PRIMARY KEY (person_id)  );

在本章后面会介绍向列中添加违反检查约束（或者在 MySQL 中，违反其枚举值）的数据会发生什么事情。

现在可以使用 mysql 命令行工具运行 create table 语句了。如下所示：

mysql> CREATE TABLE person 

-> (person_id SMALLINT UNSIGNED,

 -> fname VARCHAR(20), 

-> lname VARCHAR(20), 

-> eye_color ENUM('BR','BL','GR'), 

-> birth_date DATE, 

-> street VARCHAR(30), 

-> city VARCHAR(20), 

-> state VARCHAR(20), 

-> country VARCHAR(20),

 -> postal_code VARCHAR(20), 

-> CONSTRAINT pk_person PRIMARY KEY (person_id) 

-> );Query OK,

0 rows affected (0.37 sec)

在处理完 create table 语句之后，MySQL 服务器返回消息“Query OK，0 rows affected”，表示该语句没有语法错误。

如果想确认 person 表的存在，可以使用 describe 命令（简称 desc）查看表定义：

mysql> desc person;

+-------------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------------------+------+-----+---------+-------+

| person_id | smallint(5) unsigned | NO | PRI | NULL | |

| fname | varchar(20) | YES | | NULL | |

| lname | varchar(20) | YES | | NULL | |

| eye_color | enum('BR','BL','GR') | YES | | NULL | |

| birth_date | date | YES | | NULL | |

| street | varchar(30) | YES | | NULL | |

| city | varchar(20) | YES | | NULL | |

| state | varchar(20) | YES | | NULL | |

| country | varchar(20) | YES | | NULL | |

| postal_code | varchar(20) | YES | | NULL | |

+-------------+----------------------+------+-----+---------+-------+

10 rows in set (0.00 sec)

describe 输出的第一列和第二列的含义很好理解，第三列显示将数据插入表中时是否可以省略特定列。现在我暂时不想深入探讨这个话题（简要介绍请参见下面的“什么是空值”），后面在第四章中，我们会全面探讨这个问题。第四列显示这列是否是键值（主键或外键），这个例子中，person_id 列被标记为主键。第五列显示在向表中插入数据时，如果忽略某列，是否会用默认值填充该列。第六列（Extra）显示该列附加的说明信息。

什么是空值？

在某些情况下，插入数据的时候，可能无法为表中的某列提供具体的值。例如，在新增有关新客户订单数据时，还不能确定 ship_date 列。在本例中，该列被设置为 null（注意，我并不是说它等于“null”这个字符串），这表示没有值。null 被用于无法赋值的各种情况，例如：

• 不适用

• 未知

• 空集

在设计表时，可以指定哪些列允许为 null（默认），哪些列不允许为 null（通过在类型定义后添加关键字 not null 来指定）。

现在已经创建完 person 表，下一步是创建 favorite_food 表：

mysql> CREATE TABLE favorite_food 

-> (person_id SMALLINT UNSIGNED, 

-> food VARCHAR(20),

 -> CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food), 

-> CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)

 -> REFERENCES person (person_id) 

-> );Query OK,

0 rows affected (0.10 sec)

上述语句与 person 表的 create table 语句非常相似，但是也有不同之处，如下：

• 由于一个人可以有多种喜爱的食物（这也是创建此表的主要原因），因此要保证表中数据的唯一性，仅仅靠 person_id 列是不行的。故而该表有两列主键：person_id 和 food。

• favorite_food 表包含另一类型的约束，称为外键约束，它限制 favorite_food 表中 person_id 列的值只能来自 person 表。加了这个约束之后，如果 person 表中还没有 person_id 为 27 的行，那么在 favorite_food 表中添加一行表示 person_id 为 27 并且喜欢的食物比萨的数据是不可能的。

注意：如果在第一次创建表时忘记创建外键约束，可以稍后通过 alter table 语句添加。

执行完 create table 语句后，使用 describe 命令显示以下结果：

mysql> desc favorite_food;

+-----------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+----------------------+------+-----+---------+-------+

| person_id | smallint(5) unsigned | NO | PRI | NULL | |

| food | varchar(20) | NO | PRI | NULL | |

+-----------+----------------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

现在我们已经将表创建完毕，接下来就要向其中添加一些数据了。

操作与修改表

准备好 person 和 favorite_food 表后，可以开始研究四个 SQL 数据语句（insert、update、delete 和 select）了。

插入数据

由于 person 和 favorite_food 表中还没有任何数据，因此在四种数据语句中，我们先研究 insert 语句。insert 语句有三个主要组成部分：

• 要添加数据的表的名称

• 要使用的列的名称

• 用于插入列的值

其实并不需要为表中的每一列提供数据（除非表中的所有列都定义为 not null）。在某些情况下，初始 insert 语句中可能并不包含某列的值，这部分将在之后通过 update 语句获得更新。还有一些情况下，某列的值可能始终为 null（例如在发货前取消的客户订单，ship_date 列就不用再赋值了）。

生成数字型主键数据

在将数据插入 person 表之前，最好先讨论一下数字型主键的生成机制。除了随机选择数字外，还可以选择以下方式：

• 查看表中当前主键的最大值，在此基础之上加 1；

• 让数据库服务器自动生成。

虽然第一种方式看起来似乎很有效，但在多用户环境中它可能会出现问题，因为两个用户可能同时访问表并生成两个相同的值作为主键。实际上，如今市面上所有的数据库服务器都提供了一种安全、健壮的方法来生成数字型主键。在一些服务器中，比如 Oracle 数据库就使用了一个单独的模式（schema）对象，称为序列号（sequence）；而在 MySQL 中，为主键列启用自动递增（auto-increment）功能即可。通常，在创建表的时候就应该执行该操作。现在再介绍一下另一种方案语句——alter table，它的功能是修改现有表的定义：

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

注意：如果在数据库中运行这些语句，则应首先禁用 favorite_food 表上的外键约束，在完成表的重定义之后，再启用约束。语句如下：

set foreign_key_checks=0;

ALTER TABLE person  MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; set foreign_key_checks=1;

该语句实质上重新定义了 person 表中的 person_id 列。现在再使用 describe 命令，可以看到 person_id 的 Extra 列下列出的自增特性：

mysql> DESC person;

+-------------+------------------------+------+-----+---------+-----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+------------------------+------+-----+---------+-----------------+

| person_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| . | | | | | |

| . | | | | | |

| . | | | | | |

在向 person 表中插入数据时，只需为 person_id 列提供一个 null 值，MySQL 将自动向该列提供下一个可用的主键数字（默认情况下，MySQL 是从 1 开始递增的）。

insert 语句

现在一切就绪，所以可以向表中添加一些数据了。下面的语句在 person 表中为 William Turner 创建一行数据：

mysql> INSERT INTO person  

-> (person_id, fname, lname, eye_color, birth_date)  

-> VALUES (null, 'William','Turner', 'BR', '1972-05-27');

Query OK, 1 row affected (0.22 sec)

运行结果“Query OK, 1 row affected”表示该语句语法正确，并且有一行被添加到了数据库中（因为它是 insert 语句）。可以通过 select 语句查看刚刚添加到表中的这条数据：

mysql> SELECT person_id, fname, lname, birth_date  

-> FROM person;

+-----------+---------+--------+------------+

| person_id | fname | lname | birth_date |

+-----------+---------+--------+------------+

| 1 | William | Turner | 1972-05-27 |

+-----------+---------+--------+------------+

1 row in set (0.06 sec)

 如你所见，MySQL 服务器为主键生成的值为 1。因为 person 表中只有一行，所以我省略了查询条件以获取表中的所有行。但是，如果表中有多行，则可以添加 where 子句进行过滤，以指定需要获取的数据。检索 person_id 为 1 的行：

mysql> SELECT person_id, fname, lname, birth_date    

-> FROM person  

-> WHERE person_id = 1;

  +-----------+---------+--------+------------+

| person_id | fname | lname | birth_date |

  +-----------+---------+--------+------------+

| 1 | William | Turner | 1972-05-27 |  

 +-----------+---------+--------+------------+

1 row in set (0.00 sec)

 该查询指定了特定主键值，其实还可以指定表中的任意列，比如下面的查询就是用于查找 lname 列为 Turner 的行：

mysql> SELECT person_id, fname, lname, birth_date  

  -> FROM person

 -> WHERE lname = 'Turner';

  +-----------+---------+--------+------------+

| person_id | fname | lname | birth_date |

  +-----------+---------+--------+------------+

| 1 | William | Turner | 1972-05-27 |

  +-----------+---------+--------+------------+

1 row in set (0.00 sec)

 注意关于 insert 语句还有几点值得一提：

• 没有为任何地址列提供值。因为这些列允许空值，所以没关系。

• 为 birth_date 列提供的值是字符串。只要符合表 2-4 所示的格式，MySQL 就会将字符串转换为日期类型。

• 列名和提供的值必须在数字和类型上对应。如果表有七列但是插入的时候只提供六个值，或者提供的值无法转换为相应列的相应数据类型，则会产生错误。

William Turner 还提供了他最喜欢的三种食物的信息，因此还需要三条 insert 语句来存储他的食物偏好：

mysql> INSERT INTO favorite_food (person_id, food)

 -> VALUES (1, 'pizza');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO favorite_food (person_id, food) 

-> VALUES (1, 'cookies');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO favorite_food (person_id, food) 

-> VALUES (1, 'nachos');

Query OK, 1 row affected (0.01 sec)

下面的查询使用 order by 子句按字母顺序排列 William 最喜欢的食物：

mysql> SELECT food  

-> FROM favorite_food

 -> WHERE person_id = 1  

-> ORDER BY food;

+---------+

| food |

+---------+

| cookies | | nachos | | pizza |

+---------+

3 rows in set (0.02 sec)

order by 子句告诉服务器如何对查询返回的数据进行排序。如果不用 order by 子句，就不能保证表中的数据获取的顺序。

William 一个人可能太孤单，所以你可以再次执行 insert 语句将 Susan Smith 添加到 person 表：

mysql> INSERT INTO person

 -> (person_id, fname, lname, eye_color, birth_date,

 -> street, city, state, country, postal_code)  

-> VALUES (null, 'Susan','Smith', 'BL', '1975-11-02',

 -> '23 Maple St.', 'Arlington', 'VA', 'USA', '20220');  

Query OK, 1 row affected (0.01 sec)

由于 Susan 提供了她的地址，上面的 insert 语句比之前插入 William 数据用到的语句多五列。如果再次查询该表，可以看到 Susan 所在行的主键值被赋值为 2：

mysql> SELECT person_id, fname, lname, birth_date  

 -> FROM person;

+-----------+---------+--------+------------+ 

 | person_id | fname | lname | birth_date |

+-----------+---------+--------+------------+ 

 | 1 | William | Turner | 1972-05-27 || 2 | Susan | Smith | 1975-11-02 |  

+-----------+---------+--------+------------+

2 rows in set (0.00 sec)

可以获取 XML 格式的数据吗？

如果你使用 XML 数据，就会很高兴地看到大多数数据库服务器提供了一种从查询结果中生成 XML 输出的简单方法。例如，对于 MySQL，可以在调用 mysql 工具时使用--xml 选项，如此一来，所有的输出都将自动使用 xml 格式化。下面演示如何获取 XML 文档格式的 favorite_food 数据：

C:\database> mysql -u lrngsql -p --xml bank  

Enter password: xxxxxx

Welcome to the MySQL Monitor...  

Mysql> SELECT * FROM favorite_food;

<?xml version="1.0"?>  

<resultset statement="select * from favorite_food"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   

<row>

 <field name="person_id">1</field>   

<field name="food">cookies</field> 

</row> 

  <row>

 <field name="person_id">1</field> 

  <field name="food">nachos</field>

 </row>  

 <row> <field name="person_id">1</field>   

<field name="food">pizza</field> 

</row>  

</resultset>

3 rows in set (0.00 sec)

使用 SQL Server，不需要配置命令行工具，只需在每个查询的末尾添加 for xml 子句，如下所示：

SELECT * FROM favorite_food  FOR XML AUTO, ELEMENTS

更新数据

当 William Turner 的数据被添加到表中时，insert 语句中并没有提供地址列的数据。下面演示如何通过 update 语句更新这些列的数据：

mysql> UPDATE person  

-> SET street = '1225 Tremont St.',

 -> city = 'Boston',

 -> state = 'MA',

 -> country = 'USA',  

-> postal_code = '02138' 

 -> WHERE person_id = 1;

Query OK,

1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0

服务器响应了两行消息：“Rows matched: 1”项表示 where 子句中的条件与表中的一行匹配，“Changed: 1”项表示表中的一行数据已被修改。由于 where 子句指定了 William 所在行的主键，所以修改的数据肯定与你所预期的一样。

根据 where 子句中的条件，还可以使用单个语句修改多行数据。例如，考虑如下 where 子句：

WHERE person_id < 10

因为 William 和 Susan 的 person_id 值都小于 10，所以它们的两行都将被修改。如果省略 where 子句，那么 update 语句将修改表中的每一行数据。

删除数据

现在看来 William 和 Susan 相处得不太好，所以他们两人中得有一人离开。既然 William 是第一个来的，那么我们可以删除 Susan 的数据，使用 delete 语句如下：

mysql> DELETE FROM person  

-> WHERE person_id = 2;

Query OK,

1 row affected (0.01 sec)

同样，主键用于定位我们感兴趣的行，因此表中只会有一行数据被删除。与 update 语句一样，我们也可以根据 where 子句中的条件删除多行数据，如果省略 where 子句，则将删除表中所有行。

导致错误的语句情况

到目前为止，本章中演示的所有 SQL 数据语句都是符合标准格式并且能够正常运行的。然而，根据 person 和 favorite_food 表的表定义，在插入或修改数据时可能会出现很多运行错误。本节主要展示可能遇到的一些常见错误情况以及 MySQL 服务器是如何响应它们的。

主键不唯一

由于表定义创建了主键约束，所以 MySQL 确保不会将重复的主键值插入表中。下一条语句忽略 person_id 列的自增特性，并在 person 表中创建 person_id 为 1 的另一行数据：

mysql> INSERT INTO person  

-> (person_id, fname, lname, eye_color, birth_date)

 -> VALUES (1, 'Charles','Fulton', 'GR', '1968-01-15');

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

对于当前的方案对象来说，你完全可以创建两个具有相同姓名、地址、出生日期等条目的数据行，但是注意前提是它们的主键不同，也即 person_id 列的值不同。

不存在的外键

favorite_food 表的表定义在 person_id 列上创建了外键约束，此约束确保 favorite_food 表中所输入 person_id 列的值都在 person 表中存在。下面演示违背该约束创建新行的情况：

mysql> INSERT INTO favorite_food (person_id, food)  

-> VALUES (999, 'lasagna');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('sakila'.'favorite_food', CONSTRAINT 'fk_fav_food_person_id' FOREIGN KEY ('person_id') REFERENCES 'person' ('person_id'))

在这种情况下，因为 favorite_food 表的某些数据依赖于 person 表，所以可以将 favorite_food 表视为子表，将 person 表视为父表。如果要在两个表中都输入数据，则应该先在父表 person 中创建一行数据，才能在 favorite_food 中插入新数据。

注意：只有使用 InnoDB 存储引擎创建表时，才会强制执行外键约束。我们将在第十二章讨论 MySQL 的存储引擎。

非法列值

person 表中的 eye_color 列仅限于：“BR”表示棕色，“BL”表示蓝色，“GR”表示绿色。如果你试图将该列的值设置为任何其他值，那么你将会收到如下响应：

mysql> UPDATE person  

-> SET eye_color = 'ZZ' 

 -> WHERE person_id = 1;

ERROR 1265 (01000): Data truncated for column 'eye_color' at row 1

这个错误消息有点让人迷糊，但你大致能知道服务器对于为 eye_color 列提供的值并不满意。

无效的日期转换

如果构造用于产生 date 列的字符串，而该字符串与预期格式不匹配，则会产生又一个错误。以下示例使用的日期格式与默认的 YYYY-MM-DD 日期格式不匹配：

mysql> UPDATE person  

-> SET birth_date = 'DEC-21-1980' 

 -> WHERE person_id = 1;

ERROR 1292 (22007): Incorrect date value: 'DEC-21-1980' for column 'birth_date' at row 1

一般来说，最好显式指定格式字符串，而不是依赖默认格式。下面是语句的另一个版本，它使用 str_to_date 函数指定要使用的字符串格式：

mysql> UPDATE person  

-> SET birth_date = str_to_date('DEC-21-1980' , '%b-%d-%Y')

 -> WHERE person_id = 1;

Query OK, 1 row affected (0.12 sec) Rows matched: 1 Changed: 1 Warnings: 0

不仅数据库服务器很高兴，威廉也很高兴（我们刚刚让他年轻了 8 岁，而且还不需要昂贵的整容手术！）。

注意：在本章的前面介绍各种时态数据类型时，我展示过日期格式字符串，如 YYYY-MM-DD。虽然许多数据库服务器使用这种格式，但 MySQL 使用 %Y 指定四位数字的年份。以下是在 MySQL 中将字符串转换为 datetime 时可能需要的其他一些格式：

%a The short weekday name, such as Sun, Mon, ...

%b The short month name, such as Jan, Feb, ...

%c The numeric month (0..12)

%d The numeric day of the month (00..31)

%f The number of microseconds (000000..999999)

%H The hour of the day, in 24-hour format (00..23)

%h The hour of the day, in 12-hour format (01..12)

%i The minutes within the hour (00..59)

%j The day of year (001..366)

%M The full month name (January..December)

%m The numeric month

%p AM or PM %s The number of seconds (00..59)

%W The full weekday name (Sunday..Saturday)

%w The numeric day of the week (0=Sunday..6=Saturday)

%Y The four-digit year

Sakila 数据库

在本书的其余部分，大多数示例将会用到名叫 Sakila 的示例数据库，该数据库由使用 MySQL 的好心人提供。这个数据库是一个 DVD 租赁连锁公司的模型，虽然有点过时，但只要发挥你的想象力，就可以把它当作一家视频流媒体公司。该数据库中包括的表格有 customer、film、actor、payment、rental 和 category。当你按照本章开头介绍的步骤加载 MySQL 服务器并生成示例数据时，应该就已经创建了数据库的整个方案和示例数据。有关表字段以及相互关系的图示，请参见附录 A。

下表（2-9）显示了 Sakila 中使用的一些表，以及每个表的简单定义：

 你可以随意使用这些表，还能新增自己的表以扩展业务功能。如果你修改了数据库之后，还想使用原来完好无损的数据库的话，就可以删除数据库，然后下载文件重新创建示例数据库。如果你使用的是临时会话，那么会话关闭时，你所做的所有更改都将丢失，因此你可能需要保留所做更改的脚本（代码），以便下一次重新执行它们。

如果要查看数据库中可用的表，可以使用 show tables 命令，如下所示：

mysql> show tables;

+----------------------------+

| Tables_in_sakila |

+----------------------------+

| actor |

| actor_info |

| address |

| category |

| city |

| country |

| customer |

| customer_list |

| film |

| film_actor |

| film_category |

| film_list |

| film_text |

| inventory |

| language |

| nicer_but_slower_film_list |

| payment |

| rental |

| sales_by_film_category |

| sales_by_store |

| staff |

| staff_list |

| store |

+----------------------------+

23 rows in set (0.02 sec)

除了 Sakila 方案中的 23 个表之外，该列表还包括本章中创建的两个表：person 和 favorite_food，我们在后面的章节中不会使用它们了，所以可以通过使用下面的命令来删除它们：

mysql> DROP TABLE favorite_food;

Query OK, 0 rows affected (0.56 sec)

mysql> DROP TABLE person;

Query OK, 0 rows affected (0.05 sec)

如果要查看表中的列，可以使用 describe 命令。下面是 customer 表的 describe 输出：

mysql> desc customer;

+-------------+--------------+------+-----+-------------+----------------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+--------------+------+-----+-------------+----------------------+

| customer_id | smallint(5) | NO | PRI | NULL | auto_increment |  unsigned  | store_id | tinyint(3) | NO | MUL | NULL |

|  unsigned  | first_name | varchar(45) | NO | | NULL | |

| last_name | varchar(45) | NO | MUL | NULL | |

| email | varchar(50) | YES | | NULL | |

| address_id | smallint(5) | NO | MUL | NULL | |  

unsigned  | active | tinyint(1) | NO | | 1 | |

| create_date | datetime | NO | | NULL | |

| last_update | timestamp | YES | | CURRENT_ | DEFAULT_GENERATED on  TIMESTAMP update CURRENT_  

TIMESTAMP |

 +-------------+--------------+------+-----+-------------+----------------------+

你对示例数据库越熟悉，就越能理解示例，从而更好地理解后面章节中介绍的概念。