MySQL 基础 SQL 完整练习：从增删改查到多表连接与子查询

目录

前言

一、数据准备与回顾

1.1 study 表结构与已有语句整理

1.2 准备第二张测试表（用于连表查询）

二、多条件查询、模糊查询与 NULL 判断

三、分组与 HAVING（分组后过滤）

四、连表查询

4.1 隐式内连接（WHERE 条件，不推荐）

4.2 显式内连接 INNER JOIN（只取两边匹配到的数据）

4.3 左外连接 LEFT JOIN：左表全部，右表匹配不到则填 NULL

4.4 右外连接 RIGHT JOIN：右表全部，左表匹配不到则填 NULL

4.5 自连接：一张表自己连接自己

五、子查询

5.1 标量子查询（返回单个值）

5.2 列子查询（返回一列，搭配 IN）

5.3 EXISTS 子查询：判断子查询是否有结果

六、UNION 集合合并

七、DDL 补充语句

八、DCL 简单演示（MySQL 8.0）

九、常用函数

十、实战练习

练习 1：查询每个班级年龄最大的学生

练习 2：使用子查询找出没有学生的班级

练习 3：统计每个班级的男女学生人数

练习 4：查询年龄大于班级平均年龄的学生

练习 5：使用 UNION 合并不同条件的查询结果

十一、核心概念与SQL语句补充

1. 基础篇补充

1.1 约束（Constraints）

1.2 多表关系设计与实现

1. 一对多关系（One-to-Many）

2. 多对多关系（Many-to-Many）

3. 一对一关系（One-to-One）

4. 外键约束与级联操作

1.3 事务（Transactions）

并发事务问题

事务隔离级别

2. 进阶篇补充

2.1 存储引擎

2.2 索引（Indexes）

2.3 SQL优化示例

2.4 视图（Views）

2.5 存储过程（Stored Procedures）

2.6 触发器（Triggers）

2.7 锁（Locks）

3. 运维篇补充

3.1 日志管理

3.2 主从复制配置示例

3.3 分库分表示例

3.4 系统管理与维护

4. InnoDB引擎核心特性

总结

提示：本文内容设计较广，建议先查阅目录

本文是 study 表 SQL 练习的完整续篇，旨在系统性地补齐 MySQL 基础 SQL 语法的剩余核心部分。我们将基于已有的数据表，逐步演示内连接、左连接、右连接、自连接、子查询、UNION、EXISTS、聚合函数、多条件查询、HAVING 过滤等关键操作，并提供可直接运行的代码示例。

在开始前，请确保已创建 study 表并插入测试数据。同时，为了演示连表查询，我们还需要准备第二张班级表 class_info。

一、数据准备与回顾

1.1 study 表结构与已有语句整理

首先，回顾并修正你提供的原有 SQL 语句。注意 MySQL 中的几个关键点：

• 字符串推荐使用单引号，部分版本对双引号支持不友好。
• 在 MySQL 5.7+ 的严格模式下，GROUP BY 非聚合查询会因 ONLY_FULL_GROUP_BY 而报错，分组后只能查询分组字段或聚合函数。

— 基础增删改查
INSERT INTO study(name, age, sex, class) VALUES('张云舟', 29, '男', 2);
SELECT * FROM study;
DELETE FROM study WHERE id = 8;
UPDATE study SET name = '左苏', sex = '女' WHERE id = 9;
SELECT class FROM study WHERE class = 1;
SELECT class FROM study GROUP BY class;
— ❗ 错误示例（在 ONLY_FULL_GROUP_BY 开启时禁止执行）：
— SELECT * FROM study GROUP BY class; — 分组后只能查分组字段/聚合函数
SELECT name FROM study WHERE id IN (1, 2, 3);
ALTER TABLE study RENAME COLUMN sex TO to_sex;
SELECT name AS '名字' FROM study;
SELECT * FROM study LIMIT 0, 3;
SELECT name AS user_infos_example FROM study WHERE id IN (1, 2);
SELECT name, to_sex, class FROM study WHERE class = 1;
SELECT name FROM study WHERE id > 3;
SELECT name FROM study WHERE to_sex NOT IN ('男');
SELECT * FROM study ORDER BY age ASC, id ASC;
SELECT AVG(age) AS new FROM study WHERE age > 30;
SELECT ROUND(age, 1) AS new_age FROM study WHERE age > 30;
SELECT age AS new_age FROM study;
INSERT INTO study(name, gender, age) VALUES('qqq', '男', 19), ('aaa', '男', 38), ('yyy', '女', 15);

1.2 准备第二张测试表（用于连表查询）

创建班级表 class_info，并与 study 表的 class 字段建立关联。

— 班级表 class_info
CREATE TABLE class_info (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) COMMENT '班级名称'
);

INSERT INTO class_info(cname) VALUES ('一班'), ('二班'), ('三班');

— study 表的 class 字段对应 class_info.cid
SELECT * FROM study;
SELECT * FROM class_info;

二、多条件查询、模糊查询与 NULL 判断

— AND 并且
SELECT * FROM study WHERE age > 18 AND class = 2;

— OR 或者
SELECT * FROM study WHERE age < 20 OR class = 1;

— 模糊查询：% 匹配任意字符，_ 匹配单个字符
SELECT * FROM study WHERE name LIKE '张%';
SELECT * FROM study WHERE name LIKE '_云%';

— 判断 NULL：不能用 = NULL，必须用 IS NULL 或 IS NOT NULL
SELECT * FROM study WHERE to_sex IS NULL;
SELECT * FROM study WHERE to_sex IS NOT NULL;

— BETWEEN 范围查询
SELECT * FROM study WHERE age BETWEEN 18 AND 30;

三、分组与 HAVING（分组后过滤）

WHERE 用于分组前过滤原始数据，HAVING 用于分组后对聚合结果进行过滤。

— 查询每个班级的学生数量及平均年龄，且只显示学生数 ≥ 2 的班级
SELECT class, COUNT(*) AS num, AVG(age) AS avg_age
FROM study
WHERE age >= 16
GROUP BY class
HAVING COUNT(*) >= 2;

四、连表查询

4.1 隐式内连接（WHERE 条件，不推荐）

SELECT s.*, ci.cname
FROM study s, class_info ci
WHERE s.class = ci.cid;

4.2 显式内连接 INNER JOIN（只取两边匹配到的数据）

SELECT s.name, s.age, ci.cname
FROM study s
INNER JOIN class_info ci ON s.class = ci.cid;

4.3 左外连接 LEFT JOIN：左表全部，右表匹配不到则填 NULL

— study 全部学生，对应班级，没有班级则班级名称为 NULL
SELECT s.name, s.age, ci.cname
FROM study s
LEFT JOIN class_info ci ON s.class = ci.cid;

4.4 右外连接 RIGHT JOIN：右表全部，左表匹配不到则填 NULL

SELECT s.name, s.age, ci.cname
FROM study s
RIGHT JOIN class_info ci ON s.class = ci.cid;

4.5 自连接：一张表自己连接自己

示例场景：为 study 表增加 leader_id 字段，代表学生组长的 id。

ALTER TABLE study ADD COLUMN leader_id INT;

— 查询学生和他的组长名字
SELECT a.name AS student_name, b.name AS leader_name
FROM study a
LEFT JOIN study b ON a.leader_id = b.id;

五、子查询

5.1 标量子查询（返回单个值）

— 查询一班的学生：先查一班的 cid
SELECT * FROM study
WHERE class = (SELECT cid FROM class_info WHERE cname = '一班');

5.2 列子查询（返回一列，搭配 IN）

SELECT * FROM study
WHERE class IN (SELECT cid FROM class_info WHERE cid > 1);

5.3 EXISTS 子查询：判断子查询是否有结果

— 存在 id > 2 的班级，就查询该班级的全部学生
SELECT * FROM study s
WHERE EXISTS (
SELECT * FROM class_info ci
WHERE ci.cid > 2 AND s.class = ci.cid
);

六、UNION 集合合并

— UNION 自动去重
SELECT name, age FROM study WHERE class = 1
UNION
SELECT name, age FROM study WHERE age > 25;

— UNION ALL 不去重，速度更快
SELECT name, age FROM study WHERE class = 1
UNION ALL
SELECT name, age FROM study WHERE age > 25;

七、DDL 补充语句

— 添加字段
ALTER TABLE study ADD COLUMN phone VARCHAR(11);

— 修改字段类型
ALTER TABLE study MODIFY COLUMN phone VARCHAR(13);

— 删除字段
ALTER TABLE study DROP COLUMN phone;

— 修改表名
RENAME TABLE study TO student;

— 删除表
DROP TABLE IF EXISTS class_info;

八、DCL 简单演示（MySQL 8.0）

— 创建用户
CREATE USER IF NOT EXISTS 'stu_user'@'localhost' IDENTIFIED BY '123456';

— 授权
GRANT SELECT, INSERT, UPDATE ON test.* TO 'stu_user'@'localhost';
FLUSH PRIVILEGES;

— 删除用户
DROP USER 'stu_user'@'localhost';

九、常用函数

— 字符串函数
SELECT name, UPPER(name), LOWER(name) FROM study;
SELECT name, LENGTH(name) FROM study;

— 日期函数
SELECT NOW(), CURDATE();

— 条件判断
SELECT name, IF(age >= 18, '成年', '未成年') AS status FROM study;

— CASE 多分支
SELECT name,
CASE
WHEN class = 1 THEN '一班'
WHEN class = 2 THEN '二班'
ELSE '其他班级'
END AS className
FROM study;

十、实战练习

为了巩固本文所学的 SQL 知识，请尝试完成以下 5 道综合练习题。所有题目均基于 study 和 class_info 表，建议在本地 MySQL 环境中执行验证。

练习 1：查询每个班级年龄最大的学生

题目：使用分组聚合与子查询，找出每个班级中年龄最大的学生信息（包括学生姓名、年龄、班级名称）。

— 参考答案
SELECT s.name, s.age, ci.cname
FROM study s
INNER JOIN class_info ci ON s.class = ci.cid
WHERE (s.class, s.age) IN (
SELECT class, MAX(age)
FROM study
GROUP BY class
);

解析：先通过子查询获取每个班级的最大年龄，然后通过 IN 条件匹配原表，最后连表获取班级名称。

练习 2：使用子查询找出没有学生的班级

题目：使用 NOT EXISTS 或 NOT IN 子查询，查询出没有任何学生的班级信息。

— 方法一：NOT EXISTS
SELECT *
FROM class_info ci
WHERE NOT EXISTS (
SELECT 1
FROM study s
WHERE s.class = ci.cid
);

— 方法二：NOT IN
SELECT *
FROM class_info
WHERE cid NOT IN (SELECT DISTINCT class FROM study WHERE class IS NOT NULL);

解析：NOT EXISTS 更直观，检查是否存在关联记录；NOT IN 需注意子查询结果排除 NULL 值。

练习 3：统计每个班级的男女学生人数

题目：使用 GROUP BY 与 CASE 表达式，统计每个班级的男生和女生人数。

— 参考答案
SELECT
ci.cname AS 班级,
COUNT(CASE WHEN s.to_sex = '男' THEN 1 END) AS 男生人数,
COUNT(CASE WHEN s.to_sex = '女' THEN 1 END) AS 女生人数
FROM study s
RIGHT JOIN class_info ci ON s.class = ci.cid
GROUP BY ci.cid, ci.cname;

解析：使用 RIGHT JOIN 确保所有班级都显示，即使没有学生；CASE 配合 COUNT 实现条件计数。

练习 4：查询年龄大于班级平均年龄的学生

题目：使用关联子查询，查询出年龄大于其所在班级平均年龄的学生信息。

— 参考答案
SELECT s.name, s.age, ci.cname
FROM study s
INNER JOIN class_info ci ON s.class = ci.cid
WHERE s.age > (
SELECT AVG(age)
FROM study s2
WHERE s2.class = s.class
);

解析：关联子查询为每个学生计算其所在班级的平均年龄，外层查询筛选年龄大于该平均值的学生。

练习 5：使用 UNION 合并不同条件的查询结果

题目：查询“一班的所有学生”与“年龄小于 20 岁的所有学生”的并集，要求显示学生姓名、年龄、班级名称，并去除重复记录。

— 参考答案
SELECT s.name, s.age, ci.cname
FROM study s
INNER JOIN class_info ci ON s.class = ci.cid
WHERE ci.cname = '一班'
UNION
SELECT s.name, s.age, ci.cname
FROM study s
INNER JOIN class_info ci ON s.class = ci.cid
WHERE s.age < 20;

解析：两个查询分别通过连表获取班级名称，使用 UNION 自动去重合并结果。

提示：完成练习后，可尝试修改条件或使用其他语法（如 LEFT JOIN、HAVING）实现相同效果，以加深理解。

十一、核心概念与SQL语句补充

为了构建更完整的MySQL知识体系，以下补充基础篇、进阶篇和运维篇的核心概念及对应的SQL语句示例。

1. 基础篇补充

1.1 约束（Constraints）

约束（Constraints）是数据库管理系统（DBMS）用于强制数据完整性和一致性的规则。它们定义在表结构上，确保表中的数据符合特定的业务规则和逻辑要求，防止无效或不一致的数据被插入、更新或删除。约束是数据库设计的重要组成部分，能有效维护数据的准确性和可靠性。

MySQL 支持以下几种主要约束类型：

• 主键约束（PRIMARY KEY）：唯一标识表中的每一行，不允许 NULL 值，且值必须唯一。一张表只能有一个主键。
• 唯一约束（UNIQUE）：确保某列或列组合中的所有值都是唯一的，但允许 NULL 值（MySQL 中 NULL 视为互不相同）。
• 非空约束（NOT NULL）：强制列不能存储 NULL 值，必须包含有效数据。
• 默认约束（DEFAULT）：当插入新行未指定该列值时，自动赋予一个预定义的默认值。
• 检查约束（CHECK）：确保列中的值满足指定的条件（如年龄在 0-120 之间）。MySQL 8.0.16 及以上版本原生支持。
• 外键约束（FOREIGN KEY）：确保一个表中的数据与另一个表中的数据匹配，维护表之间的引用完整性。可以定义级联操作（如 ON DELETE CASCADE）。

约束可以在创建表时定义，也可以在表创建后通过 ALTER TABLE 语句添加或删除。合理使用约束能减少应用层的校验逻辑，将数据规则下推到数据库层面。

— 创建带约束的表
CREATE TABLE student_constraint (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
name VARCHAR(50) NOT NULL COMMENT '姓名，非空',
email VARCHAR(100) UNIQUE COMMENT '邮箱，唯一',
age INT DEFAULT 18 COMMENT '年龄，默认18',
class_id INT,
CHECK (age >= 0 AND age <= 120) COMMENT '检查约束：年龄范围',
CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class_info(cid)
ON DELETE SET NULL ON UPDATE CASCADE
);

— 添加约束
ALTER TABLE study ADD CONSTRAINT pk_study PRIMARY KEY (id);
ALTER TABLE study MODIFY COLUMN name VARCHAR(50) NOT NULL;
ALTER TABLE study ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE study ADD CONSTRAINT fk_study_class FOREIGN KEY (class) REFERENCES class_info(cid);

— 删除外键约束
ALTER TABLE study DROP FOREIGN KEY fk_study_class;

1.2 多表关系设计与实现

在数据库设计中，表与表之间通常存在三种基本关系：一对多（One-to-Many）、多对多（Many-to-Many）和一对一（One-to-One）。合理设计这些关系是构建高效、可维护数据库的关键。

1. 一对多关系（One-to-Many）

一对多是最常见的关系。一个实体（如班级）可以关联多个其他实体（如学生），但每个学生只属于一个班级。在数据库中，通常在“多”的一方（学生表）添加一个外键字段，指向“一”的一方（班级表）的主键。

示例：本文中的 study 表（学生）与 class_info 表（班级）就是典型的一对多关系。

— 一对多关系已在 study 表中实现
— study.class 字段作为外键，引用 class_info.cid
SELECT s.name, s.age, ci.cname
FROM study s
INNER JOIN class_info ci ON s.class = ci.cid;

2. 多对多关系（Many-to-Many）

多对多关系指一个实体可以关联多个其他实体，反之亦然。例如，一个学生可以选择多门课程，一门课程也可以被多个学生选择。这种关系无法直接通过两个表的外键实现，需要引入一个关联表（Junction Table）来存储两个表之间的关联关系。

设计要点：

• 创建两个主表（如 student、course）。
• 创建一个关联表（如 student_course），包含两个外键，分别指向两个主表的主键。
• 关联表的主键通常是这两个外键的组合（复合主键），以确保唯一性。

— 创建课程表
CREATE TABLE course (
course_id INT PRIMARY KEY AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL
);

— 创建学生选课关联表
CREATE TABLE student_course (
student_id INT,
course_id INT,
score DECIMAL(5,2), — 可选：记录学生在该课程的成绩
PRIMARY KEY (student_id, course_id), — 复合主键
FOREIGN KEY (student_id) REFERENCES study(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE
);

— 查询学生及其所选课程
SELECT s.name AS student_name, c.course_name, sc.score
FROM study s
INNER JOIN student_course sc ON s.id = sc.student_id
INNER JOIN course c ON sc.course_id = c.course_id;

3. 一对一关系（One-to-One）

一对一关系指一个实体最多只与另一个实体关联一次。这种关系相对少见，通常用于将一张大表拆分为多个小表，以提升查询性能或满足业务隔离需求。

实现方式：

• 共享主键：从表的主键同时作为外键，引用主表的主键。
• 外键唯一约束：从表创建一个外键字段指向主表主键，并给该外键添加唯一约束（UNIQUE）。

— 方式一：共享主键（student_detail 表的主键 student_id 引用 study.id）
CREATE TABLE student_detail (
student_id INT PRIMARY KEY, — 既是主键，也是外键
phone VARCHAR(20),
address TEXT,
FOREIGN KEY (student_id) REFERENCES study(id) ON DELETE CASCADE
);

— 方式二：外键唯一约束
CREATE TABLE student_detail (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT UNIQUE, — 唯一约束确保一对一
phone VARCHAR(20),
address TEXT,
FOREIGN KEY (student_id) REFERENCES study(id) ON DELETE CASCADE
);

— 查询学生及其详细信息
SELECT s.name, s.age, sd.phone, sd.address
FROM study s
LEFT JOIN student_detail sd ON s.id = sd.student_id;

4. 外键约束与级联操作

在定义外键时，可以指定级联操作，以自动维护数据的一致性：

• ON DELETE CASCADE：当主表记录被删除时，自动删除从表关联记录。
• ON DELETE SET NULL：当主表记录被删除时，将从表外键字段设为 NULL。
• ON UPDATE CASCADE：当主表主键更新时，自动更新从表外键值。

— 示例：带级联操作的外键
ALTER TABLE study
ADD CONSTRAINT fk_study_class
FOREIGN KEY (class) REFERENCES class_info(cid)
ON DELETE SET NULL ON UPDATE CASCADE;

合理运用多表关系设计和外键约束，可以极大地保证数据的完整性和一致性，为复杂的业务查询打下坚实基础。

1.3 事务（Transactions）

事务（Transactions）是数据库管理系统（DBMS）中保证数据一致性和完整性的核心机制。它是一组不可分割的数据库操作序列，要么全部成功执行，要么全部失败回滚，确保数据库从一个一致性状态转换到另一个一致性状态。事务主要用于处理需要多个步骤才能完成的业务逻辑，如银行转账、订单支付等场景。

事务具有 ACID 四大特性：

• 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不执行，不会停留在中间状态。
• 一致性（Consistency）：事务执行前后，数据库必须保持一致性状态，即所有约束、触发器等规则都得到满足。
• 隔离性（Isolation）：多个事务并发执行时，每个事务的操作对其他事务是隔离的，互不干扰。
• 持久性（Durability）：事务一旦提交，其对数据库的修改就是永久性的，即使系统故障也不会丢失。

并发事务问题

当多个事务并发执行时，如果没有适当的隔离机制，可能会出现以下问题：

• 脏读（Dirty Read）：一个事务读取了另一个事务尚未提交的数据。如果那个事务回滚，读取到的就是无效数据。
• 不可重复读（Non-repeatable Read）：在同一事务中，多次读取同一数据得到的结果不一致。这是因为在两次读取之间，另一个事务修改并提交了该数据。
• 幻读（Phantom Read）：在同一事务中，多次执行同一查询返回的结果集行数不一致。这是因为在两次查询之间，另一个事务插入或删除了符合查询条件的行。

事务隔离级别

MySQL 提供了四种事务隔离级别来解决上述并发问题：

• 读未提交（READ UNCOMMITTED）：最低隔离级别，允许脏读、不可重复读和幻读。
• 读已提交（READ COMMITTED）：只允许读取已提交的数据，避免脏读，但可能出现不可重复读和幻读。
• 可重复读（REPEATABLE READ）：MySQL 默认隔离级别。确保同一事务中多次读取同一数据的结果一致，避免脏读和不可重复读，但可能出现幻读。
• 串行化（SERIALIZABLE）：最高隔离级别，完全串行执行事务，避免所有并发问题，但性能最低。

— 事务基本操作
START TRANSACTION; — 或 BEGIN

— 执行一系列SQL
UPDATE study SET age = age + 1 WHERE id = 1;
INSERT INTO class_info (cname) VALUES ('四班');

— 提交事务
COMMIT;

— 或回滚事务
ROLLBACK;

— 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

— 查看当前事务隔离级别
SELECT @@transaction_isolation;

— 演示脏读场景（需要在两个会话中执行）
— 会话1：开启事务并修改数据但不提交
START TRANSACTION;
UPDATE study SET age = 30 WHERE id = 1;
— 此时 age=30 未提交

— 会话2：设置隔离级别为 READ UNCOMMITTED 并查询
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT age FROM study WHERE id = 1; — 可能读到30（脏读）
COMMIT;

— 会话1：回滚
ROLLBACK; — age恢复原值，会话2读到的30是无效数据

— 避免脏读：使用 READ COMMITTED 或更高隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在实际开发中，应根据业务需求选择合适的事务隔离级别。通常使用默认的 REPEATABLE READ 级别，在需要更高一致性时使用 SERIALIZABLE，在只读场景且对数据一致性要求不高时可以考虑 READ COMMITTED。

2. 进阶篇补充

2.1 存储引擎

存储引擎（Storage Engine）是 MySQL 中负责数据存储、索引管理、事务处理等底层机制的组件。不同的存储引擎在性能、事务支持、锁粒度、崩溃恢复等方面各有差异，选择合适的存储引擎对系统整体表现至关重要。

MySQL 中最常用的存储引擎是 InnoDB 和 MyISAM。其中 InnoDB 是 MySQL 5.5 之后的默认引擎，支持事务、行级锁和外键约束，适合绝大多数业务场景；MyISAM 不支持事务，使用表级锁，在只读或读多写少的场景下查询性能较好；MEMORY 引擎将数据存储在内存中，读写速度极快，但重启后数据会丢失，适合临时表或缓存类数据。

InnoDB 与 MyISAM 的核心区别：

• 事务支持：InnoDB 支持 ACID 事务，MyISAM 完全不支持事务，一旦中途失败无法回滚。
• 锁粒度：InnoDB 使用行级锁，并发写入时冲突更少；MyISAM 使用表级锁，写入时会锁住整张表，并发性能较差。
• 外键约束：InnoDB 支持外键和级联操作，MyISAM 不支持外键。
• 崩溃恢复：InnoDB 通过 redo log 实现崩溃安全恢复，MyISAM 崩溃后容易损坏表数据。
• 全文索引：MyISAM 原生支持全文索引，InnoDB 在 MySQL 5.6+ 也支持全文索引。
• 数据缓存：InnoDB 使用缓冲池（Buffer Pool）缓存数据和索引，MyISAM 只缓存索引，数据依赖操作系统缓存。

在实际开发中，建议优先使用 InnoDB，只有在明确不需要事务且以查询为主的场景下才考虑 MyISAM。可以通过 SHOW ENGINES 查看当前 MySQL 支持的所有引擎及其特性。

— 查看所有存储引擎
SHOW ENGINES;

— 查看表的存储引擎
SHOW TABLE STATUS LIKE 'study';

— 创建表时指定存储引擎
CREATE TABLE myisam_table (
id INT PRIMARY KEY
) ENGINE=MyISAM;

CREATE TABLE memory_table (
id INT PRIMARY KEY
) ENGINE=MEMORY;

— 修改表的存储引擎
ALTER TABLE study ENGINE = InnoDB;

— 查看 InnoDB 缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

— 查看 InnoDB 是否开启独立表空间
SHOW VARIABLES LIKE 'innodb_file_per_table';

2.2 索引（Indexes）

索引（Index）是数据库系统中用于加速数据检索的一种数据结构，类似于书籍的目录。通过索引，MySQL 可以快速定位到目标数据行，而无需扫描整张表。合理使用索引能显著提升查询性能，但索引也会占用额外存储空间，并降低写入（INSERT/UPDATE/DELETE）的速度，因此需要权衡利弊。

索引的分类：

• 普通索引（INDEX）：最基本的索引类型，没有任何唯一性限制，用于加速查询。
• 唯一索引（UNIQUE INDEX）：索引列的值必须唯一，允许 NULL 值（NULL 可重复），常用于邮箱、身份证号等字段。
• 主键索引（PRIMARY KEY）：特殊的唯一索引，不允许 NULL 值，一张表只能有一个主键索引。
• 复合索引（Composite Index）：在多个列上创建的索引，遵循最左前缀原则，查询时需从最左边的列开始匹配。
• 全文索引（FULLTEXT）：用于全文搜索，支持对文本内容进行关键词匹配，适合大文本字段。

索引使用注意事项：

• 不要对频繁更新的列创建索引，否则会拖慢写入速度。
• 不要对数据量很小的表创建索引，全表扫描可能更快。
• 避免在 WHERE 子句中对索引列使用函数或表达式，否则索引会失效。
• 使用 EXPLAIN 分析查询计划，确认索引是否被正确使用。

— 创建索引
CREATE INDEX idx_age ON study(age);
CREATE UNIQUE INDEX idx_name ON study(name);
CREATE INDEX idx_class_age ON study(class, age); — 复合索引

— 查看索引
SHOW INDEX FROM study;

— 删除索引
DROP INDEX idx_age ON study;

— 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM study WHERE age > 20;

— 强制使用索引
SELECT * FROM study FORCE INDEX (idx_age) WHERE age > 20;

— 创建全文索引
CREATE FULLTEXT INDEX idx_name_fulltext ON study(name);

— 使用全文索引查询
SELECT * FROM study WHERE MATCH(name) AGAINST('张' IN NATURAL LANGUAGE MODE);

2.3 SQL优化示例

SQL 优化是数据库性能调优的核心环节。通过优化 SQL 语句和表结构，可以显著减少查询响应时间、降低数据库负载。下面从几个常见维度介绍优化技巧。

优化原则：

• 避免 SELECT *：只查询需要的字段，减少数据传输量。
• 使用索引：确保 WHERE、ORDER BY、GROUP BY 涉及的列有合适的索引。
• 减少回表：使用覆盖索引（索引中包含查询所需的所有列），避免回表查询。
• 分批处理：大批量操作时拆分为小批次，避免长时间锁表。
• 避免隐式类型转换：字段类型与查询条件类型不一致会导致索引失效。

— 插入优化：批量插入
INSERT INTO study (name, age, to_sex, class) VALUES
('张三', 20, '男', 1),
('李四', 21, '女', 2),
('王五', 22, '男', 1);

— 主键优化：使用自增主键
ALTER TABLE study MODIFY id INT AUTO_INCREMENT;

— ORDER BY优化：使用索引覆盖
CREATE INDEX idx_class_age_name ON study(class, age, name);
EXPLAIN SELECT name, age FROM study WHERE class = 1 ORDER BY age;

— GROUP BY优化：使用索引
EXPLAIN SELECT class, COUNT(*) FROM study GROUP BY class;

— LIMIT优化：避免大偏移量
— 不好：SELECT * FROM study LIMIT 1000000, 10
— 优化：SELECT * FROM study WHERE id > 1000000 LIMIT 10

— COUNT优化：使用COUNT(1)或COUNT(*)
SELECT COUNT(1) FROM study;
SELECT COUNT(*) FROM study;

— UPDATE优化：避免行锁升级为表锁
— 确保WHERE条件使用索引列
UPDATE study SET age = age + 1 WHERE id = 1; — id是主键索引

— 避免在索引列上使用函数（会导致索引失效）
— 不好：SELECT * FROM study WHERE YEAR(create_time) = 2024;
— 优化：SELECT * FROM study WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

— 使用EXPLAIN查看执行计划
EXPLAIN SELECT s.name, ci.cname
FROM study s
INNER JOIN class_info ci ON s.class = ci.cid
WHERE s.age > 20;

2.4 视图（Views）

视图（View）是一个虚拟表，其内容由 SQL 查询定义。视图不存储实际数据，而是在查询视图时动态生成结果。视图可以看作是对复杂查询的封装，提供了一种安全、简洁的数据访问方式。

视图的优点：

• 简化查询：将复杂的多表连接、子查询封装为视图，用户只需查询视图即可。
• 安全性：可以只暴露部分字段给特定用户，隐藏敏感数据（如密码、身份证号）。
• 逻辑独立性：当底层表结构变化时，可以通过修改视图来保持对外接口不变。
• 数据一致性：视图始终反映底层表的最新数据，无需手动维护。

视图的注意事项：

• 视图不占用物理存储空间，但会占用数据库的元数据空间。
• 对视图的 INSERT、UPDATE、DELETE 操作有限制，通常要求视图是可更新的（即视图中的行与底层表的行一一对应）。
• 使用 WITH CHECK OPTION 可以确保通过视图进行的修改满足视图的 WHERE 条件。

— 创建视图
CREATE VIEW student_info_view AS
SELECT s.id, s.name, s.age, ci.cname
FROM study s
LEFT JOIN class_info ci ON s.class = ci.cid;

— 查询视图
SELECT * FROM student_info_view WHERE age > 20;

— 创建带检查选项的视图
CREATE VIEW adult_students AS
SELECT * FROM study WHERE age >= 18
WITH CHECK OPTION;

— 修改视图
CREATE OR REPLACE VIEW student_info_view AS
SELECT s.id, s.name, s.age, s.to_sex, ci.cname
FROM study s
LEFT JOIN class_info ci ON s.class = ci.cid;

— 查看视图定义
SHOW CREATE VIEW student_info_view;

— 删除视图
DROP VIEW IF EXISTS student_info_view;

2.5 存储过程（Stored Procedures）

存储过程（Stored Procedure）是一组预编译的 SQL 语句集合，存储在数据库服务器中，可以通过名称调用。存储过程可以接收输入参数、返回输出参数，并支持流程控制语句（如 IF、CASE、LOOP），适合封装复杂的业务逻辑。

存储过程的优点：

• 性能提升：存储过程在首次执行时被编译并缓存，后续调用直接执行，减少网络传输和解析开销。
• 代码复用：将常用业务逻辑封装为存储过程，多个应用可以共享调用。
• 安全性：可以授予用户执行存储过程的权限，而不直接授予表操作权限，控制数据访问粒度。
• 减少网络流量：客户端只需发送 CALL 语句，无需传输大量 SQL 文本。

存储过程的参数类型：

• IN：输入参数，调用时传入值，过程内部不能修改。
• OUT：输出参数，过程内部赋值，调用后可以获取结果。
• INOUT：输入输出参数，既可以传入值，也可以被过程修改后返回。

— 创建存储过程
DELIMITER //
CREATE PROCEDURE get_student_by_class(IN class_id INT)
BEGIN
SELECT * FROM study WHERE class = class_id;
END //
DELIMITER ;

— 调用存储过程
CALL get_student_by_class(1);

— 带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE count_students_by_class(
IN class_id INT,
OUT student_count INT
)
BEGIN
SELECT COUNT(*) INTO student_count
FROM study WHERE class = class_id;
END //
DELIMITER ;

— 调用
CALL count_students_by_class(1, @count);
SELECT @count;

— 带流程控制的存储过程
DELIMITER //
CREATE PROCEDURE get_student_status(IN student_id INT)
BEGIN
DECLARE student_age INT;
DECLARE status_msg VARCHAR(50);

SELECT age INTO student_age FROM study WHERE id = student_id;

IF student_age >= 18 THEN
SET status_msg = '成年';
ELSE
SET status_msg = '未成年';
END IF;

SELECT student_id AS id, student_age AS age, status_msg AS status;
END //
DELIMITER ;

— 调用
CALL get_student_status(1);

— 删除存储过程
DROP PROCEDURE IF EXISTS get_student_by_class;

2.6 触发器（Triggers）

触发器（Trigger）是数据库中的一种特殊存储过程，它在指定的表上执行 INSERT、UPDATE、DELETE 操作时自动触发执行。触发器常用于自动维护数据一致性、审计日志、数据校验等场景，无需应用层显式调用。

触发器的类型：

• BEFORE 触发器：在操作执行之前触发，常用于数据校验或修改即将写入的值。
• AFTER 触发器：在操作执行之后触发，常用于记录审计日志或同步关联表数据。
• 行级触发器（FOR EACH ROW）：对每一行受影响的数据都会执行一次。
• 语句级触发器：对整个 SQL 语句只执行一次（MySQL 目前仅支持行级触发器）。

触发器中的关键字：

• NEW：表示插入或更新后的新行数据，可用于 INSERT 和 UPDATE 触发器。
• OLD：表示更新或删除前的旧行数据，可用于 UPDATE 和 DELETE 触发器。

— 创建INSERT触发器：插入前校验年龄，负数自动归零
DELIMITER //
CREATE TRIGGER before_student_insert
BEFORE INSERT ON study
FOR EACH ROW
BEGIN
IF NEW.age < 0 THEN
SET NEW.age = 0;
END IF;
END //
DELIMITER ;

— 创建UPDATE触发器：更新后记录审计日志
DELIMITER //
CREATE TRIGGER after_student_update
AFTER UPDATE ON study
FOR EACH ROW
BEGIN
INSERT INTO student_audit(student_id, old_name, new_name, change_time)
VALUES (OLD.id, OLD.name, NEW.name, NOW());
END //
DELIMITER ;

— 创建DELETE触发器：删除前备份数据
DELIMITER //
CREATE TRIGGER before_student_delete
BEFORE DELETE ON study
FOR EACH ROW
BEGIN
INSERT INTO deleted_students_backup
SELECT *, NOW() FROM study WHERE id = OLD.id;
END //
DELIMITER ;

— 查看触发器
SHOW TRIGGERS;

— 删除触发器
DROP TRIGGER IF EXISTS before_student_insert;

使用建议：触发器虽然能自动维护数据逻辑，但过度使用会增加数据库负担，且隐式执行不易排查问题。建议仅在确实需要数据库层保证一致性的场景使用，并保持触发器逻辑简单清晰。

2.7 锁（Locks）

锁（Lock）是数据库用于控制并发访问、保证数据一致性的重要机制。MySQL 中的锁按粒度可分为全局锁、表级锁和行级锁，按读写性质又可分为共享锁（读锁）和排他锁（写锁）。合理使用锁能避免并发事务之间的数据冲突，但过度加锁也会降低系统吞吐量，因此需要根据业务场景权衡。

锁的分类：

• 全局锁：锁定整个数据库实例，常用于全库备份，保证备份期间数据一致性。
• 表级锁：锁定整张表，MyISAM 引擎使用表级锁，并发写入时性能较差。
• 行级锁：只锁定涉及的数据行，InnoDB 引擎默认使用行级锁，并发性能更好。
• 共享锁（读锁，LOCK IN SHARE MODE）：多个事务可以同时持有共享锁，但都不能修改数据。
• 排他锁（写锁，FOR UPDATE）：同一时刻只能有一个事务持有排他锁，其他事务既不能读也不能写。

锁的使用注意事项：

• 行级锁只在事务中生效，使用 FOR UPDATE 或 LOCK IN SHARE MODE 后必须提交或回滚事务释放锁。
• 避免长时间持有锁，否则会造成锁等待甚至死锁。
• 尽量让 WHERE 条件命中索引，否则行级锁可能升级为表级锁。
• 通过 information_schema 中的锁表可以排查锁等待和死锁问题。

— 全局锁（备份时使用）
FLUSH TABLES WITH READ LOCK;
— 执行备份操作…
UNLOCK TABLES;

— 表级锁
LOCK TABLES study READ; — 读锁
— 只能读，不能写…
UNLOCK TABLES;

LOCK TABLES study WRITE; — 写锁
— 可以读写…
UNLOCK TABLES;

— 行级锁（InnoDB）
START TRANSACTION;
SELECT * FROM study WHERE id = 1 FOR UPDATE; — 排他锁
— 其他事务不能修改id=1的记录…
COMMIT;

— 共享锁（读锁）
START TRANSACTION;
SELECT * FROM study WHERE id = 1 LOCK IN SHARE MODE;
— 其他事务可以读，但不能修改id=1的记录…
COMMIT;

— 查看锁信息
SHOW OPEN TABLES WHERE In_use > 0;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

— 查看当前是否有死锁
SHOW ENGINE INNODB STATUS\\G

3. 运维篇补充

3.1 日志管理

日志是 MySQL 运维中排查问题、监控性能的重要依据。MySQL 主要包含错误日志、通用查询日志、慢查询日志和二进制日志四类，下面分别演示它们的查看与配置方法。

— 查看错误日志位置
SHOW VARIABLES LIKE 'log_error';

— 开启通用查询日志
SET GLOBAL general_log = 'ON';
SHOW VARIABLES LIKE 'general_log%';

— 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; — 超过2秒的查询
SHOW VARIABLES LIKE 'slow_query%';

— 查看二进制日志
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'binlog.000001' LIMIT 10;

— 清理日志
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
RESET MASTER; — 删除所有二进制日志（谨慎使用）

日志类型说明：

• 错误日志（error log）：记录 MySQL 启动、运行、停止过程中的错误信息，是排查故障的第一入口。
• 通用查询日志（general log）：记录所有客户端连接和执行的 SQL 语句，便于审计，但开启后性能开销较大，生产环境慎用。
• 慢查询日志（slow query log）：记录执行时间超过 long_query_time 阈值的 SQL，是定位性能瓶颈的利器。
• 二进制日志（binlog）：记录所有数据变更操作，用于主从复制和数据恢复，是 MySQL 高可用架构的基础。

3.2 主从复制配置示例

主从复制（Master-Slave Replication）是 MySQL 高可用和读写分离的常见方案。其核心原理是：主库将数据变更写入二进制日志（binlog），从库通过 I/O 线程拉取并写入中继日志（relay log），再由 SQL 线程重放实现数据同步。下面给出完整的配置步骤。

— 主库配置（my.cnf）
— [mysqld]
— server-id=1
— log-bin=mysql-bin
— binlog-format=ROW

— 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

— 查看主库状态
SHOW MASTER STATUS;

— 从库配置（my.cnf）
— [mysqld]
— server-id=2
— relay-log=mysql-relay-bin

— 配置从库连接主库
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

— 启动从库复制
START SLAVE;

— 查看从库状态
SHOW SLAVE STATUS\\G

配置要点：

• 主库和从库的 server-id 必须不同，否则复制会冲突。
• 主库需开启 log-bin 并设置 binlog-format=ROW，行级格式能更精确地记录数据变更。
• 从库执行 CHANGE MASTER TO 时，MASTER_LOG_FILE 和 MASTER_LOG_POS 必须与主库 SHOW MASTER STATUS 的结果一致。
• 通过 SHOW SLAVE STATUS\\G 检查 Slave_IO_Running 和 Slave_SQL_Running 是否都为 YES，确认复制正常。

3.3 分库分表示例

当单表数据量过大、写入并发过高时，可以通过分库分表来水平扩展。常见策略包括水平分表（按某个字段将数据分散到多张结构相同的表）和垂直分表（将大字段拆分到独立表）。下面给出基础示例。

— 水平分表：按id范围分表
CREATE TABLE study_0 (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
CHECK (id % 2 = 0)
);

CREATE TABLE study_1 (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
CHECK (id % 2 = 1)
);

— 使用UNION ALL查询所有分表
SELECT * FROM study_0 WHERE age > 20
UNION ALL
SELECT * FROM study_1 WHERE age > 20;

— 垂直分表：将大字段分离
CREATE TABLE study_basic (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

CREATE TABLE study_detail (
id INT PRIMARY KEY,
student_id INT,
address TEXT,
phone VARCHAR(20),
FOREIGN KEY (student_id) REFERENCES study_basic(id)
);

— 使用MyCat分片配置示例（配置文件）
— schema.xml: 定义逻辑库和分片规则
— rule.xml: 定义分片算法
— server.xml: 定义用户和系统参数

分库分表注意事项：

• 水平分表后，跨表查询（如 ORDER BY、JOIN）会变得复杂，通常需要借助中间件（如 MyCat、ShardingSphere）统一路由。
• 分片键的选择至关重要，应尽量让查询条件命中分片键，避免全表扫描所有分片。
• 垂直分表适合将访问频率低的大字段（如 TEXT、BLOB）拆分出去，减少主表的行宽，提升查询效率。
• 分库分表会引入分布式事务、全局主键等新问题，需结合业务场景权衡是否必要。

3.4 系统管理与维护

日常运维中，需要掌握进程管理、参数调优、备份恢复和表维护等常用操作。下面汇总了系统管理与维护的核心命令。

— 查看系统数据库
SHOW DATABASES LIKE '%schema%';

— 查看进程列表
SHOW PROCESSLIST;

— 杀死进程
KILL [process_id];

— 查看变量
SHOW VARIABLES LIKE '%buffer%';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

— 设置变量
SET GLOBAL innodb_buffer_pool_size = 1073741824; — 1GB

— 备份数据库
— mysqldump -u root -p test > backup.sql

— 恢复数据库
— mysql -u root -p test < backup.sql

— 查看表空间信息
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ENGINE,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test';

— 优化表
OPTIMIZE TABLE study;

— 分析表
ANALYZE TABLE study;

— 修复表
REPAIR TABLE study;

维护建议：

• SHOW PROCESSLIST 可查看当前所有连接和正在执行的 SQL，发现长时间运行的查询可结合 KILL 终止。
• 调整 innodb_buffer_pool_size 等参数后，部分参数需重启生效，部分可通过 SET GLOBAL 动态修改，建议修改前先确认参数类型。
• 定期使用 mysqldump 备份数据，并验证备份文件可正常恢复，避免备份失效。
• OPTIMIZE TABLE 可回收碎片空间，ANALYZE TABLE 更新统计信息帮助优化器生成更优执行计划，建议在业务低峰期执行。

4. InnoDB引擎核心特性

— 查看InnoDB状态
SHOW ENGINE INNODB STATUS\\G

— 查看缓冲池信息
SELECT
POOL_ID,
POOL_SIZE,
FREE_BUFFERS,
DATABASE_PAGES
FROM information_schema.INNODB_BUFFER_POOL_STATS;

— 查看事务信息
SELECT * FROM information_schema.INNODB_TRX;

— 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

— 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION_ISOLATION = 'REPEATABLE-READ';

— MVCC相关查询
— 查看当前事务ID
SELECT TRX_ID FROM information_schema.INNODB_TRX
WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

— 查看undo日志
SHOW VARIABLES LIKE 'innodb_undo%';

— 查看redo日志
SHOW VARIABLES LIKE 'innodb_log%';

以上补充内容涵盖了MySQL从基础到进阶再到运维的核心概念和SQL语句。建议在实际环境中逐步实践这些示例，并结合官方文档深入理解每个概念的原理和应用场景。

总结

本文通过 study 与 class_info 两张表，系统性地演示了 MySQL 基础 SQL 的完整语法体系，涵盖了从简单的增删改查（CRUD）到复杂的多表连接、子查询、集合操作与数据控制。建议读者在本地环境中逐条执行这些 SQL，观察结果，并尝试修改条件以加深理解。掌握这些基础是进行更高级数据库设计与优化的前提。