随笔二十：SQL外键与表之间对应关系

郑统京

12、外键

file://D:\学习笔记\%E6%95%B0%E6%8D%AE%E5%BA%93.assets\1694091302752.png?lastModify=1694834371

file://D:\学习笔记\%E6%95%B0%E6%8D%AE%E5%BA%93.assets\1694091542241.png?lastModify=1694834371

# 外键由来
"""
定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
"""
#1、该表的组织结构不清晰（可忽视）
#2、浪费硬盘空间（可忽视）
#3、数据的拓展性极差（不可忽视）
​
# 如何优化？
"""
上述代码类似与我们写的面条版的程序，全部写在一个py文件里面了
"""
将员工拆分  拆成部门表和员工表1、外键含义
"""
外键就是用来帮助我们建立表与表之间关系的
foreign key
"""2、表关系
"""
表与表之间的关系最多只有四种
    一对多
        MySQL中没有多对一的概念，都是一对多
    一对一
    多对多
    无关系
​
"""1、一对多关系

file://D:\学习笔记\%E6%95%B0%E6%8D%AE%E5%BA%93.assets\1694092100026.png?lastModify=1694834371

"""
判断表与表之间的关系时候 前期在不熟练的情况下一定要换位思考，分别站在两张表的角度考虑
​
员工表与部门表为例子
    先站在员工表
        思考一个员工能否对应多个部门（一条员工数据能否对应多条部门数据）
            不能！！！！
            （不能直接的得出结论 一定要考虑两张都完全的情况下）
    在站在部门表
        思考一个部门能否对应多个员工（一个部门数据能否对应多条员工数据）
            能！！！
    得出结论
        员工表与部门表是单项的一对多
        所以表关系是一对多
"""
Foreign key
    1、 一对多的关系 外键字段建在多的一方
    2、 在创建表的时候 一定要创建被关联表
    3、 在录入数据的时候，先录入被关联的表（dep），在录入员工表
# SQL语句建立表关系
create table dep(
    id int primary key auto_increment,
    depname char(16),
    dep_desc char(16)
);
​
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) # 声明是外键字段，并且确定和哪张表有关系
)
insert into dep(dep_name,dep_desc) values('sb教学部'，'教书育人'),('外交部'，'多人外交'),('nb技术部','技术能力有限部门')
insert into emp(name,dep_id) values('jason',2),('egon'，1),('tank',3)
​
# 修改emp里面的dep_id字段 或者dep表里面的id字段
update dep set id=200 where id=2 =>不行已经被员工表约束了
# 删除也同样不行
​
需要修改的化
# 1、先删除员工表，再去部门不删除
        太繁琐了，需要操作两张表
# 2 真正做到数据之间的关系
        更新就同步更新
        删除就同步删除
"""
级联更新  
级联删除
"""
在创建表的时候需要添加参数
create table dep(
    id int primary key auto_increment,
    depname char(16),
    dep_desc char(16)
);
​
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   # 同步删除
)
​2、多对多

file://D:\学习笔记\%E6%95%B0%E6%8D%AE%E5%BA%93.assets\1694094685426.png?lastModify=1694834371

file://D:\学习笔记\%E6%95%B0%E6%8D%AE%E5%BA%93.assets\1694103622699.png?lastModify=1694834371

"""
图书表与作者表
"""
create table book(
    id int primary key auto_increment,
    title char(32),
    price int,
    author_id int,
    foreign key(author_id) reference author(id)
    on update cascade
    on delete cascade
);
​
create table author(
    id int primary key auto_increment,
    name char(32),
    age int,
    book_id int,
    foreign key(book_id) reference book(id)
    on update cascade
    on delete cascade
);
"""
按照上述的方式创建  一个创建不成功，因为需要先创建关联的表，对于他们之间都是关联的表
其实我们只是像记录书籍和作者之间的关系
针对多对多字段的关系 不能在两张原有的表中创建外键
需要单独开设一张 专门用力存储两张表的数据的关系
"""
​
create table book(
    id int primary key auto_increment,
    title char(32),
    price int,
);
​
create table author(
    id int primary key auto_increment,
    name char(32),
    age int,
);
create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) reference author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) reference book(id)
    on update cascade
    on delete cascade
)
​3、一对一
"""
id name age addr phone hobby email ...
如果一个表的字段特别多，每次查询又不是所有字段都能用得到
将表一分为二
    用户表
        用户表
            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) reference authordetail(id)
    on update cascade
    on delete cascade
);4、总结
"""
表关系的建立需要用到foreign key
    一对多
        外键字段建在多的一方
    多对多
        外键字段建立在第三张表
    一对一
        建立在任意一张表都可以，但是推荐查询频率高的表中
​
判定表之间的关系的方式
    换位思考!!!
        员工与部门
        
        图书与作者
        
        作者与作者详情
        
外键关系:
    外键创建是需要消耗资源的，可以通过创建外键关联
    可以不创建外键，关联表关系
        
"""
​
​

郑统京

一对多、多对多、一对一