mysql数学函数之trunc_MySQL数学函数简明总结

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) 返回一个随机浮点值,范围在0~1之间,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()每次产生不同0~1之间的随机数

mysql> select rand(), rand(), rand();

+--------------------+--------------------+---------------------+

| rand()             | rand()             | rand()              |

+--------------------+--------------------+---------------------+

| 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 |

+--------------------+--------------------+---------------------+

9. round(x)和round(x,y): 四舍五入函数,对x值按照y进行四舍五入,y可以省略,默认值为0;若y不为0,则保留小数点后面指定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): 返回参数x的符号,x的值为负、零或正数时返回结果依次为-1,0或1

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)功能相同,用于返回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的x乘方后的值:

mysql> select exp(3), exp(0), exp(-3);

+-------------------+--------+---------------------+

| exp(3)            | exp(0) | exp(-3)             |

+-------------------+--------+---------------------+

| 20.08553692318767 |      1 | 0.04978706836786393 |

+-------------------+--------+---------------------+

13. log(x)和log10(x): 对数运算函数(x必须为正数),log(x)-返回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): 正弦函数,其中x为弧度值

asin(x): 反正弦函数 其中x必须在-1到1之间

cos(x): 余弦函数,其中x为弧度值

acos(x): 反余弦函数 其中x必须在-1到1之间

tan(x): 正切函数,其中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 |

+--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+

希望与广大网友互动??

点此进行留言吧!


版权声明:本文为weixin_35501678原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
THE END
< <上一篇
下一篇>>