Ir al contenido

Essential SQLite Command Reference

Practical command guide for the sqlite3 CLI interactive engine and syntactic reference of DDL, DML, and DQL with relational standards for Portelcore.

0views

This section describes the practical use of the sqlite3 interactive console tool and details the structured syntax of the SQL standard, divided into three essential categories for data engineering:

  • DDL (Data Definition Language): Declarative instructions to structure schemas, tables, and indexes (e.g., CREATE TABLE).
  • DML (Data Manipulation Language): Tactical commands to interact with and modify persisted records (e.g., INSERT, UPDATE, DELETE).
  • DQL (Data Query Language): Analytical syntax to query, filter, and relate information (e.g., SELECT).

The engine provides the sqlite3 CLI tool, which runs directly from your operating system's command console to open or create binary databases:

Ventana de terminal
# Si el archivo especificado no existe, la consola de sqlite3 lo creará de forma transparente
sqlite3 portelcore_laboratorio.db

After executing the instruction, the system command console will change to the engine's interactive prompt: sqlite>.

Control commands do not belong to the standard SQL language syntax; they are internal instructions of the sqlite3 interactive interpreter and always begin with a dot (.):

  • .tables: Quickly lists the names of all active tables in the database file.
  • .schema nombre_tabla: Shows the original declarative CREATE TABLE snippet, revealing columns and data types.
  • .header on: Enables header printing with column names when performing analytical reads.
  • .mode column: Visually formats query results into aligned, readable columns in the console.
  • .quit: Safely closes the engine's interactive terminal and returns to the operating system console.

Declarative commands for the provisioning and immutability of logical schemas:

Creates a table on disk by defining integrity constraints and field types:

CREATE TABLE portel_usuarios (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nombre TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
edad INTEGER,
fecha_registro REAL DEFAULT (datetime('now', 'localtime'))
);
  • PRIMARY KEY: Unique numerical identifier of the row on disk.
  • AUTOINCREMENT: Guarantees a strictly incremental key increase without reusing deleted IDs.
  • NOT NULL: Restricts the insertion of empty fields.
  • UNIQUE: Prevents duplicate emails or data at the row level.
  • DEFAULT: Injects the current system time if omitted during insertion.

Permanently deletes a logical structure and its entire content:

-- ATENCIÓN: Esta instrucción destruye los datos de forma irreversible.
DROP TABLE portel_usuarios;

Modifies columns or renames the structure without altering persistence:

  • Add Column:
    ALTER TABLE portel_usuarios ADD COLUMN telefono TEXT;
  • Rename Structure:
    ALTER TABLE portel_usuarios RENAME TO portel_clientes;

Interactive commands to perform write, modification, and deletion transactions:

Adds records by explicitly specifying the target columns:

INSERT INTO portel_usuarios (nombre, email, edad)
VALUES ('Portel User', 'portel_user@portelcore.com', 28);

Selectively modifies the values of existing records.

[!WARNING] WHERE Filter Omission: If you run UPDATE without the WHERE validation clause, the database will apply the modification to all table records indiscriminately.

UPDATE portel_usuarios
SET edad = 29, telefono = '+34 600 000 000'
WHERE id = 1;

Removes specific rows based on logical conditions.

DELETE FROM portel_usuarios
WHERE id = 1;

Declarative data extraction syntax optimized through sorting and pagination:

Allows retrieving specific fields instead of performing full extractions:

-- Recupera únicamente las columnas de comunicación
SELECT nombre, email FROM portel_usuarios;
-- Consulta exclusiva para usuarios de laboratorio mayores de 30 años
SELECT * FROM portel_usuarios
WHERE edad > 30;
-- Búsqueda de coincidencia parcial utilizando comodines en el email
SELECT * FROM portel_usuarios
WHERE email LIKE '%@portelcore.com';

Sorts the retrieved records based on a specific criterion:

-- Ordenación descendente basada en la edad
SELECT * FROM portel_usuarios
ORDER BY edad DESC;
-- Recupera los 5 primeros registros
SELECT * FROM portel_usuarios LIMIT 5;
-- Recupera 10 registros omitiendo los primeros 20 (Simula la página 3 de una tabla visual)
SELECT * FROM portel_usuarios LIMIT 10 OFFSET 20;

JOIN (Relationship between Structures)

Sección titulada «JOIN (Relación entre Estructuras)»

Logically combines records from tables linked by foreign keys:

-- Si disponemos de una tabla de auditorías 'portel_pedidos' vinculada por 'usuario_id'
SELECT portel_usuarios.nombre, portel_pedidos.producto
FROM portel_usuarios
JOIN portel_pedidos ON portel_usuarios.id = portel_pedidos.usuario_id;

Ensure the correct construction of your relational queries in the Portelcore database by following this weekly checklist:

  • ✓
  • ✓ Query Optimization: Avoid massive use of SELECT * in production environments, prioritizing explicit field selection to save bandwidth and CPU latency.
  • ✓ Referential Integrity: Always declare NOT NULL and UNIQUE constraints on critical columns to avoid orphan record inconsistencies.
  • ✓ Key Indexing: Verify the existence of indexes on columns used recurrently in WHERE type filters or ORDER BY sorting in large-scale tables.