当前位置: 首页 > news >正文

SQL Views(视图)

目录

Views

Declaring Views

Example: View Definition

Example: Accessing a View

Advantages of Views

Triggers on Views

Interpreting a View Insertion(视图插入操作的解释)

The Trigger


Views

A view is a relation defined in terms of stored tables (called base tables ) and other views.(视图是一种储存表和其他视图之间的一种关系)

Two kinds:

  • Virtual = not stored in the database; just a query for constructing the relation.

  • Materialized = actually constructed and stored.

可以将视图分为两种形式:虚拟视图(并没有真实存在,只是构造关系的查询语句)、物化视图

Declaring Views

Declare by:

CREATE [MATERIALIZED] VIEW
<name> AS <query>;

Default is virtual.(缺省值是虚拟的)

Example: View Definition

CanDrink(drinker, beer) is a view “containing” the drinker-beer pairs such that the drinker frequents at least one bar that serves the beer:

CREATE VIEW CanDrink AS
SELECT drinker, beer
FROM Frequents, Sells
WHERE Frequents.bar = Sells.bar;

Example: Accessing a View

  • Query a view as if it were a base table.(视图的查询和普通的表没有什么区别)
  • Also: a limited ability to modify views if it makes sense as a modification of one underlying base table.(能够有限的修改能力,如果对于视图的操作能够合理的映射到基表)

Example query:

SELECT beer FROM CanDrink
WHERE drinker = ’Sally’;

Advantages of Views

  • Focus the Data for Users(为用户聚焦数据)

    • Focus on important or appropriate data only

    • Limit access to sensitive data(限制访问敏感数据)

  • Mask Database Complexity(屏蔽掉数据库的复杂性)

    • Hide complex database design

    • Simplify complex queries, including distributed queries to heterogeneous data

  • Simplify Management of User Permissions

  • Improve Performance

  • Organize Data for Export to Other Application

Triggers on Views

  • Generally, it is impossible to modify a virtual view, because it doesn’t exist.(通常情况下,修改视图是不可能的,因为视图并不存在)

  • But an INSTEAD OF trigger lets us interpret view modifications in a way that makes sense.(但是可以通过建立触发器,使得对于视图的操作能够映射到对应的基表中)

  • Example:

View Synergy has (drinker, beer, bar) triples such that the bar serves the beer, the drinker frequents the bar and likes the beer.

Interpreting a View Insertion(视图插入操作的解释)

  1. We cannot insert into Synergy --- it is a virtual view.

  2. But we can use an INSTEAD OF trigger to turn a (drinker, beer, bar) triple into three insertions of projected pairs, one for each of Likes, Sells, and Frequents.(可以使用触发器将视图投影成三个数对,对应三张表)

  3. Sells.price will have to be NULL.(要注意的是Sells表中的price一定要置空)

The Trigger

CREATE TRIGGER ViewTrig
INSTEAD OF INSERT ON Synergy
REFERENCING NEW ROW AS n
FOR EACH ROW
BEGIN
INSERT INTO LIKES VALUES(n.drinker, n.beer);
INSERT INTO SELLS(bar, beer) VALUES(n.bar, n.beer);
INSERT INTO FREQUENTS VALUES(n.drinker, n.bar);
END;
http://www.lqws.cn/news/69949.html

相关文章:

  • MyBatis源码解析:从 Mapper 接口到 SQL 执行的完整链路
  • 数据库系统概论(十二)SQL 基于派生表的查询 超详细讲解(附带例题表格对比带你一步步掌握)
  • Spring Boot中的WebSocket技术实现
  • oracle sql 语句 优化方法
  • NLP学习路线图(十九):GloVe
  • Spring Boot中保存前端上传的图片
  • Android高级开发第三篇 - JNI异常处理与线程安全编程
  • 使用 PHP 和 Guzzle 对接印度股票数据源API
  • 【Android】MT6835 + MT6631 WiFi进入Meta模式出现WiFi_HQA_OpenAdapter failed
  • 【Elasticsearch】Elasticsearch 核心技术(一):索引
  • 大数据-275 Spark MLib - 基础介绍 机器学习算法 集成学习 随机森铃 Bagging Boosting
  • MySQL数据库从0到1
  • 基于FPGA的VGA显示文字和动态数字基础例程,进而动态显示数据,类似温湿度等
  • 监控 100 台服务器磁盘内存CPU利用率
  • MPTCP 聚合吞吐
  • StarRocks部署方案详解:从单机到分布式集群
  • TDengine 的 AI 应用实战——电力需求预测
  • 【存储基础】存储设备和服务器的关系和区别
  • 第1篇:数据库中间件概述:架构演进、典型方案与应用场景
  • 序列搜索策略
  • PromQL 快速上手
  • 4.RV1126-OPENCV 图像轮廓识别
  • 【SpringBoot】| 接口架构风格—RESTful
  • 什么是 CPU 缓存模型?
  • 深入探讨redis:缓存
  • vue-13(延迟加载路由)
  • Vue-6-前端框架Vue之基于Plotly.js绘制曲线
  • 【软件测试】web自动化:Pycharm+Selenium+Firefox(一)
  • WebSocket与Reactor模式:构建实时交互应用
  • Qt OpenGL 实现交互功能(如鼠标、键盘操作)