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 Products, jossa on kolme saraketta:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price 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 price ovat kokonaislukuja (INTEGER) ja sarake name 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 taulun riveihin.

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 Products:

INSERT INTO Products (name, price) VALUES ('retiisi', 7);

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

id  name     price
--  -------  -----
1   retiisi  7    

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

INSERT INTO Products (name) VALUES ('retiisi');

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

id  name     price
--  -------  -----
1   retiisi  NULL 

Esimerkkitaulu

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

INSERT INTO Products (name, price) VALUES ('retiisi', 7);
INSERT INTO Products (name, price) VALUES ('porkkana', 5);
INSERT INTO Products (name, price) VALUES ('nauris', 4);
INSERT INTO Products (name, price) VALUES ('lanttu', 8);
INSERT INTO Products (name, price) VALUES ('selleri', 4);

Taulun sisältö on siis seuraavanlainen:

id  name      price
--  --------  -----
 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 Products;

Tässä tapauksessa kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 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 name FROM Products;

Kyselyn tulos on seuraava:

name
--------
retiisi    
porkkana             
nauris               
lanttu               
selleri              

Seuraava kysely puolestaan hakee tuotteiden nimet ja hinnat:

SELECT name, price FROM Products;

Nyt kyselyn tulos muuttuu näin:

name      price
--------  -----
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 Products WHERE name = 'lanttu';

Kyselyn tulos on seuraava:

id  name    price
--  ------  -----
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 Products WHERE price >= 4 AND price <= 6;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
2   porkkana  5    
3   nauris    4    
5   selleri   4    

SQL:ssä operaattori <> tarkoittaa erisuuruutta. Esimerkiksi seuraava kysely hakee rivit, joissa hinta ei ole 4:

SELECT * FROM Products WHERE price <> 4;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
1   retiisi   7    
2   porkkana  5    
4   lanttu    8    

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 Products ORDER BY name;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 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 Products ORDER BY name DESC;

Tämän seurauksena kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 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 Products ORDER BY name;

SELECT * FROM Products ORDER BY name 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 Products ORDER BY price DESC, name;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
 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 price FROM Products;

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

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

SELECT DISTINCT price FROM Products;

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

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 Products ORDER BY price;

Kyselyn tuloksena on seuraava tulostaulu:

id  name      price
--  --------  -----
 nauris    2    
 selleri   4    
 porkkana  5    
 retiisi   7    
 lanttu    8    

Saamme haettua kolme halvinta tuotetta seuraavasti:

SELECT * FROM Products ORDER BY price LIMIT 3;

Kyselyn tulos on seuraava:

id  name      price
--  --------  -----
3   nauris    2    
5   selleri   4    
2   porkkana  5    

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

SELECT * FROM Products ORDER BY price LIMIT 3 OFFSET 1;

Tämän kyselyn tulos on seuraava:

id  name      price
--  --------  -----
5   selleri   4    
2   porkkana  5    
1   retiisi   7    

Tietokantojen erot

Tulosrivien rajaus on esimerkki asiasta, jonka toteutus vaihtelee eri tietokannoissa. Tässä esitetty syntaksi LIMIT x OFFSET y toimii SQLiten lisäksi MySQL:ssä ja PostgreSQL:ssä.

SQL-standardiin tulosrivien rajaus tuli vasta vuonna 2008. Standardin mukainen syntaksi on OFFSET y ROWS FETCH FIRST x ROWS ONLY. Tämä syntaksi toimii esimerkiksi PostgreSQL:ssä mutta ei MySQL:ssä eikä SQLitessä.

Tiedon muuttaminen

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

UPDATE Products SET price = 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 Products SET name = 'ananas', price = 6 WHERE id = 2;

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

UPDATE Products SET price = 1;

Tiedon poistaminen

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

DELETE FROM Products WHERE id = 5;

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

DELETE FROM Products;

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

DROP TABLE Products;

Kommentit

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

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER -- price in euros
)

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

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER /* price in euros */
)

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 Products:

id  name      price
--  --------  -----
 retiisi   7    
 porkkana  5    
 nauris    4    
 lanttu    8    
 selleri   4    

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

SELECT COUNT(*) FROM Products;

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

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

SELECT COUNT(*) FROM Products WHERE price = 4;

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

Seuraava kysely hakee summan tuotteiden hinnoista:

SELECT SUM(price) FROM Products;

SUM(price)
----------
28

Tässä tapauksessa kyselyn tuloksena on 7 + 5 + 4 + 8 + 4 = 28.

Seuraava kysely hakee pienimmän ja suurimman hinnan:

SELECT MIN(price), MAX(price) FROM Products;

MIN(price)  MAX(price)
----------  ----------
4           8

Lisää COUNT-funktiosta

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 jokin arvo eli sarakkeen arvo ei ole NULL.

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

id  name     price
--  -------  -----
1   retiisi  7    
2   nauris   4    
3   lanttu   NULL 
4   selleri  4    

