Download
sql 3 join n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL 3 : JOIN PowerPoint Presentation
Download Presentation
SQL 3 : JOIN

SQL 3 : JOIN

139 Vues Download Presentation
Télécharger la présentation

SQL 3 : JOIN

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. SQL 3 : JOIN Het selecteren van data uit gekoppelde tabellen Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  2. Vorige week • Tabel: rijen en kolommen gevuld met ‘data’ • Datatype: typologie op basis van mogelijk operaties • Selecteren uit een enkele tabel ( SELECT ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  3. Basis datatype • Type informatie in een veld • Bepaalt het soort operaties dat je er op kan uitvoeren. • basis datatypen die in iedere database of programmeertaal op één of andere manier worden ondersteund: • Numeriek • Tekst • Boolean • Binair • Datum/tijd • Lange tekst Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  4. Syntax van een eenvoudige SELECT • SELECTID,naam, prijsFROMproductWHEREcategorie = ‘boek’ORDER BYprijs • ID naam prijs8 groentensoep 2,157 appelsap 2,959 blik tonijn 3,15 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  5. Berekeningen in SELECT (numeriek) • Selecteer de naam, prijs en prijs_plus_btw • SELECT ID, naam, prijs, prijs + prijs* btw_tarief/100AS prijs_met_btwFROM product • ID Naam prijs prijs_met_btw1 fiets 100 1202 brood 1 1,05 5 boek 30 36 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  6. Statistieken (aggregaties) • SELECTCOUNT(*) AS aantal, AVG(prijs) AS midPrijs,MAX(prijs) AS maxPrijs, MIN(prijs) AS minPrijsFROM product • Aantal midPrijs maxPrijs minPrijs 9 873,10 8000 2.05 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  7. Statistieken (aggregaties) per groep • SELECT categorie,COUNT(*) AS aantal, AVG(prijs) AS midPrijs,MAX(prijs) AS maxPrijs, MIN(prijs) AS minPrijsFROM productGROUP BY categorie • Cat Aantal midPrijs maxPrijs minPrijsvervoer 6 1188 8000 2.05 • boek 3 20,70 40 10 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  8. SELECT DISTINCT • SELECT DISTINCT categorieFROM product • categorievervoervoedselboek Dit is hetzelfde als: • SELECT categorieFROM productGROUP BY categorie Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  9. En.. • Hoe ging het huiswerk? Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  10. vandaag • Gegevens selecteren uit meerdere ‘gekoppelde’ tabellen. • Eigenlijk heel simpel, maar je moet het effe door hebben. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  11. Vandaag te leren • Gerelateerde tabellen • Primaire Key (PK) , Foreign Key (FK) • JOIN ( rijen uit verschillende tabellen samenvoegen ) • LEFT JOIN • RIGHT JOIN • INNER JOIN • Soorten relaties • Many-to-one • One-to-many • One-to-one • Many-to-many Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  12. Relaties • Een boek is gescheven door een auteur • Een student volgt een opleiding • De klant doet een bestelling • Een product gemaakt door een producent Meer voorbeelden? • Voor iedere ‘entiteit’ een tabel http://nl.wikipedia.org/wiki/Entiteit Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  13. Entiteit Een op zichzelf staand ‘iets’ dat bestaat en kenmerken heeft. Bijvoorbeeld: • Product • Producent • Schrijver • Boek • Leerling • Vak • Bestelling • Opleiding Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  14. Relatie tussen product en producent Foreign Key (FK) Primairy Key (PK) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  15. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  16. Relatie tussen product en producent Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  17. Gekoppelde tabellen Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  18. Verschillende soorten relaties naar kwantiteit • Many-to-one ( product -geproduceerd door> producent ) • One-to-many ( klant -doet> bestelling ) • One-to-one ( man –isgetrouwdmet> vrouw ) • Many-to-many ( bestelling –besteldproduct> product ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  19. JOIN:SELECT uit twee gekoppelde tabellen • Selecteer alle producten en koppel daar aan de naam van de producent • SELECT product.*, producent.naam FROM product, producentWHERE product.producentID = producent.ID • ID naam … producentID producent.Naam3 tonijn 6 unilever4 kip 6 unilever5 step 4 joop’s fiets… … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  20. JOIN:SELECT uit twee gekoppelde tabellen • Selecteer alle producten en koppel daar aan de naam van de producent • SELECT product.*FROM product • ID naam … producentID 3 tonijn 6 4 kip 6 5 step 4 … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  21. JOIN:SELECT uit twee gekoppelde tabellen • Selecteer alle producten en koppel daar aan de naam van de producent • SELECT product.*, producent.naam FROM product JOIN producent ON product.producentID = producent.ID • ID naam … producentID producent.Naam3 tonijn 6 unilever4 kip 6 unilever5 step 4 joop’s fiets… … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  22. JOIN:SELECT uit twee gekoppelde tabellen • Selecteer alle producten en koppel daar aan de naam van de producent en het btw tarief • SELECT product.*, producent.naam , btwtarief.percentage FROM product JOIN producent ON product.producentID = producent.IDJOIN btwtarief ON product.btwtariefID = btwtarief.ID • ID naam producentID producent.Naam percentage3 tonijn 6 unilever 5 4 kip 6 unilever 55 step 4 joop’s fiets 20… … … … Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  23. Drie soorten koppelingen • LEFT OUTER JOIN • RIGHT OUTER JOIN • INNER JOIN Opdracht voor gevorderden: Leg aan beginners uit wat het verschil is tussen deze soorten joins. Geef voorbeelden. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  24. En verder... • De sheets hierna gaan over de subtiele verschillen tussen de verschillende soorten JOINS ( INNER, LEFT, RIGHT ). • Dat heeft pas betekenis als je de JOIN begrijpt en kan toepassen Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  25. LEFT JOIN Alle rijen uit de linker tabel, plus de gekoppelde rijen uit de rechter tabel • SELECT product.*, producent.naam AS producentNaamFROM productLEFT JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fiets10 Karel en… NULL NULL Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  26. RIGHT JOIN Alle rijen uit de rechter tabel, plus de gekoppelde rijen uit de linker tabel • SELECT product.*, producent.naam AS producentNaamFROM productRIGHT JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fietsNULL NULL 9 Amalia Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  27. INNER JOIN Alleen rijen die aan elkaar gekoppeld zijn. • SELECT product.*, producent.naam AS producentNaamFROM productINNER JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fiets Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  28. INNER JOIN (alternatief) Een andere manier om een inner join te maken. • SELECT product.*, producent.naam AS producentNaamFROM product, producentWHERE product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fiets Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  29. FULL OUTER JOIN Alle rijen uit zowel de linker als de rechter tabel. Ook als er geen koppeling is. • SELECT product.*, producent.naam AS producentNaamFROM productFULL OUTER JOIN producent ON product.producentID = producent.ID • ID naam … producentID producentNaam3 tonijn 6 unilever5 step 4 joop’s fiets • NULL NULL 9 Amalia • 10 Karel en… NULL NULL Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  30. 90% van de koppelingen: ‘LEFT JOIN’ • Selecteer data uit een primaire tabel en koppel data uit gerelateerde tabellen Bijv alle data uit ‘product’ en koppel de producentnaam. SELECT product.*, producent.naam, producent.infoFROM productLEFT JOIN producent ON product.producentID = producent.ID SELECTprimaire_tabel.* gerelateerdetabel.* FROMprimaire_tabelLEFT JOINgerelateerde_tabelONprimaire_tabel.FK = gerelateerde_tabel.PK Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  31. Vele wegen naar rome • SELECT product.* , producent.naam AS producentNaamFROM product LEFT JOIN producent ON product.producentID = producent.ID • SELECT product.* , producent.naam AS producentNaamFROM producent RIGHT JOIN product ON product.producentID = producent.ID Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  32. Meerdere gerelateerde tabellen • SELECT product.*, producent.naam, categorie.naam, btwtarief.percentageFROM productLEFT JOIN producent ON product.producentID = producent.IDLEFT JOIN categorie ON product.categorieID = categorie.IDLEFT JOIN btwtarief ON product.btwtariefID = btwtarief.ID Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  33. Verschillende soorten relaties naar kwantiteit • Many-to-one ( product -geproduceerd door> producent ) • One-to-many ( klant -doet> bestelling ) • Many-to-many ( bestelling –besteldproduct> product ) • One-to-one ( man –isgetrouwdmet> vrouw ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  34. One-to-one relaties Wanneer gebruik je een one-to-one? • Natuurlijke 1 op 1 relaties: • Is_getrouwd_met • Aparte tabel voor binaire of grootte data velden • product.filmID = film.ID • SELECT product.* , film.bindata FROM product LEFT JOIN film ON product.filmID = film.ID Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  35. Many-to-manybestelling <-> product • Een aparte tabel met bestellingID-productID paren Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  36. Selecteren via relaties Selecteer alle klanten die een bepaald product hebben besteld. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  37. Stap 1: SELECT en JOIN de tabellen: SELECT klant.* FROM klant JOIN bestellingJOIN bestelling_productJOIN product WHERE product.ID = 21 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  38. Stap 1: SELECT en JOIN de tabellen(en vergeet niet de criteria voor deze relaties erbij te vertellen): SELECT klant.* FROM klant JOIN bestelling ON klant.ID = bestelling.klantIDJOIN bestelling_product ON bestelling.ID=bestelling_product.bestellingIDJOIN product ON bestelling_product.productID = product.ID WHERE product.ID = 21 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  39. Many-to-many • Selecteer van een bestelling alle producten SELECT product.ID, product.naam, product.prijs, bestelling_product.aantal FROM bestelling_product INNER JOIN product ON bestelling_product.productID = product.IDWHERE bestelling_product.bestellingID = 1 Aantal ID naam prijs 30 1 Toveren… 20 2 3 SQL… 23 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  40. Many-to-manyvan product naar bestelling • Selecteer van een product alle bestellingen SELECT bestelling_product.aantal, bestelling.datum, klant.naam FROM ( bestelling_product INNER JOIN bestelling ON bestelling_product.bestellingID = bestelling.ID) INNER JOIN klant ON bestelling.klantID= klant.IDWHERE bestelling_product.productID = 1 Aantal datum naam 30 1-9-2003 Bos 4 6-12-2003 Graaf Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  41. Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  42. V2-projecten database Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  43. Vandaag geleerd • Gerelateerde tabellen • Primaire Key (PK) , Foreign Key (FK) • JOIN ( tabellen samenvoegen ) • LEFT JOIN • RIGHT JOIN • INNER JOIN • Soorten relaties • Many-to-one • One-to-many • One-to-one • Many-to-many ( gebruik een ‘tussen tabel’ ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

  44. oefeningen • Doe de oefeningen 2 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl