De grote MySQL tutorial voor dummies

Bij het beheer van MySQL maken wij gebruik van de gratis webtoepassing phpMyAdmin. Deze maakt deel uit van de installatie van de XAMPP-softwarebundel, maar wordt op de officiële projectwebsite ook apart aangeboden als downloadpakket.

phpMyAdmin wordt beschouwd als standaardsoftware voor het beheer van MySQL-databases op het internet. De webtoepassing, die in PHP en JavaScript is geschreven, maakt het mogelijk databasebewerkingen uit te voeren via een grafische gebruikersinterface. Zo kun je de tabellen van je relationele database heel gemakkelijk met een paar muisklikken in je webbrowser aanmaken en beheren. Kennis van bijbehorende SQL-opdrachten is daarvoor vooralsnog niet nodig.

Als de softwarebundel XAMPP is geïnstalleerd, start je het databasemanagementsysteem (MySQL of MariaDB) net als de andere componenten van de testomgeving via het configuratiescherm (Control Panel). Gebruik daarvoor de knop ‘Start’ onder ‘Actions’. Om phpMyAdmin via de webbrowser te kunnen oproepen, moet je bovendien de webserver Apache starten. Geactiveerde modules krijgen in het XAMPP Control Panel een groene achtergrond. Daarnaast wordt de actuele status van de XAMPP-modules als melding in het tekstkader weergegeven.

Feit: XAMPP is in het kader van het softwareproject Apache Friends ontwikkeld als compact testsysteem voor gebruik op een lokale computer. De softwarebundel is niet bedoeld om webdiensten op internet te zetten. Wegens de vele beperkingen op het gebied van veiligheid is XAMPP niet geschikt als productiesysteem.

Voor lokale testdoeleinden is de webinterface van de beheersoftware beschikbaar op http://localhost/phpmyadmin/.

Als je bij de installatie van MySQL een wachtwoord voor het root-account hebt ingesteld, vraagt phpMyAdmin hiernaar via een aanmeldingsscherm. Als je phpMyAdmin voor een webhostingproduct gebruikt, worden de aanmeldingsgegevens via de desbetreffende aanbieder verstrekt. In dat geval heb je meestal geen root-rechten.

Als je succesvol bent aangemeld, verschijnt de startpagina van phpMyAdmin. Hier kun je basisinstellingen voor de tekenset (sortering) van de MySQL-verbinding wijzigen en de gewenste weergavemodus (taal, design, tekengrootte) kiezen. Daarnaast vind je aan de rechterzijde een overzicht van de basisgegevens van je databaseserver, de gebruikte webserversoftware en informatie over de actuele versie van phpMyAdmin. De menubalk van de startpagina wordt, net als alle andere menubalken in de toepassing, weergegeven in de vorm van tabbladen. De startpagina bevat de tabbladen Databases, SQL, Status, User accounts, Export, Import, Settings, Replication, Variables en More.

Aan de linkerkant van de gebruikersinterface vind je een navigatievenster. Hierin staan alle tabellen van je database waar je met phpMyAdmin toegang tot hebt. Onder het programmalogo in de linkerbovenhoek staan links naar de startpagina en naar de officiële documentatie. Daarnaast heb je hier de mogelijkheid het navigatievenster te configureren en de weergave te vernieuwen.

In deze MySQL cursus begin je met het aanmaken van een eerste database.

Om met phpMyAdmin een database aan te maken, klik je eerst op het tabblad ‘Databases’ in de menubalk van de startpagina.

Voer de gewenste naam voor je database in het invoerveld onder ‘Create database’ (database maken) in, en selecteer een tekenset. Wij raden de sortering utf8mb4_unicode_ci aan. Met de keuze van een tekenset deel je de databaseserver mee welke codering moet worden gebruikt voor de te verzenden en te ontvangen gegevens. De mb4-varianten laten ook exotische tekens toe, zoals symbolen of emoji’s die buiten het Unicode-basisniveau (Basic Multilingual Plane) liggen, en zijn daarom aan te raden.

Bevestig de invoer door op ‘Create’ (maken) te klikken. De aangemaakte database wordt in het navigatievenster aan de linkerzijde van het beeldscherm weergegeven. Nieuwe databases hebben in eerste instantie geen inhoud. Om gegevens op te slaan, maak je in de volgende stap een tabel aan.

Om een nieuwe tabel aan te maken, selecteer je de gewenste database en navigeer je via de menubalk naar het tabblad ‘Structure’ (structuur).

Maak een tabel aan door een naam (bijv. users) en het gewenste aantal kolommen in het veld ‘Create table’ (tabel maken) in te voeren. Vergeet niet dat elke kolom een kenmerk van een gegevensset vertegenwoordigt. Als je meer kolommen nodig hebt, kun je deze later ook nog toevoegen.

Als je bijvoorbeeld een database voor gebruikers van je website wilt maken, kun je de volgende titels voor de kolommen van de tabel gebruiken:

