7 Structured Query Language

También abreviado como SQL, es el lenguaje de alto nivel que desde los años 70 del siglo XX nos permite interactuar con estructuras tabulares en sistemas de gestión de bases de datos tabulares o RDBMS¹.

Existen varios subgrupos dentro de la especificación de SQL dependiendo de las tareas que nos interesen:

Data Definition Language (DDL) reúne los comandos necesarios para crear nuestras estructuras físicas tras tener claro qué vamos a almacenar en el sistema (posterior al modelado)
Data Manipulation Language (DML) que reúne los comandos para insertar, borrar, actualizar y seleccionar, incluso agregando, la información existente en la base de datos.
Data Control Language (DCL) que dispone los comandos para dar o revocar el acceso a la información.

Y en los casos en los que nuestro sistema permita la gestión de transacciones Transaction Control Language (TCL).

7.1 DDL

Tras la etapa de modelado y haber seleccionado nuestro repositorio de datos, emplearemos los comandos que nos permiten crear el homólogo físico de nuestro modelo en el sistema.

Es decir, si nuestro modelo dispone una tabla con ALUMNOS en nuestro modelo

erDiagram
    ALUMNOS {
        numero id_alumno PK
        texto nombre
        texto apellido
    }

Tenemos que crear las instrucciones que indican esta estructura al sistema

CREATE TABLE alumnos (
    id_alumno INTEGER,
    nombre TEXT,
    apellido TEXT,
)

Uan cuestión clave cuestiones en esta sentencia, la tipología de datos deberá ser establecida en base a los tipos de datos que soporte nuestro sistema. Además, podemos incluir la restricción de que el id_alumno debe ser único,

ALTER TABLE alumnos ADD PRIMARY KEY(id_alumno)

O bien crearlo en una única sentencia.

CREATE TABLE alumnos (
    id_alumno INTEGER,
    nombre TEXT,
    apellido TEXT,
    PRIMARY KEY (id_alumno)
)

Si, por ejemplo, seleccionamos SQLite para nuestro ejemplo, podemos ver cómo resulta de forma práctica.

import sqlite3

# Conectamos con la base de datos chinook.db
con = sqlite3.connect("my.db")

# Obtenemos un cursor que utilizaremos para hacer las consultas
cur = con.cursor()

Advertencia

Puede que necesitéis ejecutar

cur.execute("""PRAGMA foreign_keys = ON;""");

para poder contemplar las restricciones de clave foránea que veremos más adelante.

Y continuar ejecutando nuestra creación de tabla.

# La creamos si no existe
cur.execute("""
    CREATE TABLE IF NOT EXISTS alumnos (
        id_alumno INTEGER,
        nombre TEXTO,
        apellido TEXTO,
        PRIMARY KEY(id_alumno)
    )
""");

# Mostramos la información de la tabla
cur.execute("PRAGMA table_info(alumnos)").fetchall()

[(0, 'id_alumno', 'INTEGER', 0, None, 1),
 (1, 'nombre', 'TEXTO', 0, None, 0),
 (2, 'apellido', 'TEXTO', 0, None, 0)]

Hemos añadido la condición IF NOT EXISTS que nos permite crear una tabla solo si no existe. Si intentara crearla existiendo previamente, nos arrojará un error.

# La creamos si no existe
cur.execute("""
    CREATE TABLE alumnos (
        id_alumno INTEGER,
        nombre TEXTO,
        apellido TEXTO,
        PRIMARY KEY(id_alumno)
    )
""");

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In[4], line 2
      1 # La creamos si no existe
----> 2 cur.execute("""
      3     CREATE TABLE alumnos (
      4         id_alumno INTEGER,
      5         nombre TEXTO,
      6         apellido TEXTO,
      7         PRIMARY KEY(id_alumno)
      8     )
      9 """);

OperationalError: table alumnos already exists

La función .fetchall() tras la ejecución del PRAGMA se realice para que retorne la información. Un cursor ejecuta las acciones en el sistema destino, la base de datos, pero para obtener de vuelta el resultado de la operación, deberemos pedirlo expresamente.

Podemos seguir extendiendo nuestro modelo, con el resto de estructuras, donde deberemos indicar los campos que registran claves foráneas, para que exista un nexo explícito entre ambas tablas. Esto permite, por ejemplo, que no de de alta a un alumno que no esté en el listado de alumnos previamente.

