Remerciez-le!

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

Jointures avancées Paternité - Partage des Conditions Initiales à l'Identique :

Jointures avancées Paternité - Partage des Conditions Initiales à l'Identique : http://creativecommons.org/licenses/by-sa/3.0/fr/ Table des matières I - Contexte 3 II - Jointure : rappel 4 III - Exercice : Appliquer la notion 6 IV - Produit cartésien 7 V - Exercice : Appliquer la notion 9 VI - Jointures multi-tables 10 VII - Exercice : Appliquer la notion 13 VIII - Jointure externe 15 IX - Exercice : Appliquer la notion 19 X - Essentiel 21 XI - Quiz 22 Index 25 Crédits des ressources 26 2 Stéphane Crozat Contexte I Durée : 2h Environnement de travail : DB Fiddle Pré-requis : Aucun [cf. 9ICIQUXL] Lorsque des informations sont présentes dans deux tables diﬀérentes, on peut les combiner à l'aide de jointures. Un exemple typique serait de récupérer l'ensemble des commandes passées par un client en particulier. Mais les jointures sont plus puissantes : elles permettent par exemple de combiner les informations issues d'un nombre quelconque de tables. Elles permettent aussi de répondre à des questions complexes, comme pour savoir quels sont les clients qui n'ont jamais passé de commandes. Tout au long de ces modules, vous découvrirez la puissance des jointures, ainsi que des cas d'utilisation plus spécifiques. Stéphane Crozat 3 Jointure : rappel II [cf. M4BTWG5x] Objectif Savoir réaliser une jointure d'une table avec elle-même. Mise en situation Une base de données relationnelle est en général composée de plusieurs tables liées par des clés étrangères. L'opération de jointure permet de combiner les données de plusieurs tables grâce aux clés étrangères. Mais l'opération de jointure peut aussi être utilisée pour joindre une table avec elle même, par exemple pour gérer les relations hiérarchiques au sein d'un système de commentaire. Chaque commentaire qui est une réponse à un autre commentaire référence son commentaire « parent ». Une jointure permettra de retrouver le commentaire parent de chaque commentaire enfant. On appelle auto-jointure une telle jointure, et vous allez découvrir comment les réaliser dans ce module. Jointure Rappel Une jointure est une opération permettant de consulter les données de plusieurs tables en se basant sur les valeurs identiques de certains des attributs de ces tables, en général une clé étrangère et une clé primaire. 1 SELECT * 2 FROM R1 3 INNER JOIN R2 O <condition> Exemple Pays code : ISO3166 {key} nom : text {key} Forêt nom : text surface : integer contient * * 1 Pays(#code:char(2), nom:text) avec nom clé 2 Foret (#id:integer, nom:text, surface:integer) 3 Contient (#pays=>Pays, #foret=>Foret) 1 CREATE TABLE pays ( 2 code CHAR(2) PRIMARY KEY, 3 nom VARCHAR(255) UNIQUE NOT NULL 4 ); 5 6 CREATE TABLE foret ( 7 id INTEGER PRIMARY KEY, 8 nom VARCHAR(255) NOT NULL, 4 Stéphane Crozat 9 surface INTEGER NOT NULL 10 ); 11 12 CREATE TABLE contient ( 13 pays CHAR(2) REFERENCES pays(code), 14 foret INTEGER REFERENCES foret(id), 15 PRIMARY KEY (foret, pays) 16 ); 1 INSERT INTO pays VALUES ('FR', 'France'); 2 INSERT INTO pays VALUES ('ES', 'Espagne'); 3 INSERT INTO foret VALUES (1, 'Broceliande', 12000); 4 INSERT INTO foret VALUES (2, 'Pyrénéenne', 23000); 5 INSERT INTO foret VALUES (3, 'Compiégnoise', 14000); 6 INSERT INTO contient VALUES ('FR',1); 7 INSERT INTO contient VALUES ('FR',2); 8 INSERT INTO contient VALUES ('FR',3); 9 INSERT INTO contient VALUES ('ES',2); 1 SELECT p.code, f.nom 2 FROM pays p JOIN contient c 3 ON p.code = c.pays 4 JOIN foret f 5 ON c.foret = f.id 1 code | nom 2 ------+-------------- 3 FR | Broceliande 4 FR | Pyrénéenne 5 FR | Compiégnoise 6 ES | Pyrénéenne Auto-jointure Complément Une auto-jointure est la jointure d'une table avec elle-même. Pour réaliser une auto-jointure, on doit utiliser les alias des tables. Pour donner un alias à une table, on note dans la clause FROM l'alias après le nom de la relation : FROM nom_table alias. Auto-jointure Exemple 1 SELECT e1.nom 2 FROM employe e1 INNER JOIN employe e2 3 ON e1.nom=e2.nom [cf. nR8bJ1pF] Jointure : rappel Stéphane Crozat 5 Exercice : Appliquer la notion III On implémente ce modèle et peuple les tables avec les requêtes suivantes : 1 CREATE TABLE assurance ( 2 num_siret INTEGER PRIMARY KEY, 3 denomination VARCHAR(50), 4 adresse_siege_social VARCHAR(200) 5 ); 6 7 CREATE TABLE locataire ( 8 id INTEGER PRIMARY KEY, 9 prenom VARCHAR(50), 10 nom VARCHAR(50), 11 assurance INTEGER REFERENCES assurance(num_siret)); 12 13 INSERT INTO assurance 14 VALUES (839204929, 'Pro Gama', '13, rue des Peupliers 42920 Saint-Jean-le-Cèdre'); 15 16 INSERT INTO assurance 17 VALUES (839201119, 'Faim', '41, avenue des Tulipes 11001 Maintes-sur-Champs'); 18 19 INSERT INTO assurance 20 VALUES (839283114, 'Xitour', '26, chemins de la mine 61920 Sourcieux'); 21 22 INSERT INTO locataire 23 VALUES(47289, 'Jean', 'Durand', 839283114); 24 25 INSERT INTO locataire 26 VALUES(47211, 'Anne', 'Durand', 839283114); 27 28 INSERT INTO locataire 29 VALUES(47111, 'Camille', 'Dupont', 839201119); 30 31 INSERT INTO locataire 32 VALUES(47291, 'Marie', 'Martin', 839204929); 33 34 Question Réaliser une jointure qui permet d'aﬀicher les prénoms et noms des locataires et la dénomination de leur assurance. 6 Stéphane Crozat Produit cartésien IV [cf. 5e0z92ps] Objectifs Comprendre le concept de produit cartésien. Savoir réaliser un produit cartésien en SQL. Mise en situation Un produit cartésien désigne en général l'ensemble des combinaisons possibles entre deux ensembles. Par exemple, lors d'un tournoi sportif, si toutes les équipes d'un pays doivent rencontrer toutes les équipes d'un autre pays, on réalisera un produit cartésien sur ces deux ensemble de villes. La grille des matchs contiendra ainsi, pour chaque ville du premier pays, une entrée avec chaque ville du second pays. Ce concept est aussi exploité par SQL pour combiner les informations de plusieurs tables, en général dans le but de réaliser des jointures, et vous découvrirez comment dans ce module. Produit cartésien Définition Un produit cartésien de plusieurs tables est l'ensemble de toutes les concaténations ordonnées possibles des enregistrements de ces tables. Syntaxe 1 SELECT * 2 FROM R1, R2, Ri Stéphane Crozat 7 Exemple Exemple de produit (SQL et Algèbre) Explosion combinatoire du produit cartésien Attention On tiendra compte du fait que réaliser des produits cartésiens amène à une explosion combinatoire. En eﬀet, si on dispose de tables chacune contenant de l'ordre de éléments, alors le résultat contiendra de l'ordre de éléments. Usage du produit cartésien Remarque Le produit cartésien est rarement utilisé tel quel ; généralement on le restreint à un sous-ensemble via des restrictions comme dans le cas des jointures. Sémantique des syntaxes de jointure Remarque Nous avons vu qu'il y avait deux types de syntaxe pour les jointures : la syntaxe avec JOIN et la syntaxe avec les clauses FROM et WHERE. Les deux syntaxes donnent des résultats équivalents, mais la sémantique est diﬀérente : la première syntaxe avec JOIN est entièrement dédiée aux jointures et n'est utilisée que pour cela. La seconde syntaxe correspond à une restriction d'un produit cartésien sur des valeurs d'égalité sur certains attributs. [cf. gQaFxCti] Produit cartésien 8 Stéphane Crozat Exercice : Appliquer la notion V On se donne les tables suivantes stockant des informations sur des missions et des agents secrets : 1 CREATE TABLE agentSecret( 2 pseudonyme VARCHAR(20) PRIMARY KEY, 3 nom VARCHAR(20), 4 prenom VARCHAR(20) 5 ); 6 7 CREATE TABLE mission( 8 nom_de_code VARCHAR(20) PRIMARY KEY, 9 lieu_suppose VARCHAR(200) 10 ); 11 12 INSERT INTO agentSecret 13 VALUES ('OSS 117', 'Bonisseur de la Bath', 'Hubert'); 14 15 INSERT INTO agentSecret 16 VALUES ('SCEP 2421', 'El Akmar Betouche', 'Larmina'); 17 18 INSERT INTO agentSecret 19 VALUES ('MOSS_1411', 'Koulechov', 'Dolores'); 20 21 INSERT INTO mission 22 VALUES ('Silence Radio', 'Rio de Janeiro'); 23 24 INSERT INTO mission 25 VALUES ('Oisillons', 'Le Caire'); On souhaite savoir qui pourrait être envoyé en mission. Question Réaliser un produit cartésien qui permet d'aﬀicher les pseudonyme des agents secrets et le nom de code des missions. Stéphane Crozat 9 Jointures multi-tables VI [cf. 4vWUrFcS] Objectif Savoir combiner des informations présentes dans plus de deux tables. Mise en situation Si les jointures sont en général utilisées pour récupérer une information présente dans une seconde table, il y a des situations où une troisième, voire une quatrième table entre en jeu. Imaginez par exemple une base de données musicale : les morceaux sont stockés dans une table, avec leur titre et leur durée, les albums sont stockés dans une seconde table, et les artistes sont stockés dans une troisième table. Pour récupérer le nom d'un artiste à partir d'un morceau, il faut d'abord récupérer l'album, et enfin l'artiste. En d'autres termes, il faut combiner les données de trois tables diﬀérentes. C'est ce qu'on appelle une jointure multi-table. Jointure par la clause JOIN Syntaxe 1 SELECT * 2 FROM a 3 JOIN b ON a.att_1 = b.att_1 4 JOIN c ON b.att_2 = c.att2; Jointure par la clause WHERE Syntaxe 1 SELECT * 2 FROM a, b, c 3 WHERE a.att_1 = b.att_1 4 AND b.att_2 = c.att2; Exemple On se donne la situation suivante de la gestion de parcs se décomposant en zones peuplées d'espèces d'êtres vivants. 10 Stéphane Crozat Parc nom: varchar {unique} siteWeb: varchar {unique} Zone code: varchar {unique frozen} largeur: real longueur: uploads/Geographie/ 4-langage-sql-avec-jointures-avancees.pdf