mysql使用存储过程批量生成学生表、课程表和成绩表测试数据

create table Course(
    c_id int PRIMARY KEY,
    name varchar(10)
)


create table Student(
    id int PRIMARY KEY,
    name varchar(10)
)


CREATE table SC(
    sc_id int PRIMARY KEY,
    s_id int,
    c_id int,
    score int
)




//课程表写入100条数据
DELIMITER ;; 
CREATE PROCEDURE test_insert_c() 
BEGIN 
DECLARE Y INT DEFAULT 1;
WHILE Y<=100
DO
INSERT INTO test.Course VALUES(Y,CONCAT('课程',Y)); 
SET Y=Y+1; 
END WHILE ; 
COMMIT; 
END;; 
CALL test_insert_c();




//学生表写入70000条数据
DELIMITER ;; 
CREATE PROCEDURE test_insert() 
BEGIN 
DECLARE Y INT DEFAULT 1;
WHILE Y<=70000
DO
INSERT INTO test.Student VALUES(Y,CONCAT('学生',Y)); 
SET Y=Y+1; 
END WHILE ; 
COMMIT; 
END;; 
CALL test_insert();


//成绩表写入700000条数据
DELIMITER ;; 
CREATE PROCEDURE test_insert_sc() 
BEGIN 
DECLARE Y INT DEFAULT 1;  
DECLARE s_id INT DEFAULT 1;
DECLARE c_id INT DEFAULT 1;
DECLARE score INT DEFAULT 1;


WHILE Y<=700000
DO
INSERT INTO test.SC VALUES(Y,s_id,c_id,score); 
SET Y=Y+1; 
SELECT FLOOR(1 + (RAND() * 100)) into c_id;
SELECT FLOOR(1 + (RAND() * 100)) into score;
SELECT FLOOR(1 + (RAND() * 70000)) into s_id;
END WHILE ; 
COMMIT; 
END;; 
CALL test_insert_sc();


说明:
DELIMITER生命结束符，定义命令结束，默认为;