SQLite using tutorial 9 Select statement

Http://www.runoob.com/sqlite/sqlite-select.html

SqliteSelect Statement

SQLite's SELECT statement is used to fetch data from the SQLite database table and return the data as a result table. These result tables are also known as result sets.

Grammar

The basic syntax for 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 the available fields, you can use the following syntax:

SELECT * FROM table_name;

　　

Instance

Suppose the company table has the following records:

ID          NAME        age         ADDRESS     SALARY----------  ----------  ----------  ----------  -------- --1           Paul          California  20000.02           Allen          Texas       15000.03           Teddy          Norway      20000.04           Mark          rich-mond   65000.05           David          Texas       85000.06           Kim          south-hall  45000.07           James          Houston     10000.0

Here is an instance that uses the SELECT statement to get and display all of these records. Here, the first three commands are used to set the correct formatted output.

Sqlite>.header Onsqlite>.mode columnsqlite> SELECT * from company;

Finally, you will get the following results:

ID          NAME        age         ADDRESS     SALARY----------  ----------  ----------  ----------  -------- --1           Paul          California  20000.02           Allen          Texas       15000.03           Teddy          Norway      20000.04           Mark          rich-mond   65000.05           David          Texas       85000.06           Kim          south-hall  45000.07           James          Houston     10000.0

If you want to get only the fields specified in the company table, use the following query:

Sqlite> SELECT ID, NAME, SALARY from company;

The above query produces the following results:

ID          NAME        SALARY----------  ----------  ----------1           Paul        20000.02           Allen       15000.03           Teddy       20000.04           Mark        65000.05           David       85000.06           Kim         45000.07           James       10000.0

　　

Set the width of the output column

Sometimes the output is truncated because the default width of the column to be displayed causes . Mode column, in this case. At this point, you can use the . Width num, num ... command to set the width of the displayed column as follows:

Sqlite>.width, 10sqlite>select * from company;

The . Width command above 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 SELECT statement above will result in the following:

ID          NAME                  age         ADDRESS     SALARY----------  --------------------  ----------  ----------  ----------1           Paul          California  20000.02           Allen          Texas       15000.03                 Teddy          Norway      20000.04           Mark          rich-mond   65000.05           David          Texas       85000.06           Kim          south-hall  45000.07           James          Houston     10000.0

　　

Schema Information

Because all point commands are available only at the SQLite prompt, when you are programming with SQLite, you use the following SELECT with the sqlite_master table Statement to list all tables created in the database:

Sqlite> SELECT tbl_name from sqlite_master WHERE type = ' table ';

Assuming that a unique 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 a unique company table already exists in testdb.db, the following results will be produced:

CREATE TABLE Company (   ID int PRIMARY KEY isn't     null,   NAME           TEXT not    null,   age            INT     not NULL,   ADDRESS        CHAR (+),   SALARY         REAL)

　　

SQLite using tutorial 9 Select statement