sql数据库实例数据库入门.doc
数据库设计与应用实验一、 实验内容创建数据库：包括Student, Course, Enroll, Statistics表,表的结构如下：Student <sno, sname, age, sex>Course <o, ame, credit>Enroll <sno, o, grade>Statistics <sno, umber, creditSum>说明：umber是sno学生选修课程的数目；creditSum是sno学生选修课程的总学分.1、 设计并在MS SQL Server 2000中创建以上表结构,并设置完整性约束.2、 查询所有选修课程的学生的基本信息、课程信息与相应的考试成绩.3、 查询所有学生的信息,若已选课就还要给出选修课程的信息与考试成绩.4、 查询所有课程的信息,若课程有学生选修就还要给出选修课程的学生的信息与考试成绩.5、 查询选修名为"数据库"的课程的考试成绩最高的学生的信息.6、 对Student的age创建规则,满足18age25,并给出验证实例与验证结果.7、 创建触发器：当学生选修一门新的课程后,Statistics表的umber自动加1,且creditNumber自动增加新选课程的学分.8、 创建视图：找出所有已修学分超过6、所修课程平均分不低于60的学生的基本信息、以与所修课程的平均分.按照平均分排序,若平均分相同按照学号排序.9、 对于如下一组数据操作：(1) select * from course;(2) select * from course where ame=数据库(3) select * from course where credit=3(4) select * from course where credit>2 and credit<5(5) update course set credit=3 where credit=2对Course表的credit属性创建索引,并给出上述查询在创建了该索引后的执行计划；去掉Course表的credit属性上创建的索引,并给出上述查询的执行计划.对上述有无索引个查询执行的情况进行对比分析,总结出什么时候索引有效？10、 将表Student、Course、Enroll作内连接的结果发布为HTML网页格式.二、 实验步骤与分析过程1、设计并在MS SQL Server 2000中创建以上表结构,并设置完整性约束.1创建数据库SC.SQL语句为：create database SC 创建后的数据库如图1.图1 创建数据库的结果2创建表创建学生表：create table Student< sno varchar<10> not null primary key,sname varchar<50> not null,age int,sex varchar<2> not null>创建课程表：create table Course< o varchar<10> not null primary key,ame varchar<50> not null,credit int not null>创建注册表：create table Enroll< sno varchar<10> not null references Student<sno>,o varchar<10> not null references Course<o>,grade int,primary key <sno,o>>创建选课情况表：create table Statisticss< sno varchar<10> not null primary keyreferences Student<sno>,umber int not null,creditSum int not null,>创建后的表如图2.图2 创建表的结果3插入数据Student表：insert into Student values <'2008001','李贵斌',22,'男'>insert into Student values <'2008002','冉从宝',21,'男'>insert into Student values <'2008003','杨文学',20,'男'>insert into Student values <'2008004','杨璐',22,'女'>insert into Student values <'2008005','李小萌',20,'女'>Course表：insert into Course values <'001','数据库',4>insert into Course values <'002','java',3>insert into Course values <'003','操作系统',5>insert into Course values <'004','软件工程',4>insert into Course values <'005','计算机英语',2>Enroll表：insert into Enroll values <'2008001','001',89>insert into Enroll values <'2008002','003',98>insert into Enroll values <'2008003','002',85>insert into Enroll values <'2008001','004',88>insert into Enroll values <'2008004','001',89>insert into Enroll values <'2008002','001',90>insert into Enroll values <'2008003','003',78>insert into Enroll values <'2008002','004',79>4查看表的内容select * from Student结果如图3.图3表Student中的数据select * from Course 结果如图4.图4 表Course中的数据select * from Enroll 结果如图5.图5 表Enroll中的数据2、查询所有选修课程学生的基本信息、课程信息与相应的考试成绩.SQL语句为：select a.sno,a.sname,a.age,a.sex,b.o,b.ame,b.credit,c.gradefrom Student a,Course b,Enroll cwhere a.sno=c.sno and b.o=c.o执行结果如图6所示.图6 选修课程的学生、课程与成绩信息执行结果分析：只显示了选了课程的学生信息.3、查询所有学生的信息,若已选课就还要给出选修课程的信息与考试成绩.SQL语句为：select a.sno,a.sname,a.sex,a.age,d.o,d.ame,d.credit,d.gradefrom Student a left outer join <select b.o,b.ame,b.credit,c.grade,c.sno from Course b,Enroll c where b.o=c.o>don a.sno=d.sno执行结果如图7所示.图7 所有学生选课情况与成绩信息执行结果分析：不但列出选了课程的学生而且还列出来没选课程的学生信息.4、查询所有课程的信息,若课程有学生选修就还要给出选修课程的学生的信息与考试成绩.SQL语句为：select a.o,a.ame,a.credit,d.sno,d.sname,d.sex,d.age,d.gradefrom Course a left outer join <select b.sno,b.sname,b.age,b.sex,c.grade,c.o from Student b,Enroll c where b.sno=c.sno>don a.o=d.o执行结果如图8所示.图8 所有课程、选课的学生与成绩信息执行结果分析：不但列出被选课程的信息和学生选课情况而且还列出来没被选课程的信息.5、查询选修名为"数据库"的课程的考试成绩最高的学生的信息.1先查看选了数据库课程的学生信息.SQL语句为：select a.sno,a.sname,a.sex,a.age,b.ame,c.grade from Student a,Course b,Enroll c where a.sno=c.sno and b.o=c.o and b.ame='数据库'执行结果如图9所示.图9 选修"数据库"课程的学生信息执行结果分析：有三位学生选修了数据库课程.2查询选修 "数据库"课程的考试成绩最高的学生的信息.SQL语句为：select a.sno,a.sname,a.sex,a.age,d.ame,d.gradefrom Student a ,<select b.o,b.ame,c.grade,c.sno from Course b,Enroll c where b.o=c.o and b.ame='数据库' > dwhere a.sno=d.sno and d.grade=<select max<grade> from <select b.o,b.ame,c.grade from Course b,Enroll c where b.o=c.o>dwhere d.ame='数据库'>执行结果如图10所示.图10 选修"数据库"课程成绩最高学生执行结果分析：与图9结合比较,图10了列出了选修"数据库"课程成绩最高学生信息.6、对Student的age创建规则,满足18age25,并给出验证实例与验证结果.1创建规则并且绑定它.SQL语句为：create rule age_rule as age>=18 and age<=25sp_bindrule age_rule,'Student.age'2插入数据检验.insert into Student values <'2008006','李志锋',20,'男'>所影响的行数为 1 行insert into Student values <'2008007','李小龙',32,'男'>insert into Student values <'2008008','杨一民',17,'男'>服务器: 消息 513,级别 16,状态 1,行 1列的插入或更新与先前的 CREATE RULE 语句所强制的规则冲突.该语句已终止.冲突发生于数据库 'SC',表 'Student',列 'age'.语句已终止.执行结果分析：规则设置成功.7、创建触发器：当学生选修一门新的课程后,Statistics表的umber自动加1,且creditNumber自动增加新选课程的学分.SQL语句为：create trigger insert_statisticss on Enroll for insert as begin update a set umber=umber+d.counto,creditSum=creditSum+d.sumCredit from Statisticss a inner join<select b.sno,count<b.o> as counto,sum<c.credit> as sumCredit from inserted b inner join Course c on b.o=c.ogroup by b.sno>d on a.sno=d.sno insert Statisticss select b.sno,count<b.o> as counto,sum<c.credit> as sumCredit from inserted b inner join Course c on b.o=c.o where sno not in <select distinct sno from Statisticss> group by b.sno end没有在Enroll表插入任何数据时表为空,如图11所示.图11 表Statisticss的初始信息在Enroll表插入以下数据：insert into Enroll values <'2008001','005',70>insert into Enroll values <'2008006','001',59>插入数据后的结果如图12所示.图12 表Statisticss的信息执行结果分析：当在Enroll表中插入选课情况时,在表上建立的触发器insert_statisticss就被触发,进行umber自动加1和creditNumber自动增加新选课程的学分.8、创建视图：找出所有已修学分超过6、所修课程平均分不低于60的学生的基本信息、以与所修课程的平均分.按照平均分排序,若平均分相同按照学号排序.SQL语句为：create view Transcript asselect top 10 d.sno,d.sname,d.sex,d.age,d.avg_grade,d.creditSum from <select a.sno,a.sname,a.age,a.sex,avg<grade> as avg_grade,sum<credit> as creditSum from Student a,Course b,Enroll c where a.sno=c.sno and b.o=c.o group by a.sno,a.sname,a.sex,a.age>d where avg_grade>=60 and creditSum>6order by avg_grade,d.snoselect *from Transcript执行结果如图13所示.图13 Transcript视图表执行结果分析：列出的学生都按平均成绩大小从低到高排列且平均分都大于60.当平均分相同时,表中冉从宝和杨璐就按照学号大小从小低到高排列.9、对照对Course表的credit属性创建索引和去掉Course表的credit属性上创建的索引查询的执行计划.对Course表的credit属性创建索引为：create index credit_index on Course<credit>（1） 对比有无索引credit_index情形下,"select * from Course"的查询的执行计划分别如图14、图15所示.图14 有索引情形下的查询的执行计划图15 无索引情形下的查询的执行计划结果分析：由图14,、图15可知当查询是针对整个表进行,创建索引和不创建索引,查询的执行计划都是一样的.2对比有无索引credit_index情形下,"select * from course where ame=数据库"的查询的执行计划分别如图16、图17所示.图16 有索引情形下的查询的执行计划图17 无索引情形下的查询的执行计划结果分析：由图16,、图17可知当条件针对的字段不是创建了索引的那个字段时,索引是无效的.有索引和无索引,查询的执行计划都是一样的.3对比有无索引credit_index情形下,"select * from course where credit=3"的查询的执行计划分别如图18、图19所示.图18 有索引情形下的查询的执行计划图19 无索引情形下的查询的执行计划结果分析：由图18,、图19可知where条件为credit的属性,索引起效.有索引的查询效率比没有索引的查询效率高数十倍.4对比有无索引credit_index情形下,"select * from course where credit>2 and credit<5”的查询的执行计划分别如图20、图21所示.图20 有索引情形下的查询的执行计划图21 无索引情形下的查询的执行计划结果分析：由图20,、图21可知where条件为credit的属性,索引起效.有索引的查询效率比没有索引的查询效率高数十倍.5对比有无索引credit_index情形下,"update course set credit=3 where credit=2”的查询的执行计划分别如图22、图23所示.图22 有索引情形下的查询的执行计划图23 无索引情形下的查询的执行计划结果分析：由图20,、图21可知where条件为credit的属性,索引起效.有索引的查询效率比没有索引的查询效率高数十倍.索引小结：索引是对数据库表中的一列或多列的值进行排序的结构,有助于更快地获取信息.只有当经常查询索引列中的数据时,才需要创建索引.折衷：索引提高查询速度 vs. 索引占用磁盘空间,降低数据更新执行速度.若应用中大多数是数据更新操作插入、删除和修改.则应限制索引的数量.10、将表Student、Course、Enroll作内连接的结果发布为HTML网页格式.1在企业管理器的控制台中选中SC数据库,"工具"®"向导"®"管理"®"web助手向导"如图24所示.图24 启动Web助手向导2点击"下一步",选择数据库SC如图25所示.图25 选择数据库SC3在选择SC数据库后,单击"下一步",选择和设置如图26所示.图26 Web作业命名和设置4单击"下一步",在弹出的"编写Transact-SQL查询"对的话框中的文本框中输入表Student、Course、Enroll作内连接的查询语句,如图27所示.图27 表Student、Course、Enroll作内连接的查询5单击"下一步",设置调度Web助手作业,如图28所示.图28 设置调度Web助手作业 6单击"下一步",设置调度更新间隔,如图29所示.图29设置调度更新间隔7单击"下一步",设置发布Web页面的物理存储位置,如图30所示.图30 设置发布Web页面的物理存储位置8设置Web页的格式,如图31所示.图31 设置Web页的格式9查看发布的WebPage1.htm网页.查看当前Enroll表的数据,结果如图32所示测试Web页的更新方式有效性.在Enroll表中插入以下数据：insert into Enroll values <'2008006','004',67>insert into Enroll values <'2008006','005',84>执行该SQL语句后,刷新所发布的网页,结果如图33所示.结果显示：设置的Web页的更新方式的是有效的.
