Home > Database > Mysql Tutorial > body text

MySQL explains conditional judgment function

coldplay.xixi
Release: 2021-03-03 09:39:49
forward
2548 people have browsed it

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)
Copy after login

(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)
Copy after login

(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)
Copy after login

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)
Copy after login

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!

Related labels:
source:csdn.net
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template