Vendredi 27 Octobre 2023 08:30

Cheatsheet SQL

#SQL

Parce qu’aujourd’hui nous restons enfermés dans nos frameworks, nous utilisons de moins en moins certains outils dans leur forme la plus simple. Personnellement en tant que développeur symfony, j’utilise Doctrine 99% du temps. Mais il arrive parfois que je doive faire quelques requêtes à la main, ne serait-ce que pour de la maintenance de db ou pour faire de petit export bien spécifique. Et c’est dans ces moment là que l’on regrette de ne pas en faire plus souvent…

Je vous propose donc une petite cheat sheet dédiée à SQL comprenant les fonctions basiques mais également certaines choses un peu plus spécifiques en fin d’article.

Mais pour commencer, il nous faut des données et vous le savez, j’aime bien me baser sur des données intéressante pour mes exemples, nous allons donc utiliser une table nommée “pokedex” ayant la structure et les données suivantes:

+-----+-----------+------------+-------------+------------+
| id  | name      | type_first | type_second | generation |
+-----+-----------+------------+-------------+------------+
| 144 | Artikodin | Glace      | Vol         |          1 |
| 145 | Électhor  | Electrique | Vol         |          1 |
| 146 | Sulfura   | Feu        | Vol         |          1 |
| 150 | Mewtwo    | Psy        |             |          1 |
| 151 | Mew       | Psy        |             |          1 |
| 249 | Lugia     | Psy        | Vol         |          2 |
| 250 | Ho-Oh     | Feu        | Vol         |          2 |
| 251 | Celebi    | Psy        | Plante      |          2 |
| 382 | Kyogre    | Eau        |             |          3 |
| 383 | Groudon   | Sol        |             |          3 |
| 384 | Rayquaza  | Dragon     | Vol         |          3 |
+-----+-----------+------------+-------------+------------+

Oui cette liste n’est pas complète je le sais bien, j’ai dû faire des choix.

Pour les cas complexes, nous aurons besoin d’une seconde table que voici:

+----+----------+--------+------------+
| id | name     | device | generation |
+----+----------+--------+------------+
|  1 | Rouge    | GB     |          1 |
|  2 | Bleu     | GB     |          1 |
|  3 | Vert     | GB     |          1 |
|  4 | Jaune    | GB     |          1 |
|  5 | Or       | GB     |          2 |
|  6 | Argent   | GB     |          2 |
|  7 | Crystal  | GBC    |          2 |
|  8 | Ruby     | GBA    |          3 |
|  9 | Saphir   | GBA    |          3 |
| 10 | Emeraude | GBA    |          3 |
+----+----------+--------+------------+

Les explications vont suivre mais si vous souhaitez avoir le script de génération des deux tables ci dessus, le voici:

CREATE TABLE pokedex (id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, type_first VARCHAR(255) NOT NULL, type_second VARCHAR(255), generation INT DEFAULT 0);
INSERT INTO `pokedex` VALUES (144, 'Artikodin', 'Glace', 'Vol', 1);
INSERT INTO `pokedex` VALUES (145, 'Électhor', 'Electrique', 'Vol', 1);
INSERT INTO `pokedex` VALUES (146, 'Sulfura', 'Feu', 'Vol', 1);
INSERT INTO `pokedex` VALUES (150, 'Mewtwo', 'Psy', '', 1);
INSERT INTO `pokedex` VALUES (151, 'Mew', 'Psy', '', 1);
INSERT INTO `pokedex` VALUES (249, 'Lugia', 'Psy', 'Vol', 2);
INSERT INTO `pokedex` VALUES (250, 'Ho-Oh', 'Feu', 'Vol', 2);
INSERT INTO `pokedex` VALUES (251, 'Celebi', 'Psy', 'Plante', 2);
INSERT INTO `pokedex` VALUES (382, 'Kyogre', 'Eau', '', 3);
INSERT INTO `pokedex` VALUES (383, 'Groudon', 'Sol', '', 3);
INSERT INTO `pokedex` VALUES (384, 'Rayquaza', 'Dragon', 'Vol', 3);
CREATE TABLE game (id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, device VARCHAR(255) NOT NULL, generation INT DEFAULT 0);
INSERT INTO `game` VALUES (1, 'Rouge', 'GB', 1);
INSERT INTO `game` VALUES (2, 'Bleu', 'GB', 1);
INSERT INTO `game` VALUES (3, 'Vert', 'GB', 1);
INSERT INTO `game` VALUES (4, 'Jaune', 'GB', 1);
INSERT INTO `game` VALUES (5, 'Or', 'GB', 2);
INSERT INTO `game` VALUES (6, 'Argent', 'GB', 2);
INSERT INTO `game` VALUES (7, 'Crystal', 'GBC', 2);
INSERT INTO `game` VALUES (8, 'Ruby', 'GBA', 3);
INSERT INTO `game` VALUES (9, 'Saphir', 'GBA', 3);
INSERT INTO `game` VALUES (10, 'Emeraude', 'GBA', 3);

Structure des tables

Avant toute chose, il est intéressant de bien visualiser ce qu’est une table. Le terme est comme toujours englais et signifie simplement tableau qui est composé de lignes (enregistrements) et de colonnes (champs), et chaque colonne représente un type de données spécifique.

Les types de champs

On est pas sur excell et on ne peut pas mettre n’importe quoi dans n’importe quelle champ. Lors de sa création, la table se voit attribuer un certain nombre de champs défini par un nom, un type et parfois une valeur par défaut ou d’autres paramètres plus spécifiques.

Types de texte

CHAR(size)

Type de texte de longueur fixe.
La taille est définie par size, et si la chaîne de caractères insérée est plus courte que la taille, les espaces sont ajoutés à la fin pour combler la différence.
Exemple : Si CHAR(5) reçoit la valeur 'abc', elle sera stockée sous la forme 'abc ' (avec 2 espaces à la fin).

VARCHAR(size)

Type de texte de longueur variable.
La longueur maximale est déterminée par size. Contrairement à CHAR, il n'y a pas d'ajout d'espaces à la fin des chaînes plus courtes.
Exemple : Si VARCHAR(5) reçoit la valeur 'abc', elle sera stockée telle quelle, sans ajout d'espaces.

TEXT

Champ de texte de grande taille, capable de stocker jusqu'à 2 Go de données textuelles.
Utilisé pour de longs textes, comme des descriptions, des articles, etc.

Types numériques

TINYINT

Nombre entier sur 1 octet (8 bits).
Les valeurs possibles sont entre 0 et 255 pour les valeurs non signées, et entre -128 et 127 pour les valeurs signées.
Exemple : Peut être utilisé pour stocker des valeurs booléennes (comme dans le cas de BOOL).

SMALLINT

Nombre entier sur 2 octets (16 bits).
Les valeurs possibles sont entre -32,768 et 32,767 pour les valeurs signées, et entre 0 et 65,535 pour les valeurs non signées.
Exemple : Peut être utilisé pour des petits intervalles de nombres.

INT

Nombre entier sur 4 octets (32 bits).
Les valeurs possibles sont entre -2,147,483,648 et 2,147,483,647 pour les valeurs signées, et entre 0 et 4,294,967,295 pour les valeurs non signées.
Exemple : Convient pour des identifiants ou des compteurs.

BIGINT

Nombre entier sur 8 octets (64 bits).
Les valeurs possibles sont entre -9,223,372,036,854,775,808 et 9,223,372,036,854,775,807 pour les valeurs signées, et entre 0 et 18,446,744,073,709,551,615 pour les valeurs non signées.
Exemple : Utilisé pour des très grands nombres (comme des numéros d’identification très larges).

FLOAT

Nombre à virgule flottante sur 4 octets (32 bits).
Permet de stocker des valeurs en notation scientifique, avec des plages de valeurs extrêmement larges, telles que -1.79E+308 à 1.79E+308.
Exemple : Peut être utilisé pour stocker des valeurs avec une précision variable.

DECIMAL(p, d)

Nombre décimal avec une précision définie, utilisé pour des valeurs qui doivent être exactes (comme les prix).
p est le nombre total de chiffres (précision) et d est le nombre de chiffres après la virgule (échelle).
Exemple : DECIMAL(10, 2) peut stocker des nombres allant jusqu’à 99999999.99 avec 2 chiffres après la virgule.

Types booléens

BOOL

Type booléen stocké comme un TINYINT. Les valeurs possibles sont 0 (faux), 1 (vrai) ou NULL.
Exemple : Utilisé pour les indicateurs ou les champs logiques.

