MySQL常用函数总结
MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等
1. 数学函数
ABS(x) #返回x的绝对值
PI() #返回圆周率π,默认显示6位小数
SQRT(x) #返回非负数的x的二次方根
MOD(x,y) #返回x被y除后的余数
CEIL(x)、CEILING(x) #返回不小于x的最小整数
FLOOR(x) #返回不大于x的最大整数
ROUND(x)、ROUND(x,y) #前者返回最接近于x的整数,即对x进行四舍五入;后者返回最接近x的数,
#其值保留到小数点后面y位,若y为负值,则将保留到x到小数点左边y位
SIGN(x) #返回参数x的符号,-1表示负数,0表示0,1表示正数
POW(x,y)和、POWER(x,y) #返回x的y次乘方的值
EXP(x) #返回e的x乘方后的值
LOG(x) #返回x的自然对数,x相对于基数e的对数
LOG10(x) #返回x的基数为10的对数
RADIANS(x) #返回x由角度转化为弧度的值
DEGREES(x) #返回x由弧度转化为角度的值
SIN(x)、ASIN(x) #前者返回x的正弦,其中x为给定的弧度值;后者返回x的反正弦值,x为正弦
COS(x)、ACOS(x) #前者返回x的余弦,其中x为给定的弧度值;后者返回x的反余弦值,x为余弦
TAN(x)、ATAN(x) #前者返回x的正切,其中x为给定的弧度值;后者返回x的反正切值,x为正切
COT(x) #返回给定弧度值x的余切
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col) #返回指定列的平均值
COUNT(col) #返回指定列中非NULL值的个数
MIN(col) #返回指定列的最小值
MAX(col) #返回指定列的最大值
SUM(col) #返回指定列的所有值之和
GROUP_CONCAT(col) #返回由属于一组的列值连接组合而成的结果
三、字符串函数
ASCII(char) #返回字符的ASCII码值
BIT_LENGTH(str) #返回字符串的比特长度
CONCAT(s1,s2...,sn)将s1,s2...,sn #连接成字符串
CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn #连接成字符串,并用sep字符间隔
INSERT(str,x,y,instr) #将字符串str从第x位置开始,y个字符长的子
#串替换为字符串instr,返回结果
FIND_IN_SET(str,list) #分析逗号分隔的list列表,如果发现str,返回str在list中的位置
LCASE(str)或LOWER(str) #返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x) #返回字符串str中最左边的x个字符
LENGTH(s) #返回字符串str中的字符数
LTRIM(str) #从字符串str中切掉开头的空格
POSITION(substr,str) #返回子串substr在字符串str中第一次出现的位置
QUOTE(str) #用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr) #返回字符串str重复x次的结果
REVERSE(str) #返回颠倒字符串str的结果
RIGHT(str,x) #返回字符串str中最右边的x个字符
RTRIM(str) #返回字符串str尾部的空格
STRCMP(s1,s2) #比较字符串s1和s2
TRIM(str) #去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) #返回将字符串str中所有字符转变为大写后的结果
四、日期和时间函数
CURDATE()或CURRENT_DATE() #返回当前的日期
CURTIME()或CURRENT_TIME() #返回当前的时间
DATE_ADD(date,INTERVAL int keyword) #返回日期date加上间隔时间int的结果(int必须按照
#关键字进行格式化),如:
SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) #依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword) #返回日期date加上间隔时间int的结果(int必须按照
#关键字进行格式化),如:
SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date) #返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) #返回date是一个月的第几天(1~31)
DAYOFYEAR(date) #返回date是一年的第几天(1~366)
DAYNAME(date) #返回date的星期名,如:
SELECT DAYNAME(CURRENT_DATE)
FROM_UNIXTIME(ts,fmt) #根据指定的fmt格式,格式化UNIX时间戳ts
HOUR(time) #返回time的小时值(0~23)
MINUTE(time) #返回time的分钟值(0~59)
MONTH(date) #返回date的月份值(1~12)
MONTHNAME(date) #返回date的月份名,如:
SELECT MONTHNAME(CURRENT_DATE);
NOW() #返回当前的日期和时间
QUARTER(date) #返回date在一年中的季度(1~4),如
SELECT QUARTER(CURRENT_DATE)
WEEK(date) #返回日期date为一年中第几周(0~53)
YEAR(date) #返回日期date的年份(1000~9999)
#一些示例:
#获取当前系统时间:
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);
SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);
SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);
#返回两个日期值之间的差值(月数):
SELECT PERIOD_DIFF(200302,199802);
#在Mysql中计算年龄:
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age
FROM employee;
#这样,如果Brithday是未来的年月日的话,计算结果为0。
#下面的SQL语句计算员工的绝对年龄,即当Birthday是未来的日期时,将得到负值。
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(),
'00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee
五、加密函数
AES_ENCRYPT(str,key) #返回用密钥key对字符串str利用高级加密标准算法加密后的结果,
#调用AES_ENCRYPT的结果是一个二进制字符串,以BLOB类型存储
AES_DECRYPT(str,key) #返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,key) #使用key作为密钥解密加密字符串str
ENCRYPT(str,salt) #使用UNIXcrypt()函数,用关键词salt(一个可以惟一确定口令的字符串,
#就像钥匙一样)加密字符串str
ENCODE(str,key) #使用key作为密钥加密字符串str,调用ENCODE()的结果是一个
#二进制字符串它以BLOB类型存储
MD5() #计算字符串str的MD5校验和
PASSWORD(str) #返回字符串str的加密版本,这个加密过程是不可逆转的,
#和UNIX密码加密过程使用不同的算法。
SHA() #计算字符串str的安全散列算法(SHA)校验和
示例:
SELECT ENCRYPT('root','salt');
SELECT ENCODE('xufeng','key');
SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起
SELECT AES_ENCRYPT('root','key');
SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');
SELECT MD5('123456');
SELECT SHA('123456');
六、格式化函数
DATE_FORMAT(date,fmt) #依照字符串fmt格式化日期date值
FORMAT(x,y) #把x格式化为以逗号隔开的数字序列,y是结果的小数位数
INET_ATON(ip) #返回IP地址的数字表示
INET_NTOA(num) #返回数字所代表的IP地址
TIME_FORMAT(time,fmt) #依照字符串fmt格式化时间time值
#其中最简单的是FORMAT()函数,它可以把大的数值格式化为以逗号间隔的易读的序列。
#示例:
SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i %p');
SELECT INET_ATON('10.122.89.47');
SELECT INET_NTOA(175790383);
七、类型转化函数
#为了进行数据类型转化,MySQL提供了CAST()函数,它可以把一个值转化为指定的数据类型。类型有:
BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
#示例:
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY
);