Android sqlite3 command

Sqlite3: a command line interface for SQLite Databases

Address: http://www.sqlite.org/sqlite.html

The SQLite library contains a command line named sqlite3, which allows you to manually enter and execute SQL commands for the SQLite database. This document provides a brief description of sqlite3.

Start

To start the sqlite3 program, you only need to input the "sqlite3" command with the SQLite database name. If the file does not exist, a new (database) file is created. Then, the sqlite3 program prompts you to enter SQL. Enter the SQL statement (ended with a semicolon (;). After you press the Enter key, the SQL statement is executed.

For example, to create an SQLite database that contains a table "tb11" named "ex1", you can do this:

$ Sqlite3 ex1
SQLite version 3.3.17
Enter ". Help" for instructions
SQLite> Create Table tbl1 (one varchar (10), two smallint );
SQLite> insert into tbl1 values ('Hello! ', 10 );
SQLite> insert into tbl1 values ('Goodbye ', 20 );
SQLite> select * From tbl1;
Hello! | 10
Goodbye | 20
SQLite>

You can knock on the file terminator (usually Ctrl + D) or interrupt character (usually Ctrl + C) of your system ). To terminate the sqlite3 program. Are you sure you want to input a semicolon at the end of each SQL statement! The sqlite3 program queries a semicolon to determine the end of an SQL statement. If you omit the semicolon, sqlite3 will give you a continuous command prompt and wait for you to add more text to the current SQL command. This feature allows you to enter multiple SQL statements with multiple rows, for example:

SQLite> Create Table tbl2 (
...> F1 varchar (30) primary key,
...> F2 text,
...> F3 real
...> );
SQLite>

Question: query the sqlite_master table

The SQLite database framework is stored in a special table named "sqlite_master. You can query this special table by executing "select" like querying other tables. For example:

$ Sqlite3 ex1
SQLite vresion 3.3.10
Enter ". Help" for instructions
SQLite> select * From sqlite_master;
Type = table
Name = tbl1
Tbl_name = tbl1
Rootpage = 3
SQL = CREATE TABLE tbl1 (one varchar (10), two smallint)
SQLite>

However, you cannot execute commands such as drop table, update, insert, or delete in the sqlite_master table. The sqlite_master table is automatically updated when you create, delete, and index a database. You cannot manually change the sqlite_master table.

The structure of the temporary table is not stored in the "sqlite_master" table, because the temporary table is invisible to the application, rather than the table created by the application. The temporary table structure is stored in another table named "sqlite_temp_master. The "sqlite_temp_master" table is the temporary table itself.

Special sqlite3 commands

