The use of if and case statements in MySQL

This article mainly introduces a summary of the use of if and case statements in MySQL. This article explains the use of IF statements as expressions and flow control statements. Friends in need can refer to it.

Mysql's if can be used as an expression or as a process control statement in a stored procedure. The following is used as an expression:

IF expression

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), then the return value of IF() is expr2; otherwise the return value Then it is expr3. The return value of IF() is either a numeric value or a string value, depending on the context.

select *,if(sva=1,"男","女") as ssva from taname where sva != ""

if as an expression can also be implemented using CASE when:

select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''

In the return result of the first solution, value=compare-value. The return result of the second scenario is the true result of the first scenario. If there is no matching result value, the return result is the result after ELSE. If there is no ELSE part, the return value is NULL.

For example:

SELECT CASE 1 WHEN 1 THEN 'one'
  WHEN 2 THEN 'two' 
   ELSE 'more' END
as testCol

will output one

IFNULL(expr1,expr2)

If expr1 is not NULL , then the return value of IFNULL() is expr1; otherwise, the return value is expr2. The return value of IFNULL() is either a number or a string, depending on the context in which it is used.

mysql> SELECT IFNULL(1,0);        -> 1
mysql> SELECT IFNULL(NULL,10);        -> 10
mysql> SELECT IFNULL(1/0,10);        -> 10
mysql> SELECT IFNULL(1/0,'yes');        -> 'yes'

The default result value of IFNULL(expr1,expr2) is the more "general" one of the two expressions, in the order STRING, REAL or INTEGER.

IF ELSE Use

if as a process control statement to implement conditional judgment and perform different operations when different conditions are met. As long as we learn programming, we all know the role of if Okay, let's take a look at how to use if in the mysql stored procedure.

IF search_condition THEN 
    statement_list  
[ELSEIF search_condition THEN]  
    statement_list ...  
[ELSE 
    statement_list]  
END IF

is similar to the IF statement in PHP. When the condition search_condition in IF is established, the statement_list statement after THEN is executed. Otherwise, the condition in ELSEIF is judged. If it is established, the subsequent statement_list statement is executed. Otherwise, other statements are continued. branch. When the conditions of all branches are not met, the ELSE branch is executed. search_condition is a conditional expression, which can be composed of conditional operators such as "=, <, <=, >, >=, !=", and multiple expressions can be combined using AND, OR, and NOT .

For example, create a stored procedure that queries the student's grade (grade) through the student number (student_no) and course number (course_no), and returns the grade and grade. Those with grades greater than 90 points are A Those with a score less than 90 and a score of 80 or more are grade B, those with a score less than 80 and a score of 70 or more are grade C, and so on to grade E. Then, the code to create the stored procedure is as follows:

create procedure dbname.proc_getGrade  
(stu_no varchar(20),cour_no varchar(10))  
BEGIN 
declare stu_grade float;  
select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no;  
if stu_grade>=90 then 
    select stu_grade,'A';  
elseif stu_grade<90 and stu_grade>=80 then 
    select stu_grade,&#39;B&#39;;  
elseif stu_grade<80 and stu_grade>=70 then 
    select stu_grade,&#39;C&#39;;  
elseif stu_grade70 and stu_grade>=60 then 
    select stu_grade,&#39;D&#39;;  
else 
    select stu_grade,&#39;E&#39;;  
end if;  
END

Note: IF is a statement, and a semicolon ";" needs to be added after END IF to indicate the end of the statement. The same is true for other statements such as CASE, LOOP, etc. .

The above is the entire content of this article. I hope it will be helpful to everyone's study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

A question about Laravel Route redirection

The above is the detailed content of The use of if and case statements in MySQL. For more information, please follow other related articles on the PHP Chinese website!