mysql函数

数学函数

1>绝对值函数：ABS(x)

mysql> select ABS(-2);+---------+| ABS(-2) |+---------+|       2 |+---------+1 row in set (0.00 sec)

2>返回圆周率的函数PI()

mysql> select pi();+----------+| pi()     |+----------+| 3.141593 |+----------+1 row in set (0.05 sec)

3>平方根函数SQRT(x)

 mysql> select SQRT(4);+---------+| SQRT(4) |+---------+|       2 |+---------+1 row in set (0.00 sec)

4>求余函数MOD(x,y)

mysql> select MOD(10,3),MOD(12,10);+-----------+------------+| MOD(10,3) | MOD(12,10) |+-----------+------------+|         1 |          2 |+-----------+------------+1 row in set (0.00 sec)

5>获取整数的函数。

CEIL(x)，CEILING(x)意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。

mysql> select CEIL(2.3),CEILING(2.4);+-----------+--------------+| CEIL(2.3) | CEILING(2.4) |+-----------+--------------+|         3 |            3 |+-----------+--------------+1 row in set (0.00 sec)

FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT.

mysql> select FLOOR(2.3);+------------+| FLOOR(2.3) |+------------+|          2 |+------------+1 row in set (0.00 sec)

6>获取随机数的函数RAND(),RAND(x)

mysql> select RAND(),RAND(),RAND();+---------------------+---------------------+---------------------+| RAND()              | RAND()              | RAND()              |+---------------------+---------------------+---------------------+| 0.20545223840088792 | 0.16246693130812323 | 0.19597797207159734 |+---------------------+---------------------+---------------------+1 row in set (0.02 sec)

RAND(x)返回一个随机浮点值V，范围在0到1之间(即0<=v<=1.0).若已指定一个整数参数X,则它被用作种子值，用来产生重复序列。

mysql> select RAND(5),RAND(10),RAND(10);+---------------------+--------------------+--------------------+| RAND(5)             | RAND(10)           | RAND(10)           |+---------------------+--------------------+--------------------+| 0.40613597483014313 | 0.6570515219653505 | 0.6570515219653505 |+---------------------+--------------------+--------------------+1 row in set (0.00 sec)

7>函数ROUND(x),ROUND(x,y),TRUNCATE(x,y)

ROUND(x)函数对操作数进行四舍五入操作。

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

ROUND(x,y)函数对操作数进行四舍五入操作，结果保留小数点后指定y位(若y为负值，则将保留x值到小数点左边y位)

mysql> select ROUND(1.38,1),ROUND(1.30,0),ROUND(232.38,-1);+---------------+---------------+------------------+| ROUND(1.38,1) | ROUND(1.30,0) | ROUND(232.38,-1) |+---------------+---------------+------------------+|           1.4 |             1 |              230 |+---------------+---------------+------------------+1 row in set (0.00 sec)

TRUNCATE(x,y)返回被舍去至小数点后y位的数字x.若y的值是0，则结果不带有小数点或小数点部分。若y为负值，则截取x小数点做起第y位开始后面所有低位的值。（ROUND(x,y)在截取值时会四舍五入，但是TRUNCATE(x,y)直接截取值，不进行四舍五入。）

mysql> select truncate(1.99,1),truncate(1.31,2);+------------------+------------------+| truncate(1.99,1) | truncate(1.31,2) |+------------------+------------------+|              1.9 |             1.31 |+------------------+------------------+1 row in set (0.00 sec)

8>符号函数SIGN(x)

SIGN(x)返回参数的符号，x的值为负、零或正时返回结果以此为-1，0,1。

mysql> select sign(-2),sign(0),sign(3);+----------+---------+---------+| sign(-2) | sign(0) | sign(3) |+----------+---------+---------+|       -1 |       0 |       1 |+----------+---------+---------+1 row in set (0.00 sec)

9>幂运算函数POW(x,y),POWER(x,y),EXP(x)

POW(x,y)或POWER(x,y)函数返回x的y次乘方的结果值

mysql> select POW(2,2),POWER(2,3),POWER(2,-2);+----------+------------+-------------+| POW(2,2) | POWER(2,3) | POWER(2,-2) |+----------+------------+-------------+|        4 |          8 |        0.25 |+----------+------------+-------------+1 row in set (0.04 sec)

EXP(x)返回e的x乘方的值

mysql> select EXP(3);+--------------------+| EXP(3)             |+--------------------+| 20.085536923187668 |+--------------------+1 row in set (0.04 sec)

10>对数运算函数LOG(x)和LOG10(x)
LOG(x)返回x的自然对数，x相对于基数e的对数。

mysql> select LOG(3),LOG(-3);+--------------------+---------+| LOG(3)             | LOG(-3) |+--------------------+---------+| 1.0986122886681098 |    NULL |+--------------------+---------+1 row in set (0.04 sec)

LOG10(x)返回x的基数为10的对数。

mysql> select LOG10(10),LOG10(100),LOG10(1000);+-----------+------------+-------------+| LOG10(10) | LOG10(100) | LOG10(1000) |+-----------+------------+-------------+|         1 |          2 |           3 |+-----------+------------+-------------+1 row in set (0.00 sec)

11>角度与弧度相互转换的函数RADIANS(X)和DEGREES(x)

RADIANS(X)将参数x由角度转化为弧度。

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

DEGREES(x)将参数x由弧度转化为角度。

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

12> 正弦函数SIN(x)和反正弦函数ASIN(x)

SIN(x)返回x正弦,其中x为弧度。

mysql> select sin(pi()/2),sin(1);+-------------+--------------------+| sin(pi()/2) | sin(1)             |+-------------+--------------------+|           1 | 0.8414709848078965 |+-------------+--------------------+1 row in set (0.00 sec)

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

mysql> select ASIN(1);+--------------------+| ASIN(1)            |+--------------------+| 1.5707963267948966 |+--------------------+1 row in set (0.02 sec)

13>余弦函数COS(x)和反余弦函数ACOS(x)

COS(X)返回x的余弦，其中x为弧度值。

mysql> select cos(pi()),cos(1);+-----------+--------------------+| cos(pi()) | cos(1)             |+-----------+--------------------+|        -1 | 0.5403023058681397 |+-----------+--------------------+1 row in set (0.00 sec)

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

mysql> select cos(pi()),cos(1);+-----------+--------------------+| cos(pi()) | cos(1)             |+-----------+--------------------+|        -1 | 0.5403023058681397 |+-----------+--------------------+1 row in set (0.00 sec)

14>正切函数，反正切函数，余切函数

TAN(x)返回x的正切，其中x为给定的弧度值。

mysql> select ROUND(tan(pi()/4)),tan(0);+--------------------+--------+| ROUND(tan(pi()/4)) | tan(0) |+--------------------+--------+|                  1 |      0 |+--------------------+--------+1 row in set (0.00 sec)

ATAN(x)返回x的反正切，即正切为x的值。

mysql> select atan(1),atan(0);+--------------------+---------+| atan(1)            | atan(0) |+--------------------+---------+| 0.7853981633974483 |       0 |+--------------------+---------+1 row in set (0.00 sec)

COT(x)返回x的余切。（与tan(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)