一、函数的概述

  从函数定义的角度出发，我们可以将函数分为 内置函数 和 自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的；MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。

二、单行函数

单行函数只对一行进行变换，每行返回一个结果，它的参数可以是一列或一个值。单行函数可以嵌套。

2.1、数值函数

2.1.1、基本函数

ABS(x)              -- 返回x的绝对值
SIGN(X)             -- 返回X的符号。正数返回1，负数返回-1，0返回0 
PI()                --  返回圆周率的值
CEIL(x)             -- 返回大于或等于某个值的最小整数
CEILING(x)          -- 返回大于或等于某个值的最小整数
FLOOR(x)            -- 返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)    -- 返回列表中的最小值 
GREATEST(e1,e2,e3…) -- 返回列表中的最大值
MOD(x,y)            -- 返回X除以Y后的余数
RAND()              -- 返回0~1的随机值
RAND(x)             -- 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)            -- 返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)          -- 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)       -- 返回数字x截断为y位小数的结果
SQRT(x)             -- 返回x的平方根。当X的值为负数时，返回NULL

2.1.2、角度与弧度互换函数

RADIANS(x) -- 将角度转化为弧度，其中，参数x为角度值
DEGREES(x) -- 将弧度转化为角度，其中，参数x为弧度值

2.1.3、三角函数

SIN(x)     -- 返回x的正弦值，其中，参数x为弧度值
ASIN(x)    -- 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x)     -- 返回x的余弦值，其中，参数x为弧度值
ACOS(x)    -- 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)     -- 返回x的正切值，其中，参数x为弧度值
ATAN(x)    -- 返回x的反正切值，即返回正切值为x的值 
ATAN2(m,n) -- 返回两个参数的反正切值
COT(x)     -- 返回x的余切值，其中，X为弧度值

2.1.4、指数与对数

POW(x,y)     -- 返回x的y次方
POWER(X,Y)   -- 返回x的y次方
EXP(X)       -- 返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)        -- 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG(X)       -- 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X)     -- 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X)      -- 返回以2为底的X的对数，当X <= 0 时，返回NULL

2.1.5、进制间的转换

BIN(x)        -- 返回x的二进制编码
HEX(x)        -- 返回x的十六进制编码
OCT(x)        -- 返回x的八进制编码
CONV(x,f1,f2) -- 返回f1进制数变成f2进制数

2.2、字符串函数

ASCII(S)                          -- 返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)                    -- 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)                         -- 返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,......,sn)           -- 连接s1,s2,......,sn为一个字符串
CONCAT_WS(x, s1,s2,......,sn)     -- 同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr) -- 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)                -- 用字符串b替换字符串str中所有出现的字符串a 
UPPER(s)                          -- 将字符串s的所有字母转成大写字母
UCASE(s)                          -- 将字符串s的所有字母转成大写字母 
LOWER(s)                          -- 将字符串s的所有字母转成小写字母  
LCASE(s)                          -- 将字符串s的所有字母转成小写字母
LEFT(str,n)                       -- 返回字符串str最左边的n个字符
RIGHT(str,n)                      -- 返回字符串str最右边的n个字符
LPAD(str, len, pad)               -- 用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)               -- 用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)                          -- 去掉字符串s左侧的空格
RTRIM(s)                          -- 去掉字符串s右侧的空格
TRIM(s)                           -- 去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)                   -- 去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)           -- 去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)          -- 去掉字符串s结尾处的s1
REPEAT(str, n)                    -- 返回str重复n次的结果
SPACE(n)                          -- 返回n个空格
STRCMP(s1,s2)                     -- 比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)               -- 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同 
LOCATE(substr,str)                -- 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)                 -- 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)               -- 返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)                -- 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)                        -- 返回s反转后的字符串
NULLIF(value1,value2)             -- 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

注意：MySQL中，字符串的位置是从1开始的。

2.3、日期和时间函数

2.3.1、获取日期、时间

CURDATE()           -- 返回当前日期，只包含年、月、日
CURRENT_DATE()      -- 返回当前日期，只包含年、月、日
CURTIME()           -- 返回当前时间，只包含时、分、秒
CURRENT_TIME()      -- 返回当前时间，只包含时、分、秒
NOW()               -- 返回当前系统日期和时间
SYSDATE()           -- 返回当前系统日期和时间
CURRENT_TIMESTAMP() -- 返回当前系统日期和时间
LOCALTIME()         -- 返回当前系统日期和时间
LOCALTIMESTAMP()    -- 返回当前系统日期和时间
UTC_DATE()          -- 返回UTC（世界标准时间）日期
UTC_TIME()          -- 返回UTC（世界标准时间）时间

