数据库建立表关系 一对多 多对多 一对一
表之间的关系
外键
一对多 多对多 一对一
一对多
确立表与表之间的关系 一定要换位思考(必须两方都考虑周全之后才能得出结论)
以员工表和部门表为例
先站在员工表看能否有多个员工对应一个部门
翻译过来:
一个部门能否有多个员工
可以!!!(暂时只能确定员工单向多对一部门)
再站在部门表看能否有多个部门对应一个员工
翻译过来
一个员工能否属于多个部门
不可以!!!
结论:员工表和部门表之间仅仅是单向的多对一
那么它们的表关系就是"一对多"
表关系中没有多对一一说,只有一对多
(无论是多对一还是一对多都叫"一对多")
将所有的信息都记录在一张表中的问题
-
表的结构不清晰
-
浪费硬盘空间
-
表的扩展性差(这个是无法忽略的缺点)
确立表之间的关系 首先一定要换位思考 (因为要将变之间的关系考虑周全之后才能得出结论)
普通的创建两个表 (这里是没有关联关系的)
create database company; # 先创建一个库
# 创建一个公司表
use company;
create table person(
id int,
name varchar(30),
入职时间 int
);
# 创建一个公司员工表
create table company_person_info(
id int,
部门 varchar(50),
工作 varchar(50)
);
这样子就能看出问题了
外键 foreign key (中间没有下划线)
什么是外键???????
就是让表之间有硬性层面的上的关系
外键 foreign key(中间没有下划线)
外键的约束
1 创建包的时候 必须先创建被关联的表
2 插入数据的时候 也必须先插入被关联表的数据
# 创建表 先创建库
create databases company;
# 使用库
use company;
# 创建表 dep表
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(128)
);
# 创建表 emp表
create table emp(
id int primary key auto_increment,
emp_name varchar(64),
emp_gender enum('male','female') default 'male',
dep_id int,
foreign key(dep_id) reference dep(id)
);
# 加入数据 先给dep表添加数据
insert into dep(dep_name,dep_desc) values('外交部','吃饭喝酒签合同'),
('教学部','授课'),
('技术部','技术部门(NB的不行)')
;
# emp表添加数据
insert into emp(emp_name,dep_id) vlaues('james',1),
('xixi',2),
('haha',2),
('hehe',3)
;
这就是简单的让两个表使用外键建立联系
但是这里是有一点问题的 就是在修改的时候不能同时修改
update dep set id=200 where id = 1;
update emp set dep_id = 300 where id = 1;
delete from dep where id = 3;
这里想要删除数据的话 首先第一步要删除员工表(emp)的数据 再删除部门(dep)的数据
delete from emp where id = 4; # 删除员工表中(emp)的数据 这一句会报错 因为表中没有 id=4的数据
delete from dep where id = 3; # 删除部门表中(dep)的数据
想要同时修改数据的话 就要使用到级联更新个级联删除了 (解释一下 就是同时更新两个表格数据和可以同时删除两个表格内关联的数据)
完整的写法
# 创建表 先创建库
create databases company;
# 使用库
use company;
# 创建dep表
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(128)
);
# 创建emp表
create table emp(
id int primary key auto_increment,
emp_name varchar(64),
emp_gender enum('male','female') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
# 插入数据给dep表
insert into dep(dep_name,dep_desc) values('外交部','吃饭喝酒签合同',)
('教学部','授课'),
('技术部','技术能力')
;
#插入数据给emp表
insert into emp(emp_name,dep_id) values('james',1),
('hehe',2),
('xixi',2),
('haha',3)
;
多对多
多对多
图书与作者表
一定要换位思考
先站在图书
多本书能否有一个作者
一个作者能否写多本书 可以!!!
再站在作者
多个作者能否写一本书
一本书能否有多个作者 可以!!!
如果双反都是可以的 那么就是多对多
强调 foreign key只是用来帮你建立表关系的 不是某个关系特有的方法
# 先创建普通表 第一张图书表
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,
oreign key(author_id) references author(id)
on update cascade
on delete cascade
);
一对一
一对一表关系
1.一对一的场景 当你的表特别庞大的时候 你可以考虑拆分表
2.联想老男孩的客户和学生
一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率较高的一方
create table authordetail(
id int primary key auto_increment,
phone int,
addr char(255)
);
create table author(
id int primary key auto_increment,
name char(4),
age int,
authordetail_id int unique,
foreign key(authordetail_id) references authordetail(id)
on update cascade
on delete cascade
);
了解知识点
1.修改表的完整语句
-
修改表名
- ALTER TABLE 表名
RENAME 新表名; - 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 直接移到最前面
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 寻找插哪个字段的后面
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
- ALTER TABLE 表名
# 查询语句执行的结果也是一张表，可以看成虚拟表
# 复制表结构＋记录 （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;
