Esta sección sirve de guía respecto al lenguaje SQL para consultas DDL y DML.

El lenguaje de definición de datos es el que permite crear la estructura de la base de datos, y las sentencias que usaremos se indican a continuación:

Es la sentencia utilizada para la creación de un objeto (base de datos, tabla, usuario, vista, procedimiento, . . .) en una Base de Datos.

Para crear una Base de Datos:

CREATE DATABASE [IF NOT EXISTS] <nombre_base_de_datos>

La sintaxis para la creación de una tabla es la siguiente:

CREATE TABLE [IF NOT EXISTS] <nombre_tabla>
(
   <nombre_columna1>  <tipo_dato>  <restricciones>,
   <nombre_columna2>  <tipo_dato>  <restricciones>,
   ................................
)
 
-- Ejemplo
CREATE TABLE asignaturas(
   id INT PRIMARY KEY,
   nombre VARCHAR(20),
   departamento VARCHAR(20),
   id_curso INT,
   FOREIGN KEY (id_curso) REFERENCES cursos(id)
);

Es la sentencia utilizada para eliminar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de Datos.

La sintaxis para la eliminación de tablas es la siguiente:

DROP TABLE [IF EXISTS] <nombre_tabla>

Y para eliminar una Base de Datos:

DROP DATABASE [IF EXISTS] <nombre_base_de_datos>

Es la sentencia utilizada para modificar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de Datos.

La sintaxis para modificar una tabla es la siguiente:

    ALTER TABLE <nombre_tabla>
        [ ADD <definicion_columna> ]
        [ CHANGE <nombre_columna> <definicion_columna> ]
        [ DROP COLUMN <nombre_columna> ]
        [ ADD CONSTRAINT <restriccion> ]

Ejemplos:

ALTER TABLE alumnos ADD PRIMARY KEY(id);
ALTER TABLE alumnos DROP PRIMARY KEY;
ALTER TABLE alumnos ADD FOREIGN KEY (id_curso) REFERENCES cursos(id);
ALTER TABLE alumnos DROP COLUMN nombre;
ALTER TABLE alumnos MODIFY nombre VARCHAR(30) NOT NULL;

La usamos para cambiar de nombre a una tabla.

RENAME TABLE nombre_anterior TO nombre_nuevo;
 
-- también con ALTER TABLE
 
ALTER TABLE tabla_anterior RENAME nueva_tabla;

Permite realizar la conexión con una Base de Datos de MySQL.

    USE <nombre_base_de_datos>

Este tipo de datos permite almacenar cadenas de texto fijas (CHAR) o variables (VARCHAR).

El tipo CHAR permite almacenar cadenas de caracteres de longitud fija entre 1 y 255 caracteres. La longitud de la cadena se debe especificar entre paréntesis en el momento de la declaración (cadena CHAR(25)).

Por otro lado, el tipo VARCHAR permite almacenar cadenas de caracteres variables de hasta 4.000 caracteres. La declaración del tipo VARCHAR es similar a la de un tipo CHAR (cadena VARCHAR(25)). La principal y única diferencia entre estos dos tipos, es que el tipo CHAR declara una cadena fija de la longitud que se especifica mientras que en la declaración de un tipo VARCHAR lo que se especifica es un tamaño máximo, la cadena sólo ocupará el tamaño necesario para almacenar el dato que contenga (hasta llegar al máximo). En cualquier caso, no es posible almacenar cadenas de mayor tamaño al especificado en su declaración, puesto que el SGBD truncará el valor almacenándose sólo hasta la longitud establecida.

El tipo TEXT permite almacenar cadenas de caracteres de hasta varios GB de longitud. Sólo se recomienda su uso para almacenar textos realmente grandes, puesto que presenta ciertas restricciones, aunque algunas pueden variar dependiendo del SGBD que se utiliza:

• Sólo se puede definir una columna TEXT por tabla
• No se pueden establecer restricciones en columnas de este tipo
• No se permite su utilización en ciertas cláusulas

Para la representación de tipos de datos numéricos. Los tipos más utilizados son BIT, TINYINT, INT, BIGINT, FLOAT y DOUBLE, para la representación de números enteros de menor o mayor tamaño, y para números en coma flotante de menor o mayor precisión, respectivamente.

En ocasiones el rango de los valores negativos resultará prescindible (claves numéricas, valores de dinero, cantidades, . . .) por lo que será posible ampliar el rango positivo de un tipo numérico añadiendo la restricción UNSIGNED tras definir el tipo de éste.

        id INT UNSIGNED

Para el caso de los números de coma flotante conviene tener en cuenta los problemas de precisión ¹⁾ que existen con estos tipos de datos.

Los tipos más utilizado para almacenar valores de fechas (DATE) o fechas con hora (DATETIME). Por defecto el formato más utilizado es DD/MM/YY ó DD/MM/YYYY.

También se puede usar el tipo TIMESTAMP para almacenar una marca de tiempo (fecha y hora). Además, permite el uso de la constante CURRENT_TIMESTAMP en la definición de la columna al definirle un valor por defecto cuando se crea la tabla.

Permite almacenar valores lógicos Verdadero/Falso o Sí/No. boolean realmente se define la columna como del tipo TINYINT(1), que simplemente almacena los valores 0 y 1 para indicar los valores lógicos Verdadero y Falso, respectivamente. Así, podremos utilizar los valores TRUE ó FALSE o directamente asignar 1 ó 0 para asignar valor.

Las restricciones se pueden establecer, o no, a las columnas de cada tabla para forzar a que los datos almacenados en ellas cumplan una serie de condiciones, con la finalidad de que la información sea más correcta. Por ejemplo, podemos obligar a que un campo donde almacenamos el DNI de una persona tenga una longitud mínima, o bien un campo donde almacenamos la categoría de un equipo de fútbol, sólo pueda tomar unos determinados valores predefinidos (benjamín, juvenil, cadete, . . .) o bien podemos hacer que un campo no pueda repetirse, por tratarse de un valor único (DNI, NSS, teléfono, email, . . .).

