15 KiB

Raw Permalink Blame History

Bloque 3 · Tema 3

Lenguajes de interrogación de bases de datos. Estándar ANSI SQL. Procedimientos almacenados. Eventos y disparadores.

1. Esquema introductorio (visión rápida)

SQL (Structured Query Language) → Lenguaje estándar para interactuar con bases de datos relacionales.

Sublenguajes de SQL

DDL – Definición (CREATE, ALTER, DROP)
DML – Manipulación (SELECT, INSERT, UPDATE, DELETE)
DCL – Control (GRANT, REVOKE)
TCL – Transacciones (COMMIT, ROLLBACK, SAVEPOINT)

Objetos avanzados

Vistas (VIEW)
Índices (INDEX)
Procedimientos almacenados
Funciones de usuario
Disparadores (TRIGGER)
Eventos

2. Estándar ANSI SQL

2.1 Historia y versiones

Versión	Año	Novedades principales
SQL-86	1986	Primera normalización ANSI/ISO
SQL-89	1989	Mejoras de integridad referencial
SQL-92	1992	Estándar ampliamente adoptado (joins, subconsultas)
SQL:1999	1999	POO, recursividad, tipos complejos
SQL:2003	2003	XML, MERGE, funciones ventana
SQL:2011	2011	Datos temporales
SQL:2016	2016	JSON

2.2 DDL – Lenguaje de Definición de Datos

Permite crear y modificar la estructura de la base de datos.

CREATE TABLE

