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学生选修课程的总学分。1、设计并在MSSQLServer2000中创建以上表结构，并设置完整性约束。2、查询所有选修课程的学生的基本信息、课程信息及相应的考试成绩。3、查询所有学生的信息，若已选课就还要给出选修课程的信息及考试成绩。4、查询所有课程的信息，若课程有学生选修就还要给出选修课程的学生的信息及考试成绩。5、查询选修名为“数据库”的课程的考试成绩最高的学生的信息。6、对Student的age创建规则，满足18≤age≤25，并给出验证实例及验证结果。7、创建触发器：当学生选修一门新的课程后，Statistics表的cNumber自动加1，且creditNumber自动增加新选课程的学分。8、创建视图：找出所有已修学分超过6、所修课程平均分不低于60的学生的基本信息、以及所修课程的平均分。按照平均分排序，若平均分相同按照学号排序。9、对于如下一组数据操作：(1)select*fromcourse;(2)select*fromcoursewherecname=’数据库’(3)select*fromcoursewherecredit=3(4)select*fromcoursewherecredit2andcredit5(5)updatecoursesetcredit=3wherecredit=2对Course表的credit属性创建索引，并给出上述查询在创建了该索引后的执行计划；去掉Course表的credit属性上创建的索引，并给出上述查询的执行计划。对上述有无索引个查询执行的情况进行对比分析，总结出什么时候索引有效？10、将表Student、Course、Enroll作内连接的结果发布为HTML网页格式。二、实验步骤及分析过程1、设计并在MSSQLServer2000中创建以上表结构，并设置完整性约束。（1）创建数据库SC。SQL语句为：createdatabaseSC创建后的数据库如图1。（2）创建表创建学生表：createtableStudent(snovarchar(10)notnullprimarykey,snamevarchar(50)notnull,ageint,sexvarchar(2)notnull)创建课程表：createtableCourse(cnovarchar(10)notnullprimarykey,cnamevarchar(50)notnull,creditintnotnull)创建注册表：createtableEnroll(snovarchar(10)notnullreferencesStudent(sno),cnovarchar(10)notnullreferencesCourse(cno),gradeint,primarykey(sno,cno))图1创建数据库的结果创建选课情况表：createtableStatisticss(snovarchar(10)notnullprimarykeyreferencesStudent(sno),cNumberintnotnull,creditSumintnotnull,)创建后的表如图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)图2创建表的结果（4）查看表的内容select*fromStudent结果如图3。select*fromCourse结果如图4。select*fromEnroll结果如图5。2、查询所有选修课程学生的基本信息、课程信息及相应的考试成绩。SQL语句为：selecta.sno,a.sname,a.age,a.sex,b.cno,b.cname,b.credit,c.gradefromStudenta,Courseb,Enrollcwherea.sno=c.snoandb.cno=c.cno执行结果如图6所示。图3表Student中的数据图4表Course中的数据图5表Enroll中的数据图6选修课程的学生、课程及成绩信息执行结果分析：只显示了选了课程的学生信息。3、查询所有学生的信息，若已选课就还要给出选修课程的信息及考试成绩。SQL语句为：selecta.sno,a.sname,a.sex,a.age,d.cno,d.cname,d.credit,d.gradefromStudentaleftouterjoin(selectb.cno,b.cname,b.credit,c.grade,c.snofromCourseb,Enrollcwhereb.cno=c.cno)dona.sno=d.sno执行结果如图7所示。执行结果分析：不但列出选了课程的学生而且还列出来没选课程的学生信息。4、查询所有课程的信息，若课程有学生选修就还要给出选修课程的学生的信息及考试成绩。SQL语句为：selecta.cno,a.cname,a.credit,d.sno,d.sname,d.sex,d.age,d.gradefromCoursealeftouterjoin(selectb.sno,b.sname,b.age,b.sex,c.grade,c.cnofromStudentb,Enrollcwhereb.sno=c.sno)dona.cno=d.cno执行结果如图8所示。图7所有学生选课情况及成绩信息图8所有课程、选课的学生及成绩信息执行结果分析：不但列出被选课程的信息和学生选课情况而且还列出来没被选课程的信息。5、查询选修名为“数据库”的课程的考试成绩最高的学生的信息。（1）先查看选了数据库课程的学生信息。SQL语句为：selecta.sno,a.sname,a.sex,a.age,b.cname,c.gradefromStudenta,Courseb,Enrollcwherea.sno=c.snoandb.cno=c.cnoandb.cname='数据库'执行结果如图9所示。执行结果分析：有三位学生选修了数据库课程。（2）查询选修“数据库”课程的考试成绩最高的学生的信息。SQL语句为：selecta.sno,a.sname,a.sex,a.age,d.cname,d.gradefromStudenta,(selectb.cno,b.cname,c.grade,c.snofromCourseb,Enrollcwhereb.cno=c.cnoandb.cname='数据库')dwherea.sno=d.snoandd.grade=(selectmax(grade)from(selectb.cno,b.cname,c.gradefromCourseb,Enrollcwhereb.cno=c.cno)dwhered.cname='数据库')执行结果如图10所示。执行结果分析：与图9结合比较，图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(b.cno)ascountCno,sum(c.credit)assumCreditfrominsertedbinnerjoinCourseconb.cno=c.cnogroupbyb.sno)dona.sno=d.snoinsertStatisticssselectb.sno,count(b.cno)ascountCno,sum(c.credit)assumCreditfrominsertedbinnerjoinCourseconb.cno=c.cnowheresnonotin(selectdistinctsnofromStatisticss)groupbyb.snoend没有在Enroll表插入任何数据时表为空，如图11所示。在Enroll表插入以下数据：insertintoEnrollvalues('2008001','005',70)insertintoEnrollvalues('2008006','001',59)插入数据后的结果如图12所示。执行结果分析：当在Enroll表中插入选课情况时，在表上建立的触发器insert_statisticss就被触发，进行cNumber自动加1和creditNumber自动增加新选课