MySQL数据分析进阶（十二）设计数据库——PART1

※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）

【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2

目录：

第十二章：设计数据库——PART1

1、DESIGNING DATABASES——设计数据库

2、DATA MODELLING——数据建模

3、CONCEPTUAL MODELS——概念模型

4、LOGICAL MODELS——逻辑模型

5、PHYSICAL MODELS——实体模型

6、PRIMARY KEYS——主键

7、FOREIGN KEYS——外键

8、FOREIGN KEY CONSTRAINTS——外键约束

---

第十二章：设计数据库——PART1

1、DESIGNING DATABASES——设计数据库

❗从头开始设计一个新的数据库或者向现有的数据库添加新表

设计结构良好的数据库，对能否成功设计应用程序起到关键作用

在开始时进行一些规划，需要花时间

🔺如果合理设计数据库，可以轻松得围绕它进行开发，来支持新的业务需求；轻松地查询提取有用的信息，且查询会快速执行

🔺糟糕的设计数据库需要大量的维护，并且维护成本会随着时间的推移增加，最终无法开展新的业务需求

2、DATA MODELLING——数据建模

为要存储在数据库中的数据创建模型的过程

Understand the requirements（理解和分析业务需求）

越了解业务问题，就越能更好找到解决方案

在考虑表和列的问题之前，需要充分了解业务需求，要多和业务参与方、领域专家甚至终端用户谈谈

查看现有表单、文档、应用程序、电子表格、数据库，几乎一切事关你要解决的问题领域的内容

Build a Conceptual Model（构建概念模型）

识别业务中的实体、事物或概念以及它们之间的关系

只是我们所用概念的一种视觉表示，用于与涉众交流，确保进度一致

Build a Logical Model（构建逻辑模型）

逻辑模型：独立于数据库技术的抽象数据模型，只显示你需要的表和列

概念模型进一步完善，生成一个数据结构或数据模型用于存储数据

Build a Physical Model（构建实体模型）

实体模型是逻辑模型通过特定数据库技术的实现

在此模型中，需要具备由数据库技术支持的确切的数据类型，列默认值，不管是否允许空值

表主键、其他对象（视图、存储过程、触发器）

实体数据模型对于MySQL这样的数据库技术来说非常特殊的

3、CONCEPTUAL MODELS——概念模型

❗如果想建立一个销售在线课程的网站，让人们可以注册或登记一个或多个课程，一个课程可以有一个或多个标签（前端、后端）

为了给这个网站创建数据库，首先需要创建一个概念模型来表示业务中的实体、事物或概念以及它们之间的关系（人、事件、位置等等）

①这里有什么概念和实体：学生、课程

这些是这个领域里需要了解的概念

②可视化方式观察这些实体及他们之间的关系

以下两种方法都可以视觉直观地展示概念

Entity Relationship(ER)-实体关系图：通常用于数据建模

Unified Modelling Languages(UML)-标准建模语言图：范围远远超出了数据建模

创建实体关系图的工具

Windows：Microsoft Visio、draw.io、LucidCharts

以下使用 draw.io 制作

后期可能需要添加新属性或重命名或删除其中地一些属性

数据建模是个迭代过程，很难在第一次尝试中就找到完美的设计

所以会在不断地业务需求和模型之间来回切换，并不断改进它们

以上就是一个数据模型，能让我们对业务领域和领域设计内容由一个高度概览

至此还没有对每个属性类型的细节做出说明

我们即不知道也不关心未来会使用什么数据库管理系统完成这个模型，现在仅仅是一个概念模型，用它来和商业参与方交接，所以我们能处于一个层次，也能让对方理解自己的表达内容

4、LOGICAL MODELS——逻辑模型

概念模型进一步完善，生成一个数据结构或数据模型用于存储数据

独立于数据库技术的抽象数据模型，显示实体及关系架构，比概念模型更细节

当我们选择一个MySQL这样的数据库管理系统时，改进逻辑模型，来操作这个特定数据库管理系统

①指定每个属性的类型（字符串、整数、浮点型、日期）

name（string）字符串

varchar是一种实现细节，是MySQL的一种数据类型，但这个逻辑模型独立于数据技术

🔺通常把一个属性（姓名）分类多个属性（名字和姓氏）是很有帮助的，这样就可以根据学生的名字或者姓氏进行查询或排序

相反，如果使用单一属性存储全名，之后就不得不经历提取名字或姓氏这一麻烦过程，查询就变得复杂和费事

地址同理：街道、城市、邮政编码、国家

②指定实体之间关系的类型有三种

其他类型都可算作这三种类型的变体

可以选择不同的形状，drow,io支持很多不同的可视化语言

其中一些图标来源于UML（统一建模语言）

在概念层面一点不重要，只需要在实体之间安置一条直线，类型不重要

③如果需要知道某位学生注册课程日期，应该把数据属性放在哪里

× 学生：学生可能选修好几门课程，就需要为每门课程设置一个不同属性

× 课程：放在课程实体也不合理，因为每个学生注册日期都不一样，所以注册日期不能算是课程的一种属性，而是注册的一种属性（是一种学生和课程之间关系的属性）

√注册（新增）：学生可以注册多个课程，但每项注册都是针对某一特定学生，所以学生和注册之间是一种一对多的关系

一门课程也可以被注册很多次，但每次注册都针对的是某一特定课程

假设和专家聊过后发现课程定价也会随时间变动，所以每个学生可能会以不同价格购入一门课

应该把价格放在注册实体中，就会变成学生在注册一门课程的价格

在当前层面我们仍不用在意它在MySQL或其他DMBS中对应数据类型名称是什么

在MySQL，会使用decimal类型，在别的DBMS中可能会有其他类型

