一、表与表之间的关系之多对一
   1、把所以数据都存放于一张表的弊端
        1.表的组织结构复杂不清晰
        2.浪费空间
        3.扩展性极差

   2、寻找表与表之间的关系的套路
        举例:emp表        dep表
        步骤一:
                 part1:
                    1、先站在左表emp的角度
                    2、去找左表emp的多条记录能否对应右表dep的一条记录
                    3、翻译2的意义:
                                 左表emp的多条记录==》多个员工
                                 右表dep的一条记录==》一个部门

                                  最终翻译结果:多个员工是否可以属于一个部门?
                                  如果是则需要进行part2的流程
                 part2:

1、站在右表dep的角度
        2、去找右表dep的多条记录能否对应左表emp的一条记录
        3、翻译2的意义:
               右表dep的多条记录==》多个部门
               左表emp的一条记录==》一个员工

               最终翻译结果:多个部门是否可以包含同一个员工

               如果不可以,则可以确定emp与dep的关系只一个单向的多对一
               如何实现?
                   在emp表中新增一个dep_id字段,该字段指向dep表的id字段

          foreign key 会带来什么样的效果?
          1.约束1:在创建表时,先建被关联的表dep,才能建关联表emp



create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);



          2、约束2:在插入记录时,必须先插被关联的表dep,才能插入关联表emp



insert into dep(dep_name,dep_comment) values
('教学部','辅导学生学习,教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('技术部','技术能力有限部门');


insert into emp(name,gender,dep_id)  values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);



         3、约束3:更新与删除都需要考虑到关联与被关联的关系
          解决方案:
          1、先删除关联表emp,再删除被关联表dep,准备重建



mysql> drop table emp;
Query OK, 0 rows affected (0.11 sec)

mysql> drop table dep;
Query OK, 0 rows affected (0.04 sec)



           2、重建:新增功能,同步更新,同步删除



create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade   #同步更新
    on delete cascade   #同步删除
);
insert into dep(dep_name,dep_comment) values
('教学部','辅导学生学习,教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('技术部','技术能力有限部门');


insert into emp(name,gender,dep_id)  values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);


cmd运行代码:
# 同步删除
mysql> select * from dep;
+----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name         | dep_comment                                                                              |
+----+------------------+------------------------------------------------------------------------------------------+
|  1 | 教学部           | 辅导学生学习,教授python课程                                                           |
|  2 | 外交部           | 老男孩上海校区驻张江形象大使                                                             |
|  3 | 技术部           | 技术能力有限部门                                                                       |
+----+------------------+------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+------------------+--------+--------+
| id | name             | gender | dep_id |
+----+------------------+--------+--------+
|  1 | alex             | male   |      1 |
|  2 | egon             | male   |      2 |
|  3 | lxx              | male   |      1 |
|  4 | wxx              | male   |      1 |
|  5 | wenzhou          | female |      3 |
+----+------------------+--------+--------+
5 rows in set (0.00 sec)

mysql> delete from dep where id=1;
Query OK, 1 row affected (0.02 sec)

mysql> select * from dep;
+----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name         | dep_comment                                                                              |
+----+------------------+------------------------------------------------------------------------------------------+
|  2 | 外交部           | 老男孩上海校区驻张江形象大使                                                             |
|  3 | 技术部           | 技术能力有限部门                                                                       |
+----+------------------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+----+------------------+--------+--------+
| id | name             | gender | dep_id |
+----+------------------+--------+--------+
|  2 | egon             | male   |      2 |
|  5 | wenzhou          | female |      3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)

#同步更新
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name             | gender | dep_id |
+----+------------------+--------+--------+
|  2 | egon             | male   |      2 |
|  5 | wenzhou          | female |      3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)

mysql> update dep set id=200 where id =2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep;
+-----+------------------+------------------------------------------------------------------------------------------+
| id  | dep_name         | dep_comment                                                                              |
+-----+------------------+------------------------------------------------------------------------------------------+
|   3 | 技术部           | 技术能力有限部门                                                                       |
| 200 | 外交部           | 老男孩上海校区驻张江形象大使                                                             |
+-----+------------------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+----+------------------+--------+--------+
| id | name             | gender | dep_id |
+----+------------------+--------+--------+
|  2 | egon             | male   |    200 |
|  5 | wenzhou          | female |      3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)



 

 二、多对多
       1.什么是多对多
            两张表之间是一个双向的多对一的关系,称之为多对多
            如何实现?
            建立第三张表,该表中有一个字段fk左表的id,还有一个字段是fk右表的id



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

create table book(
    id int primary key auto_increment,
    bname char(16),
    price int
);

insert into author(name) values
('egon'),
('alex'),
('wxx')
;
insert into book(bname,price) values
('python',200),
('葵花宝典',800),
('九阴真经',500),
('九阳神功',100)
;


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

insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4);

cmd代码运行:
查看第三张表
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       3 |
|  2 |         1 |       4 |
|  3 |         2 |       2 |
|  4 |         2 |       4 |
|  5 |         3 |       1 |
|  6 |         3 |       2 |
|  7 |         3 |       3 |
|  8 |         3 |       4 |
+----+-----------+---------+
8 rows in set (0.00 sec)



 

 三、一对一
       左表的一条记录唯一对应右表的一条记录,反之也一样



create table customer(
    id int primary key auto_increment,
    name char(20) not null,
    qq char(10) not null,
    phone char(16) not null
);

create table student(
    id int primary key auto_increment,
    class_name char(20) not null,
    customer_id int unique, #该字段一定要是唯一的
    foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
    on delete cascade
    on update cascade
);

insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;


#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;