数据库模拟创建数据（使用于学生成绩与学科等等）

先点赞再看

 delimiter //
CREATE PROCEDURE rea ()
  begin
 declare num int default 1;
 declare yuanxi int default 1;
 declare zhuanye int default 1;
declare nianji int default 1;
declare renshu int default 1;
set @xing = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史文陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
set @ming = '优化分布式高级网站往这均可咯偶记那就看你打火机三队春清利生丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
set @email='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
set @sex='男女';
 while num<37 do
    set nianji=4-(num/9);
    set renshu=1;
    
    while renshu<51 do
         insert into student(sno,sname,sex,age,class,phone,EMAIL)
         values((2013-nianji)*100000+num*1000+nianji*100+renshu,concat(substr(@xing,floor(rand()*length(@xing)/3+1),1), substr(@ming,floor(rand()*length(@ming)/3+1),1), substr(@ming,floor(rand()*length(@ming)/3+1),1))
        ,substr(@sex,floor(rand()*length(@sex)/3+1),1),rand()*5+17,num,(rand()*100000000000),concat(substr(@email,floor(rand()*length(@email)+1),1),substr(@email,floor(rand()*length(@email)+1),1),substr(@email,floor(rand()*length(@email)+1),1),floor(rand()*1000000),'@163.com'));
        set renshu=renshu+1;
    end while;
    set num=num+1;
 end while;
 end //


CALL repeatCourse();

declare nianji int default 4-(int)(num/9);

declare nianji int dafault (int)4-(num/9);
    declare renshu int default 1;
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
select concat(substr(@xing,floor(rand()*length(@xing)/3+1),1), substr(@ming,floor(rand()*length(@ming)/3+1),1), substr(@ming,floor(rand()*length(@ming)/3+1),1));


#创建存储过程
CREATE PROCEDURE test_insert () 
    #开始
    BEGIN
        #定义变量 
        DECLARE i INT DEFAULT 1;
            #条件判断
            WHILE i<1000000 
            #执行
            DO 
                #SQL
                INSERT SQL ;
                #变量增加
                SET i=i+1;
            #结束循环 
            END WHILE ;
        #提交 
        commit; 
    #结束
    END;
#执行
CALL test_insert();    
#删除存储过程
drop procedure test_insert ;
#查看存储过程
SHOW PROCEDURE STATUS ;
#查看创建存储过程的语句
SHOW CREATE PROCEDURE test_insert8 ;



mysql> create table student1
     (
     sno char(9),
     sname char(10),
     sex char(5),
     age int,
     class int,
     phone char(11),
     EMAIL char(50),
    primary key(sno)
    )
     //




 declare yuanxi int default 1;
 declare zhuanye int default 1;

