介绍
使用数据库时，有时您可能需要更改已插入其中的数据。例如，您可能需要更正拼写错误的条目，或者您可能需要将新信息添加到不完整的记录中。 结构化查询语言（通常称为SQL）提供了UPDATE允许用户更改表中现有数据的关键字。
本指南概述了如何使用 SQL 的UPDATE语法来更改一个或多个表中的数据。它还解释了 SQL 如何处理UPDATE与外键约束冲突的操作。
先决条件
要遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已使用以下环境进行验证：
- 运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和使用 UFW 配置的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
- 在服务器上安装并保护 MySQL，如如何在 Ubuntu 20.04 上安装 MySQL 中所述。本指南已使用非 root MySQL 用户进行验证，该用户使用步骤 3中描述的过程创建。
注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，则确切的语法或输出可能会有所不同。
- 您还需要一个数据库，其中包含一些加载了示例数据的表，您可以使用这些数据来练习更新 SQL 数据。我们鼓励您阅读以下连接到 MySQL 和设置示例数据库部分，详细了解如何连接到 MySQL 服务器并创建本指南示例中使用的测试数据库。
连接到 MySQL 并设置示例数据库
如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：
- ssh sammy@your_server_ip
然后打开 MySQL 服务器提示，替换sammy为您的 MySQL 用户帐户的名称：
- mysql -u sammy -p
创建一个名为 的数据库updateDB：
- CREATE DATABASE updateDB;
如果数据库创建成功，您将收到如下输出：
OutputQuery OK, 1 row affected (0.01 sec)
要选择updateDB数据库，请运行以下USE语句：
- USE updateDB;
OutputDatabase changed
选择updateDB数据库后，在其中创建几个表。对于本指南中使用的示例，假设您经营一家人才中介机构并决定开始在 SQL 数据库中跟踪您的客户及其表现。您计划从两个表开始，其中第一个将存储有关您的客户的信息。您决定此表需要四列：
clientID：每个客户端的标识号，用int数据类型表示。此列也将作为表的主键，每个值作为其各自行的唯一标识符name: 每个客户端的名称，使用varchar最多 20 个字符的数据类型表示routine：每个客户的主要表演类型的简要描述，再次使用varchar数据类型表示，但最多 30 个字符performanceFee：一列记录每个客户的标准绩效费用，它使用decimal数据类型，该列中的任何值的长度限制为最多五位数字，其中两位数字在小数点右侧。因此，此列中允许的值范围从-999.99到999.99
创建一个名为的表clients，其中包含以下四列：
- CREATE TABLE clients
- (clientID int PRIMARY KEY,
- name varchar(20),
- routine varchar(30),
- standardFee decimal (5,2)
- );
第二个表将存储有关您的客户在当地场地的表演的信息。您决定此表需要五列：
showID：与clientID列类似，此列将保存每个节目的唯一标识号，用int数据类型表示。同样，此列将作为shows表的主键showDate: 每场演出的日期。此列的值使用date使用'YYYY-MM-DD'格式的数据类型表示clientID: 演出客户的ID号，以整数表示attendance: 每场演出的参加人数，以整数表示ticketPrice：每场演出的单张门票价格。此列使用decimal数据类型，此列中的任何值的长度限制为最多四位数字，其中两位在小数点右侧，因此此列中允许的值范围-99.99为99.99
为确保该clientID列仅包含表示有效客户端 ID 号的值，您决定将外键约束应用于clientID引用该clients表clientID列的列。外键约束是一种表达两个表之间关系的方法，它要求应用它的列中的值必须已经存在于它引用的列中。在以下示例中，FOREIGN KEY约束要求添加到表中clientID列的任何值shows必须已存在于client表的clientID列中。
创建一个名为的表clients，其中包含以下五列：
- CREATE TABLE shows
- (showID int PRIMARY KEY,
- showDate date,
- clientID int,
- attendance int,
- ticketPrice decimal (4,2),
- CONSTRAINT client_fk
- FOREIGN KEY (clientID)
- REFERENCES clients(clientID)
- );
请注意，此示例为外键约束提供了一个名称：client_fk. MySQL 会自动为您添加的任何约束生成一个名称，但是当我们稍后需要引用此约束时，在此处定义一个名称会很有用。
接下来，运行以下INSERT INTO语句以加载clients具有五行示例数据的表：
- INSERT INTO clients
- VALUES
- (1, 'Gladys', 'song and dance', 180),
- (2, 'Catherine', 'standup', 99.99),
- (3, 'Georgeanna', 'standup', 45),
- (4, 'Wanda', 'song and dance', 200),
- (5, 'Ann', 'trained squirrel', 79.99);
然后运行另一个INSERT INTO语句以加载shows具有十行示例数据的表：
- INSERT INTO shows
- VALUES
- (1, '2019-12-25', 4, 124, 15),
- (2, '2020-01-11', 5, 84, 29.50),
- (3, '2020-01-17', 3, 170, 12.99),
- (4, '2020-01-31', 5, 234, 14.99),
- (5, '2020-02-08', 1, 86, 25),
- (6, '2020-02-14', 3, 102, 39.5),
- (7, '2020-02-15', 2, 101, 26.50),
- (8, '2020-02-27', 2, 186, 19.99),
- (9, '2020-03-06', 4, 202, 30),
- (10, '2020-03-07', 5, 250, 8.99);
这样，您就可以按照指南的其余部分并开始学习如何使用 SQL 更新数据。
更新单个表中的数据
UPDATE语句的一般语法如下所示：
- UPDATE table_name
- SET column_name = value_expression
- WHERE conditions_apply;
继UPDATE关键字是表的存储要更新数据的名称。之后是一个SET子句，它指定应该更新哪个列的数据以及如何更新。将该SET子句视为将指定列中的值设置为等于您提供的任何值表达式。
在 SQL 中，值表达式（有时称为标量表达式）是任何表达式，它会为要更新的每一行返回单个值。这可以是字符串文字，也可以是对列中现有数值执行的数学运算。您必须在每条UPDATE语句中至少包含一个赋值，但您可以包含多个以更新多列中的数据。
后SET子句是一个WHERE子句。WHERE在UPDATE类似于本示例语法的语句中包含子句允许您过滤掉不想更新的任何行。一个WHERE条款是完全可选的UPDATE语句，但是如果你不包括一个操作将更新表中的每一行。
为了说明 SQL 如何处理UPDATE操作，首先查看clients表中的所有数据。以下查询包含一个星号 ( *)，它是代表表中每一列的 SQL 简写，因此此查询将返回clients表中每一列的所有数据：
- SELECT * FROM clients;
Output+----------+------------+------------------+-------------+
| clientID | name | routine | standardFee |
+----------+------------+------------------+-------------+
| 1 | Gladys | song and dance | 180.00 |
| 2 | Catherine | standup | 99.99 |
| 3 | Georgeanna | standup | 45.00 |
| 4 | Wanda | song and dance | 200.00 |
| 5 | Ann | trained squirrel | 79.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)
例如，假设您注意到 Katherine 的名字拼写错误——它应该以“K”开头，但在表中它以“C”开头——因此您决定通过运行以下UPDATE语句来更改该值。此操作将更新值name改变列name名为任何行的值Catherine到Katherine：
- UPDATE clients
- SET name = 'Katherine'
- WHERE name = 'Catherine';
OutputQuery OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
此输出表明仅更新了一行。您可以通过SELECT再次运行上一个查询来确认这一点：
- SELECT * FROM clients;
Output+----------+------------+------------------+-------------+
| clientID | name | routine | standardFee |
+----------+------------+------------------+-------------+
| 1 | Gladys | song and dance | 180.00 |
| 2 | Katherine | standup | 99.99 |
| 3 | Georgeanna | standup | 45.00 |
| 4 | Wanda | song and dance | 200.00 |
| 5 | Ann | trained squirrel | 79.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)
此输出显示以前读取的值Catherine确实已更改为Katherine。
此示例仅更新了name列中的一个值。但是，您可以使用不那么排他性的WHERE子句更新多个值。
举例来说，假设您为所有表演单口喜剧或歌舞表演的客户协商标准表演费用。以下语句将standardFee通过将列中的值设置为等于 来更新列中的值140。
请注意，此示例的WHERE子句包含一个LIKE运算符，因此它仅更新performanceFee其routine值与指定通配符模式,匹配的每个客户端的值's%'。换句话说，它将更新任何以字母“s”开头的表演者的表演费：
- UPDATE clients
- SET standardFee = 140
- WHERE routine LIKE 's%';
OutputQuery OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
如果clients再次查询表的内容，结果集将确认您的四个客户端现在具有相同的性能费用：
- SELECT * FROM clients;
Output+----------+------------+------------------+-------------+
| clientID | name | routine | standardFee |
+----------+------------+------------------+-------------+
| 1 | Gladys | song and dance | 140.00 |
| 2 | Katherine | standup | 140.00 |
| 3 | Georgeanna | standup | 140.00 |
| 4 | Wanda | song and dance | 140.00 |
| 5 | Ann | trained squirrel | 79.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)
如果表中的任何列包含数值，您可以使用SET子句中的算术运算更新它们。为了说明这一点，假设您还为每个客户的绩效费用协商增加了 40%。要在clients表中反映这一点，您可以运行如下UPDATE操作：
- UPDATE clients
- SET standardFee = standardFee * 1.4;
OutputQuery OK, 5 rows affected, 1 warning (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 1
注意：请注意，此输出表明更新导致警告。通常，当 MySQL 由于列或表的定义而被迫更改您的数据时，它会发出警告。
MySQL 提供了SHOW WARNINGS可以帮助解释您收到的任何警告的快捷方式：
- SHOW WARNINGS;
Output+-------+------+--------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------+
| Note | 1265 | Data truncated for column 'standardFee' at row 5 |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)
此输出通知我们，数据库系统发出警告，因为它必须截断一个新standardFee值，以便它符合decimal先前定义的格式——五位数字，小数点右侧有两位。
clients再次查询该表以确认每个客户的绩效费用都增加了 40%。
- SELECT * FROM clients;
Output+----------+------------+------------------+-------------+
| clientID | name | routine | standardFee |
+----------+------------+------------------+-------------+
| 1 | Gladys | song and dance | 196.00 |
| 2 | Katherine | standup | 196.00 |
| 3 | Georgeanna | standup | 196.00 |
| 4 | Wanda | song and dance | 196.00 |
| 5 | Ann | trained squirrel | 111.99 |
+----------+------------+------------------+-------------+
5 rows in set (0.00 sec)
如前所述，您还可以使用单个UPDATE语句更新多列中的数据。为此，您必须指定要更新的每一列，在每一列后面加上各自的值表达式，然后用逗号分隔每一列和值表达式对。
例如，假设您了解到您的客户表演的场地误报了 Georgeanna 和 Wanda 的所有演出的参加人数。巧合的是，您也碰巧为他们的每场表演输入了错误的票价。
在更新shows表中的数据之前，运行以下查询以检索当前保存在其中的所有数据：
- SELECT * FROM shows;
Output+--------+------------+----------+------------+-------------+
| showID | showDate | clientID | attendance | ticketPrice |
+--------+------------+----------+------------+-------------+
| 1 | 2019-12-25 | 4 | 124 | 15.00 |
| 2 | 2020-01-11 | 5 | 84 | 29.50 |
| 3 | 2020-01-17 | 3 | 170 | 12.99 |
| 4 | 2020-01-31 | 5 | 234 | 14.99 |
| 5 | 2020-02-08 | 1 | 86 | 25.00 |
| 6 | 2020-02-14 | 3 | 102 | 39.50 |
| 7 | 2020-02-15 | 2 | 101 | 26.50 |
| 8 | 2020-02-27 | 2 | 186 | 19.99 |
| 9 | 2020-03-06 | 4 | 202 | 30.00 |
| 10 | 2020-03-07 | 5 | 250 | 8.99 |
+--------+------------+----------+------------+-------------+
10 rows in set (0.01 sec)
为反映实际人数和价格，您更新表格，将 20 名参加者添加到他们的每场表演中，并将他们的每场演出的ticketPrice价值增加 50%。您可以通过如下操作来实现：
- UPDATE shows
- SET attendance = attendance + 20,
- ticketPrice = ticketPrice * 1.5
- WHERE clientID IN
- (SELECT clientID
- FROM clients
- WHERE name = 'Georgeanna' OR name = 'Wanda');
OutputQuery OK, 4 rows affected, 1 warning (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 1
请注意，此示例在WHERE子句中使用子查询clientID从clients表中返回 Georgeanna 和 Wanda 的值。通常，像标识号这样的抽象值可能很难记住，但是当您只知道有关记录的某些属性时，这种使用子查询查找值的方法会很有帮助。
更新shows表后，再次查询它以确认更改按预期进行：
- SELECT * FROM shows;
Output+--------+------------+----------+------------+-------------+
| showID | showDate | clientID | attendance | ticketPrice |
+--------+------------+----------+------------+-------------+
| 1 | 2019-12-25 | 4 | 144 | 22.50 |
| 2 | 2020-01-11 | 5 | 84 | 29.50 |
| 3 | 2020-01-17 | 3 | 190 | 19.49 |
| 4 | 2020-01-31 | 5 | 234 | 14.99 |
| 5 | 2020-02-08 | 1 | 86 | 25.00 |
| 6 | 2020-02-14 | 3 | 122 | 59.25 |
| 7 | 2020-02-15 | 2 | 101 | 26.50 |
| 8 | 2020-02-27 | 2 | 186 | 19.99 |
| 9 | 2020-03-06 | 4 | 222 | 45.00 |
| 10 | 2020-03-07 | 5 | 250 | 8.99 |
+--------+------------+----------+------------+-------------+
10 rows in set (0.00 sec)
再一次，此输出表明该UPDATE语句已成功完成。
使用JOIN子句更新多个表中的数据
到目前为止，本指南仅展示了如何一次更新一个表中的数据。但是，某些 SQL 实现允许您通过将表与JOIN子句临时组合来更新多个表中的多个列。
以下是可用于使用JOIN子句更新多个表的一般语法：
- UPDATE table_1 JOIN table_2
- ON table_1.related_column = table_2.related_column
- SET table_1.column_name = value_expression,
- table_2.column_name = value_expression
- WHERE conditions_apply;
此示例语法以UPDATE关键字开头，后跟两个表的名称，由JOIN子句分隔。接下来是ON子句，它描述了查询应该如何将两个表连接在一起。
在大多数实现中，您可以通过查找具有 SQL 标准称为“JOIN合格”数据类型的任何列集之间的匹配来连接表。这意味着，通常，您可以将任何包含数字数据的列与任何其他包含数字数据的列连接起来，而不管它们各自的数据类型如何。同样，您可以将任何包含字符值的列与任何其他包含字符数据的列连接起来。
请注意，由于JOIN子句比较多个表的内容，因此此示例语法通过在列名称前面加上表名称和句点来指定从哪个表中选择每一列。这称为完全限定的列引用。您可以像这样为任何操作指定应该从哪个表中选择列，尽管它通常仅用于在处理多个表时提高清晰度。
要使用之前创建的示例表进行说明，请运行以下UPDATE语句。这将在各自的列上连接clients和shows表clientID，然后更新表中格拉迪斯记录的routine和ticketPrice值以及clients表中列出的她的每个表演shows：
- UPDATE clients JOIN shows
- USING (clientID)
- SET clients.routine = 'mime',
- shows.ticketPrice = 30
- WHERE name = 'Gladys';
OutputQuery OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
请注意，此示例使用USING关键字而不是ON前一个示例语法中使用的关键字连接表。这是可能的，因为两个表都有一个clientID共享相似数据类型的列。
有关操作的更深入教程JOIN，请参阅我们关于如何在 SQL 中使用联接的指南。
改变外键UPDATE行为
默认情况下，任何UPDATE会导致与FOREIGN KEY约束冲突的语句都将失败。
回忆一下先决条件的连接到 MySQL 和设置示例数据库部分，表的clientID列shows是引用表clientID列的外键clients。这意味着输入到shows表clientID列中的任何值必须已经存在于表的列clients中。
如果尝试更新表中同样出现在表列中的clientID记录的值，则会导致错误：clientsclientIDshows
- UPDATE clients
- SET clientID = 9
- WHERE name = 'Ann';
OutputERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
您可以通过将现有外键约束替换为以UPDATE不同方式处理操作的外键约束来避免此错误。
注意：并非每个关系数据库管理系统或引擎都允许您在现有表中添加或删除约束，如以下段落所述。如果您使用的是 MySQL 以外的 RDBMS，您应该查阅其官方文档以了解它在管理约束方面有哪些限制。
要替换当前约束，您必须首先使用ALTER TABLE语句将其删除。回想一下，在CREATE TABLEfor 语句中shows，我们定义client_fk了表FOREIGN KEY约束的名称：
- ALTER TABLE shows
- DROP FOREIGN KEY client_fk;
OutputQuery OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
然后，创建一个新的外键约束，该约束被配置为UPDATE以对给定用例有意义的方式处理操作。除了禁止UPDATE违反外键的语句的默认设置外，大多数 RDBMS 上还有两个其他选项可用：
ON UPDATE SET NULL：此选项将允许您更新父表中的记录，并将子表中引用它们的所有值重置为NULL.ON UPDATE CASCADE：当您更新父表中的一行时，此选项将导致 SQL 自动更新子表中引用它的任何记录，以便它们与父表中的新值对齐。
就本示例而言，ON UPDATE SET NULL没有意义。毕竟，如果您更改了一位客户的识别号，但没有将其从clients牌桌上删除，那么他们仍应与牌shows桌上的表现相关联。他们的新识别号应该反映在他们的表演记录中，所以这个ON UPDATE CASCADE选项对我们的目的更有意义。
要添加FOREIGN KEY遵循ON UPDATE CASCADE行为的约束，请运行以下ALTER TABLE语句。这将创建一个名为的新约束new_client_fk，它复制了先前的约束定义，但包括以下ON UPDATE CASCADE选项：
- ALTER TABLE shows
- ADD CONSTRAINT new_client_fk
- FOREIGN KEY (clientID)
- REFERENCES clients (clientID)
- ON UPDATE CASCADE;
OutputQuery OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
此输出告诉您该操作影响了shows表中的所有十行。
注意：UPDATE您可以从一开始就在CREATE TABLE语句中定义此行为，而不是更改表的定义以更改外键处理操作的方式，如下所示：
- CREATE TABLE shows
- (showID int PRIMARY KEY,
- showDate date,
- clientID int,
- attendance int,
- ticketPrice decimal (4,2),
- CONSTRAINT client_fk
- FOREIGN KEY (clientID)
- REFERENCES clients(clientID)
- ON UPDATE CASCADE
- );
之后，您将能够更新表clientID中任何记录的值clients，这些更改将级联到shows表中引用它的任何行：
- UPDATE clients
- SET clientID = 9
- WHERE name = 'Ann';
OutputQuery OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
尽管此输出表明它仅影响一行，但它还会更新与 Ann 关联clientID的shows表中任何性能记录的值。要确认这一点，请运行以下查询以检索shows表中的所有数据：
- SELECT * FROM shows;
Output+--------+------------+----------+------------+-------------+
| showID | showDate | clientID | attendance | ticketPrice |
+--------+------------+----------+------------+-------------+
| 1 | 2019-12-25 | 4 | 144 | 22.50 |
| 2 | 2020-01-11 | 9 | 84 | 29.50 |
| 3 | 2020-01-17 | 3 | 190 | 19.49 |
| 4 | 2020-01-31 | 9 | 234 | 14.99 |
| 5 | 2020-02-08 | 1 | 86 | 30.00 |
| 6 | 2020-02-14 | 3 | 122 | 59.25 |
| 7 | 2020-02-15 | 2 | 101 | 26.50 |
| 8 | 2020-02-27 | 2 | 186 | 19.99 |
| 9 | 2020-03-06 | 4 | 222 | 45.00 |
| 10 | 2020-03-07 | 9 | 250 | 8.99 |
+--------+------------+----------+------------+-------------+
10 rows in set (0.00 sec)
正如预期的那样，对表中的clientID列所做的更新clients向下级联到表中的关联行shows。
结论
通过阅读本指南，您了解了如何使用 SQLUPDATE语句更改一个或多个表中的现有记录。您还学习了 SQL 如何处理UPDATE与外键约束冲突的操作，以及如何更改默认行为。
此处概述的命令应该适用于任何使用 SQL 的数据库管理系统。请记住，每个 SQL 数据库都使用自己独特的语言实现，因此您应该查阅 DBMS 的官方文档，以获取有关它如何处理UPDATE操作以及可用选项的更完整说明。
如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于如何使用 SQL的其他教程。
