14/06/2017

Cours de base de données

______________________________________________________________________

SQL-Interrogation des bases de données Le langage de requêtes SQL

Le langage de requêtes SQL 4 types de fonctions (briques) :

1. Introduction
2. Langage déclaratif Extraction des données • SELECT

3. Syntaxe générale d’une requête SQL

4. Syntaxe détaillée Langage de définition • CREATE, ALTER, DROP , RENAME
4.1. Expression de la projection des données LDD
4.2. Expression des restrictions
4.3. Expression des jointures Langage de
4.4. Expression des unions, intersections, différences manipulation des • INSERT, UPDATE, DELETE, MERGE
données LMD
5. Travail sur le résultat d’une requête SQL
5.1. Tri du résultat Contrôle des • SAVEPOINT; COMMIT, ROLLBACK
5.2. Effectuer un calcul sur le résultat transactions
5.3. Effectuer des sous-totaux
5.4. Restrictions sur les sous-totaux
Langage de contrôle • GRANT, REVOKE
6. Exemple de requêtes des données

4 types de fonctions (briques) : Introduction

SQL permet de communiquer avec un SGBD

Extraction • SELECT SELECT matriculate, nom

des données FROM etudiant;

Etudiant
SGBD
Matricule Nom
Q0001 Boussiga
Ce chapitre concerne uniquement Q0002 Chibane
l’instruction la brique : Extraction des Q0003 Kaidi
données et l’instruction SELECT Q0004 Mosteghanemi

1
 14/06/2017

SQL est un Langage déclaratif Syntaxe d’une requête SQL

SELECT <liste des attributs du résultat>
FROM <liste des tables impliquées dans la requête>

SQL est un langage déclaratif :
WHERE <formule de sélection> ;
 L'utilisateur définit le résultat à obtenir à l’aide de prédicats
(QUOI), le système optimise la procédure de recherche
La formule de sélection utilise les constituants (colonnes) des tables
(COMMENT) présentes dans la clause FROM ;

La formule de sélection peut contenir :

Par opposition à l’algèbre relationnelle qui introduit un ordre dans
l’enchaînement des opérations
Les opérateurs de comparaison : >,<,>=,<=,<>

Par opposition aux langages impératifs : Basic, Pascal, C, Java ...
Les opérateurs logiques : AND, OR, NOT

Les prédicats : ALL, ANY, EXISTS, BETWEEN, LIKE, IN ( à

combiner éventuellement avec NOT)

Des sous-requêtes

Syntaxe d’une requête SQL Syntaxe d’une requête SQL

Correspondance entre le SQL et le langage algébrique
On se basera sur cet exemple de Schéma pour illustrer les
SELECT a1,a2
R S commandes SQL:
FROM R, S FILM(CodeFilm, Titre, Année, Durée, Réalisateur, Genre)
P1
P1 ACTEUR(Nom, Prénom, DateNaissance, Sexe, Nationalité)
WHERE P1
And R S
JOUE(CodeFilm, NomActeur)
R S

Sémantique
1 Faire le produit cartésien de toutes les tables de la clause a1,a2
FROM
2 Appliquer à ce résultat tous les prédicats de la clause
WHERE
3 Afficher les colonnes de la clause SELECT

Expression de la projection

[Link] : permet de désigner un attribut
 Ex : afficher les noms des Réalisateurs
SELECT Film.Réalisateur
FROM Film ;

NomAttribut : permet de désigner un attribut s’il n ’y a pas
Expression de la projection d’ambiguité sur sa provenance.
réalisateur
Zucker
SELECT Réalisateur Hamina
FROM Film ;
Montecorvo
Cod Titre annee réalisateur duree Hamina
F1 Lancelo 1999 Zucker 120
F2 Vent du sud 1967 Hamina 120
F3 Batille d’Alger 1967 Montecorvo 120
F4 Année de braise 1965 Hamina 180

2
 14/06/2017

Expression de la projection Expression de la projection

DISTINCT : permet d’éliminer les lignes en double dans le
‘*’ : permet de désigner dans la clause SELECT tous les attributs
résultat. Ex : chaque Réalisateurs n’est affiché qu’une seule fois d’une table
SELECT DISTINCT Réalisateur
FROM Film ; réalisateur  Ex : afficher la table Film
Zucker SELECT Film.*
FROM Film ;
Hamina
ou
Montecorvo
SELECT *
Cod Titre annee réalisateur duree FROM Film ;
F1 Lancelo 1999 Zucker 120
F2 Vent du sud 1967 Hamina 120 Cod Titre annee réalisateur duree
F3 Batille d’Alger 1967 Montecorvo 120 F1 Lancelo 1999 Zucker 120
F4 Année de braise 1965 Hamina 180 F2 Vent du sud 1967 Hamina 120
F3 Batille d’Alger 1967 Montecorvo 120
DISTINCT agit sur toute la ligne du résultat F4 Année de braise 1965 Hamina 180

hadj de Bouira - Février 2015

Expression de la projection Expression de la projection

Spécifier des alias de colonnes avec AS : permet de

Expression d’un calcul : spécifier l’entête d’une colonne affichée dans le résultat
 Permet de spécifier un calcul appliqué à une colonne
 Ex : afficher pour chaque film, la durée en heure (durée/60) avec l’alias ‘durée
 Ex : afficher les titres des Film et les deux derniers chiffre de leur année de en heures’
parution
SELECT titre, duree/60 AS ‘Durée en heures’
SELECT Titre, Année MOD 100 FROM Film;
FROM Film ; Titre Durée
en
heures
Titre annee
Lancelo 2
Lancelo 99 Co Titre annee réalisateur duree
d Vent du sud 2
Vent du sud 67
F1 Lancelo 1999 Zucker 120 Batille d’Alger 2
Batille d’Alger 67
F2 Vent du sud 1967 Hamina 120 Année de braise 3
Année de braise 65
F3 Batille d’Alger 1967 Montecorvo 120
F4 Année de braise 1965 Hamina 180

Expression de la projection

Projection d’une constante :
 Ex : afficher réalisateur ‘a réalisé ‘ titre
SELECT realisateur,‘ A réalisé‘, titre
FROM Film

Expression des restrictions

Réalisateur A réalisé Titre
Zucker, , A réalisé Lancelo
Hamina A réalisé Vent du sud
Montecorvo, A realisé Bataille d’Alger
Hamina, A réalisé Années de braise

3
 14/06/2017

Expression des restrictions Expression des restrictions

Spécification d’un Prédicat dans la clause « WHERE »
Prédicat composé :

Notion de Prédicat :  Combinaison de prédicats reliés par les connecteur AND (ET), OR (OU)
 Expression logique à laquelle on peut répondre par VRAI ou par FAUX pour
 L’utilisation de parenthèses peut être utile pour préciser comment combiner des
chaque ligne de la table opérande.
prédicats reliés par des AND et des OR
 Une restriction ne garde que les lignes pour lesquelles le prédicat est vrai.

Prédicat simple :
 Ex : titre des films policiers de l'année 1960 et de l'année 1968 ?
 [Link] θ Valeur
SELECT Titre
ou FROM Film
NomAttribut θ Valeur WHERE (Année=1960 OR Année=1968)
AND Genre = ‘policier’;
θ est un comparateur : =, <, <=, >, >=, <>
 Ex : titre des films de l'année 1998 ?
SELECT titre
FROM Film
WHERE année = 1998 ;

Expression des restrictions Expression des restrictions

NOT : inverse le résultat d’un prédicat
Prédicat avec IN : teste si la valeur d’une colonne est incluse dans
un ensemble
 Ex : titre des films policier produits avant 1980 ?  Ex : titre des films produits entre 1980 et 1983 ?
SELECT Titre
FROM Film SELECT Titre
WHERE Année <1980 AND genre=‘Policier’ ; FROM Film
WHERE Année IN (1980, 1981, 1982, 1983) ;
peut aussi s’écrire :  Ex : Titre des films réalisés par : PARKER,
MALLE, ou VERNEUIL ?
SELECT Titre SELECT Titre
FROM Film FROM Film
WHERE NOT (Année >1980 AND genre<>‘Policier’) ; WHERE Réalisateur IN
(‘PARKER’,’MALLE’,’VERNEUIL’);

BETWEEN : Pour spécifier un prédicat comportant une intervalle
 Ex : Films qui n’ont pas été réalisés par : PARKER, MALLE, ou VERNEUIL ?
 Ex : titre des films produits entre 1980 et 1983 ? SELECT Titre
FROM Film
SELECT Titre WHERE Réalisateur NOT IN
FROM Film (PARKER’,’MALLE’,’VERNEUIL’) ;
WHERE Année BETWEEN 1980 AND 1983 ; © C.
HANAC
HI, J.M.
THEVE
Kamal BAL - Cours SGBD - Université Akli Mohand Oulhadj de Bouira - Février 2015 NIN

Expression des restrictions

