Sissejuhatus andmebaasidesse 2022/23 sügis

Tabeli loomine

Tabeli loomise üldkuju SQL keeles näeb välja järgmine:

• CREATE TABLE <tabeli nimi> ( … );

Sulgudes (komaga eraldatult) tuleb kirja panna veergude definitsioonid ning seejärel tabeli üldised kitsendused. Keskendume praegu veergudele.

Veeru loomise üldkuju on järgnev:

• <veeru nimi> <veeru andmetüüp> [vaikeväärtused] [veeru kitsendused]

Veeru kitsendusi kasutatakse konktreetse veeru võimalike väärtuste piiramiseks. Siinkohal tasub kohe välja tuua neli veeru kitsendust, millega hakkad tihti kokku puutma. Nendeks on NOT NULL, PRIMARY KEY, CHECK (tingimus) ja UNIQUE.

NOT NULL kitsendus veerus ei luba veergu lisada tühja väärtust, ehk tabelisse andmeid sisestades tuleb jälgida, et igas sisestatavas reas oleks kindlasti olemas väärtus vastava kitsendusega veeru jaoks.

CHECK (tingimus) kitsendus lubab meil endil luua mingi tingimuse, mida mingi veerg täitma peaks. Tingimus peab sisaldama veeru nime. Näiteks saame sellega piirata mingi veeru väärtused kindla arvuvahemiku vahele või lubada ainult mingit kindlat sümbolit veergu sisestada.

UNIQUE kitsendus näitab, et selles veerus võivad olla ainult unikaalsed väärtused, ehk väärtused ei tohi korduda.

PRIMARY KEY kitsendus näitab, et antud veerg on tabeli primaarvõti. Primaarvõtit kasutatakse selleks, et igat tabeli kirjet oleks võimalik üheselt tuvastada. Ühel tabelil võib olla üks primaarvõti, aga selle primaarvõtme võib moodustada, kas ühest või mitmest veerust. PRIMARY KEY kitsendusega veerg ei tohi sisaldada tühja väärtust ning ta peab olema unikaalne.

Pole probleemi, kui sa ei saa veel saa aru, mida küsimuses kirjeldatud andmetüübid endast täpsemalt kujutavad. Sellest räägime natuke hiljem. Praegu on tähtis see, et sa saaksid aru, mis osadest koonseb SQL lause tabeli ja veergude loomiseks.

Tabel Lemmikloomad

Tahame luua tabeli nimega Lemmikloomad. Enne, kui loome tabeli, tasub läbi mõelda, millised võiksid olla sellise tabeli tunnused ning tunnuste andmetüübid. Mis omadused kirjeldavad mingit lemmiklooma ning kuidas on hea lemmikloomi eristada? Kas seda omadust pannakse kirja tavaliselt numbrina või kuupäevana?

Üks viis sellist tabelit luua oleks järgnev:

CREATE TABLE Lemmikloomad( 
id serial primary key,
nimi varchar(20) not null,
liik varchar(30) not null,
sugu char(1) not null check (sugu in ('m', 'n')), 
synnipaev date, 
sisestatud timestamp not null default current_timestamp,
PoeID integer not null,
omaniku_isikukood varchar(11) not null);

Enne, kui loome tabeli enda andmebaasis, peame muutma DBeaveris veel ühte seadet, et DBeaver ei muudaks SQL lausetes olevaid suurtähti automaatselt väiketähtedeks. Ava DBeaveris üleval menüüs Window -> Preferences. Avaneb Preferences aken, kus liigu vasakul menüüs Editors -> SQL Editor -> Code Editor ning kontrolli, et valik 'Convert keyword case' ei oleks valitud. Seejärel vajuta nupule Apply and Close.

Loome nüüd tabeli Inimesed DBeaveris. Selleks, et DBeaveris SQL lauseid kirjutada tuleb ülemisel menüüribal avada SQL Editor -> Open SQL console. Kirjuta eelpool olev lause konsooli (soovitatav on alguses see käsk manuaalselt konsooli aknasse kirjutada, et tabeli loomise protsess paremini meelde jääks, samuti mõtle läbi, mida iga rida selles SQL lauses teeb).

SQL lause jooksutamiseks vajuta konsoolist vasakul olevat oranži noolt.

DBeaveris leiad oma loodud tabeli kui lähed General -> Connections -> postgres -> postgres -> Schemas -> public@ -> Tables -> lemmikloomad. Vajutades tabeli peale tekib sul DBeaveri aknas paremale vaade tabelist lemmikloomad nagu pildil on näidatud. Tabeli vaates saad liikuda Properties, Data ja ER Diagrammi vaadete vahel. Properties vaade annab üldise ülevaate tabelis olevates veergudest, Data vaates saad vaadata tabelis olevaid andmeid ning ER Diagrammi kasutatakse andmebaasis olevate tabelite omavaheliste seoste vaatamiseks.

Andmetüübid

Igal tabelis oleval tunnusel peab olema määratud andmetüüp, seega on kasulik teada, millised andmetüüpe PostgreSQL võimaldab kasutada. Allpool olevas tabelis on välja toodud PostgreSQL'is laialdaselt kasutatud andmetüüpid koos selgitustega.

Natuke lähemalt süveneme veel andmetüüpi SERIAL. SERIAL andmetüübil on veel mõned omadused lisaks sellele, et ta määrab veeru andmetüübi. SERIAL suureneb automaatselt, mis tähendab, et SERIAL andmetüübiga veergudele tekivad väärtused automaatselt kui tabelisse andmeid sisestada. Tekkiv väärtus on esimene vaba täisarv, ehk esimesele tabelisse lisatavale reale tekib SERIAL veergu väärtus 0, teisele 1, kolmandale 2 jne. Üldiselt on seda andmetüüpi hea kasutada primaarvõtmete loomisel, kuna SERIAL loob ise unikaalseid väärtuseid ning samas tagab ka, et veergu ei jääks tühja väärtust. Kokkuvõtvalt võib öelda, et SERIAL andmetüüp määrab veerule lisaks andmetüübile ka vaikeväärtuse (automaatselt suurenev täisarv).

