Table of Contents

SQLite is an open source C library that implements a fast, simple SQL database engine. Unlike other database engines, SQLite reads and writes directly to ordinary disk files.

Installation and first use

Just need to install sqlite3 package, available on most operating systems. To create (or open) a database, run:

sqlite3 <database file>
  • Database files have the .db suffix (sqlite3 mydatabase.db).

Basic commands

SQLite commands are very similar to those of other SQL engines. Check https://www.sqlite.org/docs.html for more info. These SQL commands need to end with a semicolon ;.

Note: you can write multi-line commands by pressing Enter before typing the semicolon character. To cancel a command after pressing Enter, you can generate a deliberate syntax error (after closing any single or double-quote string). For example:

sqlite> select * from tavle1
...> ** error
...>;

Create table

create table mytable (id integer primary key, name text);
  • When adding primary key to a column, that field will auto increment with each record (and will set the column value to ‘1’ in the first record). This is better than using autoincrement.
create table mytable (fecha text, lectura integer);

Insert record

insert into mytable (name) values ("Ricardo");
  • If you are going to type all column values, you don’t need to type the column name.
    insert into mytable values (1, "Ricardo");

View records

select * from mytable;
  • To view only specific columns, type the column names (separated by commas) instead of *.
  • use where to filter:
select * from mytable where name="Ricardo";

Filtering by dates (using date and time functions or string comparisons):

# selecting data before year 2022
sqlite> select * from mytable where strftime('%Y', fecha) < strftime('%Y', '2022-01-01');
2021-06-04|177
2021-08-10|184
sqlite> select * from lectura where fecha < '2022-01-01';
2021-06-04|177
2021-08-10|184

Adding units to results:

sqlite> .headers on
sqlite> select date, temp || ' ºC' as 'temp_in_celsius' from table1;
date|temp_in_celsius
2024-01-01|30 ºC
sqlite> select date, concat(col1, ' m3') as 'measurement' from table1;

Comparison operators

=, ==, <, <=, >, >=, !=, IN, NOT IN, BETWEEN, IS, IS NOT, ISNULL, LIKE, NOT LIKE.

  • Note: with LIKE, col1 LIKE '%word%' will find ‘word’ and ‘WORD’ (case insensitive), but col1 like '%patrón%' will only find ‘patrón’, not ‘PATRÓN’ (case sensitive).

Math operators

+, -, *, /

  • Note: if you divide integer columns, you’ll get an integer value. To get a float number, first convert the integers to float (by multiplying them by 1.0): select col1 * 1.0 / days from table1.

Delete records

delete from mytable where id=1;

Delete table

drop table mytable;

Copy table

If you need to rearrange the columns or simply make a copy of an existing table (without previously exporting it to CSV):

Changing columns order:

create table table2 as select col1,col3,col2 from table1;

Without changing columns order:

create table copyTable as select * from table1;

Add column

alter table table1 add column new_column text;

Rename table

alter table table1 rename to table2;

Meta commands and parameters

Don’t add ; when using sqlite meta commands.

Exit from sqlite

.quit
# or
.exit
  • You can also press Ctrl+D.

Export to CSV

.mode csv
.headers on
.once data.csv
select * from mytable;

A simpler way, using sqlite command line parameters:

sqlite3 -csv -header mydatabase.db 'select * from mytable' > table.csv
  • You can use single or double quotes for the query, but be careful when you use single quotes inside the query:
sqlite3 -csv -header mydatabase.db "select * from mytable where col1 like '%someword%'" > table.csv

List tables

.tables

Table info (schema)

.schema mytable

Show every table (and view) schema

.schema

Change table visualization

  • Separate by columns:
.mode column
  • Like a table:
.mode box
  • Change column width:
.width <col1_width> <col2_width> ...

  • A value of ‘0’ means auto width, a negative value means right-justified.

  • Wrap text (to <number> characters):

.mode box --wrap <number>
  • Valid for box and column modes.
  • Add -ww to wrap the text word by word, not by letters.

Import from CSV

.import --csv --skip 1 /path/to/file.csv mytable
  • --skip 1 will skip first row (for existing tables). Don’t include this parameter to import to a non-existent table.

Inline SQL statements

sqlite3 mydatabase.db 'select * from mytable'

More SQL functions

  • sum(col1): sum of values from ‘col1’.
  • round(sum(col1), 2): round the previous sum to 2 decimals.
  • col1 + col2, col1 - col2: when doing this kind of arithmetic operations, to ensure a null value is treated as zero, you can use the iif function (or case):
