学生成绩数据库设计 三 模拟数据

1 基础数据

 1 /*一 模拟数据说明：从2000年到当年，每年添加100个学生*/ 2 Declare @StuCount int, /*每年添加的数量*/ 3         @StartYear int,/*初始年份*/ 4         @CurYear int    /*当前年份*/ 5 Begin 6     /*设置添加数据的初始值*/ 7     SET @StuCount=100 8     SET @StartYear=2010 9     SET @CurYear=YEAR(GETDATE())10     11     /*1 向学年表添加数据*/12     Declare @XnKaishi INT /*开始年份*/13     SET @XnKaishi=@StartYear14     WHILE(@XnKaishi<=@CurYear)15         BEGIN16             IF NOT EXISTS(SELECT 1 FROM SchoolYear WHERE SyStartYear=@XnKaishi AND SyEndYear=@XnKaishi+1)17                 begin18                     insert into SchoolYear(SyStartYear,SyEndYear) values(@XnKaishi,@XnKaishi+1)19                 end20             SET @XnKaishi=@XnKaishi+121         END22     23     24     /*2 向学生表中添加数据*/25     Declare @temSc int, /*记录当前的学生ID*/26             @temXueHao NVARCHAR(12),/*当前学生的编号*/27             @temXm nvarchar(20),/*学生的姓名*/28             @temSy int  /*记录当前的年份*/29     set @temSy=@StartYear30     /*循环年份*/31     WHILE(@temSy<=@CurYear)32         BEGIN33             /*循环添加该年份的学生*/34             set @temSc=135             while(@temSc<=@StuCount)36                 begin37                     set @temXueHao=CONVERT(varchar(4),@temSy)38                                   +'-'39                                   +CONVERT(varchar(4),@temSc)40                     if not exists(select 1 from Student where StuNO=@temXueHao)41                         begin42                             set @temXm='学生'+CONVERT(varchar(4),@temSc)43                             INSERT INTO Student(StuNO,StuName,StuJoinYear) VALUES(@temXueHao,@temXm,@temSy)44                         end45                     set @temSc=@temSc+146                 end47             SET @temSy=@temSy+148         END        49 End50 51 GO52 53 /*二 模拟科目*/54 Declare @KcCount int /*课程数量*/55 BEGIN56     SET @KcCount=2057     DECLARE @i int,58             @kcMc nvarchar(30)59     set @i=160     while(@i<=@KcCount)61         begin62             set @kcMc='课程'+CONVERT(varchar(2),@i)63             if not exists(select 1 from Course where CourseName=@kcMc)64                 begin65                     insert into Course(CourseName) values(@kcMc)66                 end67             set @i=@i+168         end69 END70 GO71 72 /*三 班级模拟：暂定三个年级，每个年级有三个班级*/73 Declare @GradeNo int=1,/*年级编号*/74         @ClassNo int=1,/*班级编号*/75         @GcName  nvarchar(10)/*年级班级名称*/76 BEGIN77     WHILE(@GradeNo<=3)78         BEGIN79             SET @ClassNo=180             WHILE(@ClassNo<=3)81                 BEGIN82                     SET @GcName=CONVERT(varchar(1),@GradeNo)+'年级'+CONVERT(varchar(1),@ClassNo)+'班级'83                     if not exists(select 1 from GradeClass where GradeNo=@GradeNo and ClassNo=@ClassNo)84                         begin85                             INSERT INTO GradeClass(GradeNo,ClassNo,GcName) values(@GradeNo,@ClassNo,@GcName)86                         end87                     SET @ClassNo=@ClassNo+188                 END89             set @GradeNo=@GradeNo+190         END91 END92 GO

2 注册课程数据

  1 /*2 模拟CourseRegist（课程注册表）的数据*/  2   3 --Declare @MixRegCount int=5,/*每学年允许的最小注册课程数*/  4 --        @MaxRegCount int=12/*每学年运行的最大注册课程数*/  5 BEGIN  6     /*1 临时表：#TemPReg用于中间的数据*/  7     if   object_id('tempdb..#TempReg')   is   not   null   8           BEGIN  9             drop   table   #TempReg 10           END 11     SELECT * INTO #TempReg FROM CourseRegist where 1=2 12      13     INSERT INTO #TempReg(StuNO,SyID,CourseID) 14     SELECT Student.StuNO, 15             SchoolYear.SyID, 16             Course.CourseID 17     FROM Student 18     CROSS JOIN SchoolYear 19     CROSS JOIN Course; 20      21     /*2 删除掉非法的数据*/ 22     DELETE  #TempReg from #TempReg T1 23         WHERE EXISTS 24         ( 25             SELECT * FROM  26             ( 27                 /*这些记录都是不合法的记录*/ 28                 SELECT T.CRID, /*成绩表ID*/ 29                 T.StuNO,/*学号*/ 30                 Student.StuJoinYear,/*入学年份*/ 31                 SchoolYear.SyStartYear,/*学年开始年份*/ 32                 SchoolYear.SyEndYear /*学年结束年份*/ 33         FROM #TempReg T 34         LEFT JOIN Student on Student.StuNO=T.StuNO 35         LEFT JOIN SchoolYear ON SchoolYear.SyID=T.SyID 36         where Student.StuJoinYear>SchoolYear.SyStartYear /*入学年份大于学年开始年份*/ 37                 OR SchoolYear.SyStartYear>YEAR(GETDATE())/*该学年还没有到*/ 38                 /*该学年还没有过完。新学年从本年的9月1号到第二年的6月30号*/ 39                 OR(GETDATE() BETWEEN  40                              CONVERT(datetime,convert(varchar(4),SchoolYear.SyStartYear)+'-09-01') 41                              AND  42                              CONVERT(datetime,convert(varchar(4),SchoolYear.SyEndYear)+'-06-30') 43                    ) 44                 OR SchoolYear.SyStartYear>=Student.StuJoinYear+3/*学生入学年限已到了3年，即已毕业了*/ 45             ) T2  46             WHERE T2.CRID=T1.CRID 47         ); 48          49     /*3 随机删除注册信息：之所以做这一步是因为并非每个学生都注册所有的课程*/ 50     DECLARE @SyID int, 51             @StuNO NVARCHAR(12), 52             @topNum int,/*随机取出的课程数量*/ 53             @DeleteSql varchar(2000) /*用于随机删除的语句*/ 54     /*第一个游标CUR_SY：用于取学年ID*/ 55     DECLARE CUR_SY CURSOR FOR SELECT SyID FROM SchoolYear 56     open CUR_SY 57     fetch next from CUR_SY into @SyID 58      59     while (@@fetch_status=0) 60         BEGIN 61             /*第二个游标CUR_STU：用于取学生表编号*/ 62             DECLARE CUR_StuNO CURSOR FOR SELECT StuNO FROM Student 63             open CUR_StuNO 64             fetch next from CUR_StuNO into @StuNO 65              66             while(@@fetch_status=0) 67                 begin 68                     IF EXISTS(SELECT 1 FROM #TempReg WHERE StuNo=@StuNO and Syid=@SyID) 69                         BEGIN 70                             /*随机删除注册课程*/ 71                             select @topNum=cast(ceiling(rand() * (SELECT COUNT(1) FROM Course)) as int) 72                             SET @DeleteSql=N'DELETE #TempReg ' 73                                        +N' WHERE StuNo='''+@StuNO 74                                        +''' and Syid='+convert(varchar(2),@SyID) 75                                        +' AND CourseID not IN (SELECT TOP '+convert(varchar(2),@topNum)+' CourseID FROM Course ORDER BY NEWID())' 76                             exec (@DeleteSql) 77                         END 78                     fetch next from CUR_StuNO into @StuNO 79                 end 80             close CUR_StuNO 81             deallocate CUR_StuNO 82              83             fetch next from CUR_SY into @SyID 84         END 85     close CUR_SY 86     deallocate CUR_SY 87      88     /*4 把处理好的临时表信息放到物理表中*/ 89     Merge into CourseRegist CR 90         using  91         ( 92             select StuNO, 93                     SyID, 94                     CourseID 95             from #TempReg 96         ) T 97         ON CR.StuNO=T.StuNO 98             AND CR.SyID=T.SyID 99             AND CR.CourseID=T.CourseID100         WHEN NOT MATCHED THEN INSERT (StuNO,SyID,CourseID) VALUES(T.StuNO,T.SyID,T.CourseID);101 END

3 成绩数据

 1 /*三 模拟成绩*/ 2  3 /*模拟成绩要注意： 4 1 成绩都是随机生成的，使用floor(rand()*100) 5 2 当前假设学生经历3个学年需要毕业，所以只能在3个学年有成绩。 6   如入学年份2012年，则只能在2012-2013学年，2013-2014学年，2014-2015学年有成绩 7   但当前2014-2015学年还没有过完，所以2014-2015学年亦没有成绩 8 3 该表中的学号、课程ID、学年ID来自于CourseRegist表 9 */10 Merge into Score USING11 (12     SELECT StuNO,13         SyID,14         CourseID,15         CASE 16         /*超过一百分则使用100分减去40范围之内的随机数，保证分数在60至100之间*/17         WHEN ScoreValue>=100 THEN 100-cast(ceiling(rand(checksum(newid()))*40) as int)    18         ELSE ScoreValue19         END AS ScoreValue20     FROM21     (22         /*组合最初的数据*/    23         SELECT StuNO,SyID,CourseID,24                 /*之所以随机数乘以500，是为了避免有过多的不及格的分数*/25                cast(ceiling(rand(checksum(newid()))*500) as int) AS ScoreValue,26                /*KeepFlag：保留标记位。*/27                cast(ceiling(rand(checksum(newid()))*10) as int) AS KeepFlag    28         FROM CourseRegist29     ) A where KeepFlag>1    /*保留十分之九的数据*/30 ) B31 ON Score.StuNO=B.StuNO 32     AND Score.SyID=B.SyID 33     AND Score.CourseID=B.CourseID 34 WHEN NOT MATCHED T