SQLite: small, self-contained SQL databases
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
- Basic commands
- Meta commands and parameters
- Schema (table info)
- Import from CSV
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);
- There is no
date
format in SQLite, but you can use date and time functions to format dates.
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.
Latest tutorials and articles:
Featured content: