面試時經常會問 : 「你會某某技能嗎?」
這是一個爛問法，讓面試者很難回答，因為什麼情況才算會? 因此今天要來談一下 : 能夠回答哪些問題，表示你懂MySQL?
如果把MySQL的知識拆開為各個類別，大概可以分成以下幾類 :
(1) MySQL資料庫的基本概念
(2) MySQL資料庫/資料表設計與正規化
(3) MySQL基礎語法與資料型態
(4) MySQL進階查詢
(5) MySQL索引與效能調校
(6) MySQL上鎖與交易
(7) MySQL程式邏輯與進階功能
(8) MySQL管理與備份
(9) MySQL整合與應用
以上這些類別的知識也有淺有深，我們把各個類別的知識從淺到深列出來探討。
1-1 什麼是資料庫? 資料庫有哪些類型? MySQL屬於哪類? 各類型資料庫有何優缺點?
深入內容可以參考這篇"資料庫 vs 資料庫管理系統"。
資料庫的定義 :
資料庫是一個有組織、有結構的電子化資料集合，可以想像成一個專門用來存放和管理資料的電子倉庫。這個「倉庫」的設計目的，是為了讓使用者能夠方便、快速且安全地存取、管理、更新和檢索其中的資料。 與其將資料隨意地存放在個別檔案中（例如 Excel 或純文字檔），資料庫提供了一套更有效率的系統化方法來處理大量資料，確保資料的一致性、完整性和安全性。
資料庫有以下幾種類型 :
關聯式資料庫 (Relational Database)、物件導向資料庫 (Object-Oriented Database)、NoSQL 資料庫 (Non-Relational Database)、階層式資料庫 (Hierarchical Database)、網路式資料庫 (Network Database)等，MySQL就屬於關聯式資料庫。
關聯式資料庫
關聯式資料庫的優點是
1. 結構清晰：以表格儲存，易於理解和維護。
2. 資料一致性高：透過 ACID 原則（詳見後述）確保交易的完整性。
3. 標準化：擁有標準的查詢語言 SQL。
4. 應用廣泛：技術成熟，社群和商業支援豐富。
關聯式資料庫的缺點是
1. 擴展性較差：在面對超大規模資料時，垂直擴展（提升單機性能）成本高，水平擴展（增加伺服器數量）較複雜。
2. 彈性較低：新增或修改欄位需要變更表格結構 (Schema)，較不靈活。
3. 對非結構化資料不友善：不適合儲存文件、圖片、影片等。
就擴展性來說，原本一個資料庫在一台伺服器上，如果因為負載太大，想要把它分散負載的話，並不是直接增加伺服器就好，因為還需要考慮資料的一致性問題，因此就必須使用MySQL Cluster架構。
物件導向資料庫
物件導向資料庫是一種能夠將物件導向程式語言中的資料結構（如物件、類別、繼承）直接儲存與管理的資料庫系統。它結合了物件導向程式設計的概念與資料庫儲存功能，讓資料可用「物件」的方式來存取與操作。
物件導向資料庫的優點是
1. 物件與資料結構一致 : 程式中的物件可直接儲存在資料庫中，不需要進行資料轉換或物件關聯對應（ORM），開發更直觀。
2. 支援物件導向特性（繼承、多型、封裝）: 可以建立物件階層、子類別繼承父類別，方便複雜資料結構的建模與維護。
3. 處理複雜資料結構效能佳 : 對於包含巢狀物件、陣列、集合等結構的應用，比關聯式資料庫處理更有效率。
4. 適合儲存大型物件與多媒體資料 : 如圖片、聲音、影片、二進位檔案，能自然地包裝成物件處理。
5. 與程式語言整合性高 : 支援 Java、C++、C# 等語言，開發時無需額外定義資料結構，開發流程簡化。
物件導向資料庫的缺點是
1. 與傳統系統相容性低 : OODB 與使用 SQL 的關聯式資料庫系統不相容，資料與應用難以整合。
2. 缺乏統一的查詢語言 : 沒有像 SQL 這樣廣泛標準的查詢語言，各系統語法不同，學習門檻較高。
3. 使用族群與資源少 : 相對於 MySQL、PostgreSQL 等主流資料庫，物件導向資料庫的社群小、文件與工具支援少。
4. 開發與維護成本高 : 開發者需具備物件導向程式設計與資料庫兩種技能，且錯誤除錯較不容易。
5. 資料遷移困難 : 資料格式專屬於特定系統，若需轉換為關聯式或 NoSQL 資料庫，成本與風險都偏高。
NoSQL 資料庫
NoSQL 是一種非關聯式資料庫的統稱，強調可擴展性、彈性與高效能，常見的資料儲存方式包括文件（Document）、鍵值（Key-Value）、欄狀（Column-Family）、圖形（Graph）等。它不是以傳統表格欄位的方式儲存資料，而是根據資料使用情境設計更彈性的格式。
常見的 NoSQL 資料庫有：MongoDB（文件型）、Redis（鍵值型）、Cassandra（欄狀型）、Neo4j（圖形型）等。
NoSQL 的優點是
1. 彈性資料結構 : 不需要固定的資料表結構，適合儲存非結構化或半結構化資料，如 JSON、XML 等。可以隨時調整欄位，無需修改整張表格結構。
2. 高擴展性與高可用性 : 大多數 NoSQL 系統原生支援分散式架構，可輕鬆水平擴充至多台伺服器，適合處理大數據與高併發讀寫。
3. 效能表現佳 : 因為資料儲存方式簡化、查詢針對特定用途最佳化，NoSQL 在某些讀寫密集應用中比關聯式資料庫表現更快。
4. 開發速度快 : 開發者可直接將應用程式中的物件或資料格式（如 JSON）儲存進資料庫，不需複雜的轉換或正規化過程。
5. 適合特定應用場景 : 如即時訊息串（聊天紀錄）、使用者行為追蹤、設定檔儲存、快取系統等，NoSQL 提供快速又具彈性的解法。
NoSQL 的缺點是
1. 缺乏結構完整性保障 : 沒有資料表的外鍵與限制約束，導致資料一致性與完整性需由應用程式層自行處理。
2. 不具備標準查詢語言 : 各家 NoSQL 系統都有自家的 API 或查詢語法，缺乏像 SQL 那樣的統一語言，學習與維護成本偏高。
3. 事務支援有限 : 多數 NoSQL 資料庫不支援傳統關聯式資料庫的 ACID 交易特性，僅提供「最終一致性」，不適合金融、交易等高度正確性的應用。
4. 資料關聯處理能力弱 : 雖可儲存複雜結構，但進行多筆資料之間的關聯查詢（如 Join）較困難且效能差，需依靠程式邏輯補足。
5. 資料備份與遷移機制不成熟 : 相較於成熟的關聯式資料庫，NoSQL 在資料備份、同步、災難復原等企業級功能上仍較弱。
舉個最簡單的例子，銀行系統的資料庫適合使用關聯式資料庫，而臉書社群媒體部分功能就適合使用NoSQL資料庫。因為銀行系統任何時間都必須百分百完全正確，而臉書的按讚數並不需要隨時百分百正確，只需不要差太多，在最終某個時間點進行同步就可以了。
1-2 什麼是關聯式資料庫管理系統?
深入內容可以參考這篇"What is RDBMS?"。
關聯式資料庫是一種以「表格（table）」為基本結構的資料儲存方式。每張表格由「列（row）」與「欄（column）」組成，每一列是一筆資料，每一欄是一個資料欄位。 「關聯式」的意思是：不同表格之間可以透過「鍵值」建立關聯，例如顧客與訂單、學生與課程等關係。 關聯式資料庫的設計強調資料一致性與正規化，能避免重複資料並確保資料邏輯正確。
關聯式資料庫管理系統，簡稱 RDBMS，是一套用來建立、管理、查詢與維護關聯式資料庫的軟體系統。
1-3 使用MySQL你用過哪些工具或是軟體?
以下是使用 MySQL 時常見的工具與軟體，依用途與功能整理說明：
1. phpMyAdmin : 以 PHP 撰寫的網頁管理介面，可透過瀏覽器操作 MySQL，建立資料表、執行 SQL 查詢、備份匯入資料，適合初學者與管理後台。
2. MySQL Workbench : MySQL 官方提供的圖形化工具，支援 ER 圖設計、查詢撰寫、資料庫模型建構、伺服器監控，適合進階使用者與資料庫設計。
3. HeidiSQL : 免費的輕量級視覺化工具（支援 MySQL、MariaDB、PostgreSQL），功能包含資料瀏覽、查詢、匯出、同步資料表，執行速度快、操作介面直覺。
4. XAMPP / MAMP / WAMP : 整合式開發環境，包含 Apache、MySQL、PHP 等，安裝後即可模擬本機伺服器環境，適合開發 PHP + MySQL 網站，XAMPP 支援 Windows/Linux/Mac，MAMP 主要針對 Mac。關於XAMPP，可以參考這篇"使用XAMPP練習MySQL/MariaDB操作"。
1-4 說明MySQL 與 MariaDB、PostgreSQL 的差異。
MySQL、MariaDB 和 PostgreSQL 都是開源的關聯式資料庫管理系統（RDBMS），它們在設計理念、授權模式、功能支援上各有不同。
MySQL 是由 Oracle 擁有的資料庫系統，它廣泛應用於網站與應用程式的開發中，優點是容易上手、文件豐富、社群龐大。MySQL 預設使用 InnoDB 儲存引擎，支援 ACID、事務與外鍵，並透過 mysqli 或 PDO 等方式與 PHP 整合良好。但由於 MySQL 屬於 Oracle 旗下產品，它的新功能更新速度受限於 Oracle 的策略，部分進階功能需透過企業版才能使用。
MariaDB 是由 MySQL 的原始開發者在 MySQL 被 Oracle 收購後所創立的分支版本，目的是保持真正開源的資料庫選項。MariaDB 保持與 MySQL 高度相容，許多語法與工具可直接共用，但它在開放性與創新方面較為積極，增加了一些 MySQL 沒有的儲存引擎，例如 Aria、ColumnStore，也對查詢優化與多版本控制有較強的支援，更新頻率快且完全社群主導，授權方式比較自由。
PostgreSQL 則是一套以標準 SQL 為基礎、追求功能完整性與擴充性的資料庫系統。它支援 JSONB、全文檢索、GIS（地理資訊系統）等進階功能，並且允許自訂資料型別與函數，是屬於企業級用途的開源資料庫。PostgreSQL 在事務處理、並行控制（如 MVCC）、資料完整性驗證等方面表現優異，非常適合需要強資料一致性或複雜查詢邏輯的系統。與 MySQL 相比，它的學習曲線略高，但提供的功能更強大。
PostgreSQL有些特別的地方是，他也具有部分物件導向功能。例如繼承（Table Inheritance）、多型（Polymorphism）、複合型別（Composite Types）與自訂型別（User-defined Types）、陣列（Array）與 JSONB 支援、可擴充性（Extensibility）。
總結來說，如果你需要穩定、簡單且支援廣泛的資料庫，MySQL 是不錯的選擇；如果你希望有更多控制權、享受開源社群快速更新的好處，MariaDB 是理想的替代品；若你專注在資料一致性、複雜查詢、進階功能（如 JSON、地理資料）上，PostgreSQL 則是較合適的選擇。
1-5 如果中小企業想使用資料庫，你會建議使用哪種資料庫? 為什麼？
對於中小企業來說，我通常會建議使用MariaDB，原因如下：
一、完全開源、沒有授權疑慮
MariaDB 是由原本 MySQL 的創辦人主導開發的分支，擁有 GPL 授權（General Public License），企業使用上不需要擔心未來因為商業授權費或功能限制導致成本增加，相對 MySQL（被 Oracle 收購後，企業版與社群版的差異越來越大）更具保障。
二、與 MySQL 高度相容、容易上手
MariaDB 幾乎與 MySQL 完全相容，無論是資料庫語法、工具（像是 phpMyAdmin、HeidiSQL）、開發環境（像 PHP + mysqli），都可以無縫轉換，對初期沒有資料庫經驗的中小企業來說學習門檻低。
三、效能與功能優勢
MariaDB 在某些查詢優化上比 MySQL 表現更好，內建多種儲存引擎選項（像是 Aria、XtraDB、ColumnStore），適合中小企業因應未來規模擴張或多樣資料需求。
四、活躍的社群與快速更新
MariaDB 的開發完全由社群主導，更新速度快，能快速支援最新的安全性修補與新功能。對於資源有限、沒辦法自行維護複雜系統的中小企業而言，這點很重要。
五、支援容器化與雲端部署
MariaDB 支援 Docker、Kubernetes 等現代化部署方式，也能無痛部署於 AWS、Azure 等雲端平台，非常適合想降低硬體維運成本的企業使用。
不過，如果這家中小企業的應用非常簡單，或有特定 SaaS 服務（例如 WordPress、ERP 等）內建 MySQL，也可以直接使用 MySQL，因為主流工具普遍都支援；若企業需求涉及大量商業邏輯、自訂函數、地理資訊或 API 查詢，則 PostgreSQL 可能更適合，只是需要投入更多學習資源。 簡言之： 中小企業若想要平衡「成本、穩定、擴充性、相容性」這四個面向，MariaDB 是最推薦的選擇。
1-6 你知道的儲存引擎有哪些? 各個儲存引擎差異是什麼?
在 MySQL 和 MariaDB 中，儲存引擎（Storage Engine）負責處理資料在磁碟上的實體儲存方式與存取機制，不同儲存引擎會影響資料的效能、交易支援、鎖定方式、全文搜尋、索引策略等。
以下是常見的幾種儲存引擎與其差異：
InnoDB 用途最廣、現代 MySQL/MariaDB 的預設引擎，支援ACID 交易，支援外鍵（Foreign Keys），使用行級鎖定（Row-level Locking），支援MVCC（多版本並行控制），效能穩定、可靠性高，適合需要高一致性與交易的系統，如訂單、財務資料。
MyISAM是MySQL 早期的預設引擎，現在已經不建議使用，不支援交易，不支援外鍵，使用表級鎖定（Table-level Locking），查詢速度快，適合讀多寫少的場景，容易因異常關機或斷電導致資料損壞。
MEMORY（舊稱 HEAP）是儲存在記憶體中的資料表，重啟後資料會消失，非常快，因為資料存在記憶體中，不支援交易，適合做暫存資料、快取查詢結果，資料表大小受限於 RAM 容量。
CSV 每個資料表對應一個 CSV 檔案，方便與其他程式或資料進行資料交換，無索引、不支援交易，寫入與查詢效能低，通常用於匯入匯出用途，不適合作為應用系統用的資料表。
ARCHIVE適合儲存大量壓縮的歷史資料，不支援索引（除了 AUTO\_INCREMENT），支援 INSERT 與 SELECT，但不支援 DELETE 或 UPDATE，使用 zlib 壓縮儲存，節省磁碟空間，適合用來儲存審計、歷史日誌資料等不會更動的資料。
差異重點總結： 若重視交易與安全性，選 InnoDB ，若需要讀取速度且寫入不頻繁，舊系統可能還在用 MyISAM ，若處理大量歷史資料，可選 ARCHIVE，若要支援快取或暫存表，考慮 MEMORY。不過，InnoDB 是絕大多數應用的首選，除非你有特定的應用場景，才會使用其他儲存引擎。
關於儲存引擎，可以再參考這篇"MySQL資料庫引擎InnoDB與MyISAM有何差異?"。
1-7 哪種儲存引擎容易發生損毀? 原因是什麼?
在 MySQL 中，最容易發生損毀（corruption）的是 MyISAM 儲存引擎。
為什麼是 MyISAM 容易損毀？ 主要原因有以下幾點：
一、不支援交易與自動復原
MyISAM 不支援交易，也不支援 crash recovery（崩潰復原）機制。當資料庫在寫入過程中突然中斷（例如斷電、系統當機、硬體故障），MyISAM 無法保證資料一致性，也無法自動將資料復原至正確狀態。
二、使用表級鎖定
MyISAM 採用「表級鎖定（table-level locking）」，若在高併發環境中有大量讀寫操作，有可能導致寫入衝突或卡住，進一步引發結構錯亂或資料寫入異常。
三、索引與資料分開儲存
MyISAM 將資料（`.MYD`）與索引（`.MYI`）分成兩個獨立檔案儲存，一旦有一個檔案出現同步問題（例如只寫入資料、索引未同步），就容易發生檔案毀損。
四、沒有完整的寫入保護機制
與 InnoDB 的 redo log、undo log 相比，MyISAM 寫入時缺乏可靠的日誌保護，尤其在多筆更新時，只要過程中中斷，很容易讓資料處於「半寫入」狀態，導致損毀。
五、檢查與修復需手動進行
當 MyISAM 資料損毀時，必須使用 `myisamchk` 或 `REPAIR TABLE` 等方式手動修復，不像 InnoDB 會在啟動時自動復原未完成的交易。
結論與建議： 由於 MyISAM 的資料安全性與穩定性不足，現代 MySQL 開發建議全面改用 InnoDB。InnoDB 支援事務、行級鎖定、自動復原，能大幅降低資料損毀風險。 除非你真的非常需要 MyISAM 的特定功能（例如快速全文搜尋，且資料可丟棄），否則不建議繼續使用 MyISAM 作為正式系統的儲存引擎。
1-8 說明字元集與排序原則，對於資料庫的維護有何重要性?
在 MySQL 資料庫中，字元集（Character Set）與排序原則（Collation）對於儲存、搜尋與比較文字資料扮演非常重要的角色。若設定錯誤，不僅會導致文字亂碼，還可能影響資料查詢結果的正確性與效能，對資料庫的維護與國際化更是關鍵。
什麼是字元集（Character Set）？ 字元集指的是「資料中可以使用哪些字」，例如：
`utf8mb4`：完整支援 Unicode，包括表情符號、亞洲文字
`latin1`：僅支援西歐語系
`big5`：繁體中文編碼（主要用於舊系統）
`utf8`（MySQL 特有）：其實只支援三個位元組，無法完整支援所有 Unicode 字元（如 emoji） 選擇錯誤的字元集可能導致儲存資料時發生截斷、亂碼或無法寫入。
什麼是排序原則（Collation）？ 排序原則決定了「文字比較與排序的規則」，例如：
是否區分大小寫？（`utf8mb4_general_ci` 不區分、`utf8mb4_bin` 區分）
是否區分音調、符號？（`ci` 表示 case-insensitive）
同樣是 `utf8mb4`，可以搭配不同排序原則，例如：
`utf8mb4_unicode_ci`：以 Unicode 標準比對，支援多語言
`utf8mb4_general_ci`：速度較快，但精確度較低
`utf8mb4_bin`：以位元組精準比對，也就是依照二進位數值進行比對。
排序原則會直接影響 `WHERE`, `ORDER BY`, `GROUP BY` 等語法的結果。
對資料庫維護的重要性
1. 避免亂碼與編碼錯誤
若資料表使用的字元集與資料來源（如網頁、API）不一致，儲存時會產生亂碼或資料截斷。例如：使用 `utf8`（非 `utf8mb4`）來儲存包含 emoji 的訊息會失敗。
2. 影響搜尋與比較行為
舉例來說，`WHERE name = 'Apple'` 在 `utf8mb4_general_ci` 不會區分大小寫，`apple` 也會被查到，但在 `utf8mb4_bin` 下就不會。這會影響應用程式邏輯與使用者體驗。
3. 影響排序結果
不同排序原則在多語言情境下排序方式不同，例如西班牙文中的 ñ 與 n，或者中文筆劃排序，都會受到 collation 的影響。
4. 影響 JOIN 與索引效能
若兩張表在 JOIN 時使用不同的字元集或排序原則，MySQL 會無法使用索引，甚至產生「Illegal mix of collations」錯誤，導致效能下降或無法執行。
5. 國際化與多語系支援
若未採用通用字元集（如 utf8mb4），將來系統要支援日文、韓文、emoji 等內容時，可能需要大量重建資料表、轉碼與測試，造成維護負擔。
實務建議
預設使用 `utf8mb4` 作為字元集，搭配 `utf8mb4_unicode_ci` 或 `utf8mb4_general_ci`
網頁、程式、資料庫連線（如 PHP `mysqli`）皆設定為同一編碼（例如 `utf8mb4`），以避免亂碼
若需要精確區分（如密碼、token）請使用 `utf8mb4_bin`
新系統建議從頭就統一字元集與排序原則，避免日後遷移麻煩 。
總結來說：字元集與排序原則是資料庫設計中不可忽視的底層設定，它們會深遠地影響資料的正確性、搜尋邏輯、語系相容性與效能，是資料庫穩定維運與擴充的基礎。
1-9 ACID是什麼? 請舉實際例子說明。
ACID 是資料庫交易（Transaction）中非常重要的四大特性，它們分別代表：
A：原子性（Atomicity）
C：一致性（Consistency）
I：隔離性（Isolation）
D：持久性（Durability）
這四個特性確保資料在交易過程中能保持正確、可靠、不會因系統故障或並行操作而損毀。
以下用一個「網購轉帳付款」的例子來說明：
假設一位顧客在網路商店下訂單後，需要從自己的帳戶扣款並將金額轉入商家的帳戶，這整個過程應視為一個「交易」。
A：原子性（Atomicity） 意思是「要嘛全做，要嘛全不做」，不可只做一半。
▶️ 實例： 顧客從帳戶扣款 1000 元，但因為系統錯誤導致商家帳戶沒有收到這筆錢，這是不被允許的。原子性會保證： * 若扣款成功但匯入失敗，就會回滾（ROLLBACK），顧客帳戶恢復原本金額 * 整個交易要同時完成才能成立
C：一致性（Consistency） 意思是「交易前後，資料都必須符合資料庫的規則與邏輯」。
▶️ 實例： 顧客帳戶原有 5000 元，商家帳戶有 10000 元。轉帳後顧客變成 4000 元，商家變成 11000 元，總金額仍為 15000 元。這表示資料在交易後仍維持正確邏輯。 如果轉完帳總額變成 14900 或 16000，代表出錯了，就違反一致性。
I：隔離性（Isolation） 意思是「每筆交易在執行過程中，彼此互不干擾」。
▶️ 實例： 顧客與商家同時在存取帳戶資訊，系統要能保證其中一方的交易完成後，另一方才能看到正確資料，否則可能造成錯誤查詢或重複扣款。 不同的隔離等級（如 READ COMMITTED、REPEATABLE READ）會有不同的處理方式，避免出現「髒讀、不可重複讀、幻影讀」等問題。
D：持久性（Durability） 意思是「一旦交易完成，就算斷電也不會消失」。
▶️ 實例： 當顧客轉帳成功，資料庫即便在 1 秒後當機，重新啟動後，顧客帳戶餘額仍應正確顯示為 4000 元，商家為 11000 元。這是因為交易會寫入永久性儲存（如日誌、磁碟）中，即便系統異常也不會遺失。
總結一句話說： ACID 就像是資料庫交易的四大保護罩，讓我們在儲存與操作重要資料時，不會因為突發事件、同時操作、或程式錯誤而導致資料錯亂或遺失。 所以在設計財務系統、電商系統、訂單系統等對資料正確性要求極高的應用時，選擇支援 ACID 的儲存引擎（如 InnoDB）非常關鍵。
1-10 關聯式資料庫系統與 NoSQL 資料庫的差異是什麼? 哪種適合處理社群平台的訊息串資料？為什麼？
關聯式資料庫系統與 NoSQL 資料庫的核心差異在於資料的結構方式與設計理念不同，這影響了它們各自適合的應用場景。
關聯式資料庫系統，像是 MySQL 或 PostgreSQL，會將資料存放在結構化的資料表中，每筆資料都是一列（row），每個欄位都有固定的資料型別與約束。這種設計非常適合處理結構明確、規則一致、彼此有邏輯關聯的資料，例如會員資料、訂單紀錄、財務帳目等。它強調資料一致性與完整性，支援 ACID 特性，確保每次交易都正確無誤。
而 NoSQL 資料庫，例如 MongoDB、Cassandra 或 Redis，則以彈性與效能為優先。它不強制資料結構，允許每筆資料以不同格式存在，像是一筆留言資料可以包含文字、圖片、標籤、回覆、讚數等，結構可以根據需要動態調整。NoSQL 資料庫特別適合儲存大量非結構化或半結構化的資料，支援高併發、大規模寫入、快速讀取，並且可透過水平擴充方式提升效能。
如果要處理社群平台的訊息串資料，會建議選擇 NoSQL 資料庫。原因是社群訊息具有高頻率新增與查詢、結構彈性、格式多樣、內容變化快等特性。
例如一則貼文可能包含多段留言，每段留言下還有回覆，使用者還能按讚、分享、標註朋友，甚至上傳圖片或影片。這種資料型態在關聯式資料庫中需要大量的資料表與關聯設計，會增加查詢複雜度與效能負擔。但在 NoSQL 中，這些資料可以直接以一筆文件（document）方式儲存，查詢與擴充都更靈活有效。
總結來說，如果你的重點是結構清楚、邏輯一致、需要交易保障，那麼關聯式資料庫是好選擇。但如果你要處理如社群平台這種龐大、動態、快速成長的訊息內容與使用者互動資料，則 NoSQL 更能因應需求，提供更好的效能與彈性。
深入閱讀 : 關聯式資料庫與NoSQL資料庫的差異
2-1 你要設計一個支援多國語言的網站資料庫（如：英文、中文、阿拉伯文），你會如何設定字元集與排序規則？如果選錯可能造成哪些問題？
因為 utf8mb4 能完整支援 Unicode，包括中文、阿拉伯文、表情符號（emoji）等所有語言與符號。因此排序方式建議使用：utf8mb4_unicode_ci 或 utf8mb4_general_ci （其中 unicode_ci 提供較準確的國際語言排序規則）。特定語言的 Collation 可幫助詞語斷詞與比對，否則全文索引結果會錯誤或缺漏。
選錯字元集會導致某些特殊字符時會變成問號或錯誤字元，選錯排序方式會導致文字無法照語言習慣排序。同欄位中混用不同語言文字時，排序與 LIKE 查詢結果會不一致。
例如 '我今天好開心 😃' 這個字串，如果使用utf8字元集，會變成 '我今天好開心 ?'
因為 utf8（MySQL 內建）最多支援 3 bytes，但 emoji 需要 4 bytes。如果改用 utf8mb4，字串就會正確了。
例如
CREATE TABLE fruit_bin (
name VARCHAR(20)
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin );
INSERT INTO fruit_bin (name) VALUES ('apple'), ('香蕉'), ('Banana'), ('芒果'), ('grape'), ('蘋果'), ('Orange'), ('葡萄');
當執行這個語法 SELECT * FROM fruit_bin WHERE name LIKE '%果%';
因為以binary比對，並無法找出 "芒果" 或是 "蘋果"，若改用 utf8mb4_zh_tw_ci，就可以找到了。
2-2 什麼是主鍵? 什麼是外鍵?
主鍵是用來「唯一標識」資料表中一筆紀錄的欄位（或欄位組合）。每一筆資料都必須有唯一的主鍵，不能重複，也不能為 NULL。
外鍵是某個資料表中的欄位，它參照了另一張資料表的主鍵，用來建立兩張表之間的關聯性（關聯式資料庫的核心概念）。
例如student資料表以「student_id」當主鍵，而course資料表中有個「student_id」當外鍵，當列出某個課程(特定的course_id)的所有student_id，就可以知道這個course_id有哪些學生選修，也可以列出選修的學生姓名。
2-3 從無到有，你會如何設計MySQL資料庫及資料表?
會經過以下程序來設計資料庫及資料表 :
(1) 需求分析(Requirement Analysis) :
把各種需求收集起來，先搞清楚「要解決什麼問題」與「誰會使用這個系統」。了解了所有的需求，才能知道如何設計資料庫及資料表。
建立資料庫要先確定 ~ 哪些資料庫? 什麼名稱? 字元集是什麼? 排序原則是什麼?
例如我想要管理工廠的製造跟線上銷售的資料，「製造」跟「線上銷售」都是蠻龐大的數據，因此可以分開為manufacture_db、shop_db。然後確認資料庫會儲存哪些資料，如果是多國語言，就選擇utf8mb4字元集，以及utf8mb4_unicode_ci排序原則。
如下，才能使用語法建立資料庫
CREATE DATABASE IF NOT EXISTS shop_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS manufacture_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
其他的需求例如 :
工廠製造哪些產品? 產品需要哪些欄位? 如何編號? 如何分類? 分類有無階層結構? 產品是否可能屬於多個分類? 相同產品是否有變體? (例如不同顏色)等等。
線上銷售的商品跟產品哪些欄位是對照過來的? 哪些欄位是另外的? 是否需要另外編號? 如何分類? 是否可能銷售非自己工廠的產品? 是否需要連線付款?
然後再來根據需求，在每個資料庫下建立資料表。
在需求分析階段，可以使用DFD (Data Flow Diagram)來描述「資料流」，例如下面就是線上商店DFD，可以確定有哪些資料表。
(2) 概念塑模(Conceptual Data Model)
概念塑模的工具就是ERD (實體關係圖 Entity Relationship Diagram)，可以參考這篇 : 實體關係模型(Entity-relationship model)。
實體關係圖就是要幫助我們找到「表格的欄位」，也就是各「實體的屬性」。
例如以下的ER Diagram，可以看到「學生」、「課程」、「老師」三個實體，然後各實體有其屬性。
(3) 邏輯塑模(Logical Data Model)
邏輯塑模就是把ERD轉為建立資料庫的邏輯，例如從實體關係圖知道商品有商品編號、商品名稱、商品價格這三個欄位，那麼商品的邏輯塑模就變成 : products(product_id, product_name, product_price)，然後如果主鍵是product_id底下就畫上紅色實體線。
(4) 實體塑模(Physical Data Model)
實體塑模就是使用指令或是圖形介面把實際的資料表結構建立起來，例如 :
CREATE TABLE products (
product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
product_price DECIMAL(10,2) NOT NULL);
更多關於實務操作可以參考這篇 :
從ER Model到資料庫的實作練習
實作練習~公司員工訂餐系統
2-4 什麼是資料塑模? 解釋概念塑模、邏輯塑模、實體塑模。
如上面的問題，我們再嚴謹的定義一下
資料塑模是指在資訊系統設計中，使用明確的邏輯結構與規則來描述資料元素、資料之間的關係與約束條件的過程。其目的在於建立一個能夠支持系統需求、邏輯一致且可實作於資料庫中的資料結構模型。資料塑模有助於確保資料的一致性、完整性與可擴充性，是系統分析與資料庫設計的重要步驟。
用比較白話來說，資料塑模就像是在畫這棟「資料大樓」的設計圖，目的是先搞清楚我們的資料有哪些、資料之間有什麼關係、該怎麼儲存和整理，讓系統開發和資料庫設計有清楚的方向。
概念塑模是資料塑模的第一階段，用來以高層次、與實作無關的方式描述資料的核心概念與其之間的關係，著重於資料「是什麼」，而非「如何儲存或實作」。此階段通常透過實體關係圖（ERD, Entity-Relationship Diagram） 表示。
邏輯塑模是在概念塑模的基礎上，加入邏輯結構與完整性限制，準備轉換為資料庫設計。邏輯塑模描述資料的結構、主鍵、外鍵與資料型別（概念層級上的類型），但仍然不考慮實體儲存方式與特定資料庫技術。
實體塑模是邏輯塑模的實作版本，針對特定的資料庫管理系統（如 MySQL、PostgreSQL）具體設計資料表結構，包含實際的資料型別、索引設計、儲存參數、分割策略等技術細節。
用比較白話來說，概念塑模是先講清楚有什麼資料，邏輯塑模是開始具體規劃資料結構，實體塑模是寫成真的 SQL 表格。
2-5 為何Data Flow Diagram (DFD)會分成Level 0、Level 1不同層級?
因為一個完整的系統可能會有很多處理程序、資料流動、輸入輸出等等，如果一次畫完，會又雜又亂，難以理解也不利討論。 所以 DFD 採用自頂向下（Top-Down）設計法，用「一層一層」的方式來呈現。
Level 0 也叫上下文圖 (Context Diagram)，顯示整個系統與外部實體（使用者、其他系統）之間的互動關係，通常只有一個處理程序（Process），代表整個系統，算是系統的總覽圖，讓人快速理解「這個系統在做什麼、跟誰互動」。繼續把Level 0 拆解，就會形成Level 1、Level 2 .... 等等層級的DFD。
2-6 解釋Entity Relationship Diagram (ERD)在資料表設計時的用途?
在設計資料庫時，ERD（實體關聯圖，Entity Relationship Diagram） 是一種非常重要的視覺化工具，用來描述資料庫中的實體與資料的關係。
所謂實體通常會對應到資料表，每個實體的屬性就是資料表的欄位，關聯就是外鍵與主鍵形成。
對非技術人員（如業務、企劃、管理者）而言，ERD 比資料表定義更容易理解。它用圖形方式描述資料結構，讓不同背景的人也能參與資料設計討論。他也是一份很好的設計文件，可以在程式開發、系統維護或資料庫升級時，作為參考依據。
更多關於ERD可以參考這篇 : 實體關係模型(Entity-relationship model)
2-7 在資料表設計上，你使用過UML的哪些圖表?
UML 統一塑模語言 (Unified Modeling Language) 是一種標準化的建模語言，可用於視覺化軟體設計和架構。常被誤會的是為何他是語言 (Language)? 因為任何系統化的、用於表示資訊或表達思想的方法都可以稱為語言。
更多關於UML可以參考這篇 : UML 統一塑模語言是什麼? 作用是什麼?
UML 提供了多種不同的圖，但是在資料表設計上，常用的有三個 : 「使用案例圖」可以用來展示需求，「活動圖」可以用來展示流程，「類別圖」可以用來展示資料表結構。
想知道細節，可以參考這個實務操作來了解 : UML的使用案例圖、活動圖、類別圖練習 ~ 以網路選課為例
2-8 正規化是什麼? 有哪些正規化?
正規化（Normalization）是設計資料庫時的一種方法，目的是消除資料重複、避免異常更新、提升資料一致性與可維護性。正規化會將資料結構拆分成更小的資料表，並建立清楚的關聯，讓每筆資料只儲存在一個最適合的位置。
常見的正規化形式 :
第一正規化（1NF：First Normal Form）
資料表中的每個欄位都應是原子值（atomic value），也就是不能再拆分的單一值。
第二正規化（2NF：Second Normal Form）
在符合 1NF 的基礎上，消除對主鍵的部分依賴（partial dependency）。這表示非主鍵欄位應完全依賴於主鍵，而不是主鍵的一部分。
第三正規化（3NF：Third Normal Form）
在符合 2NF 的基礎上，消除「傳遞依賴（transitive dependency）」。 非主鍵欄位不得依賴於另一個非主鍵欄位。
BCNF（Boyce-Codd Normal Form）
比 3NF 更嚴格，要求 所有決定因子（determinant）都必須是候選鍵（Candidate Key）。 在某些特殊情況下，3NF 無法完全消除異常，這時會用 BCNF。
第四正規化（4NF）
消除多值依賴（Multivalued Dependency）。當一個實體可以對應多筆獨立資料時，應拆成獨立資料表。
更多關於正規化，細節可以參考這篇 : 資料庫正規化 Database normalization
2-9 反正規化是什麼? 為何需要反正規化?
反正規化（Denormalization） 是一種將已經正規化的資料表「部分合併或還原」的設計策略，目的是提升查詢效能或簡化應用邏輯，即使這樣做可能會引入一些資料重複或一致性風險。 簡單來說： 正規化是為了「資料一致性與結構設計的純粹性」，反正規化則是為了「查詢效率與實務需求的取捨」。
例如因為正規化而把資料表拆成兩個表單，但是如果這兩個表單很頻繁的被查詢，透過關聯來抓出兩個表單的欄位，很可能就需要「反正規化」的把這兩個表單合併或是重複欄位的方式讓查詢效率提升。
2-10 請設計一張訂單資料表及其相關表單。
細節可以參考這兩篇 : 實機練習、實作練習 : 如何從實體表單轉換為資料庫表單?
前面提過，要設計資料表需要從需求分析、概念塑模、邏輯塑模、實體塑模，因為前面已經做個，這邊不再贅述。
直接說結果，訂單資料表至少需要以下幾個表單 :
產品資料表 product (prod_no, category_no, prod_name, prod_price, prod_unit, prod_stock)
prod_no 產品編號，category_no 產品類別編號，prod_name 產品名稱，prod_price 產品價格，prod_unit 產品單位，prod_stock 產品庫存量
category_no會關聯到產品類別資料表的category_no
產品類別資料表 category (category_no, category_name)
category_no 產品類別編號，category_name 產品類別名稱
客戶資料表 customer (c_no, c_name)
c_no 客戶編號，c_name 客戶名稱 (其他欄位省略)
訂單資料表 myorder (o_no, c_no, o_total, o_date)
o_no 訂單編號，c_no 客戶編號，o_total 訂單總額，o_date 訂單日期 (其他欄位省略)
c_no會關連到客戶資料表的c_no
訂單細目資料表 order_items (oi_no, o_no, prod_no, prod_qty, prod_price)
oi_no 訂單細目編號，o_no 訂單編號，prod_no 產品編號，prod_qty 產品數量，prod_price 產品價格，o_no會關連到訂單資料表的o_no，prod_no會關連到產品資料表的prod_no
詳細的資料表格式如下 ~~
3-1 MySQL的SQL語法分成幾類? 請各類舉一個例子。
SQL語法分成以下幾種 :
(1) DDL (Data Definition Language) 資料定義語言
例如 Create Table 用來定義表格
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
product_price DECIMAL(10,2) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
(2) DML (Data Manipulation Language) 資料處理語言
例如 Update 用來更新表格的欄位資料
UPDATE products
SET price = 199.99
WHERE product_id = 1;
(3) DRL (Data Retrieval Language) 資料擷取語言
例如 Select 用來取出表格內的資料，也可以把Select列為DML
Select product_name FROM products
WHERE product_price<100;
(4) TCL (Transaction Control Language) 交易控制語言
例如 Commit 用來確認動作
START TRANSACTION;
-- 執行一連串 SQL 操作
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE user_id = 2;
COMMIT;
(5) DCL (Data Control Language) 資料控制語言
例如 Grant 用來授予使用者權限
GRANT SELECT ON mydb.products TO 'john'@'localhost';
如果把Select也列為DML，SQL語法就只分成四大類。
更多參考資料，請參考這篇 : SQL的四種類型DDL/DML/DCL/TCL
3-2 訂單資料表的設計上會用到哪些資料型態?
MySQL常用的資料型態有數值型態、字串型態、日期與時間型態、布林型態。
訂單資料表會用到的資料型態有以下幾種 :
(1) 數值型態的INT或是BigINT，當成自動生成的編號的資料型態，例如prod_no。
(2) 數值型態的INT或是SmallINT，可以當成庫存量 prod_stock 的資料型態。
(3) 數值型態的 Decimal ，可以當成產品價格 prod_price 的資料型態。
(4) 字串型態的 Varchar，可以當成文字類型欄位的資料型態，例如prod_name。
(5) 日期與時間型態的 Datetime，可以當成訂單日期 o_date的資料型態。
3-3 資料表的主鍵你會使用哪種資料型態? 為什麼?
資料表的主鍵選 INT（或 BIGINT）最常見，因為具有以下優點 :
效能好 : 整數型別在索引、排序、JOIN 時效能最佳，處理速度比字串快很多。
自動遞增簡單 : AUTO_INCREMENT 可讓主鍵自動產生唯一值，開發方便又不易重複。
儲存空間較小 : INT（4 bytes）或 BIGINT（8 bytes）比 UUID 或 VARCHAR 更省空間。
適合關聯查詢 : 整數型主鍵可以作為外鍵（foreign key）參照其他表格時更有效率。
3-4 VARCHAR 與 CHAR 的差別為何？使用時機為何？
CHAR(n)為固定長度字串，不論實際內容長度，一律補滿 n 個字元。VARCHAR(n) 可變長度字串，根據實際內容儲存，會附加額外的長度資訊（1~2 bytes）。
例如，CHAR(10)的型態如果放入字串 "abc"，其長度是10字元，而VARCHAR(10) 的 "abc" 只佔3字元 + 1~2 bytes 長度資訊。
使用時機是~~長度固定的欄位，如：身分證字號、國籍代碼、郵遞區號、性別，可以使用CHAR(n)，長度不定的欄位，如：姓名、email、地址、備註等，可以使用 VARCHAR(n)。
3-5 DECIMAL 與 FLOAT 差在哪？金額欄位該用哪一個？
DECIMAL 與 FLOAT 都可以儲存小數，但用途與特性完全不同。這在金額、科學計算或報表系統中非常關鍵！金額欄位請用 DECIMAL，不要用 FLOAT！
FLOAT 浮點數是用近似值表示小數，因此會有誤差累積，這在會計、金流、發票報表中是不可接受的！
例如
CREATE TABLE test_float_decimal (
id INT AUTO_INCREMENT PRIMARY KEY,
val_float FLOAT,
val_decimal DECIMAL(10, 2) );
INSERT INTO test_float_decimal (val_float, val_decimal) VALUES (0.1 + 0.2, 0.1 + 0.2);
SELECT val_float, val_decimal
FROM test_float_decimal;
輸出結果是 :
0.30000001192092896
0.30
可以看到 val_float 的值不是我們預期的。
3-6 AUTO_INCREMENT 如何自訂起始值？
在 MySQL 中，AUTO_INCREMENT 欄位的起始值預設為 1，但你可以透過以下幾種方式自訂起始值：
(1) 建立表單時宣告
CREATE TABLE products (
id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (id) ) AUTO_INCREMENT=1000;
id 將從 1000 開始遞增。
(2) 在資料表建立後修改起始值
ALTER TABLE products AUTO_INCREMENT = 2000;
如果以上的AUTO_INCREMENT已經超過宣告的值，則宣告會無效。
如果id的值是1,2,3,4,5，結果4,5被刪除後，你希望下個資料可以接著4開始，則可以宣告 : ALTER TABLE products AUTO_INCREMENT = 4;
3-7 NULL 與空字串 '' 在 MySQL 有什麼差別？
NULL是指「沒有值」，空字串是指「有值，值為空的字串」。任何資料型態都有可能是NULL，但是空字串只能是字串資料型態。NULL的長度是NULL，而空字串的長度是0。
當一個資料表的欄位是NULL時，表示「未填寫」，空字串則是指「填寫了但是空白」。
例如 SELECT COUNT(*) FROM users WHERE email IS NULL;
可以知道「有多少人未填寫email」
例如 SELECT COUNT(*) FROM users WHERE email = '';
則可以知道「有多少人填寫空白email」
3-8 在什麼情況下你會使用 ENUM？這種型別有什麼限制？
ENUM 是 MySQL 提供的一種「列舉型別（ENUM type）」欄位，適合用來儲存一組預定義選項中的某一個值。在正確情境下使用可以讓資料更結構化，但也有一些限制。
例如 CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
status ENUM('pending', 'paid', 'shipped') NOT NULL );
以上是指status這個欄位，會有三種狀態 : pending、paid、shipped，但是在資料欄位內是儲存1,2,3來代替。如果後來發現三個狀態不夠，就要再更改資料表結構。
ALTER TABLE orders MODIFY status ENUM('pending', 'paid', 'shipped', 'cancel') NOT NULL;
通常會使用ENUM，都是這個欄位不會變動、值不多，如果不是的話，最好建立另外一個對照表來做。
例如產品類別，可能會一直需要擴增或是修改，就建議使用如下結構 :
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
category_id INT NOT NULL ... (後面省略)
CREATE TABLE category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(20) NOT NULL ... (後面省略)
如此以category_id來關聯products與category，就比使用ENUM靈活。
3-9 解釋何為關聯模式的五大鍵?
關聯模式的五大鍵，各是Super key (超鍵)、Candidate Key (候選鍵)、Primary Key (主鍵)、Alternate Key (替代鍵)、Foreign Key (外鍵)。靠著這些鍵的特性，讓關聯模式可以用來描述實體世界的資料。
Super key 超鍵 : 符合唯一性的關聯鍵。
Candidate Key 候選鍵 : 符合唯一性以及最小性的關聯鍵。
Primary Key 主鍵 : 從候選鍵中，挑選出其中一個關聯鍵，也就是最具識別意義的關聯鍵。
Alternate Key 次要鍵 : 沒有被選為主鍵的其他候選鍵。
Foreign Key 外鍵/外部鍵 : 關聯中被用來參考到其他表格主鍵的關聯鍵，就是外鍵。
例如一個學生資料表中，學生學號+學生姓名也符合唯一性，身分證號+生日也符合唯一性，因為關聯鍵不一定都是單個欄位，有可能是兩個或三個欄位組成，只要其中搭配了學生學號或是身分證號，這些都算是[超鍵]，但是符合唯一性以及最小性的只有學生學號、身分證號。
因此我們就可以從學生學號、身分證號這兩個[候選鍵]，挑一個當成[主鍵]。如果挑選了學生學號當成[主鍵]，身分證號就成為[次要鍵]。另外有一個成績資料表，會有學生學號當成[外鍵]，用來關聯到學生資料表的[主鍵]。
至於為何要選學生學號當主鍵，而不是身分證號呢? 原則上身分證號是不會重複，但是也發生過身分證號重複的狀況，因此外部單位發給的資料，最好不要當成主鍵。
另外一個原因就是，同一個學生可能會在同一個學校不同的學制下就讀，或是入學後休退學再重新考試入學，如果使用身分證號當成主鍵，就可能發生問題。
更多參考資料請看這篇 : 關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key
3-10 解釋何為Inner Join? 何為Outer Join? 何為Union?
Inner Join是取交集，Outer Join是取聯集，Union是合併。
例如
SELECT orders.id, members.name
FROM orders
INNER JOIN members ON orders.member_id = members.id;
以上語法是列出訂單編號及會員姓名 (訂單與會員的交集)
OUTER JOIN有三種 : LEFT JOIN、RIGHT JOIN、FULL JOIN(MySQL不支援)。
LEFT JOIN : 左邊資料全保留，右邊無資料用 NULL 補。
RIGHT JOIN : 右邊資料全保留，左邊無資料用 NULL 補。
MySQL 沒有內建 FULL OUTER JOIN，但可以用 UNION 模擬。
SELECT members.name, orders.id
FROM members
LEFT JOIN orders ON members.id = orders.member_id;
顯示所有會員，即使沒有下訂單，訂單欄會是 NULL。
SELECT members.name, orders.product
FROM members
RIGHT JOIN orders ON members.id = orders.member_id;
保留右邊表 orders 的所有資料，左邊表 members 若無對應，則用 NULL 補上。
UNION可以合併兩個表單，但是前提要兩個表單結構一樣。
SELECT name, email FROM students
UNION
SELECT name, email FROM teachers;
如此兩個表單會合併為一個表單。
更多參考資料 : 什麼是Inner Join、Outer Join、Union ?
以下題目的表單結構如下 :
customer (customer_id, customer_name)
category (category_id, category_name)
product (product_id, category_id, product_name, product_price)
order_head (order_head_id, order_date, customer_id)
order_body (order_body_id, order_head_id, product_id, product_price, product_qty)
4-1 請說明 WHERE 與 HAVING 的差別，並舉出找出每個客戶總消費金額超過 $5000 的 SQL。
SELECT c.customer_id, c.customer_name, SUM(ob.product_price * ob.product_qty) AS total_spent
FROM customer c
JOIN order_head oh ON c.customer_id = oh.customer_id
JOIN order_body ob ON oh.order_head_id = ob.order_head_id
GROUP BY c.customer_id
HAVING SUM(ob.product_price * ob.product_qty) > 5000;
參考資料 : SQL語法中WHERE與HAVING有何差異?
WHERE 與 HAVING 都是篩選條件語句，但是通常HAVING會出現在GROUP BY時針對聚合函數下條件。也就是HAVING要過濾的條件，要跟GROUP BY的聚合函數有關。
(1) 沒有GROUP BY的時候，只使用WHERE而不使用HAVING。
(2) 有GROUP BY的時候，WHERE在GROUP BY前面，HAVING在GROUP BY後面。
(3) 使用HAVING的時候，只用在跟GROUP BY相關函數有關的條件上。
4-2 什麼是 視圖（View）？請寫出建立一個「本月有效訂單」的視圖的語法。
View（視圖）是儲存在資料庫中的查詢結果虛擬表，可以像查表一樣使用，用來簡化複雜查詢。
建立「本月有效訂單」的視圖 :
CREATE VIEW valid_orders_this_month AS
SELECT * FROM order_head
WHERE MONTH(order_date) = MONTH(CURDATE())
AND YEAR(order_date) = YEAR(CURDATE());
更多參考資料 : MySQL如何使用View(視圖)?
4-3 請寫出一個查詢：找出沒有任何訂單的客戶（左連接與 IS NULL 的應用）。
SELECT c.customer_id, c.customer_name
FROM customer c
LEFT JOIN order_head oh ON c.customer_id = oh.customer_id
WHERE oh.order_head_id IS NULL;
4-4 有訂單表與商品表，若每筆訂單可以有多項商品，請寫出統計「每位客戶總共買了幾種不同商品」的 SQL。
SELECT c.customer_id, c.customer_name, COUNT(DISTINCT ob.product_id) AS distinct_products
FROM customer c
JOIN order_head oh ON c.customer_id = oh.customer_id
JOIN order_body ob ON oh.order_head_id = ob.order_head_id
GROUP BY c.customer_id, c.customer_name;
4-5 請寫出查詢：找出每一類商品中價格最高的那筆資料（子查詢或 RANK() 方法皆可）。
SELECT * FROM product p
WHERE (p.category_id, p.product_price) IN (
SELECT category_id, MAX(product_price) FROM product GROUP BY category_id );
4-6 請找出過去 30 天內，有購買過「手機類商品」的客戶名單（不重複）。
SELECT DISTINCT c.customer_id, c.customer_name FROM customer c JOIN order_head oh ON c.customer_id = oh.customer_id JOIN order_body ob ON oh.order_head_id = ob.order_head_id JOIN product p ON ob.product_id = p.product_id JOIN category cat ON p.category_id = cat.category_id WHERE cat.category_name = '手機' AND oh.order_date >= CURDATE() - INTERVAL 30 DAY;
4-7 請列出訂單總金額大於所有平均訂單金額的訂單編號與金額。
SELECT oh.order_head_id, SUM(ob.product_price * ob.product_qty) AS total_amount
FROM order_head oh
JOIN order_body ob ON oh.order_head_id = ob.order_head_id
GROUP BY oh.order_head_id
HAVING total_amount > (SELECT AVG(total_amount)
FROM ( SELECT SUM(product_price * product_qty) AS total_amount FROM order_body GROUP BY order_head_id ) AS sub );
4-8 請找出有下單紀錄但沒有購買過「電腦類」商品的客戶姓名與 ID。
SELECT DISTINCT c.customer_id, c.customer_name
FROM customer c
JOIN order_head oh ON c.customer_id = oh.customer_id
WHERE c.customer_id NOT IN ( SELECT DISTINCT c2.customer_id FROM customer c2 JOIN order_head oh2 ON c2.customer_id = oh2.customer_id JOIN order_body ob2 ON oh2.order_head_id = ob2.order_head_id JOIN product p2 ON ob2.product_id = p2.product_id JOIN category cat2 ON p2.category_id = cat2.category_id WHERE cat2.category_name = '電腦' );
4-9 請列出每位客戶近一次下單時間與該筆訂單金額（order_id, order_date, total_amount）。
SELECT c.customer_id, c.customer_name, oh.order_head_id, oh.order_date, SUM(ob.product_price * ob.product_qty) AS total_amount
FROM customer c
JOIN order_head oh ON c.customer_id = oh.customer_id
JOIN order_body ob ON oh.order_head_id = ob.order_head_id
WHERE oh.order_date = ( SELECT MAX(oh2.order_date) FROM order_head oh2 WHERE oh2.customer_id = c.customer_id ) GROUP BY c.customer_id, oh.order_head_id;
4-10 請統計每類商品總共賣了幾件、銷售總金額為多少？並依金額排序。
SELECT cat.category_name, SUM(ob.product_qty) AS total_qty, SUM(ob.product_price * ob.product_qty) AS total_sales
FROM order_body ob
JOIN product p ON ob.product_id = p.product_id
JOIN category cat ON p.category_id = cat.category_id
GROUP BY cat.category_name ORDER BY total_sales DESC;
5-1 請解釋什麼是索引? 為什麼資料表需要建立索引?
在 MySQL等資料庫中，索引就像是「書的目錄」或「字典的檢索表」，用來幫助資料庫更快速地找到特定資料。 沒有索引時，資料庫需要從頭到尾掃描整張表（Full Table Scan）來找到符合條件的資料，效率低下。 使用索引時，資料庫會直接利用索引快速定位到目標資料列，而不需要檢查所有資料。
為什麼資料表需要建立索引？
(1) 提升查詢效能
對常用於 WHERE 條件、JOIN 條件、ORDER BY、GROUP BY 的欄位建立索引，可以大幅加快查詢速度。
(2) 加快排序與群組運算
ORDER BY、GROUP BY 常常使用索引加速排序或分組計算。
(3) 保證欄位唯一性
UNIQUE 索引 或 PRIMARY KEY 索引 可確保欄位值不會重複，例如會員的 email、商品編號。
5-2 哪些欄位適合建立索引？哪些欄位不建議建立索引？請說明原因。
1. 常用於搜尋（WHERE）
高選擇性（高唯一性）的欄位，例如：email、手機號碼、身分證字號。
高選擇性 → 值種類多、重複少，能有效縮小查詢範圍，加快搜尋速度。
2. 作為排序（ORDER BY）或分組（GROUP BY）
例如：訂單日期 order_date、總金額 total_amount，索引可以直接加快排序或分組，減少額外排序運算。
3. JOIN 關聯欄位
例如：order_head.customer_id（外鍵欄位），JOIN 會透過索引快速定位對應資料，加快關聯查詢。
4. 需要確保唯一性
例如：商品編號 product_code、會員帳號，PRIMARY KEY 或 UNIQUE 索引可確保資料不重複。
5. 經常用於篩選範圍查詢
例如：price BETWEEN 1000 AND 5000、order_date > '2025-01-01'，B-Tree 索引對範圍查詢效果好。
通常不常用於查詢的欄位，或是超長文字型欄位，或是經常異動的欄位，都不建議建立索引。因為建立索引雖然可以加快查詢效率，但是在寫入時都必須重建索引，反而造成資料異動時效能下降，因此並不是每個欄位都適合建立索引。
5-3 請比較以下三種索引類型的差異：PRIMARY KEY、UNIQUE INDEX、普通 INDEX。
PRIMARY KEY (主鍵) 是用來唯一標示紀錄的一個索引，必須NOT NULL，並且一個表單只能有一個主鍵。UNIQUE INDEX 也是必須唯一，但允許單一欄位有多個 NULL（在 MySQL 中 NULL 不被視為重複），並且一個表單可以有多個UNIQUE INDEX。普通 INDEX則是一般索引，只要需要都可以建立。
在使用場景上，PRIMARY KEY可能是產品編號、客戶編號之類；UNIQUE INDEX可能是電子郵件、行動電話之類；普通 INDEX則不一定，只要是經常被搜尋的都可能是普通 INDEX。
更多參考資訊 : Primary、Unique、Index各代表什麼意義?
5-4 請寫出一個 SQL 語法：為 orders 資料表的 order_date 欄位建立索引。
-- 建立普通索引 (INDEX)
CREATE INDEX idx_order_date
ON orders (order_date);
-- 如果要建立唯一索引（UNIQUE INDEX）
CREATE UNIQUE INDEX idx_order_date_unique
ON orders (order_date);
但是實務上，order_date比較不會是UNIQUE INDEX
如果要直接在 ALTER TABLE 中建立索引
ALTER TABLE orders
ADD INDEX idx_order_date (order_date);
5-5 如果對 name LIKE '%王%' 這類查詢建立索引，會有效嗎？為什麼？如何改善？
對 name LIKE '%王%' 這類查詢建立索引，是無效的。
原因是 MySQL（B-Tree 索引）在進行索引搜尋時，必須能從欄位的開頭開始比對， 但 '%王%' 的前面有萬用字元 %，導致無法利用索引的前綴特性。
因此必須是 name LIKE '王%' 才會有效果。
5-6 請解釋什麼是複合索引（Composite Index）？請說明建立 (col1, col2) 與 (col2, col1) 的差異。
複合索引（Composite Index） 是指在多個欄位上建立的一個索引，例如
CREATE INDEX idx_col1_col2 ON mytable (col1, col2);
MySQL 在使用複合索引時，必須從最左邊的欄位開始使用，稱為「最左前綴原則」。
例如以下可以順利使用索引 :
WHERE col1 = ?
WHERE col1 = ? AND col2 = ?
WHERE col1 = ? AND col2 > ?
WHERE col1 BETWEEN ? AND ?
以下無法順利使用索引 :
WHERE col2 = ?（跳過 col1）
WHERE col2 = ? AND col1 > ?（未依序使用最左欄位）
5-7 什麼是索引失效？請舉出三種常見會導致索引失效的查詢寫法。
索引失效（Index Failure）指的是雖然該欄位有建立索引，但查詢時 MySQL 無法有效利用索引，而是改用全表掃描（Full Table Scan），導致查詢效能下降。
常見會導致索引失效的查詢寫法 :
(1) 使用 LIKE '%字串%' 的前置模糊查詢
索引是按照從左到右的字串排序，如果 % 放在前面，無法利用 B-Tree 索引的「最左前綴原則」。
(2) 對索引欄位進行函數或運算
-- 假設 order_date 欄位有索引
SELECT * FROM orders
WHERE YEAR(order_date) = 2025; -- 索引失效
MySQL 需要先對每筆資料執行 YEAR() 函數，導致無法直接利用索引。
應該改寫為 :
SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
(3) 索引欄位與不同型別比較（隱式型別轉換）
-- 假設 customer_id 是 INT 並有索引
SELECT * FROM customer
WHERE customer_id = '1001';
若 customer_id 是 INT，但用字串比較，MySQL 會進行型別轉換，就無法使用原來的索引。
(4) 其他常見索引失效原因
複合索引未遵守最左前綴原則
使用 !=、<>、IS NULL、IS NOT NULL
OR 條件中只有部分欄位有索引
5-8 在寫 SELECT 查詢時，該如何改寫語法以盡量利用索引？請舉一個實務例子說明調整前後的差異。
假設資料表結構：
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATE,
customer_id INT,
amount DECIMAL(10,2), INDEX idx_order_date (order_date) );
索引失效的語法 :
SELECT order_id, customer_id, amount
FROM orders
WHERE YEAR(order_date) = 2025;
調整後（索引生效）的語法 :
SELECT order_id, customer_id, amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';
假設資料量 100 萬筆，YEAR(order_date) = 2025 大約執行時間是0.8秒，調整後的語法大約執行時間是0.05秒。
5-9 請解釋什麼是 Index Merge 策略？MySQL 何時會使用這種執行計畫？
Index Merge 是 MySQL 在執行查詢時的一種執行計畫 (Execution Plan)， 當單一索引無法滿足查詢條件，但多個索引可以分別針對不同條件加速查詢時， MySQL 會同時利用多個單欄索引 (Multiple Single-Column Indexes)，將結果集進行合併（Merge），以減少全表掃描。
例如
SELECT * FROM orders WHERE customer_id = 1001 OR amount > 500;
customer_id 與 amount 各自有索引，MySQL 可分別查出符合條件的主鍵集合，再做聯集。
例如
SELECT * FROM orders WHERE customer_id = 1001 AND amount > 500;
customer_id 與 amount 各自有索引，MySQL 會先找出兩個索引各自符合的主鍵集合， 再取交集，大幅減少回表掃描行數。
5-10 若對 email 欄位建立唯一索引，當資料表變大時，對寫入效能會有什麼影響？如何取捨？
每次 INSERT 或 UPDATE 時，MySQL 必須檢查唯一性，寫入時會額外進行索引查詢，確認該 email 是否已存在。 資料量越大，B-Tree 越深，插入索引時需要更多的 B-Tree 節點比較與維護，寫入耗時會增加。 影響主要在「寫入」，查詢效能不受明顯影響，因為唯一索引對 SELECT 還是非常有利。
適合建立唯一索引的情況:
(1)資料完整性要求高（例如 email 必須唯一，會員註冊）
(2)SELECT 查詢以 email 為主（例如登入時依 email 查詢帳號）
(3)寫入頻率相對較低、查詢頻率高的系統
不適合或需慎重考慮的情況:
(1)寫入頻率極高（例如即時大量資料寫入的 log 系統）
(2)允許 email 重複（例如行銷寄送名單，允許同 email 多次出現）
(3)寫入速度比查詢正確性更重要的應用
6-1 請說明什麼是交易（Transaction）？MySQL 中哪些語法可啟動、提交與回滾交易？
交易（Transaction） 是指一組要被當作「一個整體」執行的 SQL 操作。例如一個轉帳，要從來源帳戶扣款，再從目的帳戶入款，如果扣款或入款任何一個程序發生問題，兩個動作都需要回到原點。因此轉帳就是一個整體的程序，裡面的子程序只有「全部完成」或是「全不完成」，不容許只完成部分程序。
在MySQL中要使用交易，必須是InnoDB儲存引擎，交易主要針對 DML 語句（INSERT、UPDATE、DELETE）。 DDL 語句（CREATE、ALTER、DROP）通常會自動提交，無法回滾。
啟動交易有兩種方式：使用 START TRANSACTION 或 BEGIN
提交交易 : Commit
回滾交易 : Rollback
範例 :
-- 假設有一個 bank 資料表 (account, balance)
START TRANSACTION;
-- 1. 開始交易
UPDATE bank SET balance = balance - 1000 WHERE account = 'A001';
-- A帳戶扣款
UPDATE bank SET balance = balance + 1000 WHERE account = 'A002';
-- B帳戶加款
-- 若兩筆 SQL 都執行成功，提交交易
COMMIT;
-- 如果其中一筆 SQL 發生錯誤，可以回滾
-- ROLLBACK;
在同一個交易中，還可設置「保存點」：
-- 設置保存點
SAVEPOINT sp1;
-- 回滾到指定保存點
ROLLBACK TO sp1;
-- 釋放保存點
RELEASE SAVEPOINT sp1;
6-2 InnoDB 資料表預設使用哪種鎖？它與 MyISAM 有什麼差異？
InnoDB 資料表預設使用：行級鎖（Row-level Lock）
InnoDB 在處理 INSERT、UPDATE、DELETE 等 DML 語句時，預設只鎖住相關的資料列。 特點是鎖定範圍小，平行效能高。 適合高平行、多交易的線上交易系統（OLTP）。 依不同查詢情況，InnoDB 也可能使用 Next-Key Lock（行鎖 + 間隙鎖） 以避免幻讀。
MyISAM 預設使用：表級鎖（Table-level Lock）
MyISAM 在執行任何讀寫操作時，會鎖住整張資料表。 特點是適合以讀為主、更新不頻繁的情境（如報表或查詢分析）。 在多筆同時寫入時，效能較差、容易產生等待。
6-3 請列出 MySQL 中的四種隔離等級（Isolation Level），並說明各等級可能會出現哪些現象（如髒讀、不可重複讀、幻讀）。
你可以用語法檢查隔離等級
SELECT @@GLOBAL.transaction_isolation; -- Global default
SELECT @@SESSION.transaction_isolation; -- Current session
在各隔離等級下，可能發生一些錯誤如下:
髒讀（Dirty Read）： 讀取到其他交易尚未提交且可能會回滾的資料。
不可重複讀（Non-repeatable Read）： 同一筆資料在同一交易內，多次讀取到不同的值（因其他交易已更新並提交）。
幻讀（Phantom Read）： 在相同條件下，重複查詢可能得到不同數量的資料列（因其他交易新增或刪除資料）。
MySQL（特別是 InnoDB 引擎） 四種標準的隔離等級（Isolation Level），以及各等級可能出現的現象說明：
1. READ UNCOMMITTED（讀未提交）
SET GLOBAL transaction_isolation = 'READ UNCOMMITTED';
這個隔離等級下，交易可以讀取到其他交易尚未提交的資料。因此會發生髒讀、不可重複讀、幻讀。
2. READ COMMITTED（讀已提交）
SET GLOBAL transaction_isolation = 'READ COMMITTED';
只能讀取到其他交易已經提交的資料，可以避免髒讀，但會發生不可重複讀、幻讀。
3. REPEATABLE READ（可重複讀）
SET GLOBAL transaction_isolation = 'REPEATABLE-READ';
在同一個交易中，多次讀取相同條件的資料，結果保持一致（InnoDB 預設使用此等級）。 InnoDB 會透過 Next-Key Lock（行鎖 + 間隙鎖） 來避免幻讀。
這個隔離等級可以避免發生髒讀、不可重複讀、幻讀。
4. SERIALIZABLE（可序列化）
SET GLOBAL transaction_isolation = 'SERIALIZABLE';
最高隔離等級，所有讀寫操作都以「序列化」方式執行，相當於給查詢加上表級鎖，幾乎完全避免併發問題。這個隔離等級也可以避免發生髒讀、不可重複讀、幻讀。
REPEATABLE READ（預設） 採行級鎖 + Next-Key Lock，只鎖住查詢範圍內的資料列和間隙。 允許其他交易同時讀取資料（不會被阻塞），併發效能高。SERIALIZABLE（最嚴格） 會將「普通查詢」也視為需要鎖定，等同於對查詢加共享鎖（S Lock）。 其他交易若要修改或插入同範圍的資料，必須等待當前交易結束。
因此一般不會用到SERIALIZABLE，都只用到REPEATABLE READ。
更多參考資訊 : MySQL InnoDB Isolation Level 隔離層級是什麼?
6-4 什麼是行鎖（Row Lock）與表鎖（Table Lock）？請舉例說明兩者的適用場景與差異。
InnoDB 預設使用行鎖，行鎖是只鎖住資料表中的特定資料列的鎖定方式。只要其他交易不操作同一列，彼此就能並行執行。雖然鎖定範圍小，適合並行作業頻繁的情況，但是可能會發生死結 (dead lock)。
-- Transaction A
START TRANSACTION;
UPDATE orders SET status = 'paid'
WHERE order_id = 1;
-- Transaction B (同時執行)
START TRANSACTION;
UPDATE orders SET status = 'paid'
WHERE order_id = 2;
以上兩筆交易可以同時成功執行，因為Transaction A只鎖定order_id = 1，Transaction B只鎖定order_id = 2。
更多參考資訊 : MySQL Lock : Table Lock與Row Lock
6-5 請舉例說明在交易中可能導致「死結（Deadlock）」的情況，並說明 MySQL 如何偵測與處理。
死結（Deadlock） 是指兩個或以上的交易互相持有對方需要的鎖，且彼此都在等待對方釋放鎖，最終導致所有交易無法繼續執行。
假設有一個 account 資料表，欄位結構如下：
account(account_id INT, balance DECIMAL)
Transaction A :
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
-- Transaction A 已經鎖住 account_id = 1 的資料列
-- 然後想要更新另一筆
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
-- 但此時 account_id = 2 已被 Transaction B 鎖住 → 等待
Transaction B :
START TRANSACTION;
UPDATE account SET balance = balance - 50 WHERE account_id = 2;
-- Transaction B 已經鎖住 account_id = 2 的資料列
-- 然後想要更新另一筆
UPDATE account SET balance = balance + 50 WHERE account_id = 1;
-- 但此時 account_id = 1 已被 Transaction A 鎖住 → 等待
A 等待 B 釋放 account_id = 2 的鎖；
B 等待 A 釋放 account_id = 1 的鎖；
彼此互相等待 → 形成死結。
如果發生死結，InnoDB 會自動回滾其中一個交易（選擇回滾成本最小的交易）。 被回滾的交易會拋出錯誤：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
只要在應用程式中捕捉該錯誤，並重新執行交易即可。
要避免死結的發生，可以掌握以下幾個原則 :
(1) 只鎖定必要的資料列。
(2) 盡量一次鎖定所有需要的資源。
(3) 縮短交易時間。
(4) 固定鎖定順序，例如所有交易都先更新 account_id 小的，再更新大的，避免交錯鎖定。
以上(3)(4)不太需要解釋，但是(1)(2)乍看似乎有些矛盾，其實並不矛盾。
「盡量一次鎖定所有需要的資源」是指如果你的交易需要鎖定id=1、id=2，就一次都鎖定，不要只鎖定id=1，執行後再鎖定id=2，因為id=2很可能就被搶走了。
「只鎖定必要的資料列」是指如果你的交易需要鎖定id=1、id=2，就不要表鎖定或鎖定id=3。
6-6 請解釋 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 的差異與用途。
SELECT ... FOR UPDATE 會對查詢到的資料列加上排他鎖（Exclusive Lock, X Lock），其他交易無法對這些列進行更新或刪除，直到當前交易提交或回滾。
SELECT ... LOCK IN SHARE MODE 會對查詢到的資料列加上共享鎖（Shared Lock, S Lock）。 其他交易仍可讀取資料，但無法修改或刪除（修改時會被阻塞，直到鎖釋放）。
如果你對於查詢到的資料列，後續會更新，當然就使用SELECT ... FOR UPDATE ，如果只檢查資料一致性、不打算更新的情況，就使用SELECT ... LOCK IN SHARE MODE 。
6-7 你會如何查詢當前資料庫有哪些交易正在等待鎖？請寫出相對應的 SQL 或工具指令。
在 MySQL (InnoDB) 中，要查詢當前資料庫有哪些交易正在等待鎖，可以使用 系統資料表 (information_schema) 或 管理工具指令。以下是常用的幾種方法：
1. 使用 information_schema.innodb_trx、innodb_locks、innodb_lock_waits
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
結果說明：
waiting_trx_id：正在等待鎖的交易 ID
waiting_thread：等待鎖的 MySQL 執行緒 ID
waiting_query：正在等待鎖的 SQL 語句
blocking_trx_id：正在持有鎖、造成阻塞的交易 ID
blocking_query：造成阻塞的 SQL 語句 此查詢可以明確顯示「誰在等誰」。
2. 使用 SHOW ENGINE INNODB STATUS（快速檢視）
SHOW ENGINE INNODB STATUS\G
輸出結果中，找到 LATEST DETECTED DEADLOCK 或 LATEST FOREIGN KEY ERROR，以及 TRANSACTIONS 區塊，可以看到目前被鎖住的交易、持鎖者以及等待鎖的狀態。 此方法適合快速檢視，但不如 SQL 查詢易於結構化分析。
3. 使用 Performance Schema（MySQL 5.7+ / 8.0 推薦）
SELECT performance_schema.events_transactions_current.THREAD_ID, performance_schema.events_transactions_current.EVENT_ID, performance_schema.events_transactions_current.STATE, performance_schema.threads.PROCESSLIST_ID, performance_schema.threads.PROCESSLIST_USER, performance_schema.threads.PROCESSLIST_INFO FROM performance_schema.events_transactions_current JOIN performance_schema.threads ON events_transactions_current.THREAD_ID = threads.THREAD_ID WHERE events_transactions_current.STATE = 'LOCKED';
4. 工具指令（適合 DBA 快速查看）
SHOW PROCESSLIST ~ 查看目前執行緒狀態，有 Waiting for table metadata lock、Locked、Waiting for row lock 等狀態可辨識。
mysqladmin processlist（命令列）~ 快速取得與 SHOW PROCESSLIST 類似的結果。
6-8 若兩筆交易同時修改相同欄位資料，但隔離等級不同（READ COMMITTED vs REPEATABLE READ），其結果會有何不同？
[情境假設]
資料表：product (product_id, stock)
初始資料：product_id=1, stock=100
兩個交易：T1、T2 同時執行
隔離等級： T1 → READ COMMITTED ；T2 → REPEATABLE READ
[執行順序]
T1 (READ COMMITTED)：
START TRANSACTION;
SELECT stock FROM product WHERE product_id=1; -- 讀到 100
T2 (REPEATABLE READ)：
START TRANSACTION;
SELECT stock FROM product WHERE product_id=1; -- 讀到 100
UPDATE product SET stock = stock - 10 WHERE product_id=1; -- stock = 90
COMMIT;
T1 繼續：
SELECT stock FROM product WHERE product_id=1; -- 在不同隔離等級下，這裡會不一樣
UPDATE product SET stock = stock - 20 WHERE product_id=1;
COMMIT;
後續再來實作結果 ...
6-9 當一筆交易尚未提交，但另一筆交易已讀取該資料，這種行為是什麼？在哪些隔離等級會發生？
一筆交易（T2）讀取到另一筆交易（T1）尚未提交（Uncommitted）的修改資料。這種行為稱為 髒讀（Dirty Read）。
若 T1 最後 Rollback，T2 其實讀到的是「不存在的資料狀態」，導致資料不一致或邏輯錯誤。
這個狀況在 READ UNCOMMITTED（讀未提交）的隔離等級下會發生。
6-10 說明 SAVEPOINT 與 ROLLBACK TO SAVEPOINT 的應用。
SAVEPOINT 是在一個交易（Transaction）中設定一個「保存點」，類似於「檢查點」或「書籤」，允許你只回滾到該保存點，而不必回滾整個交易。
語法 : SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT 是只回滾（Rollback）到指定的保存點，該保存點之後的操作會被撤銷，但之前已完成的操作仍保留。
語法 : ROLLBACK TO SAVEPOINT savepoint_name;
START TRANSACTION;
-- 第一步：先減少 10 個庫存
UPDATE product SET stock = stock - 10 WHERE product_id = 1; -- stock = 90
SAVEPOINT step1;
-- 第二步：嘗試減少 20 個庫存
UPDATE product SET stock = stock - 20 WHERE product_id = 1; -- stock = 70
SAVEPOINT step2;
-- 發現第二步驟有問題，回滾到 step1
ROLLBACK TO SAVEPOINT step1; -- 回到 stock = 90
-- 第三步：改成減少 5 個庫存
UPDATE product SET stock = stock - 5 WHERE product_id = 1; -- stock = 85
-- 最終提交交易
COMMIT;
7-1 請說明儲存程序（Stored Procedure） 與儲存函式（Stored Function） 的差異與使用情境。
儲存程序是一組可執行的 SQL 語句，用於處理複雜的業務邏輯，特點是可以同時執行多步驟操作、支援交易控制，並且可以回傳多筆結果集。
它可以有三種類型的參數：輸入參數（IN）、輸出參數（OUT）、輸入輸出參數（INOUT）。 因為是流程導向的設計，所以無法直接用在 SELECT 或 WHERE 條件中，必須透過 CALL 呼叫。
適合的情境包含：
1. 當你需要一次完成多筆資料的更新、刪除或插入，例如建立訂單時，同時更新訂單表、扣減庫存、寫入日誌。
2. 當你需要批次處理，例如每天自動生成報表或夜間資料整理。
3. 當你需要確保整個交易具有原子性，例如若任何一步發生錯誤則必須回滾。
範例：建立一個處理訂單的儲存程序，先檢查庫存，再扣庫存，最後紀錄日誌。
DELIMITER $$
CREATE PROCEDURE process_order(IN p_product_id INT, IN p_qty INT)
BEGIN
DECLARE current_stock INT;
SELECT stock INTO current_stock FROM product WHERE product_id = p_product_id;
IF current_stock >= p_qty THEN
UPDATE product SET stock = stock - p_qty WHERE product_id = p_product_id;
INSERT INTO stock_log(product_id, change_qty, log_time)
VALUES(p_product_id, -p_qty, NOW());
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存不足';
END IF;
END$$
DELIMITER ;
CALL process_order(1, 5);
7-2 MySQL 的流程控制語法中，IF ... THEN、CASE、LOOP、WHILE 有什麼用途？請各舉一個簡單範例。
IF ... THEN 用於根據條件判斷執行不同的語句，可搭配 ELSEIF、ELSE。
範例 檢查庫存是否足夠，並輸出不同訊息。
DELIMITER $$
CREATE PROCEDURE check_stock(IN p_stock INT)
BEGIN
IF p_stock = 0 THEN
SELECT '缺貨' AS status;
ELSEIF p_stock < 10 THEN
SELECT '庫存不足' AS status;
ELSE
SELECT '庫存充足' AS status;
END IF;
END$$
DELIMITER ;
CALL check_stock(5);
IF ... THEN → 條件分支，靈活處理各種條件。
CASE → 適合固定值的多分支選擇，比 IF 更簡潔。
LOOP → 無條件循環，須自行控制跳出時機。
WHILE → 有明確條件的迴圈，條件不成立時自動結束。
7-3 請說明什麼是觸發器（Trigger）？你會在什麼場景下使用它？
觸發器（Trigger） 是一種特殊的資料庫物件，會在特定事件（INSERT、UPDATE、DELETE）發生時，自動被觸發執行預先定義好的 SQL 程式碼，無需手動呼叫。
特點如下：
1. 自動執行：不需要程式或使用者主動呼叫。
2. 與資料操作綁定：只能在表格上的 DML 操作發生時觸發。
3. 可以在觸發前（BEFORE）或觸發後（AFTER）執行。
4. 可以讀取或修改 NEW（新值）和 OLD（舊值）資料（INSERT 無 OLD、DELETE 無 NEW）。
觸發器通常用於資料一致性、自動化處理、審計等場景，常見應用如下：
1. 自動維護衍生資料（自動更新相關表格） 例如，當庫存表更新時，自動同步更新庫存異動紀錄表。
2. 更新庫存時，自動寫入「庫存異動日誌」。 審計與紀錄操作日誌（Audit Log） 例如，當使用者刪除訂單時，自動記錄刪除的操作與時間。
3. 維護資料完整性（不建議取代約束，但偶爾使用） 例如，當更新資料超過合理範圍時，阻止操作或自動校正。
4. 自動計算欄位值 例如，當插入訂單時，自動計算訂單總額。
簡單範例：更新商品庫存時，自動插入一筆庫存異動紀錄
DELIMITER $$
CREATE TRIGGER after_update_product_stock
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
-- 只有當庫存變動時才記錄
IF OLD.stock <> NEW.stock THEN
INSERT INTO stock_log(product_id, old_stock, new_stock, log_time)
VALUES(NEW.product_id, OLD.stock, NEW.stock, NOW());
END IF;
END$$
DELIMITER ;
7-4 請寫出一個 AFTER INSERT 觸發器：當 orders 新增訂單時，自動將 order_items 中的商品價格複製到備份表 order_items_log。
訂單明細表（order_items）
CREATE TABLE order_items (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
product_price DECIMAL(10,2) NOT NULL,
quantity INT NOT NULL
);
7-5 儲存函式中是否能使用 INSERT 語句？為什麼？若不能，有什麼替代方式？
在 MySQL 中，儲存函式不允許執行 INSERT、UPDATE、DELETE 等會修改資料的語句。
原因說明
1. 函式設計目的不同
儲存函式的設計是為了進行純運算與回傳單一值，它應該是無副作用（No Side Effects）的，方便直接用在 SELECT、WHERE、ORDER BY 等 SQL 查詢中。 如果允許函式執行資料修改，會導致查詢時就可能改動資料，造成預期外的副作用。
2. 避免邏輯混亂與效能問題
如果在查詢中大量呼叫函式（例如 SELECT function_name() FROM big_table），而函式內又執行資料修改，可能會反覆寫入資料或引發鎖衝突，嚴重影響效能及交易一致性。
3. MySQL 安全限制
在 MySQL 中，儲存函式預設要求為 DETERMINISTIC（確定性） 或至少標示 READS SQL DATA。若函式包含修改資料的語句，會被判定為 NO SQL / READS SQL DATA 規範違反，因此不允許。
如果你需要在「運算邏輯」的同時執行 INSERT，可以改用以下方法：
1. 改用儲存程序（Stored Procedure）
2. 使用觸發器（Trigger）
3. 先用函式計算，再由應用層負責插入
7-6 使用 IN, OUT, INOUT 參數的差異是什麼? 請舉範例說明。
IN 參數
用途：用來傳入值給儲存程序，程序內可讀取，但無法回傳給呼叫端（即使在程序內修改該變數）。
特性：呼叫者傳入 → 程序只讀取使用。
OUT 參數
用途：用來從儲存程序回傳值給呼叫端。
特性：呼叫時不需要給初始值，儲存程序內必須給它賦值，呼叫端執行完後可取得該值。
INOUT 參數
用途：既可以傳入值，也可以在儲存程序內被修改後再回傳給呼叫端。
特性：呼叫者可先給初始值，程序執行後會回傳修改後的值。
範例
DELIMITER $$
CREATE PROCEDURE demo_in_out_inout(
IN p_id INT, -- IN：傳入產品ID
OUT p_stock INT, -- OUT：回傳該產品庫存
INOUT p_adjust INT -- INOUT：傳入調整量並回傳更新後的庫存
)
BEGIN
-- 1. OUT：查詢目前庫存
SELECT stock INTO p_stock FROM product WHERE product_id = p_id;
-- 2. INOUT：根據調整量更新庫存，並回傳最新庫存
UPDATE product
SET stock = stock + p_adjust
WHERE product_id = p_id;
SELECT stock INTO p_adjust FROM product WHERE product_id = p_id;
END$$
DELIMITER ;
8-1 請列出幾個常見的 MySQL 資料庫管理工具。
1. phpMyAdmin
類型：Web 介面管理工具
特色：
最普及的 MySQL 管理工具之一，基於 PHP 開發。
適合快速執行基本 CRUD 操作、匯入匯出、備份還原。
免費、輕量，特別適合在 共享主機（Shared Hosting） 或 中小型專案使用。
適用對象：不熟悉 CLI 的初學者或需要快速操作的使用者。
2. MySQL Workbench
類型：官方桌面應用程式
特色：
由 MySQL 官方提供。
支援資料庫設計（ERD）、SQL 編輯器、效能監控、使用者權限管理。
較適合進階使用者與 DBA。
適用對象：開發者、DBA、需要設計與維護中大型資料庫的團隊。
3. HeidiSQL
類型：Windows 桌面應用程式
特色： 輕量、免費，支援 MySQL/MariaDB。 適合快速查詢、批次操作與匯出匯入。
適用對象：Windows 使用者、需要簡單快速操作的開發者。
4. CLI 工具（Command Line Interface）
工具名稱：mysql（官方提供）
特色： 最基本且最穩定的管理方式。 適合在 伺服器環境（特別是 Linux）中進行操作。 支援腳本自動化、批次作業。
適用對象：熟悉指令操作的 DBA 與進階使用者。
8-2 如何建立一個新的資料庫與使用者，並賦予該使用者只對特定資料庫有完全權限？請寫出 SQL。
建立新的資料庫
CREATE DATABASE myshop
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
8-3 MySQL 中如何查看目前有哪些使用者？如何查看某個使用者擁有哪些權限？
1. 查看目前有哪些使用者
SELECT user, host FROM mysql.user;
2. 查看某個使用者擁有哪些權限
SHOW GRANTS FOR '使用者名稱'@'主機';
例如，查看 app_user 這個使用者：
SHOW GRANTS FOR 'app_user'@'%';
8-4 如何修改現有使用者密碼？
可以使用以下三種方法修改現有使用者密碼:
ALTER USER '使用者名稱'@'主機' IDENTIFIED BY '新密碼';
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass123!';
SET PASSWORD FOR '使用者名稱'@'主機' = PASSWORD('新密碼');
8-5 如何查詢資料庫當前的連線數與最大連線上限？
1. 查詢當前連線數
方法一：SHOW STATUS
SHOW STATUS LIKE 'Threads_connected';
9-1 如何在 PHP 中使用 mysqli 連線至 MySQL？請寫出一段基本連線程式碼。
<?php
// 設定資料庫連線參數
$host = "localhost";
// 資料庫主機名稱
$username = "root";
// 資料庫帳號
$password = "123456";
// 資料庫密碼
$database = "test_db";
// 資料庫名稱
// 建立 mysqli 連線
$conn = new mysqli($host, $username, $password, $database);
// 檢查連線是否成功
if ($conn->connect_error) { die("連線失敗: " . $conn->connect_error); }
echo "連線成功！";
// 使用完畢後關閉連線
$conn->close();
?>
更多參考資料 : MySQL + PHP 範例實務操作
9-2 在使用 PHP 傳送使用者表單資料時，如何防止 SQL injection？
什麼是 SQL injection? 他是利用程式與SQL語法的漏洞，入侵登入系統的手法，也稱SQL隱碼或SQL注碼。登入系統的程式會要求使用者輸入帳號密碼，然後把這些帳號密碼字串放在SQL語法中，來驗證該帳號密碼是否正確。直接把使用者輸入的字串搭上SQL語法，就是一個大風險，因為會讓原本的程式邏輯誤判，而讓入侵者直接驗證過關，這就是SQL injection。
例如:
假設使用者輸入帳號密碼是 $account、$password，進入程式後就變成
"SELECT * FROM users WHERE account ='". $account."' AND password='".$password."'"
以上這個字串就是造成SQL injection的主要原因，因為可能變成這樣
"SELECT * FROM users WHERE account ='admin' OR '1'='1' AND password='123'"
(以上紅色部分就是使用者輸入帳號及密碼)
如此一來，執行這個SQL當然就是 TRUE，就通過了帳密檢查。
在 PHP 使用 mysqli 傳送使用者表單資料時，最重要的防禦 SQL Injection（SQL 注入） 的方法是使用 Prepared Statements（預備語句 / 預處理語法） 搭配 參數綁定（Parameter Binding）。
<?php
// 1. 建立連線
$host = "localhost";
$username = "root";
$password = "123456";
$database = "test_db";
$conn = new mysqli($host, $username, $password, $database);
// 檢查連線
if ($conn->connect_error) { die("連線失敗: " . $conn->connect_error); }
$conn->set_charset("utf8mb4");
// 2. 接收使用者輸入（POST）
$user = $_POST['username'];
$pass = $_POST['password'];
// 3. 使用預備語句（Prepared Statement）
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
// 4. 綁定參數（s=字串）
$stmt->bind_param("ss", $user, $pass);
// 5. 執行 SQL
$stmt->execute();
// 6. 獲取結果
$result = $stmt->get_result();
if ($result->num_rows > 0) { echo "登入成功！"; }
else { echo "帳號或密碼錯誤"; }
// 7. 關閉
$stmt->close();
$conn->close();
?>
如果你直接把使用者輸入拼接到 SQL 中（易被 SQL Injection 攻擊）
$sql = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";
$result = $conn->query($sql);
若有人輸入 admin' OR '1'='1，SQL 會變成：
SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = ''
這樣子就發生了SQL injection，讓駭客在不知道密碼的情況下，以管理者的帳號登入成功。
那麼如何避免SQL injection呢? 坊間有很多方法，但是我認為最簡單的方法就是把使用者輸入的字串「除去引號」，當然系統要先規定帳號密碼不允許使用引號，這樣就不會發生SQL injection了。
不過如果你的SQL跟上面範例不一樣，可能就要有不同的評估。
9-3 請說明什麼是 RESTful API，並舉例說明如何使用 API 把外部資料寫入 MySQL？
RESTful API (Representational State Transfer Application Programming Interface) 是一種基於 HTTP 協定的 API 設計風格，強調資源導向與統一的操作方式。
有三個主要特徵 :
(1) 使用標準 HTTP 方法
GET：取得資料
POST：新增資料
PUT / PATCH：更新資料
DELETE：刪除資料
(2) 以資源（Resource）為中心
GET /users → 取得所有使用者
POST /users → 新增使用者
PUT /users/123 → 更新 ID=123 的使用者
DELETE /users/123 → 刪除 ID=123 的使用者
(3) 回傳 JSON（最常見）
輕量且適合跨平台交換資料。
範例 :
<?php
// 設定回應格式為 JSON
header("Content-Type: application/json; charset=UTF-8");
// 允許跨來源請求（可視需求開放）
// header("Access-Control-Allow-Origin: *");
// 只接受 POST 方法
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
http_response_code(405);
echo json_encode(["error" => "只允許 POST 請求"]);
exit;
}
// 取得外部傳入的 JSON 並轉成 PHP 陣列
$data = json_decode(file_get_contents("php://input"), true);
// 驗證必填欄位
if (!isset($data['username']) || !isset($data['email'])) {
http_response_code(400);
echo json_encode(["error" => "缺少必要欄位"]);
exit;
}
// 資料庫連線參數
$host = "localhost";
$db_user = "root";
$db_pass = "123456";
$db_name = "test_db";
// 建立 MySQLi 連線
$conn = new mysqli($host, $db_user, $db_pass, $db_name);
if ($conn->connect_error) {
http_response_code(500);
echo json_encode(["error" => "資料庫連線失敗"]);
exit;
}
$conn->set_charset("utf8mb4");
// 使用 Prepared Statement 防止 SQL Injection
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $data['username'], $data['email']);
if ($stmt->execute()) {
echo json_encode([
"success" => true,
"message" => "新增成功",
"insert_id" => $stmt->insert_id
]);
} else {
http_response_code(500);
echo json_encode(["error" => "新增失敗"]);
}
// 關閉連線
$stmt->close();
$conn->close();
?>
9-4 GA4 中的事件追蹤如何透過 Google Tag Manager 將購買資料送至 GA4 並同時寫入 MySQL？
datalayer (資料層) 本質是 JavaScript 陣列，是網站與GTM (Google Tag Manager)傳遞資料的方法，然後可以再由GTM傳給Google Analytics。
消費者在網頁上購買時，由GTM觸發事件，然後透過datalayer傳遞給GA。並且可由GTM觸發php將datalayer傳遞給MySQL資料表。
例如
網頁以push的方式給datalayer
dataLayer.push({ 'event': 'purchase', 'transaction_id': 'T12345', 'value': 5000, 'currency': 'TWD' });
GTM 就會根據你設定的觸發條件，將資料傳送到 Google Analytics、GA4、Facebook Pixel 等工具。
更多參考資料 : 如何準備給工程師的 GA4 電子商務 Data Layer 資料層文件？
9-5 請說明如何將 IoT 裝置（如溫度感測器）的數據透過 HTTP 傳送並存入 MySQL？
原則上就是先建立好要儲存的MySQL資料表單，然後讓IoT設備發送 HTTP POST（或 GET）給Web伺服器，然後Web伺服器接收到之後丟到MySQL中。
步驟-1 : 建立MySQL資料表單
CREATE TABLE temperature_log ( id INT AUTO_INCREMENT PRIMARY KEY, device_id VARCHAR(50), temperature DECIMAL(5,2), recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP );
步驟-2 : IoT 裝置端（以 ESP8266 為例，使用 Arduino C）
#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>
const char* ssid = "你的WiFi";
const char* password = "WiFi密碼";
const char* serverUrl = "http://your-server.com/upload.php";
void setup() {
Serial.begin(115200);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print("."); }
Serial.println("WiFi connected");
}
void loop() {
float temperature = 28.5; // 假設感測器回傳的值
if (WiFi.status() == WL_CONNECTED) {
HTTPClient http;
http.begin(serverUrl);
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
String postData = "device_id=esp01&temperature=" + String(temperature);
int httpResponseCode = http.POST(postData);
if (httpResponseCode > 0) {
Serial.println("送出成功"); }
else {
Serial.println("送出失敗");
}
http.end();
} delay(60000); // 每分鐘送一次
}
步驟-3 : PHP 接收 HTTP 資料並寫入 MySQL（使用 mysqli）
<?php
// 接收資料（假設為 POST 傳遞）
$device_id = $_POST['device_id'];
$temperature = $_POST['temperature'];
// 建立資料庫連線 $conn = new mysqli("localhost", "username", "password", "iot_db");
if ($conn->connect_error) { die("連線失敗: " . $conn->connect_error); }
// 寫入資料 $stmt = $conn->prepare("INSERT INTO temperature_log (device_id, temperature) VALUES (?, ?)");
$stmt->bind_param("sd", $device_id, $temperature);
$stmt->execute();
echo "OK";
$conn->close();
?>
上面的範例有些地方需要修改為適合你的環境。
如果嫌上面太多問題了，希望我們可以抓幾個最重要的問題，以下再幫你各位精選幾個必懂的問題 :
(1) 假設你收集了所有的需求，請敘述一下你會如何設計MySQL資料庫及資料表?
(2) 設計MySQL資料庫及資料表時，請敘述一下你會如何設計索引?
(3) 給你一個情境 (依照面試公司需求)，請敘述一下你會用什麼方式避免資料發生錯誤?
(4) 在以上的設計過程，你會如何使用Stored Procedure及Stored Function?
(5) 在安全、效率、資源三個因素下，你會如何規劃備份計畫?
以上的問題，後續再給大家完整的參考資料 (其實上面都有提到了)。
0 留言