sqlite cheat sheet

Create DB

sqlite3 test.db

.exit

Show available databases

.databases

sqlite> .databases

seq  name             file

---  ---------------  ----------------------------------------------------------

0    main             C:\Python27\Copy.db

1    temp

Show current database structure

.dbinfo

sqlite> .dbinfo

database page size:  1024

write format:        1

read format:         1

reserved bytes:      0

file change counter: 606

database page count: 9

freelist page count: 4

schema cookie:       93

schema format:       4

default cache size:  0

autovacuum top root: 0

incremental vacuum:  0

text encoding:       1 (utf8)

user version:        0

application id:      0

software version:    3008011

number of tables:    4

number of indexes:   0

number of triggers:  0

number of views:     1

schema size:         554

Show available tables

.tables

sqlite> .tables

Cars            prices          stock_overview  stocks

Show tables specfication. This is a strange one. I would expect to see schema related information but .schema showing tables specification instead.

.schema

sqlite> .schema

CREATE TABLE Cars(Id INT, Name TEXT, Price INT);

CREATE TABLE stocks

(

 id integer primary key autoincrement,

 stocksymbol text null ,

 index_col text null,

 lasttradewithcurrency text null,

 lasttradetime text null,

 lasttradeprice text null );

CREATE TABLE prices

(

id integer primary key autoincrement,

bought_price text not null);

CREATE VIEW stock_overview as select s.id, s.stocksymbol, s.index_col, s.lasttradewithcurrency, s.lasttradetime, s.lasttradeprice, p.bought_price from stocks s, prices p where s.id = p.id;

Show headers and format

.headers on

.mode column

     ... Or all in one line

sqllite3 -column -header test.db "select * from stock_overview"

Show current settings

.show

sqlite> .show

        echo: off

         eqp: off

     explain: auto

     headers: off

        mode: list

   nullvalue: ""

      output: stdout

colseparator: "|"

rowseparator: "\n"

       stats: off

       width:

Show current health

.stats

sqlite> .stats

Memory Used:                         624416 (max 635656) bytes

Number of Outstanding Allocations:   211 (max 249)

Number of Pcache Overflow Bytes:     5120 (max 5120) bytes

Number of Scratch Overflow Bytes:    0 (max 0) bytes

Largest Allocation:                  425600 bytes

Largest Pcache Allocation:           4096 bytes

Largest Scratch Allocation:          0 bytes

Lookaside Slots Used:                3 (max 151)

Successful lookaside attempts:       932

Lookaside failures due to size:      160

Lookaside failures due to OOM:       0

Pager Heap Usage:                    11616 bytes

Page cache hits:                     15

Page cache misses:                   1

Page cache writes:                   0

Schema Heap Usage:                   3152 bytes

Statement Heap/Lookaside Usage:      0 bytes

Compact the database with VACUUM 

sqlite> vacuum;

sqlite> vacuum test;

Quit out of session

.quit

.exit