Prof. Dr.-Ing. Johannes Schildgen
johannes.schildgen@oth-regensburg.de

Datenbanken

Kapitel 4: SQL

2020-11-24

SQL...

• ... ist der Nachfolger von SEQUEL (Structured English Query Language),
• ... wurde in den 1970er-Jahren entworfen,
• ... ist ein eigenständiger Begriff,
• ... wird manchmal als Abkürzung für Structured Query Language gesehen,
• ... ist eine standardisierte Anfragesprache für strukturierte Datenbanken,
• ... ist größtenteils unabhängig vom verwendeten relationalen DBMS (RDBMS).

Populäre RDBMS

Quelle: db-engines.com/de/ranking

1. Oracle
2. MySQL
3. Microsoft SQL Server
4. PostgreSQL ← verwenden wir im Rahmen dieser Vorlesung
5. IBM DB2
6. SQLite
7. MariaDB

PostgreSQL

• "The world's most advanced open source database"
• Einfach zu installieren, viele Features, Transaktionen, gute Dokumentation, ...
• SQL-Clients: psql, pgAdmin3, pgAdmin4

DBeaver

Sprachkomponenten

DDL: Data Definition Language

• Definition des Datenbankschemas (Metadaten)
• CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE VIEW, ...

DML: Data Manipulation Language

• Einfügen, Ändern, Löschen und Lesen von Daten
• INSERT, UPDATE, DELETE, SELECT, ...

DCL: Data Control Language

• Benutzer-, Rollen- und Rechteverwaltung
• CREATE USER, CREATE ROLE, GRANT, REVOKE, ...

SQL ausprobieren

SQL Island

• Lernspiel, welches keine Vorkenntnisse voraussetzt.
• sql-island.de

SQL Fiddle, DB Fiddle, Rextester

• Tabellen erstellen, befüllen und abfragen im Browser
• sqlfiddle.com, dbfiddle.uk, rextester.com

Sprachelemente

SELECT email FROM kunden WHERE name = 'Ute';

SQL-Schlüsselworte (Keywords)

• Groß-/Kleinschreibung egal
• SELECT, FROM, WHERE, GROUP, BY, INSERT, CREATE, USER, AS, ...

Identifikatoren (Namen von Tabellen, Spalten, etc.)

• Reguläre: case-insensitive; Begrenzte: case-sensitive
• Beispiele: email, kunden, name, "user", "Kunden", "A B"

Literale (Werte eines Datentyps)

• Groß-/Kleinschreibung nicht egal!
• Beispiele: 'Ute', 'Otto''s Imbiss', 5, 2.99, NULL, '2020-11-18'

Kommentare

SELECT * FROM t -- Ein Doppelminus leitet einen Kommentar ein

SELECT * FROM t
--WHERE x = 5

SELECT kundennummer /*, email*/ FROM kunden

DDL: CREATE, ALTER, DROP

Anlegen, Verändern und Entfernen von Datenbankobjekten (Tabellen, Sichten, Funktionen, ...)

CREATE TABLE personen (pid INT, name VARCHAR(500), geboren DATE);

CREATE TABLE

• Name der zu erstellenden Tabelle
• Spalten in Klammern, Komma-getrennt
• Zu jeder Spalte: Name, Datentyp, evtl. Spaltenoptionen

Datentypen

CREATE TABLE - Spaltenoptionen

CREATE TABLE kunden (
 kundennummer INT PRIMARY KEY, name VARCHAR(100) NOT NULL, 
 email VARCHAR(500) CHECK (email LIKE '%@%') UNIQUE, 
 passwort CHAR(32), land VARCHAR(100) DEFAULT 'Deutschland', 
 geworben_von INT REFERENCES kunden(kundennummer));

Constraints (Integritätsbedingungen):

• PRIMARY KEY: Primärschlüssel
• NOT NULL: Verbot von NULL-Werten
• CHECK: Bedingung für akzeptierte Werte
• UNIQUE: Eindeutigkeit
• DEFAULT: Standardwert
• REFERENCES: Fremdschlüsselreferenz

NULL / NOT NULL

NULL (Standard)

CREATE TABLE kunden (/* ... */, name VARCHAR(100) NULL)

NULL-Werte sind in der Spalte erlaubt.

NOT NULL

CREATE TABLE kunden (/* ... */, name VARCHAR(100) NOT NULL)

NULL-Werte sind in der Spalte verboten.

UNIQUE-Constraint

CREATE TABLE kunden (/* ... */, email VARCHAR(500) UNIQUE)

oder:

CREATE TABLE kunden (/* .... */ email VARCHAR(500), 
                     UNIQUE(email))

Primärschlüssel-Constraint

CREATE TABLE kunden (kundennummer INT PRIMARY KEY, ...)

oder:

CREATE TABLE kunden (kundennummer INT, ..., 
                     PRIMARY KEY(kundennummer))

Primärschlüssel sind UNIQUE und NOT NULL.

Fremdschlüssel-Constraints

CREATE TABLE kunden (/* ... */,
 geworben_von INT REFERENCES kunden(kundennummer));

oder:

CREATE TABLE kunden (/* ... */,
 geworben_von INT,
 FOREIGN KEY(geworben_von) REFERENCES kunden(kundennummer));

Zusammengesetzte Primär-/Fremdschlüssel

CREATE TABLE bewertungen (
 kundennummer INT REFERENCES kunden(kundennummer),
 produktnummer INT REFERENCES produkte(produktnummer),
 sterne INT DEFAULT 5 CHECK(sterne BETWEEN 1 AND 5),
 bewertungstext VARCHAR(100000),
 PRIMARY KEY(kundennummer, produktnummer));

CREATE TABLE bewertungslikes (
 liker INT REFERENCES kunden(kundennummer),
 kundennummer INT, produktnummer INT,
 PRIMARY KEY(liker, kundennummer, produktnummer),
 FOREIGN KEY(kundennummer, produktnummer) 
  REFERENCES bewertungen(kundennummer, produktnummer));

Referentielle Aktionen

CREATE TABLE produkte (
 produktnummer INT PRIMARY KEY, 
 bezeichnung VARCHAR(100) NOT NULL, preis DECIMAL(9,2), 
 hersteller VARCHAR(50) REFERENCES hersteller(firma) 
                        ON DELETE SET NULL ON UPDATE CASCADE);
                                

Was soll passieren, wenn ein Hersteller gelöscht wird, von dem es noch Produkte gibt? (Analog dazu: Was passiert, wenn man ihn umbenennt? - ON UPDATE)

• ON DELETE CASCADE: Alle Produkte auch löschen
• ON DELETE SET NULL / SET DEFAULT: NULL / Default-Wert setzen
• ON DELETE RESTRICT: Löschen verbieten
• ON DELETE NO ACTION (Standardverhalten): erst mal abwarten

ON DELETE NO ACTION

Fachbereich (FBName)
Studierende (MatrNr, Name, FBName)
Professoren (ProfNr, Name, FBName)
Prüfungen (MatrNr, ProfNr, Fach, Versuch, Note)

Studierende(FBName) REFERENCES Fachbereich(FBName) ON DELETE CASCADE
Professoren(FBName) REFERENCES Fachbereich(FBName) ON DELETE CASCADE
Prüfungen(MatrNr) REFERENCES Studierende(MatrNr) ON DELETE CASCADE
Prüfungen(ProfNr) REFERENCES Professoren(MatrNr)

Fachbereich wird gelöscht ⇒ alle Studierenden in diesem werden gelöscht ⇒ Die Prüfungen der Studierenden werden gelöscht. Professoren werden gelöscht. OK!

DEFAULT-Werte für Spalten

CREATE TABLE kunden (/* ... */,
                     land VARCHAR(100) DEFAULT 'Deutschland');

Wird beim Einfügen in die Tabelle der Wert für eine Spalte nicht gesetzt, wird der DEFAULT-Wert dort eingetragen (standardmäßig NULL).

Sequenzen / Autoincrement

Primärschlüsselwerte (z. B. IDs) automatisch erzeugen lassen.

In MySQL:

CREATE TABLE kunden(kundennr INT PRIMARY KEY AUTO_INCREMENT, ...);

In PostgreSQL (aber auch MySQL):

CREATE TABLE kunden(kundennr SERIAL PRIMARY KEY  , ...);

CHECK-Constraint

CREATE TABLE kunden (/* ... */,
 email VARCHAR(500) CHECK (email LIKE '%@%'));

CREATE TABLE bewertungen (/* ... */,
 sterne INT CHECK (sterne BETWEEN 1 AND 5));

CREATE TABLE personen (/* ... */,
 geburtsdatum DATE,
 hochzeitsdatum DATE CHECK (hochzeitsdatum > geburtsdatum));

CREATE TABLE LIKE / AS

CREATE TABLE

CREATE TABLE pkw (bez VARCHAR(50) PRIMARY KEY, leistung INT);

CREATE TABLE lkw (LIKE pkw);

CREATE TABLE schrottkarren AS 
 SELECT * FROM pkw WHERE leistung < 60;

ALTER / DROP

ALTER: Verändern von Datenbankobjekten

ALTER TABLE bewertungen ADD COLUMN zeitstempel TIMESTAMP;

DROP: Entfernen von Datenbankobjekten

DROP TABLE bewertungen;

DML: INSERT, UPDATE, DELETE, ...

Einfügen, ändern, löschen, abrufen von Daten.

INSERT INTO produkte(produktnr, bezeichnung, preis, hersteller) 
             VALUES (88, 'Katzenfutter', 4.99, NULL)

UPDATE produkte SET preis = 5.99 WHERE produktnr = 88

DELETE FROM produkte WHERE produktnr = 88

INSERT

INSERT INTO produkte(produktnr, bezeichnung, preis, hersteller) 
             VALUES (88, 'Katzenfutter', 4.99, NULL)

INSERT INTO produkte(preis, bezeichnung)
             VALUES (4.99, 'Katzenfutter')

INSERT INTO produkte VALUES (88, 'Katzenfutter', 4.99, NULL)

Nachteile: Schwerer verständlich zu lesen, man muss auf die korrekte Reihenfolge achten, man darf keine Spalte vergessen, in der Zukunft könnten sich die Spalten ändern.

UPDATE

UPDATE produkte SET preis = 5.99 WHERE produktnr = 88

UPDATE produkte 
SET preis = 5.99, bezeichnung = 'Spezial-Katzenfutter' 
WHERE produktnr = 88

UPDATE produkte SET preis = preis+1

UPDATE produkte SET preis = preis * 1.1

DELETE / TRUNCATE TABLE

DELETE FROM produkte WHERE produktnr = 88

DELETE FROM produkte WHERE hersteller = 'Monsterfood'
                       AND preis < 1.00 
                                

DELETE FROM produkte

TRUNCATE TABLE produkte

SELECT

SELECT H.land, COUNT(*) AS anzahl, AVG(P.preis) AS avg_preis
FROM hersteller H LEFT JOIN produkte P ON H.firma = p.hersteller
WHERE P.preis > 3
GROUP BY h.land
HAVING COUNT(*) < 5
ORDER BY COUNT(*)
                                

"Zeige mir für jedes Land, in welchem weniger als fünf Produkte, die mehr als 3 EUR kosten, hergestellt wurden, die Anzahl der Produkte sowie die Durchschnittspreise an, aufsteigend sortiert nach der Anzahl.

• SELECT: Projektion
• FROM: Tabellen und Joins
• WHERE: Selektion
• GROUP BY: Zeilen gruppieren
• HAVING: Selektion nach der Gruppierung
• ORDER BY: Ergebnis sortieren

SELECT <spalten> FROM <tabelle>

SELECT bezeichnung, preis, round(preis * 1.15, 2) AS preis_usd 
FROM produkte

SELECT * FROM <tabelle>

SELECT * FROM produkte

SELECT DISTINCT

SELECT hersteller 
FROM produkte

SELECT DISTINCT hersteller 
FROM produkte

WHERE

SELECT * FROM produkte WHERE preis > 3

SELECT * FROM produkte WHERE preis >= 3 AND preis <= 9

SELECT * FROM produkte WHERE preis BETWEEN 3 AND 9

LIKE-Prädikat

SELECT * FROM produkte WHERE bezeichnung = 'Müsliriegel'

SELECT * FROM produkte WHERE bezeichnung LIKE 'M%'

SELECT * FROM produkte WHERE bezeichnung LIKE '%-%'

SELECT * FROM produkte WHERE bezeichnung LIKE 'M_sliriegel'

NULL-Werte

SELECT * FROM produkte WHERE hersteller IS NULL

SELECT * FROM produkte WHERE hersteller IS NOT NULL

Kreuzprodukt

SELECT * FROM produkte, hersteller

Join

SELECT * FROM produkte, hersteller
WHERE produkte.hersteller = hersteller.firma
                                    

<tabelle> JOIN <tabelle> ON <präd.>

SELECT * FROM produkte JOIN hersteller 
 ON produkte.hersteller = hersteller.firma

SELECT * FROM produkte P JOIN hersteller H
 ON P.hersteller = H.firma

LEFT/RIGHT/FULL [OUTER] JOIN

SELECT * FROM produkte P LEFT JOIN hersteller H
 ON P.hersteller = H.firma

JOIN ... USING(...)

SELECT P.*, B.sterne 
 FROM produkte P JOIN bewertungen B USING(produktnr)

Aggregatfunktionen

