Sqlite3: Command Line shell for SQLite

Address: http://blogold.chinaunix.net/u3/90025/showart_1764687.html

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

The SQLite library contains a name calledSqlite3It allows you to manually enter and execute SQL commands for SQLite databases. This document provides a brief description of sqlite3.

Start

StartSqlite3Program, just enter the name of the SQLite Database"Sqlite3"Command. If the file does not exist, a new (database) file is created. ThenSqlite3The program prompts you to enter SQL. 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 "dot commands" are usually used to change the query output format, or to execute a query statement with a pre-packaged 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" dot command to change the delimiter. 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. Change ". Output stdout" 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 at 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> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite> .schema tbl2
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
)
sqlite>     

 

You can use the ". schema" command to set the list and execute 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 

 

 

Or, if you give a ". schema" command, because you only want to get the structure of a table, the query can be like this:

 

SELECT sql FROM 

 

   (SELECT * FROM sqlite_master UNION ALL 

 

    SELECT * FROM sqlite_temp_master) 

 

WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' 

 

ORDER BY substr(type,2,1), name 

 

You can provide the. schema command with a parameter. In this case, the query can be as follows:

 

SELECT sql FROM 

 

   (SELECT * FROM sqlite_master UNION ALL 

 

    SELECT * FROM sqlite_temp_master) 

 

WHERE tbl_name LIKE '%s' 

 

  AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' 

 

ORDER BY substr(type,2,1), name 

 

 

In the query, "% s" is replaced by your parameter. This allows you to query a subset of the database structure.

 

sqlite> .schema %abc%     

 

Together with these, the ". Table" command also accepts a mode as its parameter. If you give ". Table" a parameter, "%" will be expanded before and after and a like clause will be appended to the query. This allows you to list tables that only match the specified schema.

 

The ". datebasae" command displays a list of all databases opened by the current connection. It will allow up to two at a time. The first is "Main", the database that was initially opened. The second is "Temp", which is used for the temporary table database. The data appended with the attach statement may have an additional database list. The name of the database associated with the first column of output, and the second column is the external file name.

 

sqlite> .databases     

 

Convert the entire database to an ASCII text file     

 

". Dump" command into a single ASCII text file. This file can be used as a pipeline to be passed to the sqlite3 command for conversion back to the database.

 

The best command to copy a database file is:

 

$ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz           

 

It generatesEx1.dump.gzFile, which contains all the information about restructuring the database in the future or on other machines. To reconstruct the database, you only need to enter:

 

$ zcat ex1.dump.gz | sqlite3 ex2           

 

This text format is pure SQL statements, so you can use the. Dump command to export an SQLite database to another commonly used SQL database engine. For example:

 

$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2           

 

Other point commands     

 

The ". Explain" command can be used to set the output format to "column" and set the column width to the reasonable width of the explain command. The explain command is a SQL extension specific to SQLite and is useful for debugging. If any common SQL statement is explained, the SQL command is decomposed and analyzed but not executed. Instead, the VM command sequence is used to execute SQL commands and return a similar query result. For example:

SQLite>. Explain
SQLite>Explain Delete from tbl1 where two <20;
ADDR opcode P1 P2 p3
---------------------------------------------------------------
0 listopen 0 0
1 open 0 1 tbl1
2 next 0 9
3 field 0 1
4 integer 20 0
5 GE 0 2
6 Key 0 0
7 listwrite 0 0
8 goto 0 2
9 Noop 0 0
10 listrewind 0 0
11 listread 0 14
12 Delete 0 0
13 goto 0 11
14 listclose 0 0

 

The ". Timeout" command sets sqlite3 to wait for a total time to lock an attempt to store files, except until the error returns. The default timeout value is 0. Therefore, if any database table or sequence column is locked, an error is returned immediately.

 

Finally, we mentioned that the ". Exit" command causes sqlite3 to exit.

 

Use sqlite3 in commands and scripts     

 

One way to use sqlite3 in a script command is to use "Echo" or "cat" to generate a command sequence in a file, then, sqlite3 is called when the input is repeated from a generated command line. It is useful and adapted to many environments. However, sqlite3 allows a single SQL statement to be used as the second parameter input after the database name in the command line. When the sqlite3 program starts with two parameters, the second parameter is passed to the SQLite database for processing. The query is printed to the standard output in list mode, and then the program exits. This mechanism is designed to make sqlite3 easier to connect programs such as "awk. For example:

 

$ sqlite3 ex1 'select * from tbl1' |
>  awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$      

 

End command line     

 

SQLite commands usually end with a semicolon. In a command line, you can also use the word "go" (case sensitive) or a "/" slash to end a command in the row where it is located. This is often used by SQL Server and Oracle. These will not be useful in sqlite3_exec (), because the command line translates them into semicolons before passing them to the function.

 

Compile sqlite3 from the source file     

 

The sqlite3 program is automatically created when you compile the SQLite library. You only need to copy a source file tree and run "Configure" and "make.

Shell of sqlite3 (1)

Sqlite3.exe is a tool for SQLite to access the database, in the form of command lines.
There are two types of shell commands. The first is the shell command, which starts with a point. The second is the SQL command used to manage the current database, ending with a semicolon.
Shell commands are divided into the following types: control data display mode; Set shell to read and display data; other system control commands

Common Display Mode Control commands:
. Header on | off: Specifies whether to display the column name of the data table.
. Indices table: displays all column names in the table.
. Mode mode: Set the display mode of the data table. The default value is list, and the string set by the. Separator command is used to separate characters (the default value is | ). We prefer the column format.
. Nullvalue string: string is used to fill the data with null values. The default value is an empty string, that is, nothing is displayed.
. Tables pattern: displays tables in pattern.
. Width num...: Set the data display width when it is displayed in column mode.

. Output Filename | stdout: Set the form of data output: standard output of a file or screen.
. Read filename: Read the SQL statement from the file

Other System Control commands:
. Databases: displays the currently matched database names
. Table: displays all tables in the current database.
. Help: displays shell help information.
. Show: display the current shell setting parameters
. Quit and. Exit: Exit Shell