带你从入门到精通——MySQL（八. CTE表达式和数据库设计）

如果按照我目前的想法，后续的博文应该是更新Python相关的内容了，但是如果大家还想继续学习MySQL的话，我也可以去继续为大家更新这部分的内容，但是其实这八篇博文已经把大部分的MySQL相关知识都介绍了，如果后续继续更新MySQL的内容的话，我就准备去为大家讲解力扣上经典的MySQL面试题。大家是想直接看Python相关的内容，或者是想再学习几道经典的MySQL面试题，继续巩固一下相关MySQL知识，都可以在评论区留言或者私信我，我会根据大家的意见去更新后续的内容。

八. CTE表达式和数据库设计

8.1 CTE表达式

8.1.1 基本CTE表达式

CTE全称Common Table Expresssion，即公用表表达式，CTE其本质是将一个子查询语句的查询结果作为一张临时表，我们可以在该临时表的基础上，进行进一步的查询操作，其基础语法格式如下：

WITH tmp_table1 AS (
    SELECT field_name1
    FROM table_name1
),
     tmp2_table AS (
         SELECT field_name2
         FROM table_name2
     ),
     tmp3_table AS (
         SELECT field_name3
         FROM table_name3
     )
SELECT field_name1
FROM tmp1_table;

可以看到，使用CTE表达式后，我们可以直接在临时表tmp1_table或tmp2_table或tmp3_table上直接进行进一步的查询操作，比起直接使用一般的子查询语句，使用CTE表达式，代码的整体结构更加完整，使用更加便捷。

8.1.2 递归CTE表达式

递归CTE表达式用于递归生成表中的数据，一般有两种格式，格式一如下：

WITH RECURSIVE CTE(field_name1,field_name2,...) AS (
SELECT column1,column2... FROM table_name WHERE conditions
UNION ALL
SELECT column1,column2... FROM table_name
[inner] JOIN CTE ON conditions
)

由于递归CTE不好理解，这里为大家详细介绍每条语句的具体含义：

WITH RECURSIVE CTE（field_name1,field_name2,...），该语句用于定义需要生成的表的表名和字段名，在该示例中，需要生成的表的表名为CTE，表中的字段名为field_name1,field_name2,...。

AS（），该语句的意思是将括号中的查询出的数据赋值给CTE表。

SELECT column1,column2... FROM table_name WHERE conditions，该语句是作为递归的起点，表示从名为table_name的表中按conditions进行条件过滤并筛选出column1,column2...字段，最后将该行数据作为CTE表的第一行数据。

SELECT column1,column2... FROM table_name [inner] JOIN CTE ON conditions，该语句表示将从表名为table_name的表中查询出的column1,column2...字段，与CTE表中的字段按conditions进行内连接，直到无法满足conditions时，内连接无法继续进行，递归结束。

UNION ALL，表示将递归起点与递归时的每条内连接结果进行不去重合并。

格式二如下：

WITH RECURSIVE CTE(field_name1,field_name2,...) AS (
SELECT column1,column2... FROM table_name WHERE conditions
UNION ALL
SELECT column1,column2... FROM CTE WHERE conditions
)

两种格式其实是极为相似的，唯一的差别就是在递归结束时的条件判断，格式一是通过内连接CTE表来控制递归的结束，而格式二则是使用SELECT column1,column2... FROM CTE WHERE conditions语句，直接对CTE进行查询，并添加WHERE关键字进行条件过滤，直到不满足conditions时，递归结束。

8.1.3 示例

需求：使用递归CTE表达式打印一个包含1到10（含端点）之间的整数的表，示例代码和输出结果如下：

WITH RECURSIVE cte(nums) AS (
    SELECT 1
    UNION ALL
    SELECT nums + 1 FROM cte WHERE nums < 10
)SELECT nums FROM cte;

8.2 数据库设计

8.2.1 ER模型

ER模型全称Entity-Relationship模型，即实体关系模型，是一个在关系型数据库设计中用于描述数据及其之间关系的高级概念模型。ER模型的主要目的是帮助开发者抽象出现实世界中的数据需求，并将其转化为数据库设计的基础。

在ER模型中有以下三个核心概念：

实体（Entity）：代表现实世界中的对象或事物，在ER模型中，通常用矩形表示，例如“学生”、“书籍”、“汽车”等实物都可以作为一个实体。

属性（Attribute）：描述实体的特征或属性，在ER模型中，通常用椭圆表示并通过线连接到其相关的实体，例如，对于“学生”这个实体，“姓名”、“学号”等都可以是其属性。

关系（Relationship）：表示实体间的交互，在ER模型中，通常用菱形表示并通过线连接到相关的两个实体，例如，“学生”和“课程”之间可能存在“选课”这个关系。

在基于ER模型来设计数据库时，一个实体可以表示一张表，而属性就是表中的字段。

关系可以通过表与表之间关联方式来确定，通常有以下三种关系：

一对一关系（One To One） ：A表一行 → B表一行，B表一行 → A表一行，例如，对于一个商品的售价表和一个商品的销量表，一个商品的销量和售价只能一一对应

一对多关系（One To Many） ：A表一行 → B表多行，B表一行 → A表一行，例如，对于一个商品类别表和一个商品表，一个商品只能有一个类别，而一个类别则可以对于多个商品。

多对多关系（Many To Many） ：A表一行 → B表多行，B表一行 → A表多行，例如，对于一个学生表和一个课程表，一个学生可能会选多个课程，而一个课程又可以被多个学生选择，这两种表之间就是多对多关系。

实体之间的一对多或一对一的关系，可以体现为实体表中的一个外键字段，而对于实体之间的多对多关系，一般需要将多对多关系拆分成两个一对多关系，并构建一个中间关系表，记录两张表之间的数据联系，对于上述学生表和课程表例子，我们可以构建一个学生选课表，表中记录每条个学生选课信息，就可以将多对多关系拆分成学生对于学生选课表以及课程对于学生选课表的两个一对多关系。

一个关于学生和课程的ER模型示例如下，其中部分线中的N表示表之间的一对多关系：

8.2.2 范式

在设计关系型数据库时，我们需要遵从不同的规范要求，从而设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

根据数据库冗余的大小，目前关系型数据库有以下六种范式：

第一范式（1NF）

第二范式（2NF）

第三范式（3NF）

巴斯-科德范式（BCNF）

第四范式（4NF）

第五范式（5NF，又称完美范式）

在平时设计数据库时，一般遵循前三种范式即可，因此这里我们主要介绍前三范式的相关内容,

第一范式（1NF）：每个字段都是不可分割的原子值。

第二范式（2NF）：在满足第一范式基础上每个表都需要有一个主键，此外需要消除部分函数依赖，即非主键字段必须间接或直接的依赖于主键，而不能仅是部分依赖。

第三范式（3NF）：在满足第二范式基础上需要消除传递函数依赖，即非主键字段必须直接依赖于主键，不能存在传递依赖，也就是不能存在非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。

标签：...,范式,CTE,表达式,MySQL,table,SELECT
From： https://blog.csdn.net/2401_86480334/article/details/144023333