Tutorial de SQL

La gran mayoría de aplicaciones delegan en sistemas de bases de datos SQL la responsabilidad de custodiar la información de forma durable y consistente en el tiempo. En las siguientes líneas se introduce de manera sencilla los conceptos básicos de SQL.

El siguiente artículo describe cómo realizar las operaciones más básicas sobre dos de los sistemas gestores más ampliamente utilizados: MySQL y PostgreSQL. Si no tienes ningún sistema gestor instalado en local prueba de añadir uno a tu PC mediante sencillos pasos con Docker:

• Instala una base de datos MySQL con Docker
• Instala una base de datos PostgreSQL con Docker
• Crea un usuario limitado y una base de datos con MySQL
• Crea un usuario limitado y una base de datos con PostgreSQL

Qué es SQL

Las aplicaciones necesitan ir acompañadas de un sistema de almacenamiento de información que sea duradero y consistente en el tiempo. El programa delega en el sistema de persistencia, la responsabilidad de custodiar los datos independientemente de cuántas veces se reinicie la aplicación. Incluso pueden dar servicio a más de una aplicación de forma simultánea permitiendo el balanceo de carga de las aplicaciones sobre un mismo conjunto de datos. Los sistemas gestores de la información, denominados habitualmente bases de datos, han evolucionado mucho con el tiempo, llegando a ser complejos sistemas que ofrecen muchas más funcionalidades que un mero alojamiento de datos.

Las bases de datos actualmente más usadas son las llamadas relacionales y están basadas en la teoría matemática de conjuntos para el almacenamiento de la información. También se tiene presente la relación que hay entre diferentes conjuntos para establecer vínculos fuertes entre los datos que contienen. Dentro de los sistemas relacionales, los más usados son los que permiten interactuar con ellos mediante el lenguaje SQL, siglas de Structured Query Language. SQL es un lenguaje que permite manipular conjuntos de datos y extraer la información que almacena el sistema de base de datos con un alto nivel de control.

Organización de los datos

Los sistemas SQL definen que cada conjunto de datos o colección debe estar alojado en su propia tabla que indica el tipo de datos que almacena. Y cada registro de información será una fila de la tabla. Así por ejemplo, la tabla persona será una colección de registros de personas individuales en forma de filas, de manera similar a como se representaría un listado de personas en una hoja de cálculo. Por tanto, cada registro estará compuesto por uno o más datos de la persona como nombre, apellidos, nif, etc…. Obviamente cada persona tendrá sus propios datos, así que cada fila deberá tener los huecos necesarios para almacenar el nombre y los apellidos de cada una de las personas, al igual que las columnas en la hoja de cálculo.

En definitiva, las tablas estarán compuestas de columnas, y al igual que pasa en Java donde un atributo de una clase puede tener un tipo, en los sistemas SQL pasará lo mismo. Las columnas podrán tener un tipo SQL para indicar que tal columna debe contener fechas, números, textos o alguno de los otros tipos aceptados. Algunas de estas se podrán marcar como obligatorias, otras se podrán definir con un valor por defecto y otras simplemente podrán estar vacías, dato que se representará con NULL. Cómo se debe comportar cada columna se especifica en el momento de creación de la tabla. Siguiendo con el ejemplo de la colección de personas y usando SQL estándar, la creación de la tabla sería:

create table PERSONA (
    nombre char(50),
    apellidos char(255),
    nif char(9),
    fechaNacimiento date
);

El código anterior para la creación de la tabla requiere de una conexión previa con una base de datos existente, y puede variar ligeramente según la base de datos a usar. Cada base de datos tiene sus propios tipos de columnas, algunos de ellos muy optimizados para almacenar datos de una forma determinada, sobre todo cuando se deben hacer operaciones complejas sobre éstos o cuando se requiere almacenar grandes volúmenes de información. Se debe tener a mano la documentación oficial del sistema gestor de la misma versión que se esté utilizando. En cualquier caso, el código de creación de la tabla funcionará en la mayoría de los sistemas y básicamente crea una tabla con cuatro columnas, tres de ellas para almacenar texto con char(<longitud>), más una columna de tipo fecha con date.

Nota: tradicionalmente las bases de datos MySQL usaban por defecto unas tablas que no aceptan integridad referencial justificándose por una mejora de rendimiento denominado MyIsam. Debe asegurarse que las tablas que se creen hacen uso del sistema de almacenamiento de reemplazo InnoDB. Se puede forzar durante la creación de la tabla con el parámetro engine si es necesario. Afortunadamente las versiones de MySQL tienen el sistema de tablas InnoDB activado por defecto.

create table PERSONA(
    -- omitidos los campos
    ...
    )
-- activado de las restricciones en MySQL
ENGINE=InnoDB;

Las columnas de ancho fijo, creadas con char reservan la cantidad de letras indicadas para cada fila, independientemente de si se consumen o no. Sin embargo, si el sistema únicamente guardara personas extranjeras que sólo tienen un apellido se estaría desaprovechando mucho espacio. Así que habitualmente se usa como texto una columna de ancho variable al que también se le debe indicar un máximo varchar(50). Se debe tener presente que por herencia técnica, la mayoría de sistemas tienen como longitud máxima de char 255 caracteres y 65,535 de varchar. Si la longitud es mayor, normalmente se usará otro tipo de datos como longtext o clob.

La tabla resume los principales tipos de datos que pueden usarse en MySQL y PostgreSQL, pero conviene tener siempre a mano la documentación oficial del sistema a usar. Estos sólo son algunos tipos más usados, hay muchos más. Puede obtenerse más información acerca de los tipos de cada uno de los sistemas en:

• MySQL
• PostgreSQL