Prédicat avec LIKE : Comparer la chaîne de caractère d’une colonne à
un Modèle
 Modèle : chaîne de caractère avec des jokers :

‘_’ (souligné)  remplace tout caractère isolé

‘%’ (pourcentage)  remplace Zéro, un ou plusieurs caractères
 Ex : les films dont le nom du réalisateur contient la lettre ‘P’ en 2eme position ?
SELECT Titre
Expression des jointures
FROM Film
WHERE Réalisateur LIKE ‘_P%’;

UPPER, LOWER : transformer la chaîne de caractère d’une colonne majuscules ou
en minuscules
 Ex : titre des films de Malle ?
SELECT Titre
FROM Film
WHERE UPPER(Réalisateur) =‘MALLE’

4
 14/06/2017

Expression des jointures Expression des jointures

 Ex : Afficher les réalisateurs avec qui les acteurs ont joué ?

S’évalue comme une restriction sur le produit cartésien SELECT [Link], Film.Réalisateur
des deux tables FROM Film, Joue
WHERE [Link] = [Link];
SELECT *
FROM acteur, joue Nom Acteur Realisateur
Acteur 1 Zucker
Produire toutes les combinaisons possibles de acteur et de joue
Acteur 2 Zucker

Jointure classique en SQL : ACteur3 Montecorvo

 Exprimée par un prédicat dans la clause WHERE Acteur 4 Hamina

NomRel1.NomAtt1 θ NomRel2.NomAtt2 film joue

NomActeur code
θ est un comparateur : =, <, <=, >, >=, <> Cod Titre annee réalisateur duree
Acteur 1 F1
F1 Lancelo 1999 Zucker 120
Acteur 2 F1
F2 Vent du sud 1967 Hamina 120
ACteur3 F3
F3 Batille d’Alger 1967 Montecorvo 120
Acteur 4 F4
F4 Année de braise 1965 Hamina 180

Expression des jointures Expression des jointures

 Ex : Qui a joué les films réalisés par « Zucker»
IN : expression d’une Jointure avec une sous-requête
SELECT [Link]  Une sous-requête produit un ensemble de valeurs compatibles avec l’attribut de
FROM Joue, Film
WHERE [Link] = [Link] jointure de la relation dite externe ([Link] IN (SELECT …))
AND Réalisateur = ‘Zucker‘ ;  Cet ensemble de valeurs est extrait parmi les valeurs de l’attribut de jointure de la
NomActeur relation dite interne ((SELECT Attj FROM RelInt …))
Acteur 1 Code_film
 Ex : Qui a joué les films réalisés par ‘Hamina’?
Acteur 2 F2
SELECT NomActeur
FROM Joue F4
film WHERE [Link] IN
joue (SELECT [Link]
Cod Titre annee réalisateur duree FROM Film
Nom Acteur code WHERE Réalisateur=‘Hamina‘) ;
F1 Lancelo 1999 Zucker 120
Acteur 1 F1
F2 Vent du sud 1967 Hamina 120
Acteur 2 F1
F3 Batille d’Alger 1967 Montecorvo 120
ACteur3 F3
F4 Année de braise 1965 Hamina 180 WHERE [Link] IN (F2,F4)
Acteur 4 F4

Expression des jointures Expression des jointures

EXISTS : tester l'existence d’un lien via une sous-requête
EXISTS : tester l'existence d’un lien via une sous-requête
 Le prédicats EXISTS est VRAI si la sous-requête associée ramène au moins une SELECT NomActeur
FROM Joue
ligne résultat. WHERE EXISTS (SELECT *
 La sous-requête est exécutée pour chaque ligne de la relation dite externe. A chaque FROM Film
WHERE duree>= 120
sous-requête l’attribut de jointure de la relation externe est considéré comme une AND [Link] = [Link]);
constante (restriction )
Pour nomActeur=Acteur 1
 La sous-requête contient un prédicat de jointure entre la relation externe et la SELECT NomActeur
relation dite interne. Cette jointure est traitée comme une restriction. FROM Joue
WHERE EXISTS (SELECT *
 Ex : Qui a joué dans des films de durée supérieure à 120 mn? FROM Film
Nom Acteur code WHERE duree>= 120
SELECT NomActeur Acteur 1 F1
AND [Link] = F1);
FROM Joue
WHERE EXISTS (SELECT * Acteur 2 F1 Cod Titre annee réalisateur duree
FROM Film
WHERE duree>= 120 ACteur3 F3 F1 Lancelo 1999 Zucker 120
AND [Link] = [Link]); Acteur 4 F4 F2 Vent du sud 1967 Hamina 120

