[QT] [Sql]sql Learning Record 5_sqlite view

SqliteViews (view)

A view is simply a SQLite statement stored in the database by its associated name. A view is actually a combination of tables that exist in the form of a pre-defined SQLite query.

A view can contain all the rows of a table or select rows from one or more tables. Views can be created from one or more tables, depending on the SQLite query to create the view. 、

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

• The way a user or user group looks for structured data is more natural or intuitive.

• Restricting data access, users can only see the limited data, not the complete table.

• Summarizes the data in various tables for generating reports.

The SQLite view is read-only, so you may not be able to execute a DELETE, INSERT, or UPDATE statement on the view. However, you can create a trigger on the view that is triggered when you try the DELETE, INSERT, or UPDATE view, and the action that you need to make is defined in the trigger content.

Create a View

The view of SQLite is created using the Create view statement. The SQLite view can be created from a single table, multiple tables, or other views.

The basic syntax for CREATE VIEW is as follows:

[| Temporary] VIEW view_name asselect column1, column2..... [condition];         

You can include more than one table in a SELECT statement, much like the way you would in a normal SQL select query. If the optional TEMP or temporary keyword is used, the view is created in the staging database.

Instance

Suppose the company table has the following records:

ID NAME Age ADDRESS SALARY----------  ----------  ---------- ---------- ----------1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-mond 65000.05 david 27  texas 85000.06 kim 22 Span class= "Typ" >south-hall 45000.07 james 24  houston 10000.0      

Now, here is an instance of creating a view from the company table. The view selects only a few columns from the company table:

SQLite> CREATE VIEW company_view asselect ID, NAME, agefrom company;    

You can now query Company_view, similar to how the actual table is queried. Here is an example:

SQLite>* from company_view;   

This will produce the following results:

ID NAME Age----------  ----------  ----------1 Paul 322 allen  253teddy 234  mark 255 david 27 6 kim227 james  24             

Delete a view

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

SQLite> DROP VIEW view_name;  

The following command will delete the Company_view view that we created earlier:

SQLite> DROP VIEW company_view;  

[QT] [Sql]sql Learning Record 5_sqlite view