translate from http://www.phpknowhow.com/mysql/many-to-many-relationships/
在这篇文章中,我们要看看它有一个特殊的存储机制,多对多关系对多的关系。
也就是说,当两个表有多对多的关系,可以构造一个中间表来存储的关系,
看一下下面的`employee`和`'education`表, 不想翻译了。。。下面英文也不难。。。
id | first_name | last_name | job_title | salary | notes |
---|---|---|---|---|---|
1 | Robin | Jackman | Software Engineer | 5500 | |
2 | Taylor | Edward | Software Architect | 7200 | |
3 | Vivian | Dickens | Database Administrator | 6000 | |
4 | Harry | Clifford | Database Administrator | 6800 | |
5 | Eliza | Clifford | Software Engineer | 4750 | |
6 | Nancy | Newman | Software Engineer | 5100 | |
7 | Melinda | Clifford | Project Manager | 8500 | |
8 | Harley | Gilbert | Software Architect | 8000 |
id | name |
---|---|
1 | BSc |
2 | MSc |
3 | PhD |
Think that Robin and Taylor hold BSc while Vivian holds both MSc and PhD. These relationships can be graphically represented like below.
You can see that for Vivian in `employee` table there are two related rows in `education` table and for BSc in `education` table there are two related rows in `employee` table forming many-to-many relationships (Each row in `employee` table can have more that one related row in `education` table and vise versa).
For storing many-to-many relationships, we need an intermediate table that mainly stores the primary keys (IDs) of each relationship. In this case, we can use a table (`employee_education`) like below.
employee_id | education_id |
---|---|
1 | 1 |
2 | 1 |
3 | 2 |
3 | 3 |
Once relationships are stored, you can fetch data like below. In this query we fetch employee names and their education levels using SELECT statements and left joins.
1.
SELECT
emp.first_name, emp.last_name, edu.
name
AS
edu_name
FROM
`employee`
AS
emp
LEFT
JOIN
`employee_education`
AS
ee
ON
emp.id = ee.employee_id
LEFT
JOIN
`education`
AS
edu
ON
ee.education_id = edu.id;
+------------+-----------+----------+ | first_name | last_name | edu_name | +------------+-----------+----------+ | Robin | Jackman | BSc | | Taylor | Edward | BSc | | Vivian | Dickens | MSc | | Vivian | Dickens | PhD | | Harry | Clifford | NULL | | Eliza | Clifford | NULL | | Nancy | Newman | NULL | | Melinda | Clifford | NULL | | Harley | Gilbert | NULL | +------------+-----------+----------+
You can omit the employees whose education levels are not set by having a WHERE clause.
1.
SELECT
emp.first_name, emp.last_name, edu.
name
AS
edu_name
FROM
`employee`
AS
emp
LEFT
JOIN
`employee_education`
AS
ee
ON
emp.id = ee.employee_id
LEFT
JOIN
`education`
AS
edu
ON
ee.education_id = edu.id
WHERE
edu.
name
IS
NOT
NULL
;
+------------+-----------+----------+ | first_name | last_name | edu_name | +------------+-----------+----------+ | Robin | Jackman | BSc | | Taylor | Edward | BSc | | Vivian | Dickens | MSc | | Vivian | Dickens | PhD | +------------+-----------+----------+