mysql 数据库查询语句中关于case when then end 和 if/ifnull/nullif

• case when then end 的使用

select case COLUMN
    when CONDITION1 then VALUE1
    when CONDITION2 then VALUE2
    else VALUE3
end
from table
// for example
select (case status
    when 'paying' then '支付中'
    when 'success' then '支付成功'
    when 'close' then '交易关闭'
    else '订单状态异常'
    end) AS orderStatus
from order

或者：

select case
    when COLUMN CONDITION1 then VALUE1
    when COLUMN CONDITION2 then VALUE2
    else VALUE3
    end
from table
// for example
select (case
    when status = 'paying' then '支付中'
    when status = 'success' then '付款成功'
    when status = 'close' then '交易关闭'
    else '订单状态异常'
    end) AS orderStatus
from order
-------
select (case
    when score >= 85 then '优秀'
    when score >= 70 and score < 85 then '良好'
    when score >= 60 and score < 70 then '及格'
    else '不及格'
    end) AS scoreLevel
from performance

• if 的使用

select if(condition, value1, value2) from table
// 如果condition成立，那么值为value1，否则为value2，for example
select if(sex = 1, 'male', 'female') AS sex from user

• ifnull 的使用

select ifnull(column, value) from table
// 如果column为null，那么值为value， for example
select ifnull(email, '不详') from user

• nullif 的使用

select nullif(column1, column2) from table
// 如果column1的值等于column2的值，那么为null，否则为column1的值，for example