SQL - Sous Ensemble LMD Opérations de Base



|SQL - Sous Ensemble LMD |

|Opérations de Base |

Sommaire

1. Introduction 2

1.1. Bref Historique 2

1.2. Définition 2

2. Opération de sélection 3

2.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 5

2.2.1. Quelques exemples pour comprendre : 6

2.3. Les opérateurs 8

2.3.1. Opérateurs arithmétiques 8

2.3.2. Opérateurs de comparaison 9

2.3.3. Opérateurs logiques 9

3. Eléments de dialectes 13

Introduction

Ce 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 Bref Historique

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

2 Définition

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

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

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 tables

WHERE condition sur les attributs

GROUP BY nom de l’attribut de regroupement

HAVING condition de regroupement

ORDER BY critère de tri

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

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

SELECT *

FROM Vol

WHERE HeureDepart > 16

[pic]

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.

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

[pic]

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

1 Utilisation du mot clé DISTINCT

Le 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, Capacite

FROM AVION

WHERE Capacite > 250

[pic]

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

FROM AVION

WHERE Capacite > 250

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

[pic]

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

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.

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

[pic]

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 AVION

GROUP BY Marque

[pic]

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 AVION

GROUP BY Marque

ORDER BY "Nombre Avions" DESC

Qui peut s’écrire aussi :

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

FROM AVION

GROUP BY Marque

ORDER BY COUNT(AV#) DESC

[pic]

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 AVION

GROUP BY Marque

HAVING COUNT (AV#) > 2 -- on ne peut pas utiliser "Nombre Avions"

ORDER BY COUNT (AV#) DESC

[pic]

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 AVION

WHERE localisation 'Toulouse' -- Expression de différent

GROUP BY Marque

HAVING COUNT(AV#) > 2

ORDER BY COUNT(AV#) DESC

[pic]

Capacités minimum et maximum des Boeing.

SELECT MIN(Capacite) as "Capacite Mini Boeing",

MAX(Capacite) as "Capacite Maxi Boeing"

FROM AVION

WHERE Marque = 'BOEING'

[pic]

3 Les opérateurs

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

1 Opérateurs arithmétiques

Utilisables 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

[pic]

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 Opérateurs de comparaison

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

| 250

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

[pic]

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

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.

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

[pic]

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 AVION

GROUP BY Marque

[pic]

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 AVION

GROUP BY Marque

ORDER BY "Nombre Avions" DESC

Qui peut s’écrire aussi :

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

FROM AVION

GROUP BY Marque

ORDER BY COUNT(AV#) DESC

[pic]

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 AVION

GROUP BY Marque

HAVING COUNT(AV#) > 2 -- on ne peut pas utiliser "Nombre Avions"

ORDER BY COUNT(AV#) DESC

[pic]

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 AVION

WHERE localisation 'Toulouse' -- Expression de différent

GROUP BY Marque

HAVING COUNT(AV#) > 2

ORDER BY COUNT(AV#) DESC

[pic]

Capacités minimum et maximum des Boeing.

SELECT MIN(Capacite) as "Capacite Mini Boeing",

MAX(Capacite) as "Capacite Maxi Boeing"

FROM AVION

WHERE Marque = 'BOEING'

[pic]

3 Les opérateurs

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

1 Opérateurs arithmétiques

Utilisables 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

[pic]

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 Opérateurs de comparaison

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

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

FROM AVION LEFT OUTER JOIN VOL

ON AVION.AV# = VOL.AVION

WHERE 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#, NOM

FROM AVION INNER JOIN VOL

ON AVION.AV# = VOL.AVION

INNER JOIN PILOTE

ON VOL.PILOTE = PILOTE.PIL#

[pic]

Figure 15 : 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.NOM

FROM AVION INNER JOIN VOL

ON AVION.AV# = VOL.AVION,

PILOTE INNER JOIN VOL as VOL2

ON 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…. |

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

|Vincent Bost – formateur AFPA Brive |

|Frédéric Brouard |

|SQL Les fonctions intégrées |

Sommaire

1. Introduction 2

2. Fonctions de conversion 2

3. Fonctions de traitement de chaînes 3

4. Fonctions de manipulation de dates 4

Introduction

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

Fonctions de conversion

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

[pic]

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]

[pic]

Fonctions de traitement de chaînes

Quelques exemples dans ce tableau car elles sont nombreuses …

Voir aide à l’index fonctions, chaîne

|LEFT, RIGHT |Extraire des caractères à gauche ou à droite |

|UPPER,LOWER |Mettre en majuscules ou minuscules |

|LTRIM,RTRIM |Suppression des espaces à gauche ou à droite |

|SUBSTRING |Extraction d’une sous chaîne |

|REVERSE |Inversion d’une chaîne (miroir…) |

|LEN |Longueur d’une chaîne |

|ASCII |Valeur ascii d’un caractère |

|NCHAR |Renvoie le caractère Unicode fonction de la valeur donnée |

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

Les autres caractères en minuscules

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

FROM PILOTE

[pic]

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

SELECT REPLACE(VILLE,'Toulouse','Ville Rose')

FROM PILOTE

WHERE VILLE LIKE 'TOUL%'

[pic]

Fonctions de manipulation de dates

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

|DATEPART |Extraction d’une partie de date |

|DATENAME |Chaîne représentant une partie de date |

|DAY,MONTH,YEAR |Renvoie d’une partie de date |

|GETDATE,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", DateNaissance

FROM Pilote

[pic]

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

[pic]

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

| |

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

|Vincent Bost – formateur AFPA Brive |

[pic]

-----------------------

ROYAUME DU MAROC

ROYAUME DU MAROC

ROYAUME DU MAROC

ROYAUME DU MAROC

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download