Tietokannat kurssi KSAO Datanomit kytn tuki kevt 2015

  • Slides: 12
Download presentation
Tietokannat -kurssi KSAO, Datanomit, käytön tuki kevät 2015 Lauri Tapola

Tietokannat -kurssi KSAO, Datanomit, käytön tuki kevät 2015 Lauri Tapola

Tietojen yhdistäminen eri taulujen välillä. SQL join 24. 3. 2015 Lauri Tapola

Tietojen yhdistäminen eri taulujen välillä. SQL join 24. 3. 2015 Lauri Tapola

Miksi tietoja pitää yhdistellä? • Relaatiotietokannoissa tiettyyn asiaan liittyvä data on vain yhdessä taulussa.

Miksi tietoja pitää yhdistellä? • Relaatiotietokannoissa tiettyyn asiaan liittyvä data on vain yhdessä taulussa. • Jos esimerkiksi lainaus raportille halutaan asiakkaan nimi asiakas – taulusta ja kirjan nimi kirja –taulusta, niin taulujen data pitää yhdistellä perus- ja viiteavainten avulla.

Kuinka tietoja yhdistellään? Avaimet! Ohessa on kirjastotaulujen avaimet. lainaus –taulusta on viite-avaimet kirja –tauluun

Kuinka tietoja yhdistellään? Avaimet! Ohessa on kirjastotaulujen avaimet. lainaus –taulusta on viite-avaimet kirja –tauluun sekä asiakas –tauluun. Taulu: kirja Perusavain : kirja_id Taulu: lainaus Perusavain: lainaus_id Viiteavaimet: asiakas_id ja kirja_id Taulu: asiakas Perusavain: asiakas_id

Yhdistelmäkyselyt. Perus- ja viiteavaimet. Kun yhdistellään taulujen tietoja, ensiksi annetaan tauluille aliasnimet, jotta kysely

Yhdistelmäkyselyt. Perus- ja viiteavaimet. Kun yhdistellään taulujen tietoja, ensiksi annetaan tauluille aliasnimet, jotta kysely lyhenee. Esimerkiksi kirja=kir, asiakas=asi, lainaus=lai. (Voidaan myös käyttää kokonaisia taulujen nimiä, mutta se on hitaampaa. ) SELECT –komennossa valitaan alias-nimet ja kentät: SELECT asiakas_nimi, lainauspaiva, lai. palautuspaiva, lai. kirja_id FROM asiakas asi, lainaus lai WHERE asiakas_id=lai. asiakas_id WHERE –komennossa linkitetään taulut avainkenttien perusteella. Taulu: kirja = kir Perusavain : kirja_id Taulu: lainaus = lai Perusavain: lainaus_id Viiteavaimet: asiakas_id ja kirja_id Taulu: asiakas = asi Perusavain: asiakas_id

Taulujen tietojen yhdistämiseksi tarvitaan joinkomentoja tai viiteavainten linkitys WHERE ehdossa. • Yleisin tapa eri

Taulujen tietojen yhdistämiseksi tarvitaan joinkomentoja tai viiteavainten linkitys WHERE ehdossa. • Yleisin tapa eri taulujen tietojen yhdistämiseksi on viiteavainten linkitys WHERE ehdossa seuraavan esimerkin mukaisesti: SELECT asiakas_nimi, lainauspaiva, lai. palautuspaiva, lai. kirja_id FROM asiakas asi, lainaus lai WHERE asiakas_id = lai. asiakas_id • mitä edellinen kysely palauttaa? • JOIN –komennot käydään jatkokurssilla. • Jos asia kiinnostaa, katso http: //www. w 3 schools. com/sql_join. asp • INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

Yhdistelmäkyselyt. Esimerkki 1, kirjasto 1. Hae lainatut kirjat. Listaa kirja_id, kirjan nimi ja lainauspäivä.

Yhdistelmäkyselyt. Esimerkki 1, kirjasto 1. Hae lainatut kirjat. Listaa kirja_id, kirjan nimi ja lainauspäivä. SELECT kirja_id, kirjan_nimi, lainauspaiva FROM kirja kir, lainaus lai WHERE kirja_id = lai. kirja_id Kysely palauttaa 17 riviä. Taulu: kirja = kir Perusavain : kirja_id Taulu: lainaus = lai Perusavain: lainaus_id Viiteavaimet: asiakas_id ja kirja_id

Yhdistelmäkyselyt. Esimerkki 2, kirjasto 2. Hae lainassa olevat kirjat. Listaa kirja_id, kirjan nimi, lainauspäivä

Yhdistelmäkyselyt. Esimerkki 2, kirjasto 2. Hae lainassa olevat kirjat. Listaa kirja_id, kirjan nimi, lainauspäivä ja palautuspäivä. Jos kirjaa ei ole palautettu, niin palautuspäivä on siis tyhjä isnull(lai. palautuspaiva). SELECT kirja_id, kirjan_nimi, lainauspaiva, lai. palautuspaiva FROM kirja kir, lainaus lai WHERE kirja_id = lai. kirja_id AND ISNULL(lai. palautuspaiva) Kysely palauttaa 10 riviä. Taulu: kirja = kir Perusavain : kirja_id Taulu: lainaus = lai Perusavain: lainaus_id Viiteavaimet: asiakas_id ja kirja_id

Yhdistelmäkyselyt. Esimerkki 3, kirjasto 3. Hae lainassa olevat kirjat. Listaa kirja_id, kirjan nimi, lainauspäivä,

Yhdistelmäkyselyt. Esimerkki 3, kirjasto 3. Hae lainassa olevat kirjat. Listaa kirja_id, kirjan nimi, lainauspäivä, eräpäivä ja Asiakasnimi. Listaa Asiakasnimen ja lainauspäivän mukaisessa järjestyksessä. SELECT kirja_id, kirjan_nimi, lainauspaiva, lai. erapaiva, asiakas_nimi FROM kirja kir, lainaus lai, asiakas asi Taulu: kirja = kir Perusavain : kirja_id Taulu: lainaus = lai Perusavain: lainaus_id Viiteavaimet: asiakas_id ja kirja_id WHERE kirja_id = lai. kirja_id AND asiakas_id=lai. asiakas_id AND ISNULL(lai. palautuspaiva) Taulu: asiakas = asi ORDER BY asiakas_nimi, lainauspaiva Perusavain: asiakas_id

Yhdistelmäkyselyt. Esimerkki 4, kirjasto 4. Hae myöhässä olevat kirjat. Listaa kirja_id, kirjan nimi, lainauspäivä,

Yhdistelmäkyselyt. Esimerkki 4, kirjasto 4. Hae myöhässä olevat kirjat. Listaa kirja_id, kirjan nimi, lainauspäivä, eräpäivä, nykyinen päivä, asiakasnimi ja lisäksi teksti ”MYÖHÄSSÄ”. Listaa Asiakasnimen mukaisessa järjestyksessä. SELECT kirja_id, kirjan_nimi, lainauspaiva, lai. erapaiva, date(now()) as tanaan, asiakas_nimi, "MYÖHÄSSÄ" as tila FROM kirja kir, lainaus lai, asiakas asi WHERE kirja_id = lai. kirja_id AND asiakas_id=lai. asiakas_id AND ISNULL(lai. palautuspaiva) AND date(now()) > lai. erapaiva ORDER BY asiakas_nimi, lainauspaiva

Oppimistehtävä 6 – kysymys Suunnittele kysely seuraavaan tarpeeseen. h 1. Hae Maaliskuussa lainatut kirjat.

Oppimistehtävä 6 – kysymys Suunnittele kysely seuraavaan tarpeeseen. h 1. Hae Maaliskuussa lainatut kirjat. Listaa niiden nimet ja lainauspäivä. Ohje: hae ensin maaliskuun lainaukset. Select * from lainaus where lainauspaiva >= "2015 -03 -01" and lainauspaiva < "2015 -04 -01” Yhdistä lainaus-taulu ja kirja-taulu. WHERE lai. kirja_id = kirja_id Lopuksi valitset sarakkeet yhdistelmäkyselyyn. SELECT kirjan_nimi, lainauspaiva FROM lainaus lai, kirja kir Taulu: kirja Perusavain : kirja_id Taulu: lainaus Perusavain: lainaus_id Viiteavaimet: asiakas_id ja kirja_id

Oppimistehtävä 6 – VIHJE Alla on oikeat vastaus, mutta rivit ovat menneet väärään järjestykseen,

Oppimistehtävä 6 – VIHJE Alla on oikeat vastaus, mutta rivit ovat menneet väärään järjestykseen, laita ne oikeaan järjestykseen niin löydät oikean vastauksen. h 1. Hae Maaliskuussa lainatut kirjat. Listaa niiden nimet ja lainauspäivä. lainauspaiva >= "2015 -03 -01" and lainauspaiva < "2015 -04 -01" FROM lainaus lai, kirja kir SELECT kirjan_nimi, lainauspaiva WHERE lai. kirja_id = kirja_id and Taulu: kirja Perusavain : kirja_id Taulu: lainaus Perusavain: lainaus_id Viiteavaimet: asiakas_id ja kirja_id