1.39. SQLite View (View)

A View is nothing more than a SQLite statement stored in the database by the associated name. A View is actually a combination of tables that exist in the form of a predefined SQLite query.

A View can contain all rows of a table or select rows from one or more tables. Views (View) can be created from one or more tables, depending on the SQLite query in which you want to create the view.

A View is a virtual table that allows the user to implement the following:

Users or user groups find structural data in a more natural or intuitive way.
Restrict data access, and users can only see limited data, not complete tables.
Summarize the data from various tables to generate reports.

The SQLite view is read-only and may not be executed on the view DELETE , INSERT or UPDATE Statement. But you can create a trigger on the view when you try DELETE , INSERT or UPDATE When the view is triggered, the actions to be done are defined in the contents of the trigger.

1.39.1. Create a view ¶

The view of SQLite is to use the CREATE VIEW Statement. SQLite views can be created from a single table, multiple tables, or other views.

The basic syntax of CREATE VIEW is as follows:

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

You can find it in the SELECT Statement contains multiple tables, which is the same as in the normal SQL SELECT The manner in the query is very similar. If you use the optional TEMP Or TEMPORARY Keyword, the view is created in the tempdb.

1.39.2. Example ¶

Hypothetical COMPANY The table has the following records:

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

Now, here is an example from COMPANY Table creates an instance of the view. The view is only from COMPANY Select several columns from the table:

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

Now, you can query COMPANY_VIEW Similar to the way you query the actual table Here is an example:

sqlite> SELECT * FROM COMPANY_VIEW;

This will produce the following results:

ID          NAME        AGE
----------  ----------  ----------
         Paul        32
         Allen       25
         Teddy       23
         Mark        25
         David       27
         Kim         22
         James       24

1.39.3. Delete View ¶

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

sqlite> DROP VIEW view_name;

The following command will delete the COMPANY_VIEW View:

sqlite> DROP VIEW COMPANY_VIEW;