« Structured Query Language/Requêtes SELECT » : différence entre les versions

Une page de Wikiversité, la communauté pédagogique libre.
Contenu supprimé Contenu ajouté
(:Julien:) (discussion | contributions)
Aucun résumé des modifications
(:Julien:) (discussion | contributions)
Aucun résumé des modifications
Ligne 378 : Ligne 378 :
==== BETWEEN ====
==== BETWEEN ====


On aurait pu écrire la requête du dernier exemple différemment, les mots-clefs BETWEEN ... AND ... permettent de sélectionner des valeurs comprises entre deux bornes. Voici une requête qui renverra le même résultat qu'au dessus mais avec BETWEEN :
On aurait pu écrire la requête du dernier exemple différemment, les mots-clefs BETWEEN ... AND ... permettent de sélectionner des valeurs comprises entre deux bornes. Voici une requête qui renverra le même résultat qu'au-dessus mais avec BETWEEN :


<source lang="sql">SELECT id, nom FROM test WHERE id BETWEEN 2 AND 7 AND ( nom = 'shepard' OR nom = 'somebody' );</source>
<source lang="sql">SELECT id, nom FROM test WHERE id BETWEEN 2 AND 7 AND ( nom = 'shepard' OR nom = 'somebody' );</source>
Ligne 559 : Ligne 559 :
HAVING, par contre, s'effectue après. C'est pourquoi il permet, contrairement à WHERE, de filtrer les données calculées.
HAVING, par contre, s'effectue après. C'est pourquoi il permet, contrairement à WHERE, de filtrer les données calculées.


{{non}} La requête suivante vous renverra une erreur, en général « la colonne score n'existe pas » :
La requête suivante vous renverra une erreur, en général « la colonne score n'existe pas » :


<source lang="sql">SELECT points AS score FROM test WHERE score > 100;</source>
<source lang="sql">SELECT points AS score FROM test WHERE score > 100;</source>


Une façon de contourner ce problème serait de traiter cette requête comme un tableau et d'y infliger une nouvelle requête (je dis infliger car cette pratique risque bien de mettre un serveur par terre s'il y a beaucoup de données) en mettant la première requête dans le FROM de la principale :
Une façon de contourner ce problème est de traiter cette requête comme un tableau et d'y effectuer une nouvelle requête (mais cette pratique alourdit la charge du serveur) en mettant la première requête dans le FROM de la principale :


<source lang="sql">SELECT score FROM (SELECT points AS score FROM test) s WHERE score > 100;</source>
<source lang="sql">SELECT score FROM (SELECT points AS score FROM test) s WHERE score > 100;</source>
Ligne 569 : Ligne 569 :
Le "s" après le FROM est un alias de table, il est obligatoire dans le cas d'une sous-requête (c'est la requête entre parenthèses).
Le "s" après le FROM est un alias de table, il est obligatoire dans le cas d'une sous-requête (c'est la requête entre parenthèses).


La requête suivante aura l'effet escompté, en plus d'être bien plus rapide que celle juste au dessus :
La requête suivante a l'effet escompté, en plus d'être bien plus rapide que celle juste au-dessus :


<source lang="sql">SELECT points AS score FROM test HAVING score > 100;</source>
<source lang="sql">SELECT points AS score FROM test HAVING score > 100;</source>
Ligne 667 : Ligne 667 :
|}
|}


Vous constatez qu'en effet, c'est ordonné par ordre de nom, mais vous pouvez également spécifier plusieurs colonnes pour l'ordonnancement du résultat. Par exemple par nom puis par points :
Vous constatez qu'en effet, les colonnes sont ordonnées par ordre alphabétique sur le nom, mais vous pouvez également spécifier plusieurs colonnes pour l'ordonnancement du résultat. Par exemple par nom puis par points :


<source lang="sql">SELECT * FROM test ORDER BY nom, points;</source>
<source lang="sql">SELECT * FROM test ORDER BY nom, points;</source>
Ligne 725 : Ligne 725 :
|}
|}


Si vous voulez trier une colonne par ordre inverse, il vous suffit d'ajouter le mot-clef DESC après le nom de la colonne concernée. Par exemple la même requête qu'au dessus avec les points triés par ordre inverse :
Si vous voulez trier une colonne par ordre inverse, il vous suffit d'ajouter le mot-clef DESC après le nom de la colonne concernée. Par exemple la même requête qu'au-dessus avec les points triés par ordre inverse :