CREATE TABLE empleados (
    id          INT          PRIMARY KEY AUTO_INCREMENT,
    nombre      VARCHAR(100) NOT NULL,
    dni         CHAR(9)      UNIQUE NOT NULL,
    departamento_id INT,
    salario     DECIMAL(10,2) DEFAULT 0.00,
    fecha_alta  DATE,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Restricciones (CONSTRAINTS)

Restricción	Descripción
`PRIMARY KEY`	Identifica únicamente cada fila; implica NOT NULL + UNIQUE
`FOREIGN KEY`	Referencia a la clave primaria de otra tabla (integridad referencial)
`UNIQUE`	Los valores del campo son únicos
`NOT NULL`	El campo no puede ser nulo
`DEFAULT`	Valor por defecto si no se especifica
`CHECK`	Condición que deben cumplir los valores

ALTER TABLE

-- Añadir columna
ALTER TABLE empleados ADD COLUMN email VARCHAR(200);

-- Modificar tipo
ALTER TABLE empleados MODIFY COLUMN salario DECIMAL(12,2);

-- Eliminar columna
ALTER TABLE empleados DROP COLUMN email;

-- Añadir restricción
ALTER TABLE empleados ADD CONSTRAINT chk_salario CHECK (salario >= 0);

DROP

DROP TABLE empleados;           -- Elimina tabla y datos
DROP TABLE IF EXISTS empleados; -- Evita error si no existe
TRUNCATE TABLE empleados;       -- Vacía la tabla (más rápido que DELETE)

2.3 DML – Lenguaje de Manipulación de Datos

SELECT – Consulta de datos

-- Estructura completa (orden obligatorio)
SELECT   columnas
FROM     tabla
JOIN     otra_tabla ON condicion
WHERE    filtros
GROUP BY agrupacion
HAVING   filtro_sobre_grupos
ORDER BY columna [ASC|DESC]
LIMIT    n OFFSET m;

Ejemplo completo:

SELECT d.nombre AS departamento,
       COUNT(e.id) AS num_empleados,
       AVG(e.salario) AS salario_medio
FROM empleados e
JOIN departamentos d ON e.departamento_id = d.id
WHERE e.fecha_alta >= '2020-01-01'
GROUP BY d.nombre
HAVING AVG(e.salario) > 2000
ORDER BY salario_medio DESC
LIMIT 5;

Tipos de JOIN

JOIN	Resultado
`INNER JOIN`	Solo filas que coinciden en ambas tablas
`LEFT JOIN`	Todas las de la izquierda + coincidencias de la derecha
`RIGHT JOIN`	Todas las de la derecha + coincidencias de la izquierda
`FULL OUTER JOIN`	Todas las filas de ambas tablas
`CROSS JOIN`	Producto cartesiano (todas las combinaciones posibles)
`SELF JOIN`	La tabla se une consigo misma

Funciones de agregación

Función	Descripción
`COUNT(*)`	Número de filas
`COUNT(col)`	Número de valores no nulos
`SUM(col)`	Suma
`AVG(col)`	Media
`MAX(col)`	Valor máximo
`MIN(col)`	Valor mínimo

Subconsultas (subqueries)

-- En WHERE
SELECT nombre FROM empleados
WHERE salario > (SELECT AVG(salario) FROM empleados);

-- Con IN
SELECT nombre FROM empleados
WHERE departamento_id IN (SELECT id FROM departamentos WHERE nombre = 'IT');

-- Subconsulta correlacionada
SELECT nombre, salario FROM empleados e
WHERE salario = (
    SELECT MAX(salario) FROM empleados
    WHERE departamento_id = e.departamento_id
);

INSERT

-- Una fila
INSERT INTO empleados (nombre, dni, salario) VALUES ('Ana López', '12345678A', 2500);

-- Varias filas
INSERT INTO empleados (nombre, salario) VALUES
    ('Luis Pérez', 2200),
    ('Marta Ruiz', 2800);

-- Desde otra tabla
INSERT INTO empleados_backup SELECT * FROM empleados WHERE fecha_alta < '2020-01-01';

UPDATE

UPDATE empleados
SET salario = salario * 1.05,   -- subida del 5%
    fecha_alta = CURDATE()
WHERE departamento_id = 3;

DELETE

DELETE FROM empleados WHERE id = 42;

-- Con condición compuesta
DELETE FROM empleados
WHERE salario < 1000 AND departamento_id IS NULL;

2.4 DCL – Lenguaje de Control de Datos

-- Conceder permisos
GRANT SELECT, INSERT ON empleados TO usuario1;
GRANT ALL PRIVILEGES ON base_datos.* TO admin@'localhost';

-- Revocar permisos
REVOKE INSERT ON empleados FROM usuario1;

2.5 TCL – Control de Transacciones

Una transacción es un conjunto de operaciones que se tratan como una unidad atómica (todas o ninguna).

START TRANSACTION;

UPDATE cuentas SET saldo = saldo - 1000 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 1000 WHERE id = 2;

COMMIT;    -- confirma los cambios
-- o
ROLLBACK;  -- deshace todos los cambios desde START TRANSACTION

-- Punto de guardado
SAVEPOINT punto1;
ROLLBACK TO SAVEPOINT punto1;

Propiedades ACID

Propiedad	Descripción
Atomicidad	Todo o nada
Consistencia	La BD pasa de un estado válido a otro válido
Aislamiento	Las transacciones concurrentes no interfieren entre sí
Durabilidad	Los cambios confirmados persisten aunque haya fallo

2.6 Vistas (VIEW)

Una vista es una consulta almacenada que se comporta como una tabla virtual.

-- Crear vista
CREATE VIEW empleados_activos AS
SELECT id, nombre, salario FROM empleados
WHERE activo = 1;

-- Usar la vista como si fuera una tabla
SELECT * FROM empleados_activos WHERE salario > 2000;

-- Eliminar vista
DROP VIEW empleados_activos;

Ventajas: seguridad (ocultan columnas/filas sensibles), simplicidad, reutilización.

2.7 Índices (INDEX)

Los índices aceleran las consultas a costa de mayor espacio en disco y más lentitud en escrituras.

-- Índice normal
CREATE INDEX idx_nombre ON empleados(nombre);

-- Índice único
CREATE UNIQUE INDEX idx_dni ON empleados(dni);

-- Índice compuesto
CREATE INDEX idx_dept_sal ON empleados(departamento_id, salario);

-- Eliminar índice
DROP INDEX idx_nombre ON empleados;

Tipos de índices: B-Tree (más común), Hash, Full-Text, Espacial.

Cuándo usarlos: columnas muy consultadas en WHERE, JOIN o ORDER BY. Cuándo evitarlos: tablas pequeñas, columnas con poca selectividad (ej. booleanos).

3. Procedimientos almacenados

3.1 Concepto

Un procedimiento almacenado (stored procedure) es un bloque de código SQL (y lógica de control) que se guarda en la propia base de datos y se puede invocar por nombre.

Ventajas:

Reutilización de código.
Rendimiento (se compilan y optimizan una vez).
Seguridad (solo se expone el nombre del procedimiento, no las tablas).
Reducción del tráfico de red (se ejecuta en el servidor).
Centralización de la lógica de negocio.

Inconvenientes:

Difícil depuración.
Dependencia del SGBD (menor portabilidad).
Más complejidad en el mantenimiento.

3.2 Sintaxis (MySQL / MariaDB)

DELIMITER $$

CREATE PROCEDURE actualizar_salario(
    IN  p_departamento  INT,
    IN  p_porcentaje    DECIMAL(5,2),
    OUT p_afectados     INT
)
BEGIN
    -- Variables locales
    DECLARE v_count INT DEFAULT 0;

    -- Lógica
    UPDATE empleados
    SET salario = salario * (1 + p_porcentaje / 100)
    WHERE departamento_id = p_departamento;

    SET v_count = ROW_COUNT();
    SET p_afectados = v_count;
END$$

DELIMITER ;

-- Llamada
CALL actualizar_salario(3, 5.0, @afectados);
SELECT @afectados;

3.3 Tipos de parámetros

Tipo	Dirección	Descripción
`IN`	Entrada	El llamador pasa un valor; no se modifica en el exterior
`OUT`	Salida	El procedimiento devuelve un valor
`INOUT`	Bidireccional	Entrada y salida

3.4 Estructuras de control en procedimientos

-- Condicional
IF condicion THEN
    instrucciones;
ELSEIF otra_condicion THEN
    instrucciones;
ELSE
    instrucciones;
END IF;

-- CASE
CASE variable
    WHEN valor1 THEN instrucciones;
    WHEN valor2 THEN instrucciones;
    ELSE instrucciones;
END CASE;

-- Bucle WHILE
WHILE condicion DO
    instrucciones;
END WHILE;

-- Bucle LOOP con LEAVE
mi_bucle: LOOP
    IF condicion_salida THEN LEAVE mi_bucle; END IF;
    instrucciones;
END LOOP;

-- Cursor (recorrer filas)
DECLARE cur CURSOR FOR SELECT id, nombre FROM empleados;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
FETCH cur INTO v_id, v_nombre;
CLOSE cur;

3.5 Funciones de usuario (UDF)

Similares a los procedimientos pero devuelven un valor y pueden usarse dentro de consultas SQL.

CREATE FUNCTION calcular_irpf(salario DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE irpf DECIMAL(10,2);
    IF salario < 12450 THEN SET irpf = salario * 0.19;
    ELSEIF salario < 20200 THEN SET irpf = salario * 0.24;
    ELSE SET irpf = salario * 0.30;
    END IF;
    RETURN irpf;
END;

-- Uso dentro de SELECT
SELECT nombre, salario, calcular_irpf(salario) AS irpf FROM empleados;

	Procedimiento	Función
Devuelve valor	No (usa OUT)	Sí (RETURNS)
Uso dentro de SELECT	No	Sí
Transacciones (COMMIT/ROLLBACK)	Sí	No (generalmente)

4. Disparadores (TRIGGERS)

4.1 Concepto

Un disparador (trigger) es un bloque de código que se ejecuta automáticamente cuando ocurre un evento concreto (INSERT, UPDATE, DELETE) sobre una tabla.

No se llama directamente: se activa de forma implícita cuando se produce el evento.

4.2 Sintaxis

CREATE TRIGGER nombre_trigger
    { BEFORE | AFTER }
    { INSERT | UPDATE | DELETE }
    ON nombre_tabla
    FOR EACH ROW
BEGIN
    -- cuerpo del trigger
    -- acceso a filas con NEW y OLD
END;

4.3 NEW y OLD

	INSERT	UPDATE	DELETE
`NEW`	Nueva fila	Fila después de actualizar	No disponible
`OLD`	No disponible	Fila antes de actualizar	Fila eliminada

4.4 BEFORE vs AFTER

	BEFORE	AFTER
Se ejecuta	Antes de la operación	Después de la operación
Puede cancelar la operación	Sí (usando SIGNAL)	No
Uso típico	Validaciones, modificar valores antes de insertar	Auditoría, actualizar tablas relacionadas

4.5 Ejemplos

-- AFTER INSERT: guardar en tabla de auditoría
CREATE TRIGGER trg_auditoria_empleados
AFTER INSERT ON empleados
FOR EACH ROW
BEGIN
    INSERT INTO auditoria (accion, tabla, id_registro, fecha)
    VALUES ('INSERT', 'empleados', NEW.id, NOW());
END;

-- BEFORE UPDATE: no permitir reducir el salario
CREATE TRIGGER trg_controlar_salario
BEFORE UPDATE ON empleados
FOR EACH ROW
BEGIN
    IF NEW.salario < OLD.salario THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'No se puede reducir el salario';
    END IF;
END;

-- AFTER DELETE: actualizar contador en departamento
CREATE TRIGGER trg_actualizar_contador
AFTER DELETE ON empleados
FOR EACH ROW
BEGIN
    UPDATE departamentos
    SET num_empleados = num_empleados - 1
    WHERE id = OLD.departamento_id;
END;

4.6 Gestión de triggers

-- Ver triggers
SHOW TRIGGERS FROM nombre_base_datos;

-- Eliminar trigger
DROP TRIGGER IF EXISTS trg_auditoria_empleados;

4.7 Usos habituales de los triggers

Auditoría: registrar automáticamente quién cambió qué y cuándo.
Integridad: validaciones complejas que no pueden expresarse con CHECK.
Sincronización: mantener datos derivados actualizados (contadores, totales).
Soft delete: marcar como borrado en lugar de eliminar físicamente.
Historial: guardar versiones anteriores de un registro.

5. Eventos (EVENT)

5.1 Concepto

Un evento en MySQL/MariaDB es una tarea programada que se ejecuta automáticamente en un momento concreto o de forma periódica. Es como un cron job dentro del propio SGBD.

-- Habilitar el planificador de eventos
SET GLOBAL event_scheduler = ON;

5.2 Sintaxis

CREATE EVENT nombre_evento
ON SCHEDULE
    { AT timestamp | EVERY intervalo [STARTS inicio] [ENDS fin] }
[ON COMPLETION { NOT } PRESERVE]
[ENABLE | DISABLE]
DO
    sentencia_sql;

5.3 Ejemplos

-- Ejecutar una vez en el futuro
CREATE EVENT limpiar_logs_antiguos
ON SCHEDULE AT '2026-12-31 23:59:00'
DO DELETE FROM logs WHERE fecha < '2026-01-01';

-- Ejecutar periódicamente
CREATE EVENT backup_diario
ON SCHEDULE EVERY 1 DAY
STARTS '2026-01-01 02:00:00'
ON COMPLETION PRESERVE
DO CALL hacer_backup_diario();

-- Cada hora
CREATE EVENT purgar_sesiones
ON SCHEDULE EVERY 1 HOUR
DO DELETE FROM sesiones WHERE expira < NOW();

5.4 Gestión de eventos

SHOW EVENTS;                     -- Ver eventos
ALTER EVENT backup_diario DISABLE; -- Desactivar
DROP EVENT IF EXISTS backup_diario; -- Eliminar

6. Resumen: conceptos clave para el examen

Concepto	Dato clave
DDL	CREATE, ALTER, DROP – define la estructura
DML	SELECT, INSERT, UPDATE, DELETE – manipula datos
DCL	GRANT, REVOKE – controla permisos
TCL	COMMIT, ROLLBACK, SAVEPOINT – gestiona transacciones
ACID	Atomicidad, Consistencia, Aislamiento, Durabilidad
INNER JOIN	Solo filas que coinciden en ambas tablas
LEFT JOIN	Todas las de la izquierda + coincidencias
GROUP BY + HAVING	Agrupa filas; HAVING filtra sobre grupos (≠ WHERE)
Vista	Consulta guardada como tabla virtual
Índice	Acelera consultas; B-Tree es el más común
Procedimiento almacenado	Bloque SQL guardado en BD, invocado con CALL
Función de usuario	Como procedimiento pero devuelve valor; usable en SELECT
Parámetros IN/OUT/INOUT	Entrada / Salida / Bidireccional
Trigger BEFORE	Se ejecuta antes; puede cancelar la operación con SIGNAL
Trigger AFTER	Se ejecuta después; útil para auditoría
NEW / OLD en trigger	Fila nueva (INSERT/UPDATE) / fila antigua (UPDATE/DELETE)
Evento (EVENT)	Tarea programada dentro del SGBD (cron job de SQL)

15 KiB Raw Permalink Blame History Unescape Escape