====== 03 - SQL: DML y consultas ======

El **DML** (*Data Manipulation Language*) opera sobre los datos: insertar, consultar, modificar y eliminar filas.

===== 1. INSERT =====

<sxh sql>
INSERT INTO publisher (name) VALUES ('Anaya');

INSERT INTO book (title, price, publisher_id)
VALUES ('Código limpio', 29.95, 1);

-- varias filas de una vez
INSERT INTO book (title, price, publisher_id)
VALUES ('Clean Code', 34.50, 1), ('Refactoring', 31.00, 1);
</sxh>

Las columnas autogeneradas (''id'') y las que tienen ''DEFAULT'' pueden omitirse.

===== 2. SELECT =====

<sxh sql>
SELECT id, title, price FROM book;

SELECT * FROM book WHERE price > 30;

SELECT title, price
FROM book
WHERE publisher_id = 1 AND price <= 35
ORDER BY price DESC
LIMIT 10;
</sxh>

^ Cláusula ^ Función ^
| ''WHERE'' | Filtra filas |
| ''ORDER BY'' | Ordena el resultado (''ASC''/''DESC'') |
| ''LIMIT'' | Limita el número de filas |
| ''DISTINCT'' | Elimina duplicados |

Operadores de comparación: ''='', ''<>'', ''<'', ''>='', ''BETWEEN'', ''IN'', ''LIKE'' (con ''%'' como comodín), ''IS NULL'' para comparar con ''NULL''.

===== 3. Agregación =====

<sxh sql>
SELECT COUNT(*) FROM book WHERE publisher_id = 1;

SELECT publisher_id, COUNT(*) AS total, AVG(price) AS average_price
FROM book
GROUP BY publisher_id
HAVING COUNT(*) > 5;
</sxh>

^ Función ^ Devuelve ^
| ''COUNT'' | Número de filas |
| ''SUM'' / ''AVG'' | Suma y media |
| ''MIN'' / ''MAX'' | Mínimo y máximo |

''WHERE'' filtra filas **antes** de agrupar; ''HAVING'' filtra los **grupos** ya agregados.

===== 4. JOIN =====

Un ''JOIN'' combina filas de dos tablas por sus claves:

<sxh sql>
SELECT book.title, publisher.name AS publisher
FROM book
INNER JOIN publisher ON book.publisher_id = publisher.id;

-- libros y su autor, atravesando la tabla de unión
SELECT book.title, author.name AS author
FROM book
INNER JOIN book_author ON book_author.book_id = book.id
INNER JOIN author ON author.id = book_author.author_id;
</sxh>

^ Join ^ Devuelve ^
| ''INNER JOIN'' | Solo las filas con correspondencia en ambas tablas |
| ''LEFT JOIN'' | Todas las de la izquierda; ''NULL'' si no hay correspondencia |

<sxh sql>
-- editoriales sin libros (LEFT JOIN + IS NULL)
SELECT publisher.name
FROM publisher
LEFT JOIN book ON book.publisher_id = publisher.id
WHERE book.id IS NULL;
</sxh>

===== 5. UPDATE y DELETE =====

<sxh sql>
UPDATE book SET price = 24.95 WHERE id = 42;

DELETE FROM book WHERE id = 42;
</sxh>

<note warning>''UPDATE'' y ''DELETE'' sin ''WHERE'' afectan a todas las filas. Ejecuta primero el ''SELECT'' con el mismo ''WHERE'' para comprobar qué filas van a cambiar.</note>