I- EXPLOITATION DES BASES DE DONNÉES RELATIONNELLES : SQL III-1- Généralités Le langage SQL (Structured Query Language) peut être considéré comme le langage d’accès normalisé aux bases de données. Il est aujourd’hui supporté par la plupart des produits commerciaux que ce soit par les systèmes de gestion de bases de données micro tel que Access ou par les produits plus professionnels tels que Oracle ou Sybase. Il possède 3 parties : o Un langage de définition de données (DDL : Data Definition Language) ; o Un langage de contrôle de données (DCL : Data Control Language) ; o Un langage de manipulation de données (DML : Data Manipulation Language) ; On va s’intéresser principalement à cette partie. Le DML ou LMD (Langage de manipulation de données) est lui-même divisé en 4 parties : ajout d’occurrences (INSERT) modification d’occurrences (UPDATE) suppression d’occurrences (DELETE) interrogation de la base de données : les requêtes (SELECT) III-2- Le langage de requêtes Nous avons vu que la plupart des SGBD offrent la possibilité d'effectuer des recherches directement dans les tables. Les possibilités de formuler des critères de recherche sont cependant souvent assez limitées. Heureusement, la plupart des SGBD nous offrent également la possibilité de poser pratiquement n'importe quelle "question" à nos tables, sous forme de requêtes. Les requêtes servent donc à répondre aux questions basées sur le contenu d'une ou de plusieurs tables. Expression générale d’une requête Présentation d'une requête : Une requête se présente généralement sous la forme " SELECT ... FROM ... WHERE " : la clause SELECT exprime le résultat attendu sous la forme d’une liste d’attributs auxquels il est possible d’appliquer différents opérateurs et fonctions. la clause FROM liste les relations utilisées pour évaluer les requêtes; la clause WHERE qui est facultative énonce une condition que doivent respecter les enregistrements sélectionnés. Jeu d’essai : III-3- La projection et la sélection La projection consiste à choisir certaines colonnes d’une table (la clause WHERE n’est pas utilisée alors), mais toutes les occurrences de la table sont affichées : Exemple : SELECT NOM_ELEVE,PRENOM_ELEVE,ADR_VILLE_ELEVE FROM ELEVE; La sélection dans une table consiste à choisir certaines de ses occurrences grâce à un critère de sélection (énoncé dans la clause WHERE). Exemple : SELECT NOM_ELEVE,PRENOM_ELEVE,ADR_VILLE_ELEVE ELEVE WHERE ADHESION_MUTUELLE)=0 II- LA CLAUSE SELECT IV-1- Quelques généralités L'opérateur * permet d'afficher tous les champs définis dans la table. Exemple : SELECT * FROM JOUR ; FROM Les données du type TEXTE sont entourées d'apostrophes (quotes). Exemple: SELECT * FROM MONITEUR WHERE.NOM_MONITEUR='ALBERT'; Les dates sont entourées du caractère # et indiquées dans le format américain #Mois/Jour/Année# Exemple : SELECT * FROM VEHICULE WHERE DATE_MISE_SERV = #6/25/2001#; Les critères de sélection constituent une expression logique qui peut prendre la valeur 'Vrai' ou 'Faux'. Comparaison à une valeur donnée. Voici les opérateurs de comparaison: = > < >= <= <> "est égal" "strictement supérieur" "strictement inférieur" "supérieur ou égal" "inférieur ou égal" "est différent" Exercice 1 : quels sont les jours pour lesquels le taux de remise est supérieur à 10% ? CODE_JOUR LIBELLE_JOUR TX_REMISE 2 Mardi 10 3 Mercredi 20 5 Vendredi 20 Il existe 3 opérateurs logiques: 1. NOT (Négation logique) L'opérateur NOT inverse le résultat d'une expression logique. 2. AND (Et logique) L'opérateur AND nous permet de combiner plusieurs conditions dans une expression logique. L'expression logique retourne uniquement la valeur 'Vrai' lorsque toutes les conditions sont remplies. 3. OR (Ou logique) L'opérateur OR nous permet de combiner plusieurs conditions dans une expression logique. L'expression logique retourne la valeur 'Vrai' lorsque au moins une des conditions est remplie. Priorité des opérateurs logiques Lorsqu'on combine plusieurs conditions par des opérateurs logiques, le résultat final de l'expression logique dépend de l'ordre d'exécution des différentes conditions. Cet ordre est déterminé par la priorité des opérateurs logiques. Voici l'ordre prédéfini en SQL: 1. 2. 3. 4. Déterminer le résultat logique ('Vrai','Faux') des comparaisons (=, <, > etc.) Effectuer les négations (NOT) Effectuer les AND Effectuer les OR Pour modifier cet ordre d'exécution, nous pouvons utiliser des parenthèses afin de grouper les différentes conditions logiques. Exercice 2 : quels sont les élèves nés en 1986 ou en 1987 ? NOM_ELEVE PRENOM_ELEVE DATE_NAIS_ELEVE Bernardot Eric 05/03/1986 EPAULE Pierre 03/06/1987 Exercice 3 : quels sont les élèves nés en 1986 ou en 1987 et n’habitant pas Paris ? NOM_ELEVE PRENOM_ELEVE DATE_NAIS_ELEVE ADR_VILLE_ELEVE Bernardot Eric 05/03/1986 Nanterre IV-2- La clause DISTINCT Elle sert juste à omettre les enregistrements qui stockent un doublon dans le (ou les) champs spécifié(s). Seules les valeurs uniques des champs listés dans l’instruction SELECT apparaissent dans le résultat de la requête. Exemple : quels sont les différents modèles de véhicule ? SELECT MODELE FROM VEHICULE; SELECT DISTINCT MODELE FROM VEHICULE; MODELE Honda 500 CB Peugeot 206 Renault Clio Renault Scenic Suzuki 600 Bandit IV-3- La clause BETWEEN L'opérateur BETWEEN permet de rechercher si une valeur se trouve dans un intervalle donné, quel que soit le type des valeurs de référence spécifiées (alpha, numérique, date…) Exercice 4 : quels sont les véhicules mis en service entre le 1er janvier 2001 et le 15 juillet 2002 ? NUM_IMMAC MODELE DATE_MISE_SERV 1258NPA75 Renault Scenic 25/06/2001 1520NPE75 Renault Clio 14/07/2001 4507NPE75 Suzuki 600 Bandit 16/07/2001 IV-4- La clause IN L'opérateur IN (<Liste de valeurs>) permet de déterminer si la valeur d'un champ donné appartient à une liste de valeurs prédéfinies. Exercice 5 : quels sont les élèves prénommés Pierre ou Eric ? NOM_ELEVE PRENOM_ELEVE DATE_NAIS_ELEVE Bernardot Eric 05/03/1986 EPAULE Pierre 03/06/1987 IV-5- La clause LIKE L'opérateur LIKE permet d’effectuer une comparaison partielle. Il est surtout employé avec les colonnes contenant des données de type alpha. Il utilise les jokers % et _ (‘pour cent’ et ‘blanc souligné’). Le joker % remplace n'importe quelle chaîne de caractères, y compris la chaîne vide. Le blanc souligné remplace un et un seul caractère. Pour les manipulations pratiques, il faut se rendre compte que certains SGBD utilisent des caractères spéciaux différents pour représenter une séquence de caractères respectivement un caractère quelconque. MS-Access par exemple utilise les caractères suivants: Séquence de 0 ou plusieurs caractères SQL % MS-Access * _ ? Un seul caractère quelconque Exercice 6: quels sont les véhicules de marque Renault ? NUM_IMMAC MODELE DATE_MISE_SERV 1258NPA75 Renault Scenic 25/06/2001 4484MOB75 Renault Clio 03/04/2000 1520NPE75 Renault Clio 14/07/2001 Exercice 7 : Quels sont les véhicules immatriculés à Paris? Exercice 8 : quels sont les élèves qui possèdent un”r” en 2ème lettre de leur prénom? NOM_ELEVE PRENOM_ELEVE Bernardot Eric PETITPIED Arthur IV-6- Les valeurs calculées Dans une requête on a la possibilité de définir des champs à valeur calculée. On, peut utiliser les opérateurs mathématiques de base pour combiner différentes colonnes. La colonne obtenue n’est pas le champ d’une table. On peut lui donner un nom grâce au mot-clé AS. Exemple : Select nom_prod, qté*prix as montant from commande ; Exercice 9 : quel est l’âge de chaque élève ? NOM_ELEVE Bernardot PETITPIED MENVUSA EPAULE NISCOTCH PRENOM_ELEVE Eric Arthur Gérard Pierre Nicole age 19 20 25 18 20 IV-7- Les valeurs nulles Une valeur nulle ne correspond pas à zéro, ni à la chaîne vide. C’est une donnée indéterminée, ou qui n’est pas encore renseignée. Pour tester une valeur nulle, il suffit de rajouter « Where nom champ is NULL ». IV-8- L’ordre de la requête Cette clause permet de définir le tri des colonnes de la réponse, soit en précisant le nom littéral de la colonne, soit en précisant son n° d'ordre dans l'énumération qui suit le mot clef SELECT. ASC spécifie l’ordre ascendant et DESC l’ordre descendant du tri. ASC ou DESC peuvent être omis, dans ce cas c'est l'ordre ascendant qui est utilisé par défaut. Exercice 10 : donner la liste des élèves triés par âge : NOM_ELEVE MENVUSA NISCOTCH PETITPIED Bernardot EPAULE PRENOM_ELEVE Gérard Nicole Arthur Eric Pierre age 25 20 20 19 18 Exercice 11: donner la liste des véhicules tries par modèle et par date de mise en service décroissante. NUM_IMMAC 6287LND75 7896LMC75 1520NPE75 4484MOB75 1258NPA75 4507NPE75 MODELE DATE_MISE_SERV Honda 500 CB 13/01/2000 Peugeot 206 24/11/1999 Renault Clio 14/07/2001 Renault Clio 03/04/2000 Renault Scenic 25/06/2001 Suzuki 600 Bandit 16/07/2001 III- LES FONCTIONS D’AGRÉGATION ET LE REGROUPEMENT V- 1 Les fonctions d’agrégation Derrière ce mot compliqué se cachent quelques fonctions qui peuvent être utilisées à l'intérieur des requêtes de sélection pour faire des calculs sur le résultat de la requête. Imaginons la requête suivante: SELECT NOM_ELEVE, PRENOM_ELEVE, DATE_NAIS_ELEVE, ADR_VILLE_ELEVE FROM ELEVE WHERE ADR_VILLE_ELEVE <>"Paris"; Cette requête nous retourne tous les élèves n’habitant pas Paris. Il se peut très bien que l'utilisateur ne soit pas intéressé dans le détail, mais veut uniquement connaître le nombre d’élèves n’habitant pas Paris pour réaliser des statistiques. La requête correspondante est: SELECT Count(*) AS compte FROM ELEVE WHERE ADR_VILLE_ELEVE<>'Paris'; compte 4 Voici les fonctions d'agrégations les plus répandues: Détermine le nombre d'enregistrements du résultat de la requête. Tient compte de tous les enregistrements, y inclus ceux contenant des valeurs NULL COUNT (Nom d'un champ) Détermine le nombre des enregistrements pour lesquels le champ indiqué ne contient pas la valeur NULL Calcule pour tous les enregistrements sélectionnés, la SUM (Nom d'un champ) somme des valeurs du champ indiqué, pourvu que cette valeur soit différente de NULL. Calcule pour tous les enregistrements sélectionnés, la AVG (Nom d'un champ) moyenne des valeurs du champ indiqué, pourvu que cette valeur soit différente de NULL. Détermine pour tous les enregistrements MAX (Nom d'un champ) sélectionnés, la plus grande des valeurs du champ indiqué, pourvu que cette valeur soit différente de NULL. Détermine pour tous les enregistrements MIN (Nom d'un champ) sélectionnés, la plus petite des valeurs du champ indiqué, pourvu que cette valeur soit différente de NULL. COUNT (*) Exercice 12 : donner le taux maximum de réduction. maxitaux 20 V-2- Le regroupement La clause GROUP BY intervient sur le résultat d'un SELECT. En fait, les enregistrements résultant d'une requête de sélection sont groupés, de façon qu'à l'intérieur de chaque groupe, les valeurs pour la liste des champs de groupe soient identiques. Généralement, on applique une fonction d'agrégation à un ou plusieurs champs, ne faisant pas partie de la liste des champs de groupe. Exercice 13: pour chaque modèle, quel est le nombre de véhicules ? MODELE compte Honda 500 CB 1 Peugeot 206 1 Renault Clio 2 Renault Scenic 1 Suzuki 600 Bandit 1 On a la possibilité d'appliquer la clause ORDER BY au résultat d'un GROUP BY V-3- La clause HAVING Cette clause est l’équivalent de la clause WHERE pour un GROUP BY. Elle sert de critère de sélection pour les groupes. La condition HAVING s’applique à chacun des sous-ensembles et élimine ceux qui ne la satisfont pas. Elle ne peut être utilisée qu’avec la clause GROUP BY. Par exemple, dans l’exemple précédent, je souhaite n’obtenir que les modèles dont le nombre de véhicules est supérieur à 1 : SELECT MODELE, count(*) as compte FROM VEHICULE group by modele having count(*)>1; VI –LA JOINTURE ENTRE LES TABLES La plupart des BD réelles ne sont pas constituées d'une seule table, mais d'un ensemble de tables liées entre elles via certains champs. Par conséquent, les requêtes correspondantes ne sont pas ciblées sur une, mais sur plusieurs tables. La jointure permet de relier plusieurs tables entre elles via des champs communs (clé étrangère / clé primaire). Exemple : on veut connaître le modèle de véhicule associé à chaque élève : SELECT NOM_ELEVE, PRENOM_ELEVE, MODELE FROM VEHICULE, ELEVE WHERE VEHICULE.NUM_IMMAC = ELEVE.NUM_IMMAC; NOM_ELEVE Bernardot PETITPIED NISCOTCH MENVUSA EPAULE PRENOM_ELEVE Eric Arthur Nicole Gérard Pierre MODELE Renault Scenic Renault Scenic Renault Clio Renault Clio Suzuki 600 Bandit La clause FROM contient les deux tables impliquées dans la jointure. La clause WHERE contient ce qu'on appelle la condition de jointure. Dans notre exemple, la condition de jointure demande l'égalité des valeurs pour les champs num_imac de eleve et num_immac de vehicule. La clause SELECT contient les noms des champs à afficher. Access représente la jointure d’une manière différente : SELECT NOM_ELEVE,PRENOM_ELEVE,MODELE FROM VEHICULE INNER JOIN ELEVE ON VEHICULE.NUM_IMMAC = ELEVE.NUM_IMMAC; Exercice 14: Afficher pour chaque leçon, le nom du moniteur et le nom de l’élève: CODE_LECON NOM_MONITEUR 1 ALBERT 2 JEAN 3 GISELE 4 ALBERT 5 ALBERT NOM_ELEVE Bernardot Bernardot Bernardot Bernardot Bernardot PRENOM_ELEVE Eric Eric Eric Eric Eric DATE 26/11/2004 05/12/2004 07/12/2004 08/12/2004 19/12/2004 Exercice 15 : en plus ajouter le nom du jour et le taux de remise. Exercice 16: afficher pour chaque élève, le nombre de leçons prises : NOM_ELEVE Compte Bernardot 5 HEURE 14:00:00 09:00:00 16:00:00 12:00:00 18:00:00