1.\"	$NetBSD: sqlite3.1,v 1.5 2012/12/21 19:26:39 njoly Exp $
2.Dd December 16, 2012
3.Dt SQLITE3 1
4.Os
5.Sh NAME
6.Nm sqlite3
7.Nd A command line interface for SQLite version 3
8.Sh SYNOPSIS
9.Nm
10.Op Ar options
11.Op Ar databasefile
12.Op Ar SQL
13.Sh DESCRIPTION
14.Nm
15is a terminal-based front-end to the SQLite library that can evaluate
16queries interactively and display the results in multiple formats.
17.Nm
18can also be used within shell scripts and other applications to provide
19batch processing features.
20.Pp
21To start a
22.Nm
23interactive session, invoke the
24.Nm
25command and optionally provide the name of a database file.
26If the database file does not exist, it will be created.
27If the database file does exist, it will be opened.
28.Pp
29For example, to create a new database file named "mydata.db", create
30a table named "memos" and insert a couple of records into that table:
31.Bd -literal -offset indent
32$ sqlite3 mydata.db
33SQLite version 3.1.3
34Enter ".help" for instructions
35sqlite> create table memos(text, priority INTEGER);
36sqlite> insert into memos values('deliver project description', 10);
37sqlite> insert into memos values('lunch with Christine', 100);
38sqlite> select * from memos;
39deliver project description|10
40lunch with Christine|100
41sqlite>
42.Ed
43.Pp
44If no database name is supplied, the
45.Em ATTACH
46sql command can be used
47to attach to existing or create new database files.
48.Em ATTACH
49can also be used to attach to multiple databases within the same
50interactive session.
51This is useful for migrating data between databases,
52possibly changing the schema along the way.
53.Pp
54Optionally, a SQL statement or set of SQL statements can be supplied as
55a single argument.
56Multiple statements should be separated by semi-colons.
57.Pp
58For example:
59.Bd -literal -offset indent
60$ sqlite3 -line mydata.db 'select * from memos where priority > 20;'
61    text = lunch with Christine
62    priority = 100
63.Ed
64.Ss SQLITE META-COMMANDS
65The interactive interpreter offers a set of meta-commands that can be
66used to control the output format, examine the currently attached
67database files, or perform administrative operations upon the
68attached databases (such as rebuilding indices).
69Meta-commands are always prefixed with a dot
70.Dq \&. .
71.Pp
72A list of available meta-commands can be viewed at any time by issuing
73the '.help' command.
74For example:
75.Bd -literal -offset indent
76sqlite> .help
77\&.databases             List names and files of attached databases
78\&.dump ?TABLE? ...      Dump the database in an SQL text format
79\&.echo ON|OFF           Turn command echo on or off
80\&.exit                  Exit this program
81\&.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
82\&.header(s) ON|OFF      Turn display of headers on or off
83\&.help                  Show this message
84\&.import FILE TABLE     Import data from FILE into TABLE
85\&.indices TABLE         Show names of all indices on TABLE
86\&.mode MODE ?TABLE?     Set output mode where MODE is one of:
87                         csv      Comma-separated values
88                         column   Left-aligned columns.  (See .width)
89                         html     HTML <table> code
90                         insert   SQL insert statements for TABLE
91                         line     One value per line
92                         list     Values delimited by .separator string
93                         tabs     Tab-separated values
94                         tcl      TCL list elements
95\&.nullvalue STRING      Print STRING in place of NULL values
96\&.output FILENAME       Send output to FILENAME
97\&.output stdout         Send output to the screen
98\&.prompt MAIN CONTINUE  Replace the standard prompts
99\&.quit                  Exit this program
100\&.read FILENAME         Execute SQL in FILENAME
101\&.schema ?TABLE?        Show the CREATE statements
102\&.separator STRING      Change separator used by output mode and .import
103\&.show                  Show the current values for various settings
104\&.tables ?PATTERN?      List names of tables matching a LIKE pattern
105\&.timeout MS            Try opening locked tables for MS milliseconds
106\&.width NUM NUM ...     Set column widths for "column" mode
107sqlite>
108.Ed
109.Sh OPTIONS
110.Nm
111has the following options:
112.Bl -tag -width abcdefghij
113.It Fl init Ar file
114Read and execute commands from
115.Ar file ,
116which can contain a mix of SQL statements and meta-commands.
117.It Fl echo
118Print commands before execution.
119.It Fl header
120Turn headers on.
121.It Fl noheader
122Turn headers off.
123.It Fl column
124Query results will be displayed in a table like form, using
125whitespace characters to separate the columns and align the
126output.
127.It Fl html
128Query results will be output as simple HTML tables.
129.It Fl line
130Query results will be displayed with one value per line, rows
131separated by a blank line.
132Designed to be easily parsed by scripts or other programs
133.It Fl list
134Query results will be displayed with the separator (|, by default)
135character between each field value.
136.It Fl separator Ar separator
137Set output field separator.
138Default is
139.Dq | .
140.It Fl nullvalue Ar string
141Set
142.Ar string
143used to represent
144.Dv NULL
145values.
146Default is
147.Dq \e
148(empty string).
149.It Fl version
150Show SQLite version.
151.It Fl help
152Show help on options and exit.
153.El
154.Ss INIT FILE
155.Nm
156reads an initialization file to set the configuration of the
157interactive environment.
158Throughout initialization, any previously specified setting can be overridden.
159The sequence of initialization is as follows:
160.Bl -enum
161.It
162The default configuration is established as follows:
163.Bd -literal -offset indent
164mode            = LIST
165separator       = "|"
166main prompt     = "sqlite> "
167continue prompt = "   ...> "
168.Ed
169.It
170If the file
171.Pa ~/.sqliterc
172exists, it is processed first.
173can be found in the user's home directory, it is
174read and processed.
175It should generally only contain meta-commands.
176.It
177If the
178.Fl init
179option is present, the specified file is processed.
180.It
181All other command line options are processed.
182.El
183.Sh SEE ALSO
184.Lk http://www.sqlite.org/
185.Sh AUTHORS
186This manual page was originally written by Andreas Rottmann
187.Aq rotty@debian.org ,
188for the Debian GNU/Linux system (but may be used by others).
189It was subsequently revised by Bill Bumgarner
190.Aq bbum@mac.com .
191