数据库学生选课系统代码设计

1、完整的SQL语句create database S_Cuse S_Ccreate table Student( StuID char(8) not null primary key , StuName varchar(10)not null, Tel varchar(13), Sage smallint, Ssex char(2)default 男 check (Ssex in (男,女), Semail varchar(20), Sintday datetime, GradeID char(6) not null, Snation varchar(20), Sadd varchar(40),

2、)create table Grade( GradeID char(6) not null primary key, GName varchar(20) not null, GNum int not null, Gassist varchar(10), Gdept varchar(20), Gacademy varchar(20) )select *from Gradecreate table Teacher( TID char(4) not null primary key, TName varchar(10) not null, Temail varchar(20), Tsex char(

3、2), Tnation varchar(20), Tadd varchar(20), Ttel varchar(13)create table Course( CID char(6) not null primary key, CName varchar(20)not null, Cnum varchar(2), Ccapa varchar(3), Cdate datetime, Croom char(5), C_XKL varchar(6)create table Manager( MID char(6) not null primary key, MName varchar(10) not

4、 null, Msex char(2), Mtel varchar(13), Madd varchar(40), Memil varchar(20) create table SC( SCID char(5) not null primary key, StuID char(8) not null, TID char(4), CID char(6) not null , Credit smallint, check ( Credit between 0 and 100), foreign key (StuID) references Student(StuID), foreign key (T

5、ID) references Teacher(TID), foreign key (CID) references Course(CID) ) insert into Studentvalues(,乐乐,20,女,,2010-09-20,汉,广东河源)insert into Studentvalues(,陈红,20,女,,2010-09-20,汉,广东佛山)insert into Studentvalues(,大树,20,男,,2010-09-20,回,广东汕头)insert into Student(StuID,StuName,Tel,

6、Sage,Semail,Sintday,GradeID ,Snation,Sadd)values(,李刚,21,,2009-09-20,汉,山东烟台)insert into Student(StuID,StuName,Tel,Sage,Semail,Sintday,GradeID ,Snation,Sadd)values(,大鹰,20,,2008-10-10,汉,山西太原)insert into Student(StuID,StuName,Tel,Sage,Semail,Sintday,GradeID ,Snation,Sadd)value

7、s(,大山,22,,2008-10-10,汉,陕西西安)insert into Studentvalues(,佳佳,19,女,,2009-09-20,汉,山西太原)insert into Studentvalues(,格里,6,20,女,,2009-09-20,傣,江西南昌)insert into Studentvalues(,可可,6,18,女,,2011-09-10,藏,广东河源)insert into Studentvalues(,小黑,20,男,,19

8、91-02-20,汉,广东河源)insert into Studentvalues(,小乐,20,女,,2010-09-20,汉,广东梅州)insert into Studentvalues(,小美,20,女,,2010-09-20,汉,广东汕头)insert into Studentvalues(,张强,20,男,,2010-09-20,汉,广东梅州)insert into Gradevalues(,信管,60,Lucy,信息管理与信息系统,商学院)insert into Gradevalues(,信管,55,黄强,

9、信息管理与信息系统,商学院)insert into Gradevalues(,信管,60,加乐,信息管理与信息系统,商学院)insert into Gradevalues(,信管,55,美华,信息管理与信息系统,商学院)insert into Gradevalues(,信管,60,陈德,信息管理与信息系统,商学院)insert into Gradevalues(,信管,65,刘华,信息管理与信息系统,商学院)insert into Gradevalues(,国贸,65,黄里欧,国际贸易,商学院)insert into Gradevalues(,国贸,65,黄欧,国际贸易,商学院)insert

10、into Gradevalues(,国贸,60,黄欧,国际贸易,商学院)insert into Gradevalues(,市营,65,黄里欧,市场营销,商学院)insert into Gradevalues(,英语,65,黄美丽,英语,外国语学院)insert into Gradevalues(,英语,65,姜丽,英语,外国语学院)insert into Gradevalues(,计算机,60,小飞,计算机,计算机学院)insert into Gradevalues(,信科,65,天力,信科,信息科技学院)insert into Teachervalues (T001,张莉莉,sfuij123

11、,女,汉,广东湛江,)insert into Teachervalues (T002,牛莉,sfuij123,女,回,山西太原,7)insert into Teachervalues (T003,李丽莉,3257j123,女,汉,陕西西安,)insert into Teachervalues (T004,璐璐,s7689fuij123,女,汉,广东河源,)insert into Teachervalues (T005,黎强,s35468j123,男,汉,广东梅州,)insert into Teachervalues (T006,张吉,s0897j123,男,汉,广东汕头,)insert int

12、o Teachervalues (T007,鲁克,578j123,男,汉,广东湛江,)insert into Teachervalues (T008,匡阔,4678j1353,男,汉,陕西西安,)insert into Teachervalues (T009,陆羽,sf579j123,男,汉,广东潮州,)insert into Teachervalues (T010,张铁花,sdghij123,女,汉,广东河源,)insert into Teachervalues (T011,张天华,sfuij135,男,汉,江西南昌,)insert into Course values (j001,计算机基

13、础,32,120,2011-3-01,JD306,必修)insert into Course values (g001,管理学,32,90,2011-3-01,JB306,必修)insert into Course values (g002,微观经济学,32,110,2011-3-01,JD305,必修)insert into Course values (w001,英语,32,60,2011-9-01,JD306,必修)insert into Course values (x001,两性心理,18,120,2011-9-01,JD206,通选)insert into Course value

14、s (j004,C语言,32,90,2011-3-01,HH306,必修)insert into Course values (j002,Javal,32,120,2010-9-01,HF306,必修)insert into Course values (w003,经济法,32,120,2011-3-01,HE306,必修)insert into Course values (w002,日语,32,120,2011-9-01,JD301,必修)insert into Course values (g003,管理系统,32,100,2011-3-01,JD301,限选)insert into C

15、ourse values (w003,日语,32,120,2011-3-01,JD301,必修)insert into Course values (t002,自动化,32,120,2011-9-01,JD301,必修)insert into Course values (g004,物流管理,32,120,2011-9-01,JD301,限选)insert into SCvalues (00001,T001,g003,99)insert into SCvalues (00002,T003,t002,90)insert into SCvalues (00003,T003,t002,58)inse

16、rt into SCvalues (00004,T003,t002,79)insert into SCvalues (00005,T008,x001,88)insert into SCvalues (00006,T008,x001,98)insert into SCvalues (00007,T008,x001,69)insert into SCvalues (00008,T003,w001,89)insert into SCvalues (00009,T003,w001,77)insert into SCvalues (00010,T002,w001,76)insert into SCval

17、ues (00011,T001,g003,93)insert into SCvalues (00012,T001,g003,89)insert into SCvalues (00013,T001,g003,88)insert into SCvalues (00014,T001,g003,70)insert into SCvalues (00015,T001,g003,61)select * from Studentselect * from Gradeselect * from Teacherselect * from Courseselect * from SCcreate view stu

18、dentview(学生学号,学生姓名,性别,班级,辅导员,专业,学院)asselect StuID,StuName,Ssex,GName,Gassist,Gdept,Gacademyfrom Student,Gradewhere Student.GradeID=Grade.GradeIDselect * from studentviewcreate view sc_view(学生学号,学生姓名,课程名称,授课教师,课程性质,成绩)asselect SC.StuID,StuName,CName,TName,C_XKL,Creditfrom Student , SC,Teacher ,Course

19、where Student.StuID=SC.StuID and Course.CID=SC.CID and Teacher.TID=SC.TIDselect * from sc_viewcreate view cs_view(学生学号,学生姓名,课程名称,授课教师,上课地点,时间 )asselect SC.StuID,StuName,CName,TName, Croom ,Cdatefrom Student , SC,Teacher ,Coursewhere Student.StuID=SC.StuID and Course.CID=SC.CID and Teacher.TID=SC.TID

20、select * from cs_viewcreate nonclustered index index_StuNameon Student(StuName)create unique index index_StuIDon Student(StuID)create unique index index_cidon Course (CID)create nonclustered index index_CNameon Course column(CName)create nonclustered index index_scon SC(StuID,CID,Credit)exec sp_help

21、index SCcreate procedure Pro_基本信息as select StuID,StuName,Ssex,GName,Gassistfrom Student,Gradewhere Student.GradeID=Grade.GradeIDexec Pro_基本信息create procedure Pro_班级信息GradeID char(6)as select StuID,StuName,Ssex,Grade. GradeID,GNamefrom Student,Gradewhere Student.GradeID=Grade.GradeID and Student.Grad

22、eID=GradeID exec Pro_班级信息 GradeID=exec Pro_班级信息 GradeID=create trigger trig_studenton Student after updateas begin update Grade set GNum = GNum +1 where GradeID in () update Grade set Gnum=Gnum-1 where GradeID in ()endselect *from Gradeupdate Studentset GradeID= where StuID=select *from Gradeselect

23、*from Grade where GradeID= create trigger trig_counton Student after updateas begin update Grade set GNum = GNum +1 where GradeID in (select GradeID from inserted) update Grade set GNum=GNum -1 where GradeID in (select GradeID from deleted)endselect *from Student where StuID=select *From Grade where

24、 GradeID=update Studentset GradeID=where StuID=select*from Grade where GradeID=select * from Gradecreate trigger Trig_Son Student after deleteasdelete from Student where StuID in (select StuID from deleted)Gocreate trigger Tri_scroon SCafter deleteasdelete from Student where StuID in (select StuID from deleted)Godelete from SC where StuID=select * from Student where StuID=select * from SC where StuID=create trigger trig_denyupdateon SCfor update as if update ( Credit)beginraiserror (该事务不能被处理，【Credit】列的值不能被改变,10,1)rollback transactionendupdate SC set Credit=100where SCID=00014declare s