<source lang="sql">SELECT * FROM test ORDER BY nom, points DESC;</source>
<source lang="sql">SELECT * FROM test ORDER BY nom, points DESC;</source>
Ligne 783 : Ligne 783 :
|}
|}


{{non}} ORDER BY permet de trier selon les colonnes récupérées uniquement, ainsi la requête suivante n'est pas conforme étant donné que la colonne "quand" ne fait pas partie des colonnes rapatriées :
ORDER BY permet de trier selon les colonnes récupérées uniquement, ainsi la requête suivante n'est pas conforme étant donné que la colonne "quand" ne fait pas partie des colonnes rapatriées :


<source lang="sql">SELECT id, nom FROM test ORDER BY quand;</source>
<source lang="sql">SELECT id, nom FROM test ORDER BY quand;</source>


== Une clause à part: LIMIT ou TOP ==
== Une clause à part : LIMIT ou TOP ==


Une clause {{Abréviation|S.Q.L.|Structured Query Language}} implémentée par la plupart des SGBDR permet de limiter le nombre de lignes retournées. Pour certains (PostGreSQL, MySQL, SQLite par exemple), c'est LIMIT à la fin de l'ordre SELECT, pour d'autres (Microsoft Access, Microsoft {{Abréviation|S.Q.L.|Structured Query Language}} Server), c'est TOP au début de ce même ordre.
Une clause {{Abréviation|S.Q.L.|Structured Query Language}} implémentée par la plupart des SGBDR permet de limiter le nombre de lignes retournées. Pour certains (PostGreSQL, MySQL, SQLite par exemple), c'est LIMIT à la fin de l'ordre SELECT, pour d'autres (Microsoft Access, Microsoft {{Abréviation|S.Q.L.|Structured Query Language}} Server), c'est TOP au début de ce même ordre.


Ces clauses ne font absolument pas partie du standard {{Abréviation|S.Q.L.|Structured Query Language}}, en effet le {{Abréviation|S.Q.L.|Structured Query Language}} suit une logique ensembliste, et limiter un ensemble est contraire à cette logique. N'hésitez cependant pas à les utiliser car dans beaucoup de cas ces clauses sont très pratiques et vous permettent d'éviter des élucubrations souvent dangeureuses pour la santé de votre serveur ...
Ces clauses ne font absolument pas partie du standard {{Abréviation|S.Q.L.|Structured Query Language}}, en effet le {{Abréviation|S.Q.L.|Structured Query Language}} suit une logique ensembliste, et limiter un ensemble est contraire à cette logique. N'hésitez cependant pas à les utiliser car dans beaucoup de cas ces clauses sont très pratiques.


==== Un exemple avec LIMIT ====
==== Un exemple avec LIMIT ====
Ligne 826 : Ligne 826 :
|}
|}


Les 5 premières lignes insérées sont renvoyées. Suite à certaines requêtes le SGBDR pourrait tout aussi bien vous renvoyer les lignes dans un ordre totalement aléatoire, {{Abréviation|S.Q.L.|Structured Query Language}} n'admet aucun ordre par défaut, si vous voulez être sur d'obtenir le même résultat à chaque requête sur les mêmes données, vous devez utiliser ORDER BY.
Attention, dans ce cas-ci les 5 premières lignes insérées sont renvoyées, '''ce n'est pas toujours le cas'''


Les SGBDR qui proposent la clause LIMIT proposent généralement un complément nommé OFFSET qui permet de dire de prendre n lignes à partir de la ligne k :
Suite à certaines requêtes le SGBDR pourrait tout aussi bien vous renvoyer les lignes dans un ordre totalement aléatoire, {{Abréviation|S.Q.L.|Structured Query Language}} n'admet aucune sorte "d'ordre par défaut", si vous voulez être sur d'obtenir le même résultat à chaque requête sur les mêmes données, vous devez utiliser ORDER BY.

Les SGBDR qui proposent la clause LIMIT proposent généralement un complément nommé OFFSET qui permet de dire de prendre x lignes à partir de la ligne y :


<source lang="sql">SELECT * FROM test ORDER BY id LIMIT 3 OFFSET 2;</source>
<source lang="sql">SELECT * FROM test ORDER BY id LIMIT 3 OFFSET 2;</source>
Ligne 853 : Ligne 851 :
|}
|}


==== Le même avec TOP ====
==== Le même exemple avec TOP ====


<source lang="sql">SELECT TOP 5 * FROM test;</source>
<source lang="sql">SELECT TOP 5 * FROM test;</source>



