Vous pouvez me contacter via Facebook pour questions & suggestions : Page Facebook relative à mon site
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.
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).
nom col. | type | taille | autres attributs |
---|---|---|---|
id | int | AUTO_INCREMENT PRIMARY_KEY | |
nom | varchar | 30 | NOT NULL |
prenom | varchar | 20 | NOT NULL |
sexe | varchar | 1 | NOT NULL DEFAULT 'M' |
adresse1 | varchar | 50 | NULL |
adresse2 | varchar | 50 | NULL |
adresse3 | varchar | 50 | NULL |
tel1 | varchar | 14 | NULL |
tel2 | varchar | 14 | NULL |
bureau | tinyint | NOT NULL DEFAULT 0 | |
date_naiss | date | NOT NULL | |
varchar | 50 | NULL |
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.
Certaines colonnes sont masquées ; il s'agit des champs : adresse1, adresse2, adresse3 , tel1 et tel2
id | nom | prenom | sexe | bureau | date_naiss |
---|---|---|---|---|---|
1 | Dupont | Julien | M | 0 | 1957-04-28 |
2 | Bailleul | Henriette | F | 1 | 1967-03-07 |
3 | Dupuis | Eric | M | 0 | 1980-02-08 |
4 | François | Hélène | F | 1 | 1947-01-09 |
5 | Dumoulin | Jacques | M | 1 | 1970-06-15 |
6 | Millien | jérémy | M | 0 | 1977-09-13 |
Le champ "sexe" contient M ou F.
Le champ "bureau" contient 1 (membre du bureau) ou 0 (non membre du bureau).
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.
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).
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.
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" ;
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.
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é :
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 ...
La requête :
SELECT DATE_FORMAT(date_naiss,'%d-%m-%Y') AS date_naissance,nom,prenom FROM membres ;
Le tableau retourné :
Désormais la date est affichée dans un format compréhensible.
La clause WHERE peut être suivie par une expression complexe pour obtenir des conditions de filtrage sophistiquées.
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' !
L'expression de filtrage comprend un opérateur logique, ici OR.
SELECT * FROM `membres` WHERE bureau =1 AND sexe ='F'
Ici l'opérateur logique est AND.
Ici emploi de l'opérateur logique NOT dans la première solution et != (différent) dans la deuxième solution.
SELECT * FROM membres WHERE adresse1 IS NULL OR adresse3 IS NULL ;
Il existe aussi IS NOT NULL !
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.
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 col. | type | remarque |
---|---|---|
id | int | auto-increment ; clé primaire |
titre | varchar | |
contenu | text | |
date_billet | date | |
auteur_commentaire | varchar | |
commentaire | text | |
date_commentaire | date |
Le contenu de la table pourrait être alors le suivant :
id_billet | titre | contenu | date_billet | auteur | commentaire | date_commentaire |
---|---|---|---|---|---|---|
1 | billet 1 | Texte billet 1 | 2017-03-12 | Jacques | Réaction au billet 1 | 2017-03-12 |
1 | billet 1 | Texte billet 1 | 2017-03-12 | Louis | Réaction au billet 1 | 2017-03-14 |
2 | billet 2 | Texte billet 2 | 2017-03-22 | |||
3 | billet 3 | Texte billet 3 | 2017-03-25 | |||
4 | Billet 4 | Texte billet 4 | 2017-03-31 | Denis | Réaction au billet 4 | 2017-03-31 |
4 | Billet 4 | Texte billet 4 | 2017-03-31 | Ferdinand | Réaction au billet 4 | 2017-04-02 |
4 | Billet 4 | Texte billet 4 | 2017-03-31 | Paul | Ré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.
Structure de la table :
nom du champ | type | remarque |
---|---|---|
id_billet | int | auto-increment ; clé primaire |
titre | varchar | |
contenu | text | |
date_billet | date |
Le contenu serait alors :
id_billet | titre | contenu | date_billet |
---|---|---|---|
1 | billet 1 | Texte billet 1 | 2017-03-22 |
2 | billet 2 | Texte billet 2 | 2017-03-30 |
3 | billet 3 | Texte billet 3 | 2017-03-27 |
4 | Billet 4 | Texte billet 4 | 2017-03-31 |
La table contient 4 lignes : le nombre de billets.
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 champ | type | remarque |
---|---|---|
id_commentaire | int | auto-increment ; clé primaire |
id_billet | int | |
auteur | varchar | |
commentaire | text | |
date_commentaire | date |
Le contenu de la table "commentaires" :
id_commentaire | id_billet | auteur | commentaire | date_ ... |
---|---|---|---|---|
1 | 1 | jacques | Réaction au billet 1 | 2017-03-23 |
2 | 1 | Louis | Réaction au billet 1 | 2017-03-25 |
3 | 4 | Denis | Réaction au billet 4 | 2017-03-31 |
4 | 4 | Ferdinand | Réaction au billet 4 | 2017-04-02 |
5 | 4 | Paul | Réaction au billet 4 | 2017-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.
Elles permettent d'extraire des données appartenant à plusieurs tables.
Le code de la requête :
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".
Le rendu la requête :
id_billet | titre | contenu | date_billet | id_commentaire | id_billet | auteur | commentaire | date_commentaire |
---|---|---|---|---|---|---|---|---|
1 | billet 1 | Texte du billet 1 | 2017-03-22 | 1 | 1 | jacques | Réaction au billet 1 | 2017-03-23 |
1 | billet 1 | Texte du billet 1 | 2017-03-22 | 2 | 1 | Louis | Réaction au billet 1 | 2017-03-25 |
4 | Billet 4 | Texte du billet 4 | 2017-03-31 | 3 | 4 | Denis | Réaction au billet 4 | 2017-03-31 |
4 | Billet 4 | Texte du billet 4 | 2017-03-31 | 4 | 4 | Ferdinand | Réaction au billet 4 | 2017-04-02 |
4 | Billet 4 | Texte du billet 4 | 2017-03-31 | 5 | 4 | Paul | Ré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 "sans enfants".
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 :
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_billet | titre | contenu | date_billet | id_commentaire | id_billet | auteur | commentaire | date_commentaire |
---|---|---|---|---|---|---|---|---|
1 | billet 1 | Texte du billet 1 | 2017-03-22 | 1 | 1 | jacques | Réaction au billet 1 | 2017-03-23 |
1 | billet 1 | Texte du billet 1 | 2017-03-22 | 2 | 1 | Louis | Réaction au billet 1 | 2017-03-25 |
2 | billet 2 | Texte billet 2 | 2017-03-30 | NULL | NULL | NULL | NULL | NULL |
3 | billet 3 | Texte billet 3 | 2017-03-27 | NULL | NULL | NULL | NULL | NULL |
4 | Billet 4 | Texte du billet 4 | 2017-03-31 | 3 | 4 | Denis | Réaction au billet 4 | 2017-03-31 |
4 | Billet 4 | Texte du billet 4 | 2017-03-31 | 4 | 4 | Ferdinand | Réaction au billet 4 | 2017-04-02 | 4 | Billet 4 | Texte du billet 4 | 2017-03-31 | 5 | 4 | Paul | Ré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 encore de commentaires.
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 :
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.
L'administrateur du forum doit pouvoir aussi publier de nouveaux billets.