Sqlite

🗂️ SQLite Cheat Sheet

[!info] SQLite is a self-contained, file-based relational database engine. It doesn’t require a server to run and is often used in embedded applications, prototyping, or lightweight desktop/mobile apps.


⚙️ Overview

SQLite stores data in a single cross-platform disk file. It's zero-configuration, serverless, and supports SQL transactions. While it loosely follows PostgreSQL syntax, SQLite is more permissive—particularly around types.


🚀 Features

  • Serverless architecture

  • Lightweight (~500KB binary)

  • Atomic commit and rollback

  • Cross-platform compatible database files

  • No installation or configuration required

  • Embeddable in applications (C/C++/Python/etc.)


🧰 Getting Started

🏁 Launch SQLite

sqlite3 my_database.db

[!tip] This command creates the my_database.db file if it doesn’t exist and opens the SQLite prompt.


📚 Common Commands

CommandDescription
.helpList all SQLite dot-commands
.databasesShow attached databases
.tablesList all tables in the current db
.schemaShow CREATE statements for tables
.headers onEnable column headers in output
.mode columnFormat output in aligned columns
.quitExit the SQLite shell
.backupBackup current database to file

[!example] Run .backup backup_file.db in the shell to save a copy.


🛠️ Basic SQL Commands

Create Table

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);

Insert Data

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

Select Data

SELECT * FROM users;

Update Data

UPDATE users SET name = 'Bob' WHERE id = 1;

Delete Data

DELETE FROM users WHERE id = 1;

⚠️ Notes on Type System

[!warning] SQLite uses dynamic typing. You can insert a string into an INTEGER column without error.



Explore More 🌍


Tags 📚

#sqlite #database #sql