sql数据库实例数据库入门说课材料.docx

sql数据库实例数据库入门说课材料.docx

《sql数据库实例数据库入门说课材料.docx》由会员分享，可在线阅读，更多相关《sql数据库实例数据库入门说课材料.docx（22页珍藏版）》请在冰点文库上搜索。

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*fromcoursewherecredit>2andcredit<5

（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）notnull

referencesStudent（sno）,

cnovarchar（10）notnull

referencesCourse（cno）,

gradeint,

primarykey（sno,cno）

）

创建选课情况表：

createtableStatisticss（

snovarchar（10）notnullprimarykey

referencesStudent（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）

（4）查看表的内容

select*fromStudent结果如图3。

select*fromCourse结果如图4。

select*fromEnroll结果如图5。

2、查询所有选修课程学生的基本信息、课程信息及相应的考试成绩。

SQL语句为：

selecta.sno,a.sname,a.age,a.sex,o,ame,b.credit,c.grade

fromStudenta,Courseb,Enrollc

wherea.sno=c.snoando=o

执行结果如图6所示。

执行结果分析：

只显示了选了课程的学生信息。

3、查询所有学生的信息，若已选课就还要给出选修课程的信息及考试成绩。

SQL语句为：

selecta.sno,a.sname,a.sex,a.age,o,ame,d.credit,d.grade

fromStudentaleftouterjoin

（selecto,ame,b.credit,c.grade,c.sno

fromCourseb,Enrollcwhereo=o）d

ona.sno=d.sno

执行结果如图7所示。

执行结果分析：

不但列出选了课程的学生而且还列出来没选课程的学生信息。

4、查询所有课程的信息，若课程有学生选修就还要给出选修课程的学生的信息及考试成绩。

SQL语句为：

selecto,ame,a.credit,d.sno,d.sname,d.sex,d.age,d.grade

fromCoursealeftouterjoin

（selectb.sno,b.sname,b.age,b.sex,c.grade,o

fromStudentb,Enrollcwhereb.sno=c.sno）d

ono=o

执行结果如图8所示。

执行结果分析：

不但列出被选课程的信息和学生选课情况而且还列出来没被选课程的信息。

5、查询选修名为“数据库”的课程的考试成绩最高的学生的信息。

（1）先查看选了数据库课程的学生信息。

SQL语句为：

selecta.sno,a.sname,a.sex,a.age,ame,c.grade

fromStudenta,Courseb,Enrollc

wherea.sno=c.snoando=oandame='数据库'

执行结果如图9所示。

执行结果分析：

有三位学生选修了数据库课程。

（2）查询选修“数据库”课程的考试成绩最高的学生的信息。

SQL语句为：

selecta.sno,a.sname,a.sex,a.age,ame,d.grade

fromStudenta,

（selecto,ame,c.grade,c.snofromCourseb,Enrollc

whereo=oandame='数据库'）d

wherea.sno=d.snoandd.grade=（selectmax（grade）

from（selecto,ame,c.grade

fromCourseb,Enrollcwhereo=o）d

whereame='数据库'

）

执行结果如图10所示。

执行结果分析：

与图9结合比较，图10了列出了选修“数据库”课程成绩最高学生信息。

6、对Student的age创建规则，满足18≤age≤25，并给出验证实例及验证结果。

（1）创建规则并且绑定它。

SQL语句为：

createruleage_ruleas

@age>=18and@age<=25

sp_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_statisticssonEnroll

forinsertas

begin

updateasetcNumber=cNumber+d.countCno,creditSum=creditSum+d.sumCredit

fromStatisticssainnerjoin

（selectb.sno,count（o）ascountCno,sum（c.credit）

assumCreditfrominsertedb

innerjoinCoursecono=o

groupbyb.sno）d

ona.sno=d.sno

insertStatisticssselectb.sno,count（o）ascountCno,sum（c.credit）assumCredit

frominsertedbinnerjoinCoursecono=o

wheresnonotin（selectdistinctsnofromStatisticss）

groupbyb.sno

end

没有在Enroll表插入任何数据时表为空，如图11所示。

在Enroll表插入以下数据：

insertintoEnrollvalues（'2008001','005',70）

insertintoEnrollvalues（'2008006','001',59）

插入数据后的结果如图12所示。

图12表Statisticss的信息

执行结果分析：

当在Enroll表中插入选课情况时，在表上建立的触发器insert_statisticss就被触发，进行cNumber自动加1和creditNumber自动增加新选课程的学分。

