YouTipThis chapter will explain the simple yet useful commands used by SQLite programmers. These commands are known as SQLite dot commands, and they differ in that they do not end with a semicolon (;).
Let's type a simple sqlite3 command at the command prompt. At the SQLite command prompt, you can use various SQLite commands.
$ sqlite3 SQLite version 3.3.6 Enter ".help" for instructions sqlite>
To get a list of available dot commands, you can type ".help" at any time. For example:
sqlite> .help
The above command will display a list of various important SQLite dot commands, as shown below:
| Command | Description |
|---|---|
| .backup ?DB? FILE | Backup DB database (default is "main") to FILE file. |
| .bail ON|OFF | Stop after an error. Default is OFF. |
| .databases | List the names and attached files of databases. |
| .dump ?TABLE? | Dump the database in SQL text format. If TABLE is specified, only tables matching the LIKE pattern are dumped. |
| .echo ON|OFF | Turn echo on or off. |
| .exit | Exit the SQLite prompt. |
| .explain ON|OFF | Turn output mode suitable for EXPLAIN on or off. Without arguments, it is EXPLAIN on. |
| .header(s) ON|OFF | Turn header display on or off. |
| .help | Show messages. |
| .import FILE TABLE | Import data from FILE file into TABLE table. |
| .indices ?TABLE? | Show names of all indices. If TABLE is specified, only indices for tables matching the LIKE pattern are shown. |
| .load FILE ?ENTRY? | Load an extension library. |
| .log FILE|off | Turn logging on or off. FILE can be stderr (standard error) / stdout (standard output). |
| .mode MODE | Set output mode, MODE can be one of the following: * csv Comma-separated values * column Left-aligned columns * html HTML <table> code * insert SQL insert statement for TABLE * line One value per line * list Values separated by .separator string * tabs Tab-separated values * tcl TCL list elements |
| .nullvalue STRING | Output STRING string in place of NULL values. |
| .output FILENAME | Send output to FILENAME file. |
| .output stdout | Send output to the screen. |
| .print STRING... | Output STRING string verbatim. |
| .prompt MAIN CONTINUE | Replace the standard prompt. |
| .quit | Exit the SQLite prompt. |
| .read FILENAME | Execute SQL in FILENAME file. |
| .schema ?TABLE? | Show CREATE statement. If TABLE is specified, only tables matching the LIKE pattern are shown. |
| .separator STRING | Change the separator used by output mode and .import. |
| .show | Show current values of various settings. |
| .stats ON|OFF | Turn statistics on or off. |
| .tables ?PATTERN? | List names of tables matching the LIKE pattern. |
| .timeout MS | Try opening locked tables for MS milliseconds. |
| .width NUM NUM | Set column widths for "column" mode. |
| .timer ON|OFF | Turn CPU timer on or off. |
Let's try using the .show command to see the default settings of the SQLite command prompt.
sqlite> .show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
Ensure there is no space between the sqlite> prompt and the dot command, otherwise it will not work properly.
You can use the following dot commands to format the output into 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
The main table stores key information about database tables and is named sqlite_master. To view the table summary, you can do the following:
sqlite> .schema sqlite_master
This will produce the following result:
CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text );