Introductie SQL | Basiscommando's | Distinct en like | And, Or, In en Between | Wiskundige commando's | Having en group-by |
|
|
Inleiding |
|
---|---|
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:
|
|
|
|
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. |
|
Opdrachten |
Voor het maken van de opdrachten is het nodig
StructuredQueryLanguage(SQL).htm
te bestuderen.
|
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 > 10Ten slotte kan de outputtabel gesorteerd worden door het commando ORDER BY. SELECT * FROM artikelen WHERE prijs > 10 ORDER BY artikelomschrijving ASC |
|
Opdrachten |
|
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 > 10Het volgende voorbeeld laat alle klanten zien met een 'oe' in hun achternaam. SELECT klantachternaam FROM klanten WHERE klantachternaam LIKE '%oe%' |
|
Opdrachten |
|
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.
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 > 50Als 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 |
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 |
|
|
|
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 |
|
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 |
|