Analysis of common usage examples of MySQL process functions

This article uses examples to illustrate the common usage of MySQL process functions. Share with you for your reference, the details are as follows:

Process functions are a relatively common type of function in MySQL. Users can use this type of function to implement conditional selection in a SQL statement, which can improve efficiency.

The following lists the MySQL process functions related to conditions

The following example simulates the classification of employees. First, create an employee salary table:

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

Insert some test data

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

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

Next, this table will be used to introduce the application of each function.

IF(expr1, expr2, expr3) function: Here, employees with a monthly salary of more than 2,000 yuan are considered high-paid, represented by "high"; while employees with a monthly salary of less than 2,000 yuan are considered low-paid, represented by 'low'.

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 participate in numerical operations. The following statement replaces NULL values ​​with 0.

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: Here we can use the case when..then function to solve the problem of high salary and low salary in the above example.

mysql> select CASE WHEN salary<=2000 THEN 'low' else 'high' END from 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: Here you can also divide the employee's salary into multiple levels according to multiple situations, such as the following example, which is divided into three situations: high, medium and low. The same can be divided into more situations. I will not give examples here. Friends who are interested can test it themselves.

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)

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.