mysql多对多关系的构建

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      |
+------------+-----------+----------+