学生学籍管理系统简单设计（中）

五、数据库中的具体实现（SQL SERVER)

create table Student (
		sno int primary key not null,
		sname varchar(10) not null,
		sex varchar(5),
		age int,
		deptno varchar(10) not null,
		profession varchar(20) not null,
		graduation varchar(10) not null,
		tel varchar(20))
create table Teacher(
		tno int primary key not null,
		tname varchar(10) not null,
		deptno varchar(10) not null)
create table Course(
		cno int primary key not null,
		cname varchar(10) not null,
		tno int not null references teacher(tno),
		credit int not null)
create table Sc(
		sno int not null references Student(sno),
		cno int not null references Course(cno),
		grade int,
		credit int not null,
		primary key(sno,cno))
create table Manager(
		mno int not null primary key,
		mname varchar(10) not null,
		deptno varchar(20) not null)

alter table Course add cpno int ;/*向Course表添加先行课属性*/

/*向Student表插入数据*/ 
insert into Student values (201215121,'李勇','男',20,'计算机系','计算机科学与技术','大二在校',19856123311);
insert into Student values (201215122,'刘晨','女',19,'计算机系','计算机科学与技术','大二在校',19856123312);
insert into Student values (201215123,'王敏','女',18,'计算机系','嵌入式','大二在校',19856123313);
insert into Student values (201215124,'张立','男',19,'计算机系','嵌入式','大二休学',19856123314);
insert into Student values (201215125,'马超','男',21,'计算机系','嵌入式','大二休学',19856123315);


/*向Teachaer表插入数据*/
insert into Teacher values(101,'张星','计算机系');
insert into Teacher values(102,'李珊','数学系');
insert into Teacher values(103,'赵天应','计算机系');
insert into Teacher values(104,'刘田','计算机系');

/*向Course表插入数据*/
insert into Course values(1,'数据库',101,4,5);
insert into Course values(2,'数学',102,2,null);
insert into Course values(3,'信息系统',104,4,1);
insert into Course values(4,'操作系统',104,3,6);
insert into Course values(5,'数据结构',103,4,7);
insert into Course values(6,'数据处理',103,2,null);
insert into Course values(7,'PASCAL语言',101,4,6);

/*向Sc表插入数据*/
insert into Sc values(201215121,1,92,4);
insert into Sc values(201215121,2,85,2);
insert into Sc values(201215121,3,88,4);
insert into Sc values(201215122,1,82,4);
insert into Sc values(201215122,2,90,2);
insert into Sc values(201215122,3,80,4);
insert into Sc values(201215123,1,70,4);
insert into Sc values(201215123,2,75,2);
insert into Sc values(201215123,5,69,4);


/*向Manager表插入数据*/
insert into Manager values(201,'李斯','计算机系');
/*修改Student的在校，休学信息*/
/*修改学生马超的休学为在校学习*/
update Student set graduation='大二在校' where sname='马超' 

/*删除张立的基本消息*/
delete from Student where sname='张立'

/*查询所有男生的信息*/
select * from Student where sex='男'

/*查询计算机系系年龄在21岁及以下的女生的姓名及其年龄*/
select sname,age from student where sex='女' and age<=21 and deptno='计算机系';

/*查询李勇的已知成绩*/
select cname,grade from Student,Course,Sc
where Student.sno=Sc.sno and Course.cno=Sc.cno and sname='李勇'

/*查询张星老师教的课程*/
select cname from Course,Teacher where Course.tno=Teacher.tno and tname='张星'

/*查询选修了李勇同学所修的全部课程的学生的学号*/
select distinct Sc.sno from Sc,Student where not exists
(select * from Sc sc1,Student where Student.sno=sc1.sno and sname='李勇' and not exists
(select * from sc sc2 where sc2.sno=Sc.sno and sc2.cno=sc1.cno))
and Student.sno =Sc.sno and sname!='李勇'

/*查询各门课程的最高成绩的学生的姓名及其成绩*/
select cname,sc.cno,sname,sc.grade  from Course,student,sc,
(select cno,max(grade) grade from sc group by cno) t
where student.sno=sc.sno and Course.cno=Sc.cno and Sc.cno=t.cno and sc.grade=t.grade;

六、功能实现

（1）数据备份：
use master
go
backup database [test]to disk='E:\大学Program\数据库例题实践\实验课\Test\test.bak' with NOFORMAT,NOINIT,name='备份';

（2）数据恢复：
restore database USERDBfrom disk='E:\大学Program\数据库例题实践\实验课\Test\test.bak' with replace,norecovery

（3）实现学生基本情况的录入操作以及方便地对学生的各科成绩进行查询
本次课程设计，根据老师的指导，利用Java简单实现了学生学籍情况的录入以及对各科成绩查询的功能。（代码详见下篇）