1

Bases de Données

Alain BUIProfesseur

Département de Mathématiques et Informatiquealain.bui@univ-reims.fr

Langage SQL

• Structered Query Langage– Standard, langage relationnel incontournable– 600 pages, cours donne un aperçu …

• Historique– 1970 Codd modèle relationnel– 1974 SEQUEL SEQUEL/2 (System /R, IBM)– 1981 Oracle (SQL)

– 1983 DB2 (IBM)– 1986 SQL/86 normalisé– 1989 SQL/89 (SQL/1)– 1992 SQL/92 (SQL/2)– … SQL/3

2

• SQL = – LMD Langage de manipulation de données– LDD Langage de Définition de Données– LCD Langage de Contrôle de Données

– Une requête SQL peut être• Intéractive• Incluse dans un programme d’application

LMD

• Syntaxe MinimaleSELECT <liste d’attributs projetés> FROM <liste de relations>

– Syntaxe possiblement enrichie de très nombreuses clauses permettant d’exprimer

• Projections• Restrictions

• Jointures• Tris• …

3

Bases de données exemple

• Produits (pno, design, prix, poids, couleur)• Fournisseurs (fno, nom, adresse, ville)• Commandes (cno, fno, pno, qté)

• Projection– SELECT <liste attributs> FROM <liste relations>

– Numéro et nom de tous les fournisseurs• SELECT fno, nom FROM fournisseurs

– Ensemble des attributs• SELECT * FROM <liste relations>

– Élimination des doublons (pas par défaut)• SELECT DISTINCT couleur FROM Produits

4

• RestrictionSELECT <attributs> FROM <relations>WHERE <critères de restriction>

– Donner les données sur les produits dont le poids est > 15– Donner les désignations de produits différents dont le poids est

compris entre 15 et 40• Opérateur BETWEEN

– Donner le nom des fournisseurs localisés à Amiens et Reims• Opérateur ensembliste IN

– Quels sont les noms des fournisseurs commençant par D• Opérateur LIKE (joker = _ ; séquence de caractères = % )

– Donner la liste des fournisseurs dont l’adresse est renseignée• Opérateur NULL• NOT peut préfixé les opérateurs IN, LIKE, BETWEEN et NULL

• Tri– ORDER BY <attribut 1> [ASC / DESC] ,…, <attribut n>

[ASC / DESC]

– Donner la liste des produits rouges, verts et bleus, triés sur la désignation dans l’ordre croissant et la couleur dans l’ordre décroissant

• Présentation des colonnes– SELECT <attributs> AS [nom apparaissant pour la colonne]FROM …– Donner les numéros de produits et leur désignation avec des

noms de colonnes

5

Jointures

• Produit cartésien– SELECT * FROM <relations>

– Éviter les ambiguïtés en préfixant le nom des attributs utiliséspar le nom de la relation concernée suivi d’un point : R.A

– On peut aussi utiliser le mot clé AS dans la clause FROM pour utiliser des synonymes pour les relations

• Jointure naturelle– SELECT * FROM R,S WHERE R.A = S.A

– Noms des fournisseurs avec le n° de produits et la quantité commandées

• Sans synonymes• Avec synonymes

– Jointures exécutées en cascade:Donner la liste (noms des fournisseurs + noms des produits) en

commande

– Auto-jointure:Donner la liste des couples de numéros de fournisseurs situés

dans la même ville en évitant les doublons par symétrie

6

SQL2

• Expression des jointures en SQL 2– Pas toujours supporté par tous les SGBD

– SELECT … FROM <relations> INNER /* jointure classique*/LEFT /*jointure externe*/RIGHT /*jointure externe*/

JOIN ON <condition>

– Jointure interne – Jointure externe (conserve les tuples d’une des deux relations)

Calculs

• Attributs calculés :Donner les produits avec leur prix HT et TTC

• Des stats …AVG, COUNT, MAX, MIN, SUM– Nombre de commandes du produit 102?– Quantités min max et la moyenne des qtés à livrer pour

pno=102?

– Ces opérations ne peuvent pas s’imbriquer.

7

Agrégats

– Un agrégat est un partitionnement horizontal d’une relation selon des valeurs d’un groupe d’attributs suivi d’un regroupement par une fonction de calcul.

• Exemple• Soit une relation R(A,B,C,D,E)• F_A BCD ( R )

• Groupe = ens. des • tuples de même valeur

fea

jik

dcb

fe adc b

EDCBAR

regroupement

regroupement

• Regroupements– Group By <Attribut1> , … , <Attributp>

• Groupe en une seule ligne toutes les lignes pour lesquelles les attributs de regroupement ont la même valeur

• Cette clause se place juste après la clause Where ou après la clause From si la clause Where n’existe pas

– Donner le nombre de commandes par fournisseur– Donner le nombre de commande par fournisseur pour des

commandes de quantité � 9

8

• Restriction sur les regroupements– Having <prédicat> : sert à préciser quels groupes doivent être

sélectionnés.– Se place après la clause Group By

– Donner les fournisseurs qui ont plus de 5 commandes– Donner les fournisseurs qui fournissent plus d’un produit

Synthèse

SELECT <liste d’attributs projetés>FROM <liste de relations>[WHERE < restrictions - jointure>][GROUP BY <liste d’attributs à partitionner>

[HAVING <condition>] ][ORDER BY <attribut> [ASC/DESC]

[<attribut> [ASC/DESC] ] … ]

• Pour les commandes dont le numéro est > 250, quels sont les noms des fournisseurs avec les quantités maximales commandées, dont la moyenne des quantités commandées est supérieure à 6 articles. Trier le résultat par ordre décroissant des noms.

9

Sous requêtes

– Where -> conditions par comparaisons sur des valeurs– Expressions de conditions sur des relations ??

• Sous requêtes: décrire des requêtes complexes permettant d’effectuer des opérations dépendant d’autres requêtes.

– … WHERE … – … HAVING …

– Sous requête renvoie• Une valeur unique => opérateurs de comparaisons classiques• Un attribut => opérateurs IN, EXISTS, opérateurs de comparaisons

classiques + ALL ou ANY

sous requête

– Restriction classique : une valeur SELECT … WHERE pno > (SELECT pno FROM …)

– Plusieurs valeurs : succès si valeur testée est dans le résultatde la sous requête (on peut aussi utiliser NOT)

SELECT … WHERE pno IN (SELECT pno FROM …)

SELECT … WHERE pno > ALL (SELECT pno FROM …)

SELECT … WHERE EXISTS (SELECT … WHERE pno …)

On peut exprimer jointure et autres par ce biais.

10

Quelques exemples

– Pour chaque question donner une requête imbriquée et une non imbriquée.

– Donner les fournisseurs qui habitent la même ville que le fournisseur n°10.

– Donner les fournisseurs dont au moins un des produits est fourni aussi par un fournisseur d’un produit rouge.

L’expression SQL EXISTS (SELECT… FROM…)

est évalué à Vrai ssi le résultat de l’évaluation du SELECT … FROM est non vide (le résultat donne au moins un tuple)

– Donner le nom de tous les fournisseurs du produit 102.

SELECT f.nom FROM fournisseurs AS f WHERE EXISTS (SELECT * FROM commandes AS c

WHERE c.pno=102 AND f.fno=c.fno)

Le résultat retourné n’a pas d’importance car il suffit que le résultat soit non vide

Imbrication car un champ de la requête principale est utilisé dans la ss-requêteSans cela on aura tous les noms!

11

– Donner deux solutions équivalentes avec le IN puis sans sous-requêtes.

SELECT f.nom FROM fournisseurs AS f WHERE f.fno IN (SELECT c.fno FROM commandes AS c WHERE c.pno =102)

SELECT f.nom FROM fournisseurs AS f, commandes AS c WHERE c.fno = p.pno AND c.pno = 102

