數(shù)據(jù)庫(kù)表中多對(duì)多關(guān)系怎么設(shè)計(jì)？
 

馬克-to-win：Teacher表:兩列id,name。
Studnet表: 三列：id,name，age
TeacherStudent表(關(guān)系表)：三列：id,tid,sid

create table TeacherStudent(id int not null,tid int not null,sid int not null);

2) teacher and student:（一個(gè)老師可能有多個(gè)學(xué)生，一個(gè)學(xué)生可能有多個(gè)老師）

qixy有兩個(gè)學(xué)生：liyaohua and fuwenlong, huanglaosh老師有一個(gè)學(xué)生--->fuwenlong

INSERT INTO TeacherStudent (id,tid,sid) VALUES(1,1,1);

INSERT INTO TeacherStudent (id,tid,sid) VALUES(2,1,2);

INSERT INTO TeacherStudent (id,tid,sid) VALUES(3,2,2);


qixy的學(xué)生顯示出來(lái)。

select * from Teacher t,Student s,TeacherStudent ts where t.name='qixy' and t.id=ts.tid and s.id=ts.sid;

+----+------+----+-----------+------+----+-----+-----+
| id | name | id | name      | age  | id | tid | sid |
+----+------+----+-----------+------+----+-----+-----+
|  1 | qixy |  1 | liyaohua  |   25 |  1 |   1 |   1 |
|  1 | qixy |  2 | fuwenlong |   26 |  2 |   1 |   2 |
+----+------+----+-----------+------+----+-----+-----+


huanglaosh的學(xué)生顯示出來(lái)。

select * from Teacher t,Student s,TeacherStudent ts where t.name='huanglaosh' and t.id=ts.tid and s.id=ts.sid;

+----+------------+----+-----------+------+----+-----+-----+
| id | name       | id | name      | age  | id | tid | sid |
+----+------------+----+-----------+------+----+-----+-----+
|  2 | huanglaosh |  2 | fuwenlong |   26 |  3 |   2 |   2 |
+----+------------+----+-----------+------+----+-----+-----+




fuwenlong的老師顯示出來(lái)。

select * from Teacher t,Student s,TeacherStudent ts where s.name='fuwenlong' and t.id=ts.tid and s.id=ts.sid;

+----+------------+----+-----------+------+----+-----+-----+

| id | name       | id | name      | age  | id | tid | sid |
+----+------------+----+-----------+------+----+-----+-----+
|  1 | qixy       |  2 | fuwenlong |   26 |  2 |   1 |   2 |
|  2 | huanglaosh |  2 | fuwenlong |   26 |  3 |   2 |   2 |
+----+------------+----+-----------+------+----+-----+-----+

參考一下以下游動(dòng)的同等寫(xiě)法：（未來(lái)springJdbc或mybatisxxxxx的某種技術(shù)中也許用的著，因?yàn)樗鼑?yán)格限制單表游動(dòng)）

select name from Student where id in (select sid from TeacherStudent  where tid in (select id from Teacher where name='qixy')) ;

結(jié)果：

+-----------+
| name      |
+-----------+
| liyaohua  |
| fuwenlong |
+-----------+

作業(yè)：

1）qinghua has zhangsan and lisi as its teachers. bawei has jiangfengli and taokun as its teachers.

2）yinjian taxi company has zhangsan and li as its employees. Abc taxi company has wangwu and zhaoliu as its employees.

3) 多對(duì)多：hospital system:

Doctor(d) and patient(p)

d1--->p1, d2---->p1,d1--->p3


4) student can check his score for some course.