2025年03月19日玄貓（BlackCat）

SQL 資料庫更新刪除與虛擬表應用

深入探討 SQL 資料操作語言中的 UPDATE 與 DELETE 語句，詳細解析單欄位與多欄位更新技術、條件式刪除操作、交易處理安全機制，以及虛擬表（View）的建立與應用，涵蓋資料安全、效能最佳化與實務案例。

資料庫 SQL

SQL Database UPDATE DELETE View Data Manipulation

資料庫管理的核心工作之一就是對既有資料進行修改與刪除，這些操作看似簡單，卻攸關整個系統的資料完整性與安全性。SQL 提供了 UPDATE 與 DELETE 兩個強大的資料操作語句，讓開發者能夠精確地控制資料的變更範圍。然而，這些操作的強大也意味著潛在的風險，一個不小心的 WHERE 子句遺漏，就可能導致整個資料表的資料被修改或刪除。因此，深入理解這些語句的運作機制與最佳實踐，對於任何資料庫開發者而言都是不可或缺的基本功。

除了基本的資料操作之外，虛擬表也就是檢視的概念為資料庫設計帶來了更高層次的抽象能力。檢視讓我們能夠將複雜的查詢邏輯封裝成易於使用的介面，同時提供資料安全性的控制機制。透過檢視，我們可以限制使用者只能存取特定的資料欄位或資料列，而不需要直接授予底層資料表的存取權限。這種機制在企業級應用中特別重要，因為它能夠在不影響應用程式邏輯的情況下，實現細緻的資料存取控制。

本文將從最基本的 UPDATE 語句開始，逐步深入到複雜的更新場景，包含多欄位更新、條件式更新、以及跨表更新等進階技術。接著，我們會探討 DELETE 語句的各種使用情境，並特別強調安全操作的重要性。最後，我們將全面介紹檢視的建立與應用，從基本語法到進階的可更新檢視，讓讀者能夠完整掌握這些資料庫操作的精髓。

UPDATE 語句基礎與單欄位更新

UPDATE 語句是 SQL 資料操作語言中用於修改既有資料的核心語句。當我們需要更改資料表中一個或多個資料列的欄位值時，UPDATE 語句就是我們的首選工具。這個語句的基本結構包含三個主要部分：指定要更新的資料表、設定新的欄位值、以及選擇性地指定更新條件。

最基本的 UPDATE 語法結構如下所示，其中 SET 子句用於指定要更新的欄位與新值，而 WHERE 子句則用於限定要更新的資料列範圍。如果省略 WHERE 子句，UPDATE 語句將會更新資料表中的所有資料列，這在大多數情況下並非我們想要的結果。

-- UPDATE 語句的基本結構
-- 此語法展示了更新資料的標準格式
UPDATE table_name           -- 指定要更新的資料表名稱
SET column_name = new_value -- 設定要更新的欄位與新值
WHERE condition;            -- 指定更新條件，限定要更新的資料列

讓我們以一個實際的場景來說明單欄位更新的操作。假設我們有一個管理體育場館的資料庫，其中包含場館名稱、座位容量、建造年份等資訊。當某個場館進行擴建工程後，我們需要更新其座位容量。

-- 建立場館資料表作為範例
-- 此資料表儲存各體育場館的基本資訊
CREATE TABLE arena (
    arena_id INT PRIMARY KEY AUTO_INCREMENT,  -- 場館唯一識別碼，自動遞增
    arena_name VARCHAR(100) NOT NULL,         -- 場館名稱，不可為空
    city VARCHAR(50) NOT NULL,                -- 所在城市
    seating_capacity INT,                     -- 座位容量
    year_built INT,                           -- 建造年份
    last_renovation DATE                      -- 最後一次翻修日期
);

-- 插入測試資料
-- 新增數筆場館記錄用於後續操作示範
INSERT INTO arena (arena_name, city, seating_capacity, year_built, last_renovation)
VALUES
    ('台北小巨蛋', '台北', 15000, 2005, '2020-06-15'),
    ('高雄巨蛋', '高雄', 15000, 2008, '2019-03-20'),
    ('新莊體育館', '新北', 8000, 1990, '2018-12-01'),
    ('台中洲際棒球場', '台中', 20000, 2006, '2021-01-10'),
    ('桃園國際棒球場', '桃園', 20000, 2009, '2022-05-25'),
    ('台南市立棒球場', '台南', 12000, 1995, '2017-08-30');

當我們需要更新特定場館的座位容量時，必須在 WHERE 子句中明確指定該場館的識別條件。以下範例示範如何將台北小巨蛋的座位容量更新為 16000。

-- 更新特定場館的座位容量
-- 使用 WHERE 子句限定只更新 arena_id 為 1 的場館
UPDATE arena
SET seating_capacity = 16000    -- 將座位容量設定為 16000
WHERE arena_id = 1;             -- 只更新 arena_id 等於 1 的記錄

-- 驗證更新結果
-- 查詢該場館確認座位容量已正確更新
SELECT arena_id, arena_name, seating_capacity
FROM arena
WHERE arena_id = 1;

執行上述 UPDATE 語句後，資料庫會回報受影響的資料列數量。如果 WHERE 子句的條件符合一筆記錄，則會顯示 “1 row affected”。這個回饋資訊對於確認操作是否成功非常重要，特別是在執行批次更新時。

