您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 實驗二：資料操縱語言 (DML) 完整指南與 SQL 範例

編輯：Oracle 教學

Oracle 實驗二：資料操縱語言 (DML) 完整指南與 SQL 範例

本實驗旨在讓學習者掌握 Oracle 資料庫中 SQL 資料操縱語言 (DML) 的核心操作，包含資料查詢、聚合函數應用，以及資料的新增、修改與刪除。實驗採用兩個經典範例資料庫：「工程項目採購資料庫」與「產品價格資料庫」，透過實際操作強化 SQL 語法與邏輯思維。

一、實驗目的

熟練撰寫 SQL 資料查詢語句 (SELECT)，包含單表、多表聯結與子查詢。
掌握聚合函數 (COUNT, SUM, AVG, MAX, MIN) 及 GROUP BY、HAVING 子句的分組統計技巧。
熟悉 INSERT、UPDATE、DELETE 三大資料異動指令，理解交易控制 (COMMIT/ROLLBACK) 的重要性。

二、實驗內容與 SQL 範例解析

範例資料庫 1：工程項目採購 (SPJ 資料結構)

核心表格：S (供應商)、P (零件)、J (工程)、SPJ (供應關係)。

查詢操作 (1-7)

-- (1) 向工程 J1 提供零件的供應商編號
SELECT DISTINCT SNO FROM SPJ WHERE JNO = 'J1';

-- (2) 向工程 J1 提供零件 P1 的供應商編號
SELECT DISTINCT SNO FROM SPJ WHERE JNO = 'J1' AND PNO = 'P1';

-- (3) 向工程 J1 提供紅色零件的供應商編號 (需聯結 P 表)
SELECT DISTINCT SPJ.SNO FROM SPJ, P WHERE SPJ.JNO = 'J1' AND SPJ.PNO = P.PNO AND P.COLOR = '紅';

-- (4) 未使用天津供應商提供紅色零件的工程號 (使用 NOT EXISTS 關聯子查詢)
SELECT DISTINCT JNO FROM J 
WHERE NOT EXISTS (
    SELECT * FROM SPJ SPJ1 
    WHERE SPJ1.SNO IN (SELECT SNO FROM S WHERE CITY = '天津')
    AND SPJ1.PNO IN (SELECT PNO FROM P WHERE COLOR = '紅')
    AND SPJ1.JNO = J.JNO
);

-- (5) 至少使用了供應商 S1 所供全部零件的工程號 (關係除法邏輯，雙重 NOT EXISTS)
SELECT DISTINCT JNO FROM J 
WHERE NOT EXISTS (
    SELECT * FROM SPJ SPJ1 WHERE SPJ1.SNO = 'S1'
    AND NOT EXISTS (
        SELECT * FROM SPJ SPJ2 WHERE SPJ2.PNO = SPJ1.PNO AND SPJ2.JNO = J.JNO
    )
);

-- (6) 統計各供應商供應的零件總數
SELECT SNO, COUNT(*) FROM SPJ GROUP BY SNO;

-- (7) 至少使用了 3 種以上零件的工程號
SELECT JNO FROM SPJ GROUP BY JNO HAVING COUNT(*) >= 3;

資料異動操作 (8-10)

-- (8) 將所有紅色零件顏色改為粉紅色
UPDATE P SET COLOR = '粉紅' WHERE COLOR = '紅';

-- (9) 將向工程 J1 提供零件 P1 的供應商由 S1 改為 S2
UPDATE SPJ SET SNO = 'S2' WHERE SNO = 'S1' AND JNO = 'J1' AND PNO = 'P1';

-- (10) 刪除全部紅色零件及其供應記錄 (須先刪除子表 SPJ 再刪除父表 P)
DELETE FROM SPJ WHERE PNO IN (SELECT PNO FROM P WHERE COLOR = '紅');
DELETE FROM P WHERE COLOR = '紅';

範例資料庫 2：產品價格資料庫 (Product, PC, Laptop, Printer)

查詢操作 (1-8)

-- (1) 速度至少 150 的 PC 型號
SELECT MODEL FROM PC WHERE SPEED >= 150;

-- (2) 硬碟不低於 1GB 的 Laptop 廠商
SELECT DISTINCT PRODUCT.MAKER FROM PRODUCT, LAPTOP 
WHERE PRODUCT.MODEL = LAPTOP.MODEL AND LAPTOP.HD >= 1.0;

-- (3) 廠商 B 所有產品的型號與價格 (使用 UNION 合併三類產品)
SELECT MAKER, PRODUCT.MODEL, PRICE FROM PRODUCT, PC WHERE PRODUCT.MAKER = 'B' AND PRODUCT.MODEL = PC.MODEL
UNION
SELECT MAKER, PRODUCT.MODEL, PRICE FROM PRODUCT, LAPTOP WHERE PRODUCT.MAKER = 'B' AND PRODUCT.MODEL = LAPTOP.MODEL
UNION
SELECT MAKER, PRODUCT.MODEL, PRICE FROM PRODUCT, PRINTER WHERE PRODUCT.MAKER = 'B' AND PRODUCT.MODEL = PRINTER.MODEL;

-- (4) 所有彩色雷射印表機型號
SELECT MODEL FROM PRINTER WHERE COLOR = 'TRUE';

-- (5) 生產速度至少 450MHz PC 的廠商
SELECT DISTINCT PRODUCT.MAKER FROM PRODUCT, PC WHERE PRODUCT.TYPE = 'PC' AND PRODUCT.MODEL = PC.MODEL AND PC.SPEED > 450;

-- (6) 價格最高的印表機 (兩種寫法)
SELECT * FROM PRINTER WHERE PRICE >= ALL (SELECT PRICE FROM PRINTER);
SELECT MODEL, PRICE FROM PRINTER WHERE PRICE = (SELECT MAX(PRICE) FROM PRINTER);

-- (7) 每個廠商 PC 的最高價格
SELECT MAKER, MAX(PRICE) FROM PRODUCT, PC WHERE PRODUCT.MODEL = PC.MODEL AND PRODUCT.TYPE = 'PC' GROUP BY MAKER;

-- (8) 至少生產三種不同型號 PC 的廠商
SELECT MAKER FROM PRODUCT WHERE TYPE = 'PC' GROUP BY MAKER HAVING COUNT(*) >= 3;

資料異動操作 (9-10)

-- (9) 廠商 A 併購廠商 B，將 B 產品改為 A 生產
UPDATE PRODUCT SET MAKER = 'A' WHERE MAKER = 'B';

-- (10) 刪除不生產印表機的廠商所生產的 Laptop
DELETE FROM PRODUCT P1 WHERE P1.TYPE = 'LAPTOP' 
AND EXISTS (SELECT * FROM PRODUCT P2 WHERE P2.TYPE = 'PRINTER' AND P1.MODEL = P2.MODEL);
ROLLBACK; -- 實驗結束回滾，保持資料原狀