SQLite | Query Data | Retrieve Data with SELECT
Use SELECT to retrieve data stored in a table. This article explains the basic syntax.
Retrieving Data with SELECT
Use SELECT to query table data. The basic syntax is as follows.
SELECT column1, column2, ... FROM table_name;
Write the table name after FROM and the desired columns after SELECT. Separate multiple columns with commas.
Without a condition, the query returns every row. Use a WHERE clause to retrieve only rows that satisfy a condition.
–
Create the following table for an example.
create table user (id integer, name text, address text);
sqlite> create table user (id integer, name text, address text);
sqlite>
Insert data into the table.
insert into user values (1, 'devkuma', 'Seoul');
insert into user values (2, 'kimkc', 'Busan');
insert into user values (3, 'araikuma', 'Seoul');
insert into user values (4, 'happykuma', 'Seoul');
insert into user values (5, 'mykuma', 'Daejeon');
sqlite> insert into user values (1, 'devkuma', 'Seoul');
sqlite> insert into user values (2, 'kimkc', 'Busan');
sqlite> insert into user values (3, 'araikuma', 'Seoul');
sqlite> insert into user values (4, 'happykuma', 'Seoul');
sqlite> insert into user values (5, 'mykuma', 'Daejeon');
sqlite>
Use the following query to retrieve the id and name columns from user.
select id, name from user;
The result contains the requested columns from the specified table.
sqlite> select id, name from user;
1|devkuma
2|kimkc
3|araikuma
4|happykuma
5|mykuma
SQLite CLI commands can change how results are displayed. The following settings are used in subsequent examples.
.header on
.mode column
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> select id, name from user;
id name
---------- ----------
1 devkuma
2 kimkc
3 araikuma
4 happykuma
5 mykuma
sqlite>
Retrieving Every Column
You can retrieve every column by listing each column in the SELECT statement.
select id, name, address from user;
sqlite> select id, name, address from user;
id name address
---------- ---------- ----------
1 devkuma Seoul
2 kimkc Busan
3 araikuma Seoul
4 happykuma Seoul
5 mykuma Daejeon
sqlite>
Alternatively, use an asterisk (*) to retrieve all columns in table order.
SELECT * FROM table_name;
Use * to query the user table.
select * from user;
The result contains every column.
sqlite> select * from user;
id name address
---------- ---------- ----------
1 devkuma Seoul
2 kimkc Busan
3 araikuma Seoul
4 happykuma Seoul
5 mykuma Daejeon
sqlite>
Special values such as rowid are not included by *. To include one, list it separately after the asterisk.
select *, rowid from user;
The result now includes all regular columns plus the explicitly requested rowid.
sqlite> select *, rowid from user;
id name address rowid
---------- ---------- ---------- ----------
1 devkuma Seoul 1
2 kimkc Busan 2
3 araikuma Seoul 3
4 happykuma Seoul 4
5 mykuma Daejeon 5
sqlite>