学生成绩管理系统SQL数据库技术文档格式.docx

学生成绩管理系统SQL数据库技术文档格式.docx

《学生成绩管理系统SQL数据库技术文档格式.docx》由会员分享，可在线阅读，更多相关《学生成绩管理系统SQL数据库技术文档格式.docx（25页珍藏版）》请在冰豆网上搜索。

0，则插入不成功.

●在向教师表中添加记录时，如果电话号码不是0-9的数字，则插入不成功。

（10）、创建自定义函数和索引

　这些都在后面有完整的代码和解释.

三、四个表的E-R实体模型图分析:

　1　　　　Ｎ

　　　N　　　M

在教学管理中,学校开设若干门学科，一个教师可以教授其中的一门或多门课程,每个学生也需要学习其中的几门课程,因此，教学管理中涉及的对象（实体型）有学生、教师和课程。

用E-R图描述它们之间的联系。

如图所示。

其中，学生与课程是多对多的联系，而教师与课程的联系则是一对多.

这四个表的总的实体—关系图：

四、设计数据表:

通过E—R图分析，现在已经对数据库有一个很清楚的认识了.

在此学生成绩信息中有4个表需要建立

学生信息表（student）包括学号（sｎo）、姓名（sname）、性别（Sｓex）、班级（clａss）、出生日期（sbirｔh）

教师信息表（teacher）包括教师编号（ｔｎｏ）、姓名（tname）、性别（Tseｘ）、部门（depａｒt）、职称（prof）、出生日期（ｔｂiｒth）

成绩表（ｓcore）包括学号（sno）、课程号（cnｏ）、成绩（ｄegree）

课程信息表（ｃourｓｅ）包括课程号（ｃｎo）、课程名称（cnaｍｅ）、教师编号（tno）

　五、表结构　

主键是一个表通过一个列或多个列组合的数据来唯一标识表中的每一行，这个列或列组合就被称为主键,煮件可以来强制实体完整性.

主键约束有以下特征和作用:

（1）主键约束通过不允许一个或多个列输入重复的值来保证一个表中所有行的惟一性，使所有浪都是可以区分的.

　（２）一个表张只能有一个主键，且组成主键的列的数据都不能取空值。

（3）当定义主键约束时，SQLsｅrver在主键列上建立惟一性索引,这个索引在煮件被查询时可以加快查询的速度。

在tｅacher表中，以教师编号tno为主键，对其进行惟一性约束。

在Course表中,以课程号为主键，对其进行惟一性约束。

1、Studenｔ表：

sno

Chａr（8）

不允许为空

ｓname

Ｃhａr（１0）

Ｓsex

Char

（2）

ｓｂｉｒth

Datetimｅ（8）

允许为空

ｃｌasｓ

Char（4）

ｔｙｐe

Ｃhar（7）

score表结构:

ｓno

Char（8）

ｃｎｏ

Char（５）

ｄegｒeｅ

Floａt（8）

Couｒsｅ表：

以课程号为主键,通过这个主键来对这个表进行唯一性约束.

cno（主键）

Cｈar（5）

cｎａｍe

ｖarｃｈaｒ（10）

ｔno

cｈar（3）

Tｅacher表结构：

Tno（主键）

Chaｒ（5）

tnamｅ

vaｒｃhar（10）

ｄepaｒｔ

Varchar（8）

tsex

Ｃhar

（2）

tbirth

datａtｉme

prof

Chaｒ（6）

六、其他对象的说明：

（1）、外键约束（primaｒy　key）:

外键也是由表中的一个列或多个列组成的，它是和在相关表中事先定义的具有惟一性的列一起使用的，该列一个是所在表的主键。

外键约束用来建立和强制两个表之间的关联,即一个表的一个列或列组合与另一个表的具有惟一性的列或列组合相关，这个列或列组合就成为第一个表中的外键外键约束限制将破坏这种关联的操作.作为外键的列的值可以是空值,或是它所引用的表中已经存在的值。

在score表中，其学号ｓno和课程号cno都是此表的外键约束,因为此表中学号sno要受学生信息表中的学号的约束,studｅｎt表中没有的学号，sｃｏre表中也应该没有，如果向scｏｒｅ表添加数据时违反的此规则，则添加不成功。

课程号ｃｎｏ也同样,受到coｕrse表中课程号cno的约束．。

（2）、空值（ｎull）：

空值意味着数据尚未如,它与0或长度为零的字符串（“”）的含义不同。

如果表中的某一列必须有值才能使记录有意义，那么可以指明该列不允许取空值。

　　　在教师信息表teachｅｒ中其出生日期列tbirth和职称prof允许为空,但并不表示该教师没有生日,没有职称,而是目前还不知道而已。

在学生信息表stuｄeｎｔ中其出生日期sbｉrｔh也可为空,也是同样的道理。

（3）、cｈｅcｋ约束:

检查约束可以用来限制列上可以接受的数据值,检查约束使用逻辑表达式来限制列上可以接受的数据.可以在一个列上使用多个检查约束，也可以在表上建立一个可以在多个列上使用的检查约束.

　　比如：

在学生信息表中，邮件地址seｍaｉl的格式必须是％＠%格式的。

（4）、惟一性约束:

使用惟一性约束的作用是保证在不是主键的指定惟一性的列上不会出现重复的数据。

定义了惟一性约束的列上的数据可以为空值,一个表上可以定义多个惟一性约束。

七、创建数据库、数据表、视图、存储过程、自定义函数、触发器等语句和测试语句

1、创建数据库：

create　ｄａtａbasessh

on

（namｅ=ｓsｈ,

'

e：

\老大\sql＼ssh.ｍｄf'

，

size＝1）

loｇon

（naｍe=ssh1,

＇e：

\老大\sｑl＼ssh１.lｄf'

size=1）

2、创建数据表

（1）、ｓtudent表

uｓｅssh

creａtｅtablestudeｎt

（sｎｏｃhar（8）ｐrimarykeｙ,

snameｃｈar（10）　noｔ　nuｌｌ,

　Ssexｃhaｒ

（2）notnull,

Ｓｂirthdａｔｅｔime,

clａss　ｃhaｒ（4）not　nｕll

semaｉlvarchaｒ（2０））

（2）、sｃore表

create　ｔａｂlｅｓcore

（snochar（8）ｎot　ｎｕll,

ｃno　char（4）nｏｔnull,

　deｇreefloatnoｔnull）

（3）、ｃourｓe表

crｅatetablｅcoｕrsｅ

（cnochａr（5）ｎoｔ　null　priｍarykey,

　cｎame　varchaｒ（10）notnull，

tnochar（3）noｔnｕll）

（4）、teacｈeｒ表

crｅatｅtａblｅteacheｒ

（ｔnochar（5）　notnｕllpｒｉmaｒykey，

ｔnaｍｅvarchar（10）ｎoｔ　nulｌ,

　deparｔｖarchar（8）ｎot　nuｌl,

Tｓｅｘcｈar

（2）nｏtnuｌl，

Tｂirｔhｄaｔetime，

　prｏfｃhar（６））

4向表中添加记录

（1）、向学生信息表中添加

inserｔintostudeｎtvaｌｕeｓ（'

1０1'

＇生活’，’女'

＇19８3－09—18＇,’1１'

）

iｎｓerｔinｔｏ　ｓtudeｎt　vaｌｕes（'

102’,＇风尘'

，'

男’,'

1984—01—01’,＇11’）

iｎserｔintostudentvalues（'

10３'

＇离开＇,'

男'

'

1984-０１-01＇，’11'

insertintostudentvalueｓ（’10４＇，'

流浪＇,＇女＇，’１9８4-11—１1＇，＇1１'

inseｒtiｎtoｓｔｕdeｎtvａluｅs（＇10５＇，'

生命'

男’，’1９８4-12—０5’，'

13'

ｉnsｅrt　inｔo　sｔuｄenｔvaluｅs（’1０6’，’无悔＇，'

女'

1984—11—0１’，'

1３’）

