2. Extraire des données : select

2. Extraire des données : `select`

À quoi cela servirait-il d'enregistrer des données dans une base, si l'on ne disposait pas d'outil pour les extraire ? La commande select est probablement la plus utilisée du langage, et peut-être aussi la plus complexe. On se propose de commencer son étude par quelques exemples simples, avant de voir les cas les plus compliqués.

2.1. Extraire des données d'une table unique

Nous avons déjà vu l'utilisation de cette commande sur des cas très simples dans notre partie d'introduction. La forme la plus simple de la commande select est la suivante.

Exemple 33. Forme simple de select

SELECT [DISTINCT] [nom_de_table.]nom_de_colonne | * | expression [AS alias_de_colonne], ...
    FROM nom_de_table [AS alias_de_table]
   [WHERE predicat]
   [ORDER  BY nom_de_colonne [ASC |  DESC], ...

2.1.1. Liste des colonnes et expressions

Le premier argument d'un select est la liste des colonnes ou expressions que l'on souhaite voir afficher. Chaque élément de cette liste peut être préfixé par un nom de table s'il s'agit d'une colonne. Ce préfixage est obligatoire si deux colonnes de mêmes noms cohabitent dans cette liste, dans ce cas il est nécessaire de lever l'ambigüité. Chaque nom de colonne ou expression peut être associé à un alias, de façon à rendre lisible l'affichage final. Voyons quelques exemples :

Exemple 34. Colonnes et expressions dans une requête SELECT

select nom, prenom ...
 select max(ddnaissance)...
 select Marins.nom  as nom_marin,

2.1.2. Liste de tables : clause `from`

Vient ensuite la liste des tables sur laquelle notre sélection opère. Normalement il ne doit y en avoir qu'une, puisque l'on s'interdit d'utiliser l'ancienne forme d'expression des jointures, comme nous le verrons dans la suite. Mais cette syntaxe est toujours autorisée, et utilisée. Là encore, il est possible de définir un alias sur les tables que l'on déclare, dans un but de meilleure lisibilité du code SQL écrit. Voyons quelques exemples :

Exemple 35. Spécifications des tables dans une requête SELECT

select ...  from Marins, Commune ...
 select max(ddnaissance)…
 select m1.nom, m2.nom  from Marins  as m1, Marin  as m2 …

Nous verrons dans la suite qu'il est également possible de mettre une requête select dans la clause from. On appelle cela une requête imbriquéee.

2.1.3. Conditions de sélection : clause `where`

Les conditions de sélection sont facultatives. Si elles ne sont pas présentes, alors toutes les lignes des tables spécifiées sont renvoyées. Sinon seules les lignes pour lesquelles les conditions de sélection sont vraies sont sélectionnées. Cette clause est assez complexe, aussi allons-nous la traiter dans un un paragraphe ultérieur.

2.1.4. Classement du résultat : clause `order by`

Le résultat d'un select n'est en général pas classé. Si l'on souhaite le classer par ordre alphabétique, ou numérique, il faut utiliser une clause order by. Cette clause peut prendre une liste de colonnes en paramètres. Cette première requête classe les marins par nom, dans l'ordre croissant.

Exemple 36. Clause ORDER BY ASC

select nom, age  from Marins  order  by nom  asc ;

Cette seconde requête les classe par ordre croissant de nom, puis par ordre décroissant d'âge.

Exemple 37. Clause ORDER BY DESC

select nom, age  from Marins  order  by nom  asc, age  desc ;

2.1.5. Mot-clé `distinct`

Enfin, le mot-clé distinct permet de supprimer les doublons du résultat final.

2.2. Clause `where`

Cette clause consiste à évaluer une expression booléenne pour toutes les lignes de la table (ou des tables) en paramètre de la clause from de cette requête. L'argument d'une clause where doit donc toujours se réduire à une expression dont le résultat ne peut prendre que deux valeurs : true ou false.

2.2.1. Opérateurs de comparaison

Les opérateurs de comparaison sont les opérateurs classiques de tout langage de programmation. La différence éventuelle, c'est qu'ils opèrent sur des données de nature différentes, et qu'il faut donc définir précisément leur fonctionnement.

Tableau 8. Opérateurs de comparaison

Opérateur	Comparaison numérique	Comparaison textuelle	Comparaison temporelle
=	Égal	Égal	Même moment
<	Plus petit	Avant	Plus tôt
>	Plus grand	Après	Ensuite
<=	Plus petit ou égal	Avant ou égal	Pas après
>=	Plus grand ou égal	Après ou égal	Pas avant
<>	Différent	Différent	Pas au même moment

On notera qu'il n'y a pas d'opérateur de négation dans cette table.

2.2.2. Composition de comparaison

Il est possible de composer ces opérations logiques à l'aide des opérateurs suivants :

and : et logique classique ;
or : ou logique classique ;
not : négation logique classique.

Tout se passerait comme dans n'importe quel langage de programmation, si le SQL n'introduisait pas la notion de valeur nulle, et autorisait leur prise en compte dans les opérations booléennes. Par exemple, quel est le résultat du and entre une valeur true et une valeur null ? Ou encore, quel valeur nulle est-elle inférieure ou supérieure à 2 ? La réponse du SQL est spécifiée : elle est unknown.

2.2.3. Prédicat `is`, cas des valeurs `null` et `unknown`

Dès qu'une valeur, numérique, textuelle, temporelle ou booléenne, est comparée à un valeur nulle, alors le résultat de la comparaison prend la valeur unknown. Il n'existe qu'une seule manière de savoir si une valeur est nulle ou inconnue, c'est de la tester explicitement grâce au prédicat is, qui s'utilise comme dans les exemples suivants. Ce premier exemple nous donne tous les marins dont on a oublié de renseigner le prénom.

Exemple 38. Prédicat IS NULL

select *  from Marins  where prenom  is  null ;

Ce second exemple nous donne tous les marins pour lesquels la date de mort ou la date de naissance est nulle (il s'agit surtout d'une exemple jouet permettant d'illustrer l'utilisation de is unknown).

Exemple 39. Prédicat IS UNKNOWN

select nom, (ddmort > ddnaissance)  as age_de_mort 
    from Marins 
    where (ddmort > ddnaissance)  is unknown ;

On notera l'utilisation du prédicat is avec null et unknown. C'est ce prédicat qu'il faut utiliser si l'on veut tester qu'une valeur vaut true ou false. Il est très important de noter que null et unknown ne sont égaux à rien, pas même à eux-mêmes. L'algèbre booléenne du SQL est donc un peu particulière, en ce sens qu'elle gère un troisième état en plus des habituels true et false : unknown. Toute opération booléenne avec unknown a pour résultat unknown.

2.2.4. Prédicat `like`

Le prédicat like est utilisé pour comparer les chaînes de caractères à l’aide de caractères de substitution. On compare une chaîne à un motif, dont les caractères _ et % représentent respectivement n’importe quel caractère unique, et n’importe quelle suite de caractères, éventuellement de longueur nulle. L’utilisation du caractère % peut conduire à des recherches très longues, ce prédicat est donc à utiliser avec précaution. Voyons quelques exemples simples. La requête suivante sélectionne tous les marins dont le nom commence M.

Exemple 40. Prédicat LIKE

select *  from Marins  where nom  like  'M%' ;

La requête suivante sélectionne tous les marins dont le nom ne comporte pas 4 lettres et se termine par "ly".

Exemple 41. Prédicat NOT LIKE

select *  from Marins  where nom  not  like  '__ly' ;

Notons que la comparaison de chaînes à l’aide de like compare les chaînes caractère par caractère, à la différence du comparateur =. Donc si A = 'Bart' et B = 'Bart ' (notons les espaces en fin de chaîne), alors (A = B) renverra true, alors que (A like B) renverra false. Si une comparaison de chaînes est tentée alors qu’une des deux chaînes vaut null, alors le résultat sera unknown.

2.2.5. Prédicat `between`

Le prédicat between permet de tester si une valeur se trouve entre deux autres valeurs. Voici un exemple d'utilisation.

Exemple 42. Prédicat BETWEEN

select *  from Marins  where ddnaissance  between 1750  and 1950 ;

2.2.6. Prédicat `in`

Ce prédicat retourne true si l'élément comparé est dans la liste en paramètre du in. Cette liste peut être explicite (comme sur notre exemple), ou peut être exprimée par une sous-requête. La négation de in ne renvoie par nécessairement le complément, c'est notamment le cas s'il se trouve des valeurs nulles.

Exemple 43. Prédicat IN

select *  from Marins  where id_commune  in (1, 3, 6) ;

2.2.7. Prédicat `exists`

Ce prédicat permet de tester si une requête renvoie ou non un résultat. Cette requête est en général une sous-requête. Encore une fois, l’utilisation de la négation de exists ne renvoie pas nécessairement le complément, du fait des valeurs nulles. On peut vérifier la validité d’une jointure à l’aide de ce prédicat. Écrivons par exemple une requête qui teste que tous les champs de notre colonne id_commune ont bien une correspondance dans la table Communes :

Exemple 44. Prédicat EXISTS

select nom, prenom, id_commune
    from Marin
    where  not  exists (
       select * 
       from Commune, Marin 
       where Commune.id = Marin.id_commune) ;

2.3. Requêtes imbriquées

Les requêtes imbriquées (ou sous-requêtes) peuvent être utilisées en plusieurs endroits d'une requête :

en paramètre d'une clause where, dans ce cas la sélection a lieu sur le résultat de cette requête imbriquée ;
en paramètre d'un prédicat exists ou in.

Voyons un exemple simple d'une requête imbriquée dans une clause where.

Exemple 45. Requête imbriquée dans une clause where

select *  from (
     select Marins.nom  as nom, Marins.prenom  as prenom, Communes.nom  as nom_commun
     from Marins
         join Communes  on Marins.id_commune = Communes.id
)  as marin_commune 
 order  by nom, prenom ;

Écrire une requête imbriquée crée en fait une table locale à la requête, qui ne peut pas être référencée ailleurs, avec obligatoirement son propre nom, défini ici par as marin_commune, et sa propre liste de champs, également définie par des alias. Cette table locale est aussi appelée inline view dans la documentation en anglais. Une fois cette table locale créée, elle peut être utilisée exactement comme une table normale.

2.4. Fonctions d'agrégation, groupage

Comme nous l'avons vu dans le chapitre d'introduction, il est possible de grouper plusieurs lignes entre elles sur des critères précis, et de calculer des expressions sur certaines colonnes particulières de ces lignes. La syntaxe du select se complexifie alors un peu, et devient la suivante.

Exemple 46. Forme simple de select avec fonction d'agrégation

SELECT [DISTINCT] [nom_de_table.]nom_de_colonne | * | expression [AS alias_de_colonne], ...
    FROM nom_de_table [AS alias_de_table]
   [WHERE predicat]
   [GROUP  BY liste_de_colonne_a_grouper]
   [HAVING condition_sur_les_groupes]
   [ORDER  BY nom_de_colonne [ASC |  DESC], ...

2.4.1. Fonctions d'agrégation

Voici les principales fonctions d'agrégations de SQL.

Tableau 9. Fonctions d'agrégations

Nom	Opération effectuée
`AVG()`	Calcule la moyenne de ses argument.
`COUNT()`	Retourne le nombre d'arguments passés.
`MAX()`	Retourne la plus grande valeur de ses arguments.
`MIN()`	Retourne la plus petite valeur de ses arguments.
`SUM()`	Retourne la somme des arguments.

2.4.2. Exemples d'agrégation

Voyons immédiatement l'exemple le plus simple, qui permet de compter le nombre de lignes de toute table.

Exemple 47. Utilisation de COUNT()

select count(*)  from Marins ;

Voyons un premier exemple simple, qui retourne la moyenne d'âge calculée sur toute la table Marins.

Exemple 48. Utilisation de AVG()

select avg(age)  from Marins ;

Ce deuxième exemple calcule la moyenne d'âge des marins par commune.

Exemple 49. Utilisation de AVG() ... GROUP BY

select nom_commune, avg(age)  as age_moyen  from Marins 
    group  by nom_commune ;

La clause group by indique au serveur SQL qu'il doit regrouper les marins par commune avant de calculer la moyenne d'âge de ces marins, groupe par groupe. Le résultat comportera donc autant de lignes qu'il y a de communes différentes dans la table Marins. L'utilisation de la clause having permet de filtrer ce type de résultat. Dans l'exemple qui suit, on ne s'intéresse qu'aux communes pour lesquelles la moyenne d'âge est supérieure à 20 ans.

Exemple 50. Utilisation de AVG() ... GROUP BY ... HAVING

select nom_commune, avg(age)  as age_moyen  from Marins 
    group  by nom_commune
    having age_moyen > 20 ;

Il est important de bien comprendre la différence qu'il y a entre la clause where et la clause having. Dans ce type de requête, la clause where agit comme filtre sur les données avant l'application du regroupement des données. La clause having agit une fois les groupages effectués et les fonctions d'agrégation évaluées.

Introduction à SQL

Retour au blog Java le soir

Cours & Tutoriaux