sql数据库实例数据库入门

1、数据库设计及应用实验一、 实验内容创建数据库：包括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、 设计并在MS SQL Server 2000中创建以上表结构，并设置完整性约束。2、 查询所有选修课程的学生的基本信息、课程信

2、息及相应的考试成绩。3、 查询所有学生的信息，若已选课就还要给出选修课程的信息及考试成绩。4、 查询所有课程的信息，若课程有学生选修就还要给出选修课程的学生的信息及考试成绩。5、 查询选修名为“数据库”的课程的考试成绩最高的学生的信息。6、 对Student的age创建规则，满足18age25，并给出验证实例及验证结果。7、 创建触发器：当学生选修一门新的课程后，Statistics表的cNumber自动加1，且creditNumber自动增加新选课程的学分。8、 创建视图：找出所有已修学分超过6、所修课程平均分不低于60的学生的基本信息、以及所修课程的平均分。按照平均分排序，若平均分相同按照

3、学号排序。9、 对于如下一组数据操作：(1) select * from course;(2) select * from course where cname=数据库(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属性上创建的索引，并给出上述查

4、询的执行计划。对上述有无索引个查询执行的情况进行对比分析，总结出什么时候索引有效？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,ag

5、e int,sex varchar(2) not null)创建课程表：create table Course( cno varchar(10) not null primary key,cname varchar(50) not null,credit int not null)创建注册表：create table Enroll( sno varchar(10) not null references Student(sno),cno varchar(10) not null references Course(cno),grade int,primary key (sno,cno)创建选课

6、情况表：create table Statisticss( sno varchar(10) not null primary keyreferences Student(sno),cNumber int not null,creditSum int not null,)创建后的表如图2。图2 创建表的结果（3）插入数据Student表：insert into Student values ('2008001','李贵斌',22,'男')insert into Student values ('2008002','冉从宝&#

7、39;,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','数据库

8、',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','0

9、01',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&#

10、39;,'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、查询所有选修课程学生的基本信息、课程信息

11、及相应的考试成绩。SQL语句为：select a.sno,a.sname,a.age,a.sex,o,ame,b.credit,c.gradefrom Student a,Course b,Enroll cwhere a.sno=c.sno and o=o执行结果如图6所示。图6 选修课程的学生、课程及成绩信息执行结果分析：只显示了选了课程的学生信息。3、查询所有学生的信息，若已选课就还要给出选修课程的信息及考试成绩。SQL语句为：select a.sno,a.sname,a.sex,a.age,o,ame,d.credit,d.gradefrom Student a left outer j

12、oin (select o,ame,b.credit,c.grade,c.sno from Course b,Enroll c where o=o)don a.sno=d.sno执行结果如图7所示。图7 所有学生选课情况及成绩信息执行结果分析：不但列出选了课程的学生而且还列出来没选课程的学生信息。4、查询所有课程的信息，若课程有学生选修就还要给出选修课程的学生的信息及考试成绩。SQL语句为：select o,ame,a.credit,d.sno,d.sname,d.sex,d.age,d.gradefrom Course a left outer join (select b.sno,b.sn

13、ame,b.age,b.sex,c.grade,o from Student b,Enroll c where b.sno=c.sno)don o=o执行结果如图8所示。图8 所有课程、选课的学生及成绩信息执行结果分析：不但列出被选课程的信息和学生选课情况而且还列出来没被选课程的信息。5、查询选修名为“数据库”的课程的考试成绩最高的学生的信息。（1）先查看选了数据库课程的学生信息。SQL语句为：select a.sno,a.sname,a.sex,a.age,ame,c.grade from Student a,Course b,Enroll c where a.sno=c.sno and o

14、=o and ame='数据库'执行结果如图9所示。图9 选修“数据库”课程的学生信息执行结果分析：有三位学生选修了数据库课程。（2）查询选修 “数据库”课程的考试成绩最高的学生的信息。SQL语句为：select a.sno,a.sname,a.sex,a.age,ame,d.gradefrom Student a ,(select o,ame,c.grade,c.sno from Course b,Enroll c where o=o and ame='数据库' ) dwhere a.sno=d.sno and d.grade=(select max(grad

15、e) from (select o,ame,c.grade from Course b,Enroll c where o=o)dwhere ame='数据库')执行结果如图10所示。图10 选修“数据库”课程成绩最高学生执行结果分析：与图9结合比较，图10了列出了选修“数据库”课程成绩最高学生信息。6、对Student的age创建规则，满足18age25，并给出验证实例及验证结果。（1）创建规则并且绑定它。SQL语句为：create rule age_rule as age>=18 and age<=25sp_bindrule age_rule,'Stude

16、nt.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列的插入或更新与先前的 CRE

17、ATE RULE 语句所强制的规则冲突。该语句已终止。冲突发生于数据库 'SC'，表 'Student'，列 'age'。语句已终止。执行结果分析：规则设置成功。7、创建触发器：当学生选修一门新的课程后，Statistics表的cNumber自动加1，且creditNumber自动增加新选课程的学分。SQL语句为：create trigger insert_statisticss on Enroll for insert as begin update a set cNumber=cNumber+d.countCno,creditSum=cred

18、itSum+d.sumCredit from Statisticss a inner join(select b.sno,count(o) as countCno,sum(c.credit) as sumCredit from inserted b inner join Course c on o=ogroup by b.sno)d on a.sno=d.sno insert Statisticss select b.sno,count(o) as countCno,sum(c.credit) as sumCredit from inserted b inner join Course c o

19、n o=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的

20、信息执行结果分析：当在Enroll表中插入选课情况时，在表上建立的触发器insert_statisticss就被触发，进行cNumber自动加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.

21、sex,avg(grade) as avg_grade,sum(credit) as creditSum from Student a,Course b,Enroll c where a.sno=c.sno and o=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

22、.当平均分相同时，表中冉从宝和杨璐就按照学号大小从小低到高排列。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可知当查询是针对整个表进行，创建索引和不创建索引

23、，查询的执行计划都是一样的。（2）对比有无索引credit_index情形下， “select * from course where cname=数据库”的查询的执行计划分别如图16、图17所示。图16 有索引情形下的查询的执行计划图17 无索引情形下的查询的执行计划结果分析：由图16,、图17可知当条件针对的字段不是创建了索引的那个字段时，索引是无效的。有索引和无索引，查询的执行计划都是一样的。（3）对比有无索引credit_index情形下， “select * from course where credit=3”的查询的执行计划分别如图18、图19所示。图18 有索引情形下的查询的执

24、行计划图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. 索引占用磁盘空间，降低数据更新执行