php – 多对多关系示例

我没有在这里和谷歌中找到任何MYSQL多对多关系示例.我正在寻找的是看到一个非常简单的例子,php mysql显示数据库的结果.有人可以写一个非常简单的例子吗？ 示例场景：大学的学生和课程.给定的学生可能会在几门课上,而且自然地一门课程通常会有很多学生.

示例表,简单设计：

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

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

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

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!