Capítulo 5: SQL

Enlaces

Base de datos de ejemplo

Creación

DROP DATABASE IF EXISTS videoteca;
CREATE DATABASE videoteca;

USE videoteca;

DROP TABLE IF EXISTS director;
CREATE TABLE director
(
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(64) NOT NULL,
  apellidos VARCHAR(64) NOT NULL,
  imdb VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS genero;
CREATE TABLE genero
(
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(2) NOT NULL,
  descripcion VARCHAR(32) NOT NULL,
  PRIMARY KEY(id)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS interprete;
CREATE TABLE interprete
(
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(64) NOT NULL,
  apellidos VARCHAR(64) NOT NULL,
  imdb VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY(id)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS soporte;
CREATE TABLE soporte
(
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(3) NOT NULL,
  descripcion VARCHAR(32) NOT NULL,
  PRIMARY KEY(id)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS pelicula;
CREATE TABLE pelicula
(
  id INT NOT NULL AUTO_INCREMENT,
  titulo VARCHAR(64) NOT NULL,
  idsoporte INT NOT NULL,
  idgenero INT NOT NULL,
  PRIMARY KEY(id),
  INDEX p_FK1(idsoporte),
  INDEX p_FK2(idgenero),
  FOREIGN KEY(idsoporte) REFERENCES soporte(id),
  FOREIGN KEY(idgenero) REFERENCES genero(id)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS interpretes_por_pelicula;
CREATE TABLE interpretes_por_pelicula
(
  idpelicula INT NOT NULL,
  idinterprete INT NOT NULL,
  PRIMARY KEY(idpelicula, idinterprete),
  INDEX ipp_FK1(idpelicula),
  INDEX ipp_FK2(idinterprete),
  FOREIGN KEY(idpelicula) REFERENCES pelicula(id),
  FOREIGN KEY(idinterprete) REFERENCES interprete(id)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS directores_por_pelicula;
CREATE TABLE directores_por_pelicula
(
  idpelicula INT NOT NULL,
  iddirector INT NOT NULL,
  PRIMARY KEY(idpelicula, iddirector),
  INDEX dpp_FK1(idpelicula),
  INDEX dpp_FK2(iddirector),
  FOREIGN KEY(idpelicula) REFERENCES pelicula(id),
  FOREIGN KEY(iddirector) REFERENCES director(id)
)
ENGINE = InnoDB;

Inserciones

USE videoteca;

DELETE FROM interpretes_por_pelicula;
DELETE FROM directores_por_pelicula;
DELETE FROM interprete;
DELETE FROM director;
DELETE FROM pelicula;
DELETE FROM soporte;
DELETE FROM genero;

INSERT INTO soporte(nombre, descripcion)
VALUES('DVD', 'Digital Versatile Disc');

INSERT INTO soporte(nombre, descripcion)
VALUES('VHS', 'Video Home System');

INSERT INTO genero(nombre, descripcion)
VALUES('CF', 'Ciencia Ficción');

INSERT INTO genero(nombre, descripcion)
VALUES('A', 'Aventuras');

INSERT INTO genero(nombre, descripcion)
VALUES('D', 'Drama');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Harrison', 'Ford', 'nm0000148');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Russell', 'Crowe', 'nm0000128');

INSERT INTO director(nombre, apellidos, imdb)
VALUES('Ridley', 'Scott', 'nm0000631');

INSERT INTO director(nombre, apellidos, imdb)
VALUES('Mike', 'Nichols', 'nm0001566');

INSERT INTO pelicula(titulo, idsoporte, idgenero)
VALUES('Blade Runner', 1, 1);

INSERT INTO pelicula(titulo, idsoporte, idgenero)
VALUES('Gladiator', 1, 2);

INSERT INTO pelicula(titulo, idsoporte, idgenero)
VALUES('A propósito de Henry', 2, 3);

INSERT INTO interpretes_por_pelicula
(
  idpelicula,
  idinterprete
)
VALUES
(
  1,
  1
);

INSERT INTO interpretes_por_pelicula
(
  idpelicula,
  idinterprete
)
VALUES
(
  2,
  2
);

INSERT INTO interpretes_por_pelicula
(
  idpelicula,
  idinterprete
)
VALUES
(
  3,
  1
);

INSERT INTO directores_por_pelicula
(
  idpelicula,
  iddirector
)
VALUES
(
  1,
  1
);

INSERT INTO directores_por_pelicula
(
  idpelicula,
  iddirector
)
VALUES
(
  2,
  1
);

INSERT INTO directores_por_pelicula
(
  idpelicula,
  iddirector
)
VALUES
(
  3,
  2
);

Más inserciones:

USE videoteca;

INSERT INTO soporte(nombre, descripcion)
VALUES('LD', 'Laser Disc');

INSERT INTO genero(nombre, descripcion)
VALUES('C', 'Comedia');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Lee', 'Marvin', 'nm0001511');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Clint', 'Eastwood', 'nm0000142');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Jean', 'Seberg', 'nm0781029');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Bruce', 'Dern', 'nm0001136');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Bruce', 'Boxleitner', 'nm0000310');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Rutger', ' Hauer', 'nm0000442');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Sean', 'Young', 'nm0000707');

INSERT INTO interprete(nombre, apellidos, imdb)
VALUES('Matthew', 'McConaughey', 'nm0000190');

INSERT INTO director(nombre, apellidos, imdb)
VALUES('Robert', 'Zemekis', 'nm0000709');

INSERT INTO director(nombre, apellidos, imdb)
VALUES('Douglas', 'Trumbull', 'nm0874320');

INSERT INTO pelicula(titulo, idsoporte, idgenero)
VALUES('Naves misteriosas', 1, 1);

Operadores

Operadores aritméticos

Base de datos para pruebas:

DROP DATABASE IF EXISTS pruebas;
CREATE DATABASE pruebas;

Tabla para operadores:

USE pruebas;

DROP TABLE IF EXISTS operadores;
CREATE TABLE operadores
(
  uno TINYINT UNSIGNED,
  otro TINYINT UNSIGNED
);

Valores para las pruebas:

USE pruebas;

INSERT INTO operadores VALUES(3, 7);

Operación de prueba:

USE pruebas;

SELECT uno + otro FROM operadores;

Otra inserción:

USE pruebas;

INSERT INTO operadores VALUES(300, 500);

Otra operación de prueba:

USE pruebas;

SELECT uno + otro FROM operadores;

Operadores de comparación

NULL no es igual a NULL:

SELECT 7 = 7, NULL = NULL;

NULL sí es igual a NULL:

SELECT 7 <=>7, NULL <=>NULL;

Buscar al protagonista de Gladiator:

USE videoteca;

SELECT
  *
FROM
  interprete
WHERE
  apellidos = 'Crow';

Nuevo intento:

USE videoteca;

SELECT
  *
FROM
  interprete
WHERE
  apellidos LIKE 'Crow%';

Obtención del mismo resultado:

USE videoteca;

SELECT
  *
FROM
  interprete
WHERE
  apellidos LIKE 'Crow_';

Operadores lógicos

SELECT
  0 AND 0,
  0 AND 1,
  1 AND 0,
  1 AND 1;

Precedencia de operadores

SELECT
  2 + 2 * 5,
  (2 + 2) * 5;

Consultas de selección

Lista de intérpretes:

USE videoteca;
SELECT
  *
FROM
  interprete;

Sólo algunos campos de la tabla de intérpretes:

USE videoteca;

SELECT
  id,
  nombre,
  apellidos
FROM
  interprete;

Concatenando campos:

USE videoteca;

SELECT
  id,
  CONCAT(nombre, ' ', apellidos)
FROM
  interprete;

Cambio del nombre de una columna del resultado:

USE videoteca;

SELECT
  id,
  CONCAT(nombre, ' ', apellidos) AS nombre
FROM
  interprete;

Ordenada por apellidos:

USE videoteca;

SELECT
  id,
  CONCAT(nombre, ' ', apellidos) AS nombre
FROM
  interprete
ORDER BY
  apellidos;

Ordenar por la concatenación:

USE videoteca;

SELECT
  id,
  CONCAT(nombre, ' ', apellidos) AS nombre
FROM
  interprete
ORDER BY
  2;

Número de elementos en una tabla:

USE videoteca;

SELECT
  COUNT(*)
FROM
  interprete;

Renombrar la columna del resultado:

USE videoteca;

SELECT
  COUNT(*) interpretes
FROM
  interprete;

Listados de varias tablas

Esta consulta no es útil para obtener información dispersa entre varias tablas:

USE videoteca;

SELECT
  *
FROM
  pelicula,
  interpretes_por_pelicula,
  interprete;

Pero ésta sí:

USE videoteca;

SELECT
  titulo,
  CONCAT(nombre, ' ', apellidos) AS interprete
FROM
  pelicula p
  JOIN interpretes_por_pelicula ipp ON p.id = ipp.idpelicula
  JOIN interprete i ON ipp.idinterprete = i.id;

También el director:

USE videoteca;

SELECT
  p.titulo,
  CONCAT(i.nombre, ' ', i.apellidos) AS interprete,
  CONCAT(d.nombre, ' ', d.apellidos) AS director
FROM
  pelicula p
  JOIN interpretes_por_pelicula ipp ON p.id = ipp.idpelicula
  JOIN interprete i ON ipp.idinterprete = i.id
  JOIN directores_por_pelicula dpp ON p.id = dpp.idpelicula
  JOIN director d ON dpp.iddirector = d.id;

Número de películas de cada intérprete:

USE videoteca;

SELECT
  CONCAT(i.apellidos, ', ', i.nombre) interprete,
  COUNT(ipp.idpelicula) peliculas
FROM
  interprete i
  JOIN interpretes_por_pelicula ipp ON i.id = ipp.idinterprete
GROUP BY
  i.id
ORDER BY
  i.apellidos,
  i.nombre;

Películas de cada intérprete, incluso si no tienen:

USE videoteca;

SELECT
  CONCAT(i.apellidos, ', ', i.nombre) interprete,
  COUNT(ipp.idpelicula) peliculas
FROM
  interprete i
  LEFT JOIN interpretes_por_pelicula ipp ON i.id = ipp.idinterprete
GROUP BY
  i.id
ORDER BY
  i.apellidos,
  i.nombre;

Identificador de la película Blade Runner:

USE videoteca;

SELECT
  id
FROM
  pelicula
WHERE
  titulo = 'Blade Runner';

Identificador de Rutger Hauer:

USE videoteca;

SELECT
  id
FROM
  interprete
WHERE
  nombre = 'Rutger' AND
  apellidos = ' Hauer';

Y el de Sean Young:

USE videoteca;

SELECT
  id
FROM
  interprete
WHERE
  nombre = 'Sean' AND
  apellidos = 'Young';

Actualizaciones

Cambio del apellido de un intérprete:

USE videoteca;

UPDATE
  interprete
SET
  apellidos = 'Hauer'
WHERE
  nombre = 'Rutger';

Eliminación de todos los espacios sobrantes:

USE videoteca;

UPDATE
  interprete i,
  director d
SET
  i.nombre = TRIM(i.nombre),
  i.apellidos = TRIM(i.apellidos),
  d.nombre = TRIM(d.nombre),
  d.apellidos = TRIM(d.apellidos);

Inserciones

Añadir intépretes a una película:

USE videoteca;

INSERT INTO interpretes_por_pelicula
(
  idpelicula, idinterprete
)
VALUES
(
  1,
  8
);

También se puede hacer así:

USE videoteca;

INSERT INTO interpretes_por_pelicula
VALUES(1, 9);

Comprobar el resultado de estas operaciones:

USE videoteca;

SELECT
  titulo,
  CONCAT(nombre, ' ', apellidos) AS interprete
FROM
  pelicula p
  JOIN interpretes_por_pelicula ipp ON p.id = ipp.idpelicula
  JOIN interprete i ON ipp.idinterprete = i.id
WHERE
  p.titulo = 'Blade Runner';

Intérpretes por película:

USE videoteca;

SELECT
  p.titulo,
  COUNT(ipp.idinterprete) interpretes
FROM
  pelicula p
  LEFT JOIN interpretes_por_pelicula ipp ON p.id = ipp.idpelicula
GROUP BY
  p.id;

No más interpretes sin películas:

USE videoteca;

INSERT INTO interpretes_por_pelicula
(
  idpelicula,
  idinterprete
)
VALUES
(
  4,
  6
);

Borrados

Lista de soportes:

USE videoteca;

SELECT
  *
FROM
  soporte;

¿Existen películas en todos los soportes?

USE videoteca;

SELECT
  s.nombre,
  COUNT(p.id) peliculas
FROM
  soporte s
  LEFT JOIN pelicula p ON s.id = p.idsoporte
GROUP BY
  s.id;

Fuera el Laser Disc:

USE videoteca;

DELETE FROM
  soporte
WHERE
  id = 3;