# 1. 创建学生表和课程表
create table `student_info`(
`id` int(11) not null auto_increment,
`student_id` int not null,
`name` varchar(20) default null,
`course_id` int not null,
`class_id` int(11) default null,
`create_time` datetime default current_timestamp on update current_timestamp,
primary key (`id`)
) ENGINE=INNODB auto_increment=1 default charset=utf8;
create table `course`(
`id` int(11) not null auto_increment,
`course_id` int not null,
`course_name` varchar(40) default null,
primary key (`id`)
) engine=innodb auto_increment=1 default charset=utf8;
# 函数1:创建随机产生字符串函数
select @@log_bin_trust_function_creators; # 如果值是0,需要执行下面一行设置,否则执行创建存储过程会报错
set global log_bin_trust_function_creators=1;
create function rand_string(n int)
returns varchar(255) #该函数会返回一个字符串
begin
declare chars_str varchar(100) default 'abcdefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n DO
set return_str=concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end
# 函数2:创建随机数函数
create function rand_num(from_num int, to_num int) returns int(11)
begin
declare i int default 0;
set i = floor(from_num + rand()*(to_num - from_num + 1));
return i;
end
# 存储过程1:创建插入课程表存储过程
create procedure insert_course(max_num int)
begin
declare i int default 0;
set autocommit = 0; #设置手动提交事务
repeat # 循环
set i = i + 1;
insert into course(course_id, course_name) values(rand_num(10000, 10100), rand_string(6));
until i = max_num
end repeat;
commit; # 提交事务
end
# 存储过程2:创建插入学生信息表存储过程
create procedure insert_stu(max_num int)
begin
declare i int default 0;
set autocommit = 0; # 设置手动提交事务
repeat # 循环
set i = i+1; # 赋值
insert into student_info (course_id, class_id, student_id, name) values (rand_num(10000, 10100), rand_num(10000, 10200), rand_num(1, 200000), rand_string(6));
until i = max_num
end repeat;
commit; # 提交事务
end
# 调用存储过程
call insert_course (100); # 调用存储过程1,插入课程表100条数据
select count(*) from course c ;
call insert_stu (1000000); # 调用存储过程2,插入学生表1000000条数据
select count(*) from student_info si ;