DDL建表
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT, -- 假设id是一个自增的主键
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(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE random_username VARCHAR(255);
DECLARE random_age INT;
DECLARE random_sex ENUM('M', 'F', 'O');
DECLARE random_introduce TEXT;
WHILE counter < num_students DO
-- 生成随机用户名
SET random_username = CONCAT('User', FLOOR(RAND() * 1000000));
-- 生成随机年龄(例如,15到65岁之间)
SET random_age = FLOOR(RAND() * 51) + 15;
-- 随机选择性别
SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
-- 生成随机介绍文本
SET random_introduce = CONCAT('Introduction for ', random_username);
-- 插入随机生成的数据
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (
CURDATE() - INTERVAL FLOOR(RAND() * 3650) DAY, -- 随机创建日期
random_username,
random_age,
random_sex,
random_introduce
);
-- 增加计数器
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudents(200000);
DQL插入语句
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;
-- 插入数据,其他字段随机生成或使用默认值
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (
CURDATE() - INTERVAL FLOOR(RAND() * 3650) DAY, -- 随机生成createDate
p_userName, -- 使用传入的userName
FLOOR(RAND() * 50) + 15, -- 随机生成age
ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O'), -- 随机生成sex
p_introduce -- 使用传入的introduce
);
END //
DELIMITER ;
CALL InsertStudentData('张三', '这是一个介绍文本。');
DQL查询语句
SELECT
id,
createDate,
userName,
age,
sex,
introduce,
CASE
WHEN sex = 'F' THEN '姑娘'
WHEN sex = 'M' THEN '大老爷们'
WHEN sex = 'O' THEN '泰国妹妹'
ELSE '未知' -- 可选,用于处理不符合上述条件的其他情况
END AS sex_description
FROM
student;