Le SQL de a à Z (Livre)

LE SQL de A à Z1ere partie - bases de données, SQL et types de données

Voici le premier chapitre d'un long article concernant le SQL et son implémentation dans les SGBD les plus courants (Paradox, Access, Oracle, SQL Server, Sybase…).Tout le monde connaît le SQL, mais l’avez-vous suffisamment fréquenté pour en tirer toute l’essence ?Dans cet article, nous allons voir l’historique de ce langage et ses différentes composantes.Dans les suivants, nous verrons dans l’ordre la manipulation des données à l’aide des commandes

SELECT, INSERT, UPDATE, DELETE, puis la création des structures de bases de données avec CREATE, ALTER, DROP et enfin l’attribution et la révocation des droits (GRANT et REVOKE).Mais au fait, le SQL, bientôt mort ?… Pas si sûr !

Parler du SQL de nos jours comme d’une nouveauté, serait une gageure… cependant, ne faut-il pas voir en cet indestructible langage, une tentative un peu tardive, mais souhaitable, au travers des différents middleware disponibles, de standardisation d’un mode d’interrogation des données ?Force est de constaté que même les bases de données objet et le web se mettent au SQL. Le poids du passé sans doute…

Mais alors que faire ? Squeezer SQL ou s’en accommoder ?

Il y a plus d’une vingtaine d’années le COBOL était, disait-on, assuré d’une mort certaine et à court terme. Aujourd’hui le défunt est encore haletant bien que défraîchi. En sera t-il de même pour le SQL ? Tout le laisse supposer !

1 SQL un langage ?Vous trouverez des compléments d'information sur le sujet aux pages 29 à 51 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Et d’abord, SQL est t-il un vrai langage ?

Si l’on doit accepter ce mot au sens informatique du terme, il semble difficile de dire oui tant SQL est loin de la structure et des possibilités d’un langage de programmation courant. Point de variable, point de procédure ou de fonction… Pourtant il s’agit bien de former des phrases qui seront compilées afin d’exécuter des traitements.Même si SQL s’est doté au fil du temps d’extensions, comme la possibilité de paramétrer les requêtes, il y a des lacunes importantes qui sont autant de frein à sa pénétration. Par exemple SQL ne sait être récursif (1) alors que ce mode d’exécution est nécessaire pour résoudre une frange importante de problèmes, notamment ceux pour traiter les arbres ou les graphes.

En fait SQL est un langage de type " déclaratif ". On spécifie ce que l’on veut obtenir ou faire et c’est la machine qui décide comment elle doit l’exécuter. Pour certains, SQL est perçu comme un pis-aller tandis que d’autres préfèrent l’éviter ou retarder au plus l’inéluctable moment ou son apparition sera incontournable.

La différence fondamentale entre les langages courants comme C ou Pascal, qui sont des langages procéduraux, réside dans le fait qu’avec ces derniers, vous indiquez l’ensemble des instructions nécessaires à traiter un problème. Vous gardez ainsi une certaine maîtrise sur le cycle d’exécution et l’enchaînement des différentes tâches d’un programme vous est parfaitement connu. En revanche, dans SQL vous n’avez, et d’ailleurs ne devez, pas avoir la moindre idée de comment la machine exécute votre demande, ni même dans quel ordre elle décompose le traitement de la requête en différentes tâches, ni d’ailleurs même comment elle les synchronise.

Lors d’une extraction concernant plusieurs tables, le moteur relationnel pourrait parfaitement lancer plusieurs traitements en parallèles pour extraire les données de chacune des tables, puis effectuer les jointures des différentes tables résultantes en fonction de l’ordre de terminaison des extractions…

http://sqlpro.developpez.com/bookSQL.html

http://sqlpro.developpez.com/#Note1

C’est pourquoi, les moteurs relationnels incluent des modules d’optimisation logique et parfois statistique.

Ainsi un optimiseur logique préférera traiter en premier les clauses excluant de la réponse un maximum de données, tandis qu'un optimiseur statistique commencera par traiter les tables de plus faible volume.

Exemple :

Soit une table contenant le personnel de la société " VIVE LE SQL " et une

autre contenant les salaires mensuels desdits employés.

" VIVE LE SQL " compte 1000 employés soit autant de lignes dans la table du

personnel, et la moyenne de durée d’emploi étant de 6 ans, la table des

salaires compte donc environ 72 000 lignes.

Soit la requête suivante : rechercher les employés dont le nom commence par

Z ou qui sont domiciliés à Trifouilly et qui ont eu au moins un salaire

supérieur à 50 000 Francs.

Un optimiseur logique commencera immanquablement par traiter la table

des salaires puis celle des employés. Un optimiseur statistique commencera par traiter les employés puis

les salaires.

Nous pauvres humains, aurions immédiatement traité la table des salaires,

car à ce niveau d’émolument il ne devrait pas y avoir grand monde dans la

table des salariés… Eh bien, croyez moi ou pas… certains optimiseurs statistiques auraient eût

la même appréciation ! En effet les plus performants ne se contentent pas

seulement de conserver le nombre de lignes ou le volume des données d’une

table, mais aussi les moyennes, médianes, maximums et minimums de certaines

colonnes, voir même le nombre de valeurs distinctes (indice de dispersion)

ainsi que le nombre d’occurrences des valeurs les plus fréquentes,

notamment pour les colonnes pourvues d’index… C'est le cas en particulier

du SGBDR INGRES.

Bref, pour conclure cette brève présentation de SQL, nous pouvons affirmer que, malgré ses défauts, et en attendant un futur langage objet pour l’interrogation des données, incluant tous les mécanismes dynamiques et bien entendu la récursivité (1), il nous faut continuer à utiliser sagement SQL, du mieux que nous pouvons, en rendant ainsi hommage a quelques-uns de ses créateurs….

2 SQL une histoire…

Nous sommes en 1970. Le docteur Codd, un chercheur d’IBM à San José, propose une nouvelle manière d’aborder le traitement automatique de l’information, se basant sur la théorie de l’algèbre relationnel (théorie des ensembes et logique des prédicats). Cette proposition est faite afin de garantir une plus grande indépendance entre la théorie et l’implémentation physique des données au sein des machines.


C’est ainsi que naîtrons, vers la fin des années 70, les premières applications basées sur la proposition de Ted Codd, connues de nos jours sous l’acronyme SGBDR (signifiant Système de Gestion de Bases de Données Relationnelles).

Dans cette même période, Peter CHEN tente une approche pragmatique et conceptuelle du traitement automatique des données en proposant le modèle Entité-Association comme outil de modélisation. Nous sommes en 1976.

En parallèle, différents chercheurs travaillent à réaliser ce que seront les SGBDR d’aujourd’hui : citons entre autres l’équipe de Gene Wong à l’université de Berkeley qui entame le projet INGRES en 1972, et en 1975 propose le langage QUEL comme outil d’interrogation des données (aucun ordre de mise à jour des données n’y figure).De même à Noël 1974 démarre chez IBM le projet qui portera le nom de System R, et donnera comme langage d’interrogation SEQUEL (Structured English QUEry Langage) en 1976.

Lors d’une conférence internationale à Stockolm (IFIP Congres), Larry Ellison, dirigeant d’une petite entreprise appelée Software Development Laboratories entend parler du projet et du langage d’interrogation et de manipulation des données. Il rentre en contact avec les chercheurs d’IBM. Quelques années après, Larry Ellison sort le premier SGBDR commercialisé et change le nom de sa société. Elle s’appellera désormais Oracle…

IBM, pour sa part, sortira une version commerciale bien après celle d’Oracle, ce qui fera dire à l’un des responsables du projet que " cela vous montre combien de temps il faut à IBM pour faire n’importe quoi… ". Finalement la première installation de System R est réalisée en 1977 chez Pratt & Whitney, et déjà les commandes du langage d’interrogation se sont étoffées de quelques judicieuses techniques préconisées par Oracle, comme la gestion des curseurs.

Au même moment apparaissent d’autres langages d’interrogation comme QBE de Zloof (IBM 1977) repris pour Paradox par Ansa Software, ou REQUEST de Fred Damerau (basé sur le langage naturel) ou encore RENDEZ VOUS (1974) de l’équipe de Ted Codd ainsi que SQUARE (Boyce 1975).

Mais le point critique du langage SEQUEL porte sur l’implémentation du… rien ! Ou plutôt devrais-je dire du nul !!!

Finalement la première réalisation connue sous le nom de SQL (82) voit le jour après l’arrivée de DB2, une avancée significative du System R d’IBM datant de mars 1979.

On peut donc dire que le langage SQL est né en 1979, mais baptisé SQL en 1982. En définitive SQL a vingt ans, et, comme le dit la chanson… on n’a pas tous les jours 20 ans…

Et 20 ans pour les uns ça suffit !, pour les autres ça se fête….

2.1 SQL une norme

Notons que SQL sera normalisé à quatre reprises : 1986 (SQL 86 - ANSI), 1989 (ISO et ANSI) et 1992 (SQL 2 - ISO et ANSI) et enfin 1999 (SQL:1999 - ISO) souvent apellé à tort (y compris par moi même !) SQL 3. A ce jour, aucun SGBDR n'a implémenté la totalité des spécifications de la norme actuellement en vigueur. Mais je dois dire que le simple (?) SELECT est argumenté de quelques 300 pages de spécifications syntaxiques dans le dernier document normatif...

Néanmoins, la version SQL 2 (1992) est la version vers laquelle toutes les implémentations tendent. C'est pourquoi nous nous baserons sur SQL 2.

A noter, la future norme SQL:2003, sortira en juillet 2003.

2.2 SQL un standard

Même si SQL est considéré comme le standard de toutes les bases de données relationnelles et commercialisées, il n'en reste pas moins vrai que chaque éditeur tend à développer son propre dialecte, c'est à dire à rajouter des éléments hors de la norme. Soit fonctionnellement identiques mais de syntaxe différentes (le LIKE d'Access en est l'exemple le plus stupide !) soit fonctionnellement nouveau (le CONNECT BY d'Oracle pour la récursivité par exemple). Il est alors très difficile de porter une base de données SQL d'un serveur à l'autre. C'est un moindre mal si l'on a respecté au maximum la norme, mais il est de notoriété absolue que lorsque l'élément normatif est présent dans le SGBDR avec un élément spécifique ce sera toujours l'élément spécifique qui sera proposé et documenté au détriment de la norme !

Exemple, la fonction CURRENT_TIMESTAMP, bien présente dans MS SQL Server, n'est pas spécifié dans la liste des fonctions temporelle de l'aide en ligne !!!

De plus, le plus petit dénominateur commun entres les 4 poids lourds de l'édition que dont Oracle, Sybase (ASE), Microsoft (SQL Server) et IBM (DB2) est tel qu'il est franchement impossible de réaliser la moindre base de données compatible entre ces différentes implémentation, sauf à n'y stocker que des données numériques !!!

Pour chaque SGBDR, on parle alors de dialecte. Il y a donc le dialecte SQL d'Oracle, le dialecte SQL de Sybase...etc.

Dans un excellent article, Peter Gulutzan fait le point sur l'essentiel de ce qui est normatif chez les principaux éditeurs de SGBDR : http://www.dbazine.com/gulutzan3.htmlEt fustige leur comportement, similaire à celui des constructeurs d'ordinateurs en matière d'OS UNIX incompatible et et qui maintenant pleurent à chaudes larmes devant le rouleau compresseur "Linux" !Une autre étude, par Michael M. Gorman montre que la volonté de ces éditeurs de SGBDR et leur intérêts mercantiles à courte vue est incompatible avec le respect des standards... A lire donc : http://www.tdan.com/i016hy01.htm

http://www.dbazine.com/gulutzan3.html

http://www.tdan.com/i016hy01.htm

Nous avons aussi démontré qu'une même table créée sur différents SGBDR avec les mêmes données n'avait par forcément le même comportement ua regard du simple SELECT du fait des jeux de caractères et collations par défaut. Or tous les SGBDR ne sont pas paramétrables à ce niveau et ceux qui le sont, ne présentent pas en général les mêmes offres en cette matière. A lire : Une question de caractères...

Enfin , pour tous ceux qui veulent connaître la norme comparée au dialecte de leur SGBDR favori, il suffit de lire le tableau de comparaison des fonctions de SQL : Toutes les fonctions de SQL

3 Remarques préliminaires sur les SGBDR

Avant tout, nous supposerons connu les notions de "bases de données", "table", "colonne", "ligne", "clef", "index", "intégrité référentielle" et "transaction", même si ces termes, et les mécanismes qu'ils induisent seront plus amplement décrits au fur et à mesure de votre lecture.

Voici quelques points qu'il convient d'avoir à l'esprit lorsque l'on travaille sur des bases de données :

Il existe une grande indépendance entre la couche abstraite que constitue le SGBDR et la couche physique que sont le ou les fichiers constituant une base de données. En l'occurrence il est déraisonnable de croire que les fichiers sont arrangés dans l'ordre des colonnes lors de la création des tables et que les données sont rangées dans l'ordre de leur insertion ou de la valeur de la clef.

Il n'existe pas d'ordre spécifique pour les tables dans une base ou pour les colonnes dans une table, même si le SGBDR en donne l'apparence en renvoyant assez généralement l'ordre établi lors de la création (notamment pour les colonnes). Par conséquent les tables, colonnes, index…. doivent être repérés par leur nom et uniquement par cet identifiant.

Les données sont systématiquement présentées sous forme de tables, et cela quel que soit le résultat attendu. Pour autant la table constituée par la réponse n'est pas forcément une table persistante, ce qui signifie que si vous voulez conserver les données d'une requête pour en faire usage ultérieurement, il faudra créer un objet dans la base (table ou vue) afin d'y placer les données extraites.

La logique sous-jacente aux bases de données repose sur l'algèbre relationnel lui-même basé sur la théorie des ensembles. Il convient donc de penser en terme d'ensemble et de logique et non en terme d'opération de nature atomique. A ce sujet, il est bon de se rappeler l'usage des patates (ou diagrammes de Wen) lorsque l'on "sèche" sur une requête.

Du fait de l'existence d'optimiseurs, la manière d'écrire une requête à peu d'influence en général sur la qualité de son exécution. Dans un premier temps il vaut mieux se consacrer à la résolution du problème que d'essayer de savoir si la clause "bidule" est plus gourmande en ressource lors de son exécution que la clause "truc". Néanmoins l’optimisation de l’écriture des requêtes sera abordée dans un article de la série.

http://sqlpro.developpez.com/SQL_AZ_7.html#SCHEMA4

http://sqlpro.developpez.com/SQL_AZ_F.htm

http://sqlpro.developpez.com/SQL_AZ_D.html

http://sqlpro.developpez.com/SQL_AZ_D.html

4 Les subdivisions du SQLLe SQL comporte 5 grandes parties, qui permettent : la définition des éléments d'une base de données (tables, colonnes, clefs, index, contraintes…), la manipulation des données (insertion, suppression, modification, extraction…), la gestion des droits d'accès aux données (acquisition et révocation des droits), la gestion des transactions et enfin le SQL intégré. La plupart du temps, dans les bases de données "fichier" (dBase, Paradox...) le SQL n'existe qu'au niveau de la manipulation des données, et ce sont d'autres ordres spécifiques qu'il faudra utiliser pour créer des bases, des tables, des index ou gérer des droits d'accès.Certains auteurs ne considèrent que 3 subdivisions incluant la gestion des transactions au sein de la manipulation des données... Cependant ce serait restrindre les fonctionalités de certains SGBDR capable de gérer des transactions comprenant aussi des ordres SQL de type défintion des données ou encore gestion des droits d'accès !4.1 DDL : " Data Definition Language "

C'est la partie du SQL qui permet de créer des bases de données, des tables, des index, des contraintes…

Elle possède les commandes de base suivante :

Qui permettent respectivement de créer, modifier, supprimer un élément de la base.

4.2 DML : " Data Manipulation Language "

C'est la partie du SQL qui s'occupe de traiter les données. Elle comporte les commandes de base suivantes :

Qui permettent respectivement d'insérer, de modifier de supprimer et d'extraire des données.

4.3 DCL : " Data Control Language "

C'est la partie du SQL qui s'occupe de gérer les droits d'accès aux tables. Elle comporte les commandes de base suivantes :

Qui permettent respectivement d'attribuer et de révoquer des droits.4.4 TCL : " Transaction Control Language "

C'est la partie du SQL chargé de contrôler la bonne exécution des transactions

Elle comporte les commandes de base suivantes :

Qui permettent de gérer les propriétés ACIDdes transactions. 4.5 SQL intégré : " Embedded SQL "

Il s'agit d'éléments procéduraux que l'on intégre à un langage hôte :

Le terme "ACID", ne fait pas référence, loin s'en faut au LSD, mais plus basiquement aux termes suivants

A Atomicité : une transaction s'effectue ou pas (tout ou rien), il n'y a pas de demi-mesure. Par exemple l'augmentation des prix de 10% de tous les articles d'une table des produits ne saurait être effectuée partiellement, même si le système connaît une panne en cours d'exécution de la requête. C Cohérence : le résultat ou les changements induits par une transaction doivent impérativement préserver la cohérence de la base de données. Par exemple lors d'une fusion de société, la concaténation des tables des clients des différentes entités ne peut entraîner la présence de plusieurs client ayant le même identifiant. Il faudra résoudre les conflits portant sur le numéro de client avant d'opérer l'union des deux tables.I Isolation : les transactions sont isolées les unes des autres. Par exemple la mise à jour des prix des articles ne sera visible pour d'autres transactions que si ces dernières ont démarrées après la validation de la transaction de mise à jour des données. Il n'y aura donc pas de vue partielle des données pendant toute la durée de la transaction de mise à jour.D Durabilité : une fois validée, une transaction doit perdurer, c'est à dire que les données sont persistantes même s'il s'ensuit une défaillance dans le système. Par exemple, dès lors qu'une transaction a été validée, comme la mise à jour des prix, les données modifiées doivent être physiquement stockées pour qu'en cas de panne, ces données soient conservées dans l'état où elles ont été spécifiées à la fin de la transaction.

Voici ce que disent, Sébastien BRAU, Christian CHANE

VASCONCELOS sur les propriétés ACID d'un SGBDR :

Atomicité : si tout se passe correctement, les actions de la transaction sont toutes validées, sinon on

retourne à l’état initial.

L’unité de travail est indivisible. Une transaction ne peut être partiellement effectuée.

Cohérence : le passage de l’état initial à l’état final respecte la cohérence de la base.

ci n’est pas terminée.

Une transaction n’est pas affectée par le

Durabilité

Rendre transparentes la complexité et la localisation des traitements et des données, tout en assurant

un bon niveau de performance, telles sont aujourd’hui le

critères sur lesquels s’arrêtent leurs choix.

Pour assurer l'ensemble de ces fonctions de base, les SGBDR utilisent le principe de la journalisation : un fichier dit "journal", historise toutes les transactions que les utilisateurs effectuent et surtout leur état : en cours, validé ou annulée. Une mise à jour n'est rélemment effectué que si la transaction aboutie. Ainsi en cas de panne du système, une relecture du journal permet de resynchroniser la base de données pour assurer sa cohérence. De même en cas de "RollBack", les instructions de la transaction sont lues "à l'envers" afin de rétablir les données telles qu'elles devaient être à l'origine de la transaction.

5 Implémentation physique des SGBDRIl existe à ce jour, deux types courant d'implémentation physique des SGBD relationnels. Ceux qui utilisent un service de fichiers associés à un protocole de réseau afin d'accéder aux données et ceux qui utilisent une application centralisée dite serveur de données. Nous les appellerons SGBDR "fichier" et SGBDR client/serveur (ou C/S en abrégé).

5.1 SGBDR "fichier"

Le service est très simple à réaliser : il s'agit de placer dans une unité de stockage partagée (en général un disque d'un serveur de réseau) un ou plusieurs fichiers partageables. Un programme présent sur chaque poste de travail assure l'interface pour traiter les ordres SQL ainsi que le va et vient des fichiers de données sur le réseau. Il convient de préférer des SGBDR à forte granularité au niveau des fichiers. En effet plus il y a de fichiers pour une même base de données et moins la requête encombrera le réseau, puisque seuls les fichiers nécessaires à la requête seront véhiculés sur le réseau. Ces SGBDR "fichier" ne proposent en général pas le contrôle des transactions, et peu fréquemment le DDL et le DCL. Ils sont, par conséquent, généralement peu ACID !Les plus connus sont ceux qui se reposent sur le modèle XBase. Citons parmi les principaux SGBDR "fichier" : dBase, Paradox, Foxpro, BTrieve, MySQL, … Généralement basés sur le modèle ISAM de fichiers séquentiels indexés.Avantage : simplicité du fonctionnement, coût peu élevé voir gratuit, format des fichiers ouverts, administration quasi inexistante.Inconvénient : faible capacité de stockage (quoique certains, comme Paradox, acceptent 2 Go de données par table !!!), encombrement du réseau, rarement de gestion des transactions, faible nombre d’utilisateurs, faible robustesse, cohérence des données moindre.Access se distingue du lot en étant assez proche d’un serveur SQL : pour une base de données, un seul fichier et un TCL. Mais cela présente plus d’inconvénients que d’avantages : en effet pour interroger une petite table de quelques enregistrements au sein de base de

données de 500 Mo, il faut rapporter sur le poste client, la totalité du fichier de la base de données... Un non sens absolu, que Microsoft pali en intimant à ses utilisateurs de passer à SQL Server dès que le

nombre d’utilisateurs dépasse 10 !

5.2 SGBDR "Client/Serveur"

Le service consiste à faire tourner sur un serveur physique, un moteur qui assure une relative indépendance entre les données et les demandes de traitement de l'information venant des différentes applications : un poste client envoi à l'aide d'un protocole de réseau, un ordre SQL (une série de trames réseau), qui est exécuté, le moteur renvoie les données. De plus le SGBDR assure des fonctions de gestions d'utilisateurs de manière indépendante aux droits gérés par l'OS.

A ce niveau il convient de préférer des SGBDR C/S qui pratiquent : le verrouillage d'enregistrement plutôt que le verrouillage de page (évitez donc SQL Server...), et ceux qui tournent sur de nombreuses plates-formes système (Oracle, Sybase...). Enfin certains SGBDR sont livrés avec des outils de sauvegarde et restauration.

Les SGBDR "C/S" proposent en général la totalité des services du SQL (contrôle des transactions, DDL et DCL). Ils sont, par conséquent, pratiquement tous ACID. Enfin de plus en plus de SGBD orientés objets voient le jour. Dans ce dernier cas, ils intègrent la plupart du temps le SQL en plus d'un langage spécifique d'interrogation basé sur le concept objet (O², ObjectStore, Objectivity, Ontos, Poet, Versant, ORION,GEMSTONE...) Les serveurs SQL C/S les plus connus sont : Oracle, Sybase, Informix, DB2, SQL Server, Ingres, InterBase, SQL Base...

Avantage : grande capacité de stockage, gestion de la concurrence dans un SI à grand nombre d’utilisateurs, haut niveau de paramétrage, meilleure répartition de la charge du système, indépendance vis à vis de l'OS, gestion des transactions, robustesse, cohérence des données importante. Possibilité de montée en charge très importante en fonction des types de plateformes supportées.

Inconvénient : lourdeur dans le cas de solution "monoposte", complexité du fonctionnement, coût élevé des licences, administration importante, nécessité de machines puissantes.

NOTA : Pour en savoir plus sur le sujet, lire l'étude comparative sur les SGBDR à base de fichier et ceux utilisant un moeur relationnel, intitulée : Quand faut-il investir sur le client/serveur ? On y discute aussi des différents modes de vérouillage ...

6 Type de donnéesDernier point que nous allons aborder dans ce premier article, les différents types de données spécifiés par SQL et leur disponibilité sur les 5 systèmes que nous avons retenus pour notre étude.Selon la norme ISO de SQL 92

6.1 Types alphanumériques CHARACTER (ou CHAR) : valeurs alpha de longueur fixeCHARACTER VARYING (ou VARCHAR ou CHAR VARYING) : valeur alpha de longueur maximale fixéeCes types de données sont codés sur 2 octets (EBCDIC ou ASCII) et on doit spécifier la longueur de la chaîne.Exemple :

NATIONAL CHARACTER (ou NCHAR ou NATIONAL CHAR) : valeurs alpha de longueur fixeNATIONAL CHARACTER VARYING (ou NCHAR VARYING ou NATIONAL CHAR VARYING) : valeur alpha de longueur maximale fixée sur le jeu de caractère du pays.Ces types de données sont codés sur 4 octets (UNICODE) et on doit spécifier la longueur de la chaîne.Exemple :

Nota : la valeur maximale de la longueur est fonction du SGBDR.

6.2 Types numériques NUMERIC (ou DECIMAL ou DEC) : nombre décimal à représentation exacte à échelle et précision facultatives.INTEGER (ou INT): entier longSMALLINT : entier courtFLOAT : réel à virgule flottante dont la représentation est binaire à échelle et précision obligatoireREAL : réel a virgule flottante dont la représentation est binaire, de faible précisionDOUBLE PRECISION : réel a virgule flottante dont la représentation est binaire, de grande précisionBIT : chaine de bit de longueur fixeBIT VARYING : chaîne de bit de longueur maximale

http://sqlpro.developpez.com/SQL_AZ_C.html

http://sqlpro.developpez.com/SQL_AZ_C.html

Pour les types réels NUMERIC, DECIMAL, DEC et FLOAT, on doit spécifier le nombre de chiffres significatifs et la précision des décimales après la virgule.Exemple :

signifie que le nombre comportera au plus 15 chiffres significatifs dont deux décimales.ATTENTION : le choix entre le type DECIMAL (représentation exacte) et le type FLOAT ou REAL (représentation binaire) doit être dicté par des considérations focntionnelles. En effet, pour des calculs comptable il est indispensable d'utiliser le type DECIMAL exempt, dans les calculs de toute fraction parasites capable d'entrainer des erreurs d'arrondis. En fait le type DECIMAL se comporte comme un entier dans lequel la virgule n'est qu'une représentation positionnelle. En revanche pour du calcul scientifique on préférera utiliser le type FLOAT, plus rapide dans les calculs.Exemple :

NOTA : on peut utiliser une notation particulière pour forcer le typage implicite. Il s'agit d'une lettre précédent la chaîne à ttranstyper. Les lettres autorisées, sont : N (pour Unicode), B pour binary (chaine de 0 et 1) et X pour binary (chaine hexadécimale constituées de caractères allant de 0 à F). Exemple :

6.3 Types temporels DATE : date du calendrier grégorienTIME : temps sur 24 heuresTIMESTAMP : combiné date tempsINTERVAL : intervalle de date / tempsRappelons que les valeurs stockées doivent avoir pour base le calendrier grégorien (2) qui est en usage depuis 1582, date à laquelle il a remplacé le calendrier julien. En matière de temps, la synchronisation s’effectue par rapport au TU ou temps universel (UTC : Universal Time Coodinated) anciennement GMT (Greenwich Mean Time) l’ensemble ayant été mis en place, lors la conférence de Washington DC en 1884, pour éviter que les chemins de chemins ne se télescopent.ATTENTION : Le standard ISO adopté pour le SQL repose sur le format AAAA-MM-JJ. Il est ainsi valable jusqu’en l’an 9999… ou AAAA est l’année sur 4 chiffres, MM le mois sur deux chiffres, et JJ le jour. Pour l'heure le format ISO est hh:mm:ss.nnn (n étant le nombre de millisecondes)Exemple :


est le 26 mars 1999 à 22h 54m, 28s et 123 millisecondesMais peu de moteurs de requêtes l’implémente de manière aussi formelle…Le type INTERVAL est très particulier. Il est malheureusement rarement présent dans les SGBDR.Sa définition se fait à l'aide de la syntaxe suivante :

INTERVAL précision_min TO [précision_max]

où précision_min et précision_max peuvent prendre les valeurs :

avec la condition supplémentaire suivante : précision_max ne peut être qu'une mesure temporelle plus fine que précision_min.Exemple :

ATTENTIONVeuillez noter que les réceptacles de valeurs temporelles ainsi créés par le type INTERVAL sont des entiers, et que leur valeur est contrainte lorsqu'il sont définis avec une précision maximum sur tous les éléments les composant sauf le premier. Ainsi, dans une colonne définie par le type INTERVAL MONTH TO DAY, on pourra stocker une valeur de 48 mois et 31 jours, mais pas une valeur de 48 mois et 32 jours. De même dans un INTERVAL HOUR TO SECOND la valeur en heure est illimitée, mais celle en minute et en seconde ne peut dépasser 59.

6.4 Types " BLOBS " (hors du standard SQL 2)Longueur maximale prédéterminée, donnée de type binaire, texte long voire formaté, structure interprétable directement par le SGBDR ou indirectement par add-on externes (image, son, vidéo...). Attention : ne sont pas normalisé !On trouve souvent les éléments suivants :TEXT : suite longue de caractères de longueur indéterminéIMAGE : stockage d'image dans un format déterminéOLE : stockage d'objet OLE (Windows)

6.5 Autres types courants, hors norme SQL 92 :BOOLEAN (ou LOGICAL) : curieusement le type logique (ou encore booléen) est absent de la norme. On peut en comprendre aisément les raisons… La pure logique booléenne ne saurait être respectée à cause de la possibilité offerte par SQL de gérer les valeurs nulles. On aurait donc affaire à une logique dite " 3 états " qui n’aurait plus rien de l’algèbre booléenne. La norme passe donc sous silence, et à bon escient ce problème et laisse à chaque éditeur de SGBDR le soin de concevoir ou non un booléen " à sa manière ".On peut par exemple implémenter un tel type de données, en utilisant une colonne de type caractère longueur 1, non nul et restreint à deux valeurs (V / F ou encore T /

F).MONEY : est un sous type du type NUMERIC avec une échelle maximale et une précision de deux chiffres après la virgule.BYTES (ou BINARY) : Type binaire (octets) de longueur devant être précisée. Permet par exemple le stockage d’un code barre.AUTOINC : entier à incrément automatique par trigger.

6.6 Les domaines, ou la création de types spécifiques Il est possible de créer de nouveau types de données à partir de type pré existants en utilisant la notion de DOMAINEDans ce cas, avant d'utiliser un domaine, il faut le recenser dans la base à l'aide d'un ordre CREATE :

CREATE DOMAIN nom_du_domaine AS type_de_donnée

Exemple :

Dès lors il ne suffira plus que d'utiliser ce type à la place de CHAR(5).L'utilisation des domaines possède de nombreux avantages :

ils peuvent faire l'objet de contraintes globales

ils peuvent être modifiés (ce qui modifie le type de toutes les colonnes de table utilisant ce domaine d'un seul coup !)

Exemple :

Dans ce cas le code postal saisie devra au minimum s'écrire 01000. On pourrait y ajouter une contrainte maximum de forme (VALUE <= '99999')NOTA : certains SGBDR n'ont pas implémentés l'ordre CREATE DOMAIN. C'est le cas par exemple de SQL Server. Ainsi, il faut aller "trifouiller" les tables systèmes pour insérer un nouveau type de donnée à l'aide de commandes "barbares" propre au SGBDR.Exemple (SQL Server) :

Un des immenses avantages de passer par des définitions de domaines plutôt que d'utiliser directement des type de donnés est que les domaines induisent une bonne normalisation de la conception du schéma des données.Pour ma part j'utilise souvent le jeu de domaine suivant :

varchar(32) /* spécifique aux adresses */

smallint(1) /* contraint à 0 ou 1, valeur par défaut 0 */

7 Contraintes de donnéesDans la plupart des SGBDR il est possible de contraindre le formatage des données à l'aide de différents mécanismes.Parmi les contraintes les plus courantes au sein des données de la table on trouve :

valeur minimum

valeur maximum

valeur par défaut valeur obligatoire

valeur unique

clef primaire

index secondaire

format ou modèle (par exemple 3caractères majuscules suivi de 2 caractères numériques)

table de référence (recopie d'une valeur d'une table dans un champ d'une autre table en sélectionnant par la clef) aussi appelé CHECK en SQL

liste de choix

Enfin entre deux tables liées, il est souvent nécessaire de définir une contrainte de référence qui oblige un enregistrement référencé par sa clef a être présent ou détruit en même temps que l'enregistrement visé est modifié, inséré ou supprimé. Ce mécanisme est appelé INTÉGRITÉ RÉFÉRENTIELLE.

Exemple : Soit une base de données contenant deux tables : CLIENT et COMMANDE dotées

Si l'on détruit la ligne de la table CLIENT concernant le n°212 (MARTIN) alors les factures 1999

des références de ce client persistent dans la table COMMANDE.

De même, le changement de la valeur de la clef NO_CLIENT ferait perdre la valeur de référence

du lien entre les deux tables, à moins que la modification ne soit répercutée dans la table fille.

: on parle alors de tables en relation mère / fille ou encore maître / esclave.

8 Triggers et procédures stockéesEn ce qui concerne les SGBDR en architecture client / serveur, il est courant de trouver des mécanismes de triggers (permettant d'exécuter du code en fonction d'un événement survenant dans une table) ainsi que des procédures stockées (du code pouvant être déclenché a tout moment). Dans les deux cas, c'est sur le serveur, et non dans le poste client que la procédure ou le trigger s'effectue.L'avantage réside dans une plus grande intégrité du maniement des données et souvent d'un traitement plus rapide que si le même code trournait sur le poste client.Ainsi le calcul d'un tarif dans une table de prestation peut dépendre de conditions parfois complexes ne pouvant être facilement exécutée à l'aide de requêtes SQL. Dans ce cas on aura recours au langage hôte du SGBDR, pour lequel on écrira une procédure permettant de calculer ce tarif à partir de différents paramètres.Mais la plupart du temps, triggers et procédures stockées s'écrivent dans un langage propre au SGBDR (Transact SQL pour SQL Server et Sybase, PL/SQL pour Oracle, etc...). Pour un aperçu du langage Transact SQL, veuillez lire l'article "Un aperçu du langage Transact SQL".

http://sqlpro.developpez.com/TRansactSQL/SQL_MSTransactSQL.html

http://sqlpro.developpez.com/TRansactSQL/SQL_MSTransactSQL.html

: on désire calculer le tarif d'adhésion à une mutuelle santé pour une famille composée d'un

homme né le 11/5/1950, d'une compagne née le 21/6/1965, d'un fils né le 16/3/1992, d'une fille né le

11/1/1981 et d'une grand mère à charge (ascendant) née le 21/12/1922, le futur adhérent désirant

Les bases tarifaires établies sont les suivantes :

Il est très difficile d'établir une requête permettant de trouver le bon tarif dans un tel cas. En revanche,

en passant la table de paramètre suivant à une procédure :

Il n'est pas très compliquée d'écrire dans un langage donné une procédure permettant de calculer ce

Une telle, procédure pourrait s'écrire dans un pseudo c

représentant les données des tables

; cumul des différents tarifs enfant trouvés

; dénombrement des enfants

; minoration du tarif cumulé des enfants

; recherche du tarif pour les autres types excepté les enfants

; cumul des différents autres tarifs trouvés

RésuméVoici les différentes implémentations du SQL sur quelques uns des différents moteurs relationnels que nous avons choisi d’analyser.

SGBDR Paradox 7 Access 97 Sybase adaptive 11

SQL Server 7

Oracle 8

Nature Service de fichier

Service de fichier

Serveur de données

Serveur de données

Serveur de données

Nb utilisateur (max / en pratique)

255 / 50 300 / 10

Taille max de la base

illimitée 1 Go

Taille max d'une table

2 Go (hors BLOBS)

1 Go

Normalisation SQL 92 SQL 89 ? SQL 92 SQL 92 SQL 89

DDL Oui (1) Oui Oui Oui Oui

DML Oui Oui Oui Oui Oui

DCL Non Oui Oui Oui Oui

TCL Oui (2, 3) Non Oui Oui Oui

CHARlimité à 255 car. limité à 255 car. Oui limité à 255

car. oui limité à 8 000

car.oui limité à 2 000

car.

VARCHARnon non oui oui oui limité à 4 000

car.

NUMERICoui, avec 15

chiffres significatifs

sous types comprenant des

entiers et des réels

oui oui oui

INTEGERoui non oui oui oui

SMALLINToui non oui oui oui

FLOAToui, en fait NUMERIC

voir NUMERIC oui oui oui

DATE oui non non non oui

TIME oui non non non non

TIMESTAMPoui oui oui oui non

INTERVAL non non non non non

BIT non oui oui

BOOLEAN oui (LOGICAL) oui non non non

MONEY oui oui oui oui non

BYTES oui non non non oui (RAW)

AUTOINC oui oui non (4) non (4) non (4)

BLOB

oui (4 types différents : MEMO, MEMO FORMATE en RTF, IMAGE et BINARY) limités à

2 Go

oui (2 types différents : MEMO, HYPERLIEN limité

à 64 ko)

oui (2 types différents TEXT

IMAGE)

oui (2 types différents IMAGE,

TEXT)

oui (7 types différents :

LONG, LONG RAW, LONG VARCHAR,

BFILE, BLOB, CLOB, NCLOB)

Autres typesOCTET (1 à 255),

OLEOLE, liste de choix BIT, BINARY

BIT, BINARY, CURSOR,

TINYINT, GUID

ROWID (N° d'enregistrement)

INTEGRITÉ RÉFÉRENTIELLE

oui, stricte ou cascade

(suppression et modif.)

oui, stricte et cascade

(suppression et modif.)

oui oui, pas en

cascade oui

TRIGGERS non non oui oui, limités

BEFORE INSERT, BEFORE UPDATE, BEFORE

DELETE, AFTER INSERT, AFTER UPDATE, AFTER

DELETE

PROCÉDURES STOCKÉES

non non oui, langage

propriétaire Transact SQL

oui, langage propriétaire

Transact SQL

oui, langage propriétaire

PL/SQL

1 Avec quelques limitations (par exemple les contraintes d’intégrité référentielles ne peuvent être crées par le SQL)2 Limité en rollback à 255 enregistrements3 Pas dans le SQL, mais en code du langage hôte4 Mais possible à l'aide de triggers ou de commandes spécifiques.

ConclusionEn matière de SGBDR " fichier ", Paradox se révèle plus pauvre au niveau du DDL et du TCL, mais plus riche en matière de DML qu’Access. Quant aux types de données, Paradox se révèle bien plus complet qu’Access qui n’intègre même pas de champ de type " image "... Pensez que dans Access le type entier n’est même pas défini ! Enfin en matière de BLOB la plupart des SGBDR acceptent jusqu’à 2Go de données, sauf Access qui est limité à 64 Ko...

En ce qui concerne la capacité de stockage Access révèle très rapidement de nombreuses limites, comme en nombre d'utilisateurs en réseau.En matière de contrôle des transactions Paradox est limité à 255 enregistrements en RollBack. Mais la présence de tables auxiliaires permet de dépasser ces limites sans encombre, à condition de prévoir le code à mettre en œuvre.Différence fondamentale pour Paradox, pas de DCL. Mais cela est largement compensé par un niveau de sécurité a forte granularité qui n’est pas compatible avec le SQL normalisé. Ainsi dans Paradox on peut placer des droits au niveau des tables mais aussi de chaque champ et le moteur crypte les données dès qu’un mot de passe est défini (SQL Base de Centura permet aussi de crypter les données à l’aide des plus récents algorithmes de chiffrage)Point très négatif pour Access dans sa catégorie : il pratique le verrouillage de pages !Enfin les vues n’existent pas dans Access mais elles sont présentent dans Paradox sous une forme non SQL appelée " vue de requête reliés " (QBE).En matière de serveur SQL C/S, le SGBDR Sybase se révèle très proche de SQL Server ce qui n'est pas absurde puisqu'ils sont parents. Oracle possède une bonne diversité de types mais sa conformité à la norme laisse à désirer (pas de JOIN par exemple, pauvreté des fonctions temporelles). En revanche Oracle possède un type de champ bien utile et intégré à toutes les tables, le ROWID qui donne le n° de la ligne dans la table et qui est toujours unique, même si la ligne a été supprimée. On retrouve des mécanismes similaires dans SQL Server sous le nom de GUID ou bien avec l'auto incrémentation via "identity".

NOTES :

(1) SQL 3 permet une certaine récursivité à l'aide d'une clause WITH

(2) Grégoire XIII (224eme pape), était un pape assez moderne et surtout féru de science... lui au moins aurait certainement sut que la capote ne se mettait pas à l'index ! -

2eme partie - le simple (?) SELECT et les fonctions SQL

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"

1 La commande SELECT Vous trouverez des compléments d'information sur le sujet aux pages 53 à 127 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

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 :

prédicats

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

http://sqlpro.developpez.com/SQL_AZ_B.html



En fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optioennelles.Clauses de l'ordre SELECT :

Spécification des colonnes du résultat

Spécification des tables sur lesquelles porte l'ordre

Filtre portant sur les données (conditions à remplir pour que les lignes soient présentes

dans le résultat)

Définition d'un groupe

Filtre portant sur les résultats (conditions de regroupement des lignes)

Tri des données du résultat

NOTA : La plupart du temps, la difficulté réside dans la compréhension de la différence entre le filtre WHERE et le filtre HAVING. Disons plus pargmatiquement que le filtre WHERE permet de filtrer les données des tables tandis que le filtre HAVING permet de filtrer les données du résultat.

REMARQUE : pour spécifier une valeur littérale il faut l'entourer de guillemets simples.

Un premier exemple basique :exemple 1

Arsène

PHILIPPE André

François

Permet de trouver les noms et prénoms des clients dont le titre est ‘M.’ (monsieur).

NOTA : comme tous les paramètres à prendre sous forme de litteraux doivent être exprimées entourés d'apostrophes (simple côtes), il faut dédoubler un tel caractères s'il s'avère présent dans la chaîne utilisé.

1.1 l'opérateur * (étoile)

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 2

Boulangerie du marché

Cie Internationale des Machines Electromécaniques

Arsène

André

Notons tout de suite la présence à plusieurs reprises du mot clef "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 le marquer "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 ou 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 3

Arsène

André

André

Arsène

exemple 4

1.3 Opérateur AS

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 5

1.4 Opérateur de concaténation

L'opérateur || (double barre verticale) permet de concaténer des champs de type caractères

exemple 6

Néanmoins on trouve dans certains SGBDR le + comme opérateur de concaténation, comme la fonction CONCAT.

1.5 Opérateur mathématiques de base

On, peut utiliser les opérateurs mathématiques de base pour combiner différentes colonnes(,+,-, *, /,).

exemple 7

1.6 Particularité du "FROM"

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 :

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.7 Utilisation du caractère double quote (guillemet)

Lorsqu’un nom d’un élément d’une base de données (table, colonne par exemple) est identique à un mot clef du SQL, il convient de l’entourer de guillemets (double

quote). En principe, les mots réservés du SQL sont déconseillé pour nommer des objets du modèle physique de données...

Immaginons une table de nom JOIN, composée des champs suivants :

Exemple 8 : nous désirons sélectionner les colonnes SELECT et DATE lorsque la colonne NOT vaut F...

Correct : on entoure les mots clefs du SQL par des doubles côtes

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.REMARQUE : les noms des identifiants d'objet de base de données doivent être écrit dans le jeux de caractères restreint suivant :[A..Z] + [a..z] + [0..9] + [ _ ]. Ils ne doivent pas commencer par un chiffre et sont insensible à la casse (indifférence entre majuscule et minuscule).

2 Clause ORDER BY Vous trouverez des compléments d'information sur le sujet aux pages 66 à 70 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

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 l’ordre ascendant et DESC l’ordre descendant du tri. ASC ou DESC 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 s’effectue dans un ordre non contrôlable qui peut varier d’un serveur à l’autre.

exemple 9

François

Frédéric

Joël

REMARQUE : les marqueur NULL sont situées en premier dans l'ordre ainsi établit.

NOTA : Un problème, qui n’est pas résolu, est de pouvoir choisir l’ordre des colonnes de la réponse. Sur certains serveurs cela peut être obtenu en plaçant les noms des colonnes à obtenir dans l’ordre où l’on veut les voir apparaître dans la clause SELECT, mais cette possibilité n'est jamais garantie...

ATTENTION : la clause ORDER BY est la dernière clause de tout ordre SQL et ne doit figurer qu'une seule fois dans le SELECT, même s'i l existe des requêtes imbriquées ou un jeu de requêtes ensemblistes

3 La clause WHERE Vous trouverez des compléments d'information sur le sujet aux pages 62 à 165 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.


prédicats

Le prédicat doit contenir n’importe quelle expression logique renvoyant une valeur vrai.Ainsi, une requête aussi stupide que la suivante, est supposée fonctionner :

exemple 10

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

_CLI_OCCUPE puisse être du booléen, la plupart

rue' (littéral). Si le type booléen n'existe

si l'on a choisi de définir les booléens comme INTEGER(1) avec 0 et

exemple 11Pour palier au manque de booléen, on utilise soit un littéral (True/False, Vrai/Faux, Oui/Non), soit un numérique avec les valeurs 0 (Faux) et 1 (Vrai). L'avantage des valeurs numériques est que le calcul logique est comparable aux divisions et

additions...

opérateur ET FAUX VRAI

FAUX FAUX FAUX

VRAI FAUX VRAI

similitude entre le ET et la multiplication

multiplication 0 1

0 0 0

1 0 1, <> 0

opérateur OU FAUX VRAI

FAUX FAUX VRAI

VRAI VRAI VRAI

similitude entre le OU et l'addition

addition 0 1

0 0 1, <>0

1 1, <>0 2, <>0

3.1 Opérateurs de comparaison

Dans la clause WHERE, vous disposez de différents opérateurs de comparaisons logiques :

exemple 12

Arsène

Amélie

François

Ici on obtient tous les noms et prénoms des clients dont le nom commence par les lettres A, B, C ou D.

Attention : dans certains moteurs de requête SQL l’opérateur « différend de » (<>) s’écrit !=

3.2 Opérateur IN

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 d’inverser le fonctionnement de l’opérateur IN en lui adjoignant l’opérateur NOT.

exemple 13

Noëlle

On recherche les clients de sexe féminin, basés sur le code titre

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.

3.3 Opérateur BETWEEN

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 12 peut s'écrire :

exemple 14

Arsène

Amélie

François

NOTA : les opérateurs IN et BETWEEN sont très pratiques dans le cas où l’on désire effectuer des requêtes où l’utilisateur peut saisir une liste de choix multiples (IN) ou une plage de valeur (BETWEEN).

3.4 Opérateurs LIKE

L'opérateur LIKE permet d’effectuer 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 15

François

André

BENZAQUI Joël

Frédéric

On recherche les client dont le nom commence par B.

Mais si vos données sont susceptibles de contenir un des deux caractères joker, alors il faut recourir à une séquence d’échappement, à l’aide du mot clef ESCAPE…

Cherchons les clients dont l'enseigne contient au moins un caractère blanc souligné :

exemple 16

Boulangerie du marché

François Entreprise DUPONT

Noëlle

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 : l’opé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 d’utiliser les opérateurs LOWER et UPPER (voir ci dessous). Mais la plupart du temps, l'utilisation du like dans un SGBDR donné ignore la casse.

3.5 Résumé des opérateurs pour les prédicats de la clause WHERE

Voici une tableau résumant les principaux opérateurs utilisés pour la construction des prédicats :

opérateurs de comparaisons

opérateur de négation

parenthèses

érateurs mathématiques

comparaison partielle de chaîne de caractères

comparaison à une liste de valeur

4 Fonctions diversesVous trouverez des compléments d'information sur le sujet aux pages 71 à 107 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

4.1 Trantypage à l'aide de la fonction CAST

Il permet de changer le type de données d'une colonne afin d’effectuer 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…Sa syntaxe est CAST(colonne AS nouveau type).

exemple 17

L’opérateur CAST permet de transtyper les valeurs contenues dans une colonne.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 18

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 19

NOTA : pour effectuer une recherche en ne tenant aucunement compte de la différence entre majuscules et minuscules, il faut utiliser l’opérateur UPPER (ou lower mais attention à la transformation des accents !) :exemple 20

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é.

caractère

LEADING : suppression en têteTRAILING : suppression en queueBOTH : suppression en tête et en queue

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 n’ont pas besoin de composer ce chiffre (ils doivent simplement composer le 00 33 suivi du numéro à 9 chiffres).exemple 21

NOTA : certains serveurs SQL proposent différentes fonctions comme LTRIM et RTRIM pour une suppression des blancs en tête ou en queue.

4.4 Extraire une sous chaîne

La fonction SUBSTRING permet d’extraire une sous chaîne d’une chaîne de caractère. Elle a besoin de l’ordre du premier caractère et du nombre de caractères sur lequel elle doit opérer.

Extrait la sous chaîne de nom de colonne en commençant à n sur m caractères.

exemple 22

Cet exemple construit les initiales des clients à partir des colonnes CLI_NOM et CLI_PRENOM_CLI

Attention, certains SGBDR utilisent la fonction SUBSTR

4.5 Opérateur de traitement des dates

4.5.1 Extraire un paramètre temporel d'une date

L’opérateur EXTRACT permet d’extraire depuis une date, le jour le mois ou l’année…

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 personnesexemple 23

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

L’heure courante, la date courante et le combiné date/heure courant peuvent être obtenu à l’aide des fonctions CURRENT_DATE, CURRENT_TIME et CURRENT_TIMESTAMPexemple 24

attention, le résultat de cette requête varie en fonction de la date à laquelle vous l'exécutez

Cette requête renvoi les chambres réservées pour 3 personnes entre la date du jour et pour les deux semaines à venir .

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 :

4.6 Opérateurs statistiques

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 25

Cette requête calcule la moyenne, le montant maximum, minimum, la totalisation et le nombre des tarifs de chambre pour la date de debut du premier janvier 2001.

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).

ATTENTION : nous verrons que l'utilisation des fonctions statistiques nécessite la plupart du temp la mise en place d'une caluse de groupage, afin de déterminé quel

est le sous ensemble cible d'agrégation pour les calculs.

4.7 Autres fonctions normalisées

Taille d'une colonne de type caractère (nombre de caractères)

Taille d'une colonne de type caractère (nombre d'octets)

Conversion paramétrèe d'une chaîne de caractères

Position d'une chaîne de caractères dans une sous chaîne

Traduction d'une chaîne de caractères dans un format spécifié

4.8 Autres opérateurs mathématiques (non normalisés)

Les opérateurs ci dessous peuvent être implémentés dans différents moteurs.

racine carrée

tronqué

logarithme népérien

logarithme décimal

Certains sont rarement implémentés du fait que les SGBDR sont axés sur l’informatique de gestion, la collecte et le traitement d’informations et non le calcul mathématique.

Attention : le nom de certains de ces opérateurs peut différer d’un SGBDR à l’autre.

4.9 Autres opérateurs de traitement des chaînes de caractères (non normalisés)

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 ||

complément ou troncature à n position à gauche

suppression en tête/queue d’une chaîne

complément ou troncature à n position à droite

SOUNDEX – Attention : phonétique souvent anglaise

Position d’une chaîne dans une sous chaîne

longueur de la chaîne

numérique sous forme littérale –

code ASCII d'un caractère

caractère dont le code ASCII est donné

Inverse l'ordre des caractères d'une châine

http://sqlpro.developpez.com/Soundex/SQL_AZ_soundex.htm

Pivote les partie droite et gauche d'une chaîne par rapport au n° du caractère servant

Attention : le nom de certains de ces opérateurs peut différer d’un SGBDR à l’autre.

4.10 Autres opérateurs sur les valeurs temporelles (non normalisés)

ajoute des mois, des jours des années à une date

n° du dernier jour d’un mois d’une date

date du prochain jour d’un nom donné

date sous forme littérale –

différence entre deux dates

4.11 Opérateurs d'ensemble (non normalisés)

la plus grande des valeurs d’un ensemble

la plus petite des valeurs d’un ensemble

renvoi la valeur situé en n ième position

5 Traitement des "valeurs" nulles Vous trouverez des compléments d'information sur le sujet aux pages 115 à 120 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

NOTA : le NULL n'est pas à proprement parlé une valeur, mais bien l'absence de valeur, c'est pourquoi nous parlerons de marqueur NULL et non de valeur NULL.Le marqueu NULL 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 d’inexistence.

5.1 Le null n'est ni la chaîne vide, ni le zéro

NULL n'est pas une valeur. C'est un marqueur. Par conséquent le marqueur NULL ne peut jamais être comparé a une valeur.

Recherchons les clients qui n'ont pas d'enseigneexemple 26


La réponse doit produire une table vide !Pour controurner ce problème il faut :soit penser a enregistrer une chaîne de caractère vide lors de l'insertion des données dans la tablesoit la clause WHERE avec un opérateur spécialisé dans le traitement des valeurs nulles

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 : l’absence d’information est-elle due à son ignorance ou à son impertinence ? Pourquoi donc ne pas faire de différence entre la couleur du toit d’une voiture qui n’est pas connue, et la couleur du toit d’une moto qui n’est pas applicable… Certains logiciens de l’algè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 d’absence de valeur et il faut toujours faire très attention aux clauses qui utilisent des références aux valeurs nulles, suivant ce que l’on veut obtenir. D’autant plus que les NULL se propagent dans les calculs.

Voici un extrait de la table T_LIGNE_FACTURE

Nous voulons calculer le montant total de chacune des lignes de cette table, pour une facture donnée.

La requête pour FAC_ID = 3 est la suivante :exemple 27

On constate que pour les lignes qui n'ont pas de valeurs renseignées dans les colonnes LIF_REMISE_POURCENT, LIF_REMISE_MONTANT, le résultat du calcul donne la valeur « null » qui se traduit à l'affichage par... rien !

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 : l’arithmé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 d’une colonne NULL et d’une colonne proprement renseigné, la valeur renvoyée sera NULL !!!

5.2 Opérateurs de traitement des marqueurs NULL

La norme SQL 2 (1992) spécifie une comparaison et différents opérateurs sur les marqueurs NULL :IS NULL / IS NOT NULL : teste si la colonne est vide ou non vide.COALESCE() qui recherche la première valeur non vide dans un ensembleNULLIF NULLifie une colonne en fonction d'une valeur donnée

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 28

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 !

6 Négation de valeurs

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 29

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

7 Les branchements dans le SQL Vous trouverez des compléments d'information sur le sujet aux pages 120 à 123 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

SQL possède un branchement à la manière des IF et autres structures de test des langages procéduraux.Mais il convient de ne l'utiliser qu'a bon escient, c'est à dire aussi peu souvent que possible, beaucoup de cas ouvant être traités soit par le COALESCE soit par des requêtes avec des opérations ensemblistes de type UNION. En effet les performances se dégradent très vite lors de l'usage du CASE à cause de l'impossibilité d'effcetuer des traitements par "paquets".


La structure CASE du SQL comprend deux syntaxes différentes. Le CASE pour branchement sur les valeurs d'une expression et le CASE généralisé.

7.1 CASE sur expression

Dans ce cas, la syntaxe est la suivante :

[ELSE expression_défaut]

Exemple 30 :

7.2 CASE généralisé

L'expression disparait au profit de différents prédicats.


Exemple 31 :

Qui donne le même résultat !

ATTENTION : tous les SGBDR ne supportent pas ces deux syntaxes.

NOTA : dans les deux cas il est possible de remplacer le IF d'un langage procédural :



8 Le constructeur de lignes valuées (ROW VALUE CONSTRUCTOR)

Nous voici devant un élément peu connu du SQL car rarement implanté. Rendons homage à Oracle car il est l'un des rares SGBDR à l'implémenté. Cette construction peut vous tirer d'embarras pour bien des cas en simplifiant drastiquement vos requêtes.

L'idée consiste à dire que différentes colonnes devant correspondre à différents critères comparées à l'aide d'un même opérateur de comparaison peuvent être évaluées simultanément.

Il est plus facile de comprendre ce concept à l'aide d'un exemple.

Un utilisateur créé une table T_EVENEMENT dans laquelle il a quatre colonnes indiquant l'heure, la minute, la seconde de survenance de l'événement et la nature de l'événement :

êt')

58, 59, 'Démarrage')

Démarrage

Arrêt

La question est : quel sont les événements survenus après 3h 10 ?

Exemple 32 - intuitivement, nous sommes amenés à écrire la requête suivante :

Mais elle oublie sciement trois événements survenus avant 3h10 ! Comment est-ce possible ?

En fait l'événement qui s'est produit à 4h 0 n'est pas pris en compte du fait de la première ligne de la clause WHERE !

tout simplement parce que 0 minute est bien inférieur à 10 minutes ...

Or 4h 0 est bien supérieur à 3h 10... il faut donc supprimer la partie

de la clause WHERE

La bonne construction est donc :

Exemple 33

Arrêt

D'ou l'idée d'implémenter une constrction capable d'évaluer globalement une telle opération. Cette construction est le ROW VALUE CONSTRUCTOR que j'ai traduit par constructeur de lignes valuées...

Avec un tel outil, la requête précédente s'exprime plus simplement :Exemple 34

Arrêt

La syntaxe d'une telle expression utilisant un ROW VALUE CONSTRUCTOR est :

<constructeur de lignes valuées> <constructeur de lignes valuées>

Le constructeur de lignes valuées pouvant être : une liste de colonnes, une liste de valeurs, une liste combinant colonnes et valeurs ou encore une sous requête :

(colonne1, colonne2, colonne3) (colonne1, valeur1, valeur2, colonne2) (valeur1, valeur2) SELECT * FROM Matable à condition de ne retourner qu'une seule ligne

etc... L'équivalent entre cette construction et celle n'utilisant pas le ROW VALUE CONSTRUCTOR, peut être développée comme suit.Supposons que le premier constructeur de lignes valuées comporte des colonnes de tables de 1 à n et que le second constructeur de ligne valuées comporte des valeurs de 1 à n, c'est à dire :

alors cette comparaison est équivalente à :

Toujours basé sur notre jeu d'essais, voici un exemple plus complet :: cherchons tous les événements survenus après 3h12'30" :Exemple 35

Arrêt

Exemple 36 - et sa construction logiquement équivalente :

Arrêt

