MySQL常用函数汇总——数值操作函数

 

1. ABS 求绝对值

   mysql> SELECT ABS(5),ABS(-2.4),ABS(-24),ABS(0);
   +--------+-----------+----------+--------+
   | ABS(5) | ABS(-2.4) | ABS(-24) | ABS(0) |
   +--------+-----------+----------+--------+
   |      5 |       2.4 |       24 |      0 |
   +--------+-----------+----------+--------+
   1 row in set (0.10 sec)

    

2. SQRT 求二次方根

   mysql> SELECT SQRT(25),SQRT(120),SQRT(-9);
   +----------+--------------------+----------+
   | SQRT(25) | SQRT(120)          | SQRT(-9) |
   +----------+--------------------+----------+
   |        5 | 10.954451150103322 |     NULL |
   +----------+--------------------+----------+
   1 row in set (0.06 sec)

    

3. MOD 求余数

   mysql> SELECT MOD(63,8),MOD(120,10),MOD(15.5,3);
   +-----------+-------------+-------------+
   | MOD(63,8) | MOD(120,10) | MOD(15.5,3) |
   +-----------+-------------+-------------+
   |         7 |           0 |         0.5 |
   +-----------+-------------+-------------+
   1 row in set (0.03 sec)

    

4. CEIL和CEILING 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整

   mysql> SELECT CEIL(-2.5),CEILING(2.5);
   +------------+--------------+
   | CEIL(-2.5) | CEILING(2.5) |
   +------------+--------------+
   |         -2 |            3 |
   +------------+--------------+
   1 row in set (0.00 sec)

    

5. FLOOR 向下取整

   mysql> SELECT FLOOR(5),FLOOR(5.66),FLOOR(-4),FLOOR(-4.66);
   +----------+-------------+-----------+--------------+
   | FLOOR(5) | FLOOR(5.66) | FLOOR(-4) | FLOOR(-4.66) |
   +----------+-------------+-----------+--------------+
   |        5 |           5 |        -4 |           -5 |
   +----------+-------------+-----------+--------------+
   1 row in set (0.00 sec)

    

6. RAND 生成一个0~1之间的随机数，传入整数参数来产生重复序列

   mysql> SELECT RAND(1),RAND(),RAND(1);
   +---------------------+--------------------+---------------------+
   | RAND(1)             | RAND()             | RAND(1)             |
   +---------------------+--------------------+---------------------+
   | 0.40540353712197724 | 0.7901452330780637 | 0.40540353712197724 |
   +---------------------+--------------------+---------------------+
   1 row in set (0.00 sec)

    

7. ROUND 对所传参数进行四舍五入

   mysql> SELECT ROUND(-6.66),ROUND(3.33,3),ROUND(88.66,-1),ROUND(88.46,-2);
   +--------------+---------------+-----------------+-----------------+
   | ROUND(-6.66) | ROUND(3.33,3) | ROUND(88.66,-1) | ROUND(88.46,-2) |
   +--------------+---------------+-----------------+-----------------+
   |           -7 |         3.330 |              90 |             100 |
   +--------------+---------------+-----------------+-----------------+
   1 row in set (0.00 sec)

    

8. SIGN 返回参数的符号，x 的值为负、零和正时返回结果依次为 -1、0 和 1

   mysql> SELECT SIGN(-6),SIGN(0),SIGN(34);
   +----------+---------+----------+
   | SIGN(-6) | SIGN(0) | SIGN(34) |
   +----------+---------+----------+
   |       -1 |       0 |        1 |
   +----------+---------+----------+
   1 row in set (0.00 sec)

    

9. POW和POWER 两个函数的功能相同，都是返回所传参数的次方

   mysql> SELECT POW(5,-2),POW(10,3),POW(100,0),POWER(4,3),POWER(6,-3);
   +-----------+-----------+------------+------------+----------------------+
   | POW(5,-2) | POW(10,3) | POW(100,0) | POWER(4,3) | POWER(6,-3)          |
   +-----------+-----------+------------+------------+----------------------+
   |      0.04 |      1000 |          1 |         64 | 0.004629629629629629 |
   +-----------+-----------+------------+------------+----------------------+
   1 row in set (0.00 sec)

    

10. SIN 求正弦值

    mysql> SELECT SIN(1),SIN(0.5*PI());
    +--------------------+---------------+
    | SIN(1)             | SIN(0.5*PI()) |
    +--------------------+---------------+
    | 0.8414709848078965 |             1 |
    +--------------------+---------------+
    1 row in set (0.15 sec)

     

11. ASIN 求反正弦值

    mysql> SELECT ASIN(0.8414709848078965),ASIN(2);
    +--------------------------+---------+
    | ASIN(0.8414709848078965) | ASIN(2) |
    +--------------------------+---------+
    |                        1 |    NULL |
    +--------------------------+---------+
    1 row in set (0.03 sec)

     

12. COS 求余弦值

    mysql> SELECT COS(1),COS(0),COS(PI());
    +--------------------+--------+-----------+
    | COS(1)             | COS(0) | COS(PI()) |
    +--------------------+--------+-----------+
    | 0.5403023058681398 |      1 |        -1 |
    +--------------------+--------+-----------+
    1 row in set (0.03 sec)

     

13. ACOS 求反余弦值

    mysql> SELECT ACOS(2),ACOS(1),ACOS(-1);
    +---------+---------+-------------------+
    | ACOS(2) | ACOS(1) | ACOS(-1)          |
    +---------+---------+-------------------+
    |    NULL |       0 | 3.141592653589793 |
    +---------+---------+-------------------+
    1 row in set (0.01 sec)

     

14. TAN 求正切值

    mysql> SELECT TAN(1),TAN(0);
    +--------------------+--------+
    | TAN(1)             | TAN(0) |
    +--------------------+--------+
    | 1.5574077246549023 |      0 |
    +--------------------+--------+
    1 row in set (0.03 sec)

     

15. ATAN 求反正切值

    mysql> SELECT ATAN(1.5574077246549023),ATAN(0);
    +--------------------------+---------+
    | ATAN(1.5574077246549023) | ATAN(0) |
    +--------------------------+---------+
    |                        1 |       0 |
    +--------------------------+---------+
    1 row in set (0.05 sec)

     

16. COT 求余切值

    mysql> SELECT COT(1);
    +--------------------+
    | COT(1)             |
    +--------------------+
    | 0.6420926159343306 |
    +--------------------+
    1 row in set (0.00 sec)