1.DDL
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(50) NOT NULL,
phone VARCHAR(15),
age INT,
sex CHAR(1),
introduce TEXT
);
2.DML
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES
('2023-07-01', '张三', '13800138000', 20, 'M', '我是张三,喜欢篮球。'),
('2023-07-02', '李四', '13900139000', 21, 'F', '我是李四,喜欢音乐。'),
('2023-07-03', '王五', '13700137000', 22, 'M', '我是王五,喜欢旅行。'),
('2023-07-04', '赵六', '13600136000', 23, 'F', '我是赵六,喜欢画画。'),
('2023-07-05', '孙七', '13500135000', 24, 'M', '我是孙七,喜欢摄影。'),
('2023-07-06', '周八', '13400134000', 25, 'F', '我是周八,喜欢跳舞。'),
('2023-07-07', '吴九', '13300133000', 26, 'M', '我是吴九,喜欢游泳。');
3.DQL
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE random_userName VARCHAR(50);
DECLARE random_phone VARCHAR(15);
DECLARE random_age INT;
DECLARE random_sex CHAR(1);
DECLARE random_introduce TEXT;
WHILE counter < num_students DO
-- 生成随机userName(示例:使用现有的字符串函数生成随机字符串,你可以根据需要调整)
SET random_userName = CONCAT('User', FLOOR(RAND() * 1000000));
-- 生成随机phone(示例:随机生成手机号码,你可以根据需要调整格式)
SET random_phone = CONCAT(FLOOR(RAND() * 900000000 + 100000000), '');
-- 生成随机age(示例:18到30岁之间)
SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 生成随机sex(M或F)
SET random_sex = IF(RAND() > 0.5, 'M', 'F');
-- 生成随机introduce(示例:使用现有的字符串函数生成随机字符串,你可以根据需要调整)
SET random_introduce = CONCAT('Introduction for ', random_userName);
-- 插入数据到student表
INSERT INTO student(createDate, userName, phone, age, sex, introduce)
VALUES(CURDATE(), random_userName, random_phone, random_age, random_sex, random_introduce);
-- 更新计数器
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
#调用数据
CALL InsertRandomStudents(200000);