SQL avanc

Les sous-requtes
Les fonctions de groupes
Gestion des transactions
Les vues
Indexation et Cluster
www.cours-ofppt.com
www.cours-ofppt.com

ISI 204 2
Les sous-requtes
SQL permet d'utiliser des requtes pour
laborer des conditions plus complexes et
"dynamiques", c'est dire qu'un critre de
recherche employ dans la clause WHERE
soit lui mme le rsultat d'un SELECT
Type de sous requte
Sous-requtes simples
Sous-requtes synchronises
www.cours-ofppt.com ISI 204 3
Sous-requtes simples (1)
Dans ce cas la sous-requte est d'abord value,
puis le rsultat est utilis pour excuter la
requte principale.
Exemple
slectionner la liste des employs ayant mme poste que
JONES
select ENAME from EMP
where JOB = ( select JOB from EMP
where ENAME = 'JONES' );
Remarque
les sous-requtes sont plus lisibles que des jointures
www.cours-ofppt.com ISI 204 4
Sous-requtes simples (2)
Exercice
Les personnes ayant un salaire infrieur JONES
et qui ne travaillent pas dans le mme
dpartement que ALLEN

Slectionner les employs travaillant CHICAGO
et ayant le mme job que ALLEN
Thomas Devogele ISI 204 5
Sous-requtes synchronises
Dans ce cas la sous-requte est value pour
chaque n-uplet de la requte principale
Exemple
slectionner des employs ne travaillant pas dans le mme
dpartement que leur manager.
SQL> select ENAME from EMP e
where DEPTNO != ( select DEPTNO from EMP
where e.MGR = EMPNO);
Remarque
le synonyme emp de la requte principale est utilis dans la
sous-requte.
Thomas Devogele ISI 204 6
Les oprateurs associs aux
sous-requtes (1)
L'oprateur EXISTS permet de construire un
prdicat vrai si la sous-requte qui suit ramne au
moins une ligne.
Syntaxe
select ' from nom-table where exists ( select --- )
Exercice
Liste des employs travaillant dans un dpartement qui
contient au moins un ANALYSTE ?
Thomas Devogele ISI 204 7
Les oprateurs associs aux
sous-requtes (2)
Les oprateurs ensemblistes
Dans les exemples prcdent, le SELECT de la
sous-requte ramenait un seul n-uplet, car droite du signe
"=" se trouvait une seule valeur. Cependant une
sous-requte peut ramener plusieurs n-uplets (une liste de
valeur).
Les oprateurs permettant de comparer une valeur un
ensemble de valeurs sont les oprateurs obtenus en
ajoutant ANY et ALL la suite d'un oprateur de
comparaison
ANY : la comparaison sera vraie si elle est vraie pour au
moins un lment de l'ensemble.
ALL : la comparaison sera vraie si elle est vraie pour tous les
lments de l'ensemble.
Thomas Devogele ISI 204 8
Les oprateurs associs aux
sous-requtes (3)
Exercice
slectionner les employs gagnant plus que tous les
employs du dpartement 30

Remarque
L'oprateur IN est quivalent l'oprateur = ANY
L'oprateur NOT IN est quivalent l'oprateur != ALL
Exercice
Employs du dpartement "RESEARCH" embauchs le
mme jour que quelqu'un du dpartement "SALES":
Thomas Devogele ISI 204 9
Les oprateurs associs aux
sous-requtes (4)
Les sous-requtes ramenant plusieurs
colonnes
II est possible de comparer le rsultat d'un
SELECT ramenant plusieurs colonnes une liste
de colonnes.
La liste de colonnes figurera entre parenthse
gauche de l'oprateur de comparaison.
Syntaxe
select col1, ... from nom table
where (col2,col3) = (select col2, col3 )
Exercice
slectionner la liste des employs ayant mme job et
mme salaire que FORD
Thomas Devogele ISI 204 10
Les fonctions de groupe (1)
II est possible d'utiliser certaines fonctions en
demandant de grouper les rsultats selon une ou
plusieurs colonnes.
Les fonctions de groupe sont les suivantes
avg (col)
moyenne des valeurs (les valeurs NULL sont ignores)
count (col)
nombre de n-uplet satisfaisant la condition WHERE. Les
valeurs NULL sont ignores.
max (col)
valeur maximale des valeurs de la colonne
min (col)
valeur minimale des valeurs de la colonne
sum (col)
Somme des valeurs de la colonne
variance (col)
variance des valeurs de la colonne
Thomas Devogele ISI 204 11
Les fonctions de groupe (2)
En appelant une fonction de groupe dans la liste rsultat
d'un SELECT, on obtient un rsultat comportant une seule
ligne et prenant en compte l'ensemble des lignes
satisfaisant la condition WHERE.
Exemple
Total des salaires du dpartement 10.
select SUM(SAL) from EMP
where DEPTNO = 10;
Remarque
il est impossible de demander la fois une colonne et une fonction
de groupe.
Exercice
Nom, job, et salaire de l'employ ayant le salaire le plus lev.
Liste des employs ayant le salaire le plus lev dans chaque
dpartement.
Thomas Devogele ISI 204 12
Calcul sur plusieurs groupes (1)
II est possible de subdiviser la table en
groupes,
chaque groupe tant l'ensemble des
lignes ayant une valeur commune.
Syntaxe
group by expr [,expr2 ...]
exemple
donner la somme des salaires pour chaque
dpartement
Select SUM(SAL) from EMP
group by DEPTNO;

Thomas Devogele ISI 204 13
Calcul sur plusieurs groupes (2)
Exercice
slectionner par job, le nombre d'employs, la somme et la
moyenne des salaires.
Donner le salaire moyen par dpartement et par job.
Liste des employs ayant le salaire le plus lev dans
chaque dpartement.
Attention
dans la liste des colonnes rsultat d'un SELECT de groupe
ne peuvent figurer que des caractristiques de groupe,
c'est dire :
soit des fonctions de groupe
soit des expressions figurant dans le GROUP BY
Thomas Devogele ISI 204 14
La clause HAVING
La clause HAVING permet, de faon analogue la
clause WHERE , de poser des conditions sur le
rsultat du regroupement
select col1 [col2 ...]
from table [, table2j
[where prdicat]
[group by expression [, expression]
[having prdicat] ]
Le prdicat figurant dans la clause HAVING ne
peut porter que sur des caractristiques des
fonctions de groupe figurant dans la clause
GROUP BY.
Thomas Devogele ISI 204 15
La clause HAVING (2)
Exercice
slectionner les jobs dont la moyenne des salaires est
suprieure 1500.
Remarque :
quand les clauses WHERE et GROUP BY sont prsentes, la
clause WHERE sera d'abord applique pour slectionner les
lignes, puis les groupes seront constitus partir des n-
uplets slectionns, et les fonctions de groupes seront
values
Exercices difficiles
nombre de CLERK des dpartements ayant au moins deux
employs CLERK
job ayant le salaire moyen le plus bas

Thomas Devogele ISI 204 16
Gestion des transactions (1)
transaction
traduit l'atomicit d'une squence d'oprations sur la base,
elle fait voluer la base de donnes d'un tat cohrent un
autre tat cohrent.
Ce qui signifie que si une transaction ne va pas son terme
pour une raison ou pour une autre (cas de crash du systme
ou du disque), la base est restaure dans l'tat o elle se
trouvait avant que la transaction ne dmarre.
Une transaction qui s'excute bien
est valide ;
une transaction qui ne se termine pas
est avorte.
Thomas Devogele ISI 204 17
Gestion des transactions (2)
valider la transaction en cours par la
commande COMMIT.
Les modifications deviennent dfinitives et visibles
tous les utilisateurs.
annuler la transaction en cours par la
commande ROLLBACK.
Toutes les modifications depuis le dbut de la
transaction sont alors annules.

Thomas Devogele ISI 204 18
Les vues
Definitions
Une vue est une table virtuelle
elle n'existe pas dans la base
elle est construite partir du rsultat d'un SELECT.
La vue sera vu par l'utilisateur comme une table relle.
Les vues permettent
des accs simplifis aux donnes
l'indpendance des donnes
la confidentialit des donnes : restreint les droits d'accs
certaines colonnes ou certains n-uplets.
Thomas Devogele ISI 204 19
Les vues (2)
cration d'une vue - CREATE VIEW
La commande CREATE VIEW permet de crer
une vue en spcifiant le SELECT constituant la
dfinition de la vue
Syntaxe
create view nom [(col1, ...) ] ,.as.
select col1, col2, ...
from tab
where prdicat
[with check option]
La spcification des noms de colonnes de la vue
est facultative.
Par dfaut, les colonnes de la vue ont pour nom
les noms des colonnes rsultat de SELECT.
www.cours-ofppt.com ISI 204 20
Les vues (3)
Exercice
crer une vue EMP10 sous-ensemble de la table EMP
comportant le numro, le nom et le job des employs du
dpartement 10.
L'option CHECK OPTION
permet de vrifier que la mise jour ou l'insertion faite
travers la vue ne produisent que des lignes qui feront partie
de la slection de la vue.
Par exemple, si la vue EMP10 a t cre avec "check
option", on ne pourra travers cette vue ni modifier, ni
insrer des employs ne faisant pas partie du dpartement
10.

Thomas Devogele ISI 204 21
Les vues (4)
Cration de vues partir de plusieurs tables.
Exemple
crer une vue comportant le nom des employs, le nom du
service et le lieu de travail.
create view EMPLOYES2
as select ENAME, DNAME, LOC
from EMP emp, DEPT dept
where emp. DEPTNO = dept.DEPTNO
Requtes et vues
Pour rcuprer les donnes de vues, on procdera comme
si l'on tait en face d'une table classique.
select * from EMPLOYES2
En ralit, cette table est virtuelle et est reconstruite
chaque appel de la vue EMPLOYES par excution du
SELECT constituant la dfinition de la vue.
Thomas Devogele ISI 204 22
Les vues (5)
Suppression d'une vue -
Une vue peut tre dtruite par la commande
drop view nom vue
Renommer une vue
Une vue peut tre renomme par la commande
rename ancien-nom-vue to nouveau-nom-vue
Remarques :
la prochaine version de MySQL grera les vues

Thomas Devogele ISI 204 23
Indexation
Lors d'une slection
le systme balaye toute la
table pour trouver les n-
uplets rpondant aux
prdicats
temps de rponse
prohibitifs pour des tables
dpassant quelques
centaines de lignes.
La cration d'un index
permettra au systme de
reprer de faon plus
rapide des lignes
spcifiques des tables et
donc d'amliorer
d'interrogations.
Exemple d'index
Thomas Devogele ISI 204 24
Indexation (2)
cration d'un index
Syntaxe
create index nom_index on nom table (col1, col2, ...)
Un index peut tre cr sur une table contenant dj des n-uplets.
L'index sera mis jour automatiquement lors de la modification de
la table.
Remarques
Les requtes SOL sont transparentes au fait qu'il existe un index
ou non. C'est l'optimisateur du SGBD qui optimise
II est possible de crer un index en prcisant que les valeurs de la
colonne indexe devront tre uniques (unicit de cl). Ainsi la
cration de l'index suivant empchera l'insertion dans la table EMP
d'un numro d'employ existant
create unique index EMPNO on EMP (EMPNO)
suppression d'un index
Syntaxe
drop index nom_index

Thomas Devogele ISI 204 25
Cluster
Dfinition
Le cluster est une organisation des tables qui consiste
rassembler physiquement (dans un mme bloc disque) les
lignes d'une ou plusieurs tables ayant une valeur commune
dans un champs donn. Le clustering permet d'acclrer les
jointures.
Pour notre exemple
on peut "rapprocher" les informations de DEPT et de EMP
en utilisant les colonnes communes DEPTNO.
cration d'un cluster
Syntaxe
create cluster nom_cluster (cle1 types1, ....)
II faut prciser le nom du CLUSTER, la colonne utiliser et
le type des lments de cette colonne.
www.cours-ofppt.com ISI 204 26
Cluster (2)
Activation
A sa cration, le cluster est vide. II ne contiendra des donnes que
lorsqu'on y transportera une table par la commande ALTER
CLUSTER
Syntaxe
alter cluster nom_cluster
add table nom_table
where nom_cluster.cle = nom_table.col
o l'on indique la colonne de la table identifier la cl du cluster.
Les donnes de la table sont alors dplaces.
Suppression d'une table d'un cluster
Syntaxe
alter cluster nom_cluster drop table nom_table
Suppression d'un cluster
Syntaxe
drop cluster nom cluster

Thomas Devogele ISI 204 27
Cluster (3)
Exemple
cration
create cluster DEPT-EMP (NUM number);
on associe les deux tables EMP et DEPT au
cluster
alter cluster DEPT.EMP
add table DEPT
where DEPT.DEPTNO = DEPT-EMP.NUM;
alter cluster DEPT-EMP
add table EMP
where EMP.DEPTNO = DEPT-EMP.NUM;
Thomas Devogele ISI 204 28
Contrle des accs la base : LCD (1)
Partage de droits
Le crateur d'une table en est le propritaire et il est seul
pouvoir ajouter, modifier ou supprimer des donnes ou
modifier la description des tables.
Le propritaire de la table peut nanmoins accorder ou
retirer les privilges d'autre utilisateurs
Exemples
Autoriser un utilisateur ajouter des n-uplets
Autoriser un utilisateur consulter une vue
Autoriser un utilisateur modifier la dfinition de la table

Thomas Devogele ISI 204 29
Contrle des accs la base : LCD (2)
l'ordre GRANT
L'ordre GRANT permet au propritaire d'une table de donner
d'autres utilisateurs des droits d'accs cette table ou vue.
Syntaxe
grant droit on table to nom_utilisateur [whith grant option]
grant droit on vue to nom_utilisateur [whith grant option]
l'ordre REVOKE
Un utilisateur ayant accord un droit peut le reprendre
l'aide de l'ordre REVOKE
Syntaxe
revoke droit on table from nom_utilisateur
revoke droit on vue from nom_utilisateur
Thomas Devogele ISI 204 30
Contrle des accs la base : LCD (3)
Les privilges qui peuvent tre accords sont les suivants
~ select : droit de lecture
~ insert : droit d'insertion de n-uplets
~ update(col,...) : droit de modification de lignes
~ delete : droit de suppression de lignes
~ alter : droit de modification de la dfinition de la table
~ index : droit de cration d'index
~ cluster : droit de mise en cluster
~ all : tous les droits ci-dessus
Remarques
Les trois premiers privilges s'appliquent aux tables et aux vues.
Les droits peuvent tre accords tous les utilisateurs en utilisant
le mot rserv PUBLIC la place du nom de l'utilisateur.