Insert, update en delete

Naast de select query heb je ook query's waarmee je informatie aan de database kan toevoegen of wijzigen.

Een database is opgebouwd uit een of meedere tabellen. Deze tabellen bevatten op hun beurt een of meedere kolommen waarin verschillende soorten data opgeslagen kan worden. De werkelijke data bevindt zich in rijen (records) waarvan elk apart deel van de data zich in een aparte kolom bevindt.

Een plaatje zegt meer dan woorden, dus laten we de tabel waar we straks mee gaan werken eens visualiseren:

+----+----------+---------------+------------+---------------+----------------+
| id | voornaam | tussenvoegsel | achternaam | geboortedatum | salaris_schaal |
+----+----------+---------------+------------+---------------+----------------+
|  1 | Nico     | de            | Boer       | 1958-03-24    |             18 |
|  2 | Tim      | NULL          | Janssen    | 1982-01-30    |             10 |
|  3 | Pim      | NULL          | Vosse      | 1980-12-20    |             10 |
+----+----------+---------------+------------+---------------+----------------+

Dit is de tabel werknemers waar we in deze tutorial veel mee zullen werken. Ik zal straks uitleggen hoe we deze tabel aan kunnen maken, maar laten we eerst eens naar de opbouw kijken.

Zoals ik al zei is de naam van deze tabel werknemers. We zien dat de tabel bestaat uit 6 kolommen met respectievelijke de namen id, voornaam, tussenvoegsel, achternaam, geboortedatum, salaris_schaal. De namen van de kolommen geven al aan wat voor soort data erin opgeslagen wordt.

Tenslotte zien we dat deze tabel al drie records met data bevat. Straks zullen we terugkomen op de inhoud van deze records, maar eerst zal ik uitleggen hoe we deze tabel aan kunnen maken.

Aanmaken van de tabel: de query

Om de database iets te laten doen, geven we hem opdrachten geschreven in SQL, zogenaamde queries. Elk type query heeft een bepaalde syntax die voor elk type anders is. De syntax van elk soort query kun je terugvinden in de MySQL handleiding. Zo ook de syntax voor het aanmaken van een nieuwe tabel.

Om de tabel uit bovenstaand voorbeeld te creëren gebruiken we de volgende SQL query:

CREATE TABLE werknemers (
    id int(8) NOT NULL auto_increment,
    voornaam varchar(50) NOT NULL,
    tussenvoegsel varchar(4) default NULL,
    achternaam varchar(75) NOT NULL,
    geboortedatum date NOT NULL,
    salaris_schaal int(2) default '10',
    PRIMARY KEY  (id)
) ENGINE=InnoDB;

We zien dat de query begint met CREATE TABLE dat aangeeft dat we een nieuwe tabel willen maken. Dit statement wordt gevolgd door de naam van de tabel en daarna volgen tussen haakjes de kolommen van de tabel.

De verschillende kolommen worden gescheiden door een komma en in bovenstaand voorbeeld stelt elke regel dus een aparte kolom voor. Elke regel begint met de naam van de kolom gevolgd door het datatype en de lengte (over de verschillende datatypen komen we zodadelijk te spreken). Vervolgens volgt eventueel de optie NOT NULL dat betekent dat de kolom altijd een waarde moet hebben. Als dit niet het geval is zal er altijd een default waarde opgegeven moeten worden welke overigens ook NULL kan zijn.

Andere opties die nog op deze regel voor kunnen komen zijn bijvoorbeeld het voor MySQL specifieke auto_increment. Deze optie wordt op een kolom van het type INT aangebracht en zorgt ervoor dat bij elk nieuw record dat ingevoegd wordt, deze kolom een unieke opeenvolgende waarde krijgt.

Na de kolommen volgen nog enkele regels waarin we verschillende sleutels voor de tabel aan kunnen maken. De verschillende soorten sleutels en hun functie komen later in deze tutorial aan bod. De allerlaatste regel van deze query geeft de engine aan die we gebruiken. Ook dit is weer specifiek voor MySQL en wij zullen altijd de InnoDB engine gebruiken. Waarom we dat doen zal later blijken in het gedeelte over het relationele aspect van de database.

Omdat het aanmaken van een tabel vrij specifiek is voor elke database, is deze query ook alleen te gebruiken in een MySQL omgeving. Dit komt onder andere door de auto_increment en de engine die we definiëren.

Aanmaken van de tabel: het uitvoeren

Het aanmaken van tabellen is een proces dat je voor een applicatie normaal gesproken maar één keer uitvoert. De CREATE TABLE query is dan ook een query die je niet regelmatig zult gebruiken, voornamelijk ook omdat je er waarschijnlijk voor kiest om tabellen aan te maken met een interface zoals phpMyAdmin.

Om nu deze tabel aan te maken, zou je bovenstaande query in de SQL tab van phpMyAdmin kunnen uitvoeren. Zorg wel dat je eerst zelf al een database hebt aangemaakt waar je de tabel in kwijt kunt. Als je niet weet hoe dat moet, raadpleeg dan je host of kijk eens goed rond in phpMyAdmin indien je een lokale server (WAMP bijvoorbeeld) gebruikt.

Naamgeving

Net zoals bij variabelen in PHP is het ook hier belangrijk dat je duidelijk namen voor je tabellen en kolommen kiest. Probeer in de namen enkele letters en eventueel underscores te gebruiken, dan weet je al bijna zeker dat een naam ook geldig is. De laatste vereiste is dat de naam niet voorkomt in de lijst met gereserveerde woorden.

Helaas is het grote nadeel van MySQL dat deze ook namen toestaat die eigenlijk helemaal niet mogelijk zijn. Zo is een naam als z-index helemaal geen probleem in deze database. Dit komt doordat MySQL in zijn queries standaard backticks (`) gebruikt. Dit is echter niet gewenst in ANSI SQL en wij zullen het dan ook nooit gebruiken. Wij zorgen gewoon dat onze tabel en kolomnamen wel in orde zijn!

Datatypen

Bij het aanmaken van een nieuwe tabel, geef je voor elke kolom aan wat voor soort data erin komt te staan. Dit doe je door een datatype aan die kolom toe te kennen.

Veelgebruikte datatypen zijn:

• INT - Integers
• DECIMAL - Decimale getalen
• VARCHAR - Strings
• TEXT - Langere stukken tekst
• DATE - Datum
• DATETIME - Combinatie van datum en tijd

Invoegen van records

Nu we een eigen tabel gemaakt hebben en gezien hebben hoe we de database vanuit PHP kunnen benaderen, is het tijd om eens wat gegevens aan onze tabel toe te gaan voegen.

Syntax

Voor het invoegen van records gebruiken we een INSERT query. De syntax van zo'n query ziet er als volgt uit:

INSERT INTO tabelnaam
(
    kolomnaam,
    kolomnaam
)
VALUES
(
    waarde,
    waarde
);

De query beginnen we met INSERT INTO gevolgd door de naam van de tabel waarin we records willen invoegen. Achter de tabelnaam staan tussen haakjes de kolommen in welke we gegevens weg gaan schrijven. In het VALUES gedeelte geven we vervolgens de waarden op die we in elke kolom willen invoegen.

Invoegen van een record (SQL)

De INSERT query voor het invoegen van de eerste rij uit onze tabel ziet er als volgt uit:

INSERT INTO werknemers
(
    voornaam,
    tussenvoegsel,
    achternaam,
    geboortedatum,
    salaris_schaal
)
VALUES
(
    'Nico',
    'de',
    'Boer',
    '1958-03-24',
    18
);

Allereerst valt ons op dat we de kolom id niet in onze INSERT query terug zien. Dat komt omdat we een auto_increment op die kolom hebben staan waardoor MySQL automatisch een waarde toekent.

Verder valt te zien dat we strings in SQL net zo goed tussen quotes zetten als in PHP. Aangezien we ook hier enkele quotes gebruiken, zullen we zien dat we in PHP dubbele quotes gaan gebruiken voor onze SQL queries.

Meerdere records tegelijk invoegen (SQL)

Het is ook mogelijk om met één query meerdere records in te voegen. Dit doen we door de VALUES van de verschillende records te scheiden met een komma:

INSERT INTO werknemers
(
    voornaam,
    tussenvoegsel,
    achternaam,
    geboortedatum,
    salaris_schaal
)
VALUES
(
    'Tim',
    '',
    'Janssen',
    '1982-01-30',
    10
),
(
    'Pim',
    '',
    'Vosse',
    '1982-01-30',
    10
);

Het enige dat aan deze query anders is ten opzichte van de eerste INSERT query, is dat er nu twee records tegelijkertijd ingevoegd worden. Verder is alles hetzelfde.  

Wijzigen van records

Nu hebben we records in een tabel staan en weten we hoe we deze records uit de tabel moeten selecteren. De volgende stap is het wijzigen van records in de database. Hiervoor kent SQL de UPDATE query.

Syntax

De syntax van de UPDATE query ziet er als volgt uit:

UPDATE
    tabelnaam
SET
    kolomnaam = 'waarde',
    kolomnaam = 'waarde'
WHERE
    kolomnaam = 'waarde';

Dit is de basis van de UPDATE query en die is altijd hetzelfde. Ook deze query kan op verschillende manieren uitgebreid worden, maar wij houden het voor nu bij de basis.

Een record wijzigen (SQL)

Stel dat we de salaris schaal van onze werknemer 'Tim' willen verhogen naar 12. Dan kunnen we dat met de volgende query doen:

UPDATE
    werknemers
SET
    salaris_schaal = 12
WHERE
    voornaam = 'Tim';

Bij een UPDATE query geeft MySQL geen records terug, in de output kunnen we enkel zien of er records gewijzigd zijn. Zoals we zien is dat hier het geval. Als je dit wilt controleren, zou je een SELECT query uit kunnen voeren en de resultaten bekijken.  

Verwijderen van records

De laatste belangrijke bewerking die we op data in de database uit kunnen voeren, is het verwijderen van records uit de database. De query die we hiervoor gebruiken is een DELETE query.

Syntax

De syntax van de DELETE query ziet er als volgt uit:

DELETE FROM
    tabelnaam
WHERE
    kolomnaam = 'waarde';

In deze query worden er geen kolomnamen opgegeven tussen DELETE en FROM. Dit is niet nodig aangezien toch het hele record uit de tabel verwijderd wordt. Deze query zal alle records verwijderen die voldoen aan de voorwaarde(n) uit de WHERE clausule.

Een record verwijderen (SQL)

Stel dat we alle werknemers willen verwijderen die in salaris schaal 10 zitten. We zouden de volgende query kunnen uitvoeren:

DELETE FROM
    werknemers
WHERE
    salaris_schaal = 10;

Net als bij een UPDATE query zal ook de DELETE query natuurlijk geen records teruggeven. De enige informatie die we terugkrijgen is het aantal rijen dat aangepast is.

Query`s Denken over informatica