Apuntes mysql

“SET PASSWORD has no significance for user 'root'@'localhost'”

1. Consultas avanzadas y análisis de datos: Introducción

Veamos análisis mas complejos de datos, unión de múltiples tablas, funciones agregadas y subconsultas.

2. JOIN múltiple - Navegando entre varias tablas

En bases de datos reales, es común necesitar información de 3, 4 o más tablas relacionadas. Aquí es donde SQL se pone realmente interesante.

Ejemplo complejo: Actores en películas específicas

Si queremos saber qué actores aparecen en una película concreta, necesitamos navegar por tres tablas:

SELECT f.title, a.first_name, a.last_name
FROM film f
JOIN film_actor fa ON f.film_id = fa.film_id
JOIN actor a ON fa.actor_id = a.actor_id
WHERE f.title = 'ACADEMY DINOSAUR';

o lo que es lo mismo: "Selecciona f.title (título), a.first_name (nombre), a.last_name (apellidos)
DE LA TABLA film como 'f',
ÚNELO CON film_actor como 'fa' CUANDO f.film_id sea igual a fa.film_id,
ÚNELO CON actor como 'a' CUANDO fa.actor_id sea igual a a.actor_id,
DONDE f.title sea igual a 'ACADEMY DINOSAUR'"".

O incluso mas claro: "Para la película de la tabla "film" llamada "ACADEMY DINOSAUR", buscar en la tabla "film_actor" las entradas que coincidan con su "film_id", por cada actor relacionado, buscar su información en la tabla "actor" usando el "actor_id", mostrar el título de la película, el nombre y apellido del actor. "

Este JOIN múltiple nos lleva desde la tabla film → film_actor → actor, enlazando por las claves correspondientes.

Ejemplo más complejo: Información completa del alquiler

SELECT
    c.first_name AS cliente,
    c.last_name AS apellido,
    f.title AS pelicula,
    cat.name AS categoria,
    r.rental_date AS fecha_alquiler,
    s.first_name AS empleado
FROM rental r
JOIN customer c ON r.customer_id = c.customer_id
JOIN staff s ON r.staff_id = s.staff_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category cat ON fc.category_id = cat.category_id
LIMIT 10;

Esta consulta une 7 tablas para mostrar información completa de cada alquiler.
En una pseudo traducción sería algo asi como "Obtener los primeros 10 registros de alquiler. Para cada alquiler encontrado:

  • - Obtener el nombre y apellido del cliente desde la tabla "customer".
  • - Obtener el título de la película desde la tabla "film", usando la relación de inventario.
  • - Obtener el nombre de la categoría de la película desde la tabla "category".
  • - Obtener la fecha del alquiler desde la tabla "rental".
  • - Obtener el nombre del empleado que procesó el alquiler desde la tabla "staff".
  • - Mostrar: nombre del cliente, apellido del cliente, título de la película, categoría, fecha del alquiler, nombre del empleado."

3. Tipos de JOIN avanzados

LEFT JOIN - Incluir registros sin correspondencia

A diferencia de INNER JOIN, LEFT JOIN muestra todos los registros de la tabla izquierda, aunque no tengan correspondencia en la derecha.

-- Todos los clientes, aunque no hayan alquilado nada
SELECT c.first_name, c.last_name, COUNT(r.rental_id) AS total_alquileres
FROM customer c
LEFT JOIN rental r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_alquileres;

RIGHT JOIN - Menos común pero útil

-- Todas las películas, aunque no estén en inventario
SELECT f.title, COUNT(i.inventory_id) AS copias_disponibles
FROM inventory i
RIGHT JOIN film f ON i.film_id = f.film_id
GROUP BY f.film_id, f.title
ORDER BY copias_disponibles;

4. Funciones agregadas - Análisis estadístico

Las funciones agregadas nos permiten resumir y analizar grandes cantidades de datos.

Funciones principales

  • COUNT(*): cuenta registros
  • SUM(columna): suma valores numéricos
  • AVG(columna): calcula el promedio
  • MIN(columna), MAX(columna): mínimo y máximo

Ejemplos prácticos

Estadísticas básicas:

SELECT
    COUNT(*) AS total_peliculas,
    AVG(rental_rate) AS precio_medio,
    MIN(rental_rate) AS precio_minimo,
    MAX(rental_rate) AS precio_maximo,
    SUM(replacement_cost) AS valor_total_inventario
FROM film;

Análisis por categoría:

SELECT
    c.name AS categoria,
    COUNT(*) AS numero_peliculas,
    AVG(f.rental_rate) AS precio_promedio,
    AVG(f.length) AS duracion_promedio
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY numero_peliculas DESC;

5. GROUP BY - Agrupar para analizar

GROUP BY permite agrupar registros con características similares para aplicar funciones agregadas.

Ejemplo básico: Películas por categoría

SELECT c.name, COUNT(*) AS total
FROM film_category fc
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total DESC;

Ejemplo avanzado: Análisis de clientes

SELECT
    c.first_name,
    c.last_name,
    COUNT(r.rental_id) AS total_alquileres,
    SUM(p.amount) AS dinero_gastado,
    AVG(p.amount) AS gasto_promedio
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY dinero_gastado DESC
LIMIT 10;

6. HAVING - Filtrar grupos

HAVING funciona como WHERE, pero se aplica después de agrupar los datos.

Diferencia clave:

  • WHERE filtra registros individuales antes de agrupar
  • HAVING filtra grupos después de agrupar

Ejemplo: Categorías populares

SELECT c.name, COUNT(*) AS total
FROM film_category fc
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
HAVING total > 60
ORDER BY total DESC;

Ejemplo complejo: Clientes VIP

SELECT
    c.first_name,
    c.last_name,
    COUNT(r.rental_id) AS total_alquileres,
    SUM(p.amount) AS dinero_gastado
FROM customer c
JOIN rental r ON c.customer_id = r.customer_id
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING total_alquileres > 30 AND dinero_gastado > 150
ORDER BY dinero_gastado DESC;

7. Subconsultas - Consultas dentro de consultas

¿Qué son las subconsultas?

Una subconsulta es una consulta SQL que está anidada dentro de otra consulta. Es como resolver un problema complejo dividiéndolo en pasos más pequeños.

¿Por qué usar subconsultas?

  • Permiten resolver problemas que no se pueden hacer con una sola consulta
  • Hacen el código más legible al dividir la lógica en pasos
  • Te permiten usar el resultado de una consulta como filtro para otra

Analogía: Es como preguntar "¿Cuáles son los actores de la película más cara?" - primero necesitas encontrar cuál es la película más cara, luego buscar sus actores.

Tipos de subconsultas

1. Subconsulta escalar (devuelve un solo valor)

Problema: Queremos encontrar los actores que aparecen en la película más cara de alquilar.

Pensamiento paso a paso:

  1. Primero: ¿Cuál es el precio más alto? → SELECT MAX(rental_rate) FROM film
  2. Segundo: ¿Qué actores están en películas con ese precio?
SELECT a.first_name, a.last_name
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film f ON fa.film_id = f.film_id
WHERE f.rental_rate = (
    SELECT MAX(rental_rate) FROM film  -- Esta subconsulta devuelve un número: 4.99
);

2. Subconsulta con IN (devuelve múltiples valores)

Problema: Queremos todas las películas de la categoría que tiene más películas.

Pensamiento paso a paso:

  1. ¿Cuál es la categoría con más películas?
  2. ¿Qué películas pertenecen a esa categoría?
SELECT title, rental_rate
FROM film
WHERE film_id IN (
    SELECT fc.film_id
    FROM film_category fc
    WHERE fc.category_id = (
        SELECT category_id
        FROM film_category
        GROUP BY category_id
        ORDER BY COUNT(*) DESC
        LIMIT 1  -- La categoría con más películas
    )
);

3. Subconsulta en FROM (crea una tabla temporal)

Problema: Queremos clasificar las categorías según su popularidad.

¿Por qué usar subconsulta en FROM? Porque necesitamos primero calcular estadísticas (contar películas por categoría) y luego trabajar con esos resultados calculados.

SELECT
    categoria,
    numero_peliculas,
    CASE
        WHEN numero_peliculas > 65 THEN 'Popular'
        WHEN numero_peliculas > 55 THEN 'Moderada'
        ELSE 'Nicho'
    END AS popularidad
