常用函数：流程函数

 

---

流程函数

一、MySQL流程函数

 

语法：

case when [value] then [result] ....else[default] end

1.1 if(expr,v1,v2)

mysql> select if(1<2,'yes','no'),if(1>2,'yes','no'),if(strcmp('tansk','tanshouke'),'yes','no');
+--------------------+--------------------+--------------------------------------------+
| if(1<2,'yes','no') | if(1>2,'yes','no') | if(strcmp('tansk','tanshouke'),'yes','no') |
+--------------------+--------------------+--------------------------------------------+
| yes                | no                 | yes                                        |
+--------------------+--------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('tansk','tanshouke');
+-----------------------------+
| strcmp('tansk','tanshouke') |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> 

1.2 case

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

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

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

mysql> 

1.3 case when

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

mysql> 

1.4 ifnnull ()

mysql> select ifnull(null,2),ifnull(1,2),ifnull(0/1,2),ifnull(1/0,2);
+----------------+-------------+---------------+---------------+
| ifnull(null,2) | ifnull(1,2) | ifnull(0/1,2) | ifnull(1/0,2) |
+----------------+-------------+---------------+---------------+
|              2 |           1 |        0.0000 |        2.0000 |
+----------------+-------------+---------------+---------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

 

---