TP no 16 : Bases de données (I) Travail sur une seule table

Lycée Victor Hugo MPSI-PCSI 2016-2017
TP no16 : Bases de données (I)
Travail sur une seule table
Introduction
Dans ce TP nous allons effectuer des opérations élémentaires dans une base de données
en ne travaillant que sur une seule table de cette base. Lors de la prochaine séance, nous
nous intéresserons aux requêtes portant sur plusieurs tables simultanément.
L’idée du TP est d’adapter la syntaxe générale, qui sera fournie dans l’énoncé pour
les différentes instructions SQL, au cas concret sur lequel nous allons travailler.
Nous allons travailler sur une base de données appelée world rassemblant des infor-
mations relatives aux pays et aux villes du monde. Cette base de données comprend
trois tables. Pour le moment, nous ne nous intéresserons qu’à la seule table Country qui
contient les informations sur les pays.
Le système de gestion de bases de données (SGBD) que nous allons utiliser pour cela
est MySQL. Nous allons communiquer avec MySQL grâce à une interface graphique, qui
est fournie par l’application MySQL Workbench.
Remarque Il est également possible d’intéragir avec MySQL grâce à une interface en ligne de
commande ; pour cela, on lance la commande mysql dans un terminal. Nous ne nous en servirons
pas dans ce TP.
I Création de la base de données
1 Connexion au serveur MySQL, fonctionnement de MySQL
Workbench
Action H
1. Lancez l’application MySQL Workbench en cliquant sur l’icône correspondante
(comportant un dauphin) dans la barre d’applications sur la gauche de l’écran.
2. Cliquez sur le rectangle gris Connexion élèves.
Un mot de passe vous sera demandé : il s’agit de infocommune.
Vous disposez alors d’un environnement que nous allons découvrir petit à petit.
– Dans la fenêtre centrale (qu’on appelle un script-tab) on peut écrire une requête SQL
et l’exécuter.
– Pour n’exécuter qu’une requête particulière parmi toutes celles susceptibles
d’apparaître dans le script-tab, il suffit de placer le curseur n’importe où dans cette
requête et de cliquer sur l’icône avec un éclair et un curseur (celle de droite).
On peut également utiliser le raccourci clavier Ctrl +Entrée .
1

Lycée Victor Hugo MPSI-PCSI 2016-2017
– Pour exécuter successivement la totalité des requêtes d’un script-tab, cliquez sur
l’icône contenant un éclair (celle de gauche).
Le raccourci clavier est cette fois Ctrl +Maj +Entrée .
– Après l’exécution le résultat s’affiche au milieu de la fenêtre et un compte-rendu
d’exécution apparaît tout en bas.
Dans les TP sur les bases de données, quand l’énoncé demandera d’exécuter un script,
il s’agira d’exécuter une requête SQL après l’avoir saisie dans un script-tab.
2 Création de la base de données
On commence par détruire une éventuelle base de données pour partir d’un environ-
nement propre.
Syntaxe SQL Pour détruire une base de données nommée <nom_bd>, on utilise la syn-
taxe suivante :
DROP DATABASE <nom_bd>;
Important Dans ce TP, les éléments de syntaxe SQL sont écrits de la manière suivante :
ce qui se trouve entre <et >est un champ à compléter, que vous devez remplacer par l’in-
formation de votre choix. Ainsi, dans ce premier exemple, si on souhaite supprimer la base
de données notes_ipt_2014, on remplacera le champ <nom_bd> par notes_ipt_2014, ce
qui donne :
DROP DATABASE notes_ipt_2014;
Remarques
1. Les instructions SQL sont généralement écrites en majuscules. Cela n’est pas obligatoire
mais cela permet de mieux visualiser leur structure.
2. MySQL Workbench reconnaît les mot-clés du langage SQL et les affiche en bleu.
3. Quand une commande SQL est très longue, il est possible de l’écrire sur plusieurs lignes.
Cela impose en contrepartie d’indiquer explicitement la fin d’une commande SQL à l’aide
d’une point-virgule.
Chaque commande SQL doit toujours se terminer par un point-virgule (;).
Script 1 H
Détruisez la base de données world en remplaçant <nom_bd> par world dans la syntaxe
précédente.
S’il y avait une base de données world, celle-ci a été effacée, et la requête s’est bien
passée. Si il n’y en avait pas, vous devez avoir un message d’erreur, ce qui est normal : on
ne peut pas supprimer une base de données qui n’existe pas !
Remarques
1. MySQL Workbench propose une aide à la saisie des requêtes : on peut faire générer des
squelettes de requêtes en cliquant avec le bouton droit dans le panneau latéral gauche sur
les noms des bases de données, des tables, des colonnes, puis en ouvrant le menu Send to
SQL Editor... qui contient des commandes comme Update statement,Insert statement, etc.
Essayez par vous-même dans la suite.
2

Lycée Victor Hugo MPSI-PCSI 2016-2017
2. De façon inattendue, les opérations sur le schéma de la base de données (càd celles qui mo-
difient les bases de données présentes, les tables, les colonnes) ne sont pas répercutées
instantanément dans le panneau latéral gauche. Il est donc nécessaire de rafraîchir ces
informations régulièrement, en cliquant sur le double flèche circulaire en haut droite de ce
panneau.
On peut maintenant créer la base données world.
Syntaxe SQL Pour créer une base de données nommée <nom_bd>, on utilise
CREATE DATABASE <nom_bd>;
Script 2 H
Créez la base de données world.
Remarque Pour structurer votre script SQL, vous pouvez comme en Python y écrire des
commentaires, qui seront ignorés lors de l’exécution du script :
– Un commentaire qui tient sur une ligne est introduit par --. Par exemple, vous pouvez
introduire cette question par :
-- Script 2
puis d’écrire votre requête SQL sur la ligne suivante.
– Un commentaire qui est réparti sur plusieurs lignes est ouvert par /* et refermé par */ :
/* Script 2
Dans cette question, on crée la base de données world.
Ça n’est pas une question bien difficile !
*/
MySQL Workbench détecte les commentaires et les écrit en italique et en gris clair.
On peut enfin spécifier la base de données sur laquelle on veut travailler.
Syntaxe SQL Pour choisir une base de données de travail nommée <nom_bd>, on utilise
USE <nom_bd>;
Script 3 H
Indiquez à MySQL Workbench que vous allez dorénavant travailler avec la base de
données world.
3 Création de la table Country
Le schéma de la relation Country serait un peu long à taper, alors nous allons utiliser
un fichier qui contient déjà la requête de création de la table.
Action H
– Téléchargez sur HugoPrépas le script SQL nommé CreationTableCountry.sql.
– Ouvrez-le dans MySQL Workbench à l’aide du menu File/Open SQL Script.
– Exécutez la totalité du script de création de la table Country (reportez-vous au
début de ce TP si vous ne savez plus comment faire.)
3

