2. SQL-kielen perusteet

Peruskomennot

Tässä luvussa tutustumme tavallisimpiin SQL-komentoihin, joiden avulla voidaan lisätä, hakea, muuttaa ja poistaa tietokannan sisältöä. Nämä komennot muodostavat perustan tietokannan käyttämiselle.

Taulun luonti

Komento CREATE TABLE luo taulun, jossa on halutut sarakkeet. Esimerkiksi seuraava komento luo taulun Tuotteet, jossa on kolme saraketta:

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

Voimme nimetä taulun ja sarakkeet haluamallamme tavalla. Tällä kurssilla käytäntönä on, että kirjoitamme taulun nimen suurella alkukirjaimella ja monikkomuotoisena. Sarakkeiden nimet puolestaan kirjoitamme pienellä alkukirjaimella.

Jokaisesta sarakkeesta ilmoitetaan nimen lisäksi tyyppi. Tässä taulussa sarakkeet id ja hinta ovat kokonaislukuja (INTEGER) ja sarake nimi on merkkijono (TEXT). Sarake id on lisäksi taulun pääavain (PRIMARY KEY), mikä tarkoittaa, että se yksilöi jokaisen taulun rivin ja voimme viitata sen avulla kätevästi mihin tahansa riviin.

Pääavain

Tietokannan taulun pääavain (primary key) on jokin sarake (tai sarakkeiden yhdistelmä), joka yksilöi taulun jokaisen rivin. Millään kahdella taulun rivillä ei voi olla samaa arvoa pääavaimessa. Käytännössä hyvin tavallinen valinta pääavaimeksi on kokonaislukumuotoinen id-numero.

Usein haluamme lisäksi, että id-numerolla on juokseva numerointi. Tämä tarkoittaa, että kun tauluun lisätään rivejä, ensimmäinen rivi saa automaattisesti id-numeron 1, toinen rivi saa id-numeron 2, jne. Juoksevan numeroinnin toteuttaminen riippuu tietokannasta. Esimerkiksi SQLite-tietokannassa INTEGER PRIMARY KEY -tyyppinen sarake saa automaattisesti juoksevan numeroinnin.

Tiedon lisääminen

Komento INSERT lisää uuden rivin tauluun. Esimerkiksi seuraava komento lisää rivin äsken luomaamme tauluun Tuotteet:

INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);

Tässä annamme arvot lisättävän rivin sarakkeille nimi ja hinta. Kun sarakkeessa id on juokseva numerointi, se saa automaattisesti arvon 1, kun kyseessä on taulun ensimmäinen rivi. Niinpä tauluun ilmestyy seuraava rivi:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         

Jos emme anna arvoa jollekin sarakkeelle, se saa oletusarvon. Tavallisessa sarakkeessa oletusarvo on NULL, mikä tarkoittaa tiedon puuttumista. Esimerkiksi seuraavassa komennossa emme anna arvoa sarakkeelle hinta:

INSERT INTO Tuotteet (nimi) VALUES ('retiisi');

Tällöin tauluun ilmestyy rivi, jossa hinta on NULL (eli hinta puuttuu):

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     NULL

Esimerkkitaulu

Oletamme tämän osion tulevissa esimerkeissä, että olemme lisänneet tauluun Tuotteet seuraavat viisi riviä:

INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);

Taulun sisältö on siis seuraavanlainen:

id          nimi        hinta     
----------  ----------  ----------
         retiisi     7         
         porkkana    5         
         nauris      4         
         lanttu      8         
         selleri     4         

Tiedon hakeminen

Komento SELECT suorittaa kyselyn (query) eli hakee tietoa taulusta. Yksinkertaisin tapa tehdä kysely on hakea kaikki tiedot taulusta:

SELECT * FROM Tuotteet;

Tässä tapauksessa kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
         retiisi     7         
         porkkana    5         
         nauris      4         
         lanttu      8         
         selleri     4         

Kyselyssä tähti * ilmaisee, että haluamme hakea kaikki sarakkeet. Kuitenkin voimme myös hakea vain osan sarakkeista antamalla sarakkeiden nimet. Esimerkiksi seuraava kysely hakee vain tuotteiden nimet:

SELECT nimi FROM Tuotteet;

Kyselyn tulos on seuraava:

nimi      
----------
retiisi    
porkkana             
nauris               
lanttu               
selleri              

