一、表的概念与分类
主要通过外键关键字:pirmary key 来建立连系
1、表与表之间的关系可以分为三种:一对多,多对多,一对一的关系
建立表关系的原因?:
2、 针对数据量大的情况下讨论
1:表的结构不清晰(一张表中涵盖多种数据)
2:浪费硬盘空间(有重复的数据)
3:表的扩展性极差(不便于增加,修改,删除等操作)
二、表与表的关系:一对多
例如:一个公司的员工和部门之间的连系,把员工和部门分别抽离出来分析
(1)先站在员工表看能否有多个员工对应一个部门>>>一个部门能否有多个员工(可以)
(2)再站在部门表看能否有多个部门对应一个员工>>>一个员工能否属于多个部门(不可以)
结论:员工表和部门表之间的关系是“一对多”的关系(一对多或者多对一)
根据id索引来寻找匹配的内容先通过列表的形式,把一张完整的表切分出来,分析它们之间的关系多对一还是多对多就可以看出情况,换位思考!
foreign key:外键约束
1、创建表的时候,必须先创建被关联的表
2、插入数据的时候,也必须先插入被关联表的数据(先后顺序不能颠倒,不然会报错!)
代码如下:
建表:
# 先建被关联的表
create table dep(
id int primary key auto_increment,
dep_name varcher(32),
dep_desc varcher(128)
);
# 后建数据多的表
create table emp(
id int primary key auto_increment,
emp_naem varchar(64),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreidn key(dep_id) references dep(id)
);
插入数据:
insert into dep(dep_name,dep_desc) values('外交部','搞外交'),
('教学部','教书育人'),
('技术部','技术能力有限部门')
;
insert into emp(emp_name,dep_id) values('jack',1),
('reso',2),
('gelen','3'),
('james',2);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210302190842410.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzUyNTEyOTM4,size_16,color_FFFFFF,t_70
2、修改表数据
通过主键和对应的关系可以对表中的记录增加,修改,删除等操作,也是有先后顺序的
update dep set id=200 where id=1; # 修改id
update emp set dep_id =300 where id=1;
delect from dep where id=3; # 删除id3的数据记录
外键虽然能够强制的建立表的关系,但是也会给表的数据之间增加了相关的约束
1.删除数据的时候 先删员工表的数据 再删部门表的数据
delete from emp where id = 4;
delete from dep where id = 3;
2.级联更新级联删除
3、级联更新、级联删除
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(128)
);
caeate table emp(
id int primary key auto_increment,
emp_name carchar(64),
emp-gender enum('male','female','othres') default 'male',
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('jack',2),('rose',1),('kevin',3),('galen',1);
# 修改 删除数据
update dep set id=200 where id=3;
delect from dep where id=2;
三、多对多的关系
例如:书籍和作者的关系
换位思考:站在书籍的角度和作者的角度思考,多本书可以被一个作者写,多个作者也能写一本书,
条件成立,属于多对多,外键已经不能够表达这种关系了,必须通过建立第三方表来记录两者之间的关系
先建两种普通的表 不需要设置外键
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varcher(32),
age int
);
# 手动创建第三方表
create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on uptade cascade
on delete cascade , # 直接建立级联
author_id int,
foregn key (author_id) refrrences author(id)
on update cascade
om delete cascade #
);
插入数据:
insert into book(title,price) values('百年孤独',60),(‘丰乳肥臀’,90),(‘疯狂说英语’,129);
insert into auther (nane,age) values('西亚·马尔克斯',98),(‘莫言’,87),(‘李阳’,53);
insert into book2auther(book_id,age) values(1,2),(2,3),(2,1),(1,3);
**
四、一对一关系
**
1:一对一的场景,当数据量过大的时候,可以考虑把表给拆分开
2:如老男孩的客户和学生关系(没交学费前和交学费后)
3:姓名、电话,地址
一对一的关系:外键字段都可以建在任意一方,一般建议把外键字段建立在查找频繁的那一方
创建表:
# 左表的一条记录唯一对应右表的一条记录,反之也一样
create table customer(
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
create table student(
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
# 三种外键关系都是用foreign key,区别在于如何使用以及其他条件限制即可做出三种关系
了解知识点:
修改表的完整语句
1.修改表的完整语句
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
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 旧字段名 新字段名 新数据类型 [完整性约束条件…];
# 查询语句执行的结果也是一张表,可以看成虚拟表
# 复制表结构+记录 (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;