Tanuljon meg SQL-t az 5 egyszerű recept segítségével

Az SQL (Structured Query Language) egy erős és kifejező nyelv a relációs adatbázisokból származó adatok kezelésére. De ijesztőnek tűnhet az avatatlanok számára.

A "receptek", amelyeket ma megosztok veletek, néhány egyszerű példa egy egyszerű adatbázisból. De az itt megismert minták segíthetnek pontos lekérdezések megírásában. Ezekkel pillanatok alatt úgy érzi magát, mint egy MasterChef adategyenértéke.

Megjegyzés a szintaxissal kapcsolatban: Az alábbi lekérdezések nagy része a PostgreSQL-hez használt stílusban van megírva a psql parancssorból. A különböző SQL motorok kissé eltérő parancsokat használhatnak.

Az alábbi lekérdezések többségének a legtöbb motorban csípés nélkül kell működnie, bár egyes motorok vagy GUI-eszközök megkövetelhetik az idézőjelek kihagyását a táblázat és oszlop neve körül.

1. tál: Visszaadja az összes felhasználót, amelyet egy adott dátumtartományban hoztak létre

Hozzávalók

  • SELECT
  • TÓL TŐL
  • HOL
  • ÉS

Módszer

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

Ez az egyszerű étel sokoldalú vágott elem. Itt olyan visszatérő felhasználókat találunk, amelyek két feltételnek felelnek meg, ha a WHEREfeltételeket egy ANDnyilatkozattal láncolják . Ezt további ANDállításokkal bővíthetjük .

Míg az itt látható példa egy meghatározott dátumtartományra vonatkozik, a legtöbb lekérdezéshez valamilyen feltételre van szükség az adatok hasznos szűréséhez.

2. étel: Találja meg a könyv összes megjegyzését, beleértve azt a felhasználót is, aki megjegyzést tett

(Új) Hozzávalók

  • CSATLAKOZIK

Módszer

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

Ez a lekérdezés a következő táblázatszerkezetet feltételezi:

Az egyik dolog, ami összezavarhatja az újoncokat az SQL-szel, a JOIN-ok használata az adatok megkereséséhez a társított táblákból.

A fenti ERD (Entity Relationship Diagram) három táblázatot mutat: Felhasználók, Könyvek és Megjegyzések, valamint azok társításai.

Minden táblázat egy id, amely merész A diagram azt mutatja, hogy ez az elsődleges kulcsot az asztalra. Ez az elsődleges kulcs mindig egyedi érték, és a táblákban lévő rekordok megkülönböztetésére szolgál.

A dőlt oszlopnevek userIdés bookIda Megjegyzések táblázat idegen kulcsok, ami azt jelenti, hogy ezek az elsődleges kulcsok más táblákban, és itt ezekre a táblákra hivatkoznak.

A fenti ERD-n lévő csatlakozók a 3 tábla közötti kapcsolatok jellegét is mutatják.

A csatlakozó egyetlen pontjának vége azt jelenti, hogy „egy”, a csatlakozón lévő osztott vég pedig: „sok”, tehát a Felhasználói tábla „egy-a-sokhoz” viszonyban áll a Megjegyzések táblával.

A felhasználónak például sok megjegyzése lehet, de egy megjegyzés csak egyetlen felhasználóhoz tartozhat. A Könyvek és megjegyzések a fenti ábrán azonos kapcsolatban állnak.

Az SQL lekérdezésnek annak alapján kell értelmesnek lennie, amit most ismerünk. Csak a megnevezett oszlopokat, azaz a Megjegyzések táblázat megjegyzés oszlopát és a társított Felhasználók táblázat felhasználónévét adjuk vissza (a hivatkozott idegen kulcs alapján). A fenti példában a keresést egyetlen könyvre korlátozzuk, ismét a Megjegyzések táblázatban szereplő idegen kulcs alapján.

3. étel: Számolja meg az egyes felhasználók által hozzáadott megjegyzések számát

(Új) Hozzávalók

  • SZÁMOL
  • MINT
  • CSOPORTOSÍT

Módszer

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

Ez a kis lekérdezés néhány érdekes dolgot végez. A legkönnyebben érthető az ASállítás. Ez lehetővé teszi számunkra, hogy önkényesen és ideiglenesen átnevezzük az oszlopokat a visszaküldött adatokban. Itt nevezzük át a származtatott oszlopot, de akkor is hasznos, ha több idoszlop van, mivel átnevezheti őket például userIdvagy commentIdígy tovább.

Az COUNTutasítás egy SQL függvény, amely - amire számítani lehetett - megszámolja a dolgokat. Itt megszámoljuk a felhasználóhoz társított megjegyzések számát. Hogyan működik? Nos GROUP BYez a fontos végső összetevő.

Képzeljük el röviden egy kissé eltérő lekérdezést:

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

Figyelem, nincs számlálás vagy csoportosítás. Csak szeretnénk minden megjegyzést, és ki tette.

A kimenet így nézhet ki:

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