Lernen Sie SQL mit diesen 5 einfachen Rezepten

SQL (Structured Query Language) ist eine leistungsstarke und ausdrucksstarke Sprache für den Umgang mit Daten aus relationalen Datenbanken. Aber es kann den Uneingeweihten entmutigend erscheinen.

Die "Rezepte", die ich heute mit Ihnen teilen werde, sind einige grundlegende Beispiele aus einer einfachen Datenbank. Die Muster, die Sie hier lernen, können Ihnen jedoch dabei helfen, präzise Abfragen zu schreiben. Dadurch fühlen Sie sich in kürzester Zeit wie das Datenäquivalent eines MasterChef.

Hinweis zur Syntax: Die meisten der folgenden Abfragen werden in dem für PostgreSQL verwendeten Stil über die psql-Befehlszeile geschrieben. Verschiedene SQL-Engines können leicht unterschiedliche Befehle verwenden.

Die meisten der folgenden Abfragen sollten in den meisten Engines ohne Optimierung funktionieren, obwohl bei einigen Engines oder GUI-Tools möglicherweise Anführungszeichen um Tabellen- und Spaltennamen weggelassen werden müssen.

Gericht 1: Gibt alle Benutzer zurück, die innerhalb eines bestimmten Datumsbereichs erstellt wurden

Zutaten

  • WÄHLEN
  • VON
  • WO
  • UND

Methode

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

Dieses einfache Gericht ist ein vielseitiges Grundnahrungsmittel. Hier kehren wir Benutzer zurück, die zwei bestimmte Bedingungen erfüllen, indem sie die WHEREBedingungen mit einer ANDAnweisung verketten . Wir können dies mit weiteren ANDAussagen weiter ausbauen .

Während sich das Beispiel hier auf einen bestimmten Datumsbereich bezieht, erfordern die meisten Abfragen eine Bedingung, um die Daten sinnvoll zu filtern.

Gericht 2: Hier finden Sie alle Kommentare zu einem Buch, einschließlich des Benutzers, der den Kommentar abgegeben hat

(Neue) Zutaten

  • BEITRETEN

Methode

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

Diese Abfrage nimmt die folgende Tabellenstruktur an:

Eines der Dinge, die Anfänger mit SQL verwechseln können, ist die Verwendung von JOINs, um Daten aus zugeordneten Tabellen zu finden.

Das obige ERD (Entity Relationship Diagram) zeigt drei Tabellen: Benutzer, Bücher und Kommentare sowie deren Zuordnungen.

Jede Tabelle idist fett gedruckt, um anzuzeigen, dass es sich um den Primärschlüssel für die Tabelle handelt. Dieser Primärschlüssel ist immer ein eindeutiger Wert und wird verwendet, um Datensätze in Tabellen voneinander zu unterscheiden.

Die kursiven Spaltennamen userIdund bookIdin der Kommentartabelle sind Fremdschlüssel. Dies bedeutet, dass sie der Primärschlüssel in anderen Tabellen sind und hier verwendet werden, um auf diese Tabellen zu verweisen.

Die Konnektoren in der obigen ERD zeigen auch die Art der Beziehungen zwischen den 3 Tabellen.

Das Einzelpunktende am Connector bedeutet "Eins" und das geteilte Ende am Connector bedeutet "Viele". Die Benutzertabelle hat also eine "Eins-zu-Viele" -Beziehung zur Kommentartabelle.

Ein Benutzer kann beispielsweise viele Kommentare haben, aber ein Kommentar kann nur einem einzelnen Benutzer gehören. Bücher und Kommentare haben im obigen Diagramm dieselbe Beziehung.

Die SQL-Abfrage sollte basierend auf dem, was wir jetzt wissen, sinnvoll sein. Wir geben nur die benannten Spalten zurück, dh die Kommentarspalte aus der Kommentartabelle und den Benutzernamen aus der zugeordneten Benutzertabelle (basierend auf dem referenzierten Fremdschlüssel). Im obigen Beispiel beschränken wir die Suche auf ein einzelnes Buch, das wiederum auf dem Fremdschlüssel in der Kommentartabelle basiert.

Gericht 3: Zählen Sie die Anzahl der von jedem Benutzer hinzugefügten Kommentare

(Neue) Zutaten

  • ANZAHL
  • WIE
  • GRUPPIERE NACH

Methode

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

Diese kleine Abfrage macht ein paar interessante Dinge. Am einfachsten zu verstehen ist die ASAussage. Auf diese Weise können wir Spalten in den zurückgegebenen Daten willkürlich und vorübergehend umbenennen. Hier benennen wir die abgeleitete Spalte um, aber es ist auch nützlich, wenn Sie mehrere idSpalten haben, da Sie sie wie userIdoder commentIdso umbenennen können .

Die COUNTAnweisung ist eine SQL-Funktion, die erwartungsgemäß Dinge zählt. Hier zählen wir die Anzahl der Kommentare, die einem Benutzer zugeordnet sind. Wie funktioniert es? Nun, das GROUP BYist die wichtige letzte Zutat.

Stellen wir uns kurz eine etwas andere Abfrage vor:

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

Beachten Sie, kein Zählen oder Gruppieren. Wir wollen nur jeden Kommentar und wer hat ihn gemacht.

Die Ausgabe könnte ungefähr so ​​aussehen:

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