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