Guide 01 / 4
MySQL glossary
Give meaning to MySQL terms by tying them to the recipe mini-blog.
Explain the concepts in your own words and recognise them in an example.
01 · Getting your bearings before you start
Step 5/6 of the “À table !” running project, a recipe mini-blog. Go at your own pace: read, reproduce, modify, then check.
The path and the outcome of this step
Create a database of recipes and categories, insert the same dataset as in the previous courses, then write searches, joins and controlled modifications. The same dataset runs through all six courses: Soupe de carottes (20 min), Salade de tomates (10 min), Tarte aux pommes (45 min).
Example
01. HTML : structurer trois recettes dans une page sémantique.
02. CSS : rendre le blog lisible sur mobile et sur ordinateur.
03. JavaScript : filtrer les recettes sans recharger la page.
04. PHP : produire les pages depuis des tableaux et traiter un formulaire.
05. MySQL : conserver les recettes dans une base relationnelle.
06. PDO : lire et ajouter des recettes depuis PHP avec des requêtes préparées.
Each course has four guides: glossary, essentials, workshop and resources. The complete files are at the end of the workshop.
Prerequisites and environment
Know the mini-blog and its fields; having read the PHP course helps to understand the need for persistence, but SQL queries can be practised independently. Plan for a MySQL 8.4 server, a local account allowed to create an exercise database and the mysql client or an SQL editor. Do not confuse the client, the server and a graphical tool such as phpMyAdmin. If you use MariaDB, consult its documentation for the differences.
Example
mini-blog/
index.html # HTML, CSS et JavaScript
style.css # ajouté à l’étape CSS
app.js # ajouté à l’étape JavaScript
public/ # pages PHP accessibles par HTTP
includes/ # fonctions, connexion et données PHP
database/ # scripts SQL
Create this working folder separately from the Technipédia course files. The example paths are relative to mini-blog/. Do not replace the HTML page of this platform.
The method: understand, reproduce, transform
Read a sheet, then type its example. Explain what changes on screen. Change one thing at a time and compare with the expected result. Keep a copy of the previous step before moving on.
Example
1. Je lis et je reformule.
2. Je reproduis dans mon mini-blog.
3. Je change une valeur ou une règle.
4. Je vérifie et j’explique le résultat.
Short snippets illustrate a concept; only the files announced as complete are to be copied as they are. Fragments are not to be concatenated into a single file.
02 · Describing relational data
Recipes are separated from their categories to avoid needless repetition.
SQL and MySQL
SQL is a language for defining and querying relational data. MySQL is a system that understands an SQL dialect and keeps the data on the server.
Example
SELECT titre FROM recettes;
The client sends the query to the MySQL server; PHP does not run it itself.
Database, table, row and column
The database groups tables. A table has columns describing the fields; each row represents a record, here a recipe.
Example
Base : blog_recettes
Table : recettes
Ligne : Soupe de carottes
Colonnes : id, titre, duree_minutes, portions…
Type and constraint
The type defines the shape of a value. A constraint imposes a rule: required title, existing category or duration within a range.
Example
titre VARCHAR(120) NOT NULL
duree_minutes SMALLINT UNSIGNED NOT NULL
The database checks complement PHP validation; the interface alone is not enough.
Primary key and AUTO_INCREMENT
The primary key uniquely identifies a row. AUTO_INCREMENT supplies an identifier when none is given.
Example
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
Numbers can have gaps; they are neither display positions nor an exact row counter.
Foreign key and relationship
A foreign key links a value to an existing row of another table. Several recipes can belong to one category.
Example
categories.id 1 ← n recettes.categorie_id
The category's reference is stored rather than repeating its name in each recipe.
NULL and absence
NULL represents an absent or unknown value. It is neither an empty string nor zero. NOT NULL requires a value, but does not forbid an empty string.
Example
SELECT titre FROM recettes WHERE titre IS NOT NULL;
Test for an absence with IS NULL or IS NOT NULL, not with = NULL.
03 · Reading and modifying methodically
Start with a read before any modification.
CRUD
CRUD sums up four operations: create a row, read, update and delete. The main statements are INSERT, SELECT, UPDATE and DELETE.
Example
Créer → INSERT
Lire → SELECT
Modifier → UPDATE
Supprimer → DELETE
CREATE TABLE defines a structure; INSERT creates a row in that structure.
Join
A join associates rows according to a condition. It lets you display a category's name with the recipe's title.
Example
SELECT r.titre, c.nom AS categorie
FROM recettes AS r
JOIN categories AS c ON c.id = r.categorie_id;
Transaction
A transaction groups changes that you can commit with COMMIT or cancel with ROLLBACK, on transactional tables such as InnoDB.
Example
START TRANSACTION;
UPDATE recettes SET duree_minutes = 25 WHERE id = 1;
ROLLBACK;
CREATE TABLE and other structure statements cause implicit commits in MySQL; do not count on ROLLBACK to undo them.
Index
An index helps the server find rows, at the cost of space and work on writes. The project's primary and foreign keys have indexes suited to the relationships.
Example
EXPLAIN SELECT id, titre FROM recettes WHERE categorie_id = 2;
With three rows, you mainly learn to read the plan; an index is not an automatic improvement for every search.
utf8mb4 and collation
utf8mb4 describes the character encoding; the collation governs comparisons and sorting. The project uses utf8mb4_unicode_ci for accented titles and categories.
Example
CREATE DATABASE blog_recettes
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SQL and JavaScript comparisons can differ on case and accents depending on the collation chosen.