SQLite - Commands

The SQLite provides a simple command-line utility named sqlite3 which allows the user to execute SQL statements manually against an SQLite database. This chapter is a brief introduction of sqlite3 dot ( . ) commands.

• A dot-command must begin with the "." at the left margin with no preceding whitespace.
• The dot-command must be entirely contained on a single input line.
• A dot-command cannot occur in the middle of an ordinary SQL statement. In other words, a dot-command cannot occur at a continuation prompt.
• Dot-commands do not recognize comments.

All the available dot(.) commands are listed below:

Formatting Output

The following sequence of dot commands can be used to format the output:

sqlite> .header on
sqlite> .mode column
sqlite> .timer on
sqlite> 

The above setting will produce the output in the following format.

EmpID     Name        City         Age         Salary
--------  ----------  -----------  ----------  ----------
1         John        London       25          3000.0
2         Marry       New York     24          2750.0
3         Jo          Paris        27          2800.0
4         Kim         Amsterdam    30          3100.0
5         Ramesh      New Delhi    28          3000.0
6         Huang       Beijing      28          2800.0
CPU Time: user 0.000000 sys 0.000000

The sqlite_master Table

The master table holds the key information about the database tables and it is called sqlite_master. The following dot command can be used to see its schema:

sqlite> .schema sqlite_master

This will produce the result which will be similar to:

CREATE TABLE sqlite_master (
   type text,
   name text,
   tbl_name text,
   rootpage integer,
   sql text
);