1. Johdanto

Mikä on tietokanta?

Tietokanta (database) on tietokoneella oleva kokoelma tietoa, johon voidaan suorittaa hakuja ja jonka sisältöä voidaan muuttaa. Tietokantoja ovat esimerkiksi:

yliopiston opiskelijarekisteri
verkkokaupan tuotteet ja varastotilanne
pankin tiedot asiakkaista ja tilitapahtumista
päivittäin mitatut säätiedot eri paikoissa
lentoyhtiön lentoaikataulut ja varaustilanne

Tietokantoja on nykyään valtavasti, ja useimmat ihmiset käyttävät tavallisen päivän aikana useita palveluja, jotka ovat yhteydessä tietokantoihin.

Tietokantojen haasteet

Tietokantojen tekniseen toteutukseen liittyy haasteita, kuten:

Tehokkuus: Tietokannassa voi olla suuri määrä tietoa, johon kohdistuu jatkuvasti hakuja ja muutoksia. Miten toteuttaa tietokanta niin, että tietoon pääsee käsiksi tehokkaasti?
Samanaikaisuus: Tietokannalla voi olla useita käyttäjiä, jotka voivat hakea ja muuttaa tietoa samaan aikaan. Mitä tietokannan toteutuksessa tulee ottaa huomioon tähän liittyen?
Yllätykset: Tietokannan sisällön tulisi säilyä kunnossa myös yllättävissä tilanteissa. Esimerkiksi mitä tapahtuu, jos sähköt katkeavat juuri silloin, kun tietoa ollaan muuttamassa?

Tietokantajärjestelmät

Tietokantajärjestelmä pitää huolta tietokannan sisällöstä ja tarjoaa tietokannan käyttäjälle toiminnot, joiden avulla tietoa pystyy hakemaan ja muuttamaan. Huomaa, että sana tietokanta viittaa usein myös tietokantajärjestelmään.

Useimmat käytössä olevat tietokannat perustuvat relaatiomalliin ja SQL-kieleen, joiden teoreettinen perusta syntyi 1970-luvulla. Tutustumme tällä kurssilla SQLite-tietokantaan, joka soveltuu hyvin SQL:n opettelemiseen. Muita SQL-tietokantoja ovat esimerkiksi MySQL ja PostgreSQL.

Termi NoSQL viittaa tietokantaan, joka perustuu muuhun kuin relaatiomalliin ja SQL-kieleen. Esimerkiksi MongoDB ja Redis ovat tällaisia tietokantoja. Tällä kurssilla emme kuitenkaan juurikaan käsittele NoSQL-tietokantoja.

Tee-se-itse-tietokanta

Ennen tutustumista olemassa oleviin tietokantajärjestelmiin on hyvä miettiä, mitä tarvetta tällaisille järjestelmille on. Miksi emme voisi vain toteuttaa tietokantaa itse vaikkapa tallentamalla tietokannan sisällön tekstitiedostoon sopivassa muodossa?

Esimerkki

Haluamme tallentaa tietokantaan tietoa kurssin opiskelijoiden ratkaisuista kurssin tehtäviin. Kun opiskelija lähettää ratkaisun, tietokantaan tallennetaan opiskelijanumero, tehtävän numero, ratkaisun lähetysaika sekä ratkaisun tuottama pistemäärä.

Yksinkertainen tapa toteuttaa tietokanta on luoda yksi tekstitiedosto, jonka jokaisella rivillä on yksi lähetys. Aina kun joku opiskelija lähettää ratkaisun, tiedostoon lisätään yksi rivi. Voisimme käytännössä tallentaa tietokannan CSV-formaatissa tiedostoon database.csv seuraavaan tapaan:

012121212;1;2020-05-03 12:50:32;100
012341234;1;2020-05-03 14:02:12;20
012121212;2;2020-05-04 14:05:50;70
012121212;3;2020-05-04 14:43:12;0
012341234;2;2020-05-04 10:15:23;0
012341234;2;2020-05-04 16:40:39;0
013371337;1;2020-05-06 18:11:13;0
012341234;2;2020-05-07 10:02:15;100

CSV-tiedostossa tietty erotinmerkki erottaa riveillä olevat kentät. Tässä tapauksessa erotinmerkkinä on puolipiste ;. Esimerkiksi tiedoston ensimmäinen rivi kertoo, että opiskelija 012121212 on lähettänyt ratkaisun tehtävään 1 ja saanut siitä 100 pistettä.

Nyt jos haluamme vaikkapa luoda tilaston, jossa on jokaisen opiskelijan lähetysten määrä, voimme hoitaa asian näin Python-kielellä:

stats = {}

for line in open("database.csv"):
    student_id = line.split(";")[0]

    if student_id not in stats:
        stats[student_id] = 0

    stats[student_id] += 1

print(stats)

Koodin tulostus on seuraava:

{'012121212': 3, '012341234': 4, '013371337': 1}

Tämä tarkoittaa, että opiskelija 012121212 on lähettänyt kolme ratkaisua, opiskelija 012341234 on lähettänyt neljä ratkaisua ja opiskelija 013371337 on lähettänyt yhden ratkaisun.

Mahdolliset ongelmat

Tällainen CSV-tiedostoa käyttävä tietokanta on periaatteessa toimiva, mutta sen käyttäminen voi johtaa ongelmiin:

Tehokkuus

Kun tiedon määrä kasvaa, tiedon hakeminen CSV-tiedostosta voi alkaa viedä aikaa. Tämä johtuu siitä, että joudumme useimmissa tilanteissa käymään läpi koko tiedoston sisällön alusta loppuun, kun haluamme saada haettua tietyn asian.

Esimerkiksi jos haluamme selvittää, minkä pistemäärän opiskelija 012341234 on saanut tehtävästä 2, joudumme käymään läpi tiedoston kaikki rivit, jotta löydämme oikeat rivit. Joudumme tekemään näin, koska tiettyyn opiskelijaan liittyvät rivit voivat olla eri puolilla tiedostoa eikä meillä ole etukäteen tietoa niiden sijainnista.

Tiedoston läpikäynti ei ole ongelma, jos tiedosto on pieni. Esimerkiksi jos tiedostossa on sata riviä, läpikäynti sujuu hyvin nopeasti. Mutta tiedoston ollessa suuri on hidasta käydä kaikki rivit läpi aina, kun haluamme saada selville jonkin asian tietokannasta.

Samanaikaisuus

Mitä tapahtuu, jos kaksi opiskelijaa lähettävät ratkaisun samaan aikaan? Tällöin tiedoston loppuun pitäisi tulla kaksi riviä tietoa seuraavaan tapaan:

012341234;3;2020-05-07 15:42:02;0
013371337;7;2020-05-07 15:42:02;0

Jos käy huonosti, voi kuitenkin käydä näin:

012341234;3;2020013371337;7;2020-05-07 15:42:02;0
-05-07 15:42:02;0

Tällainen tilanne voi syntyä, kun kaksi samaan aikaan käynnissä olevaa prosessia muuttaa tiedoston sisältöä. Ensimmäinen prosessi kirjoittaa ensin tiedoston loppuun 012341234;3;2020, sitten toinen prosessi kirjoittaa väliin 013371337;7;2020-05-07 15:42:02;0 ja lopuksi ensimmäinen prosessi kirjoittaa -05-07 15:42:02;0. Tämän seurauksena tiedoston rakenne menee sekaisin.

Kun tiedostoon kirjoitetaan tietoa, ei ole itsestään selvää, että tieto menee perille yhtenäisenä, jos joku muu koettaa kirjoittaa samaan aikaan. Tämä riippuu tiedostojärjestelmästä, tiedon määrästä ja tiedoston käsittelytavasta.

Yllätykset

Tarkastellaan tilannetta, jossa haluamme poistaa tietokannasta opiskelijan 012341234 lähetykset. Voimme tehdä tämän lukemalla ensin kaikki rivit muistiin ja kirjoittamalla sitten takaisin tiedostoon kaikki rivit, joissa opiskelija ei ole 012341234.

Mitä jos sähköt katkeavat juuri, kun olemme kirjoittaneet puolet riveistä takaisin? Kun käynnistämme tietokoneen uudestaan, huomaamme, että tiedostossa on vain puolet riveistä ja loput ovat kadonneet eikä meillä ole keinoa saada niitä takaisin.

Mitä opimme tästä?

Yksinkertainen tekstitiedosto ei ole sinänsä huono tapa tallentaa tietoa, mutta se ei sovellu kaikkiin käyttötarkoituksiin. Tämän vuoksi tarvitsemme erillisiä tietokantajärjestelmiä, joihin tutustumme tällä kurssilla.

Tietokantajärjestelmien kehittäjät ovat miettineet tarkasti, miten järjestelmä kannattaa toteuttaa, jotta tietoon pääsee käsiksi tehokkaasti, samanaikaiset käyttäjät eivät aiheuta ongelmia eikä tietoa katoa yllättävissä tilanteissa. Kun käytämme tietokantajärjestelmää, meidän ei tarvitse huolehtia tästä kaikesta itse.

Relaatiomalli ja SQL-kieli

Tällä kurssilla tutustumme tietokantoihin relaatiomallin ja SQL-kielen kautta. Tähän liittyvät perusideat ovat:

Kaikki tieto tallennetaan tauluihin riveinä, jotka voivat viitata toisiinsa.
Tietokannan käyttäjä käsittelee tietoa SQL-kielellä, joka kätkee käyttäjältä tietokannan sisäisen toiminnan yksityiskohdat.

Tietokannan rakenne

Tietokanta muodostuu tauluista (table), joissa on kiinteät sarakkeet (column). Tauluihin tallennetaan tietoa riveinä (row), joilla on tietyt arvot sarakkeissa. Jokaisessa taulussa on kokoelma tiettyyn asiaan liittyvää tietoa.

Seuraavassa kuvassa on esimerkki tietokannasta, jota voisi käyttää osana verkkokaupan toteutusta. Tauluissa Products, Customers ja Purchases on tietoa tuotteista, asiakkaista ja heidän ostoskoriensa sisällöstä.

id	name	price
1	retiisi	7
2	porkkana	5
3	nauris	4
4	lanttu	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Esimerkiksi taulun Products sarakkeet ovat id, name ja price. Taulussa on tällä hetkellä viisi riviä tietoa, jotka kuvaavat tietokannassa olevat tuotteet.

Tauluissa Products ja Customers jokaisella rivillä on yksilöllinen id-numero, jonka avulla niihin voi viitata. Tämä on yleinen tapa tietokantojen suunnittelussa. Tämän ansiosta taulussa Purchases voidaan esittää id-numeroiden avulla, mitä tuotteita kukin asiakas on valinnut. Tässä esimerkissä Uolevin korissa on porkkana ja selleri ja Maijan korissa on retiisi, lanttu ja selleri. Aapelin kori on puolestaan tyhjä.

SQL-kieli

SQL (Structured Query Language) on vakiintunut tapa käsitellä tietokannan sisältöä. Kielessä on komentoja, joiden avulla tietokannan käyttäjä (esimerkiksi tietokantaa käyttävä ohjelmoija) voi lisätä, hakea, muuttaa ja poistaa tietoa.

SQL-komennot muodostuvat avainsanoista (kuten SELECT ja WHERE), taulujen ja sarakkeiden nimistä sekä muista arvoista. Esimerkiksi komento

SELECT price FROM Products WHERE name = 'retiisi';

hakee tietokannan tuotteista retiisin hinnan. Komennon lopussa on puolipiste ; ja voimme käyttää välilyöntejä ja rivinvaihtoja haluamallamme tavalla. Voisimme kirjoittaa komennon myös usealle riville esimerkiksi seuraavasti:

SELECT price
FROM Products
WHERE name = 'retiisi';

SELECT
  price
FROM
  Products
WHERE
  name = 'retiisi';

Tutustumme SQL-kieleen tarkemmin materiaalin luvuissa 2–4.

SQL-kieli syntyi 1970-luvulla, ja siinä on paljon muistumia vanhan ajan ohjelmoinnista. Tällaisia piirteitä ovat esimerkiksi:

Avainsanat ovat kokonaisia englannin kielen sanoja, ja komennot muistuttavat englannin kielen lauseita.
Avainsanoissa kirjainkoolla ei ole väliä. Esimerkiksi SELECT, select ja Select tarkoittavat samaa. Avainsanat kirjoitetaan usein kokonaan suurilla kirjaimilla.
Merkki = tarkoittaa sekä asetusta että yhtäsuuruusvertailua (nykyään ohjelmoinnissa yhtäsuuruusvertailu on yleensä ==).

SQL-kielestä on olemassa standardeja, jotka pyrkivät antamaan yhteisen pohjan kielelle. Käytännössä jokaisen tietokannan SQL-kielen toteutus toimii kuitenkin hieman omalla tavallaan. Tällä kurssilla keskitymme SQL:n ominaisuuksiin, jotka ovat yleisesti käytettävissä eri tietokannoissa.

Tietokannan sisäinen toiminta

Tietokantajärjestelmän tehtävänä on käsitellä käyttäjän antamat SQL-komennot. Esimerkiksi kun käyttäjä antaa komennon, joka hakee tietoa tietokannasta, tietokantajärjestelmän tulee löytää jokin hyvä tapa käsitellä komento ja toimittaa tulokset käyttäjälle mahdollisimman nopeasti.

SQL-kielen hienoutena on, että käyttäjän riittää kuvailla, mitä tietoa hän haluaa, minkä jälkeen tietokantajärjestelmä hoitaa työn ja hankkii tiedot tietokannan uumenista. Tämä on mukavaa käyttäjälle, koska hänen ei tarvitse tietää mitään tietokannan sisäisestä toiminnasta vaan voi luottaa tietokantajärjestelmään.

Tietokantajärjestelmän toteuttaminen on vaikea tehtävä, koska järjestelmän täytyy sekä osata käsitellä tehokkaasti SQL-komentoja että huolehtia siitä, että kaikki toimii oikein samanaikaisilla käyttäjillä ja yllättävissä tilanteissa. Tällä kurssilla tutustumme tietokantoihin lähinnä tietokannan käyttäjän näkökulmasta emmekä perehdy tietokantojen sisäiseen toimintaan.

2. SQL-kielen perusteet

Peruskomennot

Tässä luvussa tutustumme tavallisimpiin SQL-komentoihin, joiden avulla voidaan lisätä, hakea, muuttaa ja poistaa tietokannan sisältöä. Nämä komennot muodostavat perustan tietokannan käyttämiselle.

Taulun luonti

Komento CREATE TABLE luo taulun, jossa on halutut sarakkeet. Esimerkiksi seuraava komento luo taulun Products, jossa on kolme saraketta:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER
);

Voimme nimetä taulun ja sarakkeet haluamallamme tavalla. Tällä kurssilla käytäntönä on, että kirjoitamme taulun nimen suurella alkukirjaimella ja monikkomuotoisena. Sarakkeiden nimet puolestaan kirjoitamme pienellä alkukirjaimella.

Jokaisesta sarakkeesta ilmoitetaan nimen lisäksi tyyppi. Tässä taulussa sarakkeet id ja price ovat kokonaislukuja (INTEGER) ja sarake name on merkkijono (TEXT). Sarake id on lisäksi taulun pääavain (PRIMARY KEY), mikä tarkoittaa, että se yksilöi jokaisen taulun rivin ja voimme viitata sen avulla kätevästi taulun riveihin.

Pääavain

Tietokannan taulun pääavain (primary key) on jokin sarake (tai sarakkeiden yhdistelmä), joka yksilöi taulun jokaisen rivin. Millään kahdella taulun rivillä ei voi olla samaa arvoa pääavaimessa. Käytännössä hyvin tavallinen valinta pääavaimeksi on kokonaislukumuotoinen id-numero.

Usein haluamme lisäksi, että id-numerolla on juokseva numerointi. Tämä tarkoittaa, että kun tauluun lisätään rivejä, ensimmäinen rivi saa automaattisesti id-numeron 1, toinen rivi saa id-numeron 2, jne. Juoksevan numeroinnin toteuttaminen riippuu tietokannasta. Esimerkiksi SQLite-tietokannassa INTEGER PRIMARY KEY -tyyppinen sarake saa automaattisesti juoksevan numeroinnin.

Tiedon lisääminen

Komento INSERT lisää uuden rivin tauluun. Esimerkiksi seuraava komento lisää rivin äsken luomaamme tauluun Products:

INSERT INTO Products (name, price) VALUES ('retiisi', 7);

Tässä annamme arvot lisättävän rivin sarakkeille name ja price. Koska sarakkeessa id on juokseva numerointi, se saa automaattisesti arvon 1, kun kyseessä on taulun ensimmäinen rivi. Niinpä tauluun ilmestyy seuraava rivi:

id  name     price
--  -------  -----
1   retiisi  7    

Jos emme anna arvoa jollekin sarakkeelle, se saa oletusarvon. Tavallisesti oletusarvo on NULL, mikä tarkoittaa tiedon puuttumista. Esimerkiksi seuraavassa komennossa emme anna arvoa sarakkeelle price:

INSERT INTO Products (name) VALUES ('retiisi');

Tällöin tauluun ilmestyy rivi, jossa hinta on NULL (eli hinta puuttuu):

id  name     price
--  -------  -----
1   retiisi  NULL 

Esimerkkitaulu

Oletamme tämän osion tulevissa esimerkeissä, että olemme lisänneet tauluun Products seuraavat viisi riviä:

INSERT INTO Products (name, price) VALUES ('retiisi', 7);
INSERT INTO Products (name, price) VALUES ('porkkana', 5);
INSERT INTO Products (name, price) VALUES ('nauris', 4);
INSERT INTO Products (name, price) VALUES ('lanttu', 8);
INSERT INTO Products (name, price) VALUES ('selleri', 4);

Taulun sisältö on siis seuraavanlainen:

id  name      price
--  --------  -----
 retiisi   7    
 porkkana  5    
 nauris    4    
 lanttu    8    
 selleri   4    

Tiedon hakeminen

Komento SELECT suorittaa kyselyn (query) eli hakee tietoa taulusta. Yksinkertaisin tapa tehdä kysely on hakea kaikki tiedot taulusta:

SELECT * FROM Products;

Tässä tapauksessa kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 retiisi   7    
 porkkana  5    
 nauris    4    
 lanttu    8    
 selleri   4    

Kyselyssä tähti * ilmaisee, että haluamme hakea kaikki sarakkeet. Kuitenkin voimme myös hakea vain osan sarakkeista antamalla sarakkeiden nimet. Esimerkiksi seuraava kysely hakee vain tuotteiden nimet:

SELECT name FROM Products;

Kyselyn tulos on seuraava:

name
--------
retiisi    
porkkana             
nauris               
lanttu               
selleri              

Seuraava kysely puolestaan hakee tuotteiden nimet ja hinnat:

SELECT name, price FROM Products;

Nyt kyselyn tulos muuttuu näin:

name      price
--------  -----
retiisi   7    
porkkana  5    
nauris    4    
lanttu    8    
selleri   4    

Kyselyn tuloksena olevat rivit muodostavat taulun, jota kutsutaan nimellä tulostaulu (result table). Sen sarakkeet ja rivit riippuvat kyselyn sisällöstä. Esimerkiksi äskeinen kysely loi tulostaulun, jossa on kaksi saraketta ja viisi riviä.

Tietokannan käsittelyssä esiintyy siis kahdenlaisia tauluja: tietokannassa kiinteästi olevia tauluja, joihin on tallennettu tietokannan sisältö, sekä kyselyjen muodostamia väliaikaisia tulostauluja, joiden tiedot on koostettu kiinteistä tauluista.

Hakuehto

Liittämällä SELECT-kyselyyn WHERE-osan voimme valita vain osan riveistä halutun ehdon perusteella. Esimerkiksi seuraava kysely hakee tiedot lantusta:

SELECT * FROM Products WHERE name = 'lanttu';

Kyselyn tulos on seuraava:

id  name    price
--  ------  -----
4   lanttu  8    

Ehdoissa voi käyttää vertailuja ja sanoja AND ja OR samaan tapaan kuin ohjelmoinnissa. Esimerkiksi seuraava kysely etsii tuotteet, joiden hinta on välillä 4…6:

SELECT * FROM Products WHERE price >= 4 AND price <= 6;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
2   porkkana  5    
3   nauris    4    
5   selleri   4    

SQL:ssä operaattori <> tarkoittaa erisuuruutta. Esimerkiksi seuraava kysely hakee rivit, joissa hinta ei ole 4:

SELECT * FROM Products WHERE price <> 4;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
1   retiisi   7    
2   porkkana  5    
4   lanttu    8    

Järjestäminen

Oletuksena tulostaulun rivien järjestys voi olla mikä tahansa. Voimme kuitenkin määrittää halutun järjestyksen ORDER BY -osan avulla. Esimerkiksi seuraava kysely hakee tuotteet aakkosjärjestyksessä nimen mukaan:

SELECT * FROM Products ORDER BY name;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 lanttu    8    
 nauris    4    
 porkkana  5    
 retiisi   7    
 selleri   4    

Järjestys on oletuksena pienimmästä suurimpaan. Kuitenkin jos haluamme järjestyksen suurimmasta pienimpään, voimme lisätä sanan DESC sarakkeen nimen jälkeen:

SELECT * FROM Products ORDER BY name DESC;

Tämän seurauksena kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 selleri   4    
 retiisi   7    
 porkkana  5    
 nauris    4    
 lanttu    8    

Tietokantakielessä järjestys on joko nouseva (ascending) eli pienimmästä suurimpaan tai laskeva (descending) eli suurimmasta pienimpään. Oletuksena järjestys on nouseva, ja avainsana DESC tarkoittaa siis laskevaa järjestystä.

SQL-kielessä on myös avainsana ASC, joka tarkoittaa nousevaa järjestystä. Seuraavat kyselyt toimivat siis samalla tavalla:

SELECT * FROM Products ORDER BY name;

SELECT * FROM Products ORDER BY name ASC;

Käytännössä sanaa ASC käytetään kuitenkin harvoin.

Voimme myös järjestää rivejä usealla eri perusteella. Esimerkiksi seuraava kysely järjestää rivit ensisijaisesti kalleimmasta halvimpaan hinnan mukaan ja toissijaisesti aakkosjärjestykseen nimen mukaan:

SELECT * FROM Products ORDER BY price DESC, name;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 lanttu    8    
 retiisi   7    
 porkkana  5    
 nauris    4    
 selleri   4    

Tässä tapauksessa tuotteet nauris ja selleri järjestetään aakkosjärjestyksessä nimen mukaan, koska ne ovat yhtä kalliita.

Erilliset tulosrivit

Joskus tulostaulussa voi olla useita samanlaisia rivejä. Näin käy esimerkiksi seuraavassa kyselyssä:

SELECT price FROM Products;

Koska kahden tuotteen hinta on 4, kahden tulosrivin sisältönä on 4:

Jos kuitenkin haluamme vain erilaiset tulosrivit, voimme lisätä kyselyyn sanan DISTINCT:

SELECT DISTINCT price FROM Products;

Tämän seurauksena kyselyn tulos muuttuu näin:

Tulosrivien rajaus

Kun lisäämme kyselyn loppuun LIMIT x, kysely antaa vain x ensimmäistä tulosriviä. Esimerkiksi LIMIT 3 tarkoittaa, että kysely antaa kolme ensimmäistä tulosriviä.

Yleisempi muoto on LIMIT x OFFSET y, mikä tarkoittaa, että haluamme x riviä kohdasta y alkaen (0-indeksoituna). Esimerkiksi LIMIT 3 OFFSET 1 tarkoittaa, että kysely antaa toisen, kolmannen ja neljännen tulosrivin.

Tarkastellaan esimerkkinä kyselyä, joka hakee tuotteita halvimmasta kalleimpaan:

SELECT * FROM Products ORDER BY price;

Kyselyn tuloksena on seuraava tulostaulu:

id  name      price
--  --------  -----
 nauris    2    
 selleri   4    
 porkkana  5    
 retiisi   7    
 lanttu    8    

Saamme haettua kolme halvinta tuotetta seuraavasti:

SELECT * FROM Products ORDER BY price LIMIT 3;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
3   nauris    2    
5   selleri   4    
2   porkkana  5    

Seuraava kysely puolestaan hakee kolme halvinta tuotetta toiseksi halvimmasta tuotteesta alkaen:

SELECT * FROM Products ORDER BY price LIMIT 3 OFFSET 1;

Tämän kyselyn tulos on seuraava:

id  name      price
--  --------  -----
5   selleri   4    
2   porkkana  5    
1   retiisi   7    

Tietokantojen erot

Tulosrivien rajaus on esimerkki asiasta, jonka toteutus vaihtelee eri tietokannoissa. Tässä esitetty syntaksi LIMIT x OFFSET y toimii SQLiten lisäksi MySQL:ssä ja PostgreSQL:ssä.

SQL-standardiin tulosrivien rajaus tuli vasta vuonna 2008. Standardin mukainen syntaksi on OFFSET y ROWS FETCH FIRST x ROWS ONLY. Tämä syntaksi toimii esimerkiksi PostgreSQL:ssä mutta ei MySQL:ssä eikä SQLitessä.

Tiedon muuttaminen

Komento UPDATE muuttaa taulun rivejä, jotka täsmäävät haluttuun ehtoon. Esimerkiksi seuraava komento muuttaa tuotteen 2 hinnaksi 6:

UPDATE Products SET price = 6 WHERE id = 2;

Useita sarakkeita voi muuttaa yhdistämällä muutokset pilkuilla. Esimerkiksi seuraava komento muuttaa tuotteen 2 nimeksi ananas ja hinnaksi 6:

UPDATE Products SET name = 'ananas', price = 6 WHERE id = 2;

Jos komennossa ei ole ehtoa, muutos vaikuttaa kaikkiin riveihin. Esimerkiksi seuraava komento muuttaa jokaisen tuotteen hinnaksi 1:

UPDATE Products SET price = 1;

Tiedon poistaminen

Komento DELETE poistaa taulusta rivit, jotka täsmäävät annettuun ehtoon. Esimerkiksi seuraava komento poistaa taulusta tuotteen 5:

DELETE FROM Products WHERE id = 5;

Kuten muuttamisessa, jos ehtoa ei ole, niin komento vaikuttaa kaikkiin riveihin. Seuraava komento siis poistaa kaikki tuotteet taulusta:

DELETE FROM Products;

Komento DROP TABLE poistaa tietokannan taulun (ja kaiken sen sisällön). Esimerkiksi seuraava komento poistaa taulun Products:

DROP TABLE Products;

Kommentit

Merkintä -- aloittaa rivin loppuun päättyvän kommentin:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER -- price in euros
)

Toinen tapa on aloittaa kommentti /* ja lopettaa */:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER /* price in euros */
)

Yhteenveto ja ryhmittely

Yhteenvetokysely antaa tuloksena jonkin yksittäisen arvon taulun riveistä, kuten taulun rivien määrän tai sarakkeen kaikkien arvojen summan. Tällaisen kyselyn tulostaulussa on vain yksi rivi.

Yhteenvetokyselyn perustana on koostefunktio (aggregate function), joka laskee yhteenvetoarvon taulun riveistä. Tavallisimmat koostefunktiot ovat seuraavat:

COUNT(): rivien määrä
SUM(): arvojen summa
MIN(): pienin arvo
MAX(): suurin arvo
AVG(): arvojen keskiarvo

Esimerkkejä

Tarkastellaan taas taulua Products:

id  name      price
--  --------  -----
 retiisi   7    
 porkkana  5    
 nauris    4    
 lanttu    8    
 selleri   4    

Seuraava kysely hakee taulun rivien määrän:

SELECT COUNT(*) FROM Products;

COUNT(*)
--------
5

Seuraava kysely hakee niiden rivien määrän, joissa hinta on 4:

SELECT COUNT(*) FROM Products WHERE price = 4;

COUNT(*)
--------
2

Seuraava kysely hakee summan tuotteiden hinnoista:

SELECT SUM(price) FROM Products;

SUM(price)
----------
28

Tässä tapauksessa kyselyn tuloksena on 7 + 5 + 4 + 8 + 4 = 28.

Seuraava kysely hakee pienimmän ja suurimman hinnan:

SELECT MIN(price), MAX(price) FROM Products;

MIN(price)  MAX(price)
----------  ----------
4           8

Lisää COUNT-funktiosta

Jos COUNT-funktion sisällä on tähti *, kysely laskee kaikki rivit. Jos taas funktion sisällä on sarakkeen nimi, kysely laskee rivit, joissa sarakkeessa on jokin arvo eli sarakkeen arvo ei ole NULL.

Tarkastellaan esimerkkinä seuraavaa taulua, jossa rivillä 3 ei ole hintaa:

id  name     price
--  -------  -----
1   retiisi  7    
2   nauris   4    
3   lanttu   NULL 
4   selleri  4    

Seuraava kysely hakee rivien yhteismäärän:

SELECT COUNT(*) FROM Products;

COUNT(*)  
--------
4

Seuraava kysely taas hakee niiden rivien määrän, joilla on hinta:

SELECT COUNT(price) FROM Products;

COUNT(price)
------------
3

Voimme myös käyttää sanaa DISTINCT, jotta saamme laskettua, montako eri arvoa jossakin sarakkeessa on:

SELECT COUNT(DISTINCT price) FROM Products;

COUNT(DISTINCT price)
---------------------
2

Tämän kyselyn tuloksena on 2, koska sarakkeessa price on kaksi eri arvoa (4 ja 7). Tähän ei lasketa mukaan NULL-arvoa.

Tyhjän aineiston käsittely

Koostefunktioiden toiminta saattaa aiheuttaa yllätyksiä, jos käsiteltävässä aineistossa ei ole yhtään riviä. Tarkastellaan esimerkkinä seuraavaa tyhjää taulua:

id  name     price
--  -------  -----

Funktio COUNT antaa rivien määräksi 0:

SELECT COUNT(price) FROM Products;

COUNT(price)
------------
0

Sen sijaan funktio SUM antaa summaksi NULL:

SELECT SUM(price) FROM Products;

SUM(price)
----------
NULL

Tämä voi tuntua yllättävältä, koska tyhjän summan voisi olettaa olevan 0 eikä NULL. Vastaavasti myös MIN, MAX ja AVG antavat tuloksen NULL tyhjälle aineistolle.

Funktion IFNULL avulla voi varautua tilanteeseen, jossa koostefunktio saattaa antaa tuloksen NULL. Seuraavassa kyselyssä IFNULL(SUM(price), 0) tarkoittaa muuten samaa kuin SUM(price), mutta tulos NULL muutetaan tulokseksi 0.

SELECT IFNULL(SUM(price), 0) FROM Products;

IFNULL(SUM(price), 0)
---------------------´
0

NULL-arvojen käsittely

Funktio IFNULL(x, y) palauttaa arvon x, jos x ei ole NULL, ja muuten arvon y. Tämän avulla voidaan käsitellä tilanne, jossa SQL-kyselyssä oleva arvo saattaa olla NULL. Esimerkiksi IFNULL(x, 0) muuttaa arvon x nollaksi, jos se on NULL.

Funktio IFNULL ei kuulu SQL-standardiin, eikä se toimi kaikissa tietokannoissa. SQL-standardin mukainen funktio on COALESCE, jota voi käyttää samalla tavalla. Esimerkiksi COALESCE(x, 0) tarkoittaa samaa kuin IFNULL(x, 0).

Palaamme NULL-arvojen käsittelyyn tarkemmin myöhemmin.

Ryhmittely

Ryhmittelyn avulla voimme yhdistää rivikohtaista ja koostefunktion antamaa tietoa. Ideana on, että rivit jaetaan ryhmiin GROUP BY -osassa annettujen sarakkeiden mukaan ja tämän jälkeen koostefunktion arvo lasketaan jokaiselle ryhmälle erikseen.

Tarkastellaan esimerkkinä seuraavaa taulua Employees:

id  name      company   salary
--  --------  --------  ------
 Anna      Google    8000  
 Liisa     Google    7500  
 Kaaleppi  Amazon    5000  
 Uolevi    Amazon    8000  
 Maija     Google    9500  
 Vihtori   Facebook  5000  

Seuraava kysely hakee kunkin yrityksen työntekijöiden määrän:

SELECT company, COUNT(*) FROM Employees GROUP BY company;

Kyselyn tulos on seuraava:

company   COUNT(*)
--------  --------
Amazon    2       
Facebook  1       
Google    3       

Tämä tarkoittaa, että Amazonilla on 2 työntekijää, Facebookilla on 1 työntekijä ja Googlella on 3 työntekijää.

Miten ryhmittely toimii?

Ryhmittelyssä jokainen ryhmä sisältää kaikki rivit, joissa on sama sisältö ryhmittelyssä käytetyissä sarakkeissa. Ryhmittely tuottaa tulostaulun, jonka rivien määrä on sama kuin ryhmien määrä. Jokaisella rivillä voi esiintyä ryhmittelyssä käytettyjä sarakkeita sekä koostefunktioita.

Äskeisessä kyselyssä ryhmittelyn ehtona on GROUP BY company, joten rivit jaetaan ryhmiin sarakkeen company mukaan. Tässä tapauksessa ryhmät ovat:

Ryhmä 1:

id  name      company  salary
--  --------  -------  ------
3   Kaaleppi  Amazon   5000  
4   Uolevi    Amazon   8000  

Ryhmä 2:

id  name     company   salary
--  -------  --------  ------
6   Vihtori  Facebook  5000  

Ryhmä 3:

id  name   company  salary
--  -----  -------  ------
1   Anna   Google   8000  
2   Liisa  Google   7500  
5   Maija  Google   9500  

Tämän jälkeen jokaiselle ryhmälle lasketaan rivien määrä funktiolla COUNT(*).

Ryhmittelyn sarakkeet

Melko usein ryhmittelyn perusteena on tasan yksi sarake, kuten sarake company äskeisessä esimerkissä. Jos ryhmittelyssä on useampi sarake, jokainen ryhmä muodostuu riveistä, joilla on sama arvoyhdistelmä ryhmittelyyn kuuluvissa sarakkeissa.

Esimerkiksi seuraavassa kyselyssä ryhmittelyn perusteena on kaksi saraketta:

SELECT company, salary, COUNT(*) FROM Employees GROUP BY company, salary;

Koska jokaisella rivillä on eri arvoyhdistelmä näissä sarakkeissa, tulostaulussa jokainen rivi on omassa ryhmässään:

company   salary  COUNT(*)
--------  ------  --------
Amazon    5000    1       
Amazon    8000    1       
Facebook  5000    1       
Google    7500    1       
Google    8000    1       
Google    9500    1       

Ryhmittelyn jälkeen tulostaulussa voi olla ryhmittelyyn kuuluvia sarakkeita mutta ei ryhmittelyn ulkopuolisia sarakkeita. Esimerkiksi seuraava kysely ei olisi mielekäs:

SELECT company, name FROM Employees GROUP BY company;

Tässä sarake name ei kuulu ryhmittelyyn, minkä takia olisi epäselvää, mikä sarakkeen arvoksi tulisi tulostaulussa. Esimerkiksi tulostauluun tulee vain yksi rivi, joka liittyy yritykseen Amazon, mutta yrityksellä on kaksi työntekijää, joilla on eri nimet (Kaaleppi ja Uolevi).

Ryhmittely SQLitessä

Huomaa, että SQLite sallii äskeisen kyselyn, jossa haetaan ryhmittelyn ulkopuolinen sarake:

SELECT company, name FROM Employees GROUP BY company;

company   name   
--------  -------
Amazon    Uolevi 
Facebook  Vihtori
Google    Maija  

Koska sarake name ei kuulu ryhmittelyyn, sillä voi olla useita arvoja ryhmässä ja tulostauluun tulee yksi niistä jollain logiikalla valittuna. Tällainen kysely ei kuitenkaan yleensä toimi muissa tietokannoissa.

Lisää kyselyjä

Seuraava kysely hakee joka yrityksestä palkkojen summan:

SELECT company, SUM(salary) FROM Employees GROUP BY company;

company   SUM(salary)
--------  -----------
Amazon    13000      
Facebook  5000       
Google    25000      

Tässä Amazonin palkkojen summa on 5000 + 8000 = 13000, Facebookin palkkojen summa on 5000 ja Googlen palkkojen summa on 8000 + 7500 + 9500 = 25000.

Seuraava kysely puolestaan hakee korkeimman palkan:

SELECT company, MAX(salary) FROM Employees GROUP BY company;

company   MAX(salary)
--------  -----------
Amazon    8000       
Facebook  5000       
Google    9500       

Tässä Amazonin suurin palkka on 8000, Facebookin suurin palkka on 5000 ja Googlen suurin palkka on 9500.

Tulossarakkeen nimentä

Oletuksena tulostaulun sarake saa nimen suoraan kyselyn perusteella, mutta voimme halutessamme antaa myös oman nimen AS-sanan avulla. Tämän ansiosta voimme esimerkiksi selventää, mistä yhteenvetokyselyssä on kyse.

Esimerkiksi seuraavassa kyselyssä toisen sarakkeen nimeksi tulee max_salary:

SELECT
  company, MAX(salary) AS max_salary
FROM
  Employees
GROUP BY
  company;

company   max_salary
--------  ----------
Amazon    8000      
Facebook  5000      
Google    9500      

Itse asiassa sana AS ei ole pakollinen, eli voimme kirjoittaa kyselyn myös näin:

SELECT
  company, MAX(salary) max_salary
FROM
  Employees
GROUP BY
  company;

Rajaus ryhmittelyn jälkeen

Voimme lisätä kyselyyn myös HAVING-osan, joka rajaa tuloksia ryhmittelyn jälkeen. Esimerkiksi seuraava kysely hakee yritykset, joissa on ainakin kaksi työntekijää:

SELECT
  company, COUNT(*)
FROM
  Employees
GROUP BY
  company
HAVING
  COUNT(*) >= 2;

company  COUNT(*)
-------  --------
Amazon   2       
Google   3       

Voimme myös käyttää koostefunktiota vain HAVING-osassa:

SELECT
  company
FROM
  Employees
GROUP BY
  company
HAVING
  COUNT(*) >= 2;

company
-------
Amazon    
Google    

Kyselyn yleiskuva

SQL-kyselyn yleiskuva on seuraava:

SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT

Kyselystä riippuu, mitkä näistä osista siinä esiintyvät. Tämä on kuitenkin aina järjestys, jossa kyselyn osat sijaitsevat toisiinsa nähden.

Tarkastellaan seuraavaksi esimerkkiä kyselystä, joka sisältää yhtä aikaa kaikki yllä mainitut osat. Kysely suoritetaan tauluun Tasks, jossa on projekteihin liittyviä tehtäviä. Jokaisella tehtävällä on tietty prioriteetti. Tehtävä on kriittinen, jos sen prioriteetti on ainakin 3.

id  project_id  priority
--  ----------  --------
 1           3       
 1           4       
 1           4       
 2           1       
 2           5       
 3           2       
 3           4       
 3           5       

Seuraava kysely etsii projektit, joissa on vähintään kaksi kriittistä tehtävää. Kysely järjestää tulokset projektin id-numeron mukaan ja antaa 10 ensimmäistä tulosta.

SELECT
  project_id, COUNT(*)
FROM
  Tasks
WHERE
  priority >= 3
GROUP BY
  project_id
HAVING
  COUNT(*) >= 2
ORDER BY
  project_id
LIMIT
  10;

Kyselyn tulos on tässä:

project_id  COUNT(*)
----------  --------
1           3       
3           2       

Tämä tarkoittaa, että projektissa 1 on kolme kriittistä tehtävää ja projektissa 3 on kaksi kriittistä tehtävää.

Katsotaan nyt tarkemmin, miten kysely toimii. Kyselyn lähtökohtana ovat kaikki taulussa Tasks olevat rivit. Ehto WHERE priority >= 3 valitsee käsittelyyn kriittiset tehtävät:

id  project_id  priority
--  ----------  --------
 1           3       
 1           4       
 1           4       
 2           5       
 3           4       
 3           5       

Kyselyn ryhmittely GROUP BY project_id jakaa rivit ryhmiin näin:

Ryhmä 1:

id  project_id  priority
--  ----------  --------
1   1           3       
2   1           4       
3   1           4       

Ryhmä 2:

id  project_id  priority
--  ----------  --------
5   2           5       

Ryhmä 3:

id  project_id  priority
--  ----------  --------
7   3           4       
8   3           5       

Osa HAVING COUNT(*) >= 2 valitsee tulostauluun ryhmät, joissa on ainakin kaksi riviä. Tässä tapauksessa valitaan ryhmät 1 ja 3.

Tulostaulussa on joka ryhmästä sarake project_id sekä funktion COUNT(*) antama tulos eli ryhmän rivien määrä. Tässä tapauksessa projektissa 1 on kolme tärkeää tehtävää ja projektissa 3 on kaksi tärkeää tehtävää.

Osa ORDER BY project_id järjestää tulostaulun rivit projektin id-numeron mukaan. Tässä tapauksessa projektit ovat 1 ja 3. Osa LIMIT 10 ei vaikuta tässä tapauksessa, koska tulostaulussa on muutenkin alle 10 riviä.

Kysely tuottaa seuraavan tulostaulun:

project_id  COUNT(*)
----------  --------
1           3       
3           2       

SQLite-tietokanta

SQLite on yksinkertainen avoimesti saatavilla oleva tietokantajärjestelmä, joka soveltuu hyvin SQL-kielen opetteluun. Voit kokeilla helposti SQL-kieleen liittyviä asioita SQLiten avulla, ja käytämme sitä tämän kurssin harjoituksissa.

SQLite on mainio valinta SQL-kielen harjoitteluun, mutta siinä on tiettyjä rajoituksia, jotka voivat aiheuttaa ongelmia todellisissa sovelluksissa. Muita suosittuja avoimesti saatavilla olevia tietokantajärjestelmiä ovat MySQL ja PostgreSQL. Niissä on suuri määrä ominaisuuksia, jotka puuttuvat SQLitestä, mutta toisaalta niiden asentaminen ja käyttäminen on vaikeampaa.

Eri tietokantajärjestelmien välillä siirtyminen on onneksi helppoa, koska kaikissa on samantapainen SQL-kieli.

SQLite-tulkki

SQLite-tulkki on ohjelma, jonka kautta voidaan käyttää SQLite-tietokantaa. Tulkki käynnistyy antamalla komentorivillä komento sqlite3. Tämän jälkeen tulkkiin voi kirjoittaa joko suoritettavia SQL-komentoja tai pisteellä alkavia tulkin omia komentoja.

SQLite-tulkin asentaminen riippuu käyttöjärjestelmästä:

Jos käytät Linuxia, voit asentaa SQLiten paketinhallinnan kautta.
Jos käytät Macia, SQLite on luultavasti valmiiksi asennettuna koneellasi.
Jos käytät Windowsia, voit hakea SQLiten lataussivulta paketin, jonka vieressä on otsikko command-line tools (eli komentorivityökalut). Tarvittava tiedosto on se, jonka nimi alkaa sqlite3.

(Video: SQLiten asennus ja käyttö Windowsissa)

Esimerkki

SQLite-tulkissa tietokanta on oletuksena muistissa (in-memory database), jolloin se on aluksi tyhjä ja katoaa, kun tulkki suljetaan. Tämä on hyvä tapa testailla SQL-kielen ominaisuuksia. Keskustelu tulkin kanssa voi näyttää vaikkapa tältä:

$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT, price INTEGER);
sqlite> .tables
Products
sqlite> INSERT INTO Products (name, price) VALUES ('retiisi', 7);
sqlite> INSERT INTO Products (name, price) VALUES ('porkkana', 5);
sqlite> INSERT INTO Products (name, price) VALUES ('nauris', 4);
sqlite> INSERT INTO Products (name, price) VALUES ('lanttu', 8);
sqlite> INSERT INTO Products (name, price) VALUES ('selleri', 4);
sqlite> SELECT * FROM Products;
1|retiisi|7
2|porkkana|5
3|nauris|4
4|lanttu|8
5|selleri|4
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Products;
id  name      price
--  --------  -----
1   retiisi   7    
2   porkkana  5    
3   nauris    4    
4   lanttu    8    
5   selleri   4    
sqlite> .quit

Esimerkissä luodaan aluksi taulu Products ja tarkastetaan sitten tulkin komennolla .tables, mitä tauluja tietokannassa on. Ainoa taulu on Products, mikä kuuluu asiaan, koska tietokanta on alussa tyhjä.

Sitten tauluun lisätään rivejä, minkä jälkeen rivit haetaan taulusta. SQLite-tulkin oletustapa näyttää tulosrivit pystyviivoin erotettuina voi olla vaikea lukea, mutta tulostusta voi parantaa tulkin komennoilla .mode column (jokaisella sarakkeella on kiinteä leveys) ja .headers on (sarakkeiden nimet näytetään).

Lopuksi suoritetaan tulkin komento .quit, joka sulkee tulkin.

Tietokanta tiedostossa

Käynnistyksen yhteydessä SQLite-tulkille voi antaa parametrina tiedoston, johon tietokanta tallennetaan. Tällöin tietokannan sisältö säilyy tallessa tulkin sulkemisen jälkeen.

Seuraavassa esimerkissä tietokanta tallennetaan tiedostoon test.db. Tämän ansiosta tietokannan sisältö on edelleen tallessa, kun tulkki käynnistetään uudestaan.

$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT, price INTEGER);
sqlite> .tables
Products
sqlite> .quit
$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
Products
sqlite> .quit

Komennot tiedostosta

Voimme myös ohjata SQLite-tulkille tiedoston, jossa olevat komennot suoritetaan peräkkäin. Tämän avulla voimme automatisoida komentojen suorittamista. Esimerkiksi voimme laatia seuraavan tiedoston commands.sql:

commands.sql

CREATE TABLE Products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO Products (name, price) VALUES ('retiisi', 7);
INSERT INTO Products (name, price) VALUES ('porkkana', 5);
INSERT INTO Products (name, price) VALUES ('nauris', 4);
INSERT INTO Products (name, price) VALUES ('lanttu', 8);
INSERT INTO Products (name, price) VALUES ('selleri', 4);
.mode column
.headers on
SELECT * FROM Products;

Tämän jälkeen voimme ohjata komennot tiedostosta tulkille näin:

$ sqlite3 < commands.sql
id  name      price
--  --------  -----
1   retiisi   7    
2   porkkana  5    
3   nauris    4    
4   lanttu    8    
5   selleri   4    

3. Monen taulun kyselyt

Taulujen viittaukset

Keskeinen idea tietokannoissa on, että taulun rivi voi viitata toisen taulun riviin. Tällöin voidaan muodostaa kyselyjä, jotka keräävät tietoa useista tauluista viittausten perusteella. Käytännössä viittauksena on yleensä toisessa taulussa olevan rivin id-numero.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa tietokannassa on tietoa kursseista ja niiden opettajista. Oletamme, että jokaisella kurssilla on yksi opettaja ja sama opettaja voi opettaa monta kurssia.

Tallennamme tauluun Teachers tietoa opettajista. Jokaisella opettajalla on id-numero, jolla voimme viitata siihen.

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

Taulun sisältö voisi olla esimerkiksi seuraava:

id  name      
--  ----------
1   Kaila     
2   Luukkainen
3   Kivinen   
4   Laaksonen 

Taulussa Courses on puolestaan tietoa kursseista ja jokaisen kurssin kohdalla viittaus kurssin opettajaan.

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

Tässä sarake teacher_id viittaa taulussa Teachers olevan rivin id-numeroon, minkä tuo esille merkintä REFERENCES Teachers.

Taulun sisältö voisi olla esimerkiksi seuraava:

id  name              teacher_id
--  ----------------  ----------
 Laskennan mallit  3         
 Tietoverkot       1         
 Graduseminaari    1         
 PHP-ohjelmointi   4         
 Neuroverkot       3         

Tämä tarkoittaa, että esimerkiksi kurssia Laskennan mallit luennoi opettaja, jonka id-numero on 3. Taulusta Teachers selviää, että kyseinen opettaja on Kivinen.

Voimme nyt hakea kurssit opettajineen seuraavalla kyselyllä, joka hakee tietoa samaan aikaan tauluista Courses ja Teachers:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Koska kyselyssä on monta taulua, ilmoitamme sarakkeiden taulut. Esimerkiksi Courses.name viittaa taulun Courses sarakkeeseen name.

Kysely antaa seuraavan tuloksen:

name              name     
----------------  ---------
Laskennan mallit  Kivinen  
Tietoverkot       Kaila    
Graduseminaari    Kaila    
PHP-ohjelmointi   Laaksonen
Neuroverkot       Kivinen  

Mitä tässä tapahtui?

Yllä olevassa kyselyssä uutena asiana on, että kysely koskee useaa taulua (FROM Courses, Teachers), mutta mitä tämä tarkoittaa?

Ideana on, että kun kyselyssä on monta taulua, tulostaulun lähtökohtana ovat kaikki tavat valita rivien yhdistelmiä tauluista. Tämän jälkeen WHERE-osan ehdoilla voi määrittää, mitkä rivien yhdistelmät ovat kiinnostuksen kohteena.

Hyvä tapa saada ymmärrystä monen taulun kyselyn toiminnasta on tarkastella ensin kyselyä, joka hakee kaikki sarakkeet ja jossa ei ole WHERE-osaa. Yllä olevassa esimerkkitilanteessa tällainen kysely on seuraava:

SELECT * FROM Courses, Teachers;

Koska taulussa Courses on 5 riviä ja taulussa Teachers on 4 riviä, kyselyn tulostaulussa on 5 * 4 = 20 riviä. Tulostaulu sisältää kaikki mahdolliset tavat valita ensin jokin rivi taulusta Courses ja sitten jokin rivi taulusta Teachers:

id  name              teacher_id  id  name      
--  ----------------  ----------  --  ----------
 Laskennan mallit  3           1   Kaila     
 Laskennan mallit  3           2   Luukkainen
 Laskennan mallit  3           3   Kivinen   
 Laskennan mallit  3           4   Laaksonen 
 Tietoverkot       1           1   Kaila     
 Tietoverkot       1           2   Luukkainen
 Tietoverkot       1           3   Kivinen   
 Tietoverkot       1           4   Laaksonen 
 Graduseminaari    1           1   Kaila     
 Graduseminaari    1           2   Luukkainen
 Graduseminaari    1           3   Kivinen   
 Graduseminaari    1           4   Laaksonen 
 PHP-ohjelmointi   4           1   Kaila     
 PHP-ohjelmointi   4           2   Luukkainen
 PHP-ohjelmointi   4           3   Kivinen   
 PHP-ohjelmointi   4           4   Laaksonen 
 Neuroverkot       3           1   Kaila     
 Neuroverkot       3           2   Luukkainen
 Neuroverkot       3           3   Kivinen   
 Neuroverkot       3           4   Laaksonen 

Suurin osa tulosriveistä ei ole kuitenkaan kiinnostavia, koska niillä olevat tiedot eivät liity toisiinsa. Esimerkiksi ensimmäinen tulosrivi kertoo vain, että on olemassa kurssi Laskennan mallit ja on olemassa opettaja Kaila.

Jotta kysely antaisi mielekkäitä tuloksia, rajaamme hakua niin, että opettajan id-numeron tulee olla sama kummankin taulun riveissä:

SELECT
  *
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Tämän seurauksena kysely alkaa antaa mielekkäitä tuloksia:

id  name              teacher_id  id  name     
--  ----------------  ----------  --  ---------
 Laskennan mallit  3           3   Kivinen  
 Tietoverkot       1           1   Kaila    
 Graduseminaari    1           1   Kaila    
 PHP-ohjelmointi   4           4   Laaksonen
 Neuroverkot       3           3   Kivinen  

Tämän jälkeen voimme vielä siistiä kyselyn tuloksia valitsemalla meitä kiinnostavat sarakkeet:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Näin päädymme samaan tulokseen kuin aiemmin:

name              name     
----------------  ---------
Laskennan mallit  Kivinen  
Tietoverkot       Kaila    
Graduseminaari    Kaila    
PHP-ohjelmointi   Laaksonen
Neuroverkot       Kivinen  

Lisää ehtoja kyselyssä

Monen taulun kyselyissä WHERE-osa kytkee toisiinsa meitä kiinnostavat taulujen rivit, mutta lisäksi voimme laittaa WHERE-osaan muita ehtoja samaan tapaan kuin ennenkin. Esimerkiksi voimme suorittaa seuraavan kyselyn:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id AND Teachers.name = 'Kivinen';

Näin saamme haettua kurssit, joiden opettajana on Kivinen:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Neuroverkot       Kivinen

Taulujen lyhyet nimet

Voimme tiivistää monen taulun kyselyä antamalla tauluille vaihtoehtoiset lyhyet nimet, joiden avulla voimme viitata niihin kyselyssä. Esimerkiksi kysely

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

voidaan esittää lyhemmin näin:

SELECT
  C.name, T.name
FROM
  Courses AS C, Teachers AS T
WHERE
  C.teacher_id = T.id;

Sana AS ei ole pakollinen, eli voimme lyhentää kyselyä lisää:

SELECT
  C.name, T.name
FROM
  Courses C, Teachers T
WHERE
  C.teacher_id = T.id;

Saman taulun toistaminen

Monen taulun kyselyssä voi esiintyä myös monta kertaa sama taulu, kunhan toistuvalle taululle annetaan eri nimet. Esimerkiksi seuraava kysely hakee kaikki tavat valita kahden opettajan pari:

SELECT A.name, B.name FROM Teachers A, Teachers B;

Kyselyn tulos on seuraava:

name        name      
----------  ----------
Kaila       Kaila     
Kaila       Luukkainen
Kaila       Kivinen   
Kaila       Laaksonen 
Luukkainen  Kaila     
Luukkainen  Luukkainen
Luukkainen  Kivinen   
Luukkainen  Laaksonen 
Kivinen     Kaila     
Kivinen     Luukkainen
Kivinen     Kivinen   
Kivinen     Laaksonen 
Laaksonen   Kaila     
Laaksonen   Luukkainen
Laaksonen   Kivinen   
Laaksonen   Laaksonen 

Liitostaulut

Taulujen välillä esiintyy yleensä kahdenlaisia suhteita:

Yksi moneen -suhde: Taulun A rivi liittyy enintään yhteen taulun B riviin. Taulun B rivi voi liittyä useaan taulun A riviin.
Monta moneen -suhde: Taulun A rivi voi liittyä useaan taulun B riviin. Taulun B rivi voi liittyä useaan taulun A riviin.

Tapauksessa 1 voimme lisätä tauluun A sarakkeen, joka viittaa tauluun B, kuten teimme edellisen osion esimerkissä. Tapauksessa 2 tilanne on kuitenkin hankalampi, koska yksittäinen viittaus kummankaan taulun rivissä ei riittäisi. Ratkaisuna on luoda kolmas liitostaulu (join table), joka sisältää tiedot viittauksista.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa verkkokaupassa on tuotteita ja asiakkaita ja jokainen asiakas on valinnut tiettyjä tuotteita ostoskoriin. Tietyn asiakkaan korissa voi olla useita tuotteita, ja toisaalta tietty tuote voi olla usean asiakkaan korissa.

Rakennamme tietokannan, jossa on kolme taulua: Products, Customers ja Purchases. Liitostaulu Purchases ilmaisee, mitä tuotteita on kunkin asiakkaan ostoskorissa. Sen jokainen rivi esittää yhden parin muotoa “asiakkaan id korissa on tuote id”.

Oletetaan, että taulujen sisällöt ovat seuraavat:

id	name	price
1	retiisi	7
2	porkkana	5
3	nauris	4
4	lanttu	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Tämä tarkoittaa, että Uolevin korissa on porkkana ja selleri sekä Maijan korissa on retiisi, lanttu ja selleri. Aapelin korissa ei ole mitään tuotetta.

Nyt voimme hakea asiakkaat ja tuotteet seuraavasti:

SELECT
  Customers.name, Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

Kyselyn ideana on hakea tauluista Customers ja Products taulun Purchases rivejä vastaavat tiedot. Jotta saamme mielekkäitä tuloksia, kytkemme rivit yhteen kahden ehdon avulla. Kysely tuottaa seuraavan tulostaulun:

name    name    
------  --------
Uolevi  porkkana
Uolevi  selleri 
Maija   retiisi 
Maija   lanttu  
Maija   selleri 

Miten kysely toimii?

Voimme taas tutkia kyselyn toimintaa hakemalla kaikki sarakkeet ja poistamalla ehdot:

SELECT * FROM Customers, Products, Purchases;

Tämän kyselyn tulostaulussa on kaikki tavat valita jokin asiakas, tuote ja ostokset. Tulostaulussa on 5 * 3 * 5 = 75 riviä ja se alkaa näin:

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  1   retiisi   7      1            2         
 Uolevi  1   retiisi   7      1            5         
 Uolevi  1   retiisi   7      2            1         
 Uolevi  1   retiisi   7      2            4         
 Uolevi  1   retiisi   7      2            5         
 Uolevi  2   porkkana  5      1            2         
 Uolevi  2   porkkana  5      1            5         
 Uolevi  2   porkkana  5      2            1         
 Uolevi  2   porkkana  5      2            4         
 Uolevi  2   porkkana  5      2            5         
 Uolevi  3   nauris    4      1            2         
 Uolevi  3   nauris    4      1            5         
 Uolevi  3   nauris    4      2            1         
 Uolevi  3   nauris    4      2            4         
 Uolevi  3   nauris    4      2            5         
...

Sitten kun lisäämme kyselyyn ehdot, saamme rajattua kiinnostavat rivit:

SELECT
  *
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  2   porkkana  5      1            2         
 Uolevi  5   selleri   4      1            5         
 Maija   1   retiisi   7      2            1         
 Maija   4   lanttu    8      2            4         
 Maija   5   selleri   4      2            5         

Kun vielä määritämme halutut sarakkeet, tuloksena on lopullinen kysely:

SELECT
  Customers.name, Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

name    name    
------  --------
Uolevi  porkkana
Uolevi  selleri 
Maija   retiisi 
Maija   lanttu  
Maija   selleri 

Lisää ehtoja kyselyyn

Voimme lisätä kyselyyn lisää ehtoja, jos haluamme saada selville muuta ostoskoreista. Esimerkiksi seuraava kysely hakee Maijan korissa olevat tuotteet:

SELECT
  Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id AND
  Customers.name = 'Maija';

name
----------
retiisi   
lanttu    
selleri   

Seuraava kysely puolestaan kertoo, keiden korissa on selleri:

SELECT
  Customers.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id AND
  Products.name = 'selleri';

name
----------
Uolevi    
Maija    

Yhteenveto tauluista

Voimme käyttää koostefunktioita ja ryhmittelyä myös usean taulun kyselyissä. Ne käsittelevät tulostaulua samalla periaatteella kuin yhden taulun kyselyissä.

Tarkastellaan edelleen tietokantaa, jossa on tuotteita, asiakkaita ja ostoksia:

id	name	price
1	retiisi	7
2	porkkana	5
3	nauris	4
4	lanttu	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Seuraava kysely luo yhteenvedon, joka näyttää jokaisesta asiakkaasta, montako tuotetta hänen ostoskorissaan on ja mikä on tuotteiden yhteishinta.

SELECT
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Kyselyn tulos on seuraava:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 

Uolevin korissa on siis 2 tavaraa, joiden yhteishinta on 9, ja Maijan korissa on 3 tavaraa, joiden yhteishinta on 19.

Miten kysely toimii?

Kyselyn perusta on tässä:

SELECT
  *
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id;

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  2   porkkana  5      1            2         
 Uolevi  5   selleri   4      1            5         
 Maija   1   retiisi   7      2            1         
 Maija   4   lanttu    8      2            4         
 Maija   5   selleri   4      2            5         

Kun kyselyyn lisätään ryhmittely GROUP BY Customers.id, rivit jakautuvat kahteen ryhmään sarakkeen Customers.id mukaan:

Ryhmä 1:

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
1   Uolevi  2   porkkana  5      1            2         
1   Uolevi  5   selleri   4      1            5         

Ryhmä 2:

id  name   id  name     price  customer_id  product_id
--  -----  --  -------  -----  -----------  ----------
2   Maija  1   retiisi  7      2            1         
2   Maija  4   lanttu   8      2            4         
2   Maija  5   selleri  4      2            5         

Näille ryhmille lasketaan sitten tuotteiden määrä COUNT(Products.id) sekä ostosten yhteishinta SUM(Products.price).

Huomaa, että kyselyssä ryhmittely tapahtuu sarakkeen Customers.id mukaan, mutta kyselyssä haetaan sarake Customers.name. Tämä on sinänsä järkevää, koska sarake Customers.id määrää sarakkeen Customers.name. Tämä kysely toimii esimerkiksi SQLitessä, mutta jotkin tietokannat vaativat tässäkin tapauksessa, että sellaisenaan haettavan sarakkeen tulee esiintyä myös ryhmittelyssä. Tällöin ryhmittelyn tulisi olla GROUP BY Customers.id, Customers.name.

Puuttuvan rivin ongelma

Äskeinen kysely toimii sinänsä hyvin, mutta jotain puuttuu:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 

Kyselyn puutteena on vielä, että tuloksissa ei ole lainkaan kolmatta tietokannassa olevaa asiakasta eli Aapelia. Koska Aapelin korissa ei ole mitään, Aapelin rivi ei yhdisty minkään muun rivin kanssa eikä pääse osaksi tulostaulua.

Olemme törmänneet ongelmaan, mutta onneksi löydämme siihen ratkaisun pian.

JOIN-syntaksi

Tähän mennessä olemme hakeneet tietoa tauluista listaamalla taulut kyselyn FROM-osassa, mikä toimii yleensä hyvin. Kuitenkin joskus on tarpeen JOIN-syntaksi, josta on hyötyä silloin, kun kyselyn tuloksesta näyttää “puuttuvan” tietoa.

Kyselytavat

Seuraavassa on kaksi tapaa toteuttaa sama kysely, ensin käyttäen ennestään tuttua tapaa ja sitten käyttäen JOIN-syntaksia.

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

SELECT
  Courses.name, Teachers.name
FROM
  Courses JOIN Teachers ON Courses.teacher_id = Teachers.id;

JOIN-syntaksissa taulujen nimien välissä esiintyy sana JOIN ja lisäksi taulujen rivit toisiinsa kytkevä ehto annetaan erillisessä ON-osassa.

Tässä tapauksessa JOIN-syntaksi on vain vaihtoehtoinen tapa toteuttaa kysely eikä se tuo mitään uutta. Kuitenkin näemme seuraavaksi, miten voimme laajentaa syntaksia niin, että se antaa meille uusia mahdollisuuksia kyselyissä.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa tietokannassa on tutut taulut Courses ja Teachers, mutta taulussa Courses yhdeltä kurssilta puuttuu opettaja:

id  name              teacher_id
--  ----------------  ----------
 Laskennan mallit  3         
 Tietoverkot       1         
 Graduseminaari    1         
 PHP-ohjelmointi   NULL      
 Neuroverkot       3         

Rivin 4 sarakkeessa teacher_id on arvo NULL, joten jos suoritamme jommankumman äskeisen kyselyn, ongelmaksi tulee, että rivi 4 ei täsmää mihinkään taulun Teachers riviin. Tämän seurauksena tulostauluun ei tule riviä kurssista PHP-ohjelmointi:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
Neuroverkot       Kivinen

Ratkaisu ongelmaan on käyttää LEFT JOIN -syntaksia, joka tarkoittaa, että mikäli jokin vasemman taulun rivi ei yhdisty mihinkään oikean taulun riviin, kyseinen vasemman taulun rivi pääsee silti mukaan yhdeksi riviksi tulostauluun. Kyseisellä rivillä jokaisen oikeaan tauluun perustuvan sarakkeen arvona on NULL.

Tässä tapauksessa voimme toteuttaa kyselyn näin:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

Nyt tulostauluun ilmestyy myös kurssi Tietokantojen perusteet ilman opettajaa:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Miten kysely toimii?

Jälleen hyvä tapa saada selkoa kyselystä on yksinkertaistaa sitä:

SELECT
  *
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

id  name              teacher_id  id    name   
--  ----------------  ----------  ----  -------
 Laskennan mallit  3           3     Kivinen
 Tietoverkot       1           1     Kaila  
 Graduseminaari    1           1     Kaila  
 PHP-ohjelmointi   NULL        NULL  NULL   
 Neuroverkot       3           3     Kivinen

Tästä näkee, että koska vasemman taulun rivi 4 ei täsmää mihinkään oikean taulun riviin, niin kyseisestä rivistä tulee tulostauluun yksi rivi, jossa jokainen sarake oikean taulun osuudessa on NULL.

JOIN-kyselyperhe

Itse asiassa JOIN-kyselystä on olemassa peräti neljä eri muunnelmaa:

JOIN eli INNER JOIN: toimii kuten tavallinen kahden taulun kysely
LEFT JOIN: jos vasemman taulun rivi ei yhdisty mihinkään oikean taulun riviin, se valitaan kuitenkin mukaan erikseen
RIGHT JOIN: jos oikean taulun rivi ei yhdisty mihinkään vasemman taulun riviin, se valitaan kuitenkin mukaan erikseen
FULL JOIN: sekä vasemmasta että oikeasta taulusta valitaan erikseen mukaan rivit, jotka eivät yhdisty toisen taulun riviin

SQLiten rajoituksena on, että vain kaksi ensimmäistä kyselytapaa ovat mahdollisia. Onneksi LEFT JOIN on yleensä se, mitä haluamme.

ON vs. WHERE

Sana ON on oleellinen LEFT JOIN -kyselyssä, koska se asettaa ehdon niin, että mukaan otetaan myös vasemman taulun ylimääräiset rivit:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Jos käytämme sen sijasta sanaa WHERE, vasemman taulun ylimääräiset rivit jäävät pois:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers
WHERE
  Courses.teacher_id = Teachers.id;

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       Kaila  
Graduseminaari    Kaila  
Neuroverkot       Kivinen

Sinänsä kyselyssä voi esiintyä sekä ON että WHERE:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id
WHERE
  Courses.name <> 'Tietoverkot';

Tällöin ON-osa hoitaa taulujen yhdistämisen ja WHERE-osa rajaa tuloksia lisää:

name              name   
----------------  -------
Laskennan mallit  Kivinen
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Tässä tapauksessa WHERE-osan vaikutuksena on, että tulostaulussa ei ole riviä, jossa kurssina on Tietoverkot.

Jos molemmat ehdot ovatkin ON-osassa, kyselyn tulos muuttuu taas:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers
          ON Courses.teacher_id = Teachers.id AND
             Courses.name <> 'Tietoverkot';

name              name   
----------------  -------
Laskennan mallit  Kivinen
Tietoverkot       NULL   
Graduseminaari    Kaila  
PHP-ohjelmointi   NULL   
Neuroverkot       Kivinen

Tässä tapauksessa kursseja Tietoverkot ja PHP-ohjelmointi vastaavat rivit jäävät ylimääräisiksi vasemmassa taulussa, koska niitä ei voida liittää mihinkään oikean taulun riviin ON-ehdossa.

Yhteenveto toimivaksi

Nyt voimme pureutua aiempaan ongelmaan, jossa yhteenvetokyselystä puuttui tietoa. Tietokannassamme on edelleen seuraavat taulut:

id	name	price
1	retiisi	7
2	porkkana	5
3	nauris	4
4	lanttu	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Muodostimme yhteenvedon ostoskoreista seuraavalla kyselyllä:

SELECT
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Kuitenkin ongelmaksi tuli, että Aapeli puuttuu yhteenvedosta:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 

Ongelman syynä on, että Aapelin ostoskori on tyhjä eli kun kysely valitsee yhdistelmiä taulujen riveistä, ei ole mitään sellaista riviä, jolla esiintyisi Aapeli. Ratkaisu ongelmaan on käyttää LEFT JOIN -syntaksia näin:

SELECT
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Nyt myös Aapeli ilmestyy mukaan yhteenvetoon:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 
Aapeli  0                   NULL               

Koska Aapelin ostoskorissa ei ole tuotteita, hintojen summaksi tulee NULL. Voimme vielä parantaa kyselyä IFNULL-funktion avulla:

SELECT
  Customers.name, COUNT(Products.id), IFNULL(SUM(Products.price), 0)
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Tämän seurauksena mahdollinen NULL muuttuu arvoksi 0:

name    COUNT(Products.id)  IFNULL(SUM(Products.price), 0)
------  ------------------  ------------------------------
Uolevi  2                   9                             
Maija   3                   19                            
Aapeli  0                   0                             

Miten kysely toimii?

Kun kyselyssä on useita LEFT JOIN -osia, tulkintana on, että ne yhdistävät tauluja vasemmalta oikealle. Yllä olevassa kyselyssä voimme ajatella, että ensimmäinen vaihe yhdistää taulut Customers ja Purchases:

SELECT
  *
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id;

id  name    customer_id  product_id
--  ------  -----------  ----------
 Uolevi  1            2         
 Uolevi  1            5         
 Maija   2            1         
 Maija   2            4         
 Maija   2            5         
 Aapeli  NULL         NULL      

Toinen vaihe puolestaan yhdistää yllä olevan tulostaulun ja taulun Products:

SELECT
  *
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id;

id  name    customer_id  product_id  id    name      price
--  ------  -----------  ----------  ----  --------  -----
 Uolevi  1            2           2     porkkana  5    
 Uolevi  1            5           5     selleri   4    
 Maija   2            1           1     retiisi   7    
 Maija   2            4           4     lanttu    8    
 Maija   2            5           5     selleri   4    
 Aapeli  NULL         NULL        NULL  NULL      NULL 

Molemmissa vaiheissa Aapeli pääsee osaksi tulostaulua, koska kyseinen rivi ei täsmää minkään oikean taulun rivin kanssa.

4. Lisää SQL-kielestä

Tyypit ja lausekkeet

SQL-kielessä esiintyy tyyppejä ja lausekkeita samaan tapaan kuin ohjelmoinnissa. Olemme jo nähneet monia esimerkkejä SQL-komennoista, mutta nyt on hyvä hetki tutustua syvällisemmin kielen rakenteeseen.

Jokainen tietokantajärjestelmä toteuttaa tyypit ja lausekkeet vähän omalla tavallaan, ja tietokantojen toiminnassa on paljon pieniä eroja. Niinpä aiheeseen liittyvät yksityiskohdat kannattaa tarkastaa käytetyn tietokannan dokumentaatiosta.

Tyypit

Taulun määrittelyssä jokaiselle sarakkeelle annetaan tyyppi:

CREATE TABLE Movies (
  id INTEGER PRIMARY KEY,
  name TEXT,
  release_year INTEGER
);

Tässä sarakkeen name tyyppi on TEXT (merkkijono) ja sarakkeen release_year tyyppi on INTEGER (kokonaisluku). Nämä ovat yleisimmät tyypit, jotka ovat saatavilla näillä nimillä monissa tietokannoissa. Esimerkkejä muista yleisistä tyypeistä ovat TIMESTAMP (ajanhetki), REAL (liukuluku) ja BLOB (raakadata).

TEXT vs. VARCHAR

Perinteikäs tapa tallentaa merkkijono SQL:ssä on käyttää tyyppiä VARCHAR, jossa annetaan suluissa merkkijonon maksimipituus. Esimerkiksi tyyppi VARCHAR(10) tarkoittaa, että merkkijonossa voi olla enintään 10 merkkiä.

Tämä on muistuma vanhan ajan ohjelmoinnista, jossa merkkijono saatettiin esittää kiinteän pituisena merkkitaulukkona. Tyyppi TEXT on kuitenkin mukavampi, koska siinä ei tarvitse keksiä maksimipituutta.

SQLiten tyypit

Erikoinen SQLiten piirre on, että taulun määrittelyssä esiintyvä tyyppi on vain ohje, mitä tyyppiä sarakkeessa tulisi olla. Voimme kuitenkin olla välittämättä ohjeesta ja vaikkapa tallentaa kokonaisluvun kohdalle merkkijonon:

INSERT INTO Movies (name, release_year) VALUES ('Lumikki', 'abc');

Lisäksi tyypin nimenä voi olla mikä tahansa merkkijono, vaikka SQLitessä ei olisi sellaista tyyppiä. Tämän avulla voimme esimerkiksi määritellä sarakkeen, johon on tarkoitus tallentaa ajanhetki:

CREATE TABLE Bookings (
  id INTEGER PRIMARY KEY,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  description TEXT
);

SQLitessä ei ole tyyppiä TIMESTAMP, vaan ajanhetkiä käsitellään merkkijonoina, mutta tässä kuitenkin sarakkeen tyyppi ilmaisee, mitä siihen on tarkoitus tallentaa.

Lausekkeet

Lauseke on SQL-komennon osa, jolla on tietty arvo. Esimerkiksi kyselyssä

SELECT price FROM Products WHERE name = 'retiisi';

on neljä lauseketta: price, name, 'retiisi' ja name = 'retiisi'. Lausekkeet price ja name saavat arvonsa rivin sarakkeesta, lauseke 'retiisi' on merkkijonovakio ja lauseke name = 'retiisi' on totuusarvoinen.

Voimme rakentaa monimutkaisempia lausekkeita samaan tapaan kuin ohjelmoinnissa. Esimerkiksi kysely

SELECT price * 5 FROM Products;

antaa jokaisen tuotteen hinnan viisinkertaisena ja kysely

SELECT name FROM Products WHERE price % 2 = 0;

hakee tuotteet, joiden hinta on parillinen.

Hyvä tapa testata SQL:n lausekkeiden toimintaa on keskustella tietokannan kanssa tekemällä kyselyitä, jotka eivät hae tietoa mistään taulusta vaan laskevat vain tietyn lausekkeen arvon. Keskustelu voi näyttää vaikkapa seuraavalta:

sqlite> SELECT 2 * (1 + 3);
8
sqlite> SELECT 'tes' || 'ti';
testi
sqlite> SELECT 3 < 5;
1

Ensimmäinen kysely laskee lausekkeen 2 * (1 + 3) arvon. Toinen kysely yhdistää ||-operaattorilla merkkijonot 'tes' ja 'ti' merkkijonoksi 'testi'. Kolmas kysely puolestaan määrittää ehtolausekkeen 3 < 5 arvon. Tästä näkee, että SQLitessä kokonaisluku ilmaisee totuusarvon: 1 on tosi ja 0 on epätosi.

Monet SQL:n lausekkeisiin liittyvät asiat ovat tuttuja ohjelmoinnista:

laskutoimitukset: +, -, *, /, %
vertaileminen: =, <>, <, <=, >, >=
ehtojen yhdistys: AND, OR, NOT

Näiden lisäksi SQL:ssä on kuitenkin myös erikoisempia ominaisuuksia, joiden tuntemisesta on välillä hyötyä. Seuraavassa on joitakin niistä:

BETWEEN

Lauseke x BETWEEN a AND b on tosi, jos x on vähintään a ja enintään b. Esimerkiksi kysely

SELECT * FROM Products WHERE price BETWEEN 4 AND 6;

hakee tuotteet, joiden hinta on vähintään 4 ja korkeintaan 6. Voimme toki kirjoittaa samalla tavalla toimivan kyselyn myös näin:

SELECT * FROM Products WHERE price >= 4 AND price <= 6;

CASE

Rakenne CASE mahdollistaa ehtolausekkeen tekemisen. Siinä voi olla yksi tai useampi WHEN-osa sekä mahdollinen ELSE-osa. Esimerkiksi kysely

SELECT
  name,
  CASE WHEN price > 5 THEN 'kallis' ELSE 'halpa' END
FROM
  Products;

hakee kunkin tuotteen nimen sekä tiedon siitä, onko tuote kallis vai halpa. Tässä tuote on kallis, jos sen hinta on yli 5, ja muuten halpa.

Lauseke x IN (...) on tosi, jos x on jokin annetuista arvoista. Esimerkiksi kysely

SELECT
  SUM(price)
FROM
  Products
WHERE
  name IN ('lanttu', 'nauris', 'selleri');

hakee lantun, nauriin ja sellerin yhteishinnan.

LIKE

Lauseke s LIKE p on tosi, jos merkkijono s vastaa kuvausta p. Kuvauksessa voi käyttää erikoismerkkejä: merkki _ tarkoittaa mitä tahansa yksittäistä merkkiä ja merkki % tarkoittaa mitä tahansa määrää mitä tahansa merkkejä. Esimerkiksi kysely

SELECT * FROM Products WHERE name LIKE '%ri%';

hakee tuotteet, joiden nimen osana esiintyy merkkijono “ri” (kuten nauris ja selleri).

Funktiot

Lausekkeiden osana voi esiintyä funktioita samaan tapaan kuin ohjelmoinnissa. Tässä on esimerkkinä joitakin SQLiten funktioita:

ABS(x): luvun x itseisarvo
LENGTH(s): merkkijonon s pituus
LOWER(s): merkkijono s pienillä kirjaimilla
MAX(x, y): suurempi luvuista x ja y
MIN(x, y): pienempi luvuista x ja y
RANDOM(): satunnainen luku
ROUND(x, d): luku x pyöristettynä d desimaalin tarkkuudelle
SUBSTR(s, a, b): merkkijonon s kohdasta a alkaen b merkkiä
UPPER(s): merkkijono s suurilla kirjaimilla

Seuraava kysely hakee tuotteet, joiden nimessä on kuusi kirjainta (kuten lanttu ja nauris).

SELECT * FROM Products WHERE LENGTH(name) = 6;

Seuraava kysely ryhmittelee tuotteet ensimmäisen kirjaimen mukaan ja ilmoittaa kullakin kirjaimella alkavien tuotteiden määrät.

SELECT
  SUBSTR(name, 1, 1), COUNT(*)
FROM
  Products
GROUP BY
  SUBSTR(name, 1, 1);

Seuraava kysely antaa rivit satunnaisessa järjestyksessä, koska järjestys ei perustu minkään sarakkeen sisältöön vaan satunnaiseen arvoon.

SELECT * FROM Products ORDER BY RANDOM();

ORDER BY ja lausekkeet

Voisi kuvitella, että kyselyssä

SELECT * FROM Products ORDER BY 1;

rivit järjestetään lausekkeen 1 mukaan. Koska lausekkeen arvo on joka rivillä 1, tämä ei tuottaisi mitään erityistä järjestystä. Näin ei kuitenkaan ole, vaan 1 järjestää rivit ensimmäisen sarakkeen mukaan, 2 toisen sarakkeen mukaan, jne. Tämä on siis vaihtoehtoinen tapa ilmaista sarake, johon järjestys perustuu.

Kuitenkin jos ORDER BY -osassa oleva lauseke on jotain muuta kuin yksittäinen luku (kuten RANDOM()), rivit järjestetään kyseisen lausekkeen mukaisesti.

NULL-arvot

NULL on erityinen arvo, joka ilmaisee, että taulun sarakkeessa ei ole tietoa tai jokin kyselyn osa ei tuottanut tietoa. NULL on tietyissä tilanteissa kätevä, mutta voi aiheuttaa myös yllätyksiä.

Oletuksena SQLite-tulkki näyttää NULL-arvon tyhjänä:

sqlite> SELECT NULL;

Kuitenkin NULL-arvon saa näkymään tulkin komennolla .nullvalue:

sqlite> .nullvalue NULL
sqlite> SELECT NULL;
NULL

NULL on selkeästi eri asia kuin luku 0. Jos NULL esiintyy laskun osana, niin koko laskun tulokseksi tulee NULL.

sqlite> SELECT 5 + NULL;
NULL
sqlite> SELECT 2 * NULL + 1;
NULL

Myöskään tavallinen vertailu ei tuota tulosta, jos verrattavana on NULL:

sqlite> SELECT 5 = NULL;
NULL
sqlite> SELECT 5 <> NULL;
NULL

Tämä on yllättävää, koska yleensä lausekkeille a ja b pätee joko a = b tai a <> b. Voimme kuitenkin tutkia erityisen syntaksin IS NULL avulla, onko lausekkeen arvo NULL:

sqlite> SELECT 5 IS NULL;
0
sqlite> SELECT NULL IS NULL;
1

Sarakkeen puuttuva tieto

NULL-arvon yksi käyttötarkoitus on ilmaista, että jossain sarakkeessa ei ole tietoa. Esimerkiksi seuraavassa taulussa Movies Dumbon vuosi puuttuu, joten sen kohdalla on NULL:

id  name       release_year
--  ---------  ------------
 Lumikki    1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      NULL        
 Bambi      1942        

Kun haemme ensin vuoden 1940 elokuvat ja sitten kaikki elokuvat muilta vuosilta, saamme seuraavat tulokset:

SELECT * FROM Movies WHERE release_year = 1940;

id  name       release_year
--  ---------  ------------
2   Fantasia   1940        
3   Pinocchio  1940        

SELECT * FROM Movies WHERE release_year <> 1940;

id  name     release_year
--  -------  ------------
1   Lumikki  1937        
5   Bambi    1942        

Koska Dumbolla ei ole vuotta, emme saa sitä kummassakaan kyselyssä, mikä on yllättävä ilmiö. Voimme kuitenkin hakea näin elokuvat, joilla ei ole vuotta:

SELECT * FROM Movies WHERE release_year IS NULL;

id  name   release_year
--  -----  ------------
4   Dumbo  NULL        

NULL-arvo koostefunktiossa

Kun koostefunktion sisällä on lauseke (kuten sarakkeen arvo), riviä ei lasketa mukaan, jos lausekkeen arvo on NULL. Tarkastellaan esimerkkinä seuraavaa taulua Employees:

id  name      company  salary
--  --------  -------  ------
 Anna      Google   8000  
 Liisa     Google   7500  
 Kaaleppi  Amazon   NULL  
 Uolevi    Amazon   NULL  
 Maija     Google   9500  

Taulussa Googlen työntekijöillä on ilmoitettu palkka, mutta Amazonin työntekijöillä ei. Koostefunktio COUNT(salary) laskee mukaan vain rivit, joissa palkka on ilmoitettu:

SELECT COUNT(salary) FROM Employees WHERE company = 'Google';

COUNT(salary)
-------------
3

SELECT COUNT(salary) FROM Employees WHERE company = 'Amazon';

COUNT(salary)
-------------
0

Kun sitten laskemme palkkojen summia koostefunktiolla SUM(salary), saamme seuraavat tulokset:

SELECT SUM(salary) FROM Employees WHERE company = 'Google';

SUM(salary)
-----------
25000      

SELECT SUM(salary) FROM Employees WHERE company = 'Amazon';

SUM(salary)
-----------
NULL

Tämä on vähän yllättävää, koska voisi myös odottaa tyhjän summan olevan 0 eikä NULL.

NULL-arvon muuttaminen

Funktio IFNULL(a, b) palauttaa arvon a, jos a ei ole NULL, ja muuten arvon b:

sqlite> SELECT IFNULL(5, 0);
IFNULL(5, 0)
------------
5          
sqlite> SELECT IFNULL(NULL, 0);
IFNULL(NULL, 0)
---------------
0

Yllä oleva tapa on tyypillinen tapa käyttää funktiota: kun toinen parametri on 0, niin funktio muuttaa mahdollisen NULL-arvon nollaksi. Tästä on hyötyä esimerkiksi LEFT JOIN -kyselyissä SUM-funktion kanssa.

Funktio IFNULL ei ole SQL-standardin mukainen funktio, eikä se toimi kaikissa tietokannoissa. Yleisempi standardin mukainen funktio on COALESCE(...), jolle annetaan lista arvoista. Funktio palauttaa listan ensimmäisen arvon, joka ei ole NULL, tai arvon NULL, jos jokainen arvo on NULL. Jos funktiolla on kaksi parametria, se toimii samoin kuin IFNULL.

sqlite> SELECT COALESCE(1, 2, 3);
COALESCE(1, 2, 3)
-----------------
1              
sqlite> SELECT COALESCE(NULL, 2, 3);
COALESCE(NULL, 2, 3)
--------------------
2                 
sqlite> SELECT COALESCE(NULL, NULL, 3);
COALESCE(NULL, NULL, 3)
-----------------------
3                    
sqlite> SELECT COALESCE(NULL, NULL, NULL);
COALESCE(NULL, NULL, NULL)
--------------------------
NULL

Alikyselyt

Alikysely on SQL-komennon osana oleva lauseke, jonka arvo syntyy jonkin kyselyn perusteella. Voimme rakentaa alikyselyjä samaan tapaan kuin varsinaisia kyselyjä ja toteuttaa niiden avulla hakuja, joita olisi vaikea saada aikaan muuten.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa tietokannassa on pelaajien tuloksia taulussa Results. Oletamme, että taulun sisältö on seuraava:

id  name      score
--  --------  -----
 Uolevi    120  
 Maija     80   
 Liisa     120  
 Aapeli    45   
 Kaaleppi  115  

Haluamme nyt selvittää ne pelaajat, jotka ovat saavuttaneet korkeimman tuloksen, eli kyselyn tulisi palauttaa Uolevi ja Liisa. Saamme tämän aikaan alikyselyllä seuraavasti:

SELECT
  name, score
FROM
  Results
WHERE
  score = (SELECT MAX(score) FROM Results);

Kyselyn tuloksena on:

name    score
------  -----
Uolevi  120  
Liisa   120  

Tässä kyselyssä alikysely on SELECT MAX(score) FROM Results, joka antaa suurimman taulussa olevan tuloksen eli tässä tapauksessa arvon 120. Huomaa, että alikysely tulee kirjoittaa sulkujen sisään, jotta se ei sekoitu pääkyselyyn.

Alikyselyn laatiminen

Alikysely voi esiintyä melkein missä tahansa kohtaa kyselyssä, ja se voi tilanteesta riippuen palauttaa yksittäisen arvon, listan arvoista tai kokonaisen taulun.

Alikysely sarakkeessa

Seuraavassa kyselyssä alikyselyn avulla luodaan kolmas sarake, joka näyttää pelaajan tuloksen eron ennätystulokseen:

SELECT
  name, score, (SELECT MAX(score) FROM Results) - score
FROM
  Results;

name      score  (SELECT MAX(score) FROM Results) - score
--------  -----  ----------------------------------------
Uolevi    120    0                                     
Maija     80     40                                    
Liisa     120    0                                     
Aapeli    45     75                                    
Kaaleppi  115    5                                     

Koska tulostaulun sarakkeen tuottava lauseke on monimutkainen, tulostaulua voi selkeyttää nimeämällä sarakkeen uudestaan:

SELECT
  name,
  score,
  (SELECT MAX(score) FROM Results) - score AS difference
FROM
  Results;

name      score  difference
--------  -----  ----------
Uolevi    120    0         
Maija     80     40        
Liisa     120    0         
Aapeli    45     75        
Kaaleppi  115    5         

Alikysely tauluna

Seuraavassa kyselyssä alikysely luo taulun, jossa on kolme parasta tulosta. Näiden tulosten summa (120 + 120 + 115) lasketaan pääkyselyssä.

SELECT
  SUM(score)
FROM
  (SELECT * FROM Results ORDER BY score DESC LIMIT 3);

SUM(score)
----------
355

Tässä LIMIT rajaa tulostaulua niin, että siinä on vain kolme ensimmäistä riviä.

Huomaa, että ilman alikyselyä saisimme väärän tuloksen:

SELECT SUM(score) FROM Results ORDER BY score DESC LIMIT 3;

SUM(score)
----------
480     

Tässä tulostaulussa on vain yksi rivi, jossa on kaikkien tulosten summa (480). Niinpä kyselyn lopussa oleva LIMIT 3 ei vaikuta mitenkään tulokseen.

Alikysely listana

Seuraava kysely hakee pelaajat, joiden tulos kuuluu kolmen parhaimman joukkoon. Alikysely palauttaa listan tuloksista IN-lauseketta varten.

SELECT
  name
FROM
  Results
WHERE
  score IN (SELECT score FROM Results ORDER BY score DESC LIMIT 3);

name
----------
Uolevi    
Liisa     
Kaaleppi  

Riippuva alikysely

Alikysely on mahdollista toteuttaa myös niin, että sen toiminta riippuu pääkyselyssä käsiteltävästä rivistä. Näin on seuraavassa kyselyssä:

SELECT
  name,
  score,
  (SELECT COUNT(*) FROM Results WHERE score > R.score) AS better_count
FROM
  Results R;

Tämä kysely laskee jokaiselle pelaajalle, monenko pelaajan tulos on parempi kuin pelaajan oma tulos. Esimerkiksi Maijalle vastaus on 3, koska Uolevin, Liisan ja Kaalepin tulos on parempi. Kysely antaa seuraavan tuloksen:

name      score  better_count
--------  -----  ------------
Uolevi    120    0           
Maija     80     3           
Liisa     120    0           
Aapeli    45     4           
Kaaleppi  115    2           

Koska taulu Results esiintyy kahdessa roolissa alikyselyssä, pääkyselyn taululle on annettu nimi R. Tämän ansiosta alikyselyssä on selvää, että halutaan laskea rivejä, joiden tulos on parempi kuin pääkyselyssä käsiteltävän rivin tulos.

Tässä on vielä toinen esimerkki riippuvasta alikyselystä:

SELECT
  name
FROM
  Results R
WHERE
  (SELECT COUNT(*) FROM Results WHERE score < R.score) >= 1;

