If you want to do a good job, you must first sharpen your tools. Learning SQLite's command line tools well is very helpful for us to learn SQLite itself. The most basic thing is that it makes the process of learning SQLite easier and more enjoyable. Let's get back to the point. On the official SQLite download website, it provides command line tools that support multiple platforms. Using this tool, we can complete most commonly used SQLite operations, just like SQLplus to Oracle. The following list gives the built-in commands for the tool:
| Command name | Command description |
| .help | List all built-in commands. |
| .backup DBNAME FILE | Backup the specified database to the specified file, default to the currently connected main database. |
| .databases | Lists all attached database names and file names in the current connection. |
| .dump TABLENAME ... | DUMP is the current main database connected in SQL text format. If the table name is specified, it is just a data table that matches DUMP and the table name. The parameter TABLENAME supports wildcards supported by LIKE expressions. |
| .echo ON|OFF | Turns the display output on or off. |
| .exit | Exit the current program. |
| .explain ON|OFF | Turn on or close the SELECT output of the current connection to the Human Readable form. |
| .header(s) ON|OFF | Whether to display the column's title when displaying the SELECT results. |
| .import FILE TABLE | Imports the data of the specified file to the specified table. |
| .indices TABLENAME | Displays the names of all indexes. If a table name is specified, only the index of the data table matching the table name is displayed. The parameter TABLENAME supports wildcards supported by LIKE expressions. |
| .log FILE|off | Turn the logging function on or off, FILE can output stdout for standard output, or stderr for standard error. |
| .mode MODE TABLENAME | Set the output mode. The most commonly used mode here is the column mode, so that the SELECT output column is left-aligned. |
| .nullvalue STRING | Use the specified string instead of display of the NULL value. |
| .output FILENAME | Redirects all outputs of the current command to the specified file. |
| .output stdout | Redirects all outputs of the current command to standard output (screen). |
| .quit | Exit the current program. |
| .read FILENAME | Executes SQL statements in the specified file. |
| .restore DBNAME FILE | Restore the database from the specified file, the default is the main database. At this time, other database names can also be specified, and the specified database becomes the attached database currently connected. |
| .schema TABLENAME | Displays the creation statement for the data table. If a table name is specified, only the data table creation statement that matches the table name is displayed. The parameter TABLENAME supports wildcards supported by LIKE expressions. |
| .separator STRING | Change the output mode and the inter-field separator of .import. |
| .show | Displays the current values for various settings. |
| .tables TABLENAME | Lists all table names of the main database in the current connection. If a table name is specified, only the data table name matching the table name is displayed. The parameter TABLENAME supports wildcards supported by LIKE expressions. |
| .width NUM1 NUM2 ... | When MODE is column, set the width of each field. Note: The order of parameter of this command indicates the order of field output. |
See the following commonly used examples:
1). Backup and restore the database.
Copy the codeThe code is as follows:
--Create a data table in the currently connected main database, and then back up the main database to D:/ file through the .backup command.
sqlite> CREATE TABLE mytable (first_col integer);
sqlite> .backup 'D:/'
sqlite> .exit
--Reestablish a connection with SQLite by executing under the command line window.
--Restore data from the backup file D:/ to the currently connected main database, and then use the .tables command to see the mytable table.
sqlite> .restore 'D:/'
sqlite> .tables
mytable
2). Create statement of the DUMP data table to the specified file.
Copy the codeThe code is as follows:
--First redirect the current output of the command line to D:/, and then output the declaration statement of the previously created mytable table to the file.
sqlite> .output D:/
sqlite> .dump mytabl%
sqlite> .exit
--Use notepad to open the target file in the DOS environment.
D:\>notepad
3). Displays all Attached databases and main databases currently connected.
Copy the codeThe code is as follows:
sqlite> ATTACH DATABASE 'D:/' AS mydb;
sqlite> .databases
seq name file
--- --------------- ------------------------
0 main
2 mydb D:\
4). Display all data tables in the main database.
Copy the codeThe code is as follows:
sqlite> .tables
mytable
5). Display all indexes of the data table matching the table name mytabl%.
Copy the codeThe code is as follows:
sqlite> CREATE INDEX myindex on mytable(first_col);
sqlite> .indices mytabl%
myindex
6). Displays the schema information of the data table matching the table name mytable%.
Copy the codeThe code is as follows:
--The index information that depends on this table is also output.
sqlite> .schema mytabl%
CREATE TABLE mytable (first_col integer);
CREATE INDEX myindex on mytable(first_col);
7). Format display SELECT output information.
Copy the codeThe code is as follows:
--Insert test data
sqlite> INSERT INTO mytable VALUES(1);
sqlite> INSERT INTO mytable VALUES(2);
sqlite> INSERT INTO mytable VALUES(3);
--Please note that there is no setting when the output format of the SELECT result set.
sqlite> SELECT * FROM mytable;
1
2
3
--Show column names of the SELECT result set.
--Show each field in column form.
--Set the display width of the first column output afterwards to 10.
sqlite> .header on
sqlite> .mode column
sqlite> .width 10
sqlite> SELECT * FROM mytable;
first_col
----------
1
2
3