【SQL】表的三种关系及实现方式

 #博学谷IT学习技术支持 

---

前言

个人学习笔记，欢迎指出任何错误，留言意见。

我的文章都将随着个人学习深入而定期更新。

以最简洁的风格，记录概念和方法

---

目录

               上篇资料链接https://blog.youkuaiyun.com/weixin_46784189/article/details/127146464?spm=1001.2014.3001.5501

                  2.2 表关系(一对多)

2.3 表关系(多对多)

2.4 表关系(一对一)

---

2.2 表关系(一对多)

• 一对多

  • 如：部门 和 员工

  • 一个部门对应多个员工，一个员工对应一个部门。

• 实现方式

  ==在多的一方建立外键，指向一的一方的主键==

• 案例

  我们还是以 员工表 和 部门表 举例:

• 经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）：

• 建表语句如下：

  -- 删除表
  DROP TABLE IF EXISTS tb_emp;
  DROP TABLE IF EXISTS tb_dept;
  ​
  -- 部门表
  CREATE TABLE tb_dept(
      id int primary key auto_increment,
      dep_name varchar(20),
      addr varchar(20)
  );
  -- 员工表 
  CREATE TABLE tb_emp(
      id int primary key auto_increment,
      name varchar(20),
      age int,
      dep_id int,
  ​
      -- 添加外键 dep_id,关联 dept 表的id主键
      CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)   
  );

  查看表结构模型图：

• 

2.3 表关系(多对多)

• 多对多

  • 如：商品 和 订单

  • 一个商品对应多个订单，一个订单包含多个商品

• 实现方式

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

• 案例

  我们以 订单表 和 商品表 举例：

• 

  经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键：

• 

  建表语句如下：

  -- 删除表
  DROP TABLE IF EXISTS tb_order_goods;
  DROP TABLE IF EXISTS tb_order;
  DROP TABLE IF EXISTS tb_goods;
  ​
  -- 订单表
  CREATE TABLE tb_order(
      id int primary key auto_increment,
      payment double(10,2),
      payment_type TINYINT,
      status TINYINT
  );
  ​
  -- 商品表
  CREATE TABLE tb_goods(
      id int primary key auto_increment,
      title varchar(100),
      price double(10,2)
  );
  ​
  -- 订单商品中间表
  CREATE TABLE tb_order_goods(
      id int primary key auto_increment,
      order_id int,
      goods_id int,
      count int
  );
  ​
  -- 建完表后，添加外键
  alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
  alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

  查看表结构模型图：

• 

2.4 表关系(一对一)

• 一对一

  • 如：用户 和 用户详情

  • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

• 实现方式

  ==在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)==

• 案例

  我们以 用户表 举例：

• 

  而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。

建表语句如下：

create table tb_user_desc (
    id int primary key auto_increment,
    city varchar(20),
    edu varchar(10),
    income int,
    status char(2),
    des varchar(100)
);
​
create table tb_user (
    id int primary key auto_increment,
    photo varchar(100),
    nickname varchar(50),
    age int,
    gender char(1),
    desc_id int unique,
    -- 添加外键
    CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)    
);

查看表结构模型图：