数据库多表设计和多表查询

一、多表设计

1、表设计分析介绍

在项目中，需要对项目的业务模块进行分析，需要知道当前需要几张表来描述业务（功能）中的数据，这些表中的数据之间是否存在一定的关联性，需要考虑多张表之间是什么关系？

2、表与表之间的关系

多张表之间存在的关系：

• 一对一：
• 一对多：
• 多对多：
• 自关联：

要将表之间这些关系维护清楚，必须使用表之间的约束条件。

约束条件：

• 单表：主键约束、唯一约束、非空约束
• 多表：外键约束（互联网企业表之间的外键约束不会进行设置，但是会有相关的列表示外键，防止级联删除操作）
  • 外键：在一张表中增加一列，数据引用的是另外一张表的主键值。

3、一对一的表设计

例如：

• 一夫一妻：一个丈夫对应要给妻子。
• 个人与身份证：每个人都唯一的一个身份证
  

一对一建表的原则：

在任意一张表中增加一列，引用另外一张的主键即可。

建立外键列名的时候，一般是：表名_主键列名

CREATE TABLE person(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(50) NOT NULL,
 sex CHAR(1),
 birthday DATE,
 address VARCHAR(100)
);

CREATE TABLE idcard(
 id INT PRIMARY KEY AUTO_INCREMENT,
 cardNo VARCHAR(18) NOT NULL UNIQUE,
 createTime DATE,
 -- 外键，引用的Person表中的主键，
 -- 它的类型必须和person表中的主键类型一致
 -- 一对一的外键需要加唯一约束
 pid INT NOT NULL UNIQUE, 
 FOREIGN KEY(pid) REFERENCES person(id)
);

4、一对多的表设计

例如：

• 程序员和项目：一个程序员可能对应不同的项目任务。
• 老师和课程：一个老师对应多个课程。
• 用户和收获地址：一个用户可以添加多个收获地址。
  

一对多建表的原则：

在多的表中添加一列作为外键，引用一的表中的主键

-- 一对多的建表
CREATE TABLE users(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(50),
 PASSWORD VARCHAR(32),
 registerTime DATETIME,
 LEVEL INT
);

CREATE TABLE receAddr(
 id INT PRIMARY KEY AUTO_INCREMENT,
 receName VARCHAR(50),
 receAddr VARCHAR(