Vaikeväärtused ja tabeli kitsendused

Vaikeväärtuseid kasutatakse üldiselt veergude puhul, millele me ise väärtusi alati sisestada ei taha. Vaikeväärtused lisatakse tabelisse automaatselt tabelisse kirjete sisestamisel. Näiteks kui me tahame lisada mingile tabelile veeru Id, kus Id oleks automaatselt suurenev number või kui me tahame hoida tabelis mingi kirje sisestamise kuupäeva. Nendel juhtudel oleks väga mugav, kui need andmed luuakse meie eest ära. Veeru loomisel SQL lauses pannakse vaikeväärtused kirja peale DEFAULT märget.

Mõned tuntumad vaikeväärtused on:

• CURRENT_DATE - sisestamise hetkel olev kuupäev
• CURRENT_TIME - sisestamise hetkel olev kellaaeg
• CURRENT_TIMESTAMP - sisestamise hetkel olev kuupäev koos kellaajaga
• CURRENT_USER - sisestamise hetkel olev aktiivne andmebaasi kasutaja

Tabeli kitsendused hõlmavad üldjuhul rohkem kui ühte veergu. Tabeli loomisel lisatakse need komaga eraldatult peale veergude definitsioone.

Tabeli kitsenduse üldkuju on järgnev:

• CONSTRAINT <kitsenduse nimi> <kitsendus>

Näiteks:

• CONSTRAINT pk_isik PRIMARY KEY (eesnimi, perenimi,synnipaev);

Lisab tabelile primaarvõtme, milleks on kolm veergu. <kitsendus> on siin lauses PRIMARY KEY (eesnimi, perenimi,synnipaev). <kitsendus> alas saab ka kasutada Check(...) kitsendust.

Tabelis kirjete lisamine ja muutmine

Meil on nüüd andmebaasis tabel, mis hoiab endas andmeid lemmikloomade kohta. Selleks, et tabelist ka mingit kasu oleks, peame lisama sinna andmeid. Selleks on kaks võimalust - kirjete ükshaaval lisamine või andmete lisamine massikaupa mingist failist. Selles praktikumis vaatame esimest võimalust.

Tabelisse rea sisestamine üldkuju on järgnev:

• INSERT INTO <tabel> ({veerud}) VALUES ({väärtused});

Veerud ja väärtused on komadega eraldatud. Väärtused peavad kattuma vastava veeru andmetüübiga. Nendeks võivad olla:

• Literalid (numbrid, kümnenderaldajaks punkt)
• Stringid – ülakomade vahel
• Kuupäevad – ülakomade vahel: 'yyyy-mm-dd'
• Muutujad

Lisa SQL konsoolis tabelisse Lemmikloomad järgmine kirje:

INSERT INTO lemmikloomad
(nimi, liik, sugu, synnipaev, PoeID, omaniku_isikukood)
VALUES
('Muri', 
'koer',
'm', 
'2002-02-04',
1,
'38002042715');

Tabelisse Lemmikloomad tekkis nüüd üks rida. Tabelis olevaid ridu saad vaadata käsuga SELECT * FROM lemmikloomad; SQL konsoolis. Pane tähele, milline väärtus tekkis 'sisestatud' veergu. SELECT käskudest räägime põhjalikumalt järgmistes praktikumides.

Proovi lisada tabelisse järgmine rida:

INSERT INTO lemmikloomad
(nimi, liik, sugu, synnipaev, PoeID)
VALUES
('Miisu', 
'kass',
'n', 
'2003-05-05',
2);

Tõenäoliselt see käsk sul läbi ei läinud ning SQL konsool viskas errori. Selle errori sõnum on lühike ning küllaltki põhjalik. Loe see läbi ning mõtle, mis probleem selle kirje sisestamisega on (vaata, mis on kirjas 'Detail:' real). Tuleta meelde, mis kitsendused tabeli veergudel on ning võrdle tabelis juba olevat kirjet sellega.

Üldjuhul on veerukitsendustega seotud errorid lihtsasti lahendatavad, kuna errori sõnumis on täpselt välja toodud, millise väärtusega on probleem ning selle põhjal saab vastavalt veeru väärtust muuta.

Lisage tabelisse Lemmikloomad veel kaks rida. Valige väärtused veergudele ise.

Kirjete muutmine

Mõnikord on vaja tabelis olevaid ridu muuta, kui mõni väärtus sai näiteks valesti sisestatud. Seda saab teha järgneva käsuga:

• UPDATE <tabel> SET <veerg> = <väärtus> WHERE <tingimus>;

<tingimus> määrab ära, milliste kirjete korral muudetus tehakse. <veerg> alasse kirjutatakse veeru nimi, kus muudatust tahetakse teha ning <väärtus> on uus soovitud väärtus sinna veergu.

Näiteks:

• UPDATE Lemmikloomad SET nimi = 'Krants' WHERE nimi = 'Muri';

See lause muudab veerus nimi kõik kirjed, kus nimi on 'Muri', uueks väärtuseks 'Krants'.

Kustutada saab kirjeid käsuga:

• DELETE FROM <tabel> WHERE <tingimus>;

Näiteks:

• DELETE Lemmikloomad WHERE nimi = 'Krants';

See käsk kustutab tabelist Lemmikloomad kõik kirjed, kus eesnime väärtuseks on 'Krants'.