MYSQL--表与表之间的关系、修改表的相关操作

表与表之间的操作：

　　如果所有信息都在一张表中：

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

　　插入数据：

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 只是用来帮你建立表关系的。不是某个关系特有的方法

　　1、先建立两种普通的表。不需要设置外键：

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

 

　　2、手动建立第三张表，用来专门记录两种表之间的关系

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

 

　　3、插入数据

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、一对一主要用在当你的表特别庞大，这时候你就可以考虑将这个表拆分开来

　　　　可以联想到客户和学生

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、修改表名称
    ALTER TABLE 表名 RENAME 新表名;
2、增加字段
    ALTER TABLE 表名 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;