Requete Imbriquee
Requête Imbriquée
Pour la suite du cours, nous allons nous entrainer sur des requêtes imbriquées.
Afin d'avoir un cas concret et un contexte favorable à ce type de requête, nous allons modéliser la base de données d'une bibliothèque.
Comme nous sommes encore au début de notre entrainement, nous prendrons une version minimale (on ne va pas se rajouter trop de travail :p) :
Quels sont les sujets d'une bibliothèque ?
- Des
livres
- Des
abonnés (ou adhérent, ou client).
Et nous aurons également une table qui permet de savoir quel livre à été emprunté par quel abonné et à quelle date (en gros: qui a emprunté quoi et quand ?).
Nous appelerons cette table :
emprunt.
Ceci nous permettra également de réaliser des requêtes sur plusieurs tables en même temps.
Création de la Base de données :
bibliotheque
Pour ce faire, vous pouvez utiliser la console mysql ou phpmyadmin :
Exercice : Création de tables
L'objectif du prochain exercice sera de créer les tables suivantes :
 | Table : Abonne |
---|
Field | Type | Null | Key | Default | Extra |
---|
id_abonne | int(3) | NO | PRI | | auto_increment |
prenom | varchar(20) | NO | | | |
Explications : id_abonne représente le champ clé primaire et auto increment (PK - AI), il s'agira d'une liste de chiffres uniques permettant de différencier chaque enregistrement.
Nous garderons en mémoire uniquement le prenom de chaque abonné mais nous pourrions enregistrer d'autres informations comme son nom, adresse, etc.
 | Table : Livre |
---|
Field | Type | Null | Key | Default | Extra |
---|
id_livre | int(3) | NO | PRI | | auto_increment |
auteur | varchar(30) | NO | | | |
titre | varchar(30) | NO | | | |
Explications : id_livre représente le champ clé primaire et auto increment (PK - AI), il s'agira d'une liste de chiffres uniques permettant de différencier chaque enregistrement.
Chaque livre possède un titre et a été écrit par un auteur (nous aurions pu également enregistrer d'autres informations).
 | Table : Emprunt |
---|
Field | Type | Null | Key | Default | Extra |
---|
id_emprunt | int(3) | NO | PRI | | auto_increment |
id_livre | int(3) | YES | | | |
id_abonne | int(3) | YES | | | |
date_sortie | date | NO | | | |
date_rendu | date | YES | | | |
Explications : id_emprunt représente le champ clé primaire et auto increment (PK - AI), il s'agira d'une liste de chiffres uniques permettant de différencier chaque enregistrement.
Dans la table emprunt, id_livre sera un champ clé étrangère en référence au champ id_livre de la table livre.
De la même manière, le champ id_abonne de la table emprunt sera une clé étrangère au champ id_abonne de la table abonne.
Pour effectuer la création, libre à vous d'utiliser PhpMyAdmin ou la Console Mysql.
Correction : Création de tables
Voici le code SQL permettant de créer vos 3 tables :
Création des tables abonne, livre et emprunt sur la base de données bibliotheque
CREATE DATABASE IF NOT EXISTS bibliotheque ;
USE bibliotheque ;
CREATE TABLE abonne (
id_abonne INT(3) NOT NULL AUTO_INCREMENT,
prenom VARCHAR(20) NOT NULL,
PRIMARY KEY (id_abonne)
) ENGINE=InnoDB ;
CREATE TABLE emprunt (
id_emprunt INT(3) NOT NULL AUTO_INCREMENT,
id_livre INT(3) DEFAULT NULL,
id_abonne INT(3) DEFAULT NULL,
date_sortie DATE NOT NULL,
date_rendu DATE DEFAULT NULL,
PRIMARY KEY (id_emprunt)
) ENGINE=InnoDB ;
CREATE TABLE livre (
id_livre INT(3) NOT NULL AUTO_INCREMENT,
auteur VARCHAR(30) NOT NULL,
titre VARCHAR(30) NOT NULL,
PRIMARY KEY (id_livre)
) ENGINE=InnoDB ;
Nous pourrions ajouter les clés étrangères comme ceci :
Création des tables abonne, livre et emprunt sur la base de données bibliotheque
ALTER TABLE emprunt ADD FOREIGN KEY ( id_livre ) REFERENCES bibliotheque.livre (id_livre);
ALTER TABLE emprunt ADD FOREIGN KEY ( id_abonne ) REFERENCES bibliotheque.abonne (id_abonne);
Exercice : Insertion du contenu
Maintenant que nous avons nos 3 tables, il faut penser à insérer du contenu à l'intérieur.
Voici les données attendues :
abonne |
---|
id_abonne | prenom |
---|
1 | Guillaume |
2 | Benoit |
3 | Chloe |
4 | Laura |
livre |
---|
id_livre | auteur | titre |
---|
100 | GUY DE MAUPASSANT | Une vie |
101 | GUY DE MAUPASSANT | Bel-Ami |
102 | HONORE DE BALZAC | Le père Goriot |
103 | ALPHONSE DAUDET | Le Petit chose |
104 | ALEXANDRE DUMAS | La Reine Margot |
105 | ALEXANDRE DUMAS | Les Trois Mousquetaires |
id_emprunt | id_livre | id_abonne | date_sortie | date_rendu |
---|
1 | 100 | 1 | 2014-12-17 | 2014-12-18 |
2 | 101 | 2 | 2014-12-18 | 2014-12-20 |
3 | 100 | 3 | 2014-12-19 | 2014-12-22 |
4 | 103 | 4 | 2014-12-19 | 2014-12-22 |
5 | 104 | 1 | 2014-12-19 | 2014-12-28 |
6 | 105 | 2 | 2015-03-20 | 2015-03-26 |
7 | 105 | 3 | 2015-06-13 | NULL |
8 | 100 | 2 | 2015-06-15 | NULL |
* Seule particularité : le champ date_rendu de la table emprunt comportera la valeur
NULL si le livre se trouve hors de la bibliothèque.
Pour effectuer les insertions, libre à vous d'utiliser PhpMyAdmin ou la Console Mysql.
Correction : Insertion de contenu
Voici le code SQL permettant de créer vos enregistrements :
Insertion d'enregistrement sur les tables abonne, livre et emprunt, pour la base de données bibliotheque
INSERT INTO abonne (id_abonne, prenom) VALUES
(1, 'Guillaume'),
(2, 'Benoit'),
(3, 'Chloe'),
(4, 'Laura');
INSERT INTO livre (id_livre, auteur, titre) VALUES
(100, 'GUY DE MAUPASSANT', 'Une vie'),
(101, 'GUY DE MAUPASSANT', 'Bel-Ami '),
(102, 'HONORE DE BALZAC', 'Le père Goriot'),
(103, 'ALPHONSE DAUDET', 'Le Petit chose'),
(104, 'ALEXANDRE DUMAS', 'La Reine Margot'),
(105, 'ALEXANDRE DUMAS', 'Les Trois Mousquetaires');
INSERT INTO emprunt (id_emprunt, id_livre, id_abonne, date_sortie, date_rendu) VALUES
(1, 100, 1, '2014-12-17', '2014-12-18'),
(2, 101, 2, '2014-12-18', '2014-12-20'),
(3, 100, 3, '2014-12-19', '2014-12-22'),
(4, 103, 4, '2014-12-19', '2014-12-22'),
(5, 104, 1, '2014-12-19', '2014-12-28'),
(6, 105, 2, '2015-03-20', '2015-03-26'),
(7, 105, 3, '2015-06-13', NULL),
(8, 100, 2, '2015-06-15', NULL);
Requête Imbriquée sur plusieurs tables
Avant d'effectuer des requêtes sur plusieurs tables, vous pouvez prendre connaissance de vos nouvelles données en effectuant les requêtes suivantes :
Requête SQL SELECT
SELECT * FROM abonne ;
SELECT * FROM livre ;
SELECT * FROM emprunt ;
Pour une première requête,
nous essaierons d'afficher les id des livres encore dans la nature n'ayant pas été rendus à la bibliothèque
La logique voudrait que l'on inscrive la requête suivante :
Requête SQL SELECT
SELECT id_livre FROM emprunt WHERE date_rendu = NULL;
Cas particulier la valeur NULL se teste avec le mot clé IS, voici donc la bonne requête :
Requête SQL SELECT
SELECT id_livre FROM emprunt WHERE date_rendu IS NULL;
Pour la prochaine requête,
essayons de trouver les titres des livres dans la nature n'ayant pas été rendus à la bibliothèque
Si nous souhaitons savoir si un livre a été rendu ou non, nous aurons besoin de la table emprunt (avec le champ date_rendu).
Si nous souhaitons connaitre le titre d'un livre, nous aurons besoin de la table livre (avec le champ titre).
Nous allons donc avoir besoin d'exécuter une requête imbriquée sur 2 tables différentes.
Demande : Quels sont les titres des livres n'ayant pas été rendus à la bibliothèque ?
Requête :
SQL - Requête Imbriquée - SELECT
SELECT titre FROM livre WHERE id_livre IN
(SELECT id_livre FROM emprunt WHERE date_rendu IS NULL);
Explications : La requête entre parenthèse s'exécute en premier et nous sort les id de livre 105 et 100.
Ensuite, nous demandons les titres des livres dans la table emprunt correspondant aux id des livres 105 et 100.
Nous utilisons le mot clé IN et non pas le signe = (égal) car plusieurs résultats seront renvoyés par la sous requête.
Dans le cadre de cet exemple, pour assurer la jonction et la correspondance des données, nous utilisons le champ id_livre, le champ annoncé en condition WHERE doit être le même que le champ SELECT juste à après.
Résultat :
titre |
---|
Une vie |
Les Trois Mousquetaires |
AttentionPour qu'une requête imbriquée sur 2 tables soit possible, il faut absolument qu'une information soit commune aux 2 tables afin que la jonction et la correspondance entre les données puissent être faites.
Dans notre cas, nous avons utilisé le champ id_livre pour joindre et faire correspondre les informations de la table livre et de la table emprunt.
Demande : Nous aimerions connaitre le n° (id) de(s) livre(s) que Chloé a emprunté à la bibliothèque
Indices : Chloé se trouve dans la table abonné, les informations (id) sur les livres qu'elle a empruntés se trouvent dans la table emprunt.
Il est donc nécessaire de faire la passerelle entre ces deux tables.
Comment faire ? un champ se répète à la fois dans la table abonné et dans la table emprunt, il s'agit du champ id_abonne. C'est grâce à ce champ que nous pourrons faire la passerelle entre les deux tables.
N'oubliez pas qu'un champ doit absolument être commun aux 2 tables pour que la jonction et la correspondance des données puissent se faire dans le cadre d'une requête imbriquée.
Requête :
SQL - Requête Imbriquée - SELECT
SELECT titre FROM livre WHERE id_livre IN
(SELECT id_livre FROM emprunt WHERE date_rendu IS NULL);
Explications : La requête entre parenthèse s'exécute en premier et nous sort de la table emprunt les id de livre 105 et 100 (ayant une date de rendu à null et donc hors de la bibliothèque).
Ensuite, nous demandons les titres des livres (dans la table livre) correspondant aux id des livres 105 et 100.
Résultat :
titre |
---|
Une vie |
Les Trois Mousquetaires |
Demande : Afficher les prénoms des abonnés ayant emprunté un livre le 19/12/2014
Indices :
- La date de sortie d'un livre se trouve dans la table emprunt.
- Les prénoms des abonnés se trouve dans la table abonné.
- Le champ en commun entre ces deux tables permettant de faire la passerelle est le champ id_abonne.
Requête :
SQL - Requête Imbriquée - SELECT
SELECT prenom FROM abonne WHERE id_abonne IN
( SELECT id_abonne FROM emprunt WHERE date_sortie='2014-12-19' ) ;
Explications : La requête entre parenthèse s'exécute en premier et nous sort de la table emprunt les id des abonnés ayant emprunté un livre le 19/12/2014.
Ces id sont 1, 3 et 4. Lorsqu'une sous requête est suceptible de sortir plusieurs résultats, il faut prévoir le mot clé IN acceptant plusieurs valeurs, et non pas le signe égal = n'acceptant qu'une seule valeur.
Ensuite, nous demandons les prénoms des abonnés correspondant aux n° id 1, 3 et 4.
Résultat :
prenom |
---|
Guillaume |
Chloe |
Laura |
Demande : Afficher la liste des abonnés ayant déjà emprunté un livre d'Alphonse DAUDET
Indices :
- La liste des abonnés se trouvent dans la table abonné.
- Les n° id des livres écrits par Alphonse Daudet se trouve dans la table livre.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relier la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.
Requête :
SQL - Requête Imbriquée - SELECT
SELECT prenom FROM abonne WHERE id_abonne IN
(SELECT id_abonne FROM emprunt WHERE id_livre IN
(SELECT id_livre FROM livre WHERE auteur='ALPHONSE DAUDET') );
Explications :
La dernière requête entre parenthèse s'exécute en premier et nous sort de la table livre les id des livres écrits par Alphonse Daudet.
SELECT id_livre FROM livre WHERE auteur='ALPHONSE DAUDET';
Le résultat de cette requête est :
Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id d'abonnés des personnes ayant emprunté ces id de livres (le 103).
SELECT id_abonne FROM emprunt WHERE id_livre IN(103);
Le résultat de cette requête est :
Pour terminer, dans la requête du dessus, nous demandons à afficher les prénoms des abonnés répondant à ces id d'abonnés (le 4).
SELECT prenom FROM abonne WHERE id_abonne IN(4);
Le résultat de cette requête est :
Résultat : C'est donc le résultat final.
Demande : Afficher le(s) titre de(s) livre(s) que Chloé a emprunté à la bibliothèque.
Indices :
- Les titres des livres se trouvent dans la table livre.
- Les prénoms des abonnés se trouvent dans la table abonné.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relier la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.
Requête :
SQL - Requête Imbriquée - SELECT
SELECT titre FROM livre WHERE id_livre IN
(SELECT id_livre FROM emprunt WHERE id_abonne =
(SELECT id_abonne FROM abonne WHERE prenom='chloe') );
Explications :
La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Chloé (dans le cadre de cet entrainement, nous considérerons qu'il n'y a qu'une seule Chloé).
SELECT id_abonne FROM abonne WHERE prenom='chloe';
Le résultat de cette requête est :
Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id de livres ayant été emprunté par l'abonné n°3
SELECT id_livre FROM emprunt WHERE id_abonne = 3;
Le résultat de cette requête est :
Pour terminer, dans la requête du dessus, nous demandons à afficher les titres des livres correspondants aux n° de livre 100 et 105..
SELECT titre FROM livre WHERE id_livre IN(100,105);
Le résultat de cette requête est :
titre |
---|
Une vie |
Les Trois Mousquetaires |
Résultat : C'est donc le résultat final.
titre |
---|
Une vie |
Les Trois Mousquetaires |
Demande : Afficher le(s) titre de(s) livre(s) que Chloé n'a pas encore emprunté à la bibliothèque.
Indices :
- Les titres des livres se trouvent dans la table livre.
- Les prénoms des abonnés se trouvent dans la table abonné.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relié la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.
Requête :
SQL - Requête Imbriquée - SELECT
SELECT titre FROM livre WHERE id_livre NOT IN
(SELECT id_livre FROM emprunt WHERE id_abonne =
(SELECT id_abonne FROM abonne WHERE prenom='chloe') );
Explications :
La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Chloé (dans le cadre de cet entrainement, nous considérerons qu'il n'y a qu'une seule Chloé).
SELECT id_abonne FROM abonne WHERE prenom='chloe';
Le résultat de cette requête est :
Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id de livres ayant été empruntés par l'abonné n°3
SELECT id_livre FROM emprunt WHERE id_abonne = 3;
Le résultat de cette requête est :
Pour terminer, dans la requête du dessus, nous demandons à afficher les titres des livres qui NE SONT PAS les n° de livres récupérés soit le 100 et 105.
En effet, nous ne voulons pas connaitre les n° de livres que Chloé à emprunté mais tous les autres !
Cette requête permet d'abord de lister les n° de livres que chloé à emprunté pour ensuite les exclures (NOT IN) au moment de la demande des titres de livres.
SELECT titre FROM livre WHERE id_livre NOT IN(100,105);
Le résultat de cette requête est :
titre |
---|
Bel-Ami |
Le père Goriot |
Le Petit chose |
La Reine Margot |
Résultat : C'est donc le résultat final.
titre |
---|
Bel-Ami |
Le père Goriot |
Le Petit chose |
La Reine Margot |
Demande : Afficher le(s) titre de(s) livre(s) que Chloé n'a pas encore rendu(s) à la bibliothèque.
Indices :
- Les titres des livres se trouvent dans la table livre.
- Les prénoms des abonnés se trouvent dans la table abonné.
- La liste des emprunts (qui a emprunté quoi ?) se trouve dans la table emprunt.
- Nous ne pouvons pas relier la table abonné directement avec la table livre (car ces deux tables ne possèdent pas de champ en commun).
- Nous pouvons relier la table livre avec la table emprunt. Nous pouvons aussi relier la table abonne avec la table emprunt.
Requête :
SQL - Requête Imbriquée - SELECT
SELECT titre FROM livre WHERE id_livre IN
(SELECT id_livre FROM emprunt WHERE date_rendu IS NULL AND id_abonne =
(SELECT id_abonne FROM abonne WHERE prenom='chloe') );
Explications :
La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Chloé (dans le cadre de cet entrainement, nous considérerons qu'il n'y a qu'une seule Chloé).
SELECT id_abonne FROM abonne WHERE prenom='chloe';
Le résultat de cette requête est :
Ensuite, dans la requête du milieu (entre parenthèses) nous demandons à obtenir les id de livres ayant été empruntés par l'abonné n°3 et n'ayant pas été rendus (date rendu is null)
SELECT id_livre FROM emprunt WHERE date_rendu IS NULL AND id_abonne = 3;
Le résultat de cette requête est :
Pour terminer, dans la requête du dessus, nous demandons à afficher les titres des livres qui correspondent au n° (id) de livres récupérés (dans notre exemple, le 105)
SELECT titre FROM livre WHERE id_livre IN(105);
Dans le cadre de cette requête, même si un seul résultat aurait pu être compris par le signe égal (=), il était plus prudent de prévoir le mot clé IN dans la mesure où le nombre de résultats renvoyés par la requête du milieu peut changer à tout moment.
Le résultat de cette requête est :
titre |
---|
Les Trois Mousquetaires |
Résultat : C'est donc le résultat final.
titre |
---|
Les Trois Mousquetaires |
Demande : Combien de livre Guillaume a emprunté à la bibliotheque ?
Indices :
- Nous ne souhaitons pas savoir quels sont les livres empruntés par Guillaume mais combien il en a emprunté.
- Nous devrons prendre en compte la table abonné (pour connaitre le n° d'abonné de Guillaume)
- Nous devrons également prendre en compte la table emprunt puisque à l'intérieur tous les emprunts y sont repertoriés.
- Le champ en commun entre les deux tables est id_abonne. Il servira donc de jonction dans le cadre de notre requête imbriquée pour assurer la correspondance des données.
Requête :
SQL - Requête Imbriquée - SELECT
SELECT COUNT(*) AS 'nombre de livre' FROM emprunt WHERE id_abonne =
(SELECT id_abonne FROM abonne WHERE prenom='guillaume');
Explications :
La dernière requête entre parenthèse s'exécute en premier et nous sort de la table abonné l'id d'abonné de Guillaume.
SELECT id_abonne FROM abonne WHERE prenom = 'guillaume';
Le résultat de cette requête est :
Ensuite, la première requête nous permet de compter (via le mot clé COUNT) tous les emprunts réalisés par cet abonné (le n°1).
SELECT COUNT(*) AS 'nombre de livre' FROM emprunt WHERE id_abonne = 1;
le mot clé AS permet de donner un alias pour renommer l'entête du résultat (cela reste facultatif mais toujours conseillé car plus lisible).
Le résultat de cette requête est :
Résultat : C'est donc le résultat final.