SQL Trainer

Spring 2025

Osa 1
Osa 2
Osa 3
all
1
2
3
4
5
6
7
8
9
10
11

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:

  • nettisivuston käyttäjärekisteri
  • 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 ovat tavallisen päivän aikana yhteydessä lukuisiin tietokantoihin.

Tietokantojen haasteet

Tietokantojen tekniseen toteutukseen liittyy monia haasteita:

  • Tiedon määrä: 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 on yleensä 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ä järkevänä 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ä termiä tietokanta käytetään usein myös silloin, kun viitataan tietokantajärjestelmään.

Useimmat käytössä olevat tietokannat perustuvat relaatiomalliin ja SQL-kieleen, joihin tutustumme tällä kurssilla. Esimerkiksi MySQL, PostgreSQL ja SQLite ovat tällaisia tietokantajärjestelmiä. Näiden tietokantojen teoreettinen perusta syntyi 1970-luvulla.

Termi NoSQL viittaa tietokantaan, joka perustuu muuhun kuin relaatiomalliin ja SQL-kieleen. Esimerkiksi MongoDB ja Redis ovat saavuttaneet viime aikoina suosiota. 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-tiedostona tähän tapaan:

012121212;1;2020-05-03 12:50:32;100
012341234;1;2020-05-03 14:02:12;100
012121212;2;2020-05-04 14:05:50;100
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:

Tiedon määrä

Kun tiedon määrä kasvaa, tiedon hakeminen CSV-tiedostosta voi muodostua ongelmaksi. 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 koon kasvaessa alkaa olla työlästä 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ässä 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. Relaatiomallin ytimessä on kaksi perusideaa:

  1. Kaikki tieto tallennetaan tauluihin riveinä, jotka voivat viitata toisiinsa.
  2. 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 Tuotteet, Asiakkaat ja Ostokset on tietoa tuotteista, asiakkaista ja heidän ostoskoriensa sisällöstä.

{: .inline title=“Tuotteet” }

idnimihinta
1retiisi7
2porkkana5
3nauris4
4lanttu8
5selleri4

{: .inline title=“Asiakkaat” }

idnimi
1Uolevi
2Maija
3Aapeli

{: .inline title=“Ostokset” }

asiakas_idtuote_id
12
15
21
24
25

Esimerkiksi taulun Tuotteet sarakkeet ovat id, nimi ja hinta. Taulussa on tällä hetkellä viisi riviä tietoa, jotka kuvaavat tietokannassa olevat tuotteet.

Tauluissa Tuotteet ja Asiakkaat jokaisella rivillä on yksilöllinen id-numero, jonka avulla niihin voi viitata. Tämä on yleinen tapa tietokantojen suunnittelussa. Tämän ansiosta taulussa Ostokset 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 hinta FROM Tuotteet WHERE nimi = '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 hinta
FROM Tuotteet
WHERE nimi = 'retiisi';
SELECT
  hinta
FROM
  Tuotteet
WHERE
  nimi = '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 Tuotteet, jossa on kolme saraketta:

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta 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 hinta ovat kokonaislukuja (INTEGER) ja sarake nimi 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 mihin tahansa riviin.

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 Tuotteet:

INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);

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

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7

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

INSERT INTO Tuotteet (nimi) VALUES ('retiisi');

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

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     NULL

Esimerkkitaulu

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

INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);

Taulun sisältö on siis seuraavanlainen:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4

Tiedon hakeminen

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

SELECT * FROM Tuotteet;

Tässä tapauksessa kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           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 nimi FROM Tuotteet;

Kyselyn tulos on seuraava:

nimi      
----------
retiisi    
porkkana             
nauris               
lanttu               
selleri

Seuraava kysely puolestaan hakee tuotteiden nimet ja hinnat:

SELECT nimi, hinta FROM Tuotteet;

Nyt kyselyn tulos muuttuu näin:

nimi        hinta     
----------  ----------
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 Tuotteet WHERE nimi = 'lanttu';

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
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 Tuotteet WHERE hinta >= 4 AND hinta <= 6;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
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 Tuotteet WHERE hinta <> 4;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
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 Tuotteet ORDER BY nimi;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
4           lanttu      8         
3           nauris      4         
2           porkkana    5         
1           retiisi     7         
5           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 Tuotteet ORDER BY nimi DESC;

Tämän seurauksena kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
5           selleri     4  
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
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 Tuotteet ORDER BY nimi;
SELECT * FROM Tuotteet ORDER BY nimi 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 Tuotteet ORDER BY hinta DESC, nimi;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
4           lanttu      8         
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
5           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 hinta FROM Tuotteet;

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

hinta     
----------
7         
5         
4         
8         
4

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

SELECT DISTINCT hinta FROM Tuotteet;

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

hinta     
----------
7         
5         
4         
8

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 Tuotteet ORDER BY hinta;

Kyselyn tuloksena on seuraava tulostaulu:

id          nimi        hinta     
----------  ----------  ----------
3           nauris      2
5           selleri     4         
2           porkkana    5         
1           retiisi     7         
4           lanttu      8

Saamme haettua kolme halvinta tuotetta seuraavasti:

SELECT * FROM Tuotteet ORDER BY hinta LIMIT 3;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
3           nauris      2         
5           selleri     4         
2           porkkana    5

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

SELECT * FROM Tuotteet ORDER BY hinta LIMIT 3 OFFSET 1;

Tämän kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
5           selleri     4         
2           porkkana    5         
1           retiisi     7

Tiedon muuttaminen

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

UPDATE Tuotteet SET hinta = 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 Tuotteet SET nimi = 'ananas', hinta = 6 WHERE id = 2;

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

UPDATE Tuotteet SET hinta = 1;

Tiedon poistaminen

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

DELETE FROM Tuotteet WHERE id = 5;

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

DELETE FROM Tuotteet;

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

DROP TABLE Tuotteet;

Kommentit

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

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta INTEGER -- hinta euroina
)

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

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta INTEGER /* hinta euroina */
)

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 Tuotteet:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4

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

SELECT COUNT(*) FROM Tuotteet;
COUNT(*)
----------
5

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

SELECT COUNT(*) FROM Tuotteet WHERE hinta = 4;
COUNT(*)
----------
2

Seuraava kysely puolestaan hakee summan tuotteiden hinnoista:

SELECT SUM(hinta) FROM Tuotteet;
SUM(hinta)
----------
28

Rivien määrä COUNT-kyselyssä

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 arvo (eli sarakkeen arvo ei ole NULL).

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

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           nauris      4         
3           lanttu      NULL
4           selleri     4

Seuraava kysely hakee rivien yhteismäärän:

SELECT COUNT(*) FROM Tuotteet;
COUNT(*)  
----------
4

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

SELECT COUNT(hinta) FROM Tuotteet;
COUNT(hinta)
------------
3

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

SELECT COUNT(DISTINCT hinta) FROM Tuotteet;
COUNT(DISTINCT hinta)
---------------------
2

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 Tyontekijat:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
1           Anna        Google      8000      
2           Liisa       Google      7500      
3           Kaaleppi    Amazon      5000      
4           Uolevi      Amazon      8000      
5           Maija       Google      9500      
6           Vihtori     Facebook    5000

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

