Apprenez SQL avec ces 5 recettes faciles

SQL (Structured Query Language) est un langage puissant et expressif pour traiter les données des bases de données relationnelles. Mais cela peut sembler décourageant pour les non-initiés.

Les «recettes» que je vais partager avec vous aujourd'hui sont quelques exemples de base tirés d'une simple base de données. Mais les modèles que vous apprendrez ici peuvent vous aider à rédiger des requêtes précises. Celles-ci vous donneront l'impression d'être l'équivalent en données d'un MasterChef en un rien de temps.

Remarque sur la syntaxe: la plupart des requêtes ci-dessous sont écrites dans le style utilisé pour PostgreSQL à partir de la ligne de commande psql. Différents moteurs SQL peuvent utiliser des commandes légèrement différentes.

La plupart des requêtes ci-dessous devraient fonctionner dans la plupart des moteurs sans modification, bien que certains moteurs ou outils d'interface graphique puissent nécessiter l'omission de guillemets autour des noms de table et de colonne.

Plat 1: renvoie tous les utilisateurs créés dans une plage de dates particulière

Ingrédients

  • SÉLECTIONNER
  • DE
  • ET

Méthode

SELECT * FROM "Users" WHERE "created_at" > "2020-01-01" AND "created_at" < "2020-02-01";

Ce plat simple est un aliment de base polyvalent. Ici, nous renvoyons les utilisateurs qui remplissent deux conditions particulières en enchaînant les WHEREconditions avec une ANDinstruction. Nous pouvons étendre cela plus loin avec plus de ANDdéclarations.

Bien que l'exemple ici concerne une plage de dates spécifique, la plupart des requêtes nécessitent une sorte de condition pour filtrer les données de manière utile.

Plat 2: rechercher tous les commentaires d'un livre, y compris l'utilisateur qui a fait le commentaire

(Nouveau) Ingrédients

  • JOINDRE

Méthode

SELECT "Comments"."comment", "Users"."username" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" WHERE "Comments"."bookId" = 1;

Cette requête suppose la structure de table suivante:

L'une des choses qui peut commencer à confondre les novices avec SQL est l'utilisation de JOINs pour trouver des données à partir des tables associées.

L'ERD (Entity Relationship Diagram) ci-dessus montre trois tableaux, Utilisateurs, Livres et Commentaires, et leurs associations.

Chaque table a un idqui est en gras dans le diagramme pour montrer qu'il s'agit de la clé primaire de la table. Cette clé primaire est toujours une valeur unique et est utilisée pour distinguer les enregistrements des tables.

Les noms de colonne en italiqueuserId et bookIddans la table Commentaires sont des clés étrangères, ce qui signifie qu'elles sont la clé primaire dans d'autres tables et sont utilisées ici pour référencer ces tables.

Les connecteurs dans la DRE ci-dessus montrent également la nature des relations entre les 3 tables.

L'extrémité de point unique sur le connecteur signifie «un» et l'extrémité divisée sur le connecteur signifie «plusieurs», de sorte que la table User a une relation «un à plusieurs» avec la table de commentaires.

Un utilisateur peut avoir de nombreux commentaires, par exemple, mais un commentaire ne peut appartenir qu'à un seul utilisateur. Les livres et les commentaires ont la même relation dans le diagramme ci-dessus.

La requête SQL devrait avoir un sens sur la base de ce que nous savons maintenant. Nous ne renvoyons que les colonnes nommées, c'est-à-dire la colonne de commentaire de la table Comments et le nom d'utilisateur de la table Users associée (en fonction de la clé étrangère référencée). Dans l'exemple ci-dessus, nous limitons la recherche à un seul livre, toujours en fonction de la clé étrangère dans la table Commentaires.

Plat 3: Comptez le nombre de commentaires ajoutés par chaque utilisateur

(Nouveau) Ingrédients

  • COMPTER
  • COMME
  • PAR GROUPE

Méthode

SELECT "Users"."username", COUNT("Comments"."id") AS "CommentCount" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" GROUP BY "Users"."id";