8、创建视图：

找出所有已修学分超过6、所修课程平均分不低于60的学生的基本信息、以及所修课程的平均分。

按照平均分排序，若平均分相同按照学号排序。

SQL语句为：

createviewTranscriptas

selecttop10d.sno,d.sname,d.sex,d.age,d.avg_grade,d.creditSum

from（selecta.sno,a.sname,a.age,a.sex,avg（grade）

asavg_grade,sum（credit）ascreditSum

fromStudenta,Courseb,Enrollc

wherea.sno=c.snoando=o

groupbya.sno,a.sname,a.sex,a.age

）d

whereavg_grade>=60andcreditSum>6

orderbyavg_grade,d.sno

select*fromTranscript

执行结果如图13所示。

图13Transcript视图表

执行结果分析：

列出的学生都按平均成绩大小从低到高排列且平均分都大于60.当平均分相同时，表中冉从宝和杨璐就按照学号大小从小低到高排列。

9、对照对Course表的credit属性创建索引和去掉Course表的credit属性上创建的索引查询的执行计划。

对Course表的credit属性创建索引为：

createindexcredit_indexonCourse（credit）

（1）对比有无索引credit_index情形下，“select*fromCourse”的查询的执行计划分别如图14、图15所示。

结果分析：

由图14,、图15可知当查询是针对整个表进行，创建索引和不创建索引，查询的执行计划都是一样的。

（2）对比有无索引credit_index情形下，“select*fromcoursewherecname=’数据库’”的查询的执行计划分别如图16、图17所示。

图16有索引情形下的查询

的执行计划

图17无索引情形下的查询

的执行计划

结果分析：

由图16,、图17可知当条件针对的字段不是创建了索引的那个字段时，索引是无效的。

有索引和无索引，查询的执行计划都是一样的。

（3）对比有无索引credit_index情形下，“select*fromcoursewherecredit=3

”的查询的执行计划分别如图18、图19所示。

图18有索引情形下的查询

的执行计划

图19无索引情形下的查询

的执行计划

结果分析：

由图18,、图19可知where条件为credit的属性，索引起效。

有索引的查询效率比没有索引的查询效率高数十倍。

（4）对比有无索引credit_index情形下，“select*fromcoursewherecredit>2andcredit<5”的查询的执行计划分别如图20、图21所示。

结果分析：

由图20,、图21可知where条件为credit的属性，索引起效。

有索引的查询效率比没有索引的查询效率高数十倍。

（5）对比有无索引credit_index情形下，“updatecoursesetcredit=3wherecredit=2”的查询的执行计划分别如图22、图23所示。

结果分析：

由图20,、图21可知where条件为credit的属性，索引起效。

有索引的查询效率比没有索引的查询效率高数十倍。

索引小结：

索引是对数据库表中的一列或多列的值进行排序的结构，有助于更快地获取信息。

只有当经常查询索引列中的数据时，才需要创建索引。

折衷：

索引提高查询速度vs.索引占用磁盘空间，降低数据更新执行速度。

若应用中大多数是数据更新操作（插入、删除和修改）。

则应限制索引的数量。

10、将表Student、Course、Enroll作内连接的结果发布为HTML网页格式。

（1）在企业管理器的控制台中选中SC数据库，“工具”→“向导”→“管理”→“web助手向导”如图24所示。

（2）点击“下一步”，选择数据库SC如图25所示。

（3）在选择SC数据库后，单击“下一步”，选择和设置如图26所示。

（4）单击“下一步”，在弹出的“编写Transact-SQL查询”对的话框中的文本框中输入表Student、Course、Enroll作内连接的查询语句，如图27所示。

（5）单击“下一步”，设置调度Web助手作业，如图28所示。

图28设置调度Web助手作业

（6）单击“下一步”，设置调度更新间隔，如图29所示。

（7）单击“下一步”，设置发布Web页面的物理存储位置，如图30所示。

（8）设置Web页的格式，如图31所示。

（9）查看发布的WebPage1.htm网页。

查看当前Enroll表的数据，结果如图32所示

图32Enroll中的数据发布结果

测试Web页的更新方式有效性。

在Enroll表中插入以下数据：

insertintoEnrollvalues（'2008006','004',67）

insertintoEnrollvalues（'2008006','005',84）

执行该SQL语句后，刷新所发布的网页，结果如图33所示。

图32更新后Enroll中的数据发布结果

结果显示：

设置的Web页的更新方式的是有效的。