SQLite3
Command Reference

These start with a dot and are not SQL — they control the SQLite shell itself.

Commands for creating and modifying database structure.

copy-- Create a table
CREATE TABLE patients (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    name    TEXT NOT NULL,
    age     INTEGER,
    created DATE DEFAULT (date('now'))
);

-- Modify a table (add column)
ALTER TABLE patients ADD COLUMN phone TEXT;

-- Delete a table
DROP TABLE patients;

Commands for inserting, updating, and deleting data.

copy-- Insert data
INSERT INTO patients (name, age) VALUES ('João Silva', 35);

-- Update data
UPDATE patients SET age = 36 WHERE id = 1;

-- Delete a specific row
DELETE FROM patients WHERE id = 1;

-- Delete all rows
DELETE FROM patients;

Commands for retrieving and filtering data.

copy-- Select all
SELECT * FROM patients;

-- Select specific columns
SELECT name, age FROM patients;

-- Filter with condition
SELECT * FROM patients WHERE age > 30;

-- Sort results
SELECT * FROM patients ORDER BY name ASC;

-- Limit results
SELECT * FROM patients LIMIT 10;

-- Count rows
SELECT COUNT(*) FROM patients;

-- Search with pattern
SELECT * FROM patients WHERE name LIKE 'João%';

Commands for combining data from multiple related tables.

copy-- Inner join (only matching rows)
SELECT patients.name, appointments.date
FROM patients
INNER JOIN appointments ON patients.id = appointments.patient_id;

-- Left join (all patients, with or without appointments)
SELECT patients.name, appointments.date
FROM patients
LEFT JOIN appointments ON patients.id = appointments.patient_id;

Built-in functions for dates, strings, and aggregation.

copy-- Date and time
SELECT date('now');          -- current date
SELECT datetime('now');      -- current date and time

-- String functions
SELECT upper(name) FROM patients;
SELECT length(name) FROM patients;

-- Aggregate functions
SELECT AVG(age) FROM patients;
SELECT MAX(age) FROM patients;
SELECT MIN(age) FROM patients;
SELECT SUM(age) FROM patients;

Commands for exporting and backing up your database.

copy-- Export entire database to SQL file
.output backup.sql
.dump
.output stdout

-- Export table to CSV
.headers on
.mode csv
.output patients.csv
SELECT * FROM patients;
.output stdout