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.
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).
3.1. Console Interaction (CLI)
Sección titulada «3.1. Interacción mediante Consola (CLI)»The engine provides the sqlite3 CLI tool, which runs directly from your operating system's command console to open or create binary databases:
Database Initialization and Creation
Sección titulada «Inicialización y Creación de la Base de Datos»# Si el archivo especificado no existe, la consola de sqlite3 lo creará de forma transparentesqlite3 portelcore_laboratorio.dbAfter executing the instruction, the system command console will change to the engine's interactive prompt: sqlite>.
Internal Control Commands (Dot-Commands)
Sección titulada «Comandos de Control Interno (Dot-Commands)»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 declarativeCREATE TABLEsnippet, 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.
3.2. DDL: Data Structure Definition
Sección titulada «3.2. DDL: Definición de Estructura de Datos»Declarative commands for the provisioning and immutability of logical schemas:
CREATE TABLE
Sección titulada «CREATE TABLE»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.
DROP TABLE
Sección titulada «DROP TABLE»Permanently deletes a logical structure and its entire content:
-- ATENCIÓN: Esta instrucción destruye los datos de forma irreversible.DROP TABLE portel_usuarios;ALTER TABLE
Sección titulada «ALTER TABLE»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;
3.3. DML: Data Manipulation
Sección titulada «3.3. DML: Manipulación de Datos»Interactive commands to perform write, modification, and deletion transactions:
INSERT INTO
Sección titulada «INSERT INTO»Adds records by explicitly specifying the target columns:
INSERT INTO portel_usuarios (nombre, email, edad)VALUES ('Portel User', 'portel_user@portelcore.com', 28);UPDATE … SET … WHERE
Sección titulada «UPDATE … SET … WHERE»Selectively modifies the values of existing records.
[!WARNING] WHERE Filter Omission: If you run
UPDATEwithout theWHEREvalidation clause, the database will apply the modification to all table records indiscriminately.
UPDATE portel_usuariosSET edad = 29, telefono = '+34 600 000 000'WHERE id = 1;DELETE FROM … WHERE
Sección titulada «DELETE FROM … WHERE»Removes specific rows based on logical conditions.
DELETE FROM portel_usuariosWHERE id = 1;3.4. DQL: Query and Analytical Extraction
Sección titulada «3.4. DQL: Consulta y Extracción Analítica»Declarative data extraction syntax optimized through sorting and pagination:
Allows retrieving specific fields instead of performing full extractions:
-- Recupera únicamente las columnas de comunicaciónSELECT nombre, email FROM portel_usuarios;WHERE (Predictive Filtering)
Sección titulada «WHERE (Filtrado Predictivo)»-- Consulta exclusiva para usuarios de laboratorio mayores de 30 añosSELECT * FROM portel_usuariosWHERE edad > 30;
-- Búsqueda de coincidencia parcial utilizando comodines en el emailSELECT * FROM portel_usuariosWHERE email LIKE '%@portelcore.com';ORDER BY
Sección titulada «ORDER BY»Sorts the retrieved records based on a specific criterion:
-- Ordenación descendente basada en la edadSELECT * FROM portel_usuariosORDER BY edad DESC;LIMIT and OFFSET (Premium Pagination)
Sección titulada «LIMIT y OFFSET (Paginación Premium)»-- Recupera los 5 primeros registrosSELECT * 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.productoFROM portel_usuariosJOIN portel_pedidos ON portel_usuarios.id = portel_pedidos.usuario_id;🏁 SQL Syntactic Validation Checklist
Sección titulada «🏁 Checklist de Validación Sintáctica en SQL»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 NULLandUNIQUEconstraints on critical columns to avoid orphan record inconsistencies. - ✓ Key Indexing: Verify the existence of indexes on columns used recurrently in
WHEREtype filters orORDER BYsorting in large-scale tables.

