学生成绩管理系统-SQL数据库技术

《SQL数据库技术》课程设计一、题目：学生成绩管理二、因为我做的是学生成绩管理，所以数据库中至少有个一个表是来存储学生的各课成绩的.有了学生成绩,得知道这个成绩是哪个学生的，所以此表中也得有学生姓名,但是学生姓名肯定有重复的，所以得必须有个标志来惟一标识一个学生,所以得给每个学生一个编号（学号），但是也得必须清楚，这个成绩是哪门课程的，所以给课程定义了一个编号.之后,得想到有了学生成绩表，总得有个表来存放学生信息吧,所以又建立一个学生信息表，此表中的必须的字段得有学号、姓名、班级,其他的字段可以根据需要来添加。然后就是得有个课程表来存放哪个教师教哪门课程信息，所以此表中至少得有课程号，课程名称和教师的惟一标识（教师编号）,再有一个表来存放教师的信息的,其中的字段必须有教师编号，教师姓名和所在的部门,当然也可以有教师出生日期、职称、电话号码等字段.总之，这个学生管理系统总共包括四个表学生信息表student、教师信息表teacher、成绩表score和课程表course。Student表：在建表时，除了添加学号、姓名、班级必要字段，还添加一些其他的字段,比如：出生日期、性别、邮箱地址和类型等.Teacher表：在建表时，除了添加教师编号,教师姓名，部门必要字段，还可以添加一些其他的字段，比如:出生日期、性别、和电话等.Score表：此表应该包括学生学号、课程号和成绩等。Course表：应包括课程号，对应的课程名称和教此课程的教师编号.(1）、建立一个数据库，然后在此数据库中建立这四个数据表。（2）、向表中添加记录。（3）、用一些查询语句来查看表中的特定记录.（4）、向表中添加一些字段。如:向teacher表中添加字段tel（5)、创建一个自定义数据类型，并修改student表中的某个字段为此数据类型。（6)、创建几个视图.查询某个班级的学生信息。查看每门课程的平均成绩。查看选修计算机课程的学生信息。查看所有男教师和所有男学生的信息（7）、创建几个存储过程●显示成绩表中的课程号在课程表中且所任教师性别为男、计算机系的成绩表●显示某学生的学号，姓名，所学课程号,课程名称和对应的成绩●在执行此存储过程时,如果没有给出参数（学生姓名），则输入全部的学生的学号，姓名，班级，任课教师编号及其姓名，所学课程名称和成绩，如果有，则显示此学生的以上信息.(8）、创建触发器◆在成绩表中建立一个触发器，当向表中添加记录时,此学生的成绩都乘以1。2◆检查学生的邮箱地址是否相同，如果相同,输出’insertingfail'，并且回滚事务；如果不相同，则插入成功。◆在成绩表建立一个触发器，在向表中插入记录时,检验插入的课程号是否在课程表中的课程号的范围之内。（9）、创建规则,并绑定●在向成绩表中添加记录时，如果成绩degree<0，则插入不成功.●在向教师表中添加记录时，如果电话号码不是0-9的数字，则插入不成功.（10)、创建自定义函数和索引

这些都在后面有完整的代码和解释。三、四个表的E-R实体模型图分析:教师学生

学教

1

N

N

M在教学管理中，学校开设若干门学科,一个教师可以教授其中的一门或多门课程，每个学生也需要学习其中的几门课程，因此，教学管理中涉及的对象（实体型）有学生、教师和课程。用E—R图描述它们之间的联系。如图所示.其中,学生与课程是多对多的联系，而教师与课程的联系则是一对多。这四个表的总的实体—关系图：班级号学号号学生信息表出生日期号学号号性别号出生日期号教师姓名号成绩表号部门号

成绩号教师信息表课程名称号课程号号

职称号教师编号号课程表四、设计数据表：通过E—R图分析，现在已经对数据库有一个很清楚的认识了.在此学生成绩信息中有4个表需要建立学生信息表(student）包括学号(sno）、姓名(sname）、性别（Ssex）、班级（class)、出生日期（sbirth）教师信息表(teacher）包括教师编号(tno）、姓名（tname）、性别(Tsex）、部门(depart)、职称（prof）、出生日期（tbirth）成绩表（score）包括学号（sno)、课程号(cno）、成绩(degree)课程信息表（course）包括课程号(cno）、课程名称(cname)、教师编号（tno)五、表结构

主键是一个表通过一个列或多个列组合的数据来唯一标识表中的每一行，这个列或列组合就被称为主键,煮件可以来强制实体完整性.主键约束有以下特征和作用:（1）主键约束通过不允许一个或多个列输入重复的值来保证一个表中所有行的惟一性,使所有浪都是可以区分的。

(2）一个表张只能有一个主键,且组成主键的列的数据都不能取空值.(3）当定义主键约束时，SQLserver在主键列上建立惟一性索引，这个索引在煮件被查询时可以加快查询的速度。在teacher表中,以教师编号tno为主键,对其进行惟一性约束。在Course表中，以课程号为主键，对其进行惟一性约束。1、Student表:snoChar（8)不允许为空snameChar（10)不允许为空SsexChar（2）不允许为空sbirthDatetime（8）允许为空classChar（4）不允许为空typeChar（7）允许为空score表结构：snoChar（8）不允许为空cnoChar（5）不允许为空degreeFloat（8）不允许为空Course表：以课程号为主键，通过这个主键来对这个表进行唯一性约束.cno(主键）Char（5）不允许为空cnamevarchar(10）不允许为空tnochar（3)不允许为空Teacher表结构：Tno（主键）Char（5）不允许为空tnamevarchar(10)不允许为空departVarchar（8)不允许为空tsexChar（2）不允许为空tbirthdatatime允许为空profChar（6)允许为空六、其他对象的说明：（1）、外键约束(primarykey)：外键也是由表中的一个列或多个列组成的,它是和在相关表中事先定义的具有惟一性的列一起使用的，该列一个是所在表的主键.外键约束用来建立和强制两个表之间的关联，即一个表的一个列或列组合与另一个表的具有惟一性的列或列组合相关，这个列或列组合就成为第一个表中的外键外键约束限制将破坏这种关联的操作。作为外键的列的值可以是空值，或是它所引用的表中已经存在的值。在score表中，其学号sno和课程号cno都是此表的外键约束，因为此表中学号sno要受学生信息表中的学号的约束，student表中没有的学号,score表中也应该没有,如果向score表添加数据时违反的此规则,则添加不成功.课程号cno也同样，受到course表中课程号cno的约束。。(2)、空值(null):空值意味着数据尚未如，它与0或长度为零的字符串（“”)的含义不同.如果表中的某一列必须有值才能使记录有意义，那么可以指明该列不允许取空值。

在教师信息表teacher中其出生日期列tbirth和职称prof允许为空，但并不表示该教师没有生日，没有职称,而是目前还不知道而已。在学生信息表student中其出生日期sbirth也可为空,也是同样的道理。（3）、check约束：检查约束可以用来限制列上可以接受的数据值，检查约束使用逻辑表达式来限制列上可以接受的数据。可以在一个列上使用多个检查约束，也可以在表上建立一个可以在多个列上使用的检查约束。

比如：在学生信息表中，邮件地址semail的格式必须是%@％格式的.（4)、惟一性约束：使用惟一性约束的作用是保证在不是主键的指定惟一性的列上不会出现重复的数据。定义了惟一性约束的列上的数据可以为空值,一个表上可以定义多个惟一性约束.七、创建数据库、数据表、视图、存储过程、自定义函数、触发器等语句和测试语句1、创建数据库:createdatabasesshon（name=ssh,

filename=’e：\老大\sql\ssh.mdf',

size=1）logon（name=ssh1,

filename='e:\老大\sql\ssh1.ldf'，

size=1)2、创建数据表(1）、student表usesshcreatetablestudent（snochar（8）primarykey，snamechar（10）notnull,Ssexchar（2)notnull,Sbirthdatetime，classchar(4)notnullsemailvarchar(20）)（2）、score表createtablescore（snochar（8）notnull,cnochar（4)notnull，

degreefloatnotnull）（3）、course表createtablecourse(cnochar(5）notnullprimarykey，cnamevarchar（10)notnull,tnochar(3)notnull)(4）、teacher表createtableteacher(tnochar（5）notnullprimarykey，tnamevarchar(10）notnull,departvarchar（8）notnull，

Tsexchar（2)notnull,

Tbirthdatetime，

profchar（6））4向表中添加记录（1）、向学生信息表中添加insertintostudentvalues（’101’,'生活’，’女’，'1983—09-18’，'11'）insertintostudentvalues（’102’，'风尘'，'男',’1984-01—01’，’11’）insertintostudentvalues(’103’,’离开','男’，’1984—01—01’，'11'）insertintostudentvalues（’104’,’流浪’，'女',’1984—11—11'，’11’）insertintostudentvalues(’105’，’生命'，'男’，’1984—12-05'，’13’）insertintostudentvalues(’106’,’无悔'，’女'，'1984—11—01’，'13'）insertintostudentvalues（'107’，'历史’，’女'，’1984—10-01’,’12')insertintostudentvalues（’108’,’风尘',’男',’1985—9-08'，'11’）insertintostudentvalues（’109'，’活着',’男','1985-12—12’，'12'）insertintostudentvalues（’110’,’傻瓜’,'女',’1985—08-28’，’12’）………………查看记录Select*fromstudent（2)、向成绩表中添加insertintoscorevalues('101’，’01’，88）insertintoscorevalues(’101’,’02',85)insertintoscorevalues（’102'，’02’，80)insertintoscorevalues（'101','03’,88)insertintoscorevalues（'102’，'02’,85)insertintoscorevalues('102’,'03’,80）insertintoscorevalues(’103’，’01’，83)insertintoscorevalues('103'，'02'，85）insertintoscorevalues（’103'，'03’,90)insertintoscorevalues('104’，'01’，60）…………查看记录Select*fromscore（3）、向教师表中添加数据insertintocoursevalues（’01'，'计算机’，’11'）insertintocoursevalues（'02’，’网络管理’，’12’)insertintocoursevalues('03’，'专业英语’，’13'）insertintocoursevalues(’04’，’软件工程'，’14’）

…………查看记录Select＊fromcourse（4）、向课程表中添加数据insertintoteachervalues（’11',’无意'，'计算机系’,'男'，’1973—4—5’，'教授’）insertintoteachervalues(’12’,'生活’,'计算机系’，'女'，’1975-12-1'，’副教授’)insertintoteachervalues（'13’,'没有’，’管理系’,’女’，'1975—3—3’，’副教授’）insertintoteachervalues（’14’，’离开’,’英语系’,'男’,'1973—5—5'，’教授’）…………查看记录Select＊fromteacher5、一些查询语句（1）、查询成绩大于学号为101的学生的课程为02的成绩的所有列。select*fromscorewheredegree〉（selectdegreefromscorewheresno=’101'andcno='02’)(2）、查询课程号01大于课程号02的最大值、并以分数降序排序的成绩表中所有列select＊o='01'ands。degree〉=（selectmax(degree）o=’02’）orderbydegreedescgoselectmax（degree)as”02max”fromscorewherecno=’02’（3）、查询性别为男的学号，姓名，班级，课程号和成绩的学生selectstudent.sno，student.sname，student.class，o，score。degreefromstudent,scorewherestudent.sno=score.snoandssex=’男’（４）、查询成绩在60到80之间的所有列select*fromscorewheredegreebetween60and80（５）、查询score表中至少有5名学生选修的并以0开头的课程的平均分selectavg（degree）as"平均分”，cnofromscorewherecnolike'0％’groupbycnohavingcount（＊）>=56、创建自定义数据类型创建一个email自定义数据类型execsp_addtypeemail，'varchar（20)’，'null'修改student表中的semail数据类型为email类型altertablestudentaltercolumnsemailemail7、向表中添加字段

向student表添加type,semail,，b并且邮件地址有check约束altertablestudentaddtypechar(7)altertablestudentaddsemailvarchar(20)nullconstraintck_semcheck（semaillike’％@％')altertableteacheraddtelvarchar(15）8、创建视图（1）、创建所有11班的学生信息的视图createviewstudent11asselect＊fromstudentwhereclass=’11'查看视图中的记录select＊fromstudent11（2）、创建视图course_degree，其中的内容是选修计算机课程的学生信息，包括（sno，sname,cno，cname,degree),创建时加上withcheckoptioncreate

viewcourse_degree(sno，sname，cno,cname，degree）asselectscore。sno，sname,o，cname，degreefromcourse，student，o=oandstudent.sno=score。snoand

cname=’计算机’withcheckoption查看视图中的记录select＊fromcourse_degree(3)、创建一个视图,其中的内容是成绩表中每门课程的createviewaverageasselectavg（degree)as'平均分’fromscoregroupbycno查看视图中的记录select＊fromaverage

（4)、创建视图其中的内容是所有男教师和男学生的name，sex，birthcreateviewmanasselectsnameasname,ssexassex,sbirthasbirthfromstudentwheressex=’男’unionselecttname，tsex,tbirthfromteacher

wheretsex=’男'查看视图中的记录select*fromman9、创建规则规则的作用月CHECK约束的部分功能相同，在向表中的某列插入或更新数据时，用它来限制输入的新值的取值范围。而它与CHECK约束不同的是:●CHECK约束是用CREATETABLE语句在建表时指定的，而规则需要作为单独的数据库对象来实现。●在一个列上只能使用一个规则.但可以使用多个CHECK约束.●规则可以应用于多个列，还可以应用于用户自定义的数据类型,而CHECK约束只能应用于它定义的列。（1）、创建一个degree_rule规则createruledegree_ruleas@values〉0把此规则绑定到score表中degree列execsp_bindrule'degree_rule’，'score。degree'在向成绩表中添加记录时，如果成绩degree〈0,则插入不成功。（2)、创建一个tel_rule规则createruletel_ruleas@valuelike’[0—9][0-9][0—9]［0—9][0—9］［0—9]［0—9][0-9］’把此规则绑定到teacher表中tel列execsp_bindrule'tel_rule’，'teacher.tel’在向教师表中添加记录时,如果电话号码不是0-9的数字，则插入不成功.10、创建存储过程存储过程是存储在服务器上的例行程序及过程,在SQLSERVER只能感定义某个过程，其中记录了一系列的造作,每次应用程序只需调用该过程就可完成该操作，这种SQLSERVER中定义的过程就被称为存储过程。它可以完成以下功能:.接受输入参数并返回多个输出值.。包含T—SQL语句用以完成特定的SQLSERVER操作，其中可以有对其他存储过程的调用。.返回一个指示成功与否及失败原因的状态代码给调用它的过程。存储过程不能接用过程名返回值,也不能直接在表达式中使用。(1)、创建一个存储过程,来显示成绩表中的课程号在课程表中并且所任教师性别为男、所在部门是计算机系的成绩表中的列createprocstudent_11asselect*fromscorewherecnoin(selectcnofromcourse，teacherwherecourse。tno=teacher.tnoanddepart=’计算机系’andtsex=’男’）调用此存储过程Execstudent_11（2）、创建一个带输入参数的存储过程,调用此存储过程时,给出一个学生名，显示出此学生的学号，姓名,所学课程号，课程名称和对应的成绩createprocstudent_name＠snamevarchar(10)asselectstudent。sno，sname，o，degreecnamefromstudent,score,coursewherestudent.sno=o=oandsname=@sname调用此存储过程,(此例是输出姓名为历史的学生的信息）execstudent_name'历史'(3)、创建一个存储过程，在执行此存储过程时，如果没有给出参数（学生姓名），则输入全部的学生的学号，姓名，班级,任课教师编号及其姓名,所学课程名称和成绩，如果有，则显示此学生的以上信息。createprocstudent_teacher＠snamevarchar（10）=nullasif@snameisnullselectstudent.sno，sname，student。class，teacher。tno，teacher。tname，degree,cnamefromstudent，score,course，teacherwherestudent。sno=o=oandcourse。tno=teacher。tnoelseselectstudent.sno，sname，student。class，teacher。tno，teacher.tname,degree,cnamefromstudent，score，course,teacherwherestudent。sno=o=oandcourse。tno=teacher。tnoandsname=＠sname调用此存储过程execstudent_teacher（没有实参）execstudent_teacher’历史’（查询姓名为历史的学生的选课信息和成绩）（4）、创建一个存储过程，传递一个学生姓名,先判断此学生是否有邮箱,如果有，则显示此学生的姓名,邮箱地址，学号，班级；如果没有的话，输出此句话'thesemailisempty’createprocstudent_email＠snamevarchar(10）asbeginif(selectsemailfromstudentwheresname=＠sname）isnullbegin

print'thesemailisempty’endelseselectsname,semail,sno，classfromstudentwheresname=＠snameend调用此存储过程execstudent_email’super’当给出姓名的那个学生没有邮箱地址时,则会显示如下内容。execstudent_emaildfdf'11、触发器

触发器是一种特殊的存储过程，它不能显式地调用，而是在往表中插入记录、更新记录或者删除记录时，被自动的激活.所以，触发器可以用来对表实施复杂的完整性约束，当触发器所保护的数据发生改变时，触发器会自动被激活,从而防止对数据的不正确修改。在触发器中卡仪查询其他表，也可以执行更复杂的T-SQL语句。如果发现引起触发器执行的T—SQL语句执行了一个非法的操作,则可以通过回滚事务使语句不能执行，回滚后SQLSERVER会自动返回到此事务执行前的状态.

SQLSERVER为每个触发器都查了两个专用表，inserted表和deleted表。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后,与该触发器相关的这两个表也会被删除.一个表中可以有多个具有不同名称的各种类型的触发器，每个触发器都可以完成不同的功能，但每个触发器只能作用在一个表上.(1）、创建一个触发器，来检查学生的邮箱地址是否相同，如果相同，输出’insertingfail'，并且回滚事务；如果不相同,则插入成功。createtriggerstudentinsertonstudentafterinsertasif

(selectsemailfrominsertedwheresemailin(selectsemailfromstudent））is

not

nullbeginprint'insertingfail’rollbacktransactionendelseprint'inseringsucceed’向学生信息表中插入一条记录，检验是否成功插入insertintostudentvalues(’114'，’lengbing'，’女’，’1985-12—12’,’11’,’lengbingssh@’，’一般'）（2)、在成绩表中建立一个触发器,当向表中添加记录时，此学生的成绩都乘以1。2createtriggerscoreupdateonscoreafterinsertasupdatescoresetdegree=degree＊1。2fromscorewheresnoin(selectsnofrominserted）向表中插入一条记录,检验触发器是否有用。insertintoscorevalues(’108’，'01',’56'）（3)、在成绩表建立一个触发器，在向表中插入记录时,检验插入的课程号是否在课程表中的课程号的范围之内。如果在，则插入成功;否则,提示信息'没有这门课程',回滚事务。createtriggercourse_scoreonscoreafterinsertasif（selectcnofrominsertedwherecnoin(selectcnofromcourse)）isnull

begin

print’没有这门课程'

rollbacktransaction

end向表中添加一条记录，进行验证.insertintoscorevalues（’108’，’06’，'60'）12、自定义函数

函数是由一条或多条T—SQL语句组成的代码段，用语实现一些常用的功能，编写好的函数可以重复使用。用户自定义函数可以接受零个或多个输入参数,函数的返回值可以是一个数值，也可以是一个表。用户自定义函数有三种类型:●返回数值的用户自定义函数●内联（单语句）的返回表的用户自定义函数●多语句的返回表的用户自定义函数

（1）、创建一个用户自定义函数,输出与指定的学生同班的学生个数createfunctionstudentcount(＠snochar(5））returnsintbegineclare＠counterintselect＠counter=count（＊)fromstudentwhereclass=（selectclassfromstudentwheresno=@sno）return@counterend调用此自定义函数（本例是查找与学号102同班的学生个数）declare@aintset＠a=dbo.studentcount（’102'）printconvert（char(3），@a）（2）、创建一个用户自定义函数，用于输出同一个班级中的学