Cheatsheet SQLite

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

sqlite3 archivo.db abre o crea la base de datos. :memory: crea una BD temporal en RAM. El archivo se crea automáticamente si no existe.

-- Exportar a CSV
.mode csv
.output salida.csv
SELECT * FROM users;
.output stdout

-- Exportar a JSON
.mode json
.output datos.json
SELECT * FROM users;
.output stdout

.output archivo redirige la salida a un archivo. .output stdout vuelve al terminal. Combina con .mode csv o .mode json para exportación.

-- Listar tablas
.tables

-- Estructura de una tabla
.schema users

-- Info de las columnas
PRAGMA table_info(users);

-- Tamaño del archivo
PRAGMA page_count;
PRAGMA page_size;

PRAGMA table_info() muestra columnas, tipos y constraints. page_count × page_size da el tamaño total. .schema muestra el SQL de creación.

.tables          -- listar tablas
.schema tabla    -- ver DDL de la tabla
.headers on      -- mostrar nombres de columnas
.mode column     -- formato en columnas
.mode csv        -- formato CSV
.mode json       -- formato JSON
.quit            -- salir

Los comandos dot son internos del CLI (no son SQL). .tables lista tablas, .schema muestra el DDL. .headers on es esencial para la legibilidad.

-- Backup binario (seguro con la BD en uso)
sqlite3 mi.db ".backup backup.db"

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

-- Restauración a partir de dump
sqlite3 nueva.db < dump.sql

-- Restauración a partir de backup
sqlite3 mi.db ".restore backup.db"

.backup hace una copia segura incluso con la BD en uso. .dump genera SQL re-ejecutable. .restore restaura a partir de un backup binario.

.mode table      -- tabla con bordes (3.33+)
.mode column     -- columnas alineadas
.mode csv        -- separado por comas
.mode json       -- array de objetos JSON
.mode markdown   -- formato Markdown
.mode html       -- tabla HTML
.mode box        -- caja con bordes

.mode define el formato de salida. table y box son los más legibles. csv y json son ideales para exportar datos a otras herramientas.

-- Vía shell
sqlite3 mi.db < script.sql

-- Dentro del CLI
.read script.sql
.read /ruta/absoluta/setup.sql

-- Comando único vía flag
sqlite3 mi.db "SELECT COUNT(*) FROM users;"

.read archivo.sql ejecuta un script dentro del CLI. Vía shell, redirige con <. La flag con string SQL ejecuta un único comando y sale.

.mode csv
.import datos.csv users

-- Con cabecera (primera línea = columnas):
.import --csv --skip 1 datos.csv users

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

.import carga datos de un CSV a una tabla. Con --skip 1 ignora la línea de cabecera. La tabla debe existir previamente con columnas compatibles.

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

El archivo .sqliterc se carga automáticamente al iniciar. Activa headers, foreign_keys y WAL por defecto. Ahorra repetición en cada sesión.

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  age INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now'))
);

INTEGER PRIMARY KEY AUTOINCREMENT crea un ID autoincremental. NOT NULL obliga a un valor. UNIQUE impide duplicados. DEFAULT define el valor por defecto.

DROP TABLE IF EXISTS logs;

DROP TABLE users;

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

DROP TABLE elimina la tabla y todos los datos. IF EXISTS evita un error si no existe. sqlite_master es el catálogo interno de SQLite.

-- Todas las tablas
SELECT name FROM sqlite_master
WHERE type = 'table' ORDER BY name;

-- DDL de un objeto
SELECT sql FROM sqlite_master
WHERE name = 'users';

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

sqlite_master es el catálogo interno. Contiene el DDL (sql) de todos los objetos. type puede ser table, index, view o trigger.

CREATE TABLE IF NOT EXISTS logs (
  id INTEGER PRIMARY KEY,
  message TEXT NOT NULL,
  level TEXT CHECK(level IN ('info', 'warn', 'error')),
  created_at TEXT DEFAULT (datetime('now'))
);

IF NOT EXISTS evita un error si la tabla ya existe. Esencial en scripts de migración y setup. CHECK valida los valores permitidos en la columna.

PRAGMA foreign_keys = ON;

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

PRAGMA foreign_keys = ON es obligatorio — por defecto vienen desactivadas. ON DELETE CASCADE borra los hijos automáticamente. Opciones: CASCADE, SET NULL, RESTRICT.

-- Recomendado (rowid implícito):
CREATE TABLE notes (
  id INTEGER PRIMARY KEY,  -- autoincrementa
  text TEXT
);

-- Con AUTOINCREMENT (evita reutilizar ids):
CREATE TABLE invoices (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  total REAL
);

-- Diferencia:
--   INTEGER PRIMARY KEY puede reutilizar
--   el mayor id tras DELETE
--   AUTOINCREMENT nunca reutiliza
--   (pero es más lento)

INTEGER PRIMARY KEY es un alias del rowid y autoincrementa automáticamente. AUTOINCREMENT garantiza que los ids borrados nunca se reutilizan, pero tiene overhead — úsalo solo si de verdad lo necesitas (ej.: facturas).

ALTER TABLE users ADD COLUMN phone TEXT;

ALTER TABLE users
  ADD COLUMN active INTEGER DEFAULT 1 NOT NULL;

