【Oracle11g SQL详解】UPDATE 和 DELETE 操作的正确使用
UPDATE 和 DELETE 操作的正确使用
UPDATE 和 DELETE 是 Oracle 11g 中用于修改和删除表中数据的重要 SQL 语句。在操作时，需特别注意数据筛选条件的准确性，以避免意外更改或删除数据。本文将详细介绍这两种语句的用法、注意事项及相关案例。
一、UPDATE 的基本用法
UPDATE 用于更新表中的现有数据，可以更新单行或多行记录，需结合 WHERE 子句指定目标记录。如果不加 WHERE，将更新表中的所有记录。
1. 基本语法
UPDATE 表名
SET 列名 = 新值 [, 列名 = 新值 ...]
[WHERE 条件];
2. 示例：更新单列数据
将员工编号为 101 的薪资调整为 8000：
UPDATE employees
SET salary = 8000
WHERE employee_id = 101;
3. 示例：更新多列数据
将编号为 102 的员工姓名和部门更新：
UPDATE employees
SET first_name = 'Alice', department_id = 20
WHERE employee_id = 102;
二、DELETE 的基本用法
DELETE 用于删除表中的指定数据行，需搭配 WHERE 子句指定删除条件。如果省略 WHERE，将删除表中的所有记录，但表结构仍然保留。
1. 基本语法
DELETE FROM 表名
[WHERE 条件];
2. 示例：删除指定行
删除部门编号为 30 的所有员工：
DELETE FROM employees
WHERE department_id = 30;
3. 示例：删除所有数据（慎用）
清空 departments 表中的数据：
DELETE FROM departments;
注意： 若想保留表结构但快速清空数据，可使用 TRUNCATE（比 DELETE 更高效）。
TRUNCATE TABLE departments;
三、UPDATE 和 DELETE 的常见注意事项
-
谨慎使用
WHERE子句WHERE条件不明确会影响意外的数据范围。- 没有
WHERE会影响全表，需格外小心。
错误示例：无
WHERE更新或删除UPDATE employees SET salary = 8000; -- 更新所有员工薪资 DELETE FROM employees; -- 删除所有员工数据 -
确认操作前备份数据
对关键表进行更新或删除前，建议备份数据，尤其在生产环境中。备份示例：创建历史表
CREATE TABLE employees_backup AS SELECT * FROM employees; -
避免触发约束错误
- 更新或删除数据时，需考虑表的外键约束。
- 违反外键关联可能导致操作失败。
解决方法：先删除外键表记录
DELETE FROM order_items WHERE order_id = 101; -- 删除关联表数据 DELETE FROM orders WHERE order_id = 101; -- 删除主表数据 -
批量更新或删除时锁定资源
- 长时间操作会导致表被锁定，其他事务可能阻塞。
- 可使用分批处理或事务控制：
BEGIN DELETE FROM employees WHERE department_id = 40; COMMIT; END;
四、UPDATE 和 DELETE 的高级用法
1. 使用子查询更新数据
通过子查询动态更新列值。
示例：根据另一个表更新薪资
UPDATE employees
SET salary = (SELECT AVG(salary)
FROM employees
WHERE department_id = 20)
WHERE department_id = 10;
2. 使用条件删除
结合复杂条件删除数据。
示例：删除薪资低于平均值的员工
DELETE FROM employees
WHERE salary < (SELECT AVG(salary) FROM employees);
3. 使用 RETURNING 提取受影响数据（仅适用于更新和删除操作）
RETURNING 可返回更新或删除的行数据。
示例：删除数据并返回信息
DELETE FROM employees
WHERE department_id = 50
RETURNING employee_id, first_name, last_name INTO :id_var, :first_name_var, :last_name_var;
五、常见错误及解决方法
-
ORA-01407: cannot update to NULL
原因： 更新操作试图将NOT NULL列赋值为空。
解决方法： 提供合法值，或修改表结构允许空值。 -
ORA-02292: integrity constraint violated - child record found
原因： 删除数据违反外键约束，关联表中存在相关记录。
解决方法： 先删除外键表中关联记录，或设置级联删除。 -
ORA-01779: cannot modify a column which maps to a non key-preserved table
原因： 更新涉及非主键表的视图列。
解决方法： 修改基础表，或确保视图中包含主键列。
六、实践案例
-
批量更新员工薪资
将部门编号为 30 的员工薪资增加 10%。UPDATE employees SET salary = salary * 1.1 WHERE department_id = 30; -
删除无效的客户记录
删除最近一年未活跃的客户。DELETE FROM customers WHERE last_active_date < ADD_MONTHS(SYSDATE, -12); -
避免重复更新：条件过滤
仅更新薪资低于 5000 的员工。UPDATE employees SET salary = 5000 WHERE salary < 5000;
七、小结
UPDATE 和 DELETE 是管理数据表中记录的核心操作，其灵活性和强大功能使其在日常开发中被广泛使用。但在操作时需特别小心，确保筛选条件准确并采取必要的备份与验证措施，以避免因误操作造成数据丢失或不一致。