Functions commonly used in 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 either a numeric value or a string value, depending on its context.
Example:
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 a number or a string, depending on the context in which it is used.
3. CONCAT (str1,str2,...) Function:
Returns a string that results in a connection parameter. If any parameter 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 binary string, the result is a binary string.
A numeric parameter is converted to its equivalent binary string format
To avoid this, you can use the explicit type cast
For example: SELECT CONCAT (CAST (int_col AS CHAR), char_col)
4. INSTR (str,substr) function:
Returns the first occurrence of the substring of the string str. If it cannot be found, return 0.
5. FLOOR (numerical value) takes the whole function:
6. Cast () type conversion function:
For data type conversion, mysql provides the cast () function, which converts a value to a specified data type.
Data types are: binary,char,date,time,datetime,signed,unsigned
7. Group_concat () type conversion function:
Usage: group_concat ([DISTINCT] the field to be connected [Order BY ASC/DESC sort field] [Separator 'delimiter'])
(1), requirements: each userid is a row, and each line displays all the scores of the userid.
SELECT userid,GROUP_CONCAT (score) FROM test GROUP BY userid
(2) requirements: to repeat score
SELECT userid,GROUP_CONCAT (DISTINCT score) FROM test4 GROUP BY userid
(3) requirements: sort score
SELECT userid,GROUP_CONCAT (score ORDER BY score DESC) FROM test4 GROUP BY userid! [] (https://s1.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), demand: set the delimiter, default is 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 left:
Description: left (intercepted field, intercepted length)
Mysql > select left ('123456789)
+-+
| | 123 |
+-+
10. Right (str, length) intercepts the string from the right:
Description: right (intercepted field, intercepted length)
Mysql > select right ('123456789)
+-+
| | 789 |
+-+
11. Substring (str, pos,length) intercepts the string:
Description:
Substring (intercepted field, from which bit to start)
Substring (intercepted field, which bit to intercept, intercept length)
Mysql > select substring ('123456789)
+-+
| | 3456789 |
+-+
12. LOCATE (substr,str) or POSITION (substr IN str) function:
Function returns the position of the first occurrence of the substring substr in the string str. If the substring substr does not exist in str, the return value is 0.
Mysql > SELECT LOCATE ('bar',' foobarbar')
-> 4
Mysql > SELECT LOCATE ('xbar',' foobar')
-> 0