Voor je gebruikersdatabase maak je dus een tabel users met zeven kolommen. Je bevestigt de invoer met ‘Go’.

Nadat de tabel is aangemaakt, geeft phpMyAdmin je de mogelijkheid titels voor de kolommen van de tabel in te voeren en opmaakinstellingen voor de te verwachten gegevens uit te voeren.

Een beschrijving van de tabelstructuur en mogelijke opmaakinstellingen vind je in onderstaande tabel.

Dit zijn de belangrijkste instellingen voor de opmaak van tabelkolommen. Als je met behulp van de schuifbalk verder naar rechts scrolt, kom je nog meer instellingsmogelijkheden tegen, maar deze bespreken wij verder niet in de MySQL-tutorial voor dummies.

In onderstaande tabel zijn verschillende gegevenstypes opgesomd die met MySQL en MariaDB kunnen worden verwerkt, evenals hun waardenbereik en het benodigde geheugen.

Voor de voorbeeldtabel users hebben we de volgende instellingen gekozen:

Mogelijke waarden voor de kolom id zijn gedefinieerd als geheel getal (integer, INT) en dragen het kenmerk UNSIGNED. De id kan zodoende alleen positieve getalswaarden aannemen. Onder ‘Index’ hebben we voor de id de instelling PRIMARY geselecteerd. Het identificatienummer fungeert zodoende als primaire sleutel voor de tabel users. Het vinkje bij ‘A_I’ (Auto_Increment) geeft aan het databasemanagementsysteem door dat ID’s voor elke vermelding automatisch doorlopend genummerd moeten worden.

De waarden voor de kolommen firstname, surname, email en password zijn als gegevenstype VARCHAR ingesteld. Dit zijn dus variabele tekenreeksen waarvan wij de lengte (M) met de optie ‘Length/Value’ tot 50 tekens hebben beperkt. Voor de kolom email is bovendien de indexoptie UNIQUE geactiveerd. Zo zorgen we ervoor dat elk e-mailadres slechts één keer in onze tabel wordt opgeslagen.

Voor de kolommen created_at en updated_at hebben we het gegevenstype TIMESTAMP gekozen. Het databasemanagementsysteem slaat tijdsgegevens voor het aanmaken en bijwerken van gegevenssets op in het formaat YYYY-MM-DD HH:MM:DD. Omdat het systeem voor elke nieuwe vermelding automatisch een tijdstempel moet aanmaken, selecteren we voor de kolom created_at de standaardwaarde CURRENT_TIMESTAMP. De kolom updated_at wordt pas relevant als we een vermelding bijwerken. Daarom laten we null-waarden voor deze kolom toe en stellen NULL in als standaardwaarde.

Feit: De waarde NULL staat in PHP voor een lege vermelding. Een veld heeft de waarde NULL als er aan dit veld nog geen waarde is toegewezen.

Als opslag-engine gebruiken we het standaard tabelformat van MySQL, InnoDB.

Alle tabelinstellingen die je via de grafische gebruikersinterface uitvoert, worden door phpMyAdmin omgezet in SQL-code. Indien gewenst kunnen de instellingen alvast worden bekeken door op de knop ‘SQL Preview’ te klikken.

CREATE TABLE test.users 
( id INT UNSIGNED NOT NULL AUTO_INCREMENT , 
firstname VARCHAR(50) NOT NULL , surname VARCHAR(50) NOT NULL , 
email VARCHAR(50) NOT NULL , password VARCHAR(50) NOT NULL , 
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
updated_at TIMESTAMP NULL DEFAULT NULL , 
PRIMARY KEY (id), UNIQUE (email)) ENGINE = InnoDB;

Een gedetailleerde beschrijving van de SQL-syntaxis volgt in het hoofdstuk over databasequery’s.

Je slaat de instellingen op door op ‘Save’ te klikken. De tabel users verschijnt in het navigatievenster onder de database test.

Om een aangemaakte tabel te beheren, klik je op de naam van de tabel in het navigatievenster. phpMyAdmin toont vervolgens in het tabblad ‘Browse’ (bladeren) een overzicht van de gegevens die in de tabel zijn opgeslagen. Dit levert in het geval van onze voorbeeldtabel een leeg resultaat op. Er zijn tenslotte nog geen gegevenssets in de tabel users opgeslagen.

Voor het beheer van gegevenstabellen staan in de menubalk verschillende tabbladen ter beschikking. Als je de structuur van een gegevenstabel wilt veranderen, klik je op het tabblad ‘Structure’. In het tabblad ‘Insert’ (invoegen) kun je nieuwe gegevensarchieven aan je tabel toevoegen. Daarnaast heb je in phpMyAdmin de mogelijkheid tabellen te doorzoeken, autorisaties te beheren en gegevenssets te exporteren of uit andere tabellen te importeren.

