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.
|
| lidnr | naam | woonplaats | geboren | leeftijd | lidsoort | score |
| 1 | Annie | Buitenpost | 1944-06-02 | 62 | senior | 2075 |
| 2 | Willem | Veenklooster | 1992-09-13 | 14 | jeugdlid | 987 |
| 3 | Hillegien | Kollum | 1968-01-11 | 38 | senior | 1598 |
| 4 | Margreet | Kollum | 1980-09-18 | 26 | aktielid | 234 |
| 5 | Eline | Buitenpost | 1996-11-04 | 10 | jeugdlid | 435 |
| 6 | Gerrit | Twijzel | 1958-05-04 | 48 | senior | 5989 |
| 7 | Femke | Augustinusga | 1993-09-19 | 13 | jeugdlid | 798 |
| 8 | Dona | Burum | 2000-05-15 | 6 | mkmt | 155 |
| 9 | Chris | Burum | 1998-12-12 | 7 | mkmt | 236 |
| 10 | Dieuwke | Buitenpost | 1959-09-06 | 47 | senior | 7345 |
| 11 | Kees | Veenklooster | 1965-07-27 | 41 | senior | 6345 |
| 12 | Leon | Augustinusga | 1980-06-06 | 26 | aktielid | 380 |
| 13 | Sabine | Drogeham | 1970-11-10 | 36 | senior | 4999 |
| 14 | Femke | Drogeham | 1979-07-06 | 27 | senior | 3768 |
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.
| soort | speeldag | aanvoerder |
| HD | donderdag | 11 |
| HE | zaterdag | 6 |
| DD | dinsdag | 3 |
| JD | woensdag | 2 |
| GD | zaterdag |
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 geïntroduceerd.
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.
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.
- Klik je op het pijltje naar rechts dan ga je naar het volgende record.
- 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.
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: |
|
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:
|
|
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:
|
|
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:
|
|
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:
|
|
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:
|
|
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:
|
|
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:
|
|
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: |
|
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: |
|
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: |
|
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:
|
|
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:
|
|
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: |
|
IS NULL
Als je wilt controleren m.b.v. SQL of een kolom is ingevuld dan gebruik je IS NULLJe 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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
En als je de query daarvoor verandert in:
SELECT AVG (score) AS
gemiddelde_score FROM
leden ;
|
dan is het resultaat: |
|
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: |
|
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 :
|
|
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:
|
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
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: |
|
| en tabel competitielid: |
|
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:
|
|
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' ;
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:
|
|
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 ;
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:
|
|
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: |
|
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 ;
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) ;
( 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) |
|
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 ) ;
( SELECT * FROM competitielid WHERE competitielid.lidnr = leden.lidnr ) ;
| Resultaat: |
|
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.
