Loo uus tabel
Esimese asjana tuleta meelde eelmises praktikumis õpitut tabeli loomise kohta ning loo enda andmebaasi veel üks tabel, kasutades SQL päringukeelt. Luua tuleb tabel lemmikloomapoed. Ava DBeaver, vaata, et tegutseksid Projects menüüs ning loo ühendus enda andmebaasiga, liikudes General -> Connections -> postgres.
Tabel loo järgnevate tunnustega (Kirjuta see korrektseteks tabeli loomise lauseks!):
Lemmikloomapoed
- Id serial primary key
- Linn varchar(50) not null
- Riik varchar(30) not null
- JuhatajaID integer
- Aktiivne bit not null
- Töötajate_arv integer check (Töötajate_arv < 11)
Kontrolli, et tabel sai loodud korrektselt. Vaata DBeaveris tabeli 'Properties' vaate all, et kõik veerud said loodud õigete andmetüüpide- ning kitsendustega. Kui midagi läks segamini, saad tabeli ära kustutada käsuga (asenda <tabel> tabeli nimega):
DROP TABLE <tabel>;
Massandmete sisestamine failist
Eelmises praktikumis vaatasime, kuidas tabelisse sisestada andmeid ükshaaval kirjete kaupa. Selline lähenemine ei ole aga hea, kui meil on vaja sisestada tabelisse palju andmeid. Selle probleemi lahendamiseks pakub SQL võimalust sisestada palju andmeid tabelisse korraga, kui need andmeid on meil juba mingis teises failis olemas.
Andmete sisestamine tabelisse toimib järgneva lausega:
COPY <tabel> ({veerud}) FROM '<täielik tee failini>' DELIMITER E'\t' ENCODING 'UTF-8';
Väljatoodud lausega on võimalik ära märkida, millistesse veergudesse failis olevaid andmeid sisestada. Seda tasub teha, kui su tabelis on mingi Serial tüüpi veerg. Serial hoiab tabeli puhul meeles indekseid, kuhu ta on mingi arvu juba loonud ning kuhu ta saab järgmise luua. Kui sa oled manuaalseslt ise lisanud Serial veergu mingi arvu, siis see indeks ei uuene ning Serial tahab lisada väärtust reale, kus arv on juba olemas. Selle tagajärjel läheb katki Serial andmetüübi omadus automaatselt suurenevaid arve sinu eest tabelisse lisada.
Faili näidisandmetega saad alla laadida siit (vajuta parema hiireklahviga lingi peale ning sealt salvesta link nimega/save as): poed.txt. Tabelisse lemmikloomapoed andmete sisestamiseks võiks see lause olla järgneval kujul:
COPY lemmikloomapoed (linn, riik, juhatajaid, aktiivne, töötajate_arv) FROM 'C:\Users\Kasutaja\Desktop\poed.txt' DELIMITER E'\t' ENCODING 'UTF-8';
NB! Failitee ei pruugi sul kattuda näites olevaga. Enne kui tahad tabelisse andmeid mingist failist sisestada, pead faili täieliku tee oma arvutis kindlaks tegema.
Probleemide korral võid vaadata videot, kus on näidatud üks võimalik failitee leidmise viis Windows 10 peal.
Tabeli ja kirjete muutmine
Tabelite muutmiseks on erinevaid võimalusi. Üldiselt saab neid jaotada kaheks suuremaks kategooriaks - nimemuutused ja struktuurimuutused.
Nimemuutused
Nimemuutuste all peetakse silmas tabeli enda nime või siis veergude ning kitsenduste nimede muutmist. Peamiselt tuleb nimetatud võimalusi kasutada, et tabelit selgemaks teha. Näiteks juhul, kui mingi tabeli veeru nimi ei anna head ülevaadet veerus olevatest andmetest, oleks mõistlik nime muuta.
Tabeli nime muutmise üldkuju on järgnev:
ALTER TABLE <tabel> RENAME TO <uus nimi>;
Veeru nime muutmiseks tuleks kasutada SQL lauset:
ALTER TABLE <tabel> RENAME COLUMN <vana veeru nimi> TO <uus veeru nimi>;
Ning lõpuks kitsenduse nime muutmine toimub järgneva lausega:
ALTER TABLE <tabel> RENAME CONSTRAINT <vana kitsenduse nimi> TO <uus kitsenduse nimi>;
Struktuurimuutused
Struktuurimuutuste all mõeldakse tabelisse uute veergude ning kitsenduste lisamist ning kustutamist. Veel on võimalik muuta olemasolevate veergude omadusi. Allpool on välja toodud peamised struktuuri muutmise SQL laused tabeli juures.
Tabelisse veeru lisamiseks ja kustutamiseks saab kasutada lauseid:
ALTER TABLE <tabel> ADD COLUMN <veeru definitsioon>;ALTER TABLE <tabel> DROP COLUMN <veeru nimi>;
Näiteks:
ALTER TABLE tudengid ADD elukoht varchar(40) NOT NULL DEFAULT 'Tartu';ALTER TABLE tudengid DROP COLUMN elukoht;
Tabelisse saab kitsendust lisada ja kustutada lausetega:
ALTER TABLE <tabel> ADD CONSTRAINT <kitsenduse nimi> <kitsenduse defintisioon>;ALTER TABLE <tabel> DROP CONSTRAINT <kitsenduse nimi>;
Välisvõtme lisamine
Välisvõti on sisuliselt kitsendus, seega lisame kitsenduse välisvõti. Süntaks:
ALTER TABLE <alamtabel> ADD CONSTRAINT <fk_nimi> FOREIGN KEY ({alamtabeli_veerg}) REFERENCES <ülemtabel>[({võtmeveerg})] [ON DELETE {tegevus}] [ON UPDATE {tegevus}];
Siin alamtabel on see tabel, millest nool lähtub ja ülemtabel, millesse nool suundub. Näiteks meil on lemmikloomad ja eraldi tabel loomaliigid. Viimases on sellised väärtused nagu koer, kass, hamster jms. Neil on omavahel 1:n seos (ühest liigist saab olla mitu looma, aga üks loom kuulub ühe liigi alla). Seega alamtabeliks on lemmikloomad ning ülemtabeliks loomaliigid. Oletame, et lemmikloomade tabelis on välisvõtmeks veerg liigi_id.
ALTER TABLE LemmikloomadADD CONSTRAINT fk_lemmikloomad2liigidFOREIGN KEY liigi_idREFERENCES Loomaliigid(id)[ON DELETE RESTRICT][ON UPDATE CASCADE];
Siin lõime välisvõtme Lemmikloomade tabeli väljast liigi_id Loomaliikide primaarvõtmesse id, milles siis ülemtabelis kustutamine on keelatud, kui alamtabelis on sellist liiki loomi (näiteks ei tohi kustutada liiki hamster, kui Lemmikloomade tabelis on hamstreid), aga muutmisega minnakse kaasa. Viimane siis tähendab, et kui näiteks hamstri id muudetakse (pole enam 3, vaid 5), siis ka kõikidel hamstritel, kes on Lemmikloomade tabelis liigi_id, mis oli 3 muutub 5ks.
Andmetüübi muutmine
Veel on võimalik muuta tabelis oleva veeru defintisiooni (andmetüüp, kitsendused jne). SQL standardi põhjal on see protsess 4-sammuline:
- Tabelisse lisatakse uus veerg
- Vanast veerust kantakse sinna andmed
- Vana veerg kustutatakse
- Uue veeru nimi muudetakse selleks, mis oli vana veeru nimi
Terve see protsess on vajalik, et olemasolevast veerust andmed kaotsi ei läheks. Lihtsalt uut veergu lisades ning vana kustutades läheksid kõik vanas veerus olevad andmed kaotsi. Selle tõttu on ka tähtis veeru definitsioon põhjalikult läbi mõelda tabelit luues, kuna pärast muudatusi teha ei ole väga mugav.
Teeme selle protsessi näite põhjal läbi. Oletame, et meil on tabel tudeng, kus on veerg eesnimi andmetüübiga varchar(6) ning kitsendus not null. Ühel hetkel avastame, et varchar(6) ei ole sobilik nime hoidmiseks, kuna nimed võivad tihti olla pikemad kui 6 sümbolit. Selleks teeme läbi järgneva protsessi:
ALTER TABLE tudeng ADD COLUMN eesnimi_uus varchar(20) not null;UPDATE tudeng SET eesnimi_uus = eesnimi;ALTER TABLE tudeng DROP COLUMN eesnimi;ALTER TABLE tudeng RENAME COLUMN eesnimi_uus TO eesnimi;
Selleks,et kustutada tabelist kõik kirjed tuleb kasutada käsku (tabel ise jääb alles):
DELETE FROM <tabel>;
Lisa see kitsendus tabelile ka DBeaveris.
| Tabelite loomine. Kirjete lisamine ja muutmine | Vaated |