sql数据库实例(数据库入门)

/数据库设计及应用实验实验内容创建数据库：包括Student,Course,Enroll,Statistics表,表的结构如下：Student<sno,sname,age,sex>Course<cno,cname,credit>Enroll<sno,cno,grade>Statistics<sno,cNumber,creditSum>说明：cNumber是sno学生选修课程的数目；creditSum是sno学生选修课程的总学分。设计并在MSSQLServer2000中创建以上表结构,并设置完整性约束。查询所有选修课程的学生的基本信息、课程信息及相应的考试成绩。查询所有学生的信息,若已选课就还要给出选修课程的信息及考试成绩。查询所有课程的信息,若课程有学生选修就还要给出选修课程的学生的信息及考试成绩。查询选修名为"数据库"的课程的考试成绩最高的学生的信息。对Student的age创建规则,满足18≤age≤25,并给出验证实例及验证结果。创建触发器：当学生选修一门新的课程后,Statistics表的cNumber自动加1,且creditNumber自动增加新选课程的学分。创建视图：找出所有已修学分超过6、所修课程平均分不低于60的学生的基本信息、以及所修课程的平均分。按照平均分排序,若平均分相同按照学号排序。对于如下一组数据操作：select*fromcourse;select*fromcoursewherecname=’数据库’select*fromcoursewherecredit=3select*fromcoursewherecredit>2andcredit<5updatecoursesetcredit=3wherecredit=2对Course表的credit属性创建索引,并给出上述查询在创建了该索引后的执行计划；去掉Course表的credit属性上创建的索引,并给出上述查询的执行计划。对上述有无索引个查询执行的情况进行对比分析,总结出什么时候索引有效？将表Student、Course、Enroll作内连接的结果发布为HTML网页格式。实验步骤及分析过程1、设计并在MSSQLServer2000中创建以上表结构,并设置完整性约束。〔1创建数据库SC。SQL语句为：createdatabaseSC创建后的数据库如图1。图1创建数据库图1创建数据库的结果〔2创建表创建学生表：createtableStudent< snovarchar<10>notnullprimarykey, snamevarchar<50>notnull, ageint, sexvarchar<2>notnull >创建课程表：createtableCourse< cnovarchar<10>notnullprimarykey, cnamevarchar<50>notnull, creditintnotnull >创建注册表：createtableEnroll< snovarchar<10>notnull referencesStudent<sno>, cnovarchar<10>notnull referencesCourse<cno>, gradeint, primarykey<sno,cno> >创建选课情况表：createtableStatisticss< snovarchar<10>notnullprimarykey referencesStudent<sno>, cNumberintnotnull, creditSumintnotnull, >创建后的表如图2。图2图2创建表的结果〔3插入数据Student表：insertintoStudentvalues<'2008001','李贵斌',22,'男'>insertintoStudentvalues<'2008002','冉从宝',21,'男'>insertintoStudentvalues<'2008003','杨文学',20,'男'>insertintoStudentvalues<'2008004','杨璐',22,'女'>insertintoStudentvalues<'2008005','李小萌',20,'女'>Course表：insertintoCoursevalues<'001','数据库',4>insertintoCoursevalues<'002','java',3>insertintoCoursevalues<'003','操作系统',5>insertintoCoursevalues<'004','软件工程',4>insertintoCoursevalues<'005','计算机英语',2>Enroll表：insertintoEnrollvalues<'2008001','001',89>insertintoEnrollvalues<'2008002','003',98>insertintoEnrollvalues<'2008003','002',85>insertintoEnrollvalues<'2008001','004',88>insertintoEnrollvalues<'2008004','001',89>insertintoEnrollvalues<'2008002','001',90>insertintoEnrollvalues<'2008003','003',78>insertintoEnrollvalues<'2008002','004',79>〔4查看表的内容select*fromStudent结果如图3。图3图3表Student中的数据select*fromCourse结果如图4。图4图4表Course中的数据select*fromEnroll结果如图5。图5图5表Enroll中的数据2、查询所有选修课程学生的基本信息、课程信息及相应的考试成绩。SQL语句为：selecta.sno,a.sname,a.age,a.sex,o,ame,b.credit,c.gradefromStudenta,Courseb,Enrollc wherea.sno=o=o执行结果如图6所示。图6选修课程的学生、课程及成绩信息图6选修课程的学生、课程及成绩信息执行结果分析：只显示了选了课程的学生信息。3、查询所有学生的信息,若已选课就还要给出选修课程的信息及考试成绩。SQL语句为：selecta.sno,a.sname,a.sex,a.age,o,ame,d.credit,d.gradefromStudentaleftouterjoin <o,ame,b.credit,c.grade,c.sno fromCourseb,Eo=o>dona.sno=d.sno执行结果如图7所示。图7图7所有学生选课情况及成绩信息执行结果分析：不但列出选了课程的学生而且还列出来没选课程的学生信息。4、查询所有课程的信息,若课程有学生选修就还要给出选修课程的学生的信息及考试成绩。SQL语句为：o,ame,a.credit,d.sno,d.sname,d.sex,d.age,d.gradefromCoursealeftouterjoin <selectb.sno,b.sname,b.age,b.sex,c.grade,o fromStudentb,Enrollcwhereb.sno=c.sno>o=o执行结果如图8所示。图图8所有课程、选课的学生及成绩信息执行结果分析：不但列出被选课程的信息和学生选课情况而且还列出来没被选课程的信息。5、查询选修名为"数据库"的课程的考试成绩最高的学生的信息。〔1先查看选了数据库课程的学生信息。SQL语句为：selecta.sno,a.sname,a.sex,a.age,ame,c.gradefromStudenta,Courseb,Enrollc wherea.sno=o=ame='数据库'执行结果如图9所示。图9图9选修"数据库"课程的学生信息执行结果分析：有三位学生选修了数据库课程。〔2查询选修"数据库"课程的考试成绩最高的学生的信息。SQL语句为：selecta.sno,a.sname,a.sex,a.age,ame,d.gradefromStudenta, <o,ame,c.grade,c.snofromCourseb,Enrollc o=ame='数据库'>dwherea.sno=d.snoandd.grade=<selectmax<grade> from<o,ame,c.grade fromCourseb,Eo=o>d ame='数据库'>执行结果如图10所示。图10图10选修"数据库"课程成绩最高学生执行结果分析：与图9结合比较,图10了列出了选修"数据库"课程成绩最高学生信息。6、对Student的age创建规则,满足18≤age≤25,并给出验证实例及验证结果。〔1创建规则并且绑定它。SQL语句为：createruleage_ruleas @age>=18and@age<=25sp_bindruleage_rule,'Student.age'〔2插入数据检验。insertintoStudentvalues<'2008006','李志锋',20,'男'>〔所影响的行数为1行insertintoStudentvalues<'2008007','李小龙',32,'男'>insertintoStudentvalues<'2008008','杨一民',17,'男'>服务器:消息513,级别16,状态1,行1列的插入或更新与先前的CREATERULE语句所强制的规则冲突。该语句已终止。冲突发生于数据库'SC',表'Student',列'age'。语句已终止。执行结果分析：规则设置成功。7、创建触发器：当学生选修一门新的课程后,Statistics表的cNumber自动加1,且creditNumber自动增加新选课程的学分。SQL语句为：createtriggerinsert_statisticssonEnrollforinsertasbeginupdateasetcNumber=cNumber+d.countCno,creditSum=creditSum+d.sumCreditfromStatisticssainnerjoin <selectb.sno,count<o>ascountCno,sum<c.credit> assumCreditfrominsertedb innerjoinCo=o groupbyb.sno>dona.sno=d.snoinsertStatisticssselectb.sno,count<o>ascountCno,sum<c.credit>assumCreditfrominsertedbinnerjoinCo=o wheresnonotin<selectdistinctsnofromStatisticss> groupbyb.snoend没有在Enroll表插入任何数据时表为空,如图11所示。图1图11表Statisticss的初始信息在Enroll表插入以下数据：insertintoEnrollvalues<'2008001','005',70>insertintoEnrollvalues<'2008006','001',59>插入数据后的结果如图12所示。图12图12表Statisticss的信息执行结果分析：当在Enroll表中插入选课情况时,在表上建立的触发器insert_statisticss就被触发,进行cNumber自动加1和creditNumber自动增加新选课程的学分。8、创建视图：找出所有已修学分超过6、所修课程平均分不低于60的学生的基本信息、以及所修课程的平均分。按照平均分排序,若平均分相同按照学号排序。SQL语句为：createviewTranscriptasselecttop10d.sno,d.sname,d.sex,d.age,d.avg_grade,d.creditSumfrom<selecta.sno,a.sname,a.age,a.sex,avg<grade> asavg_grade,sum<credit>ascreditSum fromStudenta,Courseb,Enrollc wherea.sno=o=o groupbya.sno,a.sname,a.sex,a.age >dwhereavg_grade>=60andcreditSum>6orderbyavg_grade,d.snoselect*fromTranscript执行结果如图13所示。图13Transcript图13Transcript视图表执行结果分析：列出的学生都按平均成绩大小从低到高排列且平均分都大于60.当平均分相同时,表中冉从宝和杨璐就按照学号大小从小低到高排列。9、对照对Course表的credit属性创建索引和去掉Course表的credit属性上创建的索引查询的执行计划。对Course表的credit属性创建索引为：createindexcredit_indexonCourse<credit>对比有无索引credit_index情形下,"select*fromCourse"的查询的执行计划分别如图14、图15所示。图14图14有索引情形下的查询的执行计划图15无索引情形下的查询的执行计划结果分析：由图14,、图15可知当查询是针对整个表进行,创建索引和不创建索引,查询的执行计划都是一样的。〔2对比有无索引credit_index情形下,"select*fromcoursewherecname=’数据库’"的查询的执行计划分别如图16、图17所示。图16图16有索引情形下的查询的执行计划图17无索引情形下的查询的执行计划结果分析：由图16,、图17可知当条件针对的字段不是创建了索引的那个字段时,索引是无效的。有索引和无索引,查询的执行计划都是一样的。〔3对比有无索引credit_index情形下,"select*fromcoursewherecredit=3"的查询的执行计划分别如图18、图19所示。图18图18有索引情形下的查询的执行计划图19无索引情形下的查询的执行计划结果分析：由图18,、图19可知where条件为credit的属性,索引起效。有索引的查询效率比没有索引的查询效率高数十倍。〔4对比有无索引credit_index情形下,"select*fromcoursewherecredit>2andcredit<5”的查询的执行计划分别如图20、图21图20图20有索引情形下的查询的执行计划图21无索引情形下的查询的执行计划结果分析：由图20,、图21可知where条件为credit的属性,索引起效。有索引的查询效率比没有索引的查询效率高数十倍。〔5对比有无索引credit_index情形下,"updatecoursesetcredit=3wherecredit=2”图22图22有索引情形下的查询的执行计划图23无索引情形下的查询的执行计划结果分析：由图20,、图21可知where条件为credit的属性,索引起效。有索引的查询效率比没有索引的查询效率高数十倍。索引小结：=1\*GB3①索引是对数据库表中的一列或多列的值进行排序的结构,有助于更快地获取信息。=2\*GB3②只有当经常查询索引列中的数据时,才需要创建索引。=3\*GB3③折衷：索引提高查询速度vs.索引占用磁盘空间,降低数据更新执行速度。=4\*GB3④若应用中大多数是数据更新操作〔插入、删除和修改。则应限制索引的数量。10、将表Student、Course、Enroll作内连接的结果发布为HTML网页格式。〔1在企业管理器的控制台