写点什么

数据库 SQL: 视图

发布于: 2020 年 11 月 15 日
数据库SQL:视图

从 SQL的角度来看,视图和表是相同的,两者的区别在于表中保存的是实际的数据,视图保存的是SELECT语句,视图本身并不存储数据,当我们从视图中读取数据时,视图会在内部执行该 SELECT 语句并创建出一张临时表。使用视图,可以轻松完成跨多表查询数据等复杂操作,而且视图中的数据会随着原表变化自动更新。使用视图具有如下的优点:

  • 由于视图不保存实际数据,可以节省存储设备的容量,特别是在业务量很大情况下,优势会更加突出。

  • 将频繁使用的 SELECT 语句保存成视图避免重复书写。特别是在进行汇总以及复杂的查询条件导致 SELECT 语句非常庞大时,使用视图可以大大提高效率。



首先,创建一个Product表,并向表中插入一些数据,为后续指令执行创建一个实验数据

-- 创建Product表
CREATE TABLE Product(
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY(product_id)
);
-- 插入数据
BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;

创建视图

创建视图的语法:

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>

创建视图需要注意其存在的限制:

  • 定义视图不能使用ORDER BY子句



下面实现创建商品合计的视图功能

-- 创建视图
CREATE VIEW ProductCNT (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
-- 视图查询
SELECT * FROM ProductCNT;

还可以实现基于视图创建多重视图:

-- 创建多重视图
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductCNT
WHERE product_type = '办公用品';
-- 视图查询
SELECT* FROM ProductSumJim;

更新视图

标准 SQL 中有这样的规定:如果定义视图的 SELECT 语句能够满足某些条件,比较有代表性的条件有:

  • SELECT 子句中未使用 DISTINCT

  • FROM 子句中只有一张表

  • 未使用 GROUP BY 子句

  • 未使用 HAVING 子句



基于上述描述,我们无法向ProductSumJim中更新数据,如果执行会出现:

/*
ERROR: 错误: 无法插入到视图"productcnt"
DETAIL: 包含 GROUP BY 的视图列不能自动更新.
HINT: 启用向视图插入操作, 要提供INSTEAD OF INSERT触发器或者提供一个无条件的 ON INSERT DO INSTEAD 规则.
SQL state: 55000
*/
INSERT INTO ProductSumJim VALUES ('电器制品', 5);

不是通过汇总得到的视图就可以进行更新,下面是一个实现数据更新的视图示例:

CREATE VIEW ProductJim (product_id, product_name, product_type,
sale_price, purchase_price, regist_date)
AS
SELECT *
FROM Product
WHERE product_type = '办公用品';
INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');
select * from ProductJim;

权限修改

CREATE OR REPLACE RULE insert_rule
AS ON INSERT
TO ProductJim DO INSTEAD
INSERT INTO Product VALUES (new.product_id,new.product_name,
new.product_type, new.sale_price, new.purchase_price, new.regist_date);



删除视图

删除视图的语法:

DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)

例如,我们实现删除ProductCNT视图:

-- CASCADE将相关依赖也删除,此处会递归将ProductSumJim视图也删除
DROP VIEW ProductCNT CASCADE;




参考资料

  • 书籍《SQL基础教程(第2版)》

发布于: 2020 年 11 月 15 日阅读数: 24
用户头像

正向成长 2018.08.06 加入

想要坚定地做大规模数据处理(流数据方向),希望结合结合批处理的传统处理方式,以及之后流批混合处理方向进行学习和记录。

评论

发布
暂无评论
数据库SQL:视图