04 - SQL: DML y consultas

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

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);

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

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;

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

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;

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

Un JOIN combina filas de dos tablas por sus claves:

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;

-- 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;

UPDATE book SET price = 24.95 WHERE id = 42;
 
DELETE FROM book WHERE id = 42;