MySQL statistical function record-numeric function _ MySQL

MySQL statistical function record-numeric function bitsCN.com

MySQL statistical function record-numeric function

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, if other parameters are integers, the result is an unsigned integer. See section 12.8 "Cast functions and operators ".

+

Plus sign:

Mysql> SELECT 3 + 5;

-> 8

-

Minus sign:

Mysql> SELECT 3-5;

->-2

-

Minus one dollar. Replace the parameter symbol.

Mysql> SELECT-2;

->-2

Note: If the same BIGINT operator is used at the same time, the return value is also a BIGINT. This means that you should try to avoid using an integer that may generate "-263 -.

*

Multiplication number:

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 calculated by BIGINT. (For details, see Section 11.2, "value type ".)

/

Except:

Mysql & gt; 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 result of execution is converted to an integer in the context of execution.

P

Integer division. Similar to FLOOR (), the BIGINT algorithm is also reliable.

Mysql> SELECT 5 p 2;

-> 2

12.4.2. mathematical functions

If an error occurs, all mathematical functions 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 arc cosine of X, that is, the cosine is the value of X. If X is not in the range from-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 arc sine of X, that is, the sine value of 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 of X.

Mysql> select atan (2 );

-> 1.1071487177941

Mysql> select atan (-2 );

->-1.1071487177941

ATAN (Y, X), ATAN2 (Y, X)

Returns the arc tangent of two variables X and Y. It is similar to the arc tangent calculation of Y or X unless the two 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 the smallest integer value not less than X.

Mysql> select ceiling (1.23 );

-> 2

Mysql> select ceil (-1.23 );

->-1

These two functions have the same meaning. Note that the returned value is converted to a BIGINT type.

COS (X)

Returns the cosine of X, where X is known in radians.

Mysql> select cos (PI ());

->-1

COT (X)

Returns the tangent of X.

Mysql> select cot (12 );

->-1.5726734063977

Mysql> select cot (0 );

-> NULL

CRC32 (expr)

Calculate the cyclic bytecode check value and return a 32-bit unsigned value. If the parameter is NULL, the result is NULL. This parameter should be a string and will be processed as a string if it is not a string (if possible ).

Mysql> SELECT CRC32 ('mysql ');

-> 3259397556

Mysql> SELECT CRC32 ('mysql ');

-> 2501908538

DEGREES (X)

Returns parameter X, which is converted from radians to degrees.

Mysql> select degrees (PI ());

-> 180

Mysql> select degrees (PI ()/2 );

-> 90

EXP (X)

Returns the value of X after multiplication (base of the natural logarithm) of e ).

Mysql> select exp (2 );

-> 7.3890560989307

Mysql> select exp (-2 );

-> 0.13533528323661

Mysql> select exp (0 );

-> 1

FLOOR (X)

Returns the maximum integer not greater than X.

Mysql & gt; select floor (1.23 );

-> 1

Mysql> select floor (-1.23 );

->-2

Note that the returned value is converted to a BIGINT type.

FORMAT (X, D)

Write the number X in the format '#,###,###. # 'format, that is, retain the D digit after the decimal point, while the D-digit is rounded to and the result is returned as a string. For details, see section 12.9.4 "other functions ".

LN (X)

Returns the natural logarithm of X, that is, the logarithm of X relative to the base e.

Mysql> select ln (2 );

-> 0.69314718055995

Mysql> select ln (-2 );

-> NULL

