MySQL Basics Tutorial 8 — Numerical Functions of Functions

1. Arithmetic operators

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 –2⁶³.

• *

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

2. Mathematical functions

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

• ATAN(

  X)

Returns the arc tangent of

X, that is, the tangent is # The value of ##X.

mysql> SELECT ATAN(2);
-> 1.1071487177941
mysql> SELECT ATAN(-2);
-> -1.1071487177941

ATAN(
• Y

  ,X) , ATAN2(Y,X)

Returns the arctangent of two variables

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

CEILING(
• X

  ) CEIL(X)

returns no less than

X

The smallest integer value.

mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEIL(-1.23);
-> -1

The meaning of these two functions is the same. Note that the return value will be converted to a BIGINT.

COS(
• X

  )

Returns the cosine of

X

, where X Known in radians.

mysql> SELECT COS(PI());
-> -1

COT(
• X

  )

Returns the cotangent of

X

.

mysql> SELECT COT(12);
-> -1.5726734063977
mysql> SELECT COT(0);
-> NULL

CRC32(
• expr

  )

Calculate the cyclic redundancy code check value and return a 32-bit unsigned value. If the parameter is NULL, the result is NULL. The argument should be a string, and will be treated as such (if possible) if not a string.

mysql> SELECT CRC32('MySQL');
-> 3259397556
mysql> SELECT CRC32('mysql');
-> 2501908538

DEGREES(
• X

  )

Return parameter

X

, which is converted from radians to Spend.

mysql> SELECT DEGREES(PI());
-> 180
mysql> SELECT DEGREES(PI() / 2);
-> 90

EXP(
• X

  )

Returns the value of e raised to the power of

X

(naturally base of logarithms).

mysql> SELECT EXP(2);
-> 7.3890560989307
mysql> SELECT EXP(-2);
-> 0.13533528323661
mysql> SELECT EXP(0);
-> 1

FLOOR(
• X

  )

Returns the largest integer value not greater than

X

.

mysql> SELECT FLOOR(1.23);
-> 1
mysql> SELECT FLOOR(-1.23);
-> -2

Note that the return value will be converted into a BIGINT.

FORMAT(
• X

  ,D)

Format the number

X

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）！