Tietokantakyselyt - Luento4

• Luentotaltiointi
• Kyselyt
  • Yksinkertaisia kyselyjä yhteen tauluun
  • Ehtorajoitettuja kyselyjä yhteen tauluun
  • Useamman kentän mukaan ehtorajoitettuja kyselyjä
  • Kyselyn tietojen järjestäminen
  • Kyselyjen tekeminen useampaan tauluun
  • Koostefunktiot
  • Kyselyn tietojen ryhmittely

Tällä luennollä käydään läpi tietojen lisääminen tietokannan tauluihin (relaatioihin) SQL:n avulla sekä erilaisten kyselyjen tekemistä tietokannan tiedoista.

Luennolla käytetään oheisen kuvan mukaista tietokantaa. Tietokantaa on yksinkertaistettu voimakkaasti esimerkin pitämiseksi mahdollisimman helppona. Tietokannan luomiseen ja tietojen lisäämiseen käytetyt SQL:t löydät seuraavasta.

• Esimerkkinä käytetyn videotietokannan luominen.
• Esimerkkinä käytettyjen videotietokannan tietojen lisääminen.

Kyselyt

Tietokannan tauluihin voidaan tehdään monipuolisia kyselyjä SELECT-komennon avulla. Seuraavissa esimerkeissä käydään läpi kyselyjen tekemistä aina yksinkertaisista yhteen tauluun kohdistuvasta kyselystä hieman monimutkaisempiin ja moneen tauluun kohdistuvaan kyselyyn. Seuraavassa SELECT-komennon (yksinkertaistettu) yleinen muoto. Pakollisia osia ovat ainoastaan SELECT ja FROM, kuten esimerkeistä käy ilmi.

SELECT Kentta1, Kentta2 
FROM Taulu 
WHERE Ehto;

Yksinkertaisia kyselyjä yhteen tauluun

Seuraavalla kyselyllä haetaan Elokuva-taulun kaikki taulun tiedot eli kaikkien tietueiden (rivien) kaikki kentät (sarakkeet). Tähtimerkki (*) korvaa kaikkien kenttien luettelemisen. Kyselyssä tarvitaan harvemmin kaikkia kenttiä, joten myöhemmät esimerkit ovat tavallisesti käyttökelpoisempia.

SELECT * 
FROM Elokuva;

Seuraavalla kyselyllä haetaan kaikkien elokuvien nimet eli Elokuva-taulun kaikkien rivien (tietueiden) Nimi-kentät:

SELECT nimi 
FROM Elokuva;

Seuraavalla kyselyllä haetaan kaikkien elokuvien nimet ja vuokrahinta Elokuva-taulusta.

SELECT nimi, vuokrahinta 
FROM Elokuva;

Seuraavalla kyselyllä haetaan Jasen-taulusta kaikkien jäsenten nimi ja osoitetiedot.

SELECT nimi, osoite
FROM Jasen;

Edellisen kyselyn tekeminen palauttaa näkyville taulukon, jossa oli näkyvillä nimi ja vuokrahinta sarakkeotsikot. Otsikoiden alle tuli varsinainen taulujen tieto. Haluttaessa voimme myös muuttaa tuloksessa näytettäviä sarakeotsikoita AS-sidesanan avulla. Seuraavassa esimerkissä muutamme nimi-otsikon paikalle Elokuva-otsikon ja vuokrahinta-otsikon paikalle Hinta-otsikon. Lisäksi esimerkissä on käytetty kommenttia. Kommenttirivi täytyy aloittaa tuplaviivalla (--).

-- Muutetaan hieman tuloksen otsikkotietoja 
SELECT nimi AS Elokuva, vuokrahinta AS Hinta
FROM Elokuva;

Peruskyselyillä voidaan kätevästi rajoittaa näytettäviä tietosarakkeita (kenttiä), mutta usein on tarve tehdä jonkin ehdon mukaan rajoitettuja kyselyjä tauluun.

Ehtorajoitettuja kyselyjä yhteen tauluun

Ehdon avulla voidaan kyselyssä rajoittaa tulokseen tulevia tulosrivejä (tietueita). Ehtorajoitetuissa kyselyissä pitää muistaa muutama asia.

• Pienillä ja isoilla kirjaimille on merkitystä!
• Tekstiarvot täytyy antaa heittomerkkien sisään (esim. 'tanne').
• Numeroarvot voidaan antaa ilman heittomerkkejä (esim. 2000).
• Kyselyä rajoittavan ehdon ei tarvitse kohdistua lopputulokseen tulevaan kenttään.
• Käytettävissä on vertailuoperaattorit
  • yhtäsuuruus (=)
  • pienempi kuin (<)
  • suurempi kuin (>)
  • pienempi tai yhtä suuri kuin (<=)
  • suurempi tai yhtä suuri kuin (>=)
  • erisuuruus (<>)
• NOT-operaattorilla ehdosta voidaan tehdä päinvastainen.
• LIKE-operaattorilla voidaan tehdä hakua osittain täsmäävillä arvoilla.
  • Prosenttimerkki (%) vastaa nollaa tai useampaa merkkiä. Accesissa käytetään asteriskia (*).
  • Alaviiva (_) tarkoittaa mitä tahansa yksittäistä merkkiä. Accesissa käytetään kysymysmerkkiä (?).
• NULL-arvoja voidaan etsiä IS NULL ja IS NOT NULL-määreillä. NULL-arvoja kannattaa kuitenkin vältellä viimeiseen asti.

Seuraavassa esimerkissä rajoitetaan haettavien tietueiden (rivien) määrää WHERE-ehdolla. Näkyville halutaan vain ne elokuvat, joiden vuokrahinta on tasan 5 (euroa). Ehdossa määritellään sarakkeen nimi (vuokrahinta) ja siihen liittyvä ehto (= 5).

SELECT nimi , vuokrahinta
FROM Elokuva
WHERE vuokrahinta = 5;

Seuraavassa esimerkissä hakuehtona mainittua kenttää (vuokrahinta) ei esiinny haettavissa kentissä (nimi), mutta haku toimii siitäkin huolimatta. Ominaisuus on kätevä, koska aina ei haluta lopputulokseen rajoittavia kenttiä. Esimerkissä halutaan pelkästään elokuvien nimet, joiden vuokrahinta on 5 (euroa).

SELECT nimi 
FROM Elokuva
WHERE vuokrahinta = 5;

Seuraavassa esimerkissä haetaan jäsenten nimeä ja syntymävuotta sellaisilta jäseniltä, joiden syntymavuosi on suurempi kuin 1971. Tuloksena tulee vain kolme jäsentä seitsemästä.

SELECT nimi, syntymavuosi 
FROM Jasen
WHERE syntymavuosi > 1971;

Seuraavassa esimerkissä haetaan jäsenten nimi ja syntymävuosi sellaisilta jäseniltä, joiden syntymavuosi on suurempi tai yhtä suuri kuin 1971. Tuloksena saadaan neljän jäsenen tiedot, koska edellisestä esimerkistä poiketen yksi jäsenistä oli syntynyt vuonna 1971.

SELECT nimi, syntymavuosi 
FROM Jasen
WHERE syntymavuosi >= 1970;

Seuraavassa esimerkissä haetaan niiden jäsenten nimi ja osoitetietoja Jasen-taulusta, jotka asuvat Nörttikuja 3:ssa. Huomaa, että kirjaiten koolla on merkitystä, joten nörttikuja 3 on eri asia kuin Nörttikuja 3. Vastaukseen tuli kaksi jäsentä.

SELECT nimi, osoite
FROM Jasen
WHERE osoite = 'Nörttikuja 3';

Seuraavassa esimerkissä haetaan niiden jäsenten nimi ja osoitetietoja Jasen-taulusta, jotka eivät asu Nörttikuja 3:ssa. Vastaukseen tuli viisi jäsentä.

SELECT nimi, osoite
FROM Jasen
WHERE osoite <> 'Nörttikuja 3';

Seuraava esimerkki antaa saman vastauksen kuin edellinen esimerkki. Nyt kysely on toteutettu NOT-operaattorilla.

SELECT nimi, osoite
FROM Jasen
WHERE NOT osoite = 'Nörttikuja 3';

Seuraavassa esimerkissä käytetään LIKE-operaattoria rajoittamaan hakua. Esimerkissä halutaan ainoastaan sellaisten jäsenten nimet ja osoitteet, joiden osoite alkaa sanalla Nörtti. Vastaukseen tule jäsenet (3 kpl), jotka asuvat Nörttikujalla tai Nörttikadulla.

SELECT nimi, osoite
FROM Jasen
WHERE osoite LIKE 'Nörtti%';

Seuraavassa esimerkissä halutaan näkyville sellaisten jäsenten nimi- ja osoitetiedot, jotka asuvat jollakin tiellä (tai joiden osoitteessa esiintyy sana tie, esim. kotitienkatu). Vastaukseen tulee kolmen jäsenen tiedot.

SELECT nimi, osoite
FROM Jasen
WHERE osoite LIKE '%tie%';

Useamman kentän mukaan ehtorajoitettuja kyselyjä

Taulun tietoja voidaan rajoittaa useammankin ehdon mukaan. Ehtojen liittäminen toisiinsa tapahtuu AND- ja OR-operaattoreiden mukaan.

• AND-operaattorilla yhdistetyt ehdot rajaavat taulun tietoja yhdessä. Tällöin ensimmäisen ehdon rajoittamia rivejä rajoitetaan lisää toisella ehdolla. AND-operaattorilla yhdistettyjen ehtojen täytyy toteutua samalla rivillä.
• OR-operaattorilla yhdistettyihin ehtoihin rajaavat taulun tietoja erikseen. Tällöin tuloksena tulee ensimmäisen ehdon täyttävät rivit, joihin lisätään toisen ehdon täyttämät rivit. Ehdot voivat toteutua samoilla riveillä, mutta rivejä ei näytetä kahta kertaa. OR-operaattorilla yhdistettyjen ehtojen EI tarvitse toteutua samalla rivillä.

Seuraavassa esimerkissä haetaan Elokuva-taulusta nimi- ja julkaisuvuositietoja. Ehtoa rajoitetaan ensin julkaisuvuoden mukaan (julkaisuvuosi > 2000) ja jäljelle jääviä rajoitetaan arvion mukaan (arvio=10). Ensimmäisen rajoituksen mukaan elokuvia on seitsemän kappaletta ja toisen toisen rajoituksen mukaisia näistä seitsemästä on vain kolme. Tuloksena näytetään kolmen elokuvan tiedot.

SELECT nimi, julkaisuvuosi
FROM Elokuva
WHERE julkaisuvuosi > 2000 AND arvio = 10;

Seuraavassa esimerkissä haetaan sellaisia elokuvia, joiden julkaisuvuosi on joko 2002 tai 2001. Tulokseen tulee yhteensä 7 elokuvaa.

SELECT nimi, julkaisuvuosi
FROM Elokuva
WHERE julkaisuvuosi = 2002 OR julkaisuvuosi = 2001;

Seuraavassa esimerkissä rajoitetaan elokuvia julkaisuvuoden ja arvion mukaan. Mukaan tulokseen tulee neljä elokuvaa, jotka kaikki ovat julkaistu vuoden 2000 jälkeen (>2000) ja joista osan arvio on 10 ja osan 8. Ehtojen liittämisjärjestyksen ilmoittamiseen kannattaa käyttää sulkeita, kuten esimerkissä.

SELECT nimi, julkaisuvuosi
FROM Elokuva
WHERE julkaisuvuosi > 2000 AND (arvio = 10 OR arvio = 8);

Seuraavassa esimerkissä haetaan niiden elokuvien tiedot, joiden arvio on suurempi tai yhtäsuuri kuin 7 ja pienempi tai yhtäsuuri kuin 9 (eli 7-9). Vastaukseksi tulee neljä elokuvaa.

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio >=7 AND arvio <= 9;

Edellinen esimerkki voidaan ilmaista myös BETWEEN-määreen avulla seuraavasti.

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio BETWEEN 7 AND 9;

Seuraavassa esimerkissä haetaan niiden elokuvien tiedot, joiden arvio on 7 tai 9. Vastaukseen tulee kaksi elokuvaa.

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio = 7 OR arvio = 9;

Edellinen esimerkki voidaan myös ilmaista IN-määreen avulla.

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio IN (7 ,9);

Kyselyn tietojen järjestäminen

Kyselyn lopputulokseen tulevia tietoja voidaan myös haluttaessa järjestää. Järjestäminen onnnistuu lisäämällä ORDER BY määrityksen kyselyn loppuun. Jos

• Oletuksena tiedot järjestetään pienimmästä suurimpaan eli nousevaan järjestykseen.
• ASC-määritys järjestää tiedot pienimmästä suurimpaan.
• DESC-määritys järjestää tiedot suurimmasta pienimpään eli laskevaan järjestykseen.

SELECT Kentta1, Kentta2 
FROM Taulu1, Taulu2 
WHERE Ehto
ORDER BY Kentta1 ASC;

Seuraavassa kyselyssä haetaan kaikkien jäsenten nimi järjestettynä laskevaan järjestykseen (DESC). Tällöin Ville Vidiootti on ensimmäisenä ja Leila Leffafani viimeisenä.

SELECT nimi
FROM jasen
ORDER BY nimi DESC;

Seuraavassa kyselyssä haetaan elokuvan nimi ja julkaisuvuosi Elokuva-taulusta ja ne järjestettään julkaisuvuoden mukaan vanhimmasta uusimpaan.

SELECT nimi, julkaisuvuosi
FROM Elokuva
ORDER BY julkaisuvuosi ASC;

Edellisestä kyselystä poiketen seuraavassa järjestetään elokuvat ensin julkaisuvuoden mukaan ja sen jälkeen vielä nimen mukaan.

SELECT nimi, julkaisuvuosi
FROM Elokuva
ORDER BY julkaisuvuosi ASC, nimi ASC;

Kyselyjen tekeminen useampaan tauluun

Usein on tarpeellista tehdä kyselyjä useampaan tauluun. Esimerkiksi videovuokrausrekisterin yhteydessä halutaan selvittää mitä elokuvia kukin on vuokrannut. Koska tiedot ovat useammassa taulussa, niin taulut joudutaan jollakin tavalla "yhdistämään" kyselyssä toisiinsa. Taulujen yhdistäminen tapahtuu WHERE-lausetta käyttäen. Tauluissa on toisensa yhdistäviä kenttiä, jotka näkee havainnollisesti luennon alussa olevasta taulu(relaatio)kuvasta.

Seuraavan esimerkin haku hakee kaikki tiedot sekä Elokuva että Jasen-taulusta. Tauluja ei ole kyselyssä yhdistetty mitenkään toisiinsa, joten vastauksia tulee 1155 kappaletta (15 vuokrausta * 11 elokuvaa * 7 henkiloa). Vastaus ei ole sellainen mitä haluttiin

Liittäminen tekemättä!

SELECT Jasen.nimi, Elokuva.nimi
FROM Elokuva, Jasen, Vuokraus;

Seuraavassa kyselyssä liitetään taulut toisiinsa. Elokuvataulun elokuvaID esiintyy luonnollisesti myös vuokraustaulussa. Vastaavasti Jasen-taulun jasenID esiintyy myös Vuokraus-taulussa. Ehtojen pitää olla yhtä aikaa voimassa, joten ne on yhdistetty AND-operaattorilla.

• Taulunimeä on toistettu SELECTin kentissä, koska nimet ovat samoja. Nimiä ei voi muuten erottaa toisistaan!
• Taulunimeä on toistettu myös WHERE-ehdoissa, koska eri taulun kenttiä ei voitu muuten erottaa toisistaan.
• Ensimmäisellä WHERE-ehdolla liitetään elokuva ja vuokraus-taulut toisiinsa.
• Toisella WHERE-ehdolla liitetään vuokraus- ja jasen-taulut toisiinsa.
• Tällä tavoin saatiin "yhteys" Elokuva- ja Jasen-taulun välillä.
• ORDER BY -järjestää tuloksen jasenten nimien mukaan nousevaksi.

SELECT Jasen.nimi, Elokuva.nimi
FROM Elokuva, Jasen, Vuokraus 
WHERE Elokuva.elokuvaID = Vuokraus.elokuvaID 
AND 
Jasen.jasenID = Vuokraus.JasenID
ORDER BY Jasen.nimi ASC;

Seuraava esimerkki toimii täsmälleen samoin kuin edellinenkin. Nyt taulujen nimistä on tehty käyttökelpoiset lyhenteet AS-sidesanan avulla (esim. Vuokraus AS v), jolloin koko taulun nimeä ei tarvitse toistaa enää uudelleen. Pelkkä lyhenne riittää yksilöimään taulun.

SELECT j.nimi, e.nimi
FROM Elokuva AS e, Jasen AS j, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID 
AND 
j.jasenID = v.jasenID
ORDER BY j.nimi ASC;

Koostefunktiot

Koostefunktioilla voidaan laskea yksinkertaisia yhteenvetoja taulujen tiedoista

• MAX antaa kentän maksimiarvon
• MIN antaa kentän minimiarvon
• SUM antaa kenttien summan
• AVG antaa kenttien keskiarvon
• COUNT antaa kenttien lukumäärän. COUNT(*) antaa rivien lukumäärän

SELECT MAX(vuokrahinta)
FROM Elokuva

SELECT MIN(vuokrahinta)
FROM Elokuva

SELECT SUM(vuokrahinta), AVG(vuokrahinta)
FROM Elokuva

SELECT COUNT(*)
FROM Elokuva

SELECT COUNT(arvio)
FROM Elokuva

Kyselyn tietojen ryhmittely

Kyselyn tietojen ryhmittely tulee tarpeelliseksi esimerkiksi erilaisten koostefunktioiden käytön yhteydessä. Tällöin ryhmittelyä tarvitaan kertomaan ryhmä, jonka suhteen koostefunktiota käytetään.

Ryhmittelyä avaa seuraava esimerkki

• Kyselyn rakentaminen kannattaa aloittaa ilman koostefunktiota. Jos tulokseen tulee toistuvia tyyppejä, nimiä tms. niin nämä ovat järkeviä kenttiä ryhmittelyä varten.
• GROUP BY -määrettä ei pidä käyttää ellei käytä koostefunktioita.
• Kaikki tavalliset kentät (ei koostefunktioissa käytettävät) pitää luetella GROUP BY-määreessä.
• COUNT-koostefunktio sopii määrien laskemiseen, mutta muita koostefunktioita varten tarvitaan numeerinen kenttä.

SELECT Kentta1, SUM(Kentta2) 
FROM Taulu1, Taulu2 
WHERE Ehto
GROUP BY Kentta1
HAVING  Lisaehto
ORDER BY Kentta1 ASC;

Seuraavassa kyselyssä lasketaan vuokratuista elokuvista saatu tuotto. Ryhmittelyä täytyy käyttää kyselyssä, koska sillä määritellään minkä suhteen summa laskentaa. Esimerkissä käytetään SUM-koostefunktiota.

SELECT e.nimi, SUM(e.vuokrahinta)
FROM Elokuva AS e, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID 
GROUP BY e.nimi
ORDER BY e.nimi ASC;

Seuraavalla kyselyllä lasketaan vuokratuista videoista saatu tuotto pienen lisäehdon kanssa. HAVING-lauseella halutaan rajoittaa näytettäväksi ainoastaan ne videot, jotka on vuokrattu enemmän kuin yhden kerran.

1. WHERE-ehdolla rajoitetaan rivien määrää.
2. GROUP BY -määreellä ryhmitellään rivit tietyn ehdon perusteella. Tämä mahdollistaa koostefunktioiden käytön ryhmän tietojen laskemiseen.
3. HAVING-ehdolla voidaan edelleen rajata ryhmien näyttämistä (kohdistuu siis ryhmässä oleviin riveihin).

SELECT e.nimi, SUM(e.vuokrahinta)
FROM Elokuva AS e, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID 
GROUP BY e.nimi
HAVING COUNT(e.nimi)> 1
ORDER BY e.nimi ASC;

Seuraavalla kyselyllä lasketaan videoista saatuja tuloja pienen lisäehdon avulla. Kyselyn tuloksena halutaan ainoastaan ne videot, jotka ovat tuottaneet viisi (euroa) tai enemmän.

SELECT e.nimi, SUM(e.vuokrahinta)
FROM Elokuva AS e, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID 
GROUP BY e.nimi
HAVING SUM(e.vuokrahinta)>= 5
ORDER BY e.nimi ASC;

Järjestelyehtoja voi olla useampiakin. Tällöin ne erotellaan pilkuilla GROUP BY-ehdossa. Vähintään SELECT-lauseessa olevat kentät, poislukien koostefunktiokentät, täytyy merkitä GROUP BY:hin. GROUP BY:ssa voi olla toki muitakin kenttiä mitä ei ole SELECT-lauseessa. Ryhmittely tapahtuu siinä järjestyksessä missä kentät on merkitty GROUP BY-ehtoon.