Dominio de MySQL: Desde Conceptos Básicos hasta Consultas Complejas

Arquitectura de Bases de Datos Relacionales

MySQL es un sistema de gestión de bases de datos relacionales (RDBMS) ampliamente adoptado en la industria. Tras completar la instalación y configurar las variables de entorno del sistema, se puede validar la integridad de la instalación ejecutando mysql --version en la terminal.

Bases de Datos del Sistema

Al instalarse, MySQL provisiona automáticamente cuatro bases de datos críticas para su funcionamiento:

information_schema: Almacena metadatos sobre todas las demás bases de datos, tablas, columnas y privilegios de acceso.
performance_schema: Registra métricas de bajo nivel sobre el consumo de recursos y el rendimiento del motor de almacenamiento.
mysql: Contiene las tablas del sistema para la gestión de usuarios, credenciales, permisos y registros de auditoría.
sys: Proporciona una capa de abstracción sobre performance_schema, traduciendo métricas complejas en vistas fácilmente consultables.

Para la administración visual, herramientas como DBeaver, DataGrip o HeidiSQL son estándares en la industria.

Lenguaje de Consulta Estructurado (SQL)

SQL es el lenguaje estándar para interactuar con bases de datos relacionales. Las convenciones dictan el uso de mayúsculas para palabras clave, la finalización de cada sentencia con un punto y coma (;), y el uso de comillas invertidas (```) para escapar identificadores reservados.

DDL (Lenguaje de Definición de Datos)

Permite definir y modificar la estructura de la base de datos y sus tablas.

-- Listar y seleccionar bases de datos
SHOW DATABASES;
USE retail_db;
SELECT DATABASE();

-- Gestión de bases de datos
CREATE DATABASE IF NOT EXISTS retail_db;
DROP DATABASE IF EXISTS retail_db;
ALTER DATABASE retail_db CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Gestión de tablas
SHOW TABLES;
DESC inventory;

CREATE TABLE IF NOT EXISTS inventory (
    item_id INT,
    item_name VARCHAR(50),
    stock INT
);

-- Modificaciones estructurales
ALTER TABLE inventory RENAME TO stock_items;
ALTER TABLE stock_items ADD COLUMN added_at TIMESTAMP;
ALTER TABLE stock_items CHANGE added_at created_at DATETIME;
ALTER TABLE stock_items DROP COLUMN created_at;
ALTER TABLE stock_items MODIFY item_id BIGINT;

DROP TABLE IF EXISTS stock_items;

DML (Lenguaje de Manipulación de Datos)

Facilita la inserción, actualización y eliminación de registros dentro de las tablas.

-- Inserción de registros
INSERT INTO stock_items (item_name, stock) VALUES ('Laptop', 50);

-- Eliminación condicional y total
DELETE FROM stock_items;
DELETE FROM stock_items WHERE item_id = 10;

-- Actualización de registros
UPDATE stock_items SET stock = 100;
UPDATE stock_items SET stock = 80, item_name = 'Gaming Laptop' WHERE item_id = 10;

-- Auditoría automática de tiempo
ALTER TABLE stock_items ADD updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

DQL (Lenguaje de Consulta de Datos)

Utilizado para extraer y transformar datos. A continuación, se muestra cómo poblar una tabla mediante un script de Node.js utilizando el driver mysql2 con soporte para promesas.

const mysql = require('mysql2/promise');
const fs = require('fs');

async function seedDatabase() {
    const pool = await mysql.createPool({
        host: '127.0.0.1',
        user: 'admin',
        password: 'securePass!2023',
        database: 'retail_db',
        waitForConnections: true,
        connectionLimit: 10
    });

    const catalogData = JSON.parse(fs.readFileSync('catalog.json', 'utf8'));
    const insertQuery = 'INSERT INTO stock_items SET ?';

    for (const item of catalogData) {
        await pool.execute(insertQuery, item);
    }
    
    await pool.end();
}

seedDatabase().catch(console.error);

Una vez poblada la base de datos, las consultas permiten filtrar, ordenar y paginar resultados:

-- Proyección y alias
SELECT * FROM stock_items;
SELECT item_id AS code, item_name AS product, stock FROM stock_items;

-- Filtrado lógico y de rangos
SELECT * FROM stock_items WHERE item_name = 'Laptop';
SELECT * FROM stock_items WHERE stock > 10 AND item_name LIKE '%Laptop%';
SELECT * FROM stock_items WHERE stock < 5 OR item_name = 'Keyboard';
SELECT * FROM stock_items WHERE stock BETWEEN 10 AND 50;
SELECT * FROM stock_items WHERE item_name IN ('Laptop', 'Mouse', 'Keyboard');

-- Búsqueda de patrones
SELECT * FROM stock_items WHERE item_name LIKE 'G%';
SELECT * FROM stock_items WHERE item_name LIKE '%book';
SELECT * FROM stock_items WHERE item_name LIKE '_o%';

-- Ordenamiento y paginación
SELECT * FROM stock_items ORDER BY stock DESC;
SELECT * FROM stock_items ORDER BY stock ASC;
SELECT * FROM stock_items LIMIT 10;
SELECT * FROM stock_items LIMIT 10 OFFSET 20;

Tipos de Datos y Restricciones de Tabla

MySQL soporta una amplia gama de tipos: numéricos (INT, DECIMAL), temporales (DATE, DATETIME, TIMESTAMP) y de cadena (VARCHAR, TEXT, BLOB).

Las rsetricciones garantizan la integridad de los datos a nivel de esquema:

CREATE TABLE IF NOT EXISTS employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_code VARCHAR(20) UNIQUE NOT NULL,
    department_id INT DEFAULT 1,
    salary DECIMAL(10,2) DEFAULT(30000.00),
    email VARCHAR(50) UNIQUE
);

Funciones de Argegación y Agrupamiento

Las funciones de agregación operan sobre conjuntos de filas para devolver un único valor resumido.

-- Operaciones matemáticas sobre conjuntos
SELECT AVG(salary) FROM employees WHERE department_id = 2;
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;
SELECT MAX(salary), MIN(salary), SUM(salary), COUNT(*) FROM employees;

-- Agrupamiento y filtrado de grupos
SELECT department_id, AVG(salary) AS avg_sal 
FROM employees 
GROUP BY department_id 
HAVING avg_sal > 40000;

Integridad Referencial y Claves Foráneas

Las claves foráneas vinculan tablas para mantener la consistencia referencial. Las estrategias de propagación (ON UPDATE / ON DELETE) definen el comportamiento ante cambios en la tabla padre.

RESTRICT / NO ACTION: Bloquea la operación si existen registros dependientes.
CASCADE: Propaga la actualización o eliminación a los registros hijos.
SET NULL: Establece el valor de la clave foránea en NULL en los registros hijos.

CREATE TABLE IF NOT EXISTS departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS staff (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) 
    ON UPDATE CASCADE 
    ON DELETE SET NULL
);

-- Reconfiguración de restricciones existentes
ALTER TABLE staff DROP FOREIGN KEY staff_ibfk_1;
ALTER TABLE staff ADD FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON UPDATE CASCADE ON DELETE CASCADE;

Operaciones de Unión (JOIN) y Relaciones Many-to-Many

Los JOIN permiten combinar filas de dos o más tablas basándose en columnas relacionadas. MySQL soporta INNER JOIN, LEFT JOIN y RIGHT JOIN. El FULL OUTER JOIN se simula mediante UNION.

-- Producto cartesiano implícito
SELECT * FROM staff, departments WHERE staff.dept_id = departments.dept_id;

-- Uniones explícitas
SELECT * FROM staff LEFT JOIN departments ON staff.dept_id = departments.dept_id;
SELECT * FROM staff RIGHT JOIN departments ON staff.dept_id = departments.dept_id;
SELECT * FROM staff INNER JOIN departments ON staff.dept_id = departments.dept_id;

-- Simulación de Full Outer Join
(SELECT * FROM staff LEFT JOIN departments ON staff.dept_id = departments.dept_id)
UNION
(SELECT * FROM staff RIGHT JOIN departments ON staff.dept_id = departments.dept_id);

Para modelar relaciones de muchos a muchos, se requiere una tabla intermedia (tabla de unión). El siguiente ejemplo modela desarrolladores y sus habilidades técnicas:

CREATE TABLE IF NOT EXISTS developers (
    dev_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS technologies (
    tech_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- Tabla intermedia
CREATE TABLE IF NOT EXISTS dev_tech (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dev_id INT NOT NULL,
    tech_id INT NOT NULL,
    FOREIGN KEY (dev_id) REFERENCES developers(dev_id) ON UPDATE CASCADE,
    FOREIGN KEY (tech_id) REFERENCES technologies(tech_id) ON UPDATE CASCADE
);

-- Consultas de extracción de relaciones
SELECT 
    d.dev_id, d.name AS devName, t.tech_id, t.name AS techName
FROM developers d
JOIN dev_tech dt ON d.dev_id = dt.dev_id
JOIN technologies t ON dt.tech_id = t.tech_id;

-- Desarrolladores sin habilidades asignadas
SELECT 
    d.dev_id, d.name AS devName, t.tech_id, t.name AS techName
FROM developers d
LEFT JOIN dev_tech dt ON d.dev_id = dt.dev_id
LEFT JOIN technologies t ON dt.tech_id = t.tech_id
WHERE t.tech_id IS NULL;

-- Tecnologías sin desarrolladores asociados
SELECT 
    d.dev_id, d.name AS devName, t.tech_id, t.name AS techName
FROM developers d
RIGHT JOIN dev_tech dt ON d.dev_id = dt.dev_id
RIGHT JOIN technologies t ON dt.tech_id = t.tech_id
WHERE d.dev_id IS NULL;

Etiquetas: MySQL SQL RDBMS database-design nodejs-mysql

Publicado el 6-26 01:11

Friki Work