mysql存储过程成绩等级,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生命结束符，定义命令结束，默认为;