insｅｒt　inｔostｕdent　ｖａlｕes（’10７＇,＇历史＇，'

女＇，’1984—１0-０1’，＇１2＇）

inｓertｉntｏstudentvalues（'

10８’,'

风尘’，'

男’，’1９85－９-0８’，'

11'

ｉnsertiｎtostudｅｎt　values（'

１0９’,'

活着’，’男'

19８5-1２—１2＇，'

１２'

ｉｎｓertｉnto　stuｄentvalues（’110'

，’傻瓜'

，’女'

，＇1985－08-２8＇,'

12'

）

………………

查看记录

Select＊fromｓtｕｄｅnt

（２）、向成绩表中添加

　insertｉntoscｏrｅvalueｓ（’１０1＇，’01＇，88）

inserｔinｔoｓｃorevａlues（'

101'

＇02’，８5）

insert　inｔoscorｅvalｕｅs（’1０2’,’０２'

，80）

iｎsｅrt　ｉntｏ　scｏrevａluｅs（’10１’，’03＇,8８）

inｓertiｎｔoｓｃｏre　ｖalｕes（'

1０２'

＇02’,8５）

insertｉｎtｏsｃorevalueｓ（’１02’，’0３＇，80）

iｎｓeｒｔｉntｏsｃｏrevaｌｕｅｓ（＇103'

，’０1’，83）

ｉnｓertｉntoscorevaluｅs（'

’02’，85）

inseｒtintoscｏrevａlｕes（＇103’,'

０３＇，90）

inｓｅrtｉntoscorｅ　valuｅs（＇104＇，＇01'

，6０）

…………

Ｓｅｌｅｃt*frｏmsｃore

（３）、向教师表中添加数据

iｎseｒtｉｎｔo　course　valueｓ（＇01'

，＇计算机’，’１1＇）

insertｉntｏcourｓeｖalｕｅs（'

０2'

＇网络管理＇，’12'

inseｒｔinｔocoursevalｕes（＇０3＇,'

专业英语＇,’13’）

inseｒtintocouｒse　ｖalues（’04'

，＇软件工程’,＇14＇）

　　…………

Select　*fｒomcourse

（4）、向课程表中添加数据

iｎseｒtinｔoｔeaｃher　valuｅｓ（'

11’，’无意’，’计算机系’，'

男＇，'

197３－4-5'

’教授'

　iｎsertintoteacｈeｒvaｌues（＇12＇，’生活＇,＇计算机系'

1９75-12-1'

副教授'

iｎseｒtｉntotｅacheｒｖalueｓ（'

13’，'

没有＇,＇管理系＇，’女’，'

1975－3-3＇,’副教授'

ｉnsertinto　teａchｅｒ　valuｅs（'

14’，’离开'

，’英语系＇，＇男’，'

1973－５－5＇，'

教授'

…………

Sｅlect＊from　teachｅr

５、一些查询语句

（1）、查询成绩大于学号为1０1的学生的课程为02的成绩的所有列.

ｓeｌect*　ｆｒoｍscｏｒewheｒedegree＞（selecｔdｅgreefromｓcorｅ　whereｓno='

1０１'

anｄcnｏ=＇02＇）

（2）、查询课程号0１大于课程号02的最大值、并以分数降序排序的成绩表中所有列

select＊　froｍ　scores　wheres．cｎｏ＝’01'

　ands.degree>

＝（ｓeleｃtｍax（degrｅe）ｆｒoｍscoreyｗhｅrey．cno='

02’）　ｏrｄｅrｂydｅgｒeedeｓc

go

ｓｅlecｔｍax（degree）as"

02ｍax"

frｏmｓcoｒｅwｈeｒe　cnｏ=’0２'

（3）、查询性别为男的学号，姓名,班级，课程号和成绩的学生

sｅｌeｃtstudenｔ。

sno,ｓｔｕｄent．sｎame,sｔuｄeｎt。

claｓs,ｓcore.ｃnｏ，ｓｃoｒe.deｇree　ｆromsｔudent，ｓｃorewhｅre　stuｄｅｎt．sｎｏ=sｃoｒe。

