附录B MySQL对SQL语言的扩展
# 附录B MySQL对SQL语言的扩展
由于本书所有示例均使用MySQL服务器，我认为对于打算继续使用MySQL的读者而言，添加一个关于MySQL对SQL语言扩展的附录会很有用。本附录将探讨MySQL对SELECT、INSERT、UPDATE和DELETE语句的一些扩展，这些扩展在特定情况下非常实用。
# SELECT语句的扩展
MySQL对SELECT语句的实现包含两个额外的子句，以下小节将对其进行讨论。
# LIMIT子句
在某些情况下，你可能并不关心查询返回的所有行。例如，你可能构建一个查询，返回所有银行柜员以及每个柜员新开账户的数量。如果你执行该查询的目的是确定业绩排名前三的柜员，以便银行给他们颁发奖励，那么你不一定需要知道排名第四、第五等的柜员是谁。为了应对这类情况，MySQL的SELECT语句包含LIMIT子句，它允许你限制查询返回的行数。
为了展示LIMIT子句的效用，我先构建一个查询，以显示每个银行柜员新开账户的数量：
mysql> SELECT open_emp_id,
-> COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id;
+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
| 1 | 8 |
| 10 | 7 |
| 13 | 3 |
| 16 | 6 |
+-------------+----------+
4 rows in set (0.31 sec)
2
3
4
5
6
7
8
9
10
11
12
13
结果显示有四位不同的柜员开了账户；如果你想将结果集限制为仅三条记录，可以添加LIMIT子句，指定只返回三条记录：
mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id
-> LIMIT 3;
+-------------+-----------+
| open_emp_id | how_many |
+-------------+-----------+
| 1 | 8 |
| 10 | 7 |
| 13 | 3 |
+-------------+-----------+
3 rows in set (0.06 sec)
2
3
4
5
6
7
8
9
10
11
12
由于LIMIT子句（查询的第四行），结果集现在恰好包含三条记录，排名第四的柜员（员工ID为16）已从结果集中剔除。
# 结合LIMIT子句和ORDER BY子句
虽然前面的查询返回了三条记录，但有个小问题：你并未说明在这四条记录中你感兴趣的是哪三条。如果你在寻找特定的三条记录，比如新开账户数量最多的三位柜员，就需要将LIMIT子句与ORDER BY子句配合使用，如下所示：
mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id
-> ORDER BY how_many DESC
-> LIMIT 3;
+-------------+-----------+
| open_emp_id | how_many |
+-------------+-----------+
| 1 | 8 |
| 10 | 7 |
| 16 | 6 |
+-------------+-----------+
3 rows in set (0.03 sec)
2
3
4
5
6
7
8
9
10
11
12
13
这个查询与前面查询的区别在于，LIMIT子句现在应用于一个已排序的集合，使得新开账户数量最多的三位柜员被包含在最终结果集中。除非你只想查看任意的记录样本，否则通常都需要将ORDER BY子句与LIMIT子句一起使用。
LIMIT子句是在所有筛选、分组和排序操作完成后应用的，所以它除了限制语句返回的记录数量外，永远不会改变SELECT语句的结果。
# LIMIT子句的可选第二个参数
假设你的目标不是找出排名前三的柜员，而是找出除了排名前二的所有柜员（银行不是奖励表现最佳的员工，而是要送一些业绩较差的柜员去参加自信训练）。对于这类情况，LIMIT子句允许使用一个可选的第二个参数；使用两个参数时，第一个参数指定从哪条记录开始将记录添加到最终结果集，第二个参数指定要包含多少条记录。在按编号指定记录时，请记住MySQL将第一条记录指定为记录0。因此，如果你的目标是找出业绩排名第三的员工，可以这样做：
mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id
-> ORDER BY how_many DESC
-> LIMIT 2, 1;
+-------------+-----------+
| open_emp_id | how_many |
+-------------+-----------+
| 16 | 6 |
+-------------+-----------+
1 row in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
在这个例子中，第0条和第1条记录被丢弃，从第2条记录开始包含记录。由于LIMIT子句中的第二个参数是1，所以只包含了一条记录。
如果你想从第二个位置开始并包含所有剩余记录，可以将LIMIT子句的第二个参数设置得足够大，以确保包含所有剩余记录。因此，如果你不知道有多少柜员新开了账户，想要找出除了排名前二的所有柜员，可以这样做：
mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id
-> ORDER BY how_many DESC
-> LIMIT 2, 999999999;
+-------------+-----------+
| open_emp_id | how_many |
+-------------+-----------+
| 16 | 6 |
| 13 | 3 |
+-------------+-----------+
2 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
在这个版本的查询中，第0条和第1条记录被丢弃，从第2条记录开始最多包含999,999,999条记录（在这种情况下，实际上只有两条记录，但宁可设置得大一些，也不要因为估计不足而导致最终结果集遗漏有效记录）。
# 排名查询
当与ORDER BY子句结合使用时，包含LIMIT子句的查询可称为排名查询，因为它们允许你对数据进行排名。虽然我已经演示了如何根据新开账户数量对银行柜员进行排名，但排名查询可用于回答许多不同类型的业务问题，例如：
- 2005年我们的前五名销售人员是谁？
- 棒球历史上本垒打数量第三多的是谁？
- 除了《圣经》和《毛主席语录》，有史以来接下来最畅销的98本书是什么？
- 我们销量最差的两种口味的冰淇淋是什么？
到目前为止，我已经展示了如何找出排名前三的柜员、排名第三的柜员以及除了排名前二的所有柜员。如果我想做类似于第四个例子的事情（即找出表现最差的员工），只需反转排序顺序，使结果按新开账户数量从少到多排列即可，如下所示：
mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id
-> ORDER BY how_many ASC
-> LIMIT 2;
+-------------+-----------+
| open_emp_id | how_many |
+-------------+-----------+
| 13 | 3 |
| 16 | 6 |
+-------------+-----------+
2 rows in set (0.24 sec)
2
3
4
5
6
7
8
9
10
11
12
只需简单地更改排序顺序（从ORDER BY how_many DESC改为ORDER BY how_many ASC），该查询现在就返回了表现最差的两位柜员。因此，通过使用带有升序或降序排序的LIMIT子句，你可以生成排名查询来回答大多数类型的业务问题。
# INTO OUTFILE子句
如果你希望将查询输出写入文件，可以选中查询结果，复制到剪贴板，然后粘贴到你喜欢的编辑器中。然而，如果查询的结果集非常大，或者查询是在脚本中执行的，你就需要一种无需手动干预即可将结果写入文件的方法。为了帮助解决这类情况，MySQL包含INTO OUTFILE子句，允许你指定一个文件名称，查询结果将被写入该文件。下面是一个将查询结果写入我电脑c:\temp目录下文件的示例：
mysql> SELECT emp_id, fname, lname, start_date
-> INTO OUTFILE 'C:\\TEMP\\emp_list.txt'
-> FROM employee;
Query OK, 18 rows affected (0.20 sec)
2
3
4
如果你还记得第7章的内容，在字符串中反斜杠用于转义其他字符。因此，如果你是Windows用户，在构建路径名时需要连续输入两个反斜杠。
此时，结果集没有显示在屏幕上，而是被写入了emp_list.txt文件，内容如下：
1 Michael Smith 2001-06-22
2 Susan Barker 2002-09-12
3 Robert Tyler 2000-02-09
4 Susan Hawthorne 2002-04-24
...
16 Theresa Markham 2001-03-15
17 Beth Fowler 2002-06-29
18 Rick Tulman 2002-12-12
2
3
4
5
6
7
8
默认格式是在列之间使用制表符（\t），在每条记录后使用换行符（\n）。如果你想对数据格式有更多的控制，INTO OUTFILE子句还有几个附加的子句可用。例如，如果你希望数据采用所谓的竖线分隔格式，可以使用FIELDS子句，要求在每列之间放置|字符，如下所示：
mysql> SELECT emp_id, fname, lname, start_date
-> INTO OUTFILE 'C:\\TEMP\\emp_list_delim.txt'
-> FIELDS TERMINATED BY ' | '
-> FROM employee;
Query OK, 18 rows affected (0.02 sec)
2
3
4
5
MySQL在使用INTO OUTFILE时不允许覆盖现有文件，所以如果你多次运行相同的查询，需要先删除现有文件。
emp_list_delim.txt文件的内容如下：
1|Michael|Smith| 2001-06-22
2|Susan|Barker| 2002-09-12
3|Robert|Tyler| 2000-02-09
4|Susan|Hawthorne| 2002-04-24
...
16|Theresa|Markham| 2001-03-15
17|Beth|Fowler|2002-06-29
18|Rick|Tulman| 2002-12-12
2
3
4
5
6
7
8
除了竖线分隔格式，你可能还需要数据采用逗号分隔格式，在这种情况下，你可以使用FIELDS TERMINATED BY ','。然而，如果写入文件的数据包含字符串，使用逗号作为字段分隔符可能会有问题，因为逗号比竖线更有可能出现在字符串中。考虑以下查询，它将一个数字和两个字符串以逗号分隔写入comma1.txt文件：
mysql> SELECT data.num, data.str1, data.str2
-> INTO OUTFILE 'C:\\TEMP\\comma1.txt'
-> FIELDS TERMINATED BY ','
-> FROM
-> (SELECT 1 num, 'This string has no commas' str1,
-> 'This string, however, has two commas' str2) data;
Query OK, 1 row affected (0.04 sec)
2
3
4
5
6
7
由于输出文件中的第三列（str2）是一个包含逗号的字符串，你可能会认为试图读取comma1.txt文件的应用程序在将每行解析为列时会遇到问题，但MySQL服务器已经为这类情况做了处理。comma1.txt的内容如下：
1,This string has no commas,This string\, however\, has two commas
如你所见，第三列中的逗号通过在嵌入str2列的两个逗号前加上反斜杠进行了转义。如果你运行相同的查询但使用竖线分隔格式，逗号将不会被转义，因为没有必要。如果你想使用不同的转义字符，比如另一个逗号，可以使用FIELDS ESCAPED BY子句来指定输出文件使用的转义字符。
除了指定列分隔符，你还可以指定用于分隔数据文件中不同记录的字符。如果你希望输出文件中的每条记录不是用换行符分隔，可以使用LINES子句，如下所示：
mysql> SELECT emp_id, fname, lname, start_date
-> INTO OUTFILE 'C:\\TEMP\\emp_list_atsign.txt'
-> FIELDS TERMINATED BY ' | '
-> LINES TERMINATED BY '@'
-> FROM employee;
Query OK, 18 rows affected (0.03 sec)
2
3
4
5
6
由于我在记录之间没有使用换行符，查看emp_list_atsign.txt文件时，它看起来像一长串文本，每条记录由@字符分隔：
1|Michael|Smith|2001-06-22@2|Susan|Barker|2002-09-12@3|Robert|Tyler| 2000-02-09@4|Susan|Hawthorne|2002-04-24@5|John|Gooding|2003-11-14@6|Helen|Fleming| 2004-03-17@7|Chris|Tucker|2004-09-15@8|Sarah|Parker|2002-12-02@9|Jane|Grossman| 2002-05-03@10|Paula|Roberts|2002-07-27@11|Thomas|Ziegler|2000-10-23@12|Samantha|Jameson| 2003-01-08@13|John|Blake|2000-05-11@14|Cindy|Mason|2002-08-09@15|Frank|Portman| 2003-04-01@16|Theresa|Markham|2001-03-15@17|Beth|Fowler|2002-06-29@18|Rick|Tulman|2002-12-12@
如果你需要生成一个数据文件，以便加载到电子表格应用程序中，或者在组织内部或外部发送，INTO OUTFILE子句应该能为你提供足够的灵活性，满足任何所需的文件格式。
# 插入/更新组合语句
假设你被要求创建一个表，用于记录银行的哪些客户去过哪些分支机构。该表需要包含客户ID、分支机构ID以及一个日期时间列，用于指示客户最后一次访问该分支机构的时间。每当客户访问某个分支机构时，就会向表中添加行，但如果客户已经访问过该分支机构，那么只需更新现有行的日期时间列即可 。以下是表定义：
CREATE TABLE branch_usage
(
branch_id SMALLINT UNSIGNED NOT NULL,
cust_id INTEGER UNSIGNED NOT NULL,
last_visited_on DATETIME,
CONSTRAINT pk_branch_usage PRIMARY KEY (branch_id, cust_id)
);
2
3
4
5
6
7
除了三个列定义外，branch_usage表还对branch_id和cust_id列定义了主键约束。因此，服务器将拒绝向表中添加任何分支机构/客户对已存在的行。
假设在表创建好之后，客户ID为5的客户在第一周内三次访问了主分支机构（分支机构ID为1）。在第一次访问后，可以向branch_usage表中插入一条记录，因为此时还没有客户ID为5且分支机构ID为1的记录：
mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)
-> VALUES (1, 5, CURRENT_TIMESTAMP());
Query OK, 1 row affected (0.02 sec)
2
3
然而，当客户再次访问同一分支机构时，就需要更新现有记录，而不是插入新记录；否则，将会收到以下错误：
ERROR 1062 (23000): Duplicate entry '1-5' for key 1
为避免此错误，可以查询branch_usage表，查看给定的客户/分支机构对是否存在，然后在未找到记录时插入一条记录，若记录已存在则更新现有行。不过，为了省去麻烦，MySQL的设计者扩展了insert语句，允许在insert语句因重复键而失败时指定修改一个或多个列。以下语句指示服务器，如果给定的客户和分支机构已存在于branch_usage表中，则修改last_visited_on列：
mysql> INSERT INTO branch_usage (branch_id, cust_id, last_visited_on)
-> VALUES (1, 5, CURRENT_TIMESTAMP())
-> ON DUPLICATE KEY UPDATE last_visited_on = CURRENT_TIMESTAMP();
Query OK, 2 rows affected (0.02 sec)
2
3
4
on duplicate key子句允许每次客户ID为5的客户在分支机构ID为1的地方办理业务时都执行相同的语句。如果运行100次，第一次执行会向表中添加一行，接下来的99次执行会将last_visited_on列更改为当前时间。这种操作通常称为“插入或更新（upsert）”，因为它是更新语句和插入语句的组合。
# 取代replace命令
在MySQL服务器4.1版本之前，“插入或更新”操作是使用replace命令执行的，这是一个特定的语句，在插入行之前，如果表中已存在主键值，它会先删除现有行。如果使用的是4.1或更高版本，在执行“插入或更新”操作时，可以在replace命令和insert...on duplicate key命令之间进行选择。
不过，replace命令在遇到重复键值时会执行删除操作，如果你使用的是InnoDB存储引擎并且启用了外键约束，这可能会产生连锁反应。如果约束是使用on delete cascade选项创建的，那么当replace命令删除目标表中的一行时，其他表中的行也可能会被自动删除 。因此，一般认为使用insert语句的on duplicate key子句比旧的replace命令更安全。
# 有序更新和删除
在本附录前面部分，我向你展示了如何结合使用limit子句和order by子句编写查询来生成排名，例如按开户数量排名前三的柜员。MySQL还允许在update和delete语句中使用limit和order by子句，从而可以根据排名修改或删除表中的特定行。例如，假设你被要求从一个用于跟踪客户登录银行网上银行系统的表中删除记录。该表跟踪客户ID和登录日期/时间，如下所示：
CREATE TABLE login_history
(
cust_id INTEGER UNSIGNED NOT NULL,
login_date DATETIME,
CONSTRAINT pk_login_history PRIMARY KEY (cust_id, login_date)
);
2
3
4
5
6
以下语句通过在account表和customer表之间生成交叉连接，并以account表的open_date列为基础生成登录日期，从而向login_history表中填充一些数据：
mysql> INSERT INTO login_history (cust_id, login_date)
-> SELECT c.cust_id,
-> ADDDATE(a.open_date, INTERVAL a.account_id * c.cust_id HOUR)
-> FROM customer c CROSS JOIN account a;
Query OK, 312 rows affected (0.03 sec)
Records: 312 Duplicates: 0 Warnings: 0
2
3
4
5
6
现在表中填充了312行相对随机的数据。你的任务是每月查看一次login_history表中的数据，为你的经理生成一份报告，展示哪些人在使用网上银行系统，然后从表中删除除最近50条记录之外的所有记录。一种方法是编写一个使用order by和limit的查询，找到第50条最近的登录记录，例如：
mysql> SELECT login_date
-> FROM login_history
-> ORDER BY login_date DESC
-> LIMIT 49,1;
+---------------------+
| login_date |
+---------------------+
| 2004-07-02 09:00:00 |
+---------------------+
1 row in set (0.00 sec)
2
3
4
5
6
7
8
9
10
有了这些信息后，就可以构造一个delete语句，删除所有login_date列小于查询返回日期的行：
mysql> DELETE FROM login_history
-> WHERE login_date < '2004-07-02 09:00:00';
Query OK, 262 rows affected (0.02 sec)
2
3
现在表中包含了最近的50条登录记录。不过，使用MySQL的扩展功能，可以使用一个带有limit和order by子句的delete语句达到相同的结果。在将最初的312行数据恢复到login_history表后，可以运行以下语句：
mysql> DELETE FROM login_history
-> ORDER BY login_date ASC
-> LIMIT 262;
Query OK, 262 rows affected (0.05 sec)
2
3
4
通过这条语句，行按login_date升序排序，然后删除前262行，留下最近的50行。
在这个例子中，我必须知道表中的行数才能构造limit子句（312条原始行 - 50条保留行 = 262条要删除的行）。如果能够按降序对行进行排序，并告诉服务器跳过前50行，然后删除剩余的行就更好了，如下所示：
DELETE FROM login_history
ORDER BY login_date DESC
LIMIT 49, 9999999;
2
3
然而，MySQL在delete或update语句中使用limit子句时，不允许使用可选的第二个参数。
除了删除数据，在修改数据时也可以使用limit和order by子句。例如，如果银行决定给10个最早开户的账户每个增加100美元，以帮助留住忠实客户，可以这样做：
mysql> UPDATE account
-> SET avail_balance = avail_balance + 100
-> WHERE product_cd IN ('CHK', 'SAV', 'MM')
-> ORDER BY open_date ASC
-> LIMIT 10;
Query OK, 10 rows affected (0.06 sec)
Rows matched: 10 Changed: 10 Warnings: 0
2
3
4
5
6
7
这条语句按开户日期升序对账户进行排序，然后修改前10条记录，在这种情况下，这些就是10个最早开户的账户。
# 多表更新和删除
在某些情况下，可能需要从几个不同的表中修改或删除数据来完成特定任务。例如，如果你发现银行数据库中包含一个系统测试留下的虚拟客户，可能需要从account、customer和individual表中删除数据。
在本节中，我将为account、customer和individual表创建一组克隆表，分别称为account2、customer2和individual2。这样做既是为了保护示例数据不被更改，也是为了避免表之间的外键约束出现任何问题（本节后面会详细介绍）。以下是用于生成这三个克隆表的create table语句：
CREATE TABLE individual2 AS SELECT * FROM individual;
CREATE TABLE customer2 AS SELECT * FROM customer;
CREATE TABLE account2 AS SELECT * FROM account;
2
3
如果虚拟客户的客户ID是1，可以针对这三个表分别生成三个单独的delete语句，如下所示：
DELETE FROM account2
WHERE cust_id = 1;
DELETE FROM customer2
WHERE cust_id = 1;
DELETE FROM individual2
WHERE cust_id = 1;
2
3
4
5
6
7
8
不过，MySQL允许编写一个多表删除语句，而不是编写单独的删除语句。在这种情况下，语句如下：
mysql> DELETE account2, customer2, individual2
-> FROM account2 INNER JOIN customer2
-> ON account2.cust_id = customer2.cust_id
-> INNER JOIN individual2
-> ON customer2.cust_id = individual2.cust_id
-> WHERE individual2.cust_id = 1;
Query OK, 5 rows affected (0.02 sec)
2
3
4
5
6
7
这条语句总共删除了五行数据，individual2表和customer2表各一行，account2表三行（客户ID为1的客户有三个账户）。该语句包含三个独立的子句：
delete：指定要删除数据的目标表。from：指定用于确定要删除的行的表。这个子句在形式和功能上与select语句中的from子句相同，这里命名的表不一定都要包含在delete子句中。where：包含用于确定要删除的行的过滤条件。
多表删除语句看起来很像select语句，只是使用delete子句代替了select子句。如果使用多表删除格式从单个表中删除行，这种差异就更不明显了。例如，以下是一个select语句，用于查找John Hayward拥有的所有账户的账户ID：
mysql> SELECT account2.account_id
-> FROM account2 INNER JOIN customer2
-> ON account2.cust_id = customer2.cust_id
-> INNER JOIN individual2
-> ON individual2.cust_id = customer2.cust_id
-> WHERE individual2.fname = 'John'
-> AND individual2.lname = 'Hayward';
+------------+
| account_id |
+------------+
| 8 |
| 9 |
| 10 |
+------------+
3 rows in set (0.01 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
查看结果后，如果决定从account2表中删除John的所有三个账户，只需将上一个查询中的select子句替换为命名account2表的delete子句即可，如下所示：
mysql> DELETE account2
-> FROM account2 INNER JOIN customer2
-> ON account2.cust_id = customer2.cust_id
-> INNER JOIN individual2
-> ON customer2.cust_id = individual2.cust_id
-> WHERE individual2.fname = 'John'
-> AND individual2.lname = 'Hayward';
Query OK, 3 rows affected (0.01 sec)
2
3
4
5
6
7
8
希望这能让你更好地理解多表删除语句中delete和from子句的用途。这条语句在功能上与以下单表删除语句相同，后者使用子查询来确定John Hayward的客户ID：
DELETE FROM account2
WHERE cust_id =
(SELECT cust_id
FROM individual2
WHERE fname = 'John' AND lname = 'Hayward');
2
3
4
5
当使用多表删除语句从单个表中删除行时，只是选择使用涉及表连接的类似查询的格式，而不是使用带有子查询的传统删除语句。多表删除语句的真正强大之处在于能够在一条语句中从多个表中删除数据，正如我在本节的第一个语句中展示的那样。
除了能够从多个表中删除行之外，MySQL还允许使用多表更新来修改多个表中的行。假设你的银行正在与另一家银行合并，并且两家银行的数据库存在重叠的客户ID。管理层决定通过将你数据库中的每个客户ID增加10000来解决这个问题，以便可以安全导入另一家银行的数据。以下语句展示了如何使用一条语句修改individual2、customer2和account2表中客户ID为3的客户的ID：
mysql> UPDATE individual2 INNER JOIN customer2
-> ON individual2.cust_id = customer2.cust_id
-> INNER JOIN account2
-> ON customer2.cust_id = account2.cust_id
-> SET individual2.cust_id = individual2.cust_id + 10000,
-> customer2.cust_id = customer2.cust_id + 10000,
-> account2.cust_id = account2.cust_id + 10000
-> WHERE individual2.cust_id = 3;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 5 Changed: 4 Warnings: 0
2
3
4
5
6
7
8
9
10
这条语句修改了四行数据：individual2表和customer2表各一行，account2表两行。多表更新的语法与单表更新非常相似，只是update子句包含多个表及其相应的连接条件，而不是只命名一个表。与单表更新一样，多表更新也包含一个set子句，不同之处在于update子句中引用的任何表都可以通过set子句进行修改。
如果你使用的是InnoDB存储引擎，并且涉及的表有外键约束，那么很可能无法使用多表删除和更新语句。这是因为该引擎不能保证更改的应用顺序不会违反约束。相反，你应该按正确的顺序使用多个单表语句，这样就不会违反外键约束。