set @xing = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史文陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
set @ming = '优化分布式高级网站往这均可咯偶记那就看你打火机三队春清利生丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
set @email='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
set @sex='男女';
/************************************************
 delimiter //
CREATE PROCEDURE re ()
  begin
 declare num int default 1;
declare nianji int default 1;
declare renshu int default 1;
declare kecheng int default 1;
declare jiaoshi int default 1;
 while num<37 do
    set nianji=4-(num/9);
    set renshu=1;
    if num=1 then set kecheng=7;set jiaoshi=2; 
    elseif num=3 then set kecheng=8; set jiaoshi=1;
    elseif num=5 then set kecheng=15;set jiaoshi=6;
    elseif num=7 then set kecheng=16;set jiaoshi=5;
    elseif num=9 then set kecheng=20;set jiaoshi=9;
    elseif num=10 then set kecheng=5;set jiaoshi=4;
    elseif num=12 then set kecheng=6;set jiaoshi=3;
    elseif num=14 then set kecheng=13;set jiaoshi=8;
    elseif num=16 then set kecheng=14;set jiaoshi=7;
    elseif num=18 then set kecheng=19;set jiaoshi=10;
    elseif num=19 then set kecheng=3;set jiaoshi=3;
    elseif num=21 then set kecheng=4;set jiaoshi=4;
    elseif num=23 then set kecheng=11;set jiaoshi=7;
    elseif num=25 then set kecheng=12;set jiaoshi=8;
    elseif num=27 then set kecheng=18;set jiaoshi=10;
    elseif num=28 then set kecheng=1;set jiaoshi=1;
    elseif num=30 then set kecheng=2;set jiaoshi=2;
    elseif num=32 then set kecheng=9;set jiaoshi=5;
    elseif num=34 then set kecheng=10;set jiaoshi=6;
    elseif num=36 then set kecheng=17;set jiaoshi=9;
    end if;
    while renshu<51 do
        
         insert into xk(sno,cno,tno,grade)
         values((2013-nianji)*100000+num*1000+nianji*100+renshu,
            kecheng,jiaoshi,floor((rand()*55)+45)
        );
        set renshu=renshu+1;
    end while;
    
    set num=num+1;
 end while;
 end //
/**********************************************




insert into yx(id,name) values(1,'信息技术与工程系'),(2,'数学系'),(3,'美术系')//

insert into zy(id,name,yx) values(1,'计算机科学与技术',1),(2,'网络工程',1),(3,'数学与应用数学',2),(4,'数据计算及应用',2),(5,'美术学',3)//

班级            
编号    名称    年级    专业
1    计科0901    4    1
2    计科0902    4    1
3    网络0901    4    2
4    网络0902    4    2
5    数学0901    4    3
6    数学0902    4    3
7    数据0901    4    4
8    数据0902    4    4
9    美术0901    4    5
10    计科1001    3    1
11    计科1002    3    1
12    网络1001    3    2
13    网络1002    3    2
14    数学1001    3    3
15    数学1002    3    3
16    数据1001    3    4
17    数据1002    3    4
18    美术1001    3    5
19    计科1101    2    1
20    计科1102    2    1
21    网络1101    2    2
22    网络1102    2    2
23    数学1101    2    3
24    数学1102    2    3
25    数据1101    2    4
26    数据1102    2    4
27    美术1101    2    5
28    计科1201    1    1
29    计科1202    1    1
30    网络1201    1    2
31    网络1202    1    2
32    数学1201    1    3
33    数学1202    1    3
34    数据1201    1    4
35    数据1202    1    4
36    美术1201    1    5


insert into class(id,name,year,zy) values
(1,'计科0901',4,1),(2,'计科0902',4,1),(3,'网络0901',4,2),(4,'网络0902',4,2),
(5,'数学0901',4,3),(6,'数学0902',4,3),(7,'数据0901',4,4),(8,'数据0902',4,4),(9,'美术0901',4,5),
(10,'计科1001',3,1),(11,'计科1002',3,1),(12,'网络1001',3,2),(13,'网络1002',3,2),
(14,'数学1001',3,3),(15,'数学1002',3,3),(16,'数据1001',3,4),(17,'数据1002',3,4),(18,'美术1001',3,5),
(19,'计科1101',2,1),(20,'计科1102',2,1),(21,'网络1101',2,2),(22,'网络1102',2,2),
(23,'数学1101',2,3),(24,'数学1102',2,3),(25,'数据1101',2,4),(26,'数据1102',2,4),(27,'美术1101',2,5),
(28,'计科1201',1,1),(29,'计科1202',1,1),(30,'网络1201',1,2),(31,'网络1202',1,2),
(32,'数学1201',1,3),(33,'数学1202',1,3),(34,'数据1201',1,4),(35,'数据1202',1,4),(36,'美术1201',1,5)
//


insert into teacher(tno,tname,sex,age,yx,phone,EMAIL) values
(1,'李静','女',34,1,17836942562,'180295623@163.com'),
(2,'张一平','男',41,1,15368462253,'Jsd164463@163.com'),
(3,'李晓芳','女',32,1,15796235548,'165161549@163.com'),
(4,'王宏文','男',45,1,18459325592,'Oko978862@163.com'),
(5,'王广济','男',49,2,17563288946,'Oll694959@163.com'),
(6,'王鹏举','男',35,2,19563284456,'Sojc62265@163.com'),
(7,'张于','男',29,2,17356988230,'Dnkwn5665@163.com'),
(8,'张丽娟','女',30,2,18635966526,'Sudh44564@163.com'),
(9,'诸文平','男',31,3,15963285596,'Aainn5466@163.com'),
(10,'孙小晓    ','女',32,3,18635962248,'Ajjjd5556@163.com')
//


insert into course(cno,cname,zy,credit,period) values
(1,'高级语言程序设计',1,2,2),
(2,'计算机发展史',2,2,2),
(3,'电路原理',1,6,3),
(4,'编译原理',2,4,3),
(5,'数据结构',1,3,5),
(6,'计算机组成原理',2,3,5),
(7,'计算机网络',1,2,2),
(8,'网络安全',2,4,2),
(9,'离散数学',3,3,2),
(10,'奥数',4,2,2),
(11,'数理逻辑',3,2,3),
(12,'集合论    ',4,3,3),
(13,'函数',3,4,5),
(14,'代数结构',4,4,5),
(15,'组合数学',3,3,2),
(16,'图论',4,3,2),
(17,'国画',5,2,2),
(18,'雕刻',5,4,3),
(19,'油画',5,4,5),
(20,'动画设计',5,3,2)
//