2.3.2、日期与时间戳的转换

UNIX_TIMESTAMP()         -- 以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(date)     -- 将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp) -- 将UNIX时间戳的时间转换为普通格式的时间

2.3.3、获取月份、星期、星期数、天数等函数

YEAR(date)          -- 返回具体的日期值
MONTH(date)         -- 返回具体的日期值
DAY(date)           -- 返回具体的日期值
HOUR(time)          -- 返回具体的时间值
MINUTE(time)        -- 返回具体的时间值  
SECOND(time)        -- 返回具体的时间值  
MONTHNAME(date)     -- 返回月份：January，...
DAYNAME(date)       -- 返回星期几：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date)       -- 返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)       -- 返回日期对应的季度，范围为1～4
WEEK(date)          -- 返回一年中的第几周
WEEKOFYEAR(date)    -- 返回一年中的第几周
DAYOFYEAR(date)     -- 返回日期是一年中的第几天
DAYOFMONTH(date)    -- 返回日期位于所在月份的第几天
DAYOFWEEK(date)     -- 返回周几，注意：周日是1，周一是2，。。。周六是7

2.3.4、日期的操作函数

EXTRACT(type FROM date) -- 返回指定日期中特定的部分，type指定返回的值

EXTRACT(type FROM date)函数中type的取值与含义：

type取值	含义
MICROSECOND	返回毫秒数
SECOND	返回秒数
MINUTE	返回分钟数
HOUR	返回小时数
DAY	返回天数
WEEK	返回日期在一年中的第几个星期
MONTH	返回日期在一年中的的第几个月
QUARTER	返回日期在一年中的第几个季度
YEAR	返回日期的年份
SECOND_MICROSECOND	返回秒和毫秒数
MINUTE_MICROSECOND	返回分钟和毫秒数
MINUTE_SECOND	返回分钟和秒值
HOUR_MICROSECOND	返回小时和毫秒值
HOUR_SECOND	返回小时和秒值
HOUR_MINUTE	返回小时和分钟值
DAY_MICROSECOND	返回天数和毫秒值
DAY_SECOND	返回天数和秒值
DAY_MINUTE	返回天和分钟值
SAY_HOUR	返回天和小时
YEAR_MONTH	返回年和月

2.3.5、时间和秒钟转换的函数

TIME_TO_SEC(time)    -- 将 time 转化为秒并返回结果值。转化的公式为：`小时*3600+分钟*60+秒`
SEC_TO_TIME(seconds) -- 将 seconds 描述转化为包含小时、分钟和秒的时间

2.3.6、计算日期和时间的函数

DATE_ADD(datetime, INTERVAL  expr type)  -- 返回与给定日期时间相差INTERVAL时间段的日期时间
ADDDATE(date,INTERVAL expr type)         -- 返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)        -- 返回与date相差INTERVAL时间间隔的日期
SUBDATE(date,INTERVAL expr type)         -- 返回与date相差INTERVAL时间间隔的日期
ADDTIME(time1,time2)                     -- 返回time1加上time2的时间。当time2为一个数字时，代表的是`秒`，可以为负数
SUBTIME(time1,time2)                     -- 返回time1减去time2后的时间。当time2为一个数字时，代表的是`秒`，可以为负数
DATEDIFF(date1,date2)                    -- 返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)                   -- 返回time1 - time2的时间间隔
FROM_DAYS(N)                             -- 返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)                            -- 返回日期date距离0000年1月1日的天数
LAST_DAY(date)                           -- 返回date所在月份的最后一天的日期
MAKEDATE(year,n)                         -- 针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)             -- 将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)                       -- 返回time加上n后的时间

间隔类型	含义
HOUR	小时
MINUTE	分钟
SECOND	秒
YEAR	年
MONTH	月
DAY	日
YEAR_MONTH	年和月
DAY_HOUR	日和小时
DAY_MINUTE	日和分钟
DAY_SECOND	日和秒
HOUR_MINUTE	小时和分钟
HOUR_SECOND	小时和秒
MINUTE_SECOND	分钟和秒

2.3.7、日期的格式化与解析

DATE_FORMAT(date,fmt)              -- 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)              -- 按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)  -- 返回日期字符串的显示格式
STR_TO_DATE(str, fmt)              -- 按照字符串fmt对str进行解析，解析为一个日期

上述非GET_FORMAT函数中fmt参数常用的格式符：

