Guia 03 / 4
Oficina de MySQL · À table !
Criar uma base de dados de receitas e de categorias, inserir o mesmo conjunto de dados dos cursos anteriores e escrever depois pesquisas, junções e modificações controladas.
Reproduzir uma realização funcional, testá-la e depois resolver um exercício sem copiar.
01 · Criar e alimentar a base de dados
O ficheiro completo schema.sql termina a oficina. Destina-se a uma base de dados nova chamada blog_recettes.
Etapa 1 · Preparar uma base de dados de exercício
Verificar a ligação e a versão do servidor. Se blog_recettes já existir, utilizar outro nome de exercício, adaptando USE e mais tarde DB_NAME. Conservar os dados existentes.
Exemplo
SELECT VERSION();
SHOW DATABASES;
O script não se volta a executar tal como está numa base de dados existente: CREATE DATABASE falharia e uma nova inserção do conjunto inicial criaria incoerências.
Etapa 2 · Executar o script completo
Criar mini-blog/database/schema.sql e copiar para lá o ficheiro completo. Executá-lo no editor SQL ou com SOURCE no cliente mysql, a partir de mini-blog/.
Exemplo
SOURCE database/schema.sql;
Esperado: duas tabelas, três categorias e três receitas. A última consulta apresenta o título, a duração e a categoria.
Etapa 3 · Observar as ligações
Comparar os valores categorie_id em recettes com os id em categories. Executar a junção. Identificar porque a sopa utiliza 2 enquanto a salada utiliza 1.
Exemplo
SELECT * FROM categories ORDER BY id;
SELECT id, titre, categorie_id FROM recettes ORDER BY id;
Esperado: 1 = Entrée, 2 = Plat, 3 = Dessert neste conjunto inicial novo.
Etapa 4 · Verificar as regras
Numa transação de ensaio, tentar acrescentar uma categoria já existente ou uma receita com uma categoria inexistente. Ler o erro e anular. Não desativar as restrições para contornar o problema.
Exemplo
START TRANSACTION;
INSERT INTO categories (nom) VALUES ('Plat');
-- Une erreur de doublon est attendue.
ROLLBACK;
Esperado: a categoria Plat permanece única. Continuar manualmente após o erro se o cliente interromper o lote.
02 · Escrever e verificar as suas consultas
A base de dados inicial torna os resultados fáceis de comparar.
Solução · Filtrar, juntar, ordenar
Combinar as cláusulas já praticadas; o filtro utiliza uma coluna de recettes e a apresentação um campo de categories.
Exemplo
SELECT r.titre, r.duree_minutes, c.nom AS categorie
FROM recettes AS r
JOIN categories AS c ON c.id = r.categorie_id
WHERE r.duree_minutes <= 20
ORDER BY r.duree_minutes ASC, r.id ASC;
Exercício · Acrescentar uma compota e anular
Numa transação, acrescentar uma compota de 15 minutos para 2 pessoas na categoria Dessert, encontrá-la com SELECT e anular depois o ensaio.
Exemplo
Attendu : la compote apparaît avant ROLLBACK et disparaît ensuite. Les trois recettes initiales restent présentes.
Solução · Uma escrita controlada
Verificar primeiro o identificador de Dessert: 3 no conjunto inicial. O contador AUTO_INCREMENT pode avançar apesar da anulação; é normal.
Exemplo
START TRANSACTION;
INSERT INTO recettes (titre, duree_minutes, portions, ingredients, etapes, categorie_id)
VALUES ('Compote de pommes', 15, 2, '2 pommes', 'Cuire puis écraser.', 3);
SELECT id, titre FROM recettes WHERE titre = 'Compote de pommes';
ROLLBACK;
SELECT COUNT(*) AS total FROM recettes;
Esperado final: total = 3 se a base de dados tiver ficado no seu estado de referência.