Common functions of MySQL

Original Address reference: http://www.cnblogs.com/ringwang/archive/2008/07/05/1236292.html

1. Control Flow function

1.1 Ifnull (EXPR1,EXPR2)

If EXPR1 is not Null,ifnull () returns EXPR1, it returns EXPR2.

Mysql> SelectIfnull (1,0);  - 1MySQL> SelectIfnull (0,Ten);  - 0MySQL> SelectIfnull (1/0,Ten);  - TenMySQL> SelectIfnull (1/0,'Yes');  - 'Yes'

1.2 IF (EXPR1,EXPR2,EXPR3)

If Expr1 is true (expr1<>0 and Expr1<>NULL), then if () returns EXPR2, otherwise it returns EXPR3. IF () returns a numeric or string value, depending on the context in which it is used.

Mysql> Select IF(1>2,2,3);  - 3MySQL> Select IF(1<2,'Yes','No');  - 'Yes'MySQL> Select IF(strcmp ('Test','test1'),'Yes','No');  - 'No'

EXPR1 is calculated as an integer value, which means that if you are testing floating-point or string values, you should use a comparison operation to do so.

Mysql> Select IF(0.1,1,0);  - 0MySQL> Select IF(0.1<>0,1,0);  - 1

In the first case above, if (0.1) returns 0, because 0.1 is transformed to an integer value, causing the test if (0).

1.3 Case Value when [compare-value] then result [when [compare-value] then result] [ELSE result] END

case is [condition] then result [when [condition] then result] [ELSE result] END

The first version returns result, where value=Compare-value. In the second version, if the first condition is true, a result is returned. If there is no matching result value, then the result after else is returned. If there is no else part, then NULL is returned.

Mysql> SELECT  Case 1  when 1  Then"One" when 2  Then"Both"ELSE"More"END;  -"one" MySQL> SELECT  Case  when 1>0  Then"True"ELSE"False"END;  -"true" MySQL> SELECT  Case BINARYB whenA Then 1  when"B" Then 2 END;  - NULL

2. RAND (N)

Returns a random floating-point value within a range of 0 to 1.0. If an integer parameter n is specified, it is used as the seed value.

Mysql> Select RAND();  - 0.5925MySQL> Select RAND( -);  - 0.1811MySQL> Select RAND( -);  - 0.1811MySQL> Select RAND();  - 0.2079MySQL> Select RAND();  - 0.7888

3. TRUNCATE (x,d)

Returns the number x, truncated to D decimal. If D is 0, the result will not have a decimal point or fractional part.

Mysql> Select TRUNCATE(1.223,1);  - 1.2MySQL> Select TRUNCATE(1.999,1);  - 1.9MySQL> Select TRUNCATE(1.999,0);  - 1

4. CHAR (N,)
CHAR () interprets parameters as integers and returns a string consisting of ASCII code characters for those integers. A null value was skipped.

Mysql> Select CHAR( the,121, the,Bayi,' the');  - 'MySQL'MySQL> Select CHAR( the,77.3,'77.3');  - 'MMM'

5. CONCAT (STR1,STR2,)
Returns the string from the parameter link. Returns NULL if any parameter is null. There can be more than 2 parameters. A numeric parameter is transformed into an equivalent string form.

Mysql> SelectCONCAT ('My','S','QL');  - 'MySQL'MySQL> SelectCONCAT ('My',NULL,'QL');  - NULLMySQL> SelectCONCAT (14.3);  - '14.3'

6. LENGTH (str)

Returns the length of the string str.

MySQL>Select LENGTH ('text');          - 4

7. Now ()

Common functions of MySQL