MySQL 常用函数
MySQL数据库中提供了丰富的函数。包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。
一、数学函数
ABS(X) 返回 X 的绝对值。 正数的绝对值是其本身，负数的绝对值为其相反数，0 的绝对值是 0 PI() 圆周率 π，默认显示6位小数 SIN(X) 正弦 ACOS(X) 反余弦函数 X 值必须在 -1 ~ 1之间，否则返回NULL ASIN(X) 反正弦函数 X 值必须在 -1 ~ 1之间，否则返回NULL TAN(X) 正切值 返回 X 的切线，以弧度表示 ATAN(x) 反正切函数 COS(X) 余弦 X的值以弧度给出 COT(X) 余切 MOD(N,M) 余数 返回 N 除以 M 的余数 DEGREES(X) 弧度转换为度 RADIANS(X) 角度转弧度 CEIL(X) 向上取整 取最小整数 CEILING(X) 向上取整 取最小整数 FLOOR(X) 向下取整 ROUND(X,D) 四舍五入 将参数 X 保留小数点后 D 位的值四舍五入 SQRT(X) 返回非负平方根 EXP(X) 将自然对数的底数 e 提升为指定数字 X 的幂 FORMAT(X,D) 格式化数字 X 需要格式化的数字；D 需要保留的小数位 TRUNCATE(X,D) 截断数字 将X的值截断为D的小数位数。 如果D为0，则删除小数点。 如果D为负，则该值的整数部分中的D个值将被截断 POW/POWER(X,Y) 返回数幂 返回值为 X 的 Y 次方 SIGN(X) 数字的符号 返回 X 的符号,负数为 -1,0 为0，正数为 1 GREATEST(N1,N2,......) 返回输入参数集中的最大值 INTERVALN,N1,N2,N3...) 比较数字 将N的值与值列表（N1，N2，N3等）进行比较。 如果N <N1，则函数返回0， 如果N <N2，则函数返回1， 如果N <N3，则函数返回2，依此类推。 如果N为NULL，它将返回.1。 LEAST(N1,N2,N3,..) 返回最小值 从值列表中返回最小的值（N1，N2，N3...） LOG(X)/LOG(B/X) 返回自然对数 单参数，将返回X的自然对数 双参数，将返回任意基数B的X的对数 LOG10(X) X的以10为底的对数 CONV(num，from_base，to_base) 进制转换，将数字表达式从一个基数转换为另一个基数。 num 要转换的值； from_base 数字的现有基数/进制； to_base 需要转换的基数/进制 如果任何参数为NULL，则函数返回NULL。 OCT(X) 转换为 8 进制数 等于 CONV(NUM,10,8) BIN(X) 返回二进制字符串表示 返回 X 的二进制值的字符串表示形式， X是一个long long（BIGINT）数字。 等于 CONV(NUM,10,2) 如果 X 为 NULL，则返回 NULL
SQRT(N) 返回数值的平方根
RAND() 返回一个随机数
SUM 用于计算数据的总和
AVG 用于查找各种记录中某个字段的平均值
MIN 用于在记录集中找出最小值的记录
MAX 用于在记录集中找出最大值的记录
COUNT 计算返回的记录数
二、字符串函数
ASCII(str) 返回字符串第一个字符的ASCII码
如果 str 是空字符串，则返回0。 如果 str 为 NULL ，则返回 NULL
BIT_LENGTH(str) 返回字符串的长度
以位为单位
CHAR_LENGTH/CHARACTER_LENGTH(str) 返回字符串的长度
以字符为单位，多字节字符算作单个字符
LENGTH(str) 返回字符串长度
以字节为单位；多字节字符计为多个字节
CONCAT_WS(separator,str1,str2,...) 用分隔符连接字符串
separator 参数为分隔符
分隔符为 NULL ，则结果为 NULL
CONCAT(str1,str2,...) 连接一个或者多个字符串
返回结果为连接参数产生的字符串
如有任何一个参数为 NULL ，则返回值为 NULL
ELT(N,str1,str2,...) 返回对应位置的字符串
如 N = 1，返回 str1 ；如果 N = 2，返回str2，依此类推
如 N 小于 1 或大于参数个数，则返回 NULL
SUBSTRING_INDEX(str,delim,count) 返回字串
str 原字符串 ；selim 定界符 ；count 返回值数量
如 count 为正，则返回最终定界符左侧的所有内容（从左侧开始计数）
如 count 为负，则返回最终定界符右边的所有内容（从右边开始计数）
搜索 delim 时，区分大小写
FIELD(str,str1,str2,...) 返回对应字符串的索引
返回 str1，str2，...列表中 str 的索引（从1开始的位置）
如果找不到 str，则返回0
INSERT(str,pos,len,newstr) 替换字符串
str 操作/返回字符串；pos 替换开始位置
len 替换字符长度 ； newstr 需要替换的字符串
如果 pos 不在字符串的长度内，则返回原始字符串
如果 len 不在字符串其余部分，自 pos 位置替换字符串的其余部分
如果任何参数为 NULL，则返回 NULL
REPLACE(str,from_str,to_str) 字符串替换
str 原字符串；from_str 替换目标字符串；to_str 替换后的字符串
搜索from_str时，区分大小写
SUBSTRING(str,pos) 自特定位置返回一个新字符串
str 原字符串 ；pos 返回字符串的起始位置
LPAD(str,len,padstr) 填充字符串左边
str 原字符串 ；len 填充后的字符串长度；padstr 需要填充的字符串
如果str大于len，则返回值缩短为len个字符
RPAD(str,len,padstr) 填充字符串右边
str 原字符串 ；len 填充后的字符串长度；padstr 需要填充的字符串
如果str大于len，则返回值缩短为len个字符
LTRIM(str) 删除字符串左边的空格
RTRIM(str) 删除字符串右边的空格
SUBSTRING(str FROM pos) 同上；FROM 关键字的函数语法是标准的 MySQL 语法
SUBSTRING(str,pos,len) 自特定位置返回一个给定长度的新字符串
str 原字符串；pos 返回字符串的起始位置；len 返回字符串长度
SUBSTRING(str FROM pos FOR len) 同上；FROM 和FOR 关键字的函数语法是标准的 MySQL 语法
INSTR(str,substr) 返回子字符串在字符串中首次出现的位置
str：需要被搜索的字符串 ；substr 需要搜素的字符串
LOCATE(substr,str,pos) 返回字符串的子字符串的位置
LOCATE(substr,str) 返回字符串str中子字符串substr首次出现的位置，从位置pos开始
返回子字符串substr在字符串str中首次出现的位置
如果substr不在str中，则返回0
LOWER/LCASE(str) 将字符串小写
UPPER(str)/UCASE(str) 将字符串大写
LEFT(str,len) 返回指定长度的字符串
自左侧部分开始
str 要提取字符串；len 一个正整数，指定返回的字符数
如果任意参数为NULL返回NULL
RIGHT(str,len) 返回指定长度的字符串
自右侧部分开始
str 要提取字符串；len 一个正整数，指定返回的字符数
如果任意参数为NULL返回NULL
(expr)REGEXP(pattern) 正则匹配
expr 需匹配的参数 ；pattern 正则表达式
如果匹配，则返回1；否则返回0
如 expr 或 pattern 为 NULL，则结果为NULL
REGEXP 不区分大小写
REPEAT(str,count) 字符串重复
返回一个由重复计数次数的字符串str组成的字符串
如 count 小于1，返回空字符串；如 str/count 为 NULL，返回 NULL
REVERSE(str) 字符串反转
SPACE(N) 返回 N 个空格
STRCMP(str1, str2) 比较两个字符串
如果两个字符串相等，返回0；
如果根据当前的排序顺序，str1小于str2，返回-1；否则返回1
三、日期和时间函数
NOW() 返回当前的日期和时间
格式：YY-mm-dd HH:MM:SS
SYSDATE() 返回当前系统时间
格式：YYYY-MM-DD HH:MM:SS
HOUR(time) 返回时间的小时部分
SECOND(time) 返回时间的秒数
范围为0到59
MINUTE(expr) 自表达式中返回时间分钟
范围为0到59
TIME(expr) 返回表达式的时间部分
CURDATE() 返回当前日期
CURTIME() 返回当前时间
DAYOFMONTH(date) 返回日期
返回日期中月份的日期，范围为0到31
DAY(date) 返回日期（同上）
MONTH(date) 返回日期的月份
QUARTER(date) 从日期参数返回季度
范围为1到4
DAYOFWEEK(date) 返回日期的星期几索引
1 = 星期日，2 = 星期一，..，7 = 星期六
MICROSECOND(expr) 返回时间或日期时间表达式expr的微秒
DAYOFYEAR(date) 返回日期中的某一天
范围为1到366
DAYNAME(date) 返回日期的工作日名称
如：Sunday
YEAR(date) 返回年份
LAST_DAY(date) 返回该月最后一天的值
如果参数无效，则返回NULL
DATE(datetime) 提取日期部分
参数是合法的日期表达式
DATE_FORMAT(date,format) 格式化日期
date 参数是合法的日期；
format 规定日期/时间的输出格式；允许的格式参数见备注
FROM_DAYS(N) 返回指定天数的日期
N 指定天数
UTC_DATE() 返回UTC日期
返回格式：YYYY-MM-DD
UTC_DATE() + 0，返回格式：YYYYMMDD
UTC_TIME() 返回UTC时间
返回格式：HH：MM：SS
UTC_TIME() + 0，返回格式：HHMMSS
UTC_TIMESTAMP() 返回UTC日期时间
返回格式：YYYY-MM-DD HH：MM：SS
UTC_TIMESTAMP() + 0，返回格式：YYYYMMDDHHMMSS
四、条件判断函数
IF(expr,value1,value2) 如果 expr 是 TRUE 则返回 value1，否则返回 value2
IFNULL(value1,value2) 如果 v1 不为 NULL，则返回 value1，否则返回 value2
case when
用法一，简单函数 枚举这个字段所有可能的值
CASE [field_name] WHEN [value1] THEN [result1]…ELSE [default] END
示例：SELECT name,CASE month WHEN '大宝' THEN '护手霜'
WHEN '冠益乳' THEN '酸奶'
WHEN '清风' THEN ‘纸巾'
ELSE '停用'
END '物品'
FROM order_info;
用法二，搜索函数 可以做判断，并且搜索函数只会返回第一个符合条件的值，其他 case 被忽略
CASE WHEN [expr] THEN [result1]…ELSE [default] END
示例：SELECT id,name, age '年龄',CASE WHEN age < 18 THEN '少年'
WHEN age < 30 THEN '青年'
WHEN age >= 30 AND age < 50 THEN '中年'
ELSE '老年'
END '年龄状态'
FROM user_info;
用法三，聚合函数 count/sum 配合 case when 实现行转列
示例一：SELECT id,name,
COUNT(CASE WHEN phone LIKE '18%' THEN phone ELSE 0 END) AS phone19x,
COUNT(CASE WHEN phone LIKE '18%' THEN phone ELSE 0 END) AS phone18x
FROM `user_info`
示例二：SELECT id,name,
SUM(CASE co.course_name WHEN 'JAVA程序设计' THEN sc.scores ELSE 0 END) 'JAVA程序设计',
SUM(CASE co.course_name WHEN '计算机应用基础' THEN sc.scores ELSE 0 END) '计算机应用基础'
FROM student_info
五、系统信息函数
VERSION() 查看MySQL版本号
DATABASE() 查看当前使用数据库名
CONNECTION_ID() 返回当前客户的连接ID
USER 查看当前被MySQL服务器验证的用户名和主机的组合
同：CURRENT_USER()、SYSTEM_USER()、SESSION_USER()
......
六、加密函数
PASSWORD(str) 返回 str 字符串加密字符串
函数单向加密，不可逆
MD5(str)
SHA(str)
SHA2(str)
ENCODE(str, key_str) str 需要加密的字符串；key_str 秘钥
解密 DECODE(crypt_str,key_str)
AES_ENCRYPT(str,key_str) str 需要加密的字符串；key_str 秘钥
解密：AES_DECRYPT(crypt_str,key_str)
DES_ENCRYPT(str,[key_num|key_str]) key_num 用于从DES key 文件中指定一个介于0到9之间的数字
key_str 加密秘钥
解密：DES_ENCRYPT(str,[key_num|key_str])
