Consultas SELECT Avanzadas y Uso de Procedimientos Almacenados en MySQL

El comando SELECT en MySQL es una herramienta poderosa que permite realizar consultas complejas y detalladas sobre una base de datos. Al combinar SELECT con procedimientos almacenados, puedes crear soluciones dinámicas y eficientes para manejar grandes volúmenes de datos y operaciones repetitivas. En este artículo, exploraremos técnicas avanzadas de SELECT y cómo utilizar procedimientos almacenados en MySQL.

Consultas SELECT Avanzadas

Las consultas avanzadas en MySQL pueden incluir subconsultas, funciones de ventana, CTEs (Common Table Expressions), y combinaciones de varias tablas mediante JOINs complejos.

1. Subconsultas

Las subconsultas son consultas anidadas dentro de otra consulta y pueden ser muy útiles para operaciones complejas.

Ejemplo de subconsulta:

SELECT nombre, salario
FROM empleados
WHERE salario > (SELECT AVG(salario) FROM empleados);

Este comando selecciona a los empleados cuyo salario es mayor que el salario promedio.

2. Funciones de Ventana

Las funciones de ventana permiten realizar cálculos sobre un conjunto de filas relacionadas con la fila actual, sin colapsar las filas en una sola.

Ejemplo de función de ventana:

SELECT nombre, salario, RANK() OVER (ORDER BY salario DESC) AS ranking
FROM empleados;

Este comando asigna un ranking a los empleados basado en su salario en orden descendente.

3. Common Table Expressions (CTEs)

Las CTEs son definiciones de consultas temporales que pueden ser referenciadas dentro de la instrucción SELECT.

Ejemplo de CTE:

WITH CTE_SalarioPromedio AS (
    SELECT departamento, AVG(salario) AS SalarioPromedio
    FROM empleados
    GROUP BY departamento
)
SELECT * FROM CTE_SalarioPromedio
WHERE SalarioPromedio > 5000;

Este comando define una CTE que calcula el salario promedio por departamento y luego selecciona solo aquellos departamentos con un salario promedio mayor a 5000.

4. JOINs Complejos

Combinar varias tablas usando JOINs complejos permite realizar consultas muy detalladas y específicas.

Ejemplo de JOIN complejo:

SELECT p.nombre AS proyecto, e.nombre AS empleado, l.nombre AS lenguaje
FROM proyectos p
JOIN empleados e ON p.empleado_id = e.id
JOIN lenguajes l ON p.lenguaje_id = l.id
WHERE e.departamento = 'Desarrollo' AND l.tipo = 'Backend';

Este comando selecciona los nombres de proyectos, empleados y lenguajes asociados, filtrando por el departamento de desarrollo y lenguajes de backend.

Procedimientos Almacenados

Los procedimientos almacenados son conjuntos de instrucciones SQL que se almacenan en la base de datos y pueden ser ejecutados bajo demanda. Son útiles para encapsular lógica compleja, mejorar el rendimiento y reutilizar código.

Creación de Procedimientos Almacenados

Sintaxis básica:

CREATE PROCEDURE nombre_del_procedimiento (parámetros)
BEGIN
    -- Instrucciones SQL
END;

Ejemplo de procedimiento almacenado para calcular el salario promedio:

CREATE PROCEDURE CalcularSalarioPromedio()
BEGIN
    SELECT AVG(salario) AS SalarioPromedio FROM empleados;
END;

Este procedimiento calcula el salario promedio de todos los empleados.

Procedimientos con Parámetros

Los procedimientos almacenados pueden aceptar parámetros de entrada y salida, lo que los hace muy flexibles.

Ejemplo con parámetros de entrada:

CREATE PROCEDURE EmpleadosPorDepartamento(IN depto VARCHAR(50))
BEGIN
    SELECT nombre, salario
    FROM empleados
    WHERE departamento = depto;
END;

Este procedimiento selecciona los empleados de un departamento específico pasado como parámetro.

Procedimientos con Lógica Condicional

Los procedimientos almacenados pueden contener lógica condicional para realizar diferentes acciones basadas en las condiciones.

Ejemplo con lógica condicional:

CREATE PROCEDURE ActualizarSalario(IN empleado_id INT, IN incremento DECIMAL(10,2))
BEGIN
    DECLARE salario_actual DECIMAL(10,2);

    SELECT salario INTO salario_actual FROM empleados WHERE id = empleado_id;

    IF salario_actual IS NOT NULL THEN
        UPDATE empleados SET salario = salario + incremento WHERE id = empleado_id;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Empleado no encontrado';
    END IF;
END;

Este procedimiento actualiza el salario de un empleado específico, incrementándolo por una cantidad pasada como parámetro. Si el empleado no es encontrado, lanza un error.

Uso de Procedimientos Almacenados con SELECT

Los procedimientos almacenados pueden ser invocados dentro de otras consultas SELECT para proporcionar resultados dinámicos.

Ejemplo de invocación de procedimiento:

CALL EmpleadosPorDepartamento('Ventas');

Este comando llama al procedimiento EmpleadosPorDepartamento para listar los empleados del departamento de ventas.

Combinación de SELECT Avanzados y Procedimientos

Combinar consultas SELECT avanzadas con procedimientos almacenados permite crear soluciones robustas y escalables para la gestión de datos.

Ejemplo combinado:

CREATE PROCEDURE ProyectosPorEmpleadoYSalario(IN empleado_id INT)
BEGIN
    SELECT p.nombre AS proyecto, p.fecha_inicio, p.fecha_fin, e.nombre AS empleado, e.salario
    FROM proyectos p
    JOIN empleados e ON p.empleado_id = e.id
    WHERE e.id = empleado_id;
END;

CALL ProyectosPorEmpleadoYSalario(1);

Este ejemplo crea un procedimiento que lista los proyectos de un empleado específico junto con su salario, y luego llama a ese procedimiento para el empleado con ID 1.

Conclusión

Las consultas SELECT avanzadas y los procedimientos almacenados en MySQL son herramientas esenciales para manejar y analizar datos complejos de manera eficiente. Al dominar estas técnicas, puedes crear soluciones de bases de datos robustas y escalables que simplifican el manejo de grandes volúmenes de datos y automatizan tareas repetitivas.