erDiagram
    ALUMNOS {
        numero id_alumno PK
        texto nombre
        texto apellido
    }
    CURSA{
        numero id_alumno FK
        numero id_asignatura FK
    }
    ASIGNATURAS {
        numero id_asignatura PK
        texto nombre
    }

    ALUMNOS ||--o{ CURSA: matriculado
    ASIGNATURAS ||--o{ CURSA: cursando

# La creamos si no existe
cur.execute("""
    CREATE TABLE IF NOT EXISTS asignaturas (
        id_asignatura INTEGER PRIMARY KEY,
        nombre TEXTO
    )
""");

cur.execute("""
    CREATE TABLE IF NOT EXISTS cursa (
        id_alumno INTEGER,
        id_asignatura INTEGER,
        FOREIGN KEY (id_alumno) REFERENCES alumnos (id_alumno),
        FOREIGN KEY (id_asignatura) REFERENCES asignaturas (id_asignatura)
    )
""");

# Mostramos la información de la tabla
cur.execute("PRAGMA table_info(cursa)").fetchall()

[(0, 'id_alumno', 'INTEGER', 0, None, 0),
 (1, 'id_asignatura', 'INTEGER', 0, None, 0)]

Con esto hemos informado de una estructura base que ahora podremos poblar con datos.

7.2 DML

El lenguaje de manipulación de datos nos permite introducir información, alterarla, borrar y por último seleccionar o extraer información del sistema.

7.2.1 Inserciones

Insertamos datos de un alumno.

cur.execute("""
    INSERT INTO alumnos VALUES (1, 'Iraitz', 'Montalban');
""");

cur.execute("""
    INSERT INTO alumnos VALUES (1, 'Iraitz', 'Montalban');
""");

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
Cell In[7], line 1
----> 1 cur.execute("""
      2     INSERT INTO alumnos VALUES (1, 'Iraitz', 'Montalban');
      3 """);

IntegrityError: UNIQUE constraint failed: alumnos.id_alumno

Estas restricciones se extienden a las tablas con clave foránea, ya que podríamos estar matriculando a un alumno que no existe en una asignatura que no existe.

cur.execute("""
    INSERT INTO cursa VALUES (2, 1);
""");

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
Cell In[8], line 1
----> 1 cur.execute("""
      2     INSERT INTO cursa VALUES (2, 1);
      3 """);

IntegrityError: FOREIGN KEY constraint failed

Es una de las labores clave de los sistemas transaccionales, garantizar que el sistema responde a las lógicas de los procesos de negocio que hemos modelado.

7.2.2 Actualizaciones

Podemos actualizar su información, donde la cláusula where permite filtrar y aislar qué fila deberemos actualizar.

cur.execute("""
    UPDATE alumnos SET apellido = 'Montalbán' WHERE id_alumno = 1;
""");

7.2.3 Selección

Y extraer información de estas tablas, recordando que si traemos información de la fuente deberemos hacer un fetch para que la retorne a nuestro cliente.

cur.execute("""
    SELECT * 
    FROM alumnos
""").fetchall()

[(1, 'Iraitz', 'Montalbán')]

7.2.4 Desnormalización

Si contamos con información asociada deberemos juntarla para poder extraer los datos relevantes.

# Nuevos alumnos
cur.execute("""
    INSERT INTO alumnos VALUES (2, 'Javier', 'Garcia'), (3, 'Miguel', 'Fernandez');
""");

# Nuevas asignaturas
cur.execute("""
    INSERT INTO asignaturas VALUES (1, 'Matemáticas'), (2, 'Historia'), (3, 'Biología');
""");

# Matrículas
cur.execute("""
    INSERT INTO cursa VALUES (1, 2), (2, 3), (3, 3);
""");

Por ejemplo, ¿qué alumnos cursan biología? No podemos saberlo de la tabla que almacena la relación.

cur.execute("""
    SELECT * 
    FROM cursa
""").fetchall()

[(1, 2), (2, 3), (3, 3)]

Deberemos juntarla con la tabla que presenta la información de las asignaturas y filtrar por nombre.

cur.execute("""
    SELECT * 
    FROM cursa c
        JOIN asignaturas asig ON asig.id_asignatura = c.id_asignatura
    WHERE asig.nombre = 'Biología'
""").fetchall()

[(2, 3, 3, 'Biología'), (3, 3, 3, 'Biología')]

Y esta a su vez juntarla con los alumnos para obtener los datos de estos.

cur.execute("""
    SELECT * 
    FROM cursa c
        JOIN asignaturas asig ON asig.id_asignatura = c.id_asignatura
        JOIN alumnos alum ON alum.id_alumno = c.id_alumno
    WHERE asig.nombre = 'Biología'
""").fetchall()

[(2, 3, 3, 'Biología', 2, 'Javier', 'Garcia'),
 (3, 3, 3, 'Biología', 3, 'Miguel', 'Fernandez')]

Gracias a la estructura tabular tenemos las relaciones y los datos de nuestras entidades bien representados una vez que desnormalizamos y bien podemos seleccionar los atributos (columnas) que sean de interés.

cur.execute("""
    SELECT alum.nombre, alum.apellido
    FROM cursa c
        JOIN asignaturas asig ON asig.id_asignatura = c.id_asignatura
        JOIN alumnos alum ON alum.id_alumno = c.id_alumno
    WHERE asig.nombre = 'Biología'
""").fetchall()

[('Javier', 'Garcia'), ('Miguel', 'Fernandez')]

O incluso realizar agregaciones sobre estos datos.

cur.execute("""
    SELECT asig.nombre, COUNT(alum.id_alumno) AS cuantos_alumnos
    FROM cursa c
        JOIN asignaturas asig ON asig.id_asignatura = c.id_asignatura
        JOIN alumnos alum ON alum.id_alumno = c.id_alumno
    WHERE asig.nombre = 'Biología'
    GROUP BY asig.nombre
""").fetchall()

[('Biología', 2)]

7.3 TCL

Las transacciones nos obligan a declarar procesos en la propia base de datos de forma que podamos invocarlos como si de funciones se trataran. Esto permite a la base de datos realizar su labor y gestionar todas las operaciones de las transacciones tal y como se precisan.


BEGIN TRANSACTION;

INSERT INTO alumnos VALUES (4, 'María', 'Garcia');

INSERT INTO cursa VALUES (4, 2), (4, 1);

COMMIT;

El COMMIT final se hace cargo de realizar toda la operación y en caso de fallo siempre podremos recurrir al ROLLBACK que deshace los cambios realizados al inicio de la transacción sin afectar a otros procesos del sistema.

Os dejo una muy buena entrada sobre la historia de SQL ↩︎