Retomando la creación de la tabla de PERSONA, es posible marcar algunos campos como obligatorios y otros con un valor por defecto. A modo de ejemplo, se puede reescribir la creación de la tabla indicando campos obligatorios. Obviamente no pueden existir dos tablas con el mismo nombre, así que previamente se debe eliminar la tabla anterior con drop table <nombre_tabla>:

-- Borrado de la tabla anterior
drop table PERSONA;

-- MySQL default value para Date
create table PERSONA (
    nombre char(50) not null,
    apellidos char(255),
    nif char(9),
    fechaNacimiento date default CURRENT_TIMESTAMP
);

-- PostgreSQL default value para Date
create table PERSONA (
    nombre char(50) not null,
    apellidos char(255),
    nif char(9),
    fechaNacimiento date default NOW()
);

Sólo debe ejecutarse uno de los create table que se han mostrado, el adecuado a la base de datos que se use. Tanto CURRENT_TIMESTAMP como NOW son funciones que vienen de base en el sistema gestor correspondiente y que devuelven la fecha actual del sistema. Cada sistema aporta una colección de funciones que pueden usarse, por ejemplo para convertir una cadena de texto en una fecha.

-- MySQL String to Date
STR_TO_DATE('21-5-2013','%d-%m-%Y');

-- PostgreSQL String to Date
to_date('21-5-2013','%d-%m-%Y');

Las funciones aceptan dos parámetros que son la cadena de texto que contiene la fecha y el formato de fecha a utilizar. Será necesario invocar a las funciones de conversión de cadenas de texto delimitadas con ‘, para pasar a objetos y poder así introducir registros en las tablas desde la consola de SQL.

No siempre será necesario borrar la tabla y volver a crearla. De hecho, no será deseable puesto que provoca la pérdida de los datos que contenga. Los sistemas gestores permiten la modificación de las columnas en tablas existentes con alter table <nombre_tabla> …. Mediante la modificación será posible añadir, borrar, modificar o renombrar la definición de cualquier columna. La modificación de una columna para cambiar de un tipo a otro no siempre será posible. Si la tabla contiene datos y el sistema gestor no sabe como realizar la conversión de tipos, la operación no se realizará con éxito.

-- Borrado de columna nombre
alter table PERSONA drop column nombre;

-- Crear la columna no nula de tipo varchar
alter table PERSONA add column nombre varchar(50) not null;

-- Modificar el nombre del campo fecha
alter table PERSONA rename column fechaNacimiento to fec_nac;

-- Pasar nif a varchar
alter table PERSONA modify nif varchar(9);

Las operaciones de modificación de una tabla existente permiten aplicar cambios incrementales sobre una base de datos. No sería muy práctico que por cada cambio se tuvieran que traspasar los datos desde un esquema de tablas obsoleto a uno nuevo. Por eso, es importante analizar los evolutivos del programa, para tratar de reflejar las modificaciones de las nuevas funcionalidades siempre como cambios incrementales sobre el modelo de datos.

Al igual que puede suceder durante un cambio de tipos para una columna, en donde la sentencia puede dar error si no se puede completar el cambio al no poder convertir automáticamente los datos que contiene la tabla, la inclusión de la restricción de no nulo, puede dar error si ya hay registros con valores nulos almacenados. En ese caso, se deben actualizar primero todos los registros dándoles un valor no nulo y luego incluir la restricción. Esta situación ocurrirá al incluir cualquiera de las condiciones que se explican a continuación.

Restricciones sobre datos

Las restricciones sobre los datos deben diferenciarse en dos conjuntos, los que afectan únicamente a la tabla en cuestión que contiene los datos o a aquellas restricciones en las que intervienen al menos dos tablas.

Restricciones de la tabla

Las restricciones propias de la tabla permiten controlar el comportamiento de cualquiera de las columnas que contiene. Estas restricciones provocarán un error en las operaciones de manejo de datos si se trata de insertar o guardar algún dato que no respete todas las restricciones.

Campos no nulos

Esta restricción se ha visto por encima durante la creación de la tabla PERSONA y durante la modificación de una columna con alter table. Ambas son una manera resumida de la creación de una restricción no nula sobre la columna.

-- Poner constraint en MySQL
alter table PERSONA modify nombre varchar(50) NOT NULL;

-- Eliminar constraint en MySQL
alter table PERSONA modify nombre varchar(50);

-- Poner constraint en PostgreSQL
alter table PERSONA alter column nombre set not null;

-- Eliminar constraint en PostgreSQL
alter table PERSONA alter column nombre drop not null;

Como puede verse, cada sistema gestor tiene su forma particular de manipular la base de datos. Aun así, ANSI SQL es un estándar que trata de homogeneizar las sentencias SQL para ofrecer una interfaz común para los programas y los desarrolladores, aunque sin mucho éxito, puesto que el programador deberá acudir en última instancia al manual oficial del motor de base de datos.

Claves primarias

Es bastante recomendable que exista una manera de referirse inequívocamente a un sólo registro de cualquier tabla de la base de datos, permitiendo realizar cualquier operación de modificación de un registro determinado rápidamente. Para ello, se define una columna o un conjunto de ellas como su identificador. El identificador es por tanto, un valor o valores que no pueden repetirse y que permiten obtener una fila de datos de entre todas las que hay en la tabla. Por ejemplo, el campo nif podría ser un identificador válido para la tabla de PERSONA. Si se decide que ese campo debe ser el identificador de los registros de la tabla, deberá marcarse como un campo obligatorio y único, ambas condiciones quedan implícitas al definir la columna como primary key.

-- MySQL y PostgreSQL
alter table PERSONA add primary key (nif);

También habría podido indicarse la condición de clave primaria en el momento de creación de la tabla.

