MySql之建表思想

比眉伴天荒 2022-12-28 04:08 36阅读 0赞

## 1.数据库主表、从表、主键、外键 ##

（1）主键：一般情况下，满足第一范式的表都有一个主键Primary key，用于唯一标识的数据库表中的一个字段。  
外键：外键是相对于数据库设计中的参考完整性而言，它与主键之间是彼此依赖的关系。假设现在有两个表，产品分类表ProductCategory(主键c\_id)和产品表Product(主键p\_id)，每类产品都属于一个分类。那么如果产品信息表肯定需要参考产品分类表进行定义。因为如果没有产品分类表，又何谈产品分类呢。所以产品信息表Product(从表)需要引用ProductCategory（主表）中的主键CategoryId 进行产品分类定义，Product表中引用c\_id的字段就是外键。  
（从表的外键与主表的主键相对应以此用来和主表相关联或者说主表的主键作为从表的外键以此来和从表相关联）  
主表：被作为外键引用的表。  
从表：有外键引用的表。  
外键可以为空值（除了SQLServer等一些数据库），但如果有值的话一定是你参照的那个主表中的主键值。换句话说，从表需要用到主表的属性，没有主表就没有从表。  
**当删除数据时**：  
delete cascade （级联删除）：如果主表中的一个主键被删除了，那么引用该主键的从表中的所有记录也被删除。  
restrict （删除限制）：如果主表中的一个主键被删除时,当从表中仍有外键引用这个主键时，那么不允许直接删除主表的这条记录，必须先删除或修改引用该主键的外键才能删除。  
no action（无参照完整性关系）：无参照完整性关系,有了也不生效。  
update更新同理。  
从表和主表通过主外键相关联。主表的称为主键，从表的称为外键。主外键主要用于参照完整性。

## 2.复合主键和联合主键 ##

（1）  
首先说一下联合主键，联合主键其实就是中间表。在多对多模型里，需要两个表中的主键组成联合主键，这样就可以查到两个表中的每个数据，如下例所述：

CREATE TABLE TEAM( 
    Id MEDIUMINT AUTO_INCREMENT COMMENT '主键',
    Dev VARCHAR (30) COMMENT '码农', 
    Pm VARCHAR(30) COMMENT '项目经理', 
    Hr VARCHAR(30) COMMENT '人力' , 
    PRIMARY KEY (Id) 
    )
    ENGINE = INNODB, 
    CHARSET = UTF8 ;

CREATE TABLE information (
    Id MEDIUMINT AUTO_INCREMENT COMMENT '主键',
    Name VARCHAR(30) COMMENT '姓名',
    Age int(10) COMMENT '年龄',
    PRIMARY KEY(Id)
    )
    ENGINE=INNODB,
    CHARSET = UTF8;

为了查询两个表之间的元素，我们创建一个中间表 TEAM\_info  
联合主键就是第三张表

CREATE TABLE IF NOT EXISTS TEAM_info(
    Id MEDIUMINT AUTO_INCREMENT COMMENT '主键Id',
    TEAM_Id MEDIUMINT COMMENT '团队Id',
    info_Id MEDIUMINT COMMENT '信息Id',PRIMARY KEY(Id) 
    )
    ENGINE=INNODB,
    CHARSET=UTF8;

然后就可以通过中间表进行连表查询操作。

（2）  
接下来是复合主键，一张表只能有一个主键，但根据需要，我们可以设置多个字段同时为主键，这就叫做复合主键。(PS:我看好多人翻译成联合主键，我个人感觉不太准确，容易混淆）比如我们创建一张表使用多个字段来作为主键

CREATE TABLE IF NOT EXIST student(
    Name VARCHAR (30 ) COMMENT '姓名',
    Age INT(30)  COMMENT '年龄',
    PRIMARY KEY(Name,Age) 
    );

这里的Name和Age字段就是复合主键，主要是为了防止Name重复，因此设置Age和Name为复合主键，主键的字段长度和字段数目要越少越好。然后这里有个注意点，如果要在INNODB引擎下设置主键自增长，自增长的列必须是第一列，然后只需要指定非自增长的列就可以了，否则会出现自增长重复。一旦出现自增长重复的话，我们可以创建一个主键索引，或者唯一索引。如果表结构已存在，想要添加复合主键，则应该使用如下方式。

ALTER TABLE tb_name ADD PRIMARY KEY (
    PRIMARY_KEY_NAME_1,
    PRIMARY_KEY_NAME_2
    );

需要注意的是，这里需要原来的表结构里没有主键，如果有的话，需要先ALTER DROP掉原有的主键，再添加复合主键进去。

## 3.创建表格 ##

（1）复杂类型的对象有几种表现形态

> 一对一  
> 一对多或多对一  
> 多对多

（2）一对多或多对一的对象存到数据库表的设计方案  
以部门和员工的关系来说明一对多或多对一的对象是怎么存储到数据库表中的。  
数据库表的设计的原则：先不要去管这些对象的关系，看某个对象有什么基本属性，然后设计一个表来保存此对象的基本数据。在数据库里面怎么去保证数据往数据库里面存的时候，关系不丢呢？这里面有一个原则，记住一句话——在多的一方加外键描述数据之间的关系。  
数据库表的设计方案如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70]  
数据库表的SQL语句如下：  
department表

