Lisää Sql:stä...

1. Lisää Sql:stä... Haaga-Helia Ammattikorkeakoulu

2. Esimerkkitietokanta • Seuraavissa esimerkeissä käytämme esimerkkinä tietokantaa, jossa on kaksi taulua, joista toiseen on tallennettu tietoja liittyen opiskelijoiden tietoihin ja toiseen opiskelijoiden tenttituloksia, joissa perusavaimena on käytetty sosiaaliturvatunnusta • Esimerkkitaulut näyttävät seuraavilta:

3. Oppilaat

4. Tentit

5. ALIKYSELY SELECT etunimiFROM oppilaatWHERE sotu IN (SELECT sotu FROM tentit WHERE arvosana = 3)Tulostaa niiden oppilaiden etunimet, jotka ovat saaneet jostakin tentistä arvosanan 3.

6. Etunimi Sakari Unto

7.  alikyselyä tarvitaan, jos ehdossa tarvitaan jonkin toisen kyselyn tulosta (ylempi kysely on riippuvainen alemman kyselyn tuloksesta) •  alikyselyä tarvitaan, jos kysely kohdistuu useampaan kuin yhteen relaatioon (liitos on tässä tilanteessa nopeampi) •  hakuehdossa on IN, koska alikysely voi tuottaa useita vastauksia •  hakuehdossa voi olla myös vertailuoperaattori, jos alikysely tuottaa vain yhden vastauksen

8. LIITOS (RELAATIOIDEN YHDISTÄMINEN) SELECT Oppilaat.Sukunimi, Tentit.arvosanaFROM Oppilaat, TentitWHERE Oppilaat.sotu=Tentit.sotu ”Haetaan oppilaiden sukunimet ja kyseisten oppilaiden kaikkien tenttien arvosanat”

9. Tulos… Sukunimi Arvosana Olli 3 Olli 1 Mononen 3 Mononen 2 Mononen 2 Kauppinen 1

10.  liitosta tarvitaan, jos kysely kohdistuu useampaan kuin yhteen relaatioon  liitos voidaan tehdä, jos molemmista tauluista löytyy vastaava sarake, joilla taulut voidaan yhdistää  liitosta käytetään aina ensisijaisesti, jos se on vain mahdollista, sillä liitos on nopeampi kuin alikysely

11. SELECT O.Sukunimi, T.arvosanaFROM Oppilaat AS O, Tentit AS TWHERE O.sotu=T.sotu  taulu voidaan nimetä FROM-osassa toiseksi tämän kyselyn ajaksi  jos taulu nimetään uudelleen, sarakenimen edessä on käytettävä ”uutta” taulunimeä

12. UNION SELECT sukunimi, etunimiFROM OppilaatWHERE etunimi = ’Venla’ UNIONSELECT sukunimi, etunimi FROM OppilaatWHERE Sukunimi= ’Olli’

13. SukunimiEtunimi Leinonen Venla Olli Sakari  yhdistää kahden tai useamman kyselyn tuloksen  SELECT-osa pitää olla samanlainen  karsii pois duplikaatti-vastaukset

14. FUNKTIOITA COUNT (sarake) tulosrivien lukumäärä AVG (sarake) keskiarvo SUM (sarake) summa MAX (sarake) suurin arvo MIN (sarake) pienin arvo

15.  funktiot tuottavat vain yhden vastauksen  SELECT-osassa ei voi olla sekä funktioita että yksittäisiä tietoja (ryhmittely on poikkeus)  funktiot huomioivat vain ei NULL-arvot  jos ehdossa täytyy vertailla jotain saraketta funktion tulokseen, funktio täytyy tehdä alikyselynä  funktio ei voi olla WHERE-ehdon jäsenenä EI palkka > AVG(palkka)

16. SELECT AVG (aloitusvuosi)FROM Oppilaat ”hae keskimääräinen aloitusvuosi” Expr……. 199….

17. SELECT MAX (Aloitusvuosi)FROM Oppilaat ”hae uusin opiskelija” Expr……. 1999

18. SELECT MAX (aloitusvuosi)FROM OppilaatWHERE postinumero IN (SELECT postinumero FROM Oppilaat WHERE postinumero>20000) ”Hae suurin opiskelijan aloitusvuosi opiskelijoilta, jotka asuvat pääkaupunkiseudun ulkopuolella Expr……. 1999

19. SELECT SUM (arvosana)FROM TentitWHERE kurssitunnus = ’Kem200’ ”Hae arvosanojen summa Kem200 kurssin tentituloksista” Expr……. 6

20. SELECT sukunimiFROM OppilaatWHERE Aloitusvuosi < (SELECT AVG(aloitusvuosi) FROM Oppilaat) ”Hae niiden opiskelijoiden sukunimet, jotka ovat viettäneet aikaansa opinahjossa keskimääräistä enemmän” sukunimi Mononen Nenonen

21. RYHMITTELY SELECT laitos, AVG (aloitusvuosi)FROM OppilaatGROUP BY laitos ”tulosta kunkin laitoksen aloitusvuosien keskiarvo” laitosExpr……. 1 1997 2 1995 31996

