Oracle 数据库数据操作如何精通 INSERT, UPDATE, DELETE_Oracle

在 oracle 数据库中，对表内数据进行增加、修改和删除操作是通过数据操作语言 (dml - data manipulation language) 来完成的。核心的dml语句包括 insert (插入新数据), update (修改现有数据), 和 delete (删除数据)。掌握这些语句是数据库开发和管理的基础。

思维导图

一、插入数据 (insert)

insert 语句用于向表中添加新的行记录。

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

insert into table_name (column1, column2, column3, ...)
values (value1, value2, value3, ...);

table_name: 要插入数据的表名。
(column1, column2, ...): 可选。指定要插入数据的列名列表。如果省略此列表，则 values 子句中必须提供表中所有列的值，并且顺序必须与表中列的定义顺序完全一致。
values (value1, value2, ...): 提供要插入的具体值。值的顺序和类型必须与列名列表 (或表定义中的列顺序) 匹配。

代码案例：假设有一个 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 table_name (column_a, column_b)
values (value_a, value_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 table_name (column1, column2, ...) values (value1_row1, value2_row1, ...)
  into table_name (column1, column2, ...) values (value1_row2, value2_row2, ...)
  ...
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 target_table (column1, column2, ...)
select source_column1, source_column2, ...
from source_table
where condition;

代码案例：假设有一个 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 table_name
set column1 = value1,
    column2 = value2,
    ...
where condition;

table_name: 要更新的表名。
set column1 = value1, ...: 指定要修改的列及其新值。
where condition: 非常重要！指定哪些行需要被更新。如果省略 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 table_name
where condition;

table_name: 要删除数据的表名。
where condition: 非常重要！指定哪些行需要被删除。如果省略 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; 撤销操作，除非题目特别说明不需要。)

题目：

向 products 表中插入一条新产品记录：product_id=1, product_name=‘super laptop’, category=‘electronics’, price=1200.50, stock_quantity=50。
向 products 表中插入一条新产品记录，只提供 product_id=2, product_name=‘basic mouse’, category=‘accessories’。假设 price 和 stock_quantity 允许为空或有默认值。
创建一个名为 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表已创建)。
将 products 表中 product_id 为 1 的产品的 price 更新为 1150.00，并将 stock_quantity 减少 5。
将 products 表中所有 category 为 ‘accessories’ 的产品的 price 提高10%。
删除 products 表中 stock_quantity 为 0 的所有产品记录。
向 orders 表中插入一条新的订单记录：order_id=1001, product_id=1, customer_name=‘john smith’, order_date=当前系统日期, quantity_ordered=2。
更新 orders 表中 order_id 为 1001 的订单，将其 quantity_ordered 修改为 3。
假设由于产品 product_id=2 已停产，需要删除 orders 表中所有与该产品相关的订单记录。
清空 orders 表中的所有数据，但保留表结构。

答案与解析：

插入新产品到 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)。