根据两个DDL创建一个插入信息的存储过程，先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，所有信息随机生成，学生表的自增序号从6开始。

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);
 
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(255) NOT NULL,
    result FLOAT NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);

DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01', '张三', '13800138000', 20, '男', '学习刻苦'),
('2023-04-02', '李四', '13800138001', 21, '女', '成绩优异'),
('2023-04-03', '王五', '13800138002', 22, '男', '热爱运动'),
('2023-04-04', '赵六', '13800138003', 20, '女', '文静内向'),
('2023-04-05', '孙七', '13800138004', 21, '男', '乐于助人');
 
-- 张三的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 90, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 85, 1);
 
 
-- 李四的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 88, 2);
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 92, 2);
 
 
-- 王五的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 80, 3);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 85, 3);
 
 
-- 赵六的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 95, 4);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 89, 4);
 
 
-- 孙七的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 78, 5);
INSERT INTO score (scoreName, result, studentId) VALUES ('生物', 87, 5);

DQL

DELIMITER //
CREATE PROCEDURE InsertRandomStudentAndScoreData()
BEGIN
  -- 设置学生表的自增起始值为6
  ALTER TABLE student AUTO_INCREMENT = 6;
  
  -- 定义变量
  DECLARE student_id INT;
  DECLARE i INT DEFAULT 1;
  DECLARE j INT DEFAULT 1;
  
  -- 插入10个学生记录
  WHILE i <= 10 DO
    -- 生成随机数据
    SET @createDate = DATE_ADD(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY);
    SET @userName = CONCAT('Student', FLOOR(RAND() * 10000));
    SET @phone = CONCAT('1', LPAD(FLOOR(RAND() * 9999999999), 10, '0'));
    SET @age = FLOOR(RAND() * (50 - 10 + 1)) + 10; -- 假设年龄范围在10到60之间
    SET @sex = IF(RAND() > 0.5, '男', '女');
    SET @introduce = CONCAT('This is a random introduction for student.');
    
    -- 插入学生记录
    INSERT INTO student (createDate, userName, phone, age, sex, introduce)
    VALUES (@createDate, @userName, @phone, @age, @sex, @introduce);
    
    -- 获取新插入学生的ID
    SET student_id = LAST_INSERT_ID();
    
    -- 为当前学生插入10条成绩记录
    WHILE j <= 10 DO
      SET @scoreName = CONCAT('Score', FLOOR(RAND() * 10)); -- 假设有10个不同的科目名称
      SET @result = FLOOR(RAND() * 100) + 1; -- 假设成绩范围是1到100
      
      -- 插入成绩记录
      INSERT INTO score (scoreName, result, studentId)
      VALUES (@scoreName, @result, student_id);
      
      SET j = j + 1;
    END WHILE;
    
    SET i = i + 1;
    SET j = 1; -- 重置j以便为下一个学生插入成绩
  END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudentAndScoreData();