Débuter en programmation web - sommaire

Requêtes sélection SQL monotable et multi tables

Attention, certains tableaux de cette page ne sont pas affichés sur un smartphone car trop larges.
Visualisez cette page de préférence avec un PC voire une tablette (éventuellement en mode paysage).

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"

Remarque que certaines colonnes 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 sont masquées ; il s'agit des champs : adresse1, adresse2, adresse3 , tel1 et tel2

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)

La requête est tout simplement :
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" ;

Sélection de 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

La requête :
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

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

Afficher 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

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.

Un forum informatique comprend :
• des billets
• des réactions (ou commentaires) aux billets

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 :

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.

Vous pouvez notez que l'utilisation d'une table unique génère des redondances d'informations :
• Le numéro, le titre, le contenu, la date du billet 1 sont mentionnés 2 fois.
• Le numéro, le titre, le contenu, la date du billet 4 sont mentionnés 3 fois.

Utilisation de deux tables

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

Pour éviter les répétitions de données constatées dans le cadre de la table unique il faut mieux utiliser deux tables :
• table "billets" : table mère (ou table principale) : côté 1 de la relation
• table "commentaires" (aux billets) : table fille (ou table liée) : côté 0-N de la relation

La table "billets"

Structure de la table :

Le contenu serait alors :

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" :

Le contenu de la table "commentaires" :

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.

Elles permettent d'extraire des données appartenant à plusieurs 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".

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 "sans enfants".

Requête avec jointure externe gauche

Toutes les lignes de la table "billets" doivent être pris en compte même s'il n'ont pas encore "d'enfants" dans la table "commentaires".
Le code de la requête avec jointure externe gauche : 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).

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 encore de 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.

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 * est 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.

Il faut que les personnes autorisées (les inscrits au forum) puissent :
• lire les derniers billets avec les commentaires actuels
• rajouter un commentaire à l'un des billets

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