准备两张测试表,随便插入几条模拟数据
- 用户表: user
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一id',
`name` varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
- 支付记录表: pay
CREATE TABLE `pay` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_id` int(11) NOT NULL COMMENT '用户id',
`product_name` varchar(64) DEFAULT NULL COMMENT '商品名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
1. IFNULL() 使用
查询时候需要查出字段为空或者为某个特定值的数据
例如:假设我们要查询 pay 这张表所有的支付记录,如果产品名称为 NULL,则统一填充 “未知”
SELECT id,user_id,IFNULL(product_name,"未知") FROM `pay`;
结果:
2. Case… When… Then… Else… End… 多条件判断用法
例如:假如我们查询所有的支付记录,要知道支付人的名字 name ,如果支付记录 user_id 为 0 ,则 name 填充为 “机器人”
SELECT
p.id AS id,
p.user_id AS userId,
IFNULL( p.product_name, "未知" ) AS productName,
CASE
WHEN ( p.user_id = 0 ) THEN
'机器人' ELSE u.`name`
END AS userName
FROM
`pay` p
LEFT JOIN USER u ON p.user_id = u.id
结果:
(虽然我们可以在后续的代码逻辑可以用别的逻辑去最后填充这个 userName=“机器人”这种情况,但我们是高质量的程序员,我们都要高要求自己,这个时候能从我们 优秀的 SQL 做到的话,就从 SQL 出发)
注:
Case具有两种格式。简单Case函数和Case搜索函数
- 简单Case函数
CASE user_id
WHEN '0' THEN '机器人'
WHEN '-1' THEN '机器人-1'
ELSE '其他'
END
- Case搜索函数
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE
'其他'
END