The best SQLite Select Statement Tutorial In 2024, In this tutorial you can learn grammar,Examples,Set the width of the output column,Schema Information,
SQLiteSELECT statement used to retrieve data from a SQLite database table in the form of the result table return data.These results are also called a result set.
The basic syntax of SQLite SELECT statement is as follows:
SELECT column1, column2, columnN FROM table_name;
Here, column1, column2 /en. 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;
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
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 /en/en 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
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 )