SQL statement flow Functions

SQL statement flow Functions

I used the process function today and suddenly felt unfamiliar with the syntax. I wrote this article to avoid forgetting it later !!!

 

 

Flow functions are relatively common functions in MySQL. You can use these functions to select conditions in an SQL statement to improve efficiency.

The following lists MySQL process functions related to conditions.

Function	Function
IF (expr1, expr2, expr3)	If expr1 is true, expr2 is returned; otherwise, expr3 is returned.
IFNULL (expr1, expr2)	If expr1 is not NULL, expr1 is returned; otherwise, expr2 is returned.
Case when [value1] THEN [result1]… ELSE [default] END	If the value is true, result1 is returned; otherwise, default is returned.
CASE [expr] WHEN [value1] THEN [result1]… ELSE [default] END	If expr is equal to value1, result1 is returned; otherwise, default is returned.

In the following example, employees are classified. First, a employee salary table is created:

1	create table salary(userid int, salary decimal(9,2));

Insert some test data

1	insert into salary values (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);

The data is as follows:

123456789101112

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 (0.00 sec)

Next, we will introduce the application of each function through this table. IF (expr1, expr2, expr3) function: employees with a monthly salary of more than 2000 Yuan are considered to be highly paid, which is indicated by "high"; employees with a salary of less than 2000 belong to low salaries, it is expressed by 'low.

123456789101112

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

IFNULL (expr1, expr2) function: This function is generally used to replace NULL values. We know that NULL values cannot be involved in numerical calculation. The following statement replaces NULL values with 0.

123456789101112

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

Case when [value1] THEN [result1]… ELSE [default] END function: the case when... then function can be used to implement the high salary and low salary in the above example.

123456789101112

mysql> select CASE WHEN salary<=2000 THEN 'low' else 'high' ENDfrom salary;+---------------------------------------------------+| CASE WHEN salary<=2000 THEN 'low' else 'high' END |+---------------------------------------------------+| low                                               || low                                               || high                                              || high                                              || high                                              || high                                              |+---------------------------------------------------+6 rows in set (0.00 sec)

CASE [expr] WHEN [value1] THEN [result1]… ELSE [default] END function: the employee's salary can be divided into multiple grades in multiple situations. For example, the example below is divided into three levels: High, Medium, and low. It can also be divided into more cases. Here we will not give an example. If you are interested, you can test it on your own.

123456789101112

mysql> select CASE salary WHEN 1000 THEN 'low' when 2000 THEN 'mid' ELSE 'high' END from salary;+-----------------------------------------------------------------------+| CASE salary WHEN 1000 THEN 'low' when 2000 THEN 'mid' ELSE 'high' END |+-----------------------------------------------------------------------+| low                                                                   || mid                                                                   || high                                                                  || high                                                                  || high                                                                  || high                                                                  |+-----------------------------------------------------------------------+6 rows in set (0.00 sec)