高校学籍管理系统

高校学籍管理系统

功能要求：

实现学生信息 、班级、院系、专业等的管理；

实现课程、学生成绩信息管理；

实现学生的奖惩信息管理；

创建规则用于限制性别项只能输入“男”或“女”；

创建视图查询各个学生的学号、姓名、班级、专业、院系；

创建存储过程查询指定学生的成绩单；

创建触发器当增加、删除学生和修改学生班级信息时自动修改相应班级学生人数；

建立数据库相关表之间的参照完整性约束。

er图

长方形90x40

椭圆80x50

菱形 55x50

思路

根据管理系统功能要求需要创建对应的表
考虑到以后以后数据量会变得庞大，达到表存储瓶颈使用分表方式

创建数据库并指定默认字符集

1
create database yyt default character set utf8mb4;

创建学校院系表，专业表，班级表，学生表,课程表，成绩表。

当中设置对应的主键外键关系，并设置了所有表中id字段为表的主键，唯一且自增，学生表中性别选用enum数据类型只允许选男，女，每个表都有对应的字段与别的表存在外键关系并添加级联删除和级连更新，指定表的默认字符集为utf8mb4（MySQL官方推荐）。

表的结构设计如下：

院系表结构：id-name

  • 分别对应：院系id-院系名称
1
create table yuanxi(id int primary key auto_increment,name varchar(30) not null) default charset=utf8mb4 comment='院系表';

专业表结构（major）：id-name-y_id

  • 分别对应专业id-专业名称-院系id
1
create table major(id int primary key auto_increment,name varchar(30) not null,y_id int,constraint yuanxi_major_id foreign key(y_id) references yuanxi(id) on update cascade on delete cascade) default charset=utf8mb4 comment='专业表';

**班级表结构(class)**：id-name-su-z_id

  • 分别对应：班级id-班级名称-总人数-专业id
1
create table class(id int primary key auto_increment,name varchar(20) not null,su int,z_id int,constraint major_class_id foreign key(z_id) references major(id) on update cascade on delete cascade)default charset=utf8mb4 comment='班级表';

**学生表结构(student)**：id-name-sex-c_id

  • 分别对应：学生id-学生名称-性别-班级id
1
create table student(id int primary key auto_increment,name varchar(20) not null,sex enum('男','女'),c_id int,constraint class_student_id foreign key(c_id) references class(id) on update cascade on delete cascade)default charset=utf8mb4 comment='学生表';

课程表结构(lesson): id-name

  • 分别对应：课程号-课程名
1
create table lesson(id int primary key auto_increment,name varchar(20) not null)default charset=utf8mb4 comment='课程表';

成绩表构(score): student_id-lesson_id-score

  • 分别对应：学生id-课程号-成绩
1
create table score(student_id int,lesson_id int,score int(3),constraint student_score_id foreign key(student_id) references student(id) on update cascade on delete cascade,constraint lesson_score_id foreign key(lesson_id) references lesson(id) on update cascade on delete cascade)default charset=utf8mb4 comment='成绩表';

学籍信息表（sl）：stud_id-sta-yea

  • 分别对应：学号-学籍状态-年制
1
create table sl(stud_id int,sta enum('注册','毕业','保留') not null,yea enum('二','三','五') not null,constraint  sl_stud_id foreign key(stud_id) references student(id) on update cascade on delete cascade)default charset=utf8mb4 comment='学籍信息表';

学籍日志表（sl_log）: set_id-set_time-set_type-set_c

  • 分别对应:操作id(对应学生id)-操作时间-操作类型-操作内容

创建表

1
create table sl_log(set_id int,set_time datetime not null,set_type varchar(20) not null comment 'update\delete',set_c varchar(500)) default charset=utf8mb4 comment='学籍日志表';

奖惩信息表（re）：id-re_id-re_jname-re_cname

  • 分别对应：id-学号-奖励信息-惩罚信息
1
create table re(id int primary key auto_increment,re_id int,re_jname varchar(300),re_cname varchar(300),constraint student_re_id foreign key(re_id) references student(id) on update cascade on delete cascade)default charset=utf8mb4 comment='奖惩信息表';

添加模拟数据

1
2
3
4
5
6
7
8
9
10
11
insert into yuanxi(name) values('电子信息工程'),('机电工程');

insert into major(name,y_id) values('云计算技术应用',1),('电子技术应用',2),('人工智能',1),('无人机',2);

insert into class(name,z_id) values('云计算技术应用2101',1),('云计算技术应用2102',1),('电子技术应用2021',2),('人工智能2001',3),('人工智能2002',3),('无人机01',4);

insert into student(name,sex,c_id) values('杨大','男',1),('杨二','女',2),('杨三','男',1),('李大','男',3),('王大','女',4),('王二','男',5),('刘大','男',6),('刘二','女',6);

insert into lesson(name) values('数据库管理'),('新时代特色社会主义'),('分布式系统'),('机器学习'),('Linux基础'),('创业基础'),('声明科学与人类文明');

insert into score values(1,1,99),(1,2,98),(1,5,95),(1,7,100),(2,1,100),(2,2,99),(2,5,99),(2,6,100),(4,6,90),(4,7,99);

功能

实现学生信息 、班级、院系、专业等的管理

管理基本就是增删改的操作，这个跟之前创表的表结构相关,id字段设计表的时候已经是自增，方便管理，所以id不支持修改，添加字段时也不需要填写字段的id

根据表结构班级信息，表内包含这个班级学生的总人数，后面创建的有对应的触发器，会实时更新班级人数信息，所以不建议对班级总人数字段进行修改，所以在对班级信息的管理，新增时不能提前设定和修改时不能修改总人数字段，新增时字段默认为null，插入数据后会自动更新数据

增加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
create procedure y1(in b int,in b1 varchar(30),in b2 int,in b3 int)
begin
	declare sex varchar(3);
	case
		when b = 1 then
			insert into yyt.yuanxi(name) values(b1);
			select *from yyt.yuanxi where name=b1;
		when b = 2 then
			insert into yyt.major(name) values(b1);
			select *from yyt.major where name=b1;
		when b = 3 then
			insert into yyt.class(name,z_id) values(b1,b2);
			select *from yyt.class where name=b1;
		when b = 4 then
			if b2 = 1 then
            		set sex :='男';
					insert into yyt.student(name,sex,c_id) values(b1,sex,b3);
					select *from yyt.student where name=b1;
				elseif b2 = 2 then
					set sex :='女';
					insert into yyt.student(name,sex,c_id) values(b1,sex,b3);
					select *from yyt.student where name=b1;
				else
					select '非法参数！请按照要求输入参数！';
				end if;
		else
			select '非法参数！请按照要求输入参数！';
	end case;
end$$


## 介绍
#create procedure y1(in b int,in b1 varchar(30),in b2 int,in b3 int)
其中有四个参数，分别对应b,b1,b2,b3
其中b有四个值分别对应1，2，3，4
1 表示院系表
2 表示专业表
3 表示班级表
4 表示学生信息表
b1 对应表字段中的名称
b2 当b的值为3的时候也就是增加班级信息表字段的时候，b2的值表示的是班级对应的专业的id，当引用存储过程时b参数的值为4时，表示增加学生信息表字段的性别，1为男，2为女
b3 表示增加学生信息表内容时对应的班级id(请务必选择班级表中存在的班级id)
b2和b3只有在添加学生信息表的时候用到，其他表需要将这两个参数为任意数字，不参与使用

删除

注意：创建表的时候每个表之间都有相应的外键关系，并添加了级联删除和级连更新，谨慎操作！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create procedure y2(in c int,in c2 int)
begin
	case
		when c = 1 then
			delete from yyt.yuanxi where id=c2;
			select *from yyt.yuanxi;
		when c = 2 then
			delete from yyt.major where id=c2;
			select *from yyt.major;
		when c = 3 then
			delete from yyt.class where id=c2;
			select *from yyt.class;
		when c = 4 then
			delete from yyt.student where id=c2;
			select *from yyt.student;
		else
			select '非法参数！请按照要求输入参数！';
	end case;
end$$


## 介绍
#create procedure y2(in c int,in c2 int)
其中c有四个值分别对应1，2，3，4
1 表示院系表
2 表示专业表
3 表示班级表
4 表示学生信息表
c2 对应各个表中的id主键

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
create procedure y3(in a int,in a1 int,in a2 varchar(30),in a3 int)
begin
	declare sex varchar(3);
	case
		when a = 1 then
			update yyt.yuanxi set name=a2 where id=a1;
			select *from yyt.yuanxi where id=a1;
		when a = 2 then
			update yyt.major set name=a2 where id=a1;
			select *from yyt.major where id=a1;
		when a = 3 then
			update yyt.class set name=a2,z_id=a3 where id=a1;
			select *from yyt.class where id=a1;
		when a = 4 then
            	if a3 = 1 then
            		set sex :='男';
					update yyt.student set name=a2,sex=sex where id=a1;
					select *from yyt.student where id=a1;
				elseif a3 = 2 then
					set sex :='女';
					update yyt.student set name=a2,sex=sex where id=a1;
					select *from yyt.student where id=a1;
				else
					update yyt.student set name=a2 where id=a1;
					select *from yyt.student where id=a1;
				end if;
		else
			select '非法参数！请按照要求输入参数！';
	end case;
end$$

## 介绍！
# create procedure y3(in a int,in a1 int,in a2 varchar(30),in a3 int)里面定义了四个参数，a，a1，a2，a3
其中a有四个值分别对应1，2，3，4
1 表示院系表
2 表示专业表
3 表示班级表
4 表示学生信息表
a1 对应的就是各个表内字段的id
a2 对应的就是表内字段的名字是varchar类型需要用''包裹起来
a3 有三个值分别为数字1，2，任意数字;(a3额外值，当a的值为3的时候也就是修改班级表的时候，a3表示的是班级对应的专业的id)
## 引用例子
例如需要修改专业表内id为4的专业名称，使用
call y3(2,4,'无人机技术',1)
其中2表示要修改的是专业表，4表示id为4，引号内的为name字段的值，最后的1由于修改使用不到可以使用任意数字，不参与使用；

例如需要修改学生信息表中id为1的学生的名称
call y3(4,1,'杨1',3或任意数字)
其中4表示要修改的是学生表，1表示id为1，引号内的为name字段的值，最后的3表示只修改学生的名字，如果改为1表示修改性别为男，2表示为女；

实现课程、学生成绩信息管理

  • 此存储过程包含对两个表的增删改操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create procedure y6(y int,y1 int,y2 int,y3 int,y4 int,y5 varchar(30))
begin
	case
		when y = 1 then
			if y1 = 1 then insert into yyt.lesson(name) values(y5); select *from yyt.lesson where name=y5;
			elseif y1 = 2 then insert into yyt.score values(y2,y3,y4); select *from yyt.score where student_id=y2 && lesson_id=y3;
			end if;
		when y = 2 then
			if y1 = 1 then delete from yyt.lesson where id=y2; select *from yyt.lesson;
			elseif y1 = 2 then delete from  yyt.score where student_id=y2 && lesson_id=y3; select *from yyt.score;
			end if;
		when y = 3 then
			if y1 = 1 then update yyt.lesson set name=y5 where id=y2; select *from yyt.lesson where name=y5;
			elseif y1 = 2 then update yyt.score set score=y4 where student_id=y2 && lesson_id=y3; select *from yyt.score where student_id=y2 && lesson_id=y3;
			end if;
	end case;
end$$		


# 调用方法create procedure y6(y int,y1 int,y2 int,y3 int,y4 int,y5 varchar(30))
其中y有三个值1，2，3 分别对应增删改功能
y1 有两个值 1，2 分别对应课程表跟成绩表
y2 动态使用
y3 动态使用
y4 动态使用
y5特殊需要用引号包含，y5是:当参数开头 y=1 or y=3 时表示增加课程或者修改课程名字，在这两个中必填

实现学生的奖惩信息管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
create procedure y7(y int,y1 int,y2 int,y3 varchar(300),y4 varchar(300))
begin
	case
		when y = 1 then
			if y1 = 1 then
				insert into yyt.re(re_id,re_jname) values(y2,y3);
				select *from yyt.re where re_id=y2;
			elseif y1 = 2 then
				insert into yyt.re(re_id,re_cname) values(y2,y3);
				select *from yyt.re where re_id=y2;
			elseif y2 = 3 then
				insert into yyt.re(re_id,re_jname,re_cname) values(y2,y3,y4);
				select *from yyt.re where re_id=y2;
			end if;
		when y = 2 then
			delete from yyt.re where id=y1;
		when y = 3 then
			if y1 = 1 then
				update yyt.re set re_jname=y3 where id=y2;
				select *from yyt.re where id=y2;
			elseif y1 = 2 then
				update yyt.re set re_cname=y3 where id=y2;
				select *from yyt.re where id=y2;
			elseif y1 = 3 then
				update yyt.re set re_jname=y3,re_cname=y4 where id=y2;
				select *from yyt.re where id=y2;
			end if;
	end case;
end$$

# 增加
当y等于1，y1等于1时表示添加一条奖励信息y2表示学生的学号，y3为奖励信息
当y等于1，y1等于2时表示添加一条惩罚信息y2表示学生的学号，y3为惩罚信息，y4随便加内容用引号包含，不会引用
当y等于1，y1等于3时表示添加一条奖励和惩罚信息y2表示学生的学号，y3为奖励信息，y4为惩罚信息

# 删除
当y=2时表示要删除一条奖惩信息，y1表示奖惩信息表的主键id，需要单独删除某个学生的奖励信息或惩罚信息需要用到修改

# 修改
当y等于3，y1等于1时表示修改一条奖励信息，y2表示奖惩信息表的主键id，y3为奖励信息
当y等于1，y1等于2时表示修改一条惩罚信息，y2表示奖惩信息表的主键id，y3为惩罚信息，y4随便加内容用引号包含，不会引用
当y等于1，y1等于3时表示修改一条奖励和惩罚信息，y2表示奖惩信息表的主键id，y3为奖励信息，y4为惩罚信息

通过学生id查询学生的所有奖惩信息

1
2
3
4
create procedure y8(y int)
begin
	select a.name 院系,b.name 专业,c.name 班级,e.re_id 学号,d.name 姓名,e.re_jname 奖励信息,e.re_cname 惩罚信息 from yuanxi a,major b,class c,student d,re e where  e.re_id=d.id && d.c_id=c.id && c.z_id=b.id && b.y_id=a.id && e.re_id=y;
end$$

image-20221210164607209

创建视图查询各个学生的学号、姓名、班级、专业、院系

1
2
3
create view zong as select a.id 学生id,a.name 学生姓名,b.name 班级名称,c.name 专业名称,d.name 院系名称 from student a,class b,major c,yuanxi d where a.c_id=b.id&&b.z_id=c.id&&c.y_id=d.id;

select *from zong;

select a.name 学生姓名,b.name 班级名,c.name 课程名,d.score 成绩from student a,class b,lseeon c,score d where d.student_id=a.id && d.class_id=b.id && d.lesson_id=c.id && a.c_id=b.id;

select e.name,f.name,a.name,b.name,c.name,d.score from class a,student b,lesson c,score d,yuanxi e,major f where b.c_id=a.id && d.student_id=b.id && d.lesson_id=c.id &&a.z_id=f.id && f.y_id=e.id;

创建存储过程查询指定学生的成绩单

  • 根据学生的id查询学生所参加的所有考试的成绩
1
2
3
4
5
6
create procedure y5(in s int)
begin
	select e.name 学院,f.name 专业,a.name 班级,b.name 姓名,c.name 课程,d.score 成绩 from class a,student b,lesson c,score d,yuanxi e,major f where b.c_id=a.id && d.student_id=b.id && d.lesson_id=c.id &&a.z_id=f.id && f.y_id=e.id && d.student_id=s;
end$$

# s表示学生id
image-20221207194206442

创建触发器当增加、删除学生和修改学生班级信息时自动修改相应班级学生人数

添加，自动修改各相应班级总人数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
方法1:

create trigger sun_h
	after insert on yyt.student for each row
begin
	update class set su=(select * from(select count(a.id) from class a,student b where b.c_id=a.id && a.id=new.c_id) a) where id=new.c_id;
end$$

方法2：

create trigger sun_h
	after insert on yyt.student for each row
begin
	declare cc int;
	select count(a.id) into cc from class a,student b where b.c_id=a.id && a.id=new.c_id;
	update class set su=cc where id=new.c_id;
end$$


# 当对学生信息表进行添加数据的时候会触发

删除,自动修改各相应班级总人数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
方法1:

create trigger sun_d
	after delete on yyt.student for each row
begin
	update class set su=(select * from(select count(a.id) from class a,student b where b.c_id=a.id && a.id=old.c_id) a) where id=old.c_id;
end$$

方法2：

create trigger sun_d
	after delete on yyt.student for each row
begin
	declare aa int;
	select count(a.id) into aa from class a,student b where b.c_id=a.id && a.id=old.c_id;
	update class set su=aa where id=old.c_id;
end$$


# 当对学生信息表进行删除数据的时候会触发

修改,自动修改各相应班级总人数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
方法1:

create trigger sun_u
	after update on yyt.student for each row
begin
	update class set su=(select * from(select count(a.id) from class a,student b where b.c_id=a.id && a.id=old.c_id) a) where id=old.c_id;
	update class set su=(select * from(select count(a.id) from class a,student b where b.c_id=a.id && a.id=new.c_id) a) where id=new.c_id;
end$$

方法2：

create trigger sun_d
	after update on yyt.student for each row
begin
	declare aa int;
	select count(a.id) into aa from class a,student b where b.c_id=a.id && a.id=old.c_id;
	update class set su=aa where id=old.c_id;
	select count(a.id) into aa from class a,student b where b.c_id=a.id && a.id=new.c_id;
	update class set su=aa where id=new.c_id;
end$$


# 当对学生信息表进行删除数据的时候会触发

学籍管理,保留日志

学籍信息表（sl）结构：stu_id-学籍状态-年制

创建视图，查询所有学生的学籍状态

1
2
3
create or replace view sl_st as select a.name 院系,b.name 专业,c.name 班级,d.id 学号,d.name 姓名,e.sta 学籍,e.yea 年制 from yuanxi a,major b,class c,student d,sl e where e.stud_id=d.id && d.c_id=c.id && c.z_id=b.id && b.y_id=a.id;

# 创建一个视图sl_st
image-20221208221313982

管理学籍信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create procedure y9(y int,y1 int,y2 varchar(10),y3 varchar(10),y4 int)
begin
	case
		when y = 1 then
			insert into yyt.sl values(y1,y2,y3);
			select *from yyt.sl where stud_id=y1;
		when y = 2 then
			delete from yyt.sl where stud_id=y1;
			select *from yyt.sl where stud_id=y1;
		when y = 3 then
			if y4 = 1 then
				update yyt.sl set sta=y2 where stud_id=y1;
				select *from yyt.sl where stud_id=y1;
			elseif y4 = 2 then
				update yyt.sl set yea=y2 where stud_id=y1;
				select *from yyt.sl where stud_id=y1;
			elseif y4 = 3 then
				update yyt.sl set sta=y2,yea=y3 where stud_id=y1;
				select *from yyt.sl where stud_id=y1;
			end if;
	end case;
end$$

# y有三个值1，2，3分别对应增删改
# y1表示学生学号
# y2，当y4值为1时表示的是要修改学籍的状态，y2为状态值，y4为2时 y2表示年制
# y3，当y为1时y3就表示年制，当y为1，y4为3的时候，y3也表示年制

根据对学籍信息表的管理，其中学籍的修改和删除属于高危操作。创建触发器，当对表进行修改或删除时，自动触发并记录操作信息到日志表

修改操作时触发

1
2
3
4
5
create trigger update_sl
	after update on yyt.sl for each row
begin
	insert into  yyt.sl_log(set_id,set_time,set_type,set_c) values(old.stud_id,now(),'update',concat('更新之前的内容为：stud_id= ',old.stud_id,',sta= ',old.sta,',yea= ',old.yea,' | 更新之后的内容为：stud_id= ',new.stud_id,',sta= ',new.sta,',yea= ',new.yea));
end$$

删除操作时触发

1
2
3
4
5
create trigger delete_sl
	after delete on yyt.sl for each row
begin
	insert into  yyt.sl_log(set_id,set_time,set_type,set_c) values(old.stud_id,now(),'delete',concat('删除之前的内容为：stud_id= ',old.stud_id,',sta= ',old.sta,',yea= ',old.yea));
end$$

实现效果演示图：

image-20221208235610992