mysql一对一多对多例子

原创

mob64ca12e676c8 2024-09-11 06:41:24 ©著作权

文章标签 一对一多对多 ide 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12e676c8的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL一对一与多对多关系详解

在关系型数据库中，数据表之间的关系是通过“外键”来建立的。不同类型的关系有“一对一”、“一对多”和“多对多”。本文将详细讲解一对一和多对多关系，并用代码示例进行说明。

一对一关系

一对一关系是指一个表中的每一条记录对应另一个表中的唯一一条记录。比如，一个用户只能有一张身份证。我们以用户和身份证作为例子来说明一对一关系。

数据库表设计

我们可以设计两个表：users和identities，如下所示：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE identities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE,
    identity_number VARCHAR(18) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

在上面的SQL代码中，identities表中的user_id列是一个外键，且是唯一的，这确保了每个用户只能对应一张身份证。

示例数据插入

接下来，我们可以插入一些示例数据：

INSERT INTO users (name) VALUES ('Alice'), ('Bob');

INSERT INTO identities (user_id, identity_number) VALUES 
(1, '123456789012345678'),
(2, '876543210987654321');

查询

为了查询用户及其身份证信息，可以使用下面的SQL语句进行连接查询：

SELECT u.name, i.identity_number 
FROM users u
JOIN identities i ON u.id = i.user_id;

多对多关系

多对多关系是指一个表中的记录可以与另一个表中的多条记录相关联，反之亦然。以“学生”和“课程”为例，一名学生可以选修多门课程，而一门课程也可以被多名学生选修。

数据库表设计

我们需要三个表：students、courses和student_course（关联表）。

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

示例数据插入

现在我们可以插入一些示例数据：

INSERT INTO students (name) VALUES ('Alice'), ('Bob');

INSERT INTO courses (title) VALUES ('Math'), ('Science');

INSERT INTO student_course (student_id, course_id) VALUES 
(1, 1), (1, 2), (2, 2);

查询

查询哪些学生选修了哪些课程，可以使用下面的SQL语句：

SELECT s.name AS student_name, c.title AS course_title
FROM students s
JOIN student_course sc ON s.id = sc.student_id
JOIN courses c ON c.id = sc.course_id;

流程图

下面是处理这些关系的一个流程图，用Mermaid语法表示。

flowchart TD
    A[开始] --> B{选择关系类型}
    B -->|一对一| C[创建users和identities表]
    B -->|多对多| D[创建students和courses表及student_course表]
    C --> E[插入用户及身份证信息]
    D --> F[插入学生和课程信息]
    E --> G[查询用户与身份证]
    F --> H[查询学生与课程]
    G --> I[结束]
    H --> I

旅行图

通过旅行图，我们可以了解在数据库中如何往返于不同表的信息。以下是一个例子：

journey
    title 学生与课程的旅程
    section 学生 Alice
      选择课程: 5: Alice, Bob
      修课程: 4: Math, Science
    section 学生 Bob
      选择课程: 5: Bob
      修课程: 4: Science