数据库外键约束、条件过滤与分组查询详解
外键约束机制
外键是关系型数据库中用于建立表间关联的核心机制。它通过从表中的某一列（或列组合）引用主表的主键，实现数据间的逻辑连接。主表为被引用方，通常包含基础信息；从表则通过外键字段指向主表记录，形成依赖关系。
外键定义语法
创建外键需遵循先主后从的原则：
-- 创建主表
CREATE TABLE department (
dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
dept_name VARCHAR(50) NOT NULL COMMENT '部门名称'
);
-- 创建从表并添加外键约束
CREATE TABLE employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
emp_name VARCHAR(30) NOT NULL COMMENT '姓名',
department_id INT COMMENT '所属部门',
FOREIGN KEY (department_id) REFERENCES department(dept_id)
);
若未先创建主表而直接在从表中声明外键，数据库将抛出错误。
多对一关系设计
以员工与部门为例，一个部门可拥有多个员工，属于典型的一对多关系。此时应将外键置于"多"端（即员工表），避免在部门表中重复存储员工信息导致的数据冗余。
一对一关系建模
当两个实体间存在唯一对应关系时，如用户基本信息与其扩展资料，可采用一对一设计。外键可设置在任一方，但建议放在访问频率较高的主信息表中，提升查询效率。
CREATE TABLE user_profile (
profile_id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100),
interests JSON
);
CREATE TABLE user_basic (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
phone VARCHAR(15),
profile_ref INT UNIQUE,
FOREIGN KEY (profile_ref) REFERENCES user_profile(profile_id)
);
多对多关系处理
图书与作者之间为典型的多对多关系，必须引入中间关联表进行解耦：
CREATE TABLE book (
book_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
price DECIMAL(6,2)
);
CREATE TABLE author (
author_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
birth_year YEAR
);
-- 关联表
CREATE TABLE book_author_link (
link_id INT PRIMARY KEY AUTO_INCREMENT,
book_id INT,
author_id INT,
FOREIGN KEY (book_id) REFERENCES book(book_id),
FOREIGN KEY (author_id) REFERENCES author(author_id),
INDEX idx_book (book_id),
INDEX idx_author (author_id)
);
级联操作配置
通过 ON UPDATE CASCADE 和 ON DELETE CASCADE 实现自动同步更新或删除相关记录：
ALTER TABLE employee ADD CONSTRAINT fk_dept
FOREIGN KEY (department_id) REFERENCES department(dept_id)
ON UPDATE CASCADE ON DELETE CASCADE;
此设置确保修改部门ID或删除部门时，所有关联员工记录会自动更新或清除。
WHERE 条件过滤
WHERE 子句用于筛选满足特定条件的行，执行顺序位于 FROM 之后、SELECT 之前。
范围查询
使用 BETWEEN ... AND 实现闭区间匹配：
SELECT * FROM employee WHERE emp_id BETWEEN 3 AND 6;
注意不可写作 3 <= emp_id <= 6，该语法在SQL中无效。
枚举值匹配
IN 操作符比连续使用 OR 更简洁高效：
SELECT * FROM employee WHERE salary IN (11000.80, 13000.70, 15000.99);
模式匹配
LIKE 配合通配符实现模糊查询：
%匹配任意长度字符串_匹配单个字符
-- 查询姓名含字母i的员工
SELECT emp_name, salary FROM employee WHERE emp_name LIKE '%i%';
-- 查询姓名长度为5的员工
SELECT emp_name, salary FROM employee WHERE CHAR_LENGTH(emp_name) = 5;
空值判断
NULL 值必须使用 IS NULL 判断，不能用等号：
SELECT emp_name, position FROM employee WHERE description IS NULL;
GROUP BY 分组聚合
GROUP BY 将结果集按指定列分组，结合聚合函数进行统计分析。
聚合函数应用
常见聚合操作包括：
-- 各岗位最高薪资
SELECT position, MAX(salary) AS max_pay FROM employee GROUP BY position;
-- 各部门最低薪资
SELECT department_id, MIN(salary) FROM employee GROUP BY department_id;
-- 平均薪资计算
SELECT position, AVG(salary) FROM employee GROUP BY position;
-- 薪资总和统计
SELECT department_id, SUM(salary) FROM employee GROUP BY department_id;
-- 人员数量统计（忽略NULL）
SELECT position, COUNT(*) FROM employee GROUP BY position;
注意：COUNT(column) 不计入 NULL 值，而 COUNT(*) 统计所有行。
字符串聚合
GROUP_CONCAT 将组内多行数据合并为字符串：
-- 拼接各岗位员工姓名
SELECT position, GROUP_CONCAT(emp_name) FROM employee GROUP BY position;
-- 自定义格式拼接
SELECT position, GROUP_CONCAT(emp_name, ':', salary SEPARATOR '; ')
FROM employee GROUP BY position;
与 CONCAT 不同，GROUP_CONCAT 专用于分组场景下的跨行字符串整合。
SQL模式影响
当 sql_mode 包含 ONLY_FULL_GROUP_BY 时，SELECT 列表中非聚合字段必须出现在 GROUP BY 中，否则报错。可通过调整会话模式临时规避：
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