Tämä kysely etsii pelaajat, joilla on parempi tulos kuin jollakin toisella pelaajalla. Tässä alikysely laskee, monellako pelaajalla on huonompi tulos, ja kyselyn ehtona on, että alikyselyn tulos on vähintään yksi. Kyselyn tuloksena on:

name
----------
Uolevi    
Maija     
Liisa     
Kaaleppi  

Tässä tapauksessa kysely palauttaa kaikki pelaajat paitsi Aapelin, jonka tulos on huonoin.

SQL:ssä on myös avainsana EXISTS, joka ilmaisee, palauttaako alikysely vähintään yhden rivin. Tämän avulla äskeinen kysely voidaan kirjoittaa selkeämmin:

SELECT
  name
FROM
  Results R
WHERE
  EXISTS (SELECT * FROM Results WHERE score < R.score);

Milloin käyttää alikyselyä?

Melko usein alikysely on vaihtoehtoinen tapa toteuttaa kysely, jonka voisi tehdä jotenkin muutenkin. Esimerkiksi molemmat seuraavat kyselyt hakevat tuotteiden nimet asiakkaan 1 ostoskorissa:

SELECT
  Products.name
FROM
  Products, Purchases
WHERE
  Products.id = Purchases.product_id AND Purchases.customer_id = 1;

SELECT
  name
FROM
  Products
WHERE
  id IN (SELECT product_id FROM Purchases WHERE customer_id = 1);

Ensimmäinen kysely on tyypillinen kahden taulun kysely, kun taas toinen kysely valikoi tuotteet alikyselyn avulla. Kumpi kysely on parempi?

Ensimmäinen kysely on parempi, koska tämä on tarkoitettu tapa hakea SQL:ssä tietoa tauluista viittausten avulla. Toinen kysely toimii sinänsä, mutta se poikkeaa totutusta eikä tietokantajärjestelmä myöskään pysty ehkä suorittamaan sitä yhtä tehokkaasti.

Alikyselyä kannattaa käyttää vain silloin, kun siihen on todellinen syy. Jos kyselyn voi tehdä helposti usean taulun kyselyllä, tämä on yleensä parempi ratkaisu.

Lisää tekniikoita

Tässä osiossa on lisää näytteitä SQL:n mahdollisuuksista. Näistä tekniikoista on hyötyä joidenkin SQL Trainerin vaikeiden tehtävien ratkaisemisessa.

Kumulatiivinen summa

Hyödyllinen taito SQL:ssä on osata laskea kumulatiivinen summa eli jokaiselle riville summa sarakkeen luvuista kyseiselle riville asti. Tarkastellaan esimerkiksi seuraavaa taulua Items:

id  value
--  -----
1   200  
2   100  
3   400  
4   100  

Voimme laskea kumulatiivisen summan kahden taulun kyselyllä näin:

SELECT
  A.id, SUM(B.value)
FROM
  Items A, Items B
WHERE
  B.id <= A.id
GROUP BY
  A.id;

id  SUM(B.value)
--  ------------
1   200         
2   300         
3   700         
4   800         

Tässä on ideana, että summa lasketaan taulun A riville ja taulusta B haetaan kaikki rivit, joiden id on pienempi tai sama kuin taulun A rivillä. Halutut summat saadaan laskettua SUM-funktiolla ryhmittelyn jälkeen.

Vastaavaa tekniikkaa voi käyttää muissakin tilanteissa, jos haluamme laskea tuloksen, joka riippuu jotenkin kaikista “pienemmistä” riveistä taulussa.

Sisäkkäiset koosteet

Tarkastellaan tilannetta, jossa haluamme selvittää, mikä on suurin määrä elokuvia, jotka ovat ilmestyneet samana vuonna. Esimerkiksi seuraavassa taulussa Movies haluttu tulos on 2, koska vuonna 1940 ilmestyi kaksi elokuvaa.

id  name       release_year
--  ---------  ------------
 Lumikki    1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      1941        
 Bambi      1942        

Tämä on vähän hankalalta vaikuttava tilanne, koska meidän tulisi tehdä sisäkkäin kyselyt COUNT, joka laskee ilmestymismääriä, ja sitten MAX, joka hakee suurimman arvon. SQL ei salli kuitenkaan kyselyä SELECT MAX(COUNT(release_year)) tai vastaavaa.

Voimme ottaa kuitenkin lähtökohdaksi kyselyn, joka ryhmittelee elokuvat vuoden mukaan ja hakee jokaisesta ryhmästä elokuvien määrän:

SELECT COUNT(*) FROM Movies GROUP BY release_year;

COUNT(*)  
--------
1         
2         
1         
1       

Näistä luvuista pitää vielä saada haettua suurin, mikä onnistuu alikyselyn avulla. Tässä tapauksessa kätevä tapa on käyttää alikyselyä niin, että sen tulos on pääkyselyn FROM-osassa, jolloin alikysely luo taulun, josta pääkysely hakee tietoa:

SELECT MAX(year_count) FROM (
  SELECT COUNT(*) year_count FROM Movies GROUP BY release_year
);

MAX(year_count)
---------------
2       

Entä voisiko tehtävän ratkaista ilman alikyselyä? Kyllä, koska voimme järjestää tulokset suurimmasta pienimpään ja valita tulostaulun ensimmäisen rivin:

SELECT COUNT(*) AS year_count FROM Movies GROUP BY release_year
ORDER BY year_count DESC LIMIT 1;

year_count
----------
2          

Sijaluvut

Tarkastellaan vielä taulua Results, jossa on pelaajia ja heidän tuloksiaan:

id  name      score
--  --------  -----
 Aapeli    45   
 Kaaleppi  115  
 Liisa     120  
 Maija     80   
 Uolevi    120  

Tavoitteena on hakea rivit järjestyksessä tuloksen mukaan suurimmasta pienempään ja ilmoittaa lisäksi kunkin rivin sijaluku. Yksi tapa toteuttaa tämä on tehdä alikysely, joka laskee, monellako rivillä tulos on parempi, jolloin sija on yhtä suurempi kuin alikyselyn tulos:

SELECT
  (SELECT COUNT(*) FROM Results WHERE score > R.score) + 1 AS place,
  name, score
FROM
  Results R
ORDER BY
  score DESC, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Samalla idealla voidaan laskea sijaluvut myös niin, että jokaisella on eri sija ja yhtä suuren tuloksen tapauksessa aakkosjärjestys ratkaisee sijan:

SELECT
  (SELECT COUNT(*) FROM Results WHERE score > R.score OR
    (score = T.score AND name < T.name)) + 1 AS place,
  name, score
FROM
  Results R
ORDER BY
  score DESC, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Vaihtoehtoinen tapa laskea sijalukuja on ikkunafunktio, jos käytetty tietokanta sallii sen. Esimerkiksi SQLiten uusissa versioissa ikkunafunktion RANK avulla voidaan laskea vastaavat sijaluvut kuin äskeisissä esimerkeissä.

SELECT
  RANK() OVER (ORDER BY score DESC) place, name, score
FROM
  Results
ORDER BY
  place, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

SELECT
  RANK() OVER (ORDER BY score DESC, name) place, name, score
FROM
  Results
ORDER BY
  place, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Listojen vertailu

Tarkastellaan taulua Lists, johon on tallennettu listojen sisältö. Esimerkiksi seuraavassa taulussa lista 1 sisältää luvut [2, 4, 5], lista 2 sisältää luvut [3, 5] ja lista 3 sisältää luvut [2, 4, 5]:

id  list_id  value
--  -------  -----
 1        2    
 1        4    
 1        5    
 2        3    
 2        5    
 3        2    
 3        4    
 3        5    

Seuraava kysely laskee jokaiselle listaparille, montako yhteistä lukua niillä on:

SELECT
  A.list_id, B.list_id, COUNT(*)
FROM
  Lists A, Lists B
WHERE
  A.value = B.value
GROUP BY
  A.list_id, B.list_id;

list_id  list_id  COUNT(*)
-------  -------  --------
      1        3       
      2        1       
      3        3       
      1        1       
      2        2       
      3        1       
      1        3       
      2        1       
      3        3       

Tästä selviää, että esimerkiksi listoilla 1 ja 2 on yksi yhteinen luku (5) ja listoilla 1 ja 3 on kolme yhteistä lukua (2, 4, 5). Tällaista kyselyä laajentamalla voidaan vaikkapa vertailla, onko kahdella listalla täysin sama sisältö. Näin on silloin, kun listoilla on yhtä monta lukua ja yhteisten lukujen määrä on yhtä suuri kuin yksittäisen listan lukujen määrä.

5. Tietokannat ohjelmoinnissa

Testitietokanta

Tämän luvun esimerkeissä ja kurssin tehtävissä käytetään testitietokantaa, joka sisältää tiedot kaupunkipyörillä tehdyistä matkoista vuonna 2024 Helsingin ja Espoon alueella. Tietokanta perustuu HSL:n julkaisemaan avoimen datan aineistoon, joka on muutettu tätä kurssia varten SQLite-tietokannaksi.

Voit kopioida tietokannan itsellesi tästä: bikes_2024.zip

Tietokannassa on seuraavat kaksi taulua:

