BDW1 : Bases de données et Programmation Web SQL : … · 5 Bienvenue chez les Ch’tis 2008 Comédie 5 11 ... les autres clauses seront vues plus tard car elles impliquent des requêtes

1/83

BDW1 : Bases de données et Programmation Web

SQL : Partie 1 - de la projection à la jointure

Nicolas Lumineau

[email protected]

Université Claude Bernard Lyon 1

Licence 2e année - 2017/2018

http://liris.cnrs.fr/nicolas.lumineau/> Licence 2 > BDW1

http://liris.cnrs.fr/nicolas.lumineau/

2/83

Enseignements de BDW1

1 [BD & Web] Introduction2 [BD]La syntaxe SQL

SQL : de la projection à la jointureSQL : sous-requêtes et regroupementsFonctions diverses et commandes SQL

3 [BD]Conception de base de donnéesSchéma Entité/AssociationDu modèle conceptuel au modèle relationnel

4 [Web] Programmation WebLe langage HTML/CSSLe langage PHP

Interrogation d’une BD via PHP

5 [BD] Optimisation de requêteAlgèbre RelationnelleTransformation d’arbres algébriques

UCBL - Dept. Info. - Nicolas Lumineau BDW1 : BD & Prog. Web 2

3/83

Introduction Projection, sélection et tri Produit-cartésien et jointures Les jointures

Qu’est ce que SQL ?

SQL = Structured Query Language

SQL est un langage concret pour interagir avec le modèlerelationnel :

Un langage de manipulation de donnéesUn langage de description de donnéesUn langage pour administrer la base, gérer les contrôles d’accès

SQL est un langage déclaratif qui permet à l’utilisateur dedéclarer/caractériser un résultat...

... sans avoir à dire comment le calculer !


4/83


Historique de SQL

Origine : IBM en 1974

Standards :SQL-87 : 1987 (ISO)SQL-2 : 1992SQL-3 : 1999SQL-2003SQL-2006SQL-2008SQL-2011


5/83


SQL et les SGBD relationnels

De nombreux SGBD utilisent le langage SQL :

MySQLPostgreSQLOracleSQL ServerMariaDB. . .

Chaque éditeur de SGBD à sa propre implémentation de laspécification SQL


6/83


Il y a la théorie et la pratique

Rappel théorique sur le modèle relationnelIl n’y a pas de doublonsTous les attributs ont une valeurLes tuples ne sont pas ordonnés

et en SQL :

On a la possibilité d’avoir des doublons ou de les supprimerOn a la possibilité d’ordonner le résultat des requêtesLes attributs peuvent ne pas avoir de valeurOn n’a pas forcément les raccourcis syntaxiques correspondantà chaque opérateur ensembliste


7/83

Partie 3 : Le langage SQL

1 Projection, sélection et tri

2 Produit-cartésien et jointures

3 Les jointures

8/83


Au commencement...

L’informaticien dispose de tables implémentées dans un SGBD detype MySQL

Artiste(ida, nom, prenom, nation)ida prenom nom nation1 Jean DUJARDIN FR2 Scarlett JOHANSSON USA3 Tom CRUISE USA4 Bérénice BEJO FR-AR5 Dany BOON FR6 Emily BLUNT UK7 Doug LIMAN USA8 Luc BESSON FR9 Michel HAZANAVICIUS FR

JoueDans(ida, idf, nom)ida idf nom sal1 3 George Valentin 0,31 4 Hubert Bonisseur de La Bath 0,22 2 Lucy Miller 143 1 Bill Cage 204 4 Larmina El Akmar Betouche NULL4 3 Peppy Miller NULL5 5 Antoine Bailleul 1,36 1 Rita Vrataski NULL

Film(idf, titre, annee, genre, real, budget)idf titre annee genre real budget1 Edge of tomorrow 2014 SF 7 1782 Lucy 2014 SF 8 403 The Artist 2011 Drame 9 94 OSS 117: Le Caire, Nid d’espions 2006 NULL 9 145 Bienvenue chez les Ch’tis 2008 Comédie 5 11

Pays(code, nom)code nomFR France

USA Etats-UnisUK Royaume-UniAR Argentine


9/83


Dans une requête SQL...

... il y a des clauses SQL et différents mots-clés qui vont permettrede spécifier le résultat que vous souhaitez.

Clauses SQLSELECT ...FROM ...WHERE ...ORDER BY ...GROUP BY ...HAVING ...

Mots-clés SQLAND , OR , NOTDISTINCT , DESCIN , ANY , ALLBETWEEN...AND... ...

AND ,LIKE , IS , NULLcount, sum, avg, ...

Apprendre le langage SQL pour interroger une base de données⇒ Comprendre à quoi correspondent les différentes clause

+ RESPECTER LA SYNTAXE ! ! !


10/83


Une requête SQL

SELECT a.nom, a.prenom, count(DISTINCT ja.idf) AS nbFROM Artiste a JOIN JoueDans ja ON a.ida = ja.ida,Artiste b JOIN JoueDans jb ON b.ida = jb.idaWHERE a.nation = b.nationAND a.idf IN (SELECT f.idf

FROM FilmWHERE budget > 15)

AND NOT EXISTS (SELECT *FROM joueDans jWHERE j.ida <> jb.idaAND j.nom LIKE ’%Miller%’)

GROUP BY a.ida, a.nom, a.prenomHAVING nb > 1ORDER BY a.nom, a.prenom DESC ;


11/83


Des clauses et des opérations

