实验指导材料

数据库原理与技术》上机实验安排实验1SQLserver2000的安装和常用工具的使用【实验内容】练习安装SQLSERVER2000及卸载SQLSERVER2000。（注意WINDOWS登录模式和混合登录模式的区别）在企业管理器中创建一个新的SQLServer服务器组，作为MicrosoftSQLServers的子对象，名称为“NewGroup”在计算机上再安装一个SQLServer命名实例，实例名为“people”。使用企业管理器在NewGroup服务器组下创建一个新的SQLServer注册，使它连接people实例.实验内容】c.实验2数据库及表的创建和管理1、使用企业管理器创建名为student的数据库，并设置数据库主文件名为syudent_data，大小为10MB;日志文件为student」og，大小为2MB.其他参数取默认值。步骤：ad.使用Transact—SQL语言创建一个studentl数据库，主文件逻辑名为studentl_data，物理文件名为studentl.mdf,为10MB,增长速度为10%;数据库的日志文件逻辑名为syudent1」og,物理文件名为studentl.ldf,初始化大小为1MB,最大尺寸为5MB，增长速度为1MB；文件存放路径为c:\data文件夹下。Createdatabasestudent1Onprimry(name=student1_data,filename=c:\data\student1.mdf,size=10,maxsize=unlimited,filegrowth=10%)logon(name=student1_log,filename=c:\data\student1.ldf,size=1,maxsize=5,filegrowth=1)在student数据库中创建一个名为pupil的表，要求：(snochar(6)notnull,snamechar(10)notnull,ssexchar(2)notnull,birthdaydatetimenotnull,politychar(20))步骤：++++-+~|—1—1—1. s-:[±l-:[±]-:[±]-:•一muaeimsdbNorthwindpubsStud已「it垢关系图ter新連表⑹…所有任务（幻查看（町从这里创連窗口（凹帮助凹Hjsysfilesl詞sysforeigrikeys詞sysfulltextcatalogs詞sysfulltextriutify詞sysindexes詞sysinde:：■::keyssetsTiissicinsjerties:setssrences3Srsdbodbodbodbodbodbodbodbodbodbodbodbodbodbo丢统丢统丢统丢统系统系统系统系统垂统垂统垂统垂统垂统@1帘1葩警邙融|却孕叵］列名数据类型长度允许空Enocihar6ErL：dinechar10eeexchar2hirthdaydatetimm8►politychar20*b.c.保存并命名为pupil即可。4.将pupil表的sno设置为主键。为sname字段设置惟一性约束。步骤：a1&U髦禺丨帘|葩警⑷融丨埠孕叵］列名数据类型长度允许空Enu char6设置主键（笛插入列（密删除列（虫ar10ar2t旦tim旦8ar20任务（D ►全选凶保存⑸素引腿凶…关系』旦）...匚HE□（约束㈣…雇性回厂

6.为ssex和birthday设置检查性约束，要求ssex只能为'男'或'女'，birthday应该大于‘1987-1-1'。步骤：

7•为polity字段设置默认约束，值为'群众'8再创建一个学生选课表sc(snochar(6)notnull,cnochar(10)notnull,gradereal).为sc表创建外键约束，把sc表的sno和pupil表的sno关联起来，在这两个表之间创建一种制约关系。步骤：

pupil+ErL：iiTieeeexbirthdaypulitySC+snudLUgrade创建关系I创建关系凶关系名迅）：|FK_sc_pupil主键表（1） 外键表（JQ）pupil EC 3STluJk两创連中检查现存数据匮）两对复制强制关系0）磺对INSERT和UPDATE强制关系迢）厂级联更新相关的字段QJ）厂级联删除相关的记录0C）确定|取消|帮助⑪I

最后保存该关系图即可。实验3数据查询及维护【实验内容】1.已知一个名为pupil的表：(snochar(6)notnull,snamechar(10)notnull,ssexchar(2)notnull,birthdaydatetimenotnull,politychar(20),sdeptchar(20)notnull)利用insert语句向其中插入一条新的记录：(‘0007'，‘张三'，‘男'，‘1982-3-21'，‘团员',计算机系)insertintopupilvalues(‘0007'‘张三'‘男'‘1982-3-21'‘团员'‘计算机系')b.利用update语句将编号为'0004，的学生polity改为‘党员'：updatepupilsetpolity=党员wheresno=0004a.利用delete语句将‘186-1-1'以后出生的女同学记录删除。DeletefrompupilWherepolity>1986-1-1andssex=女2.针对pupil、sc、course三张表完成以下查询：a.查询所有学生的基本信息，并按出生日期升序排列select*frompupilorderbypolity查询女同学的信息和女同学的人数select*frompupilwheressex=女computecount(sno)查询所有男同学的年龄selectyear(getdate())-year(birthday)as年龄frompupil所有选课学生的姓名、选修课程名及成绩selectsname,cname,gradefrompupil,sc,coursewherepupil.sno=o=o不及格学生的姓名selectdistinctsnamefrompupil,sc,coursewherepupil.sno=o=oandgrade<60查询‘刘'姓学生的信息select*frompupilwheresnamelike„刘%g.查询polity为团员或党员的学生信息select*frompupilwherepolityin(„团员,党员)查询各门课程的选课人数selectcno,count(sno)fromscgroupbycno查询缺少成绩的学生的学号及课程号selectsno,cnofromscwheregradeisnull查询与‘刘成'同一个系的学生情况select*frompupilwheresdeptin(selectsdeptfrompupilwheresname=刘成))查询选修了课程名为‘MIS'的学生的学号和姓名selectsno,snamefrompupilwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewnerecname=MIS))实验4Transact—SQL程序设计实验内容】1・ 编写一个用户自定义函数fun_avggrade，要求根据输入的班级号和课程号，求此班级此课程的平均分。Createfunctionfun_avggrade(@classnumberaschar(10),@coursenumberaschar(10))returnsrealbegindeclare@avgresultselect@avgresult=avg(grade)fromscwheresubstring(sno,1,len(sno)-2)=@classnumberandcno=@coursenumberreturn@avgresultend编写一个用户自定义函数,完成以下功能：根据两个输入参数(成绩上限和成绩下限)，求sc数据表中满足输入条件的学生人数。Createfunctionfun_sumren(@maxlinereal,@minlinereal)returnsintbegindeclare@personcountasintselect@personcount=(selectcount(distinctsno)fromsc)fromscwheregrade<=@maxlinerealandgrade>=@minlinereturn@personcountend3.如何通过企业管理器和Transact_SQL语句对视图进行创建、修改和删除？通过企业管理器：创建步骤：扁SQLServerEnterpriseManager-「全一人‘冲（在％loel）"上）的新視国]裔文件（E）窗口㈤帮助（出盘⑥|西置匝置!徐野欧医％运行®添加表同…全选過分爼回显示宙格►隐藏窗格（虫保存⑸另存为辿J-LI 管理索引①…列JI性（③输出排序类型 1排序顺!SELECTFROM'血文件（日窗口（创帮助®|[nns®■•微学歌医％@pupil*（所有列）£IWsrL：iJTiH1/birthhy列别名表输出排序类型ErLupupilErL：diTiHpupilbirthdaypupi17SELECTsno.,sn^ie,birthd：ayFROMdbu.pupil最后保存该视图即可。修改步骤：进入该界面重新修改该视图即可。删除步骤：通过Transact_SQL语句：创建：createview视图名[withencryption]asselect_statement[withcheckoption]修改：利用alterview视图名删除：利用dropview视图名4、通过Transact_SQL语句创建一个视图，计算各个班级的各门课程的平均分。Createviewv_avggradeAsSelectsubstring(pupil.sno,1,len(pupil.sno)-2)as班级，Cnameas课程名称，Avg(grade)as平均分Frompupil,sc,gradeWherepupil.sno=o=oGroupbysubstring(pupil.sno,1,len(pupil.sno)-2),Cname5.通过Transact_SQL语句创建一个视图，显示‘高等数学'未过的学生的信息。Createviewv_gradenotpassAsSelectpupil.sno,sname,cname,gradeFrompupil,sc,courseWherepupil.sno=o=oandgrade<60Andcname=高等数学6.通过Transact_SQL语句创建一个视图,查询的数据为99521班学生的考试成绩。Createviewv_grade99521AsSelectpupil.sno,sname,cname,gradeFrompupil,sc,courseWherepupil.sno=o=oandleft(pupil.sno,5)=99521实验5存贮过程实验内容】创建一个存储过程stugradeinfo,查询班级、学号、姓名、性另I」、课程名称、分数createprocedurestugradeinfoasselect班级=substring(pupil.sno,l,len(pupil.sno)-2),pupil.snoas学号，snameas姓名，ssexas性另，cnameas课程名称,gradeas分数frompupil,sc,coursewherepupil.sno=o=o利用企业管理器创建一个存储过程stu_info,根据传入的编号,查询某学生的基本信息。白•豳数据库+白•豳数据库+jyt+master+model+msdb+Northwind+pubs+tempdb-金一人垢关泵图二|表_|控制台根目录-每］Microsoft5QLServers-d5QLServer组-令(local)(WindowsNT)窗文件（日操作迦查看泅工具①窗口（创帮助（出X曹血鳧1包:、Q^B®ra视图3于项目名称i^cTstuviewl阳sysconstraints知syssegments新逢存體过程（3…从这里创逹窗口（翌）刷新（日帮助凹ia数据嬴嘉

代码如下：createprocedurestu_info@stunumchar(10)asifnotexists(select*frompupilwheresno=@stunum)print„查无此人！！！！！，elsebeginselectsnoas编号，snameas姓名，ssexas性别，birthdayas出生日期，polityas政治面貌frompupilwheresno=@stunumend3.创建一个存储过程stu_age，根据输入的学生姓名，计算该学生的年龄。Createprocedurestu_age@stunamechar(10)asifnotexists(select*frompupilwheresname=@stuname)print„查无此人！！！！！，elsebegindeclare@ageintselect@age=floor(datediff(day,birthday,getdate())/365)frompupilwheresname=@stunameend4.创建一个存储过程stu_grade,根据输入的学生编号，返回其选课及其成绩。Createprocedurestu_grade@stunumchar(10)ifnotexists(select*frompupilwheresname=@stuname)print„查无此人！！！！！，elsebeginifnotexists(select*frompupil,scwherepupil.sno=sc.sno)print„无此学生的选课信息!!!！9elseselectsname,cname,gradefrompupil,course,scwherepupil.sno=sc.snoandcourse=cno=oend实验6触发器实验内容】1．创建一个insert触发器,当在pupil表中插入一条新记录时给出‘你已经插入了一条新记录！！！'的提示信息。步骤：話文件（巳操作過查看伯工具①窗口輕帮助（出匕*世］110黑噩XB1 1-F1 -8®控制台根目录Microsoft5QLServers白电5QL5已件已F《目日Jq（local）（WindowsNT）

日口数据库+圜+鞫+觀+謝+觀+圜+謝-圜表29个项目.iyrmasterrnodelrnsdbNorthwindpubsternpdb金一人一”|表鉗视图幻存储过程屢用户產角色J规则二I默认用户定殳的数揽用户定义的函藝+_|数据转换服务+_|管理名称］所有者1类型］创建日期§1匚ours已dbo用户2006-4-271圉dtpropertiesdbo系统2006-4-271dbo用户2006-4-271ZJscZJsyZJsy二印二即ZJsyZJsy二印二即ZJsyZJsyZJsy二印设计表（勺打开表（◎全文索引表（巳►所有任务（灯剪切⑴复制Q删除◎重命名画帮助凹二sysp已rmb引口「庄ZJsysprop已fti巳£ZJsysprotects圉sysreferencesr=adbodbodbodbodbo用戶系统系统系统管理索引（血…管理融发器（D…管理权限®…导入数据①…导出数据⑥…新建发布㈣…生成SQL脚本⑥…显不相关性IS），..dbodbodbo系统系统系统2006-4-2712000-8-61:2000-8-61：2000-8-61：2000-3-61：2000-8-61：2000-8-61：2000-8-61：2000-3-61：2000-0-61：2000-8-61：2000-8-61：2000-8-61：2000-3-61：2000-0-61：2000-8-61：2000-8-61：2000-8-61：程序代码如下：createtriggerstuinsertonpupilforinsertasdeclare@msgchar(50)set@msg=你已经插入了一条新记录！！！print@msg2.创建一个insert触发器,当在pupil表中插入一条新记录时，不允许在学号中出现重复的编号或出现空值。程序代码如下：createtriggerstuinsert1onpupilforinsertasdeclare@stunumchar(10)set@stunum=(selectsnofrominserted)if@stunumisnullbeginprint„学号不能为空！！！！rollbacktransactionendelsebegindeclare@numintset@num=(selectcount(*)frompupilwheresnoin(selectsnofrominserted))if@num>1beginprint„已经有相同的学号，请重新确认！！！！，rollbacktransactionendelseprint„数据录入成功！！！！，end3.创建一个insert触发器,当在sc表中插入一条新记录时，sno和cno必须是已经存在的学号和课程号，且grade应该在0 100之间。程序代码如下：createtriggerscinsertonscforinsertasdeclare@gradevaluerealset@gradevalue=(selectgradefrominserted)print„触发器开始工作 ’ifnotexists(selectsnofrominsertedwheresnoin(selectsnofrompupil))beginprint„无此学生的编号，请重新确认！！！，rollbacktransactionendelsebeginifnotexists(selectcnofrominsertedwherecnoin(selectcnofromcourse))beginprint„无此课程的编号，请重新确认!!!9rollbacktransactionendelsebeginif@gradevalue<0or@gradevalue>100beginprint„分数有误，请重新确认！！！！9rollbacktransactionendelseprint„数据插入成功！！！！endend4.创建一个after触发器,在pupil表中删除某学生的记录时，删除其相应的选课记录。程序代码如下：createtriggerstudeleteonpupilfordeleteasdeclare@stunumchar(10)select@stunum=snofromdeletedifnotexists(select*frompupil,scwherepupil.sno=sc.snoandpupil.sno=@stunum)print„无此学生的选课记录!!!!9elsebeginprint„开始查找并删除该学生相映的选课记录 ‘deletefromscwheresno=@stunumprint„该学生的选课记录已经删除！！！！！9end5.创建一个insteadof触发器，当在course表中上出记录时，不允许删除course表中的数据。程序代码如下：createtriggernotallowdeleteoncourseinsteadofdeleteasprint„insteadof触发器开始工作 9print„course表中的数据不允许删除！!!不能执行删除操作！!9实验7数据库的备份及恢复、导入和导出【实验内容】Q峠1匡］函X會囤Q峠1匡］函X會囤島1金^1:、0^8®C3备份0亍项目名称二控制台棍目录I物理位置口…埴0Microsoft5QLServers白号5QLServer组白••旳(local)(WindowsNT)日口繆据库+阂jyr+協master+阂model+協msdb+燼Northwind+阂pubs+tempdb-燼金一人对关系閣二|表彌观图各存储过程屋用户產角色J规则二默认r用户定岌的数揑毎用户定交的函婪+」数据转换服务電5QLServer代理猥制黠如猥制黠如聯U复安支Me+J-m E-.:[±1-:.[±1-:E1-备份数据库(创…查看㈣从这里创建窗口(世刷新㈢导出列表Q…帮助⑹n2．使用企业管理器对一数据库做一次完全数据备份。3．使用企业管理器恢复某一数据库的完全数据备份表新逹数据库（因…新逹（阻备份数据库（因…所有任务（幻还原数据库（E）…錐护计ME）...生成5QL脚本⑥…导入数据①…导出数据同…查看伯从这里创連窗口（也删除側刷新㈢导出列表◎，，曲1观團jyr10于项目_|控制台根目录-£]JMicrosoft5QLServers-u5QLServer组-(local)(WindowsNT)"-_J表新逹数据库（因…新逹（阻备份数据库（因…所有任务（幻还原数据库（E）…錐护计ME）...生成5QL脚本⑥…导入数据①…导出数据同…查看伯从这里创連窗口（也删除側刷新㈢导出列表◎，，曲1观團jyr10于项目_|控制台根目录-£]JMicrosoft5QLServers-u5QLServer组-(local)(WindowsNT)"-_J数据库+闕a+樹+樹+B+樹+樹+B-樹X图闌园|锻丨靠I宀①超（B乌雇性®帮助®二1规则二I默认r用尸定爻的数拒毎用户定殳的函輕+_j数据转换服务-_j管理+5QLServer代理1 7［i匕整沁收缩数据库（囚…分离数据库（出…脱机回复制订阅数据库（©…查看复制沖突（也…2SJ3332J还原:広数据库也〕要还原的第1于备份程］:厘性曰I取消|帮助|确定还原数齬库竝数显示数据库备份辺〕：2006-5-916:02:40-jyr备份 !r文件组或文件旧 r从设备胆］常规|选项丨置还原为数据库（H〕：还原类型备份集日期尢小还原自备份集名称02006-5-91...913KBC:\dat...iyr备份厂时点还原［Q］：4.导入一个文本文件到SQLServer数据库当中。」控制台根目录一£]JMicrosoft5QLServers-u5QLServer组-••吊(local)(WindowsNT)

"-」数据库-jyr昭关系图—I坤t7 新逹表(或・・表25于项目名称dtpropertiespupilsys匚cilumri^sys匚匚imm已詞^v^rlfipendsgroups所有任务（幻导入数据①…查看（与从这里创逹窗口（翌）导出数据⑥… LItextcatalogs c刷新（巳导出列