MySQL 多表操作与复杂查询：深入理解多表关系和高级查询

大家好！今天我们要深入探讨 MySQL 中两个非常重要的主题——多表操作 和 复杂查询。

一. 多表操作

什么是多表操作？

在实际应用中，数据通常分布在多个表中，需要通过多表操作来获取完整信息。比如，一个学生表和一个课程表之间的关系，我们需要通过多表操作来查询某个学生的选课情况。

核心内容：

• 多表关系
• 外键约束
• 联合查询

1. 多表关系

一对一关系

一个表中的一条记录对应另一个表中的一条记录。例如，用户表和个人信息表之间可以是一对一的关系。

-- 用户表
CREATE TABLE users(
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE
);

-- 个人信息表
CREATE TABLE user_profiles(
  user_id INT PRIMARY KEY,
  address VARCHAR(255),
  phone VARCHAR(20),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

一对多关系

一个表中的一条记录对应另一个表中的多条记录。例如，订单表和用户表之间可以是一对多的关系。

-- 用户表
CREATE TABLE users(
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE
);

-- 订单表
CREATE TABLE orders(
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_number VARCHAR(50) NOT NULL,
  user_id INT,
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

多对多关系

一个表中的多条记录对应另一个表中的多条记录。例如，学生表和课程表之间可以是多对多的关系。

-- 学生表
CREATE TABLE students(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

-- 课程表
CREATE TABLE courses(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

-- 学生选课表（中间表）
CREATE TABLE student_courses(
  student_id INT,
  course_id INT,
  score DECIMAL(5,2),
  PRIMARY KEY(student_id, course_id),
  FOREIGN KEY(student_id) REFERENCES students(id),
  FOREIGN KEY(course_id) REFERENCES courses(id)
);

注意：

• 多对多关系需要通过中间表来实现。
• 中间表包含两个外键，分别引用两个表的主键。

2. 外键约束

创建外键约束

外键约束用于建立表与表之间的关联关系，确保数据的完整性和一致性。

-- 创建订单表，包含订单ID、订单号、客户ID、订单日期字段
CREATE TABLE orders(
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_number VARCHAR(50) NOT NULL,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

验证外键约束的作用

-- 插入有效员工数据
INSERT INTO employees VALUES(1, '张三', 1), (2, '李四', 2);

-- 插入无效员工数据（dept_id不存在）
INSERT INTO employees VALUES(3, '王五', 3); -- 会报错

-- 删除被引用的部门
DELETE FROM departments WHERE id = 1; -- 会报错

注意：

• 禁止插入无效的外键值。
• 禁止删除被引用的父表记录。
• 可以配置级联操作（如 ON DELETE CASCADE）。

3. 联合查询

交叉连接（CROSS JOIN）

返回两个表的笛卡尔积，即两个表中所有行的组合。

-- 交叉连接
SELECT * FROM departments CROSS JOIN employees;

-- 或者
SELECT * FROM departments, employees;

内连接（INNER JOIN）

返回两个表中匹配条件的行。

-- 内连接
SELECT e.id, e.name, d.name AS dept_name, e.salary 
FROM employees e 
INNER JOIN departments d ON e.dept_id = d.id;

左外连接（LEFT JOIN）

返回左表中的所有行，以及右表中匹配条件的行。如果右表中没有匹配的行，则返回 NULL。

-- 左外连接
SELECT e.id, e.name, d.name AS dept_name, e.salary 
FROM employees e 
LEFT JOIN departments d ON e.dept_id = d.id;

右外连接（RIGHT JOIN）

返回右表中的所有行，以及左表中匹配条件的行。如果左表中没有匹配的行，则返回 NULL。

-- 右外连接
SELECT e.id, e.name, d.name AS dept_name, e.salary 
FROM employees e 
RIGHT JOIN departments d ON e.dept_id = d.id;

二. 复杂查询

什么是复杂查询？

复杂查询是指通过子查询、聚合函数、分组等手段进行的高级查询操作。它可以帮助我们从大量数据中提取所需的信息。

核心内容：

• 子查询
• 聚合函数
• 分组查询

1. 子查询

基本子查询

嵌套在其他 SQL 语句中的查询。它可以返回单个值、多个值或表。

-- 标量子查询：查询工资高于平均工资的员工
SELECT * FROM employees WHERE salary > (
  SELECT AVG(salary) FROM employees
);

-- 列子查询：查询技术部和市场部的员工
SELECT * FROM employees WHERE dept_id IN (
  SELECT id FROM departments WHERE name IN ('技术部', '市场部')
);

ALL 关键字

表示与子查询返回的所有值进行比较。

-- 查询工资高于所有市场部员工的员工
SELECT * FROM employees WHERE salary > ALL (
  SELECT salary FROM employees WHERE dept_id = (
    SELECT id FROM departments WHERE name = '市场部'
  )
);

ANY/SOME 关键字

表示与子查询返回的任意一个值进行比较。

-- 查询工资高于市场部任意一个员工的员工
SELECT * FROM employees WHERE salary > ANY (
  SELECT salary FROM employees WHERE dept_id = (
    SELECT id FROM departments WHERE name = '市场部'
  )
);

EXISTS 关键字

表示子查询是否返回结果集。

-- 查询有员工的部门
SELECT * FROM departments d WHERE EXISTS (
  SELECT * FROM employees e WHERE e.dept_id = d.id
);

2. 聚合函数

聚合函数用于统计、求和、平均值等操作。

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

-- 计算总成绩
SELECT SUM(score) AS 总成绩 FROM students;

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

-- 按班级分组，统计每个班级的学生数量
SELECT class_id, COUNT(*) AS 学生数量 FROM students GROUP BY class_id;

-- 按班级分组，计算每个班级的平均成绩
SELECT class_id, AVG(score) AS 平均成绩 FROM students GROUP BY class_id;

-- 分组后筛选，只显示学生数量大于2的班级
SELECT class_id, COUNT(*) AS 学生数量 
FROM students 
GROUP BY class_id 
HAVING COUNT(*) > 2;

3. 自关联查询

自关联查询是指表与自身进行连接查询，通常用于处理层次结构数据。

-- 创建员工表，包含上级ID
CREATE TABLE employees(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  manager_id INT
);

-- 插入数据
INSERT INTO employees VALUES (1, '张三', NULL), (2, '李四', 1), (3, '王五', 1), (4, '赵六', 2);

-- 自关联查询，查询员工及其上级
SELECT e.name AS 员工, m.name AS 上级
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.id;

三. 总结与最佳实践

总结

最佳实践

• 合理使用多表关系：根据业务需求选择合适的关系类型。
• 灵活运用联合查询：根据需求选择合适的连接方式。
• 优化复杂查询性能：避免不必要的复杂查询，适当使用索引。

一句话总结：

多表操作帮助我们构建复杂的数据库模型，而复杂查询则是我们的“数据探索工具”，帮助我们在数据库中高效地获取所需信息。

结尾

通过这篇博客，我们详细讲解了 MySQL 中多表操作和复杂查询的核心概念和使用方法。