When and if else in MySQL

The great god says: In sql, you can use if else without case

below to see, specifically why, did not figure out if there is a great God to know to provide the following information:

MySQL can be used as an expression, or as a Process control statement in a stored procedure, as an expression:

An If expression

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

Copy CodeThe code is as follows:
Select *,if (sva=1, "male", "female") as Ssva from Taname where SVA! = ""

The IF as an expression can also be implemented with case:

Copy CodeThe code is as follows:
Select Case SVA If 1 Then ' Men ' ELSE ' female ' END as Ssva from taname where SVA! = '

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

For example:

Copy CodeThe code is as follows:
SELECT Case 1 While 1 then ' one '
When 2 Then ' both '
ELSE ' More ' END
As Testcol

Will output one

Ifnull (EXPR1,EXPR2)

If EXPR1 is not NULL, 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.

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

IF ELSE is used as a Process Control statement

If the implementation of the conditions to judge, to meet different conditions to perform different operations, this we just learn programming all know if the role of if, let's look at the MySQL stored procedure if is how to use it.

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 in the If search_condition, execute then after the Statement_list statement, otherwise determine the conditions in the ElseIf, the establishment then executes the Statement_list statement thereafter, Otherwise continue to judge other branches. Executes the Else branch when none of the conditions of the branch are true. Search_condition is a conditional expression that can be composed of conditional operators such as =, <, <=, >, >=,! =, and multiple expressions can be combined using and, or, not.

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

Copy CodeThe 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>=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, you need to add a semicolon after End if ";" The other statements, such as case, loop, are the same as the statement ends.

SQL if Else statement

IF ELSE Statement
IF ELSE is one of the most basic programming statement structures in which almost every programming language supports this structure and
It is useful to check the data returned from the database for Transact-SQL using the IF ELSE
The examples are as follows
Grammar
if (condition)
Begin
(Statement block)
End
else if (condition)
Begin
Statement block)
End
Else
Begin
(Statement block)
End
Note that when the specified condition is true, the BEGIN END statement block is executed at the same time
You should also be aware that it is a good programming habit to indent each statement into a certain amount of space, which can be greatly raised
The readability of your program and errors caused by poor legibility

When and if else in MySQL