本篇整理数据处理、汇总、分组数据相关的SQL语句和函数。
一、计算字段
计算字段的重要性
很多时候存储在表中的数据都不是应用程序所需要的,因此我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。
这就是计算字段发挥作用的所在了。与前面篇幅提到过的列不同, 计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
字段:与列 意思相近,经常互换使用。但数据库列一般称为列,而术语字段通常用在计算字段的连接上。
拼接:将值联结到一起构成单个值。
函数 1:Concat()
功能:把多个串连接起来形成一个较长的串
说明:Concat()需要一个或多个指定的串,各个串之间用逗号分隔。
实例:
函数 2:RTrim() /LTrim() /Trim()
功能:去掉值右边/左边/左右两边的所有空格
说明:一般通过删除数据内多余的空格来整理数据
实例:
关键字 3:AS 名字
功能:指示SQL创建一个包含指定计算的命名的计算字段
说明:仅用SELECT语句拼接字段并没有名字,客户机不能引用未命名的列,因而需要列别名/ 导出列。
实例:
别名还有其他用途:包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它,在原来的名字含混或容易误解时扩充它,等等。
quantity*item_price得到一个计算字段,命名为
expanded_price,MySQL支持基本算术操作符:+-*/,可以利用圆括号区分优先顺序
二、数据处理函数
多数SQL语句是可移植的,但函数的可移植性却不强。建议使用函数时,保证做好代码注释,以便以后阅读者能确切地知道所编写地SQL代码的含义
大多数SQL实现支持以下类型的函数:
用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数:RTrim()、LTrim()、Trim()、Upper()等
函数 4:Upper()
功能:将文本转换为大写
实例:
函数 5:Left()
功能:返回串左边地字符
实例:函数 6:Length()
功能:返回串的长度
实例:函数 7:Locate()
功能:找出串的一个子串
实例:函数 8:Lower()
功能:将串转换为小写函数 9:Right()
功能:返回串右边的字符函数 10:Soundex()
功能:返回串的SOUNDEX值
说明:SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较
实例:函数 11:SubString()
功能:返回子串的字符用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
函数 | 说明 |
---|---|
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Pi() | 返回圆周率 |
Rand() | 返回一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个角度的正切 |
3. 用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数。
函数 | 说明 |
---|---|
AddDate() | 增加一个日期(天、周等) |
AddTime() | 增加一个时间(时、分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期,返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
实例:检索出固定订单时间的订单记录
Date(order_date)指示MySQL仅提取列的日期部分,比where order_date=’2005-09-01’;更为可靠,因为后者在存储的order_date值为 2005-09-01 11:30:05时WHERE匹配失效
实例2:检索出2005年9月下的所有订单
或者
4. 返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数
三、聚集函数
聚集函数:运行在行组上,计算和返回单个值的函数。
聚集函数用于汇总数据,因为我们经常需要汇总数据而不用把它们实际检索出来,以节省时间和资源。
此外,MySQL还支持一系列的标准偏差聚集函数
函数 12:AVG()
功能:返回某列/所有列/某行的平均值
说明:该函数只用于单个列,若要获得多个列的平均值,必须使用多个AVG()函数
实例:
函数 13:COUNT()
功能:返回某列的行数
说明:COUNT()函数有两种使用方式:
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
- 使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。
实例:对具有电子邮件地址的客户计数
函数 13:MAX()
功能:返回某列的最大值
说明:MAX()要求指定列名;在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行; MAX()函数忽略列值为NULL的行
函数 13:MIN()
功能:返回某列的最小值
说明:与MAX()相同
函数 13:SUM()
功能:返回某列值之和
说明:SUM()函数忽略列值为NULL的行
实例:
组合聚集函数:注意:逗号不能少,否则会报错
以上5个聚集函数都可以如下使用(适用于MySQL 5.0.3及以上版本):
1、对所有的行执行计算,指定ALL参数或不给参数(因为ALL是默认行为);
2、只包含不同的值,指定DISTINCT参数。
实例:排除了相同的价格,因此与前面所示平均价格不同
![]()
如果指定列名,则DISTINCT只能用于COUNT()。DISTINCT 不能用于COUNT(*),因此不允许使用COUNT(DISTINCT),否则会产生错误。类似地,DISTINCT必须使用列名,不能用于计算或表达式。
四、数据分组
关键字:GROUP BY
分组允许把数据分为多个逻辑组,以便能对每个组进行聚集计算,例如: 返回每个供应商提供的产品数目,或者返回只提供单项产品的供应商所提供的产品,或返回提供10个以上产品的供应商
1、创建分组:GROUP BY
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套, 为数据分组提供更细致的控制。
- 如果在GROUP BY子句中嵌套了分组,数据将在后规定的分组上 进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 (但不能是聚集函数)。如果在SELECT中使用表达式,则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子 句中给出。
- 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列 中有多行NULL值,它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
实例:统计每个供应商拥有的产品数
使用WITH ROLLUP关键字,可以得到每个分组以 及每个分组汇总级别(针对每个分组)的值,如下所示:
2、过滤分组:HAVING子句
所有关于WHERE的所有这些技术和选项都适用于 HAVING。它们的句法是相同的,只是关键字有差别。
可以这样理解:WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。这是一个重要的区别,WHERE排除的行不包括在分组中。这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组。
实例1:列出至少有两个订单的所有顾客
同时使用where和having子句:
实例2:列出具有2个(含)以上、价格 为10(含)以上的产品的供应商
图中第二、三次命令失败可能原因:分组后无法查找到prod_price列
GROUP BY 和ORDER BY区别:
![]()
一般在使用GROUP BY子句时,应该也给出ORDER BY子句。确保排序一定要使用ORDER BY子句,而不能仅仅使用GROUP BY排序数据。
实例:
![]()
其中,having sum(quantity*item_price)>=50 改为having ordertotal >= 50 也是可以的
重点补充:
子句 | 说明 | 是否必须使用 |
---|---|---|
select | 要返回的列或表达式 | 是 |
from | 从中检索数据的表 | 仅在从表选择数据时使用 |
where | 行级过滤 | 否 |
group by | 分组说明 | 仅在按组计算聚集时使用 |
having | 组级过滤 | 否 |
order by | 输出排序顺序 | 否 |
limit | 要检索的行数 | 否 |