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 definitsioon on kujul:
*<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 koosneb 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,
kiip varchar(30)
sugu char(1) not null check (sugu in ('i', 'e')),
synnipaev date,
sisestatud timestamp not null default current_timestamp,
omaniku_id integer);
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 Lemmikloomad 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.
| Andmetüübi nimetus | Selgitus |
|---|---|
| char (n) | sümbolite jada fikseeritud pikkusega n, ehk char(1) lubab tabeli veergu kirjutada täpselt ühe sümboli |
| varchar (n) | sümbolite jada, kus jada pikkusesks võib olla kuni n sümbolit |
| text | sümbolite jada, kus pikkus ei ole ette määratud |
| integer | 4-baidine täisarv |
| bigint | 8-baidine täisarv |
| serial | 4-baidine täisarv, mis suureneb automaatselt |
| numeric | reaalarv |
| real | 4-baidine reaalarv |
| timestamp | kuupäev ja kellaaeg |
| date | kuupäev kujul 'yyyy-mm-dd' |
| time | kellaaeg |
| bit (n) | bittide jada fikseeritud pikkusega n, biti väärtuseks võib olla kas 0 või 1, näiteks bit(3) lubab kirjutada veergu 101 |
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 definitsioon on järgnev:
CONSTRAINT <kitsenduse nimi> <kitsendus>
Näiteks:
CONSTRAINT pk_isik PRIMARY KEY (eesnimi, perenimi, synnipaev);
Siin on kirjeldatud primaarvõti, milles 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, sugu, synnipaev, omaniku_id)
VALUES
('Muri',
'i',
'2002-02-04',
1;
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.
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'. DELETE lauses on WHERE tingimus väga oluline, sest kui see unub, kustutatakse kõik kirjed tabelist.
| Päringud mitmest tabelist | Tabelite muutmine |