{| class="wikitable"
{| class="wikitable"
Ligne 920 : Ligne 917 :
== Application de l'[[algèbre relationnelle]] ==
== Application de l'[[algèbre relationnelle]] ==
=== Sélection ===
=== Sélection ===
À partir d'un ensemble A, obtenir un ensemble B dont les valeurs des nuplets correspondent à des critères donnés.
À partir d'un ensemble A, obtenir un ensemble B dont les valeurs des n-uplets correspondent à des critères donnés.
<source lang="sql">
<source lang="sql">
select *
SELECT *
from A
FROM A
where nuplet = 'critère'
WHERE nuplet = 'critère'
</source>
</source>


=== Projection ===
=== Projection ===
À partir d'un ensemble A, obtenir un ensemble B dont les nuplets contiennent certaines composantes des nuplets de A.
À partir d'un ensemble A, obtenir un ensemble B dont les k-uplets contiennent certaines composantes des n-uplets de A.
<source lang="sql">
<source lang="sql">
select cellule
SELECT cellule
from A
FROM A
where nuplet = 'critère'
WHERE nuplet = 'critère'
</source>
</source>


=== Intersection ===
=== Intersection ===
À partir des ensembles A et B, obtenir un ensemble C qui contient les nuplets présents à la fois dans A et dans B. voir aussi intersection (mathématiques)
À partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents à la fois dans A et dans B.

<source lang="sql">
<source lang="sql">
select cellule from A
SELECT cellule FROM A
INTERSECT
intersect
select cellule from B
SELECT cellule FROM B
</source>
</source>


=== Union ===
=== Union ===
À partir des ensembles A et B, obtenir un ensemble C qui contient les nuplets présents dans A ou dans B. voir aussi union (mathématiques).
À partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents dans A ou dans B.
<source lang="sql">
<source lang="sql">
select cellule from A
SELECT cellule FROM A
UNION ALL
union all
select cellule from B
SELECT cellule FROM B
</source>
</source>


Sans les doublons :
Sans les doublons :
<source lang="sql">
<source lang="sql">
select cellule from A
SELECT cellule FROM A
UNION
union
select cellule from B
SELECT cellule FROM B
</source>
</source>


=== Différence ===
=== Différence ===
À partir des ensembles A et B, obtenir un ensemble C qui contient les nuplets présents dans A mais pas dans B. voir aussi différence.
À partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents dans A mais pas dans B.
<source lang="sql">
<source lang="sql">
select cellule
SELECT cellule
from A
FROM A
where A.cellule not in (select cellule from B);
WHERE A.cellule NOT IN (SELECT cellule FROM B);
</source>
</source>


En {{Abréviation|S.Q.L.|Structured Query Language}} 2 :
En {{Abréviation|S.Q.L.|Structured Query Language}} 2 :
<source lang="sql">
<source lang="sql">
select cellule
SELECT cellule
from A
FROM A
EXCEPT
except
select cellule
SELECT cellule
from B;
FROM B;
</source>
</source>


=== Produit cartésien ===
=== Produit cartésien ===
À partir des ensembles A et B, obtenir un ensemble C qui contient des nuplets qui sont toutes les combinaisons possibles des nuplets de A avec les nuplets de B. voir aussi produit cartésien.
À partir des ensembles A et B, obtenir un ensemble C qui contient des n-uplets qui sont toutes les combinaisons possibles des j-uplets de A avec les k-uplets de B.
<source lang="sql">
<source lang="sql">
select cellule
SELECT cellule
from A, B
FROM A, B
</source>
</source>


=== Jointure ===
=== Jointure ===
À partir des ensembles A et B, obtenir un ensemble C qui est le produit cartésien de chaque nuplet de A pour lesquels il existe des nuplets de B la valeur de la composante M de A est identique à la valeur de la composante N de B.
À partir des ensembles A et B, obtenir un ensemble C qui est un sous-ensemble du produit cartésien de A et B dans lesquel la valeur de la composante M de A est identique à la valeur de la composante N de B.
<source lang="sql">
<source lang="sql">
select A.cellule
SELECT A.cellule
from A left join B on A.cellule = B.cellule
FROM A LEFT JOIN B ON A.cellule = B.cellule
where B.cellule is null;
WHERE B.cellule IS NULL;
</source>
</source>


=== Division cartésienne ===
=== Division cartésienne ===
À partir des ensembles A et B, obtenir un ensemble C, où la combinaison de chaque nuplet de B avec chaque nuplet de C (produit cartésien de B et C) existe dans A.
À partir des ensembles A et B, obtenir un ensemble C, où la combinaison de chaque n-uplet de B avec chaque n-uplet de C (produit cartésien de B et C) existe dans A.
<source lang="sql">
<source lang="sql">
</source>
</source>

Version du 23 février 2012 à 13:38

Début de la boite de navigation du chapitre
Requêtes SELECT
Icône de la faculté
Chapitre no 3
Leçon : Structured Query Language
Chap. préc. :Instruction CREATE TABLE
Chap. suiv. :Intégrité référentielle
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Structured Query Language : Requêtes SELECT
Structured Query Language/Requêtes SELECT
 », n'a pu être restituée correctement ci-dessus.

La requête la plus simple ?

Nous allons commencer par analyser une requête toute simple qui renvoie toutes les données que nous avons créées au chapitre précédent :

SELECT * FROM test;

Remarquez que tous les ordres S.Q.L. se terminent par un point-virgule et que, par convention, les mots-clefs S.Q.L. sont écrits en majuscules.

Normalement votre client S.Q.L. devrait vous renvoyer un tableau tel que celui-ci :

id nom points quand
1 shepard 384 2007-01-01 00:00:00
2 shepard 194 2007-01-03 12:51:25
3 visiteur 156 2007-01-03 12:54:19
4 shepard 16 2007-01-03 13:04:18
5 somebody 958 2007-01-05 23:16:08
6 somebody 1084 2007-01-06 02:15:59
7 shepard 453 2007-01-10 15:32:06
8 shepard 35 2007-01-10 15:43:15
9 visiteur 125 2007-01-11 12:13:15
10 somebody 856 2007-01-11 22:19:23

La clause FROM

La commande précédente permet de récupérer toutes les données de la table « test » que nous venons de créer. Le mot-clef FROM permet simplement de dire au SGBDR de quel objet il faut prendre les données, en l'occurrence de l'objet « test » qui se trouve être une table.

Mais FROM peut prendre d'autres arguments qu'un objet de type table, par exemple on peut y mettre le nom d'une vue ou même une autre requête.

La seule obligation est que l'objet doit contenir des données réparties en colonnes, comme dans une table.

L'opérateur *

  • SELECT * peut se traduire par « prendre toutes les colonnes des tables citées en clause FROM » en S.Q.L.. Donc la requête
    SELECT * FROM test;
    
    veut dire « Prendre toutes les colonnes dans la table test ».
  • Passons à un autre exemple, aussi simple que le précédent :
SELECT id, nom FROM test;

Vous devriez deviner ce que va renvoyer cette requête : les colonnes « id » et « nom » de l'objet « test ».

id nom
1 shepard
2 shepard
3 visiteur
4 shepard
5 somebody
6 somebody
7 shepard
8 shepard
9 visiteur
10 somebody

Mini-conclusion pour ce premier exemple

  • SELECT permet d'obtenir les données qu'on veut voir dans le résultat final.
  • L'intruction suivante permet de récupérer le contenu des colonnes "x", "y" et "z" de la table "a".
 SELECT x, y, z FROM a;
  • Plus loin, dans l'étude des requêtes multi-tabulaires, nous verrons qu'il n'est pas nécessaire d'utiliser des données dans toutes les tables impliquées dans une requête.
  • L'opérateur * permet d'obtenir toutes les colonnes.
 SELECT * FROM a;
  • Notez que si vous cherchez à rendre vos requêtes aussi rapides que possible, utiliser * est déconseillé, mieux vaut écrire toutes les colonnes.

Renommer des colonnes avec AS

Quelques lignes pour vous présenter le renommage des colonnes.

Par défaut, quand vous faites

SELECT nom FROM test;

Le titre de l'unique colonne retournée est "nom". Si vous préférez que ce titre soit "pseudo", il suffit de faire :

SELECT nom AS pseudo FROM test;

L'intérêt peut vous paraître limité mais utilisé avec des fonctions il peut devenir indispensable pour éviter les confusions :

SELECT UPPER(nom) AS nom_majuscules, nom FROM test;

UPPER renvoie son argument en majuscules.

ou encore

SELECT SUM(cas_palu_enfant + cas_palu_adulte) as 'nb_cas' FROM enquete_palu;

SUM renvoie la somme de l'ensemble des valeurs des deux colonnes impliquées.

UPDATE

Cette commande permet une modification des enregistrements des tables.

UPDATE `enquete_palu`
SET  `nom` = 'UPPER(nom)'
WHERE (
`cas_palu_adulte` LIKE '%0%'
AND `nom` LIKE '%échantillon_test%'
)

DELETE

Pour supprimer des enregistrements de tables.

DELETE FROM 'enquete_palu'
WHERE (
`cas_palu_enfant` LIKE '%0%'
AND `nom` LIKE '%échantillon_test%'
)

DISTINCT

On va s'amuser un tout petit peu (oui oui, S.Q.L. c'est amusant, vous verrez !)

Essayez la requête suivante :

SELECT nom FROM test;

Vous recevez la liste des 10 noms de la table :

nom
shepard
shepard
visiteur
shepard
somebody
somebody
shepard
shepard
visiteur
somebody

Bon imaginez maintenant que vous ne vouliez pas tous ces doublons qui ne servent à rien, que vous ne vouliez que les 3 noms "shepard", "visiteur" et "somebody".

Et bien en S.Q.L. c'est très simple d'obtenir ce résultat, on le fait simplement en ajoutant le mot-clef DISTINCT juste après SELECT :

SELECT DISTINCT nom FROM test;
nom
shepard
visiteur
somebody

Cool, non ?

Bien sûr DISTINCT agit sur toute la ligne, ainsi la requête suivante vous renverra 10 lignes puisque les élements de la colonne id sont uniques :

SELECT DISTINCT id, nom FROM test;
id nom
1 shepard
2 shepard
3 visiteur
4 shepard
5 somebody
6 somebody
7 shepard
8 shepard
9 visiteur
10 somebody

DISTINCT n'est pas une fonction, juste un mot-clef, la requête suivante fonctionnera, mais n'aura pas vraiment de sens :

SELECT DISTINCT(id), nom FROM test;

Syntaxe de base

Une version simplifiée de l'ordre SELECT selon la norme S.Q.L. pourrait être :

SELECT [DISTINCT] { * | col1, col2, ... }
	FROM table
	[WHERE conditions]
	[GROUP BY colonnes groupées]
	[HAVING conditions]
	[ORDER BY colonnes de tri];

Les éléments entre crochets ne sont pas obligatoires ; il faut prendre un des éléments au choix des éléments entre accolades.

Les deux premières lignes devraient déjà vous être familières, passons à la suite.

WHERE

La clause WHERE vous permet d'appliquer un filtre sur les données, pour cela il nous faut connaitre les opérateurs de comparaison que voici :

opérateur signification
a = b a est égal à b
a < b a est plus petit que b
a <= b a est plus petit ou égal à b
a > b a est plus grand que b
a >= b a est plus grand ou égal à b
a <> b a est différent de b

Ces opérateurs fonctionnent évidemment sur les nombres, mais également sur les chaînes de caractères et sur les dates.

Un exemple :

SELECT id, nom FROM test WHERE id >= 5;
id nom
5 somebody
6 somebody
7 shepard
8 shepard
9 visiteur
10 somebody

Il est également possible de cumuler des conditions grâce aux opérateurs AND, OR et XOR (respectivement et, ou, et ou exclusif (l'un ou l'autre mais pas les deux)). Les parenthèses peuvent servir à organiser ces conditions.

Un autre exemple :

SELECT id, nom FROM test WHERE ( id >= 2 AND id <= 7 ) AND ( nom = 'shepard' OR nom = 'somebody' );
id nom
2 shepard
4 shepard
5 somebody
6 somebody
7 shepard

Vous vous souvenez peut-être également que l'on peut définir ses propres types utilisateurs ? Et bien il est également possible de (re)définir la signification des opérateurs pour ces types, et même d'en créer de nouveaux.

BETWEEN

On aurait pu écrire la requête du dernier exemple différemment, les mots-clefs BETWEEN ... AND ... permettent de sélectionner des valeurs comprises entre deux bornes. Voici une requête qui renverra le même résultat qu'au-dessus mais avec BETWEEN :

SELECT id, nom FROM test WHERE id BETWEEN 2 AND 7 AND ( nom = 'shepard' OR nom = 'somebody' );
id nom
2 shepard
4 shepard
5 somebody
6 somebody
7 shepard

IN

En fait, il y a moyen d'écrire encore plus élégamment cette requête grâce au mot-clef IN qui permet de vérifier qu'une valeur précise se trouve dans une liste de valeurs :

SELECT id, nom FROM test WHERE id BETWEEN 2 AND 7 AND nom IN ( 'shepard', 'somebody' );
id nom
2 shepard
4 shepard
5 somebody
6 somebody
7 shepard

LIKE

Voici un mot-clef un peu plus complexe : il permet de vérifier des expressions régulières simples.

Il admet deux types de « jokers » : % et _.

% remplace une chaine de caractères, y compris une chaine vide. _ remplace un caractère, mais pas un caractère vide.

Par exemple, si on veut tous les noms qui commencent par s :

SELECT DISTINCT nom FROM test WHERE nom LIKE 's%';
nom
shepard
somebody

NOT

Pour terminer, les 3 mots-clefs que nous venons d'utiliser peuvent tous être précédés d'un NOT qui inverse leur effet. Par exemple :

SELECT DISTINCT nom FROM test WHERE nom NOT LIKE 's%';
nom
visiteur

GROUP BY

GROUP BY est une clause de l'ordre SELECT permettant de regrouper des lignes.

En effet, GROUP BY nous permet de travailler non plus sur des lignes, mais sur des groupes de lignes. Le principal avantage que cela procure est qu'il existe des fonctions qu'on applique sur des groupes qui permettent, par exemple, de calculer le nombre de lignes d'un groupe, ou de calculer la moyenne des éléments d'une colonne d'un groupe, leur somme, leur minimum, leur maximum et d'autres fonctions statistiques (écart-type et variance par exemple font partie de la norme S.Q.L.).

SELECT nom FROM test GROUP BY nom;

En exécutant cette requête, vous vous rendrez compte qu'elle agit comme un DISTINCT ... En fait GROUP BY fait beaucoup plus que ça : il a, sans vous le dire, créé 3 groupes avec chacun 3 colonnes (vous pouvez également imaginer 3 « sous-tables ») :

  • Un groupe shepard qui contient 5 lignes : (1, 384, "2007-01-01 00:00:00"), (2, 194, "2007-01-03 12:51:25"), (4, 16, "2007-01-03 13:04:18"), …
  • Un groupe somebody qui contient 3 lignes : (5, 958, "2007-01-05 23:16:08"), …
  • Un groupe visiteur qui contient 2 lignes : (3, 156, "2007-01-03 12:54:19"), …

Voyons maintenant ce que GROUP BY permet de faire :

SELECT COUNT(*), nom FROM test GROUP BY nom;
count nom
2 somebody
3 visiteur
5 shepard

COUNT permet de compter le nombre de lignes que contient chaque groupe, et en effet ça correspond avec la description des 3 groupes qu'on a faite juste avant.

On pourrait également chercher à connaître le nombre de points accumulés par chaque personne, pour cela on utilise la fonction SUM sur la colonne points, comme ceci :

SELECT SUM(points), nom FROM test GROUP BY nom;
sum nom
2898 somebody
281 visiteur
1082 shepard

Voici les fonctions de groupage que l'on rencontre sur tous les SGBDR :

nom description exemple
COUNT(*) Compte le nombre de lignes dans le groupe
SELECT nom, COUNT(*) FROM test GROUP BY nom;
COUNT Compte le nombre de valeurs dans une colonne d'un groupe
SELECT nom, COUNT(points) FROM test GROUP BY nom;
SUM Somme toutes les valeurs d'une colonne d'un groupe
SELECT nom, SUM(points) FROM test GROUP BY nom;
AVG Calcule la moyenne de toutes les valeurs d'une colonne d'un groupe
SELECT nom, AVG(points) FROM test GROUP BY nom;
MIN Renvoie la valeur minimale d'une colonne d'un groupe
SELECT nom, MIN(quand) FROM test GROUP BY nom;
MAX Renvoie la valeur maximale d'une colonne d'un groupe
SELECT nom, MAX(quand) FROM test GROUP BY nom;

Les valeurs NULL

Le langage S.Q.L. accepte l'absence de valeur (ou NULL). Cette valeur n'est pas égale à 0. Dans toutes les fonctions statistiques agissant sur une colonne vues ci-dessus (sauf COUNT(*)), les valeurs NULL ne sont pas prises en compte dans le calcul. C'est la différence entre COUNT(*) et COUNT(points) : si une valeur de la colonne points est NULL, alors le résultat de COUNT(*) et de COUNT(points) ne sera pas le même.

D'autres fonctions statistiques

En plus de ces 5 fonctions statistiques, les SGBDR implémentent souvent d'autres fonctions. La variance d'une population ou d'un échantillon (VAR_POP et VAR_SAMP) ainsi que leurs écarts-types (STDDEV_POP et STDDEV_SAMP) sont compris dans le standard mais pas implémentés dans tous les SGBDR.

Par ailleurs, certains SGBDR tels que PostGreSQL permettent de créer vos propres fonctions statistiques.

HAVING

HAVING vous permet de filtrer les données.

Vous vous demandez sans doute à quoi il sert puisque WHERE le fait déjà.

En effet, WHERE le fait, mais avant de traiter les données, c'est-à-dire avant le traitement des éventuelles fonctions dans la requête, et donc des fonctions statistiques que nous venons de voir.

HAVING, par contre, s'effectue après. C'est pourquoi il permet, contrairement à WHERE, de filtrer les données calculées.

La requête suivante vous renverra une erreur, en général « la colonne score n'existe pas » :

SELECT points AS score FROM test WHERE score > 100;

Une façon de contourner ce problème est de traiter cette requête comme un tableau et d'y effectuer une nouvelle requête (mais cette pratique alourdit la charge du serveur) en mettant la première requête dans le FROM de la principale :

SELECT score FROM (SELECT points AS score FROM test) s WHERE score > 100;

Le "s" après le FROM est un alias de table, il est obligatoire dans le cas d'une sous-requête (c'est la requête entre parenthèses).

