Luento 6

• tietokannat602.mp3 3.7M
• tietokannat603.mp3 3.3M
• tietokannat604.mp3 3.7M
• tietokannat605.mp3 3.9M
• tietokannat606.mp3 2.8M
• tietokannat607.mp3 2.4M

SQL-kyselyjen perusteita

esimerkkitietokanta:

• Luominen
• Sisältö
• Kyselyt

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 Cd
  
  

• 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 Cd
  
  

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

  /* Haetaan kaikkien CD-levyjen kaikki tiedot
  SELECT *
  FROM Cd
  
  

• 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 CD-levyjen nimet. Muutetaan nimi-kentän otsikoksi 
  Nimike */
  SELECT Nimi AS Nimike
  FROM Cd
  
  

• 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 CD-levyjen tiedot.
  SELECT * 
  FROM Cd
  WHERE vuosi = 1999
  
  

  /* Haetaan kaikki yhtyeen 1 levyt. */
  SELECT *
  FROM Cd
  WHERE esittaja = 1
  
  

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

    /* Haetaan kaikki 'Hieno CD'-nimisten levyjen tiedot. */
    SELECT *
    FROM Cd
    WHERE nimi = 'Hieno CD'
    
    

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

    /* Haetaan nimet vuonna 1999 julkaistuista levyistä */
    SELECT Nimi
    FROM Cd
    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 levyistä */
    SELECT Nimi
    FROM Cd
    WHERE vuosi < 1999
    
    

    /* Haetaan nimet vuonna 1999 tai sitä ennen julkaistuista levyistä */
    SELECT Nimi
    FROM Cd
    WHERE vuosi =< 1999
    
    

    /* Haetaan kaikki ne levyt joiden nimi ei ole 'Hieno CD' */
    SELECT nimi
    FROM Cd
    WHERE nimi <> 'Hieno CD'
    
    

    /* Haetaan kaikki ne levyt joiden nimi on aakkosissa 'Pyörivä Kivi'-nimen
     jälkeen */
    SELECT nimi
    FROM Cd
    WHERE nimi > 'Pyörivä Kivi'
    
    

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

    /* Haetaan kaikki ne levyt joiden nimi ei ole 'Hieno CD' */
    SELECT *
    FROM Cd
    WHERE NOT nimi = 'Hieno CD'
    
    

    /* Haetaan kaikki ne levyt joita ei ole julkaista vuonna 1999 */
    SELECT *
    FROM Cd
    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 levyt joiden nimi alkaa W-kirjaimella */
      SELECT *
      FROM Cd
      WHERE nimi LIKE 'W%'
      
      

      /* Haetaan kaikki ne levyt joiden nimessä esiintyy  a-kirjain */
      SELECT *
      FROM Cd
      WHERE nimi LIKE '%a%'
      
      

      /* Haetaan kaikki ne levyt joiden nimi päättyy  i-kirjaimeen */
      SELECT *
      FROM Cd
      WHERE nimi LIKE '%i'
      
      

      /* Haetaan kaikki ne levyt joiden nimessä on kahdeksan kirjainta */
      SELECT *
      FROM Cd
      WHERE nimi LIKE '________'
      
      

      /* Haetaan kaikki ne levyt joiden nimessä ensimmäinen sana on viisi merkkiä */
      SELECT *
      FROM Cd
      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 levyt joiden nimessä esiintyy %-merkki */
      SELECT *
      FROM Cd
      WHERE nimi LIKE '%~%%' ESCAPE '~'
      
      

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

    /* Haetaan kaikki ne levyt, jotka on julkaistu vuosien 1950 ja 1980 välillä */
    SELECT *
    FROM Cd
    WHERE vuosi BETWEEN 1950 AND 1980
    
    

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

    /* Haetaan kaikki ne levyt, jotka on julkaistu vuosina 1965 ja 1966 */
    SELECT *
    FROM Cd
    WHERE vuosi IN (1965, 1966)
    
    

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

    /* Haetaan kaikki ne levyt, joille on määritelty julkaisuvuosi */
    SELECT *
    FROM Cd
    WHERE vuosi IS NOT NULL
    
    

    /* Haetaan kaikki ne levyt, joilta puuttuu julkaisuvuosi */
    SELECT *
    FROM Cd
    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 cd-levy */
  SELECT MIN(vuosi) AS Vanhin
  FROM Cd
  
  

  /* Kuinka monta cd-levyä kokoelmassa on? */
  SELECT COUNT(vuosi) AS lkm
  FROM Cd
  
  

  /* Milloin on julkaistu kokoelman uusin cd-levy? */
  SELECT MAX(vuosi) AS Uusin
  FROM Cd
  
  

  /* Mikä on kokoelman cd-levyjen julkaisuvuosien keskiarvo? */
  SELECT AVG(vuosi) AS Keskiarvo
  FROM Cd
  
  

  /* Mikä on kokoelman cd-levyjen vanhimman ja nuorimman julkaisuvuosien
  erotus? */
  SELECT MAX(vuosi)-MIN(vuosi) AS Erotus
  FROM Cd
  
  

  /* Kuinka monta vuotta sitten on julkaistu kokoelman vanhin levy? */
  SELECT 2000 - MIN(vuosi) AS Ikä
  FROM Cd
  
  

• Monista ohjelmistoista löytyy myös monia merkkijonojen ja lukujen käsittelyyn tarkoitettuja funktioita:

      SUBSTR
      YEAR
  		MONTH
  		DAY
      RIGHT
      LEFT
      UPPER
      LOWER
      LENGHT
      LTRIM
      RTRIM
      ROUND
      FLOOR
      CEIL
      CEILING
  
  

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