DDL
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT, -- 假设id是主键且自增
createDate DATE NOT NULL, -- 创建日期
sex ENUM('M', 'F', 'O') NOT NULL, -- 性别,M代表男,F代表女,O代表其他
age INT NOT NULL, -- 年龄
userName VARCHAR(255) NOT NULL, -- 用户名
introduce TEXT -- 介绍,可以是长文本
);
DML
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < num_students DO
INSERT INTO student (createDate, sex, age, userName, introduce)
VALUES (
-- createDate: 随机日期(例如,过去10年内的某个日期)
DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY),
-- sex: 随机性别(M, F, 或 O)
ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O'),
-- age: 随机年龄(例如,18到50岁)
FLOOR(18 + RAND() * 33),
-- userName: 随机用户名(这里简化为'User'后跟一个随机数)
CONCAT('User', FLOOR(RAND() * 1000000)),
-- introduce: 随机介绍文本(这里简化为'Introduction text'后跟一个随机数)
CONCAT('Introduction text ', FLOOR(RAND() * 1000000))
);
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
随机插入20万条数据
CALL InsertRandomStudents(200000);
if函数
DELIMITER //
CREATE PROCEDURE InsertStudent(
IN p_userName VARCHAR(255),
IN p_introduce TEXT
)
BEGIN
-- 检查userName和introduce是否为空
IF p_userName IS NULL OR p_introduce IS NULL THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: userName and introduce cannot be null.';
END IF;
-- 检查userName是否以周、吴、郑、王开头
IF LEFT(p_userName, 1) = '周' OR LEFT(p_userName, 1) = '吴'
OR LEFT(p_userName, 1) = '郑' OR LEFT(p_userName, 1) = '王' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: userName cannot start with 周, 吴, 郑, or 王.';
END IF;
-- 如果检查通过,则随机生成其他数据并插入
INSERT INTO student (createDate, sex, age, userName, introduce)
VALUES (
CURDATE() - INTERVAL FLOOR(RAND() * 3650) DAY, -- 随机生成创建日期
ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O'), -- 随机生成性别
FLOOR(18 + RAND() * 33), -- 随机生成年龄
p_userName, -- 使用传入的userName
p_introduce -- 使用传入的introduce
);
END //
DELIMITER ;
case语句
SELECT
id,
createDate,
sex,
age,
userName,
introduce,
CASE sex
WHEN 'F' THEN '姑娘'
WHEN 'M' THEN '大老爷们'
WHEN 'O' THEN '泰国妹妹'
ELSE '未知性别' -- 可选,用于处理除'M', 'F', 'O'之外的其他情况
END AS description
FROM
student;