1.4. SQLite command

This chapter will show you the simple but useful commands used by SQLite programmers. These commands are called SQLite dot commands, and they differ in that they do not end with a semicolon.

Let’s type a simple sqlite3 Command, from the SQLite command prompt, you can use various SQLite commands.

$ sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

For a list of available dot commands, you can type “.help” at any time. For example:

sqlite>.help

The above command displays a list of various important SQLite point commands, as follows:

Command	Description
.backup? DB? FILE	Back up the DB database (default is “main”) to the FILE file.
.resume ON \| OFF	Stop when an error occurs. The default is OFF.
.databases	Lists the name of the database and the files it is attached to.
.dump? TABLE?	Dump the database in SQL text format. If the TABLE table is specified, only TABLE tables that match the LIKE schema are dumped.
.echo ON \| OFF	Turns the echo command on or off.
.exit	Exit the SQLite prompt.
.explain ON \| OFF	Turns on or off the output mode suitable for EXPLAIN. If there is no parameter, it is EXPLAIN on, that is, EXPLAIN is enabled.
.header (s) ON \| OFF	Turn the head display on or off.
.help	Displays a message.
.import FILE TABLE	Import data from the FILE file into the TABLE table.
.indices? TABLE?	Displays the names of all indexes. If the TABLE table is specified, only the indexes of the TABLE table that match the LIKE schema are displayed.
.load FILE? ENTRY?	Load an extended library.
.log FILE \| off	Turns the log on or off. The FILE file can be stderr (standard error) / stdout (standard output).
.mode MODE	To set the output mode, MODE can be one of the following:
	Csv comma separated values
	Column left-aligned column
	< table > code for html HTML
	SQL insert (insert) statement of insert TABLE table
	One value per row for line
	List values delimited by a .comparator string
	Tabs values separated by Tab
	Tcl TCL list element
.nullvalue STRING	Output the STRING string at the NULL value.
.output FILENAME	Send the output to a FILENAME file.
.output stdout	Send the output to the screen.
.print STRING…	Output the STRING string verbatim.
.prompt MAIN CONTINUE	Replace the standard prompt.
.quit	Exit the SQLite prompt.
.read FILENAME	Execute the SQL in the FILENAME file.
.schema? TABLE?	Displays the CREATE statement. If the TABLE table is specified, only TABLE tables that match the LIKE schema are displayed.
.separator STRING	Change the output mode and the delimiter used by .import.
.show	Displays the current values of various settings.
.stats ON \| OFF	Turns statistics on or off.
.tables? PATTERN?	Lists the names of tables that match the LIKE schema.
.timeout MS	Try to open the locked table MS for milliseconds.
.width NUM NUM	Sets the column width for column mode.
.timer ON \| OFF	Turns the CPU timer on or off.

Let’s try to use the .show Command to view the default settings of the SQLite command prompt.

sqlite>.show
     echo: off
  explain: off
  headers: off
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>

Make sure there is no space between the sqlite > prompt and the dot command, otherwise it will not work properly.

1.4.1. Formatted output ¶

You can use the following point commands to format the output to the format listed below in this tutorial:

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

The above settings will produce output in the following format:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
         Paul        32          California  20000.0
         Allen       25          Texas       15000.0
         Teddy       23          Norway      20000.0
         Mark        25          Rich-Mond   65000.0
         David       27          Texas       85000.0
         Kim         22          South-Hall  45000.0
         James       24          Houston     10000.0
CPU Time: user 0.000000 sys 0.000000

1.4.2. `sqlite_master` Form ¶

The key information of the database table is stored in the main table and named sqlite_master . To view the table summary, do the following:

sqlite>.schema sqlite_master

This will produce the following results:

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