数据库实验4_SQL的视图

实验4、SQL的视图

实验目的

熟悉SQL支持的有关视图的操作，能够熟练使用SQL语句来创建需要的视图，对视图进行查询和删除视图。定义数据库外模式，并能使用所创建的视图实现数据管理。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。理解物化视图。

实验内容

1. 定义常见的视图形式，包括：
2. 行列子集视图
3. 基于多个基表的视图
4. 基于视图的视图
5. 带表达式的视图
6. 分组视图
7. 全量物化视图
8. WITH CHECK OPTION的视图(在SQLServer平台上选做)
   1. 使用DROP语句删除一个视图，由该视图导出的其他视图定义必须显式删除。同样的原因，删除基表时，由该基表导出的所有视图定义都必须显式删除。
   2. 通过实验考察WITH CHECK OPTION这一语句在视图定义后产生的影响，包括对修改操作、删除操作、插入操作的影响。讨论视图的数据更新情况，对行列子集视图进行数据更新。(在SQLServer平台上选做)。

实验步骤
 

1. 创建一个行列子集视图CS_View，给出选课成绩合格的学生的编号、教师编号、所选课程号和该课程成绩，并查询该视图。

1. 创建基于多个基本表的视图SCT_View，该视图由学生姓名、该生所选课程名及讲授该课程的教师姓名构成，并查询该视图。从视图SCT_View中查询所有选修课程“数据库原理及应用”的学生姓名。
2. 创建带表达式的视图EXP_View，由学生姓名及所选课程名和所有课程成绩都比原来多5分这几个属性组成，并查询该视图。
3. 创建分组视图Group_View，将学生的学号及他的平均成绩定义为一个视图，并查询该视图。
4. 创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩的视图VV_View，并查询该视图。
5. 创建全量物化视图MV_R_View，给出Reports中选课成绩合格的学生选修课程的元组数，并查询视图MV_R_View。向基表Reports插入数据('S52','T05','C07',93)，并查询视图MV_R_View。对全量物化视图做增量刷新，将会出现什么现象？为什么？向基表Reports插入数据('S52','T05','C03',83)，对全量物化视图MV_R_View做全量刷新，并查询视图MV_R_View。思考全量物化视图、增量物化视图与普通视图的区别。
6. 在SQL终端中用SQL命令查看视图MV_R_View的定义；

SELECT  pg_get_viewdef('MV_R_View');

在SQL终端中用可视化方法查看视图MV_R_View的定义；

在客户端或服务器端查看视图MV_R_View的定义。

[omm@db1 ~]$ gsql -d jiangrg_db_uni -p 26000 -U zjutuser -W yuabcd@123

sql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:54 commit 0 last mr  )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type "help" for help.

jiangrg_db_uni=> \d+ MV_R_View

               Materialized view "zjutuser.mv_r_view"

 Column |  Type  | Modifiers | Storage | Stats target | Description

--------+--------+-----------+---------+--------------+-------------

 count  | bigint |           | plain   |              |

Rules:

    "_RETURN" AS

    ON SELECT TO mv_r_view DO INSTEAD  SELECT count(*) AS count

   FROM reports

  WHERE reports.score >= 60::numeric

Replica Identity: NOTHING

Has OIDs: no

Options: orientation=row, compression=no

jiangrg_db_uni=> \d+ CS_View

                      View "zjutuser.cs_view"

 Column |         Type         | Modifiers | Storage  | Description

--------+----------------------+-----------+----------+-------------

 sno    | character varying(6) |           | extended |

 tno    | character varying(6) |           | extended |

 cno    | character varying(6) |           | extended |

 score  | numeric(5,1)         |           | main     |

View definition:

 SELECT  *

   FROM reports

  WHERE reports.score >= 60::numeric;

1. 删除视图SCT_View、视图EXP_View和视图Group_View，删除物化视图MV_R_View。
2. 操作SQL命令DROP VIEW CS_View;将出现什么现象？为什么？请给出删除视图CS_View的方法。
3. （选做）在SQL Server中建造好与openGauss一样的数据库及相关的基本表，创建一个带WITH CHECK OPTION子句的视图CS_View_opt，给出选课成绩合格的学生的编号、教师编号、所选课程号和该课程成绩，并查询该视图。openGauss与SQLServer分别是如何处理的？(注：在实现SQL语句时，对于标准SQL之外的规定，每个DBMS处理方式不一样。)通过CS_View_opt视图，插入元组（'S52','T05','C07',51）到视图CS_View_opt中。分析对插入操作有什么影响。通过视图CS_View_opt，把所有课程编号为C01的课程的成绩都减去5分。这个操作数据库是否会正确执行，为什么？如果加上5分（原来95分以上的不变）呢？在视图CS_View_opt删除编号S03学生的记录，会产生什么结果？【注：该步骤在SQL Server中操作】

实验要求

(1) 实验之前请细细阅读实验总体要求与说明指导书

(2)完成以上实验(1)-(9)步所有SQL操作，选做在SQL Server中操作完成(10)步。

实验结果

1. 创建一个行列子集视图CS_View，给出选课成绩合格的学生的编号、教师编号、所选课程号和该课程成绩，并查询该视图。

查询视图：

1. 创建基于多个基本表的视图SCT_View，该视图由学生姓名、该生所选课程名及讲授该课程的教师姓名构成，并查询该视图。从视图SCT_View中查询所有选修课程“数据库原理及应用”的学生姓名。

创建：

查询：

1. 创建带表达式的视图EXP_View，由学生姓名及所选课程名和所有课程成绩都比原来多5分这几个属性组成，并查询该视图。

查询：

1. 创建分组视图Group_View，将学生的学号及他的平均成绩定义为一个视图，并查询该视图。

1. 创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩的视图VV_View，并查询该视图

1. 创建全量物化视图MV_R_View，给出Reports中选课成绩合格的学生选修课程的元组数，并查询视图MV_R_View。向基表Reports插入数据('S52','T05','C07',93)，并查询视图MV_R_View。对全量物化视图做增量刷新，将会出现什么现象？为什么？向基表Reports插入数据('S52','T05','C03',83)，对全量物化视图MV_R_View做全量刷新，并查询视图MV_R_View。思考全量物化视图、增量物化视图与普通视图的区别。

①全量物化视图、增量物化视图与普通视图的区别。

全量物化视图与普通视图的区别：全量物化视图是将实际的表中的数据通过存储过程加入到视图中，而普通视图是将视图表中存储的虚拟数据返回给用户，两者之间存在一定程度的冗余。增量物化视图是在全量物化视图的基础上，当原表中的记录有更新时，对对应的视图进行增量刷新，用于节约时间和资源。

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据， 可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了重新组织，可以理解物化视图是完全的一张新表。

1. 在SQL终端中用SQL命令查看视图MV_R_View的定义；

SELECT  pg_get_viewdef('MV_R_View');

在SQL终端中用可视化方法查看视图MV_R_View的定义；

在客户端或服务器端查看视图MV_R_View的定义。

[omm@db1 ~]$ gsql -d jiangrg_db_uni -p 26000 -U zjutuser -W yuabcd@123

sql ((openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:54 commit 0 last mr  )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type "help" for help.

jiangrg_db_uni=> \d+ MV_R_View

               Materialized view "zjutuser.mv_r_view"

 Column |  Type  | Modifiers | Storage | Stats target | Description

--------+--------+-----------+---------+--------------+-------------

 count  | bigint |           | plain   |              |

Rules:

    "_RETURN" AS

    ON SELECT TO mv_r_view DO INSTEAD  SELECT count(*) AS count

   FROM reports

  WHERE reports.score >= 60::numeric

Replica Identity: NOTHING

Has OIDs: no

Options: orientation=row, compression=no

jiangrg_db_uni=> \d+ CS_View

                      View "zjutuser.cs_view"

 Column |         Type         | Modifiers | Storage  | Description

--------+----------------------+-----------+----------+-------------

 sno    | character varying(6) |           | extended |

 tno    | character varying(6) |           | extended |

 cno    | character varying(6) |           | extended |

 score  | numeric(5,1)         |           | main     |

View definition:

 SELECT  *

   FROM reports

  WHERE reports.score >= 60::numeric;

1. 删除视图SCT_View、视图EXP_View和视图Group_View，删除物化视图MV_R_View。

1. 操作SQL命令DROP VIEW CS_View;将出现什么现象？为什么？请给出删除视图CS_View的方法。

1. （选做）在SQL Server中建造好与openGauss一样的数据库及相关的基本表，创建一个带WITH CHECK OPTION子句的视图CS_View_opt，给出选课成绩合格的学生的编号、教师编号、所选课程号和该课程成绩，并查询该视图。openGauss与SQLServer分别是如何处理的？(注：在实现SQL语句时，对于标准SQL之外的规定，每个DBMS处理方式不一样。)通过CS_View_opt视图，插入元组（'S52','T05','C07',51）到视图CS_View_opt中。分析对插入操作有什么影响。通过视图CS_View_opt，把所有课程编号为C01的课程的成绩都减去5分。这个操作数据库是否会正确执行，为什么？如果加上5分（原来95分以上的不变）呢？在视图CS_View_opt删除编号S03学生的记录，会产生什么结果？【注：该步骤在SQL Server中操作】