MySQL数据库学习（5） -- 表与表之间的关系以及关于表的操作

外键

表与表之间的关系

定义一张员工表  表中有很多字段
id     name    gender     dep_name    dep_desc

该表的问题
    1. 该表的组织结构不是很清晰
    2. 有一定浪费空间
    3. 数据扩展性极差（比如说，要修改 教学部 的 dep_desc，就需要一个一个修改，如果数据过大，会浪费大量时间和资源）
    
# 如何优化？
    （上述问题就类似于将所有的代码都写在一个 py 文件中）
    将上述的员工表拆分成员工表和部门表

外键

外键就是用来帮助我们描述表与表之间的关系的
foreign key

表关系

表与表之间最多只有四种关系
    一对多关系（多对一关系也就是一对多关系）
    多对多关系
    一对一关系
    没有关系

一对多关系

"
判断表与表之间的关系
换位思考，站在两张表的角度思考

先站在员工表的角度
    思考一条员工数据是否可以对应多条部门数据
    不能！！！
    （但不能直接得出结论）
    
站在部门表的角度
    思考一条部门数据是否可以对应多条员工数据
    能！！！
    
得出结论
    员工表和部门表是单向的一对多关系
    所以表关系就是一对多
"

# sql 语句建立表关系
foreign key	
    一对多表关系 外键字段建在“多”的一方
    在创建表的时候一定要先建被关联表
    在录入数据的时候先录入被关联表
    
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(16)
);

create table emp(
    id int primary key auto_increment,
    emp_name char(16),
    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
("aoteman", "Tiga"),
("alterman", "ash");

insert into emp(emp_name, dep_id)
values
("123", 1),
("456", 2);

# 修改 dep 中的 id 字段
update dep set id=200 where id=2; # 报错
# 删除 dep 
delete from dep; # 报错


# 真要删除和修改数据
    # 1. 先删除对应的员工数据再删除部门，操作繁琐
    # 2. 真正做到数据之间有关系
        # 同步更新，同步删除
        # 级联更新，级联删除
        on update cascade # 同步更新
         on delete cascade # 同步删除
        
        create table dep(
            id int primary key auto_increment,
            dep_name char(8),
            dep_desc char(8)
        );
        
        create table emp(
            id int primary key auto_increment,
            emp_name char(8),
            emp_gender enum("male", "female", "other") 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
        ("aoteman", "Tiga"),
        ("alterman", "ash");
        
        insert into emp(emp_name, dep_id)
        values
        ("111", 1),
        ("222", 2);
        
        select * from dep;
+----+----------+----------+
| id | dep_name | dep_desc |
+----+----------+----------+
|  1 | aoteman  | Tiga     |
|  2 | alterman | ash      |
+----+----------+----------+
        
        select * from emp;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
|  1 | 111      | male       |      1 |
|  2 | 222      | male       |      2 |
+----+----------+------------+--------+

        # 更新数据
        update dep set id=200 where id=2;
        
        select * from dep;
+-----+----------+----------+
| id  | dep_name | dep_desc |
+-----+----------+----------+
|   1 | aoteman  | Tiga     |
| 200 | alterman | ash      |
+-----+----------+----------+
        
        select * from emp;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
|  1 | 111      | male       |      1 |
|  2 | 222      | male       |    200 |
+----+----------+------------+--------+

多对多关系

# 针对多对多的表关系 不能在两张原有的表中创建外键，需要单独开设一张表，专门用来存储两张表数据之间的关系

create table author (
    id int primary key auto_increment,
    name char(8),
    age int
);

create table book(
    id int primary key auto_increment,
    title char(8),
    price int
);

create table author2book(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

一对一关系

id  name  age  phone  hobby  email

如果一张表的字段特别多，每次查询又不是所有的字段可以用到，可以将表一分为二

用户表
    用户表
        id name age
    用户详情表
        id addr phonr hobby email
        
    站在用户表的角度
        一个用户能否对应多个详情表？
        不能！！！
        
    站在详情表的角度
        一个详情能否对应多个用户？
        不能！！！
    
    结论：
        单项的一对多不成立 那么这个时候两表之间的关系就是  一对一（或者是没有关系，没有关系的情况会很好判断）
        
# 一对一外键字段建在任意表都可以，但建议建在查询频率较高的表里（如：用户表）
create userdetail(
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);

create table user(
    id int primary key auto_increment,
    name varchar(8),
    age int,
    foreign key(id) references userdetail(id)
    on update cascade
    on delete cascade
);

修改表

# MySQL 对大小写是不敏感的（不区分大小写）

# 修改表
alter table 表名 rename 新表名;

# 增加字段（默认在尾部添加）
alter table 表名 add 字段名 字段类型（宽度） 约束条件;
alter table 表名 add 字段名 字段类型（宽度） 约束条件 first;
alter table 表名 add 字段名 字段类型（宽度）
约束条件 after 字段名;

# 删除字段
alter table 表名 drop 字段名;

# 修改字段
alter table 表名 modify 字段名 字段类型（宽度） 约束条件;

alter table 表名 change 旧字段 新字段 字段类型（宽度） 约束条件;

复制表

# sql 语句查询的结果其实也是一张虚拟表，言外之意就是针对这个表的查询结果还可以继续用查询的语法继续操作该虚拟表

# 不能复制主键，外键....复制时也可以添加条件
create table 新表名 select * from 旧表名;

补充

表与表之间如果有关系，可以有两种方式建立联系
    1. 是通过外键强制建立关系
    
    2. 就是通过 sql 语句逻辑层面上建立关系
        delete from dep where id=1;
        delete from emp where id=1;
        即：如果要执行上面的第一个 sql 语句，那么就一定要执行第二个 sql 语句
        
    创建外键会消耗一定的资源并且增加表与表的耦合度
    在实际项目中，如果表特别多，可以不建立任何外键处理 直接通过 sql 语句