mysql函数汇总之数学函数

阅读量：788 次

发布时间：2023-02-11

本文共 8963 字，大约阅读时间需要 29 分钟。

MySQL数学函数详解

MySQL中的数学函数为数据库操作提供了强大的计算能力，尤其在数据处理和分析方面尤为重要。本文将详细介绍MySQL中的数学函数及其应用。

1. 数学函数概述

数学函数主要用于处理数值数据，MySQL提供了丰富的数学函数，涵盖了从基础运算到高级计算的功能。常用的数学函数包括绝对值、圆周率、平方根、求余、四舍五入等。这些函数在数据分析、统计、报表生成等场景中发挥着重要作用。

2. 绝对值函数ABS(x)

绝对值函数用于获取数值的非负值表示，非常适用于数据清洗和统计分析。

示例：

select abs(2), abs(-6.6), abs(-99), abs(3.000);

结果：

+--------+-----------+----------+------------+| abs(2) | abs(-6.6) | abs(-99) | abs(3.000) |+--------+-----------+----------+------------+|      2 |       6.6 |       99 |      3.000 |+--------+-----------+----------+------------+

3. 圆周率函数PI()

PI()函数返回数学常数圆周率π的值，常用于三角函数和工程计算。

示例：

select pi();

结果：

+----------+| pi()     |+----------+| 3.141593 |+----------+

4. 平方根函数SQRT(x)

SQRT(x)函数返回x的非负平方根，适用于数据标准化和统计分析。

示例：

select sqrt(9), sqrt(30), sqrt(-30);

结果：

+---------+-------------------+-----------+| sqrt(9) | sqrt(30)          | sqrt(-30) |+---------+-------------------+-----------+|       3 | 5.477225575051661 |      NULL |+---------+-------------------+-----------+

5. 求余函数MOD(x,y)

MOD(x,y)函数用于计算x除以y的余数，支持浮点数运算，结果精确到小数点后若干位。

示例：

select mod(31,10), mod(31.5,3), mod(-31,10);

结果：

+------------+-------------+-------------+| mod(31,10) | mod(31.5,3) | mod(-31,10) |+------------+-------------+-------------+|          1 |         1.5 |          -1 |+------------+-------------+-------------+

6. 整数获取函数CEIL(x)、CEILING(x)和FLOOR(x)

CEIL(x)和CEILING(x)返回不小于x的最小整数值，而FLOOR(x)返回不大于x的最大整数值。

示例：

select ceil(20), ceil(-3.33), ceiling(20), ceiling(-3.33);

结果：

+----------+-------------+-------------+----------------+| ceil(20) | ceil(-3.33) | ceiling(20) | ceiling(-3.33) |+----------+-------------+-------------+----------------+|       20 |          -3 |          20 |             -3 |+----------+-------------+-------------+----------------+

select floor(20), floor(-3.33);

结果：

+-----------+--------------+| floor(20) | floor(-3.33) |+-----------+--------------+|        20 |           -4 |+-----------+--------------+

7. 随机数函数RAND()和RAND(x)

RAND()函数返回随机浮点数，范围在0到1之间。RAND(x)函数可通过指定种子值生成可重复的随机序列。

示例：

select rand(), rand(), rand();

结果：

+--------------------+---------------------+---------------------+| rand()             | rand()              | rand()              |+--------------------+---------------------+---------------------+| 0.5348021588612368 | 0.19999561477452107 | 0.39557162802254003 |+--------------------+---------------------+---------------------+

select rand(5), rand(5), rand();

结果：

+---------------------+---------------------+--------------------+| rand(5)             | rand(5)             | rand()             |+---------------------+---------------------+--------------------+| 0.40613597483014313 | 0.40613597483014313 | 0.3778713265227818 |+---------------------+---------------------+--------------------+

8. 四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

ROUND(x)函数四舍五入x到最接近的整数，ROUND(x,y)函数保留y位小数，TRUNCATE(x,y)函数截断指定位数的小数。

示例：

select round(-2.58), round(6.2), round(9.9);

结果：

+--------------+------------+------------+| round(-2.58) | round(6.2) | round(9.9) |+--------------+------------+------------+|           -3 |          6 |         10 |+--------------+------------+------------+

select round(1.78, 1), round(1.99, 0), round(222.90, -1), round(222.90, -2), round(888.80, -2);

结果：

+----------------+----------------+-------------------+-------------------+-------------------+| round(1.78, 1) | round(1.99, 0) | round(222.90, -1) | round(222.90, -2) | round(888.80, -2) |+----------------+----------------+-------------------+-------------------+-------------------+|            1.8 |              2 |               220 |               200 |               900 |+----------------+----------------+-------------------+-------------------+-------------------+

select truncate(1.22,1), truncate(1.66,1), truncate(666.66, -2), truncate(666.66,0);

结果：