La requête suivante a l'effet escompté, en plus d'être bien plus rapide que celle juste au-dessus :

SELECT points AS score FROM test HAVING score > 100;

Dans les deux cas, le résultat sera :

score
384
194
156
958
1084
453
125
856

L'intérêt de HAVING se porte évidemment sur les colonnes aliasées dont le contenu a été calculé, par exemple les colonnes résultantes de fonctions statistiques :

SELECT SUM(points) AS total FROM test GROUP BY nom HAVING total > 1000;
total
1082
2898

ORDER BY

ORDER BY permet, vous vous en doutez, d'ordonner le résultat de la requête. Il suffit de lui donner en argument la colonne selon laquelle il doit ordonner. Par exemple :

SELECT * FROM test ORDER BY nom;
id nom points quand
1 shepard 384 2007-01-01 00:00:00
2 shepard 194 2007-01-03 12:51:25
4 shepard 16 2007-01-03 13:04:18
7 shepard 453 2007-01-10 15:32:06
8 shepard 35 2007-01-10 15:43:15
5 somebody 958 2007-01-05 23:16:08
6 somebody 1084 2007-01-06 02:15:59
10 somebody 856 2007-01-11 22:19:23
3 visiteur 156 2007-01-03 12:54:19
9 visiteur 125 2007-01-11 12:13:15

