以下内容基于MySQL 5.6及更高,大部分函数5.5也基本适用,更低版本请参考对应版本手册,其内容整理自官方。
mysql常用函数被分为五类,分别为:
①Numeric:数字函数;
②String:字符串函数;
http://blog.itpub.net/29773961/viewspace-1813545/
③Date and time:日期和时间;
http://blog.itpub.net/29773961/viewspace-1808967/
④Control Flow:基于一个表达式的结果集选择不同的值(控制流函数);
http://blog.itpub.net/29773961/viewspace-1813557/
⑤Aggregate:基于一列的多个值返回单一值(聚合函数);
http://blog.itpub.net/29773961/viewspace-1813589/
【Numeric 】:
ABS(): 返回绝对值
ACOS() :返回arccos
ASIN ():返回arcsin
ATAN ():返回arctan
CEIL ()与CEILING():向上取整
CONV() :不同进制的转换
COS ():返回余弦值
COT ():返回余切值
SIGN() :返回正负(-1=负值,0=零,1=正值)
SIN():返回正弦值
SQRT():将某值开平方(当然用POW(expr1,1/2)也可以)
TAN () :返回正切值
TRUNCATE(expr1,expr2) :将expr1返回以保留expr2个小数点
作者公众号(持续更新)
mysql常用函数被分为五类,分别为:
①Numeric:数字函数;
②String:字符串函数;
http://blog.itpub.net/29773961/viewspace-1813545/
③Date and time:日期和时间;
http://blog.itpub.net/29773961/viewspace-1808967/
④Control Flow:基于一个表达式的结果集选择不同的值(控制流函数);
http://blog.itpub.net/29773961/viewspace-1813557/
⑤Aggregate:基于一列的多个值返回单一值(聚合函数);
http://blog.itpub.net/29773961/viewspace-1813589/
【Numeric 】:
ABS(): 返回绝对值
- mysql> SELECT ABS(-22), ABS(0), ABS(33);
- +----------+--------+---------+
- | ABS(-22) | ABS(0) | ABS(33) |
- +----------+--------+---------+
- | 22 | 0 | 33 |
- +----------+--------+---------+
- 1 row in set (0.00 sec)
ACOS() :返回arccos
ASIN ():返回arcsin
ATAN ():返回arctan
CEIL ()与CEILING():向上取整
- mysql> SELECT CEIL(-1.2), CEIL(1.8), CEILING(0.4), CEILING(0.8);
- +------------+-----------+--------------+--------------+
- | CEIL(-1.2) | CEIL(1.8) | CEILING(0.4) | CEILING(0.8) |
- +------------+-----------+--------------+--------------+
- | -1 | 2 | 1 | 1 |
- +------------+-----------+--------------+--------------+
- 1 row in set (0.00 sec)
CONV() :不同进制的转换
- mysql> SELECT CONV('a',16,2) 将16进制数a转换为2进制,
- -> CONV('6E',18,8),
- -> CONV(10+'10'+'10'+0xa,10,10);
- +---------------------------------+-----------------+------------------------------+
- | 将16进制数a转换为2进制 | CONV('6E',18,8) | CONV(10+'10'+'10'+0xa,10,10) |
- +---------------------------------+-----------------+------------------------------+
- | 1010 | 172 | 40 |
- +---------------------------------+-----------------+------------------------------+
- 1 row in set (0.00 sec)
COS ():返回余弦值
COT ():返回余切值
CRC32()
:返回循环冗余校验值
DEGREES ():将弧度变为角度
EXP() :返回以e为底的某次方的值
FLOOR ():返回最大不超过表达式的整数
LN ():返回log以e为底,某指的对数
LOG10 ():返回log以10为底,某值的对数
LOG2 ():返回log以2为底,某值的对数
LOG(expr1,expr2):返回以expr1为底数,expr2值的对数
MOD(expr1,expr2):返回expr1模expr2的值。
PI():返回π的值
POW(expr1,expr2)与POWER(expr1,expr2):返回expr1的expr2次方的值
RADIANS() :将角度变为弧度
RAND () :返回一个浮点随机数(0~1开区间)
ROUND () :将某值四舍五入
DEGREES ():将弧度变为角度
- mysql> SELECT DEGREES(PI());
- +---------------+
- | DEGREES(PI()) |
- +---------------+
- | 180
- +---------------+
- 1 row in set (0.01 sec)
EXP() :返回以e为底的某次方的值
FLOOR ():返回最大不超过表达式的整数
- mysql> SELECT FLOOR(1.34), FLOOR(-0.1112), FLOOR(2.56);
- +-------------+----------------+-------------+
- | FLOOR(1.34) | FLOOR(-0.1112) | FLOOR(2.56) |
- +-------------+----------------+-------------+
- | 1 | -1 | 2 |
- +-------------+----------------+-------------+
- 1 row in set (0.00 sec)
LN ():返回log以e为底,某指的对数
LOG10 ():返回log以10为底,某值的对数
LOG2 ():返回log以2为底,某值的对数
LOG(expr1,expr2):返回以expr1为底数,expr2值的对数
MOD(expr1,expr2):返回expr1模expr2的值。
PI():返回π的值
POW(expr1,expr2)与POWER(expr1,expr2):返回expr1的expr2次方的值
- mysql> SELECT POW(1,99), POWER(1,99),
- -> POW(2,10), POWER(-2,5);
- +-----------+-------------+-----------+-------------+
- | POW(1,99) | POWER(1,99) | POW(2,10) | POWER(-2,5) |
- +-----------+-------------+-----------+-------------+
- | 1 | 1 | 1024 | -32 |
- +-----------+-------------+-----------+-------------+
- 1 row in set (0.02 sec)
RADIANS() :将角度变为弧度
- mysql> SELECT RADIANS(180);
- +-------------------+
- | RADIANS(180) |
- +-------------------+
- | 3.141592653589793 |
- +-------------------+
- 1 row in set (0.00 sec)
RAND () :返回一个浮点随机数(0~1开区间)
- mysql> SELECT RAND();
- +--------------------+
- | rand() |
- +--------------------+
- | 0.7237686484342148 |
- +--------------------+
- 1 row in set (0.00 sec)
ROUND () :将某值四舍五入
- mysql> SELECT ROUND(PI()), ROUND(-2.333), ROUND(-2.7666), ROUND(3.611);
- +-------------+---------------+----------------+--------------+
- | ROUND(PI()) | ROUND(-2.333) | ROUND(-2.7666) | ROUND(3.611) |
- +-------------+---------------+----------------+--------------+
- | 3 | -2 | -3 | 4 |
- +-------------+---------------+----------------+--------------+
- 1 row in set (0.00 sec)
SIGN() :返回正负(-1=负值,0=零,1=正值)
- mysql> SELECT SIGN(-3.14), SIGN(0), SIGN(22.1)
- -> ;
- +-------------+---------+------------+
- | SIGN(-3.14) | SIGN(0) | SIGN(22.1) |
- +-------------+---------+------------+
- | -1 | 0 | 1 |
- +-------------+---------+------------+
- 1 row in set (0.00 sec)
SIN():返回正弦值
SQRT():将某值开平方(当然用POW(expr1,1/2)也可以)
- mysql> SELECT SQRT(1024),SQRT(9);
- +------------+---------+
- | SQRT(1024) | SQRT(9) |
- +------------+---------+
- | 32 | 3 |
- +------------+---------+
- 1 row in set (0.00 sec)
TAN () :返回正切值
TRUNCATE(expr1,expr2) :将expr1返回以保留expr2个小数点
- mysql> SELECT TRUNCATE(2.111111,3);
- +----------------------+
- | TRUNCATE(2.111111,3) |
- +----------------------+
- | 2.111 |
- +----------------------+
- 1 row in set (0.00 sec)
作者公众号(持续更新)
![](http://img.blog.itpub.net/blog/attachment/201805/23/29773961_15270649487W32.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29773961/viewspace-1813556/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29773961/viewspace-1813556/