05 SQL-kielen perusteet

SQL - Structured Query Language

• SQL on tietokantojen käsittelyyn kehitetty kieli
• Yleisimmät kielellä hoidettavat toiminnot:
• Tietokannan rakenteen määrittely ja muuttaminen
• Kyselyt tietokantaan ja laskenta tietokannan datalla
• Tietojen ylläpito: lisäykset, muutokset ja poistot
• Valtuuksien käsittely
• Tapahtumankäsittelyn ohjaaminen
• Lisäksi SQL:n käyttö ohjelmoinnissa
• "Upotettu” SQL; SQL:n käyttö muiden kielien sisältä:
• php.net/manual/en/mysqli.quickstart.dual-interface.php
• API-rajapinnat ohjelmointikieliin
  

SQL

• SQL on luonteeltaan ei-proseduraalinen kieli, jossa kerrotaan mitä halutaan tehdä (ei miten)
• SQL perustuu relaatioalgebraan ja joukko-oppiin
• SQL:stä useita versioita, mm. SQL-92 eli SQL2, SQL:1999 eli SQL3, SQL:2003 ja SQL:2006
• Kielestä on eri valmistajilla toisistaan poikkeavia versioita (murteita)
• SQL-kieltä voidaan käyttää useissa SQL-tuotteissa vuorovaikutteisesti antamalla SQL-käskyjä omaan ikkunaan, jolloin tulokset saadaan välittömästi (toiseenikkunaan)
  

SQL-kielen historiaa

• Alun perin SEQUEL (Structured English QUEry Language)
• 1970-luvulla: 1. versio kehitettiin IBM:n tutkimuskeskuksessa  (E.F. Coddin relaatiomalliin pohjautuen)
• 1979: 1. kaupallinen versio esiteltiin  (Oracle)
• 1986 SQL:n ANSI-standardi julkistettiin
  
• 1989 lisättiin viite-eheysmääritykset (hämmentävää, että näin myöhään)
  
• 1992 liitossyntaksi, tapahtumanhallinta ym.
• 1999 mm. herättimet, talletetut proseduurit ja oliolaajennuksia + CLI (Call Level Interface)
• 2003 ja 2006 lisää olio-ominaisuuksia, XML ym.
  

Tässä materiaalissa esiintyvät SQL-käskyt

• Noudattavat pääosin SQL2-standardia
• Suurin osa on testattu MySQLllä, Oraclella, OCELOTilla, MS SQL Serverillä ja MS Accessilla; näistä vain OCELOT on täysin SQL-standardin (SQL2 ja osin SQL3) mukainen
• Materiaalissa on mainittu, jos käskystä on jokin standardista poikkeava tuotekohtainen ratkaisu
• Rakenne (käskyjen esitysjärjestys) on Ari Hovin kirjaa mukaileva
  

DDL ja DML

• Data Definition Language (DDL): tietokannan rakenteen määrittely
  
• Perus-SQL:ssä CREATE, ALTER ja DROP TABLE, CREATE ja DROP VIEW, CREATE ja DROP INDEX
• Käyttökelpoisia esimerkiksi silloin kun on tarve luoda uudestaan taulut ja sarakkeet
• Lisäksi mm. auktorisointikäskyt GRANT ja REVOKE
  
• Data Manipulation Language (DML): tietokannan käsittely
  
• Kyselyihin SELECT, koostefunktiot (mm. SUM ja COUNT) ym.
• Tietojen ylläpitoon UPDATE, DELETE ja INSERT
• Muutosten vahvistus ja peruutus (COMMIT ja ROLLBACK)
• Lisäksi eri RDBMS:t sis. ohjauskäskyjä mm. transaktioille, sessiolle ja järjestelmälle, esim. ANALYZE , AUDIT ja COMMENT
• => ks. esimerkkejä tuotekohtaisista eroista: http://troels.arvin.dk/db/rdbms/

SQL ohjelmoinnissa

• "Upotettu” SQL: SQL-käskyt muiden kielien sisältä
• SQL-käskyt voidaan laittaa ohjelmiin sellaisenaan
• ohjelma linkitetään relaatiokantaan API-rajapinnoilla (Application Programming Interface), esim. ODBC ja JDBC
• Dynaaminen SQL: (jonkin muuttujan arvoksi sijoitettu) SQL-käsky käännetään ja suoritetaan ajoaikana

