sql - sous ensemble lmd opérations de...

ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du Travail

DIRECTION RECHERCHE ET INGENIERIE DE FORMATIONSECTEUR NTIC

SQL - Sous Ensemble LMD Opérations de Base

ROYAUME DU MAROC




Sommaire1. Introduction_____________________________________________________________2

1.1. Bref Historique____________________________________________________________2

1.2. Définition_________________________________________________________________2

2. Opération de sélection_____________________________________________________32.1. Requêtes de type détail sur une seule table______________________________________3

2.1.1. Utilisation du mot clé DISTINCT__________________________________________________4

2.2. Fonctions de groupe_________________________________________________________52.2.1. Quelques exemples pour comprendre :______________________________________________6

2.3. Les opérateurs_____________________________________________________________82.3.1. Opérateurs arithmétiques_________________________________________________________82.3.2. Opérateurs de comparaison_______________________________________________________92.3.3. Opérateurs logiques_____________________________________________________________9

3. Eléments de dialectes_____________________________________________________13

OFPPT @ Document Millésime Pagedocument.doc octobre 12 1 - 85


1.IntroductionCe support a pour but de vous présenter le sous ensemble du langage SQL(STRUCTERED QUERY LANGAGE)langage de requête structurées utilisé pour manipuler des données au sein d’un Système de Gestion de Bases de Données Relationnel.On utilisera le terme de Langage de Manipulation de Données (LMD) pour qualifier ce sous-ensemble (de DML Data Management Language en anglais).Avant d’aborder le langage SQL proprement dit, prenez connaissance du document sur l’algèbre relationnel qui vous permettra de mieux comprendre les notions sous-jacentes aux opérations traitées dans ce document.

Seront traités dans ce document :

Eléments syntaxiques relatifs à SQL Exemples de manipulation de données au travers des opérations de

Sélection, Suppression, Mise à jour et Ajout. Différences syntaxiques au sein des différents dialectes SQL (document

joint)

1.1. Bref HistoriqueS.Q.L. est un langage structuré qui permet :

de créer la structure d’une base de données relationnelle (base, tables, …) d’interroger et de modifier les données contenues dans ce type de base de

données.

S.Q.L. signifie Structured Query Language.Il est issu de SEQUEL : Structured English Query Language.C’est le premier langage pour les S.G.B.D. Relationnels. Il a été développé par IBM en 1970 pour système R, son 1er SGBDR.

S.Q.L. a été reconnu par l’ANSI (Association de Normalisation des Systèmes d’Information) puis s’est imposé comme norme. Il n’existe pas de S.G.B.D. Relationnel sans S.Q.L..

Malheureusement, malgré la norme S.Q.L., il existe un ensemble de dialectes. Les différences entre ces différents dialectes sont souvent minimes et tous respectent un minimum commun.Il existe aujourd’hui une troisième version de SQL, SQL 3, qui proposera de nouveaux éléments syntaxiques mieux à même de prendre en compte les évolutions technologiques liées au développement de systèmes à objets.

1.2. DéfinitionS.Q.L. est un langage relationnel qui permet d’effectuer les tâches suivantes :

Définition et modification de la structure de la base de données Interrogation et modification non procédurale (c’est à dire interactive) de

la base de données Contrôle de sécurité et d’intégrité de la base Sauvegarde et restauration des bases


SQL - Sous Ensemble LMD Opérations de BaseS.Q.L. est un langage interactif, mais il peut aussi être intégré dans un langage de programmation pour le développement d’applications.

S.Q.L. est un standard qui permet d’assurer une portabilité importante des bases de données relationnelles.

2.Opération de sélection L’instruction SELECT permet d’extraire des données et de les présenter triées et/ou regroupées suivant certains critères.Les enregistrements doivent vérifier certains critères exprimés dans des expressions conditionnelles.

Syntaxe de l’instruction SELECT présentant les différentes clauses utilisables dans les sélections. Certaines clauses sont facultatives, mais il est important de respecter l’ordre d’apparition des clauses dans la requête.

SELECT liste des attributs à afficher[DISTINCT] mot clé facultatif (élimination des doublons)FROM liste des noms de tablesWHERE condition sur les attributsGROUP BY nom de l’attribut de regroupementHAVING condition de regroupementORDER BY critère de tri

2.1. Requêtes de type détail sur une seule table

Ensemble des informations concernant les vols partant après 16 h.

SELECT *FROM VolWHERE HeureDepart > 16

Figure 1: Ensemble des informations concernant les vols partant après 16 h.

En fait, nous n’avons pas besoin de tous les attributs de la table des vols.Certaines informations, comme le N° d’avion ou le pilote, ne nous intéressent pas dans l’élaboration de la réponse au client voulant connaître les horaires.Par contre, celui-ci nous précise qu’il s’agit d’un vol au départ de Toulouse et à destination de Grenoble.


SQL - Sous Ensemble LMD Opérations de BaseNous alors donc restreindre les attributs retenus dans l’opération SELECT au moyen d’une Projection et compléter la clause Where.

Rappel : La projection est une opération qui consiste à ne retenir que certains attributs dans une table

SELECT vol#,heuredepart,heurearrivee FROM vol WHERE heuredepart > 16 AND villedepart LIKE 'Toulou%'AND villearrivee = 'Grenoble'

Nous avons introduit dans la clause WHERE, une opération logique AND et un opérateur sur chaîne LIKE.

Figure 2 : Vols au départ de Toulouse à destination de Grenoble après 16 h

2.1.1. Utilisation du mot clé DISTINCTLe résultat d’un SELECT étant un ensemble, il peut y avoir des doublons. Le mot clé DISTINCT permet de préciser que l’on ne veut qu’un seul exemplaire des lignes retenues dans la sélection.

Ensemble des types d’avions dont la capacité est supérieure à 250 passagers.

SELECT Marque,TypeAvion, CapaciteFROM AVIONWHERE Capacite > 250

Figure 3 : Types d'avions dont la capacité est supérieure à 250



Nous ajoutons le mot clé DISTINCT à la sélection et ordonnons les données par capacités décroissantes :

SELECT DISTINCT Marque,TypeAvion,CapaciteFROM AVIONWHERE Capacite > 250ORDER BY Capacite DESC

Remarques : la clause ORDER BY doit toujours être exprimée en dernier. Elle peut contenir plusieurs facteurs séparés par une virgule et l’ordre peut être croissant ASC (par défaut) ou décroissant DESC.DISTINCT concerne la totalité des valeurs des attributs : ici, Marque, TypeAvion et Capacite.

Figure 4 : Sélection ordonnée de lignes distinctes

2.2. Fonctions de groupe Les fonctions d'agrégation effectuent un calcul sur un ensemble de valeurs et retournent une valeur unique.

À l'exception de COUNT, les fonctions d'agrégation ignorent les valeurs NULL. Les fonctions d'agrégation sont souvent utilisées avec la clause GROUP BY de l'instruction SELECT.

SUM somme des valeurs de l’attribut MIN minimum des valeurs de l’attribut MAX maximum des valeurs de l’attribut AVG moyenne des valeurs COUNT nombre d’enregistrements sélectionnés



Nous avons à notre disposition des clauses spécifiques à la construction de requêtes récapitulatives. Nous avons ainsi les clauses :

GROUP BY permet d’exprimer sur quels attributs sont agrégés les données.HAVING permet de définir une condition sur des valeurs de regroupement.

2.2.1. Quelques exemples pour comprendre :Nombre d’avions dans la table AVION

