MySQL Common functions

One: Mathematical functions

SELECT Greatest (2,3,4), Greatest (1.1,3.2);

SELECT LEAST (2,3,4), LEAST (1.1,3.2);

Floor (X)

Returns the maximum integer value that is not greater than X.

Mysql> SELECT Floor (1.23);

1

Mysql> SELECT Floor (-1.23);

-2

LOG10 (X)

Returns the logarithm of the cardinality of X of 10.

Mysql> SELECT LOG10 (2);

0.30102999566398

mysql> SELECT LOG10 (100);

2

Mysql> SELECT LOG10 (-100);

, NULL

POW (x, y), POWER (x, y)

Returns the result value of the Y-exponentiation of X.

Mysql> SELECT POW (2,2);

4

Mysql> SELECT POW (2,-2);

0.25

ROUND (X) ROUND (x,d)

Returns the parameter x, whose value is close to the nearest integer. In the case of two parameters, an X is returned, its value is retained to the D-bit after the decimal point, and the D-bit is rounded. To retain the D-bit to the left of the decimal point of the X value, 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

Two: aggregation function

SELECT * from Core;

SELECT COUNT (*) from core;

SELECT MIN (math), MAX (physics) from core;

SELECT AVG (math) from core;

SELECT SUM (math) from core;

Three: String functions

ASCII (str)

The return value is the value of the leftmost character of the string str. If Str is an empty string, the return value is 0. If STR is NULL, the return value is null. ASCII () is used for characters with numeric values from 0 to 255.

mysql> SELECT ASCII (' 2 ');

50

Bit_length (str)

Returns the value of the binary string str length.

mysql> SELECT bit_length (' text ');

32

CHAR (N,... [USING CharSet])

CHAR () understands each parameter n as an integer whose return value is a string containing the characters given by the code value of those integers. Null values are omitted.

Mysql> SELECT CHAR (77,121,83,81, ' 76 ');

' MySQL '

Mysql> SELECT CHAR (77,77.3, ' 77.3 ');

' MMM '

CONCAT (STR1,STR2,...)

Returns the string that results from the connection parameter. If any one of the arguments is NULL, the return value is null. There may be one or more parameters. If all parameters are non-binary strings, the result is a non-binary string. If the argument contains any twos binary string, the result is a binary string. A numeric parameter is converted to the equivalent binary string format; To avoid this, you can use explicit type cast, for example: SELECT CONCAT (CAST (Int_col as CHAR), Char_col)

mysql> SELECT CONCAT (' My ', ' S ', ' QL ');

' MySQL '

mysql> SELECT CONCAT (' My ', NULL, ' QL ');

, NULL

Mysql> SELECT CONCAT (14.3);

' 14.3 '

INSTR (STR,SUBSTR)

Returns the first occurrence of a substring of string str. This is the same as the two-parameter form of locate (), unless the order of the arguments is reversed.

mysql> SELECT INSTR (' Foobarbar ', ' Bar ');

4

mysql> SELECT INSTR (' Xbar ', ' foobar ');

0

Left (Str,len)

Returns the leftmost character of Len beginning with the string str.

Mysql> SELECT left (' Foobarbar ', 5);

' Fooba '

LENGTH (str)

The return value is the length of the string str, in bytes. One multibyte character is counted as multibyte. This means that for a string containing 5 2-byte characters, the return value of LENGTH () is 10, and the return value of Char_length () is 5.

mysql> SELECT LENGTH (' text ');

4

Four: Time-date function

Adddate (Date,interval expr type) adddate (expr,days)

When activated by the interval format of the second argument, adddate () is synonymous with date_add (). The correlation function subdate () is synonymous with date_sub (). For information on the interval parameter, see the discussion about Date_add ().

mysql> SELECT date_add (' 1998-01-02 ', INTERVAL);

' 1998-02-02 '

mysql> SELECT adddate (' 1998-01-02 ', INTERVAL);

' 1998-02-02 '

If the days parameter is only an integer value, MySQL 5.1 adds it as a day value to expr.

mysql> SELECT adddate (' 1998-01-02 ', 31);

' 1998-02-02 '

Addtime (EXPR,EXPR2)

Addtime () adds expr2 to expr and returns the result. Expr is a time or time-date expression, and expr2 is a time expression.

mysql> SELECT addtime (' 1997-12-3123:59:59.999999 ',

' 1 1:1:1.000002 ');

' 1998-01-02 01:01:01.000001 '

mysql> SELECT addtime (' 01:00:00.999999 ', ' 02:00:00.999998 ');

' 03:00:01.999997 '

V: Process Control functions

The front is empty, back, followed by empty back

Selectifnull (1,10), Ifnull (null,3), ifnull (3,null);

Determines whether the value is equal, returns an empty equal, and returns the first

Selectnullif, Nullif (' A ', ' C '), Nullif (3+1,2+2);

The condition is true to return the first, returning the second for a false

Selectif (1<10,2,3), IF (1>10, ' true ', ' false ');

Output results Go

SELECT case ' green '

When the ' red ' then ' stop '

When the ' green ' then ' go '

END;

Output N/A

SELECT Case 9

When 1 Then ' a '

When 2 Then ' B '

ELSE ' N/a '

END;

Output Result: OK

SELECT case

When (=4) and then ' OK '

When (<>4) "Not OK"

END as STATUS;

Copyright Notice: Bo Master original articles, reproduced please indicate the source. Http://blog.csdn.net/dzy21

MySQL Common functions