[MUSIQUE] [MUSIQUE] [MUSIQUE] [MUSIQUE] Bienvenue dans cette leçon sur les requêtes conditionnelles. Ce type de requête permet d'extraire un sous-ensemble d'un groupe d'objets. Par exemple, on peut extraire un ensemble de voitures rouges d'un parking sur lequel sont parqués une masse énorme de véhicules, mais vous ne pouvez pas imaginer! Comme [RIRE] >> Avec même des voitures mobility! >> Avec même des voitures mobility! >> Mobility. Ouais! >> T'as la carte mobility et tu trouves plus ta voiture. Maintenant, quand tu dis j'ai perdu une voiture rouge. >> Là, les voitures, celles qui n'ont pas été avec nous dans le bar, je ne prends plus le train. >> Mais oui, mais formule le en SQM, parce que là, ils ne comprennent rien! >> Nous allons donc aborder dans cette leçon la question des requêtes conditionnelles. Les requêtes qui permettent d'extraire des données sur la base d'un critère attributaire. Dans l'exemple qui nous accompagne tout au long de ce cours, sur les Seychelles, des requêtes qui permettraient, par exemple, d'extraire le sous-ensemble des routes asphaltées. L'objectif de la leçon, est donc d'étudier le principe de la syntaxe d'une requête conditionnelle et de comprendre comment ces requêtes débouchent sur la notion de jointures, qui permettent d'associer plusieurs tables. De sorte qu'au terme de la leçon, vous soyez en mesure d'utiliser, d'écrire des requêtes conditionnelles, de sélectionner, de filtrer des données sur la base d'un critère attributaire, et d'utiliser des jointures deux tables. Nous verrons donc dans cette leçon, d'abord la notion de filtre conditionnel, basée sur l'utilisation de la clause WHERE dans une requête SQL. Puis, les divers opérateurs que l'on peut utiliser dans ces clauses. Ensuite, les jointures qui reposent sur la clause WHERE et pour conclure, les jointures basées sur un autre type de clause, qui est la clause JOIN. [MUSIQUE] Nous avons vu, dans la précédente leçon d'introduction au language SQL, que celui-ci repose sur une syntaxe de base, ui comprend un certain nombre de mots-clés définissant des clauses, des clauses de sélection, de filtres conditionnels d'agrégation, etc. Dans la présente leçon, nous abordons donc la clause de filtre conditionnel, qui s'exprime par le mot-clé WHERE assorti d'une condition. La syntaxe de base de cette requ^ete comprend donc le mot SELECT, suivi du nom des attributs ou du métacaractère astérisque lorsqu'on veut sélectionner tous les attributs, du mot-clé FROM suivi du nom de la table de laquelle on va tirer les informations, et finalement le mot-clé WHERE, suivi de la condition. Condition qui est composée par trois éléments, un attribut, un opérateur et un critère. Si l'on prend l'exemple de la recherche des hôtels de 20 lits aux Seychelles, on voit que la condition qui se trouve dans la clause conditionnelle comprend comme attribut le mot-clé hotels.lits, donc l'attribut lits, comme opérateur le signe égal et comme critère, la valeur 20. Dans le cas de la base de données des Seychelles, nous avons donc un ensemble de 124 hôtels, et si l'on applique cette requête de filtre conditionnel sur le critère nombre de lits = 20, on voit que l'on extrait une série de 10 hôtels qui comptent exactement 20 lits. [MUSIQUE] [MUSIQUE] Les opérateurs que l'on peut utiliser dans une clause WHERE sont de différentes natures, à commencer par les opérateurs généraux et en premier lieu l'opérateur égal qui permet de comparer deux valeurs entre elles, et son alter ego, l'attribut différent de qui peut s'exprimer soit sous la forme d'un point d'exclamation et du signe égal, soit de deux signes, plus petit et plus grand que. Puis les deux attributs inférieur et supérieur à, ou encore inférieur ou égal à. Les intervalles et listes, avec pour les listes le mot-clé IN suivi d'une série de valeurs séparées par des virgules et enfermées dans des parenthèses, qui exprime l'idée que la valeur de l'attribut est présente dans la collection ou l'échantillon de valeurs proposées. Et pour les intervalles, le mot-clé BETWEEN, suivi de deux valeurs séparées par le mot-clé AND qui exprime l'idée que la valeur de l'attribut est comprise entre les deux bornes définies par valeur1 et valeur2. Le test sur les valeurs nulles, avec les mots-clés IS NULL IS NOT NULL, pour vérifier si la valeur de l'attribut est nulle ou n'est pas nulle. Mais finalement, les opérateurs d'inclusion et d'exclusion de chaînes de caractères, qui permettent donc de travailler sur des chaînes de caractères, avec tout d'abord l'opérateur d'égalité, qui permet de comparer une chaîne de caractères à une valeur entre crochets. Un équivalent du signe égal, c'est l'opérateur LIKE, à ceci près qu'il est non-sensible à la casse donc indépendant des majuscules et minuscules utilisées pour décrire la chaîne de caractères valeur. Ceci est vrai dans le cas de SQL Lite, mais elle n'est pas systématique avec tous les systèmes de bases de données. L'opérateur LIKE, avec les métacaractères, ici dans le language SQL, c'est le signe % qui est utilisé comme métacaractère ou comme caractère de remplacement pour des chaînes de caractères. Donc là, on va chercher l'ensemble des objets dont l'attribut comprend la chaîne de caractères valeur à un endroit ou à un autre. Et puis, finalement, l'opérateur NOT LIKE pour dire qu'une valeur n'est pas comprise. Comme on dit dans PostgreSQL, LIKe et NOT LIKE sont équivalents et on a un autre mot-clé, un autre opérateur, ILIKE, qui est, lui, insensible à la casse. Ce tableau fait la synthèse des différents types d'opérateurs que nous venons de voir, donc les opérateurs généraux, les listes et intervalles, les valeurs nulles et les opérateurs portant sur les chaînes de caractères. Ces opérateurs sont utilisés pour définir des conditions et ce qui est intéressant dans les clauses WHERE, c'est que les conditions peuvent être combinées pour effectuer des recherches élaborées. La combinaison de conditions repose sur les mots-clés AND et OR qui permettent d'associer de manière inclusive ou exclusive, deux conditions. Des conditions supplémentaires peuvent être associées à ce système en utilisant les règles usuelles de parenthèses. Nous illustrons ici ces opérateurs, dans le cas d'une base de données SpatiaLite, en sélectionnant dans la table Hôtels, les noms et les lits, et en ajoutant comme clause conditionnelle, le fait que les lits doivent être égal à 20. On effectue cette requête. En modifiant cette requête, on peut rechercher l'ensemble des hôtels qui ont moins de 20 lits, l'ensemble des hôtels qui ont plus de 20 lits évidemment, l'ensemble des hôtels qui ont 158 lits ou 176 lits, et on en trouve deux qui ont exactement 158 et 176 lits. Alternativement, on peut rechercher l'ensemble des hôtels dont le nombre de lits est compris entre ces deux valeurs de 158 et de 176. On constate que l'on en trouve trois, ce qui permet aussi de constater que les limites, les bornes 158 et 176 sont inclusives, et non exclusives. On recherche ensuite ici l'ensemble des hôtels dont le nom correspond à Banyan Tree, on trouve cet hôtel et on voit que si l'on avait écrit Banyan tree avec un t minuscule, la requête n'aurait donné aucun résultat, alors que dans le cas de SpatiaLite, avec un LIKE, la requête n'est pas sensible à la casse et on trouve l'hôtel recherché. On recherche ensuite l'ensemble des hôtels dont le nom commence par B et on en trouve 13. Puis, l'ensmble des hôtels dont le nom commence par B ou dont le nom commence par C. Et on ajoute une condition supplémentaire, le fait que le nombre de lits doit être supérieur à 100. Et on voit que l'on trouve quatre candidats qui répondent à ces critères. Nous effectuons maintenant la même série d'opérations dans le cas d'une base de données Postgres, en utilisant l'interface Pgadmin. Donc à nouveau, on sélectionne dans la table des hôtels les champs Nom et Lits, on a on ajoute un critère dans l'interface graphique, pour que le nombre d'hôtels, le nombre de lits est égal à 20, on change l'opérateur dans cette requête pour avoir le nombre des hôtels dont le nombre de lits est inférieur à 20, maintenant supérieur à 20. Comme précédemment, on va rechercher les hôtels dont le nombre de lits se trouve, vaut 158 ou 176 et on voit que l'on doit en fait écrire dans cette interface l'ensemble de la condition de la même manière que dans le cas de SpatiaLite ou on fait ça en pur SQL. Donc ici, donc on utilise dans ces cas-là l'interface graphique où nous, on ne fait pas de différence. La requête pour les hôtels dont le nombre de chambres est compris entre 158 et 176, vous l'avez vu passer, et maintenant on recherche l'hôtel qui s'appelle Banyan Tree que l'on trouve bien évidemment. Le cas de figure avec un t minuscule et on voit que effectivement on n'a pas de résultat. En remplaçant l'opérateur EGAL par l'opérateur LIKE, toujours pas de résultat donc comme je vous l'ai dit, LIKE n'est pas non plus, est sensible à la case, si on doit utiliser l'opérateur ILIKE, pour pouvoir faire une requête qui ne soit pas sensible à la case. Comme précédemment, on recherche les hôtels dont le noms commence par b, on en trouve également 13 ce qui est rassurant, et on associe maintenant une requête supplémentaire avec de nouveau le nom de l'hôtel qui commence cette fois par la lettre c, et puis troisième requête complémentaire, le nombre de lits supérieur à 100 comme tout à l'heure, et comme tout à l'heure on trouve 4 candidats, non, là il s'est passé quelque chose de curieux, oui, les parenthèses au bon endroit, et on trouve nos quatre candidats. [MUSIQUE] [MUSIQUE] [MUSIQUE] Le principe des jointures consiste donc à associer des informations provenant de deux ou davantage de tables, et peut-être appliqué soit à la volée, soit à l'aide de requêtes SQL. Nous avions vu dans la notion portant sur la modélisation des données un exemple de jointures de tables attributaires à la volée effectuées dans le logiciel QGIS. Ici on va s'intéresser à la manière d'utiliser le langage SQL pour effectuer ces jointures de tables. Supposons donc que l'on ait deux tables A et B, constituées d'une série de lignes qui contiennent des objets, qui ont eux-mêmes un certain nombre d'attributs, de a1 à an. Et pareillement pour la table B qui a des dimensions p par q. Supposons que la table A compte 20 lignes et la table B, 10 lignes. La requête d'association de ces tables standard serait du type SELECT, la série du nom des attributs, le mot clé FROM et puis les deux noms de tables qui se suivent, séparés par une virgule. Ce type de requête donnerait le produit cartésien de ces deux tables donc chaque élément, chaque ligne de la table serait multiplié par chaque ligne de la table B ou associé à chaque ligne de la table B, de sorte à produire un ensemble résultant de 200 lignes. Et cet ensemble résultant de 200 lignes correspond en fait au cas particulier, un cas particulier de jointure qu'on appelle le CROSS JOIN. La jointure de table s'appuie toutefois le plus souvent sur un champ commun qui permet d'associer ensemble les lignes de deux tables pour lesquelles ce champ commun a la même valeur. Ce type de jointure conduit à distinguer trois types de situations dans les résultats que l'on produit, dans les comparaisons entre lignes des deux tables, tout d'abord le cas où les deux lignes sont jointes par un champ commun, qui est la même valeur, après on a les situations où les lignes de la table A qui n'ont pas d'équivalents ou de lignes jointes dans la table B, donc où B est nulle, et inversement les lignes de la table B qui n'ont pas d'équivalents du côté de A donc pour lesquelles A est nul. Si l'on regarde la taille des tables de résultats obtenues, on voit que si le nombre de jointures est nul, donc il n'existe aucun, aucune ligne des deux tables qui ont une valeur commune pour le champ Joint eh bien on trouve un ensemble de 30 lignes dans le résultat, donc les 20 lignes de la table A, pour lesquelles B est nul, et les dix lignes de la table B pour lesquelles A est nul. Dans le cas où on aurait cinq correspondances, cinq lignes de chacune des tables jointes entre elles, on voit que les lignes de A qui auraient une correspondance nulle du côté de B sont au nombre de 15, et inversement les lignes de B qui seraient nulles du côté A sont au nombre de 5, on aurait comme résultat final un total de 25 lignes dans une requête qui reprendrait l'ensemble de ces valeurs. Et finalement dans le même esprit si on a 10 jointures on retrouve au total 20 résultats possibles. La syntaxe générale donc d'une jointure basée sur la close WHERE ressemble à ceci : donc tout d'abord le mot clé SELECT suivi de l'attribut de la première table, et de l'attribut de la seconde table, séparés par une virgule, le mot clé FROM suivi des deux noms de tables, séparés par une virgule à nouveau, et puis la close WHERE avec la condition qui associe l'attribut, un attribut de la première table, l'opérateur d'égalité, et comme critère un attribut de la seconde table. Les deux attributs de la close WHERE définissant en fait la jointure. En reprenant le cas de la base de données SpatiaLite, sur les Seychelles, on écrit, voilà un peu la même requête que tout à l'heure pour les lits des hôtels, donc le mot-clé SELECT, les champs NOM et LITS nombre de lignes de la table hôtel, le mot-clé FROM avec la table hôtel, et puis la close conditionnelle dans laquelle on exprime l'idée que l'identifiant de districts qui est associé à l'hôtel est égal en fait à son équivalent dans la table districts. On ajoute en fait le nom de districts au résultat recherché et puis le nom de la table districts. Et l'on obtient donc la liste des 124 hôtels des Seychelles avec leur nombre de lits et le nom du district dans lequel il se trouve. La même opération maintenant dans l'interface pgAdmin de la table PostgreSQL PostGIS où on lit graphiquement les deux tables par le champ identifiant, on sélectionne les champs que l'on souhaite voir apparaître dans le résultat et on exécute la requête directement. [MUSIQUE] [MUSIQUE] [MUSIQUE] La syntaxe de base d'une requête de jointure basée sur la clause JOIN, se présente de la manière suivante : donc tout d'abord le mot-clé SELECT, les attributs concernés que l'on souhaite voir apparaître dans le résultat, donc un attribut de la table1, un attribut de la table2, la jointure qui va de la table1 vers la table2, et puis le mot clé ON pour spécifier le champ sur lequel s'effectue cette jointure, et dans le cas présent l'attribut3 de la table1, qui est mis en correspondance avec l'attribut4 de la table2. Si l'on compare cette syntaxe avec celle que nous venons de voir dans le cas de la jointure basée sur la close WHERE, on voit que la différence est très mince, puisque l'on introduit simplement le mot JOIN pour séparer les tables, et puis le mot clé ON à la place de la close WHERE pour définir le critère de la jointure. L'intérêt d'utiliser la syntaxe qui utilise la close JOIN c'est de bien séparer dans une requête SQL complexe les éléments de jointure des éléments de conditionnelle ce qui rend la requête plus lisible. Il existe plusieurs types de jointures, à commencer par la jointure simple ou INNER JOIN, le mot clé INNER n'étant pas nécessaire INNER JOIN et JOIN sont des choses équivalentes. On prend l'exemple d'une série d'hôtels, et associés à un lieu-dit, et d'une seconde table dans laquelle on a une série de lieux-dits. Et on voit que dans notre exemple, les éléments qui vont être sélectionnés pour lesquels les champs ID lieu-dit et ID correspondent, sont au nombre de trois, les trois hôtels, La Desirable, Augerine et Coco d'Or qui se retrouvent dans la table de résultats. Deuxième type de jointure, le LEFT JOIN ou LEFT OUTER JOIN, avec le mot OUTER qui est de nouveau facultatif, qui consiste en fait à prendre l'ensemble des éléments du tableau A, de l'ensemble A, auxquels on joint les éléments correspondants du tableau B. Donc dans notre cas l'ensemble des hôtels de la table Hotels, avec lorsque c'est possible les éléments d'information jointifs de la table Lieu-dit. On voit que notre résultat contient cette fois cinq éléments, avec dans deux cas des valeurs nulles pour la jointure et les paramètres joints, donc le Lieu-dit. Troisième type de jointure, le RIGHT JOIN, qui permet de sélectionner l'ensemble en fait des éléments de la seconde table, donc l'ensemble des lieux-dits auxquels on associe lorsque c'est possible les éléments de la première table qui ne seraient pas nuls au niveau de la jointure. Et on obtient comme résultat un tableau de quatre valeurs, dont les trois cas de base où la jointure existe et puis le dernier cas le lieu-dit Sans souci qui n'a pas de jointure et pour lesquels les éléments de la table Hotels sont nuls. Et finalement le FULL JOIN qui consiste à prendre l'ensemble en fait des possibles, donc la série des trois jointures, où on a des objets liés de part et d'autre, plus les trois cas où, soit du côté A, soit du côté B, les éléments jointifs sont nuls. Et l'on obtient donc une table qui compte six éléments, donc deux de moins, que la table de huit qu'on aurait obtenue si aucun élément jointif n'avait été présent. La syntaxe de ces requêtes spécifiques de jointures reste toujours la même avec simplement la clause LEFT, RIGHT ou FULL, qui est ajoutée à la clause JOIN dans la définition de la jointure. Un example, avec la base données SpatiaLite des Seychelles, où l'on définit dans les objets de la requête le nom des hôtels et puis le nom des districts dans lesquels se trouvent ces hôtels, puisqu'ils sont liés par un, comme on dit par une jointure, on définit cette jointure entre la table hotels et la table districts, sous la base du champ identifiant du district de la table hotels, est équivalent à l'identifiant du district dans la table districts. Même opération dans le cas de PostgreSQL PostGIS avec pgAdmin où on ajoute en fait graphiquement les deux tables dans le constructeur de requêtes graphiques, on établit la connexion de ces deux tables, les champs que l'on souhaite voir apparaître, et dans l'onglet Jointure on définit la jointure qui nous intéresse. On voit toutefois que dans la partie Edition SQL de la requête, la requête est écrite sous la forme d'une clause WHERE et non d'une clause JOIN, Je peux rectifier la chose en remplaçant la syntaxe, et on voit que le résultat est toujours le même. Mais par contre si, avec cette interface pgAdmin, je passe en mode graphique pour revenir en mode éditeur, la requête est à nouveau transformée en une requête WHERE, sans garder le mot clé JOIN. Un autre example maintenant avec le logiciel Navicat qui est un logiciel commercial, et qui offre un constructeur graphique de requêtes SQL intéressant. On ajoute la table des hôtels, la table cette fois des lieux-dits. On établit un lien entre le nom de l'hôtel et le nom des lieux-dits, on sélectionne ces deux éléments, on voit que l'on trouve deux hôtels dont le nom est le même que celui de lieux-dits. Dans l'interface graphique je peux maintenant remplacer la jointure standard par une, par un LEFT JOIN qui va me donner l'ensemble des 124 hôtels avec en tête de liste les deux pour lequel il existe un lieu-dit dans la jointure. Ensuite on passe au RIGHT JOIN, qui lui va me donner l'ensemble des lieux-dits, y compris les deux hôtels jointifs, on croit qu'il y a 346 de ces lieux-dits, et finalement on peut faire encore un FULL JOIN de ces deux tables, pour constater qu'on a, dans l'ensemble, 466 lignes dans la réponse. Dans ces jointures basées sur la clause JOIN on peut encore s'intéresser à quelques cas particuliers, que l'on peut illustrer même si l'utilisation de ces diagrammes d'ensemble n'est pas tout à fait correcte comme on le verra plus tard lorsque l'on parlera des requêtes de fusion. Ca permet quand-même d'illustrer l'esprit du propos, donc on a la requête LEFT JOIN dont on aimerait retirer en fait les éléments qui font le match pour n'avoir que les éléments de la table A qui n'ont pas de correspondance dans la table B. Si l'on reprend notre exemple de cinq hôtels et de trois lieux-dits, on se souvient que le LEFT JOIN donnait une table de cinq résultats, et sans l'intersection cela revient simplement à supprimer les trois cas où la jointure existe, pour ne garder que les deux éléments qui sont non jointifs. Du point de vue de la syntaxe SQL cela signifie simplement que l'on va ajouter à la requête de jointure, une clause de filtre dans laquelle, de filtre conditionnel dans laquelle on exprime l'idée que l'attribut de jointure, en l'occurrence l'attribut4 de la table2, donc le champ ID de la table des lieux-dits est nul. Même chose pour la jointure droite, avec dans ce cas les quatre champs sélectionnés qui lorsque l'on retire les éléments jointifs se réduisent à un seul résultat. Et à nouveau en termes de syntaxe SQL, l'adjonction d'une clause conditionnelle basée sur le fait que l'attribut de la jointure dans la première table est nul. Pour la requête complète, on avait six résultats, dont on enlève les trois éléments jointifs, pour obtenir en fait un résultat final qui comprend trois objets, et du point de vue SQL on ajoute cette fois une clause conditionnelle, qui exprime le fait que dans les deux attributs de la jointure, l'un ou l'autre doit être nul. [MUSIQUE] [MUSIQUE] [MUSIQUE] Nous avons vu donc dans cette leçon comment fonctionnent les requêtes conditionnelles basées sur la clause WHERE. En particulier nous avons vu les différents types d'opérateurs que l'on peut utiliser dans ces clauses, et nous avons vu comment cette clause peut être utilisée pour joindre deux tables. Nous avons également vu les différents types de jointures que l'on peut avoir en SQL, et nous avons vu comment, de manière très pratique, on implémente ces requêtes SQL dans divers environnements de gestion de bases de données. [MUSIQUE] [MUSIQUE]