Tabelstructuur bewerken

Als je later een extra kolom aan je tabel wilt toevoegen of een bestaande kolom wilt wissen of bewerken, ga je naar het tabblad ‘Structure’.

Voeg kolommen toe via ‘Add column(s)’ door het gewenste aantal nieuwe kolommen en de gewenste positie aan te geven.

Wil je bestaande kolommen wissen, selecteer dan de gewenste kolommen door een vinkje in het selectievakje te plaatsen en klik vervolgens op ‘Drop’.

Je kunt een kolom bewerken via de knop ‘Change’. Hiermee kom je in een bewerkingsweergave terecht, die je zult herkennen van het aanmaken van de tabel.

Wijzigingen in de tabelstructuur kunnen eventueel verlies van gegevens tot gevolg hebben. Daarom is het verstandig een back-up van je database te maken voordat je aangemaakte tabelkolommen bewerkt of wist. Ga daarvoor naar het tabblad ‘Export’ (exporteren), selecteer de gewenste bestandsindeling voor de back-up en bevestig de bewerking met ‘Go’. Er verschijnt een dialoogvenster waarin je webbrowser vraagt waar je de download wilt opslaan. Een alternatief om back-ups van databases via phpMyAdmin te maken, is het gratis back-upprogramma MySQLDumper.

Tabelvermeldingen toevoegen

Om gegevens in de tabel in te vullen via phpMyAdmin, heb je twee mogelijkheden: je kunt gegevenssets uit een extern bestand importeren (bijvoorbeeld uit een back-up) of je maakt tabelvermeldingen handmatig aan. Als je voor de laatste optie kiest, selecteer je de voorbeeldtabel users en navigeer je naar het tabblad ‘Insert’ (invoegen).

phpMyAdmin toont dan een invoervenster.

Onder ‘Column’ zijn de kolommen opgesomd die we voor de tabel users hebben aangemaakt. Onder ‘Type’ staat aangegeven welk gegevenstype voor de desbetreffende kolom wordt verwacht evenals een eventuele beperking van het aantal tekens tussen haakjes. De kolom ‘Function’ laten we buiten beschouwing en we gaan direct naar de kolom ‘Value’. Hier voeren we de waarden in voor de afzonderlijke kolommen van onze voorbeeldtabel.

In het vorige hoofdstuk hebben we de tabel users zodanig geconfigureerd dat de gegevensvelden voor de kolommen id, created_at en updated_at automatisch worden gevuld door het databasemanagementsysteem. In de kolom id wordt dankzij AUTO_INCREMENT voor elke nieuwe vermelding een doorlopend nummer ingevoegd. In het veld created_at wordt automatisch de actuele tijdstempel ingevuld. En voor updated_at vult het systeem voorlopig de standaardwaarde NULL in. We moeten dus alleen nog de gegevens voor de kolommen forename, surname en password handmatig invoeren. We laten dit zien aan de hand van fictieve gebruikersgegevens:

• firstname: John
• surname: Doe
• email: john@doe.com
• password: qwertz

Door op ‘Go’ te klikken, sla je de gegevens op in de tabel. phpMyAdmin springt automatisch naar het tabblad ‘SQL’ en geeft de uitgevoerde databasebewerking weer als statement in de SQL-syntaxis:

INSERT INTO users (id, firstname, surname, email, password, 
		
created_at, updated_at) VALUES (NULL, 'John', 'Doe', 'john@doe.com',
		
'qwerty', CURRENT_TIMESTAMP, NULL);

In principe kunnen alle databasebewerkingen die je in phpMyAdmin gemakkelijk via een grafische gebruikersinterface uitvoert, ook in de databasetaal SQL worden geschreven. Bij webontwikkeling is dat de standaardprocedure.

Zogenaamde SQL-query’s (informatieverzoeken) komen voor in de broncode van alle dynamische webtoepassingen en maken de interactie tussen webserver en database mogelijk. De databasetaal SQL is gebaseerd op commando’s, bijvoorbeeld om gegevens op te roepen en deze te gebruiken voor het uitvoeren van een programma. De belangrijkste SQL-commando’s SELECT, INSERT, DELETE en UPDATE alsook de syntaxis van deze databasebewerkingen worden toegelicht in de volgende hoofdstukken van onze MySQL-tutorial.

Eerst voeren we in de tabel users echter meer gebruikersgegevens in en bekijken we het tabeloverzicht in het tabblad ‘Browse’.

Door op een andere kolomtitel te klikken, kun je de tabel in de gewenste volgorde sorteren.

Nadat we de voorbeeldtabel users hebben ingevuld met vermeldingen, leren we in de volgende hoofdstukken hoe de opgeslagen gegevens kunnen worden opgevraagd via PHP op de Apache-webserver.

Daarvoor brengen we eerst een databaseverbinding tot stand. Hiervoor biedt PHP drie interfaces: MySQL Extension, MySQL Improved Extension (MySQLi) en PHP Data Objects (PDO).

• MySQL Extension: MySQL Extension is een MySQL-interface die vroeger erg populair was, maar nu nogal verouderd is. Ten opzichte van MySQLi en PDO heeft de oude MySQL Extension het nadeel dat deze interface noch prepared statements, noch benoemde parameters ondersteunt.
• MySQLi: MySQLi is een verbeterde versie van de klassieke PHP-extensie voor de toegang tot MySQL-databases. Deze interface werkt zowel procedureel als objectgeoriënteerd. De extensie kan echter alleen voor MySQL- en MariaDB-databases worden gebruikt.
• PDO: PHP Data Objects (PDO) is een objectgeoriënteerde interface die een ‘abstraction layer’ voor de gegevenstoegang ter beschikking stelt. Zodoende kunnen via PDO niet alleen MySQL-databases, maar ook andere databasesystemen zoals PostgreSQL, Oracle, MSSQL of SQLite in PHP worden geïntegreerd.

In het volgende deel van deze tutorial voor MySQL beperken we ons tot databaseverbindingen via PDO.

Om via een PHP-script gegevens uit een database te kunnen opvragen, moet de database eerst worden geverifieerd. Een databaseverbinding via PDO wordt met behulp van de volgende coderegel tot stand gebracht:

?php
		$pdo = new PDO('DSN', 'username', 'password');
		?;

Het is raadzaam deze regel in te voegen aan het begin van elk script dat databasebewerkingen bevat.

We gebruiken het PHP-sleutelwoord new om een entiteit van de PDO-basisklasse aan te maken. Hiervoor zijn in principe drie parameters vereist: de Data Source Name (DSN), een gebruikersnaam en het wachtwoord voor de database, mits van toepassing. De DSN bestaat in ons geval uit de volgende parameters:

• PDO-databasestuurprogramma: mysql
• Databaseserver (host=): localhost
• Databasenaam (dbname=): test
• Tekenset (charset=): utf8

Als je voor de database geen toegangsgegevens hebt ingesteld, gebruik je de gebruikersnaam root en een leeg wachtwoord:

?php
		$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
		?;

De databaseverbinding wordt in de variabele $pdo opgeslagen. Hiermee kun je later in de programmacode terugverwijzen naar de databaseverbinding.

Als er een verbinding met de database tot stand is gebracht, kun je in de volgende scriptcode een willekeurig aantal query’s naar de database verzenden. Als je het script beëindigt, wordt ook de databaseverbinding verbroken.

Het commando SELECT gebruik je om geselecteerde gegevensrijen (rows) uit een willekeurig aantal tabellen op te roepen. Als je bijvoorbeeld de voor- en achternamen en e-mailadressen van alle gebruikers uit de zojuist aangemaakte voorbeeldtabel in de webbrowser wilt weergeven, maak je in de map htdocs van je XAMPP-omgeving een nieuw PHP-bestand text.php aan en voeg je het volgende script in:

?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT firstname, surname, email FROM users";
foreach ($pdo->query($sql) as $row) {
   echo $row['firstname'] . " " . $row['surname'] ;
   echo "E-Mail: " . $row['email'] ;
}
?

Je leest de voorbeeldcode als volgt: we beginnen het script met de PHP-begincode 

De regels 4 t/m 7 tonen een foreach-lus. Deze biedt ons de mogelijkheid een willekeurige matrix te herhalen, dat wil zeggen een gegevensstructuur stap voor stap te doorlopen. Welke matrix moet worden herhaald en hoe de opgevraagde gegevens moeten worden opgeslagen, geven we tussen haakjes aan achter de foreach-constructie

$pdo->query($sql) as $row

De variabele $pdo spreekt de gewenste database aan via de in regel 2 gespecificeerde verbinding. Met de functie query() sturen we het in de variabele $sql opgeslagen SQL-statement hiernaartoe.

De webserver roept zodoende alle kolommen firstname, surname en email van de tabel users op uit de database test en doorloopt dankzij de foreach-lus elke afzonderlijke tabelrij. Waar de opgevraagde gegevens moeten worden opgeslagen, geeft het PHP-sleutelwoord as aan in de matrixvariabele $row.

In de eerste ronde van de foreach-lus ziet deze matrix er als volgt uit:

$row = array (
    firstname => John,
    surname => Doe,
    email => john@doe.com
)

In dit voorbeeld is de tekstuitvoer via echo in elke ronde onderdeel van de foreach-lus. Zo doorlopen we elke rij van de tabel users afzonderlijk, lezen de opgeslagen gegevens uit de kolommen die in de SQL-statement zijn gespecificeerd uit, en geven deze weer in de webbrowser.

