视图和索引的定义和使用

钻仰弥坚

已于 2023-04-27 17:37:58 修改

阅读量4.1k

点赞数 14

分类专栏：数据库实验文章标签：数据库 sql

于 2022-07-10 21:43:13 首次发布

本文链接：https://blog.csdn.net/capture3333/article/details/125711135

版权

数据库实验专栏收录该内容

9 篇文章 23 订阅

订阅专栏

一、实验目的

(1)熟悉掌握对数据表中视图的定义操作和SQL命令的使用；

(2)熟悉掌握对数据表中视图的查询操作和SQL命令的使用；

(3)熟悉掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系；

(4)灵活熟练的进行视图的操作，认识视图的作用；

(5)掌握建立与删除索引；

二、实验内容

1.建立视图

（1）建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为学号，姓名，出生年月，性别，系名称。

CREATE  VIEW  C_Student

AS

SELECT  学号, 姓名, 出生年月, 性别,系名称

FROM  学生,系

WHERE  系名称='数学' AND 学生.系编号=系.系编号

WITH CHECK OPTION

（2）建立学生的学号、姓名、选修课程名及成绩的视图 Student_CR。

CREATE VIEW Student_CR

AS

SELECT  学生.学号, 姓名, 课程名称, 分数

FROM  学生,成绩,课程

WHERE  学生.学号=成绩.学号 AND 成绩.课程编号=课程.课程编号

（3）定义一个反映学生出生日期的视图Student_birth。

CREATE VIEW Student_birth(学号, 姓名, 出生年月)

AS

SELECT  学号, 姓名, 出生年月

FROM  学生

2、删除视图

（4）删除视图Student_birth。

DROP VIEW Student_birth;

3、查询视图

（5）在数学系的学生视图C_Student中找出女学生姓名和出生年月。

SELECT  姓名, 出生年月

FROM  C_Student

WHERE  性别='女'

（6）在Student_CR视图中查询成绩在85分以上的学生学号、姓名和课程名称。

SELECT  学号, 姓名, 课程名称

FROM Student_CR

WHERE  分数>85;

4、更新视图

（7）将数学系学生视图C_Student中学号为991022的学生姓名改为“黄海”。

UPDATE  C_Student

SET  姓名='黄海'

WHERE  学号='991022';

说明：DBMS自动转换为对基本表的更新语句如下：

SELECT  姓名, 出生年月

FROM  学生

WHERE  系编号 in ( select 系编号 from 系

Where 系名称='数学') AND 性别='女';

（8）向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“991088”，姓名为“王海”，出生年月为“07/05/1980”。（PS：无法成功，具体分析见调试结果）

INSERT

INTO  C_Student

VALUES ('S09', '王海', '1980-07-05','女');

PS:这个更新无法完成，另外它跨了两张表（系表和学生表），如果视图是由两个以上的基本表导出的，则该视图不允许更新。

使用WITH CHECK OPTION可以这么解释：通过视图进行的操作，必须也能通过该视图看到操作后的结果。

比如INSERT，那么加的这条记录在视图查询后必须可以看到。

比如UPDATE，修改完的结果也必须能通过该视图看到。

比如DELETE，只能删除视图里有显示的记录。

如果建立视图的时候不输入WITH CHECK OPTION，同样也不会成功，它跨了两张表（系表和学生表），如果视图是由两个以上的基本表导出的，则该视图不允许更新。

如下：

（9）删除数学系学生视图C_Student中学号为“991088”的记录。（PS：无法成功，具体分析见调试结果）

DELETE

FROM  C_Student

WHERE  学号='991088'

同理，这条更新同样也不会成功（它跨了两张表（系表和学生表），如果视图是由两个以上的基本表导出的，则该视图不允许更新。）

5 、建立索引

（10）为学生选课数据库中的学生、成绩、课程三个表建立索引。其中学生表按学号升序建唯一索引，课程表按课程号升序建唯一索引，成绩表按学号升序和课程编号降序建唯一索引。其语句为：

CREATE UNIQUE INDEX Stu_Sno ON 学生(学号);

CREATE UNIQUE INDEX Cou_Cno ON 课程(课程编号);

CREATE UNIQUE INDEX Rep_Scno ON 成绩(学号 ASC,分数 DESC);

6 、删除索引

（11）删除成绩表上的Rep_SCno索引。

DROP INDEX 成绩.Rep_Scno;

7、现场综合练习

（12）在成绩表上创建一个唯一、非聚簇复合索引（按学号升序、课程号降序）。

CREATE UNIQUE INDEX Rep_Scno ON 成绩(学号 ASC,分数 DESC);

（13）定义“101”系学生基本情况视图V_IS 并查询结果

先定义：

CREATE VIEW V_IS

AS

SELECT * FROM 学生

WHERE 系编号='101'

WITH CHECK OPTION;

后查询：

SELECT * FROM V_IS;

（14) 创建视图“教师信息”，用于保存姜明明所教班级的所有任课教师的姓名、学时数、课程号。再将C102的学时数改为80。

先创建视图：

CREATE VIEW 教师信息

AS

SELECT 教工.姓名,课程.学时,课程.课程编号

FROM 课程,教工

WHERE 教工.系编号='101'AND 课程.系编号='101'

WITH CHECK OPTION;

修改学时：

UPDATE  教师信息

SET  课程编号='C102'

WHERE  学时='80';