-- MySQL y PostgreSQL
create table PERSONA (
    nif varchar(9) PRIMARY KEY,
    nombre varchar(50) not null,
    apellidos char(255),
    fechaNacimiento date
);

En una hipotética situación en donde se obtiene que dos personas pueden tener el mismo nif pero en países diferentes, no bastaría hacer una clave primaria basada sólo en el nif, se debería incluir también el código de país como parte del identificador. Esto daría como resultado que para identificar una fila se necesite el código de país y el nif, esta combinación debe devolver de la base de datos un único registro como máximo, dando lugar a una clave compuesta. Las compuestas se definen al final de la declaración de la tabla o mediante un alter table:

-- MySQL y PostgreSQL
create table PERSONA (
    codPais varchar(3),
    nif varchar(9),
    nombre varchar(50) not null,
    apellidos char(255),
    fechaNacimiento date,
    PRIMARY KEY(nif,codPais)
);

Cuando uno o o más campos forman una clave primaria, la base de datos crea un índice interno en donde mantiene unos punteros que le indican en qué posición de la tabla está cada registro, para una rápida localización de cualquiera de sus filas, siempre en función del identificador, evitándose así la búsqueda iterativa por todos sus registros. Así que el programador debe indicar que el campo, por ejemplo nif, formará parte de la clave primaria, ya sea en la creación de la tabla o en un momento posterior con la modificación de ésta.

Por comodidad y para no trabajar con más de una columna, se suele crear un campo numérico no nulo e incremental del cual se obtienen números de forma atómica, para utilizarlos como identificadores. Ese es el motivo por el que es frecuente encontrar URLs del estilo …/persona?id=453 para referirse a la fila de datos de la persona identificada con el número 453. Si se guarda una nueva persona, ésta última tendrá el número 454, y así sucesivamente. Si lo que se necesita es realizar una actualización sobre una en concreto, el proceso recuperará la fila según su identificador y actualizará todos los datos de la fila, menos ese, ya que éste número debe ser inmutable para poder recuperar siempre el mismo registro. Algunas bases de datos como MySQL asignan automáticamente el siguiente valor disponible a la columna que se utiliza como identificador. Otras como PostgreSQL requiere crear un objeto en base de datos, que se encarga de emitir valores no repetidos. Esos objetos se denominan secuencias.

La creación de la tabla de personas con un identificador automático en MySQL queda de la siguiente forma:

-- MySQL
create table PERSONA (
    id int NOT NULL AUTO_INCREMENT,
    nif varchar(9),
    nombre varchar(50) not null,
    apellidos char(255),
    fechaNacimiento date,
    PRIMARY KEY(id)
);

Así que cada vez que se inserte una persona nueva en la tabla que no lleve el valor del campo id informador lo considerará como un nuevo registro en la tabla y le asignará un valor automático. Si por el contrario se inserta un registro que tiene un identificador ya utilizado, el registro existente quedará sobreescrito en su totalidad por el nuevo.

La opción de auto-incremento se consigue en PostgreSQL mediante el uso de una secuencia. Así que será necesario crear la secuencia para obtener un comportamiento similar. Cada llamada a la secuencia obtendrá un identificador diferente que deberá hacerse llegar como valor del campo usado como clave primaria del registro a insertar.

-- PostgreSQL
create table PERSONA (
    id int PRIMARY KEY,
    nif varchar(9),
    nombre varchar(50) not null,
    apellidos char(255),
    fechaNacimiento date
);

create sequence persona_sequence
start 1
increment 1;

-- consumo de la secuencia
nextval('persona_sequence');

Siempre que se pueda, se recomienda trabajar con identificadores de una única columna porque simplificará el proceso en todas las operaciones posteriores con la tabla. En aquellos casos en donde se debe utilizar una clave compuesta, será preferible crear un identificador único, más una clave única de los campos compuestos. Aunque parezca redundante, la clave única evitará que haya más de un registro con los mismos valores y el identificador simplificará la localización de una fila de la tabla.

Claves únicas

Al igual que el identificador incluye intrínsecamente una clave única, las bases de datos permiten crear una restricción de clave única para una columna o para un conjunto de columnas. Y no necesariamente debe identificar a la fila, ya que se permite que la columna o columnas contengan NULL como valores. Si la clave única es de un sólo campo bastará con añadir unique tras su definición en la creación de la tabla.

-- MySQL y PostgreSQL
create table PERSONA (
    ...
    nif varchar(9) unique,
    ...
);

Si la clave única está compuesta por más de un campo, obligatoriamente se deberá definir al final de la creación de la tabla citando todas las columnas. Opcionalmente, se puede dar un nombre a la restricción.

-- MySQL y PostgreSQL
create table PERSONA (
    ...
    codPais varchar(3),
    nif varchar(9),
    ...,
    CONSTRAINT uk_pais_nif
    UNIQUE KEY (codPais, nif)
);

También será posible crear la restricción de unicidad independientemente de si es para una o más columnas con la modificación de una tabla existente:

-- MySQL y PostgreSQL
alter table PERSONA add
    constraint uk_pais_nif
    unique key (codPais, nif);

Como se ha dicho, la diferencia principal entre clave primaria y clave única es que ésta última permitirá tener más de una fila en donde los campos que conforman la clave sean nulos. En cambio, la clave primaria no permite tener más de un valor nulo.

Otras restricciones

Las tablas no sólo permiten restricciones de clave única, también es posible añadir restricciones más complejas que involucren algún cálculo, como por ejemplo, que un campo fecha no sea menor que una fecha dada, o que el campo salario sea mayor que un determinado importe.

-- MySQL y PostgreSQL
create table PERSONA (
    ...
    salario decimal(9,2) check (salario > 1000.00),
    ...,
);

Las comprobaciones de tipo check se realizarán en todas las inserciones y modificaciones, lanzando un error si no se satisfacen sobre cualquiera de los registros con los que se trata de operar. La comprobación debe ir entre los paréntesis y puede ser una expresión válida para SQL. Conviene revisar la documentación oficial del sistema gestor para identificar los usos y escenarios posibles.

Restricciones sobre otras tablas

Las restricciones que afectan a más de una tabla es el punto fuerte de los sistemas gestores de base de datos relacionales. Y precisamente es porque permiten relacionar registros de tablas diferentes de una manera consistente. Gracias al sistema relacional, un registro puede hacer referencia a otro permitiendo que el sistema gestor proteja los datos de operaciones accidentales y además facilita el cruce de datos durante las consultas.

Por ejemplo, mediante las restricciones entre tablas, un registro como el de la tabla persona puede apuntar a otra colección como empresa, reflejando la relación entre empresa y trabajador. Para ello, desde el registro de persona se debe indicar en qué empresa trabaja. Se entiende que una persona sólo trabaja en un momento determinado para una única empresa. Si esta relación se define a nivel de SQL, el sistema gestor evitará que se borre cualquier empresa que contenga trabajadores que le apunten. Si la empresa tiene un identificador único generado automáticamente, ya sea por auto incremento o por secuencia, el esquema sería de la siguiente manera:

-- MySQL y PostgreSQL
create table Empresa (
    id int primary key,
    nombre varchar(50)
);

create table Trabajador (
    id bigint primary key,
    nombre varchar(50) not null,
    empresa_id int,
    foreign key (empresa_id) references Empresa(id)
);

-- alternativamente se puede crear la FK
-- en un momento posterior en MySQL
alter table Trabajador
    add FOREIGN key fk_persona_empresa(empresa_id)
    references Empresa(id);

-- en un momento posterior en PostgreSQL
alter table Trabajador
    ADD CONSTRAINT fk_persona_empresa
    FOREIGN KEY (empresa_id) references Empresa(id);

Si la tabla destino tiene como identificador una clave compuesta por dos o más columnas, forzosamente la tabla que le apunta debe incluir como clave externa o foreign key todos los campos que conforman la clave primaria de la tabla apuntada. En ese caso, la combinación de los campos, tanto de la tabla origen como de la tabla destino, deberán ir separados por coma en los paréntesis de la definición de la clave externa.

-- Clave externa compuesta
alter table Trabajador
    ADD ...
    FOREIGN KEY (origen_1, origen_2, ...)
    references Empresa(destino_1, destino_2, ...);

El uso de claves primarias compuestas obliga a propagar todas las columnas a aquellas tablas que le apuntan, de ahí que sea preferible el uso de un único campo como identificador ya que producirá relaciones con otras tablas más sencillas.

Una vez que el vínculo está definido, ninguna operación de base de datos podrá borrar una empresa, siempre que haya al menos un trabajador que le apunte. A esta protección se le denomina integridad referencial y evita muchos problemas a la hora de mantener los datos consistentes en un sistema de información, y como se verá en los siguientes puntos, el usuario podrá obtener con mucha facilidad listados de trabajadores que estén asociados a una u otra empresa, usando la potencia que ofrece SQL para la realización de consultas.

Manipulación de los datos

Una vez definidas las tablas y relaciones que hay entre ellas, el siguiente paso es añadir información dentro de las colecciones recientemente creadas. A continuación, se describen las operaciones que pueden realizarse sobre éstas.

Inserción de datos

Para insertar datos en una colección, debe usarse la sentencia insert seguida del nombre de la tabla, la lista de campos y los valores para cada uno de los campos enumerados en la sentencia. De nuevo, la sintaxis puede variar ligeramente entre sistemas de base de datos.

-- MySQL y PostgreSQL
create table Inventario (
    id int primary key,
    nombre varchar(50) not null,
    cantidad int not null
);

insert into Inventario(id,nombre,cantidad)
values (1,'Radio FM', 3);

Los campos de texto deben contener los valores entre comillas simples. El número de columnas debe coincidir entre la lista de campos y la lista de valores. Y por supuesto, la posición de los valores debe ir acorde con el orden de los campos.

En los valores, pueden indicarse expresiones o llamadas a función SQL como la de convertir cadenas de texto a fechas, o llamar al siguiente valor de una secuencia.

-- MySQL
create table Usuario (
    id int not null auto_increment,
    nombre varchar(50) not null,
    alta date not null,
    primary key(id)
);

insert into Usuario(id, nombre, alta)
values (NULL,'rarnau', CURRENT_DATE());


-- PostgreSQL
create table Usuario (
    id int not null primary key,
    nombre varchar(50) not null,
    alta date not null
);

create sequence Usuario_seq;

insert into Usuario(id, nombre, alta)
    values (nextval('Usuario_seq'),'rarnau', NOW());

Si alguno de los campos es una clave externa a otra tabla, el valor indicado en el insert deberá contener uno existente en el campo identificador de la tabla destino. De no ser así, la integridad referencial lanzará un error indicando que el valor adjuntado en la sentencia no es correcto.

Modificación de datos

La modificación de registros se realiza con las sentencias update. Éstas deben indicar qué campos se desea actualizar, conteniendo un valor o una expresión, y normalmente la condición de filtrado que seleccionará los registros a modificar.

Por ejemplo, las siguientes líneas crean una columna nueva en la tabla de usuarios, añadiendo un valor por defecto a todos menos al registro identificado por 100, al que le fija un valor específico. Si el filtrado no encuentra ningún registro compatible, no se realiza ninguna modificación sobre los datos, sin producir error.

alter table Usuario add column notas varchar(200);

-- Modifica todos los usuarios
update Usuario set notas = 'sin notas';

