【MySQL5.7指南】第三章——教程

最新推荐文章于 2024-10-15 11:52:10 发布

西伯利亚疯狂的蚯蚓

最新推荐文章于 2024-10-15 11:52:10 发布

阅读量923

点赞数

分类专栏：文档数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/muluo7fen/article/details/122741909

版权

文档同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

数据库

8 篇文章 0 订阅

订阅专栏

Chapter 3 Tutorial

本章通过展示如何使用 mysql 客户端程序创建和使用简单的数据库来提供 MySQL 的教程介绍。 mysql（有时称为“终端监视器”或简称为“监视器”）是一个交互式程序，使您能够连接到 MySQL 服务器、运行查询并查看结果。 mysql 也可以在批处理模式下使用：您事先将查询放在一个文件中，然后告诉 mysql 执行该文件的内容。这里介绍了两种使用 mysql 的方法。

要查看 mysql 提供的选项列表，请使用 --help 选项调用它：

$> mysql --help

本章假设您的机器上安装了 mysql，并且您可以连接到 MySQL 服务器。如果不是这样，请联系您的 MySQL 管理员。（如果您是管理员，您需要查阅本手册的相关部分，例如第 5 章，MySQL 服务器管理。）

本章描述了设置和使用数据库的整个过程。如果您只对访问现有数据库感兴趣，您可能希望跳过描述如何创建数据库及其包含的表的部分。

由于本章是教程性质的，许多细节必然省略。有关此处涵盖的主题的更多信息，请参阅手册的相关部分。

3.1 连接和断开服务器

要连接到服务器，您通常需要在调用 mysql 时提供 MySQL 用户名，并且很可能需要提供密码。如果服务器在您登录的机器以外的机器上运行，您还需要指定主机名。请与您的管理员联系以了解您应该使用哪些连接参数进行连接（即要使用的主机、用户名和密码）。一旦你知道了正确的参数，你应该能够像这样连接：

$> mysql -h host -u user -p
Enter password: ********

host 和 user 代表你的 MySQL 服务器运行的主机名和你的 MySQL 帐户的用户名。为您的设置替换适当的值。 * * * * * * * * 代表您的密码；当 mysql 显示 Enter password: 提示时输入它。

如果可行，您应该会看到一些介绍性信息，然后是 mysql> 提示符：

$> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.7.36-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

mysql> 提示符告诉您 mysql 已准备好让您输入 SQL 语句。

如果您在运行 MySQL 的同一台机器上登录，则可以省略主机，只需使用以下命令：

$> mysql -u user -p

如果，当您尝试登录时，收到错误消息，例如 ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)，则表示 MySQL 服务器守护程序 (Unix) 或服务 (Windows) 未运行。请咨询管理员或参阅第 2 章安装和升级 MySQL 中适用于您的操作系统的部分。

有关尝试登录时经常遇到的其他问题的帮助，请参阅第 B.3.2 节，“使用 MySQL 程序时的常见错误”。

一些 MySQL 安装允许用户以匿名（未命名）用户的身份连接到在本地主机上运行的服务器。如果您的机器上出现这种情况，您应该能够通过调用 mysql 来连接到该服务器，而无需任何选项：

$> mysql

成功连接后，您可以随时在 mysql> 提示符下键入 QUIT（或 \q）断开连接：

mysql> QUIT
Bye

在 Unix 上，您也可以通过按 Control+D 断开连接。

以下部分中的大多数示例都假定您已连接到服务器。他们通过 mysql> 提示符指出这一点。

3.2 输入查询

确保您已连接到服务器，如上一节所述。这样做本身并不会选择要使用的任何数据库，但这没关系。此时，了解如何发出查询比直接创建表、将数据加载到表中以及从表中检索数据更重要。本节介绍输入查询的基本原则，您可以尝试使用几个查询来熟悉 mysql 的工作原理。

这是一个简单的查询，要求服务器告诉您它的版本号和当前日期。在 mysql> 提示符后按此处所示键入它，然后按 Enter：

mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| VERSION()    | CURRENT_DATE |
+--------------+--------------+
| 5.7.1-m4-log | 2012-12-25   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

此查询说明了有关 mysql 的几件事：

查询通常由一个 SQL 语句和一个分号组成。（有一些例外情况可能会省略分号。前面提到的 QUIT 就是其中之一。我们稍后会介绍其他情况。）
当您发出查询时，mysql 将其发送到服务器执行并显示结果，然后打印另一个 mysql> 提示以指示它已准备好进行另一个查询。
mysql 以表格形式（行和列）显示查询输出。第一行包含列的标签。以下行是查询结果。通常，列标签是您从数据库表中获取的列的名称。如果您正在检索表达式而不是表列的值（如刚刚显示的示例中所示），mysql 使用表达式本身标记列。
mysql 显示返回了多少行以及查询执行了多长时间，这让您大致了解服务器性能。这些值是不精确的，因为它们代表挂钟时间（不是 CPU 或机器时间），而且它们受服务器负载和网络延迟等因素的影响。（为简洁起见，本章其余示例中有时未显示“集合中的行”行。）

关键字可以以任何字母大小写输入。以下查询是等效的：

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

这是另一个查询。它演示了您可以将 mysql 用作简单的计算器：

mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------+---------+
| SIN(PI()/4)      | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 |      25 |
+------------------+---------+
1 row in set (0.02 sec)

到目前为止显示的查询都是相对较短的单行语句。您甚至可以在一行中输入多个语句。只需用分号结束每个：

mysql> SELECT VERSION(); SELECT NOW();
+------------------+
| VERSION()        |
+------------------+
| 5.7.10-ndb-7.5.1 |
+------------------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2016-01-29 18:02:55 |
+---------------------+
1 row in set (0.00 sec)

一个查询不需要在一行中全部给出，因此需要几行的冗长查询不是问题。 mysql 通过查找终止分号而不是查找输入行的结尾来确定语句的结束位置。（换句话说，mysql 接受自由格式的输入：它收集输入行但在看到分号之前不会执行它们。）

这是一个简单的多行语句：

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+---------------+--------------+
| USER()        | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2010-08-06   |
+---------------+--------------+

在此示例中，请注意在您输入多行查询的第一行后，提示如何从 mysql> 变为 ->。这就是 mysql 如何指示它尚未看到完整的语句并正在等待其余的语句。提示是您的朋友，因为它提供了有价值的反馈。如果您使用该反馈，您可以随时了解 mysql 正在等待什么。

如果您决定不想执行正在输入的查询，请键入 \c 取消它：

