Osa 1

SQL-kieli ja tiedon hakeminen yhdestä tietokantataulusta

Tällä kurssilla keskitytään Structured Query Language -kieleen. Structured Query Language (jatkossa SQL) on 1980-luvulla standardoitu kieli tietokantakyselyiden tekemiseen. SQL-kielen avulla voidaan tiedon hakemisen lisäksi määritellä tallennettavan tiedon muoto, luoda ja muokata tietokantatauluja, lisätä tietoa tietokantatauluihin, ja muokata tietokantatauluissa olevaa tietoa. Merkittävä osa tällä hetkellä käytössä olevista tietokannanhallintajärjestelmistä tukee SQL-kielellä tehtyjen kyselyiden käyttämistä tietokannanhallintajärjestelmässä olevien tietokantojen ja tietokantataulujen käsittelyyn.

Vuosien mittaan SQL-kielestä on julkaistu useita versioita, joista viimeisin on vuodelta 2016. Tietokannanhallintajärjestelmät ja niiden eri versiot noudattavat SQL-kieltä vaihtelevasti. Yhtä tietokannanhallintajärjestelmää varten luodut kyselyt eivät ole aina suoraan siirrettävissä toiseen tietokannanhallintajärjestelmään. On siis syytä huomioida että tietokannanhallintajärjestelmästä toiseen siirryttäessä joudutaan usein tekemään SQL-kyselyihin (pieniä) muutoksia. Tyypillisimpiä tietotyyppejä, joiden käsittelytapa vaihtelee eri tietokannanhallintajärjestelmien välillä ovat päivämäärät. Vaikka tämä on hyvä tiedostaa, erot järjestelmien välillä ovat onneksi vähentyneet ajan myötä.

Tietokantataulut ja avaimet

Muistamme johdannosta, että tietokannanhallintajärjestelmät hallinnoivat tietokantoja, jotka sisältävät tietokantatauluja. Tietokantatauluja voidaan ajatella taulukoina, joilla on sarakeotsikot. Jokainen rivi taulukossa kuvaa yhtä tietokantataulun kuvaaman asian ilmentymää.

Tietokantatauluissa voi olla avaimia. Avaimet ovat uniikkeja tunnisteita tietokantataulun riveille eli tietokantataulussa oleville ilmentymille. Pääavain liittyy juuri käsiteltävän taulun ilmentymään ja viiteavain viittaa jossain toisessa taulussa olevaan pääavaimeen.

Tiedon hakeminen yhdestä tietokantataulusta

Tiedon hakeminen tietokantataulusta onnistuu SELECT-lauseella. Avainsanaa SELECT seuraa haettavat sarakkeet, avainsana FROM, ja tietokantataulun nimi. Jokainen kysely päätetään puolipisteeseen.

SELECT sarakkeen_nimi FROM Taulun_nimi;

Oletetaan, että käytössämme on tietokantataulu Henkilo, johon on määritelty sarakkeet syntymävuosi ja nimi. Kuten ohjelmoinnissa luokkien ja muuttujien tapauksessa, tietokantataulujen ja sarakkeiden nimissä tyypillisesti vältetään ääkkösiä, koska ne voivat johtaa yllättäviin merkistöongelmiin. Tähän tauluun ei ole määritelty pääavaimia tai viiteavaimia.

Taulu Henkilo, jossa on attribuutit syntymavuosi ja nimi.  

Tietokantataulu Henkilo sisältää tapauksessamme neljä henkilöä: Pihla (s. 1997), Joni (s. 1993), Raymond (s. 1947) ja Edgar (s. 1923).

syntymavuosinimi
1997Pihla
1993Joni
1947Raymond
1923Edgar

Jokaisen nimen hakeminen ja listaaminen tapahtuu kyselyllä SELECT nimi FROM Henkilo.

SELECT nimi FROM Henkilo;

Kyselyn tulostaa seuraavanlaisen taulun.

nimi
Pihla
Joni
Raymond
Edgar

Mikäli haluamme listata jokaisen tietokantatauluun Henkilo tallennetun henkilön nimen lisäksi syntymävuoden, kyselyn sarakkeisiin tulee lisätä (pilkulla erotettuna) uuden sarakkeen nimi, eli tässä tapauksessa syntymavuosi.

SELECT nimi, syntymavuosi FROM Henkilo;

Kyselyn tuottamassa taulussa sarakkeet ovat siinä järjestyksessä, mihin ne on kyselyssä määritelty. Yllä olevan kyselyn tuottama taulu sisältää sarakkeet nimi ja syntymävuosi, joista nimi tulostuu ensin.

Kysely voi pyytää saman sarakkeen myös useampaan kertaan. Esimerkiksi kysely SELECT syntymavuosi, nimi, syntymavuosi FROM Henkilo tuottaa seuraavanlaisen taulun.