-- Modifica un registro (si lo encuentra)
update Usuario set notas = 'super usuario' where id = 100;

Las actualizaciones se realizan sobre un conjunto de registros que cumplen las condiciones indicadas tras el where. Será muy habitual indicar el id como parte del filtrado para afectar únicamente a una fila de la colección, pero no siempre será así. Se pueden encadenar condiciones AND y OR para conseguir seleccionar los registros deseados. A modo de ejemplo, una actualización con un filtrado más sofisticado podría ser:

alter table Usuario add column departamento varchar(200);

-- MySQL
update Usuario set
    notas = 'expirados',
    departamento = 'IT'
    where id > 100
    and (alta < STR_TO_DATE('2010-01-01', 'YYYY-MM-DD')
    OR alta is null) ;

-- PostgreSQL
update Usuario set
    notas = 'expirados',
    departamento = 'IT'
    where id > 100
    and (alta < TO_DATE('2010-01-01', 'YYYY-MM-DD')
    OR alta is null);

La diferencia entre las llamadas es que MySQL necesita la función STR_TO_DATE para convertir una cadena de texto a fecha, mientras que la misma operación en PostgreSQL se realiza por TO_DATE. Fíjese que se ha aprovechado para hacer cambios en más de una columna, separando la lista de campos por coma.

Borrado de datos

De manera muy similar a la modificación está el borrado mediante delete, siendo su uso más simple puesto que sólo necesita saber sobre qué conjunto de elementos deberá aplicar el borrado. Los filtros pueden ser lo complejos que se necesiten, pero recuérdese que no se podrán eliminar registros que son apuntados por otros.

delete from Usuario where id > 1000;

Las eliminaciones no pueden deshacerse, así que conviene tener mucho cuidado con las operaciones queborran o modifican más de un registro. Cuando sea necesario realizar operaciones sobre varios registros, conviene probar que los filtros se aplican correctamente sobre el conjunto deseado de registros. Para ello, antes de ejecutar algún delete o update se deberá realizar una consulta con select usando los mismos criterios.

Consulta de datos

Las consultas o extracciones de datos de las tablas se realizan con las sentencias select y son otra gran herramienta que proporcionan las bases de datos SQL. Mediante las consultas se pueden obtener informes agrupados, paginados, con cruce de datos entre tablas, ordenados y cálculos complejos sobre prácticamente todas las columnas que gestione el sistema gestor. Las construcciones de sentencias select son muy amplias, sólo se describirán los conceptos básicos para poder realizar aplicaciones de gestión. Pero el lector debe saber que hay toda una gramática SQL que permite realizar operaciones muy complejas sobre los datos almacenados. En definitiva, para realizar una extracción de datos la sintaxis es la siguiente:

SELECT t1.campo1, t2.campo2, ...
FROM Tabla t1, Tabla t2, ...
WHERE t1.campo3 = ...
AND t2.campo3 > ...
AND (t1.campo4 = ... OR t2.campo5 = ...)
ORDER BY t1.campo6 ASC, t2.campo7 DESC;

Lo primero que aparece es la palabra reservada select que indica que se comienza a especificar una consulta. El siguiente segmento es la lista de campos que se desea que aparezcan en los resultados. Se continúa con la cláusula from que es la lista de una o más tablas separadas por coma y a la que se le aplica un alias para hacer referencia a las columnas de cada tabla. Es bastante habitual que dos o más columnas tengan campos que se denominen igual y es a través de los alias que el sistema gestor sabe exactamente qué columna debe utilizar.

A continuación, vienen los filtros tras el where. En ellos, se pueden especificar los campos de cualquier tabla citada en el from acompañados de expresiones de comprobación como que sea igual a X, o mayor que Y, o diferente de NULL, etc… Los filtros pueden anidar expresiones AND y OR y deben usarse los paréntesis para deshacer las ambigüedades sobre qué operación aplicar primero. Y por último, se indica la lista de columnas por las que ordenar y si ésta debe ser ascendente o descendente. Las columnas del criterio de ordenación deben coincidir con columnas que aparezcan en la lista de resultados, es decir tras la palabra reservada select.

Una consulta puede contener muchos más elementos que los que se han citado aquí, pero esta definición es un buen punto de partida para tomar contacto con las consultas SQL.

Para ir incrementando la complejidad empezando por una consulta sencilla, la operación más simple que puede hacerse sobre la tabla de usuarios es la siguiente:

SELECT * from Usuario;

Esta operación de consulta no tiene filtrado, ni ordenación, ni selección de campos. Como resultado el sistema gestor ofrecerá todas las columnas y todos los registros de la tabla de usuarios. Si la tabla tuviera millones de registros, esta operación podría penalizar el rendimiento del sistema gestor con facilidad. Así que es conveniente indicar algunos filtros, como por ejemplo filtrar por el identificador.

SELECT u.nombre, u.alta
from Usuario u where u.id = 1;

Esta última consulta como máximo dará una sola tupla de resultados. Y además, no devolverá toda la información almacenada para ese usuario, ya que sólo se le ha exigido que ofrezca el nombre y la fecha de alta. Si se necesitara saber cuáles son los últimos usuarios dados de alta, se podrían ordenar los resultados con:

SELECT u.nombre, u.alta
from Usuario u
order by u.alta desc;

Por tanto, el programador puede controlar los resultados que desea obtener a base de incluir unas u otras cláusulas aceptadas por la operación de selección.

Paginación de resultados

Para evitar la solicitud de informes demasiado pesados, conviene limitar la cantidad de registros que se desean obtener por cada ejecución de la consulta. Si además, el sistema gestor ofrece la posibilidad de desplazarse en la lista de resultados, fácilmente se pueden obtener consultas paginadas que ofrezcan resultados más razonables desde el punto de vista de consumo de recursos informáticos.

