Common arithmetic operators can be used. Note that for -, + and *, if both parameters are positive numbers, the accuracy of the calculation result is BIGINT (64 bits). If one of the parameters is an unsigned integer and the other parameters are also integers, the result is an unsigned integer.
+
Plus sign:
mysql> SELECT 3+5; -> 8
-
Minus sign:
mysql> SELECT 3-5; -> -2
-
One dollar minus sign. Replace parameter symbols.
mysql> SELECT - 2; -> -2
Note: If the operator is used with a BIGINT at the same time, the return value is also a BIGINT. This means that you should try to avoid using – with integers that might result in –263.
*
Multiplication sign:
mysql> SELECT 3*5; -> 15 mysql> SELECT 18014398509481984*18014398509481984.0; -> 324518553658426726783156020576256.0 mysql> SELECT 18014398509481984*18014398509481984; -> 0
The result of the last expression is incorrect. The reason is that the result of integer multiplication exceeds the 64-bit range of BIGINT calculations.
/
Division sign:
mysql> SELECT 3/5; -> 0.60
The result of division by zero is NULL:
mysql> SELECT 102/(1-1); -> NULL
Division is used with the BIGINT algorithm only when the execution context requires that the result be converted to an integer.
p
Integer division. Similar to FLOOR(), however using the BIGINT algorithm is also reliable.
mysql> SELECT 5 p 2; -> 2
If an error occurs, all mathematical functions will return NULL.
ABS(X)
Returns the absolute value of X.
mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32
This function supports the use of BIGINT values.
ACOS(X)
returns the inverse cosine of X, that is, the cosine is The value of X. If X is not in the range of -1 to 1, NULL is returned.
mysql> SELECT ACOS(1); -> 0 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.5707963267949
ASIN(X)
Returns the inverse sine of X, that is, the sine is # The value of ##X. If X If X is not in the range of -1 to 1, NULL is returned.
mysql> SELECT ASIN(0.2); -> 0.20135792079033 mysql> SELECT ASIN('foo');+-------------+ | ASIN('foo') | +-------------+ | 0 | +-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS;+---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' | +---------+------+-----------------------------------------+
X)
X, that is, the tangent is # The value of ##X. mysql> SELECT ATAN(2);
-> 1.1071487177941
mysql> SELECT ATAN(-2);
-> -1.1071487177941
,X) , ATAN2(Y,X)
and Y. It is similar to the arctangent calculation of Y or X, except that the signs of both parameters are used to determine the quadrant of the result. mysql> SELECT ATAN(-2,2);
-> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
-> 1.5707963267949
) CEIL(X)
The smallest integer value. mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEIL(-1.23);
-> -1
)
, where X Known in radians. mysql> SELECT COS(PI());
-> -1
)
. mysql> SELECT COT(12);
-> -1.5726734063977
mysql> SELECT COT(0);
-> NULL
)
mysql> SELECT CRC32('MySQL'); -> 3259397556 mysql> SELECT CRC32('mysql'); -> 2501908538
)
, which is converted from radians to Spend. mysql> SELECT DEGREES(PI());
-> 180
mysql> SELECT DEGREES(PI() / 2);
-> 90
)
(naturally base of logarithms). mysql> SELECT EXP(2);
-> 7.3890560989307
mysql> SELECT EXP(-2);
-> 0.13533528323661
mysql> SELECT EXP(0);
-> 1
)
. mysql> SELECT FLOOR(1.23);
-> 1
mysql> SELECT FLOOR(-1.23);
-> -2
,D)
Written in the format of '#,,.##', that is, D digits are retained after the decimal point, and the D digit is retained by rounding, and then the result is returned in the form of a string.
LN(X)
Returns the natural logarithm of X, that is, X is the logarithm relative to base e.
mysql> SELECT LN(2); -> 0.69314718055995 mysql> SELECT LN(-2); -> NULL
This function has the same meaning as LOG(X).
LOG(X) LOG(B,X)
If Called with one argument, this function will return the natural logarithm of X.
mysql> SELECT LOG(2); -> 0.69314718055995 mysql> SELECT LOG(-2); -> NULL
If called with two parameters, this function will return the logarithm of X for any base B.
mysql> SELECT LOG(2,65536); -> 16 mysql> SELECT LOG(10,100); -> 2
LOG(B,X) is equivalent to LOG(X) / LOG(B).
LOG2(X)
返回X 的基数为2的对数。
mysql> SELECT LOG2(65536); -> 16 mysql> SELECT LOG2(-100); -> NULL
对于查出存储一个数字需要多少个比特,LOG2()非常有效。这个函数相当于表达式 LOG(X) / LOG(2)。
LOG10(X)
返回X的基数为10的对数。
mysql> SELECT LOG10(2); -> 0.30102999566398 mysql> SELECT LOG10(100); -> 2 mysql> SELECT LOG10(-100); -> NULL
LOG10(X)相当于LOG(10,X)。
MOD(N,M) , N % M N MOD M
模操作。返回N 被 M除后的余数。
mysql> SELECT MOD(234, 10); -> 4 mysql> SELECT 253 % 7; -> 1 mysql> SELECT MOD(29,9); -> 2 mysql> SELECT 29 MOD 9; -> 2
这个函数支持使用BIGINT 值。
MOD() 对于带有小数部分的数值也起作用, 它返回除法运算后的精确余数:
mysql> SELECT MOD(34.5,3); -> 1.5
PI()
返回 ϖ (pi)的值。默认的显示小数位数是7位,然而 MySQL内部会使用完全双精度值。
mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116
POW(X,Y) , POWER(X,Y)
返回X 的Y乘方的结果值。
mysql> SELECT POW(2,2); -> 4 mysql> SELECT POW(2,-2); -> 0.25
RADIANS(X)
返回由度转化为弧度的参数 X, (注意 ϖ 弧度等于180度)。
mysql> SELECT RADIANS(90); -> 1.5707963267949
RAND() RAND(N)
返回一个随机浮点值 v ,范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。若已指定一个整数参数 N ,则它被用作种子值,用来产生重复序列。
mysql> SELECT RAND(); -> 0.9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881 mysql> SELECT RAND(20); -> 0.15888261251047
若要在i ≤ R ≤ j 这个范围得到一个随机整数R ,需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如, 若要在7 到 12的范围(包括7和12)内得到一个随机整数, 可使用以下语句:
SELECT FLOOR(7 + (RAND() * 6));
在ORDER BY语句中,不能使用一个带有RAND()值的列,原因是 ORDER BY 会计算列的多重时间。然而,可按照如下的随机顺序检索数据行:
mysql> SELECT * FROM tbl_name ORDER BY RAND();
ORDER BY RAND()同 LIMIT 的结合从一组列中选择随机样本很有用:
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;
注意,在WHERE语句中,WHERE每执行一次, RAND()就会被再计算一次。
RAND()的作用不是作为一个精确的随机发生器,而是一种用来发生在同样的 MySQL版本的平台之间的可移动ad hoc随机数的快速方式。
ROUND(X) ROUND(X,D)
返回参数X, 其值接近于最近似的整数。在有两个参数的情况下,返回 X ,其值保留到小数点后D位,而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位,可将 D 设为负值。
mysql> SELECT ROUND(-1.23); -> -1 mysql> SELECT ROUND(-1.58); -> -2 mysql> SELECT ROUND(1.58); -> 2 mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1 mysql> SELECT ROUND(23.298, -1); -> 20
返回值的类型同 第一个自变量相同(假设它是一个整数、双精度数或小数)。这意味着对于一个整数参数,结果也是一个整数(无小数部分)。
当第一个参数是十进制常数时,对于准确值参数,ROUND() 使用精密数学题库:
对于准确值数字, ROUND() 使用“四舍五入” 或“舍入成最接近的数” 的规则:对于一个分数部分为 .5或大于 .5的值,正数则上舍入到邻近的整数值, 负数则下舍入临近的整数值。(换言之, 其舍入的方向是数轴上远离零的方向)。对于一个分数部分小于.5 的值,正数则下舍入下一个整数值,负数则下舍入邻近的整数值,而正数则上舍入邻近的整数值。
对于近似值数字,其结果根据C 库而定。在很多系统中,这意味着 ROUND()的使用遵循“舍入成最接近的偶数”的规则: 一个带有任何小数部分的值会被舍入成最接近的偶数整数。
以下举例说明舍入法对于精确值和近似值的不同之处:
mysql> SELECT ROUND(2.5), ROUND(25E-1); +------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 2 | +------------+--------------+
SIGN(X)
返回参数作为-1、 0或1的符号,该符号取决于X 的值为负、零或正。
mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1
SIN(X)
返回X 正弦,其中 X 在弧度中被给定。
mysql> SELECT SIN(PI()); -> 1.2246063538224e-16 mysql> SELECT ROUND(SIN(PI())); -> 0
SQRT(X)
返回非负数X 的二次方根。
mysql> SELECT SQRT(4); -> 2 mysql> SELECT SQRT(20); -> 4.4721359549996 mysql> SELECT SQRT(-16); -> NULL
TAN(X)
返回X 的正切,其中X 在弧度中被给定。
mysql> SELECT TAN(PI()); -> -1.2246063538224e-16 mysql> SELECT TAN(PI()+1); -> 1.5574077246549
TRUNCATE(X,D)
返回被舍去至小数点后D位的数字X。若D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值.
mysql> SELECT TRUNCATE(1.223,1); -> 1.2 mysql> SELECT TRUNCATE(1.999,1); -> 1.9 mysql> SELECT TRUNCATE(1.999,0); -> 1 mysql> SELECT TRUNCATE(-1.999,1); -> -1.9 mysql> SELECT TRUNCATE(122,-2); -> 100 mysql> SELECT TRUNCATE(10.28*100,0); -> 1028
所有数字的舍入方向都接近于零。
以上就是MySQL基础教程8 —— 函数之数值函数的内容,更多相关内容请关注PHP中文网(www.php.cn)!