Módulo 7
Last updated
Was this helpful?
Poco a poco nos acercamos al final de los temas del módulo de base de datos. Este módulo tiene como finalidad, terminar de abordar las temáticas que el lector debe saber manejar de manera optima para la manipulación de datos mediante SQL.
Entre los propósitos de este módulo, tenemos la unificación o composición de tablas que presentan relación entre ellas. En este punto, se hará uso de las claves foráneas que han ayudado a que se presenten dichas relaciones entre las entidades de las bases de datos y de este modo se logre extraer información en las consultas de manera más concisas, que permita tener un panorama mas amplio de los datos que se están proyectado y permitan al lector tomar decisiones sobre dicha información.
Al final del módulo se abordarán algunas sentencias avanzadas SQL que permiten realizar manipulación sobre los datos, tales como la ordenación, agrupación y el manejo de los filtros en la información que le permita al lector realizar operaciones altamente complejas sobre los datos, que se encuentran presenten en las bases de datos que tienen como propósito dar solución a problemas de la vida real.
Este módulo también se apoyará en la base de datos vista en el módulo 6 (se anexa el link para su respectiva consulta )
Observemos y pensemos en la siguiente consulta:
Sin ningún orden en nuestros datos, MySQL debe leer todos los registros de la tabla "estudiante" y efectuar una comparación entre el campo "apellido" y la cadena de caracteres "zapata" para encontrar alguna coincidencia (en la vida real habrá muchas coincidencias). A medida que esta base de datos sufra modificaciones, como un incremento en el número de registros, dicha consulta irá requiriendo un mayor el esfuerzo de la CPU y el uso de memoria necesaria para ejecutarse.
Si tuviéramos una guía telefónica a mano localizaríamos fácilmente a cualquiera con apellido "zapata", yendo al final de la guía, a la letra "Z". El método en sí está dado en función a como están ordenados los datos y en el conocimiento de los mismos. En otras palabras, localizamos rápidamente a "zapata" porque está ordenado por apellido y porque conocemos el abecedario.
Los índices de base de datos son muy similares. Al igual que el escritor decide crear un índice de términos y conceptos importantes de su libro, como administradores de una base de datos decidimos crear un índice respecto a una columna.
Técnicamente un índice se define como un puntero a una fila de una determinada tabla de nuestra base de datos. Un puntero en este contexto, no es más que una forma de referenciar que asocia el valor de una determinada columna o conjunto de columnas con las filas que contienen ese valor o valores en las columnas que componen el puntero.
Los índices mejoran el tiempo de recuperación de los datos en las consultas realizadas contra nuestra base de datos. Pero los índices no son todo ventajas, la creación de índices implica un aumento en el tiempo de ejecución sobre aquellas consultas de inserción, actualización y eliminación realizadas sobre los datos afectados por el índice (ya que tendrán que actualizarlo). Del mismo modo, los índices necesitan un espacio para almacenarse, por lo que también tienen un coste adicional en forma de espacio en disco.
La construcción de los índices es el primer paso para realizar optimizaciones en las consultas realizadas contra nuestra base de datos. Por ello, es importante conocer bien su funcionamiento y los efectos colaterales que pueden producir.
En MySQL hay cinco tipos de índices:
PRIMARY KEY: Este índice se ha creado para generar consultas especialmente rápidas, debe ser único y no se admite el almacenamiento de NULL.
KEY o INDEX: Son usados indistintamente por MySQL, permite crear índices sobre una columna, sobre varias columnas o sobre partes de una columna.
UNIQUE: Este tipo de índice no permite el almacenamiento de valores iguales.
FULLTEXT: Permiten realizar búsquedas de palabras. Sólo pueden usarse sobre columnas CHAR, VARCHAR o TEXT
Para crear un índice, se empleará la siguiente estructura:
Donde:
index_name: es el nombre del índice.
table_name: es el nombre de la tabla donde se va a crear el índice.
index_col_name: nombre de la columna (o columnas) que formarán el índice.
index_type: es el tipo del índice. Se emplea con USING [BTREE | HASH].
Veamos el siguiente ejemplo:
Una vez hemos visto los tipos de índices, vamos a ver los distintos tipos de estructuras que se pueden crear para almacenar los índices junto con las características de cada uno de ellas:
B-TREE: este tipo de índice se usa para comparaciones del tipo =, >, <, >=, <=, BETWEEN y LIKE (siempre y cuando se utilice sobre constantes que no empiecen por %). Para realizar búsquedas empleando este tipo de índice, se empleará cualquier columna (o conjunto de columnas) que formen el prefijo del índice. Por ejemplo: si un índice está formado por las columnas [A, B, C], se podrán realizar búsquedas sobre: [A], [A, B] o [A, B, C].
HASH: este tipo de índice sólo se usa para comparaciones del tipo = o <=>. Para este tipo de operaciones son muy rápidos en comparación a otro tipo de estructura. Para realizar búsquedas empleando este tipo de índice, se emplearán todas las columnas que componen el índice.
Un índice puede ser almacenado en cualquier tipo de estructura, pero, en función del uso que se le vaya a dar, puede interesar crear el índice en un tipo determinado de estructura o en otro. Por norma general, un índice siempre se creará con la estructura de B-TREE, ya que es la estructura más empleada por la mayoría de operaciones.
Las consultas multitabla llamadas así porque están basadas en más de una tabla de base de datos. Para este tema se tratará dos temas puntuales:
Composición de tablas
Unión de tablas
La composición de tablas consiste en concatenar filas de una tabla con filas de otra. En este caso obtenemos una tabla con las columnas de la primera tabla unidas a las columnas de la segunda tabla, y las filas de la tabla resultante son concatenaciones de filas de la primera tabla con filas de la segunda tabla.
Por ejemplo, queremos listar los pedidos con el nombre del representante que ha hecho el pedido, pues los datos del pedido los tenemos en la tabla de pedidos, pero el nombre del representante está en la tabla de empleados y además queremos que aparezcan en la misma línea; en este caso necesitamos componer las dos tablas (Nota: en el ejemplo expuesto a continuación, hemos seleccionado las filas que nos interesan).
Los tipos de composición de tablas son:
Producto cartesiano
JOIN e INNER JOIN
LEFT JOIN y LEFT OUTER JOIN
RIGHT JOIN y RIGHT OUTER JOIN
El producto cartesiano de dos tablas son todas las combinaciones de todas las filas de las dos tablas. Usando una sentencia SELECT se hace proyectando todos los atributos de ambas tablas. Los nombres de las tablas se indican en la cláusula FROM separados con comas, usemos las tablas cliente y pagos de la base de datos Empresa de Confecciones Limitada:
Como se ve, la salida consiste en todas las combinaciones de todas las tuplas de ambas tablas.
El INNER JOIN es otro tipo de composición interna de tablas, permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está indexada.
INNER JOIN selecciona todas las filas de las dos columnas siempre y cuando haya una coincidencia entre las columnas en ambas tablas. Es el tipo de JOIN más común.
El uso de JOIN y de INNER JOIN es el mismo, pero para algunas personas es mejor utilizar INNER JOIN, debido al uso de LEFT y RIGHT JOIN, aunque otros prefieren utilizar JOIN, ya que no necesitan agregar tanto código, ya se convierte en un aspecto de preferencias.
Veamos su sintaxis en SQL:
Lo mas común es que después de las instrucciones ON valla una condición que indica cuales tuplas se proyectan, en caso de no colocar esta instrucción se muestra todo el producto cartesiano tal como se vio anteriormente. Es muy común que en este tipo de condiciones se coloque la llave primaria de una de las tablas y la llave foránea de la otra, aunque no es obligatorio que se haga de esta manera.
Veamos su representación con un gráfico de teoría de conjuntos:
Realicemos un ejemplo con las tablas cliente y pagos de la base de datos Empresa de Confecciones Limitada:
Tipo de composición externa, que combina los valores de la primera tabla con los valores de la segunda tabla. Siempre devolverá las filas de la primera tabla, incluso aunque no cumplan la condición. A diferencia de un INNER JOIN, donde se busca una intersección respetada por ambas tablas, con LEFT JOIN damos prioridad a la tabla de la izquierda, y buscamos en la tabla derecha.
Si no existe ninguna coincidencia para alguna de las filas de la tabla de la izquierda, de igual forma todos los resultados de la primera tabla se muestran.
Veamos su representación con un gráfico de teoría de conjuntos:
Veamos su sintaxis en SQL:
Realicemos un ejemplo con las tablas cliente y pagos de la base de datos Empresa de Confecciones Limitada:
Note la diferencia, en el caso de INNER JOIN, la consulta retorno 273 filas, mientras con LEFT JOIN se obtuvieron 297 filas.
Tipo de composición externa. Es igual que LEFT JOIN pero al revés. Ahora se mantienen todas las filas de la tabla derecha (tablaB). Las filas de la tabla izquierda se mostrarán si hay una coincidencia con las de la derecha. Si existen valores en la tabla derecha pero no en la tabla izquierda, ésta se mostrará null.
Veamos su representación con un gráfico de teoría de conjuntos:
Veamos su sintaxis en SQL:
Realicemos un ejemplo con las tablas cliente y pagos de la base de datos Empresa de Confecciones Limitada:
También se puede encontrar otros tipos de JOIN, que es muy simple de implementar y entender mediante este grafico de conjuntos, que resume los ya explicados y otros que se pueden realizar en las diferentes consultas donde necesitemos usarlos para solucionar algún tipo de problemática con estas herramientas que nos ofrece el motor de base de datos.
También es posible realizar la operación de álgebra relacional unión entre varias tablas o proyecciones de tablas.
Para hacerlo se usa la sentencia UNION que permite combinar varias sentencias SELECT para crear una única tabla de salida.
Las condiciones para que se pueda crear una unión son las mismas que vimos al estudiar el álgebra relacional: las relaciones a unir deben tener el mismo número de atributos, y además deben ser de dominios compatibles.
Veamos el siguiente ejemplo:
La Empresa de Confecciones Limitada maneja dos bodegas donde almacena productos para empresa que importan y exportan sus productos, en estas bodegas se maneja la siguiente información, que se describe en la siguiente tabla.
Bodega 1
Bodega 2
- Identificador elemento almacenado
- Nombre de elemento almacenado
- Cantidad de elemento almacenado
- Nit Empresa que importa
- Correo de empresa importadora
- Identificador elemento almacenado
- Nombre de elemento almacenado
- Cantidad de elemento almacenado
- Nit Empresa exportadora
- Teléfono celular empresa exportadora
De la información almacenada en las dos bodegas se requiere conocer los artículos almacenados en ambas bodegas, veamos esta consulta:
Cuando usa la instrucción SELECT para consultar datos en una tabla y obtiene todas las filas de esa tabla, esto en muchas circunstancias es innecesario porque la aplicación solo puede procesar un conjunto de filas a la vez.
Para obtener las filas de la tabla que satisfacen una o más condiciones, use la cláusula WHERE de la siguiente manera:
En la cláusula WHERE, especifica una condición de búsqueda para filtrar las filas devueltas por la cláusula FROM. La cláusula WHERE solo devuelve las filas que hacen que la condición de búsqueda se evalúe TRUE.
La condición de búsqueda es una expresión lógica o una combinación de múltiples expresiones lógicas. En SQL, una expresión lógica a menudo se denomina predicado.
Usemos la tabla empleado de la base de datos Empresa de Confecciones Limitada para ver algunos escenarios donde se puede ser usada la cláusula WHERE.
La tabla cuenta con 122 registros, veamos como el WHERE reduce el total de registros.
A. Encontrar filas usando una igualdad simple
La siguiente declaración recupera todos los clientes con estado igual a CA:
B. Encontrar filas que cumplan dos condiciones
El siguiente ejemplo devuelve empleados que cumplen dos condiciones: la ciudad San Francisco y el empleado de ventas es el 1165. En este caso se debe emplear el operador lógico AND para combinar las dos condiciones, veamos:
C. Encontrar filas con el valor entre dos valores
La siguiente declaración devuelve los empleados que tiene el limite de crédito entre $100.000 y $300.000, veamos:
D. Encontrar tuplas que cumplan con cualquiera de las dos condiciones
El siguiente ejemplo devuelve empleados que cumplen cualquiera de las dos condiciones: la ciudad San Francisco o el empleado de ventas es el 1165. En este caso se debe emplear el operador lógico OR, veamos:
E. Encontrar filas que tengan un valor en una lista de valores
El siguiente ejemplo utiliza el operador IN para buscar empleados cuyo país de residencia es: USA, Poland, Germany y France, veamos:
F. Encontrar filas cuyos valores contienen una cadena
El siguiente ejemplo utiliza el operador LIKE para buscar empleados cuyo nombre del cliente contiene la cadena Gifts:
A continuación, se muestran algunos ejemplos que muestran diferentes operadores LIKE con comodines '%' y '_':
Consulta con operador LIKE
Descripción
WHERE pais LIKE 'a%'
Encuentra cualquier valor que comience con "a"
WHERE pais LIKE '%a'
Encuentra cualquier valor que termine con "a"
WHERE pais LIKE '%or%'
Busca cualquier valor que tenga "o" en cualquier posición
WHERE pais LIKE '_r%'
Encuentra cualquier valor que tenga "r" en la segunda posición
WHERE pais LIKE 'a_%'
Busca cualquier valor que comience con "a" y tenga al menos 2 caracteres de longitud
WHERE pais LIKE 'a__%'
Busca cualquier valor que comience con "a" y tenga al menos 3 caracteres de longitud
WHERE pais LIKE 'a%o'
Busca cualquier valor que comience con "a" y termine con "o"
Cuando usa la declaración SELECT para consultar datos de una tabla, no se garantiza el orden de las filas en el conjunto de resultados. Significa que MySQL puede devolver un conjunto de resultados con un orden de filas no especificado.
La única forma de garantizar que las filas del conjunto de resultados estén ordenadas es utilizar la cláusula ORDER BY. A continuación, se ilustra la sintaxis de la cláusula ORDER BY:
Las columnas que aparecen en la cláusula ORDER BY deben corresponder a cualquiera de las columnas de la lista de seleccionada de la tabla especificada en la cláusula FROM.
En segundo lugar, utilice ASC o DESC para especificar si los valores de la columna especificada deben ordenarse en orden ascendente o descendente.
El ASC ordena el resultado del valor más bajo al valor más alto, mientras que con DESC se ordena del valor más alto al más bajo de acuerdo al tipo de dato.
De forma predeterminada, la cláusula ORDER BY utiliza ASC si no especifica explícitamente ninguna opción.
Por tanto, las siguientes cláusulas son equivalentes:
ORDER BY columna1 ASC;
ORDER BY columna1;
Si desea ordenar el conjunto de resultados por varias columnas, especifique una lista de columnas separadas por comas en la cláusula ORDER BY, adicionalmente se le puede colocar el tipo de ordenamiento con ASC o DESC, veamos
Tenga en cuenta que la cláusula ORDER BY siempre se evalúa después de la cláusula FROM y SELECT.
Ordenar la tabla de clientes por el límite de crédito, de modo ascendente.
Ordenar la tabla de clientes por el límite de crédito, de modo descendente.
Ordenar la tabla de pedido por número de orden decentemente y luego por fecha límite de entrega ascendentemente.
La instrucción GROUP BY agrupa las filas que tienen los mismos valores en filas de resumen, como "encontrar el número de clientes en cada país".
La instrucción GROUP BY se usa a menudo con funciones agregadas (COUNT, MAX, MIN, SUM, AVG) para agrupar el conjunto de resultados por una o más columnas.
La cláusula GROUP BY es una cláusula opcional de la declaración SELECT. A continuación, se ilustra la sintaxis de la instrucción GROUP BY:
La cláusula GROUP BY debe aparecer después de las cláusulas FROM y WHERE y antes de las instrucciones HAVING, ORDER BY y LIMIT. A continuación de la instrucción GROUP BY hay una lista de columnas separadas por comas que desea utilizar como criterios de agrupación.
Supongamos que desea agrupar los valores del estado de la tabla pedido en subgrupos, para tal fin se debe agrupar mediante dicha columna, veamos:
Una función agregada realiza un cálculo en varios valores y devuelve un solo valor.
Veamos las funciones agregadas de MySQL más usadas:
La función AVG() de MySQL es una función agregada que le permite calcular el valor promedio de un conjunto.
La función AVG() se usa a menudo junto con la cláusula GROUP BY para calcular el valor promedio para cada grupo de filas en una tabla.
Por ejemplo, para calcular el precio de compra promedio de productos para cada línea de productos, use la función AVG() con la cláusula GROUP BY con la siguiente consulta:
La función COUNT() es una función agregada que devuelve el número de filas en una tabla. La función COUNT() le permite contar todas las filas o solo las filas que coinciden con una condición específica.
La función COUNT(*) se usa a menudo con una cláusula GROUP BY para devolver el número de elementos en cada grupo.
Por ejemplo, esta declaración usa la función COUNT() con la cláusula GROUP BY para devolver el número de productos en cada línea de producto:
La función SUM() es una función agregada que le permite calcular la suma de valores en un conjunto.
La función SUM() se usa a menudo con la cláusula GROUP BY para calcular la suma de cada grupo.
Por ejemplo, puede calcular el monto total de cada pedido usando la función SUM() con la cláusula GROUP BY como se muestra en la siguiente consulta:
MIN
La función MIN() devuelve el valor mínimo en un conjunto de valores. La función MIN() es muy útil en algunos escenarios, como encontrar el número más pequeño, seleccionar el producto menos costoso u obtener el límite de crédito más bajo.
Como otras funciones agregadas, la función MIN() se usa a menudo con la cláusula GROUP BY para encontrar los valores mínimos para cada grupo.
Este ejemplo usa la función MIN() con una cláusula GROUP BY para obtener el producto con el precio de compra más bajo para cada línea de producto:
La función MAX() de MySQL devuelve el valor máximo en un conjunto de valores. La función MAX() es útil en muchos casos, como encontrar el mayor número, el producto más caro y el mayor pago de los clientes.
Para encontrar el valor máximo para cada grupo, use la función MAX con la cláusula GROUP BY.
En el siguiente ejemplo se usa la función MAX() para obtener el pago más grande de cada cliente:
La cláusula HAVING se usa en una declaración SELECT para especificar condiciones de filtro para un grupo de filas o agregados.
La cláusula HAVING se usa a menudo con la cláusula GROUP BY para filtrar grupos según una condición específica. Si GROUP BY se omite la cláusula HAVING, la cláusula se comporta como la cláusula WHERE.
A continuación, se ilustra la sintaxis de la cláusula HAVING:
Veamos el siguiente ejemplo, el cual emplea la cláusula GROUP BY para obtener los números de pedido, el número de artículos vendidos por pedido y las ventas totales de cada uno presentes en la tabla detalle_pedido. De esta consulta solo interesa los resultados donde el total de ventas sean mayor a 10000, veamos:
Defina tres índices para el proyecto PPI y explique cuál es la finalidad de aplicar dichos índices.
Haciendo uso de INNER JOIN, RIGHT JOIN y LEFT JOIN genere las consultas de la siguiente relación entre las tablas empleado y oficina (de dicha consulta solo se requiere conocer número del empleado, su nombre, correo, el telefono de la oficina, el código postal y la ciudad donde se encuentra la oficina). Adjunte cada consulta y explique el resultado obtenido.
De la tabla detalle_pedido se requiere conocer la cantidad de elementos por cada orden, teniendo en cuenta, que solo interesa conocer las ordenes donde esta cantidad sea mayor a 60 (Anexe consulta y resultado).
Se requiere conocer todos los productos del pedido con número de orden 10101. De los productos solo se requiere conocer código del producto, nombre del producto, precio de compra y la cantidad que se solicitó de cada producto en el pedido (Anexe consulta y resultado).
Se requiere conocer todos los empleados que su título termine en Rep (Anexe consulta y resultado).
Se requiere conocer los pagos efectuados en todo el mes de abril del año 2005 (2005-04-01 y 2005-04-30). Adicionalmente se debe conocer el nombre completo (en una sola columna o campo) del cliente que efectuó dicho pago. (Anexe consulta y resultado).
Se requiere consultar todas las oficinas que se encuentran almacenadas en la base de datos, ordenadas por país de manera ascendente; una vez ordenadas por país, se requiere ordenar las oficinas resultantes de cada país por ciudad de manera descendente.
Veamos el script para darle solución a esta problemática de almacenamiento (el script se encuentra al final del script ).
Para algunos de los ejercicios tenga en cuenta el siguiente modelo relacional
