Teradata 简明教程

Teradata - Views

视图是通过查询构建的数据库对象。视图可以使用单个表或通过连接使用多个表构建。其定义永久存储在数据字典中，但它们不会存储数据的副本。视图的数据是动态构建的。

Views are database objects that are built by the query. Views can be built using a single table or multiple tables by way of join. Their definition is stored permanently in data dictionary but they don’t store copy of the data. Data for the view is built dynamically.

视图可能包含表的子行集或表的子列集。

A view may contain a subset of rows of the table or a subset of columns of the table.

Create a View

使用 CREATE VIEW 语句创建视图。

Views are created using CREATE VIEW statement.

Syntax

以下是创建视图的语法。

Following is the syntax for creating a view.

CREATE/REPLACE VIEW <viewname>
AS
<select query>;

Example

考虑以下 Employee 表。

Consider the following Employee table.

EmployeeNo

FirstName

LastName

BirthDate

101

Mike

James

1/5/1980

104

Alex

Stuart

11/6/1984

102

Robert

Williams

3/5/1983

105

Robert

James

12/1/1984

103

Peter

Paul

4/1/1983

以下示例在 Employee 表上创建了一个视图。

The following example creates a view on Employee table.

CREATE VIEW Employee_View
AS
SELECT
EmployeeNo,
FirstName,
LastName,
FROM
Employee;

Using Views

您可以使用常规 SELECT 语句从视图中检索数据。

You can use regular SELECT statement to retrieve data from Views.

Example

以下示例从 Employee_View 中检索记录；

The following example retrieves the records from Employee_View;

SELECT EmployeeNo, FirstName, LastName FROM Employee_View;

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

*** Query completed. 5 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo            FirstName                       LastName
-----------  ------------------------------  ---------------------------
    101                  Mike                           James
    104                  Alex                           Stuart
    102                  Robert                         Williams
    105                  Robert                         James
    103                  Peter                          Paul

Modifying Views

可以使用 REPLACE VIEW 语句修改现有视图。

An existing view can be modified using REPLACE VIEW statement.

以下是修改视图的语法。

Following is the syntax to modify a view.

REPLACE VIEW <viewname>
AS
<select query>;

Example

以下示例修改视图 Employee_View 以添加额外列。

The following example modifies the view Employee_View for adding additional columns.

REPLACE VIEW Employee_View
AS
SELECT
EmployeeNo,
FirstName,
BirthDate,
JoinedDate
DepartmentNo
FROM
Employee;

Drop View

可以使用 DROP VIEW 语句删除现有视图。

An existing view can be dropped using DROP VIEW statement.

Syntax

以下是 DROP VIEW 的语法。

Following is the syntax of DROP VIEW.

DROP VIEW <viewname>;

Example

以下是删除视图 Employee_View 的示例。

Following is an example to drop the view Employee_View.

DROP VIEW Employee_View;

Advantages of Views

  1. Views provide additional level of security by restricting the rows or columns of a table.

  2. Users can be given access only to views instead of base tables.

  3. Simplifies the use of multiple tables by pre-joining them using Views.