Common functions of MySQL

1. IF (EXPR1,EXPR2,EXPR3) function:

If Expr1 is true, the return value of if () is expr2; Otherwise the return value is EXPR3.

The return value of IF () is a numeric value or a string value, depending on the context in which it is located.

Cases:

2, ifnull (EXPR1,EXPR2) function:

If EXPR1 is not NULL, the return value of Ifnull () is expr1; Otherwise its return value is EXPR2.

The return value of Ifnull () is either a number or a string, depending on the context in which it is used.

3, CONCAT (str1,str2,...) function:
Returns the string resulting from the connection parameter. If any one of the arguments is NULL, the return value is null. The
may have one or more parameters. If all parameters are non-binary strings, the result is a non-binary string.
If the argument contains any twos string, the result is a binary string.
A numeric parameter is converted to an equal binary string format;
To avoid this, you can use explicit type cast,
For example: SELECT CONCAT (CAST (Int_col as CHAR), Char_col)

4, INSTR (STR,SUBSTR) function: The
returns the first occurrence of a substring of a string str. Returns 0 if not found.

5, floor (numeric) rounding function:

6, cast () type conversion function:
for data type conversions, MySQL provides the cast () function, which converts a value to the specified data type. The
data types are: binary,char,date,time,datetime,signed,unsigned

7, Group_concat () type conversion functions:
Usage: Group_concat ([DISTINCT] The field to be connected [order by Asc/desc sort field] [Separator ' delimiter '])

(1), requirement: Each userid is a row, And each line shows all the scores for that userid.
Select Userid,group_concat (score) from Test GROUP by UserID

(2), requirement: de-duplication score
SELECT Userid,group_concat ( DISTINCT score) from Test4 GROUP by UserID

(3), requirement: Sort score
SELECT Userid,group_concat (score ORDER by score DESC) From Test4 GROUP by userid<br/>! [] (http://i2.51cto.com/images/blog/201806/26/e0f7d8d844207b743474e25c90891f93.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=)
(4), requirement: Set delimiter, default to comma, set delimiter @.

8. ROUND () rounding function:
Mysql> Select round (5.2);

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

| Round (5.2) |

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

| 5 |

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

Mysql> Select round (5.5);

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

| Round (5.5) |

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

| 6 |

+------------+
9. Left (str, length) intercepts the string from the start:
Description: Left (truncated field, intercept length)
Mysql> Select Left (' 123456789 ', 3);
+------------+
| 123 |
+------------+
10. Right (str, length) intercepts the string:
Description: Right (truncated field, intercept length)
Mysql> Select Right (' 123456789 ', 3);
+------------+
| 789 |
+------------+
11, substring (str, pos,length) intercepts the string:
Description
SUBSTRING (intercepted field, starting from the first intercept)
SUBSTRING (intercepted field, starting from the first intercept, intercept length)

mysql> Select substring (' 123456789 ', 3);
+--------------------+
| 3456789 |
+---------------------+

Common functions of MySQL