MySQL — The basics

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.

Expected outcome :

Reproduce a working result, test it, then succeed at an exercise without copying.

Reading
0%

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.

SELECT VERSION();
SHOW DATABASES;
Remember :

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

SOURCE database/schema.sql;
Remember :

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.

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

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.

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

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.

Exercise · A quick menu

Display the title, duration and category of recipes of 20 minutes at most, from the quickest to the longest. Choose the columns explicitly.

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

Solution · Filter, join, sort

Combine the clauses already practised; the filter uses a recettes column and the display uses a categories field.

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.

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.

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

Final expected: total = 3 if the database stayed in its reference state.