Latest web development tutorials

SQLite View

View (View) is only stored in the database in a SQLite statement by the relevant names. View (View) is actually a predefined SQLite query form of a combination of the table.

All rows in the view (View) can contain a table or selected rows from one or more tables. View (View) can be created from one or more tables, depending on the view you want to create a SQLite query. ,

View (View) is a virtual table that allows users to achieve the following:

  • Find a user or group data structure way more natural or intuitive.

  • Restrict access to data, users see only limited data, instead of the full table.

  • Summary data from various tables, used to generate reports.

SQLite view is read-only, and therefore may not be performed DELETE, INSERT or UPDATE statement on a view. But you can create a trigger on a view, when you try to DELETE, INSERT or UPDATE triggered view, needs to be done in the trigger action definition content.

Creating a view

SQLite view is created using theCREATE VIEW statement.SQLite views can be created from a single table, multiple tables or other views.

CREATE VIEW basic syntax is as follows: 

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

You can include multiple tables in a SELECT statement, which in a normal SQL SELECT query very similar way. If you use the optional TEMP or TEMPORARY keyword, it will create a view in a temporary database.

Examples

Suppose COMPANY table has 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

Now, here is a view from the COMPANY table to create an instance. View select only a few columns from the COMPANY table: 

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

Now, you can query COMPANY_VIEW, and query the actual tables similar way. The following are examples: 

sqlite> SELECT * FROM COMPANY_VIEW;

This produces the following results: 

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

Delete View

To delete a view, simply use the DROP VIEW statement with theview_name.DROP VIEW basic syntax is as follows: 

sqlite> DROP VIEW view_name;

The following command will remove COMPANY_VIEW view we created earlier: 

sqlite> DROP VIEW COMPANY_VIEW;