表结构和数据
CREATE TABLE `score` (
`s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`s_score` int(3) NULL DEFAULT NULL,
PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);
INSERT INTO `score` VALUES ('1', '2', 300);
INSERT INTO `score` VALUES ('1', '3', 300);
INSERT INTO `score` VALUES ('1', '4', 300);
INSERT INTO `score` VALUES ('1003', '4', null);
INSERT INTO `score` VALUES ('1004', '3', null);
一、if else
SELECT
s_id,
s_score, --这个课可以不用查询出来下面的if else也能用
IF
( s_score > 80, '优秀', '良好' ) AS 等级 -- 这个等级就是别名,也能起成s_score
FROM
score
二、case when then
SELECT
s_id,
s_score,--可以不用写,下面也能用
(CASE
WHEN s_score>=100 THEN '优秀'
WHEN s_score>=80 THEN '良好'
WHEN s_score>=60 THEN '中等'
ELSE'良好'
END ) 等级-- 等级只是一个别名而已
FROM score
三、 IFNULL
SELECT
s_id,
IFNULL( s_score, '没有成绩' ) score
FROM
score