reveal-md

# 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:

```dbml
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:

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

```sql
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:

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

```sql
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:<br>[https://www.postgresql.org/docs/current/sql-insert.html](https://www.postgresql.org/docs/current/sql-insert.html)

- MSSQL:<br>[https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver17](https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver17)

- MySQL:<br>[https://dev.mysql.com/doc/refman/8.4/en/insert.html](https://dev.mysql.com/doc/refman/8.4/en/insert.html)

---

## Read

`SELECT`

**DQL** (Data Query Language)

----

### SQL Clause order

- `SELECT` (required)
- `FROM` (required)
- `JOIN` (optional, multiple)
- `WHERE` (optional)
- `GROUP BY` (optional)
- `HAVING` (optional)
- `ORDER BY` (optional)
- `LIMIT` / `OFFSET` (optional)

</div>
<div class="top">

- Rækkefølgen af SQL clauses er vigtig og skal følges

- Nogle clauses er valgfrie afhængig af konteksten

- Nogle clauses kan forekomme flere gange (f.eks. JOIN)

</div>
</div>

----

#### Eksempel på en kompleks `SELECT` query

```sql
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`

```sql
SELECT field1, field2, ...
FROM tableName
```

```sql
SELECT id, name, email
FROM Users;
```

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

----

### Select where

- `WHERE` clause for filtrering

- Bruges til at specificere betingelser for hvilke rækker der skal returneres

- Bruger comparison operators som i andre sprog<br>`=`, `!=`, `<`, `>`, `<=`, `>=`

- Multiple operators:<br>`AND`, `OR`, `NOT`, samt `||`, `&&`

</div>
<div>

```sql [1-3|5-7]
SELECT field1, field2, ...
FROM tableName
WHERE condition;

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

</div>
</div>

----

### `WHERE` + `AND` + `OR`

```sql []
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`

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

    - E.g. PostgreSQL, MySQL, MSSQL

----

### `WHERE IS NULL`

```sql []
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`

```sql []
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```

```sql []
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`

```sql []
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`

```sql []
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:

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

----

### Simpelt eksempel på Subquery

```sql []
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_id`s, 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`

```sql []
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:<br>[https://www.postgresql.org/docs/current/functions-subquery.html](https://www.postgresql.org/docs/current/functions-subquery.html)

- MSSQL:<br>[https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver17](https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver17)

- MySQL:<br>[https://dev.mysql.com/doc/refman/8.4/en/subqueries.html](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`

```sql []
SELECT user_id, COUNT(*) AS order_count
FROM Orders
GROUP BY user_id;
```

Eksempel return:

| user_id | order_count |
| :------ | :---------- |
| 1       | 5           |
| 2       | 3           |
| 3       | 8           |

----

### 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`

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

Eksempel return:
| user_id | order_count |
| :------ | :---------- |
| 3       | 8           |

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

----

### Where vs Having

Where

- Filtrerer individuelle rækker før gruppering
- Anvendes til betingelser på ikke-aggregat felter
- Eksempel: `WHERE user_id = 1`

</div>
<div>

Having

- Filtrerer grupper efter gruppering
- Anvendes til betingelser på aggregatfunktioner
- Eksempel : `HAVING COUNT(*) > 5`

</div>
</div>

----

#### Hvorfor ikke `WHERE COUNT(*) > 5`?

```sql []
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`

```sql []
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`

| id | name      | order_id | total_amount |
| -- | --------- | -------- | ------------- |
| 1  | John Doe  | 101      | 250.00       |
| 2  | Jane Doe  | 102      | 150.00       |
| 1  | John Doe  | 103      | 300.00       |

----

### Eksempel på `LEFT JOIN`

```sql []
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

| id | name      | order_id | total_amount |
| -- | --------- | -------- | ------------- |
| 1  | John Doe  | 101      | 250.00       |
| 2  | Jane Doe  | 102      | 150.00       |
| 1  | John Doe  | 103      | 300.00       |
| 3  | Alice     | NULL     | NULL         |

----

### Eksempel på `RIGHT JOIN`

```sql []
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

| id | name      | order_id | total_amount |
| -- | --------- | -------- | ------------- |
| 1  | John Doe  | 101      | 250.00       |
| 2  | Jane Doe  | 102      | 150.00       |
| 1  | John Doe  | 103      | 300.00       |
| NULL | NULL     | 104      | 200.00       |

----

### Eksempel på `FULL JOIN`

```sql []
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

| id   | name      | order_id | total_amount |
| ---- | --------- | -------- | ------------- |
| 1    | John Doe  | 101      | 250.00       |
| 2    | Jane Doe  | 102      | 150.00       |
| 1    | John Doe  | 103      | 300.00       |
| 3    | Alice     | NULL     | NULL         |
| NULL | NULL      | 104      | 200.00       |

---

## Update

`UPDATE`

----

### `UPDATE`

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

```sql
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`

```sql
DELETE FROM tableName
WHERE condition;
```

```sql
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/](https://sqlbolt.com/)