Sintaxis Fundamental de MySQL

Creación de Objetos

-- Crear una base de datos con codificación específica
CREATE DATABASE IF NOT EXISTS app_store CHARACTER SET utf8mb4;

-- Estructura de tabla con restricciones y claves
CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10, 2) DEFAULT 0.00,
    categoria_id INT,
    CONSTRAINT fk_categoria FOREIGN KEY (categoria_id) 
    REFERENCES categorias(id) ON DELETE SET NULL
);

-- Creación de índices para optimizar búsquedas
CREATE INDEX idx_nombre_prod ON productos(nombre);

-- Vistas para consultas recurrentes
CREATE VIEW vista_resumen_ventas AS 
SELECT p.nombre, COUNT(v.id) AS total_vendidos
FROM productos p
JOIN ventas v ON p.id = v.producto_id
GROUP BY p.nombre;

Gestión de Usuarios y Permisos

-- Crear un nuevo usuario administrativo
CREATE USER 'admin_dev'@'%' IDENTIFIED BY 'Seguridad_2024*';

-- Asignar privilegios sobre una base de datos específica
GRANT ALL PRIVILEGES ON app_store.* TO 'admin_dev'@'%';

-- Aplicar los cambios de inmediato
FLUSH PRIVILEGES;

-- Para versiones de MySQL 8.0+, la inserción manual en tablas de sistema no es recomendada; 
-- se utiliza la sintaxis CREATE USER y ALTER USER.

Modificación de Estructuras (ALTER)

-- Renombrar una tabla
ALTER TABLE productos RENAME TO inventario_items;

-- Agregar y modificar columnas
ALTER TABLE inventario_items ADD stock_minimo INT DEFAULT 5;
ALTER TABLE inventario_items MODIFY nombre VARCHAR(150);

-- Gestión de claves
ALTER TABLE inventario_items DROP FOREIGN KEY fk_categoria;
ALTER TABLE inventario_items ADD CONSTRAINT fk_categoria_nueva 
FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON UPDATE CASCADE;

Manipulación de Datos (DML)

-- Inserción masiva
INSERT INTO categorias (nombre) VALUES ('Electrónica'), ('Hogar'), ('Libros');

-- Inserción basada en una consulta
INSERT INTO historico_precios (id_prod, precio_antiguo)
SELECT id, precio FROM productos WHERE descuento > 0;

-- Actualización condicional
UPDATE productos SET precio = precio * 1.10 WHERE stock < 10;

-- Eliminación física y limpieza
DELETE FROM logs_sistema WHERE fecha_creacion < '2023-01-01';
TRUNCATE TABLE tabla_temporal; -- Reinicia contadores de autoincremento

Consultas Avanzadas y Agregación

-- Uso de alias y funciones de agregación
SELECT 
    c.nombre AS categoria,
    COUNT(p.id) AS total_productos,
    ROUND(AVG(p.price), 2) AS precio_promedio
FROM categorias c
LEFT JOIN productos p ON c.id = p.categoria_id
GROUP BY c.nombre
HAVING total_productos > 0
ORDER BY precio_promedio DESC;

-- Búsquedas con expresiones regulares y patrones
SELECT * FROM empleados WHERE nombre RLIKE '^[A-M]';
SELECT * FROM productos WHERE descripcion LIKE '%premium%';

-- Concatenación de resultados en una sola fila
SELECT categoria_id, GROUP_CONCAT(nombre SEPARATOR ' | ') 
FROM productos 
GROUP BY categoria_id;

Instalación y Configuración del Entorno

Servidor en Sistemas Linux (Ubuntu/Debian)

# Instalación del servidor
sudo apt update
sudo apt install mysql-server

# Gestión del servicio
sudo systemctl start mysql
sudo systemctl enable mysql
sudo systemctl status mysql

# Asegurar la instalación inicial
sudo mysql_secure_installation

Configuración de Parámetros Críticos

El archivo principal de configuración suele ubicarse en /etc/mysql/mysql.conf.d/mysqld.cnf. Algunos parámetros clave incluyen:

• bind-address: Define la interfaz de red para conexiones externas (usar 0.0.0.0 para todas).
• max_connections: Límite máximo de clientes simultáneos.
• slow_query_log: Habilitar para identificar consultas que afectan el rendimiento.

Integración con Python (PyMySQL)

A continuación, se presenta una implementación para interactuar con la base de datos uitlizando programación orientada a objetos.

import pymysql

class GestorInventario:
    def __init__(self, host='localhost', user='root', password='', db='tienda'):
        try:
            self.conexion = pymysql.connect(
                host=host,
                user=user,
                password=password,
                database=db,
                cursorclass=pymysql.cursors.DictCursor
            )
            self.cursor = self.conexion.cursor()
        except pymysql.MySQLError as e:
            print(f"Error de conexión: {e}")

    def listar_productos(self):
        query = "SELECT id, nombre, precio FROM productos"
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def insertar_producto(self, nombre, precio, cat_id):
        try:
            query = "INSERT INTO productos (nombre, precio, categoria_id) VALUES (%s, %s, %s)"
            self.cursor.execute(query, (nombre, precio, cat_id))
            self.conexion.commit()
            return True
        except Exception as e:
            self.conexion.rollback()
            print(f"Error al insertar: {e}")
            return False

    def cerrar(self):
        self.cursor.close()
        self.conexion.close()

# Ejemplo de ejecución
if __name__ == "__main__":
    app = GestorInventario(db='mi_negocio')
    items = app.listar_productos()
    for item in items:
        print(f"ID: {item['id']} - Producto: {item['nombre']}")
    app.cerrar()

Transacciones y Propiedades ACID

Las transacciones garantizan la integridad de los datos mediante cuatro propiedades fundamentales:

• Atomicidad: El conjunto de operaciones se ejecuta como una unidad indivisible.
• Consistencia: La base de datos pasa de un estado válido a otro estado válido.
• Aislamiento: Las operaciones concurrentes no interfieren entre sí.
• Durabilidad: Una vez confirmada, la información persiste tras fallos del sistema.

-- Ejemplo de flujo transaccional
START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 500 WHERE usuario_id = 1;
UPDATE cuentas SET saldo = saldo + 500 WHERE usuario_id = 2;

-- Si ocurre un error:
ROLLBACK;

-- Si todo es correcto:
COMMIT;

Configuración de Replicación Maestro-Esclavo

La replicación permite copiar datos de un servidor (Maestro) a uno o más servidores (Esclavos) de forma asíncrona, útil para copias de seguridad y balanceo de carga de lectura.

Pasos en el Servidor Maestro:

1. Habilitar el log binario en el archivo de configuración: ``` server-id = 1 log_bin = /var/log/mysql/mysql-bin.log

2. Crear un usuario específico para la replicación: ``` GRANT REPLICATION SLAVE ON . TO 'repl_user'@'%' IDENTIFIED BY 'password_slave';

3. Obtener el estado del log: ``` SHOW MASTER STATUS; -- Anotar File y Position

Pasos en el Servidor Esclavo:

1. Configurar un ID de servidor único: ``` server-id = 2

2. Configurar la conexión al maestro: ``` CHANGE MASTER TO MASTER_HOST='IP_MAESTRO', MASTER_USER='repl_user', MASTER_PASSWORD='password_slave', MASTER_LOG_FILE='mysql-bin.00000X', MASTER_LOG_POS=XXX;

3. Iniciar el proceso: ``` START SLAVE; SHOW SLAVE STATUS \G; -- Verificar Slave_IO_Running: Yes