Dans une requête SQL de base, il est possible d’exprimer dans :une clause SELECT

une projection pour spécifier les attributs qui constitueront lerésultat de la requête.un renommage pour afficher des attributs avec un nomdifférent dans le résultat de la requête.

une clause WHEREune sélection pour sélectionner les tuples qui seront retournés.

une clause ORDER BYun ordre sur l’affichage des tuples retournés.

Dans une requête SQL, il est toujours nécessaire d’exprimer l’originedes attributs manipulés dans la requête, i.e., les tables utiles.⇒ Clause FROMNB : les autres clauses seront vues plus tard car elles impliquent des requêtes SQL plus avancées.


12/83


Expression de la Projection

SELECT att1, att2, . . .FROM nomTable ;

Dans la clause SELECT est spécifiée la liste des attributs quiseront retournés dans le résultat. Chaque nom d’attribut estséparé par une virgule (,).Dans la clause FROM est spécifiée la table d’où les attributsproviennent.La requête se termine par un point-virgule ( ;)

Remarque importante !Une requête SQL sans SELECT ou sans FROM n’est pas correcte.


13/83


Exemple 1 de Projection

Donner le nom et le prénom des artistes

SELECT nom, prenom FROM Artiste ;


⇒

Résultat :prenom nomJean DUJARDIN

Scarlett JOHANSSONTom CRUISE

Bérénice BEJODany BOONEmily BLUNTDoug LIMANLuc BESSON

Michel HAZANAVICIUS


14/83


Un raccourci syntaxique pour dire ’tous les attributs’ : *

Dans le cas où l’on souhaite spécifier tous les attributs dans laclause SELECT , il est possible d’utiliser l’étoile (*).L’étoile est équivalente à la liste de tous les attributs provenant destables listées dans la clause FROM .

Donner les artistes

SELECT ida, nom, prenom, nation FROM Artiste ;⇐⇒ SELECT * FROM Artiste ;

Résultat :ida prenom nom nation1 Jean DUJARDIN FR2 Scarlett JOHANSSON USA3 Tom CRUISE USA4 Bérénice BEJO FR-AR5 Dany BOON FR6 Emily BLUNT UK7 Doug LIMAN USA8 Luc BESSON FR9 Michel HAZANAVICIUS FR


15/83


Exemple 2 de Projection

Donner la nationalité des artistes

SELECT nation FROM Artiste ;


⇒

Résultat :nationFR

USAUSA

FR-ARFRUKUSAFRFR


16/83


Remarque sur l’exemple 2 de Projection

Le résultat précédent n’est pas satisfaisant, car les nationalitésprésentent dans la base apparaissent autant de fois qu’il y a un/uneartiste de cette nationalité.

Pour répondre correctement à cette requête il est nécessaire desupprimer les doublons.


17/83


Suppression des doublons : DISTINCT

Le mot clé DISTINCT permet de supprimer les tuples en doublon.Il se place au début de la clause SELECT :

SELECT DISTINCT att1, att2, . . .FROM nomTable ;

Retour à l’exemple :

Donner la nationalité des artistes

SELECT DISTINCT nation FROM Artiste ;Artiste(ida, nom, prenom, nation)

ida prenom nom nation1 Jean DUJARDIN FR2 Scarlett JOHANSSON USA3 Tom CRUISE USA4 Bérénice BEJO FR-AR5 Dany BOON FR6 Emily BLUNT UK7 Doug LIMAN USA8 Luc BESSON FR9 Michel HAZANAVICIUS FR

⇒

Résultat :nationFR

USAFR-AR

UK


18/83


Renommage d’attribut dans le résultat : AS

Le mot-clé AS permet d’associer lors de l’affichage, un nomd’attribut différent du nom utilisé dans la table :

SELECT att1 AS att ′1, att2 AS att ′2, att3, . . .FROM nomTable ;

L’attribut att1 sera renommé en att ′1, et l’attribut att2 serarenommé en att ′2

Remarque :Le AS ne permet qu’un renommage de l’attribut uniquement dansl’affichage du résultat. En aucun cas, la table n’est modifiée !


19/83


Exemple de Renommage

Donner l’identifiant, le nom et la nationalité des artistes. Le nom del’artiste sera renommé "nomArtiste" et la nationalité sera renommé"pays".

SELECT ida, nom AS nomArtiste, nation AS paysFROM Artiste ;


⇒

Résultat :ida nomArtiste pays1 DUJARDIN FR2 JOHANSSON USA3 CRUISE USA4 BEJO FR-AR5 BOON FR6 BLUNT UK7 LIMAN USA8 BESSON FR9 HAZANAVICIUS FR


20/83


Expression de la sélection

SELECT att1, att2, . . .FROM nomTableWHERE condition ;

La clause WHERE permet d’exprimer une condition quicaractérisent les tuples qui seront retournés par la requête.La condition exprimée dans le WHERE est une expressionlogique qui retourne vrai ou faux. L’expression est appliqué àchaque tuple et seuls sont gardés dans le résultat les tuplesrendant l’expression vraie.

RemarqueLa clause WHERE exprime des contraintes au niveau des tuples,i.e., des contraintes qui seront testées sur chaque tuple


21/83


La condition dans le WHERE

L’expression exprimant une condition à vérifier peut contenir :une comparaison entre un attribut et une constanteune comparaison entre deux attributsune conjonction de comparaisons (comp1 AND comp2)une disjonction de comparaisons (comp1 OR comp2)des combinaisons de conjonction et/ou de disjonctiondes fonctions, des expressions plus complexes (plus tard)

Pour les comparaisons, nous avons =, ! =, <, <=, >, >=pour respectivemebnt l’égalité, le différent, le strictementinférieur, l’inférieur, le strictement supérieur et le supérieur.

Pour les constantes, nous avons différents types de donnéesutilisés pour les constantes : nombres (1, 1000, 1.5,...), deschaînes de caractères (’PIM’, ’PAM’, ’POUM’...) et des dates(’2017-01-23’) avec un formatage des dates qui peut varierselon le SGBD.


22/83


Exemple 1 de sélection

Donner les artistes américain(e)s

SELECT *FROM ArtisteWHERE nation = ’USA’ ;


⇒Résultat :

ida prenom nom nation2 Scarlett JOHANSSON USA3 Tom CRUISE USA7 Doug LIMAN USA


23/83



Donner le titre et le budget des films sortis depuis 2010 et ayant unbudget strictement supérieur à 12

SELECT titre, budgetFROM FilmWHERE annee >= 2010 AND budget > 12 ;


⇒Résultat :

titre budgetEdge of tomorrow 178

Lucy 40


24/83



Donner les film sortis entre 2011 et 2014 ou ceux dont le budgetest inférieur à 12

SELECT *FROM FilmWHERE (annee >= 2011 AND annee <= 2014)OR budget < 12 ;


⇒

Résultat :idf titre annee genre real budget1 Edge of tomorrow 2014 SF 7 1782 Lucy 2014 SF 8 403 The Artist 2011 Drame 9 95 Bienvenue chez les Ch’tis 2008 Comédie 5 11


25/83


Condition sur un intervalle : BETWEEN...AND...

Dans une clause WHERE , il est possible de contraindre unattribut à un intervalle de valeur en utilisant le raccourci syntaxiqueBETWEEN...AND... .

SELECT att1 , att2, . . .FROM nomTableWHERE att1 BETWEEN bmin AND bmax ;

bmin représente la valeur minimale pouvant être prise parl’attribut att1bmax représente la valeur maximale pouvant être prise parl’attribut att1


26/83


Condition sur un intervalle : BETWEEN...AND... (2)

SELECT att1 , att2, . . .FROM nomTableWHERE att1 BETWEEN bmin AND bmax ;

Remarque :

La notion d’intervalle s’applique non seulement aux nombres,mais aussi aux chaînes de caractères (application de l’ordrelexicographique) et aux dates.il est important de noter que le AND est associé auBETWEEN...AND... . Il ne faut pas le confondre avec unAND d’une conjonction de comparaisons.


27/83


Retour sur l’exemple 3 de sélection

Donner les film sortis entre 2011 et 2014 ou ceux dont le budgetest inférieur à 12

SELECT *FROM FilmWHERE annee BETWEEN 2011 AND 2014OR budget < 12 ;


⇒



28/83


Condition sur un ensemble de valeurs : IN

Dans une clause WHERE , il est possible de contraindre unattribut à un ensemble discret de valeurs en utilisant le raccourcisyntaxique IN .

SELECT att1 , att2, . . .FROM nomTableWHERE att1 IN ( val1, val2,... ) ;

vali représentent les valeurs possibles pour l’attribut att1


29/83



Donner les artistes de nationalité française ou franco-argentine

SELECT *FROM ArtisteWHERE nation IN (’FR’ , ’FR-AR’) ;


⇒

Résultat :ida prenom nom nation1 Jean DUJARDIN FR4 Bérénice BEJO FR-AR5 Dany BOON FR8 Luc BESSON FR9 Michel HAZANAVICIUS FR


30/83


Exemple 4’ de sélection

Requête équivalente sans le IN :

Donner les artistes de nationalité française ou franco-argentine

SELECT *FROM ArtisteWHERE nation = ’FR’ OR nation = ’FR-AR’ ;


⇒



31/83


Condition portant sur un motif : LIKE ...

Dans une clause WHERE , il est possible de contraindre unattribut non pas sur une valeur exacte mais sur un motif.Un motif est une valeur dont on ne connaît pas exactement tous lescaractères pour les chaînes de caractères ou tous les chiffres pourune valeur numérique.

Un motif s’exprime grâce à des caractères joker :le _ permet d’exprimer un seul caractère quelconquele % permet d’exprimer plusieurs caractères quelconques

Ainsi, pour le motif "P_M", les valeurs ’PIM’ et ’PAM’ répondentau motif, mais ce n’est pas le cas pour la valeur ’POUM’.Pour que cela soit le cas, il faudrait considérer le motif ’P%M’.


32/83


Condition portant sur un motif : LIKE ... (2)

Dans une clause WHERE , il est possible de contraindre unattribut sur un motif en utilisant l’opérateur LIKE et les caractèresjoker _ et %.

SELECT att1 , att2, . . .FROM nomTableWHERE att1 LIKE "mot1" ;

mot1 représente un motif contenant au moins un caractèrejoker et qui permettra de filtrer les valeurs possibles pourl’attribut att1


33/83



Donner les artistes de nationalité française

SELECT *FROM ArtisteWHERE nation LIKE "%FR%" ;


⇒



34/83



Donner les artistes dont le nom contient un "O" comme secondelettre

SELECT *FROM ArtisteWHERE nom LIKE "_O%" ;


⇒Résultat :

ida prenom nom nation2 Scarlett JOHANSSON USA5 Dany BOON FR


35/83


Condition portant sur l’absence de valeur : IS NULL

Dans une clause WHERE , il est possible de contraindre unattribut selon qu’il n’est pas de valeur.

Une valeur non définie est représentée par le mot-clé NULL .

Il est possible de filtrer un tuple qui n’a pas de valeur grâce àl’opérateur IS couplé au mot-clé NULL .

SELECT att1 , att2, . . .FROM nomTableWHERE att1 IS NULL ;

cette requête ne retournera que les tuples pour lesquelsl’attribut att1 n’a pas de valeur.


36/83



Donner les films dont on ne connait pas le genre

SELECT *FROM FilmWHERE genre IS NULL ;


⇒Résultat :

idf titre annee genre real budget4 OSS 117 : Le Caire, Nid d’espions 2006 NULL 9 14


37/83


Condition portant sur l’existence de valeurs : IS NOTNULL

A l’inverse, il est possible, dans une clause WHERE , decontraindre un attribut pour s’assurer qu’il a bien une valeur.Il suffit dans ce cas d’ajouter NOT , entre l’opérateur IS et lemot-clé NULL .

SELECT att1 , att2, . . .FROM nomTableWHERE att1 IS NOT NULL ;

cette requête ne retournera tous les tuples pour lesquelsl’attribut att1 a une valeur.


38/83



Donner les films dont on ne connaît pas le genre

SELECT *FROM FilmWHERE genre IS NOT NULL ;


⇒



39/83


Premier bilan sur la clause WHERE

Nous avons vu, que la clause WHERE permet d’exprimer desconditions qui permettent de filtrer des tuples, en fonction :

d’une valeur constanted’une combinaison (conjonction/disjonction) de valeursconstantesd’un ensemble de valeurs bornées ou définiesd’un motifde l’absence ou non de valeur


40/83


DISTINCT , NULL ...

Nous avons vu précédemment qu’il était possible :de permettre ou non la présence de doublons dans le résultatd’une requête

DISTINCT dans la clause SELECTde permettre à un attribut de ne pas avoir de valeur

symbole NULLavec possibilité de filtre grâce à l’opérateur IS (éventuellementcombiné avec la négation NOT )

Intéressons nous maintenant à la possibilité de trier le résultatd’une requête.


41/83


Expression du tri : ORDER BY ...

SELECT att1, att2, . . .FROM nomTableWHERE conditionORDER BY att1, att2, . . . ;

La clause ORDER BY permet d’exprimer l’ordre d’apparitiondes tuples dans le résultat de la requête.La clause ORDER BY se place toujours en fin de requête.La clause ORDER BY est suivie d’une liste d’attributsséparés par une virgule (,). Le résultat de la requête est triépar défaut selon l’ordre naturel croissant du premier attribut dela liste (att1). En cas d’égalité des valeurs sur le premierattribut, le deuxième attribut (att2) est considéré afin de trierles tuples concernés selon l’ordre naturel croissant de cedernier. Et ainsi de suite avec les autres attributs de la liste.


42/83


Expression du tri : ORDER BY ... (2)

SELECT att1, att2, . . .FROM nomTableWHERE conditionORDER BY att1, att2 DESC, . . . ;

Dans la clause ORDER BY , il est possible de faire suivre lenom d’un attribut par ASC ou DESC pour indiquer un ordrecroissant ou décroissant.Il est à noter que l’ordre par défaut est l’ordre croissant. Parconséquence, le mot-clé ASC sera très peu utilisé.Dans l’exemple, avec l’ajout de DESC après l’attribut att2, lerésultat de la requête est trié selon l’ordre naturel croissant del’attribut att1 et pour les valeurs commune de att1, les tuplessont triés selon l’ordre naturel décroissant de l’attribut (att2).


43/83


Expression du tri : ORDER BY ... (3)

La relation d’ordre s’applique naturellement sur les valeursnumériques mais aussi sur les chaînes de caractères et les dates.

Ordre pour les chaînes de caractèresL’ordre naturel croissant pour les chaînes de caractères estl’ordre lexicographique (ou ordre alphabétique) : A, B, C,...L’ordre naturel décroissant est l’ordre lexicographique inverse :Z, Y, X, ...

Ordre pour les datesL’ordre naturel croissant pour les dates est l’ordre l’ordrechronologique : 23-01-2017, 27-01-2017,...L’ordre naturel décroissant est l’ordre antéchronologique :27-01-2017, 23-01-2017,...


44/83


Exemple 1 de tri

Donner les films de budget supérieur strictement à 10. Le résultatsera trié par année croissante puis par budget décroissant.

SELECT *FROM FilmWHERE budget > 10ORDER BY annee, budget DESC ;


⇒

Résultat :idf titre annee genre real budget4 OSS 117 : Le Caire, Nid d’espions 2006 NULL 9 145 Bienvenue chez les Ch’tis 2008 Comédie 5 111 Edge of tomorrow 2014 SF 7 1782 Lucy 2014 SF 8 40


45/83


Exemple 2 de tri

Donner le nom et la nationalité des artistes dont le prénom contient3 ou 4 lettres. Le résultat sera trié par nationalité croissante puispar nom croissant.

SELECT nom, nationFROM ArtisteWHERE prenom LIKE "___" OR prenom LIKE "____"ORDER BY nation, nom ;


⇒

Résultat :nom nation

BESSON FRBOON FR

DUJARDIN FRCRUISE USALIMAN USA


46/83


Bilan sur la clause ORDER BY

Nous avons vu, que la clause ORDER BY permet de spécifier lesattributs sur lesquels s’appliqueront un tri pour afficher le résultat.Il est possible d’exprimer

