MySQL数据库入门(五)表结构的创建与分析

在建表之前要对数据进行认真分析，字段名是数据的抽象名称，字段属性是数据的类型与约束。建表时要把关联度极高的数据放在一张表中，假设该表中某些字段存在大量重复的情况就应该考虑分开建表，减少冗余数据。

学前准备

create database day2;  # 新建数据库day2
use day2;  # 使用day2数据库

分析表之间的关系

建表时要通过下面两个步骤认真分析表与表之间的关系：

一、分析关系：

1. 先站在左表的角度去找：
   是否左表的多条记录可以对应右表的一条记录，如果是则说明左表的某个字段foreign key右表某个字段（通常是id）。
2. 再站在右表的角度去找：
   是否右表的多条记录可以对应左表的一条记录，如果是则说明右表的某个字段foreign key左表某个字段（通常是id）。

二、得出结论：

• 多对一：
  如果只有步骤1成立，则是左表多对一右表，
  如果只有步骤2成立，则是右表多对一左表。
• 多对多：
  如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系。
• 一对一：
  如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可。

表与表之间三种关系

一、多对一，多的那张表外键关联少的那张表

• 学生表与班级表，学生多对班级一的关系：

建表时要先建"一"的班级表，班级表有cid、name、start这些字段：

create table class_name(
    cid int unsigned primary key auto_increment,
    name char(12) not null,
    start date not null
    );

班级数据：

insert into class_name(name,start) values
    ('MYSQL一期','2021-02-01'),
    ('MYSQL二期','2021-02-02'),
    ('MYSQL三期','2021-02-03');

再建"多"的学生表，学生表有id、name、cid：

create table student(
    sid int unsigned primary key auto_increment,
    name char(12) not null,
    cid int unsigned,
    foreign key(cid) references class_name(cid)
    );

学生数据：

insert into student(name,cid) values
    ('张三','1'),
    ('李四','1'),
    ('王五','2'),
    ('赵六','3'),
    ('钱三','3'),
    ('孙二','2');

学生表有一个外键cid关联班级表的cid字段。

二、多对多，使用第三张表用两个外键分别关联两张表

书籍表与作者表，书籍多对作者多的关系，此时需要另建关系表关联书籍表与作者表：

建表时要按数据流的顺序建表，首先建作者表，作者表有aid、name、gender、birthday、phone这些字段：

create table author(
    aid int unsigned primary key auto_increment,
    name char(18) not null,
    gender enum('male','female') default 'male',
    birthday date,
    phone char(11) not null
    );

作者数据：

insert into author(name,gender,birthday,phone) values
    ('张三','male','1980-01-01','139'),
    ('李四','male','1988-08-05','138'),
    ('王五','male','1989-09-09','133');

再按数据流的顺序建书籍表，书籍表有bid、name、day这些字段：

create table book(
    bid int unsigned primary key auto_increment,
    name char(18) not null,
    day date
    );

书籍数据：

insert into book(name,date) values
    ('python入门','2020-01-01'),
    ('python入门2','2020-01-02'),
    ('python入门3','2020-01-03'),
    ('python进阶','2020-01-05'),
    ('python进阶2','2020-01-07'),
    ('python深入','2020-01-08'),
    ('python深入2','2020-01-09');

最后创建关系表，将书籍与作者关联起来，关联表有aid和bid这2个字段。

create table author2book(
    bid int unsigned not null,
    aid int unsigned not null,
    constraint fk_author foreign key(aid) references author2(aid),  # 创建名称是fk_author的外键，关联本表的aid和author2表的aid
    constraint fk_book foreign key(bid) references book2(bid)  # 创建名称是fk_book的外键，关联本表的bid和book2表的bid
    );

关系表数据：

insert into author2book values
    (1,1),  # 张三 著 python入门
    (2,1),  # 张三 著 python入门2
    (3,1),  # 张三 著 python入门3
    (4,1),  # 张三 著 python进阶
    (4,2),  # 李四 著 python进阶
    (5,1),  # 张三 著 python进阶2
    (5,2),  # 李四 著 python进阶2
    (6,3),  # 王五 著 python深入
    (6,2),  # 李四 著 python深入
    (7,3),  # 王五 著 python深入2
    (7,2);  # 李四 著 python深入2

总结：多对多是三张表，第三张表创建外键约束对应前两张表中的id。

三、一对一，数据少的表外键关联数据多的表
客户表与会员表，所有客户记录在一张表中，其中部分客户加入了会员，则记录在另一张会员表中。
客户表id字段必须有not null和unique约束(primary key同时包含这2个约束)，会员表外键关联客户表id(foreign key unique)，客户表id也必须有not null和unique约束(primary key同时包含这2个约束)。
建表时首先建客户表，客户表有id、姓名、手机字段：

create table customer(
    id int unsigned primary key auto_increment,  # 客户id字段不能重复且不为空
    name char(18) not null,  # 客户姓名字段
    phone char(11) not null  # 客户电话字段
    );

客户表数据：

insert into customer(name,phone) values
    ('张三','133'),
    ('张大','132'),
    ('张二','134'),
    ('李四','130'),
    ('王五','135'),
    ('赵六','136'),
    ('钱大','137'),
    ('钱二','138');

然后创建会员表，会员表有id、入会时间这两个字段：

create table members(
    id int unsigned primary key,  # id字段关联客户表的id字段，不能重复不能为空
    join_date date,  # 入会时间
    foreign key(id) references customer(id)  # 建立id与customer表id字段的外键关系
    );

会员表数据：

insert into members values
    (1,'2020-01-01'),
    (3,'2020-01-03'),
    (6,'2020-01-05'),
    (8,'2020-02-05');

数据冗余的缺陷

这里拿前面的学生班级表举例，请看下面的案例：
表结构：

create table student2(
    id int unsigned primary key auto_increment,  # 学生id
    name char(12) not null,  # 学生姓名
    class_name char(12) not null,  # 班级姓名
    start date not null  # 开班日期
    );

表内容：

insert into student2(name,class_name,start) values
    ('张三','MYSQL一期','2021-02-01'),
    ('李四','MYSQL一期','2021-02-01'),
    ('王五','MYSQL二期','2021-02-02'),
    ('赵六','MYSQL三期','2021-02-03'),
    ('钱三','MYSQL三期','2021-02-03'),
    ('孙二','MYSQL二期','2021-02-02');

假设现在遇到这样的情况，"MYSQL三期"开班日期从2021-02-03要改为2021-02-05，这样的表结构需要更改2条数据。如果"MYSQL三期"有几百个学生，整个表有几万条记录，那么这样的操作非常耗时且容易错漏。相比较把这个student表拆分成学生表和班级表后，同样的需求只要改班级表里面的一条数据即可。大大提高了整体效率，降低了数据冗余。