PC(*) Bases de données 2014/2015 1 Principe Un système de bases de données permet de stocker et de traiter des informations sous forme de tables, dont les colonnes contiennent des données de type prédéfini (entier, réel, chaîne de caractères, booléen, etc.). L’exemple typique est celui du carnet de contacts (nom , prénom, numéro de téléphone, adresse, etc.) La base de donnée, qui contient ses renseignements, doit être accessible à un grand nombre d’utilisateur, ce qui nécessite un système complexe de clients et de requêtes. Les requêtes de chacun des utilisateurs doivent pouvoir se faire en parallèle, en respectant la hiérarchie des droits, la transparence des modifications, et la cohérence et l’intégrité de la table de données. Voici un tableau avec des types de données courant : code nom description I Integer entier long N Number réel SI Short Integer entier court BL Boolean booléen A Char caractères alpha de longueur fixe VA VarChar caractères alpha longueur variable avec un maximum D Date date MN Money monnaie 2 Quelques commandes 2.1 CREATE Elle permet de créer une base de données, soit manuellement, soit automatiquement à partir d’un fichier .csv (séries de données séparées par un délimiteur : virgule, point-virgule, tabulation, espace, etc. Par exemple, la commande : (CREATE table classe_pc nom VARCHAR(30) PRIMARY KEY NOT NULL, prenom VARCHAR(19) NOT NULL, date_naissance DATE , email VARCHAR ,) ; prépare la création de la table classe_pc avec quatre données (dont deux obligatoires). Il faut encore renseigner ces données pour chaque élève de la classe. PRIMARY KEY autorise la donnée nom à servir de référence pour un index. NOT NULL interdit la valeur nulle (absence de données). VARCHAR(30) indique une chaîne de caractère limitée à 30 caractères. 2.2 SELECT Elle permet de rechercher des éléments dans la base de données, avec des critères de sélection. Considérons par exemple une base de données pays contenant quatre colonnes : Pays Chine Inde Etats-Unis Population 1367820387 1192148288 320504419 Superficie 9596961 3287263 9629047 Densite 142.53 362.66 33.29 Alors on utilisera la commande avec la syntaxe : SELECT FROM WHERE syntaxe liste des attributs nom de la table conditions exemple SELECT * FROM pays WHERE ‘Pays‘ LIKE U% L’exemple donné correspond à la recherche de tous les pays dont le nom commence par la lettre ’U’. Opérateurs de conditions pour la commande WHERE Opérateur Description exemple WHERE ‘Pays‘ = ’Vatican’ = égale <>, != différent WHERE ‘Pays‘ <> ’Russie’ WHERE ‘Densité‘ >= 100 > supérieur à < inférieur à WHERE ‘Population‘ < 1000000 WHERE ‘Densité‘ >= 100 >= supérieur ou égale à <= inférieur ou égale à WHERE ‘Population‘ <= 1000000 WHERE Pays IN ( ’Maurice’, ’Suisse’, ’Luxembourg’ ) IN présent dans une liste BETWEEN inclus dans un intervalle WHERE ‘Densité‘ BETWEEN 50 AND 125 LIKE recherche en spécifiant un schéma WHERE ‘Pays‘ LIKE ’U%’ IS NULL valeur absente WHERE ‘email‘ IS NULL IS NOT NULL valeur présente WHERE ‘email‘ IS NOT NULL Concernant la commande LIKE, les schémas de recherche sont formés en utilisant les opérateurs _ (n’importe quel caractère) ou % (n’importe quel groupe non vide de caractère). On peut utiliser les opérateurs logiques AND, OR, NOT. 2.3 JOIN Supposons que l’on dispose d’une deuxième base de données, paycap, organisée ainsi : Pays Capitale Abou Dhabi Emirats arabes unis Abuja Nigeria Accra Ghana On cherche à savoir, en utilisant les tables paycap et paypop, quel est la densité du pays dont la capitale est Hagåtña (notée Hagatna pour plus de commodité dans notre base). On utilisera la syntaxe : SELECT * FROM ‘paycap‘ NATURAL JOIN ‘paypop‘ qui renvoie la table, qui est une fusion des deux tables paycap et paypop : Pays Chine Inde Etats-Unis Indonesie Bresil Pakistan Nigeria Capitale Pekin New Delhi Washington Jakarta Brasilia Islamabad Abuja Population 1367820387 1192148288 320504419 255461701 203891082 191541725 187380807 Superficie 9596961 3287263 9629047 1910931 8547877 796096 923768 Densite 143 363 33 134 24 241 203 SELECT * FROM ‘paycap‘ NATURAL JOIN ‘paypop‘ WHERE ‘Capitale‘ LIKE ’Hagatna’ renvoie Pays Capitale Population Superficie Densite Guam Hagatna 160378 544 295 et SELECT ‘Densite‘ FROM ‘paycap‘ NATURAL JOIN ‘paypop‘ WHERE ‘Capitale‘ LIKE ’Hagatna’ renvoie 295. 3 Exemples et pratique On pourra utiliser le serveur mis en place sur Camille, à l’adresse http://info-cpge.lyc-camilleguerin.fr/phpmyadmin avec l’identifiant pc et le mot de passe db-#&+ . B ne modifiez pas les bases déjà existantes. Vous créérez votre propre base, que vous pourrez modifier à votre guise. On aboutit sur une interface graphique permettant de créer des requêtes SQL, et renvoyant le texte de la requête. onglet « afficher » onglet « opérations » onglet « rechercher » On trouve sur le serveur deux bases : (les pays dont le nom contient un x) ∗ paypop donnant une liste de pays avec leur superficie, leur population et leur densité. ∗ paycap donnant une liste de capitales correspondant aux pays. B l’import des caractères accentués a posé un problème : ils ont été remplacés par des caractères non accentués. Par ailleurs, le choix de la capitale d’un pays est parfois contesté. 3.1 création d’une table Utiliser l’onglet Opérations pour créer votre propre table, en copiant la table paycap vers une autre table avec un nom personnalisé. Vous pourrez modifier cette table (entrées, lignes, type de données, etc.). 3.2 Quelques recherches Utiliser l’onglet recherches pour déterminer : ∗ les pays de population >= 1M hab et de superficie <= 10000 km 2 ; ∗ les pays dont le nom commence par ’U’ ∗ les pays dont le nom contient la lettre ’q’ et la capitale contient la lettre ’a’ ; ∗ les pays dont le nom contient un ’w’ et ayant plus de 10M hab. 3.3 Quelques recherches en ligne de commandes Utiliser l’onglet SQL pour déterminer : ∗ les pays dont le nom contient la lettre ’q’ mais pas la lettre ’u’ ∗ la superficie des pays de plus de 10 000 000 d’habitants dont le nom ne contient pas la lettre ’u’ ∗ les pays de moins de 100 000 km 2 dont le nom contient la lettre ’v’ ; ∗ les pays de densité < 100 dont la capitale contient une des lettres ’J’ ou ’V’ ; 4 solutions 3.2 ∗ les pays de population >= 1M hab et de superficie <= 10000 km 2 . SELECT * FROM ‘paypop‘ WHERE ‘Population‘ >=1000000 AND ‘Superficie‘ <=10000 Hong Kong Singapour Palestine Porto Rico Trinite-et-Tobago Bahrein Maurice Chypre 7219700 5469724 4420549 3548397 1346832 1346344 1219265 1172458 1104 718 6020 9104 5128 770 2040 9251 6540 7615 734 390 263 1749 598 127 ∗ les pays dont le nom commence par ’U’ SELECT * FROM ‘paypop‘ WHERE ‘Pays‘ LIKE ’U%’ Ukraine Uruguay 42928892 3467054 576468 176215 74 20 ∗ les pays dont le nom contient un ’w’ et ayant plus de 10M hab SELECT * FROM ‘paypop‘ WHERE ‘Pays‘ LIKE ’%w%’ AND ‘Population‘ >=10000000 Taiwan Malawi Zimbabwe Rwanda 23373517 15316860 13061239 11077897 36191 118484 390757 25283 646 129 33 438 ∗ les pays dont le nom contient la lettre ’q’ et la capitale contient la lettre ’a’ ; SELECT * FROM ‘paycap‘ WHERE ‘Capitale‘ LIKE ’%a%’ AND ‘Pays‘ LIKE ’%q%’ Ankara Turquie Bangui Republique centrafricaine Bratislava Slovaquie Brazzaville Republique du Congo Le Cap Afrique du Sud Doha Qatar Kinshasa Republique democratique du Congo Malabo Guinee equatoriale Maputo Mozambique Prague Republique tcheque Roseau Dominique Saint-Domingue Republique dominicaine 3.3 ∗ les pays dont le nom contient la lettre ’q’ mais pas la lettre ’u’ ; SELECT ‘Pays‘ FROM ‘paycap‘ WHERE ‘Pays‘ LIKE ’%q%’ AND ‘Pays‘ NOT LIKE ’%u%’ Un seul pays : le Qatar. ∗ la superficie des pays de plus de 10 000 000 d’habitants dont le nom ne contient pas la lettre ’u’ ; SELECT ‘Superficie‘ FROM ‘paypop‘ WHERE ‘Pays‘ NOT LIKE ’%U%’ AND ‘Population‘ >=60000000 AND ‘Densite‘ >75 9596961,3287263, 1910931, 796096, 923768, 147570, 377944, 300439, 1127127, 330951, 1001449, 357340, 513120, 551695, 302072 (total 21524726) ∗ les pays de densité < 100 dont la capitale contient une des lettres ’J’ ou ’V’ ; SELECT ‘Pays‘ FROM ‘paycap‘ WHERE (‘Capitale‘ LIKE ’%J%’ NATURAL JOIN ‘paypop‘ OR ‘Capitale‘ LIKE ’%V%’) AND ( ‘Densite‘ <100 ) Ukraine, Malaisie, Madagascar, Tchad, Soudan du Sud, Benin, Autriche, Laos, Costa Rica, Liberia, BosnieHerzegovine, Lituanie, Macedoine, Gabon, Fidji, Djibouti, Islande, Vanuatu.
© Copyright 2024