Seuraava kysely puolestaan hakee tuotteiden nimet ja hinnat:

SELECT nimi, hinta FROM Tuotteet;

Nyt kyselyn tulos muuttuu näin:

nimi        hinta     
----------  ----------
retiisi     7         
porkkana    5         
nauris      4         
lanttu      8         
selleri     4         

Kyselyn tuloksena olevat rivit muodostavat taulun, jota kutsutaan nimellä tulostaulu (result table). Sen sarakkeet ja rivit riippuvat kyselyn sisällöstä. Esimerkiksi äskeinen kysely loi tulostaulun, jossa on kaksi saraketta ja viisi riviä.

Tietokannan käsittelyssä esiintyy siis kahdenlaisia tauluja: tietokannassa kiinteästi olevia tauluja, joihin on tallennettu tietokannan sisältö, sekä kyselyjen muodostamia väliaikaisia tulostauluja, joiden tiedot on koostettu kiinteistä tauluista.

Hakuehto

Liittämällä SELECT-kyselyyn WHERE-osan voimme valita vain osan riveistä halutun ehdon perusteella. Esimerkiksi seuraava kysely hakee tiedot lantusta:

SELECT * FROM Tuotteet WHERE nimi = 'lanttu';

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
4           lanttu      8        

Ehdoissa voi käyttää vertailuja ja sanoja AND ja OR samaan tapaan kuin ohjelmoinnissa. Esimerkiksi seuraava kysely etsii tuotteet, joiden hinta on välillä 4…6:

SELECT * FROM Tuotteet WHERE hinta >= 4 AND hinta <= 6;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
2           porkkana    5         
3           nauris      4         
5           selleri     4         

Järjestäminen

Oletuksena tulostaulun rivien järjestys voi olla mikä tahansa. Voimme kuitenkin määrittää halutun järjestyksen ORDER BY -osan avulla. Esimerkiksi seuraava kysely hakee tuotteet aakkosjärjestyksessä nimen mukaan:

SELECT * FROM Tuotteet ORDER BY nimi;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
         lanttu      8         
         nauris      4         
         porkkana    5         
         retiisi     7         
         selleri     4  

Järjestys on oletuksena pienimmästä suurimpaan. Kuitenkin jos haluamme järjestyksen suurimmasta pienimpään, voimme lisätä sanan DESC sarakkeen nimen jälkeen:

SELECT * FROM Tuotteet ORDER BY nimi DESC;

Tämän seurauksena kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
         selleri     4  
         retiisi     7         
         porkkana    5         
         nauris      4         
         lanttu      8         

Tietokantakielessä järjestys on joko nouseva (ascending) eli pienimmästä suurimpaan tai laskeva (descending) eli suurimmasta pienimpään. Oletuksena järjestys on nouseva, ja avainsana DESC tarkoittaa siis laskevaa järjestystä.

SQL-kielessä on myös avainsana ASC, joka tarkoittaa nousevaa järjestystä. Seuraavat kyselyt toimivat siis samalla tavalla:

SELECT * FROM Tuotteet ORDER BY nimi;

SELECT * FROM Tuotteet ORDER BY nimi ASC;

Käytännössä sanaa ASC käytetään kuitenkin harvoin.

Voimme myös järjestää rivejä usealla eri perusteella. Esimerkiksi seuraava kysely järjestää rivit ensisijaisesti kalleimmasta halvimpaan hinnan mukaan ja toissijaisesti aakkosjärjestykseen nimen mukaan:

SELECT * FROM Tuotteet ORDER BY hinta DESC, nimi;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
         lanttu      8         
         retiisi     7         
         porkkana    5         
         nauris      4         
         selleri     4  

Tässä tapauksessa tuotteet nauris ja selleri järjestetään aakkosjärjestyksessä nimen mukaan, koska ne ovat yhtä kalliita.

Erilliset tulosrivit

Joskus tulostaulussa voi olla useita samanlaisia rivejä. Näin käy esimerkiksi seuraavassa kyselyssä:

SELECT hinta FROM Tuotteet;

Koska kahden tuotteen hinta on 4, kahden tulosrivin sisältönä on 4:

hinta     
----------
       
       
       
       
       

Jos kuitenkin haluamme vain erilaiset tulosrivit, voimme lisätä kyselyyn sanan DISTINCT:

