Transcript Le langage SQL

Le langage SQL

Histoire
langages pour les BD relationnelless incluant toutes
les possibilités de l'algèbre relationnelle : QUEL,
SEQUEL, SQL (79, sur Oracle)
– Normalisation en 87 par ANSI de SQL
– plus d'une centaine de produits supportant SQL
aujourd'hui
SQL n'est pas un langage de programmation
– 2 douzaines d'instructions environ
– peut trouver sa place dans un langage de
programmation
–

1
Maria Berger - Maîtrise d'AES 2003-2004
Caractéristiques



SQL assure l'indépendance des données
SQL est un langage déclaratif (par opposition à
procédural)
Mode interactif ou programmé
–
–


2
interactif : instruction SQL tapée directement, instantanément
traitée
à l'intérieur d'un pgm : appel de SQL dans un pgm en C, ...
instruction SQL = requête
résultat d'une requête = table
Maria Berger - Maîtrise d'AES 2003-2004
Caractéristiques

SQL est un langage à dimension triple :
–
–
–
3
le DDL (Data Definition Language) : commandes de
SQL permettant de créer, modifier ou effacer la
définition (schéma) d'une BD ou d'une table.
le DML (Data Manipulation Language) : interrogation
et modification de l'information contenue dans les
tables
le DCL (Data Control Language) : sécurité et
confidentialité de la BD.
Maria Berger - Maîtrise d'AES 2003-2004
SQL1 - 86

LANGAGE DE DEFINITIONS DE DONNEES
–
–

LANGAGE DE MANIPULATION DE DONNEES
–
–
–
–

SELECT
INSERT
UPDATE
DELETE
OPEN
FETCH
CLOSE
LANGAGE DE CONTROLE DE DONNEES
–
–
–
4
CREATE TABLE
CREATE VIEW
GRANT et REVOKE
BEGIN et END TRANSACTION
COMMIT et ROLLBACK
Maria Berger - Maîtrise d'AES 2003-2004
Le Language de Manipulation des
Données : la consultation de données

Instruction SELECT : 3 parties principales :
–
–
–
clause (obligatoire) Select : précise les valeurs qui constituent
chaque ligne du résultat
clause (obligatoire) from : indique les tables desquelles le
résultat tire ses valeurs
clause where : donne la condition de sélection que doivent
satisfaire les lignes qui fournissent le résultat
SELECT schéma
FROM table
WHERE condition ;
5
Maria Berger - Maîtrise d'AES 2003-2004
Interrogation en SQL
SELECT <liste de colonnes>
FROM <liste de tables>
[WHERE <critère de jointure> AND <critère de sélection>]
[GROUP BY <attributs de partitionnement>]
[HAVING <citère de restriction>]
 Exemple :
SELECT NomCoureur
FROM Coureurs
WHERE CodePays = ‘SUI’;
6
Maria Berger - Maîtrise d'AES 2003-2004
Interrogation d’une seule table
CRU(Nom_Cru,Commune,Région,Coul)
VINS(Région,Coul,Millésime,Qualité)
CEPAGE_REGION(Cepage,R_PROD,Coul)
OU
EMP(Num,Nom,Fonction,N_Sup,Embauche,
Salaire,Comm,N_Dep)
DEPT(N_Dept,Nom,Lieu)
7
Maria Berger - Maîtrise d'AES 2003-2004
PROJECTION
Extraction simple : afficher une table
entièrement
SELECT *
FROM Nom_table;
 Exemple :

–
–
–
8
Tous les crus ?
Expression algébrique : cru
SQL : SELECT *
FROM CRU;
Maria Berger - Maîtrise d'AES 2003-2004
PROJECTION
SELECT Nom_Col1, ..., Nom_ColN
FROM Nom_table;

Exemple :
Requête : Liste des noms de crus
Algèbre : Nom_Cru (CRU)
SQL :
SELECT Nom_Cru
FROM CRU
La clause DISTINCT permet d'éliminer les doublons.
9
Maria Berger - Maîtrise d'AES 2003-2004
SELECTION
SELECT Nom_Colonne
FROM Nom_Table
WHERE critère ;
 Exemple :
–
–
–
10
Requête : La liste des noms de crus rouges
Algèbre : Nom_Cru ( Coul = rouge(CRU))
SQL : SELECT Nom_Cru
FROM CRU
WHERE Coul=‘Rouge’
Maria Berger - Maîtrise d'AES 2003-2004
Opérateurs de comparaison


=,>,<,>=,<=,<> (ou != ou ^=)
In et not in
Requête : quels sont les bons millésimes de Bordeaux ?
– SQL :
SELECT Millésime
FROM VINS
WHERE Région = ‘Bordeaux’ AND Qualité
=‘Bonne’ OR Qualité =‘Très Bonne’ OR
Qualité =‘Excellente’
OU
SELECT Millésime
FROM VINS
WHERE Région = ‘Bordeaux’ AND Qualité IN
(‘Bonne’, ‘Très bonne’, Excellente’ )
–
11
Maria Berger - Maîtrise d'AES 2003-2004
Opérateurs de comparaison

LIKE : appartenance à une chaîne de caractères
–
–

‘_’ remplace n'importe quel caractère
‘%’ remplace n'importe quelle chaîne de caractères
Exemple :
–
–
Requête : Quels sont les noms de cru qui commencent par Ch ?
SQL :
SELECT Nom_Cru
FROM CRU
WHERE Nom_Cru like ‘Ch%’
–
–
12
Requête : Quels sont les noms de cru possédant un ‘a’ en
seconde position ?
SQL :
SELECT Nom_Cru
FROM CRU
WHERE Nom_Cru like ‘_a%’
Maria Berger - Maîtrise d'AES 2003-2004
Opérateurs de comparaison


BETWEEN : appartenance à un intervalle
Exemple :
–
–
13
Requête : Quels sont les salariés gagnant entre
20000 et 25000 ?
SQL : SELECT Nom, Salaire
FROM EMP
WHERE Salaire BETWEEN 20000 AND
25000
Maria Berger - Maîtrise d'AES 2003-2004
Nom de colonne

Les colonnes constituant le résultat d’un
SELECT peuvent être renommées dans le
SELECT
–
–
14
Requête : salaire de chaque employé
SQL : SELECT Nom,Salaire « SALAIRE MENSUEL »
FROM EMP;
Maria Berger - Maîtrise d'AES 2003-2004
Opérateurs de comparaison

IS NULL et IS NOT NULL

Exemple :
–
–
15
Requête : le nom des crus dont la commune n’a pas
été renseignée
SQL : SELECT Nom_Cru
FROM CRU
WHERE Commune IS NULL
Maria Berger - Maîtrise d'AES 2003-2004
Tri des résultats
ORDER BY
SELECT attribut1, attribut2, ...
FROM Nom_table
ORDER BY attribut1 [ASC], attribut2 [DESC],
... ;
 Exemple :

–
–
16
Requête : Les bordeaux classés par millésimes et par couleur
SQL :
SELECT *
FROM VINS
WHERE Région = ‘Bordeaux’
ORDER BY Millésime, Coul
Maria Berger - Maîtrise d'AES 2003-2004
Fonctions

La relation résultat
–
–

Fonctions numériques :
– AVG : moyenne
–
Exemple : Age moyen des professeurs
SQL : SELECT ‘2001-10-10’-AVG(Date_Naissance)
FROM PROFESSEUR;
–
SUM : somme
–
Exemple : CA généré par les vendeurs de PACA:
SQL :
SELECT SUM(CA)
FROM Vendeur
WHERE Region = 'PACA'’;
–
–
17
ne comportera qu'une ligne
ou pourra simplement être considérée comme un nombre
Maria Berger - Maîtrise d'AES 2003-2004
Fonctions

COUNT : nombre d'éléments sélectionnés
–
–


Requête : Nombre de cépages bordelais ?
SQL :
SELECT COUNT ( *)
FROM CEPAGE_REGION
WHERE R_Prod = ‘Bordeaux’;
MIN, MAX, ...
Expressions et Fonctions sur les chaînes de caractères
–
|| : seul opérateur sur les chaînes de caractères : la
concaténation.

–
18
Résultat : chaîne de caractères obtenue en écrivant d'abord la
chaîne à gauche de || puis celle à droite de ||.
LOWER(chaîne) : Renvoie chaîne en ayant mis toutes ses
lettres en minuscules.

...
Maria Berger - Maîtrise d'AES 2003-2004
Fonctions

Expressions et fonctions sur les dates
–
19
Opérateurs sur les dates : + et  date +/- nombre : le resultat est une date obtenue en
ajoutant le nombre de jours nombre à la date date.
 date2 - date1 : le resultat est le nombre de jours entre les
deux dates.
Maria Berger - Maîtrise d'AES 2003-2004
Regroupements


Il est possible de subdiviser la table en groupes
Permet d’appliquer les fonctions d’aggrégation
à des sous-groupes
–
–
20
Requête : Combien de crus rouges et de crus blancs
sont produits dans chaque commune ?
SQL : SELECT Commune, Count(*)
FROM CRU
GROUP BY Commune, Coul;
Maria Berger - Maîtrise d'AES 2003-2004
Résultat
21
Commune
Coul
Count(*)
Aloxe-Corton
Rouge
1
Barsac
Blanc
1
Margaux
Rouge
2
Meursault
Blanc
3
Meursault
Rouge
1
Pauillac
Rouge
2
Maria Berger - Maîtrise d'AES 2003-2004
Selection des groupes


22
HAVING : conditions imposées aux groupes (de lignes)
à sélectionner
– pour éviter la confusion avec la clause WHERE (qui
ne s'applique qu'à des lignes seules)
Exemple :
– Requête : Donner les communes où au moins 2 crus
rouges ou crus blancs sont produits?
– SQL : SELECT Commune, Count(*)
FROM CRU
GROUP BY Commune, Coul
HAVING Count(*) >2;
Maria Berger - Maîtrise d'AES 2003-2004
Quantificateurs

ALL + opérateur de comparaison : teste si une
expression est vérifiée dans tous les cas de figure
–
–

SOME ou ANY : expression vraie si la comparaison est
vérifiée pour au moins une valeur
–
–
23
Requête : Tous les salariés ont-ils été recrutés avant le 1er
janvier 2001 ?
SQL :
‘2001-01-01’ > ALL (SELECT
Embauche FROM EMP)
Requête : vérifiez si au moins un salarié a été recruté depuis
un an
SQL :
‘2000-10-01’ > ANY (SELECT
Embauche FROM EMP)
Maria Berger - Maîtrise d'AES 2003-2004
Quantificateurs

24
EXISTS, NOT EXISTS
Maria Berger - Maîtrise d'AES 2003-2004
Comment interpréter une requête
complexe monotable ?






25
on considère la table spécifiée dans la clause FROM
on sélectionne les lignes sur la base de la clause
WHERE
on classe ces lignes en groupes comme spécifié dans la
clause GROUP BY
on ne retient que les groupes qui vérifient la clause
HAVING
de chacun de ces groupes, on extrait les valeurs
demandées dans la clause SELECT
les valeurs demandées sont ordonnées selon la clause
ORDER BY éventuelle.
Maria Berger - Maîtrise d'AES 2003-2004
Interrogations sur plusieurs tables :

SQL permet la liaison de plusieurs tables via 3
possibilités :
–
–
–
26
Les opérations de jointure entre 2 tables en se
basant sur l'égalité entre l'un des attributs de chaque
table
Le principe des requêtes imbriquées qui repose sur
le fait que le résultat d'une requête est une table
L'utilisation d'opérations ensemblistes pour
combiner le résultat de plusieurs requêtes.
Maria Berger - Maîtrise d'AES 2003-2004
Jointures
SELECT ...
FROM nom_table1, nom_table2...
WHERE critère;
– pas de condition de sélection : résultat obtenu =
produit cartésien des tables présentes derrière le
FROM.
– Requête : à partir de quel cépage principal est
produit le Meursault ?
– SQL :
SELECT Cépage
Si l’attribut Région avait le
FROM CEPAGE_REGION, CRU
même nom dans CRU que dans
WHERE région = R_Prod AND
CEPAGE_REGION on aurait
écrit : CRU.Région =
Commune = Meursault’
27CEPAGE_REGION.Région
Maria Berger - Maîtrise d'AES 2003-2004
Auto-jointure :


28
Requête : Donner pour chaque employé le nom
de son supérieur hiérarchique.
SQL :
SELECT EMP.Nom, chef.Mom
FROM EMP, EMP chef
WHERE EMP.N_sup= chef.Num;
Maria Berger - Maîtrise d'AES 2003-2004
Autres jointures

Le critère d'égalité est le critère de jointure le
plus naturel. Mais on peut utiliser d'autres types
de comparaisons comme critères de jointures.
–
–
29
Requête : Quels sont les employés gagnant plus
que SIMON ?
SQL : SELECT EMP.nom, EMP.salaire,
EMP.fonction
FROM EMP, EMP empbis
WHERE EMP.salaire > empbis.salaire
AND empbis.nom = 'SIMON';
Maria Berger - Maîtrise d'AES 2003-2004
Requêtes imbriquées


30
une clause WHERE est elle-même le résultat
d'un SELECT
renvoient une ou plusieurs valeurs
Maria Berger - Maîtrise d'AES 2003-2004
Requêtes imbriquées

31
Sous-interrogation ramenant une seule valeur
– Requête : Quels sont les vins qui sont produits dans
la même commune que La Grappe d’Or ?
– SQL : SELECT Nom_Cru
FROM VINS
WHERE Commune = (SELECT Commune
FROM VINS
WHERE Nom_Cru =
‘La Grappe d’Or’)
– Remarque : auto-jointure possible pour répondre à
cette question
Maria Berger - Maîtrise d'AES 2003-2004
Requêtes imbriquées
32

une sous-interrogation qui ne ramène aucune
ligne se termine avec un code d'erreur.

une sous-interrogation ramenant plusieurs
lignes provoquera aussi, dans ce cas, une
erreur
Maria Berger - Maîtrise d'AES 2003-2004
Requêtes imbriquées


Sous-interrogation ramenant plusieurs
lignes
Avec des opérateurs de comparaison admettant
à leur droite un ensemble de valeurs comme :
–
–
33
l'opérateur IN
les opérateurs obtenus en ajoutant ANY ou ALL à la
suite d'un opérateur de comparaison classique (=,
<>, >, >=, <, <=)
Maria Berger - Maîtrise d'AES 2003-2004
Requêtes imbriquées


34
Requête : Quels sont les employés gagnant plus que
tous les employés du département 30 ?
SQL : SELECT Nom, Salaire
FROM Emp
WHERE salaire > ALL(SELECT Salaire
FROM Emp
WHERE N_Dep=30);
Maria Berger - Maîtrise d'AES 2003-2004
Requêtes imbriquées

Sous-interrogation ramenant plusieurs colonnes
–
–
Exemple : Quels sont les employés ayant la même
fonction et le même supérieur que CODD ?
SQL : SELECT Nom, Fonction, N_Sup
FROM Emp
WHERE (Fonction,N_Sup) = (SELECT Fonction,N_Sup
FROM Emp
WHERE Nom = 'CODD');
35
Maria Berger - Maîtrise d'AES 2003-2004
Requêtes imbriquées


Sous-interrogation ramenant au moins une ligne
L'opérateur EXISTS permet de construire un prédicat
vrai si la sous-interrogation qui suit ramène au moins
une ligne.
–
–
36
Requête : Quels sont les employés travaillant dans un
département qui a procédé à des embauches depuis le début
de l'année 2001 ?
SQL :
SELECT *
FROM Emp Empbis
WHERE EXISTS (SELECT *
FROM EMP
WHERE Embauche >= '01-jan-01'
AND N_Dept = Empbis.N_Dept);
Maria Berger - Maîtrise d'AES 2003-2004
Les opérateurs ensemblistes

permettent de "joindre" des tables verticalement c'est-àdire de combiner dans un résultat unique des lignes
provenant de deux interrogations. Les opérateurs
ensemblistes sont les suivants :
–
–
–


37
l'union : UNION
l'intersection : INTERSECT
la différence relationnelle : MINUS
La syntaxe d'utilisation est la même pour ces trois
opérateurs :
SELECT ... {UNION | INTERSECT | MINUS } SELECT
...
Maria Berger - Maîtrise d'AES 2003-2004
Les opérateurs ensemblistes

Dans une requête utilisant des opérateurs ensemblistes :
–
–
–
Tous les SELECT doivent avoir le même nombre de colonnes
sélectionnées, et leur types doivent être un à un identiques.
Les doubles sont éliminés (DISTINCT implicite).
Les noms de colonnes sont ceux du premier SELECT.
On peut combiner le résultat de plus de deux SELECT au
moyen des opérateurs UNION, INTERSECT, MINUS.
SELECT ... UNION SELECT ... MINUS SELECT ...

–
Expresion évaluée de gauche à droite. Modification de l'ordre
d'évaluation par des parenthèses.
SELECT ...
UNION (SELECT ...
MINUS
38
SELECT ...)
Maria Berger - Maîtrise d'AES 2003-2004
Les opérateurs ensemblistes
Exemple : Lister tous les enseignants
SELECT Nom, Prénom
FROM MdC
UNION
SELECT Nom, Prénom
FROM Professeur ;

39
Maria Berger - Maîtrise d'AES 2003-2004
Coment interpréter une requête
complexe multitable ?







40
on considère les tables spécifiées dans la clause FROM
on effectue la jointure de ces tables selon le critère de
jointure de la clause WHERE
on sélectionne les lignes de la jointure sur la base des
autres conditions de la clause WHERE
on classe ces lignes en groupes comme spécifié dans la
clause GROUP BY
on ne retient que les groupes qui vérifient la clause
HAVING
de chacun de ces groupes, on extrait les valeurs
demandées dans la clause SELECT
les valeurs demandées sont ordonnées selon la clause
ORDER BY éventuelle.
Maria Berger - Maîtrise d'AES 2003-2004
Coment interpréter une requête
complexe multitable ?
Exemple :
SELECT N°Client, COUNT(*), SUM(QtéCom)
FROM Commande C, LigneCom L
WHERE C.N°Com = L.N°Com
AND N°Pro = ‘PA 60’
GROUP BY N°Client
HAVING COUNT(*) >= 2
ORDER BY N°Client

41
Maria Berger - Maîtrise d'AES 2003-2004
Le Language de Manipulation des
Données : la modification de
données
Insertion de nouveaux n-uplets
INSERT INTO nom_table(nom_col1, nom_col2,...)
VALUES (val1, val2...)
 Exemple :
INSERT INTO Etudiant (n°Et, Nom, Prénom)
VALUES(96035, ‘Diego’, ‘Berger’)

42
Maria Berger - Maîtrise d'AES 2003-2004
La modification de données
Il est possible d'insérer dans une table des lignes
provenant d'une autre table. La syntaxe est la suivante :
INSERT INTO nom_table(nom_col1, nom_col2, ...)

SELECT ...
 Exemple : Insérer dans la table Bonus les noms et
