SQLite/Release3

< SQLite

Before reading this you're supposed to have downloaded and unpacked in your PATH the latest version of the executable command line interface sqlite3 for your operating system.

CommandLine

Usage: sqlite3 [OPTIONS] FILENAME [SQL]

  • FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist.
  • SQL is a dot-, SQL-command or Pragma. OPTIONS include:
  -A ARGS...           = .archive ARGS and exit
  -append              append database to end of file
  -ascii               = .mode ascii
  -bail                = .bail on
  -batch               force batch I/O
  -box                 = .mode box
  -column              = .mode column
  -cmd COMMAND         run "COMMAND" before reading stdin
  -csv                 = .mode csv
  -deserialize         open using sqlite3_deserialize()
  -echo                = .echo on
  -init FILENAME       read/process named file
  -[no]header          = .header [off] or on
  -help                show this message
  -html                = .mode HTML
  -interactive         force interactive I/O
  -json                = .mode json
  -line                = .mode line

  -list                = .mode list
  -lookaside SIZE N    use N entries of SIZE bytes for lookaside memory
  -markdown            = .mode markdown
  -memtrace            trace all memory allocations and (de)allocations
  -mmap N              default mmap size set to N
  -newline SEP         = .separator "|" SEP. Default: "\n"
  -nofollow            = refuse to open symbolic links to database files
  -nullvalue TEXT      = .NULLvalue TEXT. Default ""
  -pagecache SIZE N    use N slots of SIZE bytes each for page cache memory
  -quote               = .mode quote
  -readonly            open the database read-only
  -separator SEP       = .separator SEP. Default: "|"
  -stats               print memory stats before each finalize
  -table               = .mode table
  -tabs                = .mode tabs
  -version             = .version
  -vfs NAME            use NAME as the default VFS
  -zip                 open the file as a ZIP Archive

DotCommands

