MySQL 从 0 到 1 完整教程

包含实战操作顺序、SQL核心语法、索引优化与生产规范

📑 学习导航

🗺️ 新手必读：MySQL 实战操作顺序指南

写 SQL 不是想到什么写什么，而是遵循“库 → 表 → 数据 → 查询 → 优化”的自顶向下设计流。每次开发新功能时，请严格按以下 6 步执行：

Step 1: 创建/选择数据库

⌨️ CREATE / USE DATABASE

明确数据归属，设置正确的字符集（推荐 utf8mb4）。

💡 字符集选错会导致emoji和生僻字存储失败。

Step 2: 设计并创建表

⌨️ CREATE TABLE

定义字段类型、主键、非空约束、默认值及注释。

💡 建表时就要想好索引，后期加索引成本更高。

Step 3: 插入测试数据

⌨️ INSERT INTO

录入具有代表性的样本数据，覆盖边界情况。

💡 没有数据的表无法验证查询逻辑是否正确。

Step 4: 编写业务查询

⌨️ SELECT / UPDATE / DELETE

实现增删改查，先用小数据集验证结果正确性。

💡 UPDATE/DELETE 务必先写 WHERE 再执行！

Step 5: 分析与优化

⌨️ EXPLAIN + 索引调整

用 EXPLAIN 查看执行计划，确认是否走了预期索引。

💡 不要凭感觉优化，一切以执行计划为准。

Step 6: 权限与安全加固

⌨️ GRANT / REVOKE

为应用创建专用账号，最小化权限，禁止root直连。

💡 生产环境永远不要用 root 账号运行应用。
⚠️ 黄金法则： 先设计后编码，先验证后上线。任何 DDL/DML 操作前必须先备份。
🔍 排错顺序： 结果不对时按 WHERE条件 → JOIN关系 → 字段类型 → 索引命中 → 数据本身 逐层排查。

🔵 第一阶段：环境搭建与基础 CRUD

目标：安装 MySQL，掌握最基本的增删改查语句。

1. 数据库与表的创建

-- 创建数据库（指定字符集和排序规则）
CREATE DATABASE IF NOT EXISTS myblog 
DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE myblog;

-- 创建用户表
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0禁用 1正常',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB COMMENT='用户表';

2. 基础 CRUD 操作

-- 插入
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- 查询
SELECT * FROM users WHERE status = 1;

-- 更新（⚠️ 必须带 WHERE）
UPDATE users SET email = 'new@example.com' WHERE username = 'alice';

-- 删除（⚠️ 必须带 WHERE）
DELETE FROM users WHERE username = 'alice';

🟠 第二阶段：条件查询与常用函数

目标：掌握复杂查询能力，满足日常业务需求。

1. 条件过滤与排序分页

-- 多条件 + 模糊查询 + 排序 + 分页
SELECT id, username, email, created_at
FROM users
WHERE status = 1 
  AND username LIKE '%ali%'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;  -- 第1页，每页10条

2. 聚合与分组

-- 统计各状态的用户数量
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status
HAVING user_count > 5;

3. 常用内置函数

类别函数用途
字符串CONCAT(), SUBSTRING(), LENGTH()拼接、截取、长度
日期NOW(), DATE_FORMAT(), DATEDIFF()当前时间、格式化、天数差
条件IFNULL(), CASE WHEN空值处理、条件分支
聚合COUNT(), SUM(), AVG(), MAX()统计分析

🟢 第三阶段：表设计与多表关联

目标：理解范式与反范式，掌握 JOIN 查询。

1. 外键与关联表设计

-- 文章表（关联用户）
CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL COMMENT '作者ID',
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),       -- ⚡ 外键字段必建索引
    CONSTRAINT fk_article_user 
        FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='文章表';

2. 多表 JOIN 查询

-- 查询文章列表及作者名
SELECT a.id, a.title, u.username AS author, a.created_at
FROM articles a
INNER JOIN users u ON a.user_id = u.id
WHERE u.status = 1
ORDER BY a.created_at DESC
LIMIT 20;
💡 设计原则： 遵循三范式保证数据一致性；在高并发读场景下适当反范式（冗余字段）换取查询性能。外键约束在阿里等大厂规范中建议由应用层保证，而非数据库层。

🔴 第四阶段：索引原理与性能优化

目标：理解 B+Tree，会用 EXPLAIN 诊断慢查询。

1. 索引核心原则

原则说明反面案例
最左前缀联合索引 (a,b,c) 只有查询包含 a 时才生效WHERE b=1 AND c=2 ❌
避免函数运算索引列上做计算会导致索引失效WHERE YEAR(created_at)=2024 ❌
区分度优先将高区分度字段放联合索引左侧性别+身份证号 ❌
覆盖索引SELECT 的字段都在索引中，无需回表SELECT * 无法覆盖 ❌

2. EXPLAIN 执行计划解读

EXPLAIN SELECT * FROM articles WHERE user_id = 100;

-- 重点关注以下列：
-- type:    ref/range ✅ | ALL ❌(全表扫描)
-- key:     实际使用的索引名（NULL表示未走索引）
-- rows:    预估扫描行数（越小越好）
-- Extra:   Using index ✅(覆盖索引) | Using filesort ❌(额外排序)

🟣 第五阶段：事务、锁与生产规范

1. 事务 ACID 与隔离级别

-- 转账示例：原子性保证
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;  -- 或 ROLLBACK;

-- MySQL默认隔离级别：REPEATABLE READ（可重复读）
-- 解决幻读问题依靠 MVCC + Next-Key Lock

2. 生产环境铁律

📚 核心知识体系总结

阶段核心关键词重点掌握内容
基础CRUD, DDL建库建表, 增删改查, 数据类型
查询JOIN, 聚合, 分页多表关联, GROUP BY, 窗口函数
设计范式, 外键, ER图三范式, 反范式权衡, 关联设计
优化B+Tree, EXPLAIN索引原则, 执行计划, 慢查询分析
生产事务, 锁, 规范ACID, MVCC, 连接池, 安全加固