SQLite高级SQL修改数据操作详解

1. SQLite 修改数据基础概述

SQLite 是一款轻型的数据库，在很多应用场景中被广泛使用。在处理数据时，修改操作是必不可少的部分。SQLite 提供了丰富且灵活的方式来修改数据库中的数据，这些操作不仅能满足基本的数据更新需求，还能应对复杂业务场景下的数据变更。

1.1 核心修改语句 - UPDATE

UPDATE 语句是 SQLite 中用于修改数据的核心语句。其基本语法结构如下：

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE some_column = some_value;

table_name：指定要修改数据的表名。
SET 子句：用于指定要修改的列及其新值。可以同时修改多个列，列与列之间用逗号分隔。
WHERE 子句：这是一个可选子句，用于筛选出符合特定条件的行进行修改。如果不使用 WHERE 子句，那么表中的所有行都会被修改。

例如，假设有一个名为 employees 的表，包含 name、salary 和 department 列。现在要给所有在 “Sales” 部门的员工增加 1000 元工资，可以使用以下语句：

UPDATE employees
SET salary = salary + 1000
WHERE department = 'Sales';

在这个例子中，UPDATE 语句指定了 employees 表，SET 子句将 salary 列的值更新为原来的值加上 1000，WHERE 子句筛选出 department 为 “Sales” 的行。

1.2 基于表达式的修改

在 SET 子句中，可以使用各种表达式来计算新的值。除了简单的算术表达式，还可以使用字符串拼接、日期函数等。

例如，假设有一个 products 表，包含 product_name 和 price 列。现在要对所有产品的价格打 9 折，并在产品名称前加上 “Discounted - ” 前缀，可以这样写：

UPDATE products
SET price = price * 0.9,
    product_name = 'Discounted -'|| product_name;

这里使用了乘法表达式来计算新的价格，使用 || 运算符进行字符串拼接。

2. 复杂条件下的修改

在实际应用中，数据修改的条件往往比较复杂，需要使用多种逻辑运算符和函数来构建精确的筛选条件。

2.1 逻辑运算符的运用

SQLite 支持常见的逻辑运算符，如 AND、OR 和 NOT。这些运算符可以组合使用，以构建复杂的条件。

例如，在 customers 表中，包含 name、age、country 和 purchased_amount 列。现在要给年龄大于 30 岁且来自 “USA” 或者购买金额大于 1000 的客户提供 10% 的折扣，可以使用以下语句：

UPDATE customers
SET purchased_amount = purchased_amount * 0.9
WHERE (age > 30 AND country = 'USA') OR purchased_amount > 1000;

在这个例子中，WHERE 子句使用了 AND 和 OR 逻辑运算符，先通过 AND 筛选出年龄大于 30 岁且来自 “USA” 的客户，再通过 OR 将购买金额大于 1000 的客户也包含进来。

2.2 比较函数

SQLite 提供了一些比较函数，如 LIKE、GLOB 等，用于更灵活的字符串比较。

2.2.1 LIKE 函数

LIKE 函数用于模式匹配，支持通配符 %（匹配任意字符序列，包括空字符序列）和 _（匹配单个字符）。

例如，在 books 表中，包含 title 列。现在要将所有标题中包含 “SQL” 的书籍的价格提高 20%，可以使用以下语句：

UPDATE books
SET price = price * 1.2
WHERE title LIKE '%SQL%';

这里使用 LIKE 函数匹配标题中包含 “SQL” 的书籍。

2.2.2 GLOB 函数

GLOB 函数也用于模式匹配，但它使用的是 Unix 风格的通配符。* 匹配任意字符序列，? 匹配单个字符。

例如，要在 files 表中，将所有文件名以 “report_” 开头且扩展名是 “txt” 的文件的状态标记为 “processed”，可以这样写：

UPDATE files
SET status = 'processed'
WHERE file_name GLOB'report_*.txt';

3. 多表关联修改

在 SQLite 中，有时需要根据其他表的数据来修改当前表的数据，这就涉及到多表关联修改。

3.1 内连接关联修改

内连接是最常见的多表关联方式。假设有两个表，orders 表包含 order_id、customer_id 和 order_amount 列，customers 表包含 customer_id 和 customer_type 列。现在要给所有 “VIP” 客户的订单金额打 8 折，可以使用内连接进行修改：

UPDATE orders
SET order_amount = order_amount * 0.8
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.customer_type = 'VIP';

在这个例子中，通过 JOIN 子句将 orders 表和 customers 表基于 customer_id 列进行内连接，然后在 WHERE 子句中筛选出 customer_type 为 “VIP” 的客户订单进行金额修改。

3.2 外连接关联修改

外连接包括左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN，SQLite 中通过 LEFT JOIN 和 UNION 模拟全连接）。

以左连接为例，假设有 employees 表和 departments 表，employees 表包含 employee_id、department_id 和 salary 列，departments 表包含 department_id 和 department_budget 列。现在要根据部门预算调整员工工资，如果部门预算充足（大于 100000），给该部门员工工资增加 20%。

UPDATE employees
SET salary = salary * 1.2
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
WHERE departments.department_budget > 100000;

这里使用左连接确保即使某个部门没有员工（在 employees 表中没有对应记录），也不会影响其他有员工部门的工资调整。

4. 子查询在修改中的应用

子查询可以嵌套在 UPDATE 语句中，用于获取更复杂的条件或计算新的值。

4.1 子查询作为条件