-- SQLite no soporta DROP COLUMN antes de la 3.35
-- SQLite 3.35+:
ALTER TABLE users DROP COLUMN phone;

ALTER TABLE ADD COLUMN añade una columna al final. Las columnas nuevas necesitan DEFAULT si son NOT NULL. DROP COLUMN solo desde la versión 3.35.

CREATE TABLE config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
) WITHOUT ROWID;

-- Más eficiente cuando la PK es TEXT
-- y la tabla es pequeña

WITHOUT ROWID elimina el rowid interno y usa la PK como cluster. Más rápido para tablas pequeñas con PK no-integer. No soporta AUTOINCREMENT.

-- Validar valores en la propia tabla:
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL CHECK (price >= 0),
  stock INTEGER DEFAULT 0
    CHECK (stock >= 0),
  status TEXT CHECK (status IN ('active', 'inactive'))
);

-- Violación → error en INSERT/UPDATE:
INSERT INTO products (name, price)
VALUES ('X', -5);  -- ¡falla!

-- CHECK siempre es validado
-- por SQLite

Las constraints CHECK validan valores directamente en la definición de la columna o de la tabla. Las violaciones causan error en INSERT/UPDATE. Útil para garantizar invariantes (precios positivos, estados válidos). SQLite las valida siempre.

-- Renombrar columna (3.25+)
ALTER TABLE users
  RENAME COLUMN name TO full_name;

-- Renombrar tabla
ALTER TABLE users RENAME TO customers;

RENAME COLUMN cambia el nombre de una columna (versión 3.25+). RENAME TO cambia el nombre de la tabla. Los índices y triggers se actualizan automáticamente.

CREATE TEMP TABLE results (
  id INTEGER PRIMARY KEY,
  value REAL
);

-- Existe solo en la sesión actual
-- Se borra al cerrar la conexión
-- No aparece en .tables de otra sesión

CREATE TEMP TABLE crea una tabla visible solo en la conexión actual. Útil para cálculos intermedios. Se borra automáticamente al cerrar la base de datos.

INSERT INTO users (name, email, age)
VALUES ('Ana', 'ana@mail.com', 30);

-- Obtener el último ID insertado:
SELECT last_insert_rowid();

INSERT INTO ... VALUES inserta una fila. last_insert_rowid() retorna el rowid generado. Especifica siempre las columnas explícitamente para mayor claridad.

DELETE FROM users WHERE id = 5;

-- Borrar todos los registros:
DELETE FROM logs;

-- Reset del AUTOINCREMENT:
DELETE FROM table_name;
DELETE FROM sqlite_sequence WHERE name = 'table_name';

DELETE FROM ... WHERE elimina filas. Sin WHERE, borra todo (pero mantiene la tabla). sqlite_sequence guarda el contador del AUTOINCREMENT.

-- Retornar datos de la fila insertada
INSERT INTO users (name, email)
VALUES ('Ana', 'ana@mail.com')
RETURNING id, name;

-- Retornar en la actualización
UPDATE products SET price = price * 1.1
WHERE category = 'tech'
RETURNING id, name, price;

-- Retornar en el delete
DELETE FROM logs WHERE created < '2024-01-01'
RETURNING id;

RETURNING (versión 3.35+) retorna las filas afectadas directamente. Evita un SELECT extra tras INSERT/UPDATE/DELETE. Soporta RETURNING *.

INSERT INTO users (name, email, age) VALUES
  ('Juan', 'j@mail.com', 25),
  ('María', 'm@mail.com', 30),
  ('Pedro', 'p@mail.com', 35);

-- Insertar a partir de un SELECT:
INSERT INTO active_users
SELECT * FROM users WHERE active = 1;

Un único INSERT con múltiples VALUES es más rápido que varios inserts. INSERT INTO ... SELECT copia datos de otra tabla o consulta.

-- SQLite 3.24+
INSERT INTO config (key, value)
VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE
SET value = excluded.value;

-- Ignorar si ya existe:
INSERT OR IGNORE INTO users (id, name)
VALUES (1, 'Ana');

ON CONFLICT DO UPDATE hace upsert — inserta o actualiza. excluded.value referencia el valor que se habría insertado. INSERT OR IGNORE simplemente ignora los conflictos.

-- Varias filas en un solo INSERT:
INSERT INTO products (name, price)
VALUES
  ('Teclado', 45.00),
  ('Ratón', 25.50),
  ('Monitor', 180.00);

-- Una transacción = mucho más rápido
-- que 3 INSERTs separados

-- INSERT a partir de un SELECT:
INSERT INTO products_archive (name, price)
SELECT name, price FROM products
WHERE discontinued = 1;

Un INSERT con varias filas (VALUES (...), (...)) es mucho más rápido que INSERTs separados (una sola transacción). INSERT ... SELECT copia datos de otra tabla. Ideal para poblar la BD en bulk.

SELECT * FROM users;

SELECT name, email FROM users
WHERE age > 18
ORDER BY name ASC
LIMIT 10 OFFSET 0;

SELECT DISTINCT city FROM users;

SELECT consulta datos. WHERE filtra, ORDER BY ordena, LIMIT/OFFSET pagina. DISTINCT elimina duplicados. Evita * en producción.

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.name, COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

