mysql多对多关系的构建

translate from http://www.phpknowhow.com/mysql/many-to-many-relationships/

在这篇文章中，我们要看看它有一个特殊的存储机制，多对多关系对多的关系。


也就是说，当两个表有多对多的关系，可以构造一个中间表来存储的关系，

看一下下面的`employee`和`'education`表， 不想翻译了。。。下面英文也不难。。。

idfirst_namelast_namejob_titlesalarynotes1RobinJackmanSoftware Engineer5500 2TaylorEdwardSoftware Architect7200 3VivianDickensDatabase Administrator6000 4HarryCliffordDatabase Administrator6800 5ElizaCliffordSoftware Engineer4750 6NancyNewmanSoftware Engineer5100 7MelindaCliffordProject Manager8500 8HarleyGilbertSoftware Architect8000 idname1BSc2MSc3PhD

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_ideducation_id11213233

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