SELECT yritys, COUNT(*) FROM Tyontekijat GROUP BY yritys;

Kyselyn tulos on seuraava:

yritys      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äytetyssä 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 yritys, joten rivit jaetaan ryhmiin sarakkeen yritys mukaan. Tässä tapauksessa ryhmät ovat:

Ryhmä 1:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
3           Kaaleppi    Amazon      5000      
4           Uolevi      Amazon      8000

Ryhmä 2:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
6           Vihtori     Facebook    5000

Ryhmä 3:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
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(*).

Ryhmittely SQLitessä
Huomaa, että SQLite sallii myös seuraavan kyselyn, jossa haetaan ryhmittelyn ulkopuolinen sarake: 
SELECT yritys, nimi FROM Tyontekijat GROUP BY yritys;
yritys      nimi      
----------  ----------
Amazon      Uolevi    
Facebook    Vihtori
Google      Maija

Koska sarake nimi ei kuulu ryhmittelyyn, sillä voi olla useita arvoja ryhmässä ja tulostauluun tulee yksi niistä. Tällainen kysely ei kuitenkaan toimi monissa tietokannoissa.

Lisää kyselyjä

Seuraava kysely hakee joka yrityksestä palkkojen summan:

SELECT yritys, SUM(palkka) FROM Tyontekijat GROUP BY yritys;
yritys      SUM(palkka)
----------  -----------
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 yritys, MAX(palkka) FROM Tyontekijat GROUP BY yritys;
yritys      MAX(palkka)
----------  -----------
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 korkein:

SELECT
  yritys, MAX(palkka) AS korkein
FROM
  Tyontekijat
GROUP BY
  yritys;
yritys      korkein
----------  ----------
Amazon      8000         
Facebook    5000
Google      9500

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

SELECT
  yritys, MAX(palkka) korkein
FROM
  Tyontekijat
GROUP BY
  yritys;

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
  yritys, COUNT(*)
FROM
  Tyontekijat
GROUP BY
  yritys
HAVING
  COUNT(*) >= 2;
yritys      COUNT(*)  
----------  ----------
Amazon      2         
Google      3

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

SELECT
  yritys
FROM
  Tyontekijat
GROUP BY
  yritys
HAVING
  COUNT(*) >= 2;
yritys    
----------
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 Tehtavat, jossa on projekteihin liittyviä tehtäviä. Jokaisella tehtävällä on tietty tärkeysaste. Tehtävä on kriittinen, jos sen tärkeysaste on ainakin 3.

id          projekti_id  tarkeys   
----------  -----------  ----------
1           1            3         
2           1            4         
3           1            4         
4           2            1         
5           2            5         
6           3            2         
7           3            4         
8           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
  projekti_id, COUNT(*)
FROM
  Tehtavat
WHERE
  tarkeys >= 3
GROUP BY
  projekti_id
HAVING
  COUNT(*) >= 2
ORDER BY
  projekti_id
LIMIT
  10;

Kyselyn tulos on tässä:

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

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

Katsotaan nyt tarkemmin, miten kysely toimii. Kyselyn lähtökohtana ovat kaikki taulussa Tehtavat olevat rivit. Ehto WHERE tarkeys >= 3 valitsee käsittelyyn seuraavat rivit:

id          projekti_id  tarkeys   
----------  -----------  ----------
1           1            3         
2           1            4         
3           1            4         
5           2            5         
7           3            4         
8           3            5

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

Ryhmä 1:

id          projekti_id  tarkeys   
----------  -----------  ----------
1           1            3         
2           1            4         
3           1            4

Ryhmä 2:

id          projekti_id  tarkeys   
----------  -----------  ----------
5           2            5

Ryhmä 3:

id          projekti_id  tarkeys   
----------  -----------  ----------
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 projekti_id sekä funktion COUNT(*) antama tulos eli ryhmän rivien määrä. Tässä tapauksessa projektissa 1 on 3 tärkeää tehtävää ja projektissa 3 on 2 tärkeää tehtävää.

Osa ORDER BY projekti_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:

projekti_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 Tuotteet (
   ...> id INTEGER PRIMARY KEY,
   ...> nimi TEXT, hinta INTEGER);
sqlite> .tables
Tuotteet
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);
sqlite> SELECT * FROM Tuotteet;
1|retiisi|7
2|porkkana|5
3|nauris|4
4|lanttu|8
5|selleri|4
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Tuotteet;
id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4         
sqlite> .quit

Esimerkissä luodaan aluksi taulu Tuotteet ja tarkastetaan sitten tulkin komennolla .tables, mitä tauluja tietokannassa on. Ainoa taulu on Tuotteet, 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 ei ole kovin tyylikäs, 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 testi.db. Tämän ansiosta tietokannan sisältö on edelleen tallessa, kun tulkki käynnistetään uudestaan.

$ sqlite3 testi.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY,
   ...>                        nimi TEXT, hinta INTEGER);
sqlite> .tables
Tuotteet
sqlite> .quit
$ sqlite3 testi.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
Tuotteet
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
CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT, hinta INTEGER);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);
.mode column
.headers on
SELECT * FROM Tuotteet;

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

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

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 Tuotteet, jossa on kolme saraketta:

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta 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 hinta ovat kokonaislukuja (INTEGER) ja sarake nimi 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 mihin tahansa riviin.

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 Tuotteet:

INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);

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

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7

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

INSERT INTO Tuotteet (nimi) VALUES ('retiisi');

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

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     NULL

Esimerkkitaulu

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

INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);

Taulun sisältö on siis seuraavanlainen:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4

Tiedon hakeminen

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

SELECT * FROM Tuotteet;

Tässä tapauksessa kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           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 nimi FROM Tuotteet;

Kyselyn tulos on seuraava:

nimi      
----------
retiisi    
porkkana             
nauris               
lanttu               
selleri

Seuraava kysely puolestaan hakee tuotteiden nimet ja hinnat:

SELECT nimi, hinta FROM Tuotteet;

Nyt kyselyn tulos muuttuu näin:

nimi        hinta     
----------  ----------
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 Tuotteet WHERE nimi = 'lanttu';

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
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 Tuotteet WHERE hinta >= 4 AND hinta <= 6;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
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 Tuotteet WHERE hinta <> 4;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
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 Tuotteet ORDER BY nimi;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
4           lanttu      8         
3           nauris      4         
2           porkkana    5         
1           retiisi     7         
5           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 Tuotteet ORDER BY nimi DESC;

Tämän seurauksena kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
5           selleri     4  
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
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 Tuotteet ORDER BY nimi;
SELECT * FROM Tuotteet ORDER BY nimi 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 Tuotteet ORDER BY hinta DESC, nimi;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
4           lanttu      8         
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
5           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 hinta FROM Tuotteet;

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

hinta     
----------
7         
5         
4         
8         
4

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

SELECT DISTINCT hinta FROM Tuotteet;

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

hinta     
----------
7         
5         
4         
8

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 Tuotteet ORDER BY hinta;

Kyselyn tuloksena on seuraava tulostaulu:

id          nimi        hinta     
----------  ----------  ----------
3           nauris      2
5           selleri     4         
2           porkkana    5         
1           retiisi     7         
4           lanttu      8

Saamme haettua kolme halvinta tuotetta seuraavasti:

SELECT * FROM Tuotteet ORDER BY hinta LIMIT 3;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
3           nauris      2         
5           selleri     4         
2           porkkana    5

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

SELECT * FROM Tuotteet ORDER BY hinta LIMIT 3 OFFSET 1;

Tämän kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
5           selleri     4         
2           porkkana    5         
1           retiisi     7

Tiedon muuttaminen

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

UPDATE Tuotteet SET hinta = 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 Tuotteet SET nimi = 'ananas', hinta = 6 WHERE id = 2;

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

UPDATE Tuotteet SET hinta = 1;

Tiedon poistaminen

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

DELETE FROM Tuotteet WHERE id = 5;

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

DELETE FROM Tuotteet;

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

DROP TABLE Tuotteet;

Kommentit

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

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta INTEGER -- hinta euroina
)

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

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta INTEGER /* hinta euroina */
)

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 Tuotteet:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4

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

SELECT COUNT(*) FROM Tuotteet;
COUNT(*)
----------
5

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

SELECT COUNT(*) FROM Tuotteet WHERE hinta = 4;
COUNT(*)
----------
2

Seuraava kysely puolestaan hakee summan tuotteiden hinnoista:

SELECT SUM(hinta) FROM Tuotteet;
SUM(hinta)
----------
28

Rivien määrä COUNT-kyselyssä

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 arvo (eli sarakkeen arvo ei ole NULL).

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

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           nauris      4         
3           lanttu      NULL
4           selleri     4

Seuraava kysely hakee rivien yhteismäärän:

SELECT COUNT(*) FROM Tuotteet;
COUNT(*)  
----------
4

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

SELECT COUNT(hinta) FROM Tuotteet;
COUNT(hinta)
------------
3

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

SELECT COUNT(DISTINCT hinta) FROM Tuotteet;
COUNT(DISTINCT hinta)
---------------------
2

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 Tyontekijat:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
1           Anna        Google      8000      
2           Liisa       Google      7500      
3           Kaaleppi    Amazon      5000      
4           Uolevi      Amazon      8000      
5           Maija       Google      9500      
6           Vihtori     Facebook    5000

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

SELECT yritys, COUNT(*) FROM Tyontekijat GROUP BY yritys;

Kyselyn tulos on seuraava:

yritys      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äytetyssä 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 yritys, joten rivit jaetaan ryhmiin sarakkeen yritys mukaan. Tässä tapauksessa ryhmät ovat:

Ryhmä 1:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
3           Kaaleppi    Amazon      5000      
4           Uolevi      Amazon      8000

Ryhmä 2:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
6           Vihtori     Facebook    5000

Ryhmä 3:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
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(*).

Ryhmittely SQLitessä
Huomaa, että SQLite sallii myös seuraavan kyselyn, jossa haetaan ryhmittelyn ulkopuolinen sarake: 
SELECT yritys, nimi FROM Tyontekijat GROUP BY yritys;
yritys      nimi      
----------  ----------
Amazon      Uolevi    
Facebook    Vihtori
Google      Maija

Koska sarake nimi ei kuulu ryhmittelyyn, sillä voi olla useita arvoja ryhmässä ja tulostauluun tulee yksi niistä. Tällainen kysely ei kuitenkaan toimi monissa tietokannoissa.

Lisää kyselyjä

Seuraava kysely hakee joka yrityksestä palkkojen summan:

SELECT yritys, SUM(palkka) FROM Tyontekijat GROUP BY yritys;
yritys      SUM(palkka)
----------  -----------
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 yritys, MAX(palkka) FROM Tyontekijat GROUP BY yritys;
yritys      MAX(palkka)
----------  -----------
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 korkein:

SELECT
  yritys, MAX(palkka) AS korkein
FROM
  Tyontekijat
GROUP BY
  yritys;
yritys      korkein
----------  ----------
Amazon      8000         
Facebook    5000
Google      9500

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

SELECT
  yritys, MAX(palkka) korkein
FROM
  Tyontekijat
GROUP BY
  yritys;

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
  yritys, COUNT(*)
FROM
  Tyontekijat
GROUP BY
  yritys
HAVING
  COUNT(*) >= 2;
yritys      COUNT(*)  
----------  ----------
Amazon      2         
Google      3

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

SELECT
  yritys
FROM
  Tyontekijat
GROUP BY
  yritys
HAVING
  COUNT(*) >= 2;
yritys    
----------
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 Tehtavat, jossa on projekteihin liittyviä tehtäviä. Jokaisella tehtävällä on tietty tärkeysaste. Tehtävä on kriittinen, jos sen tärkeysaste on ainakin 3.

id          projekti_id  tarkeys   
----------  -----------  ----------
1           1            3         
2           1            4         
3           1            4         
4           2            1         
5           2            5         
6           3            2         
7           3            4         
8           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
  projekti_id, COUNT(*)
FROM
  Tehtavat
WHERE
  tarkeys >= 3
GROUP BY
  projekti_id
HAVING
  COUNT(*) >= 2
ORDER BY
  projekti_id
LIMIT
  10;

Kyselyn tulos on tässä:

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

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

Katsotaan nyt tarkemmin, miten kysely toimii. Kyselyn lähtökohtana ovat kaikki taulussa Tehtavat olevat rivit. Ehto WHERE tarkeys >= 3 valitsee käsittelyyn seuraavat rivit:

id          projekti_id  tarkeys   
----------  -----------  ----------
1           1            3         
2           1            4         
3           1            4         
5           2            5         
7           3            4         
8           3            5

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

Ryhmä 1:

id          projekti_id  tarkeys   
----------  -----------  ----------
1           1            3         
2           1            4         
3           1            4

Ryhmä 2:

id          projekti_id  tarkeys   
----------  -----------  ----------
5           2            5

Ryhmä 3:

id          projekti_id  tarkeys   
----------  -----------  ----------
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 projekti_id sekä funktion COUNT(*) antama tulos eli ryhmän rivien määrä. Tässä tapauksessa projektissa 1 on 3 tärkeää tehtävää ja projektissa 3 on 2 tärkeää tehtävää.

Osa ORDER BY projekti_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:

projekti_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 Tuotteet (
   ...> id INTEGER PRIMARY KEY,
   ...> nimi TEXT, hinta INTEGER);
sqlite> .tables
Tuotteet
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
sqlite> INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);
sqlite> SELECT * FROM Tuotteet;
1|retiisi|7
2|porkkana|5
3|nauris|4
4|lanttu|8
5|selleri|4
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Tuotteet;
id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4         
sqlite> .quit

Esimerkissä luodaan aluksi taulu Tuotteet ja tarkastetaan sitten tulkin komennolla .tables, mitä tauluja tietokannassa on. Ainoa taulu on Tuotteet, 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 ei ole kovin tyylikäs, 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 testi.db. Tämän ansiosta tietokannan sisältö on edelleen tallessa, kun tulkki käynnistetään uudestaan.

$ sqlite3 testi.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY,
   ...>                        nimi TEXT, hinta INTEGER);
sqlite> .tables
Tuotteet
sqlite> .quit
$ sqlite3 testi.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
Tuotteet
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
CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT, hinta INTEGER);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);
.mode column
.headers on
SELECT * FROM Tuotteet;

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

$ sqlite3 < commands.sql
id          nimi        hinta     
----------  ----------  ----------
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 Opettajat tietoa opettajista. Jokaisella opettajalla on id-numero, jolla voimme viitata siihen.

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

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

id          nimi              opettaja_id
----------  ----------------  -----------
1           Laskennan mallit  3          
2           Ohjelmoinnin per  1          
3           Ohjelmoinnin jat  1          
4           Tietokantojen pe  4          
5           Tietorakenteet j  3

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

SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id;

Koska kyselyssä on monta taulua, ilmoitamme sarakkeiden taulut. Esimerkiksi Kurssit.nimi viittaa taulun Kurssit sarakkeeseen nimi.

Kysely antaa seuraavan tuloksen:

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietokantojen pe  Laaksonen 
Tietorakenteet j  Kivinen

Mitä tässä tapahtui?

Yllä olevassa kyselyssä uutena asiana on, että kysely koskee useaa taulua (FROM Kurssit, Opettajat), 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 Kurssit, Opettajat;

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

id          nimi              opettaja_id  id          nimi      
----------  ----------------  -----------  ----------  ----------
1           Laskennan mallit  3            1           Kaila     
1           Laskennan mallit  3            2           Luukkainen
1           Laskennan mallit  3            3           Kivinen   
1           Laskennan mallit  3            4           Laaksonen 
2           Ohjelmoinnin per  1            1           Kaila     
2           Ohjelmoinnin per  1            2           Luukkainen
2           Ohjelmoinnin per  1            3           Kivinen   
2           Ohjelmoinnin per  1            4           Laaksonen 
3           Ohjelmoinnin jat  1            1           Kaila     
3           Ohjelmoinnin jat  1            2           Luukkainen
3           Ohjelmoinnin jat  1            3           Kivinen   
3           Ohjelmoinnin jat  1            4           Laaksonen 
4           Tietokantojen pe  4            1           Kaila     
4           Tietokantojen pe  4            2           Luukkainen
4           Tietokantojen pe  4            3           Kivinen   
4           Tietokantojen pe  4            4           Laaksonen 
5           Tietorakenteet j  3            1           Kaila     
5           Tietorakenteet j  3            2           Luukkainen
5           Tietorakenteet j  3            3           Kivinen   
5           Tietorakenteet j  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
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id;

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

id          nimi              opettaja_id  id          nimi      
----------  ----------------  -----------  ----------  ----------
1           Laskennan mallit  3            3           Kivinen   
2           Ohjelmoinnin per  1            1           Kaila     
3           Ohjelmoinnin jat  1            1           Kaila     
4           Tietokantojen pe  4            4           Laaksonen 
5           Tietorakenteet j  3            3           Kivinen

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

SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id;

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

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietokantojen pe  Laaksonen 
Tietorakenteet j  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
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id AND
  Opettajat.nimi = 'Kivinen';

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

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Tietorakenteet j  Kivinen

Taulujen lyhyet nimet

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

SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id;

voidaan esittää lyhemmin näin:

SELECT
  K.nimi, O.nimi
FROM
  Kurssit AS K, Opettajat AS O
WHERE
  K.opettaja_id = O.id;

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

SELECT
  K.nimi, O.nimi
FROM
  Kurssit K, Opettajat O
WHERE
  K.opettaja_id = O.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.nimi, B.nimi FROM Opettajat A, Opettajat B;

Kyselyn tulos on seuraava:

nimi        nimi      
----------  ----------
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:

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

  2. 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 niin, että siinä on kolme taulua: Tuotteet, Asiakkaat ja Ostokset. Liitostaulu Ostokset 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:

{: .inline title=“Tuotteet” }

idnimihinta
1retiisi7
2porkkana5
3nauris4
4lanttu8
5selleri4

{: .inline title=“Asiakkaat” }

idnimi
1Uolevi
2Maija
3Aapeli

{: .inline title=“Ostokset” }

asiakas_idtuote_id
12
15
21
24
25

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
  A.nimi, T.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;

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

nimi        nimi      
----------  ----------
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 Asiakkaat A, Tuotteet T, Ostokset O;

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          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      1           retiisi     7           1           2         
1           Uolevi      1           retiisi     7           1           5         
1           Uolevi      1           retiisi     7           2           1         
1           Uolevi      1           retiisi     7           2           4         
1           Uolevi      1           retiisi     7           2           5         
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      2           porkkana    5           1           5         
1           Uolevi      2           porkkana    5           2           1         
1           Uolevi      2           porkkana    5           2           4         
1           Uolevi      2           porkkana    5           2           5         
1           Uolevi      3           nauris      4           1           2         
1           Uolevi      3           nauris      4           1           5         
1           Uolevi      3           nauris      4           2           1         
1           Uolevi      3           nauris      4           2           4         
1           Uolevi      3           nauris      4           2           5         
...

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

SELECT
  *
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;
id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5         
2           Maija       1           retiisi     7           2           1         
2           Maija       4           lanttu      8           2           4         
2           Maija       5           selleri     4           2           5

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

SELECT
  A.nimi, T.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;
nimi        nimi      
----------  ----------
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
  T.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id AND A.nimi = 'Maija';
nimi      
----------
retiisi   
lanttu    
selleri

Seuraava kysely puolestaan kertoo, keiden korissa on selleri:

SELECT
  A.nimi
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id AND T.nimi = 'selleri';
nimi      
----------
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:

{: .inline title=“Tuotteet” }

idnimihinta
1retiisi7
2porkkana5
3nauris4
4lanttu8
5selleri4

{: .inline title=“Asiakkaat” }

idnimi
1Uolevi
2Maija
3Aapeli

{: .inline title=“Ostokset” }

asiakas_idtuote_id
12
15
21
24
25

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

SELECT
  A.nimi, COUNT(T.id), SUM(T.hinta)
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id
GROUP BY
  A.id;

Kyselyn tulos on seuraava:

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
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
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id;
id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5         
2           Maija       1           retiisi     7           2           1         
2           Maija       4           lanttu      8           2           4         
2           Maija       5           selleri     4           2           5

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

Ryhmä 1:

id          nimi        id          nimi        hinta       asiakas_id  tuote_id  
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      2           porkkana    5           1           2         
1           Uolevi      5           selleri     4           1           5

Ryhmä 2:

id          nimi        id          nimi        hinta       asiakas_id  tuote_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(T.id) sekä ostosten yhteishinta SUM(T.hinta).

Huomaa, että kyselyssä ryhmittely tapahtuu sarakkeen A.id mukaan, mutta kyselyssä haetaan sarake A.nimi. Tämä on sinänsä järkevää, koska sarake A.id määrää sarakkeen A.nimi. Tämä kysely toimii SQLitessä, mutta muut tietokannat voivat vaatia, että sellaisenaan haettavan sarakkeen tulee aina esiintyä myös ryhmittelyssä. Tällöin ryhmittelyn tulisi olla GROUP BY A.id, A.nimi.

Puuttuvan rivin ongelma

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

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
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
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit, Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id;
SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit JOIN Opettajat
          ON Kurssit.opettaja_id = Opettajat.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 Kurssit ja Opettajat, mutta taulussa Kurssit yhdeltä kurssilta puuttuu opettaja:

id          nimi              opettaja_id
----------  ----------------  -----------
1           Laskennan mallit  3          
2           Ohjelmoinnin per  1          
3           Ohjelmoinnin jat  1          
4           Tietokantojen pe  NULL        
5           Tietorakenteet j  3

Rivin 4 sarakkeessa opettaja_id on arvo NULL, joten jos suoritamme jommankumman äskeisen kyselyn, ongelmaksi tulee, että rivi 4 ei täsmää mihinkään taulun Opettajat riviin. Tämän seurauksena tulostauluun ei tule riviä kurssista Tietokantojen perusteet:

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietorakenteet j  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
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit LEFT JOIN Opettajat
          ON Kurssit.opettaja_id = Opettajat.id;

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

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen
Ohjelmoinnin per  Kaila
Ohjelmoinnin jat  Kaila
Tietokantojen pe  NULL
Tietorakenteet j  Kivinen

Miten kysely toimii?

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

SELECT
  *
FROM
  Kurssit LEFT JOIN Opettajat
          ON Kurssit.opettaja_id = Opettajat.id;
id          nimi              opettaja_id  id          nimi      
----------  ----------------  -----------  ----------  ----------
1           Laskennan mallit  3            3           Kivinen   
2           Ohjelmoinnin per  1            1           Kaila     
3           Ohjelmoinnin jat  1            1           Kaila     
4           Tietokantojen pe  NULL         NULL        NULL
5           Tietorakenteet j  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: 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
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit LEFT JOIN Opettajat
          ON Kurssit.opettaja_id = Opettajat.id;
nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietokantojen pe            
Tietorakenteet j  Kivinen

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

SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit LEFT JOIN Opettajat
WHERE
  Kurssit.opettaja_id = Opettajat.id;
nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per  Kaila     
Ohjelmoinnin jat  Kaila     
Tietorakenteet j  Kivinen

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

SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit LEFT JOIN Opettajat
          ON Kurssit.opettaja_id = Opettajat.id
WHERE
  Kurssit.nimi <> 'Ohjelmoinnin perusteet';

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

nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin jat  Kaila     
Tietokantojen pe            
Tietorakenteet j  Kivinen

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

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

SELECT
  Kurssit.nimi, Opettajat.nimi
FROM
  Kurssit LEFT JOIN Opettajat
          ON Kurssit.opettaja_id = Opettajat.id AND 
             Kurssit.nimi <> 'Ohjelmoinnin perusteet';
nimi              nimi      
----------------  ----------
Laskennan mallit  Kivinen   
Ohjelmoinnin per            
Ohjelmoinnin jat  Kaila     
Tietokantojen pe            
Tietorakenteet j  Kivinen

Tässä tapauksessa kursseja Ohjelmoinnin perusteet ja Tietokantojen perusteet 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:

{: .inline title=“Tuotteet” }

idnimihinta
1retiisi7
2porkkana5
3nauris4
4lanttu8
5selleri4

{: .inline title=“Asiakkaat” }

idnimi
1Uolevi
2Maija
3Aapeli

{: .inline title=“Ostokset” }

asiakas_idtuote_id
12
15
21
24
25

Muodostimme yhteenvedon ostoskoreista seuraavalla kyselyllä:

SELECT
  A.nimi, COUNT(T.id), SUM(T.hinta)
FROM
  Asiakkaat A, Tuotteet T, Ostokset O
WHERE
  A.id = O.asiakas_id AND T.id = O.tuote_id
GROUP BY
  A.id;

Kuitenkin ongelmaksi tuli, että Aapeli puuttuu yhteenvedosta:

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
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
  A.nimi, COUNT(T.id), SUM(T.hinta)
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
              LEFT JOIN Tuotteet T ON T.id = O.tuote_id
GROUP BY
  A.id;

Nyt myös Aapeli ilmestyy mukaan yhteenvetoon:

nimi        COUNT(T.id)  SUM(T.hinta)
----------  -----------  ------------
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
  A.nimi, COUNT(T.id), IFNULL(SUM(T.hinta),0)
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
              LEFT JOIN Tuotteet T ON T.id = O.tuote_id
GROUP BY
  A.id;

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

nimi        COUNT(T.id)  IFNULL(SUM(T.hinta),0)
----------  -----------  ----------------------
Uolevi      2            9           
Maija       3            19          
Aapeli      0            0

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

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 Asiakkaat ja Ostokset:

SELECT
  *
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id;
id          nimi        asiakas_id  tuote_id  
----------  ----------  ----------  ----------
1           Uolevi      1           2         
1           Uolevi      1           5         
2           Maija       2           1         
2           Maija       2           4         
2           Maija       2           5         
3           Aapeli      NULL        NULL

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

SELECT
  *
FROM
  Asiakkaat A LEFT JOIN Ostokset O ON A.id = O.asiakas_id
              LEFT JOIN Tuotteet T ON T.id = O.tuote_id;
id          nimi        asiakas_id  tuote_id    id          nimi        hinta     
----------  ----------  ----------  ----------  ----------  ----------  ----------
1           Uolevi      1           2           2           porkkana    5         
1           Uolevi      1           5           5           selleri     4         
2           Maija       2           1           1           retiisi     7         
2           Maija       2           4           4           lanttu      8         
2           Maija       2           5           5           selleri     4         
3           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 Elokuvat(
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  vuosi INTEGER
);

Tässä sarakkeen nimi tyyppi on TEXT (merkkijono) ja sarakkeen vuosi 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).

