数据库关联关系详解

更新: 10/22/2025字数: 0 字 时长: 0 分钟

1. 一对一关系 (One-to-One)

概念

一个表中的每条记录只对应另一个表中的一条记录。

示例

-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 用户详情表（扩展信息）
CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT UNIQUE,  -- 唯一外键
    full_name VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

特点

• 一个用户只有一个用户详情
• 一个用户详情只属于一个用户
• 通常用于数据拆分（基本信息 vs 扩展信息）

实际应用场景

• 用户表 + 用户详情表
• 产品表 + 产品库存表
• 订单表 + 订单发票表

2. 一对多关系 (One-to-Many)

概念

一个表中的一条记录可以对应另一个表中的多条记录。

示例

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,  -- 外键，关联部门表
    salary DECIMAL(10,2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

数据示例

部门表：
dept_id | dept_name
--------|-----------
1       | 技术部
2       | 销售部

员工表：
emp_id | emp_name | dept_id | salary
-------|----------|---------|--------
101    | 张三     | 1       | 8000
102    | 李四     | 1       | 7500
103    | 王五     | 2       | 6000

特点

• 一个部门可以有多个员工
• 一个员工只能属于一个部门
• 外键放在"多"的一方（员工表）

实际应用场景

• 客户 ↔ 订单（一个客户有多个订单）
• 分类 ↔ 商品（一个分类有多个商品）
• 用户 ↔ 文章（一个用户发布多篇文章）

3. 多对多关系 (Many-to-Many)

概念

一个表中的多条记录可以对应另一个表中的多条记录。有中间表作为连接。

示例

-- 学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    credit INT
);

-- 中间表（连接表）
CREATE TABLE student_courses (
    id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade DECIMAL(4,2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

数据示例

学生表：
student_id | student_name
-----------|-------------
1          | 张三
2          | 李四

课程表：
course_id | course_name | credit
----------|-------------|-------
101       | 数学        | 3
102       | 英语        | 2

中间表：
id | student_id | course_id | enrollment_date | grade
---|------------|-----------|-----------------|------
1  | 1          | 101       | 2024-01-15      | 85.5
2  | 1          | 102       | 2024-01-15      | 90.0
3  | 2          | 101       | 2024-01-16      | 78.0

特点

• 一个学生可以选择多门课程
• 一门课程可以被多个学生选择
• 必须通过中间表来实现关联
• 中间表通常包含两个外键和可能的额外信息（如成绩、日期等）

实际应用场景

• 用户 ↔ 角色（一个用户有多个角色，一个角色属于多个用户）
• 文章 ↔ 标签（一篇文章有多个标签，一个标签对应多篇文章）
• 产品 ↔ 订单（一个订单包含多个产品，一个产品出现在多个订单中）

4. 对比总结

关系类型	示例	外键位置	中间表	实际场景
一对一	用户 ↔ 用户详情	任意一方	不需要	数据拆分
一对多	部门 ↔ 员工	"多"的一方	不需要	层级关系
多对多	学生 ↔ 课程	中间表	需要	复杂关联

5. 查询示例

一对多查询

-- 查询每个部门及其员工
SELECT
    d.dept_name,
    e.emp_name,
    e.salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
ORDER BY d.dept_name, e.emp_name;

多对多查询

-- 查询学生选课情况
SELECT
    s.student_name,
    c.course_name,
    sc.grade,
    sc.enrollment_date
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
ORDER BY s.student_name, c.course_name;

6. 识别技巧

1. 一对一：两个表的主键相互关联
2. 一对多：A 表的主键出现在 B 表中作为外键
3. 多对多：存在中间表，包含两个外键分别关联两个主表