Home > Article > Database > MySQL函数学习笔记一：数学函数_MySQL

MySQL函数学习笔记一：数学函数_MySQL

WBOYOriginal: 2016-06-01 13:02:34792browse

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

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

mysql> SELECT RAND(), RAND(), RAND();
+--------------------+--------------------+---------------------+
| RAND() | RAND() | RAND() |
+--------------------+--------------------+---------------------+
| 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 |
+--------------------+--------------------+---------------------+

9. ROUND(X)和ROUND(X,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): 返回参数Ｘ的符号，Ｘ的值为负、零或正数时返回结果依次为－１，０或１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)功能相同，用于返回Ｘ的Ｙ次乘方的结果值

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的Ｘ乘方后的值:

mysql> select EXP(3), EXP(0), EXP(-3);
+-------------------+--------+---------------------+
| EXP(3) | EXP(0) | EXP(-3) |
+-------------------+--------+---------------------+
| 20.08553692318767 | 1 | 0.04978706836786393 |
+-------------------+--------+---------------------+

13. LOG(X)和LOG10(X): 对数运算函数（Ｘ必须为正数），LOG(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): 正弦函数，其中Ｘ为弧度值

ASIN(X): 反正弦函数　其中Ｘ必须在－１到１之间

COS(X): 余弦函数，其中Ｘ为弧度值

ACOS(X): 反余弦函数　其中Ｘ必须在－１到１之间

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

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：MySQLbin-log分析方法_MySQLNext article：MySQL超级简明基本操作攻略,给自己看(一)_MySQL

See more

MySQL函数学习笔记一：数学函数_MySQL

Related articles