1、DDL与DML
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
userName VARCHAR(255) NOT NULL,
phone VARCHAR(15),
age INT,
sex CHAR(1) COMMENT 'M: 男, F: 女, O: 其他',
introduce TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
INSERT INTO student (userName, phone, age, sex, introduce) VALUES
('张三', '13800138000', 20, 'M', '我是张三,就读于计算机科学专业。'),
('李四', '13900139000', 21, 'F', '我是李四,喜欢阅读和旅行。'),
('王五', '13700137000', 22, 'M', '我是王五,对摄影有浓厚兴趣。'),
('赵六', '13600136000', 23, 'F', '我是赵六,热衷于时尚和设计。'),
('孙七', '13500135000', 20, 'O', '我是孙七,对科学探索充满好奇。'),
('周八', '13400134000', 21, 'M', '我是周八,喜欢运动和健身。'),
('吴九', '13300133000', 22, 'F', '我是吴九,对音乐和舞蹈很有感觉。');
2、case创建
SELECT
id,
createDate,
userName,
phone,
age,
CASE
WHEN sex = 'M' THEN '爷们'
WHEN sex = 'F' THEN '姑娘'
ELSE '其他'
END AS gender, -- 这里使用gender作为转换后性别的别名
introduce
FROM
student;