C4W4 — 資料庫與 SQL
本週核心問題：資安分析師如何用 SQL 從資料庫中快速查詢、過濾和合併資料，以支援安全決策？
一、資料庫基礎：資料怎麼存、怎麼找？
1.1 資料庫 vs 試算表
| 面向 | 試算表（如 Google Sheets） | 資料庫 |
|---|---|---|
| 使用者 | 單人或小團隊 | 多人同時存取 |
| 資料量 | 較少 | 可儲存海量資料 |
| 處理能力 | 基本 | 可執行複雜查詢 |
1.2 關聯式資料庫
關聯式資料庫（relational database） = 包含多個彼此相關的表格的結構化資料庫。
表格結構：
| 概念 | 說明 | 類比 |
|---|---|---|
| 欄位（column / field） | 表格的直行，定義資料類型 | 表頭：employee_id、username、department |
| 列（row / record） | 一筆完整的資料紀錄 | 一位員工的所有資訊 |
1.3 主鍵與外鍵
| 鍵類型 | 定義 | 規則 | 數量限制 |
|---|---|---|---|
| 主鍵（primary key） | 每列都有唯一值的欄位 | 不可重複、不可為空 | 每個表格只有 1 個 |
| 外鍵（foreign key） | 在另一個表格中是主鍵的欄位 | 可重複、可為空 | 每個表格可有多個 |
實際範例
employees表的employee_id是主鍵（每位員工有唯一 ID），machines表的employee_id是外鍵（用來連結兩張表格）。
二、SQL 基礎：怎麼跟資料庫說話？
2.1 什麼是 SQL？
SQL（Structured Query Language） = 一種程式語言，用來建立、互動、查詢資料庫中的資料。
查詢（query） = 對資料庫表格提出的資料請求。
2.2 SQL 對資安分析師的價值
| 場景 | 說明 |
|---|---|
| 搜尋 log | 從百萬筆登入紀錄中找出可疑的登入嘗試 |
| 更新管理 | 找出還沒安裝最新修補程式的機器 |
| 維護排程 | 根據使用量最低的時段安排機器更新時間 |
| 資料驅動決策 | 用數據支持安全決策，向利害關係人報告 |
2.3 三種資料型別
| 型別 | 說明 | 範例 | 查詢時用引號？ |
|---|---|---|---|
| 字串（string） | 有序字元序列 | 'analyst10'、'USA' | 是，用單引號 |
| 數值（numeric） | 數字，可做數學運算 | 1000、503 | 否 |
| 日期時間（date and time） | 日期和/或時間 | '2023-01-15'、'18:00' | 是，用單引號 |
三、基本查詢：SELECT 與 FROM
3.1 核心語法
SELECT employee_id, device_id -- 要看哪些欄位
FROM employees; -- 從哪張表格查SQL 語法規則
- 關鍵字不區分大小寫（
SELECT=select），但慣例大寫方便閱讀- 表格名稱區分大小寫
- 每條語句以分號
;結尾- 多個欄位用逗號分隔
3.2 選取所有欄位
SELECT * -- * = 選取所有欄位
FROM employees;3.3 排序：ORDER BY
SELECT customerid, city, country
FROM customers
ORDER BY city; -- 預設升冪排序（A→Z、小→大）| 用法 | 說明 |
|---|---|
ORDER BY city | 升冪（預設）：A→Z |
ORDER BY city DESC | 降冪：Z→A |
ORDER BY country, city | 先按 country 排，相同 country 再按 city 排 |
四、過濾查詢：WHERE
4.1 基本過濾
SELECT *
FROM log_in_attempts
WHERE country = 'USA'; -- 只回傳 country 等於 USA 的紀錄4.2 比較運算子
| 運算子 | 意義 | 適用型別 | 範例 |
|---|---|---|---|
= | 等於 | 全部 | WHERE country = 'USA' |
> | 大於 / 晚於 | 數值、日期時間 | WHERE login_time > '18:00' |
< | 小於 / 早於 | 數值、日期時間 | WHERE login_date < '2023-01-15' |
>= | 大於等於 | 數值、日期時間 | WHERE birthdate >= '1970-01-01' |
<= | 小於等於 | 數值、日期時間 | |
<> 或 != | 不等於 | 全部 | WHERE country <> 'USA' |
>是排他運算子（不含邊界值），>=是包含運算子（含邊界值）
4.3 範圍過濾：BETWEEN
SELECT *
FROM machines
WHERE OS_patch_date BETWEEN '2021-03-01' AND '2021-09-01';
-- 回傳修補日期在 2021/3/1 到 2021/9/1 之間的機器（包含頭尾）
BETWEEN是包含運算子，頭尾日期都會被包含在結果中。
4.4 模式比對：LIKE 與萬用字元
當你不知道完整值、只知道部分模式時，用 LIKE 取代 =。
兩個萬用字元：
| 萬用字元 | 代表 | 範例 | 匹配結果 |
|---|---|---|---|
% | 零個或多個任意字元 | 'East%' | East-120、East-290 |
_ | 恰好一個任意字元 | 'N_' | NY、NV、NS |
-- 美國在資料庫中有 US 和 USA 兩種寫法，用 LIKE 一次抓到
SELECT *
FROM log_in_attempts
WHERE country LIKE 'US%';| 模式 | 匹配內容 |
|---|---|
'a%' | 以 a 開頭的任何字串 |
'%a' | 以 a 結尾的任何字串 |
'%a%' | 任何位置包含 a 的字串 |
'a_' | a 後面恰好一個字元 |
五、複合過濾：AND、OR、NOT
5.1 三個邏輯運算子
| 運算子 | 功能 | 回傳條件 |
|---|---|---|
AND | 同時滿足兩個條件 | 兩個條件都為真 |
OR | 滿足任一條件即可 | 至少一個條件為真 |
NOT | 否定條件 | 條件為假的紀錄 |
5.2 實際範例
-- AND：找同時使用 OS 1 且用 Email Client 1 的機器
SELECT *
FROM machines
WHERE operating_system = 'OS 1' AND email_client = 'Email Client 1';
-- OR：找使用 OS 1 或 OS 3 的機器（兩者都需要修補）
SELECT *
FROM machines
WHERE operating_system = 'OS 1' OR operating_system = 'OS 3';
-- NOT：找不是 OS 3 的所有機器
SELECT *
FROM machines
WHERE NOT operating_system = 'OS 3';OR 的寫法
即使兩個條件都是同一個欄位，也要完整寫出兩次欄位名稱。 ✅
WHERE department = 'Finance' OR department = 'Sales'❌WHERE department = 'Finance' OR 'Sales'
組合使用：
-- 排除美國和加拿大的客戶
SELECT *
FROM customers
WHERE NOT country = 'Canada' AND NOT country = 'USA';5.3 資安實戰範例
-- 找下班後（18:00 以後）的失敗登入嘗試
SELECT *
FROM log_in_attempts
WHERE login_time > '18:00' AND success = FALSE;
-- 找非墨西哥的登入嘗試（資料中有 MEX 和 MEXICO 兩種寫法）
SELECT *
FROM log_in_attempts
WHERE NOT country LIKE 'MEX%';六、合併表格：JOIN
6.1 為什麼需要 JOIN？
單張表格通常只有部分資訊。例如 employees 有員工資料，machines 有機器資料。用 JOIN 把兩張表格合併，才能看到「哪位員工用哪台機器」的完整圖片。
6.2 table.column 語法
當兩張表格有相同的欄位名稱時，必須用 表格名.欄位名 來指定要用哪張表格的欄位：
employees.employee_id -- employees 表的 employee_id
machines.employee_id -- machines 表的 employee_id
6.3 四種 JOIN
| JOIN 類型 | 回傳內容 | 不匹配的列 |
|---|---|---|
INNER JOIN | 只回傳兩張表格中匹配的列 | 丟棄 |
LEFT JOIN | 左表（第一張）全部 + 右表匹配的列 | 右表不匹配的欄位填 NULL |
RIGHT JOIN | 右表（第二張）全部 + 左表匹配的列 | 左表不匹配的欄位填 NULL |
FULL OUTER JOIN | 兩張表格全部的列 | 不匹配的欄位填 NULL |
NULL = SQL 中代表「缺失值」的特殊值。
6.4 JOIN 語法
-- INNER JOIN：只回傳兩邊都有匹配的紀錄
SELECT username, office, operating_system
FROM employees -- 左表
INNER JOIN machines -- 右表
ON employees.employee_id = machines.employee_id; -- 匹配條件
-- LEFT JOIN：回傳所有機器，即使沒有分配給員工
SELECT *
FROM machines
LEFT JOIN employees ON machines.device_id = employees.device_id;
-- RIGHT JOIN：回傳所有員工，即使沒有分配到機器
SELECT *
FROM machines
RIGHT JOIN employees ON machines.device_id = employees.device_id;LEFT JOIN 優先
實務上大多數分析師幾乎只用
LEFT JOIN，因為從左到右閱讀更直觀。FROM A LEFT JOIN B和FROM B RIGHT JOIN A結果完全相同，只要調換表格順序即可。
6.5 JOIN 三步驟口訣
- FROM：從哪張表格開始（左表）
- JOIN：加入哪張表格（右表）
- ON：用哪個欄位連接（
左表.欄位 = 右表.欄位）
七、SQL vs Linux 過濾比較
| 面向 | Linux | SQL |
|---|---|---|
| 用途 | 檔案系統中搜尋檔案、管理權限、管理程序 | 資料庫中查詢和操作結構化資料 |
| 結構 | 自由格式，輸出為純文字行 | 結構化，結果分欄清楚顯示 |
| 合併 | 無法跨檔案合併資料 | 可用 JOIN 合併多張表格 |
| 適用場景 | log 儲存為文字檔時 | 資料儲存在資料庫中時 |
從 Linux 存取 SQL
在命令列輸入
sqlite3可進入 SQLite，之後輸入的指令就是 SQL 語法而非 Linux 指令。
八、進階補充：聚合函數
聚合函數對多筆資料做計算，回傳單一結果值（不回傳原始資料）。
| 函數 | 功能 | 範例 |
|---|---|---|
COUNT() | 計算列數 | SELECT COUNT(firstname) FROM customers; |
AVG() | 計算平均值 | SELECT AVG(salary) FROM employees; |
SUM() | 計算總和 | SELECT SUM(total) FROM invoices; |
-- 計算來自美國的客戶數量
SELECT COUNT(firstname)
FROM customers
WHERE country = 'USA';九、術語速查表
| 術語 | 定義 |
|---|---|
| 資料庫（Database） | 有組織的資料集合 |
| 日期時間資料（Date and time data） | 代表日期和/或時間的資料 |
| 排他運算子（Exclusive operator） | 不包含比較值的運算子（如 >） |
| 過濾（Filtering） | 選取符合特定條件的資料 |
| 外鍵（Foreign key） | 在另一個表格中是主鍵的欄位 |
| 包含運算子（Inclusive operator） | 包含比較值的運算子（如 >=） |
| 記錄檔（Log） | 組織系統中發生的事件紀錄 |
| 數值資料（Numeric data） | 由數字組成的資料 |
| 運算子（Operator） | 代表運算的符號或關鍵字 |
| 主鍵（Primary key） | 每列都有唯一值的欄位 |
| 查詢（Query） | 對資料庫表格的資料請求 |
| 關聯式資料庫（Relational database） | 包含彼此相關表格的結構化資料庫 |
| 字串資料（String data） | 由有序字元序列組成的資料 |
| SQL（結構化查詢語言） | 用來建立、互動、查詢資料庫的程式語言 |
| 語法（Syntax） | 決定程式語言中什麼是正確結構的規則 |
| 萬用字元（Wildcard） | 可替代任何其他字元的特殊字元 |