單欄位更新的一個重要變體是使用計算式來設定新值。例如，如果我們想要將所有場館的座位容量增加百分之十，可以在 SET 子句中使用算術運算式。

-- 使用計算式更新座位容量
-- 將所有場館的座位容量增加 10%
UPDATE arena
SET seating_capacity = seating_capacity * 1.1;  -- 原值乘以 1.1 即增加 10%

-- 查看更新後的結果
-- 顯示所有場館的新座位容量
SELECT arena_id, arena_name,
       ROUND(seating_capacity) AS new_capacity  -- 四捨五入顯示整數
FROM arena;

這種計算式更新在許多業務場景中非常實用，例如調整產品價格、更新庫存數量、計算累計數值等。值得注意的是，當使用計算式更新時，資料庫會先讀取原始值，進行計算後再寫入新值，整個過程在單一操作中完成。

多欄位更新與進階技術

在實際應用中，我們經常需要同時更新多個欄位。UPDATE 語句允許在單一 SET 子句中指定多個欄位的新值，各欄位之間使用逗號分隔。這種方式不僅語法簡潔，而且執行效率也比分別執行多個 UPDATE 語句來得高。

-- 同時更新多個欄位
-- 更新特定場館的名稱、座位容量與翻修日期
UPDATE arena
SET arena_name = '台北大巨蛋',           -- 更新場館名稱
    seating_capacity = 40000,             -- 更新座位容量
    last_renovation = '2023-11-01'        -- 更新最後翻修日期
WHERE arena_id = 1;                       -- 限定更新特定場館

-- 確認多欄位更新結果
-- 查詢該場館的所有欄位以驗證更新
SELECT *
FROM arena
WHERE arena_id = 1;

多欄位更新的一個重要特性是所有欄位的更新會在同一個操作中原子性地完成。這意味著如果更新過程中發生錯誤，所有的變更都會被撤銷，確保資料的一致性。

當我們需要根據複雜條件進行更新時，可以使用 CASE 表達式來實現條件式更新。這種技術特別適合需要根據不同條件設定不同值的場景。

-- 使用 CASE 表達式進行條件式更新
-- 根據場館所在城市調整座位容量的增幅
UPDATE arena
SET seating_capacity = CASE
    WHEN city = '台北' THEN seating_capacity * 1.2   -- 台北場館增加 20%
    WHEN city = '高雄' THEN seating_capacity * 1.15  -- 高雄場館增加 15%
    WHEN city = '台中' THEN seating_capacity * 1.1   -- 台中場館增加 10%
    ELSE seating_capacity * 1.05                      -- 其他城市增加 5%
END;

-- 查看條件式更新後的結果
-- 顯示各場館更新後的座位容量
SELECT arena_id, arena_name, city,
       ROUND(seating_capacity) AS updated_capacity
FROM arena
ORDER BY city;

跨表更新是另一個進階技術，它允許我們根據另一個資料表的資料來更新目標資料表。這在資料同步、批次更新等場景中非常有用。MySQL 支援在 UPDATE 語句中使用 JOIN 來實現跨表更新。

-- 建立城市資訊表作為更新來源
-- 儲存各城市的補助金額資訊
CREATE TABLE city_subsidy (
    city VARCHAR(50) PRIMARY KEY,  -- 城市名稱作為主鍵
    subsidy_rate DECIMAL(3,2)      -- 補助比率
);

-- 插入城市補助資料
INSERT INTO city_subsidy (city, subsidy_rate)
VALUES
    ('台北', 1.20),
    ('高雄', 1.15),
    ('台中', 1.10),
    ('新北', 1.08),
    ('桃園', 1.05);

-- 跨表更新：根據城市補助比率調整座位容量
-- 使用 JOIN 連結兩個資料表進行更新
UPDATE arena a
JOIN city_subsidy cs ON a.city = cs.city    -- 透過城市欄位連結兩表
SET a.seating_capacity = a.seating_capacity * cs.subsidy_rate;  -- 應用補助比率

-- 驗證跨表更新結果
SELECT a.arena_id, a.arena_name, a.city,
       ROUND(a.seating_capacity) AS adjusted_capacity,
       cs.subsidy_rate
FROM arena a
JOIN city_subsidy cs ON a.city = cs.city
ORDER BY a.city;

子查詢更新是另一種強大的技術，它允許我們使用 SELECT 語句的結果作為更新的來源值。這種方式可以實現更複雜的更新邏輯。

-- 使用子查詢進行更新
-- 將座位容量低於平均值的場館更新為平均值
UPDATE arena
SET seating_capacity = (
    SELECT AVG(seating_capacity)  -- 計算所有場館的平均座位容量
    FROM arena
)
WHERE seating_capacity < (
    SELECT AVG(seating_capacity)  -- 選取座位容量低於平均值的場館
    FROM arena
);

-- 注意：上述語句在 MySQL 中會報錯，因為不能在 UPDATE 的子查詢中引用正在更新的表
-- 正確做法是使用臨時表或變數

-- 使用變數實現相同邏輯
SET @avg_capacity = (SELECT AVG(seating_capacity) FROM arena);

UPDATE arena
SET seating_capacity = @avg_capacity
WHERE seating_capacity < @avg_capacity;

DELETE 語句與資料刪除操作

