SQL
R.KHCHERIF
1
SQL : Structured Query
Language
" SQL est un langage pour les BDR. Cr en 1970 par IBM.
" Principales caract ristiques de SQL :
Normalisation : SQL impl mente le mod le relationnel.
Standard : Du fait de cette normalisation, la plupart des diteurs
de SGBDR int grent SQL leurs produits (Oracle, Informix,
Sybase, Ingres, MS SQL Server, DB2, etc.). Ainsi, les donn es,
requ tes et applications sont assez facilement portables dune
base une autre.
Non proc dural : SQL est un langage de requ tes qui permet
lutilisateur de demander un r sultat sans se pr occuper des
moyens techniques pour trouver ce r sultat (assertionnel). Cest
loptimiseur du SGBD (composant du moteur) qui se charge de
cette t che.
Universel : SQL peut tre utilis tous les niveaux dans la
gestion dune BDR :
"
"
"
Langage de D finition de Donn es
LDD,
Langage de Manipulation de Donn es LMD,
LCD.
Langage de Contr le de Donn es
R.KHCHERIF
2
SQL : Structured Query
Language
" Langage de d finition de donn es LDD : permet la
description de la structure de la base de donn es
(tables, vues, attributs, index).
" Langage de manipulation de donn es LMD : permet
la manipulation des tables et des vues avec les quatre
INSERT, DELETE,
commandes
UPDATE.
SELECT,
:
" Langage de contr le de donn es LCD : comprend les
primitives de gestion des transactions : COMMIT,
ROLLback et des privil ges dacc s aux donn es :
GRANT et REVOKE.
R.KHCHERIF
3
Langage de d finition de donn es :
LDD
" La d finition de donn es dans SQL permet la
d finition des objets manipul s par le SGBD.
" Les objets : table, vue, index
" Les commandes du LDD sont :
CREATE : cr ation des objets.
ALTER : modification de la structure des objets.
DROP : suppression des objets.
R.KHCHERIF
4
Langage de d finition de donn es :
LDD
" Syntaxe de Cr ation des tables
Celle-ci consiste d finir son nom,
la
composent et leurs types. Elle se fait avec la commande :
CREATE TABLE
les colonnes qui
Syntaxe :
CREATE TABLE nom_table
(col1 type [(taille)] ,
col2 type
colonne],
coln type
colonne]
);
[(taille)] &)
2. PRIMARY KEY (colonne1 [, colonne2] &)
3. FOREIGN KEY (colonne1 [, colonne2] &)
REFERENCES nomTablePere (colonne1 [, colonne2]
&)
4. CHECK {condition}
" Les 4 derni res CI sont d finissables au niveau
colonne ou au niveau table.
R.KHCHERIF
19
Exemple
" Si on consid re le sch ma suivant :
MAGASIN(NumMag, Adresse, Surface)
PRODUIT(NumProd, DesProd, Couleur, Poids, Qte_Stk,
#CodMag)
" La commande pour la cr ation de la table Magasin tant :
Create Table Magasin
(NumMag number(6) primary Key,
Adresse varchar(30),
Surface number(7,3));
" La commande pour la cr ation de la table Produit peut tre
crite de deux fa ons:
R.KHCHERIF
20
Solution 1: cl trang re comme contrainte de table
CREATE TABLE Produit
(Numprod number(6) primary key,
Desprod varchar(15),
Couleur char,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
CodMag number(6),
Constraint FK_Produit Foreing Key (CodMag)
references
Magasin(NumMag));
R.KHCHERIF
21
Solution 2: cl trang re comme contrainte de
colonne
CREATE TABLE Produit
(Numprod number(6) primary key,
Desprod varchar(15),
Couleur char,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
CodMag number(6) references Magasin(NumMag));
R.KHCHERIF
22
Contrainte de valeur avec la clause check
" Permet de limiter les valeurs possibles pour
certaine
lors des
v rifiant
se
en
contr le
une
fera
colonne
une
condition. Le
insertions des donn es.
Constraint
(colonne condition)
nom_contrainte
CHECK
" La condition sur la colonne peut utiliser :
un op rateur de comparaison
la clause between val1 and val2
R.KHCHERIF
la clause in (liste de valeurs)
23
Publicité
Exemple
" On suppose que le poids dun produit doit tre positif.
La commande de cr ation de la table Produit devient :
CREATE TABLE Produit
(Numprod number(6) primary key,
Desprod varchar(15),
Couleur char,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
CodMag number(6) references Magasin(NumMag),
Constraint Ck1_Produit CHECK (Poids >=0));
R.KHCHERIF
24
Exemple
" On suppose que pour un produit, la quantit en stock doit tre
sup rieure ou gale la quantit seuil. La commande de
cr ation de la table Produit devient :
CREATE TABLE Produit
(Numprod number(6) primary key,
Desprod varchar(15),
Couleur char,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
CodMag number(6) references Magasin(NumMag),
Constraint Ck1_Produit CHECK (Poids >=0),
Constraint Ck2_Produit CHECK (Qte_stk >= Qte_seuil));
R.KHCHERIF
25
Exemple
" On suppose que la quantit dun produit doit tre comprise entre 0 et
1000. La commande de cr ation de la table Produit devient :
CREATE TABLE Produit
(Numprod number(6) primary key,
Desprod varchar(15),
Couleur char,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
CodMag number(6) references Magasin(NumMag),
Constraint Ck1_Produit CHECK (Poids >=0),
Constraint Ck2_Produit CHECK (Qte_stk >= Qte_seuil),
Constraint Ck3_Produit CHECK (Qte_stk between 0 and 1000));
R.KHCHERIF
26
Exemple
" On suppose que la couleur dun produit ne peut tre que N, G, ou B.
La commande de cr ation de la table Produit devient :
CREATE TABLE Produit
(Numprod number(6) primary key,
Desprod varchar(15),
Couleur char,
Poids number(8,3),
Qte_stk number(7,3),
Qte_seuil number(7,3),
Prix number(10,3),
CodMag number(6) references Magasin(NumMag),
Constraint Ck1_Produit CHECK (Poids >=0),
Constraint Ck2_Produit CHECK (Qte_stk >= Qte_seuil),
Constraint Ck3_Produit CHECK (Qte_stk between 0 and 1000),
Constraint Ck4_Produit CHECK (Couleur IN (N, G, B)));
R.KHCHERIF
27
Exercice
" Pilote (NPil, Nom, NbHvol, #Compa)
" Compagnie(NComp, NomComp, AdrComp, Ville )
" Cr er les deux tables avec les contraintes n cessaires.
" Rq : Les contraintes doivent tre cr e au niveau
table.
R.KHCHERIF
28
Correction
CREATE Table Compagnie
(NComp char(4),
NomComp varchar2(15),
AdrComp varchar2(20),
Ville varchar2(15),
Constraint pk_Compagnie PRIMARY KEY (NComp));
R.KHCHERIF
29
Correction
CREATE Table Pilote
(NPil CHAR(6),
Nom CHAR(15) NOT NULL,
NbHvol NUMBER(7,2),
Compa CHAR (4),
Constraint pk_Pilote PRIMARY KEY (NPil),
Constraint ck_NbHvol CHECK (NbHvol BETWEEN 0 And
2000),
Constraint un_nom UNIQUE (nom),
Constraint FK_Pil_Compa FOREIGN KEY (Compa)
REFERENCES Compagnie (NComp));
R.KHCHERIF
30
3- Modification de la structure
dune table
" Les trois possibilit s de modification de la structure de table sous ORACLE
permettent soit d'ajouter des colonnes, soit de modifier la structure d'une
colonne, soit de supprimer des colonnes existantes.
1 re forme : Ajout de nouvelles colonnes une table
" Syntaxe :
ALTER TABLE nom_table
ADD (col1 type [(taille)] ,
col2 type [(taille)] ,
. . .
coln type [(taille)] ) ;
" Exemple :
Supposons qu'on veut ajouter une colonne type_clt la table client :
ALTER TABLE CLIENT
ADD type_clt char(3) ;
R.KHCHERIF
31
2 me forme : Modification de la structure d'une
colonne existante
" Syntaxe :
ALTER TABLE nom_table
MODIFY (col1 type [(taille)] ,
col2 type [(taille)] ,
. . .
coln type [(taille)] ) ;
" Remarque :
Pour modifier le nom d'une colonne :
RENAME COLUMN nom_table.ancien_nom TO
nom_table.nouveau_nom ;
" Exemple :
Supposons qu'on veut changer le type_clt de char(3) en char(5) :
ALTER TABLE CLIENT
MODIFY type_clt char(5) default Monas;
R.KHCHERIF
32
3 me forme : Suppression de colonnes
existantes
"
Syntaxe :
ALTER TABLE nom_table
DROP ( col1 , col2 ,&, coln ) ;
" Exemple :
Supposons qu'on veut supprimer le champ ville de la table Magasin :
ALTER TABLE Magasin
DROP ville ;
R.KHCHERIF
33
4 me forme : Ajout d'une contrainte
" Syntaxe :
ALTER TABLE nom_table
ADD Constraint Def_de_contrainte ;
" Exemple :
Publicité
Ajouter la relation Magasin la contrainte suivante :
la surface doit tre comprise entre 10 et 100 m2
ALTER TABLE Magasin
ADD Constraint ck1_magasin check(surface between
10 and 100) ;
R.KHCHERIF
34
5 me forme : Suppression de contraintes
existantes
5.1 Suppression d'une contrainte cl primaire :
" On peut effacer une cl primaire. La commande est :
ALTER TABLE nom_table DROP PRIMARY KEY
;
" Remarque :
" L'option cascade est ajout e pour pouvoir supprimer une cl
primaire r f renc e.
" Exemple :
Supprimer la contrainte cl primaire de la table magasin
ALTER TABLE magasin DROP PRIMARY KEY CASCADE
;
R.KHCHERIF
35
5.2 Suppression d'une contrainte autre que la cl
primaire :
" On peut effacer une cl trang re. La commande est :
ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte ;
" O Le nom de la contrainte c'est celui de la contrainte supprimer .
" Exemple :
Supprimer la contrainte sp cifiant les couleurs possibles pour les produits
ALTER TABLE produit DROP CONSTRAINT Ck4_Produit ;
" Remarque :
" Pour retrouver les diff rentes contraintes avec leur propri t s,
on peut utiliser la commande suivante :
Select * from user_constraints
;
Il est remarquer que pour cette commande, le nom de la table
doit tre crit en majuscule.
R.KHCHERIF
36
Suppression de tables
" Syntaxe :
DROP TABLE nom_table ;
" Exemple :
" Supposons qu'on veut supprimer la table client_tunis :
DROP TABLE client_tunis ;
" Remarque :
les
toutes
Contrainte de suppression de table : permet de
supprimer
d'int grit
r f rentielles qui se refl tent aux cl s uniques ou
primaires de la table supprimer. La commande est :
DROP
CONSTRAINTS;
CASCADE
contraintes
nom_table
TABLE
R.KHCHERIF
37
Renommage et cr ation de
synonymes de tables
" Pour changer le nom d'une table existante la commande est :
" Syntaxe :
RENAME ancien_nom TO nouveau_nom ;
"
Il est galement possible de donner une m me table plusieurs
noms diff rents appel s synonymes.
" Syntaxe :
CREATE
SYNONYM
nom_synonyme
FOR
nom_table ;
" Pour supprimer un synonyme donn , on utilise la commande :
" Syntaxe :
DROP SYNONYM nom_synonym ;
" Remarque :
La suppression d'une table implique la suppression des
R.KHCHERIF
38
synonymes correspondants.
III. Le Langage de Manipulation
de donn es (LMD SQL)
III.1 Syntaxe g n rale dune requ te:
SELECT attribut(s) FROM table(s)
]
];
" Tous les tuples dune table
ex. SELECT * FROM Client;
" Tri du r sultat
ex. Par ordre alphab tique inverse de nom
SELECT * FROM Client ORDER BY Nom DESC;
R.KHCHERIF
39
BD exemple
" client ( numcli , nom, prenom, adresse,
codepost, ville, tel);
" produit ( numprod, designation, prixunit,
qtestock );
" commande ( numcom, numcli, idvendeur,
datec, quantite, numprod);
" vendeur ( idvendeur, nomvendeur, qualit ,
salaire, commission));
R.KHCHERIF
40
Interrogation des donn es
" Calculs ex. Calcul de prix TTC
SELECT PrixUni+PrixUni*0.206 prixttc FROM
Produit;
" Projection
ex. Noms et Pr noms des clients, uniquement
SELECT Nom, Prenom FROM Client;
" Restriction
ex. Clients qui habitent Tunis
SELECT * FROM Client WHERE Ville = Tunis;
R.KHCHERIF
41
Interrogation des donn es
" ex. Commandes en quantit au moins gale 3
SELECT * FROM Commande WHERE Quantite >= 3;
" ex. Produits dont le prix est compris entre 50 et 100 DT
SELECT * FROM Produit
WHERE PrixUni BETWEEN 50 AND 100;
" ex. Commandes en quantit ind termin e
SELECT * FROM Commande WHERE Quantite IS
NULL;
R.KHCHERIF
42
Interrogation des donn es
" ex. Clients habitant une ville dont le nom se
termine par Menzel
SELECT * FROM Client
WHERE Ville LIKE %Menzel;
" Menzel% commence par Menzel
" %Men% contient le mot Men
le joker _ peut tre remplac par nimporte quel
caract re
le joker % peut tre remplac par 0, 1, 2, & caract res
R.KHCHERIF
43
Interrogation des donn es
" ex. Pr noms des clients dont le nom est Mohamed,
Ahmed ou Mahmoud
SELECT Prenom FROM Client
WHERE Nom IN (Ahmed, Mahmoud, Mohamed);
" sp cifie une liste de constantes dont une doit galer le premier
terme pour que la condition soit vraie
" pour des NUMBER, CHAR, VARCHAR, DATE
" NB : Possibilit dutiliser la n gation pour tous ces
Publicité
pr dicats : NOT BETWEEN, NOT NULL, NOT LIKE, NOT
IN.
R.KHCHERIF
44
Fonctions dagr gat
" Elles op rent sur un ensemble de valeurs, fournissent une
valeur unique
AVG() : moyenne des valeurs
SUM() : somme des valeurs
MIN(), MAX() : valeur minimum, valeur maximum
COUNT() : nombre de valeurs
" ex. Moyenne des prix des produits
SELECT AVG(PrixUni) FROM Produit;
R.KHCHERIF
45
Op rateur DISTINCT
" ex. Nombre total de commandes
SELECT COUNT(*) FROM Commande;
SELECT COUNT(NumCli) FROM
Commande;
" ex. Nombre de clients ayant pass
commande
SELECT COUNT( DISTINCT NumCli)
FROM Commande;
R.KHCHERIF
46
Exemple
" Table COMMANDE (simplifi e)
Quantite
NumCli
1
5
2
Date
22/09/99
22/09/99
22/09/99
1
3
3
COUNT(NumCli) R sultat = 3
COUNT(DISTINCT NumCli) R sultat = 2
R.KHCHERIF
47
Jointure
" Consiste en un produit cart sien ou certaines lignes seulement sont
s lectionn es via la clause WHERE
ex. Liste des commandes avec le nom des clients
"
SELECT Nom, Date, Quantite FROM Client, Commande WHERE Client.NumCli =
Commande.NumCli;
ex. Idem avec le num ro de client en plus
"
SELECT C1.NumCli, Nom, Date, Quantite
FROM Client C1, Commande C2 WHERE C1.NumCli = C2.NumCli ORDER BY Nom;
SELECT C1.NumCli, Nom, Date, Quantite FROM Client C1, Commande C2 WHERE
C1.NumCli = C2.NumCli ORDER BY 2;
" NB : Utilisation dalias (C1 et C2) pour all ger l criture + tri par nom.
R.KHCHERIF
48
Jointure exprim e avec le pr dicat
IN
" ex. Nom des clients qui ont command le
23/09
SELECT Nom FROM Client WHERE NumCli
IN (SELECT NumCli FROM Commande
WHERE DateC = 23-09-2000 );
NB : Il est possible dimbriquer des requ tes.
R.KHCHERIF
49
Pr dicats EXISTS / NOT EXISTS
" ex. Clients qui ont pass au moins une commande
SELECT * FROM Client C1
WHERE EXISTS ( SELECT * FROM
Commande C2 WHERE C1.NumCli =
C2.NumCli );
R.KHCHERIF
50
Pr dicats ALL / ANY
" ex. Num ros des clients qui ont command au moins
un produit en quantit sup rieure chacune [ au
moins une] des quantit s command es par le client n
1.
SELECT DISTINCT NumCli FROM Commande
WHERE Quantite > ALL (
SELECT Quantite FROM Commande
WHERE NumCli = 1 );
R.KHCHERIF
51
Groupement
" permet de cr er des groupes de lignes pour
appliquer des fonctions dagr gat sur les groupes
il est possible de cr er des groupes sur plusieurs attributs
" ex. Quantit totale command e par chaque client
SELECT NumCli, SUM(Quantite) FROM Commande GROUP
BY NumCli;
" ex. Nombre de produits diff rents command s...
SELECT NumCli, COUNT(DISTINCT NumProd)
FROM Commande GROUP BY NumCli;
R.KHCHERIF
52
Groupement
" ex. Quantit moyenne command e pour les produits
faisant lobjet de plus de 3 commandes
SELECT NumProd, AVG(Quantite)
FROM Commande GROUP BY NumProd HAVING
COUNT(*)>3;
Attention : La clause HAVING ne sutilise quavec
GROUP BY.
R.KHCHERIF
53
Op rations ensemblistes
"
INTERSECT, MINUS, UNION
les deux tableaux op randes doivent avoir une description
identique:
" nombre de colonnes
" domaines des valeurs de colonnes
structure g n rale
" co-requ te OPERATEUR co-requ te
" ou chaque co-requ te est une instruction SELECT
ex. Num ro des produits qui soit ont un prix inf rieur 100 DT, soit
ont t command s par le client n 2
SELECT NumProd FROM Produit WHERE PrixUni<100 UNION
SELECT NumProd FROM Commande WHERE NumCLi=2;
R.KHCHERIF
54
Les sous-requ tes
" m canisme tr s puissant mais dun emploi
subtil !
" = une requ te SELECT ins r e dans la
clause WHERE dune autre requ te
SELECT
" bien sur, une sous-requ te peut contenir
une sous-sous-requ te !
R.KHCHERIF
55
Les sous-requ tes
" peuvent retourner
une seule valeur
une liste de valeurs
" une sous-requ te constitue le second
membre dune comparaison dans la
clause WHERE
R.KHCHERIF
56
Les sous-requ tes retournant
une valeur
SELECT last_name, salary FROM s_emp
WHERE salary >= (SELECT AVG(salary) FROM
Publicité
s_emp);
LAST_NAME SALARY
------------------------- ------------------
2500
Velasquez
Ngao
Nagayama 1400
Quick-To-See 1450
...
R.KHCHERIF
1450
sous-requ te
57
Les sous-requ tes retournant
une liste de valeurs
SELECT last_name, salary FROM s_emp
WHERE salary >= ALL (SELECT salary FROM
s_emp WHERE dept_id = 10) ;
LAST_NAME SALARY
------------------------- -------------------
Velasquez
Ngao
Quick-To-See 1450
1550
Ropeburn
1490
Giljum
2500
1450
R.KHCHERIF
58
Contraintes des sous-requ tes
" clause ORDER BY y est interdite
" chaque sous-requ te doit tre entour e de
parenth ses
" clause SELECT dune sous-requ te ne peut
contenir quun seul attribut
" les attributs d finis dans la requ te principale
peuvent tre utilises dans la sous-requ te
" les attributs d finis dans la sous-requ te ne
peuvent pas tre utilises dans la requ te
principale
R.KHCHERIF
59
Op rateurs de comparaison et les
sous-requ tes
" dans le cas de sous-requ tes retournant une
seule valeur, les op rateurs classiques (<, >, &)
peuvent tre appliques
" dans le cas de sous-requ tes retournant une
liste de valeurs, il faut utiliser les quantificateurs
ANY: lexpression est vraie si une des valeurs de la
sous-requ te v rifie la comparaison
ALL: lexpression est vraie si toutes les valeurs de la
sous-requ te v rifient la comparaison
" Note: IN quivaut = ANY
R.KHCHERIF
60
Les sous-requ tes multiples
" la clause WHERE dune requ te principale
peut contenir plusieurs sous-requ tes
reli es par les connecteurs AND et OR
R.KHCHERIF
61
Mise jour des donn es
" Ajout dun tuple
INSERT INTO nom_table VALUES (val_att1, val_att2,
&);
ex. INSERT INTO Produit VALUES (400, Nouveau
produit, 78.90);
" Mise jour dun attribut
UPDATE nom_table SET attribut=valeur
;
ex. UPDATE Client SET Nom=Dudule WHERE
NumCli = 3;
R.KHCHERIF
62
Mise jour des donn es
" Suppression de tuples
DELETE FROM nom_table ;
ex. DELETE FROM Produit;
ex. DELETE FROM Client WHERE Ville = Tunis;
R.KHCHERIF
63
Les vues
" Vue : table virtuelle calcul e partir dautres
tables gr ce une requ te
" D finition dune vue
CREATE VIEW nom_vue AS requ te;
ex. CREATE VIEW Noms AS SELECT Nom,
Prenom FROM Client;
R.KHCHERIF
64
Les vues
" Int r t des vues
Simplification de lacc s aux donn es en masquant les
op rations de jointure
ex. CREATE VIEW Prod_com AS SELECT
P.NumProd, D si, PrixUni, Date, Quantite FROM
Produit P, Commande C WHERE
P.NumProd=C.NumProd;
SELECT NumProd, D si FROM Prod_com
WHERE Quantite>10;
R.KHCHERIF
65
Les vues
" Int r t des vues
Sauvegarde indirecte de requ tes complexes
Pr sentation de m mes donn es sous diff rentes formes
adapt es aux diff rents usagers particuliers
Support de lind pendance logique
ex. Si la table Produit est remani e, la vue
Prod_com doit tre refaite, mais les requ tes qui
utilisent cette vue nont pas tre remani es.
R.KHCHERIF
66
Les vues
" Int r t des vues
Renforcement de la s curit des donn es par
masquage des lignes et des colonnes sensibles aux
usagers non habilit s
" Probl mes de mise jour, restrictions
La mise jour de donn es via une vue pose des
probl mes et la plupart des syst mes impose
dimportantes restrictions.
R.KHCHERIF
67
Les vues
" Probl mes de mise jour, restrictions
Le mot cl DISTINCT doit tre absent.
La clause FROM doit faire r f rence une seule
table.
La clause SELECT doit faire r f rence
directement aux attributs de la table concern e (pas
dattribut d riv ).
Les clauses GROUP BY et HAVING sont
interdites.
R.KHCHERIF
68
" Spectacle(Spectacle_ID, Titre, DateD b, Dur e, #Salle_ID,
Chanteur)
" Concert (Concert_ID, DateC, HeureC, #Spectacle_ID)
" Salle (Salle_ID, Nom, Adresse, Capacit )
" Billet (Billet_ID, #Concert_ID, Num_Place, Cat gorie, Prix)
" Vente (Vente_ID, Date_Vente, #Billet_ID, MoyenPaiement)
" Les m mes questions r solues avec lalg bre relationnel.
R.KHCHERIF
69