时间:2021-07-01 10:21:17 帮助过:23人阅读
- CREATE TABLE emp( <span style="color: #008000">/*</span><span style="color: #008000">EMP雇员表</span><span style="color: #008000">*/</span><span style="color: #000000">
- empno MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0,<span style="color: #008000">/*</span><span style="color: #008000">编号</span><span style="color: #008000">*/</span><span style="color: #000000">
- ename VARCHAR(</span>20) NOT <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> "",<span style="color: #008000">/*</span><span style="color: #008000">名字</span><span style="color: #008000">*/</span><span style="color: #000000">
- job VARCHAR(</span>9) NOT <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> "",<span style="color: #008000">/*</span><span style="color: #008000">工作</span><span style="color: #008000">*/</span><span style="color: #000000">
- mgr MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0,<span style="color: #008000">/*</span><span style="color: #008000">是哪个及编号</span><span style="color: #008000">*/</span><span style="color: #000000">
- hiredate </span><span style="color: #008080">DATE</span> NOT <span style="color: #0000ff">NULL</span>,<span style="color: #008000">/*</span><span style="color: #008000">入职时间</span><span style="color: #008000">*/</span><span style="color: #000000">
- sal DECIMAL(</span>7,2) NOT <span style="color: #0000ff">NULL</span>,<span style="color: #008000">/*</span><span style="color: #008000">薪水</span><span style="color: #008000">*/</span><span style="color: #000000">
- comm DECIMAL(</span>7,2) NOT <span style="color: #0000ff">NULL</span>,<span style="color: #008000">/*</span><span style="color: #008000">红利</span><span style="color: #008000">*/</span><span style="color: #000000">
- deptno MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0 <span style="color: #008000">/*</span><span style="color: #008000">部门编号</span><span style="color: #008000">*/</span><span style="color: #000000">
- )ENGINE</span>=MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET=utf8
- CREATE TABLE salgrade( <span style="color: #008000">/*</span><span style="color: #008000">工资级别表</span><span style="color: #008000">*/</span><span style="color: #000000">
- grade MEDIUMINT UNSIGNED NOT </span><span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span> 0,<span style="color: #000000">
- losal DECIMAL(</span>17,2) NOT <span style="color: #0000ff">NULL</span>,<span style="color: #000000">
- hisal DECIMAL(</span>17,2) NOT <span style="color: #0000ff">NULL</span><span style="color: #000000">
- )ENGINE</span>=MyISAM <span style="color: #0000ff">DEFAULT</span> 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 $$
- <span style="color: #008000">#</span><span style="color: #008000">rand_string(n INT) rand_string 是函数名(n INT) //该函数传参一个整数</span>
- create <span style="color: #0000ff">function</span><span style="color: #000000"> rand_string(n INT)
- returns varchar(</span>255<span style="color: #000000">)
- begin
- </span><span style="color: #0000ff">declare</span> chars_str varchar(100) <span style="color: #0000ff">default</span>
- ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789‘<span style="color: #000000">;
- </span><span style="color: #0000ff">declare</span> return_str varchar(255) <span style="color: #0000ff">default</span> ‘‘<span style="color: #000000">;
- </span><span style="color: #0000ff">declare</span> i int <span style="color: #0000ff">default</span> 0<span style="color: #000000">;
- </span><span style="color: #0000ff">while</span> i < n <span style="color: #0000ff">do</span><span style="color: #000000">
- set return_str</span>=concat(return_str,substring(chars_str,<span style="color: #008080">floor</span>(1+<span style="color: #008080">rand</span>()*52),1<span style="color: #000000">));
- set i </span>= i +1<span style="color: #000000">;
- </span><span style="color: #008080">end</span> <span style="color: #0000ff">while</span><span style="color: #000000">;
- </span><span style="color: #0000ff">return</span><span style="color: #000000"> return_str;
- </span><span style="color: #008080">end</span> $$
- create <span style="color: #0000ff">function</span><span style="color: #000000"> rand_num( )
- returns int(</span>5<span style="color: #000000">)
- begin
- </span><span style="color: #0000ff">declare</span> i int <span style="color: #0000ff">default</span> 0<span style="color: #000000">;
- set i </span>= <span style="color: #008080">floor</span>(10+<span style="color: #008080">rand</span>()*500<span style="color: #000000">);
- </span><span style="color: #0000ff">return</span><span style="color: #000000"> i;
- </span><span style="color: #008080">end</span> $$
- create procedure insert_emp(in start int(10),in max_num int(10<span style="color: #000000">))
- begin
- </span><span style="color: #0000ff">declare</span> i int <span style="color: #0000ff">default</span> 0<span style="color: #000000">;
- </span><span style="color: #008000">#</span><span style="color: #008000">set autocommit = 0 把autocommit设置成0</span>
- set autocommit = 0<span style="color: #000000">;
- repeat
- set i </span>= i +1<span style="color: #000000">;
- insert into emp values ((start</span>+i),rand_string(6),‘SALESMAN‘,0001,curdate(),2000,400,<span style="color: #000000">rand_num());
- until i </span>=<span style="color: #000000"> max_num
- </span><span style="color: #008080">end</span><span style="color: #000000"> repeat;
- commit;
- </span><span style="color: #008080">end</span> $$
- delimiter ;
- call insert_emp(100001,4000000);
Mysql利用存储过程插入400W条数据
标签:his 定义 style arc 命令 drop proc 数据 调用