MySql流程控制函数

流程控制函数

if语句

根据条件进行判断

语法

if(条件,为真时的结果,为假时的结果)

实例

mysql> select if(10>11,'ok','no');
+---------------------+
| if(10>11,'ok','no') |
+---------------------+
| no                  |
+---------------------+
1 row in set (0.00 sec)

mysql> select if(10>1,'ok','no');
+--------------------+
| if(10>1,'ok','no') |
+--------------------+
| ok                 |
+--------------------+
1 row in set (0.00 sec)

ifnull

判断是否为空,当不为空时,输出本身的值

语法

ifnull(需要判断的字段或者条件,为空时的输出)

实例

mysql> select ifnull(null,1);
+----------------+
| ifnull(null,1) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> select ifnull('null',1);
+------------------+
| ifnull('null',1) |
+------------------+
| null             |
+------------------+
1 row in set (0.00 sec)

mysql> select ifnull(sqrt(-9),1);
+--------------------+
| ifnull(sqrt(-9),1) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

case~when

搜索语句

CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;




或者
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END CASE;

mysql>SELECT 
    ->  CASE WEEKDAY(NOW())
    ->	WHEN 0 THEN '0'
    ->	WHEN 1 THEN '1' 
    ->  WHEN 2 THEN '2' 
    ->	WHEN 3 THEN '3'
    ->	WHEN 4 THEN '4' 
    ->	WHEN 5 THEN '5'
    -> ELSE 'day'
    ->END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());
    ->  END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());
+---------+---------------------+----------------+----------------+
| COLUMN1 | NOW()               | WEEKDAY(NOW()) | DAYNAME(NOW()) |
+---------+---------------------+----------------+----------------+
|         | 2022-06-03 08:34:37 |              4 | Friday         |
+---------+---------------------+----------------+----------------+
1 row in set (0.00 sec)