多对多关系示例 [英] Many-to-many relationships examples
本文介绍了多对多关系示例的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在这里和谷歌中都没有找到任何 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
查找所有注册课程的学生:
Find all students registered for a course:
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屋!
查看全文