核心概念速覽
| 術語 | 說明 | 對應現實 |
|---|
| Database | 數據庫 | 一個檔案櫃 |
| Table | 表 | 櫃裡的一個抽屜 |
| Column/Field | 列/字段 | 表頭欄位名 |
| Row/Record | 行/記錄 | 一行具體數據 |
| Primary Key | 主鍵 | 身份證號（唯一標識） |
| Foreign Key | 外鍵 | 關聯另一張表的主鍵 |
| Index | 索引 | 目錄/書籤（加速查找） |
連接與基礎操作
# 登入
mysql -h 127.0.0.1 -u root -p
# 退出
exit; -- 或 quit、\q
庫表管理 (DDL)
數據庫
CREATE DATABASE IF NOT EXISTS learn_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE learn_db;
SHOW TABLES;
DESC users; -- 查看表結構
創建表範例
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用戶名',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '郵箱',
password_hash CHAR(60) NOT NULL COMMENT 'bcrypt 哈希',
status TINYINT NOT NULL DEFAULT 1 COMMENT '1啟用 0禁用',
profile_json JSON COMMENT '擴展屬性',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_status (status),
INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';
核心 CRUD 操作
INSERT - 新增
-- 指定欄位（推薦）
INSERT INTO users (username, email, password_hash) VALUES ('john', 'john@example.com', '$2y$10$...');
-- 批次插入
INSERT INTO users (username, email, password_hash) VALUES
('alice', 'alice@example.com', '$2y$10$...'),
('bob', 'bob@example.com', '$2y$10$...');
-- 衝突處理
INSERT INTO users (username, email, login_count) VALUES ('john', 'john@example.com', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;
SELECT - 查詢
-- 基礎
SELECT id, username, email FROM users WHERE status = 1;
-- 去重
SELECT DISTINCT status FROM users;
-- 排序分頁
SELECT * FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 10 OFFSET 20;
-- 聚合
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status HAVING COUNT(*) > 5;
-- 連接查詢
SELECT u.username, p.bio FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
WHERE u.status = 1;
UPDATE - 更新
UPDATE users SET status = 0, updated_at = NOW() WHERE id = 123;
-- 多表更新
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.status = 2
WHERE o.total_amount > 10000;
DELETE - 刪除
-- 軟刪除（推薦）
UPDATE users SET status = 0, deleted_at = NOW() WHERE id = 123;
-- 硬刪除
DELETE FROM users WHERE id = 123;
-- 限制刪除行數
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;
WHERE 條件構建
| 操作符 | 示例 | 說明 |
|---|
| =, !=, <> | status = 1 | 等於/不等於 |
| >, >=, <, <= | age >= 18 | 範圍比較 |
| IN / NOT IN | id IN (1,2,3) | 集合匹配 |
| BETWEEN | age BETWEEN 18 AND 60 | 閉區間 |
| LIKE | name LIKE '張%' | 模糊匹配（% 多字符，_ 單字符） |
| IS NULL / IS NOT NULL | deleted_at IS NULL | 空值判斷 |
| AND / OR | status=1 AND age>18 | 邏輯組合 |
索引基礎與優化
索引類型
-- 普通索引
CREATE INDEX idx_email ON users(email);
ALTER TABLE users ADD INDEX idx_email (email);
-- 唯一索引
CREATE UNIQUE INDEX uk_email ON users(email);
-- 組合索引（最左前綴原則）
CREATE INDEX idx_status_created ON users(status, created_at);
-- 全文索引（MySQL 5.6+ InnoDB 支援）
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
索引設計黃金法則
- 最左前綴：組合索引
(a,b,c) 支援 a、a,b、a,b,c 查詢
- 高選擇性優先：區分度高的列放前面（如 email > status）
- 覆蓋索引：查詢只需掃描索引不回表
SELECT id, email FROM users WHERE email = ?
- 避免索引失效：不在索引列上做運算/函數/類型轉換/
!=/LIKE '%...'
EXPLAIN 實戰分析
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
-- 關鍵列：
-- type: const/ref/range/index/ALL（越左越好）
-- key: 實際使用的索引
-- rows: 預估掃描行數
-- Extra: Using index/Using where/Using filesort 等
常用函數速查
| 分類 | 函數 | 示例 |
|---|
| 字符串 | CONCAT, SUBSTRING, TRIM, REPLACE, LENGTH | CONCAT(first_name, ' ', last_name) |
| 日期時間 | NOW, DATE, DATE_FORMAT, DATEDIFF, DATE_ADD | DATE_FORMAT(created_at, '%Y-%m') |
| 數學 | ROUND, CEIL, FLOOR, RAND, ABS | ROUND(price, 2) |
| 聚合 | COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT | GROUP_CONCAT(tag SEPARATOR ',') |
| 條件 | IF, CASE, COALESCE, NULLIF | IF(status=1, '啟用', '禁用') |
| JSON (5.7+) | JSON_EXTRACT, ->, ->>, JSON_ARRAY, JSON_OBJECT | profile_json->>'$.avatar' |
進階學習路線圖
- 事務與鎖機制（ACID、隔離級別、死鎖排查）
- 主從複制與讀寫分離架構
- 分區表、分庫分表中間件
- 效能調優：配置參數、慢查詢分析、Schema 設計
- 高可用：MHA、Orchestrator、InnoDB Cluster
