SQLite3: Chuleta: Guía Rápida

SQLite es un motor de base de datos formado por una librería de lenguaje C que normalmente guarda los datos en un fichero.

sqlite3 es el intérprete de comandos de la versión 3 de SQLite.

Instalar y abrir un fichero de base de datos

Instalar sqlite3 (en Debian):
$ sudo aptitude install sqlite3

Abrir el fichero de base de datos:
$ sqlite3 database_file.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.

Aparece un prompt del intérprete de comandos:
sqlite>

Comandos de SQLite3

Mostrar todas las tablas:
sqlite> .tables
NOMBRES CLIENTES VENTAS PRODUCTOS


Terminar:
sqlite> .quit


Mostrar la ayuda:
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
.echo on|off Turn command echo on or off
....


Mostrar la lista de todas las bases de datos y sus ficheros asociados:
sqlite> .databases


Mostrar el esquema de todas las tablas:
sqlite> .schema


Mostrar el esquema de una tabla:
sqlite> .schema PRODUCTOS
CREATE TABLE "PRODUCTOS" (Id INTEGER PRIMARY KEY AUTOINCREMENT ,TStamp INTEGER NOT NULL ,Name VARCHAR(60) NOT NULL ,Price DECIMAL(10,5) NOT NULL);


Ejecutar un comando de UNIX:
sqlite>.system ls -l

Tipos de datos de SQLite

SQLite usa tipos de datos dinámicos. Éstos son los tipos que podremos utilizar:

NULL: un valor nulo.

INTEGER: un entero con signo, almacenado mediante 1, 2, 3, 4, 6 u 8 bytes dependiendo de la magnitud del valor.

REAL: un valor de coma flotante, almacenado como un número de coma flotante IEEE de 8 bytes.

TEXT: una cadena de texto, almacenado usando la codificación (UTF-8, UTF-16BE ó UTF-16LE).

BLOB: un BLOB de datos (Binary Large OBject), almacenado exactamente cómo se introdujo.

Tipos de datos in SQLite3


Otros tipos como VARCHAR(N), DECIMAL(N, M) son asimilados a los tipos de datos anteriores basándose en su afinidad (VARCHAR a TEXT, DECIMAL a INTEGER), por lo que los límites (N, M..) no son tenidos en cuenta.

SQL

Asociar la base de datos (clientes) de otro fichero (clientes.db):
sqlite> ATTACH DATABASE 'clientes.db' AS clientes;
Ahora el comando .database muestra también la base de datos clientes.
Podemos acceder a las tablas de la base de datos clientes mediante el prefijo 'clientes.'


Desasocia una base de datos previamente asociada:
sqlite> DETACH DATABASE clientes;

Crear una nueva tabla:
sqlite> CREATE TABLE VARS (name VARCHAR(128) PRIMARY KEY ,value VARCHAR(512) NOT NULL);

Crear una tabla sólo si no existe previamente:
sqlite> CREATE TABLE IF NOT EXISTS VARS (name VARCHAR(128) PRIMARY KEY ,value VARCHAR(512) NOT NULL);

Muestra el contenido de una tabla:
sqlite> select * from CLIENTS;
1|Vincent
2|John

Muestra el contenido de una tabla limitando el número de filas en el resultado:
sqlite> select * from CLIENTS LIMIT 3
Muestra como mucho tres filas.
La palabra clave LIMIT pude usarse junto con ORDER BY para especificar la columna según la cual ordenar los resultados.
sqlite> select * from CLIENTS ORDER BY Name DESC LIMIT 3

Borrar una tabla:
sqlite> drop table ticket;

Renombrar una tabla:
sqlite> .tables
CASHIER
sqlite> alter table CASHIER rename to CLIENTS;
sqlite> .tables
CLIENTS


Copiar el contenido de una tabla en otra:
sqlite> INSERT INTO CLIENT SELECT * FROM CLIENT_OLD;
Las columnas que devuelve el select han de coincidir con los elementos que espera el comando insert.
SQL As Understood By SQLite (Insert)

