四、操作示例

在数据库中，视图管理通常需要使用SQL语句来进行操作

1、创建基础表

1）创建一张订单表,其中包含字段：订单编号、订单日期、供货商编号、商品编号、商品名称、商品产地、商品数量、商品进价。

--删除表 order_info
DROP TABLE IF EXISTS order_info;

--创建表 order_info
CREATE TABLE order_info
(
 order_id int PRIMARY KEY
,order_date date not null
,supplier_id int not null
,goods_id char(20) not null
,goods_name char(20) not null
,goods_home varchar(100) not null
,goods_number int not null
,goods_amount int not null
);

2）创建一张销售信息表，其中包含字段：销售编号、销售日期、商品编号、商品名称、商品数量、商品售价。

--删除表 sell_list_info
DROP TABLE IF EXISTS sell_list_info;

--创建表 sell_list_info
CREATE TABLE sell_list_info
(
 sell_id int PRIMARY KEY
,sell_date date not null
,goods_id char(20) not null
,goods_name char(20) not null
,goods_number int not null
,sell_goods_amount int not null
);

3）创建一张商品信息管理表，其中字段包括：商品编号、商品条形码、商品名称、商品产地、商品存量、商品进价、商品售价、供应商编号、仓库编号。

--删除表 goods_info
DROP TABLE IF EXISTS goods_info;

--创建表 goods_info
CREATE TABLE goods_info
(
 goods_id char(20) PRIMARY KEY
,goods_code varchar(50) not null
,goods_name char(20) not null
,goods_home varchar(100) not null
,goods_number int not null
,purchase_goods_amount int not null
,sell_goods_amount int not null
,supplier_id int not null
,warehouse_id int not null
);

2、设置视图应用场景

示例一，方便数据分析

视图可以用于对数据进行聚合、分组和筛选等操作，从而方便数据分析和报告生成。

场景：依据订单表，按商品名称及订货日期统计商品总数和总价、平均价格。

--删除视图
DROP  VIEW IF EXISTS  order_info_view;

--依据订单表，按商品名称及订货日期统计商品总数和总价、平均价格。
CREATE VIEW order_info_view 
AS
SELECT goods_name
      ,order_date
      ,SUM(goods_number) AS total_number
      ,SUM(goods_number * goods_amount) AS total_amount
      ,AVG(goods_amount) AS avg_amount
  FROM order_info 
GROUP BY goods_name, order_date;

示例二，生成月度、年度报表

场景：依据销售表，按年度统计所有销售商品的销售总数量和总销售额，以及其对应的仓库编号和供应商编号。

--删除视图
DROP  VIEW IF EXISTS  sell_list_sum_view;
--依据销售表，按年度统计所有销售商品的销售总数量和总销售额，以及其对应的仓库编号和供应商编号。
CREATE VIEW sell_list_sum_view
AS 
SELECT t1.goods_id
      ,t1.goods_name
      ,t1.s_year
      ,t1.total_number
      ,t1.total_amount
      ,t2.supplier_id
      ,t2.warehouse_id
  FROM
  (SELECT goods_id
         ,EXTRACT(YEAR FROM  sell_date) AS  s_year
         ,goods_name
         ,SUM(goods_number) AS total_number
         ,SUM(goods_number * sell_goods_amount) AS total_amount
    FROM sell_list_info
GROUP BY goods_id
        ,EXTRACT(YEAR FROM  sell_date) 
        ,goods_name
  ) t1
LEFT JOIN 
(SELECT goods_id
       ,supplier_id
       ,warehouse_id 
   FROM  goods_info
) t2
ON t1.goods_id =t2.goods_id

示例三，实现数据安全

在某些情况下，应用程序需要对数据进行访问控制，例如只允许特定的用户或角色访问某些数据。使用视图可以轻松实现这种控制。

场景：只允许某个用户或角色访问某些数据（指定列），例如，只给用户zhangsan 访问“商品信息管理表（无此表访问权限）”的字段“商品名称、商品产地、商品存量、商品售价”。
通过创建视图，并赋予zhangsan访问此视图的权限

--删除视图
DROP  VIEW IF EXISTS  goods_info_view;

--只允许某个用户或角色访问某些数据（指定列）
--例如，给用户zhangsan 访问“商品信息管理表（无此表访问权限）”的字段“商品名称、商品存量、商品售价”。 
--赋予zhangsan访问此视图的权限
CREATE VIEW goods_info_view 
AS
SELECT goods_name
      ,goods_number
      ,sell_goods_amount
  FROM goods_info;

--赋予zhangsan查询权限
GRANT SELECT ON goods_info_VIEW TO zhangsan;

示例四，简化复杂的查询

当需要对多个表进行联合查询时，可以使用视图来将这些表组合成一个单一的查询结果集，从而简化查询过程。

场景：基于上一篇文章中的学生课程成绩表，生成一张视图：获取学生的姓名、课程、成绩、代课老师的信息汇总数据

--删除视图
DROP  VIEW IF EXISTS student_c_view;

--基于上一篇文章中的学生课程成绩表，生成一张视图：获取学生的姓名、课程、成绩、代课老师的信息汇总数据
CREATE VIEW student_c_view 
AS
SELECT t2.sname
      ,t3.cname
      ,t1.scgrade
      ,t3.cteacher
  FROM 
(SELECT sno
       ,cno
       ,scgrade
  FROM sc 
) t1
LEFT JOIN  students t2
ON t1.sno=t2.sno
left join course t3
ON t1.cno=t3.cno

--查看结果
SELECT * FROM student_c_view;

五、小结

数据库视图是一种虚拟表，它是由一个查询语句定义的。视图可以看作是对数据集合的逻辑重组，用户可以通过视图来访问这些数据，而不必关心数据的存储方式和位置。

GaussDB提供了灵活的视图管理功能，包括创建（CREATE）、修改（OR REPLACE）、删除 （DROP）和查询（SELEC）视图等操作。视图是一种虚拟的表，它由一个或多个基本表的数据经过逻辑运算后得到。它可以简化复杂的数据结构，提高查询效率，同时也可以隐藏基本表的细节，用户可以根据自己的需求选择不同的视图类型和定义视图的列，以满足不同的数据分析和查询需求。同时，GaussDB还支持对视图进行权限控制，以保证数据的安全性和隐私性。

本篇就分享到这里，欢迎交流。