LE SQL de A à Z
2eme partie
Dans
le précédent article nous avons vu l'historique de SQL et
ses différentes composantes. Nous entrons maintenant dans le vif
du sujet, en nous intéressant au simple SELECT.
Simple
? Pas si sûr...
Dans
le dernier document normatif de l'ISO, la syntaxe de la commande SELECT
est décrite en plus de 300 pages ! C'est pourquoi nous nous permettons
de mettre en doute la simplicité de la commande SELECT !
NOTA : La structure de la base de
données exemple, ainsi qu'une version des principales bases utilisées
sont disponibles dans la page "La base de données
exemple"
Le SELECT est la commande de base du SQL destinée à extraire des données d'une base ou calculer de nouvelles données à partir d'existantes
Voici la syntaxe générale
d'une commande SELECT :
| SELECT [DISTINCT ou ALL]
* ou liste de colonnes FROM nom de table ou de la vue
[WHERE prédicats] [ORDER BY ordre des colonnes] [GROUP BY ordre des groupes] [HAVING condition] |
NOTA : dans cette syntaxe, les mots clef du SQL sont en majuscule droite, les paramètres en minuscule italique et entre crochets on trouve les parties optionnelles
REMARQUE : pour spécifier une valeur littérale il faut l'entourer de guillemets simples.
Un premier exemple basique :
exemple 1.1
| SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT WHERE TIT_CODE = 'M.' |
CLI_NOM CLI_PRENOM
------- ---------- DUPONT Alain MARTIN Marc BOUVIER Alain DUBOIS Paul DREYFUS Jean FAURE Alain PAUL Marcel DUVAL Arsène PHILIPPE André CHABAUD Daniel BAILLY Jean-François ... |
Permet de trouver les noms et prénoms des clients dont le titre est M. (monsieur).
NOTA : toutes les paramètres à prendre sous forme de litteraux doivent être exprimées entourés d'apostrophes (simple cotes).
Le caractère * (étoile)
récupère toutes les colonnes de la table précisée
dans la clause FROM de la requête.
Juste après le mot clef SELECT,
on précise les colonnes de la table qui doivent être présentées
dans la réponse.
L'utilisation du caractère étoile
ramène toutes les colonnes de la table dans la réponse. Dans
le cas contraire il faut expressément nommer chacune des colonnes
et les séparer par des virgules.
exemple 1.2
| SELECT *
FROM T_CLIENT WHERE TIT_CODE = 'M.' |
| CLI_ID TIT_CODE CLI_NOM
CLI_PRENOM CLI_ENSEIGNE
------- -------- -------- ---------- ------------ 1 M. DUPONT Alain NULL 2 M. MARTIN Marc Transports MARTIN & fils 3 M. BOUVIER Alain NULL 4 M. DUBOIS Paul NULL 5 M. DREYFUS Jean NULL 6 M. FAURE Alain Boulangerie du marché 11 M. PAUL Marcel Cie Internationale des Machines Electromécaniques 12 M. DUVAL Arsène NULL 13 M. PHILIPPE André NULL 16 M. CHABAUD Daniel NULL ... |
Notons tout de suite la présence à plusieurs reprises de la valeur "NULL" dans la cologne CLI_ENSEIGNE. Non il ne s'agit pas d'une enseigne particulière, mais simplement de l'absence d'information. Nous verrons que l'absence d'information, c'est la valeur "NULL" qui différe de la chaîne de caractère vierge ("") ou encore du zéro.
1.2
l'opérateur DISTINCT (ou ALL) 
Lorsque le moteur construit la réponse, il rapatrie toutes les lignes correspondantes, généralement dans l'ordre où il les trouve, même si ces dernières sont en double, c'est à dire qu'il récupère toutes les lignes (ALL par défaut). C'est pourquoi il est souvent nécessaire d'utiliser le mot clef DISTINCT qui permet d'éliminer les doublons dans la réponse.
exemple 1.3
| SELECT CLI_PRENOM
FROM T_CLIENT WHERE TIT_CODE = 'M.' |
CLI_PRENOM
---------- Alain Marc Alain Paul Jean Alain Marcel Arsène André Daniel ... |
| SELECT distinct CLI_PRENOM
FROM T_CLIENT WHERE TIT_CODE = 'M.' |
CLI_PRENOM
---------- Alain Alexandre André Arnaud Arsène Bernard Christian Christophe Daniel Denis ... |
Vous pouvez rajouter autant de colonnes
que vous le désirez en utilisant le mot clef AS.
En principe l'opérateur AS sert
à donner un nom à de nouvelles colonnes créées
par la requête.
exemple 1.5
| SELECT CLI_NOM as NOM, 'homme'
as SEXE
FROM T_CLIENT WHERE TIT_CODE = 'M.' |
NOM SEXE
------- ----- DUPONT homme MARTIN homme BOUVIER homme DUBOIS homme DREYFUS homme FAURE homme PAUL homme DUVAL homme PHILIPPE homme CHABAUD homme ... |
1.4
Opérateur de concaténation
L'opérateur || (double barre verticale) permet de concaténer des champs de type caractères
exemple 1.6
| SELECT TIT_CODE || ' ' || CLI_PRENOM
|| ' ' || CLI_NOM as NOM
FROM T_CLIENT |
NOM
----------------------- M. Alain DUPONT M. Marc MARTIN M. Alain BOUVIER M. Paul DUBOIS M. Jean DREYFUS M. Alain FAURE M. Paul LACOMBE Melle. Evelyne DUHAMEL Mme. Martine BOYER M. Martin MARTIN ... |
Il sert a transtyper un type de données dans un autre type. Sa syntaxe est CAST(colonne AS nouveau type). Bien entendu il faut qu'un type de donnée puisse être convertis dans un autre type (compatibilité de types) afin que le réponse ne soit pas entâché d'erreurs ou d'ommissions.
exemple 1.7
| SELECT ADR_VILLE, CAST(ADR_CP
AS INTEGER) + 1
FROM T_ADRESSE |
ADR_VILLE
ADR_CP ADR_CP + 1
------------ ------ ---------- VERSAILLES 78000 78001 MONTMAIZIN 11254 11255 PARIS 75015 75016 VERGNOLLES CEDEX 452 84524 84525 MARSEILLE 13002 13003 PARIS 75012 75013 BONNEUIL CEDEX 94152 94153 PARIS 75012 75013 PARIS 75014 75015 PARIS 75017 75018 ... |
1.6 Opérateur
mathématiques de base
On, peut utiliser les opérateurs
mathématiques de base pour combiner différentes colonnes
(,+,-, *, /,).
exemple 1.8
| SELECT CHB_ID, TRF_CHB_PRIX *
1.206 AS TARIF_TTC
FROM TJ_TRF_CHB WHERE TRF_DATE_DEBUT = '2001-01-01' |
CHB_ID TARIF_TTC
------ --------- 1 424,51 2 482,40 3 617,47 4 424,51 5 463,10 6 482,40 7 424,51 8 540,29 9 482,40 10 617,47 ... |
Il est possible de surnommer une table
dans la clause FROM, dans ce cas, la syntaxe de la partie FROM de la commande
SELECT est la suivante :
| FROM nom_de_table ou nom_de_la_vue surnom |
Nous verrons dans quel cas ce renom est
nécessaire ou obligatoire.
NOTA : certains auteurs préfère
utiliser le mot d'alias que nous rejetons car il indique souvent
un autre concept, ou de synonyme, que nous acceptons de manière
timorée...
1.8 Utilisation du caractère simple quote (apostrophe)
Lorsquun nom dun élément dune base de données (table, colonne par exemple) est identique à un mot clef du SQL, il convient de lentourer de guillemets simple (simple quote ou apostrophe). En principe, les mots réservés du SQL sont interdits pour nommer des objets du modèle physiquen de données, mais certains SGBDR l'admette...
Immaginons une table de nom JOIN, composée des champs suivants :
| NOM SELECT
DATE NOT
------- ------ ------- --- DURAND Oui 1999-11-12 F DUVAL Non 1998-01-17 M |
Exemple 1.9 : nous désirons sélectionner les colonnes SELECT et DATE lorsque la colonne NOT vaut F...
| SELECT SELECT DATE
FROM JOIN WHERE NOT = 'F' |
ERREUR ! |
| SELECT T.'SELECT', T.'DATE'
FROM 'JOIN' T WHERE 'JOIN'.'NOT' = 'F' |
Correct : on entoure les mots clefs du SQL par de simples côtes, on surnomme la table et on utilise le surnom si nécessaire (ambiguité). |
Cela est aussi nécessaire lorsque
le nom (d'une colonne ou d'une table) est composé de caractères
particuliers tels que les blancs ou autres, ce qui est a éviter.
| ORDER BY colonne1 | 1 [ASC ou DSC ] [, colonne2 | 2 [ASC ou DSC ] ... |
Cette clause permet de définir le
tri des colonnes de la réponse, soit en précisant le nom
littéral de la colonne, soit en précisant son n° d'ordre
dans l'énumération qui suit le mot clef SELECT.
ASC spécifie lordre ascendant
et DSC lordre descendant du tri. ASC ou DSC peut être omis, dans
ce cas c'est l'ordre ascendant qui est utilisé par défaut.
Bien que la clause ORDER BY ne soit pas
nécessaire, il est souvent utile de trier la réponse en fonction
des colonnes. En revanche le temps de réponse s'en ressent souvent.
Pourt spécifier l'ordre de tri,
on doit placer les noms des colonnes séparées par des virgules
juste après le mot clef "ORDER BY", dans l'ordre voulu.. On peut
aussi utiliser le rang de chaque colonne dans l'ordre spécifié
dans la clause SELECT.
Attention : le tri est un tri interne, il ne faut donc placer dans cette clause que les noms des colonnes présentées dans la clause SELECT.
Souvent, le fait de placer DISTINCT suffit, en général, à établir un tri puisque le moteur doit se livrer à une comparaison des lignes mais ce mécanisme n'est pas garantit car ce tri seffectue dans un ordre non contrôlable qui peut varier dun serveur à lautre.
exemple 1.10
| SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT ORDER BY CLI_NOM, CLI_PRENOM ou SELECT CLI_NOM, CLI_PRENOM
|
CLI_NOM CLI_PRENOM
-------- ---------- AIACH Alexandre ALBERT Christian AUZENAT Michel BACQUE Michel BAILLY Jean-François BAVEREL Frédéric BEAUNEE Pierre BENATTAR Bernard BENATTAR Pierre BENZAQUI Joël ... |
REMARQUE : les valeurs nulles sont situées en premier dans l'ordre ainsi établit.
NOTA : Un problème, qui nest
pas résolu, est de pouvoir choisir lordre des colonnes de la réponse.
Sur certains serveurs cela peut être obtenu en plaçant les
noms des colonnes à obtenir dans lordre où lon veut les
voir apparaître dans la clause SELECT, mais cette possibilité
n'est jamais garantie...
| WHERE prédicats |
Le prédicat doit contenir nimporte
quelle expression logique renvoyant une valeur vrai.
Ainsi, une requête aussi stupide
que la suivante, est supposée fonctionner :
exemple 1.11
| SELECT CLI_NOM
FROM T_CLIENT WHERE 1=1 |
CLI_NOM
------- DUPONT MARTIN BOUVIER DUBOIS DREYFUS FAURE LACOMBE DUHAMEL BOYER MARTIN ... |
Attention : la plupart des SGBDR
ne comportent pas de colonne de type booléen. Une requête
comme la première risque d'échouer.
exemple 1.12
| SELECT *
FROM TJ_CHB_PLN_CLI WHERE CHB_PLN_CLI_OCCUPE |
ERREUR !
bien que CHB_PLN_CLI_OCCUPE puisse être du booléen, la plupart des compilateur SQL n'accepte pas ce test direct. |
| SELECT *
FROM TJ_CHB_PLN_CLI WHERE CHB_PLN_CLI_OCCUPE = True |
CORRECT ...
Mais sur certains compilateur SQL il faut faire : CHB_PLN_CLI_OCCUPE = 'True' (littéral). Si le type booléen n'existe pas, alors il faut faire CHB_PLN_CLI_OCCUPE = 1 si l'on a choisi de définir les booléens comme INTEGER(1) avec 0 et 1 |
| opérateur ET | FAUX | VRAI |
| FAUX | FAUX | FAUX |
| VRAI | FAUX | VRAI |
| multiplication | 0 | 1 |
| 0 | 0 | 0 |
| 1 | 0 | 1, <> 0 |
| opérateur OU | FAUX | VRAI |
| FAUX | FAUX | VRAI |
| VRAI | VRAI | VRAI |
| addition | 0 | 1 |
| 0 | 0 | 1, <>0 |
| 1 | 1, <>0 | 2, <>0 |
Dans la clause WHERE, vous disposez de
différents opérateurs de comparaisons logiques :
| WHERE valeur1 [NOT et] = ou < ou <= ou > ou >= ou <>valeur2 [OR ou AND ...] |
exemple 1.13
| SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT WHERE CLI_NOM >= 'A' AND CLI_NOM <'E' ou SELECT CLI_NOM, CLI_PRENOM
plus lisible ! |
CLI_NOM CLI_PRENOM
------- ---------- DUPONT Alain BOUVIER Alain DUBOIS Paul DREYFUS Jean DUHAMEL Evelyne BOYER Martine DUVAL Arsène DAUMIER Amélie CHABAUD Daniel BAILLY Jean-François ... |
Attention : dans certains moteurs de requête SQL lopérateur « différend de » (<>) sécrit !=
L'opérateur IN permet de rechercher si une valeur se trouve dans un ensemble donné, quel que soit le type des valeurs de référence spécifiées (alpha, numérique, date). Bien entendu, il est possible dinverser le fonctionnement de lopérateur IN en lui adjoignant lopérateur NOT.
exemple 1.14
| SELECT TIT_CODE, CLI_NOM, CLI_PRENOM
FROM T_CLIENT WHERE TIT_CODE IN ('Mme.', 'Melle.') |
TIT_CODE CLI_NOM
CLI_PRENOM
-------- ---------- ---------- Mme. BOYER Martine Mme. GALLACIER Noëlle Mme. HESS Lucette Mme. LETERRIER Monique Mme. MARTINET Carmen Mme. DAVID Jacqueline Mme. MOURGUES Jacqueline Mme. ZAMPIERO Annick Mme. ROURE Marie-Louise Mme. DE CONINCK Patricia ... |
Le contenu de la parenthèse peut être remplacé par le resultat d'une requête possédant une colonne unique. Dans ce cas on parle de requêtes imbriquées, ce que nous verrons plus loin.
L'opérateur BETWEEN permet de rechercher si une valeur se trouve dans un intervalle donné, quel que soit le type des valeurs de référence spécifiées (alpha, numérique, date)
Ainsi, la requête vue dans l'exemple 1.13 peut s'écrire :
exemple 1.15
| SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT WHERE CLI_NOM BETWEEN 'A' AND 'E' |
CLI_NOM CLI_PRENOM
------- ---------- DUPONT Alain BOUVIER Alain DUBOIS Paul DREYFUS Jean DUHAMEL Evelyne BOYER Martine DUVAL Arsène DAUMIER Amélie CHABAUD Daniel BAILLY Jean-François ... |
NOTA : les opérateurs IN
et BETWEEN sont très pratiques dans le cas où lon désire
effectuer des requêtes où lutilisateur peut saisir une liste
de choix multiples (IN) ou une plage de valeur (BETWEEN).
L'opérateur LIKE permet deffectuer une comparaison partielle. Il est surtout employé avec les colonnes contenant des données de type alpha. Il utilise les jokers % et _ (pour cent et blanc souligné). Le joker % remplace n'importe quelle chaîne de caractères, y compris la chaîne vide. Le blanc souligné remplace un et un seul caractère.
exemple 1.16
| SELECT CLI_NOM, CLI_PRENOM
FROM T_CLIENT WHERE CLI_NOM LIKE 'B%' |
CLI_NOM CLI_PRENOM
------- ---------- BOUVIER Alain BOYER Martine BAILLY Jean-François BOUCHET Michel BEAUNEE Pierre BERGER Jean-Pierre BOURA André BENZAQUI Joël BAVEREL Frédéric BERTRAND Christophe ... |
Mais si vos données sont susceptibles de contenir un des deux caractères joker, alors il faut recourir à une séquence déchappement, à laide du mot clef ESCAPE
Cherchons les clients dont l'enseigne contient au moins un caractère blanc souligné :
exemple 1.17
| SELECT *
FROM T_CLIENT WHERE CLI_ENSEIGNE LIKE '%_%' |
CLI_ID TIT_CODE CLI_NOM
CLI_PRENOM CLI_ENSEIGNE
------ -------- ---------- ------------- ------------------------------------------ 2 M. MARTIN Marc Transports MARTIN & fils 6 M. FAURE Alain Boulangerie du marché 10 M. MARTIN Martin HERMAREX IMPORT_EXPORT 11 M. PAUL Marcel Cie Internationale des Machines Electrom... 17 M. BAILLY Jean-François Entreprise DUPONT CHAUFFAGE 24 M. CHTCHEPINE Dominique HOTEL *** DE LA GARE 26 M. GARREAU Paul IBM Corp. 34 Mme. GALLACIER Noëlle Transports GALLACIER 42 Mme. LETERRIER Monique SA AROMAX ENTREVONT 49 M. COULOMB Renaud Cabinet COULOMN et CALEMANT ... |
| SELECT *
FROM T_CLIENT WHERE CLI_ENSEIGNE LIKE '%#_%' ESCAPE '#' |
CLI_ID TIT_CODE CLI_NOM
CLI_PRENOM CLI_ENSEIGNE
------ -------- ---------- ------------- ------------------------------------------ 10 M. MARTIN Martin HERMAREX IMPORT_EXPORT |
Pour traiter ce cas, on défini « # » comme caractère déchappement. Le caractère qui suit ce caractère déchappement est donc interprété comme un caractère et non comme un joker.
NOTA : lopérateur LIKE effectue
une recherche en tenant compte de la différence entre lettres majuscules
et minuscules. Si vous voulez effectuer une recherche en ne tenant aucunement
compte de la différence entre majuscules et minuscules, il convient
dutiliser les opérateurs LOWER et UPPER (voir ci dessous).
4.1
Trantypage à l'aide de la fonction CAST
Il permet deffectuer une comparaison de données de type hétérogène par exemple entre un champ contenant des données numériques et un champ contenant des données de type chaîne de caractères
exemple 1.18
| SELECT CHB_ID, CHB_NUMERO, CHB_POSTE_TEL
FROM T_CHAMBRE WHERE CAST(CHB_POSTE_TEL AS INTEGER) / 10 > CHB_NUMERO |
| CHB_ID CHB_NUMERO CHB_POSTE_TEL
------ ---------- ------------- 1 1 101 2 2 102 3 3 103 4 4 104 5 5 105 6 6 106 7 7 107 8 8 108 9 9 109 10 10 110 |
Lopérateur CAST permet de transtyper
les valeurs contenues dans une colonne.
4.2 Mise en majuscule / Minuscule
Les opérateurs LOWER
et UPPER permettent
de mettre en majuscule ou en minuscule des chaînes de caractères
dans les requêtes.
exemple 1.19
| SELECT upper(CLI_PRENOM), lower(CLI_NOM)
FROM T_CLIENT |
CLI_NOM CLI_PRENOM
------- ---------- ALAIN dupont MARC martin ALAIN bouvier PAUL dubois JEAN dreyfus ALAIN faure PAUL lacombe EVELYNE duhamel MARTINE boyer MARTIN martin ... |
NOTA : pour effectuer une recherche
en ne tenant aucunement compte de la différence entre majuscules
et minuscules, il faut utiliser lopérateur UPPER (ou lower mais
attention à la transformation des accents !) :
exemple 1.20
| SELECT *
FROM T_CLIENT where upper(CLI_PRENOM) = upper(CLI_NOM) |
CLI_ID TIT_CODE CLI_NOM
CLI_PRENOM CLI_ENSEIGNE
------ -------- ------- ---------- ---------------------- 10 M. MARTIN Martin HERMAREX IMPORT_EXPORT |
NOTA : certains SGBDR permettent de paramétrer l'activation de la recherche systématiques des chaînes de caractères sans tenir compte de la casse. Sur d'autres, le paramétrage permet de confondre les lettre accentuées ou non...
4.3 Supprimer les blancs (ou tout autre caractères)
La fonction TRIM
permet de supprimer en tête ou en queue (ou les deux) le blanc ou
tout autre caractère spécifié.
| TRIM ([LEADING ou TRAILING ou BOTH] [caractère] FROM nom de colonne) |
Dans notre table téléphone,
nous voulons supprimer le zéro de tête des n° afin de
pouvoir les communiquer aux étrangers qui nont pas besoin de composer
ce chiffre (ils doivent simplement composer le 00 33 suivi du numéro
à 9 chiffres).
exemple 1.21
| SELECT TEL_NUMERO,
'00~33 ' || TRIM(LEADING '0' FROM TEL_NUMERO) AS TEL_INTERNATIONAL FROM T_TELEPHONE |
TEL_NUMERO
TEL_INTERNATIONAL
-------------- ----------------- 01-45-42-56-63 00~33 1-45-42-56-63 01-44-28-52-52 00~33 1-44-28-52-52 01-44-28-52-50 00~33 1-44-28-52-50 06-11-86-78-89 00~33 6-11-86-78-89 02-41-58-89-52 00~33 2-41-58-89-52 01-51-58-52-50 00~33 1-51-58-52-50 01-54-11-43-21 00~33 1-54-11-43-21 06-55-41-42-95 00~33 6-55-41-42-95 01-48-98-92-21 00~33 1-48-98-92-21 01-44-22-56-21 00~33 1-44-22-56-21 ... |
NOTA : certains serveurs SQL proposent
différentes fonctions comme LTRIM et RTRIM pour une suppression
des blancs en tête ou en queue.
La fonction SUBSTRING
permet dextraire une sous chaîne dune chaîne de caractère.
Elle a besoin de lordre du premier caractère et du nombre de caractères
sur lequel elle doit opérer.
| SUBSTRING ( nom de colonne FROMnTOm) |
exemple 1.22
| SELECT CLI_NOM, CLI_PRENOM,
SUBSTRING(CLI_PRENOM FROM 1 FOR 1) || SUBSTRING(CLI_NOM FROM 1 FOR 1) AS INITIALES FROM T_CLIENT |
CLI_NOM CLI_PRENOM INITIALES
------- ---------- --------- DUPONT Alain AD MARTIN Marc MM BOUVIER Alain AB DUBOIS Paul PD DREYFUS Jean JD FAURE Alain AF LACOMBE Paul PL DUHAMEL Evelyne ED BOYER Martine MB MARTIN Martin MM ... |
4.5 Opérateur de traitement des dates
4.5.1 Extraire un paramètre temporel d'une date
Lopérateur EXTRACT
permet dextraire depuis une date, le jour le mois ou lannée
| EXTRACT ( YEAR ou MONTH ou DAY FROM nom de colonne ) |
Dans la table des réservation on
recherche l'identifiant des chambres ayant été réservées
au cours du mois de mai de n'importe quelle année et pour 3 personnes
exemple 1.23
| SELECT distinct CHB_ID
FROM TJ_CHB_PLN_CLI WHERE EXTRACT(MONTH FROM PLN_JOUR) = 5 AND CHB_PLN_CLI_RESERVE = 1 AND CHB_PLN_CLI_NB_PERS = 3 |
CHB_ID
------ 1 5 6 8 11 12 16 17 18 20 |
NOTA : il est dommage de constater
que la fonction EXTRACT du standard SQL, souvent fort utile, est rarement
présente dans les moteurs de bases de données. Ni Access,
ni Oracle, ni Sybase, ni SQL Server en sont doté. Seul le middleware
BDE de Borland Inprise Corel permet d'exploiter pleinement cette fonction
avec les SGBDR Paradox, dBase, FoxPro, InterBase, MSSQL, Sybase, Informix,
DB2, Oracle.
Cependant il est courant de trouver des
fonctions s'en approchant : Exemple DATEPART dans SQL Server.
4.5.2 Heure et date courante
Lheure courante, la date courante et le
combiné date/heure courant peuvent être obtenu à laide
des fonctions CURRENT_DATE, CURRENT_TIME et CURRENT_TIMESTAMP
exemple 1.24
| SELECT distinct CHB_ID
FROM TJ_CHB_PLN_CLI WHERE (CHB_PLN_CLI_RESERVE = 1) AND PLN_JOUR BETWEEN CURRENT_DATE and CURRENT_DATE + 14 AND CHB_PLN_CLI_NB_PERS = 3 attention, le résultat de cette requête varie en fonction de la date à laquelle vous l'exécutez ! |
CHB_ID
------ 1 5 6 8 11 12 16 17 18 20 |
Attention : la plupart des
SGBDR n'acceptent pas encore cette version normalisée des fonctions
de recherche de temps courant. Voici les fonctions spécifiques aux
différents serveurs SQL :
| Oracle | SYSDATE() |
| Sybase | GETDATE() |
| SQLServer | GETDATE() |
| Access | NOW() |
| MySQL | NOW() |
| Paradox (QBE) | TODAY |
Il est possible de réaliser des comptages statistiques sur les colonnes, à l'aide des opérateurs AVG (moyenne), MAX (maximum), MIN (minimum), SUM (total), COUNT (nombre). On les appelent aussi fonctions d'aggrégations.
exemple 1.25
| SELECT AVG(TRF_CHB_PRIX) as MOYENNE,
MAX(TRF_CHB_PRIX) as MAXI, MIN(TRF_CHB_PRIX) as MINI, SUM(TRF_CHB_PRIX) as TOTAL, COUNT(TRF_CHB_PRIX) as NOMBRE FROM TJ_TRF_CHB WHERE TRF_DATE_DEBUT = '2001-01-01' |
MOYENNE MAXI
MINI TOTAL
NOMBRE
-------- -------- -------- ---------- ------ 406,74 F 512,00 F 352,00 F 7 728,00 F 19 |
On peut s'étonner que les opérateurs
statistiques VARIANCE ou STDDEV (écart type) soient
rarement présent dans les SGBDR car il s'agit de fonctions statistiques
qui possède une grande utilité. Mais la norme SQL 92 ne les
a pas retenu. Cependant, ils existent notamment dans Oracle. Cela est d'autant
plus étonnant que les techniques modernes du dataWareHouse et en
particulier le DataMining, utilisent ces fonctions dans le cadres de calculs
statistiques d'hypercubes (techniques OLAP et ROLAP).
4.7 Autres
fonctions normalisées
| BIT_LENGTH | Taille d'une colonne de type BIT ou BIT VARYING (nombre de bits) |
| CHAR_LENGTH | Taille d'une colonne de type caractère (nombre de caractères) |
| OCTET_LENGTH | Taille d'une colonne de type caractère (nombre d'octets) |
| CURRENT_DATE | Date en cours |
| CURRENT_TIME | Heure en cours |
| CURRENT_TIMESTAMP | Date et heure en cours |
| CONVERT | Conversion paramétrèe d'une chaîne de caractères |
| POSITION | Position d'une chaîne de caractères dans une sous chaîne |
| TRANSLATE | Traduction d'une chaîne de caractères dans un format spécifié |
4.8 Autres opérateurs mathématiques
Les opérateurs ci dessous peuvent être implémentés dans différents moteurs.
| ABS | valeur absolue |
| MOD | modulo |
| SIGN | signe |
| SQRT | racine carrée |
| CEIL | plus petit entier |
| FLOOR | plus grand entier |
| ROUND | arrondi |
| TRUNC | tronqué |
| EXP | exponentielle |
| LN | logarithme népérien |
| LOG | logarithme décimal |
| POWER | puissance |
| COS | cosinus |
| COSH | cosinus hyperbolique |
| SIN | sinus |
| SINH | sinus hyperbolique |
| TAN | tangente |
| TANH | tangente hyperbolique |
| PI | constante Pi |
Certains sont rarement implémentés du fait que les SGBDR sont axés sur linformatique de gestion, la collecte et le traitement dinformations et non le calcul mathématique.
Attention : le nom de certains de
ces opérateurs peut différer dun SGBDR à lautre.
4.9
Autres opérateurs de traitement des chaînes de caractères
| CONCAT | concaténation : équivalent
du || - Nota : utiliser de préférence || plus standard.
Le + entre colonne alphanumérique peut aussi souvent être utilisé comme opérateur de concaténation, préférez de toutes façons || |
| INITCAP | initiales en lettres capitales |
| LPAD | complément ou troncature à n position à gauche |
| LTRIM / RTRIM | suppression en tête/queue dune chaîne |
| REPLACE | remplacement |
| RPAD | complément ou troncature à n position à droite |
| SOUNDEX | code de consonnance Attention : phonétique souvent anglaise |
| INSTR | Position dune chaîne dans une sous chaîne |
| LENGTH | longueur de la chaîne |
| TO_CHAR | numérique sous forme littérale Attention : souvent en anglais |
| ASCII | code ASCII d'un caractère |
| CHR | caractère dont le code ASCII est donné |
Attention : le nom de certains de
ces opérateurs peut différer dun SGBDR à lautre.
4.10
Autres opérateurs sur les valeurs temporelles
| ADD_MONTH | ajoute des mois à une date |
| LAST_DAY | renvoi le n° du dernier jour dun mois dune date |
| MONTH_BETWEEN | nombre de mois entre deux dates |
| NEXT_DAY | date du prochain jour dun nom donné |
| TO_CHAR | date sous forme littérale Attention : souvent en anglais |
| GREATEST | la plus grande des valeurs dun ensemble |
| LEAST | la plus petite des valeurs dun ensemble |
| DECODE | renvoi la valeur situé en n-ième position |
5 Traitement des valeurs nulles
La valeur nulle pose une quantité de problèmes et nous allons dans ce paragraphe soulever un coin du voile, que nous traiterons un peu plus tard dans le cas général de la recherche des occurrences dinexistence.
5.1 Le null n'est ni la chaîne vide, ni le zéro
Recherchons les clients qui n'ont pas d'enseigne
exemple 1.26
| SELECT CLI_ID, CLI_NOM
FROM T_CLIENT WHERE CLI_ENSEIGNE = '' |
CLI_ID CLI_NOM
------- -------- ... |
Le problème des « null » a été bien décrit dans le livre de Joe Celko intitulé « SQL avancé » publié par Thomson International Publishing. Joe Celko pose la question en ces termes : labsence dinformation est-elle due à son ignorance ou à son impertinence ? Pourquoi donc ne pas faire de différence entre la couleur du toit dune voiture qui nest pas connue, et la couleur du toit dune moto qui nest pas applicable Certains logiciens de lalgèbre relationnel sont même allés plus loin en proposant différentes valeurs pour gérer les différents cas, en distinguant des cas très différents : le « null », le « inconnu » et le « inapplicable »...
Il y a donc un véritable dilemme a utiliser des requêtes en se basant sur des critères dabsence de valeur et il faut toujours faire très attention aux clauses qui utilisent des références aux valeurs nulles, suivant ce que lon veut obtenir. Dautant plus que les valeurs « null » se propagent dans les calculs.
Voici un extrait de la table T_LIGNE_FACTURE
| LIF_ID FAC_ID
LIF_QTE LIF_REMISE_POURCENT LIF_REMISE_MONTANT
LIF_MONTANT LIF_TAUX_TVA
-------- -------- --------- ------------------- ------------------ ----------- ------------ 1 1 1,00 15,00 NULL 320,00 F 18,60 2 3 1,00 NULL 50,00 F 250,00 F 18,60 3 3 1,00 NULL 50,00 F 320,00 F 18,60 4 3 1,00 NULL 50,00 F 240,00 F 18,60 5 5 1,00 NULL NULL 320,00 F 18,60 6 5 1,00 NULL NULL 220,00 F 18,60 7 7 1,00 NULL NULL 220,00 F 18,60 8 7 1,00 NULL NULL 250,00 F 18,60 9 7 1,00 NULL NULL 320,00 F 18,60 10 7 1,00 NULL NULL 270,00 F 18,60 ... |
La requête pour FAC_ID = 3 est la
suivante :
exemple 1.27
| select FAC_ID,
sum (LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100)) AS TOTAL_FAC, sum((LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100)) * (LIF_TAUX_TVA / (100 + LIF_TAUX_TVA))) AS TOTAL_TAXES from T_LIGNE_FACTURE where FAC_ID = 3 group by FAC_ID |
| FAC_ID TOTAL_FAC TOTAL_TAXES
------ --------- ----------- 3 NULL NULL |
NOTA : en général, pour se sortir de ce mauvais pas, on peut, lors de la création de la base de données, obliger tous les champs de type numérique (réels ou entiers) a ne pas accepter la valeur nulle et prendre par défaut la valeur zéro...
Attention : larithmétique des nuls est assez particulière Souvenez vous toujours que les « null » se propagent. Cela est vrai pour les numériques, les dates mais aussi pour les chaînes de caractères. Ainsi SQL opère une distinction entre une chaîne de caractère vide et un champ non renseigné. Dans le cas de la concaténation dun champ « null » et dun champ proprement renseigné, la valeur renvoyée sera nulle !!!
5.2
Opérateurs de traitement des valeurs nulles
La norme 92 spécifie une comparaison
et différents opérateurs sur les valeurs nulles :
IS NULL / IS NOT NULL
: teste si la valeur est nulle ou non nulle.
COALESCE() qui recherche
la première valeur non nulle dans un ensemble
NULLIF une fonction branchement
: si la valeur du champ correspond à la valeur en paramètre,
renvoi null, sinon renvoi la valeur
| COALESCE ( valeur1, valeur2 [, valeur3] ... ) |
| NULLIF ( nom_de_colonne, valeur) |
| ISNULL ( nom_de_colonne, valeur) |
NOTA : ISNULL (en un seul mot) est une autre fonction de branchement que l'on rencontre parfois (renvoi une valeur si la valeur est nulle). Dans la même veine, NVL ou VALUE sont des expressions équivalentes à COALESCE que l'on rencontre sur certains SGBDR.
La requête précédente
s'exprime, à l'aide de l'opérateur ISNULL :
exemple 1.28
| select FAC_ID,
sum (LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0)) * (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100)) AS TOTAL_FAC, sum((LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0)) * (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100)) * (LIF_TAUX_TVA / (100 + LIF_TAUX_TVA))) AS TOTAL_TAXES from T_LIGNE_FACTURE where FAC_ID = 3 group by FAC_ID |
FAC_ID TOTAL_FAC TOTAL_TAXES
------ --------- ----------- 3 810.0 127.03 |
NOTA : En règle générale,
dès que l'on traite des colonnes contenant des valeurs monétaires
ou numériques, il est bon de faire en sorte que la colonne soit
obligatoire et que par défaut elle soit renseignée à
zéro.
Sinon, il faudra faire un usage systématique
des fonctions NULLIF ou COALESCE dans tous les calculs et cela grèvera
les performances d'exécution !
C'est l'opérateur NOT qui
réalise la négation de valeurs et inverse la valeur logique
d'un prédicat.
L'opérateur NOT peut être
combiné avec la plupart des opérateurs de comparaison. Mais
il devient très intéressant lorqu'il est combiné aux
opérateurs IN, BETWEEN, LIKE et NULL
Recherchons par exemples toutes les chambres
permettant de recevoir au moins 3 personnes, ne comportant pas le chiffre
4 (chiffre de la mort au japon) ni les chambres portant les n°
7 et 13 pour un client particulièrement supersticieux...
exemple 1.29
| SELECT CHB_ID, CHB_NUMERO, CHB_COUCHAGE
FROM T_CHAMBRE WHERE NOT (CAST(CHB_NUMERO AS VARCHAR(10)) LIKE '%4%') AND CHB_NUMERO NOT IN ('7', '13') AND CHB_COUCHAGE >= 3 |
CHB_ID CHB_NUMERO
CHB_COUCHAGE
----------- ---------- ------------ 1 1 3 5 5 3 6 6 5 8 8 3 11 11 3 12 12 3 15 16 3 16 17 5 17 18 3 19 20 3 |
Nous verrons que le NOT IN est particulièrement précieux dans les requêtes imbriquées, c'est à dire les requêes multitables.
Nous voulons maintenant le nom des clients
qui ne commence pas par 'DU' :
exemple 1.30
| SELECT CLI_NOM
FROM T_CLIENT WHERE CLI_NOM NOT LIKE 'DU%' |
CLI_NOM
-------------------------------- MARTIN BOUVIER DREYFUS FAURE LACOMBE BOYER MARTIN PAUL PHILIPPE PIERRELAYE ... |
7 Les branchements dans le SQL
Branchement CASE
###
Voici les différences entre les
moteurs des bases de données
|
SGBDR
Fonctions |
9 |
2000 |
Adaptive |
7 |
8 |
|
|| (concaténation) |
|
|
|
|
|
|
CAST |
|
|
|
|
TO_NUM / TO_DATE) |
|
LIKE / ESCAPE / % / _ |
|
|
|
|
|
|
TRIM |
|
|
|
|
|
| SUBSTRING |
|
|
|
|
|
|
LOWER / UPPER |
|
|
|
|
|
|
EXTRACT |
|
|
|
|
|
|
CURRENT_DATE / CURRENT_TIME / CURRENT_TIMESTAMP |
(TODAY, NOW en QBE) |
|
|
|
|
|
AVG / MAX / MIN / SUM / COUNT |
|
|
|
|
|
| VARIANCE / STDEV |
|
|
|
|
|
| ABS / MOD / SIGN / SQRT / PI |
|
|
|
|
|
| CEIL / FLOOR / ROUND / TRUNC |
|
|
|
|
|
| EXP / LN / LOG / POWER |
|
|
|
|
|
| COS / SIN / TAN |
|
|
|
|
|
| COSH / SINH / TANH |
|
|
|
|
|
| INITCAP / LPAD / RPAD / REPLACE |
|
|
|
|
|
| SOUNDEX / TRANSLATE / INSTR / LENGTH |
|
non (LOCATE) / non |
non (PATINDEX) / non (LEN) |
non (PATINDEX) / non (LEN) |
/ non |
| TO_CHAR pour numérique / pour date |
|
|
|
non (CONVERT) |
|
| ASCII / CHR |
|
|
|
|
|
| ADD_MONTH / MONTH_BETWEEN |
|
|
|
non (DATEDIFF) |
|
| LAST_DAY / NEXT_DAY |
|
|
|
|
|
| GREATEST / LEAST / DECODE |
|
|
|
|
|
|
IS NULL / COALESCE / NULLIF |
|
|
|
|
|
(1) Contrairement à la norme, lopérateur like dAccess comme celui de SQL Server, ne fait aucune différence entre les majuscules et les minuscules (sauf paramétrage spécifique du serveur). Quand à lopérateur « escape » il nest tout simplement pas implémenté dans Access, mais il semble possible de le contourner en utilisant une syntaxe à base de jokers spécifique à Access. Les jokers dAccess ne sont pas standards : * remplace le % et ? remplace le _.
(2) La tangente hyperbolique est bien implémentée mais elle ne donne apparement pas les bons résultats... !
(3) La conversion est automatique dans Oracle
Indispensable : "crash me" pour une comparaison exhaustive des implémentations du SQL sur les SGBDR, MySQL, Access, Adabas, IBM DB2, EMPRESS, Informix, Microsoft SQL server, mSQL server, Oracle, PostgreSQL, SOLID Server, Sybase...
Curieusement Paradox na pas implémenté les opérateurs de récupération des valeurs temporelles courantes alors quils existent en QBE !. Il est facile de sen passer en passant la date ou lheure courante en paramètre de la requête, mais tout de même
En ce qui concerne MS Access, on ne peut quêtre frappé par le fait que la plupart des fonctions de base des requêtes sont incompatible avec la norme. Par exemple le LIKE utilise des jokers différent : * remplace le % et ? remplace le _. Cela oblige à utiliser une syntaxe propriétaire qui rend la portabilité des requêtes très difficile dun SGBDR à lautre. Mais ne serait-ce pas là une tactique voulue ??? Autre inconvénient il ne sait pas traiter le NOT BETWEEN !!!
Plus curieux la plupart des SGBDR n'accepte pas l'opérateur de concaténation ||!
Dans Sybase comme SQL Server la fonction modulo s'exprime sous la forme d'un caractère '%' d'ou d'énormes possibilité de confusions entre les caractères joker du like, comme le calcul de pourcentage... A quand une release sur ce sujet ???
Le SGBDR le plus proche de la norme est
celui de Sybase, suivi de SQL Server. Le plus complet par son jeu de fonction
est sans doute Oracle.
