Tietokantakyselyt - Luento4

• Yleistä SQL:n käytöstä kyselyissä ja tiedon lisäämisessä
• Tietojen lisääminen tietokantaan
  • Esimerkkejä tietojen lisäämisestä tauluun
• Kyselyt
  • Yksinkertaisia kyselyjä yhteen tauluun
  • Ehtorajoitettuja kyselyjä yhteen tauluun
  • Useamman kentän mukaan ehtorajoitettuja kyselyjä
  • Kyselyn tietojen järjestäminen
  • Kyselyjen tekeminen useampaan tauluun
  • Kyselyn tietojen ryhmittäminen
• Taulun tietojen muuttaminen
• Taulujen ja tietojen poistaminen

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 oheiden 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.

Yleistä SQL:n käytöstä kyselyissä ja tiedon lisäämisessä

Useimmista tietokantahallintajärjestelmistä tai -sovelluksista löytyy graafinen käyttöliittymä taulujen luomiseen, tietojen lisäämiseen sekä erilaisten kyselyjen tekemiseen. Miksi SQL:ää sitten kannattaa käyttää?

• Jokainen tietokannanhallintajärjestelmä tukee SQL:ää kohtuullisen hyvin. (Pieniä eroja esim. Access)
• Tietohallintajärjestelmien ja sovelluksien omat kyselytyökalut voivat olla hyvinkin rajoittuneita.

Tietojen lisääminen tietokantaan

Tietueen tietojen eli tietorivin lisääminen tietokantaan tehdään INSERT-komennon avulla. Lauseen yleinen muoto on seuraava:

INSERT INTO Taulu (Kentta1, Kentta2, ...) 
VALUES ('Arvo1', 'Arvo2', ...);

• Yhdellä INSERT-komennolla lisätään yksi rivi (tietue) kerrallaan.
• Yleisohje: Anna lisäyksessä aina kenttien nimet ja niitä vastaavat arvot (samassa järjestyksessä).
• Kenttien nimiä (Kentta1, Kentta2) ei ole aivan pakko antaa, jos tietoja lisätään taulun "kenttäjärjestyksessä". Mitäs jos järjestys muuttuu jostakin syystä?
• Tietueen lisäämisessä voidaan jättää määrittelemättä NULL-arvoja sallivat tai oletusarvoja käyttävät kentät. Kaikkiin muihin kenttiin on annettava jokin arvo!

Esimerkkejä tietojen lisäämisestä tauluun

Esimerkissä lisätään videotietokannan Elokuva-tauluun yksi elokuva. Huomaa, että elokuvaid-kentän on oltava yksilöllinen.

INSERT INTO Elokuva (elokuvaid,nimi, julkaisuvuosi, vuokrahinta, arvio) 
VALUES (1,'What women want',2001, 3 , 5);

Esimerkissä lisätään Jasen-tauluun yksi elokuva. Huomaa, että jasenid-kentän on oltava yksilöllinen.

INSERT INTO jasen (jasenid,nimi,osoite,liittymispvm, syntymavuosi) 
VALUES (8,'Leila Leffafani','Leffatie 1','1990-01-01', 1973);

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 Taulu1, Taulu2 
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 asteriksia (*).
  • 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 nimeä ja syntymävuotta sellaisilta jäseniltä, joiden syntymavuosi on suurempi tai yhtä suuri kuin 1971. Tulosena 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 toteutetty 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 Videootti 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ä on 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. Seuraavassa esimerkistä tarkempi selitys esimerkissä käytetyistä ratkaisuista.

• 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 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;

Kyselyn tietojen ryhmittäminen

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

• 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ä.

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. Esimerkin tarkempi ja vaiheittainen toiminta selviää seuraavasta.

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;

Taulun tietojen muuttaminen

Tietojen muuttaminen tauluihin tehdään UPDATE-komennon avulla. Tietoja päivittäessä pitää muistaa seuraavat asiat.

• Muista päivitykseen WHERE-ehto!
• Jos UPDATE-lauseessa ei ole WHERE-ehtoa, niin päivitys tehdään kaikille riveille!

Seuraavassa esimerkissä muutetaan henkilön nimi toiseksi UPDATE-komennon avulla.

UPDATE Jasen 
SET nimi = 'Leila Leffahani' 
WHERE nimi = 'Leila Leffafani';

Taulujen ja tietojen poistaminen

Taulun tietueiden poistaminen onnistuu DELETE-komennolla.

Seuraavassa esimerkissä tuhotaan Jasen-taulusta henkilöt, jotka asuvat Nörttukuja 3:ssa. Esimerkkitietokannan tapauksessa tuhoaminen ei onnistu, koska viite-eheys estää sen.

• Muista poistoon WHERE-ehto!
• Jos DELETE-lauseessa ei ole WHERE-ehtoa, niin koko taulu tyhennetään!

DELETE FROM Jasen 
WHERE osoite = 'Nörttikuja 3'