1. No category

Luku 5
SQL
T¨
am¨
an luvun tarkoituksena on perehdytt¨
a¨
a lukija SQL-kieleen1 . Asiat on
pyritty esitt¨
am¨
a¨
an siten, ett¨
a SQL:n omaksuminen ei vaadi relaatioalgebran
hallintaa, mutta ennen SQL:¨
a¨
an perehtymist¨
a on kuitenkin syyt¨
a tutustua
ainakin karteesisen tulon k¨
asitteeseen. Esityksess¨
a on my¨
os pyritty yksinkertaisuuteen, joten syntaksin l¨
apik¨
aymisen sijasta SQL esitell¨
a¨
an l¨
ahinn¨
a idiomeja eli kielelle tyypillisi¨
a ilmauksia k¨
aytt¨
aen. SQL:n syntaksi on muutenkin
hyvin laaja ja useimmat tietokannanhallintaj¨
arjestelm¨
at poikkeavat standardista, joten tietty¨
a tietokannahallintaj¨
arjestelm¨
a¨
a k¨
aytett¨
aess¨
a on syyt¨
a tutustua
tietokannanhallintaj¨
arjestelm¨
an manuaaleihin.
5.1
5.1.1
SQL:n perusteita
SQL:n tietotyypit
M¨
a¨
aritelt¨
aess¨
a attribuutteja SQL:n avulla on jokaiselle attribuutille
m¨
a¨
aritelt¨
av¨
a jokin tietotyyppi, joka kertoo, millaisia arvoja kyseinen
attribuutti voi saada. Tietotyypeilt¨
a¨
an SQL vastaa joitain poikkeuksia lukuunottamatta tavallisimpia ohjelmointikielti¨
a, kuten esimerkiksi Javaa. SQL:n
t¨
arkeimm¨
at numeeriset tyypit ovat seuraavat:
1. SMALLINT eli kahden tavun avulla tallennettava etumerkillinen kokonaisluku.
2. INTEGER eli INT on nelj¨
an tavun avulla tallennettava etumerkillinen
kokonaisluku.
3. REAL vastaa nelj¨
an tavun avulla tallennettavaa etumerkillist¨
a reaalilukua.
4. DOUBLE PRECISION eli DOUBLE on kahdeksan tavun avulla tallennettava
etumerkillinen reaaliluku.
1 Tarkasti ottaen luvussa perehdyt¨
a¨
an PostgreSQL-tietokannanhallintaj¨
arjestelm¨
an tukemaan SQL-murteeseen [5], jonka perustana on vuonna 1992 esitelty SQL2-standardi. Ensimm¨
aisen kerran SQL standardoitiin 1986 ja vuonna 1999 esiteltiin uusi standardi SQL3,
joka sis¨
alt¨
a¨
a mm. joitakin olio-ominaisuuksia. K¨
ayt¨
ann¨
oss¨
a SQL3:n merkitys on kuitenkin
j¨
a¨
anyt v¨
ah¨
aiseksi.
31
5. DECIMAL(P ituus, Desimaalit), miss¨
a P ituus on luvun numeropositioiden kokonaism¨
a¨
ar¨
a ja Desimaalit desimaalien m¨
a¨
ar¨
a, on k¨
aytt¨
aj¨
an
m¨
a¨
arittelem¨
an tarkkuuden etumerkillinen reaaliluku.
Tietotyyppi DECIMAL on k¨
ayt¨
ann¨
ollinen esimerkiksi silloin, kun on
tallennettava raham¨
a¨
ari¨
a. Pankkitilin saldo voitaisiin m¨
a¨
aritell¨
a tyypin
DECIMAL(9,2) avulla; tilin maksimisaldo olisi siten 9999999.99. T¨
arkeimm¨
at
merkkijonotyypit ovat seuraavat:
Merkkijonoja varten SQL:ss¨
a on m¨
aa
¨ritelty koko joukko erilaisia
tietotyyppej¨
a, joiden tallennuskoko voi olla joko kiinnitetty kuten numeerisilla
tyypeill¨
a tai vaihteleva.
1. CHARACTER(P ituus) eli CHAR(P ituus) vastaa merkkijonoa, jonka
maksimipituus on P ituus ja jonka tallentamiseen k¨
aytet¨
a¨
an aina P ituus
tavua.
2. CHARACTER VARYING(P ituus) eli VARCHAR(P ituus) vastaa vaihtelevan
pituista merkkijonoa, jonka maksimipituus on P ituus ja jonka
tallentamiseen k¨
aytet¨a¨
an enint¨
a¨
an P ituus tavua2 .
3. TEXT vastaa rajoittamattoman pituista merkkijonoa, jonka tallentamiseen
k¨
aytet¨
a¨
an vaihteleva m¨
a¨
ar¨
a tavuja3 .
Kaikissa merkkijonotyypeiss¨
a SQL erottelee toisistaan isot ja pienet
kirjaimet, joten esimerkiksi ”Jaskan vimpain” ja ”jaskan vimpain” eiv¨
at ole
ekvivalentit. Valittaessa tietotyyppi¨
a merkkijonolle on syyt¨
a mietti¨
a, millaisia
tallennettavat merkkijonot yleens¨
a ovat. Jos merkkijonojen pituus vaihtelee
suuresti, on levytilan s¨
a¨
ast¨
amiseksi syyt¨
a valita VARCHAR tai TEXT. Jos taas
tallennettavat merkkijonot ovat useimmiten yht¨
a pitki¨
a, kuten esimerkiksi
autojen rekisterinumerot, on kannattavampaa valita CHAR. Tallennettujen
merkkijonojen pituustietojen tarkistamiseen ja yll¨
apitoon nimitt¨
ain kuluu
jonkin verran ylim¨
a¨
ar¨
aist¨
a aikaa, mik¨
a hidastaa kyselyit¨
a ja p¨
aivityksi¨
a.
P¨
aiv¨
am¨
a¨
arien ja kellonaikojen tallentamiseen on SQL:ss¨
a on m¨
a¨
aritelty
seuraavat tyypit:
1. DATE vastaa p¨
aiv¨am¨
a¨
ar¨
a¨
a; p¨
aiv¨
am¨
a¨
ar¨
at esitet¨
a¨
an useimmiten
amerikkalaisittain eli muodossa YYYY-MM-DD. 1980-07-04 vastaa
siten p¨
aiv¨
am¨
a¨
ar¨
a¨
a 4.7. 1980.
2. Tyyppi TIME vastaa kellonaikaa sekuntin tarkkuudella. Useimmissa tietokannanhallintaj¨
arjestelmiss¨
a kellonajat ilmoitetaan muodossa
HH:MM:SS, eli esimerkiksi muodossa 04:05:06. K¨
ayt¨
ann¨
oss¨
a kaikki tietokannanhallintaj¨
arjestelm¨
at sallivat kellonaikojen tallentamisen tarvittaessa my¨
os millisekuntin tarkkuudella. T¨
all¨
oin kellonaika ilmaistaan useimmiten esimerkiksi muodossa 04:05:06.789.
3. TIMESTAMP eli aikaleima on tyyppien DATE ja TIME yhdistelm¨
a.
2 K¨
ayt¨
ann¨
oss¨
a tallentamiseen tarvitaan muutamia tavuja enemm¨
an, sill¨
a my¨
os tallennetun
merkkijonon pituus on tallennettava
3 Tyyppi TEXT ei oikeastaan kuulu SQL2-standardiin.Valtaosa nykyisist¨
a tietokannahallintaj¨
arjestelmist¨
a kuitenkin kuitenkin tukee t¨
at¨
a tietotyyppi¨
a.
32
Edell¨
a esiteltyjen tietotyyppien lis¨
aksi monissa tietokannanhallintaj¨
arjestelmiss¨
a on m¨
a¨
aritelty useita muita, toisinaan varsin erikoisiakin
tietotyyppej¨
a. Monissa j¨
arjestelmiss¨
a bin¨
a¨
aridatan, kuten esimerkiksi kuvien,
videoiden ja ¨
a¨
anitiedostojen, tallentamiseen tarjotaan omaa tietotyyppi¨
a;
esimerkiksi PostgreSQL:ss¨
a voidaan k¨
aytt¨
a¨
a tyyppi¨
a BYTEA eli rajoittamattoman pituista tavutaulukkoa, jonka tallennetamiseen k¨
aytet¨
aa
¨n vaihteleva
m¨
a¨
ar¨
a tavuja. BYTEA vastaa siis tyyppi¨
a TEXT sill¨
a erotuksella, ett¨
a mill¨
a¨
an
merkeill¨
a, kuten esimerkiksi sisennyksill¨
a ja rivinvaihdoilla, ei ole erityismerkityst¨
a. PostgreSQL:ss¨
a on mahdollista k¨
aytt¨
a¨
a my¨
os useita geometrisi¨
a tietotyyppej¨
a, kuten pisteit¨
a, suoria, ympyr¨
oit¨
a ja polygoneja. T¨
am¨
a saattaa olla
hyvinkin hy¨
odyllist¨
a, jos tietokannalla on tarkoitus k¨
asitell¨
a geometrist¨
a dataa.
Java-yhteensopivana mainostettu McKoi-tietokanta taas tarjoaa tietotyypin
JAVA OBJECT, joka vastaa sarjallistettua Java-oliota.
5.1.2
Kolmiarvologiikka ja NULL
Paitsi attribuuttien arvona, m¨
a¨
arittelem¨
at¨
on arvo NULL esiintyy relaatiotietokantojen yhteydess¨
a my¨os totuusarvona. Logiikkaa, jossa perinteisten totuusarvojen TRUE ja FALSE lis¨
aksi esiintyy my¨
os m¨
a¨
arittelem¨
at¨
on arvo NULL, kutsutaan kolmiarvologiikaksi. Useimmissa tapauksissa arvon NULL voi huoletta
samaistaa totuusarvoon ”ehk¨
a”, mutta joissakin tilanteissa - kuten esimerkiksi alikyselyiden yhteydess¨
a - kolmiarvologiikka saattaa tuottaa yll¨
atyksi¨
a. Siksi
asiaan on syyt¨
a perehty¨
a hieman tarkemmin.
Negaatio NOT toimii oikeastaan hyvin johdonmukaisesti. NOT FALSE = TRUE
ja NOT TRUE = FALSE. NOT NULL puolestaan on NULL, mit¨
a voi perustella
esimerkiksi siten, ett¨
a ”ehk¨
a ei” tarkoittaa oikestaan samaa kuin ”ehk¨
a”.
Konjunktion AND ja disjuktion OR kolmiarvologiikan mukaiset totuustaulut 5.1
ja 5.2 puolestaan saattavat n¨
aytt¨
a¨
a monimutkaisilta, mutta operaatiot toimivat
oikeastaan t¨
aysin kuten bin¨
a¨
arisen logiikan vastineensa. Perinteiseen tapaan
FALSE AND Arvo = FALSE, TRUE AND Arvo = Arvo, FALSE OR Arvo = Arvo
ja TRUE OR Arvo = TRUE mille tahansa totuusarvolle Arvo, siis my¨
os arvolle
NULL.
Taulukko 5.1: Konjunktion AND kolmiarvologiikan mukainen totuustaulu.
FALSE NULL
TRUE
FALSE FALSE FALSE FALSE
FALSE NULL
NULL
NULL
TRUE
FALSE NULL
TRUE
Taulukko 5.2: Disjunktion OR kolmiarvologiikan mukainen totuustaulu.
FALSE NULL TRUE
FALSE FALSE NULL TRUE
NULL
NULL TRUE
NULL
TRUE
TRUE TRUE
TRUE
Kun NULL esiintyy totuusarvon sijasta attribuutin arvona, se voidaan
33
tulkita tuntemattomaksi arvoksi. T¨
ast¨
a seuraa muutamia huomionarvoisia
seikkoja erityisesti silloin, kun NULL-arvoa vertaillaan muihin arvoihin. Jos
toinen vertailtavista arvoista nimitt¨
ain on NULL, on vertailun tulos aina
NULL. Esimerkiksi vertailut 7 < NULL, ’Orimatti’ = NULL ja jopa NULL
= NULL evaluoituvat siten totuusarvoksi NULL, mik¨
a saattaa aluksi tuntua
kummalliselta. Jos siis haluamme tarkastaa, onko jokin arvo tosiaan NULL, on
meid¨
an k¨
aytett¨
av¨
a IS NULL -vertailua; NULL IS NULL evaluoituu totuusarvoksi
TRUE.
5.2
Tietokannan m¨
a¨
arittely SQL:n avulla
5.2.1
Tietokannan luominen
Tietokannan luominen SQL:n avulla on hyvin yksinkertaista. Seuraavalla SQLkomennolla voimme luoda tietokannan, jonka nimi on Firma:
CREATE DATABASE Firma;
Yleisess¨
a tapauksessa komento on muotoa
CREATE DATABASE T ietokanta;
Edellisess¨
a komennossa T ietokanta on tietenkin luotavan tietokannan nimi.
Monissa tietokannanhallintaj¨
arjestelmiss¨
a luontioperaatiolle voidaan antaa
lis¨
am¨
a¨
areit¨
a, kuten esimerkiksi k¨
aytett¨
avien levyblokkien koko. Tietokanta
voidaan tuhota komennolla
DROP DATABASE T ietokanta;
Kannattaa huomata, ett¨
a tietokannanhallintaj¨
arjestelm¨
at eiv¨
at pyyd¨
a
k¨
aytt¨
aj¨
a¨
a vahvistamaan tietokannan tuhoamista, joten h¨
at¨
ainen k¨
aytt¨
aj¨
a voi
helposti saada aikaan paljon vahinkoa. T¨
allaisten tilanteiden v¨
altt¨
amiseksi
tietokannanhallintaj¨
arjestelm¨
at tarjoavat mahdollisuuden luoda k¨
aytt¨
aji¨
a ja
rajoittaa k¨
aytt¨
ajien oikeuksia.
5.2.2
Taulun luominen
Tauluja voidaan luoda komennolla CREATE TABLE. Seuraava komento luo
kuvassa 2.3 esitetyn tietokannan Asiakas-relaatiota vastaavan taulun:
CREATE TABLE Asiakas (
Id INT NOT NULL,
Etunimi VARCHAR32 NOT NULL,
Sukunimi VARCHAR32 NOT NULL,
PRIMARY KEY (Id)
);
Taulu luodaan siis yksinkertaisesti luettelemalla attribuutit ja niiden
tyypit. Lis¨
am¨
a¨
are NOT NULL kertoo, ett¨
a kyseisen attribuutin arvoksi ei
voida asettaa m¨
a¨
arittelem¨at¨
ont¨
a arvoa NULL. PRIMARY KEY taas m¨
a¨
arittelee
tietysti taulun p¨
a¨
aavaimen. Entiteettieheyden takaamiseksi avainattribuutit 34
eli p¨
aa
aa
a NOT
¨avaimeen kuuluvat attribuutit - on tietysti varustettava m¨
¨reell¨
NULL. Jos n¨
ain ei tehd¨
a, ilmoittavat useimmat tietokannanhallintaj¨
arjestelm¨
at
virheest¨
a. Monet tietokannanhallintaj¨
arjestelm¨
at sallivat muitakin lis¨
am¨
a¨
areit¨
a,
kuten esimerkiksi oletusarvon m¨
a¨
arittelyn. Yleisesti komento CREATE TABLE on
muotoa
CREATE TABLE T aulu (
Attribuutti1 T yyppi1 [NOT NULL] [DEFAULT Arvo1 ],
...
Attribuuttin T yyppin [NOT NULL] [DEFAULT Arvon ],
[PRIMARY KEY (Avain1 , ..., Avainm )],
[FOREIGN KEY (V iite1 , ..., V iitel ) REFERENCES Kohde
ON DELETE (CASCADE | RESTRICT | SET DEFAULT)
ON UPDATE (CASCADE | RESTRICT | SET DEFAULT)],
...
);
Edellisess¨
a komennossa valinnaiset osat on kirjoitettu merkkien [ ja ]
sis¨
aa
a¨
aritell¨
a¨
an FOREIGN KEY -osassa. Kos¨n4 . Taulua luotaessa viitevavaimet m¨
ka relaatiotietokannan on oltava ehe¨
a, taulun viitevaimia m¨
a¨
aritelt¨
aess¨
a on otettava kantaa my¨
os siihen, miten viite-eheyden rikkoviin p¨
aivitysoperaatioihin
suhtaudutaan. Viite-eheys saattaa rikkoutua kahdella tavalla: viittauksen kohteena olevan monikon avainattribuuttien arvoja voidaan muuttaa tai viittauksen kohteena oleva monikko voidaan tuhota. Viiteavainm¨
a¨
arittelyn ON UPDATE
-osa liittyy luonnollisesti edelliseen tapaukseen ja ON DELETE j¨
alkimm¨
aiseen.
Molemmissa tapauksissa voidaan k¨
aytt¨
a¨
a seuraavia m¨
a¨
arittelyj¨
a:
1. CASCADE eli vy¨
orytys tarkoittaa sit¨
a, ett¨
a muutokset propagoidaan
viittaaviin monikoihin. ON UPDATE -tapauksessa eli silloin, jos jonkin
monikon p¨
a¨
aavaimen arvoa muutetaan, muutetaan vastaavasti my¨
os
viittaavien monikoiden viiteavainten arvoja. ON DELETE -tapauksessa
eli silloin, kun monikko tuhotaan, johtaa CASCADE-m¨
a¨
arittely kaikkien
tuhottavaan monikkoon viittaavien monikkojen tuhoamiseen.
2. RESTRICT-m¨
a¨
arittely est¨
a¨
a viite-eheyden rikkovat operaatiot. Tietokannanhallintaj¨
arjestelm¨
a ei t¨
all¨
oin suorita viite-eheytt¨
a rikkovia operaatioita vaan ilmoittaa virheest¨
a.
3. SET DEFAULT asettaa viite-eheyden rikkoutuessa viiteavaimen arvoksi attribuuttim¨
a¨
arittelyn DEFAULT-osassa m¨
a¨
aritellyn oletusarvon. Esimerkiksi yrityksen tietokannassa, jossa jokainen ty¨
ontekij¨
amonikko viittaa ty¨
ontekij¨
an esimiest¨
a vastaavaan monikkoon, voisimme m¨
aa
a
¨ritell¨
”oletusesimiehen”. Jos ty¨
ontekij¨
an esimiest¨
a vastaava rivi siis poistettaisiin, asettaisi tietokannanhallintaj¨
arjestelm¨
a ty¨
ontekij¨
an esimieheksi t¨
am¨
an
oletusesimiehen.
Edell¨
a listatuista vaihtoehdoista on jokaiselle viiteavaimelle valittava sopivin
vaihtoehto. ON UPDATE -tapauksessa on luonnollisinta valita joko CASCADE tai
4 P¨
a¨
aavaimen m¨
a¨
aritteleminen siis on SQL-standardin mukaan vapaaehtoista, mutta
yleens¨
a avain kannattaa m¨
a¨
aritell¨
a. Ilman p¨
a¨
aavaimiahan ei voi olla viiteavaimia ja ilman
viiteavaimia ei moneen tauluun hajautettua tietoa voida k¨
atev¨
asti hallita.
35
RESTRICT. CASCADE sopii tietysti paremmin tapauksiin, joissa avaimia joudutaan
toisinaan p¨
aivitt¨
am¨
a¨
an5 . Jos viiteavain taas viittaa pysyv¨
a¨
an avaimeen, kuten
esimerkiksi sosiaaliturvatunnukseen, voidaan valita RESTRICT.
ON DELETE -tapaus tuottaa usein huomattavasti enemm¨
an p¨
a¨
anvaivaa. Miten
esimerkkitapauksessamme pit¨
aisi menetell¨
a esimerkiksi silloin, kun Toimittajataulusta tuhotaan rivi, johon jotkin Tuote-taulun rivit viittaavat? Jos haluamme
tuhota tuhottavaan Toimittaja-taulun monikkoon viittaavat Tuote-taulun
monikot, m¨
a¨
arittelemme Tuote-taulun seuraavasti:
CREATE TABLE Tuote (
Id INT NOT NULL,
Nimi VARCHAR32 NOT NULL,
Kuvaus VARCHAR64 NOT NULL,
Hinta DECIMAL(7,2) NOT NULL,
ToimittajaId INT NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (ToimittajaId) REFERENCES Toimittaja
ON UPDATE CASCADE ON DELETE CASCADE
);
On t¨
arke¨
a¨
a huomata, ett¨
a viite-eheysrikkomuksiin otetaan kantaa nimenomaan sen taulun m¨
a¨
arittelyss¨
a, jossa viiteavaimet sijaitsevat. T¨
am¨
a johtuu siit¨
a, ett¨
a yhteen ja samaan p¨a¨
aavaimeen voi viitata useampi vierasavain. Tilaustaulu voitaisiin esimerkiksi m¨
a¨
aritell¨
a seuraavasti:
CREATE TABLE Tilaus (
AsiakasId INT NOT NULL,
TuoteId INT NOT NULL,
PRIMARY KEY (AsiakasId, TuoteId),
FOREIGN KEY (AsiakasId) REFERENCES Asiakas
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (TuoteId) REFERENCES Tuote
ON UPDATE CASCADE ON DELETE RESTRICT
);
Jos Asiakas-taulusta siis tuhotaan rivi, tuhotaan my¨
os siihen viittaavat Tilaus-taulun rivit ja jos asiakkaan attribuuttia Id p¨
aivitet¨
a¨
an,
p¨
aivitet¨
a¨
an my¨
os viittaavien Tilaus-taulun rivien vierasavainattribuuttia
AsiakasId. Samalla tavoin p¨
aivitet¨
aa
¨n Tilaus-taulun toisen vierasavainattribuutin TuoteId arvoa, jos viittauksen kohteena olevan Tuote-taulun rivin
p¨
a¨
aavainattribuutin Id arvoa p¨
aivitet¨
a¨
an. ON DELETE RESTRICT -m¨
a¨
arittely
taas est¨
a¨
a sellaisten Tuote-taulun rivien tuhoamisen, joihin jokin Tilaus-taulun
rivi viittaa.
Taulua voidaan my¨
ohemmin muuttaa ALTER TABLE -komennolla, jonka
avulla voidaan esimerkiksi muuttaa relaation kaavaa eli lis¨
at¨
a tai poistaa
attribuutteja. Taulu tuhotaan seuraavasti:
DROP TABLE T aulu;
5 Jos avaimia joudutaan p¨
aivitt¨
am¨
a¨
an usein, on syyt¨
a mietti¨
a, onko relaation p¨
a¨
aavain
valittu oikein.
36
5.2.3
K¨
aytt¨
aj¨
an luominen
Kun
tietokannanhallintaj¨
arjestelm¨
a
asennetaan,
luodaan
samalla
k¨
aytt¨
aj¨
atunnus tietokannan hallinnasta vastaavalle k¨
aytt¨
aj¨
alle eli DBA:lle.
PostgreSQL:ss¨
a DBA:n tunnus on oletusarvoisesti postgres. Koska DBA:n
oikeudet ovat k¨
ayt¨
ann¨
oss¨
a rajoittamattomat, on tietokannahallintaj¨
arjestelm¨
an
tarjottava mahdollisuus v¨
ah¨
aisemmill¨
a oikeuksilla varustettujen k¨
aytt¨
ajien
luomiseen. Voimme esimerkiksi luoda asiakask¨
aytt¨
aj¨
an seuraavasti:
CREATE USER Asiakas WITH PASSWORD 123abc;
Yleisess¨
a tapauksessa komento on muotoa:
CREATE USER T unnus WITH PASSWORD Salasana;
Edellisess¨
a komennossa T unnus on tietenkin k¨
aytt¨
aj¨
atunnus ja Salasana
k¨
aytt¨
aj¨
an salasana. K¨
aytt¨
ajille voidaan antaa erilaisia oikeuksia tietokantojen
ja taulujen k¨
asittelyyn, luomiseen ja tuhoamiseen joko k¨
aytt¨
aji¨
a luotaessa tai
my¨
ohemmin k¨
askyll¨
a GRANT. Esimerkiksi kaikille verkkopankin tai verkkokaupan
k¨
aytt¨
ajille ei luoda omaa k¨
aytt¨
aj¨
atunnusta, vaan kaikki verkkopalvelun
k¨
aytt¨
aj¨
at toimivat samalla k¨
aytt¨
aj¨
atunnuksella ja samoilla, varsin rajatuilla
oikeuksilla. K¨
aytt¨
aj¨
a tuhotaan komennolla:
DROP USER T unnus;
5.2.4
Hakemiston luominen
Hakemisto eli indeksi on rakenne, jonka tarkoituksena on nopeuttaa
tiedon hakemista tietokannasta. Toisaalta hakemistot taas hidastavat
p¨
aivitysoperaatioita, joten jokaisen hakemiston luomista on punnittava tarkasti.
Seuraava komento luo hakemiston, jonka avulla voidaan tehokkasti hakea
asiakkaita nimen perusteella:
CREATE INDEX Nimi ON Asiakas(Sukunimi,Etunimi);
Edellinen hakemisto mahdollistaa tehokkaat haut tilanteessa, jossa
asiakkaita haetaan joko sukunimen tai sek¨
a sukunimen ett¨
a etunimen
perusteella. Hakemisto ei kuitenkaan nopeuta pelk¨
an etunimen perusteella
hakemista. Yleisess¨
a tapauksessa komento on seuraavaa muotoa:
CREATE INDEX N imi ON T aulu(Attribuutti1 ,...,Attribuuttin );
Hakemisto voidaan my¨
ohemmin m¨
aa
a ryv¨
ast¨
av¨
aksi eli klus¨ritell¨
teroiduksi indeksiksi eli indeksiksi, jonka mukaan taulun rivit on fyysisesti
j¨
arjestetty kiintolevylle. Oletuksena ryv¨
ast¨
av¨
a indeksi luodaan taulun avainattribuuteille, mutta joissakin tapauksissa on edullista valita jotkin muut attribuutit. Tyypillisimmill¨
a¨
an ryv¨
ast¨
av¨
a hakemisto luodaan silloin, kun taulusta haetaan usein monikkoja k¨
aytt¨
aen hakuehtona arvov¨
ali¨
a, kuten esimerkiksi hintav¨
ali¨
a tai hintaluokkaa. Esimerkkitietokannassamme saattaisi siten olla hy¨
odyllist¨
a rakentaa ryv¨
ast¨
av¨
a indeksi Tuote-taulun attribuutille Hinta.
Esimerkiksi PostgreSQL ei kuitenkaan tarjoa mahdollisuutta m¨
a¨
aritell¨
a indeksi¨
a ryv¨
ast¨
av¨
aksi; vain p¨
a¨
aavaimelle automaattisesti luotava indeksi siis on
ryv¨
ast¨
av¨
a. Kiintolevylle talletettujen monikkojen j¨
arjest¨
aminen uudelleen on
kuitenkin PostgreSQL:ss¨
akin mahdollista. Kun taululle T aulu on luotu indeksi
N imi, voidaan monikot j¨
arjest¨
a¨
a hakemistoattribuuttien mukaan seuraavasti:
37
Id
Asiakas
Etunimi Sukunimi
1
2
3
Orimatti
Laupias
Hanhikki
Teuras
Psaltari
Hillosilm¨
a
Etunimi
Sukunimi
Orimatti
Laupias
Hanhikki
Teuras
Psaltari
Hillosilm¨
a
Kuva 5.1: Esimerkki SQL kyselyn toiminnasta.
CLUSTER N imi ON T aulu;
Hakemisto tuhotaan komennolla:
DROP INDEX N imi;
5.3
Kyselyt SQL:n avulla
5.3.1
Yhteen tauluun kohdistuvat kyselyt
Kuten relaatioalgebran operaatioidenkin kohdalla, my¨
os SQL-kyselyn tulos on
aina relaatio eli taulu. Peruskysely koostuu SELECT-, FROM- ja WHERE-osista,
joiden merkitys on seuraava:
1. SELECT-osassa luetellaan tulostauluun valittavat sarakkeet. Jos halutaan
valita kaikki sarakkeet, voidaan k¨
aytt¨
a¨
a jokerimerkki¨
a *.
2. FROM-osassa m¨
a¨
aritell¨a¨
an, mist¨
a taulusta sarakkeita valitaan.
3. WHERE-osassa m¨
a¨
aritell¨
a¨
an mitk¨
a rivit FROM-osassa m¨
a¨
aritellyst¨
a
relaatiosta valitaan. Jos halutaa valita kaikki rivit, voidaan WHERE-osa
j¨
att¨
a¨
a kokonaan pois.
Yhteen relaatioon kohdistuva SQL-kysely siis vastaa sarakkeita valitsevan
projektion ja rivej¨
a valitsevan valintaoperaation yhdistelm¨
a¨
a siten, ett¨
a
valittavat sarakkeet eli attribuutit luetellaan SELECT-osassa ja rivien
valintaehto kirjoitetaan WHERE-osaan. Esimerkiksi seuraava SQL-kysely, jota on
havainnollistettu kuvassa 5.1, valitsee kaikkien asiakkaiden etu- ja sukunimet:
SELECT Etunimi, Sukunimi
FROM Asiakas;
Kuten relaatioalgebrassakin, merkkijonovakiot erotetaan attribuuttien
nimist¨
a k¨
aytt¨
am¨
all¨
a heittomerkki¨
a. Seuraava kysely, jota on havainnollistettu
kuvassa 5.2, valitsee siten ne asiakkaat, joiden etunimi on ”Orimatti”:
SELECT *
FROM Asiakas
WHERE Etunimi=’Orimatti’;
Tietokannanhallintaj¨
arjestelm¨
an voi ajatella evaluoivan yhteen tauluun
kohdistuvia kyselyit¨
a seuraavasti:
38
Id
Asiakas
Etunimi Sukunimi
1
2
3
Orimatti
Laupias
Hanhikki
Teuras
Psaltari
Hillosilm¨
a
Id
Etunimi
Sukunimi
1
Orimatti
Teuras
Kuva 5.2: Esimerkki SQL-kyselyn toiminnasta.
Tuote
Id
Nimi
Kuvaus
Hinta
ToimittajaId
1
2
3
4
Hilavitkutin
Vimpain
Masiina
Jiirisaha
Laatuisa vitkutin
Vimmattu vimpaaja
Tuotantov¨
aline lis¨
aarvon tuottamiseen
Komea s¨
ahk¨
oty¨
okalu
10.99
99.99
12.50
0.99
2
1
1
2
Id
Nimi
Kuvaus
Hinta
ToimittajaId
1
2
3
Hilavitkutin
Vimpain
Masiina
Laatuisa vitkutin
Vimmattu vimpaaja
Tuotantov¨
aline lis¨
aarvon tuottamiseen
10.99
99.99
12.50
2
1
1
Nimi
Hilavitkutin
Vimpain
Masiina
Kuva 5.3: Esimerkki SQL-kyselyn toiminnasta.
1. Tietokannanhallintaj¨arjestelm¨
a hakee FROM-osassa m¨
a¨
aritellyst¨
a relaatiosta WHERE-osassa esitetyn ehdon t¨
aytt¨
avat rivit.
2. Tietokannanhallintaj¨arjestelm¨
a valitsee edellisen vaiheen tulostaulusta
SELECT-osassa luetellut attribuutit eli sarakkeet.
Seuraavassa kyselyss¨
a, joka valitsee yli kymmenen euroa maksavien
tuotteiden nimet, voidaan siten erottaa kuvassa 5.3 esitetyt vaiheet:
SELECT Nimi
FROM Tuote
WHERE Hinta>10;
Ensimm¨
aisess¨
a vaiheessa valitaan WHERE-osassa m¨
a¨
aritellyt rivit eli ne rivit,
joilla Hinta-attribuutin arvo on suurempi kuin 10. N¨
am¨
a rivit on tummennettu
kuvassa 5.3. T¨
am¨
an j¨
alkeen valitaan SELECT-osassa m¨
a¨
aritellyt sarakkeet eli
t¨
ass¨
a tapauksessa vain attribuuttia Nimi vastaava sarake; sarake on tummenettu
kuvassa 5.3. Kyselyn tuloksena saadaan siten kuvassa 5.3 alinna esitetty taulu.
Relaatioalgebrasta poiketen SQL-kyselyn tulostaulu saattaa sis¨
alt¨
a¨
a rivej¨
a,
joiden sis¨
alt¨
o on t¨
asm¨
alleen sama. Esimerkiksi seuraava kysely tuottaa
tuloksenaan joukon {<2>, <1>, <1>, <2>}:
SELECT ToimittajaId
FROM Tuote;
39
Suku
Etu
Hillosilm¨
a
Psaltari
Teuras
Hanhikki
Laupias
Orimatti
Kuva 5.4: Yksinkertaisen SQL-kyselyn tulos.
Duplikaatit voidaan poistaa lis¨
a¨
am¨
all¨
a SELECT-osaan m¨
a¨
arittely DISTINCT.
Siten seuraava kysely tuottaa joukon {<2>, <1>}:
SELECT DISTINCT ToimittajaId
FROM Tuote;
Tulossarakkeita voidaan j¨
arjest¨
a¨
a lis¨
a¨
am¨
all¨
a kyselyn loppuun ORDER
BY -osa, jossa voidaan luetella attribuutit, joiden perusteella tulostaulun
rivit j¨
arjestet¨
aa
¨n joko nousevaan (ASCENDING) tai laskevaan (DESCENDING)
j¨
arjestykseen. Jotta t¨
am¨
a voitaisiin tehd¨
a, on j¨
arjest¨
amiseen k¨
aytett¨
avien
attribuuttien tietenkin sis¨
allytt¨
av¨
a SELECT-osaan. Seuraava kysely valitsee yli
kymmenen euroa maksavien tuotteiden nimet ja j¨
arjest¨
a¨
a tulostaulun rivit
nousevaan aakkosj¨
arjestykseen attribuutin Nimi perusteella:
SELECT Nimi
FROM Tuote
WHERE Hinta>10
ORDER BY Nimi ASCENDING;
Ellei ORDER BY-osassa m¨
aa
a nousevaa tai laskevaa j¨
arjestyst¨
a, k¨
aytet¨
aa
¨ritell¨
¨n
nousevaa j¨
arjestyst¨
a. Tulostaulun sarakkeita taas voidaan nimet¨
a uudelleen
k¨
aytt¨
aen AS-operaatiota. Niinp¨
a voisimme kirjoittaa esimerkiksi seuraavan
kyselyn, joka tuottaa tuloksenaan kuvassa 5.4 esitetyn relaation:
SELECT Sukunimi AS Suku, Etunimi AS Etu
FROM Asiakas
ORDER BY Suku, Etu;
5.3.2
Useaan tauluun kohdistuvat kyselyt
Relaatioalgebraa k¨
asitelt¨
aess¨
a mainittiin, ett¨
a jokaisen relaatioalgebran
operaation tulos on relaatio. Koska t¨
am¨
a p¨
atee my¨
os karteesiseen tuloon,
puoliliitoksiin ja liitoksiin, voimme m¨
aa
a FROM-osassa mink¨
a tahansa
¨ritell¨
relaation k¨
aytt¨
aen erilaisia liitoksia taulujen v¨
alill¨
a6 . Periaatteessa voimme
ajatella tietokannanhallintaj¨
arjestelm¨
an evaluoivan useaan tauluun kohdistuvia
kyselyit¨
a seuraavasti:
1. Tietokannanhallintaj¨arjestelm¨
a muodostaa FROM-osassa m¨
a¨
aritellyn
taulun. Jos liitosta ei eksplisiittisesti mainita, voimme ajatella, ett¨
a
FROM-osassa luetellut taulut yhdistet¨
a¨
an toisiinsa karteesisen tulon avulla.
6 Itse asiassa voimme kirjoittaa FROM-osaan vaikkapa kokonaisia SQL-kyselyit¨
a, mutta t¨
am¨
a
ei yleens¨
a ole tarpeen.
40
Tuote.Id
Tuote.Nimi
...
ToimittajaId
Toimittaja.Id
Toimittaja.Nimi
1
1
2
2
3
3
4
4
Hilavitkutin
Hilavitkutin
Vimpain
Vimpain
Masiina
Masiina
Jiirisaha
Jiirisaha
...
...
...
...
...
...
...
...
2
2
1
1
1
1
2
2
1
2
1
2
1
2
1
2
Jaskan vimpain
Orimattilan kone
Jaskan vimpain
Orimattilan kone
Jaskan vimpain
Orimattilan kone
Jaskan vimpain
Orimattilan kone
Tuote.Id
Tuote.Nimi
...
ToimittajaId
Toimittaja.Id
Toimittaja.Nimi
2
3
Vimpain
Masiina
...
...
1
1
1
1
Jaskan vimpain
Jaskan vimpain
Tuote.Nimi
Vimpain
Masiina
Kuva 5.5: Esimerkki SQL-kyselyn toiminnasta.
2. Tietokannahallintaj¨
arjestelm¨
a valitsee edellisen vaiheen tulostaulusta
WHERE-osassa m¨
a¨
aritellyt rivit.
3. T¨
am¨
an j¨
alkeen valitaan edellisen vaiheen tulostaulusta SELECT-osassa
m¨
a¨
aritellyt sarakkeet.
Edell¨
a esitetyn perusteella seuraava kysely tuottaa tuloksenaan relaatioiden
Tuote ja Toimittaja karteesisen tulon:
SELECT *
FROM Tuote, Toimittaja;
Jos FROM-osassa luetelluissa tauluissa on useita samannimisi¨
a attribuutteja,
voidaan attribuuttien nimi¨
a tarkentaa taulujen nimill¨
a. Seuraava kysely valitsee
Jaskan vimpaimen toimittamien tuotteiden nimet; kyselyn toimintaa on
havainnollistettu kuvassa 5.5:
SELECT DISTINCT Tuote.Nimi
FROM Tuote, Toimittaja
WHERE Toimittaja.Nimi=’Jaskan vimpain’
AND Toimittaja.Id=ToimittajaId;
Kuvassa 5.5 on ylinp¨
an¨a esitetty FROM-osassa m¨
a¨
aritelty relaatio eli t¨
ass¨
a
tapauksessa taulujen Tuote ja Toimittaja karteesinen tulo, josta sitten on valittu WHERE-osassa m¨
aa
¨ritellyt rivit eli ne rivit, joissa Toimittaja.Nimi=’Jaskan
vimpain’ ja Toimittaja.Id=ToimittajaId. Lopuksi on valittu tietenkin
SELECT-osassa m¨
a¨
aritellyt sarakkeet ja p¨
a¨
adytty kuvassa alinna esitetyyn tulostauluun.
Toisinaan attribuuttien nimien tarkentaminen edell¨
a esitetyll¨
a tavalla ei
kuitenkaan riit¨
a. T¨
all¨
oin FROM-osassa esitellyt taulut voidaan nimet¨
a uudelleen.
N¨
ain on tehty esimerkiksi seuraavassa kyselyss¨
a, joka valitsee kaikkien masiinaa
halvempien tuotteiden nimet ja hinnat:
41
t1.Id
t1.Nimi
...
t1.Hinta
...
t2.Id
t2.Nimi
...
t2.Hinta
...
1
1
1
1
2
2
2
2
3
3
3
3
4
4
4
4
Hilavitkutin
Hilavitkutin
Hilavitkutin
Hilavitkutin
Vimpain
Vimpain
Vimpain
Vimpain
Masiina
Masiina
Masiina
Masiina
Jiirisaha
Jiirisaha
Jiirisaha
Jiirisaha
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
10.99
10.99
10.99
10.99
99.99
99.99
99.99
99.99
12.50
12.50
12.50
12.50
0.99
0.99
0.99
0.99
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
Hilavitkutin
Vimpain
Masiina
Jiirisaha
Hilavitkutin
Vimpain
Masiina
Jiirisaha
Hilavitkutin
Vimpain
Masiina
Jiirisaha
Hilavitkutin
Vimpain
Masiina
Jiirisaha
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
10.99
99.99
12.50
0.99
10.99
99.99
12.50
0.99
10.99
99.99
12.50
0.99
10.99
99.99
12.50
0.99
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
t1.Id
t1.Nimi
...
t1.Hinta
...
t2.Id
t2.Nimi
...
t2.Hinta
...
3
3
Masiina
Masiina
...
...
12.50
12.50
...
...
1
4
Hilavitkutin
Jiirisaha
...
...
10.99
0.99
...
...
t2.Nimi
t2.Hinta
Hilavitkutin
Jiirisaha
10.99
0.99
Kuva 5.6: Esimerkki SQL-kyselyn toiminnasta.
SELECT DISTINCT t2.Nimi, t2.Hinta
FROM Tuote AS t1, Tuote AS t2
WHERE t1.Nimi=’Masiina’
AND t2.Hinta<t1.Hinta;
Kyselyn toimintaa on havainnollisettu kuvassa 5.6, jossa ylimp¨
an¨
a on
esitetty FROM-osassa m¨
a¨
aritelty taulu uudelleennime¨
amisineen, keskell¨
a taulu,
johon on valittu WHERE-osassa m¨
a¨
aritellyt rivit, ja alimpana kyselyn tulostaulu.
Samat vaiheet voidaan erottaa my¨
os seuraavassa kyselyss¨
a, joka valitsee jotakin
tilanneiden asiakkaiden etu- ja sukunimen sek¨
a asiakkaiden tilaamien tuotteiden
nimet:
SELECT DISTINCT Asiakas.Etunimi, Asiakas.Sukunimi,
Tuote.Nimi
FROM Asiakas, Tilaus, Tuote
WHERE Tilaus.AsiakasId=Asiakas.Id
AND Tuote.Id=Tilaus.TuoteId;
Kuten edell¨
a mainittiin, voidaan FROM-osassa k¨
aytt¨
a¨
a pelk¨
an taulujen
luettelemisen lis¨
aksi my¨
os esimerkiksi puoliliitoksia. Vasenta puoliliitosta vastaa
LEFT OUTER JOIN, oikeaa RIGHT OUTER JOIN ja t¨
aytt¨
a FULL OUTER JOIN. N¨
ain
voimme esimerkiksi seuraavan, kuvassa 5.7 havainnollistetun kyselyn avulla
valita niiden asiakkaiden nimet, jotka eiv¨
at ole tilanneet mit¨
aa
¨n:
SELECT DISTINCT Etunimi, Sukunimi
FROM Asiakas LEFT OUTER JOIN Tilaus ON Id=AsiakasId
WHERE AsiakasId IS NULL;
42
Id
Etunimi
Sukunimi
AsiakasId
TuoteId
1
2
2
3
Orimatti
Laupias
Laupias
Hanhikki
Teuras
Psaltari
Psaltari
Hillosilm¨
a
1
2
2
NULL
1
2
3
NULL
Id
Etunimi
Sukunimi
AsiakasId
TuoteId
3
Hanhikki
Hillosilm¨
a
NULL
NULL
Etunimi
Sukunimi
Hanhikki
Hillosilm¨
a
Kuva 5.7: Esimerkki SQL-kyselyn toiminnasta.
On syyt¨
a huomata, ett¨
a edellisess¨
a kyselyss¨
a AsiakasId:t¨
a vertaillaan
IS NULL -vertailulla. Jos vertailussa olisi k¨
aytetty yht¨
asuuruutta = olisi
kysely tuottanut tyhj¨
an tuloksen, mik¨
a seuraa suoraan aiemmin esitellyst¨
a
kolmiarvologiikasta.
SQL:¨
a¨
a kirjoittaessaan kannattaa aina pit¨
a¨
a mieless¨
a relaatioalgebran
operaatiot eli rivien valinta, sarakkeiden valinta, uudelleennime¨
aminen ja
erilaiset liitokset. Algebran operaatioiden ja SQL:n yhteyden ymm¨
art¨
aminen
nimitt¨
ain helpottaa kyselyiden kirjoittamista huomattavasti. Ajattelemalla
SQL-kyselyit¨
a rivien ja sarakkeiden valinnan, uudelleennime¨
amisen sek¨
a
karteesisen tulon yhdistelm¨an¨
a p¨
a¨
asee jo sangen pitk¨
alle.
5.3.3
Aggregaattifunktiot
Aggregaatti- eli koostefunktioiden avulla tietokannasta voidaan hakea
esimerkiksi keskiarvoja, maksimeita, minimej¨
a sek¨
a rivien lukum¨
a¨
ari¨
a.
Aggregaattifunktiot kirjoitetaan tuostauluun valittavien attribuuttien tapaan
kyselyn SELECT-osaan jotakin seuraavista k¨
aytt¨
aen:
1. SUM(Attribuutti), joka laskee sarakkeen Attribuutti arvojen summan.
Vaatimuksena on tietysti se, ett¨
a attribuutin arvojoukko koostuu luvuista.
2. AVERAGE(Attribuutti), joka palauttaa sarakkeen keskiarvon. Attribuuttisarakkeen on j¨
alleen sis¨
allett¨
av¨
a vain lukuja.
3. MAX(Attribuutti), joka palauttaa sarakkeen suurimman arvon. Attribuutin Attribuutti arvojen on oltava vertailtavissa.
4. MIN(Attribuutti), joka palauttaa sarakkeen pienimm¨
an arvon. Attribuutin
Attribuutti arvojen on j¨
alleen oltava vertailtavissa.
5. COUNT(Attribuutti), joka laskee attribuutin Attribuutti arvojen m¨
a¨
ar¨
an.
On syyt¨
a korostaa, ett¨
a arvojen ei tarvitse olla erilaisia. Muista
aggregaattifunktioista poiketen attribuutin ei tarvitse saada arvojaan
lukujoukosta.
Yksinkertaisimmassa muodossaan aggregaattifunktiot kohdistuvat kokonaiseen relaatioon; t¨
all¨
oin tulosrelaatiossa on vain yksi rivi. Koska funktio COUNT
43
k¨
ayt¨
ann¨
oss¨
a laskee relaation rivit, voidaan funktion sy¨
otteen¨
a muista aggregaattifunktioista poiketen k¨
aytt¨a¨
a my¨
os jokerimerkki¨
a *. Voimme esimerkiksi hakea
tilausten m¨
a¨
ar¨
an seuraavasti:
SELECT COUNT(*)
FROM Tilaus;
Kannattaa huomata, ett¨
a edellisen kyselyn tulos on yhden rivin ja
yhden sarakkeen relaatio eik¨
a yksitt¨
ainen luku. Jos haluamme laskea niiden
asiakkaiden lukum¨
a¨
ar¨
an, jotka ovat tilanneet jotakin, meid¨
an on muistettava,
ett¨
a COUNT ei laske erilaisten arvojen m¨
aa
aa
a voimme k¨
aytt¨
aa
¨r¨
¨. Niinp¨
¨ seuraavaa
kysely¨
a selvitt¨
a¨
aksemme moniko asiakas on tilannut jotakin:
SELECT COUNT(DISTINCT AsiakasId)
FROM Tilaus;
Seuraava kysely taas valitsee tuotteiden minimi- ja maksimihinnat:
SELECT MIN(Hinta), MAX(Hinta)
FROM Tuote;
Toisinaan on kuitenkin tarpeen kohdistaa aggregaattifunktio kokonaista
taulua pienempiin kokonaisuuksiin. T¨
all¨
oin k¨
aytet¨
a¨
an ns. ryhmittelyattribuutteja, jotka luetellaan kyselyn loppuun kirjoitettavassa GROUP BY osassa; ryhmittelyattibuuttien on esiinnytt¨
av¨
a my¨
os SELECT-osassa. Tietokannanhallintaj¨
arjestelm¨
an voi ajatella evaluoivan aggregaattifunktioita sis¨
alt¨
avi¨
a
kyselyit¨
a seuraavasti:
1. Tietokannanhallintaj¨
arjestelm¨
a muodostaa FROM-osassa m¨
a¨
aritellyn relaation.
2. Edellisen vaiheen tulostaulusta valitaan WHERE-osassa m¨
a¨
aritellyt rivit.
3. T¨
am¨
an j¨
alkeen tietokannanhallintaj¨
arjestelm¨
a etsii edellisen vaiheen
tulostaulusta ryhmittelyattribuuttien erilaiset arvoyhdelm¨
at ja laskee
jokaiselle yhdelm¨
alle aggregaattifunktioiden arvot.
4. Lopuksi valitaan SELECT-osassa m¨
a¨
aritellyt sarakkeet. SELECT-osassa
voidaan valita ainoastaan aggregaattifunktioita vastaavia sarakkeita tai
ryhmittelyattribuutteja.
Esimerkkin¨
a t¨
ast¨
a voimme tarkastella seuraavaa kysely¨
a, joka valitsee
kaikkien toimittajan nimen sek¨
a toimittajan toimittamien tuotteiden minimija maksimihinnat:
SELECT DISTINCT Toimittaja.Nimi, MIN(Hinta), MAX(Hinta)
FROM Tuote, Toimittaja
WHERE Tuote.ToimittajaId=Toimittaja.Id
GROUP BY Toimittaja.Nimi;
Edellisen kyselyn toimintaa on havainnollistettu kuvassa 5.8. Ensimm¨
aisess¨
a
vaiheessa siis valitaan ylinn¨
a esitetyst¨
a taulujen Tuote ja Toimittaja
karteesisesta tulosta WHERE-osassa m¨
a¨
aritellyt rivit, eli ne rivit, joissa
44
Tuote.Id
Tuote.Nimi
...
Hinta
ToimittajaId
Toimittaja.Id
Toimittaja.Nimi
1
1
2
2
3
3
4
4
Hilavitkutin
Hilavitkutin
Vimpain
Vimpain
Masiina
Masiina
Jiirisaha
Jiirisaha
...
...
...
...
...
...
...
...
10.99
10.99
99.99
99.99
12.50
12.50
9.99
9.99
2
2
1
1
1
1
2
2
1
2
1
2
1
2
1
2
Jaskan vimpain
Orimattilan kone
Jaskan vimpain
Orimattilan kone
Jaskan vimpain
Orimattilan kone
Jaskan vimpain
Orimattilan kone
Tuote.Id
Tuote.Nimi
...
Hinta
ToimittajaId
Toimittaja.Id
Toimittaja.Nimi
1
2
3
4
Hilavitkutin
Vimpain
Masiina
Jiirisaha
...
...
...
...
10.99
99.99
12.50
9.99
2
1
1
2
2
1
1
2
Orimattilan kone
Jaskan vimpain
Jaskan vimpain
Orimattilan kone
Tuote.Nimi
MIN(Hinta)
MAX(Hinta)
Orimattilan kone
Jaskan vimpain
0.99
12.50
10.99
99.99
Kuva 5.8: Esimerkki aggregaattifunktioiden k¨
ayt¨
ost¨
a.
attribuuttien ToimittajaId ja Toimittaja.Id arvot ovat samat. T¨
am¨
an
vaiheen tuloksena on keskell¨
a esitetty taulu, josta haetaan ryhmittelyattribuutin
Toimittaja.Nimi erilaiset arvot eli ”Orimattilan kone” ja ”Jaskan vimpain”.
Koska ryhmittelyattribuutilla on kaksi erilaista arvoa, tied¨
amme nyt,
ett¨
a tulostaulussa on kaksi rivi¨
a, yksi Orimattilan koneelle ja toinen
Jaskan vimpaimelle. T¨
am¨an j¨
alkeen lasketaan aggregaattifunktioiden arvot
kummallekin ryhmittelyattribuutin arvolle. Esimerkiksi Orimattilan konetta
vastaavalle tulostaulun riville attribuutin MIN(Hinta) valitaan tietysti
attribuutin Hinta pienin arvo sellaiselta keskell¨
a esitetyn taulun rivilt¨
a, jolla
Toimittaja.Nimi=’Orimattilan kone’. N¨
ain p¨
a¨
adyt¨
a¨
an alimpana esitettyyn
tulostauluun. Yht¨
a hyvin olisimme voineet vaikkapa maksimihinnan sijasta etsi¨
a
vaikkapa keskihintaa. T¨
all¨oin Orimattilan koneelle olisi laskettu toimitettujen
tuotteiden keskihinta laskemalla attribuutin Hinta keskiarvo sellaisila riveilt¨
a,
joilla Toimittaja.Nimi=’Orimattilan kone’.
On syyt¨
a viel¨
a uudelleen korostaa, ett¨
a aggregaattifunktioita sis¨
alt¨
av¨
an
kyselyn SELECT-osan attribuuttilista voi sis¨
alt¨
a¨
a vain attribuutteja, joihin
kohdistetaan jokin aggregaattifunktio tai ryhmittelyattribuutteja. Tulostaulussa
ei standardin mukaan voi esiinty¨
a muita sarakkeita. Kannattaa my¨
os huomata,
ett¨
a aggregaattifunktioita ei voi k¨
aytt¨
a¨
a kyselyn WHERE-osassa, joten jos
aggregaattifunktioiden tuloksille halutaan asettaa ehtoja, on kyselyyn viel¨
a
kirjoitettava HAVING-osa, johon ehdot sitten lis¨
at¨
a¨
an. Seuraava kysely valitsee
toimittajien nimet sek¨
a toimittajien toimittamien tuotteiden maksimihinnan
edellytt¨
aen, ett¨
a kallein toimittajan toimittama tuote maksaa v¨
ahemm¨
an
kuin 50 euroa, ja j¨
arjest¨
a¨
a tuloksen toimittajan nimen mukaiseen k¨
a¨
anteiseen
aakkosj¨
arjestykseen:
SELECT DISTINCT Toimittaja.Nimi, MAX(Hinta)
FROM Tuote, Toimittaja
WHERE ToimittajaId=Toimittaja.Id
GROUP BY Toimittaja.Nimi
45
HAVING MAX(Hinta)<50
ORDER BY Toimittaja.Nimi DESCENDING;
Aggregaattifunktioita k¨
aytt¨
avien kyselyiden tulostaulujen rivej¨
a voi siis
j¨
arjest¨
a¨
a kuten miss¨
a tahansa SQL-kyselyn tuloksen rivej¨
a. Lis¨
aksi attribuutteja
ja tauluja voidaan nimet¨
a uudelleen.
5.3.4
Alikyselyt
SQL sallii my¨
os ns. alikyselyt eli sis¨
akyselyt eli kyselyt, jotka on
kirjoitettu toisten kyselyiden sis¨
a¨
an. Kysely¨
a, joka sis¨
alt¨
a¨
a alikyselyn, kutsutaan
ulkokyselyksi. Jos sis¨
akysely palauttaa tuloksenaan yhden rivin ja yhden
sarakkeen relaation, voidaan jotakin ulkokyselyn attribuuttia arvoa verrata
sis¨
akyselyn tulokseen operaatioita =, 6=, <, ≤, > ja ≥ k¨
aytt¨
aen. N¨
ain voimme
kirjoittaa esimerkiksi seuraavan kyselyn, joka hakee kaikkien Jaskan vimpaimen
toimittamien, viitt¨
akymment¨
a euroa kalliimpien tuotteiden nimet:
SELECT DISTINCT Nimi
FROM Tuote
WHERE Hinta>50
AND ToimittajaId=(
SELECT Id
FROM Toimittaja
WHERE Nimi=’Jaskan vimpain’);
Edellisess¨
a kyselyss¨
a sis¨
akysely siis palauttaa yhden rivin relaation, jonka
ainoan attribuutin arvo on 1. Ulkokyselyn attribuutin ToimittajaId arvoa siis
verrataan t¨
ah¨
an arvoon. Alikyselyt ovat yleens¨
a pahasta, sill¨
a ne vaikeuttavat
tietokannanhallintaj¨
arjestelm¨
an kyselyjen optimoijan toimintaa7 ja saattavat
n¨
ain johtaa tehottomiin kyselyihin. Seuraava, t¨
aysin edellist¨
a kysely¨
a vastaava
kysely on lis¨
aksi huomattavasti selke¨
ampi:
SELECT DISTINCT Tuote.Nimi
FROM Tuote, Toimittaja
WHERE Hinta>50
AND ToimittajaId=Toimittaja.Id
AND Toimittaja.Nimi=’Jaskan vimpain’;
SQL sallii my¨
os sellaisten alikyselyiden k¨
ayt¨
on, jotka palauttavat useampia
rivej¨
a mutta vain yhden sarakkeen. T¨
all¨
oin ulkokyselyn jonkin attribuutin
arvoa voidaan vertailla sis¨akyselyn tulosjoukkoon. Tarkemmin sanoen kahden
attribuutin arvon tai attribuutin arvon ja vakion vertailemisen sijasta voidaan
kirjoittaa Attribuutti Operaatio (Alikysely), miss¨
a Attribuutti on attribuutti,
jonka arvoa vertaillaan, Alikysely yhden sarakkeen relaation palauttava SQLkysely ja Operaatio jokin seuraavista:
1. IN, joka vastaa normaalia joukkoon sis¨
altymist¨
a. Vastaavasti voidaan
kirjoittaa NOT IN, joka tietysti vaatii, ett¨
a attribuutin arvo ei sis¨
ally
alikyselyn tulosjoukkoon. IN-operaation sis¨
alt¨
av¨
at kyselyt voidaan yleens¨
a
kirjoittaa uudelleen yksinkertaisempia operaatioita k¨
aytt¨
aen, mutta NOT
IN on syyt¨
a painaa mieleen, sill¨
a sit¨
a saattaa joskus tarvita.
7 Kyselyjen
optimointia k¨
asitell¨
a¨
an tarkemmin Tietokantojen jatkokurssilla.
46
2. Vertailuoperaatio =, 6=, <, ≤, > ja ≥ yhdistettyn¨
a sanaan ALL. Esimerkiksi
tapauksessa < ALL valitaan vain ne ulkokyselyn rivit, joissa attribuutin
Attribuutti arvo on jokaista sis¨
akyselyn tulosjoukon arvoa pienempi.
Vertailu = ALL ei tietysti ole kovinkaan mielek¨
as, jos sis¨
akyselyn tuloksessa
on useita rivej¨
a, mutta standardi sen kuitenkin sallii.
3. Vertailuoperaatio =, 6=, <, ≤, > ja ≥ yhdistettyn¨
a sanaan ANY. Esimerkiksi
tapauksessa < ALL valitaan vain ne ulkokyselyn rivit, joissa attribuutin
Attribuutti arvo on jotakin sis¨
akyselyn tulosjoukon arvoa pienempi.
Joissakin tietokannanhallintaj¨
arjestelmiss¨
a k¨
aytet¨
a¨
an my¨
os sanaa SOME.
K¨
ayt¨
ann¨
oss¨
a t¨
at¨
ak¨
a¨
an operaatiota ei juuri tarvita.
N¨
ait¨
a operaatioita k¨
aytt¨
aen voimme esimerkiksi kirjoittaa seuraavan
kyselyn, joka hakee kaikkia Orimattilan koneen toimittamia koneita kalliimpien
tuotteiden nimet:
SELECT DISTINCT Nimi
FROM Tuote
WHERE Hinta > ALL (
SELECT Hinta
FROM Tuote, Toimittaja
WHERE Toimittaja.Nimi=’Orimattilan kone’
AND ToimittajaId=Toimittaja.Id);
Edellisten lis¨
aksi voidaan k¨
aytt¨
a¨
a olemassaolotesti¨
a EXISTS tai olemassaolemattomuustesti¨
a NOT EXISTS, joita ei edellisist¨
a operaatiosta poiketen verrata ulkokyselyn attribuutin arvoon ja joita seuraava sis¨
akysely voi sis¨
alt¨
a¨
a useita sarakkeita ja useita rivej¨
a. Voimme esimerkiksi etsi¨
a jotakin tilanneiden asiakkaiden nimet seuraavasti:
SELECT DISTINCT Etunimi, Sukunimi
FROM Asiakas
WHERE EXISTS (SELECT * FROM Tilaus WHERE Id=AsiakasId);
EXISTS-operaatio ei ole kovin keskeinen, sill¨
a se voidaan k¨
ayt¨
ann¨
oss¨
a aina
korvata yksinkertaisemmilla operaatioilla. Esimerkiksi edellinen kysely voidaan
helposti kirjoittaa uudelleen huomattavasti sievemm¨
ass¨
a muodossa:
SELECT DISTINCT Etunimi, Sukunimi
FROM Asiakas, Tilaus
WHERE Id=AsiakasId;
Kuten edell¨
a mainittiin, on alikyselyiden k¨
aytt¨
o usein pahasta. NOT IN
kannattaa kuitenkin pit¨
a¨
a mieless¨
a, koska sill¨
a on toisinaan k¨
aytt¨
o¨
a. T¨
at¨
a
havainnollistaaksemme voimme tarkastella seuraavaa kysely¨
a, jolla yritet¨
a¨
an
etsi¨
a asiakkaat, jotka eiv¨
at ole tilanneet mit¨
a¨
an:
SELECT DISTINCT Etunimi, Sukunimi
FROM Asiakas, Tilaus
WHERE NOT Id=AsiakasId;
47
Id
Etunimi
Sukunimi
AsiakasId
TuoteId
1
1
1
2
2
2
3
3
3
Orimatti
Orimatti
Orimatti
Laupias
Laupias
Laupias
Hanhikki
Hanhikki
Hanhikki
Teuras
Teuras
Teuras
Psaltari
Psaltari
Psaltari
Hillosilm¨
a
Hillosilm¨
a
Hillosilm¨
a
1
2
2
1
2
2
1
2
2
1
2
3
1
2
3
1
2
3
Id
Etunimi
Sukunimi
AsiakasId
TuoteId
1
1
2
3
3
3
Orimatti
Orimatti
Laupias
Hanhikki
Hanhikki
Hanhikki
Teuras
Teuras
Psaltari
Hillosilm¨
a
Hillosilm¨
a
Hillosilm¨
a
2
2
1
1
2
2
2
3
1
1
2
3
Etunimi
Sukunimi
Orimatti
Laupias
Hanhikki
Teuras
Psaltari
Hillosilm¨
a
Kuva 5.9: Esimerkki v¨
a¨
arin kirjoitetun SQL-kyselyn toiminnasta. Ei n¨
ain.
Kyselyn tuottama tulos on kuitenkin v¨
a¨
ar¨
a, sill¨
a tulokseen valitaan taulujen
Asiakas ja Tilaus karteesisesta tulosta ne rivit, joissa attribuuteilla Id ja
AsiakasId on eri arvot. T¨
am¨
an j¨
alkeen valitaan SELECT-osassa m¨
aa
¨ritellyt
attribuutit duplikaatit poistaen. Kyselyn toimintaa on havainnollistettu kuvassa
5.9. Ellei kysely¨
a halua kirjoittaa esimerkiksi puoliliitosta k¨
aytt¨
aen, on k¨
atev¨
a¨
a
k¨
aytt¨
a¨
a alikysely¨
a ja NOT IN -operaatiota seuraavasti:
SELECT DISTINCT Etunimi, Sukunimi
FROM Asiakas
WHERE Id NOT IN (SELECT AsiakasId FROM Tilaus);
Kannattaa kuitenkin huomata, ett¨
a NOT IN -vertailu joukkoon, joka sis¨
alt¨
a¨
a
arvon NULL, palauttaa aina tuloksenaan tyhj¨
an joukon8 . Niinp¨
a NOT IN vertailua k¨
aytett¨
aess¨
a on syyt¨
a varmistua siit¨
a, ett¨
a alikyselyn tulos ei sis¨
all¨
a
NULL-arvoa.
Voimme ajatella tietokannanhallintaj¨
arjestelm¨
an evaluoivan alikyselyit¨
a
sis¨
alt¨
av¨
at kyselyt siten, ett¨
a se tuttuun tapaan ensin muodostaa ulkokyselyn
FROM-osassa m¨
a¨
aritellyn relaation, josta sitten valitaan ne ulkokyselyn WHEREosassa m¨
a¨
aritellyt rivit, jotka valittaisiin ilman alikyselyit¨
a. T¨
am¨
an j¨
alkeen
jokaista edellisess¨
a vaiheessa valittua rivi¨
a kohden evaluoidaan alikyselyt
ja vertaillaan attribuuttien arvoja sis¨
akyselyn tuloksiin; EXISTS-tapauksessa
mukaan otetaan tietysti vain ne ulkokyselyn rivit, joille alikysely ei tuota tyhj¨
a¨
a
tulosta. Lopuksi valitaan tulokseen ulkokyselyn SELECT-osassa m¨
a¨
aritellyt
8 Lopputulos on t¨
aysin kolmiarvologiikan mukainen, vaikka se ei kovin intuitiiviselta
tuntuisikaan.
48
sarakkeet. T¨
am¨
a evaluointij¨
arjestys on voimassa riippumatta siit¨
a, k¨
aytet¨
aa
o
¨nk¨
alikyselyss¨
a ulkokyselyn FROM-osassa esiteltyjen taulujen attribuutteja vai ei.
5.3.5
Joukko-opilliset operaatiot
Edell¨
a esiteltyjen ominaisuuksien lis¨
aksi SQL sis¨
alt¨
a¨
a my¨
os joukko-opillisia
unionia, leikkausta ja erotusta vastaavat operaatiot UNION, INTERSECT
ja EXCEPT. Kyselyiden Kysely1 ja Kysely2 tulosrelaatiot voidaan n¨
aiden
operaatioiden avulla liitt¨
a¨
a toisiinsa kirjoittamalla Kysely1 Operaatio Kysely2
edellytt¨
aen, ett¨
a Kysely1 :n ja Kysely2 :n tulosrelaatiot ovat yhteensopivat, eli
molemmilla relaatioilla on sama m¨
a¨
ar¨
a attribuutteja ja attribuuttien arvojoukot
ovat samat. Edell¨
a Operaatio on tietenkin joko UNION, INTERSECT tai EXCEPT.
K¨
aytt¨
aen erotusta voimme seuraavan kyselyn avulla hakea ne asiakkaat, jotka
eiv¨
at ole tilanneet mit¨
a¨
an:
SELECT Etunimi, Sukunimi
FROM Asiakas
EXCEPT
SELECT Etunimi, Sukunimi
FROM Asiakas, Tilaus
WHERE Id=AsiakasId;
Edellisess¨
a kyselyss¨
a ensimm¨
ainen osa tuottaa joukon {<Orimatti,
Teuras>,<Laupias,Psaltari>,<Hanhikki,Hillosilm¨
a>}, josta sitten poistetaan toisen kyselyn tulosjoukko {<Orimatti,Teuras>, <Laupias,Psaltari>}
ja saadaan tulokseksi {<Hanhikki,Hillosilm¨
a>}. Operaatiot vastaavat joukkoopillisia operaatioita my¨
os siin¨
a, ett¨
a niiden tulokset eiv¨
at muista SQL:n operaatioista poiketen sis¨
all¨
a duplikaatteja. Jos duplikaatit jostain syyst¨
a halutaan
mukaan tulokseen, voidaan k¨
aytt¨
a¨
a operaatioita UNION ALL, INTERSECT ALL,
EXCEPT ALL.
Kannattaa huomata, ett¨
a operaatio INTERSECT evaluoidaan ennen
operaatioita UNION ja EXCEPT. Operaatioilla UNION ja EXCEPT ei kuitenkaan ole
keskin¨
aist¨
a j¨
arjestyst¨
a, vaan ne evaluoidaan normaalisti vasemmalta oikealle.
Mill¨
a tahansa kyselyill¨
a Kysely1 , Kysely2 ja Kysely3 lauseke Kysely1 UNION
Kysely2 EXCEPT Kysely3 siis evaluoidaan kuten (Kysely1 UNION Kysely2 )
EXCEPT Kysely3 . Kysely1 UNION Kysely2 INTERSECT Kysely3 taas evaluoidaan
kuten Kysely1 UNION (Kysely2 INTERSECT Kysely3 ).
5.4
P¨
aivitykset SQL:n avulla
5.4.1
Monikon lis¨
a¨
aminen
Tehokkaiden hakuoperaatioiden lis¨
aksi tietokannanhallintaj¨
arjestelm¨
an on
tarjottava my¨
os mahdollisuus kannassa olevan tiedon muokkaamiseen.
Yksinkertaisin p¨
aivitysoperaatio on monikon lis¨
a¨
aminen relaatioon. Voimme
esimerkiksi lis¨
at¨
a Asiakas-tauluun rivin <4,Assi,Asiakas> seuraavasti:
INSERT INTO Asiakas VALUES (4, ’Assi’, ’Asiakas’);
Kokonaisen monikon lis¨
a¨
amisen sijasta voimme lis¨
at¨
a monikon siten, ett¨
a
m¨
a¨
arittelemme vain osan monikon attribuuttien arvoista; muut sarakkeet
49
alustetaan t¨
all¨
oin oletusarvolla. Olettaen, ett¨
a Asiakas-taulua m¨
aa
aess¨
a
¨ritelt¨
Sukunimi-attribuutille ei ole m¨
a¨
aritelty NULL:ista poikkeavaa oletusarvoa,
seuraava komento lis¨
a¨
a Asiakas-tauluun rivin <5,Antti,NULL>:
INSERT INTO Asiakas (Id, Etunimi) VALUES (5, ’Antti’);
Kannattaa pit¨
a¨
a mieless¨
a, ett¨
a lis¨
aysoperaatio onnistuu vain, jos se ei riko
eheyss¨
aa
oj¨
a - siis entiteettieheytt¨
a ja viite-eheytt¨
a - tai avainrajoitetta eli sit¨
a,
¨nt¨
ett¨
a mill¨
a¨
an taulussa jo esiintyv¨
all¨
a monikolla ei ole samoja avainttribuuttien
arvoja kuin lis¨
att¨
av¨
all¨
a monikolla. Lis¨
aksi arvojen on tietenkin sovittava
attribuuttien arvojoukkoihin. Emme siis voi lis¨
at¨
a esimerkiksi monikkoa, jossa
jonkin kokonaislukuattribuutin arvo on ’xyz’.
My¨
os kyselytulosten k¨
aytt¨
aminen p¨
aivitysoperaatioissa on mahdollista.
Niinp¨
a voimme lis¨
at¨
a tauluun kyselyn tulosjoukon seuraavasti:
INSERT INTO T aulu (Attribuutti1 , ..., Attribuuttin ) Kysely;
Edellisess¨
a operaatiossa kyselyn Kysely tulosrelaation attribuuttien on tietenkin sovittava taulun T aulu attribuutteihin Attribuutti1 , ..., Attribuuttin .
T¨
allaisten INSERT-komentojen k¨
aytt¨
aminen mahdollistaa v¨
alitulosten tallentamisen. V¨
alitulosten k¨
aytt¨
aminen on suotavaa silloin, kun kysely on
kovin monimutkainen. Lopuksi kannattaa huomata, ett¨
a monissa tietokannanhallintaj¨
arjestelmiss¨
a dataa voidaan lis¨
at¨
a tauluihin suoraan tekstitiedostoista.
Esimerkiksi PostgreSQL tarjoaa t¨
ah¨
an tarkoitukseen COPY-komennon.
5.4.2
Monikon poistaminen
Monikon poistaminen on monikon lis¨
a¨
amisen tapaan hyvin yksinkertaista.
T¨
ah¨
an tarkoitukseen SQL tarjoaa DELETE-komennon. Voimme esimerkiksi
tuhota ne Asiakas-taulun rivit, joissa attribuutin Id arvo on suurempi kuin
3 seuraavasti:
DELETE FROM Asiakas WHERE Id>3;
Kuten SQL-kielen kyselyiss¨
akin, WHERE-osa m¨
a¨
arittelee ne rivit, joihin
operaatio kohdistuu. WHERE-osan ehto voi olla hyvinkin monimutkainen ja
sis¨
alt¨
aa
a rivin tuhoaminen saattaa
¨ vaikkapa alikyselyit¨a. Kannattaa muistaa, ett¨
viiteavainten m¨
a¨
arittelyist¨
a riippuen aiheuttaa muidenkin rivien tuhoamisen.
Jos kohdistamme edell¨
a esitetyn operaation kantaan, jossa olemme m¨
a¨
aritelleet
Tilaus-taulun viiteavaimen AsiakasId seuraavasti:
FOREIGN KEY AsiakasId REFERENCES Asiakas
ON UPDATE CASCADE ON DELETE CASCADE
tuhoaa tietokannanhallintaj¨
arjestelm¨
a automaattisesti my¨
os ne Tilaus-taulun
rivit, joissa AsiakasId>3. Jos taas olisimme m¨
a¨
aritelleet
FOREIGN KEY AsiakasId REFERENCES Asiakas
ON UPDATE CASCADE ON DELETE RESTRICT
ja taulussa Tilaus olisi ollut rivej¨
a, joilla AsiakasId>3, olisi tietokannanhallintaj¨
arjestelm¨
a j¨
att¨
anyt operaation suorittamatta ja palauttanut virheilmoituksen.
50
5.4.3
Monikon muuttaminen
Monikon sis¨
alt¨
o¨
a voidaan muuttaa UPDATE-komennolla. UPDATE-komennossa
voidaan DELETE-komennon tapaan k¨
aytt¨
a¨
a WHERE-osaa, joka m¨
a¨
arittelee, mihin
riveihin p¨
aivitys kohdistuu. Niinp¨
a voimme kirjoittaa esimerkiksi seuraavan
kyselyn:
UPDATE Asiakas
SET Sukunimi=’Teuras’
WHERE Sukunimi=’Hillosilm¨
a’;
Jos WHERE-osaa ei kirjoiteta, kohdistuu p¨
aivitys tietysti kaikkiin taulun
monikkoihin. Seuraava kysely korottaa kaikkien tuotteitten hintoja kymmenell¨
a
prosentilla:
UPDATE Tuote
SET Hinta=Hinta*1.1;
Toisinaan p¨
aivitysoperaatioon tarvitaan tietoja my¨
os muista kuin
p¨
aivitett¨
av¨
ast¨
a taulusta. T¨
all¨
oin muiden taulujen tietoja haetaan alikyselyiden
avulla. N¨
ain on toimittu esimerkiksi seuraavassa kyselyss¨
a:
UPDATE Tuote
SET Hinta=Hinta*1.1
WHERE ToimittajaId IN (
SELECT Id
FROM Toimittaja
WHERE Toimittaja.Nimi=’Jaskan vimpain’);
Standardi ei sit¨
a salli, mutta huomattavasti elegantimpaa olisi ilmaista sama
asia kirjoittamalla
UPDATE Tuote, Toimittaja
SET Hinta=Hinta*1.1
WHERE ToimittajaId=Toimittaja.Id
AND Toimittaja.Nimi=’Jaskan vimpain’;
Lopuksi kannattaa huomata, ett¨
a my¨
os monikon sis¨
all¨
on muuttaminen
saattaa aiheuttaa vy¨
orytyksen. Jos p¨
aivit¨
amme jonkin viittauksen kohteena
olevan p¨
a¨
aavainattribuutin arvoa, p¨
aivitet¨
a¨
an my¨
os viittaavien rivien
viiteavainattribuuttien arvoja. T¨
am¨
a tietenkin edellytt¨
a¨
a, ett¨
a viiteavaimet on
varustettu ON UPDATE CASCADE -m¨
a¨
areell¨
a.
5.5
5.5.1
Transaktiot
ACID
Tietokantaoperaatioiden
yhteydess¨
a
puhutaan
usein
ns.
ACIDominaisuuksista eli siit¨
a, ett¨
a jokainen operaatio on atomaarinen (Atomic),
s¨
ailytt¨
a¨
a tietokannan oikeellisuuden ja eheyden (Consistent), toimii itsen¨
aisesti (Isolated) ja muuttaa tietokannan tilaa pysyv¨
asti (Durable).
51
Tarkemmin sanottuna atomaarisuudella tarkoitetaan sit¨
a, ett¨
a jokainen operaatio suoritetaan joko kokonaan tai j¨
atet¨
a¨
an kokonaan suorittamatta. Jos
operaatio jostain syyst¨
a estyy tai keskeytyy, on tietokannanhallintaj¨
arjestelm¨
an
peruutettava tietokantaan tekem¨
ans¨
a muutokset. Oikeellisuuden ja eheyden
s¨
ailymisell¨
a tarkoitetaan sit¨
a, ett¨
a oikeelliseen ja ehe¨
a¨
an tietokantaan kohdistetun operaation j¨
alkeen tietokannan on edelleen oltava oikeellinen ja ehe¨
a.
Tietokannanhallintaj¨
arjestelm¨
an on huolehdittava t¨
ast¨
a joko est¨
am¨
all¨
a eheyden
rikkovat operaatiot tai eheytt¨
am¨
all¨
a tietokanta p¨
aivitysoperaation j¨
alkeen.
Tietokantaoperaatioiden itsen¨
ainen toiminta taas tarkoittaa sit¨
a, ett¨
a
mik¨
a¨
an operaatio ei saa vaikuttaa toisen, keskener¨
aisen operaation toimintaan.
Tietokantaoperaatio saattaisi esimerkiksi siirt¨
a¨
a 100 euroa tililt¨
a A, jonka
alkusaldo on 500 euroa, tilille B, jonka alkusaldo on 500 euroa. Operaatio
v¨
ahent¨
a¨
a tililt¨
a A ensin 100 euroa ja lis¨
a¨
a sitten tilille B 100 euroa, mink¨
a j¨
alkeen
tilien saldot ovat tietysti 400 ja 600 euroa. Toinen tietokantaoperaatio saattaisi
samaan aikaan tutkia tilien A ja B saldoa. Koska operaatioiden on toimittava
itsen¨
aisesti, j¨
alkimm¨
ainen operaatio havaitsisi tilien A ja B saldoiksi joko 500
ja 500 euroa tai 400 ja 600 euroa. Operaatio ei kuitenkaan voi tutkia tilien
saldoja, kun rahoja siirt¨
av¨
a operaatio on kesken eli tilien saldot ovat 400 ja 500
euroa. Tietokannanhallintaj¨
arjestelm¨
at tarjoavat tavallisesti mahdollisuuden
yhdist¨
a¨
a useita erillisi¨
a p¨
aivityksi¨
a ja kyselyit¨
a ns. transaktioksi, eli yhdeksi
tietokantaoperaatioksi, jolle taataan edell¨
a mainitut ACID-ominaisuudet.
Muutosten pysyvyys taas on varsin ilmeinen ehto. Jos tietokantapalvelimemme esimerkiksi kaatuu p¨
aivitysoperaation j¨
alkeen, on operaation tekemien
muutosten s¨
ailytt¨
av¨
a. Jos palvelin taas kaatuu kesken operaation, on tietokannanhallintaj¨
arjestelm¨
an uudelleen k¨
aynnistett¨
aess¨
a kyett¨
av¨
a peruuttamaan jo
mahdollisesti tekem¨
ans¨
a muutokset. T¨
am¨
a seuraa operaatioiden atomaarisuuden ehdosta.
5.5.2
Transaktioiden tarpeesta
Jokainen yksitt¨
ainen SQL-kysely tai -p¨
aivitys t¨
aytt¨
a¨
a edell¨
a esitetyt ACIDominaisuudet. Toisinaan tietyn p¨
aivityksen tekemiseen tarvitaan kuitenkin
useampia yksitt¨
aisi¨
a kyselyit¨
a. T¨
am¨
a on helppoa ymm¨
art¨
a¨
a tarkastelemalla
vaikkapa seuraavia kyselyit¨a, jotka siirt¨
av¨
at 100 euroa tililt¨
a 123 tilille 321:
UPDATE Tili SET Saldo=Saldo-100 WHERE Id=123;
UPDATE Tili SET Saldo=Saldo+100 WHERE Id=321;
Vaikka kyseess¨
a onkin kaksi erillist¨
a kysely¨
a, halutaan n¨
ait¨
a kyselyit¨
a
kohdella yhten¨
a transaktiona, joka joko suoritetaan kokonaisuudessaan
tai j¨
atet¨
a¨
an kokonaisuudessaan suorittamatta. Transaktioiden yhteydess¨
a
puhutaan t¨
all¨
oin transaktion vahvistamisesta eli transaktion aiheuttamien
muutosten tekemisest¨
a pysyviksi tai peruuttamisesta eli tietokannan
palauttamisesta transaktiota edelt¨
aneeseen tilaan. Esimerkiksi edellisess¨
a
tapauksessa emme siis voi sallia tilannetta, jossa tililt¨
a 123 v¨
ahennet¨
a¨
an 100
euroa, mutta tilille 321 ei lis¨
at¨
ak¨
a¨
an 100 euroa.
5.5.3
Transaktiotasot
Transaktioiden k¨
ayt¨
oll¨
a pyrit¨
a¨
an est¨
am¨
a¨
an jollain tavalla virheellisen datan
k¨
aytt¨
o tietokantaa k¨
aytt¨
aviss¨
a sovelluksissa. K¨
ayt¨
ann¨
oss¨
a siis halutaan est¨
a¨
a
52
kyselyit¨
a palauttamasta virheellist¨
a dataa, mik¨
a voi tapahtua seuraavilla
tavoilla:
1. Luetaan vahvistamatonta dataa eli sellaista dataa, jonka on
kirjoittanut vahvistamaton transaktio.
2. Vahvistamattoman datan lukeminen on toistamattomissa olevan
lukuoperaation erikoistapaus. Kohta n¨
ahd¨
a¨
an, ett¨
a kyselyoperaatiota
ei v¨
altt¨
am¨
att¨
a voida toistaa, vaikka sallittaisiinkin vain vahvistetun datan
lukeminen.
3. Toistettu lukuoperaatio palauttaa enemm¨
an rivej¨
a; ylim¨
a¨
ar¨
aisi¨
a
rivej¨
a kutsutaan haamuriveiksi. T¨
am¨
an virheen ja toistamattomien
lukuoperaatioiden eroa valotetaan pian.
Vahvistamattoman datan lukemisen ja toistamattomissa olevan lukuoperaation eroa voidaan havainnollistaa seuraavien vaiheiden avulla:
1. Transaktio 1 alkaa.
2. Transaktio 1 lukee rivin.
3. Transaktio 2 alkaa.
4. Transaktio 2 muuttaa transaktion 1 lukeman rivin sis¨
alt¨
o¨
a.
5. Transaktio 2 vahvistetaan.
6. Transaktio 1 lukee aiemmin lukemansa rivin uudelleen.
N¨
aiden vaiheiden j¨
alkeen transaktio 1 on lukenut virheellist¨
a dataa. Koska
transaktio 2 vahvistettiin ennen kuin transaktio 1 luki rivin uudelleen, ei
kyseess¨
a kuitenkaan ollut vahvistamattoman datan luku. Samalla tavoin
transaktio 2 olisi saattanut tuhota transaktion 1 lukeman rivin, mink¨
a j¨
alkeen
transaktio 1 ei tietenk¨
a¨
an olisi voinut lukea rivi¨
a. Molemmille tapauksille on
kuitenkin yhteist¨
a se, ett¨a transaktion 1 ensimm¨
ainen lukuoperaatio ei ole
toistettavissa.
Seuraava esimerkki selvitt¨
a¨
a haamurivej¨
a aiheuttavien transaktioiden
toimintaa:
1. Transaktio 1 alkaa.
2. Transaktio 1 lukee rivit, joissa vaikkapa attribuutin X arvo on 3.
3. Transaktio 2 alkaa.
4. Transaktio 2 lis¨
a¨
a rivin, jossa attribuutin X arvo on 3.
5. Transaktio 2 vahvistetaan.
6. Transaktio 1 lukee rivit, joissa attribuutin X arvo on 3.
53
Nyt transaktion 1 toisen lukuoperaation tulokseen sis¨
altyv¨
at sek¨
a
ensimm¨
aisess¨
a lukuoperaatiossa haetut rivit ett¨
a transaktion 2 lis¨
a¨
am¨
a rivi.
Periaatteessa transaktion 1 lukuoperaatio siis on toistettavissa, kunhan
tuloksesta jotenkin poistetaan transaktion 2 lis¨
a¨
am¨
a rivi. T¨
am¨
a erottaa
toitamattomissa olevan lukuoperaation ja haamurivej¨
a lukevan lukuoperaation
toisistaan.
N¨
aiden virhetilanteiden ymm¨
art¨
aminen on tarpeen, kun m¨
a¨
aritell¨
a¨
an
transaktiolle ns. transaktiotasoa. Yleisess¨
a tapauksessa operaatioista
Operaatio1 , ..., Operaation koostuva transaktio kirjoitetaan muodossa:
BEGIN;
SET TRANSACTION ISOLATION LEVEL T aso;
Operaatio1 ;
...
Operaatio1 ;
COMMIT;
Edellisess¨
a transaktiossa T aso on jokin seuraavista:
1. READ UNCOMMITTED: vahvistamattoman datan lukeminen, toistamattomat
lukuoperaatiot sek¨
a haamurivien lukeminen ovat mahdollisia.
2. READ COMMITTED: est¨a¨
a vahvistamattoman datan lukemisen, mutta ei
muita virheit¨
a. T¨
am¨
a on useimmissa tietokannanhallintaj¨
arjestelmiss¨
a
transaktioiden oletustaso.
3. READ REPEATABLE: est¨
a¨
a vahvistamattoman datan lukemisen ja toistamattomat lukuoperaatiot, mutta ei haamurivien lukemista.
4. SERIALIZABLE: est¨
a¨
a kaikki edell¨
a mainitut virheet eli vahvistamattoman
datan lukemisen, toistamattomat lukuoperaatiot sek¨
a haamurivien
lukemisen.
Jotkin tietokannahallintaj¨
arjestelm¨
at tarjoavat muitakin transaktiotasoja; n¨
aihin kannattaa tutustua lukemalla k¨
aytt¨
am¨
ans¨
a tietokannanhallintaj¨
arjestelm¨
an manuaaleja. Edell¨
a esitettyj¨
a transaktiotasoja k¨
aytt¨
aen
voimme kirjoittaa esimerkiksi seuraavan transaktion:
BEGIN;
SET TRANSACTION LEVEL SERIALIZABLE;
UPDATE Tili SET Saldo=Saldo-100 WHERE Id=123;
UPDATE Tili SET Saldo=Saldo+100 WHERE Id=321;
COMMIT;
Periaatteessa vain SERIALIZABLE takaa transaktiolle t¨
aydet ACIDominaisuudet. Korkeimman transaktiotason k¨
aytt¨
aminen kuitenkin hidastaa
tietokantaoperaatioita, joten kaikille transaktioille ei v¨
altt¨
am¨
att¨
a kannata
m¨
a¨
aritell¨
a tasoa SERIALIZABLE. Transaktioiden toteuttamiseksi tietokannanhallintaj¨
arjestelm¨
at k¨
aytt¨
av¨
at erilaisia lukituksia, jotka est¨
av¨
at muita transaktioita k¨
asittelem¨
ast¨
a sellaista dataa, jota jokin muu transaktio parhaillaan
k¨
asittelee.
54
Lis¨
aksi transaktioiden peruuttaminen vaatii sit¨
a, ett¨
a tietokannanhallintaj¨
arjestelm¨
a pit¨
a¨
a yll¨
a lokia tietokantaan tehdyist¨
a muutoksista.
K¨
ayt¨
ann¨
oss¨
a t¨
am¨
a toteutetaan siten, ett¨
a jokaiseen tietokannan riviin on liitetty
mm. tieto siit¨
a, mik¨
a transaktio sit¨
a parhaillaan k¨
asittelee ja mik¨
a on transaktion tila. Lukituksiin ja transaktioiden peruuttamiseen tutustutaan tarkemmin
Tietokantojen jatkokurssilla.
55
Kirjallisuutta
[1] E. F. Codd (1970). A Relational Model for Large Shared Data Banks.
Communications of the ACM 13(6): 377-387.
[2] Aku Ankan vuosikerrat 1951-2006.
[3] R. Elmasri & S. B. Navathe (2000). Fundamentals of Database Systems, 3rd
Edition. Addison-Wesley.
[4] P. P. Chen (1976). The Entity-Relationship Model - Toward a Unified View
of Data. ACM Transactions on Database Systems 1(1): 9-36.
[5] PostgreSQL:n dokumentaatio. http://www.postgresql.org/docs/.
56