MySQL基础(3)——计算字段、处理函数、聚集函数和数据分组
来源:互联网 发布:全民淘宝权 编辑:程序博客网 时间:2024/06/02 02:33
本篇整理数据处理、汇总、分组数据相关的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()
功能:返回子串的字符用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
3. 用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数。
实例:检索出固定订单时间的订单记录
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 也是可以的
重点补充:
- MySQL基础(3)——计算字段、处理函数、聚集函数和数据分组
- MySQL创建字段+数据处理函数+汇总数据(聚集函数)+分组数据
- MySQL数据聚集函数
- MySQL--操作简记(汇总数据(聚集函数),分组数据,子查询)
- MySQL必知必会笔记(三)SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询
- MySQL必知必会笔记(二)SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询
- MySQL必知必会笔记(三)SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询
- MySQL必知必会笔记(二)SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询
- 【MySQL】SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询
- Mysql快速复习(三)--计算字段、处理函数、汇总数据
- MySQL数据库——汇总数据(聚集函数)
- sqlServer 聚集函数 和 分组查询
- MySQL数据汇总与聚集函数——MySQL系列(七)
- MySQL-聚集函数(汇总)
- postgresql 聚集函数加分组
- 数据库笔记8:SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询
- MySQL数据汇总与聚集函数
- SQL必知必会(二) 计算字段、函数、汇总数据、分组数据、子查询
- 欢迎使用CSDN-markdown编辑器
- Android的输入法监听布局
- newtonjson.net JsonConverter 的使用自定义序列化某个类(一)
- 常用类
- revit二次开发——自适应构件族
- MySQL基础(3)——计算字段、处理函数、聚集函数和数据分组
- Android中的AOP编程之AspectJ实战实现数据埋点
- Java NIO系列教程(一) Java NIO 概述
- 《机器学习》学习笔记六 贝叶斯分类器
- JS-常用互动方法
- Java堆区的认识
- cin<<、cin.get()、getline()(个人简单整理)黄刚的博客
- redis-[3]-redis数据库与事务
- 《机器学习》学习笔记七 聚类