MySQL UPDATE 更新

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 UPDATE 命令来操作

使用 UPDATE 子句修改或更新数据表的数据，语法如下：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

参数说明：

• table_name 是你要更新数据的表的名称
• column1, column2, ... 是你要更新的列的名称
• value1, value2, ... 是新的值，用于替换旧的值
• WHERE condition 是一个可选的子句，用于指定更新的行。如果省略 WHERE 子句，将更新表中的所有行

更多说明：

• 你可以同时更新一个或多个字段
• 你可以在 WHERE 子句中指定任何条件
• 你可以在一个单独表中同时更新数据

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的

以下示例在之前创建的RoboMaster数据库下进行操作

当前数据库数据

mysql> SELECT * FROM Username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|    1 | steins_xin | 123456    |
|    2 | xin        | 123456789 |
+------+------------+-----------+
2 rows in set (0.01 sec)

示例：

1. 更新单个列的值：

UPDATE username SET Password = 123 WHERE username = 'steins_xin';

结果：

mysql> UPDATE username SET Password = 123 WHERE username = 'steins_xin';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|    1 | steins_xin | 123       |
|    2 | xin        | 123456789 |
+------+------------+-----------+
2 rows in set (0.00 sec)

1. 更新多个列的值：

UPDATE username 
SET id = 10, Password = '1'
WHERE username = 'steins_xin';

结果：

mysql> UPDATE username
    -> SET id = 10, Password = '1'
    -> WHERE username = 'steins_xin';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|   10 | steins_xin | 1         |
|    2 | xin        | 123456789 |
+------+------------+-----------+
2 rows in set (0.00 sec)

1. 使用表达式更新值：

UPDATE username
SET id = id - 9
WHERE username = 'steins_xin';

结果：

mysql> UPDATE username
    -> SET id = id - 9
    -> WHERE username = 'steins_xin';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|    1 | steins_xin | 1         |
|    2 | xin        | 123456789 |
+------+------------+-----------+
2 rows in set (0.00 sec)

1. 更新符合条件的所有行：

UPDATE username SET Password = 123456;

结果：

mysql> UPDATE username SET Password = 123456;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM username;
+------+------------+----------+
| id   | Username   | Password |
+------+------------+----------+
|    1 | steins_xin | 123456   |
|    2 | xin        | 123456   |
+------+------------+----------+
2 rows in set (0.00 sec)

1. 更新使用子查询的值：

注意：当你尝试在一个UPDATE语句中从一个子查询去更新一个目标表，而这个目标表也被包含在子查询中时。MySQL不允许这种自引用的更新

所以需要从其他的数据表中去查询数据并赋值，如下：

// 当前数据表
mysql> show tables;
+----------------------+
| Tables_in_robomaster |
+----------------------+
| test                 |
| username             |
+----------------------+
2 rows in set (0.00 sec)

// test数据表中数据
 mysql>  SELECT * FROM test;
+------+------------+--------------+
| id   | Username   | Password     |
+------+------------+--------------+
|    0 | steins_xin | 1234aaaaaa56 |
+------+------------+--------------+
1 row in set (0.00 sec)

现在我们将test中的id为0的用户密码赋值给username中的steins_xin用户

UPDATE username
SET Password = (
    SELECT Password 
    FROM test
    WHERE id = 0
)
WHERE username = 'steins_xin';

结果：

mysql> UPDATE username
    -> SET Password = (
    ->     SELECT Password
    ->     FROM test
    ->     WHERE id = 0
    -> )
    -> WHERE username = 'steins_xin';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql>  SELECT * FROM username;
+------+------------+--------------+
| id   | Username   | Password     |
+------+------------+--------------+
|    5 | steins_xin | 1234aaaaaa56 |
|    2 | xin        | 123456       |
+------+------------+--------------+
2 rows in set (0.00 sec)