18 Tulemuste järjestamine, välisvõtmed ja päringud mitme tabeli põhjal

Järjestamine

Seni koostatud päringute tulemusi oleks tihtipeale mugavam vaadata, kui need oleks millegi alusel järjestatud. Näiteks, eelmises peatükis pärisime populaarseid Netflixi näitlejaid ning saime tulemusse väga palju kirjeid. Tulemus oleks kenam välja näinud, kui näitlejad oleksid teostes näitlemiste arvu alusel järjestatud olnud.

Järjestamiseks kasutame märksõnapaari ORDER BY, mis käib seniõpitud päringulause kuju lõppu.

Jooksuta järgnevat päringut:

• SELECT naitleja_id, COUNT(*) FROM naitlemine GROUP BY naitleja_id HAVING COUNT(*) >= 25 ORDER BY COUNT(*);

Näeme, et seekord on näitlejate ID-d näitlemiste arvu põhjal järjestatud. Seega on päringu tulemusest märksa lihtsam ülevaadet saada.

Selle saavutasime kirjutades ORDER BY COUNT(*). Sama hästi oleks võinud kirjutada ORDER BY 2, mis tähendaks seda, et järjestamine tehakse SELECT osas oleva 2. tunnuse, avaldise või funktsiooni põhjal, milleks oligi hetkel COUNT(*).

Nagu nägime näidete põhjal, siis vaikimisi toimub PostgreSQL’s järjestamine kasvavalt. Järjestamise loogikat saab ka ise täpsustada, kasutades selleks abimärksõnu ASC (ascending, ee kasvav) ja DESC (descending, ee kahanev). Täpsustavad märksõnad tuleb kirjutada terve ORDER BY osa lõppu ehk üldse terve päringulause lõppu.

Päringu tulemusi võib järjestada ka mitmel alusel ning sellisel juhul on nende kirja paneku järjekord (pärast märksõnapaari ORDER BY) oluline.

Tasub teada, et grupeerimise aluse ehk märksõnadele GROUP BY vahetult järgneva osa võib samuti asendada mingi sobiva arvuga, täpselt nii nagu oleme teinud ORDER BY puhul. See on mugav abivahend päringute kiiremaks kirjutamiseks. Samas on niiviisi vead kergemad tekkima, mis tähendab, et tuleb tähelepanelik olla.

Suurimad ja vähimad

Isegi kui tulemusi piirata ja järjestada võib neid mõnikord ikkagi liiga palju olla, et nende põhjal mugavalt järeldusi teha saaks. Seega on vahepeal oluline, et päringu tulemus oleks võimalikult lühike ja kokkuvõtlik.

Siinkohal on abiks PostgreSQL’i märksõna LIMIT., mille abil saab otseselt piirata, mitut kirjet päringu tulemuses näidatakse. Märksõna käib taaskord terve seniõpitud päringu kuju lõppu. Koos märksõnaga LIMIT tuleb kirjutada ka arv, millega täpsustatakse, mitut tulemust alles soovitakse jätta.

Jooksuta järgnevat päringut:

• SELECT nimi, rahvastikuarv FROM riigid LIMIT 3;

Näeme, et päringu tulemuses on vaid kolme riigi nimi ja rahvastikuarv.

Ära ole eksitatud sellest, et päringu tulemuses on riikide nimed tähestiku järjekorras. Märksõna LIMIT omaette ei tegele päringu järjestamisega. Siinkohal lihtsalt juhtus, et tabelis Riigid on riigid juba algselt nimede alusel tähestiku järjekorras.

Tasub teada, et märksõna LIMIT korrektseks kasutamiseks tuleb see alati kombineerida märksõnapaariga ORDER BY. Vastasel juhul on päringu tulemuses põhimõtteliselt piiratud arv suvalisi kirjeid.

Välisvõtmed

Kuigi välisvõtmeid käsitleti põgusalt juba eelmises moodulis, uurime neid siinkohal veidi täpsemalt.

Välisvõtme loomine kahe tabeli vahele annab andmebaasi juhtimissüsteemile teada, et need tabelid on omavahel seotud. Seose olemus täpsustatakse võtme sees. Seostest andmebaasi juhtimissüsteemile teada andmine aitab tabeleid paremini hallata ning kontrollida. Seda just olukordades, kui tabelites olevaid andmeid või ka tabeleid ise muutma või kustutama hakatakse.

