SQLite Select statement

SQLite's SELECT statement is used to obtain data from a SQLite database table and return the data in the form of a result table. These result tables are also called result sets.

Syntax

The basic syntax of SQLite’s SELECT statement is as follows:

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2... are the fields of the table, and their values are what you want to get. If you want to get all available fields, you can use the following syntax:

SELECT * FROM table_name;

Example

Assume that the COMPANY table has The following records:

ID ADDRESS

ID ---------- ----------
1 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

The following is an example that uses the SELECT statement to obtain and display all these records. Here, the first three commands are used to set up properly formatted output.

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

Finally, you will get the following results:

ID ------ ----------
1 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 10000.0

If you only want to get the specified fields in the COMPANY table, use the following Query:

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

The above query will produce the following results:

ID The above query will produce the following results: ------
1 Paul 20000.0
2 Allen 15000.0
3 Teddy 20000.0
4 Mark 650 00.0
5 David 85000.0
6 Kim 45000.0
7 James 10000.0

Set the width of the output column

Sometimes the output is truncated due to the default width of the column to be displayed

.mode column, in which case the output is truncated. At this time, you can use the .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 sets the width of the first column to 10, the width of the second column to 20, and the width of the third column to 10. Therefore, the above SELECT statement will get the following results: ------ ---------- ---------- ----------

1 32 Texas 15000.0

3 ‐ 20 Rich-Mond 65000.0

5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James James 24 Houston 10000.0

Schema Information

Because all
dot commands

are only available from the SQLite prompt, when you program with SQLite, you want to use the following with

sqlite_master

table SELECT statement to list all tables created in the database:

http://php.cn/sqlite/sqlite-select.html

Assuming that the only COMPANY table already exists in testDB.db, the following results will be produced:

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

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

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

Assuming that the only COMPANY table already exists in testDB.db, the following results will be produced:

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

← SQLite insert statement

SQLite operators →