SQLite View
A view is simply an SQLite statement stored in the database with a related name. A view is essentially a combination of tables existing in the form of a predefined SQLite query.
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 multiple tables, depending on the SQLite query used to create the view.
A view is a virtual table that allows users to achieve the following:
- Users or user groups can find structured data in a more natural or intuitive way.
- Restrict data access, so users can only see limited data instead of the complete table.
- Summarize data from various tables for report generation.
SQLite views are read-only, so it may not be possible to execute DELETE, INSERT, or UPDATE statements on the view. However, a trigger can be created on the view that fires when attempting to DELETE, INSERT, or UPDATE the view, with the required actions defined within the trigger content.
Creating a View
SQLite views are created using the CREATE VIEW statement. SQLite views can be created from a single table, multiple tables, or other views.
The basic syntax for CREATE VIEW is as follows:
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
You can include multiple tables in the SELECT statement, similar to how it is done in a regular SQL SELECT query. If the optional TEMP or TEMPORARY keyword is used, the view will be created in the temporary database.
Example
Suppose the 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 an example of creating a view from the COMPANY table. The view selects a few columns from the COMPANY table:
sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM COMPANY;
Now, you can query the COMPANY_VIEW, similar to how you would query an actual table. Here 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
Deleting a View
To delete a view, simply use the DROP VIEW statement with the view_name. The basic syntax for DROP VIEW is as follows:
sqlite> DROP VIEW view_name;
The following command will delete the COMPANY_VIEW we created earlier:
sqlite> DROP VIEW COMPANY_VIEW;