mysql运维部落_MySQL流程函数

MySQL流程函数

IF(value,x y)

如果value是真，返回x,否则返回y

MariaDB [learn]> INSERT INTO salary(sal) VALUES (1000),(2000),(3000),(4000),(5000),(6000),(NULL);

Query OK, 7 rows affected (0.06 sec)

Records: 7 Duplicates: 0 Warnings: 0

MariaDB [learn]> SELECT * FROM salary;

+----+------+

| id | sal |

+----+------+

| 1 | 1000 |

| 2 | 2000 |

| 3 | 3000 |

| 4 | 4000 |

| 5 | 5000 |

| 6 | 6000 |

| 7 | NULL |

+----+------+

MariaDB [learn]> SELECT id,if(sal>2000,'high','low') FROM salary;

+----+---------------------------+

| id | if(sal>2000,'high','low') |

+----+---------------------------+

| 1 | low |

| 2 | low |

| 3 | high |

| 4 | high |

| 5 | high |

| 6 | high |

| 7 | low |

+----+---------------------------+

7 rows in set (0.01 sec)

IFNULL(value1,value2)

如果value1不为空，返回value1,否则返回value2

IFNULL(value1,value2)函数：这个函数一般用来替换NULL值，我们知道NULL值是不能参与数值运算的，下面这个语句就是把NULL值用0来替换。

MariaDB [learn]> SELECT id,ifnull(sal,0) FROM salary;

+----+---------------+

| id | ifnull(sal,0) |

+----+---------------+

| 1 | 1000 |

| 2 | 2000 |

| 3 | 3000 |

| 4 | 4000 |

| 5 | 5000 |

| 6 | 6000 |

| 7 | 0 |

+----+---------------+

7 rows in set (0.01 sec)

CASE WHEN [value] THEN [result]…ELSE [default] END

如果value为真 ，返回result，否则返回default

CASE WHEN [value] THEN [result]…ELSE [default] END函数：这里也可以用case when…then 实现上面例子中的高薪低能的问题。

MariaDB [learn]> SELECT id,case when sal<=2000 then 'low' else 'high' end FROM salary;

+----+------------------------------------------------+

| id | case when sal<=2000 then 'low' else 'high' end |

+----+------------------------------------------------+

| 1 | low |

| 2 | low |

| 3 | high |

| 4 | high |

| 5 | high |

| 6 | high |

| 7 | high |

+----+------------------------------------------------+

7 rows in set (0.01 sec)

CASE [expr] WHEN [value] THEN [result]…ELSE [default] END

如果expr等于value，返回result,否则返回default。

CASE [expr] WHEN [value] THEN [result]…ELSE [default] END函数：这里可以分多种情况把职员的薪水分多个档次，比如下面的例子成分高、中、低3种情况。同样还可以分成更多情况，这里就不再举例了。

MariaDB [learn]> SELECT id,CASE sal WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'low' END AS LEVEL FROM salary;

+----+-------+

| id | LEVEL |

+----+-------+

| 1 | low |

| 2 | mid |

| 3 | low |

| 4 | low |

| 5 | low |

| 6 | low |

| 7 | low |

+----+-------+

7 rows in set (0.01 sec)

原创文章，作者：dance_man，如若转载，请注明出处：http://www.178linux.com/74581