Fundamentos de Administración y Programación en Bases de Datos SQL

Administración y Programación Esencial en Bases de Datos SQL

Gestión de Usuarios

Para crear un nuevo usuario en el sistema, se utiliza el siguiente comando:

CREATE USER 'nombre_usuario'@'localhost' IDENTIFIED BY 'contraseña';

Manejo de Transacciones

Las transacciones aseguran la integridad de los datos al agrupar múltiples operaciones (como actualizaciones e inserciones) en una sola unidad lógica. Si alguna parte falla, todas las operaciones se revierten (rollback).

Ejemplo 1: Retiro Simple

Una secuencia básica de retiro:

START TRANSACTION;
UPDATE cuenta SET saldo = saldo - 100 WHERE id = 1;
INSERT INTO registro_transacciones (cuenta_id, monto, tipo) VALUES (1, 100, 'retiro');
COMMIT;

Ejemplo 2: Transferencia entre Cuentas

Una transferencia requiere modificar al menos dos cuentas:

START TRANSACTION;
-- Actualizar saldo de cuenta origen
UPDATE cuenta SET saldo = saldo - 100 WHERE id = 1;
-- Registrar la transacción
INSERT INTO registro_transacciones (cuenta_id, monto, tipo) VALUES (1, -100, 'transferencia_saliente');
-- Actualizar saldo de cuenta destino (Nota: El código original estaba incompleto aquí)
UPDATE cuenta SET saldo = saldo + 100 WHERE id = [ID_DESTINO];
COMMIT;

Ejemplo 3: Actualización de Pedido (Usando BEGIN TRANSACTION)

Aunque MySQL prefiere START TRANSACTION, BEGIN TRANSACTION es sintácticamente válido en muchos dialectos SQL:

BEGIN TRANSACTION;
-- Actualizar estado del pedido a "enviado"
UPDATE pedido SET estado = 'enviado' WHERE id = 123;
-- Registrar la acción en el registro de pedidos
INSERT INTO registro_pedido (pedido_id, accion, fecha) VALUES (123, 'enviado', GETDATE());
COMMIT;

Motores de Almacenamiento

La elección del motor afecta el rendimiento, la concurrencia y las características de integridad de la tabla.

InnoDB

InnoDB es el motor de almacenamiento por defecto en MySQL. Soporta transacciones ACID y claves foráneas.

Creación de tabla usando InnoDB:

CREATE TABLE usuarios_innodb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50),
    edad INT,
    email VARCHAR(100)
) ENGINE=InnoDB;

MyISAM (ISAM)

MyISAM (anteriormente ISAM) es conocido por ser más rápido en operaciones de lectura pura, pero no soporta transacciones ni integridad referencial.

Creación de tabla usando MyISAM:

CREATE TABLE usuarios_isam (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50),
    edad INT,
    email VARCHAR(100)
) ENGINE=MyISAM;

Procedimientos Almacenados

Los procedimientos almacenados encapsulan lógica de negocio directamente en la base de datos, mejorando la seguridad y el rendimiento.

Definición de Procedimientos

Se requiere cambiar el delimitador para definir procedimientos que contienen punto y coma internos:

DELIMITER //
CREATE PROCEDURE obtener_usuario_por_id (IN usuario_id INT)
BEGIN
    SELECT * FROM usuarios WHERE id = usuario_id;
END //
DELIMITER ;

Ejemplos de Procedimientos

• Consulta de Ventas Mensuales:

  CREATE PROCEDURE obtener_ventas_por_mes ()
  BEGIN
      SELECT MONTH(fecha_venta) AS mes, SUM(total) AS total_ventas
      FROM ventas
      GROUP BY MONTH(fecha_venta);
  END;

• Eliminación con Transacción:

  CREATE PROCEDURE eliminar_usuario_con_pedidos (IN usuario_id INT)
  BEGIN
      START TRANSACTION;
      DELETE FROM pedidos WHERE usuario_id = usuario_id;
      DELETE FROM usuarios WHERE id = usuario_id;
      COMMIT;
  END;

• Cálculo de Promedio:

  CREATE PROCEDURE calcular_promedio_edad ()
  BEGIN
      SELECT AVG(edad) AS promedio_edad FROM usuarios;
  END;

• Conteo de Usuarios (con delimitador):

  DELIMITER //
  CREATE PROCEDURE obtener_cantidad_usuarios ()
  BEGIN
      SELECT COUNT(*) AS cantidad_usuarios FROM usuarios;
  END //
  DELIMITER ;

Permisos y Seguridad

La gestión de permisos es crucial para controlar qué acciones pueden realizar los usuarios sobre los objetos de la base de datos.

Comandos de Permisos (GRANT)

Los permisos básicos incluyen:

• SELECT: Permite al usuario ver los datos de una tabla o vista.
• INSERT: Permite al usuario agregar nuevos registros a una tabla.
• UPDATE: Permite al usuario modificar registros existentes en una tabla.

Asignación de permisos a un rol:

CREATE ROLE rol_aplicacion;
GRANT SELECT, INSERT, UPDATE ON tabla_ejemplo TO rol_aplicacion;

Funciones y Permisos Condicionales

Se pueden definir funciones para implementar lógica de autorización más compleja, aunque la sintaxis de GRANT condicional varía significativamente entre sistemas de gestión de bases de datos (SGBD).

Definición de una función (ejemplo conceptual):

CREATE FUNCTION es_administrador(usuario_id INT) RETURNS BOOLEAN AS
BEGIN
    DECLARE es_admin BOOLEAN;
    -- Lógica para determinar si el usuario es administrador
    RETURN es_admin;
END;

Intento de aplicar un permiso condicional (sintaxis específica de algunos SGBD):

GRANT SELECT ON tabla_ejemplo TO usuario;
GRANT INSERT, UPDATE, DELETE ON tabla_ejemplo TO usuario WHERE es_administrador(usuario.id) = TRUE;