数据库表中一对多关系怎么设计？_数据库一对多-优快云博客

本文详细介绍了如何在数据库中实现教师与学生的多对多关系。通过创建teacher表和pupil表，使用外键tid连接两个表，实现了教师与学生的一对多关系。并提供了具体的SQL语句创建表及插入数据的示例。

Database Design（数据库设计）

马克-to-win：

(一对多：one-to-many)

1) teacher and student.

(teacher表:两列id（主键）,name。
pupil表: 三列：id（主键）,name,tid(外键))

举例： Teacher "qixy" has two students: liyaohua,fuwenlong. Teacher "huanglaosh" has two students: mashuai,jiaxiaohu.

create table pupil(id int not null,name char(10),tid int);

create table teacher(id int not null,name char(10));

INSERT INTO pupil (id,name,tid) VALUES(1,'liyaohua',1);
INSERT INTO pupil (id,name,tid) VALUES(2,'fuwenlong',1);
INSERT INTO pupil (id,name,tid) VALUES(3,'mashuai',2);
INSERT INTO pupil (id,name,tid) VALUES(4,'jiaxiaohu',2);

INSERT INTO teacher (id,name) VALUES(1,'qixy');
INSERT INTO teacher (id,name) VALUES(2,'huanglaosh');

select * from pupil,teacher where tid=teacher.id and teacher.name='qixy';

+----+-----------+------+----+------+
| id | name      | tid | id | name |
+----+-----------+------+----+------+
| 1 | liyaohua |    1 | 1 | qixy |
| 2 | fuwenlong |    1 | 1 | qixy |
+----+-----------+------+----+------+