Cheatsheet SQLite

sqlite3 minha.db
sqlite3 :memory:
sqlite3 minha.db ".tables"
sqlite3 minha.db ".schema"

sqlite3 ficheiro.db abre ou cria a base de dados. :memory: cria uma BD temporária em RAM. O ficheiro é criado automaticamente se não existir.

-- Exportar para CSV
.mode csv
.output saida.csv
SELECT * FROM utilizadores;
.output stdout

-- Exportar para JSON
.mode json
.output dados.json
SELECT * FROM utilizadores;
.output stdout

.output ficheiro redireciona o output para um ficheiro. .output stdout volta ao terminal. Combina com .mode csv ou .mode json para exportação.

-- Listar tabelas
.tables

-- Estrutura de uma tabela
.schema utilizadores

-- Info das colunas
PRAGMA table_info(utilizadores);

-- Tamanho do ficheiro
PRAGMA page_count;
PRAGMA page_size;

PRAGMA table_info() mostra colunas, tipos e constraints. page_count × page_size dá o tamanho total. .schema mostra o SQL de criação.

.tables          -- listar tabelas
.schema tabela   -- ver DDL da tabela
.headers on      -- mostrar nomes de colunas
.mode column     -- formato em colunas
.mode csv        -- formato CSV
.mode json       -- formato JSON
.quit            -- sair

Comandos dot são internos do CLI (não são SQL). .tables lista tabelas, .schema mostra o DDL. .headers on é essencial para legibilidade.

-- Backup binário (seguro com BD em uso)
sqlite3 minha.db ".backup backup.db"

-- Dump SQL completo
sqlite3 minha.db .dump > dump.sql

-- Restauro a partir de dump
sqlite3 nova.db < dump.sql

-- Restauro a partir de backup
sqlite3 minha.db ".restore backup.db"

.backup faz cópia segura mesmo com a BD em uso. .dump gera SQL re-executável. .restore repõe a partir de backup binário.

.mode table      -- tabela com bordas (3.33+)
.mode column     -- colunas alinhadas
.mode csv        -- separado por vírgulas
.mode json       -- array de objetos JSON
.mode markdown   -- formato Markdown
.mode html       -- tabela HTML
.mode box        -- caixa com bordas

.mode define o formato de output. table e box são os mais legíveis. csv e json são ideais para exportar dados para outras ferramentas.

-- Via shell
sqlite3 minha.db < script.sql

-- Dentro do CLI
.read script.sql
.read /caminho/absoluto/setup.sql

-- Comando único via flag
sqlite3 minha.db "SELECT COUNT(*) FROM users;"

.read ficheiro.sql executa um script dentro do CLI. Via shell, redireciona com <. A flag com string SQL executa um único comando e sai.

.mode csv
.import dados.csv utilizadores

-- Com cabeçalho (primeira linha = colunas):
.import --csv --skip 1 dados.csv utilizadores

-- Verificar:
SELECT COUNT(*) FROM utilizadores;

.import carrega dados de CSV para uma tabela. Com --skip 1 ignora a linha de cabeçalho. A tabela deve existir previamente com colunas compatíveis.

-- Colocar em ~/.sqliterc
.headers on
.mode table
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

O ficheiro .sqliterc é carregado automaticamente ao iniciar. Ativa headers, foreign_keys e WAL por padrão. Poupa repetição em cada sessão.

CREATE TABLE utilizadores (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nome TEXT NOT NULL,
  email TEXT UNIQUE,
  idade INTEGER DEFAULT 0,
  criado_em TEXT DEFAULT (datetime('now'))
);

INTEGER PRIMARY KEY AUTOINCREMENT cria ID auto-incremental. NOT NULL obriga valor. UNIQUE impede duplicados. DEFAULT define valor padrão.

DROP TABLE IF EXISTS logs;

DROP TABLE utilizadores;

-- Verificar se existe antes:
SELECT name FROM sqlite_master
WHERE type = 'table' AND name = 'logs';

DROP TABLE remove a tabela e todos os dados. IF EXISTS evita erro se não existir. sqlite_master é o catálogo interno do SQLite.

-- Todas as tabelas
SELECT name FROM sqlite_master
WHERE type = 'table' ORDER BY name;

-- DDL de um objeto
SELECT sql FROM sqlite_master
WHERE name = 'utilizadores';

-- Tipos: table, index, view, trigger
SELECT type, name FROM sqlite_master;

sqlite_master é o catálogo interno. Contém o DDL (sql) de todos os objetos. type pode ser table, index, view ou trigger.

CREATE TABLE IF NOT EXISTS logs (
  id INTEGER PRIMARY KEY,
  mensagem TEXT NOT NULL,
  nivel TEXT CHECK(nivel IN ('info', 'warn', 'erro')),
  criado_em TEXT DEFAULT (datetime('now'))
);

IF NOT EXISTS evita erro se a tabela já existir. Essencial em scripts de migração e setup. CHECK valida valores permitidos na coluna.

PRAGMA foreign_keys = ON;

CREATE TABLE encomendas (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total REAL,
  FOREIGN KEY (user_id)
    REFERENCES utilizadores(id)
    ON DELETE CASCADE
);

PRAGMA foreign_keys = ON é obrigatório — por padrão vêm desativadas. ON DELETE CASCADE apaga filhos automaticamente. Opções: CASCADE, SET NULL, RESTRICT.

-- Recomendado (rowid implícito):
CREATE TABLE notas (
  id INTEGER PRIMARY KEY,  -- auto-incrementa
  texto TEXT
);

-- Com AUTOINCREMENT (evita reuso de ids):
CREATE TABLE faturas (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  total REAL
);

-- Diferença:
--   INTEGER PRIMARY KEY pode reusar
--   o maior id após DELETE
--   AUTOINCREMENT nunca reusa
--   (mas é mais lento)

INTEGER PRIMARY KEY é um alias do rowid e auto-incrementa automaticamente. AUTOINCREMENT garante que ids apagados nunca são reusados, mas tem overhead — usa só se precisares mesmo (ex.: faturas).

ALTER TABLE utilizadores ADD COLUMN telefone TEXT;

ALTER TABLE utilizadores
  ADD COLUMN ativo INTEGER DEFAULT 1 NOT NULL;

-- SQLite não suporta DROP COLUMN antes da 3.35
-- SQLite 3.35+:
ALTER TABLE utilizadores DROP COLUMN telefone;

ALTER TABLE ADD COLUMN adiciona coluna no final. Novas colunas precisam de DEFAULT se forem NOT NULL. DROP COLUMN só desde a versão 3.35.

CREATE TABLE config (
  chave TEXT PRIMARY KEY,
  valor TEXT NOT NULL
) WITHOUT ROWID;

-- Mais eficiente quando a PK é TEXT
-- e a tabela é pequena

WITHOUT ROWID elimina o rowid interno e usa a PK como cluster. Mais rápido para tabelas pequenas com PK não-integer. Não suporta AUTOINCREMENT.

-- Validar valores na própria tabela:
CREATE TABLE produtos (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  preco REAL CHECK (preco >= 0),
  stock INTEGER DEFAULT 0
    CHECK (stock >= 0),
  estado TEXT CHECK (estado IN ('ativo', 'inativo'))
);

-- Violação → erro no INSERT/UPDATE:
INSERT INTO produtos (nome, preco)
VALUES ('X', -5);  -- falha!

-- CHECK é sempre validado
-- pelo SQLite

CHECK constraints validam valores diretamente na definição da coluna ou da tabela. Violações causam erro no INSERT/UPDATE. Útil para garantir invariantes (preços positivos, estados válidos). O SQLite valida-as sempre.

-- Renomear coluna (3.25+)
ALTER TABLE utilizadores
  RENAME COLUMN nome TO nome_completo;

-- Renomear tabela
ALTER TABLE utilizadores RENAME TO users;

RENAME COLUMN muda o nome de uma coluna (versão 3.25+). RENAME TO muda o nome da tabela. Índices e triggers são atualizados automaticamente.

CREATE TEMP TABLE resultados (
  id INTEGER PRIMARY KEY,
  valor REAL
);

-- Existe só na sessão atual
-- Apagada ao fechar a conexão
-- Não aparece em .tables de outra sessão

CREATE TEMP TABLE cria uma tabela visível apenas na conexão atual. Útil para cálculos intermédios. É apagada automaticamente ao fechar a base de dados.

INSERT INTO utilizadores (nome, email, idade)
VALUES ('Ana', 'ana@mail.com', 30);

-- Obter o último ID inserido:
SELECT last_insert_rowid();

INSERT INTO ... VALUES insere uma linha. last_insert_rowid() retorna o rowid gerado. Especifica sempre as colunas explicitamente para clareza.

DELETE FROM utilizadores WHERE id = 5;

-- Apagar todos os registos:
DELETE FROM logs;

-- Reset do AUTOINCREMENT:
DELETE FROM tabela;
DELETE FROM sqlite_sequence WHERE name = 'tabela';

DELETE FROM ... WHERE remove linhas. Sem WHERE, apaga tudo (mas mantém a tabela). sqlite_sequence guarda o contador do AUTOINCREMENT.

-- Retornar dados da linha inserida
INSERT INTO utilizadores (nome, email)
VALUES ('Ana', 'ana@mail.com')
RETURNING id, nome;

-- Retornar na atualização
UPDATE produtos SET preco = preco * 1.1
WHERE categoria = 'tech'
RETURNING id, nome, preco;

-- Retornar no delete
DELETE FROM logs WHERE criado < '2024-01-01'
RETURNING id;

RETURNING (versão 3.35+) retorna as linhas afetadas diretamente. Evita um SELECT extra após INSERT/UPDATE/DELETE. Suporta RETURNING *.

INSERT INTO utilizadores (nome, email, idade) VALUES
  ('João', 'j@mail.com', 25),
  ('Maria', 'm@mail.com', 30),
  ('Pedro', 'p@mail.com', 35);

-- Inserir a partir de SELECT:
INSERT INTO utilizadores_ativos
SELECT * FROM utilizadores WHERE ativo = 1;

Um único INSERT com múltiplos VALUES é mais rápido que vários inserts. INSERT INTO ... SELECT copia dados de outra tabela ou consulta.

-- SQLite 3.24+
INSERT INTO config (chave, valor)
VALUES ('tema', 'escuro')
ON CONFLICT(chave) DO UPDATE
SET valor = excluded.valor;

-- Ignorar se já existe:
INSERT OR IGNORE INTO users (id, nome)
VALUES (1, 'Ana');

ON CONFLICT DO UPDATE faz upsert — insere ou atualiza. excluded.valor referencia o valor que seria inserido. INSERT OR IGNORE simplesmente ignora conflitos.

-- Várias linhas num só INSERT:
INSERT INTO produtos (nome, preco)
VALUES
  ('Teclado', 45.00),
  ('Rato', 25.50),
  ('Monitor', 180.00);

-- Uma transação = muito mais rápido
-- que 3 INSERTs separados

-- INSERT a partir de SELECT:
INSERT INTO produtos_arquivo (nome, preco)
SELECT nome, preco FROM produtos
WHERE descontinuado = 1;

Um INSERT com várias linhas (VALUES (...), (...)) é muito mais rápido que INSERTs separados (uma transação só). INSERT ... SELECT copia dados de outra tabela. Ideal para popular a BD em bulk.

SELECT * FROM utilizadores;

SELECT nome, email FROM utilizadores
WHERE idade > 18
ORDER BY nome ASC
LIMIT 10 OFFSET 0;

SELECT DISTINCT cidade FROM utilizadores;

SELECT consulta dados. WHERE filtra, ORDER BY ordena, LIMIT/OFFSET pagina. DISTINCT remove duplicados. Evita * em produção.

SELECT u.nome, e.total
FROM utilizadores u
INNER JOIN encomendas e ON u.id = e.user_id;

SELECT u.nome, COUNT(e.id) AS total_enc
FROM utilizadores u
LEFT JOIN encomendas e ON u.id = e.user_id
GROUP BY u.id;

INNER JOIN retorna só linhas com correspondência. LEFT JOIN inclui todos da esquerda mesmo sem match. SQLite suporta também CROSS JOIN e RIGHT JOIN (3.39+).