Cette petite requête fait quelques choses intéressantes. Le plus simple à comprendre est l' ASénoncé. Cela nous permet de renommer arbitrairement et temporairement les colonnes des données renvoyées. Ici , on renomme la colonne dérivée, mais il est également utile lorsque vous avez plusieurs idcolonnes, puisque vous pouvez les renommer des choses comme userIdou commentIdet ainsi de suite.

L' COUNTinstruction est une fonction SQL qui, comme vous vous en doutez, compte les choses. Ici, nous comptons le nombre de commentaires associés à un utilisateur. Comment ça marche? Eh bien, GROUP BYc'est l'ingrédient final important.

Imaginons brièvement une requête légèrement différente:

SELECT "Users"."username", "Comments"."comment" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id";

Remarquez, pas de comptage ni de regroupement. Nous voulons juste chaque commentaire et qui l'a fait.

La sortie pourrait ressembler à ceci:

|----------|-----------------------------| | username | comment | |----------|-----------------------------| | jackson | it's good, I liked it | | jackson | this was ok, not the best | | quincy | excellent read, recommended | | quincy | not worth reading | | quincy | I haven't read this yet | ------------------------------------------

Now imagine we wanted to count Jackson's and Quincy's comments - easy to see at a glance here, but harder with a larger dataset as you can imagine.

The GROUP BY statement essentially tells the query to treat all the jackson records as one group, and all the quincy records as another. The COUNT function then counts the records in that group and returns that value:

|----------|--------------| | username | CommentCount | |----------|--------------| | jackson | 2 | | quincy | 3 | ---------------------------

Dish 4: Find users that have not made a comment

(New) Ingredients

  • LEFT JOIN
  • IS NULL

Method

SELECT "Users"."username" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId" WHERE "Comments"."id" IS NULL;

The various joins can get very confusing, so I won't unpack them here. There is an excellent breakdown of them here: Visual Representations of SQL Joins, which also accounts for some of the syntax differences between various flavours or SQL.

Let's imagine an alternate version of this query quickly:

SELECT "Users"."username", "Comments"."id" AS "commentId" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId";

We still have the LEFT JOIN but we've added a column and removed the WHERE clause.

The return data might look something like this:

|----------|-----------| | username | commentId | |----------|-----------| | jackson | 1 | | jackson | 2 | | quincy | NULL | | abbey | 3 | ------------------------

So Jackson is responsible for comments 1 and 2, Abbey for 3, and Quincy has not commented.

The difference between a LEFT JOIN and an INNER JOIN (what we've been calling just a JOIN until now, which is valid) is that the inner join only shows records where there are values for both tables. A left join, on the other hand, returns everything from the first, or left, table (the FROM one) even if there is nothing in the right table. An inner join would therefore only show the records for Jackson and Abbey.

Now that we can visualize what the LEFT JOIN returns, it's easier to reason about what the WHERE...IS NULL part does. We return only those users where the commentId is a null value, and we don't actually need the null value column included in the output, hence its original omission.

Dish 5: List all comments added by each user in a single field, pipe separated

(New) Ingredients

  • GROUP_CONCAT or STRING_AGG

Method (MySQL)

SELECT "Users"."username", GROUP_CONCAT("Comments"."comment" SEPARATOR " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

Method (Postgresql)

SELECT "Users"."username", STRING_AGG("Comments"."comment", " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

This final recipe shows a difference in syntax for a similar function in two of the most popular SQL engines.

Here is a sample output we might expect:

|----------|---------------------------------------------------| | username | comments | |----------|---------------------------------------------------| | jackson | it's good, I liked it | this was ok, not the best | | quincy | excellent read, recommended | not worth reading | ----------------------------------------------------------------

We can see here that the comments have been grouped and concatenated / aggregated, that is joined together in a single record field.

BonAppetit

Now that you have some SQL recipes to fall back on, get creative and serve up your own data dishes!

I like to think of WHERE, JOIN, COUNT, GROUP_CONCAT as the Salt, Fat, Acid, Heat of database cooking. Once you know what you're doing with these core elements, you are well on your way to mastery.

If this has been a useful collection, or you have other favourite recipes to share, drop me a comment or follow on Twitter: @JacksonBates.