1.导入sql脚本
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 100509
Source Host : localhost:3306
Source Schema : jtsys
Target Server Type : MySQL
Target Server Version : 100509
File Encoding : 65001
Date: 08/03/2022 21:58:53
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student1
-- ----------------------------
DROP TABLE IF EXISTS `student1`;
CREATE TABLE `student1` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student1
-- ----------------------------
INSERT INTO `student1` VALUES (1, '孙义龙', '1');
INSERT INTO `student1` VALUES (2, '朱亚萍', '0');
INSERT INTO `student1` VALUES (3, 'aaa', NULL);
SET FOREIGN_KEY_CHECKS = 1;
2.sql语句
--根据sex值,1为男,0为女,其他为''
--方法1:简单case函数
SELECT NAME,CASE
sex
WHEN '1' THEN
'男'
WHEN '0' THEN
'女' ELSE ''
END sex
FROM
student1
--方法2:case搜索函数
SELECT NAME,CASE
WHEN sex ='1' THEN
'男'
WHEN sex ='0' THEN
'女' ELSE ''
END sex
FROM
student1
--方法3:if函数
SELECT NAME
,
IF
( sex = '1', '男', IF ( sex = '0', '女', '' ) ) sex
FROM
student1