Mysql数学函数实战

缺乏、安全感 2021-07-25 01:10 488阅读 0赞

**一 求2，-3.3和-33的绝对值**

mysql> SELECT ABS(2), ABS(-3.3), ABS(-33);
    +--------+-----------+----------+
    | ABS(2) | ABS(-3.3) | ABS(-33) |
    +--------+-----------+----------+
    |      2 |       3.3 |       33 |
    +--------+-----------+----------+
    1 row in set (0.00 sec)

**二 返回圆周率值**

mysql> SELECT pi();
    +----------+
    | pi()     |
    +----------+
    | 3.141593 |
    +----------+
    1 row in set (0.01 sec)

**三 求9，40和-49的二次平方根**

**四 对MOD(31,8)，MOD(234, 10)，MOD(45.5,6)进行求余运算**

mysql> SELECT MOD(31,8),MOD(234, 10),MOD(45.5,6);
    +-----------+--------------+-------------+
    | MOD(31,8) | MOD(234, 10) | MOD(45.5,6) |
    +-----------+--------------+-------------+
    |         7 |            4 |         3.5 |
    +-----------+--------------+-------------+
    1 row in set (0.00 sec)

**五 使用CEILING函数返回最小整数——返回房顶的数**

mysql> SELECT  CEIL(-3.35),CEILING(3.35);
    +-------------+---------------+
    | CEIL(-3.35) | CEILING(3.35) |
    +-------------+---------------+
    |          -3 |             4 |
    +-------------+---------------+
    1 row in set (0.01 sec)

**六 使用FLOOR函数返回最大整数——返回地板的数**

mysql> SELECT FLOOR(-3.35), FLOOR(3.35);
    +--------------+-------------+
    | FLOOR(-3.35) | FLOOR(3.35) |
    +--------------+-------------+
    |           -4 |           3 |
    +--------------+-------------+
    1 row in set (0.00 sec)

**七 使用RAND()函数产生随机数**

mysql> SELECT RAND(),RAND(),RAND();
    +--------------------+--------------------+--------------------+
    | RAND()             | RAND()             | RAND()             |
    +--------------------+--------------------+--------------------+
    | 0.5071749067932133 | 0.5936268275544297 | 0.4466094481261535 |
    +--------------------+--------------------+--------------------+
    1 row in set (0.00 sec)

**八 使用RAND(x)函数产生随机数**

mysql> SELECT RAND(10),RAND(10),RAND(11);
    +--------------------+--------------------+-------------------+
    | RAND(10)           | RAND(10)           | RAND(11)          |
    +--------------------+--------------------+-------------------+
    | 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 |
    +--------------------+--------------------+-------------------+
    1 row in set (0.00 sec)

**九 使用ROUND(x)函数对操作数进行四舍五入操作**

mysql> SELECT ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66);
    +--------------+--------------+-------------+-------------+
    | ROUND(-1.14) | ROUND(-1.67) | ROUND(1.14) | ROUND(1.66) |
    +--------------+--------------+-------------+-------------+
    |           -1 |           -2 |           1 |           2 |
    +--------------+--------------+-------------+-------------+
    1 row in set (0.00 sec)

**十 使用ROUND(x,y)函数对操作数进行四舍五入操作，结果保留小数点后面指定y位**

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 |
    +----------------+----------------+-------------------+------------------+
    1 row in set (0.00 sec)

**十一 使用TRUNCATE(x,y)函数对操作数进行截断操作，结果保留小数点后面指定y位**

mysql> SELECT TRUNCATE(1.31,1), TRUNCATE(1.99,1), TRUNCATE(1.99,0), TRUNCATE(19.99,-1);
    +------------------+------------------+------------------+--------------------+
    | TRUNCATE(1.31,1) | TRUNCATE(1.99,1) | TRUNCATE(1.99,0) | TRUNCATE(19.99,-1) |
    +------------------+------------------+------------------+--------------------+
    |              1.3 |              1.9 |                1 |                 10 |
    +------------------+------------------+------------------+--------------------+
    1 row in set (0.00 sec)

**十二 使用SIGN函数返回参数的符号**

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

**十三 使用POW和POWER函数进行乘方运算**

mysql> SELECT POW(2,2), POWER(2,2),POW(2,-2), POWER(2,-2);
    +----------+------------+-----------+-------------+
    | POW(2,2) | POWER(2,2) | POW(2,-2) | POWER(2,-2) |
    +----------+------------+-----------+-------------+
    |        4 |          4 |      0.25 |        0.25 |
    +----------+------------+-----------+-------------+
    1 row in set (0.01 sec)