SQLite3.37 .helpScreen (a-g)
  • .archive OPT... FILE... Manage SQL archives a
  • .auth (ON,OFF) Show authorizer callbacks
  • .backup ?DB? FILE Backup DB (default "main") to FILE
  • .bail (on,off) Stop after hitting an error. Default OFF
  • .binary (on,off) Turn binary output on or off. Default OFF
  • .cd DIRECTORY Change the working DIRECTORY
  • .changes (on,off) Show number of rows changed by SQL
  • .check GLOB Fail if output since .testcase does not match
  • .clone NEWDB Clone data into NEWDB from the existing database
  • .connection (close #) Open/close auxiliary database
  • .databases Show names and files of attached databases
  • .dbconfig ?op? ?val? Show/change sqlite3_db_config() options
  • .dbinfo ?DB? Show status information about the database
  • .dump ?TABLE? ... Dump the database in an SQL text formatt
  • .echo (on,off) Turn command echo on or off
  • .eqp (on,off,trigger,full) En-/disable automatic EXPLAIN QUERY PLAN
  • .excel Display the output of next command in a spreadsheet
  • .exit ?CODE? Exit (=.q) this program (returning CODE)
  • .expert EXPERIMENTAL. Indexes for spec. queries
  • .explain (on,off,auto) Explain format def. auto
  • .filectrl CMD ... Run sqlite3_file_control() operationsf
  • .fullschema ?--indent? Show schema and the content of sqlite_stat tables
      (h-q)
  • .headers (on,off) Turn display of headers on or off
  • .help ?REGEX? Show this message (or only REGEX)h
  • .import OPT... FILE TABLE Import FILE > TABLE i
  • .imposter INDEX TABLE Create imposter table TABLE on index INDEX
  • .indexes ?TABLE? Show names of all indexest
  • .limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
  • .lint OPTIONS Report potential schema issues.
  • .load FILE ?ENTRY? Load an extension library
  • .log (FILE,off) Turn logging on or off. FILE can be stderr/stdout
  • .mode MODE ?TABLE? Set output modem for tablet
  • .nullvalue STRING Use STRING in place of NULL values
  • .nonce STRING disable safe mode for one command
  • .once (-e,-x,FILE) Output for the next SQL command only to FILEe
  • .open ?OPTIONS? ?FILE? Close existing database and reopen FILEo
  • .output ?FILE? Send output to FILE or stdout
  • .parameter CMD ... Manage SQL parameter bindingsp
  • .print STRING... Print literal STRING
  • .progress N Invoke progress handler after every N opcodes
  • .prompt MAIN CONTINUE Replace the standard prompts
  • .quit Quit (=.ex) this program
------ screenshot from: ------
https://en.wikibooks.org/wiki/SQLite/Release3#DotCommands
      (r-z)
  • .read FILENAME Execute SQL in FILENAME
  • .recover Recover data from corrupt db
  • .restore ?DB? FILE DB content (dflt "main") from FILE
  • .save FILE Write in-memory database into FILE
  • .scanstats (on,off) sqlite3_stmt_scanstatus() metrics
  • .schema ?PATTERN? Show the CREATE statements matching PATTERNs1
  • .selftest ?--init? Run tests defined in the SELFTEST table
  • .separator COL ?ROW? Change separator for column and optionally rows2
  • .sha3sum ?OPTIONS...? Compute a SHA3 hash of database content
  • .shell CMD ARGS... Run CMD ARGS... in OS shell (=sy)
  • .show Show the current values for various settings
  • .stats (on,off) Show stats or turn stats on or off
  • .system CMD ARGS... Run CMD ARGS... in OS shell (=.sh)
  • .tables ?TABLE? List names of tablest1
  • .testcase NAME Begin redirecting output to 'testcase-out.txt'
  • .testctrl CMD ... Run sqlite3_test_control() opst2
  • .timeout MS Try opening locked tables for MS milliseconds
  • .timer (on,off) Turn SQL timer on or off
  • .trace (FILE,off) Output each SQL statement as it is run
  • .version Show detailed SQLite version info
  • .vfsinfo ?AUX? Information about the top-level VFS
  • .vfslist List all available VFSes
  • .vfsname' ?AUX? Print the name of the VFS stack
  • .width NUM1 ... Set col widths for "column" modew
D-notes

More info @sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_

a .ar [OPTION...] [FILE...] The .ar command manages sqlar archives.

Examples: 

 .ar -cf archive.sar foo bar    # Create archive.sar from files foo and bar
 .ar -tf archive.sar            # List members of archive.sar
 .ar -xvf archive.sar           # Verbosely extract files from archive.sar

Each command line must feature exactly one command option: 

 -c, --create               Create a new archive
 -u, --update               Update or add files to an existing archive
 -t, --list                 List contents of archive
 -x, --extract              Extract files from archive

And zero or more optional options: 

 -v, --verbose              Print each filename as it is processed
 -f FILE, --file FILE       Operate on archive FILE (default is current db)
 -a FILE, --append FILE     Operate on FILE opened using the apndvfs VFS
 -C DIR, --directory DIR    Change to directory DIR to read/extract files
 -n, --dryrun               Show the SQL that would have occurred

See also: sar-support 

e or invoke system text editor (-e) or spreadsheet (-x) on the output.
f CMD ... may be size_limit [LIMIT]; chunk_size SIZE;
persist_wal [BOOLEAN]; psow [BOOLEAN]; tempfilename
has_moved; lock_timeout MILLISEC
h only show commands matching REGEX e.g. p*=starting with p
i option to skip first line; --skip 1
l lintOptions: fkey-indexes Find missing foreign key indexes
m mode: ascii delimited by 0x1F and 0x1E; 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 delimited by "|"; quote Escape answers as for SQL
tabs Tab-separated values; tcl TCL list elements
o openOptions: --new option starts with an empty file
Other options: --readonly --append --zip
p paramCMDs:
clear       Erase all bindings
init          Initialize the TEMP table that holds bindings
list          List the current parameter bindings
set PARAMETER VALUE Given SQL parameter PARAMETER a value of VALUE
     PARAMETER should start with '$', ':', '@', or '?'
unset PARAMETER           Remove PARAMETER from the binding table
s1 schemaOption: --indent for pretty-printing
s2 separator for both the output mode and .import:
t1 If TABLE specified, only dump tables matching LIKE pattern TABLE.
t2 CMD ... may be always BOOLEAN; assert BOOLEAN; byteorder;
imposter SCHEMA ON/OFF ROOTPAGE; internal_functions BOOLEAN; localtime_fault BOOLEAN
never_corrupt BOOLEAN; optimizations DISABLE-MASK; pending_byte OFFSET; prng_reset;
prng_restore; prng_save; reserve BYTES-OF-RESERVE
w Negative values right-justify

KeyWords

If one wants to use one of following words as an identifier the word needs to be enclosed in two double quotes '"', backquotes '`' or '[' and ']'.[1]

      A-B
  • ABORT
  • ACTION
  • ADD
  • AFTER
  • ALL
  • ALTER
  • ALWAYS
  • ANALYZE
  • AND
  • AS
  • ASC
  • ATTACH
  • AUTOINCREMENT
  • BEFORE
  • BEGIN
  • BETWEEN
  • BY
      C-Def
  • CASCADE
  • CASE
  • CAST
  • CHECK
  • COLLATE
  • COLUMN
  • COMMIT
  • CONFLICT
  • CONSTRAINT
  • CREATE
  • CROSS
  • CURRENT
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • DATABASE
  • DEFAULT
  • DEFERRABLE
  • DEFERRED
      Del-Fi
  • DELETE
  • DESC
  • DETACH
  • DISTINCT
  • DO
  • DROP
  • EACH
  • ELSE
  • END
  • ESCAPE
  • EXCEPT
  • EXCLUDE
  • EXCLUSIVE
  • EXISTS
  • EXPLAIN
  • FAIL
  • FALSE[2]
  • FILTER
  • FIRST
      Fo-Inn
  • FOLLOWING
  • FOR
  • FOREIGN
  • FROM
  • FULL
  • GENERATED
  • GLOB
  • GROUP
  • GROUPS
  • HAVING
  • IF
  • IGNORE
  • IMMEDIATE
  • IN
  • INDEX
  • INDEXED
  • INITIALLY
  • INNER
      Ins-N
  • INSERT
  • INSTEAD
  • INTERSECT
  • INTO
  • IS
  • ISNULL
  • JOIN
  • KEY
  • LAST
  • LEFT
  • LIKE
  • LIMIT
  • MATCH
  • NATURAL
  • NO
  • NOT
  • NOTHING
  • NOTNULL
  • NULL
  • NULLS
      O-Ref
  • OF
  • OFFSET
  • ON
  • OR
  • ORDER
  • OTHERS
  • OUTER
  • OVER
  • PARTITION
  • PLAN
  • PRAGMA
  • PRECEDING
  • PRIMARY
  • QUERY
  • RAISE
  • RANGE
  • RECURSIVE
  • REFERENCES
      Reg-Ti
  • REGEXP
  • REINDEX
  • RELEASE
  • RENAME
  • REPLACE
  • RESTRICT
  • RIGHT
  • ROLLBACK
  • ROW
  • ROWS
  • SAVEPOINT
  • SELECT
  • SET
  • TABLE
  • TEMP
  • TEMPORARY
  • THEN
  • TIES
      To-Z
  • TO
  • TRANSACTION
  • TRIGGER
  • TRUE[2]
  • UNBOUNDED
  • UNION
  • UNIQUE
  • UPDATE
  • USING
  • VACUUM
  • VALUES
  • VIEW
  • VIRTUAL
  • WHEN
  • WHERE
  • WINDOW
  • WITH
  • WITHOUT
K-notes and/or references
  1. ↑ SQLite Keywords more info @sqlite.org/lang_keywords.html.
  2. ↑ a b Not a keyword, but a predefined variable. Usage as identifier might be confusing.

Pragmas

Pragmas are special commands to show or [change] behavior of open database[schema](s)

  • Usage: PRAGMA [schema-name.]name [ = TEXT or INT ];
active pragmas (61)
  • analysis_limit
  • application_id
  • auto_vacuum
  • automatic_index
  • busy_timeout
  • cache_size
  • cache_spill
  • case_sensitive_like
  • cell_size_check
  • checkpoint_fullfsync
  • collation_list
  • compile_options
  • data_version
  • database_list
  • defer_foreign_keys
  • encoding
  • foreign_key_check
  • foreign_key_list
  • foreign_keys
  • freelist_count
  • fullfsync
  • function_list
  • hard_heap_limit
  • ignore_check_constraints
  • incremental_vacuum
  • index_info
  • index_list
  • index_xinfo
  • integrity_check
  • journal_mode
  • journal_size_limit
  • legacy_alter_table
  • legacy_file_format
  • locking_mode
  • max_page_count
  • mmap_size
  • module_list
  • optimize
  • page_count
  • page_size
  • pragma_list
  • query_only
  • quick_check
  • read_uncommitted
  • recursive_triggers
  • reverse_unordered_selects
  • schema_version
  • secure_delete
  • shrink_memory
  • soft_heap_limit
  • stats
  • synchronous
  • table_info
  • table_xinfo
  • temp_store
  • threads
  • trusted_schema
  • user_version
  • wal_autocheckpoint
  • wal_checkpoint
  • writable_schema
deprecated pragmas (7)
  • count_changes
  • data_store_directory
  • default_cache_size
  • empty_result_callbacks
  • full_column_names
  • short_column_names
  • temp_store_directory
pragmas with non-standard compile-options (5)
  • parser_trace
  • vdbe_addoptrace
  • vdbe_debug
  • vdbe_listing
  • vdbe_trace
This article is issued from Wikibooks. The text is available under Creative Commons Attribution-Share Alike 4.0 unless otherwise noted. Additional terms may apply for the media files.