Summary of use of if and case statements in MySQL

The if of MySQL can be used as an expression or in a stored procedure as a Process Control statement, as in the following is used as an expression:

An If expression

The

code is as follows:


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 is EXPR3. The return value of the IF () is a numeric value or a string value, depending on the context in which it is located.

The

code is as follows:


Select *,if (sva=1, "male", "female") as Ssva from Taname where Sva!= ""

An if of an expression can also be implemented with case:

The

code is as follows:


Select Case SVA when 1 THEN ' man ' ELSE ' woman ' end as Ssva from Taname where Sva!= '

In the return result of the first scenario, Value=compare-value. The return result of the second scheme is the true result of the first. If there is no matching result value, the result is returned, and if there is no else part, the return value is NULL.

For example:

The

code is as follows:


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, its 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.

The

code is as follows:


mysql&gt; SELECT ifnull (1,0);


-&gt; 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 for Ifnull (EXPR1,EXPR2) is one of the more "generic" in two expressions, in the order of string, real, or INTEGER.

IF ELSE is used as a Process Control statement

If the implementation of conditions to determine, to meet different conditions to perform different operations, which we learn to program all know if the role of the following, we look at the MySQL stored procedures in how to use the.

The

code is as follows:


IF search_condition THEN


statement_list


[ELSEIF search_condition THEN]


Statement_list ...


[ELSE


Statement_list]


End IF

Similar to the IF statement in PHP, when the condition search_condition is set up, the then Statement_list statement is executed, otherwise the condition in ElseIf is judged, and then the Statement_list statement is executed. Otherwise continue to judge the other branches. Executes the Else branch when none of the conditions for all branches are true. Search_condition is a conditional expression that can be composed of conditional operators such as "=, <, <=, >, >=,!=," and can use and, or, not to combine multiple expressions.

For example, establish a stored procedure that queries its results (grade) by student number (STUDENT_NO) and course number (COURSE_NO), returns grades and grades, scores greater than 90 for a, and less than 90 points greater than or equal to 80 points for Class B, Less than 80 points greater than or equal to 70 is the C class, followed by E-class. Then, the code to create the stored procedure is as follows:

The

code is as follows:


CREATE PROCEDURE Dbname.proc_getgrade


(stu_no varchar), 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&gt;=90 then


Select Stu_grade, ' A ';


ElseIf stu_grade&lt;90 and stu_grade&gt;=80 then


Select Stu_grade, ' B ';


ElseIf stu_grade&lt;80 and stu_grade&gt;=70 then


Select Stu_grade, ' C ';


ElseIf stu_grade70 and stu_grade&gt;=60 then


Select Stu_grade, ' D ';


Else


Select Stu_grade, ' E ';


End If;


End

Note: If as a statement, you need to add a semicolon after End if ";" To indicate the end of the statement, other statements such as Case, loop, and so on are the same.