SQL 必知必會 進階篇（第 14-22 章）：組合查詢、事務、視圖、存儲過程、觸發器完全筆記

第 14 章：組合查詢 (UNION)

-- 顯式列名（推薦）
INSERT INTO customers (cust_id, cust_email, cust_name, cust_zip, cust_country, cust_city)
VALUES (1021, 'liangbocv@126.com', 'Andy Liang', 610000, 'China', 'Chengdu');

-- 查詢結果插入
INSERT INTO customers (cust_id, cust_email, cust_name, cust_zip, cust_country, cust_city)
SELECT cust_id, cust_email, cust_name, cust_zip, cust_country, cust_city FROM custNew;

-- MySQL 創建表復制數據
CREATE TABLE custcopy2 AS SELECT * FROM customers;

第 16 章：更新與刪除 (UPDATE, DELETE)

-- 增加列
ALTER TABLE custcopy2 ADD cust_gender VARCHAR(8);
ALTER TABLE custcopy2 ADD add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 刪除表
DROP TABLE custcopy;

-- 重命名表
RENAME TABLE custcopy2 TO custcopy;

第 18 章：視圖 (VIEW)

-- 無參數
DELIMITER &&
CREATE PROCEDURE showAll()
BEGIN
  SELECT * FROM customers;
END &&
DELIMITER ;
CALL showAll();

-- 輸入參數
DELIMITER $
CREATE PROCEDURE getID(IN id VARCHAR(32))
BEGIN
  SELECT * FROM customers WHERE cust_id = id;
END $
DELIMITER ;
CALL getID('1000000001');

第 20 章：事務管理 (TRANSACTION)

CREATE TABLE test (
  id SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  socialid VARCHAR(64) UNIQUE,
  cust_id VARCHAR(16) NOT NULL REFERENCES customers(cust_id),
  quantity SMALLINT CHECK (quantity > 0),
  gender VARCHAR(8) CHECK (gender LIKE '[MF]')
);

• 主鍵：唯一、非空、不變、不重用、可被外鍵引用
• 唯一：唯一、可空、可變、可重用、不可被外鍵引用
• 外鍵：維護引用完整性
• 檢查：CHECK 靈活業務規則

ALTER 添加約束

CREATE INDEX prod_name_id ON PRODUCTS (prod_name);

提升搜索/排序/分組效率，代價：寫入慢、佔空間、動態維護。按查詢模式動態創建。

觸發器

CREATE TRIGGER ... BEFORE/AFTER INSERT/UPDATE/DELETE ON table FOR EACH ROW ...

• 用途：數據格式化（大寫）、審計日誌、聯動更新
• 約束優於觸發器（性能），能用約束別用觸發器