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 ---------- ---------- ---------- ---------- ---------- 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 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 ); 

1.4. SQLite command

1.4.1. Formatted output ¶

1.4.2. sqlite_master Form ¶

1.4.2. `sqlite_master` Form ¶