La paginación de consultas con MySQL se realiza incluyendo la cláusula limit <offset>, <rowcount> al final del select, permitiendo indicar cuántas filas saltar y cuántos registros obtener. Para PostgreSQL estos parámetros se podrán añadir mediante la inclusión de la cláusula offset <offset> limit <limit>.

-- MySQL
SELECT \* from Usuario u
    order by u.alta desc
    limit 50, 10;

-- PostgreSQL
SELECT \* from Usuario u
    order by u.alta desc
    offset 50 limit 10;

En ambos casos, la consulta se salta los 50 primeros registros y tiene en consideración los siguientes 10 para mostrar en la lista de resultados. Cuando desde la aplicación se implementen listados paginados, se debe calcular el offset como el número de página a mostrar por el tamaño de página, obteniendo el número de registros a saltar. La segunda cifra ha de contener el tamaño de las páginas.

-- MySQL
SELECT ...
    limit <pagina_solicitada * tamanyo_pagina>,
    <tamanyo_pagina>;

-- PostgreSQL
SELECT ...
    offset <pagina_solicitada * tamanyo_pagina>
    limit <tamanyo_pagina>;

El código anterior supone que la primera página es la 0. Además, en las aplicaciones suele mostrarse el total de páginas que hay, incluso el total de páginas de registros coincidentes tras aplicar algún filtro. Así que desde la aplicación se terminan ejecutando siempre dos consultas: la primera, que obtiene los registros de la página que el usuario ha solicitado, y la segunda, que obtiene el número de registros coincidentes, teniendo presente los filtros, para obtener el total de páginas. Para poder contar registros se necesitan realizar operaciones de agrupado sobre las consultas.

Agrupación de resultados

No siempre será necesario obtener la información de las filas tal cual están en la base de datos. En algunas ocasiones, se tendrá que contar cuántos registros hay coincidentes por ejemplo, para mostrar un listado paginado al usuario. Otras veces, se necesitarán agrupar resultados para obtener, por ejemplo, el total de facturas emitidas por área de servicio. Para este fin las bases de datos SQL incluyen funciones de cálculo como máximo o MAX, mínimo MIN, media AVG o simplemente contar líneas COUNT. Para ver el uso de uno de ellos, imagínese que se quiere contar el total de registros que hay en la tabla de usuarios:

SELECT count(u.id) from Usuario u;

La consulta anterior sólo devuelve una fila correspondiente con el conteo de valores de la columna u.id. Al no haber filtrado, el resultado es la cantidad de usuarios que hay en la tabla. Si se hubiese indicado algún filtro, la consulta habría dado el total de registros que cumplan la selección.

El siguiente ejemplo muestra cómo debe usarse la suma, pero no para sumar todos los registros, sino para sumar aquellos que tengan algo en común. Por ejemplo, para sumar las ventas por tipo de producto. Como en este caso se necesitan resultados parciales, no queda más remedio que usar un agrupador en la operación de suma:

create table Ventas (
    id int primary key,
    item_id int not null,
    precio decimal(6,2) not null
);

SELECT v.item_id,
    sum(v.precio) as total
    from Ventas v
    group by v.item_id;

Se entiende que item_id es una clave externa a una tabla donde están alojados todos los elementos que están puestos a la venta. La anterior consulta devuelve el identificador de cada ítem diferente vendido, juntamente con la suma de los importes que han acumulado las ventas por ítem. Así es fácil saber qué elemento es el que ha aportado más ingresos sobre el total.

Sin embargo, aunque el informe obtenido es muy útil, el usuario debe tener en su cabeza qué ítem es cada uno de ellos según su identificador. Le será fácil perderse si hay varios cientos de elementos diferentes vendidos. Sería interesante mostrar el nombre de los elementos al lado de los totales. Como el nombre de los elementos está en la tabla de ítems, se debe combinar la consulta para utilizar campos, tanto de la tabla de ventas como de la tabla de inventario, mediante intersección entre tablas del siguiente punto.

Intersección entre tablas

La intersección entre tablas permite combinar columnas en consultas que proceden de tablas diferentes, como por ejemplo mostrar el nombre de los productos vendidos, juntando la tabla de inventario y la de ventas. Se debe tener presente que en SQL se trata de tener el mínimo de información duplicada en las tablas. De esta manera, la tabla de ventas sólo debe contener el identificador del ítem y ninguna característica más del ítem, si éstas son invariables entre diferentes ventas. Así que el nombre del ítem estará únicamente en la tabla de inventario.

Siguiendo con el ejercicio anterior, en donde se solicita listar los productos más vendidos, se deben combinar ambas tablas para producir un informe útil para el usuario, teniendo el esquema siguiente de inventario y ventas:

create table Ventas (
    id int primary key,
    item_id int not null,
    fecha date not null,
    precio decimal(6,2) not null
);

create table Inventario (
    id int primary key,
    nombre varchar(50) not null,
    cantidad int not null
);

-- MySQL
alter table Ventas
    add FOREIGN key fk_venta_inventario(item_id)
    references Inventario(id);

-- PostgreSQL
alter table Ventas
    ADD CONSTRAINT fk_venta_inventario
    FOREIGN KEY (item_id) references Inventario(id);

-- Creando algunos registros

insert into Inventario(id,nombre,cantidad)
    values(1,'Radio FM',1);
insert into Inventario(id,nombre,cantidad)
    values(2,'Radio AM',1);
insert into Inventario(id,nombre,cantidad)
    values(3,'Radio UHF',1);

insert into Ventas(id,item_id,fecha,precio)
    values (1,1,'2019-08-01',3.5);
