day36 外键

r囧r小猫 2023-08-17 17:48 46阅读 0赞

# 外键 #

表与表之间建立关系

所有的信息都记录在一张表中所带来的问题

1.表的结构不清晰

2.浪费硬盘空间

3.表的扩展性极差(无法忽略的缺点)

![1659204-20190820200537386-558973054.png][]

类似于我们将所有的代码都写在用一个py文件内

## 一对多 ##

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

![1659204-20190820200655247-1616720346.png][]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

以员工表和部门表为例
            先站在员工表看能否有多个员工对应一个部门
                翻译过来:
                    一个部门能否有多个员工
                    可以!!!(暂时只能确定员工单向多对一部门)
            再站在部门表看能否有多个部门对应一个员工
                翻译过来:
                    一个员工能否属于多个部门
                    不可以!!!
        结论:员工表和部门表之间仅仅是单向的多对一
        那么它们的表关系就是"一对多"
        表关系中没有多对一一说,只有一对多
        (无论是多对一还是一对多都叫"一对多")

如何让两种表有代码层面上真正的关联 就必须使用外键

什么是外键?

让表与表有硬性层面上的关系

foreign key
        外键约束
            1.在创建表的时候 必须先创建被关联表
            2.插入数据的时候 也必须先插入被关联表的数据

建表

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

create table dep(
                id int primary key auto_increment,
                dep_name varchar(32),
                dep_desc varchar(128)
            );
            
        
            create table emp(
                id int primary key auto_increment,
                emp_name varchar(64),
                emp_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('外交部','搞外交'),
            ('教学部','教书育人'),
            ('技术部','技术能力有限部门')
            ;
            
            insert into emp(emp_name,dep_id) values('jason',1),
            ('egon',2),
            ('tank',2),
            ('kevin',3);
            
        
        修改表数据
            update dep set id=200 where id = 1;
            update emp set dep_id = 300 where id = 1;
            
            delete from dep where id = 3;
            
            外键虽然能够帮你强制建立表关系 但是也会给表之间增加数据相关的约束
            
            1.删除数据的时候  先删员工表的数据 再删部门表的数据
                delete from emp where id = 4;
                delete from dep where id = 3;
            2.级联更新级联删除
                create table dep(
                    id int primary key auto_increment,
                    dep_name varchar(32),
                    dep_desc varchar(128)
                );
            
                create table emp(
                    id int primary key auto_increment,
                    emp_name varchar(64),
                    emp_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(emp_name,dep_id) values('jason',1),
                ('egon',2),
                ('tank',2),
                ('kevin',3);
        
            update dep set id=200 where id = 3;
            delete from dep where id = 2;

## 多对多 ##

图书与作者表
            一定要换位思考
                先站在图书
                    多本书能否有一个作者
                        一个作者能否写多本书    可以!!!
                
                再站在作者
                    多个作者能否和写一本书        
                        一本书能否有多个作者    可以!!!
                        
            
            如果双方都是可以,那么就是多对多
            强调  foreign key只是用来帮你建表关系的 不是某个关系特有的方法

代码

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

create table book(
                    id int primary key auto_increment,
                    title varchar(32),
                    price int,
                    author_id int,
                    foreign key(author_id) references author(id)
                    on update cascade  # 同步更新
                    on delete cascade  # 同步删除
                
                );
                
                create table author(
                    id int primary key auto_increment,
                    name varchar(32),
                    age int,
                    book_id int,
                    foreign key(book_id) references book(id)
                    on update cascade  # 同步更新
                    on delete cascade  # 同步删除
                );
            
            注意上述的建法是错误的 多对多关系的建立 必须手动创建第三张表 用来专门记录两种表之间的关系
            
            
            先建两种普通的表  不需要设置外键
            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,
                    book_id int,
                    foreign key(book_id) references book(id)
                    on update cascade
                    on delete cascade,
                    author_id int,
                    foreign key(author_id) references author(id)
                    on update cascade
                    on delete cascade
            );
        
            insert into book(title,price) values('金梅',199),('聊斋',299),('jason教你删别人的库,让别人跑去吧',1);
            
            
            insert into author(name,age) values('jason',18),('tank',38);
            
            
            insert into book2author(book_id,author_id) values(4,3);  # 报错
            insert into book2author(book_id,author_id) values(1,1),(1,2),(2,1),(3,1),(3,2);

## 一对一 ##

1.一对一的场景 当你的表特别庞大的时候 你可以考虑拆分表
            2.联想老男孩的客户和学生
            
        user
        id  name  age                                  
        
        userdetail
        hobby addr IDCard 
        
        
        当你没有交学费之前 你是老男孩的客户
        当你叫了学费之后  你就变成老男哈的学生
        是所有的客户都能变成学生吗?
            
        
        
        
        
        
        
        通常将关系字段 称之为 外键字段
        一对多的外键字段  建在多的一方
        多对多   建在第三张表了
        一对一   外键字段建在任意一方都可以   但是推荐你建在查询频率较高的一方

代码

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

create table authordetail1(
                id int primary key auto_increment,
                phone int,
                addr char(255)
        
        );
        
        
        
        
        
        
        
        create table author1(
            id int primary key auto_increment,
            name char(4),
            age int,
            authordetail_id int unique,
            foreign key(authordetail_id) references authordetail1(id)
            on update cascade
            on delete cascade
        );

#### 判断表关系的最简单的语法 ####

图书与出版社
            一本书可不可以有多个出版社  不可以!!!
            一个出版社可不可以出版多本书  可以!!!
            一对多的关系
    
            图书与作者表
            一本书可不可以有多个作者    可以!!!
            一个作者可不可以写多本书     可以!!!
            多对多的关系
            
        
            作者与作者详情
            一个作者可不可以有多个详情  不可以!!!
            一个作者详情可不可以有多个作者  不可以!!!
             要么两者是一对一
             要么两者之间没任何关系

## 修改表 ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

# mysql对大小写不敏感！！！
    语法：
    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 旧字段名 新字段名 新数据类型 [完整性约束条件…];

## 复制表 ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

# 查询语句执行的结果也是一张表，可以看成虚拟表
    
    # 复制表结构＋记录 （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;

转载于:https://www.cnblogs.com/komorebi/p/11385377.html

[1659204-20190820200537386-558973054.png]: /images/20230808/cab7755c8db74bb0879dad031e43a177.png
[1659204-20190820200655247-1616720346.png]: /images/20230808/52b03d53f6d4492085314b61f98f69af.png
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20230808/0e9931d15a6a4b91b6d7b7d850711009.png