概述

实际开发中, 一个项目通常需要很多张表才能完成. 例如: 一个商场项目就需要分类表 (category), 商品表 (products), 订单表(orders) 等多张表. 且这些表的数据之间存在一定的关系. 接下来我们将在单表的基础上, 一起学习多表方面的知识.

表与表之间的关系

一对多关系

常见实例: 客户和订单, 分类和商品, 部门和员工.

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

多对多的关系

常见的实例: 学生和课程, 用户和角色.

多对多关系建表原则: 需要创建第三张表, 中间表中至少有两个字段, 这两个字段分别作为外键指向各自一方的主键.

一对一关系

一对一关系在实际的开发中应用的不多. 因为一对一可以创建成一张表.

两种建表原则:

• 外键唯一: 主表的主键和从表的外键 (唯一), 形成主外键关系, 外键唯一
• 外键是主键: 主表的主键和从表的主键, 形成主外键关系

外键约束

现在我们有两张表 “分类表” 和 “商品表”, 为了表明商品属于哪个分类, 通常情况下, 我们将在商品表上添加一列用于存放分类 cid 的信息, 此列称为: 外键.


此时 “分类表 category” 称为主表, “cid” 我们称为主键. “商品表 products” 称为从表, “category_id” 称为外键. 我们通过主表的主键和从表的外键来描述主外键关系, 呈现就是一对多关系.

外键特点

• 从表外键的值是对主表主键的引用
• 从表外键类型, 必须与主表主键类型一致

声明外键约束

语法：
alter table 从表 add [constraint 外键名称] foreign key 从表名(从表外键字段名) references 主表名(主表的主键);

[外键名称]用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称

  

 

  
1
  
2
  
3
  
4
  
5
 

注: 使用外键的目的: 保证数据完整性.

一对多操作

分析

• category 分类表, 为一方, 也就是主表, 必须提供主键 cid
• products 商品表, 为多方, 也就是从表, 必须提供外键 category_id

实现: 分类和商品

#创建分类表
create table category(
    cid varchar(32) PRIMARY KEY ,
    cname varchar(100) -- 分类名称
);

# 商品表
CREATE TABLE `products` (
    `pid` varchar(32) PRIMARY KEY ,
    `name` VARCHAR(40) ,
    `price` DOUBLE
);

#添加外键字段
alter table products add column category_id varchar(32);

#添加约束
alter table products add constraint product_fk foreign key (category_id) references
category (cid);

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
 

操作

#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');

#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
 

多对多

分析

• 商品和订单多对多关系, 将拆分成两个一对多
• products 商品表, 为其中一个一对多的主表, 需要提供主键 pid
• orders 订单表, 为另一个一对多的主表, 需要提供主键 oid
• orderitem 中间表, 为梁歪添加的第三张表, 需要提供两个外键 oid 和 pid

实现: 订单和商品

#商品表[已存在]

#订单表
create table `orders`(
`oid` varchar(32) PRIMARY KEY ,
`totalprice` double #总计
);

#订单项表
create table orderitem(
oid varchar(50),-- 订单id
pid varchar(50)-- 商品id
);

#订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references
orders(oid);

#商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid)
references products(pid);

#联合主键（可省略）
alter table `orderitem` add primary key (oid,pid);

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
 

操作

#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');

#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');

#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES('p001','x001');
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');

#4删除中间表的数据
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';

#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES('p002','x003');

#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 'p001';

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
 

文章来源: iamarookie.blog.csdn.net，作者：我是小白呀，版权归原作者所有，如需转载，请联系作者。

原文链接：iamarookie.blog.csdn.net/article/details/112727661