Informaticasite van het Lauwers College te Buitenpost                 © R.J. van der Beek
 

  9.1 Databases, inleiding

We leven in een informatietijdperk. Informatie speelt een steeds grotere rol in onze samenleving en wij zijn er steeds meer van afhankelijk. Dankzij de uitvinding van de computer kunnen we steeds grotere hoeveelheden gegevens verzamelen en deze verwerken tot informatie.
Een belangrijke rol hierin speelt het begrip database.
Een database of gegevensbank is niets anders een verzameling van "gegevens" die wordt bijgehouden.
Databases vinden we overal, en ze zijn er in alle soorten en maten.
Een adressenboekje of een telefoonlijst of een verjaardagskalender zijn voorbeelden van eenvoudige databases.
Een ingewikkelde database is die van de schooladministratie.
Vroeger werden die gegevens op kaarten bijgehouden. Voor elke leerling had je een kaart met daarop de naam, het adres, de woonplaats, telefoon, en nog veel meer gegevens.
Vroeger werden ook de cijfers van de leerlingen op de administratie bijgehouden, maar dan alleen de rapportcijfers. Die werden op de administratie op speciale rapportlijsten bijgehouden; het was teveel werk om alle cijfers op die lijsten bij te houden.

Tegenwoordig wordt dat allemaal met behulp van de computer gedaan.
Er bestaan een aantal programma's om databases mee te maken en bij te houden. Zo'n programma wordt wel een DataBase Management Systeem (DBMS) genoemd.
Een aantal bekende DBMS'en zijn:
  • Access
  • Oracle
  • Filemaker
  • Solid
  • MySQL
De manier waarop de gegevens worden vastgelegd kan op verschillende manieren plaatsvinden. We zeggen dat wel zo: er zijn verschillende gegevensmodellen of datamodellen.
Eén van de manieren om de gegevens vast te leggen is om deze in tabellen te plaatsen; dat wordt het relationele model genoemd.
Als je een programma hebt dat de gegevens met behulp van tabellen vastlegt, dus met het relationele model werkt, dan wordt dat een Relationeel DataBase Management Systeem (RDBMS) genoemd.

  9.2 Het relationele model

Wij gaan werken met een RDBMS, een Relationeel DataBase Management Systeem.
Daarin worden de de gegevens vastgelegd met behulp van tabellen en die tabellen hebben ook nog onderlinge relaties.

We bekijken als voorbeeld een database van een Tennisvereniging.
De database van die tennisvereniging bestaat uit een aantal tabellen. Ten eerste de tabel met de gegevens van alle leden, die zie je hieronder. Deze tabel noemen we leden.

lidnrnaamwoonplaatsgeborenleeftijdlidsoortscore
1AnnieBuitenpost1944-06-0262senior2075
2WillemVeenklooster1992-09-1314jeugdlid987
3HillegienKollum1968-01-1138senior1598
4MargreetKollum1980-09-1826aktielid234
5ElineBuitenpost1996-11-0410jeugdlid435
6GerritTwijzel1958-05-0448senior5989
7FemkeAugustinusga1993-09-1913jeugdlid798
8DonaBurum2000-05-156mkmt155
9ChrisBurum1998-12-127mkmt236
10DieuwkeBuitenpost1959-09-0647senior7345
11KeesVeenklooster1965-07-2741senior6345
12LeonAugustinusga1980-06-0626aktielid380
13SabineDrogeham1970-11-1036senior4999
14FemkeDrogeham1979-07-0627senior3768

Verder hebben we ook nog een tabel waarin de competities staan, waaraan sommige leden meedoen.
Deze tabel geven we aan met de naam competitiesoort en je ziet hem hieronder.

soortspeeldagaanvoerder
HDdonderdag11
HEzaterdag6
DDdinsdag3
JDwoensdag2
GDzaterdag 

Iedere tabel heeft een naam. Zo zijn de namen van bovenstaande tabellen leden en competitiesoort.

Een tabel bestaat uit rijen en kolommen.
Een rij gegevens noemen we ook wel een tupel of record, het is te vergelijken met een kaart uit een kaartenbak.
De gegevens uit een rij horen bijelkaar.
De kolommen noemen we ook wel attributen of velden.

Bovenstaande tabellen staan niet los van elkaar, er bestaat een bepaald verband tussen, ze hebben een relatie.
Want als je naar de tweede tabel kijkt, die van de competitiesoort, dan zie je dat er bij de eerste rij in de kolom soort HD staat.
HD staat voor "heren dubbel". Verder zie je in de laatste kolom dat de aanvoerder nummer 11 is.
Als je wilt weten wie daarmee bedoeld wordt moet je naar de eerste tabel kijken; daar zie je dat lidnummer 11 Kees uit Veenklooster is.
Als je de gegevens van de tabel met de competitiesoort wilt analyseren dan heb je de tabel met de leden erbij nodig, omdat er een relatie tussen die twee bestaat.

Het relationele model en het resultaat daarvan, een relationele database, werd voor het eerst door dr. Edgar F. Codd (zie hierboven) in 1970 gentroduceerd.

De belangrijkste taken van een databasesysteem zijn de volgende:
  • Het opslaan van gegevens, en het mogelijk maken van wijzigen van de gegevens.
  • Het kunnen opvragen van gegevens, en het verwerken van informatie (je moet bijvoorbeeld lijstjes kunnen maken van dingen die je nodig hebt of weten wilt)
  • Het voorkomen van fouten in de database. Natuurlijk kan een database niet controleren of je een typfout in een naam hebt gemaakt, maar er kunnen wel bepaalde dingen worden gecontroleerd.
    Het zou bij bovenstaande database bijvoorbeeld kunnen gebeuren dat je twee leden hetzelfde nummer geeft; maar dan kan de computer wel een foutmelding geven.
    Het zou bij diezelfde database ook kunnen gebeuren dat je bij de tabel van de competitiesoort in de aanvoerder-kolom een nummer gebruikt dat geen van de leden heeft. Dat kan de computer controleren en een foutmelding geven.
    We noemen dit het bewaken van de integriteit van de database. Hiermee bedoelen we dat het systeem kan controleren of de ingevoerde gegevens aan bepaalde regels voldoen.