Lycée Victor Hugo MPSI-PCSI 2016-2017
Question 4 H
En lisant la requête de création de la table Country, donnez le schéma relationnel
de cette table : les attributs de cette table, leurs domaines respectifs, les attributs qui
composent la clef primaire. Vous pourrez écrire vos réponses dans votre script à l’intérieur
d’un commentaire long /* ... */.
Il existe une commande MySQL permettant d’obtenir le schéma relationnel d’une
table. Nous pourrons obtenir une confirmation que notre réponse à la question précédente
était correcte en exécutant cette requête dans MySQL Workbench.
Syntaxe SQL Pour faire afficher le schéma d’une relation <nom_table>, on utilise
DESCRIBE <nom_table>;
Script 5 H
Faites afficher le schéma de la relation Country et vérifiez qu’elle est cohérente avec
votre réponse à la question précédente.
Remarques char(3) désigne une chaîne de 3 caractères, int(11) un entier comportant
au maximum 11 chiffres (en base 10), float(10, 2) un flottant avec 10 chiffres avant la
virgule et 2 chiffres après.
4 Création et suppression d’un enregistrement dans la table
Country
On va insérer manuellement un pays dans la base de données que nous avons créée.
Syntaxe SQL Pour insérer un enregistrement dans la table <nom_table>, on utilise
INSERT INTO <nom_table>
(<nom_attr1>, <nom_attr2>, ... )
VALUES (<val1>, <val2>, ... );
les valeurs <val1>,<val2> etc. servant à alimenter les attributs de noms <nom_attr1>,
<nom_attr2> etc.
Remarque Comme les requêtes d’insertion sont en général assez longues, il est judicieux de
les écrire sur plusieurs lignes, en allant à la ligne pour chaque partie de la requête. La fin de la
requête est de toute façon indiquée par le point-virgule.
Script 6 H
1. Déployez au maximum le schéma de la base de données world apparaissant dans
le cadre latéral gauche : pour cela, cliquez sur les petits triangles jusqu’à faire
apparaître la table Country.
2. Cliquez avec le bouton droit de la souris sur Country, ouvrez le menu Send to
SQL Editor... et choisissez Insert statement. MySQL Workbench génère pour vous
dans le script-tab le squelette de l’instruction SQL d’insertion d’un enregistrement
dans la table Country.
3. Complétez alors ce squelette : vous choisirez arbitrairement des valeurs décrivant
un pays imaginaire, en faisant attention toutefois que ces données soient en accord
avec les domaines des attributs (voir la description de la table réalisée juste avant) :
4

Lycée Victor Hugo MPSI-PCSI 2016-2017
– Le champ <{Code: }> est à remplacer intégralement, par "FRA" par exemple
(il faut tout remplacer, y compris les caractères <{:}>). Le choix de la
chaîne "FRA" est légitime car l’attribut Code a pour domaine l’ensemble des
chaînes de trois caractères.
– Les chaînes de caractères doivent être entourées de guillemets.
4. Exécutez la requête et vérifiez dans le rapport d’exécution que tout s’est bien passé
(pas d’erreur ni d’avertissement).
Script 7 H
Vérifiez qu’un enregistrement a bien été créé en exécutant la requête suivante :
SELECT * FROM Country;
Il est probable que l’enregistrement ainsi créé ne corresponde pas aux données d’un
vrai pays. Nous allons donc le supprimer.
Syntaxe SQL Pour supprimer un enregistrement de la table <nom_table> vérifiant une
condition représentée par <predicat> :
DELETE FROM <nom_table> WHERE <prédicat>;
Script 8 H
Supprimez l’enregistrement créé précédemment en utilisant comme <prédicat> :Code="FRA"
si le code donnée pour le pays est "FRA" (à adapter au choix que vous avez fait précé-
demment).
Vérifiez dans le rapport d’exécution que la requête s’est exécutée convenablement (pas
d’erreur ni d’avertissement).
Script 9 H
Vérifiez, en exécutant la requête suivante, que l’enregistrement a bien disparu de la base :
SELECT * FROM Country;
5 Création de tous les enregistrements dans la table Country
Il est temps d’alimenter vraiment la base de données. Il n’est pas question, là non plus,
d’insérer manuellement chaque enregistrement un par un. Vos gentils professeurs ont mis
à votre disposition un script contenant tout le nécessaire.
Action H
Téléchargez le script SQL nommé CreationDonnéesCountry.sql sur Hugoprépas.
Ouvrez-le par le menu File/Open SQL Script.
Script 10 H
Exécutez la totalité du script (revenez au début du TP si vous ne savez plus comment faire)
qui contient les requêtes d’insertion d’un grand nombre d’enregistrements dans la table
Country.
Vérifiez dans la zone d’exécution qu’un grand nombre de commandes a bien été exécuté.
Passons maintenant à l’exploitation de la base de données.
5