6. mysql 学习笔记 - 修改数据

千度麒麟
625 阅读5分钟

在 MySQL 中，修改（更新）数据通常使用 UPDATE 语句。通过 UPDATE 语句，可以修改表中已存在的数据。下面是关于 MySQL 中常用的修改数据的方法的汇总，包括各种用法和实际案例。

1. 基础的 UPDATE 语法

UPDATE 语句的基本语法如下：

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
  • table_name：要更新的表的名称。
  • column1, column2, ... ：要更新的列名。
  • value1, value2, ... ：更新后的新值。
  • WHERE condition：指定更新的条件，只有符合条件的记录会被更新。

假设有一个名为 userTable 的表，结构如下：

CREATE TABLE userTable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    sex ENUM('male', 'female', 'other'),
    age INT,
    phone VARCHAR(20),
    email VARCHAR(100),
    birthday DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1.1 修改单条记录

假设我们要修改 id 为 1 的用户的 name 和 phone。

UPDATE userTable SET name = 'John Updated', phone = '1112223333' WHERE id = 1;

  • name = 'John Updated' ：更新 name 字段的值为 'John Updated'。

  • phone = '1112223333' ：更新 phone 字段的值为 '1112223333'。

  • WHERE id = 1：只更新 id 为 1 的记录。

1.2 修改多条记录

如果你要修改多个记录的字段，可以在 WHERE 子句中使用 IN 或其他条件。

UPDATE userTable SET name = 'Updated Name', phone = '5559876543' WHERE id IN (2, 3);

  • id IN (2, 3) ：选择 id 为 2 或 3 的记录。

  • name = 'Updated Name' 和 phone = '5559876543' ：更新这两条记录的 name 和 phone 字段。

1.3 修改所有记录

如果省略 WHERE 子句，UPDATE 会修改所有记录的指定字段。

UPDATE userTable SET phone = '0000000000';

  • phone = '0000000000' ：将 userTable 表中所有记录的 phone 字段更新为 '0000000000'。

  • 注意：这种方式会影响所有记录，请小心使用。

2. UPDATE 与条件组合

通过 UPDATE 语句，你可以使用不同的条件来限制更新的记录。常见的条件包括：

  • 比较运算符（=, >, <, >=, <=, <>）

  • 逻辑运算符（AND, OR, NOT）

  • 模糊匹配（LIKE）

  • 范围查询（BETWEEN）

  • 空值检查（IS NULL, IS NOT NULL）

2.1 使用比较运算符

UPDATE userTable SET phone = '8887776666' WHERE age > 30;

age > 30：将 age 大于 30 的用户的 phone 字段更新为 '8887776666'。

2.2 使用逻辑运算符

UPDATE userTable SET phone = '9998887777' WHERE gender = 'female' AND age < 25;

gender = 'female' AND age < 25：更新 gender 为 'female' 且 age 小于 25 的记录。

2.3 使用 LIKE 模糊匹配

UPDATE userTable SET email = 'updated@example.com' WHERE email LIKE '%example.com';

LIKE '%example.com' ：将所有 email 以 example.com 结尾的用户的 email 字段更新为 'updated@example.com'。

2.4 使用 BETWEEN 查询范围

UPDATE userTable SET phone = '3334445555' WHERE age BETWEEN 20 AND 30;

age BETWEEN 20 AND 30：更新 age 在 20 到 30 之间的用户的 phone 字段。

2.5 使用 IS NULL 和 IS NOT NULL

UPDATE userTable SET email = 'default@example.com' WHERE email IS NULL;

email IS NULL：将所有 email 字段为空的用户的 email 字段更新为 'default@example.com'。

3. 批量更新

你可以通过 CASE 语句来实现条件不同的批量更新。

示例：根据 id 修改不同的字段值

UPDATE userTable
SET phone = CASE 
    WHEN id = 1 THEN '1234567890'
    WHEN id = 2 THEN '9876543210'
    ELSE phone
END
WHERE id IN (1, 2);

这里，id = 1 的用户将更新为 '1234567890'，id = 2 的用户将更新为 '9876543210'，其他用户的 phone 不变。

4. 更新与返回自增字段

如果你想在更新时返回自动生成的字段（如 id），你可以使用 RETURNING（MySQL 8.0 及以上版本）。

UPDATE userTable SET name = 'Alice Updated' WHERE id = 1 RETURNING id, name;
  • 这将更新 id = 1 的记录，并返回更新后的 id 和 name 字段。

注意：如果你使用的是 MySQL 5.x 或更早版本，RETURNING 不被支持，你需要在应用层获取更新后的数据。

5. 使用 JOIN 更新关联表中的数据

在某些情况下，你可能需要基于另一个表的内容更新当前表的记录。这时可以使用 JOIN 语句。

示例：根据另一个表的数据更新当前表
UPDATE userTable u
JOIN userDetails d ON u.id = d.user_id
SET u.phone = d.new_phone
WHERE d.updated = 1;

这里，将更新 userTable 中的 phone 字段，更新的值来自于 userDetails 表，前提是 userDetails 中的 updated 字段为 1。

6. UPDATE 与 LIMIT 限制更新数量

如果你只想更新一定数量的记录，可以使用 LIMIT 关键字。

示例：限制更新最多 3 条记录
UPDATE userTable
SET phone = '6667778888'
WHERE age > 30
LIMIT 3;

这将只更新最多 3 条 age > 30 的记录。

7. UPDATE 与 NOW() 更新时间字段

如果需要将时间字段更新为当前时间，可以使用 NOW() 函数。

示例：将 updated_at 字段更新为当前时间
UPDATE userTable
SET updated_at = NOW()
WHERE id = 1;

这里，updated_at 字段将被更新为当前时间戳。

8. 使用 UPDATE 设置多个表的字段

如果你的数据库设计中涉及到多个表，并且你需要同时更新多个表中的数据，可以使用 JOIN 来连接多个表。

示例：同时更新 userTable 和 userDetails 表中的数据
UPDATE userTable u
JOIN userDetails d ON u.id = d.user_id
SET u.phone = d.new_phone, d.last_updated = NOW()
WHERE u.id = 1;

这会在更新 userTable 表的 phone 字段时，同时更新 userDetails 表的 last_updated 字段。

9. 总结

方法说明示例
UPDATE标准更新方法UPDATE userTable SET name = 'John' WHERE id = 1;
UPDATE 与逻辑运算符（AND/OR）组合多个条件进行更新UPDATE userTable SET name = 'John' WHERE age > 30;
UPDATE 与 LIKE 模糊匹配使用 LIKE 进行模糊匹配进行条件查询UPDATE userTable SET email = 'new@example.com' WHERE email LIKE '%example.com';
使用 CASE 语句批量更新条件不同的情况下进行不同的更新UPDATE userTable SET phone = CASE WHEN id = 1 THEN '123' ELSE '456' END WHERE id IN (1, 2);
UPDATE 与 JOIN 联合更新使用 JOIN 联接表并更新多个表的数据UPDATE userTable u JOIN userDetails d ON u.id = d.user_id SET u.phone = d.new_phone WHERE u.id = 1;
UPDATE 与 LIMIT 限制更新限制更新的记录数量UPDATE userTable SET phone = '123' WHERE age > 25 LIMIT 2;
UPDATE 与 NOW() 更新时间使用 NOW() 函数更新时间字段UPDATE userTable SET updated_at = NOW() WHERE id = 1;
avatar
文章
阅读
粉丝