ｓｎo　andｓsex＝’男’

（4）、查询成绩在60到80之间的所有列

seｌeｃt*　fromsｃore　whｅreｄｅｇrｅebetwｅen6０and80

（5）、查询scoｒe表中至少有5名学生选修的并以0开头的课程的平均分

ｓelecｔaｖｇ（deｇｒｅe）as"

平均分”，cnofroｍscorewherecnoliｋe’０％'

　grｏuｐ　bycnohaving　count（＊）>

=5

６、创建自定义数据类型

创建一个emaｉl自定义数据类型

ｅxecsp_aｄdtypeemail,　'

varｃhar（２0）'

　,'

ｎulｌ’

修改student表中的sｅmail数据类型为email类型

ａltertablestｕｄentａlｔercolumnｓeｍaileｍail

7、向表中添加字段

　向student表添加type,semａil,，ｂ并且邮件地址有ｃｈeck约束

ａltertaｂlestｕdｅnｔ　add　tｙpechar（７）

ａlｔerｔabｌestudentadｄsemaｉｌｖarｃhar（2０）nullｃoｎstraintck_sem　cheｃk（seｍaillike　’%@％’）

alter　ｔａbleteａcher　aｄｄｔeｌvａrchaｒ（15）

8、创建视图

（1）、创建所有11班的学生信息的视图

crｅａteviewstudenｔ11

as

　ｓｅlｅct＊fromstudentｗｈｅreclaｓs=’11'

查看视图中的记录

sｅlect＊fｒomstudenｔ11

（2）、创建视图coｕrse_deｇrｅe,其中的内容是选修计算机课程的学生信息，包括（sno，ｓname,cno，cnａｍe,dｅgree），创建时加上ｗiｔｈcheckoption

ｃreateviｅwcourse_degree（ｓｎｏ，ｓnaｍe，cnｏ，cnａme,degrｅｅ）

selｅｃt　scoｒe．sno，sname，sｃｏrｅ。

ｃno，cnamｅ，degreeｆromcourｓe，sｔudｅnｔ，　scｏrｅ

wｈerｅsｃｏo=couｒse．cｎoａｎdｓtudent．sno=ｓｃorｅ。

ｓnoaｎdcname=’计算机'

witｈcｈeckｏｐｔｉｏn

seleｃt*　fｒomcoｕrse_dｅgrｅe

（3）、创建一个视图,其中的内容是成绩表中每门课程的

creatｅviewaveragｅ

selectａvg（deｇree）　as’平均分＇frｏm　scoｒegroup　byｃnｏ

select　＊frｏｍａverａge

（4）、创建视图其中的内容是所有男教师和男学生的name,seｘ,birth

cｒeａｔeviewman

as

selｅctsnameａsｎame，sｓｅxas　sｅｘ,sbirtｈａs　ｂｉrth　fｒomstudenｔwhｅre　ssex=＇男’

ｕnｉonselecttnａme,tsex,tbｉｒthｆrom　ｔｅａcheｒwheｒe　tｓｅx＝’男’

ｓeleｃt＊fｒoｍｍan

９、创建规则

规则的作用月CHECK约束的部分功能相同，在向表中的某列插入或更新数据时,用它来限制输入的新值的取值范围。

而它与CHECK约束不同的是：

●CHECＫ约束是用CRＥＡTＥTＡBLＥ语句在建表时指定的，而规则需要作为单独的数据库对象来实现。

●在一个列上只能使用一个规则。

但可以使用多个CＨECK约束.

●规则可以应用于多个列,还可以应用于用户自定义的数据类型,而CＨＥCK约束只能应用于它定义的列。

（1）、创建一个ｄegree＿rule规则

cｒeateruledｅgree_ｒule

　as

＠ｖａｌues〉0

把此规则绑定到scoｒe表中degree列

execｓｐ_ｂindrule'

deｇｒee_rｕle’,’ｓcoｒe.degｒeｅ'

在向成绩表中添加记录时,如果成绩ｄｅgreｅ〈0，则插入不成功。

（２）、创建一个ｔel_rulｅ规则

cｒｅateruletel_rulｅ

aｓ

＠value　lｉkｅ　'

[0—9]［0-９］　[０—9][0—9］　[0—9][0—9][0－９]［0-9]’

把此规则绑定到ｔｅａcｈer表中tel列

execsp_bｉndrule　’teｌ_rulｅ'

，＇teacher．tel'

在向教师表中添加记录时,如果电话号码不是０-９的数字，则插入不成功.

10、创建存储过程

　存储过程是存储在服务器上的例行程序及过程,在SＱＬ　ＳＥRVＥR只能感定义某个过程,其中记录了一系列的造作,每次应用程序只需调用该过程就可完成该操作,这种SQLＳEＲVＥR中定义的过程就被称为存储过程。

它可以完成以下功能：

⏹接受输入参数并返回多个输出值。

⏹包含T—SQL语句用以完成特定的SQLSEＲVER操作，其中可以有对其他存储过程的调用。

⏹返回一个指示成功与否及失败原因的状态代码给调用它的过程。

存储过程不能接用过程名返回值,也不能直接在表达式中使用。

（1）、创建一个存储过程，来显示成绩表中的课程号在课程表中并且所任教师性别为男、所在部门是计算机系的成绩表中的列

creaｔepｒocstｕdeｎt_1１

as　

sｅｌｅct*fromsｃoｒｅwheｒecnｏｉn（ｓｅleｃtｃnofrｏm　couｒse　,ｔeacher　whereｃouｒｓｅ。

ｔno=ｔeacher．tｎoanddepart=＇计算机系’　aｎｄtsex='

调用此存储过程

Exec　student＿1１

（2）、创建一个带输入参数的存储过程，调用此存储过程时，给出一个学生名，显示出此学生的学号,姓名，所学课程号,课程名称和对应的成绩

ｃｒｅateprocstudent_namｅ

@ｓnamｅ　ｖarｃhaｒ（10）

seleｃｔstudeｎｔ。

ｓｎo,ｓｎaｍe,score。

cno,degree　cnamefｒｏｍstudeｎｔ，ｓcｏre，cｏurse

wherｅsｔudent。

ｓｎo=sｃore。

ｓｎｏａndsｃｏrｅ.cno＝coｕrｓｅ．cnｏand　sｎame=＠snａme

调用此存储过程，（此例是输出姓名为历史的学生的信息）

execstuｄeｎt_naｍe　＇历史＇

（3）、创建一个存储过程,在执行此存储过程时，如果没有给出参数（学生姓名），则输入全部的学生的学号,姓名,班级，任课教师编号及其姓名，所学课程名称和成绩，如果有，则显示此学生的以上信息.

crｅatｅpｒocｓtudeｎt_ｔeａcheｒ

＠ｓｎａmeｖarchａr（1０）＝ｎｕｌｌ

as

　if　@sｎａｍeis　nｕll

　ｓelｅct　ｓtudeｎｔ。

ｓno，snaｍe,stuｄｅｎt.clａsｓ,teａcher．tno,teaｃｈeｒ。

ｔname,dｅgree，cnaｍｅfrom　ｓtudenｔ，score，coursｅ,tｅaｃheｒ

wｈere　student.sｎo=ｓcoｒｅ．ｓnoaｎd　scｏre。

cno=ｃourse。

ｃｎoａndcourｓe。

tnｏ=teａｃher。

tnｏ

　else

ｓeｌect　ｓtｕｄent。

ｓｎo，snａme，ｓｔudent。

clａss,teaｃhｅr.tnｏ，tｅachｅr.tnaｍe，degｒeｅ，cnａｍefromstudent，scoｒe,ｃouｒｓe，teacｈｅｒ

where　stuｄeｎt．ｓｎｏ＝ｓｃore。

sｎoand　ｏ=cｏursｅ.cnｏandcourse．tno＝teacheｒ．ｔnｏaｎdｓnaｍe=＠sｎame

调用此存储过程

exｅｃsｔudｅnt＿tｅａcher（没有实参）

eｘecstuden