mysql数据库

---恢复内容开始---

1.外键（重点）

2.修改表的完整语句（了解知识点）

一.外键

外键一共分为三大类：一对多，多对多，一对一
1.一对多：
为什么要有这些外键了：

比如说定义了一张员工表

# 定义一张部门员工表
id        name        gender        dep_name        dep_desc
1            jason            male            教学部                教书育人
2            egon            male            外交部                漂泊游荡
3            tank            male            教学部                教书育人
4            kevin            male            教学部                教书育人
5            owen            female        技术部                技术能力有限部门

那么这张表有什么问题 ？：

把所有的数据都存放在一张表的弊端：
1.表的结构不清晰
2.浪费硬盘空间（因为有相同的部门，需要重复写）
3.表的扩展性极差（无法忽略的缺点）

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

那么我们该怎么解决这个问题：（我们要把表分开，解耦和）

　　　　　　　　　　emp

id	emp_name	emp_gender	dep_id
1	jason	male	1
2	egon	female	2
3	tank	male	2
4	jerry	male	2
5	kevin	male	3

　　　　　　　　　　　　dep

id	dep_name	dep_desc
1	外交部	形象代言人
2	教学部	教书育人
3	技术部	技术能力有限部门

表示解耦和了，那么该如何确定这俩张表是什么关系：

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

先站在员工表看能否有多个员工对应一个部门
翻译过来:一个部门能否有多个员工  可以!!!(暂时只能确定员工单向多对一部门)

再站在部门表看能否有多个部门对应一个员工
翻译过来:一个员工能否属于多个部门    不可以!!!

由此得出的结论就是：
员工表和部门表之间仅仅是单向的多对一，那么他们的关系就是！！一对多！！，
表关系中没有！多对一！这么一说，只有一对多


那么该如何让俩张表有代码上的关联，这就必须使用外键了：
什么是外键：就是让表与表之间有硬性上的关系

该如何使用：
foreign key
外键约束
1.在创建表的时候 必须先创建被关联表
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=1;
update emp set dep=300 where =1;
现在还不能修改表里的数据，需要在外键后面加上 2行代码
on update cascade 级联更行
on delete cascade  级联删除

注意：外键虽然能够帮你强制建立表的关系，但是也会给表之间增加数据的相关的约束

1.删除数据的时候，需要先删员工表的数据，再删部门表的数据（因为员工表设置了外键，你要是先删部门了，那么员工表的外键序号是不存在的）
delete from emp where id = 4;
delete from dep where id = 3;

1。受限于外键约束，导致操作数据变得非常复杂，能否有一张简单的方式，
让我不需要考虑在操作目标表的时候还需要考虑关联表的情况，比如我删除部门
，那么这个部门对应的员工就应该跟着立即清空

2.更新于删除都需要考虑到关联与被关联的关系>>>同步更新与同步删除

这里就需要用到  级联更新级联删除

删除部门后，对应的部门里面的员工表数据对应删除
更新部门后，对应员工表中的标示部门的字段同步更新

2.多对多

分析多对多表的关系：

多对多表的关联关系：

可以比喻为 图书表与作者表（记住一定要换位思考）

先站在图书表来看：
    多本书能否有一个作者  可以！！
    一本书能否有多个作者  可以！！

再站在作者表来看：
    多个作者能否合写一本书  可以！！
    一本书能否有多个作者  可以！！

如果双方都是可以，那么就是多对多的关系

不逼逼了，看图吧：

book

id	title	price	desc
1	精品美	999	小黄书
2	python从入门到放弃	1000	教你如何从小白变成屌丝
3	聊斋志异	6969	带你看鬼

author

id	name	age
1	jason	18
2	egon	19

那么代码我们该如何实现：
先来想如何创建表？图书表需要有一个外键关联作者，作者也需要有一个外键字段关联图书。问题来了，先创建谁都不合适！如何解决？因为创建外键的时候，需要一个被关联的id

解决：

记住，多对多关系的建立，必须手动创建第三张表，用来专门记录俩种表之间的关系。

先建俩种普通的表，不需要设置外键

注意：

重点在第三张表上，设置2个id字段，每个字段下面设置外键

代码实现：

    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); 
        

3.一对一表的关系

1.一对一的场景，当你的表的数据非常庞大的时候，你可以考虑拆分表
2.联想老男孩的客户和学生
user
id  name  age

userdetail（用户详细信息）
hobby  addr  ICDard

当你没有交学费之前 ，你是老男孩的客户
当你交了学费之后，你就变成老男孩的学生
问题来了：
难道只要是咨询的客户都会变成学生么？？？怎么可能

一对一就是一一对应，不能一对多，也不能多对多

注意！！

我们通常将关系字段，称之为外键字段

一对多的外键字段：建在多的一方

多对多的外键字段：建在第三张表里

一对一的外键字段：建在任意一方都可以，但是推荐你建在查询频率较高的一方

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
    );
    

总结：判断表关系的最简单的方法

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

了解知识点：

    1.修改表的完整语句
            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;