Tema 1
Lenguaje SQL (DDL)
Diseño físico de bases de datos: tablas e índices
1 · SQL y el DDL
SQL y sus cuatro familias
SQL es un único lenguaje, pero sus órdenes se agrupan en cuatro familias (sublenguajes) según para qué sirven. Este tema trata de la primera, el DDL.
Recorre el diagrama paso a paso.
-
DDL— Data Definition Language: define la estructura. -
DML— consultar, insertar, eliminar y editar datos. -
DCL— controlar permisos. -
TCL— gestionar transacciones.
El lenguaje DDL de SQL permite definir, modificar y eliminar la estructura de una base de datos. No trabaja con los datos en sí (eso es el DML, Temas 2 y 3), sino con las tablas que los guardan.
Las órdenes del DDL
El DDL no es una instrucción, sino una familia de 5 órdenes básicas. Las tres más importantes son CREATE, ALTER y DROP. Cada orden se aplica a un objeto (una base de datos, una tabla, un índice): CREATE TABLE, DROP DATABASE, CREATE INDEX…
Recorre el diagrama paso a paso.
-
CREATE— crea un objeto nuevo. -
ALTER— modifica un objeto que ya existe. -
DROP— elimina un objeto por completo. -
TRUNCATE— vacía por completo una tabla (elDELETEva eliminando fila a fila). -
RENAME— cambia el nombre.
2 · Bases de datos
Gestionar bases de datos
Lo primero es tener una base de datos donde crear las tablas. Son las órdenes del DDL aplicadas al objeto DATABASE:
-
CREATE DATABASE— crea una base de datos. -
USE— la selecciona para trabajar con ella. -
DROP DATABASE— la elimina.
Script completo para crear la base de datos
-- Script re-ejecutable: borra y vuelve a crear
DROP DATABASE IF EXISTS biblioteca;
CREATE DATABASE biblioteca
CHARACTER SET utf8mb4
COLLATE utf8mb4_spanish_ci;
USE biblioteca; -- a partir de aquí, todo va aquí
SHOW TABLES; -- comprobar qué hay dentro-
IF EXISTS / IF NOT EXISTS— evitan errores si el objeto ya existe (o no): el script se puede ejecutar tantas veces como haga falta. -
utf8mb4— juego de caracteres completo (acentos, ñ, emojis…). El recomendado en MySQL 8. -
COLLATE— define cómo se comparan y ordenan los textos (mayúsculas, acentos, ñ). -
USE— selecciona la base de datos activa. Sin él,CREATE TABLEno sabe dónde crear la tabla.
3 · Tablas
Crear tablas
Las tablas se crean con CREATE TABLE. Dentro de sus paréntesis hay columnas (campos) y, al final, restricciones de tabla. Cada columna lleva su nombre, su tipo de dato y, si hace falta, propiedades.
Recorre el diagrama paso a paso.
CREATE DATABASE Ejemplo;
USE Ejemplo;
CREATE TABLE IF NOT EXISTS Alumno(
DNI CHAR(9) PRIMARY KEY,
nombre VARCHAR(20) NOT NULL,
apellidos VARCHAR(20) NOT NULL,
fechaNacimiento DATE,
poblacion VARCHAR(20) DEFAULT 'Barcelona',
numHermanos TINYINT,
ciclo ENUM('ASYR','DAW','DAM'),
edad TINYINT,
CONSTRAINT verifEdad CHECK (edad>=0)
);Los tipos de datos de este ejemplo:
-
CHAR(9)— texto de longitud fija (un DNI siempre tiene 9 caracteres). -
VARCHAR(20)— texto de longitud variable, hasta 20 caracteres. -
DATE— una fecha. -
TINYINT— un número entero pequeño. -
ENUM(...)— uno de los valores de la lista.
Elegir bien los tipos de datos (en MySQL y SQL Server) sirve para optimizar el almacenamiento y el rendimiento.
Ejemplo completo: la tabla Libro
CREATE TABLE Libro (
isbn CHAR(13) NOT NULL,
titulo VARCHAR(150) NOT NULL,
añoPublic SMALLINT,
precio DECIMAL(6,2) DEFAULT 0,
estado ENUM('disponible','prestado')
DEFAULT 'disponible',
autor_id INT,
PRIMARY KEY (isbn),
CONSTRAINT fk_libro_autor
FOREIGN KEY (autor_id) REFERENCES autor(id)
ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT ck_precio CHECK (precio >= 0)
) ENGINE = InnoDB;-
Nombre de la tabla— en singular y minúsculas, sin espacios ni acentos. -
Columnas— nombre + tipo de dato + restricciones de columna (NOT NULL,DEFAULT…). -
Restricciones de tabla— PK, FK,UNIQUEyCHECKcon nombre propio (CONSTRAINT pk_…,fk_…). -
Comportamiento de la FK— qué ocurre en la tabla hija si el padre se modifica o se borra. -
ENGINE— motor de almacenamiento. InnoDB (el de por defecto) es el único que respeta claves foráneas y transacciones.
Una clave foránea se crea con un CONSTRAINT (una regla) de tipo FOREIGN KEY, seguido del campo (autor_id) y de la tabla a la que hace referencia (REFERENCES autor(id)). Un CONSTRAINT de tipo CHECK valida el valor de un campo.
Tipos de datos esenciales en MySQL
-
INT— enteros (ids, cantidades). -
TINYINT / SMALLINT— enteros pequeños: edad, año. -
DECIMAL(p,s)— exactos: dinero, notas. -
FLOAT / DOUBLE— aproximados: medidas. -
CHAR(n)— longitud fija: DNI, ISBN, código postal. -
VARCHAR(n)— longitud variable: nombres. -
TEXT— textos largos: descripciones. -
ENUM('a','b')— lista cerrada de valores. -
DATE · TIME · DATETIME · YEAR—2026-10-21·19:30:00·2026-10-21 19:30:00·2026. -
BOOLEAN— sinónimo deTINYINT(1): 0 / 1. -
TIMESTAMP · JSON · BLOB— marca de tiempo automática · documentos JSON · binarios (mejor guardar la ruta).
-
UNSIGNED— no permite números negativos. -
AUTO_INCREMENT— el número se va incrementando solo. -
UNIQUE— clave candidata: no admite repetidos (sí admite NULL).
Propiedades de los campos
Las propiedades son las reglas que se escriben detrás del tipo de dato de una columna:
-
PRIMARY KEY— clave primaria: identifica cada fila, única y no nula. -
NOT NULL— el campo es obligatorio. -
DEFAULT— valor que se usa si no se indica otro. -
AUTO_INCREMENT— número que se genera solo, sumando 1. -
UNIQUE— el valor no se puede repetir. -
CHECK— condición que deben cumplir los valores.
Claves foráneas: ON UPDATE / ON DELETE
Cada vez que hay una relación hay que decidir qué pasa en la tabla hija cuando el padre se modifica (ON UPDATE) o se borra (ON DELETE). Una clave foránea no puede hacer referencia a algo que no exista.
FOREIGN KEY (autorId)
REFERENCES autor(id)
ON DELETE <acción> -- si se borra el padre
ON UPDATE <acción> -- si cambia su PK-
RESTRICT / NO ACTION— prohíbe borrar o modificar el padre si tiene hijos. Es el comportamiento por defecto. -
CASCADE— propaga el cambio: si se borra el padre se borran sus hijos; si cambia la PK, se actualiza la FK. -
SET NULL— deja la FK de los hijos a NULL ("desasignados"). La columna FK debe admitir NULL.
Ejemplo con ríos y poblaciones (el río guarda el código postal de la población donde nace, nacimientoCP):
-
ON UPDATE CASCADE— si el código postal 77 pasa a 78, el río se actualiza automáticamente. -
ON UPDATE RESTRICT— no deja cambiar el código postal porque ya está registrado en un río. -
ON UPDATE SET NULL— cambia el código postal y en el ríonacimientoCPqueda NULL. -
ON DELETE RESTRICT— no deja eliminar la población. -
ON DELETE CASCADE— se eliminan todos los ríos de esa población. -
ON DELETE SET NULL— se elimina la población y en sus ríosnacimientoCPqueda vacío.
Tablas relacionadas, EXPLAIN y ALTER TABLE
En tablas relacionadas hay que definir bien los campos y las restricciones (claves) que las unen. La estructura de una tabla se consulta con EXPLAIN.
Con ALTER TABLE se modifica una tabla ya creada:
- añadir o eliminar columnas,
- renombrar tablas,
- gestionar claves y restricciones.
ALTER, borrar tablas y orden de creación
ALTER modifica una tabla ya existente:
-- Columnas
ALTER TABLE socio ADD COLUMN email VARCHAR(100);
ALTER TABLE socio MODIFY email VARCHAR(150) NOT NULL;
ALTER TABLE socio RENAME COLUMN email TO correo;
ALTER TABLE socio DROP COLUMN correo;
-- Restricciones
ALTER TABLE socio ADD CONSTRAINT uq_dni UNIQUE (dni);
ALTER TABLE libro DROP FOREIGN KEY fk_libro_autor;
RENAME TABLE socio TO usuario;-
DROP— DDL. Borra los datos y la tabla. No reversible. -
TRUNCATE— DDL. Borra todos los datos, no la tabla, y reinicia el contador. No reversible. -
DELETE— DML. Borra datos conWHERE, no la tabla ni el contador. Reversible con TCL.
No se puede crear una tabla que apunte a otra que aún no existe: hay que tener orden. Primero los padres (autor, socio), después las hijas (libro, préstamo). Para borrar, al revés.
- Script re-ejecutable:
DROP DATABASE IF EXISTS+CREATE DATABASE+USEal principio. - Mismo tipo en FK y PK:
autor_id INT→autor.id INT(incluidoUNSIGNEDsi lo lleva). - SET NULL ⇒ columna que admite NULL: si la FK es
NOT NULL,ON DELETE SET NULLdará error. - PK compuesta en tablas N:M:
PRIMARY KEY (libro_isbn, socio_id, fecha)en la tabla intermedia. - Restricciones con nombre (
pk_…,fk_…,uq_…,ck_…): fáciles de localizar y borrar. - Probar antes de entregar: ejecutar el
.sqlcompleto con la BD borrada, 0 errores.
4 · Índices
Qué es un índice y qué tipos hay
Un índice mejora el rendimiento de las consultas acelerando la búsqueda y recuperación de datos.
Recorre el diagrama paso a paso.
-
Clusterizados— determinan el orden físico de las filas. -
No clusterizados— estructura separada que apunta a las filas. -
Primarios— sobre la clave primaria. -
Únicos / no únicos— según si admiten valores repetidos. -
Simples / compuestos— sobre una o varias columnas. -
FULLTEXT— para búsquedas de texto (MySQL).
Cómo leer los diagramas: raíz, hojas y claves
Los dos diagramas siguientes usan cuatro palabras. Conviene tenerlas claras antes:
-
Clave— el valor de la columna indexada (por ejemplo, cadaid). -
Raíz— la caja de arriba: toda búsqueda empieza en ella. -
Hoja— las cajas de abajo: todas las claves, ordenadas. -
Puntero— una flecha hacia la fila: indica dónde está en la tabla.
Recorre el diagrama paso a paso.
Clusterizado y no clusterizado
Clusterizado: ordena físicamente las filas de la tabla. Las hojas son las propias filas.
Recorre el diagrama paso a paso.
No clusterizado: una estructura aparte que apunta a las filas. Las hojas guardan la clave y un puntero.
Recorre el diagrama paso a paso.
Ventajas, desventajas y cuándo crearlos
-
Ventajas— mayor rapidez en búsquedas, ordenaciones y filtrados. -
Desventajas— mayor coste en inserciones y actualizaciones, y ocupan espacio.
Índices en MySQL: tipos y CREATE INDEX
Un índice es como el índice de un libro: MySQL salta directamente a las filas que busca en lugar de leerlas todas. Acelera WHERE, JOIN y ORDER BY, pero ocupa espacio y ralentiza un poco INSERT/UPDATE.
-
PRIMARY— se crea solo con la PK. Único y sin NULL. -
UNIQUE— impide duplicados y acelera las búsquedas. -
INDEX— índice normal: columnas muy consultadas que se repiten. -
FULLTEXT— búsqueda de palabras dentro de textos largos.
-- Crear un índice sobre una tabla ya existente
CREATE INDEX idx_libro_anio ON libro (anio_pub);
SHOW INDEX FROM libro; -- ver sus índices
DROP INDEX idx_libro_anio ON libro;5 · Ejercicio práctico
Ejercicio práctico: embalses y ríos
El diagrama E/R tiene cinco entidades: Operario, Río, Embalse, Población y las relaciones entre ellas. Un río puede ser afluente de otro (relación reflexiva 1:N), nace en una población (N:1), un operario registra el caudal de un río en un embalse (N:M:P, con fecha y caudal actual) y un embalse abastece a poblaciones (N:M, con fecha de inicio, fecha de fin y caudal).
Embalse: {nombre(PK), añoConstrucción, numCompuertas, capacidadMax, estado}
Población: {codigoPostal(PK), nombre, habitantes}
Operario: {codigo(PK), DNI(UN), nombre, apellidos, fechaAlta, tipo}
Río: {nombre(PK), caudal, longitud, afluente(FK), nacimientoCP(FK)}
Abastecer: {embalseNombre(PK,FK), poblacionCP(PK,FK), fechaInicio(PK), fechaFin, caudal}
Registro: {rioNombre(PK,FK), operarioCodigo(PK,FK), embalseNombre(PK,FK), fecha(PK), caudalActual}- El caudal actual (
caudalActual) de cada registro debe estar entre 10 y 100, ambos incluidos. - El estado de un embalse solo puede ser: operativo, mantenimiento o vacío.
- El tipo de operario solo puede ser: arquitecto, ingeniero o básico.
- El nombre de un embalse se puede modificar siempre y el cambio se refleja en todas las tablas relacionadas. No se permite eliminar ningún embalse.
- El código de un operario se puede modificar, pero no se puede eliminar un operario que tenga registros.
- El nombre de un río se puede modificar y el cambio se refleja en sus registros. No se puede eliminar un río que tenga registros (para no perder el histórico de caudales).
- Si se elimina un río que es afluente de otro, el campo afluente de este último quedará a NULL.
- En el resto de relaciones, el comportamiento por defecto (RESTRICT).
- La mayoría de las consultas filtran por el número de compuertas de los embalses.
Paso 1
Crea la base de datos nf02_vc01DB de forma que el script se pueda ejecutar varias veces, y selecciónala.
Tabla Embalse
Crea la tabla Embalse con su clave primaria. El estado solo puede ser operativo, mantenimiento o vacío.
Tabla Población
Crea la tabla Población.
Tabla Operario
Crea la tabla Operario. El código se genera solo, el DNI es clave candidata y el tipo solo puede ser arquitecto, ingeniero o básico.
Tabla Río (relación reflexiva)
Crea la tabla Río. afluente apunta a otro río y nacimientoCP a la población donde nace. Si se elimina un río que es afluente de otro, el campo afluente queda a NULL; el resto, comportamiento por defecto.
Tabla Abastecer
Crea la tabla Abastecer (relación N:M entre Embalse y Población). El nombre de un embalse se puede modificar siempre y no se permite eliminar ningún embalse.
Tabla Registro
Crea la tabla Registro con todas sus restricciones: PK compuesta con nombre, las tres FK según las restricciones de comportamiento y el caudal actual entre 10 y 100.
Optimización
La mayoría de las consultas filtran por el número de compuertas de los embalses. ¿Qué harías?