我没有在这里和谷歌中找到任何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