Je zag in bovenstaande database dat elk lid in de tabel leden met een nummer werd aangegeven, het lidnummer.
Zoiets gebeurt heel vaak in tabellen, dat de rijen met een nummer worden aangegeven. Daar zijn twee redenen voor.
  • Ten eerste kan er dan geen verwarring optreden als er leden zijn met dezelfde naam. Het nummer is bij iedereen verschillend (dat is tenminste wel de bedoeling), het is uniek voor elk lid.
    Een goed database-programma kan ook controleren of die nummers bij iedereen verschillend zijn.
    Het kan zelfs zo gemaakt worden dat je dat nummer zelf niet hoeft in te voeren, maar dat de computer dat nummer automatisch geeft aan een nieuw lid.
  • Het is heel gemakkelijk om met een nummer te verwijzen naar een rij uit een andere tabel.
    Zoals in bovenstaand voorbeeld: de aanvoerder in de tabel competitiesoort wordt met een nummer aangegeven, en dat nummer is het lidnummer uit de andere tabel.

  9.3 Databases in Access

Eén van de bekendste RDBMS'en is Access. Daar gaan we eerst aandacht aan besteden.

Start het programma Access, en open de database tennisvereniging. Die database kun je hier downloaden.
(Een database in Access heeft als extensie .mdb. Dat is de afkorting van Microssoft DataBase.)

Je ziet dan in Access 2003 onderstaand beginscherm.



In Access 2007 ziet het er als volgt uit:



Je ziet in Access 2003 links zeven objecten staan:
Tabellen, Query's, Formulieren, Rapporten, Pagina's, Macro's en Modules.

In Access 2007 moet je eerst op het pijltje achter Tabbellen klikken, dan krijg je onderaan ook Tabellen, Query's, Formulieren, Rapporten te zien.



Klik op het object Tabellen, dan krijg je de namen van de aanwezige tabellen te zien.
Klik op de tabel LEDEN en dan op Openen (of dubbelklik op de tabel LEDEN).
Dan krijg je de tabel in beeld en dan kun je daarin wijzigen en toevoegen.


  • De cursor kan met de muis verplaatst worden.
  • Wil je naar het volgende veld dan kan dat ook met de pijltoets, of met de tabtoets of met de entertoets.
  • Verder kun je gebruik maken van de statusbalk onderaan in het venster.



    • Klik je op het pijltje naar rechts dan ga je naar het volgende record.
    • Klik je op het pijltje naar rechts met het streepje er achter dan ga je naar het laatste record.
    • Wil je bijvoorbeeld naar record nr. 14 dan kun je dat nummer ook intikken in het venstertje met het recordnummer.
  • Onderaan is altijd een lege regel. Wil je nieuwe gegevens invoeren, dan kun je dat daar doen.
    Je komt ook bij die lege regel als je in de statusregel op het pijltje naar rechts met het sterretje er achter klikt.
  • Dingen die je hebt ingetypt worden vastgelegd zodra je naar een ander record gaat.
  • Als je een record wilt verwijderen, dan moet je dat record eerst selecteren.
    Dat doe je door links vooraan op de rijkop te klikken.
    Wil je het geselecteerde record verwijderen, klik dan op de rechtermuisknop, en kies Record verwijderen in het snelmenu, of druk op de Delete-knop.
  • Wil je een hele kolom selecteren, klik dan bovenaan op de kolomkop (waarin de veldnaam staat).
    Wil je de geselecteerde kolom verwijderen, klik dan op de rechtermuisknop, en kies Kolom verwijderen in het snelmenu.
  • Je kunt de kolombreedte op verschillende manieren breder (of smaller) maken.
    • Zet de aanwijzer in de kolomrand, op de scheiding tussen twee kolommen en wel op de rechterkant van de kolom. Sleep nu die rand naar rechts (of naar links als de kolom smaller moet worden).
    • Selecteer een kolom, klik op de rechter muisknop en kies in het snelmenu Kolombreedte. Kies dan Passend maken of geef met een getal aan wat de kolombreedte moet worden.
  • Je kunt ook de kolomnaam veranderen: dubbelklik op die kolomnaam, en breng de wijziging aan.
  • Een kolom verplaatsen kan op de volgende manier:
    Selecteer een kolom.
    Klik opnieuw op de kolomkop en sleep de kolom naar de gewenste plaats.

  9.4 Access: Sorteren, Filteren en Zoeken

Sorteren
Als je de gegevens van een tabel wilt sorteren dan zet je de cursor eerst in de kolom, waarop gesorteerd moet worden, en dan klik je op
Klik je op het linker icoontje dan wordt oplopend gesorteerd, met het rechter aflopend.

Filteren
Als je niet alle rijen van een tabel wilt zien, maar alleen de rijen die dezelfde waarde in een bepaalde kolom hebben dan kan dat m.b.v. een selectiefilter.
Zet je de cursor eerst in de kolom en de rij waarop je wilt filteren en klik dan op het linker icoontje van

Als je bijv. in de tabel LEDEN alleen de leden uit Buitenpost wilt zien, dan zet je de cursor in de kolom WOONPLAATS op iemand uit Buitenpost, en als je dan op het filter-icoontje klikt blijven alleen de leden uit Buitenpost staan
(in Access 2007 moet je eerst ook nog klikken op "is gelijk aan Buitenpost")



Als je daarna op het rechter icoontje van klikt wordt het filter opgeheven, en zie je alle leden weer.