Copiar parte del contenido de una tabla en otra:
sqlite> INSERT INTO CLIENT (name,value,state,address) SELECT name,value,-1,address FROM CLIENT_OLD;


Insertar una columna en una tabla, pero si aparece un conflicto debido a una limitación UNIQUE o PRIMARY KEY (clave primaria) entonces reemplazar la columna:
sqlite> INSERT OR REPLACE INTO VARS (name,value) VALUES('color', 'blue');
SQL As Understood By SQLite (On conflict)



Añadir una nueva columna a una tabla

Vamos a añadir la columna Difficulty a esta tabla:
sqlite> .schema ANALUX
CREATE TABLE ANALUX (Height INTEGER PRIMARY KEY ,Price REAL DEFAULT NULL);

El comando "alter" añade la nueva columna:
sqlite> ALTER TABLE ANALUX ADD Difficulty REAL DEFAULT NULL;

sqlite> .schema ANALUX
CREATE TABLE ANALUX (Height INTEGER PRIMARY KEY ,Price REAL DEFAULT NULL ,Difficulty REAL DEFAULT NULL);


Si queremos rellenar la columna Difficult con data procedente de otra tabla. p.ej: BLOCK donde Height es la clave primaria para las dos tablas:
sqlite> UPDATE ANALUX SET Difficulty = (SELECT Difficulty FROM BLOCK WHERE BLOCK.Height = ANALUX.Height);



TRANSACCIONES

Comenzar una transacción:
sqlite>BEGIN

Ejecutar algunas operaciones...

Hacer commit de la transacción:
sqlite>COMMIT

Si algo va mal podemos deshacer la transacción en lugar de hacer commit.
sqlite>ROLLBACK

Ejecutar un comando SQL en la línea de comandos

Insertar una fila en una tabla:
(Notar que tenemos que escapar las dobles comillas)
$ sqlite3 foo.db "INSERT INTO CASHIER VALUES (\"foo\",\"bar\");"

Ejecutar un script de SQL en la línea de comandos

Si ejecutamos un script SQL en un fichero de nombre example.sql como éste:

CREATE TABLE CASHIER (name    VARCHAR(10) PRIMARY KEY ,pass    VARCHAR(10) NOT NULL);
INSERT INTO CASHIER VALUES ("foo","bar");
SELECT * FROM CASHIER;

Podemos ejecutarlo:
$ sqlite3 foo.db example.sql


O usando un here documento(documento insertado en el script) en un shell script:

P.ej: fichero example.sh

#!/bin/bash

sqlite3 foo.db EOF
CREATE TABLE CASHIER (name    VARCHAR(10) PRIMARY KEY ,pass    VARCHAR(10) NOT NULL);
INSERT INTO CASHIER VALUES ("foo","bar");
SELECT * FROM CASHIER;
EOF

Entonces lo ejecutamos:
$ bash example.sh

Mostrar el tiempo de ejecución de una query (consulta)

El comando .timer muestra el tiempo de ejecución de una consulta. ON para activarlo y OFF para desactivarlo.
sqlite> .timer ON

sqlite> SELECT * FROM CASHIER;
...
Run Time: real 0.326 user 0.000523 sys 0.000261

Para desactivarlo:
sqlite> .timer OFF

Explicar una query SQL

Podemos obtener una descripción de alto nivel de cómo se ejecuta internamente una consulta SQL.
https://www.sqlite.org/eqp.html

Añadimos EXPLAIN QUERY PLAN al comienzo de la query:
sqlite> EXPLAIN QUERY PLAN SELECT * FROM ADDRESS;

QUERY PLAN
`--SCAN TABLE ADDRESS

REFERENCIA

SQLite core functions

SQLite Frequently Asked Questions

SQLite Command Line Interface

Traducido de:

SQLite3 Cheat Sheet