F3 Batille d’Alger 1967 Montecorvo 120

F4 Année de braise 1965 Hamina 180

Akli Mohand Oulhadj de Bouira - Février 2015

5
 14/06/2017

Expression des unions, intersections,

différences

Composition de requêtes SQL :
 Opérateurs de composition (OC)

UNION
INTERSECT

MINUS
Expression des unions, intersections et
 Syntaxe des requêtes composées différences
SELECT Colonnes-1
FROM …
OC
SELECT Colonnes-2
FROM … ;

Attention ! Les colonnes spécifiées dans Colonnes-1 et Colonnes-2 doivent être

compatibles deux à deux

Expression des unions, intersections, Expression des unions

différences
 Ex : noms et prénoms des acteurs qui n’ont joué ni dans des
UNION : questions avec OU
comédies ni dans des films policiers  Ex : noms et prénoms des acteurs qui ont joué dans des
SELECT Nom, Prénom comédies ou dans des films policiers
From acteur
MINUS SELECT Nom, Prénom
( SELECT Nom, Prénom FROM Acteurs , Joue , Film
FROM Acteurs , Joue , Film WHERE Genre = ‘Comédie’
WHERE Genre = ‘Comédie’ AND [Link] = [Link]
AND [Link] = [Link] AND [Link] = [Link]
AND [Link] = [Link] UNION
UNION SELECT Nom, Prénom
SELECT Nom, Prénom FROM Acteurs A, Joue J, Film F
FROM Acteurs A, Joue J, Film F WHERE Genre = ‘Policier’
WHERE Genre = ‘Policier’ AND [Link] = [Link]
AND [Link] = [Link] AND [Link] = [Link]
AND [Link] = [Link]
) ;

Expression des intersections Expression des différences

INTERSECT : questions avec ET
MINUS : questions avec NE … PAS,

 Ex : Nom et prénom des acteurs qui ont joué dans des  Ex : Nom et prénom des acteurs qui n’ont pas joué dans des
comédies et des policiers comédies
SELECT Nom, Prénom SELECT Nom, Prénom
FROM Acteurs , Joue , Film FROM Acteurs
WHERE Genre = ‘Comédie’ MINUS
AND [Link] = [Link] SELECT Nom, Prénom
AND [Link] = [Link] FROM Acteurs , Joue , Film
INTERSECT WHERE Genre = ‘Comédie’
SELECT Nom, Prénom AND [Link] = [Link]
FROM Acteurs A, Joue J, Film F AND [Link] = [Link]
WHERE Genre = ‘Policier’
AND [Link] = [Link] Principe : pour tester l’absence de lien entre un acteur et une comédie, on
AND [Link] = [Link] ; sélectionne tous les acteurs sauf (moins) ceux qui sont reliés a un comédie
(jointure)

6
 14/06/2017

Expression des différences Tester l’absence de lien

NOT EXISTS : tester l’absence de lien via une sous-

NOT IN : La différence peut aussi s’exprimer à l ’aide du prédicat requête
NOT IN
 La sous-requête est exécutée une seule fois.  Le prédicats NOT EXISTS est VRAI si la sous-requête associée ne ramène aucune
ligne résultat.
 Le prédicats NOT IN est appliqué à toutes les lignes la relation externe avec
l’ensemble de valeurs produit par la sous-requête.  La sous-requête est exécutée pour chaque ligne de la relation dite externe. A chaque
sous-requête l’attribut de jointure de la relation externe est considéré comme une
⇒ résultat = relation externe - sous-requête
constante (restriction )
⇒ Ex : Nom et prénom des acteurs qui n’ont pas joué dans des  La sous-requête contient un prédicat de jointure entre la relation externe et la
comédies relation dite interne. Cette jointure est traitée comme une restriction.

SELECT Nom, Prénom  Ex : Qui n’a jamais joué avec Alain?

FROM Acteurs
WHERE Nom NOT IN ( SELECT NomActeur
SELECT Nom FROM Joue
FROM Acteurs , Joue , Film WHERE NOT EXISTS (
WHERE Genre = ‘Comédie’ SELECT *
AND [Link] = [Link] FROM Film
AND [Link] = [Link] ) ; WHERE Réalisateur = ‘Alain‘
AND [Link] = [Link]) ;

Travailler sur le résultat d’une requête SQL

Travail sur le résultat d’une requête SQL

Les clauses suivantes sont interprétées après la clause WHERE. Elles permettent
d’effectuer un traitement supplémentaires avant d’afficher le résultat

Travailler sur le résultat d’une requête SQL  Tri du résultat

 Effectuer un calcul sur le résultat
 Effectuer des sous-totaux
 Restrictions sur les sous-totaux

Tri du résultat Effectuer un calcul sur le résultat

ORDER BY : permet de trier le résultat
Fonctions Agrégat : SQL propose 5 fonctions permettant
 Dernière clause d’une requête SQL. d’effectuer un calcul sur une colonne du résultat
 Les colonnes sur lesquelles le résultat est trié sont désignées par leur nom ou leur n°  SUM(Att) effectue la somme des valeurs de la colonne Att
d’apparition dans la clause SELECT
 ASC : tri croissant, DESC : tri décroissant  MIN(Att) restitue la plus petite valeur de la colonne Att
 Ex : afficher les pays dans l’ordre alphabétique  MAX(Att) restitue la plus grande valeur de la colonne Att
SELECT NomP
FROM Pays  AVG(Att) effectue la moyenne des valeurs de la colonne Att
ORDER BY NomP ASC ;  COUNT(*) compte le nombre de lignes du résultat.
 Ex : afficher pour chaque pays le ratio superficie/nombre d’habitants. Trier les COUNT(DISTINCT (Att) compte le nombre de valeurs différentes
dans la colonne Att
pays par ordre décroissant de ce ratio.
SELECT NomP AS Pays, Superficie/NbHab AS Occupation Remarque : l’utilisation de ces fonctions agrège le résultat de la
FROM Pays requête en une seule ligne qui affiche le résultat des fonctions.
ORDER BY 2 DESC, 1 ASC ;
Remarque : Le tri sur la colonne 1 indique que les pays ayant le même ration sont affichés
dans l’ordre alphabétique

7
 14/06/2017

Effectuer un calcul sur le résultat Effectuer des sous-totaux

Ex : afficher le nombre total de films ainsi que leur durée
GROUP BY : permet de partitionner le résultat de la clause WHERE
en sous ensembles sur lesquels on peut appliquer des fonctions agrégat
moyenne FILM
CodeFilm Durée Réalisateur Année  Cette clause se place derrière la clause WHERE
F1 2 Fellini 82  Les lignes du résultat sont triées sur les colonnes indiquées dans la clause GROUP
F2 2 Verneuil 83 BY. Un nouveau sous-ensemble est créé à chaque changement de valeur dans les
F3 1h30 Fellini 80 colonnes du tri
SELECT Count(*) AS NbFilms, AVG(Durée) AS DuréeMoyenne  Les fonctions agrégat peuvent être appliquées aux sous-ensembles de valeurs des
FROM Film ; colonnes
 Remarque : l’utilisation de cette clause agrège le résultat de la requête en une ligne
par paquet
NbFilms DuréeMoyenne
 Lorsque la requête comporte une clause GROUP BY, la clause SELECT spécifie
3 1h50 généralement les colonne de groupement plus des fonctions agrégat

Effectuer des sous-totaux Restrictions sur les sous-totaux

 Ex : afficher pour chaque acteur le nombre total de films dans lesquels il a joué
JOUE
CodeFilm NomActeur

HAVING : permet d’éliminer des sous-ensembles du résultat
lorsqu’il est partitionné par une clause GROUP BY
F2 Signoret
F2 Montant  Cette clause se place derrière la clause GROUP BY
F3 Mastroiani
F3 Montant  Permet d’effectuer des restriction sur les résultats fonctions agrégat appliquées
aux sous-ensembles de valeurs des colonnes
 Lorsqu’un prédicats est évalué à FAUX, le sous-ensemble est éliminé
SELECT NomActeur, Count(DISTINCT CodeFilm)
FROM Joue
GROUP BY NomActeur ;
=>
NomActeur COUNT(DISTINCT CodeFilm)

Mastroiani 1
Montant 2
Signoret 1

Restrictions sur les sous-totaux

 Ex : afficher le nom des acteurs qui ont joué dans au moins deux films
SELECT NomActeur, COUNT(DISTINCT CodeFilm) as nb
FROM Joue
GROUP BY NomActeur JOUE
HAVING COUNT(DISTINCT CodeFilm)>=2 ;
CodeFilm NomActeur
NomActeur nb
Montant Montant F2 Signoret
F2 Montant
F3 Mastroiani
F3 Montant

SELECT NomActeur, Count(DISTINCT CodeFilm)

FROM Joue
GROUP BY NomActeur ;

NomActeur COUNT(DISTINCT CodeFilm)

Mastroiani 1
Montant 2
Signoret 1