DDL
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
age INT NOT NULL,
sex ENUM('M', 'F', 'O') NOT NULL, -- 假设'M'代表男,'F'代表女,'O'代表其他
introduce TEXT
);
DML
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE rnd_userName VARCHAR(255);
DECLARE rnd_age INT;
DECLARE rnd_sex CHAR(1);
DECLARE rnd_introduce TEXT;
WHILE i < num_students DO
-- 生成随机用户名
SET rnd_userName = CONCAT('Student_', LPAD(FLOOR(RAND() * 1000000), 6, '0'));
-- 生成随机年龄(例如:18到30岁之间)
SET rnd_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 随机选择性别
SET rnd_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
-- 生成随机介绍文本
SET rnd_introduce = CONCAT('Introduction for ', rnd_userName);
-- 插入数据到student表中
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (CURDATE(), rnd_userName, rnd_age, rnd_sex, rnd_introduce);
-- 更新循环计数器
SET i = i + 1;
-- 可选:为了降低负载,每插入一定数量的数据后提交一次事务
IF i % 1000 = 0 THEN
COMMIT;
END IF;
END WHILE;
-- 确保所有剩余的事务都被提交
COMMIT;
END //
DELIMITER ;
CALL InsertMockStudents(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;
-- 随机生成createDate(今天之内的一个随机时间)
SET @randomDate = CURDATE() + INTERVAL FLOOR(RAND() * 1440) MINUTE;
-- 随机生成age(假设年龄在18到30岁之间)
SET @randomAge = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 随机生成sex
SET @randomSex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
-- 插入数据到student表中
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (@randomDate, p_userName, @randomAge, @randomSex, p_introduce);
-- 提交事务
COMMIT;
END //
DELIMITER ;
CALL InsertStudent('JohnDoe', 'This is John Doe. He is a new student.');
case语句如何使用
SELECT
id,
createDate,
userName,
age,
sex,
CASE
WHEN sex = 'F' THEN '姑娘'
WHEN sex = 'M' THEN '大老爷们'
WHEN sex = 'O' THEN '泰国妹妹'
ELSE '未知' -- 可选,用于处理除'M'、'F'、'O'之外的其他情况
END AS description,
introduce
FROM
student;