MySQL基础（3）——计算字段、处理函数、聚集函数和数据分组

落日映苍穹つ 2022-06-13 07:53 311阅读 0赞

本篇整理数据处理、汇总、分组数据相关的SQL语句和函数。

### **一、计算字段** ###

> **计算字段的重要性**  
> 很多时候存储在表中的数据都不是应用程序所需要的，因此我们需要直接从数据库中检索出转换、计算或格式化过的数据；*而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。*  
> 这就是计算字段发挥作用的所在了。与前面篇幅提到过的列不同， 计算字段并不实际存在于数据库表中。计算字段是**运行时在SELECT语句内创建的**。

**字段**：与*列* 意思相近，经常互换使用。但数据库列一般称为列，而术语字段通常用在计算字段的连接上。  
**拼接**：将值联结到一起构成单个值。

**函数 1**：Concat()  
功能：把多个串连接起来形成一个较长的串  
说明：Concat()需要一个或多个指定的串，各个串之间用逗号分隔。  
实例：

![这里写图片描述][SouthEast]

**函数 2**：RTrim() /LTrim() /Trim()  
功能：去掉值右边/左边/左右两边的所有空格  
说明：一般通过删除数据内多余的空格来整理数据  
实例：

![这里写图片描述][SouthEast 1]

**关键字 3**：AS 名字  
功能：指示SQL创建一个包含指定计算的命名的计算字段  
说明：仅用SELECT语句拼接字段并没有名字，客户机不能引用未命名的列，因而需要列**别名/ 导出列**。  
实例：

![这里写图片描述][SouthEast 2]

> 别名还有其他用途：包括在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它，等等。
> 
> quantity\*item\_price得到一个计算字段，命名为  
> expanded\_price，MySQL支持基本算术操作符：+-\*/，可以利用圆括号区分优先顺序

### **二、数据处理函数** ###

多数SQL语句是可移植的，但函数的可移植性却不强。建议使用函数时，保证**做好代码注释**，以便以后阅读者能确切地知道所编写地SQL代码的含义

大多数SQL实现支持以下类型的函数：

1.  用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数:RTrim()、LTrim()、Trim()、Upper()等
    
    **函数 4**：Upper()  
    功能：将文本转换为大写  
    实例：  
    
    
    ![这里写图片描述][SouthEast 3]
    
    **函数 5**：Left()  
    功能：返回串左边地字符  
    实例：
    
    ![这里写图片描述][SouthEast 4]
    
    **函数 6**：Length()  
    功能：返回串的长度  
    实例：
    
    ![这里写图片描述][SouthEast 5]
    
    **函数 7**：Locate()  
    功能：找出串的一个子串  
    实例：
    
    ![这里写图片描述][SouthEast 6]
    
    **函数 8**：Lower()  
    功能：将串转换为小写
    
    **函数 9**：Right()  
    功能：返回串右边的字符
    
    **函数 10**：Soundex()  
    功能：返回串的SOUNDEX值  
    说明：**SOUNDEX**是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较  
    实例：
    
    ![这里写图片描述][SouthEast 7]
    
    **函数 11**：SubString()  
    功能：返回子串的字符
2.  用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Abs()</td> 
   <td align="center">返回一个数的绝对值</td> 
  </tr> 
  <tr> 
   <td>Cos()</td> 
   <td align="center">返回一个角度的余弦</td> 
  </tr> 
  <tr> 
   <td>Exp()</td> 
   <td align="center">返回一个数的指数值</td> 
  </tr> 
  <tr> 
   <td>Mod()</td> 
   <td align="center">返回除操作的余数</td> 
  </tr> 
  <tr> 
   <td>Pi()</td> 
   <td align="center">返回圆周率</td> 
  </tr> 
  <tr> 
   <td>Rand()</td> 
   <td align="center">返回一个随机数</td> 
  </tr> 
  <tr> 
   <td>Sin()</td> 
   <td align="center">返回一个角度的正弦</td> 
  </tr> 
  <tr> 
   <td>Sqrt()</td> 
   <td align="center">返回一个数的平方根</td> 
  </tr> 
  <tr> 
   <td>Tan()</td> 
   <td align="center">返回一个角度的正切</td> 
  </tr> 
 </tbody> 
</table>

3. 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AddDate()</td> 
   <td align="center">增加一个日期（天、周等）</td> 
  </tr> 
  <tr> 
   <td>AddTime()</td> 
   <td align="center">增加一个时间（时、分等）</td> 
  </tr> 
  <tr> 
   <td>CurDate()</td> 
   <td align="center">返回当前日期</td> 
  </tr> 
  <tr> 
   <td>CurTime()</td> 
   <td align="center">返回当前时间</td> 
  </tr> 
  <tr> 
   <td>Date()</td> 
   <td align="center">返回日期时间的日期部分</td> 
  </tr> 
  <tr> 
   <td>DateDiff()</td> 
   <td align="center">计算两个日期之差</td> 
  </tr> 
  <tr> 
   <td>Date_Add()</td> 
   <td align="center">高度灵活的日期运算函数</td> 
  </tr> 
  <tr> 
   <td>Date_Format()</td> 
   <td align="center">返回一个格式化的日期或时间串</td> 
  </tr> 
  <tr> 
   <td>Day()</td> 
   <td align="center">返回一个日期的天数部分</td> 
  </tr> 
  <tr> 
   <td>DayOfWeek()</td> 
   <td align="center">对于一个日期，返回对应的星期几</td> 
  </tr> 
  <tr> 
   <td>Hour()</td> 
   <td align="center">返回一个时间的小时部分</td> 
  </tr> 
  <tr> 
   <td>Minute()</td> 
   <td align="center">返回一个时间的分钟部分</td> 
  </tr> 
  <tr> 
   <td>Month()</td> 
   <td align="center">返回一个日期的月份部分</td> 
  </tr> 
  <tr> 
   <td>Now()</td> 
   <td align="center">返回当前日期和时间</td> 
  </tr> 
  <tr> 
   <td>Second()</td> 
   <td align="center">返回一个时间的秒部分</td> 
  </tr> 
  <tr> 
   <td>Time()</td> 
   <td align="center">返回一个日期时间的时间部分</td> 
  </tr> 
  <tr> 
   <td>Year()</td> 
   <td align="center">返回一个日期的年份部分</td> 
  </tr> 
 </tbody> 
</table>

实例：检索出固定订单时间的订单记录

![这里写图片描述][SouthEast 8]

> Date(order\_date)指示MySQL仅提取列的日期部分，比where order\_date=’2005-09-01’;更为可靠，因为后者在存储的order\_date值为 2005-09-01 11:30:05时WHERE匹配失效

实例2：检索出2005年9月下的所有订单

![这里写图片描述][SouthEast 9]  
或者  
![这里写图片描述][SouthEast 10]  
  
4. 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数

### **三、聚集函数** ###

聚集函数：运行在行组上，计算和返回单个值的函数。

> 聚集函数用于**汇总数据**，因为我们经常需要汇总数据而不用把它们实际检索出来，以节省时间和资源。
> 
> 此外，MySQL还支持一系列的标准偏差聚集函数

**函数 12**：AVG()  
功能：返回某列/所有列/某行的平均值  
说明：该函数只用于**单个列**，若要获得多个列的平均值，必须使用多个AVG()函数  
实例：

![这里写图片描述][SouthEast 11]

**函数 13**：COUNT()  
功能：返回某列的行数  
说明：COUNT()函数有两种使用方式：

1.  使用COUNT(\*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
2.  使用COUNT(column)对特定列中具有值的行进行计数，**忽略NULL值。**

实例：对具有电子邮件地址的客户计数

![这里写图片描述][SouthEast 12]

**函数 13**：MAX()  
功能：返回某列的最大值  
说明：MAX()要求指定列名；在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行； MAX()函数忽略列值为NULL的行

**函数 13**：MIN()  
功能：返回某列的最小值  
说明：与MAX()相同

**函数 13**：SUM()  
功能：返回某列值之和  
说明：SUM()函数忽略列值为NULL的行  
实例：

![这里写图片描述][SouthEast 13]

**组合聚集函数：**注意：逗号不能少，否则会报错

![这里写图片描述][SouthEast 14]

> 以上5个聚集函数都可以如下使用（适用于MySQL 5.0.3及以上版本）：  
> 1、对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；  
> 2、只包含不同的值，指定DISTINCT参数。  
> *实例：*排除了相同的价格，因此与前面所示平均价格不同  
> 
> 
> ![这里写图片描述][SouthEast 15]
> 
> 如果指定列名，则DISTINCT只能用于COUNT()。**DISTINCT 不能用于COUNT(\*)**，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

### **四、数据分组** ###

关键字：GROUP BY

> 分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算，例如： 返回每个供应商提供的产品数目，或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商

1、创建分组：GROUP BY

> 1.  GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制。
> 2.  如果在GROUP BY子句中嵌套了分组，数据将在后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
> 3.  GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
> 4.  除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子 句中给出。
> 5.  如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列 中有多行NULL值，它们将分为一组。
> 6.  **GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。**

实例：统计每个供应商拥有的产品数

![这里写图片描述][SouthEast 16]

使用WITH ROLLUP关键字，可以得到每个分组以 及每个分组汇总级别（针对每个分组）的值，如下所示：

![这里写图片描述][SouthEast 17]

2、过滤分组：HAVING子句

> 所有关于WHERE的所有这些技术和选项都适用于 HAVING。它们的句法是相同的，只是关键字有差别。
> 
> 可以这样理解：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，**WHERE排除的行不包括在分组中**。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

实例1：列出至少有两个订单的所有顾客

![这里写图片描述][SouthEast 18]

**同时使用where和having子句：**  
实例2：列出具有2个（含）以上、价格 为10（含）以上的产品的供应商

![这里写图片描述][SouthEast 19]  
图中第二、三次命令失败可能原因：分组后无法查找到prod\_price列

> **GROUP BY 和ORDER BY区别**：  
> 
> 
> ![这里写图片描述][SouthEast 20]  
> 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。确保排序一定要使用ORDER BY子句，而不能仅仅使用GROUP BY排序数据。  
> 实例：  
> ![这里写图片描述][SouthEast 21]  
> 其中，having sum(quantity\*item\_price)>=50 改为having ordertotal >= 50 也是可以的

--------------------

**重点补充**：

**select子句及其顺序**

<table> 
 <tbody> 
  <tr> 
   <th>子句</th> 
   <th>说明</th> 
   <th>是否必须使用</th> 
  </tr> 
  <tr> 
   <td>select</td> 
   <td>要返回的列或表达式</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>from</td> 
   <td>从中检索数据的表</td> 
   <td>仅在从表选择数据时使用</td> 
  </tr> 
  <tr> 
   <td>where</td> 
   <td>行级过滤</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>group by</td> 
   <td>分组说明</td> 
   <td>仅在按组计算聚集时使用</td> 
  </tr> 
  <tr> 
   <td>having</td> 
   <td>组级过滤</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>order by</td> 
   <td>输出排序顺序</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>limit</td> 
   <td>要检索的行数</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

[SouthEast]: /images/20220613/085f5d1caf4d4113adb00b291e569391.png
[SouthEast 1]: /images/20220613/42aa9ae4178e4dfe9e23feb382da50f6.png
[SouthEast 2]: /images/20220613/ebc2cf9a5b7e495aa1a577dfa70e7ad3.png
[SouthEast 3]: /images/20220613/b42e12e213a540929e0a7c38affec450.png
[SouthEast 4]: /images/20220613/fc4bd210449847da98eb56a54585095c.png
[SouthEast 5]: /images/20220613/4e52cefef3e74dfa852062319f4a20cf.png
[SouthEast 6]: /images/20220613/3bb96f47358b4ebd8184a28a49d03a7b.png
[SouthEast 7]: /images/20220613/a2eb100a6ff840df932c9979d7f2f542.png
[SouthEast 8]: /images/20220613/265b136ac2d04542b85723324067d5d6.png
[SouthEast 9]: /images/20220613/5ad826d4554643578f8a3578eedcf2e4.png
[SouthEast 10]: /images/20220613/6ec5f2a836cb49f8945346f6559d4656.png
[SouthEast 11]: /images/20220613/b3b1519ff5ee46588da5454038e1b49f.png
[SouthEast 12]: /images/20220613/3eceb06aa1204f1b88024d51a133853c.png
[SouthEast 13]: /images/20220613/86d43fad82614221b50ca99b64cba294.png
[SouthEast 14]: /images/20220613/a7d1f484230d407a96a78745bbb58a41.png
[SouthEast 15]: /images/20220613/2cd1f425ce6642ed957aba57a6a9e1be.png
[SouthEast 16]: /images/20220613/6c460b31df81480996536ca9de8d9250.png
[SouthEast 17]: /images/20220613/08209f2884634a2dbc1f09e58f864eea.png
[SouthEast 18]: /images/20220613/0a0a6e223bf94d368001d42ca6a4dd98.png
[SouthEast 19]: /images/20220613/42442628c98144418b4a922b0e0dcb29.png
[SouthEast 20]: /images/20220613/5468b24fc4f9448ca54d104d3516427c.png
[SouthEast 21]: /images/20220613/056a1ff4735545b89be4febdd7bf248c.png