Zoeken
Als je naar bepaalde gegevens wilt zoeken dan zet je de cursor eerst in de kolom waarin je wilt zoeken en dan klik je op de verrekijker.
  • Als je bijv. in de tabel LEDEN wilt weten of er ook een Greet of Greetje of Margreet bij zit, dan zet je de cursor in de kolom NAAM, en als je dan op het zoek-icoontje klikt verschijnt het volgende venster.



  • Als je dan op Volgende zoeken klikt dan wordt er n persoon gevonden, namelijk Margreet.
  • Denk er wel om dat je achter Waar eerst Gedeelte van Veld (of Begin van Veld) uitkiest.
  • Wil je dat er niet alleen in de kolom NAAM gezocht wordt maar overal in de tabel, dan moet je achter Zoeken in kiezen voor LEDEN: Tabel

  • Verder kun je ook nog aangeven of er vanaf de cursor omhoog of omlaag wordt gezocht, of dat alles wordt doorzocht.
  • En als je een vinkje hebt gezet voor Identieke hoofdletters/kleine letters en je hebt aangegeven dat er naar Greet gezocht moet worden, dan zal Margreet niet gevonden worden.

  9.5 Access: Queries in de ontwerpweergave

Als je niet alle rijen van een tabel wilt zien, maar alleen de rijen die voldoen aan bepaalde voorwaarden dan kan dat m.b.v. een query.

