数据库实验报告22

.z.------专业资料-数据库原理及应用〔本科〕实验指导书-计算机信息教研室-二○一二年五月目录实验1创立数据库与数据表……………2实验2简单查询和连接查询……………10实验3嵌套查询和集合查询……………12实验4数据完整性………………………14时间：机房号：得分：实验1创立数据库与数据表实验目的：1．熟悉SQLSERVER环境；2．掌握数据库和数据表的创立和删除，实践SQL的CREATE、ALTER和DROP命令；3．掌握数据表的创立和数据记录的插入方法。实验内容：1.创立教学管理"J*GL〞数据库，并建立学生STUDENT、课程COURSE和选修SC三个数据表，各表中的数据如下所示：学生STUDENT：SnoSnameSse*SageSdept95001李勇M20CS95002刘晨F19IS95003王敏F18MA95004张立M18IS课程COURSE：CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据构造746数据处理27C语言64选修SC：SnoCnoGrade95001192950012859500138895002290950023802.创立供给系统"GY*T〞数据库，其中包括供给商表S、零件表P、工程工程表J和供给情况表SPJ四个数据表，各表中数据如下所示。供给商S：SNOSNAMECITYS1精益天津S2万胜S3S4丰泰隆S5康健零件P：PNOPNAMECOLORWEIGHTP1螺母红12P2螺栓绿17P3螺丝刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30工程工程J：JNOJNAMECITYJ1J2一汽J3弹簧厂天津J4造船厂天津J5机车厂J6无线电厂J7半导体厂供给情况SPJ：SNOPNOJNOQTYS1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500实验步骤：1．创立教学管理"J*GL〞数据库。(注：可采用可视窗体和SQL命令两种方法创立。以下是SQL命令方法)(1)创立教学管理"J*GL〞数据库。在命令窗格中输入如下命令，然后单击"运行〞钮执行该命令。CREATEDATABASEJ*GL;(2)清空命令窗格后，在J*GL数据库中建立STUDENT表，并插入记录，然后执行。CREATETABLESTUDENT(Snochar(5)notnullunique,Snamechar(20)notnullunique,Sse*char(1),Sageint,Sdeptchar(20));INSERTINTOSTUDENTVALUES(‘95001’,’李勇’,’M’,20,’CS’);INSERTINTOSTUDENTVALUES(‘95002’,’刘晨’,’F’,19,’IS’);INSERTINTOSTUDENTVALUES(‘95003’,’王敏’,’F’,18,’MA’);INSERTINTOSTUDENTVALUES(‘95004’,’张立’,’M’,18,’IS’);(3)清空查询窗格后，J*GL数据库中建立COURSE表，并插入记录，然后执行。CREATETABLECOURSE(Cnochar(2)notnullPRIMARYKEY(Cno),Cnamechar(20),Cpnochar(2),Ccreditsmallint);INSERTINTOCOURSEVALUES(‘1’,’数据库’,’5’,4);INSERTINTOCOURSEVALUES(‘2’,’数学’,’’,2);INSERTINTOCOURSEVALUES(‘3’,’信息系统’,’1’,4);INSERTINTOCOURSEVALUES(‘4’,’操作系统’,’6’,3);INSERTINTOCOURSEVALUES(‘5’,’数据构造’,’7’,4);INSERTINTOCOURSEVALUES(‘6’,’数据处理’,’’,2);INSERTINTOCOURSEVALUES(‘7’,’C语言’,’6’,4);(4)清空查询窗格后，J*GL数据库中建立SC表，并插入记录，然后执行。CREATETABLESC(Snochar(5)notnull,Cnochar(2)notnull,Gradesmallint,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESSTUDENT(Sno),FOREIGNKEY(Cno)REFERENCESCOURSE(Cno));INSERTINTOSCVALUES(‘95001’,‘1’,92);INSERTINTOSCVALUES(‘95001’,‘2’,85);INSERTINTOSCVALUES(‘95001’,‘3’,88);INSERTINTOSCVALUES(‘95002’,‘2’,90);INSERTINTOSCVALUES(‘95002’,‘3’,80);(5)查看三个表中的内容。分别执行以下命令，查看STUDENT、COURSE和SC数据表中的内容。SELECT*FROMSTUDENT;SELECT*FROMCOURSE;SELECT*FROMSC;(6)ALTERTABLE、DROPTABLE、DROPDATABASE命令运用。①向STUDENT表增加"入学时间〞列，其数据类型为日期型，用SELECT命令查看表中内容。ALTERTABLESTUDENTADDSCOMEDATETIME;SELECT*FROMSTUDENT;②删除"入学时间〞列，再用SELECT命令查看表中内容。ALTERTABLESTUDENTDROPCOLUMNSCOME；SELECT*FROMSTUDENT;③删除数据表。DROPTABLESC;DROPTABLESTUDENT;DROPTABLECOURSE;④删除数据库命令。切换到其他任意数据库，然后可用如下命令删除J*GL数据库。DROPDATABASEJ*GL;重复(1)~(4)过程，建立数据库备以后的查询使用。2．创立供给系统"GY*T〞数据库。(注：可采用可视窗体和SQL命令两种方法创立。)下面写出实现如下操作的SQL语句：(1)创立供给系统"GY*T〞数据库。CREATEDATABASEGY*T;(2)建立供给商表S。CREATETABLES(SNOchar(6),SNAMEchar(8),SCITYchar(20));INSERTINTOSVALUES('S1','精益','天津');INSERTINTOSVALUES('S2','万胜','');INSERTINTOSVALUES('S3','','');INSERTINTOSVALUES('S4','丰泰隆','');INSERTINTOSVALUES('S5','康健','');(3)建立零件表P。CREATETABLEP(PNOchar(6),PNAMEchar(8),COLORchar(8),WEIGHTINT);INSERTINTOPVALUES('P1','螺母','红','12');INSERTINTOPVALUES('P2','螺栓','绿','17');INSERTINTOPVALUES('P3','螺丝刀','蓝','14');INSERTINTOPVALUES('P4','螺丝刀','红','14');INSERTINTOPVALUES('P5','凸轮','蓝','40');INSERTINTOPVALUES('P6','齿轮','红','30');(4)建立工程工程表J。CREATETABLEJ(JNOCHAR(8),JNAMECHAR(8),CITYCHAR(20));INSERTINTOJVALUES('J1','','');INSERTINTOJVALUES('J2','一汽','');INSERTINTOJVALUES('J3','弹簧厂','天津');INSERTINTOJVALUES('J4','造船厂','天津');INSERTINTOJVALUES('J5','机车厂','');INSERTINTOJVALUES('J6','无线电厂','');INSERTINTOJVALUES('J7','半导体厂','');(5)建立供给情况表SPJ。CREATETABLESPJ(SNOCHAR(6),PNOCHAR(6),JNOCHAR(6),QTYINT);INSERTINTOSPJVALUES('S1','P1','J1','200');INSERTINTOSPJVALUES('S1','P1','J3','100');INSERTINTOSPJVALUES('S1','P1','J4','700');INSERTINTOSPJVALUES('S1','P2','J2','100');INSERTINTOSPJVALUES('S2','P3','J1','400');INSERTINTOSPJVALUES('S2','P3','J2','200');INSERTINTOSPJVALUES('S2','P3','J4','500');INSERTINTOSPJVALUES('S2','P3','J5','400');INSERTINTOSPJVALUES('S2','P5','J1','400');INSERTINTOSPJVALUES('S2','P5','J2','100');INSERTINTOSPJVALUES('S3','P1','J1','200');INSERTINTOSPJVALUES('S3','P3','J1','200');INSERTINTOSPJVALUES('S4','P5','J1','100');INSERTINTOSPJVALUES('S4','P6','J3','300');INSERTINTOSPJVALUES('S4','P6','J4','200');INSERTINTOSPJVALUES('S5','P2','J4','100');INSERTINTOSPJVALUES('S5','P3','J1','200');INSERTINTOSPJVALUES('S5','P6','J2','200');INSERTINTOSPJVALUES('S5','P6','J4','500');(6)应用INSERTINTO命令将相应数据写入到供给商表S、零件表P、工程工程表J和供给情况表SPJ四个数据表。时间：机房号：得分：实验2简单查询和连接查询实验目的：1．熟练掌握SQLServer查询分析器的使用方法，加深对标准SQL查询语句的理解。2．熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。实验内容：1．基于实验一创立的教学管理J*GL数据库，实现数据的根本查询操作。2．基于实验一创立的供给系统GY*T数据库，实现数据的根本查询操作。实验步骤：写出以下操作的SQL语句。1．在教学管理J*GL数据库中进展如下操作：(1)求数学系学生的**和姓名。SELECTSno,SnameFROMSTUDENTWHERESdept='MA'(2)求选修了课程的学生**。SELECTDISTINCTSnoFROMSC(3)求选修了数学课的学生**和成绩，并要求对查询结果按成绩降序排列，如果成绩一样则按**升序排列。SELECTSno,GradeFROMSCWHERE(Cno='2')ORDERBYGradeDESC,SnoASC(4)求选修数学课其且成绩在80-90之间的学生**和成绩，并将成绩乘以系数0.8输出。SElECTSno,Grade*0.8As乘分后FROMSCWHEREGrade>=80andGrade<=90andCno='2'(5)求数学系或计算机系姓刘的学生的信息。SELECT*FROMSTUDENTWHERE(Sdept='MS'ORSdept='IS')ANDSnameLIKE'%'(6)求缺少了成绩的学生的**和课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL(7)查询每个学生的情况以及他〔她〕所选修的课程。SELECT*FROMSTUDENT,SCWHERESTUDENT.Sno=SC.Sno8)求学生的**、、选修的课程名及成绩。SELECTSTUDENT.Sno,Sname,Cname,GradeFROMSTUDENT,SC,COURSEWHERESTUDENT.Sno=SC.SnoandCOURSE.Cno=SC.Cno(9)求选修数学课且成绩为90分以上的学生**、、及成绩。SELECTSTUDENT.Sno,Sname,GradeFROMSTUDENT,SC,COURSEWHERESTUDENT.Sno=SC.SnoandCOURSE.Cno=SC.CnoandSC.Cno='2'andGrade>=90(10)查询每一门课的间接先行课〔即先行课的先行课〕。SELECTa.Cno,b.CpnoFROMCOURSEa,COURSEbWHEREa.Cpno=b.Cno2．在供给系统GY*T数据库中进展如下操作：(1)求供给工程J1零件的供给商号SNO。SELECTDISTINCTSNOFROMSPJWHEREJNO='J1'(2)求供给工程J1零件P1的供给商号SNO。SELECTSNOFROMSPJWHEREJNO='J1'ANDPNO='P1'(3)统计每种零件的供给总量。SELECTJNO,SUM(QTY)AS每种零件的供给总量FROMSPJGROUPBYJNO时间：机房号：得分：实验3嵌套查询和集合查询实验目的：1．熟练掌握SQLServer查询分析器的使用方法，加深对标准SQL查询语句的理解。2．熟练掌握数据查询中的嵌套、分组、统计、计算和组合的操作方法。实验内容：1．基于实验一创立的教学管理J*GL数据库，实现数据的高级查询操作。2．基于实验一创立的供给系统GY*T数据库，实现数据的根本查询操作。实验步骤：写出以下操作的SQL语句。1．在教学管理J*GL数据库中进展如下操作：(1)求选修了数学的学生的**和姓名。SELECTSno,SnameFROMSTUDENTWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCOURSEWHERECname='数学'))(2)求数学课程成绩高于李勇的学生**和成绩。SELECTSTUDENT.Sno,GradeFROMSTUDENT,SC,COURSEWHERECOURSE.Cno=SC.CnoANDSC.Sno=STUDENT.SnoANDCOURSE.Cname='数学'ANDSC.Grade>(SELECTGradeFROMSTUDENT,SC,COURSEWHERECOURSE.Cno=SC.CnoANDSC.Sno=STUDENT.SnoANDCOURSE.Cname='数学'ANDSTUDENT.Sname='李勇')(3)求其他系中年龄小于计算机系年龄最大者的学生。SELECT*FROMSTUDENTWHERESage>ALL(SELECTSageFROMSTUDENTWHERESdept='CS')ANDSdept<>'CS'(4)求其他系中比计算机系学生年龄都小的学生。SELECT*FROMSTUDENTWHERESage<ANY(SELECTSageFROMSTUDENTWHERESDEPT='CS')ANDSDEPT<>'CS'(5)求选修了数学课的学生姓名。SELECTSnameFROMSTUDENTWHEREE*ISTS(SELECT*FROMSCWHERESTUDENT.Sno=SC.SnoANDCno='2')(6)求没有选修数学课的学生姓名。SELECTSnameFROMSTUDENTWHERE(NOTE*ISTS(SELECT*FROMSCWHERESTUDENT.Sno=SC.SnoANDCno='2'))(7)查询选修了全部课程的学生的姓名。SELECTSnameFROMSTUDENTWHERENOTE*ISTS(SELECT*FROMCOURSEWHERENOTE*ISTS(SELECT*FROMSCWHERESTUDENT.Sno=SC.SnoANDCOURSE.Cno=SC.Cno))(8)求至少选修了**为"95002〞的学生所选修的全部课程的学生**和姓名。SELECTSno,SnameFROMSTUDENTWHERE(E*ISTS(SELECT*FROMSCWHERESno=STUDENT.SnoANDCnoIN(SELECTCnoFROMSCWHERESno='95002')))(9)求选修各门课的人数及平均成绩。SELECTCOUNT(*)AS人数,AVG(Grade)AS平均成绩FROMSCGROUPBYCno(10)求选修课程在2门以上且都及格的学生号及总平均分。SELECTSno,AVG(GRADE)AS总平均分FROMSCGROUPBYSnoHAVINGCOUNT(Cno)>=2ANDMIN(GRADE)>=60(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。SELECTSno,AVG(Grade)AS总平均分FromSCWHEREE*ISTS(SELECT*FROMSTUDENTWHERENOTE*ISTS(SELECT*FROMSCWHERESno=STUDENT.SnoANDCno<60))GROUPBYSnoHAVING(COUNT(*)>=2)(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECTSno,COUNT(*)AS及格门数,AVG(Grade)AS平均成绩FROMSCWHEREGrade>=60GROUPBYSnoORDERBY平均成绩DESC,及格门数DESC(13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECTSno,COUNT(*)AS及格门数,AVG(Grade)AS平均成绩FROMSCWHEREE*ISTS(SELECT*FROMSTUDENTWHERENOTE*ISTS(SELECT*FROMSCWHERESno=STUDENT.SnoANDCno<60))GROUPBYSnoORDERBY平均成绩DESC,及格门数DESC2．在供给系统GY*T数据库中进展如下操作：(1)求供给工程J1红色零件的供给商号SNO。SELECTSNOFROMSPJWHEREPNOIN(SELECTPNOFROMPWHERECOLOR='红')ANDJNO='J1'(2)求零件供给总量在1000种以上的供给商名字。SELECTSNAMEFROMSWHERESNOIN(SELECTSNOFROMSPJGROUPBYSNOHAVINGSUM(QTY)>=1000)时间：机房号：得分：实验4数据完整性实验目的：1.了解SQLServer用约束来维护数据完整性的机制。2．掌握在企业管理器和查询分析器中如何创立、使用及删除约束的方法。实验内容：1．基于实验一创立的教学管理J*GL数据库，实现约束的创立、查看和删除操作。2．创立人事关系RSG*数据库及其中的职工数据表和部门数据表，并实现完整性约束条件的定义。实验步骤：约束是SQLServer提供的自动强制数据完整性的一种方法，它是通过定义列的取值规则来维护数据的完整性。常用的约束是NOTNULL〔非空〕、CHECK〔检查〕、UNIQUE〔惟一〕、PRIMARYKEY〔主键〕、FOREIGNKEY〔外键〕和DEFALT〔默认〕约束。1．在教学管理J*GL数据库中进展如下操作：(1)建立一个CITY表〔CityNo,CityName〕，将CityNo指定为主键。企业管理器方法：①在J*GL数据库中创立CITY表，其方法见实验1。②翻开企业管理器，展开J*GL数据库下的"表〞文件夹，在右边的详细窗格中选择要创立约束的表CITY，右击该表，选择"设计表〞命令，启动表设计器。在表设计器中，右击需要设为主键的字段CityNo，选择"设置主键〞命令其左侧将出现一个钥匙样图标。设置完毕，关闭表设计器。查询分析器方法：①用SQL语句创立主键，可使用CREATETABLE命令完成：CREATETABLECITY(CityNoCHAR(2)CONSTRAINTc1PRIMARYKEY,CityNameVARCHAR(30)NOTNULL)；②假设创立表时没有设置主键，也可以使用ALTERTABLE命令为已存在的表创立主键约束：ALTERTABLECITYADDCONSTRAINTc1PRIMARYKEY(CityNo)；其中c1为主键约束名称。(2)为J*GL数据库中的CITY表，为其"CityName〞创立惟一约束。企业管理器方法：①在企业管理器中，选择设置惟一约束的CITY表，翻开表设计器。②在表设计器中，右击CityName字段，选择"索引/键〞命令，出现"属性〞对话框，并进入"索引/键〞选项卡。③在该选项卡中，单击"新建〞按钮，然后在"索引名〞文本框中输入惟一约束名称，再从列名选项的下拉列表框中选择字段名称〔如"CityName〞〕，最后选择"创立UNIQUE〞复选框和"约束〞单项选择框，如图5.1所示。单击"关闭〞按钮，完成惟一约束的创立。图5.1创立惟一约束对话框图5.2设置检查约束查询分析器方法：为已存在的表创立惟一约束，SQL语句如下：ALTERTABLECITYADDCONSTRAINTc2UNIQUE(CityName)；(3)为J*GL数据库中的SC表，为其"GRADE〞创立检查约束。企业管理器方法：①在企业管理器中，选择设置检查约束的SC表，翻开表设计器。②在表设计器中，右击GRADE字段，选择"CHECK约束〞命令，出现"属性〞对话框，并进入"CHECK约束〞选项卡。③在该选项卡中，单击"新建〞按钮，然后在"约束名〞文本框中输入惟一约束名称，再在"约束表达式〞文本框中输入约束条件"GRADE>=0ANDGRADE<=100〞，如图5.2所示。单击"关闭〞按钮，完成检查约束的创立。查询分析器方法：为已存在的表创立检查约束，SQL语句如下：ALTERTABLESCADDCONSTRAINTc3CHECK(GRADE>=0ANDGRADE<=100)；(4)为J*GL数据库中的STUDENT表，为其"Sse*〞创立默认约束’M’。企业管理器方法：①在企业管理器中，选择设置默认约束的STUDENT表，翻开表设计器。②在表设计器中，选择Sse*字段，在默认值栏中输入"’M’〞即可，如图5.3所示。单击"关闭〞按钮，完成默认约束的创立。查询分析器方法：为已存在的表创立默认约束，SQL语句如下：ALTERTABLESTUDENTADDCONSTRAINTc4DEFAULT‘M’FORSse*；"默认值〞框"默认值〞框图5.3创立默认约束图5.4创立外键约束(5)为J*GL数据库中的SC表，为其"Sno〞创立外键约束。企业管理器方法：①在企业管理器中，选择设置外键约束的SC表，翻开表设计器。②鼠标右击，选择"关系〞命令，翻开"属性〞对话框的"关系〞选项卡。③在该选项卡中，单击"新建〞按钮，从主键表下拉列表中选择外键引用表STUDENT，并在其下拉列表框中选择外键引用的SNO列；