Consulta de datos con SQL (DQL)

En esta guía utilizaremos una base de datos llamada biblioteca cuyo diagrama de entidad-relación se detalla en la Fig. 1, y donde cada tabla almacena lo siguiente:

• autores, editores, clientes y bibliotecarios: identificadores únicos para cada registro y los nombres correspondientes.
• libros: además del identificador único de cada libro y su nombre, los libros también tienen un ISBN de 13 caracteres, un número de edición, y están relacionados con un autor y un editor mediante el uso de claves extranjeras. Como tales, apuntan a los identificadores únicos de esos cuadros, asegurando así que el autor y el editor de un libro determinado ya existe en la base de datos.

• préstamos almacena cada préstamo de libros que se ha hecho. La utilización de author_id, customer_id y book_id como claves externas se complementa con la fecha de préstamo y el status actual (activo o no).

En su forma más simple, el DQL consiste en una declaración SELECT contra una tabla entera. Otros ejemplos incluyen la selección de campos específicos de una o más tablas, la clasificación y agrupación de los resultados, la creación de alias de columna y tabla, y el uso de filtros para reducir los resultados de una búsqueda. Exploraremos todos estos casos (y más) a través de una serie de ejemplos. Vamos a sumergirnos!

Ejemplo 1 – Selección de todas las columnas y registros de una tabla

Para mostrar toda la información de los libros en la tienda, puedes hacer:

1SELECT*FROM libros;

sql

donde la estrella es un comodín que representa todas las columnas de la tabla dada. Puedes ver el resultado en la Fig. 2, donde la salida ha sido truncada para mayor brevedad:

Este enfoque, cuando se utiliza sin restricciones ni filtros, puede afectar negativamente al rendimiento de la consulta. En la mayoría de los casos, es más prudente especificar la lista de campos que le interesan.

Ejemplo 2 – Selección de uno o más campos de una tabla

En este punto, recuperar todos los campos de la tabla de libros puede no tener mucho sentido, ya que todavía no estamos en condiciones de derivar el significado de las columnas author_id y publisher_id . Por lo tanto, si desea ver la lista de nombres e ISBN, puede hacerlo:

1SELECCIONAR nombre de libro, libro_isbn DE libros;

sql

O, alternativamente:

1SELECTDISTINCT nombre_del_libro, libro_isbn DE libros;

sql

Mientras que la primera consulta devuelve todos los registros de los libros y da una idea aproximada del número de copias de cada libro, la segunda le permitirá identificar todas las combinaciones distintas de nombre de libro e ISBN, como se ilustra en la Fig. 3:

Como puedes ver arriba, hay 10 títulos diferentes en la tabla de libros .

Ejemplo 3 – Creación de alias de columna y tabla

Los alias son nombres alternativos para las tablas y las columnas que resultan útiles a la hora de escribir consultas más complejas o de presentar los resultados, respectivamente.

Para crear un alias, usaremos la palabra clave AS seguida de su nombre. La siguiente consulta muestra un alias llamado b para la tabla de libros . Esto significa que podemos usar simplemente b cada vez que necesitemos referirnos a libros . Además, en lugar de nombre_del_libro y nombre_del_libro queremos que la salida muestre Título y ISBN como cabeceras en su lugar (ver Fig. 4):

1234SELECT DISTINCTb.nombre_del_libro AS Título,b.libro_isbn AS ISBNFROM libros AS b;

En la consulta anterior, no es estrictamente necesario utilizar el alias de la tabla como prefijo para las columnas, ya que estamos utilizando sólo una tabla. Sin embargo, es una buena práctica hacerlo ya que será necesario a medida que incluyamos más tablas en nuestras consultas.

Si bien los beneficios de esta técnica pueden no parecer obvios al principio, se harán evidentes a medida que aumente la complejidad de la consulta.

Ejemplo 4 – Uniendo Tablas: Parte 1

Hay al menos un par de buenas razones por las que usamos números enteros como claves externas para referenciar identificadores únicos de otras tablas.

En primer lugar, nos permite evitar la duplicación de información y nos da más control sobre nuestros datos. Desde la perspectiva de la integridad de los datos, tiene más sentido almacenar los nombres reales de los autores en autores , respectivamente, que hacer lo mismo en libros .

En segundo lugar, el almacenamiento de números enteros es – en términos generales – menos costoso que una cadena completa, en términos de uso del disco. De acuerdo con los documentos oficiales de MySQL, el almacenamiento de un entero requiere sólo cuatro bytes, a diferencia de las cadenas en las que se necesitan L + 1 bytes (siendo L la longitud de la cadena). Por lo tanto, se prefiere referenciar a los autores por sus identificaciones (1, 2, 3, o 4) en los libros en lugar de duplicar los nombres que ya están disponibles en los autores , como se puede ver en la Fig. 5:

Dicho esto, aprovechemos la palabra clave JOIN para consultar las tablas de los libros y los autores de simultáneamente y recuperemos la lista de títulos con sus respectivos autores, como se observa en la Fig. 6:

12345SELECTDISTINCTb.nombre_del_libro COMO Título,a.nombre_del_autor COMO autorFROM libros COMO b UNIR a los autores COMO b.id_del_autor = a.id_del_autor;

sql

Dónde:

• a y b son alias de autores y libros, respectivamente.
• Título y Autor son alias para nombre de libro y nombre de autor, respectivamente.
• JOIN (o su equivalente INNER JOIN) nos permite manipular a y b usando la teoría básica de conjuntos. Este tipo particular de JOIN devolverá los registros donde existe el ID_autor en ambas tablas.
• Según cómo se presentan en la consulta, b es la tabla de la izquierda y a es la de la derecha ya que la primera aparece primero después de la palabra clave FROM. Esta distinción es importante cuando se utilizan otros tipos de JOINs (LEFT JOIN o RIGHT JOIN). La Fig. 7 muestra lo que se puede esperar en estos dos casos.

En resumen, LEFT JOIN y RIGHT JOIN devuelven todos los registros comunes a ambos conjuntos, y los que sólo están presentes en las tablas de la izquierda y la derecha, respectivamente.

Ejemplo 5 – Uniendo Tablas: Parte 2

Es importante señalar que puede unirse a tantas mesas como sea necesario. Por ejemplo, la salida de la siguiente consulta (como se muestra en la Fig. 8) indicará quién pidió prestado qué libro a cada bibliotecario y cuándo:

123456789SELECTc.nombre_cliente AS Cliente,b.nombre_libro AS Título,l.fecha_préstamo ASDate,lb.nombre_bibliotecario AS BibliotecarioFROM clientes c JOIN préstamos lON c.identificación_cliente = l.identificación_cliente JUNTA libros b ON b.identificación_libro = l.identificación_libro JUNTA bibliotecarios lb ON lb.identificación_bibliotecario = l.identificación_bibliotecario;

sql

El resultado anterior nos permite identificar a un cliente (James Benson) que ha pedido prestados tres ejemplares del mismo libro (Pilgrim Souls) a la misma bibliotecaria (Julia Roosevelt) en la misma fecha. Esto significa que podría ser el momento de una auditoría.

Ejemplo 6 – Filtrando los resultados: Parte 1

Ahora que hemos visto a un cliente que ha pedido prestado varias copias del mismo libro, ¿no sería interesante hacer una lista de todos los libros que le han prestado?

Para lograr ese objetivo, podemos adjuntar la cláusula WHERE a la consulta anterior e indicar una condición que esperamos que cumpla el resultado establecido. En este caso, nos interesan todos los registros en los que el nombre del cliente es James Benson (ver Fig. 9):

12345678910SELECTc.nombre_cliente AS Cliente,b.nombre_libro AS Título,l.fecha_préstamo ASDate,lb.nombre_bibliotecario AS BibliotecarioFROM clientes c UNIR préstamos LON c. ID_Cliente = ID_Cliente_JUNTAR libros b ON ID_Libro = ID_Libro_JUNTAR bibliotecarios lb ON ID_Libro_Bibliotecario = ID_Libro_Bibliotecario DONDE c.Nombre_Cliente =$0027James Benson$0027;

sql

Si no está seguro de cómo se escribe realmente el nombre del cliente pero está seguro de que contiene las letras hijo en algún lugar, puede usar LIKE de la siguiente manera:

12345678910SELECTc.nombre_cliente AS Cliente,b.nombre_libro AS Título,l.fecha_préstamo ASDate,lb.nombre_bibliotecario AS BibliotecarioFROM clientes c UNIR préstamos LON c. ID_Cliente = ID_Cliente_JUNTO_Libros b ON ID_Libro = ID_Libro_JUNTO_Libros Bibliotecarios lb ON ID_Bibliotecario = ID_Libro_Bibliotecario DONDE c.Nombre_Cliente COMO$0027%son%$0027;

sql

La cláusula LIKE, cuando va seguida del texto encerrado entre signos de porcentaje y comillas simples, devolverá los registros en los que el nombre del cliente contiene hijo – independientemente de su ubicación en la cadena. Si desea restringir los resultados a los registros donde son se encuentra exactamente al principio o al final de la cadena, use LIKE seguido de $0027son%$0027 o $0027%son$0027, respectivamente.

Ejemplo 7 – Filtrar los resultados: Parte 2

Puedes añadir tantas condiciones y tanta lógica a la cláusula WHERE como sea necesario. Suponga que quiere listar todas las copias de las segundas ediciones del autor Cay S. Horstmann:

1234567SELECTb.nombre_del_libro AS Título,a.nombre_del_autor AS AutorFROM libros AS b UNIR autores AS aON b.id_del_autor = a.id_del_autor DONDE b.edición_del_libro =2Y a.nombre_del_autor =$0027Cay S. Horstmann$0027;

sql

