数据库之表与表之间建关系

一、一对多关系

定义一张部门员工表

我们就会发现把所有数据存放于一张表的弊端：

1.组织结构不清晰

2.浪费硬盘空间

3.扩展性极差

这样的弊端是不是看着很眼熟，没错！这就类似于我们代码全部写在一个py文件中，那么当我们发现一个py文件中的代码冗余度很高会怎么做呢？当然就是要进行解耦合！

那么我再来分析这张表数据之间的关系：多个用户对应一个部门，一个部门就对应了多个用户，那么他们之间的关系就应该是一对多的关系，我们可以将上面的表拆开成两张表，一张是记录用户信息，另一张记录部门信息，再用某种方法使者两张表关联起来，这个方法就是：使用Foreign Key

确立表与表之间的关系一定要换位思考(必须两方面都考虑周全之后才能得出结论)

Foreign Key：外键约束

1.在创建表的时候，必须先创建被关联表

2.插入数据的时候，也必须先插入被关联表的数据

创建表：

#在创建表的时候，一定要先建被关联的表，才能创建关联表
create table dep(
id int primary key auto_increment,
dep_name varchar(64),
dep_desc varchar(64)
);

create table emp(
id int primary key auto_increment,
name varchar(16),
gender enum('male','female','others')not null default 'male',
age int,
emp_id int,
foreign key(emp_id) references dep(id) 
);

插入记录：

#插入记录时，必须先插被关联的表dep，才能插关联表emp
insert into dep(dep_name,dep_desc) values
('文娱部','文艺熏陶'),
('体育部','强身健体'),
('小卖部','好吃好喝');

insert into emp(name,gender,age,emp_id) values
('jason','female',18,1),
('egon','male',90,2),
('tank','male',38,2),
('kevien','female',20,3),
('jerry','male',40,3);

这样我们就把表都创建好了，并且表与表之间也建立了联系，但是问题也接踵而来，当我想修改emp里的dep_id或dep里面的id(修改成两张表都没有id)或者删除dep表里的记录时都会报错，如下图：

解决方式有两种：

方式1：先删除部门对应的所有的员工，在删除这个部门

★方式2：先把之前创的表删除，先删除员工表，再删除部门表，最后按照下面的方式重新创建表关系

更新与删除都需要考虑到关系与被关联的关系，也就是做到同步更新，同步删除

create table dep(
      id int primary key auto_increment,
      dep_name varchar(64),
      dep_desc varchar(64)
      );
  create table emp(
      id int primary key auto_increment,
      name varchar(16),
      gender enum('male','female','others')not null default 'male',
     age int,
     emp_id int,
     foreign key(emp_id) references dep(id)
     on update cascade
     on delete cascade
     );

插入记录：

insert into dep(dep_name,dep_desc) values
('文娱部','文艺熏陶'),
('体育部','强身健体'),
('小卖部','好吃好喝');
 
insert into emp(name,gender,age,emp_id) values
('jason','female',18,1),
('egon','male',90,2),
('tank','male',38,2),
('kevien','female',20,3),
('jerry','male',40,3);

删除部门后，对应的部门里面的员工表数据同步对应删除

更新部门后，对应员工表中的标识部门的字段同步更新

二、多对多

例：图书表与作者表之间的关系

我们仍然站在两张表的角度来分析：

1.站在图书表：一本书可不可以有多个作者，可以的！那么就是书籍多对一了作者

2.站在作者表：一个作者可不可以写多本书，也可以！那么就是作者多对一了书籍

双方都能一条数据对应对方多条记录，这种关系就是多对多！

那么我们应该如何创建表呢？图书表需要有一个外键关联作者，作者也需要有一个外键来关联书籍，然后问题来了，那我到底先创建谁呢？怎么解决这个问题呢？

解决方案：创建第三张表，该表中应该有一个foreign key字段关联图书表中的id，还应该有一个foreign key字段来关联作者表中的id，这样这两张表就通过一个中间者，建立起了联系。

 

create table author(
id int primary key auto_increment,
name char(16)
);

create table book(
id int primary key auto_increment,
b_name  varchar(32),
price int
);

插入记录：

insert into author(name) values
('jason'),
('tank'),
('egon');

insert into book(b_name,price) values
('葵花宝典',66),
('九阴真经',99),
('python大法',88),
('jason写真集',10);

★关键的来了：

创建第三张表格：

create table booktoauthor(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);

插入记录：

insert into booktoauthor(book_id,author_id) values
(1,1),
(1,3),
(2,1),
(2,2),
(3,1),
(3,2),
(3,3),
(4,1);

至此，多对多的表关系已经建立成功了！

三、一对一

依然是举个栗子:

会员表与顾客表（客户与顾客之间，只要消费了就是会员，反之没有消费的就是顾客）

站在双方的角度来看，会员表的一条记录对应了顾客表的另一条记录，反之也一样，那么这种关系就是一对一的关系

创建表：

create table vip(
id int primary key auto_increment,
birthday data,
qq int not null,
addr varchar(32),
);

create table customer(
id int primary key auto_increment,
name varchar(20) not null,
phone int not null,
vip_id int unique, #该字段一定是要唯一的
foreign key(vip_id) references vip(id) #外键的字段一定要保证unique
on update cascade
on delete cascade
);

插入记录：

insert into vip(birthday,qq,addr) values
('1980-8-8',66666666,'上海'),
('1990-8-16',77777777,'香港'),
('2000-6-6',88888888,'台湾');


insert into customer(name,phone,vip_id) values
('薛之谦',18888888888,1),
('陈赫',16666666666,null),
('邓紫棋',17777777777,2),
('林俊杰',15555555555,null),
('罗志祥',19999999999,3);

总结：三种外键关系都是用foreign key，区别在于如何使用以及其他条件限制即可做出三种关系

四、修改表

语法：

#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 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      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;