MySQL 8.0 SQL实战：从建库到高级查询的50个必备语句详解

掌握核心SQL语句是每一位后端开发者和数据分析师的基本功。下面，以 MySQL 8.0+ 为示例，详细介绍我们必须掌握的50条SQL常用语句。从建库、建表到高级查询，每条SQL语句都配有可直接运行的建表和模拟数据脚本，边看边敲，可轻松上手窗口函数、CTE、事务、索引等现代特性。

一、数据库与表操作（10条）

1、创建数据库

场景示例：创建一个名为 school 的数据库，用来存储学校相关数据。

SQL模板及解析：

-- 创建数据库school，如果不存在，就创建
CREATE DATABASE IF NOT EXISTS school
CHARACTER SET utf8mb4          -- 设置字符集为utf8mb4，支持emoji
COLLATE utf8mb4_unicode_ci;     -- 排序规则，推荐使用_unicode_ci，提高排序准确性

• CREATE DATABASE：创建数据库命令
• IF NOT EXISTS：防止重复创建报错
• CHARACTER SET：指定字符集，utf8mb4是MySQL中真正支持完整UTF-8的字符集
• COLLATE：排序规则，_ci 表示大小写不敏感（case-insensitive）

常见误区：

• 使用 utf8 而不是 utf8mb4 → 实际只支持3字节UTF-8，无法存储emoji（如：😊）
• 忽略 IF NOT EXISTS → 多次执行会报错

优化建议：

• 始终使用 utf8mb4+utf8mb4_unicode_ci
• 在生产环境中使用脚本部署时，务必加上 IF NOT EXISTS

2、使用数据库

场景示例：切换当前会话到 school 数据库。

SQL模板及解析：

-- 切换到school数据库
USE school;

• USE database_name; 是会话级命令，仅影响当前连接
• 不需要分号也能执行（但建议加“;”）

常见误区：

• 忘记执行 USE 就操作表 → 报错：“Table doesn't exist”
• 误以为 USE 是全局切换 → 实际每个连接独立

优化建议：

• 在SQL脚本开头，统一写 USE db_name;
• Navicat、DBeaver等工具，可设置默认数据库

3、创建学生表（带主键、自增）

场景示例：创建 students 表，包含学号（自增主键）、姓名、性别、出生日期、班级ID。

模拟数据准备（建表）：

USE school;

-- 创建students表
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学号，自增主键',
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    gender ENUM('男', '女') NOT NULL DEFAULT '男' COMMENT '性别',
    birth_date DATE NOT NULL COMMENT '出生日期',
    class_id INT NOT NULL COMMENT '班级ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

• AUTO_INCREMENT：自动递增，通常用于主键
• PRIMARY KEY：主键约束，唯一且非空
• ENUM：枚举类型，节省空间且限制取值
• NOT NULL：非空约束
• DEFAULT：默认值
• COMMENT：字段注释（重要！便于维护）
• ENGINE=InnoDB：支持事务、外键
• CHARSET=utf8mb4：确保表级字符集正确

常见误区：

• 主键没有设 AUTO_INCREMENT → 插入需要手动指定ID
• 忘记 NOT NULL → 允许空值，导致后续查询异常
• 使用 MyISAM 引擎 → 不支持事务和外键

优化建议：

• 所有核心表使用 InnoDB
• 主键，推荐使用 BIGINT 而非 INT（避免自增溢出），根据预估数据量选择，中小型场景 INT 足够，超大规模场景，建议开始就使用 BIGINT
• MySQL 8.0默认启用 innodb_autoinc_lock_mode=2（交错模式），显著提升批量插入并发性能；在主从复制环境中需确保与GTID等复制模式兼容
• 添加字段和表注释是良好习惯

4、插入单条学生记录

场景示例：向 students 表插入一名学生：张三，男，2005-03-15，班级ID为1。

SQL模板及解析：

-- 插入一条学生记录，student_id由AUTO_INCREMENT自动生成
INSERT INTO students (name, gender, birth_date, class_id)
VALUES ('张三', '男', '2005-03-15', 1);

• INSERT INTO table (col1, col2, ...) VALUES (...)
• 显式列出字段名，更安全（避免字段顺序变更，导致错误）
• student_id 未指定 → 自动填充
• created_at 未指定 → 使用默认值 CURRENT_TIMESTAMP

常见误区：

• 不写字段列表，直接 INSERT INTO students VALUES (...) → 一旦表结构变更极易出错
• 忘记引号 → 字符串必须用单引号包裹
• 日期格式错误 → 必须是 ‘YYYY-MM-DD’

优化建议：

• 始终显式指定字段名
• 使用参数化查询，防止SQL注入（应用层）

5、批量插入多条学生记录

场景示例：一次性插入多名学生信息。

SQL模板及解析：

-- 批量插入多条学生记录
INSERT INTO students (name, gender, birth_date, class_id)
VALUES
    (‘李四’， ‘女’， ‘2004-07-22’， 1)，
    (‘王五’， ‘男’， ‘2005-01-10’， 2)，
    (‘赵六’， ‘女’， ‘2004-12-05’， 2)，
    (‘钱七’， ‘男’， ‘2005-05-18’， 1);

• VALUES 后可接多组 (值)，用逗号分隔
• 比逐条插入效率高（减少网络往返）

常见误区：

• 每条单独写 INSERT → 性能差
• 单条数据错误，导致整个事务失败（默认自动提交）