syntymavuosinimisyntymavuosi
1997Pihla1997
1993Joni1993
1947Raymond1947
1923Edgar1923

SQL-kieli tarjoaa tuen laskujen tekemiseen. Mikäli syntymävuosi on tallennettu numerona (palaamme tallennusmuotoon kun opimme luomaan tietokantatauluja!), kunkin henkilön iän saa selville erottamalla syntymävuoden nykyvuodesta.

Esimerkiksi henkilön syntymävuoden, nimen, ja iän — vuonna 2019 — saa selville seuraavalla kyselyllä.

SELECT syntymavuosi, nimi, 2019 - syntymavuosi FROM Henkilo;
syntymavuosinimi2019 - syntymavuosi
1997Pihla22
1993Joni26
1947Raymond72
1923Edgar96

Kun tarkastelemme yllä olevaa tulosta, huomaamme että sarake 2019-syntymavuosi on vähän hölmösti nimetty.

Kyselyn tuottamassa vastauksessa käytetään oletuksena sarakkeen niminä kyselyssä annettuja sarakkeita. Sarakkeet voi halutessaan nimetä myös uudestaan — tämä onnistuu sarakkeen nimen jälkeen annettavalla AS-operaatiolla. Esimerkiksi edellisen kyselyn tuloksessa sarakkeen 2019-syntymavuosi nimeksi voidaan vaihtaa ika seuraavalla tavalla.

SELECT nimi AS henkilo, 2019 - syntymavuosi AS ika FROM Henkilo;
henkiloika
Pihla22
Joni26
Raymond72
Edgar96

Sarakkeita ei ole aina pakko määritellä SELECT-kyselyssä. Mikäli kyselyssä halutaan listata kaikki sarakkeet, voidaan SELECT-komentoa seuraava sarakelistaus korvata tähtimerkillä *.

SELECT * FROM Henkilo;

Kyselyn tulosten rajaaminen

Tarkastellaan seuraavaksi kyselyn tulosten rajaamista. Kyselyn tulosten rajausta määrittävät ehdot lisätään kyselyssä määritellyn taulun (tai määriteltyjen taulujen) jälkeen asetettavan avainsanan WHERE jälkeen. Yhdestä taulusta tietoa hakeva kysely, joka sisältää rajausehdon, näyttää seuraavalta.

SELECT sarake FROM Taulu WHERE rajausehto;

Esimerkiksi kaikki henkilöt, jotka ovat syntyneet ennen vuotta 1950, saa listattua seuraavalla kyselyllä.

SELECT * FROM Henkilo WHERE syntymavuosi < 1950;

Vastaavasti, mikäli kyselyn tuloksena haluaa vain henkilöiden nimet, vaihdetaan kaikki sarakkeet valitsevan *-merkin paikalle sarake nimi.

SELECT nimi FROM Henkilo WHERE syntymavuosi < 1950;

Kyselyissä toimivat kaikki ohjelmointikielistä tutuhkot ehdot:

  • Yhtäsuuri kuin =. Esimerkiksi SELECT * FROM Henkilo WHERE syntymavuosi = 2000 hakee vuonna 2000 syntyneet henkilöt.
  • Pienempi kuin <. Esimerkiksi SELECT * FROM Henkilo WHERE syntymavuosi < 2000 hakee ennen vuotta 2000 syntyneet henkilöt.
  • Suurempi kuin >. Esimerkiksi SELECT * FROM Henkilo WHERE syntymavuosi > 2000 hakee vuoden 2000 jälkeen syntyneet henkilöt.
  • Pienempi tai yhtäsuuri kuin <=. Esimerkiksi SELECT * FROM Henkilo WHERE syntymavuosi <= 2000 hakee vuonna 2000 ja sitä ennen syntyneet henkilöt.
  • Suurempi tai yhtäsuuri kuin >=. Esimerkiksi SELECT * FROM Henkilo WHERE syntymavuosi >= 2000 hakee vuonna 2000 ja sen jälkeen syntyneet henkilöt.
  • Eri kuin <>. Esimerkiksi SELECT * FROM Henkilo WHERE syntymavuosi <> 2000 henkilöt, jotka eivät ole syntyneet vuonna 2000.

Mikäli sarakkeen arvot ovat merkkijonoja, kuten nimi, täsmällisessä haussa käytetään =-operaatiota. Jos merkkijonosta haluaa hakea vain osaa, kyselyssä käytetään LIKE-operaatiota. Merkkijonot tulee merkitä kyselyyn hipsuilla — jotkut tietokannanhallintajärjestelmät odottavat yksittäisiä hipsuja ' kun taas jotkut odottavat tuplahipsuja ". Materiaalissa oletetaan, että käytössä on yksittäiset hipsut.

Henkilöt, joiden nimi on Joni, saa haettua seuraavalla kyselyllä.

SELECT * FROM Henkilo WHERE nimi = 'Joni';

