SQLite View definition Query page

Imagine a scenario like this where your database has a table with user information stored in it, which is referred to as the Users table.

. Schema user

CREATE TABLE User (id integer,name text,telphone text,unique (id));

ID stored in table User: equivalent to the user's actual ID, unique

Name: User's user name for display or logon use

Telphone: An additional information, phone number

In general, we also have a table in the program to store user data, which is referred to as the data table, which stores some user data, can be fully customizable. In a multi-user scenario, we typically add a field user_id to represent the user who owns the data.

. Schema data

CREATE TABLE data (message text,time datetime,user_id integer);

This is a simple scenario, and now our requirement is to provide a user interface that can display name, time, information

What do we need to do, (here is not mention of a medium-sized program that I've seen in the logic control splicing This information, code redundancy and ugliness, performance, not to mention ~)

We create a view in the database to resolve (referred to as View1):

. Schema View1

CREATE VIEW view1 as Select (select name from user where id=data.user_id) as name,time,message from data;

Query view1 in the same way as the query table, the difference between view and table is that view can only perform query operations, cannot perform inserts, update data, and so on, because the view does not correspond to the actual physical memory, but a predefined query statement.

Now I've inserted some data into the user and data table.

Sqlite> select * from user;1| Wen |1002| qingqing |100

Sqlite> SELECT * from data;data1|2014-12-08 11:19:19|1data2|2014-12-08 11:19:23|1data3|2014-12-08 11:19:27|1data4| 2014-12-08 11:19:30|1data5|2014-12-08 11:19:34|1data1|2014-12-08 11:19:39|2data2|2014-12-08 11:19:42|2data3| 2014-12-08 11:19:45|2data4|2014-12-08 11:19:49|2data5|2014-12-08 11:19:52|2

Now the result of querying View1 is

Sqlite> SELECT * from View1, Wen |2014-12-08 11:19:19|data1 wen |2014-12-08 11:19:23|data2 wen |2014-12-08 11:19:27|data3 Wen |2014-12-08 11:19:30|data4 Wen |2014-12-08 11:19:34|data5 Qing |2014-12-08 11:19:39|data1 qing |2014-12-08 11:19:42|data2 qingqing | 2014-12-08 11:19:45|data3 qingqing |2014-12-08 11:19:49|data4 qingqing |2014-12-08 11:19:52|data5

The effect of merging two tables is achieved. This view can be queried directly after it is defined in the database (the view is stored in the database). Of course, this can be used as a query for historical data, view can also have a better and more powerful way to display.

For example, the following scenario:
One of the pages in your program is to get the latest display of a record for each current user. The background of the program to keep the data in the database, the foreground fixed frequency query the latest data to display, can be said to display the meaning of real-time.

Create a view (referenced as VIEW2)

. Schema View2

CREATE View View2 as select (select name from user where id=data.user_id) as Name,max (time), the message from the data group by use r_id;

Sqlite> select * from View2; |2014-12-08 11:19:34|data5 qingqing |2014-12-08 11:19:52|data5

This makes a real-time, up-to-date data form for users to view real-time data.

Of course, you do not have to put any displayed data pre-made view such as sorting, filtering, etc., can also be made into a view before the corresponding query operation.

The pre-made view can be used directly as a Tablecontrol/tableview data source in C # or QT, greatly reducing the complexity of the program and improving efficiency.

SQLite View definition Query page