Exercice: 3 requêtes pour obtenir le nom des fournisseurs ne fournissant pas le produit 102.

Division

– SQL n’offre pas la possibilité d’exprimer directement le quantificateur ∀. On utilise une négation du quantificateur ∃.

– La question « Donner le nom des fournisseurs livrant tous les produits »

devient « Donner le nom des fournisseurs pour lesquels il n’existe aucun

produit non livré »

Procéder par étapes

12

– Produits non livrés• SELECT pno FROM produits AS p WHERE NOT EXISTS

(SELECT * FROM commandes WHERE c.pno = p.pno)

• SELECT pno FROM produits AS p WHERE p.pno NOT IN (SELECT c.pno FROM commandes as c)

– Réponse à la question• SELECT f.nom FROM fournisseurs AS f WHERE NOT EXISTS

(SELECT null FROM produits AS p WHERE NOT EXISTS (SELECT null FROM commandes AS c

WHERE c.pno = p.pno AND f.fno=c.fno) )

• Division R(A,B) / S(B) = T(A)={a ∈ R(A) / ∀ b ∈ S(B), (a,b) ∈ R(A,B)}={a ∈ R(A) / ∃ b ∈ S(B), (a,b) ∉ R(A,B)}

– La traduction « mot à mot » donne – SELECT A FROM R AS R1 WHERE NOT EXISTS

(SELECT B FROM S WHERE NOT EXISTS (SELECT A, B FROM R WHERE

R1.A = A AND S.B = B ) )

– commandes (fno,pno) / produits (pno) = T(fno) c’est à dire les fournisseurs de tous les produits.

13

Opérations ensemblistes

– UNION / INTERSECT / EXCEPT s’intercalent entre deux SELECT

– Les opérations parlent d’elles mêmes …

– Donner les produits dont le poids est > 20 ainsi que les produits commandés par le fournisseur n°150

– Donner les fournisseurs ayant des commandes en cours sauf ceux localisés à Reims

Mise à jour d’informations

• Insertion– INSERT INTO <relation> [att1,…, attn] VALUES

(val1,…,valn)– INSERT INTO <relation> (att1,…, attn) SELECT …

• Insérer un nouveau produit

Insérer des tuples dans une relation existante dont la valeur de certains attributs n’est pas connus (valeur NULL par défaut)

• Insérer les valeurs produit n°800 , écrou de 8FF dans produits

– Une relation citée dans le champ INTO du INSERT ne peut pas être citée dans le champ FROM du ss-select de ce même INSERT

14

• Mise à jour– UPDATE <relation> SET att1 = val1 , … , attn = valn

WHERE <condition> – UPDATE <relation> SET (att1, … , attn) = (SELECT …)

WHERE <condition> • Augmenter le prix de tous les produits de couleur or de 15%

• Suppression– DELETE FROM <relation> WHERE <condition>Suppression de tuples selon une condition fixée

• Supprimer les produits dont le prix est inférieur à 100FF

• DELETE FROM <relation> donne quoi comme résultat?

Exercice

• BD suivante– Buveurs (nb, nom, ville)– Vins (nv, cru, mill, region, couleur)– Conso (nb, nv, qte)REPONSE EN SQL1. Noms des buveurs rémois?2. Cru des bordeaux rouges de l’année 1994?3. Noms des buveurs rémois qui ont consommé un vin rouge?

Donner 3 réponses dont 2 utilisant des sous-requêtes

4. Noms des buveurs qui ne boivent pas de vins?Donner 2 réponses utilisant des sous-requêtes

5. Donner une version en algèbre relationnelle pour chacune des questions précedentes.

15

Exercice suite

• SQL seul

– Donner la moyenne des consommations de vins rouges.

– Donner la moyenne des consommations par couleur de vin

– Donner la quantité totale de vins consommée par chaque buveur

– Donner le nom des buveurs qui consomment plus que la moyenne.