SQLite is tiny, simple, rugged, and fast.

Getting Started

Install (on Debian-based systems):

apt install sqlite3

To start a sqlite interactive session using a given db file (creating it if necessary):

$ sqlite3 foo.db
...
sqlite>

You can also run queries using the sqlite3 command:

$ sqlite3 foo.db "select * from my_stuff"

You may create a ~/.sqliterc config file if you like. Lines in that file should look just like how you’d run the commands in the sqlite shell. For example:

.mode column
.headers on

Quoting

‘Single quotes’ are for strings (string literals).

“Double quotes” are for identifiers, if necessary (ex. table and column names).

SQLite Shell Commands

Command Description
.tables shows all tables
.schema table describes the table
.output set where dump output is to go
.dump table dumps the table
.dump dumps the whole db
.show show all sqlite3 shell settings

Dump a table to a file:

sqlite> .output some-file.sql
sqlite> .dump <table>

Read sql from a table:

sqlite> .read other-file.sql

You can also do those using the sqlite command:

$ sqlite3 foo.db ".dump mytable" > a_table.sql
$ sqlite3 foo.db < bar.sql        # or
$ sqlite3 foo.db ".read bar.sql"

Where bar.sql might contain a bunch of sql for initializing a given db.

Data Types and Storage Classes

Every column has a type affinity associated with it (discussed below). However, every value has its own storage class associated with itself — not having anything to do with the table or column where it’s stored.

A datatype is a slightly more specific storage class. As users, we usually just deal with storage classes.

The different storage classes are:

A value’s storage class is inferred:

XXX What is x’0500’ ? Is it a literal for a blob?

XXX what is null?

XXX What is “blob” data anyway?

A column can have a type affinity (what you use when specifying the column types for a table in create table ...):

You can get pretty far just using text, integer, and real.

Note, you can always see the datatype of a value for yourself:

sqlite> select my_col, typeof(my_col) from my_table;

Ways to modify column data when creating a table:

Keyword Description
primary key autoincrement is implied by primary key
unique for the entire table
not null if you never want this field empty, use not null
check(...) check that the data conforms to some rule

Dates and Times

Typical usage: just use datetime('now') and store them as text:

create table t1 (
    id integer primary key,
    date_created text
);

Examples:

sqlite> select datetime('now');
2012-10-23 04:31:52

sqlite> select strftime('%s', 'now');
1350966637

sqlite> select julianday('now');
2456223.68935774

Note, you can pass that epoch time as either an int or a string. Both work.

BTW, although that strftime call returns a string, if you put that numerical string result into a column with affinitity numerical or integer, it will get coerced to an int for you.

Using SQL

-- A comment.
create table books (
    id     integer primary key,
    title  text not null,
    price  integer check (price > 0),
    author text default 'unknown!'
);

insert into books (title, author) values ('Great stuff!', 'R. Brown');
insert into books (title, author) values ('Plow time', 'Bob Plow');

If you want a field to never have a null value, use not null. Note, even on a “not null” field, you can still set it to “” (the empty string). “Not null” just keeps you from forgetting to put in a value at all.

If there’s no “default” (and no not null), then if omitted, you get NULL.

Foreign Keys

To use foreign keys, you need to enable them per database connection:

sqlite> pragma foreign_keys;
0
sqlite> pragma foreign_keys=ON;
sqlite> pragma foreign_keys;
1

Spaces around and the case of “ON” (or “off” for that matter) don’t matter.

Specify foreign keys in your tables like so:

create table brands (
    id integer primary key,
    name text,
    made_in text
);

create table shoes (
    id integer primary key,
    name text,
    color text,
    brand_id integer,
    foreign key (brand_id) references brands (id)
);

Note: the foreign key declarations must come at the end.

Examples

select * from a_table;
select * from a_table order by some_column;
select * from a_table order by some_column desc;
select * from a_table where id = 3;
select * from a_table where id in (2, 3, 4);
select * from a_table where some_col is null;
select * from a_table where name = 'Smith';
select * from a_table limit 10;
select * from a_table limit 10 offset 4;
update a_table set some_column = 4 where id = 7;
delete from a_table where id > 4 and id < 10;
delete from a_table;
drop table tablename;
alter table othertable rename to ot2;
alter table ot2 add column email text;
select * from cars where make in ('VW', 'Audi');
select * from cars where make like 'Vol%';
select * from cars where make glob 'Vol*';
select * from cars where make like '____';
select * from cars where price between 1000 and 2000;

delete from a_table;” deletes all rows from that table.
glob '...'” means to use regular shell globbing syntax.
like '____'” means when the name is 4 characters long.
glob is case-sensitive. like is not.

String Comparison

You can use =, glob, or like for matching against a string:

select name from people where name like '%john%';

like:

glob:

Joins

Inner Joins

Given:

sqlite> select * from customers;

id          name      
----------  ----------
1           Paul      
2           Terry     
3           Jack      
4           Tom

sqlite> select * from reservations;

id          customer_id  day       
----------  -----------  ----------
1           1            2009-22-11
2           2            2009-28-11
3           2            2009-29-11
4           1            2009-29-11
5           3            2009-02-12

sqlite> select c.name, r.day from customers as c
inner join reservations as r on c.id = r.customer_id;

name        day       
----------  ----------
Paul        2009-22-11
Terry       2009-28-11
Terry       2009-29-11
Paul        2009-29-11
Jack        2009-02-12

You may write simply “join” as shorthand for “inner join”.

We could also have written that query in older style as “select name, day from customers, reservations where customers.id = reservations.customer_id;”

Outer Joins

SQLite supports left outer joins (left join). Read more about them elsewhere (maybe at http://zetcode.com/databases/sqlitetutorial/joins/).

SQLite doesn’t support right outer joins (right join), or full outer joins (outer join).

Transactions

begin transaction;
create table actors (id integer primary key autoincrement, name text);
insert ...
commit;

Various SQLite Built-In Functions

select random();

Also: max(), min(), length() (for string length).

And count(*) for the number of rows in the table:

select count(*) from mytable;
select count(distinct some_col) from mytable;

Also:

select sum(some_col) from some_table;

select date('now');
select time('now');
select datetime('now');

Dates and datetimes are just strings:

create table foo (name text, date text);
insert into foo (name, date) values ("Abe", datetime('now'));
insert into foo (name, date) values ("Bea", datetime('now'));

Queries from the command line

sqlite3 foo.db "select * from mytable"

Random weirdness

|| is sqlite’s string concatenation operator:

select 'hi' || 'bye';
hibye

More

For info on views, triggers, and transactions, see http://zetcode.com/databases/sqlitetutorial/viewstriggerstransactions/.