优化建议：

• 批量插入建议使用事务包裹：

  START TRANSACTION;
  INSERT INTO students (...) VALUES (...), (...);
  COMMIT;

• 控制每批数量（如：1000条/批，先通过 SHOW VARIABLES LIKE ‘max_allowed_packet’; 查看配置，再根据单条记录大小调整批量数量），避免锁表太久

6、创建班级表（含外键）

场景示例：创建 classes 表，并与 students 表建立外键关系。

SQL模板及解析：

-- 创建classes表
CREATE TABLE classes (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(50) NOT NULL UNIQUE COMMENT ‘班级名称，唯一’，
    teacher VARCHAR(50) COMMENT ‘班主任’，
    grade INT NOT NULL COMMENT ‘年级，如：1表示一年级’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘班级表’；

-- 添加外键约束：students.class_id引用classes.class_id
ALTER TABLE students
ADD CONSTRAINT fk_students_class
FOREIGN KEY (class_id) REFERENCES classes(class_id)
ON DELETE CASCADE ON UPDATE CASCADE;

• UNIQUE：确保班级名不重复
• FOREIGN KEY：外键约束，保证数据一致性
• ON DELETE CASCADE：删除班级时，自动删除其所有学生（⚠️ 仅适用于学生完全依附于班级，且无独立生命周期的场景；实际学校系统中通常应避免，此时建议使用软删除或设为 ON DELETE SET NULL）
• ON UPDATE CASCADE：修改班级ID时，自动更新学生表中的 class_id

常见误区：

• 外键字段类型不一致（如：students.class_id 是 VARCHAR）→ 报错
• 忘记索引外键字段 → 关联查询慢
• 外键导致删除困难（级联删除，要谨慎）
• 建表顺序倒置（先创建引用表 students，再创建被引用表 classes）→ 虽语法允许，但未创建 classes 表时插入 students 数据会触发外键约束错误

优化建议：

• 外键字段必须建立索引（InnoDB自动创建）
• 谨慎使用 CASCADE，生产环境建议软删除（加 is_deleted 字段）
• 可先插入 classes，再插入 students
• 建表顺序，需要保证被引用表（如：classes）先存在，再创建引用表（如：students），避免因表依赖关系倒置导致数据操作异常；或在创建 students 表时延迟定义外键（即先建表再添加外键约束，现有 ALTER TABLE 方式可行）

7、查看表结构

场景示例：查看 students 表的字段定义。

SQL模板及解析：

-- 查看 students 表结构
DESCRIBE students;
-- 或
DESC students;
-- 或更详细信息
SHOW CREATE TABLE students;

• DESCRIBE/DESC：快速查看字段名、类型、是否为空、键类型等
• SHOW CREATE TABLE：显示建表完整语句，包含引擎、字符集、外键等

常见误区：

• 误以为 DESC 是排序 → 实际是 DESCRIBE 缩写
• 只看字段名，忽略类型和约束

优化建议：

• 开发时，常用 SHOW CREATE TABLE 检查建表是否正确
• 注意外键、默认值、字符集是否符合预期

8、修改表结构（添加字段）

场景示例：给 students 表添加一个 email 字段。

SQL模板及解析：

-- 添加email字段，允许为空
ALTER TABLE students
ADD COLUMN email VARCHAR(100) NULL DEFAULT NULL COMMENT ‘邮箱地址’;

• ADD COLUMN：添加列
• NULL DEFAULT NULL：允许为空，初始值为NULL
• 如果添加的列指定为 NOT NULL 且未设置默认值，则要求表必须为空，或者在添加列时显式提供默认值。

常见误区：

• 添加 NOT NULL 但字段无默认值 → 报错
• 大表加字段锁表时间长（尤其MyISAM）

优化建议：

• 大表修改结构，使用 pt-online-schema-change 工具（Percona）
• 尽量在低峰期操作
• 添加字段，建议带默认值或允许NULL

9、修改字段类型或名称

场景示例：将 students.name 字段长度从50扩展到100，并重命名为 full_name。

SQL模板及解析：

-- 修改字段：改名+扩展长度
ALTER TABLE students
CHANGE COLUMN name full_name VARCHAR(100) NOT NULL COMMENT ‘学生全名’；

• CHANGE COLUMN old_name new_name type ...：可同时改名和改类型
• MODIFY COLUMN：仅修改类型，不能改名

常见误区：

• 使用 MODIFY 想改名 → 语法错误
• 改小字段长度（如：VARCHAR(100) → 50）可能导致数据截断

优化建议：

• 修改前，要备份数据
• 先查最大长度：SELECT MAX(LENGTH(name)) FROM students;
• 生产环境，要避免直接修改

10、删除字段

场景示例：删除 students 表中不再需要的 email 字段。

SQL模板及解析：

-- 删除email字段
ALTER TABLE students
DROP COLUMN email;

• DROP COLUMN：删除列
• 删除后数据永久丢失

常见误区：

• 误删关键字段 → 数据丢失
• 外键引用的字段被删 → 报错

优化建议：

• 删除前，要确认无业务依赖
• 建议先 RENAME COLUMN 改名（如：加 _old），观察一段时间再删
• 重要操作前，要备份表：CREATE TABLE students_bak AS SELECT * FROM students;

二、数据查询操作（15条）

11、查询所有学生信息

场景示例：查询 students 表中所有学生的所有字段。

SQL模板及解析：

-- 查询所有学生信息
SELECT * FROM students;

• SELECT *：选择所有字段
• 在生产SQL中，不推荐使用 *（影响性能和可维护性）

常见误区：

• 在应用中，用 SELECT * → 字段顺序变更或新增字段导致程序异常
• 大表 SELECT * → 传输大量无用数据

优化建议：

• 显式列出所需字段：SELECT student_id, name, gender FROM students
• 仅在调试时，使用 *

12、查询指定字段

场景示例：只查询学生姓名和性别。

SQL模板及解析：

-- 查询姓名和性别
SELECT name, gender FROM students;

• 显式指定字段，提高可读性和性能
• 减少网络传输量

优化建议：

• 永远只查需要的字段
• 可配合别名：SELECT name AS 姓名， gender AS 性别 FROM students

13、去重查询（DISTINCT）

场景示例：查询所有不同的性别值。

SQL模板及解析：

-- 查询去重后的性别
SELECT DISTINCT gender FROM students;

• DISTINCT：去除重复行
• 可作用于多字段：SELECT DISTINCT gender, class_id FROM students

常见误区：

• 认为 DISTINCT 是函数 → DISTINCT(gender) 写法虽合法，但不推荐
• 忘记 DISTINCT，导致结果重复

优化建议：

• 多字段去重注意性能（可能触发filesort）
• 考虑用 GROUP BY 替代（功能更强）

14、条件查询（WHERE）

场景示例：查询班级ID为1的学生。

SQL模板及解析：

-- 查询class_id = 1的学生
SELECT * FROM students WHERE class_id = 1;

• WHERE 后接条件表达式
• 支持 =, <>, <, >, IN, BETWEEN, LIKE 等

优化建议：

• WHERE 字段，应建立索引（如：class_id）
• 要避免在条件中对字段做函数处理：WHERE YEAR(birth_date) = 2005 → 无法用索引

15、多条件查询（AND / OR）

场景示例：查询班级ID为1且性别为“女”的学生。

SQL模板及解析：

-- 多条件AND查询
SELECT * FROM students
WHERE class_id = 1 AND gender = ‘女’；

• AND：逻辑与，全部条件为真才返回
• OR：逻辑或，任一为真即返回
• 注意优先级：AND 优先于 OR，复杂条件，建议加括号

优化建议：

• 使用括号明确逻辑：WHERE (a=1 OR b=2) AND c=3
• 复合条件，要考虑建立联合索引

16、范围查询（BETWEEN）

场景示例：查询出生日期在2004-01-01到2005-12-31之间的学生。

SQL模板及解析：

-- 范围查询
SELECT * FROM students
WHERE birth_date BETWEEN ‘2004-01-01’ AND ‘2005-12-31’;

• BETWEEN a AND b：闭区间，包含a和b
• 等价于 >= a AND <= b

优化建议：

• 日期字段应建立索引
• 时间范围，建议用 DATETIME 类型

17、模糊查询（LIKE）

场景示例：查询姓名包含“张”的学生。

SQL模板及解析：

-- 模糊查询：姓名包含“张”
SELECT * FROM students WHERE name LIKE ‘%张%’；

• %：匹配任意字符（包括零个）
• _：匹配单个字符
• 区分大小写取决于排序规则（_ci 不区分）

常见误区：

• LIKE ‘%张%’ 无法使用索引前缀 → 全表扫描
• 中文模糊查询效率低

优化建议：

• 前缀匹配可走索引：LIKE ‘张%’
• 大数据量，考虑使用全文索引（FULLTEXT）或 Elasticsearch

18、空值判断（IS NULL）

场景示例：查询email为空的学生。

SQL模板及解析：

-- 查询email为空的学生
SELECT * FROM students WHERE email IS NULL;

• IS NULL：判断是否为NULL
• = 不能用于NULL比较（email = NULL 永远为false）

常见误区：

• 使用 = NULL → 无结果
• 认为 ’’（空字符串）等于 NULL → 不等

优化建议：

• 设计时明确NULL含义
• 用 COALESCE(email, ‘无’) 处理NULL

19、排序查询（ORDER BY）

场景示例：按出生日期升序排列学生。

SQL模板及解析：

-- 按出生日期升序
SELECT * FROM students ORDER BY birth_date ASC;
-- 降序：DESC

• ORDER BY 可接多个字段：ORDER BY class_id, birth_date DESC
• ASC 默认可省略

优化建议：

• 排序列建立索引
• 要避免 ORDER BY RAND() 大表使用（性能极差）

20、限制结果数量（LIMIT）

场景示例：只查询前5条学生记录。

SQL模板及解析：

-- 限制返回5条
SELECT * FROM students LIMIT 5;

• LIMIT n：返回前n条
• LIMIT offset, count：跳过offset条，取count条（分页）

优化建议：

• 分页慎用大offset（如：LIMIT 10000, 10）→ 建议用游标分页
• 结合 ORDER BY 使用，避免结果不一致

21、分页查询（LIMIT+OFFSET）

场景示例：查询第2页，每页3条学生数据。

SQL模板及解析：

-- 分页：第2页，每页3条（跳过3条）
SELECT * FROM students
ORDER BY student_id
LIMIT 3， 3;  -- 跳过3条，取3条

• LIMIT offset, count
• offset 从0开始

常见误区：

• 没有加 ORDER BY → 分页结果混乱
• 大offset性能差

优化建议：

• 使用主键或唯一索引字段排序
• 游标分页：WHERE id > last_id ORDER BY id LIMIT n

22、聚合函数：计数（COUNT）

场景示例：统计学生总人数。

SQL模板及解析：

-- 统计学生总数
SELECT COUNT(*) AS total_students FROM students;

• COUNT(*)：统计所有行（包括NULL）
• COUNT(字段)：统计该字段非NULL的行数

优化建议：

• COUNT(*) 通常最快（InnoDB需扫描）
• 大表计数，可考虑缓存或近似值

23、聚合函数：求和（SUM）

场景示例：假设学生有“分数”字段，求总分。

模拟数据：

ALTER TABLE students ADD COLUMN score INT DEFAULT 0;
UPDATE students SET score = CASE
    WHEN name = ‘张三’ THEN 85
    WHEN name = ‘李四’ THEN 92
    WHEN name = ‘王五’ THEN 78
    ELSE 88 END;

SQL模板及解析：

-- 求所有学生分数总和
SELECT SUM(score) AS total_score FROM students;

• SUM(字段)：对数值字段求和
• 忽略NULL值

优化建议：

• 字段必须是数值类型
• 可结合 GROUP BY 分组求和

24、聚合函数：平均值（AVG）

场景示例：计算学生平均分。

SQL模板及解析：

-- 计算平均分
SELECT AVG(score) AS avg_score FROM students;

• AVG(字段)：计算平均值
• 自动忽略NULL

优化建议：

• 注意数据类型精度（可用 ROUND(AVG(score), 2) 保留两位小数）

25、聚合函数：最大/最小值（MAX/MIN）

场景示例：查询最高分和最低分。

SQL模板及解析：

-- 查询最高分和最低分
SELECT MAX(score) AS max_score, MIN(score) AS min_score FROM students;

• MAX/MIN 可用于数值、日期、字符串
• 字符串按字典序比较

优化建议：

• 对字段建立索引可加速 MAX/MIN

三、分组与连接查询（10条）

26、分组统计（GROUP BY）

场景示例：按班级统计学生人数。

SQL模板及解析：

-- 按class_id分组统计人数
SELECT class_id， COUNT(*) AS student_count
FROM students
GROUP BY class_id;

• GROUP BY 将数据分组
• SELECT 中非聚合字段，必须出现在 GROUP BY 中（MySQL兼容模式除外）

常见误区：

• SELECT class_id, name, COUNT(*) → name 未分组，结果不确定
• 忘记 GROUP BY 直接用聚合函数

优化建议：

• 分组字段建立索引
• 避免分组字段过多

27、分组后筛选（HAVING）

场景示例：查询学生人数大于1的班级。

SQL模板及解析：

-- 分组后筛选
SELECT class_id, COUNT(*) AS cnt
FROM students
GROUP BY class_id
HAVING cnt > 1;

• HAVING 用于过滤分组结果
• WHERE 过滤原始行，HAVING 过滤分组

优化建议：

• HAVING 条件尽量简单
• 可先用 WHERE 减少数据量

28、内连接查询（INNER JOIN）

场景示例：查询学生及其班级名称。

SQL模板及解析：

-- 内连接：学生+班级名
SELECT s.name, s.gender, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.class_id;

• INNER JOIN：仅返回两表匹配的行
• ON 指定连接条件

优化建议：

• 连接字段建立索引
• 要尽量减少连接表数量

29、左外连接（LEFT JOIN）

场景示例：查询所有学生，包括未分配班级的（如果有的话）。

SQL模板及解析：

-- 左连接：保留所有学生
SELECT s.name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

• LEFT JOIN：左表全保留，右表不匹配则为NULL
• 常用于“主表+可选信息”场景

优化建议：

• 右表连接字段建索引
• 要避免 LEFT JOIN 后无 WHERE，导致笛卡尔积

30、右外连接（RIGHT JOIN）

场景示例：查询所有班级及其学生，包括无学生的班级。

SQL模板及解析：

-- 右连接：保留所有班级
SELECT c.class_name, s.name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.class_id;

• RIGHT JOIN：右表全保留
• 功能上可用 LEFT JOIN 替代（交换表顺序）

优化建议：

• 少用 RIGHT JOIN，可读性差
• 统一使用 LEFT JOIN

31、全外连接（FULL OUTER JOIN）

场景示例：MySQL不支持 FULL OUTER JOIN，需要用 UNION ALL 来模拟。

SQL模板及解析：

SELECT s.name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id

UNION ALL  -- 两部分结果无交集，无需去重，UNION ALL更高效

SELECT NULL, c.class_name
FROM classes c
WHERE NOT EXISTS (
    SELECT 1
    FROM students s
    WHERE s.class_id = c.class_id
);

• 由于左连接部分包含所有学生（含无班级者），第二部分仅包含无学生的班级，二者无交集，因此使用 UNION ALL 即可，避免不必要的去重开销
• 模拟全外连接：通过左连接获取所有左表记录，再用 NOT EXISTS 补充右表中未被关联的记录

优化建议：

• 全外连接需全表扫描，即使使用 UNION ALL 也可能性能较低
• 请评估业务是否真的需要同时展示“孤立学生”和“孤立班级”，多数场景只需 LEFT JOIN 或 RIGHT JOIN 即可满足

32、自连接查询

场景示例：假设员工表有manager_id，查询员工及其上司姓名。

模拟数据：

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees (name, manager_id) VALUES
(‘张三’， NULL)，
(‘李四’， 1)，
(‘王五’， 1)，
(‘赵六’， 2);

SQL模板及解析：

-- 自连接：员工+上司
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

• 同一表别名连接
• 常用于树形结构（如：组织架构）

优化建议：

• manager_id 建索引
• 深层级考虑递归CTE

33、子查询（WHERE中）

场景示例：查询与“张三”同班级的学生。

SQL模板及解析：

-- 子查询：找和张三同班的学生
SELECT * FROM students
WHERE class_id = (
    SELECT class_id FROM students WHERE name = ‘张三’
);

• 子查询返回单值时可用 =
• 如果返回多值，应改用 IN

⚠️ 常见误区：

• 子查询返回多行却使用 = → 报错 Subquery returns more than 1 row
• 误认为所有子查询都性能低下 → 实际取决于查询结构和MySQL版本

优化建议：

• MySQL 8.0优化器对简单子查询（尤其是非相关子查询）支持良好，通常会自动转换为等效的 JOIN 执行，性能与手写 JOIN 相当
• 仅在以下情况优先考虑 JOIN：
  • 相关子查询（如：WHERE EXISTS (SELECT 1 FROM ... WHERE outer_col = inner_col)）
  • 多层嵌套子查询
  • 子查询逻辑复杂、可读性差
• 如果子查询结果集极小（如本例中只查一个学生的班级），性能通常不是问题
• 始终可通过 EXPLAIN 查看执行计划，确认是否被优化

上述子查询在MySQL 8.0中的执行计划通常等同于：

SELECT s1.*
FROM students s1
JOIN students s2 ON s1.class_id = s2.class_id
WHERE s2.name = ‘张三’；

两者性能几乎一致，选择应基于可读性与业务语义。

34、子查询（FROM中）

场景示例：查询每个班级的学生人数，并筛选大于1的。

SQL模板及解析：

-- FROM子查询（衍生表）
SELECT * FROM (
    SELECT class_id, COUNT(*) AS cnt
    FROM students GROUP BY class_id
) t WHERE cnt > 1;

• 子查询，必须有别名（如：t）
• 称为“派生表”

优化建议：

• 避免多层嵌套
• 考虑用CTE，提高可读性

35、相关子查询

场景示例：查询成绩高于班级平均分的学生。

SQL模板及解析：

-- 相关子查询
SELECT s1.name, s1.score, s1.class_id
FROM students s1
WHERE s1.score > (
    SELECT AVG(s2.score)
    FROM students s2
    WHERE s2.class_id = s1.class_id
);

• 子查询引用外层表字段（s1.class_id）
• MySQL 8.0优化器对简单子查询有较好支持，复杂场景优先考虑JOIN

优化建议：

• 用 JOIN+窗口函数替代：

  SELECT name, score, class_id
  FROM (
  SELECT name, score, class_id, AVG(score) OVER (PARTITION BY class_id) AS avg_score
  FROM students
  ) t
  WHERE score > avg_score;

四、高级查询与数据操作（15条）

36、窗口函数：排名（RANK/DENSE_RANK）

场景示例：为每个班级的学生按分数从高到低排名，相同分数并列，且后续排名跳过（RANK）或不跳过（DENSE_RANK）。

准备模拟数据：
要保证 students 表已有 score 字段（如：第23条已添加）：

-- 如果没有添加score字段，先执行：
ALTER TABLE students ADD COLUMN score INT DEFAULT 0;
UPDATE students SET score = CASE
    WHEN name = ‘张三’ THEN 85
    WHEN name = ‘李四’ THEN 92
    WHEN name = ‘王五’ THEN 92
    WHEN name = ‘赵六’ THEN 78
    ELSE 88
END;

SQL模板及解析：

-- 使用RANK()和DENSE_RANK()进行班级内排名
SELECT
    name,
    class_id,
    score,
    RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS dense_rank_num
FROM students;

• OVER()：定义窗口
• PARTITION BY class_id：按班级分组计算排名
• ORDER BY score DESC：排序方式
• RANK()：相同值并列，后续跳号（如：1,1,3）
• DENSE_RANK()：相同值并列，后续不跳号（如：1,1,2）

常见误区：

• 窗口函数 RANK()/DENSE_RANK() 必须在 OVER() 中指定 ORDER BY 子句，否则会报语法错误
• 误认为 RANK() 是全局函数 → 实际是窗口函数，需 OVER

优化建议：

• 窗口函数性能优于自连接或相关子查询
• 大数据量，要注意内存使用（可通过分区减少压力）

37、公用表表达式（CTE，WITH）

场景示例：使用CTE查询每个班级平均分，并列出高于平均分的学生。

SQL模板及解析：

-- 使用CTE提高可读性
WITH class_avg AS (
    SELECT
        class_id,
        AVG(score) AS avg_score
    FROM students
    GROUP BY class_id
)
SELECT
    s.name,
    s.class_id,
    s.score,
    ca.avg_score
FROM students s
JOIN class_avg ca ON s.class_id = ca.class_id
WHERE s.score > ca.avg_score;

• WITH cte_name AS (...)：定义CTE
• CTE可被后续查询引用，作用域仅限当前语句
• 支持递归CTE

常见误区：

• 在 WITH 前加 ; → 必须单独一行或紧跟上句
• 认为CTE会物化 → 实际是逻辑视图，不一定提升性能

优化建议：

• 替代复杂嵌套子查询，提升可读性
• 可定义多个CTE：WITH a AS (...), b AS (...) SELECT ...

38、联合查询（UNION 与 UNION ALL）

场景示例：将两个不同条件的学生列表合并，去重或不去重。

SQL模板及解析：

-- 合并高分和低分学生（去重）
SELECT name, score FROM students WHERE score >= 90
UNION
SELECT name, score FROM students WHERE score <= 80;

-- 不去重版本（性能更高）
SELECT name, score FROM students WHERE score >= 90
UNION ALL
SELECT name, score FROM students WHERE score <= 80;

• UNION：合并结果并去重
• UNION ALL：直接拼接，保留重复
• 要求各查询字段数量、类型兼容

常见误区：

• 字段数不一致 → 报错
• 忘记 ORDER BY 在最后统一加：

  (SELECT ... FROM t1) UNION (SELECT ... FROM t2) ORDER BY score DESC;

优化建议：

• 优先使用 UNION ALL（除非必须去重）
• 在最终结果加 ORDER BY

39、INSERT SELECT（从查询结果插入）

场景示例：将高分学生（≥90）信息复制到 excellent_students 表中。

模拟数据准备：

-- 创建优秀学生表
CREATE TABLE excellent_students (
    student_id INT,
    name VARCHAR(50),
    score INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SQL模板及解析：

-- 将查询结果插入新表
INSERT INTO excellent_students (student_id, name, score)
SELECT student_id, name, score
FROM students
WHERE score >= 90;

• INSERT INTO ... SELECT ...：高效批量导入
• 字段顺序和类型需匹配

常见误区：

• 目标表字段少于源表 → 报错
• 忘记 WHERE 导致全表复制

优化建议：

• 插入前，要清空目标表：TRUNCATE excellent_students;
• 要加索引避免后续查询慢

40、UPDATE多表更新

场景示例：根据班级平均分，给低于平均分的学生分数加5分。

SQL模板及解析：

-- 多表UPDATE：基于子查询更新主表
UPDATE students s
JOIN (
    SELECT class_id, AVG(score) AS avg_score
    FROM students
    GROUP BY class_id
) avg_table ON s.class_id = avg_table.class_id
SET s.score = s.score + 5
WHERE s.score < avg_table.avg_score;

• UPDATE t1 JOIN t2 ON ... SET ...：MySQL特有语法
• 标准SQL不支持，但MySQL允许
• 可用于复杂条件更新

常见误区：

• 忘记 JOIN 条件 → 笛卡尔积，全表误更新
• 未测试 WHERE 条件直接执行

优化建议：

• 先用 SELECT 验证逻辑：

  SELECT s.name, s.score, avg_table.avg_score
  FROM students s
  JOIN (...) avg_table ON ...
  WHERE s.score < avg_table.avg_score;

• 生产环境，建议加事务
• 如果影响行数较大（如：>1万行），应分批次更新（如：每次1000行），避免长时间持有行锁/表锁导致业务阻塞。

41、DELETE删除记录

场景示例：删除 excellent_students 表中创建时间超过7天的记录。

SQL模板及解析：

-- 删除过期优秀学生记录
DELETE FROM excellent_students
WHERE created_at < NOW() - INTERVAL 7 DAY;

• DELETE FROM table WHERE condition
• 无 WHERE 则删除所有行（危险！）

常见误区：

• 忘记 WHERE → 清空整表
• 误删主表数据无备份

优化建议：

• 删除前，要备份：CREATE TABLE excellent_bak AS SELECT * FROM excellent_students;
• 使用软删除字段（如：is_deleted TINYINT DEFAULT 0）
• 大表删除，应分批次进行（如：每次删除1000行），避免长事务持有锁过久，引发主从延迟或业务卡顿，示例：

  DELETE FROM excellent_students
  WHERE created_at < NOW() - INTERVAL 7 DAY
  LIMIT 1000;
  -- 循环执行直至无数据

42、TRUNCATE表

场景示例：清空 excellent_students 表，并重置自增ID。

SQL模板及解析：

-- 清空表并重置AUTO_INCREMENT
TRUNCATE TABLE excellent_students;

• TRUNCATE 是DDL（数据定义语言）操作，执行速度快
• 会重置自增计数器（AUTO_INCREMENT 从1重新开始）
• 不能带 WHERE 条件，始终清空整张表

⚠️ 常见误区：

• 与 DELETE 混淆：
  • DELETE FROM table 是DML（数据操作语言），可带 WHERE，逐行删除，支持事务回滚
  • TRUNCATE TABLE 是一个DDL（数据定义语言）操作，它会自动提交（隐式提交）当前事务，所以即使后面写了 ROLLBACK，也无法撤销 TRUNCATE 删除的数据。在 InnoDB 中，TRUNCATE 实际是 drop + recreate 表，故不支持事务回滚
• 外键约束限制：如果表被其他表的外键引用，TRUNCATE 会失败（除非临时禁用外键检查）

优化建议：

• 仅用在需要彻底清空整表且无需回滚的场景
• 如果表存在外键依赖，可先执行：

  SET FOREIGN_KEY_CHECKS = 0;
  TRUNCATE TABLE excellent_students;
  SET FOREIGN_KEY_CHECKS = 1;

  （注意：操作期间需确保数据一致性）

• 如需可回滚的清空操作，请使用 DELETE FROM excellent_students;（但性能较低，且不重置自增 ID；如果需要重置，可额外执行 ALTER TABLE ... AUTO_INCREMENT = 1）

43、事务控制（BEGIN, COMMIT, ROLLBACK）

场景示例：确保“扣款+记账”操作原子性，失败则回滚。

模拟数据准备：

CREATE TABLE accounts (
    user_id INT PRIMARY KEY,
    balance DECIMAL(10,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO accounts VALUES (1, 1000.00), (2, 800.00);

SQL模板及解析：

-- 开启事务：转账100元
START TRANSACTION;
-- 或BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 模拟错误判断（实际由程序控制）
-- 如果出错：ROLLBACK;
-- 正常完成：COMMIT;

COMMIT;
-- ROLLBACK; -- 出错时执行

• START TRANSACTION/BEGIN：开启事务
• COMMIT：提交更改
• ROLLBACK：撤销所有操作
• 必须使用InnoDB引擎

常见误区：

• 忘记提交 → 连接断开自动回滚
• 在自动提交模式下未显式开启事务

优化建议：

• 所有写操作，尽量包裹事务
• 设置合理超时：SET SESSION innodb_lock_wait_timeout = 30;
• 应用层捕获异常后，调用 ROLLBACK

44、创建索引（CREATE INDEX）

场景示例：为 students.name 字段创建索引以加速前缀匹配查询（如：LIKE ‘张%’）。

SQL模板及解析：

-- 为姓名字段创建普通索引（仅加速前缀匹配）
CREATE INDEX idx_students_name ON students(name);

-- 创建前缀索引（节省空间，需合理设置长度）
CREATE INDEX idx_students_name_8 ON students(name(8));  -- 建议中文场景用 8（覆盖2个汉字）

-- 创建唯一索引（防止重复值）
CREATE UNIQUE INDEX uk_classes_name ON classes(class_name);

• CREATE INDEX index_name ON table(column)
• 前缀索引：column(n) 表示索引前 n字节（非字符数）
• 唯一索引确保字段值唯一

⚠️ 常见误区：

• 索引能加速所有 LIKE 查询？ 错！只有前缀匹配（LIKE ‘张%’）能用索引；LIKE ‘%张’（后缀）或 LIKE ‘%张%’（中间匹配）无法使用B-tree索引，会触发全表扫描
• 前缀索引越短越好？ 错！utf8mb4 下，1个中文=4字节，name(4) 仅索引1个汉字，可能导致大量值前缀相同，索引失效
• 索引越多越好？ 错！索引会降低 INSERT/UPDATE/DELETE 性能，并占用存储空间

优化建议：

• 高频前缀查询字段（如：name LIKE ‘张%’），可建普通或前缀索引
• 中文字段前缀长度建议：覆盖2个汉字 → name(8)；覆盖3个汉字 → name(12)；可通过 SELECT MAX(CHAR_LENGTH(name)) FROM students; 评估实际长度
• 中间匹配需求（如：LIKE ‘%张%’），应考虑：全文索引（FULLTEXT，适用于大文本）；或应用层引入Elasticsearch等搜索引擎
• 联合查询字段，优先建联合索引（注意最左前缀原则）
• 要定期分析索引使用情况：

  SHOW INDEX FROM students;
  -- 或通过performance_schema查看实际使用率

45、视图创建（CREATE VIEW）

场景示例：创建一个视图，展示学生姓名、班级名、分数。

SQL模板及解析：

-- 创建视图
CREATE VIEW student_class_view AS
SELECT
    s.name,
    c.class_name,
    s.score
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

-- 查询视图
SELECT * FROM student_class_view WHERE score > 85;

• CREATE VIEW view_name AS select_query
• 视图是虚拟表，不存储实际数据
• 可简化复杂查询、统一业务口径、实现字段级权限控制

常见误区：

• “视图能提升查询性能” → 错误！视图本身无性能增益
• “视图会让查询变慢” → ⚠️ 不严谨！性能取决于底层SQL，而非视图语法
• 视图可任意更新 → 错误！含 JOIN、GROUP BY、DISTINCT 等的视图通常不可更新

优化建议：

• 用于逻辑抽象与权限隔离：例如只暴露部分字段给特定用户
• 避免多层嵌套视图：可能导致优化器难以生成高效执行计划
• 可添加 WITH CHECK OPTION：确保通过视图插入/更新的数据符合视图定义条件
• 性能调优关键：始终通过 EXPLAIN 分析视图展开后的实际执行计划，优化底层SQL

说明：视图是虚拟表，不存储数据，执行时会被解析为底层SQL，性能与直接执行等价SQL一致；如果性能较差，原因在于底层查询复杂（如：多表JOIN、子查询、聚合），而非视图本身开销；多层嵌套视图可能增加优化器负担，导致执行计划次优，应谨慎使用。

46、存储过程（CREATE PROCEDURE）

场景示例：创建一个存储过程，根据班级ID返回该班学生人数。

SQL模板及解析：

-- 创建存储过程
DELIMITER $$

CREATE PROCEDURE GetStudentCountByClass(
    IN class_id_param INT,
    OUT student_count INT
)
BEGIN
    SELECT COUNT(*) INTO student_count
    FROM students
    WHERE class_id = class_id_param;
END$$

DELIMITER ;

-- 调用存储过程
CALL GetStudentCountByClass(1, @count);
SELECT @count AS student_count;

• IN：输入参数
• OUT：输出参数
• DELIMITER：更改语句结束符，避免冲突
• INTO：将查询结果赋值给变量

常见误区：

• 忘记改 DELIMITER → 语法错误
• 存储过程调试困难

优化建议：

• 用于封装复杂业务逻辑
• 要避免在应用中过度依赖
• 要注意权限和版本兼容性

⚠️ 注意：MySQL存储过程中，如果参数名与表字段名相同，WHERE 字段 = 参数 会被解析为 字段 = 字段，导致条件恒真。建议参数使用前缀（如：p_、v_）或后缀（如：_param）加以区分。

47、时间函数处理（日期计算）

场景示例：查询最近7天内创建的学生。

SQL模板及解析：

-- 查询最近7天创建的学生
SELECT * FROM students
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 提取出生年份
SELECT name, YEAR(birth_date) AS birth_year FROM students;

-- 计算年龄
SELECT name, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM students;

• NOW()：当前日期时间
• DATE_SUB(date, INTERVAL n DAY/MONTH/YEAR)
• TIMESTAMPDIFF(unit, start, end)：计算时间差

常见误区：

• 使用 DATEDIFF 忽略时间部分
• YEAR(NOW()) - YEAR(birth_date) 不精确（未过生日）

优化建议：

• 年龄计算用 TIMESTAMPDIFF
• 时间字段建立索引，可加速范围查询

48、正则匹配（REGEXP）

场景示例：查询姓名包含“张”或“李”的学生。

SQL模板及解析：

-- 正则匹配
SELECT * FROM students
WHERE name REGEXP ‘张|李’；

-- 匹配以“张”开头
SELECT * FROM students WHERE name REGEXP ‘^张’；

-- 匹配数字
SELECT * FROM students WHERE name REGEXP ‘[0-9]’；

• REGEXP/RLIKE：正则表达式匹配
• 支持 ^（开头）、$（结尾）、|（或）、[]（字符集）

常见误区：

• 性能极差，无法使用索引
• 误用复杂正则，导致阻塞

优化建议：

• 小数据量使用
• 大文本搜索，建议用全文索引或 Elasticsearch
• 可配合 LIKE，先过滤再正则

49、用户变量使用（@var）

场景示例：按分数排序，并为每行添加行号。

SQL模板及解析：

-- 使用用户变量实现行号
SELECT
    name,
    score,
    @row_number := @row_number + 1 AS row_num
FROM students
CROSS JOIN (SELECT @row_number := 0) r
ORDER BY score DESC;

• @var_name：用户会话变量
• 初始化，通常用子查询 CROSS JOIN
• 变量赋值与使用在同一行

常见误区：

• 变量初始化位置错误 → 结果不稳定
• 在 WHERE 中使用变量赋值 → 行为不可预测

优化建议：

• 优先使用窗口函数 ROW_NUMBER() 替代：

  SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
  FROM students;

• 用户变量适合简单场景或兼容老版本

50、导出数据（SELECT INTO OUTFILE）

场景示例：将学生信息导出为CSV文件。

SQL模板及解析：

-- 导出为CSV（含表头）
SELECT ‘姓名’， ‘性别’， ‘出生日期’， ‘班级ID’， ‘分数’
UNION ALL
SELECT name, gender, birth_date, class_id, score
FROM students
INTO OUTFILE ‘/tmp/students.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’；

• INTO OUTFILE ‘path’：将结果写入MySQL服务器所在主机的文件系统（非客户端本地）
• FIELDS TERMINATED BY：字段分隔符（如：,）
• ENCLOSED BY：字段包围符（如："，用于包含含逗号的文本）
• 需要MySQL用户具备 FILE 权限，即使有 FILE 权限，如果 secure_file_priv 为NULL，仍无法导出

⚠️ 关键限制与常见误区：

• 仅在服务器端生效：该语句必须在MySQL服务端执行，本地客户端工具（如：Navicat、MySQL Workbench）直接运行会失败，因为路径指向的是服务器磁盘，而不是我们的电脑
• 文件不能已存在：如果目标文件已存在，MySQL不会覆盖，而是直接报错
• 受 secure_file_priv 严格限制：
  • MySQL默认通过 secure_file_priv 系统变量限制导出目录
  • 可以通过以下命令查看允许的路径：

    SHOW VARIABLES LIKE ‘secure_file_priv’；

    如果返回值为 /var/lib/mysql-files/，则只能导出到该目录；如果返回值为 NULL，则完全禁止 INTO OUTFILE 操作；如果为空字符串 ‘’，则允许任意路径（极少见，高风险）

  • 如果需要修改，须在MySQL配置文件（如：my.cnf）中设置：

    [mysqld]
    secure_file_priv = “/custom/export/path”

    并重启服务

优化建议：

• 生产环境，要慎用：INTO OUTFILE 可能导致敏感数据直接落盘，存在泄露风险
• 优先使用更安全的替代方法：使用 mysqldump --tab 导出结构化数据，或在应用层（如：Python、Java）执行查询并写入本地文件
• 要保证导出目录可写：路径必须是MySQL服务进程有写权限的目录，且符合 secure_file_priv 限制

---

以上就是50条常用的MySQL SQL语句，涵盖了从基础到进阶的绝大部分操作场景。理解并熟练运用它们，将极大地提升你操作数据库的效率与准确性。欢迎在 云栈社区 交流更多技术问题与实践经验。