MySql-多表设计-一对多

一对多

在数据库设计中，一对多关系（或称为1:N关系）是一个常见的关系类型。这种关系表示一个表中的记录可以与另一个表中的多个记录相关联，但另一个表中的每个记录只能与第一个表中的一条记录相关联。

以下是如何在数据库设计中实现一对多关系的步骤：

假设我们有两个实体：学生（Student）和课程（Course）。每个学生可以选修多门课程，但每门课程只能被一个学生选修。

* `学生`表（Student）：


	+ 学生ID（主键）
	+ 姓名
	+ ... 其他字段
* `课程`表（Course）：


	+ 课程ID（主键）
	+ 课程名称
	+ 学生ID（外键，引用学生表的学生ID）
	+ ... 其他字段

3. 关系约束：

   • 在“多”的表（如课程表）中，外键字段（如学生ID）应该有一个约束，确保它引用的值在“一”的表（如学生表）的主键字段中存在。这确保了数据的完整性和准确性。

4. 查询和操作：

   • 当你想查询某个学生选修的所有课程时，你可以使用JOIN操作，基于学生ID将学生表和课程表连接起来。
   • 当你想添加一个新的课程记录时，你需要确保你引用的学生ID在学生表中存在。
   • 如果你想删除一个学生，你可能还想删除与该学生相关的所有课程记录（这取决于你的业务逻辑）。

5. 考虑性能：

   • 如果你的表非常大，并且经常进行连接查询，那么你可能需要考虑索引优化，特别是在外键字段和经常用于查询的字段上。

6. 考虑扩展性：

   • 随着时间的推移，业务逻辑可能会发生变化。确保你的数据库设计可以适应这些变化，并且易于维护。

实例

-- 员工
create table tb_emp (
    id int unsigned primary key auto_increment comment 'ID',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image varchar(300) comment '图像',
    job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
    entrydate date comment '入职时间',
    dept_id int unsigned comment '归属的部门ID',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '员工表';


-- 部门
create table tb_dept (
    id int unsigned primary key auto_increment comment 'ID',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '部门表';

插入数据

-- 插入测试数据
insert into tb_dept (id, name, create_time, update_time) values
(1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()),
(4,'就业部',now(),now()),(5,'人事部',now(),now());

INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time)
VALUES
    (1, 'username1', 'password1', 'Name1', 1, '1.jpg', 4, '2000-01-01',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (2, 'username2', 'password2', 'Name2', 0, '2.jpg', 2, '2001-02-02',3, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (3, 'username3', 'password3', 'Name3', 1, '3.jpg', 3, '2002-03-03',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (4, 'username4', 'password4', 'Name4', 0, '4.jpg', 1, '2003-04-04',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (5, 'username5', 'password5', 'Name5', 1, '5.jpg', 2, '2004-05-05',3, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (6, 'username6', 'password6', 'Name6', 0, '6.jpg', 3, '2005-06-06',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (7, 'username7', 'password7', 'Name7', 1, '7.jpg', 4, '2006-07-07',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (8, 'username8', 'password8', 'Name8', 0, '8.jpg', 1, '2007-08-08',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (9, 'username9', 'password9', 'Name9', 1, '9.jpg', 2, '2008-09-09',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (10, 'username10', 'password10', 'Name10', 0, '10.jpg', 3, '2009-10-10',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (11, 'username11', 'password11', 'Name11', 1, '11.jpg', 4, '2010-11-11',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (12, 'username12', 'password12', 'Name12', 0, '12.jpg', 1, '2011-12-12',4, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (13, 'username13', 'password13', 'Name13', 1, '13.jpg', null, '2012-01-13',null, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (14, 'username14', 'password14', 'Name14', 0, '14.jpg', 3, '2013-02-14',4, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (15, 'username15', 'password15', 'Name15', 1, '15.jpg', 4, '2014-03-15',4, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (16, 'username16', 'password16', 'Name16', 0, '16.jpg', 1, '2015-04-16',3,'2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (17, 'username17', 'password17', 'Name17', 1, '17.jpg', 2, '2016-05-17',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (18, 'username18', 'password18', 'Name18', 0, '18.jpg', 3, '2017-06-18',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (19, 'username19', 'password19', 'Name19', 1, '19.jpg', 4, '2018-07-19',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (20, 'username20', 'password20', 'Name20', 0, '20.jpg', 1, '2019-08-20',2, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (21, 'username21', 'password21', 'Name21', 1, '21.jpg', 2, '2020-09-21',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (22, 'username22', 'password22', 'Name22', 0, '22.jpg', 3, '2021-10-22',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (23, 'username23', 'password23', 'Name23', 1, '23.jpg', 4, '2022-11-23',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
    (24, 'username24', 'password24', 'Name24', 0, '24.jpg', 1, '2023-12-24',1, '2022-10-27 16:35:33', '2022-10-27 16:35:35');

员工表 - 部门表之间的关系