DELETE 語句用於從資料表中刪除一個或多個資料列。與 UPDATE 語句類似，DELETE 語句也需要謹慎使用，因為刪除操作通常是不可逆的。在沒有適當備份的情況下，誤刪資料可能導致嚴重的後果。

DELETE 語句的基本語法非常簡單，只需要指定要刪除資料的資料表名稱，以及選擇性的 WHERE 子句來限定刪除範圍。

-- DELETE 語句的基本結構
-- 此語法展示了刪除資料的標準格式
DELETE FROM table_name    -- 指定要刪除資料的資料表
WHERE condition;          -- 指定刪除條件，限定要刪除的資料列

讓我們以具體的範例來說明 DELETE 語句的使用方式。假設我們需要刪除一個已經拆除的場館記錄。

-- 刪除特定場館記錄
-- 根據 arena_id 刪除指定的場館
DELETE FROM arena
WHERE arena_id = 6;    -- 刪除 arena_id 為 6 的場館記錄

-- 確認刪除結果
-- 查詢以確認該記錄已被刪除
SELECT COUNT(*) AS remaining_count
FROM arena
WHERE arena_id = 6;    -- 應回傳 0

當 DELETE 語句執行成功時，資料庫會回報受影響的資料列數量。如果 WHERE 子句的條件沒有匹配到任何資料列，則會顯示 “0 rows affected”，這表示沒有資料被刪除。這個資訊對於除錯和確認操作結果非常重要。

DELETE 語句可以使用各種條件運算子來指定刪除範圍，包括比較運算子、邏輯運算子、以及模式匹配等。

-- 使用多重條件刪除
-- 刪除建造年份早於 2000 年且未翻修的場館
DELETE FROM arena
WHERE year_built < 2000              -- 建造年份早於 2000 年
  AND last_renovation < '2015-01-01';  -- 最後翻修日期早於 2015 年

-- 使用 LIKE 進行模式匹配刪除
-- 刪除名稱包含「體育館」的場館
DELETE FROM arena
WHERE arena_name LIKE '%體育館%';    -- 名稱包含「體育館」

-- 使用 IN 刪除多個指定記錄
-- 刪除特定城市的所有場館
DELETE FROM arena
WHERE city IN ('基隆', '嘉義', '屏東');  -- 刪除這三個城市的場館

-- 使用 BETWEEN 刪除範圍內的記錄
-- 刪除座位容量在特定範圍內的場館
DELETE FROM arena
WHERE seating_capacity BETWEEN 5000 AND 10000;  -- 容量介於 5000 至 10000

一個非常危險但有時必要的操作是刪除資料表中的所有資料。這可以透過省略 WHERE 子句來實現，但必須極度小心。

-- 刪除所有資料（危險操作）
-- 此語句將刪除 arena 資料表中的所有記錄
DELETE FROM arena;    -- 沒有 WHERE 子句，刪除所有資料

-- 如果需要保留資料表結構但清空資料，TRUNCATE 更有效率
-- TRUNCATE 比 DELETE 快，因為它不記錄個別刪除操作
TRUNCATE TABLE arena;  -- 清空資料表，重置自動遞增值

DELETE 與 TRUNCATE 的主要差異在於：DELETE 是 DML 語句，可以有 WHERE 子句、可以回滾、會觸發觸發器；而 TRUNCATE 是 DDL 語句，會直接清空整個資料表、無法回滾、不觸發觸發器，但執行速度更快。

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100

title DELETE 與 TRUNCATE 比較

rectangle "DELETE 特性" as delete {
    card "可使用 WHERE" as d1
    card "可回滾" as d2
    card "觸發 Trigger" as d3
    card "逐列刪除" as d4
    card "保留自增值" as d5
}

rectangle "TRUNCATE 特性" as truncate {
    card "無法使用 WHERE" as t1
    card "無法回滾" as t2
    card "不觸發 Trigger" as t3
    card "直接清空" as t4
    card "重置自增值" as t5
}

delete -[hidden]right- truncate

@enduml

安全刪除與交易處理

在生產環境中執行 DELETE 操作時，安全性是首要考量。一個良好的實踐是在執行 DELETE 之前，先使用 SELECT 語句確認將要刪除的資料。

-- 安全刪除的最佳實踐
-- 步驟一：先查詢要刪除的資料
SELECT *
FROM arena
WHERE year_built < 2000;    -- 先確認符合條件的記錄

-- 步驟二：確認無誤後再執行刪除
DELETE FROM arena
WHERE year_built < 2000;    -- 執行刪除操作

交易處理是確保資料操作安全性的重要機制。透過將 DELETE 操作放在交易中，我們可以在確認結果正確後再提交變更，或在發現問題時回滾操作。

-- 使用交易進行安全刪除
-- 開始交易
START TRANSACTION;

-- 執行刪除操作
DELETE FROM arena
WHERE seating_capacity < 10000;

-- 查看刪除結果
SELECT COUNT(*) AS remaining_count
FROM arena;

-- 如果結果正確，提交交易
COMMIT;

-- 如果結果有誤，回滾交易
-- ROLLBACK;

在刪除具有外鍵關聯的資料時，需要特別注意參照完整性。如果子表有記錄參照到要刪除的父表記錄，刪除操作會失敗，除非設定了適當的外鍵約束動作。

-- 建立具有外鍵關聯的資料表
CREATE TABLE event (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    arena_id INT,
    event_date DATE,
    -- 設定外鍵約束，當父記錄刪除時設為 NULL
    FOREIGN KEY (arena_id) REFERENCES arena(arena_id)
        ON DELETE SET NULL    -- 刪除場館時，將此欄位設為 NULL
        ON UPDATE CASCADE     -- 更新場館 ID 時，同步更新此欄位
);

-- 建立另一個關聯表，刪除時連帶刪除
CREATE TABLE arena_photo (
    photo_id INT PRIMARY KEY AUTO_INCREMENT,
    arena_id INT NOT NULL,
    photo_url VARCHAR(255),
    -- 設定外鍵約束，當父記錄刪除時連帶刪除
    FOREIGN KEY (arena_id) REFERENCES arena(arena_id)
        ON DELETE CASCADE    -- 刪除場館時，連帶刪除相關照片
);

軟刪除是另一種常見的刪除策略，它不實際刪除資料，而是透過標記欄位來標示資料已被刪除。這種方式可以保留資料的歷史記錄，並且可以輕易地恢復被刪除的資料。

-- 實作軟刪除機制
-- 在資料表中新增刪除標記欄位
ALTER TABLE arena
ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE,    -- 刪除標記
ADD COLUMN deleted_at DATETIME;                  -- 刪除時間

-- 軟刪除操作
UPDATE arena
SET is_deleted = TRUE,
    deleted_at = NOW()
WHERE arena_id = 3;

-- 查詢時排除已刪除的記錄
SELECT *
FROM arena
WHERE is_deleted = FALSE;

-- 建立檢視簡化查詢
CREATE VIEW v_active_arena AS
SELECT arena_id, arena_name, city, seating_capacity,
       year_built, last_renovation
FROM arena
WHERE is_deleted = FALSE;

-- 恢復已刪除的記錄
UPDATE arena
SET is_deleted = FALSE,
    deleted_at = NULL
WHERE arena_id = 3;

虛擬表基礎：檢視的建立與查詢

虛擬表，或稱為檢視，是 SQL 中一種強大的抽象機制。檢視本質上是一個預先定義的查詢，它以虛擬資料表的形式呈現，可以像一般資料表一樣進行查詢。每當我們查詢檢視時，資料庫會執行定義檢視的查詢語句，並回傳最新的結果。

檢視的主要優點包括簡化複雜查詢、提供資料安全性控制、以及建立邏輯資料獨立性。透過檢視，我們可以將複雜的 JOIN 查詢或子查詢封裝成簡單易用的介面，讓應用程式開發者無需了解底層資料表的複雜結構。

-- 建立基本檢視
-- 此檢視只顯示場館的基本資訊
CREATE VIEW v_arena_basic AS
SELECT arena_id,
       arena_name,
       city,
       seating_capacity
FROM arena
WHERE is_deleted = FALSE;    -- 只包含未刪除的記錄

-- 查詢檢視
-- 就像查詢一般資料表一樣
SELECT *
FROM v_arena_basic
WHERE city = '台北';

-- 檢視也可以使用聚合函式
SELECT city, COUNT(*) AS arena_count, SUM(seating_capacity) AS total_capacity
FROM v_arena_basic
GROUP BY city;

檢視命名的最佳實踐是使用有意義的名稱並加上前綴以區分檢視與資料表。常見的前綴包括 v_ 或 vw_ 表示這是一個檢視。

-- 建立多種用途的檢視
-- 報表用檢視：場館統計資訊
CREATE VIEW v_arena_statistics AS
SELECT
    city,
    COUNT(*) AS arena_count,              -- 場館數量
    SUM(seating_capacity) AS total_seats, -- 總座位數
    AVG(seating_capacity) AS avg_seats,   -- 平均座位數
    MIN(year_built) AS oldest_year,       -- 最早建造年份
    MAX(year_built) AS newest_year        -- 最近建造年份
FROM arena
WHERE is_deleted = FALSE
GROUP BY city;

-- 查詢統計檢視
SELECT *
FROM v_arena_statistics
ORDER BY total_seats DESC;

-- 應用程式用檢視：格式化的場館資訊
CREATE VIEW v_arena_display AS
SELECT
    arena_id,
    arena_name,
    city,
    FORMAT(seating_capacity, 0) AS formatted_capacity,  -- 格式化數字
    CONCAT(year_built, '年建造') AS built_info,         -- 建造資訊
    COALESCE(DATE_FORMAT(last_renovation, '%Y年%m月'), '尚未翻修') AS renovation_info
FROM arena
WHERE is_deleted = FALSE;

-- 使用格式化檢視
SELECT *
FROM v_arena_display
WHERE city = '台北';

進階檢視：連結與子查詢

檢視可以基於複雜的查詢來建立，包含多表連結、子查詢、以及各種進階 SQL 功能。這使得檢視成為封裝複雜業務邏輯的理想工具。

-- 建立活動資料表用於示範
CREATE TABLE event (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    arena_id INT,
    event_date DATE,
    ticket_price DECIMAL(10, 2),
    expected_attendance INT,
    FOREIGN KEY (arena_id) REFERENCES arena(arena_id)
);

-- 插入活動測試資料
INSERT INTO event (event_name, arena_id, event_date, ticket_price, expected_attendance)
VALUES
    ('五月天演唱會', 1, '2024-12-31', 2800, 40000),
    ('NBA 季前賽', 1, '2024-10-15', 3500, 38000),
    ('黑豹旗棒球賽', 4, '2024-11-20', 500, 15000),
    ('SBL 籃球聯賽', 2, '2024-12-05', 800, 12000);

-- 建立包含 JOIN 的檢視
-- 此檢視結合場館與活動資訊
CREATE VIEW v_event_detail AS
SELECT
    e.event_id,
    e.event_name,
    e.event_date,
    e.ticket_price,
    e.expected_attendance,
    a.arena_name,
    a.city,
    a.seating_capacity,
    -- 計算預期營收
    e.ticket_price * e.expected_attendance AS expected_revenue,
    -- 計算座位使用率
    ROUND(e.expected_attendance / a.seating_capacity * 100, 2) AS occupancy_rate
FROM event e
JOIN arena a ON e.arena_id = a.arena_id
WHERE a.is_deleted = FALSE;

-- 查詢活動詳情檢視
SELECT *
FROM v_event_detail
ORDER BY event_date;

包含子查詢的檢視可以實現更複雜的邏輯，例如計算排名、比較與平均值的差異等。

-- 建立包含子查詢的檢視
-- 此檢視顯示場館座位容量與城市平均值的比較
CREATE VIEW v_arena_comparison AS
SELECT
    a.arena_id,
    a.arena_name,
    a.city,
    a.seating_capacity,
    -- 計算該城市的平均座位容量
    (SELECT AVG(seating_capacity)
     FROM arena
     WHERE city = a.city AND is_deleted = FALSE) AS city_avg_capacity,
    -- 計算與城市平均值的差異
    a.seating_capacity - (SELECT AVG(seating_capacity)
                          FROM arena
                          WHERE city = a.city AND is_deleted = FALSE) AS diff_from_avg,
    -- 判斷是否高於平均
    CASE
        WHEN a.seating_capacity > (SELECT AVG(seating_capacity)
                                   FROM arena
                                   WHERE city = a.city AND is_deleted = FALSE)
        THEN '高於平均'
        ELSE '低於平均'
    END AS comparison_result
FROM arena a
WHERE a.is_deleted = FALSE;

-- 使用比較檢視
SELECT *
FROM v_arena_comparison
ORDER BY city, seating_capacity DESC;

分層檢視是一種設計模式，透過在檢視之上建立檢視來逐步增加複雜度。這種方式可以提高可維護性和重用性。

-- 第一層檢視：基礎資料清理
CREATE VIEW v_arena_base AS
SELECT
    arena_id,
    TRIM(arena_name) AS arena_name,    -- 移除前後空白
    TRIM(city) AS city,
    COALESCE(seating_capacity, 0) AS seating_capacity,  -- NULL 轉為 0
    year_built,
    last_renovation
FROM arena
WHERE is_deleted = FALSE;

-- 第二層檢視：加入計算欄位
CREATE VIEW v_arena_enriched AS
SELECT
    v.*,
    YEAR(CURDATE()) - year_built AS age_years,  -- 計算場館年齡
    CASE
        WHEN seating_capacity >= 30000 THEN '大型'
        WHEN seating_capacity >= 15000 THEN '中型'
        ELSE '小型'
    END AS size_category
FROM v_arena_base v;

-- 第三層檢視：加入統計資訊
CREATE VIEW v_arena_complete AS
SELECT
    ve.*,
    (SELECT COUNT(*) FROM event WHERE arena_id = ve.arena_id) AS event_count,
    (SELECT SUM(expected_attendance) FROM event WHERE arena_id = ve.arena_id) AS total_attendance
FROM v_arena_enriched ve;

-- 使用最終的完整檢視
SELECT *
FROM v_arena_complete
ORDER BY event_count DESC;

可更新檢視與安全性控制

某些檢視是可更新的，這意味著我們可以透過檢視來執行 INSERT、UPDATE 和 DELETE 操作，這些操作會直接影響底層的資料表。然而，並非所有檢視都是可更新的，可更新檢視必須滿足特定條件。

可更新檢視的條件包括：不包含聚合函式、不包含 DISTINCT、不包含 GROUP BY 或 HAVING、不包含 UNION、不包含子查詢在 SELECT 清單中、不從多個資料表中選取欄位等。

-- 建立可更新檢視
-- 此檢視滿足可更新的所有條件
CREATE VIEW v_arena_updatable AS
SELECT
    arena_id,
    arena_name,
    city,
    seating_capacity,
    year_built,
    last_renovation
FROM arena
WHERE is_deleted = FALSE;

-- 透過檢視執行 INSERT
INSERT INTO v_arena_updatable
    (arena_name, city, seating_capacity, year_built, last_renovation)
VALUES
    ('嘉義市立棒球場', '嘉義', 10000, 2000, '2020-01-01');

-- 透過檢視執行 UPDATE
UPDATE v_arena_updatable
SET seating_capacity = 11000
WHERE arena_name = '嘉義市立棒球場';

-- 透過檢視執行 DELETE
DELETE FROM v_arena_updatable
WHERE arena_name = '嘉義市立棒球場';

WITH CHECK OPTION 子句可以確保透過檢視進行的修改不會建立不符合檢視條件的資料列。這是一個重要的資料完整性保護機制。

-- 建立具有 CHECK OPTION 的檢視
-- 此檢視只顯示台北的場館
CREATE VIEW v_taipei_arena AS
SELECT
    arena_id,
    arena_name,
    city,
    seating_capacity