Hay que tener en cuenta que, por lo general, las restricciones se definen en línea con la definición del campo (tal y como se muestra en la sintaxis de la sentencia de CREATE TABLE, pero de forma opcional también pueden ser definidas por separado justo debajo de la definición de todos los campos de la tabla.

Una clave primaria dentro de una tabla, es una columna o conjunto de columnas cuyo valor identifica unívocamente a cada fila. Debe ser única, no nula y es obligatoria. Como máximo podremos definir una clave primaria por tabla y es muy recomendable definirla.

Para definir una clave primaria utilizamos la restricción PRIMARY KEY.

CREATE TABLE personas(
   dni VARCHAR(9) PRIMARY KEY,
   ...
);

Y si lo hacemos al final de la definición de las columnas, quedaría así:

CREATE TABLE personas(
   dni VARCHAR(9),
   nombre VARCHAR(10),
   PRIMARY KEY (dni)
);

Hay que tener en cuenta que a la hora de definir claves primarias compuestas (la componen 2 ó más campos de la tabla), ésta deberá ser definida forzosamente tras la definición de los campos involucrados, siguiendo esta sintaxis

CREATE TABLE personas(
   dni VARCHAR(9),
   nombre VARCHAR(10),
   apellidos VARCHAR(20),
   PRIMARY KEY (nombre, apellidos)
);

Especialmente útil en el caso de aquellas columnas que se definan como claves primarias de cada tabla, resulta añadir la restricción de campo autonumérico, siempre y cuando ésta sea una columna de tipo entero. De esa manera será el SGBD el encargado de asignarle valor de forma automática, siempre asignando un valor entero de forma secuencial a medida que se van insertando las filas en dicha tabla.

La forma de definirlo es añadiendo la restricción AUTO_INCREMENT en la definición de la columna que se ha definido como clave primaria:

id INT PRIMARY KEY AUTO_INCREMENT

Una clave ajena está formada por una o varias columnas que hacen referencia a una clave primaria de otra o de la misma tabla. Se pueden definir tantas claves ajenas como sea necesario (no hay límite) en cada tabla. El valor de la columna o columnas que son clave ajena será el valor de la clave primaria de la tabla a la que hacen referencia (integridad referencial). Así, a la hora de definir una clave ajena, deberemos indicar con la cláusula REFERENCES la tabla y el campo clave primaria a la que ésta hace referencia .

Las claves ajenas se deben definir después de la definición de los campos de la tabla:

CREATE TABLE asignaturas(
   id INT PRIMARY KEY AUTO_INCREMENT,
   nombre VARCHAR(20),
   departamento VARCHAR(20),
   id_curso INT,
 
   FOREIGN KEY (id_curso) REFERENCES cursos (id)
);

El ejemplo anterior haría referencia a una definición de tabla como la siguiente:

CREATE TABLE cursos(
   id INT PRIMARY KEY AUTO_INCREMENT,
   horario VARCHAR(20),
   . . .
);

Si una tabla tiene diferentes claves ajenas, se definen del mismo modo:

   id_curso INT,
   id_profesor INT,
 
   FOREIGN KEY (id_curso) REFERENCES cursos (id),
   FOREIGN KEY (id_curso) REFERENCES cursos (id)

En caso que la clave ajena haga referencia a una clave primaria compuesta:

FOREIGN KEY (id_curso, id_aula) REFERENCES cursos(id_curso, id_aula)

En cualquiera de los casos hay que tener en cuenta que habrá que definir primero el campo con el tipo de dato correcto (el mismo que dicho campo en la tabla donde aparece como clave principal) y luego la propia definición de dicho campo como clave ajena.

Habrá que tener en cuenta que mientras que un campo definido como clave ajena haga referencia a un campo definido como clave primaria, la columna de la segunda tabla no podrá ser eliminada hasta que no lo haga la columna que le hace referencia (integridad referencial). Para evitar estos problemas (aunque no siempre es un problema) es posible definir la restricción de clave ajena añadiendo la cláusula ON DELETE o bien ON UPDATE para el caso de una actualización. De esa manera, cuando se vaya a eliminar o actualizar una fila a cuya clave primaria se haga referencia, podremos indicar a MySQL que operación queremos realizar con las filas que le hacen referencia:

• RESTRICT: Se rechaza la operación de eliminación/actualización
• CASCADE: Realiza la operación y se elimina o actualiza en cascada en las filas que hacen referencia
• SET NULL: Realiza la operación y fija a NULL el valor en las filas que hacen referencia
• NO ACTION: Se rechaza la operación de eliminación/actualización, como ocurre con la opción RESTRICT

Si no especifico ningun tipo de acción, se tomará CASCADE por defecto para ambas operaciones (UPDATE y DELETE).

-- No podré eliminar cursos si hay asignaturas referenciandolos:
FOREIGN KEY (id_curso) REFERENCES cursos (id) ON DELETE RESTRICT
 
-- Si modifico el valor de la clave primaria de un curso, se actualiza la clave ajena
-- Si elimino un curso, se pondrá a NULL el valor de la clave ajena de las asignaturas
FOREIGN KEY (id_curso) REFERENCES cursos (id) ON UPDATE CASCADE ON DELETE SET NULL

Para definir claves ajenas en MySQL habrá que tener en cuenta algunas consideraciones:

• Una columna clave ajena nunca podrá ser AUTO_INCREMENT, ya que rompería el propósito de la relación.
• La columna deberá ser del mismo tipo (y atributos) que la columna a la que se referencia.
• La columna deberá ser un índice. A partir de Mysql 8 y versiones compatibles de MAriaDB, se hace automáticamente.
• Si la columna se define como obligatoria (NOT NULL) no podrá contener la claúsula (SET NULL) para los casos de borrado (ON DELETE) o actualización (ON UPDATE).

Esta restricción obliga a que se le tenga que dar valor obligatoriamente a una columna. Por tanto, no podrá tener el valor NULL. Se utiliza la palabra reservada NOT NULL.

    apellidos VARCHAR(250) NOT NULL

Se puede definir el valor que una columna tomará por defecto, es decir, si al introducir una fila no se especifica valor para dicha columna. Se utiliza la palabra reservada DEFAULT.

fecha  TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
nombre VARCHAR(250) DEFAULT 'Sin nombre'

De forma más genérica, podemos forzar a que los valores de determinados campos de la tabla cumplan una ciertas condiciones. En caso contrario no se permitirá la inserción de esa fila en dicha tabla.

Lo habitual es definir una columna como de tipo enumeración (ENUM en MySQL) si queremos indicar que solamente una serie de valores (definidos) son válidos:

curso   ENUM ('0', '1', '2'),
horario ENUM ('mañana', 'tarde', 'noche'),

La restricción UNIQUE evita valores repetidos en una misma columna. Al contrario que ocurre con la restricción PRIMARY KEY, la restricción de valor único se puede aplicar a varias columnas de una misma tabla y admite el valor NULL. Con respecto a esta última consideración, conviene saber que si una columna se define como UNIQUE, sólo una de sus filas podrá contener el valor NULL

email VARCHAR(100) UNIQUE

La forma más habitual de trabajo a la hora de lanzar órdenes en SQL sobre un SGBD relacional como MySQL es crear ficheros por lotes de órdenes SQL, lo que se conoce como scripts SQL, donde podemos escribir todas las sentencias SQL que queremos ejecutar una detrás de otra separadas por el carácter ;.

Existe la posibilidad de añadir comentarios al código según la siguiente sintaxis:

    -- Esto es un comentario y MySQL no lo ejecuta
    /* Esto también es un comentario y 
       tampoco se ejecuta */

Por ejemplo, para la creación de una nueva Base de Datos y sus tablas podríamos preparar un script SQL como el siguiente:

CREATE DATABASE IF NOT EXISTS pagina_web;
USE pagina_web;
 
CREATE TABLE IF NOT EXISTS usuarios (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    . . .
    . . .
);
CREATE TABLE IF NOT EXISTS productos (
    . . .
    . . .
);
. . .
. . .

Una vez creado el script podremos lanzar su ejecución sobre MySQL y se ejecutarán todas las sentencias contenidas en él de forma secuencial. Es una forma muy útil de crear scripts para la creación de una Base de Datos y todas sus tablas y restricciones y también para crear scripts de actualización o parcheo de una Base de Datos existentes de forma que se incluyan todas las sentencias SQL que actualicen o arreglen los problemas que actualmente pueda haber (añadir una nueva tabla, eliminar un campo, añadir una nueva restricción, . . .).

Para estos casos a veces resulta útil desactivar las claves ajenas, realizar algunas operaciones sobre tablas que puedan tener relaciones con otras y volver a activarlas. De esa manera es posible realizar ciertas operaciones sin que las reglas de validación de la integridad referencial lancen ningún error.

-- Desactivar claves ajenas
SET FOREIGN_KEY_CHECKS = 0;
. . .
// Realizar algunos cambios en la estructura y datos de la Base de Datos
. . .
-- Activar claves ajenas
SET FOREIGN_KEY_CHECKS = 1;

• Crear el script a partir del modelo relacional

• Cómo crear un script en SQL

1. Utilizar notación snake_case para todos los identificadores (nombre de la base de datos, nombres de tablas, nombres de columnas, . . .). Y siempre en minúscula
2. No utilizar acentos, el caracter ñ ni otros caracteres extraños (|@#…) para nombres de bases de datos, tablas, columnas o cualquier otro elemento
3. Escribir las palabras reservadas del lenguaje SQL en mayúsculas
4. Todas las tablas tendrán un campo clave primaria cuyo nombre será id (definir como id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT)
5. Las claves ajenas indicarán la tabla a la que hacen referencia (en singular) como parte de su nombre. Por ejemplo: id_usuario si es una clave ajena de una tabla usuarios. Si en una tabla hay dos claves ajenas que apuntan a la misma tabla, añadiremos algo al nombre para distinguirla (id_usuario_emisor e id_usuario_receptor, por ejemplo)
6. Se recomienda que los nombres de las tablas sean en plural (users mejor que user, orders mejor que order)
7. Antes de definir un tipo de dato como numérico, comprobar si realmente voy a operar con él como tal
8. Cuidado con los campos contraseña. Realmente nunca se guarda tal cual sino como un hash utilizando algún algoritmo, por lo que la longitud real es mayor (la longitud de un hash creado con SHA1 es de 40 caracteres y con SHA2 hasta 128)

Dentro de SQL, lenguaje DML (Data Manipulation Language, Lenguaje de Manipulación de Datos) engloba aquellas sentencias que nos permiten manipular la información que almacenamos en las Bases de Datos.

Principalmente existen 4 instrucciones: insertar datos en una tabla (INSERT), modificar esos datos (UPDATE), eliminarlos (DELETE) y consultarlos (SELECT).

Todos los ejemplos sobre estas sentencias DML se han escrito tomando como referencia la Base de Datos reservas que se proporciona junto con este documento. También pueden aparecer algunos ejemplos tomando como referencia las Bases de Datos de ejemplos oficiales de MySQL

A continuación se muestran el modelo entidad-relación y relacional de esta Base de Datos de prueba:

pistas (#id, codigo, tipo, precio, -id_polideportivo)
pistas_abiertas(#-id_pista, operativa, precio, fecha_ultima_reserva, proxima_revision)
pistas_cerradas (#-id_pista, fecha_clausura, motivo)
polideportivos (#id, nombre, direccion, ciudad, extension)
servicios_polideportivo(#id, servicio, -id_polideportivo)
reservas (#id, fecha_reserva, fecha_uso, precio, -id_pista)
usuarios (#id, dni, nombre, apellidos, email, ciudad, fecha_nacimiento, descuento, fecha_alta)
usuario_usuario (#(-id_usuario, -id_amigo))
usuario_reserva (#(-id_usuario, -id_reserva), asiste)

La base de datos puede descargarse desde este enlace.

La inserción de nuevos registros a una tabla se efectúa con la sentencia INSERT, que tiene el siguiente formato:

INSERT INTO nombre_tabla [ '('columnas')' ] 
{ VALUES '(' { valores } ')',} | consulta

Veamos algunos ejemplos:

• Para insertar una fila en una tabla

INSERT INTO usuarios (dni, nombre, apellidos, email, fecha_nacimiento)
VALUES ('123456789A', 'Antonio', 'García', 'agarcia@gmail.com', '1990-12-12');
 
INSERT INTO usuarios
VALUES (200, '123456789A', 'Antonio', 'García', 'agarcia@gmail.com', 'Zaragoza', '1990-12-12', 0.3, '2003-02-01');
 
INSERT INTO usuarios (id, dni, nombre, apellidos, email, fecha_nacimiento)
VALUES (45, '123456789A', 'Antonio', 'García', 'agarcia@gmail.com', '1990-12-12');

• Para insertar varias filas en una tabla

INSERT INTO usuarios (dni, nombre, apellidos, email, fecha_nacimiento)
VALUES ('123456789A', 'Pepe', 'Sanz', 'psanz@gmail.com', '1990-12-12'),
       ('987654321Z', 'Luis', 'Peréz', 'lperez@gmail.com', '1988-01-03');

• Para insertar el resultado de una consulta en una tabla (suponiendo que la tabla otros_usuarios existe y tiene al menos los campos indicados del mismo tipo que en la tabla usuarios)

INSERT INTO usuario (dni, nombre, apellidos, email, fecha_nacimiento)
SELECT dni, nombre, apellidos, email, fecha_nacimiento 
FROM otros_usuarios;

La modificación de registros ya insertados en la tabla se realiza con la sentencia UPDATE, que tiene el siguiente formato:

UPDATE nombre_tabla
SET columna = valor [ {, columna = valor} ]
[ WHERE condiciones ]

Veamos algunos ejemplos:

• Actualizar una columna de una fila

UPDATE usuarios
SET nombre = 'Felipe'
WHERE id = 12;

• Actualizar varias columnas de una fila

UPDATE usuarios
SET nombre = 'Felipe', dni = '123654789H'
WHERE id = 15;

• Actualizar una columna de varias filas

UPDATE pistas
SET precio = precio + precio * 0.10
WHERE precio < 20 AND tipo = 'tenis';

• Actualizar una columna utilizando una subconsulta:

-- Reduce el precio de las pistas que no se han reservado todavía
UPDATE pistas
SET precio = precio - precio * 0.1
WHERE id NOT IN (SELECT id_pista FROM reservas);

Cabe destacar que la ausencia de condiciones en una sentencia de actualización, ejecutaría dicha sentencia sobre todas las filas de la tabla.

El borrado de filas de una tabla se efectúa con la sentencia DELETE, que tiene el siguiente formato:

DELETE FROM nombre_tabla
[ WHERE condiciones ]

Veamos algunos ejemplos:

• Elimina todos los usuarios

DELETE FROM usuarios;

• Borrar una fila estableciendo una condición

DELETE FROM pistas
WHERE id = 10;

• Borrar varias filas estableciendo varias condiciones

DELETE FROM pistas
WHERE tipo = 'baloncesto' OR codigo = 'BAL001';

• Borrar filas relacionando varias tablas

-- Elimina los usuarios que se dieron de alta antes de 2014
-- y aún no han reservado ninguna pista
DELETE FROM usuarios
WHERE id NOT IN (SELECT id_usuario FROM usuario_reserva)
AND fecha_alta < '2014-01-01';

Cabe destacar que la ausencia de condiciones en una sentencia de borrado, eliminaría todas las filas de la tabla.

Para poder trabajar con una base de datos es necesario que esta contenga datos, cuanta más cantidad mejor. Para ello necesitamos hacer muchas inserciones (INSERT INTO) en cada una de las tablas de la base de datos.

Existen programas que tienen sus propias bases de datos con datos de ejemplo y nos permiten generar datos adaptados a los campos de nuestras tablas. En la sección Software Necesario de esta wiki tenemos distintos generadores, de los que tratamos en el siguiente video:

Generadores de datos

La consulta de registros es la operación mas compleja, y también la más ejecutada, de una Base de Datos. Se lleva a cabo con la sentencia SELECT, que tiene el siguiente formato:

SELECT columnas
FROM tablas
[ WHERE condiciones ]
[ GROUP BY columnas ]
[ HAVING condiciones_de_grupo ]
[ ORDER BY columnas_a_ordenar [ASC|DESC] ]

Veamos para que sirve cada una de las cláusulas de esta sentencia:

La cláusula SELECT se utiliza para selecciona las columnas que se quieren visualizar como resultado de la consulta. Se puede seleccionar cualquier columna de las tablas afectadas por la consulta (cláusula FROM), valores constantes establecidos a la hora de ejecutar la consulta, o bien el comodín '*' para indicar que se quieren visualizar todas las columnas afectadas.

La cláusula FROM permite indicar con qué tablas se trabajará en la consulta. No siempre serán tablas de las que se visualicen columnas, puesto que muchas veces sólo se utilizarán para relacionar unas tablas con otras. En cualquier caso, se usen para que se visualicen sus campos o bien para relacionar otras tablas (que no están directamente relacionadas), se deben indicar en esta cláusula.

Veamos algunos ejemplos:

• Consulta de una columna de una tabla

--  Nombre de todos los usuarios (incluye repeticiones)
SELECT nombre
FROM usuarios;

• Consulta de dos columnas de una tabla

-- Nombre y apellidos de todos los usuarios
SELECT nombre, apellidos
FROM usuarios;

• Consulta de todas las columnas de una tabla

-- Toda la información de todos los usuarios
SELECT *
FROM usuarios;

La cláusula WHERE permite establecer condiciones sobre que filas se mostrarán en una sentencia de consulta. En ausencia de esta cláusula se muestran todos los registros de la tabla (aunque sólo las columnas establecidas en la cláusula SELECT). Si se indican condiciones mediante la cláusula WHERE sólo se mostrarán aquellas filas que las cumplan.

Veamos algunos ejemplos:

-- Nombre y dirección de los polideportivos de Zaragoza
SELECT nombre, direccion
FROM polideportivos
WHERE ciudad = 'Zaragoza';

Además, nos permitirá establecer condiciones para establecer lo que se conoce como un INNER JOIN (implícito) entre dos o más tablas:

-- Código y tipo de las pistas de tenis que están operativas
SELECT pistas.codigo, pistas.tipo
FROM pistas, pistas_abiertas
WHERE pistas.id = pistas_abiertas.id_pista AND
      pistas_abiertas.operativa = TRUE AND pistas.tipo = 'tenis';

-- Código y tipo de las pistas de los polideportivos
-- de Zaragoza
SELECT pistas.codigo, pistas.tipo
FROM pistas, polideportivos
WHERE pistas.id_polideportivo = polideportivos.id AND polideportivos.ciudad = 'Zaragoza'

De manera que si utilizamos alias para los nombres de las tablas, podemos escribir la misma consulta algo más rápido:

-- Código y tipo de las pistas de los polideportivos
-- de Zaragoza
SELECT P.codigo, P.tipo
FROM pistas P, polideportivos PP
WHERE P.id_polideportivo = PP.id AND PP.ciudad = 'Zaragoza'

Las cláusulas GROUP BY y HAVING permiten crear agrupaciones de datos y establecer condiciones sobre dichas agrupaciones, respectivamente.

-- Número de polideportivos hay en cada ciudad
SELECT ciudad, COUNT(*) AS cantidad
FROM polideportivos
GROUP BY ciudad;

-- Número de polideportivos hay en cada ciudad, solamente de aquellas
-- ciudades donde hay más de 10.000
SELECT ciudad, COUNT(*) AS cantidad
FROM polideportivos
GROUP BY ciudad
HAVING COUNT(*) > 10000;

-- Número de usuarios en cada ciudad
SELECT ciudad, COUNT(*) AS cantidad
FROM usuarios
GROUP BY ciudad;

También es posible añadir claúsulas WHERE para filtrar registros y agrupar el resultado final

-- Precio medio, por tipo de pista, de las pistas que no están operativas
SELECT P.tipo, AVG(P.precio) AS precio_medio
FROM pistas P, pistas_abiertas PA
WHERE P.id = PA.id_pista AND PA.operativa = FALSE
GROUP BY P.tipo;

En los casos en los que usemos las cláusulas WHERE y HAVING junto con GROUP BY, debemos tener en cuenta que:

• WHERE: filtra los registros antes de agrupar
• HAVING: filtra los resultados después de agrupar

Además, hay que tener en cuenta que en ocasiones nos puede interesar agrupar por algún campo que no va a ser mostrado como resultado de la consulta en la claúsula SELECT. En este caso, agruparíamos por id pero mostraríamos el nombre del polideportivo, ya que puede ser un dato más interesante para el usuario. Además, en casos en que el campo que queramos mostrar pueda repetirse, no conviene agrupar por dicho campo puesto que agruparía registros que en principio no tienen nada que ver. Además, en los dos siguientes ejemplos, unimos dos tablas para realizar la consulta y trabajar con los datos relacionados entre ambas (utilizando la claúsula WHERE para realizar la unión o JOIN que más adelante se explicará con más detalle):

-- Cantidad de pistas que hay en cada polideportivo
SELECT PP.nombre, COUNT(*) AS numero_pistas
FROM polideportivos PP, pistas P
WHERE PP.id = P.id_polideportivo
GROUP BY PP.id;

-- Número de reservas que ha hecho cada usuario
SELECT U.apellidos, U.nombre, U.ciudad, COUNT(*) AS numero_reservas
FROM usuarios U, usuario_reserva UR
WHERE U.id = UR.id_usuario
GROUP BY U.id
ORDER BY U.apellidos;

-- Número de pistas que hay de cada tipo en el polideportivo 'ACTUR 1'
SELECT P.tipo, COUNT(*) AS numero_pistas
FROM pistas P, polideportivos PP
WHERE P.id_polideportivo = PP.id AND PP.nombre = 'ACTUR 1'
GROUP BY P.tipo;

La cláusula ORDER BY permite ordenar el resultado de cualquier consulta atendiendo al campo o campos especificados en esta cláusula, ya sea en orden ascendente (ASC) o descendente (DESC)

-- Nombre y apellidos de los usuarios, ordenados por nombre
SELECT nombre, apellidos
FROM usuarios
ORDER BY nombre ASC;

La clausula LIMIT restringe el número máximo de registros devueltos por una consulta SELECT al número indicado. Se puede aplicar a casi todas las consultas, y genera un menor tiempo de respuesta ya que la ejecución de la consulta termina en el momento en que se completa el cupo de registros solicitados:

-- Muestra los 3 primeros registros que cumplan la consulta
SELECT nombre, apellidos
FROM usuarios
ORDER BY nombre ASC
LIMIT 3;

A la hora de establecer condiciones en una sentencia de consulta, podremos utilizar los siguientes operadores:

• =: Igual
• <: Menor
• >: Mayor
• < =: Menor o igual
• >=: Mayor o igual
• <>: Distinto
• NOT: Operador lógico para la negación de condiciones
• AND: Operador lógico para la conjunción de condiciones
• OR: Operador lógico para la disyunción de condiciones
• DISTINCT: Se utiliza para indicar a la cláusula SELECT que no se muestren valor de columnas repetidos
• LIKE: Permite comprobar si una cadena de caracteres cumple algún patrón determinado:

Permite la expresión de patrones a través de dos caracteres comodín:

• El carácter '_' para expresar un único carácter
• El carácter '%' para expresar cualquier secuencia de caracteres o incluso la secuencia vacía

Veamos un par de ejemplos:

-- Nombre de los polideportivos que están en una ciudad
-- cuyo nombre empieza por Z y tiene 8 caracteres
SELECT nombre
FROM polideportivos
WHERE ciudad LIKE 'Z_______';
 
-- Nombre de los polideportivos que están en una ciudad
-- cuyo nombre empieza por Z
SELECT nombre
FROM polideportivos
WHERE ciudad LIKE 'Z%';

• IN | NOT IN: Permite comprobar si un valor coincide (o no) con algún valor especificado como un conjunto.

-- Nombre y extensión de los polideportivos de 
-- Zaragoza, Huesca y Teruel
SELECT nombre, extension
FROM polideportivos 
WHERE ciudad IN ('Zaragoza', 'Huesca', 'Teruel');

• IS NULL | IS NOT NULL: Se utiliza para comprobar si un valor es igual (o no) a NULL. Dicha comprobación no debe realizar con ningún otro operador.

-- Nombre y apellidos de los usuarios que no indicaron su fecha de nacimiento
SELECT nombre, apellidos
FROM usuarios
WHERE fecha_nacimiento IS NULL;

• BETWEEN: Permite comprobar si el valor de una columna está comprendido entre dos valores determinados:

-- Nombre y apellidos de los usuarios que tienen un descuento
-- entre 10 y 20 %
SELECT nombre, apellidos
FROM usuarios
WHERE descuento BETWEEN 0.1 AND 0.2;

Por otra parte, también tenemos los operadores aritméticos habituales de los lenguajes de programación:

-- Calcula el 10% de las ganancias por reservas del año pasado
SELECT (SUM(precio) * 0.1) AS 'impuestos'
FROM reservas
WHERE YEAR(fecha_reserva) = YEAR(CURDATE()) - 1;

Son funciones que proporciona el lenguaje SQL, que permiten realizar operaciones sobre los datos de una base de datos:

• COUNT: Devuelve el número de filas seleccionadas

-- Número de pistas 
SELECT COUNT(*)
FROM pistas;
 
-- Número de polideportivos en Zaragoza
SELECT COUNT(*)
FROM polideportivos
WHERE ciudad = 'Zaragoza';

• SUM: Devuelve la suma de todos los valores de una columna

-- Cuánto dinero costaría alquilar todas las pistas del
-- polideportivo cuyo id es 23
SELECT SUM(precio)
FROM pistas
WHERE id_polideportivo = 23;

• MIN: Devuelve el valor mínimo de una columna

-- Cuánto vale la pista más barata
SELECT MIN(precio)
FROM pistas;

• MAX: Devuelve el valor máximo de una columna

-- Cuánto vale la pista más cara
SELECT MAX(precio)
FROM pistas;

• AVG: Devuelve el valor medio de los valores de una columna

-- Valor medio de las pistas
SELECT AVG(precio)
FROM pistas;

Hay que tener en cuenta que, excepto la función COUNT, todas las demás devolverán el valor NULL} si no hay columnas sobre las que puedan operar. La función COUNT, sin embargo, devolverá el valor 0 en ese caso.

La creación de subconsultas permite utilizar el resultado de una consulta como valor de entrada para la condición de otra consulta principal.

Veamos unos ejemplos

-- Código y tipo de la pista más barata
SELECT codigo, tipo
FROM pistas
WHERE precio = (SELECT MIN(precio) FROM pistas);
 
-- Codigo y tipo de las pistas cuyo precio está por encima de la media
SELECT codigo, tipo
FROM pistas
WHERE precio > (SELECT AVG(precio) FROM pistas)
 
-- Nombre y apellidos de los usuarios que aún no han realizado 
-- ninguna reserva
SELECT nombre, apellidos
FROM usuarios 
WHERE id NOT IN (SELECT id_usuario FROM usuario_reserva)

Como se ha visto anteriormente, aplicando la cláusula WHERE, introducíamos una posibilidad más a la hora de realizar consultas sobre los datos de nuestra base de datos, lo que se conoce como una consulta de varias tablas o combinación de tablas (en inglés JOIN).

Veamos varios ejemplos:

-- Mostrar, para cada polideportivo, el código y tipo de las pistas 
-- que tiene
SELECT PP.id, PP.nombre, P.codigo, P.tipo
FROM polideportivos PP, pistas P
WHERE PP.id = P.id_polideportivo
 
-- Consulta equivalente
SELECT PP.id, PP.nombre, P.codigo, P.tipo
FROM polideportivos PP INNER JOIN pistas P ON PP.id = P.id_polideportivo

De esta forma, al incluir a más de una tabla en la cláusula FROM estamos realizando lo que se conoce como una combinación interna (INNER JOIN), de forma que cabe la posibilidad de que sólo se muestren algunos datos de alguna de las tablas, puesto que la combinación interna sólo se queda con aquellos registros que están relacionadas con algún registro de la otra tabla.

Si ahora tenemos en cuenta que algún cliente puede no haber realizado pedido alguno, veremos como no aparecen en el resultado de la consulta anterior. En algunos casos eso será lo que queramos, pero quizás en otros casos nos interesa que su nombre aparezca aunque no esté vinculado con ninguno de los pedidos. En este caso nos interesa lo que se conoce como OUTER JOIN. En resumen, si alguna fila de cualquier tabla de la consulta puede no estar relacionado con alguna de las otras, puede ser interesante utilizar un OUTER JOIN. Decidir si utilizar un LEFT OUTER JOIN o bien un RIGHT OUTER JOIN depende de si el dato que puede no tener relación con la otra tabla está a la izquierda o la derecha, respectivamente, según el sentido en que se escribe el código SQL.

-- Mostrar, para cada pista, el codigo de reserva que ha tenido
-- Si nunca se ha reservado, se mostrarán sólo sus datos
-- (En este caso puede pasar que una pista no esté relacionada con 
-- ninguna reserva, como se puede ver en el modelo E-R)
SELECT P.id, P.codigo, P.tipo, R.id AS codigo
FROM pistas P LEFT OUTER JOIN reservas R ON P.id = R.id_pista;
ORDER BY P.codigo

De esta forma mostraremos también los datos de los pistas que no estén relacionados con ninguna reserva. Hay que tener en cuenta que, sólo en este caso, es relevante el orden en el que se especifican las tablas a la hora de definir el JOIN puesto que se incluirán aquellas filas de la tabla del lado izquierdo que no tengan relación con las de la tabla del lado derecho. Es por ello que en los INNER JOIN no se tiene que indicar el sentido de la unión.

-- Mostrar cuántas veces se ha reservado cada pista
SELECT P.id, P.codigo, P.tipo, COUNT(R.id) AS reservas
FROM pistas P LEFT OUTER JOIN reservas R ON P.id = R.id_pista
GROUP BY P.id
ORDER BY P.codigo;

-- Mostrar cuántas reservas ha hecho cada usuario
-- (Es posible que algún usuario no haya hecho reservas. Ver E-R)
SELECT U.dni, U.nombre, U.apellidos, COUNT(R.id) AS numero_reservas
FROM usuarios U LEFT OUTER JOIN usuario_reserva UR ON U.id = UR.id_usuario
                LEFT OUTER JOIN reservas R ON UR.id_reservas = R.id
                LEFT OUTER JOIN pistas P ON R.id_pista = P.id;

En definitiva, a la hora de construir una consulta SQL hay que añadir en la claúsula FROM todas aquellas tablas que estén involucradas en la consulta, bien porque se muestre alguna de sus columnas en la claúsula SELECT, porque se establezca alguna condición con WHERE, se agrupe por alguno de sus campos o incluso simplemente dicha tabla haga de puente entre dos tablas que deban estar involucradas en dicha consulta.

Con respecto al número de tablas que pueden ser incluidas en un JOIN, hay que tener en cuenta que el límite en MySQL es de 61.

La unión de consultas permite unir los resultados de dos consultas totalmente diferentes como si fuera el de una sola. Se realiza mediante la instrucción UNION y muestra los resultados sin repeticiones.

-- Código y tipo de las pistas abiertas y cerradas, indicando 
-- el estado actual
SELECT 'abierta', codigo, tipo
FROM pistas
WHERE id IN (SELECT id_pista FROM pistas_abiertas)
UNION
SELECT 'cerrada', codigo, tipo
FROM pistas
WHERE id IN (SELECT id_pista FROM pistas_cerradas);

La intersección de consultas muestra sólo los valores que aparecen en las dos consultas que se intersectan. Se realiza mediante la instrucción INTERSECT:

-- Ciudades con polideportivos que cuentan con usuarios registrados 
SELECT ciudad
FROM usuarios
INTERSECT
SELECT ciudad
FROM polideportivos;

A continuación se indican algunos métodos para resolver tipos de consultas que no podemos resolver con los visto anteriormente.

El supuesto anterior se puede resolver también con otro tipo de subconsultas. Existe la posibilidad de incluir subconsultas en la clausula FROM:

-- Nombre del polideportivo que más pistas tiene
SELECT pistas_polideportivo.nombre
FROM (SELECT PP.id, PP.nombre, COUNT(*) AS cantidad
      FROM polideportivos PP, pistas P
      WHERE PP.id = P.id_polideportivo
      GROUP BY PP.id) AS pistas_polideportivo
WHERE cantidad = (SELECT MAX(cantidad) 
                  FROM (SELECT COUNT(*) AS cantidad
                        FROM polideportivos PP, pistas P
			WHERE PP.id = P.id_polideportivo
			GROUP BY PP.id) AS cantidad_pistas);

Viendo los dos enfoques, resulta más sencillo guardar una consulta como una Vista para luego hacer una consulta más sencilla sobre ella.

Son dos operadores que se emplean en subconsultas. Se utilizan con los operadores de comparación (>,>=, <, <=, !=, <>, =). Al igual que en los ejemplos anteriores, los usaremos con subconsultas que devuelven varios valores pero solo una columna.

• ANY: comprueba si hay valores de la condición que se encuentran entre los valores que devuelve la subconsulta.
• ALL: comprueba si hay valores de la condición que cumplen con todos los valores que devuelve la subconsulta.

*Cuando la condición emplea = ANY es identico a usar el operador IN. Cuando la condición emplea <> ALL es identico a usar el operador NOT IN.

-- Usuarios de Zaragoza que tienen más descuente que algún 
-- usuario de Huesca
SELECT nombre, apellidos, descuento
FROM usuarios
WHERE ciudad = 'Zaragoza'
AND descuento > ANY (SELECT descuento FROM usuarios 
                     WHERE ciudad = 'Huesca');
 
-- Nombre y apellidos del usuario/s que más reservas 
-- ha realizado
SELECT u.nombre, u.apellidos
FROM usuarios u, usuario_reserva ur
WHERE u.id = ur.id_usuario
GROUP BY u.id
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
                        FROM usuario_reserva ur
                        GROUP BY ur.id_usuario);

• CHAR_LENGTH(str): Devuelve la longitud, en caracteres, de una cadena de texto

 > SELECT CHAR_LENGTH('Esto es una cadena');
-> 18

• CONCAT(str1, str2, . . .): Concatena las cadenas de texto que se pasan como parámetros

 > SELECT CONCAT('Esto', ' forma ', 'una cadena');
-> 'Esto forma una cadena'

• LOWER(str): Devuelve la cadena convertida a minúsculas

 > SELECT LOWER('Bases de Datos Relacionales');
-> 'bases de datos relacionales'

• LTRIM(str): Elimina los espacios en blanco al comienzo de la cadena

 > SELECT LTRIM('    MySQL');
-> 'MySQL'

• REPLACE(str, from_str, to_str): Reemplaza todas las ocurrencias de 'from_str' por 'to_str' que aparezcan en la cadena 'str'

 > SELECT REPLACE('Access es un buen SGBD', 'Access', 'MySQL');
-> 'MySQL es un buen SGBD'

• RTRIM(str): Elimina los espacios en blanco al final de la cadena

 > SELECT RTRIM('MySQL    ');
-> 'MySQL'

• STRCMP(expr1, expr2): Devuelve 0 si las cadenas son idénticas, -1 si la primera es menor, y 1 en cualquier otro caso

 > SELECT STRCMP('MySQL', 'Access');
-> -1

• SUBSTRING(str, pos), SUBSTRING(str, pos, len): Devuelve una subcadena de 'str' comenzando en la posición 'pos'. En el segundo caso tomará caracteres hasta completar una subcadena de tamaño 'len'

 > SELECT SUBSTRING('MySQL es un buen SGBD', 7);
-> 'es un buen SGBD'
 
 > SELECT SUBSTRING('MySQL es un buen SGBD', 7, 2);
-> 'es'

• TRIM(str): Elimina los espacios en blanco al comienzo y al final de la cadena

 > SELECT TRIM('   MySQL    ');
-> 'MySQL'

• UPPER(str): Devuelve la cadena convertida a mayúsculas

 > SELECT UPPER('Esto es una cadena');
-> 'ESTO ES UNA CADENA'

• RAND(), RAND(semilla): Devuelve un número aleatorio del rango de 0 a 1.0. Es posible pasar una semilla como parámetro para generar secuencias repetibles

 > SELECT RAND();
-> 0.838373287632872
 
 > SELECT RAND(20);
-> 0.1588498478438734
 
 > SELECT RAND(20);
-> 0.1588498478438734

• ROUND(X), ROUND(X, D): Redondea el valor de X al número más cercano. Si se especifica el parámetro 'D' se pueden indicar los decimales con los que se quiere aproximar

 > SELECT ROUND(0.67);
-> 1
 
 > SELECT ROUND(1.235);
-> 1
 
 > SELECT ROUND(1.235, 1);
-> 1.2

• CURDATE(): Devuelve la fecha actual

 > SELECT CURDATE();
-> '2012-01-09'

• CURTIME(): Devuelve la hora actual

 > SELECT CURTIME();
-> '19:23:02'

• CURRENT_TIMESTAMP(): Devuelve la fecha y hora actual

 > SELECT CURRENT_TIMESTAMP();
-> '2012-01-09 19:23:02'

• DATE(expr): Extrae la fecha de una expresión de fecha o de fecha y hora

 > SELECT DATE('2012-01-09 19:23:02');
-> '2012-01-09'

• DATEDIFF(expr, expr2): Devuelve el número de días entre la fecha inicial 'expr' y la fecha final 'expr2'

 > SELECT DATEDIFF('2011-01-09', CURDATE());
-> -365

• DATE_ADD(fecha, INTERVAL expr1 type), DATE_SUB(fecha, INTERVAL expr type): Realizan operaciones aritméticas con fechas

Como valor de 'type' se pueden especificar los partes de fecha con los que se quiera realizar el cálculo. Estos son algunos:

• SECOND: Segundos
• MINUTE: Minutos
• HOUR: Horas
• DAY: Días
• WEEK: Semanas
• MONTH: Meses
• YEAR: Años
• DAY_HOUR: Días Horas
• YEAR_MONTH: Años-Meses

 > SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 DAY):
-> '1998-01-01 23:59:59'
 
 > SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
 
 > SELECT DATE_ADD('1998-01-02', INTERVAL 1-1 YEAR_MONTH);
-> '1999-02-02'

• DATE_FORMAT(fecha, formato): Formatea el valor de fecha a través de estos especificadores:
  • %c Mes (1-12)
  • %e Día del mes (1-31)
  • %h Hora (01-12)
  • %H Hora (00-23)
  • %i Minutos (00-59)
  • %M Nombre del mes
  • %m Mes (01-12)
  • %s Segundos (00-59)
  • %Y Año (cuatro dígitos)
  • %y Año (dos dígitos)

 > SELECT DATE_FORMAT('1998-01-01 23:59:59', '%e/%m/%y');
-> '1/01/98'
 
 > SELECT DATE_FORMAT('1998-01-01 23:59:59', '%e/%M/%Y %H:%i');
-> '1/January/1998 23:59'

• DAY(fecha): Devuelve el día del mes de la fecha especificada (1-31)

 > SELECT DAY('1998-01-01 23:59:59');
-> 1

• DAYNAME(fecha): Devuelve el nombre del día de la semana de la fecha especificada

 > SELECT DAYNAME('1998-01-01 23:59:59');
-> Thursday

• DAYOFYEAR(fecha): Devuelve el día del año para la fecha especificada (1-366)

 > SELECT DAYOFYEAR('1998-01-01 23:59:59');
-> 1

* MONTH(fecha): Devuelve el número de mes para la fecha especificada (1-12)

 > SELECT MONTH('1998-01-01 23:59:59');
-> 1

• MONTHNAME(fecha): Devuelve el nombre del mes para la fecha especificada

 > SELECT MONTHNAME('1998-01-01 23:59:59');
-> January

• WEEK(fecha): Devuelve el número de la semana para la fecha especificada (0-52)

 > SELECT WEEK('1998-01-01 23:59:59');
-> 0

• WEEKDAY(fecha): Devuelve el día de la semana para la fecha especificada (0-6)

 > SELECT WEEKDAY('1998-01-01 23:59:59');
-> 3

• YEAR(fecha): Devuelve el número de año para la fecha especificada

 > SELECT YEAR('1998-01-01 23:59:59');
-> 1998

© 2024 Santiago Faci y Fernando Valdeón