SQLite: 10 Comandos y sentencias básicas

En el post sobre la introducción a SQLite vimos que era este tipo de base de datos y como podíamos instalarlo en nuestro sistema operativo o incluso como llegar a usarla con algún lenguaje, sin embargo, nos quedamos ahí, no vimos comandos o sentencias básicas de uso, así pues, con este articulo pretendo eso, que sea como una chuleta con algunos comandos y sentencias básicas de SQLite en consola.

¡Vayamos allá!

1. Mostrar todas las bases de datos disponibles

Lo primero es abrir la consola o terminal de usuario y escribir sqlite3 para acceder a ella.

A diferencia de MySQL, SQLite trabaja con archivos de base de datos individuales, por lo que no existe un comando SHOW DATABASES. En su lugar el comando que debemos usar es PRAGMA, que es especifico de SQLite y nos permite modificar el funcionamiento de la biblioteca SQLite o consultar datos internos.

En este caso queremos consultar que base de datos existen en SQLite, entonces hacemos:

PRAGMA database_list;

En principio, al menos por ahora no debería de mostrar nada, ya que no tenemos nada 😜​

2. Seleccionar una base de datos

En SQLite, abres una base de datos con el siguiente comando desde la línea de comandos, consola, terminal o PowerShell:

sqlite3 base_de_pruebas.db

Es importante tener en cuenta que SQLite, no crea físicamente el archivo de la base de datos a menos que hagas alguna operación que lo justifique, como crear una tabla o insertar datos.

En otras palabras, sino insertamos ningún dato, al cerrar la sesión, la supuesta base de datos que hemos creado, se eliminará.

Una nota importante a tener en cuenta también es que no puedes crear una nueva base de datos desde cero desde dentro del prompt de SQLite con solo poner nombre.db. Eso solo se puede hacer saliendo del prompt y ejecutando desde la terminal:

sqlite3 nombre_de_nuestra_base.db

Dicho esto, a modo de prueba crearemos una base de datos y una tabla con algo más de sentido para seguir más fácilmente los ejemplos del artículo.

Abrimos la consola y escribimos:

sqlite3 tienda.db

y a continuación, cuando se nos abra el prompt de SQLite creamos una nueva tabla:

CREATE TABLE productos (
    id INTEGER PRIMARY KEY,
    nombre TEXT,
    precio REAL
);

Y ahora, si ejecutamos el comando PRAGMA database_list, sí que nos mostrará la tabla:

3. Listar todas las tablas de una base de datos

Para ver todas las tablas dentro de la base de datos abierta tenemos el comando:

.tables

Evidentemente, como se muestra en la imagen de abajo, primero debemos de seleccionar la base de datos sobre la cual queremos consultar las tablas.

También podemos hacer:

SELECT name FROM sqlite_master WHERE type='table';

Cuyo resultado será el mismo, mostrar todas las tablas de la base de datos seleccionada.

Podemos filtrar resultados y buscar si hay alguna tabla que empiece con la letra… ‘a’ por ejemplo:

.tables 'a%'

En nuestro caso evidentemente no existe ninguna 😝​

4. Obtener la estructura de una tabla

Para inspeccionar la estructura de una tabla, usamos una vez más PRAGMA, ya que vamos a consultar datos internos de SQLite:

PRAGMA table_info(nombre_de_tabla);

En nuestro ejemplo:

5. Insertar múltiples filas en una sola consulta

Para optimizar inserciones, puedes agrupar varias en una sola consulta:

INSERT INTO productos (nombre, precio) VALUES ('Camiseta', 9.99), ('Pantalones', 15.50), ('Chaqueta invierno', 29.99);

Aunque también podemos usar el comando .read para leer y ejecutar un archivo .sql.

Esto nos permite crear tablas automáticamente o insertar muchos más datos de forma masiva y cómoda como así también restaurar una copia de la base de datos.

Así que para seguir con unos mejores ejemplos creamos un archivo .sql llamado “tienda_ropa.sql” con el siguiente contenido para “la tienda”:

INSERT INTO productos (nombre, precio) VALUES ('Camiseta básica', 9.99);
INSERT INTO productos (nombre, precio) VALUES ('Jeans azul', 24.95);
INSERT INTO productos (nombre, precio) VALUES ('Chaqueta de cuero', 79.90);
INSERT INTO productos (nombre, precio) VALUES ('Sudadera con capucha', 29.50);
INSERT INTO productos (nombre, precio) VALUES ('Camisa blanca', 19.99);
INSERT INTO productos (nombre, precio) VALUES ('Pantalón chino', 34.95);
INSERT INTO productos (nombre, precio) VALUES ('Falda negra', 22.00);
INSERT INTO productos (nombre, precio) VALUES ('Vestido largo', 39.90);
INSERT INTO productos (nombre, precio) VALUES ('Zapatillas deportivas puma', 49.95);
INSERT INTO productos (nombre, precio) VALUES ('Zapatos de vestir', 59.90);
INSERT INTO productos (nombre, precio) VALUES ('Cinturón de cuero', 7.99);
INSERT INTO productos (nombre, precio) VALUES ('Gorra nike', 9.50);
INSERT INTO productos (nombre, precio) VALUES ('Bufanda de lana', 12.99);
INSERT INTO productos (nombre, precio) VALUES ('Calcetines pack x3', 4.99);
INSERT INTO productos (nombre, precio) VALUES ('Chaqueta impermeable', 54.90);