Mikäli halutaan etsiä vaikkapa vain henkilöitä, joiden nimessä esiintyy kirjain 'a', kirjoitetaan kysely LIKE operaation avulla. Prosenttimerkillä ilmaistaan, että merkkijono voi sisältää mitä tahansa merkkejä. Henkilöt, joiden nimessä esiintyy 'a' saadaan selville seuraavalla kyselyllä.

SELECT * FROM Henkilo WHERE nimi LIKE '%a%';

Prosenttimerkkiä ei tarvitse käyttää haettavan merkin tai merkkijonon kummallakin puolella. Esimerkiksi kaikki henkilöt, joiden nimi päättyy kirjaimeen 'a' saa selville seuraavalla kyselyllä.

SELECT * FROM Henkilo WHERE nimi LIKE '%a';

Vastaavasti seuraava kysely hakee kaikki henkilöt, joiden nimi alkaa merkkijonolla 'abc'.

SELECT * FROM Henkilo WHERE nimi LIKE 'abc%';

Loogiset operaatiot

Rajausehtoihin voi lisätä loogisia operaatioita. Seuraavat kolme operaatiota ovat tässä vaiheessa kurssia meille oleellisimmat:

  • Operaatio 'ja' eli AND. Esimerkiksi kysely SELECT * FROM Henkilo WHERE nimi = 'Ted' AND syntymavuosi = 1920 listaa henkilöt, joiden nimi on 'Ted' ja joiden syntymävuosi on 1920.
  • Operaatio 'tai' eli OR. Esimerkiksi kysely SELECT * FROM Henkilo WHERE nimi = 'Matti' OR nimi = 'Maija' listaisi kaikki ne henkilöt, joiden nimi on Matti tai Maija.
  • Operaatio 'ei' eli NOT. Esimerkiksi kysely SELECT * FROM Henkilo WHERE nimi NOT LIKE '%a%' listaa henkilöt, joiden nimessä ei esiinny kirjainta 'a'.

Loogisia operaatioita voi lisätä kyselyyn käytännössä rajattomasti. Ehtojen suoritusjärjestystä voi ohjata suluilla. Esimerkiksi kysely SELECT * FROM Opintosuoritus WHERE (kurssi = 'Ohpe' OR kurssi = 'Ohja') AND arvosana = 3 listaa kaikki ne opintosuoritukset, joissa kurssina on joko Ohpe tai Ohja, ja arvosanana on 3.

Sulut toimivat samalla tavalla kuin ohjelmointikursseilla. Sulut ovat täälläkin tärkeät — esimerkiksi edellä kuvatun kyselyn tulos muuttuu merkittävästi, mikäli se kirjoitetaan ilman sulkuja SELECT * FROM Opintosuoritus WHERE kurssi = 'Ohpe' OR kurssi = 'Ohja' AND arvosana = 3. Tämä kysely hakisi kaikki ne opintosuoritukset, joissa kurssi on Ohpe, sekä kaikki ne opintosuoritukset, joissa kurssi on Ohja ja arvosana on 3.

Tulosten järjestäminen

Tietokantakyselyn tulosten järjestäminen tapahtuu kyselyn loppuun lisättävällä ORDER BY-komennolla, jota seuraa sarakkeet, joiden mukaan tulokset järjestetään. Mikäli sarakkeita on useampi, erotellaan ne toisistaan pilkulla. Tulokset järjestetään oletuksena nousevaan järjestykseen luonnollista järjestystä noudattaen, eli "pienimmät arvot tulevat ensin".

Esimerkkitaulumme Henkilo tulostuu kyselyn SELECT syntymavuosi, nimi FROM Henkilo ORDER BY syntymavuosi seurauksena siten, että tulokset on järjestetty syntymävuoden perusteella pienin ensin.

syntymavuosinimi
1923Edgar
1947Raymond
1993Joni
1997Pihla

Kyselyn tulokset voi järjestää vastaavasti nimen perusteella. Kysely SELECT syntymavuosi, nimi FROM Henkilo ORDER BY nimi tuottaisi seuraavanlaisen tulostaulun.

syntymavuosinimi
1923Edgar
1993Joni
1997Pihla
1947Raymond

Tulosten järjestys voi olla joko nouseva (ascending eli ASC — oletus) tai laskeva (descending eli DESC). Mikäli järjestyksen haluaa määrätä, tulee se antaa sarakkeen (tai sarakkeiden) jälkeen. Esimerkiksi henkilöiden hakeminen nimien perusteella laskevaan järjestykseen järjestettynä onnistuisi kyselyllä SELECT syntymavuosi, nimi FROM Henkilo ORDER BY nimi DESC. Tulos olisi tällöin seuraava.

syntymavuosinimi
1947Raymond
1997Pihla
1993Joni
1923Edgar
Pääsit aliluvun loppuun! Jatka tästä seuraavaan osaan: