多表设计和关联查询

多表设计

为什么要使用多表？

因为需要存储不同的信息

为什么需要多表关联？

因为设计多张表的时候，减少了数据冗余，但是数据与数据之间有一定的关系，这时就需要多表关联。

表设计原则

数据库设计范式

​ 为了建立冗余小、结构合理的数据库，设计数据库时需要遵循一定的规则。在关系型数据库中这种规则被称为范式。范式是符合某一种设计要求的总结。

目前关系型数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯科德范式（BCNF）、第四范式（4NF）、第五范式（5NF）。

1. 第一范式：确保每一列保持原子性

   第一范式是最基本的范式。意思就是说表中的每列都不可再分，如：

   编号	姓名	联系方式
   001	张三	15199996666
   002	王五	2766669999
   003	李四	1666666666

   三个人填的都是联系方式，问题来了：你知道他们填的是手机号？QQ号？又或者是微信号？确定起来非常麻烦。因此需要确保每列的原子性。可以将联系方式改为具体的手机号或者其他的。

2. 第二范式：要有主键，其他字段都依赖于主键

   • 主键：能唯一标识一条记录且不能为空、不能重复的一组属性。
   • 主键的作用：①唯一地标识一行数据；②作为可以被外键引用的对象。
   • 其他字段为什么要依赖于主键？——>因为主键是唯一标识，不依赖主键就找不到他们。更重要的是其他字段组成的这行记录和主键所表示的是同一个东西，而主键是惟一的，其他的只需要依赖主键，也就成惟一的了。

3. 第三范式：消除传递依赖，即“消除冗余”

   意思就是：不同的表只存储一类信息，然后通过主键和外键关联。

   如一个订单表：

   订单编号	数量	商品编号	商品名称	单价	订单金额
   0001	2	0202	手机	2000	4000

   在这里知道商品编号后就可以知道商品名称和单价

   订单编号	数量	订单金额	商品编号
   0001	2	4000	0202

   商品编号	商品名称	单价
   0202	手机	2000

一般来说，数据库只需要满足第三范式就可以了。

什么是外键，外键的作用

一个表的外键是另一个表的主键，外键可以重复，也可以是空值。外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。如：A表中的一个字段，是B表的主键，那么这个字段就是A表的一个外键。注意：一张表的主键只有一个，外键可以有多个。

在多表关联时，有两种关联方式：

1. 弱关联：表与表之间没有任何约束关系的

2. 强关联：有外键约束

添加外键

/*创建表时添加外键*/
CREATE TABLE score (
	cid INT PRIMARY KEY AUTO_INCREMENT ,
	score INT,
	sid INT,
	CONSTRAINT FK_students_score_id FOREIGN KEY (sid) REFERENCES students(id)
)
/*添加外键语句：
	CONSTRAINT 约束名 foreign key(本表的外键名) references 外表(外表中的主键)
	约束名规则：FK_外表_主表_外表的主键列
	
	注意：外表称为被参照表，
		 主表称为参照表。
	*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14

/*表已经创建时添加外键*/
-- 添加列
ALTER TABLE  score ADD COLUMN sid INT
-- 添加外键约束
ALTER TABLE score ADD CONSTRAINT FK_students_score_id FOREIGN KEY (sid) REFERENCES students(id)
1
2
3
4
5

当参照表中没有记录时不能将记录添加到参照表中

当参照表中有对应记录时，不能在被参照表中删除

删除被参照表之前先删除参照表

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

发生原因：没有有效的连接条件。只需要添加有效连接条件。

连接条件的分类

内连接

分为等值连接、非等值连接、自然连接

就是把满足条件的两张表中的交集数据查询出来

语法：Select 结果 from 表1，表2 where 表1.column1 = 表2.column2

外连接

外左连接（left join）：

select 结果 from 表1 left join 表2 on 表1.column1 = 表2.column2

外有链接（right join）：

select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2