|
Packit |
87b942 |
.\" Hey, EMACS: -*- nroff -*-
|
|
Packit |
87b942 |
.\" First parameter, NAME, should be all caps
|
|
Packit |
87b942 |
.\" Second parameter, SECTION, should be 1-8, maybe w/ subsection
|
|
Packit |
87b942 |
.\" other parameters are allowed: see man(7), man(1)
|
|
Packit |
87b942 |
.TH SQLITE3 1 "Fri Oct 31 10:41:31 EDT 2014"
|
|
Packit |
87b942 |
.\" Please adjust this date whenever revising the manpage.
|
|
Packit |
87b942 |
.\"
|
|
Packit |
87b942 |
.\" Some roff macros, for reference:
|
|
Packit |
87b942 |
.\" .nh disable hyphenation
|
|
Packit |
87b942 |
.\" .hy enable hyphenation
|
|
Packit |
87b942 |
.\" .ad l left justify
|
|
Packit |
87b942 |
.\" .ad b justify to both left and right margins
|
|
Packit |
87b942 |
.\" .nf disable filling
|
|
Packit |
87b942 |
.\" .fi enable filling
|
|
Packit |
87b942 |
.\" .br insert line break
|
|
Packit |
87b942 |
.\" .sp <n> insert n+1 empty lines
|
|
Packit |
87b942 |
.\" for manpage-specific macros, see man(7)
|
|
Packit |
87b942 |
.SH NAME
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
\- A command line interface for SQLite version 3
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
.SH SYNOPSIS
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
.RI [ options ]
|
|
Packit |
87b942 |
.RI [ databasefile ]
|
|
Packit |
87b942 |
.RI [ SQL ]
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
.SH SUMMARY
|
|
Packit |
87b942 |
.PP
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
is a terminal-based front-end to the SQLite library that can evaluate
|
|
Packit |
87b942 |
queries interactively and display the results in multiple formats.
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
can also be used within shell scripts and other applications to provide
|
|
Packit |
87b942 |
batch processing features.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
.SH DESCRIPTION
|
|
Packit |
87b942 |
To start a
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
interactive session, invoke the
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
command and optionally provide the name of a database file. If the
|
|
Packit |
87b942 |
database file does not exist, it will be created. If the database file
|
|
Packit |
87b942 |
does exist, it will be opened.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
For example, to create a new database file named "mydata.db", create
|
|
Packit |
87b942 |
a table named "memos" and insert a couple of records into that table:
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
$
|
|
Packit |
87b942 |
.B sqlite3 mydata.db
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
SQLite version 3.8.8
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
Enter ".help" for instructions
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
sqlite>
|
|
Packit |
87b942 |
.B create table memos(text, priority INTEGER);
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
sqlite>
|
|
Packit |
87b942 |
.B insert into memos values('deliver project description', 10);
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
sqlite>
|
|
Packit |
87b942 |
.B insert into memos values('lunch with Christine', 100);
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
sqlite>
|
|
Packit |
87b942 |
.B select * from memos;
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
deliver project description|10
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
lunch with Christine|100
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
sqlite>
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
If no database name is supplied, the ATTACH sql command can be used
|
|
Packit |
87b942 |
to attach to existing or create new database files. ATTACH can also
|
|
Packit |
87b942 |
be used to attach to multiple databases within the same interactive
|
|
Packit |
87b942 |
session. This is useful for migrating data between databases,
|
|
Packit |
87b942 |
possibly changing the schema along the way.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
Optionally, a SQL statement or set of SQL statements can be supplied as
|
|
Packit |
87b942 |
a single argument. Multiple statements should be separated by
|
|
Packit |
87b942 |
semi-colons.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
For example:
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
$
|
|
Packit |
87b942 |
.B sqlite3 -line mydata.db 'select * from memos where priority > 20;'
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
text = lunch with Christine
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
priority = 100
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
.SS SQLITE META-COMMANDS
|
|
Packit |
87b942 |
.PP
|
|
Packit |
87b942 |
The interactive interpreter offers a set of meta-commands that can be
|
|
Packit |
87b942 |
used to control the output format, examine the currently attached
|
|
Packit |
87b942 |
database files, or perform administrative operations upon the
|
|
Packit |
87b942 |
attached databases (such as rebuilding indices). Meta-commands are
|
|
Packit |
87b942 |
always prefixed with a dot (.).
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
A list of available meta-commands can be viewed at any time by issuing
|
|
Packit |
87b942 |
the '.help' command. For example:
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
sqlite>
|
|
Packit |
87b942 |
.B .help
|
|
Packit |
87b942 |
.nf
|
|
Packit |
87b942 |
.tr %.
|
|
Packit |
87b942 |
%backup ?DB? FILE Backup DB (default "main") to FILE
|
|
Packit |
87b942 |
%bail on|off Stop after hitting an error. Default OFF
|
|
Packit |
87b942 |
%clone NEWDB Clone data into NEWDB from the existing database
|
|
Packit |
87b942 |
%databases List names and files of attached databases
|
|
Packit |
87b942 |
%dump ?TABLE? ... Dump the database in an SQL text format
|
|
Packit |
87b942 |
If TABLE specified, only dump tables matching
|
|
Packit |
87b942 |
LIKE pattern TABLE.
|
|
Packit |
87b942 |
%echo on|off Turn command echo on or off
|
|
Packit |
87b942 |
%eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN
|
|
Packit |
87b942 |
%exit Exit this program
|
|
Packit |
87b942 |
%explain ?on|off? Turn output mode suitable for EXPLAIN on or off.
|
|
Packit |
87b942 |
With no args, it turns EXPLAIN on.
|
|
Packit |
87b942 |
%fullschema Show schema and the content of sqlite_stat tables
|
|
Packit |
87b942 |
%headers on|off Turn display of headers on or off
|
|
Packit |
87b942 |
%help Show this message
|
|
Packit |
87b942 |
%import FILE TABLE Import data from FILE into TABLE
|
|
Packit |
87b942 |
%indices ?TABLE? Show names of all indices
|
|
Packit |
87b942 |
If TABLE specified, only show indices for tables
|
|
Packit |
87b942 |
matching LIKE pattern TABLE.
|
|
Packit |
87b942 |
%load FILE ?ENTRY? Load an extension library
|
|
Packit |
87b942 |
%log FILE|off Turn logging on or off. FILE can be stderr/stdout
|
|
Packit |
87b942 |
%mode MODE ?TABLE? Set output mode where MODE is one of:
|
|
Packit |
87b942 |
csv Comma-separated values
|
|
Packit |
87b942 |
column Left-aligned columns. (See .width)
|
|
Packit |
87b942 |
html HTML code
|
|
Packit |
87b942 |
insert SQL insert statements for TABLE
|
|
Packit |
87b942 |
line One value per line
|
|
Packit |
87b942 |
list Values delimited by .separator string
|
|
Packit |
87b942 |
tabs Tab-separated values
|
|
Packit |
87b942 |
tcl TCL list elements
|
|
Packit |
87b942 |
%nullvalue STRING Use STRING in place of NULL values
|
|
Packit |
87b942 |
%once FILENAME Output for the next SQL command only to FILENAME
|
|
Packit |
87b942 |
%open ?FILENAME? Close existing database and reopen FILENAME
|
|
Packit |
87b942 |
%output ?FILENAME? Send output to FILENAME or stdout
|
|
Packit |
87b942 |
%print STRING... Print literal STRING
|
|
Packit |
87b942 |
%prompt MAIN CONTINUE Replace the standard prompts
|
|
Packit |
87b942 |
%quit Exit this program
|
|
Packit |
87b942 |
%read FILENAME Execute SQL in FILENAME
|
|
Packit |
87b942 |
%restore ?DB? FILE Restore content of DB (default "main") from FILE
|
|
Packit |
87b942 |
%save FILE Write in-memory database into FILE
|
|
Packit |
87b942 |
%schema ?TABLE? Show the CREATE statements
|
|
Packit |
87b942 |
If TABLE specified, only show tables matching
|
|
Packit |
87b942 |
LIKE pattern TABLE.
|
|
Packit |
87b942 |
%separator STRING ?NL? Change separator used by output mode and .import
|
|
Packit |
87b942 |
NL is the end-of-line mark for CSV
|
|
Packit |
87b942 |
%shell CMD ARGS... Run CMD ARGS... in a system shell
|
|
Packit |
87b942 |
%show Show the current values for various settings
|
|
Packit |
87b942 |
%stats on|off Turn stats on or off
|
|
Packit |
87b942 |
%system CMD ARGS... Run CMD ARGS... in a system shell
|
|
Packit |
87b942 |
%tables ?TABLE? List names of tables
|
|
Packit |
87b942 |
If TABLE specified, only list tables matching
|
|
Packit |
87b942 |
LIKE pattern TABLE.
|
|
Packit |
87b942 |
%timeout MS Try opening locked tables for MS milliseconds
|
|
Packit |
87b942 |
%timer on|off Turn SQL timer on or off
|
|
Packit |
87b942 |
%trace FILE|off Output each SQL statement as it is run
|
|
Packit |
87b942 |
%vfsname ?AUX? Print the name of the VFS stack
|
|
Packit |
87b942 |
%width NUM1 NUM2 ... Set column widths for "column" mode
|
|
Packit |
87b942 |
Negative values right-justify
|
|
Packit |
87b942 |
sqlite>
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
.fi
|
|
Packit |
87b942 |
.SH OPTIONS
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
has the following options:
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-bail
|
|
Packit |
87b942 |
Stop after hitting an error.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-batch
|
|
Packit |
87b942 |
Force batch I/O.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-column
|
|
Packit |
87b942 |
Query results will be displayed in a table like form, using
|
|
Packit |
87b942 |
whitespace characters to separate the columns and align the
|
|
Packit |
87b942 |
output.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.BI \-cmd\ command
|
|
Packit |
87b942 |
run
|
|
Packit |
87b942 |
.I command
|
|
Packit |
87b942 |
before reading stdin
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-csv
|
|
Packit |
87b942 |
Set output mode to CSV (comma separated values).
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-echo
|
|
Packit |
87b942 |
Print commands before execution.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.BI \-init\ file
|
|
Packit |
87b942 |
Read and execute commands from
|
|
Packit |
87b942 |
.I file
|
|
Packit |
87b942 |
, which can contain a mix of SQL statements and meta-commands.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-[no]header
|
|
Packit |
87b942 |
Turn headers on or off.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-help
|
|
Packit |
87b942 |
Show help on options and exit.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-html
|
|
Packit |
87b942 |
Query results will be output as simple HTML tables.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-interactive
|
|
Packit |
87b942 |
Force interactive I/O.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-line
|
|
Packit |
87b942 |
Query results will be displayed with one value per line, rows
|
|
Packit |
87b942 |
separated by a blank line. Designed to be easily parsed by
|
|
Packit |
87b942 |
scripts or other programs
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-list
|
|
Packit |
87b942 |
Query results will be displayed with the separator (|, by default)
|
|
Packit |
87b942 |
character between each field value. The default.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.BI \-mmap\ N
|
|
Packit |
87b942 |
Set default mmap size to
|
|
Packit |
87b942 |
.I N
|
|
Packit |
87b942 |
\.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.BI \-nullvalue\ string
|
|
Packit |
87b942 |
Set string used to represent NULL values. Default is ''
|
|
Packit |
87b942 |
(empty string).
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.BI \-separator\ separator
|
|
Packit |
87b942 |
Set output field separator. Default is '|'.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-stats
|
|
Packit |
87b942 |
Print memory stats before each finalize.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.B \-version
|
|
Packit |
87b942 |
Show SQLite version.
|
|
Packit |
87b942 |
.TP
|
|
Packit |
87b942 |
.BI \-vfs\ name
|
|
Packit |
87b942 |
Use
|
|
Packit |
87b942 |
.I name
|
|
Packit |
87b942 |
as the default VFS.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
.SH INIT FILE
|
|
Packit |
87b942 |
.B sqlite3
|
|
Packit |
87b942 |
reads an initialization file to set the configuration of the
|
|
Packit |
87b942 |
interactive environment. Throughout initialization, any previously
|
|
Packit |
87b942 |
specified setting can be overridden. The sequence of initialization is
|
|
Packit |
87b942 |
as follows:
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
o The default configuration is established as follows:
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
.nf
|
|
Packit |
87b942 |
.cc |
|
|
Packit |
87b942 |
mode = LIST
|
|
Packit |
87b942 |
separator = "|"
|
|
Packit |
87b942 |
main prompt = "sqlite> "
|
|
Packit |
87b942 |
continue prompt = " ...> "
|
|
Packit |
87b942 |
|cc .
|
|
Packit |
87b942 |
.sp
|
|
Packit |
87b942 |
.fi
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
o If the file
|
|
Packit |
87b942 |
.B ~/.sqliterc
|
|
Packit |
87b942 |
exists, it is processed first.
|
|
Packit |
87b942 |
can be found in the user's home directory, it is
|
|
Packit |
87b942 |
read and processed. It should generally only contain meta-commands.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
o If the -init option is present, the specified file is processed.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
o All other command line options are processed.
|
|
Packit |
87b942 |
|
|
Packit |
87b942 |
.SH SEE ALSO
|
|
Packit |
87b942 |
http://www.sqlite.org/cli.html
|
|
Packit |
87b942 |
.br
|
|
Packit |
87b942 |
The sqlite3-doc package.
|
|
Packit |
87b942 |
.SH AUTHOR
|
|
Packit |
87b942 |
This manual page was originally written by Andreas Rottmann
|
|
Packit |
87b942 |
<rotty@debian.org>, for the Debian GNU/Linux system (but may be used
|
|
Packit |
87b942 |
by others). It was subsequently revised by Bill Bumgarner <bbum@mac.com> and
|
|
Packit |
87b942 |
further updated by Laszlo Boszormenyi <gcs@debian.hu> .
|