格式符	说明
%Y	4位数字表示年份
%y	两位数字表示年份
%M	月名表示月份（January,....）
%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，....）
%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）
%d	两位数字表示月中的天数(01,02...)
%e	数字形式表示月中的天数（1,2,3,4,5.....）
%H	两位数字表示小数，24小时制（01,02..）
%h和%I	两位数字表示小时，12小时制（01,02..）
%k	数字形式的小时，24小时制(1,2,3)
%l	数字形式表示小时，12小时制（1,2,3,4....）
%i	两位数字表示分钟（00,01,02）
%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）
%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday....)
%j	以3位数字表示年中的天数(001,002...)
%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制
%r	12小时制
%p	AM或PM
%%	表示%

GET_FORMAT函数中date_type和format_type参数取值如下：

日期类型	格式化类型	返回的格式化字符串
DATE	USA	%m.%d.%Y
DATE	JIS	%Y-%m-%d
DATE	ISO	%Y-%m-%d
DATE	EUR	%d.%m.%Y
DATE	INTERNAL	%Y%m%d
TIME	USA	%h:%i:%s %P
TIME	JIS	%H:%i:%s
TIME	ISO	%H:%i:%s
TIME	EUR	%H.%i/%s
TIME	INTERNAL	%H%i%s
DATETIME	USA	%Y-%m-%d %H.%i.%s
DATETIME	JIS	%Y-%m-%d %H:%i:%s
DATETIME	ISO	%Y-%m-%d %H.%i.%s
DATETIME	EUR	%y-%m-%d %H.%i.%s
DATETIME	INTERNAL	%Y%m%d%H%i%s

2.4、流程控制函数

IF(value,value1,value2)                                                  -- 如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)                                                   -- 如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END   -- 相当于Java的if...else if...else...
CASE  expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END -- 相当于Java的switch...case... 

2.5、加密与解密函数

PASSWORD(str)               -- 返回字符串str的加密版本，41位长的字符串。加密结果`不可逆`，MySQL8.0已弃用
MD5(str)                    -- 返回字符串str的md5加密后的值若参数为NULL，则会返回NULL
SHA(str)                    -- 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL
ENCODE(value,password_seed) -- 返回使用password_seed作为加密密码加密value，MySQL8.0已弃用
DECODE(value,password_seed) -- 返回使用password_seed作为加密密码解密value，MySQL8.0已弃用

2.6、MySQL信息函数

VERSION()              -- 返回当前MySQL的版本号
CONNECTION_ID()        -- 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()   -- 返回MySQL命令行当前所在的数据库
USER()                 -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CURRENT_USER()         -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
SYSTEM_USER()          -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
SESSION_USER()         -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)         -- 返回字符串value自变量的字符集
COLLATION(value)       -- 返回字符串value的比较规则

2.7、其他函数

FORMAT(value,n)                -- 返回对数字value进行格式化后的结果数据。n表示`四舍五入`后保留到小数点后n位
CONV(value,from,to)            -- 将value的值进行不同进制之间的转换
INET_ATON(ipvalue)             -- 将以点分隔的IP地址转化为一个数字
INET_NTOA(value)               -- 将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)              -- 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code) -- 将value所使用的字符编码修改为char_code

三、聚合函数

聚合函数是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。聚合函数不能嵌套调用。

3.1、常见的聚合函数类型

AVG()        -- 平均数函数
SUM()        -- 求和函数
MAX()        -- 最大值函数
MIN()        -- 最小值函数
COUNT()      -- 求总数函数

• AVG() 和 SUM() 只使用与数值类型的字段
• MAX() 和 MIN() 使用与任何数据类型
• COUNT(*)返回表中记录总数，适用于任意数据类型
• COUNT(expr) 返回expr不为空的记录总数
• AVG() = SUM()/COUNT()
• 聚合函数忽略NULL

使用 COUNT(*)、COUNT(1)、COUNT(具体字段) 统计表中的记录数，哪个效率更高？

• 如果使用的是MyISAM存储引擎，则三者效率相同，都是O(1)；
• 如果使用是InnoDB存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(具体字段)

3.2、GROUP BY的使用

SELECT 字段名1, 分组函数(字段名2)
FROM 表名
GROUP BY 字段名1;

• 可以使用GROUP BY子句将表中的数据分成若干组；
• 在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中，反之，GROUP BY中声明的字段可以不出现在SELECT中；
• 使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
• 当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的。

3.3、HAVING的使用

SELECT 字段名1, 分组函数1(字段名2)
FROM 表名
GROUP BY 字段名1;
HAVING 带分组函数的过滤条件;

• HAVING 必须声明在 GROUP BY 后面
• 如果过滤条件中使用了聚合函数，则必须使用 HAVING 来替换 WHERE。否则，报错。
• 当过滤条件没有聚合函数时，则此过滤条件中声明在 WHERE 中或 HAVING 中都可以，建议声明在 WHERE 中