. SQL Requêtes simples . BD4 A.D., S.B. Licence MASS, Master ISIFAR, Paris-Diderot Février 2013 BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Introduction 1/19 SQL 2/19 . . . . . . . . . . . . Février 2013 1 / 19 Outline 1. Introduction 2. Requêtes mono-relation 3. Requêtes multi-relations BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) Février 2013 2 / 19 Introduction SQL - Introduction . SQL : Structured Query Language . Langage permettant : interrogation des données (requêtes) définitions des données et des contraintes structurelles sur celles-ci. . manipulation des données (insertion, suppression, mise à jour) définition des vues et des index administration des bases de données . Plusieurs fois normalisés par l'ISO : . ▷ SQL-86, ▷ SQL-89, ▷ SQL-92, ▷ SQL-99. . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Introduction 3/19 . . . . . Février 2013 . 3 / 19 Introduction Dans ce premier cours : SQL comme Langage de manipulation de données (LMD). Une instruction SQL permet de combiner : restriction (sélection), projection, renommage, ... SELECT < liste attribut > FROM < table > WHERE < condition >; . correspond à : . . π<listeattribut> (σ<condition> (< table >)) BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 4/19 . . . . . Février 2013 . 4 / 19 Introduction Syntaxe - requêtes simples SELECT [ DISTINCT ] * ou < liste attribut > FROM < liste de tables > [ WHERE < condition >] * : sélection de tous les attributs. [...] : condition facultative. DISTINCT : supprime les doublons. . Base de données exemple : . Employe (NoEmpl , NomE , Annee , TelE , Nodept ) Fournisseur (NomF ,VilleF , AdresseF ) Piece (NomP , Prix , Couleur ) . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Introduction 5/19 . . . . . . . . . . Février 2013 5 / 19 Syntaxe - requêtes simples . SELECT NomF , VilleF FROM Fournisseur ; . SELECT * FROM Fournisseur ; Equivalent à : πNomF ,VilleF (Fournisseur ) . Equivalent à : Fournisseur . . SELECT * FROM Fournisseur WHERE VilleF ='Paris '; Equivalent à : σVilleF =′ Paris ′ (Fournisseur ) . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 6/19 . . Février 2013 6 / 19 Requêtes mono-relation Structure de base . La . structure de base d'une requête s'appuie sur les trois mots clés suivants : SELECT correspond à l’opérateur de projection sur la liste d’attributs demandée il peut aussi être suivi de fonctions d’attributs FROM indique la ou les relations concernées WHERE précise une condition et correspond à l’opération de restriction/sélection en algèbre relationnelle. BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Requêtes mono-relation 7/19 . . . . . . . . Février 2013 7 / 19 Syntaxe de la Condition . .Une condition se construit à l'aide des opérations suivantes : Comparaison avec opérateurs : =, <>, >,<, >=, <= SELECT * FROM Employe WHERE NomE <>'Durand ' Combinaison de conditions à l'aide des opérateurs : AND, OR, NOT SELECT DISTINCT NomF FROM Fournisseur WHERE ( VilleF ='Londres ') OR ( VilleF ='Paris '); Attention aux priorités dans l'évaluation des expressions logiques. BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 8/19 . . . . Février 2013 8 / 19 Requêtes mono-relation Syntaxe de la Condition Test BETWEEN permettant de vérifier si la valeur d'un attribut est comprise entre deux constantes SELECT NomE FROM Employe WHERE Annee BETWEEN ' 22/06/03 ' AND ' 25/09/03 '; Test à NULL signifiant que la valeur est inconnue SELECT * FROM Employe WHERE TelE IS NULL ; BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Requêtes mono-relation 9/19 . . . . . Février 2013 . 9 / 19 Syntaxe Condition Test d'appartenance IN qui permet de vérifier si la valeur d'un attribut appartient à une liste de constantes, SELECT * FROM Fournisseur WHERE VilleF IN ('Paris ', 'Londres '); Test LIKE permettant de vérifier si un attribut de type chaîne de caractères contient une ou plusieurs sous-chaînes ; _ : remplace n'importe quel caractère % : remplace n'importe quel chaîne de caractère. SELECT VilleF FROM Fournisseur WHERE VilleF LIKE 'Saint %'; BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 10/19 . . . . . Février 2013 . 10 / 19 Requêtes multi-relations Requêtes multi-relations . Exemples jusqu'à présent : une seule relation en jeu. . . On peut : . utiliser plusieurs tables dans la clause FROM Construire des conditions complexes impliquant plusieurs tables (conditions "séparées" ou jointure). . . Produit cartésien. . SELECT * FROM Employe , Piece WHERE Employe .Annee ='06 ' AND Piece .NomP='xx '; . σAnnee=′ 06′ ∧NomP=′ xx ′ (Employe × Piece) BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Requêtes multi-relations 11/19 . . . . . . . . Février 2013 11 / 19 Requêtes multi-relations . Pour lever les ambiguités . Nom des attributs préfixé par nom des tables dans la condition Renommage des tables possible dans la clause FROM . . Retour sur exemple précédent : . SELECT * FROM Employe E, Piece P WHERE E. Annee ='06 ' AND P.NomP='xx '; . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 12/19 . . . . Février 2013 12 / 19 Requêtes multi-relations Jointure . Jointures définissables en faisant porter une comparaison (=, <, ≤, ...) sur des attributs de deux tables différentes . . On ajoute une table Livraison à notre base : . Employee (NoEmpl , NomE , Annee , TelE , Nodept ) Fournisseur (NomF , VilleF , AdresseF ) Piece (NomP , Prix , Couleur ) Livraison (NumLiv ,NomP ,NomF ,DateLiv , Quantite ) Attention : schéma simpliste... . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Requêtes multi-relations 13/19 . . . . . . . . Février 2013 13 / 19 Jointure . Villes des fournisseurs qui ont effectué des livraisons et dates des livraisons . SELECT VilleF , DateLiv FROM Fournisseur , Livraison WHERE Fournisseur .NomF = Livraison .NomF Equivalent (à renommage près...) : .πF .NomF ,DateLiv (Fournisseur ▷◁F .NomF =L.NomF Livraison) BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 14/19 . . . . Février 2013 14 / 19 Requêtes multi-relations Jointure . Villes des fournisseurs qui ont effectué des livraisons de la piece 'xx' et dates des livraisons . SELECT VilleF , DateLiv FROM Fournisseur F, Livraison L WHERE F.NomF = L.NomF AND F.NomP='xx ' . . Villes des fournisseurs qui ont effectué des livraisons de la piece 'xx', prix unitaire de cette pièce et dates des livraisons . SELECT F.VilleF , L.DateLiv , P.Prix FROM Fournisseur F, Livraison L, Piece P WHERE F.NomF = L.NomF AND F.NomP='xx ' AND P.NomP='xx ' . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Requêtes multi-relations 15/19 . . . . . Février 2013 . 15 / 19 Expression en calcul relationnel . Villes des fournisseurs qui ont effectué des livraisons de la piece 'xx', prix unitaire de cette pièce et dates des livraisons . SELECT F.VilleF , L.DateLiv , P.Prix FROM Fournisseur F, Livraison L, Piece P WHERE F.NomF = L.NomF AND F.NomP='xx ' AND P.NomP='xx ' . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 16/19 . . . . . Février 2013 . 16 / 19 Requêtes multi-relations Jointure naturelle . La norme SQL-92 . prévoit (si besoin) d'utiliser une syntaxe particulière pour la jointure naturelle. . . Noms et num. de liv. des fournisseurs de Paris qui ont livré : . SELECT DISTINCT NomF , NumLiv FROM Fournisseur NATURAL JOIN Livraison WHERE VilleF ='Paris '; . . Ville des fournisseurs qui ont livré des pièces rouges : . SELECT DISTINCT VilleF FROM Fournisseur NATURAL JOIN Livraison NATURAL JOIN Piece WHERE Couleur ='rouge '; . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Requêtes multi-relations 17/19 . . . . . Février 2013 . 17 / 19 Equi-jointure . Equi-jointure . en spécifiant les attributs de jointure (qui ne sont mis qu'une seule fois dans le résultat) SELECT DISTINCT VilleF FROM Fournisseur NATURAL JOIN Livraison NATURAL JOIN Piece WHERE Couleur ='rouge '; . . Equi-jointure en spécifiant le critère de jointure . SELECT DISTINCT NomF , NumLiv FROM Fournisseur F JOIN Livraison L WHERE VilleF ='Paris ' . BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 18/19 ON F.NomF = L.NomF . . . . . Février 2013 . 18 / 19 Requêtes multi-relations Jointure externe LEFT OUTER JOIN (resp. RIGHT OUTER JOIN) : on garde les tuples de la table de gauche (resp. droite) qui n'ont pas de complément dans l'autre table. FULL OUTER JOIN : on garde les tuples de chacune des tables qui n'ont pas de complément. On complète les valeurs non renseignées par NULL SELECT DISTINCT NomF , NumLiv FROM Fournisseur F LEFT OUTER JOIN Livraison L ON F . NomF = L . NomF WHERE VilleF = ' Paris ' BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL Conclusion 19/19 . . . . . . . . Février 2013 19 / 19 Conclusion Première approche de SQL : sélection, projection, jointure,... Toutes les requêtes précédentes : sans négation ! Reste à considérer comme LMD : ▷ ▷ ▷ ▷ requêtes ensemblistes, requêtes imbriquées, négation, agrégats (somme, moyenne, partitionnement, etc). Voir SQL comme un Langage de Définition de Données (LDD). BD4 (Licence MASS, Master ISIFAR, Paris-Diderot) SQL 20/19 . . . . Février 2013 20 / 19