Als alle kolommen van een gegevenstabel moeten worden uitgelezen, gebruik je in de SQL-statement een asterisk (*) als jokerteken (ook wel wildcard genoemd).

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT * FROM users";
foreach ($pdo->query($sql) as $row) {
   echo $row['firstname'] . " " . $row['surname'] ;
   echo "E-Mail: " . $row['email'] ;
   echo "Created at: " . $row['created_at'] ;
}
?>

Daarmee kunnen we alle gegevens die in de tabel ‘users’ zijn opgeslagen in het script gebruiken. In het volgende screenshot is de tekstuitvoer aangevuld met een tijdstempel die aangeeft wanneer de vermelding is aangemaakt.

In beide voorgaande voorbeelden geeft de webserver de gebruikersgegevens weer in de volgorde waarin wij ze in de tabel users hebben ingevoerd (gesorteerd op ID-nummer). Als je de gegevens gesorteerd wilt weergeven, bepaal je de volgorde met behulp van het SQL-sleutelwoord ORDER BY. In het volgende voorbeeld worden de gegevens gesorteerd en op de alfabetische volgorde van de voornaam weergegeven:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "SELECT firstname, surname, email FROM users ORDER BY firstname";
foreach ($pdo->query($sql) as $row) {
   echo $row['firstname'] . " " . $row['surname'] ;
   echo "E-Mail: " . $row['email'] ;
}
?>

Het aanmaken van databasevermeldingen wordt in phpMyAdmin zelden handmatig uitgevoerd. Meestal worden de gegevens via een script door de webserver in de database geschreven, bijvoorbeeld als een internetgebruiker een online formulier op een website invult of een commentaar in een webshop achterlaat. In beide gevallen wordt op de achtergrond het SQL-commando INSERT gebruikt. Een SQL-statement met het commando INSERT wordt volgens het volgende schema opgesteld:

INSERT INTO tabel (kolom1, kolom2, kolom3) VALUES (waarde1, waarde2, waarde3);

Dit lees je als volgt: roep de genoemde tabel op en voeg de waarden 1, 2 en 3 in de kolommen 1, 2 en 3 in.

Een eenvoudig PHP-script om een nieuwe gegevensset aan onze voorbeeldtabel users toe te voegen, kan er zo uitzien:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "INSERT INTO users (firstname, surname, email, password) 
VALUES ('Paddy', 'Irish', 'paddy@irish.com', 'qaywsx')";
if ($pdo->exec($sql) === 1)
  echo "New record created successfully";
?>

Eerst maken we verbinding met de database en slaan we deze op in de variabele $pdo. Daarna specificeren we het SQL-statement en slaan we dit op in de variabele $sql. In regel 5 gebruiken we de pijloperator (->) om ons toegang te verschaffen tot de variabele $pdo en met behulp van de functie exec() het in $sql opgeslagen SQL-statement uit te voeren.

Om er zeker van te zijn dat ons script slechts één gegevensset aan de tabel users toevoegt, controleren we het aantal rijen met behulp van een if-voorwaarde. Deze zorgt ervoor dat de tekst New record created successfully alleen door de webbrowser kan worden weergegeven als het aantal ingevoegde gegevenssets 1 bedraagt. Als het script opnieuw wordt uitgevoerd, wordt de melding niet weergegeven. Dubbele vermeldingen worden voorkomen doordat de waarde email als UNIQUE is ingesteld.

Als we nu het overzicht van onze voorbeeldtabel users in de database test oproepen, zien we dat de tabel is aangevuld met gegevensset 5. Het doorlopende identificatienummer en het tijdstempel zijn, zoals de bedoeling is, automatisch toegevoegd.

Als je een bestaande gegevensset wilt bijwerken, gebruik je het SQL-commando UPDATE volgens het volgende schema:

UPDATE tabel SET kolom1 = waarde1, kolom2 = waarde2 WHERE kolom3 = waarde3

Dit SQL-statement betekent: selecteer de aangegeven tabel en vervang de waarde in kolom 1 door waarde 1 en de waarde in kolom 2 door waarde 2 indien kolom 3 de waarde 3 bevat. Let op: als je de voorwaarde vergeet, overschrijft MySQL de betreffende velden in alle gegevenssets.

We hebben hier dus met een SQL-statement te maken dat een databasebewerking aan een voorwaarde koppelt. Als we dit commando op onze voorbeeldtabel willen toepassen, kan bijvoorbeeld het e-mailadres van de gebruiker John Doe worden geactualiseerd door het volgende PHP-script uit te voeren:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users 
SET email = 'john.doe@example.com', updated_at = NOW() WHERE id = 1";
if ($pdo->exec($sql) === 1)
  echo "Update successful";
?>

In het SQL-statement bepalen we dat de huidige waarde in de kolom email moet worden vervangen door de nieuwe waarde john.doe@example.com indien de waarde in de kolom id 1 bedraagt. Zo wijzigen we alleen de gegevensset met de primaire sleutel 1. Daarnaast werken we in hetzelfde SQL-statement de waarde voor de kolom updated_at bij met behulp van de MySQL-functie NOW(), die de actuele tijdstempel invoegt. Het SQL-statement wordt, net als het vorige statement, uitgevoerd met de coderegel $pdo->exec($sql) en een if-voorwaarde.

Als de update succesvol is, moet phpMyAdmin in het tabblad ‘Browse’ de bijgewerkte tabel weergeven.

In het voorbeeld hebben we een e-mailadres gewijzigd en de standaardwaarde NULL in de kolom updated_at vervangen door een tijdstempel. Het commando UPDATE maakt het bovendien mogelijk waarden uit een kolom naar een andere kolom te kopiëren. Deze bewerking kan bijvoorbeeld worden gebruikt als we de voorbeeldtabel users willen uitbreiden met de kolom email_registration. Op die manier kunnen we onderscheid maken tussen twee e-mailadressen: een die bij de registratie is gebruikt en een actueel contactadres dat in de loop van de tijd kan veranderen. In eerste instantie zijn beide adressen echter hetzelfde, zodat we de waarden van het ene veld in het andere kunnen kopiëren. Daarvoor maken we met phpMyAdmin eerst een nieuwe kolom email_registration aan via ‘Add column(s)’ in het tabblad ‘Structure’.

Om de waarden te kopiëren, gebruiken we het volgende UPDATE-statement:

UPDATE users SET email_registration = email

Omdat we alle gegevenssets willen bijwerken, formuleren we geen voorwaarde voor de update.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "UPDATE users SET email_registration = email";
$affected = $pdo->exec($sql);
echo "$affected rows updated";
?>

Als het script via de webserver wordt uitgevoerd, kopieert het databasemanagementsysteem de waarden van de kolom email voor alle gegevenssets naar de kolom email_registration.

Via de SQL-commando DELETE kunnen databasevermeldingen worden gewist. Dit kan worden uitgevoerd door het volgende schema te gebruiken:

DELETE FROM tabel WHERE kolom = waarde

Als je in een database met ID’s werkt, is het voor de hand liggend deze te gebruiken om de vermeldingen te identificeren die je wilt wissen. Als je bijvoorbeeld de 5e vermelding uit onze voorbeeldtabel wilt wissen, ga je als volgt te werk:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '');
$sql = "DELETE FROM users WHERE id = 5";
$affected = $pdo->exec($sql);
echo "$affected rows updated";
?>

Het SQL-commando DELETE wist altijd een hele database-rij. Als je alleen waarden in bepaalde kolommen van een gegevensset wilt wissen, kun je dit doen met een UPDATE-statement. Met UPDATE tabel SET kolom = NULL WHERE … kun je aan een kolom de waarde NULL toewijzen – uiteraard op voorwaarde dat voor de desbetreffende kolom de waarde NULL is toegestaan.

Zoals in het hoofdstuk over relationele databases al aangegeven, kunnen we gegevens uit meerdere tabellen tegelijk opvragen. Nu je bekend bent met de basisbewerkingen van databases, laten we zien hoe je verschillende tabellen van je database kunt verbinden tot een join (‘verbinding’ of ‘samenvoeging’).

Je kunt tabellen samenvoegen in het kader van een query met behulp van het SQL-commando JOIN. Daarbij worden twee of meer genormaliseerde tabellen via gemeenschappelijke kolommen verbonden. Dit is mogelijk via een refererende sleutel.

We laten zien hoe je databasetabellen verbindt aan de hand van het volgende voorbeeld:

Het valt meteen op dat deze tabel veel redundante gegevensvelden bevat. Deze kunnen we verhelpen door de gegevens te verdelen in gescheiden tabellen en deze tabellen met behulp van refererende sleutels te koppelen.

Een goed databaseontwerp kenmerkt zich door zo min mogelijk redundantie. Dubbele vermeldingen kunnen worden vermeden door gegevenstabellen te normaliseren. Op het gebied van het relationele databasemodel hebben drie op elkaar gebaseerde normaalvormen vaste voet gekregen, die regels vastleggen voor de optimale structurering van gegevens.

1e normaalvorm

Een tabel voldoet aan de 1e normaalvorm als alle kenmerkwaarden atomisch zijn. Kenmerkwaarden zijn atomisch als ze slechts één gegeven bevatten. Dit kunnen we duidelijk maken aan de hand van een foutief voorbeeld.

Kijk maar eens naar de kolommen album_title en interpret in de tabel album. In plaats van alle gegevens in de tabel in aparte kolommen te vermelden, hebben we ervoor gekozen informatie over het verschijningsjaar van een album en over het bestaan van een band gewoon tussen haakjes achter de albumtitel of de band te schrijven. Vroeg of laat krijgen we hier spijt van, bijvoorbeeld wanneer we alle titels willen opvragen die in een bepaald jaar zijn verschenen.

