TP no 18 : Bases de données (I) Travail sur une seule table
publicité
Lycée Victor Hugo MPSI-PCSI 2016-2017 TP no 18 : Bases de données (I) Travail sur une seule table 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 . – 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. 1 Lycée Victor Hugo 2 MPSI-PCSI 2016-2017 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é CreationCountry.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 en utilisant le raccourci Ctrl + Maj + Entrée . – Ouvrez ensuite le fichier TP18.sql : vous écrirez vos requêtes dans ce fichier. II Exploitation de la base de données 1. Afficher (et essayez de comprendre !) le schéma de la relation Country en utilisant la requête DESCRIBE Country;. Quelle est la clé primaire de Country ? 2. En utilisant la commande SELECT vue en cours, afficher tous les enregistrements de la table Country. 3. La requête précédente donne un résultat indigeste car il est trop riche en information. Affichez seulement le nom et le code de chaque pays. 4. Récupérez les noms et l’espérance de vie des pays dont l’espérance de vie est supérieure à 80 ans. 5. Affichez les noms des pays d’Amérique du Nord dont l’espérance de vie est entre 70 et 75 ans. 6. Afficher les noms des pays avec leur PIB par habitant (c’est à dire leur PIB divisé par leur nombre d’habitants). PIB est traduit par GNP en anglais. 7. Afficher les 3 pays d’Europe avec le plus faible PIB par habitant. On utilisera pour cela ORDER BY et LIMIT. 8. Afficher le 2ème pays le plus densément peuplé d’Amérique. 9. Déterminer toutes les formes de gouvernement des différents pays. Pour éviter les doublons on peut ajouter DISTINCT juste après SELECT : SELECT DISTINCT .... FROM ... ; 10. Récupérez les nom des pays qui ont un régime politique de type monarchique. Ce sont ceux dont l’attribut GovernmentForm contient ’Monarchy’. III 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 1 H Selon cette base, qui est l’actuel Président de la République française ? Quelle est la population de France métropolitaine ? 2 Lycée Victor Hugo MPSI-PCSI 2016-2017 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 2 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. 3