SELECT DISTINCT hinta FROM Tuotteet;

Tämän seurauksena kyselyn tulos muuttuu näin:

hinta     
----------
7         
5         
4         
8         

Tulosrivien rajaus

Kun lisäämme kyselyn loppuun LIMIT x, kysely antaa vain x ensimmäistä tulosriviä. Esimerkiksi LIMIT 3 tarkoittaa, että kysely antaa kolme ensimmäistä tulosriviä.

Yleisempi muoto on LIMIT x OFFSET y, mikä tarkoittaa, että haluamme x riviä kohdasta y alkaen (0-indeksoituna). Esimerkiksi LIMIT 3 OFFSET 1 tarkoittaa, että kysely antaa toisen, kolmannen ja neljännen tulosrivin.

Tarkastellaan esimerkkinä kyselyä, joka hakee tuotteita halvimmasta kalleimpaan:

SELECT * FROM Tuotteet ORDER BY hinta;

Kyselyn tuloksena on seuraava tulostaulu:

id          nimi        hinta     
----------  ----------  ----------
         nauris      2
         selleri     4         
         porkkana    5         
         retiisi     7         
         lanttu      8         

Saamme haettua kolme halvinta tuotetta seuraavasti:

SELECT * FROM Tuotteet ORDER BY hinta LIMIT 3;

Kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
3           nauris      2         
5           selleri     4         
2           porkkana    5      

Seuraava kysely puolestaan hakee kolme halvinta tuotetta toiseksi halvimmasta tuotteesta alkaen:

SELECT * FROM Tuotteet ORDER BY hinta LIMIT 3 OFFSET 1;

Tämän kyselyn tulos on seuraava:

id          nimi        hinta     
----------  ----------  ----------
5           selleri     4         
2           porkkana    5         
1           retiisi     7    

Tiedon muuttaminen

Komento UPDATE muuttaa taulun rivejä, jotka täsmäävät haluttuun ehtoon. Esimerkiksi seuraava komento muuttaa tuotteen 2 hinnaksi 6:

UPDATE Tuotteet SET hinta = 6 WHERE id = 2;

Useita sarakkeita voi muuttaa yhdistämällä muutokset pilkuilla. Esimerkiksi seuraava komento muuttaa tuotteen 2 nimeksi ananas ja hinnaksi 6:

UPDATE Tuotteet SET nimi = 'ananas', hinta = 6 WHERE id = 2;

Jos komennossa ei ole ehtoa, muutos vaikuttaa kaikkiin riveihin. Esimerkiksi seuraava komento muuttaa jokaisen tuotteen hinnaksi 1:

UPDATE Tuotteet SET hinta = 1;

Tiedon poistaminen

Komento DELETE poistaa taulusta rivit, jotka täsmäävät annettuun ehtoon. Esimerkiksi seuraava komento poistaa taulusta tuotteen 5:

DELETE FROM Tuotteet WHERE id = 5;

Kuten muuttamisessa, jos ehtoa ei ole, niin komento vaikuttaa kaikkiin riveihin. Seuraava komento siis poistaa kaikki tuotteet taulusta:

DELETE FROM Tuotteet;

Komento DROP TABLE poistaa tietokannan taulun (ja kaiken sen sisällön). Esimerkiksi seuraava komento poistaa taulun Tuotteet:

DROP TABLE Tuotteet;

Kommentit

Merkintä -- aloittaa rivin loppuun päättyvän kommentin:

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta INTEGER -- hinta euroina
)

Toinen tapa on aloittaa kommentti /* ja lopettaa */:

CREATE TABLE Tuotteet (
  id INTEGER PRIMARY KEY,
  nimi TEXT,
  hinta INTEGER /* hinta euroina */
)

Yhteenveto ja ryhmittely

Yhteenvetokysely antaa tuloksena jonkin yksittäisen arvon taulun riveistä, kuten taulun rivien määrän tai sarakkeen kaikkien arvojen summan. Tällaisen kyselyn tulostaulussa on vain yksi rivi.

Yhteenvetokyselyn perustana on koostefunktio (aggregate function), joka laskee yhteenvetoarvon taulun riveistä. Tavallisimmat koostefunktiot ovat seuraavat:

