sqlite3

sqlite3 [options] [databasefile] ['SQLstatments']

Terminal-based front-end to the SQLite library that can evaluate queries interactively and display the results in multiple formats.
Uses dbname.db, .db-shm and .db-wal

Can be used within shell scripts and other applications to provide batch processing features.

`-init file`	execute `file`, containng SQL and meta-commands.
`-echo`	output commands before execution. (useful if commands are being executed from a file)
`-[no]header`
`-separator sep`	output field separator. Default `\|`.
`-nullvalue string`	string used to represent NULL values. Default '' (empty string).
`-version`
`-help`
Results display format:
`-list`	with the separator (`\|`?, by default) between each field value.
`-column`	in a table like form, using whitespace characters to separate the columns and align the output.
`-html`	simple HTML tables.
`-line`	one value per line, rows separated by a blank line. Easily parsed by other programs

sqlite3 db "vacuum;" can be used to "clean up" the database.

If the database file does not exist, it will be silently created (i.e. no message like "database does not exist is displayed").

Example: create a new database file named mydata.db, create a table named memos and insert a couple of records into it;


       $ sqlite3 mydata.db  -column -header
       SQLite version 3.7.13  with MAC OSX Mavericks 10.9
       Enter ".help" for instructions
       sqlite> create table memos(text, priority INTEGER);
       sqlite> insert into memos values('deliver project description', 10);
       sqlite> insert into memos values('lunch with Chris', 100);
       sqlite> select * from memos order by priority;
        text                         priority  
        ---------------------------  ----------
        deliver project description  10        
        lunch with Chris             100 
       sqlite>

ATTACH to existing or create new database files or
attach to multiple databases within the same interactive session.
Useful for migrating data between databases, possibly changing the schema.

SQL statements (separated by semi-colons) can be supplied as an argument. For example:

sqlite3 -line mydata.db 'select * from memos where priority>20; text=lunch with Chris priority=100'

META-COMMANDS

