#創(chuàng)建表DEPT
CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;



#創(chuàng)建表EMP雇員
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*編號(hào)*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級(jí)編號(hào)*/
hiredate DATE NOT NULL,/*入職時(shí)間*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號(hào)*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

#工資級(jí)別表
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);

# 隨機(jī)產(chǎn)生字符串
#定義一個(gè)新的命令結(jié)束符合
delimiter $$
#刪除自定的函數(shù)
drop  function rand_string $$

#這里我創(chuàng)建了一個(gè)函數(shù). 

#rand_string(n INT) rand_string 是函數(shù)名 (n INT) //該函數(shù)接收一個(gè)整數(shù)
create function rand_string(n INT) 
returns varchar(255) #該函數(shù)會(huì)返回一個(gè)字符串
begin 
#chars_str定義一個(gè)變量 chars_str,類型是 varchar(100),默認(rèn)值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 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);

# 隨機(jī)產(chǎn)生部門編號(hào)
delimiter $$
drop  function rand_num $$

#這里我們又自定了一個(gè)函數(shù)
create function rand_num( )
returns int(5)
begin 
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
  end $$


delimiter ;
select rand_num();

#******************************************
#向emp表中插入記錄(海量的數(shù)據(jù))


delimiter $$
drop procedure insert_emp $$


#隨即添加雇員[光標(biāo)]  400w
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把a(bǔ)utocommit設(shè)置成0
 set autocommit = 0;  
 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 ;
#調(diào)用剛剛寫好的函數(shù), 1800000條記錄,從100001號(hào)開始
call insert_emp(100001,4000000);


#**************************************************************
#  向dept表中插入記錄

delimiter $$
drop procedure insert_dept $$


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 表插入數(shù)據(jù)
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 ;
#測試不需要了
#call insert_salgrade(10000,1000000);





#----------------------------------------------