【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 的常见注意事项

1. 谨慎使用 WHERE 子句

   • WHERE 条件不明确会影响意外的数据范围。
   • 没有 WHERE 会影响全表，需格外小心。

   错误示例：无 WHERE 更新或删除

   UPDATE employees SET salary = 8000; -- 更新所有员工薪资
   DELETE FROM employees; -- 删除所有员工数据
   

2. 确认操作前备份数据
   对关键表进行更新或删除前，建议备份数据，尤其在生产环境中。

   备份示例：创建历史表

   CREATE TABLE employees_backup AS
   SELECT * FROM employees;
   

3. 避免触发约束错误

   • 更新或删除数据时，需考虑表的外键约束。
   • 违反外键关联可能导致操作失败。

   解决方法：先删除外键表记录

   DELETE FROM order_items WHERE order_id = 101; -- 删除关联表数据
   DELETE FROM orders WHERE order_id = 101; -- 删除主表数据
   

4. 批量更新或删除时锁定资源

   • 长时间操作会导致表被锁定，其他事务可能阻塞。

   • 可使用分批处理或事务控制：

     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;

---

五、常见错误及解决方法

1. ORA-01407: cannot update to NULL
   原因： 更新操作试图将 NOT NULL 列赋值为空。
   解决方法： 提供合法值，或修改表结构允许空值。

2. ORA-02292: integrity constraint violated - child record found
   原因： 删除数据违反外键约束，关联表中存在相关记录。
   解决方法： 先删除外键表中关联记录，或设置级联删除。

3. ORA-01779: cannot modify a column which maps to a non key-preserved table
   原因： 更新涉及非主键表的视图列。
   解决方法： 修改基础表，或确保视图中包含主键列。

---

六、实践案例

1. 批量更新员工薪资
   将部门编号为 30 的员工薪资增加 10%。

   UPDATE employees
   SET salary = salary * 1.1
   WHERE department_id = 30;
   

2. 删除无效的客户记录
   删除最近一年未活跃的客户。

   DELETE FROM customers
   WHERE last_active_date < ADD_MONTHS(SYSDATE, -12);
   

3. 避免重复更新：条件过滤
   仅更新薪资低于 5000 的员工。

   UPDATE employees
   SET salary = 5000
   WHERE salary < 5000;
   

---

七、小结

UPDATE 和 DELETE 是管理数据表中记录的核心操作，其灵活性和强大功能使其在日常开发中被广泛使用。但在操作时需特别小心，确保筛选条件准确并采取必要的备份与验证措施，以避免因误操作造成数据丢失或不一致。