Uso de SELECT, DELETE y UPDATE Complejos en MySQL

En este artículo, vamos a explorar cómo utilizar las sentencias SELECT, DELETE y UPDATE de manera compleja y avanzada utilizando una base de datos de ejemplo llamada Comisaria. Esta base de datos contiene tablas relacionadas con agentes, ciudadanos, casos y reportes.

Creación de la Base de Datos y Tablas

Primero, creamos la base de datos y las tablas necesarias, y luego insertamos algunos datos de ejemplo.

CREATE DATABASE Comisaria;
USE Comisaria;

-- Tabla de agentes
CREATE TABLE Agentes (
    id_agente INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    rango VARCHAR(30),
    salario DECIMAL(10, 2) NOT NULL,
    fecha_ingreso DATE
);

-- Tabla de ciudadanos
CREATE TABLE Ciudadanos (
    id_ciudadano INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    direccion VARCHAR(100),
    telefono VARCHAR(15)
);

-- Tabla de casos
CREATE TABLE Casos (
    id_caso INT AUTO_INCREMENT PRIMARY KEY,
    descripcion TEXT,
    fecha_inicio DATE,
    estado VARCHAR(20),
    id_agente INT,
    FOREIGN KEY (id_agente) REFERENCES Agentes(id_agente)
);

-- Tabla de reportes
CREATE TABLE Reportes (
    id_reporte INT AUTO_INCREMENT PRIMARY KEY,
    id_caso INT,
    id_ciudadano INT,
    descripcion TEXT,
    fecha_reporte DATE,
    FOREIGN KEY (id_caso) REFERENCES Casos(id_caso),
    FOREIGN KEY (id_ciudadano) REFERENCES Ciudadanos(id_ciudadano)
);

-- Insertar datos en la tabla Agentes
INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) VALUES
('Juan Pérez', 'Sargento', 45000.00, '2015-06-23'),
('Ana Gómez', 'Teniente', 50000.00, '2018-09-15'),
('Carlos López', 'Cabo', 40000.00, '2020-01-10');

-- Insertar datos en la tabla Ciudadanos
INSERT INTO Ciudadanos (nombre, direccion, telefono) VALUES
('Pedro Martínez', 'Calle Falsa 123', '555-1234'),
('María Fernández', 'Avenida Siempre Viva 742', '555-5678'),
('Luis García', 'Boulevard Central 456', '555-8765');

-- Insertar datos en la tabla Casos
INSERT INTO Casos (descripcion, fecha_inicio, estado, id_agente) VALUES
('Robo en tienda', '2022-03-10', 'Abierto', 1),
('Asalto a mano armada', '2022-04-22', 'Cerrado', 2),
('Vandalismo en parque', '2022-05-15', 'En Proceso', 3);

-- Insertar datos en la tabla Reportes
INSERT INTO Reportes (id_caso, id_ciudadano, descripcion, fecha_reporte) VALUES
(1, 1, 'Robo de mercancía en tienda', '2022-03-10'),
(2, 2, 'Asalto a mano armada en banco', '2022-04-22'),
(3, 3, 'Daños en parque público', '2022-05-15');

Uso de SELECT Complejos

Los SELECT complejos pueden incluir uniones (JOINs), subconsultas, funciones de agregación y más.

Ejemplo 1: Uniendo Múltiples Tablas

Vamos a obtener una lista de reportes con información del caso, del ciudadano y del agente asignado.

SELECT 
    r.id_reporte, 
    r.descripcion AS descripcion_reporte, 
    c.descripcion AS descripcion_caso, 
    ci.nombre AS nombre_ciudadano, 
    a.nombre AS nombre_agente
FROM 
    Reportes r
JOIN 
    Casos c ON r.id_caso = c.id_caso
JOIN 
    Ciudadanos ci ON r.id_ciudadano = ci.id_ciudadano
JOIN 
    Agentes a ON c.id_agente = a.id_agente;

Ejemplo 2: Usando Subconsultas

Vamos a obtener el nombre del agente que ha gestionado más casos.

SELECT nombre
FROM Agentes
WHERE id_agente = (
    SELECT id_agente
    FROM Casos
    GROUP BY id_agente
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

Uso de DELETE Complejos

Las sentencias DELETE se utilizan para eliminar datos de las tablas. Pueden ser simples o complejas, dependiendo de las condiciones que se apliquen.

Ejemplo: Eliminar Casos Cerrados

Vamos a eliminar todos los casos que están en estado «Cerrado».

DELETE FROM Casos
WHERE estado = 'Cerrado';

Uso de UPDATE Complejos

Las sentencias UPDATE se utilizan para modificar datos existentes en las tablas. Pueden incluir condiciones y subconsultas.

Ejemplo 1: Actualizar Salarios

Vamos a incrementar el salario de los agentes que han trabajado en al menos un caso abierto.

UPDATE Agentes
SET salario = salario * 1.10
WHERE id_agente IN (
    SELECT DISTINCT id_agente
    FROM Casos
    WHERE estado = 'Abierto'
);

Ejemplo 2: Marcar Casos Antiguos como Cerrados

Vamos a marcar todos los casos que tienen más de un año desde su fecha de inicio como «Cerrado».

UPDATE Casos
SET estado = 'Cerrado'
WHERE fecha_inicio < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Conclusión

Las sentencias SELECT, DELETE y UPDATE en MySQL son herramientas poderosas para gestionar y manipular datos en una base de datos. Usando estas sentencias de manera compleja, puedes realizar operaciones sofisticadas que permiten obtener información detallada y mantener la integridad y actualización de los datos. Al dominar estas técnicas, puedes mejorar significativamente la eficiencia y funcionalidad de tus consultas SQL.