UE215/535 – MSI
S03 – SQL
1
Plusieurs angles de vue
SQL=Structured Query Language
Pourquoi faire ?
– Pour définir
– Pour manipuler
– Pour contrôler (sécuriser)
… les données
UE215 / 535 2
…en d’autres termes
Français Anglais Principales commandes
LDD DDL
Langage de Définition des Data Definition Language Create, alter, drop
Données
LMD DML
Langage de Manipulation Data Manipulation Select, insert, update,
des Données Language delete
LCD DCL
Langage de Contrôle des Data Control Language Grant, revoke
Données
UE215 / 535 3
Rappel
Information =
Donnée + traitement + contexte d’utilisation
• Données = éléments stockés dans une BD
• Informations = éléments recherchés dans une BD
UE215 / 535 4
Requêtes
Dans un SGBDR, 2 types de requêtes :
– QBE (Query By Example) : graphique
– SQL : codé
UE215 / 535 5
Cas Ladore
Dans les exemples ci-après, nous utiliserons ce modèle relationnel qui
représente la gestion d’une facturation simple
Modèle relationnel:
• Client(Num_Client, Titre, Nom, Prenom, Adresse, CP, Ville, Date_Creation)
• Facture(Num_Facture, Date_Facture, #Num_Client)
• Ligne Facture(#Num_Facture,# Num_article, Qte)
• Article(Num_article, Libelle, PV_TTC, Marque, Code_famille)
6
Traduction en SQL
• Si l’on crée une requête c’est pour répondre à une
question:
– « Dans quelles villes habitent les clients ? »
– « Quelles sont les différentes marques d’articles ?»
• On peut formaliser les questions en :
Sélectionner <éléments> dans la <source>
– Sélectionner Ville dans la table Client
– Sélectionner Marque dans la table Article
UE215 / 535 7
Traduction en SQL
• On nettoie ensuite la phrase des mots qui ne
sont ni un nom de colonne ou de table ni une
instruction SQL:
– Sélectionner Ville dans la table Client
– Sélectionner Marque dans la table Article
• Et l’on traduit en anglais:
Sélectionner > SELECT
Dans > FROM
– SELECT Ville FROM Client
– SELECT Marque FROM Article
UE215 / 535 8
SQL
Sélectionner les données
UE215 / 535 9
Afficher les colonnes
SELECT * FROM Table
Afficher tous les champs de la table Client :
SELECT * FROM Client;
SELECT champs1, champs2 FROM Table :
une projection (réduire le nombre de colonnes)
Lister les villes où habitent les clients :
SELECT Ville FROM Client;
UE215 / 535 10
Sélectionner
SELECT champs1, champs2 FROM Table WHERE
< condition> : réduire le nombre de lignes
Quels sont les articles de la famille « AM » ?
SELECT Num_article, Libelle FROM Article WHERE
Famille=« AM »;
UE215 / 535 11
Opérateurs Where
Quels sont les clients habitant dans les villes de Gagny et Sevran ?
SELECT Nom, Prenom, Ville FROM Client WHERE Ville IN (« Gagny »,
« Sevran »);
Quels sont les clients créés au mois de novembre 2014 ?
SELECT Nom, Prenom, Date_creation FROM Client WHERE Date_creation
BETWEEN #01/11/2014# AND #30/11/2014#;
UE215 / 535 12
TRIER
SELECT champs1, champs2 FROM Table ORDER BY
champs1 ASC : tri croissant sur le champs 1
ASC : Croissant
DESC : Décroissant
Quels sont les clients créés au mois de novembre 2014, triés par ordre
alphabétique des noms ?
SELECT Nom, Prenom, Date_creation FROM Client WHERE Date_creation
BETWEEN #01/11/2014# AND #30/11/2014# ORDER BY Nom ASC;
UE215 / 535 13
DISTINCT
L’utilisation de la commande SELECT en SQL permet de lire
toutes les données d’une ou plusieurs colonnes.
Cette commande peut potentiellement afficher des lignes
en doubles. Pour éviter des redondances dans les résultats
il faut simplement ajouter DISTINCT après le mot SELECT.
SELECT DISTINCT ma_colonne FROM nom_du_tableau;
Cette requête sélectionne le champ “ma_colonne” de la
table “nom_du_tableau” en évitant de retourner des
doublons.
UE215 / 535 14
Exercices
Ecrire les requêtes SQL correspondant aux questions suivantes :
1. Afficher tous les enregistrements de la table Article
2. Dans quelles villes habitent les clients ?
3. Quels sont les clients habitant en Loire Atlantique ?
4. Quels sont les clients habitant dans les Pays de la Loire ?
Loire-Atlantique (44) Maine-et-Loire (49) Mayenne (53) Sarthe (72) Vendée (85)
5. Quelles sont les villes entre A et C du fichier Clients, triées
par ordre alphabétique décroissant et sans doublon ?
6. Quelles sont les familles d’articles ?
UE215 / 535 15
Exercices - Réponses
Ecrire les requêtes SQL correspondant aux questions suivantes :
1. Afficher tous les enregistrements de la table Article
SELECT * FROM Article;
2. Dans quelles villes habitent les clients ?
SELECT DISTINCT Ville FROM Client;
3. Quels sont les clients habitant en Loire Atlantique ?
SELECT DISTINCT Nom, Prenom, CP, Ville FROM Client WHERE CP Like "44*";
4. Quels sont les clients habitant dans les Pays de la Loire ?
Loire-Atlantique (44) Maine-et-Loire (49) Mayenne (53) Sarthe (72) Vendée (85)
SELECT DISTINCT [Link], [Link], CP FROM Client WHERE Left([CP],2) IN
(44,49,53,72,85);
5. Quelles sont les villes entre A et C du fichier Clients, triées par ordre
alphabétique décroissant et sans doublon ?
SELECT DISTINCT [Link] FROM Client WHERE (((Client.[Ville]) Between "A" And "D"))
ORDER BY [Link] DESC;
6. Quelles sont les familles d’articles ?
SELECT DISTINCT Code_Famille FROM Article;
UE215 / 535 16
Attributs calculés
Les champs calculés sont créés à partir des opérateurs :
+ , - , * , / , appliqués entre un champ numérique et
une constante ou entre deux champs numériques.
L’utilisation d’un alias permet de donner un intitulé à ce
nouveau champ.
• Exemple sur la base Ladoré :
SELECT Num_article, Libelle, PV_TTC, PV_TTC / 1.2 AS
PV_HT
FROM Article;
UE215 / 535 17
Agrégats - Calcul
SELECT COUNT(Champ) FROM Table;
• Exemple sur la base Ladoré :
Quel est le nombre de client ?
SELECT COUNT(Num_Client)
FROM Client;
UE215 / 535 18
Agrégats - Groupement
SELECT Champ1, COUNT(Champ2), Champ3
FROM Table
GROUP BY Champ1, Champ3;
• Exemple sur la base Ladoré :
Combien a-t-on de clients par ville ?
SELECT Ville, Count(Num_Client) AS Nombre_Client
FROM Client
GROUP BY Ville;
UE215 / 535 19
Restriction sur regroupement
SELECT Champ1, COUNT(Champ2), Champ3
FROM Table
GROUP BY Champ1, Champ3
HAVING Condition;
• Exemple sur la base Ladoré :
Quelles sont les villes où il y a plus de 10 clients ?
SELECT Ville, Count(Num_Client) AS Nbre_Client, Left(CP,2) as Dept
FROM Client
GROUP BY Ville, left(CP,2)
HAVING Count(Num_Client) > 10;
UE215 / 535 20
Sous-Requête
SELECT Champ1, Champ2, Champ3
FROM Table WHERE Champ4
IN (SELECT Champ2 FROM Table WHERE Champ)
Permet d’utiliser le résultat de la requête imbriquée
comme critère de la première requête.
Exemple sur la base Ladoré :
Quels sont les articles moins chers que l’article OV-22
dans la même famille?
UE215 / 535 21
Sous-Requête
Quels sont les articles moins chers que l’article OV-22
dans la même famille?
• Quel est le prix de l’article OV-22 ?
SELECT PV_TTC
FROM Article
WHERE Num_article="OV-22";
• Quels sont les articles de la famille OV ?
SELECT *
FROM Article
WHERE Code_Famille='OV’;
UE215 / 535 22
Sous-Requête
• Quels sont les articles moins chers que l’article
OV-22 dans la même famille?
SELECT *
FROM Article
WHERE CODE_Famille='OV‘
AND PV_TTC < (SELECT PV_TTC FROM Article WHERE
Num_article="OV-22");
UE215 / 535 23
Ordre des conditions
Série 2, partie 4, page 77
SELECT Champs projetés, Agrégats AS Alias
FROM Tables
WHERE Jointures ou Critère de sélection
AND Critère de sélection
GROUP BY Champs
HAVING Conditions
ORDER BY
UE215 / 535 24
Compter les occurrences
• Combien a-t-on émis de factures ?
SELECT COUNT(Num_Facture)
FROM Facture;
• Quelles sont les années de facturation?
SELECT
SELECT Year(Date_Facture)
DISTINCT AS Annee
Year(Date_Facture) AS, Annee
N°_Facture
FROM
FROMFacture;
Facture;
UE215 / 535 25
Compter les occurrences
• Combien de factures par année ?
SELECT DISTINCT Year(Date_Facture) AS Annee,
Count(Num_Facture) AS Nombre
FROM Facture
GROUP BY Year(Date_Facture);
• Quelles sont les factures émises en 2012?
SELECT *
FROM Facture
WHERE Year(Date_Facture)=2012;
UE215 / 535 26
Jointure simple
SELECT Table_1.champ2, Table_2.champ7
FROM Table_1, Table_2
WHERE Table_1.clé_primaire= Table_2.clé_étrangère;
• Exemple sur la base Ladoré :
Qui sont les clients facturés ?
SELECT Client.*, Facture.*
FROM Client, Facture
WHERE Client.Num_Client = Facture.Num_client;
UE215 / 535 27
Jointure et sous-requête
• Quelles sont les familles d’articles qui ne sont pas
achetées ?
• On liste les codes famille dans la table Article :
SELECT Distinct Code_Famille
FROM Article;
• On liste les codes famille achetés (dans la table
ligne de facture:
SELECT Distinct Code_Famille
FROM Article, [Ligne facture]
WHERE [Ligne facture].Num_article = Article.Num_article;
UE215 / 535 28
Jointure et sous-requête
• Quelles sont les familles d’articles qui ne sont
pas achetées ?
SELECT *
FROM Article
WHERE Code_Famille
NOT IN
(SELECT Distinct Code_Famille
FROM Article, [Ligne facture]
WHERE [Ligne facture].Num_article = Article.Num_article);
UE215 / 535 29
SQL
Complément sur les jointures
UE215 / 535 30
Les jointures
7 types de jointures, que l’on peut diviser en 4 groupes :
Jointure interne ou équivalente (INNER JOIN)
Jointures externes :
– Externe gauche (LEFT OUTER JOIN)
– Externe droite (RIGHT OUTER JOIN)
– Externe entière (FULL OUTER JOIN)
Jointures externes opposées ou de non-correspondance :
– Gauche opposée (LEFT OUTER JOIN WHERE [Link]é IS NULL)
– Droite opposée (RIGHT OUTER JOIN WHERE [Link]é IS NULL)
– Entière opposée (FULL OUTER JOIN WHERE [Link]é IS NULL OR [Link]é IS NULL)
Jointure sans relation ou produit cartésien
UE215 / 535 31
Les jointures - Interne
Interne (INNER JOIN)
Select < champs >
FROM Table A as A
INNER JOIN Table B as B
ON [Link]é = [Link]é;
Les enregistrements communs à
B A et B uniquement
UE215 / 535 32
Les jointures - Externe gauche
Externe gauche (LEFT OUTER JOIN)
Select < champs >
FROM Table A as A
LEFT JOIN Table B as B
ON [Link]é = [Link]é;
Tous les enregistrements de A
A B inclus ceux en commun avec B
UE215 / 535 33
Les jointures - Externe droite
Externe droite (RIGHT OUTER JOIN)
Select < champs >
FROM Table A as A
RIGHT JOIN Table B as B
ON [Link]é = [Link]é;
Tous les enregistrements de B
A B inclus ceux en commun avec A
UE215 / 535 34
Les jointures - Externe entière
Externe entière (FULL OUTER JOIN)
Select < champs >
FROM Table A as A
FULL OUTER JOIN Table B as B
ON [Link]é = [Link]é;
Tous les enregistrements
A B de A et de B
UE215 / 535 35
Les jointures - Gauche opposée
Gauche opposée (LEFT OUTER JOIN WHERE [Link]é IS NULL)
Select < champs >
FROM Table A as A
LEFT JOIN Table B as B
ON [Link]é = [Link]é
WHERE [Link]é IS NULL;
Uniquement les enregistrements
de A (qui ne sont pas communs
avec B)
UE215 / 535 36
Les jointures - Externe droite
Droite opposée (RIGHT OUTER JOIN WHERE [Link]é IS NULL)
Select < champs >
FROM Table A as A
RIGHT JOIN Table B as B
ON [Link]é = [Link]é
WHERE [Link]é IS NULL;
Uniquement les enregistrements
de B (qui ne sont pas communs
avec A)
UE215 / 535 37
Les jointures - Externe opposée
Entière opposée (FULL OUTER JOIN WHERE [Link]é IS NULL
OR [Link]é IS NULL)
Select < champs >
FROM Table A as A
FULL OUTER JOIN Table B as B
ON [Link]é = [Link]é
WHERE [Link]é IS NULL
OR [Link]é IS NULL;
Tous les enregistrements qui ne
sont pas en commun
UE215 / 535 38
Les jointures – Sans relation
Sans relation ou Produit cartésien
Select < champs >
FROM Table A, Table B;
Les enregistrements de A
multipliés par les
A B enregistrements de B
UE215 / 535 39
SQL
Manipuler les données
UE215 / 535 40
Copier d’occurrences
SELECT champs
INTO Nouvelle table
FROM Table source
• Attention : si une table porte déjà le nom de la nouvelle
table, elle sera supprimée
• Exemple sur la base Ladoré :
SELECT Num_article, Libelle, PV_TTC, Marque, Code_famille
INTO Article_Archive
FROM Article
WHERE Code_Famille = 'INC‘;
UE215 / 535 41
Copier d’occurrences
INSERT INTO Table destination
SELECT champs
FROM Table source
• Permet d’ajouter des données à une table existante
• Exemple sur la base Ladoré :
INSERT INTO Article_Archive
SELECT *
FROM Article
WHERE Code_Famille = 'OV‘;
UE215 / 535 42
Suppression d’occurrences
DELETE
FROM Table
WHERE condition
• Exemple sur la base Ladoré :
DELETE
FROM Article_archive
WHERE Code_Famille = 'OV‘;
UE215 / 535 43
Mise à jour d’occurrences
UPDATE Table
SET champ = nouvelle valeur
Éventuellement WHERE condition
• Exemple sur la base Ladoré :
UPDATE Article_archive
SET Code_Famille = ‘TEST‘;
UE215 / 535 44