更新数据

本文档介绍如何使用 SQL 语句在 MatrixOne 中更新数据。

开始前准备

已完成单机部署 MatrixOne。

更新数据 SQL 语句

你可以通过两种方式更新数据：UPDATE 和 INSERT ON DUPLICATE KEY UPDATE。

二者的区别是：

-UPDATE：

  • UPDATE 语句用于直接更新已存在的行的数据。
  • 需要明确指定要更新的表、列和对应的新值，以及更新的条件。
  • 如果满足更新条件，将会修改已存在的行的数据。
  • 如果更新的条件不满足，将不会有任何修改操作。

INSERT ON DUPLICATE KEY UPDATE：

  • INSERT ON DUPLICATE KEY UPDATE 是 INSERT 语句的一个扩展，用于在插入新行时处理重复键的情况。
  • 当插入的数据中存在重复键时，即某一列或组合列的值与已存在的行的键值相同，将执行 UPDATE 操作而不是插入新行。
  • 可以一次性指定要插入的数据以及在重复键冲突时要进行的更新操作。
  • 对于重复键的行，将根据指定的更新操作更新相应的列。

关键区别：

  • UPDATE 语句用于直接更新已存在的行，而 INSERT ON DUPLICATE KEY UPDATE 语句用于在插入数据时处理重复键的情况。
  • UPDATE 语句需要明确指定要更新的表、列和对应的新值，以及更新的条件。而 INSERT ON DUPLICATE KEY UPDATE 语句通过一次性指定要插入的数据和更新操作，对于重复键的行执行更新操作。

UPDATE

  1. UPDATE table_reference
  2.     SET assignment_list
  3.     [WHERE where_condition]
  4.     [ORDER BY ...]
  5.     [LIMIT row_count]

  1. UPDATE table_reference：指定要更新数据的目标表。table_reference 可以是单个表或多个表的联接。

  2. SET assignment_list：指定要更新的列和值。assignment_list 是一个由列名和相应的值组成的列表，用逗号分隔。每个列名后面使用等号（=）将其与要更新的新值相关联。

  3. [WHERE where_condition]（可选）：WHERE 子句用于指定更新数据的条件。只有满足指定条件的行才会被更新。where_condition 是一个逻辑表达式，可以使用各种比较运算符和逻辑运算符来定义条件。

  4. [ORDER BY ...]（可选）：ORDER BY 子句用于按指定的列对要更新的行进行排序。可以使用一个或多个列，并可以指定升序（ASC）或降序（DESC）排序。排序将影响更新的行的顺序。

  5. [LIMIT row_count]（可选）：LIMIT 子句用于限制要更新的行数。它指定要更新的最大行数（row_count）。如果未指定 LIMIT 子句，则将更新满足 WHERE 条件的所有行。

更新数据的过程中，指定要更新的表、要更新的列和值、更新的条件以及排序和限制，以便根据需求进行灵活的数据更新。

INSERT ON DUPLICATE KEY UPDATE

  1. > INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  2.   [ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...];
  1. INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  2. INSERT INTO 语句用于向表中插入新的行。
  3. [db.]（可选）指定表所在的数据库名称。如果未提供数据库名称，则默认为当前数据库。
  4. table 是要插入数据的目标表的名称。
  5. [(c1, c2, c3)]（可选）指定要插入的列，用括号括起来，并使用逗号分隔列名。如果未指定列名，则假定将插入表中所有可用的列。

  6. VALUES 子句指定要插入的值。每个值与对应的列一一对应，用逗号分隔，放在括号中。可以插入多行数据，每一行用逗号分隔。

  7. [ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...]

  8. ON DUPLICATE KEY UPDATE 子句用于在插入数据时处理重复键（Duplicate Key）的情况。

  9. 当插入的数据中存在重复键时，即某一列或组合列的值与已存在的行的键值相同，将执行 UPDATE 操作而不是插入新行。
  10. column1, column2, column3 等表示要更新的列名，value1, value2, value3 等表示要更新的对应值。

这个语法结构允许将一行或多行数据插入到指定的表中。如果出现重复键的情况，即已存在相同键值的行，可以选择执行 UPDATE 操作来更新该行的数据。

请注意，在使用 INSERT INTO 语句时，根据表的结构和需求，提供相应的列名和对应的值。如果存在重复键的情况，并且使用了 ON DUPLICATE KEY UPDATE 子句，指定要更新的列和对应的值。

