Sqlite3 command line simple tutorial

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 score, 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>. 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 one 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

Contains a file named ex1.dump.gz that contains all the information you need to reconstruct 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 the ". Exit" command, which 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 its good line. 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.