数据库表中多对多关系怎么设计？

马克-to-win：Teacher表:两列id,name。

Studnet表: 三列：id,name，age

TeacherStudent表(关系表)：三列：id,tid,sid

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

2) teacher and student:(一个老师可能有多个学生，一个学生可能有多个老师)

qixy有两个学生：liyaohua and fuwenlong, huanglaosh老师有一个学生--->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的学生显示出来。

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的学生显示出来。

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 |

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

您的浏览器不支持video标签

fuwenlong的老师显示出来。

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 |

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

参考一下以下游动的同等写法：(未来springJdbc或mybatisxxxxx的某种技术中也许用的着，因为它严格限制单表游动)

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

结果：

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

| name      |

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

| liyaohua  |

| fuwenlong |

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

作业：

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) 多对多：hospital system:

Doctor(d) and patient(p)

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

4) student can check his score

for some course.