Queries met één tabel
Als je bijv. in de tabel LEDEN alleen de leerlingen uit Buitenpost wilt zien, die ouder dan 20 jaar zijn, dan kan dat niet met een selectiefilter. Maar m.b.v. een query kan het wel.
  • Kies in Access 2003 in het database-venster Query's uit, en klik op Nieuw.



  • Klik dan op Query maken in ontwerpweergave, en dan op Ontwerpweergave en dan op OK.

    (In Access 2007 klik je op het pijltje achter Tabellen, en dan op Query's. Dan klik je in het menu op het tabblad "Maken", en dan op "Queryontwerp")
  • Dan verschijnt het volgende venster:



  • Klik in het venster Tabel Weergeven op de naam van de tabel die je nodig hebt, dat is in ons geval LEDEN, klik dan op Toevoegen en dan op Sluiten.
    Dan verdwijnt het venster Tabel toevoegen, en in het venster Query worden de velden van de tabel LEDEN getoond.
  • Als je op een hokje achter Veld klikt dan kun je een veld uitkiezen. Kies in de eerste kolom NAAM uit.
  • Verder vul je in de eerste kolom niets in, de naam hoeft niet aan bepaalde voorwaarden te voldoen.
  • Klik in de tweede kolom op het hokje achter Veld, en kies nu WOONPLAATS uit.
  • Achter Criteria vul je "=Buitenpost" in.
    Er wordt dan voor gezorgd dat alleen de leden uit Buitenpost getoond worden.
  • Klik in de derde kolom op het hokje achter Veld, en kies dan LEEFTIJD uit.
  • Achter Criteria vul je ">20" in.
    Er wordt dan voor gezorgd dat alleen de leden worden afgedrukt die een leeftijd hebben die groter dan 20 is.



  • Je kunt verder op de bovenste regel de velden uitkiezen, die afgedrukt moeten worden.
  • Je kunt de sorteervolgorde ook nog aangeven.
    Als je bijv. wilt dat er op Naam gesorteerd wordt dan klik je op het hokje achter Sorteervolgorde onder Naam. En dan kun je kiezen uit Oplopend of Aflopend. Kies oplopend.
  • Als je vervolgens klikt op het uitroepteken in de werkbalk dan verschijnen de gevraagde gegevens.



Queries met twee tabellen
Als je de namen van alle competitiesoorten met de bijbehorende aanvoerders wilt zien, en de plaats waar ze wonen, dan heb je twee tabellen nodig, namelijk de tabel COMPETITIESOORT en de tabel LEDEN want in die laatste tabel staan de namen en de woonplaatsen.
  • Kies in het database-venster Query's uit, en dan op Ontwerpen en dan op OK.
    (In Access 2007 klik je op het pijltje achter Tabellen, en dan op Query's. Dan klik je in het menu op "Maken", en dan op "Queryontwerp")
  • Klik in het venster Tabel Weergeven eerst op de tabel LEDEN, en klik dan op Toevoegen.
    Klik dan op de tabel COMPETITIESOORT, klik weer op Toevoegen, en klik dan op Sluiten.
  • Klik in het Query-venster in de eerste kolom op het hokje achter Veld, dan kun je een veld uitkiezen. Je kunt alle velden van de tabel LEDEN uitkiezen, maar ook die van de tabel COMPETITIESOORT. De naam van de tabel staat steeds voor die van het veld, en het scheidingsteken is een punt.
    Kies in de eerste kolom COMPETITIESOORT.SOORT uit.
  • Klik in de tweede kolom op het hokje achter Veld, en kies nu COMPETITIESOORT.AANVOERDER uit.
  • Klik in de derde kolom op het hokje achter Veld, en kies LEDEN.NAAM uit.
  • Klik in de vierde kolom op het hokje achter Veld, en kies LEDEN.WOONPLAATS uit.



  • Klik vervolgens op het uitroepteken in de werkbalk, dan verschijnen de gevraagde gegevens.
    Het programma heeft de juiste naam van de aanvoerder bij de juiste competitiesoort gezocht, want het programma weet welke relatie er tussen de tabellen bestaat. Hij doet dat natuurlijk m.b.v. het lidnummer.



  9.6 SQL, inleiding

In elk RDBMS bestaat een zogenaamde vraagtaal, een taal waarmee je kunt communiceren met de database.
Deze taal heet SQL, dat is de afkorting van Structured Query Language.
SQL is een taal die speciaal voor relationele databases is ontwikkeld. Deze taal werd voor het eerst gebruikt door IBM in de 70-er jaren, maar werd toen nog SEQUEL (Structured English QUEry Language) genoemd.
Later werd de taal gestandaardiseerd en in iedere databasesysteem geïmplementeerd.
Ieder systeem heeft overigens wel z'n eigen variant of dialect, maar de verschillen zijn gelukkig klein.

SQL bestaat eigenlijk uit vier subtalen:
  • DRL (Data Retrieval Language)
    Met behulp van deze taal kunnen we de gegevens opvragen.
  • DML (Data Manipulation Language)
    Met behulp van deze taal kunnen we zogenaamde tabelmanipulaties uitvoeren.
    Denk bijvoorbeeld aan het toevoegen en verwijderen van rijen, gegevensinvoer e.d.
  • DDL: Data Definition Language
    Met behulp van deze taal kunnen we de benodigde tabellen maken, veranderen en verwijderen.
    Hiermee maak of verander je de structuur van de tabellen.
  • DCL (Data Control Language)
    Met behulp van deze taal kunnen we de authorisatie regelen.
De commando's die gebruikt worden heten query's.

Wij gaan beginnen met DRL (Data Retrieval Language), we gaan dus bekijken hoe we gegevens op kunnen vragen.
Je hebt in de vorige paragraaf gezien dat je in Access vragen kunt laten beantwoorden m.b.v. Queries in de ontwerpweergave.

Maar je kunt in Access ook vragen laten beantwoorden m.b.v. SQL. Je moet dan op de volgende manier beginnen:
  • Kies in het database-venster Query's uit, en klik op Ontwerpen en dan op OK.
    (In Access 2007 klik je op het pijltje achter Tabellen, en dan op Query's. Dan klik je in het menu op "Maken", en dan op "Queryontwerp")
  • Klik in het venster Tabel Weergeven direkt op Sluiten.
  • Dan is er bij Access 2003 in de werkblak (helemaal links) een icoontje bijgekomen met SQL. Als je op het pijltje achter SQL klikt verschijnt er een menu onder, en daarin klik je op SQL
    (je kunt ook in de menubalk op Beeld klikken, en dan op SQL).

    In Access 2007 zijn er in de werkbalk helemaal links twee icoontjes bijgekomen, één met SQL en één met een uitroepteken. Als je op het pijltje onder Weergave klikt verschijnen er twee weergavemogelijkheden onder, en daarin klik je op SQL.
  • Dan verschijnt er een (bijna leeg) venster, waarin je de query in kunt typen. De queries, waarmee je dingen op kunt vragen, beginnen altijd met het woord SELECT, en dat staat er al.
    Verder eindigt elke query met een punt-komma, en die staat er ook al.
    De rest moet je zelf intypen.

  § 9.7. SQL: Select, distinct, order by

Als je een lijstje van de namen en de scores uit de tabel wilt dan kan dat met de volgende query:

SELECT naam, score FROM leden ;

Tik die query in in het SQL-venster, en klik dan op het uitroepteken in de werkbalk. Dan krijg je het antwoord op je vraag te zien.


Je kunt de queries ook zonder Access uitproberen. Als je de queries hieronder invoert, dan wordt hij door MySQL verwerkt.

Voer hier de query in:

Misschien ontdek je dat de tabellen niet meer de juiste gegevens bevatten. Je kunt namelijk zelf de inhoud van de tabellen veranderen m.b.v. INSERT, UPDATE en DELETE.
Wil je dat de tabellen de oorspronkelijke gegevens weer bevatten, klik dan op oorspronkelijke tabellen herstellen


Het resultaat is dan:
naamscore
Annie2075
Willem987
Hillegien1598
Margreet234
Eline435
Gerrit5989
Femke798
Dona155
Chris236
Dieuwke7345
Kees6345
Leon380
Sabine4999
Femke3768

Als je klikt op Beeld en dan op SQL, dan kun je de volgende SQL-query invoeren.
Als je de lidsoorten uit de tabel wilt dan kan dat met de volgende query:

SELECT lidsoort FROM leden ;

Het resultaat is dan:

lidsoort
senior
jeugdlid
senior
aktielid
jeugdlid
senior
jeugdlid
mkmt
mkmt
senior
senior
aktielid
senior
senior

DISTINCT
Je ziet dat sommige lidsoorten meerdere keren voorkomen.
Dat komt omdat van elke rij de lidsoort wordt afgedrukt.
Om te voorkomen dat we dubbele resultaten krijgen gebruiken we het commando DISTINCT. De query komt er dan als volgt uit te zien:

SELECT DISTINCT lidsoort FROM leden ;

Het resultaat is dan:

lidsoort
senior
jeugdlid
aktielid
mkmt

Als je alle gegevens uit alle kolommen wilt laten afdrukken dan kun je alle kolommen noemen in je query, dus:

SELECT lidnr,naam,woonplaats,geboren,leeftijd,lidsoort,score FROM leden ;

Of je maakt gebruik van een sterretje (*). De query ziet er dan als volgt uit:

SELECT * FROM leden ;

Je krijgt dan de volledige tabel zien die in paragraaf 9.2 staat.

ORDER BY
Gegevens uit een tabel kun je ook laten sorteren, daarvoor gebruik je het commando ORDER_BY.
Het sorteren kan op twee manieren: oplopend en aflopend, in het engels is dat ascending en descending. In SQL wordt dat afgekort tot ASC en DESC

Stel je wilt een lijstje van de namen uit de ledentabel met de scores, en dan gesorteerd op de scores met de persoon met de hoogste score bovenaan.
De query komt er dan zo uit te zien:

SELECT naam,score FROM leden ORDER BY score DESC ;

Je krijgt hetzelfde resultaat met de volgende query:
SELECT naam,score FROM leden ORDER BY 2 DESC ;

De 2 in deze query staat voor de als tweede genoemde kolom, dus de score.

Je krijgt dan het volgende lijstje:

naamscore
Dieuwke7345
Kees6345
Gerrit5989
Sabine4999
Femke3768
Annie2075
Hillegien1598
Willem987
Femke798
Eline435
Leon380
Chris236
Margreet234
Dona155

Als je geen asc of desc bij order by gebruikt dan wordt er oplopend gesorteerd, je hoeft ASC dus eigenlijk nooit te gebruiken.

  9.8 SQL: Voorwaarden: where, and, or, not, like, is null, limit

Als je bepaalde rijen wilt laten afdrukken die voldoen aan één of meerdere voorwaarden, dan gebruik je het commando WHERE.
In het volgende voorbeeld maken we een lijstje van de leden die in Buitenpost wonen.
De query wordt:

SELECT naam,lidsoort FROM leden WHERE woonplaats = 'Buitenpost' ;

We krijgen dan dit resultaat:

naamlidsoort
Anniesenior
Elinejeugdlid
Dieuwkesenior

De basis syntax voor de query is:

SELECT kolommen FROM tabel WHERE voorwaarde ;

In de voorwaarde zit altijd een operator, je kunt de volgende operatoren in SQL gebruiken:

operator betekenis
= is gelijk aan
< is kleiner dan
> is groter dan
<= is kleiner of gelijk aan
>= is groter of gelijk aan
<> is niet gelijk aan
between ligt tussen
in is één van de volgende waarden

AND, OR, NOT
Als er aan meerdere voorwaarden moet worden voldaan kun je gebruik maken van AND, OR en NOT.

Als je alle namen wilt hebben van de leden die 30 jaar of ouder zijn en die niet in Buitenpost wonen, dan kan dat met de volgende query:

SELECT naam, leeftijd, woonplaats FROM leden WHERE leeftijd >= 30 AND woonplaats <> 'Buitenpost' ;

Denk er om dat 30 niet tussen aanhalingstekens geplaatst moet worden, omdat de leeftijd numeriek is.
(Lidnr, leeftijd en score zijn numeriek, dus getallen. Geboren is van het type date, naam, woonplaats en lidsoort zijn van het type tekst)

Je krijgt dan het volgende lijstje:

naamleeftijdwoonplaats
Hillegien38Kollum
Gerrit48Twijzel
Kees41Veenklooster
Sabine36Drogeham

Als je alleen de namen willen hebben van de leden die ouder dan 60 jaar zijn of een score van meer dan 5000 hebben, dan kun je die krijgen met de volgende query:

SELECT naam, leeftijd, score FROM leden WHERE leeftijd > 60 OR score > 5000 ;

Deze query heeft als resultaat:

naamleeftijdscore
Annie622075
Gerrit485989
Dieuwke477345
Kees416345

Annie komt in het lijstje voor omdat ze ouder dan 60 is. En de anderen voldoen aan de tweede voorwaarde, hun score zit namelijk boven de 5000.

BETWEEN
Met BETWEEN moet de kolomwaarde tussen twee waarden liggen.
Stel dat je een lijstje wilt van alle leden die een leeftijd hebben tussen de 20 en de 40. Dat kan met de volgende query:

SELECT naam, leeftijd FROM leden WHERE leeftijd BETWEEN 20 AND 40 ;

Het resultaat is:
naamleeftijd
Hillegien38
Margreet26
Leon26
Sabine36
Femke27


IN
Met het commando IN kun je hetzelfde bereiken als met OR maar de query wordt dan korter.
Als je de leden wilt afdrukken die in Kollum of Buitenpost of Veenklooster wonen, dan kun je als voorwaarde gebruiken:
where woonplaats="Kollum" or woonplaats="Buitenpost" or woonplaats="Veenklooster")
Maar het kan ook met de volgende voorwaarde:
where woonplaats in ("Kollum", "Buitenpost", "Veenklooster")

SELECT naam, woonplaats FROM leden WHERE woonplaats IN ('Kollum', 'Buitenpost','Veenklooster') ;

Resultaat:
naamwoonplaats
AnnieBuitenpost
WillemVeenklooster
HillegienKollum
MargreetKollum
ElineBuitenpost
DieuwkeBuitenpost
KeesVeenklooster

NOT
Met NOT kun je de leden krijgen die niet aan een bepaalde voorwaarde voldoen.
Stel dat je alle leden wilt hebben die niet in Buitenpost wonen.
Dat kan m.b.v. de volgende query:

SELECT * FROM leden WHERE NOT woonplaats = 'Buitenpost' ;

Je kunt hetzelfde lijstje krijgen m.b.v. deze query:

SELECT * FROM leden WHERE woonplaats <> 'Buitenpost' ;

Het resultaat is:

lidnrnaamwoonplaatsgeborenleeftijdlidsoortscore
2WillemVeenklooster1992-09-1314jeugdlid987
3HillegienKollum1968-01-1138senior1598
4MargreetKollum1980-09-1826aktielid234
6GerritTwijzel1958-05-0448senior5989
7FemkeAugustinusga1993-09-1913jeugdlid798
8DonaBurum2000-05-156mkmt155
9ChrisBurum1998-12-127mkmt236
11KeesVeenklooster1965-07-2741senior6345
12LeonAugustinusga1980-06-0626aktielid380
13SabineDrogeham1970-11-1036senior4999
14FemkeDrogeham1979-07-0627senior3768

LIKE
Met LIKE kun je selecteren op gedeelten van de tekst.
Stel dat je een lijstje wilt van de leden waarvan de naam met een D begint. Dat kan met de volgende query:

SELECT * FROM leden WHERE naam LIKE 'D%' ;

Je ziet dat er na LIKE staat: 'D%'
Het % teken is een zogenaamde wildcard en staat voor een willekeurig aantal tekens.
Het betekent dus dat de naam met een D moet beginnen, en wat er achter staat doet er niet toe, dat mag alles zijn.
(In Access moet je in plaats van het procentteken % een sterretje * gebruiken)
Je krijgt dan het volgende:

lidnrnaamwoonplaatsgeborenleeftijdlidsoortscore
8DonaBurum2000-05-156mkmt155
10DieuwkeBuitenpost1959-09-0647senior7345

Als je alle namen en hun woonplaatsen wilt hebben van mensen die een dubbele e (ee) in hun woonplaats hebben, dan gebruik je de volgende query:

SELECT naam, woonplaats FROM leden WHERE woonplaats LIKE '%ee%' ;

Deze query levert onderstaand resultaat op:

naamwoonplaats
WillemVeenklooster
KeesVeenklooster

In combinatie met LIKE kun je nog een andere wildcard gebruiken, namelijk het teken _ , de underscore. Dat teken staat voor één willekeurig teken (en een procentteken (%) staat voor meerdere willekeurige tekens (dat kan ook één of nul zijn).
(In Access moet je in plaats van het underscoreteken _ een procentteken % gebruiken)

Stel dat we alle namen willen hebben van de leden waarvan de naam uit vier letters bestaat. De query wordt dan:

SELECT naam FROM leden WHERE naam LIKE '____' ;
(Achter LIKE staan vier underscore-tekens achter elkaar!)

Dit geeft als resultaat:

naam
Dona
Kees
Leon

We kunnen de beide wildcards ook combineren. Stel dat we de namen van de leden willen hebben waarvan de voorlaatste letter een e is.
Die krijg je m.b.v. de volgende query:

SELECT naam FROM leden WHERE naam LIKE '%e_' ;

Dit geeft als resultaat:

naam
Willem
Hillegien
Margreet
Kees

IS NULL
Als je wilt controleren m.b.v. SQL of een kolom is ingevuld dan gebruik je IS NULL
Je kunt bijvoorbeeld de volgende query uitproberen:

SELECT naam FROM leden WHERE score IS NULL ;

Het resultaat is dan dat er geen enkele rij wordt afgedrukt, want overal is een score ingevuld.

LIMIT
Je kunt het aantal rijen dat wordt afgedrukt beperken m.b.v. LIMIT.
Stel dat je de naam van de persoon wilt hebben die de hoogste score heeft. Je moet dan een query maken die sorteert op score, en die maar één rij afdrukt.
De query komt er dan zo uit te zien:

SELECT naam, score FROM leden ORDER BY score DESC LIMIT 1 ;

Deze query levert het volgende op:

naamscore
Dieuwke7345

LIMIT 1 geeft aan dat de query moet stoppen na de eerste rij.

Stel dat je de drie mensen wilt hebben, die wat score betreft op de plaatsen 3, 4 en 5 staan. Dus de twee hoogsten niet, maar de drie die daarop volgen. Dat kan met de volgende query:

SELECT naam, score FROM leden ORDER BY score DESC LIMIT 2,3 ;

Je ziet dat er nu na LIMIT 2,3 staat; dat betekent dat er wordt begonnen met rij nummer 2 (dat is de derde rij want er wordt begonnen met tellen bij 0, de eerste rij heeft nummer 0 en de tweede rij heeft nummer 1), en er worden 3 rijen afgedrukt.
Bovenstaande query geeft als resultaat:
naamscore
Gerrit5989
Sabine4999
Femke3768

Het gebruik van LIMIT is geen ANSI-SQL, maar een functie van MySQL.
Het zal dus waarschijnlijk niet in ander DBMS'en werken.

  9.9 SQL: Functies in SQL, alias (AS)

De taal SQL kent een aantal functies die je kunt gebruiken om berekeningen uit te laten voeren, of om het aantal rijen te tellen, of om de kolommen op een bepaalde manier af te drukken.

Als je de volgende query gebruikt dan wordt de gemiddelde score van alle leden afgedrukt:
(AVG is de afkorting van average, en dat betekent: gemiddelde)

SELECT AVG(score ) FROM leden ;

Het resultaat van die query is:

AVG(score )
2524.5714

COUNT
Een ander voorbeeld van een functie in SQL: met de volgende query wordt het aantal leden afgedrukt:

SELECT COUNT(* ) FROM leden ;

Het resultaat van die query is:

COUNT(* )
14

AS
Je ziet dat de kop van de lijsten hierboven, bij het gebruik van functies, weinigzeggend is voor mensen die geen verstand hebben van SQL.
Je kunt de namen van de kolommen een andere naam geven, en die naam verschijnt dan ook boven de lijst.
Die andere naam wordt dan wel een alias genoemd, en dat kan met behulp van AS.

Als je de vorige sql-query als volgt wijzigt:

SELECT COUNT(*) AS aantal_leden FROM leden ;

dan is het resultaat:

aantal_leden
14

En als je de query daarvoor verandert in:

SELECT AVG (score) AS gemiddelde_score FROM leden ;

dan is het resultaat:

gemiddelde_score
2524.5714

Een aantal paragrafen verderop, bij het gebruik van meerdere tabellen door elkaar, zul je zien dat kolommen hernoemen m.b.v. AS ook heel handig is om queries overzichtelijk te houden.

ROUND
Nog een voorbeeld van een functie in SQL: ROUND(kolom, aantal decimalen) zorgt ervoor dat de getallen in de betreffende kolom worden afgerond op het aantal decimalen dat je hebt opgegeven.

Voorbeeld:

SELECT ROUND(AVG(leeftijd), 1) AS gemiddelde_leeftijd FROM leden ;

levert als resultaat de gemiddelde leeftijd, afgerond op één decimaal:

gemiddelde_leeftijd
28.6

LEN
Een andere functie is LEN(kolomnaam) : geeft het aantal tekens van de woorden in de kolom.
In MySQL wordt gebruik gemaakt van de functie LENGTH( ) in plaats van LEN( )

Voorbeeld:

SELECT naam, LEN(naam) AS aantal_letters_van_de_naam FROM leden ;

geeft als resultaat :

naamaantal_letters_van_de_naam
Annie5
Willem6
Hillegien9
Margreet8
Eline5
Gerrit6
Femke5
Dona4
Chris5
Dieuwke7
Kees4
Leon4
Sabine6
Femke5

UCASE
Nog een voorbeeld: UCASE(kolomnaam) geeft de tekst in de kolom in hoofdletters.

SELECT UCASE(naam ) FROM leden WHERE woonplaats = 'Buitenpost' ;

geeft als resultaat:

UCASE(naam )
ANNIE
ELINE
DIEUWKE

CURRENT_DATE
Er zijn ook speciale functies voor de datum en de tijd.
Voorbeeld: CURRENT_DATE( ) geeft de huidige systeemdatum als resultaat.

De query:

SELECT CURRENT_DATE() AS datum ;

Resultaat:
datum
2006-11-22

BEREKENINGEN
Het is mogelijk om direct in de query berekeningen uit te voeren met waarden.
Je kunt daarbij optellen ( + ), aftrekken ( - ), delen ( / ) en vermenigvuldigen ( * )

Stel dat je bij de scores van sommige leden 100 wilt optellen, en wel die leden waarbij de score kleiner is dan 100 maal de leeftijd, dan kan dat met de volgende query:

SELECT score + 100 AS score_plus_100 FROM leden WHERE (leeftijd * 100 > score) ;

Dit geeft als resultaat:
naamscore_plus_10
Gerrit6089
Dieuwke7445
Kees6445
Sabine5099
Femke3868

  9.10 SQL: Groeperen, group by en having

In de vorige paragraaf heb je gezien dat je het totaal aantal rijen van een tabel kunt laten tellen en afdrukken.
En je kunt de som van alle getallen in een kolom optellen, enz.
Maar je kunt de tabel ook indelen in groepjes van dezelfde soort, en de aantallen in die groepjes laten tellen en afdrukken (of optellen, of het gemiddelde bepalen, enz).
Stel dat je een lijstje wilt van alle woonplaatsen, met per plaats het aantal leden dat daar woont.
Dat kan met de volgende sql-query:

SELECT woonplaats , COUNT(*) AS aantal_leden FROM leden GROUP BY woonplaats ;

Dit geeft als resultaat:
woonplaatsaantal_leden
Augustinusga2
Buitenpost3
Burum2
Drogeham2
Kollum2
Twijzel1
Veenklooster2

Stel dat je een lijstje wilt van de lidsoorten en de totale score per lidsoort. Dit kan met de volgende query:

SELECT lidsoort, SUM(score) AS totale_score FROM leden GROUP BY lidsoort ;

En het resultaat is:
lidsoorttotale_score
aktielid614
jeugdlid2220
mkmt391
senior32119

Stel dat je per lidsoort de gemiddelde leeftijd en de gemiddelde score wilt, gesorteerd op de gemiddelde leeftijd aflopend (de hoogste bovenaan).
Dat kan met de query:

SELECT lidsoort , AVG(leeftijd ), AVG(score ) FROM leden GROUP BY lidsoort ORDER BY 2 DESC ;

En het resultaat is:
lidsoortAVG(leeftijd )AVG(score )
senior42.71434588.4286
aktielid26.0000307.0000
jeugdlid12.3333740.0000
mkmt6.5000195.5000


HAVING
Stel dat je een lijstje wilt van de lidsoorten en de totale score per lidsoort, maar dan alleen de lidsoorten die een totale score van meer dan 20000 hebben.
Bij een voorwaarde die betrekking heeft op een functie, waarbij group by gebruikt wordt, dan moet je gebruik maken van HAVING.
De query moet dan als volgt worden geformuleerd:

SELECT lidsoort, SUM(score ) FROM leden GROUP BY lidsoort HAVING SUM(score) > 20000 ;

Het resultaat is dan:
lidsoortSUM(score )
jeugdlid2220
senior32119

  9.11 SQL: Gegevens uit meer tabellen gebruiken

Het komt vaak voor dat je met meerdere tabellen tegelijk werkt.
Vanaf nu maken we, behalve van de tabel leden ook gebruik van de volgende tabellen:
Tabel competitiesoort:
soortspeeldagaanvoerder
HDdonderdag11
HEzaterdag6
DDdinsdag3
JDwoensdag2
GDzaterdag0

en tabel competitielid:
compnrlidnrsoort
16HD
211HD
312HD
46HE
511HE
61DD
73DD
87DD
92JD
107JD


Het lidnr in deze laatste tabel is natuurlijk hetzelfde lidnr als in de tabel leden.

JOIN
Als je de namen van alle leden wilt hebben met de competitie waaraan ze meedoen, dan moet je gebruik maken van de tabel leden en van de tabel competitielid, want daarin staat aan welke competitie de leden meedoen.

De query wordt dan als volgt:

SELECT naam, leden.lidnr, soort FROM leden, competitielid WHERE leden.lidnr = competitielid.lidnr ;

Als er twee tabellen worden gebruikt moet de server wel weten welke rij van de ene tabel hoort bij welke rij uit de andere tabel.
Bij dit voorbeeld is het zo dat het lidnr van de tabel leden hetzelfde moet zijn als het lidnr van de tabel competitielid
De voorwaarde in SQL is dan: WHERE leden.lidnr = competitielid.lidnr ;

Dat wordt wel de JOIN genoemd.

Je moet dan de naam van de tabel voor de naam van de kolom zetten, gescheiden door een punt.

Verder moet je dat ook doen met sommige kolomnamen achter SELECT, namelijk als dezelfde kolomnamen in beide tabellen voorkomen. De server moet natuurlijk wel weten uit welke tabel hij die kolom moet kiezen om dat veld af te drukken.

ALIAS
Als er veel voorwaarden zijn kan dat veel typwerk opleveren.
Je kunt dat beperken door aliassen voor de namen van de tabellen te gebruiken. Daarmee wordt bedoeld dat je kortere namen voor de tabellen bedenkt (en m.b.v. AS duidelijk maakt in de query).

De vorige query kan ook op de volgende manier:

SELECT naam, l.lidnr, soort FROM leden AS l , competitielid AS cl WHERE l.lidnr = cl.lidnr ;

Het resultaat is dan:

naamlidnrsoort
Gerrit6HD
Kees11HD
Leon12HD
Gerrit6HE
Kees11HE
Annie1DD
Hillegien3DD
Femke7DD
Willem2JD
Femke7JD


Drie tabellen
Stel dat je de namen van de leden en de bijbehorende competitie wilt afdrukken, en de dag waarop die competitie plaats vindt, en dan alleen van de zaterdag-competities.
Dan moet je alle drie de tabellen gebruiken.
Dan moet je twee joins gebruiken:
Het lidnr bij de tabellen leden en competitiesoort moet gelijk zijn, dus: leden.lidnr = competitielid.lidnr
en bij de tabellen competitiesoort en competitielid moet de soort gelijk zijn, dus competitiesoort.soort = competitielid.soort

De query wordt dan als volgt:

SELECT l.naam, l.lidnr, cl.soort, cs.speeldag
FROM leden AS l , competitiesoort AS cs , competitielid AS cl
WHERE l.lidnr = cl.lidnr
AND cs.soort = cl.soort
AND cs.speeldag = 'zaterdag' ;

Het resultaat is dan:

naamlidnrsoortspeeldag
Gerrit6HEzaterdag
Kees11HEzaterdag


Twee keer dezelfde tabel
Je kunt ook een query maken met twee tabellen, maar dan zo dat het om twee keer dezelfde tabel gaat.
Stel dat je een lijstje wilt waaruit blijkt welke personen ouder zijn dan de anderen, zonder dat de leeftijd wordt afgedrukt. Er wordt gewoon afgedrukt wie ouder is dan wie.
Dat kan met de volgende query:

SELECT l1.naam, " is ouder dan " , l2.naam
FROM leden AS l1 , leden AS l2
WHERE l1.leeftijd > l2.leeftijd
AND l1.leeftijd < 20
ORDER BY l1.leeftijd ASC , l2.leeftijd DESC ;

Het resultaat is dan:

naam is ouder dan naam
Chris is ouder dan Dona
Eline is ouder dan Chris
Eline is ouder dan Dona
Femke is ouder dan Eline
Femke is ouder dan Chris
Femke is ouder dan Dona
Willem is ouder dan Femke
Willem is ouder dan Eline
Willem is ouder dan Chris
Willem is ouder dan Dona

  9.12 SQL: Subqueries, exists

N.B. Je kunt de queries uit deze paragraaf alleen in Access uitproberen, MySQL staat geen namelijk geen subqueries toe.

Stel dat je de naam van het oudste lid wilt laten afdrukken, dan kan dat met een subquery.
Die gaat als volgt:

SELECT naam FROM leden WHERE geboren = ( SELECT min(geboren) FROM leden) ;

Als deze query wordt uitgevoerd dan wordt eerst de subquery (de query tussen haakjes, dus: SELECT min(geboren) FROM leden ) uitgevoerd.
Als je die uitvoert verschijnt er 1944-06-02
Daarna wordt de hoofdquery uitgevoerd, en inplaats van de subquery wordt 1944-06-02 ingevuld.
Er staat dan dus eigenlijk:
SELECT naam FROM leden WHERE geboren = 1944-06-02
Resultaat:
naam
Annie


SUBQUERY met IN
Stel dat je de namen van alle aanvoerders wilt afdrukken.
Dan kan dat door twee tabellen te gebruiken:

SELECT naam FROM leden, competitiesoort
WHERE leden.lidnr = competitiesoort.aanvoerder ;

Maar het kan ook m.b.v. een subquery:

SELECT naam FROM leden WHERE lidnr IN
( SELECT aanvoerder FROM competitiesoort) ;

Als deze query wordt uitgevoerd dan wordt eerst de subquery (de query tussen haakjes, dus: SELECT aanvoerder FROM competitiesoort ) uitgevoerd.
Als je die uitvoert is het resultaat het volgende lijstje: 11, 6, 3, 2
Daarna wordt de hoofdquery uitgevoerd, en inplaats van de subquery wordt het lijstje 11, 6, 3, 2 ingevuld.
Er staat dan dus eigenlijk:
SELECT naam FROM leden WHERE lidnr IN (11, 6, 3, 2);
Omdat er nu uit een lijstje gekozen worden moet je IN gebruiken, en geen =
Je mag alleen = gebruiken als je er zeker van bent dat het resultaat van de subquery maar één exemplaar oplevert!

Het resultaat van beide queries is:
(de volgorde kan bij de twee
queries verschillend zijn)
naam
Kees
Gerrit
Hillegien
Willem


SUBQUERY met NOT EXISTS
EXISTS : met EXISTS wordt er nagegaan of een subquery rijen oplevert. Zo niet, dan blijft het resultaat leeg.
Bij NOT EXISTS wordt er nagegaan of een subquery geen rijen oplevert. Als hij wel rijen oplevert dan blijft het resultaat leeg.

Stel dat we de namen willen afdrukken van de leden die niet aan de competitie meedoen.
Dat kan met de volgende query:

SELECT naam FROM leden WHERE NOT EXISTS
( SELECT * FROM competitielid WHERE competitielid.lidnr = leden.lidnr ) ;

Resultaat:
naam
Margreet
Eline
Dona
Chris
Dieuwke
Sabine
Femke


Je kunt een subquery op meer manieren gebruiken dan hierboven getoond is, je kunt ook subqueries gebruiken bij HAVING, INSERT, UPDATE en DELETE.

  9.13 Parameters in Access

Als je alle namen met de leeftijden wilt hebben van de leden die in Buitenpost wonen, dan kan dat met de volgende query:

SELECT naam, leeftijd FROM leden WHERE woonplaats = 'Buitenpost' ;

Als je daarna een lijstje wilt hebben met alle namen en de leeftijden van de leden die in Twijzel wonen, dan kan dat met bijna dezelfde query. Je hoeft alleen Buitenpost door Twijzel te vervangen.
En als je een lijstje wilt hebben met alle namen en de leeftijden van de leden die in Veenklooster wonen, dan moet je in dezelfde query Buitenpost door Veenklooster vervangen.

Je kunt in Access de query ook zo maken, dat voordat de query wordt uitgevoerd er een venstertje verschijnt waarin je de plaatsnaaam in kunt voeren. En na een druk op OK wordt dan de query met die plaatsnaam uitgevoerd.
Je moet dan gebruik maken van een zogenaamde parameter.

In plaats van de plaatsnaam zet je in de query dan bijvoorbeeld [plaatsnaam].
En verder moet je een regel boven de query toevoegen, waarin je zet: parameters [plaatsnaam] string;
Je moet de parameter dus declareren, zeggen van welk type de parameter is.