Andmebaasid 2021/22 kevad

SELECT lause

Praktikumis käsitlesite kõige lihtsamaid päringuid.

SELECT */{<veeruavaldised>}
FROM {<tabelid>}

ja vaatasite, et veeruavaldisteks võivad olla nii veerud, aritmeetilised tehted, funktsioonid, konstandid kui operaatorid. Lisaks tegite päringuid piirangutega, kus oli ka WHERE osa:

SELECT {<veeruavaldised>}
FROM {<tabelid>}
[WHERE {<piirangud>}]

Selles materjalis vaatame SELECT lause erinevaid osi. Kuna päringulause SELECT on üks pikemaid lauseid SQLis, mis koosneb erinevatest osadest, kus järjekord on oluline, siis paneme selle lause kokku ositi. Tervikliku lause näited on õppematerjali lõpus. Alustame aga erinevatest funktsioonide loetelust, mis võivad kõik olla nii SELECT veeruavaldistes kui ka erinevates piirangutes. Mõningaid neid juba ka kasutasite praktikumis.

Funktsioonid päringulauses

Funktsioonid jagunevad mitteagregeeritud ja agregeeritud funktsioonideks. Agregeerivad funktsioonid annavad kõigi päringusse minevate olemite peale ühe tulemuse. Mitteagregeerivad funktsioonid annavad igale funktsioonile vastava kirje kohta ühe tulemuse. Näiteks kui kasutasite funktsiooni LENGTH(name), siis see andis iga riigi puhul selle riigi nime pikkuse. LENGTH on mitteagregeeriv funktsioon. Agregeeriv funktsioon võib olla näiteks kõik teatud tunnuse väärtuste summa, nende arv (loendamise tulemus) või aritmeetiline keskmine. Alustame mõnedest olulisematest mitteagregeeritud funktsioonidest. Agregeerimata funktsioon rakendub kirjele, agregeeriv funktsioon korraga kõikidele kirjetele.

SELECT DATEDIFF(minute, algusaeg, loppaeg)
FROM Partiid

SELECT pealkiri, DATEFORMAT(kestus, 'HH:MM:SS') 
FROM laulud

SELECT DAY(algusaeg) 
FROM Partiid

SELECT MONTH(algusaeg) 
FROM Partiid

SELECT YEAR(algusaeg) 
FROM Partiid

SELECT HOUR(algusaeg) 
FROM Partiid

SELECT MINUTE(algusaeg) 
FROM Partiid

SELECT LEFT(eesnimi,1) 
FROM isikud

SELECT RIGHT(eesnimi,1) 
FROM isikud

SELECT LENGTH(eesnimi) 
FROM isikud

SELECT SUBSTRING(eesnimi,3,2) 
FROM isikud

SELECT ROUND(area,0) 
FROM World

Ja enamkasutatavad agregeerivad funktsioonid:

SELECT AVG(area) 
FROM World 
WHERE continent='Europa'

SELECT COUNT(name) 
FROM World 
WHERE continent='Europe'

SELECT MAX(area) 
FROM World

SELECT MIN(area) 
FROM World

SELECT SUM(area) 
FROM World 
WHERE continent='Europa'

Kõik SQL Anywhere funktsioonid leiad lingilt: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbreference/first-value-function.html

LIKE operaator

Piirangus võis olla ka LIKE operaator. Käsitleme seda veidi lähemalt. LIKE operaatorit kasutatakse teksitliste 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 lisaks. Päringulause

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

annab aga lisaks meile tulemustabelisse ka sellised nimed nagu Annemari jms. % märk võib esinega suvalises kohas, ka sõna keskel ja mitu korda. Analoogselt saab ka kasutada operaatorit NOT LIKE. Oluline on aga märkida LIKE operaatori oht - kui kasutatakse % esimesel kohal, siis on päring aeglasem.

IS NULL ja IS 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 raamat 
FROM laenutused 
WHERE tagastamisaeg IS NULL,

kus meile kuvatakse kõik väljalaenutatud raamatud, mis pole veel tagastatud (tagastamisaeg on sisestamata). 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.

Grupeerimine

Praktikumis kasutasite ka funktsioone SUM, MIN, COUNT. Neid nimetatakse agregeeritud funktsioonideks (annavd ü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 rühmitamist:

SELECT {<veeruavaldised>} 
FROM {<tabelid>} 
[WHERE {<piirangud>}] 
[GROUP BY {<veerud>}]

Näiteks soovides teada, kui palju on töötajate hulgas mehi, kui palju naisi, saab kirjutada:

SELECT sugu, COUNT(sugu) AS arv 
FROM töötajad 
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 'f' ja meessoost 'm', siis tulemustabel võiks välja näha selline:

SELECT lause järgmine osa HAVING on samuti piirang, kuid kui WHERE piirang rakendub olemile, siis HAVING rakendub grupile.

SELECT {<veeruavaldised>} FROM {<tabelid>} 
[WHERE {<piirangud>}] 
[GROUP BY {<veerud>}] 
[HAVING {<piirang>}]

Näiteks kui soovime kirjutada päringu, millega leiame naiste eesnimede esitähed, mida esineb enam kui seitsmel korral.

SELECT Left(eesnimi,1) AS algustäht, count(eesnimi) AS arv
FROM inimesed
WHERE sugu='f'
GROUP BY algustäht
HAVING arv>7

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

SELECT lause viimane osa on ORDER BY, mis võimaldab järjestada

SELECT {<veeruavaldised>} 
FROM {<tabelid>} 
[WHERE {<piirangud>}] 
[GROUP BY {<veerud>}] 
[HAVING {<piirang>}] 
[ORDER BY {<veerud>}];

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 inimesed
WHERE sugu='f'
GROUP BY algustäht
HAVING arv>7
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:

Kokkuvõttes: SELECT lause koosneb erinevatest osadest, millest kohustuslikud on SELECT ja FROM. Kui kasutatakse ka teisi lauseosasid, siis alati konkreetses järjekorras ehk siis ei ole võimalik kasutada HAVING või ORDER BY enne GROUP BY'd.