• COUNT: Anzahl
• SUM: Summe
• AVG: Durchschnittswert
• MIN: Kleinster Wert
• MAX: Größter Wert
• ...

Aggregatfunktion in der SELECT-Liste ohne GROUP BY-Klausel
⇒ die ganze Tabelle bildet eine große Gruppe
⇒ es kommt nur eine Zeile raus.

COUNT

SELECT COUNT(*) 
FROM produkte

SELECT COUNT(hersteller) 
FROM produkte

SELECT COUNT(DISTINCT hersteller) FROM produkte

SUM / AVG / MIN / MAX

SELECT SUM(preis), AVG(preis), MIN(preis), MAX(preis)
FROM produkte

GROUP BY

SELECT hersteller, COUNT(*), AVG(preis)
FROM produkte
GROUP BY hersteller

GROUP BY

SELECT hersteller, preis, COUNT(*)
FROM produkte
GROUP BY hersteller, preis

HAVING

SELECT hersteller, AVG(preis)
FROM produkte
GROUP BY hersteller
HAVING COUNT(*) >= 2

Sub-Anfragen

SELECT hersteller, avg_preis FROM (
 SELECT hersteller, COUNT(*) AS anzahl, AVG(preis) AS avg_preis 
 FROM produkte GROUP BY hersteller
) P WHERE anzahl >= 2

SELECT * FROM produkte
WHERE preis = (SELECT MAX(preis) FROM produkte)

SELECT * FROM produkte P1
WHERE preis = (SELECT MAX(preis) FROM produkte P2 
               WHERE P2.hersteller = P1.hersteller)

CTE: Common Table Expressions

WITH ... AS (SELECT ...) SELECT ...

WITH monsterfood_produkte AS 
 (SELECT * FROM produkte WHERE hersteller = 'Monsterfood')
SELECT count(*) FROM monsterfood_produkte

WITH monsterfood_produkte AS 
    (SELECT * FROM produkte WHERE hersteller = 'Monsterfood')
SELECT * FROM monsterfood_produkte
WHERE preis = (SELECT max(preis) FROM monsterfood_produkte)

ORDER BY

"Tabellen haben keine Ordnung, Ergebnisse schon."

• ASC: aufsteigend sortieren (Standard)
• DESC: absteigend sortieren

SELECT * FROM produkte ORDER BY preis

ORDER BY

"Tabellen haben keine Ordnung, Ergebnisse schon."

SELECT * FROM produkte 
ORDER BY hersteller NULLS LAST, preis DESC

LIMIT

SQL Standard (und DB2):

SELECT * FROM produkte ORDER BY preis DESC
FETCH FIRST 5 ROWS ONLY

PostgreSQL, MySQL, MariaDB, SQLite, ...

SELECT * FROM produkte ORDER BY preis DESC
LIMIT 5

LIMIT mit OFFSET

SELECT * FROM produkte ORDER BY preis DESC
LIMIT 2, 5

SELECT * FROM produkte ORDER BY preis DESC
LIMIT 5 OFFSET 2

SELECT

SELECT H.land, COUNT(*) AS anzahl, AVG(P.preis) AS avg_preis
FROM hersteller H JOIN produkte P ON H.firma = p.hersteller
WHERE P.preis > 3
GROUP BY h.land
HAVING COUNT(*) < 5
ORDER BY COUNT(*)
                                    

"Zeige mir für jedes Land, in welchem weniger als fünf Produkte, die mehr als 3 EUR kosten, hergestellt wurden, die Anzahl der Produkte sowie die Durchschnittspreise an, aufsteigend sortiert nach der Anzahl.

Ich will im Ergebnis auch sehen, dass es z. B. aus Österreich 0 solche Produkte gibt!

SELECT mit OUTER JOIN

SELECT H.land, COUNT(*) AS anzahl, AVG(P.preis) AS avg_preis
FROM hersteller H LEFT JOIN produkte P ON p.hersteller = H.firma
WHERE P.preis > 3
GROUP BY h.land HAVING COUNT(*) < 5 ORDER BY COUNT(*)
                                        

Problem: WHERE-Prädikat preis > 3 wird nach dem äußeren Verbund ausgeführt, schmeißt also die gewünschten Zeilen mit NULL-Werten wieder raus.

SELECT /****/ 
WHERE P.preis > 3 OR P.produktnr IS NULL

SELECT H.land, COUNT(*) AS anzahl, AVG(P.preis) AS avg_preis
FROM hersteller H
 LEFT JOIN (SELECT * FROM produkte WHERE preis > 3) P 
 ON p.hersteller = H.firma
