Summary of the use of the if and case statements in MySQL, And the mysqlifcase statement

Summary of the use of the if and case statements in MySQL, And the mysqlifcase statement

Mysql if can be used as an expression or a flow control statement in a stored procedure. The following statements are used as expressions:

IF expression
Copy codeThe Code is as follows:
IF (expr1, expr2, expr3)

IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), the return value of IF () is expr2; otherwise, the return value is expr3. The return value of IF () is a numeric or string value, depending on the context.
Copy codeThe Code is as follows:
Select *, if (sva = 1, "male", "female") as ssva from taname where sva! = ""

If, as an expression, can also be implemented using CASE when:
Copy codeThe Code is as follows:
Select CASE sva WHEN 1 THEN 'male' ELSE 'female' END as ssva from taname where sva! =''

In the returned results of the first scheme, value = compare-value. The returned results of the second solution are the real results of the first case. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, the return value is NULL.

For example:
Copy codeThe Code is as follows:
Select case 1 WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'more' END
As testCol

Output one

IFNULL (expr1, expr2)

If expr1 is not NULL, the returned value of IFNULL () is expr1; otherwise, the returned value is expr2. The returned value of IFNULL () is a number or string, depending on the context in which it is used.
Copy codeThe Code is as follows:
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 one of the two expressions that is more "common" in the order of STRING, REAL, or INTEGER.

If else is used as a process control statement

If conditions are determined to meet different conditions and perform different operations, we only need to learn programming and know the role of if. Let's take a look at how to use if in the mysql stored procedure.
Copy codeThe 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 in IF is set, execute the statement_list statement after THEN. Otherwise, judge the condition in ELSEIF. IF so, execute the subsequent statement_list statement, otherwise, continue to judge other branches. If the condition of all branches is invalid, execute the ELSE branch. Search_condition is a condition expression, which can be "=", "<=", ">", "> =", and ,! = "AND other conditional operators, AND can use AND, OR, NOT to combine multiple expressions.

For example, if you create a stored procedure, the student ID (student_no) and course number (course_no) are used to query the score (grade), and the score and grade are returned, if the score is greater than 90, the score is a level. If the score is less than 90, the score is B. If the score is less than 80, the score is C, and the score is E. The code for creating a stored procedure is as follows:
Copy codeThe Code 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, as a statement, must be followed by a semicolon (;) to END the statement. Other statements such as CASE and LOOP are the same.