Conceptos Esenciales de MySQL: Funciones, Vistas, Triggers y Gestión de Datos

Programación Almacenada en MySQL

Función para Comparar Números Enteros

Se requiere una función que acepte dos números enteros, los compare y devuelva una cadena de texto indicando cuál es mayor, menor o si son iguales.

DELIMITER $$
CREATE FUNCTION comparar_numeros(num1 INT, num2 INT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE resultado VARCHAR(50);
    IF num1 > num2 THEN
        SET resultado = CONCAT(num1, ' es mayor que ', num2);
    ELSEIF num1 < num2 THEN
        SET resultado = CONCAT(num1, ' es menor que ', num2);
    ELSE
        SET resultado = CONCAT(num1, ' es igual a ', num2);
    END IF;
    RETURN resultado;
END$$
DELIMITER ;

Procedimiento para Gestionar Personas (Niños y Adultos)

Para gestionar personas según su edad, se crearán dos tablas y un procedimiento almacenado que capture el nombre y la edad de una persona y almacene sus datos en la tabla correspondiente.

Creación de Tablas

CREATE TABLE ninios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    edad INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE adultos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    edad INT NOT NULL
) ENGINE=InnoDB;

Procedimiento Almacenado para Introducir Personas

DELIMITER $$
CREATE PROCEDURE introducir_persona(IN p_nombre VARCHAR(50), IN p_edad INT)
BEGIN
    IF p_edad < 18 THEN
        INSERT INTO ninios (nombre, edad) VALUES (p_nombre, p_edad);
    ELSE
        INSERT INTO adultos (nombre, edad) VALUES (p_nombre, p_edad);
    END IF;
END$$
DELIMITER ;

Función para Calcular Antigüedad de un Jugador

Se necesita una función almacenada que calcule la antigüedad en años de un jugador específico basándose en su fecha de alta.

DELIMITER $$
CREATE FUNCTION calcular_antiguedad_jugador(p_id_jugador INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE fecha_alta_jugador DATE;
    DECLARE dias_transcurridos INT;
    DECLARE anos_antiguedad INT;

    SELECT fecha_alta INTO fecha_alta_jugador
    FROM jugador
    WHERE id_jugador = p_id_jugador;

    SET dias_transcurridos = DATEDIFF(NOW(), fecha_alta_jugador);
    SET anos_antiguedad = FLOOR(dias_transcurridos / 365);

    RETURN anos_antiguedad;
END$$
DELIMITER ;

Vistas de Base de Datos

Vista de Información de Clientes y Cuentas

Se solicita crear una vista que combine la información esencial de los clientes (DNI, nombre y apellidos) con los detalles de sus cuentas (fecha de creación, saldo).

CREATE VIEW informacion_clientes_cuentas AS
SELECT
    c.dni,
    c.nombre,
    CONCAT(c.apellido1, ' ', c.apellido2) AS apellidos,
    cu.fecha_creacion AS fecha_de_creacion_cuenta,
    cu.saldo
FROM
    cliente c
INNER JOIN
    cuentas cu ON c.codigo_cliente = cu.cod_cliente;

Vista de Datos de Capitanes y Equipos

Se requiere una vista que muestre la información detallada de los capitanes de equipo, incluyendo sus datos personales (DNI, nombre y apellidos en una sola columna) y los de su equipo (nombre, ciudad).

USE liga;
DELIMITER $$
CREATE VIEW vista_capitanes_equipo AS
SELECT
    j.dni,
    CONCAT(j.nombre, ' ', j.apellido1, ' ', j.apellido2) AS nombre_completo_jugador,
    e.nombre AS nombre_equipo,
    e.ciudad
FROM
    jugador j
INNER JOIN
    equipo e ON j.id_jugador = e.id_capitan;
END$$
DELIMITER ;

Triggers y Variables de Usuario

Explicación de un Trigger de Actualización de Saldo

El siguiente trigger está diseñado para mantener la consistencia del saldo de una cuenta bancaria.

DELIMITER $$
CREATE TRIGGER act_cuenta
AFTER INSERT ON movimiento
FOR EACH ROW
BEGIN
    UPDATE Cuenta
    SET saldo = saldo + NEW.cantidad
    WHERE cod_cuenta = NEW.cod_cuenta;
END$$
DELIMITER ;

Explicación: Este trigger se ejecuta automáticamente después de cada inserción en la tabla movimiento. Su función es actualizar el saldo de la cuenta correspondiente, sumando la cantidad del nuevo movimiento al saldo existente.

Uso y Alcance de Variables de Usuario

A continuación, se muestra cómo crear y utilizar una variable de usuario en MySQL, junto con una explicación de su alcance.

Creación y Visualización

SET @mi_variable_usuario = 1;
SELECT @mi_variable_usuario;

Alcance de la Variable

Las variables de usuario (precedidas por @) no son variables de sesión ni globales en el sentido estricto. Son variables específicas de la conexión actual del usuario. Esto significa que:

• Su valor es visible y modificable solo dentro de la sesión en la que fueron creadas.
• Desaparecen automáticamente cuando la conexión del usuario finaliza.

Gestión de Usuarios y Privilegios

Configuración de Cuentas de Superusuario en MySQL

Para asegurar la conectividad y gestión de usuarios en MySQL, es común configurar múltiples cuentas con diferentes alcances. Consideremos el siguiente escenario:

Se han configurado dos cuentas de superusuario con el nombre de usuario "nuevo" y la contraseña "pass_nuevo". Ambas poseen ALL PRIVILEGES, otorgándoles permisos completos para realizar cualquier operación en el servidor.

• La primera cuenta está configurada para permitir la conexión exclusivamente desde el equipo local ('nuevo'@'localhost').
• La segunda cuenta permite la conexión desde cualquier otro equipo ('nuevo'@'%').

Es fundamental tener ambas cuentas. Si solo existiera la cuenta 'nuevo'@'%', al intentar conectarse desde el equipo local, la cuenta anónima para localhost (creada por mysql_install_db) tendría precedencia. Esto se debe a que la cuenta anónima tiene un valor más específico en la columna Host de la tabla user, lo que la sitúa antes en el orden de resolución de permisos. Como resultado, el usuario "nuevo" sería tratado como un usuario anónimo.

La cláusula WITH GRANT OPTION, al crear una cuenta, le otorga los mismos privilegios que el usuario que la crea, incluyendo la capacidad de conceder esos mismos permisos a otros usuarios.

Diccionario de Datos (INFORMATION_SCHEMA)

Tablas Clave del Diccionario de Datos

El diccionario de datos de MySQL, accesible a través de INFORMATION_SCHEMA, contiene metadatos cruciales sobre la estructura de la base de datos. A continuación, se listan cuatro tablas importantes y la información que almacenan:

• SCHEMATA: Proporciona información detallada sobre todas las bases de datos (esquemas) disponibles en el servidor.
• TABLES: Contiene metadatos sobre todas las tablas presentes en las bases de datos del servidor, como nombres, tipos de motor, fechas de creación, etc.
• ROUTINES: Almacena información sobre los procedimientos y funciones almacenadas definidos en las bases de datos.
• VIEWS: Ofrece detalles sobre las vistas creadas en las bases de datos, incluyendo su definición.

Definición y Acceso al Diccionario de Datos

El INFORMATION_SCHEMA es una base de datos especial en MySQL que actúa como el diccionario de datos. Su propósito principal es almacenar metadatos, es decir, información sobre todas las demás bases de datos, tablas, vistas, procedimientos, funciones y otros objetos que residen en el servidor MySQL.

Contiene una serie de vistas de solo lectura. Cada usuario tiene derecho a acceder a estas tablas, pero solo podrá visualizar los registros que corresponden a los objetos sobre los cuales posee permisos de acceso.

Consulta del Contenido del Diccionario de Datos

Para explorar el contenido del diccionario de datos INFORMATION_SCHEMA, se pueden utilizar las siguientes sentencias SQL:

Visualizar Tablas del INFORMATION_SCHEMA

SHOW TABLES FROM information_schema;

Esta consulta mostrará todas las tablas y vistas disponibles dentro de la base de datos information_schema.

Consultar Metadatos de Tablas Específicas (Ej. de la BD mysql)

Si se desea filtrar la información, por ejemplo, para ver las tablas del diccionario de datos relacionadas con la base de datos mysql (que también contiene metadatos importantes), se puede usar:

SELECT *
FROM information_schema.tables
WHERE table_schema = 'mysql';