In het voorgaande hoofdstuk heb je gezien hoe je met behulp van
phpMyAdmin MySQL databases kunt aanmaken. In deze databse tabellen kunt plaatsen en aan deze tabellen gegevens kan toevoegen.
Bezoek je Facebook, Google of een een of andere webwinkel dan zie je nooit iets als
phMyAdmin verschijnen. Wel zie je veel gegevens in tabelvorm. We zijn op weg om
een eigen webwinkel te maken. De gebruiker moet dan ook informatie te zien krijgen
die is opgeslagen in een database. phpMyAdmin is een handige tool om een database
aan te maken. Maar de uiteindelijke administratie van de inhoud van de tabellen
gegeurd vanuit de webwinkel applicatie die je zelf in php gaar schrijven.
Om gegevens in een MySQl database te kunnen opvragen, in te voeren, te verwijderen of te
veranderen moeten er gebruik worden gemaakt van een taal die het database
management programma begrijpt. Deze taal heet SQL (Structured Query Language).
Voor je de koppeling met php gaat maken leer je nu eerst een deel van deze taal
: het opvragen van gegevens.
Van het document StructuredQueryLanguage(SQL).htm
krijg je een papieren versie die je naar het schoolexamen mee mag nemen. Verzamel alle
SQL code die je in de opdrachten maakt in een document en voorzie iedere opdracht
van commentaar. Een afdruk van dat document mag je ook meenemen naar het
schoolexamen.
Aan het einde van dit hoofdstuk:
weet je wat SQL is;
kun je sql-selectie opdrachten uitvoeren in Mysql binnen phpMyAdmin;
Introductie SQL
Je hebt gezien hoe je een database, tabellen, velden en gegevens aanmaakt in Mysql.
Nu wordt het tijd om eerder opgeslagen informatie weer te geven ofwel te selecteren.
Het selecteren van gegevens uit een database is een van de belangrijkste acties.
Er moeten onder andere bestellijsten, omzetcijfers en orders worden getoond.
Het programmeren van selecties kan wel heel ingewikkeld zijn. Een tip van de sluier
zullen we hier oplichten.
We gaan phpMyAdmin gebruiken om SQL selectie opdrachten uit te laten voeren.
Later als je deze opdrachten ook in je eigen php code moet laten uitvoeren kun je
phpMyAdmin gebuiken om je SQL code te ontwerpen en te testen.
Als je in phpMyAdmin een table kiest die gegevens bevat dan wordt een lijst
van de gegevens getoond. Ook zie je een SQL commando die phpmyadmin heeft gebruikt om
de gegevens op te vragen. Aangeraden wordt om de gegevens die er standaard in komen te staan
(zoals is te zien in het voorbeeld) te verwijderen.
Daar voer je dus straks je code in en klik je vervolgens op START.
Dan krijg je of een foutmelding of een vinkje (waarin de opdracht eventueel te zien is).
Maar om hier de juiste code in te voeren zullen we dus gebruik maken van een
standaard database (inmiddels al bekend) garagebedrijf.
Voordat we aan de slag gaat met de uitleg van de code en het maken van de opdrachten,
staan we nog even stil bij de structuur van de tabellen en hun samenhang.
Een manier om dit overzichtelijk weer te geven, is door gebruik te maken van een uitgebreid tekstdiagram.
Hieronder is nogmaals het ERD en een uitgebreid tekstdiagram gegeven.
Ook in het uitgebreide tekstdiagram vind je de relaties, maar nu ook op het niveau van
de koppeling van de relaties aan de arrtibuten.
De tabelnaam is de eerste regel in iedere kolom, de primaire sleutels zijn in het geel weergegeven.
Als je straks dus problemen ondervindt, dan kun je in het tekstdiagram gebruiken om mogelijke fouten
uit de sql statements te halen. Vaak worden de tabel- en of veldnamen niet juist overgenomen.
Mysql is immers hoofdlettergevoelig.
Basiscommando's
Theorie
In een selectie opdracht slecteer je gegevens uit tabellen. Het meest eenvoudig is een selectie uit één tabel.
Je kunt alle velden uit een tabel laten zien of een slechts een deel van alle velden.
Twee voorbeelden:
Het eerste voorbeeld selecteert alle veldnamen uit de tabel orders_artikelen.
SELECT * FROM order_product;
Het tweede voorbeeld geeft alleen de voornaam en achternaam van een klant.
SELECT voornaam,achternaam FROM klant;
De stuctuur voor een selectie opdracht is dus:
SELECT 'iets' FROM 'een tabel';
Elke SQL-opdracht moet afgesloten met een puntkomma (;). Niet elk database management programma stelt dit verplicht.
Voor de netheid van je opdrachten, raad ik je dit wel aan. Verder zul je in deze module alle sql-voorbeelden aantreffen waarbij de
sql-commando's met hoofdletters zijn geschrevend, dit is echter niet verplicht.
Geef een overzicht van alle producten van het garagebedrijf.
Geef een overzicht van alle klanten van het garagebedrijf.
Geef een overzicht van alle orders. Alleen de id van de klant en van de order selecteren.
Theorie De bovenstaande basis selectie SQL code kan op vele manieren worden uitgebreid om b.v.
selecties precieser te maken, te sorteren, samen te voegen. We starten met de belangrijkste, de WHERE clausule.
De WHERE opdracht geeft de voorwaarden waaraan gegevens in een tabel
moeten voldoen om in de outputtabel te komen.
In het volgende voorbeeld worden alle artikelen weergegeven met een prijs groter dan 10 euro.
SELECT *
FROM product
WHERE prijs > 10
Ten slotte kan de outputtabel gesorteerd worden door het commando ORDER BY.
SELECT *
FROM artikelen
WHERE prijs > 10
ORDER BY artikelomschrijving ASC
Opdrachten
Geef een overzicht van de klanten uit Aalsmeer.
Geef alle klanten uit de tabel klanten waarbij alleen klantnummer, klantvoornaam en klantachternaam is weergegeven. Sorteer bovendien op klantvoornaam.
In welke order_product regels zijn er drie besteld?
Maak een overzicht van alle orders uit de orders tabel, gesorteerd op ordernummer en binnen ordernummer een sortering op klantnummer.
Maak een gesorteerd overzicht van alle order_product regels. Sorteer op aantal, grootste voorop.
Distinct en like
Theorie Andere regels die in de SQL opdrachten tegenkomt is DISTINCT en LIKE.
Met DISTINCT haal je dubbele rijen uit de outputtabel. Met LIKE kun je tekststrings met elkaar
vergelijken desgewenst in combinatie met de wildcards % en/of _.
Het onderstaande voorbeeld laat alle verschillende prijzen groter dan 10 euro
SELECT DISTINCT(prijs)
FROM artikelen
WHERE prijs > 10
Het volgende voorbeeld laat alle klanten zien met een 'oe' in hun achternaam.
SELECT klantachternaam
FROM klanten
WHERE klantachternaam LIKE '%oe%'
Opdrachten
Maak een lijst van de verschillende woonplaatsen uit de tabel KLANT horen.
Welke klanten hebben de letters AA aaneengesloten in hun achternaam?
Geef het artikelnummer en artikelomschrijving van alle artikelen waarvan de artikelomschrijving als derde letter een r hebben.
Welke artikelen hebben de letters GEEN aaneengesloten in hun naam?
Zijn er artikelen die één spatie in hun artikelomschrijving hebben?
Welke artikelen hebben een artikelomschrijving van precies 22 letters lang?
And, Or, In en Between
Theorie
In SQL gebruik je vergelijkingsoperatoren om te bepalen of iets groter, kleiner of gelijk is.
Daarnaast kun je meerdere commando's bij elkaar gebruiken met AND en OR of bepalen of iets wel of niet mag met NOT.
De volgende voorbeelden zullen niet meer bestaan uit de volledige opdracht (syntax).
We gaan ervan uit dat je met de bovenstaande opdrachten en het strokendiagram voldoende geoefend
hebt om te bepalen uit welke tabel je welke gegevens kunt halen.
In de outputtabel komen klanten die als voornaam Cees hebben en als achternaam Dal.
Eventuele familienamen met de naam Dal en anderen met de naam Cees komen dus niet voor.
Het gaat hier 'echt' om deze combinatie die exact zo in de tabel moet voorkomen.
WHERE klantachternaam='Dal' AND klantvoornaam='Cees'
In de outputtabel komen klanten waarvan de achternaam niet Dal is.
WHERE NOT klantachternaam='Dal'
In de outputtabel komen prijzen die groter zijn dan 50 Eur.NB: 50 Eur. telt niet mee.
WHERE prijs > 50
Als je de voor- en achternamen van Cees en Dal wat losser wilt invoeren, dan kun je OR gebruiken.
Met OR kun je dus alle voornamen met Cees en achternamen met Dal weergegeven worden.
Dat betekent dus dat er misschien wel meerdere namen Cees te zien zijn met andere achternamen en andersom.
WHERE klantachternaam='Dal' OR klantvoornaam='Cees'
Andere operatoren zijn: = (gelijk stellen), < (kleiner dan), >=(groter of gelijk aan) en <= ( kleiner dan of gelijk aan), NOT (ongelijk aan)
Opdrachten
Geef een overzicht van alle klanten die niet in Haarlem wonen.
Geef een overzicht van alle artikelen met een prijs boven de 50 Euro.
Theorie In SQL gebruik je IN om meerdere losse waarden van een attribuuttype in de outputtabel te krijgen.
Dit is handig wanneer er meer dan twee waarden zijn. Het gebruik van een OR is dan wat omslachtig.
In de outputtabel komen klanten waarvan de achternaam 'Dal', 'Groenedijk' en 'Vroom'
WHERE klantachternaam IN ('Dal', 'Groenedijk', 'Vroom')
In de outputtabel komen producten namen die aanwezig zijn in order_product regels
SELECT naam FROM product WHERE id IN (SELECT DISTINCT(productid) FROM order_product)
Met BETWEEN kun je tussenliggende waarden bepalen van een attribuuttype. Een AND operator kan ook gebruikt worden, maar een BETWEEN is korter.
WHERE prijs BETWEEN 9 AND 11
Opdrachten
Geef een overzicht van alle artikelen tussen Euro 2 en 7.
Geef de klantgegevens weer van klantnummer 4 en 8?
Wiskundige commando's
Theorie
In SQL kunnen wiskundige berekeningen uitgevoerd worden. Daarvoor zijn verschillende functies paraat. De standaard wiskundige operatoren zijn: +,-,*,/.
De functies in volgorde: MIN() geeft de laagste waarde in de kolom. MAX() geeft de hoogte waarde. SUM() geeft het totaal van de kolom. AVG() geeft het gemiddelde van een kolom. COUNT() telt het aantal geselecteerde items. COUNT(*) telt het aantal rijen.
Andere functies voor wiskundige berekeningen zijn: ABS(x) die de absolute waarde geeft. SQRT(x) geeft de wortel, POWER(x,y) geeft de macht, ROUND(x) rondt x af op een geheel getal. ROUND(x,d) rondt x af op het aantal decimalen d.
Het onderstaande voorbeeld berekent de gemiddelde prijs van de artikelen. AS staat voor Alias waarmee je de kolomnaam in de outputtabel een andere naam geeft.
SELECT AVG(prijs) AS gemiddelde_prijs
FROM artikelen
Opdrachten
Hoeveel klanten telt het garagebedrijf?
Geef de gemiddelde prijs van alle artikelen.
Hoeveel rijen telt de tabel orders_artikelen?
Wat is de laagste artikelprijs?
Having en group-by
Theorie
In SQL kun je ook rijen groeperen. Dit doe je met GROUP BY. Als een betreffende rij is gegroepeerd kan daar ook weer een voorwaarde aan gesteld worden. Deze voorwaarde heet HAVING en hoort bij GROUP BY.
Het onderstaande voorbeeld laat de gemiddelde prijs zien uit de tabel planten, gegroepeerd op soort en waarvan de gemiddelde prijs groter is dan 10 euro.
SELECT artikelomschrijving,AVG(prijs) AS gemiddelde_prijs
FROM artikelen
GROUP BY artikelomschrijving
HAVING AVG(prijs) > 10
Opdrachten
Tel het aantal klanten per woonplaats uit de tabel KLANT.
Idem als opdracht 23 maar geef nu de klanten per woonplaats weer waarvan er meer dan 1 komen.
Tel per product het totaal aantal bestelde producten in de order_product tabel.
SELECT SUM( aantal ), productid
FROM order_product
GROUP BY productid
Voordat we aan de slag gaat met de uitleg van de code en het maken van de opdrachten,
staan we nog even stil bij de structuur van de tabellen en hun samenhang.
Een manier om dit overzichtelijk weer te geven, is door gebruik te maken van een uitgebreid tekstdiagram.
Hieronder is nogmaals het ERD en een uitgebreid tekstdiagram gegeven.
Ook in het uitgebreide tekstdiagram vind je de relaties, maar nu ook op het niveau van
de koppeling van de relaties aan de arrtibuten.
De tabelnaam is de eerste regel in iedere kolom, de primaire sleutels zijn in het geel weergegeven.
Als je straks dus problemen ondervindt, dan kun je in het tekstdiagram gebruiken om mogelijke fouten
uit de sql statements te halen. Vaak worden de tabel- en of veldnamen niet juist overgenomen.
Mysql is immers hoofdlettergevoelig.