簡單總結 MySQL數學函數

3211

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

3. SQRT(x): 返回x的平方根，要求（x為非負數，返回NULL）

mysql>?select?SQRT(49),?SQRT(0),?SQRT(-49);    +----------+---------+-----------+    |?SQRT(49)?|?SQRT(0)?|?SQRT(-49)?|    +----------+---------+-----------+    |????????7?|???????0?|??????NULL?|    +----------+---------+-----------+

4. MOD(x,y): 求余NULL，返回x被y除后的余數；對于帶有小數部分的數據值也起作用，它返回除法運算后的精確余數。

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的最小NULL值，返回值轉為一個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?|    +--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+

? 版權聲明
THE END
數據庫
# select
喜歡就支持一下吧
點贊11 分享