How to use the IF function to perform conditional logic judgment in MySQL

How to use the IF function to perform conditional logic judgment in MySQL

In MySQL, the IF function is a function used to perform conditional judgment. Its basic syntax is as follows:

IF(condition, value_if_true, value_if_false)

Among them, condition represents a conditional expression, value_if_true represents the return value if the condition is true, and value_if_false represents if the condition Return value if false.

Below we use some code examples to introduce in detail how to use the IF function.

1. Simple conditional judgment

Suppose there is a table student, which contains two fields: student's name and grades. We can use the IF function to determine whether a student's score is qualified based on the score. The sample code is as follows:

SELECT name, IF(score >= 60, '合格', '不合格') AS result FROM student;

In the above code, we use the IF function to make conditional judgments. If the student's score is greater than or equal to 60, 'passed' is returned, otherwise 'failed' is returned. The result will be output in the result field.

2. Multi-condition judgment

Sometimes we need to execute different logic based on different conditions. The following sample code shows how to use the IF function to perform multi-condition judgment.

Suppose we have a product table product, which contains three fields: product name, price and inventory. We hope to determine the inventory status of the product and output different prompt information according to different situations. The sample code is as follows:

SELECT name, IF(stock > 0, '有货', IF(stock = 0, '缺货', '未知')) AS status FROM product;

In the above code, we use the nested IF function to perform multi-condition judgment. First determine whether the inventory is greater than 0, and if so, return 'in stock'; if not, then further determine whether the inventory is equal to 0, and if so, return 'out of stock'; otherwise, return 'unknown'. All judgment results will be output in the form of status field.

3. Using the IF function in the UPDATE statement

In addition to using the IF function in the SELECT statement, we can also apply it to the UPDATE statement. The sample code is as follows:

Suppose we have a user table user, which contains two fields: the user's name and points. We hope to update the user's level based on the user's points. The sample code is as follows:

UPDATE user SET level = IF(points >= 100, '高级会员', IF(points >= 50, '普通会员', '普通用户'));

In the above code, we use the nested IF function to perform multi-condition judgment. First, determine whether the points are greater than or equal to 100. If so, update the user's level to 'Premium Member'; if not, further determine whether the points are greater than or equal to 50. If so, update the user's level to 'Ordinary Member'; otherwise update the user's level. The level is 'normal user'.

Summary:

Through the above code examples, we understand how to use the IF function to perform conditional logic judgments in MySQL. The IF function can be flexibly used in SELECT statements and UPDATE statements to help us perform different logical operations based on different conditions.

The above is the detailed content of How to use the IF function to perform conditional logic judgment in MySQL. For more information, please follow other related articles on the PHP Chinese website!