Vous constatez qu'en effet, les colonnes sont ordonnées par ordre alphabétique sur le nom, mais vous pouvez également spécifier plusieurs colonnes pour l'ordonnancement du résultat. Par exemple par nom puis par points :

SELECT * FROM test ORDER BY nom, points;
id nom points quand
4 shepard 16 2007-01-03 13:04:18
8 shepard 35 2007-01-10 15:43:15
2 shepard 194 2007-01-03 12:51:25
1 shepard 384 2007-01-01 00:00:00
7 shepard 453 2007-01-10 15:32:06
10 somebody 856 2007-01-11 22:19:23
5 somebody 958 2007-01-05 23:16:08
6 somebody 1084 2007-01-06 02:15:59
9 visiteur 125 2007-01-11 12:13:15
3 visiteur 156 2007-01-03 12:54:19

Si vous voulez trier une colonne par ordre inverse, il vous suffit d'ajouter le mot-clef DESC après le nom de la colonne concernée. Par exemple la même requête qu'au-dessus avec les points triés par ordre inverse :

SELECT * FROM test ORDER BY nom, points DESC;
id nom points quand
7 shepard 453 2007-01-10 15:32:06
1 shepard 384 2007-01-01 00:00:00
2 shepard 194 2007-01-03 12:51:25
8 shepard 35 2007-01-10 15:43:15
4 shepard 16 2007-01-03 13:04:18
6 somebody 1084 2007-01-06 02:15:59
5 somebody 958 2007-01-05 23:16:08
10 somebody 856 2007-01-11 22:19:23
3 visiteur 156 2007-01-03 12:54:19
9 visiteur 125 2007-01-11 12:13:15

