MySQL 基礎入門全攻略：從架構原理到高級查詢實戰

1. MySQL 邏輯架構

• 連接層：每個客戶端連接一個線程，認證、權限檢查
• 服務層：查詢解析、優化、緩存、內置函數、跨引擎功能（視圖、存儲過程）
• 存儲引擎層：InnoDB（事務、行鎖）、MyISAM（全文索引）、Memory 等，通過統一 API 與服務層交互

核心認知：事務由存儲引擎實現，服務層不管理事務；InnoDB 死檢測策略為回滾持有最少行鎖的事務。

2. 基礎操作

mysql -h host -u user -p
USE db;
SHOW DATABASES/TABLES/COLUMNS FROM tbl;
SHOW CREATE TABLE tbl;
SHOW STATUS/VARIABLES;

3. 數據檢索 (SELECT)

列投影與去重

SELECT col1, col2 FROM tbl;
SELECT DISTINCT col FROM tbl;

分頁與排序

SELECT * FROM tbl ORDER BY id DESC LIMIT 10;
SELECT * FROM tbl ORDER BY id DESC, name ASC LIMIT 5, 10;  -- 多列排序

4. 過濾條件 (WHERE)

邏輯組合：AND 優先於 OR，用括號改變優先級。

5. 聚合與分組

聚合函數

SELECT COUNT(*), AVG(score), MAX(age), MIN(age), SUM(salary) FROM tbl;
SELECT COUNT(DISTINCT dept) FROM tbl;

GROUP BY 與 HAVING

SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING AVG(salary) > 5000;

• WHERE 行級過濾（分組前）
• HAVING 組級過濾（分組後）
• GROUP BY 列必須出現在 SELECT 列表或為聚合函數參數

6. 子查詢

SELECT * FROM tbl WHERE id IN (SELECT pid FROM other_tbl WHERE status=1);

注意：子查詢返回列數需與外層匹配，通常為單列。

7. 多表聯結

內聯結 (INNER JOIN)

SELECT v.name, p.name, p.price
FROM vendors v INNER JOIN products p ON v.vend_id = p.vend_id;

外聯結

-- 左外聯結：保留左表所有行
SELECT c.cust_id, o.order_num
FROM customers c LEFT OUTER JOIN orders o ON c.cust_id = o.cust_id;

自聯結

SELECT p1.prod_id, p1.prod_name
FROM products p1, products p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

8. 組合查詢 (UNION)

(SELECT col FROM tbl1 WHERE cond1)
UNION
(SELECT col FROM tbl2 WHERE cond2)
ORDER BY col;

• 列數、類型、順序必須一致
• 默認去重，UNION ALL 保留重複
• ORDER BY 僅允許最後一條 SELECT 後

9. 數據修改

-- 插入
INSERT INTO tbl (a,b) VALUES (1,2), (3,4);
INSERT INTO tbl SELECT * FROM other_tbl;

-- 更新/刪除（必帶 WHERE）
UPDATE tbl SET a=10 WHERE id=1;
DELETE FROM tbl WHERE id=1;

10. 表結構管理

CREATE TABLE vendors (
    vend_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    vend_name CHAR(50) NOT NULL,
    vend_address CHAR(50) NULL DEFAULT '*'
) ENGINE=InnoDB;

ALTER TABLE tbl ADD COLUMN c INT;
ALTER TABLE tbl DROP COLUMN c;
ALTER TABLE tbl MODIFY c VARCHAR(20) NOT NULL;
DROP TABLE tbl;
RENAME TABLE old TO new;

引擎選擇：InnoDB（事務、行鎖）、MyISAM（全文、讀多寫少）、Memory（臨時表、極速）。

11. 視圖

CREATE VIEW v AS SELECT id, name FROM tbl WHERE status=1;
SHOW CREATE VIEW v;
DROP VIEW v;

視圖本質是封裝的 SELECT，簡化複雜查詢、安全隔離、格式轉換。包含聚合、聯結、子查詢、DISTINCT 的視圖不可更新。