114 身心障礙特考三等 資訊處理類科 資料庫應用 考古題詳解

核心觀念

外鍵（Foreign Key, FK）用來確保資料表之間的參照完整性：子表中的外鍵值必須對應到父表中存在的主鍵（Primary Key, PK）值，或在外鍵允許空值時為 NULL。若刪除父表的一筆資料，而子表仍有資料參照它，就必須決定如何處理這些子表紀錄。

ON DELETE 設定常見的三種處理方式為 CASCADE、SET NULL 與 RESTRICT（部分資料庫以 NO ACTION 表示或支援此設定）。

解題方法

以「刪除一筆被參照的父表紀錄」為共同情境，逐一比較子表紀錄會如何變化，以及刪除是否能完成。如此即可掌握三種設定對資料完整性與刪除操作的影響。

例如，訂單 表中的 customer_id 外鍵參照 客戶 表的 customer_id。若刪除某位仍有訂單的客戶：

三種方式的差異

1. CASCADE：連鎖刪除

刪除父表紀錄時，資料庫也會自動刪除所有參照該筆父表紀錄的子表紀錄。

• 優點： 可自動清除不再具有父資料依據的子資料，避免留下孤兒紀錄。
• 影響與風險： 一次刪除可能連帶移除多筆子表資料，影響範圍較大；若多層資料表都採用連鎖刪除，刪除效果會沿關聯繼續傳遞。
• 適用情境： 子資料的存在完全依附於父資料，例如刪除訂單時一併刪除訂單明細。

2. SET NULL：外鍵設為空值

刪除父表紀錄時，保留子表紀錄，並將子表中參照該父資料的外鍵欄位設為 NULL。

核心觀念

本題考查 MySQL 各類日誌的記錄對象與管理目的。作答時可依用途分成四組：資料復原、伺服器診斷、查詢效能分析，以及複寫、安全與排程管理。

題目中的「交易日誌」通常指 InnoDB 的 Redo Log 與 Undo Log；「複寫日誌」主要指 Binary Log（binlog）與 Relay Log。「事件日誌」則指 MySQL Event Scheduler 的排程事件執行紀錄，應與 Windows 的系統事件記錄區分。

解題方法

逐一說明每種日誌的「記錄什麼」及「用來做什麼」，並特別分清容易混淆的項目：

• General Query Log：記錄伺服器收到哪些連線與 SQL，側重追蹤用戶端送出的內容。
• Slow Query Log：記錄符合慢查詢條件的 SQL，側重效能診斷。
• Binary Log：記錄資料變更事件，側重複寫與時間點還原。
• Redo／Undo Log：支援交易復原與一致性，屬儲存引擎層級的交易處理機制。

各類日誌用途

1. 交易日誌（Transaction Log）

用來支援交易的可靠性、復原與一致性。以 InnoDB 為例，主要包括：

• Redo Log（重做日誌）：記錄資料頁變更資訊。伺服器發生異常中止後，可在復原時重做尚未完整寫入資料檔的變更，以恢復已提交交易的結果。
• Undo Log（復原日誌）：保存變更前的資料資訊，供交易回滾使用，也支援一致性讀取，讓交易能讀取符合其視圖的舊資料版本。

簡言之，Redo Log 著重「重做已提交的變更」，Undo Log 著重「撤銷未完成的變更及提供舊版本」。

2. 錯誤日誌（Error Log）

記錄 MySQL 伺服器啟動、運作與關閉期間的重要訊息，例如啟動或關閉狀態、錯誤、警告，以及部分診斷資訊。

伺服器無法啟動、連線或儲存引擎發生錯誤時，錯誤日誌是管理者排查問題的重要依據。

3. 查詢日誌（Query Log／General Query Log）

記錄用戶端連線與中斷，以及伺服器收到的 SQL 陳述式，可用來追蹤用戶端實際送了哪些指令，或協助診斷應用程式的操作問題。

此日誌偏向「收到什麼指令」；它不是專門記錄慢查詢的工具，啟用時也可能產生大量紀錄。

4. 慢查詢日誌（Slow Query Log）

記錄執行時間超過設定門檻，或符合其他慢查詢條件的 SQL。管理者可用它找出耗時查詢，再檢視執行計畫、索引與 SQL 寫法，進行效能調校。

核心觀念

本題考查：

1. GROUP BY 分組彙總。
2. PARTITION BY 視窗函數分割資料。
3. 聚合函數 AVG 的計算方式。
4. 分組後資料筆數與原始資料筆數的差異。

兩者雖然都能依 depname 分組計算平均薪資，但執行結果不同：

• GROUP BY 會將同一群組的資料合併成一筆。
• PARTITION BY 只是在視窗函數中劃分計算範圍，仍保留每一筆原始資料。

題目第二段 SQL 的正確寫法應為：

SELECT depname, empno,
 AVG(salary) OVER (PARTITION BY depname) AS avg_salary
FROM empsalary;

解題方法

先依 depname 將資料分組，再分別計算各部門薪資總和與筆數：

1. develop 部門

薪資為：

共有 5 人，因此：

2. hr 部門

薪資為：

共有 2 人，因此：

3. sales 部門

薪資為：

共有 3 人，因此：

GROUP BY 的輸出結果

SQL：

SELECT depname, AVG(salary) AS avg_salary
FROM empsalary
GROUP BY depname;

輸出每個部門一筆資料：

原始資料共有 10 筆，分組後只有 3 筆。

原因是 GROUP BY depname 會把相同部門的資料集合成一組，並以聚合函數計算每組結果。由於 empno 沒有出現在 GROUP BY 中，也沒有搭配聚合函數，因此不能直接列入此查詢的 SELECT 清單。

PARTITION BY 的輸出結果

SQL：

SELECT depname, empno,
 AVG(salary) OVER (PARTITION BY depname) AS avg_salary
FROM empsalary;

輸出仍保留原始 10 筆資料，但每筆資料會附上所屬部門的平均薪資：

核心觀念

本題考查 SQL Server 備份類型的差異，以及如何依資料重要性、可接受的資料損失時間與復原時間，安排備份週期。

• 完整備份（Full backup）：備份資料庫中的資料，並包含足以還原該備份所需的交易紀錄。它是資料庫還原的基礎，也可作為差異備份的基準。
• 差異備份（Differential backup）：備份自最近一次完整備份後變更的資料。每次差異備份都以同一份完整備份為基準，內容會隨時間累積；新的完整備份建立後，差異基準也隨之更新。
• 交易紀錄備份（Transaction log backup）：備份自上一次交易紀錄備份以來的交易紀錄，可用於依序重播交易，將資料庫還原到特定時間點。使用此備份須採用 完整復原模式（Full recovery model）或大量記錄復原模式（Bulk-logged recovery model）；簡單復原模式（Simple recovery model）不支援交易紀錄備份。

解題方法

可依「還原時需要哪些備份」及「能接受多少資料損失」來規劃：

1. 先做完整備份，建立可還原的基準。
2. 定期做差異備份，縮短還原時套用變更的範圍。還原時只需使用最近一次完整備份，以及其後最新的一份差異備份。
3. 若需降低資料損失或支援時間點還原，定期做交易紀錄備份。還原時，在完整備份與差異備份之後，依時間順序套用後續交易紀錄備份。