INNER JOIN retorna solo filas con correspondencia. LEFT JOIN incluye todos los de la izquierda aunque no haya match. SQLite también soporta CROSS JOIN y RIGHT JOIN (3.39+).

-- Sustituye si hay conflicto de clave:
INSERT OR REPLACE INTO defs (key, value)
VALUES ('version', '2.0');

-- Ignora silenciosamente el conflicto:
INSERT OR IGNORE INTO defs (key, value)
VALUES ('version', '1.0');

-- Otras variantes:
INSERT OR ABORT ...   -- por defecto (error)
INSERT OR ROLLBACK ...
INSERT OR FAIL ...

-- REPLACE borra la fila antigua
-- e inserta una nueva (¡el id cambia!)

INSERT OR REPLACE borra la fila en conflicto e inserta una nueva (el id puede cambiar). INSERT OR IGNORE descarta silenciosamente. Para upserts que mantienen el id, prefiere ON CONFLICT DO UPDATE.

UPDATE users
SET age = 31, email = 'nuevo@mail.com'
WHERE name = 'Ana';

-- Actualizar todos (sin WHERE):
UPDATE products SET price = price * 1.1;

-- Con subquery:
UPDATE users SET active = 0
WHERE id IN (SELECT id FROM banned);

UPDATE ... SET ... WHERE modifica filas. Sin WHERE, actualiza todas las filas. Usa subqueries para condiciones complejas. Retorna el número de filas afectadas.

-- En el WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- En el FROM (tabla derivada)
SELECT category, average FROM (
  SELECT category, AVG(price) AS average
  FROM products GROUP BY category
) WHERE average > 50;

Las subqueries son consultas dentro de consultas. En el WHERE comparan con resultados agregados. En el FROM funcionan como tablas temporales. SQLite soporta subqueries correlacionadas.

INTEGER   -- enteros (1, 2, 3, 4, 6 u 8 bytes)
REAL      -- float 64-bit (IEEE 754)
TEXT      -- strings (UTF-8, UTF-16)
BLOB      -- datos binarios (sin conversión)
NUMERIC   -- decimal, boolean, date

-- SQLite usa tipado dinámico:
-- la columna acepta cualquier tipo

SQLite tiene 5 storage classes (afinidades). El tipado es dinámico — una columna INTEGER puede guardar texto. La afinidad solo sugiere una conversión preferente.

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

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

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

SQLite no tiene tipo DATE nativo — usa TEXT (ISO-8601), REAL (Julian) o INTEGER (Unix). Funciones: 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() muestra la storage class
SELECT typeof(42);       -- 'integer'
SELECT typeof(3.14);     -- 'real'
SELECT typeof('texto');  -- 'text'
SELECT typeof(NULL);     -- 'null'

CAST convierte entre tipos explícitamente. typeof() retorna la storage class real del valor. Útil para debugging y validación de datos importados.

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

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

-- rowid siempre está presente (excepto WITHOUT ROWID)

INTEGER PRIMARY KEY es un alias del rowid interno. AUTOINCREMENT impide reutilizar IDs borrados (más lento). Sin él, SQLite puede reutilizar el mayor ID.

-- Días entre dos fechas
SELECT julianday('2025-12-25') - julianday('now');

-- Edad en años
SELECT (julianday('now') - julianday('1990-05-15')) / 365.25;

-- Días del mes actual
SELECT strftime('%d', 'now', 'start of month', '+1 month', '-1 day');

julianday() convierte fechas a un número decimal de días. La diferencia da días. Divide entre 365.25 para años aproximados. strftime extrae componentes.

-- SQLite no fuerza tipos rígidos;
-- usa "affinity" (preferencia):
CREATE TABLE test (
  a INTEGER,   -- affinity INTEGER
  b TEXT,      -- affinity TEXT
  c NUMERIC    -- affinity NUMERIC
);

-- Puedes guardar cualquier valor:
INSERT INTO test VALUES ('123', 456, 'abc');

-- '123' se convierte en 123 (int)
-- 456 se guarda como texto '456'
-- 'abc' queda como texto

-- STRICT (3.37+) fuerza los tipos:
CREATE TABLE strict_table (x INTEGER) STRICT;

SQLite usa type affinity: las columnas tienen una preferencia de tipo pero aceptan cualquier valor (convirtiendo cuando es posible). CREATE TABLE ... STRICT (3.37+) fuerza tipos rígidos como en los demás SGBD.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price REAL NOT NULL CHECK(price >= 0),
  stock INTEGER DEFAULT 0
);

NOT NULL obliga a un valor. UNIQUE impide duplicados (acepta múltiples NULL). CHECK valida condiciones. DEFAULT define el valor cuando se omite.

-- SQLite no tiene BOOLEAN nativo
-- Usa INTEGER: 0 = false, 1 = true
SELECT * FROM users WHERE active = 1;

-- Manejo de NULL
SELECT COALESCE(email, 'sin email');
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT IFNULL(phone, 'N/A');

SQLite usa INTEGER 0/1 para booleanos. IS NULL / IS NOT NULL verifica nulos (no usar = NULL). COALESCE() retorna el primer no-nulo.

-- SQLite no tiene tipo DATE nativo.
-- Opciones:

-- 1. TEXT en ISO-8601 (recomendado):
INSERT INTO events (date)
VALUES ('2024-06-15 14:30:00');

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

-- Las funciones de fecha funcionan con TEXT:
SELECT date('now');                  -- hoy
SELECT datetime('now', '+7 days');   -- +7 días
SELECT strftime('%d/%m/%Y', date)
FROM events;

SQLite no tiene tipo DATE nativo — guarda fechas como TEXT en formato ISO-8601 (recomendado, ordena correctamente) o INTEGER (Unix timestamp). Las funciones date(), datetime() y strftime() operan sobre estos formatos.

-- Todos tienen TEXT affinity en SQLite:
CREATE TABLE people (
  name TEXT,           -- recomendado
  email VARCHAR(255),  -- el límite NO se impone
  country CHAR(2)      -- acepta cualquier tamaño
);

-- SQLite NO trunca ni rechaza
-- strings mayores que VARCHAR(255)

-- Convención: usar siempre TEXT

En SQLite, TEXT, VARCHAR(n) y CHAR(n) tienen todos TEXT affinity — el tamaño declarado no se impone (no hay truncado ni error). Convención: usa TEXT simple. La longitud máxima solo se valida si añades un CHECK.

CREATE TABLE files (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  data BLOB,
  type TEXT
);

-- Insertar literal hexadecimal
INSERT INTO files (name, data)
VALUES ('icon', x'89504E47');

-- Tamaño
SELECT length(data) FROM files;

BLOB almacena datos binarios (imágenes, PDFs). Los literales hexadecimales usan el prefijo x'hex'. length() retorna el tamaño en bytes. Para archivos grandes, prefiere el filesystem.

SELECT COUNT(*) FROM users;
SELECT SUM(total) FROM orders;
SELECT AVG(age) FROM users;
SELECT MIN(price), MAX(price) FROM products;
SELECT GROUP_CONCAT(name, ', ') FROM users;
SELECT TOTAL(price) FROM items;  -- 0.0 si está vacío

Agregados: COUNT, SUM, AVG, MIN, MAX. GROUP_CONCAT concatena valores. TOTAL() es como SUM() pero retorna 0.0 en vez de NULL.

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS ranking,
  SUM(salary) OVER (
    PARTITION BY department
  ) AS dept_total,
  LAG(name) OVER (ORDER BY salary) AS previous
FROM employees;

Las window functions (3.25+) calculan sobre un conjunto sin colapsar filas. RANK(), ROW_NUMBER(), LAG(), LEAD(). PARTITION BY define los grupos.

-- Primer valor no-nulo
SELECT COALESCE(phone, mobile, 'sin contacto')
FROM users;

-- NULLIF: retorna NULL si son iguales
SELECT NULLIF(price, 0);  -- NULL si price = 0

-- Evitar división entre cero:
SELECT total / NULLIF(qty, 0) AS average
FROM sales;

COALESCE() retorna el primer argumento no-NULL. NULLIF(a, b) retorna NULL si a = b. Útil para evitar la división entre cero y definir fallbacks.

SELECT category, COUNT(*) AS total,
       AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING total > 5
ORDER BY total DESC;

GROUP BY agrupa filas por columna. HAVING filtra grupos (como WHERE pero para agregados). WHERE filtra antes del agrupamiento, HAVING después.

WITH sales_2024 AS (
  SELECT user_id, SUM(total) AS total
  FROM orders
  WHERE strftime('%Y', date) = '2024'
  GROUP BY user_id
)
SELECT u.name, s.total
FROM users u
JOIN sales_2024 s ON u.id = s.user_id
ORDER BY s.total DESC;

WITH (CTE) crea consultas temporales con nombre. Mejora la legibilidad de queries complejas. Puedes encadenar múltiples CTEs separadas por comas. No se materializan.

SELECT upper('ana');        -- 'ANA'
SELECT lower('ANA');        -- 'ana'
SELECT length('hola');      -- 4
SELECT trim('  hola  ');    -- 'hola'
SELECT substr('SQLite', 1, 3);  -- 'SQL'
SELECT replace('a-b', '-', '+'); -- 'a+b'
SELECT instr('hola', 'o');  -- 2 (posición)

-- Concatenar:
SELECT 'Hola' || ' ' || 'Mundo';

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

Funciones de string: upper/lower/length/trim, substr, replace, instr (posición). Concatenación con ||. LIKE es case-insensitive para ASCII por defecto.

SELECT UPPER(name), LOWER(email);
SELECT LENGTH(description);
SELECT TRIM('  texto  ');
SELECT REPLACE(name, 'a', '@');
SELECT SUBSTR(name, 1, 3);
SELECT INSTR(email, '@');
SELECT printf('Hola %s, tienes %d años', name, age)
FROM users;

UPPER/LOWER cambian la capitalización. LENGTH cuenta caracteres. SUBSTR extrae una parte. INSTR encuentra la posición. printf() formatea strings con placeholders.

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

-- Jerarquía (árbol):
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.level + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

WITH RECURSIVE permite CTEs que se referencian a sí mismas. Ideal para jerarquías, series y grafos. Termina cuando la parte recursiva retorna cero filas.

-- Fecha/hora actual:
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

-- Extraer partes:
SELECT strftime('%Y', date) AS year,
       strftime('%m', date) AS month
FROM events;

-- Diferencia en días:
SELECT julianday('2024-12-31')
     - julianday('now');

Funciones de fecha: date(), time(), datetime() con modificadores (+1 month, start of month). strftime() extrae/formatea partes. julianday() calcula diferencias en días.

SELECT ABS(-5);          -- 5
SELECT ROUND(3.14159, 2); -- 3.14
SELECT RANDOM();          -- entero aleatorio
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 son nativas. MAX/MIN con múltiples argumentos son escalares (no agregados). Las funciones log, sqrt, pow requieren compilación con math (3.35+).

SELECT name,
  CASE
    WHEN age < 18 THEN 'menor'
    WHEN age BETWEEN 18 AND 65 THEN 'adulto'
    ELSE 'senior'
  END AS bracket
FROM users;

-- Atajo (SQLite 3.32+):
SELECT IIF(active = 1, 'Sí', 'No') FROM users;

CASE WHEN es un condicional multi-rama. IIF() es un ternario simplificado (condición, verdadero, falso). Ambos funcionan en SELECT, WHERE y ORDER BY.

CREATE INDEX idx_users_email
  ON users(email);

CREATE UNIQUE INDEX idx_code
  ON products(code);

CREATE INDEX idx_comp
  ON orders(user_id, created_at);

CREATE INDEX acelera búsquedas y ordenaciones. UNIQUE impide duplicados. Los índices compuestos siguen el orden de las columnas — la primera es la más importante para los filtros.

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

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

-- Mal resultado:
-- SCAN users  (¡full table scan!)

EXPLAIN QUERY PLAN muestra cómo SQLite ejecuta la query. SEARCH USING INDEX = bueno. SCAN = full table scan (malo). Esencial para optimizar queries lentas.

-- Índice que cubre toda la query
CREATE INDEX idx_covering
  ON orders(user_id, total, date);

-- Esta query no necesita acceder a la tabla:
SELECT total, date FROM orders
WHERE user_id = 42;

-- EXPLAIN muestra: USING COVERING INDEX

Un covering index contiene todas las columnas necesarias para la query. SQLite responde solo con el índice sin leer la tabla. Más rápido para queries frecuentes con columnas específicas.

-- Solo indexa usuarios activos
CREATE INDEX idx_active_email
  ON users(email)
  WHERE active = 1;

-- Más pequeño y rápido que un índice total
-- Solo se usa si la query incluye WHERE active = 1

Los índices parciales (WHERE) indexan solo un subconjunto de filas. Más pequeños, más rápidos de mantener. SQLite solo los usa si la query tiene una condición compatible.

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE active = 1;

SELECT * FROM active_users;

-- Borrar
DROP VIEW IF EXISTS active_users;

CREATE VIEW guarda una consulta como tabla virtual. No almacena datos — ejecuta el SELECT en cada acceso. Simplifica queries complejas y controla el acceso a columnas.

-- Índice en varias columnas:
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, date);

-- El ORDEN de las columnas importa:
-- el índice sirve para:
--   WHERE customer_id = ?
--   WHERE customer_id = ? AND date > ?
--   ORDER BY customer_id, date

-- PERO NO sirve para:
--   WHERE date > ?  (sola)

-- Regla: igualdad primero,
-- range/ordenación después

Los índices compuestos (varias columnas) siguen la regla del prefijo: sirven para filtros en la primera columna, o primera + segunda. El orden importa — columnas de igualdad primero, range/ordenación después. Un índice (a, b) no ayuda en WHERE b = ?.

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

-- La query debe usar la misma expresión:
SELECT * FROM users
WHERE lower(email) = 'ana@mail.com';

Los índices de expresión indexan el resultado de una función. La query debe usar exactamente la misma expresión para que el índice se aproveche. Útil para búsquedas case-insensitive.

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  UPDATE users
    SET updated_at = datetime('now')
    WHERE id = NEW.id;
END;

-- Borrar trigger
DROP TRIGGER IF EXISTS update_timestamp;

CREATE TRIGGER ejecuta SQL automáticamente en eventos. AFTER UPDATE, BEFORE INSERT, AFTER DELETE. NEW.columna y OLD.columna acceden a los valores.

-- Reconstruir índices (tras muchos
-- cambios o cambio de collation):
REINDEX;                      -- todos
REINDEX idx_customers_email;  -- uno solo

-- Verificar integridad de la BD:
PRAGMA integrity_check;

-- Analizar para el optimizador:
ANALYZE;

-- Eliminar índice innecesario:
DROP INDEX IF EXISTS idx_old;

-- Los índices cuestan en escritura —
-- elimina los que no se usan

REINDEX reconstruye índices (útil tras cambios de collation). ANALYZE ayuda al optimizador a elegir mejor. DROP INDEX elimina índices no usados — cada índice tiene coste en escritura, así que mantén solo los necesarios.

-- Listar índices de una tabla
PRAGMA index_list('users');

-- Detalles de un índice
PRAGMA index_info('idx_users_email');

-- Borrar índice
DROP INDEX IF EXISTS idx_users_email;

-- Reconstruir todos los índices
REINDEX;

PRAGMA index_list() muestra los índices de la tabla. PRAGMA index_info() muestra las columnas del índice. REINDEX reconstruye todos — útil tras muchos deletes.