Types de date et heure

DATE

Stocke une date sous le format YYYY-MM-DD.
Exemple : 2024-10-09.

DATETIME

Stocke une date et une heure sous le format YYYY-MM-DD hh:mm:ss.
Exemple : 2024-10-09 14:30:00.

TIMESTAMP

Stocke l'heure et la date actuelles avec une granularité à la seconde. Il est mis à jour automatiquement lors de la modification d'une ligne si configuré comme tel.
Utilisé pour enregistrer les événements comme les mises à jour d'enregistrements.

TIME(fsp)

Stocke une heure sous le format hh:mm:ss. La précision fractionnaire facultative (fsp) permet de définir le nombre de décimales après les secondes.
Exemple : 12:30:45.

YEAR

Stocke une année sous le format YYYY.
Exemple : 2024.

Caractéristiques importantes d'un champ

PRIMARY KEY (Clé primaire) : Une colonne (ou un ensemble de colonnes) qui identifie de manière unique chaque ligne d'une table. Dans l'exemple ci-dessus, la colonne id est la clé primaire.

FOREIGN KEY (Clé étrangère) : Une colonne qui fait référence à une clé primaire dans une autre table, permettant de relier des tables entre elles dans une base de données relationnelle.

NOT NULL : oblige la présence d’une valeur lors de l’ajout d’une entrée

DEFAULT : permet de définir la valeur par défaut du champ si aucune valeur ne lui est attribuée

Gestion des tables

Création

Vous l’aurez donc compris, pour créer une table il faut donc plusieurs informations dont le nom et la liste des champs avec leurs paramètres.

Voici comment créer une table:

CREATE TABLE pokedex (
     id INT PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     type_first VARCHAR(255) NOT NULL,
     type_second VARCHAR(255),
     generation INT DEFAULT 0
);

Suppression

Pour supprimer une table:

DROP TABLE pokedex;

Si vous ne souhaitez supprimer que son contenu:

TRUNCATE TABLE pokedex;

Edition

Vous aurez certainement besoin d’ajouter des champs dans votre table.

Il suffit de spécifier la table (pokedex), le nom de votre champ (category) et son type (VARCHAR).

ALTER TABLE pokedex ADD category VARCHAR;

Et si, comme moi, voir des champs dans le désordre vous dérange, vous pouvez spécifier après quel champ il faut l’ajouter grâce au paramètre AFTER.

ALTER TABLE pokedex ADD description TEXT AFTER name;

Vous changez d’avis et souhaitez renommer une colonne?

ALTER TABLE pokedex RENAME type_first TO type_one;

Vous souhaitez renommer la table?

ALTER TABLE pokedex RENAME TO pokemon;

Bien évidemment, il est possible de supprimer une colonne

ALTER TABLE pokedex DROP COLUMN category;

Effectuer des requêtes

La requête la plus simple consiste à récupérer toutes les données d’une table.

SELECT *
FROM pokedex;

SELECT permet de spécifier les champs que l’on veut récupérer. Le “*” sert de joker permettant de cibler tous les champs sans avoir besoin de les spécifier un par un.

FROM sert à désigner la table dans laquelle nous allons récupérer les données.

Si vous ne souhaitez récupérer que certaine donnée, il suffit de modifier la valeur du SELECT en spécifiant le nom des champs

SELECT id, name
FROM pokedex;

Alias

Jusqu'à présent, nous n'utilisons qu’une seule table donc pas de risque de conflit.
Mais avant de voir les liaisons, il faut comprendre le fonctionnement des alias.
Imaginons que vous souhaitiez récupérer le champ “name” de la table “pokedex” et, dans la même requête, le champ “name” de la table “game”.

SELECT name, name

…inutile de vous dire que ca ne peut pas marcher

Il est donc nécessaire d’utiliser des alias pour différencier les colonnes portant le même nom dans des tables différentes.

Un alias permet de renommer temporairement une table ou une colonne dans une requête, ce qui facilite la lecture et évite les conflits de noms.

SELECT p.name AS pokemon_name, g.name AS game_name

WHERE

Réaliser une requête sur la base de donnée c’est bien mais cibler les entrées que l’on souhaite c’est mieux. Et pour ce faire, vous pouvez accompagner vos requetes SELECT, UPDATE et DELETE avec une condition représentée par le terme WHERE

