En este capítulo creamos una base de datos que nos sirva para entender bien todos los conceptos. Crearemos una base de datos que almacenará información acerca de series y películas. Para obtener toda la información de las películas que estemos agregando nos vamos a basar en un página especializada denominada www.imdb.com.
Lo que vamos a crear es un archivo en formato .sql para definir la estructura de nuestra base de datos. Crearemos el esquema de nuestra base de datos. Lo vamos a llamar peliculas_schema.sql.
Comentarios en SQL
Podemos incluir comentarios en SQL de dos maneras distintas:
- 2 guiones: — (no es recomendable)
- Comentarios en múltiples líneas (tipo CSS): /* */
Creando la base de datos
Lo primero que incluimos en nuestro archivo será la creación de la base de datos, mediante la siguiente sintaxis.
Ejm
CREATE DATABASE IF NOT EXISTS peliculas;
Ya que hemos creado la base de datos, tenemos que ir a la misma con el comando use.
Ejm
use peliculas;
El siguiente paso es crear nuestra primera tabla, que va a ser la tabla de películas. La sintaxis sería la siguiente:
Ejm
DROP DATABASE IF EXISTS peliculas;
CREATE DATABASE IF NOT EXISTS peliculas;
USE peliculas;
/* Tabla de datos */
CREATE TABLE movies(
imdb_id CHAR(9) PRIMARY KEY,
title VARCHAR(80) NOT NULL,
plot TEXT,
author VARCHAR(100) DEFAULT 'Pending',
actors VARCHAR(100) DEFAULT 'Pending',
country VARCHAR(30) DEFAULT 'Unknown',
premiere YEAR(4) NOT NULL,
poster VARCHAR(150) DEFAULT 'no-poster.jpg',
trailer VARCHAR(150) DEFAULT 'no-trailer.jpg',
rating DECIMAL(2,1),
genres VARCHAR(50) NOT NULL,
status INTEGER UNSIGNED NOT NULL,
category ENUM('Movie', 'Serie') NOT NULL
);Los tipos de datos que estamos utilizando en esta sentencia son:
- CHAR: contiene justo los caracteres que le ponemos entre paréntesis.
- VARCHAR: se adapta a los caracteres que utilice la sentencia, solo utiliza los que hayamos pasado en la sentencia.
- TEXT: Puede contener caracteres ilimitados.
- YEAR: Para indicar el año.
- DECIMAL: Tiene dos argumentos, el valor de número entero y el valor del número decimal.
- ENUM: Como argumentos les pasamos entre comillas los valores que puede adquirir dicho campo.
La siguiente tabla que vamos a crear es la tabla status, que va a tener la siguiente sintaxis.
Ejm
/* Tabla Catálogo */ CREATE TABLE status( status_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT, status VARCHAR(20) NOT NULL );
La siguiente tabla que crearemos será la tabla para nuestros usuarios, cuya sintaxis será la siguiente:
Ejm
/* Tabla de Usuarios */
CREATE TABLE users(
user VARCHAR(15) PRIMARY KEY,
email VARCHAR(80) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
pass CHAR(32) NOT NULL,
role ENUM('Admin', 'User') NOT NULL
);Aprender a leer los errores
Es importante aprender a leer los errores, cuando nos salga algún código de error, mirar en Internet dicho código para saber donde está el error.
Como relacionar unas tablas con otras
Para ello tenemos que hacer uso de lo que se denominan llaves foráneas (foreign key). Por lo que en nuestra tabla películas le tenemos que agregar la llave foránea de la siguiente manera:
Ejm
/* Tabla Catálogo */
CREATE TABLE status(
status_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
status VARCHAR(20) NOT NULL );
/* Tabla de datos */
CREATE TABLE movies(
imdb_id CHAR(9) PRIMARY KEY,
title VARCHAR(80) NOT NULL,
plot TEXT,
author VARCHAR(100) DEFAULT 'Pending',
actors VARCHAR(100) DEFAULT 'Pending',
country VARCHAR(30) DEFAULT 'Unknown',
premiere YEAR(4) NOT NULL,
poster VARCHAR(150) DEFAULT 'no-poster.jpg',
trailer VARCHAR(150) DEFAULT 'no-trailer.jpg',
rating DECIMAL(2,1),
genres VARCHAR(50) NOT NULL,
status INTEGER UNSIGNED NOT NULL,
category ENUM('Movie', 'Serie') NOT NULL,
FOREIGN KEY status REFERENCES status(status_id)
);Creamos primero la tabla status ya que de otra manera la clave foránea creada en la tabla movies no encuentra la tabla status y da error.
Aplicar restricciones
Adicionalmente a las relaciones es importante aplicar restricciones a estas relaciones. Lo que vamos a decirle a nuestra tabla es que al eliminar queremos restricción y al actualizar queremos cascada, de la siguiente manera.
Ejm
/* Tabla de datos */
CREATE TABLE movies(
imdb_id CHAR(9) PRIMARY KEY,
title VARCHAR(80) NOT NULL,
plot TEXT,
author VARCHAR(100) DEFAULT 'Pending',
actors VARCHAR(100) DEFAULT 'Pending',
country VARCHAR(30) DEFAULT 'Unknown',
premiere YEAR(4) NOT NULL,
poster VARCHAR(150) DEFAULT 'no-poster.jpg',
trailer VARCHAR(150) DEFAULT 'no-trailer.jpg',
rating DECIMAL(2,1),
genres VARCHAR(50) NOT NULL,
status INTEGER UNSIGNED NOT NULL,
category ENUM('Movie', 'Serie') NOT NULL,
FOREIGN KEY status REFERENCES status(status_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);Para qué restringir y para qué cascada. Los posibles valores que pueden adquirir las claúsulas ON DELETE y ON UPDATE son:
- RESTRICT: Es el comportamiento por defecto, que impide realizar modificaciones que atentan contra la integridad referencial.
- CASCADE: Borra los registros de la tabla dependiente cuando se borra el registro de la tabla principal (en un sentencia DELETE) o actualiza el valor de la clave secundaria cuando se actualiza el valor de la clave referenciada (en una sentencia UPDATE).
- SET NULL: Establece a NULL el valor de la clave secundaria cuando se elimina el registro en la tabla principal o se modifica el valor del campo seleccionado.
- NO ACTION: Inhabilita el efecto de la restricción, permitiendo que se efectúe el cambio en la base de datos.
En la práctica, con los valores que se suele trabajar es con RESTRICT y con CASCADE.
Búsquedas FULL TEXT
Las búsquedas de tipo FULL TEXT es lo que permite hacer búsquedas tipo buscador como en Google. La sintaxis que se pone para incluir este tipo de sentencia es la siguiente.
Ejm
FULLTEXT KEY search(title, author, actors, genres)
Los campos de tipo TEXT no funcionan con este tipo de búsqueda. La tabla peliculas quedaría de la siguiente manera.
Ejm
/* Tabla de datos */
CREATE TABLE movies(
imdb_id CHAR(9) PRIMARY KEY,
title VARCHAR(80) NOT NULL,
plot TEXT,
author VARCHAR(100) DEFAULT 'Pending',
actors VARCHAR(100) DEFAULT 'Pending',
country VARCHAR(30) DEFAULT 'Unknown',
premiere YEAR(4) NOT NULL,
poster VARCHAR(150) DEFAULT 'no-poster.jpg',
trailer VARCHAR(150) DEFAULT 'no-trailer.jpg',
rating DECIMAL(2,1),
genres VARCHAR(50) NOT NULL,
status INTEGER UNSIGNED NOT NULL,
category ENUM('Movie', 'Serie') NOT NULL,
FULLTEXT KEY search(title, author, actors, genres),
FOREIGN KEY (status) REFERENCES status(status_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);Resumen
Esta es la sintaxis SQL que hemos generado hasta ahora.
Ejm
/*
Base de datos de películas y series
*/
DROP DATABASE IF EXISTS peliculas;
CREATE DATABASE IF NOT EXISTS peliculas;
USE peliculas;
/* Tabla Catálogo */
CREATE TABLE status(
status_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
status VARCHAR(20) NOT NULL
);
/* Tabla de datos */
CREATE TABLE movies(
imdb_id CHAR(9) PRIMARY KEY,
title VARCHAR(80) NOT NULL,
plot TEXT,
author VARCHAR(100) DEFAULT 'Pending',
actors VARCHAR(100) DEFAULT 'Pending',
country VARCHAR(30) DEFAULT 'Unknown',
premiere YEAR(4) NOT NULL,
poster VARCHAR(150) DEFAULT 'no-poster.jpg',
trailer VARCHAR(150) DEFAULT 'no-trailer.jpg',
rating DECIMAL(2,1),
genres VARCHAR(50) NOT NULL,
status INTEGER UNSIGNED NOT NULL,
category ENUM('Movie', 'Serie') NOT NULL,
FULLTEXT KEY search(title, author, actors, genres),
FOREIGN KEY (status) REFERENCES status(status_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
/* Tabla de Usuarios */
CREATE TABLE users(
user VARCHAR(15) PRIMARY KEY,
email VARCHAR(80) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
pass CHAR(32) NOT NULL,
role ENUM('Admin', 'User') NOT NULL
);