Y para ejecutarlo, en el prompt de SQLite escribimos:

.read tienda_ropa.sql

6. Actualizar datos con condiciones

Ahora supongamos que queremos modificar algún dato, por ejemplo, estamos de rebajas y vamos actualizar el precio de los pantalones.

Para ello usamos UPDATE y, como vamos a modificar un registro especifico lo usamos en conjunto con WHERE:

UPDATE productos SET precio = 10 WHERE id = 2;

Si no usáramos WHERE, se actualizarían todos los registros de la tabla.

7. Eliminar registros

Para eliminar registros específicos sin borrar toda la tabla haremos lo mismo que antes, usaremos WHERE pero con la sentencia DELETE:

DELETE FROM productos WHERE id = 3;

En el ejemplo hemos eliminado el registro 3 correspondiente a la chaqueta de invierno.

8. Usar LIMIT para paginación

Si trabajas con grandes volúmenes de datos, LIMIT ayuda a controlar la cantidad de registros devueltos.

Es útil usar LIMIT con OFFSET para controlar la cantidad de registros a ver, así como desde qué punto empezar.

Con LIMIT mostramos X resultados.

Con OFFSET nos muestra los resultados a partir de X.

Ejemplos:

Mostrar los primeros 5 productos:

SELECT * FROM productos LIMIT 5;

Mostrar 5 productos, empezando desde el producto número 6:

SELECT * FROM productos LIMIT 5 OFFSET 5;

Aunque también se puede escribir así (de forma combinada):

SELECT * FROM productos LIMIT 5, 5;

Lo cual es equivalente a: LIMIT 5 OFFSET 5.

9. Buscar datos sin importar mayúsculas o minúsculas

SQLite es sensible a mayúsculas y minúscula, por tanto, no será lo mismo buscar ‘camiseta’ que ‘Camiseta’.

Si hacemos esto:

SELECT * FROM productos WHERE nombre = 'camiseta';

No nos mostrará nada. Para ello debemos de forzar la insensibilidad con COLLATE NOCASE:

SELECT * FROM productos WHERE nombre COLLATE NOCASE = 'camiseta';

10. Usar EXPLAIN para optimizar consultas

Antes de nada, ¿Qué es EXPLAIN?

Es un comando que te dice cómo se va a ejecutar una consulta: qué tablas se van a leer, en qué orden, si se usarán índices, cuántas veces se recorrerá una tabla, etc.

Resumiendo, EXPLAIN QUERY PLAN te muestra cómo SQLite va a ejecutar una consulta, diciéndole algo así como: «Explícame cómo vas a buscar esta información.»

El objetivo es poder optimizar consultas, entender si se usan índices o si hay recorridos innecesarios.

Ten en cuenta que no modifica nada en la base de datos. Solo te da información.

Vamos a suponer que queremos hacer una consulta como la siguiente:

SELECT * FROM productos WHERE precio < 30;

Pues bien, ahora le pedimos a SQLite que nos explique cómo va a ejecutar esa consulta y mejorar su rendimiento:

EXPLAIN QUERY PLAN SELECT * FROM productos WHERE precio < 30;

El resultado:

Este mensaje significa que SQLite recorrerá toda la tabla (realizará un SCAN TABLE) para encontrar los productos cuyo precio sea menor a 30.

Como no hay un índice en la columna precio, hace un scan completo, por tanto, no puede buscar de forma eficiente, ya que tiene que mirar fila por fila (lo que se llama «table scan»).

¿Conclusión?

Menor eficiencia.

¿Solución?

Agregar un índice a la columna precio y de esa manera optimizar la consulta.

Para ello:

CREATE INDEX idx_precio ON productos(precio);

Y si ahora repetimos el comando:

EXPLAIN QUERY PLAN SELECT * FROM productos WHERE precio < 30;

El resultado es un poco distinto:

Ahora ya no ha hecho un scan table, sino que ahora, cuando queramos buscar los productos cuyo precio sea menor a 30, SQLite usará el índice en lugar de escanear toda la tabla, lo que es mucho más rápido, especialmente si tienes cientos o miles de filas.

Y por ahora eso es todo. Estos son para mí, 10 comandos y sentencias básicas de SQLite.

En una segunda parte podemos explicar algo más avanzado. Espero que te resulte de guía o de chuleta 😉

Sobre el autor