数据库试验答案

计算机与信息学院数据库实验报告专业班级学生姓名及学号课程教学班号任课教师实验指导教师实验地点2012~2013学年第二学期实验1使用向导创建和删除数据库实验目的熟悉SQLServer中SQLServerManagementStudio的环境了解SQLServer数据库的逻辑结构和物理结构掌握使用向导创建和删除数据库的方法实验要求熟练使用SSMS进行数据库的创建和删除操作。完成实验报告。实验内容设有一学籍管理系统，其数据库名为“EDUC”。初始大小为10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”,物理文件名为“student_data.mdf，存放路径为“E:\sql_data”。日志文件的逻辑文件名为“student_log”,物理文件名为“student_log.ldf”，存放路径为“E:\sql_data”。四、实验步骤1．使用SQLServerManagementStudio（简称SSMS）创建数据库。（1）启动SSMS在开始菜单中：所有程序－SQLServer2005－SQLServerManagementStudio单击“连接”按钮，便可以进入【SQLServerManagementStudio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。（2）建立数据库在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC1。2.使用向导删除上面建立的数据库。用SSMS删除建立的数据库EDUC。实验总结经过本次试验，对于SQLSERVER有了一个初步的认识，也对于在sqlserver上创建数据库有了一定的了解。实验2使用SQL语句创建和删除数据库实验目的1.了解SQLServer2005数据库的逻辑结构和物理结构。2.掌握使用SQL语句创建和删除数据库。二、实验要求1.熟练使用查询分析器进行数据库的创建和删除操作。2.完成实验报告。三、实验内容用SQL语句去创建和删除数据库。实验步骤1.创建数据库userdb1。根据步骤1的sql语句，写出创建实验1中数据库EDUC的sql语句，并建立数据库EDUC.createdatabaseEDUCon(name=EDUC_data,--数据文件的逻辑名称,注意不能与日志逻辑同名filename='E:\sql_data\student_data.mdf',--物理名称，注意路径必须存在size=10,--数据初始长度为Mmaxsize=50,--最大长度为Mfilegrowth=5%)--数据文件每次增长Mlogon(name=EDUC_log,filename='E:\sql_data\student_log.ldf',size=2,maxsize=5,filegrowth=1)3.用SQL语句删除步骤1建立的数据库userdb1。dropdatabaseuserdb1；五、实验总结掌握了用SQL语句创建和删除数据库，对SQLServer都有了进一步的认识和了解，发现其实SQL语句还是挺好用的。实验三分别用向导和SQL语句创建和删除表实验目的1．了解表的结构特点。2．了解SQLServer的基本数据类型。3．学会使用T-SQL语句创建表。二．实验要求完成SQL语句创建和删除基本表。完成实验报告。三.实验内容在数据库EDUC中，创建如下几个表：表3.1class表（班级信息表）字段名称类型允许空值主键说明ClsNOChar(6)NOTNULL是班号ClsNameVarchar(16)NOTNULL班名DirectorVarchar(10)NULL辅导员SpecialtyVarchar(30)NULL专业表3.2student表（学生信息表）字段名称类型允许空值主键说明SnoChar(8)NOTNULL是学号SnameVarchar(10)NOTNULL姓名SexChar(2)性别：男、女ClsNOChar(6)NULL班级的编号，（外键）参照表ClassSaddrVarchar(20)住址Sagenumeric(3,0)年龄，大于10，但小于30岁HeightDecimal(4,2)身高表3.3course表（课程信息表）字段名称类型允许空值主键说明CnoChar(4)NOTNULL是主键CnameVarchar(16)NOTNULL课程的名称Cpnochar(4)NULL先修课程的课程号（外键）,参照cnoCcreditTinyint学分表3.4sc表（学生选课成绩表）字段名称类型允许空值主键说明SnoChar(8)NOTNULL是学号，参照Student，与Cno组成主键CNOChar(4)NOTNULL是课程号，参照CoursegradeNumeric(4,1)NULL成绩四．实验步骤打开查询窗口，输入创建基本表的sql语句。student表：createtablestudent(snochar(8)NOTNULLprimarykey,--学号snamevarchar(10)NOTNULL,--姓名sexchar(2)check(sex='女'orsex='男'),--性别：男、女clsnochar(6),--班级编号，（外键）参照表classforeignkey(clsno)referencesclass(clsno),sadrrvarchar(20),--住址sagenumeric(3,0),--年龄，大于，但小于岁heightdecimal(4,2)--身高)class表createtableclass(clsnochar(6)NOTNULLprimarykey,--班号clsnamevarchar(16)NOTNULL,--班名directorvarchar(10)NULL,--辅导员specialtyvarchar(30)NULL--专业)course表createtablecourse(cnochar(4)NOTNULLprimarykey,--主键cnamevarchar(16)NOTNULL,--课程名称cpnochar(4),--先修课程的课程号（外键）,参照cnoccredittinyint--学分)sc表createtablesc(snochar(8)NOTNULL,--学号，参照Student，与Cno组成主键cnochar(4)NOTNULL,--课程号，参照Coursegradenumeric(4,1)NULL,--成绩primarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno),)五、实验总结通过本次试验，基本掌握了用sql语言创建数据库表的基本方法。实验四数据更新操作一.实验目的1.熟悉使用UPDATE/INSERT/DELETE语句进行表操作；2.能将这些更新操作应用于实际操作中去；二.实验要求完成下面的实验内容，并提交实验报告；在实验报告中附上相应的代码；三.实验内容(1)向表（Student）中插入数据SnoSnameSsexClsNOSaddrSageHeight20090101王军男CS01下关40#201.7620090102李杰男CS01江边路96#221.7220090306王彤女MT04中央路94#191.6519940107吴杪女PH08莲化小区74#181.60插入数据之后使用命令：Select*fromStudent;检查插入数据的正确性insertintostudentvalues('20090101','王军','男','cs01','下关#','20','1.76');insertintostudentvalues('20090102','李杰','男','cs01','江边路#','22','1.72');insertintostudentvalues('20090306','王彤','女','MT04','中央路#','19','1.65');insertintostudentvalues('19940107','吴杪','女','PH08','莲化小区#','18','1.60');select*fromstudent;（2）向表（Class）中插入数据ClsNOClsNameDirectorSpecialtyCS01计算机一班王宁计算机应用CS02计算机二班王宁计算机应用MT04数学四班陈晨数学PH08物理八班葛格物理插入数据之后使用命令：Select*fromClass;检查插入数据的正确性insertintoclassvalues('cs01','计算机一班','王宁','计算机应用');insertintoclassvalues('cs02','计算机二班','王宁','计算机应用');insertintoclassvalues('MT04','数学四班','陈晨','数学');insertintoclassvalues('PH08','物理八班','葛格','物理');select*fromclass;（3）向表（Course）中插入数据CnoCnameCpnoCredit0001高等数学Null60003计算机基础000130007物理00014插入数据之后使用命令：Select*fromCourse;检查插入数据的正确性insertintocoursevalues('0001','高等数学',NULL,'6');insertintocoursevalues('0003','计算机基础','0001','3');insertintocoursevalues('0007','物理','0001','4');select*fromcourse;（4）向表（SC）中插入数据SNOCNOGrade20090101000190200901010007862009010200018720090102000376200903060001872009030600039319940106000785insertintoscvalues('20090101','0001','90');insertintoscvalues('20090101','0007','86');insertintoscvalues('20090102','0001','87');insertintoscvalues('20090102','0003','76');insertintoscvalues('20090306','0001','87');insertintoscvalues('20090306','0003','93');insertintoscvalues('19940107','0007','85');select*fromsc;(5).对于student表，将所有班级号为‘CS01’的，并且年龄小于20岁的学生的班级号改为‘CS02’。updatestudentsetclsno='cs02'whereclsno='cs01'andsage<20;select*fromstudent;(6).对于student表，删掉所有年龄大于20岁，并且专业号为‘CS02’的学生的记录。对于student表，插入一条新记录，它的具体信息为，学号：20071101、姓名：张三、性别：男、年龄：19、班级编号：‘CS01’。由于表student数据较少，因而添加数据，用来满足要求：insertintostudentvalues('20102702','黄磊','男','cs01','下关#','18','1.78');insertintostudentvalues('20102712','李超','男','cs01','江边路#','19','1.72');insertintostudentvalues('20102708','陈明飞','男','cs02','中央路#','22','1.65');deletefromstudentwheresage>20andclsno='cs02';insertintostudent(sno,sname,ssex,sage,clsno)values('20071101','张三','男','19','cs01')(7).对于student表，将年龄最小的学生的家庭地址去掉。updatestudentsetsadrr=nullwheresage=(selectMIN(s.sage)fromstudents);(8).对于student表，将平均年龄最小的一个班级编号改为‘GL01’由于给定的class表中数据不足，因而添加数据以用来满足要求。insertintoclassvalues('gl01','计算机班','王','计算应用');updatestudentsetstudent.clsno='gl01'wherestudent.clsno=(selectt1.clsnofrom(selects1.clsno,avg(s1.sage)afromstudents1groupbys1.clsno)t1,(selectMIN(t2.a1)mfrom(selects.clsno,avg(s.sage)a1fromstudentsgroupbys.clsno)t2)t3wheret1.a=t3.m);五、实验总结通过本次试验，懂得了用sql语言来插入数据，以及一些简单的数据更新操作，也对sqlserver有了更多的了解。在实验的过程中，也遇到许多的问题，并在实验的过程中解决。比方说，在往表中插入数据时，先插的student表的数据，结果就发现出现错误，原来是student表中的clsno是class表的外码，需要先插入class表，student表内容才能插进去；还有在数据更新的过程中，比方说“对于student表，将平均年龄最小的一个班级编号改为‘GL01’”，由于class表中没有班号位“gl01”，因而出错，这是没有违反完整性约束条件，在插入一组班号为“gl01”时，该更新操作就能正确的运行。实验五创建和删除索引一.实验目的了解索引的类型和应用。学会用SQL语句对表创建和删除索引二.实验要求1.了解索引类型并比较各类索引的不同之处2.完成索引的创建和删除，并提交实验报告。三.实验内容1.用SQL语句分别建立以下索引(1)在student表的Sname列上建立普通降序索引Stusname。(2)在course表的Cname列上建立唯一索引Coucname。(3)在sc表的Sno(升序),Cno(升序)和grade(降序)三列上建立一个普通索引SCno。2.用SQL语句删除索引Student表的Stusname索引。四、实验步骤1.用SQL语句分别建立以下索引（1）在student表的sname列上建立普通降序索引Stusname。createindexstusnameonstudent(snamedesc);（2）在course表的cname列上建立唯一索引Coucname。createuniqueindexcoucnameoncourse(cname);(3)在sc表的Sno(升序),Cno(升序)和grade(降序)三列上建立一个普通索引SCno。createindexscnoonsc(sno,cno,gradedesc)2.删除索引删除Student表的Stusname索引。dropindexstusnameonstudent;五、实验总结通过本次试验，学会了用sql语言为基本表创建索引和删除索引。实验六数据查询实验目的1．观察查询结果,体会SELECT语句实际应用；2．要求学生能够在查询分析器中使用SELECT语句进行简单查询。3.熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。实验要求1.完成简单查询和连接查询操作，并验收实验结果提交实验报告实验内容所有的查询全部用Transact-SQL语句实现。此部分查询包括投影、选择条件表达、数据排序、使用临时表等。对EDUC数据库实现以下查询：查询计算机应用专业的学生学号和姓名；查询选修了课程的学生学号；查询选修课程0001且成绩在80－90之间的学生学号和成绩，并将成绩乘以系数0.75输出；查询计算机应用和数学专业的姓“张”的学生的信息。查询“0001”课程的成绩高于张三的学生学号和成绩；查询没有选修“0002”课程的学生姓名；实验步骤打开查询窗口，输入SQL查询语句，点击“执行”按钮，查看查询结果。查询计算机应用专业的学生学号和姓名；selectsno,snamefromstudent,classwherestudent.clsno=class.clsnoandclass.specialty='计算机应用';查询选修了课程的学生学号；selectsnofromscwheresnoisnotnull;查询选修课程0001且成绩在80－90之间的学生学号和成绩，并将成绩乘以系数0.75输出；selectsno,grade*0.75fromscwherecno='0001'andgradebetween80and90;查询计算机应用和数学专业的姓“张”的学生的信息。select*fromstudent,classwheresnamelike'张__'andstudent.clsno=class.clsnoand(specialty='计算机应用'orspecialty='数学');查询“0001”课程的成绩高于张三的学生学号和成绩；selectstudent.sno,gradefromstudent,scwherecno='0001'andstudent.sno=sc.snoandgradein(selectgradefromstudent,scwheresname='王军'andstudent.sno=sc.sno)查询没有选修“0002”课程的学生姓名；selectdistinctsnamefromstudent,scwherestudent.sno=sc.snoandcno!='0002五、实验总结通过本次试验，复习用sql语言处理数据查询的一些方法，更好的掌握数据的查询。实验七使用聚集函数的SELECT语句实验目的1.熟练掌握数据查询中的分组、统计、计算和组合的操作方法。2.进一步掌握SQLServer查询分析器的使用，加深对SQL语言的嵌套查询语句的理解。实验要求1.在实验之前做好准备。2.完成实验，并验收实验结果提交实验报告。实验内容在数据库EDUC中用SQL语句实现如下查询：1)求学生的总人数。2)求选修了课程的学生人数。3)求课程的课程号和选修该课程的人数。4)求选修课程超过2门课的学生学号。四.实验步骤1)求学生的总人数.selectCOUNT(*)fromstudent;2)求选修了课程的学生人数。selectCOUNT(distinctsno)fromsc;求课程的课程号和选修该课程的人数。selectCOUNT(cno)fromscgroupbycno;4)求选修课程超过2门课的学生学号。selectsnofromscgroupbysnohavingCOUNT(*)>1;实验总结通过本次试验对聚集函数的SELECT语句使用更加熟悉。实验八视图的定义与使用实验目的熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系；3.学习灵活熟练的进行视图的操作，认识视图的作用。二．实验要求在实验开始之前做好准备工作。实验之后提交实验报告，思考视图和基本表的区别三．实验内容1.定义视图在EDUC数据库中，已Student、Course和SC表为基础完成一下视图定义:将Student,Course和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_SC_G；定义一个反映学生出生年份的视图V_YEAR；将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G；将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G。2.使用视图查询以上所建的视图结果。查询平均成绩为90分及以上的学生学号、姓名和成绩；查询课程成绩大于平均成绩的学生学号、课程号、成绩和平均成绩；查询1994年出生的学生学号和姓名。实验步骤1.定义视图1)将Student,Course和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_SC_G.createviewv_sc_gasselectstudent.sno,sname,o,cname,gradefromsc,student,coursewheresc.sno=o=o;select*fromv_sc_g;dropviewv_sc_g;定义一个反映学生出生年份的视图V_YEAR.createviewv_yearasselectsno,sname,2013-sageagefromstudent;select*fromv_year;3)将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G.createviewv_avg_s_gasselectstudent.sno,COUNT(cno)cnum,avg(grade)avg_agefromsc,studentwheresc.sno=student.snogroupbystudent.sno;4)将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G。createviewv_avg_c_gasselectcno,count(*)ascnum,avg(grade)avg_gradefromscgroupbycno;select*fromv_avg_c_g;2.使用视图查询以上所建的视图结果。查询平均成绩为90分及以上的学生学号、姓名和成绩；selectstudent.sno,sname,avg_agefromv_avg_s_gs,studentwheres.sno=student.snoands.avg_age>='90';.查询科目成绩大于平均成绩的学生学号、课程号、成绩和平均成绩；selectstudent.sno,sname,cno,grade,avg_agefromsca,student,v_avg_s_gwherea.sno=student.snoandStudent.sno=v_avg_s_g.snoandnotexists(select*fromscb,v_avg_c_gwherea.Grade<v_avg_c_g.avg_gradeanda.Cno=b.Cno)查询1994年出生的学生学号和姓名。selectsname,snofromv_yearwhereage='1994';实验总结视图的使用很简单，有时使查询变得更加简单，和表的使用很像。视图就是一种很特殊的表。实验九触发器的创建与使用一、实验目的本实验的目的是使学生进一步掌握SQLServer触发器的创建及使用方法，加深SQL触发器的理解。通过对数据的更新操作体会其触发器的作用。二、实验要求1.在实验之前做好准备2.试验之后提交实验报告，并验收实验结果三、实验内容1.在班级表class中增加班级人数（c_total）字段2.创建INSERT触发器t_inst_stu3.创建DELETE触发器t_dele_stu：在学生表(student)中删除一名学生，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动减1。4.创建UPDATE触发器t_update_stu。5.体会触发器的作用。四、实验步骤1.在班级表class中增加班级人数（c_total）字段，默认值是0；并手动统计人数作为该字段的值；altertableclassaddc_totalintNOTNULLDEFAULT0--altertableclassdropcolumnc_total--删除一个字段2.创建INSERT触发器t_inst_stu：在学生表(student)中每新增一名学生，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动加1。createtrigger[dbo].[t_inst_stu]on[dbo].[student]forinsertasbegindeclare@clschar(6)select@cls=ClsNofrominsertedifexists(select*fromstudentwherestudent.ClsNo=@clsandstudent.ClsNoisnotnull)print'开始查找并将班级人数加1……..'updateclasssetc_total=c_total+1whereClsNo=@clsprint'班级人数已增加！！！！'End3.创建DELETE触发器t_dele_stu：在学生表(student)中删除一名学生，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动减1。createtrigger[dbo].t_dele_stuon[dbo].studentfordeleteasbegindeclare@clschar(6)select@cls=clsnofromdeletedifexists(select*fromstudentwherestudent.clsno=@clsandstudent.clsnoisnotnull)print'开始查找并将班级人数减……..'updateclasssetc_total=c_total-1whereclass.clsno=@clsprint'班级人数已减少！！！！'End4.创建UPDATE触发器t_update_stu:当某学生所在班号发生变化时（即调到另一班级后），将其原先所在班级的人数（c_total）减1，将新调入的班级班级的人数（c_total）加1。createtriggert_update_stuonstudentforupdateasbegindeclare@oldClsNOchar(8),@newClsNOchar(8);select@oldClsNOfromdeleted;select@newClsNOfrominserted;if@oldClsNOisnotnullupdateclasssetc_total=c_total+1whereClsNO=@oldClsNO;if@newClsNOisnotnullupdateclasssetc_total=c_total-1whereClsNO=@newClsNO;End5.触发器的作用：1)查看班级表（class）2）对学生表(student)分别插入（INSERT）、删除（DELETE）和修改（UPDATE）元组insertintostudentvalues('20102680','古鑫','男','cs01','合工大','20','1.70');deletefromstudentwheresname='古鑫';3）再次查看班级表（class），检查其数据的变化情况，体会触发器的作用。select*fromclass;

五.实验总结对触发器的创建把握的不是太好，还是需要多加锻炼。实验十存储过程的创建与使用一、实验目的本实验的目的是使学生进一步掌握SQLServer存储过程的创建及使用方法，加深对SQL存储过程的理解。通过对存储过程的调用体会其作用。二、实验要求1.在实验之前做好准备2.试验之后提交实验报告，并验收实验结果三、实验内容1、创建一个不带参数的存储过程p_stu_info1，实现对满足要求的学生基本信息的查询。2、创建一个带有参数的存储过程p_stu_info2，实现对满足要求的学生基本信息的查询。3、创建一个存储过程p_stu_info3,根据输入的学号，查询某学生的基本信息。4、创建一个存储过程p_stu_grade,根据输入的学号，返回其选课及其成绩。5.使用SQL语句分别执行p_stu_info1、p_stu_info2、p_stu_info3和p_stu_grade，并将结果显示出来。四、实验步骤1.建立不带参数的存储过程p_stu_info1（选择所有年龄<21岁的男同学）。createprocedurep_stu_infolasbeginselectdistinct*fr