MySQL — As bases

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.

Resultado esperado :

Reproduzir uma realização funcional, testá-la e depois resolver um exercício sem copiar.

Leitura
0%

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.

SELECT VERSION();
SHOW DATABASES;
A reter :

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/.

SOURCE database/schema.sql;
A reter :

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.

SELECT * FROM categories ORDER BY id;
SELECT id, titre, categorie_id FROM recettes ORDER BY id;
A reter :

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.

START TRANSACTION;
INSERT INTO categories (nom) VALUES ('Plat');
-- Une erreur de doublon est attendue.
ROLLBACK;
A reter :

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.

Exercício · Um menu rápido

Apresentar o título, a duração e a categoria das receitas de 20 minutos no máximo, da mais rápida à mais longa. Escolher explicitamente as colunas.

Attendu :
Salade de tomates | 10 | Entrée
Soupe de carottes | 20 | Plat

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.

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.

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.

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;
A reter :

Esperado final: total = 3 se a base de dados tiver ficado no seu estado de referência.