En fait le ROW VALUE CONSTRUCTOR agit, dans les recherches, un peu comme si l'on visualisait les données de manière fractale.Le plus grossier est traité, puis on s'interrese à quelques données plus fine une fois le grossier fixé, et ainsi de suiteen fonction du niveau fractal que l'on s'est fixé.NOTA : l'utilisation du ROW VALUE CONSTRUCTOR avec un critère de comparaison d'égalité donne parfois des résultats surprenants, qui peuvent défier la logique. Il faut se méfier d'un recours systématique à une telle construction.Exemple 37 - quels sont les événement survenus à 3h 12 (en utilisant le ROW VALUE CONSTRUCTOR) :

Arrêt

Ce qui offre peut d'intérêt il faut bien le dire !La construction logique équivalente étant :Exemple 38 - quels sont les événement survenus à 3h 12 (en utilisant le ROW VALUE CONSTRUCTOR) :

Arrêt

ENFIN, notez que le ROW VALUE CONSTRUCTOR possède un intérêt immense, celui de permettre l'insertion multiple (plusieurs lignes de données) au sein du même ordre INSERT !Exemple 39 - insertion multiple avec le ROW VALUE CONSTRUCTOR :

(5, 21, 1, 'Destruction finale planète

ATTENTION : seul ORACLE et quelques SGBDR plus expérimentaux que professionnels (OCELOT par exemple) utilisent une telle technique !

RésuméVoici les différences entre les moteurs des bases de données

SGBDR Fonctions

Paradox 9

Access 2000

Sybase Adaptive

SQL Server 7

Oracle 8

|| (concaténation) oui non (&) non (+) non (+) oui

CAST oui nonnon

(CONVERT)oui

non (TO_CHAR

/ TO_NUM / TO_DATE)

LIKE / ESCAPE / % / _

oui / oui / oui / oui

oui / non / non / non (1)

oui / oui / oui / oui

oui / oui / oui / oui oui / oui / oui / oui

TRIM oui non (LTRIM,

RTRIM) non (LTRIM,

RTRIM) non (LTRIM, RTRIM) non (LTRIM,

RTRIM)

SUBSTRING oui non (MID) non (ODBC

SUBSTRING)non (ODBC

SUBSTRING)non

(SUBSTR)

LOWER / UPPERoui / oui non (UCASE) /

non (LCASE) oui / oui oui / oui oui / oui

EXTRACToui non non

(DATEPART)non (DATEPART) non


CURRENT_DATE / CURRENT_TIME / CURRENT_TIMESTAMP

non / non / non (TODAY, NOW

en QBE)

non (NOW) non (GETDATE)

non (GETDATE() / CURRENT_TIMESTAMP)

non (SYSDATE)

AVG / MAX / MIN / SUM / COUNT

oui / oui / oui / oui / oui



oui / oui / oui / oui / oui oui / oui / oui / oui / oui

VARIANCE / STDEV non / non non / non non / non non / non oui / oui

ABS / MOD / SIGN / SQRT / PI

non / non / non / non / non

oui / non / non (SGN) / non (SQR) / non

oui / non (%) / oui / oui / oui

oui / non (%) / oui / oui / oui

oui / oui / oui / oui / non

CEIL / FLOOR / ROUND / TRUNC

non / non / non / non

non / non (INT) / non / non

non / oui / oui / non

non / oui / oui / nonoui / oui / oui

/ oui

EXP / LN / LOG / POWER


oui / non / oui / non

oui / non / oui / oui

oui / non / oui / ouioui / oui / non (LOG

(M, n)) / oui

COS / SIN / TAN non / non / non oui / oui / oui oui / oui / oui oui / oui / oui oui / oui /

oui

COSH / SINH / TANHnon / non / non

/ nonnon / non / non non / non / non non / non / non

oui / oui / oui (2)

INITCAP / LPAD / RPAD / REPLACE




non / non / non / oui oui / oui / oui / oui

SOUNDEX / TRANSLATE / INSTR / LENGTH

non (COMME dans QBE) /

non / non / non

non / non / non (LOCATE)

/ non

oui / non (REPLACE) /

non (PATINDEX) /

non (LEN)

oui / non (REPLACE) / non (PATINDEX) / non

(LEN)

oui / oui / non

(LOCATE) / non

TO_CHAR pour numérique / pour date

non / non non / non

non (CONVERT) /

non (CONVERT)

non (CONVERT) / non (CONVERT)

non (auto (3)) / non

ASCII / CHR non / non non / nonoui / non (CHAR)

oui / non (CHAR) oui / oui

ADD_MONTH / MONTH_BETWEEN

non / non

non (DATEADD) /

non (DATEDIFF)

non (DATEADD) /

non (DATEDIFF)

non (DATEADD) / non (DATEDIFF)

oui / oui

LAST_DAY / NEXT_DAY non / non non / non non / non non / non oui / oui

GREATEST / LEAST / DECODE

non / non / non non / non / non non / non / non non / non / non oui / oui /

non

IS NULL / COALESCE / NULLIF

oui / non / non oui / non / non

(ISNULL)oui / oui / non

(ISNULL)oui / oui / oui

oui / non / non (NVL)

ROW VALUE CONSTRUCTOR

non non non non oui



(1) Contrairement à la norme, l’opérateur like d’Access comme celui de SQL Server, ne fait aucune différence entre les majuscules et les minuscules (sauf paramétrage spécifique du serveur). Quand à l’opérateur « escape » il n’est 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 d’Access

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...Conclusion :Curieusement Paradox n’a pas implémenté les opérateurs de récupération des valeurs temporelles courantes alors qu’ils existent en QBE !. Il est facile de s’en passer en passant la date ou l’heure 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 d’un SGBDR à l’autre. 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.

http://www.analytikerna.se:9902/crash-me-choose.htmy

3eme partie - les jointures

Dans le précédent article nous avons commencer à décortiquer le simple SELECT. Dans le présent, nous allons nous consacrer aux jointures entre tables. Toutes les jointures sous toutes les coutûres !Autrement dit comment faire des requêtes portant sur plusieurs tables.

dernière mise à jour :

Par Frédéric BROUARD

dernière mise à jour :


1 Les Jointures ou comment faire des requêtes sur plusieurs tablesVous trouverez des compléments d'information sur le sujet aux pages 129 à 152 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Les jointures permettent d'exploiter pleinement le modèle relationnel des tables d'une base de données.Elle sont faites pour mettre en relation deux (ou plus) tables concourant à rechercher la réponse à des interrogations. Une jointure permet donc de combiner les colonnes de plusieurs tables.

Il existe en fait différentes natures de jointures que nous expliciterons plus en détail. Retenez cependant que la plupart des jointures entre tables s'effectuent en imposant l'égalité des valeurs d'une colonne d'une table à une colonne d'une autre table. On parle alors de jointure naturelle ou équi-jointure. Mais on trouve aussi des jointures d'une table sur elle même. On parle alors d'auto-jointure. Enfin il arrive que l'on doive procéder à des jointures externe, c'est à dire joindre une table à une autre, même si

mailto:[email protected]




la valeur de liaison est absente dans une table ou l'autre. Enfin, dans quelques cas, on peut procéder à des jointures hétérogènes, c'est à dire que l'on remplace le critère d'égalité par un critère d'inégalité ou de différence. Nous verrons au moins un cas de cet espèce.

Une jointure entre tables peut être mise en oeuvre, soit à l'aide des éléments de syntaxe SQL que nous avons déjà vu, soit à l'aide d'une clause spécifique du SQL, la clause JOIN. Nous allons commencer par voir comment à l'aide du SQL de base nous pouvons exprimer une jointure.

1.1 Premiers essais de jointure

Rappel de la syntaxe du SELECT :

C'est ici le pluriel du la partie FROM qui change tout...

Tâchons donc de récupérer les n° des téléphones associés aux clients

exemple 1

Cette requête ne possède pas de critère de jointure entre une table et l'autre. Dans ce cas, le compilateur SQL calcule le produit cartésien des deux ensembles, c'est à dire qu'à chaque ligne de la première table, il accole l'ensemble des lignes de la

seconde à la manière d'une "multiplication des petits pains" ! Nous verrons qu'il existe une autre manière, normalisée cette fois, de générer ce produit cartésien. Mais cette requête est à proscrire. Dans notre exemple elle génère 17 400 lignes...

Il faut donc définir absolument un critère de jointure.

Dans le cas présent, ce critère est la correspondance entre les colonnes contenant la référence de l'identifiant du client (CLI_ID).

exemple 2

Nous n'avons pas fait mieux, car nous avons créé une clause toujours vrai, un peu à la manièreb de 1 = 1 !En fait il nous manque une précision : il s'agit de déterminer de quelles tables proviennent les colonnes CLI_ID de droite et de gauche. Cela se précise à l'aide d'une notation pointée en donnant le nom de la table.

Il est donc nécessaire d'indiquer au compilateur la provenance de chacune des colonnes CLI_ID et donc d'opérer une distinction entre l'une et l'autre colonne.Ainsi, chaque colonne devra être précédée du nom de la table, suivi d'un point.exemple 3

On tombe ici à 174 enregistrements dans la table !!!

Mais il existe une autre façons de faire, plus simple encore. On utilise la technique du "surnommage", c'est à dire que l'on attribut un surnom à chacune des tables présente dans la partie FROM du SELECT :exemple 4

Ici, la table T_CLIENT a été surnommée "C" et la table T_TELEPHONE "T".

Bien entendu, et comme dans les requêtes monotabulaires ont peut poser des conditions supplémentaires de filtrage dans la clause where. Cherchons par exemple

les clients dont les numéros de téléphone correspondent à un fax :exemple 5

Le fait de placer comme critère de jointure entre les tables, l'opérateur logique "égal" donne ce que l'on apelle une "équi-jointure".

REMARQUE IMPORTANTEComme vous pouvez le constater, le nom du client BOUVIER n'apparait pas. Il n'a pas été "oublié" par le traitement de la requête, mais le numéro de fax de ce client n'est pas présent dans la table T_TELEPHONE. Or le moteur SQL recherche les valeurs de la jointure par égalité. Comme l'ID_CLI de BOUVIER n'est pas présent dans la table T_TELEPHONE, il ne peut effectuer la jointure et ignore donc la ligne concernant le client BOUVIER. Nous verrons comment réparer cette lacune lorsque nous parlerons des jointures externes.

NOTA : on peut aussi utiliser les surnoms dans la partie qui suit immédiatement le mot clef SELECT. Ainsi l'exemple 4, peut aussi c'écrire :exemple 6

C'est particulièrement pratique lorsque l'on veut récupérer une colonne qui se retrouve dans les deux tables, ce qui est souvent le cas de la (ou les) colonne de clef étrangère qui permet juutement d'assurer la jointure.

Pour joindre plusieurs tables, on peut utiliser le même processus de manière répétitive...exemple 7

MAIS ATTENTION ! De même que nous l'avons vu dans l'exemple 2.4, ne sont visible ici que les lignes clients ayant A LA FOIS, au moins une adresse, un e mail et au moins un numéro de téléphone... Si nous avions voulu une liste complète des clients avec toutes les coordonnées disponibles, nous aurions du faire une requête externe sur les tables...

1.2 Différents type de jointures (naturelles, équi, non equi, auto, externes, hétérogènes, croisée et union)

Lorsque nous étudions le modèle relationnel de notre base de données exemple nous avons vu que le modèle physique des données, répercute les clefs des tables maîtres en tant que clefs étrangères des tables pour lesquelles une jointure est nécessaire. En utilisant la jointure entre clefs primaires et clefs secondaires basée sur l'égalité des valeurs des colonnes nous exécutons ce que les professionnels du SQL appelle une jointure naturelle. Il est aussi possible de faire des équi-jointures qui ne sont pas naturelles, soit par accident (une erreur !), soit par nécessité. Il est aussi possible de faire des non équi-jointures, c'est à dire des jointures basée sur un critère différent de l'égalité, mais aussi des auto-jointures, c'est à dire de joindre la table sur elle même. Le cas le plus délicat à comprendre est celui des jointures externes, c'est à dire exiger que le résultat comprenne toutes les lignes des tables (ou d'au moins une des tables de la jointure), même s'il n'y a pas correspondance des lignes entre les différentes tables mise en oeuvre dans la jointure. La jointure d'union consiste à ajouter toutes les données des deux tables à condition qu'elles soient compatibles dans leurs structures. La jointure croisée permet de faire le produit cartésien des tables. Enfin on peut effectuer des requêtes hétérogènes, c'est à dire de joindre une table d'une base de données, à une ou plusieurs autres base de données eventuellement même sur des serveurs différents, voire même sur des serveurs de différents types (par exemple joindre une table T_CLIENT de la base BD_COMMANDE d'un serveur Oracle à la table T_PROSPECT de la base BD_COMMERCIAL d'un serveur Sybase !).

UN CONSEIL IMPORTANT

Dans la mesure du possible, utilisez toujours un opérateur de jointure

normalisé (mot clef JOIN).

En effet :

les jointures faites dans la clause WHERE (ancienne syntaxe de 1986 !) ne permettent pas de faire la distinction de prime abord entre ce qui relève du filtrage et ce qui relève de la jointure.

Il est à priori absurde de vouloir filtrer dans le WHERE (ce qui

restreint les données du résultat) et de voiloir "élargir" ce résultat par une jointure dans la même clause WHERE de filtrage.

La lisibilité des requêtes est plus grande en utilisant la syntaxe à base de JOIN, en isolant ce qui est du filtrage et de la jointure, mais aussi en isolant avec clarté chaque condition de jointures entre chaque couples de table

L'optimisation d'exécution de la requête est souvent plus pointue du fait de l'utilisation du JOIN.

Lorsque l'on utilise l'ancienne syntaxe et que l'on supprime la clause WHERE a des fins de tests, le moteur SQL réalise le produit cartésiens des tables ce qui revient la plupart du temps à mettre à genoux le serveur !

2 Syntaxe normalisée des jointures Vous trouverez des compléments d'information sur le sujet aux pages 136 à 152 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Les jointures normalisées s'expriment à l'aide du mot clef JOIN dans la clause FROM. Suivant la nature de la jointure, on devra préciser sur quels critères se base la jointure.Voici un tableau résumant les différents types de jointures normalisées :


Jointure croisée

Nous allons décrire en détail toutes ces jointures

2.1 opérateur de jointure naturelle

Il existe un opérateur normalisé pour effectué en SQL la jointure naturelle des tables :

L'opérateur NATURAL JOIN permet d'éciter de préciser les colonnes concernées par la jointure.Dans ce cas, le compilateur SQL va rechercher dans les 2 tables, les colonnes dont le nom est identique. Bien entendu, le type de données doit être le même !NOTA : on veillera au niveau de la modélisation et notamment au niveau du MPD (Modèle Physique de Données) que les noms des colonnes de clefs en relation avec d'autres tables par l'intermédiaires des clefs étrangères soient strictement identiques.

exemple 8

Mais cette syntaxe est rarement accepté par les moteurs SQL actuels !

La partie optionnelle "USING" permet de restreindre les colonnes concernées, lorsque plusieurs colonnes servent à définir la jointure naturelle. Ainsi la commande SQL :

Revient au même que la commande SQL de l'exemple 8.

2.2 Jointures interne

Comme il s'agit de la plus commune des jointures c'est celle qui s'exerce par défaut si on ne précise pas le type de jointure. Après le mot clef ON on doit préciser le critère de jointure.

Reprenons notre exemple de départ :exemple 9

Ou en utilisant le surnommage :exemple 10

Plus pratique à écrire et aussi lisible sinon plus !

NOTA : le mot clef INNER est facultatif. Par défaut l'absence de précision de la nature de la jointure la fait s'exécuter en jointure interne.Ainsi on peut reformuler le requête ci dessus en :

Ou en utilisant le surnommage :exemple 11

2.3 les jointures externes

Les jointures externes sont extrêmenent pratique pour rapatrier le maximum d'informations disponible, même si des lignes de table ne sont pas renseignées entre les différentes tables jointes.

Procédons à l'aide d'un exemple pour mieux comprendre la différence entre une jointure interne et une jointure externe. Nous avons vu à l'exemple 9 que seul les clients dotés d'un numéro de téléphone étaient répertoriés dans la réponse. Ainsi, le client BOUVIER était absent.

Exemple 12

Que faut-il modifier dans la requête pour obtenir une ligne BOUVIER avec aucune référence de téléphone associée dans la réponse ?Il suffit en fait d'opérer à l'aide d'une jointure externe :

Exemple 13

La syntaxe de la jointure externe est la suivante :

Les mots clefs LEFT, RIGHT et FULL indique la manière dont le moteur de requête doit effectuer la jointure externe. Il font réference à la table située à gauche (LEFT) du mot clef JOIN ou à la table située à droite (RIGHT) de ce même mot clef. Le mot FULL indique que la jointure externe est bilatérale.

On recherche toutes les valeurs satisfaisant la condition de jointure précisée dans prédicat, puis on

able TGauche qui n'ont pas été prises en compte au titre de la

satisfaction du critère.

On recherche toutes les valeurs satisfaisant la condition de jointure précisée dans prédicat, puis on

rajoute toutes les lignes de la table TDroite qui n'ont pas été prises en compte au titre de la

satisfaction du critère.

aisant la condition de jointure précisée dans prédicat, puis on

rajoute toutes les lignes de la table TGauche et TDroite qui n'ont pas été prises en compte au titre de

la satisfaction du critère.

NOTA : il existe des équivalences entre différentes expressions logiques à base de jointures externes. Les principales sont :

la jointure externe droite peut être obtenue par une jointure externe gauche dans laquelle on inverse l'ordre des tables.

la jointure externe bilatérale peut être obtenue par la combinaison de deux jointures externes unilatérales avec l'opérateur ensemblsites UNION.

Remplacement d'une jointure externe droite par une jointure externe gauche. L'équivalent logique de : Exemple 14 :

est :

Remplacement d'un FULL OUTER JOIN avec jointures externes gauche et droite :L'équivalent logique de ...Exemple 15 :

... est :

Remplacement d'un FULL OUTER JOIN avec jointures externes gauche uniquement :L'équivalent logique de ...Exemple 16 :

... est :

2.4 Différence entre jointure externe et jointure internePour bien comprendre la distinction entre les jointures internes et externes, nous devons consacrer quelques instants à aborder des problèmes de logique ensembliste sous un oeil pragmatique.

2.4.1 L'hypothèse du monde closLes jointures externes sont extrêmement pratiques pour rapatrier le maximum d'informations disponible, même si des lignes de table ne sont pas renseignées entre les différentes tables jointes.Sans le savoir, nous faisons assez systématiquement l'hypothèse du monde clos. C'est à dire que nous considérons que l'absence d'information, n'est pas une information. Si vous demandez à une secrétaire de vous communiquer les coordonnées des clients qui sont domiciliés à Paris, elle vous donnera une liste où figurera autant de fois le nom PARIS qu'il y a de clients dans la liste, et ceci paraît bien normal ! Sauf que... comme l'aurait fait tout un chacun, votre secrétaire a fait l'hypothèse du monde clos sans le savoir en présumant que les clients pour lesquels l'adresse n'est pas renseignés ne sont pas domiciliés à PARIS !C'est cela l'hypothèse du monde clos : considérer que l'absence d'information doit être synonyme de critère de discrimination... La jointure externe permet de contrer l'hypothèse du monde clos en considérant qu'en cas d'absence de jointure entre une table et l'autre, on ne supprime par pour autant l'information.

2.4.2 Mécanisme en jeuLorsqu'une ligne d'une table figurant dans une jointure n'a pas de correspondance dans les autres tables, le critère d'équi-jointure n'est pas satisfait et la ligne est rejetée. C'est la jointure interne. Au contraire, la jointure externe permet de faire figurer dans le résultat les lignes satisfaisant la condition d'équi-jointure ainsi que celles n'ayant pas de correspondances.Ainsi, si je veux contacter tous mes clients, quelque soit le mode de contact que je veux utiliser dans le cadre d'une campagne publicitaire, j'ai intérêt à obtenir une

réponse contenant TOUS les clients, même ceux qui n'ont pas de téléphone, d'e-mail ou d'adresse...Ainsi la requête de l'exemple 2.7, complètée (et améliorée) peut être récrite :exemple 17

NOTA :Sur certains moteurs SQL la jointure bilatérale externe (FULL OUTER JOIN) s'exprime :

D'anciennes syntaxes permettent de faire des jointures externes unilatérale. Par exemple, il n'est pas rare de rencontrer les syntaxes suivantes :

Elles sont bien évidemment à proscrire si la syntaxe SQL 2 est disponible !2.4.3 Discussion sur la jointure externeLa jointure externe est rarement bien comprise du premier coup. Si je vous propose de lire cette discussion qui a eût lieu sur un forum Internet, c'est parce quelle permet de mieux la comprendre ...

Et comme Frédéric va surement me répondre, je prend un exemple de son site.

Au niveau performance, il n'y a aucune différence, l'optimiseur de

SQL/Server contruit le même plan

différencier ce qui

appartient à la jointure et ce qui appartient à la limitation du jeu de

résultat

Frédéric BROUARD

Pour comprende la différence entre le prédicat de la clause de jointure

et le prédicat du filtre WHERE, je vous propose le petit test suivant :

équivalente ??? En apparence seulement !

d'accord... même rés

pas d'acord !!! résultat différend...

je vous laisse méditer

Lionel Pénuchot

Tu peux alors m'expliquer pourquoi cette requête renvoie le même résultat que ta dernière ?

Frédéric BRO

La clause WHERE est un filtre d'élimination. Il suppose que les

différentes tables sont déjà jointes en une seule et parcoure

l'ensemble des résultats à la recherche des lignes correspondant aux

itères donnés.

En revanche la clause JOIN se comporte différemment. Elle agit AVANT

Dans le cadre d'une jointure INTERNE le comportement est similaire à

Dans le cas d'une jointure externe il faut décomposer la logique en

plusieurs étapes :

1) évaluation des éléments du prédicat

Reprennons la clause de jointure de la requête donnée par Laurent :

prédicat de jointure prédicats hors jointure

1° 1=0 => aucune ligne n'est récupérée

2° TJ1.COL2 = 'taratata' => encore une fois aucune ligne n'est récupérée

3° TJ1.COL1 = TJ2.COL1 => 2 lignes sont en correspondance avec TEST_JOIN2

deux lignes seront récupérées du fait de la jointure

Pour comprendre la différence, il suffit de demander à voir les colonnes

de la table TEST_JOIN2 dans les différents cas :

et voilà !

2.5 Jointure croisée La jointure croisée n'est autre que le produit cartésien de deux tables. Rapellons que le produit cartésiens de deux ensemble n'est autre que la multiplication généralisée. Dans le cas des tables, c'est le fait d'associer à chacune des lignes de la première table, toutes les lignes de la seconde. Ainsi, si la première table compte 267 lignes et la seconde 1214, on se retrouve avec un résultat contenant 324 138 lignes. Bien entendu, s'il n'y a aucun doublon dans les tables, toutes les lignes du résultat seront aussi uniques.La jointure croisée peut s'écrire de deux manières différentes...... à l'aide de l'opérateur normalisé :

... ou à l'aide d'une clause FROM simplifiée

Ce qui est certainement l'expression la plus minimaliste de tous les ordres SELECT du SQL !Nous voudrions savoir si notre table des tarifs de chambre (TJ_TRF_CHB) est complète, c'est à dire si l'on a bien toutes les chambres (T_CHAMBRE) pour toutes les dates de début de tarif (T_TARIF) :exemple 18

En comparant rapidement le contenu des deux tables on peut s'assurer que tous les tarifs ont bien été renseignés.Avec la syntaxe normalisée, cette requête s'écrit :exemple 19

et donne le même résultat !

2.6 Jointure d'union La jointure d'union, permet de faire l'union de deux tables de structures quelconque. Elle s'exprime qu'à l'aide de l'opérateur normalisé SQL2 suivant :

Effectuons par exemple la jointure d'union entre les tables T_TITRE ("M.", "Mme.", "Melle." ) et T_TYPE ("FAX", "GSM", "TEL") :exemple 20

Télécopie

Téléphone portable

Téléphone fixe

En fait c'est comme si l'on avait listé la première table, puis la seconde en évitant toute colonne commune et complèter les espaces vides des valeurs "NULL"...Mais cette jointure est très rarement implantée dans les moteurs SQL.NOTA : si l'opérateur UNION JOIN n'est pas présent dans votre moteur, vous pouvez le fabriquer comme suit :, car elle peut être facilement remplacée par un autre ordre SQL presque aussi simple :

ON <critère>

Ou la condition <critère> est n'importe quel prédicat valant toujours faux comme "1=2".En dehors du fait de linéariser des tables hétérogènes, il est franchement permis de douter de l'utilité d'un tel opérateur.

3 Nature des conditions de jointuresNous allons maintenant analyser les différentes jointures basées sur la nature des conditions pour bien les distinguer.

3.1 Équi-jointureL'équi jointure consiste à opérer une jointure avec une condition d'égalité. Cette condition d'égalité dans la jointure peut ne pas porter nécessairement sur les clefs (primaires et étrangères).Recherchons par exemple les clients dont le nom est celui d'une ville contenu dans la table des adresses :exemple 21

Nous avons donc bien réalisé une équi jointure, mais elle n'est pas naturelle parce qu'elle ne repose pas sur les clefs des tables.Bien entendu, il existe un opérateur normalisé SQL 2 permettant de traiter le cas de

l'équi-jointure :

Le mot clef INNER n'étant pas obligatoire, mais voulant s'opposer aux mot clefs OUTER, UNION et CROSS.Ainsi, la requête précédente, s'écrit à l'aide de cette syntaxe :exemple 22

3.2 Non équi-jointureIl s'agit là d'utiliser n'importe quelle condition de jointure entre deux tables, exceptée la stricte égalité. Ce peuvent être les conditions suivantes :

supérieur

supérieur ou égal

inférieur

inférieur ou égal

différent de

En règle générale ou trouve des non équi-jointures dans le cadre de comparaisons temporelles ou de mesures physiques. Par exemple on pourrait rechercher une pièce mécanique dans un stock qui soit de même nature ou de même fonction qu'une pièce donnée, mais plus légère...Nous voulons obtenir les factures qui ont été émises avant que le prix des petits déjeuners n'atteigne 45 F...exemple 23

NOTA : pour récupérer toutes les colonnes d'une table, on peut utiliser l'opérateur * suffixé par le nom de table, comme nous l'avons fait ici pour la table des factures.Si notre table des tarifs avait été organisée par tranches, comme ceci :

alors,récupérer le tarif des chambres pour chacune des dates du planning devient un exercice très simple :exemple 24

Nous avons donc à nouveau un exemple remarquable de non equi-jointure.Remarque : Constatons que la colonne TRF_DATE_FIN de cette nouvelle version de la table des tarifs implique une redondance de l'information. En effet cette date de fin est déductible de la date de début de la ligne qui contient la date immédiatement postérieure avec un jour de moins. De plus le problème induit par cette organisation des données fait qu'il faut obligatoirement définir une date de fin des tarifs, même dans le futur, sinon certaines tarifications ne pourront être établies par cette

requête... Il ne s'agit donc pas d'une modélisation correcte !

3.3 Auto jointureLe problème consiste à joindre une table à elle même. Il est assez fréquent que l'on ait besoin de telles auto jointures car elle permettent notamment de modéliser des structures de données complexes comme des arbres. Voici quelques exemples de relation nécessitant une auto jointure de tables :

dans une table des employées, connaître le supérieur hiérarchique de tout employé

dans une table de nomenclature savoir quels sont les composants nécessaires à la réalisation d'un module, ou les modules nécessaires à la réalisation d'un appareil

dans une table de personnes, retrouver l'autre moitié d'un couple marié...

La représentation d'une telle jointure dans le modèle de données, se fait par le rajout d'une colonne contenant une pseudo clef étrangère basée sur le clef de la table. Dans ce cas, une syntaxe possible pour l'auto jointure est la suvante :

efEtrangère

C'est l'exemple typique ou l'utilisation de surnoms pour les tables est obligatoire, sinon il y a risque de confusion pour le moteur SQL... Pour donner un exemple concret à nos propos nous allons modéliser le fait qu'une chambre puisse communiquer avec une autre (par une porte). Dès lors, le challenge est de trouver quelles sont les chambres qui communiquent entre elles par exemple pour réaliser une sorte de suite...Pour ce faire, nous allons ajouter à notre table des chambres une colonne de clef étrangère basée sur la clef de la table.Dans ce cas, cette colonne doit obligatoirement accepter des valeurs nulles !1

Voici l'ordre SQL pour rajouter la colonne CHB_COMMUNIQUE dans la table T_CHAMBRE :

Alimentons là de quelques valeurs exemples en considérant que la 7 communique avec la 9 et la 12 avec la 14 :

Pour formuler la recherche de chambres communiquantes, il suffit de faire la requête suivante :exemple 25

ou la table T_CHAMBRE figure deux fois par l'entremise de deux surnommages différents c1 et c2.Notons que cette présentation n'est pas pratique car elle dédouble le nombre de couples de chambres communiquantes, donnant l'impression qu'il y a 4 couples de chambres communiquantes ce qui est faux... Il manque juste un petit quelque chose pour que cette requête soit parfaite. Il suffit en effet de ne retenir les solutions que pour des identifiants inférieurs ou supérieur d'une table par rapport à l'autre :exemple 26

