MySQL SQL 语言完全指南：DDL、DML、DQL、窗口函数与性能优化 | 数据库开发必读

一、SQL 语言分类（宏观框架）

MySQL 的 SQL 语句分为四类，记住这个分类有助于理解后续内容：

---

二、DDL：数据库与表的管理（结构层）

2.1 数据库操作

sql
折叠复制
-- 创建数据库（指定字符集，避免乱码）
CREATE DATABASE IF NOT EXISTS myblog 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE myblog;

-- 删除数据库（慎用！）
DROP DATABASE IF EXISTS myblog;

为什么用 utf8mb4 而不是 utf8？
MySQL 的 utf8 最多存 3 字节，无法存储 emoji 和部分生僻字（如"𠮷"）。utf8mb4 是真正的 UTF-8，支持 4 字节，务必使用。

---

2.2 表操作（重点）

创建表（完整语法）

sql
折叠复制
CREATE TABLE IF NOT EXISTS `users` (
    -- 主键：自增、唯一、非空
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名（唯一）',
    `email` VARCHAR(100) NOT NULL COMMENT '邮箱',
    `age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄（0-255）',
    `salary` DECIMAL(10,2) DEFAULT 0.00 COMMENT '薪资（精确小数）',
    `status` ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    -- 主键约束
    PRIMARY KEY (`id`),
    -- 唯一约束（已写在字段上，这里仅示意）
    -- 外键约束（谨慎使用，影响性能）
    -- FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

字段数据类型速查表（常用）：

修改表结构

sql
折叠复制
-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改字段类型
ALTER TABLE users MODIFY COLUMN age SMALLINT UNSIGNED;

-- 重命名字段
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20);

-- 删除字段（数据不可恢复！）
ALTER TABLE users DROP COLUMN mobile;

-- 添加索引（性能关键）
ALTER TABLE users ADD INDEX idx_age (age);
CREATE UNIQUE INDEX idx_email ON users(email);  -- 另一种方式

删除表

sql
折叠复制
-- TRUNCATE：清空表数据，重置自增 ID，速度快（不可回滚）
TRUNCATE TABLE users;

-- DROP：删除整个表结构（不可恢复）
DROP TABLE users;

---

三、DML：数据的增删改（数据层）

3.1 INSERT（插入）

sql
折叠复制
-- 单行插入（指定字段，推荐）
INSERT INTO users (username, email, age) 
VALUES ('zhangsan', '[email protected]', 25);

-- 多行批量插入（减少 I/O，性能提升 10+ 倍）
INSERT INTO users (username, email, age) VALUES
    ('lisi', '[email protected]', 30),
    ('wangwu', '[email protected]', 28);

-- 插入时遇到重复主键/唯一键则更新（ON DUPLICATE KEY UPDATE）
INSERT INTO users (id, username, email) VALUES (1, 'new_name', '[email protected]')
ON DUPLICATE KEY UPDATE 
    username = VALUES(username), 
    email = VALUES(email);

3.2 UPDATE（更新）

sql
折叠复制
-- 基础更新（务必加 WHERE！）
UPDATE users SET age = 26, updated_at = NOW() WHERE id = 1;

-- 多表关联更新（根据另一张表更新）
UPDATE users u 
JOIN orders o ON u.id = o.user_id 
SET u.status = 'vip' 
WHERE o.total_amount > 10000;

-- 更新时使用子查询（小心性能）
UPDATE users SET salary = salary * 1.1 
WHERE id IN (SELECT user_id FROM orders WHERE created_at > '2026-01-01');

⚠️ 核心安全原则：

sql
折叠复制
-- 错误示范（全表更新！）
UPDATE users SET age = 0;  -- 危险！

-- 正确做法：先 SELECT 验证影响行数
SELECT COUNT(*) FROM users WHERE id = 1;  -- 确认只有 1 条
UPDATE users SET age = 26 WHERE id = 1;

3.3 DELETE（删除）

sql
折叠复制
-- 基础删除（务必加 WHERE！）
DELETE FROM users WHERE id = 10;

-- 多表删除（同时删除两张表的数据）
DELETE u, o FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'banned';

-- 清空表（比 DELETE 快，但不可回滚）
TRUNCATE TABLE logs;  -- 相当于 DROP + CREATE，重置自增

---

四、DQL：查询核心语法（重中之重）

4.1 SELECT 执行顺序（灵魂！）

SQL 的书写顺序与执行顺序完全不同，理解这一点是调优的基础：

sql
折叠复制
-- 书写顺序（我们写的顺序）
SELECT 字段                    -- 5
FROM 表名                      -- 1
JOIN 其他表 ON 条件            -- 2
WHERE 行级条件                 -- 3
GROUP BY 分组字段              -- 4
HAVING 组级条件                -- 6
ORDER BY 排序字段              -- 7
LIMIT 分页参数;                -- 8

-- 实际执行顺序（MySQL 引擎内部的顺序）
FROM / JOIN   -- 1. 确定数据来源，生成虚拟表
ON            -- 2. 执行连接条件
WHERE         -- 3. 行过滤（此处可以使用别名吗？不可以！）
GROUP BY      -- 4. 分组
HAVING        -- 5. 组过滤（可以使用聚合函数）
SELECT        -- 6. 计算表达式和别名（此时别名才生成）
ORDER BY      -- 7. 排序（此处可以使用别名！）
LIMIT         -- 8. 分页截取

示例：为什么 WHERE 中不能使用别名？

sql
折叠复制
-- 错误写法
SELECT username AS name FROM users WHERE name = 'zhangsan';  -- 报错！WHERE 阶段别名还没生成

-- 正确写法
SELECT username AS name FROM users WHERE username = 'zhangsan';

-- ORDER BY 可以用别名（因为它在 SELECT 之后执行）
SELECT username AS name FROM users ORDER BY name;  -- OK

---

4.2 基础查询

sql
折叠复制
-- 去重查询
SELECT DISTINCT age FROM users;

-- 查询前 N 条
SELECT * FROM users LIMIT 10;

-- 分页查询（OFFSET 越大越慢）
SELECT * FROM users LIMIT 10 OFFSET 100;   -- 第 101-110 条
-- 等价写法（MySQL 语法糖）
SELECT * FROM users LIMIT 100, 10;

-- 排序（多字段排序）
SELECT * FROM users ORDER BY age DESC, id ASC;

---

五、WHERE 条件进阶（索引与性能）

5.1 常用操作符

sql
折叠复制
-- 范围查询（BETWEEN 包含边界）
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- 集合查询
SELECT * FROM users WHERE status IN ('active', 'vip');

-- 模糊查询（注意：'%abc' 不会走索引！）
SELECT * FROM users WHERE username LIKE '张%';   -- 走索引（前缀匹配）
SELECT * FROM users WHERE username LIKE '%张';   -- 不走索引（后缀匹配）

-- 空值判断（不能用 = NULL）
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

5.2 索引与 WHERE 的黄金法则

优化法则：不要把索引列放在函数或运算中！

---

六、函数库（必会）

6.1 聚合函数（配合 GROUP BY）

sql
折叠复制
SELECT 
    COUNT(*) AS total_count,                -- 总行数（包含 NULL）
    COUNT(id) AS id_count,                  -- 非空 ID 数
    COUNT(DISTINCT status) AS status_count, -- 去重统计
    SUM(age) AS age_sum,
    AVG(age) AS age_avg,
    MAX(age) AS age_max,
    MIN(age) AS age_min
FROM users;

6.2 字符串函数

sql
折叠复制
-- 拼接
SELECT CONCAT(username, ' (', email, ')') AS info FROM users;

-- 截取
SELECT SUBSTRING(username, 1, 3) AS prefix FROM users;

-- 长度
SELECT LENGTH(username) AS byte_len, CHAR_LENGTH(username) AS char_len FROM users;

-- 替换
SELECT REPLACE(email, '@', '[at]') FROM users;

-- 去空格
SELECT TRIM('   hello   ') = 'hello';   -- TRUE

6.3 日期时间函数

sql
折叠复制
-- 获取当前时间
SELECT NOW(), CURDATE(), CURTIME();

-- 日期格式化（常用）
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') FROM users;
SELECT DATE_FORMAT(created_at, '%Y年%m月%d日') AS cn_date FROM users;

-- 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);    -- 7 天后
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);  -- 1 个月前

-- 日期差值
SELECT DATEDIFF('2026-09-10', '2026-09-03');  -- 7 天

-- 按日期分组统计（经典日报）
SELECT DATE(created_at) AS day, COUNT(*) AS cnt
FROM orders
GROUP BY DATE(created_at);

6.4 流程控制函数（CASE WHEN）

sql
折叠复制
-- 方式一：简单 CASE
SELECT username,
    CASE age
        WHEN 18 THEN '成年'
        WHEN 60 THEN '退休'
        ELSE '其他'
    END AS age_label
FROM users;

-- 方式二：搜索 CASE（更灵活，推荐）
SELECT username,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 65 THEN '成年劳动力'
        WHEN age > 65 THEN '退休'
        ELSE '未知'
    END AS age_group
FROM users;

-- 配合聚合做条件统计（经典交叉表）
SELECT 
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_cnt,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_cnt
FROM users;

---

七、多表连接（JOIN 的 7 种情况）

7.1 连接类型图解（用文氏图记忆）

INNER JOIN  : 只取交集
LEFT JOIN   : 左表全部 + 右表匹配
RIGHT JOIN  : 右表全部 + 左表匹配
FULL JOIN   : 左右全部（MySQL 不直接支持，用 UNION 模拟）

7.2 实战示例

sql
折叠复制
-- 准备两张表
-- users: id, name
-- orders: id, user_id, amount

-- 1. INNER JOIN（查有订单的用户）
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 2. LEFT JOIN（所有用户，无订单的显示 NULL）
SELECT u.name, IFNULL(o.amount, 0) AS amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 3. 查"无订单的用户"（经典面试题）
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 4. 自连接（同一张表关联，如员工-上级）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 5. 三表连接
SELECT u.name, o.order_id, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_products op ON o.id = op.order_id
JOIN products p ON op.product_id = p.id;

7.3 JOIN 性能对比

---

八、子查询与派生表

sql
折叠复制
-- 1. WHERE 子句中的子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);  -- 查询比平均年龄大的用户

-- 2. FROM 子句中的派生表（必须起别名）
SELECT tmp.avg_age, COUNT(*)
FROM (SELECT AVG(age) AS avg_age FROM users) AS tmp;

-- 3. SELECT 子句中的标量子查询
SELECT name, 
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- 4. EXISTS 子查询（效率优于 IN 当内表大时）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

优化建议：80% 的子查询都可以用 JOIN 重写，且性能更好，但可读性会下降，自行权衡。

---

九、集合操作（UNION）

sql
折叠复制
-- UNION：去重合并（有去重开销）
SELECT name, email FROM users WHERE status = 'active'
UNION
SELECT name, email FROM vip_users;

-- UNION ALL：不去重合并（性能更好，确定无重复时使用）
SELECT name, email FROM users_2025
UNION ALL
SELECT name, email FROM users_2026;

-- MySQL 8.0 支持 INTERSECT（交集）和 EXCEPT（差集）
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;  -- 有订单的用户

---

十、高级语法（窗口函数 / CTE）

10.1 窗口函数（MySQL 8.0+）

sql
折叠复制
-- ROW_NUMBER：分组排名（每个部门按薪资排名）
SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- RANK / DENSE_RANK（处理并列）
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,          -- 1,1,3
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank -- 1,1,2
FROM exam_scores;

-- LAG / LEAD（获取前后行数据）
SELECT 
    date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY date) AS prev_day_amount,
    LEAD(amount, 1, 0) OVER (ORDER BY date) AS next_day_amount
FROM daily_sales;

10.2 CTE（公用表表达式，提高可读性）

sql
折叠复制
-- WITH 子句创建临时命名查询
WITH high_value_users AS (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING total_spent > 10000
)
SELECT u.name, h.total_spent
FROM users u
JOIN high_value_users h ON u.id = h.user_id;

-- 递归 CTE（查询树形结构）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

---

十一、性能优化索引原则（与 DDL 结合）

11.1 索引类型

sql
折叠复制
-- 普通索引（加速查询）
CREATE INDEX idx_age ON users(age);

-- 唯一索引（加速查询 + 约束唯一）
CREATE UNIQUE INDEX idx_email ON users(email);

-- 复合索引（最左前缀原则！）
CREATE INDEX idx_age_status ON users(age, status);
-- 查询条件中必须有 age 才能使用该索引，单独 status 不会走

11.2 最左前缀原则（核心）

sql
折叠复制
-- 索引 (a, b, c)
WHERE a = 1           -- ✅ 走索引
WHERE a = 1 AND b = 2 -- ✅ 走索引
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ 走索引
WHERE b = 2 AND c = 3 -- ❌ 不走索引（缺了 a）
WHERE a = 1 AND c = 3 -- ⚠️ 只走 a，c 不走（因为跳过了 b）

11.3 慢查询排查

sql
折叠复制
-- 查看 SQL 执行计划
EXPLAIN SELECT * FROM users WHERE age > 25;

-- 重点关注字段：
-- type: ALL（全表扫描）< index（索引扫描）< range（范围）< ref（普通索引）< const（主键/唯一）
-- rows: 预估扫描行数（越小越好）
-- Extra: Using filesort（需要手动优化） / Using temporary（临时表，需优化）

---

十二、安全操作规范（防删库跑路）

sql
折叠复制
-- 1. 开启事务保护（InnoDB 引擎支持）
START TRANSACTION;
DELETE FROM users WHERE id = 100;
-- 发现删错了！
ROLLBACK;  -- 回滚

-- 确认无误后
COMMIT;    -- 提交

-- 2. 更新/删除前务必先 SELECT
SELECT * FROM users WHERE id = 100;  -- 确认数据
UPDATE users SET status = 'banned' WHERE id = 100;

-- 3. 生产环境禁用 TRUNCATE，用 DELETE + 事务
-- TRUNCATE 无法回滚！

-- 4. 设置 SQL_SAFE_UPDATES（安全模式）
SET SQL_SAFE_UPDATES = 1;  -- 不允许无 WHERE 的 UPDATE/DELETE

---

十三、实战综合案例（完整 SQL）

sql
折叠复制
-- 需求：统计 2026 年 8 月，每个用户的订单总额，按总额降序排列，
-- 只显示总额大于 100 的用户，并标注用户等级（>1000 为金牌，>500 为银牌）

WITH user_order_stats AS (
    SELECT 
        u.id,
        u.username,
        COALESCE(SUM(o.amount), 0) AS total_spent,
        COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
        AND o.created_at BETWEEN '2026-08-01' AND '2026-08-31'
    GROUP BY u.id, u.username
    HAVING total_spent > 100
)
SELECT 
    username,
    total_spent,
    order_count,
    CASE 
        WHEN total_spent > 1000 THEN '金牌'
        WHEN total_spent > 500 THEN '银牌'
        ELSE '铜牌'
    END AS user_level
FROM user_order_stats
ORDER BY total_spent DESC
LIMIT 20;

---

结尾总结