Oracle 数据库数据操作如何精通 INSERT, UPDATE, DELETE
目录
- 思维导图
- 一、插入数据 (INSERT)
- 1.1 插入单行数据，指定所有列的值语法：
- 1.2 插入单行数据，指定部分列的值
- 1.3 插入多行数据 (INSERT ALL)
- 1.4 从其他表插入数据 (INSERT INTO … SELECT)
- 二、修改数据 (UPDATE)
- 2.1 修改特定行的列值语法：
- 2.2 修改所有行的列值 (谨慎使用)代码案例：
- 2.3 使用子查询更新数据
- 三、删除数据 (DELETE)
- 3.1 删除特定行语法：
- 3.2 删除所有行 (谨慎使用)代码案例：
- 练习题
在 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) VALUE编程S (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)
可php以将一个 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.departXYLuWment_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&npythonbsp;操作在默认情况下（取决于您的客户端工具设置，如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)。
- 清空
orders表数据：
DELETE FROM orders;
- 解析： 由于没有
WHERE子句，此DELETE语句将删除orders表中的所有行。表结构会保留。 - 更高效的替代方案 (不可回滚，但更快，且是DDL操作)：
TRUNCATE TABLE orders;
到此这篇关于Oracle 数据库数据操作：精通 INSERT, UPDATE, DELETE的文章就介绍到这了,更多相关Oracle INSERT, UPDATE, DELETE内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！
加载中,请稍侯......
精彩评论