**十四 使用EXP函数计算e的乘方**

mysql> SELECT EXP(3),EXP(-3),EXP(0);
    +--------------------+----------------------+--------+
    | EXP(3)             | EXP(-3)              | EXP(0) |
    +--------------------+----------------------+--------+
    | 20.085536923187668 | 0.049787068367863944 |      1 |
    +--------------------+----------------------+--------+
    1 row in set (0.01 sec)

**十五 使用LOG(x)函数计算自然对数，x相对于基数e的对数**

mysql> SELECT LOG(3), LOG(-3);
    +--------------------+---------+
    | LOG(3)             | LOG(-3) |
    +--------------------+---------+
    | 1.0986122886681098 |    NULL |
    +--------------------+---------+
    1 row in set, 1 warning (0.01 sec)

**十六 使用LOG10计算以10为基数的对数**

mysql> SELECT LOG10(2), LOG10(100), LOG10(-100);
    +--------------------+------------+-------------+
    | LOG10(2)           | LOG10(100) | LOG10(-100) |
    +--------------------+------------+-------------+
    | 0.3010299956639812 |          2 |        NULL |
    +--------------------+------------+-------------+
    1 row in set, 1 warning (0.00 sec)

**十七 使用RADIANS将角度转换为弧度**

mysql> SELECT RADIANS(90),RADIANS(180);
    +--------------------+-------------------+
    | RADIANS(90)        | RADIANS(180)      |
    +--------------------+-------------------+
    | 1.5707963267948966 | 3.141592653589793 |
    +--------------------+-------------------+
    1 row in set (0.00 sec)

**十八 使用DEGREES将弧度转换为角度**

mysql> SELECT DEGREES(PI()), DEGREES(PI() / 2);
    +---------------+-------------------+
    | DEGREES(PI()) | DEGREES(PI() / 2) |
    +---------------+-------------------+
    |           180 |                90 |
    +---------------+-------------------+
    1 row in set (0.00 sec)

**十九 使用SIN函数计算正弦值**

mysql> SELECT SIN(1), ROUND(SIN(PI()));
    +--------------------+------------------+
    | SIN(1)             | ROUND(SIN(PI())) |
    +--------------------+------------------+
    | 0.8414709848078965 |                0 |
    +--------------------+------------------+
    1 row in set (0.00 sec)

**二十 使用ASIN函数计算反正弦值**

**二一 使用COS函数计算余弦值**

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

**二二 使用ACOS函数计算反余弦值**

mysql> SELECT ACOS(1),ACOS(0), ROUND(ACOS(0.5403023058681398));
    +---------+--------------------+---------------------------------+
    | ACOS(1) | ACOS(0)            | ROUND(ACOS(0.5403023058681398)) |
    +---------+--------------------+---------------------------------+
    |       0 | 1.5707963267948966 |                               1 |
    +---------+--------------------+---------------------------------+
    1 row in set (0.00 sec)

**二三 使用TAN函数计算正切值**

mysql> SELECT TAN(0.3), ROUND(TAN(PI()/4));
    +---------------------+--------------------+
    | TAN(0.3)            | ROUND(TAN(PI()/4)) |
    +---------------------+--------------------+
    | 0.30933624960962325 |                  1 |
    +---------------------+--------------------+
    1 row in set (0.00 sec)

**二四 使用ATAN函数计算反正切值**

mysql> SELECT ATAN(0.30933624960962325), ATAN(1);
    +---------------------------+--------------------+
    | ATAN(0.30933624960962325) | ATAN(1)            |
    +---------------------------+--------------------+
    |                       0.3 | 0.7853981633974483 |
    +---------------------------+--------------------+
    1 row in set (0.00 sec)

**二五 使用COT()函数计算余切值**

mysql> SELECT COT(0.3), 1/TAN(0.3),COT(PI() / 4);
    +--------------------+--------------------+--------------------+
    | COT(0.3)           | 1/TAN(0.3)         | COT(PI() / 4)      |
    +--------------------+--------------------+--------------------+
    | 3.2327281437658275 | 3.2327281437658275 | 1.0000000000000002 |
    +--------------------+--------------------+--------------------+
    1 row in set (0.00 sec)