Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the use of if sentences in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Mysql's if can be used either as an expression or as a flow control statement in a stored procedure.

IF expression

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 either a numeric value or a string value, depending on its context.

Select *, if (sva=1, "male", "female") as ssva from taname where sva! = "" if as an expression can also be implemented in CASE when: select CASE sva WHEN 1 THEN 'male' 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 scheme is the real result of the first case. If there is no matching result value, the result is returned if the result is ELSE, and if there is no ELSE section, the return value is NULL.

For example:

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

One will be output

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 a number or a string, depending on the context in which it is used.

Mysql > SELECT IFNULL (1Jue 0);-> 1mysql > SELECT IFNULL (NULL,10);-> 10mysql > SELECT IFNULL (1ppt 0LJ 10);-> 10mysql > SELECT IFNULL (1pm 0rem');-> 'yes''

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

IF ELSE is used as a process control statement

If implements condition judgment and performs different operations to meet different conditions. As long as we learn to program, we all know the role of if. Let's take a look at 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

Similar to the IF statement in PHP, when the conditional search_condition in IF is established, the statement_list statement after THEN is executed, otherwise the condition in ELSEIF is judged, and the subsequent statement_list statement is executed if it is established, otherwise other branches are judged. When the conditions for all branches are not valid, the ELSE branch is executed. Search_condition is a conditional expression that can be composed of conditional operators such as "=,!", and can be combined using AND, OR, and NOT.

For example, create a stored procedure that queries its scores (grade) through the student number (student_no) and course number (course_no), and returns the grade and grade of the grade. The score greater than 90 is level A, the score less than 90 is greater than or equal to 80 is level B, and the score less than 80 or greater than 70 is level C, followed by level 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=80 then select stu_grade,'B'; elseif 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: as a statement, IF needs to be followed by a semicolon ";" to indicate the end of the statement, and other statements such as CASE, LOOP, and so on are the same.

On the use of if sentences in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.