7 Lenguaje SQL: Creación de bases de datos y tablas
A nivel teórico, existen dos lenguajes para el manejo de bases de datos:
DDL (Data Definition Language) Lenguaje de definición de datos. Es el lenguaje que se usa para crear bases de datos y tablas, y para modificar sus estructuras, así como los permisos y privilegios.
Este lenguaje trabaja sobre unas tablas especiales llamadas diccionario de datos.
DML (Data Manipilation Language) lenguaje de manipulación de datos. Es el que se usa para modificar y obtener datos desde las bases de datos.
SQL engloba ambos lenguajes DDL+DML, y los estudiaremos juntos, ya que ambos forman parte del conjunto de sentencias de SQL.
En este capítulo vamos a explicar el proceso para pasar del modelo lógico relacional, en forma de esquemas de relaciones, al modelo físico, usando sentencias SQL, y viendo las peculiaridades específicas de MySQL.
Crear una base de datos
Cada conjunto de relaciones que componen un modelo completo forma una base de datos. Desde el punto de vista de SQL, una base de datos es sólo un conjunto de relaciones (o tablas), y para organizarlas o distinguirlas se accede a ellas mediante su nombre. A nivel de sistema operativo, cada base de datos se guarda en un directorio diferente.
Debido a esto, crear una base de datos es una tarea muy simple. Claro que, en el momento de crearla, la base de datos estará vacía, es decir, no contendrá ninguna tabla.
Vamos a crear y manipular nuestra propia base de datos, al tiempo que nos familiarizamos con la forma de trabajar de MySQL.
Para empezar, crearemos una base de datos para nosotros solos, y la llamaremos "prueba". Para crear una base de datos se usa una sentencia CREATE DATABASE:
mysql> CREATE DATABASE prueba; Query OK, 1 row affected (0.03 sec) mysql>
Podemos averiguar cuántas bases de datos existen en nuestro sistema usando la sentencia SHOW DATABASES:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | mysql | | prueba | | test | +--------------------+ 3 rows in set (0.00 sec) mysql>
A partir de ahora, en los próximos capítulos, trabajaremos con esta base de datos, por lo tanto la seleccionaremos como base de datos por defecto. Esto nos permitirá obviar el nombre de la base de datos en consultas. Para seleccionar una base de datos se usa el comando USE, que no es exactamente una sentencia SQL, sino más bien de una opción de MySQL:
mysql> USE prueba; Database changed mysql>
Crear una tabla
Veamos ahora la sentencia CREATE TABLE que sirve para crear tablas.
La sintaxis de esta sentencia es muy compleja, ya que existen muchas opciones y tenemos muchas posibilidades diferentes a la hora de crear una tabla. Las iremos viendo paso a paso, y en poco tiempo sabremos usar muchas de sus posibilidades.
En su forma más simple, la sentencia CREATE TABLE creará una tabla con las columnas que indiquemos. Crearemos, como ejemplo, una tabla que nos permitirá almacenar nombres de personas y sus fechas de nacimiento. Deberemos indicar el nombre de la tabla y los nombres y tipos de las columnas:
mysql> USE prueba Database changed mysql> CREATE TABLE gente (nombre VARCHAR(40), fecha DATE); Query OK, 0 rows affected (0.53 sec) mysql>
Hemos creado una tabla llamada "gente" con dos columnas: "nombre" que puede contener cadenas de hasta 40 caracteres y "fecha" de tipo fecha.
Podemos consultar cuántas tablas y qué nombres tienen en una base de datos, usando la sentencia SHOW TABLES:
mysql> SHOW TABLES; +------------------+ | Tables_in_prueba | +------------------+ | gente | +------------------+ 1 row in set (0.01 sec) mysql>
Pero tenemos muchas más opciones a la hora de definir columnas. Además del tipo y el nombre, podemos definir valores por defecto, permitir o no que contengan valores nulos, crear una clave primaria, indexar...
La sintaxis para definir columnas es:
nombre_col tipo [NOT NULL | NULL] [DEFAULT valor_por_defecto]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
[definición_referencia]
Veamos cada una de las opciones por separado.
Valores nulos
Al definir cada columna podemos decidir si podrá o no contener valores nulos.
Debemos recordar que, como vimos en los capítulos de modelado, aquellas columnas que son o forman parte de una clave primaria no pueden contener valores nulos.
Veremos que, si definimos una columna como clave primaria, automáticamente se impide que pueda contener valores nulos, pero este no es el único caso en que puede ser interesante impedir la asignación de valores nulos para una columna.
La opción por defecto es que se permitan valores nulos, NULL, y para que no se permitan, se usa NOT NULL. Por ejemplo:
mysql> CREATE TABLE ciudad1 (nombre CHAR(20) NOT NULL, poblacion INT NULL); Query OK, 0 rows affected (0.98 sec)
Valores por defecto
Para cada columna también se puede definir, opcionalmente, un valor por defecto. El valor por defecto se asignará de forma automática a una columna cuando no se especifique un valor determinado al añadir filas.
Si una columna puede tener un valor nulo, y no se especifica un valor por defecto, se usará NULL como valor por defecto. En el ejemplo anterior, el valor por defecto para poblacion es NULL.
Por ejemplo, si queremos que el valor por defecto para poblacion sea 5000, podemos crear la tabla como:
mysql> CREATE TABLE ciudad2 (nombre CHAR(20) NOT NULL,
-> poblacion INT NULL DEFAULT 5000);
Query OK, 0 rows affected (0.09 sec)
Claves primarias
También se puede definir una clave primaria sobre una columna, usando la palabra clave KEY o PRIMARY KEY.
Sólo puede existir una clave primaria en cada tabla, y la columna sobre la que se define una clave primaria no puede tener valores NULL. Si esto no se especifica de forma explícita, MySQL lo hará de forma automática.
Por ejemplo, si queremos crear un índice en la columna nombre de la tabla de ciudades, crearemos la tabla así:
mysql> CREATE TABLE ciudad3 (nombre CHAR(20) NOT NULL PRIMARY KEY,
-> poblacion INT NULL DEFAULT 5000);
Query OK, 0 rows affected (0.20 sec)
Usar NOT NULL PRIMARY KEY equivale a PRIMARY KEY, NOT NULL KEY o sencillamente KEY. Personalmente, prefiero usar la primera forma o la segunda.
Existe una sintaxis alternativa para crear claves primarias, que en general es preferible, ya que es más potente. De hecho, la que hemos explicado es un alias para la forma general, que no admite todas las funciones (como por ejemplo, crear claves primarias sobre varias columnas). Veremos esta otra alternativa un poco más abajo.
Columnas autoincrementadas
En MySQL tenemos la posibilidad de crear una columna autoincrementada, aunque esta columna sólo puede ser de tipo entero.
Si al insertar una fila se omite el valor de la columna autoinrementada o si se inserta un valor nulo para esa columna, su valor se calcula automáticamente, tomando el valor más alto de esa columna y sumándole una unidad. Esto permite crear, de una forma sencilla, una columna con un valor único para cada fila de la tabla.
Generalmente, estas columnas se usan como claves primarias 'artificiales'. MySQL está optimizado para usar valores enteros como claves primarias, de modo que la combinación de clave primaria, que sea entera y autoincrementada es ideal para usarla como clave primaria artificial:
mysql> CREATE TABLE ciudad5 (clave INT AUTO_INCREMENT PRIMARY KEY,
-> nombre CHAR(20) NOT NULL,
-> poblacion INT NULL DEFAULT 5000);
Query OK, 0 rows affected (0.11 sec)
mysql>
Comentarios
Adicionalmente, al crear la tabla, podemos añadir un comentario a cada columna. Este comentario sirve como información adicional sobre alguna característica especial de la columna, y entra en el apartado de documentación de la base de datos:
mysql> CREATE TABLE ciudad6
-> (clave INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Clave principal',
-> nombre CHAR(50) NOT NULL,
-> poblacion INT NULL DEFAULT 5000);
Query OK, 0 rows affected (0.08 sec)
Definición de creación
A continuación de las definiciones de las columnas podemos añadir otras definiciones. La sintaxis más general es:
definición_columnas
| [CONSTRAINT [símbolo]] PRIMARY KEY (index_nombre_col,...)
| KEY [nombre_index] (nombre_col_index,...)
| INDEX [nombre_index] (nombre_col_index,...)
| [CONSTRAINT [símbolo]] UNIQUE [INDEX]
[nombre_index] [tipo_index] (nombre_col_index,...)
| [FULLTEXT|SPATIAL] [INDEX] [nombre_index] (nombre_col_index,...)
| [CONSTRAINT [símbolo]] FOREIGN KEY
[nombre_index] (nombre_col_index,...) [definición_referencia]
| CHECK (expr)
Veremos ahora cada una de estas opciones.
Índices
Tenemos tres tipos de índices. El primero corresponde a las claves primarias, que como vimos, también se pueden crear en la parte de definición de columnas.
Claves primarias
La sintaxis para definir claves primarias es:
definición_columnas | PRIMARY KEY (index_nombre_col,...)
El ejemplo anterior que vimos para crear claves primarias, usando esta sintaxis, quedaría así:
mysql> CREATE TABLE ciudad4 (nombre CHAR(20) NOT NULL,
-> poblacion INT NULL DEFAULT 5000,
-> PRIMARY KEY (nombre));
Query OK, 0 rows affected (0.17 sec)
Pero esta forma tiene más opciones, por ejemplo, entre los paréntesis podemos especificar varios nombres de columnas, para construir claves primarias compuestas por varias columnas:
mysql> CREATE TABLE mitabla1 (
-> id1 CHAR(2) NOT NULL,
-> id2 CHAR(2) NOT NULL,
-> texto CHAR(30),
-> PRIMARY KEY (id1, id2));
Query OK, 0 rows affected (0.09 sec)
mysql>
Índices
El segundo tipo de índice permite definir índices sobre una columna, sobre varias, o sobre partes de columnas. Para definir estos índices se usan indistintamente las opciones KEY o INDEX.
mysql> CREATE TABLE mitabla2 (
-> id INT,
-> nombre CHAR(19),
-> INDEX (nombre));
Query OK, 0 rows affected (0.09 sec)
O su equivalente:
mysql> CREATE TABLE mitabla3 (
-> id INT,
-> nombre CHAR(19),
-> KEY (nombre));
Query OK, 0 rows affected (0.09 sec)
También podemos crear un índice sobre parte de una columna:
mysql> CREATE TABLE mitabla4 (
-> id INT,
-> nombre CHAR(19),
-> INDEX (nombre(4)));
Query OK, 0 rows affected (0.09 sec)
Este ejemplo usará sólo los cuatro primeros caracteres de la columna 'nombre' para crear el índice.
Claves únicas
El tercero permite definir índices con claves únicas, también sobre una columna, sobre varias o sobre partes de columnas. Para definir índices con claves únicas se usa la opción UNIQUE.
La diferencia entre un índice único y uno normal es que en los únicos no se permite la inserción de filas con claves repetidas. La excepción es el valor NULL, que sí se puede repetir.
mysql> CREATE TABLE mitabla5 (
-> id INT,
-> nombre CHAR(19),
-> UNIQUE (nombre));
Query OK, 0 rows affected (0.09 sec)
Una clave primaria equivale a un índice de clave única, en la que el valor de la clave no puede tomar valores NULL. Tanto los índices normales como los de claves únicas sí pueden tomar valores NULL.
Por lo tanto, las definiciones siguientes son equivalentes:
mysql> CREATE TABLE mitabla6 (
-> id INT,
-> nombre CHAR(19) NOT NULL,
-> UNIQUE (nombre));
Query OK, 0 rows affected (0.09 sec)
Y:
mysql> CREATE TABLE mitabla7 (
-> id INT,
-> nombre CHAR(19),
-> PRIMARY KEY (nombre));
Query OK, 0 rows affected (0.09 sec)
Los índices sirven para optimizar las consultas y las búsquedas de datos. Mediante su uso es mucho más rápido localizar filas con determinados valores de columnas, o seguir un determinado orden. La alternativa es hacer búsquedas secuenciales, que en tablas grandes requieren mucho tiempo.
Claves foráneas
En MySQL sólo existe soporte para claves foráneas en tablas de tipo InnoDB. Sin embargo, esto no impide usarlas en otros tipos de tablas.
La diferencia consiste en que en esas tablas no se verifica si una clave foránea existe realmente en la tabla referenciada, y que no se eliminan filas de una tabla con una definición de clave foránea. Para hacer esto hay que usar tablas InnoDB.
Hay dos modos de definir claves foráneas en bases de datos MySQL.
El primero, sólo sirve para documentar, y, al menos en las pruebas que he hecho, no define realmente claves foráneas. Esta forma consiste en definir una referencia al mismo tiempo que se define una columna:
mysql> CREATE TABLE personas (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> nombre VARCHAR(40),
-> fecha DATE);
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE telefonos (
-> numero CHAR(12),
-> id INT NOT NULL REFERENCES personas (id)
-> ON DELETE CASCADE ON UPDATE CASCADE); (1)
Query OK, 0 rows affected (0.13 sec)
mysql>
Hemos usado una definición de referencia para la columna 'id' de la tabla 'telefonos', indicando que es una clave foránea correspondiente a la columna 'id' de la tabla 'personas' (1). Sin embargo, aunque la sintaxis se comprueba, esta definición no implica ningún comportamiento por parte de MySQL.
La otra forma es mucho más útil, aunque sólo se aplica a tablas InnoDB.
En esta forma no se añade la referencia en la definición de la columna, sino después de la definición de todas las columnas. Tenemos la siguiente sintaxis resumida:
CREATE TABLE nombre
definición_de_columnas
[CONSTRAINT [símbolo]] FOREIGN KEY [nombre_index] (nombre_col_index,...)
[REFERENCES nombre_tabla [(nombre_col,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]
[ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]]
El ejemplo anterior, usando tablas InnoDB y esta definición de claves foráneas quedará así:
mysql> CREATE TABLE personas2 (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> nombre VARCHAR(40),
-> fecha DATE)
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE telefonos2 (
-> numero CHAR(12),
-> id INT NOT NULL,
-> KEY (id), (2)
-> FOREIGN KEY (id) REFERENCES personas2 (id)
-> ON DELETE CASCADE ON UPDATE CASCADE) (3)
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql>
Es imprescindible que la columna que contiene una definición de clave foránea esté indexada (2). Pero esto no debe preocuparnos demasiado, ya que si no lo hacemos de forma explícita, MySQL lo hará por nosotros de forma implícita.
Esta forma define una clave foránea en la columna 'id', que hace referencia a la columna 'id' de la tabla 'personas' (3). La definición incluye las tareas a realizar en el caso de que se elimine una fila en la tabla 'personas'.
ON DELETE <opción>, indica que acciones se deben realizar en la tabla actual si se
borra una fila en la tabla referenciada.
ON UPDATE <opción>, es análogo pero para modificaciones de claves.
Existen cinco opciones diferentes. Veamos lo que hace cada una de ellas:
- RESTRICT: esta opción impide eliminar o modificar filas en la tabla referenciada si existen filas con el mismo valor de clave foránea.
- CASCADE: borrar o modificar una clave en una fila en la tabla referenciada con un valor determinado de clave, implica borrar las filas con el mismo valor de clave foránea o modificar los valores de esas claves foráneas.
- SET NULL: borrar o modificar una clave en una fila en la tabla referenciada con un valor determinado de clave, implica asignar el valor NULL a las claves foráneas con el mismo valor.
- NO ACTION: las claves foráneas no se modifican, ni se eliminan filas en la tabla que las contiene.
- SET DEFAULT: borrar o modificar una clave en una fila en la tabla referenciada con un valor determinado implica asignar el valor por defecto a las claves foráneas con el mismo valor.
Por ejemplo, veamos esta definición de la tabla 'telefonos':
mysql> CREATE TABLE personas3 (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> nombre VARCHAR(40),
-> fecha DATE)
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE telefonos3 (
-> numero CHAR(12),
-> id INT NOT NULL,
-> KEY (id),
-> FOREIGN KEY (id) REFERENCES personas3 (id)
-> ON DELETE RESTRICT ON UPDATE CASCADE)
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql>
Si se intenta borrar una fila de 'personas3' con un determinado valor de 'id', se producirá un error si existen filas en la tabla 'telefonos3' con mismo valor en la columna 'id'. La fila de 'personas3' no se eliminará, a no ser que previamente eliminemos las filas con el mismo valor de clave foránea en 'teléfonos3'.
Si se modifica el valor de la columna 'id' en la tabla 'personas3', se modificarán los valores de la columna 'id' para mantener la relación.
Veamos un ejemplo más práctico:
personas3 id nombre fecha 1 Fulanito 1998/04/14 2 Menganito 1975/06/18 3 Tulanito 1984/07/05 telefonos3 numero id 12322132 1 12332221 1 55546545 3 55565445 3
Si intentamos borrar la fila correspondiente a "Fulanito" se producirá un error, ya que existen dos filas en 'telefonos' con el valor 1 en la columna 'id'.
Sí será posible borrar la fila correspondiente a "Menganito", ya que no existe ninguna fila en la tabla 'telefonos3' con el valor 2 en la columna 'id'.
Si modificamos el valor de 'id' en la fila correspondiente a "Tulanito", por el valor 4, por ejemplo, se asignará el valor 4 a la columna 'id' de las filas 3ª y 4ª de la tabla 'telefonos3':
personas3 id nombre fecha 1 Fulanito 1998/04/14 2 Menganito 1975/06/18 4 Tulanito 1984/07/05 telefonos3 numero id 12322132 1 12332221 1 55546545 4 55565445 4
No hemos usado todas las opciones. Las opciones de MATCH FULL, MATCH PARTIAL o MATCH SIMPLE no las comentaremos de momento (lo dejaremos para más adelante).
La parte opcional CONSTRAINT [símbolo] sirve para asignar un nombre a la clave foránea, de modo que pueda usarse como identificador si se quiere modificar o eliminar una definición de clave foránea. También veremos esto con más detalle en capítulos avanzados.
Otras opciones
Las opiones como FULLTEXT o SPATIAL las veremos en otras secciones.
La opción CHECK no está implementada en MySQL.
Opciones de tabla
La parte final de la sentencia CREATE TABLE permite especificar varias opciones para la tabla.
Sólo comentaremos la opción del motor de almacenamiento, para ver el resto en detalle se puede consultar la sintaxis en CREATE TABLE.
Motor de almacenamiento
La sintaxis de esta opción es:
{ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM }
Podemos usar indistintamente ENGINE o TYPE, pero la forma recomendada es ENGINE ya que la otra desaparecerá en la versión 5 de MySQL.
Hay seis motores de almacenamiento disponibles. Algunos de ellos serán de uso obligatorio si queremos tener ciertas opciones disponibles. Por ejemplo, ya hemos comentado que el soporte para claves foráneas sólo está disponible para el motor InnoDB. Los motores son:
- BerkeleyDB o BDB: tablas de transacción segura con bloqueo de página.
- HEAP o MEMORY: tablas almacenadas en memoria.
- ISAM: motor original de MySQL.
- InnoDB: tablas de transacción segura con bloqueo de fila y claves foráneas.
- MERGE o MRG_MyISAM: una colección de tablas MyISAM usadas como una única tabla.
- MyISAM: el nuevo motor binario de almacenamiento portable que reemplaza a ISAM.
Generalmente usaremos tablas MyISAM o tablas InnoDB.
A veces, cuando se requiera una gran optimización, creemos tablas temporales en memoria.
Verificaciones
Disponemos de varias sentencias para verificar o consultar características de tablas.
Podemos ver la estructura de una tabla usando la sentencia SHOW COLUMNS:
mysql> SHOW COLUMNS FROM gente; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | nombre | varchar(40) | YES | | NULL | | | fecha | date | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
También podemos ver la instrucción usada para crear una tabla, mediante la sentencia SHOW CREATE TABLE:
mysql> show create table gente\G
*************************** 1. row ***************************
Table: gente
Create Table: CREATE TABLE `gente` (
`nombre` varchar(40) default NULL,
`fecha` date default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
Usando '\G' en lugar de ';' la salida se muestra en forma de listado, en lugar de en forma de tabla. Este formato es más cómodo a la hora de leer el resultado.
La sentencia CREATE TABLE mostrada no tiene por qué ser la misma que se usó al crear la tabla originalmente. MySQL rellena las opciones que se activan de forma implícita, y usa siempre el mismo formato para crear claves primarias.
Eliminar una tabla
A veces es necesario eliminar una tabla, ya sea porque es más sencillo crearla de nuevo que modificarla, o porque ya no es necesaria.
Para eliminar una tabla se usa la sentencia DROP TABLE.
La sintaxis es simple:
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
Por ejemplo:
mysql> DROP TABLE ciudad6; Query OK, 0 rows affected (0.75 sec) mysql>
Se pueden añadir las palabras IF EXISTS para evitar errores si la tabla a eliminar no existe.
mysql> DROP TABLE ciudad6; ERROR 1051 (42S02): Unknown table 'ciudad6' mysql> DROP TABLE IF EXISTS ciudad6; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>
Eliminar una base de datos
De modo parecido, se pueden eliminar bases de datos completas, usando la sentencia DROP_DATABASE.
La sintaxis también es muy simple:
DROP DATABASE [IF EXISTS] db_name
Hay que tener cuidado, ya que al borrar cualquier base de datos se elimina también cualquier tabla que contenga.
mysql> CREATE DATABASE borrame;
Query OK, 1 row affected (0.00 sec)
mysql> USE borrame
Database changed
mysql> CREATE TABLE borrame (
-> id INT,
-> nombre CHAR(40)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| borrame |
| mysql |
| prueba |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_borrame |
+-------------------+
| borrame |
+-------------------+
1 row in set (0.00 sec)
mysql> DROP DATABASE IF EXISTS borrame;
Query OK, 1 row affected (0.11 sec)
mysql> DROP DATABASE IF EXISTS borrame;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
Ejemplo 1
Veamos ahora como crear las bases de datos y tablas correspondientes a los ejemplos que vamos siguiendo desde el principio del curso.
Nuestro primer ejemplo tenía este esquema:
Estación(Identificador, Latitud, Longitud, Altitud) Muestra(IdentificadorEstacion, Fecha, Temperatura mínima, Temperatura máxima, Precipitaciones, Humedad mínima, Humedad máxima, Velocidad del viento mínima, Velocidad del viento máxima)
Y habíamos elegido los siguientes tipos para cada columna:
| Columna | Tipo |
|---|---|
| Identificador | MEDIUMINT UNSIGNED |
| Latitud | VARCHAR(14) |
| Longitud | VARCHAR(15) |
| Altitud | MEDIUMINT |
| IdentificadorEstacion | MEDIUMINT UNSIGNED |
| Fecha | DATE |
| Temperatura mínima | TINYINT |
| Temperatura máxima | TINYINT |
| Precipitaciones | SMALLINT UNSIGNED |
| Humedad mínima | TINYINT UNSIGNED |
| Humedad máxima | TINYINT UNSIGNED |
| Velocidad del viento mínima | SMALLINT UNSIGNED |
| Velocidad del viento máxima | SMALLINT UNSIGNED |
Primero crearemos una base de datos, a la que podemos llamar "meteo":
mysql> CREATE DATABASE meteo; Query OK, 1 row affected (0.02 sec)
Podemos situarnos en la base de datos, usando la USE o bien crear las tablas usando el nombre completo, es decir, mediante el prefijo "meteo.". Empecemos con la primera tabla:
mysql> USE meteo
Database changed
mysql> CREATE TABLE estacion (
-> identificador MEDIUMINT UNSIGNED NOT NULL,
-> latitud VARCHAR(14) NOT NULL,
-> longitud VARCHAR(15) NOT NULL,
-> altitud MEDIUMINT NOT NULL,
-> PRIMARY KEY (identificador)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)
Hemos creado una clave primaria, y como es obligatorio, hemos definido la columna 'indentificador' como NOT NULL. Usaremos tablas InnoDB ya que queremos que MySQL haga el control de las claves foráneas.
Veamos ahora la segunda tabla:
mysql> CREATE TABLE muestra (
-> identificadorestacion MEDIUMINT UNSIGNED NOT NULL,
-> fecha DATE NOT NULL,
-> temperaturaminima TINYINT,
-> temperaturamaxima TINYINT,
-> precipitaciones SMALLINT UNSIGNED,
-> humedadminima TINYINT UNSIGNED,
-> humedadmaxima TINYINT UNSIGNED,
-> velocidadminima SMALLINT UNSIGNED,
-> velocidadmaxima SMALLINT UNSIGNED,
-> KEY (identificadorestacion),
-> FOREIGN KEY (identificadorestacion)
-> REFERENCES estacion(identificador)
-> ON DELETE NO ACTION
-> ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)
A falta de datos concretos en el enunciado sobre qué hacer con las muestras de estaciones si estas desaparecen, hemos optado por mantener el valor de la clave foránea. También hemos decidido modificar el identificador si se cambia en la tabla de estaciones.
Ejemplo 2
El segundo ejemplo consiste en modelar una biblioteca. Este era el esquema:
Libro(ClaveLibro, Título, Idioma, Formato, ClaveEditorial) Tema(ClaveTema, Nombre) Autor(ClaveAutor, Nombre) Editorial(ClaveEditorial, Nombre, Dirección, Teléfono) Ejemplar(ClaveEjemplar, ClaveLibro, NúmeroOrden, Edición, Ubicación, Categoría) Socio(ClaveSocio, Nombre, Dirección, Teléfono, Categoría) Préstamo(ClaveSocio, ClaveEjemplar, NúmeroOrden, Fecha_préstamo, Fecha_devolución, Notas) Trata_sobre(ClaveLibro, ClaveTema) Escrito_por(ClaveLibro, ClaveAutor)
Y los tipos para las columnas:
| Columna | Tipo |
|---|---|
| ClaveLibro | INT |
| Titulo | VARCHAR(60) |
| Idioma | VARCHAR(15) |
| Formato | VARCHAR(15) |
| ClaveTema | SMALLINT |
| Nombre(tema) | VARCHAR(40) |
| ClaveAutor | INT |
| Nombre(autor) | VARCHAR(60) |
| ClaveEditorial | SMALLINT |
| Nombre(editorial) | VARCHAR(60) |
| Direccion(editorial) | VARCHAR(60) |
| Telefono(editorial) | VARCHAR(15) |
| ClaveEjemplar | INT |
| NumeroOrden | SMALLINT |
| Edicion | SMALLINT |
| Ubicacion | VARCHAR(15) |
| Categoria(libro) | CHAR |
| ClaveSocio | INT |
| Nombre(socio) | VARCHAR(60) |
| Direccion(socio) | VARCHAR(60) |
| Telefono(socio) | VARCHAR(15) |
| Categoria(socio) | CHAR |
| Fecha_prestamo | DATE |
| Fecha_devolucion | DATE |
| Notas | BLOB |
Empecemos por crear la base de datos:
mysql> CREATE DATABASE biblio; Query OK, 1 row affected (0.16 sec) mysql>USE biblio Database changed mysql>
Ahora crearemos las tablas. Primero crearemos la tabla de editoriales, ya que su clave primaria se usa como clave foránea en la tabla de libros:
mysql> CREATE TABLE editorial (
-> claveeditorial SMALLINT NOT NULL,
-> nombre VARCHAR(60),
-> direccion VARCHAR(60),
-> telefono VARCHAR(15),
-> PRIMARY KEY (claveeditorial)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)
mysql>
Ahora podemos crear la tabla de libros:
mysql> CREATE TABLE libro (
-> clavelibro INT NOT NULL,
-> titulo VARCHAR(60),
-> idioma VARCHAR(15),
-> formato VARCHAR(15),
-> claveeditorial SMALLINT,
-> PRIMARY KEY (clavelibro),
-> KEY(claveeditorial),
-> FOREIGN KEY (claveeditorial)
-> REFERENCES editorial(claveeditorial)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql>
Seguimos con las tablas de tema, autor:
mysql> CREATE TABLE tema (
-> clavetema SMALLINT NOT NULL,
-> nombre VARCHAR(40),
-> PRIMARY KEY (clavetema)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE autor (
-> claveautor INT NOT NULL,
-> nombre VARCHAR(60),
-> PRIMARY KEY (claveautor)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)
mysql>
Seguimos con la tabla ejemplar, que recordemos que contiene entidades subordinadas de libro:
mysql> CREATE TABLE ejemplar (
-> claveejemplar INT NOT NULL,
-> clavelibro INT NOT NULL,
-> numeroorden SMALLINT NOT NULL,
-> edicion SMALLINT,
-> ubicacion VARCHAR(15),
-> categoria CHAR,
-> PRIMARY KEY (claveejemplar),
-> FOREIGN KEY (clavelibro)
-> REFERENCES libro(clavelibro)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql>
Seguimos con la tabla de socio:
mysql> CREATE TABLE socio (
-> clavesocio INT NOT NULL,
-> nombre VARCHAR(60),
-> direccion VARCHAR(60),
-> telefono VARCHAR(15),
-> categoria CHAR,
-> PRIMARY KEY (clavesocio)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)
mysql>
Ahora la tabla de prestamo:
mysql> CREATE TABLE prestamo (
-> clavesocio INT,
-> claveejemplar INT,
-> numeroorden SMALLINT,
-> fecha_prestamo DATE NOT NULL,
-> fecha_devolucion DATE DEFAULT NULL,
-> notas BLOB,
-> FOREIGN KEY (clavesocio)
-> REFERENCES socio(clavesocio)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE,
-> FOREIGN KEY (claveejemplar)
-> REFERENCES ejemplar(claveejemplar)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)
mysql>
Sólo nos quedan por crear las tablas trata_sobre y escrito_por:
mysql> CREATE TABLE trata_sobre (
-> clavelibro INT NOT NULL,
-> clavetema SMALLINT NOT NULL,
-> FOREIGN KEY (clavelibro)
-> REFERENCES libro(clavelibro)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE,
-> FOREIGN KEY (clavetema)
-> REFERENCES tema(clavetema)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE escrito_por (
-> clavelibro INT NOT NULL,
-> claveautor INT NOT NULL,
-> FOREIGN KEY (clavelibro)
-> REFERENCES libro(clavelibro)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE,
-> FOREIGN KEY (claveautor)
-> REFERENCES autor(claveautor)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql>