四、表关系与复杂查询

一、表关系设计与约束

1. 表关系类型与实现

关系类型	实现方式	示例场景
一对一	共享主键 或 外键唯一约束	用户 ↔ 用户详细信息
一对多	外键约束	部门 ↔ 员工
多对多	中间表 + 联合主键	学生 ↔ 课程

2. 核心约束类型

-- 完整表创建示例（含约束）
CREATE TABLE employees (emp_id INT PRIMARY KEY AUTO_INCREMENT,      -- 主键约束emp_name VARCHAR(50) NOT NULL,              -- 非空约束email VARCHAR(100) UNIQUE,                  -- 唯一约束dept_id INT,salary DECIMAL(10,2) CHECK (salary > 0),    -- 检查约束（MySQL 8.0+）FOREIGN KEY (dept_id) REFERENCES departments(dept_id) -- 外键约束
);

约束类型详解

约束类型	作用	注意事项
PRIMARY KEY	唯一标识记录，自动创建聚集索引	每表只能有一个主键
FOREIGN KEY	强制引用完整性	可能影响性能，高并发慎用
UNIQUE	保证字段值唯一	可空字段允许存在多个NULL值
CHECK	自定义验证规则	MySQL 8.0前需通过触发器实现
NOT NULL	禁止空值	与DEFAULT配合使用更安全

---

二、索引与查询优化

1. 索引的作用与类型

索引类型	特点	适用场景
B-Tree索引	默认索引，支持范围查询	大多数场景（如WHERE、ORDER BY）
唯一索引	强制字段值唯一	身份证号、邮箱等字段
联合索引	多列组合索引	常一起查询的字段组合

2. 索引创建与使用

-- 创建索引
CREATE INDEX idx_emp_name ON employees(emp_name);  -- 单列索引
CREATE UNIQUE INDEX idx_email ON employees(email);-- 唯一索引
CREATE INDEX idx_dept_salary ON employees(dept_id, salary); -- 联合索引-- 查看索引
SHOW INDEX FROM employees;-- 删除索引
DROP INDEX idx_emp_name ON employees;

3. 索引优化原则

1. 最左前缀原则：联合索引 (A,B,C) 可生效于：

   • WHERE A=1
   • WHERE A=1 AND B=2
   • WHERE A=1 AND B=2 AND C=3

2. 避免过度索引：索引会降低写操作速度

3. 覆盖索引：查询字段全部在索引中时，无需回表

   -- 使用覆盖索引
   SELECT emp_name FROM employees WHERE emp_name LIKE '张%';
   

---

三、高级关联查询

1. JOIN 执行原理

-- 示例：三表关联
-- 查询部门名称、员工姓名及其领导的项目名称
SELECT d.dept_name,        -- 选择部门名称e.emp_name,         -- 选择员工姓名p.project_name      -- 选择项目名称
FROM departments d      -- 从部门表开始查询
JOIN employees e        -- 连接员工表ON d.dept_id = e.dept_id  -- 连接条件：部门ID匹配
LEFT JOIN projects p    -- 左连接项目表ON e.emp_id = p.leader_id;  -- 连接条件：员工ID与项目领导ID匹配

JOIN 执行顺序

1. 执行 FROM departments d
2. 执行 JOIN employees e → 生成中间结果集
3. 执行 LEFT JOIN projects p → 扩展结果集
4. 应用 WHERE 过滤（如果有）
5. 执行 SELECT 字段投影

2. 自连接（Self Join）

-- 查询在同一部门中的不同员工对
SELECT e1.emp_name AS employee1,  -- 选择第一个员工的姓名e2.emp_name AS employee2   -- 选择第二个员工的姓名
FROM employees e1              -- 从员工表（别名 e1）开始查询
JOIN employees e2              -- 自连接员工表（别名 e2）ON e1.dept_id = e2.dept_id   -- 连接条件：部门ID相同AND e1.emp_id < e2.emp_id;   -- 确保员工ID不同且避免重复配对

3. 递归查询（WITH RECURSIVE）

-- 使用递归CTE生成从1到10的数字序列
WITH RECURSIVE numbers(n) AS (  -- 定义递归CTE，命名为numbers，包含列nSELECT 1                    -- 初始查询：从1开始UNION ALL                   -- 递归部分：将结果与后续查询合并SELECT n+1 FROM numbers     -- 递归查询：每次将n的值加1WHERE n < 10                -- 递归终止条件：n小于10时继续递归
)
SELECT * FROM numbers;          -- 最终查询：返回递归CTE的结果

---

四、综合案例：电商系统优化

1. 带约束的表设计

