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
.nullvalue '∅'

Quoting

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

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

SQLite Shell Commands

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 my-table" > 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, separate from that, every value stored in a table has its own storage class associated with the value itself.

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

The different storage classes are:

• text
• integer
• real
• null
• blob (for binary data)

A value’s storage class is inferred:

• 1 is integer
• 1.2 is real
• ‘this’ is a string

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

• text
• integer
• real
• numeric
• none (use for binary/blob data)

Most commonly used are 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:

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 book (
    id     integer primary key,
    title  text not null,
    price  integer check (price > 0),
    author text default 'unknown!'
);

insert into book (title, author) values ('Great Stuff!', 'R. Brown');
insert into book (title, author) values ('It''s Snowing', 'Bob Plow');

(Note the use of two consecutive single quotes in the string literal to get one single quote within that string value.)

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 brand (
    id integer primary key,
    name text,
    made_in text
);

create table shoe (
    id integer primary key,
    name text,
    color text,
    brand_id integer,
    foreign key (brand_id) references brand (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; -- deletes all rows in `a_table`
drop table tablename;
alter table othertable rename to ot2;
alter table ot2 add column email text;
select * from car where make in ('VW', 'Audi');
select * from car where make like 'Vol%';
select * from car where make glob 'Vol*';
select * from car where make like '____';
select * from car where price between 1000 and 2000;

“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:

• lets you use % as a wildcard (zero or more characters), like a * in shell
• an underscore _ to match any one character
• and matching is case-insensitive (though case-sensitive if matching a unicode character)

glob:

• is case-sensitive
• uses standard shell globbing characters

Joins

Inner Joins

Given:

sqlite> select * from customer;

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

sqlite> select * from reservation;

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 customer as c
inner join reservation 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 customer, reservation where customer.id = reservation.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 actor (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

See also the SQLite CLI docs, and the SQLite SQL lang docs.

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