Sissejuhatus
Esimalt paigaldame enda arvutitesse vajaliku tarkvara praktikumide edukaks läbimiseks, seadistame esimese PostgreSQL andmebaasi ning loome sinna ka tabeli, kasutades SQL päringukeelt. Praktikumi käigus saad sooritada erinevaid enesekontrolli teste. Testide tulemusi ei salvestata ning nende eesmärk on aidata sul kinnistada praktikumis õpitut.
Kursuse raames on meil vaja installeerida kaks programmi. Esimene on PostgreSQL andmebaasihadustarkvara. Teiseks on meil vaja inimloetaval kujul näha, mis meie loodud andmebaasis ja tabelites toimub. Selleks kasutame kliendipoolsest tarkvara DBeaverit.
Enne kui asud allpool olevate tegevuste juurde, veendu, et oled kogu vajaliku tarkvara enda arvutisse paigaldanud läbides Tarkvara paigaldamine peatüki.
Tutvume nüüd SQL päringukeelega ning kastutame seda enda andmebaasis esimese tabeli loomiseks. SQL (ingl Structured Query Language) päringukeelt kasutatakse peamiselt andmebaasidesse tabelite loomiseks, andmete muutimseks, lisamiseks, kustutamiseks ning andmebaasist andmete kättesaamiseks. SQL on loodud IBM poolt aastal 1974.
Alustuseks vaadake üle allolev nimekiri lausete tähistustest, et SQL lausete üldkujust paremini aru saada.
SQL lausete tähistused:
- < > ümbritsevad nimesid
- [ ] mittekohustuslik osa
- { } kohustuslik grupeeritud osa – kõik mis nende vahel on, peab SQL lauses olemas olema
- | alternatiivide eraldaja, üks alternatiiv tuleb valida
- ... kordus
- := see on (defineerimiseks)
- suurtähed - SQL keele reserveeritud sõnad
- väiketähed - muutujad (nimed, literaalid)
Päringud
Tabelites loodud andmeid on vaja ka kuidagi vaadata. Üks moodus andmete vaatamiseks oleks kasutada DBeaveris tabeli 'Data' vaadet. See lahendus ei ole aga väga sobilik, kui me otsime mingeid kindlaid kirjeid tabelist ning meie tabelis on tuhandeid ridu. Parem oleks kasutada lahendust, mis otsib meie eest tabelist ridu, mis meid huvitavad ning kuvab neid meile. Selleks pakub SQL võimalust pärida tabelitest andmeid SELECT käsuga. Selles materjalis vaatamegi 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 õppematerjalis Päringulause (jätk).
Esimeses praktikumis käsitlete kõige lihtsamaid päringuid.
SELECT */{<veeruavaldised>}
FROM {<tabel>}
[WHERE {<tingimus>}];
Näited kõige lihtsamatest päringutest on:
Soovides väljastada kõiki kirjeid ja kõiki tunnuseid näiteks tabelist lemmikloomad:
SELECT * FROM lemmikloomad;
Soovides väljastada ainult ees- ja perenime kõikide omanike tabeli kirjete korral:
SELECT eesnimi, perenimi FROM omanikud;
Selleks, et mingist veerust saada kätte ainult erinevaid väärtuseid, saab kasutada DISTINCT märksõna. Soovides väljastada ainult erinevad nimed kõikide lemmikloomade kirjete korral:
SELECT DISTINCT nimi FROM lemmikloomad;
Ehk siis saame mittekorduvad väärtused. Kui on näiteks meil ka andmebaasis mitmeid koeri, kelle nimi on Pontu, siis ikka see nimi ilmub päringutulemusse ainult ühel korral. Kõige lihtsam viis sellest arusaamiseks ongi proovida DBeaveri SQL konsoolis läbi SELECT käsud erinevate veergude kombinatsioonidega. Proovi DBeaveris erinevaid SELECT käsu kombinatsioone tabelite lauljad ja laulud peal.
Päringuid piirame WHERE lauseosaga, kui meil on vaja vaadata mingeid kindlaid väärtuseid veergudes. WHERE avaldises saab kasutada palju erinevaid operaatoreid. Mõned tuntumad on =, <, >, <=, >= ja <> (!=). Näiteks soovides väljastada ainult ees- ja perenimed kõikide meessoost omanike korral teades, et sugu on tähistatud kas m (meessoost) või n (naissoost):
SELECT eesnimi, perenimi FROM omanikud WHERE sugu='m';
Märksõnale WHERE saab lisada mitu loogilist avaldist. Seda tehakse AND ja OR märksõnadega. AND tähendab, et mõlemal pool märksõna olevad avaldised peavad olema tõesed. OR tähistab, et üks OR sõnaga eraldatud avaldistest peab olema tõene. Mõlemat märksõna võib kasutada ka rohkem kui ühe korra ühes avaldises. Näiteks kui soovime väljastada nende omanike ees- ja perenimesid, kes on meessoost JA kelle sünniaasta (tabelis nimega synniaasta, sest me ei kasuta täpitähti nimedes) on hilisem kui 2000. Seega tabeli kirjetes peavad olema täidetud mõlemad tingimused ehk siis kirjutame:
SELECT eesnimi, perenimi FROM omanikud WHERE sugu='m' AND synniaasta>2000;
Aga kui soovime väljastada nende omanike ees- ja perenimesid, kes on meessoost VÕI kelle sünniaasta (tabelis nimega synniaasta, sest me ei kasuta täpitähti nimedes) on hilisem kui 2000. Seega kui tabeli kirjes on üks nendest tingimustest täidetud, siis see kirje kuvatakse meile päringutulemuses ehk siis kirjutame:
SELECT eesnimi, perenimi FROM omanikud WHERE sugu='m' OR synniaasta>2000;
WHERE lauseosasse kirjutatud loogilist avaldist saab ka vastupidiseks pöörata, kirjutades selle ette NOT märksõna. Näiteks kui soovime lemmikloomade omanikke, kelle elukoht pole Tallinn, siis:
SELECT * FROM omanikud WHERE NOT elukoht='Tallinn';
Operaatoritena saab veel kasutada IN, IS ja BETWEEN märksõnu. BETWEEN operaatoriga saab piirata tunnust kahe väärtuse vahele ning IN operaatorit kasutatakse, et kontrollida, kas tunnuse väärtus on olemas mingis järjendis. Ka neid operaatorid saab vastupidiseks pöörata NOT märksõnaga.
Siin esimesel juhul väljastatakse meile omanikud, kelle sünniaasta on 1990st kuni 2000ni, sest BETWEEN operaator haarab ka otspunkte.
SELECT * FROM omanikud WHERE synniaasta BETWEEN 1990 AND 2000;
Teises näites väljastatakse meile omanikud, kelle elukohaks on kas Tallinn, Tartu, Pärnu või Narva.
SELECT *
FROM omanikud
WHERE elukoht IN ('Tallinn','Tartu','Pärnu','Narva');
Selle viimase oleksime võinud ka kirjutada OR operaatorit kasutades, kuid IN operaatoriga on kirjapilt lühem.
Vaata videost, kuidas koostada päringud DBeaveris:
IS operaatoriga saab otsida tabelist puuduvaid väärtuseid.
Näiteks kui soovime omanikke, kelle elukoht on sisestamata:
SELECT * FROM omanikud WHERE elukoht IS NULL;
Või lemmikloomi, kellel on kiibinumber (ehk siis selle lahtri väärtus pole null):
SELECT * FROM lemmikloomad WHERE kiip IS NOT NULL;
IN ja BETWEEN operaatoreid saab tavaliselt asendada =, <, >, <=, >= ja <> (!=) operaatoritega, aga IN ja BETWEEN kasutamine on tunduvalt mugavam, kuna pole vajadust kirjutada mitut AND või OR lauset järjest.
| Mis faile on vaja esitada iseseisvate tööde jaoks? | LIKE operaator |