Présentation 7

Les bases de données
Séance 7
Les fonctions avancées :
Opérateurs ensemblistes,
Sous-requêtes et transactions
1
Bases de données VII
Les opérateurs ensemblistes
ORACLE
2
Base de données VII
Ce qui est abordé
 Les opérations ensemblistes
 Les sous requêtes
 Dépendantes
 Indépendantes
 Les transactions et accès concurrents
 Les transactions
 Les verrous
3
Base de données VII
Les opérateurs ensemblistes
 Les opérateurs ensemblistes jouent avec les
résultats de requête comme s’ils étaient
des ensembles
 UNION : réunion des résultats
 MINUS : différence des résultats
 INTERSECT : intersection des résultats
 Syntaxe générale :
SELECT …
ORACLE
<opérateur>
SELECT …
4
Base de données VII
Les opérateurs ensemblistes : conditions
 Même nombre de colonnes après
chaque SELECT.
 Colonnes de chaque table de même
type.
 Un DISTINCT est exécuté de manière
implicite
 ORDER BY : numéro des colonnes
5
Base de données VII
Les opérateurs ensemblistes : exemples
R
R
UNION
S
Col1
A
D
G
Col1
A
D
G
A1
Col2
B
E
H
Col2
B
E
H
B1
Col3
C
F
S
I
Col3
C
F
R
MINUS
I
D1 S
C1
A1
D
C2
B1
E
c3
D1
F
Col1 Col2 col3
A
B
C
G
H
I
6
Bases de données VII
Les sous-requêtes
ORACLE
MySQL
V > 5.0
7
Base de données VII
Les sous-requêtes et requêtes imbriquées
 Elles permettent d’effectuer une requête
sur la base du résultat d’une autre
requête.
 Mysql ne les implémente pas jusqu’à
MySQL 5.0
 On peut toujours s’en passer, mais elles
fournissent la façon la plus puissante
d’extraire des données complexes
8
Base de données VII
Les sous-requêtes et requêtes imbriquées
 Doivent toujours être placées après la
clause WHERE ou la clause HAVING en
lieu et place d’une constante
 Il peut y avoir plusieurs requêtes
imbriquées
 La sous-requête peut renvoyer une
valeur ou liste valeur
 Elle peut être dépendante ou
indépendante
9
Base de données VII
Les sous-requêtes qui renvoient une valeur
 Sélection classique par comparaison avec
une constante
WHERE poste = 'Manager'
 Est équivalent à :
WHERE poste = (SELECT poste from ...)
 Accepte tous les opérateurs tels que <,
>, <=, >=, <>
10
Base de données VII
Les sous-requêtes qui renvoient une valeur
SELECT
nom
FROM
employe
ORACLE
WHERE
poste = (SELECT poste
FROM employe
WHERE nom = 'Martin') ;
Renvoie tous les employés qui ont le même poste que Martin
Note : On considère que nom est clé primaire
11
Base de données VII
Celles qui renvoient une liste de valeurs
 …WHERE poste IN (SELECT poste FROM ..);
 La valeur doit être trouvée dans le résultat de la
sous requête
 On peut utiliser NOT IN
 WHERE numero > ALL (SELECT numero
FROM ..);
 La valeur testée doit être supérieure à toutes les
valeurs ramenées par la sous requête
 …WHERE poste > ANY (SELECT poste
FROM ..);
 La valeur testée doit être supérieure à au moins
une valeur obtenue par la sous requête
12
Base de données VII
Celles qui renvoient une liste de valeurs 2
 opérateur IN, NOT IN
 opérateur simple =, !=, <>, <, >,
<=, >= suivi de ALL ou ANY.
 =ANY est équivalent à IN
 !=ALL est équivalent à NOT IN
13
Base de données VII
Celles qui renvoient une liste de valeurs 3
 Exemple
SELECT
nom
FROM
client
WHERE num_client
IN (SELECT num_client
FROM commande
WHERE date_commande = '05-JUN-98');
14
Base de données VII
WHERE EXISTS et WHERE NOT EXISTS
SELECT
c.id_client
Dépendance
FROM
client c
WHERE EXISTS
(SELECT cde.id_client
FROM commande cde
WHERE cde.id_client = c.id_client)
 Cette
requête
utilise
une
sous-requête
dépendante.
 Pour obtenir des résultats la sous-requête doit être
vraie
15
Base de données VII
Sous-requêtes sur colonnes multiples
SELECT num
FROM commande
WHERE (date, id_client) =
(SELECT date, id_client
FROM commande
WHERE id_client = 'client1'
AND date = '25-jan-2005');
16
Bases de données VII
Les transactions et les accès
concurrents
ORACLE
MySQL
V > 5.0
17
Base de données VII
Transactions et accès concurrents
 Un SGBD est par définition multi-utilisateurs.
 L'exécution
simultanée
de
plusieurs
applications peut poser des problèmes
d'accès concurrents (une même information
étant manipulée par plusieurs utilisateurs à la
fois).
18
Base de données VII
Accès concurrents : le problème
 ‘Paul’ lit un enregistrement ‘a’ de la table ‘A’.
‘Paul’ modifie les données dans son application.
 ‘Pierre’ lit le même enregistrement.
 ‘Pierre’ modifie les données dans son
application
 ‘Pierre’ a fini plus tôt. Il sauvegarde ses
modifications.
 ‘Paul’ n’a rien vu, il sauvegarde à son tour.
 ‘Pierre’ revient sur son enregistrement et ne
trouve plus ses données.
19
Base de données VII
Les transactions
 Une transaction est une séquence d'opérations
(sauf create, alter, drop) qui accèdent et
modifient le contenu d'une base de données.
 Une transaction peut être validée ou invalidée.
 Le début d'une transaction peut être implicite
(début d’une session de connexion ou la fin
d’une transaction précédente) ou explicite.
 La fin d'une transaction peut être implicite ou
explicite.
20
Base de données VII
Les transactions 2
 Une fin explicite donne lieu à un
 ROLLBACK (annulation) ou un
 COMMIT (validation).
 Une fin implicite est définie par :
 Exécution d'une commande du langage
de définition de données (create alter
drop)
 Fin d’une session.
21
Base de données VII
Les transactions : points de sauvegarde
 On peut découper une transaction complexe
en créant des points d'arrêts (savepoints).
 SAVEPOINT <point_de_repere>;
 Ces points d'arrêt permettent d’annuler une
partie de la transaction. La syntaxe est :
 ROLLBACK TO [<point_de_repere>;
22
Base de données VII
Les transactions : Modèle ACID
 Atomicité
:
Une
transaction
est
complètement exécutée ou pas du tout
(unité atomique de traitement).
 Cohérence : une transaction prend une
base de données dans un état cohérent et
la transforme dans une nouvelle base de
données qui est dans un état cohérent
(préservation de la cohérence). Par contre,
la cohérence peut être violée pendant
l'exécution d'une transaction.
23
Base de données VII
Les transactions : Modèle ACID 2
 Isolation : les mises à jour faites par une
transaction ne sont visibles à l'extérieur de
celle-ci (pour les autres transactions)
qu'après la validation de la transaction.
 Durabilité : après la fin d'une transaction,
les mises à jour sont définitives même en
cas de futurs problèmes matériels (grâce
au mécanisme de reprise)
24
Base de données VII
Gestion des accès concurrents
 Un verrou est une variable d'état associée
à un objet X de la base et indiquant son
état vis à vis des opérations de
lecture/écriture.
 Verrou binaire 2 états
 verrouillé ou libre avec deux opérations
verrouiller(X) et libérer(X) => restrictif
 Verrou ternaire 3 états

en lecture (ou partagé), en écriture, ou
libre.
25
Base de données VII
Gestion des accès concurrents 2
 Granularité de contrôle de concurrence (de
forte à faible)




un attribut d’enregistrement,
un enregistrement,
une table,
une base.
 Plus la granularité est forte, plus le niveau
de concurrence augmente, et plus le niveau
de complexité de gestion des verrous
augmente
au
détriment
des
performances.
26
FIN DU COURS
27