SQL (Structured Query Language) este un limbaj puternic și expresiv pentru tratarea datelor din bazele de date relaționale. Dar poate părea descurajant pentru cei neinițiați.

„Rețetele” pe care le voi împărtăși astăzi sunt câteva exemple de bază dintr-o bază de date simplă. Dar modelele pe care le veți învăța aici vă pot ajuta să scrieți interogări precise. Acestea vă vor face să vă simțiți ca echivalentul de date al unui MasterChef în cel mai scurt timp.

O notă despre sintaxă: Majoritatea interogărilor de mai jos sunt scrise în stilul folosit pentru PostgreSQL din linia de comandă psql. Diferite motoare SQL pot utiliza comenzi ușor diferite.

Majoritatea interogărilor de mai jos ar trebui să funcționeze în majoritatea motoarelor fără modificări, deși unele motoare sau instrumente GUI ar putea necesita omiterea ghilimelelor în jurul numelor tabelelor și coloanelor.

Dish 1: Returnează toți utilizatorii creați într-un anumit interval de date

Ingrediente

  • SELECTAȚI
  • DIN
  • UNDE
  • ȘI

Metodă

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

Acest fel de mâncare simplu este un produs de bază versatil. Aici ne întoarcem utilizatorii care îndeplinesc două condiții speciale prin înlănțuirea WHERE condiții cu un AND afirmație. Putem extinde acest lucru în continuare cu mai multe AND declarații.

În timp ce exemplul de aici este pentru un anumit interval de date, majoritatea interogărilor necesită un fel de condiție pentru a filtra datele în mod util.

Dish 2: Găsiți toate comentariile pentru o carte, inclusiv utilizatorul care a făcut comentariul

(Noi) Ingrediente

  • A TE ALATURA

Metodă

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

Această interogare presupune următoarea structură a tabelului:

Aflati SQL cu aceste 5 retete usoare
ERD afișează utilizatorilor care pot avea multe comentarii și cărți care pot avea, de asemenea, multe comentarii

Unul dintre lucrurile care pot începe să confunde novicii cu SQL este utilizarea JOIN-urilor pentru a găsi date din tabelele asociate.

ERD (Diagrama relației cu entitățile) de mai sus arată trei tabele, Utilizatori, cărți și comentarii și asociațiile acestora.

Fiecare masă are un id care este îndrăzneţ din diagramă pentru a arăta că este cheia primară pentru tabel. Această cheie primară este întotdeauna o valoare unică și este utilizată pentru a distinge înregistrările în tabele.

cursiv numele coloanelor userId și bookId în tabelul Comentarii sunt chei străine, ceea ce înseamnă că sunt cheia primară din alte tabele și sunt folosite aici pentru a face referire la aceste tabele.

Conectorii din ERD de mai sus arată, de asemenea, natura relațiilor dintre cele 3 tabele.

Capătul punctului unic al conectorului înseamnă „unul”, iar capătul împărțit al conectorului înseamnă „mulți”, astfel încât tabela Utilizator are o relație „unu-la-mulți” cu tabelul Comentarii.

Un utilizator poate avea multe comentarii, de exemplu, dar un comentariu poate aparține doar unui singur utilizator. Cărțile și comentariile au aceeași relație în diagrama de mai sus.

Interogarea SQL ar trebui să aibă sens pe baza a ceea ce știm acum. Întoarcem doar coloanele numite, adică coloana de comentarii din tabelul Comentarii și numele de utilizator din tabela Utilizatori asociați (pe baza cheii străine de referință). În exemplul de mai sus constrângem căutarea la o singură carte, din nou pe baza cheii străine din tabelul Comentarii.

Dish 3: Numărați numărul de comentarii adăugate de fiecare utilizator

(Noi) Ingrediente

  • NUMARA
  • LA FEL DE
  • A SE GRUPA CU

Metodă

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

Această mică interogare face câteva lucruri interesante. Cel mai ușor de înțeles este AS afirmație. Acest lucru ne permite să redenumim în mod arbitrar și temporar coloanele din datele care sunt returnate. Aici redenumim coloana derivată, dar este utilă și atunci când aveți mai multe id coloane, deoarece le puteți redenumi lucruri precum userId sau commentId si asa mai departe.

COUNT statement este o funcție SQL care, așa cum v-ați aștepta, contează lucrurile. Aici numărăm numărul de comentarii asociate cu un utilizator. Cum functioneazã? Ei bine GROUP BY este ingredientul final important.

Să ne imaginăm pe scurt o interogare ușor diferită:

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

