SQL Server ALTER TABLE 實戰大全：欄位增刪改、索引、外鍵、級聯操作

欄位操作

-- 修改既有欄位為 IDENTITY
ALTER TABLE tbl ALTER COLUMN id INT NOT NULL IDENTITY(1,1);

-- 新增欄位並設為主鍵
ALTER TABLE tbl ADD new_id INT IDENTITY(1,1) PRIMARY KEY;

-- 新增主鍵
ALTER TABLE tbl ADD PRIMARY KEY (id);

-- 新增欄位
ALTER TABLE tbl ADD col_name VARCHAR(50);

-- 刪除欄位
ALTER TABLE tbl DROP COLUMN col_name;

-- 重命名欄位
ALTER TABLE tbl CHANGE old_name new_name INT;  -- MySQL 語法
-- SQL Server: sp_rename 'tbl.old_name', 'new_name', 'COLUMN';

-- 調整欄位順序 (MySQL)
ALTER TABLE `users` CHANGE `user_password` `user_password` VARCHAR(20) NOT NULL AFTER user_name;

-- 修改資料型別
ALTER TABLE tbl ALTER COLUMN col_name BIGINT NOT NULL;
ALTER TABLE infos ALTER COLUMN list TINYINT NOT NULL DEFAULT 0;

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;  -- MySQL
-- SQL Server: sp_rename 'old_name', 'new_name';

外鍵與級聯

-- 建立含級聯的外鍵
CREATE TABLE mail_model (
    id VARCHAR(50) PRIMARY KEY,
    mail_filename VARCHAR(200),
    content VARCHAR(2000)
) ENGINE=InnoDB;

CREATE TABLE mail_model_extend (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rid VARCHAR(50) NOT NULL,
    content VARCHAR(2000),
    INDEX (rid),
    FOREIGN KEY (rid) REFERENCES mail_model(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

索引概念深度解析

為何需要索引？

• 無索引 = 全表掃描，資料量大時極慢
• 索引 = 排序後的查找結構（B+ Tree），O(log n) 定位

叢集 vs 非叢集索引

索引類型對照

• 唯一索引：鍵值不可重複，CREATE UNIQUE INDEX
• 複合索引：多欄組合，最多 16 欄，CREATE INDEX idx_ab ON t(a,b)
• 最左前綴：複合索引 (a,b,c) 支援 a/a,b/a,b,c

索引使用黃金法則

1. 低更新高查詢 + 重複值多 → 叢集索引
2. 常同時存取多欄 → 複合索引，前導欄選擇度最高者
3. WHERE 欄位運算導致索引失效 → 移至等號右側
4. LIKE '%str%' 導掃 → 全文索引或搜尋引擎
5. IN/OR 可能用工作表 → 拆分為 UNION ALL
6. 善用 ANALYZE TABLE 更新統計、EXPLAIN 驗證計畫
7. 存儲過程封裝複雜邏輯，減少網路往返