SQL-kategorier: DDL, DML, DQL, DCL och TCL

SQL-kommandon delas in i fem huvudkategorier beroende på vad de gör. Att förstå dessa kategorier hjälper dig att veta vilket verktyg du ska använda för olika uppgifter.

Innehållsförteckning

Översikt
DDL - Data Definition Language
DML - Data Manipulation Language
DQL - Data Query Language
DCL - Data Control Language
TCL - Transaction Control Language
Jämförelse och användning
Praktiska exempel
Best practices
TL;DR

Översikt

Kategori	Vad gör den?	Vanliga kommandon
DDL	Definierar struktur	CREATE, ALTER, DROP, TRUNCATE
DML	Ändrar data	INSERT, UPDATE, DELETE
DQL	Hämtar data	SELECT
DCL	Styr behörigheter	GRANT, REVOKE
TCL	Kontrollerar transaktioner	COMMIT, ROLLBACK, SAVEPOINT

Notera: Vissa källor inkluderar SELECT i DML. Här separerar vi DQL (Data Query Language) för SELECT eftersom det är så centralt och skiljer sig från de andra DML-kommandona (SELECT läser, de andra skriver).

Minnesregel:

DDL = Define (schema/struktur)
DML = Modify (ändra data - INSERT/UPDATE/DELETE)
DQL = Query (hämta data - SELECT)
DCL = Control (säkerhet/rättigheter)
TCL = Transaction (commit/rollback)

DDL - Data Definition Language

DDL (Data Definition Language) definierar strukturen i databasen - tabeller, index, vyer, constraints.

Kommandon

CREATE - Skapa struktur

Skapa databas:

CREATE DATABASE MyShop;

Skapa tabell:

CREATE TABLE Customers (
    CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Email TEXT UNIQUE NOT NULL,
    CreatedAt TEXT DEFAULT (datetime('now'))
);

Skapa index:

CREATE INDEX idx_customers_email ON Customers(Email);

Skapa vy:

CREATE VIEW ActiveCustomers AS
SELECT CustomerId, Name, Email
FROM Customers
WHERE IsActive = 1;

ALTER - Ändra struktur

Lägg till kolumn:

ALTER TABLE Customers
ADD COLUMN Phone TEXT;

Byt namn på tabell:

ALTER TABLE Customers
RENAME TO Users;

Lägg till constraint (SQLite-limitation):

-- SQLite stödjer inte ADD CONSTRAINT direkt
-- Måste återskapa tabell med ny struktur
CREATE TABLE CustomersNew (
    CustomerId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Email TEXT UNIQUE NOT NULL,
    CHECK (Email LIKE '%@%.%')  -- Ny constraint
);

INSERT INTO CustomersNew SELECT * FROM Customers;
DROP TABLE Customers;
ALTER TABLE CustomersNew RENAME TO Customers;

DROP - Ta bort struktur

Ta bort tabell:

DROP TABLE IF EXISTS OldCustomers;

Ta bort index:

DROP INDEX IF EXISTS idx_old_index;

Ta bort vy:

DROP VIEW IF EXISTS OldView;

Varning: DROP är permanent - all data försvinner!

TRUNCATE - Töm tabell

SQLite: Använd DELETE (TRUNCATE finns inte)

-- Töm tabell (behåll struktur)
DELETE FROM TempData;

-- Återställ AUTOINCREMENT
DELETE FROM sqlite_sequence WHERE name = 'TempData';

SQL Server / PostgreSQL:

TRUNCATE TABLE TempData;  -- Snabbt, återställer AUTOINCREMENT

DDL Egenskaper

✓ Auto-commit - DDL-kommandon sparas direkt (kan inte ROLLBACK i de flesta databaser) ✓ Schema-ändringar - Påverkar struktur, inte data ✓ Metadata - Sparas i systemtabeller (sqlite_master, information_schema)

Viktigt i SQLite:

DDL körs oftast UTANFÖR transaktioner (auto-commit)
ALTER TABLE har begränsningar (kan inte ta bort kolumner direkt)
Använd migrations för komplexa schema-ändringar

DML - Data Manipulation Language

DML (Data Manipulation Language) ändrar innehållet i tabellerna - lägga till, ändra, ta bort data.

Notera: Traditionellt inkluderar DML även SELECT, men vi separerar det till DQL (se nedan) eftersom SELECT är read-only medan övriga DML-kommandon skriver data.

Kommandon

INSERT - Lägga till data

Enkel insert:

INSERT INTO Customers (Name, Email)
VALUES ('Anna Andersson', 'anna@example.com');

Flera rader:

INSERT INTO Customers (Name, Email) VALUES
    ('Bengt Bengtsson', 'bengt@example.com'),
    ('Cecilia Carlsson', 'cecilia@example.com'),
    ('David Davidsson', 'david@example.com');

Från SELECT:

INSERT INTO ArchivedOrders
SELECT * FROM Orders
WHERE OrderDate < '2020-01-01';

UPDATE - Ändra data

Enkel update:

UPDATE Customers
SET Email = 'anna.new@example.com'
WHERE CustomerId = 1;

Uppdatera flera kolumner:

UPDATE Products
SET Price = Price * 1.10,
    UpdatedAt = datetime('now')
WHERE CategoryId = 5;

Med JOIN (SQLite-syntax):

UPDATE Products
SET CategoryName = (
    SELECT Name FROM Categories
    WHERE Categories.CategoryId = Products.CategoryId
);

DELETE - Ta bort data

Enkel delete:

DELETE FROM Customers
WHERE CustomerId = 999;

Med villkor:

DELETE FROM Orders
WHERE OrderDate < '2020-01-01'
  AND Status = 'Cancelled';

Ta bort allt (behåll struktur):

DELETE FROM TempData;  -- Ta bort alla rader

DML Egenskaper

✓ Transaktionell - Kan ROLLBACK (om i transaktion) ✓ Data-ändringar - Påverkar innehåll, inte struktur ✓ Logging - Loggas för ångerbarhet ✓ Skriver data - Ändrar innehållet (INSERT/UPDATE/DELETE)

Viktigt:

DML kan köras i transaktioner (BEGIN TRANSACTION, COMMIT, ROLLBACK)
INSERT/UPDATE/DELETE ändrar data
För att LÄSA data, se DQL (SELECT)

DQL - Data Query Language

DQL (Data Query Language) hämtar data från tabellerna utan att ändra något. DQL består i praktiken endast av SELECT-kommandot, men det är så centralt att det förtjänar sin egen kategori.

Varför separera DQL från DML?

SELECT skiljer sig fundamentalt:

✓ Read-only - Ändrar ALDRIG data
✓ Ingen logging - Behöver inte loggas för ROLLBACK
✓ Ingen locking (vanligtvis) - Blockerar inte skrivoperationer
✓ Mest använd - 80-90% av alla SQL-queries är SELECT

DML (INSERT/UPDATE/DELETE) däremot:

✗ Skriver data
✗ Kräver logging
✗ Låser rader
✗ Kan ROLLBACK

SELECT - Hämta data

Enkel select:

SELECT Name, Email FROM Customers;

Alla kolumner:

SELECT * FROM Customers;

Med villkor (WHERE):

SELECT * FROM Customers
WHERE IsActive = 1
ORDER BY Name;

Med JOIN:

SELECT c.Name, o.OrderDate, o.Total
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.Total > 1000;

Med aggregering:

SELECT
    CategoryId,
    COUNT(*) AS ProductCount,
    AVG(Price) AS AvgPrice,
    MAX(Price) AS MaxPrice
FROM Products
GROUP BY CategoryId
HAVING COUNT(*) > 5;

Med subquery:

SELECT Name FROM Customers
WHERE CustomerId IN (
    SELECT CustomerId FROM Orders
    WHERE OrderDate > '2024-01-01'
);

Med UNION:

SELECT Name, 'Customer' AS Type FROM Customers
UNION
SELECT Name, 'Supplier' AS Type FROM Suppliers;

Med CTE (Common Table Expression):

WITH HighValueCustomers AS (
    SELECT CustomerId, SUM(Total) AS TotalSpent
    FROM Orders
    GROUP BY CustomerId
    HAVING SUM(Total) > 10000
)
SELECT c.Name, h.TotalSpent
FROM Customers c
JOIN HighValueCustomers h ON c.CustomerId = h.CustomerId;

DQL Egenskaper

✓ Read-only - Ändrar aldrig data ✓ Transaktionsoberoende - Behöver inte COMMIT ✓ Ingen locking (default) - Blockerar inte andra queries ✓ Optimerbar - Query planner kan optimera ✓ Cacheable - Resultat kan cachas

Viktigt för SELECT:

SELECT returnerar ResultSet (rader/kolumner)
Kan vara långsam på stora tabeller (använd index!)
ORDER BY sorterar (extra kostnad)
DISTINCT tar bort dubbletter (extra kostnad)
COUNT/SUM/AVG kräver scan av alla rader (långsamt utan index)

Prestanda-tips för DQL

DÅLIGT ❌:

-- Hämtar ALLA kolumner (även oanvända)
SELECT * FROM Products;

-- Ingen WHERE = full table scan
SELECT Name FROM Products;

-- COUNT(*) på stor tabell utan index
SELECT COUNT(*) FROM LargeTable;

BRA ✓:

-- Hämta bara nödvändiga kolumner
SELECT ProductId, Name FROM Products;

-- Använd WHERE för att filtrera tidigt
SELECT Name FROM Products WHERE CategoryId = 5;

-- Använd index för COUNT
SELECT COUNT(*) FROM Products WHERE CategoryId = 5;  -- Om index på CategoryId

DCL - Data Control Language

DCL (Data Control Language) styr behörigheter och säkerhet - vem får göra vad.

Kommandon

GRANT - Ge rättigheter

SQLite: SQLite har INGEN inbyggd säkerhet (fil-baserad). DCL fungerar inte!

SQL Server / PostgreSQL / MySQL:

-- Ge läsrättigheter
GRANT SELECT ON Customers TO user_readonly;

-- Ge alla rättigheter på tabell
GRANT ALL PRIVILEGES ON Orders TO user_admin;

-- Ge specifika rättigheter
GRANT SELECT, INSERT, UPDATE ON Products TO user_sales;

REVOKE - Ta bort rättigheter

-- Ta bort läsrättigheter
REVOKE SELECT ON Customers FROM user_readonly;

-- Ta bort alla rättigheter
REVOKE ALL PRIVILEGES ON Orders FROM user_admin;

DCL i SQLite

SQLite har INGEN DCL!

Hela databasen är en fil
Säkerhet hanteras på fil-nivå (OS permissions)
Använd filsystem-rättigheter (chmod, ACL) istället

Exempel (Linux/Mac):

# Endast owner kan läsa/skriva
chmod 600 mydb.db

# Alla kan läsa, endast owner kan skriva
chmod 644 mydb.db

Exempel (Windows):

Högerklicka fil → Properties → Security
Sätt läs/skriv-rättigheter per användare

DCL i produktionsdatabaser

Best practices:

-- Läsanvändare (applikation)
CREATE USER app_readonly WITH PASSWORD 'secure_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- Skrivanvändare (applikation)
CREATE USER app_writer WITH PASSWORD 'secure_pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;

-- Admin (DBA)
CREATE USER dba_admin WITH PASSWORD 'secure_pass';
GRANT ALL PRIVILEGES ON DATABASE mydb TO dba_admin;

Princip om minsta privilegium: Ge endast de rättigheter som behövs!

TCL - Transaction Control Language

TCL (Transaction Control Language) kontrollerar transaktioner - commit, rollback, savepoints.

Kommandon

BEGIN TRANSACTION - Starta transaktion

BEGIN TRANSACTION;
-- eller kort
BEGIN;

COMMIT - Spara ändringar permanent

BEGIN TRANSACTION;

INSERT INTO Accounts (AccountId, Balance) VALUES (1, 1000);
INSERT INTO Accounts (AccountId, Balance) VALUES (2, 2000);

COMMIT;  -- Ändringar sparas permanent

ROLLBACK - Ångra ändringar

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

-- Något gick fel!
ROLLBACK;  -- Ångra ALLA ändringar sedan BEGIN

SAVEPOINT - Skapa återställningspunkt

SQLite stödjer SAVEPOINT:

BEGIN TRANSACTION;

INSERT INTO Orders (CustomerId, Total) VALUES (1, 500);
SAVEPOINT order_created;

INSERT INTO OrderLines (OrderId, ProductId, Qty) VALUES (1, 101, 2);
SAVEPOINT lines_added;

INSERT INTO OrderLines (OrderId, ProductId, Qty) VALUES (1, 102, 1);

-- Något gick fel med sista raden!
ROLLBACK TO SAVEPOINT lines_added;  -- Ångra bara sista INSERT

-- Fortsätt arbeta...
INSERT INTO OrderLines (OrderId, ProductId, Qty) VALUES (1, 103, 3);

COMMIT;  -- Spara allt utom den rad vi rullade tillbaka

RELEASE SAVEPOINT:

RELEASE SAVEPOINT order_created;  -- Släpp savepoint (kan inte längre rollback till den)

Praktiskt exempel: Banköverföring

BEGIN TRANSACTION;

-- Kontrollera saldo
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- 1000 kr

-- Dra från första kontot
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;

-- Kontrollera att det gick bra
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- 500 kr

-- Lägg till på andra kontot
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

-- Allt OK? Spara!
COMMIT;

Med felhantering:

BEGIN TRANSACTION;

-- Savepoint innan kritisk operation
SAVEPOINT before_transfer;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;

-- Kontrollera att saldo inte blev negativt
SELECT Balance FROM Accounts WHERE AccountId = 1;

-- Om negativt: ROLLBACK TO SAVEPOINT before_transfer
-- Om OK: fortsätt

UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

COMMIT;

TCL Egenskaper

✓ ACID-garantier:

Atomicity - Allt eller inget
Consistency - Data förblir giltigt
Isolation - Transaktioner påverkar inte varandra
Durability - Committed data överlever krascher

Viktigt:

Transaktioner låser rader (kan orsaka deadlocks)
Håll transaktioner korta (minimera lock-tid)
COMMIT sparar, ROLLBACK ångrar
SQLite: En skrivare i taget (databas-nivå lås)

Jämförelse och användning

När använder jag vilken?

Uppgift	Kategori	Kommando
Skapa ny tabell	DDL	CREATE TABLE
Lägga till kolumn	DDL	ALTER TABLE ADD
Lägga till data	DML	INSERT
Hämta data	DQL	SELECT
Ändra data	DML	UPDATE
Ta bort data (behåll tabell)	DML	DELETE
Ta bort tabell (allt)	DDL	DROP TABLE
Räkna antal rader	DQL	SELECT COUNT(*)
JOIN flera tabeller	DQL	SELECT … JOIN
Ge användare läsrättigheter	DCL	GRANT SELECT
Spara ändringar	TCL	COMMIT
Ångra ändringar	TCL	ROLLBACK

Interaktion mellan kategorier

-- DDL: Skapa struktur
CREATE TABLE Products (
    ProductId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Price REAL NOT NULL
);

-- DML: Lägg till data
INSERT INTO Products (Name, Price) VALUES ('Laptop', 9999);

-- DQL: Hämta data
SELECT * FROM Products;

-- TCL: Starta transaktion
BEGIN TRANSACTION;

-- DML: Ändra data
UPDATE Products SET Price = Price * 1.10;

