1 建表create table dept( id int unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname varchar(20) not null default "",
loc varchar(13) not null default "")engine=innodb default charset=gbk;create table emp( id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,/*编号*/
ename varchar(20) not null default "",/*姓名*/
job varchar(9) not null default "",/*工作*/
mgr mediumint unsigned not null default 0,/*上级编号*/
hiredate date not null,/*入职时间*/
sal decimal(7,2) not null, /*薪水*/
comm decimal(7,2) not null,/*红利*/
deptno mediumint unsigned not null default 0/*部门编号*/)engine=innodb default charset=gbk;
2 设置参数show variables like 'log_bin_trust_function_creators';set global log_bin_trust_function_creators=1;
3 创建函数a.随机产生字符串
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnm';
declare return_str varchar(255) default '';
declare i int default 0; while i
set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1; end while; return return_str; end $$
b.随机产生部门编号
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10); return i; end $$
4 创建存储过程a.创建往emp表中插入数据的存储过程
delimiter $$create procedure insert_emp(in start int(10),in max_num int(10))begin declare i int default 0; /*把autocommit设置成0*/ set autocommit= 0;
repeat set i=i+1; insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i=max_num end repeat;commit;end $$
b.创建往dept表中插入数据的存储过程
delimiter $$create procedure insert_dept(in start int(10),in max_num int(10))begindeclare i int default 0; set autocommit=0;
repeat set i=i+1; insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i=max_num end repeat; commit; end $$
5 调用存储过程call insert_dept(100,10);//从deptno为100起插入10条随机生成数据call insert_emp(100001,5000000);//从empno为100001起插入5000000条随机生成数据