mysql> SELECT
    -> USER()
    -> \c
mysql>

在这里，也请注意提示。在您键入 \c 后，它会切换回 mysql>，提供反馈以指示 mysql 已准备好进行新查询。

下表显示了您可能会看到的每个提示，并总结了它们对 mysql 所处状态的含义。

Prompt	Meaning
`mysql>`	准备好新的查询
`->`	等待多行查询的下一行
`'>`	等待下一行，等待以单引号 (`'`) 开头的字符串的完成
`">`	等待下一行，等待以双引号 (`"`) 开头的字符串的完成
``>` \| 等待下一行，等待以反引号 (```) 开头的标识符的完成
`/*>`	等待下一行，等待以 `/*` 开头的注释完成

当您打算在单行上发出查询时，通常会意外出现多行语句，但忘记了终止分号。在这种情况下，mysql 等待更多输入：

mysql> SELECT USER()
    ->

如果这发生在你身上（你认为你已经输入了一个语句，但唯一的响应是 -> 提示符），很可能 mysql 正在等待分号。如果您没有注意到提示告诉您什么，您可能会坐在那里一段时间，然后才意识到您需要做什么。输入分号完成语句，mysql执行：

mysql> SELECT USER()
    -> ;
+---------------+
| USER()        |
+---------------+
| jon@localhost |
+---------------+

'> 和 “> 提示出现在字符串收集期间（另一种说法是 MySQL 正在等待字符串的完成）。在 MySQL 中，您可以编写由 ’ 或 " 字符包围的字符串（例如，‘hello’ 或 " goodbye”)，而 mysql 允许您输入跨越多行的字符串。当您看到 '> 或 "> 提示时，这意味着您已输入包含以 ’ 或 " 引号字符开头的字符串的行，但尚未输入终止字符串的匹配引号。这通常表明您无意中遗漏了引号字符。例如：

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '>

如果您输入此 SELECT 语句，然后按 Enter 并等待结果，则没有任何反应。与其想知道为什么这个查询需要这么长时间，不如注意 '> 提示符提供的线索。它告诉您 mysql 期望看到未终止的字符串的其余部分。（您看到语句中的错误了吗？字符串 'Smith 缺少第二个单引号。）

这个时候，你怎么办？最简单的事情是取消查询。但是，在这种情况下，您不能只键入 \c，因为 mysql 将其解释为它正在收集的字符串的一部分。相反，输入右引号字符（这样 mysql 就知道你已经完成了字符串），然后输入 \c：

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '> '\c
mysql>

提示符变回 mysql>，表示 mysql 已准备好进行新的查询。

`> 提示符类似于 '> 和 "> 提示符，但表示您已开始但未完成反引号引号标识符。

了解 '>、"> 和 `> 提示符的含义很重要，因为如果您错误地输入了一个未终止的字符串，您键入的任何其他行似乎都会被 mysql 忽略——包括包含 QUIT 的行。这可能是令人困惑，尤其是如果您不知道在取消当前查询之前需要提供终止引号时。

注意

从这一点开始，多行语句的编写没有辅助（-> 或其他）提示，以便更容易复制和粘贴语句以自己尝试。

3.3. 创建和使用一个数据库

3.3.1 创建和选择数据库

3.3.2 创建表

3.3.3 将数据加载到表中

3.3.4 从表中检索信息

一旦您知道如何输入 SQL 语句，您就可以访问数据库了。

假设您家中有几只宠物（您的动物园），并且您想跟踪有关它们的各种类型的信息。您可以通过创建表格来保存您的数据并使用所需信息加载它们来做到这一点。然后，您可以通过从表格中检索数据来回答有关您的动物的不同类型的问题。本节介绍如何执行以下操作：

创建数据库
创建表
将数据加载到表中
以各种方式从表中检索数据
使用多个表

动物园数据库很简单（故意地），但不难想到可能使用类似类型数据库的现实情况。例如，农民可以使用这样的数据库来跟踪牲畜，或者兽医可以使用这样的数据库来跟踪患者记录。可以从 MySQL 网站获得包含以下部分中使用的一些查询和示例数据的动物园分布。它在 https://dev.mysql.com/doc/ 上以压缩 tar 文件和 Zip 格式提供。

使用 SHOW 语句找出服务器上当前存在哪些数据库：

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

mysql 数据库描述了用户访问权限。测试数据库通常作为工作空间供用户试用。

该语句显示的数据库列表在您的机器上可能会有所不同；如果您没有 SHOW DATABASES 权限，则 SHOW DATABASES 不会显示您没有权限的数据库。请参见第 13.7.5.14 节，“SHOW DATABASES 语句”。

如果测试数据库存在，请尝试访问它：

mysql> USE test
Database changed

USE 和 QUIT 一样，不需要分号。（如果你愿意，你可以用分号结束这些语句；它没有坏处。） USE 语句在另一个方面也很特殊：它必须在一行中给出。

您可以在下面的示例中使用测试数据库（如果您可以访问它），但是您在该数据库中创建的任何内容都可以被其他有权访问它的人删除。出于这个原因，您可能应该向您的 MySQL 管理员请求使用您自己的数据库的权限。假设你想访问你的动物园。管理员需要执行如下语句：

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

其中 your_mysql_name 是分配给您的 MySQL 用户名，your_client_host 是您连接到服务器的主机。

3.3.1 创建和检索一个数据库

如果管理员在设置您的权限时为您创建了数据库，您就可以开始使用它了。否则，您需要自己创建它：

mysql> CREATE DATABASE menagerie;

在 Unix 下，数据库名称区分大小写（与 SQL 关键字不同），因此您必须始终将数据库称为 menagerie，而不是 Menagerie、MENAGERIE 或其他一些变体。这也适用于表名。（在 Windows 下，此限制不适用，尽管您必须在整个给定查询中使用相同的字母大小写来引用数据库和表。但是，由于各种原因，建议的最佳实践始终是使用相同的字母大小写数据库已创建。）

Note

如果您在尝试创建数据库时收到诸如 ERROR 1044 (42000): Access denied for user ‘micah’@‘localhost’ to database ‘menagerie’ 之类的错误，这意味着您的用户帐户没有必要的权限来执行此操作所以。与管理员讨论或参见第 6.2 节“访问控制和帐户管理”。

创建数据库不会选择使用它；你必须明确地这样做。要使 menagerie 成为当前数据库，请使用以下语句：

mysql> USE menagerie
Database changed

您的数据库只需要创建一次，但您必须在每次开始 mysql 会话时选择它以供使用。您可以通过发出 USE 语句来执行此操作，如示例中所示。或者，您可以在调用 mysql 时在命令行上选择数据库。只需在您可能需要提供的任何连接参数之后指定其名称即可。例如：

$> mysql -h host -u user -p menagerie
Enter password: ********

Important

刚才显示的命令中的 menagerie 不是您的密码。如果要在 -p 选项后的命令行上提供密码，则必须在没有中间空格的情况下这样做（例如，作为 -ppassword，而不是作为 -p 密码）。但是，不建议将您的密码放在命令行上，因为这样做会使您的计算机上登录的其他用户窥探它。

Note

您可以随时使用 SELECT DATABASE() 查看当前选择的数据库。

3.3.2 创建一个表

创建数据库是很容易的部分，但此时它是空的，正如 SHOW TABLES 告诉你的那样：

mysql> SHOW TABLES;
Empty set (0.00 sec)

更难的部分是决定数据库的结构应该是什么：您需要哪些表以及每个表中应该包含哪些列。

您需要一个包含每只宠物的记录的表。这可以称为宠物表，它应该至少包含每只动物的名字。因为名称本身不是很有趣，所以该表应该包含其他信息。例如，如果您的家庭中不止一个人养宠物，您可能需要列出每只动物的主人。您可能还想记录一些基本的描述性信息，例如物种和性别。

年龄呢？这可能很有趣，但存储在数据库中并不是一件好事。随着时间的推移，年龄会发生变化，这意味着您必须经常更新您的记录。相反，最好存储一个固定值，例如出生日期。然后，每当您需要年龄时，您都可以将其计算为当前日期和出生日期之间的差异。 MySQL 提供了做日期运算的函数，所以这并不难。存储出生日期而不是年龄还有其他优点：

您可以将数据库用于诸如为即将到来的宠物生日生成提醒等任务。（如果您认为这种类型的查询有些愚蠢，请注意，您可能会在业务数据库的上下文中提出相同的问题，以识别您需要在当前一周或本月向其发送生日祝福的客户，为此计算机辅助个人触发。）
您可以计算与当前日期以外的日期相关的年龄。例如，如果您将死亡日期存储在数据库中，您可以轻松计算出宠物死亡时的年龄。

您可能会想到在 pet 表中有用的其他类型的信息，但到目前为止确定的信息就足够了：姓名、所有者、物种、性别、出生和死亡。

使用 CREATE TABLE 语句指定表的布局：

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
       species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR 是名称、所有者和物种列的不错选择，因为列值的长度不同。这些列定义中的长度不必全部相同，也不必是 20。您通常可以选择 1 到 65535 之间的任何长度，只要您认为最合理。如果你做了一个糟糕的选择，后来发现你需要一个更长的字段，MySQL 提供了一个 ALTER TABLE 语句。

可以选择几种类型的值来表示动物记录中的性别，例如“m”和“f”，或者可能是“男性”和“女性”。使用单个字符“m”和“f”是最简单的。

对出生和死亡列使用 DATE 数据类型是一个相当明显的选择。

创建表后，SHOW TABLES 应该会产生一些输出：

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

要验证您的表是否按预期方式创建，请使用 DESCRIBE 语句：

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

您可以随时使用 DESCRIBE，例如，如果您忘记了表中列的名称或它们的类型。

有关 MySQL 数据类型的更多信息，请参阅第 11 章，数据类型。

3.3.3 将数据加载到表中

创建表后，您需要填充它。 LOAD DATA 和 INSERT 语句对此很有用。

假设您的宠物记录可以如此处所示。（注意 MySQL 需要 ‘YYYY-MM-DD’ 格式的日期；这可能与您习惯的不同。）

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

因为您从一个空表开始，所以填充它的一种简单方法是创建一个包含每个动物的行的文本文件，然后使用单个语句将文件的内容加载到表中。

您可以创建一个文本文件 pet.txt，其中每行包含一条记录，其值由制表符分隔，并按照 CREATE TABLE 语句中列出的列的顺序给出。对于缺失值（例如未知性别或仍然活着的动物的死亡日期），您可以使用 NULL 值。要在文本文件中表示这些，请使用 \N（反斜杠，大写 -N）。例如，Whistler the bird 的记录如下所示（值之间的空格是单个制表符）：

Whistler        Gwen    bird    \N      1997-12-09      \N

要将文本文件 pet.txt 加载到 pet 表中，请使用以下语句：

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

如果您在 Windows 上使用使用 \r\n 作为行终止符的编辑器创建文件，则应改用以下语句：

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
       LINES TERMINATED BY '\r\n';

（在运行 macOS 的 Apple 机器上，您可能希望使用 LINES TERMINATED BY ‘\r’。）

如果您愿意，可以在 LOAD DATA 语句中显式指定列值分隔符和行尾标记，但默认值是制表符和换行符。这些足以让语句正确读取文件 pet.txt。

如果语句失败，很可能您的 MySQL 安装没有默认启用本地文件功能。有关如何更改此设置的信息，请参见第 6.1.6 节，“LOAD DATA LOCAL 的安全注意事项”。

当您想一次添加一条新记录时，INSERT 语句很有用。在其最简单的形式中，您按照列在 CREATE TABLE 语句中列出的顺序为每列提供值。假设黛安得到了一只名为“Puffball”的新仓鼠。您可以使用这样的 INSERT 语句添加新记录：

mysql> INSERT INTO pet
       VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

字符串和日期值在此处指定为带引号的字符串。此外，使用 INSERT，您可以直接插入 NULL 来表示缺失值。不要像使用 LOAD DATA 那样使用 \N。

从这个示例中，您应该能够看到，最初使用多个 INSERT 语句而不是单个 LOAD DATA 语句来加载记录会涉及更多的输入。

3.3.4 从表中检索信息

