In alle lessen (hoorcolleges en practica) hebben we volgende zaken nodig:

1. Deze cursus

2. Een laptop met daarop Windows 10 (Pro/Education/Enterprise) en MySQL Workbench. Je vindt gedetailleerde filmpjes en schriftelijke instructies op .

Boeken

Er zijn oneindig veel boeken over SQL, maar een goed boek om een overzicht te krijgen van de basisinstructies is:

• Standard SQL - Basisbook, Peter Spaas, LannooCampus

Helpen?

Fijn dat je interesse toont om aan deze cursus te helpen. Dit kan op meerdere manieren:

• Je mag ons altijd gewoon mailen met tips, opmerkingen of verbeteringen.

• Je kan rechtstreeks aanpassingen doen via een fork in github en deze via een merge requests in de cursus integreren.

Deze cursus wordt gebruikt als handboek binnen de opleiding graduaat programmeren van de AP Hogeschool.

Indien bepaalde hoofdstukken of onderdelen niet duidelijk zijn of je hebt suggesties ter verbetering/aanvulling, aarzel dan niet om ons te contacteren.

Veel leer- en leesplezier.

Peter Spaas, Vincent Van Camp en Vincent Nys

Voorbeeld 1

Een klassiek voorbeeld daarvan is een lijst met de namen en adressen. Die lijst kan er als volgt uitzien:

In bovenstaand voorbeeld is de eerste rij de aanduiding van de kolommen (veldnamen).

Bovenstaand voorbeeld bevat dus vijf rijen (records) die telkens bepaalde waarden bevatten, hier gaat het over de "Naam", "Voornaam", "Adres", "Postcode", "Gemeente", "Telefoon" en "Verdieping".

Voorbeeld 2

Een bedrijf in de productiesector zal onder andere volgende gegevens willen bijhouden, nl.:

• Personeelsgegevens

• De zgn. productiegegevens. Hierbij kunnen we denken aan bv. de productsamenstelling

• De gegevens betreffende de noodzakelijke bestellingen

• De gegevens van de bestaande klanten

In dit geval is het best om voor elk bullet point een aparte tabel te voorzien. Groepjes gegevens die sterk aan elkaar gelinkt zijn (bijvoorbeeld "voornaam werknemer" en "familienaam werknemer" komen in dezelfde tabel). Groepjes gegevens die losser gelinkt zijn komen in verschillende tabellen. Er zijn nog andere redenen om verschillende tabellen te gebruiken. Die komen later.

Voorbeeld 3

Een bank houdt per rekening van een bepaalde klant geen box bij met daarin het geld van een klant. Het bedrag op de rekening van die klant is een cijfer dat wordt bijgehouden in een database. Er zou bijvoorbeeld een tabel Rekeningen kunnen zijn, die je je zo kan voorstellen:

Daarnaast heeft de bank nog allerlei informatie, bijvoorbeeld over hypotheken,... Die zullen vermoedelijk in andere tabellen staan. De volledige verzameling tabellen vormt de database van de bank.

Een database (of gegevensbank) is een gestructureerde verzameling elektronische gegevens (of data) die door één of meerdere gebruikers (of users) gelijktijdig kunnen gemanipuleerd worden. De enorme hoeveelheden gegevens van banken worden beheerd in ultramoderne databases.

De database zelf, dat zijn dus de geordende gegevens. Maar gegevens zijn pas waardevol als er iets mee gedaan wordt. Het is de taak van een database management system (DBMS) om gegevens op te vragen, aan te passen, enzovoort.

Het DBMS moet er bovendien over waken dat de integriteit van de database behouden blijft. "Waken over integriteit" betekent bijvoorbeeld dat het juiste telefoonnummer bij de juiste persoon blijft horen, dat er geen personen zonder adres in het systeem geplaatst worden,...

Maar het DBMS doet veel meer dan alleen waken over integriteit. Zonder DBMS is het onmogelijk gegevens in een database onder andere te bekijken, te wijzigen, te verwijderen... Alleen het DBMS weet waar en hoe de gegevens in de database verwerkt moeten worden.

Een DBMS zal uiteraard nooit uit zichzelf de gegevens in een database opzoeken, wijzigen of verwijderen. Om dit te doen is steeds een specifieke opdracht van de gebruiker nodig. Bijvoorbeeld: "Verander het telefoonnummer van Jan Claessens van (02) 568 95 65 in (03) 574 23 84".

Dit soort instructie wordt echter niet in een natuurlijke taal gegeven. De meeste DBMS beschikken over een eigen taal voor instructies. Die taal heeft een specifieke "syntax", een stel regels om instructies te ontleden in hun bestanddelen. Deze regels gelijken een beetje op de regels die wij hanteren wanneer we Nederlands spreken. "Ga op die bank zitten" is een verstaanbare opdracht. We kunnen zeggen wat de taak is van elk woordje in deze Nederlandse zin. "Bank daar zitten ga op" is geen geldige zin. We kunnen als mensen nog wel raden naar de betekenis van deze zin, maar hij volgt de regels van de taal niet. Een machine zou het dan ook veel moeilijker hebben om er iets van te maken.

De regels van de talen waarmee men opdrachten kan geven aan een DBMS zijn dus veel strikter dan de regels voor het Nederlands: ze laten geen ruimte voor fouten. Een komma verkeerd volstaat om de opdracht onverstaanbaar en dus onuitvoerbaar te maken.

De belangrijkste commando's van de DDL zijn:

• CREATE: Hiermee maak je nieuwe structuren aan.

• ALTER: Hiermee verander je bestaande structuren.

• DROP: Hiermee wis je bestaande structuren.

Informatica is het verwerken van informatie. Het beheer van gegevens is dus vaak het eigenlijke doel van een informaticasysteem en is de bestandsorganisatie minstens zo belangrijk dan de eigenlijke programmatuur. Met gegevens bedoelen we hier informatie die voor kortere of langere tijd wordt opgeslagen en die onontbeerlijk is voor de werking van de organisatie/onderneming/…

De basiswoordenschat van relationele databanken is als volgt:

• entiteittypes: de verzamelingen van interessante gehelen waarover we informatie bijhouden. Op de figuur onderaan is dit bijvoorbeeld de hele verzameling met personen of de hele verzameling met boeken.

• entiteiten: de elementen van de verzameling. Elke persoon in de linkse verzameling, bijvoorbeeld William Shakespeare, is een entiteit. Elke persoon en elk boek is een entiteit.

• relatietypes: een soort verband tussen verzamelingen. Op de figuur is "heeft gelezen" een relatietype.

• relaties: een concreet verband tussen entiteiten. Op de figuur is bv. "Michiel heeft The Tempest gelezen" een relatie.

• tabellen: de structuur waarin een volledig entiteittype of een relatietype wordt opgeslagen. Deze tabel heeft steeds een naam, of tabelnaam.

• records: een tabel bestaat uit rijen of records. Eén record bevat alle gegevens van de rij en stelt dus één entiteit voor.

• kolommen (of "velden"): iedere kolom omvat één aanduiding van een gegeven binnen een tabel. Iedere kolom heeft een unieke naam.

Ter herinnering, de verzameling met personen stelden we zo voor:

Download: https://dev.mysql.com/downloads/workbench/

Login

Basisscherm MySQL Workbench:

Stap 1: Klik op het plusje.

Stap 2: Vul de juiste gegevens in.

Voor jouw gebruikersnaam en paswoord verwijzen we naar de e-mail die je hebt ontvangen van lector V. Nys.

Je klikt na het invullen van de juiste gegevens op de knop "Store in Vault" en vult het paswoord dat je via vermelde mail hebt ontvangen in.

Stap 3: Connectie testen.

Je moet volgende boodschap krijgen.

De "relationele" database is waarschijnlijk de meestgebruikte soort database. Dit type database is uitgewerkt in de jaren 1970 en steunt op de wiskundige verzamelingenleer.

Zonder deze wiskundige fundering in veel detail te behandelen: het basisidee is dat "interessante gehelen" van informatie in verzamelingen van één soort data worden opgedeeld. Tussen deze verzamelingen bestaan verbanden.

Onderstaande figuur illustreert dit. Op deze figuur zijn de interessante gehelen personen en boeken. Deze twee verzamelingen bestaan los van elkaar, maar er is een verband: sommige personen hebben sommige boeken gelezen.

Er kunnen veel verbanden zijn. We kunnen bijvoorbeeld een tweede verband hebben dat uitdrukt dat een bepaalde persoon een bepaald boek heeft geschreven:

Om dit in een elektronisch systeem voor te stellen, maken we geen tekeningen, maar gebruiken we tabellen. We doen dit zowel voor de "interessante gehelen" (dus de elementen van de verzamelingen) als voor de verbanden tussen elementen van deze verzamelingen.

Voor de eerste tekening zou een tabelvoorstelling van de verzamelingen er zo kunnen uitzien:

Ook het verband zou kunnen worden uitgedrukt met een tabel. Hoe dat precies werkt is voor iets verder, maar een vereenvoudiging die een goed beeld geeft is als volgt:

Zowat alle DBMS'en voor relationele databases spreken (ongeveer) dezelfde taal: de "Structured Query Language" of SQL. Deze taal is ook gebaseerd op de relationele algebra's en ze maakt dat je vrij snel kan leren werken met één relationele database als je al met een andere relationele database overweg kan. Elk DBMS spreekt wel een eigen "dialect" van SQL, dus je kan niet verwachten dat code voor bijvoorbeeld PostgreSQL (een type relationele database) letterlijk kan worden uitgevoerd in SQLite (een ander type relationele database). Maar normaal moet je er niet veel aan aanpassen.

Typisch voor relationele databases is dat je op voorhand moet vastleggen wat de structuur van je database is: welke tabellen er zijn en wat voor gegevens hier in mogen worden geplaatst. Deze structuur noemen we het "schema". Als je nieuwe gegevens wil bijhouden die niet passen binnen de structuur, moet je eerst de structuur aanpassen. Dit is niet bij alle soorten databanken zo. Andere types databanken hebben soms een heel losse structuur. Dit geeft hen soms meer flexibiliteit, maar zorgt er soms ook voor dat ze minder kunnen waken over de integriteit van hun gegevens.

Structuur van MySQL

MySQL is opgedeeld in een aantal deeltalen. Elke deeltaal dient voor een bepaald soort gebruik. Het is nuttig (sommige van) deze deeltalen meteen te kunnen herkennen, want ze maken het makkelijker de syntax te onthouden.

De volledige lijst deeltalen vind je terug in de (uitstekende) MySQL documentatie. Hij ziet er zo uit:

Je hoeft deze niet uit het hoofd te leren, maar het is wel nuttig om vanaf het begin een onderscheid te maken tussen data definition statements en data manipulation statements.

Data definition statements (ook de "Data Definition Language" of "DDL") dienen om vast te leggen hoe je gegevens er zullen uitzien. Met andere woorden, de structuur van de data. Ze bieden een antwoord op vragen zoals:

• Welke tabellen zijn er?

• Welk type gegevens mag ik in een bepaalde kolom bijhouden?

• Is het toegestaan bepaalde cellen leeg te laten?

Data manipulation statements (of de "Data Manipulation Language" of "DML") dient om specifieke data te beheren en niet de structuur van de data. Voorbeelden hiervan zijn:

• Maak een nieuwe rij aan in de tabel Personen.

• Pas Said aan naar Saïd.

• Verwijder Sanae uit de tabel Personen.

Als je jezelf steeds de vraag stelt of iets thuishoort in de DML of in de DDL, zal je veel makkelijker de juiste syntax terugvinden.

Voorbeeldinstructies MySQL

MySQL-instructies zijn vrij leesbaar in vergelijking met veel programmeertalen. Hieronder volgen enkele voorbeelden. Je hoeft deze nog niet zelf te kunnen gebruiken. Ze staan er alleen om je een idee te geven van hoe MySQL syntax er uitziet.

Volgend stukje code voegt een nieuwe persoon, Zadie Smith, toe aan de tabel met personen: INSERT INTO Personen(Voornaam,Familienaam,Geboortejaar) VALUES ('Zadie','Smith',1975); Hier betekent INSERT dat er iets nieuws wordt toegevoegd.

Volgend stukje code voegt een nieuwe tabel, Liedjes, toe aan de database. Elk liedje heeft een titel en een duurtijd. De titel is tekst met maximum 100 symbolen en de duurtijd is een geheel getal: CREATE TABLE Liedjes(Titel VARCHAR(100), Duurtijd INT);

Instructies zoals deze zal je intypen in je MySQL client (typisch MySQL Workbench).

Om een beter overzicht van het databaseschema te krijgen, maken we vaak gebruik van een diagram. Dit wordt een entity-relationship diagram (of "ERD") genoemd. Dit lijkt wat op de verzamelingennotatie die we eerder zagen, maar werkt op een ander niveau.

Neem volgend voorbeeld:

Dit zegt volgende zaken:

In het algemeen kunnen we dus stellen: personen hebben een lievelingsboek. Als we verder vastleggen dat personen altijd exact één lievelingsboek hebben, kunnen we dit grafisch uitdrukken als volgt:

Hier staat niets over Vincent, Esther of Michiel. Maar er staat dat er een relatietype is dat personen en boeken verbindt. De exacte betekenis van de getallen, de icoontjes en de stippellijn laten we voor iets later, maar onthoud alvast dat de rechthoeken de entiteiten voorstellen (met hun kolommen) en de ruiten relaties.

Let op: er zijn veel stijlen van notatie voor het entity-relationship diagram. Wij hebben dit met MySQL Workbench getekend door eerst volgende knop te gebruiken:

Daarna hebben we de plusknop gebruikt en op "Add diagram" geklikt.

Ten slotte hebben we via de menuknop "Model" gekozen voor "Object Notation" → "Workbench (Simplified)" en voor "Relationship Notation" → "Classic". Dit is ook de afspraak die we zullen hanteren voor de rest van de cursus.

Als je een ERD tegenkomt op het internet, ga dan eerst na welke notatie gebruikt is. Anders kan je niet weten wat het ERD precies voorstelt.

Opgelet!

Aanmaken van je eerste database

Je zou het volgende SQL statement gebruiken om een database te maken:

Het creëren van een database volstaat niet om die vervolgens te kunnen gebruiken. Je moet in een script expliciet opgeven dat je een bepaalde database wilt gebruiken met de instructie USE:

Een database moet slechts één keer gemaakt worden, maar je moet vooraleer die te gebruiken ze telkens weer selecteren. Dat doe je met de USE instructie zoals in het voorgaande voorbeeld.

Aanmaken van je eerste tabellen

Het niveau onder dat van de databank is het niveau van de tabel. Een tabel bevat typisch informatie over één entiteit, d.w.z. één soort interessante data. Dat is bijvoorbeeld bijvoorbeeld een tabel Boeken in een bibliotheeksysteem. We vertrekken van een voorstelling voor boeken en personen.

We willen volgende gegevens in het systeem bijhouden:

Gebruik eerst USE om je database te activeren. Eerst leggen we vast we welke tabellen en welke datatypes we nodig hebben:

Negeer de "1 more" onder "Geboortejaar". Negeer ook het gele sleuteltje. Die zaken komen later. Om de tabel "Personen" aan te maken, schrijven we:

Enkel aanmaken wat niet bestaat

Via het CREATE-commando maak je een nieuwe structuur aan met een bepaalde naam. Als die naam al bestaat, levert dat een foutmelding. Daarom moeten we voorzichtig omspringen met het CREATE commando. We doen dit door onze CREATE enkel uit te voeren als de naam die we willen gebruiken (voor een database of een tabel of een andere structuur) nog niet gebruikt wordt. Hiervoor vervangen we bijvoorbeeld CREATE TABLE MyTable (MyColumn VARCHAR(100)); door CREATE TABLE IF NOT EXISTS MyTable (MyColumn VARCHAR(100));. Dit vermijdt dat we op een foutmelding botsen. Het kan wel een waarschuwing opleveren, maar dat is op zich niet erg.

DROP DATABASE

Om een database te verwijderen, gebruik je een statement van de vorm DROP DATABASE, gevolgd door de naam van de databank. Als je niet zeker bent dat deze database bestaat en je een foutmelding wil vermijden, gebruik dan drop database if exists.

DROP TABLE

Hier is de syntax gelijkaardig, maar je moet de database kiezen waaruit je een tabel laat vallen.

Bijvoorbeeld:

Vaak kan je in een bepaalde kolom maar een beperkt aantal mogelijke waarden invullen. Een datatype zoals INT of VARCHAR is dan niet op zijn plaats, omdat die heel veel mogelijke waarden toelaten. Dat zorgt voor inefficiëntie en een grotere kans op foute invoer.

Voor dergelijke kolommen is het beter enumeraties te gebruiken, of ENUMs, om het met de woordenschat van MySQL te zeggen. Een ENUM is een stuk tekst met een waarde uit een op voorhand aangegeven stel mogelijkheden.

Veronderstel dat je een database met stukken kledij bijhoudt, bijvoorbeeld deze:

Als de winkel alleen polo's, broeken en truien verkoopt en als er maar drie formaten bestaan, stel je de tweede en derde kolom best voor met een enumeratie.

Dat gaat als volgt:

Noem die code 0026__CreateKledingstukken.sql.

Vervolgens kan je data aanmaken alsof de tweede en derde kolom tekst bevatten, met 0027__InsertKledingstukken.sql:

Wat niet gaat, is dit (hoef je niet op te slaan):

Dat komt omdat we alle mogelijke waarden al hebben vastgelegd.

Enumeraties hebben verschillende voordelen:

• Ze zijn zuiniger in gebruik van opslagruimte dan strings die dezelfde tekst voorstellen.

• Ze zorgen voor meer leesbare invoer en uitvoer dan getallen.

Let wel op! Enumeraties lijken op strings, maar ze worden anders gesorteerd. De volgorde waarin waarden van een enum gesorteerd worden, is de volgorde waarin de elementen gedeclareerd zijn.

Dit kan je afleiden uit volgend script (0028__SelectKledingstukken.sql):

Wat zou dit geven als formaat een VARCHAR was?

Soorten datatypes

MySQL ondersteunt drie brede soorten datatypes, met per soort verschillende concretere vormen. Deze drie soorten zijn:

1. string types (ofwel "tekst")

2. numerieke types (ofwel "getallen")

3. temporele types (datums en/of tijdstippen)

In deze cursus beperken we ons tot vier brede types: varchar, int, float en datetime. Dit is een vereenvoudigde weergave, bedoeld om de concepten aan te leren. Er zijn nog andere basistypes en je kan ook bepaalde extra beperkingen opleggen. Je kan alle details terugvinden in de (uitstekende) officiële MySQL documentatie.

String types

VARCHAR

VARCHAR stelt een stuk tekst met een bepaalde maximumlengte voor. Zo is een kolom met type VARCHAR(50) beperkt tot maximum 50 karakters.

strings schrijven

Als je data van dit type hebt, zet je het tussen enkele of dubbele aanhalingstekens. Bijvoorbeeld 'Do,Re,Mi,Fa,Sol,La,Si'. Als je data zelf aanhalingstekens bevat, gebruik dan het andere type voor de gehele string.

numerieke types

INT en varianten

INT dient voor de opslag van gehele getallen. Standaard kan een INT positieve en negatieve gehele getallen voorstellen. Je kan met INT geen kleiner getal opslaan dan -2147483648 en geen groter getal dan 2147483647.

FLOAT

Dit type dient om kommagetallen bij benadering op te slaan. Dit wil zeggen dat (vaak héél kleine) afrondingen toegestaan zijn wanneer je data in het systeem plaatst.

getallen schrijven

Deze data zet je niet tussen quotes. Je gebruikt een punt in plaats van een komma.

temporele types

Temporele types worden gebruikt om tijdstippen voor te stellen. Hierbij wordt volgende notatie gebruikt om een formaat voor te stellen:

• Y: cijfer van een jaar

• M: cijfer van een maand

• D: cijfer van een dag

DATETIME

Een DATETIME is een waarde die een specifiek ogenblik in de tijd voorstelt. Met andere woorden ongeveer een combinatie van een dag en een moment van de dag. Het formaat is YYYY-MM-DD HH:MM:SS en het bereik ligt tussen het jaar 1000 en 9999.

datums schrijven

Je schrijft datums alsof het strings waren in een afgesproken formaat, dus wel tussen quotes.

defaultwaarden

Je kan er bij het aanmaken of wijzigen van een kolom voor zorgen dat je een kolom niet uitdrukkelijk hoeft in te vullen. Dat doe je door, na de naam van de kolom, het woordje default noteren, gevolgd door een waarde van dat type. Bijvoorbeeld:

Dit voegt een nieuwe kolom toe voor het ISBN-nummer aan een bestaande tabel boeken. Dit is een tekstkolom van maximum 25 symbolen. Als de gebruiker géén ISBN invult, krijgt een boek automatisch de waarde ABC123 in die kolom.

Concept

Het is niet erg handig om telkens de primaire sleutel achteraf toe te voegen. Als je een nieuwe tabel maakt, kan je in één keer een kolom aanduiden als primaire sleutel:

USE ApDB;
DROP TABLE IF EXISTS Boeken;

CREATE TABLE Boeken(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Voornaam varchar(50) char set utf8mb4,
    Familienaam varchar(80) char set utf8mb4,
    Titel varchar(255) char set utf8mb4,
    Uitgeverij varchar(255) char set utf8mb4,
    Stad varchar(50) char set utf8mb4,
    Verschijningsdatum varchar(4),
    Herdruk varchar(4),
    Commentaar varchar(2000) char set utf8mb4,
    Categorie varchar(120) char set utf8mb4
);

Deze hoef je niet uit te voeren.

Relatietypes voorstellen

Afhankelijk van hoe entiteiten aan elkaar gekoppeld kunnen zijn, delen we de koppelingen tussen deze entiteiten op in categorieën:

• een-op-een-relaties, d.w.z. één entiteit van een entiteittype hoort bij exact één entiteit van een gekoppeld type

• een-op-veel-relaties, d.w.z. één entiteit van een entiteittype hoort niet bij 0, 1 of meerdere entiteiten van een gekoppeld type

• veel-op-veel-relaties (ook wel M-op-N relaties genoemd)

Er is nog een indeling in relaties: identificerende tegenover niet-identificerende relaties. Dit onderscheid heeft niet veel impact op het basisgebruik van een databank. Met de werkwijze die wij volgen, kan je steeds niet-identificerende relaties gebruiken, tenzij het om veel-op-veel relaties gaat.

een-op-een relaties

De simpelste verbanden zijn één-op-één verbanden. Dat wil zeggen: precies twee rijen nemen deel aan de relatie. Normaal zijn dit rijen van verschillende entiteittypes, al is het niet verplicht.

Een voorbeeld: een sportclub organiseert een jaarlijks etentje en alle leden krijgen precies één taak. Eén lid zorgt bijvoorbeeld voor bestek, een ander voor frisdrank, een ander voor onderleggers, enzovoort. De club gebruikt een database om de taken en de leden bij te houden en de taken zijn elk jaar dezelfde. Bijvoorbeeld:

• taken:

  • bestek voorzien

  • frisdrank meebrengen

Onderstaande figuur stelt deze indeling voor in een ERD getekend in de editor van MySQL Workbench:

In dit geval is het logisch om een aparte tabel (Taken) voor taken en een aparte tabel (Leden) voor leden te gebruiken. Het is onhandig om uit te leggen dat één rij in de databank een lid en een taak voorstelt, omdat de tabellen best zo goed mogelijk overeenstemmen met duidelijke concepten. Het is logischer de leden en de taken als aparte entiteiten te beschouwen en een relatie tussen beide vast te leggen.

voorstelling van één-op-één relaties

Om een relaties tussen rijen van de tabellen vast te leggen, maken we de rijen eerst identificeerbaar met een primaire sleutel. Bijvoorbeeld, voor de taken:

Voor de leden:

Zet de structuur van de twee tabellen om in SQL-tabellen met een script 0052__CreateTakenLeden.sql. Elke normale kolom bestaat uit een reeks van maximaal 45 karakters en is verplicht. De Id-kolom stel je voor met een INT die automatisch ophoogt. Elke kolom heeft dezelfde naam die gebruikt wordt in de hoofdingen hierboven.

Vul de twee tabellen, Taken en Leden in met een script 0053__InsertTakenLeden.sql.

Als Bavo bestek voorziet, Yannick frisdrank meebrengt en Max aardappelsla maakt, kunnen we dat als volgt bijhouden in een aparte tabel die alleen vreemde sleutels bevat:

In de praktijk wordt er normaal niet voor gekozen om deze relatie in een aparte tabel vast te leggen. Dat zou gaan, maar het is gewoon een beetje te veel van het goede. Je hebt geen aparte tabel nodig. Eén van de twee tabellen wordt uitgebreid met een foreign key. Er zijn twee mogelijkheden:

of

Beide zijn even goed. Typisch wordt (in geval van een niet-identificerende relatie en die gebruiken wij altijd) gekozen om de foreign key in de tabel te zetten met het kleinste aantal kolommen, om alles een beetje in evenwicht te houden. Hier hebben beide even veel kolommen dus het maakt helemaal niet uit.

Pas je tabel Leden aan zodat ze de tweede mogelijkheid van hierboven implementeert in een script 0054__AlterLeden.sql. Je moet eerst de kolom toevoegen, dan invullen, dan verplicht maken.

Dit ziet er zo uit:

Je zou de data nu kunnen combineren, maar daar heb je een JOIN-operatie voor nodig. Die komt later.

een-op-veel relaties

Een een-op-veel (of 1-op-N) verband is een verband dat je heel vaak tegenkomt op websites met een achterliggende databank. Bij dit soort verband stemt een rij uit een bepaalde tabel A overeen met meerdere rijen uit een tabel B. In de omgekeerde richting stemt een rij uit tabel B maar met één rij van tabel A overeen. Bijvoorbeeld, als je tweets bijhoudt in een databank, kan één persoon meerdere tweets hebben, maar één tweet kan oorspronkelijk slechts van één persoon komen. Hier moet je je inbeelden dat personen bijgehouden worden in tabel A en tweets in tabel B.

In een database van een webshop kan één persoon meerdere bestellingen plaatsen, maar één bestelling kan slechts van één klant komen. Hier geldt: personen in A, bestellingen in B.

Hier zijn enkele tweets die we als voorbeeld zullen gebruiken, voorafgegaan door de handle van de gebruiker die ze geschreven heeft:

Zoals in het geval van de 1-op-1 relatie, kunnen we deze relatie tussen gebruikers en tweets voorstellen in een tabel:

Dit is opnieuw iets meer dan we nodig hebben. We kunnen een foreign key van één tabel toevoegen aan een andere. Maar, in tegenstelling tot de precieze 1-op-1-relatie, mogen we niet kiezen. We zetten de foreign key in de tabel die niet aan de "exact-1"-kant van de relatie zit. Zorg er ook voor dat de vreemde sleutel nooit NULL is met een constraint.

Voer dit zelfstandig uit voor de reeks tweets hierboven. Volg de reeds afgesproken afspraken: één tabel Users voor users (met een kolom Handle), één tabel Tweets voor tweets (met een kolom Bericht), beide voorzien van primaire sleutels, met de vreemde sleutel aan de "N-kant". Stel gebruikersnamen en tweets voor met kolommen van variabele lengte (tot 144 tekens), zonder internationale tekens. De @ maakt geen deel uit van een gebruikersnaam. Zet de SQL-code die je nodig hebt om de (lege) tabellen te maken in een script 0055__CreateUsersTweets.sql. Zet de code die je nodig hebt om de vreemde sleutel toe te voegen in 0056__AlterTweets.sql. Zet ten slotte de code om de tabellen in te vullen in een script 0057__InsertUsersTweets.sql. Begin met een INSERT voor de users, doe dan pas die voor de tweets.

Voor het laatste script bespaart onderstaande gedeeltelijke SQL je het copy-pasten van de tweets.

speciaal geval: een-op-max-een-relaties

Een een-op-max-een relatie is een relatie waarbij één entiteit A gelinkt is aan hooguit één andere entiteit B. Het kan ook zijn dat A aan geen enkele B gelinkt is. Deze stel je voor zoals een 1-op-N relatie, dus met de vreemde sleutel in de tabel aan de niet-exact-1-kant.

Hoe je de tweets terug koppelt aan de juiste account, lees je ook bij de uitleg rond . We tonen hier alleen dat het mogelijk is. Koppel users aan de juiste tweets met dit script, 0058__SelectUsersTweets.sql:

veel-op-veel relaties

Een auteur kan meerdere boeken hebben en een boek kan verschillende auteurs hebben. Een game kan op verschillende platformen uitgebracht zijn en voor elk platform zijn er verschillende games beschikbaar. Een student volgt verschillende vakken en in elk vak zitten verschillende studenten. Dit zijn allemaal voorbeelden waar één rij uit een tabel A gekoppeld kan zijn aan meerdere rijen uit een tabel B en één rij uit dezelfde tabel B gekoppeld kan zijn aan meerdere rijen uit dezelfde tabel A. We zeggen dan ook dat er een veel-op-veel of M-op-N-relatie bestaat tussen de entiteiten A en B.

Bij 1-op-1-relaties mochten we de vreemde sleutel in tabel A of B zetten (en sommige systemen vereisen zelfs dat je de tabellen gewoon samensmelt). Bij 1-op-max-1 of 1-op-N relaties zetten we de vreemde sleutel in de tabel die niet precies één keer gekoppeld was. Dit werd vooral gedaan om geen overbodige tabellen toe te voegen. We konden in principe de takenverdeling voor het etentje ook als volgt voorstellen, met een aparte tabel:

We deden dit alleen anders omdat we het met een tabel minder (en dus in totaal ook een kolom minder) konden. Voor een M-op-N-relatie is deze voorstelling echter onze beste optie.

voorbeeld

Een game kan beschikbaar zijn op meerdere platformen en op elk platform zijn er natuurlijk meerdere games beschikbaar. Bijvoorbeeld:

• Anthem: beschikbaar op PS4, XBox One, Windows

• Sekiro: beschikbaar op PS4, XBox One, Windows

• Devil May Cry 5: beschikbaar op PS4, XBox One

Veronderstel dat Anthem ID 1 heeft, Sekiro 2, enzovoort. Veronderstel ook dat PS4 ID 1 heeft, Xbox One ID 2, Windows ID 3 en Nintendo Switch ID 4. Dan kunnen we voorstellen welke games uitgebracht zijn op welke platformen als volgt:

Je hebt hier drie tabellen nodig: een voor games, een voor platformen, een voor de koppeling. De tabel Games heeft naast de Id één kolom: Titel, een stuk tekst van maximaal 50 karakters dat nooit leeg mag zijn en mogelijk Unicode karakters bevat. Voor Platformen is er een gelijkaardige structuur, maar de naam van de kolom die niet als sleutel wordt gebruikt is Naam. Noem de tabel die de koppeling afhandelt Releases. Volg de conventie voor de naam van de kolommen die naar beide andere tabellen verwijzen. Sla de DDL-instructies op als 0059__CreateGamesPlatformenReleases.sql. Voeg de DML-instructies toe als 0060__InsertGamesPlatformenReleases.sql.

Voor 0059 zou je dit moeten hebben:

Dit stemt overeen met een diagram in Workbench dat er zo uitziet:

Voor 0060:

legt uit hoe je nu toont welke games op welk platform verschenen zijn. Dit wordt hier gedaan in 0061__SelectReleases.sql:

Relaties met attributen

Attributen horen meestal bij entiteiten, maar kunnen ook bij relaties horen. Bovenstaande tabel Releases geeft bijvoorbeeld aan welk spel op welk platform verschenen is, maar wat als we de releasedatum willen bijhouden? Deze hoort niet in de tabel Games. Hij hoort ook niet in de tabel Platformen. Hij hoort bij de combinatie van een game en een platform, d.w.z. bij de relatie die wordt voorgesteld met de tabel Releases. Daarom kunnen we de tabel ook uitbreiden met een kolom Releasedatum. Voor het leesgemak stellen we de games en de platformen niet voor via hun Id-attribuut.

In een ERD stellen we dit als volgt voor:

In dit geval is Releases niet gewoon een tabel die een relatie voorstelt, maar wel een associative entity: een relatie tussen Games en Platformen die eigen kenmerken bezit, zodat je ze eigenlijk ook als een entiteit zou kunnen zien.

Voeg zelf de nodige info toe. Hiervoor volg je volgende stappen:

1. Voeg een kolom van type DATE toe aan de tabel Releases. Deze kan nog niet verplicht zijn. Noem het script 0062__AlterReleases.sql.

2. Kopieer het script dat games en hun releaseplatform weergeeft naar een nieuw script, 0063__UpdateReleases.sql.

3. Pas voor de gecombineerde tabel de datum aan volgens de gegevens hierboven. Je kan in deze tabel een

Hier komen geen nieuwe ideeën aan bod, maar je moet de eerdere stappen goed begrijpen om deze stappen te doorlopen.

Verdere soorten relaties

Tabellen kunnen meer dan twee entiteiten verbinden. Voor releases van games kan je bijvoorbeeld een spel, een uitgever en een platform aan elkaar linken met een M-op-N-op-K relatie. Dit is wel niet vaak nodig, dus denk altijd even goed na voor je dit doet. De keuze berust vooral op een goede analyse en goed overleg met de klant! In deze cursus zal je nooit een ternaire (d.w.z. tussen drie entiteiten) of hogere relatie nodig hebben. En, zoals eerder gezegd, gaan we niet verder in op het onderscheid tussen identificerende en niet-identificerende relaties.

Hier zien we iets meer in verband met het aanmaken van structuren voor je data. We starten onze database met behulp van onderstaand calibratiescript, dat je 0013__Calibratie.sql mag noemen:

ALTER TABLE

ALTER TABLE verandert de structuur van een tabel, zonder bestaande data te beschadigen. Je kan het gebruiken om kolommen toe te voegen of te verwijderen. Je kan het ook gebruiken om het soort data in een kolom aan te passen, specifieker of juist breder te maken. Voor deze taken heb je binnenin een ALTER TABLE statement extra clausules nodig.

een kolom schrappen

Script bijhouden

Sla het script om de voornaam te verwijderen op wanneer je klaar bent. Geef het de naam 0014__AlterBoeken.sql.

een kolom toevoegen

Om een kolom toe te voegen maak je gebruik van de ADD clausule bij het ALTER statement. Je zou hier misschien het woordje CREATE verwachten. Zoals je ondertussen weet, gebruik je in sql namelijk het keyword CREATE om een structuur aan te maken. Maar binnenin ALTER is het dus ADD! Je moet m.a.w. aangeven dat je iets, in dit geval een kolom, wil toevoegen aan de tabel.

Je merkt in bovenstaand script dat er opgave wordt gegeven van een CHAR SET. Standaard is utf8mb4 van toepassing voor MySql, maar we vermelden ze hier uitdrukkelijk, omdat er soms ook andere karaktersets worden gebruikt.

Script bijhouden

Pas dit script aan om naast de kolom Commentaar ook de kolom Voornaam terug toe te voegen en een kolom Familienaam toe te voegen, beide VARCHAR(100) en niet verplicht. Sla je script opnieuw op wanneer je klaar bent. Geef het de naam 0015__AlterBoeken.sql.

beperkingen toevoegen

Het is goed om van meet af aan de integriteit van de database te denken. We gaan ervan uit dat de familienaam moet ingevuld worden. Zelfs al is de auteur onbekend, moet dan zoiets als "onbekend" worden ingevuld. Om een kolom verplicht te maken voegen we een constraint toe. In volgende code zijn OldColumnName, NewColumnName en NewColumnType placeholders voor de namen van twee kolommen en een datatype met de nodige constraints:

Via de NOT NULL constraint kunnen we zorgen dat een kolom een waarde moet bevatten, maar die regel mogen we enkel opleggen als er momenteel geen rijen zonder waarde in die kolom zijn.

Eerst moeten we ervoor zorgen dat de nieuw toegevoegde kolom voor iedere rij een waarde krijgt, tot nu is deze waarde NULL.

Vervolgens gaan we de kolom Familienaam qua structuur wijzigen en een beperking opleggen.

Script bijhouden

Sla een script met bovenstaande UPDATE en ALTER achter elkaar opnieuw op wanneer je klaar bent. Geef het de naam 0016__AlterBoeken.sql.

tabelnamen wijzigen

Het zou misschien te gemakkelijk geweest zijn als het wijzigen van tabelnamen in MySQL met het ALTER statement kon worden uitgevoerd. Om de naam van een tabel te wijzigen kunnen we het ALTER statement niet gebruiken. Er bestaat daarvoor een apart RENAME statement. De generieke vorm is als volgt. Let op het gebruik van backticks rond de naam van de tabellen. Die zijn verplicht in het geval dat de naam van de tabel overeenkomt met een gereserveerd woord van MySQL:

In ons voorbeeld:

Sleutels in MySQL: motivatie

De tabellen die je tot hiertoe gemaakt hebt in MySQL, groeperen heel veel informatie in één record. Informatie groeperen is een belangrijk doel van databanken, maar het gebeurt beter pas wanneer de groepering zelf nodig is. Anders krijg je databanken die meer plaats innemen dan nodig, die makkelijker fouten zullen bevatten en die moeilijker te navigeren zijn. Het belangrijkste principe om de data pas te groeperen wanneer dat nodig is, is dat van een sleutel. Hier bekijken we het binnen MySQL.

Met een sleutel kan je elke rij in een tabel aanduiden zonder te verwijzen naar de eigenlijke informatie in die rij. We wijzen eerst op enkele problemen met de aanpak die we tot hier gehanteerd hebben en leggen dan uit hoe sleutels deze problemen kunnen oplossen.

Een eerste problematische tabel

Soms hebben twee rijen dezelfde waarden in bepaalde velden, maar gaat het toch over verschillende data. Dit is een eerste probleem dat we kunnen oplossen met attributen die we aanduiden als sleutels.

Een quizvraag voor de gamers: wat hebben de games God of War, Doom en Tomb Raider met elkaar gemeen? Antwoord: het zijn allemaal remakes van oude games met dezelfde titel en dezelfde ontwikkelaar.

Als je een tabel van (oude en nieuwe) games aanmaakt, met als kolommen hun titel en ontwikkelaar, zou een stukje van de tabel er dus als volgt kunnen uitzien:

Voor SQL is er geen verschil tussen de oude en de nieuwe versies van deze games, maar het gaat in werkelijkheid wel om verschillende zaken. Je zou het jaar van uitgave als kolom kunnen toevoegen om elke rij uit elkaar te houden. Of de spelconsole waarop het spel uitkwam. Of misschien is de uitgever steeds veranderd. Dan kan je elke rij uniek maken door die toe te voegen. Maar niets verhindert een ontwikkelaar om twee gelijknamige games meteen na elkaar uit te geven bij eenzelfde uitgever.

Het idee om elke rij uniek te maken is goed, maar je volgt best een voorzichtige aanpak. Dit kan door kolommen toe te voegen die een rij uniek kunnen identificeren. Een stel kolommen waarmee je een rij uniek kan identificeren, kan dan aangeduid worden als primaire sleutel of primary key. Deze verzameling kolommen (of ene kolom) hoeft niet noodzakelijk "leesbare" informatie te bevatten. Vaak is het gewoon een getal, zoals in dit voorbeeld, waarin de kolom Id een primaire sleutel is:

Je kan in SQL uitdrukken dat een bepaalde kolom wordt gebruikt als primaire sleutel, zodat je nooit per ongeluk twee rijen kan aanmaken met eenzelfde waarde in deze kolom.

Sleutels voor efficiënt gebruik van ruimte

Tabellen zoals we ze eerder soms hebben gezien, zijn ook niet bruikbaar voor (middel)grote systemen omwille van een efficiëntieprobleem. Sleutels zullen je ook toestaan op grotere schaal te werken.

Veronderstel dat je, in opdracht van Game Mania, een databank met videogames moet opstellen. Ze hebben momenteel volgende producten in hun inventaris en willen deze in hun nieuwe databank opslaan:

Hier is elke rij wel verschillend, maar toch is er een probleem. Denk eraan dat we in onze definities zo precies mogelijk uitdrukken of iets CHAR, VARCHAR,... is, hoe veel karakters er in passen,... Dat is omdat een databank zuinig moet zijn voor goede performantie. Bovenstaande tabel is dat niet: er zijn heel veel stukken lange tekst die regelmatig terugkomen en die nemen elke keer heel wat bytes in.

Zuinig zijn is bovendien niet alleen belangrijk voor performantie, maar helpt ook fouten te voorkomen. Hoe vaker we een waarde volledig moeten uitschrijven, hoe groter de kans dat we eens een fout maken.

Het zou al zuiniger zijn elke game en elk platform aan te duiden met een uniek identificatienummer. Dat bespaart heel veel ruimte tegenover wanneer we telkens de volledige tekst uit te schrijven. We kunnen bijvoorbeeld het volgende afspreken voor de titels:

• Anthem: 1

• Sekiro: Shadows Die Twice: 2

• Devil May Cry 5: 3

Voor de platformen:

• PS4: 1

• XBox One: 2

• Windows: 3

Dan krijgen we voor de hele tabel:

Merk op dat we onze mappings van games / platformen op getallen ook in twee tabelvoorstellingen met telkens 2 kolommen (de game/het platform en het volgnummer) kunnen gieten. Dat is dan ook wat we zullen doen. We zullen de volgnummers aanduiden als primary keys die automatisch ophogen. Eens we dat gedaan hebben, kunnen we bijna op een heel efficiënte wijze data gaan combineren (met behulp van JOIN-operaties). We zullen tabellen gelijkaardig aan die hierboven gebruiken om te verwijzen naar primaire sleutels in andere tabellen. Zo'n verwijzing zal een vreemde sleutel (foreign key) heten.

Primaire sleutel toevoegen/verwijderen voor een bestaande tabel

We vertrekken hier van volgend script, 0048__CalibrateDB.sql:

de basis

We voegen een Id kolom toe aan de tabel Boeken die we als primaire sleutel gaan gebruiken.

Om een primaire sleutel toe te voegen aan een reeds bestaande tabel, gebruik je de DDL ALTER TABLE instructie in combinatie met een DDL ADD instructie:

Sla die instructie op in 0049__AlterBoeken.sql.

Je kan ook nagaan of de primaire sleutel is toegevoegd door het volgende statement uit te voeren:

Het feit dat een kolom een primaire sleutel is, is een constraint. Men spreekt van een constraint als iets een beperking is. Als je een rij zou toevoegen met een Id waarvan de waarde reeds in een andere rij bestaat, krijg je een foutmelding. Een ander (en reeds gekend) voorbeeld van een constraint is NOT NULL. Deze constraint is zwakker dan de PRIMARY KEY constraint (omdat NULL niet geschikt is om een rij te identificeren), dus je hoeft ze nooit toe te voegen aan een kolom die dient als primaire sleutel.

AUTO_INCREMENT

Het is beter om de Id door SQL zelf te laten toekennen. Zo hoef je niet telkens na te kijken welke waarde beschikbaar is voor Id. Om dat te doen, gebruik je de eigenschap AUTO_INCREMENT. Als je een nieuwe tabel maakt voeg je de eigenschap toe na de declaratie van de kolom. Zorg ervoor dat je op die kolom een primary key constraint hebt staan:

Sla op als 0050__CreatePersonen.sql.

Je kan de beginwaarde zelf bepalen. Bijvoorbeeld, als je de boeken wil nummeren vanaf 5 in plaats van 1 (de default):

Dit kan van pas komen als je al wat data hebt en SQL alleen voor de nieuwe data zelf de nummers wil laten genereren.

Primary key constraints verwijderen

Een constraint behoort tot de definitie van de tabel, dus moet je DROP gebruiken:

Oefeningen

Tip

Als je tabellen maakt, zit het niet altijd meteen juist. Vaak probeer je een bepaalde structuur voor je data en verbeter je deze regelmatig. Om je de structuur van je data te bekijken, moet je in MySQL Workbench eerst je databanken refreshen. Daarna kan je je databank openklappen en via rechtermuisklik de structuur van je tabel controleren.

nummers

Schrijf een script dat een tabel Nummers voor muzieknummers toevoegt aan je databank met volgende karakteristieken:

Sla op als 0019__Oefening.sql.

huisdieren

Doe hetzelfde voor een tabel Huisdieren met huisdieren, met volgende kenmerken:

Sla op als 0020__Oefening.sql.

data nummers

Plaats volgende data in je tabel met muzieknummers:

1. het nummer "John the Revelator" van de groep "Larkin Poe". Het genre is "Blues" en het verschijningsjaar is 2017.

2. het nummer "Missionary Man" van de groep "Ghost". Het genre is "Metal" en het verschijningsjaar is 2016.

Sla op als 0021__Oefening.sql.

data huisdieren

Plaats volgende data in je tabel met huisdieren:

1. Ming, 9 jaar oud, is de hond van Christiane

2. Bientje, 12 jaar oud, is de kat van Esther

3. Misty, 7 jaar oud, is de hond van Vincent

Sla op als 0022__Oefening.sql.

zoekopdracht huisdieren

Toon met een SELECT de duo's met huisdieren en baasjes in het formaat H+B, gerangschikt volgens de leeftijd van het huisdier.

De juiste oplossing toont volgende rijen in MySQL Workbench, in exact deze volgorde:

Sla op als 0023__Oefening.sql.

een kolom voor klassificatie

Klassificatie van boeken is een wetenschap op zich. Er zijn veel systemen voor, maar ze gebruiken allemaal een code van een vrij klein aantal letters om aan te geven in welke cluster een bepaald boek thuishoort.

Schrijf zelf een script dat een verplichte kolom met naam Categorie toevoegt aan de bestaande tabel met boeken. De categorie van een boek wordt uitgedrukt in maximaal 40 lettertekens. Dit zijn "gewone" lettertekens die je zou aantreffen in een typische Engelstalige tekst. Deze kolom heeft ook een defaultwaarde, namelijk "Zonder categorie".

Noem je script 0017__Oefening.sql.

een kolom voor de klasseur

Het is handig om bij te houden wie een boek in het systeem heeft geplaatst. Daarom willen we een extra kolom met de naam IngevoegdDoor die maximaal 255 karakters lang is en zeker internationale karakters moet kunnen bevatten. Deze kolom is niet verplicht.

Noem je script 0018__Oefening.sql.

observaties (structuur)

We willen wetenschappelijke observaties van het grondwaterniveau bijhouden. Maak hiervoor een tabel GrondwaterObservaties met twee verplichte kolommen: één kolom voor het tijdstip van de observatie (dag, maand, jaar, uur, minuten én seconden) en één kolom voor het grondwaterniveau uitgedrukt als kommagetal. Het grondwaterniveau hoeft niet met perfecte precisie voorgesteld te worden en je hoeft er niet veel opslagruimte voor te gebruiken. Bepaal zelf het meest geschikte kolomtype.

Noem je script 0024__Oefening.sql

observaties (data)

Plaats volgende informatie in je nieuwe tabel:

Noem je script 0025__Oefening.sql

aankopen (structuur en data)

In een winkelsysteem willen we alle aankopen registreren. Maak daarom een tabel Aankopen met drie stukjes informatie, die allemaal verplicht zijn:

• de naam van het artikel, uitgedrukt als verplichte tekst van maximum 100 karakters

• het aantal stuks, uitgedrukt als positief getal dat past in één byte

• de aankoopprijs per stuk, uitgedrukt als exact kommagetal dat bestaat uit vijf cijfers, waarvan twee na de komma

Plaats, in hetzelfde script, volgende gegevens in deze tabel:

Noem je script 0029__Oefening.sql

bestellingen auto's

Maak een tabel Bestellingen die kan dienen voor het bestelsysteem van een autodealer. Deze bevat drie (verplichte) zaken:

• het tijdstip waarop de bestelling geplaatst is

• het model - dit kan alleen een van onderstaande opties zijn (tip: gebruik een enum datatype)

  • Qivic

Noem je script 0030__Oefening.sql

Inspecteren van je data

Je kan in MySQL Workbench wel rechtstreeks naar je tabellen kijken, maar om gerichter te zoeken, moet je het commando kennen dat achter de schermen wordt uitgevoerd. Dat is het SELECT-commando.

De belangrijkste commando's van de DML zijn:

• INSERT: Hiermee voeg je data in

• SELECT: Hiermee vraag je data op

In de meeste gevallen zijn we niet geïnteresseerd om alle rijen uit een tabel te selecteren. We willen over de mogelijkheid beschikken om alleen de rijen, die aan een bepaalde voorwaarde voldoen, te kunnen selecteren.

De oplossing bestaat erin de WHERE-clausule te gebruiken. De WHERE-clausule bevat een booleaanse expressie. Dit is een expressie die als resultaat voor een bepaalde rij in principe TRUE of FALSE moet opleveren. In tegenstelling tot de meeste programmeertalen gebruikt SQL ook een derde mogelijke waarde: NULL. Deze waarde komt voor wanneer we niet kunnen bepalen of iets waar of niet waar is.

Om de WHERE te gebruiken, zet je hem na de FROM Tabel.

Bijvoorbeeld:

Ofwel (sla volgend script op als 0032__SelectBoeken.sql):

Je krijgt NULL wanneer je bijvoorbeeld vergelijkt met een niet-ingevulde waarde, ook geschreven als NULL. Zelfs NULL is niet gelijk aan NULL. Probeer maar eens alle boeken zonder titel op te vragen met een vergelijking (0033__SelectBoeken.sql):

Maar probeer ook deze eens (0034__SelectBoeken.sql):

Het ligt niet aan de data! Vergelijkingen met NULL via = en <> zijn zinloos! Als je wil controleren of de waarde in een bepaalde kolom ontbreekt, schrijf dan IS NULL in plaats van = NULL!

In dit deel leer je data over verschillende tabellen heen te combineren. Er is nog veel meer te leren over de DML, maar dit is het meest gevorderde gebruik van de DML in deze cursus.

Net zoals het DROP statement verwijdert het DELETE statement objecten uit de database. Het DROP statement verwijdert een tabel uit de database, het DELETE statement verwijdert hele rijen uit de tabel maat laat de tabelstructuur staan.

Meestal willen we specifieke records verwijderen en geen volledige tabellen leegmaken. Daarom ondersteunt DELETE dezelfde WHERE-clausule als SELECT. Als je geen WHERE clausule gebruikt, worden alle rijen uit de tabel verwijderd en blijft alleen de structuur van de tabel over. Wees daar dus voorzittig mee want als de rijen gedeletet zijn kan je ze niet meer terughalen. Net als bij UPDATE verbiedt MySQL standaard bepaalde "onveilige" operaties. Ook hier gebruik je SET SQL_SAFE_UPDATES = 0 en SET SQL_SAFE_UPDATES = 1 voor, respectievelijk na de operatie.

De syntax van DELETE lijkt erg op die van SELECT, maar in plaats van bepaalde rijen te tonen, zal MySQL ze gewoon wissen. Je kan ook geen specifieke kolommen wissen, dus je schrijft DELETE FROM Boeken en niet DELETE * FROM Boeken of DELETE Voornaam FROM Boeken.

Relationele databases vormen regelmatig de ruggegraat van (web)applicaties. We zullen dat in de verdere labo-oefeningen demonstreren. Voor de verdere oefeningen zullen we voornamelijk werken met een database genaamd apTunes. Dit is een database die je zou kunnen gebruiken om een streamingdienst (vergelijkbaar met Spotify, Deezer,...) te ondersteunen.

apTunes moet allerlei functionaliteit ondersteunen die je gewend bent van echte streamingdiensten, onder meer:

• voor de eindgebruiker:

  • zoeken op titel, artiest, genre, etc. van een nummer

  • bijhouden van een persoonlijke collectie nummers, albums, playlists

  • nummers opslaan als favorieten

  • enzovoort

• voor de eigenaar:

  • de populairste nummers binnen een bepaalde periode opvragen

  • per artiest het aantal afgespeelde nummers in een bepaalde periode opvragen

Tegen het einde van de cursus ken je genoeg MySQL om de databasekant van een prototype van een dergelijke streamingdienst te schrijven.

Het startpunt

We starten vanaf een voorstelling die we steeds beter en beter zullen maken:

Schrijf zelf code die deze twee tabellen aanmaakt en noem ze aptunes__0001.sql. Merk op dat de duurtijd uitgedrukt wordt in seconden.

Mogelijkheden verkennen

• Voer bovenstaand script uit en zet het in je map met scripts met de naam aptunes__0002.sql.

• Schrijf een script, aptunes__0003.sql, dat alle nummers van Led Zeppelin in het systeem toont volgens titel.

• Schrijf een script, aptunes__0004.sql, dat alle nummers van Ghostface Killah en alle nummers van Blues Pills samen toont, eerst volgens artiest en dan volgens titel. Dus eerst komen alle nummers van Blues Pills in alfabetische volgorde en dan alle nummers van Ghostface Killah in alfabetische volgorde.

Denk nu even na over een aantal zoekopdrachten die je met dit model nog niet zou kunnen doen. Noteer ze voor jezelf. Bespreek tijdens het labo klassikaal.

Structuur verbeteren

Voor onze streamingdienst is het handiger te werken met een vaste set van genres. Anders krijgen we zo veel subgenres dat het niet meer handig is genres te gebruiken. Vandaar volgende wijziging:

• Voeg met script aptunes__0008.sql een kolom GenreEnum toe. Deze is voorlopig niet verplicht en kan alleen volgende waardes bevatten: Klassiek, Pop, Jazz, Metal, Rap, Electro, Folk, Wereldmuziek, Blues, Rock

We merken ook dat het niet erg nuttig is om een exacte datum bij te houden per nummer. We willen alleen het jaar. Je kan het jaar uit een datum halen door er de YEAR-functie op toe te passen.

• Maak met script aptunes__0012.sql een kolom ReleaseJaar aan, vul deze automatisch in op basis van de releasedatum die je al hebt, maak de nieuwe kolom verplicht en verwijder ten slotte de kolom ReleaseDatum. Dit zal niet werken voor de nummers van Debussy, omdat ze te oud zijn om met het YEAR-datatype voor te stellen. Vul daarom het jaar van de uitvoering in voor deze nummers: 1985.

Functionaliteit toevoegen

Nu willen we weten wat de prijs is die de artiest verdient (in eurocent) wanneer een nummer gestreamd wordt. We zullen dit bijhouden met een kolom Royalties. Deze heeft type TINYINTen is unsigned. Er is een vuistregel voor het vastleggen van royalties, maar voor sommige nummers is er een speciale regeling. Omdat er alleen in gehele bedragen in eurocent wordt gewerkt, heb je de functie ROUND nodig om een getal af te ronden.

• Voeg met script aptunes__0013.sql deze kolom toe. Vul ze in als volgt:

  • Klassieke nummers krijgen normaal 1 eurocent per 60 seconden, dus je stelt de royalties in als de duurtijd gedeeld door 60 (afgerond).

  • Rocknummers en metalnummers krijgen 1 eurocent per 20 seconden.

Informatie voor de eigenaars

De eigenaars van onze streamingdienst willen statistieken over de artiesten.

Gebruik eerst volgend script aptunes__0015.sql om meer data in het systeem te plaatsen:

Nu er redelijk wat data is, moeten we die gaan samenvatten eerder dan rij per rij te bekijken.

• Schrijf een script aptunes__0016.sql dat toont hoe veel rocknummers er in het systeem zijn.

• Schrijf een script aptunes__0017.sql dat voor elk genre toont hoe veel nummers er zijn. Het formaat van de uitvoer is als volgt en de genres zijn gesorteerd volgens de volgorde van de enum die de genres voorstelt:

• Toon het releasejaar van het oudste nummer in het systeem. De uitvoer is één rij met één kolom. De titel van deze kolom maakt niet uit. Noem je script aptunes__0018.sql.

• Herschrijf volgende query zonder DISTINCT zodat je toch nog hetzelfde resultaat krijgt en noem je script aptunes__0019.sql: SELECT DISTINCT Artiest FROM Nummers;

• Het blijkt dat erg lange klassieke nummers niet erg winstgevend zijn voor onze dienst. Toon daarom alfabetisch alle artiesten die klassieke nummers hebben, maar enkel als hun klassieke nummers ook gemiddeld langer dan 8 minuten duren. Noem je script aptunes__0023.sql. Tip: afhankelijk van hoe je dit doet, heb je de meeste of zelfs alle clausules nodig.

Normalisatie van de apTunes databank

De apTunes database, zoals ze ontwikkeld is met scripts 1 tot 23, is niet erg efficiënt ontworpen. We zullen ze herstructureren met relationele concepten.

We willen ons ontwerp verbeteren, zodat we uiteindelijk volgende relaties krijgen:

Dit ERD toont alleen de entiteittypes en de relatietypes. De precieze velden ervan moet je zelf kunnen afleiden uit de informatie waarvan je vertrekt.

We zullen eerst de 1-op-N relaties voorstellen.

• Maak een script dat een tabel Artiesten aanmaakt voor artiesten. Noem dit aptunes__0024.sql. Zorg ervoor dat elke artiest die we toevoegen automatisch genummerd wordt.

• Maak een script om de data over artiesten in te vullen in deze tabel. Je mag veronderstellen dat twee artiesten met dezelfde naam dezelfde artiest zijn.

Veel-op-veel (M-op-N) relaties

Fris het vorige deel op indien je ergens niet kan volgen, want deze leerstof bouwt rechtstreeks voort op de vorige. We werken hier verder naar het ontwerp volgens het ERD.

• Het script om GebruikerHeeftAlbum toe te voegen noem je aptunes__0035.sql. Naast de sleutelkolommen voorzie je ook een kolom DatumToevoeging om het tijdstip toe te voegen waarop dit album toegevoegd is aan de bibliotheek. Dit is een verplichte datetime.

• Het script om de data toe te voegen (aptunes__0036.sql) vind je hieronder terug.

Joins

• Toon de titel en de artiest van alle nummers. Maak hierbij gebruik van een inner join. Noem dit script aptunes__0041.sql.

• Toon deze zelfde gegevens nu enkel voor nummers waarvan de titel begint met de letter "A". Noem dit script aptunes__0042.sql.

basisprincipe

Soms maken we fouten bij het ingeven van data. Soms verouderen gegevens. In beide situaties willen we bestaande rijen wel bewaren, maar bepaalde kolomwaarden aanpassen. We kunnen het UPDATE statement hier voor gebruiken.

MySQL staat dit om veiligheidsredenen niet zomaar toe, maar voorlopig zijn we aan het verkennen. Om dit dus toch mogelijk te maken, moet je SET SQL_SAFE_UPDATES = 0 toevoegen voor een "onveilig" commando en achteraf SET SQL_SAFE_UPDATES = 1 toevoegen.

Bijvoorbeeld:

Dit zet de kolom Categorie van alle boeken op "Metafysica".

Je kan ook de inhoud van meer dan één kolom aanpassen. Dat zou je als volgt doen (maar hoef je niet uit te voeren of op te slaan):

Zonder verdere specificatie zet het eerste stukje code de kolom Categorie van alle rijen op 'Metafysica'.

verfijnd aanpassen

Het zou kunnen dat alle boeken in de categorie Metafysica thuishoren. En in dat geval doet het statement precies wat je er van verwacht. Maar meestal willen wie niet alle rijen op dezelfde manier aanpassen. We moeten dus in het UPDATE statement specifiëren in welke rij we de kolom Categorie willen updaten. We moeten één bepaalde rij eruit filteren. Dat doen we met de WHERE clausule. We beperken de update tot de records die we met een SELECT zouden laten zien:

De WHERE clausule bepaalt een voorwaarde die door de database als waar of vals geëvalueerd wordt. De voorwaarde Titel = 'Logicaboek' wordt voor elke rij in de tabel geëvalueerd. Als de evaluatie van de logische expressie voor een bepaalde rij waar oplevert wordt die rij geüpdatet.

samengestelde constructies

Je kan ook meerdere rijen in één keer updaten. Dat doe je door bijvoorbeeld de logische operator OR te gebruiken:

Waar je een nieuwe waarde instelt, mag je ook weer een expressie gebruiken die een waarde oplevert. Net als in programmeren. Je kan bijvoorbeeld een waarde instellen die berekend wordt door strings aan elkaar te hangen of een substring te bepalen:

Het is niet genoeg gegevens uit een tabel te kunnen opvragen, je moet ze ook kunnen sorteren of ordenen. We willen lijsten in alfanumerieke volgorde weergeven omdat het gemakkelijker is in een geordende lijst te zoeken.

Om te sorteren gebruiken we de clausule ORDER BY. Het sorteren kan op 2 manieren:

• oplopend

• aflopend

In het Engels is dat ascending en descending. In SQL wordt dat afgekort naar ASC en DESC. Stel dat je de namen uit de tabel boeken wil opvragen gesorteerd op de voornaam, de familienaam en titel van de boeken. De query ziet er dan zo uit:

Het eerste veld in de ORDER BY clause wordt dan eerst bekeken, dan het tweede, enzovoort. Als niet aangegeven is of de sortering voor een bepaald veld oplopend of aflopend is, is ze standaard oplopend. Dat wil zeggen dat als je ASC en DESC niet vermeldt na een kolom, de code zich gedraagt alsof er ASC staat.

De eerste bovenstaande SELECT clausule toont dus eerst boeken in alfabetische volgorde van de familienamen van de auteurs. Als twee auteurs dezelfde familienaam hebben (althans volgens de gebruikte collation!), wordt hun voornaam gebruikt om de knoop door te hakken. Dan komt het boek met de voornaam die alfabetisch eerst komt ook eerst in de lijst. Als twee auteurs dezelfde naam hebben, of als het gewoon om twee boeken van dezelfde auteur gaat, wordt de titel gebruikt om de knoop door te hakken.

Voor de tweede query komen de familienamen met een 'Z' voor die met een 'A'. Let op: de voornamen met een 'A' komen wel voor de voornamen met een 'Z'!

Merk op uit bovenstaand voorbeeld: de volgorde van de kolommen in de ORDER BY clausule hoeft niet dezelfde te zijn als de volgorde van de weergave in de SELECT clausule. Het kan perfect zijn dat je wil sorteren op familienaam, maar wel eerst de voornaam wil tonen en dan pas de achternaam. Sterker nog: je kan zelfs ordenen op kolommen die je niet toont in de uitvoer.

Functies in SQL staan je toe een waarde te berekenen in plaats van een vaste waarde of de waarde in een of andere kolom.

SUBSTRING

Deze functie gebruik je om een deel van een stuk tekst over te houden. Je kan bijvoorbeeld dit doen om de eerste twee letters van de familienaam van een auteur te tonen:

Je bent ook niet beperkt tot kolomwaarden, je mag ook gewone constanten gebruiken:

Je kan wel net zo goed dit doen:

Als je alleen het begin van een string wil, kan je ook LEFT gebruiken:

CONCAT

Deze functie gebruik je om stukken tekst aan elkaar te hangen. Je kan dus dit doen om de volledige naam van auteurs te tonen:

Je kan dit ook duidelijker laten weergeven in Workbench met:

LENGTH

Hiermee bereken je de lengte van een stuk tekst. Je kan bijvoorbeeld dit doen:

Dit zal je niet de familienaam van elke auteur tonen, maar wel het aantal letters in hun familienaam.

Net zo kan je dit doen:

Dan zal je als resultaat 3 krijgen.

Wiskundige operaties

Ook standaard wiskundige operaties zijn functies. Bijvoorbeeld:

Eerst hebben we een relationele databank vooral gebruikt om individuele records bij te houden en op te vragen. Via aggregaatfuncties hebben we informatie over alle records gebundeld. Soms zoeken we iets dat in het midden ligt: informatie die niet handelt over individuele records, maar over groepen records. Typisch zijn dit records met dezelfde waarde in bepaalde velden. De oplossing schuilt in een GROUP BY clausule.

basisidee

Met de GROUP BY operator kan je rijen "samenpersen" en de gewenste informatie uit de samengeperste rijen halen. Dit "samenpersen" gebeurt eerst, voor de gewenste informatie wordt geselecteerd. Beeld je in dat er een tussenliggende tabel wordt aangemaakt op basis van de tabel waarin je wenst te zoeken.

Veronderstel dat je onderstaande tabel Honden hebt:

Informatie die over groepen records gaat kan dan zijn: "hoeveel mannelijke honden zijn er in het systeem?" of "wat is de gemiddelde leeftijd per geslacht?" Deze vragen kan je niet meteen beantwoorden met de eerdere DML-commando's, maar wel met behulp van GROUP BY. Honden GROUP BY Honden.Geslacht moet je zien als een tijdelijke tabel die er als volgt uitziet:

De kolom na GROUP BY neemt geen nieuwe vorm aan, maar komt in de resultaten nog één keer voor per waarde. Er is dus precies één rij met de waarde "mannelijk" en één rij met de waarde "vrouwelijk". De andere kolommen veranderen eigenlijk van datatype: de kolom voor de naam bevat een sequentie van VARCHAR(50) per rij in plaats van een VARCHAR(50) per rij. De kolom voor de leeftijd bevat een sequentie van TINYINT in plaats een TINYINT per rij, wat hier aangegeven is door de verschillende waarden tussen rechte haakjes te zetten. Er is gekozen voor deze notatie omdat dit lijkt op het gebruik van lijsten in de meeste programmeertalen. De kolom voor het geslacht bevat nog steeds waarden uit een ENUM, omdat GROUP BY nu juist zo werkt dat er precies één waarde is voor elke rij.

Je kan bij gebruik van GROUP BY de data die in het voorbeeld tussen rechte haakjes staat (onder default omstandigheden) niet rechtstreeks tonen. Je kan er wel een aggregatiefunctie op toepassen. Als je wil weten wat de gemiddelde leeftijd per geslacht is, schrijf je dit (0046__SelectHonden.sql):

Als je GROUP BY gebruikt, wordt een aggregatiefunctie dus niet meer over heel de kolom toegepast, maar per groep. Geslacht mag je wel tonen want daar heb je op gegroepeerd, dus die data staan niet tussen rechte haakjes.

Een speciaal geval is COUNT(*). Dit vertelt je hoe veel elementen er gegroepeerd zijn.

De tussenliggende tabel is een hulpmiddel om hierover na te denken; volgende code zou niet werken:

Ze levert: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ApDB.Honden.Leeftijd' which is not functionally dependent on columns in GROUP BY clause". Het probleem is dus dat je data groepeert en dan geen aggregaat toepast op de data die in het voorbeeld hierboven tussen rechte haakjes wordt getoond.

uitbreiding naar meerdere kolommen

GROUP BY hoeft niet gevolgd te worden door één kolom, maar kan door meerdere kolommen gevolgd worden. In dat geval groepeer je records per unieke combinatie van kolomwaarden. Je kan bijvoorbeeld dit doen (0047__SelectHonden.sql):

Dit toont je hoeveel mannelijke en hoeveel vrouwelijke honden er zijn van elke leeftijd die in het systeem voorkomt. Er zijn na uitvoering van het calibratiescript bijvoorbeeld 6 vrouwelijke honden van 1 jaar oud en 4 mannelijke honden van 1 jaar oud. We kunnen ter controle ook dit even doen (0048__SelectHonden.sql):

Dit toont ons dat er 10 (dus 6 vrouwelijke en 4 mannelijke) honden zijn van 1 jaar oud. Anders gezegd: hoe meer kolommen je vermeldt na GROUP BY, hoe meer onderverdelingen je zal zien.

handige aggregaatfunctie: group_concat

Eerder zeiden we: "Honden GROUP BY Honden.geslacht moet je zien als een tijdelijke tabel die er als volgt uitziet:"

Je kan deze tabel niet echt op deze manier tonen, maar je kan ze wel bijna verkrijgen door middel van de group_concat (aggregaat)functie. Deze aggregeert alle waarden in één groep door middel van concatentie (met andere woorden: ze plakt ze allemaal achter elkaar).

Probeer maar eens volgende query: select group_concat(Naam), group_concat(Leeftijd), Geslacht from Honden group by Honden.Geslacht

Met GROUP BY kan je er voor zorgen dat bepaalde waarden worden samengenomen. Op die manier kan je bijvoorbeeld (voor de dataset met honden) elk geslacht één keer tonen. Dat is nuttig, want vaak zijn we niet geïnteresseerd in dubbels. We hebben echter gezien dat na groepering enkel gegroepeerde of geaggregeerde waarden kunnen tonen.

Er is een andere (en meer expliciete) manier om dubbels te vermijden: SELECT laten volgen door het sleutelwoordje DISTINCT. Hierdoor worden alle dubbele resultaten verwijderd. "Dubbel" wordt hier geïnterpreteerd als "gelijk volgens de collation van de kolom in kwestie".

Volgende query geeft je alle hondennamen (0056__SelectHonden.sql) precies één keer:

USE ApDB;
SELECT DISTINCT Naam
FROM Honden;

Na DISTINCT kan je schrijven wat je bij een gewone SELECT zou kunnen schrijven, dus dit gaat ook:

Je kan DISTINCT ook gebruiken binnen sommige aggregatiefuncties. Bijvoorbeeld: COUNT(DISTINCT Naam) om het aantal verschillende namen te weten te komen. Over welke aggregatiefuncties het gaat, vind je terug.

We willen niet alleen rijen kunnen selecteren gebaseerd op één kolom die gelijk is aan een bepaalde waarde. We moeten ook complexere voorwaarden kunnen opstellen. Daarom beschikken over we een hele reeks operatoren die we bovendien kunnen combineren.

wat zijn booleaanse expressies?

Een expressie is een waarde of iets dat je kan uitrekenen om een waarde te krijgen. Een booleaanse expressie is een expressie met een uiteindelijke waarde TRUE, FALSE of (in SQL) NULL.

hoe booleaanse expressies opbouwen?

De bouwstenen zijn:

• TRUE, FALSE en NULL

• haakjes (voor groepering)

• de logische operatoren

Het gebruik van de ronde haakjes dient om de volgorde van de evaluatie van de operatoren te bepalen. Wat tussen haakjes staat, werk je eerst uit als één geheel, vooraleer je de onderdelen buiten deze haakjes uitrekent.

Voor de logische operatoren gebruiken we waarheidstabellen:

Je kan de waarheidstabellen ook nabouwen in Workbench, bv.:

In veel gevallen weten we maar half wat we willen zoeken. We kennen een deel van de naam, het begin of het einde of iets tussenin. Met de LIKE operator kan je in SQL naar patronen zoeken (pattern matching). Je kan met de LIKE operator naar patronen in tekst zoeken. Je combineert de LIKE operator met jokers (wildcards) om een booleaanse expressie te vormen. De set van jokers bestaat uit symbolen die één of meer ontbrekende tekens voorstellen.

De LIKE operator vergelijkt een tekstuitdrukking in het linkerlid, dat eventueel een wildcard bevat, met een patroon tussen aanhalingstekens in het rechterlid. Een patroon is een veralgemeende tekstuitdrukking. d.w.z. een tekst waarin nog enige vrijheid bestaat.

De betekenis van de wildcards in SQL is als volgt:

• %: nul, één of meer willekeurige tekens

• _: exact één willekeurig teken

Om alle boeken te selecteren waarvan de familienaam van de auteur begint met B als de collation niet hoofdlettergevoelig is:

Om alle boeken te selecteren waarvan de familenaam van de auteur eindigt op een s:

Om boeken waarvoor in de titel het woord economie voorkomt, te selecteren:

Aanmaken van je eerste concrete data

Aan alleen structuur heb je niets. We willen ook data bijhouden in ons systeem. Met andere woorden: rijen, niet alleen tabellen.

Er is een vaste syntax om data in je databank te plaatsen, waar je even aan zal moeten wennen. Het is een goed idee om met een stub te beginnen, een skelet statement. Dit zorgt er voor dat je fouten in de syntax snel opmerkt. Voor een tabel Boeken ziet dit er zo uit, voor een enkel nieuw record:

INSERT INTO Boeken (
-- hier moet je gebruikte kolomnamen invullen
)
VALUES (
-- hier moet je waarden voor de gebruikte invullen
);

Meer data in een keer

Je hoeft geen INSERT statement te schrijven per stukje data. Je kan meerdere rijen tegelijk invoegen door ze te scheiden met komma's:

overzicht operatoren

Om waarden te vergelijken, maakt MySQL gebruik van enkele operatoren die erg lijken op wat je mogelijk kent uit andere programmeertalen:

operatoren in combinatie met collations

Voor getallen betekenen bovenstaande operatoren precies wat je zou verwachten. Je moet in het achterhoofd houden dat FLOAT en DOUBLE niet helemaal precies zijn, maar voor de rest betekenen ze wat je verwacht uit de wiskunde.

Voor tekst is het wat complexer. Je kan wel degelijk iets als het volgende schrijven:

Dit SELECT statement retourneert alle rijen uit de tabel Boeken waarvan de Familienaam een waarde heeft die "kleiner" is dan 'B'. Wanneer is een string "kleiner" dan een andere string? Om op die vraag te antwoorden moet je eerst weten dat de namen in een bepaalde, gewoonlijk alfabetische volgorde (in het Engels collation order) worden gerangschikt. "Alle namen kleiner dan 'B'" wil dan zeggen "alle namen die na het sorteren voor 'B' komen."

De collation is een stel regels voor het vergelijken van tekens in een tekenset. De tekenset bepaalt dat A een symbool is dat je kan weergeven en bepaalt welke bytes nodig zijn om dit symbool voor te stellen. De collation legt vast dat "A" voor "B" komt en of "A" voor, na of op dezelfde plaats als "a" komt.

Als de familienaam Beth in je tabel voorkomt, wordt deze niet in de selectie opgenomen. Om ook die rij te selecteren, zou je kunnen denken om de expressie Familienaam <= 'B' te gebruiken:

Dit zal niet werken. Dat komt omdat we de boeken opvragen waarvan de familienaam van de auteur kleiner of gelijk is aan 'B'. De tekst 'Beth' is niet gelijk aan 'B' en is zeker niet kleiner! In het algemeen mag je dit onthouden: als een string A een prefix is van een string B, d.w.z. als string B letterlijk begint met de inhoud van A en dan nog meer tekst bevat, dan zal A altijd voor B gesorteerd worden. Zo wordt 'schoen' gesorteerd voor 'schoenmaker', omdat 'schoen' een prefix is van 'schoenmaker'. Maar er wordt éérst naar de letters gekeken en dan pas naar de lengte van de tekst. Zo wordt 'schoeisel' voor 'schoen' gesorteerd, omdat 'i' voor 'n' komt.

Ter verduidelijking: als we 'B' als familienaam toevoegen, wordt het resultaat wel geselecteerd:

Stel dat je alle titels wilt van de auteurs wilt waarvan de familienaam begint met een' A' of een 'B'. Dan moet je de WHERE clausule verfijnen. Je zou dit kunnen proberen:

Maar hier zijn verschillende problemen mee. Als 'Bz' een prefix is van de naam van een auteur (niet erg waarschijnlijk, maar het zou kunnen), zal dit niet werken. Nog lastiger: dit is erg afhankelijk van de collation. Het is niet in elke collation zo dat 'Bz' voor 'Bé' komt, bijvoorbeeld. Sommige collations sorteren eerst de gebruikelijke 26 letters van het alfabet en pas daarna de letters met accenten.

Volgend script is beter (je mag er in het algemeen wel vanuit gaan dat 'B' voor 'C' komt):

Hoe weet je precies wat er met accenten en hoofdletters gebeurt? Dat hangt ervan af of de gebruikte collation hoofdlettergevoelig (case sensitive) en/of accentgevoelig (accent sensitive) is voor de kolom die je aan het beschouwen bent.

Probeer dit eens uit:

Als de collation voor Familienaam accentongevoelig is, zal je sowieso beide nieuwe records zien. Anders worden de zaken complexer. Dan moet je kijken naar het gebruikte collation algoritme en de accentgevoeligheid. Dan moet je bijvoorbeeld weten of de é pas ergens na de z komt of na de e maar voor de f. Als je de collation van de kolommen van de tabel Boeken te weten wil komen, kan je in MySQL Workbench rechtsklikken op de tabel in kwestie en dan via "Table Inspector" naar "Columns" gaan om de collation te achterhalen. Er zijn véél collations, maar onthoud vooral volgende vuistregels:

• als ze _bin bevat (al dan niet met hoofdletters geschreven), worden de bytevoorstellingen van de karakters vergeleken

  • dit is zowat de strengste definitie van gelijkheid die je kan hebben

• als ze _as

Als je dus bijvoorbeeld enkel een boek van Breton wil, zonder boeken van "Bréton" of "breton", schrijf je:

Het is niet voldoende om rijen te kunnen opvragen. Je moet ook in staat zijn om specifieke informatie op te vragen, statistische gegevens te genereren, enz. Dit kan door uitdrukkingen en extra clausules te gebruiken in SELECT statements.

Vorm

De algemene vorm van een SELECT statement is:

Hierbij heb je al redelijk wat flexibiliteit: je kan gewoon kolomnamen gebruiken als expressies; je kan functies toepassen op kolomnamen; je kan vaste tekst of getallen mee opnemen in de resultaten. Maar er zijn nog meer mogelijkheden. In het algemeen ziet het SELECT statement er zo uit:

Deze volgorde is vast: FROM komt altijd voor WHERE, enzovoort.

Hiermee kan je:

• enkel bepaalde rijen weergeven

• rijen met eenzelfde waarde voor een bepaalde eigenschap groeperen

• statistische eigenschappen van de getoonde data afdwingen

In de loop van deze cursus komen al deze clausules aan bod.

Verwerking

Hoewel bovenstaande volgorde bepaalt hoe je een query moet uitschrijven, is dat niet hoe een query wordt uitgevoerd. De verschillende onderdelen worden verwerkt als volgt:

1. FROM

2. WHERE

3. GROUP BY

Waarschijnlijk ken je al deze clausules nog niet, maar dat is niet erg. Je kan terug kijken naar deze uitleg telkens je een nieuwe clausule leert. Het idee is als volgt:

• eerst geef je aan waar je data vandaag komt

• dan bepaal je, per record, of dat record mee verwerkt zal worden of niet

• de behouden records kan je groeperen om informatie over een bepaald groepje te tonen

Met de WHERE-clausule kon je voorwaarden uitdrukken voor het selecteren (of updaten of wissen) van records. Je kan met WHERE echter geen voorwaarden uitdrukken op kolommen die vermeld zijn in de GROUP BY-clause of op geaggregeerde waarden. Met andere woorden, dit gaat niet:

Ook dit zal niet werken:

Dat komt omdat WHERE rij per rij bekijkt en beslist of die rij behouden of verworpen wordt.

Als je voorwaarden op gegroepeerde data wil uitdrukken, maak je gebruik van HAVING (0050__SelectHonden.sql) en (0051__SelectHonden.sql):

In het kort: voor voorwaarden op kolommen in een GROUP BY of op geaggregeerde waarden gebruik je HAVING, voor andere voorwaarden gebruik je WHERE.

Oefeningen

Voor je deze oefeningen maakt, gebruik je onderstaand script om zeker te zijn dat je databank in de gewenste toestand is.

Sla dit op als 0509__CalibrateDB.sql in je map met scripts.

Deel 1

nummers

Schrijf een script dat volgende nummers toevoegt aan je tabel, Liedjes. Ze worden gegeven in het formaat "titel / artiest / album / jaar":

• Stairway to Heaven / Led Zeppelin / Led Zeppelin IV / 1971

• Good Enough / Molly Tuttle / Rise / 2017

• Outrage for the Execution of Willie McGee / Goodnight, Texas / Conductor / 2018

Sla op als 0510__Oefening.sql.

huisdieren

Doe hetzelfde voor een tabel Huisdieren, met volgende gegevens. Ze worden gegeven in het formaat "baasje / naam / leeftijd / soort":

• Vincent / Misty / 6 / hond

• Christiane / Ming / 8 / hond

• Esther / Bientje / 6 / kat

Sla op als 0511__Oefening.sql.

nummers tonen

Schrijf een script, 0512__Oefening.sql, dat alle info over liedjes toont.

huisdieren tonen

Schrijf een script, 0513__Oefening.sql, dat de naam en soort van elk dier toont, maar niet de leeftijd of het baasje.

sorteren

Schrijf een script, 0514__Oefening.sql, dat (alleen!) de titels van liedjes toont, gaande van het oudste nummer tot het recentste nummer.

gedetailleerd sorteren

Schrijf een script, 0515__Oefening.sql, dat alle info over huisdieren toont, maar de huisdieren alfabetisch ordent volgens naam en, indien de naam dezelfde is, alfabetisch sorteert volgens soort.

omschrijvingen tonen

Schrijf een script, 0516__Oefening.sql, dat per dier een tabel met één kolom, een omschrijving, toont. Deze heeft volgende vorm, inclusief de juiste hoofding:

afronden

Wanneer je klaar bent, voer dan de gewoonlijke commando's uit om je scripts te delen:

Ze hierna je database terug zoals ze was na de theorieles met het script van het einde van vorige labosessie.

Deel 2

gedetailleerd filteren

Schrijf een script, 0517__Oefening.sql, dat alle nummers (alle kolommen) toont die uitgekomen zijn in een jaar dat eindigt op 1. Tip: gebruik WHERE in combinatie met een functie.

informatie toevoegen

Schrijf een script, 0518__Oefening.sql, dat volgende zaken doet:

• een niet-verplichte kolom Geluid toevoegen aan de tabel met huisdieren, die tekst (tot 20 karakters uit het Engels) kan bevatten

  • tip: dit is een DDL-statement, dus kijk terug naar de DDL indien nodig

• het geluid van alle honden aanpast naar

nog informatie toevoegen

Schrijf een script, 0519__Oefening.sql, dat volgende zaken doet:

• de niet-verplichte kolom Genre terug toevoegt aan de tabel met nummers (tot 20 karakters uit het Engels)

• met maximum één UPDATE-operatie alle nummers van Led Zeppelin en Van Halen aanduidt als Hard Rock

wissen

Schrijf een script, 0520__Oefening.sql, dat alle nummers wist (maar de tabelstructuur bewaart).

meer wissen

Schrijf een script, 0521__Oefening.sql, dat alle papegaaien wist.

afronden

Wanneer je klaar bent, voer dan de gewoonlijke commando's uit om je scripts te delen:

Ze hierna je database terug zoals ze was na de theorieles met het script van het einde van vorige labosessie.

Vaak zijn we geïnteresseerd in een specifieke deel data. Bijvoorbeeld alle personen in een bepaalde leeftijdscategorie. We kunnen dit al schrijven als volgt (in dit geval voor grote puppy's in plaats van personen):

USE ApDB;
SELECT *
FROM Honden
WHERE Leeftijd >= 1 AND Leeftijd <= 2;

Een beetje zoals bij de syntax die we hebben ingekort met IN zitten we hier met overbodig schrijfwerk. We kunnen dit oplossen met BETWEEN ... AND ... (0055__SelectHonden.sql):

USE ApDB;
SELECT *
FROM Honden
WHERE Leeftijd BETWEEN 1 AND 2;

Let op: hier is AND op zich geen booleaanse operator, maar BETWEEN ... AND ... is wel een tweedelige booleaanse operator. Met andere woorden, 1 AND 2 hierboven levert je geen booleaanse waarde op, terwijl de meeste voorkomens van AND dat wel doen. Omdat BETWEEN ... AND ... een booleaanse operator is, kan je ook dit doen:

En je kan (zoals overal waar je een string gebruikt) de gebruikte collation wijzigen:

Om de integriteit van de database te bewaren, normaliseren we de gegevens in de database. Door normalisering geraakt de informatie echter verspreid over meer dan één tabel. De gebruiker heeft daar echter niet veel aan. Primaire en vreemde sleutels zeggen de gebruiker niets. Anders gesteld, een tabel vol getallen zoals onderaan op de pagina over primary keys is op zich niet leesbaar. We moeten dus een manier vinden om de informatie weer voor te stellen alsof die uit één tabel komt.

CROSS JOIN

De "domste" manier om data uit meerdere tabellen te combineren tot data die uit één tabel lijkt te komen, is de "cross join". Deze "plakt" elke rij uit tabel A aan elke rij uit tabel B. Veronderstel bijvoorbeeld volgende tabellen voor taken en voor personen:

Deze kunnen we op deze manier combineren en tonen:

Dit levert dan een resultaat dat er als volgt uitziet:

Dit bevat nuttige informatie, maar ook rijen waar we niets aan hebben. De interessante rijen zijn die, die een persoon koppelen aan een taak. Dat zijn de rijen waarin Taken.Id gelijk is aan Taken_Id (afkomstig uit Leden).

Je kan dus personen koppelen aan hun taak via:

Hier moet je Taken.Id schrijven omdat zowel Taken als Leden een kolom Id hebben. Door de tabelnaam toe te voegen, maak je duidelijk over welke kolom het precies gaat.

INNER JOIN

Dit laatste voorbeeld werkt in MySQL, maar het wordt typisch anders geschreven. Meestal zal CROSS JOIN vervangen worden door INNER JOIN, terwijl WHERE vervangen wordt door ON. Wanneer we twee tabellen willen koppelen zodat samenhorende rijen uit tabel A en tabel B één nieuwe rij opleveren, zullen we deze conventie volgen.

Het resultaat zal er dus zo uitzien:

Het resultaat is hetzelfde, maar in dit scenario wordt INNER JOIN verkozen. ON is ook geen synoniem voor WHERE, want het kan alleen gebruikt worden in een JOIN-statement.

Aggregaatfuncties lijken op de functies die je al kent (substring, concat, length, round). Het verschil is dat, als je een kolomnaam invult, de aggregaatfunctie op heel de kolom wordt toegepast. Anders gezegd, aggregaatfuncties worden niet binnen een rij toegepast, maar over meerdere rijen heen. Voor elke rij wordt er een waarde geproduceerd en een aggregaatfunctie gebruikt al deze waarden samen als invoer.

Op deze pagina geven we een overzicht van de belangrijkste aggregatiefuncties, maar er bestaan er nog.

calibratie

Voor deze voorbeelden gebruiken we volgende calibratiestap, die je moet opslaan als 0041__CreateHonden.sql:

aggregaatfunctie: COUNT

COUNT krijgt als invoer een lijst rijen en zegt hoeveel niet-NULL elementen er in deze lijst zitten.

De simpelste toepassing is het tellen van al je records, zoals hier in 0042__SelectHonden.sql:

Dit levert slechts één resultaat, ongeacht hoe veel honden er in de database staan. Dit is anders dan bijvoorbeeld LENGTH(Naam). Die laatste zou één resultaat per hond geven. De reden is dat COUNT een aggregaatfunctie is. Ze verzamelt alle waarden in de gegeven kolom Naam, telt hoe veel dat er zijn en toont dat resultaat. Namen die twee keer voorkomen, worden twee keer geteld. Let op: COUNT telt geen NULL-waarden.

Soms wil je gewoon weten hoe veel rijen er in een tabel zijn. Dan gebruik je COUNT(*). Bijvoorbeeld:

Dit geeft hetzelfde resultaat, want Naam was toch een verplichte kolom.

aggregaatfunctie: SUM

Deze aggregaatfunctie krijgt een expressie en berekent voor de som van het toepassen van deze expressie voor elk record. Een eenvoudig voorbeeld van een expressie is hier (in 0043__SelectHonden.sql) een kolomnaam:

Merk op: hier zal je niet zomaar * in plaats van Leeftijd kunnen invullen. Je kan de som nemen van getallen, maar niet van om het even welk soort data. SUM werkt alleen met getallen.

Je kan wel iets algemener een expressie die een getal oplevert invullen, niet gewoon een kolomnaam. Dus je kan ook dit doen:

aggregaatfuncties: MIN en MAX

Deze aggregaatfuncties krijgen een expressie en berekenen het minimum of het maximum voor het toepassen van deze expressie voor elk record. Een eenvoudig voorbeeld van een expressie is hier (in 0044__SelectHonden.sql) een kolomnaam:

Dit vertelt je wat de leeftijd van de oudste hond in het systeem is.

aggregaatfunctie: AVG

Deze aggregaatfunctie krijgt een expressie en berekent het gemiddelde voor het toepassen van deze expressie voor elk record. Een eenvoudig voorbeeld van een expressie is hier (in 0045__SelectHonden.sql) een kolomnaam:

combineren van aggregaatfuncties en niet-geaggregeerde expressies

Onder normale omstandigheden kan je geen combinatie maken van geaggregeerde resultaten en niet-geaggregeerde resultaten. Iets als het volgende gaat dus niet:

Dit komt omdat je uitvoer nog steeds een tabel is, bestaande uit één aantal rijen en één aantal kolommen. Als je COUNT(*) zou toepassen, zou je één rij overhouden en als je Naam zou opvragen, zou je even veel rijen overhouden als er honden zijn. Dus als deze query zou werken, zou je op basis van de eerste kolom maar één rij mogen hebben en op basis van de tweede kolom zou je er meerdere hebben. Dat gaat niet. Beide kolommen moeten leiden tot hetzelfde aantal rijen.