Daarom is het raadzaam gegevenstabellen altijd volgens de regels van de 1e normaalvorm op te stellen. Volgens deze regels ziet onze voorbeeldtabel er dan als volgt uit:

Alle gegevens zijn nu gescheiden en dus zonder problemen op te vragen. Onze tabel bevat echter nog steeds redundanties. In de volgende stappen laten we zien hoe we deze verhelpen.

2e normaalvorm

Een tabel voldoet aan de 2e normaalvorm als alle voorwaarden van de 1e normaalvorm zijn vervuld én elk niet-sleutelkenmerk functioneel afhankelijk is van de primaire sleutel van de tabel.

Vaak hebben gegevenstabellen slechts één kolom die als primaire sleutel dient. Zulke tabellen voldoen automatisch aan de 2e normaalvorm, mits de voorwaarden van de 1e normaalvorm zijn vervuld. Het komt echter ook voor dat de primaire sleutel van een tabel uit twee kolommen bestaat. Dit is bij onze voorbeeldtabel het geval.

Om een bepaalde titel uit de kolom title van de tabel album op te roepen, hebben we ten eerste de album_id nodig en ten tweede het titelnummer uit de kolom track. De titel Sympathy for the Devil kan bijvoorbeeld worden opgeroepen met de primaire sleutel album_ID = 3 AND track = 1. We hebben hier dus te maken met een samengestelde primaire sleutel. Deze is echter alleen nodig bij query’s met betrekking tot de kolom title. De kolommen album_title, released, interpret en years_active zijn alleen afhankelijk van de album_id. Deze kolommen zijn dus niet volledig functioneel afhankelijk van de complete primaire sleutel. De voorwaarden van de 2e normaalvorm zijn hierbij niet vervuld.

Dit kunnen we echter aanpassen door de kolom title in een nieuwe tabel op te slaan en via een refererende sleutel (album_id) aan de eerste tabel te koppelen.

Dit kunnen we echter aanpassen door de kolom title in een nieuwe tabel op te slaan en via een refererende sleutel (album_id) aan de eerste tabel te koppelen.

De aangepaste tabel album bevat nu een eendelige primaire sleutel en voldoet dus aan de voorwaarden van de 2e normaalvorm. De nieuwe tabel title bevat uitsluitend de niet-sleutelkolom title. Deze is volledig functioneel afhankelijk van de primaire sleutel (album_id en track) en voldoet dus eveneens aan de 2e normaalvorm.

Maar ook in de 2e normaalvorm heeft onze gegevenstabel album nog redundante vermeldingen.

Om aan de 3e normaalvorm te voldoen, moeten alle voorwaarden van de 2e normaalvorm (en dus ook van de 1e normaalvorm) zijn vervuld. Daarnaast geldt dat geen enkel niet-sleutelkenmerk transitief afhankelijk mag zijn van een sleutelkenmerk. Deze voorwaarde klinkt ingewikkeld, maar is eenvoudig uit te leggen: een transitieve afhankelijkheid bestaat wanneer een niet-sleutelkenmerk afhankelijk is van een ander niet-sleutelkenmerk.

Dit is het geval bij onze voorbeeldtabel album. Deze bevat de kolommen interpret en years_active. Terwijl de band kan worden bepaald aan de hand van de album_id, is de bestaansperiode van de band wederom afhankelijk van de band en dus transitief afhankelijk van de album_id. Dit heeft als nadeel dat het databasemanagementsysteem, telkens als er een nieuw album van een reeds ingevoerde band wordt ingevoerd, automatisch een redundante waarde in de kolom years_active opslaat.

Om aan de voorwaarden van de 3e normaalvorm te voldoen en dus alle redundanties uit onze tabel te verwijderen, moeten we ook de kolom interpret, inclusief years_active, in een aparte tabel opslaan en via een refererende sleutel interpret_id aan de tabel album koppelen.

We hebben dan drie genormaliseerde tabellen: interpret, album en title.

Als we nu een bepaalde titel, inclusief informatie over het album en de band, willen opvragen in de database, moeten we de drie aparte tabellen verbinden met behulp van het SQL-commando JOIN en de desbetreffende refererende sleutels.

Als je als database-engine InnoDB hebt gekozen, kun je de relaties van refererende sleutels gemakkelijk vastleggen via de grafische gebruikersinterface van de beheersoftware phpMyAdmin. Daarbij kan de primaire sleutel van een tabel in een willekeurig aantal andere tabellen als refererende sleutel worden gebruikt.

In ons voorbeeld hebben we twee verbindingen nodig om de drie genormaliseerde tabellen album, interpret, en title te koppelen:

• Voor de eerste verbinding gebruiken we de primaire sleutel album_id van de tabel album als refererende sleutel in de tabel title.
• Voor de tweede verbinding gebruiken we de primaire sleutel interpret_id van de tabel interpret als refererende sleutel in de tabel album.

