Le langage SQL : interroger une base
Partie 2 sur 4
4Choisir des colonnes : la projection
Une projection sélectionne un ensemble de colonnes d’une table. Toutes les colonnes s’obtiennent avec l’étoile ; une liste de noms, séparés par des virgules, n’en garde que certaines :
SELECT * FROM occaz; SELECT Modele, Serie FROM occaz; -- 18 RL ; Kangoo RL ; Kangoo RL ; 106 KID ; 309 chorus ; Escort Match SELECT DISTINCT Modele, Serie FROM occaz; -- la ligne « Kangoo RL » n'apparaît plus qu'une fois
On peut renommer une colonne du résultat avec AS, et calculer de nouvelles colonnes à partir des autres. Le point-virgule termine une requête ; il est souvent facultatif quand on n’en envoie qu’une.
SELECT Marque, Modele, Compteur / 1000 AS milliers_de_km FROM occaz;
5Choisir des lignes : la restriction
5.1La clause WHERE
Une restriction sélectionne les lignes qui satisfont une condition logique portant sur leurs valeurs. Elle s’exprime avec la clause WHERE, suivie d’une condition construite avec :
- des opérateurs logiques :
AND,OR,NOT; - des comparateurs :
=,<>ou!=(différent),>,<,>=,<=; les comparateurs!>et!<(non supérieur, non inférieur) n’existent que dans certains SGBD, comme SQL Server ; - des prédicats sur les chaînes et les ensembles :
LIKE,BETWEEN,IN; - des opérateurs arithmétiques :
+,-,*,/,%(reste de la division) ; les opérateurs sur les bits&,|,^et~existent dans plusieurs SGBD, avec des notations qui varient.
SELECT * FROM occaz WHERE Compteur < 100000; -- les deux Kangoo et la 106 SELECT Marque, Compteur FROM occaz WHERE Compteur < 100000; -- Renault 56000 ; Renault 12000 ; Peugeot 75600 SELECT * FROM occaz WHERE Compteur >= 30000 AND Compteur <= 100000; -- la Kangoo à 56000 km et la 106
Une requête qui se limiterait à WHERE (Compteur = 30000) ne renverrait aucune ligne ; la version ci-dessus, avec les deux bornes, est la bonne.
5.2Comparer des chaînes : LIKE
Le prédicat LIKE compare une chaîne à un modèle contenant des caractères jokers : % remplace une suite de caractères, éventuellement vide ; _ remplace exactement un caractère, comme le « blanc » au Scrabble.
SELECT * FROM occaz WHERE Marque LIKE '_e%'; -- Renault et Peugeot : un « e » en deuxième position
La notation entre crochets, comme [J-M] pour un caractère compris entre J et M, est propre à SQL Server ; d’autres SGBD offrent des expressions régulières, plus puissantes (voir les expressions régulières). La distinction entre majuscules et minuscules dans LIKE dépend du SGBD : le modèle '_E%' ne trouve « Renault » que si la comparaison ignore la casse. Pour rechercher un caractère joker lui-même, on le fait précéder d’un caractère d’échappement, déclaré avec ESCAPE.
5.3Intervalles et listes : BETWEEN et IN
BETWEEN vérifie qu’une valeur se trouve dans un intervalle, bornes comprises ; IN, qu’elle appartient à une liste :
SELECT * FROM occaz
WHERE Compteur BETWEEN 30000 AND 100000;
SELECT * FROM occaz
WHERE Marque IN ('Peugeot', 'Ford');
Attention à l’ordre des bornes : BETWEEN attend la plus petite d’abord. Écrire BETWEEN 100000 AND 30000 donne une condition qu’aucune valeur ne peut remplir : la requête ne renvoie rien.
5.4Les valeurs manquantes : NULL
Quand un champ n’est pas renseigné, le SGBD lui attribue la marque spéciale NULL. On ne la recherche pas avec =, mais avec les prédicats IS NULL et IS NOT NULL :
SELECT * FROM occaz WHERE Compteur IS NULL; -- la Ford Escort
5.5Une logique à trois valeurs
Pourquoi Compteur = NULL ne fonctionne-t-il pas ? Parce que NULL signifie « inconnu », et qu’une comparaison avec une valeur inconnue ne donne ni vrai ni faux, mais inconnu. SQL utilise ainsi une logique à trois valeurs : vrai, faux et inconnu. Une ligne n’est retenue par WHERE que si la condition est vraie. Conséquence surprenante : la requête WHERE Compteur < 100000 OR Compteur >= 100000 ne renvoie pas la Ford, alors qu’elle semble couvrir tous les cas ; pour la Ford, les deux comparaisons sont inconnues. De même, NOT IN appliqué à une liste qui contient NULL ne renvoie jamais rien (exercice 6). Ces subtilités sont une source classique d’erreurs dans les rapports et les contrôles.
Pour s’exercer
Exercice 2 : les jokers
Que renvoient WHERE Modele LIKE 'K%', WHERE Modele LIKE '1__' et WHERE Serie LIKE '%o%' ?
Voir la réponse
'K%' : les modèles qui commencent par K, les deux Kangoo. '1__' : les modèles de trois caractères commençant par 1, c’est-à-dire la 106 ; la Renault 18 n’a que deux caractères. '%o%' : les séries qui contiennent un « o », c’est-à-dire « chorus » ; si la comparaison ignore la casse, aucune autre série ne s’y ajoute, car « RL », « KID » et « Match » ne contiennent pas de o.
6Trier et regrouper
6.1ORDER BY
La clause ORDER BY trie le résultat, par ordre croissant (ASC, par défaut) ou décroissant (DESC), sur des nombres, des chaînes ou des dates. On peut trier sur plusieurs colonnes : la seconde départage les lignes à égalité sur la première.
SELECT * FROM occaz ORDER BY Marque ASC; -- Ford, puis Peugeot, puis Renault SELECT * FROM occaz ORDER BY Marque ASC, Compteur DESC; -- Ford ; Peugeot 309 (189500), Peugeot 106 (75600) ; Renault 18, Kangoo 56000, Kangoo 12000
Sans ORDER BY, l’ordre des lignes n’est pas garanti, même s’il semble stable : il dépend de la façon dont le SGBD a exécuté la requête. La place des valeurs NULL dans un tri varie selon les SGBD ; la norme permet de la fixer avec NULLS FIRST ou NULLS LAST. Pour ne garder que les premières lignes, la norme prévoit FETCH FIRST 10 ROWS ONLY ; beaucoup de SGBD utilisent aussi LIMIT 10, et SQL Server TOP 10.
6.2GROUP BY et les fonctions d’agrégation
On veut souvent calculer des statistiques par groupe : le kilométrage moyen par marque, le nombre de ventes par mois. La clause GROUP BY, suivie des colonnes qui définissent les groupes, regroupe les lignes ; les fonctions d’agrégation résument ensuite chaque groupe (figure 2) :
AVGcalcule la moyenne d’une colonne, ou de chaque groupe ;COUNTcompte les lignes ;COUNT(*)compte toutes les lignes,COUNT(colonne)seulement celles où la colonne n’est pasNULL;MAXetMINdonnent la plus grande et la plus petite valeur ;SUMfait la somme des valeurs.
SELECT Marque, AVG(Compteur) AS moyenne FROM occaz GROUP BY Marque; -- Renault 63816,7 ; Peugeot 132550 ; Ford NULL
GROUP BY forme des groupes ; les fonctions d’agrégation les résument.Les fonctions d’agrégation ignorent les valeurs NULL : la moyenne de la Ford, qui n’a qu’un compteur inconnu, est elle-même inconnue. Règle importante : dans une requête avec GROUP BY, chaque colonne du SELECT doit soit figurer dans le GROUP BY, soit être résumée par une fonction d’agrégation. Demander le Modele dans la requête ci-dessus n’aurait pas de sens : quel modèle afficher pour le groupe Renault ?
6.3HAVING
La clause HAVING va de pair avec GROUP BY : elle filtre les groupes, comme WHERE filtre les lignes. Pour n’afficher que les moyennes connues :
SELECT Marque, AVG(Compteur) AS moyenne FROM occaz GROUP BY Marque HAVING AVG(Compteur) IS NOT NULL; -- Renault 63816,7 ; Peugeot 132550
On écrit parfois HAVING Moyenne IS NOT NULL, en réutilisant le nom donné par AS. Certains SGBD, comme MySQL, l’acceptent, mais pas la norme : d’après l’ordre d’évaluation (figure 1), HAVING est traité avant que les colonnes du SELECT ne soient nommées. On répète donc l’expression. La différence entre WHERE et HAVING est l’objet de l’exercice 4.
Pour s’exercer
Exercice 3 : compter
Que renvoient SELECT COUNT(*) FROM occaz et SELECT COUNT(Compteur) FROM occaz ? Écrivez ensuite la requête qui donne, pour chaque marque, le nombre de voitures et le kilométrage maximal.
Voir la réponse
COUNT(*) compte toutes les lignes : 6. COUNT(Compteur) ne compte que les lignes où le compteur est renseigné : 5, la Ford étant exclue.
SELECT Marque, COUNT(*) AS nombre, MAX(Compteur) AS maximum FROM occaz GROUP BY Marque; -- Renault 3 123450 ; Peugeot 2 189500 ; Ford 1 NULL
Exercice 4 : WHERE ou HAVING ?
Quelle différence entre ces deux requêtes ?
-- A SELECT Marque, AVG(Compteur) FROM occaz WHERE Compteur < 150000 GROUP BY Marque; -- B SELECT Marque, AVG(Compteur) FROM occaz GROUP BY Marque HAVING AVG(Compteur) < 150000;
Voir la réponse
A filtre les lignes avant le regroupement : la Peugeot 309 (189 500 km) est écartée, la moyenne Peugeot devient 75 600 ; la Ford, dont le compteur est inconnu, est aussi écartée, car la condition n’est pas vraie pour elle. Résultat : Renault 63 816,7, Peugeot 75 600. B calcule d’abord les moyennes sur toutes les lignes, puis filtre les groupes : Renault (63 816,7) et Peugeot (132 550) sont gardés, la Ford, dont la moyenne est inconnue, est écartée. Les deux requêtes répondent à des questions différentes.