《MySQL 必知必會》核心知識點整理與速查手冊

《MySQL 必知必會》核心知識點整理與速查手冊

本文整理自《MySQL 必知必會》經典入門書，涵蓋 MySQL 基礎操作、查詢語法、函數應用、資料處理、進階特性等核心內容，適合作為日常開發的快速參考手冊。

基礎連線與資料庫操作

# 連線 MySQL
mysql -u root -p

# 顯示所有資料庫
SHOW DATABASES;

# 選擇資料庫
USE mytest;

# 顯示資料表
SHOW TABLES;

# 檢視表結構
SHOW COLUMNS FROM tmall_user;
DESCRIBE tmall_user;  -- 等同效果

SHOW 指令進階用法

資料查詢 (SELECT) 核心語法

基本查詢

-- 查詢單一欄位
SELECT username FROM tmall_user;

-- 查詢多欄位
SELECT username, password, time FROM tmall_user;

-- 查詢所有欄位
SELECT * FROM tmall_user;

-- 去重查詢
SELECT DISTINCT password FROM tmall_user;

-- 限制筆數 (分頁)
SELECT username FROM tmall_user LIMIT 5;        -- 前 5 筆
SELECT username FROM tmall_user LIMIT 5, 5;     -- 第 6-10 筆 (從 0 起算)
SELECT username FROM tmall_user LIMIT 4 OFFSET 3; -- 同 LIMIT 3,4

排序 (ORDER BY)

-- 單欄位升序 (預設)
SELECT username FROM tmall_user ORDER BY username;

-- 多欄位排序
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price, prod_name;

-- 指定降序
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price DESC;

-- 多欄位混合排序
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price DESC, prod_name;  -- 價格降序，名稱升序

-- 結合 LIMIT 取極值
SELECT prod_price FROM products
ORDER BY prod_price DESC LIMIT 1;  -- 最貴商品

條件過濾 (WHERE)

-- 比較運算子
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
SELECT prod_name FROM products WHERE prod_price IS NULL;

邏輯運算子與進階過濾

-- AND / OR 組合
SELECT prod_name FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;
SELECT prod_name FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

-- LIKE 萬用字元
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';  -- s 開頭 e 結尾
-- %: 任意長度字元 (不含 NULL)
-- _: 單一字元

常用函數分類速查

字串處理函數

日期時間函數

-- 取得當前日期時間
NOW(), CURDATE(), CURTIME()

-- 日期運算
DATE_ADD(date, INTERVAL expr unit)
DATEDIFF(date1, date2)

-- 格式化輸出
DATE_FORMAT(date, format)

-- 元件擷取
YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()

-- 實用範例
SELECT username FROM tmall_user WHERE DATE(time) = '2016-09-21';
SELECT COUNT(id) FROM tmall_user 
WHERE DATE(time) BETWEEN '2016-09-01' AND '2016-09-30';
-- 或使用
SELECT COUNT(id) FROM tmall_user 
WHERE YEAR(time) = 2016 AND MONTH(time) = 9;

彙總函數 (聚合函數)

-- 進階用法
SELECT AVG(DISTINCT prod_price) FROM products WHERE vend_id = 1003;
SELECT COUNT(*), MIN(prod_price), MAX(prod_price), AVG(prod_price) FROM products;

分組查詢 (GROUP BY / HAVING)

-- 基本分組
SELECT vend_id, COUNT(*) FROM products GROUP BY vend_id;

-- 分組後過濾 (HAVING vs WHERE)
-- WHERE: 分組前過濾列 | HAVING: 分組後過濾群組
SELECT cust_id, COUNT(*) FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

-- 結合 WHERE 與 HAVING
SELECT vend_id, COUNT(*) FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

-- SELECT 子句執行順序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

子查詢與聯結

子查詢範例

-- 巢狀子查詢
SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN (
  SELECT cust_id FROM orders WHERE order_num IN (
    SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'
  )
);

-- 作為計算欄位
SELECT cust_name, cust_state,
  (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;

聯結類型

-- 內部聯結 (等值聯結)
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

-- 多表聯結
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
  AND orderitems.prod_id = products.prod_id
  AND order_num = 20005;

-- 表別名簡化
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'TNT2';

-- 自聯結 (替代子查詢)
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

-- 外部聯結 (保留無對應資料)
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

資料異動 (INSERT / UPDATE / DELETE)

-- 插入 (支援多行)
INSERT INTO tb_people(user, password, email, createtime)
VALUES ('czy','11111111','2384439266@qq.com','2016-10-10 02:36:50');

-- 更新 (務必加 WHERE)
UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

-- 刪除
DELETE FROM tb_people WHERE user='wha';

資料備份與還原

# 備份
mysqldump -uroot -p11111111 db_czy > D:\backup\db_czy.sql

# 還原
mysql -uroot -p11111111 db_czy < D:\backup\db_czy.sql

最佳實踐規範

• SQL 關鍵字大寫，資料表/欄位小寫，提升可讀性
• WHERE 條件混用 AND/OR 時，務必用括號明確分組
• 避免過度使用萬用字元 % 開頭查詢，效能較差
• 善用彙總函數替代應用層計算，資料庫內建優化更高效
• 逐步建構複雜查詢，先驗證子查詢再組合