Naučite SQL s ovih 5 jednostavnih recepata

Programiranje

SQL (strukturirani jezik upita) moćan je i izražajan jezik za obradu podataka iz relacijskih baza podataka. No, neupućenima se može učiniti zastrašujućim.

"Recepti" koje ću danas podijeliti s vama osnovni su primjeri iz jednostavne baze podataka. No obrasci koje ćete ovdje naučiti mogu vam pomoći da napišete precizne upite. Zbog njih ćete se začas osjećati poput podataka ekvivalentnih MasterChefu.

Napomena o sintaksi: Većina upita u nastavku napisana je u stilu koji se koristi za PostgreSQL iz naredbenog retka psql. Različiti SQL motori mogu koristiti malo drugačije naredbe.

Većina upita u nastavku trebala bi raditi u većini strojeva bez dotjerivanja, iako bi neki motori ili GUI alati mogli zahtijevati izostavljanje navodnika oko imena tablica i stupaca.

Jelo 1: Vrati sve korisnike stvorene u određenom datumskom rasponu

Sastojci

  • ODABERI
  • IZ
  • GDJE
  • I

Metoda

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

Ovo jednostavno jelo svestrana je glavna namirnica. Ovdje vraćamo korisnike koji ispunjavaju dva posebna uvjeta stavljanjem WHEREuvjeta u ANDizjavu. To možemo dalje proširiti s više ANDizjava.

Iako se primjer ovdje odnosi na određeni datumski raspon, većina upita zahtijeva nekakav uvjet za korisno filtriranje podataka.

Dish 2: Pronađite sve komentare za knjigu, uključujući korisnika koji je dao komentar

(Novo) Sastojci

  • PRIDRUŽITI

Metoda

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

Ovaj upit pretpostavlja sljedeću strukturu tablice:

Jedna od stvari koja početnike može zbuniti s SQL-om je upotreba JOIN-ova za pronalaženje podataka iz pridruženih tablica.

Gore navedeni ERD (dijagram odnosa entiteta) prikazuje tri tablice, Korisnici, Knjige i Komentari i njihove asocijacije.

Svaka tablica ima idšto je podebljano u dijagramu pokazati da je primarni ključ za tablicu. Ovaj primarni ključ uvijek je jedinstvena vrijednost i koristi se za razdvajanje zapisa u tablicama.

U italic nazivi stupaca userIdi bookIdu tablici Komentari su strani ključevi, što znači da su primarni ključ u ostalim tablicama i ovdje se koriste za označavanje tih tablica.

Konektori u ERD-u gore također pokazuju prirodu odnosa između 3 tablice.

Kraj jedne točke na konektoru znači "jedan", a podijeljeni kraj na konektoru znači "mnogo", tako da korisnička tablica ima odnos "jedan prema više" s tablicom komentara.

Na primjer, korisnik može imati mnogo komentara, ali komentar može pripadati samo jednom korisniku. Knjige i komentari imaju isti odnos na gornjem dijagramu.

SQL upit trebao bi imati smisla na temelju onoga što sada znamo. Vraćamo samo imenovane stupce, tj. Stupac komentara iz tablice Komentari i korisničko ime iz pridružene tablice Korisnici (na temelju referenciranog stranog ključa). U gornjem primjeru ograničavamo pretraživanje na jednu knjigu, opet na temelju stranog ključa u tablici Komentari.

Jelo 3: Prebrojite broj komentara koje je dodao svaki korisnik

(Novo) Sastojci

  • RAČUNATI
  • KAO
  • GRUPIRAJ PO

Metoda

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

Ovaj mali upit čini nekoliko zanimljivih stvari. Najlakše je razumjeti ASizjavu. To nam omogućuje proizvoljno i privremeno preimenovanje stupaca u podacima koji se vraćaju. Ovdje preimenujemo izvedeni stupac, ali je korisno i ako imate više idstupaca, jer im možete preimenovati stvari poput userIdili commentIdi slično.

COUNTIzjava je SQL funkcija, kao što biste očekivali, broji stvari. Ovdje računamo broj komentara povezanih s korisnikom. Kako radi? Pa to GROUP BYje važan završni sastojak.

Zamislimo ukratko malo drugačiji upit:

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

Obavijest, nema brojanja ili grupiranja. Samo želimo svaki komentar i tko ga je dao.

Izlaz bi mogao izgledati otprilike ovako:

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

Sad zamislite da smo htjeli prebrojati Jacksonove i Quincyjeve komentare - ovdje ih je lako vidjeti na prvi pogled, ali teže s većim skupovima podataka kao što možete zamisliti.

GROUP BYIzjava u biti govori upit za liječenje sve jacksonrekorde kao jedne skupine, a sve quincyzapise kao drugi. Zatim COUNTfunkcija broji zapise u toj grupi i vraća tu vrijednost:

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

Jelo 4: Pronađite korisnike koji nisu dali komentar

(Novo) Sastojci

  • LIJEVA PRIDRUŽITE
  • JE NIŠTA

Metoda

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

Razni udruživanja mogu postati vrlo zbunjujuća, pa ih ovdje neću raspakirati. Ovdje postoji izvrsna raščlamba njih: Vizualni prikazi SQL pridruživanja, koji također objašnjavaju neke razlike u sintaksi između različitih okusa ili SQL-a.

Zamislimo brzo alternativnu verziju ovog upita:

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.