MySQL 第11章、第12章、第13章、第14章 使用数据处理函数、汇总数据、分组数组、使用子查询
使用数据处理函数
本章介绍什么是函数,MySQL支持何种函数,以及如何使用这些函数。
函数
与其他大多数计算机语言一样,SQL支持利用函数来处理数据。函数 一般是在数据上执行的,它给数据的转换和处理提供了方便。
在前一章中用来去掉串尾空格的RTrim()就是一个函数的例子。
使用函数
大多数SQL实现支持以下类型的函数。
用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数。
用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)
的数值函数。
用于处理日期和时间值并从这些值中提取特定成分(例如,返回
两个日期之差,检查日期有效性等)的日期和时间函数。
返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本
细节)的系统函数。
文本处理函数
上一章中我们已经看过一个文本处理函数的例子,其中使用RTrim()
函数来去除列值右边的空格。下面是另一个例子,这次使用Upper()函数:
日期和时间处理函数
日期和时间采用相应的数据类型和特殊的格式存储,以便能快速和
有效地排序或过滤,并且节省物理存储空间。
一般,应用程序不使用用来存储日期和时间的格式,因此日期和时 间函数总是被用来读取、统计和处理这些值。由于这个原因,日期和时 间函数在MySQL语言中具有重要的作用。
表11-2列出了某些常用的日期和时间处理函数。
数值处理函数
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角
或几何运算,因此没有串或日期—时间处理函数的使用那么频繁。
具有讽刺意味的是,在主要DBMS的函数中,数值函数是最一致最统
一的函数。表11-3列出一些常用的数值处理函数。
汇总数据
本章介绍什么是SQL的聚集函数以及如何利用它们汇总表的数据。
聚集函数
我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提 供了专门的函数。使用这些函数,MySQL查询可用于检索数据,以便分析和报表生成。这种类型的检索例子有以下几种。
确定表中行数(或者满足某个条件或包含某个特定值的行数)。
获得表中行组的和。
找出表列(或所有行或某些特定的行)的最大值、最小值和平均
值。
上述例子都需要对表中数据(而不是实际数据本身)汇总。因此, 返回实际表数据是对时间和处理资源的一种浪费(更不用说带宽了)。重 复一遍,实际想要的是汇总信息。
为方便这种类型的检索,MySQL给出了5个聚集函数,见表12-1。 这些函数能进行上述罗列的检索。
聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数。
AVG() 函数
AVG()通过对表中行数计数并计算特定列值之和,求得该列的平均值。AVG()可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
下面的例子使用AVG()返回products表中所有产品的平均价格:
COUNT()函数
COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特
定条件的行的数目。 COUNT()函数有两种使用方式。
使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数,忽略 NULL值。
下面的例子返回customers表中客户的总数:
MAX()函数
MIN()函数
SUM()函数
聚集不同值
组合聚集函数
分组数据
数据分组
创建分组
分组是在SELECT语句的GROUP BY子句中建立的。理解分组的最好办法是看一个例子:
过滤分组
分组和排序
SELECT子句顺序
使用子查询
子查询
利用子查询进行过滤
本书所有章中使用的数据库表都是关系表(关于每个表及关系的描 述,请参阅附录B)。订单存储在两个表中。对于包含订单号、客户ID、 订单日期的每个订单,orders表存储一行。各订单的物品存储在相关的 orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。
现在,假如需要列出订购物品TNT2的所有客户,应该怎样检索?下 面列出具体的步骤。
(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。
上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT 语句返回的结果用于另一条SELECT语句的WHERE子句。
也可以使用子查询来把3个查询组合成一条语句。 第一条SELECT语句的含义很明确,对于prod_id为TNT2的所有订单物品,它检索其order_num列。输出列出两个包含此物品的订单:
作为计算字段使用子查询