MySQL基础：多表查询案例

一、一对一关系

场景

用户表（users）与用户详情表（user_details），每个用户对应一条详情记录。

表结构与外键

-- 用户表（主表）
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

-- 用户详情表（从表）
CREATE TABLE user_details (
    user_id INT PRIMARY KEY, -- 主键 + 外键，确保一对一
    email VARCHAR(100),
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

查询示例

-- 查询用户及其详情（内连接）
SELECT u.username, ud.email, ud.phone
FROM users u
INNER JOIN user_details ud ON u.user_id = ud.user_id;

二、一对多关系

场景

部门表（departments）与员工表（employees），一个部门有多个员工。

表结构与外键

-- 部门表（主表）
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL
);

-- 员工表（从表）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT, -- 外键指向部门表
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL
);

查询示例

-- 查询所有部门及其员工（左连接，包括无员工的部门）
SELECT d.dept_name, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

三、多对多关系

场景

学生表（students）与课程表（courses），一个学生可选修多门课程，一门课程可被多个学生选修。

表结构与外键

-- 学生表（主表）
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL
);

-- 课程表（主表）
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL
);

-- 中间表（选课关系表）
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id), -- 联合主键
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

查询示例

-- 查询学生及其选修的课程（三表连接）
SELECT s.student_name, c.course_name
FROM students s
INNER JOIN student_courses sc ON s.student_id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.course_id;

四、外键创建的核心规则

1. 语法

   FOREIGN KEY (当前表列名) REFERENCES 目标表(目标表列名)
   [ON DELETE {CASCADE | SET NULL | NO ACTION}]
   [ON UPDATE {CASCADE | NO ACTION}]
   

2. 注意事项
   • 数据类型：外键列与目标列的数据类型必须一致。
   • 索引：外键列会自动创建索引（MySQL 隐式处理）。
   • 级联操作：
     • ON DELETE CASCADE：主表删除记录时，从表关联记录自动删除。
     • ON DELETE SET NULL：主表删除记录时，从表外键字段设为 NULL。

五、总结

• 查询逻辑：
  • 一对一/一对多用 INNER JOIN 或 LEFT JOIN。
  • 多对多通过中间表多次 INNER JOIN。
• 外键约束：确保数据一致性，避免脏数据。