CREATE TABLE Stations (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE Trips (
    id INTEGER PRIMARY KEY,
    start_time TEXT,
    end_time TEXT,
    start_station_id INTEGER REFERENCES Stations,
    end_station_id INTEGER REFERENCES Stations,
    distance INTEGER,
    duration INTEGER
);

Taulu Stations sisältää tiedot kaupunkipyöräasemista. Taulussa on kaksi saraketta: id (id-numero) sekä name (aseman nimi).

Taulu Trips sisältää tiedot matkoista. Taulussa on seuraavat sarakkeet:

id: id-numero
start_time: matkan aloitusaika
end_time: matkan lopetusaika
start_station_id: aloitusaseman id-numero
end_station_id: lopetusaseman id-numero
distance: matkan pituus (metreinä)
duration: matkan kesto (sekunteina)

Voimme tutkia tietokannan sisältöä SQLite-tulkin kautta seuraavaan tapaan:

$ sqlite3 bikes_2024.db
sqlite> .tables
Stations  Trips
sqlite> SELECT COUNT(*) FROM Stations;
458
sqlite> SELECT COUNT(*) FROM Trips;
2585668
sqlite> SELECT * FROM Stations LIMIT 10;
1|Kaivopuisto
2|Laivasillankatu
3|Kapteeninpuistikko
4|Viiskulma
5|Sepänkatu
6|Hietalahdentori
7|Designmuseo
8|Vanha kirkkopuisto
9|Erottajan aukio
10|Kasarmitori
sqlite> SELECT * FROM Trips WHERE id = 100;
100|2024-04-01T10:05:03|2024-04-01T10:26:19|119|259|4627|1271
sqlite> SELECT name FROM Stations WHERE id = 119;
Gebhardinaukio
sqlite> SELECT name FROM Stations WHERE id = 259;
Petter Wetterin tie
sqlite> .quit

Tämän perusteella tietokannassa on 458 asemaa ja 2585668 matkaa. Esimerkiksi matka id-numerolla 100 alkoi Gebhardinaukiolta ja päättyi Petter Wetterin tielle. Matkan pituus oli 4,6 kilometriä ja kesto reilut 21 minuuttia.

Katsotaan seuraavaksi, miten voimme käsitellä tätä tietokantaa Python- ja R-kielillä.

SQLite Pythonissa

Python-kielen standardikirjastossa on moduuli sqlite3, jonka avulla voidaan käyttää SQLite-tietokantaa. Seuraava koodi muodostaa yhteyden tietokantaan ja hakee taulusta Stations tietoa SQL-kyselyillä:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

result = db.execute("SELECT id, name FROM Stations WHERE id = 5")
print(result.fetchone())

result = db.execute("SELECT id, name FROM Stations ORDER BY id LIMIT 10")
print(result.fetchall())

Koodin tulostus on seuraava:

(5, 'Sepänkatu')
[(1, 'Kaivopuisto'), (2, 'Laivasillankatu'), (3, 'Kapteeninpuistikko'), (4, 'Viiskulma'), (5, 'Sepänkatu'), (6, 'Hietalahdentori'), (7, 'Designmuseo'), (8, 'Vanha kirkkopuisto'), (9, 'Erottajan aukio'), (10, 'Kasarmitori')]

Tässä db on tietokantaolio, jonka kautta voidaan suorittaa SQL-komentoja metodilla execute. Tässä koodissa suoritetaan kaksi SELECT-komentoa.

Ensimmäinen SELECT-komento hakee taulusta Stations rivin, jonka id-numero on 5. Koska kysely palauttaa yhden rivin, käytetään metodia fetchone, joka palauttaa yhden rivin tuplena.

Toinen SELECT-komento hakee taulusta Stations kymmenen ensimmäistä riviä. Nyt käytetään metodia fetchall, joka palauttaa listan, jossa jokainen tuple vastaa yhtä tulostaulun riviä.

Tietokantatiedoston sijainti

Tyypillinen ongelma tietokantaa käyttävässä koodissa on, että tietokantatiedosto sijaitsee eri paikassa tietokoneella kuin koodi olettaa sen sijaitsevan. Jos et saa yllä olevaa koodia toimimaan, syy on luultavasti tässä.

Jos koodissa viitattua tietokantatiedostoa ei ole olemassa, koodi luo uuden tyhjän tietokantatiedoston. Koska tässä tietokannassa ei ole taulua Stations, yritys hakea tietoa taulusta epäonnistuu.

Esimerkki: Aseman nimi

Seuraava koodi kysyy käyttäjältä aseman id-numeroa ja hakee sen perusteella aseman nimen tietokannasta:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

station_id = input("Anna aseman id-numero: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])
station_name = result.fetchone()[0]
print("Aseman nimi:", station_name)

Koodin suoritus voi näyttää seuraavalta:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Tässä kyselyn osana on parametri ?, jonka arvoksi tulee muuttujassa station_id oleva id-numero. Koodi näyttää, miten metodin execute kutsumisen yhteydessä annetaan parametrien arvot listana.

Tässä tapauksessa metodi fetchone palauttaa tuplen, jossa on yksi alkio. Tämän alkion sisältö saadaan muuttujaan []-syntaksilla, jossa 0 tarkoittaa tuplen ensimmäisen alkion hakemista.

Koodin heikkoutena on vielä, että se ei ota huomioon tilannetta, jossa tietokannassa ei ole asemaa annetulla id-numerolla:

Anna aseman id-numero: 666
Traceback (most recent call last):
  File "test.py", line 7, in <module>
    station_name = result.fetchone()[0]
                   ~~~~~~~~~~~~~~~~~^^^
TypeError: 'NoneType' object is not subscriptable

Tässä tilanteessa metodi fetchone palauttaa arvon None, jolloin ei ole mahdollista hakea tuplen ensimmäistä alkiota. Voimme käsitellä tämän tilanteen esimerkiksi seuraavasti try/except-rakenteen avulla:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

station_id = input("Anna aseman id-numero: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])

try:
    station_name = result.fetchone()[0]
    print("Aseman nimi:", station_name)
except TypeError:
    print("Asemaa ei löytynyt")

Nyt koodi antaa selkeän viestin siitä, ettei asemaa löytynyt:

Anna aseman id-numero: 666
Asemaa ei löytynyt

Tässä on vielä toinen toteutustapa, jossa tietokantahaku suoritetaan funktion find_station_name kautta. Tämä funktio palauttaa aseman nimen tai merkkijonon --, jos asemaa ei löytynyt.

import sqlite3

db = sqlite3.connect("bikes_2024.db")

def find_station_name(station_id):
    result = db.execute("SELECT name FROM Stations WHERE id = ?",
                        [station_id])
    result_row = result.fetchone()
    return result_row[0] if result_row else "--"

station_id = input("Anna aseman id-numero: ")

station_name = find_station_name(station_id)
print("Aseman nimi:", station_name)

Nyt koodi toimii seuraavasti:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Anna aseman id-numero: 666
Aseman nimi: --

Esimerkki: Kohdeasemat

Seuraava koodi kysyy käyttäjältä lähtöaseman ja päivämäärän ja etsii sitten kaikki kohdeasemat, joihin päättyi lähtöasemalta alkanut matka annettuna päivänä.

import sqlite3

db = sqlite3.connect("bikes_2024.db")

def find_destinations(station_name, date):
    sql = """SELECT
                 DISTINCT B.name
             FROM
                 Stations AS A, Stations AS B, Trips AS T
             WHERE
                 T.start_station_id = A.id AND
                 T.end_station_id = B.id AND
                 A.name = ? AND
                 T.start_time LIKE ?
             ORDER BY
                 B.name"""
    result = db.execute(sql, [station_name, date + "%"])
    return [row[0] for row in result.fetchall()]

station_name = input("Anna aseman nimi: ")
date = input("Anna päivämäärä: ")

destinations = find_destinations(station_name, date)
print("Number of destinations:", len(destinations))
for destination in destinations:
    print(destination)

Tässä on esimerkki koodin toiminnasta:

Anna aseman nimi: Syystie
Anna päivämäärä: 2024-05-16
Number of destinations: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Tässä tarvittava SQL-komento on monimutkainen, minkä takia se on jaettu koodissa usealle riville. Tässä on kätevä Pythonin """-syntaksi, jonka avulla pystyy määrittelemään usean rivin merkkijonon.

Komennolle annetaan kaksi parametria, jotka sijoitetaan merkkien ? kohdalle siinä järjestyksessä kuin ne on annettu listassa. Listan ensimmäinen alkio menee ensimmäisen merkin ? kohdalle ja toinen alkio menee toisen merkin ? kohdalle. Koska parametrit ovat merkkijonoja, ne sijoitetaan '-merkkien sisään SQL:ssä.

Tiettynä päivänä alkavat matkat saadaan etsittyä LIKE-syntaksin avulla rajoittamalla hakua niin, että sarakkeen start_time arvon alussa tulee olla annettu päivämäärä. Merkki % ilmaisee, että päivämäärän jälkeen voi tulla mikä tahansa kellonaika.

SQLite R-kielessä

R:ssä SQLite-tietokantaa käytetään tyypillisesti kirjaston RSQLite avulla, jonka voi asentaa komennolla install.packages("RSQLite"). Seuraava koodi muodostaa yhteyden tietokantaan ja hakee taulusta Stations tietoa SQL-kyselyillä:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

result <- dbGetQuery(db, "SELECT id, name FROM Stations WHERE id = 5")
print(result)

result <- dbGetQuery(db, "SELECT id, name FROM Stations ORDER BY id LIMIT 10")
print(result)

Koodin tulostus on seuraava:

  id      name
5 Sepänkatu

   id               name
 1        Kaivopuisto
 2    Laivasillankatu
 3 Kapteeninpuistikko
 4          Viiskulma
 5          Sepänkatu
 6    Hietalahdentori
 7        Designmuseo
 8 Vanha kirkkopuisto
 9    Erottajan aukio
10        Kasarmitori

Tässä db on tietokantaolio, jonka kautta voidaan suorittaa SQL-komentoja funktiolla dbGetQuery. Tässä koodissa suoritetaan kaksi SELECT-komentoa.

Ensimmäinen SELECT-komento hakee taulusta Stations rivin, jonka id-numero on 5. Toinen SELECT-komento hakee taulusta Stations kymmenen ensimmäistä riviä.

Tietokantatiedoston sijainti

RStudiossa hakemiston voi määrittää valikosta Session -> Set Working Directory -> To Source File Location. Tämän jälkeen koodin pitäisi toimia, jos tietokantatiedosto on samassa hakemistossa kuin kooditiedosto.

Esimerkki: Aseman nimi

Seuraava koodi kysyy käyttäjältä aseman id-numeroa ja hakee sen perusteella aseman nimen tietokannasta:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

station_id <- readline(prompt = "Anna aseman id-numero: ")
result <- dbGetQuery(db,
                     "SELECT name FROM Stations WHERE id = ?",
                     params = list(station_id))

station_name <- result$name[1]
cat("Aseman nimi:", station_name, "\n")

Koodin suoritus voi näyttää seuraavalta:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Tässä kyselyn osana on parametri ?, jonka arvoksi tulee muuttujassa station_id oleva id-numero. Koodi näyttää, miten funktion dbGetQuery kutsumisen yhteydessä annetaan parametrien arvot listana.

Tässä syntaksi result$name[1] tarkoittaa, että haetaan sarakkeen name arvo tulostaulun rivillä 1 eli tässä tapauksessa ainoalla rivillä.

Jos tietokannassa ei ole asemaa annetulla id-numerolla, koodi antaa seuraavan tuloksen (NA tarkoittaa puuttuvaa arvoa):

Anna aseman id-numero: 666
Aseman nimi: NA

Voimme antaa tässä tilanteessa selkeämmän viestin asiasta esimerkiksi tarkastamalla tulostaulun rivien määrän:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

station_id <- readline(prompt = "Anna aseman id-numero: ")

result <- dbGetQuery(db,
                     "SELECT name FROM Stations WHERE id = ?",
                     params = list(station_id))

if (nrow(result) == 1) {
  station_name <- result$name[1]
  cat("Aseman nimi:", station_name, "\n")
} else {
  cat("Asemaa ei löytynyt\n")
}

Nyt koodi antaa selkeän viestin siitä, ettei asemaa löytynyt:

Anna aseman id-numero: 666
Asemaa ei löytynyt

Tässä on vielä toinen toteutustapa, jossa tietokantahaku suoritetaan funktion find_station_name kautta. Tämä funktio palauttaa aseman nimen tai merkkijonon --, jos asemaa ei löytynyt.

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

find_station_name <- function(station_id) {
  result <- dbGetQuery(db,
                       "SELECT name FROM Stations WHERE id = ?",
                       params = list(station_id))
  if (nrow(result) == 1) result$name[1] else "--"
}

station_id <- readline(prompt = "Anna aseman id-numero: ")
station_name <- find_station_name(station_id)
cat("Aseman nimi:", station_name, "\n")

Nyt koodi toimii seuraavasti:

Anna aseman id-numero: 42
Aseman nimi: Haapaniemenkatu

Anna aseman id-numero: 666
Aseman nimi: --

Esimerkki: Kohdeasemat

Seuraava koodi kysyy käyttäjältä lähtöaseman ja päivämäärän ja etsii sitten kaikki kohdeasemat, joihin päättyi lähtöasemalta alkanut matka annettuna päivänä.

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

find_destinations <- function(station_name, date) {
  sql <- "
    SELECT DISTINCT B.name
    FROM Stations AS A, Stations AS B, Trips AS T
    WHERE
      T.start_station_id = A.id AND
      T.end_station_id = B.id AND
      A.name = ? AND
      T.start_time LIKE ?
    ORDER BY B.name
  "
  res <- dbGetQuery(db, sql, params = list(station_name, paste0(date, "%")))
  res$name
}

station_name <- readline(prompt = "Anna aseman nimi: ")
date <- readline(prompt = "Anna päivämäärä: ")

destinations <- find_destinations(station_name, date)
cat("Number of destinations:", length(destinations), "\n")
for (destination in destinations) {
  cat(destination, "\n")
}

Tässä on esimerkki koodin toiminnasta:

Anna aseman nimi: Syystie
Anna päivämäärä: 2024-05-16
Number of destinations: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Tiettynä päivänä alkavat matkat saadaan etsittyä LIKE-syntaksin avulla rajoittamalla hakua niin, että sarakkeen start_time arvon alussa tulee olla annettu päivämäärä. Funktio paste0 lisää päivämäärän perään merkin %, joka ilmaisee, että päivämäärän jälkeen voi tulla mikä tahansa kellonaika.

Mitä tehdä missäkin?

Tietokannan ja koodin puolella voi usein tehdä samantapaisia asioita. Esimerkiksi seuraavassa on kaksi tapaa etsiä pisin matka tietokannasta:

result = db.execute("SELECT MAX(distance) FROM Trips")
max_distance = result.fetchone()[0]
print(max_distance)

result = db.execute("SELECT distance FROM Trips")
max_distance = max(result.fetchall())[0]
print(max_distance)

Ensimmäisessä tavassa haetaan pisin matka tietokannan puolella SQL:n MAX-funktiolla. Toisessa tavassa haetaan tietokannasta kaikkien matkojen pituudet listaan ja etsitään sitten koodin puolella listan pisin matka Pythonin max-funktiolla.

Näistä kahdesta tavasta ensimmäinen tapa on selkeästi parempi: ei ole hyvä hakea turhaa tietoa koodin puolelle ja tehdä käsittelyä, jonka voi tehdä helposti myös tietokannassa.

Erityisesti kannattaa välttää tilannetta, jossa suoritetaan turhaan useita SQL-komentoja, vaikka vain yksi komento riittäisi. Esimerkiksi seuraavassa on huono tapa hakea tietokannasta jokaisesta asemasta matkojen määrä:

result = db.execute("SELECT id, name FROM Stations")
stations = result.fetchall()

for station_id, station_name in stations:
    result = db.execute("""SELECT COUNT(*) FROM Trips
                           WHERE start_station_id = ?""",
                        [station_id])
    trip_count = result.fetchone()[0]
    print(station_name, trip_count)

Koodi hakee ensin listaan kunkin aseman id-numeron ja nimen. Tämän jälkeen koodi hakee silmukassa jokaisesta asemasta erikseen niiden matkojen määrän, jotka alkoivat kyseiseltä asemalta. Koodi on kyllä toimiva mutta se tekee valtavasti turhaa työtä hakiessaan jokaisen tiedon erikseen. Parempi ratkaisu on muodostaa yksi kysely, joka hakee suoraan kaiken tarvittavan:

sql = """SELECT S.name, COUNT(*)
         FROM Stations AS S
           LEFT JOIN Trips AS T ON S.id = T.start_station_id
         GROUP BY S.id"""

data = db.execute(sql).fetchall()
for station_name, trip_count in data:
    print(station_name, trip_count)

Tuloksena oleva kysely on monimutkaisempi, mutta sen avulla tietokantajärjestelmä voi optimoida kokonaisuutena halutun tiedon hakemisen ja toimittaa tiedon mahdollisimman tehokkaasti koodille.

6. Tietokannan suunnittelu

Suunnittelun periaatteet

Tietokannan suunnittelussa tulee päättää tietokannan rakenne: mitä tauluja tietokannassa on sekä mitä sarakkeita kussakin taulussa on. Tähän on sinänsä suuri määrä mahdollisuuksia, mutta tuntemalla muutaman periaatteen pääsee pitkälle.

Hyvä tavoite suunnittelussa on, että tuloksena olevaa tietokantaa on mukavaa käyttää SQL-kielen avulla. Tietokannan rakenteen tulisi olla sellainen, että tietoa pystyy hakemaan ja muuttamaan näppärästi SQL-komennoilla.

Tietokannan suunnittelun periaatteet ovat hyödyllisiä ja johtavat usein toimiviin ratkaisuihin. Kuitenkin kannattaa miettiä, mikä periaatteissa on taustalla ja milloin kannattaa mahdollisesti tehdä toisin. Hyvä tavoite on luoda käyttötarkoitukseen sopiva tietokanta eikä noudattaa periaatteita ilman omaa ajattelua.

Taulu vs. luokka

Tietokannan taulun määrittely kuvaa, minkä tyyppistä tietoa tauluun voidaan tallentaa. Esimerkiksi seuraavassa taulussa Movies jokainen taulun rivi sisältää elokuvan nimen ja julkaisuvuoden:

CREATE TABLE Movies (
  id INTEGER PRIMARY KEY,
  name TEXT,
  release_year INTEGER
);

INSERT INTO Movies (name, release_year) VALUES ('Lumikki', 1937);
INSERT INTO Movies (name, release_year) VALUES ('Fantasia' 1940);
INSERT INTO Movies (name, release_year) VALUES ('Pinocchio', 1940);

Monissa ohjelmointikielissä luokan määrittely kuvaa, minkä tyyppistä tietoa oliot sisältävät. Esimerkiksi seuraava Python-koodi määrittelee luokan Movie, joka sisältää elokuvan nimen ja julkaisuvuoden. Tämän jälkeen koodi lisää olioita listaan.

@dataclass
class Movie:
    name: str
    release_year: int

movies = []
movies.append(Movie("Lumikki", 1937))
movies.append(Movie("Fantasia", 1940))
movies.append(Movie("Pinocchio", 1940))

Tietokannan taulun määrittely muistuttaa siis ohjelmoinnin luokkaa, ja yksittäinen taulun rivi on lähellä luokasta luotua oliota.

Yksi vai useita tauluja?

Ohjelmoinnissa kaikki saman tyyppiset oliot perustuvat samaan luokkaan, ja vastaavasti tietokannassa kaikki saman tyyppiset rivit ovat yhdessä taulussa. Tämän ansiosta voimme käsitellä rivejä kätevästi SQL-komennoilla.

Esimerkiksi jos tietokannassa on elokuvia, hyvä ratkaisu on tallentaa kaikki elokuvat samaan tauluun Movies:

id  name       release_year
--  ---------  ------------
 Lumikki    1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      1941        
 Bambi      1942        

Tästä taulusta voimme hakea esimerkiksi vuoden 1940 elokuvat näin:

SELECT name FROM Movies WHERE release_year = 1940;

Mutta mitä kävisi, jos jakaisimmekin elokuvat moneen tauluun? Esimerkiksi voisimme jakaa elokuvat tauluihin vuosien mukaan. Tällöin taulussa Movies1940 olisi vuoden 1940 elokuvat, ja voisimme hakea ne näin:

SELECT name FROM Movies1940;

Tällainen ratkaisu toimii niin kauan, kuin haluamme hakea vain tietyn vuoden elokuvia. Kuitenkin tietokanta muuttuu vaikeakäyttöiseksi heti, jos haluamme tehdä jotain muita hakuja. Esimerkiksi jos haluamme hakea kaikki elokuvat vuosilta 1940–1950, tarvitsemme useita kyselyjä:

SELECT name FROM Movies1940;
SELECT name FROM Movies1941;
SELECT name FROM Movies1942;
...
SELECT name FROM Movies1950;

Kuitenkin kun elokuvat ovat samassa taulussa, niin selviämme yhdellä kyselyllä:

SELECT name FROM Movies WHERE release_year BETWEEN 1940 AND 1950;

Kun elokuvat ovat yhdessä taulussa, pystymme käsittelemään niitä monipuolisesti yksittäisillä SQL-komennoilla, mikä ei olisi mahdollista, jos tauluja olisi useita.

Viittausten toteutus

Yksi moneen -suhde

Tarkastellaan tietokantaa, johon tallennetaan kursseja ja opettajia. Taulujen välillä on yksi moneen -suhde: jokaisella kurssilla on yksi opettaja, kun taas yhdellä opettajalla voi olla monta kurssia. Voimme luoda tietokannan taulut näin:

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

Taulussa Courses sarake teacher_id viittaa tauluun Teachers, eli siinä on jonkun opettajan id-numero. Viittaus ilmaistaan REFERENCES-määreellä, joka kertoo, että sarakkeessa oleva kokonaisluku viittaa tauluun Teachers.

Voisimme laittaa tauluihin tietoa vaikkapa seuraavasti:

INSERT INTO Teachers (name) VALUES ('Kaila');
INSERT INTO Teachers (name) VALUES ('Kivinen');
INSERT INTO Teachers (name) VALUES ('Laaksonen');

INSERT INTO Courses (name, teacher_id) VALUES ('Tietoverkot', 1);
INSERT INTO Courses (name, teacher_id) VALUES ('Graduseminaari', 1);
INSERT INTO Courses (name, teacher_id) VALUES ('PHP-ohjelmointi', 3);
INSERT INTO Courses (name, teacher_id) VALUES ('Neuroverkot', 2);

Monta moneen -suhde

Tarkastellaan sitten tilannetta, jossa useampi opettaja voi järjestää kurssin yhteisesti. Tällöin kyseessä on monta moneen -suhde, koska kurssilla voi olla monta opettajaa ja opettajalla voi olla monta kurssia.

Nyt taulun Teachers rivi voi liittyä useaan taulun Courses riviin, ja vastaavasti taulun Courses rivi voi liittyä useaan taulun Teachers riviin. Koska tietokannan rivi ei voi sisältää listaa viittauksista, emme voi lisätä viittauksia suoraan kumpaankaan tauluun, vaan meidän täytyy luoda uusi taulu viittauksille:

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE CourseTeachers (
  course_id INTEGER REFERENCES Courses,
  teacher_id INTEGER REFERENCES Teachers
);

Muutoksena aiempaan on, että taulussa Courses ei ole enää viittausta tauluun Teachers, mutta sen sijaan tietokannassa on uusi taulu CourseTeachers, joka viittaa kumpaankin tauluun. Jokainen rivi tässä taulussa kuvaa yhden suhteen muotoa “kurssilla id opettaa opettaja id”.

Esimerkiksi voisimme ilmaista näin, että kurssilla on kaksi opettajaa:

INSERT INTO Teachers (name) VALUES ('Laaksonen');
INSERT INTO Teachers (name) VALUES ('Luukkainen');

INSERT INTO Courses (name) VALUES ('PHP-ohjelmointi');
INSERT INTO Courses (name) VALUES ('Neuroverkot');

INSERT INTO CourseTeachers VALUES (1, 1);
INSERT INTO CourseTeachers VALUES (1, 2);
INSERT INTO CourseTeachers VALUES (2, 1);

Tämä tarkoittaa, että Laaksonen ja Luukkainen opettavat kurssilla PHP-ohjelmointi. Lisäksi Laaksonen opettaa kurssilla Neuroverkot.

Huomaa, että voisimme käyttää tätä ratkaisua myös aiemmassa tilanteessa, jossa kurssilla on aina tasan yksi opettaja. Tässä tapauksessa tietokannassa olisi kuitenkin tavallaan turha taulu.

Tiedon atomisuus

Periaate: Tietokannan taulun jokaisessa sarakkeessa on yksittäinen eli atominen tieto, kuten yksi luku tai yksi merkkijono. Sarakkeessa ei saa olla listaa tiedoista.

Tämä periaate helpottaa tietokannan käsittelyä SQL-komentojen avulla: kun jokainen tieto on omassa sarakkeessaan, tietoon pystyy viittaamaan kätevästi.

Tietokantaan voidaan tallentaa lista luomalla taulu, jonka jokainen rivi on listan yksittäinen alkio, kuten äskeinen taulu CourseTeachers. Mutta miksi emme voisi vain tallentaa listaa yhteen sarakkeeseen? Seuraava esimerkki selventää asiaa.

Esimerkki

Vaihe 1

Haluamme tallentaa tietokantaan opiskelijoiden tenttituloksia. Tentissä on neljä tehtävää, joista voi saada 0–6 pistettä. Voisimme koettaa tallentaa pisteet näin:

student_id  points 
----------  -------
1           6,5,1,4
2           3,6,6,6
3           6,4,0,6

Ideana on, että sarakkeessa points on merkkijono, jossa on lista pisteistä pilkuilla erotettuina. Tämä ratkaisu kuitenkin rikkoo periaatetta, että jokaisessa sarakkeessa on yksittäinen tieto. Mitä vikaa ratkaisussa on?

Ratkaisun ongelmana on, että meidän on vaivalloista koettaa päästä pisteisiin käsiksi SQL-komennoissa, koska pisteet ovat merkkijonon sisällä. Esimerkiksi jos haluamme laskea jokaisen opiskelijan yhteispisteet, tarvitsemme seuraavan tapaisen kyselyn:

SELECT student_id,
       SUBSTR(points, 1, 1) + SUBSTR(points, 3, 1) +
       SUBSTR(points, 5, 1) + SUBSTR(points, 7, 1) AS total_points
FROM Results;

Tässä funktio SUBSTR erottaa merkkijonosta tietyssä kohdassa olevan osajonon. Kysely on kuitenkin hankala ja lisäksi toimii vain, kun pisteitä on tasan neljä ja ne ovat yksinumeroisia. Tarvitsemme paremman tavan tallentaa pisteet.

Vaihe 2

Seuraavassa taulussa pisteille on neljä saraketta, jolloin voimme käsitellä niitä yksitellen:

student_id  points1  points2  points3  points4
----------  -------  -------  -------  -------
1           6        5        1        4
2           3        6        6        6
3           6        4        0        6

Tämän ansiosta saamme jo toteutettua kyselyn mukavammin:

SELECT student_id,
       points1 + points2 + points3 + points4 AS total_points
FROM Results;

Tämä ratkaisu on selkeästi parempaan suuntaan, mutta siinä on edelleen ongelmia. Vaikka pisteet ovat eri sarakkeissa, oletuksena on edelleen, että tehtäviä on tasan neljä. Jos tehtävien määrä muuttuu, joudumme muuttamaan taulun rakennetta ja kaikkia pisteisiin liittyviä SQL-komentoja, mikä ei ole hyvä tilanne.

Vaihe 3

Kun haluamme tallentaa listan tietokantaan, hyvä ratkaisu on tallentaa jokainen listan alkio omalle rivilleen. Tämän esimerkin tapauksessa voimme luoda taulun, jonka jokainen rivi ilmaisee tietyn opiskelijan pisteet tietyssä tehtävässä:

student_id  task_id  points
----------  -------  ------
         1        6     
         2        5     
         3        1     
         4        4     
         1        3     
         2        6     
         3        6     
         4        6     
         1        6     
         2        4     
         3        0     
         4        6     

Nyt voimme hakea kunkin opiskelijan yhteispisteet näin:

SELECT student_id, SUM(points) AS total_points
FROM Results
GROUP BY student_id;

Tämä on yleiskäyttöinen kysely eli se toimii yhtä hyvin riippumatta tehtävien määrästä. Pystymme hyödyntämään summan laskemisessa funktiota SUM sen sijaan, että meidän tulisi luetella kaikki tehtävät itse.

Huomaa, että muutoksen seurauksena taulun rivien määrä kasvoi selvästi. Tätä ei kannata kuitenkaan hätkähtää: tietokantajärjestelmät on toteutettu niin, että ne toimivat hyvin, vaikka taulussa olisi paljon rivejä.

Mikä on atomista tietoa?

Atomisen tiedon käsite ei ole hyvin määritelty. Selkeästi lista ei ole atominen tieto, mutta onko sitten vaikka merkkijonokaan, jossa on useita sanoja?

Tarkastellaan esimerkkinä tilannetta, jossa taulun sarakkeessa on käyttäjän nimi. Onko tämä huonoa suunnittelua, koska samassa sarakkeessa on etu- ja sukunimi?

id  name          
--  --------------
1   Anna Virtanen 
2   Maija Korhonen
3   Pasi Lahtinen 

Voisimme myös tallentaa etu- ja sukunimen erikseen näin:

id  first_name  last_name
--  ----------  ---------
1   Anna        Virtanen 
2   Maija       Korhonen 
3   Pasi        Lahtinen 

Riippuu tilanteesta, kumpi taulu on parempi. Jos järjestelmässä on erityisesti tarvetta etsiä tietoa etu- tai sukunimen perusteella (esimerkiksi etsiä kaikki käyttäjät, joiden etunimi on Anna), jälkimmäinen taulu on parempi. Kuitenkaan usein ei ole näin eikä ole mitään pahaa tallentaa samaan sarakkeeseen etu- ja sukunimi.

Vastaavasti jos tietokantaan tallennetaan käyttäjän lähettämä viesti, siinä voi olla monia sanoja eli tavallaan viesti on lista sanoja, mutta on silti hyvä ratkaisu tallentaa koko viesti yhteen sarakkeeseen, koska viestiä käsitellään tietokannassa yhtenä kokonaisuutena. Olisi hyvin huono ratkaisu jakaa “atomisesti” viestin sanat omiin sarakkeisiin.

Kannattaakin ajatella asiaa niin, että jos jotain tietoa on tarvetta käsitellä erillisenä SQL-komennoissa, niin se on atominen tieto, jonka tulee olla omassa sarakkeessa. Jos taas tietoon ei viitata SQL-komennoissa, se voi olla sarakkeessa osana laajempaa kokonaisuutta.

Toisteinen tieto

Periaate: Jokainen tieto on tasan yhdessä paikassa tietokannassa. Tietokannassa ei ole tietoa, jonka voi laskea tai päätellä tietokannan muun sisällön perusteella.

Tätä periaatetta seuraamalla tietokannan sisällön päivittäminen on helppoa, koska päivitys riittää tehdä yhteen paikkaan eikä se vaikuta tietokannan muihin osiin.

Esimerkki 1

Tallennamme järjestelmään käyttäjien lähettämiä viestejä seuraavasti tauluun Messages:

id  user        message       
--  ----------  --------------
1   Anna123     Missä olet?   
2   Joulupukki  Bussissa vielä
3   Anna123     Meneekö kauan?
4   Joulupukki  5 min         

Tämä on muuten toimiva ratkaisu, mutta tietokannan sisältöä on hankalaa päivittää, jos käyttäjä päättää vaihtaa nimeään. Esimerkiksi jos Anna123 haluaa muuttaa nimeään, muutos täytyy tehdä jokaiseen viestiin, jonka hän on lähettänyt.

Parempi ratkaisu on toteuttaa tietokanta niin, että käyttäjän nimi on vain yhdessä paikassa. Luonteva paikka tälle on taulu Users, joka sisältää käyttäjät:

id  name      
--  ----------
1   Anna123   
2   Joulupukki

Muissa tauluissa on vain viitteenä käyttäjän id-numero, joka on muuttumaton tieto. Esimerkiksi taulu Messages näyttää nyt tältä:

id  user_id  message       
--  -------  --------------
1   1        Missä olet?   
2   2        Bussissa vielä
3   1        Meneekö kauan?
4   2        5 min         

Tämän jälkeen käyttäjän nimen muuttaminen on helppoa, koska muutos riittää tehdä taulun Users yhteen riviin ja muutos päivittyy heti kaikkialle, koska muissa tauluissa viitataan edelleen oikeaan riviin.

Tämä monimutkaistaa kyselyjä, koska tietoa täytyy hakea useista tauluista, mutta ratkaisu on kuitenkin kokonaisuuden kannalta hyvä.

Vieläkin toisteisuutta?

Äskeisestä muutoksesta huolimatta tietokannassa saattaa esiintyä edelleen toisteisuutta. Esimerkiksi seuraavassa tilanteessa käyttäjät lähettävät samanlaisen viestin “Hei!”. Pitäisikö tietokannan rakennetta parantaa?

id  user_id  message
--  -------  -------
1   1        Hei!   
2   2        Hei!   

Tässä tapauksessa ei olisi hyvä idea toteuttaa tietokantaa niin, että jos kaksi käyttäjää lähettää saman sisältöisen viestin, viestin sisältö tallennetaan vain yhteen paikkaan.

Vaikka viesteissä on sama sisältö, ne ovat erillisiä viestejä, joiden ei ole tarkoitus viitata samaan asiaan. Jos käyttäjä 1 muuttaa viestin sisältöä, muutoksen ei tule heijastua käyttäjän 2 viestiin, vaikka siinä sattuu olemaan tällä hetkellä sama sisältö.

Esimerkki 2

Tallennamme tietokantaan tietoa opiskelijoiden suorituksista. Tietokannasta voidaan kysyä, montako opintopistettä opiskelija on suorittanut.

Seuraavassa tietokannassa jokaisen opiskelijan yhteyteen on tallennettu tieto, montako opintopistettä hän on suorittanut. Taulun Students sisältönä on:

id  name    total_credits
--  ------  -------------
1   Maija   20           
2   Uolevi  10           

Taulussa Completions puolestaan on seuraavat rivit:

id  student_id  course_id  credits
--  ----------  ---------  -------
 1           1          5      
 1           2          5      
 1           4          10     
 2           1          5      
 2           3          5      

Voimme hakea helposti opiskelijan opintopisteiden yhteismäärän näin:

SELECT total_credits FROM Students WHERE name = 'Maija';

Kuitenkin tietokannassa on toisteista tietoa: taulun Students sarakkeen total_credits sisältö voidaan laskea taulun Completions avulla. Esimerkiksi Maijan opintopisteiden määrä 20 taulussa Students voidaan myös laskea summana 5 + 5 + 10 taulusta Completions.

Ongelmana on, että suoritusta lisätessä täytyy sekä lisätä uusi rivi tauluun Completions että päivittää opintopisteiden yhteismäärä tauluun Students. Jos päivitys unohtuu tehdä tai epäonnistuu, tietokantaan tulee ristiriitaista tietoa.

Pääsemme eroon toisteisesta tiedosta poistamalla sarakkeen total_credits taulusta Students:

id  name  
--  ------
1   Maija 
2   Uolevi

Tämän muutoksen seurauksena on vaikeampaa selvittää opiskelijan opintopisteiden yhteismäärä, koska tieto täytyy laskea suorituksista lähtien:

SELECT SUM(Completions.credits) AS total_credits
FROM Completions, Students
WHERE Completions.student_id = Students.id AND Students.name = 'Maija';

Tämä on kuitenkin kokonaisuutena hyvä muutos, koska nyt voimme huoletta muuttaa suorituksia taulussa Completions ja luottaa siihen, että saamme aina laskettua ajantasaisen tiedon opiskelijan opintopisteistä.

Muutokset vs. kyselyt

Vaikka ihanteena on, että tietokannassa ei ole toisteista tietoa, joskus kuitenkin toisteista tietoa tarvitaan hakujen tehostamiseksi. Toisteinen tieto vaikeuttaa tietokannan muuttamista mutta helpottaa kyselyjen tekemistä.

Usein esiintyvä ilmiö tietojenkäsittelytieteessä on, että joudumme tasapainoilemaan sen kanssa, haluammeko muuttaa vai hakea tehokkaasti tietoa ja paljonko tilaa voimme käyttää. Tämä tulee tietokantojen lisäksi vastaan esimerkiksi algoritmien suunnittelussa.

Jos tietokannassa ei ole toisteista tietoa, muutokset ovat helppoja, koska jokainen tieto on vain yhdessä paikassa eli riittää muuttaa vain yhden taulun yhtä riviä. Toinen hyvä puoli on, että toisteinen tieto ei vie tilaa tietokannassa. Toisaalta kyselyt voivat olla monimutkaisia ja hitaita, koska halutut tiedot pitää kerätä kasaan eri puolilta tietokantaa.

Toisteista tietoa lisäämällä pystymme nopeuttamaan kyselyjä mutta toisaalta muutokset hankaloituvat, koska muutettu tieto pitää päivittää useaan paikkaan. Samaan aikaan myös tietokannan tilankäyttö kasvaa toisteisen tiedon takia.

Ei ole mitään yleistä sääntöä, paljonko toisteista tietoa kannattaa lisätä, vaan tämä riippuu tietokannan sisällöstä ja halutuista kyselyistä. Yksi hyvä tapa on aloittaa tilanteesta, jossa toisteista tietoa ei ole, ja lisätä sitten toisteista tietoa tarvittaessa, jos kyselyt eivät muuten ole riittävän tehokkaita.

Suunnitteluesimerkki

Tarkastellaan lopuksi laajempaa esimerkkiä, jossa tavoitteemme on suunnitella tietokanta yliopiston luentosalien varaamista varten. Tietokannan tulee mahdollistaa seuraavat toiminnot:

Käyttäjä voi kirjautua järjestelmään antamalla tunnuksen ja salasanan. Järjestelmässä on peruskäyttäjiä ja ylläpitäjiä.
Käyttäjä näkee varattavan salin tunnuksen, rakennuksen, istumapaikkojen määrän ja hintaluokan. Esimerkki: A111, Exactum, 280, A10
Varauksen tuntihinta riippuu hintaluokasta. Käyttäjä näkee kokonaishinnan varauksen yhteydessä.
Rakennuksilla on tietyt aukioloajat. Rakennuksen salit ovat varattavissa rakennuksen ollessa avoinna.
Käyttäjä pystyy etsimään sopivaa salia ilmoittamalla rakennuksen ja istumapaikkojen minimimäärän.
Käyttäjä pystyy tekemään salista varauspyynnön, jossa on tietty aikaväli tiettynä päivänä sekä kommentti. Ylläpitäjä vahvistaa varauksen varauspyynnön perusteella.

Suunnittelun vaiheet

Tietokannan suunnittelu etenee yleensä pikkuhiljaa niin, että tietokantaan lisätään uusia tauluja ja sarakkeita aina, kun uusissa toiminnoissa on tarvetta niille.

Seuraavaksi näemme, miten esimerkkitietokanta rakentuu vaihe vaiheelta vaadittujen toimintojen perusteella.

Kirjautuminen järjestelmään

Käyttäjä voi kirjautua järjestelmään antamalla tunnuksen ja salasanan. Järjestelmässä on peruskäyttäjiä ja ylläpitäjiä.

Tämä on tavallinen toiminto, josta on hyvä aloittaa tietokannan suunnittelu. Tarvitsemme taulun, jossa on käyttäjän tunnus ja salasana:

CREATE TABLE Users (
  id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT
);

Koska järjestelmässä on kahdenlaisia käyttäjiä (peruskäyttäjät ja ylläpitäjät), tämä tieto tulee tallentaa myös tietokantaan. Kaksi mahdollista ratkaisua ovat:

Ratkaisu 1: Tauluun Users lisätään sarake, joka ilmaisee käyttäjän roolin.
Ratkaisu 2: Taulussa Users on peruskäyttäjät ja luodaan toinen taulu Admins, jossa on ylläpitäjät.

Kokemus on osoittanut, että ratkaisu 1 on yleensä parempi tietokannan käyttämisen kannalta. Lisätään tauluun Users uusi sarake user_role, joka ilmaisee käyttäjän roolin (esim. 1 = peruskäyttäjä, 2 = ylläpitäjä):

CREATE TABLE Users (
  id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT,
  user_role INTEGER
);

Varattavan salin tiedot

Käyttäjä näkee varattavan salin tunnuksen, rakennuksen, istumapaikkojen määrän ja hintaluokan. Esimerkki: A111, Exactum, 280, A10

Hyvä ratkaisu on luoda erilliset taulut Buildings ja Categories, joihin tallennetaan tiedot rakennuksista ja hintaluokista:

CREATE TABLE Buildings (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
);

Tämän jälkeen voimme luoda vielä taulun Rooms, joka sisältää tiedot saleista. Tämä taulu viittaa tauluihin Buildings ja Categories.

CREATE TABLE Rooms (
  id INTEGER PRIMARY KEY,
  name TEXT,
  building_id INTEGER REFERENCES Buildings,
  seat_count INTEGER,
  category_id INTEGER REFERENCES Categories
);

Nyt voisimme lisätä järjestelmään seuraavasti tiedot salista A111:

INSERT INTO Buildings (name) VALUES ('Exactum');
INSERT INTO Categories (name) VALUES ('A10');
INSERT INTO Rooms (name, building_id, seat_count, category_id)
       VALUES ('A111', 1, 280, 1);

Varauksen kokonaishinta

Varauksen tuntihinta riippuu hintaluokasta. Käyttäjä näkee kokonaishinnan varauksen yhteydessä.

Tätä toimintoa varten voimme lisätä tauluun Categories sarakkeen price_per_hour, joka ilmaisee hintaluokan tuntihinnan:

CREATE TABLE Categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price_per_hour INTEGER
);

Tämän jälkeen voisimme laskea seuraavasti, paljonko maksaa neljän tunnin varaus saliin A111:

SELECT Categories.price_per_hour * 4 AS total_price
FROM Rooms, Categories
WHERE Rooms.category_id = Categories.id AND Rooms.name = 'A111';

Rakennusten aukioloajat

Rakennuksilla on tietyt aukioloajat. Rakennuksen salit ovat varattavissa rakennuksen ollessa avoinna.

Rakennukset ovat yleensä avoinna arkipäivinä mutta eivät viikonloppuisin ja pyhäpäivinä. Lisäksi aukioloajat voivat muuttua esimerkiksi kesän ajaksi.

Käytännössä voisi olla vaikeaa määritellä kaikissa tapauksissa toimivaa säännöstöä, joka kertoisi, milloin tietty rakennus on auki. Sen sijaan hyvä ratkaisu on luoda taulu OpeningHours, joka sisältää rivin jokaisen rakennuksen jokaiselle aukiolopäivälle:

CREATE TABLE OpeningHours (
  id INTEGER PRIMARY KEY,
  building_id INTEGER REFERENCES Buildings,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

Esimerkiksi seuraavat komennot lisäävät tiedot, miten Exactum-rakennus on auki syyskuun ensimmäisellä viikolla vuonna 2025:

INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2025-09-01 08:00:00', '2025-09-01 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2025-09-02 08:00:00', '2025-09-02 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2025-09-03 08:00:00', '2025-09-03 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2025-09-04 08:00:00', '2025-09-04 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2025-09-05 08:00:00', '2025-09-05 18:00:00');

Sopivan salin etsiminen

Käyttäjä pystyy etsimään sopivaa salia ilmoittamalla rakennuksen ja istumapaikkojen minimimäärän.

Tämä toiminto onnistuu olemassa olevien taulujen avulla. Esimerkiksi seuraava komento etsii Exactumista salia, johon mahtuu ainakin 100 henkilöä:

SELECT Rooms.name
FROM Rooms, Buildings
WHERE Rooms.building_id = Buildings.id AND
      Rooms.seat_count >= 100 AND Buildings.name = 'Exactum';

Varauspyyntö ja vahvistus

Käyttäjä pystyy tekemään salista varauspyynnön, jossa on tietty aikaväli tiettynä päivänä sekä kommentti. Ylläpitäjä vahvistaa varauksen varauspyynnön perusteella.

Voimme tehdä seuraavan taulun Requests, johon tallennetaan varauspyynnöt. Jokaiseen pyyntöön liittyy käyttäjä, sali, varauksen alku- ja loppuaika sekä kommentti.

CREATE TABLE Requests (
  id INTEGER PRIMARY KEY
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  comment TEXT
);

Mitä tapahtuu sitten, kun ylläpitäjä hyväksyy varauksen? Yksi ratkaisu olisi tehdä toinen taulu Reservations, joka sisältää hyväksytyt varaukset:

CREATE TABLE Reservations (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

Vaikka varauspyyntö ja vahvistettu varaus ovat sinänsä eri asioita, tässä tulisi kuitenkin ongelmaksi, että tauluissa Requests ja Reservations on hyvin samanlaista tietoa. Parempi ratkaisu voisi olla yhdistää nämä taulut esimerkiksi seuraavasti:

CREATE TABLE Reservations (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  status INTEGER
);

Tässä sarake status ilmaisee varauksen tilan. Voisimme esimerkiksi päättää, että tila 1 tarkoittaa varauspyyntöä ja tila 2 tarkoittaa vahvistettua varausta.

Tietokannan kuvaaminen

Tietokannan rakenteen kuvaamiseen on kaksi tavallista tapaa: graafinen tietokantakaavio, joka esittää taulujen suhteet, sekä SQL-skeema, jossa on taulujen luontikomennot.

Tietokantakaavio

Tietokantakaavio on tietokannan graafinen esitys, jossa jokainen tietokannan taulu on laatikko, joka sisältää taulun nimen ja sarakkeet listana. Rivien viittaukset toisiinsa esitetään laatikoiden välisinä yhteyksinä.

Tietokantakaavion piirtämiseen on monia vähän erilaisia tapoja. Seuraava kaavio on luotu netissä olevalla työkalulla dbdiagram.io:

SQL-skeema

SQL-skeema sisältää CREATE TABLE -komennot, joiden avulla tietokanta voidaan muodostaa. Seuraava SQL-skeema vastaa tietokantaamme:

CREATE TABLE Users (
  id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT,
  user_role INTEGER
);

CREATE TABLE Buildings (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price_per_hour INTEGER
);

CREATE TABLE Rooms (
  id INTEGER PRIMARY KEY,
  name TEXT,
  building_id INTEGER REFERENCES Buildings,
  seat_count INTEGER,
  category_id INTEGER REFERENCES Categories
);

CREATE TABLE OpeningHours (
  id INTEGER PRIMARY KEY,
  building_id INTEGER REFERENCES Buildings,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

CREATE TABLE Reservations (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  status INTEGER
);

7. Tietokannan ominaisuudet

Tiedon eheys

Tiedon eheys tarkoittaa, että tietokannassa oleva tieto on paikkansa pitävää ja ristiriidatonta. Päävastuu tiedon laadusta on tietokantaa muuttavalla käyttäjällä tai sovelluksella, mutta myös tietokannan suunnittelija voi vaikuttaa asiaan lisäämällä tauluihin ehtoja, jotka valvovat tietokantaan syötettävää tietoa.

Sarakkeiden ehdot

Voimme lisätä taulun luonnin yhteydessä sarakkeisiin ehtoja, joita tietokantajärjestelmä valvoo tietoa lisättäessä ja muutettaessa. Näillä ehdoilla voi rajoittaa tietokantaan tulevaa tietoa. Tyypillisiä ehtoja ovat seuraavat:

UNIQUE

Ehto UNIQUE tarkoittaa, että sarakkeessa tulee olla eri arvo joka rivillä. Esimerkiksi seuraavassa taulussa vaatimuksena on, että joka tuotteella on eri nimi:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE,
  price INTEGER
);

Ehto UNIQUE voi kohdistua myös useampaan sarakkeeseen, jolloin se merkitään erikseen sarakkeiden jälkeen:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER,
  UNIQUE(name, price)
);

Tämä tarkoittaa, että taulussa ei voi olla kahta riviä, joilla on sama nimi ja sama hinta.

NOT NULL ja DEFAULT

Ehto NOT NULL tarkoittaa, että sarakkeessa ei saa olla arvoa NULL. Esimerkiksi seuraavassa taulussa tuotteen hinta ei saa olla tyhjä:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER NOT NULL
);

Määre DEFAULT antaa sarakkeelle tietyn oletusarvon, jos sen arvo puuttuu rivin lisäämisessä. Esimerkiksi voimme määrittää hinnalle oletusarvon 0 näin:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER DEFAULT 0
);

CHECK

Yleisempi tapa luoda ehto on käyttää avainsanaa CHECK, jonka jälkeen voi kirjoittaa minkä tahansa ehtolausekkeen. Esimerkiksi seuraavassa taulussa ehto price >= 0 tarkoittaa, että hinta ei saa olla negatiivinen:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER,
  CHECK (price >= 0)
);

Ehtojen valvonta

Ehtojen hyötynä on, että tietokantajärjestelmä valvoo ehtoja ja kieltäytyy tekemästä niitä rikkovia lisäyksiä tai muutoksia. Seuraavassa on esimerkki tästä SQLitessä:

sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT,
   ...>                        price INTEGER,
   ...>                        CHECK (price >= 0));
sqlite> INSERT INTO Products(name, price) VALUES ('retiisi', 4);
sqlite> INSERT INTO Products(name, price) VALUES ('selleri', 7);
sqlite> INSERT INTO Products(name, price) VALUES ('nauris', –2);
Error: CHECK constraint failed: Products
sqlite> SELECT * FROM Products;
1|retiisi|4
2|selleri|7
sqlite> UPDATE Products SET price = –2 WHERE id = 2;
Error: CHECK constraint failed: Products

Kun koetamme lisätä tauluun Products rivin, jossa hinta on negatiivinen, tämä rikkoo ehdon price >= 0 eikä SQLite salli rivin lisäämistä vaan antaa virheen CHECK constraint failed: Products. Samalla tavalla käy, jos koetamme muuttaa olemassa olevan rivin sarakkeen hinnan negatiiviseksi jälkeenpäin.

Ehdot ohjelmoinnissa

Seuraava esimerkki näyttää, miten taulussa olevaa ehtoa voidaan hyödyntää ohjelmoinnissa. Haluamme, että tietokannan jokaisella tuotteella on eri nimi, minkä vuoksi sarakkeessa name on ehtona UNIQUE:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE,
  price INTEGER
);

Nyt tuotteen lisäämisen tietokantaan voi toteuttaa näin:

try:
    db.execute("""
               INSERT INTO Products (name, price) VALUES (?, ?)
               """, [name, price])
    print("Tuote lisätty tauluun")
except sqlite3.IntegrityError:
    print("Lisäys ei onnistunut, tuote valmiiksi taulussa")

Tässä tapauksessa komento INSERT epäonnistuu, jos taulussa on valmiina samanniminen tuote. Python-koodissa tilanteen voi tunnistaa siitä, että lisäysyritys aiheuttaa virheen sqlite3.IntegrityError. Niinpä koodi voi yrittää lisätä tuotetta tutkimatta, onko tuote jo valmiiksi taulussa, ja tästä syntyvä mahdollinen virhe ilmaisee, että tuote on lisätty aiemmin.

Tämä on selkeästi parempi toteutus kuin tutkia koodissa ennen lisäämistä SELECT-kyselyllä, onko tuotetta jo taulussa, koska UNIQUE-ehdon avulla tietokanta pitää luotettavasti huolen asiasta ja koodiakin tarvitaan vähemmän.

Jos taulussa ei olisi UNIQUE-ehtoa ja sovellus suorittaisi komennot SELECT ja INSERT, olisi mahdollista, että toinen tietokannan käyttäjä ehtisi lisätä välissä saman tuotteen tauluun, jolloin taulussa olisikin kaksi tuotetta samalla nimellä. Kuitenkaan UNIQUE-ehdon kanssa näin ei voi tapahtua mitenkään.

Viittausten ehdot

Tauluissa oleviin viitauksiin liittyvät ehdot pitävät huolen siitä, että viittaukset viittaavat todellisiin riveihin. Tarkastellaan esimerkkinä seuraavia tauluja:

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER
);

Tässä tarkoituksena on, että taulun Courses sarake teacher_id viittaa taulun Teachers sarakkeeseen id. Kuitenkin tietokannan käyttäjä voi antaa sarakkeen teacher_id arvoksi mitä tahansa (esim. luvun 123), jolloin tietokannan sisältö muuttuu epämääräiseksi.

Voimme parantaa tilannetta kertomalla taulun Courses luonnissa REFERENCES-merkinnällä, että sarake teacher_id viittaa tauluun Teachers:

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

Tämän jälkeen voimme luottaa siihen, että taulussa Courses sarakkeen teacher_id arvot viittaavat todellisiin riveihin taulussa Teachers.

Huomaa, että historiallisista syistä SQLite ei oletuksena valvo viittauksia, vaan meidän tulee ensin suorittaa seuraava komento:

sqlite> PRAGMA foreign_keys = ON;

Tämä on SQLiten erikoisuus, ja muissa tietokannoissa viittauksia valvotaan aina.

Tässä on esimerkki viittauksien valvomisesta:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE Teachers (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE Courses (id INTEGER PRIMARY KEY, name TEXT,
   ...>                       teacher_id INTEGER REFERENCES Teachers);
sqlite> INSERT INTO Teachers (name) VALUES ('Kaila');
sqlite> INSERT INTO Teachers (name) VALUES ('Kivinen');
sqlite> SELECT * FROM Teachers;
1|Kaila
2|Kivinen
sqlite> INSERT INTO Courses (name, teacher_id)
   ...>             VALUES ('Laskennan mallit', 2);
sqlite> INSERT INTO Courses (name, teacher_id)
   ...>             VALUES ('PHP-ohjelmointi', 123);
Error: FOREIGN KEY constraint failed   

Taulussa Teachers on kaksi opettajaa, joiden id-numerot ovat 1 ja 2. Niinpä kun koetamme lisätä tauluun Courses rivin, jossa teacher_id on 123, SQLite ei salli tätä vaan saamme virheilmoituksen FOREIGN KEY constraint failed.

Viittaukset ja poistot

Viittausten ehtoihin liittyy tavallisia sarakkeiden ehtoja mutkikkaampia tilanteita, koska viittaukset ovat kahden taulun välisiä. Erityisesti mitä tapahtuu, jos taulusta yritetään poistaa rivi, johon viitataan toisessa taulussa?

Yleensä oletuksena tietokannoissa riviä ei voi poistaa, jos siihen on viittaus muualta. Esimerkiksi jos koetamme äskeisen esimerkin päätteeksi poistaa taulusta Teachers rivin 2, tämä ei onnistu, koska siihen viitataan taulussa Courses:

sqlite> DELETE FROM Teachers WHERE id = 2;
Error: FOREIGN KEY constraint failed

Halutessamme voimme kuitenkin määrittää taulun luonnissa tarkemmin, mitä tällaisessa tilanteessa tapahtuu. Yksi vaihtoehto on ON DELETE CASCADE, mikä tarkoittaa, että rivin poistuessa myös siihen viittaavat rivit poistetaan:

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers ON DELETE CASCADE
);

Nyt jos tietokannasta poistetaan opettaja, niin samalla poistetaan automaattisesti kaikki kurssit, joita hän opettaa. Tämä voi kuitenkin olla kyseenalainen vaihtoehto, koska tämän seurauksena tietokannan tauluista voi kadota yllättäen tietoa.

Mahdollisia vaihtoehtoja ON DELETE -osassa ovat:

NO ACTION: “älä tee mitään” (oletus)
RESTRICT: estä poistaminen
CASCADE: poista myös viittaavat rivit
SET NULL: muuta viittaukset arvoksi NULL
SET DEFAULT: muuta viittaukset oletusarvoksi

Hämmentävä seikka on, että myös oletusvaihtoehto NO ACTION estää rivin poistamisen, vaikka nimestä voisi päätellä muuta. Vaihtoehdot NO ACTION ja RESTRICT toimivat käytännössä lähes samalla tavalla, mutta tietokannasta riippuen niiden toiminnassa voi olla eroja joissain erikoistilanteissa.

Transaktiot

Transaktio on joukko peräkkäisiä SQL-komentoja, jotka tietokantajärjestelmä lupaa suorittaa yhtenä kokonaisuutena. Tietokannan käyttäjä voi luottaa siihen, että joko (1) kaikki komennot suoritetaan ja muutokset jäävät pysyvästi tietokantaan tai (2) transaktio keskeytyy eivätkä komennot aiheuta mitään muutoksia tietokantaan.

Transaktioiden yhteydessä esiintyy usein ihanteena kirjainyhdistelmä ACID, joka tulee seuraavista sanoista:

Atomicity: Transaktiossa olevat komennot suoritetaan yhtenä kokonaisuutena.
Consistency: Transaktio säilyttää tietokannan sisällön eheänä.
Isolation: Transaktiot suoritetaan eristyksessä toisistaan.
Durability: Loppuun viedyn transaktion tekemät muutokset jäävät pysyviksi.

Transaktion vaiheet

Itse asiassa transaktio on hyvin arkipäiväinen asia tietokannoissa, sillä oletuksena jokainen suoritettava SQL-komento on oma transaktionsa. Tarkastellaan esimerkiksi seuraavaa komentoa, joka kasvattaa jokaisen tuotteen hintaa yhdellä:

UPDATE Products SET price = price + 1;

Koska komento suoritetaan transaktiona, voimme luottaa siihen, että joko jokaisen tuotteen hinta todella kasvaa yhdellä tai sitten minkään tuotteen hinta ei muutu. Jälkimmäinen voi tapahtua esimerkiksi silloin, kun sähköt katkeavat kesken päivityksen. Silloinkaan ei siis voi käydä niin, että vain osa hinnoista muuttuu.

Usein kuitenkin sana transaktio viittaa erityisesti siihen, että suoritettavaan kokonaisuuteen kuuluu useampi SQL-komento. Tällöin annamme ensin komennon BEGIN, joka aloittaa transaktion, sitten kaikki transaktioon kuuluvat komennot tavalliseen tapaan ja lopuksi komennon COMMIT, joka päättää transaktion.

Klassinen esimerkki transaktiosta on tilanne, jossa pankissa siirretään rahaa tililtä toiselle. Esimerkiksi seuraava transaktio siirtää 100 euroa Maijan tililtä Uolevin tilille:

BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
COMMIT;

Transaktion ideana on, että mitään pysyvää muutosta ei tapahdu ennen komentoa COMMIT. Niinpä yllä olevassa esimerkissä ei ole mahdollista, että Maija menettäisi 100 euroa mutta Uolevi ei saisi mitään. Joko kummankin tilin saldo muuttuu ja rahat siirtyvät onnistuneesti tai molemmat saldot säilyvät entisellään.

Jos transaktio keskeytyy jostain syystä ennen komentoa COMMIT, kaikki transaktiossa tehdyt muutokset peruuntuvat. Yksi syy transaktion keskeytymiseen on jokin häiriö tietokoneen toiminnassa (kuten sähköjen katkeaminen), mutta voimme myös itse halutessamme keskeyttää transaktion antamalla komennon ROLLBACK.

Transaktioiden kokeilu

Hyvä tapa saada ymmärrystä transaktioista on kokeilla käytännössä, miten ne toimivat. Tässä on esimerkkinä yksi keskustelu SQLiten kanssa:

sqlite> CREATE TABLE Accounts (id INTEGER PRIMARY KEY,
   ...>                        customer TEXT, balance INTEGER);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Uolevi', 350);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Maija', 600);
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|500
sqlite> ROLLBACK;
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
sqlite> COMMIT;
sqlite> SELECT * FROM Accounts;
1|Uolevi|450
2|Maija|500

Alkutilanteessa Uolevin tilillä on 350 euroa ja Maijan tilillä on 600 euroa. Ensimmäisessä transaktiossa poistamme ensin Maijan tililtä 100 euroa, mutta sen jälkeen tulemme toisiin ajatuksiin ja keskeytämme transaktion. Niinpä transaktiossa tehty muutos peruuntuu ja tilien saldot ovat samat kuin alkutilanteessa. Toisessa transaktiossa viemme kuitenkin transaktion loppuun, minkä seurauksena Uolevin tilillä on 450 euroa ja Maijan tilillä on 500 euroa.

Huomaa, että transaktion sisällä muutokset kyllä näkyvät, vaikka niitä ei olisi tehty vielä pysyvästi tietokantaan. Esimerkiksi ensimmäisen transaktion SELECT-kysely antaa Maijan tilin saldoksi 500 euroa, koska edellinen UPDATE-komento muutti saldoa.

Transaktiot ohjelmoinnissa

Transaktiokomentoja (BEGIN, COMMIT, jne.) voi suorittaa ohjelmoinnissa samaan tapaan kuin muitakin SQL-komentoja. Esimerkiksi seuraava koodi lisää tauluun Products tuhat riviä for-silmukassa yhden transaktion sisällä:

db.execute("BEGIN")

for i in range(1000):
    db.execute("""
               INSERT INTO Products (name, price) VALUES (?, ?)
               """, ["product" + str(i), 1])

db.execute("COMMIT")

Koska koodi on transaktion sisällä, koodi joko lisää kaikki rivit tietokantaan tai ei yhtään riviä, jos transaktio epäonnistuu jostain syystä.

Tässä tapauksessa transaktion käyttäminen myös nopeuttaa koodia, koska jokaista riviä ei lisätä erillisen transaktion sisällä vaan lisäys tapahtuu kokonaisuutena. Tämä auttaa tietokantaa toteuttamaan rivien lisäämisen tehokkaammin.

Rinnakkaiset transaktiot

Lisämaustetta transaktioiden käsittelyyn tuo, että tietokannalla voi olla useita käyttäjiä, joilla on meneillään samanaikaisia transaktioita. Missä määrin eri käyttäjien transaktiot tulisi eristää toisistaan?

Tämä on kysymys, johon ei ole yhtä oikeaa vastausta, vaan vastaus riippuu käyttötilanteesta ja myös tietokannan ominaisuuksista. Tavallaan paras ratkaisu olisi eristää transaktiot täydellisesti toisistaan, mutta toisaalta tämä voi haitata tietokannan käyttämistä.

SQL-standardi määrittelee transaktioiden eristystasot seuraavasti:

Taso 1 (read uncommitted)

On sallittua, että transaktio pystyy näkemään toisen transaktion tekemän muutoksen, vaikka toista transaktiota ei ole viety loppuun.

Taso 2 (read committed)

Toisin kuin tasolla 1, transaktio saa nähdä toisen transaktion tekemän muutoksen vain, jos toinen transaktio on viety loppuun.

Taso 3 (repeatable read)

Tason 2 vaatimus ja lisäksi jos transaktion aikana luetaan saman rivin sisältö useita kertoja, joka kerralla saadaan sama sisältö.

Taso 4 (serializable)

Transaktiot ovat täysin eristettyjä ja komennot käyttäytyvät samoin kuin jos transaktiot olisi suoritettu peräkkäin yksi kerrallaan jossain järjestyksessä.

Esimerkki

Tarkastellaan tilannetta, jossa tuotteen 1 hinta on aluksi 8 ja kaksi käyttäjää (K1 ja K2) suorittaa samaan aikaan komentoja transaktioiden sisällä:

K1: BEGIN;

K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;

K1: SELECT price FROM Products WHERE id = 1;
K1: COMMIT;

Tasolla 1 käyttäjä 1 voi saada kyselyistä tulokset 5 ja 7, koska käyttäjän 2 tekemät muutokset voivat tulla näkyviin heti, vaikka käyttäjän 2 transaktioita ei ole viety loppuun.

Tasolla 2 käyttäjä 1 voi saada kyselyistä tulokset 8 ja 7, koska ensimmäisen kyselyn kohdalla toista transaktiota ei ole viety loppuun, kun taas toisen kyselyn kohdalla se on viety loppuun.

Tasoilla 3 ja 4 käyttäjä 1 saa kyselyistä tulokset 8 ja 8, koska tämä on tilanne ennen transaktion alkua eikä välissä loppuun viety transaktio saa muuttaa luettua rivin sisältöä.

Transaktiot käytännössä

Transaktioiden toteutustavat ja saatavilla olevat eristystasot riippuvat käytetystä tietokantajärjestelmästä. Esimerkiksi SQLitessä ainoa mahdollinen taso on 4, kun taas PostgreSQL toteuttaa tasot 2–4 ja oletuksena käytössä on taso 2.

Eristystaso 4 on tavallaan paras, koska silloin transaktioiden muutokset eivät voi näkyä mitenkään toisilleen. Miksi edes muut tasot ovat olemassa ja miksi esimerkiksi PostgreSQL:n oletustaso on 2?

Hyvän eristämisen hintana on, että se voi hidastaa tai estää transaktioiden suorittamista, koska transaktion vieminen loppuun voisi aiheuttaa ristiriitaisen tilanteen. Toisaalta monissa käytännön tilanteissa riittää mainiosti heikompikin eristys, kunhan tietokannan käyttäjä on siitä tietoinen.

Hyvää tietoa rinnakkaisten transaktioiden toiminnasta saa perehtymällä käytetyn tietokannan dokumentaatioon sekä testailemalla asioita itse käytännössä. Esimerkiksi voimme käynnistää itse kaksi SQLite-tulkkia, avata niillä saman tietokannan ja sen jälkeen kirjoittaa transaktioita sisältäviä komentoja ja tehdä havaintoja.

Seuraava keskustelu näyttää edellisen esimerkin tuloksen kahdessa rinnakkain käynnissä olevassa SQLite-tulkissa:

K1: BEGIN;
                                         
K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;
8

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;
Error: database is locked

K1: SELECT price FROM Products WHERE id=1;
8
K1: COMMIT;

Tästä näkee, että ensimmäinen transaktio tosiaan saa kummastakin kyselystä tuloksen 8. Toista transaktiota ei sen sijaan saada vietyä loppuun, vaan tulee virheviesti Error: database is locked, koska tietokanta on lukittuna samanaikaisen transaktion takia. Eristys toimii siis hyvin, mutta toista transaktiota pitäisi yrittää viedä loppuun uudestaan.

Vertailun vuoksi tässä on vastaava keskustelu PostgreSQL-tulkeissa (tasolla 2):

K1: BEGIN;

K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;
8

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;

K1: SELECT price FROM Products WHERE id = 1;
7
K1: COMMIT;

Nyt toisen transaktion muuttama arvo 7 ilmestyy ensimmäiseen transaktioon, mutta toisaalta molemmat transaktiot saadaan vietyä loppuun ongelmitta.

Miten transaktiot toimivat?

Transaktioiden toteuttaminen on kiehtova tekninen haaste tietokannoissa. Tavallaan transaktion tulee tehdä muutoksia tietokantaan, koska komennot voivat riippua edellisistä komennoista, mutta toisaalta mitään ei saa muuttaa pysyvästi ennen transaktion viemistä loppuun.

Yksi keskeinen ajatus tietokantojen taustalla on tallentaa muutoksia kahdella tavalla. Ensin kuvaus muutoksesta kirjataan lokitiedostoon (write-ahead log), jota voi ajatella luettelona suoritetuista komennoista. Vasta tämän jälkeen muutokset tehdään tietokannan varsinaisiin tietorakenteisiin. Nyt jos jälkimmäisessä vaiheessa sattuu jotain yllättävää, muutokset ovat jo tallessa lokitiedostossa ja ne voidaan suorittaa myöhemmin uudestaan.

Transaktioiden yhteydessä tietokantajärjestelmän täytyy myös pitää kirjaa siitä, mitkä muutokset ovat minkäkin meneillään olevan transaktion tekemiä. Käytännössä tauluihin voidaan tallentaa rivimuutoksia, jotka näkyvät vain tietyille transaktioille. Sitten jos transaktio pääsee loppuun asti, nämä muutokset liitetään taulun pysyväksi sisällöksi.

Kyselyjen suoritus

SQL-kielen taustalla oleva idea on, että tietokannan käyttäjän riittää kuvata, mitä tietoa hän haluaa hakea, ja tietokantajärjestelmä hoitaa loput. Niinpä tietokantajärjestelmän on tärkeää pystyä löytämään jokin tehokas tapa toteuttaa käyttäjän antama kysely ja toimittaa kyselyn tulokset käyttäjälle.

Kyselyn suunnitelma

Monet tietokantajärjestelmät kertovat pyydettäessä suunnitelmansa, miten annettu kysely aiotaan suorittaa. Tämän avulla voidaan tutkia tietokantajärjestelmän sisäistä toimintaa.

Tarkastellaan esimerkkinä kyselyä, joka hakee retiisin tiedot taulusta Products:

SELECT * FROM Products WHERE name = 'retiisi';

Kun laitamme SQLitessä kyselyn eteen sanan EXPLAIN, saamme seuraavan tapaisen selostuksen suunnitelmasta:

sqlite> EXPLAIN SELECT * FROM Products WHERE name = 'retiisi';
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
   Init           0     12    0                    00  Start at 12  
   OpenRead       0     2     0     3              00  root=2 iDb=0; Products
   Rewind         0     10    0                    00               
     Column         0     1     1                    00  r[1]=Products.name
     Ne             2     9     1     (BINARY)       52  if r[2]!=r[1] goto 9
     Rowid          0     3     0                    00  r[3]=rowid   
     Copy           1     4     0                    00  r[4]=r[1]    
     Column         0     2     5                    00  r[5]=Products.price
     ResultRow      3     3     0                    00  output=r[3..5]
   Next           0     3     0                    01               
  Close          0     0     0                    00               
  Halt           0     0     0                    00               
  Transaction    0     0     1     0              01  usesStmtJournal=0
  TableLock      0     2     0     Products       00  iDb=0 root=2 write=0
  String8        0     2     0     retiisi        00  r[2]='retiisi'
  Goto           0     1     0                    00 

SQLite muuttaa kyselyn tietokannan sisäiseksi ohjelmaksi, joka hakee tietoa tauluista. Tässä tapauksessa ohjelman suoritus alkaa riviltä 12, jossa alkaa transaktio, ja sitten rivillä 14 rekisteriin 2 sijoitetaan hakuehdossa oleva merkkijono “retiisi”. Tämän jälkeen suoritus siirtyy riville 1, jossa aloitetaan taulun Products käsittely, ja rivit 2–9 muodostavat silmukan, joka etsii hakuehtoa vastaavat rivit taulusta.

Voimme myös pyytää tiiviimmän suunnitelman laittamalla kyselyn eteen sanat EXPLAIN QUERY PLAN. Tällöin tulos voi olla seuraava:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM Products WHERE name = 'retiisi';
0|0|0|SCAN TABLE Products

Tässä SCAN TABLE Products tarkoittaa, että kysely käy läpi taulun Products rivit.

Kyselyn optimointi

Jos kyselyssä haetaan tietoa vain yhdestä taulusta, kysely on yleensä helppo suorittaa, mutta todelliset haasteet tulevat vastaan usean taulun kyselyissä. Tällöin tietokantajärjestelmän tulee osata optimoida kyselyn suorittamista eli muodostaa hyvä suunnitelma, jonka avulla halutut tiedot saadaan kerättyä tehokkaasti tauluista.

Tarkastellaan esimerkkinä seuraavaa kyselyä, joka listaa kurssien ja opettajien nimet:

SELECT Courses.name, Teachers.name
FROM Courses, Teachers
WHERE Courses.teacher_id = Teachers.id;

Koska kysely kohdistuu kahteen tauluun, kyselyn voi ajatella muodostavan ensin kaikki rivien yhdistelmät tauluista Courses ja Teachers ja valitsevan sitten ne rivit, joilla pätee ehto Courses.teacher_id = Teachers.id. Tämä on hyvä ajattelutapa, mutta tämä ei vastaa sitä, miten kunnollinen tietokantajärjestelmä toimii.

Ongelmana on, että tauluissa Courses ja Teachers voi molemmissa olla suuri määrä rivejä. Esimerkiksi jos kummassakin taulussa on miljoona riviä, rivien yhdistelmiä olisi miljoona miljoonaa ja veisi valtavasti aikaa muodostaa ja käydä läpi kaikki yhdistelmät.

Tässä tilanteessa tietokantajärjestelmän pitääkin ymmärtää, mitä käyttäjä oikeastaan on hakemassa ja miten kyselyssä annettu ehto rajoittaa tulosrivejä. Käytännössä riittää käydä läpi kaikki taulun Courses rivit ja etsiä jokaisen rivin kohdalla jotenkin tehokkaasti yksittäinen haluttu rivi taulusta Teachers.

Voimme taas pyytää SQLiteä selittämään kyselyn suunnitelman:

sqlite> EXPLAIN QUERY PLAN SELECT Courses.name, Teachers.name FROM Courses, Teachers WHERE Courses.teacher_id = Teachers.id;
0|0|0|SCAN TABLE Courses
0|1|1|SEARCH TABLE Teachers USING INTEGER PRIMARY KEY (rowid=?)

Tämä kysely käy läpi taulun Courses rivit (SCAN TABLE Courses) ja hakee tietoa taulusta Teachers pääavaimen avulla (SEARCH TABLE Teachers). Jälkimmäinen tarkoittaa, että kun käsittelyssä on tietty taulun Courses rivi, kysely hakee tehokkaasti taulusta Teachers rivin, jossa Teachers.id on sama kuin Courses.teacher_id.

Mutta miten käytännössä taulusta Teachers voi hakea tehokkaasti? Tämä onnistuu käyttämällä taulun yhteydessä olevaa indeksiä.

Indeksit

Indeksi on tietokannan taulun yhteyteen tallennettu hakemistorakenne, jonka tavoitteena on tehostaa tauluun liittyvien kyselyiden suorittamista. Indeksin avulla tietokantajärjestelmä voi selvittää tehokkaasti, missä päin taulua on rivejä, jotka täsmäävät tiettyyn hakuehtoon.

Indeksiä voi ajatella samalla tavalla kuin kirjan lopussa olevaa hakemistoa, jossa kerrotaan hakusanoista, millä kirjan sivuilla ne esiintyvät. Hakemiston avulla löydämme tietyn sanan sijainnit paljon nopeammin kuin lukemalla koko kirjan läpi.

Pääavaimen indeksi

Kun tietokantaan luodaan taulu, sen pääavain saa automaattisesti indeksin. Tämän ansiosta voidaan suorittaa tehokkaasti hakuja, joissa ehto liittyy pääavaimeen.

Esimerkiksi kun luomme SQLitessä taulun

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER
);

niin taululle luodaan indeksi sarakkeelle id ja voimme etsiä tehokkaasti tuotteita id-numeron perusteella. Tämän ansiosta esimerkiksi seuraava kysely toimii tehokkaasti:

SELECT price FROM Products WHERE id = 3;

Voimme varmistaa tämän kysymällä kyselyn suunnitelman:

sqlite> EXPLAIN QUERY PLAN SELECT price FROM Products WHERE id = 3;
selectid    order       from        detail                                                   
----------  ----------  ----------  ---------------------------------------------------------
0           0           0           SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)

Suunnitelmassa näkyy SEARCH TABLE, mikä tarkoittaa, että kysely pystyy hakemaan taulusta tietoa tehokkaasti indeksin avulla.

Indeksin luominen

Pääavaimen indeksi on kätevä, mutta voimme haluta myös etsiä tietoa jonkin muun sarakkeen perusteella. Esimerkiksi seuraava kysely hakee rivit sarakkeen price perusteella:

SELECT name FROM Products WHERE price = 4;

Tämä kysely ei ole oletuksena tehokas, koska sarakkeelle price ei ole indeksiä. Näemme tämän pyytämällä taas selitystä kyselystä:

sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid    order       from        detail             
----------  ----------  ----------  -------------------
0           0           0           SCAN TABLE Products

Nyt suunnitelmassa näkyy SCAN TABLE, mikä tarkoittaa, että kysely joutuu käymään läpi taulun kaikki rivit. Tämä on hidasta, jos taulussa on paljon rivejä.

Voimme kuitenkin luoda uuden indeksin, joka tehostaa saraketta price käyttäviä kyselyitä. Saamme luotua indeksin komennolla CREATE INDEX näin:

CREATE INDEX idx_price ON Products (price);

Tässä idx_price on indeksin nimi, jolla voimme viitata siihen myöhemmin. Indeksi toimii luonnin jälkeen täysin automaattisesti, eli tietokantajärjestelmä osaa käyttää sitä kyselyissä ja huolehtii sen päivittämisestä.

Indeksin luomisen jälkeen voimme kysyä uudestaan kyselyn suunnitelmaa:

sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid    order       from        detail                                               
----------  ----------  ----------  -----------------------------------------------------
0           0           0           SEARCH TABLE Products USING INDEX idx_price (price=?)

Indeksin ansiosta suunnitelmassa ei lue enää SCAN TABLE vaan SEARCH TABLE. Suunnitelmassa näkyy myös, että aikomuksena on hyödyntää indeksiä idx_price.

Lisää käyttötapoja

Voimme käyttää indeksiä myös kyselyissä, joissa haemme pienempiä tai suurempia arvoja. Esimerkiksi sarakkeelle price luodun indeksin avulla voimme etsiä vaikkapa rivejä, joille pätee ehto price < 3 tai price >= 8.

Indeksi on myös mahdollista luoda usean sarakkeen perusteella. Esimerkiksi voisimme luoda indeksin näin:

CREATE INDEX idx_price ON Products (price, name);

Tässä indeksissä rivit on järjestetty ensisijaisesti hinnan ja toissijaisesti nimen mukaan. Indeksi tehostaa hakuja, joissa hakuperusteena on joko pelkkä hinta tai yhdessä hinta ja nimi. Kuitenkaan indeksi ei tehosta hakuja, joissa hakuperusteena on pelkkä nimi.

Miten indeksi toimii?

Indeksi tarvitsee tuekseen hakemistorakenteen, josta voi hakea tehokkaasti rivejä sarakkeen arvon perusteella. Tämä voidaan toteuttaa esimerkiksi puurakenteena, jonka avaimina on sarakkeiden arvoja.

Asiaan liittyvää teoriaa käsitellään tarkemmin kurssilla Tietorakenteet ja algoritmit binäärihakupuiden yhteydessä. Tyypillisiä tietokantojen yhteydessä käytettäviä puurakenteita ovat B-puu ja sen muunnelmat.

Milloin luoda indeksi?

Periaatteessa voisi ajatella, että taulun jokaiselle sarakkeelle kannattaa luoda indeksi, jolloin monenlaiset kyselyt ovat nopeita. Tämä ei ole kuitenkaan käytännössä hyvä idea.

Vaikka indeksit tehostavat kyselyitä, niissä on myös kaksi ongelmaa: indeksi vie tilaa ja hidastaa myös tiedon lisäämistä ja muuttamista. Jälkimmäinen johtuu siitä, että kun taulun sisältö muuttuu, muutos täytyy päivittää kaikkiin tauluun liittyviin indekseihin. Indeksiä ei siis kannata luoda huvin vuoksi.

Hyvä syy indeksin luontiin on, että haluamme suorittaa usein tietynlaisia kyselyitä ja ne toimivat hitaasti, koska tietokantajärjestelmä joutuu käymään läpi turhaan jonkin taulun kaikki rivit kyselyn aikana. Tällöin voimme lisätä taululle sopivan indeksin, jonka avulla tällaiset kyselyt toimivat jatkossa tehokkaasti.

Indekseillä on käytännössä suuri vaikutus tietokantojen tehokkuuteen. Moni tietokanta toimii hitaasti sen takia, että siitä puuttuu oleellisia indeksejä.

Indeksit ovat myös yksi esimerkki siitä, miten toisteinen tieto voi tehostaa kyselyjä. Indekseissä kuitenkaan toisteista tietoa ei tallenneta tauluun vaan taulun ulkopuolelle erilliseen hakemistorakenteeseen.

8. Tietokantojen teoria

Tämä materiaalin luku on päivittymässä, ja uusi versio ilmestyy syyskuun puoliväliin mennessä.