insert into Ventas(id,item_id,fecha,precio)
    values (2,1,'2019-08-02',4);
insert into Ventas(id,item_id,fecha,precio)
    values (3,2,'2019-08-03',2);

Como nota, destacar que la conversión de cadenas de texto a fechas es automática si la fecha está escrita siguiendo el formato YYYY-MM-DD tanto en MySQL como en PostgreSQL.

Una vez creadas las tablas y habiendo insertado algunos datos, la consulta que da información del identificador y nombre del ítem, juntamente con el total de ventas realizadas durante el mes de agosto del 2019 para cada uno de ellos, es el siguiente:

select i.id as item_id,
    i.nombre as item_nombre,
    sum(v.precio) as total
    from Inventario i, Ventas v
    where v.item_id = i.id
    and v.fecha >= '2019-08-01'
    and v.fecha <= '2019-08-31'
    group by i.id
    order by total desc;

Para que el sistema gestor pueda llevar a cabo la intersección de ambas tablas, es necesario indicar en la consulta qué campo hará de enlace entre los registros de una tabla y otra. Para ello se ha definido que la comparación de los identificadores de los ítems de las ventas, han de ser iguales a los identificadores de los mismos ítems en la tabla de inventario con v.item_id = i.id. Una expresión de este tipo siempre será necesaria para establecer las conexiones entre campos que son claves externas de las tablas relacionadas.

Aparte de los filtros de fechas, el otro punto importante es que se requieren los resultados parciales de la suma de v.precio según cada ítem diferente, de ahí la operación de agrupación group by i.id.

La intersección de las tablas como se ha realizado en el ejemplo se denomina inner join, debido a que debe existir una coincidencia de dos registros, uno en cada extremo, para que se muestre una fila en la lista de resultados. Si el sistema no encontrara el registro relacionado ya sea a la izquierda o a la derecha de la expresión, no se ofrecería como resultado la línea correspondiente.

Esta forma de escribir un inner join se llama implícita, puesto que el sistema gestor está obligado a conjugar las tablas para poder cumplir una de las condiciones del where, aquella que compara el identificador de una con la clave externa de la otra. Pero existe otra manera de forzar este hecho de una forma más directa, usando las palabras inner join de forma explícita en la sentencia. La consulta anterior de forma explícita quedaría como sigue:

-- reescritura con inner join
select i.id as item_id,
    i.nombre as item_nombre,
    sum(v.precio) as total
    from Inventario i
    inner join Ventas v
    on v.item_id = i.id
    where v.fecha >= '2019-08-01'
    and v.fecha <= '2019-08-31'
    group by i.id
    order by total desc;

Fíjese cómo se ha descrito la conjunción entre tablas usando inner join <tabla> on <comparación>. Esta es la manera correcta de hacer intersecciones, pero como se ha comentado, este tipo de relaciones requiere que existan registros a ambos lados de la relación, provocando que el ítem 3 de Radio UHF no aparezca.

Sin embargo, a veces es útil que se muestre alguno de los extremos aunque no se encuentre el registro relacionado. En ese caso, las columnas del registro no encontrado valdrían NULL a menos que en la construcción del select se indique lo contrario. Este caso aparecería por ejemplo, si se desea que se muestren todos los ítems, aunque algunos de ellos no hayan tenido ventas. Para ello, se puede hacer otro tipo de conjunción denominado left o right join durante la consulta. Si se cambia la anterior consulta por un left join, la tabla alojada a la izquierda de la expresión tomará el control forzando a que aparezcan los registros que contiene.

-- reescritura con inner join
select i.id as item_id,
    i.nombre as item_nombre,
    sum(v.precio) as total
    from Inventario i
    left join Ventas v
    on v.item_id = i.id
    group by i.id
    order by total desc;

El resultado de la operación left join es la tabla siguiente:

El uso de inner o left join dependerá del resultado que se necesite en cada caso, pero suele ser interesante conocer estos conceptos, sobre todo cuando no se obtienen los resultados según las tablas usadas y los filtros aplicados. Es bastante probable que si los registros esperados no aparecen, el motivo sea por la realización de un inner join.

Las opciones para realizar consultas son muy diversas, habiendo dejado fuera de este texto otras muchas opciones interesantes para sacar el máximo partido a las sentencias SQL. Aunque con lo explicado hay más que suficiente para realizar una simple aplicación de gestión, el dominio de SQL por parte del programador es un requisito más que básico y deseable para cualquier persona que opte a participar en un proyecto profesional. Puede ampliar más conocimientos sobre el área en multitud de recursos disponibles en Internet como:

• Amplia tus conocimientos de SQL en W3Schools

Transacciones

Los sistemas relacionales SQL también incluyen la posibilidad de ejecutar un conjunto de sentencias transaccionalmente, es decir, que o se ejecutan todas correctamente o no se ejecuta ninguna. Esta funcionalidad es necesaria cuando se necesita que la base de datos sea consistente la mayor parte de tiempo posible.

Imagínese por un momento que se tratan de ejecutar algunas actualizaciones por lotes en la base de datos y de repente alguna de ellas contiene un error o no supera alguna restricción definida en la tabla. Eso implicaría que algunas sentencias se han podido ejecutar y otras no. Al ser así, no se tiene la seguridad de cómo han quedado los datos, dificultando enormemente la identificación de qué pasos se han de realizar para dejar la base de datos en su estado anterior, en donde la información era correcta en su totalidad. Más aún, si ocurre en un entorno concurrente con varias peticiones simultáneas. De ahí que es deseable que las bases de datos incorporen la transaccionalidad. Si el sistema aporta esta funcionalidad, la base de datos deshará automáticamente los cambios realizados desde el principio de la transacción.