(15) 创建视图“选修门数”，用于保存选修课门数在1门以上的学生学号、门数。再显示选修课门数为2门的学生学号。

先创建视图：

CREATE VIEW 选修门数

AS

SELECT 学号,COUNT(课程编号) AS 门数

FROM 成绩

GROUP BY 学号

查询：

SELECT  学号

FROM  选修门数

WHERE  门数=2

(16)将学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;

先创建视图：

CREATE VIEW V_S_C_G

AS

SELECT 成绩.学号,学生.姓名,成绩.课程编号,课程名称,分数

FROM 成绩,课程,学生

WHERE 成绩.学号=学生.学号 AND 课程.课程编号=成绩.课程编号

GROUP BY 成绩.学号,学生.姓名,成绩.课程编号,课程名称,分数

最好后一行可有可无。

查询：

SELECT  *

FROM V_S_C_G

（17) 将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;

先创建视图：

CREATE VIEW V_NUM_AVG

AS

SELECT 系名称, COUNT(学号)AS 人数 ,AVG(2020-year(出生年月)) AS 平均年龄

FROM 系,学生

WHERE 学生.系编号=系.系编号

GROUP BY 系名称

查询：

SELECT  *

FROM V_NUM_AVG

(18) 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;

先创建视图：

CREATE VIEW V_AVG_S_G

AS

SELECT 学号,COUNT(课程编号) AS 门数,AVG(分数) AS 平均成绩

FROM 成绩

GROUP BY 学号

查询：

SELECT  *

FROM V_AVG_S_G

(19) 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G并查询结果;

先创建视图：

CREATE VIEW V_AVG_C_G

AS

SELECT 课程编号,COUNT(学号) AS 选修人数,AVG(分数) AS 平均成绩

FROM 成绩

GROUP BY 课程编号

查询：

SELECT  *

FROM V_AVG_C_G

(20) 查询平均成绩为90分以上的学生学号、姓名和成绩；

这个代码仅仅列出平均成绩为90分以上的学生学号、姓名和所有成绩：

SELECT 学号,姓名,分数

FROM V_S_C_G

WHERE 学号 IN

(SELECT 学号

FROM V_S_C_G

GROUP BY 学号

HAVING AVG(分数)>=90);

这个代码仅仅列出平均成绩为90分以上的学生学号、姓名和平均成绩：

SELECT 学号,姓名,AVG(分数)AS 平均成绩

FROM V_S_C_G

GROUP BY 学号,姓名

HAVING AVG(分数)>=90;

(21) 查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；（没有这样的学生）

SELECT *

FROM V_S_C_G

WHERE 学号 IN

(SELECT 学生.学号

FROM V_S_C_G

学生

WHERE NOT EXISTS

(SELECT 成绩.分数

FROM V_S_C_G 成绩

WHERE 分数 <= (SELECT AVG(课程.分数)

FROM V_S_C_G 课程

WHERE 成绩.课程编号=课程.课程编号) AND 学生.学号=成绩.学号 ) );

(22)按系统计各系平均成绩在80分以上的人数，结果按降序排列；

SELECT 系编号,COUNT(平均成绩) AS 平均成绩在80分以上的人数

FROM 学生,V_AVG_S_G

WHERE 学生.学号=V_AVG_S_G.学号 AND 平均成绩>=80

GROUP BY 系编号

ORDER BY 平均成绩在80分以上的人数 DESC;

(23)通过视图V_IS，分别将学号为“993012”和“994125”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果;（学号为993012，994125的学生都不是数学系）

UPDATE V_IS SET 姓名='S1_MMM' WHERE 学号='993012';

UPDATE V_IS SET 姓名='S4_MMM' WHERE 学号='994125';

SELECT * FROM V_IS;

SELECT * FROM 学生;

(24) 通过视图V_IS，新增加一个学生记录 (‘994165’,’王宏’,’10/05/1979’,’101’)，并查询结果；

INSERT

INTO V_IS(学号,姓名,出生年月,系编号)

VALUES ('994165','王宏','1979-10-05','101')

SELECT * FROM 学生

WHERE 学号='994165';

SELECT * FROM 学生

SELECT * FROM V_IS

(25) 通过视图V_IS，删除学号为“994165”的学生信息，并查询结果；

DELETE FROM V_IS WHERE 学号='994165'

SELECT * FROM V_IS;

SELECT * FROM 学生;

（26）要通过视图V_S_C_G，将学号为“993012”的姓名改为“S12_MMM”，是否可以实现？

UPDATE V_S_C_G

SET 姓名='S12_MMM'

WHERE 学号='993012';

看具体操作，当创建视图使用了GROUP语句时便无法修改实现，因为V_S_C_G视图的字段来自聚集函数，含有GROUP BY字句，此视图不允许更新，但是不使用GROUP语句这个是可以修改实现的，两种方式都有操作具体见下面的实验调试。

不成功：

成功：

结果修改成功。

（27）要通过视图V_AVG_S_G，将学号为“991022”的平均成绩改为90分，是否可以实现？

UPDATE V_AVG_S_G

SET 平均成绩='90'

WHERE 学号='991022';

不可以实现，用不用GROUP语句都一样。会出现错误“对视图或函数 'V_AVG_S_G' 的更新或插入失败，因其包含派生域或常量域”。因为所创建的视图对其属性值进行了计算的其他形式上的改变，而对视图的更改最终表现为对表的更改而表中不存在视图的某一属性，或属性的性质不相同，则无法更改，这是一种视图机制。