{: .note-title } 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 Elokuvat (nimi, vuosi) 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 Tapahtumat(
  id INTEGER PRIMARY KEY,
  paiva TIMESTAMP,
  viesti 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 hinta FROM Tuotteet WHERE nimi = 'retiisi';

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

Voimme rakentaa monimutkaisempia lausekkeita samaan tapaan kuin ohjelmoinnissa. Esimerkiksi kysely

SELECT hinta * 5 FROM Tuotteet;

antaa jokaisen tuotteen hinnan viisinkertaisena ja kysely

SELECT nimi FROM Tuotteet WHERE hinta % 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 Tuotteet WHERE hinta 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 Tuotteet WHERE hinta >= 4 AND hinta <= 6;

CASE

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

SELECT
  nimi,
  CASE WHEN hinta > 5 THEN 'kallis' ELSE 'halpa' END
FROM
  Tuotteet;

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.

IN

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

SELECT
  SUM(hinta)
FROM
  Tuotteet
WHERE
  nimi 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ä _ (mikä tahansa yksittäinen merkki) sekä % (mikä tahansa määrä mitä tahansa merkkejä). Esimerkiksi kysely

SELECT * FROM Tuotteet WHERE nimi LIKE '%ri%';

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

Funktiot

Lausekkeiden osana voi esiintyä myös 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 Tuotteet WHERE LENGTH(nimi) = 6;

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

SELECT
  SUBSTR(nimi, 1, 1), COUNT(*)
FROM
  Tuotteet
GROUP BY
  SUBSTR(nimi, 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 Tuotteet ORDER BY RANDOM();

ORDER BY ja lausekkeet

Voisi kuvitella, että kyselyssä

SELECT * FROM Tuotteet 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 Elokuvat Dumbon vuosi puuttuu, joten sen kohdalla on NULL:

id          nimi        vuosi     
----------  ----------  ----------
1           Lumikki     1937      
2           Fantasia    1940      
3           Pinocchio   1940      
4           Dumbo       NULL
5           Bambi       1942

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

SELECT * FROM Elokuvat WHERE vuosi = 1940;
id          nimi        vuosi     
----------  ----------  ----------
2           Fantasia    1940      
3           Pinocchio   1940      
SELECT * FROM Elokuvat WHERE vuosi <> 1940;
id          nimi        vuosi     
----------  ----------  ----------
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 Elokuvat WHERE vuosi IS NULL;
id          nimi        vuosi     
----------  ----------  ----------
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 Tyontekijat:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
1           Anna        Google      8000      
2           Liisa       Google      7500      
3           Kaaleppi    Amazon      NULL   
4           Uolevi      Amazon      NULL
5           Maija       Google      9500

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

SELECT COUNT(palkka) FROM Tyontekijat WHERE yritys = 'Google';
COUNT(palkka)
-------------
3

SELECT COUNT(palkka) FROM Tyontekijat WHERE yritys = 'Amazon';
COUNT(palkka)
-------------
0

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

SELECT SUM(palkka) FROM Tyontekijat WHERE yritys = 'Google';
SUM(palkka)
-----------
25000      
SELECT SUM(palkka) FROM Tyontekijat WHERE yritys = 'Amazon';
SUM(palkka)
-----------
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.

Yleisempi 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 Tulokset. Oletamme, että taulun sisältö on seuraava:

id          nimi        tulos     
----------  ----------  ----------
1           Uolevi      120       
2           Maija       80        
3           Liisa       120       
4           Aapeli      45        
5           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
  nimi, tulos
FROM
  Tulokset
WHERE
  tulos = (SELECT MAX(tulos) FROM Tulokset);

Kyselyn tuloksena on:

nimi        tulos     
----------  ----------
Uolevi      120       
Liisa       120

Tässä kyselyssä alikysely on SELECT MAX(tulos) FROM Tulokset, 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
  nimi, tulos, (SELECT MAX(tulos) FROM Tulokset)-tulos
FROM
  Tulokset;
nimi        tulos       (SELECT MAX(tulos) FROM Tulokset)-tulos
----------  ----------  ---------------------------------------
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(tulos)
FROM
  (SELECT * FROM Tulokset ORDER BY tulos DESC LIMIT 3);
SUM(tulos)
----------
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(tulos) FROM Tulokset ORDER BY tulos DESC LIMIT 3;
SUM(tulos)
----------
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
  nimi
FROM
  Tulokset
WHERE
  tulos IN (SELECT tulos FROM Tulokset ORDER BY tulos DESC LIMIT 3);
nimi      
----------
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
  nimi,
  tulos,
  (SELECT COUNT(*) FROM Tulokset WHERE tulos > T.tulos)
FROM
  Tulokset T;

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:

nimi        tulos       (SELECT COUNT(*) FROM Tulokset WHERE tulos > T.tulos)
----------  ----------  -----------------------------------------------------
Uolevi      120         0                                                    
Maija       80          3                                                    
Liisa       120         0                                                    
Aapeli      45          4                                                    
Kaaleppi    115         2

Koska taulu Tulokset esiintyy kahdessa roolissa alikyselyssä, pääkyselyn taululle on annettu nimi T. 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
  nimi
FROM
  Tulokset T
WHERE
  (SELECT COUNT(*) FROM Tulokset WHERE tulos < T.tulos) >= 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:

nimi      
----------
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 tutkii, palauttaako alikysely vähintään yhden rivin. Tämän avulla äskeinen kysely voidaan kirjoittaa selkeämmin:

SELECT
  nimi
FROM
  Tulokset T
WHERE
  EXISTS (SELECT * FROM Tulokset WHERE tulos < T.tulos);

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
  T.nimi
FROM
  Tuotteet T, Ostokset O
WHERE
  T.id = O.tuote_id AND O.asiakas_id = 1;
SELECT
  nimi
FROM
  Tuotteet
WHERE
  id IN (SELECT tuote_id FROM Ostokset WHERE asiakas_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:

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

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

SELECT
  A.id, SUM(B.tulos)
FROM
  Tulokset A, Tulokset B
WHERE
  B.id <= A.id
GROUP BY
  A.id;
id          SUM(B.tulos)
----------  ------------
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 haluttu tulos on 2, koska vuonna 1940 ilmestyi kaksi elokuvaa.

id          nimi        vuosi     
----------  ----------  ----------
1           Lumikki     1937      
2           Fantasia    1940      
3           Pinocchio   1940      
4           Dumbo       1941      
5           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(vuosi)) 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 Elokuvat GROUP BY vuosi;
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(c) FROM (
  SELECT COUNT(*) c FROM Elokuvat GROUP BY vuosi
);
MAX(c)    
----------
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(*) c FROM Elokuvat GROUP BY vuosi ORDER BY c DESC LIMIT 1;
c         
----------
2

Sijaluvut

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

id          nimi        tulos     
----------  ----------  ----------
1           Aapeli      45        
2           Kaaleppi    115       
3           Liisa       120       
4           Maija       80        
5           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 Tulokset WHERE tulos > T.tulos)+1 sija,
  nimi, tulos
FROM
  Tulokset T
ORDER BY
  tulos DESC, nimi;
sija        nimi        tulos     
----------  ----------  ----------
1           Liisa       120       
1           Uolevi      120       
3           Kaaleppi    115       
4           Maija       80        
5           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 Tulokset WHERE tulos > T.tulos OR
    (tulos = T.tulos AND nimi < T.nimi))+1 sija,
  nimi, tulos
FROM
  Tulokset T
ORDER BY
  tulos DESC, nimi;
sija        nimi        tulos     
----------  ----------  ----------
1           Liisa       120       
2           Uolevi      120       
3           Kaaleppi    115       
4           Maija       80        
5           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 tulos DESC) sija, nimi, tulos
FROM
  Tulokset
ORDER BY
  sija, nimi;
sija        nimi        tulos     
----------  ----------  ----------
1           Liisa       120       
1           Uolevi      120       
3           Kaaleppi    115       
4           Maija       80        
5           Aapeli      45   
SELECT
  RANK() OVER (ORDER BY tulos DESC, nimi) sija, nimi, tulos
FROM
  Tulokset
ORDER BY
  sija, nimi;
sija        nimi        tulos     
----------  ----------  ----------
1           Liisa       120       
2           Uolevi      120       
3           Kaaleppi    115       
4           Maija       80        
5           Aapeli      45

Listojen vertailu

Tarkastellaan taulua, 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          lista_id    luku     
----------  ----------  ----------
1           1           2         
2           1           4         
3           1           5         
4           2           3         
5           2           5         
6           3           2         
7           3           4         
8           3           5

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

SELECT
  A.lista_id, B.lista_id, COUNT(*)
FROM
  Listat A, Listat B
WHERE
  A.luku = B.luku