Peruskyselyt

SELECT-kyselyn eri muodot:

• Johdanto kyselyihin
• Funktiot
• Liitokset
• Yhdiste
• Alikyselyt
  

SELECT-kysely

• Kyselyiden tekeminen tietokantaan on SQL-kielen keskeisin osa
• SELECTin prusrakenteessa kolme avainsanaa, joista SELECT ja FROM ovat pakollisia:
• SELECT <attribuuttilista> valitsee listan mukaiset sarakkeet (attribuutit) kyselyyn
• FROM <taululista> määrittelee haettavat taulut
• WHERE <ehdot> määrittelee ehdot haettaville riveille (monikoille)
• SELECT * hakee kaikki sarakkeet
• SELECT ilman WHERE-ehtoa hakee kaikki rivi
  

SELECT-kyselyn esitystapa ja rakenne

SELECT-kyselyn yleinen muoto:

SELECT c1 [, c2, ... cn] -- valitut sarakkeet

FROM t1 [, t2, ... tm]   -- valitut taulut

WHERE [e1]               -- kyselyn ehto

GROUP BY [cx]            -- ryhmittelyehto

HAVING [e2]              -- ryhmittelyn hakuehto

ORDER BY [cy]            -- lajitteluehto

-- Kommentit alkavat yleensä kahdella viivalla

/* Monirivinen kommentti onnistuu useissa
   tuotteissa näin (mm. MySQL, Oracle, SQL Server) */

GROUP BY / HAVING -huomioita

• SELECTin muoto on
  

  SELECT FROM WHERE
  GROUP BY [cx] HAVING [e2] ORDER BY [cy]

  
  
• GROUP BY: Kun SELECTissä on tavallisten sarakkeiden lisäksi koostefunktioita
  => Kaikki tavissarakkeet LAITETTAVA GROUP BY-ehtoon (Hovi s. 67, myöhemmin tarkemmin)
  
• HAVING: "sama ryhmille kuin where riveille"
  

Puolipiste SQL-kyselyn lopussa ei kuulu standardiin

• Mutta: Monissa tuotteissa/käyttöliittymissä SQL-kyselyn loppuun on kirjoitettava puolipiste merkiksi kyselyn päättymisestä
• Monissa esimerkeissä puolipiste on lisätty helpottamaan copy/paste-kokeiluja kaikenlaisiin tuotteisiin/käyttöliittymiin
  

SQL-kyselyn rivittäminen

• SQL-kysely voidaan rivittää halutusti
• Selkeyttä tavoitellessa rivinvaihtomerkit voi sijoittaa ennen tärkeimpiä/kaikkia SQL-avainsanoja
  

SELECT *
FROM henkilo
WHERE sukunimi  = 'Rantala';

-- tai:

SELECT * FROM henkilo WHERE sukunimi  = 'Rantala';

Kirjainkoko SQL-kyselyissä

• SQL-avainsanoissa kirjainkoolla ei ole merkitystä, mutta ne on yleisesti tapana kirjoittaa ISOIN KIRJAIMIN (versaalilla)
  

SELECT * FROM henkilo;

-- tai:

Select * from henkilo;

• Taulujen ja sarakkeiden nimien kirjainkoot saattavat olla joissakin konfiguraatioissa merkityksellisiä.
  

SELECT * FROM henkilo;

-- ei ole välttämättä sama kuin:

SELECT * FROM Henkilo;

• Tallennetun datan kirjainkoko on yleisimmin merkityksellinen, mutta ei kaikissa tuotteissa/konfiguraatioissa (esim. MS:n tuotteissa ja MySQL:ssä)
  

SELECT * FROM henkilo WHERE sukunimi  = 'Rantala';

-- voi palauttaa eri tuloksia kuin

SELECT * FROM henkilo WHERE sukunimi  = 'rantala';

SELECT-perusesimerkkejä

1. Hae kaikki henkilöt, jotka ovat syntyneet v. 1975:

SELECT *
FROM henkilo
WHERE syntvuosi  = 1975;

• Huomaa: Vain yksi yhtäsuuruusmerkki!

