数据库3表之间的关系
一.一对多
当我们把所有的信息存在同一张表中:
缺点:
结构不清晰,分不清主次(员工信息还是部门信息是主体)
扩展性差(假设要取消销售部需要一个个去删除)
其中有重复的内容,造成内存的浪费
解决方式:(将表进行拆分)
当我们拆分成2张表的时候,这时候表和表之间就没有关系了,现在我们需要找出其中的关系并给他们建立关系.这时候就用到外键了.
站在员工的角度:
一名员工只能属于一个部门
站在部门的角度:
一个部门可以有多名员工
一对多关系的建立:
ps:当理出关系后,建表时一定要先创建被关联表,要不会报错
#创建被关联表dep create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) );
#创建emp表 create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) );
ps:当我们建好表之后插入数据,这是也要先插入被关联表中的数据,要不也会报错
#先插入被关联的表数据 insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); #在插入关联表数据 insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3);
删除值:若此时删除dep表中的id=1的值,这时候会报错:
原因是此时这两张表还在关联,你要是删除了dep中的id=1的信息,那表emp中dep_id=1的值就是无家可归的孤儿了,所以不能删除.
修改:当我们要修改emp表中的dep_id值,若要将其改成dep中存在的id值是可以的,但是若要修改为不存在的值也会报错:
若想改变着这种情况有以下方法:
1.删表重建
2.先删除员工表中的数据,再删部门表
这些方式当数据量较大时都比较耗费时间.所以都不采用,联想将两表的数据进行关联,同步删除同步修改
create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade # 级联修改 (同步修改) on delete cascade # 级联删除 (同步删除) );
二.多对多
当表之间的关系时多对多的时候,若我们还像一对多一样去创建表的话,就会出现和之前创建表一样的情况,那是因为两表之间都存在外键,相互关联,先建谁都不行,这时候就需要一个第三方来和他们关联,将其数据进行整合关联.这时候这两张表看起来就没有任何关系了
#创建作者表 create table author( id int primary key auto_increment, name char(16) );
#创建书表 create table book( id int primary key auto_increment, bname char(16), price int );
#创建第三方表 create table author2book( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, foreign key(book_id) references book(id) on update cascade on delete cascade ); ps:一定要注意逗号的使用
三.一对一
左边的一张表一条数据唯一对应右边表的一条数据(或不存在对应),反过来也成立
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 ); ps:外键一般放置在查询较多的表中
总结:总结表和表之间的关系一定要站在双方的角度去看,不能只看一边,当有两个一对多的时候就是多对多,当有一个一对多就是一对多,当没有一对多关系时可能为两情况,要么没关系,要么就是一对一的关系,这需要我们自己去判断
修改表:
语法： 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;
