SQL-kyselyjen perusteita

Luennon äänittäminen epäonnistui :-(. Korvikkeeksi voi kuunnella viime vuoden vastaavaa luentoa.

esimerkkitietokanta:

• Luominen
• Sisältö
• Kyselyjä

Tietojen lisääminen (INSERT)

Uusien tietueiden lisääminen tehdään INSERT-komennolla:

INSERT INTO Tentit 
VALUES ('000000-0000', 1, '15.3.1999', 2.5)

Kaikkien kenttien tietoja ei ole pakko antaa. Tällöin on lueteltava niiden sarakkeiden nimet, joihin tietoja syötetään.

INSERT INTO Tentit (sotu, KurssiID, pvm, arvosana) 
VALUES ('000000-0000', 1, '15.3.1999', 2.5) 

Vain sellaiset kentät voidaan jättää puuttumaan, jotka sallivat NULL-arvoja tai joihin on määritelty oletusarvo.
Rivejä voidaan lisätä myös useampia kerrallaan, jos haetaan lisättävät rivit alikyselyllä.

INSERT INTO Tietokannat 
SELECT * 
FROM Tentit 
WHERE KurssiID = 1

Kyselyt (SELECT)

SELECT <lista haettavista kentistä>

FROM <mistä taulu(i)sta
WHERE     <mitkä rivit haetaan>
GROUP BY      <miten ryhmitellään>
HAVING        <mitkä ryhmittelyn tulosriveistä haetaan>
ORDER BY      <miten lajitellaan>

• Käskyssä on oltava SELECT ja FROM, muut osat ova valinnaisia. Järjestyksen on oltava sama kuin yllä.

  -- Haetaan kaikkien CD-levyjen nimet 
  SELECT Nimi
  FROM Aanite
  
  

• Kommentteja SQL-komentojen keskelle voidaan kirjoittaa ---alkuisille riveille (SQL92). Joissakin ohjelmistoissa käytetään --- ja -merkintöjä (vrt. C/C++).
• Mahdolliset toistuvat rivit voidaan poistaa lisäämällä DISTINCT määre heti SELECT-sanan jälkeen ennen kenttäluetteloa.

  -- Haetaan kaikkien CD-levyjen nimet. Näytetään sama nimi vain kerran 
  SELECT DISTINCT Nimi
  FROM Aanite
  
  

• Jos halutaan hakea kaikki kentät niin lista voidaan korvata asteriskilla (*).

  -- Haetaan kaikkien äänitteiden kaikki tiedot
  SELECT *
  FROM Aanite
  
  

• Kentille voidaan antaa myös uudet otsikot ja niiden väliin voidaan lisätä merkkijonoja, jotka tulevat näkyviin joka riville. Uusi otsikko annetaan käyttämällä sidesanaa AS.

  -- Haetaan kaikkien äänitteiden nimet. Muutetaan nimi-kentän otsikoksi 
  Nimike 
  SELECT Nimi AS Nimike
  FROM Aanite
  
  

• WHERE <mitkä rivit haetaan> määrää mitkä rivit kelpaavat tulosjoukkoon. Ehdossa määritellään sarakkeen nimi ja arvo, joka kyseisestä sarakkeesta pitää löytyä.

  -- Haetaan kaikkien vuonna 1999 julkaistujen äänitteiden tiedot.
  SELECT * 
  FROM Aanite
  WHERE vuosi = 1999
  
  

  -- Haetaan kaikki yhtyeen 1 levyt. 
  SELECT *
  FROM Aanite
  WHERE esittaja = 1
  
  

  • merkkijonot heittomerkkien '' sisään. Esim. 'Kemopetrol'. Pienillä ja isoilla kirjaimilla on merkitystä!

    -- Haetaan kaikki 'Hieno CD'-nimisten äänitteiden tiedot. 
    SELECT *
    FROM Aanite
    WHERE nimi = 'Hieno CD'
    
    
    

  • numerot annetaan sellaisenaan
  • Hakua rajoittavan ehdon ei tarvitse kohdistua lopputulokseen tulevaan kenttään.

    -- Haetaan nimet vuonna 1999 julkaistuista äänitteistä 
    SELECT Nimi
    FROM Aanite
    WHERE vuosi = 1999
    
    

  • Käytettävissä on seuraavat vertailuoperaattorit:
    • = yhtäsuuri
    • < pienempi kuin
    • > suurempi kuin
    • >= suurempi tai yhtäsuuri kuin
    • <> erisuuri kuin

    -- Haetaan nimet ennen vuotta 1999 julkaistuista äänitteistä
    SELECT Nimi
    FROM Aanite
    WHERE vuosi < 1999
    
    

    -- Haetaan nimet vuonna 1999 tai sitä ennen julkaistuista äänitteistä 
    SELECT Nimi
    FROM Aanite
    WHERE vuosi =< 1999
    
    

    
    -- Haetaan kaikki ne äänitteet joiden nimi ei ole 'Hieno CD' 
    SELECT nimi
    FROM Aanite
    WHERE nimi <> 'Hieno CD'
    
    

    -- Haetaan kaikki ne äänitteet joiden nimi on aakkosissa 'Pyörivä Kivi'-nimen
     jälkeen 
    SELECT nimi
    FROM Aanite
    WHERE nimi > 'Pyörivä Kivi'
    
    

  • NOT-operaatiolla voidaan kääntää ehtolauseen merkitys päinvastaiseksi.

    -- Haetaan kaikki ne äänitteet joiden nimi ei ole 'Hieno CD' 
    SELECT *
    FROM Aanite
    WHERE NOT nimi = 'Hieno CD'
    
    
    

    -- Haetaan kaikki ne äänitteet joita ei ole julkaista vuonna 1999 
    SELECT *
    FROM Aanite
    WHERE NOT vuosi = 1999
    
    

  • Merkkijonojen kohdalla voidaan tehdä hakuja myös osittain täsmäävillä arvoilla käyttäen LIKE-operaattoria.
    • % tarkoittaa mitä tahansa merkkiä nolla tai useamman kerran (SQL92). Accessissa käytetään asteriskia (*).
    • _ tarkoittaa mitä tahansa yksittäistä merkkiä (SQL92). Accessissa käytetään kysymysmerkkiä (?).

      -- Haetaan kaikki ne äänitteet joiden nimi alkaa W-kirjaimella 
      SELECT *
      FROM Aanite
      WHERE nimi LIKE 'W%'
      
      

      -- Haetaan kaikki ne äänitteet joiden nimessä esiintyy  a-kirjain 
      SELECT *
      FROM Aanite
      WHERE nimi LIKE '%a%'
      
      

      -- Haetaan kaikki ne äänitteet joiden nimi päättyy  i-kirjaimeen 
      SELECT *
      FROM Aanite
      WHERE nimi LIKE '%i'
      
      
      

      -- Haetaan kaikki ne äänitteet joiden nimessä on kahdeksan kirjainta 
      SELECT *
      FROM Aanite
      WHERE nimi LIKE '________'
      
      

      -- Haetaan kaikki ne äänitteet joiden nimessä ensimmäinen sana on viisi merkkiä 
      SELECT *
      FROM Aanite
      WHERE nimi LIKE '_____ %'
      
      

    • Jos halutaan erityisesti hakea % ja/tai _ merkkejä niin pitää määritellä jokin merkki jolla voidaan ilmaista että halutaan käsitellä % ja _ -merkkejä niiden literaalisessa merkityksessä. Tämä onnistuu ESCAPE-määreellä.

      -- Haetaan kaikki ne äänitteet joiden nimessä esiintyy %-merkki 
      SELECT *
      FROM Aanite
      WHERE nimi LIKE '%~%%' ESCAPE '~'
      
      

  • BETWEEN-määreellä voidaan helposti etsiä tietyllä välillä olevia arvoja. Päätepisteet luetaan mukaan haettavaan alueeseen.

    -- Haetaan kaikki ne äänitteet, jotka on julkaistu vuosien 1950 ja 1980 välillä 
    SELECT *
    FROM Aanite
    WHERE vuosi BETWEEN 1950 AND 1980
    
    

  • IN-määreellä voidaan luetella isompikin joukko kelpuutettavia arvoja.

    -- Haetaan kaikki ne äänitteet, jotka on julkaistu vuosina 1965 ja 1966 
    SELECT *
    FROM Aanite
    WHERE vuosi IN (1965, 1966)
    
    

  • NULL-arvoja voidaan etsiä IS NULL- ja IS NOT NULL -määreillä.

    -- Haetaan kaikki ne äänitteet, joille on määritelty julkaisuvuosi 
    SELECT *
    FROM Aanite
    WHERE vuosi IS NOT NULL
    
    

    -- Haetaan kaikki ne äänitteet, joilta puuttuu julkaisuvuosi 
    SELECT *
    FROM Aanite
    WHERE vuosi IS NULL
    
    

• SQL:stä löytyy seuraavanlaiset koostefunktiot:

      AVG             keskiarvo
      COUNT           arvojen lukumäärä
      MAX             suurin arvo
      MIN             pienin arvo
      SUM             summa
  

• Koostefunktioita voidaan käyttää vain joko SELECT-lauseen kenttäluettelossa tai HAVING-lauseessa. WHERE-lauseessa niitä ei voi käyttää.

  -- Milloin on julkaistu kokoelman vanhin äänite 
  SELECT MIN(vuosi) AS Vanhin
  FROM Aanite
  
  

  
  -- Kuinka monta äänitettä kokoelmassa on? 
  SELECT COUNT(vuosi) AS lkm
  FROM Aanite
  
  

  -- Milloin on julkaistu kokoelman uusin äänite? 
  SELECT MAX(vuosi) AS Uusin
  FROM Aanite
  
  

  -- Mikä on kokoelman äänitteiden julkaisuvuosien keskiarvo? 
  SELECT AVG(vuosi) AS Keskiarvo
  FROM Aanite
  
  

  -- Mikä on kokoelman äänitteiden vanhimman ja nuorimman julkaisuvuosien
  erotus? 
  SELECT MAX(vuosi)-MIN(vuosi) AS Erotus
  FROM Aanite
  
  
  

  -- Kuinka monta vuotta sitten on julkaistu kokoelman vanhin äänite? 
  SELECT 2000 - MIN(vuosi) AS Ikä
  FROM Aanite
  
  

• AND ja OR -operaatioilla voidaan yhdistää kaksi tai useampia hakuehtoja.
  • Jos on hiemankin epävarma loogisten operaattorien toteutusjärjestyksestä niin kannattaa käyttää sulkuja. Sulkuja ei koskaan tule käytettyä liikaa.

NULL-arvot

• NULL tarkoittaa puuttuvaa tai tuntematonta arvoa/tietoa. NULL-arvojen käyttäminen aiheuttaa ongelmia erityisesti lasku- ja vertailuoperaatioissa.
• Jos NULL osallistuu laskutoimitukseen niin lopputulos on NULL
• Vertailuissa esim. kentta < 10 lopputulos on tuntematon, jos kentän arvo on ollut NULL. Lopputulos ei siis ole tosi eikä epätosi.
• NULL-arvojen testaaminen pitää tehdä IS NULL ja IS NOT NULL -komennoilla. SQL-kielessä on käytössä kolmiarvologiikka:

  x	y	x AND y	x OR y	NOT x
  Tosi	Tosi	Tosi	Tosi	Epätosi
  Tosi	NULL	NULL	Tosi	Epätosi
  Tosi	Epätosi	Epätosi	Tosi	Epätosi
  NULL	Tosi	NULL	Tosi	NULL
  NULL	NULL	NULL	NULL	NULL
  NULL	Epätosi	Epätosi	NULL	NULL
  Epätosi	Tosi	Epätosi	Tosi	Tosi
  Epätosi	NULL	Epätosi	NULL	Tosi
  Epätosi	Epätosi	Epätosi	Epätosi	Tosi

• Yhteenvetofunktiot jättävät NULL-arvot käsittelemättä lukuunottamatta COUNT(*)-funktiota.

Tietojen muuttaminen (UPDATE)

Tietoja muutetaan UPDATE-komennolla

UPDATE Tentit 
SET pvm = '1.5.1999', 
arvosana = 3 
WHERE sotu = '000000-0000' 
AND KurssiID = 'TIE110' 
AND pvm = '1.2.1999' 

Haluttaessa päivittää vain yksi tietty tietue on muistettava määritellä kyseisen tietueen avain tarkasti!

Tietojen poistaminen (DELETE)

Poistaminen tapahtuu DELETE-komennolla

DELETE FROM Oppilaat 
WHERE sotu = '666666-6666' 

Koko taulun tyhjentäminen: DELETE FROM Taulu
Joissakin ohjelmistoissa myös komento: TRUNCATE Taulu Nopea. Ei voida peruuttaa koska ei tallenna muutoksia lokitiedostoon.