CREATE TRIGGER audit_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, id, field, old_value, new_value)
  VALUES ('products', NEW.id, 'price', OLD.price, NEW.price);
END;

Los triggers de auditoría registran cambios en una tabla de log. OLD.valor es el valor antes, NEW.valor después. Útil para histórico de precios y cambios de estado.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100
  WHERE id = 1;
UPDATE accounts SET balance = balance + 100
  WHERE id = 2;

COMMIT;
-- o ROLLBACK; para anular todo

BEGIN inicia, COMMIT confirma, ROLLBACK anula. Todas las operaciones entre BEGIN y COMMIT son atómicas — o todas tienen éxito o ninguna se aplica.

-- Compactar el archivo de la BD
VACUUM;

-- VACUUM en un esquema específico (3.27+):
VACUUM main;

-- Auto-vacuum (configurar antes de crear tablas):
PRAGMA auto_vacuum = FULL;
PRAGMA auto_vacuum = INCREMENTAL;

VACUUM reconstruye el archivo, recuperando el espacio de datos borrados. auto_vacuum = FULL libera páginas automáticamente. Requiere exclusividad — no usar en producción activa.

PRAGMA synchronous = FULL;    -- más seguro (por defecto)
PRAGMA synchronous = NORMAL;  -- buen compromiso
PRAGMA synchronous = OFF;     -- más rápido (¡riesgo!)

-- Combinación recomendada:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

synchronous controla cuántos fsync() hace SQLite. FULL es el más seguro. NORMAL con WAL es seguro y más rápido. OFF puede corromper en un fallo de energía.

BEGIN DEFERRED;   -- por defecto (lock solo cuando es necesario)
BEGIN IMMEDIATE;  -- write lock inmediato
BEGIN EXCLUSIVE;  -- lock total (nadie lee ni escribe)

-- Recomendado para apps concurrentes:
BEGIN IMMEDIATE;

DEFERRED (por defecto) solo adquiere el lock en la primera escritura. IMMEDIATE previene deadlocks en concurrencia. EXCLUSIVE bloquea todo — raramente necesario.

-- Verificar integridad de la BD
PRAGMA integrity_check;
-- "ok" si todo está bien

-- Verificar foreign keys huérfanas
PRAGMA foreign_key_check;

-- Optimizar estadísticas de índices
PRAGMA optimize;

integrity_check valida la estructura interna. foreign_key_check encuentra referencias rotas. optimize actualiza estadísticas para el query planner. Ejecutar periódicamente.

-- Transacciones anidadas:
SAVEPOINT point1;

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

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

-- Deshacer solo hasta el point2:
ROLLBACK TO point2;

-- Confirmar todo:
RELEASE point1;

-- Útil para "deshacer parcialmente"
-- sin abortar toda la transacción

SAVEPOINT permite transacciones anidadas. ROLLBACK TO nombre deshace hasta ese punto sin abortar el resto; RELEASE nombre confirma. Ideal para deshacer parcialmente un conjunto de operaciones.

PRAGMA journal_mode = WAL;

-- Ventajas:
-- Las lecturas no bloquean escrituras
-- Las escrituras no bloquean lecturas
-- Mejor rendimiento en concurrencia

PRAGMA wal_autocheckpoint = 1000;
PRAGMA synchronous = NORMAL;

WAL (Write-Ahead Logging) permite lecturas concurrentes con escrituras. Más rápido que el modo DELETE (por defecto) en la mayoría de escenarios. Persistente entre conexiones.

-- Esperar hasta 5s si la BD está bloqueada
PRAGMA busy_timeout = 5000;

-- Verificar estado de locking
PRAGMA lock_status;

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

busy_timeout define cuánto tiempo esperar cuando la BD está bloqueada por otra conexión. Sin esto, retorna SQLITE_BUSY inmediatamente. Esencial en apps multi-thread.

-- SQLite bloquea la BD durante
-- las escrituras. Si otra conexión intenta
-- escribir, recibe "database is locked".

-- Esperar hasta 5s en vez de fallar ya:
PRAGMA busy_timeout = 5000;

-- Con WAL, lecturas y escrituras
-- pueden ocurrir en simultáneo:
PRAGMA journal_mode = WAL;

-- Buenas prácticas:
--   1 conexión para escritura
--   busy_timeout siempre activo
--   transacciones cortas

SQLite permite solo un escritor a la vez. busy_timeout hace que las conexiones esperen (en vez del error "database is locked"). Con WAL, lecturas y escrituras concurren. Mantén transacciones cortas y usa busy_timeout siempre.

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

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

ROLLBACK TO step2;  -- anula solo el step2
RELEASE step1;      -- confirma el step1

SAVEPOINT crea puntos de restauración parciales dentro de una transacción. ROLLBACK TO anula hasta el savepoint. RELEASE confirma. Útil para operaciones multi-etapa.

PRAGMA journal_mode = DELETE;   -- por defecto (borra el journal)
PRAGMA journal_mode = WAL;      -- write-ahead log
PRAGMA journal_mode = MEMORY;   -- journal en RAM
PRAGMA journal_mode = OFF;      -- sin journal (¡riesgo!)

-- Ver modo actual:
PRAGMA journal_mode;