COUNT(): rivien määrä
SUM(): arvojen summa
MIN(): pienin arvo
MAX(): suurin arvo
AVG(): arvojen keskiarvo

Esimerkkejä

Tarkastellaan taas taulua Tuotteet:

id          nimi        hinta     
----------  ----------  ----------
         retiisi     7         
         porkkana    5         
         nauris      4         
         lanttu      8         
         selleri     4         

Seuraava kysely hakee taulun rivien määrän:

SELECT COUNT(*) FROM Tuotteet;

COUNT(*)
----------
5

Seuraava kysely hakee niiden rivien määrän, joissa hinta on 4:

SELECT COUNT(*) FROM Tuotteet WHERE hinta = 4;

COUNT(*)
----------
2

Seuraava kysely puolestaan hakee summan tuotteiden hinnoista:

SELECT SUM(hinta) FROM Tuotteet;

SUM(hinta)
----------
28

Rivien määrä COUNT-kyselyssä

Jos COUNT-funktion sisällä on tähti *, kysely laskee kaikki rivit. Jos taas funktion sisällä on sarakkeen nimi, kysely laskee rivit, joissa sarakkeessa on arvo (eli sarakkeen arvo ei ole NULL).

Tarkastellaan esimerkkinä seuraavaa taulua, jossa rivillä 3 ei ole hintaa:

id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           nauris      4         
3           lanttu      NULL
4           selleri     4         

Seuraava kysely hakee rivien yhteismäärän:

SELECT COUNT(*) FROM Tuotteet;

COUNT(*)  
----------
4

Seuraava kysely taas hakee niiden rivien määrän, joilla on hinta:

SELECT COUNT(hinta) FROM Tuotteet;

COUNT(hinta)
------------
3

Voimme myös käyttää sanaa DISTINCT, jotta saamme laskettua, montako eri arvoa jossakin sarakkeessa on:

SELECT COUNT(DISTINCT hinta) FROM Tuotteet;

COUNT(DISTINCT hinta)
---------------------
2

Ryhmittely

Ryhmittelyn avulla voimme yhdistää rivikohtaista ja koostefunktion antamaa tietoa. Ideana on, että rivit jaetaan ryhmiin GROUP BY -osassa annettujen sarakkeiden mukaan ja tämän jälkeen koostefunktion arvo lasketaan jokaiselle ryhmälle erikseen.

Tarkastellaan esimerkkinä seuraavaa taulua Tyontekijat:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
         Anna        Google      8000      
         Liisa       Google      7500      
         Kaaleppi    Amazon      5000      
         Uolevi      Amazon      8000      
         Maija       Google      9500      
         Vihtori     Facebook    5000    

Seuraava kysely hakee kunkin yrityksen työntekijöiden määrän:

SELECT yritys, COUNT(*) FROM Tyontekijat GROUP BY yritys;

Kyselyn tulos on seuraava:

yritys      COUNT(*)  
----------  ----------
Amazon      2         
Facebook    1
Google      3    

Tämä tarkoittaa, että Amazonilla on 2 työntekijää, Facebookilla on 1 työntekijä ja Googlella on 3 työntekijää.

Miten ryhmittely toimii?

Ryhmittelyssä jokainen ryhmä sisältää kaikki rivit, joissa on sama sisältö ryhmittelyssä käytetyssä sarakkeissa. Ryhmittely tuottaa tulostaulun, jonka rivien määrä on sama kuin ryhmien määrä. Jokaisella rivillä voi esiintyä ryhmittelyssä käytettyjä sarakkeita sekä koostefunktioita.

Äskeisessä kyselyssä ryhmittelyn ehtona on GROUP BY yritys, joten rivit jaetaan ryhmiin sarakkeen yritys mukaan. Tässä tapauksessa ryhmät ovat:

Ryhmä 1:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
3           Kaaleppi    Amazon      5000      
4           Uolevi      Amazon      8000      

Ryhmä 2:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
6           Vihtori     Facebook    5000    

Ryhmä 3:

id          nimi        yritys      palkka    
----------  ----------  ----------  ----------
1           Anna        Google      8000      
2           Liisa       Google      7500      
5           Maija       Google      9500     

Tämän jälkeen jokaiselle ryhmälle lasketaan rivien määrä funktiolla COUNT(*).

Ryhmittely SQLitessä

Huomaa, että SQLite sallii myös seuraavan kyselyn, jossa haetaan ryhmittelyn ulkopuolinen sarake:

SELECT yritys, nimi FROM Tyontekijat GROUP BY yritys;

yritys      nimi      
----------  ----------
Amazon      Uolevi    
Facebook    Vihtori
Google      Maija    

Koska sarake nimi ei kuulu ryhmittelyyn, sillä voi olla useita arvoja ryhmässä ja tulostauluun tulee yksi niistä. Tällainen kysely ei kuitenkaan toimi monissa tietokannoissa.

Lisää kyselyjä

Seuraava kysely hakee joka yrityksestä palkkojen summan:

SELECT yritys, SUM(palkka) FROM Tyontekijat GROUP BY yritys;

yritys      SUM(palkka)
----------  -----------
Amazon      13000      
Facebook    5000
Google      25000   

Tässä Amazonin palkkojen summa on 5000 + 8000 = 13000, Facebookin palkkojen summa on 5000 ja Googlen palkkojen summa on 8000 + 7500 + 9500 = 25000.

Seuraava kysely puolestaan hakee korkeimman palkan:

SELECT yritys, MAX(palkka) FROM Tyontekijat GROUP BY yritys;

yritys      MAX(palkka)
----------  -----------
Amazon      8000   
Facebook    5000
Google      9500

Tässä Amazonin suurin palkka on 8000, Facebookin suurin palkka on 5000 ja Googlen suurin palkka on 9500.

Tulossarakkeen nimentä

Oletuksena tulostaulun sarake saa nimen suoraan kyselyn perusteella, mutta voimme halutessamme antaa myös oman nimen AS-sanan avulla. Tämän ansiosta voimme esimerkiksi selventää, mistä yhteenvetokyselyssä on kyse.

Esimerkiksi seuraavassa kyselyssä toisen sarakkeen nimeksi tulee korkein:

SELECT
  yritys, MAX(palkka) AS korkein
FROM
  Tyontekijat
GROUP BY
  yritys;

yritys      korkein
----------  ----------
Amazon      8000         
Facebook    5000
Google      9500       

Itse asiassa sana AS ei ole pakollinen, eli voimme kirjoittaa kyselyn myös näin:

SELECT
  yritys, MAX(palkka) korkein
FROM
  Tyontekijat
GROUP BY
  yritys;

Rajaus ryhmittelyn jälkeen

Voimme lisätä kyselyyn myös HAVING-osan, joka rajaa tuloksia ryhmittelyn jälkeen. Esimerkiksi seuraava kysely hakee yritykset, joissa on ainakin kaksi työntekijää:

SELECT
  yritys, COUNT(*)
FROM
  Tyontekijat
GROUP BY
  yritys
HAVING
  COUNT(*) >= 2;

yritys      COUNT(*)  
----------  ----------
Amazon      2         
Google      3     

Voimme myös käyttää koostefunktiota vain HAVING-osassa:

SELECT
  yritys
FROM
  Tyontekijat
GROUP BY
  yritys
HAVING
  COUNT(*) >= 2;

yritys    
----------
Amazon    
Google    

Kyselyn yleiskuva

SQL-kyselyn yleiskuva on seuraava:

SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT

Kyselystä riippuu, mitkä näistä osista siinä esiintyvät. Tämä on kuitenkin aina järjestys, jossa kyselyn osat sijaitsevat toisiinsa nähden.

Tarkastellaan seuraavaksi esimerkkiä kyselystä, joka sisältää yhtä aikaa kaikki yllä mainitut osat. Kysely suoritetaan tauluun Tehtavat, jossa on projekteihin liittyviä tehtäviä. Jokaisella tehtävällä on tietty tärkeysaste. Tehtävä on kriittinen, jos sen tärkeysaste on ainakin 3.

id          projekti_id  tarkeys   
----------  -----------  ----------
         1            3         
         1            4         
         1            4         
         2            1         
         2            5         
         3            2         
         3            4         
         3            5   

Seuraava kysely etsii projektit, joissa on vähintään kaksi kriittistä tehtävää. Kysely järjestää tulokset projektin id-numeron mukaan ja antaa 10 ensimmäistä tulosta.

SELECT
  projekti_id, COUNT(*)
FROM
  Tehtavat
WHERE
  tarkeys >= 3
GROUP BY
  projekti_id
HAVING
  COUNT(*) >= 2
ORDER BY
  projekti_id;
LIMIT
  10

Kyselyn tulos on tässä:

projekti_id  COUNT(*)  
-----------  ----------
1            3         
3            2         

Tämä tarkoittaa, että projektissa 1 on 3 kriittistä tehtävää ja projektissa 3 on 2 kriittistä tehtävää.

Katsotaan nyt tarkemmin, miten kysely toimii. Kyselyn lähtökohtana ovat kaikki taulussa Tehtavat olevat rivit. Ehto WHERE tarkeys >= 3 valitsee käsittelyyn seuraavat rivit:

id          projekti_id  tarkeys   
----------  -----------  ----------
         1            3         
         1            4         
         1            4         
         2            5         
         3            4         
         3            5   

Kyselyn ryhmittely GROUP BY projekti_id jakaa rivit ryhmiin näin:

Ryhmä 1:

id          projekti_id  tarkeys   
----------  -----------  ----------
1           1            3         
2           1            4         
3           1            4         

Ryhmä 2:

id          projekti_id  tarkeys   
----------  -----------  ----------
5           2            5         

Ryhmä 3:

id          projekti_id  tarkeys   
----------  -----------  ----------
7           3            4         
8           3            5   

Osa HAVING COUNT(*) >= 2 valitsee tulostauluun ryhmät, joissa on ainakin kaksi riviä. Tässä tapauksessa valitaan ryhmät 1 ja 3.

Tulostaulussa on joka ryhmästä sarake projekti_id sekä funktion COUNT(*) antama tulos eli ryhmän rivien määrä. Tässä tapauksessa projektissa 1 on 3 tärkeää tehtävää ja projektissa 3 on 2 tärkeää tehtävää.

Osa ORDER BY projekti_id järjestää tulostaulun rivit projektin id-numeron mukaan. Tässä tapauksessa projektit ovat 1 ja 3. Osa LIMIT 10 ei vaikuta tässä tapauksessa, koska tulostaulussa on muutenkin alle 10 riviä.

Kysely tuottaa seuraavan tulostaulun:

projekti_id  COUNT(*)  
-----------  ----------
1            3       
3            2         

SQLite-tietokanta

SQLite on yksinkertainen avoimesti saatavilla oleva tietokantajärjestelmä, joka soveltuu hyvin SQL-kielen opetteluun. Voit kokeilla helposti SQL-kieleen liittyviä asioita SQLiten avulla, ja käytämme sitä tämän kurssin harjoituksissa.

SQLite on mainio valinta SQL-kielen harjoitteluun, mutta siinä on tiettyjä rajoituksia, jotka voivat aiheuttaa ongelmia todellisissa sovelluksissa. Muita suosittuja avoimesti saatavilla olevia tietokantajärjestelmiä ovat MySQL ja PostgreSQL. Niissä on suuri määrä ominaisuuksia, jotka puuttuvat SQLitestä, mutta toisaalta niiden asentaminen ja käyttäminen on vaikeampaa.

Eri tietokantajärjestelmien välillä siirtyminen on onneksi helppoa, koska kaikissa on samantapainen SQL-kieli.

SQLite-tulkki

SQLite-tulkki on ohjelma, jonka kautta voidaan käyttää SQLite-tietokantaa. Tulkki käynnistyy antamalla komentorivillä komento sqlite3. Tämän jälkeen tulkkiin voi kirjoittaa joko suoritettavia SQL-komentoja tai pisteellä alkavia tulkin omia komentoja.

SQLite-tulkin asentaminen riippuu käyttöjärjestelmästä:

Jos käytät Linuxia, voit asentaa SQLiten paketinhallinnan kautta.
Jos käytät Macia, SQLite on luultavasti valmiiksi asennettuna koneellasi.
Jos käytät Windowsia, voit hakea SQLiten lataussivulta paketin, jonka vieressä on otsikko command-line tools (eli komentorivityökalut). Tarvittava tiedosto on se, jonka nimi alkaa sqlite3.

Esimerkki

SQLite-tulkissa tietokanta on oletuksena muistissa (in-memory database), jolloin se on aluksi tyhjä ja katoaa, kun tulkki suljetaan. Tämä on hyvä tapa testailla SQL-kielen ominaisuuksia. Keskustelu tulkin kanssa voi näyttää vaikkapa tältä:

$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE Tuotteet (
   ...>   id INTEGER PRIMARY KEY,
   ...>   nimi TEXT, hinta INTEGER);
sqlite> .tables
Tuotteet
sqlite> INSERT INTO Tuotteet (nimi, hinta)
   ...>             VALUES ('retiisi', 7);
sqlite> INSERT INTO Tuotteet (nimi, hinta)
   ...>             VALUES ('porkkana', 5);
sqlite> INSERT INTO Tuotteet (nimi, hinta)
   ...>             VALUES ('nauris', 4);
sqlite> INSERT INTO Tuotteet (nimi, hinta)
   ...>             VALUES ('lanttu', 8);
sqlite> INSERT INTO Tuotteet (nimi, hinta)
   ...>             VALUES ('selleri', 4);
sqlite> SELECT * FROM Tuotteet;
1|retiisi|7
2|porkkana|5
3|nauris|4
4|lanttu|8
5|selleri|4
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Tuotteet;
id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4         
sqlite> .quit

Esimerkissä luodaan aluksi taulu Tuotteet ja tarkastetaan sitten tulkin komennolla .tables, mitä tauluja tietokannassa on. Ainoa taulu on Tuotteet, mikä kuuluu asiaan, koska tietokanta on alussa tyhjä.

Tämän jälkeen tauluun lisätään rivejä, minkä jälkeen rivit haetan taulusta. SQLite-tulkin oletustapa näyttää tulosrivit pystyviivoin erotettuina ei ole kovin tyylikäs, mutta tulostusta voi parantaa tulkin komennoilla .mode column (jokaisella sarakkeella on kiinteä leveys) ja .headers on (sarakkeiden nimet näytetään).

Lopuksi suoritetaan tulkin komento .quit, joka sulkee tulkin.

Tietokanta tiedostossa

Käynnistyksen yhteydessä SQLite-tulkille voi antaa parametrina tiedoston, johon tietokanta tallennetaan. Tällöin tietokannan sisältö säilyy tallessa tulkin sulkemisen jälkeen.

Seuraavassa esimerkissä tietokanta tallennetaan tiedostoon testi.db. Tämän ansiosta tietokannan sisältö on edelleen tallessa, kun tulkki käynnistetään uudestaan.

$ sqlite3 testi.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY,
   ...>                        nimi TEXT, hinta INTEGER);
sqlite> .tables
Tuotteet
sqlite> .quit
$ sqlite3 testi.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
Tuotteet
sqlite> .quit

Komennot tiedostosta

Voimme myös ohjata SQLite-tulkille tiedoston, jossa olevat komennot suoritetaan peräkkäin. Tämän avulla voimme automatisoida komentojen suorittamista. Esimerkiksi voimme laatia seuraavan tiedoston komennot.sql:

CREATE TABLE Tuotteet (id INTEGER PRIMARY KEY,
                       nimi TEXT, hinta INTEGER);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('retiisi', 7);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('porkkana', 5);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('nauris', 4);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('lanttu', 8);
INSERT INTO Tuotteet (nimi, hinta) VALUES ('selleri', 4);
.mode column
.headers on
SELECT * FROM Tuotteet;

Tämän jälkeen voimme ohjata komennot tiedostosta tulkille näin:

$ sqlite3 < komennot.sql
id          nimi        hinta     
----------  ----------  ----------
1           retiisi     7         
2           porkkana    5         
3           nauris      4         
4           lanttu      8         
5           selleri     4         

Tietokantojen perusteet

kevät 2024

2. SQL-kielen perusteet

Peruskomennot

Taulun luonti

Tiedon lisääminen

Esimerkkitaulu

Tiedon hakeminen

Hakuehto

Järjestäminen

Erilliset tulosrivit

Tulosrivien rajaus

Tiedon muuttaminen

Tiedon poistaminen

Kommentit

Yhteenveto ja ryhmittely

Esimerkkejä

Rivien määrä COUNT-kyselyssä

Ryhmittely

Miten ryhmittely toimii?

Lisää kyselyjä

Tulossarakkeen nimentä

Rajaus ryhmittelyn jälkeen

Kyselyn yleiskuva

SQLite-tietokanta

SQLite-tulkki

Esimerkki

Tietokanta tiedostossa

Komennot tiedostosta