Les requêtes sélection

Dans le chapitre précédent je vous ai donné le code SQL d'une requête sélection mais sans donner d'explications.

Une requête sélection extrait des données d'une ou plusieurs tables de la base.
A la différence d'une requête action (requête qui commence par UPDATE ou INSERT ou DELETE) une requête sélection ne modifie pas les données de la base.

Les requêtes sélection monotables

Commençons par le plus simple : les requêtes sélection qui extraient des données d'une table.

Dans la base de données du site de l'association il y a la table "news" mais aussi la table "membres" ( qui reprend tous les adhérants au club).

Structure de la table "membres"

nom du champtype de champtailleautres attributs
idintAUTO_INCREMENT PRIMARY_KEY
nom varchar30NOT NULL
prenomvarchar20NOT NULL
sexevarchar1NOT NULL DEFAULT 'M'
adresse1varchar50NULL
adresse2varchar50NULL
adresse3varchar50NULL
tel1varchar14NULL
tel2varchar14NULL
bureautinyintNOT NULL DEFAULT 0
date_naissdateNOT NULL
mailvarchar50NULL

Remarque que certains champs ont l'attribut NULL et d'autres NOT NULL.
Si un champ à l'attribut NOT NULL cela signifie qu'il faut affecter un valeur à ce champ lors de l'ajout d'un nouvel enregistement.
Par contre si l'attribut est à NULL et lors de l'insertion d'un nouvel enregistrement, il n'est pas obligatoire d'affecter une valeur à ce champ.
Il y a aussi l'attribut DEFAULT. Ainsi si on n'a pas rempli le champ BUREAU à l'occasion d'un ajout de ligne, c'est la valeur 0 qui lui sera affectée.

Contenu de la table

Certaines colonnes n'apparaissent pas ici dans un souci de lisibilité ; il s'agit des champs : adresse1, adresse2, adresse3 , tel1 et tel2

idnomprenomsexebureaudate_naiss
1DupontJulienM01957-04-28
2BailleulHenrietteF11967-03-07
3DupuisEricM01980-02-08
4FrançoisHélèneF11947-01-09
5DumoulinJacquesM11970-06-15
6MillienjérémyM01977-09-13

Le champ "sexe" contient M ou F.
Le champ "bureau" contient 1 (membre du bureau) ou 0 (non membre du bureau).

Les requêts sélection sur cette table

Afficher toutes les lignes (ou enregistrements) et colonnes (ou champs)

	SELECT * FROM membres;

Les enregistrements sont affichés dans l'ordre de saisie.
Il est d'usage d'écrire les mots réservés du langage SQL en majuscules (ce n'est pas une obligation) et de terminer la requête par un point virgule.

La clause ORDER BY

Elle permet d'afficher les lignes selon un ordre différent de l'ordre de saisie donc d'effectuer un tri.

Afficher les membres dans l'ordre alphabétique :

	SELECT * FROM membres ORDER BY nom ; 
	ou mieux encore :
	SELECT * FROM membres ORDER BY nom, prenom ;

Afficher du plus jeune au plus vieux :
Le membre le plus jeune a la date de naissance la plus forte ; le plus âgé la plus faible.
Il faut donc afficher selon la colonne date_naiss par valeurs décroissantes.

	SELECT * FROM membres ORDER BY date_naiss DESC ;

DESC : comme "descending" (ou décroissant en Français).

La clause LIMIT

Elle permet de n'afficher que les lignes correspondant à une étendue.

Afficher les quatre premiers enregistrements saisis :

	SELECT * FROM membres LIMIT 0,4 ;

Afficher les quatre derniers enregistrements saisis :

	SELECT * FROM membres ORDER BY id DESC LIMIT 0,4 ;

Le premier nombre indique le début de la sélection (0 veut dire premier) et le deuxième nombre l'étendue.

La clause WHERE

Grâce à cette clause on n'affiche que les lignes vérifiant une condition. Il s'agit donc d'un filtrage d'enregistrements.

Afficher uniquement les membres du bureau (contenu du champ "bureau" vaut 1) :

 
	SELECT * FROM membres WHERE bureau =1 ;

Afficher les membres de sexe féminin :

	SELECT * FROM membres WHERE sexe ="F" ;

Affichez seulement certains colonnes (ou champs)

Il suffit de remplacer * par une énumération de champs.
Exemple : liste des membres du bureau par ordre alphabétique :

	SELECT nom, prenom, sexe, tel1, tel2 FROM membres WHERE bureau ="1" ORDER BY nom, prenom ;

Ici il y a sélection de colonnes (liste de champs après SELECT) ainsi que tri et filtrage de lignes.

Utiliser des fonctions SQL dans la requête

Afficher les noms en majuscules et les prénoms en minuscules :

	SELECT UPPER(nom) AS nom, LOWER(prenom) AS prenom, sexe, date_naiss	FROM membres ;

Tableau retourné :

nom				prenom			sexe		date_naiss	
DUPONT			julien			M			1957-04-28	
BAILLEUL		henriette		F			1967-03-07	
DUPUIS			eric			M			1980-02-08	
FRANÇOIS		hélène			F			1947-01-09	
DUMOULIN		jacques			M			1970-06-15	
MILLIEN			jérémy			M			1977-09-13	

Les noms sont affichés en majuscules grâce à la fonction UPPER et les prénoms en minuscules avec la fonction LOWER.
Mais la date est affichée dans un format peu lisibles pour un francophone ...

Utiliser la fonction date_format()

La requête :

	SELECT DATE_FORMAT(date_naiss,'%d-%m-%Y') AS date_naissance,nom,prenom FROM membres ;

Le tableau retourné :

date_naissance	nom			prenom	
28-04-1957		Dupont		Julien	
07-03-1967		Bailleul	Henriette	
08-02-1980		Dupuis		Eric	
09-01-1947		François	Hélène	
15-06-1970		Dumoulin	Jacques	
13-09-1977		Millien		jérémy	

Désormais la date est affichée dans un format compréhensible.

Des conditions de filtrage complexes

La clause WHERE peut être suivie par une expression complexe pour obtenir des conditions de filtrage sophistiquées.

Affichez les membres dont le nom commence par la lettre D

	SELECT * FROM membres WHERE nom LIKE 'D%'

Le caractère % est un joker ; il remplace un nombre variable de caractères.

Attention dès que vous utiliser le joker % il faut utiliser l'opérateur LIKE (et non pas =).

Autre solution :

	SELECT * FROM `membres` WHERE LEFT(nom,1) ='D'

Emploi de la fonction chaîne LEFT. Ici le deuxième argument de la fonction est 1 donc extraction du premier caractère de chaque nom pour comparer avec D.

MySQL n'est pas sensible à la casse ; retourne toutes les lignes dont le nom commence par 'd' ou 'D' !

Affichez les membres dont le nom commence par la lettre D ou par la lettre M

	SELECT * FROM membres WHERE nom LIKE 'D%' OR nom LIKE 'M%' 
	Ou
	SELECT * FROM `membres` WHERE LEFT(nom,1) ='D' OR LEFT(nom,1) ='M'

L'expression de filtrage comprend un opérateur logique, ici OR.

Les membres du bureau de sexe féminin

	SELECT * FROM `membres` WHERE bureau =1 AND sexe ='F'

Ici l'opérateur logique est AND.

Liste des membres dont le nom commence par n'importe quelle lettre autre que D

	SELECT * FROM membres WHERE nom NOT LIKE 'D%'
	Ou
	SELECT * FROM membres WHERE LEFT(nom,1) != 'D'

Ici emploi de l'opérateur logique NOT dans la première solution et != (différent) dans la deuxième solution.

Afficher les lignes de la table mal renseignées

	SELECT * FROM membres WHERE adresse1 IS NULL OR adresse3 IS NULL

Il existe aussi IS NOT NULL !

Afficher les membres qui ont 40 ans ou moins de 40 ans (en 2017)

	SELECT * FROM membres WHERE YEAR(date_naiss) >= 1977

On utilise la fonction YEAR pour extraire l'année de chaque champ date_naiss et comparer à 1977.
La liste des fonctions de type date proposées par SQL est impressionnante. Mais attention certaines ne sont pas implémentées sur tous les SGBD.

Requêtes multi-tables

Les données à manipuler

Le bureau de l'association vous demande maintenant de créer au sein du site du club un "forum". En effet avant de prendre un décision importante le bureau souhaite pouvoir consulter les autres membres, leur demander leur avis au travers d'un forum.

Donc pour chaque billet il y aura 0 ou N commentaires !
On peut envisager qu'à cette application ne corresponde qu'une seule table qui aurait alors la structure suivante :

nom du champtyperemarque
idintauto-increment ; clé primaire
titrevarchar
contenutext
date_billetdate
auteur_commentairevarchar
commentairetext
date_commentairedate

Le contenu de la table pourrait être alors le suivant :

id_billettitrecontenudate_billetauteurcommentairedate_commentaire
1billet 1Texte billet 12017-03-12JacquesRéaction au billet 1 2017-03-12
1billet 1Texte billet 12017-03-12LouisRéaction au billet 1 2017-03-14
2billet 2Texte billet 22017-03-22
3billet 3Texte billet 32017-03-25
4Billet 4Texte billet 42017-03-31DenisRéaction au billet 4 2017-03-31
4Billet 4Texte billet 42017-03-31FerdinandRéaction au billet 4 2017-04-02
4Billet 4Texte billet 42017-03-31PaulRéaction au billet 4 2017-04-06

Il y a deux réactions pour le billet 1, aucun commentaire pour les billets 2 et 3 et trois commentaires pour le billet 4.

Entre "billets" et "commentaires" il y a une relation 1 à 0-N ; un billet c'est 0 à N commentaires.

Utilisation de deux tables

La table "billets"

Structure de la table :

nom du champtyperemarque
id_billetintauto-increment ; clé primaire
titrevarchar
contenutext
date_billetdate

Le contenu serait alors :

id_billettitrecontenudate_billet
1billet 1Texte billet 12017-03-22
2billet 2Texte billet 22017-03-30
3billet 3Texte billet 32017-03-27
4Billet 4Texte billet 42017-03-31

La table contient 4 lignes : le nombre de billets.

La table "commentaires"

Il faut impérativement pouvoir relier un commentaire à un billet. Il faut donc un champ commun entre les deux tables.
Dans la table principale( table "billets") ce champ commun est clé primaire (doublons interdits) pour identifier sans ambiguïté un billet.
Dans la table fille (table "commentaires") ce champ commun doit autoriser les doublons puisqu'il peut y avoir plusieurs commentaires pour chaque billet.
Ce champ commun peut porter le même nom dans les deux tables. C'est ce que je fais ici en nommant le champ commun "id_billet" dans les deux tables. Mais ce n'est pas une obligation !

Structure de la table "commentaires" :

nom du champtyperemarque
id_commentaireintauto-increment ; clé primaire
id_billetint
auteurvarchar
commentairetext
date_commentairedate

Le contenu de la table "commentaires" :

id_commentaireid_billetauteurcommentairedate_commentaire
11jacquesRéaction au billet 12017-03-23
21LouisRéaction au billet 12017-03-25
34DenisRéaction au billet 42017-03-31
44FerdinandRéaction au billet 42017-04-02
54PaulRéaction au billet 42017-04-06

La table contient 5 enregistrements : le nombre de commentaires.
On peut relier les deux premières lignes au premier billet et les trois dernières lignes au quatrième billet.

Les requêtes sélection multi-tables.

Requête avec jointure interne

Le code de la requête :

	SELECT * FROM billets INNER JOIN commentaires
	ON billets.id_billet = commentaires.id_billet ;

Ici le caractère * indique que l'on prend en compte toutes les colonnes de deux tables : "billets" et "commentaires".

Comme il y a deux champs ayant le même nom (id_billet) il faut lever toute ambiguïté en utilisant le nom complet du champ (nom_table.nomchamp) au niveau de l'expression de jointure.
Cette expression de jointure indique que l'on associe à chaque ligne de la table "billets" des lignes dans la table "commentaire" qui ont le même contenu pour le champ commun "id_billet".

Tableau retourné par la requête :

id_billettitrecontenudate_billetid_commentaireid_billetauteur commentairedate_commentaire
1billet 1Texte du billet 12017-03-2211jacquesRéaction au billet 1 2017-03-23
1billet 1Texte du billet 12017-03-2221LouisRéaction au billet 1 2017-03-25
4Billet 4Texte du billet 42017-03-3134DenisRéaction au billet 4 2017-03-31
4Billet 4Texte du billet 42017-03-3144FerdinandRéaction au billet 4 2017-04-02
4Billet 4Texte du billet 42017-03-3154PaulRéaction au billet 4 2017-05-06

Cette requête n'affiche que cinq lignes (nombre de commentaires). Les billets 2 et 3 ne sont pas pris en compte !
En effet une requête avec jointure interne ne prend pas en compte les lignes de la table mère "n'ayant pas d'enfants" c'est à dire n'ayant pas de correspondant dans la table fille (table "commentaires" dans l'exemple).

Requête avec jointure externe gauche

Le code de la requête :

	SELECT * FROM billets LEFT JOIN commentaires
	ON billets.id_billet = commentaires.id_billet ORDER BY billets.id_billet;

La table "billets" est la table gauche (à gauche de JOIN) et la table "commentaires" est la table droite (à droite de JOIN).

Tableau retourné par la requête :

id_billettitrecontenudate_billetid_commentaireid_billetauteur commentairedate_commentaire
1 billet 1Texte du billet 12017-03-2211jacquesRéaction au billet 1. 2017-03-23
1 billet 1 Texte du billet 12017-03-222 1LouisRéaction au billet 12017-03-25
2billet 2Texte billet 22017-03-30NULLNULLNULLNULLNULL
3billet 3Texte billet 32017-03-27NULLNULLNULLNULLNULL
4Billet 4Texte du billet 42017-03-3134DenisRéaction au billet 4 2017-03-31
4Billet 4Texte du billet 42017-03-3144FerdinandRéaction au billet 4 2017-04-02
4Billet 4Texte du billet 42017-03-3154PaulRéaction au billet 4 2017-05-06

Le tableau retourné comprend 7 lignes.
Désormais tous les enregistrements de la table mère (billets) sont pris en compte même les lignes de "billets" qui n'ont pas de correspondant dans la table "commentaires".

Amélioration de la requête

Dans le cadre d'une requête sur deux tables il faut éviter d'afficher les deux champs de jointure s'ils portent le même nom (ce qui est le cas ici : id_billet) !

La requête améliorée :

	SELECT billets.id_billet, titre, contenu, date_billet, commentaire, date_commentaire
	FROM billets left join commentaires
	ON billets.id_billet = commentaires.id_billet ORDER BY billets.id_billet ;

Le caractère * remplacé par une énumération de colonnes appartenant aux deux tables.
Il faut préciser le nom complet pour le champ id_billet (nomTable.nomChamp) sinon message d'erreur de MYSQL : "champ ambigu !"

Je n'évoque pas dans ce chapitre les pages PHP pour faire vivre ce forum.

L'administrateur du forum doit pouvoir publier de nouveaux billets.
Retour menu