Databases 2024/25 spring

Päringulause SELECT (jätk)

Sissejuhatus

SELECT lause osadega tutvusite juba esimesel nädalal ja praktikumis. Selles materjalis käsitleme operaatoreid LIKE, NOT LIKE, IS NULL, IS NOT NULL ja vaatame päringulause teisi osi. Täielik SELECT lause on kujul:

SELECT {<veeruavaldised>} 
FROM {<tabel>} 
[WHERE {<tingimus>}] 
[GROUP BY {<veerg>}] 
[HAVING {<tingimus>}] 
[ORDER BY {<veerg>}]
[LIMIT n];

Esmalt tutvume siin materjalis eelnimetatud operaatoritega ning seejärel vaatame SELECT lause teisi osi ükshaaval.

LIKE ja NOT LIKE operaatorid

Päringu WHERE osas võib olla ka LIKE operaator. Käsitleme seda veidi lähemalt. LIKE operaatorit kasutatakse tekstiliste andmetüüpidega (varchar, text, char) defineeritud väljadel. LIKE operaatori puhul võrreldakse teksti teatud mustriga. Mustri koostamiseks on kasutada kaks põhilist sümbolit: '_' ja '%'. Alakriips '_' sobib suvalise ühe sümboli asemele ning protsendimärk '%' sobib sümbolite jada asemele. Näiteks kirjutades päringu

SELECT eesnimi 
FROM isikud 
WHERE eesnimi 
LIKE 'Mari%', 

saame tulemuseks nii nime Mari kui ka nimed Maris, Mariann, Marit, Mari-Liis jne. Kirjutades aga päringulause

SELECT eesnimi 
FROM isikud 
WHERE eesnimi 
LIKE 'Mari_' 

saame tulemuseks küll Maris, Marit, Marin, Maria jne, kuid teisi nimesid ei saaks, sest siin nõutakse, et oleks lisaks algusele Mari veel üks suvaline täht. Samas teades, et tabelis on keegi eesnimega Peeter, aga me ei ole kindlad, kas perekonnanimi on Peet või Leet. Ehk siis ühe sümboli vahe, siis sellisel juhul on _ kasutamine omal kohal.
Sooritame päringu:

SELECT * FROM isikud WHERE eesnimi='Peeter' AND perenimi LIKE '_eet';

% saab kasutada mustris ees ja taga. Päringulause

SELECT eesnimi 
FROM isikud 
WHERE LOWER(eesnimi) 
LIKE '%mari%' 

annab lisaks loetetud nimedele (Maris, Mariann, Marit, Mari-Liis jne) meile tulemustabelisse ka sellised nimed nagu Annemari jms. % märk võib esineda suvalises kohas, ka sõna keskel ja mitu korda. Kuna PostgreSQL eristab suur- ja väiketähti siis kasutasime eesnime teisendamist väiketähtedeks. Kuid siin saab kasutada aga ka operaatorit ILIKE, mis väljastab tulemuse sõltuvata sellest, kas see on väike- või suurtähedes kirjutatud. Siis ei ole vaja eesnime eelnevalt väiketähtedesse teisendada. Päringulause

SELECT eesnimi 
FROM isikud 
WHERE eesnimi 
ILIKE '%mari%' 

Ka selle päringuga saaksime sellised nimed nagu Annemari, Mariann jms. Kui siin päringus oleks aga ILIKE asemel LIKE, saaksime ainult Annemari, kui ei saaks nimesid Mariann, Maris jms.

Analoogselt LIKE operaatorile saab ka kasutada operaatorit NOT LIKE. Oluline on aga märkida LIKE operaatori oht - kui kasutatakse % esimesel kohal, siis on päring aeglasem.

Mustri elemente % ka _ saab kasutada ka kombineeritult.
Pärime kõik kirjed, kus perenimes enne n sümbolit võib olla üks või mitu sümbolit ja peale n tähte võib olla üks suvaline sümbol.

SELECT perenimi FROM isikud WHERE perenimi LIKE '%n_';

Mõtiskle, millised perenimed vastaksid sellisele tingimusele. Kui soovid oma teadmisi kontrollida, ava test.
AVA TEST

Mustri elemente % ka _ saab kasutada ka sõne keskosa maskeerimisel.
Mõtiskle, millised perenimed vastaksid sellisele tingimusele:

SELECT perenimi FROM isikud WHERE perenimi LIKE 'K%s';

Kui soovid oma teadmisi kontrollida, ava test.
AVA TEST

NOT LIKE toimib LIKE suhtes vastupidi.
NOT LIKE puhul on mustri loomise elemendid _ ja % ja nende kombinatsioonid samuti nagu LIKE puhul kasutatavad.
Kui soovime otsida isikuid, kelle perenimed ei alga K-tähega, saame kirjutada päringu

SELECT * FROM isikud WHERE perenimi NOT LIKE 'K%';

IS NULL ja NOT NULL

SQL kontekstis tähendab NULL puuduvat väärtust. NULL ei ole sama mis 0. NULL väärtust väljal võib tõlgendada ka terminiga „ei tea“ , „pole sisestatud“. Seega saame ka päringus panna piiranguks, kas on andmed sisestatud või ei. Näiteks

SELECT * FROM isikud 
WHERE klubi IS NULL;

kus meile kuvatakse kõik isikud, kes ei kuulu klubisse. Või päring

SELECT eesnimi, perenimi 
FROM töötajad 
WHERE juhitav_osakond IS NOT NULL;

annab meile kõik töötajad, kes juhivad mingit osakonda.

Mis iganes on ärireeglid, tehniliselt annavad operaatorid NULL ja NOT NULL uued võimalused tingimuste defineerimiseks. Võrreldes IS NULL kasutamisega annab IS NOT NULL (ehk IS NULL eitamine) alati vastupidise hulga.
Kui soovid oma teadmisi päringu operaatorite kohta kontrollida, ava test.
AVA TEST

Järgnevalt läheme edasi SELECT lause osadega.

Grupeerimine - GROUP BY

Praktikumis kasutasite ka funktsioone SUM, MIN, COUNT. Neid nimetatakse agregeeritud funktsioonideks (annavad ühe tulemuse) ning neid ei saa seepärast kasutada päringus koos mitteagregeeritud funktsioonide (näiteks LENGTH, LEFT, YEAR jms) või veerunimedega. Selleks on vajalik kasutada grupeerimist. Grupid tekivad agregeerimisfunktsiooni rakendamisest mingile väljale.

SELECT {<veeruavaldised>} 
FROM {<tabel>} 
[WHERE {<tingimus>}] 
[GROUP BY {<veerg>}];

Näiteks soovides teada, kui palju on isikute hulgas mehi, kui palju naisi, saab kirjutada:

SELECT sugu, COUNT(sugu) AS arv 
FROM isikud 
GROUP BY sugu;

Kuna siin sugu on agregeerimata (atribuudi sugu väärtuste arv on sama, mis olemite arv tabelis) ja COUNT(sugu) on agregeeritud funktsioon, siis agregeerimata veerg peab olema GROUP BY järel. Kui naissoost töötajad on kodeeritud 'n' ja meessoost 'm', siis tulemustabel võiks välja näha selline:

Siin päringus kirjutasime COUNT(sugu) AS arv ning näeme, et tulemustabeli pealkirjaks sai ka arv. AS abil me saame veeru või avaldise ümber nimetada. Kui on tegemist ühe sõnaga, võime selle kirjutada jutumärke kasutamata, kui aga on mitu sõna, siis peame panema selle jutumärkide vahele. Kui veerg on ümber nimetatud, siis saame seda ka kasutada GROUP BY juures (vaata HAVING all olevat näidet).

Tingimus grupile - HAVING

SELECT lause järgmine osa HAVING on samuti tingimus, millega piiratakse päringusse tulevaid kirjeid, kuid kui WHERE piirang rakendub olemile (tabeli reale), siis HAVING rakendub grupile.

