表与表之间建关系
外键
一对多
多对多
一对一
所有的信息都记录在一张表中所带来的问题
1.表的结构不清晰
2.浪费硬盘空间
3.表的扩展性极差(无法忽略的缺点)
PS:类似于我们将所有的代码都写在用一个py文件内
确立表与表之间的关系,一定要换位思考(必须两方都考虑周全之后才能得出结论)
以员工表和部门表为例:
先站在员工表看能否有多个员工对应一个部门
分析:
一个部门能否有多个员工
可以!!!(暂时只能确定员工单向多对一部门)
再站在部门表看能否有多个部门对应一个员工
分析:
一个员工能否属于多个部门
不可以!!!
结论:
- 员工表和部门表之间仅仅是单向的多对一
- 那么它们的表关系就是"一对多"
- 表关系中没有多对一一说,只有一对多(无论是多对一还是一对多都叫"一对多")
关于让两种表有代码层面上真正的关联,必须使用外键
什么是外键?
让表与表有硬性层面上的关系
foreign key
- 外键约束
- 1.在创建表的时候 必须先创建被关联表
- 2.插入数据的时候 也必须先插入被关联表的数据
# 建表
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(128)
);
create table emp(
id int primary key auto_increment,
emp_name varchar(64),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
# 插入数据
insert into dep(dep_name,dep_desc) values('外交部','搞外交'),
('教学部','教书育人'),
('技术部','技术能力有限部门')
;
insert into emp(emp_name,dep_id) values('jason',1),
('egon',2),
('tank',2),
('kevin',3);
修改表数据
update dep set id=200 where id = 1;
update emp set dep_id = 300 where id = 1;
delete from dep where id = 3;
注意:外键虽然能够帮你强制建立表关系 但是也会给表之间增加数据相关的约束
1.删除数据的时候 先删员工表的数据 再删部门表的数据
delete from emp where id = 4;
delete from dep where id = 3;
2.级联更新级联删除
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(128)
);
create table emp(
id int primary key auto_increment,
emp_name varchar(64),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
insert into dep(dep_name,dep_desc) values('外交部','搞外交'),
('教学部','教书育人'),
('技术部','技术能力有限部门')
;
insert into emp(emp_name,dep_id) values('jason',1),
('egon',2),
('tank',2),
('kevin',3);
update dep set id=200 where id = 3;
delete from dep where id = 2;
多对多
图书与作者表
一定要换位思考
先站在图书分析:
多本书能否有一个作者
一个作者能否写多本书 可以!!!
再站在作者分析:
多个作者能否和写一本书
一本书能否有多个作者 可以!!!
如果双方都是可以,那么就是多对多
强调:foreign key只是用来帮你建表关系的 不是某个关系特有的方法
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
# 先建两种普通的表 不需要设置外键
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
insert into book(title,price) values('三体',199),('聊斋',299),('jason教你删别人的库,让别人跑去吧',1);
insert into author(name,age) values('jason',18),('tank',38);
insert into book2author(book_id,author_id) values(4,3); # 报错
insert into book2author(book_id,author_id) values(1,1),(1,2),(2,1),(3,1),(3,2);
一对一表关系
- 1.一对一的场景,当你的表特别庞大的时候,你可以考虑拆分表
- 2.联想老男孩的客户和学生
- 当你没有交学费之前,你是老男孩的客户
- 当你叫了学费之后,你就变成老男哈的学生
- 并不是所有的客户都能变成学生
通常将关系字段称之为外键字段
- 一对多的外键字段建在多的一方
- 多对多:建在第三张表了
- 一对一:外键字段建在任意一方都可以,但是推荐你建在查询频率较高的一方
- 用户user:(id name age)
- 用户信息userdetail:(hobby addr IDCard)
create table authordetail1(
id int primary key auto_increment,
phone int,
addr char(255)
);
create table author1(
id int primary key auto_increment,
name char(4),
age int,
authordetail_id int unique,
foreign key(authordetail_id) references authordetail1(id)
on update cascade
on delete cascade
);
判断表关系的最简单的语法
图书与出版社
一本书可不可以有多个出版社 不可以!!!
一个出版社可不可以出版多本书 可以!!!
一对多的关系
图书与作者表
一本书可不可以有多个作者 可以!!!
一个作者可不可以写多本书 可以!!!
多对多的关系
作者与作者详情
一个作者可不可以有多个详情 不可以!!!
一个作者详情可不可以有多个作者 不可以!!!
要么两者是一对一
要么两者之间没任何关系
了解知识点
1.修改表的完整语句
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
# 查询语句执行的结果也是一张表,可以看成虚拟表
# 复制表结构+记录 (key不会复制: 主键、外键和索引)
create table new_service select * from service;
# 只复制表结构
select * from service where 1=2; # 条件为假,查不到任何记录
create table new1_service select * from service where 1=2;
create table t4 like employees;