MySQL复习资料(附加)case when
【摘要】 编辑MySQL复习资料(附加)case when 📋文章链接📋MySQL复习资料(一)——MySQL环境安装MySQL复习资料(二)——MySQL-DDL语句MySQL复习资料(三)——MySQL-DML语句MySQL复习资料(四)——MySQL-聚合函数MySQL复习资料(五)——MySQL-索引MySQL复习资料(六)——MySQL-多表联合查询MySQL复习资料(七)——My...
MySQL复习资料(附加)case when
📋文章链接📋
本章内容：case when的使用
/*
Navicat Premium Data Transfer
Source Server : rm-bp1zq3879r28p726lco.mysql.rds.aliyuncs.com_3306
Source Server Type : MySQL
Source Server Version : 50732
Source Host : rm-bp1zq3879r28p726lco.mysql.rds.aliyuncs.com:3306
Source Schema : mytest
Target Server Type : MySQL
Target Server Version : 50732
File Encoding : 65001
Date: 28/06/2022 18:58:48
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`createDate` datetime(0) NOT NULL,
`userName` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`passWord` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`age` int(3) NOT NULL,
`phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`introduce` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `userName_index`(`userName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (1, '2022-06-26 13:43:11', 'admin', '123456', 22, '15912345678', '喜欢学习.');
INSERT INTO `users` VALUES (2, '2022-06-26 13:43:11', 'zhangsan', '123456', 32, '15912345678', '喜欢做饭.');
INSERT INTO `users` VALUES (3, '2022-06-26 13:43:11', 'lisi', '45451', 42, '15912345678', '喜欢化妆.');
INSERT INTO `users` VALUES (4, '2022-06-26 13:43:11', 'zhaoliu', '2222', 52, '15912345678', '喜欢武术.');
INSERT INTO `users` VALUES (5, '2022-06-26 13:43:11', 'zhaoliu', '11111', 16, '15912345678', '喜欢舞蹈.');
INSERT INTO `users` VALUES (6, '2022-06-26 13:43:11', 'zhaoliu', '123787', 27, '15912345678', '喜欢泡妞.');
SET FOREIGN_KEY_CHECKS = 1;
测试：
直接无符号判断
select userName,
(
case
when sex=0 then '女'
when sex=1 then '男'
end
) sexName
from users;
有符号判断
select userName,age,
(
case
when age<20 then '青少年'
when age<30 then '青年'
else '壮年'
end
) state
from users
创建视图，并针对视图查询
#查询并创建视图
create view newTableName as (
select userName,age,
(
case
when age<20 then '青少年'
when age<30 then '青年'
else '壮年'
end
) state
from users
);
select state '状态',count(state) '数量'
from newTableName GROUP BY state;注意勤加练习啊。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）