GROUP BY h.land HAVING COUNT(*) < 5 ORDER BY COUNT(*)

UNION

Aus welchen Ländern sind unsere Kunden und Hersteller?

SELECT land FROM kunden
UNION
SELECT land FROM hersteller
                                

Bei Mengenoperationen ohne das Stichwort ALL wird nach UNION, INTERSECT und EXCEPT eine Duplikateliminierung vorgenommen.

UNION ALL

Aus welchen Ländern sind unsere Kunden und Hersteller?

SELECT land FROM kunden
UNION ALL
SELECT land FROM hersteller
                                

INTERSECT

In welchen Ländern gibt es Kunden und Hersteller?

SELECT land FROM kunden
INTERSECT
SELECT land FROM hersteller
                                

SELECT land FROM kunden
INTERSECT ALL
SELECT land FROM hersteller

EXCEPT (MINUS)

In welchen Ländern gibt es Kunden, aber keine Hersteller?

SELECT land FROM kunden
EXCEPT
SELECT land FROM hersteller
                                

                                        SELECT land FROM kunden
                                        EXCEPT ALL
                                        SELECT land FROM hersteller
                                

Das EXISTS-Prädikat

• Eingabe: Eine SELECT-Anfrage
• Ausgabe:
  • TRUE, wenn die Anfrage mind. 1 Zeile liefert
  • FALSE, wenn die Anfrage die leere Menge liefert

SELECT * FROM hersteller H
WHERE EXISTS 
 (SELECT * FROM produkte P WHERE p.hersteller = H.firma)
                            

NOT EXISTS

Von welchen Herstellern gibt es keine Produkte?

    SELECT * FROM hersteller H
    WHERE NOT EXISTS 
     (SELECT * FROM produkte P WHERE p.hersteller = H.firma)
                                

Das IN-Prädikat

• Eingabe: Ein Ausdruck und entweder eine Werte-Liste oder eine SELECT-Anfrage
• Ausgabe:
  • TRUE, wenn der Ausdruck enthalten ist
  • FALSE, wenn der Ausdruck nicht enthalten ist

SELECT * FROM hersteller H
WHERE firma IN ('Holzkopf', 'Monsterfood')
                                    

Das IN-Prädikat

SELECT * FROM hersteller H
WHERE firma IN (SELECT hersteller FROM produkte)
                                    

SELECT * FROM hersteller H
WHERE firma NOT IN 
 (SELECT hersteller FROM produkte WHERE hersteller IS NOT NULL)
                                    

CAST: Typumwandlung

CAST(ausdruck AS datentyp)

SELECT preis, CAST(preis AS INT), CAST(preis AS VARCHAR(50))
FROM produkte
                                    

CASE WHEN

CASE WHEN ... THEN ... ELSE ... END

SELECT bezeichnung, preis, 
CASE WHEN preis < 1 THEN 'billig' 
     WHEN preis < 5 THEN 'mittel' 
     ELSE 'teuer' END
FROM produkte
                                    

CASE WHEN

CASE ... WHEN ... THEN ... ELSE ... END

    SELECT bezeichnung, preis, 
    CASE preis WHEN 0 THEN 'kostenlos' ELSE preis END
    FROM produkte ORDER BY preis
                                        

Dummy-Tabelle DUAL

In einigen DBMS (Oracle, DB2, ...) gibt es die Tabelle DUAL.

SELECT * FROM dual

Praktisch zum Rechnen und Ausprobieren von Funktionen:

SELECT 5*3, CASE WHEN 7>4 THEN 'Ja' ELSE 'Nein' END FROM DUAL

SELECT ohne FROM in PostgreSQL

SELECT 5*3, CASE WHEN 7>4 THEN 'Ja' ELSE 'Nein' END

SELECT DATE '2020-12-25' - CURRENT_DATE

Kapitelzusammenfassung

• SQL: Anfragesprache für strukturierte Datenbanken
• DDL: CREATE / ALTER / DROP TABLE
• Datentypen: INT, VARCHAR, CHAR, DECIMAL, DATE, ...
• Constraints: PRIMARY KEY, NOT NULL, CHECK, UNIQUE, ...
• Referentielle Aktionen: ON DELETE/UPDATE ...
• DML: INSERT, UPDATE, DELETE, TRUNCATE, SELECT
• SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
• [LEFT/RIGHT/FULL] JOIN ON ...
• Aggregatfunktionen: COUNT, SUM, AVG, MIN, MAX
• Subanfragen, CTEs, EXISTS, IN
• Mengenoperationen: UNION / INTERSECT / EXCEPT [ALL]