-- DQL: Kontrollera ändring
SELECT Name, Price FROM Products;

-- TCL: Spara eller ångra
COMMIT;  -- eller ROLLBACK

-- DCL: Ge rättigheter (ej SQLite)
GRANT SELECT ON Products TO user_sales;

-- DDL: Ändra struktur
ALTER TABLE Products ADD COLUMN Stock INTEGER DEFAULT 0;

-- DQL: Hämta uppdaterat schema
SELECT * FROM Products;

Praktiska exempel

Exempel 1: Skapa och fylla databas

-- DDL: Skapa schema
CREATE TABLE Customers (
    CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Email TEXT UNIQUE NOT NULL
);

CREATE TABLE Orders (
    OrderId INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerId INTEGER NOT NULL,
    Total REAL NOT NULL,
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

-- DML: Lägg till testdata
INSERT INTO Customers (Name, Email) VALUES
    ('Anna', 'anna@example.com'),
    ('Bengt', 'bengt@example.com');

INSERT INTO Orders (CustomerId, Total) VALUES
    (1, 500),
    (1, 750),
    (2, 1200);

-- DML: Hämta resultat
SELECT
    c.Name,
    COUNT(o.OrderId) AS OrderCount,
    SUM(o.Total) AS TotalSpent
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId;

Exempel 2: Migrera schema med data

-- DDL: Skapa ny tabell med bättre struktur
CREATE TABLE CustomersNew (
    CustomerId INTEGER PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Email TEXT UNIQUE NOT NULL
);

-- DML: Kopiera data (dela upp Name)
INSERT INTO CustomersNew (CustomerId, FirstName, LastName, Email)
SELECT
    CustomerId,
    substr(Name, 1, instr(Name, ' ') - 1) AS FirstName,
    substr(Name, instr(Name, ' ') + 1) AS LastName,
    Email
FROM Customers;

-- DDL: Byt ut gamla tabellen
DROP TABLE Customers;
ALTER TABLE CustomersNew RENAME TO Customers;

Exempel 3: Säker transaktion med felhantering

-- TCL: Starta transaktion
BEGIN TRANSACTION;

-- DML: Kritisk operation
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountId = 1;

-- Kontrollera resultat
SELECT Balance FROM Accounts WHERE AccountId = 1;
-- Om negativt → ROLLBACK
-- Om OK → fortsätt

UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountId = 2;

-- TCL: Spara
COMMIT;

Best practices

DDL Best Practices

✓ Versionshantera schema:

-- migrations/001_create_customers.sql
CREATE TABLE Customers (...);

-- migrations/002_add_phone.sql
ALTER TABLE Customers ADD COLUMN Phone TEXT;

✓ Använd IF EXISTS:

DROP TABLE IF EXISTS OldTable;
CREATE TABLE IF NOT EXISTS NewTable (...);

✓ Backup innan stora ändringar:

# SQLite backup
sqlite3 mydb.db ".backup backup_$(date +%Y%m%d).db"

DML Best Practices

✓ Alltid använd WHERE i UPDATE/DELETE:

-- DÅLIGT ❌
UPDATE Products SET Price = 100;  -- Uppdaterar ALLA!

-- BRA ✓
UPDATE Products SET Price = 100 WHERE ProductId = 5;

✓ Testa SELECT innan DELETE:

-- Kör först för att se vad som tas bort
SELECT * FROM Orders WHERE Status = 'Cancelled';

-- Sen DELETE
DELETE FROM Orders WHERE Status = 'Cancelled';

✓ Använd LIMIT vid testning:

-- Testa på en rad först
UPDATE Products SET Price = Price * 1.10 LIMIT 1;

TCL Best Practices

✓ Håll transaktioner korta:

-- DÅLIGT ❌
BEGIN;
SELECT * FROM BigTable;  -- Långsam query
-- Användaren tänker...
UPDATE ...;
COMMIT;  -- Låser länge!

-- BRA ✓
-- Läs UTANFÖR transaktion
SELECT * FROM BigTable;

-- Kort transaktion för ändring
BEGIN;
UPDATE ...;
COMMIT;

✓ Explicit COMMIT/ROLLBACK:

-- DÅLIGT ❌
BEGIN;
INSERT ...;
-- Glömde COMMIT!

-- BRA ✓
BEGIN;
try {
    INSERT ...;
    COMMIT;
}
catch {
    ROLLBACK;
    throw;
}

DCL Best Practices

✓ Minsta privilegium:

-- DÅLIGT ❌
GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;

-- BRA ✓
GRANT SELECT, INSERT, UPDATE ON customers TO app_user;

✓ Separata användare för olika roller:

CREATE USER app_readonly;  -- SELECT
CREATE USER app_writer;    -- SELECT, INSERT, UPDATE, DELETE
CREATE USER dba_admin;     -- ALL

TL;DR

SQL-kategorier:

Kategori	Syfte	Kommandon
DDL	Definiera struktur	CREATE, ALTER, DROP, TRUNCATE
DML	Ändra data	INSERT, UPDATE, DELETE
DQL	Hämta data	SELECT
DCL	Styra behörigheter	GRANT, REVOKE
TCL	Kontrollera transaktioner	COMMIT, ROLLBACK, SAVEPOINT

Minnesregler:

DDL = Schema/struktur (tabeller, index)
DML = Modify data (INSERT/UPDATE/DELETE - SKRIVER)
DQL = Query data (SELECT - LÄSER)
DCL = Säkerhet/rättigheter (ej SQLite!)
TCL = Transaktioner (commit/rollback)

Viktiga skillnader:

DDL = Auto-commit (kan inte ROLLBACK)
DML = Transaktionell, skriver data (kan ROLLBACK)
DQL = Read-only, ingen locking (kan inte ROLLBACK)
DCL = Ej i SQLite (fil-baserad säkerhet)
TCL = ACID-garantier

Varför separera DQL från DML?

SELECT är read-only (ändrar inget)
SELECT är 80-90% av alla queries
SELECT kräver ingen logging/locking
INSERT/UPDATE/DELETE skriver data

Best practices:

✓ Versionshantera DDL (migrations)
✓ Alltid WHERE i UPDATE/DELETE
✓ Testa SELECT innan DELETE
✓ Håll transaktioner korta
✓ Explicit COMMIT/ROLLBACK
✓ Minsta privilegium (DCL)

Exempel:

-- DDL: Skapa struktur
CREATE TABLE Products (ProductId INTEGER PRIMARY KEY, Name TEXT);

-- DML: Lägg till data
INSERT INTO Products (Name) VALUES ('Laptop');

-- DQL: Hämta data
SELECT * FROM Products;

-- TCL: Transaktion
BEGIN;
UPDATE Products SET Name = 'Gaming Laptop' WHERE ProductId = 1;  -- DML
SELECT Name FROM Products WHERE ProductId = 1;  -- DQL (kontrollera)
COMMIT;

-- DDL: Ta bort
DROP TABLE Products;

Kom ihåg:

DDL = Struktur (CREATE/ALTER/DROP)
DML = Ändra (INSERT/UPDATE/DELETE)
DQL = Läsa (SELECT)
DCL = Rättigheter (GRANT/REVOKE)
TCL = Transaktioner (COMMIT/ROLLBACK)

Förstå kategorierna → Använd rätt verktyg för rätt uppgift! 🎯

title: SQL — kategorier och innehåll

Vanliga kategorier i kursen:

Basics (DDL, DML)
Joins & Aggregation
Index & Performance
Transactions & Concurrency
Security & Backups

Exempel (skapande av kategori-tabell)

CREATE TABLE Categories (
    CategoryId INTEGER PRIMARY KEY,
    CategoryName TEXT NOT NULL UNIQUE,
    Description TEXT
);

INSERT INTO Categories (CategoryName, Description) VALUES ('Joins', 'Joins och relationer');