假设有一个 students 表，包含 student_id、name 和 score 列。现在要给成绩高于平均成绩的学生增加 5 分，可以使用子查询来获取平均成绩，并作为条件进行修改：

UPDATE students
SET score = score + 5
WHERE score > (SELECT AVG(score) FROM students);

在这个例子中，子查询 (SELECT AVG(score) FROM students) 计算出所有学生的平均成绩，主查询的 WHERE 子句使用这个平均成绩作为条件，筛选出成绩高于平均成绩的学生进行成绩修改。

4.2 子查询用于计算新值

假设有 products 表和 product_categories 表，products 表包含 product_id、category_id 和 price 列，product_categories 表包含 category_id 和 category_markup 列。现在要根据产品所属类别的加价率来调整产品价格，可以这样写：

UPDATE products
SET price = price * (SELECT category_markup FROM product_categories WHERE product_categories.category_id = products.category_id);

这里子查询 (SELECT category_markup FROM product_categories WHERE product_categories.category_id = products.category_id) 根据产品的类别 ID 获取对应的加价率，主查询使用这个加价率来计算并更新产品价格。

5. 事务与数据修改

在 SQLite 中，事务对于确保数据修改的一致性和完整性非常重要。一个事务可以包含多个 UPDATE 操作，要么全部成功执行，要么全部回滚。

5.1 事务的开始与提交

要开始一个事务，可以使用 BEGIN 语句，事务结束时使用 COMMIT 语句提交事务。例如，假设有一个银行转账操作，从账户 A 向账户 B 转账 100 元，涉及到两个 UPDATE 操作：

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';
COMMIT;

在这个事务中，首先从账户 A 减去 100 元，然后给账户 B 增加 100 元。如果这两个操作都成功执行，最后通过 COMMIT 提交事务，数据修改永久生效。

5.2 事务的回滚

如果在事务执行过程中出现错误，可以使用 ROLLBACK 语句回滚事务，撤销之前的所有数据修改。例如，假设在上述转账事务中，给账户 B 增加金额时出现错误：

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A';
-- 假设这里出现错误，比如账户 B 不存在
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';
ROLLBACK;

在这种情况下，通过 ROLLBACK 语句，之前对账户 A 的余额减少操作也会被撤销，保证了数据的一致性，不会出现账户 A 减少金额而账户 B 未增加金额的情况。

6. 数据修改的性能优化

在进行大量数据修改时，性能优化至关重要。以下是一些优化数据修改操作的方法。

6.1 合理使用索引

索引可以显著提高 UPDATE 操作中 WHERE 子句的筛选效率。例如，如果经常根据 customer_id 来修改 customers 表中的数据，可以在 customer_id 列上创建索引：

CREATE INDEX idx_customers_customer_id ON customers(customer_id);

这样在执行 UPDATE 语句时，如：

UPDATE customers
SET email = 'new_email@example.com'
WHERE customer_id = 123;

SQLite 可以更快地定位到要修改的行，从而提高修改操作的性能。

6.2 批量修改

避免在循环中执行单个 UPDATE 操作，尽量将多个修改合并为一个批量操作。例如，假设要给多个员工增加工资，如果一个个执行 UPDATE 操作：

UPDATE employees SET salary = salary + 1000 WHERE employee_id = 1;
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 2;
-- 以此类推

这种方式性能较低。可以将所有要修改的员工 ID 收集起来，然后一次性执行修改：

UPDATE employees
SET salary = salary + 1000
WHERE employee_id IN (1, 2, 3, 4, 5);

这样可以减少数据库的 I/O 操作，提高性能。

6.3 减少事务中的操作

尽量将事务中的 UPDATE 操作数量控制在合理范围内。如果事务中包含大量复杂的 UPDATE 操作，不仅会增加事务执行时间，还可能导致锁争用等问题，影响数据库的并发性能。例如，可以将一些不相关的 UPDATE 操作放在不同的事务中执行。

7. 数据修改的注意事项

在进行 SQLite 数据修改操作时，有一些需要特别注意的地方。

7.1 备份数据

在进行大规模或复杂的数据修改之前，一定要备份数据库。因为一旦修改操作出现错误，可能导致数据丢失或损坏。可以使用 SQLite 的备份命令或工具，如 sqlite3 命令行工具的 .backup 命令：

sqlite3 your_database.db ".backup backup_database.db"

这样就可以将当前数据库备份到 backup_database.db 文件中。

7.2 谨慎使用无 WHERE 子句的 UPDATE

如前文所述，不使用 WHERE 子句的 UPDATE 语句会修改表中的所有行。在执行这样的操作时要格外小心，确保这是你真正想要的结果。例如，不小心执行了以下语句：

UPDATE employees
SET salary = 0;

这将把所有员工的工资都设置为 0，可能会造成严重的数据错误。

7.3 数据类型兼容性

在使用 UPDATE 语句修改数据时，要确保新值的数据类型与列的数据类型兼容。例如，如果一个列定义为 INTEGER 类型，不能试图将一个字符串值赋给它，否则会导致错误。例如：

-- 假设 age 列是 INTEGER 类型
UPDATE students
SET age = 'twenty'
WHERE student_id = 1;

这样的操作会失败，因为字符串 “twenty” 无法隐式转换为 INTEGER 类型。

通过深入理解 SQLite 中数据修改的各种方式、技巧以及注意事项，可以更高效、准确地对数据库中的数据进行维护和更新，满足不同应用场景下的数据处理需求。无论是简单的单表修改，还是复杂的多表关联、基于子查询的修改，都能运用自如，确保数据的一致性和完整性。