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
Preface Nginx is a lightweight HTTP server that u...
Mysql query time period intersection Usage scenar...
Background: Some experiments need to be completed...
Installation environment: CentOS7 64-bit, MySQL5....
What is a big deal? Transactions that run for a l...
The following is some basic sql knowledge I have ...
Table of contents Preface Introduction to Session...
1. HTML tags with attributes XML/HTML CodeCopy co...
Hello everyone, today we will talk about how to u...
1. Introduction to MMM: MMM stands for Multi-Mast...
The specific code is as follows: package epoint.m...
Preface In order to reflect the difference betwee...
I’ve always preferred grayscale images because I t...
Sometimes you need to create some test data, base...
There are obvious differences between volume moun...