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. You may also be interested in:
|
<<: Vue uses el-tree lazy loading to implement the add, delete, modify and query functions
>>: A complete tutorial on installing Ubuntu 20.04 using VMware virtual machine
1Several common character sets In MySQL, the most...
Designers have their own font library, which allo...
This article describes how to install mysql5.6 us...
Table of contents Introduction to Docker Docker e...
Introduction to Git Git is an open source version...
Common scenarios for Nginx forwarding socket port...
How to modify the mysql table partitioning progra...
Preface Docker has been very popular in the past ...
Table of contents Demand background Thought Analy...
mysql-5.7.9 finally provides shutdown syntax: Pre...
Mixins provide a very flexible way to distribute ...
Server Status Analysis View Linux server CPU deta...
Tomcat accesses the project, usually ip + port + ...
MongoDB is cross-platform and can be installed on...
Enable the service when you need it, and disable ...