当前位置：首页 > news >正文

多表设计-一对多一对多-外键

news 2025/7/18 18:53:50

一.多表设计概述：

二.一对多：

1.需求：

根据页面原型及需求文档，完成部门及员工模块的表结构设计

-->部门和员工就是一对多，因为一个部门下会有多个员工，但一个员工只归属一个部门

2.页面原型：

3.需求文档：

4.代码实现：

a.创建员工表：

-- 员工
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 '入职时间',create_time datetime         not null comment '创建时间',update_time datetime         not null comment '修改时间'
) comment '员工表';

b.创建部门表：

-- 部门
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 '部门表';

c.完善员工表：员工对应一个部门，因此员工表中还需要一个部门的属性(每个员工归属一个部门)

员工表中部门命名为dept_id，且部门表中用id记录部门，部门表中id是什么属性，员工表中dept_id就是什么属性

-- 员工
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 '员工表';

d.对比：

其中部门表是1的一方，员工表是多的一方（一个部门下有多个员工，一个员工只归属一个部门）

1的一方也是父表，多的一方也是子表（一个爹可以有多个儿子，但一个儿子只能有一个爹）

三.一对多关系实现：

在数据库表中多的一方添加字段(外键)，来关联一的一方的主键。

四.一对多-外键：

1.准备工作：插入数据

a.部门表：

-- auto-generated definition
create table tb_dept
(id          int unsigned auto_increment comment 'ID'primary key,name        varchar(10) not null comment '部门名称',create_time datetime    not null comment '创建时间',update_time datetime    not null comment '修改时间',constraint nameunique (name)
)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());

b.员工表：

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


-- 插入测试数据
insert into tb_emp -- insert into也可以写成INSERT INTO
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 1, '2007-02-01', 1, now(), now()),(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 1, '2008-08-18', 1, now(), now()),(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 2, '2012-11-01', 2, now(), now()),(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),(17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());