Seuraava kysely hakee rivien yhteismäärän:

SELECT COUNT(*) FROM Products;

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

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

SELECT COUNT(price) FROM Products;

COUNT(price)
------------
3

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

SELECT COUNT(DISTINCT price) FROM Products;

COUNT(DISTINCT price)
---------------------
2

Tämän kyselyn tuloksena on 2, koska sarakkeessa price on kaksi eri arvoa (4 ja 7). Tähän ei lasketa mukaan NULL-arvoa.

Tyhjän aineiston käsittely

Koostefunktioiden toiminta saattaa aiheuttaa yllätyksiä, jos käsiteltävässä aineistossa ei ole yhtään riviä. Tarkastellaan esimerkkinä seuraavaa tyhjää taulua:

id  name     price
--  -------  -----

Funktio COUNT antaa rivien määräksi 0:

SELECT COUNT(price) FROM Products;

COUNT(price)
------------
0

Sen sijaan funktio SUM antaa summaksi NULL:

SELECT SUM(price) FROM Products;

SUM(price)
----------
NULL

Tämä voi tuntua yllättävältä, koska tyhjän summan voisi olettaa olevan 0 eikä NULL. Vastaavasti myös MIN, MAX ja AVG antavat tuloksen NULL tyhjälle aineistolle.

Funktion IFNULL avulla voi varautua tilanteeseen, jossa koostefunktio saattaa antaa tuloksen NULL. Seuraavassa kyselyssä IFNULL(SUM(price), 0) tarkoittaa muuten samaa kuin SUM(price), mutta tulos NULL muutetaan tulokseksi 0.

SELECT IFNULL(SUM(price), 0) FROM Products;

IFNULL(SUM(price), 0)
---------------------´
0

NULL-arvojen käsittely

Funktio IFNULL(x, y) palauttaa arvon x, jos x ei ole NULL, ja muuten arvon y. Tämän avulla voidaan käsitellä tilanne, jossa SQL-kyselyssä oleva arvo saattaa olla NULL. Esimerkiksi IFNULL(x, 0) muuttaa arvon x nollaksi, jos se on NULL.

Funktio IFNULL ei kuulu SQL-standardiin, eikä se toimi kaikissa tietokannoissa. SQL-standardin mukainen funktio on COALESCE, jota voi käyttää samalla tavalla. Esimerkiksi COALESCE(x, 0) tarkoittaa samaa kuin IFNULL(x, 0).

Palaamme NULL-arvojen käsittelyyn tarkemmin myöhemmin.

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 Employees:

id  name      company   salary
--  --------  --------  ------
 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 company, COUNT(*) FROM Employees GROUP BY company;

Kyselyn tulos on seuraava:

company   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äytetyissä 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 company, joten rivit jaetaan ryhmiin sarakkeen company mukaan. Tässä tapauksessa ryhmät ovat:

Ryhmä 1:

id  name      company  salary
--  --------  -------  ------
3   Kaaleppi  Amazon   5000  
4   Uolevi    Amazon   8000  

Ryhmä 2:

id  name     company   salary
--  -------  --------  ------
6   Vihtori  Facebook  5000  

Ryhmä 3:

id  name   company  salary
--  -----  -------  ------
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(*).

Ryhmittelyn sarakkeet

Melko usein ryhmittelyn perusteena on tasan yksi sarake, kuten sarake company äskeisessä esimerkissä. Jos ryhmittelyssä on useampi sarake, jokainen ryhmä muodostuu riveistä, joilla on sama arvoyhdistelmä ryhmittelyyn kuuluvissa sarakkeissa.

Esimerkiksi seuraavassa kyselyssä ryhmittelyn perusteena on kaksi saraketta:

SELECT company, salary, COUNT(*) FROM Employees GROUP BY company, salary;

Koska jokaisella rivillä on eri arvoyhdistelmä näissä sarakkeissa, tulostaulussa jokainen rivi on omassa ryhmässään:

company   salary  COUNT(*)
--------  ------  --------
Amazon    5000    1       
Amazon    8000    1       
Facebook  5000    1       
Google    7500    1       
Google    8000    1       
Google    9500    1       

Ryhmittelyn jälkeen tulostaulussa voi olla ryhmittelyyn kuuluvia sarakkeita mutta ei ryhmittelyn ulkopuolisia sarakkeita. Esimerkiksi seuraava kysely ei olisi mielekäs:

SELECT company, name FROM Employees GROUP BY company;

Tässä sarake name ei kuulu ryhmittelyyn, minkä takia olisi epäselvää, mikä sarakkeen arvoksi tulisi tulostaulussa. Esimerkiksi tulostauluun tulee vain yksi rivi, joka liittyy yritykseen Amazon, mutta yrityksellä on kaksi työntekijää, joilla on eri nimet (Kaaleppi ja Uolevi).

Ryhmittely SQLitessä

Huomaa, että SQLite sallii äskeisen kyselyn, jossa haetaan ryhmittelyn ulkopuolinen sarake:

