SQLite Tutorial (Eight): Introduction to command line tools _sqlite

To be good at work, you must first sharpen it.Learning SQLite's command line tools is very helpful for us to learn the SQLite itself. The most basic one is that it makes us learn the SQLite process more relaxed and happy. Let's go back to the official download site at SQLite, which provides command-line tools that support multiple platforms, and we can do most of the most common SQLite operations, like Sqlplus to Oracle. The following list shows the built-in commands for the tool:

Command name	Command description
. Help	Lists all the built-in commands.
. Backup DBName FILE	Backs up the specified database to the specified file, and defaults to the currently connected main database.
. databases	Lists all attached database names and file names in the current connection.
... dump tablename ...	Dumps the main database of the current connection in the format of SQL text, and if the table name is specified, only the data table with the Dump and table name matching. Parameter TableName supports wildcard characters supported by like expressions.
. Echo on| Off	Turns display output on or off.
. exit	Exits the current program.
. Explain on| Off	Turns on or off the select output of the current connection to the human readable form.
. Header (s) on| Off	Whether the column's caption is displayed when the select result is displayed.
. Import FILE TABLE	Imports data from the specified file to the specified table.
. Indices tablename	Displays the names of all indexes, and if you specify a table name, only the index of the datasheet that matches the table name is displayed, and the parameter tablename supports wildcard characters supported by like expressions.
. Log File|off	Turn logging on or off, file can be stdout for standard output, or standard error output stderr.
. Mode Mode TableName	Sets the output mode, where the most commonly used pattern is column mode, which causes the Select Output column to appear left-aligned.
. Nullvalue STRING	Replaces the display of NULL values with the specified string.
. Output FILENAME	Redirects all output of the current command to the specified file.
. Output stdout	Redirects all output of the current command to 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 you can specify a different database name, and the specified database becomes the attached database for the current connection.
. Schema TableName	Displays the creation statement for the datasheet, 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 wildcard characters supported by like expressions.
. Separator STRING	Change the field separator between the output mode and the. Import.
. Show	Displays the current values for various settings.
. Tables TableName	Lists all the table names for the main database in the current connection, and if you specify a table name, only the table name that matches the table name is displayed, and the parameter tablename supports wildcard characters supported by like expressions.
. width NUM1 NUM2 ...	When mode is column, set the width of each field, note that the order of the parameters of the command represents the order of the field output.

See the following common examples:

1. Back up and restore the database.

 code as follows:

-Create a datasheet in the currently connected main database and then back up the main database into the D:/mydb.db file by using the. Backup command.
Sqlite> CREATE TABLE mytable (First_col integer);
sqlite>. Backup ' d:/mydb.db '
Sqlite>. Exit
--to re-establish and sqlite a connection by executing the Sqlite3.exe under the Command Line window.
--Recover the data from the backup file D:/mydb.db to the currently connected main database, and then pass 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.

 code as follows:

--redirect The current output of the command line to D:/myoutput.txt before outputting the declaration statement of the MyTable table that was created earlier to the file.
sqlite>. Output D:/myoutput.txt
sqlite>. Dump mytabl%
Sqlite>. Exit
--Open the destination file in a DOS environment with Notepad.
D:\>notepad Myoutput.txt

3. Displays all attached databases and main databases for the current connection.

code as follows:

sqlite> ATTACH DATABASE ' d:/mydb.db ' as mydb;
sqlite>. Databases
Seq Name file
---  ---------------  ------------------------
0 Main
2 MyDB D:\mydb.db

4). Displays all the data tables in the main database.

 code as follows:

Sqlite>. Tables
MyTable

5. Displays all indexes of the data table that matches the table name mytabl%.

 code as follows:

Sqlite> CREATE INDEX myindex on MyTable (First_col);
sqlite>. Indices mytabl%
Myindex

6. Displays schema information for the data table that matches the table name mytable%.

code as follows:

--The index information that relies on the table is also exported.
sqlite>. Schema mytabl%
CREATE TABLE mytable (First_col integer);
CREATE INDEX Myindex on MyTable (First_col);

7. Format displays the output information of the Select.

 code as follows:

--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 there are no settings.
Sqlite> SELECT * FROM MyTable;
1
2
3
--Displays the column names of the select result set.
--Displays each field as a column.
--Sets the display width of the first column in the subsequent output to 10.
Sqlite>. Header on
sqlite>. Mode column
sqlite>. Width 10
Sqlite> SELECT * FROM MyTable;
First_col
----------
1
2
3