11 个基本 MySQL 更新命令示例

最常见的 MySQL 操作之一是更改表中记录的现有值。

在本文中，我们将解释如何使用 MySQL 更新命令以及一些有用的示例。

本教程涵盖以下内容：

1. 更新所有行
2. 仅更新选定的行
3. 用表达式更新列值
4. 更新中的 DEFAULT 关键字用法
5. 同时更新多列
6. 在更新中使用 LIMIT 子句
7. 多表更新（使用内连接）
8. 多表更新（使用左连接）
9. 返回更新值（或更新前值）
10. 将 CASE 或 IF 语句与 Update 结合使用
11. 为什么使用 ORDER by Clause with Update？

在本教程中，我们将使用以下员工表作为示例。这是此示例表的结构。

mysql> DESC employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| dept   | varchar(10) | YES  |     | Sales   |                |
| salary | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

目前雇员表有以下记录。

mysql> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
| 501 | Ritu   | Accounting |   NULL |
+-----+--------+------------+--------+

如果您不熟悉 MySQL，您可能应该首先了解MySQL 基础知识，包括如何创建 MySQL 数据库。

1. 更新所有行

在以下基本示例中，此更新命令将员工表中所有行的 dept 列的值设置为 Technology。

mysql> UPDATE employee SET dept='Technology';
Query OK, 3 rows affected (0.02 sec)
Rows matched: 6  Changed: 3  Warnings: 0

UPDATE 命令的输出将有以下两行：

• 第 1 行：如果执行了查询，这将显示“查询确定”。如果有语法错误，它会在这里显示。即使它没有更新任何记录，只要没有语法错误并且语句是干净的，该行仍然会说“Query OK”。此行还将显示此查询更新了多少记录（例如：受影响的 3 行）。最后，这还将显示 MySQL 执行查询所需的时间（例如：0.02 秒）。
• 第 2 行：这将说明与更新语句的条件匹配的记录数。在此示例中，没有 WHERE 条件来限制应考虑进行更新的记录数（因此，它表示：匹配的行数：6）。接下来，这将显示实际更新了多少记录（例如：Changed: 3）。最后，它将显示更新期间有多少警告。在大多数情况下，当一切正常时，您会看到警告为 0。

以下是上述更新命令后的更新记录。

mysql> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Technology |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Technology |   9500 |
| 500 | Randy  | Technology |   6000 |
| 501 | Ritu   | Technology |   NULL |
+-----+--------+------------+--------+

2. 只更新选定的行

您可以根据 WHERE 条件有选择地更新某些记录，而不是更新所有记录。

下面的示例将更新员工表，并将所有工资大于或等于 7000 的员工分配到市场部。

mysql> UPDATE employee SET dept='Marketing' WHERE salary >=7000;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

只有两条记录符合上述 WHERE 条件，更新如下。

mysql> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Technology |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Marketing  |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
| 501 | Ritu   | Technology |   NULL |
+-----+--------+------------+--------+

3. 用表达式更新列值

在 SET 之后为列分配值时，不必总是指定静态值。您还可以使用如下所示的表达式。

下面是一个非常简单的表达式，它将技术部门所有员工的薪水值增加 500。

mysql> UPDATE employee SET salary=salary+500 WHERE dept='Technology';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4  Changed: 3  Warnings: 0

只有 4 条记录符合上述 WHERE 条件。但是只有三条记录被更新，如下所示，因为属于技术部门的员工记录之一在工资字段中具有 NULL 值。所以，上面的salary+500 表达式也变成了NULL，并且它没有更新那个特定的记录。

mysql> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Technology |   5500 |
| 200 | Jason  | Technology |   6000 |
| 300 | Mayla  | Marketing  |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6500 |
| 501 | Ritu   | Technology |   NULL |
+-----+--------+------------+--------+

4. 将列值更新为 DEFAULT

除了指定静态值或表达式之外，您还可以在为 SET 后的列分配值时使用关键字“DEFAULT”。

如果您查看下面显示的“DESC 员工”的输出，您会看到有一个名为 Default 的列。正如您在那里看到的，薪水的 DEFAULT 值为 NULL。该部门的 DEFAULT 值为 Sales。

mysql> DESC employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| dept   | varchar(10) | YES  |     | Sales   |                |
| salary | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

首先，让我们使用 DEFAULT 关键字将工资列更新为默认值，如下所示。

mysql> UPDATE employee SET salary=DEFAULT;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 6  Changed: 5  Warnings: 0

接下来，使用 DEFAULT 关键字将部门列更新为默认值，如下所示。