SELECT company, name FROM Employees GROUP BY company;

company   name   
--------  -------
Amazon    Uolevi 
Facebook  Vihtori
Google    Maija  

Koska sarake name ei kuulu ryhmittelyyn, sillä voi olla useita arvoja ryhmässä ja tulostauluun tulee yksi niistä jollain logiikalla valittuna. Tällainen kysely ei kuitenkaan yleensä toimi muissa tietokannoissa.

Lisää kyselyjä

Seuraava kysely hakee joka yrityksestä palkkojen summan:

SELECT company, SUM(salary) FROM Employees GROUP BY company;

company   SUM(salary)
--------  -----------
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 company, MAX(salary) FROM Employees GROUP BY company;

company   MAX(salary)
--------  -----------
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 max_salary:

SELECT
  company, MAX(salary) AS max_salary
FROM
  Employees
GROUP BY
  company;

company   max_salary
--------  ----------
Amazon    8000      
Facebook  5000      
Google    9500      

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

SELECT
  company, MAX(salary) max_salary
FROM
  Employees
GROUP BY
  company;

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
  company, COUNT(*)
FROM
  Employees
GROUP BY
  company
HAVING
  COUNT(*) >= 2;

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

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

SELECT
  company
FROM
  Employees
GROUP BY
  company
HAVING
  COUNT(*) >= 2;

company
-------
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 Tasks, jossa on projekteihin liittyviä tehtäviä. Jokaisella tehtävällä on tietty prioriteetti. Tehtävä on kriittinen, jos sen prioriteetti on ainakin 3.

id  project_id  priority
--  ----------  --------
 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
  project_id, COUNT(*)
FROM
  Tasks
WHERE
  priority >= 3
GROUP BY
  project_id
HAVING
  COUNT(*) >= 2
ORDER BY
  project_id
LIMIT
  10;

Kyselyn tulos on tässä:

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

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

Katsotaan nyt tarkemmin, miten kysely toimii. Kyselyn lähtökohtana ovat kaikki taulussa Tasks olevat rivit. Ehto WHERE priority >= 3 valitsee käsittelyyn kriittiset tehtävät:

id  project_id  priority
--  ----------  --------
 1           3       
 1           4       
 1           4       
 2           5       
 3           4       
 3           5       

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

Ryhmä 1:

id  project_id  priority
--  ----------  --------
1   1           3       
2   1           4       
3   1           4       

Ryhmä 2:

id  project_id  priority
--  ----------  --------
5   2           5       

Ryhmä 3:

id  project_id  priority
--  ----------  --------
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 project_id sekä funktion COUNT(*) antama tulos eli ryhmän rivien määrä. Tässä tapauksessa projektissa 1 on kolme tärkeää tehtävää ja projektissa 3 on kaksi tärkeää tehtävää.

Osa ORDER BY project_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:

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

(Video: SQLiten asennus ja käyttö Windowsissa)

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 Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT, price INTEGER);
sqlite> .tables
Products
sqlite> INSERT INTO Products (name, price) VALUES ('retiisi', 7);
sqlite> INSERT INTO Products (name, price) VALUES ('porkkana', 5);
sqlite> INSERT INTO Products (name, price) VALUES ('nauris', 4);
sqlite> INSERT INTO Products (name, price) VALUES ('lanttu', 8);
sqlite> INSERT INTO Products (name, price) VALUES ('selleri', 4);
sqlite> SELECT * FROM Products;
1|retiisi|7
2|porkkana|5
3|nauris|4
4|lanttu|8
5|selleri|4
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Products;
id  name      price
--  --------  -----
1   retiisi   7    
2   porkkana  5    
3   nauris    4    
4   lanttu    8    
5   selleri   4    
sqlite> .quit

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

Sitten tauluun lisätään rivejä, minkä jälkeen rivit haetaan taulusta. SQLite-tulkin oletustapa näyttää tulosrivit pystyviivoin erotettuina voi olla vaikea lukea, 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 test.db. Tämän ansiosta tietokannan sisältö on edelleen tallessa, kun tulkki käynnistetään uudestaan.

$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT, price INTEGER);
sqlite> .tables
Products
sqlite> .quit
$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
Products
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 commands.sql:

commands.sql

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

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

$ sqlite3 < commands.sql
id  name      price
--  --------  -----
1   retiisi   7    
2   porkkana  5    
3   nauris    4    
4   lanttu    8    
5   selleri   4    

Tietokantojen perusteet

syksy 2025

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ä

Lisää COUNT-funktiosta

Tyhjän aineiston käsittely

Ryhmittely

Miten ryhmittely toimii?

Ryhmittelyn sarakkeet

Lisää kyselyjä

Tulossarakkeen nimentä

Rajaus ryhmittelyn jälkeen

Kyselyn yleiskuva

SQLite-tietokanta

SQLite-tulkki

Esimerkki

Tietokanta tiedostossa

Komennot tiedostosta