DFU 09

CRUD

CRUD

DML, DQL

SQL Sproglig definition

• Clause, et sætningselement i en SQL erklæring
  • e.g. SELECT, FROM, WHERE, INSERT INTO
• Statement, en komplet SQL kommando, som udfører en handling på databasen
  • e.g. en INSERT, UPDATE, DELETE eller SELECT declaration
• Query, en SQL erklæring, som henter data fra databasen (ofte en SELECT erklæring)

DBML for videre eksempler:

Table Users {
  id integer [pk, increment]
  name varchar(100)
  email varchar(100) [unique]
  created_at timestamp
}

Create

INSERT

--

DML (Data Manipulation Language)

Create eller INSERT

INSERT uden field definition:

INSERT INTO tableName 
VALUES (value1, value2, value3, ...);

INSERT INTO Users
VALUES (null, 'John Doe', 'john@doe.com', CURRENT_TIMESTAMP);

• bemærk hvordan alle data inkl. PK/AI feltet skal med

Create eller INSERT

INSERT med field definition:

INSERT INTO tableName (field2, field3, ...)
VALUES (value2, value3, ...);

INSERT INTO Users (name, email, created_at)
VALUES ('Jane Doe', 'john@doe.com', CURRENT_TIMESTAMP);

• bemærk hvordan PK/AI feltet nu kan udelades og databasen selv sætter værdien

Fuld dokumentation af INSERT

• PostgreSQL:
  https://www.postgresql.org/docs/current/sql-insert.html

• MSSQL:
  https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver17

• MySQL:
  https://dev.mysql.com/doc/refman/8.4/en/insert.html

Read

SELECT

--

DQL (Data Query Language)

SQL Clause order

Eksempel på en kompleks SELECT query

SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10 OFFSET 0;

• Denne query henter de top 10 aktive brugere med mere end 5 ordrer, sorteret efter antal ordrer i faldende rækkefølge.
• Group og Having clauses bruges til at aggregere data baseret på bruger-id og navn.

Read eller SELECT

SELECT field1, field2, ...
FROM tableName

SELECT id, name, email
FROM Users;

• ALDRIG brug SELECT * i produktion
• Vælg altid specifikke felter for bedre performance og klarhed

Select where

SELECT field1, field2, ...
FROM tableName
WHERE condition;

SELECT id, name, email
FROM Users
WHERE id = 1;

WHERE + AND + OR

SELECT id, name, email
FROM Users
WHERE name = 'John' OR 'Jon' AND id > 5;

• Kombiner flere betingelser med AND og OR

• Husk at AND har højere præcedens end OR

• Brug parenteser for at styre rækkefølgen af evaluering

- E.g. hvis du ønsker `OR` til at have højere præcedens end `AND`
<!-- .element class="fragment" -->

• || og && kan også bruges i stedet for OR og AND i mange SQL dialekter

- E.g. PostgreSQL, MySQL, MSSQL
<!-- .element class="fragment" -->

WHERE IS NULL

SELECT id, name, email
FROM Users
WHERE email IS NULL;

• IS NULL bruges til at tjekke for NULL værdier

• IS NOT NULL bruges til at tjekke for ikke-NULL værdier

WHERE LIKE

SELECT id, name, email
FROM Users
WHERE name LIKE 'J%' AND email IS NOT NULL;

• LIKE operatoren bruges til mønstermatchning i strenge

• Wildcard % repræsenterer nul eller flere tegn

• Wildcard _ repræsenterer et enkelt tegn

`WHERE IN```

SELECT id, name, email
FROM Users
WHERE id IN (1, 2, 3, 4, 5);

• IN operatoren bruges til at tjekke om en værdi findes i en liste af værdier

• Kan gøre koden mere læsbar end flere OR betingelser

• Ofte ret praktisk ved filtrering mod et sæt kendte værdier

WHERE BETWEEN

SELECT id, name, email
FROM Users
WHERE created_at BETWEEN '2024-01-01' AND '2024-06-30'
OR id BETWEEN 10 AND 20;

• BETWEEN operatoren bruges til at filtrere værdier inden for et bestemt interval

• Inkluderer både start- og slutværdierne

• Kan bruges med både numeriske og dato værdier

WHERE NOT

SELECT id, name, email
FROM Users
WHERE NOT (email LIKE '%@ucl.dk');

• NOT operatoren bruges til at negere en betingelse

• Kan bruges til at ekskludere bestemte mønstre eller værdier

Subqueries

Hvad er en Subquery?

• En subquery er en SQL query indlejret inden i en anden SQL query

• Bruges til at udføre komplekse forespørgsler, hvor resultatet af den indre query bruges i den ydre query

• Subqueries kan placeres i SELECT, FROM, eller WHERE clauses

DBML for Subquery eksempler:

Table Orders {
  id integer [pk, increment]
  user_id integer [ref: > Users.id]
  total_amount decimal
  created_at timestamp
}

Simpelt eksempel på Subquery

SELECT id, name, email
FROM Users
WHERE id IN (
    SELECT user_id
    FROM Orders
    WHERE total_amount > 100
);

• Denne query henter alle brugere, der har ordrer med en total amount større end 100

• Den indre query returnerer en liste af user_ids, som den ydre query bruger til at filtrere Users tabellen

• Bemærk hvordan den indre query trækker user_id (FK) fra Orders tabellen for at matche med id (PK) i Users tabellen

WHERE EXISTS

SELECT id, name, email
FROM Users u
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.user_id = u.id
);

• EXISTS operatoren bruges til at tjekke om en subquery returnerer nogen rækker

