
Cheatsheet 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:
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:
Les explications vont suivre mais si vous souhaitez avoir le script de génération des deux tables ci dessus, le voici:
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:
Suppression
Pour supprimer une table:
Si vous ne souhaitez supprimer que son contenu:
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).
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.
Vous changez d’avis et souhaitez renommer une colonne?
Vous souhaitez renommer la table?
Bien évidemment, il est possible de supprimer une colonne
Effectuer des requêtes
La requête la plus simple consiste à récupérer toutes les données d’une table.
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
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”.
…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.
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
Comparaison simple
Le plus simple est de comparer la valeur d’un champ avec une valeur donnée.
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 :
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 :
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 :
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" :
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" :
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" :
Ou de manière équivalente avec NOT et une liste :
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) :
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)
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:
OFFSET
Imaginons que vous souhaiteriez obtenir ensuite les 3 suivants, OFFSET permet de définir un décalage de départ
DISTINCT
Si vous désirez ne récupérer que des valeurs différentes voici comment faire:
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 :
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 :
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 :
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 :
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 :
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 :
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 :
Cela supprimera Kyogre de la table pokedex.