MySQL复习资料(附加)case when

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;

注意勤加练习啊。