Bij de koppeling van de tabellen geldt dat een kolom die als refererende sleutel moet fungeren, de kenmerken UNIQUE of INDEX moet hebben.

De relatie tussen primaire sleutels en refererende sleutels komt normaal gesproken overeen met het relatietype 1:n. Elk gegevensveld in de kolom van de primaire sleutel van tabel A staat in relatie met een willekeurig aantal (n) gegevensvelden in de kolom van de refererende sleutel van tabel B. Maar elk gegevensveld in de kolom van de refererende sleutel van tabel B heeft altijd betrekking tot slechts één gegevensveld in de kolom van de primaire sleutel van tabel A. Zo hebben we in de kolom van de primaire sleutel album_id van de tabel album vier vermeldingen die via de refererende sleutel title.album_id gekoppeld zijn aan acht vermeldingen van de tabel title.

Om de gewenste koppelingen te genereren, maken we de tabellen album, interpret en title aan in phpMyAdmin en leggen we de primaire sleutels bij het aanmaken van de tabellen vast met de optie ‘Index’. Let erop dat kolommen die later als refererende sleutel moeten dienen, eveneens via de optie ‘Index’ gemarkeerd zijn als INDEX of UNIQUE. Voor 1:n-relaties is eigenlijk alleen INDEX geschikt, omdat de waarden in een UNIQUE-veld niet vaker mogen voorkomen.

In de volgende stap bepalen we de refererende sleutels. Dit laten we zien aan de hand van de tabel album. We selecteren de tabel in het navigatievenster en gaan naar het tabblad Structure in de menubalk. Hier vind je de knop ‘Relation view’ (relatieweergave).

Relaties van refererende sleutels bepaal je in de relatieweergave van een gegevenstabel via het invoerveld ‘Foreign key constraints’.

In de tabel album moet de kolom interpret_id fungeren als refererende sleutel die is gebaseerd op de primaire sleutel interpret_id van de tabel interpret.

We selecteren in de vervolgkeuzelijst onder ‘Column’ de kolom interpret_id als refererende sleutel. Let op dat hier alleen kolommen worden weergegeven die als INDEX, UNIQUE of PRIMARY zijn gemarkeerd. In het driedelige invoerveld ‘Foreign key constraint (InnoDB)’ bepalen we op welke primaire sleutel, van welke tabel, in welke database onze refererende sleutel gebaseerd moet zijn. We selecteren de volgende opties:

Database: test

Tabel: interpret

Primaire sleutel: interpret_id

Het veld ‘Constraint name’ kan leeg blijven. Het databasemanagementsysteem vult hier automatisch een naam in. We moeten echter wel bepalen hoe een tabel met refererende sleutel zich gedraagt zodra de primaire sleutel die aan de refererende sleutel ten grondslag ligt, wordt gewijzigd of gewist.

Als bijvoorbeeld een band uit de bovenliggende tabel interpret wordt gewist, verdwijnt ook de primaire sleutel die met deze vermelding verbonden is. Daarom moet worden bepaald wat er gebeurt met vermeldingen die via refererende sleutels betrekking hebben op deze vermelding – in ons voorbeeld dus albums van een band.

Om het gedrag van een tabel met refererende sleutel in het geval van een UPDATE of DELETE te bepalen, heb je in MySQL en MariaDB vier opties.

• RESTRICT - De optie RESTRICT sluit een wijziging van de bovenliggende tabel uit indien er andere tabellen bestaan die naar deze tabel verwijzen. In ons geval zou dat betekenen dat een gegevensset in de tabel interpret niet kan worden gewist, als hieraan gegevenssets in de tabel album zijn gekoppeld.
• CASCADE - De optie CASCADE zorgt ervoor dat een wijziging in de bovenliggende tabel wordt doorgegeven aan alle tabellen die betrekking hebben op deze bovenliggende tabel. Als we bijvoorbeeld de interpret_id van de band The Rolling Stones van 2 in 8 veranderen, wordt deze wijziging bij de optie CASCADE ook toegepast op alle tabellen die de interpret_id als refererende sleutel gebruiken. Als een vermelding in de bovenliggende tabel wordt gewist, heeft dit tot gevolg dat ook alle gegevenssets die met deze vermelding verbonden zijn in andere tabellen worden gewist. Let op: op deze manier kan het wissen van een enkele vermelding het verdwijnen van talrijke gegevens tot gevolg hebben.
• SET NULL - Als je de optie SET NULL kiest, wordt de waarde van de kolom van de refererende sleutel op NULL gezet, zodra de primaire sleutel in de bovenliggende tabel wordt gewijzigd of gewist.
• NO ACTION - In MySQL is de optie NO ACTION een equivalent van de optie RESTRICT.

Als je de gewenste optie voor de relatie van de refererende sleutel hebt geselecteerd, bevestig je de invoer door op ‘Save’ te klikken. Het databasemanagementsysteem geeft de nieuwe relatie automatisch een naam.