从手办看SQL基础

落日映苍穹つ 2022-11-27 15:38 117阅读 0赞

### 文章目录 ###

*  导读
 *  数据存储
 *  \`SQL\`语句基本公式
 *  运行解析
 *  文章索引（手动）

# 导读 #

**`SQL`语句是所有数据库系统没有办法绕开的。与其说是基础，不如说是所有数据库系统遵循的“行业规范”。这也是我个人的`MySQL`专栏的开篇。由于是一篇一篇逐步完善，所以上一篇和下一篇的切换做的不是很好。后续会逐步完善。**

# 数据存储 #

因为数据库的数据非常多，内存往往不能满足所有数据的存储，而且当服务器断电后，内存中的所有数据将会立即消失，是非常严重的事故。如果涉及交易，这将会是毁灭性的灾难。

所以数据往往都是存在硬盘中，也作“**磁盘**”，大容量、信息不随着电的消失而消失，是非常不错的载体。但是速度奇慢，无法在有效时间内回复大量的查询请求。于是，人们发明了**B+树**。

**B+树**的特点就是非叶子节点并不占用内存，只有真正找到了位置并读取数据的时候才有一次磁盘IO，在此之前完全基于内存的操作极大提升了查询速度。而使得数据按照一定规律排列成B+树的就是数据库的**索引**。

索引能够根据指定的字段重构数据库表用于存储的树形结构，从而能够在一定程度上优化查询，从 O ( n ) O(n) O(n)降到 O ( log ⁡ ( n ) ) O(\\log(n)) O(log(n))，如果是4000行数据，将会从4000次对比降到20次以内，这是非常贴近理想的优化。

# `SQL`语句基本公式 #

每个计算机语言都有他的公式，比如**C语言**的`printf("%d", &n)`，又如**Java**的`System.out.println("Hello World")`，都是有规律可循。依照规律，无论是平时作业还是大型项目都是信手拈来。下面给出`SQL`语句的公式：

SELECT <选择字段>
    FROM <左表> <join类型>
    JOIN <右表> ON <join条件>
    WHERE <选择条件>
    GROUP BY <集合条件>
    HAVING <聚合条件>
    ORDER BY <排序条件>
    LIMIT <每页选择行数> -- 这里是MySQL特有的分页

# 运行解析 #

当然，`SQL`是一门**高级语言**，**只知道0和1的机器无法直接识别**，只能说是由机器解释后运行。解析便和编辑完全是两回事了。我们假设有这么一句`SQL`和上面给出的公式完全一样的顺序，解析之后就会变成这样：

FROM <左表> -- 获得笛卡尔积
    ON <join条件> -- 主表保留
    <JOIN类型> JOIN <右表> -- 不符合ON的数据也添加进去
    WHERE <选择条件> -- 非聚合数据
    GROUP BY <集合条件> -- 改变对表的引用
    HAVING <聚合条件> -- 之作用分组后
    SELECT
    DISTINCT
    ORDER BY <排序条件> -- 可使用SELECT别名
    LIMIT <每页选择行数> -- 行数、偏移量

*有些人估计就直接蒙圈了。来举个有点奇怪的例子吧：*

你迷恋上了手办，准备搜罗一下有没有自己喜欢的，然后统一入手。初期，你收集了一点点信息，并做成了表格：

**手办表**

<table> 
 <thead> 
  <tr> 
   <th>手办id</th> 
   <th>对应人物</th> 
   <th>服饰</th> 
   <th>价格</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>蕾姆</td> 
   <td>蓝色比基尼</td> 
   <td>99.00</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>巧克力</td> 
   <td>红色长袖睡衣</td> 
   <td>258.00</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>香草</td> 
   <td>蓝色旗袍</td> 
   <td>258.00</td> 
  </tr> 
 </tbody> 
</table>

**番剧表**

<table> 
 <thead> 
  <tr> 
   <th>番剧id</th> 
   <th>番剧名称</th> 
   <th>出场人物</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>NEKOPARA</td> 
   <td>巧克力</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>NEKOPARA</td> 
   <td>香草</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Re:从零开始的异世界生活</td> 
   <td>蕾姆</td> 
  </tr> 
 </tbody> 
</table>

然后，你想把这两个表对应起来，于是编写`SQL`：

SELECT DISTINCT 手办表.对应人物, 手办表.服饰, 手办表.价格, 番剧表.番剧名称, 番剧表.出场人物
    FROM 手办表, 番剧表
    WHERE 手办表.对应人物 = 番剧表.出场人物
    GROUP BY 手办表.对应人物, 手办表.服饰, 手办表.价格, 番剧表.番剧名称, 番剧表.出场人物;
    ORDER BY 手办表.价格 DESC

*我们待会来解释这几行代码*。运行这样的语句之后，你的表格就变成了这样：

**番剧手办表**

<table> 
 <thead> 
  <tr> 
   <th>对应人物</th> 
   <th>服饰</th> 
   <th>番剧名称</th> 
   <th>价格</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>蕾姆</td> 
   <td>蓝色比基尼</td> 
   <td>Re:0</td> 
   <td>99</td> 
  </tr> 
  <tr> 
   <td>巧克力</td> 
   <td>红色长袖睡衣</td> 
   <td>NEKOPARA</td> 
   <td>258.00</td> 
  </tr> 
  <tr> 
   <td>香草</td> 
   <td>蓝色旗袍</td> 
   <td>NEKOPARA</td> 
   <td>258.00</td> 
  </tr> 
 </tbody> 
</table>

看起来挺不错的。

在系统里面他们又是怎么运行的呢？

首先，我们**向内存中加载**`FROM`语句后面**所有**的表，并**交叉相联合**，也就是我们高中时期最为熟悉的**排列组合**，番剧表三条记录、手办表三条记录，所以内存一共加载了 3 × 3 = 9 3\\times3=9 3×3=9条记录；

其次，没有发现`ON`、`JOIN`语句，跳过；

然后发现有`WHERE`语句，所以系统将会按照`WHERE`语句给出的条件筛选手办对应任务和番剧出场人物相同的行。这里一共匹配到了三项，也就是上面的“**番剧手办表**”所显示的内容；

接着就发现了`GROUP BY`语句，于是根据所有选择的项进行分组。一般情况下应当是所有出现在`SELECT`列表上的**所有项都应该出现在这里**；

`HAVING`是分组条件，也就是在`GROUP BY`存在的情况下才能使用。具体使用其实和`WHERE`是完全一样的。只不过**我个人**喜欢在分组的时候更倾向于使用范围，也就是`HAVING field > 0`之类的语句。当然具体需求具体分析啦；

分组完成后就是列出所有项了。`SELECT`将会从已经加载在内存里所有的列中选取指定的列；后面紧跟着的`DISTINCT`就是保证选出来的**所有行**中不会有相同的**一行数据**；

最后就是`ORDER BY`对最终得出的行根据指定的字段进行排列，整段代码最后的`DESC`是倒序排列的意思；而默认情况是`ASC`，即正序排列。

这么过了一遍原理，会不会有点理解了？

# 文章索引（手动） #

上一篇：没有了