MySQL自学笔记——函数

单行函数

只对一行进行变换，每行返回一个结果

数值函数

函数	用法
ABS(X)	返回x的绝对值
SIGN(X)	返回x的符号，正数返回1，负数返回-1，0返回0
PI()	返回圆周率
CEIL(X)  , CEILING(X)	返回大于或等于x的最小整数
FLOOR(X)	返回小于或等于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为负数，返回null
POW(X,Y),POWER(X,Y)	返回x的y次方
EXP(X)	返回e的x次方
LN(X),LOG(x)	返回以e为底，x的对数，x<=0时，返回null
LOG10(x)	返回以10为底，x的对数，x<=0时，返回null

示例代码：

SELECT RAND(1),RAND(1),RAND(10),RAND(10),ROUND(129.456,-1),TRUNCATE(129.456,-1)
FROM DUAL

结果截图：

字符串函数

函数	用法
ASCII()	返回s中第一个字符的ASCII码
CHAR_LENGTH(S)	返回s的字符数
LENGHT(s)	返回s的字节数
CONCAT(s1,s2,s3...)	链接s1,s2,s3...
CONCAT_WS        (x,s1,s2,s3...)	同上，但是每个字符串之间要加上x
INSERT(str,idx,len,replacestr)	将str从idx开始，len个字符长的子串替换为replacestr
REPLACE(str,a,b)	用b替换str中所有的a
UPPER(s)	s中所有的字母改为大写
LOWER(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个字符
LOCATE(substr,str)	返回substr在str中首次出现的位置，如果未找则到返回0
ELT(m,s1,s2,...,sn)	返回指定位置的字符串，如果m=1，则返回s1，其余类似
FIELD(s,s1,s2,...,sn)	返回s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回s1在s2中出现的位置(s2是一个以逗号分隔的字符串)
REVERSE(s)	返回s反转后的结果
NULLIF(value1,value2)	比较两个字符串，如果相等返回NULL，否则返回value1

 注意：在MySQL中，字符串的索引是从1开始

日期和时间函数

函数	用法
CURDATE()	返回当前日期(年月日)
CURTIME()	返回当前日期(时分秒)
NOW()	返回当前系统日期和时间
UTC_DATE()	返回世界标准时间的日期
UTC_TIME()	返回世界标准时间的时间
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份: January, ...
DAYNAME(date)	返回星期几: MONDAY, TUESA....SNDA
WEEKDAY(date)	返回周几，注意,周1是0,周2是1,。。。周日是6
QUARTER(date)	返回日期对应的季度，范围为1 ~4
WEEK(date)，WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意:周日是1，周一是2，。。。周六是7

流程控制函数

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

加密与解密函数

函数	用法
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL,则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。
ENCODE(value,password seed)	返回使用password_ seed作为加密密码加密value
DECODE(value,password_ seed)	返回使用password seed作为加密密码解密value

聚合函数

作用于一组数据，并对一组数据返回一个值

AVG()/SUM()

作用:求平均值/总和

注意:只适用于数值类型的字段,不计算为NULL的值

MIX()/MIN()

作用:求最大值/最小值

适用于字符串类型、数值类型、日期时间类型的字段

COUNT()

作用:计算指定字段在查询结构中出现的个数

注意:不计算为NULL的值

如果要查询表中有多少条记录，可以使用COUNT(*),COUNT(1)

GROUP BY()

注意

SELECT语句中出现的非组函数的字段必须声明在GROUP BY中。反之，GROUP BY中声明的字段可以不出现在SELECT中。

GROUP BY声明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面

HAVING

如果过滤条件使用了聚合函数，则需要HAVING来替换WHERE。

当过滤条件中没有聚合函数时，则过滤条件声明在WHERE或HAVING中都可以。但是声明在WHERE中效率高。

HAVING必须声明在GROUP BY后面