FROM arena
WHERE city = '台北' AND is_deleted = FALSE
WITH CHECK OPTION;    -- 確保修改後的資料仍符合檢視條件

-- 以下操作會成功
INSERT INTO v_taipei_arena (arena_name, city, seating_capacity)
VALUES ('新台北體育館', '台北', 20000);

-- 以下操作會失敗，因為違反 CHECK OPTION
INSERT INTO v_taipei_arena (arena_name, city, seating_capacity)
VALUES ('高雄新場館', '高雄', 18000);
-- 錯誤訊息：CHECK OPTION failed

檢視是實現資料安全性控制的重要工具。透過只授予使用者對檢視的存取權限，而不授予對底層資料表的權限，我們可以實現細緻的資料存取控制。

-- 建立安全檢視：隱藏敏感資訊
-- 此檢視用於一般使用者，隱藏財務相關資訊
CREATE VIEW v_arena_public AS
SELECT
    arena_id,
    arena_name,
    city,
    -- 模糊化座位容量
    CASE
        WHEN seating_capacity >= 30000 THEN '30000以上'
        WHEN seating_capacity >= 20000 THEN '20000-29999'
        WHEN seating_capacity >= 10000 THEN '10000-19999'
        ELSE '10000以下'
    END AS capacity_range
FROM arena
WHERE is_deleted = FALSE;

-- 授予使用者只能存取檢視
-- GRANT SELECT ON v_arena_public TO 'general_user'@'localhost';

-- 建立管理員檢視：包含所有資訊
CREATE VIEW v_arena_admin AS
SELECT *
FROM arena;    -- 管理員可以看到所有欄位，包括已刪除的記錄

-- 授予管理員完整權限
-- GRANT ALL ON v_arena_admin TO 'admin_user'@'localhost';

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100

title 檢視安全性控制架構

actor "一般使用者" as user
actor "管理員" as admin

rectangle "檢視層" {
    rectangle "v_arena_public\n(有限資訊)" as vpublic
    rectangle "v_arena_admin\n(完整資訊)" as vadmin
}

rectangle "資料表層" {
    database "arena" as arena
}

user --> vpublic : SELECT
admin --> vadmin : ALL
vpublic --> arena
vadmin --> arena

note right of vpublic
    隱藏敏感欄位
    限制資料範圍
end note

note right of vadmin
    完整存取
    包含已刪除記錄
end note

@enduml

檢視管理與效能考量

管理檢視包括修改檢視定義、查看檢視資訊、以及刪除不需要的檢視。瞭解這些操作對於維護資料庫架構至關重要。

-- 修改檢視定義
-- 使用 ALTER VIEW 或 CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW v_arena_basic AS
SELECT
    arena_id,
    arena_name,
    city,
    seating_capacity,
    year_built    -- 新增 year_built 欄位
FROM arena
WHERE is_deleted = FALSE;

-- 查看檢視定義
SHOW CREATE VIEW v_arena_basic;

-- 查看所有檢視
SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

-- 刪除檢視
DROP VIEW IF EXISTS v_arena_basic;

-- 一次刪除多個檢視
DROP VIEW IF EXISTS v_arena_statistics, v_arena_display, v_arena_comparison;

雖然檢視提供了許多便利，但它們也有效能上的考量。因為檢視在每次查詢時都會重新執行定義的查詢語句，複雜的檢視可能導致效能問題。

-- 效能最佳化：避免巢狀檢視過深
-- 不建議的做法：多層巢狀檢視
CREATE VIEW v_level1 AS SELECT * FROM arena WHERE city = '台北';
CREATE VIEW v_level2 AS SELECT * FROM v_level1 WHERE seating_capacity > 10000;
CREATE VIEW v_level3 AS SELECT * FROM v_level2 WHERE year_built > 2000;
-- 查詢 v_level3 時，資料庫需要解析三層檢視

-- 建議的做法：扁平化檢視
CREATE VIEW v_taipei_large_new AS
SELECT *
FROM arena
WHERE city = '台北'
  AND seating_capacity > 10000
  AND year_built > 2000
  AND is_deleted = FALSE;

-- 使用 EXPLAIN 分析檢視查詢的執行計畫
EXPLAIN SELECT * FROM v_arena_complete WHERE city = '台北';

對於需要頻繁存取的複雜查詢，可以考慮使用物化檢視的概念。雖然 MySQL 原生不支援物化檢視，但可以透過資料表加上觸發器或排程工作來模擬。

-- 模擬物化檢視
-- 步驟一：建立儲存結果的資料表
CREATE TABLE mv_arena_statistics (
    city VARCHAR(50) PRIMARY KEY,
    arena_count INT,
    total_seats INT,
    avg_seats DECIMAL(10, 2),
    last_updated DATETIME
);

-- 步驟二：建立更新程序
DELIMITER //
CREATE PROCEDURE refresh_mv_arena_statistics()
BEGIN
    -- 清空並重新填入資料
    TRUNCATE TABLE mv_arena_statistics;

    INSERT INTO mv_arena_statistics (city, arena_count, total_seats, avg_seats, last_updated)
    SELECT
        city,
        COUNT(*) AS arena_count,
        SUM(seating_capacity) AS total_seats,
        AVG(seating_capacity) AS avg_seats,
        NOW()
    FROM arena
    WHERE is_deleted = FALSE
    GROUP BY city;
