mysql批量创建脚本

#创建部门表

create table dept(/*部门表*/

deptno mediumint unsigned not null default 0,

dname varchar(13) not null default "",

loc varchar(13) not null default ""

)ENGINE=MyIsam default charset = utf8;

#创建雇员表EMP

create table emp(

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 = MyIsam default charset = utf8;

#工资级别表 

create table salgrade

(

grade mediumint unsigned not null default 0,

losal decimal(17,2) not null,

hisal decimal(17,2) not null)

Engine = MYisam default charset = utf8;

insert into salgrade values(1,700,1200);

insert into salgrade values(2,1201,1400); 

insert into salgrade values(3,1401,2000);

insert into salgrade values(4,2001,3000);

insert into salgrade values(5,3001,9999); 

#随机产生字符串

delimiter$$

drop function  rand_string $$

create function rand_string (n int )

returns varchar(255)

begin

    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABC  DEFGHIJKLMNOPQRSTUVWXYZ';

    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$$

delimiter ;

select rand_string(6);

#随机产生部门编号

delimiter $$

drop function rand_num/*如果不存在会报错*/

#创建自定义产生随机数的函数

create function rand_num()

returns int(5)

begin 

    declare i int default 0; 

    set i = floor(10+rand()*500);/*产生10-510之间的数*/ 

return i;

end$$

delimiter ;

select rand_num();

 

#****************************************************************

#向emp  表中加入记录

delimiter $$

drop procedure  insert_emp$$/*没有此存储过程的话会报错，不理即可*/

create procedure insert_emp(in start int(10),in max_num int(10))

begin

    declare i int default 0;

    set autocommit = 0; /*这里autocommit=0相当于开启事务start transaction，使用事务的原因主要是保持插入的完整性，如果不实用事务，，默认一次Insert是自动提交commit并修改数据库的，开启事务后只有当所有都修改完毕后提交才会修改数据库  */

    repeat

    set i = i +1;

        insert into emp values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());

        until i = max_num

    end repeat;

    commit;

end$$

delimiter ;

#调用刚刚写好的函数

call insert_emp(100001,1800000);

delimiter $$

#创建添加部门的存储过程语句

create procedure insert_dept(in start int(10), in max_num int(10))

begin

    declare i int default 0; 

    set autocommit = 0; 

    repeat  

        set i = i+1;

        insert into dept values ((start+i),rand_string(10), rand_string(8)); 

        until i = max_num 

    end repeat; 

    commit;

end $$

delimiter ;

call insert_dept(100,10);

#*********************************

#向salgrade表插入数据

delimiter $$

drop procedure insert_salgrade $$ /*没有的话会报错，忽略即可*/

create procedure insert_salgrade(in start int(10),in max_num int(10))

begin

    declare i int default 0; 

    set autocommit = 0; 

    alter table emp disable keys; /*这里是禁用索引，*/ 

    repeat 

        set i = i +1; 

        insert into salgrade values((start+i),(start+i),(start+i)); 

        until i = max_num;

    end repeat; 

    commit; 

end $$

delimiter ;