3.4 Jointure hétérogèneUne jointure hétérogène consiste à joindre dans une même requête, des tables provenant de bases de données différentes, voire de serveurs de données différents. Un tel type de jointure n'est possible que :

entre deux bases d'un même serveur : que si le SQL du serveur le permet (par exemple deux bases Oracle, deux bases SQL Server...)

entre deux bases de deux serveurs différents : qu'en passant par un outil tiers de type "middleware" (par exemple une entre une base Oracle et une base Sybase). L'un des rares middleware a faire ce type de jointure est le BDE (Borland Database Engine) d'Inprise.

Dans ces deux cas, il faut bien vérifier la compatibilité des types car il se peut que des résultats surprenants apparaissent notament dans le traitement des nombre réels...

Pour effectuer de telles requêtes, la syntaxe est toujours spécifique au moteur employé. Avec le BDE le niveau de SQL est SQL 2, cantoné aux focntions de base.Voici un exemple de requête entre deux serveur à l'aide du BDE :exemple 27 requête que l'exemple 2.4 avec jointure hétérogène entre 2 bases de 2 serveurs grâce au BDE de Borland / Inprise / Corel

En fait le BDE se sert d'alias permettant de définir à la fois le serveur et la base concerné. Ici les alias sont : ":ORACLE_CLIBD:" et ":SYBASE_TELBD:"

4 Récapitulatif des jointures normalisée 4.1 terminologie et syntaxe des jointuresJOINTURE NATURELLE : la jointure s'effectue sur les colonnes communes, c'est à dire celles de même nom et type :

prédicat] ...

Le mot clef USING permet de restreindre les colonnes communes à prendre en considérationJOINTURE INTERNE : la jointure s'effectue entre les tables sur les colonnes précisées dans la condition de jointure :

prédicat] ...

JOINTURE EXTERNE : la jointure permet de récupérer les lignes des tables correspondant au critère de jointure, mais aussi celle pour lesquelles il n'existe pas de correspondances.

prédicat] ...

RIGHT OUTER : la table à droite de l'expression clef "RIGHT OUTER" renvoie des lignes sans correspondance avec la table à gauche.

LEFT OUTER : la table à gauche de l'expression clef "LEFT OUTER" renvoie des lignes sans correspondance avec la table à droite.FULL OUTER : les deux tables renvoient des lignes sans correspondance entre elles.JOINTURE CROISÉE : la jointure effectue le produit cartésien (la "multiplication") des deux tables. Il n'y a pas de condition

prédicat] ...

JOINTURE D'UNION : la jointure concatène les tables sans aucune correspondances de colonnes

Il n'y a pas de critère de jointureNOTA : si votre SGBDR n'implémente pas la jointure externe droite, inversez l'ordre des tables et faire une jointure externe gauche lorsque cela est possible.4.2 Arbre de jointureLa jointure de multiple tables peut se représenter sous la forme d'un arbre. Cet arbre possède donc une racine, c'est la table principale, celle d'ou l'on veut que l'information parte. Elle possède aussi des feuilles, c'est à dire des tables d'entités. Les tables situées entre la racine et les feuilles, sont souvent des tables de jointure, possédant en principe deux clef étrangères. Dans le principe toute table de jointure devrait être un noeud de l'arbre.La représentation arborescente d'une jointure est un excellent moyen pour visualiser si la clause de jointure de votre requête est à priori correcte. En effet, une référence circulaire dans la clause de jointure ne peut pas être représentée sous la forme d'un arbre et il y a fort à parier que la requête est incorrecte.Voici par exemple la requête qui "met à plat", la base hotel :

adresse, table d'entité (feuille de l'arbre)

titre, table d'entité (feuille de l'arbre)

mail, table d'entité (feuille de l'arbre)

téléphone, table d'entité servant de jointure (noeud dans

type de téléphone, table d'entité

date du planning, table d'entité (feuille de l'arbre)

chambre, table d'entité serv

tarif, table d'entité (feuille de l'arbre)

facture, table d'entité servant de jointure

ligne de facture, table d'entité (feuille de l'arbre)

mode de paiement, table d'entité (feuille de l'arbre)

Correctement indenté on distingue déjà la structure arborescente. On peut la mettre en évidence en supprimant tout ce qui n'est pas un nom de table :

adresse, table d'entité (feuille de l'arbre)

titre, table d'entité (feuille de l'arbre)

mail, table d'entité (feuille de l'arbre)

téléphone, table d'entité servant de jointure (noeud dans

type de téléphone, table d'entité

date du planning, table d'entité (feuille de l'arbre)

chambre, table d'entité servant de jointure (noeud dans l'arbre)

tarif, table d'entité (feuille de l'arbre)

facture, table d'entité servant de jointure

ligne de facture, table d'entité (feuille de l'arbre)

mode de paiement, table d'entité (feuille de l'arbre)

Par cette indentation, il est facile de repérer les jointures entre les tables.

NOTE IMPORTANTE :Les jointures ne sont pas la seule manière de mettre en realtion différentes tables aus ein d'une même requête SQL. On peut aussi joindre plusieurs tables à l'aide des sous requêtes ainsi qu'à l'aides des opérateurs ensemblistes que nous allons voir aux deux prochains chapitres.

Résumé

Voici les différences entre les moteurs des bases de données

Paradox Access PostGreSQL Sybase SQL Server 7

Oracle 8 DB2 (400)

INNER JOIN oui oui oui oui oui non (1) oui

OUTER JOINLEFT,

RIGHT, FULL

LEFT, RIGHT

LEFT, RIGHT, FULL

LEFT, RIGHT, FULL

LEFT, RIGHT, FULL

non (1) LEFT

(4)

UNION JOIN non (2) non non non (2) non (2) non (1)(2) non

CROSS JOIN non (3) non (3) oui non (3) oui non (1)(3) oui

(1) Oracle ne connais toujours pas le JOIN (ça fait quand même plus de dix ans de retard pour cet éditeur pionnier qui semble s'endormir sur ses lauriers). Il faut donc utiliser une syntaxe propriétaire. Exception : la version 9 supporte enfin les jointures normalisées.(2) Possible avec un FULL OUTER JOIN(3) Possible avec l'ancienne syntaxe sans précision de critère WHERE(4) de plus IBM DB2 (400) dispose d'un très intéressant "exception join" equivalent à :

éférences de jointure

Données concernant DB2 400 aimablement communiquées par kerigan

4eme partie - groupage, sous ensembles et opérateurs ensemblistes

Le groupage et les requêtes portants sur des sous ensembles constituent souvent la partie la plus difficile à comprendre du SQL... C'est pourquoi nous y consacrerons un long article.Enfin, nous verrons différents opérateurs ensemblistes pour manipuler les tables à la manière des patates de Venn.

dernière mise à jour : 26 août 2003 à 08:26



1 La clause GROUP BY Vous trouverez des compléments d'information sur le sujet aux pages 153 à 154 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

La clause BROUP BY est nécessaire dès que l'on utilise des fonctions de calculs statistiques avec des données bruts. Cette clause groupe les lignes sélectionnées en se basant sur la valeur de colonnes spécifiées pour chaque ligne et renvoie une seule ligne par groupe.On peut la comparer à une opération de découpage de sous ensemble un peut à la manière des "niveaux de rupture" lorsque l'on réalise des états imprimés.

Cherchons à compter le nombre de chambre par étage de notre hôtel :exemple 1





La requête, telle que présentée ci dessus n'est pas calculable. En effet comment décider si le comptage doit se faire pour chaque chambre ou pour un groupe de chambre et notamment les groupes formés par chacun des étages ?

Si la requête se faisait pour chaque chambre, le résultat serait :

Et n'aurait pas beaucoup de sens !

En revanche, un regroupement par étage, donne tout son sens à la requête :

Pour réaliser un tel regroupement il faut introduire une clause de groupage dans la requête. Pour cela SQL fournit le clause GROUP BY :

exemple 1 (bis)

NOTA :

La présence de la clause GROUP BY est nécessaire dès que la clause de sélection, ou le filtre WHERE, ou encore les jointures comportent

simultanément des calculs d'agrégation et la présence de colonnes de table hors de calculs d'agrégation.

De plus, outes les colonnes représentées hors des calculs d'agrégation doivent figurer dans la clause GROUP BY

La plupart du temps, le moteur de requête vous avertira d'un probable incohérence de calcul des agrégats à l'aide d'un message d'erreur (avant exécution de la requête), du genre : "La colonne ... est incorrecte dans la liste de sélection parce qu'elle n'est pas contenue dans une fonction d'agrégation et qu'il n'y a pas de clause GROUP BY" (SQL Server).

Cherchons maintenant à compter le couchage de chaque étage :exemple 2

Un peu plus compliqué, cherchons à savoir quel a été le nombre de nuitées pour chaque chmabre au cours de l'année 1999 (une nuitée étant une personne passant une nuit dans une chambre. Si deux personnes occupent la même chambre cela fait deux nuitées) :exemple 3

Partant de la, nous pouvons rechercher le taux d'occupation de chaque chambre dans cette periode.Le nombre maximal de nuitées étant le nombre de couchage de chaque chambre multiplié par toutes les dates de l'année, ce calcul s'obtient par :exemple 4

Il ne suffit plus que de "raccorder" les requêtes des exemples 3 et 4 :exemple 5

Nous allons voir, maintenant que la clause GROUP BY est souvent utilisée avec la clause HAVING...

2 La clause HAVING Vous trouverez des compléments d'information sur le sujet aux pages 153 à 154 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

La clause HAVING agit comme le filtre WHERE, mais permet de filtrer non plus les données, mais les opérations résultant des regroupements, c'est à dire très généralement toute exptression de filtre devant introduire un calcul d'agrégation.Pour tenter de comprendre l'utilité de la clause having, nous allons procéder par un exemple simple : recherchons un étage de l'hôtel capable de coucher au moins 20 personnes.Partant de notre exemple 2, nous serions tenter d'écrire :exemple 6

Mais cette requête va imanquablement provoquer une erreur avant exécution du fait de la clause WHERE. En effet, souvenons nous que le filtre WHERE agit sur les données des tables et permet de filtrer ligne après ligne. Or le filtrage ne porte plus sur la notion de lignes, mais sur une notion de sous ensemble de la table. En d'autre termes, le filtre, ici, doit porter sur chacun des groupes. C'est pourquoi SQL introduit le filtre HAVING qui porte, non pas sur les données, mais sur les calculs résultants des regroupements.En l'occurence, dans notre exemple, nous devons déporter le filtre WHERE dans la clause HAVING et l'opération deviendra possible :exemple 6 bis

Autre exemple :Partant de la requête vue à l'exemple 6, essayons de ne retenir que les chambres occupées à plus de 2/3, soit 66.666666... % ?Une première tentative consisterait à écrire :exemple 7


Un autre exemple intéressant est de rechercher quelles sont les chambres qui ont été innocupées plus de 10 jours au cours du mois de janvier 2000...Pour calculer le nombre de jour ou les chambres ont été occupées au cours de janvier 2000, il suffit de faire :exemple 8

Mais nous voulons le complément de cette colonne NOMBRE avec le nombre de jours du mois de janvier 2000, ce qui représente 31 jours. La requête devient donc :exemple 9

Dès lors il ne suffit plus que de filtrer le calcul :

pour ne retenir que les valeurs supérieures à 10. Comme cette expression contient une fonction d'agrégation, ce qui suppose un groupage, il est nécessaire d'utiliser le filtre WHERE :exemple 10

Pour dernier exemple, nous allons nous placer dans un contexte plus proche de la réalité. Voici notre directeur qui souhaite inciter les clients avec lequel il a peu

travaillé à venir plus souvent. L'idée lui prend d'offrir un bon de réduction de 15% sur l'ensemble des prestations hotelières pour tous les clients ayant eut un chiffre d'affaire inférieur à 15 000 F HT au cours de l'année 2000.Une première approche consisterais par exemple a rechercher toutes les chambres libres entre les deux dates :exemple 11

SELECT F.CLI_ID, -- le calcul est le suivant : -- (quantité * montant * remise en %) - remise en francs -- les colonnes "remises" pouvant avoir des valeurs NULL -- il faut les remplacer par un zéro à l'aide de la fonction COALESCE SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100,

0))) - COALESCE(LIF_REMISE_MONTANT, 0))

AS CA FROM T_FACTURE F INNER JOIN T_LIGNE_FACTURE L ON F.FAC_ID = L.FAC_ID WHERE EXTRACT(YEAR FROM F.FAC_DATE) = 2000 GROUP BY C.CLI_ID HAVING SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100,

0))) - COALESCE(LIF_REMISE_MONTANT, 0)) <

15000

Les clients 1, 37, 39, 40, 52, 61, 87 étant bien les personnes visées par la promotion.Mais poussons notre raisonnement... Imaginons une personne ayant été cliente en 1999 et pas en 2000. Pour cela rajoutons les lignes suivantes dans notre jeu de données :

L'exécution de la requête de l'exemple 10, nous délivre le même résultat, alors que nous aimerions récupérer le client 101... Quelle erreur avons nous commise ?Le fait est que nous avons à nouveau fait l'hypothèse du monde clos ! (voir à la page

Le SQL de A à Z, 3eme partie, le paragraphe 2.4) Il faut impérativement remonter jusqu'à la table des clients et réaliser des jointures externes sur les tables des factures et des lignes de facture :exemple 12

SELECT C.CLI_ID, SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100,


AS CA FROM T_CLIENT C LEFT OUTER JOIN T_FACTURE F ON C.CLI_ID = F.CLI_ID LEFT OUTER JOIN T_LIGNE_FACTURE L ON F.FAC_ID = L.FAC_ID WHERE EXTRACT(YEAR FROM F.FAC_DATE) = 2000 GROUP BY C.CLI_ID HAVING SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100,


15000

Or et pour aussi bizarre que cela puisse paraître, cette requête ne nous donne toujours pas le client 101... Pourquoi ? Il persiste en fait deux raisons à cela :1) nous avons supposé que le calcul du CA retournait une valeur...2) nous avons filtré sur l'année 2000, après avoir effectué la jointure.Pour contrer le cas n°1, nous ne devons pas oublier que les NULL se propagent dans les calculs et que le marqueur NULL ne peut être comparée à aucune valeur. Il faut donc réaliser le fitre HAVING en retenant l'expression si elle vaut moins de 15000 mais aussi si elle vaut NULL.Pour contrer le cas n°2, et c'est beaucoup plus subtil, il faut inclure dans la condition de jointure entre les tables T_CLIENT et T_FACTURE l'expression située dans la clause WHERE.La requête ainsi corrigée, devient :exemple 13

SELECT C.CLI_ID, SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100,


AS CA FROM T_CLIENT C LEFT OUTER JOIN T_FACTURE F ON C.CLI_ID = F.CLI_ID AND EXTRACT(YEAR FROM F.FAC_DATE) = 2000

http://sqlpro.developpez.com/SQL_AZ_3.html#JOIN_externe_diff

LEFT OUTER JOIN T_LIGNE_FACTURE L ON F.FAC_ID = L.FAC_ID GROUP BY C.CLI_ID HAVING SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100,


15000 OR SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100,


IS NULL

Nous pourrions ainsi passer encore plusieurs cas en revue tant le mélange des clauses WHERE et HAVING associées aux jointures externes peut devenir passionnant, mais ce site n'a pas pour but de vous poser des pièges, mais plutôt de vous faire toucher du doigt la problématique de ce type de requête et le logique ensembliste sous jacente.Dans la suite du site et notamment lorsque vous aborderez les sous requêtes, de nouveaux exemples utilisant la clause HAVING et le groupage, sont présentés.

3 Les opérateurs ensemblistes Vous trouverez des compléments d'information sur le sujet aux pages 180 à 187 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

C'est une des parties les plus simple de l'ordre SELECT. A la fois par sa syntaxe mais sussi par sa compréhension facile. Il s'agit, ni plus ni moins que de réaliser des opérations sur les ensembles représentés par des tables ou des extraits de table. Les opérations ensemblistes du SQL sont l'union, l'intersection et le différence.

3.1 L'UNION Pour faire une union, il siffit de disposer de deux ensembles de données compatibles et d'utiliser le mot clef UNION. La syntaxe est alors :

Bien entendu il est indispensable que les deux ordres SELECT :

produisent un même nombre de colonnes;


