一、建表语句 创建测试表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`
本文为麦志健原创文章,转载无需和我联系,但请注明来自麦志健博客http://maizhijian.com
最新评论