FROM (
    -- Esta subconsulta crea una "tabla temporal" con estadísticas
    SELECT
        c.name AS categoria,
        COUNT(*) AS numero_peliculas
    FROM film_category fc
    JOIN category c ON fc.category_id = c.category_id
    GROUP BY c.name
) AS estadisticas_categoria  -- Le damos un nombre a nuestra tabla temporal
ORDER BY numero_peliculas DESC;

8. Casos de uso avanzados

Análisis de rentabilidad

-- Películas más rentables por días de alquiler
SELECT
    f.title,
    f.rental_rate,
    f.rental_duration,
    (f.rental_rate / f.rental_duration) AS rentabilidad_diaria,
    COUNT(r.rental_id) AS veces_alquilada
FROM film f
LEFT JOIN inventory i ON f.film_id = i.film_id
LEFT JOIN rental r ON i.inventory_id = r.inventory_id
GROUP BY f.film_id, f.title, f.rental_rate, f.rental_duration
HAVING veces_alquilada > 0
ORDER BY rentabilidad_diaria DESC
LIMIT 10;

Análisis temporal

-- Alquileres por mes
SELECT
    YEAR(rental_date) AS año,
    MONTH(rental_date) AS mes,
    COUNT(*) AS total_alquileres,
    SUM(p.amount) AS ingresos_mes
FROM rental r
JOIN payment p ON r.rental_id = p.rental_id
GROUP BY YEAR(rental_date), MONTH(rental_date)
ORDER BY año, mes;

Optimización básica

¿Por qué importa la optimización? Una consulta mal escrita puede tardar minutos en ejecutarse cuando debería tardar segundos. Con bases de datos grandes, esto es crítico.

¿Qué son los índices?

Los índices son como el índice de un libro: te permiten encontrar información rápidamente sin leer todo el contenido. En SQL, un índice en una columna hace que las búsquedas por esa columna sean muchísimo más rápidas.

Ejemplo: Si buscas frecuentemente por customer.email, crear un índice en esa columna acelera drásticamente las consultas.

Consejos prácticos para consultas eficientes:

1. Usa índices estratégicamente

  • Las columnas que aparecen en WHERE y JOIN deben tener índices
  • Las claves primarias y foráneas ya tienen índices automáticamente

2. Sé específico con las columnas

--  Lento: trae datos innecesarios
SELECT * FROM film WHERE title = 'ACADEMY DINOSAUR';

--  Rápido: solo trae lo que necesitas
SELECT title, rental_rate FROM film WHERE title = 'ACADEMY DINOSAUR';

3. Ordena las condiciones inteligentemente

--  Mejor: condición más selectiva primero
SELECT title FROM film
WHERE rental_rate > 4.00 AND rating = 'PG-13';  -- Pocas películas cuestan >$4

--  Peor: condición menos selectiva primero
SELECT title FROM film
WHERE rating = 'PG-13' AND rental_rate > 4.00;  -- Muchas películas son PG-13

4. Usa LIMIT cuando solo necesites algunos resultados

-- Si solo necesitas 10 resultados, díselo a la base de datos
SELECT title, rental_rate FROM film
ORDER BY rental_rate DESC
LIMIT 10;

¿Qué es una vista?

Una vista es como una "consulta guardada" que se comporta como una tabla virtual. Imagina que tienes una consulta compleja que usas frecuentemente - en lugar de escribirla cada vez, puedes guardarla como vista y consultarla como si fuera una tabla normal.

Ventajas de las vistas:

  • Simplifican consultas complejas repetitivas
  • Ocultan la complejidad del modelo de datos
  • Mejoran la seguridad (puedes dar acceso a la vista sin exponer las tablas base)
  • Centralizan la lógica de negocio

Ejemplo de vista:

-- Crear la vista (se hace una sola vez)
CREATE VIEW alquileres_completos AS
SELECT
    c.first_name AS cliente,
    f.title AS pelicula,
    cat.name AS categoria,
    r.rental_date,
    p.amount AS precio
FROM rental r
JOIN customer c ON r.customer_id = c.customer_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category cat ON fc.category_id = cat.category_id
JOIN payment p ON r.rental_id = p.rental_id;

-- Usar la vista (como si fuera una tabla)
SELECT cliente, pelicula FROM alquileres_completos
WHERE categoria = 'Action'
LIMIT 10;

Sin la vista, tendrías que escribir todo el JOIN complejo cada vez que quisieras esta información.

TOP