El journal mode controla cómo SQLite garantiza la atomicidad. DELETE es el predeterminado. WAL es el recomendado. OFF es peligroso — corrupción en caso de crash.

-- Extraer un valor
SELECT json_extract(data, '$.name') FROM configs;
SELECT data->>'$.email' FROM users;  -- 3.38+

-- Crear JSON
SELECT json_object('id', id, 'name', name) FROM users;
SELECT json_group_array(name) FROM users;

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

SQLite tiene soporte JSON nativo (3.38+). json_extract() o el operador ->> extrae valores. json_object() crea JSON. json_group_array() agrega en un array.

PRAGMA cache_size = -64000;   -- 64 MB de caché
PRAGMA temp_store = MEMORY;   -- temp en RAM
PRAGMA mmap_size = 268435456; -- 256 MB mmap
PRAGMA page_size = 4096;      -- tamaño de página

-- Verificar settings:
PRAGMA cache_size;
PRAGMA compile_options;

cache_size negativo = KB de memoria. temp_store = MEMORY evita el disco para tablas temporales. mmap_size usa memory-mapped I/O. Ajustar según la RAM disponible.

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

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

ON CONFLICT DO UPDATE acumula valores con excluded.columna. Ideal para contadores y estadísticas diarias. DO NOTHING ignora silenciosamente los conflictos.

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

-- Eliminar una clave
SELECT json_remove('{"a":1,"b":2}', '$.b');

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

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

json_set() añade/modifica claves. json_remove() elimina. json_type() retorna el tipo. json_each() es una table-valued function que itera arrays JSON.

-- Generar una serie de números (3.38+)
SELECT value FROM generate_series(1, 10);

-- Con paso
SELECT value FROM generate_series(0, 100, 5);

-- Usar en un JOIN
SELECT d.name, s.value AS day
FROM departments d
CROSS JOIN generate_series(1, 7) AS s;

generate_series() es una table-valued function que genera secuencias. Útil para informes por período, relleno de gaps y pruebas. Retorna la columna value.

-- Columnas calculadas automáticamente
-- (SQLite 3.31+):
CREATE TABLE items (
  price REAL,
  qty INTEGER,
  total REAL GENERATED ALWAYS
    AS (price * qty) STORED
);

-- VIRTUAL (calculada en la lectura):
--   AS (...) VIRTUAL
-- STORED (guardada en disco):
--   AS (...) STORED

INSERT INTO items (price, qty) VALUES (10, 3);
SELECT total FROM items;  -- 30

-- No se puede escribir en ellas

Las generated columns (SQLite 3.31+) calculan el valor a partir de otras columnas. STORED guarda en disco (puede indexarse); VIRTUAL calcula en la lectura. No pueden escribirse directamente — solo leerse.

CREATE VIRTUAL TABLE docs USING fts5(
  title, body
);

INSERT INTO docs VALUES
  ('Guía SQLite', 'Base de datos embebida...'),
  ('Tutorial SQL', 'Consultas y filtros...');

SELECT * FROM docs WHERE docs MATCH 'sqlite AND datos';
SELECT * FROM docs WHERE docs MATCH 'title:guia';

FTS5 es búsqueda de texto completo. MATCH hace la búsqueda con operadores booleanos (AND, OR, NOT). Soporta búsqueda por columna con columna:término.

ATTACH DATABASE 'otra.db' AS second;

-- Consultar entre bases de datos:
SELECT * FROM main.users
UNION ALL
SELECT * FROM second.users;

-- Copiar datos:
INSERT INTO main.archive
SELECT * FROM second.logs;

DETACH DATABASE second;

ATTACH vincula otra BD a la sesión actual. Referencia con nombre.tabla. main es la BD principal. Permite JOINs y copias entre BDs. Máximo 10 BDs en simultáneo.

-- CTE simple:
WITH totals AS (
  SELECT customer_id, SUM(total) AS total
  FROM orders GROUP BY customer_id
)
SELECT c.name, t.total
FROM totals t JOIN customers c ON c.id = t.customer_id;

-- CTE RECURSIVA (jerarquías):
WITH RECURSIVE subs AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e JOIN subs s ON e.manager_id = s.id
)
SELECT * FROM subs;

CTE (WITH) crea tablas temporales con nombre. WITH RECURSIVE permite recursión — ideal para jerarquías (organigramas, categorías padre/hijo, árboles). Más legible que subqueries anidadas.

-- Ranking por relevancia
SELECT title, rank FROM docs
WHERE docs MATCH 'sqlite'
ORDER BY rank;

-- Snippet con resaltado
SELECT snippet(docs, 1, '<b>', '</b>', '...', 32)
FROM docs WHERE docs MATCH 'datos';

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

rank ordena por relevancia (BM25). snippet() extrae un fragmento con resaltado. bm25() permite ponderar columnas — el primer argumento da más peso al título.

-- Límites de SQLite:
-- Máx. columnas por tabla: 2000
-- Máx. tamaño de BD: 281 TB
-- Máx. longitud TEXT/BLOB: 1 GB
-- Máx. profundidad de JOIN: 1000
-- Máx. variables por query: 32766

PRAGMA compile_options;
PRAGMA max_page_count;

SQLite es generoso con los límites: BD hasta 281 TB, texto hasta 1 GB, 2000 columnas. PRAGMA compile_options muestra las funcionalidades compiladas. Suficiente para el 99% de los casos.

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

Configuración ideal para aplicaciones: WAL para concurrencia, NORMAL para rendimiento, foreign_keys ON para integridad, busy_timeout para evitar errores SQLITE_BUSY.

-- SQLite tiene ALTER TABLE limitado
-- Para cambios complejos:

-- 1. Crear nueva tabla
CREATE TABLE users_new (...);

-- 2. Copiar los datos
INSERT INTO users_new SELECT ... FROM users;

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

El ALTER TABLE de SQLite es limitado (solo ADD/RENAME/DROP COLUMN). Para cambios complejos (cambiar tipo, constraints), usa el patrón de 4 pasos: crear, copiar, borrar, renombrar.

-- Ver el SQL ejecutado
PRAGMA vdbe_listing = ON;

-- Contar operaciones de I/O
PRAGMA cache_spill = OFF;

-- Tamaño real de la BD
SELECT page_count * page_size AS bytes
FROM pragma_page_count(), pragma_page_size();

-- Espacio libre
PRAGMA freelist_count;

freelist_count muestra páginas libres (espacio recuperable con VACUUM). page_count × page_size da el tamaño total. vdbe_listing muestra bytecode para debugging avanzado.

-- LENTO: 1000 transacciones
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
-- ...

-- RÁPIDO: 1 transacción
BEGIN;
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, 'b');
-- ... (1000 inserts)
COMMIT;

Sin transacción, cada INSERT hace un fsync individual (muy lento). Envolver en BEGIN/COMMIT agrupa en un único fsync. Diferencia de 100x o más en bulk inserts.

-- 1. Ver el plan de ejecución
EXPLAIN QUERY PLAN SELECT ...;

-- 2. Medir el tiempo
.timer on
SELECT ...;

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

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

EXPLAIN QUERY PLAN muestra si usa índice o scan. .timer on mide el tiempo de ejecución. ANALYZE recopila estadísticas para el query planner. PRAGMA optimize las actualiza.

-- VACUUM: reescribe la BD, libera
-- espacio de filas borradas:
VACUUM;

-- (el archivo .db encoge)
-- Ejecutar fuera de una transacción,
-- requiere espacio libre en disco

-- ANALYZE: recopila estadísticas
-- para el optimizador de queries:
ANALYZE;

-- Auto-vacuum (alternativa continua):
PRAGMA auto_vacuum = FULL;
-- (debe definirse antes
--  de crear tablas)

VACUUM reescribe el archivo y recupera el espacio de datos borrados (el .db encoge). ANALYZE recopila estadísticas para que el optimizador elija mejores índices. auto_vacuum = FULL hace limpieza continua (a definir antes de crear tablas).

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

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

Los prepared statements con placeholders ? previenen SQL injection y son más rápidos en ejecuciones repetidas. Nunca concatenes strings SQL con input del usuario.

-- SQLite nativo NO tiene encriptación
-- Opciones:
-- • SQLCipher (fork con AES-256)
-- • SEE (extensión oficial de pagado)
-- • wxSQLite3 (extensión open-source)

-- Buenas prácticas:
-- • Permisos del archivo: chmod 600 bd.db
-- • No guardar secrets en texto plano
-- • Usar WAL (¡el archivo -wal también tiene datos!)

SQLite no encripta de forma nativa. SQLCipher es la solución más popular (AES-256). Cuidado: en modo WAL, los archivos -wal y -shm también contienen datos.

-- Backup seguro con la BD en uso:
sqlite3 mi.db ".backup backup.db"

-- NO copies el archivo directamente
-- con la BD abierta (puede corromperse)

-- Dump en SQL (portable):
sqlite3 mi.db .dump > schema.sql

-- Restaurar desde el dump:
sqlite3 nueva.db < schema.sql

-- .backup = binario, seguro online
-- .dump = texto, para migraciones

Nunca copies el archivo .db con la base abierta (puede corromperse). Usa .backup (copia binaria segura online) o .dump (SQL en texto, portable entre versiones). Restaura el dump con sqlite3 nueva.db < archivo.sql.

-- ✅ Bueno para:
-- Apps móviles / desktop
-- Prototipos y pruebas
-- BDs hasta ~1 TB con 1 writer
-- Sistemas embedded / IoT
-- Caché local / configuraciones

-- ❌ Malo para:
-- Múltiples writers concurrentes
-- Acceso vía red (NFS/SMB)
-- Alta disponibilidad / failover

SQLite es ideal para apps locales, prototipos y embedded. Un único archivo, cero configuración. No es adecuado para múltiples writers en red o sistemas que necesitan failover automático.

-- 1. Usar WAL
PRAGMA journal_mode = WAL;

-- 2. Timeout generoso
PRAGMA busy_timeout = 10000;

-- 3. Transacciones cortas
BEGIN IMMEDIATE;
-- operaciones rápidas
COMMIT;

-- 4. Una conexión para escritura
-- Múltiples para lectura (WAL lo permite)

SQLite permite un writer a la vez. WAL permite lecturas concurrentes con escrituras. Mantén transacciones cortas. Usa BEGIN IMMEDIATE para evitar deadlocks. Una conexión de escritura, varias de lectura.