Guide 02 / 4
Les essentiels de MySQL
Un aide-mémoire expliqué : à quoi sert chaque élément, comment l’écrire et quand l’utiliser.
Choisir et utiliser les éléments essentiels pour réaliser cette étape du blog.
01 · Se connecter et définir le modèle
Les commandes de terminal ne se collent pas dans l’éditeur SQL ; les requêtes SQL ne se collent pas directement dans le shell.
Client mysql et sélection de la base
Dans le terminal, ouvrir le client avec son compte. À l’invite mysql>, sélectionner la base puis consulter les tables. -p demande le mot de passe sans l’inscrire dans la commande.
Exemple
Terminal : mysql -u votre_compte -p
SQL : USE blog_recettes;
SQL : SHOW TABLES;
SQL : DESCRIBE recettes;
Les préfixes « Terminal : » et « SQL : » sont des explications, pas une partie des commandes.
CREATE DATABASE et CREATE TABLE
Créer d’abord la base, puis la table parent categories, puis recettes qui la référence. Le fichier database/schema.sql complet est en fin d’atelier.
Exemple
CREATE TABLE categories (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB;
Types du mini-blog
VARCHAR convient aux titres courts ; TEXT aux ingrédients et étapes ; SMALLINT et TINYINT aux petites valeurs entières ; TIMESTAMP à la date de création.
Exemple
titre VARCHAR(120)
ingredients TEXT
duree_minutes SMALLINT UNSIGNED
portions TINYINT UNSIGNED
cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
On conserve ingrédients et étapes sous forme de lignes de texte pour ce premier projet ; leur modélisation détaillée viendra dans un cours plus avancé.
NOT NULL, UNIQUE, CHECK et FOREIGN KEY
NOT NULL impose une présence ; UNIQUE empêche les doublons d’une valeur ; CHECK impose une condition ; FOREIGN KEY vérifie une relation. Les deux colonnes liées doivent avoir des types compatibles.
Exemple
CONSTRAINT ck_duree CHECK (duree_minutes BETWEEN 1 AND 1440),
CONSTRAINT fk_categorie FOREIGN KEY (categorie_id)
REFERENCES categories(id) ON DELETE RESTRICT
ON DELETE RESTRICT refuse la suppression d’une catégorie encore utilisée.
02 · Insérer et interroger
Énumérer les colonnes rend les requêtes lisibles et indépendantes de leur ordre physique.
INSERT INTO et VALUES
INSERT ajoute une ligne. Les chaînes utilisent des apostrophes ; une apostrophe interne se double. Le moteur produit l’id si on l’omet.
Exemple
INSERT INTO categories (nom) VALUES ('Goûter');
INSERT INTO categories (nom) VALUES ('Chef d''un jour');
Dans le code PHP du cours PDO, on transmettra les valeurs par paramètres plutôt que fabriquer les chaînes SQL.
SELECT, FROM et alias
SELECT choisit les colonnes ; FROM indique la table. Un alias nomme temporairement une table ou une colonne dans le résultat.
Exemple
SELECT r.id, r.titre, r.duree_minutes AS duree
FROM recettes AS r;
WHERE, AND et OR
WHERE sélectionne les lignes selon une condition. AND exige les deux critères. Ajouter des parenthèses rend les combinaisons avec OR explicites.
Exemple
SELECT id, titre FROM recettes
WHERE duree_minutes <= 20 AND portions = 2;
Attendu sur les données initiales : soupe et salade.
LIKE et caractères génériques
LIKE cherche un motif : % représente zéro ou plusieurs caractères ; _ en représente un. La sensibilité à la casse et aux accents dépend de la collation.
Exemple
SELECT titre FROM recettes WHERE titre LIKE '%pommes%';
Attendu : Tarte aux pommes. Les symboles % et _ ont un sens spécial même quand le motif est transmis par un paramètre PDO.
ORDER BY et LIMIT
ORDER BY trie les résultats. LIMIT en réduit le nombre. Ajouter un id au tri départage les égalités pour un résultat stable.
Exemple
SELECT id, titre, duree_minutes FROM recettes
ORDER BY duree_minutes ASC, id ASC
LIMIT 2;
Attendu : salade (10), puis soupe (20). Sans ORDER BY, aucun ordre d’affichage n’est garanti.
JOIN et condition ON
JOIN relie chaque recette à sa catégorie. Les alias r et c raccourcissent la requête et lèvent les ambiguïtés entre les colonnes id.
Exemple
SELECT r.titre, c.nom AS categorie
FROM recettes AS r
JOIN categories AS c ON c.id = r.categorie_id
ORDER BY r.id;
COUNT et GROUP BY
COUNT(*) compte les lignes. GROUP BY regroupe les lignes qui partagent une valeur pour calculer un total par groupe.
Exemple
SELECT c.nom, COUNT(r.id) AS total
FROM categories AS c
LEFT JOIN recettes AS r ON r.categorie_id = c.id
GROUP BY c.id, c.nom
ORDER BY c.id;
LEFT JOIN garde aussi une catégorie sans recette ; COUNT(r.id) vaut alors 0, contrairement à COUNT(*).
03 · Modifier avec un périmètre clair
S’entraîner uniquement dans la base du mini-blog.
UPDATE avec WHERE
UPDATE modifie les lignes sélectionnées. Faire d’abord un SELECT avec le même WHERE pour vérifier le périmètre.
Exemple
SELECT id, titre, duree_minutes FROM recettes WHERE id = 1;
START TRANSACTION;
UPDATE recettes SET duree_minutes = 25 WHERE id = 1;
SELECT id, titre, duree_minutes FROM recettes WHERE id = 1;
ROLLBACK;
Attendu : la durée revient à 20 après ROLLBACK. Sans WHERE, toutes les lignes seraient concernées.
DELETE dans une transaction d’essai
DELETE retire des lignes. Dans cet essai, on observe le résultat puis on annule. Exécuter les instructions sur la même connexion.
Exemple
START TRANSACTION;
DELETE FROM recettes WHERE id = 3;
SELECT COUNT(*) AS total FROM recettes;
ROLLBACK;
SELECT COUNT(*) AS total FROM recettes;
Attendu sur la base initiale : 2 pendant l’essai, puis 3 après annulation. Le script fourni ne supprime aucune table.