SYSTEM
多表设计
src/content/notes/system/多表设计.md 多表设计
一对一
多表:有多张数据表,而表与表之间有一定的关联关系,通过外键约束实现,分为一对一、一对多、多对多三类
举例:人和身份证
实现原则:在任意一个表建立外键,去关联另外一个表的主键
-- 创建person表
CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
NAME VARCHAR(20) -- 姓名
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');
-- 创建card表
CREATE TABLE card(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
number VARCHAR(20) UNIQUE NOT NULL, -- 身份证号
pid INT UNIQUE, -- 外键列
CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

一对多
举例:用户和订单、商品分类和商品
实现原则:在多的一方,建立外键约束,来关联一的一方主键
-- 创建user表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
NAME VARCHAR(20) -- 姓名
);
-- 添加数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
-- 创建orderlist表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
number VARCHAR(20), -- 订单编号
uid INT, -- 外键列
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2);

多对多
举例:学生和课程。一个学生可以选择多个课程,一个课程也可以被多个学生选择
实现原则:借助第三张表中间表,中间表至少包含两个列,这两个列作为中间表的外键,分别关联两张表的主键
-- 创建student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
NAME VARCHAR(20) -- 学生姓名
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
-- 创建course表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
NAME VARCHAR(10) -- 课程名称
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');
-- 创建中间表
CREATE TABLE stu_course(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id
sid INT, -- 用于和student表中的id进行外键关联
cid INT, -- 用于和course表中的id进行外键关联
CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id) -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