Como puedes ver, es posible construir sobre las condiciones previas usando álgebra booleana. En este ejemplo, usamos AND para filtrar los resultados a las segundas ediciones del autor dado solamente.

Otra consulta nos ayudará a ilustrar más este concepto:

123456789101112SELECTb.nombre_libro AS Título,c.nombre_cliente AS Cliente,c.dirección_cliente AS Dirección,l.fecha_préstamo ASDateFROM libros AS b UNIR préstamos AS lON b.libro_id = l.libro_id UNIR clientes AS c ON c. ID_Cliente = ID_Cliente_JUNTO autores AS a ON b.ID_Autor = a.ID_Autor EN EL LUGAR b.Edición_Libro =1AND(a.nombre_del_autor =$0027Cay S. Horstmann$0027OR a.nombre_del_autor =$0027Barbara Abercrombie$0027);

sql

Como se observa en la Fig. 10, la consulta anterior devuelve la lista de clientes que han pedido prestados los libros de la primera edición de los autores Cay S. Horstmann o Barbara Abercrombie. Nótese que los paréntesis que rodean los nombres de los autores son necesarios para preservar la precedencia del operador.

Ejemplo 8 – Ordenar los resultados

La consulta anterior puede mejorarse clasificando los resultados en orden ascendente (por defecto) o descendente en un campo determinado muy fácilmente. Para ordenar por fecha de préstamo descendente, todo lo que necesitamos hacer es añadir la cláusula ORDEN POR como sigue:

12345678910111213SELECTb.nombre_libro AS Título,c.nombre_cliente AS Cliente,c.dirección_cliente AS Dirección,l.fecha_préstamo ASDateFROM libros AS b UNIR préstamos AS lON b.libro_id = l.libro_id UNIR clientes AS c ON c. ID_Cliente = ID_Cliente_JUNTO autores AS a ON b.ID_Autor = a.ID_Autor_DONDE b.Edición_Libro =1AND(a.nombre_del_autor =$0027Cay S. Horstmann$0027OR a.nombre_del_autor =$0027Barbara Abercrombie$0027)ORDENAR POR l.fecha_de_préstamo DESC;

sql

Para clasificar en orden ascendente, elimina la palabra clave DESC. Alternativamente, puede añadir más criterios de clasificación utilizando una lista de nombres de campo separados por comas. Por ejemplo,

12345678910111213SELECTb.nombre_libro AS Título,c.nombre_cliente AS Cliente,c.dirección_cliente AS Dirección,l.fecha_préstamo ASDateFROM libros AS b UNIR préstamos AS lON b.libro_id = l.libro_id UNIR clientes AS c ON c. ID_Cliente = ID_Cliente_JUNTO autores AS a ON b.ID_Autor = a.ID_Autor_DONDE b.Edición_Libro =1AND(a.Nombre_Autor =$0027Cay S. Horstmann$0027OR a.Nombre_Autor =$0027Barbara Abercrombie$0027)ORDENAR por l.fecha_préstamo DESC, b.Nombre_Libro DESC;

sql

producirá esencialmente el mismo resultado que antes, pero clasificará primero por fecha de préstamo y luego por nombres de libros – ambos en orden descendente.

Ejemplo 9 – Agrupación y agregación: Parte 1

A los efectos de la presentación de informes, puede ser necesario contar cuántos libros ha tomado prestados cada cliente. Para lograr ese objetivo podemos utilizar la función de agregado de conteo y la cláusula de GRUPO POR. Cuando se usan en conjunto, nos permiten agrupar por un campo dado y contar el número de registros en cada grupo.

123456SELECTCOUNT(l.customer_id)AS Préstamos,c.nombre_del_cliente AS Préstamos AS l ÚNETE a los clientes AS cON l.customer_id = c.customer_idGROUPBY l.customer_id ORDERBYCOUNT(l.customer_id)DESC;

sql

Como regla general, se puede agrupar por uno (o más) de los campos a los que se ha aplicado una función agregada.

Ejemplo 10 – Agrupación y agregación: Parte 2

En un ejemplo anterior, explicamos el uso de WHERE para filtrar los resultados de una consulta. Desafortunadamente, no puede ser usado en un campo agregado. En su lugar, se puede limitar la búsqueda utilizando la cláusula HAVING seguida del campo agregado y la condición deseada:

1234567SELECTCOUNT(l.customer_id)AS Préstamos,c.nombre_del_cliente AS Préstamos AS l JOIN clients AS cON l.customer_id = c.customer_idGROUPBY l.customer_id HAVINGCOUNT(l.customer_id)>2ORDERBYCOUNT(l.customer_id)DESC;

sql

Observe que la consulta anterior es casi la misma que antes, excepto que hemos conectado la línea

1HAVINGCOUNT(l.customer_id);2

sql

después de agrupar por identificación de cliente, para devolver sólo a los clientes que han pedido prestado más de dos libros. Estrictamente hablando, estamos hablando de los clientes cuyo ID aparece más de dos veces en la tabla préstamos .