Informaticasite van het Sondervick College te Veldhoven                 © L.J.M van Haperen (bron : R.J. van der Beek)
 

SQL, vervolg

  § 5. 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


Opgaven:
  1. Geef de query voor een lijstje van de lidsoorten, met het aantal leden per lidsoort.
  2. Geef de query voor een lijstje van de lidsoorten, met de gemiddelde leeftijd per lidsoort.
  3. Geef de query voor een lijstje van de lidsoorten, met het aantal leden per lidsoort, maar dan alleen de lidsoorten die uit meer dan drie leden bestaan.
  4. Geef de query voor een lijstje van de plaatsen, met de gemiddelde score van de leden uit die plaats, maar dan alleen de plaatsen waar de gemiddelde score boven 4000 zit.
Je kunt de queries hier uitproberen. Voer de query hieronder in, dan wordt hij door MySQL verwerkt.

Voer hier de query in:

  § 6. 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


Opgaven:
  1. Geef de query voor een lijstje van de leden, die aan de competitie DD (damesdubbel) meedoen. Geef de naam, de woonplaats en de (competitie)soort.
  2. Geef de query voor een lijstje van de leden uit Buitenpost, die aan de competitie meedoen. Geef de naam, de woonplaats en de (competitie)soort.
  3. Geef de query voor een lijstje van de leden, die aan de competitie meedoen, met de dag waarop ze spelen. Geef de naam, de woonplaats, de (competitie)soort, en de speeldag.
  4. Geef de query voor een lijstje van alle competitiesoorten, met de naam van de aanvoerder van die soort.
Je kunt de queries hier uitproberen. Voer de query hieronder in, dan wordt hij door MySQL verwerkt.

Voer hier de query in:

  § 7. Subqueries, exists

Stel dat je de naam van het oudste lid wilt laten afdrukken, dan kan dat met een subquery.
(N.B. Subqueries werken (nog) niet is MySQL)
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.

Opgaven:
  1. Geef de query voor de naam van het jongste lid van de tennisvereniging.
  2. Geef de query voor de naam van het lid uit Buitenpost met de hoogste score.
  3. Geef de query voor een lijstje van de leden, die niet aan de competitie meedoen.
Je kunt de queries hier niet uitproberen, want MySQL staat geen subqueries toe.

  § 8. Toevoegen en bewerken van gegevens met INSERT, UPDATE, DELETE

Met INSERT voegen we gegevens toe aan een tabel. De basis syntax is:

INSERT INTO tabel (kolommen ) VALUES ('waarden') ;

Als je een nieuw lid wilt toevoegen aan de tabel leden, dan kan dat met de volgende query:

INSERT INTO leden ( lidnr, naam, woonplaats, geboren, leeftijd, lidsoort, score)
VALUES (15,'Rienk', 'Buitenpost','1961-09-12', '45','senior', 0) ;

Als je deze uit laat voeren wordt er een rij aan de tabel leden toegevoegd.

Een andere manier is dit:

INSERT INTO leden
VALUES (15,'Rienk', 'Buitenpost','1961-09-12', '45','senior', 0) ;

Je ziet dat nu de kolomnamen zijn weggelaten en alleen de waarden worden beschreven. Dan moet wel aan elke kolom een waarde worden gegeven, en in de goede volgorde!

Bij de eerste manier hoef je niet elke kolom een waarde te geven.

Bij MySQL bestaat het commando auto_increment, dat betekent dat die kolom automatisch met één wordt opgehoogd.
Als de kolom lidnr van de tabel leden in MySQL auto_increment is, dan heeft het geen zin die kolom zelf een waarde te geven.
Verder zal er bij een nieuw lid nog geen score zijn, en heeft het geen zin die score in te voeren. De query kan in dat geval ook als volgt:

INSERT INTO leden ( naam, woonplaats, geboren, leeftijd, lidsoort)
VALUES ('Rienk', 'Buitenpost','1961-09-12', '45','senior') ;


UPDATE
Het wijzigen van gegevens gebeurt met het commando UPDATE. De basis syntax is:

UPDATE tabel
SET kolom1 = 'waarde1', kolom2 = 'waarde2', kolom3 = 'waarde3', enz
WHERE voorwaarden ;

Stel dat je de tabel leden wilt aanpassen omdat Annie verhuisd is van Buitenpost naar Kollum, en haar score is gestegen van 2075 naar 3000. Haar lidnr is 1

Deze wijziging wordt ingevoerd m.b.v. de query:

UPDATE leden
SET woonplaats = 'Kollum', score = 3000
WHERE lidnr = 1 ;

Als je de laatste regel weglaat (dus WHERE lidnr = 1 ) dan worden de woonplaatsen van alle leden gewijzigd in Kollum, en alle scores in 3000. En dat is natuurlijk niet de bedoeling.
Let er daarom op dat je zoveel mogelijk de unieke waarde (of primairy key), in dit geval dus het lidnr, neemt als voorwaarde voor een enkele rij met UPDATE.

Stel je voor dat bij alle senior-leden de score met 100 verhoogd moet worden, dan kan dat met de volgende query:

UPDATE leden
SET score = score + 100 WHERE lidsoort = 'senior' ;

DELETE
Het verwijderen van rijen uit een tabel gebeurt met het commando DELETE. De basis syntax is:

DELETE FROM tabel
WHERE voorwaarden ;

Dit commando verwijdert hele rijen tegelijk, het is dus heel belangrijk om duidelijk aan te geven aan welke voorwaarden de te verwijderen rijen moeten voldoen om niet de verkeerde gegevens te verwijderen. Als je de voorwaarden weg laat dan worden alle rijen uit de tabel verwijderd!

Stel dat Femke uit Drogeham (lidnr 14) haar lidmaatschap van de tennisvereniging heeft opgezegd, dan kan ze uit de tabel worden verwijderd.
De query wordt dan:

DELETE FROM leden
WHERE lidnr = 14 ;

Net zoals bij UPDATE maken we bij de voorwaarden bij DELETE zoveel mogelijk gebruik van de unieke waarde (of primairy key), hier dus het lidnr.
Zouden we de volgende query gebruiken:

DELETE FROM leden
WHERE naam = 'Femke' ;

dan zouden alle rijen met de naam 'Femke' worden verwijderd. In ons voorbeeld zijn dat twee rijen!

Opgaven:
  1. Geef de query voor het toevoegen van een nieuw lid: Theo, Augustinusga, geboren 1945-01-13, leeftijd: 61, lidsoort: senior
  2. Margreet uit Kollum (lidnr 4) is geen aktielid meer maar is nu senior-lid, en haar score is 350 geworden. Geef de query waarmee deze wijziging ingevoerd kan worden.
  3. Kees uit Veenklooster (lidnr 11) heeft zijn lidmaatschap opgezegd. Geef de query waarmee hij verwijderd kan worden.
  4. Er is weer een jaar voorbij. Dat heeft tot gevolg dat bij iedereen de leeftijd met één verhoogd moet worden. Geef de query waarmee dat voor iedereen wordt gebeurt.
Je kunt de queries hier uitproberen. Voer de query hieronder in, dan wordt hij door MySQL verwerkt.

Voer hier de query in:

  § 9. Bewerkingen op complete tabellen: create, drop, grant

Met het commando CREATE kun je een tabel aanmaken. De basis syntax is:

      CREATE TABLE tabelnaam
(
      naam_kolom1 type van kolom1 ,
      naam_kolom2 type van kolom2 ,
      naam_kolom3 type van kolom3 ,
      enz.
      PRIMARY KEY (kolomnaam)
);

De query om de leden-tabel te maken is als volgt:

CREATE TABLE leden
(
      lidnr INT NOT NULL AUTO_INCREMENT ,
      naam VARCHAR(30) NOT NULL ,
      woonplaats VARCHAR(20) NOT NULL ,
      geboren DATE NOT NULL ,
      leeftijd INT NOT NULL ,
      lidsoort VARCHAR(20) ,
      score INT ,
      PRIMARY KEY ( lidnr )
);

DROP
Met het commando DROP kun je een tabel verwijderen. De basis syntax is:

      DROP TABLE tabelnaam ;

De query om de leden-tabel te verwijderen is als volgt:

DROP TABLE leden;


GRANT
Met het commando GRANT kun je een gebruiker de bevoegdheid geven om gegevens van een tabel te raadplegen, te wijzigen, toe te voegen, en of te verwijderen. De basis syntax is:

      GRANT SELECT, UPDATE, INSERT, DELETE ON 'tabelnaam ' TO 'gebruikersnaam';

De query om de gebruiker lcleerling de bevoegdheid te geven om gegevens van de tabel leden te raadplegen en te wijzigen is als volgt:

GRANT SELECT, UPDATE ON 'leden ' TO 'lcleerling';


Opgaven:
  1. Geef de query voor het maken van de tabel competitiesoort. De kolommen zijn: soort (tekst, 2 tekens), speeldag (tekst, 10 tekens) en aanvoerder (integer, unsigned). De soort is de primaire sleutel.
  2. Geef de query voor het verwijderen van de tabel competitielid
Je kunt de queries hier niet uitproberen, we hebben Create en Drop uitgeschakeld.

  § 10. Overzicht

De SELECT-opdracht
De basisstructuur van een SQL-query is als volgt:
SELECT [ALL | DISTINCT] kolommen en/of functies
FROM tabellen
[WHERE voorwaarden]
[GROUP BY groepeer-kolommen]
[HAVING groeps-voorwaarden]
[ORDER BY sorteer-kolommen] [ASC | DESC]
  • De eerste twee regels zijn verplicht.
    De andere regels hoeven niet voor te komen, dit wordt aangegeven door de blokhaken.
  • De namen van de kolommen worden gescheiden door komma's.
    Wanneer er verwarring mogelijk is doordat er kolommen met dezelfde naam voorkomen dan zet je de tabelnaam voor de kolomnaam, met een punt ertussen.
  • Alle kolommen uit een tabel krijg je met SELECT *
  • Door het woord DISTINCT achter SELECT te zetten zullen er in het resultaat geen dubbele rijen verschijnen, elke rij verschijnt maar één keer.
  • Wanneer je meer dan één tabel gebruikt dan zet je achter FROM de namen van de tabellen, gescheiden door komma's.
    Je moet dan een koppelvoorwaarde gebruiken bij WHERE.
    Met zo'n koppelvoorwaarde geef je aan hoe de koppeling van de tabellen is. Meestal bestaat zo'n voorwaarde eruit dat twee kolommen (vaak met dezelfde naam) die in beide tabellen voorkomen, gelijk moeten zijn (bijvoorbeeld: leerlingen.llnr=uitleningen.llnr)
Voorwaarden
  • Zet aanhalingstekens om tekst en om datums, bij getallen zijn die niet nodig.
  • Gebruik =, < , >, <= , >= of <> om te vergelijken.
    Bijvoorbeeld: WHERE voornaam = "Piet";
    Ander voorbeeld: WHERE leeftijd = 25;
  • Gebruik LIKE i.p.v. = als je gebruik maakt van wildcards, en niet exacte gelijkheid bedoelt.
    Het % teken staat voor een aantal willekeurige tekens (in Access: * )
    Het _ teken staat voor één willekeurig teken (in Access: % )
    Bijvoorbeeld: WHERE voornaam LIKE "P*";
  • Gebruik IS NULL om te testen of er iets is ingevuld in een veld.
    WHERE tussenvoegsel IS NULL";
