索引、视图 

一、实验目的

1、加深对索引和视图概念的理解

2、熟练掌握索引的创建和删除

3、熟练掌握视图的创建、修改和删除。

4、熟练运用视图进行各种检索操作。

二、实验内容与步骤

以StudentCourse数据库为基础，用SQL语句实现下面操作：

1、建立计算机科学与技术系的学生的视图View_Stu。

（1）查看视图内容

（2）在视图中添加一行该系学生信息，然后查看结果

（3）在视图中修改某学生姓名，然后查看结果

（4）删除View_Stu中学号为070102的记录，然后查看结果。

（5）将视图View_Stu改成电子信息工程系的学生学号以及平均成绩的视图

2、将所有女生的学号以及平均成绩定义为一个视图SF_G。

（1）利用该视图查询平均成绩在70分以上的学生学号和平均成绩

（2）在视图中添加一行记录，然后查看结果

3、利用学生表、课程表、选课表建立视图 TOTAL(sno,sname,cno,cname,grade)。

4、利用视图TOTAL统计各门课程的选课人数，结果显示课程号、课程名及选课人数。

5、验证能否利用视图TOTAL修改选修了“高等数学1”课程的学生的成绩。

6、对每个同学找出他获得最高成绩的课程号、课程名以及相应的课程分数，并将查询结果作为VMgrade视图保存。

7、在学生表student的学号sno上建立聚簇索引。

8、在学生表student中，为姓名sname建立非聚簇索引。

9、在课程表的课程号Cno上建立唯一索引。

10、在选课表的学号sno、成绩Grade上建立复合索引，要求学号为升序，学号相同时成绩为降序。

三、实验心得

实验内容

以StudentCourse数据库为基础，用SQL语句实现下面操作：

1、建立计算机科学与技术系的学生的视图View_Stu。

（1）查看视图内容

语句：

CREATE VIEW View_Stu
AS
SELECT *
FROM Student
WHERE 专业名 = '计算机科学与技术';复制

结果：

 

（2）在视图中添加一行该系学生信息，然后查看结果

语句：

SELECT * 
FROM View_Stu

INSERT INTO View_Stu(学号,姓名,专业名,性别,出身时间,总学分,备注)
    VALUES('070100','test1','计算机科学与技术','男','1990-1-1','80','三好生')

SELECT *
         FROM  View_Stu复制

结果：

 

（3）在视图中修改某学生姓名，然后查看结果

语句：

UPDATE View_Stu
    SET 姓名 = 'test2'
         WHERE 学号 = 070100
SELECT *
         FROM  View_Stu复制

结果：

 

（4）删除View_Stu中学号为070102的记录，然后查看结果。

结果：

 

（5）将视图View_Stu改成电子信息工程系的学生学号以及平均成绩的视图

语句：

ALTER VIEW View_Stu
AS
SELECT StuCourse.学号,AVG(成绩) AS 平均成绩
FROM Student,StuCourse
WHERE 专业名 = '电子信息工程' AND StuCourse.学号 = Student.学号
GROUP BY StuCourse.学号复制

结果：

 

2、将所有女生的学号以及平均成绩定义为一个视图SF_G。

（1）利用该视图查询平均成绩在70分以上的学生学号和平均成绩

语句：

CREATE VIEW SF_G
AS
SELECT StuCourse.学号,AVG(成绩) AS 平均成绩
FROM StuCourse,Student
WHERE 性别 = '女' AND StuCourse.学号 = Student.学号
GROUP BY StuCourse.学号复制

结果：

 

（2）在视图中添加一行记录，然后查看结果

视图不能直接添加数据

结果：

 

3、利用学生表、课程表、选课表建立视图 TOTAL(sno,sname,cno,cname,grade)。

语句：

CREATE VIEW TOTAL(sno,sname,cno,cname,grade)
AS
SELECT Student.学号,Student.姓名,Course.课程号,Course.课程名,StuCourse.成绩
FROM StuCourse,Student,Course
WHERE StuCourse.学号 = Student.学号 AND Course.课程号 = StuCourse.课程号

SELECT *
FROM TOTAL复制

结果：

 

4、利用视图TOTAL统计各门课程的选课人数，结果显示课程号、课程名及选课人数。

语句：

SELECT 课程号,课程名,COUNT(课程号) AS '课程人数'
FROM TOTAL
GROUP BY 课程号,课程名
复制

结果：

 

5、验证能否利用视图TOTAL修改选修了“高等数学1”课程的学生的成绩。

视图不能直接修改数据

结果：

 

6、对每个同学找出他获得最高成绩的课程号、课程名以及相应的课程分数，并将查询结果作为VMgrade视图保存。

语句：

CREATE VIEW VMgrade
AS
SELECT Student.姓名, StuCourse.课程号, Course.课程名, a.成绩
FROM
	(
	SELECT StuCourse.学号, MAX(StuCourse.成绩) AS '成绩'
	FROM StuCourse
	GROUP BY StuCourse.学号)a, Student, StuCourse, Course
WHERE a.学号 = StuCourse.学号 AND a.成绩 = StuCourse.成绩 AND StuCourse.课程号 = Course.课程号 AND Student.学号 = a.学号复制

结果：

 

7、在学生表student的学号sno上建立聚簇索引。

语句：

CREATE UNIQUE CLUSTERED INDEX sno_index
ON Student(学号)复制

8、在学生表student中，为姓名sname建立非聚簇索引。

语句：

CREATE INDEX sname_index
ON Student(姓名)复制

9、在课程表的课程号Cno上建立唯一索引。

语句：

CREATE UNIQUE INDEX Cno_index
ON Course(课程号)复制

10、在选课表的学号sno、成绩Grade上建立复合索引，要求学号为升序，学号相同时成绩为降序。

语句：

CREATE INDEX index
ON StuCourse(学号 ASC,成绩 DESC)复制