Command-line tools

工欲善其事, its prerequisite. Learning SQLite's command-line tools is very helpful for us to learn about SQLite itself. The basic one is that it makes the process of learning sqlite easier and more enjoyable. Let's go back to the official website of SQLite, which provides command-line tools to support multiple platforms, and we can do most of the usual sqlite operations, like Sqlplus to Oracle. The following list shows the built-in commands for the tool:

Command name	Command description
. Help	Lists all built-in commands.
. Backup DBNAME FILE	Backs up the specified database to the specified file, which defaults to the main database of the current connection.
. databases	Lists all attached database names and file names in the current connection.
. Dump TABLENAME ...	Dump the main database of the current connection in the format of the SQL text, if the table name is specified, only the data table that the dump and the table name match. The parameter tablename supports wildcard characters that are supported by a like expression.
. Echo on| OFF	Turns the display output on or off.
. exit	Exits the current program.
. Explain on| OFF	Turns the select output of the current connection to the human readable form on or off.
. Header (s) on| OFF	Displays the title of the column when the select result is displayed.
. Import FILE TABLE	Imports data from the specified file into the specified table.
. Indices TABLENAME	Displays the names of all indexes, and if a table name is specified, only the index of the data table that matches the table name is displayed, and the parameter tablename supports wildcards supported by the like expression.
. Log File|off	The log function is turned on or off, file can be a standard output stdout, or a standard error output stderr.
. Mode Mode TABLENAME	Sets the output mode, where the most commonly used mode is the column mode, leaving the Select Output column aligned to the left.
. Nullvalue STRING	Replaces the display of a null value with the specified string.
. Output FILENAME	Redirects all output from the current command to the specified file.
. Output stdout	Redirects all output from the current command to the standard output (screen).
. Quit	Exits the current program.
. Read FILENAME	Executes the SQL statement within the specified file.
. Restore DBNAME FILE	Restores the database from the specified file by default to the main database, at which point other database names can be specified, and the specified database becomes the currently connected attached database.
. Schema TABLENAME	Displays the creation statement for the data table, and if the table name is specified, only the data table creation statement that matches the table name is displayed, and the parameter tablename supports wildcards supported by the like expression.
. Separator STRING	Change the inter-field delimiter between the output mode and. Import.
. Show	Displays the current values for the various settings.
. Tables TABLENAME	Lists all the table names of the main database in the current connection, and if the table name is specified, only the data table name that matches the table name is displayed, and the parameter tablename supports wildcards supported by the like expression.
. width NUM1 NUM2 ...	When mode is column, set the width of each field, note that the order of the command's parameters indicates the order in which the fields are output.

See the following common examples:
1). Back up and restore the database.
--Create a data table in the main database of the current connection, and then back up the main database to the D:/mydb.db file by using the. Backup command.
Sqlite> CREATE TABLE mytable (First_col integer);
Sqlite>. Backup' D:/mydb.db '
Sqlite>. Exit
--Re-establish the connection to SQLite by executing the sqlite3.exe in the command-line window.
--Recover data from backup file D:/mydb.db to the main database of the current connection, and then the. Tables command to see the MyTable table.
Sqlite>. Restore' D:/mydb.db '
Sqlite>. Tables
MyTable

2). Dump data table creation statement to the specified file.
--redirects the current output of the command line to D:/myoutput.txt before outputting the declaration statement of the previously created MyTable table to the file.
Sqlite>. OutputD:/myoutput.txt
Sqlite>. Dumpmytabl%
Sqlite>. Exit
--Use Notepad to open the target file in a DOS environment.
D:\>NotepadMyoutput.txt

3). Displays all attached databases and main databases that are currently connected.
Sqlite>ATTACH DATABASE' D:/mydb.db ' asMyDB
Sqlite>. Databases
Seq Name file
---  ---------------  ------------------------
0 Main
2 MyDB D:\mydb.db

4). Displays all data tables in the main database.
Sqlite>. Tables
MyTable

5). Displays all indexes of the data table that match the table name mytabl%.
Sqlite> CREATE INDEX myindex on MyTable (First_col);
Sqlite>. IndicesMytabl%
Myindex

6). Displays the schema information for the data table that matches the table name mytable%.
--The index information that relies on the table is also output.
Sqlite>. SchemaMytabl%
CREATE TABLE mytable (First_col integer);
CREATE INDEX Myindex on MyTable (First_col);

7). Format displays the output information for select.
--Inserting test data
Sqlite> INSERT into MyTable VALUES (1);
Sqlite> INSERT into MyTable VALUES (2);
Sqlite> INSERT into MyTable VALUES (3);
--Note the output format of the select result set when no settings are in use.
Sqlite> SELECT * FROM MyTable;
1
2
3
--Displays the column name of the select result set.
--Displays individual fields as columns.
--Set the display width of the first column after the output to ten.
Sqlite>. HeaderOn
Sqlite>. ModeColumn
Sqlite>. Width10
Sqlite> SELECT * FROM MyTable;
First_col
----------
1
2
3

Command-line tools