Notes et transcription du cours Implémentez vos bases de données relationnelles avec SQL disponible sur la plateforme Openclassrooms.

Pour ce chapitre, on travaille sur la table foodly, la même version que dans la partie précédente.

Insérer de la donnée, la modifier, la supprimer, ou bien en tirer des informations utiles est désormais à votre portée.

Mais il reste une chose à voir, une chose importante dont vous aurez toujours besoin si vous souhaitez adapter votre BDD aux évolutions de votre application.

Il s’agit de la modification de la structure de la BDD en elle-même. Commençons simplement par l’ajout et la suppression de champs.

Le dernier rapport sur l’utilisation de Foodly vient de tomber. Dans celui-ci, les utilisateurs ont pu voter pour les améliorations qu’ils souhaiteraient voir apparaître dans l’application.

La plus demandée était celle de voir la contenance en vitamines de chaque aliment.

Sachant cela, l’équipe de Foodly organise une réunion concernant l’implémentation d’une telle fonctionnalité. Le design est prêt, les développeurs ont codé l’interface, mais il manque une chose cruciale pour récupérer la contenance en vitamines dans l’application : l’ajout de cette donnée dans la BDD.

Prenons l’exemple de la vitamine C.

Cette colonne n’apparaît nulle part dans le schéma de la table aliment. Il nous faut la créer.

De la même manière que vous l’avez fait lors de la première partie, il faudra spécifier son type à MySQL. Pour ce faire, utilisez la commande ALTER TABLE.

Avant de nous jeter dans le grand bain, regardons la structure de notre table aliment. Vous vous souvenez de la commande ? Non ? Pas de soucis, la voici : SHOW COLUMNS FROM aliment;

On obtient :

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| nom         | varchar(100) | NO   |     | NULL    |                |
| marque      | varchar(100) | YES  |     | NULL    |                |
| sucre       | float        | YES  |     | NULL    |                |
| calories    | int          | NO   |     | NULL    |                |
| graisses    | float        | YES  |     | NULL    |                |
| proteines   | float        | YES  |     | NULL    |                |
| bio         | tinyint(1)   | YES  |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+

Bon, maintenant, voyons à quoi ressemble la commande SQL pour rajouter cette colonne, puis décortiquons-la ensemble :

ALTER TABLE aliment ADD vitamines_c FLOAT;

Ici, on signale à MySQL :

• De modifier la structure d’une table avec ALTER TABLE ;
• Quelle table modifier (ici “aliment”) ;
• Que la modification va faire ajouter une colonne avec ADD ;
• Le nom de cette nouvelle colonne (ici “vitamines_c”) ;
• Enfin, le type de la colonne (ici,FLOAT, car les vitamines sont stockées usuellement en mg/100g, valeur décimale).

La commande ALTER TABLE

Finalement la commande ALTER TABLE ressemble grandement à la commande CREATE TABLE. Quelque part, c'est totalement normal d'ailleurs. On doit spécifier systématiquement le type de la variable, et éventuellement les options.

Si vous avez tapé la commande, vous avez en retour :

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

C'est un peu trompeur… On dirait que rien ne s'est passé.

Pour s'en assurer, on peut lancer la commande SHOW COLUMNS FROM aliment; pour contrôler la bonne exécution de l'opération.

Faisons un petit exercice :

Admettons que l’on souhaite rajouter une colonne à la table “langue”. Cette colonne, c’est le code ISO des langues (par exemple : “fr-fr” pour le Français de métropole).

Comment feriez-vous cela ?

Tentez de créer la colonne et de la remplir par vous-même pour le Français et l’Anglais. Voici la correction :

Réponse

# Ajoute le champ code_iso
ALTER TABLE langue ADD code_iso VARCHAR(5);
 
# Met à jour les enregistrements existants
UPDATE langue
  SET nom='Français', code_iso='fr'
  WHERE id=1;
 
UPDATE langue
  SET nom='Agnlais', code_iso='en'
  WHERE id=2;
 
# Ajoute contrainte d unicité
ALTER TABLE langue
  MODIFY code_iso VARCHAR(5) UNIQUE NOT NULL;
 
# Création de nouveaux enregistrements
INSERT INTO langue (nom, code_iso)
  VALUES ('Français (France)', 'fr-FR'), ('Français (Belge)', 'fr-BE'), ('Anglais (Angleterre)', 'en-GB'), ('Anglais (Etats-Unis)', 'en-US');

INSERT INTO langue (nom, code_iso) VALUES ('france', 'fr');
ERROR 1062 (23000): Duplicate entry 'fr' for key 'code_iso'

Le management de chez Foodly nous mentionne d’autres changements à effectuer sur l’application, qui impliquent la BDD. C’est une nouvelle surprenante !

Les utilisateurs de Foodly n’utilisent que très rarement la fonctionnalité pour savoir si un aliment est bio ou non. 😢

Les développeurs, dans leur volonté de toujours diminuer la dette technique (à savoir, le code à maintenir), souhaitent retirer l’affichage bio de la fiche des aliments sur Foodly.

