if函数
mysql> select if(1>2,'yes','no'); +--------------------+ | if(1>2,'yes','no') | +--------------------+ | no | +--------------------+ 1 row in set (0.00 sec)
mysql> select user,if(user='root','管理员','普通用户') from mysql.user; +------------------+--------------------------------------------+ | user | if(user='root','管理员','普通用户') | +------------------+--------------------------------------------+ | test | 普通用户 | | test1 | 普通用户 | | root | 管理员 | | mysql.infoschema | 普通用户 | | mysql.session | 普通用户 | | mysql.sys | 普通用户 | | root | 管理员 | | test | 普通用户 | +------------------+--------------------------------------------+ 8 rows in set (0.01 sec)
case函数
等值判断
mysql> select * from t2; +------+------+ | id | comm | +------+------+ | 1 | NULL | | 2 | 15 | | 3 | 18 | | 4 | 25 | | 5 | NULL | +------+------+ 5 rows in set (0.00 sec) mysql> select case comm when 15 then 'low' when 18 then 'middle' else 'high' end from t2 where comm is not null; +--------------------------------------------------------------------+ | case comm when 15 then 'low' when 18 then 'middle' else 'high' end | +--------------------------------------------------------------------+ | low | | middle | | high | +--------------------------------------------------------------------+ 3 rows in set (0.00 sec)
表达式
mysql> select * from t2; +------+------+ | id | comm | +------+------+ | 1 | NULL | | 2 | 15 | | 3 | 18 | | 4 | 25 | | 5 | NULL | +------+------+ 5 rows in set (0.00 sec) ## 控制不会参与比较，需要使用coalesce函数或ifnull函数进行转换才可以 mysql> select id,case when comm <10 then "low" when 10<comm<20 then "middle" else 'high' end from t2; +------+------------------------------------------------------------------------------+ | id | case when comm <10 then "low" when 10<comm<20 then "middle" else 'high' end | +------+------------------------------------------------------------------------------+ | 1 | high | | 2 | middle | | 3 | middle | | 4 | middle | | 5 | high | +------+------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)
mysql> update t2 set comm =5 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update t2 set comm =3 where id=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select id,case when comm <10 then "low" when 10<comm<20 then "middle" else 'high' end from t2; +------+------------------------------------------------------------------------------+ | id | case when comm <10 then "low" when 10<comm<20 then "middle" else 'high' end | +------+------------------------------------------------------------------------------+ | 1 | low | | 2 | middle | | 3 | middle | | 4 | middle | | 5 | low | +------+------------------------------------------------------------------------------+ 5 rows in set (0.00 sec)
ifnull函数 判断值是否为0
mysql> select ifnull(comm,0) from t2; +----------------+ | ifnull(comm,0) | +----------------+ | 0 | | 15 | | 18 | | 25 | | 0 | +----------------+ 5 rows in set (0.01 sec)