mysql> UPDATE employee SET dept=DEFAULT;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

正如您从下面的输出中看到的，我们可以看到上面的 DEFAULT 关键字从员工表定义中获取了相应的默认值，并使用它们来更新它。Department 列更新为 Sales，salary 更新为 NULL，如下所示。

mysql> SELECT * FROM employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   NULL |
| 200 | Jason  | Sales |   NULL |
| 300 | Mayla  | Sales |   NULL |
| 400 | Nisha  | Sales |   NULL |
| 500 | Randy  | Sales |   NULL |
| 501 | Ritu   | Sales |   NULL |
+-----+--------+-------+--------+

5.同时更新多列

在单个更新语句中，您还可以更新多个列的值，如下所示。

在以下示例中，我们为员工 ID 大于 300 的所有记录同时为工资和部门列赋值。

mysql> UPDATE employee SET salary=5000, dept='Marketing' WHERE id > 300;
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

正如您从以下输出中看到的，上面的 update 命令更新了与上述 WHERE 条件匹配的最后三个记录的两个列值。

mysql> SELECT * FROM employee;
+-----+--------+-----------+--------+
| id  | name   | dept      | salary |
+-----+--------+-----------+--------+
| 100 | Thomas | Sales     |   NULL |
| 200 | Jason  | Sales     |   NULL |
| 300 | Mayla  | Sales     |   NULL |
| 400 | Nisha  | Marketing |   5000 |
| 500 | Randy  | Marketing |   5000 |
| 501 | Ritu   | Marketing |   5000 |
+-----+--------+-----------+--------+

6. 限制要更新的记录数

我们还可以使用 LIMIT 选项来限制应该更新的记录数。

即使 where 条件匹配更多记录，更新语句也只会更新 LIMIT 值指定的第一个 X 条记录。

在下面的示例中，我们将所有记录的工资分配给 6500，因为我们没有 where 条件。但是，我们使用的是 LIMIT 3。这意味着它只会更新匹配条件的前三个记录的工资。

mysql> UPDATE employee SET salary=6500 LIMIT 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

正如您从以下输出中看到的，只有前三个记录被上述命令更新。

mysql> SELECT * FROM employee;
+-----+--------+-----------+--------+
| id  | name   | dept      | salary |
+-----+--------+-----------+--------+
| 100 | Thomas | Sales     |   6500 |
| 200 | Jason  | Sales     |   6500 |
| 300 | Mayla  | Sales     |   6500 |
| 400 | Nisha  | Marketing |   5000 |
| 500 | Randy  | Marketing |   5000 |
| 501 | Ritu   | Marketing |   5000 |
+-----+--------+-----------+--------+

要记住的一件事是 LIMIT 3 并不真的意味着更新将继续进行，直到它更新 3 条记录。相反，更新语句将在处理完与 where 条件匹配的前 3 行后立即停止执行，而不管这些记录是否真的被更新。

7.多表更新（使用Inner Join）

您还可以在更新期间合并两个表。您还可以使用单个更新语句同时更新两个表中的值。

在此示例中，我们将使用以下福利表以及我们现有的员工表。

mysql> SELECT * from benefits;
+------------+-------+
| dept       | bonus |
+------------+-------+
| Sales      |  1000 |
| Technology |  NULL |
| Marketing  |   800 |
+------------+-------+

以下更新语句将employee 表中salary 列的值增加到benefit 表中为相应dept 列指定的值。

这意味着我们必须在 UPDATE 期间合并员工和福利表，如下所示。在 WHERE 子句中使用这两个表之间的公共字段。在此示例中，公共字段是 dept。

此外，在 UPDATE 关键字之后，指定两个表的名称，如下所示。在 SET 关键字之后，您可以指定一个或多个需要从一个表或两个表中更新的列名。

mysql> UPDATE employee,benefits 
    -> SET employee.salary=employee.salary+benefits.bonus 
    -> WHERE employee.dept=benefits.dept and benefits.bonus is not null;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

请注意，在上面的更新语句中，我们使用了内连接。

以下是执行上述更新语句后的输出。如下所示，员工的工资根据上述福利表中的值增加。

mysql> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   7500 |
| 200 | Jason  | Sales      |   7500 |
| 300 | Mayla  | Technology |   6500 |
| 400 | Nisha  | Technology |   5000 |
| 500 | Randy  | Marketing  |   5800 |
| 501 | Ritu   | Marketing  |   5800 |
+-----+--------+------------+--------+

8. 多表更新（使用左连接）

与前面的内连接示例类似，我们也可以使用左连接。