[3.3.4.1 检索所有数据](#3.3.4.1 检索所有数据)

[3.3.4.2 检索特定的行](#3.3.4.2 检索特定的行)

[3.3.4.3 检索特定列](#3.3.4.3 检索特定列)

[3.3.4.4 排序行](#3.3.4.4 排序行)

[3.3.4.5 日期计算](#3.3.4.5 日期计算)

[3.3.4.6 使用NULL值](#3.3.4.6 使用NULL值)

[3.3.4.7 模式匹配](#3.3.4.7 模式匹配)

[3.3.4.8 计数行](#3.3.4.8 计数行)

[3.3.4.9 使用多个表](#3.3.4.9 使用多个表)

SELECT 语句用于从表中提取信息。语句的一般形式是：

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select 表示你想看到什么。这可以是列列表，或 * 表示“所有列”。 which_table 表示要从中检索数据的表。 WHERE 子句是可选的。如果存在，conditions_to_satisfy 指定行必须满足才能获得检索资格的一个或多个条件。

3.3.4.1 检索所有数据

最简单的 SELECT 形式从表中检索所有内容：

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

这种形式的 SELECT 使用 *，它是“选择所有列”的简写。如果您想查看整个表格，例如，在您刚刚将初始数据集加载到表格之后，这将非常有用。例如，您可能碰巧认为 Bowser 的出生日期似乎不太正确。查阅您的原始谱系文件，您会发现正确的出生年份应该是 1989 年，而不是 1979 年。

至少有两种方法可以解决这个问题：

编辑文件 pet.txt 以更正错误，然后清空表并使用 DELETE 和 LOAD DATA 重新加载它：
```
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
```
但是，如果您这样做，您还必须重新输入 Puffball 的记录。
仅使用 UPDATE 语句修复错误记录：
```
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
```
UPDATE 仅更改有问题的记录，不需要您重新加载表。

3.3.4.2 检索特定的行

如上一节所示，检索整个表很容易。只需从 SELECT 语句中省略 WHERE 子句。但通常你不想看到整个表，尤其是当它变大时。相反，您通常对回答特定问题更感兴趣，在这种情况下，您可以对所需信息指定一些限制条件。让我们根据他们回答的有关您的宠物的问题来看看一些选择查询。

您只能从表中选择特定行。例如，如果您想验证您对 Bowser 的出生日期所做的更改，请选择 Bowser 的记录，如下所示：

mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

输出确认年份被正确记录为 1989 年，而不是 1979 年。

字符串比较通常不区分大小写，因此您可以将名称指定为“bowser”、“BOWSER”等。查询结果是一样的。

您可以在任何列上指定条件，而不仅仅是名称。例如，如果您想知道哪些动物是在 1998 年期间或之后出生的，请测试出生列：

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

例如，您可以结合条件来定位母狗：

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

前面的查询使用 AND 逻辑运算符。还有一个 OR 运算符：

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

AND 和 OR 可以混合使用，尽管 AND 的优先级高于 OR。如果您同时使用这两个运算符，最好使用括号来明确指示应如何对条件进行分组：

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

3.3.4.3 检索特定列

如果您不想查看表中的整行，只需命名您感兴趣的列，用逗号分隔。例如，如果您想知道动物的出生时间，请选择名称和出生列：

mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

要找出谁拥有宠物，请使用以下查询：

mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

请注意，查询只是从每条记录中检索所有者列，其中一些记录不止一次出现。要最小化输出，只需通过添加关键字 DISTINCT 检索每个唯一的输出记录一次：

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

您可以使用 WHERE 子句将行选择与列选择结合起来。例如，要仅获取狗和猫的出生日期，请使用以下查询：

mysql> SELECT name, species, birth FROM pet
       WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+

3.3.4.4 排序行

您可能已经注意到，在前面的示例中，结果行没有按特定顺序显示。当行以某种有意义的方式排序时，通常更容易检查查询输出。要对结果进行排序，请使用 ORDER BY 子句。

以下是动物的生日，按日期排序：

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

在字符类型列上，排序（与所有其他比较操作一样）通常以不区分大小写的方式执行。这意味着对于除大小写外相同的列，顺序是未定义的。您可以使用 BINARY 强制对列进行区分大小写的排序，如下所示：ORDER BY BINARY col_name。

默认排序顺序是升序，最小值在前。要以反向（降序）顺序排序，请将 DESC 关键字添加到您要排序的列的名称：

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

您可以对多列进行排序，并且可以对不同的列进行不同方向的排序。例如，要按动物类型升序排序，然后按动物类型中的出生日期降序排序（最小的动物优先），请使用以下查询：

mysql> SELECT name, species, birth FROM pet
       ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

DESC 关键字仅适用于紧接在其前面的列名（出生）；它不影响物种列的排序顺序。

3.3.4.5 日期计算

MySQL 提供了几个函数，您可以使用它们来执行日期计算，例如，计算年龄或提取部分日期。

要确定每只宠物的年龄，请使用 TIMESTAMPDIFF() 函数。它的参数是你想要表达结果的单位，以及取差的两个日期。以下查询显示了每只宠物的出生日期、当前日期和年龄（以年为单位）。别名（年龄）用于使最终输出的列标签更有意义。

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

查询有效，但如果以某种顺序显示行，则可以更轻松地扫描结果。这可以通过添加 ORDER BY name 子句来按名称对输出进行排序来完成：

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

要按年龄而不是名称对输出进行排序，只需使用不同的 ORDER BY 子句：

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

类似的查询可用于确定已死亡动物的死亡年龄。您可以通过检查死亡值是否为 NULL 来确定这些是哪些动物。然后，对于具有非 NULL 值的那些，计算死亡值和出生值之间的差异：

mysql> SELECT name, birth, death,
       TIMESTAMPDIFF(YEAR,birth,death) AS age
       FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

该查询使用 death IS NOT NULL 而不是 death <> NULL 因为 NULL 是一个特殊值，无法使用通常的比较运算符进行比较。这将在后面讨论。请参阅第 3.3.4.6 节，“使用 NULL 值”。

如果你想知道哪些动物下个月过生日怎么办？对于这种类型的计算，年份和日期是无关紧要的；您只是想提取出生列的月份部分。 MySQL 提供了几个函数来提取部分日期，例如 YEAR()、MONTH() 和 DAYOFMONTH()。 MONTH() 是这里的适当函数。要查看它是如何工作的，请运行一个显示出生和 MONTH(birth) 值的简单查询：

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

寻找下个月过生日的动物也很简单。假设当前月份是四月。然后月份值为 4，您可以像这样查找 5 月（第 5 个月）出生的动物：

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

如果当前月份是 12 月，则会出现一个小问题。您不能只在月份编号 (12) 上加一，然后寻找在第 13 个月出生的动物，因为没有这样的月份。相反，您会寻找 1 月（第 1 个月）出生的动物。

您可以编写查询，以便无论当前月份是什么月份它都能正常工作，这样您就不必使用特定月份的数字。 DATE_ADD() 使您能够将时间间隔添加到给定日期。如果将月份添加到 CURDATE() 的值，然后使用 MONTH() 提取月份部分，则结果会生成查找生日的月份：

mysql> SELECT name, birth FROM pet
       WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

MONTH()返回1到12之间的数字。MOD(something,12) 返回一个介于 0 和 11 之间的数字。所以加法必须在 MOD() 之后，否则我们将从 11 月 (11) 到 1 月 (1)。

如果计算使用无效日期，则计算失败并产生警告：

mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+
mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+

3.3.4.6 使用NULL值

在您习惯之前，NULL 值可能会令人惊讶。从概念上讲，NULL 表示“缺失的未知值”，它的处理方式与其他值有些不同。

要测试 NULL，请使用 IS NULL 和 IS NOT NULL 运算符，如下所示：

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

您不能使用算术比较运算符（例如 =、< 或 <>）来测试 NULL。为了自己演示这一点，请尝试以下查询：

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

因为任何与 NULL 的算术比较的结果也是 NULL，所以您无法从此类比较中获得任何有意义的结果。

在 MySQL 中，0 或 NULL 表示 false，其他任何值都表示 true。布尔运算的默认真值是 1。

这种对 NULL 的特殊处理就是为什么在上一节中，有必要使用 death IS NOT NULL 而不是 death <> NULL 来确定哪些动物不再活着。

在 GROUP BY 中，两个 NULL 值被视为相等。

执行 ORDER BY 时，如果执行 ORDER BY … ASC，则首先显示 NULL 值，如果执行 ORDER BY … DESC，则最后显示 NULL 值。

使用 NULL 时的一个常见错误是假设不可能将零或空字符串插入到定义为 NOT NULL 的列中，但事实并非如此。这些实际上是值，而 NULL 表示“没有值”。您可以通过使用 IS [NOT] NULL 轻松地对此进行测试，如下所示：

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+

因此，完全可以将零或空字符串插入 NOT NULL 列，因为它们实际上是 NOT NULL。请参见第 B.3.4.3 节，“NULL 值的问题”。

3.3.4.7 模式匹配

MySQL 提供标准的 SQL 模式匹配以及一种基于扩展正则表达式的模式匹配，类似于 Unix 实用程序（如 vi、grep 和 sed）使用的那些。

SQL 模式匹配使您可以使用 _ 匹配任何单个字符，使用 % 匹配任意数量的字符（包括零个字符）。在 MySQL 中，SQL 模式默认不区分大小写。此处显示了一些示例。使用 SQL 模式时不要使用 = 或 <>。请改用 LIKE 或 NOT LIKE 比较运算符。

要查找以 b 开头的名称：

mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

要查找以 fy 结尾的名称：

mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

要查找包含 w 的名称：

mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

要查找恰好包含五个字符的名称，请使用 _ 模式字符的五个实例：

mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

MySQL 提供的另一种模式匹配使用扩展的正则表达式。当您测试此类模式的匹配时，请使用 REGEXP 和 NOT REGEXP 运算符（或 RLIKE 和 NOT RLIKE，它们是同义词）。

以下列表描述了扩展正则表达式的一些特征：

. 匹配任何单个字符。
字符类 […] 匹配括号内的任何字符。例如，[abc] 匹配 a、b 或 c。要命名一系列字符，请使用破折号。 [a-z] 匹配任何字母，而 [0-9] 匹配任何数字。
匹配它前面的事物的零个或多个实例。例如，x* 匹配任意数量的 x 字符，[0-9]* 匹配任意数量的数字，而 .* 匹配任意数量的任何内容。
如果模式匹配正在测试的值中的任何位置，则正则表达式模式匹配成功。（这与 LIKE 模式匹配不同，后者仅在模式匹配整个值时才成功。）
要锚定一个模式，使其必须匹配被测试值的开头或结尾，请在模式的开头使用 ^ 或在模式的结尾使用 $。

为了演示扩展正则表达式的工作原理，前面显示的 LIKE 查询在此处被重写为使用 REGEXP。

要查找以 b 开头的名称，请使用 ^ 匹配名称的开头：

mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

要强制 REGEXP 比较区分大小写，请使用 BINARY 关键字使其中一个字符串成为二进制字符串。此查询仅匹配名称开头的小写 b：

SELECT * FROM pet WHERE name REGEXP BINARY '^b';

要查找以 fy 结尾的名称，请使用 $ 匹配名称的结尾：

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

要查找包含 w 的名称，请使用以下查询：

mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

因为如果正则表达式模式出现在值中的任何位置，它就会匹配，因此在前面的查询中没有必要在模式的任一侧放置通配符以使其匹配整个值，就像 SQL 模式那样。

要查找恰好包含五个字符的名称，请使用 ^ 和 $ 来匹配名称的开头和结尾，以及 . 介于两者之间：

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

您还可以使用 {n}（“repeat-n-times”）运算符编写前面的查询：

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

有关正则表达式语法的更多信息，请参阅第 12.8.2 节，“正则表达式”。

3.3.4.8 计数行

数据库通常用于回答以下问题：“某种类型的数据在表中出现的频率如何？” 例如，您可能想知道您有多少只宠物，或者每个主人有多少只宠物，或者您可能想对您的动物执行各种类型的普查操作。

计算您拥有的动物总数与“宠物表中有多少行？”是同一个问题。因为每只宠物有一个记录。 COUNT(*) 计算行数，因此计算动物数量的查询如下所示：

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

之前，您检索了拥有宠物的人的姓名。如果你想知道每个主人有多少只宠物，你可以使用 COUNT()：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

前面的查询使用 GROUP BY 对每个所有者的所有记录进行分组。将 COUNT() 与 GROUP BY 结合使用对于在各种分组下表征您的数据很有用。以下示例显示了执行动物普查操作的不同方法。

每个物种的动物数量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

每性别的动物数量：

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

（在此输出中，NULL 表示性别未知。）

每个物种和性别组合的动物数量：

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

使用 COUNT() 时不需要检索整个表。例如，上一个查询，当只对狗和猫执行时，看起来像这样：

mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE species = 'dog' OR species = 'cat'
       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

或者，如果您只想要已知性别的动物的每种性别的动物数量：

mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE sex IS NOT NULL
       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

如果除了 COUNT() 值之外还命名要选择的列，则应该存在一个 GROUP BY 子句来命名这些相同的列。否则，会出现以下情况：

如果启用了 ONLY_FULL_GROUP_BY SQL 模式，则会出现错误：

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by

如果未启用 ONLY_FULL_GROUP_BY，则通过将所有行视为一个组来处理查询，但为每个命名列选择的值是不确定的。服务器可以自由地从任何行中选择值：

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        8 |
+--------+----------+
1 row in set (0.00 sec)

另见第 12.20.3 节，“MySQL 对 GROUP BY 的处理”。有关 COUNT(expr) 行为和相关优化的信息，请参阅第 12.20.1 节，“聚合函数描述”。

3.3.4.9 使用多个表

宠物桌会记录您拥有的宠物。如果您想记录有关它们的其他信息，例如它们生活中的事件，例如去看兽医或产仔时，您需要另一张表。这张桌子应该是什么样子的？它需要包含以下信息：

宠物名称，以便您知道每个事件与哪种动物有关。
一个日期，以便您知道事件发生的时间。
描述事件的字段。
一个事件类型字段，如果您希望能够对事件进行分类。

鉴于这些考虑，事件表的 CREATE TABLE 语句可能如下所示：

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
       type VARCHAR(15), remark VARCHAR(255));

与 pet 表一样，通过创建包含以下信息的制表符分隔的文本文件来加载初始记录是最简单的。

name	date	type	remark
Fluffy	1995-05-15	litter	4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Gave him a new chew toy
Claws	1998-03-17	birthday	Gave him a new flea collar
Whistler	1998-12-09	birthday	First birthday

像这样加载记录：

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

根据您从对宠物表运行的查询中了解到的信息，您应该能够对事件表中的记录执行检索；原理是一样的。但是，事件表本身何时不足以回答您可能提出的问题？

假设您想了解每只宠物的产仔年龄。我们之前看到了如何从两个日期计算年龄。母亲的产仔日期在事件表中，但要计算她在该日期的年龄，您需要她的出生日期，该日期存储在宠物表中。这意味着查询需要两个表：

mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

关于这个查询有几点需要注意：

FROM 子句连接两个表，因为查询需要从这两个表中提取信息。
当组合（连接）来自多个表的信息时，您需要指定一个表中的记录如何与另一个表中的记录匹配。这很容易，因为它们都有一个名称列。该查询使用 ON 子句根据名称值匹配两个表中的记录。

该查询使用 INNER JOIN 来组合表。当且仅当两个表都满足 ON 子句中指定的条件时，INNER JOIN 才允许任一表中的行出现在结果中。在此示例中，ON 子句指定宠物表中的名称列必须与事件表中的名称列匹配。如果名称出现在一个表中，而另一个表中没有，则该行不会出现在结果中，因为 ON 子句中的条件失败。
因为 name 列出现在两个表中，所以在引用该列时必须具体说明您指的是哪个表。这是通过将表名添加到列名来完成的。

您不需要有两个不同的表来执行连接。如果要将表中的记录与同一表中的其他记录进行比较，有时将表连接到自身很有用。例如，要在您的宠物中找到繁殖对，您可以将宠物表与自身连接起来以生成相似物种的活雄性和雌性候选对：

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM pet AS p1 INNER JOIN pet AS p2
         ON p1.species = p2.species
         AND p1.sex = 'f' AND p1.death IS NULL
         AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

在此查询中，我们为表名指定别名以引用列，并保持每个列引用与表的哪个实例相关联。

3.4 获取有关数据库和表的信息

如果您忘记了数据库或表的名称，或者给定表的结构是什么（例如，其列的名称）怎么办？ MySQL 通过几个语句解决了这个问题，这些语句提供了有关它支持的数据库和表的信息。

您之前已经看过 SHOW DATABASES，它列出了服务器管理的数据库。要找出当前选择了哪个数据库，请使用 DATABASE() 函数：

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

如果您尚未选择任何数据库，则结果为 NULL。

要找出默认数据库包含哪些表（例如，当您不确定表的名称时），请使用以下语句：

mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

此语句生成的输出中的列名称始终为 Tables_in_db_name，其中 db_name 是数据库的名称。有关详细信息，请参阅第 13.7.5.37 节，“SHOW TABLES 语句”。

如果您想了解表的结构，DESCRIBE 语句很有用；它显示有关每个表列的信息：

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Field 表示列名，Type 是列的数据类型，NULL 表示列是否可以包含 NULL 值，Key 表示列是否被索引，Default 指定列的默认值。 Extra 显示有关列的特殊信息：如果使用 AUTO_INCREMENT 选项创建列，则该值为 auto_increment 而不是空的。

DESC 是 DESCRIBE 的缩写形式。有关详细信息，请参阅第 13.8.1 节，“DESCRIBE 语句”。

您可以使用 SHOW CREATE TABLE 语句获取创建现有表所需的 CREATE TABLE 语句。请参阅第 13.7.5.10 节，“SHOW CREATE TABLE 语句”。

如果表上有索引，SHOW INDEX FROM tbl_name 会生成有关它们的信息。有关此语句的更多信息，请参阅第 13.7.5.22 节，“SHOW INDEX 语句”。

3.5 在批处理模式下使用 mysql

在前面的部分中，您以交互方式使用 mysql 输入语句并查看结果。您还可以在批处理模式下运行 mysql。为此，将要运行的语句放入文件中，然后告诉 mysql 从文件中读取其输入：

$> mysql < batch-file

如果您在 Windows 下运行 mysql 并且文件中有一些导致问题的特殊字符，您可以这样做：

C:\> mysql -e "source batch-file"

如果您需要在命令行中指定连接参数，该命令可能如下所示：

$> mysql -h host -u user -p < batch-file
Enter password: ********

当您以这种方式使用 mysql 时，您正在创建一个脚本文件，然后执行该脚本。

如果您希望脚本继续运行，即使其中的某些语句产生错误，您应该使用 --force 命令行选项。

为什么要使用脚本？这里有几个原因：

如果您重复运行查询（例如，每天或每周），将其设为脚本可以避免每次执行时都重新键入它。
您可以通过复制和编辑脚本文件从现有的类似查询中生成新查询。
在开发查询时，批处理模式也很有用，特别是对于多行语句或多语句序列。如果您犯了错误，您不必重新输入所有内容。只需编辑您的脚本以更正错误，然后告诉 mysql 再次执行它。
如果您有一个产生大量输出的查询，您可以通过寻呼机运行输出，而不是看着它从屏幕顶部滚动：
```
$> mysql < batch-file | more
```
您可以在文件中捕获输出以进行进一步处理：
```
$> mysql < batch-file > mysql.out
```
您可以将您的脚本分发给其他人，以便他们也可以运行这些语句。

某些情况不允许交互式使用，例如，当您从 cron 作业运行查询时。在这种情况下，您必须使用批处理模式。

以批处理模式运行 mysql 时，默认输出格式与交互使用时不同（更简洁）。例如，当 mysql 交互运行时，SELECT DISTINCT species FROM pet 的输出如下所示：

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

在批处理模式下，输出看起来像这样：

species
bird
cat
dog
hamster
snake

如果要在批处理模式下获取交互式输出格式，请使用 mysql -t。要将执行的语句回显到输出，请使用 mysql -v。

您还可以通过使用 source 命令或 \ 来使用 mysql 提示符下的脚本。命令：

mysql> source filename;
mysql> \. filename

有关详细信息，请参阅第 4.5.1.5 节，“从文本文件执行 SQL 语句”。

3.6 常见查询示例

[3.6.1 列的最大值](#3.6.1 列的最大值)

[3.6.2 某列的最大值所在的行](#3.6.2 某列的最大值所在的行)

[3.6.3 每组最大列数](#3.6.3 每组最大列数)

[3.6.4 保持某一列的分组最大值的行](#3.6.4 保持某一列的分组最大值的行)

[3.6.5 使用用户定义的变量](#3.6.5 使用用户定义的变量)

[3.6.6 使用外键](#3.6.6 使用外键)

[3.6.7 搜索两个键](#3.6.7 搜索两个键)

[3.6.8 计算每天访问量](#3.6.8 计算每天访问量)

[3.6.9 使用 AUTO_INCREMENT](#3.6.9 使用 AUTO_INCREMENT)

以下是如何解决 MySQL 的一些常见问题的示例。

一些示例使用表店来保存某些交易者（经销商）的每件商品（商品编号）的价格。假设每个交易者每篇文章有一个固定的价格，那么（文章，经销商）是记录的主键。

启动命令行工具 mysql 并选择一个数据库：

$> mysql your-database-name

要创建和填充示例表，请使用以下语句：

CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

发出语句后，表格应具有以下内容：

SELECT * FROM shop ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+

3.6.1 列的最大值

“最高的货号是多少？”

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

3.6.2 某列的最大值所在的行

任务：找出最贵商品的编号、经销商和价格。

这可以通过子查询轻松完成：

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

另一种解决方案是使用 LEFT JOIN，如下所示：

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

您还可以通过按价格降序对所有行进行排序并使用 MySQL 特定的 LIMIT 子句仅获取第一行来做到这一点，如下所示：

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

Note

如果有几篇最昂贵的文章，每篇的价格为 19.95，则 LIMIT 解决方案将只显示其中一篇。

3.6.3 每组最大列数

任务：找到每篇文章的最高价格。

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article
ORDER BY article;

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

3.6.4 保持某一列的分组最大值的行

任务：对于每件商品，找到价格最贵的经销商或经销商。

这个问题可以用这样的子查询来解决：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)
ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

前面的示例使用了一个相关的子查询，这可能是低效的（请参阅第 13.2.10.7 节，“相关子查询”）。解决该问题的其他可能性是在 FROM 子句或 LEFT JOIN 中使用不相关的子查询。

不相关子查询：

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;

LEFT JOIN 的工作原理是，当 s1.price 处于最大值时，没有更大的 s2.price，因此对应的 s2.article 值为 NULL。请参阅第 13.2.9.2 节，“JOIN 子句”。

3.6.5 使用用户定义的变量

您可以使用 MySQL 用户变量来记住结果，而不必将它们存储在客户端的临时变量中。（请参阅第 9.4 节，“用户定义的变量”。）

例如，要查找价格最高和最低的文章，您可以这样做：

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

Note

也可以将数据库对象的名称（例如表或列）存储在用户变量中，然后在 SQL 语句中使用此变量；但是，这需要使用准备好的语句。有关更多信息，请参阅第 13.5 节，“准备好的语句”。

3.6.6 使用外键

MySQL 支持外键，允许跨表交叉引用相关数据，外键约束有助于保持相关数据的一致性。

外键关系涉及一个包含初始列值的父表，以及一个包含引用父列值的列值的子表。在子表上定义了外键约束。

以下示例通过单列外键关联父表和子表，并显示外键约束如何强制引用完整性。

创建父子表：

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;


CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
) ENGINE=INNODB;

在父表中插入一行：

mysql> INSERT INTO parent (id) VALUES (1);

验证数据是否已插入：

mysql> SELECT * FROM parent;
+----+
| id |
+----+
|  1 |
+----+

在子表中插入一行：

mysql> INSERT INTO child (id,parent_id) VALUES (1,1);

插入操作成功，因为 parent_id 1 存在于父表中。

使用父表中不存在的 parent_id 值向子表中插入一行：

mysql> INSERT INTO child (id,parent_id) VALUES(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) 
REFERENCES `parent` (`id`))

此操作失败，因为子表中的记录包含引用的 id (parent_id) 值。

当操作影响父表中具有匹配行的子表中的键值时，结果取决于 FOREIGN KEY 子句的 ON UPDATE 和 ON DELETE 子句指定的引用操作。省略 ON DELETE 和 ON UPDATE 子句（如在当前子表定义中）与指定 RESTRICT 选项相同，该选项拒绝影响父表中具有匹配行的父表中的键值的操作。

为了演示 ON DELETE 和 ON UPDATE 引用操作，删除子表并重新创建它以包含带有 CASCADE 选项的 ON UPDATE 和 ON DELETE 子句。 CASCADE 选项在删除或更新父表中的行时自动删除或更新子表中的匹配行。

DROP TABLE child;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;

将以下行插入到子表中：

mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);

验证数据是否已插入：

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+

更新父表中的 id，将其从 1 更改为 2。

mysql> UPDATE parent SET id = 2 WHERE id = 1;

验证更新是否成功：

mysql> SELECT * FROM parent;
+----+
| id |
+----+
|  2 |
+----+

验证 ON UPDATE CASCADE 引用操作是否更新了子表：

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+

为了演示 ON DELETE CASCADE 引用操作，从 parent_id = 2 的父表中删除记录，这将删除父表中的所有记录。

mysql> DELETE FROM parent WHERE id = 2;

因为子表中的所有记录都与 parent_id = 2 关联，所以 ON DELETE CASCADE 引用操作会从子表中删除所有记录：

mysql> SELECT * FROM child;
Empty set (0.00 sec)

有关外键约束的更多信息，请参阅第 13.1.18.5 节，“外键约束”。

3.6.7 搜索两个键

使用单个键的 OR 得到了很好的优化，AND 的处理也是如此。

一个棘手的情况是搜索结合 OR 的两个不同的键：

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

本案例进行了优化。请参阅第 8.2.1.3 节，“索引合并优化”。

您还可以通过使用结合了两个单独的 SELECT 语句的输出的 UNION 来有效地解决问题。请参见第 13.2.9.3 节，“UNION 条款”。

每个 SELECT 只搜索一个键并且可以优化：

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

3.6.8 计算每天访问量

以下示例显示了如何使用位组函数来计算用户每月访问网页的天数。

CREATE TABLE t1 (year YEAR, month INT UNSIGNED,
             day INT UNSIGNED);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

示例表包含表示用户访问页面的年-月-日值。要确定这些访问每个月有多少天不同，请使用以下查询：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

返回内容：

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
+------+-------+------+

该查询计算每个年/月组合在表中出现的不同天数，并自动删除重复条目。

3.6.9 使用 AUTO_INCREMENT

AUTO_INCREMENT 属性可用于为新行生成唯一标识：

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

返回内容：

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

没有为 AUTO_INCREMENT 列指定值，因此 MySQL 自动分配了序列号。除非启用 NO_AUTO_VALUE_ON_ZERO SQL 模式，否则您还可以将 0 显式分配给列以生成序列号。例如：

INSERT INTO animals (id,name) VALUES(0,'groundhog');

如果该列被声明为 NOT NULL，也可以将 NULL 分配给该列以生成序列号。例如：

INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

当您将任何其他值插入到 AUTO_INCREMENT 列中时，该列将设置为该值并重置序列，以便下一个自动生成的值从最大的列值开始按顺序排列。例如：

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

更新 InnoDB 表中现有的 AUTO_INCREMENT 列值不会像对 MyISAM 和 NDB 表那样重置 AUTO_INCREMENT 序列。

您可以使用 LAST_INSERT_ID() SQL 函数或 mysql_insert_id() C API 函数检索最近自动生成的 AUTO_INCREMENT 值。这些函数是特定于连接的，因此它们的返回值不受另一个也在执行插入的连接的影响。

对 AUTO_INCREMENT 列使用最小的整数数据类型，该数据类型大到足以容纳您需要的最大序列值。当列达到数据类型的上限时，下一次生成序列号的尝试失败。如果可能，请使用 UNSIGNED 属性以允许更大的范围。例如，如果您使用 TINYINT，则允许的最大序列号为 127。对于 TINYINT UNSIGNED，最大值为 255。请参阅第 11.1.2 节，“整数类型（精确值） - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT ” 表示所有整数类型的范围。

Note

对于多行插入，LAST_INSERT_ID() 和 mysql_insert_id() 实际上从插入的第一行返回 AUTO_INCREMENT 键。这使得多行插入能够在复制设置中的其他服务器上正确复制。

要从 1 以外的 AUTO_INCREMENT 值开始，请使用 CREATE TABLE 或 ALTER TABLE 设置该值，如下所示：

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB Notes

有关特定于 InnoDB 的 AUTO_INCREMENT 用法的信息，请参阅第 14.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。

MyISAM Notes

对于 MyISAM 表，您可以在多列索引中的辅助列上指定 AUTO_INCREMENT。在这种情况下，AUTO_INCREMENT 列的生成值计算为 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。当您要将数据放入有序组时，这很有用。

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

返回内容：

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

在这种情况下（当 AUTO_INCREMENT 列是多列索引的一部分时），如果您删除任何组中具有最大 AUTO_INCREMENT 值的行，则会重用 AUTO_INCREMENT 值。即使对于 MyISAM 表也会发生这种情况，因为 AUTO_INCREMENT 值通常不会被重用。

如果 AUTO_INCREMENT 列是多个索引的一部分，则 MySQL 使用以 AUTO_INCREMENT 列开头的索引生成序列值（如果有的话）。例如，如果动物表包含索引 PRIMARY KEY (grp, id) 和 INDEX (id)，MySQL 将忽略 PRIMARY KEY 以生成序列值。结果，该表将包含单个序列，而不是每个 grp 值的序列。

延伸阅读

有关 AUTO_INCREMENT 的更多信息，请参见此处：

如何将 AUTO_INCREMENT 属性分配给列：第 13.1.18 节，“CREATE TABLE 语句”和第 13.1.8 节，“ALTER TABLE 语句”。
AUTO_INCREMENT 的行为取决于 NO_AUTO_VALUE_ON_ZERO SQL 模式：第 5.1.10 节，“服务器 SQL 模式”。
何使用 LAST_INSERT_ID() 函数查找包含最新 AUTO_INCREMENT 值的行：第 12.16 节，“信息函数”。
设置要使用的 AUTO_INCREMENT 值：第 5.1.7 节，“服务器系统变量”。
第 14.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。
AUTO_INCREMENT 和复制：第 16.4.1.1 节，“复制和 AUTO_INCREMENT”。
与可用于复制的 AUTO_INCREMENT（auto_increment_increment 和 auto_increment_offset）相关的服务器系统变量：第 5.1.7 节，“服务器系统变量”。

3.7 将 MySQL 与 Apache 一起使用

有一些程序可以让您从 MySQL 数据库验证您的用户，还可以让您将日志文件写入 MySQL 表。

您可以通过将以下内容放入 Apache 配置文件中来更改 Apache 日志记录格式，以便 MySQL 轻松读取：

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

要将这种格式的日志文件加载到 MySQL 中，可以使用如下语句：

LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

应创建命名表以使其具有与 LogFormat 行写入日志文件的列相对应的列。

西伯利亚疯狂的蚯蚓

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

【MySQL5.7指南】第三章——教程

Chapter 3 Tutorial

文章目录

3.1 连接和断开服务器

3.2 输入查询

3.3. 创建和使用一个数据库

3.3.1 创建和检索一个数据库

3.3.2 创建一个表

3.3.3 将数据加载到表中

3.3.4 从表中检索信息

3.3.4.1 检索所有数据

3.3.4.2 检索特定的行

3.3.4.3 检索特定列

3.3.4.4 排序行

3.3.4.5 日期计算

3.3.4.6 使用NULL值

3.3.4.7 模式匹配

3.3.4.8 计数行

3.3.4.9 使用多个表

3.4 获取有关数据库和表的信息

3.5 在批处理模式下使用 mysql

3.6 常见查询示例

3.6.1 列的最大值

3.6.2 某列的最大值所在的行

3.6.3 每组最大列数

3.6.4 保持某一列的分组最大值的行

3.6.5 使用用户定义的变量

3.6.6 使用外键

3.6.7 搜索两个键

3.6.8 计算每天访问量

3.6.9 使用 AUTO_INCREMENT

3.7 将 MySQL 与 Apache 一起使用