Opi SQL näitä 5 helppoa reseptiä

SQL (Structured Query Language) on tehokas ja ilmeikäs kieli relaatiotietokannoista saatavien tietojen käsittelemiseksi. Mutta aloittelijoille se voi tuntua pelottavalta.

"Reseptit", jotka aion jakaa kanssasi tänään, ovat joitain perusesimerkkejä yksinkertaisesta tietokannasta. Mutta täältä opit mallit voivat auttaa sinua kirjoittamaan tarkkoja kyselyitä. Nämä saavat sinut tuntemaan itsesi MasterChefin vastaavaksi hetkessä.

Huomautus syntaksista: Suurin osa alla olevista kyselyistä kirjoitetaan tyylillä, jota käytetään PostgreSQL: lle psql-komentoriviltä. Eri SQL-moottorit voivat käyttää hieman erilaisia ​​komentoja.

Suurimman osan alla olevista kyselyistä pitäisi toimia useimmissa moottoreissa säätämättä, vaikka jotkut moottorit tai GUI-työkalut saattavat edellyttää lainausmerkkien jättämistä taulukkojen ja sarakkeiden nimien ympärille.

Ruokalaji 1: Palauta kaikki tietyllä ajanjaksolla luodut käyttäjät

Ainekset

  • VALITSE
  • Alkaen
  • MISSÄ
  • JA

Menetelmä

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

Tämä yksinkertainen ruokalaji on monipuolinen katkottu. Tässä olemme palaavia käyttäjiä, jotka täyttävät kaksi erityisehtoa ketjuamalla WHEREehdot ANDlausunnolla. Voimme laajentaa tätä edelleen uusilla ANDlausunnoilla.

Vaikka tässä esimerkki koskee tiettyä ajanjaksoa, useimmat kyselyt edellyttävät jonkinlaista ehtoa tietojen hyödylliseen suodattamiseen.

Ruokalaji 2: Etsi kaikki kirjan kommentit, mukaan lukien kommentin kirjoittanut käyttäjä

(Uusi) Ainekset

  • LIITTYÄ SEURAAN

Menetelmä

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

Tämä kysely olettaa seuraavan taulukon rakenteen:

Yksi asioista, jotka voivat alkaa sekoittaa aloittelijoita SQL: ään, on JOIN-tiedostojen käyttö tietojen löytämiseen liittyvistä taulukoista.

Yllä olevassa ERD: ssä (Entity Relationship Diagram) on kolme taulukkoa, Käyttäjät, Kirjat ja Kommentit sekä heidän assosiaatiot.

Jokaisessa taulukossa on id, joka on lihavoitu on kaavio, joka esittää, että se on ensisijainen avain taulukon. Tämä ensisijainen avain on aina ainutlaatuinen arvo, ja sitä käytetään taulukoiden tietueiden erottamiseen toisistaan.

Kursivoitu sarake nimet userIdja bookIdKommentit-taulukossa ovat ulkomaisia avaimet, mikä tarkoittaa, että ne ovat perusavain muiden taulukoiden ja niitä käytetään tässä viittaamaan näissä taulukoissa.

Yllä olevan ERD: n liittimet osoittavat myös kolmen taulukon välisten suhteiden luonteen.

Liittimen yhden pisteen pää tarkoittaa "yhtä" ja liittimen jaettu pää tarkoittaa "monia", joten käyttäjätaulukolla on "yksi moniin" suhde Kommentit-taulukkoon.

Käyttäjällä voi olla esimerkiksi useita kommentteja, mutta kommentti voi kuulua vain yhdelle käyttäjälle. Kirjoilla ja kommenteilla on sama suhde yllä olevassa kaaviossa.

SQL-kyselyn pitäisi olla järkevää sen perusteella, mitä tiedämme nyt. Palautamme vain nimetyt sarakkeet, ts. Kommentti-sarake Kommentit-taulukosta ja käyttäjänimi liitetystä Käyttäjät-taulukosta (viitatun vieraan avaimen perusteella). Yllä olevassa esimerkissä rajoitamme haun yhteen kirjaan, joka perustuu jälleen Kommentit-taulukon vieraan avaimeen.

Ruokalaji 3: Laske kunkin käyttäjän lisäämien kommenttien määrä

(Uusi) Ainekset

  • KREIVI
  • KUTEN
  • RYHMÄ

Menetelmä

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

Tämä pieni kysely tekee muutamia mielenkiintoisia asioita. Helpoin ymmärtää on ASlausunto. Tämän avulla voimme nimetä sarakkeet mielivaltaisesti ja väliaikaisesti palautettavissa olevissa tiedoissa. Tässä nimetään johdettu sarake uudelleen, mutta se on hyödyllinen myös silloin, kun sinulla on useita idsarakkeita, koska voit nimetä ne esimerkiksi userIdtai commentIdniin edelleen.

COUNTLausunto on SQL toiminto, kuten odottaa saattaa, laskee asioita. Tässä lasketaan käyttäjään liittyvien kommenttien määrä. Kuinka se toimii? No, se GROUP BYon tärkeä lopullinen ainesosa.

Kuvitellaan lyhyesti hieman erilainen kysely:

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

Huomaa, ei laskemista tai ryhmittelyä. Haluamme vain jokaisen kommentin ja kuka sen teki.

Tulos voi näyttää tältä:

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

Kuvittele nyt, että halusimme laskea Jacksonin ja Quincyn kommentit - helppo nähdä yhdellä silmäyksellä täällä, mutta vaikeampaa suuremmalla tietojoukolla kuin voit kuvitella.

GROUP BYLausuma olennaisesti kertoo kyselyn kohdella kaikkia jacksonkirjaa yhtenä ryhmänä, ja kaikki quincyennätykset kuin toinen. COUNTToiminto laskee sitten kirjaa kyseisen ryhmän ja kannattavuus arvo:

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

Ruokalaji 4: Etsi käyttäjät, jotka eivät ole kirjoittaneet kommenttia

(Uusi) Ainekset

  • VASEN LIITTYMINEN
  • ON NULL

Menetelmä

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

Eri liittymät voivat tulla hyvin hämmentäviksi, joten en pura niitä tänne. Niistä on erinomainen erittely tässä: SQL-liittymien visuaaliset esitykset, mikä myös ottaa huomioon eräiden makujen tai SQL: n syntaksierot.

Kuvitellaan tämän kyselyn vaihtoehtoinen versio nopeasti:

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

Nyt kun sinulla on joitain SQL-reseptejä, joiden avulla voit palata takaisin, ole luovaa ja tarjoile omia tietoruokasi!

Haluan ajatella WHERE, JOIN, COUNT, GROUP_CONCATkuten suola-, rasva-, hapot, kuumuus tietokannan ruoanlaittoon. Kun tiedät, mitä teet näiden ydinelementtien kanssa, olet hyvin matkalla mestaruuteen.

Jos tämä on ollut hyödyllinen kokoelma tai sinulla on muita suosikkireseptejä jaettavaksi, jätä minulle kommentti tai seuraa Twitterissä: @JacksonBates.