sql视图_SQL视图

sql视图

A VIEW in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

SQL中的VIEW是来自一个或多个表的数据的逻辑子集。 视图用于限制数据访问。

Syntax for creating a View,

创建视图的语法

CREATE or REPLACE VIEW view_name 
    AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition

As you may have understood by seeing the above SQL query, a view is created using data fetched from some other table(s). It's more like a temporary table created with data.

通过查看上面SQL查询，您可能已经理解，使用从其他表中获取的数据来创建视图。 它更像是用数据创建的临时表。

创建一个视图 (Creating a VIEW)

Consider following Sale table,

考虑以下销售表，

oid	order_name	previous_balance	customer
11	ord1	2000	Alex
12	ord2	1000	Adam
13	ord3	2000	Abhi
14	ord4	1000	Adam
15	ord5	2000	Alex

oid	order_name	以前的余额	顾客
11	ord1	2000	亚历克斯
12	ord2	1000	亚当
13	ord3	2000	阿比
14	ord4	1000	亚当
15	ord5	2000	亚历克斯

SQL Query to Create a View from the above table will be,

从上表中创建视图SQL查询将是，

CREATE or REPLACE VIEW sale_view 
AS 
SELECT * FROM Sale WHERE customer = 'Alex';

The data fetched from SELECT statement will be stored in another object called sale_view. We can use CREATE and REPLACE seperately too, but using both together works better, as if any view with the specified name exists, this query will replace it with fresh data.

从SELECT语句中获取的数据将存储在另一个名为sale_view的对象中。 我们也可以分别使用CREATE和REPLACE ，但是将两者一起使用会更好，就像存在任何具有指定名称的视图一样，此查询将用新数据替换它。

显示一个视图 (Displaying a VIEW)

The syntax for displaying the data in a view is similar to fetching data from a table using a SELECT statement.

在视图中显示数据的语法类似于使用SELECT语句从表中获取数据。

SELECT * FROM sale_view;

强制创建视图 (Force VIEW Creation)

FORCE keyword is used while creating a view, forcefully. This keyword is used to create a View even if the table does not exist. After creating a force View if we create the base table and enter values in it, the view will be automatically updated.

强制创建视图时使用FORCE关键字。 即使表不存在，该关键字也可用于创建视图。 创建强制视图后，如果我们创建了基础表并在其中输入了值，则该视图将自动更新。

Syntax for forced View is,

强制视图的语法是

CREATE or REPLACE FORCE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition;

更新视图 (Update a VIEW)

UPDATE command for view is same as for tables.

视图的UPDATE命令与表相同。

Syntax to Update a View is,

更新视图的语法是

UPDATE view-name SET VALUE
WHERE condition;

NOTE: If we update a view it also updates base table data automatically.

注意：如果我们更新视图，它也会自动更新基表数据。

只读视图 (Read-Only VIEW)

We can create a view with read-only option to restrict access to the view.

我们可以创建一个带有只读选项的视图来限制对该视图的访问。

Syntax to create a view with Read-Only Access

使用只读访问权限创建视图的语法

CREATE or REPLACE FORCE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition WITH read-only;

The above syntax will create view for read-only purpose, we cannot Update or Insert data into read-only view. It will throw an error.

上面的语法将创建只读视图，我们无法将数据更新或插入只读视图。 它将引发错误 。

视图类型 (Types of View)

There are two types of view,

有两种类型的视图，

• Simple View

  简单检视

• Complex View

  复杂视图

Simple View	Complex View
Created from one table	Created from one or more table
Does not contain functions	Contain functions
Does not contain groups of data	Contains groups of data

简单检视	复杂视图
从一张表创建	从一个或多个表创建
不包含功能	包含功能
不包含数据组	包含数据组

翻译自: https://www.studytonight.com/dbms/sql-views.php

sql视图