Luku 4

Lisää SQL:stä

Olemme oppineet nyt perusasiat SQL:stä, mutta olemme käyneet läpi vain pienen osan nykyisten SQL-toteutusten ominaisuuksista. Tässä aliluvussa on joitain näytteitä SQL:n muista ominaisuuksista.

Perusteiden oppimisen jälkeen hyvä tapa perehtyä syvällisemmin SQL-kieleen on etsiä lisää tietoa käytetyn tietokannan dokumentaatiosta. Niissä on yleensä kuvattu tarkasti, mitä ominaisuuksia tietokannassa on ja miten niitä voi käyttää SQL:llä.

Taulun muuttaminen

Voimme muuttaa taulun rakennetta ALTER TABLE -komennolla. Seuraavassa esimerkissä luomme ensin taulun Tuotteet tavalliseen tapaan. Tämän jälkeen vielä lisäämme siihen yhden sarakkeen.

CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY, nimi TEXT);
ALTER TABLE Tuotteet ADD COLUMN hinta INTEGER;

Ikkunafunktiot

Ikkunafunktion avulla voimme laskea jokaiselle tulostaulun riville arvon, joka riippuu muista riveistä. Seuraavassa esimerkissä laskemme funktiolla RANK pelaajien sijoitukset järjestettynä tuloksen mukaan suurimmasta pienimpään:

SELECT nimi, tulos, RANK() OVER (ORDER BY tulos DESC) FROM Tulokset;
nimi        tulos       RANK() OVER (ORDER BY tulos DESC)
----------  ----------  ---------------------------------
Uolevi      120         1                                
Liisa       120         1                                
Kaaleppi    115         3                                
Maija       80          4                                
Aapeli      45          5       

Näkymät

Näkymä luo rajatun tavan hakea tietoa taulusta jonkin ehdon perusteella. Voimme hakea tietoa näkymästä samaan tapaan kuin taulusta, mutta haku kohdistuu kuitenkin todellisuudessa näkymän kohteena olevaan tauluun.

Esimerkiksi seuraava komento luo tauluun Elokuvat näkymän Klassikot, jonka kautta pääsemme käsiksi vuotta 1970 vanhempiin elokuviin:

CREATE VIEW Klassikot AS SELECT * FROM Elokuvat WHERE vuosi < 1970;

Voimme hakea näkymän kautta tietoa vaikkapa näin:

SELECT nimi, vuosi FROM Klassikot;
id          nimi        vuosi     
----------  ----------  ----------
1           Lumikki     1937      
2           Pinocchio   1940      
5           Bambi       1942    

Tämä tarkoittaa samaa kuin seuraava kysely suoraan taulusta:

SELECT nimi, vuosi FROM Elokuvat WHERE vuosi < 1970;

Triggerit

Triggeri aiheuttaa halutun toiminnon tietokannassa taulun muutoksen yhteydessä.

Esimerkiksi seuraava triggeri saa aikaan, että kun taulussa Tuotteet muutetaan tuotteen hintaa, tästä kirjautuu automaattisesti tieto tauluun Muutokset.

CREATE TRIGGER hinta_muuttuu AFTER UPDATE ON Tuotteet WHEN OLD.hinta <> NEW.hinta
BEGIN
INSERT INTO Muutokset(tuote_id,vanha,uusi) VALUES (NEW.id,OLD.hinta,NEW.hinta);
END;

Tämän jälkeen kun suoritetaan komennot

INSERT INTO Tuotteet(nimi,hinta) VALUES ('selleri',5);
UPDATE Tuotteet SET hinta=6 WHERE id=1;
UPDATE Tuotteet SET hinta=3 WHERE id=1;

niin tauluun Muutokset ilmestyvät seuraavat rivit:

id          tuote_id    vanha       uusi      
----------  ----------  ----------  ----------
1           1           5           6         
2           1           6           3