About

a self-contained, serverless, zero-configuration, transactional SQL database engine.

Features

• Single Database File
• Stable Cross-Platform
• Variable-length records
  • If you store a single character in a VARCHAR(100) column, then only a single byte of disk space is consumed.

Quirks

• Flexible Typing
  • SQLite allows you to store a 2000-character string into a column of type VARCHAR(50)
• No Separate BOOLEAN Datatype
• No Separate DATETIME Datatype
• The datatype is optional
  • CREATE TABLE t1(a,b,c,d);

Quick Start

1
2
# create a new database
sqlite3 test.db

CLI

• .cd
• .tables
• .databases
• .headers
• .show
• .mode
  • column, box, markdown, table
• .output
• .once
• .read
• .import
• .shell/.system

https://www.sqlite.org/cli.html

Datetypes

Storage Classes

• NULL
• INTEGER - stored in 1, 2, 3, 4, 6, or 8 bytes
• REAL - stored as an 8-byte IEEE floating point number
• TEXT
• BLOB

Type Affinity

1. contains INT -> INTEGER
2. contains CHAR, CLOB, or TEXT -> TEXT
3. contains BLOB -> BLOB
4. contains REAL, FLOA, or DOUB -> REAL
5. Otherwise -> NUMERIC

SQL

https://www.sqlite.org/lang.html

Database

• ATTACH
• DETACH

Table

• CREATE
• DROP
• ALTER

Virtual Table

CRUD

• INSERT
• SELECT
• UPDATE
• DELETE

Function

https://www.sqlite.org/lang_corefunc.html

Aggregate

• max/min/sum/avg
• count
• group_concat

Index

• CREATE
• DROP

View

• CREATE
• DROP

Trigger

• CREATE
• DROP

TRANSACTION

• BEGIN
• END
• COMMIT
• ROLLBACK

EXPLAIN

PRAGMA

ON CONFLICT

• REPLACE

RETURNING

since version 3.35.0 (2021-03-12)

GENERATED

since version 3.31.0 (2020-01-22)

Tips

Autoincrement

A column with type INTEGER PRIMARY KEY is an alias for the ROWID.

If the ROWID is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use.

The purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.