#1、创建DDL
CREATE TABLE student (
id INT PRIMARY KEY,
createDate DATE,
userName VARCHAR(255),
age INT,
sex VARCHAR(10),
introduce TEXT
);
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < num_students DO
INSERT INTO student (id, createDate, userName, age, sex, introduce)
VALUES (
-- 假设id是自增的,可以省略,或者根据需要生成唯一的id
0, -- 或者使用某个生成唯一ID的逻辑
-- 随机生成创建日期,这里假设从过去10年内随机选择
FROM_UNIXTIME(UNIX_TIMESTAMP('2013-01-01') + FLOOR(RAND() * (UNIX_TIMESTAMP('2023-01-01') - UNIX_TIMESTAMP('2013-01-01') + 1))),
-- 随机生成用户名
CONCAT('User', FLOOR(RAND() * 1000000)),
-- 随机生成年龄(假设范围在18到30岁)
FLOOR(RAND() * (30 - 18 + 1)) + 18,
-- 随机分配性别
IF(RAND() > 0.5, 'Male', 'Female'),
-- 随机生成介绍文本(这里为了简化,只使用简单的文本)
CONCAT('Introduction for User ', FLOOR(RAND() * 1000000))
);
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
#2、插入20万条DML
CALL InsertRandomStudents(200000);
#3、if判断如何使用
DELIMITER //
CREATE PROCEDURE InsertStudentData(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 = '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 = 'userName cannot start with 周, 吴, 郑, or 王';
END IF;
-- 随机生成id(假设id不是自增,否则可以省略)
SET @random_id := FLOOR(RAND() * 1000000);
-- 随机生成createDate
SET @random_createDate := FROM_UNIXTIME(UNIX_TIMESTAMP('2000-01-01') + FLOOR(RAND() * (UNIX_TIMESTAMP(CURDATE()) - UNIX_TIMESTAMP('2000-01-01') + 1)));
-- 随机生成age
SET @random_age := FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 随机分配sex
SET @random_sex := IF(RAND() > 0.5, 'Male', 'Female');
-- 插入数据
INSERT INTO student (id, createDate, userName, age, sex, introduce)
VALUES (@random_id, @random_createDate, p_userName, @random_age, @random_sex, p_introduce);
END //
DELIMITER ;
CALL InsertStudentData('JohnDoe', 'This is John Doe.');
#4、case语句如何使用
SELECT
id,
createDate,
userName,
age,
sex,
introduce,
CASE
WHEN sex = 'F' THEN '姑娘'
WHEN sex = 'M' THEN '大老爷们'
WHEN sex = 'O' THEN '泰国妹妹'
ELSE '未知' -- 可选,用于处理除F、M、O之外的其他情况
END AS sex_description
FROM
student;