This function has the same significance as LOG (X.

LOG (X) LOG (B, X)

If a parameter is used, this function returns the natural logarithm of X.

Mysql> select log (2 );

-> 0.69314718055995

Mysql> select log (-2 );

-> NULL

If two parameters are used, this function returns the logarithm of X to any base B.

Mysql> select log (2,65536 );

-> 16

Mysql & gt; select log (10,100 );

-> 2

LOG (B, X) is equivalent to LOG (X)/LOG (B ).

LOG2 (X)

Returns the logarithm of base 2 of X.

Mysql> SELECT LOG2 (65536 );

-> 16

Mysql> SELECT LOG2 (-100 );

-> NULL

LOG2 () is very effective when detecting how many bits are required to store a number. This function is equivalent to the expression LOG (X)/LOG (2 ).

LOG10 (X)

Returns the base 10 logarithm of X.

Mysql> SELECT LOG10 (2 );

-> 0.30102999566398

Mysql> SELECT LOG10 (100 );

-> 2

Mysql> SELECT LOG10 (-100 );

-> NULL

LOG10 (X) is equivalent to LOG (10, X ).

MOD (N, M), N % M N MOD M

Mode operation. Returns the remainder after N is divided by M.

Mysql> select mod (234, 10 );

-> 4

Mysql> SELECT 253% 7;

-> 1

Mysql> select mod (29,9 );

-> 2

Mysql> SELECT 29 MOD 9;

-> 2

This function supports the use of BIGINT values.

MOD () also applies to values with decimals. it returns the exact remainder after division:

Mysql> select mod (34.5, 3 );

-> 1.5

PI ()

? (Pi) value. By default, the number of decimal places is 7. However, full dual-precision values are used in MySQL.

Mysql> select pi ();

-> 3.141593

Mysql> select pi () + 0.000000000000000000;

-> 3.141592653589793116

POW (X, Y), POWER (X, Y)

Returns the result of the Y multiplication of X.

Mysql> select pow (2, 2 );

-> 4

Mysql> select pow (2,-2 );

-> 0.25

RADIANS (X)

Returns the X parameter that converts degrees to radians. (note? Radians are equal to 180 degrees ).

Mysql> select radians (90 );

-> 1.5707963267949

RAND () RAND (N)

Returns a random floating point value v, ranging from 0 to 1 (that is, the range is 0 ≤ v ≤ 1.0 ). If an integer parameter N is specified, it is used as the seed value to generate a recurring series.

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

To obtain a random integer R in the range of I ≤ R ≤ j, use the expression FLOOR (I + RAND () * (j-I + 1 )). For example, to get a random integer in the range of 7 to 12 (including 7 and 12), you can use the following statement:

Select floor (7 + (RAND () * 6 ));

In the order by statement, a column with the RAND () value cannot be used because order by calculates the time of multiple columns. However, you can retrieve data rows in the following random order:

Mysql> SELECT * FROM tbl_name order by rand ();

Combination of order by rand () and LIMIT it is useful to select random samples from a group of columns:

Mysql> SELECT * FROM table1, table2 WHERE a = B AND c

-> Order by rand () LIMIT 1000;

Note: in the WHERE statement, RAND () is calculated again every time WHERE is executed.

RAND () is not used as an accurate random generator, but a fast method for moving ad hoc random numbers between platforms of the same MySQL version.

ROUND (X) ROUND (X, D)

Return parameter X, whose value is close to the nearest integer. If there are two parameters, X is returned, and its value is retained to the D digit after the decimal point, and the D-digit is retained to rounding. To retain the D to the left of the X decimal point, set D to a negative value.

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 & gt; select round (23.298,-1 );

-> 20

The type of the returned value is the same as that of the first independent variable (assuming it is an integer, double-precision number, or decimal number ). This means that for an integer parameter, the result is also an integer (no decimal part ).

When the first parameter is a decimal constant, ROUND () uses the precision mathematical question bank for the exact value parameter:

For an exact number, ROUND () uses the "rounding" or "rounding to the nearest number" rule: for a score part, it is. 5 or greater. the value of 5. the positive value is rounded up to the nearest integer, and the negative value is rounded down to the nearest integer. (In other words, the rounding direction is the direction away from zero on the number axis ). For a value with a fraction less than. 5, a positive number is placed in the next integer, a negative number is placed in the adjacent integer, and a positive number is rounded to the adjacent integer.

For approximate values, the result depends on the C database. In many systems, this means that the use of ROUND () follows the rule of "rounding to the nearest even number: A value with any decimal part is rounded to the nearest even integer.

The following example shows the differences between the exact value and the approximate value of the rounding method:

Mysql> select round (2.5), ROUND (25E-1 );

+ ------------ + -------------- +

| ROUND (2.5) | ROUND (25E-1) |

+ ------------ + -------------- +

| 3 | 2 |

+ ------------ + -------------- +

For details, see Chapter 1 precision mathematics.

SIGN (X)

The return parameter is a-1, 0, or 1 symbol, depending on whether the value of X is negative, zero, or positive.

Mysql> select sign (-32 );

->-1

Mysql> select sign (0 );

-> 0

Mysql> select sign (1, 234 );

-> 1

SIN (X)

Returns X sine, where X is given in radians.

Mysql> select sin (PI ());

-> 1.2246063538224e-16

Mysql> select round (SIN (PI ()));

-> 0

SQRT (X)

Returns the quadratic root of non-negative X.

Mysql> select sqrt (4 );

-> 2

Mysql> select sqrt (20 );

-> 4.4721359549996

Mysql> select sqrt (-16 );

-> NULL

TAN (X)

Returns the tangent of X, where X is given in radians.

Mysql> select tan (PI ());

->-1.2246063538224e-16

Mysql> select tan (PI () + 1 );

-> 1.5574077246549

TRUNCATE (X, D)

Returns the number X rounded to the decimal point. If the value of D is 0, the result does not contain the decimal point or decimal part. You can set D to a negative number. if you want to cut (return to zero) X to the left of the decimal point, all the low values after the start of the nth digit.

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 *, 0 );

-> 1028

The rounding direction of all numbers is close to zero.

BitsCN.com