ORDER BY permet de trier selon les colonnes récupérées uniquement, ainsi la requête suivante n'est pas conforme étant donné que la colonne "quand" ne fait pas partie des colonnes rapatriées :

SELECT id, nom FROM test ORDER BY quand;

Une clause à part : LIMIT ou TOP

Une clause S.Q.L. implémentée par la plupart des SGBDR permet de limiter le nombre de lignes retournées. Pour certains (PostGreSQL, MySQL, SQLite par exemple), c'est LIMIT à la fin de l'ordre SELECT, pour d'autres (Microsoft Access, Microsoft S.Q.L. Server), c'est TOP au début de ce même ordre.

Ces clauses ne font absolument pas partie du standard S.Q.L., en effet le S.Q.L. suit une logique ensembliste, et limiter un ensemble est contraire à cette logique. N'hésitez cependant pas à les utiliser car dans beaucoup de cas ces clauses sont très pratiques.

Un exemple avec LIMIT

SELECT * FROM test LIMIT 5;
id nom points quand
1 shepard 384 2007-01-01 00:00:00
2 shepard 194 2007-01-03 12:51:25
3 visiteur 156 2007-01-03 12:54:19
4 shepard 16 2007-01-03 13:04:18
5 somebody 958 2007-01-05 23:16:08