--产品表（products）
CREATE TABLE products (product_id INT PRIMARY KEY AUTO_INCREMENT,  -- 产品ID，主键，自增product_name VARCHAR(255) NOT NULL,    -- 产品名称，必填price DECIMAL(10,2) CHECK (price >= 0),    -- 产品价格，必须大于等于0stock INT DEFAULT 0 CHECK (stock >= 0)    -- 库存数量，默认值为0，必须大于等于0
);CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,    -- 订单ID，主键，自增user_id INT NOT NULL,    -- 用户ID，必填，外键关联用户表order_date DATETIME DEFAULT CURRENT_TIMESTAMP,    -- 订单日期，默认值为当前时间status ENUM('pending', 'shipped', 'completed') DEFAULT 'pending',    -- 订单状态，枚举类型，默认值为'pending'FOREIGN KEY (user_id) REFERENCES users(user_id)    -- 外键约束，关联用户表的user_id字段
);-- 中间表（含联合主键）
CREATE TABLE order_items (order_id INT,    -- 订单ID，联合主键之一，外键关联订单表product_id INT,    -- 产品ID，联合主键之一，外键关联产品表quantity INT CHECK (quantity > 0),    -- 购买数量，必须大于0PRIMARY KEY (order_id, product_id),    -- 联合主键，由order_id和product_id组成FOREIGN KEY (order_id) REFERENCES orders(order_id),    -- 外键约束，关联订单表的order_id字段FOREIGN KEY (product_id) REFERENCES products(product_id)    -- 外键约束，关联产品表的product_id字段
);

2. 索引优化实战

-- 高频查询：按用户和状态查订单
CREATE INDEX idx_user_status ON orders(user_id, status);-- 高频搜索：商品名称模糊查询
CREATE FULLTEXT INDEX idx_product_name ON products(product_name);-- 使用全文索引查询
SELECT * FROM products 
WHERE MATCH(product_name) AGAINST('手机 -配件' IN BOOLEAN MODE);

3. 复杂业务查询

-- 统计每个用户的2023年度消费总金额（包含未消费用户）
SELECT u.user_name,                             -- 用户姓名--当SUM的结果为NULL时，COALESCE会返回0COALESCE(SUM(oi.quantity * p.price), 0) AS total_spent  -- 计算消费总金额（未消费显示0）
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id          -- 关联用户和订单表（保留未下单用户）
LEFT JOIN order_items oi ON o.order_id = oi.order_id  -- 关联订单和订单明细表
LEFT JOIN products p ON oi.product_id = p.product_id  -- 关联订单明细和商品表
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'  -- 筛选2023年订单
GROUP BY u.user_id;                          -- 按用户ID分组汇总消费金额-- 查找库存紧张的热销商品（销量TOP 10且库存<100）
SELECT p.product_name,                         -- 商品名称SUM(oi.quantity) AS total_sold,         -- 统计商品总销量p.stock                                 -- 显示当前库存量
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id  -- 关联商品和订单明细表
GROUP BY p.product_id                       -- 按商品ID分组统计销量
HAVING total_sold > 100 AND stock < 100     -- 筛选销量>100且库存<100的商品
ORDER BY total_sold DESC                    -- 按销量降序排列
LIMIT 10;                                   -- 仅显示销量最高的前10条记录

---

五、常见错误与调试技巧

1. 外键约束冲突

错误示例：

-- 尝试删除有子记录的部门
DELETE FROM departments WHERE dept_id = 1;
-- 报错：Cannot delete or update a parent row: a foreign key constraint fails

解决方案：

-- 方法1：先删除子记录
DELETE FROM employees WHERE dept_id = 1;
DELETE FROM departments WHERE dept_id = 1;-- 方法2：使用级联删除（设计表时定义）
CREATE TABLE employees (...FOREIGN KEY (dept_id) REFERENCES departments(dept_id)ON DELETE CASCADE
);

2. 索引失效场景

场景	示例	优化方案
对索引列使用函数	WHERE YEAR(create_time)=2023	改为范围查询：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
使用前导通配符模糊查询	WHERE name LIKE '%张三%'	使用全文索引或倒序存储
隐式类型转换	WHERE id = '100'（id为INT）	保持类型一致：WHERE id = 100

---

六、实战练习建议

1. 设计医院管理系统

• 核心表：患者表、医生表、科室表、预约记录表、病历表
• 要求：
  1. 实现多对多关系（医生-科室）
  2. 添加合理的约束（如预约时间不能早于当前时间）
  3. 创建必要的索引
  4. 编写以下查询：
     • 查找某科室最繁忙的医生
     • 统计各科室的预约成功率
     • 检索患者的完整就诊历史

2. 性能优化挑战

1. 对已有慢查询进行 EXPLAIN 分析
2. 通过索引优化将查询时间从 2s 降至 200ms 以内
3. 使用 FORCE INDEX 强制使用特定索引对比效果