Realización de consultas.

Caso práctico

Ilustración de Noiba, alumna que realiza la FCT en el departamento de informática de la empresa — Alain Bachellier (CC BY-NC-SA)

En la unidad anterior Noiba y Vindio han dejado lista la base de datos que le encargaron en Talleres Faber, sobre un taller mecánico, para empezar a introducir la información en las tablas, de forma que se pueda acceder a los datos como si de una sola tabla se tratara. En este momento ha acabado la primera parte de su proyecto y quieren mostrar el resultado.

La empresa ha crecido mucho desde que empezó su actividad como un pequeño taller mecánico que reparaba los vehículos de los vecinos del barrio.

Actualmente tiene varios empleados y el número de clientes es cada vez mayor.

Los socios de la empresa están deseando ver cómo el trabajo de Noiba y Vindio simplifican las tareas de gestión y organización, y quieren comprobar que ofrece todas las prestaciones adicionales que habían prometido.

Una de las cosas más importantes que ofrece una base de datos es la opción de poder consultar los datos que guarda, por eso Noiba y Vindio van a intentar sacar el máximo partido a las tablas que han guardado y sobre ellas van a obtener toda aquella información que su cliente les ha solicitado. Sabemos que dependiendo de quién consulte la base de datos, se debe ofrecer un tipo de información u otra. Es por esto que deben crear distintas consultas y vistas.

Ocultar

En esta unidad aprenderemos a extraer la información de una base de datos estableciendo los requisitos y criterios para seleccionar los datos.

Por lo que has estudiado en unidades anteriores, conoces las ventajas que ofrece un SGBD, pero también sabes que si no conocemos la forma de relacionarnos con la base de datos, la información puede estar “disponible” pero no podremos acceder a ella.

El SGBD va a ser capaz, a partir de las instrucciones, de hacer consultas enlazando las tablas mediante las relaciones definidas en el diseño y en la creación. Por eso es tan importante que todo se haya hecho respetando los criterios establecidos por el modelo, tanto en la fase de diseño lógico como físico. De no hacerlo así nos podríamos encontrar con que, una vez realizado todo el trabajo y después de registrar los datos en las tablas, no tenemos acceso a lo que buscamos o que la información devuelta por las consultas resulta incoherente.

Ocultar

El siguiente paso sería introducir los datos en las tablas, pero para facilitar la comprensión del proceso vamos a partir de una base de datos ya creada y que contenga información. Explicaremos a continuación como hacer consultas en ella, tanto desde herramientas gráficas como utilizando el lenguaje SQL.

1.- Herramientas para la realización de consultas.

Caso práctico

Ocultar

Ilustración de Vindio, compañero de Juan en el departamento de informática — Alain Bachellier (CC BY-NC-SA)

Noiba y Vindio saben que para conseguir el acceso a los datos es muy importante conocer bien:

El lenguaje que nos permita construir las consultas.
Las herramientas gráficas de que dispone el SGBD para ayudarnos.

Pues bien, vamos a revisar con ella las herramientas gráficas que se utilizan con algunos SGBD muy conocidos como: asistentes para realizar consultas, entornos para introducir consultas en modo diseño, editores para crear nuestras sentencias en SQL etc.

Ocultar

Como sabemos un SGBD integra, además de la base de datos, un conjunto de herramientas que nos facilitan el diseño, la gestión y la administración de la base de datos.

En unidades anteriores hemos visto cómo algunas de esas herramientas nos pueden ayudar a establecer tanto el diseño lógico como el diseño físico de la base de datos. En el caso de las consultas también contamos cada vez con un mayor número de utilidades.

Podemos hacer una clasificación de estas herramientas en dos tipos:

Herramientas proporcionadas por el gestor: algunos gestores proporcionan un entorno integrado donde se incluyen asistentes, editores gráficos, etc. En esta categoría nos encontramos con Microsoft Access, OpenOffice Base, Workbench o phpMyAdmin. Las dos últimas se utilizan, exclusivamente, para interactuar con servidores MySQL.
Herramientas externas al gestor: en este caso hablamos de herramientas que añaden funcionalidades y son compatibles con distintas bases de datos. Navicat y HeidiSQL pertenecen a este tipo de herramientas.

A continuación veremos algunos ejemplos de los distintos tipos de herramientas que hemos citado, teniendo en cuenta que se trata de un pequeño ejemplo de las numerosas herramientas presentes en el mercado, tanto de software libre como propietario.

Autoevaluación

Ocultar

Solución

1.1.- Base de datos para ejemplos: campeonato.

Ocultar

A lo largo de esta unidad realizaremos numerosos ejercicios para aprender a manejar el lenguaje SQL. Como base de la mayoría de los ejercicios usaremos la base de datos denominada CAMPEONATO, que tendrás que desplegar en tu servidor MySQL. Te indicamos a continuación el modelo y script SQL de la base de datos y la forma de instalarla en tu servidor MySQL.

Como cliente de MySQL, puedes usar la línea de comandos o cualquier cliente gráfico, como por ejemplo Workbench.

Ocultar

A continuación vemos:

El modelo la base de datos de ejemplos, denominada campeonato.
Cómo desplegarla en nuestro servidor MySQL y lanzar consultas desde el cliente Workbench.
El script de creación de la base de datos, de la estructura y con una serie de datos iniciales te lo proporcionamos en el siguiente tutorial (punto 3).

Modelo ER de la base de datos CAMPEONATO

El modelo ER de la base de datos campeonato es el siguiente:

Modelo Relacional de la base de datos CAMPEONATO

El modelo relacional correspondiente a la base de datos campeonato es el siguiente: te mostramos el modelo textual a la izquierda y de forma gráfica a la derecha (elaborado con la herramienta de modelado de Workbench).

En el modelo textual se ha indicado la restricción de cada claveajena o foránea en cuanto a los borrados y las modificaicones:

BR: borrado restrictivo, BN:borrado con puesta a nulos, BC: borrado en cascada.
MC: modificcación en cascada.

Ilustración del Modelo Relacional base de datos campeonato — Libre Office Writter (Elaboración propia)

Ilustración del modelo relacional en Workbench de campeonato — Workbench (Elaboración propia)

Carga o despliega la base de datos en MySQL

Descarga desde el siguiente enlace el script de creación de la base de datos campeonato: script de creación basse de datos campeonato (zip - 1,80 KB)
Una vez descargado, lo debes descomprimir para acceder ela fichero de extensión .sql
Inicia sesión o conéctate al servidor MySQL con el cliente Workbench. Para ello, asegúrate de tener iniciado o puesto en marcha el servidor de bases de datos MySQL e inicia sesión en como el usuario root, indicando su contraseña.
Carga en Workbench el script que descargaste en el apartado anterior, para ello ve al menú File/Open SQL Script... y busca en tus carpetas locales el archivo que descargaste. Una vez localizado, lo seleccionas y pulsas en Abrir. Aparecerá el script en pantalla.

Ilustración de script de creación de base de datos campeonato en Workbench — Workbench (Elaboración propia)

Ejecuta el script para implantar la base de datos CAMPEONATO

Con en script SQL en pantalla, pulsa sobre File/Run SQL Script o bien sobre el botón de atajo "el rayo amarillo". Se ejecutará el script y dejará implantada la base de datos en el servidor.
Puedes revisar las sentencias SQL de creación de la base de datos y así repasar su estructura.

Ilustración de Ejecución script SQL — Workbench (Elaboración propia)

Muestra la base de datos en tu Servidor

Actualiza el cliente gráfico pulsando sobre la doble flecha, esquina superior izquierda y verás la nueva base de datos, o bien, escribe en una nueva ventana de SQL (File/New Query Tab) la sentencia: SHOW DATABASES; y comprobarás que ya está la base de datos en tu servidor.

Ilustración de la base de datos campeonato en el servidor — Workbench (Elaboración propia)

Pon en uso la base de datos y ejecuta algunas consultas

Pon en uso la base de datos, si no lo está, ejecutando USE campeonato; (aparecerá en negrita en la sección de la izquierda).
Puedes ver los datos de cada tabla ejecutando la sentencia SELECT que corresponda. Por ejemplo, para ver los datos de la tabla equipo, escribe en el ventana de SQL la sentencia: SELECT * FROM equipo; y ejecútala pulsando sobre le "rayo amarillo con cursor" o bien pulsando las teclas CTRL+INTRO.
Puedes seguir viendo el contenido del resto de tablas, ejecutando la correspondiente sentencia SELECT ........ ¿te animas a ello?

Ilustración de una consulta en Workbench — Workbench (Elaboración propia)

Recomendación

Ocultar

Todas las consultas de ejemplo sobre la base de datos CAMPEONATO y las que se piden en los ejercicios deben probarse sobre el servidor MySQL. Puedes escribir las consultas en la línea de comandos o en cualquier cliente gráfico de MySQL, como Workbench.

Es muy importante que se almacenen todas las sentencias que se realicen en los distintos ejercicios. Para ello puedes utilizar cualquier editor de texto plano.

1.2.- Asistentes y herramientas gráficas de diseño.

Ocultar

Ilustración de dos herramientas: llave inglesa y destornillador — Everaldo Coelho and YellowIcon (GNU/GPL)

Tanto incluidos en el propio SGBD, como cuando se trata de aplicaciones externas al propio gestor, existen herramientas gráficas que nos ayudan a realizar consultas a una o más tablas de una base de datos de una forma fácil y sencilla, incluso sin necesidad de conocer el lenguaje SQL. Podemos clasificar esas herramientas en:

Asistentes para consultas.
Herramientas para el diseño de consultas.
Herramientas de edición de SQL.

Creación de consultas desde un asistente para consultas.

Estos asistentes son aplicaciones que nos permiten seleccionar las tablas, los campos, el orden de las filas, establecer criterios de selección, grupos y cálculos, etc., siguiendo unos pasos muy sencillos.

Ejemplos de estos asistentes los podemos ver OpenOffice Base y en Microsoft Access, aunque también incorporan este tipo de asistentes bases de datos como Oracle.

Creación de consultas desde la ventana de diseño de consultas.

Tanto en los propios SGBD como en aplicaciones que se pueden instalar para aumentar las prestaciones y que son externas al SGBD, podemos encontrar herramientas gráficas para diseñar consultas mediante opciones como:

Representaciones gráficas de tablas y campos.
Opciones con listas desplegables para elegir los tipos de datos, los tipos de índices, etc.
Posibilidad de establecer los criterios de selección, de ordenación, etc, seleccionando opciones o eligiendo operadores, etc.

Se trata de herramientas que permiten al usuario elaborar consultas sencillas de forma intuitiva, con el simple manejo de recursos gráficos presentados en una ventana donde se encuentran disponibles todos los elementos que representan las distintas cláusulas que una consulta puede recoger. Aplicaciones como Workbench, phpMyAdmin o Navicat incorporan este tipo de herramientas.

Recomendación

Ocultar

Para ver un ejemplo de cómo crear consultas mediante un asistente te recomendamos que visualices el siguiente vídeo dónde se explica cómo usar el asistente para crear consultas en Access. Pulsa sobre el siguiente enlace para visualizarlo.

Creación de consultas mediante el asistente de Access

Para saber más

Ocultar

Algunas herramientas que permiten trabajar con diferentes SGBD, entre ellos MySQL, son Navicat y SQLMaestro.

Es interesante que accedas a su páginas oficiales y que navegues por ellas para comprobar qué aplicaciones ofrecen para la gestión de servidores de bases de datos. Los enlaces son los siguientes:

Página oficial de Navicat

Página oficial de SQLMaestro

En ambos casos puedes descargar la aplicación para MySQL (son versiones de prueba de un determinado número de días). Las puedes instalar en tu ordenador, ejecutarlas, y recorrer las principales opciones. Más adelante las usaremos en algunos ejercicios.

También es interesante que te descargues el manual oficial de esas aplicaciones, en formato pdf (está en inglés).

1.3.- Editores de SQL.

Ocultar

Aunque hemos citado una serie de herramientas que nos pueden facilitar el diseño de consultas, la herramienta más potente a la hora de establecer criterios para seleccionar la información almacenada en una base de datos es el conocimiento del lenguaje SQL. Todos los SGBD tienen un editor que, con más o menos elementos de ayuda, nos permite introducir nuestras instrucciones y nos muestra dónde se producen los posibles errores.

Ilustración de una lupa sobre una tabla — Everaldo Coelho and YellowIcon (GNU/GPL)

Veamos un ejemplo con Workbench.

Conexión con Workbench al servidor MySQL

Nos conectamos al servidor MySQL mediante la conexión de Workbench que se ha creado para el usuario root, poniendo la contraseña que dimos en la instalación de MySQL.

Lo normal es que existan diferentes usuarios en el SGBD, cada uno de ellos con ciertos permisos o privilegios sobre las diferentes bases de datos instaladas en el Servidor. Para esos otros usuarios aparecen conexiones en la pantalla inical de Workbench.

De momento, en vuestro caso, tendréis sólo una conexión para el usuario root.

Ilustración de Workbench y la conexión al servidor MySQL como usuario root — Workbench (Elaboración propia)

En el Editor de SQL activamos base de datos y mostramos sus tablas

Observa que en la ventana de edición de SQL se escriben las sentencias.

Para ejecutar una sentencia, hacemos los siguiente:

Situar el cursor sobre esa sentencia o al final del ; (punto y coma) y pulsar las teclas Ctrl+ Intro, o bien pulsar sobre el botón superior del rayo indicado en la imagen.

Otras secciones son la Ventana con los resultados de ejecutar las entencias y la ventana en la que el sistema informa del éxito de ejecución o los errores encontrados. En este ejemplo, no hay errroes en la ejecución.

ILustración de Workbench con sentencias SQL — Workbench (Elaboración propia)

En el editor de SQL escribimos una consulta, sin errores

La consulta SELECT * FROM departamento; muesta todos los datos existentes en la tabla departamento.

Observa que se ejecuta sin errores, y se muestran todos los datos de los departamentos.

Ilustración de Workbench con una consulta SQL sin errores — Workbench (Elaboración propia)

En el Editor SQL escribimos una consula, con errores

La consulta SELECT * FROM departamentos;

Observa que se ejecuta con errores y el sistema avisa de ello. El error es, que el nombre de la tabla no es correcto, es departamento sin s al final.

Ilustración de Workbench de una consulta SQL con errores — Workbench (Elaboración propia)

Ejercicio Resuelto

Ocultar

Puedes empezar a practicar sobre la base de datos CAMPEONATO y lanzar una consulta sobre cada una de sus tablas. Para ello vas a usar el cliente gráfico Workbench, de manera que debes tener desplegada (instalada) la base de datos campeonato en el servidor MySQL y después, utilizando la ventana SQL de esta herramienta debes consultar todos los datos de las tablas: concursante, equipo, juego y participa.

Autoevaluación

Ocultar

Pregunta

Sobre la herramienta Workbench, marca las opciones correctas.

Respuestas

Opción 1

Proporciona un interfaz (ventana SQL) para redactar consultas en lenguaje SQL y ejecutarlas contra el servidor.

Opción 2

Proporciona una ventana con los resultados de ejecutar las sentencias SQL.

Opción 3

No proporciona ninguna venta o salida de información en la que el sistema informe del éxito de ejecución o los errores encontrados.

Opción 4

Para ejecutar una sentencia redactada en la ventana SQL podemos situar el cursor sobre esa sentencia o al final del ; (punto y coma) y pulsar las teclas <abbr title="Control">Ctrl</abbr>+ <abbr title="Introducir">Intro</abbr>

Retroalimentación

Siempre nos avisará de los posibles errores tras ejecutar una sentencia redactada en la ventana SQL

Solución

2.- El lenguaje de manipulación de datos: LMD.

Caso práctico

Ocultar

En este punto Noiba se dispone a trabajar con el lenguaje de manipulación de datos (LMD), y dentro de él comenzará por las instrucciones que le van a permitir hacer consultas a los datos contenidos en Talleres Faber.

Conociendo esas instrucciones, con consultas fáciles de escribir, podrá saber cosas tan importantes como:

El importe facturado este mes.
Las reparaciones en las que interviene cada mecánico.
Qué tipo de avería es la más común en el taller.
Cuanto tiempo, por término medio, lleva una reparación, etc.

Ocultar

En la unidad anterior clasificamos las sentencias del lenguaje SQL en 4 tipos ¿recuerdas?

Tipos de sentencias SQL
Lenguaje SQL	Sentencias
LDD (Lenguaje de Definición de Datos)	Incluye sentencias para gestionar las. estructuras
LMD (Lenguaje de Manipulación de Datos)	Incluye sentencias para gestionar los datos.
LCD (Lenguaje de Control de Datos)	Incluye sentencias para gestionar la seguridad y los permisos.
LTC (Lenguaje de Control de Transacciones)	Incluye sentencias para gestionar transacciones

Pues bien, ahora vamos a tratar con el LMD o DML que es una parte del lenguaje SQL que se encarga de la gestión de los datos almacenados. El LMD tiene instrucciones para ayudarnos a:

Insertar filas (INSERT).
Consultar los datos almacenados (SELECT).
Modificar esos datos (UPDATE).
Borrar filas (DELETE).

Ocultar

Sin ninguna duda, la operación que más veces se realiza sobre una base de datos es la consulta.

Reflexiona

Ocultar

Tanto en el LDD, como en el LMD existen sentencias que nos permiten Modificar o Borrar. La diferencia está en que cuando tratamos con el Lenguaje de definición de datos, las instrucciones de modificación y/o borrado afectan a la estructura de la base de datos: tablas, columnas, índices, etc.; es decir, a los objetos que la componen.

Cuando utilizamos instrucciones correspondientes al Lenguaje de manipulación de datos, los cambios que hagamos afectarán a los datos contenidos en la base de datos: las filas de las tablas.

De hecho, los nombres de las sentencias que se usan para crear, modificar y borrar elementos en ambos sublenguajes son diferentes.

Autoevaluación

Ocultar

Solución

3.- La sentencia SELECT.

Caso práctico

Ocultar

Ilustración de Juan, responsable del departamento de informática — Jonny Goldstein (CC BY)

Como hemos visto, las herramientas gráficas nos ofrecen una gran ayuda a la hora de enviar consultas a nuestra base de datos. Pero sin duda para tener mayor control sobre cualquier SGBD, y en concreto sobre las consultas a los datos que almacena, Juan recomienda a Noiba y Vindio que lo más seguro es conocer las instrucciones de SQL que integran el LMD o DML.

Para realizar consultas se utiliza la sentencia SELECT. Siguiendo el criterio de Juan, aprenderemos a utilizar las opciones más importantes de SELECT, desde consultas sencillas a una sola tabla hasta consultas complejas combinando datos de varias tablas.

Ocultar

¿Cómo podemos seleccionar los datos que nos interesen dentro de una base de datos? Para recuperar o seleccionar los datos, de una o varias tablas puedes valerte del lenguaje SQL, para ello utilizarás la sentencia SELECT, que consta, como veremos de muchas opciones para poder construir consultas todo lo complejas que puedas imaginar. Veremos en esta unidad las opciones más importantes de la sentencia SELECT.

Ocultar

SELECT es la sentencia que tiene mayores variaciones y opciones sintácticas en todos los SGBD del mercado y también la más compleja y potente de todas las instrucciones SQL.

Ocultar

Utilizaremos esta sentencia para consultar los datos de una base de datos. En la sentencia SELECT el usuario especifica lo que quiere obtener, no cómo, ni dónde.

Como hemos dicho presenta muchas opciones. Las más comunes son:

Ocultar

SELECT
[ALL | DISTINCT | DISTINCTROW ]
{Expresión | Columna1, ... | * }
FROM TablasReferenciadas
[WHERE Condiciones]
[GROUP BY {NombreColumna | Expresión | Posición} …]
[HAVING Condiciones]
[ORDER BY { NombreColumna | Expresión | Posición } [ASC | DESC] , ...]
[LIMIT { [desplazamiento,] contador | contador OFFSET desplazamiento} ]

Ocultar

Antes de empezar a analizar la función de cada una de las cláusulas u opciones de esta sentencia veremos algunas consideraciones generales:

Para realizar una consulta a una o varias tablas con esta sentencia es imprescindible utilizar al menos las palabras reservadas SELECT y FROM:
- SELECT seguido del nombre de la columna o columnas que se quieran mostrar, o expresiones, separándolas por coma.
- FROM seguido del nombre de la tabla o tablas de las que se obtienen los datos.
- El resto de las cláusulas son opcionales, pero en el caso de que se incluyan debe respetarse el orden descrito anteriormente.

Según nos indica el formato: SELECT {Expresión | Columna1, ... | * }

Tras la sentencia SELECT se deben incluir uno más ítems, separados por coma, o bien un asterisco:
- Nombres de columnas de tablas que queramos listar y/o expresiones formadas por columnas, operadores y funciones.
- Un asterisco ( * ) para indicar que se listen todas las columnas de la tabla. (SELECT * FROM nombreTabla;)
- Pueden aparecer otras expresiones, funciones, etc.; incluso aunque no correspondan a ninguna tabla. ( SELECT user(), curdate(), 7*4;)
- Se pueden poner alias a los nombres de las columnas para hacer más descriptivo el resultado de una consulta, ya que el encabezado de la consulta se forma a partir de los ítems de los la SELECT, y no siempre son apropiados los nombre de las columnas, o los cálculos realizados.

Te mostramos algunos Ejemplos.

**Todas las columnas de la tabla (*)**.

Ejemplo. Una consulta mostrando todos los datos de los juegos de la base de datos CAMPEONATO. Es una consulta sobre la tabla juego mostrando todas sus columnas.

SELECT * FROM juego;

La redactamos la consulta en la ventana SQL de Workbench. Previamente hay que poner en uso o activar la base de datos campeonato.

Ilustración del resultado de ejecutar la consulta a la tabla juego de campeonato — MySQL Workbench (Elaboración propia)

Alias de columnas.

El encabezado de las columnas que aparece en las consultas, es el nombre de la columna o expresión correspondiente. Si éste resulta demasiado largo o es el resultado de un cálculo, se puede cambiar usando un alias de columna. Es posible asignar un alias a cualquiera de las expresiones SELECT.

Para ello, se escribe tras el nombre de columna la cláusula AS seguida del nuevo nombre que va a aparecer en el encabezado entre comillas, aunque esta palabra AS es opcional.

El alias puede usarse dentro de toda la consulta en cualquier referencia a la columna correspondiente, por ejemplo, dentro de las cláusulas WHERE, HAVING o GROUP BY que veremos a continuación.

Ejemplo. Listado del código, nombre y equipo de los concursantes. Poniendo un alias a las columnas código del concursante y del quipo.

SELECT cdconcur as 'concursante', nombre, cdequipo "equipo"
FROM concursante;

Se indica un alias para cada una de las columnas cdconcur y cdequipo, que son 'concursante' y "equipo" respectivamente. Observa que es opcional poner delante del alias la palabra reservada AS, y además se admite la comilla doble y la comilla simple en MySQL.

Ilustración el resultado de una consulta con alias de columna — MySQL Workbench (Elaboración propia)

Columnas calculadas.

Una consulta en SQL puede incluir columnas cuyos valores se obtienen haciendo cálculos a partir de los datos almacenados en las columnas de las tablas. Cuando queremos mostrar el resultado de la columna calculada dentro de SELECT es conveniente utilizar un alias de columna para evitar que se incluya como cabecera la propia función u operación realizada.

Ejemplo 1. Una consulta mostrando el resultado de una cálculo y el resultado de utilizar ciertas funciones del SGBD MySQL, y poniendo un alias a algunos ítems para clarificar o hacer más legible el encabezado del resultado de la consulta.

SELECT 7*4 "Cálculo", USER(), VERSION(),CURDATE() "Fecha Actual", ROUND(10/3,2);

Ejecutada esta consulta, desde consola, daría el siguiente resultado.

Ilustración de una captura con el resultado de la consulta anterior. — MySQL Command Line (Elaboración propia)

Observa como se sustituye en el encabezado de la consulta el ítem correspondiente por el alias que se ha indicado para esa columna.

Ejemplo 2. Listado de todos los concursantes, con su código, nombre y días de antigüedad (días transcurridos desde su inscripción hasta la fecha actual)

SELECT cdconcur, nombre, datediff(curdate(), fecha_inscri) AS "Antigüedad en días"
FROM concursante;

Observa que para obtener los días de antigüedad se ha utilizado la función curdate() que devuelve la fecha actual del sistema y la función datediff() que obtiene la diferencia en días, entre la primera y segunda fecha que le pasamos a la función.

Poniendo el nombreTabla.nombreColumna.

También es válido, y además necesario cuando se seleccionan varias columnas con el mismo nombre en diferentes tablas, especificar el nombre de la columna separado con un punto del nombre de la tabla, de la siguiente forma:

NombreTabla.NombreColumna

Ejemplo. Una consulta que muestre el nombre de cada juego y el nombre de su equipo.

En este caso tenemos una consulta a dos tablas, juego y equipo, y en ambas tablas existe una columna denominada nombre, por lo que para que MySQL puede diferenciarlas se deben calificar, poniendo delante, por ejemplo, el nombre de la tabla de dónde proceden.

SELECT juego.nombre, equipo.nombre 
FROM juego
JOIN equipo ON juego.cdequipo=equipo.cdequipo;

Ilustración de una consulta select y su resultado de ejecución en Workbench — MySQL Workbench (Elaboración propia)

Consultar tablas de una base de datos que no está en uso.

Se pueden consultar datos de tablas de una base de datos que no está actualmente en uso estableciendo el nombre de la base de datos a la que pertenece separado con un punto del nombre de la tabla:

NombreBaseDeDatos.NombreTabla

Ejemplo. Estando en uso otra base de datos diferente a campeonato, se podría lanzar la consulta:

 SELECT * FROM campeonato.juego;

Recomendación

Ocultar

Las cláusulas ALL y DISTINCT son opcionales.

Si incluyes la cláusula ALL después de SELECT, indicarás que quieres seleccionar todas las filas estén o no repetidas. Es el valor por defecto y no se suele especificar.
Si incluyes la cláusula DISTINCT después de SELECT, se suprimirán aquellas filas del resultado que tengan igual valor que otras.

Veremos su uso en un apartado posterior.

Autoevaluación

Ocultar

Contesta si es verdadera o falsa la siguiente afirmación:

Retroalimentación

Falso

Debe respetarse el orden de las cláusulas definido en la sintaxis.

Para saber más

Ocultar

Como hemos dicho, la sentencia SELECT admite muchas cláusulas y además éstas presentan diferencias en función del SGBD elegido. A lo largo de esta unidad iremos viendo las más utilizadas y que, en muchos casos, son comunes a un gran número de gestores. Para conocer todas las opciones que admite la sentencia SELECT en MySQL pincha en el siguiente enlace:

Sintaxis de la sentencia SELECT

3.1.- Cláusula FROM.

Ocultar

Ilustración de una interrogación con lupa — Everaldo Coelho and YellowIcon (GNU/GPL)

Como hemos visto, el formato básico de esta sentencia incluye como mínimo la palabra SELECT y la cláusula FROM. Empezaremos por describir su sintaxis y seguiremos con el resto de las cláusulas que más se utilizan.

Formato básico de una consulta.

SELECT
A continuación de SELECT se especifican las columnas que se desea mostrar. Podemos referenciarlas:

Con un *: Indica que se consultan todas las columnas de la tabla.
Con una lista de nombres de columnas separadas por comas.

Cláusula FROM.

Pero ¿de dónde se extraen esos datos? Justo eso lo indicamos tras el FROM.

FROM
Indica la tabla o las tablas que se van a consultar, escribiendo sus nombres separados por comas si se trata de más de una tabla.

Ejemplo: Mostrar el código, nombre y fecha de inscripción de todos los concursantes.

SELECT cdconcur, nombre, fecha_inscri FROM concursante;

Ejemplo: usando Alias de columna.

SELECT cdconcur AS "Código de concursante", nombre, fecha_insri "Fecha de Inscripción" 
 FROM concursante;

Al igual que existen Alias de columna, se pueden indicar alias para la tablas. En este caso el alias de tabla suele estar formado por una o dos letras (La inicial o iniciales de comienzo de la tabla) y no es necesario poner AS ni ponerlo entre comillas.

Alias de tablas.

Un alias se puede utilizar también para asociar un nuevo nombre a una tabla. Se trata de un nombre, generalmente más corto, que se utiliza sobre todo cuando consultamos varias tablas y hay nombres de columnas que coinciden.

Ejemplo. Listar el nombre de cada concursante y el nombre de su equipo.

SELECT c.nombre "Concursante", e.nombre "Equipo"  
FROM concursante c, equipo e 
WHERE……… ;

(Hemos asignado el alias c a la tabla concursante y el alias e a la tabla equipo por eso los nombres de las columnas van precedidos del alias c ó e seguido de punto y el nombre de la columna. Además se han puesto alias de columna.

Ocultar

Autoevaluación

Ocultar

Indica si la siguiente afirmación es verdadera o falsa

Retroalimentación

Verdadero

3.2.- Cláusula WHERE.

Ocultar

Las consultas que hemos realizado hasta ahora muestran todas las filas de la tabla o tablas implicadas.

Pero, ¿Cómo indicamos o ponemos un filtro de selección de manera que solamente se listen las filas que cumplan cierta condición? Eso lo hacemos utilizando la cláusula WHERE.

Cláusula WHERE . Poniendo un filtro a la consulta.

[WHERE Condiciones]

WHERE
Permite obtener los datos de las filas que cumplen con la condición expresada. Tras WHERE se escribe una expresión de tipo relacional o de comparación que usará alguno de los operadores relacionales y lógicos que veremos después.

El formato de la condición es: expresión operador expresión

Pueden construirse condiciones múltiples con los operadores lógicos AND, OR ó NOT.
En una cláusula WHERE se puede usar cualquier función disponible en MySQL, excluyendo sólo las de agregado o resumen. Estas funciones están diseñadas específicamente para usarse en cláusulas GROUP BY y se verán en otro apartado.

Te mostramos algunos ejemplos.

Ejemplo 1. Mostrar el nombre, fecha de inscripción y cuota e los concursantes con cuota de inscripción superior a 70 euros.

SELECT nombre, cuota_inscri, fecha_inscri
FROM concursante
WHERE cuota_inscri>70;

Que redactada y ejecutada desde Workbench daría el siguiente resultado:

Ilustración de una consulta con claúsula WHERE — MySQL Workbench (Elaboración propia)

Ejemplo 2. Mostrar el nombre, megusta y dificultad de los juegos cuya dificultad sea 'media' o 'baja'.

En este caso la condición del WHERE es una condición compuesta que usa el operador lógico OR y operadores realacionales de igualdad =

SELECT nombre, megusta, dificultad
FROM juego
WHERE dificultad="media" OR dificultad="baja";

Que redactada y ejecutada desde Workbench daría el siguiente resultado:

Ilustración del resultado de una select con filtro where — MySQL Workbench (Elaboración propia)

Autoevaluación

Ocultar

Indica si la siguiente afirmación es verdadera o falsa.

Retroalimentación

Falso

Justo las funciones de agregado o resumen no se pueden incluir en la condición asociada a la cláusula WHERE.

3.3.- Cláusula ORDER BY.

Ocultar

En las consultas del apartado anterior hemos obtenido una lista de nombres de juegos, su dificultad y los megusta que tienen. Nos puede interesar que aparezcan ordenados de más amenos megusta, por ejemplo, o por orden alfabético del nombre del juego. De este modo, si necesitáramos localizar un registro concreto la búsqueda sería más rápida. ¿Cómo lo haremos? Para ello usaremos la cláusula ORDER BY.

Cláusula ORDER BY. Ordenando el resultado de la consulta.

ORDER BY se utiliza para especificar el criterio de ordenación de la respuesta a nuestra consulta.

El formato a seguir con ORDER BY es el siguiente:

<span class=" destacado_inline"><strong>[ORDER BY { NombreColumna | Expresión | Posición } [ASC | DESC]  ...]</strong> </span><br />

Después de cada elemento o ítem de ordenación se puede incluir el tipo de ordenación (ascendente ASC o descendente DESC ) utilizando las palabras reservadas ASC o DESC. Por defecto, y si no se pone nada, la ordenación es ascendente. (ASC)
Es posible ordenar por más de una columna. Es más, puedes ordenar no solo por columnas sino a través de una expresión creada con columnas, una constante (aunque no tendría mucho sentido) o funciones SQL.
Puedes colocar el número de orden del campo por el que quieres que se ordene en lugar de su nombre, es decir, referenciar a los campos por su posición en la lista de selección. (Empezando desde 1)

Ejemplo. Ordenando por una columna. El listado con el nombre del juego, su megusta y dificultad, para dificultad 'media' o 'baja', y ordenado de más a menos megusta sería el siguiente: (habría que añadir al final ORDER BY megusta DESC)

SELECT nombre, megusta, dificultad
FROM juego
WHERE dificultad="media" OR dificultad="baja"
ORDER BY megusta DESC;

Ilustración del resultado de una consulta ordenando por una columna — MySQL Workbench (Elaboración propia)

Ejemplo: Ordenando por dos columnas. En la consulta anterior podemos incluir el código del equipo y ordenar primero por los megusta de forma descendente (de más a menos) y después, en caso de empate, ascendentemente por el código de equipo.

Sería: (Ordenamos por megusta DESC y en caso de empate por cdequipo ASC:)

SELECT nombre, megusta, dificultad, cdequipo
FROM juego
WHERE dificultad="media" OR dificultad="baja"
ORDER BY megusta DESC, cdequipo;

Ilustración de una consulta con un ORDER BY de dos columnas — MySQL Workbench (Elaboración propia)

Ejemplo. Ordenando por la posición del campo en la SELECT.

La consulta anterior la podemos expresar de forma equivalente utilizando la posición que ocupa la columna en la SELECT y sería:

SELECT nombre, megusta, dificultad, cdequipo
FROM juego
WHERE dificultad="media" OR dificultad="baja"
ORDER BY 2 DESC, 4;

Observa que el campo megusta ocupa la posición 2 en la SELECT y cdequipo ocupa la posición 4.

Ejercicio Resuelto

Ocultar

Redacta las siguientes consultas sobre la base de datos campeonato:

Listado con todos los datos de los juegos, ordenados alfabéticamente por dificultad.
Listado con el nombre de cada concursante, su cuota, equipo y fecha de inscripción. Solo los concursantes con cuota mayor a 40 euros. Ordena el listado de más a menos cuota y a igualdad de cuota alfabéticamente.

3.4.- Cláusulas ALL/DISTINCT.

Ocultar

Ilustración de unos prismáticos sobre un papel — Everaldo Coelho and YellowIcon (GNU/GPL)

Ya has visto cómo hacer consultas sencillas sobre una base de datos. A continuación, verás otras cláusulas que permiten eliminar repeticiones y limitar el número de filas a mostrar.

Cláusulas ALL/DISTINCT. Mostrar todas las filas o eliminar repeticiones.

En el formato de SELECT podemos ver:

SELECT [ALL | DISTINCT | DISTINCTROW ] {Expresión | Columna1, ... | * }

La cláusula ALL.

ALL
Permite que se puedan presentar todas las filas afectadas por el resto de condiciones de la consulta aunque algunas filas estén repetidas. Es la opción por defecto.

Ejemplo. Listar el código de los concursantes que participan en juegos.

SELECT ALL cdconcur FROM participa;

(Muestra un listado de los códigos de los concursantes que participan en juegos. Si el concursante participa en más de un juego, su código aparecerá repetido, tal y como puedes ver en la siguiente imagen).

Ilustración del resultado de una consulta incluyendo ALL — MySQL Workbench (Elaboración propia)

La cláusula DISTINCT.

DISTINCT y DISTINCTROW
Cuando seleccionamos algunas columnas de una o varias tablas pueden mostrarse valores de filas repetidos, como hemos visto en el ejemplo anterior. Pero puede que esas repeticiones, a veces, no aporten información, sino que distorsionen el resultado que andamos buscando, por lo que sería más legible e intuitivo eliminarlas.
Esta cláusula, DSITINCT y DSITINCTROW permite que se puedan presentar todas las filas afectadas por el resto de condiciones de la consulta pero las repetidas aparecen sólo una vez.

Ejemplo. Listar el código de los concursantes que participan en juegos. Sólo nos interesa saber quienes participan en juegos, no en cuantos juegos.

SELECT DISTINCT cdconcur FROM participa;

(Muestra un listado de los códigos de los concursantes que participan en juegos. Si el concursante participa en más de un juego, con DISTINCT evitaremos que su código aparezca repetido).

Ejercicio resuelto

Ocultar

En la base de datos Campeonato:

Queremos consultar el código de los equipos en los que hay concursantes.
Lista el código, nombre y código de equipo de los concursantes del equipo '03' 0 '04'. Ordena el listado de forma ascendente, primero por equipo y luego por nombre.

Autoevaluación

Ocultar

Contesta si esta afirmación es verdadera o falsa.

Retroalimentación

Verdadero

Cierto. En un mismo juego pueden participar más de un concursante y obteniendo más de 10 puntos.

Retroalimentación

Verdadero

Cierto. Un mismo concursante puede haber iniciado su participación en varios juegos después de esa fecha.

3.5.- Cláusula LIMIT.

Ocultar

¿Qué hacemos si solamente nos interesa listar un número concreto de filas? Por ejemplo limitar el resultado de una consulta a 3, 10 o 15 filas, por ejemplo.

Para eso usaremos la cláusula LIMIT.

Cláusula LIMIT. Limitando las filas devueltas.

Siempre será la última cláusula a incluir en una SELECT. Su formato es el siguiente:

[LIMIT { [desplazamiento,] contador | contador OFFSET desplazamiento} ]

LIMIT
Permite limitar el número de filas devueltas. Se suele utilizar para no sobrecargar demasiado al servidor o a la aplicación que va a recibir los resultados de la consulta.

LIMIT puede admitir uno o dos parámetros:

Cuando se usa un solo dígito, lo que se corresponde con contador, indica el número de filas devueltas.
Cuando se usan dos dígitos separados por coma, el primero, desplazamiento, indica el desplazamiento de filas desde el comienzo, y el segundo, contador, el número de filas a recuperar o listar.

Ejemplos.

Listar las 5 primarias filas de la tabla concursante.
- SELECT * FROM concursante LIMIT 5; es equivalente a escribir: SELECT * FROM concursante LIMIT 0, 5; pues 0 indica que se desplace 0 filas desde el resultado y liste las siguientes 5 filas.

SELECT * FROM concursante LIMIT 5;   --  Devuelve las 5 primeras filas.

SELECT * FROM concursante LIMIT 0,5;    --  Devuelve las 5 primeras filas. (Se desplaza 0 filas, y devuelve las 5 primeras filas)

Ilustración del resultado de una consulta usando LIMIT — MySQL Workbench (Elaboración propia)

Listar 10 filas de la tabla concursante a partir de la fila 6, incluyendo la sexta.
- SELECT * FROM concursante LIMIT 5,10; se desplaza 5 filas desde el resultado y lista las siguientes 10 filas, incluida la fila 6.

SELECT * FROM concursante LIMIT 5,10;   -- Devuelve a partir de fila 5, las 10 siguientes

Ilustración del resultado de una consulta usando LIMIT con un desplazamiento — MySQL Workbench (Elaboración propia)

Ejercicio Resuelto

Ocultar

En la base de datos campeonato:

Listar el código, nombre y megusta de cinco de los juegos con más megusta.
Listar los datos de 7 de los concursantes con mayor cuota.

3.6.- Cláusulas GROUP BY y HAVING.

Ocultar

Y si quisiera, por ejemplo, obtener el total de juegos por dificultad. ¿Cómo podría hacerlo? Para ello hay que hacer uso de funciones especiales de agrupamiento y de la cláusula GROUP BY.

Piensa lo que harías manualmente para obtener ese resultado. Seguro que estás pensando en contar en la tabla juego, para cada dificultad diferente, el total de filas que aparecen en esa tabla.

¿Cómo crees que se podría realizar en SQL? Pues de forma algo similar, utilizando la cláusula que permite agrupar registros por valores que se repiten de forma vertical (en columnas), de las filas de la tabla.

Si observas la siguiente imagen de la tabla juego, con diferente color, se resaltan los grupos que harías: un grupo para dificultad alta, otro para dificultad baja y otro para dificultad media, y contarías cuantas veces aparece cada uno. Pues eso es lo que hace la cláusula GROUP BY.

Ilustración de una tabla resaltando la forma de agrupar registros — MySQL Workbench (Elaboración propia)

Cláusula GROUP BY. Agrupando filas.

El formato a seguir con GROUP BY es el siguiente:

[GROUP BY {NombreColumna | Expresión | Posición} …]

GROUP BY
Es posible agrupar las filas que devuelve una consulta según los valores de una columna, usando la cláusula GROUP BY. Aunque en algunos casos podríamos hacer esto mismo usando la opción DISTINCT, esta cláusula es más potente y presenta las siguientes diferencias:

La salida se ordena según la columna indicada.
Se eliminan los valores duplicados aunque la consulta no devuelva filas duplicadas.
Permite usar funciones de resumen o agregado. Por ejemplo: COUNT(), SUM(), MAX(), MIN(), AVG(), STD(), VARIANCE().

Estas funciones también se pueden usar sin la cláusula GROUP BY siempre que en la consulta no se seleccionen otras columnas. Estas consultas se denominan consultas de resumen y las veremos en esta unidad.

Esta cláusula GROUP BY, combinada con las funciones de agregado anteriores, permite hacer cálculos sobre una serie de filas agrupadas. A estas consultas las denominaremos consultas agrupadas y se verán en un apartado posterior.

Ejemplo. Para obtener el total de juegos por dificultad, sería:

SELECT dificultad, count(*) 'Total Juegos'
FROM juego
GROUP BY dificultad;

Ilustración del resultado de una consulta agrupando juegos por dificultad — MySQL Workbench (Elaboración propia)

Cláusula HAVING. Filtrando grupos.

El formato a seguir con HAVING es el siguiente:

[HAVING Condiciones]

HAVING
Permite hacer selecciones o filtros de grupos, en situaciones en las que no es posible usar WHERE. La cláusula WHERE no se puede aplicar a columnas calculadas mediante las funciones de agrupamiento que hemos mencionado en el apartado anterior. Tanto la cláusula GROUP BY como HAVING se tratarán más adelante en las consultas agrupadas.

Pero a título de ejemplo, veamos el siguiente.

Ejemplo. Para obtener el total de juegos por dificultad, pero listando solo si hay más de 3 juegos, sería:

SELECT dificultad, count(*) 'Total Juegos'
FROM juego
GROUP BY dificultad
HAVING count(*) >3;

Ilustración del resultado de una consulta agrupando juegos por dificultad y filtrando con HAVING — MySQL Workbench (Elaboración propia)

Autoevaluación

Ocultar

Indica si la siguiente afirmación sobre HAVING es verdadera o falsa.

Retroalimentación

Verdadero

Filtramos grupos con HAVING en situaciones en las que no es posible usar WHERE. La cláusula WHERE no se puede aplicar a columnas calculadas mediante las funciones de agrupamiento

4.- Operadores.

Caso práctico

Ocultar

Ilustración de María, que junto con Félix son los propietarios de la empresa. — Nate Steiner (CC0)

María, que junto con Félix son los propietarios de la empresa.La BK Sistemas Informáticos, se ha interesado por la marcha del proyecto que desarrollan Noiba y Vindio. Éstos le comentan que están preparando una batería de consultas, según requerimientos del cliente del taller mecánico. Ahora están revisando los operadores que se pueden usar dentro de las expresiones en el SGBD que está utilizando, en este caso MySQL, y que le ayudarán, entre otras cosas, a establecer condiciones de búsqueda básicas.

Ocultar

Aunque ya hemos visto en la unidad anterior algunos operadores que pueden intervenir en una sentencia SQL, dado que en la mayor parte de los casos se incluyen en sentencias de tipo SELECT, vamos a ampliar en este apartado los tipos de operadores.

Con el lenguaje SQL podemos utilizar muchos operadores diferentes para cada tipo de columna. Veremos los operadores de que dispone MySQL, pero la mayoría de ellos son compatibles con otros SGBD. Pero ¿qué es un operador?

Ocultar

Los operadores son símbolos que permiten realizar operaciones matemáticas, concatenar cadenas o hacer comparaciones, entre otras funciones

Los operadores se utilizan para construir expresiones que se usan en cláusulas WHERE, ORDER BY y HAVING y además se pueden emplear directamente en las sentencias.

Debes conocer

Ocultar

Con frecuencia utilizaremos la sentencia SELECT acompañada de expresiones muy extensas y resultará difícil saber que parte de dicha expresión se evaluará primero, por ello es conveniente conocer el orden de precedencia en MySQL.

Para consultar el orden de prioridad de los operadores disponibles en MySQL pincha en el siguiente enlace:

Orden de prioridad de los operadores.

Reflexiona

Ocultar

En caso de que tengas que combinar varios operadores en una misma expresión es aconsejable que utilices tantos paréntesis como consideres necesario para que éstos se evalúen en el orden que te interese. De este modo evitarás sorpresas desagradables.

Para saber más

Ocultar

Si quieres connocer todos los operadores que se pueden usar en MySQL consulta el siguinte enlace:

Operadores en MySQL

4.1.- Operador de asignación y Operadores aritméticos.

Ocultar

Ilustración de varios discos y un signo mas verde — Everaldo Coelho and YellowIcon (GNU/GPL)

Sabemos que los operadores son símbolos que permiten realizar distintos tipos de operaciones. Vamos a verlos.

Operador de asignación.

En MySQL podemos crear variables de sesión y usarlas posteriormente en expresiones. Podemos crear variables de dos formas:

Con SET y un símbolo =
Ejemplo:

SET @hoy= CURRENT_DATE();
SELECT @hoy;

(Veremos la fecha actual)

Con SET y utilizando el operador de asignación :=
Ejemplo:

SET @X:=10;
SELECT @X;

(Veremos el valor almacenado 10)

Una variable sin asignar será de tipo cadena y su valor será NULL.(ausencia de valor)

Operadores aritméticos.

Los operadores aritméticos se aplican a valores numéricos, ya sean enteros o en coma flotante. El resultado siempre es un valor numérico, entero o en coma flotante.
MySQL dispone de los operadores aritméticos habituales: suma, resta, multiplicación, división, potencia, etc.

Operadores aritméticos y significado
Operador	Significado
+	Se utiliza para sumar dos números y, como operador unario, para simbolizar signo positivo de un número.
-	Se utiliza para hallar la diferencia entre dos números y, como operador unario, para simbolizar signo negativo de un número.
*	Se utiliza para multiplicar dos números.
/	Se utiliza para dividir dos números.
^	Se utiliza para elevar un número a la potencia del exponente (número ^ exponente).
\	Se utiliza para dividir dos números y el resultado cociente en forma de entero (división entera) entero.
% o <b>mod</b>	Dividen dos números y devuelven el resto entero de la división.
-	Se obtiene un valor de signo contrario.

El orden de precedencia, entre los operadores artiméticos, es el siguiente:

Exponenciación.
Operadores de signo.
Multiplicación y división.
Suma y resta.

Ejemplo:

El resultado de la siguiente operación: 10 + 6 / 2 es 13 (primero se divide 6/2 y a ese resultado se suma 10).
El resultado de la siguiente operación: (10+6) / 2 es 8 (primero se resuelve el paréntesis (10+6) y ese resultado se divide entre 2.

Por tanto, los paréntesis permitirán forzar el orden de evaluación de una expresión.

Primero se comienza resolviendo los paréntesis si los hay, de izquierda a derecha.
Si hay paréntesis anidados, se comienza a resolver por más interno hacia fuera.

Autoevaluación

Ocultar

Solución

4.2.- Operadores de comparación.

Ocultar

Ilustración de dos tabletas una sobre otra — Everaldo Coelho and YellowIcon (GNU/GPL)

Los puedes conocer con otros nombres como relacionales, nos permitirán comparar expresiones, que pueden ser valores concretos de campos, variables, etc.

Los operadores de comparación son símbolos que se usan como su nombre indica para comparar dos valores. Estos operadores devuelven los valores lógicos de verdadero o falso. (1 ó 0). A esta expresiones cuyo resultado de evaluación es verdadero o falso se les denomina expresiones lógicas

Estos operadores son habituales en cualquier lenguaje de programación, pero algunos lenguajes como SQL añade algunos muy útiles que se usan frecuentemente.

Operadores de comparación y significado
Operador	Significado
< =	Menor o igual.
<	Menor.
>	Mayor.
> =	Mayor o igual
=	Compara dos expresiones y devuelve 1, sin son iguales y 0, si son diferentes.
< =>	Funciona como =, salvo que si una de las dos expresiones o las dos es nula, el resultado no es NULL. Si se comparan dos expresiones nulas, el resultado es verdadero (1).
< > ó !=	Si las expresiones comparadas son diferentes, el resultado es verdadero, y si son iguales, el resultado es falso.

Estos operadores tienen todos ellos la misma prioridad.

Comparación de cadenas de caracteres y de fechas.

Los operadores relacionales pueden usarse para comparar cadenas.

Cuando se comparan cadenas, se considera menor la cadena que aparezca antes por orden alfabético no distinguiendo entre mayúsculas y minúsculas, a no ser que se realicen sobre campos definidos con el atributo BINARY.
Las cadenas constantes se han de expresar encerradas entre comillas dobles o simples.
Si son fechas, se considera menor cuánto más antigua sea.

Autoevaluación

Ocultar

Solución

4.3.- Operadores lógicos.

Ocultar

Ilustración de una hoja con ceros y unos — Everaldo Coelho and YellowIcon (GNU/GPL)

Habrá ocasiones en las que tengas que evaluar más de una expresión y necesites verificar que se cumple una única condición, otras veces comprobar si se cumple una u otra o ninguna de ellas. Para poder hacer esto utilizaremos los operadores lógicos.

Los operadores lógicos se usan para crear expresiones lógicas complejas.

Solo existen dos valores posibles para los resultados: verdadero y falso, pero MySQL añade un tercer valor: desconocido para poder trabajar con valores NULL.

Valores lógicos
1	<b>TRUE</b>
0	<b>FALSE</b>
desconocido	<b>NULL</b>

Operadores lógicos y significado
Operador	Función
<b>AND</b> ó <b>&& </b>	Devuelve el valor TRUE cuando las dos condiciones son verdaderas y FALSE si alguna de ellas es falsa.
<b>OR</b> ó \|\|	Devuelve el valor TRUE si cualquiera de las dos condiciones es verdadera y FALSE cuando las dos condiciones son falsas
<b>NOT</b> ó <b>!</b>	Devuelve lo opuesto a la condición que sigue a NOT. Si el operador es NULL devuelve NULL.
<b>XOR</b>	Devuelve NULL si cualquiera de los operadores es NULL. Cuando uno de los operadores es verdadero, devuelve TRUE y si ambos son verdaderos o falsos devuelve FALSE.

Todos estos operadores tienen menor precedencia en las expresiones que los operadores aritméticos. Entre ellos, el orden de precedencia es el siguiente:

Operadores de comparación.
Operador NOT.
Operador AND.
Operador OR.

Al igual que todos los operadores binarios que veremos, estos operadores se pueden asociar, es decir, se pueden crear expresiones como:

A AND B AND C.
- El hecho de que se requieran dos operandos significa que las operaciones se realizan tomando los operandos dos a dos, y estas expresiones se evalúan de izquierda a derecha, teniendo en cuenta la prioridad de cada operador. Primero se evalúa A AND B, y el resultado, R, se usa como primer operando de la siguiente operación R AND C.
A AND B OR C.
- Como AND tiene mayor prioridad que OR, primero se evalúa A AND B, y el resultado R, se usa como primer operando de la siguiente operación R OR C.
A OR B AND C.
- Como AND tiene mayor prioridad que OR, primero se evalúa B AND C, y el resultado R, se usa como segundo operando de la operación A OR R.
A AND B AND (B OR C).
- Para forzar el orden de evaluación según interese se usan paréntesis. En este caso, lo primero que se evalúa es el paréntesis B OR C, obteniendo un resultado R1, y ahora de izquierda a derecha se evalúa la expresión A AND B AND R1, primero A AND B obteniendo R2 y después R2 AND R1.

Te mostramos a continuación algunos ejemplos prácticos de todo esto.

Ejemplo 1.

Listado de los concursantes cuya cuota de inscripción sea mayor que 20€ y menor o igual a 100€ , y su equipo es el '04'

SELECT *
FROM concursante
WHERE cuota_inscri>20 AND cuota_inscri<=100 AND cdequipo='04';

Ilustración del resultado de una consulta con un WHERE usando el operador NAD — MySQL Workbench (Elaboración propia)

Ejemplo 2.

Listado de los concursantes cuya cuota de inscripción es mayor que 20€ y menor o igual a 100€, y el equipo puede ser el '04' o el '02'.

SELECT *
FROM concursante
WHERE cuota_inscri>20 AND cuota_inscri<=100 
AND (cdequipo='04' OR cdequipo='02');

Observa el uso del paréntesis para forzar el orden de evaluación de los operadores AND y OR. Primero se evalúa el paréntesis comprobando que el equipo sea el '02' o el '04' y después se enlaza con AND, para que de forma simultánea se cumpla que la cuota está en el rango especificado.

Ilustración del resultado de una consulta con filtro WHERE usando AND y OR — MySQL Workbench (Elaboración propia)

Ejercicio resuelto

Ocultar

En la base de datos Campeonato, consultar los datos de los concursantes inscritos antes del 2020, con cuota menor a 100 euros y cuyo equipo sea el '02' o '03':

En la base de datos campeonato obtén las siguientes consultas:

Todos los datos de los juegos con dificultad alta o media y con 3 o más megusta.
Nombre, comunidad y año de fundación de los equipos de Andalucía o Murcia, con año fundación posterior al 2018.

4.4.- Operadores especiales. LIKE, BETWEEN,IN().

Ocultar

Ilustración de un dibujo, un pincel y tinta amarilla. — Everaldo Coelho (YellowIcon) (GNU/GPL)

En MySQL disponemos de varios operadores adicionales que vemos a continuación.

Comparación con patrones de cadena. LIKE.

LIKE y NOT LIKE

Se utilizan en comparación de cadenas con las construcciones WHERE y HAVING.
Para buscar cadenas que coincidan con una cadena patrón, se usa la cláusula LIKE dentro de la cláusula WHERE y unos comodines para crear el patrón de búsqueda..
Tanto si la cadena como el patrón son NULL, el resultado es NULL.
LIKE no distingue entre mayúsculas y minúsculas.

La sintaxis de LIKE es:

WHERE Expresión [NOT] LIKE patrón

En SQL de MySQL tenemos dos caracteres especiales que pueden aparecer en cualquier posición del patrón:

Comodines y significado
Comodín	Significado
%	Representa a cualquier cadena de 0 o más caracteres.
_	Representa a un carácter cualquiera.

Te mostramos algunos ejemplos.

Ejemplo 1. Listar el nombre y código de equipo de los concursantes cuyo nombre comience por la letra M.

SELECT nombre, cdequipo 
FROM concursante 
WHERE nombre LIKE “M%”;

Ilustración del resultado de una consulta utilizando LIKE M% — MySQL Workbench (Elaboración propia)

Ejemplo 2. Listar el nombre y código de equipo de los concursantes cuyo nombre contiene la palabra 'rojo'.

SELECT nombre, cdequipo 
FROM concursante 
WHERE nombre LIKE '%rojo%';

Ilustración del resultado de una consulta usando LIKE '%ROJO%' — MySQL Workbench (Elaboración propia)

Ejemplo 3. Listar el nombre y código de equipo de los concursantes cuyo nombre contiene como segunda letra una E.

SELECT nombre, cdequipo 
FROM concursante 
WHERE nombre LIKE'_E%';

Ejemplo 4.- Listado de concursantes cuyo nombre no contiene la palabra 'Verde', basta con usar NOT LIKE patrón.

SELECT nombre, cdequipo 
FROM concursante 
WHERE nombre NOT LIKE'%Verde%';

Reflexiona

Ocultar

¿Cómo buscaríamos en una cadena el carácter % ó el _?

Ocultar

Operador de rango. BETWEEN.

BETWEEN y NOT BETWEEN

Sirve para comprobar si una expresión está comprendida o no en un determinado rango de valores. La sintaxis es:

Ocultar

WHERE Expresión [NOT] BETWEEN ValorInicial AND ValorFinal.

Ocultar

ValorInicial debe ser menor que el ValorFinal.
El tipo de dato puede ser cualquiera de los usuales soportados por MySQL.
Es equivalente a: Expresion >= ValorInicial AND Expresion<= ValorFinal

Ejemplo 1. Obtener un listado con el nombre y las cuotas de concursantes, para aquellos cuya cuota está entre 50 y 100 euros, incluido el 50 y 100.

SELECT nombre, cuota_inscri
FROM concursante
WHERE cuota_inscri BETWEEN 50 AND 100;

También podríamos haber escrito:

SELECT nombre, cuota_inscri
FROM concursante
WHERE cuota_inscri >= 50 AND cuota_inscri<=100;

Ejemplo 2. Listado de concursantes que se inscribieron entre los años 2019 y 2021.

SELECT *
FROM concursante
WHERE fecha_inscri BETWEEN '2019-01-01' AND '2021-12-31';

Ejemplo 3. Y si queremos justo lo contrario, concursantes inscritos en años fuera del periodo 2019 - 2021, basta con negar el criterio anterior.

SELECT *
FROM concursante
WHERE fecha_inscri NOT BETWEEN '2019-01-01' AND '2021-12-31';

y si además, supiéramos de la existencia de la función YEAR(), que devuelve el año de la fecha que se le pasa como parámetro, podrías resolver estas consultas de la forma:

SELECT *
FROM concursante
WHERE YEAR(fecha_inscri) BETWEEN 2019 AND 2021;
-- 
SELECT *
FROM concursante
WHERE YEAR(fecha_inscri) NOT BETWEEN 2019 AND 2021;

Las funciones que nos proporciona MySQL las veremos en una apartado posterior. Ahora es solamente para ilustrar cómo podemos ir integrándolas en las consultas.

Reflexiona

Ocultar

Observa la diferencia entre las dos soluciones del Ejemplo 1:

Con BETWEEN la expresión que se evalúa se escribe una sola vez.
En el segundo ejemplo las expresiones a ambos lados de AND deben escribirse completas.

Ocultar

Operador de pertenencia a un conjunto. IN().

IN, NOT IN

El operador IN devuelve 1 (verdadero) si el valor de la expresión es igual a alguno de los valores de la lista y falso en caso contrario. El operador NOT IN devuelve 1 (verdadero) si el valor de la expresión no está en la lista.

La sintaxis es:

Ocultar

WHERE Expresión IN [NOT IN] (Valor1, Valor2, ……. )

Ocultar

Los valores dentro de los paréntesis pueden ser de cualquier tipo (números, fechas, caracteres, fechas, etc.), pero todos ellos del mismo tipo.
Es equivalente a la expresión formulada con OR: Expresion = valor1 OR Expresion=valor2 OR Expresion=valor3 ...

Ejemplo 1. Listado de los concursantes cuyo equipo sea el '01', '02' o el '04'.

SELECT *
FROM concursante
WHERE cdequipo IN('01', '02','04');

También podríamos haber escrito:

SELECT *
FROM concursante
WHERE cdequipo = '01' OR cdequipo ='02' OR cdequipo ='04';

Ejemplo 2. Listado de los concursantes cuyo equipo no es el '01', '02' ni el '04'.

SELECT *
FROM concursante
WHERE cdequipo NOT IN('01', '02','04');

Basta con negar con NOT la condición anterior.

Ejercicio Resuelto

Ocultar

En la base de datos campeonato, realiza las siguientes consultas:

Todos los datos de los concursantes con fecha de inscripción entre el 1 de enero de 2019 y el 31 de diciembre de 2019, incluidos esos límites, ordenado el listado de fecha más reciente a más antigua.
Todos los datos de los equipos cuya comunidad no es Madrid ni Murcia.
Todos los datos de los juegos que empiezan por la letra G.

Autoevaluación

Ocultar

Las siguientes consultas ¿son equivalentes?

Retroalimentación

Falso

Solamente son equivalentes las consultas 2.- y 3.-

5.- Tratamiento de valores nulos.

Caso práctico

Ocultar

Ilustración de Naroba, alumna que realiza la FCT en el departamento de informática de la empresa — Alain Bachellier (CC BY-NC-SA)

En este punto Noiba plantea revisar el tratamiento que se hace en los SGBD de los valores nulos. Ella sabe que no es lo mismo que un campo contenga el valor nulo o que contenga un cero.

Conviene que nos detengamos con ella en este punto porque de no hacerlo así no podríamos diferenciar cuando el valor de un dato en una columna no ha sido introducido y por eso lo desconocemos, de cuando ese dato existe y es un cero o un espacio en blanco.

Ocultar

¿Cómo indicamos que no hay valor en un determinado campo de una tabla? Lo haremos con un valor nulo.

Datos y nulos

Un dato es una unidad de información con sentido que se almacena en un campo. Dependiendo del contenido de la información almacenada, los datos pertenecen a diferentes tipos: cadenas de caracteres, fechas, o números, etc.

Un valor nulo es un valor que no es asignable a ningún tipo de datos y que significa que no se ha definido ningún valor inicial para ese dato, es decir que el valor de ese dato es desconocido.

Un cero o un espacio en blanco es un valor que se ha asignado a un campo, por tanto el campo tiene un contenido: el cero o el espacio en blanco.

Ocultar

Se dice que el contenido de una columna para un elemento es NULL si está completamente vacía, no se cargó en su momento ningún valor.

Este valor es compatible con todos los tipos de datos y significa ausencia de valor.

Ocultar

Comprobación de valor nulo.

IS NULL, IS NOT NULL

Para comprobar si en una columna hay un valor nulo se utiliza la expresión:

NombreColumna IS NULL

Ejemplo.

La siguiente consulta muestra los datos de los concursantes que no pertenecen a ningún equipo.

SELECT *
FROM concursante
WHERE cdequipo IS NULL;

Por el contrario, para comprobar si en una columna hay algún valor, se emplea la expresión:

NombreColumna IS NOT NULL

Ejemplo.

La siguiente consulta muestra los datos de los concursantes que si pertenecen a algún equipo.

SELECT *
FROM concursante
WHERE cdequipo IS NOT NULL;

Debes conocer

Ocultar

Los operadores aritméticos con valores nulos devuelven un valor nulo.

Autoevaluación

Ocultar

Solución

6.- Funciones.

Caso práctico

Ocultar

Noiba y Vindio han reunido información sobre los distintos tipos de operadores que pueden intervenir en las consultas SQL. A menudo estos operadores intervienen en expresiones construidas también con funciones, por eso considera importante consultar la documentación relativa a las funciones que podrían ser de utilidad. Dado que MySQL dispone de multitud de funciones no considera necesario conocerlas todas. Siguiendo sus pasos nos conformaremos con agruparlas por tipos y ver las más comunes. Para el resto de funciones incluiremos el enlace correspondiente.

Ocultar

¿Has pensado en todas las operaciones que puedes realizar con los datos que guardas en una base de datos? Seguro que son muchísimas. Pues bien, en casi todos los Sistemas Gestores de Base de Datos existen funciones ya creadas que facilitan la creación de consultas más complejas. Dichas funciones varían según el SGBD, veremos aquí las que utiliza MySQL.

Las funciones son realmente operaciones que se realizan sobre los datos y que realizan un determinado cálculo. Para ello necesitan unos datos de entrada llamados parámetros o argumentos y en función de éstos, se realizará el cálculo de la función que se esté utilizando. Normalmente los parámetros se especifican entre paréntesis.

Las funciones se usan dentro de expresiones y actúan sobre los valores de las columnas de las tablas, con variables o con constantes. Se utilizan en cláusulas SELECT, WHERE y ORDER BY.

Las funciones pueden producir dos tipos de resultados:

Modificar la información original.
Indicar alguna información sobre el elemento al que se aplica.

Podemos clasificar las funciones en varios tipos:

Funciones aritméticas:

Estas funciones trabajan con datos de tipo numérico.
Los datos de tipo numérico incluyen los dígitos del 0 al 9, el punto decimal, y el signo menos.
Los literales numéricos no se encierran entre comillas.

Funciones de cadena de caracteres:

Estas funciones trabajan con datos de tipo carácter.
Este tipo de datos incluyen cualquier carácter alfanumérico: letras, números y caracteres especiales.
Se deben encerrar entre comillas simples.
Mediante estas funciones podremos calcular el número de caracteres de una cadena, convertir una cadena a mayúsculas o a minúsculas, añadir o eliminar caracteres a la izquierda o a la derecha, etc.

Funciones de manejo de fechas y horas:

En MySQL existen numerosas funciones para trabajar con las fechas y las horas.
Por defecto el formato de almacenamiento de las fechas es AAAA-MM-DD y de las horas HH:MM:SS pero existen muchas funciones que nos permiten descomponer la fecha y la hora, obtener la fecha y hora actuales, etc.

Funciones de comparación:

Se trata de funciones que comparan los valores de cada una de las columnas en el interior de una fila para obtener: el menor o el mayor valor de ellos, así como comprobar si el valor de una columna es nulo.

Debes conocer

Ocultar

En el siguiente documento te presentamos un resumen de las funciones de cada tipo que se utilizan con más frecuencia en MySQL y algunos ejemplos de aplicación:

Resumen de las funciones más frecuentes en MySQL. (pdf - 262,21 KB)

Autoevaluación

Ocultar

Retroalimentación

Verdadero

MONTHNAME() devuelve el nombre del mes de la fecha pasada y YEAR() devuelve el año de la fecha pasada como parámetro.

Para saber más

Ocultar

Como hemos dicho, existen muchas funciones que pueden aplicarse a las columnas de una tabla. En algunas herramientas gráficas se encuentran accesibles todas las funciones disponibles agrupadas por categorías.

Para consultar la documentación relativa a todas las funciones ya implementadas en MySQL puedes acceder a lsiguiente enlace:

Funciones internas para MySQL.

7.- Consultas de resumen.

Caso práctico

Ocultar

-- 1 SELECT j.cdjuego, j.nombre 'juego', j.dificultad, e.cdequipo 'equipo', e.nombre FROM juego j, equipo e WHERE j.cdequipo=e.cdequipo; -- 2 SELECT j.cdjuego, j.nombre 'juego', j.dificultad, e.cdequipo 'equipo', e.nombre FROM juego j, equipo e WHERE j.cdequipo=e.cdequipo AND j.dificultad='baja'; -- 3 SELECT c.cdconcur, c.nombre 'concursante', p.cdjuego, j.nombre 'juego' FROM concursante c, participa p, juego j WHERE c.cdconcur=p.cdconcur AND p.cdjuego=j.cdjuego; — Alain Bachellier (CC BY-NC-SA)

Noiba está reuniendo información sobre los distintos tipos de consulta que se pueden realizar sobre las tablas de la base de datos Talleres Faber. En este momento veremos cómo obtener resúmenes de los datos aplicando sobre las columnas una serie de funciones de grupos. Estas funciones van a permitir calcular, por ejemplo, el precio medio de los recambios, el número de vehículos que visitan el taller en un mes, el tiempo máximo que dura una reparación, etc.

Está claro que se trata de cuestiones que pueden tener mucho interés para el taller mecánico. Veamos cómo obtener esos datos

Ocultar

La sentencia SELECT nos va a permitir obtener resúmenes de los datos de modo vertical. Para ello consta de una serie de cláusulas específicas (GROUP BY, HAVING) y tenemos también unas funciones llamadas de agrupamiento o de agregado que son las que nos dirán qué cálculos queremos realizar sobre los datos (sobre la columna).

Hasta ahora las consultas que hemos visto daban como resultado un subconjunto de filas de la tabla de la que extraíamos la información. Sin embargo, este tipo de consultas que vamos a ver no corresponde con ningún valor de la tabla sino un total calculado sobre los datos de la tabla. Esto hará que las consultas de resumen tengan limitaciones que iremos viendo.

Las funciones que podemos utilizar se llaman de agrupamiento (de agregado). Éstas toman un grupo de datos (una columna) y producen un único dato que resume el grupo. Por ejemplo, la función SUM() acepta una columna de datos numéricos y devuelve la suma de estos.

Las funciones de grupos de valores ignoran los valores nulos a la hora de realizar los cálculos.

Estas funciones se muestran en la siguiente tabla:

Funciones de agragado y descripción
FUNCIONES DE COLUMNA	Descripción
COUNT(Expresión o Columna)	Cuenta el número de valores no nulos de la columna que aparece como argumento. Si ponemos * cuenta las filas que tengan valor no nulo.
SUM(Expresión o Columna)	Calcula la suma de los valores numéricos indicados en el argumento.
MIN(Expresión o Columna)	Obtiene el valor mínimo del argumento indicado.
MAX(Expresión o Columna)	Obtiene el valor máximo del argumento indicado.
AVG(Expresión o Columna)	Obtiene la media aritmética del argumento indicado. No considera los valores nulos.

Ocultar

El argumento de estas funciones suele ser un nombre de columna.

El simple hecho de utilizar una función de agregado en una consulta la convierte en consulta de resumen.

Ocultar

Todas las funciones tiene una sintaxis similar a la siguiente:

Ocultar

FUNCION ([DISTINCT | ALL] expresión)

Ocultar

Debemos tener en cuenta que:

La palabra ALL indica que se tienen que tomar todos los valores de la columna. Es el valor por defecto.
La palabra DISTINCT indica que se considerarán todas las repeticiones del mismo valor como uno solo (considera valores distintos).
El grupo de valores sobre el que actúa la función lo determina el resultado de la expresión que será el nombre de una columna o una expresión basada en una o varias columnas. Por tanto, en la expresión nunca puede aparecer ni una función de agregado ni una subconsulta.
Todas las funciones se aplican a las filas del origen de datos una vez ejecutada la cláusula WHERE (si la tuviéramos).
Todas las funciones (excepto COUNT) ignoran los valores NULL.
Podemos encontrar una función de agrupamiento dentro de una lista de selección en cualquier sitio donde pudiera aparecer el nombre de una columna. Es por eso que puede formar parte de una expresión pero no se pueden anidar funciones de este tipo.
No se pueden mezclar funciones de columna con nombres de columna ordinarios, aunque hay excepciones que veremos más adelante.

Reflexiona

Ocultar

Analiza las siguientes consultas:
SELECT COUNT(DISTINCT cdequipo) 'Equipos' FROM concursante; SELECT DISTINCT COUNT(cdequipo) 'Equipos' FROM concursante;

¿De qué forma interviene la cláusula DISTINCT en ambas?

Ejercicio resuelto. Consultas resumen I.

Ocultar

Realiza las siguientes consultas en la base de datos campeonato:

Valor medio de los megusta de los juegos.
Total de concursantes que iniciaron juegos en Enero.
Cuota máxima de lo concursantes.

Ejercicio resuelto. Consultas resumen II.

Ocultar

Realiza las siguientes consultas en la base de datos campeonato:

Total de consursantes y la cuota media.
Total de consursantes que tienen equipo.
Total de comunidades en las que hay equipos.
Fecha más reciente con inscripción de concursantes

Retroalimentación

-- 1
SELECT COUNT(*) 'Total concursantes', AVG(cuota_inscri) 'cuota media'
FROM concursante;

-- 2
SELECT COUNT(*) 'Concursantes con equipo'
FROM concursante
WHERE cdequipo IS NOT NULL;

-- o bien
SELECT COUNT(cdequipo) 'Concursantes con equipo'
FROM concursante;

-- 3
SELECT COUNT(DISTINCT comunidad) 'Comunidades'
FROM equipo;

-- 4
SELECT MAX(fecha_inscri) ' Fecha últimas inscripcciones'
FROM concursante;

Observa que en la consulta 1.-, la cuota media la puedes redondear con 2 decimales, si usas la función ROUND().
Observa que en la consulta 4.- puedes formatear la fecha de manera apropiada usando la función date_format()

-- 1
SELECT COUNT(*) 'Total concursantes', ROUND(AVG(cuota_inscri),2) 'cuota media'
FROM concursante;

-- 4 
SELECT DATE_FORMAT(MAX(fecha_inscri), '%d/%m/%Y') ' Fecha últimas inscripcciones'
FROM concursante;

8.- Consultas agrupadas.

Caso práctico

Ocultar

magen Ilustración de Juan, responsable del departamento de informática. — Jonny Goldstein (CC BY)

Cuando hemos recorrido con Noiba y Vindio las cláusulas de la sentencia SELECT, hemos visto que podíamos utilizar GROUP BYpara agrupar filas yHAVINGpara establecer condiciones en las filas agrupadas. Combinando estas cláusulas con las funciones de grupo del apartado anterior, Juan les dice a Noiba y Vindio que podrán encontrar soluciones a cuestiones que hasta ahora no podían resolver y que pueden ser útiles.

Por ejemplo, saber cuántos vehículos de cada marca visitan el taller podría resultar interesante a la hora de especializar a los mecánicos en determinados motores.

Ocultar

En el apartado anterior hemos usado funciones de grupo para calcular totales, obteniendo un único dato resumen. Las consultas agrupadas nos permitirán realizar cálculos de grupo sobre filas que tienen un valor coincidente en una o varias columnas, y obtener subtotales.

Como ejemplo, observa las siguientes imágenes:

A la izquierda se muestran todos los juegos con su código, nombre, dificultad y equipo organizador. ¿Cómo podemos obtener para cada equipo el total de juegos que organiza? Para ello hay que agrupar (uso de GROUP BY) por equipo y para cada equipo contar los juegos que tiene. (Observa que es contar las filas en las que aparece repetido el código de equipo)
A la derecha se muestra para cada equipo el total de juegos que organiza (Consulta agrupada).

Ilustración de una consulta a la tabla de juegos — Workbench (Elaboración propia)

Ilustración de una consulta agrupada — Workbench (Elaboración propia)

Imagina que en la base de datos de campeonato queremos saber, por ejemplo, ¿cuántos concursantes hay de cada equipo? Hasta ahora lo que podíamos hacer era mostrar las filas de la tabla concursante, ordenadas por cdequipoy contar visualmente cuántas filas había por cada equipo, de la siguiente forma:

Ejemplo 1. Listado de los equipos de los concursantes ordenado por código de equipo.

SELECT cdequipo
FROM concursante
ORDER BY cdequipo;

¿Y si la tabla tuviera miles de filas? No podríamos hacerlo así. Es el momento de recurrir a la cláusula GROUP BY.

Seguidamente se obtiene un listado con dos columnas: El código de equipo y el número de concursantes de ese equipo. La consulta devolverá una fila por cada cdequipo diferente, con el código del equipo y el número de concursantes de ese equipo. El listado estará ordenado por cdequipo.

SELECT cdequipo AS 'equipo', count(*) AS 'concursantes' 
FROM concursante
GROUP BY cdequipo 
ORDER BY cdequipo;

En el listado obtenido observarás que aparece una fila con valor NULL para cdequipo y con 3 concursantes.

Ilustración del resultado de una consulta usando GROUP BY — MySQL Workbench (Elaboración propia)

Eso significa en nuestra base de datos que hay 3 concursantes sin equipo asignado. Si nos interesara eliminar del listado a los concursantes sin equipo ¿Cómo lo hacemos? Incluyendo un filtro WHERE de la tabla, para que no se considere el valor NULL de la columna cdequipo. La consulta sería:

SELECT cdequipo AS 'equipo', count(*) AS 'concursantes' 
FROM concursante
WHERE cdequipo IS NOT NULL
GROUP BY cdequipo 
ORDER BY cdequipo;

Ejemplo 2.

Imagina que nos interesa filtrar ese listado obtenido anteriormente con GORUP BY de manera que sólo aparezca el código de los equipos con 4 o más concursantes. ¿Cómo lo hacemos? En este caso no se usa WHERE, lo que se utiliza es la cláusula HAVING que es la que permite filtrar los grupos obtenidos con GROUP BY , quedando por lo tanto la consulta de la siguiente forma:

SELECT cdequipo AS 'equipo', count(*) AS 'concursantes' 
FROM concursante
WHERE cdequipo IS NOT NULL
GROUP BY cdequipo 
HAVING count(*)>=4
ORDER BY cdequipo;

Tanto GROUP BY como HAVING se usan en consultas de resumen. La sintaxis de estas consultas es:

Ocultar

SELECT NombreColumna,…, Función,…

FROM NombreTabla

[WHERE Condiciones…..]

GROUP BY NombreColumaDeGrupo

[HAVING Condiciones]

[ORDER BY Criterio]

Ocultar

En la cláusula GROUP BY se colocan las columnas por las que vamos a agrupar. En la cláusula HAVING se especifica la condición que han de cumplir los grupos para que se realice la consulta.

Debes tener en cuenta el orden en el que se ejecutan las cláusulas indicadas anteriormente:

WHERE se encarga de filtrar las filas según las condiciones o criterio que pongamos.
GROUP BY se encarga de crear una tabla con los agrupamientos o grupos indicados.
HAVING filtra los grupos obtenidos por GROUP BY.
ORDER BY se encarga de ordenar la salida o resultado final.

Las columnas que aparecen en el SELECT y que no aparezcan en la cláusula GROUP BY deben tener una función de agrupamiento. Si esto no se hace así producirá un error en el SQL estándar. Otra opción es poner en la claúsula GROUP BY las mismas columnas que aparecen en SELECT.

En el caso de MySQL, se acepta incluir en la SELECT otras columnas que no aparezcan en el GROUP BY, como por ejemplo:

Una consulta que liste para equipo, su código, nombre, comunidad y total de concursantes del equipo.

SELECT equipo.cdequipo, equipo.nombre, count(*) 'concursantes'
FROM concursante 
INNER JOIN equipo ON equipo.cdequipo=concursante.cdequipo
GROUP BY concursante.cdequipo;

Ejercicio resuelto usando HAVING

Ocultar

En la base de datos campeonato, obtener la media de puntos acumulados por cada concursante que participa en juegos, mostrando únicamente el código de concursante cuya media no sea inferior a 40 puntos.

Ejercicio Resuelto Consultas Agrupadas

Ocultar

En la base de datos campeonato, realiza las siguientes consultas:

Total de concursantes en cada equipo. Muestra el código de equipo y total de concursantes, ordenando de más a menos concursantes.
La cuota media de inscripción de los concursantes femeninos y masculinos.
Total de puntos acumulados que lleva cada consursante que participa en juegos. Ordena el resultado de más a menos puntos.
Total de puntos acumulados que lleva cada concursante que particpa en juegos. Muestra solo los que llevan entre 100 y 300 puntos.
Total de concursantes del equipo '02' inscritos por año.

Autoevaluación

Ocultar

Indica si las siguientes consultas son equivalentes para listar el total de juegos por equipo, siempre que el equipo tenga más de 2 juegos.

Retroalimentación

Falso

Sólo es correcta la consulta 1.-

La consulta 2.- es incorrecta y dará error, pues la cláusula WHERE no admite el uso de funciones de agregado.

9.- Consultas multitabla.

Caso práctico

Ocultar

Hasta ahora nos hemos dejado guiar por Noiba y Vindio para aprender a realizar consultas sencillas que manejaban una sola tabla. Pero Juan, les idnica que ha llegado el momento de realizar consultas más complejas para obtener datos de varias tablas. Está claro que en el diseño de Talleres Faber intervienen múltiples tablas y que en el trabajo diario con la base de datos estas consultas van a ser frecuentes. Tendremos que seguir los pasos de Alejandra para aprender a combinar las tablas y hacer consultas que muestren los resultados buscados.

Ocultar

En las consultas realizadas hasta ahora sólo hemos utilizado una tabla que se indicaba a la derecha de la cláusula FROM, pero hay veces que una consulta necesita acceder a columnas de varias tablas (recuerda que en el modelo relacional los datos los tenemos distribuidos en diferentes tablas). Por ejemplo, obtener ciertos datos de los juegos (tabla juego) junto a datos de sus equipos (tabla equipo), habrá que incluir en la consulta esas dos tablas.

Ejemplo 1.

Imagina que, en la base de datos campeonato, queremos un listado en el que se muestren los nombres de todos los juegos, su dificultad y el nombre del equipo organizador. Tendremos que consultar las tablas juego y equipo. ¿Y cómo las consultamos? Es importante que al consultarlas las combinemos de forma correcta, esto es, por las columnas que permiten relacionar esas tablas, que normalmente será la columna que es clave ajena en una tabla y la clave primaria de la otra tabla. En las siguientes imágenes te lo explicamos.

Tabla con los datos de todos los juegos

La columna cdequipo es clave ajena o foránea en la tabla juego, y permite relacionar esta tabla con la tabla equipo por esa columna.

Ilustración de los datos de la tabla juego — Workbench (Elaboración propia)

Tabla con los datos de todos los equipos

La columna cdequipo es clave primaria en la tabla equipo, y permite relacionar esta tabla con la tabla juego por esa columna, ya que en la tabla juego existe una clave ajena o foránea que la referencia.

Ilustración los datos de la tabla equipo — Workbench (Elaboración propia)

Consulta multitabla

En la siguiente imagen puedes ver el resultado de combinar las tablas equipo y juego y listar para cada juego, su nombre, dificultad, código de equipo y nombre de equipo organizador.

SELECT j.nombre 'juego', j.dificultad, e.cdequipo, e.nombre 'equipo'
FROM juego j, equipo e
WHERE j.cdequipo=e.cdequipo;

Observa que:

En este ejemplo, las columnas que permiten combinar las dos tablas tienen el mismo nombre, pero eso no es obligatorio, pueden tener nombres diferentes.
Se han utilizado alias de tabla para poder diferenciar las columnas que tienen el mismo nombre y pertenecen a tablas diferentes.

Ilustración de los datos combinados de juegos y equipos — Workbench (Elaboración propia)

Producto cartesiano

Observa que si al hacer la consulta multitabla omitimos el filtro de combinación de las tablas, (... WHERE j.cdequipo=e.cdequipo ) lo que se obtiene es lo que se llama 'el producto cartesiano' de esas tablas, esto es, cada fila de una tabla combinada con todas las filas de la segunda tabla.

SELECT j.nombre 'juego', j.dificultad, e.cdequipo, e.nombre 'equipo'
FROM juego j, equipo e;

Ilustración del producto cartesiano de dos tablas — Workbench (Elaboración propia)

¿Qué ocurre si combinamos dos o más tablas sin ninguna restricción? El resultado será un producto cartesiano.

El producto cartesiano entre dos tablas da como resultado todas las combinaciones de todas las filas de esas dos tablas.

Se indica poniendo en la cláusula FROM las tablas que queremos componer separadas por comas. Y puedes obtener el producto cartesiano de las tablas que quieras.

Como lo que se obtiene son todas las posibles combinaciones de filas, debes tener especial cuidado con las tablas que combinas. Si tienes dos tablas de 20 filas cada una, el resultado tendrá 20x20 filas, a medida que aumentemos el número de filas que contienen las tablas, mayor será el resultado final, con lo cual se puede considerar que nos encontraremos con una operación costosa.

Esta operación no es de las más utilizadas ya que toma una fila de una tabla y la asocia con todas y cada una de las filas de la otra tabla, independientemente de que tengan relación o no. Lo más normal es que queramos seleccionar los registros o filas según algún criterio.

Necesitaremos discriminar de alguna forma para que únicamente aparezcan filas de una tabla que estén relacionadas con la otra tabla. A esto se le llama asociar tablas (JOIN).

Para hacer una composición interna se parte de un producto cartesiano y se eliminan aquellas filas que no cumplen la condición de composición.

Lo importante en las composiciones o combinaciones internas es emparejar las columnas que han de tener valores iguales (las que permiten relacionar las tablas).

Cuando combinamos varias tablas tenemos que tener en cuenta:

Se pueden combinar tantas tablas como deseemos.
En la cláusula SELECT se pueden citar columnas de todas las tablas.
Si hay columnas que tienen el mismo nombre en distintas tablas de la cláusula FROM se deben especificar poniendo el nombre de la tabla antes del nombre de la columna, o bien el alias que se le asigne a la tabla:

NombreTabla.NombreColumna o bien aliasTabla.NombreColumna

Si el nombre de una columna no coincide en dos tablas no es necesaria la notación anterior, pero es conveniente y una buen práctica.
En la cláusula WHERE es necesario especificar el criterio que se sigue para combinar las tablas ya que si no se especifica el resultado será el producto cartesiano que combina todas las filas de una tabla con cada una de las filas de la otra.

Ejemplo 2. En la base de datos campeonato, oun listado con el nombres de los concursantes, su cuota y el nombre y comunidad de su equipo.

Como en las tablas concursante y equipo hay columnas con el mismo nombre, se deben denominar anteponiendo el nombre de la tabla o el alias de la tabla.

SELECT c.nombre 'concursante', c.cuota_inscri, e.nombre 'equipo', e.comunidad
FROM concursante c, equipo e 
WHERE c.cdequipo=e.cdequipo;

Ejemplo 3. Listar el nombre y cuota de los concursantes de sexo femenino (mujeres), junto con el código del juego en el que participan y los puntos conseguidos.

SELECT nombre, cuota_inscri, sexo, cdjuego 'juego', puntos
FROM concursante, participa
WHERE concursante.cdconcur=participa.cdconcur and sexo ='M';

En este caso no es necesario indicar en la SELECT el nombre a alias de la tabla delante del nombre de la columna, pues tienen denominaciones diferentes, pero observa que queda bastante más legible y es un buena práctica calificar cada columna indicando la tabla de la que proviene.

SELECT c.nombre, c.cuota_inscri, c.sexo, p.cdjuego 'juego', p.puntos
FROM concursante c, participa p
WHERE c.cdconcur=p.cdconcur and c.sexo ='M';

Reflexiona

Ocultar

Cuando realizamos consultas a varias tablas y no especificamos las columnas por las que se relacionan, el resultado de la consulta es el producto cartesiano, tal y como hemos explicado. Este resultado casi siempre será un error. En muy pocos casos nos interesa mezclar filas de una tabla con filas de otra, sin ningún criterio para establecer esa combinación.

Ejemplo: si combinamos en una consulta juegos con equipos sin hacer coincidir el cdequipo en ambas tablas, obtendremos un listado que mezcla cada juego con todos los equipos de la tabla. Desde un punto de vista lógico esta consulta no tendría ningún sentido.

Ocultar

Las columnas que aparecen en la cláusula WHERE se denominan columnas de emparejamiento, composición o combinación ya que son las que permiten combinar las filas de las dos tablas. Éstas no tienen por qué estar incluidas en la lista de selección. Combinamos tablas que estén relacionadas entres sí y además, una de las columnas de combinación será clave principal en su tabla, y en otra tabla será clave ajena. Cuando combinamos columnas debemos especificar de la siguiente forma: NombreTabla1.Camporelacionado1 = NombreTabla2.Camporelacionado2

Ejercicio Resuelto Multitabla

Ocultar

En la base de datos campeonato, realiza las siguientes consultas:

Listado con el código, nombre y dificultad de cada juego y código y nombre del equipo que lo organiza.
Como la anterior, pero solo se muestran los juegos de dificultad baja.
Listado de los códigos y nombre de cada concursante y código y nombre de los juegos en los que participa.

Autoevaluación

Ocultar

Pregunta

Observa la siguiente consulta y elige la opción correspondiente de la lista:

SELECT c.nombre 'concursante',  j.nombre 'juego', p.puntos
FROM concursante c, juego j, participa p
WHERE c.cdconcur=p.cdconcur AND p.cdjuego=j.cdjuego;

Respuestas

Opción 1

No tiene errores.

Opción 2

Las tablas no están relacionadas correctamente en la cláusula WHERE.

Opción 3

No se pueden consultar esas 3 tablas por no estar relacionadas. Error en la cláusula FROM.

Opción 4

Las columnas seleccionadas no corresponden a las tablas relacionadas. Error en SELECT.

Retroalimentación

Correcto. Las columnas seleccionadas muestran los concursantes y los juegos en los que participan, así como los puntos en cada juego.

Falso. El criterio para combinar las tablas es el adecuado.

Falso. Las tablas pueden consultarse porque hay relación entre ellas.

Falso. Las columnas corresponden a las tablas que están relacionadas en la cláusula FROM.

Solución

10.- Combinaciones internas.

Caso práctico

Ocultar

Cuando en el apartado 7 hemos aprendido a hacer consultas a los datos contenidos en varias tablas combinando las filas, nos hemos dejado por el camino un tipo de consultas que pueden ser también de utilidad en un taller como talleres Faber, porque permiten mostrar, no sólo los empleados y las reparaciones en las que han intervenido, sino que se podrá conocer también los empleados que no hayan intervenido en ninguna reparación. Lo vemos con Noiba.

Ocultar

Las combinaciones de tablas que hemos visto hasta ahora en consultas multitabla son “Composiciones o combinaciones internas”. En éstas se combina información procedente de dos o más tablas, formando filas relacionadas según la condición de búsqueda establecida con WHERE que generalmente es la clave principal de una tabla y la clave ajena relacionada en la otra tabla.

En la versión SQL de 1999 o SQL99 se especifica una nueva sintaxis para consultar varias tablas que MySQL incorpora, así que también la veremos. La razón de esta nueva sintaxis era separar las condiciones de combinación de tablas respecto a las condiciones o filtros de selección de registros. Además, esta nueva sintaxis permitirá realizar dos tipos de combinación de tablas:

La composición o combinación interna.
La composición o combinación externa.

No centramos en este apartado en la combinación interna.

Ocultar

Estas composiciones se denominan internas porque en el resultado no aparece ninguna fila de una tabla que no tenga correspondencia con las filas de la tabla con la que se combina.

Ocultar

Las sintaxis de las combinaciones internas que vemos a continuación son equivalentes:

Ocultar

SELECT … FROM NombreTabla1 , NombreTabla2 WHERE condición de relación..

SELECT … FROM NombreTabla1 INNER JOIN NombreTabla2 ON condición de relación..

Ocultar

Ejemplo 1.

En la base de datos campeonato, la consulta que muestra para cada juego su nombre, dificultad, código y nombre de su equipo organizador se puede formular de dos formas:

Sin JOIN, con WHERE.

SELECT j.nombre 'juego', j.dificultad, e.cdequipo, e.nombre 'equipo'
FROM juego j, equipo e
WHERE j.cdequipo=e.cdequipo;

Con JOIN interno o INNER JOIN.

SELECT j.nombre 'juego', j.dificultad, e.cdequipo, e.nombre 'equipo'
FROM juego j
INNER JOIN equipo e ON j.cdequipo=e.cdequipo;

Ocultar

Una buena práctica es utilizar JOIN en vez de solo WHERE, pues así separamos las condiciones de combinación de las tablas con el filtro de selección. Observa el siguiente ejemplo.

Ocultar

Ejemplo 2.

En la consulta anterior, supongamos que el listado solo se desea para los juegos cuyo equipo organizador es de Andalucía o Murcia. Las consultas serían:

Sin JOIN, con WHERE.

SELECT j.nombre 'juego', j.dificultad, e.cdequipo, e.nombre 'equipo'
FROM juego j, equipo e 
WHERE j.cdequipo=e.cdequipo AND e.comunidad IN ('Andalucia', 'Murica');

Con JOIN interno o INNER JOIN.

SELECT j.nombre 'juego', j.dificultad, e.cdequipo, e.nombre 'equipo'
FROM juego j
INNER JOIN equipo e ON j.cdequipo=e.cdequipo
WHERE e.comunidad IN('Andalucia', 'Murica');

Otras sintaxis de combinación interna, igualmente equivalentes, son:

Ocultar

SELECT … FROM NombreTabla1 JOIN NombreTabla2 ON condición de relación..
SELECT … FROM NombreTabla1 JOIN NombreTabla2 WHERE condición de relación..
SELECT ….FROM NombreTabla1 INNER JOIN NombreTabla2 USING(ColumnaRelacionada)..

Ocultar

Las columnas que aparecen en la cláusula WHERE, ON o USING, se denominan columnas de emparejamiento, combinación o composición ya que son las que permiten combinar las filas de las dos tablas. Éstas no tienen por qué estar incluidas en la lista de selección. Combinamos tablas que estén relacionadas entres sí y además, una de las columnas de emparejamiento será clave principal en su tabla y clave ajena en la otra tabla. Si las dos columnas de combinación tieen el mismo nombre, se puede usar USING(nombre_columna) o bien si se usa WHERE u ON, habrá que calificar la columna con el alias o nombre de la tabla correspondiente.

Ocultar

Seguimos viendo otros ejemplos sobre la base de datos campeonato.

Ejemplo 3.

En la salida de la siguiente consulta sólo aparecen los datos de los concursantes que participan en juegos, y los juegos en los que participan. Los concursantes que no participan en juegos no aparecen. Para los concursantes que participan en varios juegos su código y nombre se repiten.

SELECT concursante.cdconcur, concursante.nombre, participa.cdjuego, juego.nombre
FROM concursante 
INNER JOIN participa ON concursante.cdconcur = participa.cdconcur
INNER JOIN juego ON juego.cdjuego=participa.cdjuego;

De otra forma, con USING sería:

SELECT concursante.cdconcur, concursante.nombre, participa.cdjuego, juego.nombre
FROM concursante 
INNER JOIN participa USING(cdconcur) 
INNER JOIN juego USING(cdjuego);

Ejemplo 4.

Esta consulta combina 3 tablas para obtener el importe (unidades*precio) de los recambios sustituidos en cada reparación.

SELECT REPARACIONES.IdReparacion, sum(Unidades * PrecioReferencia)
AS 'Importe recambios' 
FROM (REPARACIONES INNER JOIN Incluyen ON REPARACIONES.IdReparacion=Incluyen.IdReparacion)
INNER JOIN RECAMBIOS ON Incluyen.IdRecambio=RECAMBIOS.IdRecambio 
GROUP BY IdReparacion;

Reflexiona

Ocultar

Compara las siguientes alternativas para definir combinaciones internas:

SELECT … FROM NombreTabla1 INNER JOIN NombreTabla2 ON condición de relación..
SELECT ….FROM NombreTabla1 INNER JOIN NombreTabla2 USING(ColumnaRelacionada)

¿Por qué podemos considerar que la sintaxis empleada en la opción b es más restrictiva que la de la opción a?

Ocultar

Ejercicio Resuelto INNER JOIN

Ocultar

En la base de datos campeonato, realiza las siguientes consultas:

Listado con el código, nombre y dificultad de cada juego y código, nombre y comunidad del equipo que lo organiza. En el listado deben aparecer sólo los juegos de dificultad alta o media que organizan equipos de Andalucía o Galicia.
Listado de los códigos y nombre de cada concursante y código y nombre de los juegos en los que participa. Ordena el listado por nombre de concursante.
Como la consulta anterior, pero también debe listarse el códiog y nombre del equipo que organiza cada juego.
Para cada concursante listar su código y nombre y el código y nombre de su ídolo. Ordena el listado por nombre de concursante.

Retroalimentación

-- 1
SELECT j.cdjuego, j.nombre 'juego', j.dificultad, e.cdequipo, e.nombre 'equipo'
FROM juego j
INNER JOIN equipo e ON j.cdequipo=e.cdequipo
WHERE j.dificultad IN ('alta', 'media') AND e.comunidad IN('Andalucía', 'Galicia');

-- 2
SELECT c.cdconcur, c.nombre 'concursante', p.cdjuego, j.nombre 'juego'
FROM concursante c 
INNER JOIN participa p ON c.cdconcur=p.cdconcur
INNER JOIN juego j ON p.cdjuego=j.cdjuego
ORDER BY c.nombre;

-- 3
SELECT c.cdconcur, c.nombre 'concursante', p.cdjuego, j.nombre 'juego'
FROM concursante c 
INNER JOIN participa p ON c.cdconcur=p.cdconcur
INNER JOIN juego j ON p.cdjuego=j.cdjuego
INNER JOIN equipo e ON e.cdequipo=j.cdequipo
ORDER BY c.nombre;

-- 4
SELECT c.cdconcur, c.nombre 'concursante', id.cdconcur, id.nombre 'ídolo'
FROM concursante c 
INNER JOIN concursante id ON c.cdidolo=id.cdconcur
ORDER BY c.nombre;

11.- Combinaciones externas.

Caso práctico

Ocultar

Revisando la información que es necesario obtener en el taller mecánico, Vindio se ha dado cuenta que hay casos en los que es necesario mostrar en los listados filas de una tabla que no están relacionadas con la tabla con la que se combina. Por ejemplo, si se desea un listado con todos los clientes y las facturas enviadas, y en el listado se desea que aparezccan también los clientes a los que aún no se le han enviado facturas. ¿Cómo obtener esos datos? Noiba, le recuerda que para esos casos existen las combinaciones externas.

Ocultar

Ilustra un cuadrado multicolor — Everaldo Coelho (YellowIcon) (GNU/GPL)

¿Has pensado que puede que te interese seleccionar algunas filas de una tabla aunque éstas no tengan correspondencia con las filas de la otra tabla? Esto puede ser necesario.

Por ejemplo, en la base de datos campeonato, hay concursantes que no tienen equipo, van por libre. Si se deseara un listado con los nombres de todos los concursantes y el nombre de su equipo, con una combinación interna no lo podríamos obtener, pues sólo se combinan las filas en las que hay coincidencia en las columnas de combinación. ¿Cómo se puede obtener? En este caso hay que usar una combianción externa.

Una combinación externa es una variedad de composición de tablas que permite seleccionar algunas filas de una tabla aunque éstas no tengan correspondencia con las filas de la otra tabla con la que se combina. Cuando no existe correspondencia en la segunda tabla se combina con NULL.

La combinación externa puede expresarse de dos formas:

Combinación externa a la izquierda: LEFT JOIN. Cada fila de la tabla colocada a la izquierda de LEFT JOIN que no se pueda combinar con ninguna fila de la segunda tabla, añade una fila al resultado con los valores de las columnas de la primera tabla (izquierda) y NULL para todas las columnas de la segunda que no han encontrado resultados coincidentes.
Combinación externa a la derecha: RIGHT JOIN. Cada fila de la tabla colocada a la derecha de RIGHT JOIN que no se pueda combinar con ninguna fila de la primera tabla, añade una fila al resultado con los valores de las columnas de la segunda tabla (derecha) y NULL para todas las columnas de la primera que no encuentren coincidencias.

La sintaxis equivalente en ambos casos para LEFT JOIN:

Ocultar

SELECT ….FROM NombreTabla1 LEFT JOIN NombreTabla2 ON condición de relación..
SELECT ….FROM NombreTabla1 LEFT JOIN NombreTabla2 USING(ColumnaRelacionada)…
SELECT ….FROM NombreTabla1 LEFT OUTER JOIN NombreTabla2 ON condición de relación..

Ocultar

La sintaxis equivalente en ambos casos para RIGHT JOIN:

Ocultar

SELECT ….FROM NombreTabla1 RIGHT JOIN NombreTabla2 ON condición de relación..
SELECT ….FROM NombreTabla1 RIGHT JOIN NombreTabla2 USING(ColumnaRelacionada)…
SELECT ….FROM NombreTabla1 RIGHT OUTER JOIN NombreTabla2 ON condición de relación..

Ocultar

Vemos algunos ejemplos con la base de datos campeonato.

Ejemplo 1. Listado con el código y nombre de cada concursante y el código y nombre de su equipo. En el listado deben aparecer todos los concursantes, aunque no tengan equipo. Ordenar el listado por nombre de concursante. Con LEFT JOIN.

SELECT c.cdconcur, c.nombre 'concursante', e.cdequipo, e.nombre 'equipo'
FROM concursante c 
LEFT JOIN equipo e ON c.cdequipo=e.cdequipo
ORDER BY c.nombre;

Para los concursantes que no tengan equipo, aparecerá el valor NULL en las columnas e.cdequipo y e.cdnombre.

Ejemplo 2. Listado con el código y nombre de cada concursante y el código y nombre de su equipo. En el listado deben aparecer todos los concursantes, aunque no tengan equipo. Ordenar el listado por nombre de concursante. Con RIGHT JOIN.

SELECT c.cdconcur, c.nombre 'concursante', e.cdequipo, e.nombre 'equipo'
FROM equipo e
RIGHT JOIN concursante c ON c.cdequipo=e.cdequipo
ORDER BY c.nombre;

Para los concursantes que no tengan equipo, aparecerá el valor NULL en las columnas e.cdequipo y e.cdnombre.

Ejemplo 3. Listado con el código y nombre de cada concursante y el código y nombre de su equipo. En el listado deben aparecer todos los concursantes, aunque no tengan equipo. Ordenar el listado por nombre de concursante. Con LEFT JOIN y usando la función IFNULL() para personalizar los valores nulos.

SELECT c.cdconcur, c.nombre 'concursante', IFNULL(e.cdequipo,'***'),IFNULL(e.nombre, 'SIN EQUIPO') 'equipo'
FROM concursante c 
LEFT JOIN equipo e ON c.cdequipo=e.cdequipo
ORDER BY c.nombre;

Para los concursantes que no tengan equipo, aparecerá el valor *** en la columna e.cdequipo y SIN EQUIPO en la columna e.cdnombre.

Vemos otros ejemplos incluyendo otras cláusulas de la SELECT

Ejemplo 4. Listado con el código y nombre de cada concursante con el código y nombre de los juegos en los que participan. Si el concursante no participa en juegos debe salir también en el listado.

SELECT c.cdconcur, c.nombre, p.cdjuego
FROM concursante c
LEFT JOIN participa p ON c.cdconcur=p.cdconcur;

(En este caso aunque tengamos algún concursante que no participe en juegos se mostrará su código y nombre. Y en la columna de la tabla participa p.cdjuego, aparecerá NULL).

Ejemplo 5. El mismo ejemplo anterior pero formulado con con RIGHT JOIN.

SELECT c.cdconcur, c.nombre, p.cdjuego
FROM participa p
RIGHT JOIN concursante c ON c.cdconcur=p.cdconcur;

Observa que al formular la misma consulta del Ejemplo4 con RIGHT JOIN la tabla que aparece a la derecha de RIGHT JOIN es concursante, que es la tabla de la que interesa que se listen todas las filas, aunque no se puedan combinar con ninguna fila de la participa.

Ejercicio resuelto

Ocultar

Con la base de datos campeonato, obtener un listado de los juegos registrados en nuestra base de datos, su código y nombre, y el total de concursantes que participan en cada uno de ellos, y los puntos acumulados.

Ordena el listado de manos a más puntos acumulados y si hay juegos en los que no se participa, debe aparecer *** como puntos acumulados.

Ejercicio Resuelto LEFT JOIN/RIGHT JOIN

Ocultar

En la base de datos campeonato, realiza las siguientes consultas:

Listado con el código y nombre de todos los concursantes y el nombre y puntos del juego en el que participan. Si un concursante no participa en juegos, en el nombre y puntos del juego deben aparecer tres asteriscos. Ordenar el listado por nombre de concursante.
Listado con el código y nombre de todos los equipos, y el total de concursantes que tienen. Deben aparecer en el listado todos los equipos y si no tiene concursantes el quipo, aparecerá un 0 en ese total. Ordenar el listado de más a menos concursantes.

Autoevaluación

Ocultar

Indica si las siguientes consultas son equivalentes.

Retroalimentación

Verdadero

Observa que si se usa LEFT JOIN, se listan todas las filas de la tabla a la izquierda o delante de LEFT JOIN, se puedan o no realcionar con filas de la otra tabla.

Y si se usa RIGHT JOIN, se listan todas las filas de la tabla a la derecha o detrás de RIGHT JOIN, se puedan o no relacionar con filas de la otra tabla.

Por lo que ambas sentencias son equivalentes.

Para saber más

Ocultar

Para más información sobre las combinaciones de tablas en MySQL consulta el siguiente enlace.

Combinación de tablas en MySQL

12.- Otras composiciones y consultas multitabla.

Caso práctico

Ocultar

En este apartado vamos a aprender a diseñar un tipo de consultas que puede ser de gran utilidad en nuestro proyecto. Alejandra se encuentra en muchos casos con tablas que, teniendo la misma estructura, contienen información que se refiere a distintos periodos de tiempo. Por ejemplo las facturas de los clientes almacenadas en distinta tabla cada año (facturas_2010, facturas_2011). Vindio y Noiba nos van a mostrar cómo podríamos unir la información que contienen dos o más tablas en una consulta, para saber, por ejemplo, la facturación acumulada desde que Talleres Faber inició su actividad.

Ocultar

Seguro que cuando empieces a trabajar con bases de datos llegará un momento en que dispongas de varias tablas con los mismos datos guardados para distintos registros y quieras unirla en una única tabla. ¿Esto se puede hacer? Es una operación muy común junto a otras. Al fin y al cabo, una consulta da como resultado un conjunto de filas y con conjuntos podemos hacer tres tipos de operaciones muy comunes como son: unión, intersección y diferencia.

Además, existen otros tipos de composiciones SQL99 que vamos a ver a continuación.

12.1.- Composiciones SQL99.

Ocultar

Ilustración de un dibujo en colores con sentencias SQL99 — Libre Office (Elaboración propia)

Como has visto anteriormente, SQL incluye en esta versión de 1999 mejoras de la sintaxis a la hora de crear composiciones en consultas multitabla. Hemos visto las composiciones internas (INNER JOIN) y externas (LEFT/RIGHT OUTER JOIN), pero existen otros tipos de composiciones.

La sintaxis de estas composiciones es la siguiente:

Ocultar

SELECT tabla1.columna1, tabla1.columna2, …, tabla2.columna1, tabla2.columna2, …

FROM tabla1

[CROSS JOIN tabla2] |

[NATURAL JOIN tabla2] |

[JOIN tabla2 USING (columna)] |

[JOIN tabla2 ON (tabla1.columna=tabla2.columna)] |

[LEFT | RIGTH | FULL OUTER JOIN tabla2 ON (tabla1.columna=tabla2.columna)];

Ocultar

CROSS JOIN

CROSS JOIN: realiza un producto cartesiano de las filas de ambas tablas por lo que sería el equivalente a usar la cláusula WHERE sin indicar las columnas de empearejamiento de las tablas.

Ejemplo.

La siguiente consulta realiza el producto cartesiano entre las tablas juego y equipo, de manera que cada juego lo combina con todos los equipos de la tabla equipo.

SELECT juego.cdjuego, juego.nombre, juego.cdequipo, equipo.nombre
FROM juego
CROSS JOIN equipo;

Y es equivalente a esta otra consulta.

SELECT juego.cdjuego, juego.nombre, juego.cdequipo, equipo.nombre
FROM juego, equipo;

NATURAL JOIN

NATURAL JOIN: detecta automáticamente las columnas de emparejamiento, basándose en el nombre de la columna que coincide en ambas tablas. Por lo tanto, se requerirá que las columnas de emparejamiento tengan el mismo nombre en cada tabla. Además, esta característica funcionará incluso si no están definidas las claves primarias o ajenas.

Ejemplo.

La siguiente consulta expresada con NATURAL JOIN no mostraría nada, pues combina las tablas juego y equipo por las columnas que tienen igual denominación, que son cdequipo y nombre, y está claro que el nombre de un equipo y el nombre de un juego no tiene nada que ver, y no hay coincidencia de valores en esas columnas de ambas tablas.

SELECT juego.cdjuego, juego.nombre, juego.cdequipo, equipo.nombre
FROM juego
NATURAL JOIN equipo;

JOIN USING

JOIN USING: esta cláusula permite establecer relaciones entre tablas indicando qué columna o columnas comunes se quieren utilizar para ello. Por lo tanto, las columnas de emparejamiento deben tener el mismo nombre en cada tabla.

Ejemplo.

La siguiente consulta expresada con JOIN USING combina las tablas juego y equipo por la columna que tiene igual denominación en ambas tablas cdequipo y que permite relacionarlas, por lo que mostrará el código y nombre de cada juego, junto al código y nombre de su equipo.

SELECT juego.cdjuego, juego.nombre, juego.cdequipo, equipo.nombre
FROM juego
JOIN equipo USING(cdequipo);

JOIN ON

JOIN ON: se utiliza para componer tablas en las que los nombres de las columnas de emparejamiento no coinciden en ambas tablas o se necesita establecer asociaciones más complicadas. Si las columnas de emparejamiento se denominan igual en las tablas, será necesario calificar con el nombre de la tabla o con su alias a cada columna de emparejamiento.

Ejemplo.

La siguiente consulta expresada con JOIN ON combina las tablas juego y equipo por la columna que tiene igual denominación en ambas tablas cdequipo y que permite relacionarlas, por lo que mostrará el código y nombre de cada juego, junto al código y nombre de su equipo.

SELECT juego.cdjuego, juego.nombre, juego.cdequipo, equipo.nombre
FROM juego
JOIN equipo ON juego.cdequipo=equipo.cdequipo;

LEFT OUTER JOIN

LEFT OUTER JOIN: es una composición externa izquierda, todas las filas de la tabla de la izquierda se devuelven, aunque no haya ninguna columna correspondiente en las tablas combinadas.

Ejemplo.

La siguiente consulta expresada con LEFT OUTER JOIN combina las tablas concursante y equipo por la columna que tiene igual denominación en ambas tablas cdequipo y que permite relacionarlas, y al ser un LEFT JOIN, mostrará el código y nombre de todos los concursantes (tabla a la izquierda del JOIN) tengan o no equipo, por lo que para los que no tienen equipo las columnas de la tabla equipo estarán a NULL.

SELECT concursante.cdconcur, concursante.nombre, equipo.cdequipo, equipo.nombre
FROM concursante
LEFT OUTER JOIN equipo ON concursante.cdequipo=equipo.cdequipo;

RIGTH OUTER JOIN

RIGTH OUTER JOIN: es una composición externa derecha, todas las filas de la tabla de la derecha se devuelven, aunque no haya ninguna columna correspondiente en las tablas combinadas.

Ejemplo.

La siguiente consulta expresada con RIGHT OUTER JOIN combina las tablas concursante y equipo por la columna que tiene igual denominación en ambas tablas cdequipo y que permite relacionarlas, y al ser un RIGHT JOIN, mostrará el código y nombre de todos los equipos (tabla a la derecha del JOIN) tengan o no concursantes, por lo que para los que no tienen concursantes las columnas de la tabla concursante estarán a NULL.

SELECT equipo.cdequipo, equipo.nombre, concursante.cdequipo, concursante.nombre
FROM concursante
RIGHT OUTER JOIN equipo ON concursante.cdequipo=equipo.cdequipo;

FULL OUTER JOIN

FULL OUTER JOIN: es una composición externa en la que se devolverán todas las filas de las columnas no relacionados de ambas tablas. (No implementado en MySQL)

El FULL OUTER JOIN, se puede emular en MySQL mediante la unión (UNION) de las dos consultas anteriores.

Ejemplo.

La siguiente consulta muestra todos los concursantes tengan o no equipo, y todos los equipos, tengan o no concursantes.

SELECT concursante.cdconcur, concursante.nombre, equipo.cdequipo, equipo.nombre
FROM concursante
LEFT OUTER JOIN equipo ON concursante.cdequipo=equipo.cdequipo
UNION
SELECT concursante.cdconcur, concursante.nombre, equipo.cdequipo, equipo.nombre
FROM concursante
RIGHT OUTER JOIN equipo ON concursante.cdequipo=equipo.cdequipo;

Ejercicio Resuelto

Ocultar

Utilizando la base de datos campeonato, realiza las siguientes consultas:

Listado con el nombre de cada concursante, juego en el que participa y puntos. Solo para los concursantes que participan en juegos.
Listado con el nombre de cada concursante, juego en el que participa y puntos. Si el concursante no participa en juegos, también debe salir en el listado. Utiliza IFNULL() donde corresponda para sustituir los nulos por 'sin juegos'.
Listado con el código y nombre de cada concursante, y código y nombre de su ídolo. Solo para los concursante con ídolo.

Retroalimentación

1.-

SELECT c.nombre 'concursante', p.cdjuego 'juego', p.puntos
FROM concursante c 
INNER JOIN participa p ON c.cdconcur=p.cdconcur;

2.-

SELECT c.nombre 'concursante', IFNULL(p.cdjuego, 'sin juegos') 'juego', IFNULL(p.puntos,0) 'puntos'
FROM concursante c 
LEFT JOIN participa p ON c.cdconcur=p.cdconcur;

3.- En este caso se trata de combinar la tabla concursante con ella misma, desde dos puntos de vista: como tabla de concursantes y como tabla de ídolos. (Observa que hay una relación reflexiva en la tabla concursante, que indica que un concursante puede tener a otro concursante como ídolo, y que un concursante puede ser ídolo de varios concursantes)

SELECT c.cdconcur, c.nombre 'concursante', idol.cdconcur 'ídolo', idol.nombre
FROM concursante c 
INNER JOIN concursante idol ON c.cdidolo=idol.cdconcur;

Autoevaluación

Ocultar

Retroalimentación

Verdadero

En ambos casos se está combinando con un JOIN INTERNO las tablas juego y equipo por la columna que permite relacionarlas, la columna cdequipo.

12.2.- Unión, intersección y diferencia de consultas.

Ocultar

Ilustración de un dibujo en color con operaciones de conjuntos — Libre Office (Elaboración propia)

Las consultas de unión, intersección y diferencia, son consultas que utilizan al menos dos SELECT cuyos resultados se pueden combinar para formar una sola consulta. Se basan en los operadores matemáticos de conjuntos: (unión, intersección y diferencia.

Vamos a ver en qué consisten estos tipos de consultas:

La unión (UNION) combina las filas de un primer SELECT con las filas de otro SELECT, desapareciendo las filas duplicadas.
La intersección (INTERSECT) examina las filas de dos SELECT y devuelve aquellas que aparezcan en ambos conjuntos. Las filas duplicadas se eliminarán.
La diferencia (MINUS) devuelve aquellas filas que están en el primer SELECT pero no en el segundo. Las filas duplicadas del primer SELECT se reducirán a una antes de comenzar la comparación.

Para estas tres operaciones es muy importante que:

Utilices en todas las SELECT que intervienen el mismo número y tipo de datos compatible en columnas y además, en el mismo orden.
Si se ordena la consulta final con ORDER BY, esta cláusula, como ya sabes, será la última de la consulta.

Estas operaciones se pueden combinar anidadas, pero es conveniente utilizar paréntesis para indicar qué operación quieres que se haga primero.

Las vemos a continuación en más detalle y con ejemplos de cada una de ellas.

UNION

Esta operación se realiza sobre:

Dos o más tablas con las mismas columnas.
Dos o más consultas que contienen las mismas columnas.
Tablas y consultas que tienen las mismas columnas.

El resultado es una consulta (tabla de la consulta) incluyendo las filas de la primera consulta y a continuación las filas de las siguientes consultas.

La sintaxis para realizar una UNION es:

Ocultar

SELECT…..FROM…..WHERE….. UNION [ALL | DISTINCT] SELECT……. FROM…….WHERE….

Ocultar

Si no se especifica ALL o DISTINCT por defecto se usa DISTINCT, por lo que se eliminan las filas repetidas que pudiera dar el resultado final de la unión de consultas.

Las condiciones en las que se establece una consulta de unión son:

Las consultas unidas deben tener el mismo número de columnas, con tipos de dato compatibles y en el mismo orden.
El resultado de la consulta tendrá como encabezado, el identificador de columnas de la primera consulta (la de la izquierda del operador UNION).
Se pueden establecer criterios o condiciones WHERE para la unión, no unir todas las columnas de las tablas, ordenar el resultado, etc.
Si en las tablas unidas hay filas que contienen la misma información, sólo aparece una fila en el resultado de la consulta y no dos repetidas. (UNION DISTINCT)
Se puede hacer que las filas repetidas aparezcan en el resultado de la consulta usando UNION ALL.
Sobre el resultado final no se pueden aplicar otras cláusulas como GROUP BY, únicamente puede contener ORDER BY al final de la sentencia SELECT.

Ejemplo.

Si en un centro educativo tenemos una base de datos centro con una tabla, de nombre FPgeneral, para el alumnado que cursa Ciclos Formativos de Informática por la mañana en Régimen General y otra tabla de similar estructura y columnas, de nombre FPsemipresencial, para el alumnado que cursas esos mismo Ciclos Formativos por la tarde en régimen Semipresencial, podríamos obtener una consulta que nos listara el nombre y apellidos, e email de todo el alumnado que cursa el ciclo formativo de código DAM o DAW ordenado por código de ciclo formativo, apellidos y nombre.

SELECT apellidos, nombre, email, codciclo "Código Ciclo"
FROM FPgeneral
WHERE codciclo in('DAM', 'DAW')
UNION
SELECT apellidos, nombre, email, codciclo 
FROM FPsemipresenical
WHERE codciclo in('DAM', 'DAW')
ORDER BY codciclo, apellidos, nombre;

Ejercicio resuelto UNION

Ocultar

Supongamos que en la base de datos campeonato tenemos dos tablas con datos de equipos. La tabla equipo con los datos de los equipos dados de alta hasta el año 2020 y otra tabla con la misma estructura que equipo denominada equipos_20 con los datos de equipos dados de alta a partir de 2020.
Vamos a realizar una consulta de unión entre los equipos de ambas tablas correspondientes a los años comprendidos entre 2018 y 2023. Ordenar el listado por año de fundación.

NOTA.

Para poder probarla, debes crear la tabla equipos_20 con filas en común en ambas tablas.

Retroalimentación

SELECT * 
FROM equipo
WHERE anio_funda BETWEEN 2018 AND 2020
UNION
SELECT * 
FROM equipos_20
WHERE anio_funda BETWEEN 2020 AND 2023
order by anio_funda;

Para poder probar este ejemplo, puedes crear previamente una nueva tabla en la base de datos campeonato, con los datos que te indicamos a continuación. Ejecuta las siguientes sentencias y podrás comprobar el resultado de la unión de esas dos consultas.

USE campeonato;
-- crea la tabla equipos_20
create table equipos_20 like equipo;

-- inserta los siguientes equipos
insert into equipos_20 () VALUES
('06','Los increibles','Andalucía',2020), ('07','El parral','Madrid',2020),
('08','Superingenio','Andalucía',2021), ('09','Supersónicos','Murcia',2022),
('10','Invencibles','Andalucía',2023), ('11','Los braseros','Galicia',2023)
;

Ocultar

INTERSECT

Como te hemos indicado, esta sentencia permite obtener un listado de las filas que tienen en común las consultas de las que se hace intersección, siempre que cumplan los requisitos indicados anteriormente: mismo número de columnas compatibles en tipo de dato y en el mismo orden.

Ejemplo.

En una escuela de música que imparten clases de chelo y piano, se almacenan los datos del alumnado en dos tablas diferentes: alum_chelo, y alum_piano, respectivamente. Las tablas tienen la misma estructura, columnas y tipo de dato de columna. Si la escuela de música necesita obtener el nombre y el email de los alumnos que cursan chelo y piano , para enviarles cierta información, podría obtener un listado de todos ellos mediante la siguiente consulta:

SELECT nombre, email FROM alum_chelo
 INTERSECT
SELECT nombre, email FROM alum_piano;

Debes conocer - Emular INTERSEC en MySQL

Ocultar

De momento, hasta la versión 8.0.30, MySQL no soporta INTERSEC, pero se puede emular el funcionamiento de esta sentencia mediante:

INNER JOIN.

Ejemplo.

En la base de datos campeonato, si tenemos dos tablas con datos de equipos: equipo y equipos_20 (con los datos indicados en el Ejercicio resuelto UNION), podemos obtener los equipos en común en ambas tablas (la intersección) de la siguiente forma:

SELECT equipo.*
FROM equipo 
INNER JOIN equipos_20 ON equipos_20.cdequipo=equipo.cdequipo;

Ocultar

MINUS

Como te hemos indicado, esta sentencia devuelve aquellas filas que están en el primer SELECT pero no en el segundo. Y es requisito, que las columnas de las SELECT implicadas sean igual en número, mismo orden y tipos de datos compatibles.

Ejemplo.

Si ahora, en la escuela de música necesitaran obtener un listado, con el nombre y el email, del alumnado que cursa solamente chelo, la consulta a realizar sería la siguiente:

SELECT nombre, email 
FROM alum_chelo
MINUS 
SELECT nombre,eamil 
FROM alum_piano;

Debes conocer - Emular MINUS en MySQL

Ocultar

De momento, hasta la versión 8.0.30, MySQL no soporta MINUS, pero se puede emular el funcionamiento de esta sentencia mediante:

Subconsultas.

Ejemplo.

Si interesa listar el código, nombre y año de fundación de los equipos que hay en la tabla equipo y que no están en la tabla equipos_20 (la diferencia), la consulta sería la siguiente:

SELECT  cdequipo, nombre, anio_funda
FROM equipo
WHERE (cdequipo, nombre, anio_funda) NOT IN
                    (SELECT cdequipo, nombre, anio_funda FROM equipos_20);

Autoevaluación

Ocultar

Pregunta 1

Contesta verdadero o falso según creas que se puede realizar o no la siguiente consulta de unión entre las tablas siguientes:

Recambios
Recambios_chapa	Recambios_mecánica
Codigo_Recambio Varchar(15)	IdRecambio Varchar(10)
Nombre Varchar(60)	Descripcion Varchar(100)
Unidades Varchar(50)	UnidadBase Varchar(50)
Cantidad int	Stock Smallint
Precio float	PrecioReferencia double

SELECT IdReparación, Nombre, Unidades, Cantidad
FROM Recambios_chapa
 UNION 
SELECT IdRecambio, Descripcion, UnidadBase, Stock 
FROM Recambios_mecanica
 ORDER BY Nombre LIMIT 100;

Verdadero Falso

Retroalimentación

Verdadero

El nombre de las columnas no tiene porqué coincidir. Es necesario que los datos sean compatibles.

13.- Subconsultas.

Caso práctico

Ocultar

Siguiendo los pasos de Noiba, parece que ya estamos listos para escribir cualquier tipo de consulta en SQL que nos permita acceder a la información almacenada, pero no es así. En muchas ocasiones para realizar una consulta necesitaremos los datos devueltos por otra consulta. Investigaremos con Alejandra si este tipo de consultas, denominadas subconsultas, están recogidas en el SQL de nuestro SGBD. MySQL ,y cómo se utilizan.

Ocultar

Para entender la necesidad de realizar una subconsulta veremos un ejemplo: supongamos que queremos obtener un listado con los concursantes que cuya cuota es superior a la cuota media.

Con lo que hemos visto hasta ahora necesitaríamos hacer dos consultas:

Obtener la cuota media de los concursantes.
Obtener un listado con los datos de los concursantes cuya cuota sea mayor que la cuota media.

En primer lugar, haríamos la primera consulta:

SELECT AVG(cuota_inscri)
FROM concursante;

Supongamos que el Importe medio obtenido sea 66,32. Ahora haríamos la segunda consulta basada en el resultado anterior:

SELECT *
FROM concursante
WHERE cuota_inscri >66.32;

Las subconsultas permiten escribir estas dos consultas en una, escribiendo tras la cláusula WHERE otra sentencia SELECT. Así el SELECT principal actúa sobre las filas devueltas por el SELECT anidado.

La sintaxis será:

Ocultar

SELECT NombreColumna,…. FROM NombreTabla,… WHERE Expresión OperadorRelacional (SELECT….)

Ocultar

En nuestro ejemplo sería:

SELECT *
FROM concursante
WHERE cuota_inscri >
(SELECT AVG(cuota_inscri) FROM concursante);

La subconsulta entre paréntesis (subconsulta anidada) se ejecuta primero y el valor extraído se introduce en la consulta más externa.

Ocultar

El tipo de dato de la expresión situada después de WHERE debe coincidir con el tipo de dato devuelto por la subconsulta.

Ocultar

Subconsultas que devuelven valores simples.

Se trata de subconsultas que devuelven un valor simple. En estas se utilizan los operadores de comparación (<, >, <>, <=, >=, =). Si la subconsulta devuelve más de una fila da ERROR.

Ejemplo 1. Listado con el nombre de los concursantes del mismo equipo que Ismael Rojo.

Sabemos que esta subconsulta, en nuestro modelo de base de datos, sólo puede devolver un valor, pues un concursante solamente puede pertenecer a un equipo.

SELECT cdconcur, nombre
FROM concursante
WHERE cdequipo = (SELECT cdequipo FROM concursante WHERE nombre LIKE 'Ismael Rojo');

Subconsultas que devuelven listas de valores.

Son subconsultas que devuelven más de una fila o más de un valor. En ese caso utilizaremos

El operador IN ó NOT IN .
O los operadores ALL o ANY combinados con algún operador relacional. Donde ALL indica todos y ANY indica alguno.

Pero ¿Cómo se realiza la comparación con la subconsulta utilizando estos operadores?

ANY. Compara con cualquier fila o valor devuelto por subconsulta. La instrucción es válida si hay un registro en la subconsulta que permite que la comparación sea cierta.
ALL. Compara con todas las filas o valores de vueltos por la subconsulta. La instrucción resultará cierta si es cierta toda la comparación con los registros de la subconsulta.
IN. No utiliza comparador, lo que hace es comprobar si el valor se encuentra en el resultado de la subconsulta.
NOT IN. Comprueba si un valor no se encuentra en una subconsulta.

Ejemplo 2. Listado de los concursantes que aún no están participando en ningún juego.

SELECT *
FROM concursante
WHERE cdconcur NOT IN (SELECT cdconcur FROM participa);

Ejemplo 3. Datos de los concursantes que son ídolos de otros concursantes.

SELECT *
FROM concursante
WHERE cdconcur IN (SELECT cdidolo FROM concursante);

Ejemplo 4. Código, nombre, y cuota de los concursantes con mayor que todos los concursantes del equipo '01'.

SELECT cdconcur, nombre, cuota_inscri
FROM concursante
WHERE cuota_inscri > ALL (SELECT cuota_inscri FROM concursante WHERE cdequipo='01');

Observa que > ALL indica mayor que todos, y es equivalente a la siguiente subconsulta expresada como mayor que el máximo:

SELECT cdconcur, nombre, cuota_inscri
FROM concursante
WHERE cuota_inscri > (SELECT MAX(cuota_inscri) FROM concursante WHERE cdequipo='01');

Ejemplo 5. Listado de juegos con valor en megusta, menor que alguno de los juegos del equipo '03'

SELECT *
FROM juego
WHERE megusta < ANY (SELECT megusta FROM juego WHERE cdequipo ='03');

Observa que < ANY indica menor que alguno, y es equivalente a la siguiente subconsulta expresada como menor que el máximo:

SELECT *
FROM juego
WHERE megusta < (SELECT MAX(megusta) FROM juego WHERE cdequipo ='03');

Ocultar

Dentro de una consulta se pueden usar varias subconsultas enlazadas con el operador AND. Las subconsultas se pueden usar dentro de otras sentencias como DELETE, INSERT y UPDATE.

Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la cláusula WHERE de otra subconsulta o incluso como ítem de una SELECT.

También pueden aparecer subconsultas tras las cláusulas FROM y HAVING. (A la subconsulta tras FROM se le denomina tabla derivada y debe llevar un alias de tabla)

Reflexiona

Ocultar

En la práctica una consulta consume mucho más tiempo y memoria cuando se incrementa el número de niveles de anidamiento, además resulta más difícil de leer y de mantener cuando contiene más de dos niveles de anidamiento.

Ejercicio Resuelto Subconsultas sencillas

Ocultar

En la base de datos campeonato, realiza las siguientes consultas:

Código, nombre y cuota del o los concursantes con la mayor cuota. (La máxima) .
Datos del o los concursantes que se inscribieron en la fecha más antigua.
Datos de los juegos con la misma dificultad que el nombre del juego 'Elvenar'.
Datos de los juegos cuyos equipos son de Andalucía.

Ejercicio resuelto Subconsultas en WHERE, FROM, SELECT y HAVING

Ocultar

En la base de datos campeonato

Obtener los nombres y fecha de inscripción de los concursantes con año de inscripción más reciente y que no participan en juegos.
Obtener el número medio de puntos conseguido en juegos.
Para cada concursante, su código, nombre y nombre de su equipo.
Código de concursante y total de puntos en sus juegos, para los concursantes que han obtenido más puntos en juegos, que los que se hayan obtenido en el juego 'FOE'

Retroalimentación

-- 1.- Subconsulta tras WHERE.

SELECT nombre, fecha_inscri
FROM concursante
WHERE YEAR(fecha_inscri)=(SELECT MAX(YEAR(fecha_inscri)) FROM concursante)
AND cdconcur NOT IN (SELECT cdconcur FROM participa);

-- 2 .- Subconsulta tras FROM. (A la subconsulta tras FROM se le denomina tabla derivada y debe llevar un alias de tabla)

SELECT AVG(suma_puntos)
FROM (SELECT cdjuego, SUM(puntos) AS 'suma_puntos' 
            FROM participa
            GROUP BY cdjuego) AS dev;

-- 3 .- Subconsulta tras SELECT.

SELECT c.cdconcur, c.nombre, (SELECT e.nombre
                                        FROM equipo e
                                        WHERE c.cdequipo=e.cdequipo)  as 'equipo'    
FROM concursante c;

-- 4.- Subconsulta tras HAVING.

SELECT cdconcur, SUM(puntos) 
FROM participa
GROUP BY cdconcur
HAVING SUM(puntos)> (SELECT SUM(puntos) 
                        FROM participa 
                        WHERE cdjuego='FOE'); -- puntos totales del juego FOE

Autoevaluación

Ocultar

Indica si la siguiente afirmación es verdadera o falsa.

Retroalimentación

Falso

Una subconsulta puede aparecer también detrás de un FROM, y en ese caso, a la tabla que genera se le denomina tabla derivada y es obligatorio ponerle un alias.

Para saber más

Ocultar

Puedes conusltar otros ejemplos de subconsultas y más posibilidades de uso en el siguiente enlace:

Subconsultas en MySQL

14.- Ejemplos y casos prácticos resueltos.

Caso práctico

Ocultar

Siguiendo los pasos de Noiba, ahora sí, estamos listos para escribir cualquier tipo de consulta en SQL que nos permita acceder a la información almacenada. Para poner en práctica todo lo aprendido Noiba junto a Vindio se atreven a gestionar algunos proyectos que ha llegado a BK Sistemas Informáticos.

Uno de esos proyectos consiste en gestionar la base de datos para una Web de alquileres online de disfraces, otro trata de gestionar la base de datos de un campeonato de juegos JamToday y otro en la gestión de la base de datos de una Agencia de Viajes y la gestión de las reservas.

Ocultar

En los siguientes apartados vamos a resolver algunos casos prácticos de realización de consultas en las que intervengan las cláusulas de la sentencia SELECT vistas en esta unidad, así como la creación de vistas (VIEW) para facilitar la realización de consultas complejas, reutilizando su código. También verás ejemplos de cómo el uso de índices (INDEX) adecuados, optimiza la realización de determinadas consultas.

14.1.- Disfraces.

Ocultar

El comercio Disfraces BK dispone de una base de datos para gestionar los alquileres de disfraces a sus clientes. La base de datos de nombre disfraces está formada por las tablas DISFRAZ, CATEGORIA, ALQUILER y CLIENTE, y su modelo o esquema relacional es el siguiente:

Ilustración que muestra el modelo o esquema relacional de D disfraces — MySQL Workbench (Elaboración propia)

CATEGORIA: almacena el código y nombre de una determinada categoría de disfraces.
DISFRAZ: almacena los datos de los disfraces disponibles para cada categoría. Para cada disfraz se guarda en cantidad el total de unidades para alquilar de ese disfraz y en alquilados el total de unidades alquiladas hasta el momento y en preciodia, el precio diario por alquiler.
CLIENTE: almacena los datos de los clientes registrados que pueden alquilar un disfraz.
ALQUILER: almacena el cliente, disfraz, fecha en la que ha sido alquilado, fecha de devolución y si está pagado.

¿Cómo funciona el alquiler de disfraces?

Cuando se va a alquilar un disfraz, se comprueba si el número de unidades alquiladas (columna alquilados) es inferior a la cantidad de unidades existente (columna cantidad) de ese disfraz.
- Si alquilados<cantidad, se alquila el disfraz insertando una fila en la tabla de ALQUILER e incrementando en 1 la columna alquilados de la tabla DISFRAZ, ya que para cada unidad alquilada se hace un registro en la tabla ALQUILER.
- En caso contrario, el disfraz no se puede alquilar, pues no hay unidades disponibles.

Disfraces.

Ocultar

El comercio Disfraces BK nos solicita que le resolvamos las siguientes consultas y que intentemos optimizar algunas de ellas que se realizan con bastante frecuencia. También le interesa que dejemos almacenada la definición de las consultas más complejas para poder reutilizar ese código.

Lo que nos solicita es lo siguiente:

Listado con el nombre de cada disfraz, su categoría y las unidades actualmente disponibles. El listado debe estar ordenado alfabéticamente por disfraz.
Listado de todos los clientes ordenado alfabéticamente por apellidos y nombre. En una columna debe concatenarse los apellidos y el nombre.
Listar los clientes cuyo nombre se ajusta a un determinado patrón, por ejemplo todos los cliente cuyo nombre empieza por 'A' o sus apellidos terminan en 'da'.
Listar el nombre y precio de los disfraces con precio comprendido entre 9 y 11 euros diarios. Ordenar el listado de mayor a menor precio.
Listado con los datos de 4 de los disfraces más caros.
Listado con los datos de los disfraces con precio inferior al precio medio.
Listar el nombre de los disfraces de determinadas categorías y las unidades disponibles, por ejemplo los de categoría de nombre 'Piratas', 'Época' o 'Fantasía'.
Listar para cada categoría el total de disfraces diferentes y el precio medio de los disfraces de cada categoría, ordenado de mayor a menor precio medio.
La consulta 8, pero mostrando solamente las categorías con precio medio inferior a 10€.
Listar el nombre de los disfraces que tienen unidades disponibles de una determinada categoría de disfraz, por ejemplo de la categoría 'Futurista'. Esta operación se realiza con bastante frecuencia, por lo que se requiere optimizar esta consulta mediante índices. (INDEX)
Listar el nombre y categoría de los disfraces que hay alquilados, el nombre del cliente a quién se alquila, fecha de alquiler y días que faltan para su devolución. Ordenado primero de menos a más días para la devolución, y por apellidos y nombre de cliente. Esta consulta se realiza con frecuencia y se creará para ella una vista (VIEW)
Listado con el nombre y apellidos de todos los clientes y nombre y fecha de devolución del disfraz alquilado. Si el cliente no tiene alquiler de disfraces debe aparecer en el listado y se debe poner '***' dónde proceda por no tener alquileres.

Para desarrollar y probar las consultas puedes descargar la base de datos disfraces desde aquí (zip - 1,40 KB).

14.2.- Jamtoday.

Ocultar

Disponemos de la base de datos de nombre jamtoday para gestionar la participación de estudiantes y profesorado en la creación o gestión de videojuegos educativos en la Asociación BK sin ánimo de lucro. Los estudiantes y profesorado se catalogan como concursantes que pueden o no integrarse en un equipo. Los equipos son los que crean y gestionan los juegos. Por otra parte, cualquier concursante puede votar con cierta puntuación los juegos que le parezcan más interesantes. Además, todos los concursantes pueden aportar de forma periódica un donativo a la Asociación BK.

Sobre esta base de datos nos piden realizar una serie de consultas para obtener cierta información de interés. Antes de redactar las consultas, debemos tener claro las columnas de cada tabla y la forma en la que éstas se relacionan. Por ello, es importante conocer el modelo de la base de datos, tanto su modelo Entidad/Relación, como el modelo Relacional. Estos modelos son los siguientes:

El modelo ENTIDAD/RELACIÓN de la base de datos es el siguiente:

Ilustración del diagrama ER de la base de datos — Software DIA (Elaboración propia)

El MODELO RELACIONAL (modelo lógico) de la base de datos es el siguiente:

Ilustración del Modelo relacional de la base de datos — Libre Office (Elaboración propia)

El modelo de la base de datos obtenido mediante ingeniería inversa y personalizado según el modelo Entidad/Relación que nos dan, es el siguiente:

Ilustración del Modelo Base de datos en Workbench — MySQL Workbench (Elaboración propia)

JAMTODAY

Ocultar

Sobre la Base de Datos Jamtoday, la Asociación BK nos pide que resolvamos las siguientes consultas que son de su interés:

Listado con todos los datos de los concursantes sin equipo asignado ordenado por código de concursante.
Listado de los concursantes cuyo donativo está entre 10 euros y 30 euros, inclusive, que se dieron de alta alta el 2022. En el listado mostrar el código, nombre, fecha de alta, donativo y rol del concursante.
Listado de juegos cuyo equipo es alguno de estos: 1, 3, 5 o 7. En el listado interesa que se muestre el código, nombre del juego y código de su equipo.
Listado con todos los datos de los concursantes de rol 'programador'. Este tipo de consulta basada en el nombre del rol se suele hacer con bastante frecuencia, por lo que interesa ver si se puede optimizar de alguna forma, como por ejemplo con índices (INDEX).
Listar el número total de concursantes, la media de sus donativos con dos decimales y el valor del mayor y el menor donativo.
Listado en el que aparezca el nombre de cada rol de concursante y el total de concursantes por cada rol. Este listado debe estar ordenado de más a menos concursantes por rol.
Listado donde aparezca el código de los juegos en los que se apuesta y total de puntos que se apuesta por él. En el listado solo se deben mostrar los juegos en los que se apuesta en total 100 o más puntos.
Listado con el código y nombre de cada concursante, su rol, el código, nombre y categoría de su equipo. En el listado deben aparecer solo los concursantes con equipo, y el listado debe ordenarse por código de equipo de forma ascendente.
Listado con el código, nombre y año de alta de cada concursante y sus habilidades o skills.
Si el concursante no tiene habilidades, debe aparecer 'SIN SKILL' donde corresponda.
Listado donde aparezcan los códigos y nombres de todos los juegos, nombres de sus equipos y el nombre del concursante que apuesta en ese juego, con los puntos apostados. En el listado deben aparecer todos los juegos. Si no se apuesta por el juego, donde corresponda debe aparecer '***'. Esta consulta se realiza de forma recurrente de manera que interesa tenerla definida en una vista (VIEW).
Código, nombre y donativo, del o los concursantes que tienen el mayor donativo.
Listado con el código, nombre y rol, de los concursantes del mismo equipo que 'María Azul'.
Listado con los nombres de los juegos en los que aparezca la palabra “galaxy”, o que empiecen por C, y cuyo equipo sea alguno de estos: 'Monster', 'Monolito' o 'Indalo'.

Para desarrollar y probar las consultas puedes descargar la base de datos jamtoday desde aquí (zip - 2,31 KB).

Se proporcionan dos scripts, el primero que debes ejecutar es el que crea la estructura de la base de datos (bd_jamtoday_create.sql) y después el que inyecta o inserta los datos (bd_jamtoday_datos.sql).

Condiciones y términos de uso de los materiales

Ocultar

Materiales desarrollados inicialmente por el Ministerio de Educación, Cultura y Deporte y actualizados por el profesorado de la Junta de Andalucía bajo licencia Creative Commons BY-NC-SA.

Antes de cualquier uso leer detenidamente el siguente Aviso legal

Ocultar

Historial de actualizaciones

Versión: 02.00.00	Fecha de actualización: 24/05/23	Autoría: Isabel Cruz Granados
Ubicación: Apartado 1.1 Asistentes y herramientas gráficas de diseño Mejora (tipo 1): En el apartado Recomendación, el vídeo no funciona, les he puesto este como alternativo. https://youtu.be/TQTcQR2GZAo Ubicación: En la Unidad Mejora (tipo 3): * Incluir al principio de la unidad, el tutorial de la instalación de la base de datos con la que se va a trabajar en todos los ejemplos de la unidad, y que actualmente está en el apartado 3.1. El resto de este apartado se unifica con el aprtado 3.2 que trata sobre la sentnecia . * A partir del apartado 1.2 se deben incluir ejemplos y ejercicios resueltos que se puedan probar sobre la base de datos instalada al principio de la unidad. Pues muchos de los que hay, no se pueden probar ya que aluden a una base de datos inexistente. Así puede probarlos el alumnado y comprobar el resultado. * El actual apartado 3.2, unificado con la parte de 3.1 sobre , se puede desglosar en subapartados en los que se puedan diferenciar claramente las distintas cláusulas más comunes de la y ejemplos de caso de uso incial sobre la base de datos de referencia, en los que se profundizará después: FROM, ALL, DISTINCT, ORDER BY, LIMIT, WHERE, GROUP BY, HAVING, JOIN * Incluir más ejemplos, de menor a mayor dificultad sobre consultas básicas que utilizan la claúsula WHERE y operadores lógicos, relacionales, BETWEEN, LIKE e IN(). * Cambiar el orden de los apartados a partir del 7. Consultas multitabla para dar mayor coherencia entre ellos, e ando algún apartado nuevo quedando como: 6.- Funciones, 7.-Consultas resumen, 8.- Consultas agrupadas, 9.-Consultas multitabla: composiciones internas y externas con JOIN, 10.-Otras composiciones JOIN. 11.- Otras consultas multitabla: UNION, INTERSEC, MINUS, 12.-Subconsultas. * Incluir al final de la unidad varios tutoriales o videotutoriales, que pueda probar el alumnado, con Ejemplos de: - consultas sencillas o básicas a una sola tabla, con WHERE, BETWEEN, LIKE e IN() y operadores lógicos y aritméticos. - uso de funciones del SGBD - agrupamientos de tablas (funciones de agregado y uso de HAVING) - consultas a varias tablas con WHERE y JOIN. (Join interno y externo) - ejemplos de creación de vistas con consultas complejas para comprobar sus ventajas de uso. - ejemplos de creación y uso de índices para comprobar cómo se pueden optimizar las consultas, comprobando ese hecho con EXPLAIN. * Modificar el mapa conceptual según los nuevos contenidos. * Modificar las orientaciones al Alumnado según los nuevos contenidos. Ubicación: Mapa Mejora (Mapa conceptual): Actualizado según los nuevos contenidos. Ubicación: Tabla de contenidos Mejora (Orientaciones del alumnado): Actualizado el índice según los nuevos contenidos (desde apartado 4.- a 14.-)

Versión: 02.00.00

Fecha de actualización: 24/05/23

Autoría: Isabel Cruz Granados

Ubicación: Apartado 1.1 Asistentes y herramientas gráficas de diseño
Mejora (tipo 1): En el apartado Recomendación,
el vídeo no funciona, les he puesto este como alternativo. https://youtu.be/TQTcQR2GZAo

Ubicación: En la Unidad
Mejora (tipo 3): * Incluir al principio de la unidad, el tutorial de la instalación de la base de datos con la que se va a trabajar en todos los ejemplos
de la unidad, y que actualmente está en el apartado 3.1. El resto de este apartado se unifica con el aprtado 3.2 que trata sobre la sentnecia .
* A partir del apartado 1.2 se deben incluir ejemplos y ejercicios resueltos que se puedan probar sobre la base de datos instalada al principio de la unidad.
Pues muchos de los que hay, no se pueden probar ya que aluden a una base de datos inexistente. Así puede probarlos el alumnado y comprobar el resultado.
* El actual apartado 3.2, unificado con la parte de 3.1 sobre , se puede desglosar en subapartados en los que se puedan diferenciar
claramente las distintas cláusulas más comunes de la y ejemplos de caso de uso incial sobre la base de datos de referencia, en los que se profundizará después:
FROM, ALL, DISTINCT, ORDER BY, LIMIT, WHERE, GROUP BY, HAVING, JOIN
* Incluir más ejemplos, de menor a mayor dificultad sobre consultas básicas que utilizan la claúsula WHERE y operadores lógicos, relacionales,
BETWEEN, LIKE e IN().
* Cambiar el orden de los apartados a partir del 7. Consultas multitabla para dar mayor coherencia entre ellos, e ando algún apartado nuevo quedando como:
6.- Funciones, 7.-Consultas resumen, 8.- Consultas agrupadas, 9.-Consultas multitabla: composiciones internas y externas con JOIN,
10.-Otras composiciones JOIN. 11.- Otras consultas multitabla: UNION, INTERSEC, MINUS, 12.-Subconsultas.
* Incluir al final de la unidad varios tutoriales o videotutoriales, que pueda probar el alumnado, con Ejemplos de:
- consultas sencillas o básicas a una sola tabla, con WHERE, BETWEEN, LIKE e IN() y operadores lógicos y aritméticos.
- uso de funciones del SGBD
- agrupamientos de tablas (funciones de agregado y uso de HAVING)
- consultas a varias tablas con WHERE y JOIN. (Join interno y externo)
- ejemplos de creación de vistas con consultas complejas para comprobar sus ventajas de uso.
- ejemplos de creación y uso de índices para comprobar cómo se pueden optimizar las consultas, comprobando ese hecho con EXPLAIN.
* Modificar el mapa conceptual según los nuevos contenidos.
* Modificar las orientaciones al Alumnado según los nuevos contenidos.

Ubicación: Mapa
Mejora (Mapa conceptual): Actualizado según los nuevos contenidos.

Ubicación: Tabla de contenidos
Mejora (Orientaciones del alumnado): Actualizado el índice según los nuevos contenidos (desde apartado 4.- a 14.-)

Versión: 01.00.00	Fecha de actualización: 23/07/20
Versión inicial de los materiales.

Pregunta

Respuestas

Retroalimentación

Solución

Modelo ER de la base de datos CAMPEONATO

Modelo Relacional de la base de datos CAMPEONATO

Carga o despliega la base de datos en MySQL

Ejecuta el script para implantar la base de datos CAMPEONATO

Muestra la base de datos en tu Servidor

Pon en uso la base de datos y ejecuta algunas consultas

Creación de consultas desde un asistente para consultas.

Creación de consultas desde la ventana de diseño de consultas.

Conexión con Workbench al servidor MySQL

En el Editor de SQL activamos base de datos y mostramos sus tablas

En el editor de SQL escribimos una consulta, sin errores

En el Editor SQL escribimos una consula, con errores

Retroalimentación

Pregunta

Respuestas

Retroalimentación

Solución

Pregunta

Respuestas

Retroalimentación

Solución

Todas las columnas de la tabla (*).

Alias de columnas.

Columnas calculadas.

Poniendo el nombreTabla.nombreColumna.

Consultar tablas de una base de datos que no está en uso.

Pregunta 1

Retroalimentación

Formato básico de una consulta.

Cláusula FROM.

Alias de tablas.

Pregunta 1

Retroalimentación

Cláusula WHERE . Poniendo un filtro a la consulta.

Pregunta 1

Retroalimentación

Cláusula ORDER BY. Ordenando el resultado de la consulta.

Retroalimentación

Cláusulas ALL/DISTINCT. Mostrar todas las filas o eliminar repeticiones.

Retroalimentación

Pregunta 1

Retroalimentación

Pregunta 2

Retroalimentación

Cláusula LIMIT. Limitando las filas devueltas.

Retroalimentación

Cláusula GROUP BY. Agrupando filas.

Cláusula HAVING. Filtrando grupos.

Pregunta 1

Retroalimentación

Operador de asignación.

Operadores aritméticos.

Pregunta

Respuestas

Retroalimentación

Solución

Pregunta

Respuestas

Retroalimentación

Solución

Comparación de cadenas de caracteres y de fechas.

Pregunta

Respuestas

Retroalimentación

Solución

Retroalimentación

Retroalimentación

Comparación con patrones de cadena. LIKE.

Retroalimentación

Operador de rango. BETWEEN.

Operador de pertenencia a un conjunto. IN().

Retroalimentación

Pregunta 1

Retroalimentación

Comprobación de valor nulo.

Pregunta

**Todas las columnas de la tabla (*)**.