MySQL II – Tablas y Consultas

Introducción

Esta es la segunda entrada de una serie sobre MySQL. Donde se tratan la creación de tablas, las diferentes consultas principales sobre dichas tablas y alguna modificación sobre ellas.

Si no has visto la primera entrada. te invito a que procedas a la lectura de ella en el siguiente enlace.

Preparación

La base de datos de ejemplo se encuentra en el siguiente enlace.

La base de datos tiene nada mas y nada menos que 300.000 empleados :-). Así que para hacer pruebas esta bastante bien.

Para windows hay bastante software con entornos gráficos para importar la base de datos. A continuación indico los pasos para importarla desde linux:

Descargar el repositorio:

Y utilizar el cliente de mysql:

Funciones de agregación y agrupamiento

Distinct

La función distinct sirve para devolver valores únicos cuando el resultado de la consulta devuelve filas con un campo repetido.

Por ejemplo, no es lo mismo contar cuantos usuarios existen en la tabla frente a cuantos nombres únicos(primer nombre) existen en dicha tabla:

Count

El método count nos proporciona un recuento del número de registros que coinciden con la condición indicada o en el caso de no indicar una condición, el total de registros de la tabla.

Partiendo de la tabla anterior, se puede contar tanto por los usuarios totales, como por cuantos nombres únicos existen:

AVG

Para calcular la media de un conjunto de valores seleccionados, por ejemplo, el salario medio de los empleados de la base de datos o el salario medio calculado a partir de los salarios únicos:

MIN

Obtener el valor más pequeño de todos los devueltos en la consulta. Por ejemplo, el salario más bajo:

MAX

O de lo contrario, el valor más grande devuelto en la consulta. Por ejemplo, el salario más alto:

SUM

Suma todos los valores devueltos en la consulta. Por ejemplo, la suma de todos los salarios de todos los empleados:

Aritmética en MySQL

También es posible realizar cálculos como sumar, restar, multiplicar o divir.

Por ejemplo, calcular la media de los salarios sin utilizar la función agregada avg:

O sumarle un 10% al salario base:

Group By

Sirve para agrupar la consulta en grupos basándose en una columna o varias. Por ejemplo, se quiere obtener el número de contrataciones que hubo cada día de los días que existen almacenados en la tabla de employees:

A la consulta anterior, también es posible añadirle una condición de order by:

Having

Se utiliza para añadir una condición, cuando se agrupa por columnas. Ya que la palabra clave where no funciona con las funciones agregadas, por ejemplo, mostrar la cantidad de empleados que existen con un mismo apellido, donde solo existan menos de 200, ordenados de manera alfabética y solo los primeros 5 registros:

Nombrado, Claves foráneas y Joins

Para este apartado voy a utilizar de ejemplo el siguiente esquema, es muy sencillo para poder ver mejor los diferentes joins, os dejó el código de creación en este repositorio.

Diseño

Nombrando tablas y columnas

Si os habéis fijado detenidamente en algunas consultas anteriores, habréis visto la palabra clave as, básicamente lo que se consigue es crear un alias o un nombre mas legible cuando una de las columnas de la consulta provenga de un conjunto de funciones agregadas, evitando así esto:

Dejando el conjunto de funciones agregadas

Haciendo uso de alias

O cuando se empiezan a operar con varias columnas y tablas, es importante nombrarlas para hacer la consulta mas legible:

Foreign Keys

Las foreign keys son la manera de relacionar una tabla con otra, mediante una columna. Propagándose el valor de una columna, a la columna de otra.

Por ejemplo, si se coge de ejemplo la tablas del esquema actual. Ya hemos visto que de alguna manera a la hora de hacer una consulta, se utiliza una columna que coincide tanto en una tabla como en otra y estas son las consideradas como foreign keys.

En el esquema de ejemplo, posteriormente a la creación de las tablas, se puede visualizar el código que crea la tabla, en este caso la tabla orders:

Se puede observar como la ejecución devuelve estas dos líneas:

Esto nos indica que la tabla orders se compone de dos foreign keys, donde la columna ‘id_customer’ viene dada de la tabla customers y la columna ‘id_dealer’ viene a raíz de la tabla dealers.

Y después de saber esto.. se puede llegar a pensar, ¿Y bueno que pasaría si elimino un registro de la tabla customers y este registro tiene a su vez registros en la tabla orders referenciados a su id?

Ejemplo:

Obviamente MySQL no deja que ocurra esto de forma predeterminada, a esto se le denomina integridad referencial siendo necesario que la clave externa de una tabla debe corresponder a una fila válida de la tabla a la que hace referencia.

Junto a la foreign key se pueden definir las dos operaciones en la tabla padre que van afectar a las filas de la tabla hija, que son: UPDATE y DELETE.

A estas operaciones se le definen el comportamiento o repercusión que tendrá en la tabla hija, cuando sucedan, las opciones son las siguientes:

  • CASCADE : automáticamente se actualizarán o borrarán las filas relacionadas de la tabla hija.
  • SET NULL : las claves foráneas de las filas relacionadas en la tabla hija se pondrán a null. Hay que asegurarse que la columna en la tabla hija no tiene la opción not null asociada o se recibirán errores.
  • RESTRICT : es la opción configurada por defecto y evita cualquier tipo de operación cuando se intente hacer sobre la tabla padre.

Producto cartesiano y joins

