程序员需掌握的SQL条件判断函数（六）

文章目录

前言

MySQL 提供了IF、IFNULL、CASE等条件判断函数，这类函数在开发中也是常常能应用到的，下表是MySQL提供的条件判断函数。

条件函数

函数名	作用
IF(expr,v1,v2)	如果expr为真则返回v1，否则返回v2
IFNULL(v1,v2)	如果v1不为空则返回v1，否则返回v2
CASE WHEN v1 then r1 … ELSE [default] END	如果v1为真，则返回r1，否则返回默认default值
CASE expr WHEN v1 then r1 … ELSE [default] END	如果表达式expr为v1 则返回v2否则返回默认值

实操

下面将以一个示例演示以上函数的用法，首先创建一个tb_person的表如下所示：

CREATE TABLE `tb_person` (
  `id` int(11)  NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `age` int(10) NOT NULL,
  `gender` bit(1) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
)

插入一些测试数据查询后如下所示：

mysql> select * from tb_person;
+----+---------+-----+--------+------------+--------------+
| id | name    | age | gender | salary     | address      |
+----+---------+-----+--------+------------+--------------+
|  1 | 张三    |  23 | NULL   |   2500.00 | 中国上海     |
|  2 | 李四    |  24 |        |   4500.00 | 中国北京     |
|  3 | 王武    |  26 |        |   5000.00 | 中国南京     |
|  4 | 赵六    |  28 |       |   15000.00 | 中国南宁     |
|  7 | mike    |  25 |        |   20000.20 | America      |
|  8 | peter |  37 |       |  50000.23 | UN           |
+----+---------+-----+--------+------------+--------------+
6 rows in set (0.00 sec)

数据准备好以后我们再来介绍各个函数的使用

1. IF(expr,v1,v2)

有个需求：员工月薪在5000以上为高薪，月薪在3000以下为底薪。这里就可以使用IF函数如下：

mysql> select name,salary ,if(salary>5000,'高薪','不是高薪') as level  from tb_person;
+--------+------------+--------------+
| name   | salary     | level        |
+--------+------------+--------------+
| 张三   |    2500.00 | 不是高薪     |
| 李四   |    4500.00 | 不是高薪     |
| 王武   |    5000.00 | 不是高薪     |
| 赵六   |   15000.00 | 高薪         |
| mike   |   50000.20 | 高薪         |
| peter  | 1000000.23 | 高薪         |
+--------+------------+--------------+
6 rows in set (0.00 sec)

2. IFNULL(v1,v2)

如果员工薪资为null则显示为0，首先我们添加一条薪资为null的一条新纪录如下：

mysql> insert into tb_person(name,age,address) values('法外狂徒',9999,'unknow');
Query OK, 1 row affected (0.00 sec)

如果直接查询表则会查出来salary为null的那一条记录如下：

mysql> select * from tb_person;
+----+--------------+------+--------+------------+--------------+
| id | name         | age  | gender | salary     | address      |
+----+--------------+------+--------+------------+--------------+
|  1 | 张三         |   23 | NULL   |    2500.00 | 中国上海     |
|  2 | 李四         |   24 |        |    4500.00 | 中国北京     |
|  3 | 王武         |   26 |        |    5000.00 | 中国南京     |
|  4 | 赵六         |   28 |       |   15000.00 | 中国南宁     |
|  7 | mike         |   25 |        |   50000.20 | America      |
|  8 | peter        |   37 |       | 1000000.23 | UN           |
|  9 | 法外狂徒     | 9999 | NULL   |       NULL | unknow       |
+----+--------------+------+--------+------------+--------------+
7 rows in set (0.00 sec)

使用IFNULL如下所示：

mysql> select name,age,ifnull(salary,0) as salary from tb_person;
+--------------+------+------------+
| name         | age  | salary     |
+--------------+------+------------+
| 张三         |   23 |    2500.00 |
| 李四         |   24 |    4500.00 |
| 王武         |   26 |    5000.00 |
| 赵六         |   28 |   15000.00 |
| mike         |   25 |   50000.20 |
| peter        |   37 | 1000000.23 |
| 法外狂徒     | 9999 |       0.00 |
+--------------+------+------------+
7 rows in set (0.00 sec)
mysql>

3. CASE WHEN

此类函数包含有两种形式：

形式一

CASE 列名 WHEN v1 then r1 … ELSE [default] END
有下面一个需求：区分上面员工表高薪与底薪的问题如下：

mysql> select name ,salary, case salary when 2500 then '低' when 4500 then '低' else '高' end as level from tb_person;
+--------------+------------+-------+
| name         | salary     | level |
+--------------+------------+-------+
| 张三         |    2500.00 | 低    |
| 李四         |    4500.00 | 低    |
| 王武         |    5000.00 | 高    |
| 赵六         |   15000.00 | 高    |
| mike         |   50000.20 | 高    |
| peter        | 1000000.23 | 高    |
| 法外狂徒     |       NULL | 高    |
+--------------+------------+-------+
7 rows in set (0.01 sec)

形式二

CASE WHEN 表达式1 then r1 WHEN 表达式2 then r2 else 默认值1 END

我们可以使用这个表达式二完成以下需求：薪资小于等于5000属于低，薪资在5000至15000属于较高，大于15000小于60000 属于高其他为特高。

mysql> select name ,salary ,case when salary <=5000 then '低' when salary >5000 and salary <=15000 then '较高' when salary>15000 and salary<=60000 then '高' else '太高了' end as level from tb_person where salary ;
+--------+------------+-----------+
| name   | salary     | level     |
+--------+------------+-----------+
| 张三   |    2500.00 | 低        |
| 李四   |    4500.00 | 低        |
| 王武   |    5000.00 | 低        |
| 赵六   |   15000.00 | 较高      |
| mike   |   50000.20 | 高   |
| peter  | 1000000.23 | 太高了    |
+--------+------------+-----------+
6 rows in set (0.00 sec)