MySQL逻辑判断用法

一、建表语句 创建测试表user,并添加测试数据;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `sex` tinyint(2) NOT NULL DEFAULT 0 COMMENT '0男1女',
  `score` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 51 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '姓名1', 1, 23);
INSERT INTO `user` VALUES (2, '姓名2', 0, 92);
INSERT INTO `user` VALUES (3, '姓名3', 0, 49);
INSERT INTO `user` VALUES (4, '姓名4', 0, 85);
INSERT INTO `user` VALUES (5, '姓名5', 1, 45);
INSERT INTO `user` VALUES (6, '姓名6', 1, 100);
INSERT INTO `user` VALUES (7, '姓名7', 1, 42);
INSERT INTO `user` VALUES (8, '姓名8', 1, 44);
INSERT INTO `user` VALUES (9, '姓名9', 1, 56);
INSERT INTO `user` VALUES (10, '姓名10', 0, 17);
INSERT INTO `user` VALUES (11, '姓名11', 1, 22);
INSERT INTO `user` VALUES (12, '姓名12', 0, 21);
INSERT INTO `user` VALUES (13, '姓名13', 0, 97);
INSERT INTO `user` VALUES (14, '姓名14', 0, 96);
INSERT INTO `user` VALUES (15, '姓名15', 1, 86);
INSERT INTO `user` VALUES (16, '姓名16', 0, 82);
INSERT INTO `user` VALUES (17, '姓名17', 0, 5);
INSERT INTO `user` VALUES (18, '姓名18', 1, 31);
INSERT INTO `user` VALUES (19, '姓名19', 0, 90);
INSERT INTO `user` VALUES (20, '姓名20', 0, 19);
INSERT INTO `user` VALUES (21, '姓名21', 0, 88);
INSERT INTO `user` VALUES (22, '姓名22', 0, 77);
INSERT INTO `user` VALUES (23, '姓名23', 1, 34);
INSERT INTO `user` VALUES (24, '姓名24', 1, 8);
INSERT INTO `user` VALUES (25, '姓名25', 1, 95);
INSERT INTO `user` VALUES (26, '姓名26', 0, 20);
INSERT INTO `user` VALUES (27, '姓名27', 0, 62);
INSERT INTO `user` VALUES (28, '姓名28', 1, 67);
INSERT INTO `user` VALUES (29, '姓名29', 0, 56);
INSERT INTO `user` VALUES (30, '姓名30', 1, 10);
INSERT INTO `user` VALUES (31, '姓名31', 0, 28);
INSERT INTO `user` VALUES (32, '姓名32', 1, 45);
INSERT INTO `user` VALUES (33, '姓名33', 0, 88);
INSERT INTO `user` VALUES (34, '姓名34', 0, 72);
INSERT INTO `user` VALUES (35, '姓名35', 0, 27);
INSERT INTO `user` VALUES (36, '姓名36', 1, 3);
INSERT INTO `user` VALUES (37, '姓名37', 1, 36);
INSERT INTO `user` VALUES (38, '姓名38', 0, 56);
INSERT INTO `user` VALUES (39, '姓名39', 0, 57);
INSERT INTO `user` VALUES (40, '姓名40', 1, 51);
INSERT INTO `user` VALUES (41, '姓名41', 1, 57);
INSERT INTO `user` VALUES (42, '姓名42', 0, 31);
INSERT INTO `user` VALUES (43, '姓名43', 0, 56);
INSERT INTO `user` VALUES (44, '姓名44', 1, 98);
INSERT INTO `user` VALUES (45, '姓名45', 1, 78);
INSERT INTO `user` VALUES (46, '姓名46', 0, 50);
INSERT INTO `user` VALUES (47, '姓名47', 1, 85);
INSERT INTO `user` VALUES (48, '姓名48', 0, 64);
INSERT INTO `user` VALUES (49, '姓名49', 0, 85);
INSERT INTO `user` VALUES (50, '姓名50', 1, 92);

SET FOREIGN_KEY_CHECKS = 1;


二、if基本用法

//语法
//如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。
IF(expr1,expr2,expr3)

例子:查询user表男女分别总人数;

SELECT
	SUM( IF ( sex = 0, 1, 0 ) ) AS '男总数',
	SUM( IF ( sex = 1, 1, 0 ) ) AS '女总数' 
FROM
	`user`

结果:


三、case when基本用法

MySQL 的 case when 的语法有两种:

简单函数:

//语法1
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

搜索函数:

//语法2
CASE WHEN [expr] THEN [result1]…ELSE [default] END

例子:查询user表阶梯分数的数据:

SELECT
	*,
	( 
		CASE 
			sex WHEN 0 THEN 
				'男' 
			WHEN 1 THEN 
				'女' 
			END 
	) AS '性别',
	( 
		CASE 
			WHEN score >= 90 THEN
				'优秀' 
			WHEN score >= 60 THEN 
				'及格' 
			ELSE 
				'不及格' 
		END 
		) AS '等级' 
FROM
	`user`

麦志建博客
请先登录后发表评论
  • latest comments
  • 总共0条评论