GROUP BY
  A.lista_id, B.lista_id;
lista_id    lista_id    COUNT(*)  
----------  ----------  ----------
1           1           3         
1           2           1         
1           3           3         
2           1           1         
2           2           2         
2           3           1         
3           1           3         
3           2           1         
3           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

Tietokannan käyttäminen

Python-kielen standardikirjastossa on moduuli sqlite3, jonka avulla voidaan käyttää SQLite-tietokantaa. Seuraava koodi on pohja tietokannan käyttämiselle:

import sqlite3

db = sqlite3.connect("testi.db")
db.isolation_level = None

# tietokantakomennot

Koodi luo olion db, jonka kautta voidaan käyttää tiedostossa testi.db olevaa tietokantaa. Jos tiedostoa ei ole valmiina olemassa, tiedosto luodaan ja tietokanta on aluksi tyhjä.

Koodi myös määrittelee, että isolation_level on None, mikä tarkoittaa, että kun tietokantaan tehdään muutoksia, ne tulevat voimaan välittömästi samaan tapaan kuin SQLite-tulkissa.

Komentojen suoritus

Metodi execute suorittaa halutun SQL-komennon tietokannassa. Esimerkiksi seuraavat komennot luovat taulun Tuotteet ja lisäävät sinne kolme riviä:

db.execute("""
           CREATE TABLE Tuotteet (
             id INTEGER PRIMARY KEY,
             nimi TEXT,
             hinta INTEGER
           )
           """)

db.execute("""
           INSERT INTO Tuotteet (nimi, hinta)
                       VALUES ('selleri', 5)
           """)
db.execute("""
           INSERT INTO Tuotteet (nimi, hinta)
                       VALUES ('nauris', 8)
           """)
db.execute("""
           INSERT INTO Tuotteet (nimi, hinta)
                       VALUES ('lanttu', 4)
           """)

Tässä on käytössä Pythonin """-syntaksi, jonka avulla voi määritellä monirivisen merkkijonon. Tämä syntaksi on kätevä SQL-komennoissa, jotka ovat usein pitkiä ja jotka pystyy jakamaan usealle riville tällä tavalla.

Huomaa, että SQL-komentojen lopussa ei tarvitse olla puolipistettä ;, koska on muutenkin selvää, mihin komento päättyy.

Metodilla execute voidaan myös hakea tietoa tietokannasta. Metodi fetchall antaa kyselyn tulokset listana, jossa jokaista tulostaulun riviä vastaa tuple:

tuotteet = db.execute("""
                      SELECT nimi, hinta
                      FROM Tuotteet
                      """).fetchall()
print(tuotteet)
[('selleri', 5), ('nauris', 8), ('lanttu', 4)]

Metodi fetchone puolestaan palauttaa ensimmäisen tulosrivin tuplena. Tämä metodi on erityisen hyödyllinen kyselyissä, jotka palauttavat aina yhden rivin:

hinta = db.execute("""
                   SELECT MAX(hinta)
                   FROM Tuotteet
                   """).fetchone()
print(hinta)
(8,)

Huomaa, että metodin palauttama arvo on tuple, jonka sisällä on luku, eikä sellaisenaan oleva luku. Seuraava koodi tulostaa luvun sellaisenaan:

hinta = db.execute("""
                   SELECT MAX(hinta)
                   FROM Tuotteet
                   """).fetchone()
print(hinta[0])
8

Parametrit

Seuraava koodi kysyy käyttäjältä tuotteen nimeä ja ilmoittaa sitten tuotteen hinnan tai tiedon siitä, että tuotetta ei ole tietokannassa.

nimi = input("Tuotteen nimi: ")

hinta = db.execute("""
                   SELECT hinta FROM Tuotteet WHERE nimi = ?
                   """, [nimi]).fetchone()

if hinta:
    print("Hinta on", hinta[0])
else:
    print("Ei löytynyt")

Tässä käyttäjän antama tieto yhdistetään kyselyyn parametrina: kyselyssä tiedon kohdalla on merkki ? ja sen kohdalle tuleva arvo annetaan listassa [nimi]. Esimerkiksi jos käyttäjä antaa nimen nauris, kyselystä tulee SELECT hinta FROM Tuotteet WHERE nimi = 'nauris'. Koska nimi on merkkijono, sen ympärille tulee SQL-komennossa automaattisesti '-merkit oikealla tavalla.

Jos SQL-kysely ei palauta riviä, metodi fetchone palauttaa arvon None. Tämän avulla voidaan tunnistaa tilanne, jossa tietokannassa ei ole haettua tietoa. Yllä olevassa koodissa ehto if hinta pätee silloin, kun hinta on jotain muuta kuin None eli kysely palautti rivin.

Seuraava koodi lisää uuden tuotteen tietokantaan:

nimi = input("Tuotteen nimi: ")
hinta = input("Tuotteen hinta: ")

db.execute("""
           INSERT INTO Tuotteet (nimi, hinta) VALUES (?, ?)
           """, [nimi, hinta])

Kun SQL-komennossa on useita parametreja, niiden arvot tulevat listan arvoista samassa järjestyksessä vasemmalta oikealle. Esimerkiksi jos käyttäjä antaa nimen nauris ja hinnan 5, äskeisestä komennosta tulee INSERT INTO Tuotteet (nimi, hinta) VALUES (̈́'nauris', 5).

Parametreja kannattaa käyttää aina, kun SQL-komennon osana on muuttuvaa tietoa. Parametrien etuna on, että tieto liitetään varmasti oikealla tavalla SQL-komennon osaksi. Esimerkiksi jos tuotteen nimi on Pepe's Drink, nimessä esiintyy '-merkki ja oikea tapa ilmoittaa nimi komennossa on 'Pepe\'s Drink'. Kun tieto annetaan parametrina, tämä muutos tehdään automaattisesti.

{: .note-title } SQL-injektio

_SQL-injektio_ on tietoturva-aukko, jossa pahantahtoinen käyttäjä muuttaa SQL-komennon rakennetta antamalla sopivanlaista tietoa. Tehokas tapa suojautua SQL-injektioilta on käyttää aina parametreja SQL-komennoissa, jolloin käyttäjän antama tieto lisätään komentoihin turvallisesti. Aihetta käsitellään tarkemmin kurssilla _Tietokannat ja web-ohjelmointi_.

Virheenkäsittely

Tietokannassa suoritettava komento saattaa epäonnistua. Esimerkiksi seuraava komento epäonnistuu, jos taulu Tuotteet on jo olemassa:

db.execute("""
           CREATE TABLE Tuotteet (
             id INTEGER PRIMARY KEY,
             nimi TEXT,
             hinta INTEGER
           )
           """)

Tällöin ohjelman suoritus päättyy seuraavaan virheeseen:

Traceback (most recent call last):
  File "testi.py", line 6, in <module>
    db.execute("""
sqlite3.OperationalError: table Tuotteet already exists

Virhe voidaan käsitellä myös Python-koodin puolella vaikkapa näin:

try:
    db.execute("""
               CREATE TABLE Tuotteet (
                 id INTEGER PRIMARY KEY,
                 nimi TEXT,
                 hinta INTEGER
               )
               """)
except:
    print("Taulua ei voitu luoda")

Tällöin ohjelman suoritus jatkuu eteenpäin eikä pääty virheeseen.

Lisätyn rivin id-numero

Seuraava koodi ilmoittaa tietokantaan lisätyn rivin id-numeron:

tulos = db.execute("""
                   INSERT INTO Tuotteet (nimi, hinta) 
                               VALUES ('lanttu', 4)
                   """)
print(tulos.lastrowid)

Tästä on hyötyä, jos tietokantaan lisätään tämän jälkeen muita rivejä, joka viittaavat ensin lisättyyn riviin.

Käyttöliittymä

Seuraava ohjelma toteuttaa käyttöliittymän, jonka avulla käyttäjä voi lisätä tietokantaan tuotteita, hakea tuotteen hinnan tai poistua ohjelmasta. Ohjelma olettaa, että tiedostossa testi.db on valmiina olemassa taulu Tuotteet.

import sqlite3

db = sqlite3.connect("testi.db")
db.isolation_level = None

print("1 - Lisää uusi tuote")
print("2 - Hae tuotteen hinta")
print("3 - Sulje ohjelma")

while True:
    komento = input("Anna komento: ")

    if komento == "1":
        nimi = input("Tuotteen nimi: ")
        hinta = input("Tuotteen hinta: ")

        db.execute("""
                   INSERT INTO Tuotteet (nimi, hinta) 
                               VALUES (?, ?)
                   """, [nimi, hinta])

    if komento == "2":
        nimi = input("Tuotteen nimi: ")
        hinta = db.execute("""
                           SELECT hinta FROM Tuotteet 
                                        WHERE nimi = ?
                           """, [nimi]).fetchone()

        if hinta:
            print("Hinta on", hinta[0])
        else:
            print("Ei löytynyt")

    if komento == "3":
        break

Ohjelman suoritus voi näyttää seuraavalta:

1 - Lisää uusi tuote
2 - Hae tuotteen hinta
3 - Sulje ohjelma
Anna komento: 2
Tuotteen nimi: selleri
Hinta on 5
Anna komento: 2
Tuotteen nimi: palsternakka
Ei löytynyt
Anna komento: 1
Tuotteen nimi: palsternakka
Tuotteen hinta: 9
Anna komento: 2 
Tuotteen nimi: palsternakka
Hinta on 9
Anna komento: 3

Koodin rakenne paremmaksi

Usein pidetään hyvänä, että tietokannan käsittely ja käyttöliittymän toteutus ovat toisistaan erillään ohjelmassa. Seuraava koodi toteuttaa tämän niin, että moduuli tuotteet.py käsittelee tietokantaa ja moduuli main.py on pääohjelma, joka näyttää käyttöliittymän.

tuotteet.py

```python import sqlite3

db = sqlite3.connect(“testi.db”) db.isolation_level = None

def lisaa_tuote(nimi, hinta): db.execute(""" INSERT INTO Tuotteet (nimi, hinta) VALUES (?, ?) """, [nimi, hinta])

def hae_hinta(nimi): hinta = db.execute(""" SELECT hinta FROM Tuotteet WHERE nimi = ? """, [nimi]).fetchone()

if hinta:
    return hinta[0]
else:
    return None

<p class="code-title">main.py</p>
```python
import tuotteet

print("1 - Lisää uusi tuote")
print("2 - Hae tuotteen hinta")
print("3 - Sulje ohjelma")

while True:
    komento = input("Anna komento: ")

    if komento == "1":
        nimi = input("Tuotteen nimi: ")
        hinta = input("Tuotteen hinta: ")
        
        tuotteet.lisaa_tuote(nimi, hinta)

    if komento == "2":
        nimi = input("Tuotteen nimi: ")
        hinta = tuotteet.hae_hinta(nimi)

        if hinta:
            print("Hinta on", hinta)
        else:
            print("Ei löytynyt")

    if komento == "3":
        break

Tällaisessa toteutuksessa käyttöliittymässä ei näy mitään siitä, että tiedot tallennetaan nimenomaan SQLite-tietokantaan, vaan tallennustapaa voisi periaatteessa muuttaa ilman, että käyttöliittymään tulisi mitään muutoksia.

Laajemmassa sovelluksessa olisi mielekästä jakaa tietokannan käsittely useampaan tiedostoon. Tällaisia sovelluksia tehdään myöhemmillä tietojenkäsittelytieteen kursseilla.

Mitä tehdä missäkin?

Tietokannan ja koodin puolella voi usein tehdä samantapaisia asioita. Esimerkiksi seuraavassa on kaksi tapaa etsiä kallein tuotteen hinta tietokannasta:

kallein = db.execute("""
                     SELECT MAX(hinta) FROM Tuotteet
                     """).fetchone()
hinnat = db.execute("""
                    SELECT hinta FROM Tuotteet
                    """).fetchall()
kallein = max(hinnat)

Ensimmäisessä tavassa haetaan kallein hinta tietokannan puolella SQL:n MAX-funktiolla. Toisessa tavassa haetaan tietokannasta kaikkien tuotteiden hinnat listaan ja etsitään sitten koodin puolella listan kallein hinta 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 jokaisen opettajan nimi ja kurssien määrä:

opettajat = db.execute("""
                       SELECT id, nimi FROM Opettajat
                       """).fetchall()

for opettaja in opettajat:
    maara = db.execute("""
                       SELECT COUNT(*) FROM Kurssit
                                       WHERE opettaja_id = ?
                       """, [opettaja[0]]).fetchone()
    print(opettaja[1], maara[0])

Koodi hakee ensin listaan kunkin opettajan id-numeron ja nimen. Tämän jälkeen koodi hakee silmukassa jokaisesta opettajasta erikseen niiden kurssien määrän, joita kyseinen opettaja opettaa. 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:

tiedot = db.execute("""
                    SELECT O.nimi, COUNT(*)
                    FROM Opettajat O LEFT JOIN Kurssit K ON O.id = K.opettaja_id
                    GROUP BY O.id
                    """).fetchall()

for rivi in tiedot:
    print(rivi[0], rivi[1])

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

Kuitenkaan tietokannan puolella ei kannata tehdä kaikkea, mikä on teoriassa mahdollista. Tästä esimerkkinä on seuraava koodi, joka hakee tietokannasta tuloslistan, jossa pelaajat on järjestettynä pistemäärän ja nimen mukaan. Tulostuksessa pelaajista näytetään myös sija (1, 2, 3, jne.) listalla.

lista = db.execute("""
                   SELECT nimi, pisteet
                   FROM Tulokset
                   ORDER BY pisteet DESC, nimi
                   """).fetchall()

sija = 1
for tulos in lista:
    print(sija, tulos[0], tulos[1])
    sija += 1

Tässä tapauksessa pelaajien sijat lasketaan koodin puolella muuttujan sija avulla. Olisi mahdollista laatia monimutkainen SQL-kysely, jonka tulostaulussa on myös sijat, kuten on tehty kurssin materiaalin luvun 4 lopussa. Kuitenkin parempi ratkaisu on laskea sijat koodissa yllä olevalla tavalla, koska tämän voi tehdä helposti ja tehokkaasti ja SQL-kysely säilyy yksinkertaisena.