MySQL 约束详解与多表复杂查询实战指南

SQL 关键字执行顺序

理解 SQL 语句的执行顺序对于编写复杂的查询逻辑至关重要。MySQL 处理 SQL 语句的实际执行流程如下：

1. FROM：确定数据来源表。
2. ON / JOIN：执行表连接条件匹配。
3. WHERE：对中间结果进行行级过滤。
4. GROUP BY：按指定列进行分组。
5. HAVING：对分组后的结果集进行条件筛选。
6. SELECT：选取需要显示的列及计算字段。
7. DISTINCT：去除重复行。
8. ORDER BY：对最终结果进行排序。
9. LIMIT：限制返回行数。

数据库约束详解

1. 常见约束类型概览

约束用于在数据库层面强制实施业务规则，确保数据的完整性。MySQL 支持以下主要约束：

• NOT NULL：非空约束，规定列不能包含 NULL 值。
• UNIQUE：唯一约束，保证列中每行数据必须唯一。
• DEFAULT：默认值约束，当未指定值时自动填入预设值。
• PRIMARY KEY：主键约束，兼具 NOT NULL 和 UNIQUE 特性，用于唯一标识记录。
• FOREIGN KEY：外键约束，维护两张表之间的参照完整性。
• CHECK：检查约束，验证列值是否符合特定条件（注：MySQL 5.7 及以下版本会解析但忽略此约束，8.0.16+ 开始支持）。

2. NOT NULL 约束实操

当某列必须拥有具体数值时使用。创建表时在字段定义后添加该约束。

-- 创建表并指定 id 不为空
CREATE TABLE student (id INT NOT NULL, name VARCHAR(20));
DESC student;

-- 插入 null 值会报错
INSERT INTO student VALUES (NULL, '张三'); -- ERROR 1048

3. UNIQUE 约束与默认值

UNIQUE 约束防止数据重复插入：

CREATE TABLE student (id INT UNIQUE, name VARCHAR(20) UNIQUE);
INSERT INTO student VALUES (1, '李四'); -- 若 id=1 已存在则报错 Duplicate entry

DEFAULT 默认值用于简化录入：

CREATE TABLE student (id INT, name VARCHAR(20) DEFAULT '匿名');
INSERT INTO student (id) VALUES (1); -- name 将自动填充为 '匿名'

4. PRIMARY KEY 主键约束

主键是唯一标识记录的列，通常使用整数 ID。MySQL 支持自增主键，在插入时将 id 设为 null 可触发自动递增逻辑。

分布式主键策略
在分库分表场景下，单机的自增主键无法保证全局唯一。通用的解决公式为：
全局ID = 时间戳 + 主机编号 + 随机因子，确保在不同时间、不同节点产生的 ID 不发生冲突。

5. FOREIGN KEY 外键与表关系

外键用于建立父表与子表的联系。若子表插入的数据在父表中不存在，则插入失败。

-- 创建父表 class
CREATE TABLE class (class_id INT PRIMARY KEY AUTO_INCREMENT, class_name VARCHAR(20));
-- 创建子表 student 并绑定外键
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES class(class_id)
);

-- 错误示例：插入不存在的班级号
INSERT INTO student (name, class_id) VALUES ('孙李', 4); -- 失败

删除限制：
若有子表依赖，不能直接删除父表记录。需先解除子表关联或使用逻辑删除（设置状态字段标记删除，而非物理丢弃数据），从而避免外键约束报错。

6. 约束背后的索引原理

无论是主键、唯一键还是外键引用的列，MySQL 都会自动为其创建索引。这使得在插入或更新数据时的唯一性校验不再需要进行全表扫描，而是通过 B+ 树结构快速查找，极大提升了性能。

数据库表关系设计

在设计多表结构时，实体间的关系主要分为三种，处理方式各不相同：

1. 一对一关系

如学生与学生档案。
方案 A：合并到一张表中。
方案 B：两表共用一个主键，或通过外键单向关联。

2. 一对多关系

如班级与学生。应在多端（学生表）添加一列存储另一端（班级表）的主键作为外键。

3. 多对多关系

如学生与课程。必须引入第三张中间表（关联表），该表至少包含两个外键，分别指向两端的主键。

表的复杂查询操作

1. 数据导入与聚合统计

跨表插入：可将查询结果直接存入新表，要求源表列顺序与目标表一致。

INSERT INTO target_table SELECT * FROM source_table;

聚合函数包括 COUNT（计数）、SUM（求和）、AVG（平均）、MAX/MIN（最值）。注意：这些函数会自动忽略 NULL 值，且不支持嵌套调用（如 COUNT(SUM(x)) 是非法的）。

2. 分组与条件过滤

使用 GROUP BY 进行分组后，可使用 HAVING 筛选组。区分在于：WHERE 用于分组前过滤原始行（不可用聚合函数），HAVING 用于分组后过滤（可以使用聚合函数）。

3. 多表连接查询

多表查询的基础是笛卡尔积，实际开发中主要通过 JOIN 语法配合 ON 条件来消除无效组合。

(1) 内连接 (INNER JOIN)

只返回两张表中满足连接条件的交集数据。

SELECT s.name, sc.score 
FROM student s 
JOIN score sc ON s.id = sc.student_id 
WHERE s.name = '许仙';

(2) 外连接 (LEFT/RIGHT JOIN)

• 左外连接：以左表为主，即使右表无匹配项，左表记录也会保留（右表对应字段填 NULL）。
• 右外连接：以右表为主，逻辑同上。

(3) 自连接 (Self Join)

同一张表通过别名视为两张表进行连接，常用于比较同行或同级数据（如比较同一学生的不同科目成绩）。

SELECT s1.* FROM score s1, score s2 
WHERE s1.student_id = s2.student_id 
AND s1.course_id = 3 AND s2.course_id = 1; -- 查找计算机原理成绩高于Java的学生

4. 子查询与合并查询

子查询允许在一个 SELECT 语句内部嵌套另一个 SELECT。

• 单行子查询：返回单条记录，可使用 =。
• 多行子查询：返回多条记录，常配合 IN 操作符使用。

UNION 合并：
UNION 会将多个查询结果纵向拼接并去重；UNION ALL 则保留所有重复记录。相比 OR，UNION 还能跨表实现并集查询。

以上就是 MySQL 数据库约束和多表复杂查询的详细入门与进阶操作总结，更多关于 SQL 查询优化的资料请关注本站其它相关文章！