Para ejecutar las sentencias en una transacción se debe indicar el principio y fin de ésta. El inicio se denota con la sentencia begin, mientras que el final depende de si ha ido todo correctamente con commit. En cambio, si se ha detectado algún error y se desea que ningún cambio quede persistente, se deshará todo con rollback.

Puede comprobarse el funcionamiento con una simple actualización que se deshace al finalizar.

begin;
delete from Ventas;
rollback;
-- El borrado no se realiza
select * from Ventas;

Los sistemas transaccionales ofrecen una capa de seguridad que juntamente con las restricciones relacionales producen aplicaciones sólidas y confiables por muchas actualizaciones que se realicen. Conviene tenerlo presente a la hora de escoger un sistema gestor u otro para aplicaciones críticas.

Ejecución de código en base de datos

Las bases de datos SQL permiten otras funcionalidades que pueden ser útiles en algunos escenarios. La primera de ellas es la creación de disparadores que pueden ejecutar lógica procedural al provocarse ciertas situaciones, cómo una inserción, modificación o borrado de una determinada tabla. La segunda, en la misma línea, es crear procedimientos y funciones que contienen lógica de aplicación en forma de instrucciones de SQL.

Disparadores PL/SQL

A pequeños programas que se ejecutan cuando ocurre alguna manipulación sobre un conjunto de datos se les conoce como triggers. Suelen utilizarse para controles de auditoría o comprobaciones más sofisticadas que las que pueden hacerse con restricciones de tipo check antes o después de inserciones, modificaciones o borrados.

A modo de ejemplo, se incluye un posible trigger para que el lector tenga una ligera idea de que existen y de cómo puede realizar alguno en un momento posterior. La sintaxis utilizada viene definida en PL/SQL de Procedural Language.

Por ejemplo, se introduce un trigger capaz de comprobar los importes antes de una actualización con PL/SQL en MySQL para que este número esté siempre entre 0 y 100:

CREATE TABLE account(
    acct_num INT,
    amount DECIMAL(10,2)
);

CREATE TRIGGER upd_check
    BEFORE UPDATE ON account
        FOR EACH ROW
    BEGIN
    IF NEW.amount < 0 THEN
        SET NEW.amount = 0;
    ELSEIF NEW.amount > 100 THEN
        SET NEW.amount = 100;
    END IF;
END;

El siguiente ejemplo, algo más complejo, representa unas validaciones que pueden lanzar error en PostgreSQL mediante un disparador que es ejecutado antes de cada inserción o actualización. Las siguientes líneas comprueban que el salario de los trabajadores (registros de la tabla emp) sea no nulo y mayor que cero.

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION
'% cannot have null salary', NEW.empname;
END IF;

-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION
'% cannot have a negative salary', NEW.empname;
END IF;

-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Los triggers pueden ser útiles cuando hay varias aplicaciones que consumen datos de una misma base de datos. Idealmente, cada aplicación debería aplicar las reglas de validación y comprobaciones necesarias. Pero para asegurar la integridad de los datos, debe ser la base de datos como pieza central que realice las comprobaciones, evitando que se escape alguna comprobación entre sistemas.

Procedimientos almacenados PL/SQL

Utilizando el mismo lenguaje de PL/SQL, se añadió a los sistemas gestores la capacidad de definir procedimientos y funciones que pueden ser invocados mediante sentencias SQL. Éstos pueden contener lógica como la mostrada en los triggers con bucles, condicionales y ejecución de operaciones SQL sobre tablas. Todo ello es ejecutado por la base de datos al invocar alguna función o procedimiento.

En principio, PL/SQL diferencia las funciones de los procedimientos siguiendo el criterio de que las funciones están pensadas para devolver uno o más valores dados unos parámetros que pueden ser ejecutados en sentencias SQL como max o avg. Sin embargo, de los procedimientos no se espera un resultado, éstos son utilizados normalmente para ejecutar sentencias SQL y realizar cambios o construir informes de la base de datos.

El siguiente procedimiento realiza la cuenta de las filas de la tabla ventas en MySQL y lo guarda en la variable indicada @a

delimiter //

CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(\*) INTO param1 FROM Ventas;
END//

delimiter ;

CALL simpleproc(@a);
SELECT @a;

Por otro lado, en el siguiente fragmento de código se construye una función que acepta un parámetro de entrada de tipo texto y de ancho fijo 20, devolviendo una cadena de 50 letras concatenándole un prefijo y sufijo.

CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');

SELECT hello('world');

Estos procedimientos son ejecutados por y en el sistema gestor de base de datos. La base de datos compila este código y genera código binario que ejecutará eficientemente. Así que los sistemas gestores son herramientas sofisticadas de software que abarcan un gran número de funcionalidades. Esto es sólo una pequeña muestra de las posibilidades que ofrecen. De vez en cuando conviene revisar las novedades que incluyen, puesto que siguen siendo importantes piezas dentro de los servicios ofrecidos por el departamento de Tecnologías de la Información en continua evolución.

Las sentencias que escribe el programador para realizar funciones y procedimientos en bases de datos, deben tener el mismo tratamiento que el que se daría al código Java de un proyecto. Y como todo código fuente, debe estar controlado por un sistema de control de versiones y contener un plan de versionado y despliegue de modificaciones en los diferentes entornos de pruebas y producción.

Conclusiones

SQL es el lenguaje fundamental para el manejo de datos seguros y fiables. Si deseas dominar este poderoso lenguaje y mejorar tus habilidades en bases de datos, ¡visita la web de Arteco Consulting SL ahora! Nuestros expertos pueden guiarte a través de los conceptos básicos de SQL y llevarte al siguiente nivel en el manejo de datos. ¡No pierdas la oportunidad de impulsar tu carrera en el desarrollo web y la programación con nosotros!