MySQL入门学习：函数

最新推荐文章于 2024-03-08 23:25:10 发布

溪午闻璐

最新推荐文章于 2024-03-08 23:25:10 发布

阅读量324

点赞数

分类专栏：数据库文章标签： mysql sql

本文链接：https://blog.csdn.net/sssxlxwbwz/article/details/122783373

版权

数据库专栏收录该内容

21 篇文章 1 订阅

订阅专栏

一、计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式。如下几个例子：

如果想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般包含在不同的表列中。
城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。
列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）。为打印发票，需要物品的总价格。
需要根据表数据进行总数、平均数计算或其他计算。

上述例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户应用程序中重新格式化。

这就是计算字段发挥作用的所在了。与数据库中存在的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

我们应该注意到，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户机（如应用程序）的角度来看，计算字段的数据是以与其他列的数据相同的方式返回的。

1.1 拼接字段

拼接（concatenate）：将值联结到一起构成单个值。

示例：

vendors表包含供应商名和位置信息。假如要生成一个供应商报表，需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。
此报表需要单个值，而表中数据存储在两个列vend_name和vend_ country中。此外，需要用括号将vend_country括起来，这些东西都没有明确存储在数据库表中。

解决办法是把两个列拼接起来。在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。

SELECT CONCAT(vend_name,'(',vend_country,')') 
FROM vendors 
ORDER BY vend_name;

Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

从前面的输出中可以看到，SELECT语句拼接地址字段工作得很好。但此新计算列实际上它没有名字，它只是一个值。如果仅在SQL查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户机应用中，因为客户机没有办法引用它。为了解决这个问题，SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。示例如下：

SELECT CONCAT(vend_name,'(',vend_country,')') AS vend_title 
FROM vendors 
ORDER BY vend_name;

上例中在SELECT语句中计算字段之后跟了文本AS vend_title。它指示SQL创建一个包含指定计算的名为vend_title的计算字段。从输出中可以看到，结果与前例相同，但现在列名vend_title，任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样。

1.2 执行算数计算

计算字段的另一常见用途是对检索出的数据进行算术计算。

示例：

orders表包含收到的所有订单，orderitems表包含每个订单中的各项物品。使用SQL语句检索订单号20005中的所有物品：

SELECT prod_id,quantity,item_price FROM orderitems WHERE order_num = 20005

查询结果中，item_price列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）：

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price 
FROM orderitems 
WHERE order_num = 20005

输出中显示的expanded_price列为一个计算字段，此计算为quantity*item_price。客户机应用现在可以使用这个新计算列，就像使用其他列一样。

二、数据处理函数

2.1 函数

与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数一般是在数据上执行的，它给数据的转换和处理提供了方便。大多数SQL实现支持以下类型的函数：

用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数
用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

2.1.1 文本函数

示例：采用Upper()函数将文本转化为大写。

SELECT vend_name,UPPER(vend_name) AS vend_name_up 
FROM vendors
ORDER BY vend_name;

常用的文本处理函数：

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

2.1.2 日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

常用日期和时间处理函数：

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

注意MySQL采用的日期格式，无论我们什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。

示例Date()：

SELECT cust_id,order_num
FROM orders
WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

2.1.3 数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此没有串或日期—时间处理函数的使用那么频繁。

常用数值处理函数 :

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

三、汇总数据

3.1 聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有以下几种：

确定表中行数（或者满足某个条件或包含某个特定值的行数）。
获得表中行组的和。
找出表列（或所有行或某些特定的行）的最大值、最小值和平均值

上述例子都需要对表中数据（而不是实际数据本身）汇总。因此，返回实际表数据是对时间和处理资源的一种浪费（更不用说带宽了）。实际我们想要的是汇总信息。

SQL聚集函数：

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

3.1.1 AVG()函数

AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

示例：

SELECT AVG(prod_price) AS avg_price 
FROM products;

求特定列或行的平均值：

SELECT AVG(prod_price) AS avg_price 
FROM products
WHERE vend_id = 1003;

3.1.2 COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。COUNT()函数有两种使用方式：

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

示例：

SELECT COUNT(*) AS num_cust
FROM customers;

SELECT COUNT(cust_email) AS num_cust
FROM customers;

3.1.3 MAX()函数、MIN()函数

MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示，返回prod_price列中最大的数值。

SELECT MAX(prod_price) AS max_price 
FROM products

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。

3.1.4 SUM()函数

SUM()用来返回指定列值的和（总计）。

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005

3.2 组合聚集函数

SELECT语句可根据需要包含多个聚集函数。示例如下：

SELECT COUNT(*) AS num_items,
       AVG(prod_price) AS price_avg,
       MAX(prod_price) AS price_max,
       MIN(prod_price) AS price_min
FROM products;

聚集函数用来汇总数据。这些函数是高效设计的，它们返回结果一般比你在自己的客户机应用程序中计算要快得多。

四、分组数据

4.1 创建分组

分组是在SELECT语句的GROUP BY子句中建立的。示例如下：

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

上例中，SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立）。GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods。从输出中可以看到，供应商1001有3个产品，供应商1002有2个产品，供应商1003有7个产品，而供应商1005有2个产品。

使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集

在具体使用GROUP BY子句前，需要知道一些重要的规定：

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

溪午闻璐

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL入门学习：函数

一、计算字段存储在数据库表中的数据一般不是应用程序所需要的格式。如下几个例子：如果想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般包含在不同的表列中。城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。物品订单表存储物品的价格和数量，但不需要存储每个物品的总价格（用价格乘以数量即可）。为打印发票，需要物品的总价格。需要根据表数据进行总数、平均数计...
复制链接

扫一扫