简介
在使用数据库时，有时可能需要更改已经插入其中的数据。例如，您可能需要纠正拼写错误的条目，或者可能需要向不完整的记录添加新信息。结构化查询语言——通常被称为SQL——提供了
UPDATE
关键字，允许用户更改表中的现有数据。
本指南概述了如何使用SQL的
UPDATE
语法更改一个或多个表中的数据。它还解释了SQL如何处理与外键约束冲突的
UPDATE
操作。
前期准备
为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。
注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。
你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。
连接到MySQL并设置一个示例数据库
如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器:
ssh sammy@your_server_ip
然后打开MySQL服务器提示符，将
==sammy==
替换为你的MySQL用户账户的名称:
mysql -u sammy -p
创建一个名为
updateDB
的数据库:
CREATEDATABASE updateDB;
如果数据库成功创建,您将收到这样的输出:
OutputQuery OK, 1 row affected (0.01 sec)
要选择
updateDB
数据库，运行以下
USE
语句:
USE updateDB;
OutputDatabase changed
选择
updateDB
数据库后，在其中创建两个表。对于本指南中使用的示例，假设您经营一家人才代理公司，并决定开始在SQL数据库中跟踪您的客户及其表现。你计划从两个表开始，第一个表存储客户端的信息。你决定这个表需要4列：
clientID：每个客户端的标识码，用int数据类型表示。这一列也是表的主键，每个值都是对应一行的唯一标识符name：每个客户端的名称，使用varchar数据类型表示，不超过20个字符routine：每个客户端主要性能类型的简要描述，再次使用varchar数据类型表示，但不超过30个字符performanceFee：记录每个客户端的标准性能费用的一列，它使用decimal数据类型，该列中的任何值都被限制为长度不超过5位，其中有两位在小数点的右侧。因此，在本专栏中值允许的范围从-999.99到999.99
创建一个名为
clients
的表，包含以下四列:
CREATETABLE clients
(clientID intPRIMARYKEY,
name varchar(20),routinevarchar(30),
standardFee decimal(5,2));
第二个表将存储您的客户在当地场地的演出信息。这个表需要5列:
showID:与clientID列类似，这一列将保存每个show的唯一标识号，用int数据类型表示。同样，这一列将作为shows表的主键showDate:每次演出的日期。这一列的值使用date数据类型表示，该数据类型使用“YYYY-MM-DD”格式clientID:参加演出的客户端的ID号，用整数表示attendance:与会者的数量比率（出勤率），用整数表示ticketPrice:每场演出的门票价格。这一列使用decimal数据类型，该列中任何值的长度限制为最多四位，其中两位在小数点的右侧，因此这一列允许的值范围是-99.99到99.99
为了确保
clientID
列只保存代表有效客户端ID数字的值，你决定对引用
clients
表中的
clientID
列的
clientID
列应用一个外键约束。外键约束是一种表达两个表之间关系的方式，它要求应用的列中的值必须已经存在于它引用的列中。在下面的例子中，
FOREIGN KEY
约束要求任何添加到
shows
表中的
clientID
列的值必须已经存在于
client
表的
clientID
列中。
创建一个名为
clients
的表，包含以下5列:
CREATETABLE shows
(showID intPRIMARYKEY,
showDate date,
clientID int,
attendance int,
ticketPrice decimal(4,2),CONSTRAINT client_fk
FOREIGNKEY(clientID)REFERENCES clients(clientID));
注意，这个例子为外键约束提供了一个名称
client_fk
。MySQL会为你添加的任何约束自动生成一个名称，但在这里定义一个名称将在我们稍后需要引用该约束时非常有用。
接下来，运行以下
INSERT INTO
语句来加载包含5行示例数据的
clients
表:
INSERTINTO 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);
Then run another
INSERT INTO
statement to load the
shows
table with ten rows of sample data:
然后运行另一个
INSERT INTO
语句加载
shows
示例数据表10行:
INSERTINTO 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 inset(0.00 sec)
例如，假设你注意到Katherine的名字拼写错误——它应该以“K”开头，但在表中它以“C”开头——因此你决定通过运行以下
UPDATE
语句来更改该值。这个操作通过将任何名为
Catherine
的行的
name
值更改为
Katherine
来更新
name
列中的值:
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 inset(0.00 sec)
这个输出表明原来读为
Catherine
的值确实被更改为
Katherine
。
这个例子只更新了
name
列中的一个值。然而，你可以使用不那么排他性的
WHERE
子句更新多个值。
举例来说，想象一下，你为所有表演单口相声或歌舞节目的客户谈判标准表演费用。下面的语句将更新
standardFee
列中的值，将其设置为
140
。
请注意，这个示例的
WHERE
子句包含一个
LIKE
操作符，因此它只更新每个
routine
值匹配指定通配符模式
's%'
的客户端的
performanceFee
值。换句话说，它将更新任何例程以字母“s”开头的表演者的演出费用:
UPDATE clients
SET standardFee =140WHEREroutineLIKE's%';
OutputQuery OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
如果你再次查询
clients
表的内容，结果集将确认你的四个客户端现在具有相同的性能费用
standardFee
:
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 inset(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 forcolumn'standardFee' at row 5|
+-------+------+--------------------------------------------------+
1 row inset(0.00 sec)
这个输出告诉我们，数据库系统发出了警告，因为它必须截断其中一个新的
standardFee
值，以符合之前定义的
decimal
格式——5个数字，小数点右边2个。
再次查询
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 inset(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 inset(0.01 sec)
为了反映实际的人数和价格，您可以更新该表，将20名与会者添加到他们的每次演出中，并将他们的
ticketPrice
值增加50%。可以使用如下操作来实现:
UPDATE shows
SET attendance = attendance +20,
ticketPrice = ticketPrice *1.5WHERE 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
子句中的子查询，从
clients
表中返回Georgeanna和Wanda的
clientID
值。通常情况下，像标识号这样的抽象值很难记住，但是当你只知道记录的某些属性时，这种使用子查询来查找值的方法可能很有用。
更新
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 inset(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
语句。这将在各自的
clientID
列中连接
clients
和
shows
表，然后更新
clients
表中Gladys记录的
routine
和
ticketPrice
值，以及在
shows
表中列出的她的每一次表演:
UPDATE clients JOIN shows
USING(clientID)SET clients.routine='mime',
shows.ticketPrice =30WHERE name ='Gladys';
OutputQuery OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
注意，这个例子使用
USING
关键字来连接表，而不是前面示例语法中使用的
ON
关键字。这是可能的，因为两张表都有一个共享类似数据类型的
clientID
列。
修改外键
默认情况下，任何与
FOREIGN KEY
约束冲突的
UPDATE
语句都会失败。
回忆一下先决条件中连接到MySQL并设置一个示例数据库的部分，
shows
表的
clientID
列是一个外键，引用
clients
表的
clientID
列。这意味着输入到
shows
表的
clientID
列中的任何值都必须已经存在于
clients
表中。
如果你试图更新
clients
表中同时出现在
shows
表的
clientID
列中的记录的
clientID
值，它将导致错误:
UPDATE clients
SET clientID =9WHERE name ='Ann';
OutputERROR 1217(23000): Cannot delete or update a parent row: a foreign key constraint fails
您可以通过将现有的外键约束替换为以不同方式处理
UPDATE
操作的约束来避免此错误。
注意：并不是每个关系型数据库管理系统或引擎都允许你在现有表中添加或删除约束，如下所述。如果你使用的是MySQL以外的RDBMS，应该查阅它的官方文档，以了解它在管理约束方面有哪些限制。
要替换当前的约束，你必须首先用
ALTER TABLE
语句删除它。回想一下，在
shows
的
CREATE TABLE
语句中，我们定义了
client_fk
作为表的
FOREIGN KEY
约束的名称:
ALTERTABLE shows
DROPFOREIGNKEY 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
选项对我们的目的更有意义。
要在
ON UPDATE CASCADE
行为之后添加一个
FOREIGN KEY
约束，请运行以下
ALTER TABLE
语句。这创建了一个名为
new_client_fk
的新约束复制之前的约束定义，但包括
ON UPDATE CASCADE
选项:
ALTERTABLE shows
ADDCONSTRAINT new_client_fk
FOREIGNKEY(clientID)REFERENCES clients (clientID)ONUPDATECASCADE;
OutputQuery OK, 10 rows affected (0.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
这个输出告诉你操作影响了
shows
表中的所有10行。
注意：与其改变表的定义来改变外键如何处理
UPDATE
操作，你可以在
CREATE table
语句中从一开始就定义这种行为，如下所示:
CREATETABLE shows
(showID intPRIMARYKEY,
showDate date,
clientID int,
attendance int,
ticketPrice decimal(4,2),CONSTRAINT client_fk
FOREIGNKEY(clientID)REFERENCES clients(clientID)ONUPDATECASCADE);
接下来，你将能够更新
clients
表中任何记录的
clientID
值，这些更改将级联到
shows
表中引用它的任何行:
UPDATE clients
SET clientID =9WHERE name ='Ann';
OutputQuery OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
虽然这个输出说它只影响了一行，但它也更新了
shows
表中与Ann关联的所有性能记录的
clientID
值。为了确认这一点，运行以下查询来检索
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 inset(0.00 sec)
不出所料，对
clients
表中
clientID
列的更新会级联到
shows
表中的相关行。
总结
通过阅读本指南，您学习了如何使用SQL的
UPDATE
语句更改或更多表中的现有记录。你还学习了SQL如何处理与外键约束冲突的
UPDATE
操作，以及如何更改默认行为。
这里列出的命令应该适用于任何使用SQL的数据库管理系统。请记住，每个SQL数据库都使用自己独特的语言实现，因此你应该查阅相应DBMS的官方文档，以更完整地描述它如何处理
UPDATE
操作以及它们有哪些可用选项。
版权归原作者 CHQIUU 所有， 如有侵权，请联系我们删除。