Tietokannat kurssi KSAO Datanomit kytn tuki kevt 2015
- Slides: 12
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
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 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 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 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ä. 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ä 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ä, 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ä, 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. 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, 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