# Introduction et premiers SELECT > Principes des bases de données, premières requêtes SELECT et types de données. --- Index LLMS : [llms.txt](/fr/llms.txt) --- # Introduction et premiers SELECT {#dev1-ch1} ## Préambule - Qu’est-ce que le standard SQL ? - Comment lire des données - Quels types de données sont disponibles ? Ce module a pour but de présenter le standard SQL. Il se concentre sur la lecture de données déjà présentes en base. Ceci permet aussi d’aborder la question des types de données disponibles. ### Menu - Principes d’une base de données - Premières requêtes - Connaître les types de données ### Objectifs - Comprendre les principes - Écrire quelques requêtes en lecture - Connaître les différents types de données - et quelques fonctions très utiles ## Principes d’une base de données - Base de données - ensemble organisé d’informations - SGBD : **S**ystème de **G**estion de **B**ases de **D**onnées - *Database Management System* (DBMS) en anglais - programme assurant la gestion et l’accès à une ou plusieurs bases de données - SGBDR (ou RDBMS) - modèle **R**elationnel - assure la cohérence des données Si des données sont récoltées, organisées et stockées afin de répondre à un besoin spécifique, alors on parle de base de données. Une base de données peut utiliser différents supports : papier, fichiers informatiques, etc. Un Système de Gestion de Bases de Données ([SGBD](https://fr.wikipedia.org/wiki/Syst%C3%A8me_de_gestion_de_base_de_donn%C3%A9es)), ou *Database Management System* (DBMS) en anglais, assure la gestion d’une base de données informatisée. Il permet l’accès aux données, leur modification, et assure que ces opérations en conservent la cohérence. On voit aussi parfois le sigle SGBDR pour les bases « relationnelles » comme MySQL/MariaDB, Oracle, PostgreSQL… ### Type de bases de données - Modèle hiérarchique - Modèle réseau - Modèle relationnel - Modèle objet - Modèle relationnel-objet - NoSQL Au fil des années ont été développés plusieurs modèles de données, que nous allons décrire. ### Type de bases de données (1) - Modèle hiérarchique - structure arborescente - redondance des données - Modèle réseau - structure arborescente, mais permettant des associations - ex : Bull IDS2 sur GCOS Les modèles hiérarchiques et réseaux ont été les premiers modèles de données utilisées dans les années 60 sur les mainframes IBM ou Bull. Ils ont été rapidement supplantés par le modèle relationnel car les requêtes étaient dépendantes du modèle de données. Il était nécessaire de connaître les liens entre les différents nœuds de l’arborescence pour concevoir les requêtes. Les programmes sont donc complètement dépendants de la structure de la base de données. Cependant, des recherches cherchaient déjà à rendre indépendantes la vue logique et l’implémentation physique de la base de données. ### Type de bases de données (2) - Modèle relationnel - basé sur la théorie des ensembles et la logique des prédicats - à partir des années 70 - standardisé par la norme SQL (1986) - Modèle objet - structure objet - pas de standard - Modèle relationnel-objet - le standard SQL ajoute des concepts objets Le modèle relationnel est issu des travaux d’[Edgar F. Codd](https://fr.wikipedia.org/wiki/Edgar_Frank_Codd) menés dans les laboratoires d’IBM à la fin des années 60. Ses travaux avaient pour but de rendre indépendant le stockage physique de la vue logique de la base de données. Mathématicien de formation, Codd s’est appuyé sur la théorie des ensembles et la logique des prédicats pour établir les fondements des bases de données relationnelles. Pour manipuler les données de façon ensembliste, il a mis au point le langage SQL. Ce langage a été développé dans les années 70, et est à l’origine du standard SQL, qui s’est répandu dans les années 80 et qui a rendu le modèle relationnel très populaire. La puissance de calcul du matériel de cette époque a permis la concrétisation de l’implémentation de cette couche d’abstraction entre modèle logique et stockage physique des données. Le modèle objet est, quant à lui, issu de la mouvance autour des langages objets. Du fait de l’absence d’un standard avéré, le modèle objet n’a jamais été populaire et est toujours resté dans l’ombre du modèle relationnel. La norme du modèle relationnel a néanmoins été étendue pour intégrer des fonctionnalités objets. On parle alors de modèle relationnel-objet. PostgreSQL en est un exemple, c’est un SGBDRO (Système de Gestion de Bases de Données Relationnel-Objet). ### Type de bases de données (3) - NoSQL : *Not only SQL* - clé-valeur (Redis) - graphe (Neo4J) - document (MongoDB, CouchDB) - orienté colonne (HBase) - pas de norme de langage de requête - Rapprochement relationnel/NoSQL - PostgreSQL permet de stocker des documents (JSON, XML) Les bases NoSQL sont une famille de bases de données qui répondent à d’autres besoins et contraintes que les bases relationnelles. Les bases NoSQL sont souvent des bases « sans schéma », la base ne vérifiant plus l’intégrité des données selon des contraintes définies dans le modèle de données. Chaque base de ce segment dispose d’un langage de requête spécifique, il n’y a ni standard ni norme. Ce type de base offre souvent la possibilité d’offrir du *sharding* simple à mettre en œuvre. Le *sharding* consiste à répartir les données physiquement sur plusieurs serveurs pour paralléliser les traitements. En contrepartie, la durabilité des données n’est pas assurée, au contraire d’une base relationnelle qui assure la durabilité dès la réponse à un `COMMIT`. La cohérence des données entre nœuds est un autre sujet. Toute amélioration d’un outil sur ces points a évidemment un impact sur les performances. Un des avantages des technologies NoSQL est qu’un modèle clé-valeur permet facilement d’utiliser des algorithmes de type *MapReduce* : diviser le problème en sous-problèmes traités parallèlement par différents nœuds (phase *Map*), puis synthétisés de façon centralisée (phase *Reduce*). Les bases de données relationnelles ne sont pas incompatibles avec [MapReduce](https://en.wikipedia.org/wiki/MapReduce) en soi. Simplement, le langage SQL étant déclaratif, il est conceptuellement opposé à la description fine des traitements qu’on doit réaliser avec MapReduce. C’est le travail de l’optimiseur d’effectuer ce genre d’opérations, et non celui du développeur. Un meilleur argument des bases NoSQL est le côté *schemaless*, permettant d’enregistrer des documents sans trop se soucier de leur cohérence, de leur contenu, sans devoir réfléchir par avance à un format de données aussi rigide que peut l’être une table SQL. La flexibilité est un atout, mais il faut être conscient que le manque de cohérence ou de rigueur se paie. forcément plus tard. La « mode » du NoSQL est un peu retombée ces dernières années. En effet, les bases de données classiques ont profité des progrès matériels (disques NVMe et nombreux cœurs sur un serveur, par exemple) ; la parallélisation est mieux gérée : par requête, sur des secondaires, voir avec du *sharding*; et les algorithmes se sont améliorés. Tout cela permet de procéder à des tâches de plus en plus lourdes tout en conservant les avantages d’un SGBDR : SQL standardisé, verrous bien gérés, cohérence forte des données. Enfin, les SGBDR ont intégré quelques fonctionnalités NoSQL, en premier lieu le stockage des documents JSON, ce qui permet de les utiliser là où ils sont pertinents (schémas flexibles). Le choix d’un outil résulte donc d’un arbitrage entre différentes priorités. ### Modèle relationnel - Indépendance entre la vue logique et la vue physique - le SGBD gère lui-même le stockage physique - Table ou *relation* - Un ensemble de tables représente la vue logique Le modèle relationnel garantit l’indépendance entre la vue logique et la vue physique. L’utilisateur ne se préoccupe que des objets logiques (pour lire ou écrire des enregistrements), et le SGBD traduit la demande exprimée avec des objets logiques (tables, vues, fonctions…) en actions à réaliser sur des objets physiques (fichiers, sockets, mémoire…). Les objets logiques sont appelés des relations. Ce sont généralement les tables, mais il existe d’autres objets qui sont aussi des relations (les vues par exemple, mais aussi les index et les séquences). ### Caractéristiques du modèle relationnel - Théorie des ensembles - Logique des prédicats - Logique à 3 états - Vrai, Faux, `NULL` (= inconnu) Le modèle relationnel se base sur la théorie des ensembles. Chaque relation contient un ensemble de données et ces différents ensembles peuvent se joindre suivant certaines conditions. La logique des prédicats est un sous-ensemble de la théorie des ensembles. Elle sert à exprimer des formules logiques qui permettent de filtrer les ensembles de départ pour créer de nouveaux ensembles (autrement dit, filtrer les enregistrements d’une relation). Cependant, tout élément d’un enregistrement n’est pas forcément connu à un instant t. Les filtres et les jointures doivent donc gérer trois états lors d’un calcul de prédicat : vrai, faux ou « inconnu ». **Astuce** Cette dernière valeur est aussi connue comme `NULL`, et son utilisation dans les conditions est parfois délicate. Pour un humain ou pour l’application, `NULL` peut signifier : « inconnu », « non pertinent » ou « non encore renseigné ». ### ACID Gestion transactionnelle : la force des bases de données relationnelles : - **Atomicité** (*Atomic*) - **Cohérence** (*Consistent*) - **Isolation** (*Isolated*) - **Durabilité** (*Durable*) Les propriétés ACID sont le fondement même de toute bonne base de données. Il s’agit de l’acronyme des quatre règles que toute transaction (c’est-à-dire une suite d’ordres modifiant les données) doit respecter : - **A** : Une transaction est appliquée en « tout ou rien ». - **C** : Une transaction amène la base d’un état stable à un autre. - **I** : Les transactions n’agissent pas les unes sur les autres. - **D** : Une transaction validée sera conservée de manière permanente. Les bases de données relationnelles les plus courantes depuis des décennies (PostgreSQL bien sûr, mais aussi Oracle, MySQL, SQL Server, SQLite…) se basent sur ces principes, même si elles font chacune des compromis différents suivant leurs cas d’usage, les compromis acceptés à chaque époque avec la performance et les versions. **Atomicité** : Une transaction doit être exécutée entièrement ou pas du tout, et surtout pas partiellement, même si elle est longue et complexe, même en cas d’incident majeur sur la base de données. L’exemple basique est une transaction bancaire : le montant d’un virement doit être sur un compte ou un autre, et en cas de problème ne pas disparaître ou apparaître en double. Ce principe garantit que les données modifiées par des transactions valides seront toujours visibles dans un état stable, et évite nombre de problèmes fonctionnels comme techniques. **Cohérence** : Un état cohérent respecte les règles de validité définies dans le modèle, c’est-à-dire les contraintes définies dans le modèle : types, plages de valeurs admissibles, unicité, liens entre tables (clés étrangères), etc. Le non-respect de ces règles par l’applicatif entraîne une erreur et un rejet de la transaction. **Isolation** : Des transactions simultanées doivent agir comme si elles étaient seules sur la base. Surtout, elles ne voient pas les données *non validées* des autres transactions. Ainsi une transaction peut travailler sur un état stable et fixe, et durer assez longtemps sans risque de gêner les autres transactions. Il existe plusieurs « niveaux d’isolation » pour définir précisément le comportement en cas de lectures ou écritures simultanées sur les mêmes données et pour arbitrer avec les contraintes de performances ; le niveau le plus contraignant exige que tout se passe comme si toutes les transactions se déroulaient successivement. **Durabilité** : Une fois une transaction validée par le serveur (typiquement : `COMMIT` ne retourne pas d’erreur, ce qui valide la cohérence et l’enregistrement physique), l’utilisateur doit avoir la garantie que la donnée ne sera pas perdue ; du moins jusqu’à ce qu’il décide de la modifier à nouveau. Cette garantie doit valoir même en cas d’événement catastrophique : plantage de la base, perte d’un disque… C’est donc au serveur de s’assurer autant que possible que les différents éléments (disque, système d’exploitation…) ont bien rempli leur office. C’est à l’humain d’arbitrer entre le niveau de criticité requis et les contraintes de performances et de ressources adéquates (et fiables) à fournir à la base de données. **NoSQL** : À l’inverse, les outils de la mouvance (« NoSQL », par exemple MongoDB ou Cassandra), ne fournissent pas les garanties ACID. C’est le cas de la plupart des bases non-relationnelles, qui reprennent le modèle [BASE](https://en.wikipedia.org/wiki/Eventual_consistency) (*Basically Available*, *Soft State*, *Eventually Consistent*, soit succintement : disponibilité d’abord ; incohérence possible entre les réplicas ; cohérence… à terme, après un délai). Un intérêt est de débarrasser le développeur de certaines lourdeurs apparentes liées à la modélisation assez stricte d’une base de données relationnelle. Cependant, la plupart des applications ont d’abord besoin des garanties de sécurité et cohérence qu’offrent un moteur transactionnel classique, et la décision d’utiliser un système ne les garantissant pas ne doit pas être prise à la légère ; sans parler d’autres critères comme la fragmentation du domaine par rapport au monde relationnel et son SQL (à peu près) standardisé. Avec le temps, les moteurs transactionnels ont acquis des fonctionnalités qui faisaient l’intérêt des bases NoSQL (en premier lieu la facilité de réplication et le stockage de JSON), et ces dernières ont tenté d’intégrer un peu plus de sécurité dans leur modèle. ### Langage SQL - Norme ISO 9075 - dernière version stable : SQL:2023 - Langage déclaratif - on décrit le résultat et pas la façon de l’obtenir - Traitement ensembliste - par opposition au traitement procédural - « on effectue des opérations sur des relations pour obtenir des relations » Le langage SQL a été normalisé par l’ANSI en 1986 et est devenu une norme ISO internationale en 1987. La norme a subi [plusieurs évolutions](https://fr.wikipedia.org/wiki/Structured_Query_Language#Historique) dans le but d’ajouter des fonctionnalités correspondantes aux attentes de l’industrie logicielle. Parmi ces améliorations, notons l’intégration de quelques fonctionnalités objets pour le modèle relationnel-objet (SQL-99), ou les puissantes « fonctions de fenêtrage » (SQL:2003, SQL:2008). Le dernier standard est SQL:2023. **Astuce** Le standard n’est suivi strictement ni par PostgreSQL ni ses concurrents. De nombreuses fonctionnalités apparaissent chez l’un ou l’autre avant d’être standardisées, si elles le sont. Chaque produit doit aussi gérer la compatibilité avec un historique parfois très ancien. PostgreSQL est le moteur qui se rapproche le plus du standard, au point de devenir une référence, mais possède néanmoins quelques écarts et de nombreuses extensions. ### SQL est un langage - Langage - règles d’écriture - règles de formatage - commentaires - Améliore la lisibilité d’une requête Hormis la syntaxe, il n’y a pas de règles strictes concernant l’écriture de requêtes SQL (majuscules, minuscules, espaces…). Il faut néanmoins avoir à l’esprit qu’il s’agit d’un langage à part entière et, au même titre que ce qu’un développeur fait avec n’importe quel code source, il convient de l’écrire de façon lisible. ### Recommandations d’écriture et de formatage - Écriture - mots clés SQL en MAJUSCULES - identifiants de colonnes/tables en minuscule - Formatage - dissocier les éléments d’une requête - un prédicat par ligne - indentation Cet exemple est tiré du [forum postgresql.fr](https://forum.postgresql.fr/viewtopic.php?id=2610). Quelle est la requête la plus lisible ? - celle-ci ? **Exemple 1-1.** Exemple 1\-1 \- code ou sortie extraite du manuel DEV1\. ```sql select groupeid,datecreationitem from itemagenda where typeitemagenda = 5 and groupeid in(12225,12376) and datecreationitem > now() order by groupeid, datecreationitem ; ``` - ou celle-ci ? **Exemple 1-2.** Exemple 1\-2 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT groupeid, datecreationitem FROM itemagenda WHERE typeitemagenda = 5 AND groupeid IN (12225,12376) AND datecreationitem > now() ORDER BY groupeid, datecreationitem ; ``` - ou encore celle-ci, réécrite avec [pgFormatter](https://sqlformat.darold.net/) ? **Exemple 1-3.** Exemple 1\-3 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT groupeid, datecreationitem FROM itemagenda WHERE typeitemagenda = 5 AND groupeid IN (12225, 12376) AND datecreationitem > now() ORDER BY groupeid, datecreationitem; ``` La base de données exécutera sans souci l’un comme l’autre code, avec bien sûr le même résultat. Sans se tenir forcément à un format précis, il convient surtout d’adopter un style lisible, et cohérent avec le code SQL existant. ### Commentaires - Commentaire sur le reste de la ligne **Exemple 1-4.** Exemple 1\-4 \- code ou sortie extraite du manuel DEV1\. ```sql -- commentaire ``` - Commentaire dans un bloc **Exemple 1-5.** Exemple 1\-5 \- code ou sortie extraite du manuel DEV1\. ```sql /* bloc */ ``` Une requête SQL peut être commentée au même titre qu’un programme standard. Le marqueur `--` permet de signifier à l’analyseur syntaxique que le reste de la ligne est commenté, il n’en tiendra donc pas compte dans l’analyse de la requête. Un commentaire peut aussi se présenter sous la forme d’un bloc de commentaire, le bloc pouvant occuper plusieurs lignes : **Exemple 1-6.** Exemple 1\-6 \- code ou sortie extraite du manuel DEV1\. ```sql /* Ceci est un commentaire sur plusieurs lignes */ ``` Aucun des éléments compris entre le marqueur de début de bloc `/*` et le marqueur de fin de bloc `*/` ne sera pris en compte. Certains SGBDR propriétaires utilisent ces commentaires pour y placer des informations (appelées parfois *hints*) qui influencent le comportement de l’optimiseur, mais PostgreSQL ne possède pas nativement ce genre de mécanisme. ### Les 4 types d’ordres SQL - **DDL** - *Data Definition Language* - définit les structures de données - **DML** - *Data Manipulation Language* - manipule les données - **DCL** - *Data Control Language* - contrôle l’accès aux données - **TCL** - *Transaction Control Language* - contrôle les transactions - implicites si « autocommit » Le langage SQL est divisé en quatre sous-ensembles qui ont chacun un but différent. Les ordres DDL (pour `Data Definition Language`) permettent de définir les structures de données. On y retrouve les ordres suivants : - `CREATE` : crée un objet ; - `ALTER` : modifie la définition d’un objet ; - `DROP` : supprime un objet ; - `TRUNCATE` : vide un objet ; - `COMMENT` : ajoute un commentaire sur un objet. Les ordres DML (pour `Data Manipulation Language`) permettent l’accès et la modification des données. On y retrouve les ordres suivants : - `SELECT` : lit les données d’une ou plusieurs tables ; - `INSERT` : ajoute des données dans une table ; - `UPDATE` : modifie les données d’une table ; - `MERGE` : ajoute ou met à jour une ligne selon une condition ; - `DELETE` : supprime les données d’une table. Les ordres DCL (pour `Data Control Language`) permettent de contrôler l’accès aux données. Ils permettent plus précisément de donner ou retirer des droits à des utilisateurs ou des groupes sur les objets de la base de données : - `GRANT` : donne un droit d’accès à un rôle sur un objet - `REVOKE` : retire un droit d’accès d’un rôle sur un objet Enfin, les ordres TCL (pour *Transaction Control Language*) permettent de contrôler les transactions : - `BEGIN` : ouvre une transaction ; - `COMMIT` : valide les traitements d’une transaction ; - `ROLLBACK` : annule les traitements d’une transaction ; - `SAVEPOINT` : crée un point de reprise dans une transaction ; - `SET TRANSACTION` : modifie les propriétés d’une transaction en cours. Les ordres `BEGIN` et `COMMIT` sont souvent implicites dans le cas d’ordres isolés, si l’« autocommit » est activé. Vous devez encadrer manuellement vos commandes avec `BEGIN ;` et `COMMIT ;` pour créer une transaction de plus d’un ordre. Le mode d’autocommit dépend en fait de l’outil client, et `psql` a un paramètre `autocommit` à `on` par défaut. Mais ce n’est pas forcément le cas sur votre configuration précise, et d’autres bases de données peuvent avoir un comportement par défaut inverse. Le `ROLLBACK` est implicite en cas de sortie brutale (plantage…). Noter que, contrairement à d’autres bases, PostgreSQL n’effectue pas de `COMMIT` implicite sur certaines opérations : les ordres `CREATE TABLE`, `DROP TABLE`, `TRUNCATE TABLE`… sont transactionnels, n’effectuent aucun `COMMIT` et peuvent être annulés par `ROLLBACK`. ## Lecture de données - Ordre `SELECT` - lecture d’une ou plusieurs tables - ou appel de fonctions La lecture des données se fait via l’ordre `SELECT`. Il permet de récupérer des données d’une ou plusieurs tables (il faudra dans ce cas joindre les tables). Il permet aussi de faire appel à des fonctions stockées en base. ### Syntaxe de SELECT **Exemple 1-7.** Exemple 1\-7 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT expressions_colonnes [ FROM elements_from ] [ WHERE predicats ] [ ORDER BY expressions_orderby ] [ LIMIT limite ] [ OFFSET offset ]; ``` L’ordre `SELECT` est composé de différents éléments dont la plupart sont optionnels. L’exemple de syntaxe donné ici n’est pas complet. La syntaxe complète de l’ordre `SELECT` est disponible dans le [manuel de PostgreSQL](https://docs.postgresql.fr/current/sql-select.html). ### Liste de sélection - Description du résultat de la requête - colonnes retournées - renommage - dédoublonnage La liste de sélection décrit le format de la table virtuelle qui est retournée par l’ordre `SELECT`. Les types de données des colonnes retournées seront conformes au type des éléments donnés dans la liste de sélection. ### Colonnes retournées - Liste des colonnes retournées - expression - séparées par une virgule - Expression - constante - référence de colonne : ```sql table.colonne ``` - opération sur des colonnes et/ou des constantes Les exemples ci-dessous utilisent la base de données **cave** qu’il vous faudra peut-être installer. Si vous ne l’avez pas déjà, la base **cave** (dump de 2,6 Mo, pour 71 Mo sur le disque au final) peut être téléchargée et restaurée ainsi : **Exemple 1-8.** Exemple 1\-8 \- code ou sortie extraite du manuel DEV1\. ```bash curl -kL https://dali.bo/tp_cave -o cave.dump psql -c "CREATE ROLE caviste LOGIN PASSWORD 'caviste'" psql -c "CREATE DATABASE cave OWNER caviste" pg_restore -d cave cave.dump # NB : une erreur sur un schéma 'public' existant est normale ``` La liste de sélection décrit le format de la table virtuelle qui est retournée par l’ordre `SELECT`. Cette liste est composée d’expressions séparées par une virgule. Chaque expression peut être une simple constante, peut faire référence à des colonnes d’une table lue par la requête, et peut être un appel à une fonction. Une expression peut être plus complexe. Par exemple, elle peut combiner plusieurs constantes et/ou colonnes à l’aide d’opérations. Parmi les opérations les plus classiques, les opérateurs arithmétiques classiques sont utilisables pour les données numériques. L’opérateur de concaténation permet de concaténer des chaînes de caractères. L’expression d’une colonne peut être une constante : **Exemple 1-9.** Exemple 1\-9 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT 1; ``` **Tableau 1-1.** Tableau 1\-1 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text ?column? ---------- 1 ``` Elle peut aussi être une référence à une colonne d’une table : **Exemple 1-10.** Exemple 1\-10 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT appellation.libelle FROM appellation; ``` Comme il n’y a pas d’ambiguïté avec la colonne `libelle`, la référence de la colonne `appellation.libelle` peut être simplifiée en `libelle` : **Exemple 1-11.** Exemple 1\-11 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT libelle FROM appellation; ``` Le SGBD saura déduire la table et la colonne mises en œuvre dans cette requête. Il faudra néanmoins utiliser la forme complète `table.colonne` si la requête met en œuvre des tables qui possèdent des colonnes qui portent des noms identiques. Une requête peut sélectionner plusieurs colonnes. Dans ce cas, les expressions de colonnes sont définies sous la forme d’une liste dont chaque élément est séparé par une virgule : **Exemple 1-12.** Exemple 1\-12 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT id, libelle, region_id FROM appellation; ``` Le joker `*` permet de sélectionner l’ensemble des colonnes d’une table, elles apparaîtront dans leur ordre physique (attention si l’ordre change !). **Attention** L’utilisation de `SELECT *` est généralement une mauvaise pratique. Le code de production ne devrait pas en contenir. De nombreux soucis de performance sont liés à trop de colonnes récupérées sans être utilisées au final (volumétrie, inhibition d’optimisations…). **Exemple 1-13.** Exemple 1\-13 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM appellation; ``` Si une requête met en œuvre plusieurs tables, on peut choisir de retourner toutes les colonnes d’une seule table : **Exemple 1-14.** Exemple 1\-14 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT appellation.* FROM appellation; ``` Enfin, on peut récupérer un tuple entier de la façon suivante : **Exemple 1-15.** Exemple 1\-15 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT appellation FROM appellation; ``` Une expression de colonne peut également être une opération, par exemple une addition : **Exemple 1-16.** Exemple 1\-16 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT 1 + 1; ``` **Tableau 1-2.** Tableau 1\-2 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text ?column? ---------- 2 (1 row) ``` Ou une soustraction : **Exemple 1-17.** Exemple 1\-17 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT annee, nombre - 10 FROM stock; ``` ### Alias de colonne - Renommage - ou alias - `AS` : ```sql expression AS alias ``` - le résultat portera le nom de l’alias Afin de pouvoir nommer de manière adéquate les colonnes du résultat d’une requête `SELECT`, le mot clé `AS` permet de définir un alias de colonne. Cet alias sera utilisé dans le résultat pour nommer la colonne en sortie : **Exemple 1-18.** Exemple 1\-18 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT 1 + 1 AS somme; ``` **Tableau 1-3.** Tableau 1\-3 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text somme ------- 2 (1 row) ``` Cet alias n’est pas utilisable dans le reste de la requête (par exemple dans la clause `WHERE`). ### Dédoublonnage des résultats **Exemple 1-19.** Exemple 1\-19 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT DISTINCT expressions_colonnes… ``` - Dédoublonnage des résultats avant de les retourner - à ne pas utiliser systématiquement Par défaut, `SELECT` retourne tous les résultats d’une requête. Parfois, des doublons peuvent se présenter dans le résultat. La clause `DISTINCT` permet de les éviter en réalisant un dédoublonnage des données avant de retourner le résultat de la requête. Il faut néanmoins faire attention à l’utilisation systématique de la clause `DISTINCT`. En effet, elle entraîne une déduplication systématique des données juste avant de retourner les résultats de la requête, ce qui va souvent consommer de la ressource mémoire, voire de la ressource disque si le volume de données à trier est important. De plus, cela va augmenter le temps de réponse de la requête du fait de cette opération supplémentaire. En règle générale, la clause `DISTINCT` devient inutile lorsqu’elle doit trier un ensemble qui contient des colonnes qui sont déjà uniques. Si une requête récupère une clé primaire, les données sont uniques par définition. Le `SELECT DISTINCT` sera alors transformé en simple `SELECT`. ### Dérivation - SQL permet de dériver les valeurs des colonnes - opérations arithmétiques : `+`, `-`, `/`, `*` - concaténation de chaînes : `||` - appel de fonction Les constantes et valeurs des colonnes peuvent être dérivées selon le type des données manipulées. Les données numériques peuvent être dérivées à l’aide des opérateurs arithmétiques standards : `+`, `-`, `/`, `*`. Elles peuvent faire l’objet d’autres calculs à l’aide de fonctions internes et de fonctions définies par l’utilisateur. La requête suivante permet de calculer le volume total en litres de vin disponible dans le stock du caviste : **Exemple 1-20.** Exemple 1\-20 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT SUM(c.contenance * s.nombre) FROM stock s JOIN contenant c ON (contenant_id=c.id); ``` Les données de type chaînes de caractères peuvent être concaténées à l’aide de l’opérateur dédié `||`. Cet opérateur permet de concaténer deux chaînes de caractères mais également des données numériques avec une chaîne de caractères. Dans la requête suivante, l’opérateur de concaténation est utilisé pour ajouter l’unité. Le résultat est ainsi implicitement converti en chaîne de caractères. **Exemple 1-21.** Exemple 1\-21 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT SUM(s.contenance * s.nombre) || ' litres' FROM stock AS s JOIN contenant c ON (contenant_id=c.id); ``` De manière générale, il n’est pas recommandé de réaliser les opérations de formatage des données dans la base de données. La base de données ne doit servir qu’à récupérer les résultats, le formatage étant assuré par l’application. Différentes fonctions sont également applicables aux chaînes de caractères, de même qu’aux autres types de données. ### Fonctions utiles - Fonctions sur données temporelles : - date et heure courante : `now()` - âge : `age(timestamp)` - extraire une partie d’une date : `extract('year' FROM timestamp)` - ou `date_part('Y',timestamp)` - `to_char(date, format)` : formatter l’affichage d’une date - Fonctions sur données caractères : - longueur d’une chaîne de caractère : `char_length(chaine)` - Compter les lignes : `count(*)` Parmi les fonctions les plus couramment utilisées, la fonction `now()` permet d’obtenir la date et l’heure courante. Elle ne prend aucun argument. Elle est souvent utilisée, notamment pour affecter automatiquement la valeur de l’heure courante à une colonne. La fonction `age(timestamp)` permet de connaître l’âge d’une date par rapport à la date courante. La fonction `char_length(varchar)` permet de connaître la longueur d’une chaîne de caractère. Enfin, la fonction `count(*)` permet de compter le nombre de lignes. Il s’agit d’une fonction d’agrégat, il n’est donc pas possible d’afficher les valeurs d’autres colonnes sans faire appel aux capacités de regroupement des lignes de SQL. **Exemples** Affichage de l’heure courante (noter la présence du fuseau horaire) : **Exemple 1-22.** Exemple 1\-22 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now(); ``` **Tableau 1-4.** Tableau 1\-4 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text now ------------------------------ 2025-08-04 18:03:08.396493+02 ``` Affichage de l’âge du 1ᵉʳ janvier 2000 : **Exemple 1-23.** Exemple 1\-23 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT age(date '2000-01-01'); ``` **Tableau 1-5.** Tableau 1\-5 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text age ------------------------ 25 years 7 mons 3 days ``` Affichage de la longueur de la chaîne « Dalibo » : **Exemple 1-24.** Exemple 1\-24 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT char_length('Dalibo'); ``` **Tableau 1-6.** Tableau 1\-6 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text char_length ------------- 6 ``` Affichage du nombre de lignes de la table `vin` : **Exemple 1-25.** Exemple 1\-25 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT count(*) FROM vin; ``` **Tableau 1-7.** Tableau 1\-7 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text count ------- 6067 ``` ### Clause FROM **Exemple 1-26.** Exemple 1\-26 \- code ou sortie extraite du manuel DEV1\. ```sql FROM expression_table [, expression_table …] ``` - Description des tables mises en œuvre dans la requête - une seule table - plusieurs tables jointes - sous-requête La clause `FROM` permet de lister les tables qui sont mises en œuvres dans la requêtes `SELECT`. Il s’agit souvent d’une table physique ou d’une vue, mais ce peut être aussi une vue matérialisée ou une sous-requête. Le résultat de leur lecture sera une table du point de vue de la requête qui la met en œuvre. Plusieurs tables peuvent être précisées ici, généralement pour les associer avec une jointure. ### Alias de table - Mot-clé `AS` - optionnel : ```sql reference_table alias ``` - La table sera ensuite référencée par l’alias **Exemple 1-27.** Exemple 1\-27 \- code ou sortie extraite du manuel DEV1\. ```sql reference_table [AS] alias ``` **Exemple 1-28.** Exemple 1\-28 \- code ou sortie extraite du manuel DEV1\. ```sql reference_table AS alias (alias_colonne1, …) ``` De la même façon qu’on peut créer des alias de colonnes, on peut créer des alias de tables. La table sera ensuite référencée uniquement par cet alias dans la requête. Elle ne pourra plus être référencée par son nom réel. L’utilisation du nom réel provoquera d’ailleurs une erreur. **Astuce** Les alias sont très utiles pour la lisibilité, notamment s’il y a beaucoup de tables ou si leurs noms sont longs. Ils sont même vitaux quand on utilise la même table plusieurs fois dans une requête. N’hésitez pas à rajouter des alias de table dès qu’il y en a plus d’une. Le mot clé `AS` permet de définir un alias de table. Le nom réel de la table se trouve à gauche, l’alias se trouve à droite. L’exemple suivant définit un alias `reg` sur la table `region` : **Exemple 1-29.** Exemple 1\-29 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT id, libelle FROM region AS reg; ``` Le mot clé `AS` est optionnel : **Exemple 1-30.** Exemple 1\-30 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT id, libelle FROM region reg; ``` La requête suivante montre l’utilisation d’un alias pour les deux tables mises en œuvre dans la requête. La table `stock` a pour alias `s` et la table `contenant` a pour alias `c`. Les deux tables possèdent toutes les deux une colonnes `id`, ce qui peut poser une ambiguïté dans la clause de jointure (`ON (contenant_id=c.id)`). La condition de jointure portant sur la colonne `contenant_id` de la table `stock`, son nom est unique et ne porte pas à ambiguïté. La condition de jointure porte également sur la colonne `id` de table `contenant`, il faut préciser le nom complet de la colonne en utilisant le préfixe `c` pour la nommer : `c.id`. **Exemple 1-31.** Exemple 1\-31 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT SUM(c.contenance * s.nombre) AS volume_total FROM stock s JOIN contenant c ON (contenant_id=c.id); ``` ### Nommage des objets - Noms d’objets par défaut en minuscules - `a-z`, `_` et chiffres, si possible - `Nom_Objet` devient `nom_objet` - Au besoin, le guillemet double `"` conserve la casse - `"Nom_Objet"` Avec PostgreSQL, les noms des objets sont automatiquement convertis en minuscule, sauf s’ils sont englobés entre des guillemets doubles. Si jamais ils sont créés avec une casse mixte en utilisant les guillemets doubles, chaque appel à cet objet devra utiliser la bonne casse et les guillemets doubles. Même si c’est parfaitement possible, il est préférable de ne pas utiliser d’accents ou de caractères exotiques dans les noms des objets, car il y a parfois des surprises en fonction des outils et systèmes d’exploitation utilisés. Les noms de tables et de champs sont généralement masqués par les outils de requêtage, ou peuvent être aliasés pour la présentation. **Astuce** Il est donc conseillé d’utiliser une notation des objets ne comprenant que des caractères minuscules, au besoin des chiffres et `_` à la place d’un espace. Dans les données par contre, rien ne vous interdit d’utiliser tout ce que permet l’encodage Unicode : **Exemple 1-32.** Exemple 1\-32 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT nom AS "Nom", prenom as "Prénom" FROM eleves ; ``` **Tableau 1-8.** Tableau 1\-8 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text Nom | Prénom ----------------+------------- L'Huillier | Jean-Pierre Sigurðardóttir | Þór Einarsson | Björn ``` ### Clause WHERE - Permet d’exprimer des conditions de filtrage - prédicats - Un prédicat est une opération logique - renvoie vrai ou faux - La ligne est présente dans le résultat - si l’expression logique des prédicats est vraie La clause `WHERE` permet de définir des conditions de filtrage des données. Ces conditions de filtrage sont appelées des prédicats. Après le traitement de la clause `FROM`, chaque ligne de la table virtuelle dérivée est vérifiée avec la condition de recherche. Si le résultat de la vérification est positif (`true`), la ligne est conservée dans la table de sortie, sinon (c’est-à-dire si le résultat est faux ou nul) la ligne est ignorée. La condition de recherche référence typiquement au moins une colonne de la table générée dans la clause `FROM` ; ceci n’est pas requis mais, dans le cas contraire, la clause `WHERE` n’aurait aucune utilité. ### Expression et opérateurs de prédicats - Comparaison - `=`, `<`, `>`, `<=`, `>=`, `<>` - Négation - `NOT` **Exemple 1-33.** Exemple 1\-33 \- code ou sortie extraite du manuel DEV1\. ```sql expression operateur_comparaison expression ``` Un prédicat est composé d’une expression qui est soumise à un opérateur de prédicat pour être éventuellement comparé à une autre expression. L’opérateur de prédicat retourne alors `true` si la condition est vérifiée ou `false` si elle ne l’est pas, ou `NULL` si son résultat ne peut être calculé. Les opérateurs de comparaison sont les opérateurs de prédicats les plus souvent utilisés. L’opérateur d’égalité `=` peut être utilisé pour vérifier l’égalité de l’ensemble des types de données supportés par PostgreSQL. Il faudra faire attention à ce que les données comparées soient de même type. L’opérateur de différence `!=` peut aussi s’écrire `<>`. L’opérateur de négation `NOT` permet d’inverser la véracité des prédicats. Un prédicat faux retournera donc vrai et inversement. La clause `NOT` se place devant l’expression entière. **Exemples** Sélection de la région dont l’identifiant est égal à 3 (et ensuite différent de 3) : **Exemple 1-34.** Exemple 1\-34 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM region WHERE id = 3; SELECT * FROM region WHERE NOT id = 3; ``` ### Combiner des prédicats - OU logique - `predicat OR predicat` - ET logique - `predicat AND predicat` Les opérateurs logiques `OR` et `AND` permettent de combiner plusieurs prédicats dans la clause `WHERE`. L’opérateur `OR` est un OU logique. Il retourne vrai si au moins un des deux prédicats combinés est vrai. L’opérateur `AND` est un ET logique. Il retourne vrai si et seulement si les deux prédicats combinés sont vrais. Au même titre qu’une multiplication ou une division sont prioritaires sur une addition ou une soustraction dans un calcul, l’évaluation de l’opérateur `AND` est prioritaire sur celle de l’opérateur `OR`. Et, tout comme dans un calcul, il est possible de protéger les opérations prioritaires en les encadrant de parenthèses. **Astuce** D’ailleurs, au moindre doute, n’hésitez pas à ajouter des parenthèses. **Exemples** Dans le stock, affiche les vins dont le nombre de bouteilles est inférieur à 2 ou supérieur à 16 : **Exemple 1-35.** Exemple 1\-35 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM stock WHERE nombre < 2 OR nombre > 16; ``` ### Correspondance de motif - Comparaison de motif **Exemple 1-36.** Exemple 1\-36 \- code ou sortie extraite du manuel DEV1\. ```sql chaine LIKE motif ESCAPE 'c' ``` - `%` : toute chaîne de 0 à plusieurs caractères - `_` : un seul caractère - Expression régulière POSIX **Exemple 1-37.** Exemple 1\-37 \- code ou sortie extraite du manuel DEV1\. ```sql chaine ~ motif ``` L’opérateur `LIKE` permet de réaliser une recherche simple sur motif. La chaîne exprimant le motif de recherche peut utiliser deux caractères joker : `_` et `%`. Le caractère `_` prend la place d’un caractère inconnu, qui doit toujours être présent. Le caractère `%` est un joker qui permet d’exprimer que PostgreSQL doit trouver entre 0 et plusieurs caractères. Exploiter la clause `LIKE` avec un motif sans joker ne présente pas d’intérêt. Il est préférable dans ce cas d’utiliser l’opérateur d’égalité. Le mot clé `ESCAPE 'c'` permet de définir un caractère d’échappement pour protéger les caractères `_` et `%` qui font légitimement partie de la chaîne de caractère du motif évalué. Lorsque PostgreSQL rencontre le caractère d’échappement indiqué, les caractères `_` et `%` seront évalués comme étant les caractères `_` et `%` et non comme des jokers. L’opérateur `LIKE` dispose d’une déclinaison qui n’est pas sensible à la casse. Il s’agit de l’opérateur `ILIKE`. **Exemples** Création d’un jeu d’essai : **Exemple 1-38.** Exemple 1\-38 \- code ou sortie extraite du manuel DEV1\. ```sql CREATE TABLE motif (chaine varchar(30)); INSERT INTO motif (chaine) VALUES ('Durand'), ('Dupont'), ('Dupond'), ('Dupon'), ('Dupuis'); ``` Toutes les chaînes commençant par la suite de caractères `Dur` : **Exemple 1-39.** Exemple 1\-39 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM motif WHERE chaine LIKE 'Dur%'; ``` **Tableau 1-9.** Tableau 1\-9 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text chaine -------- Durand ``` Toutes les chaînes terminant par `d` : **Exemple 1-40.** Exemple 1\-40 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM motif WHERE chaine LIKE '%d'; ``` **Tableau 1-10.** Tableau 1\-10 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text chaine -------- Durand Dupond ``` Toutes les chaînes qui commencent par `Dupon` suivi d’un caractère inconnu. La chaîne `Dupon` devrait être ignorée : **Exemple 1-41.** Exemple 1\-41 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM motif WHERE chaine LIKE 'Dupon_'; chaine ------------ Dupont Dupond ``` ### Listes et intervalles - Liste de valeurs **Exemple 1-42.** Exemple 1\-42 \- code ou sortie extraite du manuel DEV1\. ```sql expression IN (valeur1 [, …]) ``` - Chevauchement d’intervalle de valeurs **Exemple 1-43.** Exemple 1\-43 \- code ou sortie extraite du manuel DEV1\. ```sql expression BETWEEN expression AND expression ``` - Chevauchement d’intervalle de dates **Exemple 1-44.** Exemple 1\-44 \- code ou sortie extraite du manuel DEV1\. ```sql (date1, date2) OVERLAPS (date3, date4) ``` La clause `IN` permet de vérifier que l’expression de gauche est égale à une valeur présente dans l’expression de droite, qui est une liste d’expressions. La négation peut être utilisée en utilisant la construction `NOT IN`. L’opérateur `BETWEEN` permet de vérifier que la valeur d’une expression est comprise entre deux bornes. Par exemple, l’expression `valeur BETWEEN 1 AND 10` revient à exprimer la condition suivante : `valeur >= 1 AND valeur<= 10`. La négation peut être utilisée en utilisant la construction `NOT BETWEEN`. **Exemples** Recherche les chaînes qui sont présentes dans la liste `IN` : **Exemple 1-45.** Exemple 1\-45 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM motif WHERE chaine IN ('Dupont', 'Dupond', 'Ducobu'); ``` **Tableau 1-11.** Tableau 1\-11 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text chaine -------- Dupont Dupond ``` ### Tris - SQL ne garantit pas l’ordre des résultats ! - tri explicite requis - Tris des lignes selon des expressions **Exemple 1-46.** Exemple 1\-46 \- code ou sortie extraite du manuel DEV1\. ```sql ORDER BY expression [ ASC | DESC | USING opérateur ] [ NULLS { FIRST | LAST } ] [, …] ``` - Ordre du tri : `ASC` ou `DESC` - placement des valeurs `NULL` : `NULLS FIRST` ou `NULLS LAST` - ordre de tri des caractères : `COLLATE collation` La clause `ORDER BY` permet de trier les lignes du résultat d’une requête selon une ou plusieurs expressions combinées. **Attention** Sans clause `ORDER BY`, aucun ordre des lignes n’est garanti ! PostgreSQL les renverra dans l’ordre où il les trouvera, ce qui dépend de la méthode choisie par l’optimiseur, des emplacements physiques sur le disques et des manipulations nécessaires, et cet ordre peut varier entre deux exécutions ! L’expression de tri la plus simple est le nom d’une colonne. Dans ce cas, les lignes seront triées selon les valeurs de la colonne indiquée, et par défaut dans l’ordre ascendant, c’est-à-dire de la valeur la plus petite à la plus grande pour une donnée numérique ou temporelle, et dans l’ordre alphabétique pour une donnée textuelle. Les lignes peuvent être triées selon une expression très complexe, par exemple avec une fonction d’un calcul sur plusieurs colonnes. L’ordre de tri peut être inversé à l’aide de la clause `DESC` qui permet un tri dans l’ordre descendant, donc de la valeur la plus grande à la plus petite (ou alphabétique inverse le cas échéant). La clause `NULLS` permet de contrôler l’ordre d’apparition des valeurs `NULL`. La clause `NULLS FIRST` permet de faire apparaître d’abord les valeurs `NULL` puis les valeurs non `NULL` selon l’ordre de tri. La clause `NULLS LAST` permet de faire apparaître d’abord les valeurs non `NULL` selon l’ordre de tri suivies par les valeurs NULL. Si cette clause n’est pas précisée, alors PostgreSQL utilise implicitement `NULLS LAST` dans le cas d’un tri ascendant (`ASC`, par défaut) ou `NULLS FIRST` dans le cas d’un tri descendant (`DESC`, par défaut). **Exemples** Tri de la table `region` selon le nom de la région : **Exemple 1-47.** Exemple 1\-47 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM region ORDER BY libelle; ``` Tri de la table `stock` selon le nombre de bouteille, dans l’ordre décroissant : **Exemple 1-48.** Exemple 1\-48 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM stock ORDER BY nombre DESC; ``` Enfin, la clause `COLLATE` permet d’influencer sur l’ordre de tri des chaînes de caractères. ### Limiter le résultat - Obtenir des résultats à partir de la ligne n - `OFFSET n` - Limiter le nombre de lignes à n lignes - `FETCH {FIRST | NEXT} n ROWS ONLY` - `LIMIT n` - Opérations combinables - `OFFSET` doit apparaitre avant `FETCH` - Peu d’intérêt sur des résultats non triés La clause `OFFSET` permet d’exclure les `n` premières lignes du résultat. Toutes les autres lignes sont ramenées. La clause `FETCH` permet de limiter le résultat d’une requête. La requête retournera au maximum `n` lignes de résultats. Elle en retournera moins, voire aucune, si la requête ne peut ramener suffisamment de lignes. La clause `FIRST` ou `NEXT` est obligatoire, mais le choix de l’une ou l’autre n’a aucune conséquence sur le résultat. La clause `FETCH` est synonyme de la clause `LIMIT`. Mais `LIMIT` est une clause propre à PostgreSQL et quelques autres SGBD. Il est recommandé d’utiliser `FETCH` pour se conformer au standard. Ces deux opérations peuvent être combinées. La norme impose de faire apparaître la clause `OFFSET` avant la clause `FETCH`. PostgreSQL permet néanmoins d’exprimer ces clauses dans un ordre différent. Il faut faire attention au fait que ces fonctions ne permettent pas d’obtenir des résultats stables si les données ne sont pas triées explicitement. Rappelons que le standard SQL ne garantit en aucune façon l’ordre des résultats à moins d’employer la clause `ORDER BY`. **Exemples** La fonction `generate_series()` permet de générer une suite de valeurs numériques. Par exemple, une suite comprise entre 1 et 10 : **Exemple 1-49.** Exemple 1\-49 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM generate_series(1, 10); ``` **Tableau 1-12.** Tableau 1\-12 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text generate_series ----------------- 1 (…) 10 (10 rows) ``` La clause `FETCH` permet donc de limiter le nombre de lignes du résultats : **Exemple 1-50.** Exemple 1\-50 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM generate_series(1, 10) FETCH FIRST 5 ROWS ONLY; ``` **Tableau 1-13.** Tableau 1\-13 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text generate_series ----------------- 1 2 3 4 5 (5 rows) ``` La clause `LIMIT` donne un résultat équivalent : **Exemple 1-51.** Exemple 1\-51 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM generate_series(1, 10) LIMIT 5; ``` **Tableau 1-14.** Tableau 1\-14 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text generate_series ----------------- 1 2 3 4 5 (5 rows) ``` La clause `OFFSET 4` permet d’exclure les quatre premières lignes et de retourner les autres lignes du résultat : **Exemple 1-52.** Exemple 1\-52 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM generate_series(1, 10) OFFSET 4; ``` **Tableau 1-15.** Tableau 1\-15 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text generate_series ----------------- 5 6 7 8 9 10 (6 rows) ``` Les clauses `LIMIT` et `OFFSET` peuvent être combinées pour ramener les deux lignes en excluant les quatre premières : **Exemple 1-53.** Exemple 1\-53 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM generate_series(1, 10) OFFSET 4 LIMIT 2; ``` **Tableau 1-16.** Tableau 1\-16 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text generate_series ----------------- 5 6 (2 rows) ``` ### Utiliser plusieurs tables - Clause `FROM` - liste de tables séparées par `,` - Une table est combinée avec une autre - jointure - produit cartésien Il est possible d’utiliser plusieurs tables dans une requête `SELECT`. Lorsque c’est le cas, et sauf cas particulier, on fera correspondre les lignes d’une table avec les lignes d’une autre table selon certains critères. Cette mise en correspondance s’appelle une jointure et les critères de correspondances s’appellent une condition de jointure. Si aucune condition de jointure n’est donnée, chaque ligne de la première table est mise en correspondance avec toutes les lignes de la seconde table. C’est un produit cartésien. En général, un produit cartésien n’est pas souhaitable et est généralement le résultat d’une erreur de conception de la requête. **Exemples** Création d’un jeu de données simple : **Exemple 1-54.** Exemple 1\-54 \- code ou sortie extraite du manuel DEV1\. ```sql CREATE TABLE mere (id integer PRIMARY KEY, val_mere text); CREATE TABLE fille ( id_fille integer PRIMARY KEY, id_mere integer REFERENCES mere(id), val_fille text ); INSERT INTO mere (id, val_mere) VALUES (1, 'mere 1'); INSERT INTO mere (id, val_mere) VALUES (2, 'mere 2'); INSERT INTO fille (id_fille, id_mere, val_fille) VALUES (1, 1, 'fille 1'); INSERT INTO fille (id_fille, id_mere, val_fille) VALUES (2, 1, 'fille 2'); ``` Pour procéder à une jointure entre les tables `mere` et `fille`, les identifiants `id_mere` de la table `fille` doivent correspondre avec les identifiants `id` de la table `mere` : **Exemple 1-55.** Exemple 1\-55 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM mere, fille WHERE mere.id = fille.id_mere; ``` **Tableau 1-17.** Tableau 1\-17 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text id | val_mere | id_fille | id_mere | val_fille ----+----------+----------+---------+----------- 1 | mere 1 | 1 | 1 | fille 1 1 | mere 1 | 2 | 1 | fille 2 (2 rows) ``` Un produit cartésien est créé en omettant la condition de jointure, le résultat n’a plus de sens : **Exemple 1-56.** Exemple 1\-56 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM mere, fille; ``` **Tableau 1-18.** Tableau 1\-18 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text id | val_mere | id_fille | id_mere | val_fille ----+----------+----------+---------+----------- 1 | mere 1 | 1 | 1 | fille 1 1 | mere 1 | 2 | 1 | fille 2 2 | mere 2 | 1 | 1 | fille 1 2 | mere 2 | 2 | 1 | fille 2 (4 rows) ``` ## Types de données - Type de données - du standard SQL - certains spécifiques PostgreSQL PostgreSQL propose l’ensemble des types de données du standard SQL, à l’exception du type `BLOB` qui a toutefois un équivalent. Mais PostgreSQL a été conçu pour être extensible et permet de créer facilement des types de données spécifiques. C’est pourquoi PostgreSQL propose un certain nombre de types de données spécifiques qui peuvent être intéressants. ### Qu’est-ce qu’un type de données ? - Le système de typage valide les données - Un type détermine - les valeurs possibles - comment les données sont stockées - les opérations que l’on peut appliquer On utilise des types de données pour représenter une information de manière contrainte et cohérente. Les valeurs possibles d’une donnée vont dépendre de son type. Un entier long ne permet par exemple pas de coder des valeurs décimales. De la même façon, un type entier ne permet pas de représenter une chaîne de caractère, mais l’inverse est possible. L’intérêt du typage des données est qu’il permet également à la base de données de valider les données manipulées. Ainsi un entier (`integer`) permet de représenter des valeurs comprises entre -2 147 483 648 et 2 147 483 647. Si l’utilisateur tente d’insérer une donnée qui dépasse les capacités de ce type de données, une erreur lui sera retournée. On retrouve ainsi la notion d’intégrité des données. Comme pour les langages de programmation fortement typés, cela permet de détecter davantage d’erreurs, plus tôt : à la compilation dans les langages typés, ou ici des la première exécution d’une requête, plutôt que plus tard, quand une chaîne de caractère ne pourra pas être convertie à la volée en entier par exemple. Le choix d’un type de données va également influencer la façon dont les données sont représentées. En effet, toute donnée a une représentation textuelle, une représentation en mémoire et sur disque. Ainsi, un `integer` est représenté en interne sous la forme d’une suite de 4 octets, manipulables directement par le processeur, alors que sa représentation textuelle est une suite de caractères. Cela a une implication forte sur les performances de la base de données. Le type de données choisi permet également de déterminer les opérations que l’on pourra appliquer. Tous les types de données permettent d’utiliser des opérateurs qui leur sont propres. Ainsi il est possible d’additionner des entiers, de concaténer des chaînes de caractères, etc. Si une opération ne peut être réalisée nativement sur le type de données, il faudra utiliser des conversions coûteuses. Vaut-il mieux additionner deux entiers issus d’une conversion d’une chaîne de caractère vers un entier ou additionner directement deux entiers ? Vaut-il mieux stocker une adresse IP avec un `varchar` ou avec un type de données dédié ? Il est à noter que l’utilisateur peut contrôler lui-même certains types de données paramétrés. Le paramètre représente la longueur ou la précision du type de données. Ainsi, un type `varchar(15)` permettra de représenter des chaînes de caractères de 15 caractères maximum. ### Types de données - Types standards SQL - Types dérivés - Types spécifiques à PostgreSQL - Types utilisateurs Les types de données standards permettent de traiter la plupart des situations qui peuvent survenir. Dans certains cas, il peut être nécessaire de faire appel aux types spécifiques à PostgreSQL, par exemple pour stocker des adresses IP avec le type spécifique et bénéficier par la même occasion de toutes les classes d’opérateurs qui permettent de manipuler simplement ce type de données. Et si cela ne s’avère pas suffisant, PostgreSQL permet à l’utilisateur de créer lui-même ses propres types de données, ainsi que les classes d’opérateurs et fonctions permettant d’indexer ces données. ### Types standards (1) - Caractère - `char`, `varchar` - Numérique - `integer`, `smallint`, `bigint` - `real`, `double precision`, `float` - `numeric`, `decimal` - Booléen - `boolean` Le standard SQL propose des types standards pour stocker des chaînes de caractères (de taille fixe ou variable), des données numériques (entières, à virgule flottante) et des booléens. ### Types standards (2) - Temporel - `date`, `time` - `timestamp` - `interval` - Chaînes de bit - `bit`, `bit varying` - Formats validés - JSON - XML La norme propose également des types standards pour stocker des éléments temporels (date, heure, la combinaison des deux avec ou sans fuseau horaire, intervalle). D’utilisation plus rare, SQL permet également de stocker des chaînes de bit et des données validées au format XML. Le format JSON est de plus en plus courant. ### Caractères - `char(n)` - longueur fixe - de *n* caractères - complété à droite par des espaces si nécessaire - `varchar(n)` - longueur variable - maximum *n* caractères - *n* optionnel Le type `char(n)` permet de stocker des chaînes de caractères de taille fixe, donnée par l’argument `n`. Si la chaîne que l’on souhaite stocker est plus petite que la taille donnée à la déclaration de la colonne, elle sera complétée par des espaces à droite. Si la chaîne que l’on souhaite stocker est trop grande, une erreur sera levée. Le type `varchar(n)` permet de stocker des chaînes de caractères de taille variable. La taille maximale de la chaîne est donnée par l’argument `n`. Toute chaîne qui excédera cette taille ne sera pas prise en compte et générera une erreur. Les chaînes de taille inférieure à la taille limite seront stockées sans altérations. La longueur de chaîne est mesurée en nombre de *caractères* sous PostgreSQL. Ce n’est pas forcément le cas dans d’autres SGBD, qui peuvent compter en *octets*. En effet, de nombreux caractères peuvent nécessiter plusieurs octets. ### Représentation données caractères - Norme SQL - chaîne encadrée par `'` - `'chaîne de caractères'` - Chaînes avec échappement du style C - chaîne précédée par `E` ou `e` - `E'chaîne de caractères'` - Chaînes avec échappement Unicode - chaîne précédée par `U&` - `U&'chaîne de caractères'` La norme SQL définit que les chaînes de caractères sont représentées encadrées de guillemets simples (caractère `'`). Le guillemet double (caractère `"`) ne peut être utilisé car il sert à protéger la casse des noms d’objets. PostgreSQL interprétera alors la chaîne comme un nom d’objet et générera une erreur. Une représentation correcte d’une chaîne de caractères est donc de la forme suivante : **Exemple 1-57.** Exemple 1\-57 \- code ou sortie extraite du manuel DEV1\. ```sql 'chaîne de caractères' ``` Les caractères `'` doivent être doublés s’ils apparaissent dans la chaîne : **Exemple 1-58.** Exemple 1\-58 \- code ou sortie extraite du manuel DEV1\. ```sql 'J''ai acheté des croissants' ``` Une extension de la norme par PostgreSQL permet d’utiliser les méta-caractères des langages tels que le C, par exemple `\n` pour un retour de ligne, `\t` pour une tabulation, etc. : **Exemple 1-59.** Exemple 1\-59 \- code ou sortie extraite du manuel DEV1\. ```sql E'chaîne avec un retour \nde ligne et une \ttabulation' ``` ### Numériques - Entier - `smallint`, `integer`, `bigint` - signés - Virgule flottante - `real`, `double precision` - valeurs inexactes - Précision arbitraire - `numeric(precision, echelle)`, `decimal(precision, echelle)` - valeurs exactes Le standard SQL propose des types spécifiques pour stocker des entiers signés. Le type `smallint` permet de stocker des valeurs codées sur 2 octets, soit des valeurs comprises entre -32 768 et +32 767. Le type `integer` ou `int`, codé sur 4 octets, permet de stocker des valeurs comprises entre -2 147 483 648 et +2 147 483 647. Enfin, le type `bigint`, codé sur 8 octets, permet de stocker des valeurs comprises entre -9 223 372 036 854 775 808 et 9 223 372 036 854 775 807. Le standard SQL ne propose pas de stockage d’entiers non signés. Le standard SQL permet de stocker des valeurs décimales en utilisant les types à virgules flottantes. Avant de les utiliser, il faut avoir à l’esprit que ces types de données ne permettent pas de stocker des valeurs exactes, des différences peuvent donc apparaître entre la donnée insérée et la donnée restituée. Le type `real` permet d’exprimer des valeurs à virgules flottantes sur 4 octets, avec une précision relative de six décimales. Le type `double precision` permet d’exprimer des valeurs à virgules flottantes sur huit octets, avec une précision relative de 15 décimales. Beaucoup d’applications, notamment les applications financières, ne se satisfont pas de valeurs inexactes. Pour cela, le standard SQL propose le type `numeric`, ou son synonyme `decimal`, qui permet de stocker des valeurs exactes, selon la précision arbitraire donnée. Dans la déclaration `numeric(precision, echelle)`, la partie `precision` indique combien de chiffres significatifs sont stockés, la partie `echelle` exprime le nombre de chiffres après la virgule. Au niveau du stockage, PostgreSQL ne permet pas d’insérer des valeurs qui dépassent les capacités du type déclaré. En revanche, si l’échelle de la valeur à stocker dépasse l’échelle déclarée de la colonne, alors sa valeur est simplement arrondie. On peut aussi utiliser `numeric` sans aucune contrainte de taille, pour stocker de façon exacte n’importe quel nombre. ### Représentation de données numériques - Chiffres décimaux : 0 à 9 - Séparateur décimal : `.` - Hexadécimal, octal, binaire possibles - Exemples : - `42` - `3.14159`, `0.5`, `-.005` - `1.0e6`, `-314e-2` - `1_000_000` (v16+) - `0xFF`, `0o377`, `0b11111111` - Conversion de type : **Exemple 1-60.** Exemple 1\-60 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT REAL '1.23', cast ('1.23' AS real), '1.23'::float4 ; ``` Au moins un chiffre doit être placé avant ou après le point décimal, s’il est utilisé. Au moins un chiffre doit suivre l’indicateur d’exponentiel (caractère `e`), s’il est présent. Il peut ne pas y avoir d’espaces ou d’autres caractères imbriqués dans la constante. Notez que tout signe `+` ou `-` en avant n’est pas forcément considéré comme faisant partie de la constante ; il est un opérateur appliqué à la constante. PostgreSQL accepte des `_` intercalaires pour la lisibilité depuis sa version 16. Une constante numérique contenant soit un point décimal soit un exposant est tout d’abord présumée du type `integer` si sa valeur est contenue dans le type integer (4 octets). Dans le cas contraire, il est présumé de type `bigint` si sa valeur entre dans un type `bigint` (8 octets). Dans le cas contraire, il est pris pour un type `numeric`. Les constantes contenant des points décimaux et/ou des exposants sont toujours présumées de type `numeric`. Des notations hexadécimales, octales, binaires sont possibles : **Exemple 1-61.** Exemple 1\-61 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT 0xFF AS hexa, 0o377 AS octal, 0b1111_1111 AS binaire ; ``` **Tableau 1-19.** Tableau 1\-19 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text hexa | octal | binaire ------+-------+--------- 255 | 255 | 255 ``` Le type de données affecté initialement à une constante numérique est seulement un point de départ pour les algorithmes de résolution de types. Dans la plupart des cas, la constante sera automatiquement convertie dans le type le plus approprié suivant le contexte. Si nécessaire, vous pouvez forcer l’interprétation d’une valeur numérique sur un type de données spécifiques en la convertissant. Par exemple, vous pouvez forcer une valeur numérique à être traitée comme un type `real` (`float4`) de plusieurs manières différentes : **Exemple 1-62.** Exemple 1\-62 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT '1.23', 1.23 , REAL '1.23', cast ('1.23' AS real), '1.23'::float4 \gdesc ``` **Tableau 1-20.** Tableau 1\-20 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text Column | Type ----------+--------- ?column? | text ?column? | numeric float4 | real float4 | real float4 | real ``` La première syntaxe ne fonctionne que pour des constantes. La syntaxe `::` se rencontre fréquemment et est très pratique, mais est propre à PostgreSQL. La norme SQL conseille l’opérateur `cast`. - Documentation officielle : - [Conversions de type](https://docs.postgresql.fr/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS) - [Constantes d’autres types](https://docs.postgresql.fr/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC) ### Booléens - `boolean` - 3 valeurs possibles - `TRUE` - `FALSE` - `NULL` (valeur absente/non pertinente) Le type `boolean` permet d’exprimer des valeurs booléennes, c’est-à-dire une valeur exprimant vrai ou faux. Comme tous les types de données en SQL, une colonne booléenne peut aussi ne pas avoir de valeur, auquel cas sa valeur sera `NULL`. Un des intérêts des types booléens est de pouvoir écrire : **Exemple 1-63.** Exemple 1\-63 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM ma_table WHERE valide; SELECT * FROM ma_table WHERE NOT consulte; ``` ### Temporel - Date - `date` - Heure - `time` - avec ou sans fuseau horaire - Date et heure - `timestamp` - avec ou sans fuseau horaire - Intervalle de temps - `interval` Le type `date` exprime une date. Ce type ne connaît pas la notion de fuseau horaire. Le type `time` exprime une heure. Par défaut, il ne connaît pas la notion de fuseau horaire. En revanche, lorsque le type est déclaré comme `time with time zone`, il prend en compte un fuseau horaire. Mais cet emploi n’est pas recommandé. En effet, une heure convertie d’un fuseau horaire vers un autre pose de nombreux problèmes. En effet, le décalage horaire dépend également de la date : quand il est 6 h 00, heure d’été, à Paris, il est 21 h 00 sur la côte Pacifique aux États-Unis, mais encore à la date de la veille. Le type `timestamp` permet d’exprimer une date et une heure. Par défaut, il ne connaît pas la notion de fuseau horaire. Lorsque le type est déclaré `timestamp with time zone`, il est adapté aux conversions d’heure d’un fuseau horaire vers un autre car le changement de date sera répercuté dans la composante date du type de données. Il est précis à la microseconde. Le format de saisie et de restitution des dates et heures dépend du paramètre `DateStyle`. La documentation de ce paramètre permet de connaître les différentes valeurs possibles. Il reste néanmoins recommandé d’utiliser les fonctions de formatage de date qui permettent de rendre l’application indépendante de la configuration du SGBD. La norme ISO (ISO-8601) impose le format de date « année-mois-jour ». La norme SQL est plus permissive et permet de restituer une date au format « jour/mois/année » si DateStyle est égal à `'SQL, DMY'`. **Exemple 1-64.** Exemple 1\-64 \- code ou sortie extraite du manuel DEV1\. ```sql SET datestyle = 'ISO, DMY'; SELECT current_timestamp; ``` **Tableau 1-21.** Tableau 1\-21 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text now ------------------------------- 2017-08-29 16:11:58.290174+02 ``` **Exemple 1-65.** Exemple 1\-65 \- code ou sortie extraite du manuel DEV1\. ```sql SET datestyle = 'SQL, DMY'; SELECT current_timestamp; ``` **Tableau 1-22.** Tableau 1\-22 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text now -------------------------------- 29/08/2017 16:12:25.650716 CEST ``` ### Représentation des données temporelles - Conversion explicite - `TYPE 'chaine'` - Format d’un timestamp - `'YYYY-MM-DD HH24:MI:SS.ssssss'` - `'YYYY-MM-DD HH24:MI:SS.ssssss+fuseau'` - `'YYYY-MM-DD HH24:MI:SS.ssssss' AT TIME ZONE 'fuseau'` - Format d’un intervalle - `INTERVAL 'durée interval'` Expression d’une date, forcément sans gestion du fuseau horaire : **Exemple 1-66.** Exemple 1\-66 \- code ou sortie extraite du manuel DEV1\. ```sql DATE '2017-08-29' ``` Expression d’une heure sans fuseau horaire : **Exemple 1-67.** Exemple 1\-67 \- code ou sortie extraite du manuel DEV1\. ```sql TIME '10:20:10' ``` Ou, en spécifiant explicitement l’absence de fuseau horaire : **Exemple 1-68.** Exemple 1\-68 \- code ou sortie extraite du manuel DEV1\. ```sql TIME WITHOUT TIME ZONE '10:20:10' ``` Expression d’une heure, avec fuseau horaire invariant. Cette forme est déconseillée : **Exemple 1-69.** Exemple 1\-69 \- code ou sortie extraite du manuel DEV1\. ```sql TIME WITH TIME ZONE '10:20:10' AT TIME ZONE 'CEST' ``` Expression d’un timestamp sans fuseau horaire : **Exemple 1-70.** Exemple 1\-70 \- code ou sortie extraite du manuel DEV1\. ```sql TIMESTAMP '2017-08-29 10:20:10' ``` Ou, en spécifiant explicitement l’absence de fuseau horaire : **Exemple 1-71.** Exemple 1\-71 \- code ou sortie extraite du manuel DEV1\. ```sql TIMESTAMP WITHOUT TIME ZONE '2017-08-29 10:20:10' ``` Expression d’un timestamp avec fuseau horaire, avec microseconde : **Exemple 1-72.** Exemple 1\-72 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT TIMESTAMP WITH TIME ZONE '2017-08-29 10:20:10.123321' AT TIME ZONE 'Europe/Paris' ; ``` **Tableau 1-23.** Tableau 1\-23 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text timezone ---------------------------- 2017-08-29 10:20:10.123321 ``` Expression d’un intervalle d’une journée : **Exemple 1-73.** Exemple 1\-73 \- code ou sortie extraite du manuel DEV1\. ```sql INTERVAL '1 day' ``` Il est possible de cumuler plusieurs expressions : **Exemple 1-74.** Exemple 1\-74 \- code ou sortie extraite du manuel DEV1\. ```sql INTERVAL '1 year 1 day' ``` Les valeurs possibles sont : - `YEAR` pour une année ; - `MONTH` pour un mois ; - `DAY` pour une journée ; - `HOUR` pour une heure ; - `MINUTE` pour une minute ; - `SECOND` pour une seconde. ### Gestion des fuseaux horaires - Paramètre `timezone` - Session : `SET TIME ZONE` - Expression d’un fuseau horaire - nom complet : `'Europe/Paris'` - nom abrégé : `'CEST'` - décalage : `'+02'` Le paramètre `timezone` du fichier de configuration `postgresql.conf` permet de positionner le fuseau horaire de l’instance PostgreSQL. Elle est initialisée par défaut en fonction de l’environnement du système d’exploitation. Le fuseau horaire de l’instance peut également être défini au cours de la session à l’aide de la commande `SET TIME ZONE`. La France métropolitaine utilise deux fuseaux horaires normalisés. Le premier, `CET`, correspond à *Central European Time* ou autrement dit à l’heure d’hiver en Europe centrale. Le second, `CEST`, correspond à *Central European Summer Time*, c’est-à-dire l’heure d’été en Europe centrale. La liste des fuseaux horaires supportés est disponible dans la table système `pg_timezone_names` : **Exemple 1-75.** Exemple 1\-75 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM pg_timezone_names ; ``` **Tableau 1-24.** Tableau 1\-24 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text name | abbrev | utc_offset | is_dst ----------------------------------+--------+------------+-------- GB | BST | 01:00:00 | t ROK | KST | 09:00:00 | f Greenwich | GMT | 00:00:00 | f (…) ``` Il est possible de positionner le fuseau horaire au niveau de la session avec l’ordre `SET TIME ZONE` : **Exemple 1-76.** Exemple 1\-76 \- code ou sortie extraite du manuel DEV1\. ```sql SET TIME ZONE "Europe/Paris"; SELECT now(); ``` **Tableau 1-25.** Tableau 1\-25 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text now ------------------------------- 2017-08-29 10:19:56.640162+02 ``` **Exemple 1-77.** Exemple 1\-77 \- code ou sortie extraite du manuel DEV1\. ```sql SET TIME ZONE "Europe/Kiev"; SELECT now(); ``` **Tableau 1-26.** Tableau 1\-26 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text now ------------------------------- 2017-08-29 11:20:17.199983+03 ``` Conversion implicite d’une donnée de type `timestamp` dans le fuseau horaire courant : **Exemple 1-78.** Exemple 1\-78 \- code ou sortie extraite du manuel DEV1\. ```sql SET TIME ZONE "Europe/Kiev"; SELECT TIMESTAMP WITH TIME ZONE '2017-08-29 10:20:10 CEST'; ``` **Tableau 1-27.** Tableau 1\-27 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text timestamptz ------------------------ 2017-08-29 11:20:10+03 ``` ou encore (`AT LOCAL TIME` est disponible depuis PostgreSQL 17) : **Exemple 1-79.** Exemple 1\-79 \- code ou sortie extraite du manuel DEV1\. ```sql SHOW timezone; ``` **Tableau 1-28.** Tableau 1\-28 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text TimeZone -------------- Europe/Paris ``` **Exemple 1-80.** Exemple 1\-80 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT '2024-11-26 00:00:00 +11' AT LOCAL TIME ; -- minuit à Nouméas ``` **Tableau 1-29.** Tableau 1\-29 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text time --------------------- 2024-11-26 01:00:00 ``` Conversion explicite d’une donnée de type `timestamp` dans un autre fuseau horaire : **Exemple 1-81.** Exemple 1\-81 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT '2017-08-29 06:00:00' AT TIME ZONE 'US/Pacific'; ``` **Tableau 1-30.** Tableau 1\-30 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text timezone --------------------- 28/08/2017 21:00:00 ``` ou encore : **Exemple 1-82.** Exemple 1\-82 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT '2023-10-23 6:30:00+02'::timestamptz AT TIME ZONE 'Europe/London'; ``` **Tableau 1-31.** Tableau 1\-31 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text timezone --------------------- 2023-10-23 05:30:00 ``` ### Chaînes de bits - Chaînes de bits - `bit(n)`, `bit varying(n)` Les types `bit` et `bit varying` permettent de stocker des masques de bits. Le type `bit(n)` est à longueur fixe alors que le type `bit varying(n)` est à longueur variable mais avec un maximum de `n` bits. ### Représentation des chaînes de bits - Représentation binaire - chaîne de caractères précédée de la lettre `B` - `B'01010101'` - Représentation hexadécimale - chaîne de caractères précédée de la lettre `X` - `X'55'` ### XML - Type validé - `xml` - Chaîne de caractères - validation du document XML Le type `xml` permet de stocker des documents XML. Par rapport à une chaîne de caractères simple, le type `xml` apporte la vérification de la structure du document XML ainsi que des fonctions de manipulations spécifiques (voir [la documentation officielle](https://docs.postgresql.fr/current/functions-xml.html)). ### JSON - Type `json` : texte, avec validation du format JSON - Préférer le type `jsonb` (binaire) - Fonctions de manipulation Les types `json` et `jsonb` permettent de stocker des documents JSON. Ces deux types permettent de vérifier la structure du document JSON ainsi que des fonctions de manipulations spécifiques (voir [la documentation officielle](https://docs.postgresql.fr/current/functions-json.html)). Sous PostgreSQL, on préférera de loin le type `jsonb` pour son stockage optimisé (en binaire), et ses fonctionnalités supplémentaires, notamment en terme d’indexation. ### Types dérivés - Types spécifiques à PostgreSQL - Incrémentés: - principe de l’« autoincrément » - `serial` - `smallserial` - `bigserial` - équivalent à un type entier associé à une séquence et avec une valeur par défaut - préférer un type entier + la propriété `IDENTITY` - Caractères - `text` Les types `smallserial`, `serial` et `bigserial` permettent d’obtenir des fonctionnalités similaires aux types `autoincrement` rencontrés dans d’autres SGBD. Néanmoins, ces types restent assez proches de la norme car ils définissent au final une colonne qui utilise un type et des objets standards. Selon le type dérivé utilisé, la colonne sera de type `smallint`, `integer` ou `bigint`. Une séquence sera également créée et la colonne prendra pour valeur par défaut la prochaine valeur de cette séquence. Il est cependant préférable de passer par un type `IDENTITY` que par ces types dérivés. **Attention** Attention : ces types n’interdisent pas l’insertion manuelle de doublons. Ajouter une contrainte de clé primaire explicite reste nécessaire pour les éviter. Le type `text` est l’équivalent du type `varchar` mais sans limite de taille de la chaîne de caractère. ### Types additionnels hors standard SQL - `bytea` - `array` - `enum` - `cidr`, `inet`, `macaddr` - `uuid` - `json`, `jsonb`, `hstore` - `range` Les types standards ne sont pas toujours suffisants pour représenter certaines données. Comme tous ses concurrents, PostgreSQL propose des types de données supplémentaires pour répondre à certains besoins. On notera le type `bytea` qui permet de stocker des objets binaires dans une table. Le type `array` permet de stocker des tableaux (de nombres, de dates, de chaînes…) et `enum` des énumérations. Les types `json` et `hstore` permettent de stocker des documents non structurés dans la base de données. Le premier au format JSON, le second dans un format de type clé/valeur. Le type `json` a été complété par `jsonb` qui permet de stocker un document JSON binaire et optimisé, et d’accéder à une propriété sans désérialiser intégralement le document. Le type `hstore` est un type clé/valeur qui par rapport à JSON a l’intérêt de la simplicité. Le type `range` permet de stocker des intervalles de données. Ces données sont ensuite manipulables par un jeu d’opérateurs dédiés et par le biais de méthodes d’indexation permettant d’accélérer les recherches. ### Types utilisateurs - Types utilisateurs - composites - énumérés (`enum`) - intervalles (`range`) - scalaires - tableau ```sql CREATE TYPE ``` PostgreSQL permet de créer ses propres types de données. Les usages les plus courants consistent à créer des types composites pour permettre à des fonctions de retourner des données sous forme tabulaire (retour de type `SETOF`). L’utilisation du type énuméré (`enum`) nécessite aussi la création d’un type spécifique. Le type sera alors employé pour déclarer les objets utilisant une énumération. Enfin, si l’on souhaite étendre les types intervalles (`range`) déjà disponibles, il est nécessaire de créer un type spécifique. La création d’un type scalaire est bien plus marginale. Elle permet en effet d’étendre les types fournis par PostgreSQL mais nécessite d’avoir des connaissances fines des mécanismes de PostgreSQL. De plus, dans la majeure partie des cas, les types standards suffisent en général à résoudre les problèmes qui peuvent se poser à la conception. Quant aux types tableaux, ils sont créés implicitement par PostgreSQL quand un utilisateur crée un type personnalisé. **Exemples** Utilisation d’un type `enum` : **Exemple 1-83.** Exemple 1\-83 \- code ou sortie extraite du manuel DEV1\. ```sql CREATE TYPE arc_en_ciel AS ENUM ( 'red', 'orange', 'yellow', 'green', 'blue', 'purple' ); CREATE TABLE test (id integer, couleur arc_en_ciel); INSERT INTO test (id, couleur) VALUES (1, 'red'); INSERT INTO test (id, couleur) VALUES (2, 'pink'); ERROR: invalid input value for enum arc_en_ciel: "pink" LINE 1: INSERT INTO test (id, couleur) VALUES (2, 'pink'); ``` Création d’un type interval `float8_range` : **Exemple 1-84.** Exemple 1\-84 \- code ou sortie extraite du manuel DEV1\. ```sql CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi); ``` ## Conclusion - Modèle relationnel : extrêmement efficace - SQL : traitement d’ensembles d’enregistrements - Pour les lectures : `SELECT` - Nom des objets en minuscules - Des types de données simples et d’autres plus complexes Le standard SQL permet de traiter des ensembles d’enregistrements. Un enregistrement correspond à une ligne dans une relation. Il est possible de lire ces relations grâce à l’ordre `SELECT`. ### Bibliographie - *Bases de données - de la modélisation au SQL* (Laurent Audibert) - *SQL avancé : programmation et techniques avancées* (Joe Celko) - *SQL : Au coeur des performances* (Markus Winand) - *The Manga Guide to Databases* (Takahashi, Mana, Azuma, Shoko) - *The Art of SQL* (Stéphane Faroult) **Bases de données - de la modélisation au SQL** - Auteur : Laurent Audibert - Éditeur : Ellipses - ISBN : 978-2729851200 Ce livre présente les notions essentielles pour modéliser une base de données et utiliser le langage SQL pour utiliser les bases de données créées. L’auteur appuie ses exercices sur PostgreSQL. **SQL avancé : programmation et techniques avancées** - Auteur : Joe Celko - Editeur : Vuibert - ISBN : 978-2711786503 Ce livre est écrit par une personne ayant participé à l’élaboration du standard SQL. Il a souhaité montré les bonnes pratiques pour utiliser le SQL pour résoudre un certain nombre de problèmes de tous les jours. Le livre s’appuie cependant sur la norme SQL-92, voire SQL-89. L’édition anglaise *SQL for Smarties* est bien plus à jour. Pour les anglophones, la lecture de l’ensemble des livres de Joe Celko est particulièrement recommandée. **SQL : Au cœur des performances** - Auteur : Markus Winand - Éditeur : auto-édité - ISBN : 978-3950307832 - [site Internet](https://use-the-index-luke.com/fr) Il s’agit du livre de référence sur les performances en SQL. Il dresse un inventaire des différents cas d’utilisation des index par la base de données, ce qui permettra de mieux prévoir l’indexation dès la conception. Ce livre s’adresse à un public avancé. **The Manga Guide to Databases** - Auteur : Takahashi, Mana, Azuma, Shoko - Éditeur : No Starch Press - ASIN : B00BUFN70E **The Art of SQL** - Auteur : Stéphane Faroult - Éditeur : O’Reilly - ISBN : 978-0-596-00894-9 - ISBN : 978-0-596-15971-9 (e-book) Ce livre s’adresse également à un public avancé. Il présente également les bonnes pratiques lorsque l’on utilise une base de données. ### Questions N’hésitez pas, c’est le moment ! ## Quiz **** ## Travaux pratiques La version en ligne des solutions de ces TP est disponible sur . **Requêtes simples** : > Afficher l’heure courante, au méridien de Greenwich. > Afficher la date et l’heure qu’il sera dans 1 mois et 1 jour. > Extraire le mois courant et calculer le nombre de mois restant avant le mois de décembre. > Ajouter 1 au nombre de type réel ‘1.42’. Pourquoi ce résultat ? Quel type de données permet d’obtenir un résultat correct ? **Requêtes sur la base tpc** : Pour les questions suivantes, il faudra se connecter à la base de données **tpc**. Si vous ne l’avez pas déjà, la base **tpc** (dump de 31 Mo, pour 267 Mo sur le disque au final) et ses utilisateurs peuvent être installés comme suit : **Exemple 1-85.** Exemple 1\-85 \- code ou sortie extraite du manuel DEV1\. ```bash curl -kL https://dali.bo/tp_tpc -o /tmp/tpc.dump curl -kL https://dali.bo/tp_tpc_roles -o /tmp/tpc_roles.sql # Exécuter le script de création des rôles psql < /tmp/tpc_roles.sql # Création de la base createdb --owner tpc_owner tpc # L'erreur sur un schéma 'public' existant est normale pg_restore -d tpc /tmp/tpc.dump ``` Les mots de passe sont dans le script `/tmp/tpc_roles.sql`. Pour vous connecter : **Exemple 1-86.** Exemple 1\-86 \- code ou sortie extraite du manuel DEV1\. ```bash $ psql -U tpc_admin -h localhost -d tpc ``` Le schéma suivant montre les différentes tables de la base : **Figure 1-1.** Figure 1\-1 \- Schéma base tpc \(page PDF 65\)\. ![Schéma base tpc](/images/dev1/manual/dev1-p065-01.png) Avec `psql`, pour changer de base, vous pouvez lancer la méta-commande suivante: **Exemple 1-87.** Exemple 1\-87 \- code ou sortie extraite du manuel DEV1\. ```sql \c tpc ``` > Afficher le contenu de la table `pays` en classant les pays dans l’ordre alphabétique. > Afficher les pays contenant la lettre `a`, majuscule ou minuscule. Plusieurs solutions sont possibles. > Afficher le nombre lignes de commandes (table `lignes_commandes`) dont la quantité commandée est comprise entre 5 et 10. > Pour chaque pays, afficher son nom et la région du monde dont il fait partie. **Exemple 1-88.** Exemple 1\-88 \- code ou sortie extraite du manuel DEV1\. ```sh nom_pays | nom_region --------------------+----------- ALGÉRIE | Afrique (…) ``` > Afficher le nombre total de clients français et allemands. Sortie attendue : **Exemple 1-89.** Exemple 1\-89 \- code ou sortie extraite du manuel DEV1\. ```sh count ------- 12418 ``` > Afficher le numéro de commande et le nom du client ayant passé la commande. Seul un sous-ensemble des résultats sera affiché : les 20 premières lignes du résultat seront exclues et seules les 20 suivantes seront affichées. Il faut penser à ce que le résultat de cette requête soit stable entre plusieurs exécutions. Sortie attendue : **Exemple 1-90.** Exemple 1\-90 \- code ou sortie extraite du manuel DEV1\. ```sh numero_commande | nom_client -----------------+-------------- 67 | Client112078 68 | Client33842 (…) 132 | Client18152 ``` > Afficher les noms et codes des pays qui font partie de la région « Europe ». Sortie attendue : **Exemple 1-91.** Exemple 1\-91 \- code ou sortie extraite du manuel DEV1\. ```sh nom_pays | code_pays ----------------+----------- ALLEMAGNE | DE (…) ``` > Pour chaque pays, afficher une chaîne de caractères composée de son nom, suivi entre parenthèses de son code puis, séparé par une virgule, du nom de la région dont il fait partie. Sortie attendue : **Exemple 1-92.** Exemple 1\-92 \- code ou sortie extraite du manuel DEV1\. ```sh detail_pays ---------------------- ALGÉRIE (DZ), Afrique (…) ``` > Pour les clients ayant passé des commandes durant le mois de janvier 2011, affichez les identifiants des clients, leur nom, leur numéro de téléphone et le nom de leur pays. Sortie attendue : **Exemple 1-93.** Exemple 1\-93 \- code ou sortie extraite du manuel DEV1\. ```sh client_id | nom | telephone | nom_pays ----------+--------------+-----------------+------------------------------- 83279 | Client83279 | 12-835-574-2048 | JAPON ``` > Pour les dix premières commandes de l’année 2011, afficher son numéro, sa date ainsi que son âge. Sortie attendue : **Exemple 1-94.** Exemple 1\-94 \- code ou sortie extraite du manuel DEV1\. ```sh numero_commande | date_commande | age ----------------+---------------+--------------------------- 54274 | 2011-01-01 | 5336 days 10:09:01.6222 (…) ``` ## Travaux pratiques (solutions) **Requêtes simples** : > Afficher l’heure courante, au méridien de Greenwich. **Exemple 1-95.** Exemple 1\-95 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now() AT TIME ZONE 'GMT'; ``` > Afficher la date et l’heure qu’il sera dans 1 mois et 1 jour. **Exemple 1-96.** Exemple 1\-96 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now() + INTERVAL '1 month 1 day'; ``` > Extraire le mois courant et calculer le nombre de mois restant avant le mois de décembre. **Exemple 1-97.** Exemple 1\-97 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT 12 - EXTRACT( 'month' FROM now()); ``` > Ajouter 1 au nombre de type réel ‘1.42’. Pourquoi ce résultat ? Quel type de données permet d’obtenir un résultat correct ? **Exemple 1-98.** Exemple 1\-98 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT REAL '1.42' + 1 AS resultat; ``` **Tableau 1-32.** Tableau 1\-32 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text resultat ------------------ 2.41999995708466 (1 row) ``` Le type de données `real` est un type numérique à virgule flottante, codé sur 4 octets. Il n’offre pas une précision suffisante pour les calculs précis. Son seul avantage est la vitesse de calcul. Pour effectuer des calculs précis, il vaut mieux privilégier le type de données `numeric`. **Requêtes sur la base tpc** : Pour les questions suivantes, il faudra se connecter à la base de données **tpc**. Si vous ne l’avez pas déjà, la base **tpc** (dump de 31 Mo, pour 267 Mo sur le disque au final) et ses utilisateurs peuvent être installés comme suit : **Exemple 1-99.** Exemple 1\-99 \- code ou sortie extraite du manuel DEV1\. ```bash curl -kL https://dali.bo/tp_tpc -o /tmp/tpc.dump curl -kL https://dali.bo/tp_tpc_roles -o /tmp/tpc_roles.sql # Exécuter le script de création des rôles psql < /tmp/tpc_roles.sql # Création de la base createdb --owner tpc_owner tpc # L'erreur sur un schéma 'public' existant est normale pg_restore -d tpc /tmp/tpc.dump ``` Les mots de passe sont dans le script `/tmp/tpc_roles.sql`. Pour vous connecter : **Exemple 1-100.** Exemple 1\-100 \- code ou sortie extraite du manuel DEV1\. ```bash $ psql -U tpc_admin -h localhost -d tpc ``` Le schéma suivant montre les différentes tables de la base : **Figure 1-2.** Figure 1\-2 \- Schéma base tpc \(page PDF 69\)\. ![Schéma base tpc](/images/dev1/manual/dev1-p065-01.png) Avec `psql`, pour changer de base, vous pouvez lancer la méta-commande suivante: **Exemple 1-101.** Exemple 1\-101 \- code ou sortie extraite du manuel DEV1\. ```sql \c tpc ``` > Afficher le contenu de la table `pays` en classant les pays dans l’ordre alphabétique. **Exemple 1-102.** Exemple 1\-102 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM pays ORDER BY nom_pays; ``` > Afficher les pays contenant la lettre `a`, majuscule ou minuscule. Plusieurs solutions sont possibles. **Exemple 1-103.** Exemple 1\-103 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM pays WHERE lower(nom_pays) LIKE '%a%'; ``` **Exemple 1-104.** Exemple 1\-104 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM pays WHERE nom_pays ILIKE '%a%'; ``` **Exemple 1-105.** Exemple 1\-105 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM pays WHERE nom_pays LIKE '%a%' OR nom_pays LIKE '%A%'; ``` En terme de performances, la seconde variante sera plus rapide sur un volume de données important si l’on dispose du bon index. La taille de la table `pays` ne permet pas d’observer de différence significative sur cette requête. > Afficher le nombre lignes de commandes (table `lignes_commandes`) dont la quantité commandée est comprise entre 5 et 10. **Exemple 1-106.** Exemple 1\-106 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT count(*) FROM lignes_commandes WHERE quantite BETWEEN 5 AND 10; ``` Autre écriture possible : **Exemple 1-107.** Exemple 1\-107 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT count(*) FROM lignes_commandes WHERE quantite >= 5 AND quantite <= 10; ``` > Pour chaque pays, afficher son nom et la région du monde dont il fait partie. **Exemple 1-108.** Exemple 1\-108 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT nom_pays, nom_region FROM pays p, regions r WHERE p.region_id = r.region_id; ``` > Afficher le nombre total de clients français et allemands. **Exemple 1-109.** Exemple 1\-109 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT count(*) FROM clients cl, contacts cn, pays p WHERE cl.contact_id = cn.contact_id AND cn.code_pays = p.code_pays AND p.nom_pays IN ('FRANCE', 'ALLEMAGNE'); ``` À noter que cette syntaxe est obsolète, il faut utiliser la clause `JOIN`, plus lisible et plus complète, qui sera vue plus loin : **Exemple 1-110.** Exemple 1\-110 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT count(*) FROM clients cl JOIN contacts cn ON (cl.contact_id = cn.contact_id) JOIN pays p ON (cn.code_pays = p.code_pays) WHERE p.nom_pays IN ('FRANCE', 'ALLEMAGNE'); ``` En connaissant les codes de ces pays, il est possible d’éviter la lecture de la table `pays` : **Exemple 1-111.** Exemple 1\-111 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT count(*) FROM clients cl, contacts cn WHERE cl.contact_id = cn.contact_id AND cn.code_pays IN ('FR', 'DE'); ``` L’équivalent avec la syntaxe `JOIN` serait : **Exemple 1-112.** Exemple 1\-112 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT count(*) FROM clients cl JOIN contacts cn ON (cl.contact_id = cn.contact_id) WHERE cn.code_pays IN ('FR', 'DE'); ``` > Afficher le numéro de commande et le nom du client ayant passé la commande. Seul un sous-ensemble des résultats sera affiché : les 20 premières lignes du résultat seront exclues et seules les 20 suivantes seront affichées. Il faut penser à ce que le résultat de cette requête soit stable entre plusieurs exécutions. La syntaxe normalisée SQL impose d’écrire la requête de la façon suivante. La stabilité du résultat de la requête est garantie par un tri explicite, s’il n’est pas précisé, la base de données va retourner les lignes dans l’ordre physique qui est susceptible de changer entre deux exécutions : **Exemple 1-113.** Exemple 1\-113 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT numero_commande, nom AS nom_client FROM commandes cm, clients cl, contacts cn WHERE cm.client_id = cl.client_id AND cl.contact_id = cn.contact_id ORDER BY numero_commande FETCH FIRST 20 ROWS ONLY OFFSET 20; ``` Mais PostgreSQL supporte également la clause `LIMIT` : **Exemple 1-114.** Exemple 1\-114 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT numero_commande, nom AS nom_client FROM commandes cm, clients cl, contacts cn WHERE cm.client_id = cl.client_id AND cl.contact_id = cn.contact_id ORDER BY numero_commande LIMIT 20 OFFSET 20; ``` Et l’équivalent avec la syntaxe `JOIN` serait : **Exemple 1-115.** Exemple 1\-115 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT numero_commande, nom AS nom_client FROM commandes cm JOIN clients cl ON (cm.client_id = cl.client_id) JOIN contacts cn ON (cl.contact_id = cn.contact_id) ORDER BY numero_commande LIMIT 20 OFFSET 20; ``` > Afficher les noms et codes des pays qui font partie de la région « Europe ». **Exemple 1-116.** Exemple 1\-116 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT nom_pays, code_pays FROM regions r, pays p WHERE r.region_id = p.region_id AND r.nom_region = 'Europe'; ``` Et l’équivalent avec la syntaxe `JOIN` serait : **Exemple 1-117.** Exemple 1\-117 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT nom_pays, code_pays FROM regions r JOIN pays p ON (r.region_id = p.region_id) WHERE r.nom_region = 'Europe'; ``` > Pour chaque pays, afficher une chaîne de caractères composée de son nom, suivi entre parenthèses de son code puis, séparé par une virgule, du nom de la région dont il fait partie. **Exemple 1-118.** Exemple 1\-118 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT nom_pays || ' (' || code_pays || '), ' || nom_region FROM regions r, pays p WHERE r.region_id = p.region_id; ``` Et l’équivalent avec la syntaxe `JOIN` serait : **Exemple 1-119.** Exemple 1\-119 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT nom_pays || ' (' || code_pays || '), ' || nom_region FROM regions r JOIN pays p ON (r.region_id = p.region_id); ``` Alternativement la fonction `format` peut être utilisée : **Exemple 1-120.** Exemple 1\-120 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT format('%s (%s), %s', nom_pays, code_pays, nom_region) FROM regions r JOIN pays p ON (r.region_id = p.region_id); ``` > Pour les clients ayant passé des commandes durant le mois de janvier 2011, affichez les identifiants des clients, leur nom, leur numéro de téléphone et le nom de leur pays. **Exemple 1-121.** Exemple 1\-121 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT cl.client_id, nom, telephone, nom_pays FROM clients cl, commandes cm, contacts cn, pays p WHERE cl.client_id = cm.client_id AND cl.contact_id = cn.contact_id AND cn.code_pays = p.code_pays AND date_commande BETWEEN '2011-01-01' AND '2011-01-31'; ``` Le troisième module de la formation abordera les jointures et leurs syntaxes. Pour retrouver cette notion dans le manuel, voir [la section sur les jointures](/fr/dev1/04-plus-loin-avec-sql/#jointures). À l’issue de ce prochain module, la requête de cet exercice pourrait être écrite de la façon suivante : **Exemple 1-122.** Exemple 1\-122 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT cl.client_id, nom, telephone, nom_pays FROM clients cl JOIN commandes cm USING (client_id) JOIN contacts co USING (contact_id) JOIN pays p USING (code_pays) WHERE date_commande BETWEEN '2011-01-01' AND '2011-01-31'; ``` > Pour les dix premières commandes de l’année 2011, afficher son numéro, sa date ainsi que son âge. **Exemple 1-123.** Exemple 1\-123 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT numero_commande, date_commande, now() - date_commande AS age FROM commandes WHERE date_commande BETWEEN '2011-01-01' AND '2011-12-31' ORDER BY date_commande LIMIT 10; ```