SELECT COUNT(AV#)FROM AVION

Le résultat est 16

On peut donner un nom de colonne au résultat de l’expression COUNT en ayant recours au mécanisme des alias : NomColonne AS NomAlias.

SELECT COUNT(AV#) as "Nombre Avions"FROM AVION

On peut aussi vouloir comptabiliser le nombre d’avions par marque.La clause GROUP BY permet alors de définir les conditions de regroupement des calculs récapitulatifs :

SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONGROUP BY Marque

Il serait éminemment préférable de faire figurer le nom de la marque dans le résultat et de trier les valeurs récapitulatives :

SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONGROUP BY MarqueORDER BY "Nombre Avions" DESC

Qui peut s’écrire aussi :

SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONGROUP BY Marque


SQL - Sous Ensemble LMD Opérations de BaseORDER BY COUNT(AV#) DESC

Je peux aussi ne pas vouloir conserver dans mon résultat les marques dont le nombre d’avions est inférieur à 3. J’introduis alors dans ma requête une clause HAVING qui exprime une condition sur une opération de regroupement.

SELECT Marque, COUNT (AV#) as "Nombre Avions"FROM AVIONGROUP BY MarqueHAVING COUNT (AV#) > 2 -- on ne peut pas utiliser "Nombre Avions"ORDER BY COUNT (AV#) DESC

Attention à ne pas confondre la clause HAVING et la clause WHERE qui filtre les lignes retenues pour les calculs. Ainsi, si je souhaite ne pas retenir les avions localisés à Toulouse, je constate que les résultats diffèrent :

SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONWHERE localisation <> 'Toulouse' -- Expression de différentGROUP BY MarqueHAVING COUNT(AV#) > 2 ORDER BY COUNT(AV#) DESC



Capacités minimum et maximum des Boeing.

SELECT MIN(Capacite) as "Capacite Mini Boeing", MAX(Capacite) as "Capacite Maxi Boeing"FROM AVIONWHERE Marque = 'BOEING'

2.3. Les opérateursUn opérateur est un symbole spécifiant une action exécutée sur une ou plusieurs expressions. Nous trouvons en SQL, différentes catégories d’opérateurs. Je vous présente ici les principaux utilisables dans les requêtes de sélection.

Opérateurs arithmétiques Opérateurs de comparaison Opérateurs logiques Opérateur de traitement de chaînes ;

2.3.1. Opérateurs arithmétiquesUtilisables sur toute expression de 2 valeurs numériques et sur les valeurs de type DateTime ou SmallDateTime (+ et – uniquement)

Opérateur Description+ (Ajouter) Addition- (Soustraire) Soustraction* (Multiplication) Multiplication/ (Division) Division% (Modulo) Retourne le reste entier d'une

division. 12 % 5 = 2.

Exemple : Total du CA Net généré par commande

SELECT OrderID, sum(Unitprice * (1-Discount)*Quantity) as "Total Commande"FROM [Order Details]GROUP BY OrderID



Figure 5 : Somme du CA Net par Commande

Remarques : Attention à la priorité des opérateurs : le plus simple pour éviter les problèmes de mise au point est de réaliser les opérations entre parenthèses : les opérations internes (à l’intérieur des parenthèses) sont évaluées en premier.Les noms composés des tables doivent être exprimés entre crochets [ ].

2.3.2. Opérateurs de comparaisonLes opérateurs de comparaison testent si deux expressions sont identiques.Ils peuvent s'utiliser sur toutes les expressions composées de données structurées, donc à l'exception des expressions de type de données text, ntext ou image. Nous reviendrons sur ces types particuliers ultérieurement.

Opérateur Description= (Égal à) Égal à> (Supérieur à) Supérieur à< (Inférieur à) Inférieur à>= (Supérieur ou égal à) Supérieur ou égal à<= (Inférieur ou égal à) Inférieur ou égal à<> (Différent de) Différent de

2.3.3. Opérateurs logiquesLes opérateurs logiques testent la valeur logique d'une condition. Les opérateurs logiques, comme les opérateurs de comparaison, retournent un type de données booléen de valeur TRUE ou FALSE.

Un certains nombre d’entre eux (signalés par un * dans le tableau) sont utilisés pour comparer une valeur scalaire (unique) avec une sous requête. Nous en illustrerons l’utilisation dans le chapitre consacré aux jointures ensemblistes.



Opérateur DescriptionALL (*) TRUE si tous les éléments d'un jeu de

comparaisons sont TRUE.AND TRUE les deux expressions booléennes sont

TRUE.ANY (*) TRUE si n'importe quel élément d'un jeu de

comparaison est TRUE.BETWEEN TRUE si l'opérande est situé dans une certaine

plage.EXISTS (*) TRUE si une sous-requête contient des lignes.IN (*) TRUE si l'opérande est égal à un élément

d'une liste d'expressions.LIKE TRUE si l'opérande correspond à un modèle.NOT Inverse la valeur de tout autre opérateur

booléen.OR TRUE si l'une ou l'autre expression booléenne

est TRUE.SOME (*) TRUE si certains éléments d'un jeu de

comparaisons sont TRUE.* Utilisés avec des sous-requêtes

Exemple : Liste des vols au départ de Toulouse et a destination de Paris entre 12 et 15 heures.

SELECT vol#, villedepart, villearrivee, heuredepart, heurearriveeFROM VolWHERE Heuredepart BETWEEN 12 AND 15 AND villedepart LIKE 'TOULO%'AND villearrivee LIKE 'PAR%'

Figure 6 : Illustration utilisation des opérateurs logiques

Syntaxe de l’opérateur Between : Between valeur de début valeur de fin.Les bornes sont retenues dans la sélection comme le prouve la requête ci-dessus.



Syntaxe de l’opérateur Like

Caractère générique

Description

% Toute chaîne de zéro caractère ou plus_ N'importe quel caractère à cet emplacement[ ] Tout caractère de l'intervalle ([a-f]) ou de l'ensemble

spécifié ([abcdef])[^] Tout caractère en dehors de l'intervalle ([^a-f]) ou de

l'ensemble spécifié (^abcdef]). ^ représente le NOT

L’opérateur LIKE est extrêmement puissant pour réaliser des recherches sur des chaînes avec des valeurs approximatives.

Quelques exemples illustrant son utilisation.

A noter , l’utilisation :des caractères - - pour introduire une ligne de commentairesdes caractère /* …. */ pour un bloc de commentaires

-- Liste des noms de pilotesSELECT nomFROM Pilote

-- Liste des Pilotes dont le nom contient la chaîne el */ SELECT nomFROM PiloteWhere nom LIKE '%el%'



-- Liste des Pilotes dont le 3ème caractère du nom est un r*/ SELECT nomFROM PiloteWhere nom LIKE '__r%'

-- Liste des Pilotes dont le nom comporte les caractères u,c,ou lSELECT nomFROM PiloteWhere nom LIKE '%[cu]%'

-- Liste des Pilotes dont le nom commence par le caractère m ou lSELECT nomFROM PiloteWhere nom LIKE '[ml]%'

/* Liste des Pilotes dont le nom commence par le caractère m ou let se termine par l */SELECT nomFROM PiloteWhere nom LIKE '[ml]%l'

Les opérateurs SOME, ANY et ALL seront vus dans le chapitre suivant dans le cadre des opérations sur plusieurs tables.



3.Eléments de dialectesSQL est une norme. Mais comme toute norme, elle n’est pas toujours strictement respectée. La syntaxe des différents SGBDR s’éloigne parfois de cette dernière ou apporte des fonctions non implémentées dans la norme.

Le tableau accompagnant ce document vous permettra d’avoir quelques informations sur l’existence ou non de différentes fonctions par type de SGBDR.

Par Frédéric BROUARD , 2002

Légende :

O : Oui N : Non X : Existe mais syntaxe hors norme ! : Même nom mais fonction différente

Agrégation statistique

FonctionDescription Norme SQL Paradox Access MySQL Post

GreSQLSQL

ServerOracle Inter

Base

AVG Moyenne O O O O O O O OCOUNT Nombre O O X O O O O OMAX Maximum O O O O O O O OMIN Minimum O O O O O O O OSUM Total O O O O O O O O

Fonction "système"

FonctionDescription Norme

SQLParadox Access MySQL Post

GreSQLSQL

ServerOracle Inter

Base


SQL - Sous Ensemble LMD Opérations de BaseCURRENT_DATE Date courante O N N O O N N OCURRENT_TIME Heure courante O N N O O N N OCURRENT_TIMESTAMP Date et heure courante O N N O O O N OCURRENT_USER Utilisateur courant O N N N O O N NSESSION_USER Utilisateur autorisé O N N X O O N NSYSTEM_USER Utilisateur système O N N X O O N NCURDATE Date du jour N N N O N N N NCURTIME Heure courante N N N O N N N NDATABASE Nom de la bases de

données couranteN N N O N O O N

GETDATE Heure et date courante N N N N N O N NNOW Heure et date courante N O O O O O O NSYSDATE Date et/ou heure

couranteN N N O N N O N

TODAY Date du jour N O N N N N N NUSER Utilisateur courant N N N O N O O OVERSION Version du SGBDR N N N O O N N N



Fonctions générales



GreSQLSQL

ServerOracle Inter

Base

CAST Transtypage O O N O O O O OCOALESCE Valeur non NULL O N N O O O N NNULLIF Valeur NULL O N N O O O N NOCTET_LENGTH Longueur en octet O N N O O N O NDATALENGTH Longueur N N N N N O N NDECODE Fonction conditionnelle N N N N N N O NGREATEST Plus grande valeur N N N O N N O NIFNULL Valeur non NULL N N N O O O N NLEAST Plus petite valeur N N N N O N O NLENGTH Longueur N N O O O O O NNVL Valeur non NULL N N N N N N O NTO_CHAR Conversion de données

en chaîneN N N N N N O N

TO_DATE Conversion en date N N N N O N O NTO_NUMBER Conversion en nombre N N N N N N O N



Fonctions de chaînes de caractèresFonction Description Norme


GreSQLSQL

ServerOracle Inter

Base|| Concaténation O O N X O N O OLEN OCHAR_LENGTH Longueur d'une chaîne O N N X O N N NCHARACTER_LENGTH Longueur d'une chaîne O N N O O O N NCOLLATE Substitution à une

séquence de caractèresO N N N N N N O

CONCATENATE Concaténation O N N N N N N NCONVERT Conversion de format

de caractèresO N N N N ! O O

LIKE (prédicat) Comparaison partielle O O X O O O O OLOWER Mise en minuscule O O N O O O O NPOSITION Position d'une chaîne

dans une sous chaîneO N N O O N N N

SUBSTRING Extraction d'une sous chaîne

O O N O O O N N

TRANSLATE Conversion de jeu de caractères

O N N N X N X N

TRIM Suppression des caractères inutiles

O O N O O N O N

UPPER Mise en majuscule O O N O O O O OCHAR Conversion de code en

caractère ASCIIN N O O N O N N

CHAR_OCTET_LENGTH Longueur d'une chaîne en octets

N N N N N O N N

CHARACTER_MAXIMUM_LENGTH Longueur maximum d'une chaîne

N N N N N O N N

CHARACTER_OCTET_LENGTH Longueur d'une chaîne N N N N N O N NCONCAT Concaténation N N O O N O O NILIKE LIKE insensible à la

casseN N N N O N N N



Fonctions de chaînes de caractères (Suite)



GreSQLSQL

ServerOracle Inter

Base

INITCAP Initiales en majuscule N N N N O N O NINSTR Position d'une chaîne

dans une autreN N O O N N O N

LCASE Mise en minuscule N N O O N O O NLOCATE Position d'une chaîne

dans une autreN O O O N O O N

LPAD Remplissage à gauche N N N O O N O NLTRIM TRIM à gauche N O O O O O O NNCHAR Conversion de code en

caractère UNICODEN N N N N O N N

PATINDEX Position d'un motif dans une chaîne

N N N N N O N N

REPLACE Remplacement de caractères

N N N O N O O N

REVERSE Renversement N N N O N O O NRPAD Remplissage à droite N N N O O N O NRTRIM TRIM à droite N N O O O O O NSOUNDEX Code de consonance N N N O O O O NSPACE Génération d'espaces N N O O N O O NUCASE Mise en majuscule N N O O N O O N



Fonctions de chaînes de bits



GreSQLSQL

ServerOracle Inter

Base

BIT_LENGTH Longueur en bit O N N N N N N N& "et" pour bit logique N N ? ? ? O ? ?| "ou" pour bit logique N N ? ? ? O ? ?^ "ou" exclusif pour bit

logiqueN N ? ? ? O ? ?



Fonction numériques



GreSQLSQL

ServerOracle Inter

Base

% Modulo N N N O O O N N+ - * / ( ) Opérateurs et

parenthésageO O O O O O O O

ABS Valeur absolue N N O O O O O NACOS Angle de cosinus N N N O O O O NASCII Conversion de

caractère en code ASCII

N N O O O O O N

ASIN Angle de sinus N N N O O O O NATAN Angle de tangente N N N O O O O NCEILING Valeur approchée

hauteN N O O N O N N

COS Cosinus N N O O O O O NCOT Cotangente N N O O O O N NEXP Exponentielle N N O O O O O NFLOOR Valeur approchée

basseN N O O O O O N

LN Logarithme népérien N N N N N N O NLOG Logarithme népérien N N O O N O O NLOG(n,m) Logarithme en base n

de mN N N N O N O N

LOG10 Logarithme décimal N N N O N O O NMOD Modulo N N O O O O O NPI Pi N N N O O O O NPOWER Elévation à la

puissanceN N O O N O O N

RAND Valeur aléatoire N N O O N O N NROUND Arrondi N N O O O O N N


SQL - Sous Ensemble LMD Opérations de BaseFonction numériques (Suite)



GreSQLSQL

ServerOracle Inter

Base

SIGN Signe N N O O O O O NSIN Sinus N N O O O O O NSQRT Racine carrée N N O O O O N NTAN Tangente N N O O O O O NTRUNC Troncature N N N N N N O NTRUNCATE Troncature N N O O O O NUNICODE Conversion de

caractère en code UNICODE

N N N N N O ? N



Fonctions temporelles



GreSQLSQL

ServerOracle Inter

Base

EXTRACT Partie de date O O N O O N O NINTERVAL (opérations sur) Durée O N N N N N O NOVERLAPS (prédicat) Recouvrement de

périodeO N N N O N N N

ADDDATE Ajout d'intervalle à une date

N N N O N N N N

AGE Age N N N N O N N NDATE_ADD Ajout d'intervalle à

une dateN N N O N N N N

DATE_FORMAT Formatage de date N N N O N N N NDATE_PART Partie de date N N N N O N N NDATE_SUB Retrait d'intervalle à


DATEADD Ajout de date N N N N N O N NDATEDIFF Retrait de date N N N N N O N NDATENAME Nom d'une partie de

dateN N N N N O N N

DATEPART Partie de date N N N N N O N NDAY Jour d'une date N N N N N O N NDAYNAME Nom du mois N N O O N O N NDAYOFMONTH Jour du mois N N N O N N N NDAYOFWEEK Jour de la semaine N N N O N N N NDAYOFYEAR Jour dans l'année N N N O N N N NHOUR Extraction de l'heure N N O O N O N NLAST_DAY Dernier jour du mois N N N N N N O NMINUTE N N O O N O N NMONTH Mois d'une date N N O O N O O N


SQL - Sous Ensemble LMD Opérations de BaseFonctions temporelles



GreSQLSQL

ServerOracle Inter

Base

MONTH_BETWEEN Nombre de mois N N NMONTHNAME Nom du mois N N O O N O N NNEXT_DAY Prochain premier jour

de la semaineN N N N N N O N

SECOND Extrait les secondes N N O O N O N NSUBDATE Retrait d'intervalle à


WEEK Numéro de la semaine N N O O N O O NYEAR Année d'une date N N O O N O O N



Prédicat, opérateurs et structures diverses



GreSQLSQL

ServerOracle Inter

Base

CASE Structure conditionnelle

O N N O O O X N

IS [NOT] TRUE Vrai O N N N N N N NIS [NOT] FALSE Faux O N N N N N N NIS [NOT] UNKNOWN Inconnu O N N N N N N NIS [NOT] NULL NULL O O X O O O O OINNER JOIN Jointure interne O O O O O O N OLEFT, RIGHT, FULL OUTER JOIN

Jointure externe O O O O O O N O

NATURAL JOIN Jointure naturelle O N N O O N N NUNION JOIN Jointure d'union O N N N N N N NCROSS JOIN Jointure croisée O N N O O O N NLEFT, RIGHT, FULL OUTER NATURAL JOIN

Jointure naturelle externe

O N N X O N N N

INTERSECT Intersection (ensemble)

O ? N N O N X N

UNION Union (ensemble) O ? O N O O O OEXCEPT Différence (ensemble) O ? N N O N N N[NOT] IN Liste O O O X O O O O[NOT] BETWEEN Fourchette O O O O O O O[NOT] EXISTS Existence O ? ? N O O O O






GreSQLSQL

ServerOracle Inter

Base

ALL Comparaison à toutes les valeurs d'un ensemble

O ? O N O O O O

ANY / SOME Comparaison à au moins une valeur de l'ensemble

O ? O N O O O O

UNIQUE Existance sans doublons

O N N N N N N N

MATCH UNIQUE Correspondance O N N N N N N Nrow value construteur Construteur de ligne

valuéesO N N N N N O N

MINUS Différence (ensemble) N N N N O O O NLIMITE nombre de ligne

retournéeN N TOP LIMIT LIMIT TOP N ROWS

identifiant de ligne N N N _rowid oid N rowid ?



Sous requêtesNorme SQL

Paradox Access MySQL Post GreSQL

SQL Server

Oracle Inter Base

Imbriquées O O O N O O O OCorrélées O O O N O O O ODans la clause SELECT O X O N O O O ODans la clause FROM O N N N O O O NDans la clause WHERE O O O N O O O ODans la clause HAVING O O N N O O O O


ROYAUME DU MAROC


Pour approfondir le sujet….Vous trouverez des éléments complémentaires sur le site de Microsoft qui comporte un portail consacré à SQL Version 2005 et les premiers éléments sur la prochaine version à venir 2008

Consultez les publications de Frédéric Brouard qui reste un expert de référence en ce qui concerne SQL et les bases de données relationnelles.

Sources de référenceVincent Bost – formateur AFPA BriveFrédéric Brouard


ROYAUME DU MAROC





Sommaire

1. Introduction_____________________________________________________________21.1. Bref Historique____________________________________________________________2

1.2. Définition_________________________________________________________________2

2. Opération de sélection_____________________________________________________32.1. Requêtes de type détail sur une seule table______________________________________3

2.1.1. Utilisation du mot clé DISTINCT__________________________________________________4

2.2. Fonctions de groupe_________________________________________________________52.2.1. Quelques exemples pour comprendre :______________________________________________6

2.3. Les opérateurs_____________________________________________________________82.3.1. Opérateurs arithmétiques_________________________________________________________82.3.2. Opérateurs de comparaison_______________________________________________________92.3.3. Opérateurs logiques_____________________________________________________________9

3. Eléments de dialectes_____________________________________________________13



4.IntroductionCe support a pour but de vous présenter le sous ensemble du langage SQL utilisé pour manipuler des données au sein d’un Système de Gestion de Bases de Données Relationnel.On utilisera le terme de Langage de Manipulation de Données (LMD) pour qualifier ce sous-ensemble (de DML Data Management Language en anglais).Avant d’aborder le langage SQL proprement dit, prenez connaissance du document sur l’algèbre relationnel qui vous permettra de mieux comprendre les notions sous-jacentes aux opérations traitées dans ce document.

Seront traités dans ce document :

Eléments syntaxiques relatifs à SQL Exemples de manipulation de données au travers des opérations de

Sélection, Suppression, Mise à jour et Ajout. Différences syntaxiques au sein des différents dialectes SQL (document

joint)

4.1. Bref HistoriqueS.Q.L. est un langage structuré qui permet :

de créer la structure d’une base de données relationnelle (base, tables, …) d’interroger et de modifier les données contenues dans ce type de base de

données.

S.Q.L. signifie Structured Query Language.Il est issu de SEQUEL : Structured English Query Language.C’est le premier langage pour les S.G.B.D. Relationnels. Il a été développé par IBM en 1970 pour système R, son 1er SGBDR.

S.Q.L. a été reconnu par l’ANSI (Association de Normalisation des Systèmes d’Information) puis s’est imposé comme norme. Il n’existe pas de S.G.B.D. Relationnel sans S.Q.L..

Malheureusement, malgré la norme S.Q.L., il existe un ensemble de dialectes. Les différences entre ces différents dialectes sont souvent minimes et tous respectent un minimum commun.Il existe aujourd’hui une troisième version de SQL, SQL 3, qui proposera de nouveaux éléments syntaxiques mieux à même de prendre en compte les évolutions technologiques liées au développement de systèmes à objets.

4.2. DéfinitionS.Q.L. est un langage relationnel qui permet d’effectuer les tâches suivantes :

Définition et modification de la structure de la base de données Interrogation et modification non procédurale (c’est à dire interactive) de

la base de données Contrôle de sécurité et d’intégrité de la base Sauvegarde et restauration des bases


SQL - Sous Ensemble LMD Opérations de BaseS.Q.L. est un langage interactif, mais il peut aussi être intégré dans un langage de programmation pour le développement d’applications.

S.Q.L. est un standard qui permet d’assurer une portabilité importante des bases de données relationnelles.

5.Opération de sélection L’instruction SELECT permet d’extraire des données et de les présenter triées et/ou regroupées suivant certains critères.Les enregistrements doivent vérifier certains critères exprimés dans des expression conditionnelles.

Syntaxe de l’instruction SELECT présentant les différentes clauses utilisables dans les sélections. Certaines clauses sont facultatives, mais il est important de respecter l’ordre d’apparition des clauses dans la requête.

SELECT liste des attributs à afficher[DISTINCT] mot clé facultatif (élimination des doublons)FROM liste des noms de tablesWHERE condition sur les attributsGROUP BY nom de l’attribut de regroupementHAVING condition de regroupementORDER BY critère de tri

5.1. Requêtes de type détail sur une seule table

Ensemble des informations concernant les vols partant après 16 h.

SELECT *FROM VolWHERE HeureDepart > 16

Figure 7: Ensemble des informations concernant les vols partant après 16 h.

En fait, nous n’avons pas besoin de tous les attributs de la table des vols.Certaines informations, comme le N° d’avion ou le pilote, ne nous intéressent pas dans l’élaboration de la réponse au client voulant connaître les horaires.Par contre, celui-ci nous précise qu’il s’agit d’un vol au départ de Toulouse et à destination de Grenoble.


SQL - Sous Ensemble LMD Opérations de BaseNous alors donc restreindre les attributs retenus dans l’opération SELECT au moyen d’une Projection et compléter la clause Where.

Rappel : La projection est une opération qui consiste à ne retenir que certains attributs dans une table

SELECT vol#,heuredepart,heurearrivee FROM vol WHERE heuredepart > 16 AND villedepart LIKE 'Toulou%'AND villearrivee = 'Grenoble'

Nous avons introduit dans la clause WHERE, une opération logique AND et un opérateur sur chaîne LIKE.

Figure 8 : Vols au départ de Toulouse à destination de Grenoble après 16 h

5.1.1. Utilisation du mot clé DISTINCTLe résultat d’un SELECT étant un ensemble, il peut y avoir des doublons. Le mot clé DISTINCT permet de préciser que l’on ne veut qu’un seul exemplaire des lignes retenues dans la sélection.

Ensemble des types d’avions dont la capacité est supérieure à 250 passagers.

SELECT Marque,TypeAvion, CapaciteFROM AVIONWHERE Capacite > 250

Figure 9 : Types d'avions dont la capacité est supérieure à 250



Nous ajoutons le mot clé DISTINCT à la sélection et ordonnons les données par capacités décroissantes :

SELECT DISTINCT Marque,TypeAvion,CapaciteFROM AVIONWHERE Capacite > 250ORDER BY Capacite DESC

Remarques : la clause ORDER BY doit toujours être exprimée en dernier. Elle peut contenir plusieurs facteurs séparés par une virgule et l’ordre peut être croissant ASC (par défaut) ou décroissant DESC.DISTINCT concerne la totalité des valeurs des attributs : ici, Marque, TypeAvion et Capacite.

Figure 10 : Sélection ordonnée de lignes distinctes

5.2. Fonctions de groupe Nous pouvons travailler sur des données consolidées au travers de requêtes qui, à partir d’un ensemble de lignes sélectionnées, réalisent des opérations de synthèse sur ces données.

Nous pouvons combiner ainsi des fonctions récapitulatives à la liste des attributs sélectionnés:

SUM somme des valeurs de l’attribut MIN minimum des valeurs de l’attribut MAX maximum des valeurs de l’attribut AVG moyenne des valeurs COUNT nombre d’enregistrements sélectionnés



Nous avons à notre disposition des clauses spécifiques à la construction de requêtes récapitulatives. Nous avons ainsi les clauses :

GROUP BY permet d’exprimer sur quels attributs sont agrégés les données.HAVING permet de définir une condition sur des valeurs de regroupement.

5.2.1. Quelques exemples pour comprendre :Nombre d’avions dans la table AVION

SELECT COUNT(AV#)FROM AVION

Le résultat est 16 (avec mon jeu d’essai)

Je peux aussi donner un nom de colonne au résultat de l’expression COUNT en ayant recours au mécanisme des alias : NomColonne AS NomAlias. Nous verrons que nous devrons recourir de nouveau aux alias dans d’autres contextes.

SELECT COUNT(AV#) as "Nombre Avions"FROM AVION

Je peux aussi vouloir comptabiliser le nombre d’avions par marque.La clause GROUP BY me permet alors de définir les conditions de regroupement des calculs récapitulatifs :

SELECT COUNT(AV#) as "Nombre Avions"FROM AVIONGROUP BY Marque

Il serait éminemment préférable de faire figurer le nom de la marque dans le résultat et de trier les valeurs récapitulatives :

SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONGROUP BY MarqueORDER BY "Nombre Avions" DESC

Qui peut s’écrire aussi :



SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONGROUP BY MarqueORDER BY COUNT(AV#) DESC

Je peux aussi ne pas vouloir conserver dans mon résultat les marques dont le nombre d’ avions est inférieur à 3. J’introduis alors dans ma requête une clause HAVING qui exprime une condition sur une opération de regroupement.

SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONGROUP BY MarqueHAVING COUNT(AV#) > 2 -- on ne peut pas utiliser "Nombre Avions"ORDER BY COUNT(AV#) DESC

Attention à ne pas confondre la clause HAVING et la clause WHERE qui filtre les lignes retenues pour les calculs. Ainsi, si je souhaite ne pas retenir les avions localisés à Toulouse, je constate que les résultats diffèrent :

SELECT Marque,COUNT(AV#) as "Nombre Avions"FROM AVIONWHERE localisation <> 'Toulouse' -- Expression de différentGROUP BY MarqueHAVING COUNT(AV#) > 2 ORDER BY COUNT(AV#) DESC



Capacités minimum et maximum des Boeing.

SELECT MIN(Capacite) as "Capacite Mini Boeing", MAX(Capacite) as "Capacite Maxi Boeing"FROM AVIONWHERE Marque = 'BOEING'

5.3. Les opérateursUn opérateur est un symbole spécifiant une action exécutée sur une ou plusieurs expressions. Nous trouvons en SQL, différentes catégories d’opérateurs. Je vous présente ici les principaux utilisables dans les requêtes de sélection.

Opérateurs arithmétiques Opérateurs de comparaison Opérateurs logiques Opérateur de traitement de chaînes ;

5.3.1. Opérateurs arithmétiquesUtilisables sur toute expression de 2 valeurs numériques et sur les valeurs de type DateTime ou SmallDateTime (+ et – uniquement)

Opérateur Description+ (Ajouter) Addition- (Soustraire) Soustraction* (Multiplication) Multiplication/ (Division) Division% (Modulo) Retourne le reste entier d'une

division. 12 % 5 = 2.

Exemple : Total du CA Net généré par commande

SELECT OrderID, sum(Unitprice * (1-Discount)*Quantity) as "Total Commande"FROM [Order Details]GROUP BY OrderID



Figure 11 : Somme du CA Net par Commande

Remarques : Attention à la priorité des opérateurs : le plus simple pour éviter les problèmes de mise au point est de réaliser les opérations entre parenthèses : les opérations internes (à l’intérieur des parenthèses) sont évaluées en premier.Les noms composés des tables doivent être exprimés entre crochets [ ].

5.3.2. Opérateurs de comparaisonLes opérateurs de comparaison testent si deux expressions sont identiques.Ils peuvent s'utiliser sur toutes les expressions composées de données structurées, donc à l'exception des expressions de type de données text, ntext ou image. Nous reviendrons sur ces types particuliers ultérieurement.

Opérateur Description= (Égal à) Égal à> (Supérieur à) Supérieur à< (Inférieur à) Inférieur à>= (Supérieur ou égal à) Supérieur ou égal à<= (Inférieur ou égal à) Inférieur ou égal à<> (Différent de) Différent de

5.3.3. Opérateurs logiquesLes opérateurs logiques testent la valeur logique d'une condition. Les opérateurs logiques, comme les opérateurs de comparaison, retournent un type de données booléen de valeur TRUE ou FALSE.

Un certains nombre d’entre eux (signalés par un * dans le tableau) sont utilisés pour comparer une valeur scalaire (unique) avec une sous requête. Nous en illustrerons l’utilisation dans le chapitre consacré aux jointures ensemblistes.



Opérateur DescriptionALL (*) TRUE si tous les éléments d'un jeu de

comparaisons sont TRUE.AND TRUE les deux expressions booléennes sont

TRUE.ANY (*) TRUE si n'importe quel élément d'un jeu de

comparaison est TRUE.BETWEEN TRUE si l'opérande est situé dans une certaine

plage.EXISTS (*) TRUE si une sous-requête contient des lignes.IN (*) TRUE si l'opérande est égal à un élément

d'une liste d'expressions.LIKE TRUE si l'opérande correspond à un modèle.NOT Inverse la valeur de tout autre opérateur

booléen.OR TRUE si l'une ou l'autre expression booléenne

est TRUE.SOME (*) TRUE si certains éléments d'un jeu de

comparaisons sont TRUE.* Utilisés avec des sous-requêtes

Exemple : Liste des vols au départ de Toulouse et a destination de Paris entre 12 et 15 heures.

SELECT vol#, villedepart, villearrivee, heuredepart, heurearriveeFROM VolWHERE Heuredepart BETWEEN 12 AND 15 AND villedepart LIKE 'TOULO%'AND villearrivee LIKE 'PAR%'

Figure 12 : Illustration utilisation des opérateurs logiques

Syntaxe de l’opérateur Between : Between valeur de début valeur de fin.Les bornes sont retenues dans la sélection comme le prouve la requête ci-dessus.



Syntaxe de l’opérateur Like

Caractère générique

Description

% Toute chaîne de zéro caractère ou plus_ N'importe quel caractère à cet emplacement[ ] Tout caractère de l'intervalle ([a-f]) ou de l'ensemble

spécifié ([abcdef])[^] Tout caractère en dehors de l'intervalle ([^a-f]) ou de

l'ensemble spécifié (^abcdef]). ^ représente le NOT

L’opérateur LIKE est extrêmement puissant pour réaliser des recherches sur des chaînes avec des valeurs approximatives.

Quelques exemples illustrant son utilisation.

A noter , l’utilisation :des caractères - - pour introduire une ligne de commentairesdes caractère /* …. */ pour un bloc de commentaires

-- Liste des noms de pilotesSELECT nomFROM Pilote

-- Liste des Pilotes dont le nom contient la chaîne el */ SELECT nomFROM PiloteWhere nom LIKE '%el%'



-- Liste des Pilotes dont le 3ème caractère du nom est un r*/ SELECT nomFROM PiloteWhere nom LIKE '__r%'

-- Liste des Pilotes dont le nom comporte les caractères u,c,ou lSELECT nomFROM PiloteWhere nom LIKE '%[cu]%'

-- Liste des Pilotes dont le nom commence par le caractère m ou lSELECT nomFROM PiloteWhere nom LIKE '[ml]%'

/* Liste des Pilotes dont le nom commence par le caractère m ou let se termine par l */SELECT nomFROM PiloteWhere nom LIKE '[ml]%l'

Les opérateurs SOME, ANY et ALL seront vus dans le chapitre suivant dans le cadre des opérations sur pliusieurs tables.



6.Eléments de dialectesSQL est une norme. Mais comme toute norme, elle n’est pas toujours strictement respectée. La syntaxe des différents SGBDR s’éloigne parfois de cette dernière ou apporte des fonctions non implémentées dans la norme.

Le tableau accompagnant ce document vous permettra d’avoir quelques informations sur l’existence ou non de différentes fonctions par type de SGBDR.

Par Frédéric BROUARD , 2002

Légende :

O : Oui N : Non X : Existe mais syntaxe hors norme ! : Même nom mais fonction différente



Agrégation statistique

FonctionDescription Norme SQL Paradox Access MySQL Post

GreSQLSQL

ServerOracle Inter

Base

AVG Moyenne O O O O O O O OCOUNT Nombre O O X O O O O OMAX Maximum O O O O O O O OMIN Minimum O O O O O O O OSUM Total O O O O O O O O

Fonction "système"



GreSQLSQL

ServerOracle Inter

Base

CURRENT_DATE Date courante O N N O O N N OCURRENT_TIME Heure courante O N N O O N N OCURRENT_TIMESTAMP Date et heure courante O N N O O O N OCURRENT_USER Utilisateur courant O N N N O O N NSESSION_USER Utilisateur autorisé O N N X O O N NSYSTEM_USER Utilisateur système O N N X O O N NCURDATE Date du jour N N N O N N N NCURTIME Heure courante N N N O N N N NDATABASE Nom de la bases de

données couranteN N N O N O O N

GETDATE Heure et date courante N N N N N O N NNOW Heure et date courante N O O O O O O NSYSDATE Date et/ou heure

couranteN N N O N N O N

TODAY Date du jour N O N N N N N NUSER Utilisateur courant N N N O N O O OVERSION Version du SGBDR N N N O O N N N



Fonctions générales



GreSQLSQL

ServerOracle Inter

Base

CAST Transtypage O O N O O O O OCOALESCE Valeur non NULL O N N O O O N NNULLIF Valeur NULL O N N O O O N NOCTET_LENGTH Longueur en octet O N N O O N O NDATALENGTH Longueur N N N N N O N NDECODE Fonction conditionnelle N N N N N N O NGREATEST Plus grande valeur N N N O N N O NIFNULL Valeur non NULL N N N O O O N NLEAST Plus petite valeur N N N N O N O NLENGTH Longueur N N O O O O O NNVL Valeur non NULL N N N N N N O NTO_CHAR Conversion de données

en chaîneN N N N N N O N

TO_DATE Conversion en date N N N N O N O NTO_NUMBER Conversion en nombre N N N N N N O N



Fonctions de chaînes de caractèresFonction Description Norme


GreSQLSQL

ServerOracle Inter

Base|| Concaténation O O N X O N O OCHAR_LENGTH Longueur d'une chaîne O N N X O N N NCHARACTER_LENGTH Longueur d'une chaîne O N N O O O N NCOLLATE Substitution à une

séquence de caractèresO N N N N N N O

CONCATENATE Concaténation O N N N N N N NCONVERT Conversion de format

de caractèresO N N N N ! O O

LIKE (prédicat) Comparaison partielle O O X O O O O OLOWER Mise en minuscule O O N O O O O NPOSITION Position d'une chaîne

dans une sous chaîneO N N O O N N N

SUBSTRING Extraction d'une sous chaîne

O O N O O N N N

TRANSLATE Conversion de jeu de caractères

O N N N X N X N

TRIM Suppression des caractères inutiles

O O N O O N O N

UPPER Mise en majuscule O O N O O O O OCHAR Conversion de code en

caractère ASCIIN N O O N O N N

CHAR_OCTET_LENGTH Longueur d'une chaîne en octets

N N N N N O N N

CHARACTER_MAXIMUM_LENGTH Longueur maximum d'une chaîne

N N N N N O N N

CHARACTER_OCTET_LENGTH Longueur d'une chaîne N N N N N O N NCONCAT Concaténation N N O O N O O NILIKE LIKE insensible à la

casseN N N N O N N N


SQL - Sous Ensemble LMD Opérations de BaseFonctions de chaînes de caractères (Suite)



GreSQLSQL

ServerOracle Inter

Base

INITCAP Initiales en majuscule N N N N O N O NINSTR Position d'une chaîne

dans une autreN N O O N N O N

LCASE Mise en minuscule N N O O N O O NLOCATE Position d'une chaîne

dans une autreN O O O N O O N

LPAD Remplissage à gauche N N N O O N O NLTRIM TRIM à gauche N O O O O O O NNCHAR Conversion de code en

caractère UNICODEN N N N N O N N

PATINDEX Position d'un motif dans une chaîne

N N N N N O N N

REPLACE Remplacement de caractères

N N N O N O O N

REVERSE Renversement N N N O N O O NRPAD Remplissage à droite N N N O O N O NRTRIM TRIM à droite N N O O O O O NSOUNDEX Code de consonance N N N O O O O NSPACE Génération d'espaces N N O O N O O NUCASE Mise en majuscule N N O O N O O N



Fonctions de chaînes de bits



GreSQLSQL

ServerOracle Inter

Base

BIT_LENGTH Longueur en bit O N N N N N N N& "et" pour bit logique N N ? ? ? O ? ?| "ou" pour bit logique N N ? ? ? O ? ?^ "ou" exclusif pour bit

logiqueN N ? ? ? O ? ?



Fonction numériques



GreSQLSQL

ServerOracle Inter

Base

% Modulo N N N O O O N N+ - * / ( ) Opérateurs et

parenthésageO O O O O O O O

ABS Valeur absolue N N O O O O O NACOS Angle de cosinus N N N O O O O NASCII Conversion de

caractère en code ASCII

N N O O O O O N

ASIN Angle de sinus N N N O O O O NATAN Angle de tangente N N N O O O O NCEILING Valeur approchée

hauteN N O O N O N N

COS Cosinus N N O O O O O NCOT Cotangente N N O O O O N NEXP Exponentielle N N O O O O O NFLOOR Valeur approchée

basseN N O O O O O N

LN Logarithme népérien N N N N N N O NLOG Logarithme népérien N N O O N O O NLOG(n,m) Logarithme en base n

de mN N N N O N O N

LOG10 Logarithme décimal N N N O N O O NMOD Modulo N N O O O O O NPI Pi N N N O O O O NPOWER Elévation à la

puissanceN N O O N O O N

RAND Valeur aléatoire N N O O N O N NROUND Arrondi N N O O O O N N


SQL - Sous Ensemble LMD Opérations de BaseFonction numériques (Suite)



GreSQLSQL

ServerOracle Inter

Base

SIGN Signe N N O O O O O NSIN Sinus N N O O O O O NSQRT Racine carrée N N O O O O N NTAN Tangente N N O O O O O NTRUNC Troncature N N N N N N O NTRUNCATE Troncature N N O O O O NUNICODE Conversion de

caractère en code UNICODE

N N N N N O ? N



Fonctions temporelles



GreSQLSQL

ServerOracle Inter

Base

EXTRACT Partie de date O O N O O N O NINTERVAL (opérations sur) Durée O N N N N N O NOVERLAPS (prédicat) Recouvrement de

périodeO N N N O N N N

ADDDATE Ajout d'intervalle à une date

N N N O N N N N

AGE Age N N N N O N N NDATE_ADD Ajout d'intervalle à


DATE_FORMAT Formatage de date N N N O N N N NDATE_PART Partie de date N N N N O N N NDATE_SUB Retrait d'intervalle à


DATEADD Ajout de date N N N N N O N NDATEDIFF Retrait de date N N N N N O N NDATENAME Nom d'une partie de

dateN N N N N O N N

DATEPART Partie de date N N N N N O N NDAY Jour d'une date N N N N N O N NDAYNAME Nom du mois N N O O N O N NDAYOFMONTH Jour du mois N N N O N N N NDAYOFWEEK Jour de la semaine N N N O N N N NDAYOFYEAR Jour dans l'année N N N O N N N NHOUR Extraction de l'heure N N O O N O N NLAST_DAY Dernier jour du mois N N N N N N O NMINUTE N N O O N O N NMONTH Mois d'une date N N O O N O O N


SQL - Sous Ensemble LMD Opérations de BaseFonctions temporelles



GreSQLSQL

ServerOracle Inter

Base

MONTH_BETWEEN Nombre de mois N N NMONTHNAME Nom du mois N N O O N O N NNEXT_DAY Prochain premier jour

de la semaineN N N N N N O N

SECOND Extrait les secondes N N O O N O N NSUBDATE Retrait d'intervalle à


WEEK Numéro de la semaine N N O O N O O NYEAR Année d'une date N N O O N O O N






GreSQLSQL

ServerOracle Inter

Base

CASE Structure conditionnelle

O N N O O O X N

IS [NOT] TRUE Vrai O N N N N N N NIS [NOT] FALSE Faux O N N N N N N NIS [NOT] UNKNOWN Inconnu O N N N N N N NIS [NOT] NULL NULL O O X O O O O OINNER JOIN Jointure interne O O O O O O N OLEFT, RIGHT, FULL OUTER JOIN

Jointure externe O O O O O O N O

NATURAL JOIN Jointure naturelle O N N O O N N NUNION JOIN Jointure d'union O N N N N N N NCROSS JOIN Jointure croisée O N N O O O N NLEFT, RIGHT, FULL OUTER NATURAL JOIN

Jointure naturelle externe

O N N X O N N N

INTERSECT Intersection (ensemble)

O ? N N O N X N

UNION Union (ensemble) O ? O N O O O OEXCEPT Différence (ensemble) O ? N N O N N N[NOT] IN Liste O O O X O O O O[NOT] BETWEEN Fourchette O O O O O O O[NOT] EXISTS Existence O ? ? N O O O O






GreSQLSQL

ServerOracle Inter

Base

ALL Comparaison à toutes les valeurs d'un ensemble

O ? O N O O O O

ANY / SOME Comparaison à au moins une valeur de l'ensemble

O ? O N O O O O

UNIQUE Existance sans doublons

O N N N N N N N

MATCH UNIQUE Correspondance O N N N N N N Nrow value construteur Construteur de ligne

valuéesO N N N N N O N

MINUS Différence (ensemble) N N N N O O O NLIMITE nombre de ligne

retournéeN N TOP LIMIT LIMIT TOP N ROWS

identifiant de ligne N N N _rowid oid N rowid ?



Sous requêtesNorme SQL

Paradox Access MySQL Post GreSQL

SQL Server

Oracle Inter Base

Imbriquées O O O N O O O OCorrélées O O O N O O O ODans la clause SELECT O X O N O O O ODans la clause FROM O N N N O O O NDans la clause WHERE O O O N O O O ODans la clause HAVING O O N N O O O O


ROYAUME DU MAROC


Pour approfondir le sujet….Vous trouverez des éléments complémentaires sur le site de Microsoft qui comporte un portail consacré à SQL Version 2005 et les premiers éléments sur la prochaine version à venir 2008




ROYAUME DU MAROC





Sommaire

1. Introduction_____________________________________________________________2

2. Requêtes intégrant plusieurs tables___________________________________________32.1. La méthode ensembliste_____________________________________________________3

2.2. La méthode prédicative_______________________________________________________8


ROYAUME DU MAROC


7.IntroductionCe support a pour but de vous présenter les différents types de jointure, les opérations de jointure et d’une manière générale, le travail avec des données issues de plusieurs tables.Seront traités dans ce document :

Les opérateurs ensemblistes La méthode des prédicats Les différentes natures de jointure (equi-jointure, jointure droite, gauche et

totale)


ROYAUME DU MAROC


8.Requêtes intégrant plusieurs tablesNous allons aborder ici la mise en œuvre de requêtes portant sur plusieurs tables. Deux méthodes sont à notre disposition, la méthode ensembliste et la méthode prédicative. Ces deux méthodes réalisent des jointures.

La jointure consiste à rechercher entre deux tables ayant un attribut commun (même type et même domaine de définition) tous les tuples (toutes les lignes) pour lesquels ces attributs ont la même valeur.

la méthode ensembliste réalise l’intersection de deux ensembles et s’exprime sous forme de requêtes imbriquées.

la méthode prédicative vérifie l’égalité de deux attributs et s’exprime sous la forme d’une seule sélection conditionnelle.

8.1. La méthode ensemblisteSchéma de construction :

SELECT liste d’attributsFROM table1WHERE attribut de jointure

IN (SELECT attribut de jointureFROM table2WHERE condition)

La requête à l’intérieur des parenthèses est dite requête interne ou sous-requête. Elle est évaluée en premier, constituant ainsi un premier ensemble dont on réalisera l’intersection (IN) avec l’ensemble issu de l’évaluation de la requête externe.

Les attributs sélectionnés, et retenus dans le jeu de résultat, sont nécessairement issus de la requête externe. Il s’agit donc d’une méthode assez restrictive.

D’un manière générale, l’exécution des requêtes construites selon la méthode ensembliste demande plus de ressources au système. Elles peuvent toutefois être plus faciles à réaliser et bien adaptées à certains cas de figure.

Exemple : Liste des pilotes assurant un vol au départ de Paris

SELECT PIL# as "Code Pilote",NOMFROM PILOTEWHERE PIL# IN (SELECT PILOTE

FROM VOLWHERE Villedepart = 'Paris');


ROYAUME DU MAROC


Figure 13 : Illustration de la méthode ensembliste

Autre exemple : Traitement du sauf (négation NOT) ou différence. Liste des pilotes qui ne sont pas affectés à des vols.

SELECT PIL# as "Code Pilote",NOMFROM PILOTEWHERE PIL# NOT IN (SELECT PILOTEFROM VOL)

L’attribut de jointure est une valeur scalaire et ne peut donc être une valeur vectorielle. Ainsi, vous ne pouvez pas écrire une requête sous la forme qui suit :

SELECT PIL# as "Code Pilote",NOMFROM PILOTEWHERE (PIL#,VILLE) NOT IN (SELECT PILOTE,VILLEDEPARTFROM VOL)

On ne pourra pas non plus écrire la requête suivante :

SELECT PIL# as "Code Pilote",NOM,VILLEDEPARTFROM PILOTEWHERE PIL# IN (SELECT PILOTE FROM VOL)

Car l’attribut VILLEDEPART n’appartient pas à la table sur laquelle porte la requête externe. Vous obtenez un message d’erreur :

Serveur : Msg 207, Niveau 16, État 3, Ligne 1'VILLEDEPART' : nom de colonne incorrect.

On peut par contre joindre un attribut avec un attribut qui résulte d’une opération récapitulative.Nous pouvons ainsi obtenir la liste des avions dont la capacité en passagers est égale à la capacité maximum.

SELECT AV#,MARQUE,TYPEAVION,CAPACITE FROM AVION


ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du TravailWHERE CAPACITE IN

(SELECT MAX(CAPACITE) FROM AVION)


ROYAUME DU MAROC


Figure 14 : Liste des avions dont la capacité est égale à la capacité maximum

Si nous souhaitons préciser qu’il s’agit en fait des avions qui ont la capacité maximum du type, nous modifierons instinctivement la requête comme suit, afin de calculer le maximum selon le type :

SELECT AV#,MARQUE,TYPEAVION,CAPACITE FROM AVIONWHERE CAPACITE IN

(SELECT MAX(CAPACITE) FROM AVION GROUP BY MARQUE,TYPEAVION)

Ce qui donne pour résultat (extrait) :

Ce qui est faux, car un Airbus A320 se trouve être retenu car sa capacité vaut la capacité maximum des avions de type Caravelle !

En fait, pour obtenir un résultat exact, il nous faut corréler les lignes de la requête externe avec celles de la requête interne. Tous les SGBDR ne sont pas en capacité de réaliser ce type d’opérations.Elles sont à utiliser avec parcimonie dans la mesure du possible car très coûteuses en matière de ressources et donc de temps de traitement.

En fait, la requête interne se trouve évaluée pour chaque ligne de la requête externe !


ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du TravailDans l’exemple suivant, vous remarquerez l’utilisation d’un alias pour distinguer deux occurrences d’une même table.


ROYAUME DU MAROC


Requêtes corrélées

SELECT AV#,MARQUE,TYPEAVION,CAPACITE FROM AVION as "AV1"WHERE CAPACITE IN

(SELECT MAX(CAPACITE) FROM AVION WHERE MARQUE = AV1.MARQUE AND TYPEAVION = AV1.TYPEAVION GROUP BY MARQUE,TYPEAVION)

Le résultat est alors correct et l’avion 100 éliminé du jeu de résultats.

Figure 15 : Exemple de requête corrélée

Comparaison d’une valeur avec l’ensemble des valeurs d’un attribut d’une requête interne.

On peut utiliser les opérateurs SOME, ANY, ou ALL pour comparer la valeur d’une expression avec les valeurs d’un attribut d’une requête interne.

Exemple : Liste des avions dont la capacité est supérieure à toute capacité des avions de marque Boeing.

SELECT AV#,MARQUE,TYPEAVION,CAPACITE FROM AVIONWHERE CAPACITE > ALL

(SELECT CAPACITE FROM AVION WHERE MARQUE='Boeing')

La liste de valeurs générée par la requête interne est {250, 300, 400}


ROYAUME DU MAROC


Le résultat est le suivant :

Figure 16 : Comparer une valeur à un ensemble avec ALL

En modifiant la requête et en remplaçant ALL par ANY ou SOME, nous obtenons la liste de tous les avions dont la capacité est > 200.

Figure 17 : Comparer une valeur à un ensemble avec SOME ou ANY

Utilisation de EXISTS.

EXISTS peut être utilisé pour réaliser des intersections ou des différences entre deux requêtes. Ce n’est pas la meilleure forme d’utilisation, car elle fait appel à des requêtes corrélées et peut s’écrire plus simplement. Je donne donc ici un seul exemple d’utilisation. Nous verrons d’autres exemples de mise en œuvre plus judicieux par la suite dans la partie Programmation du SGBDR.

Liste des pilotes qui habitent la ville de départ d’un vol.


ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du TravailL’évaluation de EXISTS renvoie un booléen porteur de la valeur vrai ou faux.


ROYAUME DU MAROC


SELECT PIL#,NOMFROM PILOTEWHERE EXISTS ( SELECT VILLEDEPART FROM VOL WHERE VILLEDEPART = PILOTE.VILLE)

Figure 18 : Illustration de l'opérateur EXISTS

Remarque : Cette requête serait mieux écrite ainsi :

SELECT PIL#,NOMFROM PILOTEWHERE VILLE IN ( SELECT VILLEDEPART FROM VOL)

Utilisation de EXCEPT et INTERSECT.

EXCEPT et INTERSECT sont de nouveaux opérateurs permettant de trouver des enregistrements communs à deux jeux de données ou de retrouver des enregistrements figurant dans un jeu de données et pas dans l’autre. Ces opérateurs obéissent aux mêmes règles que UNION, les jeux d’enregistrement doivent être de structure identique (même nombre de colonnes, mêmes types voire même longueur).

8.2. La méthode prédicativeLa requête comporte une seule instruction SELECT qui traite plusieurs tables dont la liste apparaît dans la clause FROM.La traduction de la jointure se fait par une équation de jointure (égalité entre 2 attributs) exprimée au niveau de la clause FROM.

Il existe quatre natures de jointure qui sont respectivement exprimées par les mots clés INNER, RIGHT OUTER, LEFT OUTER ou FULL OUTER dont nous verrons les différents cas d’usage dans les exemples suivants.


ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du TravailImportant : La méthode prédicative permet de rapporter dans le jeu de résultats les valeurs des attributs des différentes tables définies dans la clause FROM.

Cette méthode est en général préférable à la méthode ensembliste car plus performante.


ROYAUME DU MAROC


Premier exemple de jointure interne (INNER JOIN)

Liste des avions affectés à des vols avec la mention de la ville de départ du vol . Nous avons besoin d’utiliser les tables VOL et AVION et de déterminer l’attribut commun aux deux tables sur lequel portera la jointure.Ici le code de l’avion, nommé AV# dans AVION et AVION dans VOL.

SELECT AV#,MARQUE,TYPEAVION,VILLEDEPARTFROM AVION INNER JOIN VOL

ON AVION.AV# = VOL.AVION

Autre manière

SELCT AV#,MARQUE,TYPEAVION,VILLEDEPART FROM AVION A ,VOL VWHERE A.AV# = V.AVION

La clause INNER JOIN, jointure interne, ne retient que les lignes des deux tables pour lesquelles l'expression exprimée au niveau de ON se vérifie.

Figure 19 : Expression de la jointure INNER JOIN

Si nous voulons la liste de tous les avions avec, pour ceux qui volent, des informations sur les villes de départ, nous écrierons :

SELECT AV#,MARQUE,TYPEAVION,VILLEDEPARTFROM AVION LEFT OUTER JOIN VOL

ON AVION.AV# = VOL.AVION

Pour les avions qui ne sont pas en service, l’attribut VILLEDEPART aura la valeur NULL


ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du TravailLe résultat nous prouve qu’une requête de type LEFT OUTER renvoie la liste des lignes pour lesquelles la jointure avec égalité est respectée, complétée par les lignes de la table de gauche sans correspondance dans la table de droite.


ROYAUME DU MAROC


Résultat d’une jointure externe gauche

Figure 20 : Illustration de LEFT OUTER JOIN

Compléments sur la définition des jointures

INNERSpécifie toutes les paires correspondantes de lignes renvoyées. Supprime les lignes n'ayant pas de correspondance entre les deux tables. Ceci est l'option par défaut si aucun type de jointure n'est spécifié.

FULL [OUTER] : Peu usitéePrécise qu'une ligne de la table de gauche ou de droite, qui ne correspond pas à la condition de jointures, est comprise dans l'ensemble de résultats et que les colonnes de sortie sans correspondance dans l'autre table ont des valeurs nulles. Ceci est fourni en plus de toutes les lignes renvoyées par INNER JOIN.

LEFT [OUTER]Spécifie que toutes les lignes de la table de gauche ne respectant pas la condition de jointure sont comprises dans l'ensemble de résultats, et que les colonnes de sortie de l'autre table sans correspondance ont des valeurs NULL.

RIGHT [OUTER] : Spécifie que toutes les lignes de la table de droite ne respectant pas la condition de jointure sont comprises dans l'ensemble de résultats, et que les colonnes de sortie correspondant à l'autre table ont des valeurs NULL.JOINIndique que l'opération de jointure spécifiée doit avoir lieu entre les tables ou vues données.

ON <condition de recherche>Indique la condition sur laquelle se base la jointure

Auto-jointure


ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du TravailL’auto-jointure est la jointure entre une table et elle-même, pour sélectionner des enregistrements correspondant à d’autres de la même table. Il est nécessaire de recourir alors à des alias pour définir la table déjà utilisée.


ROYAUME DU MAROC


Exemple d’auto-jointure : Liste des avions de même capacité

SELECT AVION.AV#, AVION.MARQUE, AVION.TYPEAVION, AVION.CAPACITE, AV2.AV#, AV2.MARQUE, AV2.TYPEAVION, AV2.CAPACITEFROM AVION INNER JOIN AVION AS "AV2"

ON AVION.CAPACITE = AV2.CAPACITEWHERE AVION.AV# > AV2.AV#

A noter : L’expression de la clause WHERE pour éliminer les paires de lignes figurant plusieurs fois.Notez de plus le préfixe systématique du nom des colonnes (attributs) par le nom de la table afin d’éviter une erreur liée au caractère ambigu du nom.En effet le serveur ne sait pas dans quelle table (ou occurrence d’une table) il doit extraire la valeur d’un attribut si celui-ci existe dans plusieurs tables (occurrences) sous le même nom.

Utilisation du mot clé DISTINCT

Les jointures peuvent rapporter des lignes figurant plusieurs fois dans le jeu de résultat. Vous pouvez utiliser DISTINCT pour ne conserver qu’un seul exemplaire des lignes rapportées dans le jeu de résultat.

Exemple de traitement de la différence

Il faut réaliser une jointure gauche et ne retenir que les éléments de la table de gauche (LEFT OUTER) ou de droite (RICHT OUTER) ayant des valeurs nulles. Nous introduisons ici la fonction IS NULL permettant d’évaluer un attribut de valeur nulle.

Liste des avions non affectés à des vols

SELECT AV#,MARQUE,TYPEAVIONFROM AVION LEFT OUTER JOIN VOL

ON AVION.AV# = VOL.AVIONWHERE VOL.AVION IS NULL

Exemple de requêtes portant sur 3 tables jointes 2 à 2

Liste des vols avec informations sur pilotes et avions

SELECT VOL#, VILLEARRIVEE, VILLEDEPART, HEUREDEPART, AV#,MARQUE, TYPEAVION, PIL#, NOMFROM AVION INNER JOIN VOL ON AVION.AV# = VOL.AVION

INNER JOIN PILOTEON VOL.PILOTE = PILOTE.PIL#


ROYAUME DU MAROC


Figure 21 : Illustration de jointure de 3 tables 2 à 2

Exemple de 2 ensembles issus de 2 jointures 2 à 2 non dépendantes

D’une part des pilotes volant sur des airbus (1er ensemble)D’autre part des pilotes habitant dans une ville qui est le point de départ d’un vol.

SELECT VOL.VOL#,VOL.VILLEDEPART,AV#,MARQUE,TYPEAVION,PILOTE.NOMFROM AVION INNER JOIN VOL

ON AVION.AV# = VOL.AVION,PILOTE INNER JOIN VOL as VOL2ON PILOTE.VILLE = VOL2.VILLEDEPART

WHERE AVION.MARQUE = 'Boeing'AND AVION.LOCALISATION = PILOTE.VILLE

Les 2 ensembles de jointures sont séparés par une virgule.Si une table est utilisée plusieurs fois, il convient de lui donner un alias.

Pour approfondir le sujet….


ROYAUME DU MAROC

Office de la Formation Professionnelle et de la Promotion du TravailVous trouverez des éléments complémentaires sur le site de Microsoft qui comporte un portail consacré à SQL Version 2005 et les premiers éléments sur la prochaine version à venir 2008




ROYAUME DU MAROC





Sommaire

1. Introduction_____________________________________________________________2

2. Fonctions de conversion___________________________________________________2

3. Fonctions de traitement de chaînes__________________________________________3

4. Fonctions de manipulation de dates___________________________________________4



9.IntroductionCe support a pour but de vous présenter les principales fonctions intégrées proposées par SQL Server mais présente aussi sous des formes similaires sinon identiques sous les principaux SGBDR du marché.Sont présentées dans ce document :

Fonctions de conversion Fonctions de traitement de chaînes Fonctions de manipulation de dates Quelques fonctions intégrées

SQL server propose de nombreuses fonctions intégrées qui permettent de manipuler des données utilisateurs ou des données du système.Je vous livre ici quelques fonctions parmi les plus usitées.

10.Fonctions de conversionCertaines conversions ne peuvent être automatiquement réalisées par le système. Nous devons alors réaliser ces conversions de manière explicite au moyen des fonctions de conversion CAST et CONVERT. Attention aux types d’origine et résultant de la conversion : toutes les combinaisons ne sont pas admises.

CONVERT permet de définir un style pour la donnée convertie alors que CAST ne le permet pas.

La fonction système GETDATE() renvoie la date du jour.Si je souhaite convertir celle-ci dans un format américain, j’utilise la fonction CONVERT avec le style approprié.Pour plus d’informations, voir l’aide de Transact SQL à l’index CONVERT

SELECT CONVERT(DATETIME,GETDATE(),102) AS "Date au format américain"

Je souhaite que le CA net soit converti et présenté dans un decimal de 10 de long avec 3 chiffres derrière la virgule.

SELECT CONVERT(Decimal(10,3),sum((UnitPrice * 1- Discount) * Quantity)) AS "TOTAL CA Net"FROM [Order Details]



11. Fonctions de traitement de chaînesQuelques exemples dans ce tableau car elles sont nombreuses …Voir aide à l’index fonctions, chaîne

LEFT, RIGHT Extraire des caractères à gauche ou à droiteUPPER,LOWER Mettre en majuscules ou minusculesLTRIM,RTRIM Suppression des espaces à gauche ou à droiteSUBSTRING Extraction d’une sous chaîneREVERSE Inversion d’une chaîne (miroir…)LEN Longueur d’une chaîneASCII Valeur ascii d’un caractèreNCHAR Renvoie le caractère Unicode fonction de la

valeur donnéeREPLACE Remplacement d’une occurrence de chaîne

par une autre

Liste des noms des pilotes formatés.Le premier caractère de gauche est mis en majusculesLes autres caractères en minuscules

SELECT Upper(Substring(Nom,1,1)) + Substring(Nom,2,Len(Nom)-1)FROM PILOTE

Remplacement de l’occurrence Toulouse par Ville Rose dans l’attribut Ville de la Table Pilote.

SELECT REPLACE(VILLE,'Toulouse','Ville Rose')FROM PILOTEWHERE VILLE LIKE 'TOUL%'



12.Fonctions de manipulation de datesFonctions intégrées permettant de manipuler des valeurs de type DATETIME.

DATEADD Ajout d’un intervalle de temps à une date DATEDIFF Intervalle de temps entre deux datesDATEPART Extraction d’une partie de dateDATENAME Chaîne représentant une partie de dateDAY,MONTH,YEAR Renvoie d’une partie de dateGETDATE,GETUTCDATE Date du système

Quelques exemples :

Ajout de 3 jours à la date de naissance du Pilote

SELECT DATEADD(DAY,3,DateNaissance) as "Date + 3 jours", DateNaissanceFROM Pilote

Nombre de jours entre la date de naissance et la date du jour.

SELECT NOM,DATEDIFF(DAY,DateNaissance,GETDATE()) as "Nombre jours depuis Naissance"FROM Pilote

Extrait de portions de la date de naissance avec DatePart et DateName

SELECT DATEPART(MONTH,DateNaissance),DATENAME(MONTH,DateNaissance)FROM PILOTE



Pour approfondir le sujet….Vous trouverez des éléments complémentaires dans l’aide consacrée à SQl Server et au transact-sql.


Sources de référenceVincent Bost – formateur AFPA Brive


sql - sous ensemble lmd opérations de...

Documents