SQLite’s SELECT Statement is used to get data from an SQLite database table and return it as a result table. These result tables are also called result sets. The basic syntax of the SELECT statement for SQLite is as follows: Here, column1 column2…. Are the fields of the table, and their values are what you want to get. If you want to get all the available fields, you can use the following syntax: Suppose the COMPANY table has the following records: The following is an example that uses the SELECT statement to get and display all of these records. Here, the first two commands are used to set the correctly formatted output. Finally, the following results will be obtained: If you want to get only the fields specified in the COMPANY table, use the following query: The above query produces the following results: Sometimes, due to the default width of the column to be displayed .mode column In this case, the output is truncated At this point, you can use the .width num, num…. The command sets the width of the display column, as follows: Above. .width The 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. So the above SELECT statement will get the following result: Because all of them 点命令 Available only in the SQLite prompt, so when you program with SQLite, you use the following sqlite_master Table SELECT statement to list all tables created in the database: Assuming that a unique COMPANY table already exists in testDB.db, the following results are produced: You can list complete information about the COMPANY table, as follows: Assuming that a unique COMPANY table already exists in testDB.db, the following results are produced: 1.13.1. Grammar ¶
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
1.13.2. Example ¶
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
sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;
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
sqlite> SELECT ID, NAME, SALARY FROM COMPANY;
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
1.13.3. Set the width of the output column ¶
sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;
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
1.13.4. Schema information ¶
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';
tbl_name
----------
COMPANY
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)