这在逻辑模型中不打紧，只是一个抽象的数据结构

④属性补充完整

概念模型VS逻辑模型

概念模型：并不能真正为我们提供存储数据的结构，只代表业务实体及其关系，用它来帮助理解问题域，以便和域专家交流

逻辑模型：为我们的概念模型增加了很多细节，几乎了解什么结构或什么表需要用来存储数据，这里用到的实体，最终会以表格的形式出现在我们的数据库中

5、PHYSICAL MODELS——实体模型

实体模型是逻辑模型通过特定数据库技术的实现

使用逻辑数据模型并在MySQL中创建一个实体数据模型

EER（Enhance Entity Relationship）增强实体关系，可以创建实体关系图

①默认情况下，这个数据模型假设了一个名为mydb的数据库

②在此图中可以添加表和视图

习惯表名使用复述；因为表是多个实体（student）的容器

（表的单复数保持一致性，并且不要轻易打破原有的规则）

③添加列及其属性

效果：

实体模型和逻辑模型之间的区别：

在当前层面，在这个实体模型上确定了MySQL里的确切类型，也知道某列是否允许空值，还可以设置默认值

练习：

接着在这个实体模型中添加之前在逻辑模型中定义好的其他实体

先不管它们之间的关系，只用添加表和列就行

很多数据建模人员为列名添加表明前缀

（enrollment_date、enrollment_price）

其实没有必要，这会让代码看着不清爽

date是注册的一种属性，而不是注册日期

6、PRIMARY KEYS——主键

主键：唯一标识给定表里每条记录的列

用什么列来唯一识别每个学生——引入新列id列

设置主键并放在首列

7、FOREIGN KEYS——外键

students、enrollments之间的关系

一对多，一个学生可以注册很多个

①添加关系箭头：关系的一端称为母表或主键表，另一端称为子表或外键表

students为母表或主键表，courses为子表或外键表（不能再没有学生的情况下进行注册）

MySQL工作台添加了新的一列，students_students_id（可修改）

说明这列引用了students表中的student_id，就可知道是谁注册的

每次在enrollments插入记录，列中存储那位学生的id

students_id左侧由黄钻，因为它是表中的外键

外键是在一张表中引用了另一张表主键的那列

（再次明确，实体模型比逻辑模型包含了更多细节，在逻辑层不需要管主键、外键问题）

②在enrollments表中添加一个主键：

使用student_id和courses_id的组合，为唯一标识每次注册，复合主键

引入一个注册表id的新列，设置为自动递增

🔺是否要设置成复合主键根据实际情况来判断，无硬性规定

复合主键的好处：防止意外为同一个学生重复注册同一门课，因为表的主键是不能重复，必须是唯一的，每个主键唯一识别了给定表中的每条记录（防止不良数据被插入到表中）

复合主键的问题：如果未来出现别的表了，注册表和那张之间会存在一种关系，而这两个键会需要在那个表中作为外键重复出现

回到本案例中：

当学生表和注册表添加一个关系时，MySQL自动将students_id列添加为了外键，如果以后再在这添加了另一张表，并在两个表中间建立了母子关系，注册表两个键必须在那张新表中重复

好坏取决数据量、新表是否还有另一张子表

另外如果引入了一个新列，比如enrollment_id，就不会有以上问题，如果以后出现新表、就建立关系、只需要重复enrollment_id（只重复了一个值，重复了一个整数，而不是两个整数）

目前看来，暂时不会出现添加新表，所以还是可以用复合主键的，直接就能利用好处防止不良数据被插入到表中

（后期如果出现子表之类的，再回来重新审视这套设计，写变更脚本来更新表设计，no big deal）

8、FOREIGN KEY CONSTRAINTS——外键约束

需要为外键设置约束，本质可以保护数据不受损坏

①修改外键名称

有时候MySQL会自动在结尾加上1或2，防止名字与数据库中的另外一个外键出现冲突

因为所有外键都有这个名字，而这个名字必须是数据库中唯一的

（明确目前只有三个表，也没有同名的外键，所以删掉1）

②设置外键操作

最右侧时关于母表中的对应记录被更新或删除时的操作

一般来说不应该更改主键，这是一种陋习，万一某张表的主键变了，想要确保外键表也随之更新

当student1变成student2时，想确保该学生的所有注册记录也都更新，变成引用student2

CASCADE：如果主键更改，MySQL会自动更新子表中的记录

RESTRICT：限制，拒绝更新

SET NULL：设置空值（如果student_id变了，外键就会被设置为空值，导致一条子记录，也就是enrollments失去了母表——孤儿记录，不良数据）

NO ACTION：和RESTRICT完全一样，防止或拒绝更新操作

在此案例中，删除一个学生时，意味着学生的注册将被MySQL自动删除

在此注册表存储财务信息：注册课程的支付价格，如果删除就无法回答“销售走势”、“哪门课程热度高”等问题，因此要防止删除操作

在这个数据库中，一个学生注册了至少一门课，而想删除该学生，希望MySQL防止或拒绝删除操作——设置RESTRICT或者NO ACTION

如果有外键，就需要设置以上这两个约束，告诉MySQL，当主键被更新或删除时应该进行什么操作

一般来说更新就设置：CASADE

删除大多数不想丢数据就设置RESTRICT或者NO ACTION，如果不是很重要就可以用CASADE（取决于业务情况，了解业务需求非常重要）

例如一个让人为自己设置提醒的应用程序，注册、创建账户、设置提醒，当有人注销账号，大概率不在意她们设置的提醒，想要删除

设置好curses

如果一门课程有至少一条注册记录，除非先删除注册，否则不能删除该课程

————TBC