2. DISTINCT suodatttaa/poistaa moninkertaiset esiintymät:
   
   

SELECT DISTINCT myyja
FROM tuote;

3. SELECT-kysely voi sisältää laskentaa sarakkeilla ja sarakkeelle voidaan antaa aliasnimi AS-sidesanalla:
   

SELECT hinta, hinta * 1.23 AS HintaSisALV, TuoteNimi
FROM tuote;

• Monissa tuotteissa AS-sidesana voidaan jättää pois
  

SELECT / Lajittelu

1. Haetaan kaikki tuotetiedot, lajittelu nimen mukaan
   
   

SELECT *
FROM tuote
ORDER BY TuoteNimi;

• Lajittelujärjestys oletuksena nouseva eli ASC ( ASCENDING)
• laskeva DESC (DESCENDING)
  

2. Lajittelee ensisijaisesti sukunimen, toissijaisesti etunimen mukaan
   Kolmantena lajitteluperusteena palkka (suurin palkka ensin):
   

SELECT etunimi, sukunimi, kunta, tutkinto, palkka
FROM henkilo
ORDER BY sukunimi, etunimi, palkka DESC;

• Huomaa: Esimerkissä sarakkeiden näyttöjärjestys on eri kuin lajittelujärjetys
  

Rivien valinta: WHERE-ehto

• Ilman WHERE-ehtoa SELECT-kysely näyttää kaikki rivit
• WHERE-ehto voi sisältää
• vertailuoperaattoreita <, >, <>, <=, >=, !=, !>, !<
• BETWEEN-määreen
• IN-määreen
• LIKE-määreen
• AND, OR ja NOT-operaattoreita
• IS NULL tai IS NOT NULL -ehdon
• Merkkijonohaut:
• % = mikä tahansa merkkijono (Accessissa *)
• _ = yksittäinen merkki (Accessissa ?)
• Kirjainkoko on yleisimmin merkityksellinen, mutta ei kaikissa tuotteissa/konfiguraatioissa (esim. MS:n tuotteissa ja MySQL:ssä)

WHERE-esimerkkejä

SELECT tuote_nimi, hinta
FROM tuote
WHERE hinta BETWEEN 100 AND 1000;

• Rajat kuuluu mukaan

SELECT htun, enimi, snimi
FROM henkilo
WHERE htun > 2134 AND htun < 2884;

• Rajat EI kuuluu mukaan

SELECT tuote_nimi FROM tuote
WHERE tuote_ryhma LIKE '%levy';

• Kaikki tuotenimet niistä tuoteryhmistä, jotka päättyvät sanaan levy
• Huomaa LIKE-avainsana
  
• Huomaa heittomerkit => merkkijono
  

SELECT tuote_nimi, hinta FROM tuote
WHERE tuote_ryhma NOT IN ('tulostimet' , 'skannerit');

• tuoteryhmä EI saa olla tulostimet tai skannerit
  

NULL-arvot

• Relaatiokannoissa on ns. kolmiarvoinen predikaattilogiikka; arvo voi olla
• ”tosi”,
• ”epätosi” tai
  
• ”ehkä”
• Onko sinulla insinöörin tutkinto? ON, EI tai EHKÄ? (NULL)
• NULLia tarvitaan, esim: Lämpötiloja keräävä anturi rikki ja arvoa ei pystytä mittaamaan => tallennetaan NULL ja keskiarvolämpötiloja laskeva AVG-funktio ei ota näitä NULL-arvojahuomioon
  

NULL-arvot

• Puuttuva, tuntematon arvo (ei nolla, välilyönti tms.)
• Haussa käytettävä IS-määrettä:

WHERE sarake IS [NOT] NULL;

• Esimerkiksi jos haetaan kaikki ne tuotteet (nimi ja hinta), joilla on jokin nimi:

SELECT tuote_nimi, hinta
FROM tuote
WHERE tuote_nimi IS NOT NULL;

• COALESCE-funktiolla voidaan testata, onko sarakkeen arvo NULL ja korvata NULL jollain tekstillä tai nollalla, esim. COALESCE(sarake, 0)
  

---

Luku on kooste Ari Hovin kirjasta, Jouni Huotarin materiaalista ja Ari Rantalan täydennyksistä.