Control the output format, examine the currently attached database files, or perform administrative operations upon the attached databases (such as rebuilding indices). Meta-commands are prefixed with a dot (.) (unlike mysql where they are prefixed with \.


       sqlite> .help       rearranged by ed
          .databases              List names and files of attached databases
          .tables [?TABL?]       List names of tables
                                   If TABL specified, only list tables matching LIKE pattern TABL.

          .schema [?TABL?]       Show the CREATE statements
                                   If TABL specified, only show tables matching LIKE pattern TABL.

          .dump ?TABL? ...      Dump the database in an SQL text format
                                   If TABL specified, only dump tables matching LIKE pattern TABL.
          .read FILENAME         Execute SQL in FILENAME
          .echo ON|OFF           
          .output FILENAME       
          .output stdout         

          .explain ON|OFF      Turn output mode suitable for EXPLAIN on or off.default on
          .import FILE TABLE     Import data from FILE into TABLE
          .indices ?TABL?       Show names of all indices
                                   If TABL specified, only show indices for tables matching LIKE pattern TABL.
          .log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
          .mode MODE ?TABL?     Set output mode where MODE is one of:


 csv       Comma-separated values
label,weight,source,identifier,version,conditions
"google chrome",200.0,EQHXZ8M8AV,com.google.Chrome,,{errors=[-67013]}
"google chrome (canary)",300.0,EQHXZ8M8AV,com.google.Chrome.canary,,{errors=[-67013]}

column    Left-aligned columns.  (See .width)

label          weight      source      identifier         version     conditions       
-------------  ----------  ----------  -----------------  ----------  -----------------
google chrome  200.0       EQHXZ8M8AV  com.google.Chrome              {errors=[-67013]}
google chrome  300.0       EQHXZ8M8AV  com.google.Chrome              {errors=[-67013]}


label|weight|source|identifier|version|conditions
google chrome|200.0|EQHXZ8M8AV|com.google.Chrome||{errors=[-67013]}
google chrome (canary)|300.0|EQHXZ8M8AV|com.google.Chrome.canary||{errors=[-67013]}


html     HTML <table> code
 <TR><TH>label</TH> <TH>weight</TH> <TH>source</TH>
  <TH>identifier</TH> <TH>version</TH> <TH>conditions</TH> </TR>
<TR><TD>google chrome</TD> <TD>200.0</TD> <TD>EQHXZ8M8AV</TD>
   <TD>com.google.Chrome</TD> <TD></TD> <TD>{errors=[-67013]}</TD> </TR>
<TR><TD>google chrome (canary)</TD> <TD>300.0</TD> <TD>EQHXZ8M8AV</TD>
   <TD>com.google.Chrome.canary</TD> <TD></TD> <TD>{errors=[-67013]}</TD> </TR> 


label  weight  source
  identifier  version  conditions  
google chrome  200.0  EQHXZ8M8AV
   com.google.Chrome   {errors=[-67013]}  
google chrome (canary)  300.0  EQHXZ8M8AV
   com.google.Chrome.canary   {errors=[-67013]}   


                                   
insert   SQL insert statements for TABLE
INSERT INTO table(label,weight,source,identifier,version,conditions)
    VALUES('google chrome',200.0,'EQHXZ8M8AV','com.google.Chrome',NULL,'{errors=[-67013]}');
INSERT INTO table(label,weight,source,identifier,version,conditions) 
   VALUES('google chrome (canary)',300.0,'EQHXZ8M8AV','com.google.Chrome.canary',NULL,'{errors=[-67013]}');

                                   
line     One value per line
     label = google chrome
    weight = 200.0
    source = EQHXZ8M8AV
identifier = com.google.Chrome
   version = 
conditions = {errors=[-67013]} 
     label = google chrome (canary)
    weight = 300.0
    source = EQHXZ8M8AV
identifier = com.google.Chrome.canary
   version = 
conditions = {errors=[-67013]}
list     Values delimited by .separator string
label,weight,source,identifier,version,conditions
google chrome,200.0,EQHXZ8M8AV,com.google.Chrome,,{errors=[-67013]}
google chrome (canary),300.0,EQHXZ8M8AV,com.google.Chrome.canary,,{errors=[-67013]}

tabs     Tab-separated values


label  weight  source  identifier version  conditions
google chrome  200.0  EQHXZ8M8AV  com.google.Chrome  {errors=[-67013]}
google chrome (canary)  300.0  EQHXZ8M8AV  com.google.Chrome.canary  {errors=[-67013]}


  tcl      TCL list elements
"label" "weight" "source" "identifier" "version" "conditions"
"google chrome" "200.0" "EQHXZ8M8AV" "com.google.Chrome" "" "{errors=[-67013]}"
"google chrome (canary)" "300.0" "EQHXZ8M8AV" "com.google.Chrome.canary" "" "{errors=[-67013]}"





          .header(s) ON|OFF      Turn display of headers on or off
          .width NUM1 NUM2 ...   Set column widths for "column" mode

          .bail ON|OFF           Stop after hitting an error.  Default OFF
          .prompt MAIN CONTINUE  Replace the prompts

          .backup ?DB? FILE      default "main"
          .restore ?DB? FILE     default "main" 

          .nullvalue STRING      Print STRING in place of NULL values
          .separator STRING      Change separator used by output mode and .import
          .show                  current values for various settings
                                        echo: off
                                         eqp: off
                                  explain: off
                                     headers: off
                                        mode: list
                                   nullvalue: ""
                                      output: stdout
                                colseparator: "|"
                                rowseparator: "\n"
                                       stats: off
                                       width: 
                                
                
          .stats ON|OFF          Turn stats on or off
          .timeout MS            Try opening locked tables for MS milliseconds
          .trace FILE|off        Output each SQL statement as it is run
          .vfsname ?AUX?         Output the name of the VFS stack    unix
          .timer ON|OFF          Turn the CPU timer measurement on or off Run Time: real 0.001 user 0.000098 sys 0.000046

          .exit                  
          .quit                
          .help

INIT FILE

The sequence of initialization is :

The defaults:

          mode            = LIST
          separator       = "|"
          main prompt     = "sqlite> "
          continue prompt = "   ...> "

~/.sqliterc if it is readable, (It should generally only contain meta-commands.)
-init file if it is readable
command line options

sqlite3

META-COMMANDS

INIT FILE

See also

Example