在本教程中，我们将学习在MySQL中更新表的数据。

我们使用" UPDATE table_name"命令来更新表中的数据。

更新语法

UPDATE table_name
SET column_name = value
WHERE condition;

使用主键更新单行的列

在以下示例中，我们将更新员工编号" e05"的员工得分。

在更新之前：

mysql> SELECT * FROM employee WHERE employeeid = 'e05';
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e05        | Bob       | Coder    | [email protected] |     5 | 1900-08-20 | 2016-01-01 01:02:10 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

更新查询：

mysql> UPDATE employee
SET score = 8
WHERE employeeid = 'e05';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

UPDATE之后的员工数据：

mysql> SELECT * FROM employee WHERE employeeid = 'e05';
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e05        | Bob       | Coder    | [email protected] |     8 | 1900-08-20 | 2016-01-01 01:02:10 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

使用主键更新一行的多列

要更新多个列，我们使用逗号分隔它们。

在以下示例中，我们将更新员工编号" e05"的得分和最后修改的列。

mysql> UPDATE employee
SET score = 7,
lastmodified = '2016-01-02 03:04:05'
WHERE employeeid = 'e05';

更新后，我们得到以下结果。

mysql> SELECT * FROM employee WHERE employeeid = 'e05';
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e05        | Bob       | Coder    | [email protected] |     7 | 1900-08-20 | 2016-01-02 03:04:05 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

给定匹配项更新多行的列

在下面的示例中，我们将更新所有姓氏为" Doe"的员工的得分和最后修改的数据。

mysql> UPDATE employee
SET score = 9,
lastmodified = '2016-01-02 03:04:05'
WHERE lastname = 'Doe';

我们得到以下

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

更新的行：

mysql> SELECT * FROM employee WHERE lastname = 'Doe';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email               | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| e02        | John      | Doe      | [email protected] |     9 | 1900-02-03 | 2016-01-02 03:04:05 | 2016-01-01 01:01:04 |
| e03        | Jane      | Doe      | [email protected] |     9 | 1900-05-20 | 2016-01-02 03:04:05 | 2016-01-01 01:01:04 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

没有任何WHERE子句的更新

如果在更新时不提供WHERE子句，它将更新表的所有行。

如果没有WHERE条件，请勿运行UPDATE查询。
您将最终更新您不需要的内容。

在更新之前：

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        |      |   | @example.com    |     6 | 1900-01-01 | 2016-01-01 01:01:01 | 2016-01-01 01:01:01 |
| e02        | John      | Doe      | [email protected]  |     9 | 1900-02-03 | 2016-01-02 03:04:05 | 2016-01-01 01:01:04 |
| e03        | Jane      | Doe      | [email protected]  |     9 | 1900-05-20 | 2016-01-02 03:04:05 | 2016-01-01 01:01:04 |
| e04        | Tin       | Tin      | [email protected]   |     7 | 1900-10-20 | 2016-01-01 01:02:03 | 2016-01-01 01:02:03 |
| e05        | Bob       | Coder    | [email protected] |     7 | 1900-08-20 | 2016-01-02 03:04:05 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

更新查询：

mysql> UPDATE employee
SET score = 7,
lastmodified = '2016-02-04 06:08:10';

Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

更新后：

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        |      |   | @example.com    |     7 | 1900-01-01 | 2016-02-04 06:08:10 | 2016-01-01 01:01:01 |
| e02        | John      | Doe      | [email protected]  |     7 | 1900-02-03 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e03        | Jane      | Doe      | [email protected]  |     7 | 1900-05-20 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e04        | Tin       | Tin      | [email protected]   |     7 | 1900-10-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:03 |
| e05        | Bob       | Coder    | [email protected] |     7 | 1900-08-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)