22.  GROUP BY muodostaa annetun sarakkeen mukaan ryhmiä, joista haetaan funktioilla tietoja  SELECT-osassa voi olla vain funktioita ja ryhmittelyn mukainen sarake

23. SELECT laitos, AVG (aloitusvuosi)FROM OppilaatWHERE laitos < 3GROUP BY laitosHAVING COUNT (aloitusvuosi) > 1 ”laitoksennumero ja aloituvuosien keskiarvo niistä laitoksista, joiden laitosnumero on pienempi kuin 3 ja laitoksista on enemmän kuin yksi työntekijä ” OsnumeroExpr……. 1 1997 2 1995

24.  WHERE-osassa rajoitetaan mukaanotettavia rivejä  HAVING-osassa rajoitetaan mukaanotettavia ryhmiä

25. PÄIVÄMÄÄRIEN KÄSITTELY SELECT kurssitunnus, päivämääräFROM TentitWHERE arvosana<2 ”Kurssitunnukset ja tenttipäivät niistä tenteistä, joissa on saatu arvosanoja alle 2:en” KurssitunnusPäivä Kem300 21.1.1995 Kem555 1.1.1999

26. SELECT kurssitunnus, arvosanaFROM TentitWHERE kurssitunnus=’Kem200’ AND päivämäärä< #06-30-1996# ”tulosta kurssitunnukset ja arvosanat niistä tenttisuorituksista, joiden kurssitunnus on Kem200 ja joiden ´suorituspäivä on aikaisempi kuin 30.6.1996” Kurssitunnus Arvosana Kem200 3 Kem200 3

27. PÄIVÄMÄÄRÄ-FUNKTIOITA Date() meneillään oleva päivä(mm-dd-yy) Year(sarake) päivämäärä-kentän vuosi Month(sarake) päivämäärä-kentän kuukausi Day(sarake) päivämäärä-kentän päivä Näyttää päivämäärästä DatePart(”yyyy”,sarake) vuoden numeron DatePart(”m”,sarake) kuukauden numeron DatePart(”d”,sarake) päivän numeron

28. Näkymän luominen • Näkymä tarkoittaa SQL-kielen avulla määriteltyä kyselyä, jolle on annettu nimi ja joka on tallennettu kantaan (virtuaalitaulu) • Näkymän kautta voi myös rajoitetusti päivittää kohdetaulujen dataa. Tämä ei kuitenkaan ole suositeltavaa. • Rajoitukset ovat tuotekohtaisia

29. Näkymän käyttömahdollisuuksia • Olennaisen tiedon näyttäminen tauluista • Tallennetut näkymät ovat optimoituja SQL-lauseita • Valmiilla näkymillä voidaan vähentää käyttäjien tarvetta luoda monimutkaisia SQL-lausekkeita • Suojataan varsinaista tietokantaa/rakennetta

30. Näkymän luonti: Esimerkki CREATE VIEW henkilot AS SELECT t.tnimi, o.osnimi FROM ttekija as t, osasto as o WHERE t.osnumero=o.osnumero Luo näkymän henkilot liitoksena ttekija- ja osasto –tauluista.

31. KYSELYJEN TEHOKKUUDESTA • liitos mieluummin kuin alikysely • jos liitostauluja n, ehtoja pitäisi olla vähintään n-1 • WHERE – lause määrää liittämisjärjestyksen • WHERE – ehdoissa vältettävä aritmeettisia operaatioita • WHERE – ehdossa vältä LIKE:a • OR tehoton ehdoissa (ei käytä indeksejä) • vältä lajittelua (DISTINCT, ORDER BY, GROUP BY) • indeksit nopeuttavat hakua

32. RIVIEN LISÄYS (INSERT) INSERT INTO taulu (sar1, ……., sarn)VALUES (arvo1, ……… , arvon) INSERT INTO ttekija (tnumero, tnimi, osnumero, palkka) VALUES (2000, ’Niemi’, 30, 10000)

33. INSERT INTO ttekija_historia (tnumero, tnimi, osnumero, palkka) VALUES SELECT tnumero, tnimi, osnumero, palkka FROM ttekija WHERE osnumero = 10

34. RIVIEN PÄIVITYS (UPDATE) UPDATE taulu SET sar1=arvo1, ……., sarn=arvon[ WHERE ehto UPDATE ttekija SET palkka = 10000 ”päivitä KAIKKIEN työntekijöiden palkaksi 10000”

35. UPDATE ttekija SET palkka = 15000 WHERE tnimi = ’Niemi’ ”päivitä Niemi-nimisen työntekijän palkaksi 10000” UPDATE ttekija SET palkka = palkka * 1.1 WHERE nimike = ’suunnittelija’ ”päivitä 1.1-kertaiseksi niiden työntekijöiden palkka, joiden nimike on suunnittelija”

36. RIVIEN POISTAMINEN (DELETE) DELETE * FROM taulu [ WHERE ehto ] DELETE * FROM ttekija ”poista KAIKKI työntekijät”

37. DELETE * FROM ttekijaWHERE tnumero = 3000 ”poista työntekijä, jonka numero on 3000” DELETE * FROM ttekijaWHERE palkka < (SELECT AVG(palkka) FROM ttekija) ”poista ne työntekijät, joiden palkka on pienempi kuin keskipalkka”