Consultas Avanzadas y Gestión de Bases de Datos en MySQL

Aplicación de Sentencias Condicionales en Operaciones de Tablas

En MySQL, las sentencias condicionales permiten controlar actualizaciones, inserciones y eliminaciones de registros. Por ejemplo, para actualizar datos basados en condiciones específicas:

-- Actualización condicional
UPDATE empleados SET salario = salario * 1.1 WHERE departamento = 'ventas';
-- Actualización con límite y orden
UPDATE empleados SET bonificacion = bonificacion + 500 ORDER BY fecha_contrato ASC LIMIT 5;
-- Eliminación condicional
DELETE FROM empleados WHERE estado = 'inactivo' ORDER BY fecha_registro DESC LIMIT 10;

Las consultas de unión (JOIN) combinan datos de múltiples tablas. Existen varios tipos, como la unión interna (INNER JOIN) que selecciona registros coincidentes en ambas tablas:

-- Unión interna para obtener empleados y sus departamentos
SELECT e.id_empleado, e.nombre, d.nombre_departamento 
FROM empleados AS e 
INNER JOIN departamentos AS d ON e.id_departamento = d.id_departamento;

-- Unión con condiciones y agrupación
SELECT e.id_empleado, e.nombre, d.nombre_departamento, COUNT(*) AS total
FROM empleados AS e
JOIN departamentos AS d ON e.id_departamento = d.id_departamento
WHERE e.activo = 1
GROUP BY d.nombre_departamento
HAVING COUNT(*) > 3
ORDER BY e.id_empleado ASC
LIMIT 0, 5;

Las uniones externas (LEFT JOIN o RIGHT JOIN) incluyen todos los registros de una tabla y los coincidentes de otra.

Operaciones con Llaves Foráneas

Las llaves foráneas garantizan la integridad referencial entre tablas. Requieren que las tablas usen el motor InnoDB y que los tipos de datos sean compatibles. Ejemplo de creación:

-- Tabla principal: departamentos
CREATE TABLE IF NOT EXISTS departamentos (
    id_departamento SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- Tabla secundaria: empleados con llave foránea
CREATE TABLE IF NOT EXISTS empleados (
    id_empleado INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre_usuario VARCHAR(30) NOT NULL UNIQUE,
    id_departamento SMALLINT UNSIGNED,
    FOREIGN KEY (id_departamento) REFERENCES departamentos(id_departamento)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Al definir acciones como CASCADE, los cambios en la tabla principal se propagan a la secundaria. Por ejemplo, eliminar un departamento también elimina sus empleados asociados.

-- Consulta de unión para ver empleados y departamentos
SELECT e.id_empleado, e.nombre_usuario, d.nombre 
FROM empleados AS e 
JOIN departamentos AS d ON e.id_departamento = d.id_departamento;

-- Probar integridad: intentar eliminar un departamento con empleados
DELETE FROM departamentos WHERE id_departamento = 1; -- Esto eliminará registros relacionados gracias a CASCADE

Las consultas UNION combinan resultados de múltiples SELECT, eliminando duplicados con UNION o manteniéndolos con UNION ALL.

Subconsultas, Expretiones Regulares y Operadores

Las subconsultas permiten anidar consultas dentro de otras. Por ejemplo, con IN:

-- Subconsulta con IN
SELECT id_empleado, nombre FROM empleados 
WHERE id_departamento IN (SELECT id_departamento FROM departamentos WHERE ubicacion = 'norte');

Para expresiones regulares, se usa REGEXP con patrones como '^' para inicio, '$' para fin, y '[ ]' para conjuntos de caracteres:

-- Buscar nombres que comiencen con 'a'
SELECT * FROM empleados WHERE nombre REGEXP '^a';
-- Buscar nombres que contengan 'ez' o 'oz'
SELECT * FROM empleados WHERE nombre REGEXP 'ez|oz';

Los operadores en MySQL incluyen aritméticos (+, -, *, /), comparativos (=, !=, BETWEEN), y lógicos (AND, OR, NOT). Su prioridad afecta el orden de evaluación en expresiones.

Funciones y Expresiones Integradas

MySQL ofrece funciones matemáticas como CEIL() para redondear hacia arriba, FLOOR() hacia abajo, y MOD() para el módulo:

-- Ejemplo de funciones matemáticas
SELECT CEIL(3.2) AS redondeo_arriba, FLOOR(7.8) AS redondeo_abajo, MOD(10, 3) AS modulo;

Las funciones de cadena incluyen CONCAT() para unir textos, LENGTH() para longitud, y REPLACE() para sustituir subcadenas:

-- Manipulación de cadenas
SELECT CONCAT(nombre, ' ', apellido) AS nombre_completo 
FROM empleados 
WHERE LENGTH(nombre) > 5;

Para fechas, funciones como NOW() obtienen la fecha actual, YEAR() extrae el año, y DATEDIFF() calcula diferencias entre fechas:

-- Funciones de fecha
SELECT nombre, fecha_contrato, YEAR(fecha_contrato) AS anio_contrato 
FROM empleados 
WHERE DATEDIFF(NOW(), fecha_contrato) > 365;

Otras funciones útiles incluyen IF() para condicionales, IFNULL() para valores predeterminados, y CASE para lógica compleja.

Uso de Índices y Administración de Bases de Datos

Los índices mejoran el rendimiento de consultas al acelerar búsquedas. Tipos comunes incluyen índices normales, únicos y de texto completo. Ejemplo de creación:

-- Crear tabla con índices
CREATE TABLE productos (
    id_producto INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    precio DECIMAL(10,2),
    INDEX idx_nombre (nombre),
    UNIQUE INDEX idx_precio (precio)
) ENGINE=InnoDB;

Se pueden agregar índices a tablas existentes con CREATE INDEX o ALTER TABLE:

-- Añadir un índice compuesto
CREATE INDEX idx_nombre_precio ON productos(nombre, precio);
-- Eliminar un índice
DROP INDEX idx_nombre ON productos;

Para la administración, herrmaientas como phpMyAdmin o clientes de línea de comandos facilitan la gestión de bases de datos. Es importante monitorear el rendimiento y ajustar índices según las necesidades de consulta.

Etiquetas: MySQL SQL Llaves Foráneas Subconsultas expresiones regulares

Publicado el 6-24 21:21

Friki Work