SQLite tutorial by examples

SQLite tutorial

In this tutorial, you’ll see several SQLite examples and tips that will help jump start your journey of becoming a SQLite database developer.

Before you begin

A GNU Linux/Mac OS/Windows machine with a SQLite command line client is good enough to follow this tutorial.

SQLite open file / existing database

$ sqlite3 <test.db>

SQLite create table

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name (field1 type, field2 type, ... , fieldN type);

Create table users:

$ sqlite3 test.db
sqlite> CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name TEXT, surname TEXT);

SQLite show tables

$ sqlite3 test.db
sqlite> .tables
users

In SQLite, it is also possible to list all tables from a database using a SQL query:

$ sqlite3 test.db
sqlite> SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%';
users

SQLite drop table

$ sqlite3 test.db
sqlite> DROP TABLE IF EXISTS users;
sqlite> .quit

SQLite INSERT

Syntax:

INSERT INTO table_name (field1, field2, ... ,fieldN) VALUES ('value1', 'value2', ..., 'valueN');
INSERT [OR REPLACE] INTO table_name (field1, field2, ... ,fieldN) VALUES ('value1', 'value2', ..., 'valueN');

Insert into table users:

$ sqlite3 test.db
sqlite> INSERT INTO users (id, name, surname) VALUES (1, 'Unknown', 'Doe');

Insert or replace into table users, useful when there are unique constrains:

$ sqlite3 test.db
sqlite> INSERT OR REPLACE INTO users (id, name, surname) VALUES (1, 'John', 'Doe');
sqlite> select * from users;
1|John|Doe|

SQLite select

Syntax:

SELECT field1, field2, ..., fieldN FROM table_name;

Select users:

$ sqlite3 test.db
sqlite> SELECT id, name, surname FROM users;
1|John|Doe

SQLite concat

There is no CONCAT function in SQLITE. We can achieve the same result by combining fields with ||.

$ sqlite3 test.db
sqlite> SELECT name || ' ' || surname as fullname FROM users WHERE id=1;
John Doe

SQLite datetime

Date and time in UTC:

$ sqlite3 test.db
sqlite> SELECT datetime('now');
2020-01-06 07:41:06

Date and time in local time:

$ sqlite3 test.db
sqlite> SELECT datetime('now','localtime');
2020-01-06 02:41:10

SQLite update

Syntax:

UPDATE table_name SET field1=value1, ..., fieldN=valueN [WHERE field2=value2 ... AND fieldK=fieldK];

Update users:

$ sqlite3 test.db
sqlite> UPDATE users SET name='Jane' WHERE id=1;

SQLite delete

Syntax:

DELETE FROM table_name [WHERE field1=value1 ... AND fieldN=valueN];

Delete users:

$ sqlite3 test.db
sqlite> DELETE FROM users WHERE id=1;

SQLite truncate

There is no TRUNCATE command in SQLITE. We can achieve the same performance result by deleting all the rows without a WHERE clause.*

$ sqlite3 test.db
sqlite> DELETE FROM users;

SQLite join

$ sqlite3 test.db
sqlite> CREATE TABLE IF NOT EXISTS hobbies (id INTEGER PRIMARY KEY AUTOINCREMENT, userid int, description TEXT);
sqlite> INSERT INTO hobbies (userid, description) VALUES (1, 'Hiking');
sqlite> INSERT INTO hobbies (userid, description) VALUES (1, 'Astronomy');
sqlite> INSERT INTO hobbies (userid, description) VALUES (99, 'Chess');
sqlite> INSERT INTO users (id, name, surname) VALUES (2, 'Baby', 'Doe');

Note: We are using SQLite AUTOINCREMENT to avoid the need to pass hobbies.id for each record.

Once we have two tables we can perform the different SQLite JOIN queries:

SQLite join or inner join

$ sqlite3 test.db
sqlite> SELECT u.name, h.description AS hobby FROM users u, hobbies h WHERE u.id = h.userid;
sqlite> SELECT u.name, h.description AS hobby FROM users u JOIN hobbies h ON u.id = h.userid;
sqlite> SELECT u.name, h.description AS hobby FROM users u INNER JOIN hobbies h ON u.id = h.userid;
Jane|Hiking
Jane|Astronomy

All three previous queries perform a inner join giving the same result.

SQLite left join

$ sqlite3 test.db
sqlite> SELECT u.name, h.description as hobby FROM users u LEFT JOIN hobbies h ON u.id = h.userid;
sqlite> SELECT u.name, h.description as hobby FROM users u LEFT OUTER JOIN hobbies h ON u.id = h.userid;
Jane|Astronomy
Jane|Hiking
Baby|

SQLite LEFT JOIN and LEFT OUTER JOIN are the same.

SQLite right join

RIGHT JOIN and RIGHT OUTER JOIN are not supported in SQLite, instead we can use LEFT JOIN changing the origin and destination table:

$ sqlite3 test.db
sqlite> SELECT u.name, h.description as hobby FROM hobbies h LEFT JOIN users u ON u.id = h.userid;
sqlite> SELECT u.name, h.description as hobby FROM hobbies h LEFT OUTER JOIN users u ON u.id = h.userid;
Jane|Hiking
Jane|Astronomy
|Chess

SQLite where

$ sqlite3 test.db
sqlite> SELECT * FROM users WHERE surname='Doe';
1|Jane|Doe|
2|Baby|Doe|
3|John|Doe|

SQLite in list

$ sqlite3 test.db
sqlite> SELECT id, name, surname FROM users WHERE id IN (1,3);
1|Jane|Doe
3|John|Doe
sqlite> SELECT id, name, surname FROM users WHERE id NOT IN (2);
1|Jane|Doe
3|John|Doe

SQLite like

$ sqlite3 test.db
sqlite> SELECT id, name, surname FROM users WHERE name LIKE 'J%';
1|Jane|Doe
3|John|Doe
sqlite> SELECT id, name, surname FROM users WHERE name NOT LIKE 'J%';
2|Baby|Doe

SQLite union

$ sqlite3 test.db
sqlite> CREATE TABLE IF NOT EXISTS superheroes (id INT PRIMARY KEY, name TEXT);
sqlite> INSERT INTO superheroes (id, name) VALUES (1, 'Batwoman');
sqlite> INSERT INTO superheroes (id, name) VALUES (2, 'Batman');
sqlite> INSERT INTO superheroes (id, name) VALUES (3, 'Batman');
sqlite> SELECT name || ' ' || surname FROM users UNION ALL SELECT name FROM superheroes;
Jane Doe
Baby Doe
Batwoman
Batman
Batman

sqlite> SELECT name || ' ' || surname FROM users UNION SELECT name FROM superheroes;
Baby Doe
Batman
Batwoman
Jane Doe

Note: SQLite UNION ALL includes duplicate records, UNION doesn’t.

SQLite describe table

$ sqlite3 test.db
sqlite> .schema users
CREATE TABLE users (id INT PRIMARY KEY, name TEXT, surname TEXT);

To better describe a SQLite table we can use the extension PRAGMA as well:

$ sqlite3 test.db
sqlite> .header on
sqlite> .mode column
sqlite> pragma table_info('users');
cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           id          INT         0                       1
1           name        TEXT        0                       0
2           surname     TEXT        0                       0

SQLite alter table

SQLite add column

$ sqlite3 test.db
sqlite> ALTER TABLE users ADD COLUMN phone VARCHAR(15);
sqlite> .schema users
CREATE TABLE users (id INT PRIMARY KEY, name TEXT, surname TEXT, phone VARCHAR(15));

SQLite rename table

$ sqlite3 test.db
sqlite> ALTER TABLE users RENAME TO users_foo;
sqlite> .tables 'users%'
users_foo
sqlite> ALTER TABLE users_foo RENAME TO users;
sqlite> .tables 'users%'
users

SQLite version

$ sqlite3 test.db
sqlite> SELECT sqlite_version();
3.26.0



Loading Comments