que les types de données de chaque paires ordonnées de colonnes soient de même type (ou d'un type équivalent).

3.2 L'INTERSECTION La démarche est la même pour faire une intersection, que pour le mécanisme de l'union. La syntaxe utilisant le mot clef INTERSECT :

Avec les mêmes contraintes d'ordre syntaxique

3.3 LA DIFFÉRENCE La différence de deux ensembles s'obtient de la même manière, en utilisant le mot clef EXCEPT :

3.4 Considération diverses sur les opérations ensemblistes du SQL3.4.1 Le mot clef ALL peut qualifier l'opérateur ensembliste.ALL récupérera tous les doublons des ensembles considérés.ATTENTION : l'opérateur par défaut opère le dédoublonnage contrairement à la clause SELECT de l'ordre SELECT.Ainsi lorsque l'on va utiliser une telle construction pour des opérations comptable récupérant des sommes financières en utilisant l'opérateur UNION, on devra impérativement utiliser le mot clef ALL sinon les dommes identiques disparaitrons.3.4.2 On peut restreindre l'intersection à certaines colonnes en utilisant le filtre CORRESPONDING BY.Ainsi on poura récupérer dans le résultats plus de colonnes que celles qui opèrent la véritable intersection.3.4.3 Les opérateurs UNION et INTERSECT sont réflexifs, mais EXCEPT ne l'est pas.Ainsi l'ordre des requêtes de selection n'a pas d'importance pour les opération d'union et d'intersection, mais pour la différence cet ordre est essentiel et ne donne pas le même résultat en cas de permutation (c'est la propre de la soustraction et de la division).3.4.5 On peut utiliser la clause ORDER BY.La clause ORDER BY doit être la clause finale, car il ne peut y en avoir qu'une.

3.4.6 Le nom des colonnes du résultat est celui du premier des ordres SELECT composant la globalité de la requête.Autrement dit si les noms des colonnes sont différents entre les divers ordres SELECT s'enchainant avec des opérateurs ensemblistes, ceux qui ont prépondérance sont ceux des colonnes de la première requête SELECT.3.4.7 Mis à part l'union, les opérations ensemblistes d'intersection et de différence peuvent être construites à partir de requêtes de base.Exemples :L'intersection peut être établie par :

La différence peut être établie par :

t2.* étant n'importe quelle colonne de t2

3.5 Quelques exemplesPour présenter des exemples significatifs, voici le jeu de test que nous allons utiliser :

INSERT INTO T_MACHINE (MAC_NOM, MAC_PRIX, MAC_REF) VALUES ('RÉVEIL', 128, 'LIP

3.5.1 exemple d'UNIONCommençons par la basique union des noms des objets et machines de nos deux tables :exemple 14

SELECT OBJ_NOM FROM T_OBJET UNION SELECT MAC_NOM FROM T_MACHINE

RÉVEIL

Remarquons que la moto, comme la perçeuse ne figurent qu'une seule fois dans le résultat. Ce défaut peut être corrigé par l'emploi du mot clef ALL :exemple 15

SELECT OBJ_NOM FROM T_OBJET UNION ALL SELECT MAC_NOM FROM T_MACHINE

RÉVEIL

Voici symbolisé par un dessin, le principe de l'union tel que décrit dans les deux requêtes précédentes :

union de l'ensemble des noms (OBJ_NOM + MAC_NOM) des tables OBJET et MACHINE

3.5.2 exemple de DIFFÉRENCELa différence de l'ensemble des noms des objets par rapport à celui des noms de machine, nous donnera :exemple 16

SELECT OBJ_NOM FROM T_OBJET EXCEPT SELECT MAC_NOM FROM T_MACHINE

Elle peut aussi être exprimée en l'absence d'opérateur EXCEPT, par :exemple 17

SELECT OBJ_NOM FROM T_OBJET WHERE OBJ_NOM NOT IN (SELECT MAC_NOM FROM T_MACHINE)

Ou encore :exemple 18

Voici le diagramme de Venn correspondant à la différence telle que les requêtes que nous venons, l'exprime :

différence entre les noms des objets et ceux des machines

3.5.3 exemple d'INTERSECTIONQuand à l'intersection, celle-ci n'est pas plus difficile :exemple 19

SELECT OBJ_NOM FROM T_OBJET INTERSECT SELECT MAC_NOM FROM T_MACHINE

Que l'on peut exprimer par l'équivalence logique :exemple 20

SELECT OBJ_NOM FROM T_OBJET O JOIN T_MACHINE M ON O.OBJ_NOM = M.MAC_NOM

Ou encore :exemple 21

SELECT OBJ_NOM FROM T_OBJET O WHERE OBJ_NOM IN (SELECT MAC_NOM FROM T_MACHINE)

Et par un dessin reprennant les célèbres patates, le principe de l'intersection tel qu'exprimé décrit dans les requêtes ci dessus :

intersection de l'ensemble des noms (OBJ_NOM + MAC_NOM) des tables OBJET et MACHINE

Un petit dernier exemple sera consacré à l'intersection avec correspondance. Pour cela nous avons besoin de modifier nos deux tables (ou au moins l'une d'entre elles) de façon a ce que les noms des colonnes des tables soient identiques.Par exemple, notre nouveau jeu de données pourrait être :

INSERT INTO T_MACHINE2 (NOM, PRIX, REF) VALUES ('RÉVEIL', 128, 'LIP

Dès lors, la requête suivante :exemple 22

Le mot clef CORRESPONDING se comporte un peu à la manière du NATURAL JOIN et de son mot clef USING, c'est à dire qu'en le précisant, SQL va chercher la correpondance des colonnes par leur position, nom et type.On peut en outre préciser sur quelles colonnes la correspondance va porter, à l'aide du mot clef BY :exemple 23

A noter que l'usage du mot clef est accepté dans

l'intersection comme dans l'union. Cependant, rare sont les moteurs SQL a l'accepter !

RésuméVoici les différences entre les moteurs des bases de données

Paradox Access Sybase SQL Server Oracle

GROUP BY oui oui oui oui oui

HAVING oui oui oui oui oui

UNION oui oui oui oui oui

INTERSECT non non non non ?

EXCEPT non non non non MINUS

CORRESPONDING BY

non non non non non

5eme partie - les sous requêtes

Nous voici maintenant dans le plus puissant du SQL avec les sous requêtes, c'est à dire des requêtes situées à l'intérieur d'autres requêtes, et plus puissant nous aborderons la corrélation des sous requêtes...




1 Les sous requêtes Vous trouverez des compléments d'information sur le sujet aux pages 154 à 174 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Avec SQL il est possible d'imbriquer des requêtes un peu à la manière de poupée gigognes.

Mais pour toucher du doigt ce que l'on peut faire avec des sous requêtes, posons nous la question :

Où placer une sous requête ?

Observons les types de résultats qu'une requête produit pour déterminer les emplacements qu'elle peut prendre au sein d'un ordre SELECT :

Résultat Typologie des résultats

Une table composée





Noëlle

Arsène

André

Une table d'une seule colonne, c'est à dire une liste...

Une table d'une seule ligne et d'une seule colonne, c'est à dire

Pas de réponse (une table vide) et par opposition, une table NON

Voyons maintenant comment imbriquer cela dans une autre requête...

Imbrication requête

(résultat de requête)

Typologie de résultat et

requête

Représentation

Noëlle

equête renvoyant

une table peut être

imbriqué dans la clause

requête...

table du résultat de la requête, car l

nommées.

Une requête renvoyant

être imbriqué dans le

prédicat IN... mais aussi

dans les prédicats ALL,

Arsène

André


être imbriquée dans un

rédicat MATCH et

comparé à une ligne

valuée.


être imbriquée partout


être imbriquée dans un

prédicat EXISTS,

Nous verrons qu'il existe des opérateurs spécialisés, comme EXISTS pour traiter particulièrement de cas d'imbrication. En particulier, les opérateurs ALL, SOME(ANY), MATCH et UNIQUE.

REMARQUE IMPORTANTE : chaque fois que vous voudrez mettre une sous requête dans un ordre SQL, assurez vous que la sous requête est comprise dans une paire de parentèses.

1.1 sous requêtes renvoyant une seule valeur

Nous allons d'abord étudier ce que nous pouvons faire en utilisant des sous requêtes ne renvoyant qu'une valeur unique. La plupart du temps, nous avons l'assurance de ne renvoyer qu'une valeur unique si nous utilisons une requête dont l'unique colonne est le résultat d'un calcul statistique (agrégation) comme les MAX, MIN, AVG, COUNT et SUM. C'est pourquoi on trouvera souvent ce genre d'expression dans les requêtes imbriquées des filtres WHERE et HAVING, mais aussi parfois dans la

clause SELECT.

1.1.1 dans la clause SELECT

On peut placer dans la clause SELECT, à la place de colonnes, des sous requêtes, voire même combiner par une opération, une colonne et une sous requête.

Notre hotelier voudrais connaître l'évolution du prix moyen de ses chambres par rapport à son tarif de référence au premier janvier 2000.Le prix moyen des chambres, pour n'importe quelle date d'application peut être obtenu par :exemple 1

Le tarif de référence qui nous intéresse est visible sur la 3eme ligne de la réponse. Nous pouvons l'obtenir en précisant la requête :exemple 2

Pour cacluler l'écart, il suffit de reporter ce nombre en le soustrayant du prix moyen de la requête de l'exemple 1 :exemple 3

Il ne suffit plus que de remplacer la valeur 306.50 par la requête qui l'a générée :exemple 4

NOTA : remarquez que nous n'avons plus besoin de nommer les colonnes de la sous requête. Observez aussi que la sous requête a été placée dans une paire de parenthèses.

1.1.2 dans les filtres WHERE et HAVING

C'est l'endroit le plus classique pour placer une sous-requête.

Premier exemple :

Intéressons de savoir quelles sont les chambres au 01/01/2000 qui ont un prix voisin à + ou - 10 F de la moyenne des prix au 1/1/200...Nous savons déjà que la moyenne des prix au 1/1/200 de toutes les chambres a déjà été calculée par la requête de l'exemple 2 et sa valeur est 306.50.Nous pouvons donc formuler ainsi la requête :exemple 5

Il semble qu'il faudrait envisager de placer deux fois la sous requête... Mais une petite astuce due aux propriétés des équations va nous permettre de résoudre ce problème. En effet , si nous retrons du prix de la chambre la valeur 306.50, la requête devient :exemple 6

Ce qui d'ailleurs est le strict énoncé du départ. Dès lors le remplacement de cette somme par la requête de l'exemple 2 est un jeu d'enfant :exemple 7

Deuxième exemple :

Notre hôtelier désire savoir quels sont les mois pour lesquels le taux d'occupation de son hôtel à dépassé les 2/3.Calculer le taux d'occupation (c'est à dire le nombre de chambre occupé chaque mois) est assez simple. Il suffit de compter le nombre d'occurences de la table ou sont stockées les informations des réservations (TJ_CHB_PLN_CLI).exemple 8

De même calculer un taux d'occupation de 66.67% consiste à faire le décompte des chambres et le multiplier par ce facteur, ce qui donne un taux d'occupation par nuit, que l'on peut ensuite ramener au mois par une côte mal taillée de 30 jours (référence comptable) :exemple 9

Dès lors nous vaons les éléments pour imbriquer nos requêtes...Le filtrage d'un agrégat (calcul statistique) ne peut se faire que par le filtre HAGING :exemple 10

La touche finale consistant à remplacer la valeur numérique 400.02 par la requête de l'exemple 9 en n'oubliant pas les parenthèses :exemple 11

Ce n'est pas plus compliqué que cela ! En fait, il faut comprendre que les mécanismes de base pour l'implémentation des sous requêtes sont toujours les mêmes : décomposition de la requêtes en éléments simple, création des requêtes élémentaires puis réassemblage du tout.

NOTA : on peut aussi placer une sous requête dans le filtre de jointure ON...

1.2 sous requêtes renvoyant une liste ( Opérateurs IN, ALL et ANY(SOME) )

Une liste de valeurs, c'est à dire une colonne, ne peut être utilisé comme critère de comparaison que par des opérateurs spécialisés.C'est le cas de l'opérateur IN, mais aussi des opérateurs ALL et ANY (ou SOME) que nous allons étudier dans un second temps..

1.2.1 dans le prédicat IN

L'opérateur IN est utilisable dans tous les prédicats, c'est pourquoi on le retrouve dans les filtres WHERE et HAVING. Pour alimenter une liste de valeur pour le prédicat IN, il suffit de palcer une requête ne renvoyant qu'une seule colonne.

Premier exemple :Monsieur BOUVIER vient réserver une chambre, et comme il s'y prend à l'avance, il aimerait prendre une chambre dans laquelle il n'a jamais dormit au cours de l'année 2001...Dans ce genre de cas, la négation se fait par différence : toutes les chmabres de l'hôtel MOINS les chambres dans lesquelles monsieur BOUVIER a déjà couché au cours de l'année 2001.

Trouver les chambres occupées par monsieur BOUVIER au cours de l'année 2001, n'est pas bien difficile :exemple 12

De même, trouver toutes les chambres de l'hôtel, relève de la plus élémentaire des requêtes :exemple 13

Dès lors l'utilisation du IN, et plus particulièrement ici du NOT IN, va permettre de faire le lien entre les deux requêtes :exemple 14

Notre client pourra coucher dans l'une des chambres 5 ou 8...

NOTA : beaucoup de requête utilisant le IN (comme le NOT IN) peuvent être simplifiées en utilisant des jointures. Le IN par des jointures internes, le NOT IN par des jointures externes associées à une clause HAVING COUNT(...) = 0. En général les performances seront meilleures en utilisant une jointure que dans le cas d'une sous requête avec [NOT] IN.

Ainsi notre exemple 14, peut se récrire :exemple 15

Second exemple :

Le gérant de l'hôtel voudrais savoir quels sont les mois (et les années) qui ont eu un nombre de nuitées égal aux nuitées enregistrées au cours de n'importe quel mois de janvier ?

Là encore, il convient de décortiquer la question pour en trouver les requêtes élémentaires...Pour connaître le nombre de nuitées des mois de janvier, il suffit de totaliser la colonne CHB_PLN_CLI_NB_PERS de la table TJ_CHB_PLN_CLI, comme suit :exemple 16

Étendre le comptage à tous les mois de toutes les années n'est pas plus difficile :exemple 17

Maintenant, pour ne plus filtrer ces comptages que sur les valeurs retournées par la première requête (404, 415, 418), il ne suffit plus que d'utiliser la clause HAVING comme suit :exemple 18

C'est à dire en reprenant le résultat de la requête 16 dans une liste IN.

Il ne nous reste plus qu'à remplacer le contenu de la dernière parenthèse située après le IN par la première requête à laquelle on ne laisse subsister que la colonne de comptage dans la clause select :exemple 19

C'est bien évidemment le résultat attendu !

1.2.2 dans les prédicats ALL, ANY(SOME)

L'opérateur IN que nous venons de voir, ne permet qu'une comparaison avec une stricte égalité.Il arrive que l'on soit confronté au cas où l'on souhaite que le critère de comparaison des deux ensmebles soit une inégalité. Par exemple pour trouver une valeur supérieure ou égale à toutes les valeurs d'un ensemble donné.Ceci est possible à l'aide des opérateurs ALL et ANY (l'opérateur SOME étant un autre nom de l'opérateur ANY).

Les opérateurs ANY (ou SOME) et ALL permettent de comparer des ensembles de valeurs de manière globale.Voici les conditions d'utilisation des opérateurs ALL et ANY (ou SOME) :

demande une comparaison à toutes les valeurs pour que le prédicat soit vrai

SOME qui est un synonyme) est vrai si, au moins une valeur de l'ensemble répond vrai à la

Le critère de comparaison devant être, de préférence, une inégalité (>, >=, <, <=, <>).

Notons par exemple que <> ALL correspondons au NOT IN.

Plongeons nous dans le quotidien de notre hôtelier, qui, cette fois ci, doit héberger une équipe de football ! De fait, ce dernier cherche à savoir quel est l'étage qui permet de coucher le maximum de personnes ? En fait, son but inavoué est de circonscrire les débordements qui pourrait intervenir lors de la troisième mi temps afin d'épargner le sommeil des autres clients, et par conséquent il envisage de loger tout ce petit monde au même étage avec à chaque bout du corridor un vigile...

La première idée qui vient à l'esprit pour tenter ce calcul est d'imbriquer deux calculs d'agrégation : obtenir le maximum de la somme des couchages par étage. Or une telle formulation :exemple 20

Impossible d'exécuter une fonction d'agrégation sur une

expression comportant un agrégat.

N'est pas possible en SQL.

En revanche il est facile de calculer le volume des couchages par étage :exemple 21

On voit d'ailleurs assez facilement quel est l'étage permettant le couchage le plus important.En fin de compte, la solution à ce problème est sous vos yeux... Quelle est la particularité du chiffre 23, par rapport à la colonne COUCHAGE ?D'en être le maximum, on est d'accord... mais encore, si nous devions comparer 23 avec tous les autres chiffres, quel critère pourrions nous retenir ?Ca y est, vous êtes sur la voie ! Oui, c'est bien cela 23 est supérieur ou égal à chacun des nombres 23, 22 et 9 !Dès lors l'opérateur ALL venant à notre rescousse, va nous tirer de cet embarras :exemple 22

Nous devons comprendre que, des deux ensembles distinct possédant les mêmes valeurs, nous cherchons a obtenir l'élément qui est au moins aussi fort que tous les éléments figurant dans l'autre ensemble. Autrement dit nous cherchons à savoir quel est l'étage dont le total de places de couchage est supérieur ou égal à tous les totaux de places de couchage de tous les étages !

Avec l'opérateur ANY, nous pouvons obtenir différents enseignements. D'abord nous pouvons obtenir le complément à la requête précédente en faisant :exemple 23

Il suffit d'utiliser le critère de comparaison strictement complémentaire. Cela correspond à demander tous les étages sauf celui de capacité maximale.

En outre si nous voulons tous les étages sauf celui dont le couchage est le plus petit, nous pouvons faire :exemple 24

1.3 sous requêtes comparant une ligne (prédicat MATCH)

Bien que rarement implémenté, car reposant sur la technique de la ligne valuée, le prédicat MATCH permet de comparer un ensemble de valeur de ligne à un ensemble de lignes retournée par une sous requête. La comparasion s'effectue globalement pour toutes les éléments de la ligne testée par rapport aux valeurs des colonnes

La syntaxe de l'opérateur MATCH est la suivante :

<ligne> MATCH [UNIQUE] [SIMPLE | PARTIAL | FULL] <requête>

<requête>

Les conditions de vérification d'un tel prédicat sont les suivantes :

<requête>

ou (équivalent)

SIMPLE <requête>

uête> correspond exactement à <ligne>

UNIQUE <requête>

ou (équivalent)

<requête>

equête> contient

une unique ligne correspondant exactement à <ligne>

PARTIAL <requête>

soit toutes les colonnes non NULL de <ligne> correspondent au moins à

une ligne de <requête> ayant exactement les valeurs requises

<requête>

soit toutes les colonnes non NULL de <ligne> correspondent à une unique

ligne de <requête> ayant exactement les valeurs requises

<requête>

soit aucune des colonnes de <ligne> ne possède de marqueur NULL et que

e <ligne> correspondent au moins à une ligne de <requête>

<requête>

possède de marqueur NULL et que

les valeurs de <ligne> correspondent à une unique ligne de <requête> ayant

Un petit exemple étant le bienvenu, cherchons à savoir quels sont les mois et les années pour lesquels on n'a pas eût un maximum de personne dans une chambre avec réservations préalables :exemple 25

Elle est équivalente à :exemple 26

parce qu'elle ne possède pas de valeurs NULL lors des calculs...

On voit bien que le prédicat MATCH propose une écriture plus élégante pour des requêtes complexes d'évaluation de lignes valuées avec différentes correspondances. Cependant il se fait encore rare dans les SGBDR !

1.4 sous requêtes renvoyant une table

N'importe quelle requête est capable de renvoyer une table... Car un résultat de requête est bien une table. C'est l'essence même de la fonction d'une requête.Or, où place t-on une table dans une requête ? Dans la clause FROM.Autrement dit il est possible de placer une sous requête dans la clause FROM de n'importe quelle requête à la place d'un nom de table !

Nous verrons aussi qu'il est possible de placer une sous requête dans des prédicats très particuliers de SQL 3, à l'aide des opérateurs FOR ALL, FOR ANY et FOR SOME...

1.4.1 Sous requête dans la clause FROM

Continuons notre recherche entamé précédemment. Notre hotelier voudrait bien éviter aux autres clients les rituelles batailles de polochons qui suivent les matches et pénalisent le sommeil du juste. Il cherche donc à savoir si au moins un étage de son hôtel permet de coucher les 24 personnes qui compose cette équipe (joueurs, remplaçants, entraîneurs...). Il voudrait donc savoir quel est le maximum de la somme des couchages des étages...

Nous avions vu que le calcul de la totalité des places de couchage par étage est assez aisé :exemple 27

De ce résultat nous pourrions extraire le maximum. Il sufirait de reprendre le résultat de cette requête, et de faire :exemple 28

En remplaçant le résultat par la requête de l'exemple 25, nous obtenons :exemple 29

Qui est le résultat attendu !C'est moins que la composition de l'équipe, mais je vous rassure, notre hôtelier, qui à du métier, a finalement eût l'idée de rajouter dans la plus grande chambre, un lit d'enfant...

ATTENTION : lorsque l'on place une sous requête en tant que table dans la clause FROM d'une requête, il faut pouvoir donner un nom à cette table ne serait-ce que parce qu'elle peut être jointes aux autres. Il convient donc de lui donner systématiquement un surnom.Ici le surnom choisit a été la lettre T, comme Table !

1.5 sous requêtes vide, non vide

Le principe est le suivant : si la sous requête renvoie un résultat quelconque, alors le prédicat vaut vrai. Si le sous requête ne renvoit aucune ligne, le prédicat vaut faux. SQL 2 à prévu deux prédicats spécillisés qui sont EXISTS et UNIQUE.

1.5.1 dans le prédicat EXISTS

Le prédicat EXISTS permet de tester l'existence ou l'absence de données dans la sous requête.Si la sous requête renvoie au moins une ligne, même remplie de marqueurs NULL, le prédicat est vrai. Dans le cas contraire le prédicat à valeur fausse, y compris si l'évaluation à la valeur UNKNOWN (dans la cas d'un comparaison avec un marqueur NULL).Le prédicat EXISTS peut être combinés avec l'opérateur de négation NOT.

Nous voulons obtenir le total du couchage de l'hotel, toutes chambres confondues, à condition qu'il y ait au moins une chambre dotée d'un couchage pour au moins 3 personnes :exemple 30

En fait l'utilisation d'un prédicat EXISTS n'a pas grand sens sans l'utilisation des sous requêtes corrélées.

IMPORTANT

le prédicat EXISTS est en général plus rapide que le prédicat IN;

le comportement du prédicat EXISTS face au retour de marqueurs NULL renvoyés par la sous requête diffère sensiblement d'un SGBDR à l'autre. En particulier DB2 et Oracle ne sont pas conforme à la norme SQL2;

le prédicat EXISTS n'a aucun intérêt sans une sous requête corrélée; il convient de toujours utiliser l'étoile comme unique contenu de la clause

SELECT de la sous requête car dans ce cas particulier, le moteur SQL choisit une constante la plus adaptée à un traitement performant.

1.5.2 dans le prédicat UNIQUE

UNIQUE est un raffinement du prédicat EXISTS. Cette extension du fonctionnement du prédicat EXISTS porte sur le doublonnage des lignes renvoyées. En effet, UNIQUE exige qu'il n'y ait aucun doublon dans les lignes renvoyées par la sous requête.En d'autres termes UNIQUE vaut faux si au moins deux lignes renvoyées par la sous requête comporte les mêmes données.Nous voulons obtenir le total du couchage de l'hotel, toutes chambres confondues, à condition qu'il n'y ait qu'une seule chambre dotée d'un couchage pour exactement 5 personnes :exemple 31

Il existe deux chambres (6 et 17, d'ID 6 et 16) dotées de 5 places de couchage. La sous requête renvoie donc deux lignes possédant des données identiques. UNIQUE vaut alors faux, aucun résultat n'est donc renvoyé.En fait l'utilisation d'un prédicat EXISTS n'a pas grand sens sans l'utilisation des sous requêtes corrélées.IMPORTANT

Le prédicat UNIQUE est en général beaucoup plus rapide que certaines constructions telles que le prédicat NOT EXIST associé au SELECT DISTINCT oue encore une sous requête avec un filtre HAVING pour comptre les lignes dédoublées..

Le comportement du prédicat UNIQUE face au retour de marqueurs NULL renvoyés par la sous requête peut diffèrer sensiblement d'un SGBDR à l'autre.

Le prédicat UNIQUE n'a aucun intérêt sans une sous requête corrélée. Contrairement à EXISTS, il convient de toujours spécifier les colonnes visées

dans la clause SELECT.

NOTA : ce prédicat est hélas rarement implémenté dans les SGBDR... Dommage !

2 Les sous requêtes corrélées Vous trouverez des compléments d'information sur le sujet aux pages 154 à 180 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Nous allons maintenant rajouter une couche à la puissance des sous requêtes en étudiant comment on peut corréler une sous requête à la requête au sein de laquelle elle est imbriquée.


NOTA : On trouve parfois les mots sous requêtes "imbriquées", sous requêtes "liées" pour désigner la techniques des sous requêtes corrélées.En fait une sous requête corrélée est une sous requête qui s'exécute pour chaque ligne de la requête principale et non une fois pour toute. Pour arriver à un tel résultat, il suffit de faire varier une condition (en général un prédicat) en rappelant dans la sous requête la valeur d'une colonne de la requête principale.Il est plus facile de comprendre cette notion à l'aide d'une comparaison à un langage procédural, mais aussi en visualisant graphiquement une telle imbrication.Le principe procédural est simple. Les sous requêtes corrélées correspondent en fait à des boucles imbriquées. Voici un exemple de procédure permettant de lire un fichier et de scruter chaque ligne du fichier à la recherche d'un mot précis :

screen.prompt("Mot " + LeMot + " trouvé à la ligne : " + string(NumLigne))

Nous voyons bien que les deux boucles sont imbriquées. C'est à dire qu'il faut recommencer à rechercher l'occurrence du mot dans chacune des lignes du fichier que l'on "balaye". C'est exactement comme cela que fonctionnent les requêtes imbriquées. La requête principale donne à la sous requête de nouvelles conditions d'exécution à chaque fois que la requête principale trouve une ligne correspondant à ses propres filtres.

De manière graphique, le principe d'une sous requête est le suivant :

Cherchons donc à trouver les clients qui ont un prénom en commun. Autrement dit pour qu'un client soit sélectionné, il faut qu'un autre client porte le même prénom. Nous apellerons cela l'homoprenymie !Une première idée qui vient immédiatement à l'esprit consiste à faire :exemple 32

La réponse est le contenu complet de la table des clients... Tous les clients auraient-ils un homoprényme ? Non, bien entendu, mais la faute que nous avons commise est simple : un client porte bien évidemment le même prénom que.. lui même !C'est pourquoi nous devons retirer dans la sous requête la référence au client que l'on scrute danas la requête principale.La requête propre est donc celle-ci :exemple 33

André

André

Soit 53 occurences.Ici, la corrélation entre la requête principale et la sous requête se fait dans la clause WHERE et porte sur le fait que l'identifiant des clients scrutés dans la sous requête

ne doit pas être le même que dans la requête principale : .

Notons que pour obtenir cette corrélation, il faut donner des surnoms à nos tables.Plus élégament, nous pouvons écrire cette requête à l'aide d'une clause EXISTS qui sera notablement plus performante :exemple 34

André

André

Ce qui, bien évidemment donne le même résultat.Si maintenant nous exigeons qu'il n'y ait qu'un seul homoprényme de nos clients, alors le prédicat UNIQUE vient à notre secours :exemple 35

André

André

Notons qu'ils ne sont plus que 20 occurences, et non plus 53, soit 10 paires de clients homoprénymes. En fait un nombre pair était bien évidemment attendu !REMARQUE :Nous n'avons présenté ici que quelques brefs exemples afin de faire toucher du doigt le concept de la corrélation des requêtes.Bien entendu les sous requêtes corrélées peuvent l'être dans toutes les clauses de l'ordre SELECT.Nous pourrions passer en revue les principaux opérateurs combinés aux principales clauses capable de recevoir une sous requête, mais cela deviendrait vite rébarbatif. C'est pourquoi je vous invite à lire les exemples que l'on trouve dans les différents papiers de ce site, tel que celui consacré à la division relationelle, ou encore de lire la nombreuse littérature sur le sujet et en particulier le chapitre 8 de mon livre "SQL, La référence" qui donne plus de 120 exemples dont une bonne partie sont des requêtes corrélées..

3 Sous requêtes ou jointures ?Disons le tout de suite, il est faux de croire que toutes les sous requêtes peuvent trouver leur équivalent sous forme de jointures, même si certains SGBDR par faute d'avoir implémenté les sous requêtes ont codés quelques "trucs" afin de paliers leurs défauts.Un premier exemple d'une simplicité extrême va vous donner la mesure de la complexité de la chose.Soit les tables suivantes, et leur jeu de données associés :

http://sqlpro.developpez.com/DivRel/SQL_DIVR.htm



Quel est donc l'équivalent absolu, sans sous requête et exprimé à l'aide uniquement de jointure, de la requête :exemple 36

Essayons quelques formulations...Une jointure interne simple :exemple 37

PERDU : il y a une valeur 2 en trop !Rajoutons le dédoublonnage :exemple 38

PERDU : il y a une valeur 1 en moins !Voici quelques exemples de sous requêtes ne possédant aucun équivalent sous forme de jointure :exemple 39, 40 et 41

Je laisse à votre sagacité le soin de rechercher comment exprimer ces requêtes sans l'imbrication de sous requêtes. Je remercie au passage Peter GULUTZAN de ces exemples tirés de son livre "SQL-99 complete really" publié chez R&D books.

Le grand perdant de cette affaire est MySQL incapable de la moindre sous requête et donc inapte à de nombreuse extraction de base du SQL !

Résumé [partie en construction]Voici les différences entre les moteurs des bases de données

Sous requêtes

Paradox Access Sybase SQL Server OracleMySQL InterBase PostGreSQL

dans le SELECT

OUI OUI NON

dans le FROM

NON OUI NON

dans les filtres WHERE et HAVING

OUI OUI

NON

opérateur IN

OUI OUI NON

opérateurs ALL et ANY (ou SOME)

OUI OUI NON

opérateur MATCH

NON NON NON

opérateur EXISTS

OUI OUI NON

opérateur UNIQUE

NON NON NON

corrélées dans le SELECT

OUI OUI NON

corrélées dans le FROM

NON OUI NON

corrélées dans les filtres WHERE et HAVING

OUI OUI NON

corrélées dans l'opérateur IN

OUI OUI NON

corrélées dans les opérateurs ALL / ANY

OUI OUI NON

(SOME)

corrélées dans l'opérateur EXISTS

OUI OUI NON

corrélées dans l'opérateur UNIQUE

NON NON NON

corrélées dans l'opérateur MATCH

NON NON NON

Conclusion :

6eme partie - les mises à jour de données : INSERT, UPDATE, DELETE

Après avoir vu en long, en large et en travers le SELECT, nous allons maintenant nous consacrer aux ordres SQL permettant de mettre à jour les données. Ces ordres sont au nombre de trois : INSERT pour l'insertion de nouvelle(s) ligne(s) dans une table, DELETE pour la suppression et enfin UPDATE qui permet de modifier tout ou partie des valeurs des colonnes.




Il existe une différence fondamentale entre l'ordre de manipulation des données qu'est le SELECT et les différents ordres de mise à jour de données :

parce qu'en dehors de problèmes de syntaxe ou de typage, le select est une opération dont l'aboutissement est en théorie toujours garantie (sauf à indiquer des noms d'objets inexsitants) alors que les opérations de mise à jour des données sont susceptibles d'être rejetées, notamment si elle violent des contraintes,

parce qu'autant il est possible d'effectuer des extractions de données via un ordre SELECT sur plusieurs tables, autant il est impossible d'insérer, de modifier ou de supprimer dans plusieurs tables simultanément.

parce qu'une requête de mise à jour (INSERT, UPDATE ou SELECT) est une transaction en elle même et que tout doit être réalisé, sinon rien ne se passe (en particulier si une seule donnée viole une contrainte aucune insertion, mise à jour ou supression n'est effective dans le cas d'un ordre concernant la mise à jour de plusieurs lignes simultanément).




parce qu'à moins de gérer une transaction et notamment à l'aide du mot clef ROLLBACK, il est impossible de revenir sur un ordre de mise à jour si l'on s'est trompé. Dans le cas d'un SELECT ceci n'a pas d'importance puisque les données ne sont pas modifiées.

1 Insérer à l'aide d'INSERT Vous trouverez des compléments d'information sur le sujet aux pages 235 à 239 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur. La syntaxe de base de l'ordre SQL d'insertion de données dans une table est la suivante :

liste_des_colonnes_visées

| requête_select |

NOTA :

la liste des colonnes visées peut être ommise à condition que l'ordre d'insertion concerne toutes les colonnes de la table.

la liste des valeurs peut être remplacée par un constructeur de lignes valuées pour une insertion de plusieurs lignes en un seul ordre, mais rare sont les SGBDR à l'accpeter (Oracle est l'un des rares SGBDR à accepter cette syntaxe).

ATTENTION : l'ordre des valeurs de la liste doit être le même que l'ordre des colonnes visées et cela, même si la liste des colonnes visées est ommise.REMARQUE :Du fait de sa syntaxe, l'ordre INSERT se décompose en trois ordres assez différents :

Insertion simple explicite; Insertion multiple explicite (à l'aide du constructeur de lignes valuées; Insertion à base de sous requête SELECT; Insertion des valeurs par defaut

Nous allons maintenant détailler ces différentes déclinaisons de l'ordre INSERT...

1.1 Insertion simple explicite Cette syntaxe porte sur l'insertion d'une ligne unique au sein de la table. Il s'agit de préciser les valeurs à insérer explicitement.exemple 1

Cet exemple propose d'insérer dans la table T_MODE_PAIEMENT une ligne comportant les valeurs "CB" et "Carte bancaire" dans les colonnes respectives PMT_CODE et PMT_LIBELLE.


Compte tenu que cette table ne possède que deux colonnes, on aurait pu omettre de préciser les colonnes. Dans ce cas, la requête devient :exemple 2

Qui, bien évidemment donne le même résultat.NOTA : lorsqu'une valeur n'est pas connue, il est possible de préciser le mot clef NULL (marqueur) qui laisse la colonne vide.exemple 3

Qui insère un mode de paiement de code "X" et dont le libellé n'est pas renseigné.

1.2 Insertion multiple explicite à l'aide du constructeur de lignes valuées Cette syntaxe porte sur l'insertion de multiples ligne au sein de la table cible. Il faut préciser les lignes de valeurs à insérer explicitement.exemple 4

Cet exemple propose d'insérer dans la table T_TITRE trois lignes de valeurs dans les colonnes TIT_CODE et TIT_LIBELLE.De même que dans notre précédent exemple, cette table ne possèdant que deux colonnes, on aurait pu omettre de préciser les colonnes. Dans ce cas, la requête devient :exemple 5

et donne le même résultat que l'exemple 4Le constructeur de lignes valuées consiste à donner une liste de ligne en argument.NOTA : le constructeur de lignes valuées est rarement implémenté dans les SGBDR. Oracle est l'un de seuls à accepter une telle syntaxe.

1.3 Insertion partiellement explicite avec le mot clef DEFAULT Si la défintition de la table possède une ou plusieurs valeurs par défaut, alors il est possible de les y insérer en utilisant le mot clef DEFAUT en lieu et place de la valeur.Supposons que nous créons une table permettant de "pister" les connexions à la base de données, de la manière suivante :

Alors nous pouvons insérer l'heure et la date par defaut sans en préciser la valeur, à l'aide de la requête :exemple 6

Qui insère automatiquement la valeur par défaut dans la colonne CNX_DATE_HEURE.Bien entendu on peut omettre la liste des noms de colonnes puisque, à nouveau, toutes les colonnes sont concernées par l'ordre d'insertion :exemple 7

Qui donne le même résultat si l'ordre est exécuté au même moment !

visées, les autres dotées

d'une valeur par défaut seront automatiquement alimentées :

exemple 8

Qui donne encore le même résultat si l'ordre est exécuté au même moment !

1.4 Insertion totalement implicite avec l'expression DEFAULT VALUES Si chacune des colonnes de la définition de la table possède des valeurs par défaut, on peut demander l'insertion de toutes les valeurs par défaut en utilisant l'expression clef DEFAULT VALUES. Dans ce cas il ne faut pas préciser les noms des colonnes :exemple 9

Qui insérera l'utilisateur 'Administrateur' avec la date et l'heure courante.

1.5 Insertion multiple à base de sous requête SELECT On peut insérer une ou plusieurs lignes dans une table en utilisant une sous requête de type SELECT. Dans ce cas les colonnes retournées par l'ordre SELECT doivent avoir les contraintes suivantes :

être en nombre didentique aux colonnes précisées dans la liste ou en l'absence de précision de cette liste le même nombre de colonnes que la table

avoir le même ordre que l'ordre des noms de colonnes de la liste ou bien le même ordre que les colonnes de la table si l'on ommet cette liste

avoir des types correspondant répondre à toutes les contraintes et dans le cas ou au moins une seule valeur

viole une contrainte aucune ligne n'est insérée

Nous allons décrire différents cas et le comportement du SGBDR correspondant. Pour ce faire, je vous propose de créer de toutes pièces une nouvelle table de notre base de données exemple, la table des prospects et d'y insérer explicitement quelques données :

La table T_PROSPECT contient donc :

Un premier exemple, que nous allons décortiquer, va nous permettre de comprendre comment fonction l'insertion avec une sous-requête SELECT :exemple 10

Décomposons le travail du SGBDR...Première étape, exécution de la sous requête SELECT :

Seconde étape, Il faut maintenant insérer ces données dans les colonnes CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM correspondantes de la table T_CLIENT. Celle-ci contient :

L'exécution de la requête de l'exemple 9 donne :

Niveau 14, État 1, Ligne 1

Violation de la contrainte PRIMARY KEY 'PK_T_CLIENT'. Impossible d'insérer une clé en double dans

L'instruction a été arrêtée.

Et oui, car on ne peut insérer des clefs en double, or nous tentons d'insérer un deuxième client portant la clef 1, un autre portant le clef 2, etc...En l'occurence aucune ligne n'est donc insérée car toute requête de mise à jour est une transaction et fonctionne en "tout ou rien", c'est à direq qu'aucune ligne n'est insérée si au moins une contrainte n'est pas vérifié. En l'occurence la violation de la contrainte de clef primaire étant ici évidente rien n'est inséré dans la table T_CLIENT.Nous savons à la lecture des données de la table des clients, que la clef la plus haute possède la valeur 100. Il est donc possible d'insérer des clients dont la clef est supérieure à cette valeur. D'ou l'idée de rajouter cette valeur à la valeur de clef de la table T_PROSPECT récupérée à l'aide de la requête SELECT, par une simple addition :exemple 11

Et là nos trois prospects ont été insérés :

Mais comme nous sommes déjà en train de traiter une sous requête SELECT, il semble facile de remplacer la valeur 100 par une autre sous requête renvoyant la valeur maximale de la clef de la table T_CLIENT afin de l'ajouter à la valeur de la clef de la table T_PROSPECT :exemple 12

Ce qui s'exécute parfaitement et donne :

L'explication est simple : la sous requête (SELECT MAX...) n'est excutée qu'une seule fois puisque qu'elle n'est pas corrélée avec la table cible de l'insertion.

1.6 Insertion multiple et conditionnelle à base de sous requête SELECT

corrélée Il est possible de corréler la sous requête d'insertion avec la requête cible. Dans ce cas il faut préciser une seconde fois la table cible dans la clause FROM de la sous requête SELECT.Pour reprendre notre exemple, nous voudrions insérer en tant que client les prospects dont le couple de valeurs nom/prenom est différent. Dans ce cas, les prospects 1 et 2 ne doivent pas être insérés, mais le prospect 3 (Dubois) doit, lui, être inséré.Décomposons notre travail...Première phase, trouvons la requête qui va récupérer les client qui sont des prospects :

Qui donne :

NOTA : Si vous avez inséré les données des exemples précédents, veuillez procéder

à la suppression des prospects insérés à l'aide de la requête

Pour trouver l'inverse, c'est à dire les prospects qui ne sont pas des clients, il suffit de réaliser une exclusion. On peut alors utiliser l'ordre EXCEPT s'il est implémenté :

ou encore une construction équivalente, par exemple avec des jointures externes :

Qui, toutes deux donnent :

Dès lors, l'insertion devient simple si l'on oublie pas de rajouter à la clef la valeur maximale de la clef de T_CLIENT...exemple 13

1.7 Insertion en auto référence L'insertion en auto référence consiste à ajouter à une table une ou plusieurs nouvelles lignes calculées d'après les lignes existantes de la table cible.Par exemple nous voulons insérer dans la tables des tarifs une nouvelle ligne avec comme date d'application du tarif, le premier janvier 2002, le même taux de taxe que le tarif précédent et un prix de petit déjeuner de 10% de plus que le précédent tarif.Obtenir le précédent tarif consiste à trouver la ligne de la table dont la valeur de la date est maximale. Cela peut s'effectuer à l'aide de la requête suivante :

qui donne

Dès lors on peut utiliser cet ordre SELECT en le modifiant un peu de manière à lui faire insérer la nouvelle ligne tarifaire :exemple 14

REMARQUE :Voici les principaux cas pour lesquels un ordre d'insertion ne peut aboutir :

violation de clef (index primaire) violation de contrainte d'index secondaire unique

violation de contrainte de données (colonne not null) violation d'intégrité référentielle

violation de contrainte de contrôle de validité (min, max, étendue, domaine...)

2 Suppression à l'aide de DELETE Vous trouverez des compléments d'information sur le sujet aux pages 243 à 244 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.


La syntaxe de base de l'ordre SQL de suppression de données dans une table est la suivante :

Le seul cas pour lequel cet ordre peut ne pas aboutir est lorsque la suppression viole la contrainte d'intégrité référentielle. Il est en effet absurde de vouloir supprimer un client si les factures relatives à ce client n'ont pas été préalablement supprimées.NOTA : Dans certains cas, il se peut que la suppression d'une ligne entraîne la suppressions d'autres lignes dans d'autres tables lorsqu'existe des intégritées référentielles de suppression en cascade.

2.1 suppression de toutes les lignes d'une table C'est la forme la plus simple de l'ordre DELETE puisqu'il suffit d'omettre la clause WHERE :exemple 15

Supprime tous les prospects.

2.2 suppression conditionnelle Il suffit de rajouter la clause WHERE dotée d'un prédicatexemple 16

Supprime tous les prospects dont le nom se termine par la lettre 'd'.

2.3 suppression avec sous requête conditionnelle Il est possible d'utiliser une sous requête conditionnelle dans la clause WHERE d'un ordre DELETE.Supprimons les prospects dont le couple de valeurs nom/prénom se trouve dans la table des clients. Procédons pour cela par étape.Pour obtenir la liste des prospects qui figurent en tant que client, nous pouvons faire la requête suivante :

Dès lors il suffit de supprimer les prospects dont l'identifiant est récupéré par la sous requête :exemple 17

On peut procéder aussi à l'aide du constructeur de lignes valuées, si votre SGBDR le supporte, ce qui simplifie l'écriture de la requête :exemple 18

Bien entendu la construction de sous requêtes dans un ordre DELETE peut être compliqué à souhait afin d'effectuer la suppression conditionnelle désirée...

3 Modification à l'aide d'UPDATE Vous trouverez des compléments d'information sur le sujet aux pages 239 à 242 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

La syntaxe de base de l'ordre SQL de modification de données dans une table est la suivante :

3.1 Mise à jour d'une colonne unique sans condition C'est la forme la plus simple de l'ordre UPDATE. Nous voulons par exemple fixer à 55 F les tarifs de nos petits déjeuners dans la table T_TARIF :exemple 19

3.2 Mise à jour d'une colonne unique avec reprise de valeur (auto

référence) On peut aussi reprendre la valeur de la colonne (ou d'une autre colonne de la table cible). Par exemple nous pouvons demander une augmentation de 15% des tarifs des petits déjeuners :exemple 20


3.3 Mise à jour d'une colonne unique avec filtrage On peut ajouter une clause de filtrage WHERE dans une requête de mise à jour. Par exemple nous pouvons décider de n'augementer de 15% que les tarifs des petits déjeuners des périodes postérieures à 1999exemple 21

3.4 Mise à jour de plusieurs colonnes simultanément Pour mettre à jour simultanément plusieurs colonnes, il suffit de répéter aitant de fois que nécessaire le contenu de la clause SET, à raison d'un couple colonne/valeur par colonne visées par la mise à jour.exemple 22

NOTA : dans ce cas, la nullité de l'exécution de modification d'une valeur dans une colonne possédant le marqueur NULL, n'entraîne pas la nullité de l'exécution des mises à jour des autres colonnes, chaque modification de colonne étant évaluées séparément.

3.5 Mise à jour avec sous requête Comme dans les ordres INSERT et DELETE, il est possible d'utiliser une sous requête dans la clause WHERE de l'ordre UPDATE afin de filtrer de manière plus complète.Par exemple, afin d'éviter de confondre des prospects qui ont le même nom et prenom que certains client, on désire ajouter le mot "bis" aux prospects homonymes :exemple 23

3.6 Mise à jour de valeurs particulières (défaut et marqueur NULL) Il est possible de mettre à jour une colonne à sa valeur par défaut si elle possède une telle spécificité élaborée dans la création de la table :En reprenant la définition des tables de connexion vu au paragraphe 1.3, donnons à la colonne CNX_USER sa valeur par défaut pour toutes les lignes de la table :exemple 24

Il est aussi possible de supprimer le contenu d'une colonne (ou de plusieurs) en y plaçant le marqueur NULL :exemple 25

Cette requête vide la colonne CLI_ENSEIGNE de la table des clients en y plaçantNOTA : c'est le seul cas ou l'on trouvera le mot clef NULL associé au signe égal, car dans ce cas le signe egal est un opérateur d'affectation. De manière syntaxique il aurait mieux valut une construction du genre :

Mais ceci est une autre histoire !

ATTENTION :Une mise à jour peut échouer si elle viole les contraintes.Voici les principaux cas pour lesquels un ordre de modification ne peut aboutir :

violation de clef (index primaire) violation de contrainte d'index secondaire unique

violation de contrainte de données (colonne not null) violation d'intégrité référentielle

violation de contrainte de contrôle de validité (min, max, étendue, domaine...)

4 Valeurs ambigüesIl arrive lors des insertions et des mise à jour que la valeur passée en argument soit ambigüe car son format ou son type ne peut être exprimé que par l'intermédiaire du jeu de caractère ordinaire. Comment donc savoir si la chaîne "AF12" est une chaîne de caractères ou un code hexadécimal représentant 4 octets soit 2 caractères ?Pour lever cette ambigüité, on doit utiliser une lettre de préfixage :.

Hexadécimal

Exemple 26 :


Mise à jour des données


INSERT explicite

OUI OUI OUI OUI OUI OUI OUI OUI

INSERT explicite

multi ligneNON NON NON NON OUI NON NON NON

INSERT avec

DEFAULT

OUI (1) OUI

INSERT avec

DEFAULT VALUES

NON OUI

INSERT avec sous

requête

OUI OUI OUI OUI OUI NON OUI OUI

INSERT avec sous

requête corrélée

OUI OUI OUI OUI

NON OUI OUI

INSERT en auto

référence

OUI OUI

DELETE (vidage de

table)


DELETE avec filtre WHERE


DELETE avec sous

requête

OUI OUI OUI OUI OUI NON OUI OUI

UPDATE colonne unique


UPDATE en auto

référenceOUI OUI OUI OUI OUI OUI OUI OUI

UPDATE multi

colonneOUI OUI OUI OUI OUI OUI OUI OUI

UPDATE avec sous

requêteOUI OUI OUI OUI OUI NON OUI OUI

UPDATE avec NULL / DEFAULT

OUI / NON OUI

lettre de préfixage

NON OUI (2)

(1) en omettant de préciser la colonne

Conclusion :

7eme partie - Création et manipulation des schémas : CREATE, ALTER, DROP

Nous allons maintenant nous intéresser à ce que la norme SQL 2 apelle la création des schémas, c'est à dire les objets qui composent une base de données. Tout le monde connait la notion de Table, mais connaissez vous les "DOMAIN", les "ASSERTION", les "COLLATION" et les "TRANSLATION" ???Si ces noms ne vous sont pas familiés, nul doute qu'ils vous deviendrons connus à la lecture de ce papier !Au fait, cette partie du SQL s'apelle le DDL pour Data Definition Language. Et saviez vous qu'aucun de ces ordre ne peut être transactionné ?

dernière mise à jour : 16 octobre 2003 à 08:49

nota : cet article est en deux pages du fait de sa longueur !Pour la suite de cet article ici, appuyez sur le bouton ci après



AVANT PROPOS




Sans doute allez-vous immédiatement vous intéresser à la syntace du CREATE TABLE. C'est pour cela, sans doute, que vous êtes tombé sur cette page... Je vous plains car vous allez tout droit créer des bases de données boiteuses et rapidement impossible à maintenir. Si j'avais un conseil à vous donner ce serait le suivant : lisez les paragraphes 2 à 4 avant même de vous plonger, tête baissée dans la création des tables. Il y a tant de choses qui vous faciliteront la vie que je gage que vous m'en serez éternellement reconnaissant...

1 Règles de nommage

La norme SQL 2 impose un certain nombre de règles concernant les noms des objets d'une base de données.

Un nom d'objet (table, colonne, contrainte, vue...) doit avoir les caractéristiques suivantes :

ne pas dépasser 128 caractères

commencer par une lettre

comprendre uniquement les caractères suivants [ 'A' .. 'Z'] U ['a' .. 'z'] U [ '0' ..

'1'] U [ '_' ]

un nom d'objet ne peut pas être un mot réservé de SQL sauf à être utilisé avec des guillemets

être insensible à la casse

Voici quelques identifiants normatifs et non... Exemple 1

ATTENTION : un nom d'objet doit être unique au sein de l'objet qui le contient. Par exemple un nom de table ou de vue doit être unique au sein de la base, un nom de colonne doit être unique au sein de la table ou la vue, etc...

2 Au début était le néant...C'est toujours la même histoire... Comment créer une base de données ? En se connectant au serveur de bases de données ! Mais comment se connecter au serveur de base de données ? En créant une base de données dotée au moins d'une connexion et d'un utilisateur avec les privilèges adéquats... Bref, nous entrons de plein front dans le célèbre problème de l'oeuf et de la poule...Heureusement la plupart du temps, les fournisseurs de SGBDR pourvoient leur engin avec un utilisateur, son mot de passe et un certain nombre de bases de données

précréés afin de nous faciliter la vie.Par exemple sur InterBase le nom d'utilisateur et son mot de passe sont "SYSDBA/masterkey". Sur MS SQL Server, le nom d'utilisateur est "sa" sans mot de passe (et je vous conseille vivement d'en mettre un immédiatement). Sur Oracle, c'est "System/manager" ou bien "Sys/change_on_install". Sur DB2 c'est "db2admin". Sur Informix un compte "informix" est créé dans le goupe "informix-admin". Sous mySQL c'est "mysqladm" qu'il faut utiliser... Ingres est plus restrictif puisqu'il impose à l'utilisateur de donner expressément un nom lors de l'installation. Pour Sybase et ses deux bases, des différences : ASE veut "sa" sans mot de passe, tandis que ASA impose "DBA/SQL" !En fait tout ces "comptes" sont des connexions au serveur et la plupart du temps correspondent à la fois à la notion de connexion et d'utilisateur. Mais La norme SQL fait une différence entre le concept de connexion et celui d'utilisateur.

2.1 La connexion Pour SQL 2, la connexion à un SGBDR prend la syntaxe suivante :

Par exemple l'ordreExemple 2

Se connecte au serveur de base de données défini par défaut. La plupart du temps un serveur de bases de données est installé sur une machine dédiée, ce qui fait qu'il n'y a pas dambiguîté. En revanche, si plusieurs serveurs sont installés sur la même machine, il faut nommer le serveur.Exemple 3

Se connecte au serveur identifié "MON_SERVEUR".Il est même possible de renommer ce serveur :Exemple 4

Qui se connecte à "MON_SERVEUR", mais le surnomme SRV1. Dès lors ce nouveau nom pourra être utilisé dans divers ordres SQL en lieu et place du nom authentique du serveur.Enfin, il est possible de préciser le nom de l'utilisateur (qui doit exister dans le SGBDR) qui sera associé à la connexion. Sans cette porécision, le SGBDR emprunte le nom par défaut implanté par le constructeur.Exemple 5

Se connecte au serveur "MON_SERVEUR" en empruntant l'identité FRED_BROUARD comme utilisateur.Vous l'aurez compris, à une connexion est toujours associée un nom d'utilisateur. Petite précision, un utilisateur SQL est un objet du serveur (ou de la base de données dans certains cas) et se définit aussi par un ordre SQL...NOTA : la plupart du temps, ce mécanisme est masqué par une interface graphique, oue encore par l'imbrication du serveur de bases de données et de son OS. Par

exemple pour SQL Server de Microsoft, cet ordre de connexion s'effectue derrière la boîte de dialogue suivante :

Pour InterBase, c'est la boîte de dialogue suivante qui fait office :

Mais si vous désirez passer des ordre SQL InterBase en vous connectant à une base spécifique et ceci depuis une commande du shell, vous pouvez lancer l'ordre suivant :Exemple 6 (InterBase)

La norme SQL propose en outre la possibilité de basculer d'une connexion à l'autre (à condition que l'autre existe et soit dormante à l'aide de l'ordre :

Ou nom_session représente une connexion déjà établie et dormante (c'est à dire une connexion ouverte mais qui n'est pas activée par le passage d'ordre SQL..)Bien entendu il est possible de fermer une connexion en utilisant l'ordre SQL DISCONNECT :

La lecture de cette syntaxe laisse bien peu d'interprétation et je laisse donc à votre sagacité le soin de comprendre à quoi peuvent bien faire penser de tels mots clefs !

2.2 La session Là nous entrons déjà dans une notion plus complète et donc, forcément plus complexe...Une session au sens de la norme SQL est une connexion activé et possède certains attributs particuliers. Les attributs d'une session sont :

un identifiant d'autorisation (AUTHORIZATION) un nom de catalogue (CATALOG) un nom de schéma (SCHEMA) un fuseau horaire (TIME ZONE) un jeu de caractères (CHARACTER SET)

L'identifiant d'autorisation doit impérativement être choisit parmi les mots clefs suivants USER, CURRENT_USER, SESSION_USER et SYSTEM_USER ou bien en donnant un nom d'utilisateur spécifique. Un catalogue (CATALOG est le mot clef SQL) est une collection de bases de données et peut soit prendre la forme d'un serveur, d'un groupe de serveurs ou bien de "répertoires" de bases de données. Un schéma n'est autre qu'une base de données, c'est à dire son nom. La norme SQL les apellent SCHEMA parce qu'une base de données doit être décrite pour être utilisée, et son architecture (tables, colonnes, vues...) correspond à un modèle de données physique. Un fuseau horaire (TIME ZONE)n'est autre que l'indication du décalage de l'heure locale par rapport au temps universel (ou UTC Unified Time Coordination). C'est ainsi qu'il permet de gérer les décalages horaires de toutes les zones locales de la planète ce qui est bien partique lorsque l'on veut développer une application internationale, notamment pour les sites web ! Enfin, le jeu de caractère (CHARACTER SET) permet de définir quel sous ensemble de symboles est utilisé pour les 256 combinaisons de deux octets qui correspondent à la frappe des caractères. N'oublions pas que même en europe les jeux de caractères ne sont pas les mêmes d'une nation à l'autre. Par exemple l'alphabet finois ou le tchèque sont dotés de curieux petits accents en forme de cercle ou de croissant, tandis que dans le cyrillique c'est la forme même des lettres qui change... et tout de même, ces gens là doivent pouvoir s'exprimer ! (notons que le français possède un certains nombre de caractères particulier comme le "c" avec ça cédille ou l'"e" dans l'"o", présent par exemple dans le mot coeur).

2.3 Catalogues et shémas Comme nous venons de le définir, le terme CATALOG permet de définir une collection de bases de données. Il est très différement implémenté dans les divers SGBDR que propose les éditeurs. Pour SQL Server la notion de CATALOG se confond avec celle de base de données, tandis que la notion de schema se confond avec celle de propriétaire. Par exemple en demandant à la machine de nous fournir la liste des bases de données avec la requête suivante, voici ce que le serveur nous

propose :Exemple 7 (SQL Server)

3 Créer une nouvelle base de données Vous trouverez des compléments d'information sur le sujet aux pages 190 à 192 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Évidemment ce qui vous intéresse le plus c'est d'abord de créer une base de données. C'est à dire un SCHEMA !La norme SQL propose l'ordre de création d'une base de données (pardon, d'un schéma) suivant :

jeu_de_caractè

[ liste_des_objets_du_schéma ]

La liste des objets du schéma n'étant autre que la création des éléments de la base.Je ne m'étendrais pas sur cette syntaxe ni sur l'ordre SQL 2 de suppression des schemas :

car ces ordres sont très rarement implémentés tels quels dans les SGBDR. En revanche on trouve la plupart du temps un pseudo ordre SQL "CREATE DATABASE" fort pratique, mais qui n'existe nulle part dans la norme !

Mais si j'ai tenu à vous montrer ceci, c'est que, par défaut, une connexion ou un serveur possède des attributs. Les méconnaitre et notamment en méconnaitre le paramétrage peut vous causer les pires ennuis. C'est pourquoi je vous propose de réfléchir sur trois questions fondamentales :


Quel est l'ordre de tri de mes données littérales ?

Comment s'effectue la comparaison entre colonnes contenant des chaînes de caractères notamment si ces dernières sont de type différent (VARCHAR et CHAR en particulier) ?

Quelle est à tout moment l'heure et la date procurée par le SGBDR et cette heure est-elle modifiée en fonction des saisons ???

Si vous n'avez pas réflécis à ces problèmes fondamentaux, vous risquer quelques problèmes difficilement surmontables...Par exemple :

comment faire correspondre l'ordre de mes données entre un tableau trié par une routine en C++, Java ou Delphi et le serveur de base de données ?

comment faire en sorte de distinguer les deux références suivantes "GFY-12-aj" et "gfy-12-AJ" dans un SELECT si le serveur a été paramétré pour être rendu insensible à la casse ??

Comment compter exactement la durée d'une intervention technique en heure si entre la déclaration de la panne et la résolution du problème est intervenu un changement d'horaire du fait de l'heure d'éte ???

Je ne m'étendrais donc pas plus sur le sujet car il est assez spécifique aux différents SGBDR de chaque éditeur. Pensez simplement que l'installation par défaut du serveur prôné avec un gadget qui le rend insensible à la casse est une abhération. En effet, autant il est facile de formatter des données pour que la comparaison et même la saisie se fasse au bon format, autant lorsque cette insensibilité est activée, le coût de retour en arrière est exhorbitant ! Faîtes donc le test suivant. Créez dans votre base de données la table et les données suivantes :Exemple 8

INSERT INTO TEST VALUES ('Électricité')

INSERT INTO TEST VALUES ('électricité')

INSERT INTO TEST VALUES ('électeur')

Executez donc les requêtes de test suivantes :Exemple 9

En principe vous devriez n'avoir qu'une seule occurence...Exemple 10

Là, le folklore est en place !Puis créez dans votre langage favori un tableau de chaîne de caractères et avec une routine de tri de votre cru, triez le et comparez les résultats...Vous êtes horrifié ? Bien fait !... Vous auriez dû me lire avant de vous lançer dans la conception des bases de données relationnelles !!!Voici ce test effectué sous différentes bases de données

paramétra

par défaut

paramétrage

par défaut

paramétrage par

défaut

Paramétrage

par défaut

Paramétrage

par défaut

| Électricité |

| électricité |

électeur

Électricité

électricité

électeur

électricité

Électricité

Électricité

électeur

électricité

électeur

électricité

Électricité

| électeur

| Électricité |

| électricité |

4 Une question de caractères... Je vais encore vous embêtez avec un concept qui, la plupart du temps est ignoré ou passé sous silence, mais qui permet de se sauver de situation inextricables portant sur la comparaison des littéraux. La norme SQL a prévu, outre le jeu de caractère, l'utilisation de séquences de collation. Même si elle ne sont pas encore parfaitement implantées telle que la norme SQL 2 l'a prévue, il est rare qu'un mécanisme similaire ne soit pas fournit dans votre SGBDR.

4.1 Jeu de caractères Le jeu de caractères consiste en une correspondance des 256 ou 65536 caractères par rapport aux symboles graphiques représentés.256 correspond au type CHAR et VARCHAR et 65536 aux types NATIONAL CHAR ou NATIONAL VARCHAR. La plupart du temps nous utilisons sans le savoir un jeu de caractères basique établis par défaut dans la version locale de l'OS.Voici par exemple les jeux de caractères disponible dans les création de tables de Paradox :Exemple 11

chèque, hongrois polonais, slovène

nordique, espagnol, suédois, finlandais

, suèdois, finlandais, espagnol

brésilien, portugais, canadien, français

chèque, hongrois polonais, slovène

norvégien, danois

tchèqu

thaïlandais

coréen

Notez qu'ils sont décomposés en deux familles, celle du DOS (la table des caractères de base n'étant pas normalisé mais propre à l'inventeur du système d'exploitation DOS...) et celle de l'ANSI (relatif à Windows qui se base sur la norme ANSI - American National Standard Institute).On parle souvent de table ASCII. L'ASCII a été créé par BEMMER en 1965 , produit par le groupe de travail X3.4 de l'ANSI, certifié en 1977 et adopté par l'ISO en 1968 sous le n° 646 [f2s]. Ce ne sont en fait que les 128 premiers caractères du fait du codage à l'origine sur 7 bits. Lorsque le codage des caractères à été étendu par nécessité à 8 bits, des déclinaisons de cette table ont été possible pour régler le problème des caractères diacritiques (accents, cédilles, caractères spéciaux tel que le double ss allemand).Rassurez vous l'ISO (International Standard Organisation) s'en est mêlé et propose des jeux de caractères plus cohérent. Voici par exemple la table iso8859-1, actuellement la plus répandue et la plus utilisée notamment sur le net :Exemple 12

Charactèr name

espace non sécable

¡ > ¡ > ¡

¢ > ¢ > ¢

£ > £ > £

symbole monétère général ¤ > ¤

¤

¥ > ¥ > ¥

barre verticale brisée ¦ > ¦ > ¦

§ > § > §

¨ > ¨ > ¨

© > © > ©

ª > ª > ª

« > « > «

¬ > ¬ > ¬

® > ® > ®

¯ > ¯ > ¯

signe des degrés ° > ° > °

± > ± > ±

puissance carrée ² > ² > ²

³ > ³ > ³

´ > ´ > ´

µ > µ > µ

¶ > ¶ > ¶

· > · > ·

cédille ¸ > ¸ > ¸

¹ > ¹ > ¹

º > º > º

» > » > »

¼ > ¼ > ¼

½ > ½ > ½

¾ > ¾ > ¾

errogation inversé ¿ > ¿ > ¿

À > À

À

Á > Á

Á

Â > Â

Â

Ã > Ã > Ã

Ä > Ä

Ä

Å > Å > Å

Æ > Æ

Æ

Ç > Ç > Ç

È > È > È

É > É > É

Ê > Ê > Ê

Ë > Ë

Ë

Ì > Ì > Ì

Í > Í > Í

Î > Î > Î

Ï > Ï > Ï

Ð > Ð > Ð

Ñ > Ñ > Ñ

Ò > Ò > Ò

Ó > Ó > Ó

Ô > Ô > Ô

Õ > Õ > Õ

Ö > Ö

> Ö

× > × > ×

Ø > Ø > Ø

Ù > Ù > Ù

Ú > Ú > Ú

Û > Û > Û

Ü > Ü

Ü

Ý > Ý > Ý

Þ > Þ

Þ

ß > ß

ß

à > à > à

á > á > á

â > â > â

ã > ã > ã

ä > ä

ä

å > å > å

æ > æ

æ

ç > ç > ç

è > è > è

é > é > é

ê > ê > ê

ë > ë

ë

ì > ì > ì

í > í > í

î > î > î

ï > ï > ï

ð > ð > ð

ñ > ñ > ñ

ò > ò > ò

ó > ó > ó

ô > ô > ô

õ > õ > õ

ö > ö

ö

÷ > ÷ > ÷

ø > ø > ø

ù > ù > ù

ú > ú > ú

û > û > û

ü > ü

ü

ý > ý > ý

þ > þ > þ

ÿ > ÿ > ÿ

On trouvera diverses information sur le sujet dans les pages web suivantes :http://alis.isoc.org/codage/iso8859/jeuxiso.htmhttp://naxos.biomedicale.univ-paris5.fr/pp/rg/html/iso8859-1.htmlhttp://www.ping.be/~ping9985/html/iso8859-.htmhttp://www.ceveil.qc.ca/Normes/jcaraciso.htmlhttp://dyninfographie.ca.edu:81/typo/iso-8859-1/http://www.w3.org/MarkUp/html-spec/html-spec_9.html#SEC9.7.2

Autrement dit, le jeux de caractères restreint à 256 symboles ASCII les possibilités de combiner les lettres, signes de ponctuation et autres caractères particulier imprimables.

4.2 Collation et "translation" La séquence de collation est un complément indispensable du jeu de caractères et permet de définir non seulement la position ordinale de chaque caractère mais leur éventuelle "confusion" ! Hé oui, comment pensez vous que la machine puisse devenir insensible à la casse et aux accents sans un tel mécanisme ?C'est ainsi que différentes séquence de collation sont possible sur un même jeu de caractères.Voici par exemple les séquences de collation des différents jeux de caractères de Paradox :Exemple 13

Jeu de caractère Classement des caractères

http://alis.isoc.org/codage/iso8859/jeuxiso.htm

http://naxos.biomedicale.univ-paris5.fr/pp/rg/html/iso8859-1.html

http://www.ping.be/~ping9985/html/iso8859-.htm

http://www.ceveil.qc.ca/Normes/jcaraciso.html

http://dyninfographie.ca.edu:81/typo/iso-8859-1/

http://www.w3.org/MarkUp/html-spec/html-spec_9.html#SEC9.7.2

Par exemple dans la séquence de collation ISO 1252 spanish, le "ñ" ("n" avec tilde) sera confondu avec le caractère "n" lors des comparaions partielles...Pour InterBase par exemple, les jeux de caractères disponibles sont les suivants :Exemple 14

Auxquels sont associés les séquences de collations suivantes :Exemple 15

Comment cela se passe à l'intérieur et à quoi cela correpond-t-il ? Jettons un coup d'oeuil à la procédure stockée sp_helpsort de MS SQL Server et tout va devenir plus lumineux...Exemple 16

¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾

¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A=a À=à Á=á Â=â Ã=ã Ä=ä Å=å Æ=æ

=c Ç=ç D=d E=e È=è É=é Ê=ê Ë=ë F=f G=g H=h I=i Ì=ì Í=í Î=î

Ï=ï J

=j K=k L=l M=m N=n Ñ=ñ O=o Ò=ò Ó=ó Ô=ô Õ=õ Ö=ö Ø=ø

=s ß T=t U=u Ù=ù Ú=ú Û=û Ü=ü V=v W=w X=x Y=y Ý=ý ÿ Z=z

Ð=ð Þ=þ

Comme on le voit, l'ordre de chacun des caractères est établis par la séquence, mais comme cette séquence de collation est rendue insensible à la casse, il y a confusion entre les majuscules et les minuscules...Autrement dit, l'ordre de tri, comme la correspondances des recherches que l'on effectuera dans des requêtes SQL sera prédéfini par la séquence de collation. La méconnaître et méconnaître les possibilités de paramétrage de son SGBDR a ce niveau, c'est laisser faire Microsoft, Oracle ou Borland, comme il l'entend et non comme vous voulez que ce soit et rejaillira tôt ou tard comme problématique dans la mise au point de vos applications...Sachez qu'en général, une séquence de colation par défaut est proposée lors de l'instrallation de votre serveur, et que sur certains SGBDR on peut modifier la

séquence de collation pour la table entière ou encore pour une colonne seulement.Bien entendu la norme SQL 2 a codifié et penser cela depuis fort longtemps et propose même des mécanismes de traduction afin de faire correspondre des données venant de jeux et collations différentes, mais ces possibilités sont encore trop rares à être implémentées au sein des SGBDR des différents éditeurs. Ainsi SQL 2 a défini les CREATE CHARACTER SET et CREATE COLLATION afin de répondre à toutes les attentes. Malheureusement la plupart du temps il n'est possible d'utiliser que les jeux et collations prédéfinies par l'éditeur du SGBDR.Par exemple Microsoft dans la version 7 de sa base de données SQL Server était très en retard sur le sujet. Un seul jeu de caractèress et une seule séquence de collation pouvait être utilisé au sein du serveur ! Comme le serveur était mono instance, il fallit autant de serveur (machine) que d'application pour les différents pays d'exploitation de la base de données, ce qui rendait SQL Server difficile comme choix pour des applications Intenet internationales, sauf à utiliser quelques "trucs" et "bouts de ficelles" à base d'UNICODE... En revanche, de ce côté, un modeste serveur comme InterBase était fort bien pensé !Par exemple pour InterBase, lors de la création d'une base de données, on peut préciser le jeu de caractères :Exemple 17

On peut aussi le faire pour chaque colonne dans la création de table :Exemple 18

Et préciser en outre la collation utilisée :Exemple 19

Mais attention, pas question de rentrer n'importe quoi dans la table, le serveur veille en général à la correspondances des jeux dans les entrées/sortie de flux d'information (requêtes et ensemble de résultats).Ainsi en alimentant la table TEST_COLLATION avec les mêmes données pour les deux colonnes...Exemple 20

INSERT INTO TEST_COLLATION VALUES ('Électricité', 'Électricité')

INSERT INTO TEST_COLLATION VALUES ('électricité', 'électricité')

INSERT INTO TEST_COLLATION VALUES ('électeur', 'électeur')

...nous n'obtiendrons pas le même ordre de tri :Exemple 21 et 22

électeur

électricité

Électricité

Électricité

électeur

électricité

NOTA : pour pouvoir utiliser ce jeu d'essais dans votre interface de requêtage vous devez préalablement indiquer au serveur quen vous utilisez le jeu de caractères ISO8859_1 en utilisant la commande SET NAMES d'InterBase ou le paramétrage avancé.Bien entendu, on peut forcer une séquence de collations dans un select à des fins de comparaison ou de tri. Pour cela il faut utiliser le mot clef COLLATE et donner le nom de la collation de travail :Exemple 23 et 24

COL1 COLLATE EN_US = 'électricité'

électricité

Électricité

électeur

électricité

C'est pourquoi on ne le répétera jamais assez, ce paramètrage entre jeu de caractères et séquence de collation est délicat et obéi souvent à des mécanismes souvent peu normatifs.NOTA : Peter GULUTZAN a écrit deux bons articles sur la problématiques des séquences de collation que l'on trouvera sur le site de dbazine.com à l'url :http://dbazine.com/gulutzen1.htmlhttp://dbazine.com/gulutzen1a.html

Enfin, je ne saurait trop vous recommander, dans la mesure ou vous le pouvez, de paramétrer votre base de données de la manière suivante :

Jeu de caractères ISO8859-1

Sensible à la casse (donc différences entre majuscules et minuscules) Sensible aux accents et autres caractères diacritiques

En effet, on peut toujours jouer sur les séquences de collation pour redéfinir l'ordre de tri et utiliser des fonctions de conversion telles que UPPER ou LOWER pour insensibiliser la casse, ou encore des fonctions avancées telles que la fonction TRANSLATION proposée par ORACLE pour s'affranchir des accents et autres caractères diacritiques. N'oubliez pas qu'une séquence de collation complexe (par exemple assimilant les majuscules, minuscules et accents comme équivalents) est forcément couteuse en temps d'exécution et cela rejaillira sur les performances d'accès et de traitement de vos données... La translation, rarement implémentée consiste à définir un ensemble de correspondances entre caractères. Elle permet de remplacer des caractères par d'autres. Par exemple si nous avons créer une translation nommée REMPLACE_PONCTUATION_ESPACE qui définie que les caractères . , ; : ! ? ' " sont remplacés par un caractères espace, alors l'application de la fonction TRANSLATE avec comme paramètre cet identifiant de translation nous obtiendrons une chaîne de caractères dans laquelle tous les signes de ponctuation mentionnés seront remplacés par des espaces.Exemple 25

Résumons... La norme prévoit la spécification d'un jeu de caractères à l'aide des ordres :

nom_jeu_caractère

nom_jeu_caractère_existant

nom_jeu_caractère

La norme prévoit la spécification d'une collation à l'aide des ordres :

nom_jeu_caractère_existant

http://dbazine.com/gulutzen1.html

http://dbazine.com/gulutzen1a.html

Dans les deux cas (CREATE CHARCATER SET et CREATE COLATION), une ressource de collation pouvant elle même être définie comme :

La norme prévoit la spécification d'une translation à l'aide des ordres :

jeu_caractère_cible

Une collation peut être spécifiée, caractères par caractères à l'aides d'opérateurs de comparaison.Exemple 26

a=A a=à a=â a=ä b=B c=C c=ç d=D e=E e=é e=è ...

Dans ce cas, les caractères a, A, à auront la position ordinale 1, b ou B la position ordinale 2, etc... et cette séquence de collation sera insensible à la casse et aux caractères diacritiques...Le jeu de caractères peut se spécifier dans la création de la base de données :

nom_jeu_caractère]

Enfin, une collation peut être utilisée selon la norme SQL 2 dans une comparaison comme dans la clause de tri d'un ordre SELECT.NOTA : l'implémentation de ces différents éléments varie fortement d'un éditeur de SGBDR à l'autre. C'est pourquoi je vous invite fortement à lire la documentation de votre SGBDR avant de définir votre base et vos tables.

5 Types de données et domainesVous trouverez des compléments d'information sur le sujet aux pages 36 à 46 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.

Nous avons déjà parlé de ces éléments si vous avez lu le tout premier papier consacré à SQL. Mais comme rien ne vaut un bon rappel, voici ce que sont les domaines et les données de SQL.


5.1 Les types SQL 2 La norme SQL 2 propose 4 familles de types de données : les littéraux, les numériques, les binaires et les temporelles, elles mêmes parfois subdiviées.Le tableau suivant résume les différents types de données. En vert figurent les types SQL 2 et en blanc les familles et sous familles :

n précise la longeur associé au type, p la précision (nombre de chiffres significatifs), s les décimales et i1 et i2 la précision de durée parmi YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

5.1.1 Détails des différents types de données... CHARACTER (n) permet de préciser une données litérale (chaîne de caractères) dont les caractères sont codés sur un jeu de type ASCI, EBCDIC ou DOS (2 octets par caractères) de longueur fixe n. Si la donnée à insérer est de taille inférieure elle est complétée par des blancs significatifs.Exemple 27

définit une colonne comportant exactement 4 caractèresLe synonyme CHAR peut être utilisé.CHARACTER VARYING (n) permet de préciser une donnée litérale (chaîne de caractères) dont les caractères sont codés sur un jeu de type ASCI, EBCDIC ou DOS de longueur maximale n. Si la donnée à insérer est de taille inférieure elle compactée et aucun caractère blancs significatifs ne la complète.Exemple 28

définit une colonne comportant au maximum 32 caractères.Le synonyme VARCHAR peut être utilisé.NATIONAL CHARACTER et NATIONALCHARACTERVARYING sont des déclinaisons des deux exemples précédents, mais codés sur le jeu UNICODe, ce qui suppose 4 octets par caractères.Les synaonymes NATIONAL CHAR, NCHAR, NATIONAL CHAR VARYING et NATIONAL VARCHAR peuvent être utilisés.DECIMAL est un réel représentant un nombre en base 10 sans altération de la précision des chiffres situés après la virgule. La précision p peut aller au dela du maximum prévu pour le traitement des données numérique du processeur.Exemple 29

définit une colonne comportant un réel acceptant jusqu'à 60 chiffres significatifs avec 16 chiffres après la virgule. Aucune erreur d'arrondi ne doit apparaître dans le stockage ni les calculs décimaux.NUMERIC est un réel représentant un nombre en base 10 sans altération de la précision des chiffres situés après la virgule. La précision p ne peut dépasser le maximum prévu pour le traitement des données numérique du processeur.Exemple 30

définit une colonne comportant un réel acceptant jusqu'à 16 chiffres significatifs avec 2 chiffres après la virgule. Aucune erreur d'arrondi ne doit apparaître dans la stockage ni les calculs décimaux.FLOAT est un réel représentant un nombre en base 10 stocké en binaire. La précision p peut aller au dela du maximum prévu pour le traitement des données numérique du processeur.Exemple 31

définit une colonne comportant un réel codé en binaire acceptant jusqu'à 64 chiffres significatifs avec 16 chiffres après la virgule. Des erreurs d'arrondi peuvent apparaître dans le stockage ou les calculs décimaux.REAL est un réel représentant un nombre en base 10 stocké en binaire. La précision p ne peut dépasser le maximum prévu pour le traitement des données numérique du processeur sur un nombre de bits déterminé par le système.Exemple 32

définit une colonne comportant un réel codé en binaire acceptant jusqu'à 12 chiffres significatifs avec 4 chiffres après la virgule. Des erreurs d'arrondi peuvent apparaître dans le stockage ou les calculs décimaux.DOUBLE PRECISION est un réel représentant un nombre en base 10 stocké en binaire. La précision p ne peut dépasser le maximum prévu pour le traitement des données numérique du processeur sur un nombre de bits déterminé par le système et plus largement que le REAL.Exemple 33

définit une colonne comportant un réel codé en binaire acceptant jusqu'à 18 chiffres significatifs avec 6 chiffres après la virgule. Des erreurs d'arrondi peuvent apparaître dans le stockage ou les calculs décimaux.SMALLINT est un entier représentant un nombre en base 10 stocké en binaire. La précision est fixe et déterminée par le processeur pour un demi mot.Exemple 34

définit une colonne comportant un entier codé en binaire dont la plage de valeur est fixée dans l'étendue des capacité du demi mot du processeur. Les calculs et le stockage sont toujours exacts.INTEGER est un entier représentant un nombre en base 10 stocké en binaire. La précision est fixe et derminée par le processeur pour un mot entier.Exemple 35

définit une colonne comportant un entier codé en binaire dont la plage de valeur est fixée dans l'étendue des capacités du mot entier du processeur. Les calculs et le stockage sont toujours exacts.BIT définit une chaîne de bits dont la longueur est fixe.Exemple 36

définit une colonne comportant deux bits pouvant avoir les valeurs 00 01 10 ou 11.BIT VARYING définit une chaîne de bits dont la longueur est variable.Exemple 37

définit une colonne comportant au maximum 8 bits pouvant prendre la plage de valeur allant de 0 à 11111111.TIMESTAMP combine date et heure avec une précision du millième de seconde. On peut préciser le fuseau horaire avec l'option TIME ZONE. La norme SQL 2 précise que l'étendue de ce type de données doit aller du premier janvier de l'an 1 à zéro heure jusqu'au 31 décembre de l'an 9999 à 23h59m59s999.Exemple 38

L'option TIME ZONE permet de définir le décalage horaire par rapport à l'heure universelle (UTC) :Exemple 39

Par exemple en été en France, ou le décalage horaire est de +2 heures par rapport au temps universel, nous pourrions stocker des données sous la forme :Exemple 40

DATE permet de stoker des dates dans l'étendue premier janvier de l'an 1 jusqu'au 31 décembre de l'an 9999.Exemple 41

TIME permet de stoker des heures dans l'étendue de 00:00:00.000 h à 23:59:59.999 h. Comme dans le cas du TIMESTAMP, l'option TIME ZONE permet de définir le décalage horaire par rapport à l'heure universelle (UTC) :Exemple 42

NOTA : pour les types DATE, TIME et TIMESTAMP, le format de saisie est calé sur la norme ISO qui précise que les données temporelles doivent être écrites de la manière suivante : AAAA-MM-JJ et les heures : HH:MM:ss.xxxATTENTION : d'après la norme, on peut préciser le nombre de chiffres significatifs après la seconde des types TIME et TIMESTAMP. Ainsi, la déclaration d'un TIMESTAMP(6) permettrait de stocker des informations horaires avec une précision du milliardième de seconde. Compte tenu de la difficulté d'implémentation, de stockage et de calculs de telles données, ce dispositif est rarement implémenté sur les SGBDR des différents éditeurs.INTERVAL est un type de données permettant de stocker des durées. Le stockage peut se faire sur une unité de temps ou sur une étendue d'unité continuelle de temps à l'aide des mots clefs YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.La syntaxe de déclaration d'un tel type est la suivante :

Avec les règles suivantes :

doit être plus précis que

2) debut peut dépasser la capacité maximale ordinaire habituellement attribuée à la mesure

Exemple 43

Si vous désirez mesurer des durées en heures, vous pouvez par exemple stocker 178 heures 23 minutes et 16 secondes sous la forme suivante :Exemple 44

NOTA : Ce type INTERVAL est maheureusement rarement implémenté. La plupart du temps les éditeurs recommandent d'utilier un FLOAT pour stocker des durées en fraction de jours.

REMARQUE : on peut être étonné de l'absence de type "booléen" dans la norme. Il est facile d'y palier en utilisant un type BIT(1). En l'absence du type BIT, on peut utiliser un SMALLINT associé à une règle de validation ou bien un littéral de type CHAR(1) avec une plage de valeur.ATTENTION : lorsqu'une ligne est ajoutée à une table, les colonnes non renseignées possèdent le marqueur NULL.

5.1.2 Typage rapide avec des préfixes Comment préciser le type cible d'une chaîne de caractère ? Là encore la norme vient à notre rescousse. En principe une chaîne de caractères est par défaut du type CHAR ou VARCHAR. Pour la convertir en NATIONAL (CHAR ou VARCHAR) il suffit de la préfixer avec la lettre d'attribut N. De même pour une chaîne représentant une valeur binaire, il faut utiliser la lettre B et pour une chaîne de valeur hexadécimale la lettre X. Il n'y a pas de lettres préfixes pour les autres types. Ainsi pour les chaînes de caractères CHAR / VARCHAR il suffit d'utiliser l'apostrophe comme délimiteur de la chaîne en dédoublant toute apostrophe se trouvant au sein de la chaîne. Enfin pour les données temporelles on peut les préfixer du nom de type.Exemples 45

chaîne CHAR ou VARCHAR

chaîne NATIONAL CHAR ou NATIONAL

chaîne hexadécimale

chaîne binaire

Combiné date/heure avec décalage de

Intervalle de temps borné par heures et

5.2 Les nouveaux types SQL 3 La norme SQL 3 (1999) a rajouté 3 types fondamentaux : booléen, CLOB et BLOBBOOLEAN est un type de données valant vrai ou faux (ou possédant le marqueur NULL si non renseigné).Exemple 46

NOTA : du fait de la présence du marqueur NULL, le type booléen obéi à une logique ternaire.CHARACTER LARGE OBJECT [(n [m])] est un type litteral permettant de stocker des chaînes de caractères de grande dimension dans un jeu à deux octets par caractères (ASCII). Le paramètre n permet de préciser la longeur et le paramètre m

la mesure de cette longueur parmi les lettres K, M ou G.Exemple 47

Est un type littéral acceptant 64 kilo binaire de caractères, soit 64 * 1024 = 65 536 caractères (donc 131 072 octets).Le synonyme CLOB peut être utilisé.NATIONAL CHARACTER LARGE OBJECT [(n [m])] est une déclinaison de CHARACTER LARGE OBJECT opérant sur un jeu à quatre octets par caractères (UNICODE)Le synonyme NCLOB peut être utilisé.BINARY LARGE OBJECT [(n [m])] définit une chaîne de bits de grande dimension dont la longueur est fixe. Le paramètre n permet de préciser la longeur et le paramètre m la mesure de cette longueur parmi les lettres K, M ou G.Exemple 48

Est un type chaîne de bits acceptant 2 giga octets de données, soit 2 097 152 octets.Le synonyme BLOB peut être utilisé.La norme SQL 3 a de plus défini les types structurés ARRAY, ROW, REF ainsi que des types utilisateurs appelés UDT.Syntaxe :

Exemple 49

Ces trois exemples montrent :

un tableau d'entiers à deux dimensions doté de 12 lignes et 31 colonnes, une ligne dotée de 5 éléments permettant de stocker des adresses

une données référentielle basée sur un type utilisateur permetant de restreindre les valeurs aux données situées dans une autre table.

NOTA : le type SQL 3 ROW sont assimilables à des types "record" dans certains langages comme Pascal/Delphi. Bien entendu ces types peuvent être combinés :Exemple 50

REMARQUE : il n'entre pas dans ce papier d'expliquer les UDT dont l'intérêt et la complexité mérite un papier à part entière. Néanmoins les UDT permettent de définir de nouveaux types de données, par exemples pour stocker des coordonnées planaires ou spaciales (polaire, cartèsiennes...), des adresses IP, etc...

5.3 Types communs présent dans certains SGBDR des différents éditeursOn trouve courrament les types suivants dans certains SGBDR :

ur restreintes (à éviter)

un format spécifique aux valeurs monétaires (à éviter)

un entier avec une plage de valeur très restreinte (par

exemple 0 à 255)

un entier avec une plage de valeur très étendue

un BLOB spécialisé pour stocker des images

données "brutes"

des identifiants spécifiques auto générés.

des valeurs dans un ensemble prédéfini

Ces types ne sont pas normatif, il convient donc de les éviter pour des raisons de portabilité.

5.4 Définir des domaines et les utiliser Un domaine au sens de la norme SQL est la définition d'un type associé à un certain nombre de règles de validité.Imaginons que nous voulons modéliser le nombre de pages d'un livre. Il est à priori absurde d'accepter un nombre de pages négatif ou nul. Dans ce cas, il faudrait créer un nouveau type dont la plage des valeurs serai restreinte. D'ou l'idée de domaine qui rassemble à la fois le type et l'étendue des valeurs possible, c'est à dire le domaine des possibilités d'exploitation.

La défintion d'un domaine s'opére avec la syntaxe suivante :

] type_donnée

valeur_défaut]

NOTA : une règle de validation doit utiliser le mot clef VALUE pour faire référence à la donnée.Exemple 51

La clause DEFAULT d'un ordre de création de domaine peut contenir une valeur explicite, ou bien l'une des valeurs retournées par les fonctions suivantes :

utilisateur par défaut

utilisateur système

non renseigné

Exemple 52

Cet exemple créé un domaine de type 3 caractères UNICODE sur la séquence de collation UNICODE (4 octets par caractères, ordre binaire) avec la valeur par défaut 'NON' et accepte toutes les combinaisons de valeurs suivantes :'oui', Oui', 'OUi', 'OUI', 'oUI', 'OuI', 'oUi', 'ouI', 'non', 'Non', 'nOn', 'noN', 'NOn', 'nON', 'NoN',

'NON'

Exemple 53

Cet exemple permet de modéliser des immatriculations au format : "NNN AAA MM" ou NNN sont des chiffres de 0 à 9, AAA des lettres majuscules de 'A' à 'Z' excepté 'I' et 'O', et un code minéralogique correspondant au numéro des départements de 01 à 95 excepté 20, mais acceptant 2A et 2B pour la Corse !Bien entendu la norme SQL 2 prévoir la modification d'un domaine en permettant de rajouter ou retirer une clause défaut ou une contrainte :

valeur_défaut

Exemple 54

NOTA : il n'est pas possible de changer le type de données de la définition d'un domaine.La suppression d'un domaine s'effectue avec un ordre SQL DROP :

Lorsque l'on précise CASCADE, le domaine pourra être supprimé sans que les colonnes afférentes à ce domaine en soit affectées. Dans ce cas, le type du domaine comme les contraintes y afférent sont transférés à toutes les colonnes utilisant ce domaine. Si la clause RESTRICT est utilisée alors une erreur sera levée si des colonnes de table dans la base utilise le domaine à supprimer. Sinon, la suppression sera effective.REMARQUE : certains SGBDR n'utilise pas des domaines mais proposent des mécanismes similaires.En particulier c'est le cas de MS SQL Server qui utilise une combinaison de règles et de type utilisateur.Exemple 55 :

Création de l'équivalent d'un domaine dans MS SQL Server pour représenter un pourcentage.NOTA : un domaine est bien entendu utilisable à la place d'un type SQL pour définir une colonne de table.

La suite de cette article ici


objets et types


noms normatifs

NON OUI NON

CONNEXION NON NON

SESSION NON NON

CATALOG NON OUI

SCHEMA OUI OUI

jeu de caractères

OUI OUI

collations OUI OUI (v2000)

translation NON NON

CHAR NON OUI

VARCHAR OUI OUI

NCHAR NON OUI

NVARCHAR NON OUI

BIT NON OUI

VARBINARY OUI OUI

INT OUI OUI

SMALLINT OUI OUI

FLOAT OUI OUI

DOUBLE PRECISION

NON OUI

DECIMAL OUI (BCD) OUI

NUMERIC NON OUI

TIMESTAMP OUI OUI

TIME OUI NON

DATE OUI NON

INTERVAL non NON

BOOLEAN OUI NON (2 -

BIT)

BLOB OUI OUI

CLOB / NCLOB

OUI / non OUI

ARRAY non NON

ROW non NON

REF non NON

DOMAIN non NON (2)

autres types non NON (2)

(1) le BIT SQL est limité à 1 BIT valant 0 ou 1(2) non, mais mécanisme similaire

6 Les assertions

Les assertions sont des contraintes dont l'étendue dépasse les types de données, les colonnes et la table pour permettre des règles de validation entre différentes colonnes de différentes tables. Les assertions au sens de la norme SQL sont donc des objets de la base de données.

La syntaxe de création d'une assertion est la suivante :

NOTA : les règles de déférabilité seront discutées lors de la partie consacrée aux contraintes de table.

Par exemple vous pouvez définir une règle de gestion qui indique que le montant des commandes non réglées ne doit pas dépasser 20% du montant du chiffre d'affaire déjà réalisé par le client.Pour réaliser notre exemple nous avons besoin des tables T_CLIENT, T_FACTURE et T_COMPTE, définies de manière simpliste comme suit :

Dans ce cas, l'assertion prendra la forme :Exemple 56 :

Autre exemple, considérons que l'unicité d'une clef doit porter sur deux tables. Par exemple que la clef identifiant un client ou un prospect doit être unique au sein des deux atbles afin qu'un prospect puisse devenir un client sans changement de clef. Dans ce cas l'assertion suivante peut être mise en place :Exemple 56 :

REMARQUE : certains SGBDR n'utilise pas les assertions mais propose des mécanismes similaires généralement nommés RULE (règle)..

7 Les tables

La voila la grosse partie du DDL qui vous passionne. Alors otons nous tout de suite une épine du pied en définissant la syntaxe de la création de table :

( prédicat_de_colonne )

spécification_référence

( prédicat_de_table )

nom_table (liste_colonne) spécification_référence }

C'est une des syntaxes les plus simples que j'ai pu trouver pour vous montrer l'ensemble des possibilités qu'offre la norme SQL pour créer une table.

En gros, disons que :

une table peut être créée de manière durable (par défaut) ou temporaire et dans ce dernier cas uniquement pour l'utilisateur et la connexion qui l'a créé ou bien pour l'ensemble des utilisateurs de la base.

une table comporte des colonnes et des contraintes de table

une colonne peut être spécifié d'après un type SQL ou un domaine créé par l'utilisateur

une colonne définie peut être dotée de contraintes de colonnes telles que : obligatoire, clef, unicité, intégrité référentielle et validation.

une contrainte de table porte sur une ou plusieurs colonnes et permet l'unicité, la validation et l'intégrité référentielle

RAPPEL : un nom de colonne doit être unique au sein de la table

Voici quelques exemple de création de table utilisant tantôt des types SQL tantôt des domainesExemple 57 :

Une table de clients doté de deux colonnes avec les nom et prénom des clients.Exemple 58 :

Une table de clients doté de trois colonnes avec la clef (numero du client) les nom et prénom des clients.Exemple 59 :

Une table de clients similaire à l'exemple 57 à base de domaines mais la clef ne peut être négative.Exemple 60 :

Une table de clients similaire à l'exemple 57 dont le nom ne peut commencer par un blanc, doit être en majuscule et dont le prénom doit figurer dans la table de référence TR_PRENOM à la colonne PRN_PRENOM.Exemple 61 :

Une table de voiture avec immatriculation unique et couleur limitée à 'BLANC', 'NOIR', 'ROUGE', 'VERT', 'BLEU'.Exemple 62 :

Une table de clients dont la clef est le couple de colonne NOM/PRENOM.Nous allons maintenant détailler les différentes contraintes dites verticales ou horizontales suivant qu'il s'agit de contrainte de colonne ou de contrainte de ligne. Cette notion de vertical et horizontal fait référence à la visualisation des données de la table :

Une contrainte de colonne est dite verticale parce qu'elle porte sur une seule colonne. Dans la figure ci dessus la contrainte de colonne est une clef (PRIMARY KEY).Une contrainte de ligne est dite horizontale parce qu'elle porte sur plusieurs colonne et se valide pour chaque ligne insérée.La différence entre contrainte de ligne (horizontale) et contrainte de colonne (verticale) est purement terminologique puisque certaines contraintes peuvent être définies horizontalement comme verticalement :

REMARQUE ; notons que toute contrainte peut être déférrée, c'est à dire que ses effets peuvent être suspendue pour ne jouer qu'à la fin d'une transaction plutôt qu'à chaque ordre SQL sensée la solliciter.

7.1 Les contraintes de colonnes (verticales) Une colonne peut donc recevoir les contraintes suivante :

NULL / NOT NULL : précise si une valeur doit obligatoirement être saisie dans la colonne ou non

DEFAULT : valeur par défaut qui est placée dans la colonne lors des insertions et de certaines opération particulières, lorsque l'on a pas donné de valeur explicite à la colonne.

COLLATE : précise la séquence de collation, c'est à dire l'ordre des caractères pour le tri et les évnetuelles confusions possible (minuscules/majuscules, caractères diacritiques distinct ou non). Voir paragraphe 4 a ce sujet.

PRIMARY KEY : précise si la colonne est la clef de la table. ATTENTION : nécessite que la colonne soit NOT NULL.

UNIQUE : les valeurs de la colonne doivent être unique ou NULL, c'est à dire qu'à l'exception du marqueur NULL, il ne doit jamais y avoir plus d'une fois la même valeur (pas de doublon).

CHECK : permet de préciser un prédicat qui acceptera la valeur s'il est évalué à vrai.

FOREIGN KEY : permet, pour les valeurs de la colonne, de faire référence à des valeurs préexitantes dans une colonne d'une autre table. Ce mécanisme s'apelle intégrité référentielle.

NOTA : toutes ces contraintes peuvent être placées dans plusieurs colonne, à l'exception de la contrainte de clef PRIMARY KEY qui ne peut être placée que sur une seule colonne. Pour faire de plusieurs colonne une clef, il faut utiliser une contrainte de ligne (horizontale). Lorsqu'au cours d'un ordre SQL d'insertion, de modification ou de suppression, une contrainte n'est pas vérifiée on dit qu'il y a "violation" de la contrainte et les effets de l'ordre SQL sont totalement annulé (ROLLBACK).REMARQUE : le mot clef CONSTRAINT comme le nom de la contrainte n'est pas obligatoire dans le cas de contraintes de colonnes.

7.1.1 Obligatoire ([NOT] NULL) On peut rendre la saisie d'une colonne obligatoire en apposant le mot clef NOT NULL. Dans ce cas, il ne sera jamais possible de faire en sorte que la colonne soit vide. Autrement dit, la colonne devra toujours être renseigné lors des ordres d'insertion INSERT et de modification UPDATE.Si l'on désire que la colonne puisse ne pas être renseignée (donc accepter les marqueurs NULL), il n'est pas nécessaire de préciser le mot clef NULL, mais il est courrant qu'on le fasse par facilité de lectureExemple 63 :

Créé une table dont les colonnes PRS_ID et PRS_NOM doivent obligatoirement être renseignés.Exemple 64 - insertion et modification acceptées :


Exemple 65 - insertion et modification refusées :

NOTA : les colonnes concourrant à la définition d'une clef de table doivent impérativement possèder une contrainte NOT NULL.

7.1.2 Valeur par défaut (DEFAULT) La contrainte DEFAULT permet de préciser une valeur qui sera automatiquement insérée en l'absence de précision d'une valeur explicite dans un ordre d'insertion. Certains autres ordres SQL, comme la gestion de l'intégrité référentielle peuvent faire référence à cette valeur par défaut. Seule une valeur explicite, un marqueur NULL ou la valeur retournée par les fonctions suivantes sont acceptées : CURRENT_DATE, CURRENT_TIME[(p)], CURRENT_TIMESTAMP[(p)], LOCALTIME[(p)], LOCALTIMESTAMP[(p)], USER, CURRENT_USER, SESSION_USER, SYSTEM_USER.Exemple 66 :

NOTA : il n'est pas possible de préciser une valeur par défaut qui soit le résultat d'une expression de requête.Exemple 67 :

Qui pourrait s'avérer bien utile pour générer de nouvelles valeurs de clefs auto incrémentées !

7.1.3 Séquence de collation (COLLATE) La séquence de collation permet de préciser l'ordre positionnel des caractères et leur éventuelle confusion, par exemple pour s'affranchir de la différence entre majuscule et minuscule ou encore entre caractères simples et caractères diacritiques (accents, cédille et autre...). La séquence de collation opére sur le tri et la comparaison des valeurs littérales.Exemple 68 :

Rapellons qu'une séquence de collation est attaché à un jeu de caractère. Pour de plus amples informations, voir "Jeu de caractères et séquence de collation"

7.1.4 Clef (PRIMARY KEY) Selon le docteur Codd, toute table doit être munie d'une clef (souvent apellé à tort clef primaire en opposition à clef étrangère...). Et toujours selon le docteur Codd et la théorie des bases de données, une clef doit impérativement toujours être pourvue d'une valeur ! (sinon à quoi servirait une clef en l'absence de serrure ?).Lorsque la clef porte sur une seule colonne il est possible de donner à cette colonne la contrainte PRIMARY KEY.Nous avons vu que la contrainte PRIMARY KEY peut être posée sur une colonne (contrainte verticale) ou sur plusieurs colonnes en contrainte de ligne (horizontale). Si nous choisissons de la poser en contrainte de colonne, alors une seule colonne de la table peut en bénéficier.Exemple 69 :

La contrainte PRIMARY KEY assure qu'il n'y aura aucune valeur redondante (doublon) dans la colonne. La contrainte complémentaire NOT NULL assure qu'il y aura toujours une valeur. Toute tentative d'insérer une valeur préexistante de la colonne se soldera par une violation de contrainte de clef. Voici par exemple le message généré par SQL Server dans ce cas :

Violation de la contrainte PRIMARY KEY 'PK__T_PERSONNE5__45F365D3'. Impossible d'insérer

une clé en double dans l'objet 'T_PERSONNE5'.


NOTA : il est d'usage de placer la colonne clef en tête de la description de la table pour des fins de lisibilité.Exemple 70 - clef primaire multicolonne impossible en contrainte verticale :

Impossible d'ajouter plusieurs contraintes PRIMARY KEY à la table 'T_PERSONNE6'.

7.1.5 Unicicité (UNIQUE) La contrainte d'unicité exige que toutes les valeurs explicites contenue dans la colonne soient uniques au sein de la table. En revanche, la colonne peut ne pas être renseignée. En effet, souvenez vous que les marqueurs NULL se propagent dans les calculs et donc comparaison d'un marqueur NULL à un ensemble de valeurs est impossible et se solde par le renvoi d'un marqueu UNKNOW à la place des valeurs TRUE ou FALSE attendue.


Exemple 71 :

'André',

Violation de la contrainte UNIQUE KEY 'UQ__T_PERSONNE7__47DBAE45'. Impossible d'insérer une

clé en double dans l'objet 'T_PERSONNE7'.

ction a été arrêtée.

André

Dans cet exemple Dugland n'a pas été inséré car son numéro de téléphone est identique à Durand.REMARQUE : certains SGBDR comme MS SQL Server refuse de voir la présence de plusieurs marqueurs NULL dans la cas d'une contrainte d'unicité. D'autres comme InterBase refusent une contrainte d'unicité dépourvue d'une contrainte NOT NULL...ATTENTION : vous ne pouvez pas définir une contrainte d'unicité sur des colonnes

de type BLOB

7.1.6 Validation (CHECK) La contrainte CHECK de validation est celle qui offre le plus de possibilité. En contre partie son exécution est très couteuse. Elle permet de définir un prédicat complexe, basé sur une comparaison pouvant contenir une requête de type SELECT. Pour valider la contrainte, le prédicat doit etre évalué à TRUE ou UNKNOWN (présence de NULL).Sa syntaxe est :

CHECK ( prédicat )

ou prédicat peut contenir le mot clef VALUE pour faire référence à la colonne pour laquelle la contrainte est définie.Exemple 72 :

La colonne PRS_ID ne peut avoir de valeurs inférieures à 0La colonne PRS_NOM doit avoir des valeurs contenant au moins 2 caractèresLe premier caractère de la colonne PRS_PRENOM, si elle est renseigné, doit être compris entre A et ZLa colonne PRS_SEXE peut avoir exclusivement les valeurs M ou FLes trois premiers caractères de la colonne PRS_TELEPHONE si elle est renseignée doit correspondre à une valeur se trouvant dans la colonne PREFIXE de la table T_NUM_TEL.ATTENTION : la longueur du prédicat d'une contrainte CHECK (en nombre de caractères) peut être limité. Il faut en effet pouvoir stocker cette contrainte dans le dictionnaire des informations de la base et ce dernier n'est pas illimité.

7.1.7 Intégrité référentielle (FOREIGN KEY / REFERENCES) La contrainte de type FOREIGN KEY permet de mettre en place une intégrité référentielle entre une (ou plusieurs) colonnes d'une table et la (ou les) colonne composant la clef d'une autre table afin d'assurer les relations existantes et joindre les tables dans le requête selon le modèle relationnel que l'on a défini.Le but de l'intégrité référentielle est de maintenir les liens entre les tables quelque soit les modiifications engendrées sur les données dans l'une ou l'autre table.Cette contrainte dans sa syntaxe complète est assez complexe et c'est pourquoi nous allons dans ce paragraphe donner une syntaxe très simplifié à des fins didactiques :

La syntaxe complète de la clause FOREIGN KEY sera vu au paragraphe 7.3.

ATTENTION : la colonne spécifiée comme référence doit être une colonne clef.Exemple 73

La table T_FACTURE1 est liée à la table T_PERSONNE5 et ce lien se fait entre la clef étrangère PRS_ID de la table T_FACTURE1 et la clef de la table T_PERSONNE5 qui s'intitule aussi PRS_ID.NOTA : il est très important que les noms des colonnes de jointure soit les mêmes dans les différentes tables (notamment à cause du NATURAL JOIN), mais cela n'est pas obligatoire.Dès lors toute tentative d'insertion d'une facture dont la référence de client est inexistante se soldera par un échec. De même toute tentative de supprimer un client

pour lequel les données d'une ou de plusieurs factures sont présente se soldera par un arrêt sans effet de l'ordre SQL.Examinons maintenant comment le SGBDR réagit pour assurer la cohérence de la base lors d'opérations tenant de briser les liens d'intégrité référentielle :Exemple 74 :

INSERT INTO T_PERSONNE5 VALUES (2, 'Duval', 'André')

pas référencé dans la table

onnées 'DB_HOTEL', table 'T_PERSONNE5',


d'une personnet possédant

survenu dans la base de données 'DB_HOTEL', table 'T_FACTURE1',


REMARQUE : Comme on le voit, le mécanisme d'intégrité référentielle est un élément indispensable au maintient des relations entre tables. Un SGBD qui en est dépourvu ne peut pas prétendre à gérer le relationnel. En particulier MySQL ne peut en aucun cas prétendre être une base de données relationnelle !

7.2 Les contraintes de table Une table peut être pourvue des contraintes de ligne suivante :

PRIMARY KEY : précise que la ou les colonnes composent la clef de la table. ATTENTION : nécessite que chaque colonne concourrant à la clef soit NOT NULL.

UNIQUE : les valeurs de la ou les colonnes doivent être unique ou NULL, c'est à dire qu'à l'exception du marqueur NULL, il ne doit jamais y avoir plus d'une fois la même valeur (pas de doublon) au sein de l'ensemble de données formé par les valeurs des différentes colonnes composant la contrainte.

CHECK : permet de préciser un prédicat validant différentes colonnes de la table et qui accepterons les valeurs s'il est évalué à vrai.

FOREIGN KEY : permet, pour les valeurs de la ou les colonnes, de faire référence à des valeurs préexitantes dans une ou plusieurs colonnes d'une autre table. Ce mécanisme s'apelle intégrité référentielle.

Comme dans le cas des contraintes de colonne, lorsqu'au cours d'un ordre SQL d'insertion, de modification ou de suppression, une contrainte n'est pas vérifiée on dit qu'il y a "violation" de la contrainte et les effets de l'ordre SQL sont totalement annulé (ROLLBACK).

7.2.1 Clef multicolonne (PRIMARY KEY) La clef d'une table peut être composée de plusieurs colonnes. Dans ce cas la syntaxe est :

Exemple 75 - clef primaire sur PRS_NOM / PRS_PRENOM

7.2.2 Unicité globale (UNIQUE) Un contrainte d'unicité peut être portée sur plusieurs colonnes. Dans ce cas chaque n-uplets de valeurs explicite doit être différents.Dans ce cas la syntaxe est :

Exemple 76 - défintion d'une clef unqiue sur PRS_NOM / PRS_PRENOM

'André')

'André')

Impossible d'insérer une clé en double dans l'objet 'T_PERSONNE10'.


André

REMARQUE : certains SGBDR comme MS SQL Server refuse de voir la présence de plusieurs marqueurs NULL dans la cas d'une contrainte d'unicité. D'autres comme InterBase refusent une contrainte d'unicité dépourvue d'une contrainte NOT NULL...ATTENTION : vous ne pouvez pas définir une contrainte d'unicité sur des colonnes

de type BLOB

7.2.3 Validation de ligne (CHECK) La contrainte CHECK permet d'effectuer un contrôle de validation multicolonne au sein de la table.Sa syntaxe est :

CONSTRAINT nom_contrainte CHECK ( prédicat )

Exemple 77 - vérification de présence d'information dans au moins une colonne crédit ou débit de la table compte :

Toute tentative d'insérer une ligne avec des valeurs non renseignée pour les colonnes debit et credit, ou bien avec des valeurs négative se soldera par un refus.

7.2.4 Integrité référentielle de table (FOREIGN KEY /

REFERENCES) Comme dans la cas d'une contrainte référentielle de colonne, il est possible de placer une contrainte d'intégrité portant sur plusieurs colonne. Ceci est d'autant plus important qu'il n'est pas rare de trouver des tables dont la clef est composée de plusieurs colonnes. La syntaxe est la suivante :

Exemple 78

La table T_FACTURE2 est liée à la table T_PERSONNE9 et ce lien se fait entre la clef étrangère composite PRS_NOM / PRS_PRENOM de la table T_FACTURE2 et la clef de la table T_PERSONNE9 elle même composée des colonnes PRS_NOM / PRS_PRENOM.Examinons maintenant comment le SGBDR réagit pour assurer la cohérence de la base lors d'opérations tenant de briser les liens d'intégrité référentielle :Exemple 79

SERT INTO T_PERSONNE9 VALUES ('Duval', 'André', NULL)

INSERT INTO T_FACTURE2 VALUES (1, 'Duval', 'André', '2002

pas référencé dans la table

e données


d'une personnet possédant

survenu dans la base de données 'DB_HOTEL', table 'T_FACTURE1',


7.3 La gestion de l'intégrité référentielle Comme nous l'avions annoncer, la syntaxe de la pose de contraintes d'intégrité est plus complexe que ce qui vient d'être évoqué. Voici la syntaxe complète de cettte structure :

référence

REFERENCES table_référencée (liste_colonne_référencées)

ON UPDATE de mise à

Clause de déférabilité

Nous allons maintenant détailler les clause MATCH, ON UPDATE, ON DELETE et la déferabilité.

7.3.1 Mode de gestion de la la référence, clause MATCH Pour mieux comprendre le fonctionnement de cette clause, voici le modèle utilisé :Exemple 80 :

MATCH SIMPLE implique que :

si toutes les colonnes contraintes sont renseignées, la contrainte s'applique

si une colonne au moins possède un marqueur NULL, la contrainte ne s'applique pas

Exemple 81 :

Insertion réussie

de données 'DB_HOTEL', table 'T_FOURNISSEUR'.


MATCH FULL implique que :

la contrainte s'aplique toujours sauf si toutes les colonnes sont pourvues d'un marqueur NULL

Par conséquent, il ne peut y avoir une colonne renseigné et l'autre pas. Exemple 82

Insertion réussie

base de données 'DB_HOTEL', table 'T_FOURNISSEUR'.


MATCH PARTIAL implique que :

La contrainte s'applique pour toutes les colonnes renseignées

Exemple 83

Insertion réussie

de données 'DB_HOTEL', table 'T_FOURNISSEUR'.


NOTA : certains SGBDR n'ont pas implémenté le mode de gestion de la référence. C'est le cas en particulier de MS SQL Server et d'InterBase.

7.3.2 Mode de gestion de l'intégrité clauses ON UPDATE / ON

DELETE Le mode de gestion de l'intégrité consiste à se poser la question de ce que la machine doit faire dans le cas ou l'on tente de briser une intégrité référentielle. Nous avons vu que par défaut il n'est pas possible de supprimer une personne ayant encore desdonnées dans la table des factures et qu'il n'est pas possible d'insérer une facture pour une personne nonréférencée. Se mode est dit en SQL : ON UPDATE NO ACTION, ON DELETE NO ACTION ce qui signifie qu'aucune action particulière n'est entreprise en cas de mise à jour ou suppression.Nous allons maintenant voir quels sont les autre mode de gestion de l'intégrité référentielleATTENTION : ce mode n'a aucun effet sur le comportement de la contrainte qui s'exerce de toute façon en fonction de la clause MATCHON DELETE NO ACTION / ON UPDATE NO ACTION : aucun traitement particulier n'est entrepris en cas de mise à jour ou suppression d'informations référencées. Autrement dit, il y a blocage du traitement car le lien d'intégrité ne doit pas être brisé. Même effets que RESTRICT, mais post opératoire.

ON DELETE CASCADE / ON UPDATE CASCADE : en cas de suppression d'un élément, les éléments qui le référence sont aux aussi supprimés. En cas de modification de la valeur de la clef, les valeurs des clefs étrangères qui le référence sont elles aussi modifiées afin de maintenir l'intégrité. Par exemple en cas de suppression d'un client les factures et commandes sont elles aussi supprimées. NOTA : ce mode est très tentant, mais son coût de traitement est très élevé et les performances peuvent très rapidement se dégrader fortement.

ON DELETE SET NULL / ON UPDATE SET NULL : en cas de suppression d'un élément, les éléments qui le référence voit leur clef étrangère posséder le marqueur NULL . De même en cas de modification de la valeur de la clef. Le lien d'intégrité est alors brisé. L'intérêt d'une telle manoeuvre est de permettre la suppression des lignes devenues orphelines de manière différé, par exemple dans un batch de nuit.

ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT : en cas de suppression comme en cas de mise à jour de la clef référencée, la référence passe à la valeur par défaut définie lors de la création de la table. Ce mode permet l'insertion d'un client générique, possédant un identifiant particulier (par exemple 0 ou -1) afin de ne jamais briser le lient d'intégrité référentielle. Bien entendu on veillera ensuite à rectifier la vrai valeur du lien au moment opportun si besoin est.

ON DELETE RESTRICT / ON UPDATE RESTRICT : même effets que NO ACTION, mais pré opératoire.

NOTA : certains SGBDR n'ont pas implémenté le mode de gestion de l'intégrité. C'est le cas en particulier de MS SQL Server. En revanche, il est courant de trouver dans les SGBDR des options plus limitées que celles fournies par la norme.

7.4 Mode de gestion de la déférabilité La déférabilité d'une contrainte est une opération nécessaire dès que différentes contraintes intéragissent créant ainsi ce que l'on apelle une "référence circulaire"...Ainsi lorsqu'une table T1 fait référence à une table T2 par une intégrité référentielle, se pose le problème de la mise en place d'une intégrité référentielle inverse de T2 vers T1...Encore une fois nous voici confronté au problème de l'oeuf et de la poule... C'est pour trancher ce dilemne que la déférabilité d'une contrainte a été défini par la norme SQL 2. Les SGBDR metant en oeuvre cette gestion, comme ORACLE, ne courrent pas les rues !Tentons cependant d'y voir clair, à l'aide d'un exemple...Imaginons que nous voulons modéliser un client et ses commandes et placer dans la table du client la dernière commande servie... Le problème se pose ainsi : comment insérer un nouveau client qui, par définition, n'a pas encore de commande, alors que l'on exige dans la table client de faire référence à la dernière commande ?Voici un premier jet du script de création de nos deux tables :Exemple 84 :

Il y a fort à parier que ce script ne puisse être joué sur la plupart des SGBDR...On peut néanmoins l'amender de la manière suivante :Exemple 85 :

Cependant je vous met au défi de pouvoir insérer quoi que ce soit dans l'une quelconque des tables, puisque l'une à besoin des informations de l'autre et vice versa...Pour répondre à ce cas de figure, la norme SQL 2 à défini la "déférabilité" d'une contrainte... Au fait savez-vous ce qu'est la déférabilité ? Déférer quelqu'un c'est transféré la responsabilité de cette personne à un moment donné, à une autre instance. Ainsi un gangster déféré au parquet voit la responsabilité de son arrestation, passer des mains des policiers aux mains des juges. La déférabilité est donc la possibilité de "déférer".Pour la norme SQL 2, la déférabilité se précise :

lors de la création du schéma

lors de l'exécution de la contrainte

Elle permet de transférer le moment ou la validation de la contrainte va s'effectuer... Reprenons la syntaxe de la clause de déférabilité d'une contrainte :

Une contrainte peut donc être définie comme NOT DEFERRABLE (c'est l'option par défaut) dans ce cas elle s'apllique immédiatement (INITIALLY IMMEDIATE).Si elle est définie comme DEFERRABLE, alors il convient de préciser quand :

INITIALLY DEFERRED signifie qu'elle prendra ses effets en fin de transaction, INITIALLY IMMEDIATE signifie qu'elle appliquera la contrainte dans l'ordre de

mise à jour (INSERT, UPDATE DELETE) sans attendre la fin de la transaction.

Pour modifier la déférabilité d'une contrainte, SQL 2 à prévu l'ordre SET CONSTRAINTS :

Cet ordre permet de changer la déférabilité d'une contrainte à la volée.IMPORTANT

la déférabilité d'une contrainte est le seul élément du langage capable de créer un "auto rollback"

certains SGBDR valident les contraintes pour chaque ligne ce qui n'est pas conforme à la norme SQL 2 pour laquelle toute requête est une transaction....

Pour résoudre notre problème, nous pouvons gérer la déférabilité dans la construction de la table : Exemple 86 :

Mais nous pouvons aussi piloter cette déférabilité dans un script transactionné :Exemple 87 :

Au fait, dans le principe n'importe quelle contrainte (de colonne ou de table) peut disposer d'une clause de déférabilité. Pas seulement les intégrité référentielle !

7.5 Contraintes horizontales ou verticales ?Comme nous l'avons vu, les contraintes peuvent être définie PRIMARY KEY, UNIQUE, CHECK et FOREIGN KEY peuvent être définies indifférement en contraintes de colonnes comme en contrainte de ligne. Ainsi une clef portant sur une seule colonne peut parfaitement être définie en tant que contrainte de table.Ainsi les deux ordres suivants :Exemple 88 :

Sont strictement équivalent, même si l'un est plus verbeux.Mais il y a un net avantage à utiliser systématiquemen des contraintes horizontales.Simplement parce que :

elle sont plus lisible

elle sont nommées

elle peuvent facilement être supprimées et réinsérées

Essayez donc dans le premier cas de l'exemple 88 de faire porter la clef primaire sur la colonne CLI_NOM plutôt que CLI_ID... Pour le deuxième cas, c'est bien plus simple :Exemple 89 :

7.6 Alter et Drop Les ordres ALTER et DROP sont les ordres de modification (ALTER pour altération) et suppression (DROP).L'ordre ALTER peut porter sur un domaine, une assertion, une table, une vue, etc...L'ordre ALTER sur une table permet de :

supprimer une colonne

supprimer une contrainte

ajouter une colonne

ajouter une contrainte

ajouter une contrainte de ligne DEFAULT

Il ne permet pas de :

changer le nom d'une colonne

changer le type d'une colonne

ajouter une contrainte de ligne NULL / NOT NULL

Syntaxe de l'ordre ALTER sur table :

| ALTER nom_colonne { SET DEFAULT valeur_défaut | DROP DEFAULT }

définition_contrainte_ligne

L'option CASCADE / RESTRICT permet de gérer l'intégrité de référence de la colonne ou la contrainte.Si RESTRICT, alors tout objet dépendant de cette colonne ou de cette contrainte provoquera l'annulation de l'opération de suppression.Si CASCADE, alors tous les objets dépendant de cette colonne ou contrainte seront supprimés.Exemple 90 :

ATTENTION : ne pas tenter de rajouter une colonne avec l'attribut NOT NULL lorsque la table contient déjà des lignes. Pour cette opération, veuillez procéder en plusieurs étapes dans un script transactionné.DROP est l'ordre de suppression. Sa syntaxe est onne peut plus simple :

7.6.1 Changer le nom ou le type d'une colonneCe cas n'est pas géré par un ordre simple de SQL. En effet cette modification est trop risquée pour être standardisée. Quid des données contenue dans la colonne au passage de CHAR en FLOAT ? Quid des références de cette colonne dans des vues, des contraintes, des triggers si l'on en change le nom ?Mais il est possible de contourner le problème en réalisant un script transactionné. Certains SGBDR proposent un ordre ALTER étendu ou une procédure stockée (par exemple sp_rename de MS SQL Server).Avant de lancer un tel script il convient de s'assurer que le colonne ne fait l'objet d'aucune référence interne (contraintes de table par exemple) ou externe (vue, triggers...). Si c'est le cas, il faut impérativement modifier, désactiver ou supprimer ces éléments avant la modification de la colonne.Voici les différentes étapes du script à mettre en oeuvre :

créer une colonne temporaire de même nom et même type (ALTER TABLE ADD...

alimenter la colonne temporaire avec les valeurs de l'actuelle (UPDATE ... supprimer l'actuelle colonne (ALTER TABLE DROP... créer une nouvelle colonne avec le nouveau nom et/ou le nouveau type

(ALTER TABLE ADD... alimenter la nouvelle colonne avec les données de la colonne temporaire

(UPDATE ... supprimer la colonne temporaire (ALTER TABLE DROP ...

Exemple 91 - modification d'une colonne CHAR(6) contenant une date courte format FR en date SQL :

condition de départ

on aura noté que le pivot de date pour changement de siècle aura été géré dans le dernier upd

7.6.2 Ajouter ou supprimer la contrainte NULL ou NOT NULLLes étapes du script différent très peu. Voici un exemple :Exemple 92 - modification d'une colonne IMP_NOM en plaçant la contrainte NOT NULL :

condition de départ

on aura noté qu'afin d'éviter un rejet massif de notre script, on place un nom constitué d'une chaîne

vide grace à l'opérateur coalesce, dans le premier update

8 Les vues Les vues de la norme SQL 2 ne sont autre que des requêtes instanciées.Elles sont nécessaires pour gérer finement les privilèges. Elles sont utiles pour masquer la complexité de certains modèles relationnel.Voici la syntaxe SQL 2 pour définir une vue :

requête_select

Exemple 93 - vue simplifiant un modèle

permet de stocker l'évolution d'un tarif, sachant que celui

pour un produit donné (PRD_ID) qu'à partir de la date TRF_DATE

pour des raisons de commodité d'interrogation des données, on voudrait

faire apparaître l'intervalle de validité du tarif plutôt que la date d'application

la vue suivante répond à cette attente

Une vue peut être utilisée comme une table dans toute requête de type SELECT. Mais à la différence des tables, une vue peut être mise à jour (INSERT, UPDATE, DELETE) que si elle obéit à un certain nombre de conditions :

ne porter que sur une table (pas de jointure) ne pas contenir de dédoublonnage (pas de mot clef DISTINCT) si la table n'a

pas de clef contenir la clef de la table si la table en a une

ne pas transformer les données (pas de concaténation, addition de colonne, calcul d'agrégat...)

ne pas contenir de clause GROUP BY ou HAVING

ne pas contenir de sous requête

répondre au filtre WHERE si la clause WITH CHECK OPTIONS est spécifié lors de la création de la vue

Bien évidemment une vue peut porter sur une autre vue et pour que la nouvelle vue construite à partir d'une autre vue puisse être modifié, il faut que les deux vues répondent aussi à ces critères. En fait c'est plus simple qu'il n'y parait : il suffit que le SGBDR puisse retrouver trace de la ligne dans la table et de chaque valeur de chaque colonne.Exemple 94 - vue restreignant l'accès aux colonnes

permet de stocker les employés de l'entreprise

pour le syndicat, on pourra définir la vue suivante :

elle ne peut être mise à jour car la clef ne s'y trouve pas

pour le carnet d'adresse on pourra définir

elle ne peut être mise à jour à cause des transformation de données (concaté

pour le service comptable, on pourra définir la vue suivante :

rra être mis à jour uniquement pour les salariés de type 'ETAM'

La clause WITH CHECK OPTION implique que si la vue peut être mise à jour, alors les valeurs modifiées insérées ou supprimées doivent répondre à la validation de la clause WHERE comme s'il s'agissait d'une contrainte.Par exemple dans le cadre de la vue pour le service comptable, il n'est pas possible de faire :

9 Les informations de schéma Toute base de données, tout SGBDR bien constitué permet de savoir ce qu'il contient. Ce sont les méta données ou le dictionnaire des données (souvent appelées à tort "tables systèmes") que la norme apelle "information de schéma".SQL 2 précise 23 vues permettant de connaitre les éléments constituant l'architecture de données du CATALOG du SGBDR. En voici la liste :

Élément du liste des langages supportés au niveau

Élément du

Élements d'une

jeux de caractères de la base

liste des collations (schémas d'équivalence de

caractères) de la base

liste des "translations" (schémas de

remplacement de caractères) de la base

Éléments d'une

liste des intégrités référentielles de la base

liste des contraintes de validité de la base

des colonnes définissant les clefs (primaire

ou étrangère) de la base

liste des colonnes définissant les contraintes de

liste des tables utilisée par les contraintes de la

Éléments d'une

Éléments d'un onnes basées sur les domaines de

Privilèges

liste des privilèges des tables de la base

liste des privilèges de colonnes de table de la

liste des privilèges des autres objets de la base

En sus de ces vues, la norme SQL impose une table composé d'une unique ligne et d'une seule colonne contenant le nom du CATALOG. Cette table se nomme INFORMATION_SCHEMA_CATALOG_NAME.Pour interroger ces vues, il faut en préciser l'origine qui est par défaut "INFORMATION_SCHEMA".Exemple 95 :

Cet exemple liste les clef étrangères et les colonnes associées de la table T_EMPLOYE_EMP.ATTENTION : tous les SGBDR ne proposent pas ces vues standards pour accèder aux méta données. Voici quelques éléments pour certains SGBDR :

DB2 : (SYSCAT.xxx) SYSCAT.TABLES, SYSCAT.SCHEMATA, SYSCAT.REFERENCES, SYSCAT.KEYCOLUSE...

ORACLE : USER_CATALOG, USER_TABLES, ALL_TABLES, USER_SYNONYMS...

INFORMIX : SYSTABLES, SYSREFERENCES, SYSSYNONYMS... SYBASE : SYSDATABASES, SYSOBJETCS, SYSKEYS... MS SQL SERVER : SYSDATABASES, SYSOBJETCS, SYSFOREIGNKEYS,

SYSREFERENCES... INTERBASE : RDB$RELATIONS, RDB$FIELDS, RDB$DATABASE...

Dans tous les cas, si votre SGBDR supporte les vues standard de SQL 2 il vaut mieux les utiliser. Dans le cas contraire, les créer semble un moindre mal ! En effet, les vues sont garanties par la norme tandis que les tables "systèmes" peuvent évoluer d'une version à l'autre du SGBDR... NOTA : tous les objets d'une base ne sont pas toujours tous accessibles par les vues normalisées. Voici un exemple de requête interrogeant directement les tables systèmes d'une base MS SQL Server à la recherche des objets "tiggers", "fonctions", "procédures stockées" et "vues" créées par l'utilisateur :Exemple 96 :

les procédures stockées

10 Les indexContrairement à une idée reçue, les index ne font nullement partie du SQL. Ce sont en revanche des éléments indispensables à une exploitation performante de base de données. En effet un index permet de spécifier au SGBDR qu'il convient de créer une structure de données adéquate afin de stocker les données dans un ordre précis. Par conséquent les recherches et en particuliers les comparaisons, notamment pour les jointures, sont notablement accélérées. Dans le principe le gain de temps espéré est quadratique. Par exemple si une recherche sur une colonne dépourvue d'index met 144 secondes, avec un index cette même recherche sera supposée mettre 12 seconde (racine carré de 144) !Différents types d'index sont généralement proposés. Voici quelques exemples de techniques d'indexation :

index en cluster : l'ordre des données répond à un ordre physique d'insertion, convient particulièrement pour les clefs numériques auto incrémentées (dans ce cas une table ne peut recevoir qu'un seul index de ce type).

index en arbre équilibré : convient pour la plupart des types de données. index en clef de hachage : convient pour des colonnes dont la dispersion est

très importante. Un algorithme de hachage est mis en place (il s'agit en général d'une transformation injective)

index bitmap : convient pour des colonnes à faible dispersion (ideal pour des colonnes booléennes)

En règle général les fabriquants de SGBDR proposent un mécanisme de création d'index dont la syntaxe est proche des ordres basiques du SQL. C'est en général l'ordre CREATE INDEX. Voici la syntaxe d'un tel ordre pour MS SQL Server :

La plupart du temps lorsque vous créez une contrainte de clef primaire, étrangère ou une contrainte d'unicité, le SGBDR implante automatiquement un index pour assurer la mécanisme de contrainte avec des performances correctes. En effet une contrainte d'unicité est facilité si un tri sur les données de la colonne peut être activé très rapidement.CONSEIL : pour une table donnée, il convient d'indexer dans l'ordre :

les colonnes composant la clef les colonnes composant les clefs étrangères

les colonnes composant les contraintes d'unicité

les colonnes dotées de contraintes de validité

les colonnes fréquemment mises en relation, indépendemenent des jointures naturelles

les colonnes les plus sollicitées par les recherches.

Dans la mesure du possible on placera des index à ordre descendant pour les colonnes de type DATE, TIME et DATETIME.


Mise à jour des

données


noms normatifs

NON OUI NON

CONNEXION NON NON

SESSION NON NON

CATALOG NON OUI

SCHEMA OUI OUI

jeu de caractères

OUI OUI

collations OUI OUI (v2000)

translation NON NON

CHAR NON OUI

VARCHAR OUI OUI

NCHAR NON OUI

NVARCHAR NON OUI

BIT NON NON (1)

VARBINARY OUI OUI

INT OUI OUI

SMALLINT OUI OUI

FLOAT OUI OUI

DOUBLE PRECISION

NON OUI

DECIMAL OUI (BCD) OUI

NUMERIC NON OUI

TIMESTAMP OUI OUI

TIME OUI

DATE OUI

INTERVAL non

BOOLEAN OUI NON

BLOB OUI OUI

CLOB / NCLOB

OUI / non OUI

ARRAY non NON

ROW non NON

REF non NON

DOMAIN non NON (2)

ASSERTION non NON (2)

(1) le BIT SQL est limité à 1 BIT valant 0

(2) non, mais mécanisme similaire

Conclusion :

Documents

Le SQL de a à Z (Livre)