Les 5 premières lignes insérées sont renvoyées. Suite à certaines requêtes le SGBDR pourrait tout aussi bien vous renvoyer les lignes dans un ordre totalement aléatoire, S.Q.L. n'admet aucun ordre par défaut, si vous voulez être sur d'obtenir le même résultat à chaque requête sur les mêmes données, vous devez utiliser ORDER BY.

Les SGBDR qui proposent la clause LIMIT proposent généralement un complément nommé OFFSET qui permet de dire de prendre n lignes à partir de la ligne k :

SELECT * FROM test ORDER BY id LIMIT 3 OFFSET 2;
id nom points quand
3 visiteur 156 2007-01-03 12:54:19
4 shepard 16 2007-01-03 13:04:18
5 somebody 958 2007-01-05 23:16:08

Le même exemple avec TOP

SELECT TOP 5 * FROM test;
id nom points quand
1 shepard 384 2007-01-01 00:00:00
2 shepard 194 2007-01-03 12:51:25
3 visiteur 156 2007-01-03 12:54:19
4 shepard 16 2007-01-03 13:04:18
5 somebody 958 2007-01-05 23:16:08

Il n'existe pas d'équivalent à OFFSET pour Access et S.Q.L. Server. Il y a toutefois moyen de contourner ce problème de la manière suivante :

SELECT 	*
FROM 	( SELECT TOP 3 	*
	FROM 	( SELECT TOP 5 	*
		FROM test
		ORDER BY id )
	ORDER BY id DESC )
ORDER BY id;
id nom points quand
3 visiteur 156 2007-01-03 12:54:19
4 shepard 16 2007-01-03 13:04:18
5 somebody 958 2007-01-05 23:16:08

Je vous invite à décortiquer cette requête en guise d'exercice.

Application de l'algèbre relationnelle

Sélection

À partir d'un ensemble A, obtenir un ensemble B dont les valeurs des n-uplets correspondent à des critères donnés.

SELECT *
FROM A
WHERE nuplet = 'critère'

Projection

À partir d'un ensemble A, obtenir un ensemble B dont les k-uplets contiennent certaines composantes des n-uplets de A.

SELECT cellule
FROM A
WHERE nuplet = 'critère'

Intersection

À partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents à la fois dans A et dans B.

SELECT cellule FROM A
INTERSECT
SELECT cellule FROM B

Union

À partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents dans A ou dans B.

SELECT cellule FROM A
UNION ALL
SELECT cellule FROM B

Sans les doublons :

SELECT cellule FROM A
UNION
SELECT cellule FROM B

Différence

À partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents dans A mais pas dans B.

SELECT cellule
FROM A
WHERE A.cellule NOT IN (SELECT cellule FROM B);

En S.Q.L. 2 :

SELECT cellule
FROM A
EXCEPT
SELECT cellule
FROM B;

Produit cartésien

À partir des ensembles A et B, obtenir un ensemble C qui contient des n-uplets qui sont toutes les combinaisons possibles des j-uplets de A avec les k-uplets de B.

SELECT cellule
FROM A, B

Jointure

À partir des ensembles A et B, obtenir un ensemble C qui est un sous-ensemble du produit cartésien de A et B dans lesquel la valeur de la composante M de A est identique à la valeur de la composante N de B.

SELECT A.cellule
FROM A LEFT JOIN B ON A.cellule = B.cellule
WHERE B.cellule IS NULL;

Division cartésienne

À partir des ensembles A et B, obtenir un ensemble C, où la combinaison de chaque n-uplet de B avec chaque n-uplet de C (produit cartésien de B et C) existe dans A.

Références