END //
DELIMITER ;

-- 步驟三：定期執行更新
CALL refresh_mv_arena_statistics();

-- 步驟四：建立觸發器自動更新（可選）
DELIMITER //
CREATE TRIGGER tr_arena_insert_mv
AFTER INSERT ON arena
FOR EACH ROW
BEGIN
    CALL refresh_mv_arena_statistics();
END //
DELIMITER ;

-- 查詢物化檢視
SELECT * FROM mv_arena_statistics ORDER BY total_seats DESC;

實務應用案例

讓我們透過一個完整的實務案例來整合本文所學的所有概念。假設我們正在開發一個場館管理系統，需要處理各種資料操作需求。

-- 實務案例：場館管理系統

-- 場景一：批次更新場館資訊
-- 因應新的安全法規，所有場館需要更新最大容納人數
START TRANSACTION;

-- 先備份原始資料
CREATE TABLE arena_backup AS SELECT * FROM arena;

-- 執行批次更新
UPDATE arena
SET seating_capacity = CASE
    WHEN year_built < 2000 THEN seating_capacity * 0.9   -- 舊場館減少 10%
    WHEN year_built < 2010 THEN seating_capacity * 0.95  -- 中期場館減少 5%
    ELSE seating_capacity                                  -- 新場館維持不變
END
WHERE is_deleted = FALSE;

-- 確認更新結果
SELECT arena_name, year_built, seating_capacity
FROM arena
WHERE is_deleted = FALSE
ORDER BY year_built;

COMMIT;

-- 場景二：條件式刪除過時記錄
-- 刪除超過 50 年且近 20 年未翻修的場館
START TRANSACTION;

-- 先確認要刪除的記錄
SELECT arena_name, year_built, last_renovation
FROM arena
WHERE YEAR(CURDATE()) - year_built > 50
  AND (last_renovation IS NULL OR YEAR(CURDATE()) - YEAR(last_renovation) > 20)
  AND is_deleted = FALSE;

-- 執行軟刪除
UPDATE arena
SET is_deleted = TRUE,
    deleted_at = NOW()
WHERE YEAR(CURDATE()) - year_built > 50
  AND (last_renovation IS NULL OR YEAR(CURDATE()) - YEAR(last_renovation) > 20)
  AND is_deleted = FALSE;

COMMIT;

-- 場景三：建立完整的報表檢視系統
-- 營運報表檢視
CREATE OR REPLACE VIEW v_operational_report AS
SELECT
    a.city,
    COUNT(DISTINCT a.arena_id) AS arena_count,
    SUM(a.seating_capacity) AS total_capacity,
    COUNT(e.event_id) AS event_count,
    COALESCE(SUM(e.expected_attendance), 0) AS total_attendance,
    COALESCE(SUM(e.ticket_price * e.expected_attendance), 0) AS total_revenue,
    ROUND(COALESCE(SUM(e.expected_attendance) / NULLIF(SUM(a.seating_capacity), 0) * 100, 0), 2) AS avg_occupancy
FROM arena a
LEFT JOIN event e ON a.arena_id = e.arena_id
WHERE a.is_deleted = FALSE
GROUP BY a.city;

-- 場館使用率排行檢視
CREATE OR REPLACE VIEW v_arena_utilization_rank AS
SELECT
    a.arena_name,
    a.city,
    a.seating_capacity,
    COUNT(e.event_id) AS event_count,
    COALESCE(AVG(e.expected_attendance / a.seating_capacity * 100), 0) AS avg_utilization,
    RANK() OVER (ORDER BY COUNT(e.event_id) DESC) AS event_rank,
    RANK() OVER (ORDER BY AVG(e.expected_attendance / a.seating_capacity * 100) DESC) AS utilization_rank
FROM arena a
LEFT JOIN event e ON a.arena_id = e.arena_id
WHERE a.is_deleted = FALSE
GROUP BY a.arena_id, a.arena_name, a.city, a.seating_capacity;

-- 查詢營運報表
SELECT * FROM v_operational_report ORDER BY total_revenue DESC;

-- 查詢使用率排行
SELECT * FROM v_arena_utilization_rank ORDER BY utilization_rank;

@startuml
!define DISABLE_LINK
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100

title 場館管理系統資料流程

rectangle "資料輸入" as input {
    card "新增場館" as add
    card "活動登記" as event
}

rectangle "資料處理" as process {
    card "UPDATE\n批次更新" as update
    card "DELETE\n軟刪除" as delete
    card "交易處理" as trans
}

rectangle "檢視層" as view {
    card "v_arena_public\n公開資訊" as vpub
    card "v_operational_report\n營運報表" as vop
    card "v_arena_utilization_rank\n使用排行" as vrank
}

rectangle "資料輸出" as output {
    card "前端應用" as frontend
    card "管理報表" as report
}

add --> update
event --> update
update --> trans
delete --> trans
trans --> vpub
trans --> vop
trans --> vrank
vpub --> frontend
vop --> report
vrank --> report

@enduml

最佳實踐與常見錯誤

在使用 UPDATE、DELETE 與檢視時，遵循最佳實踐可以避免許多常見錯誤並提升系統的可維護性。

-- 最佳實踐一：始終使用交易
-- 所有修改操作都應該在交易中執行
START TRANSACTION;

-- 修改操作
UPDATE arena SET seating_capacity = 20000 WHERE arena_id = 1;

