5.1 数据处理函数

---

---

1.概述

SQL支持使用函数来处理数据，函数一般是在数据上执行的，它给数据的处理和转换提供了方便；

MySql支持以下类型的函数：

• 用于处理字符串的文本处理函数；
• 用于在数值数据上进行算术操作的数值函数；
• 用于处理日期和时间值并从这些值上提取特定成分的日期和时间函数；
• 返回DBMS正使用的特殊信息的系统函数；

2.文本处理函数

我们可以使用TRIM( )文本处理函数来去除值左右俩边的空格，也可以使用RTRIM( )或LTRIM( )来去除右边或左边的空格，在接收他人输入的值时，为了保证输入值的准确性，我们经常使用这个函数；

我们也可以使用UPPER( )函数来将文本转为大写，如：

输入：

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

输出：

下列是常用的一些文本处理函数：

函数	说明
LEFT( )	返回字符串左边的字符
LENGTH( )	返回字符串右边的字符
LOCATE( )	找出字符串的子串
LOWER( )	将字符串转换为小写
UPPER( )	将字符串转换为大写
LTRIM( )	去掉字符串左边的空格
RTRIM( )	去掉字符串右边的空格
TRIM( )	去掉字符串俩边的空格
SOUNDEX( )	去掉字符串SOUNDEX值
SUBSTRING( )	返回字串的字符

SOUNDEX是一个将任何字符串转换为描述其语音表示的字母数字模式的算法。其考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。这里我们只做了解即可；

3.日期和时间处理函数

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

一般，应用程序不使用数据库来存储日期和格式的格式，因此日期和时间函数总是被用来读取、统计和处理这些值，所以日期和时间函数在MySql中具有重要的作用，下列是常用的日期和时间处理函数：

函数	说明
ADDDATE( )	增加一个日期（天，周等）
ADDTIME( )	增加一个时间（时，分等）
CURDATE( )	返回当前日期
CURTIME( )	返回当前时间
DATE( )	返回日期时间的日期部分
DATEDIFF( )	计算俩个日期之差
DATE_ADD( )	高度灵活的日期运算函数
DATE_FORMAT( )	返回一个格式化的日期或时间字符串
DAY( )	返回一个日期的天数部分
DAYOFWEEK( )	对于一个日期，返回对应的星期几
HOUR( )	返回一个时间的小时部分
MINUTE( )	返回一个时间的分钟部分
MONTH( )	返回一个时间的月份部分
NOW( )	返回当前日期和时间
SECOND( )	返回一个时间的秒部分
TIME( )	返回一个日期时间的时间部分
YEAR( )	返回一个日期的年份部分

我们在使用日期和时间处理函数时需要注意日期格式，大多数时候，除非其他必要情况，我们都使用yyyy-mm-dd日期格式，因为这种日期格式排除的多义性，例如2022年12月12日，此格式为2022-12-12；但是其他格式可能表示为22/12/12，你无法确定是22年还是12年，是22日还是12日；

当我们需要将日期作为查询条件时：

输入：

SELECT cust_id,order_num,order_date
FROM orders
WHERE order_date = '2005-09-01';

输出：

因为样例表中的值的时间部分全部是00：00：00，但在实际情况中，并不是这样的，如果用当前日期和时间存储订单日期，比如存储的order_date值为2005-09-01 11：30：05，那么 WHERE order_date = '2005-09-01’并不会匹配任何结果，因为WHERE匹配失败；

我们需要指定MySql仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列进行比较。因此需要使用DATE( ) 函数，如：

输入：

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

输出：

这样的SQL语句才更为可靠，哪怕你知道order_date存储的只有日期，但是你只使用日期与之比较，也应该这样做，因为你不能确定之后的所有数据也只存储日期，万一之后存储的日期与时间，这时的代码根本无需改变；

另外，如果你想查询2005年9月的所有订单你应该怎么做呢？

输入：

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

输出：

当然，这只是其中一种方式，SQL的实现有很多种方式，我们只需要选择其中比较合适的一种即可，当然你也可以这样实现它：

输入：

SELECT cust_id,order_num,order_date
FROM orders
WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;

输出：

YEAR函数从日期中返回年份，month函数从日期中返回月份，同时满足年份和月份即返回匹配的结果；

4.数值处理函数

数值处理函数仅处理数值数据，这些函数主要用于代数等运算，下列是常用的数值计算函数：

函数	说明
ABS( )	返回一个数的绝对值
COS( )	返回一个角度的余弦
EXP( )	返回一个数的指数值
MOD( )	返回除操作的余数，即取余操作
PI( )	返回圆周率
RAND( )	返回一个随机数
SIN( )	返回一个角度的正弦
SQRT( )	返回一个数的平方根
TAN( )	返回一个角度的正切
ROUND( )	用于对数字进行四舍五入

对于数值处理函数我们最常用的就是ROUND( )函数，其用来对数字进行四舍五入。你可以指定一个数字并告诉 SQL 你要将它四舍五入到哪个小数位。

ROUND( )函数的常见语法如下：

ROUND(number, decimal_places)

• number 是你要四舍五入的数字。
• decimal_places 是你要四舍五入到的小数位。如果省略，则默认为 0，表示四舍五入到整数。

示例:

• 四舍五入到整数：

SELECT ROUND(123.4567);  -- 结果为 123

• 四舍五入到小数点后两位：

SELECT ROUND(123.4567, 2);  -- 结果为 123.46

• 四舍五入到小数点后一位：

SELECT ROUND(123.4567, 1);  -- 结果为 123.5