LIKE operaator
LIKE operaator on ka üks võimalikest operaatoritest, mida saab kasutada koos WHERE märksõnaga. LIKE operaator pakub võimalust täpsustada enda järel mingit mustrit, mille põhjal saab veergude väärtuseid piirata. Tihtipeale kasutatakse seda sõnede juures, kus otsitakse tabelist kirjeid, kus veeru väärtus sisaldab mingit kindlat sümbolite kombinatsiooni. Aga sellega saab ka teisi andmetüüpe piirata.
Näiteks:
SELECT riik FROM lemmikloomapoed WHERE riik LIKE 'Ees%';
Selles lauses on kasutatud mustrit 'Ees%'. 'Ees' osa mustris tähendab, et käsk otsib veerust riik sõnesid, mis algavad nende sümbolitega. % märk aga tähendab, et selle asemel võib olla ükskõik kui palju igasuguseid sümboleid (isegi 0 sümbolit). Ehk näide tagastab meile kõik veeru riik kirjed, kus riigiks on Eesti. Pane tähele, et muster eristab ka suuri- ja väiketähtesid.
Eesti riiki saab pärida ka näiteks lausega:
SELECT riik FROM lemmikloomapoed WHERE riik LIKE '%sti';
LIKE mustris saab ka kasutada alakriipsu _. Alakriips tähendab, et selle asemel võib olla üks ükskõik milline sümbol. Alakriipse võib ka järjest mitu olla ning neid saab kombineerida ka sümboliga %.
SELECT riik FROM lemmikloomapoed WHERE riik LIKE 'Eest_';
PostgreSQL'i LIKE operaator töötab ainult sõnedega. Seega, kui me tahame operaatorit kasutatada veergude juures, kus andmetüübiks on number, peame me SELECT lauses andmetüübi teisendama sõneks. Seda saab teha, kirjutades WHERE osas veeru nime taha kaks koolonit ning uue andmetüübi.
SELECT * FROM lemmikloomapoed WHERE töötajate_arv::text LIKE '5'
Aritmeetilised tehted veeruavaldistena
SQL pakub võimalust andmete pärimise ajal tabeli veergudega teha aritmeetilisi tehteid. Seda saab kasutada näiteks selleks, et tabelis hoida ruumi kokku. Selle asemel, et teha tabelisse uus veerg uue tunnusega, saame hoopis kasutada juba olemasolevaid veerge ning kuvada soovitav tulemus aritmeetilise tehtega. Veergudega saab teha tehteid omavahel või mõne konstandiga.
SELECT linn, töötajate_arv + 5 FROM lemmikloomapoedSELECT töötajate_arv * 5 FROM lemmikloomapoed
Käskusid jooksutades võid tähele panna, et veergudel, millega on tehtud mõni tehe, pole normaalsel kujul nime. See juhtub, kuna DBeaver ei tea, mida veeruga tehtud tehte tulemus peaks tähistama. Selleks, et veerule anda tema väärtuseid illustreeriv pealkiri saab kasutada AS võtmesõna.
SELECT linn, töötajate_arv + 5 AS tootajaid_jouludel FROM lemmikloomapoed
Mitteagregeerivad funktsioonid
Veeruavaldistena saab veel lisaks kasutada PostgreSQL'i poolt pakutavaid sisseehitatud funktsioone. Need jaotatakse kaheks - mitteagregeerivad ja agregeerivad funktsioonid. Kõigepealt räägime mitteagreveerivatest funktsioonidest.
Mitteagreveerivaid funktsioone on palju ning nende kõikide kohta saab lugeda PostgreSQL dokumentatsioonist. Siin peatükis vaatame läbi mõned kasulikumad nendest.
Stringitüüpi tunnustele saab rakendada järgmiseid funktsioone:
- LENGTH (<veerg>)
- LEFT (<veerg>, arv)
- RIGHT (<veerg>, arv)
- UPPER (<veerg>)
- SUBSTRING(<veerg>,arv,arv)
LEFT ja RIGHT funktsioone kasutatakse sellest, et saada veeru kirjest kätte mingi kindel arv sümboleid, alustades sümbolite loendamist veerus, kas vasakulkt või paremalt. Arv parameeter funktsioonis määrabki ära sümbolite arvu.
SELECT LEFT(riik, 2) FROM lemmikloomapoedSELECT RIGHT(riik, 2) FROM lemmikloomapoed
LENGTH funktsiooni kasutatakse peamiselt päringu WHERE osas ning sellega saab otsida andmeid veeru kirje tähemärkide arvu põhjal.
SELECT linn FROM lemmikloomapoed WHERE LENGTH(linn) > 5
UPPER funktsioon teisendab kõik veerus olevad väiketähed suurtähtedeks. SUBSTRING töötab sarnaselt LEFT ja RIGHT funktsioonidele. Erinevus seisneb selles, et me saame täpsemalt valida, kust me tahame veeru kirjet tükeldada.
SELECT UPPER(linn) FROM lemmikloomapoedSELECT SUBSTRING(riik, 1, 2) FROM lemmikloomapoed
Ehk eelmine käsk annab meile veeru riik kirjete esimesed kaks sümbolit. Esimene number funktsioonis määrab ära, millisest indeksist algab tükeldamine. Eelnevas näites on see 1, ehk tükeldamine algab esimesest kirje sümbolist. Teine number määrab ära kui pikk see tükeldatud osa peab olema. Meie näite puhul on see kaks sümbolit.
Funktsioone saab ka koos kasutada.
SELECT UPPER(SUBSTRING(riik, 1, 2)) FROM lemmikloomapoed
Lõpuks tasuks ka teada funktsiooni DATE_PART, mida kasutatakse kuupäeva märkivate andmetüüpidega veergude töötlemiseks. Näiteks on sellega võimalik kätte saada TIMESTAMP andmetüübist ainult aasta.
SELECT DATE_PART('year', sisestatud) FROM lemmikloomad
DATE_PART võtab esimeseks argumendiks sõne, millega saabki märkid, millist osa kuupäevast sa soovid näha. Veel on võimalik 'year' asemel kirjutada näiteks 'month' ja 'day'.
Agregeerivad funktsioonid
Agreveerivatest funktsioonidest toome ka välja mõned tähtsamad:
- Count(*)
- Count(distinct <veerg>)
- Max(<veerg>)
- Min(<veerg>)
- Sum(<veerg>)
- Avg (<veerg>)
COUNT funktsiooniga saab vaadata tabelis olevate kirjete arvu. Veel saab funktsiooni sees kasutada DISTINCT märksõna.
SELECT Count(*) FROM lemmikloomapoed
Lause annab meile kõik tabelis lemmikloomad olevate kirjete arvu. Funktsiooniga saab otsida ka mingi kindla veeru kirjete arvu.
SELECT Count(riik) FROM lemmikloomapoed
MAX ja MIN funktsioonid tagastavad tabeli veeru vastavalt suurima või väikseima kirje. Funktsiooni saab kasutada nii sõnetüüpi tunnuste kui ka numbriliste tunnuste peal. Sõnede puhul vaatavad funktsioonid tähestiku järjekorda, ehk MAX tagastab tähestikus viimase kirje veerus.
SELECT MAX(töötajate_arv) FROM lemmikloomapoed WHERE töötajate_arv > 4SELECT MIN(riik) FROM lemmikloomapoed
SUM funktsioon tagastab veerus olevate väärtuste summa ja AVG keskmise väärtuse.
SELECT SUM(töötajate_arv) FROM lemmikloomapoed
Mitteagreveerivate ja agreveerivate funktsioonide vahe seisneb selles, et agregeerivad funktsioonid annavad ühe tulemuse veeru kirjete kohta, millele funktsiooni rakendatakse, ehk SQL konsoolis näed tulemusena alati ühte väärtust. Mitteagreveerivad aga annavad ühe tulemuse iga veeru kirje kohta, mille peal funktsiooni kasutatakse. Mitteagreveerivaid ja agreveerivaid funktsioone ei saa korraga läbisegi kasutada.
Seosed tabelite vahel
Tabelite vahel saab luua seoseid välisvõtmetega. Välisvõtmega märgitakse, et mingi tabeli mingi tunnus viitab teise tabeli mingile tunnusele. Näiteks saab välisvõtmega määrata, et esimeses tabelis võtmega ühendatud tunnuse kirje kustutamisel kustub kirje ka teises tabelis. Välisvõtmega märgitud tunnustel peab olema sama andmetüüp.
Välisvõtme loomine näeb välja järgnev:
ALTER TABLE <alamtabel> ADD CONSTRAINT <fk_nimi> FOREIGN KEY ({alamtabeli_veerg}) REFERENCES <ülemtabel>[({võtmeveerg})] [ON DELETE {tegevus}] [ON UPDATE {tegevus}];
Näiteks:
ALTER TABLE lemmikloomad ADD CONSTRAINT fk_lemmikloomad2poed FOREIGN KEY (PoeID) REFERENCES Lemmikloomapoed(id) ON DELETE CASCADE ON UPDATE CASCADE;
Loodud välisvõti tähistab, et tabeli lemmikloomad veerg PoeID viitab tabeli lemmikloomapoed veerule id. Alamtabeliks kutsutakse tavaliselt tabelit, millele luuakse välisvõti ning ülemtabeliks seda, mille primaarvõtit viidatakse. ON DELETE CASCADE tähendab, et lemmikloomapoe kustutamisel kustub automaatselt ka lemmikloom, mille PoeID väärtus oli võrdne kustutatud poe id väärtusega. ON DELETE CASCADE omadusele on veel lisaks kaks tähtsat omadust ON UPDATE CASCADE ja ON DELETE RESTRICT. ON UPDATE CASCADE tähendab, et viidatud ülemtabeli veeru muutmisel muutuvad ka alamtabeli kirjete väärtused vastavalt, ehk üleval olevas näites kui me muudaksime tabelis lemmikloomapoed mingi poe id väärtuse, siis muutuvad PoeID väärtused ka tabelis lemmikloomad. ON DELETE RESTRICT ei lase viidatud tabeli kirjeid kustutada.
Kui SQL konsoolis saad välisvõtit luues errori, siis võib selle taga olla ON UPDATE CASCADE. Tõenäoliselt on sul viidatud veerus mingi väärtus, mida teises tabelis viidatud veerus pole. Vaata, et mõlemas tabelis viidatud veergudes olevate kirjete väärtuste seas ei oleks erinevusi.
DBeaveris saad hea ülevaate tabelite vahelistest seosetest, kui liigud Schemas->public ning public vaates vajutad paremal pool aknas ER Diagram.
| Päringud | Päringud mitmest tabelist |