un ordre sur une valeur numérique, une chaîne de caractères etune dateque le tri sera croissant (par défaut) ou décroissant (enspécifiant DESC après l’attribut)


47/83


Une requête SQL

Les mot-clés que nous avons déjà vu !

SELECT a.nom, a.prenom, count(DISTINCT ja.idf) AS nbFROM Artiste a JOIN JoueDans ja ON a.ida = ja.ida,Artiste b JOIN JoueDans jb ON b.ida = jb.idaWHERE a.nation = b.nationAND a.idf IN (SELECT f.idf

FROM FilmWHERE budget > 15)

AND NOT EXISTS (SELECT *FROM joueDans jWHERE j.ida <> jb.idaAND j.nom LIKE ’%Miller%’)

GROUP BY a.ida, a.nom, a.prenomHAVING nb > 1ORDER BY a.nom, a.prenom DESC ;


48/83


Requêtes multi-sources

Il est possible d’exprimer dans la clause FROM une liste de tablesséparées par une virgule (,).

SELECT att1, att2, att3, . . .FROM nomTable1, nomTable2,... ;

Dans la clause SELECT , on spécifie la liste des attributs quiseront retournés dans le résultat. Chaque nom d’attribut estséparé par une virgule (,).Dans la clause FROM est spécifiée la liste des tables d’où lesattributs proviennent. Chaque nom de table est séparé par unevirgule (,).La requête se termine par un point-virgule ( ;)


49/83


Requêtes multi-sources (2)

Afin de spécifier de quelle table vient quel attribut, il est possible defaire précéder le nom de l’attribut par le nom de la table (avec unpoint entre les deux).

SELECT nomTable1.att1, nomTable1.att2,nomTable2.att3, . . .

FROM nomTable1, nomTable2,... ;


50/83


Définition des alias pour les tables

La notation avec les noms de tables préfixant les attributs peuts’avérer fastidieuse.Dans la clause FROM , il est possible d’associer à chaque instanceun alias qui identifiera chaque instance de manière unique.

SELECT A.att1, A.att2, B.att3, . . .FROM nomTable1 A, nomTable2 B,... ;

Dans la clause FROM l’alias est séparé du nom de la table parun espace ( ).Dans la clause SELECT , on préfixe les attributs par l’aliasIl est d’usage d’associer un alias plus court que le nom de latable.


51/83


Expression du produit cartésien

SELECT *FROM nomTable1, nomTable2 ;

Le produit cartésien permet d’associer à chaque tuple de lapremière table nomTable1 chaque tuple de la tablenomTable2.L’utilisation de * dans la clause SELECT fait que le résultatest composé de tous les attributs composant les deux tables.Les tables nomTable1 et nomTable2 peuvent être les mêmes(i.e.,deux instances de la même table)


52/83


Exemple 1 de Produit cartésien

Donner le produit cartésiend’artiste avec pays

SELECT *FROM Artiste, Pays ;


ida prenom nom nation code nom1 Jean DUJARDIN FR FR France1 Jean DUJARDIN FR USA Etats-Unis1 Jean DUJARDIN FR UK Royaume-Uni1 Jean DUJARDIN FR AR Argentine2 Scarlett JOHANSSON USA FR France2 Scarlett JOHANSSON USA USA Etats-Unis2 Scarlett JOHANSSON USA UK Royaume-Uni2 Scarlett JOHANSSON USA AR Argentine3 Tom CRUISE USA FR France3 Tom CRUISE USA USA Etats-Unis3 Tom CRUISE USA UK Royaume-Uni3 Tom CRUISE USA AR Argentine4 Bérénice BEJO FR-AR FR France... ... ... ... ... ...



⇐


52/83









⇐


52/83









⇐


53/83


Plusieurs instances... donc risque d’ambiguïté !

Question : Que retourne la requête suivante ?

SELECT nomFROM Artiste, Pays ;

Est-ce le nom des artistes ?Est-ce le nom des pays ?Est-ce les deux ?En fait, il y a ambiguïté ! ce qui se traduit par une erreur !La requête est fausse !


53/83



Question : Que retourne la requête suivante ?

SELECT nomFROM Artiste, Pays ;

Est-ce le nom des artistes ?Est-ce le nom des pays ?Est-ce les deux ?En fait, il y a ambiguïté ! ce qui se traduit par une erreur !La requête est fausse !


54/83



Dans ce cas, il est indispensable de préciser la provenance del’attribut nom.

Donner tous les couples nom d’artiste, nom de pays possibles.SELECT A.nom AS nomArtiste, P.nom AS nomPaysFROM Artiste A, Pays P ;




Résultat :nomArtiste nomPaysDUJARDIN FranceDUJARDIN Etats-UnisDUJARDIN Royaume-UniDUJARDIN Argentine

JOHANSSON FranceJOHANSSON Etats-UnisJOHANSSON Royaume-UniJOHANSSON Argentine

CRUISE FranceCRUISE Etats-UnisCRUISE Royaume-UniCRUISE ArgentineBEJO France

... ...


55/83


Un raccourci syntaxique pour dire "tous les attributs d’unedes tables"

Il est possible de préfixer l’étoile (*) par le nom/alias de la table.

Donner chaque artiste autant de fois qu’il y a de pays

SELECT A.* FROM Artiste A, Pays P ;Artiste(ida, nom, prenom, nation)

ida prenom nom nation1 Jean DUJARDIN FR2 Scarlett JOHANSSON USA3 Tom CRUISE USA4 Bérénice BEJO FR-AR5 Dany BOON FR6 Emily BLUNT UK7 Doug LIMAN USA8 Luc BESSON FR9 Michel HAZANAVICIUS FR



Résultat :ida prenom nom nation1 Jean DUJARDIN FR1 Jean DUJARDIN FR1 Jean DUJARDIN FR1 Jean DUJARDIN FR2 Scarlett JOHANSSON USA2 Scarlett JOHANSSON USA2 Scarlett JOHANSSON USA2 Scarlett JOHANSSON USA3 Tom CRUISE USA3 Tom CRUISE USA3 Tom CRUISE USA3 Tom CRUISE USA4 Bérénice BEJO FR-AR... ... ... ...


56/83

Plan

1 Projection, sélection et tri

2 Produit-cartésien et jointures

3 Les jointures

57/83


Retour sur le Produit Cartésien




Résultat :ida prenom nom nation code nom1 Jean DUJARDIN FR FR France1 Jean DUJARDIN FR USA Etats-Unis1 Jean DUJARDIN FR UK Royaume-Uni1 Jean DUJARDIN FR AR Argentine2 Scarlett JOHANSSON USA FR France2 Scarlett JOHANSSON USA USA Etats-Unis2 Scarlett JOHANSSON USA UK Royaume-Uni2 Scarlett JOHANSSON USA AR Argentine3 Tom CRUISE USA FR France3 Tom CRUISE USA USA Etats-Unis3 Tom CRUISE USA UK Royaume-Uni3 Tom CRUISE USA AR Argentine4 Bérénice BEJO FR-AR FR France... ... ... ... ... ...



⇐


58/83


Besoin de joindre sur plusieurs tables

Pour répondre à certains besoins, il est nécessaire de mettre enrelation des attributs issus de tables différentes.

Exemple de besoins portant sur plusieurs tablesDonner le nom des pays des artistes dont le nom commencepar un ’B’.Donner le nom et le prénom des artistes ayant joué dans unfilm réalisé par ’Doug LIMAN’.Donner le nom des artistes américain(e)s ayant joué pour unréalisateur français.Donner les films ayant plus d’un quart de son budget consacréau salaire d’un(e) artiste....

Pour répondre à ces besoins, nous allons devoir joindre des tables.


59/83


Différents types de jointures

Il existe différent types de jointures :

Jointure interne (INNER JOIN)θ-jointure et équi-jointureJointure naturelleSemi-jointure

Jointure externe (OUTTER JOIN)Jointure gauche et Jointure droiteJointure plein


60/83


Jointure interne ou θ-jointure

Une jointure interne s’exprime dans la clause FROM de la requête.La jointure interne est fréquemment utilisée : seuls les tuples quirespectent la condition de jointure sont conservés.

SELECT att1, att2, . . .FROM nomTable1 [INNER] JOIN nomTable2ON condition_de_jointure[WHERE conditions_de_selection] ;

: Rappel : ce qui est entre crochets ([ ]) est optionnel.

La condition de jointure est introduite par ON .la condition de jointure porte sur un opérateur quelconque (=,! =, >, >=, <, =<, IN , LIKE , . . .) appliqué à deuxattributs ou une combinaison (conjonction/disjonction)d’opérateurs appliquésNB : on parle aussi de theta-jointure.


61/83


Jointure interne : équi-jointure et auto-jointure

En fonction de la spécificité de la condition de jointure, la jointurepeut être qualifiée d’équi-jointure ou d’auto-jointure.

SELECT A.att1, B2.att2, . . .FROM nomTable1 A JOIN

(nomTable2 B1 JOIN nomTable2 B2ON B1.attx < B2.attz)

ON A.attx = B2.attx ;

Types de conditions dans les jointures :la condition de jointure est une égalité ou une conjonctionexclusive d’égalités⇒ la theta-jointure est dite alors équi-jointure

la jointure porte sur une table avec elle-même⇒ la theta-jointure est dite alors auto-jointure


62/83


Exemple 1 de jointure interne (équi-jointure)

Donner le nom des personnages du film ’The Artist’SELECT JD.nomFROM Film F JOIN JoueDans JD ON F .idF = JD.idFWHERE Film.titre = ’The Artist’ ;



⇒ Résultat :nom

George ValentinPeppy Miller


63/83


Exemple 2 de jointure interne (Auto-jointure)

Donner les couples de titres de film de même genreSELECT F1.titre AS Film1, F2.titre AS Film2FROM Film F1 JOIN Film F2 ON F1.genre = F2.genre ;


⇒

Résultat :Film1 Film2

Edge of tomorrow Edge of tomorrowEdge of tomorrow Lucy

Lucy LucyLucy Edge of tomorrow


64/83


Exemple 3 de jointure interne (θ-jointure)

Donner le titre des films dont le budget est inférieur ou égal ausalaire du personnage ’Lucy Miller’

SELECT F .titreFROM Film F JOIN JoueDans JD ON F .budget <= JD.salWHERE JD.nom = ’Lucy Miller’ ;



⇒ Résultat :titre

The ArtistOSS 117 : Le Caire, Nid d’espions

Bienvenue chez les Ch’tis


65/83


Remarque sur l’exemple 2 de jointure interne (1)

On remarque que le résultat retourné n’est pas satisfaisant, du faitdes doublons.

Intuitivement, il faut que les deux films d’un tuple résultat soientdifférents.

Dans ce cas la requête devient :

Donner les couples de titres de film de même genreSELECT F1.titre AS Film1, F2.titre AS Film2FROM Film F1 JOIN Film F2 ON F1.genre = F2.genre

AND F1.idf != F2.idf ;

Mais le résultat sera :Résultat :

Film1 Film2Edge of tomorrow Lucy

Lucy Edge of tomorrow


66/83


Remarque sur l’exemple 2 de jointure interne (2)

Pour obtenir le résultat attendu, il est nécessaire de n’avoir que(Edge of tomorrow, Lucy) ou (Lucy, Edge of tomorrow) dans lerésultat, mais pas les deux.L’astuce consiste à ajouter une condition de sélection afin deprendre que le tuple dont l’identifiant Film1 est plus petit quel’identifiant de Film2.La requête devient :

Donner les couples de titres de film de même genreSELECT F1.titre AS Film1, F2.titre AS Film2FROM Film F1 JOIN Film F2 ON F1.genre = F2.genre

AND F1.idf < F2.idf ;

Et le résultat devient :Résultat :

Film1 Film2Edge of tomorrow Lucy


67/83


Jointure interne ’Old-School’

Même si la syntaxe est obsolète, il est intéressant de remarquerqu’une jointure interne peut s’exprimer comme la combinaison d’unproduit cartésien et d’une sélection.Ainsi l’expression d’un JOIN ... ON

SELECT att1, att2, . . .FROM nomTable1 [INNER] JOIN nomTable2ON condition_de_jointure[WHERE conditions_de_selection] ;

peut s’exprimer de la manière suivante :

SELECT att1, att2, . . .FROM nomTable1, nomTable2WHERE condition_de_jointure[AND conditions_de_selection] ;


68/83


Retour sur l’exemple 2 de jointure interne

Donner le nom des personnages du film ’The Artist’SELECT JD.nomFROM Film F JOIN JoueDans JD ON F .idf = JD.idfWHERE F .titre = ’The Artist’ ;

Donner le nom des personnages du film ’The Artist’SELECT JD.nomFROM Film F, JoueDans JDWHERE F .idf = JD.idfAND F .titre = ’The Artist’ ;


69/83


Expression de la jointure naturelle (1)

Nous remarquons que sur la requête suivante, nous avons uneéqui-jointure et que l’égalité porte sur tous les attributs communsaux deux tables (i.e., idF ).

Donner le nom des personnages du film ’The Artist’SELECT JD.nomFROM Film F JOIN JoueDans JD ON F .idf = JD.idfWHERE F .titre = ’The Artist’ ;

Un raccourci syntaxique possible est d’utiliser une jointure naturelle.

Donner le nom des personnages du film ’The Artist’SELECT JD.nomFROM Film F NATURAL JOIN JoueDans JDWHERE F .titre = ’The Artist’ ;


70/83


Expression de la jointure naturelle (2)

La jointure naturelle permet d’exprimer une équi-jointure pourlaquelle la condition de jointure porte sur l’ensemble des attributscommuns aux deux tables jointes.

SELECT att1, att2, . . .FROM nomTable1 A NATURAL JOIN nomTable2 ;

ce qui correspond à :

SELECT att1, att2, . . .FROM nomTable1 A JOIN nomTable2 BON A.attx = B.attx AND A.atty = B.attyAND A.attz = B.attz ;

avec {attx , atty , attz} l’ensemble des attributs communs auxtables nomTable1 et nomTable2.


71/83


Exemple 1 de jointure naturelle

Pour chaque nom de personnage, donner le titre du film dans lequelledit personnage apparaît

SELECT JD.nom, F .titreFROM JoueDans JD NATURAL JOIN Film F ;



⇒Résultat :

nom titreGeorge Valentin The Artist

Hubert Bonisseur de La Bath OSS 117 : ...Lucy Miller LucyBill Cage Edge of tomorrow

Larmina El Akmar Betouche OSS 117 : ...Peppy Miller The Artist

Antoine Bailleul Bienvenue chez ...Rita Vrataski Edge of tomorro


72/83


Question à propos de la jointure naturelle

Que retourne la requête suivante ?

? ? ?SELECT A.nom, JD.salFROM JoueDans JD NATURAL JOIN Artiste A ;




73/83


Réponse

Donner le nom des artistes qui ont joué dans un film un personnagedont le prénom et le nom correspond au nom dudit artiste

SELECT A.nom, JD.salFROM JoueDans JD NATURAL JOIN Artiste A ;

La requête retournera :nom sal

car elle est équivalente à :

SELECT A.nom, JD.salFROM JoueDans JD JOIN Artiste AON JD.idA = A.idA AND JD.nom = A.nom ;


74/83


Expression de la semi-jointure

Une requête dans laquelle on effectue une jointure et que le résultatretourné ne porte que sur les attributs d’une des deux tablesexprime une semi-jointure :

SELECT nomTable2.*FROM nomTable1 JOIN nomTable2ON nomTable1.attx = nomTable2.attx ;


75/83


Exemple de semi-jointure

Donner les artistes qui n’ont pas de multi-nationalité

SELECT A.*FROM Artiste A JOIN Pays PON A.nation = P .code ;




⇒

Résultat :ida prenom nom nation1 Jean DUJARDIN FR2 Scarlett JOHANSSON USA3 Tom CRUISE USA5 Dany BOON FR6 Emily BLUNT UK7 Doug LIMAN USA8 Luc BESSON FR9 Michel HAZANAVICIUS FR


76/83


Remarque

Donner pour chaque artiste son prénom et son nom et le nom deson pays d’origine

