Guide 03 / 4
MySQL workshop · À table !
Create a database of recipes and categories, insert the same dataset as in the previous courses, then write searches, joins and controlled modifications.
Reproduce a working result, test it, then succeed at an exercise without copying.
01 · Creating and populating the database
The complete schema.sql file ends the workshop. It is meant for a new database named blog_recettes.
Step 1 · Preparing an exercise database
Check the connection and the server version. If blog_recettes already exists, use another exercise name by adapting USE and later DB_NAME. Keep your existing data.
Example
SELECT VERSION();
SHOW DATABASES;
The script cannot be rerun as is on an existing database: CREATE DATABASE would fail and inserting the initial dataset again would create inconsistencies.
Step 2 · Running the complete script
Create mini-blog/database/schema.sql and copy the complete file into it. Run it in the SQL editor or with SOURCE in the mysql client, from mini-blog/.
Example
SOURCE database/schema.sql;
Expected: two tables, three categories and three recipes. The last query displays title, duration and category.
Step 3 · Observing the links
Compare the categorie_id values in recettes with the id values in categories. Run the join. Work out why the soup uses 2 while the salad uses 1.
Example
SELECT * FROM categories ORDER BY id;
SELECT id, titre, categorie_id FROM recettes ORDER BY id;
Expected: 1 = Entrée, 2 = Plat, 3 = Dessert on this fresh initial dataset.
Step 4 · Checking the rules
In a trial transaction, try adding a category that already exists or a recipe with a non-existent category. Read the error then cancel. Do not disable the constraints to work around the problem.
Example
START TRANSACTION;
INSERT INTO categories (nom) VALUES ('Plat');
-- Une erreur de doublon est attendue.
ROLLBACK;
Expected: the category Plat stays unique. Continue manually after the error if the client interrupts the batch.
02 · Writing and checking your queries
The initial database makes the results easy to compare.
Solution · Filter, join, sort
Combine the clauses already practised; the filter uses a recettes column and the display uses a categories field.
Example
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;
Exercise · Add a compote then cancel
In a transaction, add a 15-minute compote for 2 servings in the Dessert category, find it with SELECT, then cancel the trial.
Example
Attendu : la compote apparaît avant ROLLBACK et disparaît ensuite. Les trois recettes initiales restent présentes.
Solution · A controlled write
First check Dessert's id: 3 in the initial dataset. The AUTO_INCREMENT counter may advance despite the cancellation; this is normal.
Example
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;
Final expected: total = 3 if the database stayed in its reference state.