如何将数据中保存性别(0,1)在查询出来显示的时候显示为男女呢?如果统计男,女的人数呢。
之前对SQL中的WHEN-CASE不太熟悉。这里对WHEN-CASE做一个学习笔记。
简介
简单介绍一下CASE-WHEN语句
MySQL中的CASE-WHEN语句用于根据一定的条件来返回不同的结果。它可以用在SELECT、UPDATE、DELETE等语句中,用于对结果进行筛选、排序、计算等操作。
基本语法如下图所示
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
其中,condition是一个条件表达式,result是条件成立时返回的结果。如果没有任何条件成立,则返回ELSE后面的结果。
创建学生表
为测试做点准备,创建一张学生表。
CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) NOT NULL,
age INT(11),
gender INT(11) COMMENT '0-未知,1-男,2-女',
phone VARCHAR(16),
email VARCHAR(32)
);
插入数据
INSERT INTO student (name, age, gender, phone, email) VALUES
('张三', 20, 1, '13812345678', 'zhangsan@example.com'),
('李四', 22, 2, '13912345678', 'lisi@example.com'),
('王五', 21, 1, '13612345678', 'wangwu@example.com'),
('赵六', 19, 0, '13712345678', 'zhaoliu@example.com');
现在表中的数据
MySQL [test]> select * from student;
+----+--------+------+--------+-------------+----------------------+
| id | name | age | gender | phone | email |
+----+--------+------+--------+-------------+----------------------+
| 1 | 张三 | 20 | 1 | 13812345678 | zhangsan@example.com |
| 2 | 李四 | 22 | 2 | 13912345678 | lisi@example.com |
| 3 | 王五 | 21 | 1 | 13612345678 | wangwu@example.com |
| 4 | 赵六 | 19 | 0 | 13712345678 | zhaoliu@example.com |
+----+--------+------+--------+-------------+----------------------+
在CRUD语句中的应用
SELECT
-
将对应的性别int值转换为男、女进行展示,当
gender = 0
时展示未知,为1时展示男,为2时展示女。当然也可以使用as
来设置列的别名。SELECT id, gender, CASE WHEN gender = 0 THEN '未知' WHEN gender = 1 THEN '男' WHEN gender = 2 THEN '女' END AS '性别' FROM student; +----+--------+--------+ | id | gender | 性别 | +----+--------+--------+ | 1 | 1 | 男 | | 2 | 2 | 女 | | 3 | 1 | 男 | | 4 | 0 | 未知 | +----+--------+--------+
以上语句等同于,将需要判断的列至于case-when之间。
SELECT id, gender, CASE gender WHEN 0 THEN '未知' WHEN 1 THEN '男' WHEN 2 THEN '女' END AS '性别' FROM student;
上面语句并没有else,如果不符合所有的条件,则值为NULL。
# 插入一条语句 INSERT INTO student(NAME, age, gender, phone, email) VALUE ( '陈平安', 18, 3, '18312312344', 'aaaa@xxx.com' ); #再次查询 结果 +----+--------+--------+ | id | gender | 性别 | +----+--------+--------+ | 1 | 1 | 男 | | 2 | 2 | 女 | | 3 | 1 | 男 | | 4 | 0 | 未知 | | 5 | 3 | NULL | +----+--------+--------+
配合COUNT使用。统计男生和女生的总人数和男生,女生人数。
SELECT COUNT( CASE WHEN gender IN(1, 2) THEN 1 ELSE NULL END ) AS '总人数', COUNT( CASE WHEN gender = 1 THEN 1 ELSE NULL END ) AS '男生', COUNT( CASE WHEN gender = 2 THEN 1 ELSE NULL END ) AS '男生' FROM student; # 结果 +-----------+--------+--------+ | 总人数 | 男生 | 男生 | +-----------+--------+--------+ | 3 | 2 | 1 | +-----------+--------+--------+
上面的语句不用ELSE结果也是相同的,因为COUNT(条件)不会统计NULL值,没有else那么默认的也为NULL。
CREATE
这里只是简单的举个例子。
# 我们新增一个字段,直接展示学生的性别。
ALTER TABLE
student ADD gender_cn VARCHAR(16);
# 插入一条语句 当然周米粒是一个女孩
INSERT INTO student(NAME, age, gender, phone, email,gender_cn)
VALUE
(
'周米粒',
18,
1,
'1111111',
'aaaa@xxx.com',
case gender when 1 then '男'when 2 then '女' else '未知' end
);
# 结果
+----+-----------+------+--------+-------------+----------------------+-----------+
| id | name | age | gender | phone | email | gender_cn |
+----+-----------+------+--------+-------------+----------------------+-----------+
| 1 | 张三 | 20 | 1 | 13812345678 | zhangsan@example.com | NULL |
| 2 | 李四 | 22 | 2 | 13912345678 | lisi@example.com | NULL |
| 3 | 王五 | 21 | 1 | 13612345678 | wangwu@example.com | NULL |
| 4 | 赵六 | 19 | 0 | 13712345678 | zhaoliu@example.com | NULL |
| 5 | 陈平安 | 18 | 3 | 18312312344 | aaaa@xxx.com | NULL |
| 6 | 周米粒 | 18 | 1 | 1111111 | aaaa@xxx.com | 男 |
+----+-----------+------+--------+-------------+----------------------+-----------+
UPDATE
也可以用于修改语句,根据条件修改为不同的值。
# 语句
UPDATE
student
SET
gender_cn = CASE gender WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知'
END;
#结果
+----+-----------+------+--------+-------------+----------------------+-----------+
| id | name | age | gender | phone | email | gender_cn |
+----+-----------+------+--------+-------------+----------------------+-----------+
| 1 | 张三 | 20 | 1 | 13812345678 | zhangsan@example.com | 男 |
| 2 | 李四 | 22 | 2 | 13912345678 | lisi@example.com | 女 |
| 3 | 王五 | 21 | 1 | 13612345678 | wangwu@example.com | 男 |
| 4 | 赵六 | 19 | 0 | 13712345678 | zhaoliu@example.com | 未知 |
| 5 | 陈平安 | 18 | 3 | 18312312344 | aaaa@xxx.com | 未知 |
| 6 | 周米粒 | 18 | 1 | 1111111 | aaaa@xxx.com | 男 |
+----+-----------+------+--------+-------------+----------------------+-----------+
DELETE
比如我们想删除年龄等于20岁的男性,大于22的女性
DELETE FROM
student
WHERE
age = (CASE WHEN gender = 1 THEN 20 WHEN gender = 2 THEN 22
END);
# 结果
+----+-----------+------+--------+-------------+---------------------+-----------+
| id | name | age | gender | phone | email | gender_cn |
+----+-----------+------+--------+-------------+---------------------+-----------+
| 3 | 王五 | 21 | 1 | 13612345678 | wangwu@example.com | 男 |
| 4 | 赵六 | 19 | 0 | 13712345678 | zhaoliu@example.com | 未知 |
| 5 | 陈平安 | 18 | 3 | 18312312344 | aaaa@xxx.com | 未知 |
| 6 | 周米粒 | 18 | 1 | 1111111 | aaaa@xxx.com | 男 |
+----+-----------+------+--------+-------------+---------------------+-----------+
CASE-THEN语句的应用场景非常广泛,可以用于SELECT语句中的数据筛选、排序、计算等操作,也可以用于UPDATE和DELETE语句中的数据更新和删除操作。此外,CASE-THEN语句还可以用于其他表达式中,例如WHERE子句、HAVING子句、GROUP BY子句等。