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

Lycée Victor Hugo
MPSI-PCSI 2016-2017
TP no 16 : 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 informations 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 environnement 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’information 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 modifient 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.
Pour supprimer un enregistrement de la table <nom_table> vérifiant une
condition représentée par <predicat> :
Syntaxe SQL
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
Lycée Victor Hugo
II
1
MPSI-PCSI 2016-2017
Exploitation et modification de la base de données
Récupération de tous les attributs de tous les enregistrements
Syntaxe SQL
Pour récupérer toutes les données d’une relation appelée <nom_table>,
on utilise
SELECT * FROM <nom_table>;
Script 11 H
Récupérez toutes les données de tous les enregistrements de la relation Country.
2
Projection : récupération de certains attributs de tous les enregistrements
La requête précédente donne un résultat indigeste car il est trop riche en information.
Il est peu probable que l’on ait besoin de la totalité de ces données d’un seul coup ! Pour ne
garder que quelques attributs des enregistrements d’une relation, on utilise la projection.
Pour récupérer les attributs <nom_attr1>, <nom_attr2> etc. de tous les
enregistrements d’une relation <nom_table> on utilise :
Syntaxe SQL
SELECT <nom_attr1>, <nom_attr2>, <...>
FROM <nom_table>;
Script 12 H
Récupérez le nom et le code de chaque pays (rappel : le schéma de la table s’obtient par
une requête DESCRIBE si nécessaire, ou en en exploitant le volet gauche qui contient le schéma
de la base de données).
3
Renommage
Si le nom des colonnes (en anglais dans notre exemple) ne plaît pas, on peut les renommer
grâce à la clause AS qui suit l’attribut que l’on veut renommer. Il est bien sûr possible de
renommer plusieurs attributs d’un seul coup.
Syntaxe SQL
Pour récupérer les valeurs de l’attribut <nom_attr> et le renommer en <nouveau_nom>,
on utilise :
SELECT <nom_attr> AS <nouveau_nom>
FROM <nom_table>;
Script 13 H
Récupérez le nom du pays avec comme nom de colonne Nom (au lieu de Name), le
Code (sans renommage), et le produit national brut, initialement donné par la colonne GNP (pour Gross National Product), que l’on renommera en "PNB en millions
de dollars".
6
Lycée Victor Hugo
MPSI-PCSI 2016-2017
Remarques
1. Pour pouvoir mettre une chaîne de caractères complète comme nom de colonne, il
faut l’entourer de guillemets.
2. Il faut bien comprendre que le renommage n’a lieu que dans le résultat de la requête
effectuée et pas dans la base de données elle-même. Plus généralement, une requête
SELECT ne modifie jamais le contenu d’une base de données : elle se contente
d’en extraire de l’information et de la mettre en forme conformément à la demande.
4
Sélection : récupération de certains enregistrements seulement
Jusqu’à présent, nous avons récupéré des informations pour tous les enregistrements de
la table. Nous allons maintenant sélectionner des enregistrements qui vérifient des critères
particuliers.
Pour cela, après la partie FROM <nom_table>, qui indique de quelle table est extraite
l’information, on rajoute une clause WHERE suivie d’une condition (on dit aussi un prédicat).
De façon classique, une condition s’écrit en comparant, grâce à un opérateur de comparaison, la valeur des attributs des enregistrements d’une part, à une valeur explicite
d’autre part. La syntaxe est
<nom_attribut> <operateur_comparaison> <valeur>
Exemples ... WHERE Code = "FRA"
... WHERE Population >= 10000000
... WHERE SurfaceArea BETWEEN 10000 AND 20000
Les valeurs des attributs sont simplement représentées par le nom de l’attribut. On
utilise souvent les opérateurs de comparaison suivants :
Opérateur de comparaison
=
<>
<
<=
>
>=
BETWEEN <min> AND <max>
Signification
est égal à
est différent de
est strictement inférieur à
est inférieur ou égal à
est strictement supérieur à
est supérieur ou égal à
est entre <min> et <max> (au sens large)
Il est également possible de combiner des conditions portant sur plusieurs attributs
en utilisant des connecteurs logiques : AND (« et » logique), OR (« ou », inclusif comme en
mathématiques), NOT (« non », contraire de), XOR (« ou exclusif ») etc.
Syntaxe SQL
SELECT *
FROM <nom_table>
WHERE <prédicat>;
7
Lycée Victor Hugo
MPSI-PCSI 2016-2017
Script 14 H
1. Récupérez les données des pays dont l’espérance de vie est supérieure à 80 ans.
2. Récupérez les données des pays dont l’espérance de vie est comprise entre 78 et
80 ans en utilisant BETWEEN.
3. Récupérez les données des pays dont l’espérance de vie est comprise entre 70 et
80 ans, et dont le PNB est compris entre 500 000 $ et 2 000 000 $.
4. Récupérez les données des pays dont l’espérance de vie est comprise entre 75 et
80 ans, et dont le PNB est compris entre 1 000 000 $ et 2 000 000 $, et dont la
capitale a plus d’un million d’habitants.
5. Récupérez les données des pays dont l’espérance de vie est comprise entre 75 et
80 ans ou dont le PNB est compris entre 1 000 000 $ et 2 000 000 $.
6. Récupérez les données des pays dont l’espérance de vie est comprise entre 75 et
80 ans et dont le PNB est compris entre 1 000 000 $ et 2 000 000 $, ou dont la
population de la capitale dépasse 1 million d’habitants.
7. Récupérez les données des pays dont l’espérance de vie est comprise entre 75 et
80 ans, et dont le PNB est compris entre 1 000 000 $ et 2 000 000 $ où la population
de la capitale dépasse 2 millions d’habitants.
Lorsqu’on travaille sur des chaînes de caractères, la signification des opérateurs de
comparaison est légèrement différente. On dispose également d’un opérateur supplémentaire, qui permet de rechercher des chaînes de caractères respectant un certain motif : il
s’agit de LIKE.
Opérateur de comparaison
=
<>
<
<=
>
>=
BETWEEN <mot1> AND <mot2>
LIKE <motif>
Signification pour les chaînes de caractères
est égal à (sans tenir compte des majuscules et des accents)
est différent de (idem)
apparait avant, dans l’ordre alphabétique
apparait avant ou au même niveau dans l’ordre alphabétique
apparait après dans l’ordre alphabétique
apparait après ou au même niveau dans l’ordre alphabétique
est entre <mot1> et <mot2> dans le dictionnaire
est conforme au motif <motif>
Dans un motif, on peut entre autres choses utiliser deux symboles spéciaux % et _.
Le symbole % permet de désigner n’importe quelle chaîne de caractères (même vide). Le
symbole _ désigne quant à lui un unique caractère quelconque.
Par exemple,
LIKE "%E" permet de sélectionner les chaînes de caratères qui finissent par "E",
LIKE "F%", celles qui commencent par "F",
LIKE "%RAN%" celles qui contiennent la sous-chaîne "RANC",
LIKE "FR_" celles qui commencent par "FR" plus un caractère non précisé.
Notez que dans le troisième exemple, la chaîne "RANDONNÉE" sera acceptée, le premier "%"
étant remplacé par une chaîne vide.
Il y a encore bien d’autres possibilités : consultez la documentation en cas de besoin.
8
Lycée Victor Hugo
MPSI-PCSI 2016-2017
Script 15 H
Récupérez les informations des pays qui ont un régime politique de type monarchique
(avec d’éventuelles variantes).
Pour ce faire, commencez par regarder dans la base de données les valeurs que peut
prendre l’attribut GovernmentForm. De manière à éliminer les doublons, vous pourrez
ajouter le mot-clef DISTINCT juste après SELECT : SELECT DISTINCT ...
5
Mise à jour de certains enregistrements d’une table
Cette base de données est assez vieille et certaines données manquent cruellement de
fraîcheur.
Script 16 H
Selon cette base, qui est l’actuel Président de la République française ? Quelle est la
population de France métropolitaine ?
Nous allons donc mettre à jour ces informations.
Pour mettre à jour les attributs <att1> et <att2> de la relation <nom_table
> avec les nouvelles valeurs <val1> et val2, dans les enregistrements vérifiant une condition <predicat>, on utilise :
Syntaxe SQL
UPDATE <nom_table>
SET <attr1> = <val1>,
<attr2> = <val2>,
<...>
WHERE <prédicat>;
Script 17 H
1. La population de France métropolitaine est de 64 859 599 habitants au 1er janvier 2017. Mettez à jour la population et le nom du chef de l’état pour la France.
2. Il ne faut pas oublier les collectivités d’outre-mer ! Mettez à jour le nom du chef
de l’état pour toutes les collectivités d’outre-mer françaises. Pour les trouver, vous
pourrez utiliser le fait qu’elles ont comme valeur de l’attribut GovernmentForm
une chaîne de caractères qui contient le mot France.
6
Un petit plaisir en tant qu’application...
On va se faire un petit plaisir en rayant de la carte un pays rugbystiquement ennemi,
ainsi que ses dépendances : l’Angleterre ! [NdBM : ce passage n’engage que M. Tuloup, veuillez
vous adresser à lui pour toute réclamation] [NdBM bis : ah, il prétend que c’est de l’humour, keep cool !]
Script 18 H
1. Faites afficher tous les pays concernés (il ne s’agit pas de tout dézinguer dans la
base de données, nous sommes des êtres civilisés). Pour y parvenir, la question est :
« qu’ont-ils tous en commun ? »
2. Feu ! Supprimez tous ces pays...
3. Comment vérifier que ces pays ne sont plus dans la base de données ?
9