学生成绩管理.sql

yi ge xue sheng cheng ji guan li xi tong de yuan ma .

create database DBResults
go
use DBResults
go
create table AdminInfo
(
 AdminId int identity(1,1) primary key,
 Users varchar(50) not null,
 Apassword varchar(50) not null,
 AdminType varchar(20) null
)
go
create table Teacher
(
 TeacherId int identity(1,1) primary key,
 Tname varchar(50) not null,
 Tpassword varchar(50) not null,
 E_mail varchar(100) null,
 Phone varchar(50) null,
 CourseName varchar(50) not null,
 Term varchar(50) null,
 Tnumber int not null
)
go
create table Student
(
 StudentId int identity(1,1) primary key,
 Snumber int not null,
 Sname varchar(50) not null,
 Spassword varchar(50) not null,
 ClassNumber varchar(50) not null,
 E_mail varchar(100) null
)
go
create table Results
(
 ResultsId int identity(1,1) primary key,
 Snumber int not null,
 Sname varchar(50) not null,
 Result smallint not null,
 CourseName Varchar(50) not null,
 Teacher varchar(50) not null,
 ClassNumber varchar(50) not null,
 Term varchar(50) not null,
 Readintime datetime default getdate() null
)
go
create table Class
(
 ClassId int identity(1,1) primary key,
 ClassNumber varchar(50) not null,
 ClassName varchar(50) null
)
go
create table Course
(
 CourseId int identity(1,1) primary key,
 CourseName varchar(50) not null
)
go

select * from AdminInfo
select * from Teacher
select * from Student
select * from Results
select * from Class
select * from Course
go 

create procedure procAdmin
@Auser varchar(50),
@Apwd varchar(50),
@Atype varchar(20)
as
insert into AdminInfo(Users,Apassword,AdminType) values(@Auser,@Apwd,@Atype)
go

create procedure procShowStudent
@Aid int
as
select Snumber,Sname,E_mail,ClassName
from Student s inner join Class c
on s.ClassNumber=c.ClassNumber
where StudentId=@Aid
go

create procedure procTeacher
@Anumber int,
@Aname varchar(50),
@Apwd varchar(50),
@Acourse varchar(50)
as
insert into Teacher(Tnumber,Tname,Tpassword,CourseName) values(@Anumber,@Aname,@Apwd,@Acourse)
go

create procedure procSelTeacher
as
select TeacherId,Tnumber,Tname,CourseName,Term,Phone,E_mail
from Teacher
go

create procedure procStudent
@Anumber int,
@Aname varchar(50),
@Apwd varchar(50),
@AClassNumber varchar(50)
as
insert into Student(Snumber,Sname,Spassword,ClassNumber) values(@Anumber,@Aname,@Apwd,@AClassNumber)
go

create procedure procResult
@Aclass varchar(50)
as 
select s.ClassNumber,s.Snumber,s.Sname,Result
from Student s left join Results r on s.Snumber=r.Snumber
where s.ClassNumber=@Aclass order by s.Snumber DESC
go

create procedure procInsertResult
@Aclass varchar(50),
@ASnumber int,
@ASname varchar(50),
@Aresult smallint,
@Acourse varchar(50),
@Aterm varchar(50),
@Ateacher varchar(50),
@Atime datetime
as
insert into Results(Snumber,Sname,Result,CourseName,Teacher,ClassNumber,Term,Readintime) values(@ASnumber,@ASname,@Aresult,@Acourse,@Ateacher,@Aclass,@Aterm,@Atime)
go

create procedure procSelResult
@Aclass varchar(50)
as
select Snumber,Sname,Result,Teacher,ClassNumber,Term,Readintime,CourseName
from Results
where ClassNumber=@Aclass
go

create proc procSelResult2
@Aclass varchar(50),
@Acourse varchar(50)
as
select Snumber,Sname,Result,Teacher,ClassNumber,Term,Readintime,CourseName
from Results
where ClassNumber=@Aclass and CourseName=@Acourse order by Snumber ASC
go

create procedure procSelStudent
as
select StudentId,Snumber,Sname,ClassNumber,E_mail
from Student
go