一、環境準備與連線
- 安裝 WAMP / XAMPP / Docker MySQL
- 連線命令:
mysql -u root -p -h localhost
二、庫級操作
SHOW DATABASES;
USE db_name;
CREATE DATABASE db_name CHARSET utf8mb4;
DROP DATABASE db_name;
三、表級操作
3.1 基礎 DDL
SHOW TABLES;
DESC table_name;
SHOW CREATE TABLE table_name;
CREATE TABLE user (
id INT AUTO_INCREMENT,
name VARCHAR(20) NOT NULL DEFAULT '',
age TINYINT UNSIGNED NOT NULL DEFAULT 0,
INDEX idx_id (id)
) ENGINE=InnoDB CHARSET=utf8mb4;
3.2 修改表結構 (ALTER TABLE)
-- 增加欄位
ALTER TABLE tb ADD col_name INT NOT NULL DEFAULT 0;
-- 修改欄位(含重命名)
ALTER TABLE tb CHANGE old_col new_col VARCHAR(50) NOT NULL DEFAULT '';
-- 刪除欄位
ALTER TABLE tb DROP col_name;
-- 主鍵操作
ALTER TABLE tb ADD PRIMARY KEY (id);
ALTER TABLE tb DROP PRIMARY KEY;
-- 索引操作
ALTER TABLE tb ADD UNIQUE INDEX uk_name (name);
ALTER TABLE tb ADD FULLTEXT INDEX ft_content (content);
ALTER TABLE tb DROP INDEX idx_name;
-- 清空表(重置自增 ID)
TRUNCATE TABLE tb;
四、數據類型詳解
| 類別 | 類型 | 存儲/範圍 | 適用場景 |
| 整數 | TINYINT | 1B / -128~127 | 狀態碼、布爾值 |
| SMALLINT | 2B / -32K~32K | 較小計數 |
| MEDIUMINT | 3B / -8M~8M | 中等計數 |
| INT | 4B / -21E~21E | 主鍵、通用計數 |
| BIGINT | 8B / -9E18~9E18 | 大數據 ID、時間戳 |
| 浮點/定點 | FLOAT(M,D) | 4B / 7 位精度 | 科學計算、非精確金額 |
| DECIMAL(M,D) | M+2 B / 精確 | 金融金額、精確計算 |
| 字符串 | CHAR(M) | 固定長 M | MD5、UUID、固定代碼 |
| VARCHAR(M) | 實際長度+1/2B | 通用變長文本 |
| TEXT/BLOB | 65KB~4GB | 長文本、二進制文件 |
| 日期時間 | DATE | 3B / 1000-01-01~9999-12-31 | 生日、截止日 |
| DATETIME | 8B / 秒級 | 創建/更新時間 |
| TIMESTAMP | 4B / 1970~2038 | 自動更新時間戳 |
| YEAR | 1B / 1901~2155 | 年份存儲 |
五、增刪改查 (CRUD) 核心語法
-- 插入
INSERT INTO tb (col1, col2) VALUES (val1, val2);
INSERT INTO tb VALUES (val1, val2), (val3, val4); -- 批次
-- 更新
UPDATE tb SET col1=val1, col2=val2 WHERE condition;
-- 刪除
DELETE FROM tb WHERE condition;
-- 查詢完整結構
SELECT [DISTINCT] select_list
FROM table_list
[WHERE condition]
[GROUP BY group_list]
[HAVING having_condition]
[ORDER BY order_list]
[LIMIT offset, count];
六、條件查詢運算符
- 比較:
= != < > <= >=
- 模糊:
LIKE '%abc%'、NOT LIKE、_%(單字符)
- 集合:
IN (1,2,3)、NOT IN、BETWEEN 100 AND 500
- 空值:
IS NULL、IS NOT NULL
七、連接查詢 (JOIN)
| 類型 | 關鍵字 | 結果集特徵 |
| 內連接 | INNER JOIN | 兩表匹配行的交集 |
| 左連接 | LEFT JOIN | 左表全量 + 右表匹配行(無匹配為 NULL) |
| 右連接 | RIGHT JOIN | 右表全量 + 左表匹配行 |
SELECT g.goods_name, c.cat_name, b.brand_name
FROM ecs_goods g
LEFT JOIN ecs_category c ON g.cat_id = c.cat_id
LEFT JOIN ecs_brand b ON g.brand_id = b.brand_id
WHERE g.cat_id = 4;
八、子查詢
WHERE 型(標量/列子查詢)
SELECT * FROM goods
WHERE goods_id = (SELECT MAX(goods_id) FROM goods);
FROM 型(派生表)
SELECT * FROM (
SELECT goods_id, cat_id, goods_name
FROM ecs_goods ORDER BY goods_id DESC
) AS t GROUP BY cat_id; -- 取每分類最新商品
九、字符集與存儲引擎
SET NAMES utf8mb4:一次性設定 client/connection/results 三個字符集變量。
- MyISAM:讀快、全文索引、不支持事務、表鎖。
- InnoDB:支持事務、行鎖、外鍵、崩潰恢復、聚簇索引(主鍵順序寫入最快)。
十、事務控制
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK;
十一、觸發器實戰
-- 下單後扣減庫存
CREATE TRIGGER tg_after_order_insert
AFTER INSERT ON `order`
FOR EACH ROW
UPDATE goods SET goods_number = goods_number - NEW.num WHERE id = NEW.gid;
-- 刪單後恢復庫存
CREATE TRIGGER tg_after_order_delete
AFTER DELETE ON `order`
FOR EACH ROW
UPDATE goods SET goods_number = goods_number + OLD.num WHERE id = OLD.gid;
-- 改單後調整庫存
CREATE TRIGGER tg_after_order_update
AFTER UPDATE ON `order`
FOR EACH ROW
UPDATE goods SET goods_number = goods_number + OLD.num - NEW.num WHERE id = OLD.gid;
十二、索引最佳實踐
- 優先為
WHERE、JOIN ON、ORDER BY 欄位建索引。
- 高選擇性(基數大)欄位索引效果最好(如
user_id、mobile)。
- 聯合索引遵循「最左前綴原則」:
INDEX idx_a_b_c (a,b,c) 可支持 a、a,b、a,b,c 查詢。
- 索引不是越多越好:每次寫入需維護所有索引,評估讀寫比。
十三、綜合練習題(節選)
點擊展開:ECSHOP 商品表實戰查詢
-- 1.1 主鍵 32
SELECT goods_id, goods_name, shop_price FROM ecs_goods WHERE goods_id = 32;
-- 1.5 第 4 或 11 分類
SELECT goods_id, cat_id, goods_name, shop_price FROM ecs_goods WHERE cat_id IN (4,11);
-- 1.9 分類 3,價格<1000 或 >3000,點擊>5
SELECT goods_id, cat_id, goods_name, shop_price, click_count
FROM ecs_goods
WHERE cat_id = 3 AND (shop_price < 1000 OR shop_price > 3000) AND click_count > 5;
-- 2.8 每分類統計
SELECT cat_id,
MAX(shop_price) AS max_price,
MIN(shop_price) AS min_price,
AVG(shop_price) AS avg_price,
SUM(goods_number) AS total_stock,
COUNT(*) AS goods_count
FROM ecs_goods GROUP BY cat_id;
-- 3.6 積壓貨款 > 20000 的分類
SELECT cat_id, SUM(goods_number * shop_price) AS stock_amount
FROM ecs_goods GROUP BY cat_id HAVING stock_amount > 20000;
-- 5.1 多表關聯
SELECT g.goods_name, c.cat_name, g.shop_price
FROM ecs_goods g
LEFT JOIN ecs_category c ON g.cat_id = c.cat_id;
十四、面試高頻題:分組統計「不及格門數 ≥ 2 的學生平均分」
-- 錯誤寫法:COUNT(score<60) 計算的是非空行數,非不及格數
SELECT name, COUNT(score<60) AS fail_cnt, AVG(score)
FROM stu GROUP BY name HAVING fail_cnt >= 2;
-- 正確寫法:SUM(score<60) 利用 TRUE=1/FALSE=0 特性
SELECT name, SUM(score < 60) AS fail_cnt, AVG(score) AS avg_score
FROM stu GROUP BY name HAVING fail_cnt >= 2;
十五、UNION 合併結果集
-- 合併並去重
SELECT * FROM ta UNION SELECT * FROM tb;
-- 合併保留重複(性能更好)
SELECT * FROM ta UNION ALL SELECT * FROM tb;
-- 合併後分組求和
SELECT id, SUM(num) FROM (
SELECT * FROM ta UNION ALL SELECT * FROM tb
) AS tmp GROUP BY id;
結語
本筆記涵蓋 MySQL 從安裝配置、基礎 DDL/DML、進階查詢、事務、觸發器、索引優化到實戰案例。建議配合官方文檔 MySQL Reference Manual 深入學習,並在測試環境大量練習,構建肌肉記憶。