+------------------+------------------+----------------------+--------------------+| truncate(1.22,1) | truncate(1.66,1) | truncate(666.66, -2) | truncate(666.66,0) |+------------------+------------------+----------------------+--------------------+|              1.2 |              1.6 |                  600 |                666 |+------------------+------------------+----------------------+--------------------+

9. 符号函数SIGN(x)

SIGN(x)函数返回x的符号，返回值为-1（负数）、0（零）或1（正数）。

示例：

select sign(-19), sign(-1.11), sign(0), sign(99);

结果：

+-----------+-------------+---------+----------+| sign(-19) | sign(-1.11) | sign(0) | sign(99) |+-----------+-------------+---------+----------+|        -1 |          -1 |       0 |        1 |+-----------+-------------+---------+----------+

10. 幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

POW(x,y)或POWER(x,y)函数计算x的y次幂，EXP(x)函数计算e的x次幂。

示例：

select pow(2,2), power(2,2), pow(3,3), power(3,3), pow(-0.5,2), power(-0.5,2);

结果：

+----------+------------+----------+------------+-------------+---------------+| pow(2,2) | power(2,2) | pow(3,3) | power(3,3) | pow(-0.5,2) | power(-0.5,2) |+----------+------------+----------+------------+-------------+---------------+|        4 |          4 |       27 |         27 |        0.25 |          0.25 |+----------+------------+----------+------------+-------------+---------------+

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

结果：

+--------------------+----------------------+--------+| exp(3)             | exp(-3)              | exp(0) |+--------------------+----------------------+--------+| 20.085536923187668 | 0.049787068367863944 |      1 |+--------------------+----------------------+--------+

11. 对数函数LOG(x)和LOG10(x)

LOG(x)返回x的自然对数，LOG10(x)返回x的以10为基数的对数。

示例：

select log(3), log(-3), log(9), log(-9), log(0);

结果：

+--------------------+---------+--------------------+---------+--------+| log(3)             | log(-3) | log(9)             | log(-9) | log(0) |+--------------------+---------+--------------------+---------+--------+| 1.0986122886681098 |    NULL | 2.1972245773362196 |    NULL |   NULL |+--------------------+---------+--------------------+---------+--------+

select log10(2), log10(100), log10(-100);

结果：

+--------------------+------------+-------------+|| log10(2)           | log10(100) | log10(-100) |+--------------------+------------+-------------+| 0.3010299956639812 |          2 |        NULL |+--------------------+------------+-------------+

12. 角度与弧度转换函数RADIANS(x)和DEGREES(x)

RADIANS(x)将角度转换为弧度，DEGREES(x)将弧度转换为角度。

示例：

select radians(90), radians(100);

结果：

+--------------------+--------------------+| radians(90)        | radians(100)       |+--------------------+--------------------+| 1.5707963267948966 | 1.7453292519943295 |+--------------------+--------------------+

select degrees(pi()), degrees(pi()/2);

结果：

+---------------+-----------------+| degrees(pi()) | degrees(pi()/2) |+---------------+-----------------+|           180 |              90 |+---------------+-----------------+

13. 三角函数SIN(x)和ASIN(x)

SIN(x)返回x的正弦值，ASIN(x)返回x的反正弦值。

示例：

select sin(1), round(sin(pi()));

结果：

+--------------------+------------------+|| sin(1)             | round(sin(pi())) |+--------------------+------------------+|| 0.8414709848078965 |                0 |+--------------------+------------------+|

select asin(0.8414709848078965), asin(2);

结果：

+--------------------------+---------+|| asin(0.8414709848078965) | asin(2) |+--------------------------+---------+||                        1 |    NULL |+--------------------------+---------+|

14. 余弦函数COS(x)和ACOS(x)

COS(x)返回x的余弦值，ACOS(x)返回x的反余弦值。

示例：

select cos(0), cos(pi()), cos(1);

结果：

+--------+-----------+--------------------+| cos(0) | cos(pi()) | cos(1)             |+--------+-----------+--------------------+|      1 |        -1 | 0.5403023058681398 |+--------+-----------+--------------------+

select acos(1), round(acos(0.5403023058681398));

结果：

+---------+---------------------------------+| acos(1) | round(acos(0.5403023058681398)) |+---------+---------------------------------+|       0 |                               1 |+---------+---------------------------------+

15. 正切函数TAN(x)、反正切函数ATAN(x)和余切函数COT(x)

TAN(x)返回x的正切值，ATAN(x)返回x的反正切值，COT(x)返回x的余切值。

示例：

select tan(0.4), round(tan(pi()/4));

结果：

+--------------------+--------------------+| tan(0.4)           | round(tan(pi()/4)) |+--------------------+--------------------+| 0.4227932187381618 |                  1 |+--------------------+--------------------+

select atan(0.4227932187381618), atan(1);

结果：

+--------------------------+--------------------+| atan(0.4227932187381618) | atan(1)            |+--------------------------+--------------------+|                      0.4 | 0.7853981633974483 |+--------------------------+--------------------+

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 |+--------------------+--------------------+--------------------+