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 guide.

Table of content

1. SQLite databases

1.1. SQLite open file / existing database

$ sqlite3 <test.db>

2. SQLite tables

2.1. SQLite create table

Syntax:

# Create a brand new SQLite table
CREATE TABLE [IF NOT EXISTS] table_name (field1 type, field2 type, ... , fieldN type);

# Or create a table from existing SQLite tables
CREATE TABLE [IF NOT EXISTS] table_name AS SELECT field1, field2, ..., fieldN FROM table_names [WHERE conditions];

Create a new SQLite table users if not exists:

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

2.2. 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

2.3. SQLite drop table

Syntax:

DROP TABLE [IF EXISTS] table_name;

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

3. 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 (if record does not exist) or replace (if record exists) 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 id, name, surname FROM users;
1|John|Doe|

4. SQLite select

Syntax:

SELECT field1, field2, ..., fieldN FROM table_name [WHERE conditions] [LIMIT N[,K]];

Select users:

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

5. SQLite dates

5.1. 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

6. SQLite string functions

6.1. SQLite concat

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

Syntax:

SELECT str1 || str2 [ || strN ] AS concatenated_sqlite_fields FROM table_name;

Example concatenating names and surnames:

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

7. SQLite aggregate functions

7.1. SQLite count

$ sqlite3 test.db
sqlite> SELECT COUNT(1) AS num_users FROM users;
1

8. SQLite update

Syntax:

UPDATE table_name SET field1=value1, ..., fieldN=valueN [WHERE conditions];

Update users:

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

9. SQLite delete

Syntax:

DELETE FROM table_name [WHERE conditions];

Delete users:

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

10. SQLite truncate

There is no TRUNCATE table 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;

11. 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: INNER and LEFT JOIN.

11.1. 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.

11.2. 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.

11.3. 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

12. SQLite where

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

12.1. 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

12.2. 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

13. SQLite union

Syntax:

SELECT field(s) FROM table_name1 UNION [ALL] SELECT field(s) FROM table_name2 [UNION [ALL] SELECT field(s) FROM table_nameN];

$ 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
John Doe
Baby Doe
Batwoman
Batman
Batman

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

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

14. SQLite views

14.1. SQLite create view

Syntax:

DROP VIEW IF EXISTS view_name;
CREATE VIEW view_name AS SELECT field1, field2, ..., fieldN FROM table_names [WHERE conditions]

SQLite does not support the CREATE OR REPLACE syntax to create a view. To achieve the same purpose we should drop the view before its creation. The following example creates a new view called people as a union from users and superheroes tables:

$ sqlite3 test.db
sqlite> DROP VIEW IF EXISTS people;
sqlite> CREATE VIEW people AS SELECT name || ' ' || surname FROM users UNION SELECT name FROM superheroes;
sqlite> SELECT name FROM people;
Baby Doe
Batman
Batwoman
Jane Doe
John Doe

14.2. SQLite drop view

Syntax:

DROP VIEW [IF EXISTS] view_name;

Drops people view if exists:

$ sqlite3 test.db
sqlite> DROP VIEW IF EXISTS people;

15. SQLite describe

15.1. 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

Finally, we can describe a SQLite table using a SQL query as well:

$ sqlite3 test.db
sqlite> SELECT sql
   ...> FROM sqlite_master
   ...> WHERE name = 'users';
CREATE TABLE "users" (id INT PRIMARY KEY, name TEXT, surname TEXT, phone VARCHAR(15), email VARCHAR)

16. SQLite alter

16.1. SQLite alter table 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));

16.2. SQLite alter table 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

17. SQLite version

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

Recommended books to expand your SQLite knowledge:



Loading Comments

DevOps books:









Cloud providers:



DigitalOcean offers affordable pricing for VMs and many other public cloud services. You can sign up for DigitalOcean and receive a $100 free credit using this referral link.