2. DML

Data Modification Language is de taal die we gebruiken om de data van onze database te bekijken of aan te passen. Met DML kunnen we CRUD operaties uitvoeren. Create, Read, Update en Delete.

SELECT

SELECT is het commando dat we gebruiken om data op te vragen uit de database.

    SELECT { DISTINCT } expression
    FROM table
    { WHERE condition }

LIKE operator

LIKE wordt gebruikt om wildcard searches uit te voeren. Deze kan enkel gebruikt worden op alfanumerieke velden.

% is een match anything character voor een onbeperkt aantal karakters (0 tot n). Zo matcht Gen% met volgende waardes: Gen, Genk, Gent, Genève, Genua, …

_ is een match anything character voor één karakter. Zo matcht Gen_ met volgende waardes: Genk, Gent, … Maar niet met volgende waardes: Gen, Genève, Genua, …

NULL

NULL is het ontbreken van een waarde. Het is een onbekende. We kunnen in DML niet zomaar vergelijken met NULL. We kunnen namelijk niet zeggen dat een onbekende gelijk is aan een andere onbekende.

Hieronder een overzicht van een binary AND table met True, False en NULL waardes.

Hieronder vinden we de binary OR table met True, False en NULL waardes.

Als we willen vergelijken met NULL in queries, dan gebruiken we volgende code:

<value> IS NULL

en

<value> IS NOT NULL

JOIN

Wanneer we informatie willen ophalen uit meerdere tabellen dan gebruiken we daar een JOIN statement voor. Die syntax ziet er als volgt uit:

    SELECT { DISTINCT } expression
    FROM table
    INNER JOIN other_table ON join_condition
    { WHERE condition }

Hiermee matchen we alle data van de ene tabel met de andere tabel op de meegegeven conditie. Er bestaan drie verschillende types JOINs:

• INNER JOIN - Geeft alle resultaten die bestaan zowel in de ene als de andere tabel
• LEFT JOIN - Geeft alle resultaten die bestaan in de base tabel, ook al bestaan die niet in de tabel waarop we joinen
• RIGHT JOIN - Wordt in de praktijk zelden tot nooit gebruikt. Geeft alle resultaten die bestaan in de gejoinde tabel ook al bestaan ze niet in de base tabel.

GROUP BY

Soms willen we data aggregeren. In Basic Engineering Skills in Python werd aggregratie gebruikt om bijvoorbeeld de som van een lijst te nemen, of met funtools.reduce() een custom functie los te laten op een lijst. (Dit gaan we ook nog zien in het hoofdstuk rond NoSQL – Advanced map-reduce queries).

In RDBMS bestaan hiervoor een aantal verschillende functies. De meest courante zijn hieronder te vinden:

• MAX()
• MIN()
• COUNT()
• AVG()
• SUM()

Elke waarde die je extra selecteert in een query bovenop een aggregate function, moet in een GROUP BY clause komen. Hoe ziet dit er dan bijvoorbeeld uit?

SELECT BillingCity, SUM(Total)
FROM Invoices
GROUP BY BillingCity

Zonder GROUP BY statement krijg je ofwel een fout ofwel maar één record terug, zoals in SQLite.

HAVING

Als we willen filteren op een grouping function, dan gaat dat niet via een WHERE clause, dan krijg je namelijk een foutmelding:

SELECT BillingCity, count(*) FROM invoices
WHERE count(*) > 2
GROUP BY BillingCity

Om te filteren op een grouping function schrijven we dit in een HAVING clause die de query gebruikerlijks afsluit:

SELECT BillingCity, count(*) FROM invoices
GROUP BY BillingCity
HAVING count(*) > 2

Subqueries

Een query die we uitvoeren geeft een set van resultaten terug. Die set kunnen we opnieuw gebruiken als input voor een nieuwe query. We kunnen die set op verschillende plaatsen gebruiken als input voor een nieuwe query. Hieronder een aantal voorbeelden.

In een WHERE clause

SELECT * FROM invoice_items
WHERE invoice_items.TrackId IN (
	SELECT tracks.TrackId FROM tracks
	WHERE name LIKE '%hell%'
)

In een FROM clause

SELECT * FROM (
	SELECT tracks.TrackId, tracks.Name FROM tracks
	WHERE name LIKE '%hell%'
)

In een JOIN clause

SELECT * FROM tracks
INNER JOIN (
	SELECT tracks.TrackId, tracks.Name FROM tracks
	WHERE name LIKE '%hell%'
) hell_tracks ON hell_tracks.TrackId = tracks.TrackId

SELECT * FROM invoice_items
WHERE EXISTS (
	SELECT 1 FROM tracks
	WHERE name LIKE '%hell%' AND tracks.TrackId = invoice_items.TrackId
)

De IN clause gaat een subquery volledig ophalen om alle rijen te hebben om dan in die lijst van rijen te kunnen zoeken. Een EXISTS clause gaat een subquery maar zo lang uitvoeren tot er een resultaat gevonden is. Als de tabel uit de subquery 1000 rijen bevat en er wordt een match gevonden op rij 200, dan gaan de andere 800 niet meer geëvauleerd worden.

Data manipulatie

INSERT

Met een INSERT Statement gaan we data toevoegen in de database. We gebruiken een column listing om aan te geven welke kolommen, in welke volgorde, we van een waarde kan voorzien. Kolommen die NULL values ondersteunen mogen uit de column listing gelaten worden.

INSERT INTO Genres(Name)
VALUES('Rock')

UPDATE

Met een UPDATE statement kunnen we één of meerdere waardes in een set van data aanpassen.

UPDATE Tracks 
SET MediaTypeId = 1
WHERE AlbumId = 2

DELETE

Hiermee kunnen we een set van data verwijderen.

LET OP! Een DELETE statement zonder WHERE clause verwijdert alles uit de tabel!

DELETE FROM Genre
WHERE Name = 'Rock'

Wat is volgens jou het verschil tussen DELETE FROM zonder WHERE en DROP TABLE?