SQL多表设计--一对多(外键)

 -- 完成部门和员工的
 -- 选择当前db03 这个数据库
 use db03;
 -- 查看当前选中的数据库
 select  database();
 -- 创建员工表
 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 '入职时间',
                         dep_id int unsigned comment  '员工归属的部门',
                         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 '部门表';
 
# 部门表为父表 员工表为子表 子表的dept_id和父表的id字段相对应  进行关联
 
-- 插入员工表的数据
 INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate,dep_id,create_time, update_time) VALUES
                                                                                                                (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 1,'2022-10-27 16:35:33', '2022-10-27 16:35:35'),
                                                                                                                (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
                                                                                                                (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01',2 ,'2022-10-27 16:35:33', '2022-10-27 16:35:39'),
                                                                                                                (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01',1 ,'2022-10-27 16:35:33', '2022-10-27 16:35:41'),
                                                                                                                (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05',2, '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
                                                                                                                (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1,'2022-10-27 16:35:33', '2022-10-27 16:35:45'),
                                                                                                                (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01',1, '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
                                                                                                                (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09',1, '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
                                                                                                                (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 2,'2022-10-27 16:35:33', '2022-10-27 16:35:51'),
                                                                                                                (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 2,'2022-10-27 16:35:33', '2022-10-27 16:35:53'),
                                                                                                                (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01',1, '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
                                                                                                                (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', 2,'2022-10-27 16:35:33', '2022-10-27 16:35:57'),
                                                                                                                (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01',2, '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
                                                                                                                (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01',1 ,'2022-10-27 16:35:33', '2022-10-27 16:36:01'),
                                                                                                                (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2,'2022-10-27 16:35:33', '2022-10-27 16:36:03'),
                                                                                                                (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 1,'2022-10-27 16:35:33', '2022-10-27 16:36:05'),
                                                                                                                (17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21',1, '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
                                                                                                                (18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
                                                                                                                (19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
                                                                                                                (20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
                                                                                                                (21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01',1,'2022-10-27 16:35:33', '2022-10-27 16:36:15'),
                                                                                                                (22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01',1,'2022-10-27 16:35:33', '2022-10-27 16:36:17'),
                                                                                                                (23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
                                                                                                                (24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
                                                                                                                (25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
                                                                                                                (26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
                                                                                                                (27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
                                                                                                                (28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
                                                                                                                (29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01',1, '2022-10-27 16:35:33', '2022-10-27 16:36:31');
 
 
 
-- 插入部门表的数据
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());
 
# 到此为止 这两张表任然是没有任何关联的 相互独立  这就会导致 我们即使删除了父表部门中的id为1的部门  而 子表中的  任然存在
-- 通过外键约束 来进行表之间的关联

设置外键

图形化操作添加:

sql写法添加:

 alter table tb_emp
     add constraint tb_emp_fk_dept_id foreign key (dept_id) references
     tb_dept (id) ;

# 使用foreign key 定义外键关联另一张表
# 缺点:
# 1.影响增删改的效率(需要检查外键的关系)
# 2.仅用于单节点数据库,不适用与分布式,集群场景
# 3.容易引发数据库的死锁问题,消耗性能

在大型项目开发中:推荐使用逻辑外键,使用代码逻辑进行关联