如何使用SQL系列 之 如何在SQL中更新数据_sql两种更新数据的语句方法

简介

在使用数据库时，有时可能需要更改已经插入其中的数据。例如，您可能需要纠正拼写错误的条目，或者可能需要向不完整的记录添加新信息。结构化查询语言——通常被称为SQL——提供了UPDATE关键字，允许用户更改表中的现有数据。
本指南概述了如何使用SQL的UPDATE语法更改一个或多个表中的数据。它还解释了SQL如何处理与外键约束冲突的UPDATE操作。

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。

连接到MySQL并设置一个示例数据库

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器:

ssh sammy@your_server_ip
1

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称:

mysql -u sammy -p
1

创建一个名为updateDB的数据库:

CREATE DATABASE updateDB;
1

如果数据库成功创建,您将收到这样的输出:

OutputQuery OK, 1 row affected (0.01 sec)
1

要选择updateDB数据库，运行以下USE语句:

USE updateDB;
1

OutputDatabase changed
1

选择updateDB数据库后，在其中创建两个表。对于本指南中使用的示例，假设您经营一家人才代理公司，并决定开始在SQL数据库中跟踪您的客户及其表现。你计划从两个表开始，第一个表存储客户端的信息。你决定这个表需要4列：

• clientID：每个客户端的标识码，用int数据类型表示。这一列也是表的主键，每个值都是对应一行的唯一标识符
• name：每个客户端的名称，使用varchar数据类型表示，不超过20个字符
• routine：每个客户端主要性能类型的简要描述，再次使用varchar数据类型表示，但不超过30个字符
• performanceFee：记录每个客户端的标准性能费用的一列，它使用decimal数据类型，该列中的任何值都被限制为长度不超过5位，其中有两位在小数点的右侧。因此，在本专栏中值允许的范围从-999.99到999.99

创建一个名为clients的表，包含以下四列:

CREATE TABLE clients
(clientID int PRIMARY KEY,
name varchar(20),
routine varchar(30),
standardFee decimal (5,2)
);
1
2
3
4
5
6

第二个表将存储您的客户在当地场地的演出信息。这个表需要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列:

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)
);
1
2
3
4
5
6
7
8
9
10

注意，这个例子为外键约束提供了一个名称client_fk。MySQL会为你添加的任何约束自动生成一个名称，但在这里定义一个名称将在我们稍后需要引用该约束时非常有用。

接下来，运行以下INSERT INTO语句来加载包含5行示例数据的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);
1
2
3
4
5
6
7

Then run another INSERT INTO statement to load the shows table with ten rows of sample data:
然后运行另一个INSERT INTO语句加载shows示例数据表10行:

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);
1
2
3
4
5
6
7
8
9
10
11
12

有了这些，你就可以开始学习如何使用SQL更新数据了。

更新单个表中的数据

UPDATE语句的通用语法如下所示:

UPDATE table_name
SET column_name = value_expression
WHERE conditions_apply;
1
2
3

UPDATE关键字后面是存储要更新数据的表的名称。之后是一个SET子句，它指定了哪一列的数据应该被更新以及如何更新。可以将SET子句视为将指定列中的值设置为等于你提供的任何值表达式。

在SQL中，值表达式——有时被称为标量表达式——是为每一行返回一个要更新的值的任何表达式。这可以是一个字符串字面量，也可以是对列中已有的数值执行的数学操作。你必须在每个UPDATE语句中包含至少一个赋值语句，但是你可以包含多个赋值语句来更新多个列中的数据。

在SET子句之后是WHERE子句。包括一个WHERE条件的UPDATE语句像在这个例子中语法允许你过滤掉任何你不想更新的行。WHERE的条件是完全可选的UPDATE语句,但是如果你不包括一个操作将会更新表中的每一行。

为了说明SQL如何处理UPDATE操作，首先看一下clients表中的所有数据。以下查询包括星号(*)的SQL速记代表每一列在表中,这个查询将返回所有数据从clients表中的每一列:

SELECT * FROM clients;
1

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)
1
2
3
4
5
6
7
8
9
10

例如，假设你注意到Katherine的名字拼写错误——它应该以“K”开头，但在表中它以“C”开头——因此你决定通过运行以下UPDATE语句来更改该值。这个操作通过将任何名为Catherine的行的name值更改为Katherine来更新name列中的值:

UPDATE clients
SET name = 'Katherine'
WHERE name = 'Catherine';
1
2
3

OutputQuery OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2

这个输出表明只有一行被更新了。你可以再次运行之前的SELECT查询来确认这一点:

SELECT * FROM clients;
1

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)
1
2
3
4
5
6
7
8
9
10

这个输出表明原来读为Catherine的值确实被更改为Katherine。

这个例子只更新了name列中的一个值。然而，你可以使用不那么排他性的WHERE子句更新多个值。

举例来说，想象一下，你为所有表演单口相声或歌舞节目的客户谈判标准表演费用。下面的语句将更新standardFee列中的值，将其设置为140。

请注意，这个示例的WHERE子句包含一个LIKE操作符，因此它只更新每个routine值匹配指定通配符模式's%'的客户端的performanceFee值。换句话说，它将更新任何例程以字母“s”开头的表演者的演出费用:

UPDATE clients
SET standardFee = 140
WHERE routine LIKE 's%';
1
2
3

OutputQuery OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
1
2

如果你再次查询clients表的内容，结果集将确认你的四个客户端现在具有相同的性能费用standardFee:

SELECT * FROM clients;
1

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)
1
2
3
4
5
6
7
8
9
10

如果你的表中有任何列保存了数值，你可以在SET子句中使用算术运算来更新它们。举例来说，假设你还为每个客户的业绩费增加40%。为了在clients表中反映这一点，你可以运行一个UPDATE操作:

UPDATE clients
SET standardFee = standardFee * 1.4;
1
2

OutputQuery OK, 5 rows affected, 1 warning (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 1
1
2

注意：此输出表明更新导致了警告。通常情况下，当MySQL因为某个列或表的定义而被迫修改数据时，它会发出警告。

MySQL提供了SHOW WARNINGS快捷方式，可以帮助解释你收到的任何警告:

SHOW WARNINGS;
1

Output+-------+------+--------------------------------------------------+
| Level | Code | Message                                          |
+-------+------+--------------------------------------------------+
| Note  | 1265 | Data truncated for column 'standardFee' at row 5 |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

这个输出告诉我们，数据库系统发出了警告，因为它必须截断其中一个新的standardFee值，以符合之前定义的decimal格式——5个数字，小数点右边2个。

再次查询clients表，确认每个客户的绩效费用都增加了40%。

SELECT * FROM clients;
1

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)
1
2
3
4
5
6
7
8
9
10

如前所述，您也可以使用单个UPDATE语句更新多个列中的数据。要做到这一点，必须指定要更新的每一列，在每一列后面加上相应的值表达式，然后用逗号分隔每一列和值表达式对。

例如，假设您了解到您的客户表演的场地错误报告了Georgeanna和Wanda所有演出的出席人数。巧合的是，你也碰巧输入了他们每场演出的票价。

在更新shows表中的数据之前，运行以下查询以检索当前保存在其中的所有数据:

SELECT * FROM shows;
1

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

为了反映实际的人数和价格，您可以更新该表，将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');
1
2
3
4
5
6
7

OutputQuery OK, 4 rows affected, 1 warning (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 1
1
2

注意，这个例子使用了WHERE子句中的子查询，从clients表中返回Georgeanna和Wanda的clientID值。通常情况下，像标识号这样的抽象值很难记住，但是当你只知道记录的某些属性时，这种使用子查询来查找值的方法可能很有用。

更新shows表后，再次查询它以确认更改是否按预期进行:

SELECT * FROM shows;
1

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

同样，这个输出表明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;
1
2
3
4
5

这个示例语法以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 = 30
WHERE name = 'Gladys';
1
2
3
4
5

OutputQuery OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
1
2

注意，这个例子使用USING关键字来连接表，而不是前面示例语法中使用的ON关键字。这是可能的，因为两张表都有一个共享类似数据类型的clientID列。

修改外键

默认情况下，任何与FOREIGN KEY约束冲突的UPDATE语句都会失败。

回忆一下先决条件中连接到MySQL并设置一个示例数据库的部分，shows表的clientID列是一个外键，引用clients表的clientID列。这意味着输入到shows表的clientID列中的任何值都必须已经存在于clients表中。

如果你试图更新clients表中同时出现在shows表的clientID列中的记录的clientID值，它将导致错误:

UPDATE clients
SET clientID = 9
WHERE name = 'Ann';
1
2
3

OutputERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
1

您可以通过将现有的外键约束替换为以不同方式处理UPDATE操作的约束来避免此错误。

注意：并不是每个关系型数据库管理系统或引擎都允许你在现有表中添加或删除约束，如下所述。如果你使用的是MySQL以外的RDBMS，应该查阅它的官方文档，以了解它在管理约束方面有哪些限制。

要替换当前的约束，你必须首先用ALTER TABLE语句删除它。回想一下，在shows的CREATE TABLE语句中，我们定义了client_fk作为表的FOREIGN KEY约束的名称:

ALTER TABLE shows
DROP FOREIGN KEY client_fk;
1
2

OutputQuery OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2

接下来，创建一个新的外键约束，该约束被配置为以对给定用例有意义的方式处理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选项:

ALTER TABLE shows
ADD CONSTRAINT new_client_fk
FOREIGN KEY (clientID)
REFERENCES clients (clientID)
ON UPDATE CASCADE;
1
2
3
4
5

OutputQuery OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0
1
2

这个输出告诉你操作影响了shows表中的所有10行。

注意：与其改变表的定义来改变外键如何处理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
);
1
2
3
4
5
6
7
8
9
10
11

接下来，你将能够更新clients表中任何记录的clientID值，这些更改将级联到shows表中引用它的任何行:

UPDATE clients
SET clientID = 9
WHERE name = 'Ann';
1
2
3

OutputQuery OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2

虽然这个输出说它只影响了一行，但它也更新了shows表中与Ann关联的所有性能记录的clientID值。为了确认这一点，运行以下查询来检索shows表中的所有数据:

SELECT * FROM shows;
1

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

不出所料，对clients表中clientID列的更新会级联到shows表中的相关行。

总结

通过阅读本指南，您学习了如何使用SQL的UPDATE语句更改或更多表中的现有记录。你还学习了SQL如何处理与外键约束冲突的UPDATE操作，以及如何更改默认行为。

这里列出的命令应该适用于任何使用SQL的数据库管理系统。请记住，每个SQL数据库都使用自己独特的语言实现，因此你应该查阅相应DBMS的官方文档，以更完整地描述它如何处理UPDATE操作以及它们有哪些可用选项。