Välisvõtmed tagavad selle, et ühes tabelis leiduvad väärtused, mis viitavad teisele tabelile, tõepoolest eksisteerivad ka teises tabelis. Seega aitavad välisvõtmed kaudselt kaasa mitme tabeli põhjal päringute loomisele, mida õpime juba selles samas peatükis.

Välisvõtme loomisel tuleb SQL lauses täpsustada, et ühe tabeli mingi tunnus ja selle väärtused viitavad tegelikult teise tabeli tunnusele ja selle väärtustele. Välisvõtme loomiseks peavad seotavate tunnuste andmetüübid täpselt kattuma, näiteks peavad mõlemad olema tüüpi INTEGER.

Meie andmebaasis on vajalikud välisvõtmed juba loodud. Seega, juurde pole neid tarvis lisada. Vaatame siiski teadmiseks ühte olemasolevat välisvõtit, kuidas seda loodi ning mida see niiöelda ütleb.

Vaatame välisvõtit naitlemine2isikud, mis on tabeli Naitlemine tunnuse naitleja_id ja tabeli Isikud tunnuse id vahel ning mis loodi järgneva lausega:

• ALTER TABLE naitlemine ADD CONSTRAINT naitlemine2isikud FOREIGN KEY (naitleja_id) REFERENCES isikud(id) ON DELETE CASCADE ON UPDATE CASCADE;

Vaatame selle lause osi ning nende tähendusi lähemalt:

Tuleta meelde, et juba mooduli Andmebaaside tutvustus peatükis Vaikeväärtused ja kitsendused oli juttu kitsendustest. Nagu näha, on ka välisvõtmed omamoodi kitsendused (vt lause ADD CONSTRAINT osa), kuna nad määravad, kuidas andmebaasi juhtimissüsteem tabelite muutmise ja kustutamise korral toimima peaks.

Tabelite olemasolevaid välisvõtmeid saab tabelite kaupa ka ise lähemalt uurida. Selleks tuleb Projects vaates navigeeruda vastava tabeli nimeni ning avada selle alamkaust Foreign Keys. Seal on näha kõiki vastava tabeli välisvõtmeid ning neile klikates saab ka nende sisu näha.

Ka kõiki ühe skeema tabelite välisvõtmeid on võimalik korraga näha. Selleks tuleb navigeeruda vastava skeema alamkaustani Tables, teha sellele topeltklikk ning valida ülevalt menüüst alamvaade ER Diagram. Seejärel peaks avanema sarnane vaade.

Pildil olevad tabelitevahelised katkendjooned tähistavad olemasolevaid välisvõtmeid. Nende kohal hiirekursorit hoides on võimalik näha ka nende nimesid.

Välisvõtmete uurimine näidatud meetoditel on hea viis võõra andmebaasi ülesehituse ja tabelite vaheliste seostega tutvumiseks.

Päringud mitme tabeli põhjal

Vaatamegi lõpuks, kuidas koostada päringuid mitme tabeli põhjal. Nii saame päringu tulemuses kombineerida erinevate tabelite andmeid, mis aitab meil luua huvitavamaid ja kasulikemaid päringuid. Näiteks, mitme tabeli põhjal loodud päringu tulemuses on korraga võimalik kuvada nii riikide kui ka teoste nimesid. Seni õpitu põhjal me seda teha ei saanud, kuna vastavad andmed asuvad erinevates tabelites.

Vaatame lihtsat näidet, mis demonstreerib mitme tabeli põhjal päringute loomise kasulikkust.

Oletame, et me soovime leida kõiki Soomes tehtud teoseid. Selleks peaksime esmalt tabelist Riigid leidma Soomele vastava ID (mis on 19). Seda saame teha kas Data vaate abil tabelit Riigid vaadeldes või jooksutades järgnevat päringut:

• SELECT id FROM riigid WHERE nimi = 'Finland';

Seejärel saame Soomes tehtud filme leida kas Data vaates eelmises peatükis õpitud meetodil andmeid filtreerides või jooksutades järgnevat päringut:

• SELECT pealkiri FROM teosed WHERE id = 19;

