任先生的笔记库

# 一、表设计（合）

# 1、表 与 表

  一个表代表一个实体，实体之间都有关联关系的

  • 一对一关系（优化访问效率）
  • 一对多关系（明确主从关系）
  • 多对多关系（体现实体联系）

  例如：设计一个简单新闻管理系统的数据库

答案

  • 新闻信息表：id、标题、内容、发布时间、作者 id（作者表主属性）、分类 id（分类表主属性）、阅读量、推荐数

  • 作者表：id、作者名字、作者来源 id（来源表）

  • 来源表：id、来源名字、来源描述

  • 分类表：id、分类名字、分类级别（父分类 id）

  • 评论表：id、评论人 id（评论表）、评论时间、评论内容（不回复）

# 1、一对一

TIP

  一张表中的一条记录与另外一张表中有且仅有一条记录有关系

  一对一关系通常是用来将一张原本就是一体的表拆分成两张表

产生场景

1、一张表的数据字段较多且数据量较大

2、表中有部分字段使用频次较高，而另一部分较少使用

3、将常用字段和不常用字段拆分成两张表，使用同样的主键对应

1、学生信息表

id 姓名 学号 密码 邮箱 性别 年龄 身高 体重 籍贯 政治面貌
1 张飞 20631727322 552372 [email protected] 男 20 178 160 蜀 农民
2 武则天 20631727323 344231 [email protected] 女 21 168 110 唐 党员

2、一对一关系设计

常用表

id 姓名 学号 密码 邮箱
1 张飞 20631727322 552372 [email protected]
2 武则天 20631727323 344231 [email protected]

不常用表

id 性别 年龄 身高 体重 籍贯 政治面貌
1 男 20 178 160 蜀 农民
2 女 21 168 110 唐 党员

# 2、一对多 ✨

TIP

  一张表中的一条记录与另外一张表的多条记录对应，反过来另外一张表的多条记录只能对应当前表的一条记录

1、确定实体间的关系为一对多（多对一）关系

2、在多表中增加一个字段记录一表中对应的主属性

一个 建筑（building）可以有多个 房间（rooms）

SQL 代码 
CREATE TABLE IF NOT EXISTS buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
) COMMENT = '建筑表';


INSERT INTO buildings(building_name,address)
VALUES('海南大厦','海口市国兴大道1234号'),
      ('万达水城','海口市大同路1200号'),
      ('中山大厦', '海口市骑楼街56号');

CREATE TABLE IF NOT EXISTS rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT,
    FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '房间表';

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Lakeside Cottage',2);
ER-图表

建筑表 （1）

building_no building_name address
1 海南大厦 海口市国兴大道 1234 号
2 万达水城 海口市大同路 1200 号
3 中山大厦 海口市骑楼街 56 号

房间表 （n）

room_no room_name building_no
1 Amazon 1
2 War Room 1
3 Office of CEO 1
4 Marketing 2
5 Lakeside Cottage 2

# 3、多对多 ✨

TIP

  一张表中的一条记录对应另外一个表中多条记录，反过来一样

1、确定实体间的关系为多对多关系

2、设计中间表，记录两张表的对应关系

一个学生（student）可以选择多个课程（courses）；一个课程（course）可以被多个学生（students）选中

SQL 代码 
-- 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT
) COMMENT = '学生表';

INSERT INTO `students` (name, age) VALUES('why', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);

-- 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    price DOUBLE NOT NULL
) COMMENT = '课程表';

INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);
INSERT INTO `courses` (name, price) VALUES ('物理', 100);

-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';

# why 选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
# lilei选修了 语文和数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);
ER-图表

学生表 （n）

id name age
1 why 18
2 tom 22
3 lilei 25
4 lucy 16
5 lily 20
5 Lakeside Cottage 2

课程表 （n）

id name price
1 英语 100
2 语文 666
3 数学 888
4 历史 80
5 物理 100

学生-课程关系表

拓展：👏 联合主键
  • id 主键
-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';
  • 联合主键
-- 创建学生-课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	student_id INT NOT NULL,
	course_id INT NOT NULL,
    PRIMARY KEY(student_id, course_id),
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT = '学生-课程关系表';
id student_id course_id
1 1 1
2 1 3
3 3 2
4 3 3
5 3 4

# 二、设计范式（拆）

# 1、范式概述

  范式是离散数学里的概念，目标是在满足组织和存储的前提下使数据结构冗余最小化，范式级别越高，表的级别就越标准

简要

1、范式是一种数学理论，在关系型数据库上用来==减少数据冗余==

2、满足的范式越多，越符合高标准表设计

3、范式一共有 6 层，但是数据库的设计通常只要求满足 3 层即可

  目前数据库应用到的范式有以下几层

  • 第一范式：1NF
  • 第二范式：2NF
  • 第三范式：3NF
  • 逆规范化

# 2、使用示例

  一张员工表

工号 姓名 部门 入职时间
0001 杨戬 武装部 0001-01-01
0002 李白 书院部 1500-12-12

  每个员工都是与部门挂钩的，但是部门不可能很多，所以上述表中会有很多数据重复，此时应该将部门单独维护出来，减少数据冗余

