The function of learning MySQL with little white

# # Lead Once I thought, learned the Select, UPDATE, insert and delete, I learned the database ~, but to the company to see the SQL is full of all kinds of functions, I almost believe ~, the original self is how naïve, O (╯-╰) O now the powerful database functions, so that a lot of business logic to the database, more transactions and stored procedures appear in the system, therefore, splicing strings become the necessary skills of modern programmers, in order to learn to splice thousands of rows of SQL, statements, you cannot master the following functions. # # # CONCAT ( ) can be described as a string of functions of a brother, with him, it is possible to achieve two or even a number of statements splicing. "' Mysql> Select Concat (' Ma Yun ', ' is Cloud Lao Wang ', '! '); +--------------------------------------------+| Concat (' Ma Yun ', ' is Cloud Lao Wang ', '! ') |+--------------------------------------------+| Ma Yun is cloud Lao wang!                  | +--------------------------------------------+```

Pay particular attention to whether space is required at the junction of each statement, and NULL if every statement inside is empty.

# # # CONCAT_WS () and the first floor are brothers, using the way Concat_ws (Separator,str1,str2,...), where the first is a delimiter, which is automatically added to each of the subsequent two strings. There are a lot of times when you spell a very large number of SQL statements, commonly used this function, such as where to add 5 and conditional sentence, this time it will show the skill. "' Mysql> Select Concat_ws (' and ', ' c=5 ', IF (1>2, ' d=3 ', null), ' b=2 '); +---------------------------------------- -----------+| Concat_ws (' and ', ' c=5 ', IF (1>2, ' d=3 ', null), ' b=2 ') |+---------------------------------------------------+| C=5 and b=2                                       |+---------------------------------------------------+ "

Tips & Notes: The first key to separate characters for and, note that there are spaces on both sides. Note that this function cannot skip the empty string, only skips null, so if there is a judgment statement inside the string entry, do not assign the string to null.

# # # ELT   *elt (N,STR1,STR2,STR3,...) * This function is a supplementary function of the field function, according to the value of N to select the nth string, such as sex in the data table is generally stored as a number, such as 1 for the male, 2 for the female, when we need to show the gender code according to sex, we can use the ELT function. "' Mysql> Select ELT (1, ' Male ', ' female '); +------------------+| ELT (1, ' Male ', ' female ') |+------------------+| Male               |+------------------+ "' This function can also be used as an extension of SQL statements, such as the choice of the parameters to choose the Sort method, etc., which greatly enhance the processing power of a SQL statement, similar to the programming language of the case or if statements.

# # # IF   *if (EXPR1,EXPR2,EXPR3) *if function probably a lot of people will feel too simple, real-time indeed, but the more simple things are easier to ignore some details and make mistakes, some time ago discovered a bug caused by IF. The general situation is as follows: "' Mysql> Select if (' Hello ', ' 1 ', ' 2 '); +---------------------+| if (' Hello ', ' 1 ', ' 2 ') |+---------------------+| 2                   |+---------------------+ "' Of course the first parameter is actually mostly a variable (assuming v_str), and many times it is written that it may use the above statement to determine if the v_str is empty, This is entirely from the neglect of simple problems and the possibility and Ifnull silly division is unclear. At the same time it does not have to be the same as the if else in the programming language, and the above case will return ' 1 ' if it appears in the programming language;

### find_in_set 
 Prototype Find_in_set (str,strlist) in the database and data file interaction, often there is the conversion of encoding and real data, For example, now you need to import a batch of student data into a student table, in the Gender column, Excel shows "male, female," and the database is stored in ' "," Now you need to convert the actual data into encoded data to use this function. "' Mysql> Select Find_in_set (' Male ', ' male, female '); +---------------------------+|                         Find_in_set (' Male ', ' male, female ') |+---------------------------+| 1 |+---------------------------+ ' ' function returns, the first parameter appears in the second parameter in the subscript position (starting from 1). If there is no return 0, of course, the example is relatively simple, the actual use may be used in the string there are many kinds of time, such as a company's position. In addition, the strlist is separated by the ', ' number, so consider carefully the first parameter contains ', ', and check the function can be effective. 

### LOCATE LOCATE, as the name implies, is a positional function that returns the position of the first parameter in the second argument, which is relatively simple. But in the database, locate seems to assume more responsibility, more often, it is always in the ' temptation ' to determine whether certain conditions are established, according to the test results of the classification of SQL statements splicing. "' Mysql> Select if (Locate (', ', '" ' N ' y '), ' good ', ' hello '); +-----------------------------------------+| if (Locate (', ', ' |+ '), ' You're good ', ' hello ') +|----------------------------------------- How are you, |+?-----------------------------------------+ "In the above example, assume that ' one-of-a-kind ' is a student ID, which is a string, then according to Loc Ate can tell if it is a student or multiple students, and locate can optionally associate the table with a field that contains an associated word after the where condition sentence. Locate brings a lot of convenience to the concatenation of SQL statements. 

# # # Quote Last but definitely the most important one, the question about seat belts is always the most important, when there are user input data in the program, we must use this function to deal with, to prevent the basic SQL injection problem. "' Mysql> Select Concat (' WHERE id = ', ' 5411 or 1=1 '), concat (' WHERE id = ', quote (' 5411 or 1=1 ')); +---------------------- ---------------+--------------------------------------------+| Concat (' Where id = ', ' 5411 or 1=1 ') | Concat (' Where id = ', quote (' 5411 or 1=1 ')) |+-------------------------------------+--------------------------------- -----------+| WHERE id = 5411 or 1=1              | where id = ' 5411 or 1=1 '                   |+-------------------------------------+----------------------- ---------------------+ "The most basic SQL injection as a demonstration, if the user interface of a software is to enter the student's ID, so that the user input ' 5411 or 1=1 ', all the student's information will come out! At this time, if the quote function is processed, then there will be nothing to search, to ensure the security of the data, while the quote can also correctly handle the escape problem.

# # Summarize these powerful functions provided by the MySQL database, although not as powerful as programming language functions, but as long as you're thinking, you can basically meet almost anything you want to achieve. Furthermore, by combining these functions with basic business processing, you will find that each function is powerful.

The function of learning MySQL with little white