SQLite Select Statement

grammar

The basic syntax of SQLite SELECT statement is as follows:

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2 ... is a field in the table, that is, their value is that you want to get. If you want to get all the available fields, you can use the following syntax:

SELECT * FROM table_name;

Examples

Suppose 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

Here is an example of using a SELECT statement to retrieve and display all of these records. Here, the first three commands are used to set the correct formatted output.

sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM COMPANY;

Finally, we get the following results:

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

If you want to get the COMPANY field specified in the table, use the following query:

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

The above query will produce the following results:

ID NAME SALARY
---------- ---------- ----------
1 Paul 20000.0
2 Allen 15000.0
3 Teddy 20000.0
4 Mark 65000.0
5 David 85000.0
6 Kim 45000.0
7 James 10000.0

Set the width of the output column

Sometimes, due to the default column width to show cause.mode column, in this case, the output is truncated.At this point, you can use.width num, num .... command to set the width of the display column, as follows:

sqlite> .width 10, 20, 10
sqlite> SELECT * FROM COMPANY;

The above.width command of the first column width of 10, second column width of 20, the third column width of 10.So the above SELECT statement to get the following results:

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

Schema Information

Because all of thepoints only SQLite commandprompt available, so when you are programming with SQLite, you should use the following SELECT statement withsqlite_mastertable to list all tables created in the database:

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

Assuming testDB.db already exists only COMPANY table will result in the following:

tbl_name
----------
COMPANY

You can list the complete information about the COMPANY table, as follows:

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

Assuming testDB.db already exists only COMPANY table will result in the following:

CREATE TABLE COMPANY (
   ID INT PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (50),
   SALARY REAL
)