SELECT A.prenom,A.nom, P .nom AS paysFROM Artiste A JOIN Pays PON A.nation = P .code ;


⇒

Résultat :prenom nom paysJean DUJARDIN France

Scarlett JOHANSSON Etats-UnisTom CRUISE Etats-UnisDany BOON FranceEmily BLUNT Royaume-uniDoug LIMAN Etats-UnisLuc BESSON Fance

Michel HAZANAVICIUS France



Nous n’avons plus "chaque artiste" !Il faudra s’assurer de garder chaque artiste et si son paysd’origine est dans la table Pays alors, on associe le nomdu pays, sinon l’attribut reste sans valeur.


77/83


Expression d’une jointure externe : OUTER JOIN

Une requête avec jointure externe (OUTER JOIN) affiche unsur-ensemble des tuples qui vérifient la condition de la jointure.Les tuples qui vérifient la condition de jointure, ont des valeursissues des deux tables jointes. Les autres tuples, qui ne vérifient pasla condition de jointure, ont les attributs issus d’une des deuxtables sans valeur (i.e., à NULL ).il existe trois types de jointure externes :

la jointure gauche (LEFT JOIN )la jointure droite (RIGHT JOIN )la jointure pleine (FULL JOIN )

SELECT *FROM nomTable1 <LEFT|RIGHT|FULL> [OUTER] JOIN

nomTable2[ WHERE conditions_de_selection ] ;


78/83


Différences entre LEFT JOIN , RIGHT JOIN et FULLJOIN

LEFT JOIN : les tuples de la table de gauche sanscorrespondance dans l’autre table sont incluses dans le résultatavec une valeur NULL pour les attributs de l’autre table

RIGHT JOIN : les tuples de la table de droite sanscorrespondance dans l’autre table sont incluses dans le résultatavec une valeur NULL pour les attributs de l’autre table

FULL JOIN : toutes les lignes de chacune des tables sontretournées. Les lignes sans correspondance ont leurs attributscomplétés par la valeur NULL .


79/83


Exemple de jointure externe gauche

Donner pour chaque artiste son prénom et son nom et le nom deson pays d’origine

SELECT A.prenom,A.nom, P .nom AS paysFROM Artiste A LEFT JOIN Pays PON A.nation = P .code ;


⇒


Scarlett JOHANSSON Etats-UnisTom CRUISE Etats-Unis

Bérénice BEJO NULLDany BOON FranceEmily BLUNT Royaume-uniDoug LIMAN Etats-UnisLuc BESSON France

Michel HAZANAVICIUS FrancePays(code, nom)

code nomFR France


Nous avons bien "chaque artiste" !


80/83


Exemple de jointure externe droite

Donner pour chaque pays son nom, ainsi que le prénom et le nomdes artistes originaires dudit pays.

SELECT P .nom AS pays, A.prenom,A.nomFROM Artiste A RIGHT JOIN Pays PON A.nation = P .code ;


⇒

Résultat :pays prenom nom

France Jean DUJARDINFrance Dany BOONFrance Luc BESSONFrance Michel HAZANAVICIUS

Etats-Unis Scarlett JOHANSSONEtats-Unis Tom CRUISEEtats-Unis Doug LIMAN

Royaume-uni Emily BLUNTArgentine NULL NULL



Nous avons bien "chaque pays" !


81/83


Exemple précédent exprimé par une jointure gauche

Donner pour chaque pays son nom, ainsi que le prénom et le nomdes artistes originaires dudit pays.

SELECT P .nom AS pays, A.prenom,A.nomFROM Pays P LEFT JOIN Artiste AON A.nation = P .code ;


⇒

Résultat :pays prenom nom

France Jean DUJARDINFrance Dany BOONFrance Luc BESSONFrance Michel HAZANAVICIUS

Etats-Unis Scarlett JOHANSSONEtats-Unis Tom CRUISEEtats-Unis Doug LIMAN

Royaume-uni Emily BLUNTArgentine NULL NULL




82/83


Exemple de jointure externe pleine

Donner pour chaque artiste son prénom et son nom et son paysd’origine, sans perdre de pays.

SELECT A.prenom,A.nom, P .nom AS paysFROM Artiste A FULL JOIN Pays PON A.nation = P .code ;


⇒


Scarlett JOHANSSON Etats-UnisTom CRUISE Etats-Unis

Bérénice BEJO NULLDany BOON FranceEmily BLUNT Royaume-uniDoug LIMAN Etats-UnisLuc BESSON France

Michel HAZANAVICIUS FranceNULL NULL Argentine



Nous avons bien "chaque artiste" et tous les pays appa-raissent dans la table !


83/83


Bilan sur les jointures

Jointure interne : JOIN ... ONC’est la jointure la plus courante qui retourne uniquement lestuples qui respectent la condition de jointure.Jointure naturelle : NATURAL JOINC’est un raccourci syntaxique qui permet d’exprimer uneéqui-jointure sur tous des attributs communs aux deux tablesjointes.Jointure externe : LEFT JOIN , RIGHT JOIN et FULLJOINCe sont des jointures qui retournent non seulement les tuplesqui respectent la condition de jointure, mais aussi des tuplesissus d’une des deux tables (ou des deux) complétés par desattributs sans valeurs issues de l’autre table.


Documents

BDW1 : Bases de données et Programmation Web SQL : … · 5 Bienvenue chez les Ch’tis 2008 Comédie 5 11 ... les autres clauses seront vues plus tard car elles impliquent des requêtes