数学函数
绝对值函数ABS(x)
mysql> select abs(-1), abs(-23), abs(34); +---------+----------+---------+ | abs(-1) | abs(-23) | abs(34) | +---------+----------+---------+ | 1 | 23 | 34 | +---------+----------+---------+ 1 row in set (0.00 sec)圆周率的函数PI()
mysql> select pi(); +----------+ | pi() | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec)平方根函数SQRT(x)
mysql> select sqrt(4), sqrt(5), sqrt(36), sqrt(-4); +---------+------------------+----------+----------+ | sqrt(4) | sqrt(5) | sqrt(36) | sqrt(-4) | +---------+------------------+----------+----------+ | 2 | 2.23606797749979 | 6 | NULL | +---------+------------------+----------+----------+ 1 row in set (0.00 sec)求余函数MOD(x,y)
mysql> select mod(5,3), mod(34, 7), mod(-34, -4), mod(-24, 3); +----------+------------+--------------+-------------+ | mod(5,3) | mod(34, 7) | mod(-34, -4) | mod(-24, 3) | +----------+------------+--------------+-------------+ | 2 | 6 | -2 | 0 | +----------+------------+--------------+-------------+ 1 row in set (0.00 sec)获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)，CEIL(x)和CEILING(x)的意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。
mysql> select ceil(1.5), ceiling(2.3), floor(5.5); +-----------+--------------+------------+ | ceil(1.5) | ceiling(2.3) | floor(5.5) | +-----------+--------------+------------+ | 2 | 3 | 5 | +-----------+--------------+------------+ 1 row in set (0.00 sec) mysql> select ceil(-1.5), ceiling(-2.3), floor(-5.5); +------------+---------------+-------------+ | ceil(-1.5) | ceiling(-2.3) | floor(-5.5) | +------------+---------------+-------------+ | -1 | -2 | -6 | +------------+---------------+-------------+ 1 row in set (0.00 sec)获取随机数的函数RAND()和RAND(x)，当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。
mysql> select rand(), rand(), rand(); +----------------------+---------------------+--------------------+ | rand() | rand() | rand() | +----------------------+---------------------+--------------------+ | 0.009510553450799132 | 0.13407438167750313 | 0.6418402787687632 | +----------------------+---------------------+--------------------+ 1 row in set (0.00 sec) mysql> select rand(12), rand(12), rand(12), rand(23); +---------------------+---------------------+---------------------+--------------------+ | rand(12) | rand(12) | rand(12) | rand(23) | +---------------------+---------------------+---------------------+--------------------+ | 0.15741774081943347 | 0.15741774081943347 | 0.15741774081943347 | 0.9094319407915994 | +---------------------+---------------------+---------------------+--------------------+ 1 row in set (0.00 sec)函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)，ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入，ROUND(x,y)函数对操作数进行四舍五入操作，结果保留小数点后面指定y位，TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。ROUND(x,y)函数在截取值的时候会四舍五入，而TRUNCATE (x,y)直接截取值，并不进行四舍五入。
mysql> select round(1.23), round(-0.23), round(-2.3333); +-------------+--------------+----------------+ | round(1.23) | round(-0.23) | round(-2.3333) | +-------------+--------------+----------------+ | 1 | 0 | -2 | +-------------+--------------+----------------+ 1 row in set (0.00 sec) mysql> select round(1.23, 1), round(-0.236, 2), round(-2.33546, 4); +----------------+------------------+--------------------+ | round(1.23, 1) | round(-0.236, 2) | round(-2.33546, 4) | +----------------+------------------+--------------------+ | 1.2 | -0.24 | -2.3355 | +----------------+------------------+--------------------+ 1 row in set (0.00 sec) mysql> select truncate(1.23, 1), truncate(2.335, 2), truncate(123.23, -1), truncate(12.23, 0); +-------------------+--------------------+----------------------+--------------------+ | truncate(1.23, 1) | truncate(2.335, 2) | truncate(123.23, -1) | truncate(12.23, 0) | +-------------------+--------------------+----------------------+--------------------+ | 1.2 | 2.33 | 120 | 12 | +-------------------+--------------------+----------------------+--------------------+ 1 row in set (0.00 sec)符号函数SIGN(x)
mysql> select sign(-12), sign(0), sign(123); +-----------+---------+-----------+ | sign(-12) | sign(0) | sign(123) | +-----------+---------+-----------+ | -1 | 0 | 1 | +-----------+---------+-----------+ 1 row in set (0.00 sec)幂运算函数POW(x,y)、POWER(x,y)和EXP(x)，POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值，EXP(x)返回e的x乘方后的值。
mysql> select pow(2,3), power(2,3), power(3,3), exp(3), exp(0); +----------+------------+------------+--------------------+--------+ | pow(2,3) | power(2,3) | power(3,3) | exp(3) | exp(0) | +----------+------------+------------+--------------------+--------+ | 8 | 8 | 27 | 20.085536923187668 | 1 | +----------+------------+------------+--------------------+--------+ 1 row in set (0.00 sec)对数运算函数LOG(x)和LOG10(x)，LOG(x)返回x的自然对数，x相对于基数e的对数，LOG10(x)返回x的基数为10的对数。
mysql> select log(3), log(5), log10(10), log10(100), log10(23); +--------------------+--------------------+-----------+------------+--------------------+ | log(3) | log(5) | log10(10) | log10(100) | log10(23) | +--------------------+--------------------+-----------+------------+--------------------+ | 1.0986122886681098 | 1.6094379124341003 | 1 | 2 | 1.3617278360175928 | +--------------------+--------------------+-----------+------------+--------------------+ 1 row in set (0.01 sec)角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)，
mysql> select radians(30), radians(90), radians(180); +--------------------+--------------------+-------------------+ | radians(30) | radians(90) | radians(180) | +--------------------+--------------------+-------------------+ | 0.5235987755982988 | 1.5707963267948966 | 3.141592653589793 | +--------------------+--------------------+-------------------+ 1 row in set (0.00 sec) mysql> select degrees(pi()), degrees(pi()/2), degrees(pi()/4); +---------------+-----------------+-----------------+ | degrees(pi()) | degrees(pi()/2) | degrees(pi()/4) | +---------------+-----------------+-----------------+ | 180 | 90 | 45 | +---------------+-----------------+-----------------+ 1 row in set (0.00 sec)正弦函数SIN(x)和反正弦函数ASIN(x)，ASIN(x)返回x的反正弦，若x不在-1~1的范围之内，则返回NULL。
mysql> select sin(pi()), sin(1), asin(1), asin(0), asin(-1), asin(2); +------------------------+--------------------+--------------------+---------+---------------------+---------+ | sin(pi()) | sin(1) | asin(1) | asin(0) | asin(-1) | asin(2) | +------------------------+--------------------+--------------------+---------+---------------------+---------+ | 1.2246467991473532e-16 | 0.8414709848078965 | 1.5707963267948966 | 0 | -1.5707963267948966 | NULL | +------------------------+--------------------+--------------------+---------+---------------------+---------+ 1 row in set (0.01 sec)余弦函数COS(x)和反余弦函数ACOS(x)，ACOS(x)返回x的反余弦，若x不在-1~1的范围之内，则返回NULL。
mysql> select cos(pi()), cos(1), acos(1), acos(0), acos(-1), acos(2); +-----------+--------------------+---------+--------------------+-------------------+---------+ | cos(pi()) | cos(1) | acos(1) | acos(0) | acos(-1) | acos(2) | +-----------+--------------------+---------+--------------------+-------------------+---------+ | -1 | 0.5403023058681398 | 0 | 1.5707963267948966 | 3.141592653589793 | NULL | +-----------+--------------------+---------+--------------------+-------------------+---------+ 1 row in set (0.00 sec)正切函数、反正切函数和余切函数，TAN(x)返回x的正切，ATAN(x)返回x的反正切，COT(x)返回x的余切。
mysql> select tan(2), atan(2), cot(2); +--------------------+--------------------+----------------------+ | tan(2) | atan(2) | cot(2) | +--------------------+--------------------+----------------------+ | -2.185039863261519 | 1.1071487177940904 | -0.45765755436028577 | +--------------------+--------------------+----------------------+ 1 row in set (0.00 sec)
Comments | NOTHING