在 Oracle 數(shù)據(jù)庫中，對表內(nèi)數(shù)據(jù)進(jìn)行增加、修改和刪除操作是通過數(shù)據(jù)操作語言 (DML - Data Manipulation Language) 來完成的。核心的DML語句包括 INSERT (插入新數(shù)據(jù)), UPDATE (修改現(xiàn)有數(shù)據(jù)), 和 DELETE (刪除數(shù)據(jù))。掌握這些語句是數(shù)據(jù)庫開發(fā)和管理的基礎(chǔ)。

思維導(dǎo)圖

一、插入數(shù)據(jù) (INSERT)

INSERT 語句用于向表中添加新的行記錄。

1.1 插入單行數(shù)據(jù)，指定所有列的值語法：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• table_name: 要插入數(shù)據(jù)的表名。
• (column1, column2, ...): 可選。指定要插入數(shù)據(jù)的列名列表。如果省略此列表，則 VALUES 子句中必須提供表中所有列的值，并且順序必須與表中列的定義順序完全一致。
• VALUES (value1, value2, ...): 提供要插入的具體值。值的順序和類型必須與列名列表 (或表定義中的列順序) 匹配。

代碼案例：假設(shè)有一個(gè) 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);

如果省略列名列表 (不推薦，除非非常清楚表結(jié)構(gòu)且列順序不會改變)：

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

1.2 插入單行數(shù)據(jù)，指定部分列的值

如果某些列允許為 NULL 或有 DEFAULT 值，你可以只插入部分列的數(shù)據(jù)。

語法：

INSERT INTO table_name (column_a, column_b)
VALUES (value_a, value_b);

代碼案例：插入一個(gè)員工，只提供必要信息，hire_date 使用默認(rèn)值，salary 暫時(shí)不指定 (將為 NULL)：

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

1.3 插入多行數(shù)據(jù) (INSERT ALL)

Oracle 提供了 INSERT ALL 語句，可以一次性向一個(gè)或多個(gè)表中插入多行數(shù)據(jù)。

語法 (插入到同一張表的多行)：

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的虛擬表，這里用于觸發(fā)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 從其他表插入數(shù)據(jù) (INSERT INTO … SELECT)

可以將一個(gè) SELECT 語句的查詢結(jié)果直接插入到另一個(gè)表中。

語法：

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

代碼案例：假設(shè)有一個(gè) employees_archive 表，結(jié)構(gòu)與 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;

二、修改數(shù)據(jù) (UPDATE)

UPDATE 語句用于修改表中已存在行的列值。

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

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

• table_name: 要更新的表名。
• SET column1 = value1, ...: 指定要修改的列及其新值。
• WHERE condition: 非常重要！指定哪些行需要被更新。如果省略 WHERE 子句，表中所有行的指定列都會被更新，這通常是危險(xiǎn)操作。

代碼案例：將 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 修改所有行的列值 (謹(jǐn)慎使用)代碼案例：

給所有員工的薪水普調(diào)增加500 (假設(shè)所有員工都適用)：

UPDATE employees
SET salary = salary + 500;
-- 再次強(qiáng)調(diào)：沒有WHERE子句會更新所有行，操作前務(wù)必確認(rèn)！

2.3 使用子查詢更新數(shù)據(jù)

SET 子句中的值或 WHERE 子句中的條件可以來源于子查詢。

代碼案例：假設(shè)有一個(gè) departments_avg_salary 表 (department_id, avg_sal)。將 employees 表中每個(gè)員工的薪水更新為其所在部門的平均薪水 (僅為示例，實(shí)際邏輯可能更復(fù)雜)。

-- 僅為語法示例，實(shí)際邏輯可能需要更復(fù)雜的關(guān)聯(lián)更新
UPDATE employees e
SET e.salary = (SELECT d.avg_sal
                FROM departments_avg_salary d
                WHERE e.department_id = d.department_id) -- 假設(shè)employees表有department_id
WHERE EXISTS (SELECT 1
              FROM departments_avg_salary d
              WHERE e.department_id = d.department_id);

更常見的做法是使用 Oracle 的 MERGE 語句進(jìn)行復(fù)雜的關(guān)聯(lián)更新。

三、刪除數(shù)據(jù) (DELETE)

DELETE 語句用于從表中刪除一行或多行記錄。

3.1 刪除特定行語法：

DELETE FROM table_name
WHERE condition;

• table_name: 要刪除數(shù)據(jù)的表名。
• WHERE condition: 非常重要！指定哪些行需要被刪除。如果省略 WHERE 子句，表中所有行都會被刪除 (效果類似 TRUNCATE TABLE，但 DELETE 可以回滾，TRUNCATE 通常不行且更快，不過 TRUNCATE 不是本節(jié)重點(diǎn))。

代碼案例：刪除 employee_id 為 105 的員工記錄：

DELETE FROM employees
WHERE employee_id = 105;

刪除所有薪水低于40000的員工：

DELETE FROM employees
WHERE salary < 40000;

3.2 刪除所有行 (謹(jǐn)慎使用)代碼案例：

DELETE FROM employees;
-- 這會刪除employees表中的所有數(shù)據(jù)，但表結(jié)構(gòu)依然存在。
-- 如果要快速清空表并且不需要DML的回滾能力，TRUNCATE TABLE employees; 效率更高。

