php 多对多关系示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2923809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Many-to-many relationships examples
提问by good_evening
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?
我还没有在这里和谷歌中找到任何 MYSQL 多对多关系示例。我正在寻找的是一个非常简单的例子，用 php+mysql 显示数据库的结果。有人可以写一个非常简单的例子吗？
回答by Hammerite
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
回答by Syntax Error
Here's a quick and dirty example of the SQL involved. I don't see any need to muddy up the concept with php. Just retrieve the set like you would any other.
这是所涉及的 SQL 的一个快速而肮脏的示例。我认为没有必要用 php 混淆这个概念。就像检索其他任何东西一样检索该集合。
In this example, there are many names, and many colors. People are allowed to have more than one favorite color, and many people can have the same favorite color. Hence many to many.
在这个例子中，有很多名字和很多颜色。人们可以拥有不止一种喜欢的颜色，而且很多人可以拥有同一种喜欢的颜色。因此多对多。
***** Tables **********
person
--------
id - int
name - varchar
favColor
-------------
id - int
color - varchar
person_color
------------
person_id - int (matches an id from person)
color_id - int (matches an id from favColor)
****** Sample Query ******
SELECT name, color
FROM person
LEFT JOIN person_color ON (person.id=person_id)
LEFT JOIN favColor ON (favColor.id=color_id)
****** Results From Sample Query *******
Name - Color
---------------
John - Blue
John - Red
Mary - Yellow
Timmy - Yellow
Suzie - Green
Suzie - Blue
etc...
Does that help?
这有帮助吗？
回答by zloctb
mysql> SELECT * FROm products;
+----+-----------+------------+
| id | name | company_id |
+----+-----------+------------+
| 1 | grechka | 1 |
| 2 | rus | 1 |
| 3 | makaronu | 2 |
| 4 | yachna | 3 |
| 5 | svuniacha | 3 |
| 6 | manka | 4 |
+----+-----------+------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROm company;
+----+----------+
| id | name |
+----+----------+
| 1 | LVIV |
| 2 | KIEV |
| 3 | KHarkiv |
| 4 | MADRID |
| 5 | MILAN |
| 6 | KOR |
+----+----------+
6 rows in set (0.00 sec)
mysql> SELECT * FROm many_many;
+------------+---------+
| product_id | city_id |
+------------+---------+
| 1 | 1 |
| 1 | 3 |
| 2 | 3 |
| 1 | 2 |
| 1 | 4 |
| 2 | 4 |
| 2 | 1 |
| 3 | 1 |
+------------+---------+
8 rows in set (0.00 sec)
mysql> SELECT products.name,company.name FROM products JOIN many_many ON many_
ny.product_id =products.id JOIN company ON company.id= many_many.city_id;
+----------+---------+
| name | name |
+----------+---------+
| grechka | LVIV |
| grechka | KHarkiv |
| grechka | KIEV |
| grechka | MADRID |
| rus | KHarkiv |
| rus | MADRID |
| rus | LVIV |
| makaronu | LVIV |
+----------+---------+
8 rows in set (0.00 sec)
回答by RuchiS
SELECT a.a_id, b.b_id, b.b_desc,
CASE WHEN x.b_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM a
CROSS JOIN b
LEFT JOIN x ON (x.a_id = a.a_id AND x.b_id = b.b_id)
WHERE (a.a_id = 'whatever')
