S Q L

Base de Données
M Cieloski
SQL
STRUCTURED QUERY LANGAGE
Définition :
Langage de définition et de manipulation de données.
1er mouture SQL
SQL 1 en 1986
puis
SQL 1 en 1989
puis
SQL 2 en 1992
norme ISO
Une version SQL 3 orientée objet est à l’étude aux USA.
Il s’agit en fait de 3 langages :
-
LDD Langage de Description des Données
LMD Langage de Manipulation des données
LCD Langage de contrôle des données
LE LMD

L’obtention des données se fait dans un ordre unique : SELECT
Select <liste colonne>
From <liste table>
* pour toutes les colonnes
Attention le Select seul n’efface pas les doublons
Exemple : les exercices suivant découle du poly. ALGEBRE RELATIONNEL (produits,
fournisseurs, commandes)
-
lister la table des fournisseurs.
Select *
From fournisseurs ;
-
lister les n° de facture et les fournisseurs.
Select fno,nom
From fournisseurs ;
Pour éviter les doublons on peut utiliser DISTINCT après le SELECT.

-
Instruction de restriction : WHERE
lister les données des produits dont le poids est supérieur à 15 Kg
Select *
From produits
Where poids>15 ;
1
Base de Données
-
M Cieloski
lister les données des produits dont le poids est > 15 Kg et < à 40 Kg.
Select *
From produits
Where poids > 15 and poids < 40;
 Instruction BETWEEN, IN, LIKE
_ remplace n’importe quel caractère
% remplace n’importe quel séquence
Select *
From produits
Where poids IN (15 ,40) ;
Select *
From produits
Where poids NOT BETWEEN 15 and 40;
Select *
From fournisseurs
Where ville=”LYON”;
Select *
From fournisseurs
Where nom NOT LIKE ‘D%’ ;
Le test d’une valeur manquante est également possible, représenté par NULL.
-
Ou
lister les fournisseurs dont l’adresse n’est pas renseignée.
Select *
From fournisseurs
Where adresse IS NULL ; (pas d’adresse)
Where adresse IS NOT NULL ; (il existe une adresse)
RAPPEL SUR LES TABLES DE VERITE
ET
V
F
Null
V
V
F
Null
F
F
F
F
Null
Null
F
Null
OU
V
F
Null
V
V
V
V
F
V
F
Null
Null
V
Null
Null
V
F
Null
NON
F
V
Null
2
Base de Données
M Cieloski
TRI EST PRESENTATION
Le langage SQL permet de trier les tuples obtenus grace à une clause ODER BY;
ASC et DESC permettent de préciser si le tri est croissant ou décroissant, par défaut
il est croissant.
-
afficher les produits rouge ou vert, trier sur le nom croissant et la couleur
décroissante.
Select *
From produits
Where couleur IN (‘rouge’,’vert’)
Order by design ASC, couleur DESC;
-
lister les produits cartésien de la tables produits et fournisseurs
Select *
From fournisseurs, produits ;
-
lister les noms des fournisseurs avec les n° de produits commandés ainsi que
la quantité commandés.
Select nom, pno, qute
From fournisseurs, commandes
Where fournisseur.fno=commandes.fno ;
Autre façon via les synonymes de tables :
Select nom, pno, qute
From fournisseur F, commande C
Where F.fno=C.fno ;
-
lister les couples de réferences fournisseurs situés dans la même ville.
Select F1.fno, F2.fno
From fournisseur.F1, fournisseur.F2,
Where F1.fno<F2.fno
And F1.ville=F2.ville;
LA MANIPULATION DE DONNEES
Lister le prix des produits avec une TVA à 5.5%
Select Distinct design, prix*1.055 AS prix_ttc
From produits ;
3
Base de Données
M Cieloski
Exemple, illustrer le volume financier commandé par des commandes de
moins de 10 articles.
Select Distinct cno, prix*qute AS volume
From produits p, commande c
Where c.pno=p.pno
and qute<10;
D’autres commande:
YEAR (Date)
LOWER (Chaine)
MONTH (Date)
UPPER (Chaine)
DAY (Date)
LENGTH (Chaine)
SUBSTRING (Chaine, position départ, longueur)
LES FONCTIONS STATISTIQUES
AVG
COUNT
MAX
MIN
SUM
moyenne
compter le nombre d’éléments
donne le MAX du nombre d’éléments
donne le MIN du nombre d’éléments
fait la somme d’une liste d’éléments
Select COUNT (fno)
From fournisseurs;
-
calculer la somme des quantités commandées.
Select SUM (qute)
From commandes;
-
donner le nombre de livraison du produit 102.
Select COUNT (*)
From commandes
Where pno=102;
-
compter les noms des fournisseurs différents.
Select COUNT (Distinct nom)
From fournisseurs;
-
donner la quantité maximum commandée
select MAX (qute)
from commandes ;
4
Base de Données
M Cieloski
LES REGROUPEMENTS
Il est souvent intéressant de regrouper des données pour y faire des opérations.
GROUP BY (nom d’une colonne)
HAVING qualificatif
Ex : compter les commandes par fournisseur.
Select fno, COUNT (*) nb
From commandes
Group by fno;
(ou as nb)
HAVING permet d’exprimer une restriction sur les résultats obtenus avec une
fonction de calcul.
Pour éviter toute confusion les tests sur les colonnes simples : Where ; les tests
sur les colonnes de regroupements Having.
Ex : lister uniquement les n° de fournisseurs qui ont plus de trois commandes
d’au moins dix articles, liste triée sur le nom du fournisseur.
Select F.fno, nom
From commande C, fournisseur F,
Where qute>10
and C.fno=F.fno
Group by F.fno
Having Count (*) > 3
Order by nom;
Ex : donner le n° de fournisseur qui fournit plus d’un produit.
Select fno
From fournisseur
Group by fno
Having Count (fno) > 1;
Ex : On ne s’intéresse qu’aux commandes dont le n° et > à 1000 et pour celles ci,
on souhaite lister les noms des fournisseurs avec les quantités maximum
commandées dont la moyenne des quantités commandées et > à 7 articles.
Select nom, MAX (qute)
From commande C, fournisseur F,
Where C.fno=F.fno
and C.cno > 1000
Group by nom
Having AVG (qute) > 7;
5
Base de Données
M Cieloski
LES SOUS-REQUETES
Il est possible d’effectuer des sous-requêtes afin d’obtenir des requêtes complexes
afin d’effectuer des opérations qui dépendent d’autres requêtes.
Select _ _ _
From _ _ _
Where fno=10 ;
Select _ _ _
From _ _ _
Where fno = (Select _ _ _
From _ _ _)
1 seule valeur
Select _ _ _
From _ _ _
Where fno IN (Select _ _ _
From _ _ _ )
Plusieurs données
Select _ _ _
From _ _ _
Where fno > ALL (Select _ _ _
From _ _ _ )
Supérieur à toute les valeurs renvoyées par le sous select.
Select _ _ _
From _ _ _
Where fno > ANY (Select _ _ _
From _ _ _ )
Au moins une valeur renvoyée par le sous select.
Select _ _ _
From _ _ _
Where EXISTS (Select _ _ _
From _ _ _ )
Existe si le sous select renvoie quelque chose.
6
Base de Données
-
lister les fournisseurs qui habitent dans la même ville que le fournisseur 10
Select nom
From fournisseurs
Where ville=(Select ville
From fournisseurs
Where fno=10) ;
-
M Cieloski
 Select nom
From fournisseurs
Where ville=’LILLE’ ;
lister les fournisseurs dont au moins 1 des produits est fournis par un
fournisseur de produits rouges.
Select fno
From commande
Where pno IN (Select Distinct pno
From commandes
Where fno IN (Select Distinct fno
From commandes
Where pno IN (Select pno
From produits
Where couleur=’rouge’)));
-
lister les noms des fournisseurs livrant tout les produits.
Select nom
From fournisseurs F
Where NOT EXISTS (Select *
From produits P
Where NOT EXISTS (Select *
From commandes C
Where C.pno=P.pno
And F.fno=P.fno));
-
lister les références des fournisseurs livrant au moins 1 produit en quantité
supérieur à chacun des produits livrés par le fournisseur 19.
Select fno
From commandes
Where qute> ALL (Select qute
From commandes
Where fno=19);
Ou
Select fno
From commandes C1
Where Not Exists (Select *
From commandes C2
Where C2.fno=19
And C1.qute <= C2.qute);
7
Base de Données
M Cieloski
LES OPERATIONS ENSEMBLISTES
UNION
INTERSECT
EXCEPT
fournit la réunion des tuples de deux Select
fournit l’intersection des tuples de deux Select
fournit les tuples du 1er Select qui ne sont pas dans le second
Condition : elles ne sont valables qu’a condition que les colonnes de chacun des
Selects aient exactement la même description (la même longueur)
L’opération UNION supprime par défaut les doublons, si l’on veut
obtenir les doublons, il faut utiliser UNION ALL .
LES OPERATIONS DE LA MISE A JOUR
Insérer des données
INSERT
permet d’ajouter des lignes dans une table si l’ordre INSERT contient
une clause VALUES alors 1 seule ligne est insérée dans la table, par
contre si l’ordre INSERT contient une clause Select alors plusieurs
lignes peuvent être simultanément insérées dans la table.
Ex : Ajouter un nouveau produit
INSERT INTO produits (pno, design, prix, poids, couleur)
VALUES (8, ‘ecrou’, 10, 0.5, ‘vert’) ;
Ajouter dans la table petites_cmd les n° et quantité des produits commandés en
moins de 5 articles. (on supposera que la table petites_cmd existe)
INSERT petites_cmd (pno, qute)
Select pno, qute
From commandes
Where qute < 5 ;
Remarque :
si une valeur est insérer dans toutes les colonnes de la table,
l’énumération des colonnes est facultative.
INSERT INTO produits
VALUES (8, ‘ecrou’, 10, 0.5, ‘vert’) ;
Modification des données
UPDATE
permet de modifier des lignes dans une tables, l’expression contenant
les modifications à effectuer peut être une constante, une expression
arithmétique ou le résultat d’un Select imbriqué.
8
Base de Données
-
M Cieloski
Augmenter le prix de tout les produits de 5% s’ils sont de couleur rouge.
UPDATE produits
SET prix = prix * 1.05
Where couleur = ‘rouge’ ;
-
Effectuer une promotion de 10% sur tout les produits de prix maximum.
UPDATE produits
SET prix = prix * 0.90
Where prix = (Select MAX (prix)
From produits) :
Suppression
DELETE
-
permet de supprimer des lignes dans 1 tableau suivant une
qualification.
Supprimer tout les produits dont le prix est > à 1000F.
DELETE from produits
Where prix > 1000 ;
9