Observați, fără numărare sau grupare. Vrem doar fiecare comentariu și cine a făcut-o.

Rezultatul ar putea arăta cam așa:

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

Acum imaginați-vă că am vrut să numărăm comentariile lui Jackson și Quincy – ușor de văzut dintr-o privire aici, dar mai greu cu un set de date mai mare, după cum vă puteți imagina.

GROUP BY declarația spune în esență interogării pentru a trata toate jackson înregistrează ca un singur grup și toate quincy înregistrări ca altul. COUNT funcția numără apoi înregistrările din acel grup și returnează acea valoare:

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

Dish 4: Găsiți utilizatori care nu au făcut niciun comentariu

(Noi) Ingrediente

  • ÎNREGISTRARE STÂNGA
  • ESTE NUL

Metodă

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

Diferitele îmbinări pot deveni foarte confuze, așa că nu le voi despacheta aici. Există o defalcare excelentă a acestora aici: Reprezentări vizuale ale îmbinărilor SQL, care explică, de asemenea, unele dintre diferențele de sintaxă dintre diferite arome sau SQL.

Să ne imaginăm rapid o versiune alternativă a acestei interogări:

SELECT "Users"."username", "Comments"."id" AS "commentId"
FROM "Users"
LEFT JOIN "Comments"
ON "Users"."id" = "Comments"."userId";

Mai avem LEFT JOIN dar am adăugat o coloană și am eliminat WHERE clauză.

Datele returnate ar putea arăta cam așa:

|----------|-----------|
| username | commentId |
|----------|-----------|
| jackson  | 1         |
| jackson  | 2         |
| quincy   | NULL      |
| abbey    | 3         |
------------------------

Deci Jackson este responsabil pentru comentariile 1 și 2, Abbey pentru 3 și Quincy nu a comentat.

Diferența dintre a LEFT JOIN si un INNER JOIN (ceea ce am numit doar un JOIN până acum, ceea ce este valabil) este că îmbinarea interioară arată numai înregistrări în care există valori pentru ambele tabele. O îmbinare la stânga, pe de altă parte, returnează totul de la prima, sau stânga, tabel ( FROM unul) chiar dacă nu este nimic în tabelul potrivit. Prin urmare, o îmbinare interioară ar arăta doar înregistrările pentru Jackson și Abbey.

Acum că putem vizualiza ceea ce LEFT JOIN se întoarce, este mai ușor să raționăm despre ceea ce WHERE...IS NULL parte face. Întoarcem doar acei utilizatori în care comentariul este o valoare nulă și nu avem nevoie de fapt de coloana cu valoare nulă inclusă în ieșire, de unde și omisiunea sa originală.

Dish 5: enumerați toate comentariile adăugate de fiecare utilizator într-un singur câmp, separat prin țevi

(Noi) Ingrediente

  • GROUP_CONCAT sau STRING_AGG

Metoda (MySQL)

SELECT "Users"."username", GROUP_CONCAT("Comments"."comment" SEPARATOR " | ") AS "comments"
FROM "Users"
JOIN "Comments"
ON "Users"."id" = "Comments"."userId"
GROUP BY "Users"."id";

Metoda (Postgresql)

SELECT "Users"."username", STRING_AGG("Comments"."comment", " | ") AS "comments"
FROM "Users"
JOIN "Comments"
ON "Users"."id" = "Comments"."userId"
GROUP BY "Users"."id";

Această rețetă finală arată o diferență de sintaxă pentru o funcție similară în două dintre cele mai populare motoare SQL.

Iată un exemplu de ieșire la care ne-am putea aștepta:

|----------|---------------------------------------------------|
| username | comments                                          |
|----------|---------------------------------------------------|
| jackson  | it's good, I liked it | this was ok, not the best |
| quincy   | excellent read, recommended | not worth reading   |
----------------------------------------------------------------

Putem vedea aici că comentariile au fost grupate și concatenate / agregate, adică unite într-un singur câmp de înregistrare.

Bine Apetit

Acum, că aveți câteva rețete SQL pe care să vă întoarceți, creați-vă și serviți-vă propriile feluri de mâncare de date!

Îmi place să mă gândesc la WHERE, JOIN, COUNT, GROUP_CONCAT dupa cum Sare, grăsimi, acizi, căldură de gătit în baze de date. Odată ce știi ce faci cu aceste elemente de bază, ești pe drumul spre stăpânire.

Dacă aceasta a fost o colecție utilă sau aveți alte rețete preferate de distribuit, trimiteți-mi un comentariu sau urmați pe Twitter: @JacksonBates.