从多角度详解 MySQL 插入或更新

一、MySQL 更新语句

MySQL 更新语句用于修改表中的数据，可以根据需要修改一行或多行数据，具体语法如下：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

其中 table_name 表示要更新数据的表名称，field1、field2 表示要更新的字段名称，new-value1、new-value2 表示要更新的数据， WHERE Clause 则表示筛选条件，限定更新哪些数据行，如果不加条件，则会更新表中的所有数据。

通过以下示例可以更好地理解 MySQL 更新语句：

UPDATE users SET age=25 WHERE name='Tom';
UPDATE users SET age=age+1 WHERE gender='male';

第一个示例中将名字为 Tom 的用户的年龄修改为 25，第二个示例中将所有男性用户的年龄加 1。

二、MySQL 连表更新

在实际开发中，可能需要更新多个表中的数据，这时可以使用 MySQL 连表更新语句，例如：

UPDATE table1, table2 
SET table1.column = new_value1, 
    table2.column = new_value2
WHERE table1.join_column = table2.join_column;

其中，table1 、table2 表示需要更新的两个表，join_column 表示它们的连接列，new_value1、new_value2 表示新的值。

以下示例演示了如何使用 MySQL 连表更新语句：

UPDATE users, orders
SET users.age = 30, orders.status = 2
WHERE users.id = orders.user_id AND users.name='Tom';

上述示例中，当用户 Tom 的订单发生变化时，可以通过更新 users 表中的数据来反映订单状态的变化。

三、MySQL 更新数据

MySQL 更新数据的方式有两种：

1. 直接更新，通过 SET 关键字设置新值。

2. 直接复制，通过 SELECT 子句设置新值。

具体语法如下所示：

UPDATE table_name
SET column_name1 = new_value1,
    column_name2 = (SELECT expression FROM table_name WHERE condition),
[WHERE];

以下示例演示了如何使用 MySQL 更新数据：

UPDATE accounts SET balance = 1000 WHERE account_number = 123;
UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 2) WHERE department_id = 2;

第一个示例中将账户号为 123 的账户的余额更新为 1000，第二个示例中将部门 2 中员工的薪水更新为该部门的平均工资。

四、MySQL 联表更新

MySQL 联表更新语句是一种特殊的更新语句，它允许我们更新与某个记录有关联的所有表，具体语法如下所示：

UPDATE table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name
SET table_name1.column_name = new_value1,
table_name2.column_name = new_value2
[WHERE];

以下示例演示了如何使用 MySQL 联表更新语句：

UPDATE customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
SET customers.last_order = orders.order_date
WHERE orders.order_status = 'shipped';

上述示例中，当订单发生变化时，将顾客的最后一张订单更新为新订单的订单日期。

五、MySQL 版本更新

MySQL 版本更新可以用于在更新数据时记录修改时间戳或更新版本，具体语法如下所示：

UPDATE table_name SET column_name = new_value, 
version = version + 1
WHERE primary_key = key_value AND version = current_version;

其中，primary_key 表示主键，key_value 表示记录唯一标识，version 表示版本号，new_value 表示新的数据。

以下示例演示了如何使用 MySQL 版本更新：

UPDATE accounts SET balance = balance - 100, version = version + 1 WHERE account_number = 123 AND version = 3;

上述示例中，将账户号为 123 的账户余额减去 100，同时将版本号加 1。

六、MySQL 更新视图

MySQL 视图是一种虚拟表，它是由一个或多个基表的行或列组合而成的，我们可以使用 MySQL 更新视图来修改视图中的数据，具体语法如下所示：

UPDATE view_name SET column_name = new_value WHERE condition;

其中，view_name 表示要更新的视图名，column_name 表示要更新的列名，new_value 表示新值，condition 是更新的条件。

以下示例演示了如何使用 MySQL 更新视图：

CREATE VIEW v_customers_orders AS 
SELECT customers.customer_id, 
orders.order_id, 
orders.order_total 
FROM customers 
JOIN orders ON customers.customer_id = orders.customer_id;
UPDATE v_customers_orders SET order_total = 500 WHERE customer_id = 100;

上述示例中，先创建了一个名为 v_customers_orders 的视图，然后更新了该视图中客户 100 的订单总额为 500。

七、MySQL 批量更新

MySQL 批量更新语句可以同时更新多个记录，从而提高更新效率，具体语法如下所示：

UPDATE table_name 
SET column_name1 = CASE 
    WHEN condition1 THEN new_value1_1 
    WHEN condition2 THEN new_value1_2 
    ... 
    ELSE column_name1 
END,
column_name2 = CASE 
    WHEN condition1 THEN new_value2_1 
    WHEN condition2 THEN new_value2_2 
    ... 
    ELSE column_name2 
END
[WHERE condition];

其中，table_name 表示要更新的表名，column_name1、column_name2 表示要更新的列名，new_value1_1、new_value1_2 表示新值， condition1、condition2 是条件，ELSE 表示默认情况下保持原来的值。

以下示例演示了如何使用 MySQL 批量更新：

UPDATE employees SET salary = CASE 
    WHEN department_id = 1 THEN salary * 1.05 
    WHEN department_id = 2 THEN salary * 1.08 
    ELSE salary 
END, 
commission_pct = CASE 
    WHEN department_id = 1 THEN commission_pct * 1.2 
    WHEN department_id = 2 THEN commission_pct * 1.5 
    ELSE commission_pct 
END 
WHERE hire_date < '2000-01-01';

上述示例中，将 2000 年以前入职的员工的薪水和佣金按部门分别进行不同的加成。