3
mysql函数汇总之数学函数
source link: https://blog.51cto.com/u_12386780/5502733
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
什么是mysql函数？
函数表示对输入参数值返回一个具有特定关系的值，mysql提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常使用到各种函数。
各类函数从功能方面主要分为以下几种：
- 字符串函数
- 日期和时间函数
- 条件判断函数
- 系统信息函数
- 加密函数等其他函数
本篇文章主要介绍数学函数的相关使用方法。
什么是数学函数？
数学函数主要用来处理数值数据，主要的数学函数包括以下函数：
- 绝对值函数
- 随机函数等
如果函数报错了，数学函数将会返回空值NULL。
绝对值函数ABS(x)
abs(x)返回x的绝对值；
mysql> 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 |
+--------+-----------+----------+------------+
1 row in set (0.00 sec)
mysql>
+--------+-----------+----------+------------+
| abs(2) | abs(-6.6) | abs(-99) | abs(3.000) |
+--------+-----------+----------+------------+
| 2 | 6.6 | 99 | 3.000 |
+--------+-----------+----------+------------+
1 row in set (0.00 sec)
mysql>
返回圆周率的函数PI()
PI()返回圆周率π的值。默认显示小数位数是前6位。
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
mysql>
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
mysql>
平方根函数SQRT(x)
SQRT(x)返回非负数x的二次方根。
mysql> select sqrt(9), sqrt(30), sqrt(-30);
+---------+-------------------+-----------+
| sqrt(9) | sqrt(30) | sqrt(-30) |
+---------+-------------------+-----------+
| 3 | 5.477225575051661 | NULL |
+---------+-------------------+-----------+
1 row in set (0.00 sec)
mysql>
+---------+-------------------+-----------+
| sqrt(9) | sqrt(30) | sqrt(-30) |
+---------+-------------------+-----------+
| 3 | 5.477225575051661 | NULL |
+---------+-------------------+-----------+
1 row in set (0.00 sec)
mysql>
求余函数MOD(x,y)
mod(x,y)返回x被y除后的余数，mod()对于带有小数部分的数值也能计算，他返回触发运算后的精确余数。
mysql> 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 |
+------------+-------------+-------------+
1 row in set (0.00 sec)
mysql>
+------------+-------------+-------------+
| mod(31,10) | mod(31.5,3) | mod(-31,10) |
+------------+-------------+-------------+
| 1 | 1.5 | -1 |
+------------+-------------+-------------+
1 row in set (0.00 sec)
mysql>
获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)
ceil(x)和ceiling(x) 的意义相同，返回不小于x的最小整数值，返回值转化成一个bigint。
mysql> 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 |
+----------+-------------+-------------+----------------+
1 row in set (0.01 sec)
mysql>
+----------+-------------+-------------+----------------+
| ceil(20) | ceil(-3.33) | ceiling(20) | ceiling(-3.33) |
+----------+-------------+-------------+----------------+
| 20 | -3 | 20 | -3 |
+----------+-------------+-------------+----------------+
1 row in set (0.01 sec)
mysql>
floor(x)返回不大于x最大整数值；
mysql> select floor(20), floor(-3.33);
+-----------+--------------+
| floor(20) | floor(-3.33) |
+-----------+--------------+
| 20 | -4 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql>
+-----------+--------------+
| floor(20) | floor(-3.33) |
+-----------+--------------+
| 20 | -4 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql>
获取随机数的函数RAND()和RAND(x)
rand(x)返回一个随机浮点值，范围在0~1之间，若指定一个整体参数x，则这个x被用作种子值，用来产生重复序列。
rand不加参数
mysql> select rand(), rand(), rand();
+--------------------+---------------------+---------------------+
| rand() | rand() | rand() |
+--------------------+---------------------+---------------------+
| 0.5348021588612368 | 0.19999561477452107 | 0.39557162802254003 |
+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql>
+--------------------+---------------------+---------------------+
| rand() | rand() | rand() |
+--------------------+---------------------+---------------------+
| 0.5348021588612368 | 0.19999561477452107 | 0.39557162802254003 |
+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql>
可以看到返回的每个随机值都是不同的。
rand添加参数
mysql> select rand(5), rand(5), rand();
+---------------------+---------------------+--------------------+
| rand(5) | rand(5) | rand() |
+---------------------+---------------------+--------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.3778713265227818 |
+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)
mysql>
+---------------------+---------------------+--------------------+
| rand(5) | rand(5) | rand() |
+---------------------+---------------------+--------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.3778713265227818 |
+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)
mysql>
从上面可以看到，如果使用rand(x)，且x的值是一样的，那么产生的随机值，就是一样的，反之，不同的x，产生的随机值，就不一样了。
函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)
round(x)返回最接近于参数x的整数，对x值进行四舍五入。
mysql> select round(-2.58), round(6.2), round(9.9);
+--------------+------------+------------+
| round(-2.58) | round(6.2) | round(9.9) |
+--------------+------------+------------+
| -3 | 6 | 10 |
+--------------+------------+------------+
1 row in set (0.00 sec)
mysql>
+--------------+------------+------------+
| round(-2.58) | round(6.2) | round(9.9) |
+--------------+------------+------------+
| -3 | 6 | 10 |
+--------------+------------+------------+
1 row in set (0.00 sec)
mysql>
round(x,y)返回最接近于参数x的数，其值保留到小数点后y位，若y为负值，则将保留x值到小数点左边y位，同样使用的四舍五入。
mysql> 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 |
+----------------+----------------+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
mysql>
+----------------+----------------+-------------------+-------------------+-------------------+
| 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 |
+----------------+----------------+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
mysql>
truncate(x,y)返回被舍去小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。这里适用的方式是数学中的去尾法。
mysql> 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 |
+------------------+------------------+----------------------+--------------------+
1 row in set (0.00 sec)
mysql>
+------------------+------------------+----------------------+--------------------+
| truncate(1.22,1) | truncate(1.66,1) | truncate(666.66, -2) | truncate(666.66,0) |
+------------------+------------------+----------------------+--------------------+
| 1.2 | 1.6 | 600 | 666 |
+------------------+------------------+----------------------+--------------------+
1 row in set (0.00 sec)
mysql>
符号函数SIGN(x)
sign(x)返回参数的符号，x的值为负、零或正是返回结果依次对应的是-1、0或者1。
mysql> select sign(-19), sign(-1.11), sign(0), sign(99);
+-----------+-------------+---------+----------+
| sign(-19) | sign(-1.11) | sign(0) | sign(99) |
+-----------+-------------+---------+----------+
| -1 | -1 | 0 | 1 |
+-----------+-------------+---------+----------+
1 row in set (0.00 sec)
mysql>
+-----------+-------------+---------+----------+
| sign(-19) | sign(-1.11) | sign(0) | sign(99) |
+-----------+-------------+---------+----------+
| -1 | -1 | 0 | 1 |
+-----------+-------------+---------+----------+
1 row in set (0.00 sec)
mysql>
幂运算函数POW(x,y)、POWER(x,y)和EXP(x)
pow(x,y)或者power(x,y)函数返回x的y次乘方的结果值。
mysql> 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 |
+----------+------------+----------+------------+-------------+---------------+
1 row in set (0.00 sec)
mysql>
+----------+------------+----------+------------+-------------+---------------+
| 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 |
+----------+------------+----------+------------+-------------+---------------+
1 row in set (0.00 sec)
mysql>
exp(x)返回e的x乘方后的值
mysql> select exp(3), exp(-3), exp(0);
+--------------------+----------------------+--------+
| exp(3) | exp(-3) | exp(0) |
+--------------------+----------------------+--------+
| 20.085536923187668 | 0.049787068367863944 | 1 |
+--------------------+----------------------+--------+
1 row in set (0.00 sec)
mysql>
+--------------------+----------------------+--------+
| exp(3) | exp(-3) | exp(0) |
+--------------------+----------------------+--------+
| 20.085536923187668 | 0.049787068367863944 | 1 |
+--------------------+----------------------+--------+
1 row in set (0.00 sec)
mysql>
对数运算函数LOG(x)和LOG10(x)
log(x)返回x的自然对数，x相当于基数e的对数。
mysql> 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 |
+--------------------+---------+--------------------+---------+--------+
1 row in set, 3 warnings (0.00 sec)
mysql>
+--------------------+---------+--------------------+---------+--------+
| log(3) | log(-3) | log(9) | log(-9) | log(0) |
+--------------------+---------+--------------------+---------+--------+
| 1.0986122886681098 | NULL | 2.1972245773362196 | NULL | NULL |
+--------------------+---------+--------------------+---------+--------+
1 row in set, 3 warnings (0.00 sec)
mysql>
由于对数定义域为>=0，所以负数和0返回的结果都是null。
log10(x)返回x的基数为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)
mysql>
+--------------------+------------+-------------+
| log10(2) | log10(100) | log10(-100) |
+--------------------+------------+-------------+
| 0.3010299956639812 | 2 | NULL |
+--------------------+------------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)
radians(x)将参数x由角度转化为弧度；
mysql> select radians(90), radians(100);
+--------------------+--------------------+
| radians(90) | radians(100) |
+--------------------+--------------------+
| 1.5707963267948966 | 1.7453292519943295 |
+--------------------+--------------------+
1 row in set (0.00 sec)
mysql>
+--------------------+--------------------+
| radians(90) | radians(100) |
+--------------------+--------------------+
| 1.5707963267948966 | 1.7453292519943295 |
+--------------------+--------------------+
1 row in set (0.00 sec)
mysql>
degrees(x)将参数x由弧度转化为角度；
mysql> select degrees(pi()), degrees(pi()/2);
+---------------+-----------------+
| degrees(pi()) | degrees(pi()/2) |
+---------------+-----------------+
| 180 | 90 |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql>
+---------------+-----------------+
| degrees(pi()) | degrees(pi()/2) |
+---------------+-----------------+
| 180 | 90 |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql>
提示：PI()是返回了圆周率。
正弦函数SIN(x)和反正弦函数ASIN(x)
sin(x)返回x正弦，其中x为弧度值；
mysql> select sin(1), round(sin(pi()));
+--------------------+------------------+
| sin(1) | round(sin(pi())) |
+--------------------+------------------+
| 0.8414709848078965 | 0 |
+--------------------+------------------+
1 row in set (0.00 sec)
mysql>
+--------------------+------------------+
| sin(1) | round(sin(pi())) |
+--------------------+------------------+
| 0.8414709848078965 | 0 |
+--------------------+------------------+
1 row in set (0.00 sec)
mysql>
asin(x)返回x的反正弦，即正弦为x的值。若x不在-1到1的范围之内，则返回null。
mysql> select asin(0.8414709848078965), asin(2);
+--------------------------+---------+
| asin(0.8414709848078965) | asin(2) |
+--------------------------+---------+
| 1 | NULL |
+--------------------------+---------+
1 row in set (0.00 sec)
mysql>
+--------------------------+---------+
| asin(0.8414709848078965) | asin(2) |
+--------------------------+---------+
| 1 | NULL |
+--------------------------+---------+
1 row in set (0.00 sec)
mysql>
函数ASIN和SIN互为反函数。
余弦函数COS(x)和反余弦函数ACOS(x)
cos(x)返回x的余弦，其中x为弧度值；
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)
mysql>
+--------+-----------+--------------------+
| cos(0) | cos(pi()) | cos(1) |
+--------+-----------+--------------------+
| 1 | -1 | 0.5403023058681398 |
+--------+-----------+--------------------+
1 row in set (0.00 sec)
mysql>
acos(x)返回x的反余弦，即余弦是x的值。若x不在-1~1的范围只内，则返回null。
mysql> select acos(1), round(acos(0.5403023058681398));
+---------+---------------------------------+
| acos(1) | round(acos(0.5403023058681398)) |
+---------+---------------------------------+
| 0 | 1 |
+---------+---------------------------------+
1 row in set (0.00 sec)
mysql>
+---------+---------------------------------+
| acos(1) | round(acos(0.5403023058681398)) |
+---------+---------------------------------+
| 0 | 1 |
+---------+---------------------------------+
1 row in set (0.00 sec)
mysql>
函数ACOS和COS互为反函数。
正切函数、反正切函数和余切函数
TAN(x)返回x的正切，其中x为给定的弧度值。
mysql> select tan(0.4), round(tan(pi()/4));
+--------------------+--------------------+
| tan(0.4) | round(tan(pi()/4)) |
+--------------------+--------------------+
| 0.4227932187381618 | 1 |
+--------------------+--------------------+
1 row in set (0.01 sec)
mysql>
+--------------------+--------------------+
| tan(0.4) | round(tan(pi()/4)) |
+--------------------+--------------------+
| 0.4227932187381618 | 1 |
+--------------------+--------------------+
1 row in set (0.01 sec)
mysql>
ATAN(x)返回x的反正切，即正切为x的值。
mysql> select atan(0.4227932187381618), atan(1);
+--------------------------+--------------------+
| atan(0.4227932187381618) | atan(1) |
+--------------------------+--------------------+
| 0.4 | 0.7853981633974483 |
+--------------------------+--------------------+
1 row in set (0.01 sec)
mysql>
+--------------------------+--------------------+
| atan(0.4227932187381618) | atan(1) |
+--------------------------+--------------------+
| 0.4 | 0.7853981633974483 |
+--------------------------+--------------------+
1 row in set (0.01 sec)
mysql>
函数ATAN和TAN互为反函数；
COT(x)返回x的余切；
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)
mysql>
+--------------------+--------------------+--------------------+
| cot(0.3) | 1/tan(0.3) | cot(pi()/4) |
+--------------------+--------------------+--------------------+
| 3.2327281437658275 | 3.2327281437658275 | 1.0000000000000002 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
mysql>
函数cot和TAN互为倒函数。
至此，本文结束。
更多内容请转至VX公众号 “运维家” ，获取最新文章。
------ “运维家” ------
------ “运维家” ------
------ “运维家” ------
linux中断异常，linuxuml建模，linux编译多个源文件，linux怎么更新时间，linux中的分页视频教程，
linux怎么解压gzip，linux解压某个目录，怎么关闭linux网关，linux文件架构图，linux提高权限执行，
linux文件用什么标志，网络安全linux加固，linux中查看邮件，linux修改背景颜色，linux环境下搭建网站。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK