SQLSQL, de afkorting van Structured Query Language, is een vraagtaal waarmee gegevens van databases kunnen worden opgevraagd.Daarvoor is een standaard ontwikkeld waaraan alle databaseontwikkelaars zich in principe moeten houden. Toch zijn er enkele verschillen in de SQL-lidsoorten bij de verschillende DBMS'en (database management systemen) Bepaalde commando's werken bij de ene DBMS wel en bij andere niet. De taal SQL kan onderverdeeld worden in een aantal subtalen.
|
| 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 |
Vanaf § 6 maken we ook gebruik van de volgende tabellen:
| Tabel competitiesoort: |
|
| en tabel competitielid: |
|
§ 2. 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 ;
| Het resultaat is dan: |
|
Als je de lidsoorten uit de tabel wilt dan kan dat met de volgende query:
SELECT lidsoort
FROM leden ;
|
Het resultaat is dan:
|
|
| DISTINCT |
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 1 staat.
| 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.
Opgaven:
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
- Geef de query voor een lijstje van alle leden, met alle gegevens, gesorteerd op leeftijd, en wel zo dat de oudste bovenaan staat.
- Geef de query voor een lijstje met de naam, de woonplaats, en de score van alle leden, gesorteerd op de naam.
- Geef de query voor een lijstje van alle plaatsen waar leden vandaan komen, alfabetisch gerangschikt.
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
§ 3. 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 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 |
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 |
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 |
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 |
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 |
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 |
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.
Opgaven:
Voer hier de query in:
- Geef de query voor een lijstje van alle leden uit Veenklooster, geef de naam, de woonplaats, en de score.
- Geef de query voor een lijstje van alle leden (geef de naam en de leeftijd), die ouder dan 20 jaar zijn, en waarvan de naam met een F begint
- Geef de query voor een lijstje van alle leden (geef de naam, de leeftijd en de lidsoort), die geen aktielid zijn, en die jonger dan 20 jaar zijn.
- Geef de query voor een lijstje van alle jeugdleden (geef de naam, de leeftijd,de lidsoort en de score), gesorteerd op score, en wel zo dat degene met de hoogste score bovenaan staat.
- Geef de query voor de competitiesoort(en), waarvoor nog geen aanvoerder is vastgelegd.
- Geef de query voor de competitiesoorten, die op zaterdag worden gespeeld.
Voer hier de query in:
§ 4. 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 |
SELECT COUNT(*
) FROM leden ;
|
Het resultaat van die query is: |
|
| AS |
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 |
Voorbeeld:
SELECT ROUND(AVG(leeftijd),
1) AS gemiddelde_leeftijd
FROM leden ;
|
levert als resultaat de gemiddelde leeftijd, afgerond op één decimaal: |
|
| LEN |
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 |
SELECT UCASE(naam )
FROM leden
WHERE woonplaats =
'Buitenpost' ;
|
geeft als resultaat:
|
|
| CURRENT_DATE |
Voorbeeld: CURRENT_DATE( ) geeft de huidige systeemdatum als resultaat.
De query:
SELECT CURRENT_DATE() AS
datum ;
| Resultaat: |
|
| BEREKENINGEN |
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: |
|
Overzicht van de sql-functies
- COUNT(kolomnaam) : geeft het aantal rijen in de tabel. Ook kan count(*) en count(nummer van de kolom)
- MIN(kolom) : geeft het kleinste getal bij een numerieke kolom, of bij een tekstuele kolom de string die alfabetisch het eerst komt, of bij een datum-kolom de vroegste datum.
- MAX(kolom) : geeft het grootste getal bij een numerieke kolom, of bij een tekstuele kolom de string die alfabetisch het laatst komt, of bij een datum-kolom de laatste datum.
- SUM(kolom) : geeft de som van alle waarden uit de kolom, dat kan alleen bij numerieke kolommen.
- AVG(kolom) : geeft het gemiddelde van een kolom, dat kan alleen bij numerieke kolommen.
- ROUND(kolom, aantal decimalen): zorgt ervoor dat de getallen in de betreffende kolom worden afgerond op het aantal decimalen dat je hebt opgegeven.
- LCASE(kolomnaam) : geeft de tekst in de kolom in kleine letters.
- CONCAT(kolom1,kolom2 ) : plakt de twee kolommen achter elkaar, er kan ook een string aan worden vastgeplakt.
- LEFT(kolom,n ) : geeft de eerste n tekens uit een kolom.
- RIGHT(kolom,n ) : geeft de laatste n tekens uit een kolom.
- INSTR(kolom, "karakter ") : geeft de eerste positie van een bepaald karakter in de kolom. Als het teken niet voorkomt in de string dan is het resultaat 0.
- LEN(kolom) : geeft het aantal tekens van de woorden in de kolom. (in MySQL is het LENGTH( ) i.p.v. LEN( ) )
- CURRENT_DATE( ) geeft de huidige systeemdatum als resultaat.
- CURRENT_TIME( ) geeft de huidige systeemtijd als resultaat.
- CURRENT_TIMESTAMP( ) geeft de huidige systeemdatum + systeemtijd als resultaat.
- NOW( ) geeft ook de huidige systeemdatum + systeemtijd als resultaat.
Opgaven:
Voer hier de query in:
- Geef de query voor de gemiddelde leeftijd van de seniorleden.
- Geef de query voor het aantal aktieleden.
- Geef de query voor de leeftijd van het oudste lid.
- Geef de query voor de geboortedatum van het jongste lid uit Buitenpost.
Voer hier de query in:
