Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Support de cours bases de données M. FOTSO CHATUE HERMANN C H A P IT R E 3 La n

Support de cours bases de données M. FOTSO CHATUE HERMANN C H A P IT R E 3 La nga ge de R equ êtes Str u ctu r é S Q L Introduction: pourquoi le langage de manipulation des données? SQL (Structured Query Language) est le langage de manipulation des données relationnelles le plus utilisé aujourd’hui. Il est devenu un standard de fait pour les relationnels. Il possède des caractéristiques proches de l’algèbre relationnelle (jointure par emboîtement) et d’autres proches du calcul des tuples (variables sur les relations). Les exemples dans ce chapitre s’appuient sur la base de données relative aux fournisseurs (F), produits (P), usines (U) et livraisons (PUF), décrite par le schéma suivant: F (NF, nomF, statut, ville) P (NP, nomP, poids, couleur) U (NU, nomU, ville) PUF (NP, NU, NF, qt) 1. Format de base d'une requête SELECT Liste des noms d'attributs du résultat FROM Nom d'une relation (ou de plusieurs relations) [ WHERE Condition logique qui définit les tuples du résultat ] Exemple: nom et poids des produits rouges. SELECT nomP, poids FROM P WHERE couleur = "rouge" Exemple : tous les renseignements sur tous les fournisseurs. SELECT NF, nomF, statut, ville FROM F ou SELECT * /* l'étoile signifie: tous les attributs*/ FROM F Un résultat sans doubles Les SGBD commercialisés (dont les SQL…) ne suppriment pas automatiquement les doubles. La clause DISTINCT permet à l'utilisateur d'avoir un résultat sans double Exemple : liste des couleurs qui existent. SELECT DISTINCT couleur FROM P Un résultat trié La clause ORDER BY permet de définir un ordre de tri pour les tuples du résultat . Exemple : liste des fournisseurs de Lausanne par ordre alphabétique. SELECT nomF, NF, statut FROM F WHERE ville = "Lausanne" ORDER BY nomF ASC , NF ASC Si plusieurs fournisseurs ont le même nom, ils seront classés selon leurs numéros. 2. Recherche avec blocs emboîtés Exemple : numéros des fournisseurs de produits rouges ? ensemble des numéros des produits rouges : SELECT NP FROM P WHERE couleur = "rouge" ensemble des numéros des fournisseurs de produits rouges : SELECT NF FROM PUF WHERE NP IN ( SELECT NP FROM P WHERE couleur = "rouge" )  Le mot clef IN signifie "appartient", l'opérateur mathématique de la théorie des ensembles ( La phrase NP IN (SELECT NP FROM P WHERE couleur = "rouge") est une condition logique, signifiant "la valeur de NP est dans l'ensemble des numéros de produits rouges", ce qui est vrai ou faux.  Dans le WHERE , la condition logique peut avoir plusieurs formes : - elle peut être composée de conditions élémentaires connectées par AND, OR, NOT, et par des parenthèses; - <opérateur de comparaison> ::= >  <  ≥  ≤  =  ≠ - <opérateur d'appartenance> ::= IN  NOT IN Exemple : noms des fournisseurs n°1, 2, 3. SELECT nomF FROM F WHERE NF = 1 OR NF =2 OR NF = 3 ou SELECT nomF FROM F WHERE NF IN (1, 2, 3) 3. Qualification des noms d'attributs Notations : NP : attribut P.NP, PUF.NP : attributs qualifiés par le nom d'une relation Règle 1 : Un nom d'attribut non qualifié, référence la relation la plus interne qui a un attribut de ce nom- là. Règle 2 : On peut renommer localement une relation dans la clause FROM . Exemple: ... FROM PUF PUF1 ... la relation PUF s'appelle alors PUF1 pour le SELECT correspondant à ce FROM uniquement. Exemple : noms des fournisseurs ne livrant pas le produit numéro 2. SELECT nomF FROM F WHERE 2 NOT IN (SELECT NP /* ensemble des NP FROM PUF livrés par ce WHERE PUF.NF = F.NF) fournisseur */ Dans le WHERE du SELECT interne, on aurait pu tout aussi bien écrire : WHERE NF = F.NF (cf. règle 1). Exemple : numéros des fournisseurs livrant le même produit que le fournisseur 1 en une quantité plus grande. Sur le schéma, la requête peut être décrite comme suit : =1 PUF ( NP, NU, NF, qt ) = > PUF ( NP, NU, NF, qt ) Ce qui s'écrit en SQL: SELECT NF FROM PUF PUFX /* PUF est renommé*/ WHERE NP IN (SELECT NP /* ensemble FROM PUF des produits du WHERE NF = 1 AND qt < PUFX.qt) fournisseur 1 */ 4. Recherche sur plusieurs relations simultanément Format général: SELECT Ai … FROM R1, R2…, Rn WHERE < condition de jointure entre les Ri > AND < condition(s) de la requête > Exemple : pour chaque produit livré, le nom du produit et les villes de leurs fournisseurs. SELECT nomP, ville FROM P, F, PUF WHERE PUF.NP = P.NP AND PUF.NF = F.NF 5. Recherche avec quantificateurs : SOME, ANY, ALL SQL permet d'écrire des conditions où apparaissent des quantificateurs proches de ceux de la logique ("il existe" (), "quelque soit" () ), grâce aux mots clefs SOME, ANY et ALL Les mots clefs SOME et ANY ont exactement la même signification; ce sont des synonymes. Syntaxe Cette commande s’utilise dans une clause conditionnelle juste après un opérateur conditionnel et juste avant une sous-requête. L’exemple ci-dessous démontre une utilisation basique de ANY dans une requête SQL : SELECT * FROM table1 WHERE condition > ANY ( SELECT * FROM table2 WHERE condition2 ) Cette requête peut se traduire de la façon suivante : sélectionner toutes les colonnes de table1, où la condition est supérieure à n’importe quel résultat de la sous-requête. A savoir : les opérateur conditionnels peuvent être les suivants : =, <, >, <>, !=,<=, >=. ALL demande une comparaison à toutes les valeurs pour que le prédicat soit vrai ANY (ou SOME qui est un synonyme) est vrai si, au moins une valeur de l'ensemble répond vrai à la comparaison. Le mot clef IN est équivalent à un quantificateur existentiel (SOME ou ANY) avec l'opérateur de comparaison d'égalité. SOME et ANY sont donc plus puissants. De même, les requêtes avec un quantificateur universel (ALL) et un comparateur d'égalité peuvent s'écrire avec une condition ensembliste (voir le paragraphe suivant). Cependant le mot clef ALL ne permet pas d'exprimer toutes les requêtes contenant un quantificateur du type "quelque soit". On peut alors écrire la requête inverse avec un "NOT EXISTS" (voir paragraphe plus loin). Par exemple la requête "chercher les X qui pour tout Y satisfont telle condition" peut aussi s'exprimer: "chercher les X tels qu'il n'existe aucun Y qui ne satisfait pas telle condition". Exemples: Ensemble des numéros des fournisseurs de produits rouges : SELECT NF FROM PUF WHERE NP = ANY ( SELECT NP FROM P WHERE couleur = "rouge" ) Ensemble des numéros des fournisseurs qui ne fournissent que des produits rouges : SELECT FROM NF F WHERE "rouge" = ALL ( SELECT couleur FROM P WHERE NP = ANY ( SELECT NP FROM PUF WHERE PUF.NF = F.NF) ) 6. Recherche avec des conditions sur des ensembles Dans les paragraphes précédents, les conditions élémentaires portant sur une valeur d'attribut ont été définies. D'autres types de conditions élémentaires permettent de comparer des ensembles entre eux. Ce sont: 6.1. Test d’égalité d’ensembles: <ensemble 1> = <ensemble 2> <ensemble 1> ≠ <ensemble 2> 6.2. Test d’inclusion d’ensembles: <ensemble 1> CONTAINS <ensemble 2> Cette condition signifie que l'ensemble 1 contient (ou est égal à) à l'ensemble 2, ce qui en théorie des ensembles s'écrirait : <ensemble 1>  <ensemble 2>. La condition: <ensemble 1> NOT CONTAINS <ensemble 2> est la négation de la précédente; elle est vraie si un élément de l'ensemble 2 n'appartient pas à l'ensemble 1. Exemple : noms des fournisseurs qui fournissent tous les produits rouges. SELECT nomF FROM F WHERE (SELECT NP FROM PUF /* ensemble des produits du WHERE NF = F. NF) fournisseur F*/ CONTAINS (SELECT NP FROM P /* ensemble des produits rouges*/ WHERE couleur ="rouge") 6.3. EXISTS < ensemble> Cette condition teste si l'ensemble n'est pas vide (ensemble ≠ Ø). Exemple : noms des fournisseurs qui fournissent au moins un produit rouge. SELECT nom F FROM F WHERE EXISTS (SELECT * FROM PUF, P WHERE NF = F.NF AND couleur ="rouge" AND PUF.NP=P.NP) Il existe aussi la condition inverse : NOT EXISTS <ensemble> qui teste si l'ensemble est vide. 7. Fonctions d'agrégation SQL offre les fonctions d'agrégation usuelles: cardinal : COUNT moyenne : AVG minimum et maximum : MIN, MAX total : SUM qui opèrent sur un ensemble de valeurs prises par un attribut, ou pour COUNT uniquement, sur un ensemble de tuples. Exemple : quel est le nombre de livraisons faites par le fournisseur 1 ? SELECT COUNT (*) /* on compte les tuples de PUF tels FROM PUF que NF = 1 */ WHERE NF=1 Exemple : combien de produits différents a livré le fournisseur 1 ? Attention : il faut ôter les doubles, car COUNT compte toutes les valeurs, y compris les valeurs doubles. SELECT COUNT (DISTINCT NP) FROM PUF WHERE NF=1 8. Recherche avec partition des tuples d'une relation 8.1. Clause Group by Exemple: combien de produits différents ont uploads/Industriel/ chap-3-et-4-bases-de-donnees 1 .pdf