【Mysql】多表关系设计

多表关系设计

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们一起学习一下多表关系设计方面的知识
一对多关系（多见）
一对多关系（1:n）
•例如：班级和学生，部门和员工，客户和订单，分类和商品

一对多建表原则
•在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

** 多对多关系(常见)**
多对多（m:n）
•例如：老师和学生，学生和课程，用户和角色
多对多关系建表原则
•需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主键。

一对一关系(了解)
一对一（1:1）
•在实际的开发中应用不多.因为一对一可以创建成一张表。
一对一建表原则
•外键唯一 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

设计 省&市表
1)分析: 省和市之间的关系是 一对多关系,一个省包含多个市

SQL实现

-- 创建省表 (主表,注意: 一定要添加主键约束)
CREATE TABLE province(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20)
);

-- 创建市表 (从表,注意: 外键类型一定要与主表主键一致)
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20),
    pid INT,
    -- 添加外键约束
    CONSTRAINT pro_city_fk FOREIGN KEY (pid) REFERENCES province(id)
);

查看表关系

设计 演员与角色表
1)分析: 演员与角色 是多对多关系, 一个演员可以饰演多个角色, 一个角色同样可以被不同的演员扮演

2. SQL 实现

-- 创建演员表
CREATE TABLE actor(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

-- 创建角色表
CREATE TABLE role(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
 
-- 创建中间表
CREATE TABLE actor_role(
    -- 中间表自己的主键
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 指向actor 表的外键
    aid INT,
    -- 指向role 表的外键
    rid INT 
);

2. 添加外键约束

-- 为中间表的aid字段,添加外键约束 指向演员表的主键
ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(id);

-- 为中间表的rid字段, 添加外键约束 指向角色表的主键
ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);

3. 查看表关系