SQLite tutorial (8): command line tool introduction, sqlite command line

SQLite tutorial (8): command line tool introduction, sqlite command line

To do well, you must first sharpen your tools. Learning SQLite's command line tool is very helpful for us to learn SQLite itself. The most basic one is that it makes learning SQLite easier and more enjoyable. To put it bluntly, the official download website of SQLite provides a command line tool that supports multiple platforms. Using this tool, we can complete most common SQLite operations, just like sqlplus in Oracle. The following lists the built-in commands for this tool:

Command name	Command description
. Help	List all built-in commands.
. Backup DBNAME FILE	Back up a specified database to a specified file. The default value is the currently connected main database.
. Databases	Lists all the attached database names and file names in the current connection.
. Dump TABLENAME...	DUMP is currently connected to the main database in SQL text format. If the table name is specified, it is only a data table that matches the DUMP and table name. The TABLENAME parameter supports wildcards supported by the LIKE expression.
. Echo ON | OFF	Turn on or off the display output.
. Exit	Exit the current program.
. Explain ON | OFF	Enable or disable the SELECT output of the current connection in the form of Human Readable.
. Header (s) ON | OFF	Whether to display the column title when the SELECT result is displayed.
. Import FILE TABLE	Import the data of the specified file to the specified table.
. Indices TABLENAME	Display the names of all indexes. If the table name is specified, only the indexes of the data tables matching the table name are displayed. The TABLENAME parameter supports wildcards supported by the LIKE expression.
. Log FILE | off	Enable or disable the log function. FILE can output stdout for standard output or stderr for standard error output.
. Mode MODE TABLENAME	Set the output mode. The most common mode is column mode, which enables the SELECT output column to be left aligned.
. Nullvalue STRING	Use the specified string to display the NULL value.
. Output FILENAME	Redirects all output of the current command to the specified file.
. Output stdout	Redirects all output of the current command to the standard output (screen ).
. Quit	Exit the current program.
. Read FILENAME	Execute the SQL statement in the specified file.
. Restore DBNAME FILE	Restore the database from the specified file. The default value is the main database. You can also specify other database names. The specified database becomes the currently connected attached database.
. Schema TABLENAME	The table creation statement is displayed. If the table name is specified, only the table creation statements matching the table name are displayed. The TABLENAME parameter supports wildcards supported by the LIKE expression.
. Separator STRING	Change the delimiter between output mode and. import fields.
. Show	Displays the current values of various settings.
. Tables TABLENAME	Lists all the table names of the main database in the current connection. If the table name is specified, only the table names matching the table name are displayed. The TABLENAME parameter supports wildcards supported by the LIKE expression.
. Width NUM1 NUM2...	When MODE is column, set the width of each field. Note: The Parameter order of this command indicates the field output order.

See the following common examples:

1). Back up and restore the database.
 Copy codeThe Code is as follows:
-- Create a data table in the currently connected main database, and then use the. backup command to back up the main database to the D:/mydb. db file.
Sqlite> create table mytable (first_col integer );
Sqlite>. backup 'd:/mydb. db'
Sqlite>. exit
--Execute sqlite3.exe in the command line window to re-establish a connection with SQLite.
-- Restore data from the backup file D:/mydb. db to the currently connected main database, and then run the. tables command to view the mytable table.
Sqlite>. restore 'd:/mydb. db'
Sqlite>. tables
Mytable

2) DUMP data table creation statement to the specified file.
 Copy codeThe Code is as follows:
-- First redirect the current output of the command line to D:/myoutput.txt, and then output the declaration statement of the previously created mytable table to this file.
Sqlite>. output D:/myoutput.txt
Sqlite>. dump mytabl %
Sqlite>. exit
-- Use NotePad to open the target file in the DOS environment.
D: \> notepad myoutput.txt

3) display all the currently connected Attached databases and main databases.
 Copy codeThe Code is as follows:
Sqlite> attach database 'd:/mydb. db' AS mydb;
Sqlite>. databases
Seq name file
------------------------------------------
0 main
2 mydb D: \ mydb. db

4). display all data tables in the main database.
 Copy codeThe Code is as follows:
Sqlite>. tables
Mytable

5). All indexes of the data table matching the table name mytabl % are displayed.
 Copy 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 that matches the table name mytable %.
 Copy codeThe Code is as follows:
-- The index information dependent on the table is also output.
Sqlite>. schema mytabl %
Create table mytable (first_col integer );
Create index myindex on mytable (first_col );

7). Format and display the SELECT output information.
 Copy 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 );
-- Note that the output format of the SELECT result set is not set.
Sqlite> SELECT * FROM mytable;
1
2
3
-- Display the column name of the SELECT result set.
-- Each field is displayed as a column.
-- Set the display width of the first output column to 10.
Sqlite>. header on
Sqlite>. mode column
Sqlite>. width 10
Sqlite> SELECT * FROM mytable;
First_col
----------
1
2
3