1 / 44

SQL 4 :

SQL 4 :. 1. Meer met gerelateerde tabellen 2. Data toevoegen, wijzigen, verwijderen 3. ‘Referentiele integriteit’. Tot nu toe. Tabel: rijen en kolommen Datatype: typologie op basis van mogelijk operaties Selecteren uit een enkele tabel ( SELECT ) Tabellen koppelen met

selina
Télécharger la présentation

SQL 4 :

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL 4 : 1. Meer met gerelateerde tabellen 2. Data toevoegen, wijzigen, verwijderen 3. ‘Referentiele integriteit’ 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. Tot nu toe • Tabel: rijen en kolommen • Datatype: typologie op basis van mogelijk operaties • Selecteren uit een enkele tabel ( SELECT ) • Tabellen koppelen met • primairy key (PK) • foreign key (FK) • 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

  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. SELECT DISTINCT • SELECT DISTINCT categorieFROM product • categorievervoervoedselboek 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 • 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. 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

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

  10. SELECT uit twee gekoppelde tabellen • Selecteer alle producten en koppel daar aan de naam van de producent • SELECT product.*, producent.naam AS producentNaamFROM productLEFT JOINproducentON product.producentID = producent.ID • ID naam … producentID producentNaam3 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

  11. JOIN = SELECT tabel1.* , tabel2.Nm FROM tabel1 LEFT JOIN tabel2 ON tabel1.tabel2ID = tabel2.ID t1.ID t1.Nm t1.t2ID t2.ID t2.Nm 1 aaa 1 1 een 2 gghg 1 1 een 3 uoig 2 2 twee 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. Drie soorten koppelingen • LEFT JOIN • RIGHT JOIN • INNER JOIN En nog een vierde • FULL JOIN ( bonuspunt voor Paul ) 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. LEFT JOIN Alle rijen uit de linker tabel, ook als er geen relatie is met een rij 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

  14. RIGHT JOIN Alle rijen uit de rechter tabel, ook als er geen relatie is met een rij 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

  15. INNER JOIN Alleen rijen uit de beide tabel, als er een relatie is met een rij uit de andere tabel. • 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

  16. 90% van de koppelingen: ‘LEFT JOIN’waarvan 80% ook met INNER JOIN kan • Selecteer data uit een primaire tabel en koppel data uit gerelateerde tabellen 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

  17. Aggregaties met een JOIN Selecteer alle producenten met daarbij het aantal producten dat die producent levert. SELECT producent.ID, producent.naam product.ID, product.naam FROM producent LEFT JOIN product ON producent.ID = product.producentID SELECT producent.ID, producent.naam COUNT( product.ID ) AS aantalproducten FROM producent LEFT JOIN product ON producent.ID = product.producentID GROUP BY producent.ID, producent.naam 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. Many-to-one Many-to-one Product wordt_geleverd_door producent Boek is_geschreven_door auteur Bestelling door klant One-to-many producent levert product auteur schrijft boek klant doet bestelling In de databasetabellen: De rijen in tabel aan de many-kant van de relatie bevat een FK die verwijst naar de PK van gerelateerde rij aan de one-kant van de relatie. 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. 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

  21. Many-to-many • Selecteer van bestelling met ID = 1 alle producten SELECT bestelling_product.aantal, product.ID, product.naam, product.prijs FROM bestelling_product INNER JOIN product ON bestelling_product.productID = product.IDWHERE bestelling_product.bestellingID = 1 Aantal productID 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

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

  23. Welke klanten hebben iets besteld van producent X? Uit welke categorieën doet klant X zijn bestellingen ? 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. Als je de relaties volgt kan je allerlei informatie uit de database selecteren Alle klanten die iets hebben besteld van producent 1 SELECT DISTINCT klant.* FROM klant INNER JOIN bestelling ON bestelling.klantID = klant.ID INNER JOIN bestelling_product ON bestelling_product.bestellingID = bestelling.ID INNER JOIN product ON product.ID = bestelling_product.productID WHERE product.producentID = 1 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. Tot zover het samenvoegen van tabellen (JOIN) 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. Subquery SELECT in een SELECT • Twee manier om een subquery te gebruiken • In een selectie criterium • In een berekende kolom • NB: het is altijd mogelijk en vaak zelfs beter om hetzelfde met een JOIN te bereiken. 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. Subqueries in WHERE … IN • Selecteer klanten die iets hebben besteld • SELECT klant.* FROM klant WHERE klant.ID IN ( SELECT klantID FROM bestelling ) • SELECT DISTINCT klant.* FROM klant INNER JOIN bestelling ON klant.ID = bestelling.klantID 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. Subquerie om iets te berekenen • Selecteer alle producenten en daarbij het aantal producten van die producenten • SELECT producent.ID, producent.naam, (SELECT COUNT(*) FROM product WHERE product.producentID = producent.ID ) AS aantalproductenFROM producent • SELECT producent.ID, producent.naam, COUNT(product.ID) AS aantalproductenFROM producent LEFT JOIN product ON product.producentID = producent.IDGROUP BY producent.ID, producent.Naam 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. bonus vraag • Waarom is het eigenlijk niet toegestaan om onderstaande querie te maken • SELECT * FROM (SELECT * FROM klant) 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. En dan nu: data manipuleren INSERT UPDATE DELETE 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. INSERT Voeg een klant toe aan de tabel ‘klant’ INSERT INTO klant ( voornaam, achternaam ) VALUES ( 'piet' , 'jansen') INSERT INTOtabel ( kolomX, kolomY, kolomZ) VALUES( waardeX, waardeY, waardeZ) NB autonumber kolommen worden automatisch ingevuldbijvoordbeeld ID velden 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. UPDATE Wijzig de vooraam van klant 1 UPDATE klant SET voornaam = ‘Gijs' WHERE ID = 1 UPDATEtabel SETkolomX = waardeX, SETkolomY = waardeY, SETkolomZ = waardeZ, WHERE ID = 1 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. DELETE Verwijder klant 1 DELETE * FROM klant WHERE ID = 1 DELETE * FROM tabel WHERE criterium 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. Veel gestelde vragen • Als je bij een nieuw product de producentID moet invullen, hoe weet je dan welke producentID je moet hebben? Gewoon de naam van de producent zou toch veel gemakkelijker zijn? 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. Veel gestelde vragen • Als je bij een nieuw product de categorieID moet invullen, hoe weet je dan wel categorieID je moet hebben? Gewoon de naam van de categorie zou toch veel gemakkelijker zijn? 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. Veelgestelde vragen INSERT INTOtabel ( kolomX, kolomY ) VALUES( waardeX, waardeY ) UPDATEtabel SETkolomX = waardeX, SETkolomY = waardeY, WHERE ID = 1 DELETE * FROM tabel WHERE criterium • Waarom zijn INSERT, UPDATE en DELETE zo eenvoudig in vergelijking met 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

  37. Referentiele integriteit 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. Oops Wat gebeurd er als je per ongeluk • Je een producent verwijdert, terwijl er nog producten van de producent in de tabel ‘product’ staan? • Als je bij product.producentID een producentID invult die niet bestaat? De database wordt ‘corrupt’ 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. ‘Referentiele integriteit’ • Om te voorkomen dat een database ‘per ongeluk’ corrupt raakt, is het mogelijk om het database managementsysteem de opdracht te geven alle relaties af te dwingen. De RDBMS geeft dan foutmelding als je iets doet wat de database zou corrumperen • Dit afdwingen van de referentiele integriteit is één van de belangrijkste functies van een RDBMS 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. Integriteit waarborgen =functionaliteit afschermen • De database is zo geprogrammeerd dat geen corrupte data in kunnen. • De programmeur van de Logic module kan geen requests doen die de data corrupt maken. => Minder kans op bugs. 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. Normalisatie • Alle informatie is slecht één keer in de database opgeslagen. • In plaats van het dubbel opslaan van informatie worden relaties (FK’s ) gebruikt om te verwijzen naar informatie. Bijvoorbeelden: • Niet bij ieder boek de naam van de auteur, maar een verwijzing (FK) naar de auteur in de auteurtabel, waar zijn naam één keer staat. • Niet bij iedere bestelling de adresgegevens van de klant, maar een verwijzing (FK) naar de klant in de klant-tabel, waar de adresgegevens één keer staan. 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. Methodes om referentiele integriteit te waarborgen • DBMS controleert en geen foutmeldingen als er iets gebeurd wat de database corrupt maakt. • ‘TRIGGERS’ in bouwen die bij gebeurtenissen de database update. • ‘STORED PROCEDURES’ ( een serie SQL queries ) die de integriteit in tak houdt. • ‘TRANSACTION PROCESSING’ : bij een foutmelding worden alle voorgaande queries teruggedraaid. • PHP: Dataobjecten: alle queries moeten via een dataobject. Het object is zo geprogrammeerd dat de database integer blijft. 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. TOETS Wat kan je verwachten 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. TOETS • Begrippen omschrijven • SQL queries maken • Selecties in gewone taal bedenken Niveau voor een V : alle ‘*’ opdrachten uit oefeningen2.doc Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

More Related