SQL (mysql)

Université de Caen-Normandie

Bruno Crémilleux
SQL : Structured Query Language

Créé en 1974, normalisé depuis 1986.

LDD : Langage de Définition des Données : CREATE, ALTER
Créer le schéma d’une base de données.
LMD : Langage de Manipulation des Données :
SELECT, INSERT, UPDATE, . . .
Interroger, insérer, modifier, supprimer des données.
LCD : Langage de Contrôle des Données : GRANT,. . .
Autorisation, sécurité, accès concurrents.
Langage “déclaratif” : l’utilisateur indique les données qui l’intéressent via une
assertion (description formelle de l’information recherchée, sans spécification
de chemin).

2/68
Calcul relationnel sur n-uplets
Univers de SQL : c’est l’ensemble des n-uplets des relations de la
BD.
Variable typée : une variable typée par une table prend ses valeurs
dans les lignes de cette table.
Exemple avec la BD “Commandes” (cf. TD et TP) :
CLIENT C crée la “variable client” C qui peut prendre pour valeur un
des n-uplets de la table CLIENT.
Exemple avec le 1er n-uplet de CLIENT :
[Link] = 1 [Link] = ’Goffin’ [Link] = ’Namur’ [Link] = ’B2’

Mêmes formules de sélection que pour l’algèbre relationnelle :

connecteurs logiques (∧, ∨, ¬), opérateurs de comparaison,
opérateurs arithmétiques.
3/68
mysql: premiers pas (1/2)

À partir d’un terminal :

mysql -h [Link] -u LOGIN -p

exemple :
Welcome to the MySQL monitor. Commands end with ; or \g.
[...]
MariaDB [(none)]>

valeurs des paramètres de connexion : répertoire

~/Protected/[Link] de votre home.

informations à [Link]

4/68
mysql: premiers pas (2/2)

La première fois, il faut créer sa base :

sous mysql :
MariaDB [(none)]> CREATE DATABASE LOGIN_bd ;

puis se connecter à sa base :

MariaDB [(none)]> use LOGIN_bd

Pour les connexions suivantes à la base LOGIN_bd :

à partir d’un terminal :
mysql -h [Link] -u LOGIN -p LOGIN_bd

5/68
mysql(1/3)

Deux types de commandes :

commandes de mysql:
\? ou help : aide
\q : quitter
\. FILE (ou source FILE) : exécute le script sql FILE
\! COMMAND : exécute la commande shell COMMAND
\T FILE : redirige la sortie dans le fichier FILE
...
commandes SQL : SELECT, CREATE, INSERT,. . .
Une requête SQL se termine par un ;

Bonne habitude de travail : préparer les requêtes via un éditeur de

texte et charger le script contenant les requêtes avec \. (ou source)
6/68
mysql(2/3)
Liste des tables : MariaDB [LOGIN_bd]> show tables ;
+-------------------------+
| Tables_in_cremilleux_bd |
+-------------------------+
| CLIENT |
| COMMANDE |
| DETAIL |
| PRODUIT |
+-------------------------+
Schéma d’une table : MariaDB [LOGIN_bd]> describe CLIENT ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| RefC | int(11) | NO | PRI | NULL | |
| NomC | varchar(20) | NO | | NULL | |
| Ville | varchar(20) | NO | | NULL | |
| CAT | varchar(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
7/68
mysql(3/3)

Contenu d’une table : MariaDB [LOGIN_bd]> SELECT * from CLIENT ;

+------+-----------+-----------+------+
| RefC | NomC | Ville | CAT |
+------+-----------+-----------+------+
| 1 | GOFFIN | Namur | B2 |
| 2 | HANSENNE | Poitiers | C1 |
| 3 | MONTI | Geneve | B2 |
...
15 rows in set (0,00 sec)

Nous reviendrons sur le SELECT

Commentaire :
une ligne : # ou ––
plusieurs lignes : /* ... */
8/68
Création d’une table
CREATE TABLE... :
description de chaque attribut :
nom de l’attribut (une chaîne de caractères)
type de l’attribut : entier, réel, chaîne, date,. . .
propriétés de l’attribut : clé, NOT NULL, contraintes,. . .

CREATE TABLE DETAIL(

RefCOM INT NOT NULL,
RefP VARCHAR(5) NOT NULL,
Quantite INT NOT NULL,
PRIMARY KEY (RefCOM, RefP)
) ;

PRIMARY KEY (RefC) : déclaration d’une clé (identifiant unique pour chaque
n-uplet)
La table est vide après sa création.
9/68
Insertion de n-uplets
Deux possibilités :
via un script SQL (commande INSERT) :
INSERT INTO CLIENT VALUES (1, ’GOFFIN’, ’Namur’, ’B2’) ;
INSERT INTO CLIENT VALUES (2, ’HANSENNE’, ’Poitiers’, ’C1’) ;
...

via le chargement d’un fichier texte (cf. TP) :

syntaxe mysql : (utiliser \copy en postgres)
LOAD DATA LOCAL INFILE "[Link]" INTO TABLE CLIENT ;
où [Link] contient :
1 GOFFIN Namur B2
2 HANSENNE Poitiers C1
...
Il est possible que vous deviez explicitement activer lors de votre connexion la
possibilité de chargement d’un fichier, cf. informations à
[Link]
10/68
Création d’une table
à partir d’une requête d’interrogation

CREATE TABLE NomTable AS

SELECT [...] ;

La table est remplie après sa création.

Utilisation : lorsque la base de données contient les informations sur
la nouvelle table (e.g., reconstruction, mise à jour).

Possibilité d’insertion à partir d’une sélection :

INSERT INTO NomTable SELECT [...] ;

11/68
Exemple de requête
(en calcul des n-uplets et SQL)

Noms des clients qui habitent Namur :

Calcul des n-uplets : {[Link] de CLIENT C où Ville = ’Namur’}
En SQL : SELECT [Link]
FROM CLIENT C
WHERE [Link] = ’Namur’ ;

Autres façons d’écrire en SQL :

SELECT [Link] SELECT NomC
FROM CLIENT FROM CLIENT
WHERE [Link] = ’Namur’ ; WHERE Ville = ’Namur’ ;

On confond le nom et le type de la SQL est tolérant (il se débrouille

variable (une seule variable CLIENT) avec le contexte si il n’y a pas
d’ambiguïté)
12/68
Exemple de requête
(en calcul des n-uplets et SQL)

Les types de produits :

Calcul des n-uplets : {[Link] de PRODUIT P}
En SQL : SELECT DISTINCT [Link]
FROM PRODUIT P ;
DISTINCT : pour éliminer les doublons (par défaut, SQL est
+----------+ paresseux)
| TypeP |
+----------+ +----------+
| Cheville | | TypeP |
| Cheville | +----------+
| Cheville | | Cheville |
| Clou | | Clou |
| Clou | | Planche |
| Planche | +----------+
| Planche |
+----------+ Avec DISTINCT
Sans DISTINCT
13/68
Exemple de requête
(en calcul des n-uplets et SQL)

Toutes les informations sur le client qui a effectué la commande de

référence numéro 4 :
Calcul des n-uplets :
{C.* de CLIENT C où ∃ COMMANDE COM ([Link] = [Link] ∧
[Link] = 4)}
SELECT C.*
En SQL : FROM CLIENT C, COMMANDE COM
WHERE [Link] = [Link]
AND [Link] = 4 ;
C.* : tous les attributs de C.
+------+----------+----------+------+
| RefC | NomC | Ville | CAT |
+------+----------+----------+------+
| 9 | PONCELET | Toulouse | B2 |
+------+----------+----------+------+ 14/68
Exemple de requête
(en calcul des n-uplets et SQL)

Pour chaque nom de client, les références des produits qu’il a

commandés :
Calcul des n-uplets :
{[Link], D. RefP de CLIENT C, DETAIL D où ∃ COMMANDE COM
([Link] = [Link] ∧ [Link] = [Link])}
SELECT DISTINCT [Link], [Link]
En SQL : FROM CLIENT C, COMMANDE COM, DETAIL D
WHERE [Link] = [Link]
AND [Link] = [Link] ;
DISTINCT car un client peut avoir commandé le même produit dans
2 commandes différentes.
En algèbre relationnelle : (C×COM×D):(([Link] = [Link]) ∧
([Link] = [Link]))[NomC, RefP]
Produit cartésien - sélection - projection 15/68
Forme générale d’une requête SQL

(3) SELECT [DISTINCT] A1, A2,..., Am (liste d’attributs

et d’expressions calculées)
(1) FROM R1, R2,..., Rn (liste de relations)
(2) WHERE F (expression de sélection)

Ordre d’exécution de la requête : (1) - (2) - (3)

En algèbre relationnelle :
((R1×R2×...×Rn):F)[A1,A2,...,Am]

Le SELECT correspond à une projection

16/68
Cas particulier : produit cartésien

SELECT *
FROM R1, R2,...,Rm

Les tables ne sont pas forcément distinctes.

Exemple : paires de noms de clients habitants la même ville.
C1 = Client ; C2 = Client ;

SELECT DISTINCT [Link], [Link]

FROM CLIENT C1, CLIENT C2
WHERE [Link] = [Link]
AND [Link] < [Link] ;

En algèbre relationnelle :
(C1 × C2):([Link] = [Link] et [Link] < [Link])
[[Link], [Link]]
17/68
Tri de l’affichage : ORDER BY
Références, types et prix des produits commandés en 2006. Le résultat sera
ordonné selon l’ordre croissant des types de produits puis l’ordre décroissant des
prix :
SELECT DISTINCT [Link], [Link], [Link]
FROM PRODUIT P, DETAIL D, COMMANDE COM
WHERE [Link] = [Link]
AND [Link] = [Link]
AND YEAR([Link]) = 2006
-- psql : EXTRACT(YEAR FROM [Link]) = 2006
ORDER BY [Link], [Link] DESC ;
+----------+--------+-------+
| TypeP | Prix | RefP |
+----------+--------+-------+
| Cheville | 220.00 | CH464 |
| Cheville | 120.00 | CH264 |
| Clou | 105.00 | CL45 |
| Clou | 95.00 | CL60 |
| Planche | 185.00 | PL224 |
+----------+--------+-------+
18/68
Tri de l’affichage :
forme générale d’exécution

(3) SELECT [DISTINCT] A1, A2,..., Am (liste d’attributs

et d’expressions calculées)
(1) FROM R1, R2,..., Rn (liste de relations)
(2) WHERE F (expression de sélection)
(4) ORDER BY Ai [ASC|DESC],..., Aj [ASC|DESC] ;

Ordre d’exécution de la requête : (1) - (2) - (3) - (4)

19/68
Between
Noms des clients qui habitent Toulouse et dont la référence est inférieure à 6
ou comprise entre 11 et 15 :
+--------+
SELECT [Link] | NomC |
FROM CLIENT C +--------+
| GILLET |
WHERE [Link] = ’Toulouse’ | AVRON |
AND (RefC <= 6 | NEUMAN |
OR RefC BETWEEN 11 AND 15) ; +--------+

Attention aux parenthèses :

+-----------+
SELECT [Link] | NomC |
+-----------+
FROM CLIENT C | GILLET |
WHERE [Link] = ’Toulouse’ | AVRON |
AND RefC <= 6 | VANBIST |
OR RefC BETWEEN 11 AND 15 ; | NEUMAN |
| FRANCK |
| VANDERKA |
Sans les parenthèses, tous les clients dont RefC | GUILLAUME |
est entre 11 et 15 sont dans le résultat. +-----------+
20/68
LIKE
Filtrer une chaîne selon un patron de motif donné.
Deux caractères jokers :
% : 0 ou plusieurs caractères
_ : un caractère quelconque
Exemple : Noms et villes des clients qui habitent dans une ville dont
le nom (de la ville) se termine par un e :
+----------+----------+
| NomC | Ville |
+----------+----------+
| MONTI | Geneve |
SELECT NomC, Ville | VANBIST | Lille |
FROM CLIENT | GILLET | Toulouse |
WHERE Ville LIKE ’%e’ | AVRON | Toulouse |
ORDER BY Ville ; | MERCIER | Toulouse |
| PONCELET | Toulouse |
| NEUMAN | Toulouse |
21/68
+----------+----------+
Noms et villes des clients qui habitent dans une ville dont le nom
contient un e :
+-----------+-----------+
| NomC | Ville |
+-----------+-----------+
| JACOB | Bruxelles |
| MONTI | Geneve |
| VANBIST | Lille |
SELECT NomC, Ville | HANSENNE | Poitiers |
FROM CLIENT | FERARD | Poitiers |
WHERE Ville LIKE ’%e%’ | TOUSSAINT | Poitiers |
ORDER BY Ville ; | GILLET | Toulouse |
| AVRON | Toulouse |
| MERCIER | Toulouse |
| PONCELET | Toulouse |
| NEUMAN | Toulouse |
+-----------+-----------+
Bruxelles et Poitiers sont aussi dans le résultat.

22/68
Noms et villes des clients qui habitent dans une ville dont le nom
contient au moins deux e :
+-------+-----------+
| NomC | Ville |
SELECT NomC, Ville
+-------+-----------+
FROM CLIENT
| JACOB | Bruxelles |
WHERE Ville LIKE ’%e%e%’
| MONTI | Geneve |
ORDER BY Ville ;
+-------+-----------+

Noms et villes des clients qui habitent dans une ville dont le deuxième
caractère du nom de ville est un a :
+-----------+-------+
| NomC | Ville |
+-----------+-------+
SELECT NomC, Ville
| GOFFIN | Namur |
FROM CLIENT
| FRANCK | Namur |
WHERE Ville LIKE ’_a%’
| VANDERKA | Namur |
ORDER BY Ville ;
| GUILLAUME | Paris |
+-----------+-------+
23/68
Expressions régulières (REGEXP 1)

Filtrer une chaîne selon un motif donné (la chaîne est dans le résultat dès
que le motif est présent, peu importe ce qui est devant ou derrière le motif).

Caractères jokers :
. un caractère
*, +, ? : répétition de ce qui précède
ˆ : ancrage début de chaîne
$ : ancrage fin de chaîne
[...] : ensemble
| : alternative
(...) : atome de plusieurs caractères
1
postgres: utilisez ˜ au lieu de REGEXP
24/68
Noms et villes des clients qui habitent dans une ville dont le nom
contient au moins deux e :
+-------+-----------+
| NomC | Ville |
SELECT NomC, Ville
+-------+-----------+
FROM CLIENT
| JACOB | Bruxelles |
WHERE Ville REGEXP ’e.*e’
| MONTI | Geneve |
ORDER BY Ville ;
+-------+-----------+
(et pas ’.*e.*e.*’ : notez la différence par rapport à LIKE)
Noms et villes des clients qui habitent dans une ville dont le nom se
termine par un e :
+----------+----------+
| NomC | Ville |
+----------+----------+
| MONTI | Geneve |
SELECT NomC, Ville | VANBIST | Lille |
FROM CLIENT | GILLET | Toulouse |
WHERE Ville REGEXP ’e$’ | AVRON | Toulouse |
ORDER BY Ville ; | MERCIER | Toulouse |
| PONCELET | Toulouse |
| NEUMAN | Toulouse |
+----------+----------+ 25/68
Pour chaque ville dont le nom contient la lettre ’e’ ou la lettre ’i’,
les références des clients qui y habitent :

SELECT Ville, RefC SELECT Ville, RefC

FROM CLIENT FROM CLIENT
WHERE Ville REGEXP ’[ei]’ WHERE Ville like ’%e%’
ORDER BY Ville ASC, RefC DESC ; OR Ville like ’%i%’
ORDER BY Ville ASC, RefC DESC ;
avec REGEXP avec LIKE

26/68
Calcul arithmétique
Détail des commandes en y incluant le type de chaque produit commandé, son prix unitaire
et son prix total :
SELECT [Link], [Link], [Link], [Link], [Link] AS "Prix unitaire",
[Link]*[Link] AS PrixTotal
FROM DETAIL D, PRODUIT P
WHERE [Link]=[Link]
ORDER BY [Link], PrixTotal DESC ;
+--------+-------+----------+----------+---------------+-----------+
| RefCom | RefP | TypeP | Quantite | Prix unitaire | PrixTotal |
+--------+-------+----------+----------+---------------+-----------+
| 1 | CH464 | Cheville | 25 | 220.00 | 5500.00 |
| 2 | CH262 | Cheville | 60 | 75.00 | 4500.00 |
| 2 | CL60 | Clou | 20 | 95.00 | 1900.00 |
| 3 | CL60 | Clou | 30 | 95.00 | 2850.00 |
| 4 | CH464 | Cheville | 120 | 220.00 | 26400.00 |
| 4 | CL45 | Clou | 20 | 105.00 | 2100.00 |
| 5 | PL224 | Planche | 600 | 185.00 | 111000.00 |
| 5 | CH464 | Cheville | 260 | 220.00 | 57200.00 |
| 5 | CL60 | Clou | 15 | 95.00 | 1425.00 |
| 6 | CL45 | Clou | 3 | 105.00 | 315.00 |
| 7 | CH264 | Cheville | 180 | 120.00 | 21600.00 |
| 7 | PL224 | Planche | 92 | 185.00 | 17020.00 |
| 7 | CL60 | Clou | 70 | 95.00 | 6650.00 |
| 7 | CL45 | Clou | 22 | 105.00 | 2310.00 |
+--------+-------+----------+----------+---------------+-----------+

calcul arithmétique : [Link]*[Link]

renommage d’une colonne : AS (pour affichage noms colonnes et pour le ORDER BY) 27/68
Fonctions agrégats

COUNT : comptage
SUM : somme
AVG : moyenne
MIN : minimum
MAX : maximum

Principe :
le calcul porte sur un ensemble de n-uplets (et non pas sur un seul
n-uplet). Un tel ensemble est une table ou un élément d’une partition
d’une table.

28/68
Fonctions agrégats

Nombre de produits, somme et moyenne des prix des produits, prix

minimum et maximum des produits :
SELECT COUNT(*), SUM(Prix), AVG(Prix), Min(Prix), Max(Prix)
FROM PRODUIT ;

+----------+-----------+------------+-----------+-----------+
| COUNT(*) | SUM(Prix) | AVG(Prix) | Min(Prix) | Max(Prix) |
+----------+-----------+------------+-----------+-----------+
| 7 | 1030.00 | 147.142857 | 75.00 | 230.00 |
+----------+-----------+------------+-----------+-----------+

COUNT(*) renvoie le nombre de lignes de la table PRODUIT

29/68
COUNT : exemples

SELECT COUNT(*), COUNT(Ville), COUNT(DISTINCT Ville)

FROM CLIENT ;

+----------+--------------+-----------------------+
| COUNT(*) | COUNT(Ville) | COUNT(DISTINCT Ville) |
+----------+--------------+-----------------------+
| 15 | 15 | 7 |
+----------+--------------+-----------------------+

COUNT(Ville) : nombre de champs Ville non nuls.

COUNT(DISTINCT Ville) : nombre de champs Ville non nuls et distincts.

30/68
Partitionnement
Nombre de fois où le produit CL60 a été commandé :
SELECT [Link], COUNT(*)
FROM DETAIL D
WHERE [Link] = ’CL60’ ;
+------+----------+
| RefP | COUNT(*) |
+------+----------+
| CL60 | 4 |
+------+----------+
Nombre de fois où le produit CL45 a été commandé :
SELECT [Link], COUNT(*)
FROM DETAIL D
WHERE [Link] = ’CL45’ ;
+------+----------+
| RefP | COUNT(*) |
+------+----------+
| CL45 | 3 |
+------+----------+
31/68
Partitionnement
Pour chaque produit, nombre de fois où il a été commandé :
une requête pour chaque produit ?
fastidieux. . .
et la liste des produits n’est pas connue, sauf en interrogeant la base.
å utiliser un partitionnement avec GROUP BY
SELECT [Link], COUNT(*)
FROM DETAIL D
GROUP BY [Link] ;
+-------+----------+
| RefP | COUNT(*) |
+-------+----------+
| CH262 | 1 |
| CH264 | 1 |
| CH464 | 3 |
| CL45 | 3 |
| CL60 | 4 |
| PL224 | 2 |
+-------+----------+ 32/68
Partitionnement

En ordonnant par ordre décroissant du nombre de ventes, puis ordre

croissant suivant RefP :
SELECT [Link], COUNT(*) AS NB
FROM DETAIL D
GROUP BY [Link]
ORDER BY NB DESC, [Link] ;
+-------+----+
| RefP | NB |
+-------+----+
| CL60 | 4 |
| CH464 | 3 |
| CL45 | 3 |
| PL224 | 2 |
| CH262 | 1 |
| CH264 | 1 |
+-------+----+

33/68
Partitionnement
Pour chaque produit de prix supérieur à 100, nombre de fois où il a
été commandé :
å le COUNT doit porter sur un ensemble de n-uplets qui est
l’ensemble des commandes d’un même produit : partitionnement
avec GROUP BY selon RefP
+-------+----------+
| RefP | COUNT(*) |
SELECT [Link], COUNT(*) +-------+----------+
FROM DETAIL D, PRODUIT P | CH264 | 1 |
WHERE [Link] = [Link] | CH464 | 3 |
AND [Link] > 100 | CL45 | 3 |
GROUP BY [Link] ; | PL224 | 2 |
+-------+----------+

Remarque :
la jointure élimine les produits non commandés (ici PL222).
34/68
Partitionnement
Pour chaque client qui a fait au moins une commande, le montant
total de ses commandes :
å partitionnement selon les clients.

SELECT [Link], SUM([Link]*[Link])

FROM DETAIL D, COMMANDE COM, PRODUIT P
WHERE [Link]=[Link]
AND [Link]=[Link]
GROUP BY [Link] ;
+------+------------------------+
| RefC | SUM([Link]*[Link]) |
+------+------------------------+
| 7 | 47580.00 |
| 9 | 35215.00 |
| 12 | 169625.00 |
| 14 | 8350.00 | -> 8350 = 25*220 + 30*95
+------+------------------------+
35/68
Partitionnement avec sélection
Pour chaque client qui a fait au moins une commande, le montant
total de ses commandes lorsque les commandes d’un client ont un
montant supérieur à 10000 :
å sélection sur les éléments de la partition : HAVING
SELECT [Link], SUM([Link]*[Link])
FROM DETAIL D, COMMANDE COM, PRODUIT P
WHERE [Link]=[Link]
AND [Link]=[Link]
GROUP BY [Link]
HAVING SUM([Link]*[Link]) > 10000 ;
+------+------------------------+
| RefC | SUM([Link]*[Link]) |
+------+------------------------+
| 7 | 47580.00 |
| 9 | 35215.00 |
| 12 | 169625.00 |
+------+------------------------+ 36/68
Partitionnement avec sélection
Pour chaque client qui a fait au moins une commande, le montant
total de ses commandes lorsque les commandes d’un client ont un
montant supérieur à 10000 et que le client a commandé strictement
moins de 5 produits :
SELECT [Link], SUM([Link]*[Link])
FROM DETAIL D, COMMANDE COM, PRODUIT P
WHERE [Link]=[Link]
AND [Link]=[Link]
GROUP BY [Link]
HAVING (SUM([Link]*[Link]) > 10000
AND COUNT([Link]) < 5)
+------+------------------------+
| RefC | SUM([Link]*[Link]) |
+------+------------------------+
| 7 | 47580.00 |
| 12 | 169625.00 |
+------+------------------------+ 37/68
Partitionnement avec sélection
Rappel : par défaut, SQL n’élimine pas les doublons. Si on introduit DISTINCT :
SELECT [Link], SUM([Link]*[Link])
FROM DETAIL D, COMMANDE COM, PRODUIT P
WHERE [Link]=[Link]
AND [Link]=[Link]
GROUP BY [Link]
HAVING (SUM([Link]*[Link]) > 10000
AND COUNT(DISTINCT [Link]) < 5)
+------+------------------------+
| RefC | SUM([Link]*[Link]) |
+------+------------------------+
| 7 | 47580.00 |
| 9 | 35215.00 |
| 12 | 169625.00 |
+------+------------------------+
Le client dont RefC est égal à 9 est alors dans le résultat : il a commandé
5 produits, mais 4 produits distincts. 38/68
Forme générale d’une requête SQL

(5) SELECT [DISTINCT] A1, A2,..., Am (liste d’attributs

et d’expressions calculées)
(1) FROM R1, R2,..., Rn (liste de relations)
(2) WHERE F (expression de sélection)
(3) GROUP BY (clé de partitionnement
(4) HAVING (selection sur un groupe)
(6) ORDER BY Ai [ASC|DESC],..., Aj [ASC|DESC] ;

Ordre d’exécution de la requête : (1) - (2) - (3) - (4) - (5) - (6)

On ne peut pas avoir de HAVING sans GROUP BY (puisque le HAVING

sélectionne des groupes).
39/68
Requêtes imbriquées

Pour exprimer des notions comme :

la non appartenance
les objets maximaux/minimaux
calcul avec des granularités différentes d’ensembles
(comparaison, calcul de pourcentage)
division de l’algèbre relationnelle
...

40/68
Requêtes imbriquées : appartenance

Références de produits qui n’ont jamais été commandés :

SELECT RefP
FROM PRODUIT
WHERE RefP NOT IN
(SELECT RefP
FROM DETAIL) ;

IN : signifie ∈
NOT IN : signifie 6∈
L’élément dont l’appartenance est testée (ici RefP) doit être du type
des éléments retournés par le SELECT imbriqué.

41/68
Requêtes imbriquées : appartenance
Références et prix des produits de prix supérieur à 100 et qui ont été
commandés au moins deux fois en 2005 :
SELECT RefP, Prix
FROM PRODUIT
WHERE Prix > 100
+-------+--------+
AND RefP IN | RefP | Prix |
(SELECT RefP +-------+--------+
FROM DETAIL D, COMMANDE COM | CH464 | 220.00 |
WHERE [Link] = [Link] +-------+--------+
AND YEAR(DateCom) = ’2005’
GROUP BY RefP
HAVING COUNT(*) >= 2) ;
L’élément dont l’appartenance est testée (ici RefP) doit être du type des
éléments retournés par le SELECT imbriqué.
La granularité du SELECT interne (groupe de produits) est différente de
celle du SELECT externe (un produit). 42/68
Requêtes imbriquées : appartenance

Références et prix des produits de prix supérieur à 100 et qui ont été
commandés en 2005 :
une requête imbriquée n’est pas nécessaire :
SELECT [Link], [Link] +-------+--------+
FROM PRODUIT P, DETAIL D, COMMANDE COM | RefP | Prix |
WHERE [Link] = [Link] +-------+--------+
| CH464 | 220.00 |
AND [Link] = [Link] | CH464 | 220.00 |
AND [Link] > 100 | CL45 | 105.00 |
AND YEAR([Link]) = ’2005’ ; +-------+--------+

43/68
Requêtes imbriquées : appartenance

La requête ’Références et prix des produits de prix supérieur à 100 et qui ont
été commandés en 2005” peut aussi être écrite avec une requête
imbriquée mais au prix d’une forte dégradation de la lisibilité et de la
déclarativité. L’écriture suivante est donc déconseillée :

SELECT RefP, Prix

FROM PRODUIT
WHERE Prix > 100
AND RefP IN
(SELECT RefP
FROM DETAIL D, COMMANDE COM
WHERE [Link] = [Link]
AND YEAR(DateCom) = ’2005’) ;

44/68
Requêtes imbriquées : appartenance
Pour chaque produit dont le prix est supérieur à 100, références des clients
n’ayant jamais acheté ce produit :

SELECT DISTINCT [Link], [Link]

FROM PRODUIT P, COMMANDE COM
WHERE [Link] >= 100
AND ([Link], [Link]) NOT IN
(SELECT [Link], [Link]
FROM DETAIL D, COMMANDE COM
WHERE [Link] = [Link]) ;

Remarques :
produit cartésien pour générer toutes les paires (produit, client)
le test d’appartenance s’effectue sur une liste d’attributs
en utilisant COMMANDE dans le premier SELECT, on considère uniquement les
clients qui ont commandé au moins une fois.
Pour considérer tous les clients : utiliser CLIENT
45/68
Requêtes imbriquées
Produit le plus cher (1/2)

SELECT MAX(Prix)
FROM PRODUIT ;

Référence et prix du produit le plus cher : si on écrit :

SELECT RefP, MAX(Prix)

FROM PRODUIT ;
Le résultat est :
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of
SELECT list contains nonaggregated column ’cremilleux_bd.[Link]’;
this is incompatible with sql_mode=only_full_group_by

RefP n’est pas unique sur la granularité sur laquelle l’agrégat est calculé (ici, la
table PRODUIT)
46/68
Requêtes imbriquées
Produit le plus cher (2/2)

Un SELECT imbriqué est nécessaire :

SELECT RefP, Prix +-------+--------+
FROM PRODUIT | RefP | Prix |
WHERE Prix = +-------+--------+
(SELECT MAX(Prix) | PL222 | 230.00 |
+-------+--------+
FROM PRODUIT) ;
Remarque : le maximum n’est pas forcément unique.

pour cet exemple, >= ALL peut aussi être utilisé.

ALL : comparaison ensembliste.
SELECT RefP, Prix
FROM PRODUIT
WHERE Prix >= ALL
(SELECT MAX(Prix)
FROM PRODUIT) ;
47/68
Requêtes imbriquées : comparaison

Références et prix des produits de type cheville qui sont moins chers
que le produit référencé PL224 :
SELECT RefP, Prix
FROM PRODUIT
WHERE TypeP = "Cheville"
and Prix < (SELECT Prix
FROM PRODUIT
WHERE RefP = "PL224") ;

Pour que la comparaison soit correcte, la requête imbriquée doit ici

retourner une valeur simple (i.e. une table à 1 ligne et 1 colonne).

48/68
Requêtes imbriquées : calcul de pourcentage
Nombre de produits achetés par client :
SELECT [Link], COUNT(DISTINCT [Link])
FROM DETAIL D, COMMANDE COM
WHERE [Link]=[Link]
GROUP BY [Link] ;

Pourcentage de produits achetés par client :

SELECT [Link], (COUNT(DISTINCT [Link])*100/(SELECT COUNT(*)

FROM PRODUIT)) AS Pourcentage
FROM DETAIL D, COMMANDE COM
WHERE [Link]=[Link]
GROUP BY [Link]
ORDER BY Pourcentage DESC ;

Remarquez le SELECT imbriqué (i.e. (SELECT COUNT(*) FROM PRODUIT) pour calculer
le nombre total de produits.
49/68
Requêtes imbriquées dans le FROM (1/2)

Les deux calculs (sur des granularités différentes) sont effectués par deux requêtes
imbriquées dans le FROM.
SELECT PRODUIT_CLIENT.RefC, (NbProduitsParClient*100)/NbProduitsTotal AS
Pourcentage
FROM (SELECT [Link], COUNT(DISTINCT [Link]) AS NbProduitsParClient
FROM DETAIL D, COMMANDE COM
WHERE [Link] = [Link]
GROUP BY [Link]) PRODUIT_CLIENT,
(SELECT COUNT(*) AS NbProduitsTotal
FROM PRODUIT) NB_PRODUIT
ORDER BY Pourcentage DESC ;

+------+-------------+
| RefC | Pourcentage |
+------+-------------+
| 7 | 57.1429 |
| 9 | 57.1429 |
| 12 | 42.8571 |
| 14 | 28.5714 |
+------+-------------+
50/68
Requêtes imbriquées dans le FROM (2/2)

Ce qui est faux : l’erreur provient du fait que SQL n’a pas de garantie que
NB_PRODUIT.NbProduitsTotal a une valeur unique pour chaque élément du
GROUP BY

SELECT [Link], COUNT(DISTINCT [Link])*100/NB_PRODUIT.NbProduitsTotal AS

Pourcentage
FROM (SELECT COUNT(*) AS NbProduitsTotal
FROM PRODUIT) NB_PRODUIT,
DETAIL D, COMMANDE COM
WHERE [Link]=[Link]
GROUP BY [Link]
ORDER BY Pourcentage DESC ;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause

and contains nonaggregated column ’NB_PRODUIT.NbProduitsTotal’ which is not
functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by

51/68
NOT EXISTS

Références des clients qui n’ont pas passé de commande :

Reformulation : référence de chaque client C tel qu’il n’existe pas de commande
COM faite par C

SELECT [Link]
FROM CLIENT C
WHERE NOT EXISTS
(SELECT *
FROM COMMANDE COM
WHERE [Link] = [Link]) ;

La requête imbriquée est ici corrélée avec la requête principale :

le SELECT imbriqué est exécuté pour chaque valeur de la variable CLIENT C
déclarée à l’extérieur.

52/68
NOT EXISTS

Cette requête (rappel : Références des clients qui n’ont pas passé de
commande ) peut aussi être écrite avec NOT IN
(et cette écriture est certainement plus naturelle)
SELECT NomC
FROM CLIENT
WHERE RefC NOT IN (SELECT RefC
FROM COMMANDE) ;

La sous-requête (non corrélée) est exécutée une seule fois.

53/68
NATURAL JOIN : pour la jointure naturelle
Quantité totale de chevilles commandées par des clients de Toulouse :
SELECT SUM(Quantite)
FROM CLIENT, COMMANDE, DETAIL, PRODUIT
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND TypeP = ’Cheville’
AND Ville = ’Toulouse’ ;

Avec NATURAL JOIN :

SELECT SUM(Quantite)
FROM CLIENT NATURAL JOIN COMMANDE NATURAL JOIN DETAIL NATURAL JOIN PRODUIT
WHERE TypeP = ’Cheville’
AND Ville = ’Toulouse’ ;

ATTENTION : NATURAL JOIN effectue la jointure sur tous les attributs de

mêmes noms entre les tables sans autre considération
(dans la pratique, une jointure doit s’effectuer sur des attributs qui ont une
signification qui “correspondent” pour que le résultat ait du sens).
54/68
NATURAL JOIN : pour la jointure naturelle
Ajout d’une table COMMUNE :
Namur Belgique 99
Poitiers France 86
CREATE TABLE COMMUNE ( Geneve Suisse 99
Commune VARCHAR(20) NOT NULL, Toulouse France 31
Pays VARCHAR(20) NOT NULL, Bruxelles Belgique 99
Paris France 75
Departement INT NOT NULL, Rome Italie 99
PRIMARY KEY (Commune) Londres Grande-Bretagne 99
) ; Lyon France 69
Anvers Belgique 99
Lille France 59

Noms et pays des clients : quel est le résultat de la requête ?

SELECT [Link], [Link]
FROM CLIENT NATURAL JOIN COMMUNE ;

55/68
NATURAL JOIN : pour la jointure naturelle
Ajout d’une table COMMUNE :
Namur Belgique 99
Poitiers France 86
CREATE TABLE COMMUNE ( Geneve Suisse 99
Commune VARCHAR(20) NOT NULL, Toulouse France 31
Pays VARCHAR(20) NOT NULL, Bruxelles Belgique 99
Paris France 75
Departement INT NOT NULL, Rome Italie 99
PRIMARY KEY (Commune) Londres Grande-Bretagne 99
) ; Lyon France 69
Anvers Belgique 99
Lille France 59

Noms et pays des clients : quel est le résultat de la requête ?

SELECT [Link], [Link]
FROM CLIENT NATURAL JOIN COMMUNE ;

Dans cet exemple, comme il n’y a pas d’attribut commun entre les deux tables,
cela revient à un produit cartésien (ici : 165 n-uplets).
Il n’y a pas d’erreur de syntaxe.
55/68
NATURAL JOIN : pour la jointure naturelle
Pour que cette requête soit correcte, il faut expliciter la jointure entre CLIENT et
COMMUNE :
SELECT [Link], [Link]
FROM CLIENT C, COMMUNE CO
WHERE [Link] = [Link] ;
+-----------+----------+
| NomC | Pays |
+-----------+----------+
| GOFFIN | Belgique |
| HANSENNE | France |
| MONTI | Suisse |
| GILLET | France |
| AVRON | France |
| FERARD | France |
| MERCIER | France |
| TOUSSAINT | France |
| PONCELET | France |
| JACOB | Belgique |
| VANBIST | France |
| NEUMAN | France |
| FRANCK | Belgique |
| VANDERKA | Belgique |
| GUILLAUME | France |
+-----------+----------+

56/68
NATURAL JOIN : pour la jointure naturelle

Un nom de commune n’est pas forcément unique : ajout d’un identifiant RefC,
pour une capitale, la valeur de RefC est une dizaine (cf. table COMMUNE2) :

12 Namur Belgique 99
22 Poitiers France 86
CREATE TABLE COMMUNE2( 50 Geneve Suisse 99
RefC INT NOT NULL, 21 Toulouse France 31
Commune VARCHAR(20) NOT NULL, 10 Bruxelles Belgique 99
Pays VARCHAR(20) NOT NULL, 20 Paris France 75
Departement INT NOT NULL, 40 Rome Italie 99
PRIMARY KEY (RefC) 30 Londres Grande-Bretagne 99
) ; 25 Lyon France 69
11 Anvers Belgique 99
23 Lille France 59

57/68
NATURAL JOIN : pour la jointure naturelle
Quel est le résultat de la requête ?
SELECT *
FROM CLIENT NATURAL JOIN COMMUNE2 ;

58/68
NATURAL JOIN : pour la jointure naturelle
Quel est le résultat de la requête ?
SELECT *
FROM CLIENT NATURAL JOIN COMMUNE2 ;
+------+---------+-----------+------+-----------+----------+-------------+
| RefC | NomC | Ville | CAT | Commune | Pays | Departement |
+------+---------+-----------+------+-----------+----------+-------------+
| 10 | JACOB | Bruxelles | C2 | Bruxelles | Belgique | 99 |
| 11 | VANBIST | Lille | B1 | Anvers | Belgique | 99 |
| 12 | NEUMAN | Toulouse | C2 | Namur | Belgique | 99 |
+------+---------+-----------+------+-----------+----------+-------------+

RefC est un attribut de même nom pour les 2 tables : NATURAL JOIN le
considère pour la jointure même si les sémantiques de ces deux attributs sont
distinctes. Le résultat pour JACOB est correct uniquement par hasard !

Remarque : “Noms et pays des clients” : même requête que précédemment :

SELECT [Link], [Link]
FROM CLIENT C, COMMUNE2 CO
WHERE [Link] = [Link] ;
58/68
La division entre ensembles
en algèbre relationnelle
Noms des personnes qui ont visité toutes les capitales (on suppose ici
qu’une personne voyageant dans un pays visite la capitale de ce pays).
Si on suppose que Rappel : Pays[capitale] : dublin, vienne, lima, skopje
Voyage contient :
Voyage ./ Pays
nompers | nompays nompers | nompays | capitale
---------+----------- ---------+-----------+----------
chloe | macedoine chloe | macedoine | skopje
chloe | irlande chloe | irlande | dublin
mehdi | irlande mehdi | irlande | dublin
mehdi | perou mehdi | perou | lima
helena | perou helena | perou | lima
chloe | autriche chloe | autriche | vienne
chloe | perou chloe | perou | lima
la requête (Voyage ./ Pays)[nompers, capitale] / Pays[capitale]
produit le résultat. 59/68
Division entre ensembles en SQL (1/2)

Références des clients qui ont acheté au moins un produit de chaque type de
produits (i.e., ont acheté tous les types de produits).
Écriture de la division selon les opérateurs fondamentaux de l’algèbre
relationnelle : (utilisation d’une double différence) :
Soient R(X1 , ..., Xr , Xr +1 , ..., Xm ) et S(Yr +1 , ..., Ym )
alors R/S = R[X1 , ..., Xr ] − ((R[X1 , ..., Xr ] × S) − R)[X1 , ..., Xr ]
Sur notre exemple :
COM[RefC]-(((COM×P)[[Link],[Link]]) -
((COM ./ D ./ P)[[Link],[Link]]))[RefC]
SELECT [Link]
FROM COMMANDE COM
WHERE [Link] NOT IN
(SELECT [Link]
FROM COMMANDE COM, PRODUIT P
WHERE ([Link], [Link]) NOT IN
(SELECT [Link], [Link]
FROM COMMANDE COM, DETAIL D, PRODUIT P
WHERE [Link] = [Link]
AND [Link] = [Link])) ; 60/68
Division entre ensembles en SQL (2/2)

Avec comptage : clients dont le nombre de types de produits

commandés est égal au nombre de types de produits dans la base.
SELECT [Link]
FROM COMMANDE COM, DETAIL D, PRODUIT P
WHERE [Link] = [Link]
AND [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) =
(SELECT COUNT(DISTINCT [Link])
FROM PRODUIT P) ;

DISTINCT est indispensable pour calculer le nombre correct de types

de produits.

61/68
Pour obtenir les noms des clients (au lieu de leurs références) :
(écriture avec une requête imbriquée dans un FROM)
SELECT [Link]
FROM (SELECT [Link]
FROM COMMANDE COM
WHERE [Link] NOT IN
(SELECT [Link]
FROM COMMANDE COM, PRODUIT P
WHERE ([Link], [Link]) NOT IN
(SELECT [Link], [Link]
FROM COMMANDE COM, DETAIL D, PRODUIT P
WHERE [Link] = [Link]
AND [Link] = [Link]))) CLIENT_TOUS_TYPEP,
CLIENT C
WHERE CLIENT_TOUS_TYPEP.RefC = [Link] ;
+---------+
| NomC |
+---------+
| NEUMAN |
| MERCIER |
+---------+
62/68
En utilisant CLIENT dans le premier SELECT, il n’est plus nécessaire d’imbriquer
une requête :

SELECT [Link]
FROM CLIENT C, COMMANDE COM
WHERE [Link] = [Link]
AND [Link] NOT IN
(SELECT [Link]
FROM COMMANDE COM, PRODUIT P
WHERE ([Link], [Link]) NOT IN
(SELECT [Link], [Link]
FROM COMMANDE COM, DETAIL D, PRODUIT P
WHERE [Link] = [Link]
AND [Link] = [Link])) ;
+---------+
| NomC |
+---------+
| NEUMAN |
| MERCIER |
+---------+

63/68
Question : dans la requête précédente, pourquoi la jointure avec
COMMANDE du premier SELECT est nécessaire ?
Autrement dit, pourquoi la requête ci-dessous ne produit pas le
résultat demandé ?

SELECT [Link]
FROM CLIENT C
WHERE [Link] NOT IN
(SELECT [Link]
FROM COMMANDE COM, PRODUIT P, CLIENT C
WHERE [Link] = [Link]
AND ([Link], [Link]) NOT IN
(SELECT [Link], [Link]
FROM COMMANDE COM, DETAIL D, PRODUIT P
WHERE [Link] = [Link]
AND [Link] = [Link])) ;

64/68
Autres

contraintes : clé étrangère : dans la table DETAIL

FOREIGN KEY (RefP) REFERENCES PRODUIT
impose que toute valeur de RefP dans une ligne de la table
DETAIL soit présente comme identifiant primaire (PRIMARY
KEY) de la table PRODUIT
DROP TABLE IF EXISTS CLIENT, PRODUIT, DETAIL,
COMMANDE ;
INSERT
DELETE FROM <NomTable> WHERE <expression de
sélection>
UPDATE <Relation> SET <Liste affectations
attributs> WHERE <expression de sélection>
65/68
Remerciements

Ces diapositives, et notamment les exemples, doivent beaucoup

à Etienne Grandjean et Jean-Jacques Hébrard,
enseignants-chercheurs au département mathématiques et
informatique de l’Université de Caen Normandie.

66/68