# Databases Wat een relationele database is, je eerste SQL queries op 151 Pokémon, en MySQL op je eigen laptop met DBngin en TablePlus. Multimedia & Creatieve Technologie · 2.1 Programming principles · Les 4 · Databases, deel 1 · versie 1.1.1 · 2026-10-10 Tags: database, sql, mysql, tableplus Slides: Speaker notes: ## Agenda 1. Wat een database is 2. RDBMS en SQL 3. Tabellen ontwerpen 4. SELECT en WHERE 5. Meer SELECT 6. Data aanpassen 7. MySQL op je laptop: DBngin en TablePlus 8. Opdracht ## Vandaag en volgende week ### Vandaag 😁 - Eén tabel met 151 rijen - Ophalen, filteren, sorteren en tellen - Aanpassen, verwijderen en toevoegen - MySQL op je eigen laptop ### Volgende week 🤯 - Meerdere tabellen - Relaties en foreign keys - JOIN ## 1. Wat een database is ### Je kent dit al | id | email | first_name | last_name | | --- | --- | --- | --- | | 1 | jan.janssens@student.kdg.be | Jan | Janssens | | 2 | bob.debouwer@student.kdg.be | Bob | De Bouwer | | 3 | fatima.elamrani@student.kdg.be | Fatima | El Amrani | Kolommen met een naam, en een rij per student. Zoals in Google Sheets, maar strenger. ### Overal rondom je - Je to-do lijstje en je agenda - Je verlanglijstje op bol.com - Elke like, elke volger en elk bericht op Instagram - De highscores van een game - Je punten op Canvas **Data, gestructureerd bewaard.** Zodat je ze later terugvindt, aanpast en weer weggooit. Ook als het er miljoenen zijn. ### Wat erin zit - Gebruikers: e-mail, gebruikersnaam en een hash van het wachtwoord, nooit het wachtwoord zelf - De bestellingen van een webshop - Voorkeuren: notificaties aan, dark mode uit - De teksten van een website die een klant zelf aanpast - Waar een afbeelding staat. Het bestand zelf staat op de schijf. - Highscores en missies van een game **English please 🙏** first_name, niet voornaam. Je code is Engels, dus je database ook. ## 2. RDBMS en SQL ### DBMS **Database Management System**: Software die data opslaat, terug ophaalt en beheert. Jij vraagt, het DBMS zoekt het op de schijf. - **MongoDB**: Documenten, zoals JSON - **Redis**: Sleutels en waarden, in het geheugen - **MySQL**: Tabellen met rijen en kolommen ### Relationeel of niet #### RDBMS - Data in tabellen, met rijen en kolommen - Relaties tussen tabellen: een trainer heeft Pokémon - Je praat ermee in SQL - MySQL, PostgreSQL, SQLite, SQL Server #### NoSQL - Documenten, sleutels, grafen: elk zijn eigen vorm - Geen vaste kolommen, elk document mag anders zijn - Elk een eigen manier om te vragen - MongoDB, Redis, Firestore ### Server, database, tabel MySQL op je laptop met 2 databases: pokedex (tabellen pokemon: id, name, type, hp; trainers: id, name, badges) en portfolio (tabellen projects: id, title, year; messages: id, email, body). ### Wat een RDBMS je geeft - Persistentie: de data staat er nog na een herstart - Integriteit: geen tekst in een kolom met getallen, geen twee rijen met dezelfde id - Gebruikers en rechten: wie mag lezen, wie mag schrijven - Locks: twee mensen die tegelijk bestellen, krijgen niet hetzelfde laatste ticket - Backups - Snel, ook bij miljoenen rijen ### Waarom geen JSON bestand #### products.json ```json [ { "name": "Space Star", "dealer": "Garage Peeters", "dealer_city": "Lier" }, { "name": "ASX", "dealer": "Garage Peeters", "dealer_city": "Lier" } ] ``` #### Wat er misgaat - Dezelfde data staat er tien keer in. De garage verhuist, en je past tien plekken aan. - Elke vraag is code die alles inleest: welke auto’s kosten minder dan 20.000 euro? - Twee mensen schrijven tegelijk, en een van de twee is zijn werk kwijt - Wie het bestand kan lezen, ziet alles ### Welk RDBMS Staafdiagram, DB-Engines, relationele databases, oktober 2026. Rechts: punten gewonnen of verloren op een jaar.: 1. Oracle (1120, -93.0 op een jaar), 2. MySQL (838, -41.7 op een jaar), 3. Microsoft SQL Server (694, -20.6 op een jaar), 4. PostgreSQL (689, +45.6 op een jaar), 5. Snowflake (221, +22.6 op een jaar), 6. Databricks (171, +42.4 op een jaar), 7. IBM Db2 (106, -16.1 op een jaar), 8. SQLite (96, -8.8 op een jaar), 9. MariaDB (76, -12.2 op een jaar), 10. Apache Hive (70, -5.6 op een jaar). ### MySQL - My: de naam van de dochter van een van de makers - SQL: Structured Query Language - Server software. Ze draait altijd, en programma’s verbinden ermee. - Van Oracle. MariaDB is de open source fork van de oorspronkelijke makers. **[MySQL](https://www.mysql.com/)**: Wat wij gebruiken **[MariaDB](https://mariadb.org/)**: Bijna hetzelfde, open source **[PostgreSQL](https://www.postgresql.org/)**: Meer features, groeit hard ### LAMP en LEMP | | LAMP | LEMP | | --- | --- | --- | | Besturingssysteem | Linux | Linux | | Webserver | Apache | Nginx (spreek uit: engine x) | | Database | MySQL | MySQL | | Taal | PHP | PHP | ### SQLite - Geschreven in 2000, in opdracht van de US Navy. Voor raketschepen. 😱 - Geen server: de hele database is één bestand - Niets te installeren - Zit in elke smartphone en elke browser > [!NOTE] > **De Querydex** > > De Pokémon in de Querydex zitten in een SQLite database, in je browser. Bijna alles wat we vandaag doen, werkt in MySQL precies hetzelfde. ### SQL - **Data**: `SELECT` ophalen, `INSERT` toevoegen, `UPDATE` aanpassen, `DELETE` verwijderen - **Structuur**: `CREATE` een database of tabel maken, `ALTER` een tabel aanpassen, `DROP` weggooien ```sql SELECT name, type FROM pokemon WHERE hp > 100; ``` ### Waar SQL draait 1. Browser → Webserver, **GET /pokemon/25**: Een gewone request, zoals bij elke pagina. 2. Webserver → MySQL, **SELECT**: Je code maakt er een query van en stuurt die naar de database: `SELECT * FROM pokemon WHERE id = 25` 3. MySQL → Webserver, **Eén rij**: De database zoekt de rij en stuurt het resultaat terug. 4. Webserver → Browser, **HTML**: Je code giet de rij in een template. De browser ziet nooit een query. ## 3. Tabellen ontwerpen ### Database, tabel, kolom, rij | | Wat | Voorbeeld | Niet | | --- | --- | --- | --- | | Database | Alle tabellen van één applicatie | `pokedex` | `pokemon` | | Tabel | Eén soort ding | `pokemon` | `pokedex` | | Kolom | Naam en type van één eigenschap | `name VARCHAR(50)` | `name_and_type` | | Rij | Eén ding, ook record of row | Pikachu | | ### Naamgeving | | Stijl | Voorbeeld | | --- | --- | --- | | Database | snake_case, duidelijk | `pokedex, kdg_portfolio` | | Tabel | snake_case, meervoud | `students, order_items, pokemon` | | Kolom | snake_case, enkelvoud | `first_name, type_2, evolves_from` | > [!TIP] > Engels, kleine letters, en geen spaties of leestekens. Dan werkt het op elke server. ### snake_case 🐍 ![snake_case met een slang, PascalCase met Blaise Pascal en camelCase met een kameel, telkens met voor- en nadelen](https://slides.mctantwerp.be/decks/nl/programming-principles/05-databases/naming-cases.webp) ### Column types | Type | Voor | Voorbeeld | | --- | --- | --- | | `INT` | Gehele getallen | hp, aantal, id | | `DECIMAL(8,2)` | Exacte kommagetallen | prijs, gewicht | | `FLOAT` | Kommagetallen die mogen afronden | coördinaten, metingen | | `VARCHAR(n)` | Tekst tot n tekens | naam, e-mail | | `TEXT` | Lange tekst | blogpost, beschrijving | | `DATE` | Een datum, of DATETIME met het uur erbij | date_of_birth, created_at | | `BOOLEAN` | 1 of 0 | legendary, is_admin | ### VARCHAR - Jij kiest de maximumlengte: `VARCHAR(50)` voor een naam, `VARCHAR(255)` voor een e-mail - Neemt alleen de ruimte die de tekst nodig heeft. `Pikachu` kost minder dan `Charmander`. - Langer dan 255 tekens mag, maar voor echt lange tekst neem je TEXT - Een postcode of telefoonnummer is tekst, geen getal. Je rekent er nooit mee, en 0485 verliest zijn nul als INT. ### Primary key *pokedex.sql* ```sql CREATE TABLE pokemon ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, category VARCHAR(50) NOT NULL, type VARCHAR(20) NOT NULL, type_2 VARCHAR(20) NULL, ability VARCHAR(50) NOT NULL, height DECIMAL(4,1) NOT NULL, legendary BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (id) ); ``` - Uniek per tabel: twee rijen met dezelfde id kan niet - Nooit leeg: NOT NULL - Telt vanzelf op met AUTO_INCREMENT - Negen op de tien keer heet hij `id` ### Bewaar wat je kan berekenen #### Niet - `full_name` - `age` - `price VARCHAR` - `date VARCHAR` #### Wel - `first_name` en `last_name`, dan kan je op achternaam sorteren - `date_of_birth`, die is volgend jaar nog juist - `price DECIMAL(8,2)`, dan kan je optellen - `published_at DATE`, dan kan je sorteren ### Welk type geef je aan de kolom price van een webshop? - A. VARCHAR(10) - B. INT - C. FLOAT - D. DECIMAL(8,2) **Antwoord:** D. DECIMAL(8,2) DECIMAL is exact. FLOAT rondt af in binair, en dan klopt je totaal op een cent na niet. VARCHAR kan je niet optellen of sorteren als getal. INT kan wel, als je in centen rekent, maar dan moet iedereen in het team dat weten. ## 4. SELECT en WHERE Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). SELECT. Startquery: `SELECT * FROM pokemon;` **Een query is een virtuele tabel.** Een SELECT geeft een resultaat terug, de tabel zelf blijft zoals ze was. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). Kolommen kiezen. Startquery: `SELECT name, type FROM pokemon;` Knoppen met andere queries: `SELECT name, type FROM pokemon;`, `SELECT name, hp, attack FROM pokemon;`, `SELECT type, name FROM pokemon;`. ### Hoe je een query schrijft ```sql SELECT name, hp FROM pokemon WHERE type = 'Fire' ORDER BY hp DESC; ``` - Keywords in hoofdletters, je eigen namen in kleine letters - Tekst tussen enkele quotes, getallen zonder - Een puntkomma sluit de query af - Witruimte maakt niet uit. Een lange query zet je op meerdere regels. ### Vergelijken | Operator | Betekent | Voorbeeld | | --- | --- | --- | | `=` | Gelijk aan | `type = 'Fire'` | | `<> !=` | Verschillend van | `type <> 'Normal'` | | `< > <= >=` | Kleiner, groter | `hp >= 100` | | `BETWEEN` | Tussen, grenzen inbegrepen | `weight BETWEEN 50 AND 100` | | `IN` | Een van deze | `type IN ('Fire', 'Water')` | | `LIKE` | Lijkt op een patroon | `name LIKE 'P%'` | | `IS NULL` | Heeft geen waarde | `type_2 IS NULL` | Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). WHERE. Startquery: `SELECT name, type, type_2 FROM pokemon WHERE type = 'Fire';` Knoppen met andere queries: `SELECT name, type, type_2 FROM pokemon WHERE type = 'Fire';`, `SELECT name, hp FROM pokemon WHERE hp > 100;`, `SELECT name, weight FROM pokemon WHERE weight BETWEEN 50 AND 100;`, `SELECT name, type FROM pokemon WHERE type IN ('Ghost', 'Dragon', 'Ice');`, `SELECT name, type FROM pokemon WHERE type <> 'Normal';`. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). NULL. Startquery: `SELECT name, type, type_2 FROM pokemon WHERE type_2 = NULL;` Knoppen met andere queries: `SELECT name, type, type_2 FROM pokemon WHERE type_2 = NULL;`, `SELECT name, type, type_2 FROM pokemon WHERE type_2 IS NULL;`, `SELECT name, type, type_2 FROM pokemon WHERE type_2 IS NOT NULL;`. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). LIKE. Startquery: `SELECT name FROM pokemon WHERE name LIKE 'P%';` Knoppen met andere queries: `SELECT name FROM pokemon WHERE name LIKE 'P%';`, `SELECT name FROM pokemon WHERE name LIKE '%chu';`, `SELECT name, ability FROM pokemon WHERE ability LIKE '%Eye%';`, `SELECT name FROM pokemon WHERE name LIKE '____';`. ### AND, OR en NOT | | Waar als | Voorbeeld | | --- | --- | --- | | `AND` | Beide voorwaarden waar zijn | `type = 'Fire' AND hp > 70` | | `OR` | Minstens één waar is | `type = 'Fire' OR type = 'Water'` | | `NOT` | De voorwaarde niet waar is | `NOT type = 'Bug'` | > [!WARNING] > **Voorrang** > > Eerst NOT, dan AND, dan OR. Bij twijfel zet je haakjes. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). Haakjes 🚨. Startquery: `SELECT name, type, hp FROM pokemon WHERE type = 'Fire' OR type = 'Water' AND hp > 80;` Knoppen met andere queries: `SELECT name, type, hp FROM pokemon WHERE type = 'Fire' OR type = 'Water' AND hp > 80;`, `SELECT name, type, hp FROM pokemon WHERE (type = 'Fire' OR type = 'Water') AND hp > 80;`. ### Wat geeft `WHERE type = 'Grass' OR type = 'Bug' AND legendary = 1` terug? - A. Alle legendary Grass en Bug Pokémon - B. Alle Grass Pokémon, en de Bug Pokémon die legendary zijn - C. Niets, de query is fout - D. Alle Grass en alle Bug Pokémon **Antwoord:** B. Alle Grass Pokémon, en de Bug Pokémon die legendary zijn AND gaat voor OR. SQL leest type = 'Grass' OR (type = 'Bug' AND legendary = 1). Wil je alleen legendaries, dan zet je haakjes rond de OR. ### LIMIT en OFFSET ```sql SELECT id, name FROM pokemon ORDER BY id LIMIT 5 OFFSET 10; ``` - `LIMIT 5` geeft maximaal vijf rijen terug - `OFFSET 10` slaat er eerst tien over, je krijgt rij 11 tot en met 15 - Allebei achteraan in je query, na `ORDER BY` - Zonder `ORDER BY` kiest de database zelf de volgorde - `LIMIT 10, 5` doet hetzelfde, met de offset eerst > [!TIP] > **Pagina's** > > Vijf per pagina: pagina 1 is `OFFSET 0`, pagina 2 `OFFSET 5`, pagina 3 `OFFSET 10`. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). ORDER BY en LIMIT. Startquery: `SELECT name, attack FROM pokemon ORDER BY attack DESC;` Knoppen met andere queries: `SELECT name, attack FROM pokemon ORDER BY attack DESC;`, `SELECT name, type FROM pokemon ORDER BY type, name;`, `SELECT name, weight FROM pokemon ORDER BY weight DESC LIMIT 3;`, `SELECT id, name FROM pokemon ORDER BY id LIMIT 5 OFFSET 10;`. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). Vang ze. Startquery: `SELECT name, speed FROM pokemon;` Opdracht: De naam en de speed van de vijf snelste Pokémon die geen legendary zijn. Een oplossing: `SELECT name, speed FROM pokemon WHERE legendary = 0 ORDER BY speed DESC LIMIT 5;` ## 5. Meer SELECT Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). Rekenen en AS. Startquery: `SELECT name, attack + defense AS total FROM pokemon ORDER BY total DESC;` Knoppen met andere queries: `SELECT name, attack + defense AS total FROM pokemon ORDER BY total DESC;`, `SELECT CONCAT(name, ' (', type, ')') AS label FROM pokemon;`, `SELECT name, hp FROM pokemon WHERE hp % 2 = 0;`, `SELECT name, weight, weight * 2.2 AS pounds FROM pokemon;`. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). CASE en COALESCE. Startquery: `SELECT name, weight, CASE WHEN weight > 100 THEN 'zwaar' WHEN weight > 20 THEN 'gemiddeld' ELSE 'licht' END AS class FROM pokemon;` Knoppen met andere queries: `SELECT name, weight, CASE WHEN weight > 100 THEN 'zwaar' WHEN weight > 20 THEN 'gemiddeld' ELSE 'licht' END AS class FROM pokemon;`, `SELECT name, type, COALESCE(type_2, 'geen') AS type_2 FROM pokemon;`. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). Tellen en groeperen. Startquery: `SELECT COUNT(*) AS total FROM pokemon;` Knoppen met andere queries: `SELECT COUNT(*) AS total FROM pokemon;`, `SELECT DISTINCT type FROM pokemon ORDER BY type;`, `SELECT type, COUNT(*) AS total FROM pokemon GROUP BY type ORDER BY total DESC;`, `SELECT type, ROUND(AVG(hp), 1) AS avg_hp, MAX(weight) AS max_weight FROM pokemon GROUP BY type;`. ### GROUP BY en SELECT \* ```sql SELECT * FROM pokemon GROUP BY type; ``` *MySQL 8* ```text ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pokedex.pokemon.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ``` > [!TIP] > Na GROUP BY selecteer je alleen de kolom waarop je groepeert, plus COUNT, AVG, MIN, MAX of SUM. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). Vang ze. Startquery: `SELECT type, attack FROM pokemon;` Opdracht: Per type de gemiddelde attack, zonder afronden. Een oplossing: `SELECT type, AVG(attack) AS avg_attack FROM pokemon GROUP BY type;` ### Opdracht: Tussenopdracht *15 minuten, Individueel* Schrijf een query voor elke vraag, in de Querydex op de volgende slide. 1. Hoeveel Water Pokémon zijn er? 2. Welke Pokémon hebben Mr. of Jr. in hun naam? 3. Wat is de gemiddelde hp van alle legendaries? 4. Welke tien Pokémon hebben de hoogste attack + defense + speed? 5. Hoeveel Pokémon zijn er per ability? Alleen de abilities met meer dan drie. 6. Bonus: welke Pokémon evolueren niet uit een andere, en hebben toch geen tweede type? Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). Speeltuin. Startquery: `SELECT * FROM pokemon;` ## 6. Data aanpassen **De WHERE is alles.** UPDATE en DELETE zonder WHERE raken elke rij van de tabel. Er is geen prullenbak en geen Cmd+Z. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). UPDATE. Startquery: `UPDATE pokemon SET hp = 99 WHERE name = 'Pikachu';` Knoppen met andere queries: `UPDATE pokemon SET hp = 99 WHERE name = 'Pikachu';`, `UPDATE pokemon SET type = 'Electric', type_2 = 'Fire' WHERE id = 26;`, `UPDATE pokemon SET hp = hp + 10 WHERE legendary = 1;`, `UPDATE pokemon SET name = 'Pikachu';`. Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). DELETE. Startquery: `DELETE FROM pokemon WHERE type = 'Bug';` Knoppen met andere queries: `DELETE FROM pokemon WHERE type = 'Bug';`, `DELETE FROM pokemon WHERE weight > 200;`, `DELETE FROM pokemon WHERE name LIKE '%a%';`, `DELETE FROM pokemon;`. ### Eerst SELECT, dan DELETE ```sql -- 1. Kijk wat je raakt SELECT * FROM pokemon WHERE type = 'Bug'; -- 2. Dezelfde WHERE, nu pas weg DELETE FROM pokemon WHERE type = 'Bug'; ``` - 12 rijen in de SELECT, dan verwacht je er 12 in de DELETE - Zelfde regel voor UPDATE - Liever een id dan een naam: WHERE id = 25 raakt er altijd maar één Querydex, een SQL editor op de slide met een tabel pokemon (151 Pokémon). INSERT. Startquery: `INSERT INTO pokemon (name, category, type, ability, height, weight, hp, attack, defense, speed) VALUES ('Sparky', 'Mouse', 'Electric', 'Static', 0.4, 6, 35, 55, 40, 90);` Knoppen met andere queries: `INSERT INTO pokemon (name, category, type, ability, height, weight, hp, attack, defense, speed) VALUES ('Sparky', 'Mouse', 'Electric', 'Static', 0.4, 6, 35, 55, 40, 90);`, `INSERT INTO pokemon VALUES ('Sparky', 'Electric');`, `INSERT INTO pokemon (name, category, type, ability, height, weight, hp, attack, defense, speed) VALUES ('Electric', 'Mouse', 'Sparky', 'Static', 0.4, 6, 35, 55, 40, 90);`, `INSERT INTO pokemon (name, category, type, ability, height, weight, hp, attack, defense, speed) VALUES ('Sparky', 'Mouse', 'Electric', 'Static', 0.4, 6, 35, 55, 40, 90), ('Blazy', 'Lizard', 'Fire', 'Blaze', 0.6, 9, 40, 50, 40, 65);`. ### Je voert `UPDATE pokemon SET hp = 1;` uit. Wat gebeurt er? - A. Niets, er ontbreekt een WHERE - B. De eerste rij krijgt hp 1 - C. Elke rij krijgt hp 1 - D. Je krijgt eerst een waarschuwing **Antwoord:** C. Elke rij krijgt hp 1 Een WHERE is niet verplicht. Zonder WHERE geldt de UPDATE voor elke rij, en MySQL vraagt niets. Sommige clients waarschuwen je wel, maar daar reken je beter niet op. ## 7. MySQL op je laptop ### Server en client #### De server - MySQL, die draait en wacht op queries - Op 127.0.0.1, poort 3306 - Geen venster, geen icoon **[DBngin](https://dbngin.com/)**: Start en stopt de server. Mac en Windows, gratis. #### De client - Verbindt met de server - Toont tabellen en rijen - Voert je queries uit **[TablePlus](https://tableplus.com/)**: Een venster op je database. Mac, Windows en Linux. ### DBngin 1. **Download en installeer**: Van [dbngin.com](https://dbngin.com/), voor Mac of Windows. Sta alles toe wat hij vraagt. 2. **Maak een service**: De + linksboven, kies MySQL en versie 8.4. Poort 3306 laat je staan. 3. **Start hem**: Met Start, tot het bolletje groen is 4. **Start on login**: Zodat hij vanzelf draait als je laptop opstart > [!NOTE] > **Wat DBngin aanmaakt** > > Een MySQL server op `127.0.0.1`, poort `3306`, met gebruiker `root` en geen wachtwoord. Op je laptop is dat prima, op een server nooit. ### TablePlus 1. **Download en installeer**: Van [tableplus.com](https://tableplus.com/), voor Mac, Windows of Linux 2. **Nieuwe connectie**: Create a new connection, en kies MySQL 3. **Vul in en test**: Met Test kleuren de velden groen als alles klopt 4. **Connect**: En je ziet de databases op je server - **[Sequel Ace](https://sequel-ace.com/)**: Ook goed, gratis op Mac - **[HeidiSQL](https://www.heidisql.com/)**: Ook goed, gratis op Windows - **[DataGrip](https://www.jetbrains.com/datagrip/)**: Gratis met een JetBrains studentenlicentie ### Verbinden Het venster MySQL Connection van TablePlus, ingevuld: Name Lokaal, Host 127.0.0.1, Port 3306, User root, Password leeg, Database leeg. - 127.0.0.1 is je eigen laptop - 3306 is de standaardpoort van MySQL - root heeft alle rechten - Connection refused? Dan draait de server niet: kijk in DBngin. ### Een database maken *SQL editor* ```sql CREATE DATABASE pokedex; ``` - De knop SQL in de toolbar opent een editor - Uitvoeren: Windows Ctrl+Enter, Mac Cmd+Enter - Naar je nieuwe database: Windows Ctrl+K, Mac Cmd+K, en kies pokedex ### Importeren 1. **Download pokedex.sql**: Dezelfde 151 Pokémon als in de Querydex 2. **Selecteer pokedex**: Met Windows Ctrl+K, Mac Cmd+K 3. **File, Import**: From SQL Dump, kies het bestand en Import 4. **Vernieuwen**: Windows Ctrl+R, Mac Cmd+R, en pokemon staat links **[pokedex.sql](/decks/nl/programming-principles/05-databases/pokedex.sql)**: De structuur en de data, voor MySQL en MariaDB *Daarna in de SQL editor* ```sql SELECT * FROM pokemon; ``` ## 8. Opdracht ### Opdracht: Databases, deel 1 *Individueel* Northwind is een demo database van Microsoft: een groothandel met klanten, producten en bestellingen. 1. Installeer DBngin en TablePlus, en start een MySQL server 2. Maak een database `northwind` 3. Importeer eerst `northwind.sql`, dan `northwind-data.sql` 4. Maak de 45 queries: SELECT, UPDATE, DELETE en INSERT **[northwind.sql](/decks/nl/programming-principles/05-databases/northwind.sql)**: De structuur, 20 tabellen **[northwind-data.sql](/decks/nl/programming-principles/05-databases/northwind-data.sql)**: De data ### Hoe het werkt - De volgorde telt: een UPDATE of een DELETE verandert wat de queries erna teruggeven. - Database stuk? `DROP DATABASE northwind;`, een nieuwe maken en de twee bestanden opnieuw importeren. - Meer dan tien minuten vast? Vraag het aan een klasgenoot. Daarna pas de oplossing, achter de 😵 onder elke opdracht. ### Opdracht: SELECT: customers 1. Alle customers uit Los Angeles 2. Alle customers die eigenaar zijn (`Owner`) 3. Alle customers uit de state `WA` 4. Alle customers, gesorteerd op voornaam, aflopend 5. Alle customers, gesorteerd op achternaam, oplopend 6. De volledige naam van elke customer als `Voornaam_Achternaam`, in een kolom `full_name` > [!TIP] > **SELECT: customers** > > *Los Angeles* > > 2 rijen. > > ```sql > SELECT * FROM customers > WHERE city = 'Los Angeles'; > ``` > > *Owner* > > 6 rijen. De functie staat in job_title. > > ```sql > SELECT * FROM customers > WHERE job_title = 'Owner'; > ``` > > *WA* > > 2 rijen. > > ```sql > SELECT * FROM customers > WHERE state_province = 'WA'; > ``` > > *Voornaam* > > ```sql > SELECT * FROM customers > ORDER BY first_name DESC; > ``` > > *Achternaam* > > ASC is de standaard, je mag het weglaten. > > ```sql > SELECT * FROM customers > ORDER BY last_name; > ``` > > *full_name* > > ```sql > SELECT CONCAT(first_name, '_', last_name) AS full_name > FROM customers; > ``` ### Opdracht: SELECT: products 1. Alle producten van de categorie `Beverages` 2. Alle producten met een `standard_cost` hoger dan 5 3. Alle producten met een `list_price` van 20 of minder 4. Alle producten met een B in hun `product_code` > [!TIP] > **SELECT: products** > > *Beverages* > > 5 rijen. > > ```sql > SELECT * FROM products > WHERE category = 'Beverages'; > ``` > > *Hoger dan 5* > > 28 rijen. > > ```sql > SELECT * FROM products > WHERE standard_cost > 5; > ``` > > *20 of minder* > > 32 rijen. Met < mis je de producten die precies 20 kosten. > > ```sql > SELECT * FROM products > WHERE list_price <= 20; > ``` > > *Een B* > > 9 rijen. > > ```sql > SELECT * FROM products > WHERE product_code LIKE '%B%'; > ``` ### Opdracht: SELECT: rekenen 1. De naam van elk product, en wat het kost met 70% korting op de `standard_cost` 2. De naam van elk product met een tag: `smart` als er een S in de code zit, anders `dumb` 3. Alle producten met een oneven `reorder_level` 4. Alle producten waar `target_level` geen veelvoud van 50 is > [!TIP] > **SELECT: rekenen** > > *Korting* > > Wie 70% korting krijgt, betaalt nog 30%. > > ```sql > SELECT product_name, standard_cost * 0.3 AS discounted_price > FROM products; > ``` > > *Tag* > > 7 producten zijn smart. > > ```sql > SELECT product_name, > CASE > WHEN product_code LIKE '%S%' THEN 'smart' > ELSE 'dumb' > END AS tag > FROM products; > ``` > > *Oneven* > > 10 rijen. Oneven: de rest na delen door 2 is niet 0. > > ```sql > SELECT * FROM products > WHERE reorder_level % 2 <> 0; > ``` > > *Veelvoud van 50* > > 32 rijen. > > ```sql > SELECT * FROM products > WHERE target_level % 50 <> 0; > ``` ### Opdracht: SELECT: orders en suppliers 1. Alle orders die niet betaald zijn: ze hebben geen `paid_date` 2. Alle orders van 5 april 2006 3. Alle orders die al verstuurd zijn: ze hebben een `shipped_date` 4. Alle orders met een `shipping_fee` tussen 6 en 10 5. Alle orders naar een state waarvan de code op een L eindigt 6. Alle suppliers met een e in hun voornaam die Sales Manager of Marketing Manager zijn > [!TIP] > **SELECT: orders en suppliers** > > *Niet betaald* > > 10 rijen. Met = NULL krijg je er 0, zonder foutmelding. > > ```sql > SELECT * FROM orders > WHERE paid_date IS NULL; > ``` > > *5 april* > > 5 rijen. Een datum schrijf je als jaar, maand, dag, tussen quotes. > > ```sql > SELECT * FROM orders > WHERE order_date = '2006-04-05'; > ``` > > *Verstuurd* > > 39 rijen. > > ```sql > SELECT * FROM orders > WHERE shipped_date IS NOT NULL; > ``` > > *Shipping fee* > > 8 rijen. Bij BETWEEN zijn de grenzen inbegrepen. > > ```sql > SELECT * FROM orders > WHERE shipping_fee BETWEEN 6 AND 10; > ``` > > *Eindigt op L* > > 10 rijen. > > ```sql > SELECT * FROM orders > WHERE ship_state_province LIKE '%L'; > ``` > > *Suppliers* > > 3 rijen. Zonder haakjes zijn het er 5: dan komen alle Marketing Managers mee, ook die zonder e. > > ```sql > SELECT * FROM suppliers > WHERE first_name LIKE '%e%' > AND (job_title = 'Sales Manager' OR job_title = 'Marketing Manager'); > ``` ### Opdracht: UPDATE Controleer elke UPDATE met een SELECT. 1. Elk product met een `standard_cost` lager dan 10 is `discontinued` 2. Tel 5 op bij het `reorder_level` van elk product met een C in de code 3. Jan Kotas en Andrew Cencini (employees) werken nu voor Google 4. Steven Thorpe (employees) noemt zichzelf voortaan Stivie 5. De stad Boise heet nu Boston: pas de orders aan > [!TIP] > **UPDATE** > > *discontinued* > > 26 rijen. TRUE in plaats van 1 mag ook. > > ```sql > UPDATE products > SET discontinued = 1 > WHERE standard_cost < 10; > ``` > > *reorder_level* > > 18 rijen. > > ```sql > UPDATE products > SET reorder_level = reorder_level + 5 > WHERE product_code LIKE '%C%'; > ``` > > *Google* > > 2 rijen. Voornaam en achternaam horen samen, vandaar de haakjes. > > ```sql > UPDATE employees > SET company = 'Google' > WHERE (first_name = 'Jan' AND last_name = 'Kotas') > OR (first_name = 'Andrew' AND last_name = 'Cencini'); > ``` > > *Stivie* > > 1 rij. > > ```sql > UPDATE employees > SET first_name = 'Stivie' > WHERE first_name = 'Steven' AND last_name = 'Thorpe'; > ``` > > *Boston* > > 2 rijen. > > ```sql > UPDATE orders > SET ship_city = 'Boston' > WHERE ship_city = 'Boise'; > ``` ### Opdracht: Meer UPDATE 1. Elk order krijgt een `tax_rate` van 21, en `taxes` die 21% van de `shipping_fee` zijn 2. Supplier Naoki Sato heeft een `home_phone`: 123-456-789 3. Elke customer krijgt een `web_page`: `https://achternaam.northwind.com`, in kleine letters. Zoek `LOWER` op. 4. Customers uit de state `WA` of `CA` krijgen `WMCA` als state 5. Shipping Company A (shippers) staat nu op naam van Kevin Van Oevelen > [!TIP] > **Meer UPDATE** > > *Taxes* > > 48 rijen: elk order, dus geen WHERE. Twee kolommen in één SET, met een komma ertussen. > > ```sql > UPDATE orders > SET tax_rate = 21, taxes = shipping_fee * 0.21; > ``` > > *Naoki Sato* > > 1 rij. > > ```sql > UPDATE suppliers > SET home_phone = '123-456-789' > WHERE first_name = 'Naoki' AND last_name = 'Sato'; > ``` > > *web_page* > > 29 rijen. LOWER zet tekst in kleine letters, CONCAT plakt de stukken aan elkaar. > > ```sql > UPDATE customers > SET web_page = CONCAT('https://', LOWER(last_name), '.northwind.com'); > ``` > > *WMCA* > > 5 rijen. > > ```sql > UPDATE customers > SET state_province = 'WMCA' > WHERE state_province IN ('WA', 'CA'); > ``` > > *Kevin* > > 1 rij. > > ```sql > UPDATE shippers > SET first_name = 'Kevin', last_name = 'Van Oevelen' > WHERE company = 'Shipping Company A'; > ``` ### Opdracht: DELETE Schrijf eerst de SELECT met dezelfde WHERE. Klopt het resultaat, dan maak je er een DELETE van. 1. Alle invoices van voor 3 april 2006 2. Alle producten zonder `quantity_per_unit` 3. De producten met code `NWTCM-95`, `NWTCM-96`, `NWTBGM-86` en `NWTB-87` 4. Alle producten uit `Canned Fruit & Vegetables` met een `standard_cost` lager dan 5 5. Alle customers uit Boston > [!TIP] > **DELETE** > > *Invoices* > > 11 rijen. > > ```sql > DELETE FROM invoices > WHERE invoice_date < '2006-04-03'; > ``` > > *Zonder eenheid* > > 5 rijen. > > ```sql > DELETE FROM products > WHERE quantity_per_unit IS NULL; > ``` > > *Codes* > > 4 rijen. > > ```sql > DELETE FROM products > WHERE product_code IN ('NWTCM-95', 'NWTCM-96', 'NWTBGM-86', 'NWTB-87'); > ``` > > *Canned* > > 7 rijen. > > ```sql > DELETE FROM products > WHERE category = 'Canned Fruit & Vegetables' > AND standard_cost < 5; > ``` > > *Boston* > > 2 rijen. > > ```sql > DELETE FROM customers > WHERE city = 'Boston'; > ``` ### Opdracht: Meer DELETE 1. Alle `purchase_order_details` die nog niet ontvangen zijn: ze hebben geen `date_received` 2. Alle `order_details` met een even `unit_price`, als ze status 2 hebben of een `purchase_order_id` 3. Supplier I en Supplier J (suppliers) 4. Alles uit `sales_reports` 5. Shipping Company B (shippers). Welke foutmelding krijg je? > [!TIP] > **Meer DELETE** > > *Niet ontvangen* > > 12 rijen. > > ```sql > DELETE FROM purchase_order_details > WHERE date_received IS NULL; > ``` > > *order_details* > > 19 rijen. Zonder haakjes zijn het er 29. > > ```sql > DELETE FROM order_details > WHERE unit_price % 2 = 0 > AND (status_id = 2 OR purchase_order_id IS NOT NULL); > ``` > > *Suppliers* > > 2 rijen. > > ```sql > DELETE FROM suppliers > WHERE company IN ('Supplier I', 'Supplier J'); > ``` > > *sales_reports* > > 5 rijen. Geen WHERE, dus de tabel is leeg. Hier is dat de bedoeling. > > ```sql > DELETE FROM sales_reports; > ``` > > *Shipping Company B* > > De query is juist, en toch weigert MySQL: error 1451, a foreign key constraint fails. Er zijn nog orders die naar deze shipper verwijzen. Wat een foreign key is en wat je ermee doet, zien we volgende week. > > ```sql > DELETE FROM shippers > WHERE company = 'Shipping Company B'; > ``` ### Opdracht: INSERT 1. Een customer met gegevens die je zelf verzint. Elke kolom krijgt een waarde, behalve `id`. 2. Een customer Ronny Ronssens, met als functie Developer 3. Twee suppliers in één query: Sam Serrien van GeNx (`genx.be`) en Alessandro Aussems van Meteor (`meteor.be`) 4. Een product `APPLE-13`: iPhone 13, categorie Iphones, `standard_cost` 899.99 en `list_price` 950.52 5. Een order van vandaag naar Salesianenlaan 90, Hoboken, met 120.45 `taxes`, betaald met Payconiq. Zoek `CURRENT_DATE` op. > [!TIP] > **INSERT** > > *Verzonnen* > > Jouw waarden zijn anders. id laat je weg, die telt vanzelf op. > > ```sql > INSERT INTO customers > (company, last_name, first_name, email_address, job_title, > business_phone, home_phone, mobile_phone, fax_number, > address, city, state_province, zip_postal_code, country_region, > web_page, notes, attachments) > VALUES > ('G&L', 'Vermeulen', 'Joske', 'joske@vermeulen.be', 'CEO', > '123-456-789', '987-654-321', '456-789-123', '789-123-456', > 'Kerkstraat 1', 'Antwerpen', 'Antwerpen', '2000', 'Belgium', > 'https://vermeulen.be', 'Joske is een toffe peer', 'joske.jpg'); > ``` > > *Ronny* > > Kolommen die je niet noemt, krijgen NULL of hun standaardwaarde. > > ```sql > INSERT INTO customers (first_name, last_name, job_title) > VALUES ('Ronny', 'Ronssens', 'Developer'); > ``` > > *Suppliers* > > Twee rijen in één query, met een komma tussen de twee reeksen waarden. > > ```sql > INSERT INTO suppliers (company, first_name, last_name, web_page) > VALUES > ('GeNx', 'Sam', 'Serrien', 'genx.be'), > ('Meteor', 'Alessandro', 'Aussems', 'meteor.be'); > ``` > > *APPLE-13* > > Een kommagetal schrijf je met een punt. > > ```sql > INSERT INTO products (product_code, product_name, standard_cost, list_price, category) > VALUES ('APPLE-13', 'iPhone 13', 899.99, 950.52, 'Iphones'); > ``` > > *Order* > > CURRENT_DATE is de datum van vandaag. NOW() geeft ook het uur. > > ```sql > INSERT INTO orders (order_date, ship_address, taxes, payment_type) > VALUES (CURRENT_DATE, 'Salesianenlaan 90, Hoboken', 120.45, 'Payconiq'); > ``` ## Begrippen - **Database**: Data die gestructureerd bewaard wordt, zodat je ze later terugvindt, aanpast en weggooit. In een RDBMS is een database een verzameling tabellen, meestal één per applicatie. (slide 7) - **DBMS**: Database Management System: de software die data opslaat, ophaalt en beheert. MongoDB, Redis, MySQL. (slide 11) - **RDBMS**: Relational Database Management System: een DBMS dat data in tabellen met kolommen en rijen bewaart, relaties tussen tabellen legt en met SQL werkt. MySQL, PostgreSQL, SQLite. (slide 12) - **SQL**: Structured Query Language: de taal waarmee je een relationele database vraagt om data op te halen, toe te voegen, aan te passen of te verwijderen, en om tabellen te maken. (slide 20) - **Query**: Eén opdracht in SQL aan de database, zoals SELECT \* FROM pokemon. Een SELECT geeft een virtueel resultaat terug, de tabel zelf verandert niet. (slide 20) - **Tabel**: Een verzameling rijen met dezelfde kolommen, voor één soort ding: pokemon, students, orders. Naam in snake_case en in het meervoud. (slide 23) - **Kolom**: Een veld met een naam en een datatype, zoals name VARCHAR(50). Elke rij heeft een waarde voor elke kolom, of NULL. Naam in snake_case en enkelvoud. (slide 23) - **Rij**: Eén record in een tabel: één Pokémon, één student, één bestelling. Ook row of record genoemd. (slide 23) - **Primary key**: De kolom die elke rij uniek maakt, meestal id. Mag niet leeg zijn, kan niet dubbel voorkomen en telt met AUTO_INCREMENT vanzelf op. (slide 28) - **NULL**: Geen waarde. Niet 0 en geen lege tekst. Je test erop met IS NULL of IS NOT NULL, want = NULL is nooit waar. (slide 38) - **VARCHAR**: Een kolomtype voor tekst met een maximumlengte, zoals VARCHAR(50). Neemt alleen de ruimte in die de tekst nodig heeft. Voor lange tekst neem je TEXT. (slide 26) - **DECIMAL**: Een exact kommagetal, zoals DECIMAL(8,2) voor een prijs. FLOAT rondt af, en dat wil je niet bij geld. (slide 26) - **snake_case**: Kleine letters met een underscore tussen de woorden: first_name, order_items. De afspraak voor databases, tabellen en kolommen. (slide 24) - **SELECT**: Haalt data op. SELECT name, type FROM pokemon geeft twee kolommen van elke rij. Met \* krijg je alle kolommen. (slide 32) - **WHERE**: Het filter van een query: alleen de rijen waarvoor de voorwaarde waar is. Werkt bij SELECT, UPDATE en DELETE. (slide 37) - **LIKE**: Vergelijkt tekst met een patroon. % staat voor nul of meer tekens, \_ voor precies één. name LIKE 'P%' vindt alles wat met P begint. (slide 39) - **ORDER BY**: Sorteert het resultaat op een of meer kolommen. ASC is oplopend en de standaard, DESC aflopend. (slide 44) - **LIMIT**: Beperkt het aantal rijen in het resultaat. LIMIT 5 OFFSET 10 slaat er tien over en geeft de vijf daarna. (slide 43) - **OFFSET**: Slaat rijen over voor LIMIT begint te tellen. Met vijf per pagina is pagina 3 LIMIT 5 OFFSET 10. (slide 43) - **Alias**: Een andere naam voor een kolom in het resultaat, met AS: attack + defense AS total. (slide 47) - **GROUP BY**: Voegt rijen met dezelfde waarde samen tot één rij, zodat je per groep kan tellen of rekenen met COUNT, AVG, MIN en MAX. (slide 49) - **UPDATE**: Past bestaande rijen aan: UPDATE pokemon SET hp = 50 WHERE id = 25. Zonder WHERE pas je elke rij aan. (slide 56) - **DELETE**: Verwijdert rijen: DELETE FROM pokemon WHERE id = 25. Zonder WHERE is de tabel leeg, en er is geen prullenbak. (slide 57) - **INSERT**: Voegt een rij toe: INSERT INTO pokemon (name, type) VALUES ('Sparky', 'Electric'). Zet de kolomnamen er altijd bij. (slide 59) - **MySQL**: Het populairste open source RDBMS, van Oracle. De M in LAMP en de database achter het meeste PHP op het web. (slide 17) - **MariaDB**: Een fork van MySQL door de oorspronkelijke makers, nadat Oracle MySQL kocht. Werkt bijna overal waar MySQL werkt. (slide 17) - **SQLite**: Een RDBMS zonder server: de hele database is één bestand. Zit in elke smartphone en elke browser. (slide 19) - **LAMP**: Linux, Apache, MySQL, PHP: de klassieke stack van een webserver. Met Nginx in plaats van Apache heet het LEMP. (slide 18) - **DBngin**: Een gratis app voor Mac en Windows die MySQL, PostgreSQL en Redis op je laptop start en stopt, zonder installatie in de terminal. (slide 63) - **Database client**: Een programma dat verbindt met een database server, zodat je tabellen ziet en queries uitvoert. TablePlus, Sequel Ace, HeidiSQL, DBeaver. (slide 62) - **TablePlus**: Een database client voor Mac, Windows en Linux, van dezelfde makers als DBngin. De gratis versie volstaat voor dit vak. (slide 64) - **SQL dump**: Een .sql bestand met de CREATE TABLE en de INSERT queries om een database opnieuw op te bouwen. Zo deel je een database. (slide 67) ## Links en bronnen ### In de presentatie - [MySQL](https://www.mysql.com/) (slide 17) - [MariaDB](https://mariadb.org/) (slide 17) - [PostgreSQL](https://www.postgresql.org/) (slide 17) - [DBngin](https://dbngin.com/) (slide 62, slide 63) - [TablePlus](https://tableplus.com/) (slide 62, slide 64) - [Sequel Ace](https://sequel-ace.com/) (slide 64) - [HeidiSQL](https://www.heidisql.com/) (slide 64) - [DataGrip](https://www.jetbrains.com/datagrip/) (slide 64) ### Bronnen - [MySQL 8.4 Reference Manual, SELECT Statement](https://dev.mysql.com/doc/refman/8.4/en/select.html) - [MySQL 8.4 Reference Manual, Data Types](https://dev.mysql.com/doc/refman/8.4/en/data-types.html) - [MySQL 8.4 Reference Manual, The CHAR and VARCHAR Types](https://dev.mysql.com/doc/refman/8.4/en/char.html): VARCHAR tot 65.535 bytes - [MySQL 8.4 Reference Manual, MySQL Handling of GROUP BY](https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html): ONLY_FULL_GROUP_BY - [MySQL 8.4 Reference Manual, Operator Precedence](https://dev.mysql.com/doc/refman/8.4/en/operator-precedence.html) - [DB-Engines Ranking of Relational DBMS](https://db-engines.com/en/ranking/relational+dbms): Stand van oktober 2026 - [SQLite, Most Widely Deployed and Used Database Engine](https://www.sqlite.org/mostdeployed.html) - [TablePlus, import and export a database](https://tableplus.com/blog/2018/10/import-export-database.html) - [PokeAPI](https://pokeapi.co/): De data in de tabel pokemon - [mywind, Northwind voor MySQL](https://github.com/dalers/mywind): De database van de opdracht, BSD 2-Clause - [sql.js](https://sql.js.org/): SQLite in de browser, de motor van de Querydex