当我们使用内连接时，我们不指定关键字“内连接”，因为这是组合多个表时的默认值。

然而，当使用左连接时，我们应该明确指定“左连接”，如下所示。

mysql> UPDATE employee LEFT JOIN benefits on employee.dept = benefits.dept
    -> SET employee.salary = employee.salary+500 
    -> WHERE benefits.bonus is null;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

这是上述更新后的输出。

mysql> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   7500 |
| 200 | Jason  | Sales      |   7500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Technology |   5500 |
| 500 | Randy  | Marketing  |   5800 |
| 501 | Ritu   | Marketing  |   5800 |
+-----+--------+------------+--------+

9. 返回更新值（或更新前值）

在 MySQL 中，在 update 命令中，没有直接获取新更新值的方法。

例如，在 PostgreSQL 中，我们可以这样使用：“UPDATE table_name SET column_name = expression WHERE condition RETURNING column_name。在 MySQL 中，我们没有 RETURNING 概念作为 MySQL 更新命令的一部分。

但是，您可以执行以下操作以在您的过程、包、函数中或从命令行获取返回值。

mysql> UPDATE employee SET salary = salary+500 WHERE id=400;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT salary AS tmp_salary FROM employee WHERE id=400;
+------------+
| tmp_salary |
+------------+
|       5500 |
+------------+

在上面的示例中，更新完成后，tmp_salary 变量将具有更新后的员工 id 工资

如果您想在更新完成之前获取 Salary 的值，显然您只需要切换上述两个语句的顺序即可。在这种情况下，您将首先执行 SELECT，然后执行 UPDATE。

或者，您可以使用“@”作为 UPDATE 语句的一部分并获取更新前的值，如下所示。

UPDATE employee SET salary = salary+500 WHERE id=400 
  AND @tmp_salary := salary

在上面的更新命令中，更新完成后，tmp_salary 变量将具有员工 id 400 的预更新工资。如您所见，即使工资值已经更新为 6000。 tmp_salary 变量在上面的 UPDATE 命令的值仍然是 5500。

mysql> SELECT * FROM employee WHERE id = 400;
+-----+-------+------------+--------+
| id  | name  | dept       | salary |
+-----+-------+------------+--------+
| 400 | Nisha | Technology |   6000 |
+-----+-------+------------+--------+

mysql> SELECT @tmp_salary;
+-------------+
| @tmp_salary |
+-------------+
|        5500 |
+-------------+

10. 将 CASE 或 IF 语句与 Update 结合使用

您还可以使用 MySQL 条件命令（如 CASE、IF 等）来使用条件更新。这有助于简化您的更新。

您可能只使用组合所有案例的单个 UPDATE 命令，而不是使用多个更新。

例如，假设我们有以下三个更新语句，它们根据来自 dept 字段的值更新工资字段。

UPDATE employee SET salary = salary+1000 WHERE dept = 'Sales';
UPDATE employee SET salary = salary+500 WHERE dept = 'Technology';
UPDATE employee SET salary = salary+800 WHERE dept = 'Marketing';

您可以使用 CASE 条件将上述所有三个 UPDATE 语句组合成一个单个 UPDATE 语句，如下所示。

UPDATE employee SET salary = 
  CASE dept
   WHEN 'Sales' THEN salary+1000
   WHEN 'Technology' THEN salary+500
   WHEN 'Marketing' THEN salary+500
   ELSE salary 
  END;

就像 CASE 一样，您也可以使用 IF 条件来相应地更新列值。

11. 按条款订购并更新

您可以在更新期间使用 ORDER BY 值。ORDER BY 子句在 SELECT 语句中绝对有意义。但是，为什么我们在更新期间需要 ORDER BY。

假设您在员工表的 id 字段上有一个唯一的 id。

当您执行以下命令将员工 ID 增加 100 时，您可能会收到一条 DUPLICATE 错误消息。

mysql> UPDATE contractor set id=id+100;
ERROR 1062 (23000): Duplicate entry '200' for key 'PRIMARY'

这是因为当它尝试将 id 的值从 100 更新到 200 时，已经存在一个 id 为 200 的记录。 ID 字段也有一个 UNIQUE 约束，在这种情况下它是 PRIMARY 键。所以，我们得到了上述错误。

为此，我们必须使用 ORDER BY id desc 执行以下命令。

mysql> UPDATE employee SET id=id+100 order by id desc;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

在上述情况下，它将首先从最大员工 ID 开始，更新该记录，然后继续下一个。这样，它在更新过程中永远不会出现重复值的情况。