Consultas DQL en Tablas Únicas

El manejo de datos en una sola tabla es la base de cualquier interacción con MySQL. A continuación, exploraremos cómo organizar, agrupar y filtrar información de manera eficiente.

Configuración Inicial

Para comenzar, crearemos un entorno de pruebas con una base de datos nueva y una estructura de tabla básica.

-- Creación de la base de datos con codificación UTF-8
CREATE DATABASE gestion_empleados CHARACTER SET utf8;

-- Clonar la estructura de una tabla existente (ejemplo)
CREATE TABLE personal_it LIKE base_datos_anterior.empleados;

Ordenamiento de Resultados

La cláusula ORDER BY permite organizar visualmente los registros sin alterar su almacenamiento físico.

-- Estructura base
SELECT columnas FROM tabla WHERE condicion ORDER BY columna [ASC | DESC];

Ejemplos de ordenamiento:

-- Ordenamiento simple por salario de forma descendente
SELECT * FROM personal_it ORDER BY remuneracion DESC;

-- Ordenamiento compuesto: por departamento (asc) y luego por salario (desc)
SELECT * FROM personal_it ORDER BY area_trabajo ASC, remuneracion DESC;

Uso de Funciones de Agregado

Estas funciones reailzan cálculos sobre una columna específica y devuelven un único valor resultante, ignorando generalmente los valores NULL.

-- Contar el total de colaboradores
SELECT COUNT(*) AS total_empleados FROM personal_it;

-- Obtener métricas financieras de la nómina
SELECT 
    SUM(remuneracion) AS gasto_total,
    MAX(remuneracion) AS sueldo_maximo,
    MIN(remuneracion) AS sueldo_minimo,
    AVG(remuneracion) AS promedio_salarial
FROM personal_it;

-- Filtrado con agregado: Empleados en el área de 'Desarrollo'
SELECT COUNT(*) FROM personal_it WHERE area_trabajo = 'Desarrollo';

Agrupación de Datos

La instrucción GROUP BY segmenta los resultados en grupos basados en valores idénticos en las columnas especificadas.

-- Calcular el promedio salarial por cada departamento
SELECT area_trabajo, AVG(remuneracion) 
FROM personal_it 
GROUP BY area_trabajo;

-- Uso de HAVING para filtrar grupos (promedios mayores a 5000)
SELECT area_trabajo, AVG(remuneracion) as promedio
FROM personal_it 
GROUP BY area_trabajo 
HAVING promedio > 5000;

Nota técnica: WHERE filtra filas antes de la agrupación, mientras que HAVING filtra los grupos resultantes tras procesar las funciones de agregado.

Paginación con LIMIT

Para manejar grandes volúmenes de datos en aplicaciones web, se utiliza LIMIT para fragmentar los resultados.

-- Sintaxis: LIMIT inicio, cantidad
-- Obtener los primeros 10 registros
SELECT * FROM personal_it LIMIT 0, 10;

-- Obtener la segunda página (registros del 11 al 20)
SELECT * FROM personal_it LIMIT 10, 10;

-- Fórmula para paginación: Inicio = (Nº Página - 1) * Cantidad por página

Restricciones de Integridad (Constraints)

Las restricciones aseguran que los datos ingresados sean válidos y consistentes.

Clave Primaria (PRIMARY KEY)

Identifica de forma única cada registro. No admite duplicados ni valores nulos.

-- Definición al crear la tabla con incremento automático
CREATE TABLE usuarios (
    id_usuario INT PRIMARY KEY AUTO_INCREMENT,
    nombre_usuario VARCHAR(50) NOT NULL
);

-- Modificar tabla existente para añadir clave primaria
ALTER TABLE usuarios ADD PRIMARY KEY (id_usuario);

Comportamiento de Autoincremento

• DELETE: Si se borran registros, el contador de autoincermento continúa desde el último valor generado.
• TRUNCATE: Borra todos los datos y reinicia el contador a 1 (o al valor inicial definido).

Otras Restricciones Comunes

• NOT NULL: Obliga a que la columna siempre tenga un valor.
• UNIQUE: Garantiza que todos los valores en la columna sean distintos (permite NULL).
• DEFAULT: Asigna un valor predeterminado si no se especifica uno.

CREATE TABLE inventario (
    id_producto INT PRIMARY KEY AUTO_INCREMENT,
    codigo_sku VARCHAR(20) UNIQUE,
    stock INT NOT NULL DEFAULT 0,
    estado VARCHAR(10) DEFAULT 'activo'
);

Trensacciones en la Base de Datos

Una transacción es una unidad lógica de trabajo que debe ejecutarse de forma completa o no ejecutarse en absoluto.

Operaciones Manuales

Por defecto, MySQL utiliza autocommit. Para operaciones críticas como transferencias bancarias, se requiere control manual.

-- 1. Iniciar la transacción
START TRANSACTION;

-- 2. Ejecutar operaciones
UPDATE cuentas SET saldo = saldo - 200 WHERE titular = 'Alice';
UPDATE cuentas SET saldo = saldo + 200 WHERE titular = 'Bob';

-- 3. Confirmar o Deshacer
-- Si todo es correcto:
COMMIT;
-- Si hubo un error:
ROLLBACK;

Niveles de Aislamiento y Problemas de Concurrencia

Cuando múltiples usuarios acceden a los mismos datos simultáneamente, pueden ocurrir anomalías:

1. Lectura Sucia (Dirty Read): Leer datos que otra transacción aún no ha confirmado.
2. Lectura No Repetible: Los datos cambian entre dos lecturas de una misma transacción debido a un UPDATE externo.
3. Lectura Fantasma (Phantom Read): Aparecen nuevos registros entre lecturas debido a un INSERT externo.

Configuración de niveles de aislamiento:

-- Consultar nivel actual
SELECT @@tx_isolation;

-- Cambiar nivel a REPEATABLE READ (Evita lecturas sucias y no repetibles)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Los niveles disponibles son: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (predeterminado en InnoDB) y SERIALIZABLE (el más estricto, pero más lento).