salaires des directeurs.
INSERT INTO bonus
SELECT nom, salaire
FROM emp
WHERE fonction = 'directeur';
43
Maria Berger - Maîtrise d'AES 2003-2004
La modification de données

Modification de lignes
–

La commande UPDATE permet de modifier les valeurs d'une
ou plusieurs colonnes, dans une ou plusieurs lignes existantes
d'une table. La syntaxe est la suivante :
UPDATE nom_table
SET nom_col1 = {expression1 | ( SELECT ...) },
nom_col2 = {expression2 | ( SELECT ...) }
[WHERE critère];
Exemple : Augmenter de 10% les salaires [des
ingénieurs].
UPDATE emp
SET salaire = salaire * 1.1
[WHERE fonction = 'ingenieur' ];
44
Maria Berger - Maîtrise d'AES 2003-2004
La modification de données

Suppression de lignes
–
La commande DELETE permet de supprimer des lignes d'une
table. La syntaxe est la suivante :
DELETE FROM nom_table
WHERE critère;
–
–
Toutes les lignes pour lesquelles le critère est évalué à vrai sont
supprimées. En l'absence de clause WHERE, toutes les lignes
de la table sont supprimées.
Exemple :
DELETE FROM emp
WHERE fonction = ‘retraité‘;
45
Maria Berger - Maîtrise d'AES 2003-2004
Le language de définition de
données

Création d'une table
CREATE TABLE nom_table
(nom_col1 TYPE1,[NOTNULL/
PRIMARY KEY/FOREIGN KEY]
nom_col2 TYPE2,[.../.../...]
...);
–
–
–
–
46
Types acceptés :
CHAR(longueur), VARCHAR(longueur)
SMALLINT, INTEGER, DECIMAL(m,n), FLOAT, SERIAL(n)
DATE
Maria Berger - Maîtrise d'AES 2003-2004
Création d'une table
47
CREATE TABLE Departement
(NDep SERIAL(20),NOTNULL,PRIMARY KEY,
NomDep CHAR(20),
Directeur CHAR(20),
Budget DECIMAL(6,0));
CREATE TABLE Etudiant
(NEtud SERIAL(20),NOTNULL,PRIMARY KEY,
Nom CHAR(20),
Prénom CHAR(20),
DateNaissance DATE,
Rue CHAR(80),
CodePostal DECIMAL(5,0),
Ville CHAR(20),DEFAULT ‘‘PARIS’’,
NDep SERIAL(20),NOTNULL,FOREIGN KEY);
Maria Berger - Maîtrise d'AES 2003-2004
Suppresion et modification d'une
table

DROP TABLE nom_table ;
–

Exemple : DROP TABLE Etudiant ;
Modification d'une table
–
Ajoût d'une ou plusieurs colonnes :
ALTER TABLE nom_table
ADD(nom_col1 TYPE1, nom_col2 TYPE2, ...);
– option : [BEFORE nom_col_before]
– Exemple : On aimerait connaître le téléphone des étudiants
ALTER TABLE Etudiant
ADD(Téléphone DECIMAL(10,0)
BEFORE NDep);
48
Maria Berger - Maîtrise d'AES 2003-2004
Modification d'une table

Suppression d'une colonne :
ALTER TABLE nom_table
DROP nom_col;
–

Modification d’une table :
ALTER TABLE nom_table
MODIFY(nom_col1 TYPE1,nom_col2 TYPE2,...);
–
49
Attention aux problèmes d'intégrité !
Exemple : Un nom peut dépasser 20 caractères
ALTER TABLE Etudiant
MODIFY(Nom Char(25));
Maria Berger - Maîtrise d'AES 2003-2004
Modification d'une table
Changement de nom de tables ou de colonnes :
RENAME TABLE ancien_nom TO nouveau_nom ;
RENAME COLUMN
nom_relation.ancien_nom_col TO
nouveau_nom_col ;

–
50
Exemple :
RENAME COLUMN Etudiant.DateNaissance
TO BirthDay;
Maria Berger - Maîtrise d'AES 2003-2004
Les vues


Les vues permettent d'assurer l'objectif d'indépendance
logique. Grace à elles, chaque utilisateur pourra avoir
sa vision propre des données.
Créer une vue
La commande CREATE VIEW permet de créer une vue en
spécifiant le SELECT constituant la définition de la vue :
CREATE VIEW nom_vue [(nom_col1,...)]
AS SELECT ...
WITH CHECK OPTION ;
–
51
Maria Berger - Maîtrise d'AES 2003-2004
Les vues
Exemple : Création d'une vue constituant une
restriction de la table emp aux employés du
departement 10.
CREATE VIEW emp10 AS
SELECT *
FROM emp
WHERE n_dept = 10 ;
 Le CHECK OPTION permet de vérifier que la mise à
jour ou l'insertion faite à travers la vue ne produisent
que des lignes qui font partie de la sélection de la vue.
 Supprimer une vue
DROP VIEW nom_vue;

52
Maria Berger - Maîtrise d'AES 2003-2004
Les index
Considérons le SELECT suivant :
SELECT *
FROM Etudiant
WHERE nom = 'MARTIN'

–
–


53
moyen de recherche des lignes avec nom = 'MARTIN' :
balayer toute la table
prohibitif dès que nb lignes > qq centaines
Solution offerte par tous les SGBD : création d'index
Les index sont des structures permettant de retrouver
une ligne dans une table à partir de la valeur d'une
colonne ou d'un ensemble de colonnes.
Maria Berger - Maîtrise d'AES 2003-2004
Choix des index

Indexer en priorité :
–
–
–

Ne pas indexer :
–
–
54
les clés primaires
les colonnes servant de critère de jointure
les colonnes servant souvent de critère de recherche
les colonnes contenant peu de valeurs distinctes
(index alors peu efficace)
les colonnes fréquemment modifiées
Maria Berger - Maîtrise d'AES 2003-2004
Créer un index




Un index peut être créé par la commande suivante :
CREATE [UNIQUE] INDEX nom_index
ON nom_table (nom_col1 , nom_col2, ...)
Option UNIQUE : interdiction que deux lignes aient la
même valeur dans la colonne indexée.
Les requêtes SQL sont transparentes au fait qu'il existe
un index ou non.
Effacement d'un index :
DROP INDEX Nom_de_l’index
55
Maria Berger - Maîtrise d'AES 2003-2004
Les contraintes d'intégrité

Sur les colonnes :
–
–
–
–
–

Sur la table globalement :
–
–
–
56
NOT NULL : force la saisie de la colonne
DEFAULT : précise une valeur par défaut
UNIQUE : vérifie que toutes les valeurs sont différentes
CHECK : vérifie la condition précisée
CONSTRAINT : permet de nommer une contrainte
CONSTRAINT
PRIMARY KEY : clé primaire
FOREIGN KEY (liste_col1) REFERENCES table(liste_col2): clé
étrangère => intégrité référentielle
Maria Berger - Maîtrise d'AES 2003-2004
Les contraintes d'intégrité

Clé étrangère :
–
–
–

Du point de vue de la table qui référence (T1) :
–

FOREIGN KEY (T1) REFERENCES table(T2): clé étrangère =>
intégrité référentielle
Du point de vue de la table référencée (T2) :
–
–
57
Attribut ou groupe d’attribut dans une table T1 dont les valeurs
doivent exister comme valets de la clé candidate dans la table
T2.
T1 : table qui référence : table secondaire du lien (contient la clé
étrangère)
T2 : table référencée : table primaire du lien
Modification de la clé primaire => effets sur les autres tables
utilisant cette clé comme clé étrangère ?
REFERENCES complétée par ON UPDATE ou ON DELETE
Maria Berger - Maîtrise d'AES 2003-2004
Les contraintes d'intégrité




58
RESTRICT : échec de la modification ou
suppression de la clé primaire
CASCADE : modification ou suppression des
lignes correspondantes en cascade
SET NULL : mettre NULL
SET DEFAULT : mettre la valeur par défaut
Maria Berger - Maîtrise d'AES 2003-2004
Gestion des transactions

Plusieurs utilisateurs peuvent accéder à la base de
données de façon concurrente, "en même temps".
–


59
Exemple : un même système de réservations de billets d'avions
est utilisé en concurrence par des centaines d'employés
d'agences de voyage.
Ce concept pose des problèmes quant à la cohérence
et l'intégrité de la base de données.
Le SGBD devra donc gérer les utilisations concurrentes
sur les données avec le plus d'efficacité possible.
Maria Berger - Maîtrise d'AES 2003-2004
Gestion des transactions

Une transaction est un ensemble de requêtes
élémentaires sur les données d'une base.
–
Exemple : vente de produit à un client = 2 parties



Fin de transaction :
–
–
60
mise à jour de l'inventaire en soustrayant la quantité vendue du
produit, et
mise à jour de la table des encaissements à réaliser pour facturer
le client.
COMMIT : Validation.
ROLLBACK : suite à un echec, le SGBD doit revenir à l'état
précédant le début de l’action. Ceci est fait automatiquement
par la commande ROLLBACK.
Maria Berger - Maîtrise d'AES 2003-2004
Administration d'une Base de
Données

GESTION DES DROITS
–
protéger les données de la base contre les accès non autorisés



LES DIFFERENTS UTILISATEURS
–
ADMINISTRATEUR BD

–
–
possède tous les droits
UTILISATEURS PRIVILEGIES

peuvent créer des relations
UTILISATEURS FINAUX

61
enregistrer et authentifier les utilisateurs
définir des autorisations de manipulation des objets de la base par
les utilisateurs (relation, vue, contrainte d'intégrité, index)
peuvent manipuler des relations
Maria Berger - Maîtrise d'AES 2003-2004
Les différents droits sur une relation




62
DROIT D'INTERROGATION
DROIT DE MISE A JOUR (insertion,
modification, suppression de tuples)
DROIT D'ADMINISTRATION (déclaration de
clés, définition d'index, modif. / suppression du
schéma, transmission des droits)
DROIT DE CREATION (création schéma
relation)
Maria Berger - Maîtrise d'AES 2003-2004
Droits sur une relation

EXPRESSION DES DROITS EN SQL
–
DROITS GERES PAR SQL

–
-> droits de manipulation des relations de base et des
vues
ATTRIBUTION DE DROITS
grant <droits> on <relation> to <usagers>
– grant SELECT on VINS to Grobuveur

–
REVOCATION DES DROITS (SQL2)
revoke <droits > on < relation > to <usagers>
– revoke SELECT on VINS to grobuveur

63
Maria Berger - Maîtrise d'AES 2003-2004
Le mode Client-Serveur


Serveur : serveur de ressources communes
Client : logiciel qui accède au serveur
Machine A
Utilisateur
Logiciel
Client
Machine B
requête
réponse
Logiciel
Serveur
Besoin de permettre la connexion de n’importe quel type de
client avec n’importe quel type de serveur
Naissance de protocoles comme ODBC (Open DataBase
Connectivity) ou JDBC (Java DataBase Connectivity)
64
Maria Berger - Maîtrise d'AES 2003-2004
Le mode Client-Serveur


65
permettent de traduire les requêtes du logiciel
client en ordres exploitables par le serveur puis
à traduire le format des données renvoyées par
le serveur en données manipulables par le
client
Problème : le choix du bon constructeur. Que
mettre dans le client et que mettre sans le
serveur ?
Maria Berger - Maîtrise d'AES 2003-2004
Le mode ClientServeur
ODBC : accès aux bases de
données dans le monde
de Microsoft
66
Maria Berger - Maîtrise d'AES 2003-2004