le langage sqlpaulpoeu.free.fr/racine/cours de madame sylvie/non … · · 2007-12-26relation...
TRANSCRIPT
LE LANGAGE SQL
1. Préambule.............................................................................1
2. Le langage SQL....................................................................1 2.1. Le standard SQL............................................................................................................. 1 2.2. Les limites de SQL......................................................................................................... 2
2.2.1. Langage non procédural ......................................................................................... 2 2.2.2. SQL : une portabilité limitée.................................................................................. 2
2.3. SQL : un Langage de Manipulation des Données : Le LID........................................... 2 2.3.1. Projection (La commande SELECT… FROM…)................................................. 3
2.3.1.1. Le symbole *...................................................................................................... 4 2.3.1.2. La clause AS ...................................................................................................... 4 2.3.1.3. Les champs calculés........................................................................................... 5 2.3.1.4. Concaténation de chaînes de caractères............................................................. 5 2.3.1.5. Les agrégats ....................................................................................................... 6
2.3.2. Sélection ou restriction (La commande SELECT … FROM … WHERE … ) ..... 7 2.3.3. Clauses complémentaires ....................................................................................... 8
2.3.3.1. La clause DISTINCT......................................................................................... 8 2.3.3.2. La clause ORDER BY ....................................................................................... 9 2.3.3.3. La clause GROUP BY (le partitionnement) .................................................... 10 2.3.3.4. La clause HAVING ......................................................................................... 11
2.3.4. Les Différents opérateurs ..................................................................................... 12 2.3.4.1. Les opérateurs de comparaisons : .................................................................... 12 2.3.4.2. Les opérateurs logiques ................................................................................... 13 2.3.4.3. Les opérateurs arithmétiques ........................................................................... 13 2.3.4.4. Les opérateurs Spéciaux .................................................................................. 14 2.3.4.5. L’opérateur de concaténation........................................................................... 15
2.3.5. Les fonctions ........................................................................................................ 16 2.3.5.1. les fonctions arithmétiques .............................................................................. 16 2.3.5.2. Expression sur les dates ................................................................................... 17 2.3.5.3. Expression sur les heures................................................................................. 19
2.3.6. Jointure ................................................................................................................. 19 2.3.6.1. La jointure de 2 tables...................................................................................... 20 2.3.6.2. La jointure de plusieurs tables ......................................................................... 21 2.3.6.3. Les jointures naturelles .................................................................................... 22 2.3.6.4. La jointure externe ........................................................................................... 22
2.3.7. Les techniques d’imbrication ............................................................................... 23 2.3.7.1. Utilisation de la technique de l’imbrication..................................................... 23 2.3.7.2. Utilisation de l’opérateur IN, la formulation algébrique ................................. 23 2.3.7.3. Utilisation de l’opérateur = .............................................................................. 24 2.3.7.4. Utilisation de l’opérateur EXISTS .................................................................. 26
2.3.8. Les requêtes sous-requêtes ou requêtes imbriquées............................................. 26 2.3.8.1. Les requête sous-requêtes ................................................................................ 26 2.3.8.2. Les requête imbriquées .................................................................................... 26
2.3.9. Les requêtes dépendantes ou corrélées................................................................. 27 2.3.10. L’autojointure....................................................................................................... 28 2.3.11. Union, Intersection et Différence ......................................................................... 28
2.3.11.1. Union ............................................................................................................... 29 2.3.11.2. Intersection....................................................................................................... 29 2.3.11.3. Différence ........................................................................................................ 29
3. Les Tables de l’exemple. ....................................................30 3.1. Table Ingrédients :........................................................................................................ 30 3.2. Table Recette................................................................................................................ 30
3.3. Table Types : ................................................................................................................ 30 3.4. Table Couts .................................................................................................................. 30 3.5. Table Difficultes :......................................................................................................... 31 3.6. Table Contenir.............................................................................................................. 31 3.7. Table Chefs .................................................................................................................. 31 3.8. Le MCD........................................................................................................................ 32 3.9. Le MLD Graphique...................................................................................................... 32
Page 1/32 Année 2003-2004 SG - LMS
1. Préambule Pour ce cours, considérons le schéma relationnel suivant, qui décrit la base de
données CUISINE, et qui nous servira d’exemple :
MLD Ingrédients (Code_Ingrédient, Nom_Ingrédient) Contenir (#Code_Ingrédient, #Code_Recette, Quantite,Unite_quantite) Types (Code_Type, Libelle_Type ) Couts (Code_Cout, Libelle_Cout) Difficultes( Code_Difficulte, Libelle_Difficulte ) Recettes (Code_Recette, Nom_Recette, Nb_Personnes, Preparation, Cuisson, Repos, #Code_Type, #Code_Cout, #Code_Difficulte, #Code_Chef ) Chefs (Code_Chef, Nom_Chef, Prenom_Chef, Date_Naiss_Chef, Adresse_Chef, Telephone_Chef)
Rappels des vocabulaire
Algèbre Relationnelle MCD MLD MPD
Relation Entité/Association Relation Table
Clé primaire Identifiant Identifiant Clé primaire
Attribut Propriété/Caractéristique Propriété Colonne
Tuple Occurrence/Tuple Enregistrement Enregistrement/Ligne
2. Le langage SQL SQL ( Structured Query Language) a une triple fonction .
• Interrogation : et modification d'une base de données relationnelle
• Définition et modification du schéma d'une base de données relationnelle
• Contrôle de sécurité et d'intégrité de la base.
2.1. Le standard SQL La norme définit deux langages SQL :
• Un langage de définition de données, SQL-LDD, à utiliser pour déclarer les structures logiques de données et leurs contraintes d'intégrité. La gestion des accès aux données est précisée en langage de contrôle de données: SQL-LCD
• Un langage de manipulation de données SQL-LMD, pour supprimer, ajouter ou modifier des données. On peut en plus, distinguer la partie interrogation (LID) de la partie mise à jour.
Page 2/32 Année 2003-2004 SG - LMS
Les principales instructions du langage SQL
LMD LDD
LID LCD
SELECT INSERT
UPDATE
DELETE
TRUNCATE1
GRANT
REVOKE
CREATE
ALTER
DROP
RENAME
2.2. Les limites de SQL
2.2.1. Langage non procédural Le langage SQL présente des limites. SQL est un langage de requête, non un langage de
programmation: il est non procédural.
En outre, SQL ignore la notion de variables dimensionnée et indicée, de format, les affectations, les entrées-sorties, etc.
Toutes ces notions étant nécessaires, certains fournisseurs ont fait des ajouts à SQL, par exemple le SQL *Plus et PL/SQL de la société Oracle, ou TRANSAC-SQL de SQL Server.
2.2.2. SQL : une portabilité limitée Lorsqu'une application utilise une base de données, sa portabilité concerne les domaines
suivants : • portabilité des données vers des matériels différents, où leur représentation est
différente, • portabilité de l'architecture physique de la base, • portabilité des requêtes d'accès au SGBD avec sous-jacent le problème des types de
données, • portabilité des permissions administratives d'accès.
Ce qui est le plus portable dans SQL, c'est sa philosophie de modèle tabulaire de données, où l'on peut accéder aux informations par le contenu.
Dans une moindre mesure, la manipulation simple de données est portable. Dans une mesure encore moindre, la définition des données est réutilisable d'un SGBDR à l'autre.
Mais en pratique le portage demandera encore longtemps pas mal d'attention et d'efforts humains, du fait des différences entre les différents SGBDR commercialisés par les éditeurs.
2.3. SQL : un Langage de Manipulation des Données : Le LID Le LID permet d’extraire des informations d’une base de données et de les afficher sous forme
de tableau. Le LID n’est constitué que de la commande Select.
1 Ordre spécifique à ORACLE
Page 3/32 Année 2003-2004 SG - LMS
Notation utilisée : • Les mots réservés SQL sont imprimés en lettres majuscules. • Les noms symboliques ou expressions spécifiques à écrire par le programmeur sont
imprimés en caractères minuscules. Les symboles < et > correspondent à la possibilité de répéter plusieurs fois l’expression séparée par une virgule.
• Les expressions facultatives sont éditées entre crochets [ ]. • Lorsqu'on peut choisir entre plusieurs expressions mais que, au moins l'une d'elles est
obligatoire, elles sont regroupées entre accolades { }. • L'alternative entre expressions est symbolisée par | • Le souligné en tirets indique le défaut.
Le langage de manipulation de données contient le langage d’interrogation de données (LID), ce dernier permet, comme son nom l’indique, d’interroger une base de données. Avec le langage SQL, c’est la commande SELECT qui constitue le langage d’interrogation de la base. Ce langage permet de :
• Sélectionner certaines colonnes d’une table • Sélectionner certaines lignes d’une table (à partir de leur contenu) • Combiner des informations venant de plusieurs tables.
La syntaxe élémentaire comporte les deux clauses indiquant quels colonne (clause SELECT) sont à extraire et de quelle(s) tables(s) (clause FROM). Couper la phrase pour aller à la ligne ne change rien au résultat.
SELECT NomColonne FROM NomTable ;
2.3.1. Projection (La commande SELECT… FROM…) Une projection s'exprime à l'aide du langage SQL par la clause :
SELECT [DISTINCT] { * | <nom_de_colonne>}
FROM <nom_de_table> ;
Toujours terminer un ordre SQL avec un point virgule.
SQL n'élimine pas les doubles à moins que ceci soit explicitement demandé par le mot clé DISTINCT.
Exemple : Liste des noms des recettes de la base :
SELECT Nom_Recette FROM Recettes;
Résultat :
Combien obtenez vous d’enregistrements ?
Attention : l’ordre dans lequel vous écrivez les colonnes détermine le résultat de l’affichage.
Page 4/32 Année 2003-2004 SG - LMS
Exercice: Donner la requête permettant d’obtenir la liste des noms des ingrédients.
2.3.1.1. Le symbole * Le symbole * : signifie que l'on affiche toutes les colonnes d'une table.
Exemple : Liste de toutes les recettes.
SELECT * FROM Recettes;
Résultat:
Exercice: Ecrire la requête permettant d’obtenir la liste de tous les ingrédients
2.3.1.2. La clause AS La clause AS lors d’une projection permet de nommer les colonnes de la table autrement que
par le nom de colonne enregistré dans la table, ce nom doit être unique.
Ce n’est pas une clause du SQL standard, elle est utilisée sous Access. Pour accepter les nouveaux noms avec des blancs, Access impose les crochets.
SELECT [DISTINCT] { * | <nom_de_colonne>} [ AS nom_alias ]
FROM <nom_de_table> ;
Exemple: Liste des type de plats :
SELECT Libelle_Type AS Type_de_plat , code_type AS Numero
FROM Types ;
Résultat :
Page 5/32 Année 2003-2004 SG - LMS
2.3.1.3. Les champs calculés. Le champ calculé résulte d’une opération effectuée sur une colonne numérique existante d’une
table.
Exemple : Présenter une augmentation de 10% du temps de préparation par recette
SELECT Nom_recette, Preparation*1.10 FROM Recettes ;
Résultat :
Exercice : Nous voulons cette fois une diminution de 10% du temps de préparation :
Résultat
La requête suivante fonctionne-t-elle ?
SELECT Nom_recette, Preparation * 1.10 as NouveauTempsPreparation,
NouveauTempsPreparation / 60 as TempsPreparationEnHeure FROM Recettes ;
Exercice : Afficher pour chaque recette le double du temps de repos avec entête de colonne résultante.
2.3.1.4. Concaténation de chaînes de caractères Concaténation des colonnes avec du texte. Il est possible d’ajouter un libellé lors de l’affichage
des colonnes en utilisant le symbole ‘||’ pour Oracle ou ‘&’ pour Access :
Exemple : Ajouter le mot environ après l’affichage de la colonne unite_quantite de la table Contenir.
SELECT Code_Recette, Quantite, unite_quantite || ‘ environ ‘ FROM Contenir ;
Page 6/32 Année 2003-2004 SG - LMS
Extrait du résultat :
Exercice : Afficher le mot « très », juste avant la colonne Libelle_Cout de la table Couts :
2.3.1.5. Les agrégats Les AGREGATS permettent d’agréger plusieurs données en une seule. Les agrégats les plus
utilisés sur les colonnes en SQL sont :
MIN : correspond à la plus petite valeur de la colonne
MAX : affiche la plus grande valeur de la colonne
AVG : indique la moyenne (average) de toutes les données d’une seule colonne
SUM : affiche la somme des valeurs d’une colonne (attention la colonne doit être numérique avec aucun champ à NULL)
COUNT : Compte le nombre de lignes d’une colonne quelles que soient ses valeurs.
Bien retenir que l’utilisation des agrégats au niveau des colonnes ne ramène qu’un seul nombre. C’est la fonction même d’une agrégation.
Exemple : Combien d’enregistrements avons nous dans la table Ingredients ?
SELECT count(*) FROM Ingredients ;
Résultat :
27
Exemple : Combien avons-nous de noms de recette dans la table Recettes ?
SELECT count (Nom_Recette) FROM Recettes ;
Exercice : Afficher la quantité mini, maxi et moyenne des ingrédients de la table Contenir.
Page 7/32 Année 2003-2004 SG - LMS
NB : Quel résultat obtient-on après avoir lancé la requête suivante ?
SELECT MIN(quantite) as Minimum, code_ingredient as ingredient_Correspondant
FROM Contenir ;
Exercice : Indiquer le nombre maximum de personnes prévues dans les recettes .
2.3.2. Sélection ou restriction (La commande SELECT … FROM … WHERE … ) Une sélection ou restriction s'exprime par un bloc d'instructions du type :
SELECT [DISTINCT] { * | <nom_de_colonne>}
FROM <nom_des_tables>
WHERE <conditions de selection>;
La combinaison avec une projection s'effectue en remplaçant * par la liste des colonnes à projeter.
Attention : Dans la clause WHERE les sélections de caractères ou de dates se font entre guillemets, les sélections
de nombre se font sans guillemets.
Le format standard retenu dans ce cours pour la date sera JJ/MM/AAAA. Il est à noter que le format de la date varie selon les SGBD utilisés.
Exemple: La liste des recettes prévues pour 4 personnes
SELECT * FROM Recettes WHERE Nb_Personnes = 4;
Résultat :
Exercice : Donner la liste code de recette dont le libellé est TAPENADE.
Page 8/32 Année 2003-2004 SG - LMS
2.3.3. Clauses complémentaires La clause SELECT… FROM…WHERE… peut-être enrichie par diverses clauses
complémentaires.
2.3.3.1. La clause DISTINCT La clause SELECT… FROM…WHERE… peut-être enrichie par le complément DISTINCT
Les doublons s’appliquent à une ligne et non à un champ.
L’ordre SQL:
SELECT distinct Nom_Chef, Prenom_Chef FROM Chefs ;
Est équivalent à sous ACCESS (sous Oracle cela génère une erreur) :
SELECT distinct (Nom_Chef, Prenom_Chef) FROM Chefs ;
La requête suivante fonctionne sous Oracle mais ne fonctionne pas sous Access :
SELECT count (distinct Prenom_Chef) FROM Chefs;
N’oubliez pas DISTINCT quand il est nécessaire, c’est le petit détail qui évite bien des erreurs.
DISTINCT n’est pas à utiliser lorsque l’on souhaite tous les enregistrements (doublons compris) comme par exemple la liste des Clients ou Fournisseurs dans les exercice standards.
Exemple: Ecrire la requête donnant la liste des prénoms des chefs
SELECT DISTINCT Prenom_Chef FROM Chefs;
Résultat :
Exemple: Ecrire la requête donnant la liste des prénoms de la table Chef
SELECT Prenom_Chef FROM Chefs ;
Résultat :
Exercice: Liste des différents Noms des chefs cuisiniers
Page 9/32 Année 2003-2004 SG - LMS
2.3.3.2. La clause ORDER BY Cette clause permet de trier le résultat d’une requête par ordre croissant (ASCendant) ou
décroissant (DESCendant). Dans le cas d’un tri en ordre croissant, ASC est facultatif car il est le défaut.
Pour du numérique le tri ASC correspond aux nombres classés du plus petit au plus grand (du négatif au positif). Pour l’alphabétique le tri ASC correspond à l’ordre lexicographique (du dictionnaire). Pour les dates le tri ASC correspond du plus ancien au plus récent.
NB : Les valeurs numériques stockées en chaîne de caractères sont triés par ordre alphabétique ‘1’, ‘10’, ‘100’,’2’, ‘20’, ‘21’…
Le Tri sur un champ calculé se fait en rappelant dans le ORDER BY le champ calculé.
Exemple : Présenter la quantité des ingrédients augmentée de 10% classé par unité des ingrédients par ordre alphabétique et décroissant pour la nouvelle quantité.
SELECT Unite_Quantite, Quantite * 1.10 FROM Contenir
ORDER BY Unite_Quantite , Quantite * 1.10 DESC;
Extrait du Résultat :
Vous remarquez que « CS » (Cuillère à Soupe) est répété deux fois avec la même quantité ! qu’aurait-t’il fallu comme paramètre pour n’avoir qu’une seule ligne ?
Attention : Dans le ORDER BY la virgule des décimaux est interprétée comme séparateur de colonne il est
nécessaire d’utiliser le point.
Exercice : Trier le nom des chefs dans l’ordre croissant de leur nom et prénom du plus jeune au plus vieux.
Vous obtenez ce résultat :
Page 10/32 Année 2003-2004 SG - LMS
Que remarquez-vous ?
Exercice :
Que faudrait-il écrire pour avoir réellement ce classement du plus jeune au plus vieux ?
Remarque il est également possible de ne pas recopier le nom des colonnes à trier en respectant l’ordre d’affichage des colonnes du SELECT :
Exemple :
SELECT Unite_Quantite, Quantite * 1.10 FROM Contenir
ORDER BY 1, 2 DESC ;
2.3.3.3. La clause GROUP BY (le partitionnement) Elle permet de constituer des sous-ensembles d’enregistrements dans une requête.
Noter que chaque colonne citée dans le SELECT hormis les agrégats doit être incluse dans le GROUP BY. Mais le GROUP BY peut contenir des colonnes supplémentaires par
rapport au SELECT. La clause Group by ne doit contenir que des champs de la clause SELECT.
Attention : Ne faire des regroupements que sur des colonnes significatives, comme un identifiant par exemple,
pour éviter les homonymes.
Exemple : Indiquer le poids total d’ingrédients en grammes . par recette
SELECT Code_Recette, SUM(Quantite) FROM Contenir
WHERE Unite_Quantite = ‘Gr’
GROUP BY Code_Recette ;
Résultat :
Page 11/32 Année 2003-2004 SG - LMS
Exercice : Ajoutez la colonne « Unite_Quantite » lors de l’affichage de la requête précédente (pour bien préciser que ce sont des grammes) :
Autre exercice : Donner la liste des recettes par nombre de personnes :
2.3.3.4. La clause HAVING C’est l’équivalent de la clause WHERE pour un GROUP BY. Cela sert de critère de sélection
pour les regoupements. Ils s’appliquent en général sur les agrégats.
Exemple : : Indiquer les recettes dont le poids total en grammes est supérieur à 230.
SELECT Code_Recette, SUM(Quantite) AS Somme_Qte FROM Contenir
WHERE Unite_Quantite = ‘Gr’
GROUP BY Code_Recette
HAVING SUM(Quantite) > 230;
Résultat :
Exercice Donnez la liste des recettes par nombre de personnes et dont le nombre de personnes
ne dépasse pas 5
Attention : La clause HAVING doit suivre la clause GROUP BY
et doit s’appliquer à un regroupement de données sur lequel on a pratiqué un GROUP BY. Toutes les colonnes du HAVING doivent figurer dans le SELECT précédent.
Page 12/32 Année 2003-2004 SG - LMS
2.3.4. Les Différents opérateurs
2.3.4.1. Les opérateurs de comparaisons : Opérateur Description
= Egal à
!= ou <> Différent de
> Supérieur à
>= Supérieur ou Egal à
< Inférieur à
<= Inférieur ou Egal à
Like Comme
Pour l'opérateur LIKE vous pouvez utilisez des caractères « Joker ». Cet opérateur est très utile pour effectuer des recherches dans des chaînes alphanumériques.
Il est à noter également que selon les SGBD les « Jokers » peuvent varier.
Sous Access :
* : remplace de 0 à n caractères quelconques exemple : like 'M*'
? : remplace un caractère quelconque et un seul exemple : like ' ??A'
Sous Oracle :
% : remplace de 0 à n caractères quelconques exemple : like 'M%'
_ : remplace un caractère quelconque et un seul exemple : like ' _ _A'
Exemple : Liste recettes commençant par la lettre T
SELECT * FROM Recettes
Where Nom_Recette like "T*" ;
Résultat :
Si on remplace le LIKE par = quel résultat obtient-on ?
Exercice : Liste des noms des Ingrédients de 3 lettres.
Remarquer que sous Access les majuscules ne se distingue pas des minuscules, mais sous Oracle la casse est à respecter.
Page 13/32 Année 2003-2004 SG - LMS
2.3.4.2. Les opérateurs logiques Opérateur Description
Not exprime le contraire de la condition
And les deux conditions doivent être vérifiées simultanément
Or au moins une des deux conditions doit être vérifiée
Les opérateurs logiques s’utilisent après la clause WHERE vous ne pouvez les utiliser juste après le SELECT.
Exercice: Liste des Recettes dont le code est supérieur à 2 :
2.3.4.3. Les opérateurs arithmétiques Opérateur Description
+ et - Addition et Soustraction
* et / Multiplication et Division
** et ^ Exponentiation
La difficulté dans l’utilisation des opérateurs arithmétiques consiste à se demander s’il faut les utiliser dans le SELECT ou après le WHERE.
Après le SELECT, l’opération concerne tous les enregistrements :
Exemple : Indiquer le temps total de préparation, de cuisson et de repos pour chaque recette :
SELECT Nom_Recette, Preparation+Cuisson+Repos as Temps_Total
FROM Recettes ;
Résultat :
*
Exemple : Indiquer le temps de préparation, de cuisson et de repos pour chaque recette dont le total de préparation est strictement supérieur à 40 mn:
SELECT Nom_Recette, Preparation, Cuisson, Repos FROM Recettes
WHERE PREPARATION+REPOS+CUISSON > 40 ;
Page 14/32 Année 2003-2004 SG - LMS
Résultat :
Exercice : Indiquer le temps moyen pour réaliser une recette :
Ou
2.3.4.4. Les opérateurs Spéciaux
Opérateur Description
Between … AND … Entre … et …
In Dans
Is Null (le champ) Est indéfini ou ne contient aucune donnée
Any Au moins 1
All Tout
2.3.4.4.1. BETWEEN … AND L’utilisation de BETWEEN dans la clause WHERE permet de déterminer un intervalle.
Exemple : Liste des Chefs nés entre 1945 et 1955
Syntaxe sous Oracle :
SELECT * FROM Chefs WHERE Date_Naiss_Chef BETWEEN ‘01/01/1945’ AND ‘31/12/1955’;
Syntaxe sous Access :
SELECT * FROM Chefs
WHERE Date_Naiss_Chef BETWEEN #01/01/1945# AND #31/12/1955#;
Page 15/32 Année 2003-2004 SG - LMS
Exercice : Indiquer les recettes dont le temps de préparation se situe entre 0 et 15 mn:
2.3.4.4.2. IN L’utilisation de IN Dans la clause WHERE permet de rechercher une valeur dans un ensemble
de valeurs données.
Exemple : Liste des informations des Chefs dont le nom est soit GREGOIRE soit PINEL
SELECT * FROM Chefs
WHERE Nom_Chef IN (‘GREGOIRE’, ‘PINEL’) ;
Résultat :
Exercice : Liste des recettes dont le temps de préparation est soit 10 mn ou 20 mn.
2.3.4.5. L’opérateur de concaténation L'opérateur de concaténation symbolisé par la double barre verticale (AltGR+6 sur votre
clavier ou Alt+0124) est disponible avec Oracle. Le symbole & est disponible pour Access. Une fonction Concat est également disponible.
Il permet de présenter un résultat de deux colonnes sur une seule colonne.
Exemple : Liste avec flèche des codes recettes le nom de la recette.
SELECT Code_Recette Il'->' Il Nom_Recette FROM Recettes ;
Résultat :
Page 16/32 Année 2003-2004 SG - LMS
2.3.5. Les fonctions
2.3.5.1. les fonctions arithmétiques Attention :
certaines fonctions qui suivent ne sont pas applicables à tous les SGBD (Notamment pour Access).
ABS (n) : Valeur absolue de n
ABS(-23.5) = 23.5
MOD (M,N) : Reste de la division entière de M par N
MOD(7,2) = 1
SIGN (n) : signe d'un entier: -1 si n<0, a si n=0, 1 si n>0
SIGN(-23.5) = -1
SIGN(0) = 0
SIGN(+23.5) = +1
POWER (M,N) : puissance
POWER(2,3) = 8
SQRT (n) : racine carrée de n
SQRT(16) = 4
SQRT(-16) = NULL
ROUND(N,[M]) : arrondi d'un nombre
ROUND(23.462,2)=23.46
ROUND(23.466,2)=23.47
ROUND(1200,-3)=1000
ROUND(1500,-3)=2000
TRUNC(N,[M]): troncature d'un nombre (partie entière si M=0)
TRUNC(23.462,2)=23.46
TRUNC(23.466,2)=23.46
TRUNC(1200,-3)=1000
SUBSTR (chaîne, position, longueur) : extraire une chaîne
SUBSTR('MARTIN',2,3) = 'ART'
Exemple sous Access : Ne retenir que l’arrondi par défaut du temps moyen temps de préparation+Cuisson+Repos :
SELECT Nom_Recette,ROUND ( (Preparation+Cuisson+Repos)/3 , 2)
FROM Recettes;Les fonctions sur les caractères
Page 17/32 Année 2003-2004 SG - LMS
CHR (n) : retourne le caractère de la valeur ASCII n
CHR(65) donne 'A'
UPPER (chaîne) : transformation en majuscule
UPPER('Dupont') = 'DUPONT'
LOWER ( chaîne) : transformation en minuscule
LOWER(' Dupont') donne ' dupont'
INITCAP (chaîne) : première lettre en majuscule, reste minuscule
INITCAP('dupoNT') donne ' Dupont'
LENGTH (chaîne) : longueur d'une chaîne de caractères
LENGTH('DUPONT') donne 6
LPAD(chaîne,longueur,caractère) = compléter par la gauche
LPAD('Dupont',10,'*') donne '****Dupont'
RPAD(chaîne,longueur,caractère) = compléter par la droite
RPAD('Dupont',10,'*') donne 'Dupont****'
SOUNDEX (chaîne) : fonction de comparaison phonétique
nompilote=SOUNDEX(' Dupont')
Cette expression est vraie pour tous les noms de pilote dont la prononciation est proche de 'Dupont'.
2.3.5.2. Expression sur les dates Le mot clé SYSDATE correspond à la date en cours chez Oracle, le mot Date() correspond à la
date du jour sous Access.
Exemple sous Oracle :
SELECT TO_CHAR(SYSDATE, 'dd-mon-yyyy HH:MI:SS AM') FROM dual;
Résultat :
Dual est une table fictive appelée ‘Dummy table’ sous Oracle. Les dates sont ordonnées
chronologiquement du passé vers l’avenir, c’est pourquoi les dates du passé sont inférieures aux dates récentes. Cette notion est à appliquer pour toutes les dates (dates d’achat, date de ventes etc.). La date est un entier dont la date de référence est le 01/01/1900. Il est donc possible de faire des comparaisons sur les dates : >, <, <=, >=, <>.
Exemples
Date1 + Nombre = Date2 (attention le nombre correspond aux jours pour une date !!!)
'10/10/2007' + 3 = ' 13/10/2007'
Page 18/32 Année 2003-2004 SG - LMS
Date 1 - Nombre = Date2
'10/10/2007' - 3 = ' 07/10/2007'
Date1 - Date2 = Nombre
'10/10/2007' - ' 07/10/2007' = 3
L’année, le mois ou le jour peut être extrait de la date à l’aide des mots suivants : YEAR, MONTH, DAY ainsi :
Year (#25/10/2007#) = 2007 ; Year (DateNaissEntneur) = 2007; Month (#25/10/2007#) = 10 ; Month (DateNaissEntneur) = 10; Day (#25/10/2007#) = 25; Day (DateNaissEntneur) = 25;
Year (Date())+1 : l’année prochaine ; Month (Date()) +1 : le mois prochain ; Day (Date()) –1 : hier
Ces fonctions peuvent être utilisées lors d’une projection (colonne calculée) dans le SELECT ou dans une sélection, clause WHERE.
Sous Oracle :
To_CHAR(‘25/10/2005’,’yyyy’) = 2005 ; To_CHAR(DateNaissEntneur,’yyyy’) = 2005 ; To_CHAR (‘25/10/2005’,’mm’) = 10 ; To_CHAR (DateNaissEntneur,’mm’) = 10 ; To_CHAR (‘25/10/2005’,’dd’) = 25 ; To_CHAR (DateNaissEntneur,’dd’) = 25 ;
To_CHAR(sysdate,’yyyy’) + 1 : l’année prochaine ; To_CHAR(sysdate,’mm’) + 1 : le mois prochain ; To_CHAR(sysdate,’dd’) – 1 : hier (extraction de la date en chiffre) To_CHAR(sysdate,’day’) affiche le jour exact de la journée en cours (ex: lundi) TO_CHAR(sysdate, 'HH:MI:SS') affiche l’heure, les minutes et secondes
Exemple : Indiquer les Chefs nés en novembre sous Access.
SELECT * FROM Chefs WHERE Month(Date_Naiss_Chef) = 11 ;
ou :
SELECT * FROM Chefs WHERE Date_Naiss_Chef like #*/11/*#;
Sous Oracle :
select * FROM Chefs WHERE to_CHAR(Date_Naiss_Chef ,'mm') = 11 ;
ou:
select * FROM Chefs WHERE to_CHAR(Date_Naiss_Chef like '%/12/%' ;
Résultat :
Page 19/32 Année 2003-2004 SG - LMS
Attention : Les requêtes avec « joker » ne fonctionne qu’avec * ou % et non avec ? ou _
Exercice: Liste des noms des chefs dont l’âge est compris entre à 40 et 60 ans (sous Access).
Sous Oracle nous obtenons :
Exercice : Indiquer quel est la date de naissance du plus jeune chef.
2.3.5.3. Expression sur les heures L’heure est définie en HH :MM :SS. l’heure est aussi à précéder et succéder de # sous Access
ou avec TO_CHAR pour Oracle.
2.3.6. Jointure Un cas particulier simple de jointure sans condition est le produit cartésien.
Définition : Le produit cartésien, de deux tables R et S, est une table T ayant pour colonne la concaténation (l’association) des colonnes de R et S. Tous les enregistrements de T sont obtenus par la concaténation de chaque enregistrement de R à tous les enregistrements de S. La table T contient donc R * S lignes.
Page 20/32 Année 2003-2004 SG - LMS
2.3.6.1. La jointure de 2 tables Avec SQL, le produit cartésien s'exprime en incluant plusieurs tables dans la clause FROM.
Pour réaliser la jointure on spécifiera la condition de la jointure dans la clause WHERE de la requête.
Lorsque l’on souhaite connaître la valeur d’un enregistrement lié à une autre table, on effectue la liaison avec la clé primaire et la clé étrangère.
Exemple : Afficher pour chaque nom de recette le libellé de son type
+
SELECT Nom_Recette, Libelle_Type
FROM Recettes, Types
WHERE Recettes.Code_Type= Types.Code_Type;
Résultat :
NB : Parce qu’il y a ambiguïté sur la colonne Code_Type (le même nom appartient à 2 tables),
il faut préfixer la colonne par sa table pour trouver l’égalité.
Question : Quel effet a la requête suivante : SELECT * FROM Recettes, Types ?
Exercice : Afficher pour chaque recette le libellé de sa difficulté.
Il est possible d’utiliser des alias pour renommer les tables pour le temps de la requête.
Exemple : (le même que précédemment)
SELECT Nom_Recette, Libelle_Type
FROM Recettes R, Types T
WHERE R.Code_Type= T.Code_Type;
Page 21/32 Année 2003-2004 SG - LMS
Exercice : Refaire l’exercice précédent avec des alias :
Exercice : La requête suivante est-elle correcte ?
SELECT Nom_Recette, Libelle_Cout
FROM Recettes R, Couts C
WHERE R.Code_Cout= Couts.Code_Cout;
2.3.6.2. La jointure de plusieurs tables On utilise les jointures de plusieurs tables lorsque l’on souhaite obtenir un lien entre des tables
reliées deux par deux. Nous travaillons alors sur les jointures Clé primaire/clé étrangère. La clause WHERE comporte alors une condition de jointure pour 2 tables, 2 conditions de jointure pour 3 tables etc.
Exemple : Donner la quantité et l’unité de quantité par Recette (Nom_Recette) et ses ingrédients (Nom_Ingredient) :
SELECT Nom_Ingredient, Nom_Recette, Quantite , Unite_Quantite
FROM Ingredients I, Contenir C, Recettes R
WHERE I.Code_Ingredient = C.Code_Ingredient
AND C.Code_Recette=R.Code_Recette ;
Page 22/32 Année 2003-2004 SG - LMS
Extrait du Résultat :
…
Exercice : Donner pour chaque nom de recette, le libellé de son type et le libellé de son coût :
2.3.6.3. Les jointures naturelles Une forme particulière de l'opération de jointure consiste à n'afficher qu'une des colonnes
communes aux deux tables. Il s'agit alors d'une jointure naturelle.
Exemple :
SELECT Nom_Recette, Nb_Personnes, Types.*
FROM Recettes, Types
WHERE Recettes.Code_Type = Types.Code_Type ;
2.3.6.4. La jointure externe Dans le cas d’une jointure classique, lorsqu’une ligne d’une table ne satisfait pas à la condition
de jointure, cette ligne n’apparaît pas dans le résultat final. Il peut cependant être souhaitable de conserver les lignes d’une table qui ne répondent pas à la condition de jointure. On parle alors de semi-jointure ou de jointure externe (outer join).
Page 23/32 Année 2003-2004 SG - LMS
Lorsque la requête est basée sur deux tables, deux semi-jointures peuvent être réalisées : la semi-jointure droite et la semi-jointure gauche. Dans l’exemple ci dessous, la semi-jointure gauche consiste à ajouter au résultat de la jointure normale, l’ensemble des Ingrédients qui n’auraient pas de nom d’ingrédients, ici ce n’est pas possible car nous avons une intégrité référentielle très forte. Ce résultat est obtenu en ajoutant le symbole (+) à côté de Code_Ingrédients.
Exemple : Quels sont les libellés de tous les codes_Ingrédient ?
SELECT *
FROM Ingredients, Contenir
WHERE Ingredients.Code_Ingredient = Contenir.Code_Ingredient(+);
Le signe (+) permet d’ajouter une ligne fictive vide (NULL), mais qui vérifie la condition de jointure. La semi-jointure droite consiste à ajouter (+) à côté de Ingredients.Code_Ingredient. Dans ce cas, il s’agirait d’afficher des Code_Ingredient possédant un numéro inexistant dans la table Ingredients. Cela est peu vraisemblable à cause de l’intégrité référentielle qu’il est nécessaire de vérifier entre Ingredients et Contenir.
La jointure généralisée consiste à faire apparaître les lignes des deux tables qui ne satisfont pas à la condition de jointure. La réalisation se fera par l’union de la semi-jointure droite et de la semi-jointure gauche.
SELECT * FROM table1, table2 WHERE colonne1 = colonne2(+)
UNION
SELECT * FROM table1, table2 WHERE colonne1(+)
2.3.7. Les techniques d’imbrication
2.3.7.1. Utilisation de la technique de l’imbrication Définition :Une requête imbriquée est composée de deux ou plusieurs SELECT. La première
est appelée requête principale, la ou les suivantes, sous-requêtes.
L’exécution se fait en deux temps :
D’abord les sous-requêtes qui extraient les valeurs intermédiaires
Puis la requête principale s’exécute sur les valeurs intermédiaires.
Le lien entre deux SELECT est réalisé par :
IN si la sous-requête fournit plusieurs valeurs
= si la sous-requête ne fournit qu’une seule valeur
2.3.7.2. Utilisation de l’opérateur IN, la formulation algébrique Le IN indique que l’on peut obtenir plus d’une valeur dans la requête imbriquée, on compare
donc une valeur à une liste de valeurs.
On peut également utiliser le NOT IN pour indiquer les valeurs que l’on ne veut pas retenir dans la requête imbriquée.
Page 24/32 Année 2003-2004 SG - LMS
Lorsque l’on utilise que des IN nous avons une Formulation algébrique
Exemple : Quelles sont les recettes réalisées par Guillaume OUDARD ?
SELECT Nom_Recette FROM Recettes
WHERE Code_Chef IN
(SELECT Code_Chef FROM Chefs
WHERE Nom_Chef = ‘OUDARD’
and Prenom_Chef = ‘Guillaume’) ;
Résultat :
Exercice : Quel sont les recettes dont le libellé de difficulté est facile ?
2.3.7.3. Utilisation de l’opérateur = L’opérateur égal indique que l’on obtient une et une seule valeur à l’issue de la requête
imbriquée. Le égal est à utiliser lorsque l’on est sûr (à l’aide du texte) que nous n’avons qu’une seule valeur réponse dans la table.
Exemple : Liste des recettes dont chef s’appelle « Bernard PINEL »
SELECT Code_Recette, Nom_Recette FROM Recettes
WHERE Code_Chef =
(SELECT Code_Chef FROM Chefs
WHERE Nom_Chef = ‘PINEL’
AND Prenom_Chef = ‘Bernard’) ;
Résultat :
Remarque : C’est parce qu’on est sûr qu’il n’y a qu’un seul Chef qui s’appelle Bernard PINEL
que l’on peut utiliser l’opérateur égal.
Exercice : Quel est le nom et le prénom du seul chef de la recette « Crêpes au chocolat ».
Page 25/32 Année 2003-2004 SG - LMS
A la place de IN, il est possible d’utiliser ANY, ALL ou EXISTS : ces mots clés doivent être utilisés lorsque la sous-requête est susceptible de retourner plusieurs résultats.
2.3.7.4. Utilisation de l’opérateur ANY Définition : Une valeur est inférieure à ANY (x < ANY) si la valeur est inférieure à au moins
une valeur quelconque de l’ensemble. ANY détermine si la condition WHERE est vérifiée pour au moins une des valeurs renvoyées par la sous-requête.
NB : x < ANY (SELECT…) signifie x < (SELECT MAX…). x > ANY (SELECT…) signifie x > (SELECT MIN… ) x = ANY (SELECT…) signifie x IN (SELECT …)
Exemple : Liste des chefs qui ont au moins une recette à proposer.
SELECT Nom_Chef, Prenom_chef FROM Chefs WHERE Code_chef = ANY
(SELECT Code_chef FROM Recettes ) ;
Exercice : Liste des recettes où il y a au moins une heure de repos :
2.3.7.5. Utilisation de l’opérateur ALL Définition : Une valeur est inférieure à ALL (x < ALL) si toutes la valeur est inférieure à toutes
les valeurs retournée par le select imbriqué. ALL détermine si la condition WHERE est vérifiée pour toutes les valeurs renvoyées par une sous-requête.
NB : x < ALL (SELECT…) signifie x < (SELECT MIN…) x > ALL (SELECT…) signifie x > (SELECT MAX…) x <> ALL (SELECT…) signifie x NOT IN (SELECT …)
Exemple : Liste des codes recette dont la préparation est supérieure à 60 mn
SELECT code_recette from Recette
WHERE preparation > ALL (select preparation from Recette
WHERE preparation = 60) ;
Exercice : Liste des Recette dont la préparation est supérieure à « la tapenade »
Page 26/32 Année 2003-2004 SG - LMS
2.3.7.6. Utilisation de l’opérateur EXISTS Définition : EXISTS permet de créer une sous-requête qui renvoie Vrai si elle retourne des
valeurs, sinon elle retourne Faux. EXISTS permet d’évaluer la clause WHERE c’est à dire si l’expression logique est vérifiée pour cet enregistrement, ce dernier est conservé.
Exemple : Liste des Chefs qui ne font pas de recettes.
SELECT * FROM Recettes
WHERE NOT EXISTS (SELECT Code_chef FROM Chefs);
Exercice : Liste des Recettes dont le code difficulté est « Difficile ».
2.3.8. Les requêtes sous-requêtes ou requêtes imbriquées Avec le langage SQL, vous pouvez très facilement combiner les opérations de jointure,
sélection et projection. Il est également possible d'imbriquer des blocs SELECT. ..FROM ...WHERE à plusieurs niveaux. Les requêtes peuvent être formulées à l’aide de la formulation prédicative, de la formulation algébrique ou de la formulation mixte.
2.3.8.1. Les requête sous-requêtes
Exercice : Quels sont les chef plus jeunes que Philippe PINEL ?
2.3.8.2. Les requête imbriquées Il existe 3 types de formulations de requêtes imbriquées :
La formulation prédicative qui est une formulation sans les IN
La formulation algébrique qui n’utilise que des IN
La formulation mixte qui utilise une imbrication simple et une imbrication avec IN.
Prenons l’exemple de la requête : Le chef OUDARD utilise quels ingrédients dans ses recettes ?
Page 27/32 Année 2003-2004 SG - LMS
La formulation prédicative est la suivante :
SELECT Nom_Ingredient
FROM Ingredients, Contenir, Recettes , Chefs
WHERE Ingredients.Code_Ingredient = Contenir.Code_Ingredient
AND Contenir.Code_Recette = Recettes.Code_Recette
AND Recettes.Code_Chef = Chefs.Code_Chef
AND Nom_Chef = "OUDARD";
La formulation algébrique est la suivante :
SELECT Nom_Ingredient
FROM Ingredients
WHERE Code_Ingredient IN
(SELECT Code_Ingredient FROM Contenir
WHERE Code_Recette IN
(SELECT Code_Recette FROM Recettes
WHERE Code_Chef IN
(SELECT Code_Chef FROM Chefs
WHERE Nom_Chef = "OUDARD")));
La formulation mixte est la suivante :
SELECT Nom_Ingredient
FROM Ingredients, Contenir
WHERE Ingredients.Code_Ingredient = Contenir.Code_Ingredient
AND Contenir.Code_Recette in
(SELECT Code_Recette FROM Recettes
WHERE Code_Chef IN
(SELECT Code_Chef FROM Chefs
WHERE Nom_Chef = "OUDARD"));
2.3.9. Les requêtes dépendantes ou corrélées Elle constitue une forme très puissante de requêtes.
La requête principale (ou une sous-requête s’il y a plusieurs niveaux) fournit un ensemble de valeurs à la requête imbriquée. Pour chacune de ces valeurs, la requête imbriquée est évaluée.
Exemple : Liste du nombres de personnes le plus élevé par recette.
Page 28/32 Année 2003-2004 SG - LMS
SELECT Code_Recette, Nb_Personnes FROM Recettes A
WHERE Nb_Personnes =
(SELECT max(Nb_Personnes) FROM Recettes B
WHERE A.Code_Recette= B.Code_Recette);
2.3.10. L’autojointure Une autre forme de jointure dont on a besoin est l'autojointure.
L'autojointure met en corrélation les lignes d'une table avec d'autres lignes de la même table. Elle permet donc de ramener sur la même ligne de résultat des informations venant d'une ligne plus des informations venant d'une autre ligne de la même table.
La jointure d'une table à elle-même n'est possible qu'a condition d'utiliser des "alias" ou abréviations de table pour faire référence à une même table sous des noms différents.
L'utilisation d'un alias (ou nom d'emprunt ou synonyme) permet de renommer une des tables et évite les problèmes d'ambiguïté pour les noms de colonnes qui doivent être préfixées par le synonyme des différentes tables.
Exemple : Afficher les Codes Recettes qui utilisent les mêmes ingrédients
SELECT Code_Recette, Code_Ingredient FROM Contenir A
WHERE A.Code_Ingredient in
(SELECT Code_Ingredient FROM Contenir B
WHERE A.Code_Recette <> B.Code_Recette ) ;
Exercice : Afficher les recettes qui utilisent les mêmes nombres de personnes:
2.3.11. Union, Intersection et Différence Syntaxe :
SELECT ... Opérateur ensembliste SELECT ...
Règles :
1. Même nombre de colonnes, de types identiques dans chacun des select
2. Les doublons sont éliminés (distinct implicite)
3. Les titres des colonnes sont identiques à ceux du premier Select.
4. La largeur de la colonne est égale à la plus grande de tous les select.
5. La clause Order By fait référence au numéro de colonne et non plus à son nom.
Page 29/32 Année 2003-2004 SG - LMS
2.3.11.1. Union L'opérateur d'union est UNION.
Le résultat de l’union de deux requêtes est l’ensemble des enregistrements des deux requêtes. Les enregistrements des deux requêtes doivent avoir la même structure.
Exemple: Liste des ingrédients et des recettes commençant par la lettre C ou A.
SELECT Nom_Ingredient FROM Ingredients
Where Nom_Ingredient like “C*”
UNION
SELECT Nom_Ingredient FROM Ingredients
Where Nom_Ingredient like “A*”
2.3.11.2. Intersection L'opérateur d'intersection est: INTERSECT
Le résultat de l’intersection de deux requêtes est l’ensemble des enregistrements communes aux deux requêtes. Les enregistrements des deux requêtes doivent avoir la même structure.
2.3.11.3. Différence L'opérateur de différence est: MINUS (sous Oracle), sous Access Minus n’existe pas, il faut
utiliser NOT IN.
Requête 1 – Requête 2 est l’ensemble des enregistrements de la Requête 1 qui ne sont pas dans la Requête 2. Les deux requêtes doivent voir des enregistrements de même type.
Exemple: Liste des Chefs qui ne font pas la Tartiflette. (C’est en fait la liste de tous les chef , moins celui qui fait la tartiflette)
SELECT Code_Chef, Nom_Chef, Prenom_Chef
FROM Chefs, Recettes
WHERE Chefs.Code_chef = Recettes.Code_Chef
MINUS
SELECT Code_Chef, Nom_Chef, Prenom_Chef
FROM Chefs, Recettes
WHERE Chefs.Code_chef = Recettes.Code_Chef
AND Nom_Recette = ‘Tartiflette’;
Les requêtes jointures peuvent toujours être écrites avec des sous-requêtes. La lourdeur des sous-requêtes fait qu’il faut les réserver aux requêtes qui ne peuvent s’écrire autrement.
Pour chaque requête, posez-vous systématiquement la question : dois-je mettre un distinct ?
Page 30/32 Année 2003-2004 SG - LMS
3. Les Tables de l’exemple.
3.1. Table Ingrédients :
3.2. Table Recette
3.3. Table Types :
3.4. Table Couts
Page 31/32 Année 2003-2004 SG - LMS
3.5. Table Difficultes :
3.6. Table Contenir
3.7. Table Chefs
Page 32/32 Année 2003-2004 SG - LMS
3.8. Le MCD
3.9. Le MLD Graphique
Ingrédients Recettes
Code_Ingrédient
Nom_Ingrédient
Code_Recette
Nom_Recette
Nb_Personnes
Preparation
Cuisson
Repos
Contenir
Quantite
Unité_Qté 1,n 1, n
Types Difficultés Couts
Code_Type Libelle_Type
Code_Difficulte Libelle_Difficulte
Code_Cout Libelle_Cout
1, n
1, 1
1, 1 1, 1
1, n
1, n
Ingrédients Recettes
Code_Ingrédient
Nom_Ingrédient
Code_Recette
Nom_Recette
Nb_Personnes
Preparation
Cuisson
Repos
Contenir
Quantite
Unité_Qté
Types Difficultés Couts
Code_Type Libelle_Type
Code_Difficulte Libelle_Difficulte
Code_Cout Libelle_Cout
Fk_Code_Chef Fk_Code_Cout
Fk_Code_Difficulte
Fk_Code_Recette Fk_Code_Ingrédient
1, n
Chefs
Chefs
Code_Chef Nom_Chef Prenom_Chef Date_Naiss_Chef Adresse_Chef Telephone_Chef
Code_Chef Nom_Chef Prenom_Chef Date_Naiss_Chef Adresse_Chef Telephone_Chef
1, 1