Langage de manipulation des données

Cours
“Bases de données” 1. La partie LMD de SQL
1.1. Les requêtes de base
1.2. Insertions, suppressions, mises à jour
3° année (MISI)
1.3. Autres fonctionnalités
Antoine Cornuéjols
www.lri.fr/~antoine
[email protected]

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

SELECT-FROM-WHERE SELECT-FROM-WHERE

SELECT <liste d’attributs> SELECT <liste d’attributs>

FROM <liste de tables> FROM <liste de tables>

WHERE <condition> ; WHERE <condition> ;

SELECT Date_Naiss, Adresse

<liste d’attributs> : liste des noms des attributs dont la requête doit FROM EMPLOYÉ
extraire la valeur
WHERE PRENOM=’Bernard’ AND NOM=’Schmidt’ ;
<liste de tables> : liste des noms des relations nécessaires pour
traiter la requête
Requête SELECT = opération de SELECTION-PROJECTION
<condition> : expression conditionnelle (booléenne)
identifiant les tuples à récupérer par la requête SELECT spécifie les attributs de la projection

WHERE spécifie la condition de sélection

3 4
Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Chercher la date de naissance et l’adresse des employés dont le nom est ‘John B. Smith”
SELECT-FROM-WHERE (syntaxe générale)
SELECT Date_Naiss, Adresse

SELECT nom_table, nom_colonnes* FROM EMPLOYÉ

FROM nom_table* WHERE PRENOM=’Bernard’ AND NOM=’Schmidt’ ;

[WHERE conditions_de_sélection_sur_lignes*]
[GROUP BY nom_colonne_de_regroupement*] ATTENTION : La clause SELECT peut ramener des tuples dupliqués.
[HAVING conditions_de_sélection_sur_groupe*]
[ORDER BY nom_colonne_tri*] SELECT DISTINCT auteur

FROM Livre;

* = plusieurs occurrences possibles ; [] = optionnel

5 6

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Chercher dans la table LIVRE le titre et le genre des ouvrage écrits par DUMAS : Chercher les auteurs dont le nom contient un A en deuxième position :

SELECT titre, genre FROM Livre WHERE auteur = ‘Dumas’; SELECT * FROM Evrivain WHERE auteur LIKE ‘_A%’;

Chercher les ouvrages non écrits par Dumas et postérieurs à 1835 : Chercher les ouvrages (auteur, titre, année) publiés en 1839, 1866 ou 1857 :

SELECT * FROM Livre WHERE auteur <> ‘Dumas’ AND année > 1835; SELECT auteur, titre, année FROM Livre WHERE année IN
(1839,1866,1857);
Chercher le nom (et lieu de naissance) des auteurs nés entre 1802 et 1850 :

SELECT auteur, lieu FROM Ecrivain WHERE né_en BETWEEN 1802 AND 1850; Chercher les ouvrages pour lesquels on dispose de l’année de parution

SELECT auteur, titre, année FROM Livre WHERE année NOT NULL;
Chercher les auteurs dont le nom commence par B :

SELECT * FROM Evrivain WHERE auteur LIKE ‘B%’;

7 8
Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Expression des restrictions Tri du résultat À l’aide de la clause ORDER BY suivie de la liste des
attributs servant de critère au tri.
Restriction = combinaison booléenne (or, and, not) de conditions élémentaires
portant sur les colonnes d’une table.

Prédicats de restriction : permettent la comparaison d’une valeur portée par Chercher les stations par ordre croissant de tarifs et par ordre alphabétique :
une colonne à une valeur constante. SELECT * FROM Station ORDER BY tarif, nomStation;

à l’aide des opérateurs =, <>, <, >, <=, >=

à l’aide du prédicat d’intervalle BETWEEN qui teste si la valeur de l’attribut est compris
entre deux valeurs constantes. L’attribut doit porter une valeur numérique ou de type Pour trier en ordre descendant, on ajoute le mot-clé DESC après la liste des
date
attributs concernés.
à l’aide du prédicat de comparaison de texte LIKE qui teste si un attribut de type chaîne
contient une ou plusieurs sous-chaînes. Le caractère souligné remplace un caractère
quelconque. Le caractère % remplace une séquence de caractères
SELECT * FROM Station ORDER BY tarif DESC, nomStation ASC;
à l’aide du prédicat de test de nullité (attribut non renseigné) NULL
à l’aide du prédicat d’appartenance IN qui teste si la valeur de l’attribut appartient à une
liste de valeurs
9 10

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)
Exemple :
Expression des jointures
CREATE TABLE Livre CREATE TABLE Ecrivain
(auteur CHAR (8), (auteur CHAR (8),
Une jointure sans qualification (sans clause WHERE) est le produit cartésien.
titre CHAR (24), né_en SMALLINT,
Dans une jointure avec qualification, le produit cartésien est restreint par une année SMALLINT, lieu CHAR (20),
combinaison de prédicats de comparaison. genre CHAR (8), rayon SMALLINT,
prix DECIMAL (5,2), PRIMARY KEY (auteur));
Il est aussi possible de privilégier une table.
PRIMARY KEY (auteur, titre));

