How to use MySQL flow control function

In MySQL, a process control function refers to a statement that can control the execution flow of a stored procedure or function. The following are several commonly used process control functions:

1.IF function

realizes the effect of IF……ELSE…….

# 如果expr1为true，则返回expr2，否则返回expr3
IF(expr1,expr2,expr3)

It can be seen that the IF function is very similar to the ternary operator, such as:

		// 比较最大数
        int a=10;
        int b=5;
        // 比较
        int bigNum=a>b?a:b;

That is, among the three parameters of the IF function, expr1 is a conditional expression, and the final result is true or false, if the condition is true (true), expr2 is returned, if the condition is not true (false), expr3 is returned. Example:

SELECT IF(10>5,10,5) AS bigNum;

2.IFNULL function

also achieves the effect of IF......ELSE...and is equivalent to a variant of the IF function.

# 如果expr1不为NULL，则返回expr1，否则返回expr2
IFNULL(expr1,expr2)

The original conditional expression is transformed to determine whether expr1 is null. If it is not null, it is itself (expr1). If it is empty, it is expr2.

SELECT IFNULL(null,'不空') AS notNull

Equivalent to the IF function

SELECT IF(null IS NULL,null,'不空') AS notNull

Determine whether expr1 is empty, if it is empty (true), it will display expr2, if it is not empty (false), it will display expr3

3.CASE function

The case function has two different effects

switch case

It is equivalent to the effect of switch case in java. That is, the value of the variable expression in switch is compared with the constant behind the case.

		int week=3;
        switch (week){
            case 1:
                System.out.println("星期一");
                break;
            case 2:
                System.out.println("星期二");
                break;
            case 3:
                System.out.println("星期三");
                break;
            case 4:
                System.out.println("星期四");
                break;
            case 5:
                System.out.println("星期五");
                break;
            case 6:
                System.out.println("星期六");
                break;
            case 7:
                System.out.println("星期日");
                break;
            default:
                System.out.println("非法数据");
                break;
        }

Represented in sql:

# NOW()函数用于获取当前日期和时间，
# WEEKDAY(date)函数,表示返回date对应的工作日索引，
# 因为索引从0开始，所以加1；也可以不加1，将when的常量改为工作日索引也可
SELECT CASE WEEKDAY(NOW())+1
	WHEN 1 THEN '星期一'
	WHEN 2 THEN '星期二'
    WHEN 3 THEN '星期三'
    WHEN 4 THEN '星期四'
    WHEN 5 THEN '星期五'
    WHEN 6 THEN '星期六'
    WHEN 7 THEN '星期日'	
	ELSE '非法数据'
END AS `week`;

Function structure

case Variable (field) or expression to be judged
when constant 1 then value 1 (or statement 1) to be displayed;
when constant 2 then value 2 (or statement 2) to be displayed;
...
else to Displayed value n or statement n;
end

Note:

• is different from java, write case directly instead of switch, and there are no curly brackets

• Add the constant value directly after when, no need to write a colon, use then

• If the value after then is the displayed value, no need to add Semicolon; if a statement is added after then, a semicolon needs to be added.

• when……then…… There can be multiple statements.

• The default is else.

• ends with end

4. Multiple IF

is similar to the multiple if judgments in Java.

		int grade=87;
        if (grade>=90){
            System.out.println("优秀");
        }else if (grade>=80){
            System.out.println("良好");
        }else if (grade>=70){
            System.out.println("一般");
        }else if (grade>=60){
            System.out.println("及格");
        }else {
            System.out.println("不及格");
        }

Expressed in sql:

SELECT id,`name`,chinese,
	CASE
		WHEN chinese>=90 THEN '优秀'
		WHEN chinese>=80 THEN '良好'
		WHEN chinese>=70 THEN '一般'
		WHEN chinese>=60 THEN '及格'
		ELSE '不及格'
	END AS `rank`
FROM student

Function structure:

case
when Condition 1 then To be displayed Value 1 or statement 1;
when condition 2 then value 2 or statement 2 to be displayed;
......
else value n or statement n to be displayed
end

Note:

• There is no condition after case

• When there is a condition after it, the result is true or false; the condition is met Execute then and display the following value or statement

• If the same then is followed by the displayed value, there is no need to add a semicolon; if the statement is added after then, a semicolon is required. .

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