SELECT column1, column2
FROM table_name
WHERE condition;

Comparaison simple

Le plus simple est de comparer la valeur d’un champ avec une valeur donnée.

SELECT name, type_first
FROM pokedex
WHERE type_first = 'Psy';

Comparaison multiple

Lorsque vous avez de grande quantitée de donnée, il est peu probable qu’une seule condition suffise et dans ce cas, il suffit de combiner vos conditions et pour ce faire, deux solutions:

AND est utilisé pour combiner des conditions qui doivent être toutes vraies.

OR est utilisé pour combiner des conditions dont au moins une doit être vraie.

Exemple avec AND : Récupérer les Pokémon de type "Psy" qui font partie de la première génération :

SELECT name, type_first, generation
FROM pokedex
WHERE type_first = 'Psy' AND generation = 1;

Cela retournera Mewtwo et Mew, car ils appartiennent à la génération 1 et sont de type Psy.

Exemple avec OR : Récupérer les Pokémon de type "Psy" ou ceux de la génération 2 :

SELECT name, type_first, generation
FROM pokedex
WHERE type_first = 'Psy' OR generation = 2;

Cette requête retournera tous les Pokémon de type "Psy" ainsi que ceux de la génération 2, qu'ils soient de type Psy ou non.

Plages de valeurs

La condition BETWEEN permet de sélectionner des valeurs comprises dans une plage donnée, comme des nombres, des dates ou des textes.

Exemple : Sélectionner les Pokémon ayant un id compris entre 151 et 251 :

SELECT name, id
FROM pokedex
WHERE id BETWEEN 151 AND 251;

Cette requête retournera tous les Pokémon dont les identifiants sont compris dans cette plage, soit de Mew à Celebi.

Recherche dans une liste de valeurs

La condition IN permet de vérifier si une valeur correspond à l'une des valeurs présentes dans une liste.

Exemple : Sélectionner les Pokémon dont le type est soit "Feu", soit "Eau" :

SELECT name, type_first
FROM pokedex
WHERE type_first IN ('Feu', 'Eau');

Cela retournera Sulfura (Feu) et Kyogre (Eau), car leurs types sont dans la liste spécifiée.

Correspondance

Dans certains cas, vous ne voulez pas chercher des entrées sur base d’une valeur définie mais plutôt d’une valeur approximative comme un mot dans un texte ou les premières lettres d’un nom.

La condition LIKE est utilisée pour effectuer des recherches de texte avec des motifs, en utilisant les caractères joker :

% : Remplace zéro ou plusieurs caractères.

_ : Remplace un seul caractère.

Exemple : Sélectionner les Pokémon dont le nom commence par "L" :

SELECT name
FROM pokedex
WHERE name LIKE 'L%';

Cela retournera Lugia, car son nom commence par "L".

Négations

Vous pouvez utiliser NOT pour exclure certains résultats.

Exemple : Sélectionner les Pokémon qui ne sont pas de type "Vol" :

SELECT name, type_first
FROM pokedex
WHERE type_first != 'Vol';

Ou de manière équivalente avec NOT et une liste :

SELECT name, type_first
FROM pokedex
WHERE type_first NOT IN ('Vol');

Cette requête retournera les Pokémon qui n'ont pas "Vol" comme type principal.

Valeurs NULL

Dans SQL, NULL représente une valeur inconnue ou manquante. Pour vérifier si une colonne est NULL, on utilise IS NULL ou IS NOT NULL.

Exemple : Sélectionner les Pokémon qui n'ont pas de type second (c'est-à-dire dont type_second est NULL) :

SELECT name
FROM pokedex
WHERE type_second IS NULL;

Cela retournera les Pokémon comme Mewtwo, Mew, Kyogre, et Groudon, car ils n'ont pas de type secondaire.

ORDER

Par défaut, les résultats seront triés par ordre d’insertion.

Si vous souhaitez les trier d’une autre manière, vous pouvez utiliser ORDER BY suivit du nom du champ sur lequel doit se baser le tri et ajouter ASC (ordre croissant) ou DESC (ordre décroissant)

SELECT name
FROM pokedex
ORDER BY name ASC;

LIMIT

Il n’est pas toujours nécessaire de récupérer l'entièreté des résultats.

Vous pouvez limiter le nombre de résultat de cette manière:

