May 16, 2021 SQLite
A view is simply an SQLite statement stored in a database by a related name. View is actually a combination of tables that exist as predefined SQLite queries.
A view can contain all rows of a table or select rows from one or more tables. A view can be created from one or more tables, depending on the SQLite query that you want to create the view. 、
A view is a virtual table that allows the user to:
The way a user or group of users looks for structural data is more natural or intuitive.
Limiting data access, users can only see limited data, not a complete table.
Summarize the data in various tables to generate reports.
The SQLite view is read-only, so DELETE, INSERT, or UPDATE statements may not be executed on the view. However, you can create a trigger on the view that is triggered when you try a DELETE, INSERT, or UPDATE view, and the actions that need to be done are defined in the trigger content.
The view of SQLite was created using 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 include multiple tables in a SELECT statement, much like you would in a normal SQL SELECT query. If you use the optional TEMP or TEMPORARY keyword, the view is created in the temporary database.
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's an example of creating a view from a COMPANY table. The view selects only a few columns from the COMPANY table:
sqlite> CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;
You can now query the COMPANY_VIEW, similar to how you query the actual table. Here are some examples:
sqlite> SELECT * FROM COMPANY_VIEW;
This results in the following:
ID NAME AGE ---------- ---------- ---------- 1 Paul 32 2 Allen 25 3 Teddy 23 4 Mark 25 5 David 27 6 Kim 22 7 James 24
To delete a view, simply use a DROP VIEW view_name with a message. The basic syntax of DROP VIEW is as follows:
sqlite> DROP VIEW view_name;
The following command removes the view we created COMPANY_VIEW earlier:
sqlite> DROP VIEW COMPANY_VIEW;