MySQL数值函数主要用于数值运算和/或数学计算。下表详细介绍了MySQL实现中可用的数值函数。
| Sr.No. | Name & Description |
|---|---|
| 1 |
ABS()
返回数值表达式的绝对值。 |
| 2 |
ACOS()
返回数值表达式的反余弦值。如果该值不在-1到1之间,则返回NULL。 |
| 3 |
ASIN()
返回数字表达式的反正弦值。如果值不在-1到1的范围内,则返回NULL |
| 4 |
ATAN()
返回数值表达式的反正切。 |
| 5 |
ATAN2()
返回传递给它的两个变量的反正切。 |
| 6 |
BIT_AND()
返回按位与表达式中的所有位。 |
| 7 |
BIT_COUNT()
返回传递给它的二进制值的字符串表示形式。 |
| 8 |
BIT_OR()
返回传递的表达式中所有位的按位或。 |
| 9 |
CEIL()
返回不小于传递的数字表达式的最小整数值 |
| 10 |
CEILING()
返回不小于传递的数字表达式的最小整数值 |
| 11 |
CONV()
将数字表达式从一个基数转换为另一个基数。 |
| 12 |
COS()
返回传递的数字表达式的余弦值。数值表达式应以弧度表示。 |
| 13 |
COT()
返回传递的数值表达式的余切。 |
| 14 |
DEGREES()
返回从弧度转换为度的数值表达式。 |
| 15 |
EXP()
返回自然对数(e)的底数,该底数提高为传递的数值表达式的幂。 |
| 16 |
FLOOR()
返回不大于传递的数字表达式的最大整数值。 |
| 17 |
FORMAT()
返回四舍五入到小数位数的数字表达式。 |
| 18 |
GREATEST()
返回输入表达式的最大值。 |
| 19 |
INTERVAL()
接受多个表达式exp1,exp2和exp3,依此类推。如果exp1小于exp2,则返回0；如果exp1小于exp3,则返回1,依此类推。 |
| 20 |
LEAST()
如果给定两个或多个,则返回最小值输入。 |
| 21 |
LOG()
返回所传递数值表达式的自然对数。 |
| 22 |
LOG10()
返回所传递数值表达式的以10为底的对数。 |
| 23 |
MOD()
通过潜水另一表达式来返回一个表达式的其余部分。 |
| 24 |
OCT()
返回传递的数字表达式的八进制值的字符串表示形式。如果传递的值为NULL,则返回NULL。 |
| 25 |
PI()
返回pi的值 |
| 26 |
POW()
返回一个表达式的值加到另一表达式的幂上 |
| 27 |
POWER()
返回一个表达式的值加到另一表达式的幂上 |
| 28 |
RADIANS()
返回传递的表达式的值,该表达式从度转换为弧度。 |
| 29 |
ROUND()
返回四舍五入为整数的数字表达式。可用于将表达式四舍五入到小数点 |
| 30 |
SIN()
返回以弧度为单位的数字表达式的正弦值。 |
| 31 |
SQRT()
返回数值表达式的非负平方根。 |
| 32 |
STD()
返回数值表达式的标准偏差。 |
| 33 |
STDDEV()
返回数值表达式的标准偏差。 |
| 34 |
TAN()
返回以弧度表示的数字表达式的切线。 |
| 35 |
TRUNCATE()
返回截断为小数位exp2的数字exp1。如果exp2为0,则输出将没有小数点。 |
ABS(X)
ABS()函数返回X的绝对值。请考虑以下Example-
mysql> SELECT ABS(2); +---------------------------------------------------------+ | ABS(2) | +---------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ABS(-2); +---------------------------------------------------------+ | ABS(2) | +---------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
ACOS(X)
此函数返回X的反余弦值。X的值必须介于.1和1之间,否则将返回NULL。考虑以下Example-
mysql> SELECT ACOS(1); +---------------------------------------------------------+ | ACOS(1) | +---------------------------------------------------------+ | 0.000000 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
ASIN(X)
ASIN()函数返回X的反正弦值。X的值必须在.1到1的范围内,否则返回NULL。
mysql> SELECT ASIN(1); +---------------------------------------------------------+ | ASIN(1) | +---------------------------------------------------------+ | 1.5707963267949 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
阿坦(X)
此函数返回X的反正切。
mysql> SELECT ATAN(1); +---------------------------------------------------------+ | ATAN(1) | +---------------------------------------------------------+ | 0.78539816339745 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
ATAN2(Y,X)
此函数返回两个参数的反正切值:X和Y。它与Y/X的反正切相似,不同之处在于两者的符号均用于查找输出的象限。
mysql> SELECT ATAN2(3,6); +---------------------------------------------------------+ | ATAN2(3,6) | +---------------------------------------------------------+ | 0.46364760900081 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
BIT_AND(表达式)
BIT_AND函数返回表达式中所有位的按位与。基本前提是,如果两个对应的位相同,则按位AND运算将返回1,如果它们不同,则按位AND运算将返回0。该函数本身返回64位整数值。如果没有匹配项,则它将返回18446744073709551615。以下Example在按汽车制造商分组的PRICE列上执行BIT_AND函数-
mysql> SELECT MAKER, BIT_AND(PRICE) BITS FROM CARS GROUP BY MAKER +---------------------------------------------------------+ | MAKER BITS | +---------------------------------------------------------+ | CHRYSLER 512 | | FORD 12488 | | HONDA 2144 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
BIT_COUNT(数值)
BIT_COUNT()函数返回numeric_value中活动的位数。以下Example演示了使用BIT_COUNT()函数返回数字范围内的有效位数-
mysql> SELECT BIT_COUNT(2) AS TWO, BIT_COUNT(4) AS FOUR, BIT_COUNT(7) AS SEVEN +-----+------+-------+ | TWO | FOUR | SEVEN | +-----+------+-------+ | 1 | 1 | 3 | +-----+------+-------+ 1 row in set (0.00 sec)
BIT_OR(表达式)
BIT_OR()函数返回表达式中所有位的按位或。按位或函数的基本前提是,如果对应的位匹配,则返回0,否则返回1。该函数返回一个64位整数,如果没有匹配的行,则返回0。下面的Example在CARS表的PRICE列上执行BIT_OR()函数,按MAKER分组-
mysql> SELECT MAKER, BIT_OR(PRICE) BITS FROM CARS GROUP BY MAKER +---------------------------------------------------------+ | MAKER BITS | +---------------------------------------------------------+ | CHRYSLER 62293 | | FORD 16127 | | HONDA 32766 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
CEIL(X)
天花板(X)
此函数返回不小于X的最小整数值。请考虑以下Example-
mysql> SELECT CEILING(3.46); +---------------------------------------------------------+ | CEILING(3.46) | +---------------------------------------------------------+ | 4 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CEIL(-6.43); +---------------------------------------------------------+ | CEIL(-6.43) | +---------------------------------------------------------+ | -6 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
CONV(N,from_base,to_base)
CONV()函数的目的是在不同数字基数之间转换数字。该函数返回从from_base转换为to_base的N值的字符串。最小基值为2,最大基值为36。如果任何参数为NULL,则函数返回NULL。考虑以下Example,该Example将数字5从基数16转换为基数2-
mysql> SELECT CONV(5,16,2); +---------------------------------------------------------+ | CONV(5,16,2) | +---------------------------------------------------------+ | 101 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
COS(X)
此函数返回X的余弦。X的值以弧度给出。
mysql>SELECT COS(90); +---------------------------------------------------------+ | COS(90) | +---------------------------------------------------------+ | -0.44807361612917 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
COT(X)
此函数返回X的余切。请考虑以下Example-
mysql>SELECT COT(1); +---------------------------------------------------------+ | COT(1) | +---------------------------------------------------------+ | 0.64209261593433 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
学位(X)
此函数返回从弧度转换为度的X值。
mysql>SELECT DEGREES(PI()); +---------------------------------------------------------+ | DEGREES(PI()) | +---------------------------------------------------------+ | 180.000000 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
经验值(X)
此函数返回e的值(自然对数的底数),升为X的幂。
mysql>SELECT EXP(3); +---------------------------------------------------------+ | EXP(3) | +---------------------------------------------------------+ | 20.085537 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
楼层(X)
此函数返回不大于X的最大整数值。
mysql>SELECT FLOOR(7.55); +---------------------------------------------------------+ | FLOOR(7.55) | +---------------------------------------------------------+ | 7 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
格式(X,D)
FORMAT()函数用于将数字X格式化为以下格式:###,###,###。##被截断为D小数位。以下Example演示了FORMAT()函数的用法和输出-
mysql>SELECT FORMAT(423423234.65434453,2); +---------------------------------------------------------+ | FORMAT(423423234.65434453,2) | +---------------------------------------------------------+ | 423,423,234.65 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
最棒的(n1,n2,n3,.......)
GREATEST()函数返回输入参数集中的最大值(n1,n2,n3等)。以下Example使用GREATEST()函数从一组数值中返回最大数-
mysql>SELECT GREATEST(3,5,1,8,33,99,34,55,67,43); +---------------------------------------------------------+ | GREATEST(3,5,1,8,33,99,34,55,67,43) | +---------------------------------------------------------+ | 99 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
间隔(N,N1,N2,N3,..........)
The INTERVAL() function compares the value of N to the value list (N1, N2, N3, and so on ). The function returns 0 if N < N1, 1 if N < N2, 2 if N <N3, and so on. It will return .1 if N is NULL. The value list must be in the form N1 < N2 < N3 in order to work properly. The following code is a simple example of how the INTERVAL() function works −
mysql>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10); +---------------------------------------------------------+ | INTERVAL(6,1,2,3,4,5,6,7,8,9,10) | +---------------------------------------------------------+ | 6 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
间隔(N,N1,N2,N3,..........)
The INTERVAL() function compares the value of N to the value list (N1, N2, N3, and so on ). The function returns 0 if N < N1, 1 if N < N2, 2 if N <N3, and so on. It will return .1 if N is NULL. The value list must be in the form N1 < N2 < N3 in order to work properly. The following code is a simple example of how the INTERVAL() function works −
mysql>SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10); +---------------------------------------------------------+ | INTERVAL(6,1,2,3,4,5,6,7,8,9,10) | +---------------------------------------------------------+ | 6 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
请记住,6是第一个值大于N的值列表中从零开始的索引。在我们的Example中,7是有问题的值,位于第六个索引槽中。
最小(N1,N2,N3,N4,......)
LEAST()函数与GREATEST()函数相反。它的目的是从值列表中返回值最小的项(N1,N2,N3等)。以下Example显示LEAST()函数的正确用法和输出-
mysql>SELECT LEAST(3,5,1,8,33,99,34,55,67,43); +---------------------------------------------------------+ | LEAST(3,5,1,8,33,99,34,55,67,43) | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
对数(X)
对数(B,X)
该函数的单参数版本将返回X的自然对数。如果使用两个参数调用该函数,它将返回任意基数B的X的对数。请考虑以下Example-
mysql>SELECT LOG(45); +---------------------------------------------------------+ | LOG(45) | +---------------------------------------------------------+ | 3.806662 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT LOG(2,65536); +---------------------------------------------------------+ | LOG(2,65536) | +---------------------------------------------------------+ | 16.000000 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
LOG10(X)
此函数返回X的以10为底的对数。
mysql>SELECT LOG10(100); +---------------------------------------------------------+ | LOG10(100) | +---------------------------------------------------------+ | 2.000000 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
MOD(N,M)
此函数返回N除以M的余数。请考虑以下Example-
mysql>SELECT MOD(29,3); +---------------------------------------------------------+ | MOD(29,3) | +---------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
十月(N)
OCT()函数返回八进制数N的字符串表示形式。这等效于使用CONV(N,10,8)。
mysql>SELECT OCT(12); +---------------------------------------------------------+ | OCT(12) | +---------------------------------------------------------+ | 14 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
PI()
该函数仅返回pi的值。 MySQL内部存储pi的完整双精度值。
mysql>SELECT PI(); +---------------------------------------------------------+ | PI() | +---------------------------------------------------------+ | 3.141593 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
战俘(X,Y)
功率(X,Y)
这两个函数将X的值返回到Y的幂。
mysql> SELECT POWER(3,3); +---------------------------------------------------------+ | POWER(3,3) | +---------------------------------------------------------+ | 27 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
拉丹斯(X)
此函数返回X的值,将其从度转换为弧度。
mysql>SELECT RADIANS(90); +---------------------------------------------------------+ | RADIANS(90) | +---------------------------------------------------------+ | 1.570796 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
圆(X)
圆角(X,D)
此函数返回四舍五入到最接近的整数的X。如果提供了第二个参数D,则函数将X返回四舍五入到D的小数位。 D必须为正数,否则将删除小数点右边的所有数字。考虑以下Example-
mysql>SELECT ROUND(5.693893); +---------------------------------------------------------+ | ROUND(5.693893) | +---------------------------------------------------------+ | 6 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT ROUND(5.693893,2); +---------------------------------------------------------+ | ROUND(5.693893,2) | +---------------------------------------------------------+ | 5.69 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
签名(X)
此函数返回X的符号(负,零或正)为.1、0或1。
mysql>SELECT SIGN(-4.65); +---------------------------------------------------------+ | SIGN(-4.65) | +---------------------------------------------------------+ | -1 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT SIGN(0); +---------------------------------------------------------+ | SIGN(0) | +---------------------------------------------------------+ | 0 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT SIGN(4.65); +---------------------------------------------------------+ | SIGN(4.65) | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
罪恶(X)
此函数返回X的正弦值。请考虑以下Example-
mysql>SELECT SIN(90); +---------------------------------------------------------+ | SIN(90) | +---------------------------------------------------------+ | 0.893997 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
SQRT(X)
此函数返回X的非负平方根。考虑以下Example-
mysql>SELECT SQRT(49); +---------------------------------------------------------+ | SQRT(49) | +---------------------------------------------------------+ | 7 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
性病(表达)
STDDEV(表达式)
STD()函数用于返回表达式的标准偏差。这等效于取表达式的VARIANCE()的平方根。以下Example计算我们的CARS表中PRICE列的标准差-
mysql>SELECT STD(PRICE) STD_DEVIATION FROM CARS; +---------------------------------------------------------+ | STD_DEVIATION | +---------------------------------------------------------+ | 7650.2146 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
SO(X)
该函数返回以弧度表示的参数X的切线。
mysql>SELECT TAN(45); +---------------------------------------------------------+ | TAN(45) | +---------------------------------------------------------+ | 1.619775 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
截断(X,D)
此函数用于返回截断为小数位数D的X值。如果D为0,则删除小数点。如果D为负,则该值的整数部分中的D个值将被截断。考虑以下Example-
mysql>SELECT TRUNCATE(7.536432,2); +---------------------------------------------------------+ | TRUNCATE(7.536432,2) | +---------------------------------------------------------+ | 7.53 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
参考链接
https://www.learnfk.com/mysql/mysql-numeric-functions.html
标签：返回,Functions,set,0.00,无涯,Numeric,mysql,+---------------------------------------- From： https://blog.51cto.com/u_14033984/8599338