MySQL explains conditional judgment function

Function:

(1)IF(expr,v1,v2) function
(2)IFNULL(v1,v2) function
(3 )CASE function

(Related free learning recommendations: mysql video tutorial)

(1)if(expr,v1,v2) function

• In the if(expr,v1,v2) function, if the expression expr is true (expr<>0 and epr<>null), return v1, otherwise return v2.

[Example] Use the if() function to perform conditional judgment. The SQL statement is as follows:

mysql> select if(1>2,2,3),
    -> if(1<2,&#39;yes&#39;,&#39;no&#39;),
    -> if(strcmp('test','test1'),'no','yes');+-------------+--------------------+---------------------------------------+| if(1>2,2,3) | if(1<2,&#39;yes&#39;,&#39;no&#39;) | if(strcmp(&#39;test&#39;,&#39;test1&#39;),&#39;no&#39;,&#39;yes&#39;) |+-------------+--------------------+---------------------------------------+|           3 | yes                | no                                    |+-------------+--------------------+---------------------------------------+1 row in set (0.00 sec)

(2)ifnull(v1,v2) function

• In ifnull(v1,v2), if v1 is not null, the return value of ifnull() is v1, otherwise the return value is v2.
• If only one of v1 or v2 is explicitly null, the result type of the if() function is the result type of the non-null expression.

[Example] Use the ifnull() function to perform conditional judgment. The SQL statement is as follows:

mysql> select ifnull(1,2),ifnull(null,10),ifnull(1/0,'wrong');+-------------+-----------------+---------------------+| ifnull(1,2) | ifnull(null,10) | ifnull(1/0,'wrong') |+-------------+-----------------+---------------------+|           1 |              10 | wrong               |+-------------+-----------------+---------------------+1 row in set (0.00 sec)

(3)case function

1.case expr when v1 then r1 [ when v2 then r2] [else rn] end

• This function means that if the expr value is equal to a certain vi, then the result after the corresponding position then is returned. If it matches all values If not equal, return m after else.

[Example] Use case value when statement to perform branch operation, the SQL statement is as follows;

mysql> select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;+------------------------------------------------------------+| case 2 when 1 then 'one' when 2 then 'two' else 'more' end |+------------------------------------------------------------+| two                                                        |+------------------------------------------------------------+1 row in set (0.00 sec)

2.case when v1 then rv [when v2 then r2] else rn] end

• This function means that when a certain vi value is true, return the result after the corresponding position then. If all values ​​are not true, return rn after else.
• The default return value type of a case expression is a compatible collection type of any return value, depending on the context in which it is located.

[Example] Use the case when statement to perform branch operations. The SQL statement is as follows:

mysql> select case when 1<0 then 'true' else 'false' end;+--------------------------------------------+| case when 1<0 then 'true' else 'false' end |+--------------------------------------------+| false                                      |+--------------------------------------------+1 row in set (0.00 sec)

More related free learning recommendations: mysql tutorial( video)

The above is the detailed content of MySQL explains conditional judgment function. For more information, please follow other related articles on the PHP Chinese website!