mysql的测试和造数据脚本
mysql的测试和造数据脚本:
use cim; drop table if exists student; create table student ( s_id int(11) not null auto_increment, sno int(11), sname varchar(50), sage int(11), ssex varchar(8), father_id int(11), mather_id int(11), note varchar(500), primary key (s_id), unique key uk_sno (sno) ) engine = innodb default charset = utf8mb4; truncate table student; delimiter $$ drop function if exists insert_student_data $$ create function insert_student_data() returns int deterministic begin declare i int; set i = 1; while i < 50000000 do insert into student values (i, i, concat('name', i), i, case when floor(rand() * 10) % 2 = 0 then 'f' else 'm' end, floor(rand() * 100000), floor(rand() * 1000000), concat('note', i)); set i = i + 1; end while; return 1; end$$ delimiter ; select insert_student_data(); select count(*) from student; use cim; create table course ( c_id int(11) not null auto_increment, cname varchar(50), note varchar(500), primary key (c_id) ) engine = innodb default charset = utf8mb4; truncate table course; delimiter $$ drop function if exists insert_course_data $$ create function insert_course_data() returns int deterministic begin declare i int; set i = 1; while i <= 1000 do insert into course values (i, concat('course', i), floor(rand() * 1000), concat('note', i)); set i = i + 1; end while; return 1; end$$ delimiter ; select insert_course_data(); select count(*) from course; use cim; drop table if exists sc; create table sc ( s_id int(11), c_id int(11), t_id int(11), score int(11) ) engine = innodb default charset = utf8mb4; truncate table sc; delimiter $$ drop function if exists insert_sc_data $$ create function insert_sc_data() returns int deterministic begin declare i int; set i = 1; while i <= 50000000 do insert into sc values (i, floor(rand() * 1000), floor(rand() * 10000000), floor(rand() * 750)); set i = i + 1; end while; return 1; end$$ delimiter ; select insert_sc_data(); commit; select insert_sc_data(); commit; create index idx_s_id on sc (s_id); create index idx_t_id on sc (t_id); create index idx_c_id on sc (c_id); select count(*) from sc; use cim; drop table if exists teacher; create table teacher ( t_id int(11) not null auto_increment, tname varchar(50), note varchar(500), primary key (t_id) ) engine = innodb default charset = utf8mb4; truncate table teacher; delimiter $$ drop function if exists insert_teacher_data $$ create function insert_teacher_data() returns int deterministic begin declare i int; set i = 1; while i <= 10000000 do insert into teacher values (i, concat('tname', i), concat('note', i)); set i = i + 1; end while; return 1; end$$ delimiter ; select insert_teacher_data(); commit; select count(*) from teacher;
扫码领红包
微信赞赏
支付宝扫码领红包
声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。