-- 確認結果
SELECT * FROM arena WHERE arena_id = 1;

-- 提交或回滾
COMMIT;  -- 或 ROLLBACK;

-- 最佳實踐二：避免在 WHERE 子句中使用函式處理欄位
-- 不好的做法（無法使用索引）
UPDATE arena
SET seating_capacity = seating_capacity * 1.1
WHERE YEAR(last_renovation) = 2020;

-- 好的做法（可以使用索引）
UPDATE arena
SET seating_capacity = seating_capacity * 1.1
WHERE last_renovation >= '2020-01-01'
  AND last_renovation < '2021-01-01';

-- 最佳實踐三：更新前記錄原始值
-- 建立稽核日誌表
CREATE TABLE arena_audit_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    arena_id INT,
    field_name VARCHAR(50),
    old_value VARCHAR(255),
    new_value VARCHAR(255),
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50)
);

-- 使用觸發器自動記錄變更
DELIMITER //
CREATE TRIGGER tr_arena_update_audit
BEFORE UPDATE ON arena
FOR EACH ROW
BEGIN
    IF OLD.seating_capacity != NEW.seating_capacity THEN
        INSERT INTO arena_audit_log (arena_id, field_name, old_value, new_value, changed_by)
        VALUES (OLD.arena_id, 'seating_capacity', OLD.seating_capacity, NEW.seating_capacity, CURRENT_USER());
    END IF;
END //
DELIMITER ;

-- 最佳實踐四：檢視命名與文件化
-- 使用清楚的命名規範
CREATE VIEW v_rpt_monthly_revenue AS  -- v_ 前綴，rpt_ 表示報表
SELECT
    YEAR(e.event_date) AS year,
    MONTH(e.event_date) AS month,
    SUM(e.ticket_price * e.expected_attendance) AS revenue
FROM event e
GROUP BY YEAR(e.event_date), MONTH(e.event_date);

-- 加入註解說明
-- 檢視用途：產生月度營收報表
-- 建立者：DBA Team
-- 建立日期：2024-01-01
-- 最後修改：2024-06-15

-- 最佳實踐五：限制 DELETE 操作
-- 建立刪除前確認機制
DELIMITER //
CREATE TRIGGER tr_arena_before_delete
BEFORE DELETE ON arena
FOR EACH ROW
BEGIN
    -- 檢查是否有關聯的活動
    DECLARE event_count INT;
    SELECT COUNT(*) INTO event_count FROM event WHERE arena_id = OLD.arena_id;

    IF event_count > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '無法刪除：此場館仍有關聯的活動記錄';
    END IF;
END //
DELIMITER ;

常見錯誤與避免方式包括以下幾點。忘記 WHERE 子句是最常見且最危險的錯誤，會導致整個資料表的資料被修改或刪除。

-- 常見錯誤一：忘記 WHERE 子句
-- 危險操作
UPDATE arena SET seating_capacity = 15000;  -- 更新所有記錄！
DELETE FROM arena;  -- 刪除所有記錄！

-- 預防措施：設定 safe-updates 模式
SET SQL_SAFE_UPDATES = 1;  -- 啟用安全更新模式
-- 此模式下，UPDATE/DELETE 必須有 WHERE 子句且使用主鍵或索引欄位

-- 常見錯誤二：在檢視中引用已刪除的資料表
-- 如果 arena 表被刪除，相關檢視會失效
DROP TABLE arena;  -- 這會導致所有引用此表的檢視失效

-- 預防措施：刪除前檢查相依性
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE()
  AND VIEW_DEFINITION LIKE '%arena%';

-- 常見錯誤三：過度使用 SELECT * 在檢視中
-- 不好的做法
CREATE VIEW v_bad AS SELECT * FROM arena;
-- 如果 arena 表結構改變，檢視可能出現問題

-- 好的做法
CREATE VIEW v_good AS
SELECT arena_id, arena_name, city, seating_capacity
FROM arena;

-- 常見錯誤四：檢視效能問題
-- 避免在檢視中使用複雜的子查詢
-- 不建議
CREATE VIEW v_slow AS
SELECT *,
       (SELECT COUNT(*) FROM event WHERE arena_id = a.arena_id) AS event_count
FROM arena a;

-- 建議使用 JOIN
CREATE VIEW v_fast AS
SELECT a.*, COALESCE(e.event_count, 0) AS event_count
FROM arena a
LEFT JOIN (SELECT arena_id, COUNT(*) AS event_count FROM event GROUP BY arena_id) e
    ON a.arena_id = e.arena_id;

本文深入探討了 SQL 中 UPDATE 與 DELETE 語句的使用方式，從基本語法到進階技術，涵蓋了單欄位與多欄位更新、條件式更新、跨表更新、安全刪除、交易處理等核心概念。同時，我們也全面介紹了檢視的建立與應用，包括基本檢視、進階檢視、可更新檢視、以及檢視在安全性控制中的角色。

這些資料操作技術是資料庫開發的基石，熟練掌握它們不僅能提升開發效率，更能確保資料的完整性與安全性。透過遵循本文介紹的最佳實踐，開發者可以避免常見錯誤，建構出更可靠、更易於維護的資料庫系統。在實際應用中，建議始終在測試環境中先行驗證操作，並善用交易處理機制來保護生產資料，如此方能充分發揮 SQL 資料操作的強大能力。