部门编号 部门名称
1 武装部
2 书院部
工号 姓名 部门编号 入职时间
0001 杨戬 1 0001-01-01
0002 李白 2 1500-12-12

# 3、第一范式 1NF

第一范式

  1NF，数据字段设计时必须满足原子性

  1NF 要求字段数据是不需要拆分就可以直接应用，即要求将数据拆分到最小单位（使用），然后设计成字段

如果数据使用的时候需要进行拆分那么就违背 1NF

  • 步骤
1、设计的字段是否在使用的时候还需要再拆分？
2、将数据拆分到最小单位（使用），然后设计成字段
3、满足1NF

1、设计一张学生选修课成绩表

学生 性别 课程 教室 成绩 学习时间
张三 男 PHP 101 100 2 月 1 日,2 月 28 日
李四 女 Java 102 90 3 月 1 日,3 月 31 日
张三 男 Java 102 95 3 月 1 日,3 月 31 日

2、满足 1NF 的设计：字段颗粒度应用层最小（不需要拆分）✨

学生 性别 课程 教室 成绩 开始时间 结束时间
张三 男 PHP 101 100 2 月 1 日 2 月 28 日
李四 女 Java 102 90 3 月 1 日 3 月 31 日
张三 男 Java 102 95 3 月 1 日 3 月 31 日

# 4、第二范式 2NF

第二范式

  2NF，字段设计不能存在部分依赖

  部分依赖：表存在复合主键，并且有的字段不是依赖整个主键，而只是依赖主键中的一部分

解决方案

  让所有非主属性都依赖一个候选关键字

  • 最简单方式：取消复合主键（一般选用逻辑主键替代，但是本质依然是复合主键做主），所有非主属性都依赖主属性（逻辑主键）
  • 正确方式：将部分依赖关系独立成表
1、表中是否存在复合主键？
2、其他字段是否存在依赖主键中的一部分？
3、如果存在部分依赖，将部分依赖的关系独立拆分成表
4、满足 2NF

1、学生成绩表

学生（P） 性别 课程（P） 教室 成绩 开始时间 结束时间
张三 男 PHP 101 100 2 月 1 日 2 月 28 日
李四 女 Java 102 90 3 月 1 日 3 月 31 日
张三 男 Java 102 95 3 月 1 日 3 月 31 日
  • 成绩是由学生和课程决定的，是完全依赖主属性（复合主键）
  • 性别只依赖学生（部分依赖）
  • 教室、开始时间和结束时间依赖课程（部分依赖）

2、解决方案：将学生信息维护到一张表，课程信息维护到一张表，成绩表取两个表的主属性即可

学生表

Stu_id（P） 姓名 性别
1 张三 男
2 李四 女
  • Stu_id 是姓名的代指属性（逻辑主键）
  • 性别只依赖姓名（主属性）

课程表

Class_id（P） 课程 教室 开始时间 结束时间
1 PHP 101 2 月 1 日 2 月 28 日
2 Java 102 3 月 1 日 3 月 31 日
  • Class_id 是课程的代指属性（逻辑主键）
  • 教室、开始时间和结束时间都依赖课程（主属性）

成绩表

Stu_id（P） Class_id（P） 成绩
1 1 100
2 2 90
1 2 95
  • Stu_id 和 Class_id 共同组成主属性

# 5、第三范式 3NF

第二范式

  3NF，字段设计不能存在传递依赖

  传递依赖：字段某个非主属性不直接依赖主属性，而是通过依赖某个其他非主属性而传递到主属性之上

解决方案

  让依赖非主属性的字段与依赖字段独立成表

  • 步骤
1、确定表中的所有字段都是🚩依赖主属性的
2、如果存在不直接依赖主属性，而是通过依赖其他属性产生依赖的，形成独立的表
3、满足 3NF

1、学生表：包括所在系信息

学号（P） 姓名 专业编号 专业名字
205821181 张三 0001001 软件工程
205822081 李四 0001002 土木工程
  • 专业名字 ----> 专业编号 和 姓名 ----> 学号
  • 专业名字 间接依赖 学号：传递依赖

随着学生增加，专业名字 会出现大量数据冗余

2、解决方案：将存储传递依赖部分的字段（非主属性）独立成表，然后在需要使用相关信息的时候，引入即可

专业表

专业编号（P） 专业名字
0001001 软件工程
0001002 土木工程

学生表

学号（P） 姓名 专业编号
205821181 张三 0001001
205822081 李四 0001002

# 6、逆规范化

逆规范化

  为了提升数据查询的效率而刻意违背范式的规则(增加表之间的关联)

1、逆规范化只有在数据量大，查询效率低下的时候为了提升查询效率而牺牲磁盘空间的一种做法

2、逆规范化后数据表的设计必然是不完全符合范式要求的（2NF/3NF）

学号（P） 学生姓名 课程号（P） 课程名字 成绩
1 张三 1 PHP 100
1 张三 2 Java 90
更新于 : 8/7/2024, 2:16:31 PM

← 数据查询-DQL（下） →

Apache License 2.0 | Copyright © 2022 lencamo All Rights Reserved | 蜀ICP备2022012538号-2