SELECT *
FROM pokedex
LIMIT 3;

OFFSET

Imaginons que vous souhaiteriez obtenir ensuite les 3 suivants, OFFSET permet de définir un décalage de départ

SELECT *
FROM pokedex
LIMIT 3 OFFSET 3;

DISTINCT

Si vous désirez ne récupérer que des valeurs différentes voici comment faire:

SELECT DISTINCT device
FROM game;

Jointure

Les jointures sont utilisées pour combiner des données provenant de plusieurs tables dans une base de données relationnelle. Nous allons nous servir de nos deux tables d’exemple (pokedex et game) pour mieux comprendre les différentes jointures.

Inner Join

L'inner join permet de récupérer les enregistrements qui ont des correspondances dans les deux tables. Cela signifie que seules les lignes qui ont des valeurs correspondantes dans les deux tables seront renvoyées.

Exemple : Obtenons les noms des Pokémon et les jeux dans lesquels ils apparaissent en fonction de leur génération :

SELECT p.name AS pokemon_name, g.name AS game_name
FROM pokedex p
INNER JOIN game g ON p.generation = g.generation;

Dans cet exemple, seules les générations correspondant aux deux tables seront affichées. Par exemple, Artikodin (génération 1) apparaîtra avec les jeux "Rouge", "Bleu", "Vert", et "Jaune".

Left Join

Le left join renvoie toutes les lignes de la table de gauche (ici, pokedex), même si aucune correspondance n’est trouvée dans la table de droite (game). Si aucune correspondance n’est trouvée, les colonnes de la table de droite contiendront des valeurs NULL.

Exemple : Obtenons les noms des Pokémon et les jeux, même si certains Pokémon n'apparaissent dans aucun jeu :

SELECT p.name AS pokemon_name, g.name AS game_name
FROM pokedex p
LEFT JOIN game g ON p.generation = g.generation;

Les Pokémon de la génération 3, comme Kyogre, Rayquaza et Groudon, apparaîtront, même s'ils ne sont pas présents dans les jeux des premières générations.

Right Join

Le right join est similaire au left join, sauf qu'il renvoie toutes les lignes de la table de droite (ici, game), même si aucune correspondance n’est trouvée dans la table de gauche (pokedex).

Exemple : Obtenons tous les jeux et les Pokémon correspondants, même si certains jeux ne contiennent pas de Pokémon :

SELECT p.name AS pokemon_name, g.name AS game_name
FROM pokedex p
RIGHT JOIN game g ON p.generation = g.generation;

Full Join

Le full join renvoie toutes les lignes des deux tables, même si aucune correspondance n’est trouvée. Les colonnes sans correspondance dans l’autre table contiendront des NULL.

Exemple : Obtenons une liste complète des Pokémon et des jeux, qu’il y ait ou non une correspondance :

SELECT p.name AS pokemon_name, g.name AS game_name
FROM pokedex p
FULL JOIN game g ON p.generation = g.generation;

Ce type de jointure est utile pour voir toutes les données des deux tables, même si elles ne sont pas directement liées par une génération commune.

Modification des données

Maintenant que l’on sait lire dans notre table, il serait bien de pouvoir y écrire.
Que se soit pour y ajouter une nouvelle entrée ou pour éditer une entrée existante.

Insertion de données

Pour ajouter des enregistrements dans une table, nous utilisons la commande INSERT INTO.

Exemple : Ajoutons un nouveau Pokémon à la table pokedex :

INSERT INTO pokedex (id, name, type_first, type_second, generation)
VALUES (385, 'Jirachi', 'Acier', 'Psy', 3);

Ce script ajoute Jirachi, un Pokémon de la génération 3 avec les types Acier et Psy.

Mise à jour de données

La commande UPDATE permet de modifier des données existantes dans une table.

Exemple : Mettons à jour le type second de Mewtwo, en ajoutant un type Combat :

UPDATE pokedex
SET type_second = 'Combat'
WHERE name = 'Mewtwo';

Ici, Mewtwo aura désormais un second type (Combat), en plus de son type Psy.

Suppression de données

La commande DELETE est utilisée pour supprimer des enregistrements d'une table.

Exemple : Supprimons un Pokémon de la table pokedex :

DELETE FROM pokedex
WHERE name = 'Kyogre';

Cela supprimera Kyogre de la table pokedex.