PHP、MySQL 和 JavaScript 学习指南第六版（三）

原文：zh.annas-archive.org/md5/4aa97a1e8046991cb9f8d5f0f234943f

译者：飞龙

协议：CC BY-NC-SA 4.0

第八章：MySQL 简介

MySQL 已经安装了超过一千万个实例，可能是最流行的用于 Web 服务器的数据库管理系统。在 1990 年代中期开发，现在是一种成熟的技术，支持如今最受欢迎的互联网目的地之一。

其成功之一是与 PHP 一样，它是免费使用的。但它也非常强大且速度异常快。MySQL 也具有高度可扩展性，这意味着它可以随着您的网站增长；最新的基准测试结果可以在线更新。

MySQL 基础知识

数据库是存储在计算机系统中的结构化记录或数据的集合，并以可以快速搜索并能迅速检索信息的方式组织。

MySQL 中的 SQL 代表 Structured Query Language。这种语言基于英语并且也用于其他数据库，如 Oracle 和 Microsoft SQL Server。它旨在通过命令允许从数据库中发出简单请求，例如：

SELECT title FROM publications WHERE author = 'Charles Dickens';

一个 MySQL 数据库包含一个或多个表，每个表包含记录或行。在这些行中有各种列或字段，包含数据本身。表 8-1 展示了一个包含五个出版物的示例数据库，详细说明了作者、标题、类型和出版年份。

表 8-1。简单数据库示例

作者	标题	类型	年份
马克·吐温	汤姆·索亚历险记	小说	1876
简·奥斯汀	傲慢与偏见	小说	1811
查尔斯·达尔文	物种起源	非虚构	1856
查尔斯·狄更斯	古玩店	小说	1841
威廉·莎士比亚	罗密欧与朱丽叶	戏剧	1594

表中的每一行与 MySQL 表中的一行相同，表中的每一列对应 MySQL 中的一列，行内的每个元素与 MySQL 中的字段相同。

为了唯一标识此数据库，我将在接下来的示例中称其为出版物数据库。同时，正如您已经注意到的，所有这些出版物都被认为是文学经典，所以我将称表中保存详细信息的表为经典文学。

数据库术语总结

您现在需要熟悉的主要术语如下：

数据库

包含 MySQL 数据集合的整体容器

表

数据库中存储实际数据的子容器

行

表内的单个记录，可能包含多个字段

列

一行中的字段名称

我应该指出，我并不试图复制关于关系数据库使用的精确术语，而只是提供简单、日常的术语，以帮助您快速掌握基本概念并开始使用数据库。

通过命令行访问 MySQL

有三种主要方式可以与 MySQL 交互：使用命令行、通过诸如 phpMyAdmin 的 Web 界面以及通过 PHP 等编程语言。我们将从第 11 章开始进行这些操作的第三种方法，但现在让我们先看看前两种方法。

启动命令行界面

下面的部分描述了 Windows、macOS 和 Linux 的相关说明。

Windows 用户

如果您按照第 2 章中解释的方式安装了 AMPPS，您将能够从以下目录访问 MySQL 可执行文件：

C:\Program Files\Ampps\mysql\bin

注意

如果您在其他位置安装了 AMPPS，则需要使用该目录，例如 32 位安装的 AMPPS 如下：

C:\Program Files (x86)\Ampps\mysql\bin

默认情况下，初始 MySQL 用户是root，默认密码是mysql。因此，要进入 MySQL 的命令行界面，请选择开始→运行，输入CMD到运行框中，然后按回车。这将调用 Windows 命令提示符。从那里，输入以下内容（根据刚才讨论做出适当更改）：

cd C:\"Program Files\Ampps\mysql\bin"
mysql -u root -pmysql

第一条命令切换到 MySQL 目录，第二条告诉 MySQL 使用用户root和密码mysql登录。您现在已登录到 MySQL，可以开始输入命令了。

如果您使用的是 Windows PowerShell（而不是命令提示符），它不会从当前位置加载命令，因此您必须明确指定要从何处加载程序，这种情况下，您应输入以下内容（注意在mysql命令之前加上前缀 ./）：

cd C:\"Program Files\Ampps\mysql\bin"
./mysql -u root -pmysql

要确保一切都按预期工作，请输入以下内容，结果应类似于图 8-1：

SHOW databases;

图 8-1. 从 Windows 命令提示符访问 MySQL

您现在可以继续下一节，“使用命令行界面”。

macOS 用户

要继续本章，您应按照第 2 章中详细介绍的方式安装 AMPPS。您还应该运行 Web 服务器并启动 MySQL 服务器。

要进入 MySQL 命令行界面，请启动 Terminal 程序（Finder→Utilities 中应该有）。然后调用已安装在目录*/Applications/ampps/mysql/bin*中的 MySQL 程序。

默认情况下，初始 MySQL 用户是root，密码为mysql。因此，要启动程序，请输入以下内容：

/Applications/ampps/mysql/bin/mysql -u root -pmysql

此命令告诉 MySQL 使用用户root和密码mysql登录。为了验证一切正常，请输入以下内容（应该会得到图 8-2 的结果）：

SHOW databases;

图 8-2. 从 macOS 终端程序访问 MySQL

如果收到类似Can't connect to local MySQL server through socket的错误，请首先按照第二章中描述的步骤启动 MySQL 服务器。

您现在应该准备好进入下一节，“使用命令行界面”。

Linux 用户

在运行类似 Linux 的 Unix 操作系统的系统上，您可能已经安装并运行了 PHP 和 MySQL，并且能够进入下一节的示例（如果没有，则可以按照第二章中的步骤安装 AMPPS）。首先，您应该输入以下内容以登录到您的 MySQL 系统：

mysql -u root -p

这告诉 MySQL 使用用户root登录，并要求输入密码。如果有密码，请输入；否则，只需按回车键即可。

一旦登录成功，请输入以下内容来测试程序——您应该会看到类似图 8-3 的响应：

SHOW databases;

图 8-3. 使用 Linux 访问 MySQL

如果此过程在任何时候失败，请参考第二章确保您已正确安装 MySQL。否则，您现在应该准备好进入下一节，“使用命令行界面”。

远程服务器上的 MySQL

如果您正在访问远程服务器上的 MySQL，它可能是 Linux/FreeBSD/Unix 类型的服务器，您应该通过安全的 SSH 协议连接到它（切勿使用不安全的 Telnet 协议）。一旦连接成功，您可能会发现事情有些不同，这取决于系统管理员如何设置服务器——尤其是如果它是共享主机服务器。因此，您需要确保已获得 MySQL 的访问权限，并且拥有您的用户名和密码。有了这些信息，您可以输入以下内容，其中*username*是您提供的用户名：

mysql -u *username* -p

提示输入密码。然后，您可以尝试以下命令，这应该会得到类似图 8-3 的结果：

SHOW databases;

可能已经存在其他数据库，而test数据库可能不存在。

还要记住系统管理员对所有事物有最终控制权，您可能会遇到一些意外的设置。例如，您可能会发现需要在创建的所有数据库名称之前加上唯一的标识字符串，以确保您的名称不会与其他用户创建的数据库名称发生冲突。

因此，如果您遇到任何问题，请与系统管理员交谈，他们将会帮助您解决问题。只需告诉系统管理员您需要用户名和密码。您还应该要求能够创建新的数据库或者至少为您创建一个准备好使用的数据库。然后，您可以在该数据库中创建所有需要的表格。

使用命令行界面

从现在开始，无论您使用 Windows、macOS 还是 Linux 直接访问 MySQL，使用的所有命令（以及可能出现的错误）都是相同的。

分号

让我们从基础知识开始。您注意到您键入的SHOW databases;命令末尾的分号（;）了吗？分号由 MySQL 用于分隔或结束命令。如果您忘记输入它，MySQL 将发出提示并等待您输入。所需的分号被纳入语法中，以便您输入多行命令，这可能很方便，因为有些命令变得相当长。它还允许您在每个命令后放置分号，一次输入多个命令。当您按下 Enter（或 Return）键时，解释器会将它们全部接收并按顺序执行。

注意

很常见的是，您会收到 MySQL 提示符而不是您命令的结果；这意味着您忘记了最后的分号。只需输入分号并按 Enter 键，您将得到您想要的结果。

MySQL 可能会向您展示六种不同的提示符（参见表 8-2），因此您始终会知道在多行输入时的位置。

表 8-2. MySQL 的六个命令提示符

MySQL 提示符	意义
mysql>	准备并等待命令
->	等待下一行命令
'>	等待下一行以单引号开头的字符串
">	等待下一行以双引号开头的字符串
`>	等待下一行以反引号开头的字符串
/*>	等待下一行以/*开头的注释

取消命令

如果您在输入命令的过程中决定不执行它，可以输入\c并按回车键。示例 8-1 展示了如何使用该命令。

示例 8-1. 取消输入行

meaningless gibberish to mysql \c

当您键入该行时，MySQL 将忽略您键入的所有内容并发出新的提示符。如果没有\c，它将显示错误消息。但要小心：如果您已打开字符串或注释，请先关闭它，然后再使用\c，否则 MySQL 会认为\c只是字符串的一部分。示例 8-2 展示了正确的做法。

示例 8-2. 从字符串内部取消输入

this is "meaningless gibberish to mysql" \c

还要注意，在分号后使用\c将不会取消之前的命令，因为这是一个新的语句。

MySQL 命令

您已经看到了SHOW命令，它列出了表、数据库和许多其他项目。您最常使用的命令列在表 8-3 中。

表 8-3. 常见的 MySQL 命令

命令	动作
ALTER	修改数据库或表
BACKUP	备份表
\c	取消输入
CREATE	创建数据库
DELETE	从表中删除一行
DESCRIBE	描述表的列
DROP	删除数据库或表
EXIT (Ctrl-C)	退出（某些系统上）
GRANT	更改用户权限
HELP (\h, \?)	显示帮助
INSERT	插入数据
LOCK	锁定表
QUIT `(`\q`)`	同EXIT
RENAME	重命名表
SHOW	列出对象的详细信息
SOURCE	执行文件
STATUS (\s)	显示当前状态
TRUNCATE	清空表
UNLOCK	解锁表
UPDATE	更新现有记录
USE	使用数据库

在我们继续之前，我将涵盖其中大部分内容，但首先，您需要记住 MySQL 命令的几个要点：

• SQL 命令和关键字不区分大小写。CREATE、create和CrEaTe都表示同一意思。然而，为了清晰起见，您可能更喜欢使用大写。

• 在 Linux 和 macOS 上，表名区分大小写，但在 Windows 上不区分大小写。因此，为了可移植性，请始终选择一种大小写风格并坚持使用。推荐的风格是对表名使用小写。

创建数据库

如果您正在远程服务器上工作，并且只有一个用户帐户和访问为您创建的单个数据库，请继续到“创建表”部分（#creating_a_table）。否则，通过执行以下命令来创建一个名为publications的新数据库：

CREATE DATABASE publications;

一条成功的命令将返回一条目前意义不大的消息—Query OK, 1 row affected (0.00 sec)—但很快就会变得合理。现在您已经创建了数据库，想要使用它，请执行以下命令：

USE publications;

您现在应该看到消息Database changed，然后可以继续执行以下示例。

创建用户

现在您已经看到了如何轻松使用 MySQL 并创建了您的第一个数据库，是时候看看如何创建用户了，因为您可能不想将 PHP 脚本授予 MySQL 的 root 访问权限——如果被黑客入侵将会带来真正的麻烦。

要创建用户，请发出CREATE USER命令，其形式如下（不要输入此命令；这不是一个实际的工作命令）：

CREATE USER '*`username`*'@'*`hostname`*' IDENTIFIED BY '*`password`*';
GRANT *`PRIVILEGES`* ON *`database``.``object`* TO '*`username`*'@'*`hostname`*';

这看起来都很简单，可能除了*database.object*部分，它指的是数据库本身及其包含的对象，如表（参见表 8-4）。

表 8-4. GRANT命令的示例参数

参数	意义
*.*	所有数据库及其所有对象
*`database`*.*	仅限名为*database*的数据库及其所有对象
*`database`*.*`object`*	仅限名为*database的数据库及其名为object*的对象

所以，让我们创建一个用户，他可以访问新的publications数据库及其所有对象，输入以下命令（用您选择的用户名jim和密码password替换）：

CREATE USER 'jim'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON publications.* TO 'jim'@'localhost';

这样做的效果是允许用户jim@localhost使用密码password完全访问publications数据库。你可以通过输入quit退出并重新运行 MySQL 来测试此步骤是否有效，但不要像以前那样以 root 身份登录，而是使用你创建的用户名登录（例如，jim）。参见 Table 8-5 获取适合你操作系统的正确命令。如果mysql客户端程序安装在系统的不同目录中，则需要根据需要修改。

表 8-5. 启动 MySQL 并作为 jim@localhost 登录

操作系统	示例命令
Windows	C:\"Program Files\Ampps\mysql\bin\mysql" -u jim -p
macOS	/Applications/ampps/mysql/bin/mysql -u jim -p
Linux	mysql -u jim –p

现在你只需要在提示时输入密码，就可以登录了。

如果你愿意，你可以在-p后立即输入你的密码（无需任何空格），以避免在提示时输入密码，但这被认为是不良实践，因为如果其他人登录到你的系统，可能有方法查看你输入的命令并找出你的密码。

注意

你只能授予你已经拥有的权限，并且你必须有权限执行GRANT命令。如果你不打算授予所有权限，你可以选择授予一系列的权限。有关GRANT命令和一旦授予就可以撤销的REVOKE命令的更多详细信息，请参阅文档。此外，请注意，如果你创建一个新用户但没有指定IDENTIFIED BY子句，该用户将没有密码，这是非常不安全的情况，应该避免。

创建表格

此时，你应该已经登录到 MySQL，为publications数据库（或为你创建的数据库）授予了ALL权限，因此你可以开始创建你的第一个表格了。确保输入以下内容以使用正确的数据库（如果名称不同，请替换publications）：

USE publications;

现在逐行输入 Example 8-3 中的命令。

Example 8-3. 创建名为 classics 的表

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4)) ENGINE InnoDB;

注意

此命令中的最后两个词需要稍作解释。MySQL 可以以多种不同的方式内部处理查询，这些不同的方式由不同的引擎支持。从版本 5.6 开始，InnoDB是 MySQL 的默认存储引擎，我们在这里使用它是因为它支持FULLTEXT搜索。只要你有一个相对最新的 MySQL 版本，你可以在创建表时省略命令中的ENGINE InnoDB部分，但我现在保留它以强调正在使用的引擎。

如果您使用的是 MySQL 5.6 之前的版本，则 InnoDB 引擎不支持FULLTEXT索引，因此您需要在命令中将InnoDB替换为MyISAM以指示您希望使用该引擎（参见“创建 FULLTEXT 索引”）。

InnoDB 通常更高效且是推荐的选项。如果您按照第二章中详细说明的方法安装了 AMPPS 堆栈，则应至少具有 MySQL 的 5.6.35 版本。

注意

您还可以将上一个命令一次性在一行上执行，就像这样：

CREATE TABLE classics (author VARCHAR(128), title
VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE
InnoDB;

但是 MySQL 命令可能会很长和复杂，因此我建议在您熟悉较长命令之前使用示例 8-3 中显示的格式。

然后 MySQL 应显示响应Query OK, 0 rows affected，以及执行命令所花费的时间。如果您看到错误消息，请仔细检查您的语法。每个括号和逗号都很重要，打字错误很容易出现。

要检查新表是否已创建，请输入以下内容：

DESCRIBE classics;

一切顺利的话，您将看到示例 8-4 中显示的一系列命令和响应，特别注意显示的表格式。

示例 8-4. MySQL 会话：创建和检查新表

mysql> USE publications;
Database changed
mysql> CREATE TABLE classics (
    ->  author VARCHAR(128),
    ->  title VARCHAR(128),
    ->  type VARCHAR(16),
    ->  year CHAR(4)) ENGINE InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> DESCRIBE classics;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| author | varchar(128) | YES  |     | NULL    |       |
| title  | varchar(128) | YES  |     | NULL    |       |
| type   | varchar(16)  | YES  |     | NULL    |       |
| year   | char(4)      | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

当您需要确保已正确创建 MySQL 表时，DESCRIBE命令是一种无价的调试辅助工具。您还可以使用它来回顾表的字段或列名称以及每个字段中的数据类型。让我们详细查看每个标题：

Field

表内每个字段或列的名称

Type

字段中存储的数据类型

Null

字段是否允许包含NULL值

Key

已应用的键的类型（在 MySQL 中，键或索引是快速查找和搜索数据的一种方式）

Default

如果在创建新行时未指定值，则将分配给字段的默认值

Extra

附加信息，例如字段是否设置为自动增量

数据类型

在示例 8-3 中，您可能已经注意到表的三个字段被赋予了VARCHAR数据类型，而一个字段被赋予了CHAR类型。术语VARCHAR代表可变长度字符串，并且该命令接受一个数值，告诉 MySQL 在此字段中存储的字符串的最大长度。

CHAR和VARCHAR都接受文本字符串，并对字段大小施加限制。不同之处在于，CHAR字段中的每个字符串都具有指定的大小。如果插入较小的字符串，它将填充空格。VARCHAR字段不会填充文本；它允许字段的大小根据插入的文本变化。但是，VARCHAR需要一些开销来跟踪每个值的大小。因此，如果所有记录中的大小类似，CHAR稍微更有效率，而如果大小差异很大并且可能很大，则VARCHAR更有效率。此外，这种开销导致对VARCHAR数据的访问比对CHAR数据稍慢。

字符和文本列的另一个特性，对今天的全球网络覆盖非常重要的是字符集。这些为特定字符分配特定的二进制值。你用于英语的字符集显然与用于俄语的不同。你可以在创建字符或文本列时为其指定字符集。

在我们的示例中，VARCHAR非常有用，因为它可以容纳不同长度的作者名和标题，同时帮助 MySQL 规划数据库的大小，并更轻松地执行查找和搜索。只需注意，如果您尝试分配超过允许长度的字符串值，它将被截断为表定义中声明的最大长度。

然而，year字段具有可预测的值，因此我们使用更高效的CHAR(4)数据类型而不是VARCHAR。参数为4允许 4 个字节的数据，支持从–999 到 9999 年的所有年份；一个字节包括 8 位，可以具有 00000000 到 11111111 的值，即 0 到 255 的十进制数值。

当然，你可以只存储两位数值来表示年份，但如果你的数据在下个世纪仍然需要使用，或者可能会发生环绕，那么就需要先进行清理—考虑到可能导致日期从 2000 年 1 月 1 日开始被视为 1900 年的“千年虫”问题，在全球最大的计算机设施中都存在这个问题。

注意

我在classics表中没有使用YEAR数据类型，因为它仅支持 0000 年和 1901 年至 2155 年的年份。这是因为 MySQL 为了效率的原因将年份存储在单个字节中，但这意味着只有 256 年可用，并且classics表中的标题出版年份远在 1901 年之前。

CHAR数据类型

表 8-6 列出了CHAR数据类型。这两种类型都有一个参数，用于设置字段允许的字符串最大（或确切）长度。正如表所示，每种类型都有一个内置的最大字节数。

表 8-6. MySQL 的 CHAR 数据类型

数据类型	使用的字节	示例
CHAR(*`n`*)	精确 n（<= 255）	CHAR(5) “Hello” uses 5 bytes CHAR(57) “Goodbye” uses 57 bytes
VARCHAR(*`n`*)	最多 n (<= 65535)	VARCHAR(7) “Hello” 使用 5 字节 VARCHAR(100) “Goodbye” 使用 7 字节

BINARY 数据类型

BINARY 数据类型（参见 表 8-7）存储没有相关字符集的字节字符串。例如，你可以使用 BINARY 数据类型来存储 GIF 图像。

表 8-7. MySQL 的 BINARY 数据类型

数据类型	使用字节	示例
BINARY(n)	恰好 n (<= 255)	与 CHAR 相似但包含二进制数据
VARBINARY(n)	最多 n (<= 65535)	与 VARCHAR 相似但包含二进制数据

TEXT 数据类型

字符数据也可以存储在一个 TEXT 字段中。这些字段与 VARCHAR 字段之间的差异很小：

• 在版本 5.0.3 之前，MySQL 会从 VARCHAR 字段中删除前导和尾随空格。

• TEXT 字段不能有默认值。

• MySQL 仅索引 TEXT 列的前 n 个字符（创建索引时指定 n）。

这意味着，如果你需要搜索字段的整个内容，VARCHAR 是更好和更快的数据类型。如果你永远不会搜索字段中超过某个前导字符数，那么你可能应该使用 TEXT 数据类型（参见 表 8-8）。

表 8-8. MySQL 的 TEXT 数据类型

数据类型	使用字节	属性
TINYTEXT(*`n`*)	最多 n (<= 255)	被视为带有字符集的字符串
TEXT(*`n`*)	最多 n (<= 65535)	被视为带有字符集的字符串
MEDIUMTEXT(*`n`*)	最多 n (<= 1.67e + 7)	被视为带有字符集的字符串
LONGTEXT(*`n`*)	最多 n (<= 4.29e + 9)	被视为带有字符集的字符串

数据类型的最大值越小，效率也越高；因此，你应该使用你知道对于字段中存储的任何字符串都足够的最小最大值。

BLOB 数据类型

术语 BLOB 代表 二进制大对象，因此，正如你想的那样，BLOB 数据类型对于超过 65,536 字节大小的二进制数据最为有用。BLOB 和 BINARY 数据类型之间的主要区别是 BLOB 不能有默认值。BLOB 数据类型列在 表 8-9 中。

表 8-9. MySQL 的 BLOB 数据类型

数据类型	使用字节	属性
TINYBLOB(*`n`*)	最多 n (<= 255)	被视为二进制数据—无字符集
BLOB(*`n`*)	最多 n (<= 65535)	被视为二进制数据—无字符集
MEDIUMBLOB(*`n`*)	最多 n (<= 1.67e + 7)	被视为二进制数据—无字符集
LONGBLOB(*`n`*)	最多 n (<= 4.29e + 9)	被视为二进制数据—无字符集

数值数据类型

MySQL 支持各种数值数据类型，从单字节到双精度浮点数。尽管数值字段可以使用的最大内存为 8 字节，但建议您选择能够充分处理您期望的最大值的最小数据类型。这有助于保持数据库小并快速访问。

表 8-10 列出了 MySQL 支持的数值数据类型及其可包含的值范围。如果您对术语不熟悉，有符号数的可能范围从负值通过 0 到正值；无符号数的值范围从 0 到正值。它们都可以保存相同数量的值；可以想象有符号数被左移一半，使其一半的值为负数，另一半为正数。请注意，浮点数值（任何精度）可能只是有符号的。

表 8-10. MySQL 的数值数据类型

数据类型	使用字节	最小值	最大值
有符号	无符号	有符号	无符号
---	---	---	---
TINYINT	1	–128	0	127	255
SMALLINT	2	–32768	0	32767	65535
MEDIUMINT	3	–8.38e + 6	0	8.38e + 6	1.67e + 7
INT / INTEGER	4	–2.15e + 9	0	2.15e + 9	4.29e + 9
BIGINT	8	–9.22e + 18	0	9.22e + 18	1.84e + 19
FLOAT	4	–3.40e + 38	n/a	3.4e + 38	n/a
DOUBLE / REAL	8	–1.80e + 308	n/a	1.80e + 308	n/a

要指定数据类型是否为无符号，请使用UNSIGNED限定词。以下示例创建了一个名为tablename的表，其中包含一个名为fieldname的UNSIGNED INTEGER数据类型的字段：

CREATE TABLE tablename (fieldname INT UNSIGNED);

在创建数值字段时，您还可以传递一个可选的数字作为参数，如下所示：

CREATE TABLE tablename (fieldname INT(4));

但您必须记住，与BINARY和CHAR数据类型不同，此参数并不表示要使用的存储字节数。这似乎是反直觉的，但该数字实际上表示的是在检索字段中的数据时的显示宽度。它通常与ZEROFILL限定词一起使用，如下所示：

CREATE TABLE tablename (fieldname INT(4) ZEROFILL);

这样做的效果是使任何宽度小于四个字符的数字填充一个或多个零，以使字段的显示宽度为四个字符长度。当字段已经达到或超过指定宽度时，不会进行填充。

日期和时间类型

MySQL 支持的主要剩余数据类型涉及日期和时间，可以在表 8-11 中查看。

表 8-11. MySQL 的DATE和TIME数据类型

数据类型	时间/日期格式
DATETIME	'0000-00-00 00:00:00'
DATE	'0000-00-00'
TIMESTAMP	'0000-00-00 00:00:00'
TIME	'00:00:00'
YEAR	0000（仅年份 0000 和 1901–2155）

DATETIME和TIMESTAMP数据类型显示方式相同。主要区别在于TIMESTAMP具有非常狭窄的范围（从 1970 年到 2037 年），而DATETIME将保存几乎您可能指定的任何日期，除非您对古代历史或科幻感兴趣。

TIMESTAMP很有用，因为你可以让 MySQL 为你设置值。如果在添加行时未指定值，则自动插入当前时间。你还可以让 MySQL 在每次更改行时更新TIMESTAMP列。

自动增量属性

有时您需要确保数据库中每一行都保证是唯一的。您可以通过仔细检查输入的数据并确保至少有一个值在任何两行中不同来在程序中执行此操作，但这种方法容易出错，并且仅在某些情况下有效。例如，在classics表中，作者可能多次出现。同样，出版年份也会频繁重复，等等。很难保证没有重复行。

通用解决方案是专门为此目的使用额外的列。一会儿我们将讨论使用出版物的 ISBN（国际标准书号），但首先我想介绍AUTO_INCREMENT数据类型。

正如其名称所示，赋予此数据类型的列将将其内容的值设置为先前插入行中的列条目值加 1。示例 8-5 显示了如何向表classics添加名为id的新列，该列具有自增功能。

示例 8-5. 添加自增列 id

ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

这是你对ALTER命令的介绍，它与CREATE非常相似。ALTER操作现有表，可以添加、更改或删除列。我们的示例添加了一个名为id的列，具有以下特性：

INT UNSIGNED

使该列采用足够大的整数，以便我们在表中存储超过 40 亿条记录。

NOT NULL

确保每一列都有值。许多程序员在字段中使用NULL来指示其没有任何值。但这样做会允许重复，这将违反该列存在的整体原因，因此我们禁止NULL值。

AUTO_INCREMENT

如前所述，这会导致 MySQL 为每一行的该列设置一个唯一值。我们实际上无法控制该列在每行中将取得的值，但我们并不在乎：我们关心的是我们可以保证一个唯一的值。

KEY

自增列作为键很有用，因为你往往会基于这一列搜索行。这将在“索引”部分进行解释。

每个id列的条目现在都将具有唯一的编号，第一个从 1 开始，其他的依次递增。每次插入新行时，其id列将自动获得序列中的下一个数字。

而不是事后应用列，您可以通过以稍微不同的格式发布CREATE命令来包含它。在这种情况下，示例 8-3 中的命令将替换为示例 8-6。特别要注意最后一行。

示例 8-6. 在表创建时添加自增 id 列

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4),
 id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE InnoDB;

如果您想检查列是否已添加，请使用以下命令查看表的列和数据类型：

DESCRIBE classics;

现在我们已经完成了，id列不再需要了，因此如果您是通过示例 8-5 创建它的，现在应该使用示例 8-7 中的命令来删除该列。

示例 8-7. 删除 id 列

ALTER TABLE classics DROP id;

向表添加数据

要向表中添加数据，请使用INSERT命令。让我们通过使用INSERT命令的一种形式，重复将来自表 8-1 的数据填充到表classics中来看看它的效果（示例 8-8）。

示例 8-8. 填充经典表

INSERT INTO classics(author, title, type, year)
 VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');
INSERT INTO classics(author, title, type, year)
 VALUES('Jane Austen','Pride and Prejudice','Fiction','1811');
INSERT INTO classics(author, title, type, year)
 VALUES('Charles Darwin','The Origin of Species','Nonfiction','1856');
INSERT INTO classics(author, title, type, year)
 VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841');
INSERT INTO classics(author, title, type, year)
 VALUES('William Shakespeare','Romeo and Juliet','Play','1594');

每输入完两行之后，您应该看到一个Query OK消息。一旦所有行都已输入，请输入以下命令，它将显示表的内容。结果应该类似于图 8-4。

SELECT * FROM classics;

现在暂时不必担心SELECT命令——我们将在“查询 MySQL 数据库”一节中讨论它。简单来说，按照现在的方式输入，它将显示您刚刚输入的所有数据。

如果您看到返回的结果顺序不同也不要担心，这是正常的，因为此时的顺序是未指定的。在本章后面我们将学习如何使用ORDER BY来选择希望结果返回的顺序，但目前它们可能以任何顺序显示。

图 8-4. 填充经典表并查看其内容

让我们回头看看我们如何使用INSERT命令。首先部分INSERT INTO classics告诉 MySQL 在哪里插入以下数据。然后在括号内，列出了四个列名——author、title、type和year——所有列名之间用逗号分隔。这告诉 MySQL 这些是要插入数据的字段。

每个INSERT命令的第二行包含关键字VALUES，后面跟着括号内用逗号分隔的四个字符串。这向 MySQL 提供了要插入到先前指定的四个列中的四个值。（一如既往，我在哪里断行是任意选择的。）

每个数据项将插入到相应的列中，一一对应。如果您意外地按不同于数据的顺序列出列，则数据将进入错误的列中。此外，列的数量必须与数据项的数量匹配。（有更安全的INSERT使用方法，我们很快就会看到。）

重命名表

重命名表，如通过ALTER命令对表的结构或元信息进行的任何其他更改。因此，例如，要将表名classics更改为pre1900，您将使用以下命令：

ALTER TABLE classics RENAME pre1900;

如果您尝试过该命令，则应该通过输入以下内容恢复表名，以便本章后面的示例按照打印的方式运行：

ALTER TABLE pre1900 RENAME classics;

修改列的数据类型

更改列的数据类型还需使用ALTER命令，这次配合MODIFY关键字。要将列year的数据类型从CHAR(4)更改为SMALLINT（仅需要 2 个字节的存储空间，因此可以节省磁盘空间），请输入以下内容：

ALTER TABLE classics MODIFY year SMALLINT;

当您执行此操作时，如果数据类型的转换对 MySQL 有意义，它将自动更改数据并保持含义。在这种情况下，它将每个字符串转换为可比较的整数，只要该字符串可识别为指代整数。

添加新列

假设您已创建了一个表并填充了大量数据，但后来发现需要增加一个额外的列。别担心。以下是如何添加新列pages的方法，用于存储出版物的页数：

ALTER TABLE classics ADD pages SMALLINT UNSIGNED;

使用UNSIGNED SMALLINT数据类型添加名为pages的新列，足以容纳最多 65535 的值——希望这足够存储任何已发表的书籍！

如果您要求 MySQL 使用DESCRIBE命令描述更新后的表，如下所示，您将看到已进行了更改（参见图 8-5）：

DESCRIBE classics;

图 8-5。添加新页面列并查看表

重命名列

再次查看图 8-5，您可能会发现，拥有一个名为type的列很令人困惑，因为这是 MySQL 用来标识数据类型的名称。再次强调——没问题——让我们将其名称更改为category，如下所示：

ALTER TABLE classics CHANGE type category VARCHAR(16);

请注意，在此命令的末尾添加了VARCHAR(16)。这是因为CHANGE关键字要求指定数据类型，即使您不打算更改它，并且在最初创建该列时指定了VARCHAR(16)作为类型。

删除列

实际上，在反思之后，您可能会决定这个特定数据库中的页数列pages并不那么有用，因此下面是如何通过使用DROP关键字删除该列的方法：

ALTER TABLE classics DROP pages;

警告

记住，DROP是不可逆的。您应该始终谨慎使用它，因为如果不小心，它可能会意外删除整个表（甚至整个数据库）！

删除表

删除表确实非常简单。但是，因为我不希望您不得不重新输入classics表的所有数据，让我们快速创建一个新表，验证其存在，然后再删除它。您可以通过键入示例 8-9 中的命令来执行此操作。这四个命令的结果应如图 8-6 所示。

示例 8-9. 创建、查看和删除表

CREATE TABLE disposable(trash INT);
DESCRIBE disposable;
DROP TABLE disposable;
SHOW tables;

图 8-6. 创建、查看和删除表

索引

就目前而言，classics表可以在 MySQL 中无问题地工作和搜索，直到它的行数超过几百行。此时，随着每新增一行，数据库访问速度会变得越来越慢，因为 MySQL 在发出查询时必须搜索每一行。这就像在需要查找某物时搜索图书馆中的每一本书一样。

当然，你不必像这样搜索图书馆，因为它们要么有一个卡片索引系统，要么更可能有自己的数据库。对 MySQL 也是如此，因为通过稍微增加内存和磁盘空间的开销，您可以为 MySQL 创建一个“卡片索引”，用于进行极快的搜索。

创建索引

实现快速搜索的方法是在创建表时或随时以后添加索引。但是，决策并不那么简单。例如，有不同的索引类型，如常规INDEX、PRIMARY KEY或FULLTEXT索引。此外，您必须决定哪些列需要索引，这需要您预测是否会搜索每列的任何数据。索引也可以变得更加复杂，因为您可以将多个列组合到一个索引中。即使在做出了决策后，您仍然可以通过限制要索引的每列的数量来减少索引大小。

如果我们想象一下可能在classics表上进行的搜索，就会发现所有列都可能需要进行搜索。但是，如果在“添加新列”部分创建的pages列没有被删除，那么可能不需要索引，因为大多数人不太可能通过页面数来搜索书籍。不管怎样，继续为每个列添加索引，使用示例 8-10 中的命令。

示例 8-10. 为 classics 表添加索引

ALTER TABLE classics ADD INDEX(author(20));
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);
DESCRIBE classics;

前两个命令在author和title列上创建索引，将每个索引限制为仅前 20 个字符。例如，当 MySQL 索引以下标题时：

The Adventures of Tom Sawyer

它实际上只会存储索引的前 20 个字符：

The Adventures of To

这样做是为了减小索引的大小并优化数据库访问速度。我选择了 20，因为对于这些列中的大多数字符串来说，这可能足以确保唯一性。如果 MySQL 发现两个内容相同的索引，它将不得不浪费时间访问表本身，并检查索引的列，以找出真正匹配的行。

使用category列，目前只需要第一个字符来识别字符串的唯一性（F 表示 Fiction，N 表示 Nonfiction，P 表示 Play），但我选择了四个字符的索引，以便未来可能共享前三个字符的类别。当您有更完整的类别集时，还可以稍后重新索引此列。最后，我对year列的索引没有设置限制，因为它的长度明确定义为四个字符。

发出这些命令的结果（以及确认它们是否有效的DESCRIBE命令）可以在图 8-7 中看到，该图显示了每列的MUL关键字。此关键字表示该列中可能存在多个值的多次出现，这正是我们想要的，因为作者可能出现多次，同一书名可能由多位作者使用，等等。

图 8-7. 为经典表添加索引

使用 CREATE INDEX

添加索引的另一种方法是使用ALTER TABLE命令。它们是等效的，但CREATE INDEX不能用于创建PRIMARY KEY（参见“主键”一节）。此命令的格式显示在例子 8-11 的第二行中。

例子 8-11. 这两个命令是等效的

ALTER TABLE classics ADD INDEX(author(20));
CREATE INDEX author ON classics (author(20));

在创建表时添加索引

您无需等到创建表后才添加索引。事实上，这样做可能会很耗时，因为向大表添加索引可能需要很长时间。因此，让我们看一下一个命令，它创建了带有索引的classics表。

例子 8-12 是例子 8-3 的重新制作，在这个版本中，索引与表同时创建。请注意，为了融入本章所做的修改，此版本使用了新的列名category代替type，并将year的数据类型设置为SMALLINT，而不是CHAR(4)。如果您想在首先删除当前classics表之前尝试它，请将第 1 行中的classics改为其他名称，如classics1，然后在完成后删除classics1。

例子 8-12. 创建带索引的经典表

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 category VARCHAR(16),
 year SMALLINT,
 INDEX(author(20)),
 INDEX(title(20)),
 INDEX(category(4)),
 INDEX(year)) ENGINE InnoDB;

主键

到目前为止，您已经创建了表classics并确保 MySQL 可以通过添加索引快速搜索它，但仍然有一些缺失。表中的所有出版物都可以进行搜索，但没有单个唯一的键用于即时访问行。当我们开始从不同的表中组合数据时，拥有具有唯一值的键的重要性将显现出来。

在“AUTO_INCREMENT 属性”一节中简要介绍了在创建自增列id时主键的概念，该列本可以用作此表的主键。然而，我希望将该任务保留给更合适的列：国际公认的 ISBN。

因此，让我们继续为此键创建一个新的列。请记住，ISBN 号码长度为 13 个字符，您可能认为以下命令将起作用：

ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;

但并非如此。如果您尝试，您将收到类似于Duplicate entry for key 1 的错误。原因是表已经填充了一些数据，并且此命令试图向每一行添加具有值NULL的列，这是不允许的，因为在具有主键索引的任何列中，所有值必须是唯一的。如果表中还没有数据，此命令将正常工作，就像在表创建时添加主键索引一样。

在我们当前的情况下，我们必须稍微狡猾地创建新列，而不添加索引，填充数据，然后使用示例 8-13 中的命令回顾性地添加索引。幸运的是，当前数据集中每年都是唯一的，因此我们可以使用year列来标识每一行进行更新。请注意，此示例使用了UPDATE命令和WHERE关键字，在“查询 MySQL 数据库”一节中有更详细的解释。

示例 8-13. 使用数据填充 isbn 列并使用主键

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
UPDATE classics SET isbn='9780582506206' WHERE year='1811';
UPDATE classics SET isbn='9780517123201' WHERE year='1856';
UPDATE classics SET isbn='9780099533474' WHERE year='1841';
UPDATE classics SET isbn='9780192814968' WHERE year='1594';
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;

输入这些命令后，结果应该如图 8-8 所示。请注意，关键字PRIMARY KEY在ALTER TABLE语法中替代了关键字INDEX（比较示例 8-10 和 8-13）。

图 8-8. 回顾性地向经典表添加主键

当创建classics表时已经创建了主键，您可以使用示例 8-14 中的命令。同样，如果您希望尝试此示例，请在第 1 行中将classics重命名为其他名称，然后删除测试表。

示例 8-14. 创建具有主键的经典表

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 category VARCHAR(16),
 year SMALLINT,
 isbn CHAR(13),
 INDEX(author(20)),
 INDEX(title(20)),
 INDEX(category(4)),
 INDEX(year),
 PRIMARY KEY (isbn)) ENGINE InnoDB;

创建 FULLTEXT 索引

不同于常规索引，MySQL 的FULLTEXT允许对整个文本列进行超快速的搜索。它会将每个数据字符串中的每个单词存储在特殊索引中，您可以使用“自然语言”进行搜索，类似于使用搜索引擎。

注意

MySQL 并不严格地存储FULLTEXT索引中的所有单词，因为它有一个内置的停用词列表，包含超过 500 个常见词汇，它们会被忽略，因为它们对搜索并不是非常有用。这些词称为停用词，列表包括the、as、is、of等。该列表有助于 MySQL 在执行FULLTEXT搜索时运行更快，并保持数据库的大小。

以下是关于FULLTEXT索引的一些事项，您应该知道：

• 自 MySQL 5.6 起，InnoDB 表可以使用FULLTEXT索引，但在此之前，FULLTEXT索引只能用于 MyISAM 表。如果需要将表转换为 MyISAM，通常可以使用 MySQL 命令ALTER TABLE tablename ENGINE = MyISAM;。

• 只能为CHAR、VARCHAR和TEXT列创建FULLTEXT索引。

• 在创建表时，可以在CREATE TABLE语句中定义FULLTEXT索引，也可以稍后使用ALTER TABLE（或CREATE INDEX）添加。

• 对于大型数据集，将数据加载到没有FULLTEXT索引的表中，然后再创建索引，速度会快得多。

要创建FULLTEXT索引，请将其应用于一个或多个记录，如示例 8-15 中所示，在classics表中的author和title列对上添加了一个FULLTEXT索引（此索引是额外创建的，并不影响已创建的索引）。

示例 8-15. 在经典表中添加FULLTEXT索引

ALTER TABLE classics ADD FULLTEXT(author,title);

您现在可以在这对列上执行FULLTEXT搜索。如果现在将这些出版物的整篇文章添加到数据库中（尤其是它们已经脱离版权保护期），那么它们将成为完全可搜索的。查看“MATCH...AGAINST”一节，了解使用FULLTEXT进行搜索的描述。

注意

如果发现 MySQL 在访问数据库时运行速度比预期慢，问题通常与索引有关。要么你在需要索引的地方没有索引，要么索引设计不够优化。调整表的索引通常可以解决这类问题。性能超出本书的范围，但在第九章中，我会给出一些提示，让你知道应该注意什么。

查询 MySQL 数据库

到目前为止，我们已经创建了一个 MySQL 数据库和表，填充了数据，并添加了索引以加快搜索速度。现在是时候看看这些搜索是如何执行的，以及可用的各种命令和限定符。

SELECT

如您在 图 8-4 中看到的，SELECT 命令用于从表中提取数据。在该部分中，我使用了最简单的形式来选择所有数据并显示它们——这是您除了最小的表外永远不希望做的事情，因为所有数据将以不可读的速度滚动。或者，在 Unix/Linux 计算机上，您可以通过发出以下命令告诉 MySQL 每次一页地分页输出屏幕：

pager less;

这将输出传输到 less 程序。要恢复标准输出并关闭分页，您可以发出以下命令：

nopager;

现在让我们更详细地研究 SELECT。其基本语法是：

SELECT *`something`* FROM *`tablename`*;

something 可以是 *（星号），如前所示，表示 每一列，或者您可以选择仅选择特定列。例如，示例 8-16 显示了如何仅选择 作者 和 标题，以及仅选择 标题 和 ISBN。输入这些命令的结果可以在 图 8-9 中看到。

示例 8-16. 两个不同的 SELECT 语句

SELECT author,title FROM classics;
SELECT title,isbn FROM classics;

图 8-9. 两个不同 SELECT 语句的输出

SELECT COUNT

something 参数的另一个替代品是 COUNT，它可以以多种方式使用。在 示例 8-17 中，它通过将 * 作为参数来显示表中的行数，这意味着 所有行。正如您所期望的那样，返回的结果是 5，因为表中有五个出版物。

示例 8-17. 计数行

SELECT COUNT(*) FROM classics;

SELECT DISTINCT

DISTINCT 修饰符（及其合作伙伴 DISTINCTROW）允许您在包含相同数据的多个条目时清除多个条目。例如，假设您想要列出表中所有的作者。如果您仅从包含同一作者多本书的表中选择 作者 列，通常会看到一个长列表，其中包含一遍又一遍相同的作者名字。但通过添加 DISTINCT 关键字，您可以仅显示每个作者一次。所以，让我们通过添加另一行来测试这一点，这行重复了我们现有的某个作者（示例 8-18）。

示例 8-18. 复制数据

INSERT INTO classics(author, title, category, year, isbn)
 VALUES('Charles Dickens','Little Dorrit','Fiction','1857', '9780141439969');

现在查尔斯·狄更斯在表中出现了两次，我们可以比较使用 SELECT 带有和不带有 DISTINCT 修饰符的结果。示例 8-19 和 图 8-10 显示，简单的 SELECT 列表将狄更斯列出两次，而带有 DISTINCT 修饰符的命令只显示一次。

示例 8-19. 带有和不带有 DISTINCT 修饰符

SELECT author FROM classics;
SELECT DISTINCT author FROM classics;

图 8-10. 带有和不带有 DISTINCT 的选择数据

DELETE

当您需要从表中删除行时，请使用 DELETE 命令。其语法与 SELECT 命令类似，并允许您使用 WHERE 和 LIMIT 等限定符来缩小要删除的确切行或行的范围。

现在您已经看到了DISTINCT限定词的效果，如果您输入了 Example 8-18，则应该通过输入 Example 8-20 中的命令来移除小杜丽。

Example 8-20. 移除新条目

DELETE FROM classics WHERE title='Little Dorrit';

本示例为所有标题列包含确切字符串小杜丽的行发出DELETE命令。

WHERE关键字非常强大，正确输入非常重要；错误可能导致命令应用于错误的行（或在没有匹配WHERE子句的情况下无效）。因此，我们现在将花一些时间来详细介绍这个子句，这是 SQL 的核心。

WHERE

WHERE关键字使您能够通过返回仅当某个表达式为真时的查询结果来缩小查询范围。Example 8-20 仅返回列完全匹配字符串Little Dorrit的行，使用等号操作符=。Example 8-21 显示了使用WHERE与=操作符的更多示例。

Example 8-21. 使用WHERE关键字

SELECT author,title FROM classics WHERE author="Mark Twain";
SELECT author,title FROM classics WHERE isbn="9781598184891";

鉴于我们当前的表格，在 Example 8-21 中的两个命令显示相同的结果。但是如果我们可以轻松地添加更多马克·吐温的书籍，那么第一行将显示他写的所有书名，第二行将继续（因为我们知道 ISBN 是唯一的）显示汤姆·索亚历险记。换句话说，使用唯一键进行搜索更加可预测，稍后您将看到唯一和主键的价值。

您还可以使用LIKE限定词进行搜索模式匹配，允许对字符串的部分进行搜索。此限定词应与%字符一起使用。当放置在关键字之前时，%表示任何内容在前面。放置在关键字之后时，表示任何内容在后面。Example 8-22 执行了三种不同的查询，分别是字符串开头的查询、字符串结尾的查询以及字符串中的任意位置的查询。

Example 8-22. 使用LIKE限定词

SELECT author,title FROM classics WHERE author LIKE "Charles%";
SELECT author,title FROM classics WHERE title LIKE "%Species";
SELECT author,title FROM classics WHERE title LIKE "%and%";

您可以在 Figure 8-11 中看到这些命令的结果。第一个命令输出了查尔斯·达尔文和查尔斯·狄更斯的出版物，因为LIKE限定词设置为返回与字符串Charles后跟任何其他文本匹配的内容。然后只返回了物种起源，因为这是唯一一行的列以Species结尾。最后，傲慢与偏见和罗密欧与朱丽叶都被返回，因为它们在列中任何位置匹配字符串and。如果占位符中没有内容，%也会匹配空字符串。

图 8-11. 使用WHERE与LIKE限定词

LIMIT

LIMIT限定符使您能够选择在查询中返回多少行以及从表中的哪个位置开始返回它们。当传递一个参数时，它告诉 MySQL 从结果的开头开始，并仅返回该参数中给定的行数。如果传递两个参数，则第一个指示 MySQL 从结果开头的偏移量开始显示，第二个指示要返回的行数。您可以将第一个参数视为指示“跳过开头这些结果行数”的内容。

Example 8-23 包括三条命令。第一条返回表中的前三行。第二条从位置 1 开始返回两行（跳过第一行）。最后一条命令从位置 3 开始返回单行（跳过前三行）。Figure 8-12 显示了执行这三条命令的结果。

Example 8-23. 限制返回的结果数

SELECT author,title FROM classics LIMIT 3;
SELECT author,title FROM classics LIMIT 1,2;
SELECT author,title FROM classics LIMIT 3,1;

警告

要注意LIMIT关键字，因为偏移量从 0 开始，但要返回的行数从 1 开始。因此，LIMIT 1,3意味着从第二行开始返回三行。您可以将第一个参数视为指定要跳过多少行，因此英文指令将是“返回 3 行，跳过第 1 行”。

Figure 8-12. 限制使用LIMIT返回的行

MATCH...AGAINST

MATCH...AGAINST结构可以用于已经添加了FULLTEXT索引的列（参见“创建FULLTEXT索引”部分）。使用它，您可以像在互联网搜索引擎中一样进行自然语言搜索。与使用WHERE...=或WHERE...LIKE不同，MATCH...AGAINST允许您在搜索查询中输入多个单词，并将它们与FULLTEXT列中的所有单词进行匹配。FULLTEXT索引不区分大小写，因此查询中使用的大小写不重要。

假设您已经在author和title列上添加了FULLTEXT索引，请输入 Example 8-24 中显示的三个查询。第一个查询要求返回包含单词and的任何行。如果您使用的是 MyISAM 存储引擎，由于and是该引擎中的停用词，MySQL 会忽略它，该查询将始终生成一个空集——无论列中存储的内容是什么。否则，如果您使用的是 InnoDB，and是一个允许的单词。第二个查询要求返回包含单词curiosity和shop的任何行，无论顺序如何。最后一个查询对单词tom和sawyer执行相同类型的搜索。Figure 8-13 显示了这些查询的结果。

Example 8-24. 在FULLTEXT索引上使用MATCH...AGAINST

SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('and');
SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('curiosity shop');
SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('tom sawyer');

图 8-13. 在 FULLTEXT 索引上使用 MATCH...AGAINST

在布尔模式中使用 MATCH...AGAINST

如果希望使您的 MATCH...AGAINST 查询具有更大的能力，可以使用 布尔模式。这将改变标准 FULLTEXT 查询的效果，使其搜索任何搜索词的组合，而不是要求所有搜索词都在文本中。列中的单个词的存在会导致搜索返回该行。

布尔模式还允许您在搜索词前加上 + 或 - 符号，以指示它们是必须包含还是排除的。如果普通布尔模式表示，“这些词中的任何一个都可以”，加号表示，“这个词必须存在；否则，不返回该行。” 减号表示，“这个词不能存在；其存在则使该行被排除在返回结果之外。”

示例 8-25 通过两个查询展示了布尔模式。第一个查询要求返回所有包含单词 charles 而不包含单词 species 的行。第二个使用双引号请求返回所有包含确切短语 origin of 的行。图 8-14 显示了这些查询的结果。

示例 8-25. 在布尔模式下使用 MATCH...AGAINST

SELECT author,title FROM classics
 WHERE MATCH(author,title)
 AGAINST('+charles -species' IN BOOLEAN MODE);
SELECT author,title FROM classics
 WHERE MATCH(author,title)
 AGAINST('"origin of"' IN BOOLEAN MODE);

图 8-14. 在布尔模式下使用 MATCH...AGAINST

如您所料，第一个请求只返回查尔斯·狄更斯的《老古玩店》；任何包含单词 species 的行均已排除，因此忽略了查尔斯·达尔文的出版物。

注

第二个查询中有一点值得注意：停用词 of 是搜索字符串的一部分，但仍然被搜索使用，因为双引号覆盖了停用词。

UPDATE...SET

此结构允许您更新字段的内容。如果要更改一个或多个字段的内容，首先需要缩小范围，就像使用 SELECT 命令一样。示例 8-26 展示了两种不同方式使用 UPDATE...SET。您可以在 图 8-15 中查看结果。

示例 8-26. 使用 UPDATE...SET

UPDATE classics SET author='Mark Twain (Samuel Langhorne Clemens)'
 WHERE author='Mark Twain';
UPDATE classics SET category='Classic Fiction'
 WHERE category='Fiction';

图 8-15. 更新经典表中的列

在第一个查询中，马克·吐温的真名塞缪尔·兰霍恩·克莱蒙斯被添加到他的笔名后面的括号中，这只影响了一行。然而，第二个查询影响了三行，因为它将 category 列中所有 Fiction 一词更改为 Classic Fiction。

在执行更新时，您还可以利用已经见过的限定词，比如 LIMIT，以及后续的 ORDER BY 和 GROUP BY 关键字。

ORDER BY

ORDER BY sorts returned results by one or more columns in ascending or descending order. 示例 8-27 shows two such queries, the results of which can be seen in 图 8-16.

示例 8-27. 使用 ORDER BY

SELECT author,title FROM classics ORDER BY author;
SELECT author,title FROM classics ORDER BY title DESC;

图 8-16. 请求结果排序

As you can see, the first query returns the publications by author in ascending alphabetical order (the default), and the second returns them by title in descending order.

If you wanted to sort all the rows by author and then by descending year of publication (to view the most recent first), you would issue the following query:

SELECT author,title,year FROM classics ORDER BY author,year DESC;

This shows that each ascending and descending qualifier applies to a single column. The DESC keyword applies only to the preceding column, year. Because you allow author to use the default sort order, it is sorted in ascending order. You could also have explicitly specified ascending order for that column, with the same results:

SELECT author,title,year FROM classics ORDER BY author ASC,year DESC;

GROUP BY

In a similar fashion to ORDER BY, you can group results returned from queries using GROUP BY, which is good for retrieving information about a group of data. For example, if you want to know how many publications there are of each category in the classics table, you can issue the following query:

SELECT category,COUNT(author) FROM classics GROUP BY category;

which returns the following output:

+-----------------+---------------+
| category        | COUNT(author) |
+-----------------+---------------+
| Classic Fiction |             3 |
| Nonfiction      |             1 |
| Play            |             1 |
+-----------------+---------------+
3 rows in set (0.00 sec)

Joining Tables Together

It is quite normal to maintain multiple tables within a database, each holding a different type of information. For example, consider the case of a customers table that needs to be able to be cross-referenced with publications purchased from the classics table. Enter the commands in 示例 8-28 to create this new table and populate it with three customers and their purchases. 图 8-17 shows the result.

示例 8-28. 创建和填充 customers 表

CREATE TABLE customers (
 name VARCHAR(128),
 isbn VARCHAR(13),
 PRIMARY KEY (isbn)) ENGINE InnoDB;
INSERT INTO customers(name,isbn)
 VALUES('Joe Bloggs','9780099533474');
INSERT INTO customers(name,isbn)
 VALUES('Mary Smith','9780582506206');
INSERT INTO customers(name,isbn)
 VALUES('Jack Wilson','9780517123201');
SELECT * FROM customers;

图 8-17. 创建 customers 表

注意

There’s also a shortcut for inserting multiple rows of data, as in 示例 8-28, in which you can replace the three separate INSERT INTO queries with a single one listing the data to be inserted, separated by commas, like this:

INSERT INTO customers(name,isbn) VALUES
 ('Joe Bloggs','9780099533474'),
 ('Mary Smith','9780582506206'),
 ('Jack Wilson','9780517123201');

Of course, in a proper table containing customers’ details there would also be addresses, phone numbers, email addresses, and so on, but they aren’t necessary for this explanation. While creating the new table, you should have noticed that it has something in common with the classics table: a column called isbn. Because it has the same meaning in both tables (an ISBN refers to a book, and always the same book), we can use this column to tie the two tables together into a single query, as in 示例 8-29.

示例 8-29. 合并两个表为一个 SELECT

SELECT name,author,title FROM customers,classics
 WHERE customers.isbn=classics.isbn;

这个操作的结果如下所示：

+-------------+-----------------+------------------------+
| name        | author          | title                  |
+-------------+-----------------+------------------------+
| Joe Bloggs  | Charles Dickens | The Old Curiosity Shop |
| Mary Smith  | Jane Austen     | Pride and Prejudice    |
| Jack Wilson | Charles Darwin  | The Origin of Species  |
+-------------+-----------------+------------------------+
3 rows in set (0.00 sec)

看看这个查询是如何巧妙地将表格链接在一起，展示了从classics表格中由customers表格中的人购买的出版物？

自然连接

使用NATURAL JOIN，您可以节省一些输入时间并使查询更加清晰。这种连接方式会自动连接具有相同名称的列。因此，要达到与示例 8-29 相同的结果，您可以输入以下内容：

SELECT name,author,title FROM customers NATURAL JOIN classics;

JOIN...ON

如果您希望指定连接两个表的列，请使用JOIN...ON结构，如下所示，以获得与示例 8-29 相同的结果：

SELECT name,author,title FROM customers
 JOIN classics ON customers.isbn=classics.isbn;

使用 AS

您还可以通过使用AS关键字创建别名来节省输入时间并提高查询的可读性。因此，下面的代码也与示例 8-29 的操作相同：

SELECT name,author,title from
 customers AS cust, classics AS class WHERE cust.isbn=class.isbn;

这个操作的结果如下所示：

+-------------+-----------------+------------------------+
| name        | author          | title                  |
+-------------+-----------------+------------------------+
| Joe Bloggs  | Charles Dickens | The Old Curiosity Shop |
| Mary Smith  | Jane Austen     | Pride and Prejudice    |
| Jack Wilson | Charles Darwin  | The Origin of Species  |
+-------------+-----------------+------------------------+
3 rows in set (0.00 sec)

您还可以使用AS来重命名列（无论是否连接表），如下所示：

SELECT name AS customer FROM customers ORDER BY customer;

这导致以下输出：

+-------------+
| customer    |
+-------------+
| Jack Wilson |
| Joe Bloggs  |
| Mary Smith  |
+-------------+
3 rows in set (0.00 sec)

当您的查询中多次引用相同的表名时，别名特别有用。

使用逻辑运算符

您还可以在 MySQL 的WHERE查询中使用逻辑运算符AND、OR和NOT来进一步缩小选择范围。示例 8-30 展示了每个运算符的一个实例，但您可以根据需要混合使用它们。

示例 8-30. 使用逻辑运算符

SELECT author,title FROM classics WHERE
 author LIKE "Charles%" AND author LIKE "%Darwin";
SELECT author,title FROM classics WHERE
 author LIKE "%Mark Twain%" OR author LIKE "%Samuel Langhorne Clemens%";
SELECT author,title FROM classics WHERE
 author LIKE "Charles%" AND author NOT LIKE "%Darwin";

我选择了第一个查询，因为查尔斯·达尔文有可能以他的全名查尔斯·罗伯特·达尔文出现在某些行中。该查询返回任何author列以Charles开头并以Darwin结尾的出版物。第二个查询搜索使用马克·吐温的笔名或他的真实姓名萨缪尔·兰霍恩·克莱门斯写的出版物。第三个查询返回由名为查尔斯但姓不是达尔文的作者写的出版物。

MySQL 函数

您可能会想知道为什么有人要在 PHP 中使用 MySQL 函数，因为 PHP 本身带有大量强大的函数。答案非常简单：MySQL 函数直接在数据库中处理数据。如果使用 PHP，您首先必须从 MySQL 提取原始数据，然后操作它，并执行所需的数据库查询。

在 MySQL 中内置的函数大大减少了执行复杂查询所需的时间及其复杂性。您可以从文档中了解更多有关所有可用的string和date/time函数的信息。

通过 phpMyAdmin 访问 MySQL

虽然使用 MySQL 需要学习这些主要命令及其工作原理，但一旦理解它们，使用诸如phpMyAdmin之类的程序来管理您的数据库和表将会更快更简单。

要完成此操作，请假设您已按照第二章中描述的方式安装了 AMPPS，输入以下内容打开程序（参见图 8-18）：

http://localhost/phpmyadmin

图 8-18. phpMyAdmin 主界面

在 phpMyAdmin 主界面的左侧窗格中，您可以单击以选择要操作的任何表（尽管在创建之前不会有可用的表）。您还可以单击“新建”以创建新数据库。

从这里，您可以执行所有主要操作，例如创建新数据库、添加表、创建索引等等。要了解更多关于 phpMyAdmin 的信息，请查阅文档。

如果您通过本章中的示例与我一起工作，那么恭喜您——这是一段相当漫长的旅程。您已经从学习如何创建 MySQL 数据库开始，通过发出结合多个表的复杂查询，到使用布尔运算符并利用 MySQL 的各种限定符。

在下一章中，我们将开始学习如何进行高效的数据库设计、高级 SQL 技术以及 MySQL 函数和事务。

问题

1. 在 MySQL 查询中，分号的作用是什么？

2. 您将使用哪个命令来查看可用的数据库或表？

3. 如何在本地主机上创建一个名为newuser、密码为newpass并具有对数据库newdatabase中所有内容访问权限的新 MySQL 用户？

4. 如何查看表的结构？

5. MySQL 索引的目的是什么？

6. FULLTEXT索引提供了哪些好处？

7. 什么是停用词？

8. SELECT DISTINCT和GROUP BY都导致显示只显示每个列中值的一个输出行，即使多行包含该值。SELECT DISTINCT和GROUP BY之间的主要区别是什么？

9. 使用SELECT...WHERE结构，如何返回只包含author列中的classics表中某处包含单词Langhorne的行？

10. 需要在两个表中定义什么才能使它们可以连接在一起？

参见附录 A 中的“第八章答案”，获取这些问题的答案。

第九章：精通 MySQL

第八章为您提供了使用结构化查询语言的关系数据库实践的良好基础。您已经了解了如何创建数据库及其包含的表，以及插入、查找、更改和删除数据。

掌握了这些知识后，我们现在需要看看如何设计数据库以实现最大速度和效率。例如，您如何决定将哪些数据放在哪个表中？多年来，已经制定了许多指导原则，如果您遵循这些原则，可以确保您的数据库高效，并且能够随着数据的增加而增长。

数据库设计

在开始创建数据库之前，正确设计数据库非常重要；否则，您几乎肯定需要返回并更改它，分割一些表，合并其他表，并移动各种列，以建立 MySQL 可以轻松使用的合理关系。

坐下来，拿起一张纸和一支铅笔，写下您认为您和您的用户可能会问的一些查询是一个很好的起点。对于在线书店的数据库，您的一些问题可能是：

• 数据库中有多少作者、书籍和客户？

• 哪位作者写了某本书？

• 哪些书是某个作者写的？

• 哪本书是最贵的？

• 哪本书是畅销书？

• 哪些书今年没有销售？

• 特定客户购买了哪些书籍？

• 哪些书与其他书同时购买？

当然，在这样的数据库上，您可以提出许多其他查询，但即使这样一个小样本也将开始为您提供如何布置表的见解。例如，书籍和 ISBN 可能可以合并到一个表中，因为它们密切相关（稍后我们将讨论一些微妙之处）。相反，书籍和客户应该在不同的表中，因为它们之间的连接非常松散。一个客户可以购买任何一本书，甚至是多本书，而一本书可以被许多客户购买，也可能被更多潜在客户忽略。

当您计划对某些内容进行大量搜索时，一个搜索通常可以从拥有自己的表中受益。当事物之间的耦合松散时，最好将它们放在单独的表中。

考虑到这些简单的经验法则，我们可以猜测我们至少需要三个表来容纳所有这些查询：

作者

将有很多搜索关于作者的信息，其中许多作者合作撰写了书籍，并且许多作者将出现在合集中。将每位作者的所有信息一起列出，并与该作者关联，将为搜索产生最佳结果，因此有一个 作者 表。

书籍

许多书籍有不同的版本。有时它们会更改出版商，有时它们与其他无关的书籍有相同的标题。因此，书籍和作者之间的链接足够复杂，需要一个单独的表格来处理。

顾客

明确起见，顾客应该有自己的表格，因为他们可以自由购买任何作者的任何书籍。

主键：关系数据库的关键

利用关系数据库的强大功能，我们可以在一个地方定义每位作者、每本书和每位顾客的信息。显然，我们感兴趣的是它们之间的链接——比如谁写了每本书以及谁购买了它——但我们可以通过仅在三个表格之间建立链接来存储这些信息。我将向你展示基本原则，然后你只需实践，它会变得自然而然。

这个神奇的方法是给每个作者一个唯一的标识符。我们将为每本书和每位顾客做同样的事情。我们在前一章中已经看到了这样做的方法：主键。对于书籍来说，使用 ISBN 是有意义的，尽管然后你必须处理具有不同 ISBN 的多个版本。对于作者和顾客，你可以简单地分配任意的键值，上一章中提到的AUTO_INCREMENT功能使这一过程变得容易。

简而言之，每个表格将围绕着你可能经常搜索的某个对象设计——在本例中是作者、书籍或顾客——并且该对象将有一个主键。不要选择可能对不同对象具有相同值的键。ISBN 是一个少见的情况，产业已经提供了一个可以依赖的主键，对于每个产品都是唯一的。大多数情况下，为此目的创建一个任意的键，使用AUTO_INCREMENT。

规范化

将数据分成表格并创建主键的过程称为规范化。它的主要目标是确保每个信息片段只出现在数据库中一次。数据重复是低效的，因为它使数据库变得比必要的更大，从而减慢访问速度。更重要的是，重复数据的存在会增加你只更新重复数据的一行的风险，从而在数据库中创建不一致性，可能引发严重的错误。

例如，如果你在作者表格和书籍表格中列出书籍的标题，并且你需要纠正标题中的印刷错误，你必须搜索这两个表格，并确保在书名出现的每个地方都做出相同的更改。最好将书名保留在一个地方，并在其他地方使用 ISBN。

但是在将数据库拆分成多个表格的过程中，重要的是不要走得太远，创建比必要更多的表格，这也会导致设计效率低下和访问速度变慢。

幸运的是，关系模型的发明者 E·F·科德分析了规范化的概念，并将其分为三个称为第一、第二和第三范式的独立模式。如果按顺序修改数据库以满足这些范式中的每一个，您将确保您的数据库在快速访问和最小内存和磁盘空间使用方面达到最佳平衡。

要了解规范化过程是如何工作的，请从表 9-1 中的相当庞大的数据库开始，该表格显示了一个包含所有作者姓名、书名和（虚构的）客户详细信息的表格。您可以考虑它是一个旨在跟踪哪些客户订购了书籍的第一个尝试。显然，这是一种低效的设计，因为数据到处重复（重复部分已经高亮显示），但这代表了一个起点。

表 9-1. 数据库表的高度低效设计

| 作者 1 | 作者 2 | 标题 | ISBN | 价格 | 客户姓名 | 客户地址 | 购买日期 | | --- | --- | --- | --- | --- | --- | --- | | 大卫·斯克拉 | 亚当·特拉彻伯格 | PHP Cookbook | 0596101015 | 44.99 | 艾玛·布朗 | 加利福尼亚州洛杉矶市彩虹路 1565 号 | 2009 年 3 月 3 日 | | 丹尼·古德曼 |   | 动态 HTML | 0596527403 | 59.99 | 达伦·赖德 | 弗吉尼亚州里士满市艾米丽大道 4758 号 | 2008 年 12 月 19 日 | | 休·威廉姆斯 | 大卫·莱恩 | PHP 和 MySQL | 0596005436 | 44.95 | 厄尔·B·瑟斯顿 | 肯塔基州法兰克福市格雷戈里大道 862 号 | 2009 年 6 月 22 日 | | 大卫·斯克拉 | 亚当·特拉彻伯格 | PHP Cookbook | 0596101015 | 44.99 | 达伦·赖德 | 弗吉尼亚州里士满市艾米丽大道 4758 号 | 2008 年 12 月 19 日 | | 拉斯穆斯·勒多夫 | 凯文·塔特罗 & 彼得·麦金泰尔 | 编程 PHP | 0596006815 | 39.99 | 大卫·米勒 | 马萨诸塞州沃尔瑟姆市思达克莲街 3647 号 | 2009 年 1 月 16 日 |

在接下来的三个部分中，我们将检查这个数据库设计，您将看到我们如何通过消除各种重复条目并将单个表格拆分为包含一种类型数据的多个表格来改进它。

第一范式

要使数据库符合第一范式，它必须满足三个要求：

• 不应存在包含相同类型数据的重复列。

• 所有列应包含单一值。

• 每行应该有一个主键，以唯一标识每一行。

顺序查看这些要求时，您应立即注意到作者 1和作者 2列均属于重复数据类型。因此，我们已经有了一个目标列，可以将其拉入单独的表格，因为重复的作者列违反了规则 1。

其次，最终书籍《编程 PHP》列出了三位作者。我通过让凯文·塔特罗和彼得·麦金泰尔共享作者 2列来处理这一点，这违反了规则 2——这也是将作者详细信息转移到单独表格的另一个原因。

然而，规则 3 已得到满足，因为 ISBN 的主键已经创建。

表 9-2 显示从表 9-1 中去除作者列的结果。尽管仍然有突出显示的重复项，但看起来清爽多了。

表 9-2. 从表 9-1 中去除作者列的结果

标题	ISBN	价格	客户姓名	客户地址	购买日期
PHP Cookbook	0596101015	44.99	艾玛·布朗	加利福尼亚州洛杉矶市彩虹路 1565 号	2009 年 3 月 3 日
动态 HTML	0596527403	59.99	达伦·赖德	弗吉尼亚州里士满市艾米丽大道 4758 号	2008 年 12 月 19 日
PHP 与 MySQL	0596005436	44.95	厄尔·B·瑟斯顿	肯塔基州法兰克福市格雷戈里巷 862 号	2009 年 6 月 22 日
PHP Cookbook	0596101015	44.99	达伦·赖德	弗吉尼亚州里士满市艾米丽大道 4758 号	2008 年 12 月 19 日
编程 PHP	0596006815	39.99	大卫·米勒	马萨诸塞州沃尔瑟姆市雪松巷 3647 号	2009 年 1 月 16 日

显示在表 9-3 中的新作者表规模小而简单。它只列出标题的 ISBN 及其作者。如果一本书有多位作者，其他作者将有他们自己的行。起初，你可能对这个表感到不适，因为你无法知道哪位作者写了哪本书。但别担心：MySQL 可以快速告诉你。你要做的就是告诉它你想获取信息的书籍，MySQL 将使用其 ISBN 在作者表中进行搜索，仅需几毫秒。

表 9-3. 新的作者表

ISBN	作者
0596101015	大卫·斯克拉
0596101015	亚当·特拉切伯格
0596527403	丹尼·古德曼
0596005436	休·E·威廉姆斯
0596005436	大卫·莱恩
0596006815	拉斯穆斯·勒多夫
0596006815	凯文·塔特罗
0596006815	皮特·麦金泰尔

正如我之前提到的，当我们创建书籍表时，ISBN 将成为其主键。我在这里提到这一点是为了强调 ISBN 不是作者表的主键。在现实世界中，作者表也应该有一个主键，以便每位作者都有一个唯一的标识符。

所以，在作者表中，ISBN只是一列，用于加快搜索速度，我们可能会将其作为一个键，但不是主键。实际上，在这个表中它不能成为主键，因为它不是唯一的：同一个 ISBN 在两个或更多作者合作撰写的书籍中会出现多次。

因为我们将用它来链接另一张表中的作者与书籍，所以这列被称为外键。

注意

键（也称为索引）在 MySQL 中有几个目的。定义键的根本原因是加快搜索速度。在第八章中的示例中，您已经看到键在WHERE子句中用于搜索。但键还可以用于唯一标识一个项。因此，唯一键通常用作一张表的主键，并用作外键以将该表中的行链接到另一张表中的行。

第二范式

第一范式处理多列之间的重复数据（或冗余）。第二范式则涉及多行之间的冗余。要实现第二范式，你的表必须已经处于第一范式。完成这一步后，通过识别数据在不同位置重复的列，然后将其移至自己的表中，即可实现第二范式。

因此，让我们再次看看表 9-2。请注意，达伦·赖德购买了两本书，因此他的详细信息重复了。这告诉我们需要将客户列单独提取到自己的表中。表 9-4 显示了从表 9-2 中移除客户列的结果。

表 9-4. 新的标题表

ISBN	标题	价格
0596101015	PHP Cookbook	44.99
0596527403	Dynamic HTML	59.99
0596005436	PHP and MySQL	44.95
0596006815	Programming PHP	39.99

正如您所见，在表 9-4 中，现在仅剩下四本唯一书籍的ISBN、标题和价格列，因此这现在构成了一个高效且自包含的表，满足了第一和第二范式的要求。在此过程中，我们设法将信息减少到与书名密切相关的数据。此表还可以包括出版年份、页数、再版次数等详细信息，因为这些详细信息也与书名密切相关。唯一的规则是不能在任何列中放入可能对于单本书有多个值的列，因为那样我们就必须在多行中列出同一本书，从而违反第二范式。例如，恢复作者列将违反这种规范化。

然而，看到提取的客户列，现在在表 9-5，我们可以看到仍然有更多的规范化工作要做，因为达伦·赖德的详细信息仍然重复。同时，也可以认为第一范式规则 2（所有列应包含单个值）尚未得到正确遵守，因为地址确实需要拆分为地址、城市、州和邮政编码的单独列。

表 9-5. 来自表 9-2 的客户详细信息

ISBN	客户名称	客户地址	购买日期
0596101015	Emma Brown	1565 Rainbow Road, Los Angeles, CA 90014	Mar 03 2009
0596527403	Darren Ryder	4758 Emily Drive, Richmond, VA 23219	Dec 19 2008
0596005436	Earl B. Thurston	862 Gregory Lane, Frankfort, KY 40601	Jun 22 2009
0596101015	Darren Ryder	4758 Emily Drive, Richmond, VA 23219	Dec 19 2008
0596006815	David Miller	3647 Cedar Lane, Waltham, MA 02154	Jan 16 2009

我们必须进一步分割此表，以确保每个客户的详细信息仅输入一次。因为 ISBN 不能用作标识客户（或作者）的主键，所以必须创建一个新的键。

表 9-6 是将Customers表规范化为第一和第二范式的结果。现在每个客户都有一个名为CustNo的唯一客户号，它是表的主键，很可能是通过AUTO_INCREMENT创建的。客户地址的所有部分也已分开为不同的列，以便于搜索和更新。

表 9-6. 新客户表

客户号	姓名	地址	城市	州	邮编
1	Emma Brown	1565 Rainbow Road	洛杉矶	加利福尼亚	90014
2	Darren Ryder	4758 Emily Drive	Richmond	VA	23219
3	Earl B. Thurston	862 Gregory Lane	Frankfort	KY	40601
4	David Miller	3647 Cedar Lane	Waltham	MA	02154

同时，为了规范化表 9-6，我们不得不删除有关客户购买的信息，否则每本书购买都会有客户详细信息的多个实例。相反，购买数据现在放在一个称为Purchases的新表中（参见表 9-7）。

表 9-7. 新购买表

客户号	ISBN	日期
1	0596101015	Mar 03 2009
2	0596527403	Dec 19 2008
2	0596101015	Dec 19 2008
3	0596005436	Jun 22 2009
4	0596006815	Jan 16 2009

在这里，来自表 9-6 的CustNo列被重用为将Customers和Purchases表联系在一起的键。因为ISBN列也在这里重复，所以这个表也可以与Authors和Titles表关联。

CustNo 列在Purchases表中可能是一个有用的键，但不是主键。一个客户可以购买多本书（甚至同一本书的多本副本），因此CustNo列不是主键。事实上，Purchases表没有主键。这没关系，因为我们不希望跟踪唯一的购买。如果一个客户在同一天购买了两本相同的书，我们会允许两行具有相同的信息。为了方便搜索，我们可以将CustNo和ISBN都定义为键，但不是主键。

注意

现在有四个表，比我们最初预计需要的三个多了一个。我们通过规范化过程做出了这个决定，方法是遵循第一和第二范式规则，这明确了还需要一个名为 Purchases 的第四个表。

现在我们有的表是 Authors（表 9-3）、Titles（表 9-4）、Customers（表 9-6）和 Purchases（表 9-7），我们可以使用 CustNo 或 ISBN 键将每个表与任何其他表链接起来。

例如，要查看 Darren Ryder 购买了哪些书，你可以在表 9-6，即 Customers 表中查找他；在这里你会看到他的 CustNo 是 2。拿着这个编号，你现在可以去表 9-7，即 Purchases 表；查看这里的 ISBN 列，你会看到他在 2008 年 12 月 19 日购买了书籍 0596527403 和 0596101015。对于人类来说，这看起来是一件很麻烦的事情，但对 MySQL 来说却并不难。

要确定这些标题是什么，你可以参考表 9-4，即 Titles 表，并看到他购买的书是 Dynamic HTML 和 PHP Cookbook。如果你想知道这些书的作者，你也可以使用刚刚在表 9-3 查找的 ISBN，即 Authors 表，你会发现 ISBN 0596527403 的 Dynamic HTML 是由 Danny Goodman 写的，而 ISBN 0596101015 的 PHP Cookbook 是由 David Sklar 和 Adam Trachtenberg 写的。

第三范式

一旦你有一个符合第一和第二范式的数据库，它就已经非常完善了，你可能不需要进一步修改它。然而，如果你希望对数据库非常严格，你可以确保它符合第三范式，这要求那些不直接依赖于主键但依赖于表中另一个值的数据也被移到单独的表中，根据依赖关系。

例如，在表 9-6 中，Customers 表中可以认为 State、City 和 Zip 键与每个客户并非直接相关，因为许多其他人的地址中也会有相同的细节。然而，它们在彼此之间是直接相关的，因为 Address 依赖于 City，而 City 又依赖于 State。

因此，为了满足表 9-6 的第三范式，你需要将它分解成表 9-8 至表 9-11。

表 9-8. 第三范式 Customers 表

CustNo	Name	Address	Zip
1	Emma Brown	1565 Rainbow Road	90014
2	Darren Ryder	4758 Emily Drive	23219
3	Earl B. Thurston	862 Gregory Lane	40601
4	David Miller	3647 Cedar Lane	02154

表 9-9. 第三范式邮政编码表

邮政编码	城市 ID
90014	1234
23219	5678
40601	4321
02154	8765

表 9-10. 第三范式城市表

城市 ID	名称	州 ID
1234	洛杉矶	5
5678	里士满	46
4321	法兰克福	17
8765	沃尔瑟姆	21

表 9-11. 第三范式州表

州 ID	名称	缩写
5	加利福尼亚州	CA
46	弗吉尼亚州	VA
17	肯塔基州	KY
21	马萨诸塞州	MA

那么，您将如何使用这四个表的集合，而不是单个的表 9-6？好吧，您将在表 9-8 中查找Zip code，然后在表 9-9 中找到匹配的CityID。有了这些信息，您可以在表 9-10 中查找城市名称，然后再找到StateID，您可以在表 9-11 中使用它来查找州的名称。

虽然以这种方式使用第三范式可能看起来有些多余，但它确实有其优势。例如，请参阅表 9-11，在这里可以同时包括一个州的名称及其两字母缩写。如果您希望，它还可以包含人口详细信息和其他人口统计数据。

注意

表 9-10 还可以包含更多对您和/或您的客户有用的本地化人口统计信息。通过拆分这些数据片段，您可以更轻松地在未来维护您的数据库，如果有必要添加列的话。

决定是否使用第三范式可能会有些棘手。您的评估应基于以后可能需要添加的数据。如果您绝对确定客户的姓名和地址是您所需要的全部信息，那么您可能希望跳过最后这个规范化阶段。

另一方面，假设您正在为美国邮政服务等大型组织编写数据库。如果一个城市改名了，您会怎么做？如果像表 9-6 这样的表，您将需要在每个该城市的实例上进行全局搜索和替换。但是如果您根据第三范式设置了数据库，您只需要在表 9-10 中更改一个条目，即可反映在整个数据库中。

因此，我建议您问自己两个问题，以帮助您决定是否对任何表执行第三范式规范化：

• 是否可能需要向这个表中添加许多新列？

• 这个表的任何字段在任何时候是否需要全局更新？

如果两个答案中有任何一个是肯定的，那么您可能应该考虑执行这个最后的规范化阶段。

不使用规范化的时机

现在你已经了解了规范化的所有内容，我要告诉你为什么在高流量站点上应该把这些规则丢掉。没错——在会导致 MySQL 抖动的站点上，你永远不应该完全规范化你的表。

规范化要求在多个表之间分布数据，这意味着每个查询需要对 MySQL 进行多次调用。在一个非常流行的站点上，如果你有规范化的表，一旦并发用户超过几十个，你的数据库访问速度将显著减慢，因为它们将在它们之间创建数百次数据库访问。事实上，我甚至会说，在你能够看到 MySQL 阻塞之前，你应该反规范化任何常常查找的数据。

如果你的表中存在重复的数据，你可以显著减少需要进行的额外请求的数量，因为大多数你想要的数据在每个表中都是可用的。这意味着你只需在查询中添加一个额外的列，该字段将对所有匹配结果可用。

当然，你必须处理之前提到的缺点，比如消耗大量的磁盘空间，并确保在需要修改数据时更新每一个副本。

多次更新可以自动化。MySQL 提供了一个名为触发器的功能，可以在你做出更改时自动修改数据库。（触发器超出了本书的范围。）另一种传播冗余数据的方法是设置一个定期运行的 PHP 程序来保持所有副本同步。该程序从“主”表中读取更改并更新所有其他表。（你将在下一章学习如何从 PHP 访问 MySQL。）

然而，在你对 MySQL 非常熟悉之前，我建议你完全规范化所有表（至少达到第一和第二范式），因为这将养成良好的习惯并使你处于良好的状态。只有当你真正开始看到 MySQL 日志阻塞时，你才应该考虑反规范化。

关系

MySQL 被称为关系型数据库管理系统，因为它的表不仅存储数据，还存储数据之间的关系。有三类关系。

一对一

一个一对一关系就像（传统的）婚姻：每个项目只与另一种类型的项目有关系。这种情况非常罕见。例如，一个作者可以写多本书，一本书可以有多个作者，甚至一个地址可以与多个客户关联。也许在本章中迄今为止最好的一个一对一关系的例子是一个州的名称与其两个字符缩写之间的关系。

但是，为了论证，让我们假设在任何地址只能始终存在一个客户。在这种情况下，图 9-1 中的 Customers–Addresses 关系是一对一关系：每个地址只有一个客户居住，并且每个地址只能有一个客户。

图 9-1. 客户表，表 9-8，分成两个表

通常，当两个项目具有一对一关系时，您只需将它们作为同一表中的列包括。将它们拆分为单独的表的两个原因是：

• 您希望做好准备，以防关系以后发生变化，并且不再是一对一。

• 表格有很多列，你认为通过拆分可以提高性能或维护。

当然，当您在现实世界中构建自己的数据库时，您将不得不创建一对多的客户-地址关系（一个 地址，多个 客户）。

一对多

一对多（或多对一）关系发生在一个表中的一行与另一个表中的多行相关联时。如果允许在同一地址有多个客户，如前所述，表 9-8 将形成一对多关系，这就是为什么在这种情况下必须拆分它。

因此，在 图 9-1 内查看表 9-8a 时，您可以看到它与 表 9-7 具有一对多关系，因为表 9-8a 中每个客户只有一个。然而 表 9-7，Purchases 表，可以（也确实）包含来自同一客户的多次购买。因此，一个 客户与 多个 购买之间存在关系。

您可以在 图 9-2 中并排查看这两个表，其中连接每个表中行的虚线从左侧表的单行开始，但可以连接到右侧表的多行。这种一对多关系也是描述多对一关系的首选方案，此时通常会交换左右表以将它们视为一对多关系。

图 9-2. 说明两个表之间的关系

要在关系数据库中表示一对多关系，请为“多”创建一个表和“一”创建一个表。 “多”表必须包含一个列，该列列出“一”表的主键。因此，Purchases 表将包含一个列，列出客户的主键。

多对多

在 多对多关系 中，一个表中的多行与另一个表中的多行关联。要创建此关系，请添加一个包含其他每个表中相同关键列的第三个表。该第三个表除了连接其他表之外什么也不包含，因为它的唯一目的就是连接其他表。

Table 9-12 就是这样一张表。它是从 Table 9-7，即 Purchases 表中提取的，但省略了购买日期信息。它包含每个售出书籍的 ISBN 及其购买者的客户号的副本。

Table 9-12. 一个中介表

CustNo	ISBN
1	0596101015
2	0596527403
2	0596101015
3	0596005436
4	0596006815

有了这个中介表，您可以通过一系列的关联遍历数据库中的所有信息。您可以以地址作为起点，查找住在该地址的客户购买的任何书籍的作者。

例如，假设您想了解邮政编码为 23219 的购买情况。在表 9-8b 中查找该邮政编码，您会发现客户号为 2 的客户至少购买了一件数据库中的物品。此时，您可以使用表 9-8a 查找该客户的姓名，或使用新的中介 Table 9-12 查看购买的书籍。

从这里，您将发现购买了两个标题，并可以跟踪它们回到 Table 9-4 查找这些书籍的标题和价格，或者到 Table 9-3 查看这些书籍的作者。

如果您觉得这实际上是将多个一对多关系结合在一起，那么您是完全正确的。为了说明这一点，Figure 9-3 将三个表结合在一起。

图 9-3. 通过第三张表创建多对多关系

跟随左侧表中的任何邮政编码到相关的客户 ID。从那里，您可以链接到中间表，该表通过链接客户 ID 和 ISBN 连接左侧和右侧表。现在您只需跟随一个 ISBN 到右侧表，即可查看它与哪本书相关联。

您还可以使用中介表从书名反向查找到邮政编码。Titles 表可以告诉您 ISBN，然后您可以在中间表中使用这个 ISBN 找到购买该书的客户的 ID 号码，最后您可以使用 Customers 表将客户 ID 号码与客户的邮政编码匹配。

数据库与匿名性

使用关系的一个有趣方面是，你可以累积关于某个项（如客户）的大量信息，而不需要实际了解该客户是谁。请注意，在前面的示例中，我们从客户的邮政编码到客户的购买记录，再返回，而无需了解客户的姓名。数据库可以用于跟踪人员，但也可以帮助保护人们的隐私，同时返回关于购买的信息，而不泄露其他客户详细信息，例如。

事务

在一些应用程序中，确保一系列查询按正确顺序运行，并且每个查询都成功完成非常重要。例如，假设你正在创建一系列用于从一个银行账户转账到另一个账户的查询。你不希望发生以下事件之一：

• 你将资金添加到第二个账户，但当你尝试从第一个账户扣除时，更新失败，现在两个账户都有资金。

• 你从第一个银行账户中扣除资金，但是添加到第二个账户的更新请求失败了，资金就这样消失了。

如你所见，在这种类型的事务中，查询的顺序非常重要，而且事务的每个部分都必须成功完成。但是，如何确保这种情况发生呢？因为一旦发生了查询，它就无法撤销了？你是否需要跟踪事务的所有部分，然后逐个撤销它们？答案绝对是否定的，因为 MySQL 提供了强大的事务处理功能，正好可以处理这些情况。

另外，事务允许多个用户或程序同时访问数据库。MySQL 通过确保所有事务排队执行，并确保用户或程序依次执行，而不会互相干扰，无缝处理这一过程。

事务存储引擎

要使用 MySQL 的事务功能，你必须使用 MySQL 的 InnoDB 存储引擎（从版本 5.5 开始默认使用）。如果不确定你的代码将在哪个版本的 MySQL 上运行，而不是假设 InnoDB 是默认引擎，你可以在创建表时强制使用它，如下所示。

通过在示例 9-1 中输入命令来创建一个银行账户表。（记住，为此你需要访问 MySQL 命令行，并且必须已经选择了一个适合创建此表的数据库。）

示例 9-1. 创建一个事务准备的表

CREATE TABLE accounts (
 number INT, balance FLOAT, PRIMARY KEY(number)
 ) ENGINE InnoDB;
DESCRIBE accounts;

此示例的最后一行显示了新表的内容，以确保它已正确创建。输出应如下所示：

+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| number  | int(11) | NO   | PRI | NULL    |       |
| balance | float   | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

现在让我们在表内创建两行，以便你可以练习使用事务。在示例 9-2 中输入这些命令。

示例 9-2. 填充 accounts 表

INSERT INTO accounts(number, balance) VALUES(12345, 1025.50);
INSERT INTO accounts(number, balance) VALUES(67890, 140.00);
SELECT * FROM accounts;

第三行显示表格内容以确认行已正确插入。输出应如下所示：

+--------+---------+
| number | balance |
+--------+---------+
|  12345 |  1025.5 |
|  67890 |     140 |
+--------+---------+
2 rows in set (0.00 sec)

创建并预填充了这个表后，你可以开始使用事务了。

使用 BEGIN

MySQL 的事务以 BEGIN 或 START TRANSACTION 语句开始。键入 示例 9-3 中的命令将事务发送给 MySQL。

示例 9-3. MySQL 事务

BEGIN;
UPDATE accounts SET balance=balance+25.11 WHERE number=12345;
COMMIT;
SELECT * FROM accounts;

这个事务的结果由最后一行显示，应如下所示：

+--------+---------+
| number | balance |
+--------+---------+
|  12345 | 1050.61 |
|  67890 |     140 |
+--------+---------+
2 rows in set (0.00 sec)

正如你所见，账号 12345 的余额增加了 25.11，现在是 1050.61。你也许注意到了 示例 9-3 中的 COMMIT 命令，接下来将对其进行解释。

使用 COMMIT

当你确认事务中的一系列查询已成功完成时，使用 COMMIT 命令将所有更改提交到数据库。在接收到 COMMIT 命令之前，MySQL 认为你所做的所有更改只是临时的。这个功能让你有机会通过不发送 COMMIT 而是发出 ROLLBACK 命令来取消事务。

使用 ROLLBACK

使用 ROLLBACK 命令，你可以告诉 MySQL 忘记从事务开始以来的所有查询，并取消事务。通过在 示例 9-4 中输入资金转移事务来看看它的运行效果。

示例 9-4. 资金转移事务

BEGIN;
UPDATE accounts SET balance=balance-250 WHERE number=12345;
UPDATE accounts SET balance=balance+250 WHERE number=67890;
SELECT * FROM accounts;

输入完这些行后，你应该看到以下结果：

+--------+---------+
| number | balance |
+--------+---------+
|  12345 |  800.61 |
|  67890 |     390 |
+--------+---------+
2 rows in set (0.00 sec)

第一个银行账户的值比之前少了 250，第二个账户增加了 250；你已经在它们之间转移了 250 的价值。但假设出现了问题，你希望撤销这笔交易。你只需按照 示例 9-5 中的命令执行即可。

示例 9-5. 使用 ROLLBACK 取消事务

ROLLBACK;
SELECT * FROM accounts;

现在你应该看到以下输出，显示两个账户的先前余额已经恢复，因为整个事务已通过 ROLLBACK 命令被取消：

+--------+---------+
| number | balance |
+--------+---------+
|  12345 | 1050.61 |
|  67890 |     140 |
+--------+---------+
2 rows in set (0.00 sec)

使用 EXPLAIN

MySQL 提供了一个强大的工具来调查你向其发出的查询的解释方式。使用 EXPLAIN，你可以获取任何查询的快照，以查明是否可以以更好或更有效的方式发出它。示例 9-6 展示了如何与你之前创建的 accounts 表一起使用它。

示例 9-6. 使用 EXPLAIN 命令

EXPLAIN SELECT * FROM accounts WHERE number='12345';

这个 EXPLAIN 命令的结果应该如下所示：

+--+------+--------+------+-----+--------+-------+----+-----+----+------+-----+
|id|select|table   |part- |type |possible|key    |key |ref  |rows|fil-  |Extra|
|  |_type |        |itions|     |_keys   |       |_len|     |    |tered |     |
+--+------+--------+------+-----+--------+-------+----+-----+----+------+-----+
|1 |SIMPLE|accounts|NULL  |const|PRIMARY |PRIMARY|4   |const|1   |100.00|NULL |
+--+------+--------+------+-----+--------+-------+----+-----+----+------+-----+
1 row in set (0.00 sec)

MySQL 在此给出的信息如下：

select_type

选择类型是 SIMPLE。如果你正在将表连接在一起，这将显示连接类型。

table

当前正在查询的表是 accounts。

type

查询类型为const。从效率最低到最高的类型，可能的值可以是ALL、index、range、ref、eq_ref、const、system和NULL。

possible_keys

存在一个PRIMARY键，这意味着访问应该很快。

key

实际使用的键是PRIMARY。这很好。

key_len

键长度为4。这是 MySQL 将使用的索引字节数。

ref

ref列显示与键一起使用的列或常量。在这种情况下，正在使用一个常量键。

rows

此查询需要搜索的行数为1。这很好。

每当你有一个查询似乎执行时间比你预期的长时，请尝试使用EXPLAIN来查看可以优化的地方。你将会发现哪些键（如果有的话）正在使用，它们的长度等信息，从而可以相应地调整你的查询或表的设计。

注意

当您完成临时accounts表的实验后，可能希望通过输入以下命令将其删除：

DROP TABLE accounts;

备份与恢复

无论您在数据库中存储哪种类型的数据，它对您都必须有一定的价值，即使只是重新输入它所需的时间成本，如果硬盘发生故障。因此，重要的是您保持备份以保护您的投资。此外，有时您必须将数据库迁移到新服务器；通常最好的方法是先备份它。还重要的是，您定期测试备份以确保它们有效，并且在需要时能够使用。

幸运的是，使用mysqldump命令轻松备份和恢复 MySQL 数据。

使用 mysqldump

使用mysqldump，您可以将一个或多个数据库转储到一个或多个文件中，这些文件包含重新创建所有表并填充其数据所需的所有指令。此命令还可以生成CSV（逗号分隔值）和其他分隔文本格式的文件，甚至是 XML 格式的文件。其主要缺点是在备份时必须确保没有人在写入表。有各种方法可以做到这一点，但最简单的方法是在运行mysqldump之前关闭 MySQL 服务器，然后在mysqldump完成后重新启动服务器。

或者，在运行mysqldump之前，您可以锁定要备份的表。要锁定表以便读取数据（因为我们想要读取数据），请从 MySQL 命令行执行以下命令：

LOCK TABLES *`tablename1`* READ, *`tablename2`* READ ...

然后，要释放锁，请输入以下命令：

UNLOCK TABLES;

默认情况下，mysqldump的输出只是简单地打印出来，但您可以通过>重定向符号将其捕获到文件中。

mysqldump命令的基本格式如下所示：

mysqldump -u *user* -p*password database*

但在转储数据库内容之前，必须确保 mysqldump 在你的路径中，否则需在命令中指定其位置。表 9-13 显示了在不同安装和操作系统中可能的程序位置，这些已在第二章中涵盖。如果使用不同的安装程序，则其位置可能略有不同。

表 9-13. 不同安装的 mysqldump 的可能位置

操作系统和程序	可能的文件夹位置
Windows AMPPS	C:\Program Files\Ampps\mysql\bin
macOS AMPPS	/Applications/ampps/mysql/bin
Linux AMPPS	/Applications/ampps/mysql/bin

因此，要将你在第八章中创建的publications数据库的内容转储到屏幕上，首先退出 MySQL，然后输入示例 9-7 中的命令（必要时指定mysqldump的完整路径）。

示例 9-7. 将 publications 数据库转储到屏幕上

mysqldump -u *user* -p*password* publications

确保用你的 MySQL 安装的正确详细信息替换 user 和 password。如果用户没有设置密码，可以省略命令的这部分，但 -u user 部分是必需的，除非你以 root 身份执行而无需密码并且正在以 root 身份执行（不推荐）。执行此命令的结果将类似于 图 9-4。

图 9-4. 将 publications 数据库转储到屏幕上

创建备份文件

现在 mysqldump 可以工作，并且已验证其正确输出到屏幕，你可以使用 > 重定向符号直接将备份数据发送到文件。假设你希望将备份文件命名为 publications.sql，则输入 示例 9-8 中的命令（记得替换 user 和 password 为正确的详细信息）。

注意

示例 9-8 中的命令将备份文件存储到当前目录。如果需要保存到其他位置，应在文件名之前插入文件路径。还必须确保备份目录已设置正确的权限，允许写入文件，但不允许任何非特权用户访问！

示例 9-8. 将 publications 数据库转储到文件中

mysqldump -u *user* –p*password* publications > publications.sql

注意

有时在使用 Windows PowerShell 访问 MySQL 时可能会出现错误，而在标准命令提示符窗口中则看不到这些错误。

如果将备份文件 echo 到屏幕或加载到文本编辑器中，你会看到它包含如下的 SQL 命令序列：

DROP TABLE IF EXISTS 'classics';
CREATE TABLE 'classics' (
  'author' varchar(128) default NULL,
  'title' varchar(128) default NULL,
  'category' varchar(16) default NULL,
  'year' smallint(6) default NULL,
  'isbn' char(13) NOT NULL default '',
  PRIMARY KEY  ('isbn'),
  KEY 'author' ('author'(20)),
  KEY 'title' ('title'(20)),
  KEY 'category' ('category'(4)),
  KEY 'year' ('year'),
  FULLTEXT KEY 'author_2' ('author','title')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这是一个智能代码，可用于从备份中恢复数据库，即使当前数据库已存在；它将首先删除需要重新创建的任何表，从而避免可能出现的 MySQL 错误。

备份单个表

要仅从数据库备份中备份单个表（例如从publications数据库的classics表），您应首先从 MySQL 命令行内锁定该表，通过发出以下命令：

LOCK TABLES publications.classics READ;

这确保 MySQL 继续运行以进行读取，但不能进行写入。然后，保持 MySQL 命令行打开状态，使用操作系统命令行发出以下命令：

mysqldump -u *user* -p*password* publications classics > classics.sql

现在，您必须从 MySQL 命令行内释放表锁，通过在第一个终端窗口中输入以下命令，解锁在当前会话期间已锁定的所有表：

UNLOCK TABLES;

备份所有表

如果要一次备份所有 MySQL 数据库（包括系统数据库如mysql），可以使用类似示例 9-9 中的命令，这样可以恢复整个 MySQL 数据库安装。记得在需要时使用锁定。

示例 9-9. 将所有 MySQL 数据库转储到文件

mysqldump -u *user* -p*password* --all-databases > all_databases.sql

注意

当然，备份的数据库文件中不仅仅是几行 SQL 代码。建议您花几分钟时间查看一些文件，以熟悉备份文件中出现的命令类型及其工作原理。

从备份文件恢复

要从文件执行恢复，请调用mysql可执行文件，并使用<符号指定要从中恢复的文件。因此，要恢复使用--all-databases选项转储的整个数据库，可以使用类似示例 9-10 中的命令。

示例 9-10. 恢复整个数据库集

mysql -u *user* -p*password* < all_databases.sql

要恢复单个数据库，请使用-D选项，后跟数据库名称，如示例 9-11，其中publications数据库正在从示例 9-8 生成的备份中恢复。

示例 9-11. 恢复 publications 数据库

mysql -u *user* -p*password* -D publications < publications.sql

要将单个表恢复到数据库，请使用类似示例 9-12 中的命令，这里只恢复publications数据库中的classics表。

示例 9-12. 将 classics 表恢复到 publications 数据库

mysql -u *user* -p*password* -D publications < classics.sql

以 CSV 格式转储数据

如前所述，mysqldump程序非常灵活，支持各种输出格式，例如 CSV 格式，您可以将其用于将数据导入电子表格等其他用途。示例 9-13 展示了如何将publications数据库中的classics和customers表数据导出到c:/temp文件夹下的classics.txt和customers.txt文件中。在 macOS 或 Linux 系统上，您应修改目标路径为一个已存在的文件夹。

示例 9-13. 将数据转储到 CSV 格式文件

mysqldump -u *user* -p*password* --no-create-info --tab=c:/temp
  --fields-terminated-by=',' publications

此命令非常长，在这里显示为两行，但您必须将其作为单行输入。结果如下：

Mark Twain (Samuel Langhorne Clemens)','The Adventures of Tom Sawyer',
 'Classic Fiction','1876','9781598184891
Jane Austen','Pride and Prejudice','Classic Fiction','1811','9780582506206
Charles Darwin','The Origin of Species','Nonfiction','1856','9780517123201
Charles Dickens','The Old Curiosity Shop','Classic Fiction','1841','9780099533474
William Shakespeare','Romeo and Juliet','Play','1594','9780192814968

Mary Smith','9780582506206
Jack Wilson','9780517123201

计划您的备份

备份的黄金法则是在您认为合适的频率进行备份。数据越有价值，您就越应该频繁备份，并制作更多的副本。如果您的数据库每天至少更新一次，您应该真的每天备份一次。另一方面，如果更新频率不高，您可能可以更少地备份。

注意

您应考虑制作多份备份并将它们存储在不同的位置。如果您有多台服务器，将备份在它们之间复制将变得非常简单。您还应该考虑在可移动硬盘、闪存驱动器、CD 或 DVD 等物理介质上进行备份，并将其保管在不同的地方——最好是像防火保险柜之类的地方。

定期测试数据库的还原也很重要，以确保您的备份操作正确无误。您还需要熟悉数据库的还原操作，因为在压力和匆忙情况下，例如网站遭遇停电后，可能需要进行数据库的还原操作。您可以将数据库还原到私有服务器并运行一些 SQL 命令，以确保数据符合您的预期。

一旦您消化了本章的内容，您将能够熟练使用 PHP 和 MySQL；下一章概述了最新 PHP/MySQL 版本的变化。

问题

1. 在关系数据库中，“关系”一词的含义是什么？

2. 删除重复数据和优化表的过程术语是什么？

3. 第一范式有哪三条规则？

4. 如何使表满足第二范式？

5. 在一个包含一对多关系项的两个表中，您会在列中放置什么以将它们联系在一起？

6. 如何创建具有多对多关系的数据库？

7. 什么命令启动和结束 MySQL 事务？

8. MySQL 提供了哪个功能，使您能够详细查看查询的工作方式？

9. 您会使用什么命令将publications数据库备份到名为publications.sql的文件中？

参见“第九章答案”，在附录 A 中查看这些问题的答案。