Annotation of src/usr.bin/sqlite3/sqlite3.1, Revision 1.2
1.1 espie 1: .\" Hey, EMACS: -*- nroff -*-
2: .\" First parameter, NAME, should be all caps
3: .\" Second parameter, SECTION, should be 1-8, maybe w/ subsection
4: .\" other parameters are allowed: see man(7), man(1)
1.2 ! jturner 5: .TH SQLITE3 1 "Mon Jan 31 11:14:00 2014"
1.1 espie 6: .\" Please adjust this date whenever revising the manpage.
7: .\"
8: .\" Some roff macros, for reference:
9: .\" .nh disable hyphenation
10: .\" .hy enable hyphenation
11: .\" .ad l left justify
12: .\" .ad b justify to both left and right margins
13: .\" .nf disable filling
14: .\" .fi enable filling
15: .\" .br insert line break
16: .\" .sp <n> insert n+1 empty lines
17: .\" for manpage-specific macros, see man(7)
18: .SH NAME
19: .B sqlite3
20: \- A command line interface for SQLite version 3
21:
22: .SH SYNOPSIS
23: .B sqlite3
24: .RI [ options ]
25: .RI [ databasefile ]
26: .RI [ SQL ]
27:
28: .SH SUMMARY
29: .PP
30: .B sqlite3
31: is a terminal-based front-end to the SQLite library that can evaluate
32: queries interactively and display the results in multiple formats.
33: .B sqlite3
34: can also be used within shell scripts and other applications to provide
35: batch processing features.
36:
37: .SH DESCRIPTION
38: To start a
39: .B sqlite3
40: interactive session, invoke the
41: .B sqlite3
42: command and optionally provide the name of a database file. If the
43: database file does not exist, it will be created. If the database file
44: does exist, it will be opened.
45:
46: For example, to create a new database file named "mydata.db", create
47: a table named "memos" and insert a couple of records into that table:
48: .sp
49: $
50: .B sqlite3 mydata.db
51: .br
1.2 ! jturner 52: SQLite version 3.8.3
1.1 espie 53: .br
54: Enter ".help" for instructions
55: .br
56: sqlite>
57: .B create table memos(text, priority INTEGER);
58: .br
59: sqlite>
60: .B insert into memos values('deliver project description', 10);
61: .br
62: sqlite>
63: .B insert into memos values('lunch with Christine', 100);
64: .br
65: sqlite>
66: .B select * from memos;
67: .br
68: deliver project description|10
69: .br
70: lunch with Christine|100
71: .br
72: sqlite>
73: .sp
74:
75: If no database name is supplied, the ATTACH sql command can be used
76: to attach to existing or create new database files. ATTACH can also
77: be used to attach to multiple databases within the same interactive
78: session. This is useful for migrating data between databases,
79: possibly changing the schema along the way.
80:
81: Optionally, a SQL statement or set of SQL statements can be supplied as
82: a single argument. Multiple statements should be separated by
83: semi-colons.
84:
85: For example:
86: .sp
87: $
88: .B sqlite3 -line mydata.db 'select * from memos where priority > 20;'
89: .br
90: text = lunch with Christine
91: .br
92: priority = 100
93: .br
94: .sp
95:
96: .SS SQLITE META-COMMANDS
97: .PP
98: The interactive interpreter offers a set of meta-commands that can be
99: used to control the output format, examine the currently attached
100: database files, or perform administrative operations upon the
101: attached databases (such as rebuilding indices). Meta-commands are
102: always prefixed with a dot (.).
103:
104: A list of available meta-commands can be viewed at any time by issuing
105: the '.help' command. For example:
106: .sp
107: sqlite>
108: .B .help
109: .nf
110: .cc |
1.2 ! jturner 111: .backup ?DB? FILE Backup DB (default "main") to FILE
! 112: .bail ON|OFF Stop after hitting an error. Default OFF
1.1 espie 113: .databases List names and files of attached databases
114: .dump ?TABLE? ... Dump the database in an SQL text format
1.2 ! jturner 115: If TABLE specified, only dump tables matching
! 116: LIKE pattern TABLE.
1.1 espie 117: .echo ON|OFF Turn command echo on or off
118: .exit Exit this program
1.2 ! jturner 119: .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.
! 120: With no args, it turns EXPLAIN on.
1.1 espie 121: .header(s) ON|OFF Turn display of headers on or off
122: .help Show this message
123: .import FILE TABLE Import data from FILE into TABLE
1.2 ! jturner 124: .indices ?TABLE? Show names of all indices
! 125: If TABLE specified, only show indices for tables
! 126: matching LIKE pattern TABLE.
! 127: .load FILE ?ENTRY? Load an extension library
! 128: .log FILE|off Turn logging on or off. FILE can be stderr/stdout
1.1 espie 129: .mode MODE ?TABLE? Set output mode where MODE is one of:
130: csv Comma-separated values
131: column Left-aligned columns. (See .width)
132: html HTML <table> code
133: insert SQL insert statements for TABLE
134: line One value per line
135: list Values delimited by .separator string
136: tabs Tab-separated values
137: tcl TCL list elements
1.2 ! jturner 138: .nullvalue STRING Use STRING in place of NULL values
! 139: .open ?FILENAME? Close existing database and reopen FILENAME
1.1 espie 140: .output FILENAME Send output to FILENAME
141: .output stdout Send output to the screen
1.2 ! jturner 142: .print STRING... Print literal STRING
1.1 espie 143: .prompt MAIN CONTINUE Replace the standard prompts
144: .quit Exit this program
145: .read FILENAME Execute SQL in FILENAME
1.2 ! jturner 146: .restore ?DB? FILE Restore content of DB (default "main") from FILE
1.1 espie 147: .schema ?TABLE? Show the CREATE statements
1.2 ! jturner 148: If TABLE specified, only show tables matching
! 149: LIKE pattern TABLE.
1.1 espie 150: .separator STRING Change separator used by output mode and .import
151: .show Show the current values for various settings
1.2 ! jturner 152: .stats ON|OFF Turn stats on or off
! 153: .tables ?TABLE? List names of tables
! 154: If TABLE specified, only list tables matching
! 155: LIKE pattern TABLE.
1.1 espie 156: .timeout MS Try opening locked tables for MS milliseconds
1.2 ! jturner 157: .trace FILE|off Output each SQL statement as it is run
! 158: .vfsname ?AUX? Print the name of the VFS stack
! 159: .width NUM1 NUM2 ... Set column widths for "column" mode
! 160: .timer ON|OFF Turn the CPU timer measurement on or off
1.1 espie 161: sqlite>
162: |cc .
163: .sp
164: .fi
165: .SH OPTIONS
166: .B sqlite3
167: has the following options:
168: .TP
1.2 ! jturner 169: .B \-bail
! 170: Stop after hitting an error.
! 171: .TP
! 172: .B \-batch
! 173: Force batch I/O.
! 174: .TP
! 175: .B \-column
! 176: Query results will be displayed in a table like form, using
! 177: whitespace characters to separate the columns and align the
! 178: output.
! 179: .TP
! 180: .BI \-cmd\ command
! 181: run
! 182: .I command
! 183: before reading stdin
! 184: .TP
! 185: .B \-csv
! 186: Set output mode to CSV (comma separated values).
! 187: .TP
! 188: .B \-echo
! 189: Print commands before execution.
! 190: .TP
! 191: .BI \-init\ file
1.1 espie 192: Read and execute commands from
193: .I file
194: , which can contain a mix of SQL statements and meta-commands.
195: .TP
196: .B \-[no]header
197: Turn headers on or off.
198: .TP
1.2 ! jturner 199: .B \-help
! 200: Show help on options and exit.
1.1 espie 201: .TP
202: .B \-html
203: Query results will be output as simple HTML tables.
204: .TP
1.2 ! jturner 205: .B \-interactive
! 206: Force interactive I/O.
! 207: .TP
1.1 espie 208: .B \-line
209: Query results will be displayed with one value per line, rows
210: separated by a blank line. Designed to be easily parsed by
211: scripts or other programs
212: .TP
213: .B \-list
214: Query results will be displayed with the separator (|, by default)
215: character between each field value. The default.
216: .TP
1.2 ! jturner 217: .BI \-mmap\ N
! 218: Set default mmap size to
! 219: .I N
! 220: \.
1.1 espie 221: .TP
222: .BI \-nullvalue\ string
223: Set string used to represent NULL values. Default is ''
224: (empty string).
225: .TP
1.2 ! jturner 226: .BI \-separator\ separator
! 227: Set output field separator. Default is '|'.
! 228: .TP
! 229: .B \-stats
! 230: Print memory stats before each finalize.
! 231: .TP
1.1 espie 232: .B \-version
233: Show SQLite version.
234: .TP
1.2 ! jturner 235: .BI \-vfs\ name
! 236: Use
! 237: .I name
! 238: as the default VFS.
1.1 espie 239:
240:
241: .SH INIT FILE
242: .B sqlite3
243: reads an initialization file to set the configuration of the
244: interactive environment. Throughout initialization, any previously
245: specified setting can be overridden. The sequence of initialization is
246: as follows:
247:
248: o The default configuration is established as follows:
249:
250: .sp
251: .nf
252: .cc |
253: mode = LIST
254: separator = "|"
255: main prompt = "sqlite> "
256: continue prompt = " ...> "
257: |cc .
258: .sp
259: .fi
260:
261: o If the file
262: .B ~/.sqliterc
263: exists, it is processed first.
264: can be found in the user's home directory, it is
265: read and processed. It should generally only contain meta-commands.
266:
267: o If the -init option is present, the specified file is processed.
268:
269: o All other command line options are processed.
270:
271: .SH SEE ALSO
272: http://www.sqlite.org/
273: .br
1.2 ! jturner 274: The sqlite3-doc package.
1.1 espie 275: .SH AUTHOR
276: This manual page was originally written by Andreas Rottmann
277: <rotty@debian.org>, for the Debian GNU/Linux system (but may be used
1.2 ! jturner 278: by others). It was subsequently revised by Bill Bumgarner <bbum@mac.com> and
! 279: further updated by Laszlo Boszormenyi <gcs@debian.hu> .