mysql---使用函数处理数据

SQL支持利用函数来处理数据，不同的DBMS都有自己特有的函数，移植性差，所以决定使用函数要做好注释，方便在移植到其他DBMS时进行修改。

1. 函数类型

• 文本串处理函数
• 数值计算函数
• 日期时间处理函数
• 系统函数(用户信息，版本信息等)

1.1 文本处理函数

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

SOUNDEX是查找发音类似的字段，仅对英文有效。
假如数据库中又一个名字’Lee‘，但实际是’Lie‘，那么使用’Lie‘是查不到的，而使用Soundex()就可以找到。

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Lie')

1.2 日期和时间处理函数

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

注意mysql使用的日期格式，无论什么时候指定一个日期，不管是插入、更新、过滤，日期格式必须是yyyy-mm-dd.

1.3 数值处理函数

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

2. 聚集函数

在一些应用场景下，需要得到数据的汇总信息而不用检索出来，mysql提供了5个专门的函数汇总数据。

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

COUNT()函数由两种使用方式：
- 使用COUNT(*)对表中行的数目进行计数，无论表列中包含的是空值(NULL)还是非空值
- 使用COUNT(column)对特定的列中有值的行进行计数，忽略NULL值

MAX()函数忽略值为NULL的行
虽然MAX()一般用来找出最大的数值或日期，但mysql允许返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按照相应的列排序，则MAX()返回最后一行。
MIN()函数与MAX()函数正好相反

SUM()函数忽略值为NULL的行

3. 聚集不同值

使用DISTINCT函数可以只计算不同的值
例如，使用AVG()函数返回本选择的供应商提供的产品的平均价格，但使用了DISTINCT参数后，平均价格只考虑各个不同的价格：

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

如果指定列名，则DISTINCT只能用于COUNT().DISTINCT不能用于COUNT(*)，因此不允许使用COUNT(DISTINCT)，否则会产生错误。DISTINCT不能用于计算或表达式。

4. 组合聚集函数

多个聚集函数可以在同一个SELECT语句中使用：

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