SELECT {<veeruavaldised>}
FROM {<tabel>} 
[WHERE {<tingimus>}] 
[GROUP BY {<veerg>}] 
[HAVING {<tingimus>}];

Näiteks kui soovime kirjutada päringu, millega leiame naiste eesnimede esitähed, mida esineb enam kui neljal korral. Paneme tähele, et siin GROUP BY juurde ei kirjutanud me pikalt terve avaldise LEFT(eesnimi,1), vaid algustäht, sest me nimetasime selle avaldise SELECT juures nii ümber. HAVING juures kahjuks ümbernimetatud veeru/avaldise nime kasutada ei saa (saab GROUP BY ja ORDER BY juures).

SELECT LEFT(eesnimi,1) AS algustäht, count(eesnimi) AS arv
FROM isikud
WHERE sugu='n'
GROUP BY algustäht
HAVING count(eesnimi)>4;

Selle tulemus võiks välja näha selline:

Andmebaasi juhtimissüsteem rakendab esmalt WHERE piirangut ja peale seda GROUP BY kirjetele, mis WHERE piirangu tulemusena alles jäävad.

Järjestamine - ORDER BY

SELECT {<veeruavaldised>}
FROM {<tabel>} 
[WHERE {<tingimus>}] 
[GROUP BY {<veerg>}] 
[HAVING {<tingimus>}];
[ORDER BY {<veerg> [ ASC | DESC ] } ];

Järjestada on võimalik nii kasvavalt (mittekahanevalt) ASC kui ka kahanevalt (mittekasvavalt) DESC. Näiteks kui me eelmise näite korral tahame ka järjestada kirjutades esimeseks eesnime esitähe, millega on kõige enam naisi, siis näeb lause välja selline:

SELECT Left(eesnimi,1) AS algustäht, count(eesnimi) AS arv
FROM isikud
WHERE sugu='n'
GROUP BY algustäht
HAVING count(eesnimi)>4
ORDER BY arv DESC;

Oleks võinud kirjutada ka ORDER BY 2 DESC, sest arv on SELECT osas teisel positsioonil. Tulemus näeks välja selline:

Piiramine - LIMIT

Kui ei soovi väljastada kõiki tingimustele vastavaid kirjeid, vaid ainult teatud arvu, siis saab kasutada SELECT lauseosa LIMIT.

SELECT {<veeruavaldised>}
FROM {<tabel>} 
[WHERE {<tingimus>}] 
[GROUP BY {<veerg>}] 
[HAVING {<tingimus>}];
[ORDER BY {<veerg> [ ASC | DESC ] } ]
[LIMIT n];

Siin n on ridade (kirjete) arv, mida soovitakse väljastada. Näiteks soovime väljastada viie kõige kõrgema ränkinguga mängija andmed. NB! LIMIT osa saab kasutada ainult koos ORDER BYga. Vastasel juhul annab see suvalised viis kirjet. Seega viie kõrgeima ränkinguga mängija väljastamiseks peame esmalt tulemuse järjestama kahanevalt (DESC) ja siis sealt valima 5 esimest:

SELECT * FROM isikud 
ORDER BY ranking DESC
LIMIT 5;

Leidmaks viite kõige madalama ränkinguga mängijat oleks sama lause, ainult järjestamine oleks olnud kasvav (ASC).
Mõelge, mida võiks aga väljastada selline päringulause:

SELECT * FROM isikud 
ORDER BY ranking DESC
LIMIT 5 OFFSET 2;

Siin väljastatakse küll viis kõrgemate ränkingutega mängijatest, kuid alates kolmandast positsioonist ehk siis kahte kõige paremat mängijat ei tule tulemustabelisse, vaid need, kes on paremuselt 3.-7. positsioonil. PostgreSQL dokumentatsioonist saad lugeda lisa päringulause kohta

Kui soovid oma teadmisi päringulause kohta kontrollida, ava test.
AVA TEST

Päringulause SELECT jätkuvideo:

Andmetüübid

Alampäringud