SQLite commands
This chapter will teach you simple but useful commands used by SQLite programmers. These commands are known as SQLite's dot commands, and these commands differ in that they do not end with a semicolon (;).
Let us type a simple sqlite3 command in the command prompt. In the SQLite command prompt, you can use various SQLite commands.
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>
To obtain the available point commands list, you can enter ".help" at any time. For example:
The above command will display a list of various important SQLite point commands as follows:
| Command | Description |
|---|---|
| .backup ?DB? FILE | Backup the DB database (default is "main") to the FILE file. |
| .bail ON|OFF | Stop after an error occurs. Default is OFF. |
| .databases | Lists the names and files of attached databases. |
| .dump ?TABLE? | Dumps the database in SQL text format. If TABLE tables are specified, only TABLE tables matching the LIKE pattern are dumped. |
| .echo ON|OFF | Turn on or off the echo command. |
| .exit | Exit the SQLite prompt. |
| .explain ON|OFF | Turn on or off the output mode suitable for EXPLAIN. If there is no parameter, it is EXPLAIN on, and EXPLAIN is turned on. |
| .header(s) ON|OFF | Turn on or off the header display. |
| .help | Display message. |
| .import FILE TABLE | Import data from the FILE file into the TABLE table. |
| .indices ?TABLE? | Display the names of all indexes. If a TABLE table is specified, only indexes for TABLE tables matching the LIKE pattern are displayed. |
| .load FILE ?ENTRY? | Load an extension library. |
| .log FILE|off | Turn on or off the log. FILE file can be stderr (standard error)/stdout (standard output). |
| .mode MODE | Set the output mode, MODE can be one of the following:
|
| .nullvalue STRING | Outputs the STRING string where the NULL value is. |
| .output FILENAME | Send output to the FILENAME file. |
| .output stdout | Sends output to the screen. |
| .print STRING... | Output the STRING string verbatim. |
| .prompt MAIN CONTINUE | Replaces the standard prompt. |
| .quit | Quit the SQLite prompt. |
| .read FILENAME | Execute the SQL in the FILENAME file. |
| .schema ?TABLE? | Display CREATE statement. If TABLE tables are specified, only TABLE tables matching the LIKE pattern are displayed. |
| .separator STRING | Change the output mode and the separator used by .import. |
| .show | Displays the current values of various settings. |
| .stats ON|OFF | Turn statistics on or off. |
| .tables ?PATTERN? | Lists the names of tables that match the LIKE pattern. |
| .timeout MS | Attempt to open locked table MS microseconds. |
| .width NUM NUM | Sets the column width for "column" mode. |
| .timer ON|OFF | Turn on or off CPU timer measurement. |
Let us try using the .show command to see the default settings of the SQLite command prompt.
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.
Formatted output
You can use the following dot command to format the output into the format listed below in this tutorial:
sqlite>.mode column
sqlite>.timer on
sqlite>
The above settings will produce output in the following format:
------------------------------------------------------------------------------------------------------------------------------------------------------ ------
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
sqlite_master table
The main table stores the key information of the database table and names it sqlite_master. To view the table summary, do the following:
This will produce the following results:
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);