Cuando se realizan consultas a dos o más tablas, y no se hace uso de la clausula where el motor de búsqueda realiza el producto cartesiano de todas las tablas incluidas. Evitando así la ambigüedad de los campos.

Inner Joins

Con la palabra clave inner join se obtienen todos los registros que coinciden en todas las tablas indicadas.

Ejemplos:

Join con dos tablas

Join con tres tablas

Left Join

Devuelve todos los registros de la tabla izquierda (la indicada en el FROM) y todos los registros que coinciden con los de la tabla derecha. El resultado es NULL por parte del lado derecho, si no existe coincidencia.

Ejemplo:

Las filas con id NULL, se debe a que hay clientes que no han realizado ningún pedido y por lo tanto no hay ninguna fila en la tabla orders con el id de dicho cliente.

Right Join

Devuelve todos los valores de la tabla derecha (la definida después del RIGHT JOIN) y las coincidencias con la tabla de la izquierda (FROM). El resultado será null en el lado derecho, si no existe coincidencia.

Ejemplo:

Joins en múltiples tablas

Cuando quieres devolver campos de diversas tablas en el select y solo resultados que realmente exista una relación entre las tablas.

Consultas Combinadas

Union

Sirve para unir la salida de una consulta con otra, por ejemplo:

Se tiene estas dos consultas por separado:

Si se desea unir ambas consultas, eliminando los registros que están duplicados, hay que utilizar la palabra clave union:

Si se quiere tener todos los registros por ambas consultas, sin eliminar los duplicados utilizar union all:

Subconsultas

Los joins son necesarios siempre y cuando se quieran campos de diferentes tablas en la consulta. Cuando solo se quiere consultar campos de una misma tabla se puede utilizar las subconsultas.

Aquí destacar la diferencia de dos “operadores”, in y =. Ya que puede ser confuso a veces, de todas formas con ejemplos esto se ve mejor 😉

El operativo “=” solo se debe usar cuando se sepa con seguridad que la subconsulta no va a devolver más de un registro. Ya que de lo contrario devolvería un error.

Por ejemplo, se quiere saber quien fue el cliente que hizo el último pedido:

Si de lo contrario no se hubiera especificado la condición en la fecha, y la última consulta devolviese varios valores, ocurriría lo siguiente:

Para ver un ejemplo del operador IN, se quiere consultar los clientes que han hecho un pedido alguna vez:

O por ejemplo, si se quiere conocer los clientes que son de ciertas ciudades:

Modificando schemas

Añadir una columna

Aunque lo ideal es no tener que manipular el esquema de la base de datos, a veces bien sea por despiste o porque la idea de negocio este cambiando constantemente, es necesario modificar la estructura de las tablas.

El siguiente ejemplo, añade una nueva columna a la tabla customers llamada email:

Por defecto la nueva columna se añade al final de la tabla, pero existe la posibilidad de especificarle la posición de la nueva columna dentro de la tabla con las palabras claves:

  • first : colocando así la nueva columna en la primera posición de la tabla.
  • after : coloca la nueva columna después de la columna existente en la tabla que se le especifique.

Ejemplo:

O bien si se quiere eliminar una columna:

Añadir una foreign key

Bueno como ya se comento en el otro apartado, el uso de las foreign key es recomendable para asegurar la integridad de nuestros datos almacenados.

Las foreign key se pueden asignar tanto a la hora de crear una tabla, como posteriormente.

Para realizar un ejemplo, vamos a crear dos tablas library y books con algún registro de prueba.

¿Te has dado cuenta de que has podido introducir un libro que tiene como referencia una biblioteca con id 10?, esto no debería de ser posible ya que no tiene sentido que se tenga almacenado en la base de datos relaciones cuando la referencia no existe.

Por eso, las foreign key nos garantiza que esto no ocurra, si se añade la fk para referenciar al campo id de la tabla library en la tabla book, pero antes eliminar el registro existente en dicha tabla:

Si ahora se intenta insertar nuevamente el registro anterior donde el id 10 no existe en la otra tabla, devolverá:

Así se evita tener relaciones inexistentes en nuestra base de datos 😉

Si se quiere eliminar la foreign key:

Si se desea saber si una tabla tiene una columna referenciada y a que tabla, se puede ejecutar la siguiente instrucción:

Añadir índices

Por defecto a las claves primarias y las claves foráneas se les crea automáticamente un index.

Cuando la cantidad de información almacenada empieza a ser llamativa, y hay una aplicación que hace uso de la base de datos hay consultas que pueden demorarse bastante en devolver el resultado, es por esto, que cuando se sabe que hay una consulta especifica que hace uso de un campo determinado bastantes veces, se le crea un index a dicha columna para optimizar el tiempo de respuesta. Cuando se crea un índice se crea una copia ordenada de esa columna, para encontrar antes las filas que estén asociadas a la condición de la consulta.

Cuando realmente se ve la diferencia es con millones de registros. Pero el siguiente ejemplo deja una ligera idea del resultado. La siguiente consulta devuelve 184 registros de 300.000 existentes en esa tabla en 0,08 segundos:

Ahora creamos un index para la columna last_name, en este caso tarda 0,5 segundos en crearlo para 300.000 registros, por ejemplo si hubiera 1 millón de registros se demoraría unos 2 segundos.

Si ahora se hace la misma consulta que antes donde la condición es el valor del campo last_name será mucho más inferior el tiempo de respuesta:

Para eliminar un index:

Esto es todo por esta entrada, pero continuará 😉

Autor entrada: CharlieJ

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *