DB2 SQL 與 XQuery 教程（六）：DDL 與 DCL 核心語句實戰

教程簡介

本系列前五部分聚焦 SELECT 查詢，本部分系統講解 SQL 三大組成：DML（操縱）、DDL（定義）、DCL（控制）。以示例數據庫 Aroma（咖啡茶具銷售）為背景，演示表創建、數據增刪改、結構變更、視圖/索引、權限控制全生命周期操作。

環境準備

CONNECT TO aromadb USER userid USING password
-- 或無認證: CONNECT TO aromadb

SQL 組成速查

DDL 核心實戰

CREATE TABLE —— 黃金會員表

CREATE TABLE aroma.gold (
  card_id   SMALLINT NOT NULL PRIMARY KEY,
  storekey  INTEGER  NOT NULL,
  fname     VARCHAR(20) NOT NULL,
  lname     VARCHAR(20) NOT NULL,
  email     VARCHAR(30),
  status    VARCHAR(10) NOT NULL,
  FOREIGN KEY (storekey) REFERENCES aroma.store (storekey)
);

• PRIMARY KEY 保證卡號唯一；FOREIGN KEY 關聯門店表確保引用完整性。
• 列定義三要素：名稱、類型、NULL 特性（NOT NULL 或默認 NULL）。
• 命名規則：首字符字母/$/@/#，後續可含數字/下劃線。

INSERT —— 批量裝載會員

-- 單行
INSERT INTO aroma.gold VALUES (1, 1, 'Ada', 'Alexander', 'coffee_lover@hotmail.com', 'Active');

-- 多行（單語句）
INSERT INTO aroma.gold VALUES
  (2, 1, 'Ben', 'Bowman', 'mmmcoffee@yahoo.com', 'Active'),
  (3, 1, 'Cynthia', 'Chen', 'iheartcoffee@gmail.com', 'Active'),
  (4, 1, 'David', 'Doyle', 'coffee_maniac@msn.com', 'Active'),
  (5, 1, 'Emily', 'Enright', 'cupAday@hotmail.com', 'Active'),
  (6, 1, 'Frank', 'Freeman', 'coffee_addict@gmail.com', 'Active');

值順序必與列定義順序一致；字符串用單引號括起。

UPDATE —— 修改會員姓名

UPDATE aroma.gold
SET lname = 'Freeman'
WHERE card_id = 5;

關鍵：WHERE 子句必加，且宜用主鍵定位，防誤更新同名記錄。

DELETE —— 註銷會員

DELETE FROM aroma.gold WHERE card_id = 6;

同理必加 WHERE，否則全表清空。

ALTER TABLE —— 結構演進

-- 增加推薦計數列
ALTER TABLE aroma.gold ADD referrals SMALLINT;

-- 擴展主鍵類型應對增長
ALTER TABLE aroma.gold ALTER card_id SET DATA TYPE INTEGER;

-- 終止計劃刪除列
ALTER TABLE aroma.gold DROP COLUMN referrals;

新增列默認 NULL；類型變更需確保數據兼容；刪除列不可逆。

CREATE VIEW —— 隱私保護視圖

CREATE VIEW aroma.gold_public (card_id, storekey, status) AS
SELECT card_id, storekey, status FROM aroma.gold;

視圖不存儲數據，僅定義查詢；列名一對一映射，可重命名。

CREATE INDEX —— 查詢加速

CREATE INDEX aroma.gold_index ON aroma.gold (storekey);
RUNSTATS ON TABLE aroma.gold ON ALL COLUMNS;
COMMIT WORK;

索引創建後必執行 RUNSTATS 更新統計信息，優化器才會採用新索引（訪問計劃從 TBSCAN 轉為 IXSCAN）。

DROP TABLE —— 方案退役

DROP TABLE aroma.gold;

不可逆操作，數據永久丟失。驗證：SELECT * FROM sysibm.systables WHERE creator='AROMA'; 確認表已消失。

DCL 權限控制

GRANT —— 授權新 DBA

GRANT SELECT, INSERT, UPDATE, DELETE ON aroma.sales TO user02;

權限列表可含 ALL PRIVILEGES。

REVOKE —— 收回危險權限

REVOKE UPDATE, DELETE ON aroma.sales FROM user02;

保留查詢與插入權限，禁止修改歷史數據。

總結

掌握 DDL/DCL 使開發人員具備數據庫對象全生命周期管理能力，配合 DML 構建完整應用數據層。關鍵原則：主鍵定位更新刪除、索引配合 RUNSTATS、視圖實現列級安全、最小權限授予。