多对多关系示例 [英] Many-to-many relationships examples

问题描述

我没有在这里和google中找到任何MYSQL多对多关系的例子。我正在寻找的是看到一个非常简单的例子，php + mysql显示数据库的结果。任何人都可以写一个非常简单的例子？

I haven't found any MYSQL many-to-many relationships examples here and in google. What I am looking is to see a very simple example with php+mysql showing database's results. Can anybody write a very simple example?

推荐答案

示例场景：学生和大学课程。

Example scenario: students and courses at a university. A given student might be on several courses, and naturally a course will usually have many students.

示例表格，简单的设计：

Example tables, simple design:

CREATE TABLE `Student` (
    `StudentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `FirstName` VARCHAR(25),
    `LastName` VARCHAR(25) NOT NULL,
    PRIMARY KEY (`StudentID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `Course` (
    `CourseID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Code` VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
    `Name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`CourseID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `CourseMembership` (
    `Student` INT UNSIGNED NOT NULL,
    `Course` SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (`Student`, `Course`),
    CONSTRAINT `Constr_CourseMembership_Student_fk`
        FOREIGN KEY `Student_fk` (`Student`) REFERENCES `Student` (`StudentID`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `Constr_CourseMembership_Course_fk`
        FOREIGN KEY `Course_fk` (`Course`) REFERENCES `Course` (`CourseID`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

查找所有注册的学生

SELECT
    `Student`.*
FROM
    `Student`
    JOIN `CourseMembership` ON `Student`.`StudentID` = `CourseMembership`.`Student`
WHERE
    `CourseMembership`.`Course` = 1234

查找给定学生所修读的所有课程：

Find all courses taken by a given student:

SELECT
    `Course`.*
FROM
    `Course`
    JOIN `CourseMembership` ON `Course`.`CourseID` = `CourseMembership`.`Course`
WHERE
    `CourseMembership`.`Student` = 5678

这篇关于多对多关系示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！