Analysis of common usage examples of MySQL process functions

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

function Function
IF(expr1,expr2,expr3) If expr1 is true, returns expr2, otherwise returns expr3
IFNULL(expr1,expr2) If expr1 is not NULL, returns expr1, otherwise returns expr2
CASE WHEN [value1] THEN [result1]… ELSE [default] END If value is true, return result1, otherwise return default
CASE [expr] WHEN [value1] THEN [result1]… ELSE [default] END If expr is equal to value1, return result1, otherwise return default

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:
  • How to use MySQL common functions to process JSON
  • Common usage tutorial of mysql_fetch_* functions in PHP operation of MySQL
  • Detailed explanation of Mysql function call optimization
  • Example tutorial on using the sum function in MySQL
  • Specific use of MySQL segmentation function substring()
  • Use of MySQL DATE_FORMAT function
  • Common functions of MySQL basics

<<:  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

Recommend

The whole process of configuring reverse proxy locally through nginx

Preface Nginx is a lightweight HTTP server that u...

How to query the intersection of time periods in Mysql

Mysql query time period intersection Usage scenar...

Detailed tutorial of pycharm and ssh remote access server docker

Background: Some experiments need to be completed...

MySQL 5.7 installation and configuration tutorial under CentOS7 (YUM)

Installation environment: CentOS7 64-bit, MySQL5....

Why MySQL should avoid large transactions and how to solve them

What is a big deal? Transactions that run for a l...

How to use SessionStorage and LocalStorage in Javascript

Table of contents Preface Introduction to Session...

Simple implementation of html hiding scroll bar

1. HTML tags with attributes XML/HTML CodeCopy co...

Basic implementation method of cross-component binding using v-model in Vue

Hello everyone, today we will talk about how to u...

MySQL high availability solution MMM (MySQL multi-master replication manager)

1. Introduction to MMM: MMM stands for Multi-Mast...

How to process blob data in MySQL

The specific code is as follows: package epoint.m...

Mysql uses stored procedures to quickly add millions of data sample code

Preface In order to reflect the difference betwee...

Three ways to create a gray effect on website images

I’ve always preferred grayscale images because I t...

How to create a test database with tens of millions of test data in MySQL

Sometimes you need to create some test data, base...

The difference between VOLUME and docker -v in Dockerfile

There are obvious differences between volume moun...