Een SQL database maken: stap voor stap tutorial

Structured Query Language, kortweg SQL, is een krachtige taal die wordt gebruikt om gegevens in een relationele database te beheren en te manipuleren. Databases zijn essentieel voor veel toepassingen en bedrijven, omdat ze de opslag en organisatie van grote hoeveelheden informatie mogelijk maken. Het is dan ook voor veel mensen handig om SQL te leren en te begrijpen hoe je een SQL database maakt.

In dit blog zullen we het proces doorlopen van het maken van een database. Hierbij werken we toe naar deze bekende IMDb voorbeeld dataset, en we gebruiken we SQL dialect MySQL. SQL dialecten hebben onderling overeenkomsten en verschillen.

We behandelen de volgende onderwerpen:

• Een SQL database maken
• Een tabel met gegevens toevoegen en aanpassen
• Meerdere tabellen in een SQL database maken
• Conclusies m.b.t. het maken van een SQL database

Een SQL database maken

Om een database te maken met behulp van SQL, gebruiken we de instructie CREATE DATABASE gevolgd door de naam van de database die we willen maken.

Hier volgt een voorbeeld:

CREATE DATABASE movie_database;

Dit creëert een nieuwe database met de naam movie_database. We kunnen nu tabellen in deze database maken om gegevens op te slaan.

Een tabel met gegevens toevoegen en aanpassen

Tabellen worden gebruikt om gegevens in een database te ordenen en op te slaan. Om een nieuwe tabel te maken, gebruiken we het statement CREATE TABLE gevolgd door de naam van de tabel die we willen maken en een lijst met de kolommen in de tabel, samen met hun data types.

Hier lees je alles over het CREATE TABLE statement

We voegen nu een tabel met films toe aan de database:

CREATE TABLE movies (
  id INT NOT NULL DEFAULT '0',
  name VARCHAR(100) DEFAULT NULL,
  year INT DEFAULT NULL,
  rank FLOAT DEFAULT NULL,
  PRIMARY KEY (id)
);

Hiermee maak je een nieuwe tabel met de naam movies met vier kolommen: id, name, year en rank. De id-kolom is de primary key voor de tabel, wat betekent dat elke rij in de tabel uniek wordt geïdentificeerd door de waarde in deze kolom.

Gegevens invoegen in een SQL tabel

Nadat we een tabel hebben gemaakt, kunnen we er gegevens invoegen met behulp van de INSERT INTO statement.

Hier is een voorbeeld:

INSERT INTO movies
VALUES
    (10920, 'Aliens', 1986, 8.2),
    (17173, 'Animal House', 1978, 7.5),
    (18979, 'Apollo 13', 1995, 7.5);

Dit voegt drie nieuwe rijen toe in de filmtabel met de kolommen id, name, year en rank.

Gegevens opvragen uit een SQL database

Om gegevens uit een tabel op te halen, gebruiken we het SELECT statement in onze SQL query.

SELECT *
FROM movies;

Hiermee worden alle rijen en kolommen uit de movies tabel opgehaald.

Resultaat:

We zien nu inderdaad de 3 toegevoegede rijen.

Gegevens updaten in een database

Om bestaande gegevens in een tabel bij te werken, gebruiken we het UPDATE statement. Hier is een voorbeeld:

UPDATE movies SET rank = 8.5 WHERE id = 112290;

Hiermee wordt de rank kolom voor de rij met id gelijk aan 112290 bijgewerkt naar de waarde 8,5.

We bekijken het resultaat:

SELECT *
FROM movies;

De rank is inderdaad aangepast.

Gegevens verwijderen uit een SQL database

Om bestaande gegevens in een tabel te verwijderen, gebruiken we het DELETE FROM statement. Hier is een voorbeeld:

DELETE FROM movies
WHERE id = 10920;

Dit verwijdert de rij met id gelijk aan 10920 uit de movies tabel.

We bekijken het resultaat:

SELECT *
FROM movies;

De rij is inderdaad verwijderd.

Meerdere tabellen in een SQL database maken

Veelal werk je in relationele databases met meerdere tabellen met onderlinge relationships. Dit is om verschillende redenen handig.

Ten eerste kun je op deze manier het aantal datapunten sterk terugbrengen. Daarmee win je flink in de computerkracht die nodig is om door een tabel heen te gaan en het zorgt voor meer snelheid aan de gebruikerskant. Daarnaast vermindert het de benodigde data-opslag fors.

Een tweede voordeel is dat meerdere tabellen zorgen voor overzicht. Iedere tabel behandelt een specifiek onderwerp. Datapunten die niet relevant zijn voor het onderwerp verstoren de aandacht dus niet.

Een laatste voordeel is dat aanpassingen makkelijk doorgevoerd kunnen worden, omdat data maar op één centrale plaats staan opgeslagen.

