MySQL statistical function record-numeric Function

MySQL statistical function record-numeric functions can use common arithmetic operators. 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 ". Www.2cto.com + + plus sign: mysql> SELECT 3 + 5;-> 8-minus sign: mysql> SELECT 3-5;->-2-unary minus sign. 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 ".) /Division: mysql> SELECT 3/5;-> 0.60 the result of division by zero is NULL: mysql> SELECT 102/(1-1);-> NULL is only used in the context of execution, division is used with the BIGINT algorithm only when the result is converted to an integer. DIV integer division. Similar to FLOOR (), the BIGINT algorithm is also reliable. Mysql> SELECT 5 DIV 2;-> 212.4.2. If a mathematical function is incorrect, 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 using 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);-> NULLmysql> 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 + | 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 (-0.78539816339745);->-1.5707963267949 mysql> SELECT ATAN2 (PI (), 0);-> 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 both 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 ();->-1COT (X) returns the remainder of X. Mysql> select cot (12);->-1.5726734063977 mysql> select cot (0);-> NULLCRC32 (expr) calculates the cyclic signature code check value and returns 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) return parameter X, this parameter is converted from radians to degrees. Mysql> select degrees (PI ();-> 180 mysql> select degrees (PI ()/2);-> 90EXP (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> select floor (1.23);-> 1 mysql> select floor (-1.23);->-2 note that the return value is converted into a BIGINT. FORMAT (X, D) writes the FORMAT of number X '#,###,###. # '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 has the same meaning as LOG (X. If LOG (X) LOG (B, X) is called with a parameter, this function returns the natural logarithm of X. Mysql> select log (2);-> 0.69314718055995 mysql> select log (-2);-> If NULL is called using two parameters, this function returns the logarithm of X to any base B. Mysql> select log (10,100);-> 16 mysql> select log ();-> 2 LOG (B, X) is equivalent to LOG (X)/LOG (B ). LOG2 (X) returns the logarithm of X whose base is 2. Mysql> SELECT LOG2 (65536);-> 16 mysql> SELECT LOG2 (-100);-> NULL is very effective for finding out 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);-> NULLLOG10 (X) it 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 ();-> 2 mysql> SELECT 29 MOD 9; -> 2. This function supports the BIGINT value. MOD () also applies to values with decimal places. It returns the exact remainder after Division: mysql> select mod (34.5, 3);-> 1.5PI () returns? (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.141592653589793366pow (X, Y), POWER (X, Y) returns the result of the Y multiplication of X. Mysql> select pow (0.25);-> 4 mysql> select pow (2,-2);-> RADIANS (X) returns the parameter X from 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 value 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 if you want to obtain a random integer R in the range of I ≤ R ≤ j, the expression FLOOR (I + RAND () * (j-I + 1) is required )). For example, to obtain a random integer in the range of 7 to 12 (including 7 and 12), 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 (); order by rand () it is useful to SELECT random samples FROM a group of columns in combination with LIMIT: mysql> SELECT * FROM table1, table2 WHERE a = B AND c <d-> 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) returns 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> select round (23.298,-1 ); -> 20 the return value is of the same type as 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, for an exact value parameter, ROUND () uses a precise mathematical question bank: For an exact value number, ROUND () use the "Rounding" or "rounding to the nearest number" rule: for a score section. 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 and approximate values of the rounding method: mysql> select round (2.5), ROUND (25E-1); + ------------ + -------------- + | ROUND (2.5) | ROUND (25E-1) | + ------------ + -------------- + | 3 | 2 | + ------------ + -------------- + For details, see chapter 24th: precision mathematics. SIGN (X) returns a parameter as a SIGN of-1, 0, or 1, depending on whether X is negative, zero, or positive. Mysql> select sign (-32);->-1 mysql> select sign (0);-> 0 mysql> select sign (234);-> 1SIN (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*1028, 0);-> the rounding direction of all numbers is close to zero