Научите СКЛ са ових 5 једноставних рецепата

СКЛ (Струцтуред Куери Лангуаге) је моћан и изражајан језик за обраду података из релационих база података. Али неупућенима може изгледати застрашујуће.

„Рецепти“ које ћу данас поделити са вама су неки основни примери из једноставне базе података. Али обрасци које ћете овде научити могу вам помоћи да напишете прецизне упите. Због њих ћете се зачас осећати као подаци еквивалентни МастерЦхефу.

Напомена о синтакси: Већина упита у наставку написана је у стилу који се користи за ПостгреСКЛ из командне линије пскл. Различити СКЛ мотори могу користити мало другачије наредбе.

Већина упита у наставку требало би да функционише у већини мотора без прилагођавања, иако би неки мотори или ГУИ алати могли захтевати изостављање наводника око имена табела и ступаца.

Јело 1: Врати све кориснике створене у одређеном временском распону

Састојци

  • СЕЛЕЦТ
  • ОД
  • ГДЕ
  • И

Метод

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

Ово једноставно јело је свестрана основна храна. Овде враћамо кориснике који испуњавају два одређена услова укидањем WHEREуслова ANDизјавом. То можемо даље проширити са више ANDизјава.

Иако се пример овде односи на одређени период, већина упита захтева неку врсту услова да би се подаци корисно филтрирали.

Дисх 2: Пронађите све коментаре за књигу, укључујући корисника који је дао коментар

(Ново) Састојци

  • ПРИДРУЖИТИ

Метод

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

Овај упит претпоставља следећу структуру табеле:

Једна од ствари која може почети да збуњује почетнике са СКЛ-ом је употреба ЈОИН-ова за проналажење података из придружених табела.

Горе наведени ЕРД (Ентити Релатионсхип Диаграм) приказује три табеле, Корисници, Књиге и Коментари и њихове асоцијације.

Свака табела хас ан idкоји је храбар у дијаграму да покаже да је примарни кључ за столом. Овај примарни кључ је увек јединствена вредност и користи се за раздвајање записа у табелама.

Тхе италиц имена колона userIdи bookIdу коментарима табели су страни кључеви, што значи да су примарни кључ у другим табелама и овде се користи као референца те табеле.

Конектори у ЕРД-у горе такође показују природу односа између 3 табеле.

Крај једне тачке на конектору значи „један“, а раздвојени крај на конектору значи „много“, па корисничка табела има везу „један према више“ са табелом коментара.

На пример, корисник може имати много коментара, али коментар може припадати само једном кориснику. Књиге и коментари имају исти однос на горњем дијаграму.

СКЛ упит треба да има смисла на основу онога што сада знамо. Враћамо само именоване колоне, односно колону коментара из табеле Коментари и корисничко име из повезане табеле Корисници (на основу референцираног страног кључа). У горњем примеру ограничавамо претрагу на једну књигу, опет засновану на страном кључу у табели Коментари.

Јело 3: Пребројите број коментара које је додао сваки корисник

(Ново) Састојци

  • ЦОУНТ
  • КАО
  • ГРУПА ОД

Метод

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

This little query does a few interesting things. The easiest to understand is the AS statement. This allows us to arbitrarily, and temporarily, rename columns in the data that gets returned. Here we rename the derived column, but it's also useful when you have multiple id columns, since you can rename them things like userId or commentId and so on.

The COUNT statement is a SQL function that, as you'd expect, counts things. Here we count the number of comments associated with a user. How does it work? Well the GROUP BY is the important final ingredient.

Let's briefly imagine a slightly different query:

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

Notice, no counting or grouping. We just want each comment and who made it.

The output might look something like this:

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

Сад кад имате неке СКЛ рецепте на које се можете вратити, будите креативни и послужите своја јела!

Волим да мислим о WHERE, JOIN, COUNT, GROUP_CONCATкао Салт, масти, киселина, висока кувања базе података. Једном када знате шта радите са овим основним елементима, на добром сте путу да савладате.

Ако је ово била корисна колекција или имате друге омиљене рецепте за дељење, додајте ми коментар или пратите на Твиттер-у: @ЈацксонБатес.