How to use if statement in MySQL

Mysql’s if can be used as an expression or as a process control statement in a stored procedure. This article mainly talks about the regular use of if statements in mysql. It has certain learning value. Friends who are interested You can find out.

IF expression

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 a8093152e673feb7aba1828c43532094 0 and expr1 6d267e5fab17ea8bc578f9e7e5e1570b NULL), the return value of IF() is expr2; otherwise the return value 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' ENDas testCol

will output one

IFNULL(expr1,expr2)

If expr1 is not NULL, 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);
        -> 1mysql> SELECT IFNULL(NULL,10);
        -> 10mysql> SELECT IFNULL(1/0,10);
        -> 10mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

IFNULL(expr1,expr2) The default result value is the more "general" 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. Let’s take a look. Let's see how if is used in mysql stored procedures.

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 "=, 91eef5681cad6f0cc96f9adeb2931b45, >=, !=", 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 Grade B is a score of less than 90 points but not less than 80 points, Grade C is a score of less than 80 points but not less than 70 points, 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,'B';  elseif stu_grade<80 and stu_grade>=70 then 
    select stu_grade,'C';  elseif stu_grade70 and stu_grade>=60 then 
    select stu_grade,'D';  else 
    select stu_grade,'E';  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. .

Related tutorials: mysql video tutorial

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