Apprendre SQL : Lire dans sa BDD (SELECT)

Dans un CRUD, on retrouve également une opération dite de lecture. Pour lire des données, on effectuera des requêtes de sélection.

Sélection

Pour récupérer des données stockées en BDD, on utilise les mots-clés SELECT et FROM.

• SELECT permet de spécifier quelles colonnes récupérer au sein de la table.
• FROM permet de spécifier dans quelle table effectuer la sélection.

Cette commande retourne des enregistrements dans un tableau de résultat.

-- Récupération d'une colonne
SELECT colonne FROM nom_table;

-- Récupération de plusieurs colonnes
SELECT colonne_1, colonne_2 FROM nom_table;

-- Récupération de toutes les colonnes
SELECT * FROM nom_table;

SELECT titre FROM articles;

L’utilisation de la commande SELECT récupère l’ensemble des enregistrements d’une table. En revanche, cette commande peut potentiellement afficher des lignes en double. Pour éviter des doublons dans les résultats il faudra alors ajouter DISTINCT après le mot SELECT.

SELECT DISTINCT colonne
FROM nom_table;

SELECT DISTINCT auteur FROM articles;

Si ces commandes s’avèrent très utiles lorsqu’il s’agit de travailler sur un jeu de données complet, bien souvent, nous ne souhaitons en récupérer qu’une partie. Pour cela, nous aurons la possibilité de filtrer les résultats avec la clause WHERE, abordée plus tard dans ce cours.

Calculs

Parfois, il est intéressant d’effectuer des calculs sur un ensemble de données d’une colonne. Nous aurons alors besoin de fonctions d’agrégations statistiques.

SELECT FONCTION(colonne) FROM nom_table;

Il existe plusieurs fonctions d’agrégations statistiques qui s’appliquent sur un ensemble d’enregistrements. Les principales sont les suivantes :

• SUM() pour calculer la somme de plusieurs lignes.
• AVG() pour calculer la moyenne de plusieurs lignes.
• COUNT() pour compter le nombre de lignes concernées.
• MAX() pour récupérer la plus grande valeur.
• MIN() pour récupérer la plus petite valeur.

Faire appel à une fonction d’agrégation statistique a pour effet de créer une nouvelle colonne contenant le résultat. Cette colonne sera nommée du nom de la fonction utilisée.

SELECT
SUM(vues) AS total,
AVG(vues) AS moyenne,
MAX(vues) AS record,
COUNT(vues) AS videos
FROM videos;

Groupement

Bien souvent, il est utile de grouper des données par colonne et d’utiliser une fonction statistique d’agrégation sur un groupe de résultats. On utilisera pour cela la commande GROUP BY.

SELECT colonne_1, FONCTION(colonne_2)
FROM nom_table
GROUP BY colonne_1;

SELECT devise, SUM(quantite) AS total
FROM transactions
GROUP BY devise;

SELECT pseudo, devise, SUM(quantite) as total
FROM transactions
GROUP BY pseudo, devise;

Filtrage

Filtrer sur un tuple

Dans une requête SQL, la commande WHERE permet d’extraire des lignes d’une base de données respectant une condition. Cette clause vient compléter un SELECT de la manière suivante :

SELECT * FROM nom_table WHERE {condition};

Filtrer sur un groupe de tuples

Si un SELECT est combiné à un GROUP BY, il sera également possible de spécifier une condition de filtre non pas sur une ligne individuelle (WHERE) mais sur une agrégation de lignes avec la commande HAVING.

Pour rappel, cette agrégation est possible grâce à des fonctions statistiques comme COUNT(), SUM(), AVG(), MIN() ou encore MAX().

SELECT * FROM nom_table GROUP BY colonne HAVING {condition};

Ecrire une condition

Opérateurs de comparaison

Pour écrire une condition, on fait appel à des opérateurs de comparaison.

SELECT * FROM nom_table WHERE colonne {operateur_de_comparaison} valeur;

Opérateurs de comparaisons génériques

Ces opérateurs de comparaisons génériques sont identiques aux opérateurs élémentaires utilisés en algorithmie.

Opérateurs de comparaison spécifiques

En plus des opérateurs de comparaison universels, SQL propose des opérateurs spécifiques au langage, permettant d’écrire des requêtes plus simplement.

Ces opérateurs sont les suivants :

SELECT * FROM nom_table WHERE colonne IN ('machin', 'bidule', 'truc') permet d’éviter d’écrire :

SELECT * FROM nom_table WHERE
colonne = 'machin' OR
colonne = 'bidule' OR
colonne = 'truc';

On qualifie cela de « sucre syntaxique ». Le sucre syntaxique constitue une écriture propre à un langage (ici SQL) qui permet d’écrire et de lire une expression de manière plus agréable, plus simple et expressive.

Opérateurs logiques

AND et OR

Comme il est souvent utile de vérifier la véracité de plusieurs conditions, il existe les opérateurs AND et OR qui permettent de cumuler des conditions.

SELECT * FROM nom_table WHERE condition_1 AND_ou_OR condition_2;

NOT

Il existe également l’opérateur NOT qui va quant à lui nous servir à vérifier qu’une condition écrite avec les opérateurs (LIKE, BETWEEN ou encore LIKE) n’est pas vraie.

SELECT COUNT(id) FROM utilisateurs
WHERE age >= 18 AND vaccine = 1;

SELECT prenom, nom
FROM notes
GROUP BY prenom, nom
HAVING AVG(note) >= 10;

Tri

Trier les données renvoyées par une requête est possible avec la commande ORDER BY.

Cette commande doit être suivie d’un mot-clé caractérisant le sens de tri souhaité :

• ASC (ascendant) : croissant, alphabétique ou chronologique.
• DESC (descendant) : décroissant, anti-alphabétique ou anti-chronologique.

SELECT * FROM nom_table ORDER BY colonne_1 ASC, colonne_2 DESC;

SELECT * FROM utilisateurs
WHERE vaccine = 1
ORDER BY age DESC;

SELECT DISTINCT prenom FROM utilisateurs
ORDER BY prenom;

Pagination

Limite

Parfois, il peut être intéressant de limiter le nombre de résultats que l’on souhaite récupérer (affichage des X derniers articles sur la page d’accueil d’un blog, pagination affichant Y produits par page, etc.).

Pour cela nous utiliserons la commande LIMIT en fin de requête.

SELECT * FROM nom_table ORDER BY colonne DESC LIMIT nombre_limite;

Décalage

Parfois, il est utile de décaler les lignes à obtenir. C’est-à-dire qu’au lieu de récupérer les X dernières ressources, on souhaiterait récupérer X ressources en sautant un certain nombre de résultats. On parle d’offset.

On précisera alors cet offset avant la limite, séparé d’une virgule ,.

SELECT * FROM nom_table ORDER BY colone DESC LIMIT nombre_decalage, nombre_limite;

La meilleure manière de comprendre cette notion est de penser à un système de pagination :

• Page 1 : Je souhaite récupérer les 10 derniers ***.
• Page 2 : Je souhaite récupérer les 10 *** suivants (je saute les 10 premiers).
• Page 3 : Je souhaite récupérer les 10 *** suivants (je saute les 20 premiers).
• Etc.

SELECT * FROM produits
ORDER BY prix
LIMIT 3;

SELECT * FROM produits
ORDER BY prix
LIMIT 6, 3;

Jointures

Les jointures en SQL permettent d’obtenir des résultats qui combinent des données de plusieurs tables liées entre elles. Basées sur la théorie mathématique des ensembles, elles sont l’essence même du potentiel offert par les bases de données dites relationnelles.

Pour bien comprendre l’intérêt des jointures et par extension des relations, écrivons une aberration. Voici une table articles qui stocke les articles d’un blog.

Plusieurs problèmes évidents se dégagent de cette structure :

• ❌ Une base de données trop lourde par duplication d’informations.
• ❌ Une base de données brouillon, avec des types d’informations mélangés.
• ❌ Une base de données non évolutive.
  • Admettons qu’un auteur souhaite modifier sa biographie ; il faudra modifier autant de lignes qu’il a écrit d’articles.
  • Si on souhaite ajouter une caractéristique à un auteur, alors il faudra la mettre à jour autant de fois qu’il a écrit d’articles.

Pour pallier à ces problématiques, la solution est de séparer ces informations en deux tables dont le rôle de chacune va être de stocker un type d’information spécifique.

Table articles

Table auteurs

Maintenant que vous êtes convaincus, parlons jointures !

En SQL, il existe plusieurs types de jointures, mais la plupart du temps nous utilisons la même, à savoir le INNER JOIN. Il s’agit littéralement d’une « jointure interne », permettant de retourner les enregistrements liés par une valeur commune au sein de deux tables, définie via le mot-clé ON.

SELECT * FROM table_1
INNER JOIN table_2 ON table_1.fk_id = table_2.id;

Cette syntaxe stipule qu’il faut sélectionner les enregistrements des tables table_1 et table_2 lorsque la clé étrangère de la table_1 (table_1.fk_id) est égale à la clé primaire de la table_2 (table_2.id).

SELECT articles.titre FROM articles 
INNER JOIN auteurs ON articles.auteur_id = auteurs.id
WHERE auteurs.prenom = 'John' AND auteurs.nom = 'Doe';

Requêtes imbriquées

Dans le langage SQL une sous-requête (aussi appelé « requête imbriquée ») consiste à intégrer une requête SQL à l’intérieur d’une autre.

Les requêtes imbriquées sont notées entre parenthèses et peuvent être intégrées à diverses parties de la requête parente. Habituellement, elles sont exploitées dans une clause WHERE ou HAVING, bien que leur utilisation ne soit pas limitée à cela.

Une requête imbriquée peut alors retourner :

• Une valeur
• Un enregistrement
• Plusieurs enregistrements

SELECT * FROM produits WHERE prix > (SELECT AVG(prix) FROM produits);

En permettant d’utiliser les résultats d’une requête à l’intérieur d’une autre, les requêtes imbriquées permettent des opérations complexes et spécifiques (filtrages, comparaisons, agrégations complexes…).