# Curso de MySQL y bases de datos relacionales para bibliotecarios y profesionales de la información. # Índice del Curso MySQL para Bibliotecarios ## Sesión 1: Conceptos Básicos - ¿Qué es SQL? - Componentes de bases de datos relacionales - Comandos SQL (DDL, DML, DCL, TCL) - Tipos de datos en MySQL - Claves y restricciones ## Sesión 2: Creación y Manipulación de Tablas - Operaciones básicas (CREATE, ALTER, DROP) - Entorno XAMPP y phpMyAdmin - Respaldos - Inserción masiva de datos ## Sesión 3: Manipulación de Registros - Operaciones CRUD - Consultas básicas con SELECT - Filtros y ordenación (WHERE, ORDER BY) - Funciones de agregación (COUNT, MAX, MIN) - GROUP BY y HAVING ## Sesión 4: Consultas Avanzadas - Uso de alias - Tipos de JOIN - Subconsultas - Vistas - Funciones especiales ## Sesión 5: Optimización y Seguridad - Índices y rendimiento - Análisis de consultas con EXPLAIN - Gestión de usuarios y privilegios - Respaldos y restauración - Documentación (diccionario de datos) ## Sesión 6: Automatización - Consultas programadas - Procedimientos almacenados - Eventos MySQL - Importación de datos externos - Recursos adicionales ## Sesión 1. Conceptos Básicos de SQL ### ¿Qué es SQL? SQL (Structured Query Language) es un lenguaje de programación diseñado para administrar y recuperar información de sistemas de gestión de bases de datos relacionales (RDBMS). Desarrollado inicialmente por IBM en los años 70, se ha convertido en el estándar para interactuar con bases de datos. Aquí puedes ver los componentes y comandos principales que encontrarás en este material: ![SQl sheet](https://hackmd.io/_uploads/HkTFB5a51l.jpg) ### Componentes principales de una base de datos relacional 1. **Tablas**: Estructuras que almacenan datos en formato de filas y columnas (similar a una hoja de cálculo). 2. **Campos/Columnas**: Cada columna representa un atributo específico de los datos. 3. **Registros/Filas**: Cada fila contiene un conjunto de datos relacionados. 4. **Claves**: Identificadores únicos que permiten relacionar tablas entre sí. 5. **Relaciones**: Conexiones lógicas entre tablas que establecen cómo se relacionan los datos. ### Categorías principales de comandos SQL 1. **DDL (Data Definition Language)**: Comandos para definir y modificar estructuras de datos - `CREATE`: Crear objetos como bases de datos, tablas, vistas - `ALTER`: Modificar objetos existentes - `DROP`: Eliminar objetos - `TRUNCATE`: Vaciar tablas 2. **DML (Data Manipulation Language)**: Comandos para manipular datos - `SELECT`: Consultar datos - `INSERT`: Insertar nuevos registros - `UPDATE`: Modificar registros existentes - `DELETE`: Eliminar registros 3. **DCL (Data Control Language)**: Comandos para control de acceso - `GRANT`: Otorgar permisos - `REVOKE`: Revocar permisos 4. **TCL (Transaction Control Language)**: Comandos para control de transacciones - `COMMIT`: Confirmar cambios - `ROLLBACK`: Deshacer cambios - `SAVEPOINT`: Establecer puntos de guardado ### Ejemplo en contexto bibliotecario: En un sistema de gestión de biblioteca, SQL permite: - Crear tablas para libros, usuarios, préstamos y catálogos - Insertar nuevos libros en el inventario - Consultar disponibilidad de títulos - Actualizar el estado de los préstamos - Generar informes de actividad - Relacionar préstamos con usuarios y libros - Gestionar permisos para diferentes tipos de personal ## Tipos de Datos en MySQL Los tipos de datos definen qué clase de información puede almacenarse en cada columna. Elegir el tipo de dato adecuado es crucial para la eficiencia y precisión de la base de datos. ### Tipos Numéricos | Tipo | Descripción | Rango | Uso en biblioteca | |------|-------------|-------|-------------------| | `INT` | Entero estándar | -2,147,483,648 a 2,147,483,647 | IDs de libros, conteo de ejemplares | | `TINYINT` | Entero pequeño | -128 a 127 | Flags, estados (1/0) | | `SMALLINT` | Entero mediano | -32,768 a 32,767 | Año de publicación | | `DECIMAL(p,s)` | Número exacto con precisión | Depende de p (precisión) y s (escala) | Precios, multas por retraso | | `FLOAT` | Número de punto flotante | Depende de hardware | Calificaciones, valoraciones | ### Tipos de Cadenas de Texto | Tipo | Descripción | Almacenamiento | Uso en biblioteca | |------|-------------|----------------|-------------------| | `CHAR(n)` | Cadena de longitud fija | n bytes | Códigos de clasificación, ISBN | | `VARCHAR(n)` | Cadena de longitud variable | Longitud real + 1-2 bytes | Títulos, nombres de autores | | `TEXT` | Texto largo (hasta 65,535 caracteres) | Longitud + 2 bytes | Resúmenes, descripciones | | `ENUM` | Lista de valores permitidos | 1-2 bytes | Estado de libro, tipo de material | ### Tipos de Fecha y Hora | Tipo | Descripción | Formato | Uso en biblioteca | |------|-------------|---------|-------------------| | `DATE` | Solo fecha | 'YYYY-MM-DD' | Fecha de adquisición | | `TIME` | Solo hora | 'HH:MM:SS' | Horarios de apertura | | `DATETIME` | Fecha y hora | 'YYYY-MM-DD HH:MM:SS' | Fecha y hora de préstamo | | `TIMESTAMP` | Marca temporal | 'YYYY-MM-DD HH:MM:SS' | Registro de operaciones | | `YEAR` | Año | 'YYYY' | Año de publicación | ### Tipos Binarios | Tipo | Descripción | Uso en biblioteca | |------|-------------|-------------------| | `BLOB` | Objeto binario grande | Portadas de libros escaneadas | | `BINARY` | Datos binarios de longitud fija | Códigos de barras | ### Ejemplo en contexto bibliotecario: ```sql CREATE TABLE libros ( id INT AUTO_INCREMENT PRIMARY KEY, -- ID numérico único para cada libro isbn CHAR(13) UNIQUE, -- ISBN con longitud fija de 13 caracteres titulo VARCHAR(200) NOT NULL, -- Título de longitud variable autor VARCHAR(100) NOT NULL, -- Nombre del autor descripcion TEXT, -- Resumen/descripción larga del libro anio_publicacion YEAR, -- Solo el año de publicación fecha_adquisicion DATE, -- Fecha cuando se adquirió el libro precio DECIMAL(6,2), -- Precio con 2 decimales (ej. 9999.99) estado ENUM('Disponible','Prestado','Reparación','Extraviado'), -- Estados predefinidos portada BLOB, -- Imagen de la portada ultima_actualizacion TIMESTAMP -- Cuándo se actualizó el registro por última vez ); ``` ## Claves y Restricciones Las claves y restricciones garantizan la integridad de los datos y establecen reglas que deben cumplirse. ### Tipos de Claves | Tipo de Clave | Descripción | Ejemplo en biblioteca | |---------------|-------------|----------------------| | **Clave Primaria (PRIMARY KEY)** | Identifica de forma única cada registro en una tabla | ID de libro, número de carnet de usuario | | **Clave Foránea (FOREIGN KEY)** | Campo que se relaciona con la clave primaria de otra tabla | ID de usuario en tabla de préstamos | | **Clave Única (UNIQUE)** | Garantiza que todos los valores en una columna sean únicos | ISBN, email de usuario | | **Clave Compuesta** | Clave primaria formada por múltiples columnas | Combinación de código de biblioteca y código de libro | ### Restricciones Comunes | Restricción | Descripción | Ejemplo en biblioteca | |-------------|-------------|----------------------| | **NOT NULL** | No permite valores nulos | Título de libro no puede ser nulo | | **DEFAULT** | Asigna un valor predeterminado | Estado predeterminado 'Disponible' | | **CHECK** | Verifica que los datos cumplan una condición | Año de publicación no puede ser futuro | | **AUTO_INCREMENT** | Genera valores secuenciales automáticamente | ID de libro que se incrementa solo | ### Ejemplos en contexto bibliotecario: ```sql -- Clave primaria CREATE TABLE usuarios ( id INT AUTO_INCREMENT PRIMARY KEY, -- Clave primaria auto-incrementada nombre VARCHAR(100) NOT NULL, -- Restricción NOT NULL email VARCHAR(100) UNIQUE, -- Clave única fecha_registro DATE DEFAULT CURRENT_DATE, -- Valor predeterminado = fecha actual tipo_usuario ENUM('Estudiante', 'Profesor', 'Personal', 'Externo') NOT NULL, multa_pendiente DECIMAL(6,2) CHECK (multa_pendiente >= 0) -- Restricción CHECK ); -- Clave foránea CREATE TABLE prestamos ( id INT AUTO_INCREMENT PRIMARY KEY, libro_id INT NOT NULL, usuario_id INT NOT NULL, fecha_prestamo DATE DEFAULT CURRENT_DATE, fecha_devolucion DATE, FOREIGN KEY (libro_id) REFERENCES libros(id), -- Clave foránea a tabla libros FOREIGN KEY (usuario_id) REFERENCES usuarios(id) -- Clave foránea a tabla usuarios ); -- Clave compuesta CREATE TABLE copias_libros ( biblioteca_id INT, libro_id INT, numero_copia INT, estado ENUM('Disponible', 'Prestado', 'Reparación') DEFAULT 'Disponible', PRIMARY KEY (biblioteca_id, libro_id, numero_copia) -- Clave primaria compuesta ); ``` # Sesión 2 del curso: Creación de bases de datos y manipulación de Tablas **Duración:** 3 horas **Objetivos:** - Configurar el entorno de desarrollo local mediante XAMPP y phpMyAdmin. - Crear bases de datos desde phpmyadmin - Diseñar, crear y modificar tablas en MySQL para tratar datos reales. - Aplicar las operaciones básicas de SQL: - **Creación:** `CREATE TABLE` - **Modificación:** `ALTER TABLE` - **Eliminación:** `DROP TABLE` - **Inserción:** `INSERT INTO` - Elaborar diagramas de relaciones (ER) que faciliten la visualización de la estructura de la base de datos. - Enseñar buenas prácticas, como la realización de respaldos previos antes de ejecutar operaciones destructivas. --- ## 1. Introducción En sesiones previas se introdujeron conceptos básicos de MySQL. En esta sesión se desarrolla un caso práctico básico: 1. **Base de datos de colecciones:** Caso muy básico para comenzar a aprender cómo crear la primera base de datos, crear tablas, definir claves, establecer relaciones y visualizar realaciones entre tablas. --- ## 2. Fundamentos Teóricos y Operaciones Básicas ### 2.1. Operaciones sobre Datos en MySQL - **Creación de Tablas (`CREATE TABLE`):** Define la estructura de una tabla. ```sql CREATE TABLE nombre_tabla ( columna1 TIPO_DE_DATO CONDICIONES, columna2 TIPO_DE_DATO CONDICIONES, ... ); ---# Comandos SQL y Gestión de Bases de Datos ## Modificación de Tablas (ALTER TABLE) Permite agregar, modificar o eliminar columnas. ```sql ALTER TABLE nombre_tabla ADD columna_nueva TIPO_DE_DATO; ALTER TABLE nombre_tabla MODIFY COLUMN columna EXISTENTE NUEVO_TIPO; ALTER TABLE nombre_tabla DROP COLUMN columna; ``` ## Eliminación de Tablas (DROP TABLE) Se utiliza para borrar una tabla completa (siempre con respaldo previo). ```sql DROP TABLE nombre_tabla; ``` ## Inserción de Registros (INSERT INTO) Agrega datos a una tabla. ```sql INSERT INTO nombre_tabla (columna1, columna2, ...) VALUES (valor1, valor2, ...); ``` ## 2.2. Entorno XAMPP y phpMyAdmin ### XAMPP Herramienta que integra Apache, MySQL y PHP para desarrollar de forma local. # Comandos SQL y Gestión de Bases de Datos ## Modificación de Tablas (ALTER TABLE) Permite agregar, modificar o eliminar columnas. ```sql ALTER TABLE nombre_tabla ADD columna_nueva TIPO_DE_DATO; ALTER TABLE nombre_tabla MODIFY COLUMN columna EXISTENTE NUEVO_TIPO; ALTER TABLE nombre_tabla DROP COLUMN columna; ``` ## Eliminación de Tablas (DROP TABLE) Se utiliza para borrar una tabla completa (siempre con respaldo previo). ```sql DROP TABLE nombre_tabla; ``` ## Inserción de Registros (INSERT INTO) Agrega datos a una tabla. ```sql INSERT INTO nombre_tabla (columna1, columna2, ...) VALUES (valor1, valor2, ...); ``` ## Inserción Masiva de Datos en phpMyAdmin Para insertar datos de forma masiva (y no de manera manual uno por uno) en phpMyAdmin, tienes varias opciones: ## 1. Usar la función "Importar" con un archivo CSV o SQL * Prepara un archivo CSV con los datos que quieres insertar. Asegúrate de que el separador de columnas (generalmente la coma "," o el punto y coma ";") y el de texto (por ejemplo, comillas dobles) sean consistentes con la configuración de phpMyAdmin. * En phpMyAdmin, selecciona la base de datos y la tabla donde desees insertar los datos. * Haz clic en la pestaña **"Importar"**. * En la sección **"Archivo a importar"**, elige tu archivo CSV o SQL. * Ajusta las opciones de formato (por ejemplo, establece **CSV** si es un archivo de texto) y configura correctamente el separador de campos, de texto, cotejamiento (codificación), etc. * Haz clic en **"Continuar"** para comenzar la importación. Notas: - si el id es autoincremental (NO se incluye en CSV) - las tablas que importan que se nombren de la misma forma que las tablas de tu base de datos - confirma que el separador es correcto, ; - Cdificalo a uft8 - ## 1. Generar el archivo SQL 🔹 Usando la función de "Exportar" en phpMyAdmin Si deseas crear un archivo con INSERT INTO directamente en phpMyAdmin, sigue estos pasos: 1️⃣ Abre phpMyAdmin y selecciona tu base de datos. 2️⃣ Ve a la pestaña "SQL". 3️⃣ Escribe la consulta SQL con los datos que quieres insertar: sql Copiar código INSERT INTO Colecciones (nombre_coleccion, descripcion) VALUES ("Literatura Clásica", "Colección de libros clásicos de diferentes siglos."), ("Ciencia Ficción", "Libros con temática futurista y espacial."), ("Biografías", "Colección de vidas y obras de personajes ilustres."), ("Poesía", "Antologías y obras destacadas de poesía mundial."), ("Historia", "Libros sobre eventos históricos y civilizaciones."), ("Filosofía", "Obras fundamentales del pensamiento filosófico."), ("Novela Negra", "Colección de novelas policíacas y de misterio."), ("Literatura Infantil", "Libros adaptados para público infantil."), ("Ensayo", "Colección de ensayos sobre temas diversos."), ("Ciencia", "Obras de divulgación científica."); 4️⃣ Haz clic en "Simular consulta" para asegurarte de que no hay errores. 5️⃣ Si la consulta está bien, ve a la pestaña "Exportar" (arriba en phpMyAdmin). 6️⃣ En "Método de exportación", elige "Personalizado". 7️⃣ Selecciona el formato "SQL". 8️⃣ En opciones, activa "Agregar instrucciones INSERT" para que guarde las sentencias INSERT INTO. 9️⃣ Haz clic en "Continuar" y phpMyAdmin generará el archivo .sql para descargarlo ## 2.2. Entorno XAMPP y phpMyAdmin ### XAMPP XAMPP es una distribución de Apache que integra en un solo paquete Apache, MySQL/MariaDB, PHP y Perl. Es una herramienta esencial para desarrollar y probar aplicaciones web de forma local sin necesidad de acceso a internet o servicios externos. #### Instalación de XAMPP 1. **Descarga del instalador**: - Visita el sitio oficial de XAMPP: [https://www.apachefriends.org/es/index.html](https://www.apachefriends.org/es/index.html) - Selecciona la versión adecuada para tu sistema operativo (Windows, macOS o Linux) - Descarga el instalador (archivo .exe para Windows, .dmg para macOS, o .run para Linux) 2. **Proceso de instalación en Windows**: - Ejecuta el archivo descargado como administrador - Si aparece una advertencia de seguridad de Windows, haz clic en "Sí" - En la pantalla de bienvenida del instalador, haz clic en "Next" - Selecciona los componentes a instalar (para este curso, asegúrate de que Apache, MySQL, PHP y phpMyAdmin estén seleccionados) - Elige la carpeta de instalación (por defecto es C:\xampp) y haz clic en "Next" - Desmarca la opción de aprender más sobre Bitnami si no estás interesado - Haz clic en "Next" para comenzar la instalación - Espera a que se complete el proceso de instalación - Al finalizar, marca la casilla "Do you want to start the Control Panel now?" y haz clic en "Finish" 3. **Ejecución del Panel de Control de XAMPP**: - El Panel de Control de XAMPP se abrirá automáticamente después de la instalación - También puedes abrirlo manualmente ejecutando "xampp-control.exe" desde la carpeta de instalación - El panel te muestra todos los servicios disponibles (Apache, MySQL, FileZilla, Mercury, Tomcat) 4. **Iniciar los servicios necesarios**: - Para comenzar a trabajar, necesitarás al menos iniciar Apache y MySQL - Haz clic en el botón "Start" junto a Apache - Haz clic en el botón "Start" junto a MySQL - Si ves el texto "Running" en verde junto a ambos servicios, significa que están funcionando correctamente 5. **Configuración de puertos (en caso de conflictos)**: - Si al iniciar Apache o MySQL aparece un error de que el puerto está en uso: - Para Apache: Haz clic en "Config" → "Apache (httpd.conf)" y cambia los puertos 80 y 443 a otros disponibles (ejemplo: 8080 y 8443) - Para MySQL: Haz clic en "Config" → "MySQL (my.ini)" y cambia el puerto 3306 a otro disponible (ejemplo: 3307) - Guarda los cambios y reinicia los servicios 6. **Verificar la instalación**: - Abre tu navegador web y escribe "http://localhost/" o "http://127.0.0.1/" - Si ves la página de bienvenida de XAMPP, la instalación fue exitosa - La página de bienvenida contiene enlaces a herramientas útiles, incluyendo phpMyAdmin ### phpMyAdmin phpMyAdmin es una herramienta escrita en PHP que permite administrar bases de datos MySQL a través de una interfaz web intuitiva, sin necesidad de conocer comandos SQL complejos. #### Acceso y configuración de phpMyAdmin 1. **Acceder a phpMyAdmin**: - Asegúrate de que los servicios Apache y MySQL estén iniciados en el Panel de Control de XAMPP - Abre tu navegador web y escribe "http://localhost/phpmyadmin" o "http://127.0.0.1/phpmyadmin" - Se abrirá la interfaz de inicio de phpMyAdmin - No es necesario iniciar sesión en instalaciones nuevas de XAMPP, ya que por defecto no tiene contraseña (aunque se recomienda configurar una para entornos reales) 2. **Interfaz de phpMyAdmin**: - **Panel izquierdo**: Muestra la lista de bases de datos disponibles - **Panel central**: Área principal de trabajo, donde se muestran tablas, resultados de consultas, etc. - **Barra superior**: Contiene menús para diferentes funciones (Base de datos, SQL, Estado, Usuario, Exportar, Importar, etc.) 3. **Configuración inicial recomendada**: - Establecer una contraseña para el usuario root: - Haz clic en "Usuario" en la barra superior - Localiza el usuario "root" y haz clic en "Editar privilegios" - Selecciona la pestaña "Cambiar contraseña" - Introduce una contraseña segura y confírmala - Haz clic en "Ejecutar" 4. **Configurar el archivo config.inc.php** (opcional pero recomendado): - Localiza el archivo "config.inc.php" en la carpeta "C:\xampp\phpMyAdmin\" - Realiza una copia de seguridad del archivo original - Edita el archivo con un editor de texto (como Notepad++ o Visual Studio Code) - Encuentra la línea `$cfg['Servers'][$i]['auth_type'] = 'config';` y cámbiala a `$cfg['Servers'][$i]['auth_type'] = 'cookie';` - Esto hará que phpMyAdmin solicite credenciales al iniciar, lo que es más seguro #### Funciones básicas de phpMyAdmin 1. **Crear una base de datos**: - Haz clic en "Nueva" en el panel izquierdo - Introduce un nombre para la base de datos - Selecciona el cotejamiento (recomendado: utf8_general_ci para soporte de caracteres internacionales) - Haz clic en "Crear" 2. **Crear una tabla**: - Selecciona la base de datos en el panel izquierdo - Haz clic en "Nueva tabla" - Introduce un nombre para la tabla y el número de columnas - Haz clic en "Ejecutar" - Define cada columna (nombre, tipo, longitud, índice, etc.) - Haz clic en "Guardar" cuando hayas terminado 3. **Insertar datos**: - Selecciona la tabla en el panel izquierdo - Haz clic en la pestaña "Insertar" - Completa los campos con los valores deseados - Haz clic en "Continuar" para añadir más registros o "Ejecutar" para finalizar 4. **Consultar datos**: - Selecciona la tabla en el panel izquierdo - Haz clic en la pestaña "Examinar" para ver todos los registros - Utiliza la pestaña "Buscar" para realizar consultas más específicas - Usa la pestaña "SQL" para ejecutar consultas SQL personalizadas 5. **Exportar datos**: - Selecciona la base de datos o tabla en el panel izquierdo - Haz clic en la pestaña "Exportar" - Elige el método "Rápido" para opciones predeterminadas o "Personalizado" para más opciones - Selecciona el formato (SQL es el más común para respaldos) - Configura opciones adicionales si es necesario - Haz clic en "Ejecutar" 6. **Importar datos**: - Selecciona la base de datos en el panel izquierdo - Haz clic en la pestaña "Importar" - Haz clic en "Examinar" y selecciona el archivo a importar - Configura las opciones según sea necesario - Haz clic en "Ejecutar" 7. **Gestión de usuarios**: - Haz clic en "Usuario" en la barra superior - Aquí puedes añadir nuevos usuarios, modificar privilegios o eliminar usuarios existentes - Para añadir un usuario, haz clic en "Agregar usuario" - Completa los datos (nombre, host, contraseña, privilegios) - Haz clic en "Ejecutar" 8. **Usar el diseñador visual (Designer)**: - Selecciona la base de datos en el panel izquierdo - Haz clic en "Diseñador" en el menú superior - Añade las tablas que deseas visualizar - Arrastra las tablas para organizarlas - Las relaciones entre tablas se muestran con líneas conectoras - Puedes crear nuevas relaciones arrastrando campos entre tablas ## 2.3. Respaldo Previo (Backup) Antes de ejecutar comandos destructivos como DROP TABLE es fundamental respaldar la información. Se recomienda: ### Exportar la Base de Datos o Tabla en phpMyAdmin 1. Seleccionar la base de datos o la tabla a respaldar. 2. Hacer clic en la pestaña "Exportar". 3. Elegir el método "Rápido" o "Personalizado" según la necesidad. 4. Guardar el archivo SQL generado. ### Uso del Comando mysqldump (Línea de Comandos) ```bash mysqldump -u [usuario] -p [nombre_base_de_datos] > respaldo.sql ``` Este comando crea un archivo con las instrucciones necesarias para restaurar la base de datos. ### Restauración Utilizar la pestaña "Importar" en phpMyAdmin o el comando: ```bash mysql -u [usuario] -p [nombre_base_de_datos] < respaldo.sql ``` # Caso prático 1. Estructura de la Base de Datos de Colecciones ## 1. Creación de la tabla `colecciones` ```sql CREATE TABLE colecciones ( id_coleccion INT AUTO_INCREMENT PRIMARY KEY, nombre_coleccion VARCHAR(100) NOT NULL, descripcion TEXT ); ``` * **id_coleccion**: Clave primaria (primary key) de tipo entero, con autoincremento. * **nombre_coleccion**: Nombre de la colección, de tipo `VARCHAR(100)`. * **descripcion**: Campo más largo para describir la colección, de tipo `TEXT`. ## 2. Creación de la tabla `libros` ```sql CREATE TABLE libros ( id_libro INT AUTO_INCREMENT PRIMARY KEY, titulo VARCHAR(255) NOT NULL, autor VARCHAR(255) NOT NULL, isbn CHAR(13), editorial VARCHAR(100), ano_publicacion YEAR, id_coleccion INT, FOREIGN KEY (id_coleccion) REFERENCES colecciones(id_coleccion) ); ``` * **id_libro**: Clave primaria de la tabla `libros`, entero con autoincremento. * **titulo**: Título del libro (`VARCHAR(255)`). * **autor**: Autor del libro (`VARCHAR(255)`). * **isbn**: Código ISBN de 13 caracteres (`CHAR(13)`). * **editorial**: Nombre de la editorial (`VARCHAR(100)`). * **ano_publicacion**: Año de publicación (`YEAR`). * **id_coleccion**: Referencia a la tabla `colecciones`, para indicar a qué colección pertenece el libro. ## 3. Inserción de datos. # Inserción Masiva de Datos en phpMyAdmin Para insertar datos de forma masiva (y no de manera manual uno por uno) en phpMyAdmin, tienes varias opciones: ## 1. Usar la función "Importar" con un archivo CSV o SQL * Prepara un archivo CSV con los datos que quieres insertar. Asegúrate de que el separador de columnas (generalmente la coma "," o el punto y coma ";") y el de texto (por ejemplo, comillas dobles) sean consistentes con la configuración de phpMyAdmin. * En phpMyAdmin, selecciona la base de datos y la tabla donde desees insertar los datos. * Haz clic en la pestaña **"Importar"**. * En la sección **"Archivo a importar"**, elige tu archivo CSV o SQL. * Ajusta las opciones de formato (por ejemplo, establece **CSV** si es un archivo de texto) y configura correctamente el separador de campos, de texto, cotejamiento (codificación), etc. * Haz clic en **"Continuar"** para comenzar la importación. Notas: si el id es autoincremental (NO se incluye en CSV): ## 2. Ejecutar un script SQL con múltiples INSERT * Crea un archivo `.sql` que contenga múltiples sentencias `INSERT` o `INSERT IGNORE` (según necesites), por ejemplo: ```sql INSERT INTO nombre_tabla (columna1, columna2, columna3) VALUES ('valor1a', 'valor2a', 'valor3a'), ('valor1b', 'valor2b', 'valor3b'), (..., ..., ...); ``` * Ve a la misma pestaña **"Importar"** en phpMyAdmin. * Selecciona el archivo `.sql` que contenga las instrucciones de inserción. * Deja las opciones por defecto (o ajusta el cotejamiento si usas otro que UTF-8). * Da clic en **"Continuar"**. ## 3. Usar la línea de comandos (si tienes acceso) * Esto ya no es directamente en phpMyAdmin, sino con MySQL/MariaDB en consola. Puede ser útil para manejar grandes volúmenes de datos: ```bash mysql -u usuario -p nombre_base < archivo_insercion.sql ``` * Después de esto, verifica en phpMyAdmin que la importación haya sido correcta. Generalmente, para no insertar datos de forma manual en phpMyAdmin, la forma más sencilla suele ser preparar un archivo CSV o un script SQL e importarlo mediante la pestaña "Importar". Asegúrate de que las columnas, tipos de datos y codificaciones concuerden entre tu archivo y la tabla de destino. ## 3. Inserción de datos de ejemplo en `colecciones` ### 📂 Descarga de archivos CSV Puedes descargar los archivos CSV desde el siguiente enlace: [📥 Descargar archivos CSV](https://drive.google.com/drive/folders/1sLbRZ2PzVbUmgxUaGj0fUDzpIRFX-KT1) Para crear un fichero .sql, este es el código ```sql INSERT INTO colecciones (nombre_coleccion, descripcion) VALUES ('Literatura Clásica', 'Colección de libros clásicos de diferentes siglos.'), ('Ciencia Ficción', 'Libros con temática futurista y espacial.'), ('Biografías', 'Colección de vidas y obras de personajes ilustres.'); ``` ## 4. Inserción de datos de ejemplo en `libros` ```sql INSERT INTO libros (titulo, autor, isbn, editorial, ano_publicacion, id_coleccion) VALUES ('Don Quijote de la Mancha', 'Miguel de Cervantes', '9788467037420', 'Santillana', 2010, 1), ('1984', 'George Orwell', '9780451524935', 'Signet Classic', 1961, 2), ('Yo, Robot', 'Isaac Asimov', '9780553294385', 'Bantam', 1991, 2), ('Steve Jobs', 'Walter Isaacson', '9781451648539', 'Simon & Schuster', 2011, 3), ('El diario de Ana Frank', 'Ana Frank', '9780553296983', 'Bantam', 1993, 3); ``` En este ejemplo: * Varios libros (Don Quijote, 1984, Yo, Robot...) pertenecen a diferentes colecciones (1 = Literatura Clásica, 2 = Ciencia Ficción, 3 = Biografías). * Si intentas insertar un `id_coleccion` que no exista en la tabla `colecciones`, la operación fallará, protegiendo la integridad referencial. ## 3.6. Creación del Diagrama de Relaciones (ER) Visual ### Pasos en phpMyAdmin 1. Seleccione la base de datos sistema_prestamos en el panel izquierdo. 2. Haga clic en "Designer" o "Relación" en el menú superior. 3. Organice las tablas (usuarios, libros y préstamos) arrastrándolas para visualizar claramente las relaciones (las claves foráneas se conectan automáticamente a las claves primarias). 4. Guarde o exporte el diagrama para documentación. # Sesión 3 del curso: Manipulación de registros en bases de datos. Queries con SELECT ## Introducción En esta sesión, trabajaremos con MySQL para gestionar una base de datos de préstamos en una biblioteca. Aprenderemos a crear y manipular tablas, insertar registros y realizar consultas utilizando el comando SELECT. El objetivo principal es que los bibliotecarios puedan manejar bases de datos de manera eficiente para registrar y consultar información sobre préstamos de libros. ## Objetivos - Crear una base de datos en MySQL para gestionar préstamos. - Insertar registros de usuarios, libros y préstamos. Aprenderás a insertar los registros usando lenguaje SQL - Manipular registros existentes con UPDATE y DELETE. - Aprender a realizar consultas básicas con SELECT para extraer información útil. ## 1. Creación de la base de datos y tablas ### 1. Prompt para generar la base de datos con IA Si deseas utilizar Inteligencia Artificial para generar el código SQL para la base de datos de biblioteca, puedes usar el siguiente prompt: Crea un script SQL completo para una base de datos de biblioteca llamada "Biblioteca" con las siguientes tablas y campos: 1. Configura la base de datos para usar CHARACTER SET utf8mb4 y COLLATE utf8mb4_unicode_ci. 2. Tabla "Usuarios": - id_usuario (clave primaria auto-incrementable) - nombre - correo (único) - telefono* 3. Tabla "Libros": - id_libro (clave primaria auto-incrementable) - titulo - autor - anio_publicacion - isbn (único) - disponible (booleano, valor predeterminado TRUE) 4. Tabla "Prestamos": - id_prestamo (clave primaria auto-incrementable) - id_usuario (referencia a Usuarios) - id_libro (referencia a Libros) - fecha_prestamo - fecha_devolucion 5. Configura las relaciones para que: - No se puedan eliminar usuarios o libros que tengan préstamos asociados - Las actualizaciones de IDs se propaguen automáticamente Incluye comentarios explicativos en el código*.* .......................................................................... Este prompt te ayudará a generar la estructura básica de la base de datos utilizada en este curso. La IA sugerirá los tipos de datos apropiados y las configuraciones para cada campo, que luego podrás ajustar según tus necesidades específicas.# Gestión de Base de Datos de Biblioteca con MySQL ### Crear la base de datos ```sql CREATE DATABASE IF NOT EXISTS Prestamos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE Prestamos; ``` Explicación detallada: CREATE DATABASE: Comando principal que indica a MySQL que debe crear una nueva base de datos. IF NOT EXISTS: Condición que evita errores si la base de datos ya existe. Si ya existe una base de datos con el nombre especificado, el comando no hará nada en lugar de fallar. Biblioteca: Nombre asignado a la nueva base de datos. CHARACTER SET utf8mb4: Define el conjunto de caracteres que utilizará la base de datos. utf8mb4 es una versión mejorada de UTF-8 que puede almacenar caracteres que requieren más de 3 bytes Permite almacenar cualquier carácter Unicode (incluyendo emojis y caracteres especiales) COLLATE utf8mb4_unicode_ci: Define las reglas de ordenación (collation) para la base de datos. utf8mb4_unicode_ci especifica la ordenación unicode case-insensitive Determina cómo se comparan y ordenan los caracteres El sufijo _ci significa "case insensitive", no distingue entre mayúsculas y minúsculas Esta ordenación sigue las reglas de Unicode, por lo que funciona bien con múltiples idiomas USE Biblioteca: Selecciona la base de datos recién creada para su uso en las consultas posteriores. ### Crear las tablas ```sql CREATE TABLE Usuarios ( id_usuario INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, correo VARCHAR(100) UNIQUE NOT NULL, telefono VARCHAR(20) )ENGINE=InnoDB; CREATE TABLE Libros ( id_libro INT AUTO_INCREMENT PRIMARY KEY, titulo VARCHAR(200) NOT NULL, autor VARCHAR(100) NOT NULL, anio_publicacion INT, isbn VARCHAR(20) UNIQUE, disponible BOOLEAN DEFAULT TRUE )ENGINE=InnoDB; CREATE TABLE Prestamos ( id_prestamo INT AUTO_INCREMENT PRIMARY KEY, id_usuario INT, id_libro INT, fecha_prestamo DATE, fecha_devolucion DATE, FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (id_libro) REFERENCES Libros(id_libro) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB; ``` Explicación de las claves foráneas y acciones referenciales: En la tabla Prestamos, se han definido dos claves foráneas con comportamientos específicos: FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario) Esta clave foránea vincula cada préstamo con un usuario de la tabla Usuarios ON DELETE RESTRICT: Impide eliminar un usuario si tiene préstamos asociados ON UPDATE CASCADE: Si cambia el id_usuario en la tabla Usuarios, actualiza automáticamente todas las referencias en la tabla Prestamos FOREIGN KEY (id_libro) REFERENCES Libros(id_libro) Esta clave foránea vincula cada préstamo con un libro de la tabla Libros ON DELETE RESTRICT: Impide eliminar un libro si tiene préstamos asociados ON UPDATE CASCADE: Si cambia el id_libro en la tabla Libros, actualiza automáticamente todas las referencias en la tabla Prestamos Las acciones referenciales pueden ser: RESTRICT: Impide la operación si existen registros relacionados (comportamiento más seguro) CASCADE: Propaga la operación a todos los registros relacionados SET NULL: Establece NULL en los campos relacionados NO ACTION: Similar a RESTRICT, pero se comprueba al final de la transacción SET DEFAULT: Establece el valor predeterminado en los campos relacionados ## 2. Inserción de Registros A continuación se muestran ejemplos de inserción de mínimo 20 registros para cada tabla. ### Usuarios (20 registros) ```sql INSERT INTO usuarios (nombre, correo, telefono) VALUES ('María González', 'maria.gonzalez@biblioteca.com', '555-1234'), ('Juan Pérez', 'juan.perez@biblioteca.com', '555-2345'), ('Ana López', 'ana.lopez@biblioteca.com', '555-3456'), ('Carlos Ramírez', 'carlos.ramirez@biblioteca.com', '555-4567'), ('Laura Fernández', 'laura.fernandez@biblioteca.com', '555-5678'), ('Luis Torres', 'luis.torres@biblioteca.com', '555-6789'), ('Sofía Martínez', 'sofia.martinez@biblioteca.com', '555-7890'), ('Miguel Hernández', 'miguel.hernandez@biblioteca.com', '555-8901'), ('Elena Díaz', 'elena.diaz@biblioteca.com', '555-9012'), ('Pedro Gómez', 'pedro.gomez@biblioteca.com', '555-0123'), ('Verónica Ruiz', 'veronica.ruiz@biblioteca.com', '555-1122'), ('Roberto Sánchez', 'roberto.sanchez@biblioteca.com', '555-2233'), ('Claudia Morales', 'claudia.morales@biblioteca.com', '555-3344'), ('Fernando Rojas', 'fernando.rojas@biblioteca.com', '555-4455'), ('Patricia Vega', 'patricia.vega@biblioteca.com', '555-5566'), ('Diego Castillo', 'diego.castillo@biblioteca.com', '555-6677'), ('Mónica Navarro', 'monica.navarro@biblioteca.com', '555-7788'), ('Andrés Silva', 'andres.silva@biblioteca.com', '555-8899'), ('Gabriela Cruz', 'gabriela.cruz@biblioteca.com', '555-9900'), ('Esteban Molina', 'esteban.molina@biblioteca.com', '555-1010'); ``` ### Libros (20 registros) ```sql INSERT INTO libros (titulo, autor, anio_publicacion, isbn) VALUES ('Cien Años de Soledad', 'Gabriel García Márquez', 1967, '9788437604947'), ('El amor en los tiempos del cólera', 'Gabriel García Márquez', 1985, '9780307389732'), ('La sombra del viento', 'Carlos Ruiz Zafón', 2001, '9780143126393'), ('Rayuela', 'Julio Cortázar', 1963, '9788497592573'), ('Ficciones', 'Jorge Luis Borges', 1944, '9788491050509'), ('Don Quijote de la Mancha', 'Miguel de Cervantes', 1605, '9788491051191'), ('La ciudad y los perros', 'Mario Vargas Llosa', 1963, '9788497591681'), ('El túnel', 'Ernesto Sabato', 1948, '9788491051413'), ('Pedro Páramo', 'Juan Rulfo', 1955, '9788437609080'), ('Los detectives salvajes', 'Roberto Bolaño', 1998, '9788497591903'), ('Sobre héroes y tumbas', 'Ernesto Sabato', 1961, '9788491051896'), ('El otoño del patriarca', 'Gabriel García Márquez', 1975, '9788497592105'), ('La tregua', 'Mario Benedetti', 1960, '9788497591780'), ('El coronel no tiene quien le escriba', 'Gabriel García Márquez', 1961, '9788497591668'), ('La fiesta del chivo', 'Mario Vargas Llosa', 2000, '9788491052243'), ('El laberinto de la soledad', 'Octavio Paz', 1950, '9788491052304'), ('La invención de Morel', 'Adolfo Bioy Casares', 1940, '9788491052119'), ('Aura', 'Carlos Fuentes', 1962, '9788491052282'), ('Los pasos perdidos', 'Alejo Carpentier', 1953, '9788491052374'), ('La hojarasca', 'Gabriel García Márquez', 1955, '9788491052456'); ``` ### Préstamos (20 registros) Se asume que los identificadores (id_usuario y id_libro) corresponden a los registros insertados previamente. ```sql INSERT INTO prestamos (id_usuario, id_libro, fecha_prestamo, fecha_devolucion) VALUES (1, 1, '2025-03-01', '2025-03-15'), (2, 2, '2025-03-02', '2025-03-16'), (3, 3, '2025-03-03', '2025-03-17'), (4, 4, '2025-03-04', '2025-03-18'), (5, 5, '2025-03-05', '2025-03-19'), (6, 6, '2025-03-06', '2025-03-20'), (7, 7, '2025-03-07', '2025-03-21'), (8, 8, '2025-03-08', '2025-03-22'), (9, 9, '2025-03-09', '2025-03-23'), (10, 10, '2025-03-10', '2025-03-24'), (11, 11, '2025-03-11', '2025-03-25'), (12, 12, '2025-03-12', '2025-03-26'), (13, 13, '2025-03-13', '2025-03-27'), (14, 14, '2025-03-14', '2025-03-28'), (15, 15, '2025-03-15', '2025-03-29'), (16, 16, '2025-03-16', '2025-03-30'), (17, 17, '2025-03-17', '2025-03-31'), (18, 18, '2025-03-18', '2025-04-01'), (19, 19, '2025-03-19', '2025-04-02'), (20, 20, '2025-03-20', '2025-04-03'); ``` ## 3. Manipulación de registros ### Actualizar registros Ejemplo: Marcar un libro como no disponible al ser prestado ```sql UPDATE Libros SET disponible = FALSE WHERE id_libro = 1; ``` Ejemplo: Cambiar la fecha de devolución de un préstamo ```sql UPDATE Prestamos SET fecha_devolucion = '2024-03-20' WHERE id_prestamo = 1; ``` ### Eliminar registros Ejemplo: Eliminar un préstamo ```sql SELETE FROM Prestamos WHERE id_prestamo = 1; ``` # Sesión 4 del curso: # Inicio a las consultas con SQL ## Consulta de ejemplo explicada ```sql SELECT i.nombre, COUNT(inv.id_investigador) AS num_investigadores FROM instituciones i LEFT JOIN investigadores inv ON i.id_institucion = inv.id_institucion GROUP BY i.id_institucion ORDER BY num_investigadores DESC; ``` ## 1️⃣ Uso de Alias en las Tablas 📌 En SQL, podemos asignar un alias a una tabla con una **letra o abreviatura** para hacer más legible la consulta. | Tabla | Alias usado | Motivo | |-------|-------------|--------| | `instituciones` | `i` | Es más corto y fácil de escribir. | | `investigadores` | `inv` | Hace referencia a la tabla de investigadores. | 🔹 **Cómo se asigna un alias**: ```sql FROM instituciones i ``` Aquí, `i` es el alias de la tabla `instituciones`. Ahora, cada vez que queramos referirnos a esa tabla, solo usamos `i`. ## 2️⃣ SELECT: Qué datos queremos ver ```sql SELECT i.nombre, COUNT(inv.id_investigador) AS num_investigadores ``` 📌 **Explicación**: * `i.nombre` → Selecciona el nombre de la institución (`i` es el alias de `instituciones`). * `COUNT(inv.id_investigador) AS num_investigadores` → Cuenta cuántos investigadores (`id_investigador`) hay en cada institución y lo muestra con el alias `num_investigadores`. ## 3️⃣ FROM: De qué tablas obtenemos los datos ```sql FROM instituciones i ``` 📌 **Explicación**: * Tomamos los datos desde la tabla `instituciones`, usando el alias `i`. ## 4️⃣ JOIN: Relacionamos las tablas ```sql LEFT JOIN investigadores inv ON i.id_institucion = inv.id_institucion ``` 📌 **Explicación**: * Unimos (`JOIN`) la tabla `investigadores` (`inv`). * La condición `ON i.id_institucion = inv.id_institucion` conecta ambas tablas. * **LEFT JOIN** → Muestra todas las instituciones, incluso si no tienen investigadores. ## 5️⃣ GROUP BY: Agrupamos por institución ```sql GROUP BY i.id_institucion ``` 📌 **Explicación**: * Agrupa los resultados por `id_institucion` para contar los investigadores de cada institución. ## 6️⃣ ORDER BY: Ordenamos los resultados ```sql ORDER BY num_investigadores DESC; ``` 📌 **Explicación**: * `ORDER BY num_investigadores` → Ordena por el número de investigadores. * `DESC` → Orden descendente (de mayor a menor). ## 💡 Ejemplo de Datos y Resultado **instituciones** (Tabla `i`) | id_institucion | nombre | |----------------|--------| | 1 | MIT | | 2 | Harvard | | 3 | Stanford | **investigadores** (Tabla `inv`) 🔹 **Resultado de la consulta**: | nombre | num_investigadores | |--------|-------------------| | MIT | 1 | | Harvard | 1 | | Stanford | 0 | ## ✅ Resumen Esta consulta cuenta el número de investigadores por institución y ordena los resultados de mayor a menor, mostrando todas las instituciones aunque no tengan investigadores asociados. ## Consultas básicas con SELECT El comando SELECT se usa para consultar datos almacenados en una base de datos. A continuación, se explican algunos usos básicos: Las siguentes consultas básicas la sharemos usando la base de datos Préstamos creada en la sesión anterior. ### Seleccionar todos los registros de una tabla: ```sql SELECT * FROM Usuarios; ``` ### Seleccionar columnas específicas: ```sql SELECT nombre FROM Usuarios; ``` ### Filtrar registros con WHERE: La cláusula WHERE se utiliza para filtrar registros que cumplen con una condición específica. ```sql SELECT * FROM Libros WHERE disponible = TRUE; ``` ### Ordenar resultados con ORDER BY: ```sql SELECT * FROM Libros ORDER BY titulo ASC; ``` ### Limitar resultados con LIMIT: La cláusula LIMIT restringe el número de filas devueltas por una consulta. Es útil para trabajar con grandes conjuntos de datos o para obtener los primeros N registros. ```sql SELECT * FROM Libros ORDER BY anio_publicacion DESC LIMIT 5; ``` ### Filtrar con patrones usando LIKE: El operador LIKE se utiliza en la cláusula WHERE para buscar un patrón específico en una columna. - % representa cero, uno o múltiples caracteres - _ representa un único carácter ```sql SELECT * FROM Usuarios WHERE correo LIKE '%@biblioteca.com'; ``` ### Contar registros con COUNT: ```sql SELECT COUNT(*) AS total_usuarios FROM Usuarios; ``` ### Obtener valores máximos y mínimos: ```sql SELECT MAX(fecha_prestamo) FROM Prestamos; SELECT MIN(fecha_prestamo) FROM Prestamos; ``` ### Agrupar resultados con GROUP BY: La cláusula GROUP BY agrupa filas que tienen los mismos valores en columnas especificadas. Se usa generalmente con funciones de agregación como COUNT, MAX, MIN, SUM, AVG. ```sql SELECT autor, COUNT(*) AS numero_libros FROM Libros GROUP BY autor; ``` ### Filtrar grupos con HAVING: La cláusula HAVING se usa para filtrar resultados después de aplicar GROUP BY, normalmente con funciones de agregación. ```sql SELECT autor, COUNT(*) AS numero_libros FROM Libros GROUP BY autor HAVING COUNT(*) > 1; ``` ## 5. Consultas con SELECT (Caso práctico) ### Consultar todos los usuarios ```sql SELECT * FROM Usuarios; ``` ### Listar todos los libros disponibles ```sql SELECT * FROM Libros WHERE disponible = TRUE; ``` ### Obtener los títulos de todos los libros ```sql SELECT titulo FROM Libros; ``` ### Consultar los nombres de todos los usuarios registrados ```sql SELECT nombre FROM Usuarios; ``` ### Obtener los préstamos realizados sin mostrar las claves foráneas ```sql SELECT id_prestamo, fecha_prestamo, fecha_devolucion FROM Prestamos; ``` ### Contar cuántos libros hay en la biblioteca ```sql SELECT COUNT(*) AS total_libros FROM Libros; ``` ### Contar cuántos préstamos se han registrado ```sql SELECT COUNT(*) AS total_prestamos FROM Prestamos; ``` ### Obtener los libros prestados (solo IDs) ```sql SELECT id_libro FROM Prestamos; ``` ### Consultar las fechas de préstamo de un usuario específico por su ID ```sql SELECT fecha_prestamo FROM Prestamos WHERE id_usuario = 1; ``` ### Obtener la fecha más reciente de préstamo registrado ```sql SELECT MAX(fecha_prestamo) AS ultima_fecha_prestamo FROM Prestamos; ``` ### Obtener la fecha más antigua de préstamo registrado ```sql SELECT MIN(fecha_prestamo) AS primera_fecha_prestamo FROM Prestamos; ``` ## 6. Ejercicios prácticos 1. Crear una consulta que muestre los préstamos con fecha de devolución posterior al 25 de marzo de 2025 2. Escribir una consulta que muestre los títulos de libros ordenados alfabéticamente 3. Crear una consulta que cuente cuántos libros hay de cada autor 4. Mostrar el libro más antiguo y el más reciente según su año de publicación 5. Contar cuántos usuarios tienen un correo que termina en '@biblioteca.com' ## 7. Respuestas a ejercicios prácticos ### 1. Consulta para mostrar préstamos con fecha de devolución posterior al 25 de marzo de 2025 ```sql SELECT id_prestamo, id_usuario, id_libro, fecha_prestamo, fecha_devolucion FROM Prestamos WHERE fecha_devolucion > '2025-03-25'; ``` ### 2. Consulta para mostrar títulos de libros ordenados alfabéticamente ```sql SELECT titulo FROM Libros ORDER BY titulo ASC; ``` ### 3. Consulta para contar cuántos libros hay de cada autor ```sql SELECT autor, COUNT(*) AS cantidad_libros FROM Libros GROUP BY autor; ``` ### 4. Consulta para mostrar el libro más antiguo y el más reciente ```sql -- Libro más antiguo SELECT titulo, autor, anio_publicacion FROM Libros ORDER BY anio_publicacion ASC LIMIT 1; -- Libro más reciente SELECT titulo, autor, anio_publicacion FROM Libros ORDER BY anio_publicacion DESC LIMIT 1; ``` ### 5. Consulta para contar usuarios con correo que termina en '@biblioteca.com' ```sql SELECT COUNT(*) AS total_usuarios_biblioteca FROM Usuarios WHERE correo LIKE '%@biblioteca.com'; ``` ## Consultas avanzadas - Dominar consultas SQL avanzadas con funciones de agregación - Comprender y aplicar diferentes tipos de JOIN - Utilizar subconsultas (subqueries) para resolver problemas complejos - Implementar vistas (VIEWS) para simplificar consultas complejas - Trabajar con consultas que involucran múltiples tablas relacionadas ## Fundamentos conceptuales ### Funciones de agregación Las funciones de agregación son operaciones que actúan sobre un conjunto de valores y devuelven un único resultado. Estas funciones son fundamentales para el análisis estadístico y el resumen de datos. A diferencia de las consultas básicas que muestran filas individuales, las funciones de agregación permiten obtener información consolidada de múltiples registros. **Características principales:** - Operan sobre conjuntos de registros, no sobre registros individuales - Suelen utilizarse con la cláusula GROUP BY para agrupar registros según criterios específicos - Pueden filtrarse los resultados agrupados mediante la cláusula HAVING - Son la base para la generación de informes y dashboards analíticos ### JOIN: Concepto y tipos Los JOIN son operaciones fundamentales en bases de datos relacionales que permiten combinar datos de diferentes tablas basándose en las relaciones entre ellas. Entender los diferentes tipos de JOIN es esencial para extraer información significativa de bases de datos con múltiples tablas relacionadas. **Fundamentos de JOIN:** - Establece una relación temporal entre tablas durante la ejecución de una consulta - Se basa en condiciones de coincidencia especificadas después de ON - Permite acceder a datos relacionados sin necesidad de múltiples consultas - Existen varios tipos según las necesidades de inclusión de datos **Tipos principales:** - **INNER JOIN**: Solo incluye filas donde existe coincidencia en ambas tablas - **LEFT JOIN**: Incluye todas las filas de la tabla izquierda y las coincidencias de la derecha - **RIGHT JOIN**: Incluye todas las filas de la tabla derecha y las coincidencias de la izquierda - **FULL JOIN**: Incluye todas las filas de ambas tablas (en MySQL se simula con UNION) - **CROSS JOIN**: Genera el producto cartesiano de ambas tablas (todas las combinaciones posibles) ### Subconsultas (Subqueries) Las subconsultas son consultas anidadas dentro de otras consultas, permitiendo realizar operaciones más complejas que no serían posibles con una única consulta simple. Funcionan como bloques de construcción para consultas más elaboradas. **Características de las subconsultas:** - Pueden aparecer en cláusulas SELECT, FROM, WHERE o HAVING - Pueden devolver un único valor, una sola columna, una sola fila o una tabla completa - Pueden ser correlacionadas (hacen referencia a columnas de la consulta externa) o no correlacionadas - Permiten comparaciones con operadores como IN, NOT IN, EXISTS, NOT EXISTS, ANY, ALL ### Vistas (VIEWS) Las vistas son consultas SQL almacenadas que funcionan como tablas virtuales. No almacenan datos físicamente, sino que contienen una consulta predefinida que se ejecuta cada vez que se accede a la vista. **Ventajas de las vistas:** - Simplifican consultas complejas al encapsularlas bajo un nombre - Proporcionan una capa de abstracción que oculta la complejidad de las tablas subyacentes - Mejoran la seguridad al permitir acceso selectivo a datos específicos - Mantienen consistencia en las consultas utilizadas frecuentemente ### Cláusulas avanzadas y funciones especiales SQL ofrece diversas cláusulas y funciones que permiten realizar operaciones avanzadas sobre los datos: **CASE WHEN**: Permite implementar lógica condicional en las consultas SQL, similar a una estructura if-then-else en lenguajes de programación. **Window Functions**: Permiten realizar cálculos a través de conjuntos de filas relacionadas con la fila actual, sin agrupar las filas en un único resultado. **Funciones de fecha y tiempo**: Facilitan operaciones con fechas como cálculo de diferencias, extracción de componentes (año, mes, día) y formateo. **Funciones de texto**: Permiten manipular cadenas de texto mediante concatenación, extracción de subcadenas, cambio de mayúsculas/minúsculas, etc. ## Caso práctico: Sistema de Gestión de Producción Científica En esta sesión trabajaremos con una base de datos que modela la producción científica de investigadores en diferentes instituciones. Esta base de datos permitirá gestionar investigadores, publicaciones, proyectos de investigación, colaboraciones y métricas de impacto. ### Estructura de la base de datos Nuestra base de datos `produccion_cientifica` constará de las siguientes tablas: 1. `instituciones`: Almacena información sobre universidades y centros de investigación 2. `investigadores`: Datos de los investigadores 3. `publicaciones`: Artículos y papers publicados 4. `proyectos`: Proyectos de investigación 5. `financiamiento`: Fuentes de financiamiento para proyectos 6. `colaboraciones`: Relación entre investigadores y publicaciones 7. `citas`: Registro de citas entre publicaciones ## Creación de la base de datos Comenzaremos creando nuestra base de datos y las tablas necesarias: ```sql -- Crear la base de datos CREATE DATABASE produccion_cientifica; -- Usar la base de datos USE produccion_cientifica; -- Crear tabla de instituciones CREATE TABLE instituciones ( id_institucion INT PRIMARY KEY AUTO_INCREMENT, nombre VARCHAR(150) NOT NULL, pais VARCHAR(50) NOT NULL, tipo ENUM('Universidad', 'Centro de Investigación', 'Laboratorio', 'Organismo Público') NOT NULL, fecha_fundacion DATE, ranking INT ); -- Crear tabla de investigadores CREATE TABLE investigadores ( id_investigador INT PRIMARY KEY AUTO_INCREMENT, nombre VARCHAR(50) NOT NULL, apellido VARCHAR(50) NOT NULL, id_institucion INT, departamento VARCHAR(100), h_index INT DEFAULT 0, area_investigacion VARCHAR(100), FOREIGN KEY (id_institucion) REFERENCES instituciones(id_institucion) ); -- Crear tabla de publicaciones CREATE TABLE publicaciones ( id_publicacion INT PRIMARY KEY AUTO_INCREMENT, titulo VARCHAR(255) NOT NULL, fecha DATE NOT NULL, revista VARCHAR(150), doi VARCHAR(50) UNIQUE, factor_impacto DECIMAL(5,2) ); -- Crear tabla de financiamiento CREATE TABLE financiamiento ( id_financ INT PRIMARY KEY AUTO_INCREMENT, nombre VARCHAR(150) NOT NULL, tipo ENUM('Público', 'Privado', 'Internacional', 'Mixto') NOT NULL, presupuesto DECIMAL(12,2), pais_origen VARCHAR(50) ); -- Crear tabla de proyectos CREATE TABLE proyectos ( id_proyecto INT PRIMARY KEY AUTO_INCREMENT, titulo VARCHAR(255) NOT NULL, fecha_inicio DATE NOT NULL, fecha_fin DATE, id_investigador INT, id_financ INT, estado ENUM('Activo', 'Finalizado', 'Suspendido', 'Propuesto') DEFAULT 'Propuesto', FOREIGN KEY (id_investigador) REFERENCES investigadores(id_investigador), FOREIGN KEY (id_financ) REFERENCES financiamiento(id_financ) ); -- Crear tabla de colaboraciones (relación muchos a muchos entre investigadores y publicaciones) CREATE TABLE colaboraciones ( id_colaboracion INT PRIMARY KEY AUTO_INCREMENT, id_investigador INT, id_publicacion INT, rol ENUM('Autor principal', 'Co-autor', 'Editor', 'Revisor') NOT NULL, orden_autor INT, FOREIGN KEY (id_investigador) REFERENCES investigadores(id_investigador), FOREIGN KEY (id_publicacion) REFERENCES publicaciones(id_publicacion) ); -- Crear tabla de citas CREATE TABLE citas ( id_cita INT PRIMARY KEY AUTO_INCREMENT, id_pub_citante INT, id_pub_citada INT, fecha_cita DATE, FOREIGN KEY (id_pub_citante) REFERENCES publicaciones(id_publicacion), FOREIGN KEY (id_pub_citada) REFERENCES publicaciones(id_publicacion) ); ``` ## Insertando datos de ejemplo Ahora vamos a insertar algunos datos de ejemplo para trabajar con consultas: ```sql -- Insertar instituciones INSERT INTO instituciones (nombre, pais, tipo, fecha_fundacion, ranking) VALUES ('Universidad Complutense de Madrid', 'España', 'Universidad', '1499-01-01', 25), ('CSIC', 'España', 'Centro de Investigación', '1939-11-24', 5), ('Max Planck Institute', 'Alemania', 'Centro de Investigación', '1911-01-11', 3), ('Harvard University', 'Estados Unidos', 'Universidad', '1636-09-08', 1), ('CERN', 'Suiza', 'Laboratorio', '1954-09-29', 2); -- Insertar investigadores INSERT INTO investigadores (nombre, apellido, id_institucion, departamento, h_index, area_investigacion) VALUES ('María', 'González', 1, 'Biología Molecular', 25, 'Genómica'), ('Juan', 'Martínez', 2, 'Física Teórica', 18, 'Física Cuántica'), ('Ana', 'López', 1, 'Ciencias de la Computación', 15, 'Inteligencia Artificial'), ('Pedro', 'Sánchez', 3, 'Neurociencia', 32, 'Neurobiología'), ('Laura', 'Fernández', 4, 'Matemáticas', 21, 'Teoría de Números'), ('Carlos', 'Rodríguez', 5, 'Física de Partículas', 40, 'Física de Altas Energías'), ('Elena', 'Díaz', 2, 'Química Orgánica', 16, 'Catálisis'), ('Miguel', 'Torres', 4, 'Bioinformática', 22, 'Genómica Computacional'); -- Insertar publicaciones INSERT INTO publicaciones (titulo, fecha, revista, doi, factor_impacto) VALUES ('Advances in Quantum Computing', '2022-05-15', 'Nature', 'doi:10.1038/s41586-022-04789-9', 42.78), ('Novel Catalysts for Organic Synthesis', '2021-11-03', 'Science', 'doi:10.1126/science.abc1234', 38.70), ('Neural Networks in Drug Discovery', '2023-01-20', 'Cell', 'doi:10.1016/j.cell.2023.01.003', 36.32), ('Climate Change Impact on Biodiversity', '2022-08-14', 'PLOS ONE', 'doi:10.1371/journal.pone.0012345', 3.24), ('New Approaches in Number Theory', '2022-04-10', 'Journal of Mathematics', 'doi:10.1007/s00222-022-12345', 2.95), ('Particle Collision at High Energy', '2023-02-28', 'Physical Review Letters', 'doi:10.1103/PhysRevLett.123.456789', 9.16), ('Protein Folding Algorithms', '2022-09-05', 'Bioinformatics', 'doi:10.1093/bioinformatics/btab123', 6.93), ('Genomic Analysis of Rare Diseases', '2023-03-15', 'Genome Research', 'doi:10.1101/gr.123456.123', 11.09); -- Insertar financiamiento INSERT INTO financiamiento (nombre, tipo, presupuesto, pais_origen) VALUES ('Ministerio de Ciencia e Innovación', 'Público', 1500000.00, 'España'), ('European Research Council', 'Internacional', 2500000.00, 'Unión Europea'), ('Fundación Bill & Melinda Gates', 'Privado', 3000000.00, 'Estados Unidos'), ('National Science Foundation', 'Público', 1200000.00, 'Estados Unidos'), ('BBVA Foundation', 'Privado', 800000.00, 'España'); -- Insertar proyectos INSERT INTO proyectos (titulo, fecha_inicio, fecha_fin, id_investigador, id_financ, estado) VALUES ('Computación Cuántica Aplicada', '2021-01-01', '2023-12-31', 2, 2, 'Activo'), ('Nuevos Métodos en Inteligencia Artificial', '2022-03-01', '2025-02-28', 3, 1, 'Activo'), ('Estudio de Partículas Fundamentales', '2020-06-01', '2023-05-31', 6, 2, 'Activo'), ('Genómica y Enfermedades Raras', '2021-09-01', '2024-08-31', 1, 3, 'Activo'), ('Matemáticas Discretas', '2022-01-01', '2024-12-31', 5, 4, 'Activo'), ('Catalizadores Sostenibles', '2021-04-01', '2023-03-31', 7, 5, 'Finalizado'), ('Bioinformática Estructural', '2022-07-01', '2025-06-30', 8, 4, 'Activo'); -- Insertar colaboraciones INSERT INTO colaboraciones (id_investigador, id_publicacion, rol, orden_autor) VALUES (2, 1, 'Autor principal', 1), (3, 1, 'Co-autor', 2), (7, 2, 'Autor principal', 1), (3, 3, 'Autor principal', 1), (8, 3, 'Co-autor', 2), (1, 4, 'Co-autor', 2), (4, 4, 'Autor principal', 1), (5, 5, 'Autor principal', 1), (6, 6, 'Autor principal', 1), (2, 6, 'Co-autor', 2), (8, 7, 'Autor principal', 1), (1, 8, 'Autor principal', 1), (4, 8, 'Co-autor', 2), (8, 8, 'Co-autor', 3); -- Insertar citas INSERT INTO citas (id_pub_citante, id_pub_citada, fecha_cita) VALUES (3, 1, '2023-01-20'), (3, 7, '2023-01-20'), (4, 2, '2022-08-14'), (6, 1, '2023-02-28'), (7, 3, '2022-09-05'), (8, 1, '2023-03-15'), (8, 3, '2023-03-15'), (8, 7, '2023-03-15'), (5, 2, '2022-04-10'), (6, 5, '2023-02-28'); ``` ## Consultas avanzadas con funciones de agregación Las funciones de agregación permiten realizar cálculos sobre conjuntos de registros. Las más comunes son: - `COUNT()`: Cuenta el número de filas - `SUM()`: Suma los valores de una columna - `AVG()`: Calcula el promedio de los valores de una columna - `MAX()`: Encuentra el valor máximo - `MIN()`: Encuentra el valor mínimo ### Ejemplos: #### 1. Contar el número de investigadores por institución: ```sql SELECT i.nombre, COUNT(inv.id_investigador) AS num_investigadores FROM instituciones i LEFT JOIN investigadores inv ON i.id_institucion = inv.id_institucion GROUP BY i.id_institucion ORDER BY num_investigadores DESC; ``` #### 2. Calcular el promedio de factor de impacto por revista: ```sql SELECT revista, AVG(factor_impacto) AS impacto_promedio FROM publicaciones GROUP BY revista HAVING impacto_promedio > 5 ORDER BY impacto_promedio DESC; ``` Nota: La cláusula `HAVING` se utiliza para filtrar después de aplicar funciones de agregación, mientras que `WHERE` filtra antes de agrupar. #### 3. Encontrar el h-index máximo por área de investigación: ```sql SELECT area_investigacion, MAX(h_index) AS max_h_index FROM investigadores GROUP BY area_investigacion ORDER BY max_h_index DESC; ``` ## Tipos de JOIN Los JOIN permiten combinar filas de diferentes tablas basándose en una condición de relación entre ellas. Veamos los diferentes tipos: ### INNER JOIN Devuelve registros cuando hay coincidencias en ambas tablas. ```sql -- Obtener publicaciones con sus autores principales SELECT p.titulo, i.nombre, i.apellido FROM publicaciones p INNER JOIN colaboraciones c ON p.id_publicacion = c.id_publicacion INNER JOIN investigadores i ON c.id_investigador = i.id_investigador WHERE c.rol = 'Autor principal'; ``` ### LEFT JOIN Devuelve todos los registros de la tabla izquierda y los coincidentes de la tabla derecha. ```sql -- Listar todos los investigadores y sus proyectos (incluso los que no tienen proyectos) SELECT i.nombre, i.apellido, p.titulo AS proyecto FROM investigadores i LEFT JOIN proyectos p ON i.id_investigador = p.id_investigador; ``` ### RIGHT JOIN Devuelve todos los registros de la tabla derecha y los coincidentes de la tabla izquierda. ```sql -- Listar todas las fuentes de financiamiento y los proyectos que financian (incluso las que no financian ningún proyecto) SELECT f.nombre AS financiador, p.titulo AS proyecto FROM proyectos p RIGHT JOIN financiamiento f ON p.id_financ = f.id_financ; ``` ### FULL JOIN (MySQL usa UNION de LEFT y RIGHT JOIN) MySQL no implementa FULL JOIN directamente, pero se puede simular con UNION: ```sql -- Listar todas las instituciones y todos los investigadores (relación completa) SELECT i.nombre AS institucion, inv.nombre, inv.apellido FROM instituciones i LEFT JOIN investigadores inv ON i.id_institucion = inv.id_institucion UNION SELECT i.nombre AS institucion, inv.nombre, inv.apellido FROM instituciones i RIGHT JOIN investigadores inv ON i.id_institucion = inv.id_institucion; ``` ### Otro ejemplo con UNION ```sql -- Listar publicaciones de físicos y matemáticos SELECT p.titulo, i.nombre, i.apellido, 'Física' AS area FROM publicaciones p JOIN colaboraciones c ON p.id_publicacion = c.id_publicacion JOIN investigadores i ON c.id_investigador = i.id_investigador WHERE i.area_investigacion LIKE '%Física%' UNION SELECT p.titulo, i.nombre, i.apellido, 'Matemáticas' AS area FROM publicaciones p JOIN colaboraciones c ON p.id_publicacion = c.id_publicacion JOIN investigadores i ON c.id_investigador = i.id_investigador WHERE i.area_investigacion = 'Matemáticas' ORDER BY area, titulo; ``` ¿Otra forma de simplificar esta consulta ? ``` SELECT p.titulo, i.nombre, i.apellido, IF(i.area_investigacion LIKE '%Física%', 'Física', 'Matemáticas') AS area FROM publicaciones p JOIN colaboraciones c ON p.id_publicacion = c.id_publicacion JOIN investigadores i ON c.id_investigador = i.id_investigador WHERE i.area_investigacion LIKE '%Física%' OR i.area_investigacion = 'Matemáticas' ORDER BY area, titulo;* ``` ## Subconsultas (Subqueries) Las subconsultas son consultas anidadas dentro de otras consultas. Permiten realizar operaciones más complejas. ### Subconsulta en la cláusula WHERE: ```sql -- Investigadores con h-index superior al promedio SELECT nombre, apellido, h_index FROM investigadores WHERE h_index > (SELECT AVG(h_index) FROM investigadores); ``` ### Subconsulta en la cláusula FROM: ```sql -- Publicaciones que han recibido más citas que el promedio SELECT p.titulo, citas_recibidas FROM ( SELECT id_pub_citada, COUNT(*) AS citas_recibidas FROM citas GROUP BY id_pub_citada ) AS conteo_citas JOIN publicaciones p ON conteo_citas.id_pub_citada = p.id_publicacion WHERE citas_recibidas > ( SELECT AVG(citas_count) FROM ( SELECT COUNT(*) AS citas_count FROM citas GROUP BY id_pub_citada ) AS promedio ); ``` ### Subconsulta con EXISTS: ```sql -- Investigadores que han publicado en Nature SELECT i.nombre, i.apellido FROM investigadores i WHERE EXISTS ( SELECT 1 FROM colaboraciones c JOIN publicaciones p ON c.id_publicacion = p.id_publicacion WHERE c.id_investigador = i.id_investigador AND p.revista = 'Nature' ); ``` ## Otros comandos avanzados ### CASE WHEN para lógica condicional: ```sql -- Clasificar instituciones por ranking SELECT nombre, ranking, CASE WHEN ranking <= 5 THEN 'Top 5 mundial' WHEN ranking <= 20 THEN 'Top 20 mundial' ELSE 'Ranking superior a 20' END AS categoria_ranking FROM instituciones ORDER BY ranking; ``` ``` ### Funciones de fecha y tiempo: ```sql -- Calcular duración de proyectos en meses SELECT titulo, fecha_inicio, fecha_fin, TIMESTAMPDIFF(MONTH, fecha_inicio, CASE WHEN fecha_fin IS NULL THEN CURDATE() ELSE fecha_fin END ) AS duracion_meses FROM proyectos ORDER BY duracion_meses DESC; ``` ### Consultas con Window Functions (desde MySQL 8): ```sql -- Ranking de investigadores por h-index dentro de cada institución SELECT i.nombre AS investigador, inst.nombre AS institucion, i.h_index, RANK() OVER (PARTITION BY i.id_institucion ORDER BY i.h_index DESC ``` ## Ejercicios prácticos ### Ejercicio 1: Consultas con JOIN Encuentra todos los investigadores que han colaborado en publicaciones con un factor de impacto superior a 10. ### Ejercicio 2: Subconsultas Lista las instituciones que tienen al menos 3 investigadores cuyo h-index sea superior a la media. ### Ejercicio 3: Funciones de agregación Calcula el número total de citas recibidas por publicación y ordénalas de mayor a menor. ### Ejercicio 4: CASE WHEN Clasifica a los investigadores según su h-index: 'Junior' (<15), 'Senior' (15-25), 'Experto' (>25). ### Ejercicio 5: Vistas Crea una vista que muestre el top 3 de investigadores por área de investigación según su h-index. ## Soluciones a los ejercicios ### Solución 1: ```sql SELECT DISTINCT i.nombre, i.apellido, i.area_investigacion FROM investigadores i JOIN colaboraciones c ON i.id_investigador = c.id_investigador JOIN publicaciones p ON c.id_publicacion = p.id_publicacion WHERE p.factor_impacto > 10 ORDER BY i.apellido, i.nombre; ``` ### Solución 2: ```sql SELECT i.nombre, i.pais, COUNT(inv.id_investigador) AS num_investigadores FROM instituciones i JOIN investigadores inv ON i.id_institucion = inv.id_institucion WHERE inv.h_index > (SELECT AVG(h_index) FROM investigadores) GROUP BY i.id_institucion HAVING num_investigadores >= 3; ``` ### Solución 3: ```sql SELECT p.titulo, p.doi, COUNT(c.id_cita) AS total_citas FROM publicaciones p LEFT JOIN citas c ON p.id_publicacion = c.id_pub_citada GROUP BY p.id_publicacion ORDER BY total_citas DESC; ``` ### Solución 4: ```sql SELECT nombre, apellido, h_index, CASE WHEN h_index < 15 THEN 'Junior' WHEN h_index BETWEEN 15 AND 25 THEN 'Senior' ELSE 'Experto' END AS categoria FROM investigadores ORDER BY h_index DESC; ``` ### Solución 5: ```sql CREATE VIEW top_investigadores_por_area AS SELECT * FROM ( SELECT nombre, apellido, area_investigacion, h_index, RANK() OVER (PARTITION BY area_investigacion ORDER BY h_index DESC) AS ranking FROM investigadores ) ranked WHERE ranking <= 3; -- Consultar la vista SELECT * FROM top_investigadores_por_area; ``` ## Conclusiones y buenas prácticas - Utiliza JOIN adecuadamente según tus necesidades (INNER, LEFT, RIGHT, FULL) - Las subconsultas permiten resolver problemas complejos, pero pueden afectar el rendimiento - Las vistas simplifican el acceso a datos complejos - Utiliza funciones de agregación combinadas con GROUP BY para análisis estadístico - Optimiza tus consultas para mejorar el rendimiento: - Usa índices apropiados - Escribe consultas que minimicen el número de filas procesadas - Utiliza EXPLAIN para analizar la ejecución de consultas # Sesión 5. Optimización, Seguridad y Documentación en MySQL para Bibliotecarios ## 5.1 OPTIMIZACIÓN DE CONSULTAS ### Conceptos Fundamentales La optimización de consultas mejora el rendimiento de nuestra base de datos, haciéndola más rápida y eficiente, especialmente cuando crece: 1. **Índices**: Son como el índice de un libro - permiten encontrar información rápidamente sin revisar toda la base de datos. 2. **EXPLAIN**: Herramienta para entender cómo MySQL ejecuta las consultas. 3. **Consultas eficientes**: Solicitar solo los datos necesarios para ahorrar recursos. 4. **Mantenimiento de tablas**: Limpieza periódica para mantener el rendimiento. ### Ejemplos con Base de Datos de Producción Científica #### Creación y Uso de Índices con phpMyAdmin 1. **Ver índices existentes**: - Ingresa a phpMyAdmin - Selecciona la base de datos "produccion_cientifica" - Haz clic en la tabla "articulos_cientificos" - Ve a la pestaña "Estructura" - Los índices existentes aparecerán en la sección "Índices" 2. **Crear un nuevo índice**: - En la misma pestaña "Estructura" - Desplázate hacia abajo hasta encontrar "Crear un índice" - Selecciona el campo "anio_publicacion" - Elige "INDEX" en el menú desplegable - Escribe "idx_anio_publicacion" como nombre - Haz clic en "Guardar" #### Uso de EXPLAIN para Analizar Consultas 1. **Analizar una consulta**: - Ve a la pestaña "SQL" en phpMyAdmin - Escribe la siguiente consulta: ```sql EXPLAIN SELECT * FROM articulos_cientificos WHERE anio_publicacion > 2020; ``` - Haz clic en "Ejecutar" - Observa la tabla de resultados que muestra cómo MySQL ejecuta la consulta #### Optimización de Consultas 1. **Seleccionar solo columnas necesarias**: - Ve a la pestaña "SQL" - En lugar de: ```sql SELECT * FROM articulos_cientificos WHERE anio_publicacion = 2023; ``` - Escribe: ```sql SELECT id_articulo, titulo, doi FROM articulos_cientificos WHERE anio_publicacion = 2023; ``` 2. **Limitar resultados**: - Añade LIMIT al final de tus consultas: ```sql SELECT titulo, fecha_publicacion FROM articulos_cientificos ORDER BY fecha_publicacion DESC LIMIT 100; ``` #### Mantenimiento de Tablas con phpMyAdmin 1. **Optimizar una tabla**: - Selecciona la tabla "articulos_cientificos" en phpMyAdmin - Ve a la pestaña "Operaciones" - Busca la sección "Mantenimiento de tabla" - Haz clic en "Optimizar tabla" ## 5.2: SEGURIDAD DE DATOS Y USUARIOS ### Conceptos Fundamentales La seguridad es crucial para proteger la información: 1. **Usuarios**: Cuentas individuales para acceder a la base de datos. 2. **Privilegios**: Permisos específicos para cada usuario según su rol. 3. **Respaldos**: Copias de seguridad para prevenir pérdida de datos. 4. **Encriptación**: Protección de información sensible. ### Ejemplos con Base de Datos de Producción Científica usando phpMyAdmin #### Gestión de Usuarios 1. **Crear un nuevo usuario**: - En phpMyAdmin, haz clic en "Cuentas de usuario" en el menú superior - Haz clic en "Agregar cuenta de usuario" - Completa los siguientes campos: - Nombre de usuario: `bibliotecario` - Nombre de host: `192.168.1.%` (para acceso desde la red local) - Contraseña: Ingresa una contraseña segura - Confirma la contraseña #### Asignación de Privilegios 1. **Asignar privilegios a un usuario**: - Durante la creación del usuario o editando un usuario existente - En la sección "Privilegios de base de datos" - Selecciona "produccion_cientifica" en el menú desplegable - Para un bibliotecario, marca: - SELECT (en todas las tablas) - INSERT, UPDATE (solo en tablas específicas como "prestamos") - Haz clic en "Continuar" #### Gestión de Respaldos 1. **Crear un respaldo**: - Selecciona la base de datos "produccion_cientifica" - Haz clic en la pestaña "Exportar" - Deja seleccionada la opción "Exportación rápida" - Elige formato "SQL" - Marca "Añadir DROP TABLE" - Haz clic en "Continuar" - Guarda el archivo en tu computadora 2. **Restaurar un respaldo**: - Selecciona la base de datos "produccion_cientifica" - Haz clic en la pestaña "Importar" - Haz clic en "Examinar" y selecciona el archivo de respaldo (.sql) - Haz clic en "Continuar" #### Encriptación y Protección de Datos 1. **Crear tabla con campos encriptados**: - Ve a la pestaña "SQL" - Ejecuta la siguiente consulta: ```sql CREATE TABLE usuarios_sistema ( id_usuario INT PRIMARY KEY AUTO_INCREMENT, nombre VARCHAR(100), correo VARCHAR(100), contrasena_hash VARCHAR(255), fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` 2. **Insertar datos con encriptación**: - Ve a la pestaña "SQL" - Ejecuta: ```sql INSERT INTO usuarios_sistema (nombre, correo, contrasena_hash) VALUES ('Juan Pérez', 'jperez@biblioteca.org', SHA2('contraseña123', 256)); ``` ## 5.3: DICCIONARIO DE DATOS ### Conceptos Fundamentales Un diccionario de datos es como un glosario que describe la estructura y contenido de la base de datos: 1. **¿Qué es?**: Una documentación detallada de todas las tablas y campos. 2. **Componentes**: Nombres de tablas, campos, tipos de datos, restricciones. 3. **Beneficios**: Facilita entender la base de datos y entrenar a nuevos miembros del equipo. 4. **Implementación**: Puede crearse manualmente o generarse automáticamente. ### Ejemplos con Base de Datos de Producción Científica usando phpMyAdmin #### Consultar Información del Esquema 1. **Ver estructura de tablas**: - En phpMyAdmin, selecciona la base de datos "produccion_cientifica" - Haz clic en la tabla "articulos_cientificos" - La pestaña "Estructura" muestra información detallada de todos los campos 2. **Obtener detalles de una tabla específica**: - En la pestaña "SQL", ejecuta: ```sql DESCRIBE articulos_cientificos; ``` #### Crear un Diccionario de Datos Manual 1. **Crear tabla para el diccionario de datos**: - Ve a la pestaña "SQL" - Ejecuta: ```sql CREATE TABLE diccionario_datos ( id_entrada INT PRIMARY KEY AUTO_INCREMENT, nombre_tabla VARCHAR(100), nombre_campo VARCHAR(100), tipo_dato VARCHAR(50), longitud VARCHAR(50), permite_nulos ENUM('SI', 'NO'), descripcion TEXT, ejemplo_valor VARCHAR(255), fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ``` 2. **Añadir entradas al diccionario**: - Ve a la pestaña "Insertar" de la tabla "diccionario_datos" - Completa los campos para cada entrada - Haz clic en "Continuar" #### Documentación de Procedimientos Almacenados con phpMyAdmin 1. **Crear procedimiento almacenado**: - Ve a la pestaña "Rutinas" - Haz clic en "Crear nueva rutina" - Elige "PROCEDURE" como tipo - Nombre: "buscar_articulos_por_tema" - Parámetro: "IN tema VARCHAR(100)" - En el cuerpo, copia el siguiente código: ```sql BEGIN /* Descripción: Este procedimiento busca artículos que contengan el tema especificado en el título, resumen o palabras clave. */ SELECT id_articulo, titulo, anio_publicacion, doi FROM articulos_cientificos WHERE titulo LIKE CONCAT('%', tema, '%') OR resumen LIKE CONCAT('%', tema, '%') OR palabras_clave LIKE CONCAT('%', tema, '%') ORDER BY anio_publicacion DESC; END ``` - Haz clic en "Guardar" ## EJERCICIOS PRÁCTICOS ### Ejercicio 1: Optimización de Consultas **Enunciado:** Utilizando phpMyAdmin, optimiza la siguiente consulta: ```sql SELECT * FROM articulos_cientificos a JOIN autores b ON a.id_autor = b.id_autor WHERE b.institucion = 'Universidad Nacional' ORDER BY a.fecha_publicacion; ``` **Solución:** 1. Primero, crea un índice para mejorar la búsqueda: - Ve a la tabla "autores" en phpMyAdmin - En la pestaña "Estructura", crea un índice para el campo "institucion" - Nombra el índice "idx_institucion" 2. Luego, modifica la consulta para hacerla más eficiente: - En la pestaña "SQL", escribe: ```sql SELECT a.id_articulo, a.titulo, a.fecha_publicacion, b.nombre AS nombre_autor, b.institucion FROM articulos_cientificos a JOIN autores b ON a.id_autor = b.id_autor WHERE b.institucion = 'Universidad Nacional' ORDER BY a.fecha_publicacion LIMIT 1000; ``` - Ejecuta la consulta ### Ejercicio 2: Gestión de Usuarios y Privilegios **Enunciado:** Crea tres usuarios con diferentes perfiles utilizando phpMyAdmin: 1. Administrador (acceso total) 2. Catalogador (puede insertar y actualizar, pero no eliminar) 3. Consulta (solo lectura) **Solución:** 1. Accede a "Cuentas de usuario" en phpMyAdmin 2. Crea cada usuario con sus respectivos privilegios: **Para el administrador:** - Usuario: admin_biblio - Host: localhost - Privilegios: selecciona "Conceder todos los privilegios" para la base de datos "produccion_cientifica" **Para el catalogador:** - Usuario: catalogador - Host: 192.168.1.% - Privilegios para "produccion_cientifica": - SELECT (todas las tablas) - INSERT, UPDATE (tablas específicas como articulos_cientificos, autores, revistas) **Para consulta:** - Usuario: consulta - Host: % (cualquier host) - Privilegios: solo SELECT en todas las tablas ### Ejercicio 3: Crear un Diccionario de Datos Automatizado **Enunciado:** Crea un procedimiento almacenado que genere automáticamente un diccionario de datos para la base de datos de producción científica. **Solución con phpMyAdmin:** 1. Ve a la pestaña "Rutinas" 2. Haz clic en "Crear nueva rutina" 3. Configura: - Tipo: PROCEDURE - Nombre: generar_diccionario_datos - Parámetros: (ninguno) 4. En el cuerpo del procedimiento, copia este código: ```sql BEGIN -- Limpiar tabla de diccionario si existe DROP TABLE IF EXISTS diccionario_datos_auto; -- Crear tabla para almacenar el diccionario CREATE TABLE diccionario_datos_auto ( id_entrada INT PRIMARY KEY AUTO_INCREMENT, nombre_tabla VARCHAR(100), nombre_campo VARCHAR(100), tipo_dato VARCHAR(50), longitud VARCHAR(50), permite_nulos VARCHAR(3), es_clave VARCHAR(20), valor_defecto VARCHAR(255), descripcion TEXT, fecha_generacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Insertar datos de todas las tablas de la base de datos actual INSERT INTO diccionario_datos_auto ( nombre_tabla, nombre_campo, tipo_dato, longitud, permite_nulos, es_clave, valor_defecto ) SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH WHEN NUMERIC_PRECISION IS NOT NULL THEN CONCAT(NUMERIC_PRECISION, ',', NUMERIC_SCALE) ELSE 'N/A' END, IS_NULLABLE, CASE WHEN COLUMN_KEY = 'PRI' THEN 'PRIMARY KEY' WHEN COLUMN_KEY = 'UNI' THEN 'UNIQUE KEY' WHEN COLUMN_KEY = 'MUL' THEN 'FOREIGN KEY' ELSE '' END, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE(); -- Mostrar resultado SELECT * FROM diccionario_datos_auto ORDER BY nombre_tabla, es_clave DESC, nombre_campo; END ``` 5. Haz clic en "Guardar" 6. Para ejecutar el procedimiento: - Ve a la pestaña "Rutinas" - Encuentra "generar_diccionario_datos" en la lista - Haz clic en "Ejecutar" --- # Sesión 6: Automatización de Actualizaciones de Datos en MySQL Esta sesión está dedicada a los procedimientos para automatizar la actualización de datos en MySQL, desde enfoques sencillos hasta métodos más sofisticados como procesos ETL incrementales. El contenido está adaptado especialmente para bibliotecarios y gestores de bases de datos de producción científica. ## 6.1 FUNDAMENTOS DE AUTOMATIZACIÓN DE DATOS ### Conceptos Fundamentales La automatización de actualizaciones de datos permite mantener la información actualizada sin intervención manual constante: 1. **¿Qué es la automatización de datos?**: Conjunto de técnicas para actualizar, limpiar y mantener datos sin necesidad de intervención manual constante. 2. **Beneficios**: - Ahorro de tiempo - Reducción de errores humanos - Consistencia en los datos - Actualizaciones programadas y predecibles 3. **Niveles de automatización**: - Básico: Consultas programadas y scripts simples - Intermedio: Procedimientos almacenados y eventos - Avanzado: Procesos ETL y replicación ### Ejemplos con Base de Datos de Producción Científica #### Escenarios comunes de automatización en bibliotecas 1. **Actualización de catálogos** de publicaciones científicas 2. **Sincronización de datos bibliográficos** con repositorios externos 3. **Generación automática de informes** de producción científica 4. **Limpieza y normalización** de datos de autores e instituciones 5. **Detección de duplicados** en artículos científicos ## 6.2 MÉTODOS BÁSICOS DE AUTOMATIZACIÓN ### Consultas Programadas con phpMyAdmin #### Exportación Automática de Datos 1. **Guardar consultas frecuentes**: - Ve a la pestaña "SQL" en phpMyAdmin - Escribe tu consulta, por ejemplo: - ```sql SELECT p.titulo, p.doi, p.fecha, i.nombre, i.apellido FROM publicaciones p JOIN colaboraciones c ON p.id_publicacion = c.id_publicacion JOIN investigadores i ON c.id_investigador = i.id_investigador WHERE YEAR(p.fecha) = YEAR(CURDATE()) ORDER BY p.fecha DESC; ``` - Haz clic en "Guardar en favoritos" - Asigna un nombre descriptivo: "articulos_del_año_actual" - Luego recuperarlas 2. **Acceder a consultas guardadas**: - Ve al panel izquierdo en phpMyAdmin - Haz clic en "Consultas guardadas" - Selecciona "articulos_del_año_actual" ### Utilización de SQL Avanzado para Automatización #### Consultas de Actualización Condicional 1. **Actualización masiva basada en condiciones**: ```sql -- Actualizar área de investigación de publicaciones según palabras clave UPDATE publicaciones SET revista = 'Sistemas de Información' WHERE titulo LIKE '%biblioteca digital%' AND revista IS NULL; ``` 2. **Inserción condicional para evitar duplicados**: ```sql -- Insertar nueva publicación solo si no existe ya (por DOI) INSERT INTO publicaciones (titulo, doi, fecha, revista) SELECT 'Nuevos métodos de catalogación', '10.1234/ejemplo.2023', '2023-01-15', 'Journal of Library Science' FROM dual WHERE NOT EXISTS ( SELECT 1 FROM publicaciones WHERE doi = '10.1234/ejemplo.2023' ); ``` Nota: dual es una tabla especial de MySQL que se usa cuando necesitas ejecutar una consulta SELECT pero no necesitas obtener datos de ninguna tabla real 3. **Actualizaciones basadas en múltiples tablas**: ```sql -- Actualizar factor de impacto de publicaciones basado en citas UPDATE publicaciones p SET p.factor_impacto = ( SELECT COUNT(*) FROM citas WHERE id_pub_citada = p.id_publicacion ); ``` ## 6.3 AUTOMATIZACIÓN INTERMEDIA CON PROCEDIMIENTOS ALMACENADOS Y EVENTOS ### Procedimientos Almacenados para Actualización de Datos #### Creación de Procedimientos Básicos 1. **Procedimiento para actualizar metadatos de publicaciones**: - En el cuerpo de pestaña SQL , escribe incluyenod los DELIMITER : ```sql DELIMITER // CREATE PROCEDURE actualizar_metadatos_publicaciones() BEGIN -- 1. Normalizar títulos (primera letra en mayúscula) UPDATE publicaciones SET titulo = CONCAT(UPPER(LEFT(titulo, 1)), SUBSTRING(titulo, 2)) WHERE titulo IS NOT NULL AND titulo != ''; -- 2. Calcular factor de impacto basado en tabla de citas UPDATE publicaciones p SET p.factor_impacto = ( SELECT COUNT(*) FROM citas WHERE id_pub_citada = p.id_publicacion ); -- Mostrar confirmación simple SELECT 'Actualización completada' AS resultado; END // DELIMITER ; ``` Para verificar que tu procedimiento almacenado funciona correctamente, sigue estos pasos: Ejecuta el procedimiento: En phpMyAdmin, ve a la pestaña "Rutinas" Busca tu procedimiento "actualizar_metadatos_publicaciones" en la lista Haz clic en el enlace "Ejecutar" junto a él Confirma la ejecución Verifica los resultados: Después de ejecutar, deberías ver el mensaje "Actualización completada" si incluiste la instrucción SELECT en el procedimiento Este es el primer indicador de que el procedimiento se ejecutó sin errores Comprueba los cambios en los datos: Examina los títulos en la tabla "publicaciones" para verificar que la primera letra está en mayúscula Verifica que el campo "factor_impacto" se ha actualizado con el conteo de citas para cada publicación Pruebas específicas: Ejecuta SELECT * FROM publicaciones LIMIT 10; para ver una muestra de registros y confirmar los cambios Ejecuta una consulta como SELECT id_publicacion, titulo, factor_impacto FROM publicaciones WHERE factor_impacto > 0; para ver qué publicaciones tienen citas ### Práctica del procedimiento Prueba con datos específicos: A). Inserta manualmente algunos registros de prueba con títulos en minúscula B). Añade algunas citas entre publicaciones C). Ejecuta el procedimiento nuevamente D). Verifica que los nuevos datos se actualizaron correctamente ### Procedimiento con parámetros para importar datos externos**: Caso importación de datos de publicaciones # Tutorial: Procedimiento para Importar Publicaciones Externas Este tutorial explica cómo implementar y utilizar un procedimiento almacenado para importar publicaciones científicas desde fuentes externas como Scopus o Web of Science, evitando duplicados. ## 1. Crear la tabla temporal para datos externos Primero, necesitas crear la tabla temporal que contendrá los datos a importar: ```sql CREATE TABLE tabla_externa_temp ( id_temp INT PRIMARY KEY AUTO_INCREMENT, titulo VARCHAR(255), doi VARCHAR(50), fecha_publicacion DATE, revista_nombre VARCHAR(150), factor_impacto DECIMAL(5,2) ); ``` ## 2. Crear la tabla de logs (si no existe) ```sql CREATE TABLE IF NOT EXISTS log_actualizaciones ( id_log INT PRIMARY KEY AUTO_INCREMENT, tabla VARCHAR(50), operacion VARCHAR(100), fecha DATETIME, registros_afectados INT, fuente VARCHAR(50), parametros TEXT ); ``` ## 3. Crear el procedimiento con parámetros ```sql DELIMITER // CREATE PROCEDURE importar_publicaciones_externas( IN origen VARCHAR(50), IN fecha_desde DATE ) BEGIN /* Procedimiento para importar publicaciones desde tabla temporal proveniente de fuente externa (por ejemplo, Scopus o WoS) */ -- Variable para contar inserciones DECLARE v_contador INT DEFAULT 0; -- 1. Insertar solo publicaciones que no existen (por DOI) INSERT INTO publicaciones ( titulo, doi, fecha, revista, factor_impacto ) SELECT et.titulo, et.doi, et.fecha_publicacion, et.revista_nombre, et.factor_impacto FROM tabla_externa_temp et WHERE et.fecha_publicacion >= fecha_desde AND NOT EXISTS ( SELECT 1 FROM publicaciones WHERE doi = et.doi ); -- Guardar número de registros insertados SET v_contador = ROW_COUNT(); -- 2. Registro de la actividad INSERT INTO log_actualizaciones ( tabla, operacion, fecha, registros_afectados, fuente, parametros ) VALUES ( 'publicaciones', 'importación_externa', NOW(), v_contador, origen, CONCAT('fecha_desde: ', fecha_desde) ); -- 3. Devolver número de registros afectados SELECT CONCAT('Se importaron ', v_contador, ' nuevas publicaciones desde ', origen) AS resultado; END // DELIMITER ; ``` el código anterior es para hacerlo directo en la pestaña de SQL Para hacerlo a través de phpmyadmin, elimina los compando DELIMITER en la parte del código: **Usando phpMyAdmin**: - Crea un nuevo procedimiento llamado `importar_publicaciones_externas` - Define los parámetros: - `IN origen VARCHAR(50)` - `IN fecha_desde DATE` ```sql BEGIN /* Procedimiento para importar publicaciones desde tabla temporal proveniente de fuente externa (por ejemplo, Scopus o WoS) */ -- Variable para contar inserciones DECLARE v_contador INT DEFAULT 0; -- 1. Insertar solo publicaciones que no existen (por DOI) INSERT INTO publicaciones ( titulo, doi, fecha, revista, factor_impacto ) SELECT et.titulo, et.doi, et.fecha_publicacion, et.revista_nombre, et.factor_impacto FROM tabla_externa_temp et WHERE et.fecha_publicacion >= fecha_desde AND NOT EXISTS ( SELECT 1 FROM publicaciones WHERE doi = et.doi ); -- Guardar número de registros insertados SET v_contador = ROW_COUNT(); -- 2. Registro de la actividad INSERT INTO log_actualizaciones ( tabla, operacion, fecha, registros_afectados, fuente, parametros ) VALUES ( 'publicaciones', 'importación_externa', NOW(), v_contador, origen, CONCAT('fecha_desde: ', fecha_desde) ); -- 3. Devolver número de registros afectados SELECT CONCAT('Se importaron ', v_contador, ' nuevas publicaciones desde ', origen) AS resultado; END ``` ## 4. Cargar datos de ejemplo en la tabla temporal ```sql -- Limpiar tabla temporal antes de cargar nuevos datos TRUNCATE TABLE tabla_externa_temp; -- Insertar datos de ejemplo (simulando datos de Scopus) INSERT INTO tabla_externa_temp (titulo, doi, fecha_publicacion, revista_nombre, factor_impacto) VALUES ('Análisis bibliométrico de producción científica en bibliotecas digitales', '10.1234/jlis.2023.001', '2023-01-15', 'Journal of Library Science', 2.4), ('Tendencias en catalogación y metadatos para 2023', '10.1234/jlis.2023.002', '2023-02-20', 'Information Technology', 1.8), ('Impacto de la inteligencia artificial en la búsqueda bibliográfica', '10.1234/techlib.2023.005', '2023-03-10', 'Tech Library Journal', 3.2), ('Conservación digital de archivos históricos', '10.1234/digpres.2022.010', '2022-11-05', 'Digital Preservation Quarterly', 1.5), ('Modelos de acceso abierto y su impacto en bibliotecas universitarias', '10.1234/openlib.2023.003', '2023-03-22', 'Open Access Library Journal', 2.1); ``` ## 5. Ejecutar el procedimiento ```sql -- Llamar al procedimiento para importar desde Scopus los artículos desde 2023 CALL importar_publicaciones_externas('Scopus', '2023-01-01'); ``` Nota: Si lo ejecutas desde la opción rutinas / procedimientos, entonces deberás incluir los parámetros qu ete pedirá ## 6. Verificar los resultados ```sql -- Comprobar las publicaciones importadas SELECT id_publicacion, titulo, doi, fecha, revista, factor_impacto FROM publicaciones WHERE doi LIKE '10.1234/%'; -- Verificar el registro de la actividad SELECT * FROM log_actualizaciones WHERE operacion = 'importación_externa' ORDER BY fecha DESC LIMIT 1; ``` ## Práctica alumnos. Ejemplo de uso con otra fuente (Web of Science) ```sql -- Limpiar tabla temporal TRUNCATE TABLE tabla_externa_temp; -- Insertar datos simulados de Web of Science INSERT INTO tabla_externa_temp (titulo, doi, fecha_publicacion, revista_nombre, factor_impacto) VALUES ('Gestión de datos de investigación: mejores prácticas', '10.5678/data.2023.001', '2023-01-25', 'Research Data Journal', 2.7), ('Evaluación de usabilidad en interfaces de búsqueda de biblioteca', '10.5678/ui.2023.002', '2023-02-18', 'User Interface Studies', 1.9), ('Preservación a largo plazo de colecciones digitales', '10.5678/pres.2023.001', '2023-03-15', 'Digital Preservation Quarterly', 1.5); -- Llamar al procedimiento para importar desde Web of Science CALL importar_publicaciones_externas('Web of Science', '2023-01-01'); ``` ## Beneficios de este procedimiento - **Evita duplicados**: La verificación por DOI asegura que no se inserten publicaciones repetidas - **Registra actividad**: Mantiene un historial de importaciones en la tabla de logs - **Filtrado por fecha**: Permite importar solo publicaciones a partir de una fecha específica - **Versatilidad**: Puede utilizarse con diferentes fuentes bibliográficas (Scopus, WoS, etc.) ## Posibles mejoras - Añadir manejo de errores con bloques TRY-CATCH - Implementar la asociación automática con autores e instituciones - Crear un proceso de validación y limpieza de datos antes de la inserción - Ampliar para importar también las referencias entre publicaciones ### Eventos Programados en MySQL #### Configuración de Eventos 1. **Habilitar el programador de eventos**: ```sql -- Verificar si el programador está activo SHOW VARIABLES LIKE 'event_scheduler'; -- Habilitar el programador si está desactivado SET GLOBAL event_scheduler = ON; ``` 2. **Crear evento para actualización diaria**: **Usando phpMyAdmin**: - Ve a la pestaña "Eventos" - Haz clic en "Nueva" - Configura: - Nombre: evento_actualizacion_diaria - Programación: EVERY 1 DAY - Hora de inicio: 01:00:00 - En el cuerpo del evento, escribe: ```sql BEGIN -- Llamar al procedimiento de actualización de metadatos CALL actualizar_metadatos_publicaciones(); -- Actualizar estadísticas de consulta UPDATE estadisticas_publicaciones e SET total_consultas_mes = ( SELECT COUNT(*) FROM log_consultas WHERE id_publicacion = e.id_publicacion AND fecha_consulta >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) WHERE EXISTS ( SELECT 1 FROM log_consultas WHERE id_publicacion = e.id_publicacion AND fecha_consulta >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) ); END ``` 3. **Crear evento para limpieza mensual**: ```sql CREATE EVENT evento_limpieza_mensual ON SCHEDULE EVERY 1 MONTH STARTS '2023-01-01 02:00:00' COMMENT 'Limpieza mensual de registros temporales y logs antiguos' DO BEGIN -- Eliminar logs de consulta de más de 6 meses DELETE FROM log_consultas WHERE fecha_consulta < DATE_SUB(CURDATE(), INTERVAL 6 MONTH); -- Limpiar tablas temporales TRUNCATE TABLE datos_temporales_importacion; -- Registrar la limpieza INSERT INTO log_mantenimiento (operacion, fecha, detalles) VALUES ('limpieza_mensual', NOW(), 'Limpieza de logs antiguos y tablas temporales'); END; ``` ## Referencias y recursos adicionales - [Documentación oficial de MySQL](https://dev.mysql.com/doc/) - [W3Schools SQL Tutorial](https://www.w3schools.com/sql/) - [HackerRank SQL challenges](https://www.hackerrank.com/domains/sql) - [Mode SQL Tutorial](https://mode.com/sql-tutorial/)