MySQL's if,case use notes

In an SQL expression:

An If expression:

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.

Select *,if (sva=1, "male", "female") as Ssva from Taname where SVA! = ""

　　　When SVA returns to the male at 1 o'clock, the woman is returned.

Ifnull expression:

Ifnull (EXPR1,EXPR2)

　When Expr1<>null, return expr1, otherwise return EXPR2, can be nested:

Ifnull (Ifnull (Expr1,ifnull (...)), Ifnull (...)

　　Case-When expression:

Two types of formats:

The simple case function compares an expression to a set of simple expressions to determine the result.

The case search function calculates a set of Boolean expressions to determine the result.

The optional ELSE parameter is supported in both formats.

A complete simple case

Case input_expression while    when_expression then result_expression        [... n]    [         ELSE Else_result_ Expression    END

　　

When input_expression = when_expression, returns result_expression

Such as

Select (Case Aix if '     s400 ' then ' hp ' when    ' A10 ' and ' dell '    Else ' any '    end) Machinenamefrom tablename  

　input_expression is generally a table field, or Other valid SQL expressions (this is not quite clear).

　The when_expression is the same as the input_expression data type, or can be implicitly converted. In other words, this is not a Boolean expression (string comparison, expression evaluation, etc.), unless input_expression is of type bool.

Complete Search case

case where    boolean_expression then result_expression        [... n]    [         ELSE else_result_expression    END

　When Boolean_expression=true, return to result_expression.

Such as

Select when Aix = ' s400 ' and ' hp ' when    aix = ' A10 ' and ' dell '    Else ' any ' end '    machinenamefrom ta Blename  

　　It can be understood that when a case is omitted as a simple case, true. The implementation process or difference between the two has yet to be studied.

　　

　　

MySQL's if,case use notes