-- Substitui se houver conflito de chave:
INSERT OR REPLACE INTO defs (chave, valor)
VALUES ('versao', '2.0');

-- Ignora silenciosamente o conflito:
INSERT OR IGNORE INTO defs (chave, valor)
VALUES ('versao', '1.0');

-- Outras variantes:
INSERT OR ABORT ...   -- padrão (erro)
INSERT OR ROLLBACK ...
INSERT OR FAIL ...

-- REPLACE apaga a linha antiga
-- e insere uma nova (id muda!)

INSERT OR REPLACE apaga a linha em conflito e insere uma nova (o id pode mudar). INSERT OR IGNORE descarta silenciosamente. Para upserts que mantêm o id, prefere ON CONFLICT DO UPDATE.

UPDATE utilizadores
SET idade = 31, email = 'novo@mail.com'
WHERE nome = 'Ana';

-- Atualizar todos (sem WHERE):
UPDATE produtos SET preco = preco * 1.1;

-- Com subquery:
UPDATE utilizadores SET ativo = 0
WHERE id IN (SELECT id FROM banidos);

UPDATE ... SET ... WHERE modifica linhas. Sem WHERE, atualiza todas as linhas. Usa subqueries para condições complexas. Retorna o número de linhas afetadas.

-- No WHERE
SELECT * FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos);

-- No FROM (tabela derivada)
SELECT categoria, media FROM (
  SELECT categoria, AVG(preco) AS media
  FROM produtos GROUP BY categoria
) WHERE media > 50;

Subqueries são consultas dentro de consultas. No WHERE comparam com resultados agregados. No FROM funcionam como tabelas temporárias. SQLite suporta subqueries correlacionadas.

INTEGER   -- inteiros (1, 2, 3, 4, 6 ou 8 bytes)
REAL      -- float 64-bit (IEEE 754)
TEXT      -- strings (UTF-8, UTF-16)
BLOB      -- dados binários (sem conversão)
NUMERIC   -- decimal, boolean, date

-- SQLite usa tipagem dinâmica:
-- a coluna aceita qualquer tipo

O SQLite tem 5 storage classes (afinidades). A tipagem é dinâmica — uma coluna INTEGER pode guardar texto. A afinidade apenas sugere conversão preferencial.

SELECT datetime('now');
SELECT date('now');
SELECT time('now');

-- Formatar
SELECT strftime('%d/%m/%Y %H:%M', 'now');

-- Aritmética de datas
SELECT datetime('now', '-7 days');
SELECT datetime('now', '+1 month', 'start of month');

SQLite não tem tipo DATE nativo — usa TEXT (ISO-8601), REAL (Julian) ou INTEGER (Unix). Funções: date(), time(), datetime(), strftime().

SELECT CAST('42' AS INTEGER);
SELECT CAST(3.14 AS INTEGER);   -- 3
SELECT CAST(100 AS REAL);       -- 100.0
SELECT CAST(1 AS TEXT);         -- '1'

-- typeof() mostra a storage class
SELECT typeof(42);       -- 'integer'
SELECT typeof(3.14);     -- 'real'
SELECT typeof('texto');  -- 'text'
SELECT typeof(NULL);     -- 'null'

CAST converte entre tipos explicitamente. typeof() retorna a storage class real do valor. Útil para debugging e validação de dados importados.

-- Alias para rowid (recomendado):
CREATE TABLE t (
  id INTEGER PRIMARY KEY
);

-- Com autoincremento explícito:
CREATE TABLE t (
  id INTEGER PRIMARY KEY AUTOINCREMENT
);

-- rowid está sempre presente (exceto WITHOUT ROWID)

INTEGER PRIMARY KEY é um alias do rowid interno. AUTOINCREMENT impede reutilização de IDs apagados (mais lento). Sem ele, o SQLite pode reusar o maior ID.

-- Dias entre duas datas
SELECT julianday('2025-12-25') - julianday('now');

-- Idade em anos
SELECT (julianday('now') - julianday('1990-05-15')) / 365.25;

-- Dias do mês atual
SELECT strftime('%d', 'now', 'start of month', '+1 month', '-1 day');

julianday() converte datas para número decimal de dias. A diferença dá dias. Divide por 365.25 para anos aproximados. strftime extrai componentes.

-- O SQLite não força tipos rígidos;
-- usa "affinity" (preferência):
CREATE TABLE teste (
  a INTEGER,   -- affinity INTEGER
  b TEXT,      -- affinity TEXT
  c NUMERIC    -- affinity NUMERIC
);

-- Podes guardar qualquer valor:
INSERT INTO teste VALUES ('123', 456, 'abc');

-- '123' é convertido para 123 (int)
-- 456 é guardado como texto '456'
-- 'abc' fica como texto

-- STRICT (3.37+) força os tipos:
CREATE TABLE rigida (x INTEGER) STRICT;

O SQLite usa type affinity: colunas têm uma preferência de tipo mas aceitam qualquer valor (convertendo quando possível). CREATE TABLE ... STRICT (3.37+) força tipos rígidos como nos outros SGBDs.

CREATE TABLE produtos (
  id INTEGER PRIMARY KEY,
  codigo TEXT NOT NULL UNIQUE,
  nome TEXT NOT NULL,
  preco REAL NOT NULL CHECK(preco >= 0),
  stock INTEGER DEFAULT 0
);

NOT NULL obriga valor. UNIQUE impede duplicados (aceita múltiplos NULL). CHECK valida condições. DEFAULT define valor quando omitido.

-- SQLite não tem BOOLEAN nativo
-- Usa INTEGER: 0 = false, 1 = true
SELECT * FROM users WHERE ativo = 1;

-- NULL handling
SELECT COALESCE(email, 'sem email');
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT IFNULL(telefone, 'N/A');

SQLite usa INTEGER 0/1 para booleanos. IS NULL / IS NOT NULL verifica nulos (não usar = NULL). COALESCE() retorna o primeiro não-nulo.

-- O SQLite não tem tipo DATE nativo.
-- Opções:

-- 1. TEXT em ISO-8601 (recomendado):
INSERT INTO eventos (data)
VALUES ('2024-06-15 14:30:00');

-- 2. INTEGER como Unix timestamp:
INSERT INTO eventos (data)
VALUES (strftime('%s', 'now'));

-- Funções de data funcionam com TEXT:
SELECT date('now');                  -- hoje
SELECT datetime('now', '+7 days');   -- +7 dias
SELECT strftime('%d/%m/%Y', data)
FROM eventos;

O SQLite não tem tipo DATE nativo — guarda datas como TEXT em formato ISO-8601 (recomendado, ordena corretamente) ou INTEGER (Unix timestamp). As funções date(), datetime() e strftime() operam sobre estes formatos.

-- Todos têm TEXT affinity no SQLite:
CREATE TABLE pessoas (
  nome TEXT,          -- recomendado
  email VARCHAR(255), -- limite NÃO é imposto
  pais CHAR(2)        -- aceita qualquer tamanho
);

-- O SQLite NÃO trunca nem rejeita
-- strings maiores que VARCHAR(255)

-- Convenção: usar sempre TEXT

No SQLite, TEXT, VARCHAR(n) e CHAR(n) têm todos TEXT affinity — o tamanho declarado não é imposto (não há truncagem nem erro). Convenção: use TEXT simples. O comprimento máximo só é validado se adicionares um CHECK.

CREATE TABLE ficheiros (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  dados BLOB,
  tipo TEXT
);

-- Inserir hex literal
INSERT INTO ficheiros (nome, dados)
VALUES ('icon', x'89504E47');

-- Tamanho
SELECT length(dados) FROM ficheiros;

BLOB armazena dados binários (imagens, PDFs). Literais hexadecimais usam prefixo x'hex'. length() retorna o tamanho em bytes. Para ficheiros grandes, prefere o filesystem.

SELECT COUNT(*) FROM users;
SELECT SUM(total) FROM encomendas;
SELECT AVG(idade) FROM users;
SELECT MIN(preco), MAX(preco) FROM produtos;
SELECT GROUP_CONCAT(nome, ', ') FROM users;
SELECT TOTAL(preco) FROM itens;  -- 0.0 se vazio

Agregados: COUNT, SUM, AVG, MIN, MAX. GROUP_CONCAT concatena valores. TOTAL() é como SUM() mas retorna 0.0 em vez de NULL.

SELECT nome, salario,
  RANK() OVER (ORDER BY salario DESC) AS ranking,
  SUM(salario) OVER (
    PARTITION BY departamento
  ) AS total_dept,
  LAG(nome) OVER (ORDER BY salario) AS anterior
FROM funcionarios;

Window functions (3.25+) calculam sobre um conjunto sem colapsar linhas. RANK(), ROW_NUMBER(), LAG(), LEAD(). PARTITION BY define grupos.

-- Primeiro valor não-nulo
SELECT COALESCE(telefone, telemovel, 'sem contacto')
FROM users;

-- NULLIF: retorna NULL se iguais
SELECT NULLIF(preco, 0);  -- NULL se preco = 0

-- Evitar divisão por zero:
SELECT total / NULLIF(qtd, 0) AS media
FROM vendas;

COALESCE() retorna o primeiro argumento não-NULL. NULLIF(a, b) retorna NULL se a = b. Útil para evitar divisão por zero e definir fallbacks.

SELECT categoria, COUNT(*) AS total,
       AVG(preco) AS preco_medio
FROM produtos
GROUP BY categoria
HAVING total > 5
ORDER BY total DESC;

GROUP BY agrupa linhas por coluna. HAVING filtra grupos (como WHERE mas para agregados). WHERE filtra antes do agrupamento, HAVING depois.

WITH vendas_2024 AS (
  SELECT user_id, SUM(total) AS total
  FROM encomendas
  WHERE strftime('%Y', data) = '2024'
  GROUP BY user_id
)
SELECT u.nome, v.total
FROM users u
JOIN vendas_2024 v ON u.id = v.user_id
ORDER BY v.total DESC;

WITH (CTE) cria consultas temporárias nomeadas. Melhora legibilidade de queries complexas. Pode encadear múltiplas CTEs separadas por vírgula. Não são materializadas.

SELECT upper('ana');        -- 'ANA'
SELECT lower('ANA');        -- 'ana'
SELECT length('olá');       -- 3
SELECT trim('  oi  ');      -- 'oi'
SELECT substr('SQLite', 1, 3);  -- 'SQL'
SELECT replace('a-b', '-', '+'); -- 'a+b'
SELECT instr('olá', 'l');   -- 2 (posição)

-- Concatenar:
SELECT 'Olá' || ' ' || 'Mundo';

-- LIKE é case-insensitive (ASCII):
SELECT * FROM users
WHERE nome LIKE 'ana%';

Funções de string: upper/lower/length/trim, substr, replace, instr (posição). Concatenação com ||. LIKE é case-insensitive para ASCII por padrão.

SELECT UPPER(nome), LOWER(email);
SELECT LENGTH(descricao);
SELECT TRIM('  texto  ');
SELECT REPLACE(nome, 'a', '@');
SELECT SUBSTR(nome, 1, 3);
SELECT INSTR(email, '@');
SELECT printf('Olá %s, tem %d anos', nome, idade)
FROM users;

UPPER/LOWER mudam capitalização. LENGTH conta caracteres. SUBSTR extrai parte. INSTR encontra posição. printf() formata strings com placeholders.

WITH RECURSIVE contagem(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM contagem WHERE n < 10
)
SELECT n FROM contagem;

-- Hierarquia (árvore):
WITH RECURSIVE arvore AS (
  SELECT id, nome, parent_id, 0 AS nivel
  FROM categorias WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.nome, c.parent_id, a.nivel + 1
  FROM categorias c JOIN arvore a ON c.parent_id = a.id
)
SELECT * FROM arvore;

WITH RECURSIVE permite CTEs que se referenciam a si próprias. Ideal para hierarquias, séries e grafos. Termina quando a parte recursiva retorna zero linhas.

-- Data/hora atual:
SELECT date('now');            -- 2024-06-15
SELECT time('now');            -- 14:30:00
SELECT datetime('now');        -- ambos
SELECT strftime('%s', 'now');  -- Unix epoch

-- Modificadores:
SELECT date('now', '+1 month');
SELECT date('now', 'start of month');
SELECT date('now', 'weekday 0');  -- domingo

-- Extrair partes:
SELECT strftime('%Y', data) AS ano,
       strftime('%m', data) AS mes
FROM eventos;

-- Diferença em dias:
SELECT julianday('2024-12-31')
     - julianday('now');

Funções de data: date(), time(), datetime() com modificadores (+1 month, start of month). strftime() extrai/formata partes. julianday() calcula diferenças em dias.

SELECT ABS(-5);          -- 5
SELECT ROUND(3.14159, 2); -- 3.14
SELECT RANDOM();          -- inteiro aleatório
SELECT MAX(1, 5, 3);     -- 5 (escalar)
SELECT MIN(1, 5, 3);     -- 1 (escalar)

-- SQLite 3.35+ (math functions):
SELECT log(100), sqrt(16), pow(2, 10);
SELECT ceil(3.2), floor(3.8), pi();

ABS, ROUND, RANDOM são nativas. MAX/MIN com múltiplos argumentos são escalares (não agregados). Funções log, sqrt, pow requerem compilação com math (3.35+).

SELECT nome,
  CASE
    WHEN idade < 18 THEN 'menor'
    WHEN idade BETWEEN 18 AND 65 THEN 'adulto'
    ELSE 'senior'
  END AS faixa
FROM users;

-- Atalho (SQLite 3.32+):
SELECT IIF(ativo = 1, 'Sim', 'Não') FROM users;

CASE WHEN é condicional multi-ramo. IIF() é um ternário simplificado (condição, verdadeiro, falso). Ambos funcionam em SELECT, WHERE e ORDER BY.

CREATE INDEX idx_users_email
  ON utilizadores(email);

CREATE UNIQUE INDEX idx_codigo
  ON produtos(codigo);

CREATE INDEX idx_comp
  ON encomendas(user_id, criado_em);

CREATE INDEX acelera pesquisas e ordenações. UNIQUE impede duplicados. Índices compostos seguem a ordem das colunas — a primeira é a mais importante para filtros.

EXPLAIN QUERY PLAN
SELECT * FROM utilizadores
WHERE email = 'ana@mail.com';

-- Resultado esperado:
-- SEARCH USING INDEX idx_users_email (email=?)

-- Mau resultado:
-- SCAN utilizadores  (full table scan!)

EXPLAIN QUERY PLAN mostra como o SQLite executa a query. SEARCH USING INDEX = bom. SCAN = full table scan (mau). Essencial para otimizar queries lentas.

-- Índice que cobre toda a query
CREATE INDEX idx_cobertura
  ON encomendas(user_id, total, data);

-- Esta query não precisa de aceder à tabela:
SELECT total, data FROM encomendas
WHERE user_id = 42;

-- EXPLAIN mostra: USING COVERING INDEX

Um covering index contém todas as colunas necessárias à query. O SQLite responde só com o índice sem ler a tabela. Mais rápido para queries frequentes com colunas específicas.

-- Só indexa utilizadores ativos
CREATE INDEX idx_ativos_email
  ON utilizadores(email)
  WHERE ativo = 1;

-- Mais pequeno e rápido que índice total
-- Só é usado se a query incluir WHERE ativo = 1

Índices parciais (WHERE) indexam só um subconjunto de linhas. Mais pequenos, mais rápidos de manter. O SQLite só os usa se a query tiver condição compatível.

CREATE VIEW utilizadores_ativos AS
SELECT id, nome, email
FROM utilizadores
WHERE ativo = 1;

SELECT * FROM utilizadores_ativos;

-- Apagar
DROP VIEW IF EXISTS utilizadores_ativos;

CREATE VIEW guarda uma consulta como tabela virtual. Não armazena dados — executa o SELECT a cada acesso. Simplifica queries complexas e controla acesso a colunas.

-- Índice em várias colunas:
CREATE INDEX idx_pedidos_cliente_data
ON pedidos (cliente_id, data);

-- A ORDEM das colunas importa:
-- o índice serve para:
--   WHERE cliente_id = ?
--   WHERE cliente_id = ? AND data > ?
--   ORDER BY cliente_id, data

-- MAS NÃO serve para:
--   WHERE data > ?  (sozinha)

-- Regra: igualdade primeiro,
-- range/ordenação depois

Índices compostos (várias colunas) seguem a regra do prefixo: servem para filtros na primeira coluna, ou primeira + segunda. A ordem importa — colunas de igualdade primeiro, range/ordenação depois. Um índice (a, b) não ajuda em WHERE b = ?.

-- Indexar valor calculado
CREATE INDEX idx_lower_email
  ON utilizadores(lower(email));

-- A query deve usar a mesma expressão:
SELECT * FROM utilizadores
WHERE lower(email) = 'ana@mail.com';

Índices de expressão indexam o resultado de uma função. A query deve usar exatamente a mesma expressão para o índice ser aproveitado. Útil para pesquisas case-insensitive.

CREATE TRIGGER atualizar_timestamp
AFTER UPDATE ON utilizadores
FOR EACH ROW
BEGIN
  UPDATE utilizadores
    SET atualizado = datetime('now')
    WHERE id = NEW.id;
END;

-- Apagar trigger
DROP TRIGGER IF EXISTS atualizar_timestamp;

CREATE TRIGGER executa SQL automaticamente em eventos. AFTER UPDATE, BEFORE INSERT, AFTER DELETE. NEW.coluna e OLD.coluna acedem aos valores.

-- Reconstruir índices (após muitas
-- alterações ou mudança de collation):
REINDEX;                     -- todos
REINDEX idx_clientes_email;  -- um só

-- Verificar integridade da BD:
PRAGMA integrity_check;

-- Analisar para o otimizador:
ANALYZE;

-- Remover índice desnecessário:
DROP INDEX IF EXISTS idx_antigo;

-- Índices custam em escrita —
-- remove os que não são usados

REINDEX reconstrói índices (útil após alterações de collation). ANALYZE ajuda o otimizador a escolher melhor. DROP INDEX remove índices não usados — cada índice tem custo em escrita, por isso mantém só os necessários.

-- Listar índices de uma tabela
PRAGMA index_list('utilizadores');

-- Detalhes de um índice
PRAGMA index_info('idx_users_email');

-- Apagar índice
DROP INDEX IF EXISTS idx_users_email;

-- Reconstruir todos os índices
REINDEX;

PRAGMA index_list() mostra índices da tabela. PRAGMA index_info() mostra colunas do índice. REINDEX reconstrói todos — útil após muitos deletes.

CREATE TRIGGER audit_update
AFTER UPDATE ON produtos
FOR EACH ROW
BEGIN
  INSERT INTO auditoria (tabela, id, campo, antigo, novo)
  VALUES ('produtos', NEW.id, 'preco', OLD.preco, NEW.preco);
END;

Triggers de auditoria registam alterações numa tabela de log. OLD.valor é o valor antes, NEW.valor depois. Útil para histórico de preços, alterações de estado.

BEGIN TRANSACTION;

UPDATE contas SET saldo = saldo - 100
  WHERE id = 1;
UPDATE contas SET saldo = saldo + 100
  WHERE id = 2;

COMMIT;
-- ou ROLLBACK; para anular tudo

BEGIN inicia, COMMIT confirma, ROLLBACK anula. Todas as operações entre BEGIN e COMMIT são atómicas — ou todas sucedem ou nenhuma é aplicada.

-- Compactar o ficheiro da BD
VACUUM;

-- VACUUM numa tabela específica (3.27+):
VACUUM main;

-- Auto-vacuum (configurar antes de criar tabelas):
PRAGMA auto_vacuum = FULL;
PRAGMA auto_vacuum = INCREMENTAL;

VACUUM reconstrói o ficheiro, recuperando espaço de dados apagados. auto_vacuum = FULL liberta páginas automaticamente. Requer exclusividade — não usar em produção ativa.

PRAGMA synchronous = FULL;    -- mais seguro (padrão)
PRAGMA synchronous = NORMAL;  -- bom compromisso
PRAGMA synchronous = OFF;     -- mais rápido (risco!)

-- Combinação recomendada:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

synchronous controla quantos fsync() o SQLite faz. FULL é o mais seguro. NORMAL com WAL é seguro e mais rápido. OFF pode corromper em falha de energia.

BEGIN DEFERRED;   -- padrão (lock só quando necessário)
BEGIN IMMEDIATE;  -- write lock imediato
BEGIN EXCLUSIVE;  -- lock total (ninguém lê nem escreve)

-- Recomendado para apps concorrentes:
BEGIN IMMEDIATE;

DEFERRED (padrão) só adquire lock ao primeiro write. IMMEDIATE previne deadlocks em concorrência. EXCLUSIVE bloqueia tudo — raramente necessário.

-- Verificar integridade da BD
PRAGMA integrity_check;
-- "ok" se tudo estiver bem

-- Verificar foreign keys órfãs
PRAGMA foreign_key_check;

-- Otimizar estatísticas de índices
PRAGMA optimize;

integrity_check valida a estrutura interna. foreign_key_check encontra referências quebradas. optimize atualiza estatísticas para o query planner. Executar periodicamente.

-- Transações aninhadas:
SAVEPOINT ponto1;

INSERT INTO logs (msg) VALUES ('a');

SAVEPOINT ponto2;
INSERT INTO logs (msg) VALUES ('b');

-- Desfazer só até ao ponto2:
ROLLBACK TO ponto2;

-- Confirmar tudo:
RELEASE ponto1;

-- Útil para "desfazer parcialmente"
-- sem abortar a transação toda

SAVEPOINT permite transações aninhadas. ROLLBACK TO nome desfaz até esse ponto sem abortar o resto; RELEASE nome confirma. Ideal para desfazer parcialmente um conjunto de operações.

PRAGMA journal_mode = WAL;

-- Vantagens:
-- Leituras não bloqueiam escritas
-- Escritas não bloqueiam leituras
-- Melhor performance em concorrência

PRAGMA wal_autocheckpoint = 1000;
PRAGMA synchronous = NORMAL;

WAL (Write-Ahead Logging) permite leituras concorrentes com escritas. Mais rápido que o modo DELETE (padrão) na maioria dos cenários. Persistente entre conexões.

-- Esperar até 5s se BD estiver bloqueada
PRAGMA busy_timeout = 5000;

-- Verificar estado de locking
PRAGMA lock_status;

-- Em código (exemplo Python):
-- conn.execute("PRAGMA busy_timeout = 5000")

busy_timeout define quanto tempo esperar quando a BD está bloqueada por outra conexão. Sem isto, retorna SQLITE_BUSY imediatamente. Essencial em apps multi-thread.

-- O SQLite bloqueia a BD durante
-- escritas. Se outra ligação tentar
-- escrever, recebe "database is locked".

-- Esperar até 5s em vez de falhar já:
PRAGMA busy_timeout = 5000;

-- Com WAL, leituras e escritas
-- podem acontecer em simultâneo:
PRAGMA journal_mode = WAL;

-- Boas práticas:
--   1 ligação para escrita
--   busy_timeout sempre ativo
--   transações curtas

O SQLite permite só um escritor de cada vez. busy_timeout faz as ligações esperarem (em vez de erro "database is locked"). Com WAL, leituras e escritas concorrem. Mantém transações curtas e usa busy_timeout sempre.

SAVEPOINT passo1;
INSERT INTO t VALUES (1, 'a');

SAVEPOINT passo2;
INSERT INTO t VALUES (2, 'b');

ROLLBACK TO passo2;  -- anula só o passo2
RELEASE passo1;      -- confirma o passo1

SAVEPOINT cria pontos de restauro parciais dentro de uma transação. ROLLBACK TO anula até ao savepoint. RELEASE confirma. Útil para operações multi-etapa.

PRAGMA journal_mode = DELETE;   -- padrão (apaga journal)
PRAGMA journal_mode = WAL;      -- write-ahead log
PRAGMA journal_mode = MEMORY;   -- journal em RAM
PRAGMA journal_mode = OFF;      -- sem journal (risco!)

-- Ver modo atual:
PRAGMA journal_mode;

O journal mode controla como o SQLite garante atomicidade. DELETE é o padrão. WAL é o recomendado. OFF é perigoso — corrupção em caso de crash.

-- Extrair valor
SELECT json_extract(dados, '$.nome') FROM configs;
SELECT dados->>'$.email' FROM users;  -- 3.38+

-- Criar JSON
SELECT json_object('id', id, 'nome', nome) FROM users;
SELECT json_group_array(nome) FROM users;

-- Validar
SELECT json_valid('{"a":1}');  -- 1

SQLite tem suporte JSON nativo (3.38+). json_extract() ou operador ->> extrai valores. json_object() cria JSON. json_group_array() agrega em array.

PRAGMA cache_size = -64000;   -- 64 MB de cache
PRAGMA temp_store = MEMORY;   -- temp em RAM
PRAGMA mmap_size = 268435456; -- 256 MB mmap
PRAGMA page_size = 4096;      -- tamanho de página

-- Verificar settings:
PRAGMA cache_size;
PRAGMA compile_options;

cache_size negativo = KB de memória. temp_store = MEMORY evita disco para tabelas temporárias. mmap_size usa memory-mapped I/O. Ajustar conforme RAM disponível.

INSERT INTO stats (dia, visitas, unicos)
VALUES ('2025-01-15', 100, 80)
ON CONFLICT(dia) DO UPDATE SET
  visitas = visitas + excluded.visitas,
  unicos = unicos + excluded.unicos;

-- DO NOTHING (ignorar conflito):
INSERT INTO users (email) VALUES ('a@b.com')
ON CONFLICT(email) DO NOTHING;

ON CONFLICT DO UPDATE acumula valores com excluded.coluna. Ideal para contadores e estatísticas diárias. DO NOTHING ignora silenciosamente conflitos.

-- Definir valor
SELECT json_set('{"a":1}', '$.b', 2);
-- {"a":1,"b":2}

-- Remover chave
SELECT json_remove('{"a":1,"b":2}', '$.b');

-- Tipo de um valor
SELECT json_type('{"a":[1,2]}', '$.a');  -- "array"

-- Cada elemento de array
SELECT value FROM json_each('[1,2,3]');

json_set() adiciona/altera chaves. json_remove() elimina. json_type() retorna o tipo. json_each() é uma table-valued function que itera arrays JSON.

-- Gerar série de números (3.38+)
SELECT value FROM generate_series(1, 10);

-- Com passo
SELECT value FROM generate_series(0, 100, 5);

-- Usar em JOIN
SELECT d.nome, s.value AS dia
FROM departamentos d
CROSS JOIN generate_series(1, 7) AS s;

generate_series() é uma table-valued function que gera sequências. Útil para relatórios por período, preenchimento de gaps e testes. Retorna coluna value.

-- Colunas calculadas automaticamente
-- (SQLite 3.31+):
CREATE TABLE itens (
  preco REAL,
  qtd INTEGER,
  total REAL GENERATED ALWAYS
    AS (preco * qtd) STORED
);

-- VIRTUAL (calculada na leitura):
--   AS (...) VIRTUAL
-- STORED (guardada em disco):
--   AS (...) STORED

INSERT INTO itens (preco, qtd) VALUES (10, 3);
SELECT total FROM itens;  -- 30

-- Não se pode escrever nelas

Generated columns (SQLite 3.31+) calculam o valor a partir de outras colunas. STORED guarda em disco (pode ser indexada); VIRTUAL calcula na leitura. Não podem ser escritas diretamente — apenas lidas.

CREATE VIRTUAL TABLE docs USING fts5(
  titulo, corpo
);

INSERT INTO docs VALUES
  ('Guia SQLite', 'Base de dados embebida...'),
  ('Tutorial SQL', 'Consultas e filtros...');

SELECT * FROM docs WHERE docs MATCH 'sqlite AND dados';
SELECT * FROM docs WHERE docs MATCH 'titulo:guia';

FTS5 é pesquisa de texto completo. MATCH faz a busca com operadores booleanos (AND, OR, NOT). Suporta pesquisa por coluna com coluna:termo.

ATTACH DATABASE 'outra.db' AS segunda;

-- Consultar entre bases de dados:
SELECT * FROM main.users
UNION ALL
SELECT * FROM segunda.users;

-- Copiar dados:
INSERT INTO main.arquivo
SELECT * FROM segunda.logs;

DETACH DATABASE segunda;

ATTACH liga outra BD à sessão atual. Referencia com nome.tabela. main é a BD principal. Permite JOINs e cópias entre BDs. Máximo 10 BDs em simultâneo.

-- CTE simples:
WITH totais AS (
  SELECT cliente_id, SUM(total) AS total
  FROM pedidos GROUP BY cliente_id
)
SELECT c.nome, t.total
FROM totais t JOIN clientes c ON c.id = t.cliente_id;

-- CTE RECURSIVA (hierarquias):
WITH RECURSIVE subs AS (
  SELECT id, nome, chefe_id, 1 AS nivel
  FROM empregados WHERE chefe_id IS NULL
  UNION ALL
  SELECT e.id, e.nome, e.chefe_id, s.nivel + 1
  FROM empregados e JOIN subs s ON e.chefe_id = s.id
)
SELECT * FROM subs;

CTE (WITH) cria tabelas temporárias nomeadas. WITH RECURSIVE permite recursão — ideal para hierarquias (organogramas, categorias pai/filho, árvores). Mais legível que subqueries aninhadas.

-- Ranking por relevância
SELECT titulo, rank FROM docs
WHERE docs MATCH 'sqlite'
ORDER BY rank;

-- Snippet com destaque
SELECT snippet(docs, 1, '<b>', '</b>', '...', 32)
FROM docs WHERE docs MATCH 'dados';

-- BM25 (ranking personalizado)
SELECT bm25(docs, 5.0, 1.0) FROM docs
WHERE docs MATCH 'sqlite';

rank ordena por relevância (BM25). snippet() extrai trecho com destaque. bm25() permite pesar colunas — primeiro argumento dá mais peso ao título.

-- Limites do SQLite:
-- Máx. colunas por tabela: 2000
-- Máx. tamanho BD: 281 TB
-- Máx. comprimento TEXT/BLOB: 1 GB
-- Máx. profundidade JOIN: 1000
-- Máx. variáveis por query: 32766

PRAGMA compile_options;
PRAGMA max_page_count;

O SQLite é generoso nos limites: BD até 281 TB, texto até 1 GB, 2000 colunas. PRAGMA compile_options mostra funcionalidades compiladas. Suficiente para 99% dos casos.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;
PRAGMA temp_store = MEMORY;

Configuração ideal para aplicações: WAL para concorrência, NORMAL para performance, foreign_keys ON para integridade, busy_timeout para evitar erros SQLITE_BUSY.

-- SQLite tem ALTER TABLE limitado
-- Para mudanças complexas:

-- 1. Criar nova tabela
CREATE TABLE users_new (...);

-- 2. Copiar dados
INSERT INTO users_new SELECT ... FROM users;

-- 3. Substituir
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

O ALTER TABLE do SQLite é limitado (só ADD/RENAME/DROP COLUMN). Para mudanças complexas (mudar tipo, constraints), usa o padrão de 4 passos: criar, copiar, apagar, renomear.

-- Ver SQL executado
PRAGMA vdbe_listing = ON;

-- Contar operações de I/O
PRAGMA cache_spill = OFF;

-- Tamanho real da BD
SELECT page_count * page_size AS bytes
FROM pragma_page_count(), pragma_page_size();

-- Espaço livre
PRAGMA freelist_count;

freelist_count mostra páginas livres (espaço recuperável com VACUUM). page_count × page_size dá o tamanho total. vdbe_listing mostra bytecode para debugging avançado.

-- LENTO: 1000 transações
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
-- ...

-- RÁPIDO: 1 transação
BEGIN;
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
-- ... (1000 inserts)
COMMIT;

Sem transação, cada INSERT faz fsync individual (muito lento). Envolver em BEGIN/COMMIT agrupa num único fsync. Diferença de 100x ou mais em bulk inserts.

-- 1. Ver plano de execução
EXPLAIN QUERY PLAN SELECT ...;

-- 2. Medir tempo
.timer on
SELECT ...;

-- 3. Verificar índices usados
PRAGMA index_list('tabela');

-- 4. Estatísticas
ANALYZE;
PRAGMA optimize;

EXPLAIN QUERY PLAN mostra se usa índice ou scan. .timer on mede tempo de execução. ANALYZE recolhe estatísticas para o query planner. PRAGMA optimize atualiza-as.

-- VACUUM: reescreve a BD, liberta
-- espaço de linhas apagadas:
VACUUM;

-- (o ficheiro .db encolhe)
-- Correr fora de transação,
-- requer espaço livre em disco

-- ANALYZE: recolhe estatísticas
-- para o otimizador de queries:
ANALYZE;

-- Auto-vacuum (alternativa contínua):
PRAGMA auto_vacuum = FULL;
-- (tem de ser definido antes
--  de criar tabelas)

VACUUM reescreve o ficheiro e recupera espaço de dados apagados (o .db encolhe). ANALYZE recolhe estatísticas para o otimizador escolher melhores índices. auto_vacuum = FULL faz limpeza contínua (a definir antes de criar tabelas).

-- Em Python:
cursor.execute(
  "SELECT * FROM users WHERE idade > ?", (18,)
)

-- Em PHP (PDO):
$stmt = $pdo->prepare(
  "SELECT * FROM users WHERE cidade = ?"
);
$stmt->execute(['Lisboa']);

Prepared statements com placeholders ? previnem SQL injection e são mais rápidos em execuções repetidas. Nunca concatenar strings SQL com input do utilizador.

-- SQLite nativo NÃO tem encriptação
-- Opções:
-- • SQLCipher (fork com AES-256)
-- • SEE (extensão oficial paga)
-- • wxSQLite3 (extensão open-source)

-- Boas práticas:
-- • Permissões do ficheiro: chmod 600 bd.db
-- • Não guardar secrets em texto plano
-- • Usar WAL (ficheiro -wal também tem dados!)

O SQLite não encripta nativamente. SQLCipher é a solução mais popular (AES-256). Cuidado: em modo WAL, os ficheiros -wal e -shm também contêm dados.

-- Backup seguro com a BD em uso:
sqlite3 minha.db ".backup backup.db"

-- NÃO copies o ficheiro diretamente
-- com a BD aberta (pode corromper)

-- Dump em SQL (portável):
sqlite3 minha.db .dump > schema.sql

-- Restaurar do dump:
sqlite3 nova.db < schema.sql

-- .backup = binário, seguro online
-- .dump = texto, para migrações

Nunca copies o ficheiro .db com a base aberta (pode corromper). Usa .backup (cópia binária segura online) ou .dump (SQL em texto, portável entre versões). Restaura o dump com sqlite3 nova.db < ficheiro.sql.

-- ✅ Bom para:
-- Apps mobile / desktop
-- Protótipos e testes
-- BDs até ~1 TB com 1 writer
-- Sistemas embedded / IoT
-- Cache local / configurações

-- ❌ Mau para:
-- Múltiplos writers concorrentes
-- Acesso via rede (NFS/SMB)
-- Alta disponibilidade / failover

SQLite é ideal para apps locais, protótipos e embedded. Um único ficheiro, zero configuração. Não é adequado para múltiplos writers em rede ou sistemas que precisam de failover automático.

-- 1. Usar WAL
PRAGMA journal_mode = WAL;

-- 2. Timeout generoso
PRAGMA busy_timeout = 10000;

-- 3. Transações curtas
BEGIN IMMEDIATE;
-- operações rápidas
COMMIT;

-- 4. Uma conexão para escrita
-- Múltiplas para leitura (WAL permite)

SQLite permite um writer de cada vez. WAL permite leituras concorrentes com escritas. Mantém transações curtas. Usa BEGIN IMMEDIATE para evitar deadlocks. Uma conexão de escrita, várias de leitura.