MySQL 篇-深入了解多表设计、多表查询

1.0 多表设计概述

        多表设计是指在数据库中将数据分散存储在多个表中的设计方法。这种设计方法通常用于将数据按照不同的实体或属性进行划分，以便更好地组织和管理数据。

        在多表设计中，不同的表之间通常会通过外键来建立关联关系，从而实现数据之间的引用和关联。这种设计方法有助于减少数据冗余、提高数据的一致性和完整性，并且可以更好地支持数据的查询和分析。

        总的来说，为了数据在表中更好的管理，将数据拆分到不同的表中。而表与表之间通过外键来建立联系。

        多表设计的类型主要分为：一对多、一对一、多对多。

        1.1 多表设计 - 一对多

        在数据库设计中，一对多关系指的是一个实体在另一个实体中有多个关联记录的关系。通常使用外键来实现一对多关系。假设我们有两个实体 A 和 B ，A 实体可以有多个关联的 B 实体记录，而B实体只能关联一个 A 实体记录。

外键语法：

• 
  

  -- 创建表时指定
• 
  

  create table 表名(
• 
  

          字段名 数据结构,
• 
  

          ...
• 
  

          [constraint] [外键名] foreign key(外键字段名) references 主表(字段名)
• 
  

      );
• 
  

  
  
• 
  

  
  
• 
  

  -- 建完表后，添加外键
• 
  

  alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);
  
  

        举个例子，部门与员工的关系，一个部门可以有多个员工，而一个员工只能属于一个部门，这就构成了一对多的关系，为了建立部门与员工的关系，则在员工表中添加外键即可。

代码如下：

• 
  

  create table department(
• 
  

      id tinyint unsigned primary key comment '序号',
• 
  

      name varchar(10) not null  comment '部门名称',
• 
  

      last_time datetime not null comment '最后的操作时间'
• 
  

  )comment '部门表';
• 
  

  
  
• 
  

  create table employee(
• 
  

      id tinyint unsigned primary key comment 'id号',
• 
  

      name varchar(10) not null  comment '名字',
• 
  

      department_id tinyint unsigned comment '部门号',
• 
  

      last_time datetime not null comment '最后的操作时间'
• 
  

  )comment '员工表';
• 
  

  
  
• 
  

  -- 添加外键约束
• 
  

  alter table employee add  foreign key (department_id) references department(id);
• 
  

  
  
• 
  

  -- 添加部门表中的数据
• 
  

  insert into department values (1,'学工部',now()),(2,'教研部',now()),(3,'教学部',now()),(4,'后勤部',now());
• 
  

  -- 添加员工表中的数据
• 
  

  insert into employee values (1,'张三',1,now()),(2,'李四',2,now()),(3,'王五',2,now()),(4,'赵六',4,now());
  
  

部门表：

员工表：

        这两个表已经建立了物理联系，通过外键来建立物理上的联系是为了保证数据的一致性和完整性。

        比如，现在要删除部门表中的 '教研部' 数据，代码如下：

• 
  

  
  
• 
  

  delete from department where id = 2;
  
  

执行结果如下：

        由于添加了外键联系，为了确保数据的一致性和完整性，所以影响该操作失败。

而对与删除员工表中的数据则可以删除成功，代码如下：

delete from employee where name = '王五';

执行结果如下：

        物理外键：

       使用 foreign key 定义外键关联另外一张表。但是会影响增、删、改的效率(因为需要检查外键关系)、仅用于单节点数据库，不适用与分布式、集群场景、容易引发数据库的死锁问题、消耗性能。所以我们一般建立表与表之间的逻辑外键联系，而不建立物理外键联系。

        关于在一对多关系中在哪一个表中添加外键：

        简单粗暴的说，在一对多中，代表多的表需要添加外键，一个员工表与一个部门表，显然员工表是代表多的一方，部门表代表少的一方。因为一个部门有很多员工，而一个员工只能属于一个部门。

        1.2 多表设计 - 一对一

        在数据库中，一对一关系是指两个实体之间存在一种一对一的关联关系。这种关系通常通过在两个表之间共享一个相同的主键来实现。

        在任意一方加入外键，关联另外一个的主键，并且设置外键为唯一的 unique 。

        举个例子，用户与身份证信息的关系，一对一关系，用于单表的拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。

代码如下：

• 
  

  create table user(
• 
  

      id tinyint unsigned primary key comment 'id号',
• 
  

      name varchar(10) not null comment '名字',
• 
  

      phone varchar(11) comment '电话号码',
• 
  

      degree varchar(10) comment '学历',
• 
  

      birthday date comment '出生日期'
• 
  

  )comment '用户表';
• 
  

  
  
• 
  

  create table user_id_card(
• 
  

      id_card varchar(18) primary key comment '身份证号码',
• 
  

      issued varchar(10) not null,
• 
  

      fk_id tinyint unsigned,
• 
  

      constraint fk foreign key (fk_id) references user(id)
• 
  

  )comment '用户id表';
• 
  

  
  
• 
  

  -- 添加数据
• 
  

  insert into user values (1,'鹰王','18812340001','初中','1960-11-06');
• 
  

  insert into user values (2,'辐王','18812340002','高中','1961-11-06'),(3,'龙王','18812340003','高中','1962-11-06');
• 
  

  
  
• 
  

  insert into user_id_card values (100000000010000011,'朝阳',1),(100000000010000022,'西阳',2),(100000000010000033,'东阳',3);
  
  

             用户ID表：

用户表：

       通过物理外键已经建立好了两个表的联系了。

        关于在一对一关系中在哪一个表中添加外键：

        在一对一中，任意一个表中都可以添加外键，任选一个表即可。

        1.3 多表设计 - 多对多

        在多表设计中，多对多关系通常需要使用一个中间表来实现。这种中间表包含两个外键，分别指向参与关系的两个表。这样就可以实现多对多关系的表示。

        举个例子，学生与课程的关系，一个学生可以选修多门课程，一门课程也可以供多个学生选择。

代码如下：

• 
  

  create table student(
• 
  

      id tinyint primary key comment 'id号',
• 
  

      name varchar(10) not null comment '名字',
• 
  

      no varchar(20) comment '学号'
• 
  

  )comment '学生表';
• 
  

  
  
• 
  

  create table course(
• 
  

      id tinyint primary key comment 'id号',
• 
  

      name varchar(10) not null unique comment '课程名'
• 
  

  )comment '课程表';
• 
  

  
  
• 
  

  create table course_student(
• 
  

      id tinyint primary key comment 'id号',
• 
  

      student_id tinyint comment '外键id号',
• 
  

      course_id tinyint comment '外键id号',
• 
  

      constraint fk_s foreign key (student_id) references student(id),
• 
  

      constraint fk_c foreign key (course_id) references course(id)
• 
  

  )comment '学生与课程的中间表';
• 
  

  
  
• 
  

  -- 添加数据
• 
  

  insert into student values (1,'张三',2002350101),(2,'李四',2002350102),(3,'王五',2002350103);
• 
  

  insert into course values (1,'Java'),(2,'PHP'),(3,'MySQL');
• 
  

  insert into course_student values (1,1,1),(2,1,2),(3,1,3),(4,2,1),(5,2,3),(6,3,2);
  
  

                                课程表：

                                中间表：

                                学生表：

        小结：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

        2.0 多表查询概述

        多表查询是指在数据库中同时查询多个表的数据的操作。通过多表查询，可以根据不同表之间的关联关系，将数据连接起来。

        多表查询的方式：内连接、外连接、子查询。

        2.1 多表查询 - 内连接

        内连接是一种多表查询的方式，用于检索两个或多个表中满足连接条件的数据。内连接只返回满足连接条件的行，即两个表中的数据必须在连接条件下匹配才会被检索出来。

内连接语法：

• 
  

  -- 隐式内连接
• 
  

  select 字段列表 from 表1,表2 where 条件 ...;
• 
  

  
  
• 
  

  
  
• 
  

  -- 显示内连接
• 
  

  select 字段列表 from 表1 [inner] join 表2 on 链接条件 ...;
  
  

        显示内连接更推荐使用，因为它提供了更清晰的语法结构和更好的可读性。隐式内连接虽然在一些情况可以简化语句，但是容易造成混淆和错误，不易维护。但是两者的效果是一样的。

        2.2 多表查询 - 外连接

        在数据库中，多表查询可以通过外连接来实现。外连接是一种连接操作，用于检索两个或多个表中的数据，即使其中一个表中的数据在另一个表中没有匹配项也可以检索出来。在外连接中，常见的类型有左外连接、右外连接。左外连接会返回左表中的所有数据，即使右表中没有匹配项；右外连接会返回右表中的所有数据，即使左表中没有匹配项。

外连接语法：

• 
  

  -- 左外链接
• 
  

  select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;
• 
  

  
  
• 
  

  -- 右外链接
• 
  

  select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;
  
  

        左外链接与右外链接可以相互转换的，一般习惯用左外链接方式来查询多表。

        2.3 多表查询 - 子查询

        在数据库中，多表查询是指从多个表中检索数据的操作。而子查询是指在一个查询中嵌套另一个查询的操作。所以子查询也称为嵌套查询。

可以具体分为：

        标量子查询：子查询返回的结果为单个值。

        列子查询：子查询返回的结果为一列。

        行子查询：子查询返回的结果为一行。

        表子查询：子查询返回的结果为多行多列。

        返回的多行多列就是一个表，常作为临时表，常用的操作符：in 。