1. abs(x): 返回x的绝对值
mysql> select abs(1), abs(-1), abs(0); +--------+---------+--------+ | abs(1) | abs(-1) | abs(0) | +--------+---------+--------+ | 1 | 1 | 0 | +--------+---------+--------+
2. pi(): 返回圆周率
mysql> select pi(); +----------+ | pi() | +----------+ | 3.141593 | +----------+
3. sqrt(x): 返回x的平方根，要求（x为非负数，返回null）
mysql> select sqrt(49), sqrt(0), sqrt(-49); +----------+---------+-----------+ | sqrt(49) | sqrt(0) | sqrt(-49) | +----------+---------+-----------+ | 7 | 0 | null | +----------+---------+-----------+
4. mod(x,y): 求余函数，返回x被y除后的余数；对于带有小数部分的数据值也起作用，它返回除法运算后的精确余数。
mysql> select mod(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> select ceil(-3.35), ceil(3.35); +-------------+------------+ | ceil(-3.35) | ceil(3.35) | +-------------+------------+ | -3 | 4 | +-------------+------------+
6. ceiling(x): 同ceil(x)
mysql> select ceiling(-3.35), ceiling(3.35); +----------------+---------------+ | ceiling(-3.35) | ceiling(3.35) | +----------------+---------------+ | -3 | 4 | +----------------+---------------+
7. floor(x):返回不大于x的最大整数值，返回值转为一个bigint.
mysql> select floor(-3.35), floor(3.35); +--------------+-------------+ | floor(-3.35) | floor(3.35) | +--------------+-------------+ | -4 | 3 | +--------------+-------------+
8. rand()和rand(x)
rand(x) 返回一个随机浮点值，范围在０～１之间，x为整数，它被称作种子值，用来产生重复序列。即当ｘ值相同时，产生的随机数也相同；
mysql> select rand(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> select rand(), rand(), rand(); +--------------------+--------------------+---------------------+ | rand() | rand() | rand() | +--------------------+--------------------+---------------------+ | 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 | +--------------------+--------------------+---------------------+
9. round(x)和round(x,y): 四舍五入函数，对ｘ值按照ｙ进行四舍五入，ｙ可以省略，默认值为０;若ｙ不为０，则保留小数点后面指定ｙ位。
mysql> select round(-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> select round(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 | +---------------+---------------+------------------+------------------+
10. truncate(x,y): 与round(x,y)功能类似，但不进行四舍五入，只进行截取。
mysql> select truncate(1.33,1), truncate(1.99,1), truncate(1.99,0), truncate(19.99,-1); +------------------+------------------+------------------+--------------------+ | truncate(1.33,1) | truncate(1.99,1) | truncate(1.99,0) | truncate(19.99,-1) | +------------------+------------------+------------------+--------------------+ | 1.3 | 1.9 | 1 | 10 | +------------------+------------------+------------------+--------------------+
11. sign(x): 返回参数ｘ的符号，ｘ的值为负、零或正数时返回结果依次为－１，０或１
mysql> select sign(-21), sign(-0),sign(0), sign(0.0), sign(21); +-----------+----------+---------+-----------+----------+ | sign(-21) | sign(-0) | sign(0) | sign(0.0) | sign(21) | +-----------+----------+---------+-----------+----------+ | -1 | 0 | 0 | 0 | 1 | +-----------+----------+---------+-----------+----------+
12. pow(x,y), power(x,y)和exp(x)
pow(x,y)与power(x,y)功能相同，用于返回ｘ的ｙ次乘方的结果值
mysql> select pow(2,2), pow(2,-2), pow(-2,2), pow(-2,-2); +----------+-----------+-----------+------------+ | pow(2,2) | pow(2,-2) | pow(-2,2) | pow(-2,-2) | +----------+-----------+-----------+------------+ | 4 | 0.25 | 4 | 0.25 | +----------+-----------+-----------+------------+ mysql> select power(2,2), power(2,-2), power(-2,2), power(-2,-2); +------------+-------------+-------------+--------------+ | power(2,2) | power(2,-2) | power(-2,2) | power(-2,-2) | +------------+-------------+-------------+--------------+ | 4 | 0.25 | 4 | 0.25 | +------------+-------------+-------------+--------------+
exp(x): 返回e的ｘ乘方后的值:
mysql> select exp(3), exp(0), exp(-3); +-------------------+--------+---------------------+ | exp(3) | exp(0) | exp(-3) | +-------------------+--------+---------------------+ | 20.08553692318767 | 1 | 0.04978706836786393 | +-------------------+--------+---------------------+
13. log(x)和log10(x): 对数运算函数（ｘ必须为正数），log(x)-返回ｘ的自然对数(x相对于基数e的对数)　log10(x)-返回x的基数为10的对数：
mysql> select log(-3), log(0), log(3), log10(-100), log10(0), log10(100); +---------+--------+--------------------+-------------+----------+------------+ | log(-3) | log(0) | log(3) | log10(-100) | log10(0) | log10(100) | +---------+--------+--------------------+-------------+----------+------------+ | null | null | 1.0986122886681098 | null | null | 2 | +---------+--------+--------------------+-------------+----------+------------+
14. radians(x) 和 degrees(x): 角度与弧度转换函数
mysql> select radians(90), radians(180), degrees(pi()), degrees(pi()/2); +--------------------+-------------------+---------------+-----------------+ | radians(90) | radians(180) | degrees(pi()) | degrees(pi()/2) | +--------------------+-------------------+---------------+-----------------+ | 1.5707963267948966 | 3.141592653589793 | 180 | 90 | +--------------------+-------------------+---------------+-----------------+
15. sin(x), asin(x), cos(x), acos(x), tan(x), atan(x), cot(x)
sin(x): 正弦函数，其中ｘ为弧度值
asin(x): 反正弦函数　其中ｘ必须在－１到１之间
cos(x): 余弦函数，其中ｘ为弧度值
acos(x): 反余弦函数　其中ｘ必须在－１到１之间
tan(x): 正切函数，其中ｘ为弧度值
atan(x): 反正切函数，atan(x)与tan(x)互为反函数
cot(x):　余切函数，函数cot和tan互为倒函数
mysql> select sign(pi()/2),asin(1),cos(pi()), acos(-1), tan(pi()/4), atan(1), cot(0.5); +--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+ | sign(pi()/2) | asin(1) | cos(pi()) | acos(-1) | tan(pi()/4) | atan(1) | cot(0.5) | +--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+ | 1 | 1.5707963267948966 | -1 | 3.141592653589793 | 0.9999999999999999 | 0.7853981633974483 | 1.830487721712452 | +--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+
以上就是简单总结 mysql数学函数的详细内容。