Consultas SELECT, DELETE y UPDATE Complejas en MySQL: Segunda Parte

En la primera parte del artículo, exploramos cómo utilizar las sentencias SELECT, DELETE y UPDATE de manera avanzada utilizando una base de datos de ejemplo llamada Comisaria. En esta segunda parte, continuaremos con más ejemplos complejos para estas operaciones, demostrando técnicas avanzadas para manejar datos en MySQL.

Más Ejemplos de SELECT Complejos

Ejemplo 3: Uso de Funciones de Agregación y Agrupación

Vamos a obtener el número de casos gestionados por cada agente, junto con el salario total que reciben.

SELECT 
    a.nombre, 
    COUNT(c.id_caso) AS numero_casos, 
    SUM(a.salario) AS salario_total
FROM 
    Agentes a
JOIN 
    Casos c ON a.id_agente = c.id_agente
GROUP BY 
    a.nombre;

Este ejemplo utiliza la función COUNT() para contar el número de casos gestionados por cada agente y la función SUM() para sumar sus salarios.

Ejemplo 4: Usando UNION para Combinar Resultados

Vamos a combinar dos conjuntos de resultados: uno que muestra los casos abiertos y otro que muestra los casos cerrados.

SELECT 
    'Abiertos' AS estado_caso, 
    descripcion 
FROM 
    Casos 
WHERE 
    estado = 'Abierto'
UNION
SELECT 
    'Cerrados' AS estado_caso, 
    descripcion 
FROM 
    Casos 
WHERE 
    estado = 'Cerrado';

El operador UNION combina los resultados de dos SELECT, permitiendo mostrar datos de múltiples conjuntos en una sola consulta.

Más Ejemplos de DELETE Complejos

Ejemplo 2: Eliminar Reportes Antiguos

Vamos a eliminar todos los reportes que tienen más de un año desde su fecha de reporte.

DELETE FROM Reportes
WHERE fecha_reporte < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Esta consulta utiliza la función DATE_SUB() para calcular la fecha de hace un año desde la fecha actual y elimina todos los reportes anteriores a esa fecha.

Más Ejemplos de UPDATE Complejos

Ejemplo 3: Actualizar Estado de Casos Basado en el Tiempo

Vamos a actualizar el estado de todos los casos a «Pendiente Revisión» si han estado en estado «Abierto» por más de seis meses.

UPDATE Casos
SET estado = 'Pendiente Revisión'
WHERE estado = 'Abierto' AND fecha_inicio < DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

Esta consulta utiliza la función DATE_SUB() para calcular la fecha de hace seis meses y actualiza el estado de los casos que han estado abiertos desde entonces.

Ejemplo 4: Incrementar el Salario de Agentes Basado en Años de Servicio

Vamos a incrementar el salario de los agentes en un 5% por cada año de servicio completado.

UPDATE Agentes
SET salario = salario * (1 + (TIMESTAMPDIFF(YEAR, fecha_ingreso, CURDATE()) * 0.05));

Esta consulta utiliza la función TIMESTAMPDIFF() para calcular los años de servicio de cada agente y ajusta su salario en consecuencia.

Consultas SELECT Avanzadas con Subconsultas y Joins

Ejemplo 5: Subconsulta en el FROM

Vamos a obtener el nombre del agente y el número de casos abiertos que han gestionado, utilizando una subconsulta en la cláusula FROM.

SELECT 
    a.nombre, 
    c.num_casos_abiertos
FROM 
    Agentes a
JOIN (
    SELECT 
        id_agente, 
        COUNT(*) AS num_casos_abiertos 
    FROM 
        Casos 
    WHERE 
        estado = 'Abierto' 
    GROUP BY 
        id_agente
) c ON a.id_agente = c.id_agente;

Esta consulta utiliza una subconsulta para contar el número de casos abiertos por agente y luego une este resultado con la tabla Agentes.

Consultas DELETE y UPDATE con Joins

Ejemplo 5: Eliminar Datos Usando Joins

Vamos a eliminar todos los reportes asociados a casos que han sido cerrados.

DELETE r
FROM Reportes r
JOIN Casos c ON r.id_caso = c.id_caso
WHERE c.estado = 'Cerrado';

Esta consulta utiliza un JOIN para identificar los reportes que están asociados con casos cerrados y luego los elimina.

Ejemplo 6: Actualizar Datos Usando Joins

Vamos a actualizar el salario de los agentes que han gestionado casos cerrados, incrementándolos en un 10%.

UPDATE Agentes a
JOIN Casos c ON a.id_agente = c.id_agente
SET a.salario = a.salario * 1.10
WHERE c.estado = 'Cerrado';

Esta consulta utiliza un JOIN para identificar los agentes que han gestionado casos cerrados y luego actualiza sus salarios.

Conclusión

En esta segunda parte, hemos explorado más ejemplos avanzados de las sentencias SELECT, DELETE y UPDATE en MySQL. Estas técnicas permiten realizar consultas sofisticadas y manipulaciones complejas de los datos, mejorando la eficiencia y funcionalidad de las bases de datos. Al dominar estas técnicas, puedes manejar y analizar datos de manera más efectiva en tus aplicaciones y proyectos.