数据库多表设计和多表查询
一、多表设计
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(