SQL - tietojen lisääminen, poistaminen, päivittäminen ja kyselyt - luento 5

• Luentotaltiointi
• Kertausta
• Tietojen lisääminen tietokantaan
  • Esimerkkejä tietojen lisäämisestä tauluun
• Taulun tietojen muuttaminen
• Taulun tietojen poistaminen
• SQL-kyselyt
  • Yksinkertaisia kyselyjä yhteen tauluun
  • Ehtorajoitettuja kyselyjä yhteen tauluun

Tutustumme tietokannan tietojen muokkaamiseen SQL-kielellä sekä SQL-kyselyjen perusteisiin.

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.
• Videotietokanta COUNTER-tietotyypillä terästettynä

Kertausta

• Miksi nimi ei saa olla perusavain
  • Turhaa toistoa
  • Päivittäminen täytyy tehdä useaan paikkaan
  • Vie turhaan ylimääräistä tilaa -> myös hidastaa toimintaa
• Valmistusvaiheen perusavaimen muodostuminen (ns. Heikko kohde)
  • Perusavain muodostetaan yhdistettynä avaimena viiteavaimesta ja muista kentistä
  • Erkka ei osaa tehdä tätä vaan täytyy itse muuttaa sql-koodiin
• M-to-M-suhteet ovat sama asia kuin kaksi 1-to-M-suhdetta
  • M-to-M-suhteen voi aina jakaa 1-to-M-suhteiksi

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.
• Tietueen lisäämisessä voidaan jättää määrittelemättä NULL-arvoja sallivat, oletusarvoja käyttävät kentät ja autonumber/counter-tyyppiset itsestään muodostuvat 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);

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 haluat päivittää vain yhden tietueen tietoja niin WHERE-ehdossa on aina käytettävä perusavainta.
• 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 jasenid = 8;

Taulun tietojen poistaminen

Taulun tietueiden poistaminen onnistuu DELETE-komennolla.

Seuraavassa esimerkissä tuhotaan Jasen-taulusta henkilöt.

• Muista lisätä poistoon WHERE-ehto ja käytä ehtona perusavainta jos haluat poistaa vain yhden rivin!
• Jos DELETE-lauseessa ei ole WHERE-ehtoa, niin koko taulu tyhjennetään!

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

SQL-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').
• Numeeriset arvot annetaan 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 tekstiä sisältävissä kentissä
  • 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ä kirjainten 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%';