SQLite Learning Manual (command line tool)

工欲善其事, 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	list all built-in commands.
.backup DBNAME FILE
.databases
.dump TABLENAME ...	Dump the currently connected main database in SQL text format, and if a table name is specified, it is only a data table with dump and table name matching. 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 on or off the current connected select output to the human readable form.
.header (s) on| OFF
.import FILE TABLE	Import data from the specified file to the specified table.
.indices TABLENAME	Displays the names of all indexes, and if the table name is specified, displays only the index of the data table that matches the table name, and the parameter tablename supports wildcards supported by the like expression.
.log file|off	turn log on or off, file can be a standard output stdout, or a standard error output stderr.
.mode mode TABLENAME	Set the output mode, where the most commonly used mode is the column mode, leaving the Select Output column aligned to the left display.
.nullvalue STRING
.output FILENAME
.output stdout
. Quit	Exits the current program.
. Read FILENAME	Executes the SQL statement within the specified file.
.restore DBNAME FILE	Restore the database from the specified file, the default is the main database, you can also specify a different database name, the specified database becomes the currently connected attached database.
.schema TABLENAME	Displays the creation statement for the data table, and if you specify a table name, 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 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> . Output D:/myoutput.txt
Sqlite> . Dump mytabl%
Sqlite> . Exit
--Use Notepad to open the target file in a DOS environment.
D:\>Notepad Myoutput.txt

3). Displays all attached databases and main databases that are currently connected.
Sqlite> ATTACH DATABASE ' D:/mydb.db '  as MyDB
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> . Indices Mytabl%
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> . Schema Mytabl%
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> . Header On
Sqlite> . Mode Column
Sqlite> . Width 10
Sqlite> SELECT * FROM MyTable;
First_col
----------
1
2
3

SQLite Learning Manual (command line tool)