一、前言
GaussDB是一款基于云计算技术的高性能关系型数据库,支持多种数据模型和分布式架构。在GaussDB中,视图管理是非常重要的一项功能,它可以帮助用户更方便地管理和查询数据。
数据库视图管理是指对数据库中的视图进行创建、修改、删除、查询等操作的过程。
二、准备条件
参考上一篇文章《GaussDB云数据库SQL应用系列-基础使用》
1、登录华为云数据库GaussDB
2、选择对应实例并进入到SQL执行界面
三、视图语法及参数
1、CREATE VIEW语法格式
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
[ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
AS query;
【参数说明】
OR REPLACE:如果视图已存在,则重新定义。
TEMP | TEMPORARY:创建临时视图。
view_name:要创建的视图名称。可以用模式修饰。
column_name:可选的名称列表,用作视图的字段名。如果没有给出,字段名取自查询中的字段名。
view_option_name [= view_option_value]:该子句为视图指定一个可选的参数。目前view_option_name支持的参数仅有security_barrier,当VIEW试图提供行级安全时,应使用该参数。
Query:为视图提供行和列的SELECT或VALUES语句。
2、DROP VIEW语法格式
DROP VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];
【参数说明】
IF EXISTS:如果指定的视图不存在,则发出一个notice而不是抛出一个错误。
view_name:要删除的视图名称。
CASCADE | RESTRICT:CASCADE:级联删除依赖此视图的对象(比如其他视图)。RESTRICT:如果有依赖对象存在,则拒绝删除此视图。此选项为缺省值。
四、操作示例
在数据库中,视图管理通常需要使用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还支持对视图进行权限控制,以保证数据的安全性和隐私性。
——结束
没有回复内容