• Hvis subquery returnerer mindst én række, evalueres EXISTS til sand (true)

• I dette eksempel henter vi alle brugere, der har mindst én ordre i Orders tabellen

• Den indre query tjekker for eksistensen af ordrer for hver bruger ved at matche user_id med id

Where clauses og Subqueries

• Subqueries kan også bruges i WHERE clauses med forskellige comparison operators

• Alle typer af WHERE clauses kan kombineres med subqueries

• Alle typer af WHERE clauses kan bruges inde i subqueries

Fuld dokumentation af subqueries

• PostgreSQL:
  https://www.postgresql.org/docs/current/functions-subquery.html

• MSSQL:
  https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver17

• MySQL:
  https://dev.mysql.com/doc/refman/8.4/en/subqueries.html

GROUP BY og HAVING

Hvad er GROUP BY

• GROUP BY clause bruges til at gruppere rækker, der har samme værdier i specificerede kolonner

• Ofte brugt sammen med aggregatfunktioner som COUNT(), SUM(), AVG(), MAX(), og MIN()

• Tillader opsummering af data baseret på grupper af rækker

Eksempel på GROUP BY

SELECT user_id, COUNT(*) AS order_count
FROM Orders
GROUP BY user_id;

Eksempel return:

Hvad er HAVING

• HAVING clause bruges til at filtrere grupper efter at de er blevet dannet af GROUP BY

• Ligner WHERE clause, men opererer på grupper i stedet for individuelle rækker

• Bruges ofte til at specificere betingelser på aggregatfunktioner

Eksempel på HAVING

SELECT user_id, COUNT(*) AS order_count
FROM Orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Eksempel return:

• Denne query henter kun de brugere, der har mere end 5 ordrer

Where vs Having

Hvorfor ikke WHERE COUNT(*) > 5?

SELECT user_id, COUNT(*) AS order_count
FROM Orders
WHERE COUNT(*) > 5
GROUP BY user_id;

• Dette vil resultere i en SQL fejl, fordi WHERE ikke kan bruges med aggregatfunktioner som COUNT()

• Aggregatfunktioner beregnes efter WHERE clause er anvendt, så de er ikke tilgængelige på det tidspunkt

• Derfor skal HAVING bruges til at filtrere baseret på aggregatfunktioner efter gruppering

Joins

Hvad er et Join?

• Et Join bruges til at kombinere rækker fra to eller flere tabeller baseret på en relateret kolonne mellem dem

• Joins er essentielle for at hente relaterede data spredt over flere tabeller i en relationel database

• Forskellige typer af Joins bestemmer hvordan rækker matches og hvilke rækker der inkluderes i resultatet

Typer af Joins

• INNER JOIN: Returnerer kun de rækker, hvor der er et match i begge tabeller

• LEFT JOIN (eller LEFT OUTER JOIN): Returnerer alle rækker fra venstre tabel og de matchende rækker fra højre tabel; NULL for ikke-matchende rækker

• RIGHT JOIN (eller RIGHT OUTER JOIN): Returnerer alle rækker fra højre tabel og de matchende rækker fra venstre tabel; NULL for ikke-matchende rækker

• FULL JOIN (eller FULL OUTER JOIN): Returnerer rækker, når der er et match i en af tabellerne; NULL for ikke-matchende rækker i begge tabeller

Eksempel på INNER JOIN

SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM Users u
INNER JOIN Orders o ON u.id = o.user_id;

• Henter alle brugere sammen med deres ordrer, kun hvis der er et match mellem Users og Orders

Eksempel på LEFT JOIN

SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id;

• Henter alle brugere sammen med deres ordrer, inklusive brugere uden ordrer

Eksempel på RIGHT JOIN

SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM Users u
RIGHT JOIN Orders o ON u.id = o.user_id;

• Henter alle ordrer sammen med deres brugere, inklusive ordrer uden tilknyttede brugere

Eksempel på FULL JOIN

SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM Users u
FULL JOIN Orders o ON u.id = o.user_id;

• Henter alle brugere og ordrer, inklusive brugere uden ordrer og ordrer uden tilknyttede brugere

Update

UPDATE

UPDATE

UPDATE tableName
SET field1 = value1, field2 = value2, ...
WHERE condition;

UPDATE Users
SET email = 'real@mail.com'
WHERE id = 1;

• Opdaterer email for brugeren med id 1

Hvad kan vi med update

• Alle de WHERE clauses vi har set tidligere kan bruges med UPDATE

• Vi kan opdatere flere felter på én gang

• Vi kan opdatere flere rækker på én gang ved at bruge en WHERE clause, der matcher flere rækker

• Sørg ALTID for at have en WHERE clause, medmindre du vil opdatere alle rækker i tabellen

Delete

DELETE

DELETE

DELETE FROM tableName
WHERE condition;

DELETE FROM Users
WHERE id = 1;

• Sletter brugeren med id 1

Hvad kan vi med DELETE

• Alle de WHERE clauses vi har set tidligere kan bruges med DELETE

• Vi kan slette flere rækker på én gang ved at bruge en WHERE clause, der matcher flere rækker

• Sørg ALTID for at have en WHERE clause, medmindre du vil slette alle rækker i tabellen

Vigtigt

• SELECT kan bruges i VIEWS, STORED PROCEDURES, og FUNCTIONS

• INSERT, UPDATE, og DELETE kan også bruges i STORED PROCEDURES og FUNCTION

• Vær altid forsigtig med UPDATE og DELETE uden WHERE clauses, da de påvirker alle rækker i tabellen

• Husk transactions

Tutorials i SQL CRUD

https://sqlbolt.com/