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.

Table of Contents

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.

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).
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):

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

Delete records

delete from mytable where id=1;

Delete table

drop table mytable;

Meta commands and parameters

Don’t add ; when using sqlite meta commands.

Exit from sqlite

.quit

Export to CSV

.mode csv
.headers on
.output data.csv
select * from mytable;
.quit

Inline SQL statements (sqlite3 parameter)

sqlite3 -line mydatabase.db 'select * from mytable;'

Schema (table info)

.schema mytable

Import from CSV

.import --csv --skip 1 /path/to/file.csv mytable
  • skip 1 will skip first row.

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