Ce que tu vas faire
Une base de données garde des informations dans des tables. Une table ressemble à un tableur : chaque colonne est un genre d’information, et chaque ligne est une chose, par exemple un client.
SQL est le langage qui sert à parler à une base de données. Dans cette leçon, tu ajoutes une nouvelle table à une petite boutique en ligne. Tu y mets des lignes, tu poses des questions, tu changes une ligne et tu en supprimes une.
Avant de commencer
Remarque
Appuie sur Démarrer le lab dans la barre en haut de cette leçon et attends que la barre indique que le lab tourne. Le premier démarrage est un peu plus long, parce que la base de la boutique est créée à ce moment-là. Chaque bouton Exécuter ci-dessous tape sa commande dans le terminal de ton lab, l’une après l’autre, dans la même session. Pour les voir passer, ou pour taper tes propres commandes, appuie sur Ouvrir le terminal (nouvel onglet) dans la barre.
1. Explique au terminal comment se connecter
MySQL fabrique son propre certificat de sécurité. La première commande dit donc au client de la base de l’accepter. La connexion reste privée. La deuxième commande crée un nom court, sql, pour la longue commande qui se connecte à la base shop et exécute une instruction SQL. Ensuite, tu tapes seulement sql suivi de l’instruction entre guillemets.
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfAffichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
alias sql='mariadb -h db -u student -pstudent shop -t -e'Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
2. Regarde les tables qui existent
La boutique a déjà des tables. SHOW TABLES les liste. Chacune contient un genre d’information différent : customers contient les clients, products contient les produits qu’ils achètent.
sql "SHOW TABLES"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
3. Regarde à l’intérieur d’une table
DESCRIBE montre les colonnes d’une table : leur nom, leur type, et d’autres choses. Regarde la colonne Key. PRI veut dire clé primaire. Une clé primaire est une colonne dont la valeur est différente dans chaque ligne. Elle désigne donc une ligne et aucune autre. Ici, c’est id.
sql "DESCRIBE customers"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
sql "DESCRIBE products"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
4. Crée une nouvelle table
Maintenant, tu crées ta propre table, wishlist (la liste d’envies) : quel client voudrait quel produit. CREATE TABLE la crée, et la partie entre parenthèses liste ses colonnes.
id INT AUTO_INCREMENT PRIMARY KEY: un nombre entier, que MySQL compte tout seul à chaque nouvelle ligne. C’est la clé primaire.customer_idetproduct_id: quel client, et quel produit. Ils contiennent l’idd’une ligne d’une autre table.priority: à quel point le client le veut, de 1 (beaucoup) à 5. Si tu ne dis rien, c’est 3.UNIQUE KEY: le même client et le même produit ne peuvent pas être deux fois dans la table.FOREIGN KEY(clé étrangère) : les identifiants doivent exister danscustomersetproducts. Tu ne peux donc pas souhaiter un produit qui n’est pas dans la boutique.
L’instruction est longue, mais tu ne l’exécutes qu’une fois.
sql "CREATE TABLE wishlist (id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, product_id INT NOT NULL, priority INT NOT NULL DEFAULT 3, UNIQUE KEY customer_product (customer_id, product_id), FOREIGN KEY (customer_id) REFERENCES customers(id), FOREIGN KEY (product_id) REFERENCES products(id))"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
sql "DESCRIBE wishlist"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
5. Mets des lignes dans la table
INSERT INTO ajoute des lignes. D’abord la table et ses colonnes, puis VALUES et une parenthèse pour chaque ligne. Tu ne donnes pas d’id : MySQL compte à ta place. Cette commande ajoute trois lignes : le client 1 veut le produit 1 et le produit 5, et le client 2 veut le produit 5. Ensuite, SELECT * montre toutes les colonnes de toutes les lignes.
sql "INSERT INTO wishlist (customer_id, product_id, priority) VALUES (1, 1, 3), (1, 5, 2), (2, 5, 1)"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
sql "SELECT * FROM wishlist"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
6. La clé unique dit non
Le client 1 veut déjà le produit 1. Essaie d’ajouter encore la même paire. MySQL refuse avec une erreur, à cause de la clé UNIQUE KEY de l’étape 4. Cette erreur n’est pas ta faute : elle montre que la table protège ses propres données. Rien n’a été ajouté.
sql "INSERT INTO wishlist (customer_id, product_id, priority) VALUES (1, 1, 1)"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
sql "SELECT * FROM wishlist"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
La clé étrangère dit non aussi
Remarque
Essaie sql "INSERT INTO wishlist (customer_id, product_id) VALUES (1, 999)" dans le terminal. Le produit 999 n’existe pas, donc la FOREIGN KEY refuse.
7. Pose une question simple
SELECT pose une question. FROM dit dans quelle table. WHERE garde seulement les lignes qui correspondent, et ORDER BY les met en ordre. Cette requête montre ce que veut le client 1, le plus voulu en premier.
sql "SELECT * FROM wishlist WHERE customer_id = 1 ORDER BY priority"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
sql "SELECT name, price FROM products WHERE price > 50 ORDER BY price DESC"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
8. Relie deux tables
La liste d’envies ne contient que des nombres. Les noms des produits sont dans products. Une jointure (« join ») met deux tables côte à côte. JOIN products ON products.id = wishlist.product_id veut dire : pour chaque ligne de la liste, trouve le produit dont l’id est dans product_id. Maintenant, tu peux la lire.
sql "SELECT wishlist.id, products.name, products.price, wishlist.priority FROM wishlist JOIN products ON products.id = wishlist.product_id"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
9. Relie trois tables
Tu peux relier encore une table pour ajouter le nom du client. C’est la même idée, avec un JOIN de plus.
sql "SELECT customers.first_name, products.name, wishlist.priority FROM wishlist JOIN customers ON customers.id = wishlist.customer_id JOIN products ON products.id = wishlist.product_id ORDER BY customers.first_name"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
10. Change une ligne
UPDATE change des lignes. SET donne la nouvelle valeur. WHERE id = 2 dit quelle ligne. Le client 1 veut maintenant le produit 5 plus que tout, donc sa priorité passe à 1. Ensuite, vérifie avec un SELECT que la ligne a bien changé.
sql "UPDATE wishlist SET priority = 1 WHERE id = 2"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
sql "SELECT * FROM wishlist WHERE id = 2"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
N’oublie jamais WHERE
Attention
On croit facilement que UPDATE et DELETE changent seulement la ligne qu’on a en tête. Sans WHERE, ils changent toutes les lignes de la table. Écris toujours le WHERE en premier, et vérifie avec un SELECT avant et après.
11. Supprime une ligne
DELETE FROM supprime des lignes. Là aussi, WHERE dit lesquelles. Le client 2 a changé d’avis, donc supprime la ligne 3. Ensuite, regarde la table : la ligne 3 a disparu, et les autres sont toujours là.
sql "DELETE FROM wishlist WHERE id = 3"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
sql "SELECT * FROM wishlist"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
12. Compte ce qui reste
COUNT(*) compte les lignes. Ici, on compte celles dont l’id est 3 : la réponse est 0, ce qui prouve que la ligne a disparu.
sql "SELECT COUNT(*) AS rows_found FROM wishlist WHERE id = 3"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
13. Regarde dans phpMyAdmin
Dans la barre du lab, appuie sur phpMyAdmin pour l’ouvrir dans un nouvel onglet. Choisis shop à gauche. Ta nouvelle table wishlist est dans la liste avec les autres. Appuie sur wishlist, puis sur Browse pour voir ses lignes, et sur Structure pour voir la clé primaire et la clé unique que tu as créées.
Refais-le par toi-même
Astuce
Pour reprendre la leçon depuis le début, supprime ta table avec sql "DROP TABLE wishlist" et retourne à l’étape 4. Ensuite, essaie tes propres questions : quels produits coûtent moins de 10 ? Quels clients habitent à Paris ?
Si le terminal a redémarré, exécute d’abord à nouveau l’alias de l’étape 1. La base garde ta table.