示例

  • 示例 1：UPDATE
  1. -- 创建表
  2. CREATE TABLE employees (
  3.   id INT PRIMARY KEY,
  4.   name VARCHAR(50),
  5.   department VARCHAR(50),
  6.   salary DECIMAL(10, 2)
  7. );
  9. -- 插入数据
  10. INSERT INTO employees (id, name, department, salary)
  11. VALUES (1, 'John Doe', 'HR', 5000),
  12.        (2, 'Jane Smith', 'Marketing', 6000),
  13.        (3, 'Mike Johnson', 'IT', 7000),
  14.        (4, 'Emily Brown', 'Finance', 8000),
  15.        (5, 'David Wilson', 'HR', 5500);
  17. -- 查看初始数据
  18. mysql> SELECT * FROM employees;
  19. +------+--------------+------------+---------+
  20. | id   | name         | department | salary  |
  21. +------+--------------+------------+---------+
  22. |    1 | John Doe     | HR         | 5000.00 |
  23. |    2 | Jane Smith   | Marketing  | 6000.00 |
  24. |    3 | Mike Johnson | IT         | 7000.00 |
  25. |    4 | Emily Brown  | Finance    | 8000.00 |
  26. |    5 | David Wilson | HR         | 5500.00 |
  27. +------+--------------+------------+---------+
  28. 5 rows in set (0.01 sec)
  30. -- 更新数据，使用 UPDATE 语句更新了部门为'HR'的前两个员工的薪资，薪资增加了 10%。WHERE 子句指定了更新数据的条件，只有满足部门为'HR'的行才会被更新。ORDER BY 子句按照 id 列进行升序排序，LIMIT 子句限制只更新两行数据。
  31. mysql> UPDATE employees
  32.        SET salary = salary * 1.1
  33.        WHERE department = 'HR'
  34.        ORDER BY id
  35.        LIMIT 2;
  36. Query OK, 2 rows affected (0.02 sec)
  38. -- 查看更新后的数据
  39. mysql> SELECT * FROM employees;
  40. +------+--------------+------------+---------+
  41. | id   | name         | department | salary  |
  42. +------+--------------+------------+---------+
  43. |    2 | Jane Smith   | Marketing  | 6000.00 |
  44. |    3 | Mike Johnson | IT         | 7000.00 |
  45. |    4 | Emily Brown  | Finance    | 8000.00 |
  46. |    1 | John Doe     | HR         | 5500.00 |
  47. |    5 | David Wilson | HR         | 6050.00 |
  48. +------+--------------+------------+---------+
  49. 5 rows in set (0.00 sec)
  • 示例 2：INSERT ... ON DUPLICATE KEY UPDATE
  1. -- 创建表
  2. CREATE TABLE students (
  3.   id INT PRIMARY KEY,
  4.   name VARCHAR(50),
  5.   age INT,
  6.   grade VARCHAR(10)
  7. );
  9. -- 插入数据
  10. INSERT INTO students (id, name, age, grade)
  11. VALUES (1, 'John Doe', 18, 'A'),
  12.        (2, 'Jane Smith', 17, 'B'),
  13.        (3, 'Mike Johnson', 19, 'A'),
  14.        (4, 'Emily Brown', 18, 'A');
  16. -- 查看初始数据
  17. mysql> SELECT * FROM students;
  18. +------+--------------+------+-------+
  19. | id   | name         | age  | grade |
  20. +------+--------------+------+-------+
  21. |    1 | John Doe     |   18 | A     |
  22. |    2 | Jane Smith   |   17 | B     |
  23. |    3 | Mike Johnson |   19 | A     |
  24. |    4 | Emily Brown  |   18 | A     |
  25. +------+--------------+------+-------+
  26. 4 rows in set (0.01 sec)
  28. -- 更新数据
  29. mysql> INSERT INTO students (id, name, age, grade)
  30.        VALUES (2, 'Jane Smith', 18, 'A')
  31.        ON DUPLICATE KEY UPDATE age = VALUES(age), grade = VALUES(grade);
  32. Query OK, 1 row affected (0.01 sec)
  34. -- 查看更新后的数据
  35. mysql> SELECT * FROM students;
  36. +------+--------------+------+-------+
  37. | id   | name         | age  | grade |
  38. +------+--------------+------+-------+
  39. |    1 | John Doe     |   18 | A     |
  40. |    3 | Mike Johnson |   19 | A     |
  41. |    4 | Emily Brown  |   18 | A     |
  42. |    2 | Jane Smith   |   18 | A     |
  43. +------+--------------+------+-------+
  44. 4 rows in set (0.00 sec)

在上述示例中，首先创建了一个名为 students 的表，包含 id、name、age 和 grade 四个列。然后，使用 INSERT INTO 语句插入了四行学生数据。

接下来，使用 SELECT 语句查看初始数据。然后，使用 INSERT INTO 语句插入了一行学生数据，其中 id 为 2 的学生已经存在，这会导致重复键的情况。在这种情况下，使用 ON DUPLICATE KEY UPDATE 子句来更新该行的数据。通过 VALUES 函数，我们指定要更新的列和对应的值。

最后，使用 SELECT 语句再次查看更新后的数据，可以看到 id 为 2 的学生的年龄和成绩已经被更新。