Last updated about 3 years ago

Sqlite 3

Cli shell commands

• Help

  .help

• Some useful commands:

  • Open a new connection .open
  • List of databases attached .databases
  • List of tables present .tables
  • Read an sql file .read <file>
  • Describe a table .schema <table> pragma table_info("<table>")
  • Display options .header on .mode column

• Create table (don’t forget semicolon at end)

  create table [if not exists] <name> ( ... );

• Storage Classes (NOT Data Types!)

  • Null
  • Integer
  • Real
  • Text
  • Blob

• Foreign key (inside create table)

  foreign key ("column_id") references "foreign_table"("foreign_id")

• Insert Query

  insert into <table> (<col1>, <col2>, ...) values (<v1>, <v2>, ...), (<vk>, <vl>, ...);

• Update Query

  update <table> set <key> = <val>, ... [from select ...] [where <key> = <val>];

• Delete Query

  delete from <table> [where <col> = <val>];

• Select Query

  select [<col1>, <col2> | *]
    from <table1>, [<table2> ...]
    [where <cond1> [<cond2>]]
    [order by <colx> [desc | asc] ...]
    [group by <coly>]

  • Distinct Query

    select distinct <col1>, <col2> from <table>;

  • Limit Query

    select ... LIMIT <number>;

  • Offset Query

    select ... OFFSET <number>;

    • Offset and Limit may be used together (with order by) for paginated results

  • Like Query

    select * from <table> where LIKE <pattern>;

    • <pattern> is like regex but not really. Here’s a comparison:
      • x% = ^x+ = starts with “x”
      • %x = .*x$ = ends with “x”
      • %xyz% = .*xyz.* = contains “xyz”
      • _x% = ^.x.* = has x in second position, _ is like . - means any
      • x%y = ^x.*y$ = starts with “x” and ends with “y”

  • Sum | Min | Max | Average | Count - Aggregate functions

  select [SUM | MIN | MAX | AVG | COUNT](<col>) from <table> [group by <table>]

  • HAVING: Like “WHERE” filter for aggregate functions

  select <col> from <table> [group by <col>] HAVING <[sum | min | max | avg | count ] bool expr>;

• Backup Sqlite3 (non-interactively)

  $ sqlite3 <name>.db ".backup '<name>.backup.db'"

  • Alternatively, compress it with xz

    $ xz --compress --threads=0 --quiet <name>.backup.db