Detailed description of SQLite command line program

1. sqlite3: a command line interface of the SQLite Database

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.

2. Start

Start sqlite3ProgramYou 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 do this by typing the end of file (usually Ctrl + D) or break character (usually Ctrl + C) on your system. To terminate the sqlite3 program. Make sure you type a semicolon at the end of each SQL statement!

The sqlite3 program determines the end of an SQL statement by looking for a semicolon. 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 over multiple lines, for example:

sqlite> create table tbl2 (
   ...> f1 varchar (30) primary key,
   ...> f2 text,
   ...> f3 real
   ...>);
sqlite>
 

 

3． Off topic: Query the SQLITE_MASTER table

    The SQLite database framework is stored in a special table called "sqlite_master". You can query this particular table by executing a "SELECT" just like any other table. E.g:

$ 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>
    But 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 the database. You cannot change the sqlite_master table manually.

    The structure of the TEMPORARY table is not stored in the "sqlite_master" table, because the TEMPORARY table is not visible to the application, not the application creates this table. The TEMPORARY table structure is stored in another special table called "sqlite_temp_master". The "sqlite_temp_master" table is the temporary table itself.

 

4． sqlite3 special commands

   Most of the time, sqlite3 reads input lines and passes them to the SQLite library to run. But if the input line starts with a dot ("."), Then this line will be intercepted and interpreted by the sqlite3 program itself. These "dot commands" are usually used to change the format of the query output, or to execute a query that pre-packages (pre-defined) the query.

You can type ".help" at any time to list the available dot commands. E.g

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 of:
                         csv Comma-separated values
                         column Left-aligned columns. (See .width)
                         html 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 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 NUM ... Set column widths for "column" mode
sqlite>
4.1 Changing the output format

The sqlite3 program can display the results of a query in eight different formats: "csv", "column", "html", "insert", "row", "tabulation" and "tcl". You can switch between these output formats with the ".mode" dot command.

    The default output format is "list". In list mode, each query result record is written in a row and each column is separated by a string separator. The default delimiter is a pipe symbol ("|"). List notation is especially useful when you output the query results to another program (such as AWK) that adds processing.

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 separator 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 on its own line. Each row consists of a column name, an equal sign, and column data. The next record is separated by a blank line. Here is an example of line 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. Listed as:

sqlite> .mode column
sqlite> select * from tbl1;
one two
---------- ----------
hello 10
goodbye 20
sqlite>
        By default, each column is at least 10 characters wide. 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 example above, the ".width" command sets the width of the first column to 12 and the width of the second column to 6. The other column widths do not change. You can specify as many ".width" parameters as the number of columns you need for your query results.

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

    The column labels that appear in the first two lines of the output can be turned off with the ".header" dot command. In the example above, the column flags are turned on. You can turn off column labeling in the following ways:

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 to look like a SQL INSERT statement. You can use insert mode to generate files (for convenience) for later input to different databases.

    When specifying the insert mode, you must give a specific parameter the name of the table to be inserted. E.g:

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 results of the query as an XHTML table. The opening <TABLE> and ending </ TABLE> (tags) are not written, but there are <TR>, <TH>, and <TD> Equal delimiter. HTML output is quite useful for CGI.

 

4.2 Write the results to a file

By default, sqlte3 sends the knot to standard output. You can change it with the ".output" command. Just take the output file name as the output parameter of the .output command and all subsequent query results will be written to that file. Use ".output stdout" to change to standard output again. E.g:

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
$
5. Query database structure

    The sqlite3 program provides several useful shortcut commands for querying the database structure. These are not impossible to achieve in other ways. These commands are just a shortcut.

    For example, to see a list of tables in a database, you can type ".tables".

sqlite> .tables
tbl1
tbl2
sqlite>
    The ".tables" command is similar to setting the list mode and executing the next 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 check the source code of sqlite3 (you can find it in src / shell.c in the source file tree), and you can find the specific query above.
   The ".indices" command works similarly by listing all indexes for a particular table. The ".indics" command requires one parameter, which is the table name of the indexed table.

      Last, but not least, 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 shows the original CREATE statement that created 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>
    The ".schema" command can be implemented with a list of settings and then 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
    Or, if you give the ".schema" command a parameter, since you only want to get the structure of a table, the query could look 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 a parameter to the .schema command. If that's the case, the query could look like this:

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
    "% S" is replaced by your parameter in the query. This allows you to query a certain subset of the database structure.
sqlite> .schema% abc%
    Along with these, the ".table" command also accepts a schema as its argument. If you give ".table" a parameter, "%" will be expanded back and forth and a LIKE clause will be appended to the query. This allows you to list tables that only match a specific pattern.
    The ".datebasae" command displays a list of all databases opened by the current connection. Will allow at least two at a time. The first is "main", the database that was originally opened. The second is "temp", a database for temporary tables. For data appended with the ATTACH statement, there may be additional database lists. The first column of the output is the database name associated with it, and the second column is the external file name.
sqlite> .databases
    Convert entire database to ASCII text file
    ".dump" command into a single ASCII text file. This file can be used as a pipe to the sqlite3 command to convert back to the database.
    One of the best commands to make a copy of a database archive is:
$ echo '.dump' | sqlite3 ex1 | gzip -c> ex1.dump.gz
    It produces a file named ex1.dump.gz, which contains all the information you need to reconstruct the database later or on another machine. To refactor the database, just type:
$ zcat ex1.dump.gz | sqlite3 ex2
    The text format is pure SQL so you can use the .dump command to export a SQLite database to another commonly used SQL database engine. such as:
$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2
Other dot commands
The ".explain" command can be used to set the output format to "column" and the column width to a width that the EXPLAIN command looks reasonable. The EXPLAIN command is a SQLite-specific SQL extension that is useful for debugging. If any regular SQL is executed by EXPLAIN, the SQL commands are broken down and analyzed but not executed. Instead, the sequence of virtual machine instructions will be used to execute SQL commands and return a similar query result. Such as:

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
   

".Timeout" command to set sqlite3, etc.

The total time to wait for an attempt to store a file lock until the error returns. The default timeout value is 0 so it will immediately return an error if any required database table or sequence column is locked.
    In the end, we mentioned that the ".exit" command caused it to exit sqlite3.

6. 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, and then call sqlite3 when redirecting input from a generated command line. It is useful and adaptable to many environments. But as an added convenience, sqlite3 allows a single SQL statement to be entered as the second parameter after the database name on the command line. When the sqlite3 program starts with two parameters, the second parameter is passed to the SQLite library for processing, the query is printed to standard output in list mode, and the program exits. This mechanism is designed to make sqlite3 easy to connect to programs such as "AWK". E.g:

$ 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
$
7. End command line command
    SQLite commands usually end with a semicolon. On a command line you can also end a command with the word "GO" (case sensitive) or a "/" slash at the line 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 these into semicolons before passing them to the function.
Compile sqlite3 from source files
    The sqlite3 program is created automatically when you compile the SQLite library. Just get a copy of the source tree and run "configure" and then "make".