Brief SQLite Notes
SQLite is tiny, simple, rugged, and fast.
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
‘Single quotes’ are for strings (string literals).
“Double quotes” are for identifiers, if necessary (ex. table and column names).
SQLite Shell Commands
|.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:
- 1 is integer
- 1.2 is real
- ‘this’ is a string
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 ...):
- none (use for blob data)
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:
||for the entire table|
||if you never want this field empty, use
||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 );
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
strftimecall 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.
-- 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.
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.
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.
You can use
like for matching against a string:
select name from people where name like '%john%';
- lets you use
%as a wildcard (zero or more characters), like a
- an underscore
_to match any one character
- and matching is case-insensitive (though case-sensitive if matching a unicode character)
- is case-sensitive
- uses standard shell globbing characters
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;”
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 (
begin transaction; create table actors (id integer primary key autoincrement, name text); insert ... commit;
Various SQLite Built-In Functions
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;
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"
|| is sqlite’s string concatenation operator:
select 'hi' || 'bye'; hibye
For info on views, triggers, and transactions, see http://zetcode.com/databases/sqlitetutorial/viewstriggerstransactions/.