Oracle数据库数据操作：玩转插入、更新与删除

文章标题：

Oracle数据库的数据操作：玩转数据的插入、更新与删除

文章内容：

作者：IvanCodes
日期：2025年6月18日

专栏：Oracle教程

在Oracle数据库里，对表内数据开展增加、修改和删除这类操作是通过数据操作语言（也就是DML – Data Manipulation Language）来实现的。核心的DML语句包含 INSERT（用于插入新的数据）、UPDATE（用来修改已有的数据）以及 DELETE（用于删除数据）。熟练掌握这些语句是进行数据库开发和管理的基础。

思维导图

一、插入数据（INSERT）

INSERT语句的作用是向表中添加新的行记录。

1.1 插入单行数据，明确所有列的值
语法：

INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

• 表名：是要插入数据的目标表的名称。
• (列1, 列2, ...)：这是可选部分。它用来指定要插入数据的列名称列表。要是省略了这部分，那么VALUES子句中就必须提供表中所有列的值，并且顺序得和表中列的定义顺序完全一致。
• VALUES (值1, 值2, ...)：用来提供具体要插入的数据值。值的顺序以及类型要和列名列表（或者表定义里的列顺序）相匹配。

代码示例：
假设有一个employees表：

CREATE TABLE employees (
  employee_id NUMBER(6) PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25) NOT NULL,
  email VARCHAR2(25) NOT NULL UNIQUE,
  hire_date DATE DEFAULT SYSDATE,
  salary NUMBER(8,2)
);

插入一条完整的员工记录：

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES (101, 'John', 'Doe', 'john.doe@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 60000);

要是省略列名列表（不建议这么做，除非非常清楚表结构且列顺序不会改变）：

INSERT INTO employees
VALUES (102, 'Jane', 'Smith', 'jane.smith@example.com', TO_DATE('2023-02-20', 'YYYY-MM-DD'), 75000);

1.2 插入单行数据，指定部分列的值
要是某些列允许为NULL或者有DEFAULT值，那么就可以只插入部分列的数据。

语法：

INSERT INTO 表名 (列A, 列B)
VALUES (值A, 值B);

代码示例：
插入一个员工，只提供必要信息，hire_date使用默认值，salary暂时不指定（会为NULL）：

INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (103, 'Peter', 'Jones', 'peter.jones@example.com');

1.3 插入多行数据（INSERT ALL）
Oracle提供了INSERT ALL语句，能够一次性向一个或多个表中插入多行数据。

语法（插入到同一张表的多行）：

INSERT ALL
  INTO 表名 (列1, 列2, ...) VALUES (值1_行1, 值2_行1, ...)
  INTO 表名 (列1, 列2, ...) VALUES (值1_行2, 值2_行2, ...)
  ...
SELECT * FROM dual; -- dual是Oracle的虚拟表，这里用于触发INSERT ALL

代码示例：

INSERT ALL
  INTO employees (employee_id, first_name, last_name, email, salary) VALUES (104, 'Alice', 'Wonder', 'alice.w@example.com', 55000)
  INTO employees (employee_id, first_name, last_name, email, salary) VALUES (105, 'Bob', 'Marley', 'bob.m@example.com', 62000)
SELECT * FROM dual;

1.4 从其他表插入数据（INSERT INTO … SELECT）
可以把一个SELECT语句的查询结果直接插入到另一个表中。

语法：

INSERT INTO 目标表 (列1, 列2, ...)
SELECT 源表列1, 源表列2, ...
FROM 源表
WHERE 条件;

代码示例：
假设有一个employees_archive表，结构和employees类似。把employees表中薪水低于50000的员工备份到employees_archive：

INSERT INTO employees_archive (employee_id, first_name, last_name, email, hire_date, salary)
SELECT employee_id, first_name, last_name, email, hire_date, salary
FROM employees
WHERE salary < 50000;

二、修改数据（UPDATE）

UPDATE语句的作用是修改表中已存在行的列值。

2.1 修改特定行的列值
语法：

UPDATE 表名
SET 列1 = 值1,
    列2 = 值2,
    ...
WHERE 条件;

• 表名：是要更新的表的名称。
• SET 列1 = 值1, ...：用来指定要修改的列以及对应的新值。
• WHERE 条件：非常关键！它用来指定哪些行需要被更新。要是省略了WHERE子句，那么表中所有行的指定列都会被更新，这通常是很危险的操作。

代码示例：
把employee_id为101的员工薪水增加10%：

UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = 101;

修改employee_id为103的员工的first_name和salary：

UPDATE employees
SET first_name = 'Pete',
    salary = 52000
WHERE employee_id = 103;

2.2 修改所有行的列值（谨慎使用）
代码示例：
给所有员工的薪水普调增加500（假设所有员工都适用）：

UPDATE employees
SET salary = salary + 500;
-- 再次强调：没有WHERE子句会更新所有行，操作前务必确认！

2.3 使用子查询更新数据
SET子句中的值或者WHERE子句中的条件可以来自子查询。

代码示例：
假设有一个departments_avg_salary表（department_id, avg_sal）。把employees表中每个员工的薪水更新为其所在部门的平均薪水（仅为示例，实际逻辑可能更复杂）。

-- 仅为语法示例，实际逻辑可能需要更复杂的关联更新
UPDATE employees e
SET e.salary = (SELECT d.avg_sal
                FROM departments_avg_salary d
                WHERE e.department_id = d.department_id) -- 假设employees表有department_id
WHERE EXISTS (SELECT 1
              FROM departments_avg_salary d
              WHERE e.department_id = d.department_id);

更常见的做法是使用Oracle的MERGE语句进行复杂的关联更新。

三、删除数据（DELETE）

DELETE语句的作用是从表中删除一行或多行记录。

3.1 删除特定行
语法：

DELETE FROM 表名
WHERE 条件;

• 表名：是要删除数据的表的名称。
• WHERE 条件：非常关键！它用来指定哪些行需要被删除。要是省略了WHERE子句，那么表中所有行都会被删除（效果类似TRUNCATE TABLE，但DELETE可以回滚，TRUNCATE通常不行且更快，不过TRUNCATE不是本节重点）。

代码示例：
删除employee_id为105的员工记录：

DELETE FROM employees
WHERE employee_id = 105;

删除所有薪水低于40000的员工：

DELETE FROM employees
WHERE salary < 40000;

3.2 删除所有行（谨慎使用）
代码示例：

DELETE FROM employees;
-- 这会删除employees表中的所有数据，但表结构依然存在。
-- 如果要快速清空表并且不需要DML的回滚能力，TRUNCATE TABLE employees; 效率更高。

重要提示： 所有的INSERT、UPDATE、DELETE操作在默认情况下（取决于您的客户端工具设置，如SQL*Plus或SQL Developer）不是自动提交的。您需要显式使用COMMIT命令来永久保存更改，或者使用ROLLBACK命令来撤销未提交的更改。如果不提交就关闭会话，未提交的更改通常会自动回滚。

总结： INSERT、UPDATE、DELETE是日常数据库操作的核心。一定要理解它们的语法，特别是WHERE子句在UPDATE和DELETE中的重要性，以避免意外修改或删除数据。

---

练习题

背景表结构：
假设我们有以下两个表：

create table products (
product_id NUMBER PRIMARY KEY, 
product_name VARCHAR2(100), 
category VARCHAR2(50), 
price NUMBER(8,2), 
stock_quantity NUMBER);
create table orders (
order_id NUMBER PRIMARY KEY, 
product_id NUMBER, 
customer_name VARCHAR2(100), 
order_date DATE, 
quantity_ordered NUMBER, 
FOREIGN KEY (product_id) REFERENCES products(product_id));

请为以下每个场景编写相应的SQL DML语句。 (提交您的DML语句后，记得使用COMMIT;保存更改，或ROLLBACK;撤销操作，除非题目特别说明不需要。)

题目：

1. 向products表中插入一条新产品记录：product_id=1, product_name=‘Super Laptop’, category=‘Electronics’, price=1200.50, stock_quantity=50。
2. 向products表中插入一条新产品记录，只提供product_id=2, product_name=‘Basic Mouse’, category=‘Accessories’。假设price和stock_quantity允许为空或有默认值。
3. 创建一个名为special_offers的新表，其结构包含product_id, product_name, offer_price。然后从products表中选择所有category为‘Electronics’且price大于1000的产品，将其product_id, product_name以及price * 0.9 (作为offer_price) 插入到special_offers表中。(只需写INSERT INTO…SELECT部分，假设special_offers表已创建)。
4. 将products表中product_id为1的产品的price更新为1150.00，并将stock_quantity减少5。
5. 将products表中所有category为‘Accessories’的产品的price提高10%。
6. 删除products表中stock_quantity为0的所有产品记录。
7. 向orders表中插入一条新的订单记录：order_id=1001, product_id=1, customer_name=‘John Smith’, order_date=当前系统日期, quantity_ordered=2。
8. 更新orders表中order_id为1001的订单，将其quantity_ordered修改为3。
9. 假设由于产品product_id=2已停产，需要删除orders表中所有与该产品相关的订单记录。
10. 清空orders表中的所有数据，但保留表结构。

答案与解析：

1. 向products表插入新产品：

INSERT INTO products (product_id, product_name, category, price, stock_quantity)
VALUES (1, 'Super Laptop', 'Electronics', 1200.50, 50);

• 解析： 使用标准的INSERT INTO ... VALUES语句，明确指定了所有列名和对应的值。

• 向products表插入部分列数据：

INSERT INTO products (product_id, product_name, category)
VALUES (2, 'Basic Mouse', 'Accessories');

• 解析： 只为指定的列提供了值。未指定的price和stock_quantity列将根据表定义获得默认值或NULL。

• 从products表插入数据到special_offers表：
  (假设special_offers表已创建，结构：product_id NUMBER, product_name VARCHAR2(100), offer_price NUMBER(8,2))

INSERT INTO special_offers (product_id, product_name, offer_price)
SELECT product_id, product_name, price * 0.9
FROM products
WHERE category = 'Electronics' AND price > 1000;

• 解析： 使用INSERT INTO ... SELECT结构。SELECT语句从products表筛选数据，并计算offer_price。查询结果的列与special_offers表的列对应插入。

• 更新特定产品信息：

UPDATE products
SET price = 1150.00,
    stock_quantity = stock_quantity - 5
WHERE product_id = 1;

• 解析： 使用UPDATE语句，SET子句指定了要修改的多个列及其新值。WHERE子句精确定位到product_id为1的记录。

• 批量更新产品价格：

UPDATE products
SET price = price * 1.10
WHERE category = 'Accessories';

• 解析： WHERE子句筛选出所有类别为‘Accessories’的产品，然后它们的price被更新为原价格的1.1倍。

• 删除库存为0的产品：

DELETE FROM products
WHERE stock_quantity = 0;

• 解析： DELETE语句通过WHERE子句找到所有stock_quantity为0的记录并删除它们。

• 向orders表插入新订单：

INSERT INTO orders (order_id, product_id, customer_name, order_date, quantity_ordered)
VALUES (1001, 1, 'John Smith', SYSDATE, 2);

• 解析： 插入新的订单记录。SYSDATE是Oracle获取当前系统日期和时间的函数。

• 更新特定订单数量：

UPDATE orders
SET quantity_ordered = 3
WHERE order_id = 1001;

• 解析： UPDATE语句根据order_id定位到特定订单，并修改其quantity_ordered。

• 删除特定产品的所有订单：

DELETE FROM orders
WHERE product_id = 2;

• 解析： DELETE语句删除orders表中所有product_id为2的订单。由于orders.product_id有外键约束引用products.product_id，如果products表中product_id=2的记录也需要删除，通常需要先删除orders中的相关记录 (或者外键设置了级联删除ON DELETE CASCADE)。

• 清空orders表数据：

DELETE FROM orders;

• 解析： 由于没有WHERE子句，此DELETE语句将删除orders表中的所有行。表结构会保留。
• 更高效的替代方案 (不可回滚，但更快，且是DDL操作)： TRUNCATE TABLE orders;