create table department
    (
        id int primary key,
        name varchar(40)
    );

d\_employee表

create table d_employee
    (
        id int primary key,
        name varchar(40),
        salary decimal(8,2),
        department_id int,
        constraint department_id_FK foreign key(department_id) references department(id)
    );

> 主表的主键来作为从表的外键

（3）多对多对象的表的设计  
以老师和学生的关系来说明多对多的对象是怎么存储到数据库表中的。  
数据库表的设计的原则：先不要去管这些对象的关系，看某个对象有什么基本属性，然后设计一个表来保存此对象的基本数据。在数据库里面怎么去保证数据往数据库里面存的时候，关系不丢呢？需要加一个中间表来描述数据的关系。  
数据库表的设计方案如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 1]  
数据库表的SQL语句如下：  
teacher表

create table teacher
    (
        id int primary key,
        name varchar(40),
        salary decimal(8,2)
    );

student表

create table student
    (
        id int primary key,
        name varchar(40)
    );

teacher\_student表(中间表)

create table teacher_student
    (
        teacher_id int,
        student_id int,
        primary key(teacher_id,student_id),
        constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
        constraint student_id_FK foreign key(student_id) references student(id)
    );

> 注意：关于中间表的设计，在设计主键时，应将teacher\_id，student\_id这2列作为联合主键，即这2列值加在一起不能重复。（使用第三张表，主键相互对应）

（4）一对一的对象的数据库设计  
以身份证管理系统来说明一对一的对象是怎么存储到数据库表中的。  
数据库表的设计的原则：先不要去管这些对象的关系，看某个对象有什么基本属性，然后设计一个表来保存此对象的基本数据。一对一的对象有一个主从关系，主可以没有从，但从不能没有主。在身份证管理系统中，一对一的关系表现为一个人只能有一张身份证，一张身份证只能属于一个人；主是人，从是身份证，一对一的对象有一个主从关系表现为人可以没有身份证，但身份证不能不属于人。  
数据库表的设计方案如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 2]  
数据库表的SQL语句如下：  
person表

create table person
    (
        id int primary key,
        name varchar(40)
    );

idcard表

create table idcard
    (
        id int primary key,
        city varchar(40),
        constraint id_FK foreign key(id) references person(id)
    );

> （通过两张表主键想对应）

（5）自连接表的设计  
以家族管理系统来说明自连接表的设计。  
数据库表的设计方案如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 3]  
注意：自连接，外键列不能加非空约束。  
数据库表的SQL语句如下：  
person表

create table person
    (
        id int primary key,
        name varchar(40),
        parent_id int,
        constraint parent_id_FK foreign key(parent_id) references person(id)
    );

面试题：一个无限极分类的数的数据库表的设计(请设计一个无限极分类的表)。  
解：以一个商品分类表来说明怎样去设计一个无限极分类的表。这里用图来说明

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 4]  
总结  
如果不使用数据库的三大范式建表，而是设计一张表，把所有数据存到一张表里，那么会出现冗余数据，但是查询性能很好，不需要查多表。例如，在多对多对象的表的设计中，我们可以设计一张表，把所有数据存到一张表里。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70]: /images/20221120/259f32d4eda6471e91713506a4906be7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 1]: /images/20221120/c79b18fab8654348b75c04b25c934de9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201214135504890.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201214135757108.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201214135913713.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwODQ0Nw==,size_16,color_FFFFFF,t_70