首页 > 代码库 > MysqL之数值函数
MysqL之数值函数
1.CEIL()
用法：向上取整
举例：
mysql> select CEIL(3.5);+-----------+| CEIL(3.5) |+-----------+| 4 |+-----------+1 row in set (0.05 sec)mysql> select CEIL(3.1);+-----------+| CEIL(3.1) |+-----------+| 4 |+-----------+1 row in set (0.00 sec)mysql> select CEIL(3.0);+-----------+| CEIL(3.0) |+-----------+| 3 |+-----------+1 row in set (0.00 sec)
2.DIV（DIV(X,Y)）
用法：数字除法，除数不能为0
举例：
mysql> select 3 DIV 4;+---------+| 3 DIV 4 |+---------+| 0 |+---------+1 row in set (0.03 sec)mysql> select 3 DIV 4.0;+-----------+| 3 DIV 4.0 |+-----------+| 0 |+-----------+1 row in set (0.00 sec)mysql> select 3.3 DIV 4;+-----------+| 3.3 DIV 4 |+-----------+| 0 |+-----------+1 row in set (0.00 sec)mysql> select 4.3 DIV 4;+-----------+| 4.3 DIV 4 |+-----------+| 1 |+-----------+1 row in set (0.00 sec)
3.FLOOR()
用法：向下取整（可以使正数可以使负数）
举例：
mysql> select FLOOR(2.3);+------------+| FLOOR(2.3) |+------------+| 2 |+------------+1 row in set (0.00 sec)mysql> select FLOOR(2.0);+------------+| FLOOR(2.0) |+------------+| 2 |+------------+1 row in set (0.00 sec)mysql> select FLOOR(-2.3);+-------------+| FLOOR(-2.3) |+-------------+| -3 |+-------------+1 row in set (0.00 sec)
4.MOD(MOD(X,Y))
用法：取余数（可以是正数可以是余数）
举例：
mysql> select 3 MOD 4;+---------+| 3 MOD 4 |+---------+| 3 |+---------+1 row in set (0.00 sec)mysql> select -3 MOD 4;+----------+| -3 MOD 4 |+----------+| -3 |+----------+1 row in set (0.00 sec)mysql> select -5 MOD 4;+----------+| -5 MOD 4 |+----------+| -1 |+----------+1 row in set (0.00 sec)mysql> select 5 MOD 4;+---------+| 5 MOD 4 |+---------+| 1 |+---------+1 row in set (0.00 sec)
5.POEWE()
用法：幂运算
举例：
mysql> select POWER(2,3);+------------+| POWER(2,3) |+------------+| 8 |+------------+1 row in set (0.03 sec)mysql> select POWER(2,-3);+-------------+| POWER(2,-3) |+-------------+| 0.125 |+-------------+1 row in set (0.00 sec)mysql> select POWER(2,2.2);+------------------+| POWER(2,2.2) |+------------------+| 4.59479341998814 |+------------------+1 row in set (0.03 sec)
6.ROUND()
用法：四舍五入
举例：
mysql> select ROUND(3.4);+------------+| ROUND(3.4) |+------------+| 3 |+------------+1 row in set (0.01 sec)mysql> select ROUND(3.5);+------------+| ROUND(3.5) |+------------+| 4 |+------------+1 row in set (0.00 sec)mysql> select ROUND(-3.5);+-------------+| ROUND(-3.5) |+-------------+| -4 |+-------------+1 row in set (0.00 sec)
7.TRUNCATE()
用法：数字截取（和正负数没有关系，只是截取位数）
举例：
mysql> select TRUNCATE(2.343434,3);+----------------------+| TRUNCATE(2.343434,3) |+----------------------+| 2.343 |+----------------------+1 row in set (0.00 sec)mysql> select TRUNCATE(2.343934,3);+----------------------+| TRUNCATE(2.343934,3) |+----------------------+| 2.343 |+----------------------+1 row in set (0.00 sec)mysql> select TRUNCATE(-2.343934,3);+-----------------------+| TRUNCATE(-2.343934,3) |+-----------------------+| -2.343 |+-----------------------+1 row in set (0.00 sec)
8.ABS()
用法：返回绝对值
举例：
mysql> select ABS(2);+--------+| ABS(2) |+--------+| 2 |+--------+1 row in set (0.03 sec)mysql> select ABS(-2);+---------+| ABS(-2) |+---------+| 2 |+---------+1 row in set (0.00 sec)
9.SIGN()
用法：返回符号（-1,0，1）
举例：
mysql> select SIGN(2) -> ;+---------+| SIGN(2) |+---------+| 1 |+---------+1 row in set (0.00 sec)mysql> select SIGN(-2) -> ;+----------+| SIGN(-2) |+----------+| -1 |+----------+1 row in set (0.00 sec)mysql> select SIGN(0);+---------+| SIGN(0) |+---------+| 0 |+---------+1 row in set (0.00 sec)
10.SQRT(N)
用法：求N的平方根
举例：
mysql> select SQRT(4);+---------+| SQRT(4) |+---------+| 2 |+---------+1 row in set (0.00 sec)mysql> select SQRT(3);+--------------------+| SQRT(3) |+--------------------+| 1.7320508075688772 |+--------------------+1 row in set (0.00 sec)
11.SIN(),TAN(),COS(),ACOS(),ASIN(),ATAN(),COT(),ASCOT()
用法：数学的三角函数
举例：
mysql> select SIN(30);+---------------------+| SIN(30) |+---------------------+| -0.9880316240928618 |+---------------------+1 row in set (0.00 sec)mysql> select SIN(PI());+------------------------+| SIN(PI()) |+------------------------+| 1.2246467991473532e-16 |+------------------------+1 row in set (0.00 sec)
12.DEGREES(N),RADIANS(N)
用法：把N从弧度变换为角度并返回（把N从角度变换为弧度并返回）
举例：
mysql> select DEGREES(PI());+---------------+| DEGREES(PI()) |+---------------+| 180 |+---------------+1 row in set (0.00 sec)mysql> select RADIANS(180);+-------------------+| RADIANS(180) |+-------------------+| 3.141592653589793 |+-------------------+1 row in set (0.00 sec)
13.LEAST(),GREATEST()
用法：返回最大值最小值
举例：
mysql> select LEAST(3,1,4,6,-1);+-------------------+| LEAST(3,1,4,6,-1) |+-------------------+| -1 |+-------------------+1 row in set (0.00 sec)mysql> select LEAST("B","A","C");+--------------------+| LEAST("B","A","C") |+--------------------+| A |+--------------------+1 row in set (0.00 sec)mysql> select GREATEST("B","A","C");+-----------------------+| GREATEST("B","A","C") |+-----------------------+| C |+-----------------------+1 row in set (0.00 sec)mysql> select GREATEST(2,4,5);+-----------------+| GREATEST(2,4,5) |+-----------------+| 5 |+-----------------+1 row in set (0.00 sec)
MysqL之数值函数
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。