介绍
例如,您可能需要纠正错误的输入,或者您可能有新的信息要添加到不完整的记录中。
本指南概述了如何使用 SQL 的更新语法来更改一个或多个表中的数据,并解释了 SQL 如何处理与外部密钥限制相冲突的更新操作。
前提条件
要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。
- 运行 Ubuntu 20.04 的服务器,具有非 root 用户管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 中所描述的。
<$>[注] 注:请注意,许多RDBMS使用自己的独特的SQL实现程序,虽然本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。
- 您还需要一个数据库,其中有一些表载有样本数据,您可以使用它来练习更新 SQL 数据. 我们鼓励您通过以下 连接到 MySQL 和设置样本数据库部分,以了解如何连接到 MySQL 服务器并创建本指南中使用的测试数据库的详细信息。
连接到MySQL并设置样本数据库
如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:
1[environment local]
2ssh sammy@your_server_ip
然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:
1mysql -u sammy -p
创建一个名为updateDB的数据库:
1CREATE DATABASE updateDB;
如果数据库创建成功,您将收到这样的输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
若要选择updateDB数据库,请运行以下USE语句:
1USE updateDB;
1[secondary_label Output]
2Database changed
选择更新DB数据库后,在其内创建几个表. 对于本指南中使用的示例,想象一下,您正在运行一个人才机构,并已决定在 SQL 数据库中开始跟踪您的客户和他们的表现。
这个列也将作为表的主要密钥,每个值作为其相应行
- 名称的独特标识符运作:每个客户的名称,用最多 20 个字符
- 常规表示使用
varchar数据类型:每个客户的主要性能类型的简要描述,再次用varchar数据类型表示,但最多 30 个字符 - 性能费:一个列用于记录每个客户的标准性能费,它使用这个列中的任何值限制在最多五位数的长度,其中两个数字在十进制点右侧。
创建一个名为客户的表,其中包含以下四个列:
1CREATE TABLE clients
2(clientID int PRIMARY KEY,
3name varchar(20),
4routine varchar(30),
5standardFee decimal (5,2)
6);
第二个表格将存储有关客户在本地场所的表现的信息. 您决定该表需要五个列:
showID:类似于clientID列,本列将为每个节目提供一个独特的识别号码,用int数据类型表示。同样,本列将作为show表的主要密钥- showDate:每个表演的日期。本列的值用
date数据类型表示,该数据类型使用YYYYY-MM-DD格式 clientID:在节目中表演的客户的 ID 号码,以整数attendance表示:每个表演的参观者数量,以整数ticketprice表示:每个节目中个人门票的价格。本列使用 `decimal data' 类型,
要确保clientID列只包含代表有效的客户 ID 号码的值,您决定将一个 foreign key 限制应用于clientID列,该列指向客户表的clientID列。
创建一个名为客户的表,其中包含以下五个列:
1CREATE TABLE shows
2(showID int PRIMARY KEY,
3showDate date,
4clientID int,
5attendance int,
6ticketPrice decimal (4,2),
7CONSTRAINT client_fk
8FOREIGN KEY (clientID)
9REFERENCES clients(clientID)
10);
请注意,此示例为外钥限制提供一个名称:client_fk. MySQL 将自动生成您添加的任何限制的名称,但在我们需要稍后参考此限制时,在这里定义一个名称将是有用的。
接下来,运行下面的INSERT INTO语句来加载客户表,其中包含五行样本数据:
1INSERT INTO clients
2VALUES
3(1, 'Gladys', 'song and dance', 180),
4(2, 'Catherine', 'standup', 99.99),
5(3, 'Georgeanna', 'standup', 45),
6(4, 'Wanda', 'song and dance', 200),
7(5, 'Ann', 'trained squirrel', 79.99);
然后运行另一个INSERT INTO语句来加载显示表,其中包含十行样本数据:
1INSERT INTO shows
2VALUES
3(1, '2019-12-25', 4, 124, 15),
4(2, '2020-01-11', 5, 84, 29.50),
5(3, '2020-01-17', 3, 170, 12.99),
6(4, '2020-01-31', 5, 234, 14.99),
7(5, '2020-02-08', 1, 86, 25),
8(6, '2020-02-14', 3, 102, 39.5),
9(7, '2020-02-15', 2, 101, 26.50),
10(8, '2020-02-27', 2, 186, 19.99),
11(9, '2020-03-06', 4, 202, 30),
12(10, '2020-03-07', 5, 250, 8.99);
有了这一点,您已经准备好跟随本指南的其余部分,并开始学习如何使用 SQL 更新数据。
在单一表中更新数据
一个更新陈述的通用语法看起来如下:
1UPDATE table_name
2SET column_name = value_expression
3WHERE conditions_apply;
在更新的关键字后面是存储您要更新的数据的表名称,然后是SET条款,该条款规定哪个列的数据应该更新以及如何更新。
在 SQL 中,一个值表达式(有时被称为 _scalar 表达式)是指每个更新的行都会返回一个单一值的表达式,这可能是字母字符串,或者是对列中现有的数值执行的数学操作。
在SET条款之后,有一个WHERE条款。在UPDATE陈述中包含一个WHERE条款,如本示例中的语法允许您过滤任何您不希望更新的行。
要说明 SQL 如何处理更新操作,请先查看客户表中的所有数据。 以下查询包含一个星座(*),即表示表中的每个列的 SQL 缩写,因此此查询将返回客户表中的每个列的所有数据:
1SELECT * FROM clients;
1[secondary_label Output]
2+----------+------------+------------------+-------------+
3| clientID | name | routine | standardFee |
4+----------+------------+------------------+-------------+
5| 1 | Gladys | song and dance | 180.00 |
6| 2 | Catherine | standup | 99.99 |
7| 3 | Georgeanna | standup | 45.00 |
8| 4 | Wanda | song and dance | 200.00 |
9| 5 | Ann | trained squirrel | 79.99 |
10+----------+------------+------------------+-------------+
115 rows in set (0.00 sec)
例如,假设您注意到凯瑟琳的名字写错了 - 它应该从K开始,但在表中它从C开始 - 所以您决定通过运行以下更新陈述来更改该值。
1UPDATE clients
2SET name = 'Katherine'
3WHERE name = 'Catherine';
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
3Rows matched: 1 Changed: 1 Warnings: 0
此输出表示只更新了一行,您可以通过重新运行之前的SELECT查询来确认此情况:
1SELECT * FROM clients;
1[secondary_label Output]
2+----------+------------+------------------+-------------+
3| clientID | name | routine | standardFee |
4+----------+------------+------------------+-------------+
5| 1 | Gladys | song and dance | 180.00 |
6| 2 | Katherine | standup | 99.99 |
7| 3 | Georgeanna | standup | 45.00 |
8| 4 | Wanda | song and dance | 200.00 |
9| 5 | Ann | trained squirrel | 79.99 |
10+----------+------------+------------------+-------------+
115 rows in set (0.00 sec)
此输出显示,以前读到的Catherine的值确实被更改为Katherine。
此示例只更新了名称列中的一个值,但是,您可以使用不那么独家的WHERE条款更新多个值。
举例来说,想象一下,您正在为所有执行站式喜剧或歌曲和舞蹈习惯的客户谈判标准性能费用,下面的陈述将更新标准费列中的值,将其设置为140。
请注意,本示例的WHERE条款包括一个LIKE运算器,因此它只更新每个客户端的常规值匹配指定的 wildcard 模式,%值的PerformanceFee值。
1UPDATE clients
2SET standardFee = 140
3WHERE routine LIKE 's%';
1[secondary_label Output]
2Query OK, 4 rows affected (0.00 sec)
3Rows matched: 4 Changed: 4 Warnings: 0
如果您再次查询客户表的内容,结果集将确认您的四个客户现在有相同的性能费用:
1SELECT * FROM clients;
1[secondary_label Output]
2+----------+------------+------------------+-------------+
3| clientID | name | routine | standardFee |
4+----------+------------+------------------+-------------+
5| 1 | Gladys | song and dance | 140.00 |
6| 2 | Katherine | standup | 140.00 |
7| 3 | Georgeanna | standup | 140.00 |
8| 4 | Wanda | song and dance | 140.00 |
9| 5 | Ann | trained squirrel | 79.99 |
10+----------+------------+------------------+-------------+
115 rows in set (0.00 sec)
如果你的表中的任何列都包含数字值,你可以使用SET条款中的算术操作来更新它们. 为了说明,假设你也谈判为每个客户的绩效费用增加40%。 要在客户表中反映这一点,你可以运行这样的UPDATE操作:
1UPDATE clients
2SET standardFee = standardFee * 1.4;
1[secondary_label Output]
2Query OK, 5 rows affected, 1 warning (0.00 sec)
3Rows matched: 5 Changed: 5 Warnings: 1
<$>[注] 注:请注意,此输出表示更新导致了警告. 通常,MySQL会发出警告,当它因列或表的定义而被迫对您的数据进行更改。
MySQL 提供「SHOW WARNINGS」捷径,可以帮助解释您收到的任何警告:
1SHOW WARNINGS;
1[secondary_label Output]
2+-------+------+--------------------------------------------------+
3| Level | Code | Message |
4+-------+------+--------------------------------------------------+
5| Note | 1265 | Data truncated for column 'standardFee' at row 5 |
6+-------+------+--------------------------------------------------+
71 row in set (0.00 sec)
此输出告知我们,数据库系统发出警告,因为它不得不缩小一个新的标准费值,以便它符合以前定义的十进制格式 - 五位数与二位数右边的十进制点。
再次查询客户表,以确认每个客户的绩效费用增加了40%。
1SELECT * FROM clients;
1[secondary_label Output]
2+----------+------------+------------------+-------------+
3| clientID | name | routine | standardFee |
4+----------+------------+------------------+-------------+
5| 1 | Gladys | song and dance | 196.00 |
6| 2 | Katherine | standup | 196.00 |
7| 3 | Georgeanna | standup | 196.00 |
8| 4 | Wanda | song and dance | 196.00 |
9| 5 | Ann | trained squirrel | 111.99 |
10+----------+------------+------------------+-------------+
115 rows in set (0.00 sec)
如前所述,您还可以使用单个更新语句更新多个列中的数据. 要做到这一点,您必须指定要更新的每一个列,然后按相应的值表达式来跟随每个列,然后用字符串将每个列和值表达式对分开。
例如,假设你发现你的客户表演的场所错误地报告了Georgeanna和Wanda的所有节目的参观者数量。
在更新显示表中的数据之前,运行以下查询以获取当前存储在表中的所有数据:
1SELECT * FROM shows;
1[secondary_label Output]
2+--------+------------+----------+------------+-------------+
3| showID | showDate | clientID | attendance | ticketPrice |
4+--------+------------+----------+------------+-------------+
5| 1 | 2019-12-25 | 4 | 124 | 15.00 |
6| 2 | 2020-01-11 | 5 | 84 | 29.50 |
7| 3 | 2020-01-17 | 3 | 170 | 12.99 |
8| 4 | 2020-01-31 | 5 | 234 | 14.99 |
9| 5 | 2020-02-08 | 1 | 86 | 25.00 |
10| 6 | 2020-02-14 | 3 | 102 | 39.50 |
11| 7 | 2020-02-15 | 2 | 101 | 26.50 |
12| 8 | 2020-02-27 | 2 | 186 | 19.99 |
13| 9 | 2020-03-06 | 4 | 202 | 30.00 |
14| 10 | 2020-03-07 | 5 | 250 | 8.99 |
15+--------+------------+----------+------------+-------------+
1610 rows in set (0.01 sec)
为了反映实际数字和价格,您可以更新表格,将20名参赛者添加到他们的每个表演,并将每个票价值增加50%。
1UPDATE shows
2SET attendance = attendance + 20,
3ticketPrice = ticketPrice * 1.5
4WHERE clientID IN
5(SELECT clientID
6FROM clients
7WHERE name = 'Georgeanna' OR name = 'Wanda');
1[secondary_label Output]
2Query OK, 4 rows affected, 1 warning (0.00 sec)
3Rows matched: 4 Changed: 4 Warnings: 1
请注意,本示例使用WHERE条款中的子查询来从客户表中返回Georgeanna和Wanda的clientID值。
更新显示表后,请再次查询,以确认更改按预期进行:
1SELECT * FROM shows;
1[secondary_label Output]
2+--------+------------+----------+------------+-------------+
3| showID | showDate | clientID | attendance | ticketPrice |
4+--------+------------+----------+------------+-------------+
5| 1 | 2019-12-25 | 4 | 144 | 22.50 |
6| 2 | 2020-01-11 | 5 | 84 | 29.50 |
7| 3 | 2020-01-17 | 3 | 190 | 19.49 |
8| 4 | 2020-01-31 | 5 | 234 | 14.99 |
9| 5 | 2020-02-08 | 1 | 86 | 25.00 |
10| 6 | 2020-02-14 | 3 | 122 | 59.25 |
11| 7 | 2020-02-15 | 2 | 101 | 26.50 |
12| 8 | 2020-02-27 | 2 | 186 | 19.99 |
13| 9 | 2020-03-06 | 4 | 222 | 45.00 |
14| 10 | 2020-03-07 | 5 | 250 | 8.99 |
15+--------+------------+----------+------------+-------------+
1610 rows in set (0.00 sec)
再次,此输出表示更新声明已成功完成。
使用JOIN条款更新多个表中的数据
到目前为止,本指南只显示了如何一次更新一个表中的数据,但是,一些SQL实现程序允许您通过暂时将表与JOIN条款相结合来更新多个表中的多个列。
以下是您可以使用的通用语法以JOIN条款更新多个表:
1UPDATE table_1 JOIN table_2
2ON table_1.related_column = table_2.related_column
3SET table_1.column_name = value_expression,
4table_2.column_name = value_expression
5WHERE conditions_apply;
此示例语法始于更新的关键字,然后是两个表的名称,由一个JOIN条款分开,然后是ON条款,描述了查询如何将两个表连接在一起。
在大多数实现中,您可以通过找到具有 SQL 标准称为‘JOIN’合格的数据类型的任何一组列之间的匹配来加入表,这意味着,一般来说,您可以将持有数字数据的任何列与持有数字数据的任何其他列结合起来,而不论它们的数据类型如何。
请注意,因为JOIN条款比较了多个表的内容,所以本示例语法指定哪个表选择每个列,通过以表名和期限为前列的名称。
若要使用先前创建的示例表进行示范,请运行以下更新声明,将客户和显示表加入各自的客户ID列,然后更新例行和票价值,以便在客户表中更新格拉迪斯的记录和显示表中列出的每个表现:
1UPDATE clients JOIN shows
2USING (clientID)
3SET clients.routine = 'mime',
4shows.ticketPrice = 30
5WHERE name = 'Gladys';
1[secondary_label Output]
2Query OK, 2 rows affected (0.01 sec)
3Rows matched: 2 Changed: 2 Warnings: 0
请注意,此示例将表与使用关键字相结合,而不是在上一个示例语法中使用的ON关键字。
有关JOIN操作的更深入的教程,请参阅我们在 如何在SQL中使用 Joins上的指南。
更改外部密钥更新行为
默认情况下,任何会导致与外部密钥限制发生冲突的更新声明都会失败。
从连接到MySQL和设置示例数据库的前提条件部分中回忆一下,显示表的clientID列是指客户表的clientID列的外部密钥,这意味着显示表的clientID列中输入的任何值都必须在客户表中存在。
如果您尝试更新客户表中的记录的clientID值,该记录也出现在显示表的clientID列中,则会导致错误:
1UPDATE clients
2SET clientID = 9
3WHERE name = 'Ann';
1[secondary_label Output]
2ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
您可以通过替换现有的外部密钥限制,以一种以不同的方式处理更新操作来避免此错误。
<$>[注意] 注意:不是每个关系数据库管理系统或引擎允许您在以下段落中描述的现有表中添加或删除限制。
要取代当前的限制,您必须先用ALTER TABLE语句删除它,请记住,在显示的CREATE TABLE语句中,我们将client_fk定义为表的FOREIGN KEY限制名称:
1ALTER TABLE shows
2DROP FOREIGN KEY client_fk;
1[secondary_label Output]
2Query OK, 0 rows affected (0.01 sec)
3Records: 0 Duplicates: 0 Warnings: 0
然后,创建一个新的外部密钥限制,该限制被配置为对待UPDATE操作,以便对所述使用情况有意义。
ON UPDATE SET NULL:此选项将允许您从母表中更新记录,并将重置将其引用为NULL的子表中的任何值。ON UPDATE CASCADE:当您更新母表中的行时,此选项将导致 SQL 自动更新在母表中引用其中的任何记录,以便它们与母表中的新值一致。
在本示例中,ON UPDATE SET NULL没有意义,毕竟,如果您更改客户的识别号码,但不将其从客户表中删除,他们仍然应该与他们在显示表中的表现相关联。
若要添加遵循ON UPDATE CASCADE行为的FOREIGN KEY约束,请运行以下ALTER TABLE声明,从而创建一个名为new_client_fk的新约束,该约束复制了以前的约束定义,但包含了ON UPDATE CASCADE选项:
1ALTER TABLE shows
2ADD CONSTRAINT new_client_fk
3FOREIGN KEY (clientID)
4REFERENCES clients (clientID)
5ON UPDATE CASCADE;
1[secondary_label Output]
2Query OK, 10 rows affected (0.02 sec)
3Records: 10 Duplicates: 0 Warnings: 0
此输出告诉您,该操作影响了显示表中的所有十行。
<$>[注]
注:而不是更改表的定义以更改外部密钥如何处理更新操作,您可以在创建表声明中从一开始就定义此行为:
1CREATE TABLE shows
2(showID int PRIMARY KEY,
3showDate date,
4clientID int,
5attendance int,
6ticketPrice decimal (4,2),
7CONSTRAINT client_fk
8FOREIGN KEY (clientID)
9REFERENCES clients(clientID)
10ON UPDATE CASCADE
11);
美元
接下来,您将能够更新客户端表中的任何记录的客户端ID值,并且这些更改将降至显示表中的任何行,其中引用它:
1UPDATE clients
2SET clientID = 9
3WHERE name = 'Ann';
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
3Rows matched: 1 Changed: 1 Warnings: 0
虽然此输出表示它只影响了一行,但它还会更新与 Ann 相关的显示表中的任何性能记录的clientID值。
1SELECT * FROM shows;
1[secondary_label Output]
2+--------+------------+----------+------------+-------------+
3| showID | showDate | clientID | attendance | ticketPrice |
4+--------+------------+----------+------------+-------------+
5| 1 | 2019-12-25 | 4 | 144 | 22.50 |
6| 2 | 2020-01-11 | 9 | 84 | 29.50 |
7| 3 | 2020-01-17 | 3 | 190 | 19.49 |
8| 4 | 2020-01-31 | 9 | 234 | 14.99 |
9| 5 | 2020-02-08 | 1 | 86 | 30.00 |
10| 6 | 2020-02-14 | 3 | 122 | 59.25 |
11| 7 | 2020-02-15 | 2 | 101 | 26.50 |
12| 8 | 2020-02-27 | 2 | 186 | 19.99 |
13| 9 | 2020-03-06 | 4 | 222 | 45.00 |
14| 10 | 2020-03-07 | 9 | 250 | 8.99 |
15+--------+------------+----------+------------+-------------+
1610 rows in set (0.00 sec)
正如预期的那样,在客户表中的clientID列上进行的更新将逐步降至显示表中的相关行。
结论
通过阅读本指南,您了解如何使用 SQL 的UPDATE语句更改在或多个表中的现有记录,您还了解如何处理与外部密钥约束相冲突的UPDATE操作,以及如何更改此默认行为。
请记住,每个 SQL 数据库都使用其独特的语言实现,所以您应该咨询您的 DBMS 的官方文档,以便更全面地描述它如何处理UPDATE操作以及可用的选项。
如果您想了解有关使用 SQL 的更多信息,我们鼓励您查看本系列中的其他教程在 如何使用 SQL。