We voegen een tweede SQL tabel toe in onze databse

Hier is de SQL-code om de tweede tabel te maken:

CREATE TABLE genres (
  id INT NOT NULL DEFAULT '0',
  genre_name VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (id)
);

Deze tabel heeft twee kolommen - id en genre_name. De kolom id is een heel getal en dient als primary key voor de tabel. In de kolom genre_name sla je het filmgenre op.

Laten we nu wat gegevens aan deze tabel toevoegen:

INSERT INTO genres
VALUES
    (1, 'Action'),
    (2, 'Comedy'),
    (3, 'Drama'),
    (4, 'Sci-Fi'),
    (5, 'Thriller');

We hebben vijf verschillende genres aan onze tabel toegevoegd: Action, Comedy, Drama, Sci-Fi, en Thriller. We hebben elk genre een uniek id toegewezen.

We bekijken het resultaat:

SELECT *
FROM genres;

De tabel bevat nu inderdaad 5 rijen.

Een relatie tussen de tabellen toevoegen

Om deze tabel te relateren aan onze vorige movies tabel, moeten we een nieuwe kolom toevoegen aan die tabel. De nieuwe kolom verwijst naar het genre van elke film. We kunnen dit doen door een foreign key toe te voegen aan onze movies tabel die verwijst naar de genres tabel. Hier is de SQL-code om deze kolom toe te voegen:

ALTER TABLE movies
ADD genre_id INT NOT NULL,
ADD CONSTRAINT fk_genre
    FOREIGN KEY (genre_id)
    REFERENCES genres (id);

Deze code voegt een nieuwe kolom met de naam genre_id toe aan onze movies tabel waarin de id van het genre voor elke film wordt opgeslagen. Verder past het een foreign key constraint toe op deze kolom. Deze foreign key constraint verwijst naar de id kolom in de genrestabel, waardoor een relationele link wordt gecreëerd tussen de twee tabellen.

Laten we nu wat gegevens toevoegen aan deze nieuwe kolom in onze movies tabel:

UPDATE movies
SET genre_id = 1
WHERE name IN ('Batman Begins', 'Kill Bill: Vol. 1', 'Kill Bill: Vol. 2', 'Matrix, The', 'Pirates of the Caribbean');

UPDATE movies
SET genre_id = 2
WHERE name IN ('Animal House', 'Footloose', 'Little Mermaid, The', 'Office Space', 'Shrek', 'UHF');

UPDATE movies
SET genre_id = 3
WHERE name IN ('Apollo 13', 'Fargo', 'Few Good Men, A', 'Garden State', 'Hollow Man', 'JFK', 'Lost in Translation', 'Mystic River', 'O Brother, Where Art Thou?', 'Pi', 'Shawshank Redemption, The', 'Stir of Echoes');

UPDATE movies
SET genre_id = 4
WHERE name IN ('Aliens', 'Godfather, The', 'Star Wars', 'Vanilla Sky');

UPDATE movies
SET genre_id = 5
WHERE name IN ('Braveheart', 'Titanic');

In deze SQL-code werken we de kolom genre_id in onze filmtabel bij op basis van de naam van de film. We wijzen elke film een genre_id toe op basis van het genre van de film.

Als we nu de naam van een film en het bijbehorende genre willen ophalen, kunnen we een JOIN statement gebruiken:

SELECT movies.name, genres.genre_name
FROM movies
JOIN genres ON movies.genre_id = genres.id;

De tabellen worden met deze query aan elkaar gelinkt o.b.v. genre_id uit de movies tabel en de id kolom uit de genres tabel. Het haalt vervolgens de naam van de film uit de movies tabel en de genre_name van de film uit de genres tabel.

Conclusies m.b.t. het maken van een SQL database

Het maken van een database met behulp van SQL is een krachtige tool waarmee grote hoeveelheden gegevens kunnen worden opgeslagen en georganiseerd. In dit blog hebben we het proces doorlopen van het maken van een database en tabel, het invoegen van gegevens in de tabel en het opvragen, bijwerken en verwijderen van gegevens uit de tabel met behulp van SQL-query's. Ook hebben we een tweede tabel toegevoegd en een voorbeeld laten zien van hoe relaties tussen deze tabellen kunnen werken.

Wil je nog veel meer leren over SQL? Bekijk dan onze SQL cursus voor data analyse of download onderstaande opleidingsbrochure. In onze SQL cursus wisselen theorie en praktijkoefening elkaar steeds af. Nadat je deze training hebt gevolgd kun je zelfstandig met SQL werken. Je kunt ook altijd even contact opnemen via info@datasciencepartners.nl of 020 - 24 43 146 als je een vraag hebt.