Mysql必读MySQL数学函数简明总结
《Mysql必读MySQL数学函数简明总结》要点: 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): 求余函数,返回x被y除后的余数;对于带有小数部分的数据值也起作用,它返回除法运算后的精确余数. mysql> select MOD(31,8),MOD(21,-8),MOD(-7,2),-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)MYSQL实例 RAND(X) 返回一个随机浮点值,范围在0~1之间,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()每次产生不同0~1之间的随机数 代码如下: mysql> SELECT RAND(),RAND(),RAND(); +--------------------+--------------------+---------------------+ | RAND()???????????? | RAND()???????????? | RAND()????????????? | +--------------------+--------------------+---------------------+ | 0.6931893636409094 | 0.5147262984092592 | 0.49406343185721285 | +--------------------+--------------------+---------------------+ 9. ROUND(X)和ROUND(X,Y): 四舍五入函数,对X值按照Y进行四舍五入,Y可以省略,默认值为0;若Y不为0,则保留小数点后面指定Y位.MYSQL实例 代码如下: 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),-2); 10. TRUNCATE(X,Y): 与ROUND(X,Y)功能类似,但不进行四舍五入,只进行截取.MYSQL实例 代码如下:mysql> select TRUNCATE(1.33,TRUNCATE(1.99,TRUNCATE(19.99,-1); +------------------+------------------+------------------+--------------------+ | TRUNCATE(1.33,1) | TRUNCATE(1.99,0) | TRUNCATE(19.99,-1) | +------------------+------------------+------------------+--------------------+ |????????????? 1.3 |????????????? 1.9 |??????????????? 1 |???????????????? 10 | +------------------+------------------+------------------+--------------------+ 11. SIGN(X): 返回参数X的符号,X的值为负、零或正数时返回结果依次为-1,0或1MYSQL实例 代码如下: 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)MYSQL实例 POW(X,Y)与POWER(X,Y)功能相同,用于返回X的Y次乘方的结果值 +----------+-----------+-----------+------------+ | pow(2,2) | pow(2,-2) | pow(-2,2) | pow(-2,-2) | +----------+-----------+-----------+------------+ |??????? 4 |????? 0.25 |???????? 4 |?????? 0.25 | +----------+-----------+-----------+------------+ mysql> select power(2,power(2,power(-2,-2); EXP(X): 返回e的X乘方后的值: 代码如下: mysql> select EXP(3),EXP(0),EXP(-3); +-------------------+--------+---------------------+ | EXP(3)??????????? | EXP(0) | EXP(-3)???????????? | +-------------------+--------+---------------------+ | 20.08553692318767 |????? 1 | 0.04978706836786393 | +-------------------+--------+---------------------+ 13. LOG(X)和LOG10(X): 对数运算函数(X必须为正数),LOG(X)-返回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): 正弦函数,其中X为弧度值 ASIN(X): 反正弦函数 其中X必须在-1到1之间 COS(X): 余弦函数,其中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 | +--------------+--------------------+-----------+-------------------+--------------------+--------------------+-------------------+ (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |