数据库学生成绩管理系统

select * from Class; select * from Course; select * from Grade; select * from Student; select * from Teacher; /*建立索引*/ --建立唯一索引 CREATE UNIQUE INDEX Classclno ON Class(CLno); CREATE UNIQUE INDEX Coursecno ON Course(Cno); CREATE UNIQUE INDEX Studentsno ON Student(Sno); CREATE UNIQUE INDEX Teachertno ON Teacher(Tno); --成绩按降序建立索引 CREATE INDEX Grade_G ON Grade(Grade DESC); drop index Teachertno ON Teacher; select * from Grade; /*建立约束条件*/ Alter Table Course Add Constraint CourseFKey Foreign Key(Tno) References Teacher(Tno); select * from Course; Alter Table Grade Add Constraint GradeFKey1 Foreign Key(Cno) References Course(Cno); Alter Table Grade Add Constraint GradeFKey2 Foreign Key(Sno) References Student(Sno); Alter Table Grade Add Constraint G1 Check(Grade >= 0 And Grade <= 100); Alter Table Student Add Constraint StudentFKey Foreign Key(Cla_CLno) References Class(CLno); Alter Table Student Add Constraint S1 Check(Ssex IN('男','女') ); select * from Grade; select * from Student; select * from Teacher; /*子模式设计:视图的建立*/ Create View Teacher_View(教师姓名, 教师号, 性别, 课程名称, 课号) As Select Teacher.Tname,Teacher.Tno,Teacher.Tsex,Course.Cname,Course.Cno From Teacher,Course Where Teacher.Tno = Course.Tno; Create View Teacher_View(学生姓名, 学号, 性别, 年龄, 班级, 系别, 专业)/*学生和班级*/ As Select Student.Sname,Student.Sno,Student.Ssex,Student.Sage,Class.Clno,Class.CLdept,Class.CLmajor From Student,Class Where Student.Cla_CLno = Class.CLno; Create View StudentGradeAdding_View(学号, 课号, 成绩) /*学生和选课*/ As Select Student.Sno,Grade.Cno,Grade.Grade From Student,Grade Where Student.Sno = Grade.Sno; Create View StudentGradeSearching_View(姓名, 学号, 性别, 课程名称, 课号, 成绩 ,课程学分) /*学生、成绩*/ As Select Student.Sname,Student.Sno,Student.Ssex,Course.Cname,Grade.Cno,Grade.Grade,Course.Ccredit From Student,Grade,Course Where Student.Sno = Grade.Sno And Grade.Cno = Course.Cno; /*设置触发器——每次对表Student的学号修改操作时对应的成绩表的学号也做修改*/ CREATE TRIGGER Student_Sno on Student for update as declare @old_id int declare @new_id int select @old_id=Sno from Grade Where select @new_id=Sno from Student if update(Sno) begin update Grade set Sno=@new_id where Sno=@old_id end /*测试触发器*/ UPDATE Student SET Sno='10' WHERE Sno='2' ;SELECT * from Grade; SELECT * from Student; /* 数据库的安全性 安全性方面的要求： Teacher_View、StudentGradeSearching_View不可更改只可查看； 老师<Teacher>有修改Student_View、StudentGradeAdding_View的权限 可以查询所有表单； 学生有查询Student_View、StudentGradeSearching_View权限 */ CREATE LOGIN Teacher_admin with password='123', default_database=master; /*创建登陆名及密码，默认数据库*/ CREATE LOGIN Student_Admin with password='321', default_database=master; CREATE USER Teacher_user for login Teacher_admin; /*创建用户*/ CREATE USER Student_user for login Student_Admin; CREATE ROLE R1; /*Teacher*/ CREATE ROLE R2; /*Student*/ GO GRANT SELECT,UPDATE,INSERT,DELETE ON Student_View to R1 WITH GRANT OPTION GO GRANT SELECT,UPDATE,INSERT,DELETE ON StudentGradeAdding_View to R1 WITH GRANT OPTION GO GRANT SELECT ON Teacher_View to R1 WITH GRANT OPTION GO GRANT SELECT ON StudentGradeSearching_View to R1 WITH GRANT OPTION GO GRANT SELECT ON StudentGradeSearching_View to R2 GO GRANT SELECT ON Student_View to R2 GO EXEC sp_addrolemember R1, Teacher_user; EXEC sp_addrolemember R2, Student_user; ---测试---- /*Student对StudentGradeSearching_View视图的查询、插入、修改*/ SELECT * FROM StudentGradeSearching_View; INSERT INTO StudentGradeSearching_View VALUES('宋宋',1,'女','数据库',1,99,3); UPDATE StudentGradeSearching_View SET 成绩=100 WHERE 学号='1'; /*Teacher对Student_View、StudentGradeAdding_View视图的查询、插入、修改*/ SELECT * FROM StudentGradeSearching_View; UPDATE StudentGradeSearching_View SET 成绩=100 WHERE 学号='1'; ---查询--- /*查询学习了C语言课程的女同学个数*/ Select 课程名称,COUNT(学号) 学生人数 From StudentGradeSearching_View Where 性别 = '女' And 课程名称 = 'C语言' Group By 课程名称 GO /* 查学生宋君华选修的平均分；*/ SELECT AVG(Grade) 平均成绩 FROM Grade WHERE Cno IN (SELECT Cno FROM Course WHERE Sno IN (SELECT Sno FROM Student WHERE Sname='宋君华' ) );