CREATE TABLE Rayon

(rayon SMALLINT,
salle SMALLINT,
PRIMARY KEY (rayon));

11 12
 Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)
Exemple (suite) :
SELECT A.auteur, B.lieu FROM Livre A, Ecrivain B;

CREATE TABLE Livre CREATE TABLE Ecrivain

forme le produit cartésien des tables Livre et Ecrivain sur les attributs auteur de Livre
(auteur CHAR (8), (auteur CHAR (8),
et lieu de Ecrivain. On peut associer un nom abrégé (alias) aux noms de table pour
alléger l’écriture des requêtes. titre CHAR (24), né_en SMALLINT,
année SMALLINT, lieu CHAR (20),
SELECT A.auteur, A.titre, B.rayon FROM Livre A, Ecrivain B WHERE
genre CHAR (8), rayon SMALLINT,
A.auteur = B.auteur;
prix DECIMAL (5,2), PRIMARY KEY (auteur));
Equi-jointure : liste le titre, l’auteur et le rayon de rangement des ouvrages de la bibliothèque.
PRIMARY KEY (auteur, titre));
CREATE TABLE Ouvrage

CREATE TABLE Rayon (auteur CHAR (8),

(rayon SMALLINT, titre CHAR (24),

salle SMALLINT, né_en SMALLINT,

PRIMARY KEY (rayon)); salle SMALLINT,

rayon SMALLINT);

13 14

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

SELECT X.auteur, X.titre, Y.né_en, Z.salle, Z.rayon FROM Livre X, SELECT A.rayon B.auteur FROM Rayon A, Ecrivain B WHERE A.rayon =
Ecrivain Y, Rayon Z WHERE X.auteur = Y.auteur AND Y.rayon = Z.rayon; B.rayon (+);

reconstitue la table OUVRAGE à partir des tables LIVRE, ECRIVAIN et RAYON. réalise une jointure dans laquelle la table RAYON est privilégiée. Cette jointure affiche la
liste de tous les rayons de la bibliothèque et les noms d’auteurs qui y sont rangés si les rayons
ne sont pas vides.
SELECT A.auteur, A.titre, A.année, A.genre, A.prix, B.né_en, B.lieu,
B.rayon FROM Livre A, Ecrivain B WHERE A.auteur = B.auteur;

réalise une jointure naturelle (les attributs de jointure ne sont pas inclus dans la relation
résultante).

SELECT A.auteur, A.titre, A.né_en, B.auteur FROM Ecrivain A,

Ecrivain B WHERE A.né_en = B.né_en AND A.auteur > B.auteur;

jointure d’une table sur elle-même : liste le nom, la date de naissance des écrivains nés la
même année.
15 16
Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

SELECT auteur, lieu FROM Ecrivain

Utilisation de sous-questions WHERE auteur IN (SELECT auteur FROM Livre);

liste le nom et le lieu de naissance des auteurs de la table AUTEUR figurant dans la table
On peut imbriquer des sous-questions au niveau de la clause WHERE. LIVRE.
Cette requête est équivalente à la requête suivante utilisant une jointure :
Le résultat d’une sous-question peut être soit une valeur simple, soit un
ensemble de valeurs. SELECT auteur, lieu FROM Ecrivain, Livre
WHERE Auteur.auteur = Livre.auteur;
Le résultat d’une sous-question est exploité pour :

tester l’appartenance d’une valeur à une liste de valeurs élaborée dans la Il est possible d’utiliser des variables définies dans un bloc interne au niveau d’un bloc
externe. On parle alors de variable de corrélation.
sous-question
La requête suivante liste le nom, le lieu de naissance et le genre des livres écrits par des
vérifier un prédicat de comparaison exprimé à l’aide des quantificateurs de auteurs de la table AUTEUR figurant dans la table LIVRE.
la logique du premier ordre et d’opérateurs de comparaison (=, >, <, >=,
<=). SELECT auteur, lieu, A.genre FROM Ecrivain WHERE anteur IN (SELECT
auteur FROM Livre A);
17 18

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Questions quantifiées

On peut comparer le (ou les) attribut(s) se trouvant dans la clause WHERE avec SELECT titre FROM Livre
l’ensemble de valeurs résultat d’une sous-question à l’aide de quantificateurs. WHERE année > ALL (SELECT né_en FROM Ecrivain);

Le prédicat de comparaison est vrai : liste le titre des ouvrages de la table LIVRE écrits après la naissance de tous les auteurs de la
table ECRIVAIN.
s’il est vérifié pour tous les éléments de l’ensemble avec le quantificateur
ALL,
SELECT auteur FROM Ecrivain
s’il est vérifié pour au moins un élément de l’ensemble avec le WHERE Auteur = SOME (SELECT auteur FROM Livre);
quantificateur SOME (ou ANY, synonyme).
liste les auteurs de la table ECRIVAIN ayant un livre dans la table LIVRE.

Remarque : SOME est équivalent à IN.

19 20
Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Expression des unions

On peut tester si le résultat d’une sous-question est vide ou non. Pour cela, on
utilise le quantificateur EXISTS. SELECT année, ‘parution ‘, auteur FROM Livre WHERE année >=1825 AND
EXISTS <sous-question> s’évalue à vrai si le résultat de la sous-question année <= 1850
UNION
est non vide.
SELECT né_en, ‘naissance’, auteur FROM Ecrivain WHERE né_en >= 1825
AND né_en <= 1850 ORDER BY 1;

SELECT auteur, titre FROM Livre X

liste par ordre chronologique les événements intervenus entre 1825 et 1850 figurant dans la
WHERE EXISTS (SELECT * FROM Ecrivain WHERE né_en = X.année);
table LIVRE (parution) et dans la table ECRIVAIN.

liste le nom des auteurs et le titre des ouvrages de la table LIVRE écrits la même année que
l’année de naissance d’un des auteurs de la table ECRIVAIN. Remarque : les tables résultantes des selects doivent avoir le même nombre de
colonnes et deux colonnes de même rang doivent être de même nature
(chaînes de caractères, données numériques ou temporelles).

21 22

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Agrégations et partitionnements Fonctions d’agrégation

Toutes les requêtes vues jusqu'à présent pouvaient être considérées comme une Ces fonctions s'appliquent à une colonne, en général de type numérique. Ce
suite d'opérations effectuées tuple à tuple. sont :

Les fonctionnalités d'agrégation de SQL permettent d'exprimer des conditions COUNT qui compte le nombre de valeurs non nulles.
sur des groupes de tuples, et de constituer les résultat par agrégation de valeurs MAX et MIN.
au sein de chaque groupe.
AVG, STDDEV, VARIANCE qui calculent la moyenne, la déviation
La syntaxe SQL fournit donc : standard et la variance des valeurs de la colonne.
Le moyen de partitionner une relation en groupes selon certains critères. SUM qui effectue le cumul.
Le moyen d'exprimer des conditions sur ces groupes.
Des fonctions d'agrégation. SELECT COUNT nomStation AVG(tarif), MIN(tarif), MAX(tarif)
Il existe un groupe par défaut : le relation toute entière. Sans même définir de FROM Station;

groupe donc, on peut utiliser les fonctions d'agrégation. compte le nombre de stations, les tarifs moyen, minimum et maximum de la table Station.

23 24
Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Partitionnement
Il est possible d’effectuer des restrictions sur le résultat du partitionnement i.e.
Le partitionnement s’exprime avec l’option GROUP BY
écarter des sous-ensembles. Pour cela, on utilise l’option HAVING.
SELECT auteur, genre, COUNT(titre)
FROM Livre GROUP BY auteur, genre;

SELECT auteur, genre FROM Livre

liste en les regroupant par auteur et genre, les attributs auteur et genre de la table Livre. GROUP BY auteur, genre HAVING genre <> ‘Poésie’;
Le résultat pourrait être (autant de groupes que de couples (auteur, genre)) :
liste, en les regroupant par auteur et genre, les attributs auteur et genre en éliminant les
No Partition auteur genre count groupes dont le genre est “poésie”.
1 Balzac Roman 1 Dans l’exemple ci-dessus, élimination du sous-ensemble 4.
2 Dumas Roman 1
3 Dumas Théâtre 1
4 Hugo Poésie 2
5 Hugo Roman 1
25 26

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Mise à jour de tuples

SELECT auteur, ‘genre :’, genre FROM Livre
Sélection avec intention de mise à jour
GROUP BY année <> 1834 HAVING genre <> ‘Théâtre’
ORDER BY genre ASC, auteur DESC; SELECT salle, rayon
FROM Ouvrage
WHERE auteur IN (‘Hugo’, ‘Malouf’)
liste l’auteur, le libellé “genre” et le genre des ouvrages non publiés en 1834, en les FOR UPDATE;
regroupant par genre et par auteur, en éliminant les pièces de théâtre et en triant le résultat par
genre croissant et par auteur décroissant. ...

UPDATE Ouvrage
SET salle = 2, rayon = 3
Remarque : l’option WHERE permet de sélectionner les tuples avant la formation
WHERE auteur IN
des partitions. L’option HAVING est évaluée ensuite. Elle permet d’effectuer une
(SELECT auteur FROM Ecrivain WHERE lieu = ‘Paris’);
restriction sur les partitions.
met à jour la table OUVRAGE en affectant les valeurs 2 pour la salle et 3 pour le rayon pour les
auteurs nés à Paris.
27 28
Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Suppression de tuples L’opérateur de division en SQL

Suppression avec sous-question : Sur l’exemple de la base aérienne, quels sont les pilotes qui pilotent tous les Airbus ?

Avion(#av_No, AvNom, ...)

DELETE FROM Ouvrage
WHERE auteur IN Pilote(#PLNo, ...)

(SELECT auteur FROM Ecrivain WHERE lieu = ‘Paris’); Vol(#VolNo, AvNo, PlNo, ...)

SELECT DISTINCT PLNo FROM Vol

supprime de la table OUVRAGE les lignes correspondant à des auteurs pour les auteurs nés à
WHERE av_No IN
Paris.
(SELECT AvNo FROM Avion WHERE AvNom = ‘Airbus’)
GROUP BY PlNo
HAVING COUNT (DISTINCT av_No) =
(SELECT COUNT (av_No) FROM Avion WHERE AvNom = ‘Airbus’);

29 30

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

L’opérateur d’intersection en SQL Les vues en SQL

On crée d’abord deux tables Livre1 et Livre2 : Une vue est une table virtuelle qui résulte d’une requête. Ce résultat est ré-évalué
INSERT INTO Livre1 (SELECT * FROM Livre WHERE année < 1850); à chaque fois que l’on accède à la vue. Une vue est donc essentiellement une
INSERT INTO Livre2 (SELECT * FROM Livre WHERE année >= 1850); requête à laquelle on a donné un nom.

SELECT A.auteur FROM Livre1 A CREATE VIEW <nom_vue>

WHERE EXISTS AS <requête>
(SELECT B.auteur FROM Livre2 B WHERE A.auteur = B.auteur); [WITH CHEXK OPTION];
liste les auteurs ayant écrit un livre avant 1850 et un livre après 1850.

SELECT A.auteur FROM Livre1 A CREATE VIEW ParisCinemas

WHERE NOT EXISTS AS SELECT * FROM Cinema WHERE ville = ‘Paris’;
(SELECT B.auteur FROM Livre2 B WHERE A.auteur = B.auteur);
crée une vue qui ne “contient” que les cinémas parisiens.
liste les auteurs ayant écrit un livre avant 1850 et pas après 1850.
31 32
 Le modèle relationnel Le modèle relationnel
Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Les vues en SQL On peut créer une vue qui regroupe des informations par jointures.
Exemple : on crée une vue Casting donnant explicitement les titres des films, leur année et
les noms et prénoms des acteurs.

CREATE VIEW Casting (film, année, acteur, prenom) AS

CREATE VIEW SimpleParisCinemas
SELECT Titre, Annee, Nom, Prenom
AS SELECT nom, COUNT(*) AS nbSalles
FROM Film F, Role R, Artiste A
FROM Cinema C, salle S
WHERE F.ideFilm = R.idFilm
WHERE ville = ‘Paris’ AND R.idActeur = A.idArtiste;
AND C.nom = S.nomCinema
GROUP BY C.nom; Remarque : on a donné explicitement des noms d’attributs au lieu d’utiliser les attributs de la
clause SELECT.

restreint la vision des cinémas parisiens à leur nom et à leur nombre de salles SELECT acteur, prenom FROM Casting WHERE année = 1997;

GRANT SELECT ON Casting TO PUBLIC;

On peut donner des droits en lecture pour tous sur cette vue.
33 34

Le modèle relationnel Le modèle relationnel

Langage de Manipulation de Données SQL3 (99) Langage de Manipulation de Données SQL3 (99)

Les triggers Les triggers

Un trigger (non évoqué dans SQL2, mais discuté dans SQL3) est une procédure
CREATE TRIGGER CumulCapacite
déclenchée par des événements de mise à jour spécifiés par l’utilisateur et ne AFTER UPDATE ON Salle
s’exécute que quand une condition est satisfaite. FOR EACH ROW
Les triggers peuvent être considérés comme une extension du système de WHEN (new.capacite != old.capacite)
contraintes proposée par la clause CHECK. mais à la différence de cette BEGIN
dernière, l’événement déclencheur est explicitement indiqué et l’action n’est pas UPDATE Cinema
limitée à la simple alternative acceptation/rejet. SET capacite = capacite - :old.capacite + :new.capacite
WHERE nom = :new.nomCinema;
END;

Exemple de trigger qui maintient la capacité d’un cinéma à chaque mise-à-jour sur la table
Salle.

35 36