Voorwaarden combineren
  • "voorwaarde1" AND "voorwaarde2" is waar als beide voorwaarden waar zijn.
    Dat kan gebruikt worden in een WHERE-voorwaarde: WHERE "voorwaarde1" AND "voorwaarde2"
  • "voorwaarde1" OR "voorwaarde2" is waar als één van de voorwaarden waar is of als beide voorwaarden waar zijn.
    Dat kan ook gebruikt worden in een WHERE-voorwaarde: WHERE "voorwaarde1" OR "voorwaarde2"
  • IN (lijst van waarden) is waar als de kolomwaarde gelijk is aan één van de waarden in de lijst.
    Dat kan gebruikt worden in een WHERE-voorwaarde: WHERE kolom1 IN (lijst van waarden)
  • BETWEEN waarde1 AND waarde2 is waar als de kolomwaarde tussen waarde1 en waarde2 zit.
    Dat kan ook gebruikt worden in een WHERE-voorwaarde: WHERE kolom1 BETWEEN waarde1 AND waarde2
Groeps-functies
MINgeeft de kleinste waarde in een kolom
MAXgeeft de grootste waarde in een kolom
SUMgeeft de som van de getalswaarden in een kolom
AVGgeeft de gemiddelde waarde in een kolom
COUNTgeeft het aantal waarden in een kolom
COUNT(*)geeft het aantal rijen van een tabel

Groeps-functies worden gebruikt om iets te berekenen in een kolom bij een SELECT-opdracht opdracht, en dan wordt er één waarde als uitkomst gegeven.
Als je een "GROUP BY" opdracht gebruikt is daarbij een groeps-functie vereist, maar groeps-functies kunnen ook zonder de "GROUP BY"-opdracht worden gebruikt.

Voorbeeld:
SELECT kolom1, SUM(kolom2)
FROM tabel
[WHERE voorwaarden]

Voorbeeld waarin het aantal rijen wordt gegeven:
SELECT COUNT(*)
FROM tabel
[WHERE voorwaarden]

GROUP BY
Een GROUP BY-opdracht wordt altijd in combinatie met een groeps-functie gebruikt.
Als je GROUP BY gebruikt zullen er groepjes gemaakt worden van rijen waarvan de gegevens in de genoemde kolom(men) achter GROUP BY gelijk zijn, en de groeps-functie zal dan worden toegepast op de kolom die tussen haakjes achter de groeps-functie staat.

GROUP BY syntax met SUM als voorbeeld:
SELECT kolom1[,kolom2], SUM(kolom3)
FROM tabellen-lijst
GROUP BY kolom1[,kolom2];

HAVING
Je gebruikt HAVING als de waarden van de groeps-functie aan bepaalde voorwaarden moeten voldoen.
HAVING komt altijd na GROUP BY in een select-opdracht.

Voorbeeld:
SELECT kolom1[,kolom2], SUM(kolom3)
FROM tabellen-lijst
GROUP BY kolom1[,kolom2]
HAVING voorwaarde;

Subquery, EXISTS
  • Binnen een query kun je een subquery maken voor een tussenresultaat. Die subquery moet dan tussen haakjes staan en moet één kolom opleveren.
    Bijvoorbeeld: SELECT * FROM leerlingen WHERE voornaam IN (SELECT voornaam FROM auteurs);
    Als je zeker weet dat de subquery maar één woord of getal als resultaat oplevert dan kun je i.p.v. IN ook = gebruiken.
    Bijvoorbeeld: SELECT * FROM leerlingen WHERE geboortedatum = (SELECT max(geboortedatum FROM leerlingen);
  • Met EXISTS kun je nagaan of dingen wel of niet voorkomen.
    Bijvoorbeeld: SELECT * FROM leerlingen WHERE NOT EXISTS (SELECT voornaam FROM auteurs WHERE leerlingen.voornaam = auteurs.voornaam);