MySQL数学函数简明总结
1.ABS(x):返回x的绝对值
mysql>selectABS(1),ABS(-1),ABS(0); +--------+---------+--------+ |ABS(1)|ABS(-1)|ABS(0)| +--------+---------+--------+ | 1| 1| 0| +--------+---------+--------+
2.PI():返回圆周率
mysql>selectPI(); +----------+ |PI() | +----------+ |3.141593| +----------+
3.SQRT(x):返回x的平方根，要求（x为非负数，返回NULL）
mysql>selectSQRT(49),SQRT(0),SQRT(-49); +----------+---------+-----------+ |SQRT(49)|SQRT(0)|SQRT(-49)| +----------+---------+-----------+ | 7| 0| NULL| +----------+---------+-----------+
4.MOD(x,y):求余函数，返回x被y除后的余数；对于带有小数部分的数据值也起作用，它返回除法运算后的精确余数。
mysql>selectMOD(31,8),MOD(21,-8),MOD(-7,2),MOD(-7,-2),MOD(45.5,6); +-----------+------------+-----------+------------+-------------+ |MOD(31,8)|MOD(21,-8)|MOD(-7,2)|MOD(-7,-2)|MOD(45.5,6)| +-----------+------------+-----------+------------+-------------+ | 7| 5| -1| -1| 3.5| +-----------+------------+-----------+------------+-------------+
5.CEIL(X):返回不小X的最小整数值，返回值转为一个BIGINT.
mysql>selectCEIL(-3.35),CEIL(3.35); +-------------+------------+ |CEIL(-3.35)|CEIL(3.35)| +-------------+------------+ | -3| 4| +-------------+------------+
6.CEILING(X):同CEIL(X)
mysql>selectCEILING(-3.35),CEILING(3.35); +----------------+---------------+ |CEILING(-3.35)|CEILING(3.35)| +----------------+---------------+ | -3| 4| +----------------+---------------+
7.FLOOR(X):返回不大于X的最大整数值，返回值转为一个BIGINT.
mysql>selectFLOOR(-3.35),FLOOR(3.35); +--------------+-------------+ |FLOOR(-3.35)|FLOOR(3.35)| +--------------+-------------+ | -4| 3| +--------------+-------------+
8.RAND()和RAND(X)
RAND(X)返回一个随机浮点值，范围在０～１之间，X为整数，它被称作种子值，用来产生重复序列。即当Ｘ值相同时，产生的随机数也相同；
mysql>selectRAND(10),RAND(10),RAND(2),RAND(-2); +--------------------+--------------------+--------------------+--------------------+ |RAND(10) |RAND(10) |RAND(2) |RAND(-2) | +--------------------+--------------------+--------------------+--------------------+ |0.6570515219653505|0.6570515219653505|0.6555866465490187|0.6548542125661431| +--------------------+--------------------+--------------------+--------------------+
RAND():不带参数的RAND()每次产生不同０～１之间的随机数
mysql>SELECTRAND(),RAND(),RAND(); +--------------------+--------------------+---------------------+ |RAND() |RAND() |RAND() | +--------------------+--------------------+---------------------+ |0.6931893636409094|0.5147262984092592|0.49406343185721285| +--------------------+--------------------+---------------------+
9.ROUND(X)和ROUND(X,Y):四舍五入函数，对Ｘ值按照Ｙ进行四舍五入，Ｙ可以省略，默认值为０;若Ｙ不为０，则保留小数点后面指定Ｙ位。
mysql>selectROUND(-1.14),ROUND(-1.9),ROUND(1.14),ROUND(1.9); +--------------+-------------+-------------+------------+ |ROUND(-1.14)|ROUND(-1.9)|ROUND(1.14)|ROUND(1.9)| +--------------+-------------+-------------+------------+ | -1| -2| 1| 2| +--------------+-------------+-------------+------------+
mysql>selectROUND(1.38,1),ROUND(1.38,0),ROUND(232.38,-1),ROUND(232.38,-2); +---------------+---------------+------------------+------------------+ |ROUND(1.38,1)|ROUND(1.38,0)|ROUND(232.38,-1)|ROUND(232.38,-2)| +---------------+---------------+------------------+------------------+ | 1.4| 1| 230| 200| +---------------+---------------+------------------+------------------+