OpenGauss数据库-G.视图的使用

第1关：创建视图

任务描述

本关任务：创建一个视图。

相关知识

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询

创建视图

执行如下命令创建新视图MyView。

1. CREATE OR REPLACE VIEW MyView AS SELECT * FROM tpcds.web_returns WHERE trunc(wr_refunded_cash) > 10000;
2. CREATE VIEW

说明： CREATE VIEW中的OR REPLACE可有可无，当存在OR REPLACE时，表示若以前存在该视图就进行替换。

查询视图

执行如下命令查询MyView视图。

1. SELECT * FROM MyView;

查看某视图的具体信息

执行如下命令查询dba_users视图的详细信息。

postgres=# \d+ dba_users View "PG_CATALOG.DBA_USERS" Column | Type | Modifiers | Storage | Description ----------+-----------------------+-----------+----------+------------- USERNAME | CHARACTER VARYING(64) | | extended | View definition: SELECT PG_AUTHID.ROLNAME::CHARACTER VARYING(64) AS USERNAME FROM PG_AUTHID;

编程要求

数据库中已经存在三个基础表： student ( num integer,
name char(20), age integer, level integer, dept char(20) );

course ( id integer,
name char(20)

);

sel_course ( studentid integer,
courseid integer, score integer

); 根据提示，在右侧编辑器补充代码，完成视图的创建。 创建视图stu_sco_view。该视图包含学生姓名，所选课程和成绩,并按照dept、学生姓名(name)升序排序。

--创建视图stu_sco_view。该视图包含学生姓名，所选课程和成绩,并按照dept、学生姓名(name)升序排序。
--Begin
CREATE VIEW stu_sco_view
AS (
    SELECT 
        student.name AS "name",
        course.name AS "course",
        sel_course.score AS "score"
    FROM 
        student
    JOIN sel_course ON student.num = sel_course.studentid
    JOIN course ON sel_course.courseid = course.id
    ORDER BY
        student.dept,
        student.name
);
--End

第2关：管理视图

任务描述

本关任务：修改视图。

相关知识

修改视图

功能描述

ALTER VIEW更改视图的各种辅助属性。（如果用户是更改视图的查询定义，要使用CREATE OR REPLACE VIEW。）

注意事项

只有视图的所有者或者被授予了视图ALTER权限的用户才可以执行ALTER VIEW命令，系统管理员默认拥有该权限。针对所要修改属性的不同，对其还有以下权限约束：

修改视图的模式，当前用户必须是视图的所有者或者系统管理员，且要有新模式的CREATE权限。 修改视图的所有者，当前用户必须是视图的所有者或者系统管理员，且该用户必须是新所有者角色的成员，并且此角色必须有视图所在模式的CREATE权限。

语法格式

1. 设置视图列的默认值。
2. ALTER VIEW [ IF EXISTS ] view_name
3. ALTER [ COLUMN ] column_name SET DEFAULT expression;
5. 取消列视图列的默认值。
6. ALTER VIEW [ IF EXISTS ] view_name
7. ALTER [ COLUMN ] column_name DROP DEFAULT;
9. 修改视图的所有者。
10. ALTER VIEW [ IF EXISTS ] view_name
11. OWNER TO new_owner;
13. 重命名视图。
14. ALTER VIEW [ IF EXISTS ] view_name
15. RENAME TO new_name;
17. 设置视图的所属模式。
18. ALTER VIEW [ IF EXISTS ] view_name
19. SET SCHEMA new_schema;
21. 设置视图的选项。
22. ALTER VIEW [ IF EXISTS ] view_name
23. SET ( { view_option_name [ = view_option_value ] } [, ... ] );
25. 重置视图的选项。
26. ALTER VIEW [ IF EXISTS ] view_name
27. RESET ( view_option_name [, ... ] );

参数说明

IF EXISTS 使用这个选项，如果视图不存在时不会产生错误，仅有会有一个提示信息。

view_name 视图名称，可以用模式修饰。 取值范围：字符串，符合标识符命名规范。

column_name 可选的名称列表，视图的字段名。如果没有给出，字段名取自查询中的字段名。 取值范围：字符串，符合标识符命名规范。

SET/DROP DEFAULT 设置或删除一个列的缺省值，该参数暂无实际意义。

new_owner 视图新所有者的用户名称。

new_name 视图的新名称。

new_schema 视图的新模式。

view_option_name [ = view_option_value ] 该子句为视图指定一个可选的参数。 目前view_option_name支持的参数仅有security_barrier，当VIEW试图提供行级安全时，应使用该参数。 取值范围：Boolean类型，TRUE、FALSE。

示例

``` --创建一个由c_customer_sk小于150的内容组成的视图。 postgres=# CREATE VIEW tpcds.customer_details_view_v1 AS SELECT * FROM tpcds.customer WHERE c_customer_sk < 150;

--修改视图名称。 postgres=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

--修改视图所属schema。 postgres=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

编程要求

1、根据提示，在右侧编辑器补充代码，修改视图stu_sco_view为只显示部门dept为“cs”的学生选课成绩。

2、创建总分超过250分的同学的视图stu_good_view，显示其姓名、学院、总成绩。

 --修改视图stu_sco_view。使该视图只包含dept=‘cs’的学生的姓名，所选课程和成绩,,并按照name升序排序。(使用create or replace view... 命令)
 --begin
 CREATE OR REPLACE VIEW stu_sco_view
 AS (
    SELECT 
        student.name AS "name",
        course.name AS "course",
        sel_course.score AS "score"
    FROM 
        student
    JOIN sel_course ON student.num = sel_course.studentid
    JOIN course ON sel_course.courseid = course.ID
    WHERE 
        student.dept IN('cs')
    ORDER BY
        student.name
 );
 --end
  
 --创建总分超过250分的同学的视图stu_good_view，显示其姓名、学院、总成绩。
 --begin
 CREATE VIEW stu_good_view
AS(
    SELECT 
        student.num AS "id",
        student.name AS "name",
        student.dept AS "dept",
        SUM(sel_course.score) AS "total"
    FROM 
        student
    JOIN sel_course ON student.num = sel_course.studentid
    GROUP BY
        student.num,
        student.name,
        student.dept
    HAVING 
        SUM(sel_course.score) >= 250
)
 --end

第3关：删除视图

任务描述

本关任务：删除视图。

相关知识

删除视图 执行如下命令删除MyView视图。

1. DROP VIEW MyView;
2. DROP VIEW

编程要求

根据提示，在右侧编辑器补充代码，完成视图的删除。

 --删除视图stu_sco_view。
 --begin
 DROP VIEW stu_sco_view;
 --end
 --删除视图stu_good_view。
  --begin
 DROP VIEW stu_good_view;
 --end