(iif("col1" ISNULL, 0, "col1") - iif("col2" ISNULL, 0, "col2"))
(case when "col1" ISNULL then 0 else "col1" end - case when "col2" ISNULL then 0 else "col2" end)
  • format('<PATTERN>', column): format a column value. Similar to printf.
    • format('%.2f', col1): number with two decimals.
    • format('%6.2f', col1): number with two decimals, padded to the right if it is less than six characters long (including the decimal separator: ‘123.45’).
  • printf('%6.2f', col1)
  • substr(column, <start>, <end>): returns a substring of column, starting with the <start>-th character, with <end> characters long. <end> can be omitted.
select substr(col1, 0, 5) as my_generated_column from table1;

Examples

  • Calculating differences
sqlite> select * from table1;
date          value
------------  -------
2021-06-04    177
2021-08-10    184
2021-10-07    190
2021-12-06    197
2022-02-04    203
2022-04-05    210
sqlite> CREATE VIEW table1_diff as select table1.date, (table1.value - t_next.value) as diff from table1 inner join table1 as t_next on l_next.rowid = table1.rowid - 1;
sqlite> select * from table1_diff;
date            diff
------------  ----------
2021-08-10    7
2021-10-07    6
2021-12-06    7
2022-02-04    6
2022-04-05    7
  • Calculating an account balance
sqlite> select * from balance;
date       description      income  expense
----------  --------------  -------  -----
2024-01-01  Saldo anterior  150
2024-01-02  Ingreso1        200
2024-01-02  Gasto1                   30
2024-01-02  Gasto2                   22.5
sqlite> CREATE VIEW balance_total as select date, description, income, expense, round(iif(sum(income) over (order by rowid) ISNULL, 0, sum(income) over (order by rowid)) - iif(sum(expense) over (order by rowid) ISNULL, 0, sum(expense) over (order by rowid)), 2) as current_total from balance;
sqlite> select * from balance_total;
date       description     income  expense  current_total
----------  --------------  -------  -----  ------------
2024-01-01  Saldo anterior  150             150.0
2024-01-02  Ingreso1        200             350.0
2024-01-02  Gasto1                   30     320.0
2024-01-02  Gasto2                   22.5   297.5
  • Find the deviation from average
sqlite> select col1 - (select avg(col1) from table1) as 'deviation_from_average' from table1;
  • Search on several tables (with the same structure)
sqlite> select * from table1 where description like '%word%' union select * from table2 where description like '%otherword%' order by date;
  • Formating numbers
sqlite> select * from table1;
┌────────────┬────────────────┬─────────┬───────┐
│   fecha    │    concepto    │ ingreso │ gasto │
├────────────┼────────────────┼─────────┼───────┤
│ 2024-01-01 │ Saldo anterior │ 150     │       │
│ 2024-01-02 │ Ingreso1       │ 200     │       │
│ 2024-01-02 │ Gasto1         │         │ 30    │
│ 2024-01-02 │ Gasto2         │         │ 22.5  │
│ 2024-01-04 │ Gasto3         │         │ 16.8  │
│ 2024-01-11 │ Gasto4         │         │ 5.52  │
└────────────┴────────────────┴─────────┴───────┘
sqlite> select fecha,concepto,iif(ingreso ISNULL, NULL, format('%9.2f',ingreso)) as ingreso, iif(gasto ISNULL,NULL,format('%9.2f',gasto)) as gasto from table1;
┌────────────┬────────────────┬───────────┬───────────┐
│   fecha    │    concepto    │  ingreso  │   gasto   │
├────────────┼────────────────┼───────────┼───────────┤
│ 2024-01-01 │ Saldo anterior │    150.00 │           │
│ 2024-01-02 │ Ingreso1       │    200.00 │           │
│ 2024-01-02 │ Gasto1         │           │     30.00 │
│ 2024-01-02 │ Gasto2         │           │     22.50 │
│ 2024-01-04 │ Gasto3         │           │     16.80 │
│ 2024-01-11 │ Gasto4         │           │      5.52 │
└────────────┴────────────────┴───────────┴───────────┘
  • Calculate time between two dates on sucessive rows (as days) using unixepoch() (you can also use strftime('%s', column))
sqlite> select (unixepoch(table1.date) - unixepoch(t1_next.date))/60/60/24 as days from table1 inner join table1 as t1_next on t1_next.rowid = table1.rowid - 1;
  • Exclude results based on some criteria
select count(id) - (select count(id) from table1 where comments like '%word1%' or comments like '%word2%') as 'filteredResults' from table1;
  • Group by first 5 characters of some column value:
select substr(description, 0, 5) as grouped_desc, sum(expenses) as sum_expenses from 2024 group by grouped_desc;

If you have any suggestion, feel free to contact me via social media or email.