4、表与表的关系

4.1foreign key外键

外键就是来建立表与表之间关系的

foreign key

两个表之间有关系，但是要通过一种方式将两个表的关系连接起来，此时就可以用到外键。

表与表之间的关系：

一对多关系、多对对关系、一对一关系、没有关系

注意：没有多对一的概念

4.2一对多关系

确定表与表之间的关系，需要换位思考，分别站在两张表的角度考虑

比如员工表与部门表

先在员工的角度思考：员工表里面一个员工是否可以对应部门表里面的多个部门，不可以

在站在部门表去考虑：部门表里面的一个部门能否对应员工表里面的多个员工，可以

结论：员工表与部门表只是单向的一对多成立，那么员工表与部门表具有一对多关系

foreign key
    1 一对多表关系   外键字段建在多的一方
    2 在创建表的时候 一定要先建被关联表 
    3 在录入数据的时候 也必须先录入被关联表
 # 以员工表与部门表为例 
 create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);
​
create table emp(
    id int primary key auto_increment,
    name char(16),
    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(name,dep_id) values('a',2),('b',1),('c',1),('d',3);
​
# 真正做到数据之间有关系，删除与更新两个表也可以同步
​

 

4.3多对多关系

以图书表和作者表为例

先站在图书表：一本书可以有多个作者 可以

在站在作者表：一个作者可以有多本书 可以

所以，图书表和作者表是多对多的关系

# 多对多关系，无法按照一对多关系的方式来创建
# 针对多对多关系
​
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,
    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  # 同步删除

 

4.4一对一关系

如果一个表的字段特别多 每次查询又不是所有的字段都能用得到 将表一分为二 用户表 id name age 用户详情表 id addr phone hobby email........

站在用户表 一个用户能否对应多个用户详情 不能 站在详情表 一个详情能否属于多个用户 不能！ 结论:单向的一对多都不成立 那么这个时候两者之间的表关系 就是一对一 或者没有关系(好判断)

客户表和学生表 在报名之前你们是客户端 报名之后是学生(期间有一些客户不会报名)

# 一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
​
create table authordetail(
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

总结：

表关系的建立需要用到foreign key 一对多：外键字段建在多的一方 多对多：自己开设第三张存储 一对一：建在任意一方都可以 但是推荐建在查询频率较高的表中

判断表之间关系的方式：换位思考

4.5修改表

注意：MySQL不区分大小写

# 1、修改表名
alter table 表名 rename 新表名;
​
# 2、增加字段
alter table  表名 add 字段名 字段类型(宽度) 约束条件;
​
# 增加在第一行
alter table 表名 add 字段名 字段类型(宽度)  约束条件 first;
    
# 增加在什么字段的后面
alter table 表名 add 字段名 字段类型(宽度)  约束条件 after 字段名;
​
# 3 删除字段
alter table 表名 drop 字段名;
​
# 4 修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
    
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

 

4.6复制表

sql语句查询的结果其实也是一张虚拟表

create table 表名 select * from 旧表;  # 不能复制主键 外键 ...
​
create table new_dep2 select * from dep where id>3;