Kuigi lihtsamaid ülesandeid saab niimoodi mitme sammuga täita küll, on see suures plaanis kohmakas ning liialt aeganõudev.

Sama ülesannet täitev mitme tabeli põhjal loodud päring näeb välja järgnev:

• SELECT teosed.pealkiri, riigid.nimi FROM teosed, riigid WHERE teosed.riigi_id = riigid.id AND riigid.nimi = 'Finland';

Eelolevas päringus on kõigi tunnuste puhul välja toodud ka see, millisest tabelist see pärit on. Nagu näha, saab seda teha kujul <tabeli_nimi>.<tunnuse_nimi>. Siin on seda tehtud peamiselt selleks, et päringulause inimeste jaoks selgem oleks.

Vaatame nüüd, kuidas see päring täpselt töötas.

Kõigepealt täpsustasime tunnused, mida soovime pärida – teoste pealkirjad ja riikide nimed – ning ka tabelid, millest pärime – Teosed ja Riigid.

Näeme, et pärides mitme tabeli põhjal, tuleb tabelite nimed komadega eraldada, nii nagu eraldame omavahel ka tunnuseid.

Seejärel täpsustasime WHERE osas esmalt seda, et riigi_id väärtus peab olema võrdne riigid.id väärtusega. Selle tulemusena ühendati kõigi teoste pealkirjad ID-de võrdumise alusel vastavate riikide nimedega. Lõpuks piirasime WHERE osas päringu tulemust veel nii, et alles jääks vaid Soome teosed.

Vaatame uut näidet. Oletame, et soovime leida, mitu korda on TOP 10 enim lavastanud lavastajat lavastanud ning järjestame nad lavastamiste arvu alusel kahanevalt.

Seda kõike saame leida jooksutades järgnevat päringut:

• SELECT nimi, COUNT(lavastamine.*) FROM isikud, lavastamine WHERE lavastamine.lavastaja_id = isikud.id GROUP BY nimi ORDER BY COUNT(lavastamine.*) DESC LIMIT 10;

Vaatame ka selle päringu osade haaval üle.

Taaskord andsime kõigepealt teada, mida soovime pärida – lavastajate nimed ja lavastamiste arvud – ning ka tabelid, millest pärime – Isikud ja Lavastamine.

Seejärel täpsustasime WHERE osas seda, et lavastaja_id peab olema võrdne isikud.id väärtusega, mille tulemusena ühendati kõik lavastamised ID-de võrdumise alusel vastavate lavastajate nimedega.

Järgmisena grupeerisime väärtused lavastajate nimede alusel selleks, et iga lavastaja nime kõrval näidataks agregeeriva funktsiooni COUNT abil leitud arv, mitu korda ta lavastanud on.

Lõpuks järjestasime tulemused lavastamiste arvu alusel kahanevalt ning jätsime neist alles vaid 10 esimest tulemust.

Mitme tabeli põhjal päringuid luues ei pea kindlasti piirduma vaid kahe tabeliga. Tabeleid võib päringusse kaasata teoreetiliselt nii palju, kui neid andmebaasis leidub. Seda siiski eeldusel, et tabelid on omavahel sobivalt seotud.

Näiteks, käesolevas andmebaasis saab luua järgneva neljal tabelil põhineva päringu:

• SELECT pealkiri, riigid.nimi AS riik, isikud.nimi AS näitleja FROM teosed, riigid, naitlemine, isikud WHERE teosed.riigi_id = riigid.id AND naitlemine.teose_id = teosed.id AND naitlemine.naitleja_id = isikud.id;

Uuri põhjalikult ülalolevat päringut ning proovi mõista, kuidas see täpselt töötab ning mis on selle päringu tulemuses.

Proovi ka ise huvitavaid mitmel tabelil põhinevaid päringuid välja mõelda. Katseta neis siiani õpitud tehteid, funktsioone, grupeerimist ja kõike muud.

Nüüd oleme õppinud päringute tulemusi järjestama ja nende seast suurimaid ja vähimaid leidma. Uurisime, mis on välisvõtmed ning õppisime ka mitme tabeli põhjal huvitavaid päringuid looma. Järgmisena looma otsingumoorit sisaldava veebirakenduse Netflixi teoste otsimiseks.