SQLite | Views | Create a View
A view is a virtual table defined by selected columns and query conditions. This article explains how to create and use views.
Creating a View
Use the following syntax to create a view.
CREATE VIEW view_name AS SELECT statement;
After AS, write a SELECT statement that defines the source columns and conditions.
create view myview as select name, price from product where price> 3000;
A view exposes part of a table like another table. You can query it, but cannot insert or update data through it in this example.
Create the source table.
create table user (id integer, name text, address text, old integer);
sqlite> create table user (id integer, name text, address text, old integer);
sqlite>
Insert data into the table.
insert into user values (1, 'devkuma', 'Seoul', 23);
insert into user values (2, 'kimkc', 'Busan', 19);
insert into user values (3, 'araikuma', 'Seoul', 38);
insert into user values (4, 'happykuma', 'Seoul', 24);
insert into user values (5, 'mykuma', 'Daejeon', 18);
sqlite> insert into user values (1, 'devkuma', 'Seoul', 23);
sqlite> insert into user values (2, 'kimkc', 'Busan', 19);
sqlite> insert into user values (3, 'araikuma', 'Seoul', 38);
sqlite> insert into user values (4, 'happykuma', 'Seoul', 24);
sqlite> insert into user values (5, 'mykuma', 'Daejeon', 18);
sqlite>
Create a view named seouluser.
create view seouluser as select id, name from user where address = 'Seoul';
sqlite> create view seouluser as select id, name from user where address = 'Seoul';
sqlite>
The view exposes id and name from user for rows whose address is Seoul. It does not store those rows itself.
Query the view.
select * from tokyouser;
sqlite> select * from seouluser;
1|devkuma
3|araikuma
4|happykuma
The view can be queried like a table and produces the same result as the underlying SELECT.
select id, name from user where address = 'Tokyo';
sqlite> select id, name from user where address = 'Seoul';
1|devkuma
3|araikuma
4|happykuma
sqlite>
A view makes frequently needed data easy to query.
Data Returned by a View
A view stores only a query definition, not the data itself. Each query reads current data from the source table, so results can change when the source changes.
Inspect the current source data.
select * from user;
sqlite> select * from user;
1|devkuma|Seoul|23
2|kimkc|Busan|19
3|araikuma|Seoul|38
4|happykuma|Seoul|24
5|mykuma|Daejeon|18
Query the view.
select * from seouluser;
sqlite> select * from seouluser;
1|devkuma
3|araikuma
4|happykuma
Add data to the source table and query the view again. The new matching row appears.
insert into user values (6, 'yourkuma', ' Seoul', 17);
sqlite> insert into user values (6, 'yourkuma', ' Seoul', 17);
sqlite>
sqlite> select * from seouluser;
1|devkuma
3|araikuma
4|happykuma
6|yourkuma
sqlite>
A view always reads from its source rather than maintaining a separate copy.
Inserting, Updating, and Deleting Through a View
You can query this view, but cannot insert, delete, or update through it.
Attempting modification produces Error: cannot modify seouluser because it is a view.
insert into seouluser values (8, 'kuma');
sqlite> insert into seouluser values (8, 'kuma');
Error: cannot modify seouluser because it is a view
sqlite>
Listing Views
The .tables command lists both tables and views.
.tables
sqlite> .table
seouluser user
sqlite>
Query sqlite_master to obtain view names and their defining SQL.
select name, sql from sqlite_master where type = 'view';
sqlite> select name, sql from sqlite_master where type = 'view';
seouluser|CREATE VIEW seouluser as select id, name from user where address = 'Seoul'
sqlite>