Shulou(Shulou.com)05/31 Report--

This article will explain in detail what process functions there are in MySQL, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Flow function is a kind of function which is relatively commonly used in MySQL. Users can use this kind of function to implement conditional selection in a SQL statement, which can improve efficiency.

The following is a list of MySQL process functions related to conditions

Function function IF (expr1,expr2,expr3) returns expr2 if expr1 is true, otherwise returns expr3IFNULL (expr1,expr2) if expr1 is not NULL, returns expr1, otherwise returns expr2CASE WHEN [value1] then [result 1]. ELSE [default] END returns result1 if value is true, otherwise returns defaultCASE [expr] WHEN [value1] then [result 1]... ELSE [default] END returns result1 if expr equals value1, otherwise returns default

The following example simulates the classification of employees, starting with the creation of a staff payroll:

Create table salary (userid int, salary decimal (9pm 2))

Insert some test data

Insert into salary values (1d1000), (2d2000), (3jc3000), (4pyrr4000), (5pyrr5000), (1dint null)

The data are as follows

Mysql > select * from salary;+-+-+ | userid | salary | +-+-+ | 1 | 1000.00 | 2 | 2000.00 | 3 | 3000.00 | 4 | 4000.00 | 5 | 5000.00 | | 1 | NULL | +-+-+ 6 rows in set (sec)

Next, through this table to introduce the application of each function.

IF (expr1,expr2,expr3) function: here, employees earning more than 2000 yuan a month are considered as high salaries, expressed as "high'", while employees below 2000 are paid with low salaries, expressed as' low'.

Mysql > select if (salary > 2000, 'high',' low') from salary +-- + | if (salary > 2000, 'high' 'low') | +-+ | low | | low | | high | | low | +-+ 6 rows in set (0.00 sec)

IFNULL (expr1,expr2) function: this function is generally used to replace the null value, we know that the null value cannot participate in the numerical operation, the following statement is to replace the NULL value with 0.

Mysql > select ifnull (salary,0) from salary;+-+ | ifnull (salary,0) | +-+ | 1000.00 | | 2000.00 | 3000.00 | 4000.00 | 5000.00 | 5000.00 | +-+ 6 rows in set (sec)

CASE WHEN [value1] then [result 1]... ELSE [default] END function: here you can use the case when..then function to implement the problem of high salary and low salary in the above example.

Mysql > select CASE WHEN salary

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.