En tant que gestionnaire de la BDD, vous pensez qu’il serait pratique de faire de même de votre côté, pour éviter de maintenir un champ qui ne sera plus mis à jour.

Pour ce faire, rien de plus simple : supprimons la colonne “bio” de la table “aliment” avec DROP.

Voici la commande SQL pour le faire :

ALTER TABLE aliment DROP bio;

Décomposons cette commande ensemble :

• On modifie toujours la structure d’une table avec ALTER TABLE;
• On lui signale à nouveau quelle table modifier (ici, “aliment”) ;
• On lui indique que la modification va supprimer une colonne avec DROP;
• On mentionne le nom de la colonne à supprimer (ici, “bio”).

Utilisation de DROP

Maintenant, admettons que le service légal de Foodly vous dise que stocker des noms de famille va à l’encontre des conditions de protection des données des utilisateurs.

Comment feriez-vous pour supprimer les noms de famille ?

Réponse

ALTER  TABLE utilisateur DROP nom;

Ajouter, supprimer, vous voilà maître en matière de modification de schéma de table ! Enfin… pas trop vite ! Que se passerait-il si vous deviez modifier une colonne existante ?

C’est ce que vous allez voir par la suite.

Le management de Foodly revient encore une fois vers vous !

Après avoir regardé des utilisateurs se servir de Foodly, ils se sont rendu compte que les utilisateurs souhaiteraient pouvoir filtrer les aliments selon leurs apports calorifiques avec plus de précision.

Pour ce faire, les développeurs vous indiquent qu’ils vont devoir comparer les calories de deux aliments entre eux, et ce, à la virgule près.

Or, les calories sont actuellement stockées sous forme d’entiers (sans décimales, du coup). Comment les modifier ?

Voici la commande pour effectuer cette opération :

ALTER TABLE aliment MODIFY calories FLOAT;

Analysons cette commande :

• On modifie toujours la structure d’une table avec ALTER TABLE;
• On lui signale à nouveau quelle table modifier (ici, “aliment”) ;
• On lui indique que la modification va modifier le type d’une colonne avec MODIFY;
• On mentionne le nom de la colonne à modifier (ici, “calories”) ;
• On indique le nouveau type de la colonne (ici, FLOAT).

L'utilisation de FLOAT

Les développeurs viennent vous voir affolés ! Certains utilisateurs ne peuvent pas s’inscrire car leur e-mail dépasse les 255 caractères.

Pourriez-vous augmenter la limite à 500 ?

Réponse

ALTER TABLE utilisateur MODIFY email VARCHAR(500);

Plutôt pratique de savoir modifier le type d’une colonne, non ? Mais que se passerait-il si vous souhaitiez changer le nom de cette dernière ?

Ce coup-ci, c’est la designer de Foodly qui revient vers vous : les utilisateurs sont perturbés… 😨 Pour chaque aliment, la valeur en protéines, vitamines, matières grasses est mentionnée au pluriel. Alors que les sucres, eux, sont mentionnés au singulier. Ce qui perturbe la compréhension des aliments par les utilisateurs.

Comment renommer cette colonne dans la BDD ?

Voici la commande à effectuer :

ALTER TABLE aliment CHANGE sucre sucres FLOAT;

Voici les explications de cette commande :

• On modifie toujours la structure d’une table avec ALTER TABLE;
• On lui signale à nouveau quelle table modifier (ici, “aliment”) ;
• On lui indique que la modification du label d’une colonne avec CHANGE;
• On mentionne le nom de la colonne à renommer, ainsi que son nouveau nom (ici, “sucre” devient “sucres”) ;
• On indique le nouveau type de la colonne (ici,FLOAT).

Ce qui n’est pas nécessaire si vous utilisez un autre SGBD. Cela permet de modifier à la fois le nom et le type d’une colonne dans une seule commande. Et ce, même si vous ne souhaitez pas le modifier (réutilisez alors le même type 😉).

À présent, les développeurs trouvent que le nom “code” dans la table des langues est peu explicite. Ils aimeraient qu’elle s’appelle “code_pays”.

Pouvez-vous implémenter ce changement ?

++++ Réponse |

ALTER TABLE langue CHANGE code_iso code_pays VARCHAR(5);

• Vous savez que toute modification de la structure d’un objet s’effectue avec une commande ALTER TABLE.
• Pour ajouter un champ à un objet, il vous faut utiliser le mot clé ADD.
• Pour supprimer un champ, le mot clé est DROP.
• Pour modifier un champ, le mot clé est MODIFY pour en modifier la structure, CHANGE pour le renommer.
• Il faut être très vigilant quand on utilise la commande ALTER TABLE, et bien réfléchir aux conséquences des modifications qui vont être effectuées.

Vous voilà capable de totalement modifier le schéma d’une table d’une BDD MySQL. Vous êtes presque maître en BDD MySQL.

Presque ? Il vous reste encore à apprendre à modifier des relations dans une table ! Ne vous inquiétez pas, vous allez tout comprendre dans le chapitre suivant.

◁ Précédent | ⌂ Retour au sommaire | Suivant ▷