MySQL — Les bases

Guide 02 / 4

Les essentiels de MySQL

Un aide-mémoire expliqué : à quoi sert chaque élément, comment l’écrire et quand l’utiliser.

Résultat attendu :

Choisir et utiliser les éléments essentiels pour réaliser cette étape du blog.

Lecture
0%

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.

Terminal : mysql -u votre_compte -p
SQL : USE blog_recettes;
SQL : SHOW TABLES;
SQL : DESCRIBE recettes;
À retenir :

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.

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.

titre VARCHAR(120)
ingredients TEXT
duree_minutes SMALLINT UNSIGNED
portions TINYINT UNSIGNED
cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
À retenir :

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.

CONSTRAINT ck_duree CHECK (duree_minutes BETWEEN 1 AND 1440),
CONSTRAINT fk_categorie FOREIGN KEY (categorie_id)
  REFERENCES categories(id) ON DELETE RESTRICT
À retenir :

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.

INSERT INTO categories (nom) VALUES ('Goûter');
INSERT INTO categories (nom) VALUES ('Chef d''un jour');
À retenir :

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.

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.

SELECT id, titre FROM recettes
WHERE duree_minutes <= 20 AND portions = 2;
À retenir :

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.

SELECT titre FROM recettes WHERE titre LIKE '%pommes%';
À retenir :

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.

SELECT id, titre, duree_minutes FROM recettes
ORDER BY duree_minutes ASC, id ASC
LIMIT 2;
À retenir :

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.

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.

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;
À retenir :

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.

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;
À retenir :

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.

START TRANSACTION;
DELETE FROM recettes WHERE id = 3;
SELECT COUNT(*) AS total FROM recettes;
ROLLBACK;
SELECT COUNT(*) AS total FROM recettes;
À retenir :

Attendu sur la base initiale : 2 pendant l’essai, puis 3 après annulation. Le script fourni ne supprime aucune table.