重要提示： 所有的 INSERT, UPDATE, DELETE 操作在默認(rèn)情況下（取決于您的客戶端工具設(shè)置，如SQL*Plus或SQL Developer）不是自動提交的。您需要顯式使用 COMMIT 命令來永久保存更改，或者使用 ROLLBACK 命令來撤銷未提交的更改。如果不提交就關(guān)閉會話，未提交的更改通常會自動回滾。

總結(jié)： INSERT, UPDATE, DELETE 是日常數(shù)據(jù)庫操作的核心。務(wù)必理解它們的語法，特別是 WHERE 子句在 UPDATE 和 DELETE 中的重要性，以避免意外修改或刪除數(shù)據(jù)。

練習(xí)題

背景表結(jié)構(gòu)：假設(shè)我們有以下兩個(gè)表：

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));

請為以下每個(gè)場景編寫相應(yīng)的SQL DML語句。 (提交您的DML語句后，記得使用 COMMIT; 保存更改，或 ROLLBACK; 撤銷操作，除非題目特別說明不需要。)

題目：

• 向 products 表中插入一條新產(chǎn)品記錄：product_id=1, product_name=‘Super Laptop’, category=‘Electronics’, price=1200.50, stock_quantity=50。
• 向 products 表中插入一條新產(chǎn)品記錄，只提供 product_id=2, product_name=‘Basic Mouse’, category=‘Accessories’。假設(shè) price 和 stock_quantity 允許為空或有默認(rèn)值。
• 創(chuàng)建一個(gè)名為 special_offers 的新表，其結(jié)構(gòu)包含 product_id, product_name, offer_price。然后從 products 表中選擇所有 category 為 ‘Electronics’ 且 price 大于1000的產(chǎn)品，將其 product_id, product_name 以及 price * 0.9 (作為 offer_price) 插入到 special_offers 表中。(只需寫INSERT INTO…SELECT部分，假設(shè)special_offers表已創(chuàng)建)。
• 將 products 表中 product_id 為 1 的產(chǎn)品的 price 更新為 1150.00，并將 stock_quantity 減少 5。
• 將 products 表中所有 category 為 ‘Accessories’ 的產(chǎn)品的 price 提高10%。
• 刪除 products 表中 stock_quantity 為 0 的所有產(chǎn)品記錄。
• 向 orders 表中插入一條新的訂單記錄：order_id=1001, product_id=1, customer_name=‘John Smith’, order_date=當(dāng)前系統(tǒng)日期, quantity_ordered=2。
• 更新 orders 表中 order_id 為 1001 的訂單，將其 quantity_ordered 修改為 3。
• 假設(shè)由于產(chǎn)品 product_id=2 已停產(chǎn)，需要刪除 orders 表中所有與該產(chǎn)品相關(guān)的訂單記錄。
• 清空 orders 表中的所有數(shù)據(jù)，但保留表結(jié)構(gòu)。

答案與解析：

• 插入新產(chǎn)品到 products：

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

• 解析： 使用了標(biāo)準(zhǔn)的 INSERT INTO ... VALUES 語句，明確指定了所有列名和對應(yīng)的值。

• 插入部分列到 products：

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

• 解析： 只為指定的列提供了值。未指定的 price 和 stock_quantity 列將根據(jù)表定義獲得默認(rèn)值或 NULL。

• 從 products 插入到 special_offers：(假設(shè) special_offers 表已創(chuàng)建，結(jié)構(gòu)：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 結(jié)構(gòu)。SELECT 語句從 products 表篩選數(shù)據(jù)，并計(jì)算 offer_price。查詢結(jié)果的列與 special_offers 表的列對應(yīng)插入。

• 更新特定產(chǎn)品信息：

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

• 解析： 使用 UPDATE 語句，SET 子句指定了要修改的多個(gè)列及其新值。WHERE 子句精確定位到 product_id 為 1 的記錄。

• 批量更新產(chǎn)品價(jià)格：

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

• 解析： WHERE 子句篩選出所有類別為 ‘Accessories’ 的產(chǎn)品，然后它們的 price 被更新為原價(jià)格的1.1倍。

• 刪除庫存為0的產(chǎn)品：

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 獲取當(dāng)前系統(tǒng)日期和時(shí)間的函數(shù)。

• 更新特定訂單數(shù)量：

UPDATE orders
SET quantity_ordered = 3
WHERE order_id = 1001;

• 解析： UPDATE 語句根據(jù) order_id 定位到特定訂單，并修改其 quantity_ordered。

• 刪除特定產(chǎn)品的所有訂單：

DELETE FROM orders
WHERE product_id = 2;

• 解析： DELETE 語句刪除 orders 表中所有 product_id 為 2 的訂單。由于 orders.product_id 有外鍵約束引用 products.product_id，如果 products 表中 product_id=2 的記錄也需要刪除，通常需要先刪除 orders 中的相關(guān)記錄 (或者外鍵設(shè)置了級聯(lián)刪除 ON DELETE CASCADE)。

• 清空 orders 表數(shù)據(jù)：

DELETE FROM orders;

• 解析： 由于沒有 WHERE 子句，此 DELETE 語句將刪除 orders 表中的所有行。表結(jié)構(gòu)會保留。
• 更高效的替代方案 (不可回滾，但更快，且是DDL操作)： TRUNCATE TABLE orders;

到此這篇關(guān)于Oracle 數(shù)據(jù)庫數(shù)據(jù)操作：精通 INSERT, UPDATE, DELETE的文章就介紹到這了,更多相關(guān)Oracle INSERT, UPDATE, DELETE內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論