In most cases, sqlite3 reads the input lines and passes them to the SQLite library for running. However, if the input line starts with a vertex ("."), the row will be intercepted and explained by the sqlite3 program. These "Point commands" are usually used to change the query output format, or to execute a query statement for pre-packaging (prepackaged.

You can enter ". Help" at any time to list available point commands. For example

SQLite>. Help
. Bail On | off stop after hitting an error. Default off
. Databases list names and files of attached Databases
. Dump? Table? ... Dump the database in an SQL text format
. Echo on | off turn command echo on or off
. Exit exit this program
. Explain on | off turn output mode suitable for explain on or off.
. Header (s) on | off turn display of headers on or off
. Help show this message
. Import file table import data from file into table
. Indices table show names of all indices on table
. Load file? Entry? Load an extension Library
. Mode mode? Table? Set output mode where mode is one:
CSV comma-separated values
Column left-aligned columns. (See. width)
HTML <Table> code
Insert SQL insert statements for table
Line one value per line
List values delimited by. Separator string
Tabs tab-separated values
TCL list elements
. Nullvalue string print string in place of null values
. Output Filename send output to filename
. Output stdout send output to the screen
. Prompt main continue Replace the standard prompts
. Quit exit this program
. Read filename Execute SQL in Filename
. Schema? Table? Show the create statements
. Separator string change Separator Used by output mode and. Import
. Show show the current values for various settings
. Tables? Pattern? List names of tables matching a like pattern
. Timeout MS try opening locked tables for MS milliseconds
. Width num... Set column widths for "column" Mode
SQLite>

Change output format

The sqlite3 program can display the query results in eight different formats: "CSV", "column", "html", "insert", "row ", "tabulation" and "TCL ". You can use the ". mode" command to switch between these output formats.

The default output format is "list ". In list mode, each query result record is written in one row and each column is separated by a string delimiter. The default Delimiter is a pipe sign ("| "). List symbols are particularly useful when you output the query result to another program (such as awk) that is processed by another operator.

SQLite>. Mode list
SQLite> select * From tbl1;
Hello | 10
Goodbye | 20
SQLite>

You can use the ". separator" command to change the separator. For example, to change the delimiter to a comma and a space, you can do this:

SQLite>. separator ","
SQLite> select * From tbl1;
Hello, 10
Goodbye, 20
SQLite>

In line mode, each column in a record is displayed in its own row. Each row consists of a column name, an equal sign, and column data. The next record is separated by an empty row. This is an example of Row-mode output:

SQLite>. Mode line
SQLite> select * From tbl1;
One = Hello
Two = 10

One = goodbye
Two = 20
SQLite>

In column mode, each record is displayed as a data column alignment in a separate row. Example:

SQLite>. Mode Column
SQLite> select * From tbl1;
One Two
--------------------
Hello 10
Goodbye 20
SQLite>

By default, each column must have at least 10 characters in width. Data that is too wide will be intercepted. You can use the ". width" command to adjust the column width. As follows:

SQLite>. Width 12 6
SQLite> select * From tbl1;
One Two
------------------
Hello 10
Goodbye 20
SQLite>

In the preceding example, the ". width" command sets the first column width to 12 and the second column width to 6. The width of other columns remains unchanged. You can specify the ". width" parameter that is the same as the number of columns required for your query results.

If you specify a column width of 0, the column width will automatically use the maximum value of the three numbers below as the column width: 10, the header width, and the width of the widest data column. This allows the column to automatically adjust the width. The default value of each column is 0.

You can use the ". Header" command to close the column marker that appears in the first two rows of the output. In the preceding example, the column ID is open. You can use the following method to disable column labeling:

SQLite>. header off
SQLite> select * From tbl1;
Hello 10
Goodbye 20
SQLite>

Another useful output mode is "insert ". In insert mode, the quilt is formatted as a SQL insert statement. You can use the insert mode to generate files (for ease of use) for input to different databases.

When specifying the insert mode, you must specify a specific parameter that is the name of the table to be inserted. For example:

SQLite>. Mode insert new_table
SQLite> select * From tbl1;
Insert into 'new _ table' values ('hello', 10 );
Insert into 'new _ table' values ('Goodbye ', 20 );
SQLite>

The latest output format is "html ". In this mode, sqlite3 writes the query results into an XHTML table. The START <Table> and end </table> are not written, but there are <tr>, <TH>, and <TD> delimiters. HTML output is quite useful for CGI.

Write the result to the file

By default, sqlte3 sends the knot to the standard output. You can use the ". Output" command to change it. The output file name must be used as the output parameter of the. Output command, and all subsequent query results will be written to that file. Use ". Output stdout" to change it to standard output again. For example:

SQLite>. Mode list
SQLite>. Separator |
SQLite>. Output test_file_1.txt
SQLite> select * From tbl1;
SQLite>. Exit
$ Cat test_file_1.txt
Hello | 10
Goodbye | 20
$

Query database structure

The sqlite3 program provides several useful shortcut commands for querying database structures. These cannot be implemented in other ways. These commands are just a shortcut.

For example, to view the table list of a database, you can type ". Tables ".

SQLite>. Tables
Tbl1
Tbl2
SQLite>

The ". Tables" command is similar to setting the list mode and then executing the following query:

Select name from sqlite_master
Where Type in ('table', 'view') and name not like 'sqlite _ %'
Union all
Select name from sqlite_temp_master
Where Type in ('table', 'view ')
Order by 1 in fact, you can view the source code of sqlite3 (in src/shell. C of the source file tree), you can find the specific query above. The ". Indices" command is similar to listing all the indexes of a specific table. The ". indics" command requires a parameter, that is, the name of the table to be indexed. Last, but not least, it is the ". schema" command. Without any parameters, the ". schema" command displays the original create table and create index statements used to create the current database. If you give the ". schema" command a table name, it displays the create statement for creating the table and all its indexes. We can: SQLite>
. Schemacreate table tbl1 (one varchar (10), two smallint) Create Table tbl2 (F1 varchar (30) primary key, F2 text, F3 real) SQLite>. schema tbl2create table tbl2 (F1 varchar (30) primary key, F2 text, F3 real) SQLite> ". the schema command can be implemented by setting the list and executing the following query:

Select SQL from
(Select * From sqlite_master Union all
Select * From sqlite_temp_master)
Where type! = 'Meta'
Order by tbl_name, type DESC, name

. Databases: List database file names
. Tables? Pattern? List? Pattern? Matched table name
. Import file table:
. Dump? Table? Generate an SQL script to form a database table
. Output Filename: import the output to the specified file.
. Output stdout print the output to the screen
. Mode mode? Table? Set the data output mode (CSV, HTML, TCL...
. Nullvalue string replaces the output null string with the specified string
. Read filename: Execute the SQL statement in the specified file
. Schema? Table? Print the SQL statement used to create a database table
. Separator string replaces the field separator with the specified string
. Show print the settings of all SQLite Environment Variables
. Quit exit command line interface