Sqlite 简明教程

SQLite - Views

视图只不过是一条存储在数据库中并具有关联名称的 SQLite 语句。它实际上是一个以预定义的 SQLite 查询形式存在的表。

A view is nothing more than a SQLite statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQLite query.

视图可以包含一张表的所有行或来自一张或多张表中的选定行。可以基于一个或多个表创建一个视图，这取决于编写用于创建视图的 SQLite 查询。

A view can contain all rows of a table or selected rows from one or more tables. A view can be created from one or many tables which depends on the written SQLite query to create a view.

视图是一种虚拟表，允许用户 −

Views which are kind of virtual tables, allow the users to −

  1. Structure data in a way that users or classes of users find natural or intuitive.

  2. Restrict access to the data such that a user can only see limited data instead of a complete table.

  3. Summarize data from various tables, which can be used to generate reports.

SQLite 视图是只读的，因此你可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是，你可以在视图上创建一个触发器，在试图 DELETE、INSERT 或 UPDATE 视图时触发并执行触发器主体中你需要执行的操作。

SQLite views are read-only and thus you may not be able to execute a DELETE, INSERT or UPDATE statement on a view. However, you can create a trigger on a view that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.

Creating Views

SQLite 视图是使用 CREATE VIEW 语句创建的。SQLite 视图可以从单个表格、多个表格或其他视图创建。

SQLite views are created using the CREATE VIEW statement. SQLite views can be created from a single table, multiple tables, or another view.

以下是基本的 CREATE VIEW 语法。

Following is the basic CREATE VIEW syntax.

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

你可以用类似于在普通 SQL SELECT 查询中使用表格的方式在 SELECT 语句中包含多个表格。如果存在可选的 TEMP 或 TEMPORARY 关键字，将在 temp 数据库中创建视图。

You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query. If the optional TEMP or TEMPORARY keyword is present, the view will be created in the temp database.

Example

考虑下面记录的 COMPANY 表格 −

Consider COMPANY table with the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

以下是一个从 COMPANY 表格创建视图的示例。此视图将仅从 COMPANY 表格中获取几行。

Following is an example to create a view from COMPANY table. This view will be used to have only a few columns from COMPANY table.

sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

你现在可以像查询实际表格一样查询 COMPANY_VIEW。以下是一个示例 −

You can now query COMPANY_VIEW in a similar way as you query an actual table. Following is an example −

sqlite> SELECT * FROM COMPANY_VIEW;

这将产生以下结果。

This will produce the following result.

ID          NAME        AGE
----------  ----------  ----------
1           Paul        32
2           Allen       25
3           Teddy       23
4           Mark        25
5           David       27
6           Kim         22
7           James       24

Dropping Views

要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。基本的 DROP VIEW 语法如下 −

To drop a view, simply use the DROP VIEW statement with the view_name. The basic DROP VIEW syntax is as follows −

sqlite> DROP VIEW view_name;

以下命令将删除我们上一部分创建的 COMPANY_VIEW 视图。

The following command will delete COMPANY_VIEW view, which we created in the last section.

sqlite> DROP VIEW COMPANY_VIEW;