ANDMEHALDUS JA ANDMEBAASID
Päringud ja aruanded
Andmebaaside päringkeelte areng algas relatsioonalgebraga ja jõudis struktureeritud päringukeeleni (Structured Query Language - SQL). Relatsioonalgebra on protsessidega seotud protseduurkeel: võib olla vajalik teostada rohkem järjestatud tegevusi soovitava tulemuse saamiseks nagu näidatud järgnevate näidetega paaril leheküljel. SQL on peamiselt mitteprotseduuriline keel: iga korraldus annab tulemuseks soovitud väljundi. Kuidas vastava väljundini jõutakse, see jääb suurelt jaolt andmebaasimootori ülesandeks. See kehtib eriti tõlgendatud SQL kohta, millist kasutavad andmebaaside administraatorid, kuid keeles on konstruktsioone, millised saab kasutada ainult programmeerimiskeele kaudu ja mis võimaldavad kasutada teatud protseduure, mis on olemas ainult standardse SQL sisemises määratluses ehk spetsifikatsioonis (põhiliselt on need käsurea korraldused). [5]
Relatsioonalgebra põhioperatsioonid
Et tõhusalt mõista andmete otsimist, on kasulik alustada relatsioonalgebra mõistetest, uurides tema võimalikke tehteid.
- Kahe tabeli ühend (union). See on ridade hulk (komplekt), mis pärineb vähemalt ühest kahe tabelilisest komplektist. Seda võib mõista, kui tabelit, mis on kokku pandud kahe tabeli erinevatest veergudest.
- Kahe tabeli vahe ehk erinevus (difference). Eeldades, et kahes tabelis on samad veerud, on see ridade hulk (komplekt), mis on olemas esimeses ja puuduvad teises tabelis.
- Otsekorrutis (Descartes'i korrutis, karteesiakorrutis) on ridade hulk (komplekt), mis saadakse asetades kõrvuti ükshaaval iga rea esimesest tabelist ridadega teisest tabelist. Näide kahe alloleva tabeli otsekorrutisest on toodud allolevas teises tabelis. Ehk siis tegemist kõikvõimalike kombinatsioonidega kahe tabeli ridade vahel. [5]
| Kasutajad | Õppeained | |||
|---|---|---|---|---|
| Tudengikood | Nimi | Aine | Tudeng | |
| A01 | Marek | Ajalugu | A01 | |
| A02 | Maria | Ajalugu | A03 | |
| A03 | Ella | Geograafia | A01 | |
| Geograafia | A02 | |||
| Otsekorrutis: Kasutajad ja Oppeained | |||
|---|---|---|---|
| Tudengikood | Nimi | Aine | Tudeng |
| A01 | Marek | Ajalugu | A01 |
| A01 | Marek | Ajalugu | A03 |
| A01 | Marek | Geograafia | A01 |
| A01 | Marek | Geograafia | A02 |
| A02 | Maria | Ajalugu | A01 |
| A02 | Maria | Ajalugu | A03 |
| A02 | Maria | Geograafia | A01 |
| A02 | Maria | Geograafia | A02 |
| A03 | Ella | Ajalugu | A01 |
| A03 | Ella | Ajalugu | A03 |
| A03 | Ella | Geograafia | A01 |
| A03 | Ella | Geograafia | A02 |
- Valik tabelist. See on ridade hulk (komplekt) tabelist, mis rahuldab tingimuste seeriaid, mis on näha valikust endast. Eelmises tabelis on halli taustaga esile toodud read, mis rahuldavad tingimust " Tudengikood " Kasutajate tabelist vastab " Tudeng " Oppeained tabelis. Need read on kopeeritud järgmisse tabelisse.
| Valik otsekorrutisest | |||
|---|---|---|---|
| Tudengikood> | Nimi | Aine | Tudeng |
| A01 | Marek | Ajalugu | A01 |
| A01 | Marek | Geograafia | A01 |
| A02 | Maria | Geograafia | A02 |
| A03 | Ella | Ajalugu | A03 |
- Projektsioon. Projektsioon esindab veergude alamhulka, saadakse otsekorrutisest kõrvaldades veergude mitmekordse esinemise (või ebamäärased atribuudid), seega kõrvaldatakse mitu korda esinevad veerud ja kustutatakse soovimatu informatsiooniga veerud. Eelmises näites esitab " tudeng " veerg sama informatsiooni, mis " Tudengikood " veerg ja ei jää seetõttu alles projektsiooni tehte tulemusena.
| Projektsioon | ||
|---|---|---|
| Tudengikood | Nimi | Aine |
| A01 | Marek | Ajalugu |
| A01 | Marek | Geograafia |
| A02 | Maria | Geograafia |
| A03 | Ella | Ajalugu |
- Konjunktsioon ehk Seotus (liitumine, ühendamine - join). Liitumistehe on põhimõtteliselt otsekorrutis koos järgneva valikuga. Kui valik sisaldab ainult võrdseid tingimusi, siis on tehte nimi "ekvivalentne seos - equi-join". Ekvivalentse seose tulemus on eriline kahe tabeli vaheline liitumine. Kui sellele järgneb projektsioon, on sekke tehte nimeks "loomulik liitumine - natural join". Kaks viimast ülaltoodud näitest saadud tabelit on ekvivalentse seose ja loomuliku liitumise näited.
- Ümbernimetamine. Kasutatakse tabeli veergude ümbernimetamiseks. Näiteks "Tudengikood " veeru võib ümber nimetada "kood" veeruks.
| Ümbernimetamine | ||
|---|---|---|
| Kood | Nimi | Aine |
| A01 | Marek | Ajalugu |
| A01 | Marek | Geograafia |
| A02 | Maria | Geograafia |
| A03 | Ella | Ajalugu |
Andmete määratluskeel (DDL) ja andmete manipuleerimiskeel (DML)
Liikudes relatsioonalgebra juurest SQL juurde peab tähele panema, et see keel sisaldab mitmeid sisemisi osi (komponente). Eriti olulised on:
- Andmete defineerimiskeel või andmete määratluskeel (Data Definition Language - DDL) on SQL osa, mis on mõeldud andmete defineerimiseks. Keel sisaldab korraldusi: create - loo; drop - pilla, kukuta, kaota ja alter - muuda. Need käsud võimaldavad tabelite, vaadete ja indeksite loomist, kõrvaldamist ja muutmist. Neid korraldusi tutvustame põgusalt järgmises lõigus.
- Andmete manipuleerimiskeel (Data Manipulation Language - DML) on SQL osa, mis on mõeldud andmete manipuleerimiseks. Keel sisaldab korraldusi insert - pane vahele, sisesta, lisa; delete - kustuta ja update - uuenda. Need käsud võimaldavad ridasid lisada, kustutada või muuta. Lisaks on korraldus Select - vali, mis võimaldab andmeid pärida.
Toetavad korraldused on seotud turvalisuse ja kasutajate õigustega (grant- andma, tagama; revoke- tühistama), andmetehingutega (commit- soorita, teosta; rollback- keri tagasi; rollforward- keri edasi) või programmeerimiskeele korraldustega (tehted käsureal). [5]
Andmete määratluskeele tähtsamad korraldused
Tabeli loomiskorraldus (create table) võimaldab luua tabelit ja lihtsustatud vorm on:
create table tname (colname coltype [not null], ...);
Siinjuures on kasutatud tähistusi:
tname - loodava tabeli nimi;
colname - veerunimi;
coltype - veeru andmetüüp;
not null - kui on lisatud, siis ei saa veerus olla tühja väärtust ja andmed tuleb sellesse veergu sisestada!
Mõned võimalikud andmetüübid on toodud järgnevas tabelis.
| Tüüp | Tähendus |
|---|---|
| integer | 32 bitine täisarv |
| smallint | 16 bitine täisarv |
| float | 64 bitine ujuvkomaga arv (12 tüvenumbrit) |
| smfloat | 32 bitine ujuvkomaga arv (7 tüvenumbrit) |
| char(n) | String (tekst) n sümbolist |
| varchar(n) | Muutuva pikkusega string (sümbolite suurim arv on n) |
| date | Kuupäev (päevade arv peale 31 Dec. 1899) ( 01 Jaan 1900 on päev 1) (tuntud ka Juliuse kalendri kuupäevana) |
Tabeli loomise käsu juurde peab olema lisatud tabel nimi ja tabeli veergude nimekiri.
Iga veeru jaoks on vajalik näidata andmetüüp ja osutama, kas vastav veerg võib või
ei või olla jäetud tühjaks (omada null - väärtust *).
Null valuetähendab SQL puhul mitte väärtust "0"
(zero value) vaid tühja, ilma sisuta välja!
Programmeerimiskeeles on sellele sarnaseks vasteks tühiviit, tavaelus väärtuse mitteteadmine.
| Näited: |
|---|
| Create table tudengid (tkood integer not null, eesnimi char(10) not null, perenimi char(15) not null, synnipaev date); |
Tabeli koos oma struktuuri ja sisuga tühistamiseks (kustutamiseks) võib kasutada korraldust:
drop table tname;
Kus
tname - kustutatava tabeli nimi.
| Näited: |
|---|
| Drop table tudengid; |
Tabeli struktuuri muutmiseks võib kasutada järgmisi korraldusi:
alter table tname modify ( colname coltype [not null], ...);
alter table tname add ( colname coltype [not null], ...);
alter table tname delete ( colname, ...);
Kus tname, colname, not null tähendus on toodud ülalpool (tabeli loomine)
Esimene korraldus muudab veeru/veergude andmetüüpe.
Andmetüüpide muutmine on alati võimalik, kui tabel on tühi, kuid võib osutuda
võimatuks, kui tabelis on andmed.
Kas see on võimalik, või mitte see sõltub andmete "vahetatavusest":
näiteks on alati võimalik muundada arv stringiks, aga vastupidine on võimalik
ainult siis, kui string esindab arvu. Mõne teisendusega võib kaasneda andmete
kaotus: näiteks stringi muutmine väiksema pikkusega stringiks või float tüüpi
andmete muutmine smfloat tüüpi andmeteks.
Teine korralduste rida võimaldab veeru/veergude lisamist tabelisse: see võib
osutuda võimatuks, kui tabel sisaldab andmeid ja lisatav veerg ei luba null
väärtust (mõni SQL versioon lubab lisamiseks kasutada niinimetatud "vaikeväärtust"
sellise probleemi lahendamiseks).
Kolmas korralduste rida võimaldab veeru tühistamist (kustutamist): kui see
veerg sisaldab andmeid, kustutatakse nad samuti.
| Näited: |
|---|
| Alter table tudengid add (pikkus smallint); |
| Alter table tudengid modify (pikkus float); |
| Alter table tudengid delete (pikkus); |
Järgmist korraldust kasutatakse indeksi loomiseks:
create [unique] index idxname on tname (colname, ...);
kus on kasutatud tähiseid
unique - kui kasutatud, tähendab, et indeks on unikaalne;
idxname - loodava indeksi nimi;
tname - tabelinimi;
colname - veerunimi.
On kasulik teada, et pole võimalik luua unikaalset indeksit tabelile, mis juba sisaldab andmeid, kui andmed ei vasta unikaalse indeksi loomusele.
| Näited: |
|---|
| Create unique index tudengiid on tudengid (tkood); |
| Create index tudenginimi on tudengid (perenimi, eesnimi); |
Järgmise korraldusega saab indeksi tühistada:
drop index idxname;
Kus idxname on kustutatava indeksi nimi.
Indeksi tühistamisel ei loe, kas indeks on unikaalne või mitte. Indeksite muutmisvõimalusi keeles pole: muutmiseks tuleb indeks kustutada ja uuesti luua. Indeksi tühistamine ei põhjusta andmete kaotust, aga tasub teada, et unikaalse indeksi tühistamine võib luua olukorra, kus vahepealne andmete lisandumine võib põhjustada tema taasloomise võimatuse. [5]
| Näited: |
|---|
| drop index tudenginimi; |
SQL andmete manipuleerimiskeele (DML) käsud [5]
Liikudes andmete manipuleerimiskeele (DML) juurde, võib märkida, et on võimalik andmeid tabelisse lisada järgmise korraldusega:
insert into tname [(colname, ...)] values (colval, ...);
kus on kasutatud tähiseid:
tname - tabelinimi, millisesse andmeid lisatakse;
colname - veerunimi, kuhu lisatakse;
colval - konkreetsed andmed, mida lisatakse.
Andmete lisamise operatsioon võib nurjuda mitmel põhjusel: lisatavad andmed dubleerivad unikaalse indeksi väärtusi või sisestatavad andmed ei vasta veeru määratud andmetüübile.
| Näited: |
|---|
| Insert into tudengid (tkood, eesnimi, perenimi) values (12, "Juhan", "Juurikas"); |
Ridade kustutusoperatsioon võimaldab lisada ka kustutamise tingimusi, tutvustades uut süntaksielementi klausel where.
delete from tname [where colname condition {colval|colname} [oprel ...]];
kus on kasutatud tähiseid
Tname - tabelinimi, millisest andmeid kustutatakse;
colname - veerunimi;
condition - tingimusoperaator;
colval - väärtus, mida võrreldakse colname veeru sisuga;
oprel - suhteoperaator (relatsiooniline operaator).
Siin käsitletud tingimused on küllaltki lihtsad. Kõikvõimalikud where klausli tingimused on palju laiemad ja jäävad käesolevast käsitlusest välja.
| Tingimus | Tähendus |
|---|---|
| = | Tingimus on tõene, kui operaatori mõlemad pooled on võrdsed. |
| != | Tingimus on tõene, kui operaatori mõlemad pooled on erinevad. |
| > >= <= < | Tingimus on tõene, kui operaatori mõlemad pooled vastavad näidatud võrratuse tingimusele. |
| like | Tingimus on tõene, kui termini "like" vasak pool sisaldab parempoolseid tingimusi (sümboleid). Võib kasutada metamärke nagu "%" sümbol. Sümbolit % käsitletakse, kui sümbolite mistahes järjestust. |
| matches | Tingimus on tõene, kui vasak pool vastab parempoolsetele tingimustele (sümbolitele). Võib kasutada metamärke. |
| Suhteoperaator | Tähendus |
|---|---|
| and | Avaldis on tõene, kui operaatorile eelnevad ja järgnevad tingimused on mõlemad tõesed. |
| or | Avaldis on tõene, kui vähemalt üks kahest tingimusest on tõene. |
| not | Eitab operaatorile järgnevat tingimust (vastab vastandväärtusele). |
Järgnev näide demonstreerib, kuidas tühistatakse kõik õpilane kirjed Juurikas perekonnanimega, kes on sündinud enne 1. jaanuari 1983. Rea tühistamiseks (kustutamiseks) peab see rahuldama mõlemaid tingimusi ja seepärast kasutatakse and operaatorit.
| Näited: |
|---|
| delete from tudengid where synnipaev <"01.01.1983" and perenimi="Juurikas"; |
Tabeli väärtuste muudatusi võib teha järgmise korraldusega:
update tname set colname=valcol, ... [where colname condition {colval|colname} [oprel ...]];
kus on kasutatud tähiseid
tname - tabelinimi, millisest andmeid kustutatakse;
colname - veerunimi;
valcol - veerule omistatud väärtus;
condition - tingimusoperaator;
colval - väärtus, mida võrreldakse colname veeru sisuga;
oprel - suhteoperaator (relatsiooniline operaator).
SQL lisaklauslite kasutamine
Andmete uuenduskorraldus lubab tabeli mitut rida ja veergu uuendada samaaegselt. Rea valik teostatakse where klausliga, samas peab veeru uuenduse korralduses täpselt näitama.
Järgnevas näites uuendatakse kõikide õpilaste, kelle kood on 2, eesnimi ja perekonnanimi; kui unikaalne indeks on defineeritud koodina (vaadake indeksi definitsiooni juures olevat näidet) siis uuendatakse ainult üks kirje (või mitte ühtki, kui pole õpilast koodiga 2).
| Näited: |
|---|
| update tudengid set perenimi="Juurikas" and eesnimi="Piret" where tkood=2; |
Oleme seni vaadanud andmete muutmiskorraldusi, nüüd hakkame uurima, kuidas tabelitest vajalikku informatsiooni väljastada. Selleks kasutatakse korraldust select, mida laialdaselt kasutatakse ja mis on väga paindlik:
select ( colname, ...) from tname [where ...] [group by (colname, ...)] [order by (colname, ...)] [having ...] [into [temp] table tname];
kus on kasutatud tähiseid
colname - veerunimi;
tname - tabelinimi, millisest andmeid kustutatakse;
where - klausel, millist kirjeldati ülalpool delete ja update korralduste juures;
having - sarnaneb veidi where klausliga, kuid hinnatakse osalisi tulemusi.
order by klausel võimaldab väljastada andmeid korrastatud järjekorras,
samal ajal kui group by klausel võimaldab andmete rühmitamist.
Sellised korraldused, kus on kasutatud kõiki loetletud klausleid rakendatakse
esimesena where, järgmisena order by ja group by
ning alles siis having klauslit.
Teades select korralduse keerukust ja paindlikkust, vaatleme mitut erinevat näidet.
Esimeses näites näitame, kuidas valima kogu informatsioon tabelist: tärn veergude nimekirja asemel osutab,
et kõik tabeli veerud peaks olema valitud. Where klausli puudumine võimaldab valida kõik read.
[5]
| Näited: |
|---|
| Select * from tudengid; |
Teises näites valitakse kõikide õpilaste eesnimed ja perekonnanimed, kes olid vanemad, kui 18 aastat, kuupäeval 31.detsember 2003. Tulemus korrastatakse perekonnanime ja eesnime järgi. Pange tähele: veergude korrastusjärjekord on vaja eraldi näidata order by järel.
| Näited: |
|---|
| select perenimi, eesnimi from tudengid where synnipaev <="31.12.1985" order by perenimi, eesnimi; |
Kolmandas näites on rühmitatud kõik sama nimega õpilased; valitakse nimi koos sama nimega õpilaste arvuga. Loendamisfunktsioon count(*) väljastab rühmitatud ridade arvu. Näites pole kasutatud whereklauslit, kuigi võiks olla.
| Näited: |
|---|
| select eesnimi, count(*) from tudengid group by eesnimi order by eesnimi; |
Neljas näide näitab, kuidas having klauslit kasutada harvaesinevate nimede valimiseks või ainult üks kord esinevate nimede valimiseks.
| Näited: |
|---|
| select eesnimi, count(*) from tudengid group by eesnimi order by eesnimi having count(‘)=1; |
Viiendas näites valitakse õppeainest osa võtvate õpilaste perenimi ja eesnimi. See teostatakse suhte loomise kaudu õpilaste ja oppeainete tabel vahel. Korraldusest aru saamiseks, peaks teadma, et oppeainete tabel sisaldab veerge aine ja tudeng (õpilane osaleb õppeaines). Korraldust uurides võib märgata seal kahte veergu nimega "eesnimi" - need veerud on eristatavad sest tabeli nimi on enne veerunime ja neid seob vahepealne punkt. Sellist süntaksit kasutatakse ka tingimuste näitamiseks where klauslis aga seda on kasutatud ainult selguse huvides (where tudeng = kood võib kirjutada probleemideta). Mõistmaks, kuidas korraldus töötab, vaatame kuidas ABHS seda teostab:
- Leitakse otsekorrutis õppeained ja tudengid tabelite vahel
- valitakse ainult read, kus "tudeng" veerg väärtus võrdub "kood" veerg väärtusega
- luuakse projektsioon, mis kustutab kõik sobimatud veerud
- tehakse päring valitud veergude (oppeained.tudeng, tudengid.eesnimi, perenimi) baasil saadud tabelist.
Kõik need operatsioonid vastavad loomulikule liitumisele (loomulik seos).
| Näited: |
|---|
| select oppeained.eesnimi, perenimi, tudengid.eesnimi from oppeained, order by oppeained.eesnimi, perenimi, tudengid.eesnimi where oppeained.tudeng=tudengid.kood; |
Kuues ja viimane näide kasutab viiendat näidet ja teostab täienduse, mis samuti lubab valida aineid, kus pole registreerunud õpilasi. Sellel juhul read sisaldavad aine andmeid ja neid ridu, kus õpilase andmed puuduvad (kuna aines pole õpilasi). Korralduse seisukohalt on piisav, kui lisada sõna outer enne tudengid tabeli nime. Seda tüüpi seotus on väline liitumine - outer joinvõi vasak liitumine - left join. Välise liitumise süntaks võib olla tunduvalt keerulisem ja võib sõltuma kasutatava SQL keele standardist.
| Näited: |
|---|
| select oppeained.eesnimi, perenimi, tudengid.eesnimi from oppeained, outer tudengid order by oppeained.eesnimi, perenimi, tudengid.eesnimi where oppeained.tudeng=tudengid.kood; |
Vaated ning transaktsioonihaldus
Olles eelnevalt piisavalt uurinud select korraldust, võib nüüd uurida kasutaja korraldust create view. Kasutaja vaade (user view) on funktsionaalsest seisukohast vaade nagu tabel ise, aga see luuakse select korraldusega. Põhiliselt on see võimalus esitada tabeleid erineval viisil. Korralduse süntaks:
create view vname as select ... ;
Kasutatud tähised:
vname - loodava vaate nimi;
select - valikukorraldus, mis näitab, kuidas andmed saadakse.
Näites vaatame vaadet, mis esitab hetke andmed õpilaste kohta ainete nimekirjas (registreerunud õpilaste nimekiri).
| Näited: |
| create view registreerunud as select oppeained.eesnimi, perenimi, tudengid.eesnimi from oppeained, tudengid where oppeained.tudeng=tudengid.kood; |
Vastupidine korraldus vaate loomisele on vaate hävitamiskorraldus, nagu seda sai teha tabelite ja indeksitega, selle süntaks on:
drop view vname;
Kus vname - kustutatava vaate nimi;
| Näited: |
|---|
| drop view registreerunud; |
Nende põgusate selgituste lõpetamiseks näitame andmetehingute juhtimiskorraldusi:
commit work;
rollback work;
Kui automaatne andmetehingute lõpetamine on välja lülitatud (autocommit=off), siis andmete muutmiskorraldused (insert - lisamine, delete - kustutamine, update - uuendus) ei muuda tabeli sisu, selle asemel nad registreerivad vajalikud muutused (tavaliselt logides). Need muutused teostatakse ainult juhul, kui sooritatakse korraldus commit. Kui sooritatakse korraldus rollback, siis muudatused lükatakse tagasi. Selliselt on võimalik luua käskude komplekte, mis täidetakse tervikuna või jäetakse tervikuna täitmata.
