Aprenda SQL con estas 5 recetas sencillas

SQL (Structured Query Language) es un lenguaje poderoso y expresivo para tratar con datos de bases de datos relacionales. Pero puede parecer desalentador para los no iniciados.

Las "recetas" que voy a compartir con ustedes hoy son algunos ejemplos básicos de una base de datos simple. Pero los patrones que aprenderá aquí pueden ayudarlo a escribir consultas precisas. Estos te harán sentir como el equivalente en datos de un MasterChef en muy poco tiempo.

Una nota sobre la sintaxis: la mayoría de las consultas a continuación están escritas en el estilo utilizado para PostgreSQL desde la línea de comandos psql. Los diferentes motores SQL pueden usar comandos ligeramente diferentes.

La mayoría de las consultas a continuación deberían funcionar en la mayoría de los motores sin ajustes, aunque algunos motores o herramientas de GUI pueden requerir la omisión de comillas en los nombres de tablas y columnas.

Plato 1: Devuelve todos los usuarios creados dentro de un rango de fechas en particular

Ingredientes

  • SELECCIONE
  • DESDE
  • DÓNDE
  • Y

Método

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

Este sencillo plato es un alimento básico versátil. Aquí estamos regresando usuarios que cumplen dos condiciones particulares al encadenar las WHEREcondiciones con una ANDdeclaración. Podemos extender esto aún más con más ANDdeclaraciones.

Si bien el ejemplo aquí es para un rango de fechas específico, la mayoría de las consultas requieren algún tipo de condición para filtrar los datos de manera útil.

Plato 2: buscar todos los comentarios de un libro, incluido el usuario que hizo el comentario

(Nuevos) ingredientes

  • UNIRSE

Método

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

Esta consulta asume la siguiente estructura de tabla:

Una de las cosas que puede empezar a confundir a los principiantes con SQL es el uso de JOIN para buscar datos de tablas asociadas.

El ERD (diagrama de relación de entidades) anterior muestra tres tablas, usuarios, libros y comentarios, y sus asociaciones.

Cada tabla tiene una idque es negrita en el diagrama para mostrar que es la clave principal de la tabla. Esta clave principal es siempre un valor único y se utiliza para diferenciar los registros de las tablas.

Los nombres de las columnas en cursivauserId y bookIden la tabla Comentarios son claves externas, lo que significa que son la clave principal en otras tablas y se utilizan aquí para hacer referencia a esas tablas.

Los conectores en el ERD anterior también muestran la naturaleza de las relaciones entre las 3 tablas.

El extremo de un solo punto en el conector significa 'uno' y el extremo dividido en el conector significa 'muchos', por lo que la tabla de usuarios tiene una relación de 'uno a muchos' con la tabla de comentarios.

Un usuario puede tener muchos comentarios, por ejemplo, pero un comentario solo puede pertenecer a un único usuario. Los libros y los comentarios tienen la misma relación en el diagrama anterior.

La consulta SQL debería tener sentido según lo que sabemos ahora. Estamos devolviendo solo las columnas nombradas, es decir, la columna de comentarios de la tabla Comentarios y el nombre de usuario de la tabla Usuarios asociada (según la clave externa a la que se hace referencia). En el ejemplo anterior, restringimos la búsqueda a un solo libro, nuevamente basado en la clave externa en la tabla Comentarios.

Plato 3: cuente la cantidad de comentarios agregados por cada usuario

(Nuevos) ingredientes

  • CONTAR
  • COMO
  • AGRUPAR POR

Método

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

Esta pequeña consulta hace algunas cosas interesantes. La más fácil de entender es la ASdeclaración. Esto nos permite cambiar el nombre de las columnas de forma arbitraria y temporal en los datos que se devuelven. Aquí cambiamos el nombre de la columna derivada, pero también es útil cuando se tienen múltiples idcolumnas, ya que se puede cambiar el nombre de ellos las cosas como userIdo commentIdy así sucesivamente.

La COUNTdeclaración es una función SQL que, como era de esperar, cuenta cosas. Aquí contamos el número de comentarios asociados a un usuario. ¿Como funciona? Bueno, el GROUP BYes el ingrediente final importante.

Imaginemos brevemente una consulta ligeramente diferente:

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

Fíjate, sin contar ni agrupar. Solo queremos cada comentario y quién lo hizo.

La salida podría verse así:

|----------|-----------------------------| | 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.