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

Datenbanken

Kapitel 4: SQL

2020-11-24

SQL...

33, 36

... 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

34-35

Quelle: db-engines.com/de/ranking

Oracle
MySQL
Microsoft SQL Server
PostgreSQL ← verwenden wir im Rahmen dieser Vorlesung
IBM DB2
SQLite
MariaDB

Haben Sie schon einmal mit einem RDBMS gearbeitet?

Oracle
MySQL / MariaDB
Microsoft SQL Server
PostgreSQL
IBM DB2
SQLite
Mit einem anderen
Mit keinem

https://frage.space

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, ...

Mit welcher Art Kommando können Sie Daten von einer Tabelle eine andere kopieren?

https://frage.space

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'

Was wäre, wenn in der Anfrage 'Ute' nicht in Hochkommas stünde?

Das ginge trotzdem
Das wäre kein wohlgeformtes SQL
Dann wäre damit die Variable Ute gemeint
Dann wäre damit die Spalte Ute gemeint

https://frage.space

Kommentare

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

SELECT * FROM t
--WHERE x = 5

SELECT kundennummer /*, email*/ FROM kunden

Alles, was in einer Zeile auf ein -- folgt, ist ein Kommentar und gehört nicht mehr zur Anfrage. Dies ist auch praktisch, um vorübergehend gewisse Teile einer Anfrage wegzulassen und später wieder zu entkommentieren. Die Syntax /* ... */ ermöglicht Kommentare innerhalb einer Zeile und Kommentare, die mehrere Zeilen umfassen.

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

39-41

Datentyp	Alias	Beschreibung	Beispiel
INTEGER	INT	Ganze Zahl	-217
DECIMAL(p,s)	NUMERIC	Zahl mit p Stellen, davon s nach dem Dezimalpunkt	DECIMAL(5,2) -149.99
DOUBLE	REAL	Fließkommazahl (nicht exakt)	-15.127E-2
CHARACTER(l)	CHAR	Zeichenkette mit fixer Länge l	CHAR(3) 'AB '
CHARACTER VARYING(l)	VARCHAR	Zeichenkette mit max. Länge l	VARCHAR(3) 'AB'
DATE		Datum (Jahr, Monat, Tag)	'2020-11-18'
TIMESTAMP		Zeitstempel (Datum und Uhrzeit)	'2020-11-18 14:37:52.681'

CREATE TABLE - Spaltenoptionen

45, 47

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.

In den meisten DBMS ist NULL die Standard-Eigenschaft von Spalten, das heißt, man darf NULL-Werte in die Spalte eintragen. Will man dies verhindert, deklariert man die Spalte als NOT NULL. Im hier gezeigten unteren Beispiel ist das Attribut "Name" also ein Pflichtfeld.

UNIQUE-Constraint

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

oder:

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

Welchen Vorteil hat die untere Notationsform?

Keinen
Besser lesbar
UNIQUE-Constraint über eine Kombination mehrerer Spalten möglich
Spart Schreibarbeit

https://frage.space

Spezifiziert man hinter einer Spalte die Eigenschaft UNIQUE, darf es in dieser Spalte keine doppelten Werte gegen. In unserer Kundentabelle dürfen also keine zwei Kunden die gleiche E-Mail-Adresse haben. Die untere Notation ist nötig, wenn das UNIQUE-Constraint aus mehreren Spalten besteht, also wenn die Kombination aus mehreren Spaltenwerten eindeutig sein soll. Anders als bei Primärschlüsseln (in einer Tabelle darf höchstens einen geben), können auf einer Tabelle beliebig viele UNIQUE-Constraints definiert werden.

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.

Wenn der Primärschlüssel nur aus einer Spalte besteht, kann man PRIMARY KEY direkt hinter die Spalte schreiben. Primärschlüssel sind UNIQUE, d. h. es gibt keine Duplikate in der Tabelle in diesen Spalten. Außerdem sind Primärschlüsselattribute alle NOT NULL, d. h. in ihnen dürfen keine NULL-Werte vorkommen.

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));

Besteht der Fremdschlüssel nur aus einer Spalte, kann REFERENCES tabelle(spalte) direkt hinter die Spalte geschrieben werden. Ansonsten muss die unten stehende FOREIGN KEY-Schreibweise verwendet werden.

Zusammengesetzte Primär-/Fremdschlüssel

46, 48

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

Was wird hier beim Löschen auf NULL gesetzt?

produkte.hersteller
hersteller.firma
hersteller.land
gar nichts

https://frage.space

Unter einer Aktion versteht man den Dann-Teil einer Wenn-Dann-Regel. Wenn man einen Hersteller löscht, dann... Im oben gezeigten Beispiel wird in einem solchen Fall wegen ON DELETE SET NULL der Wert der Hersteller-Spalte in der Produkte-Tabelle auf NULL gesetzt. Das ON UPDATE CASCADE sorgt dafür, dass bei einer Umbenennung im Primärschlüsselattribut des Herstellers (Firma) diese Umbenennung auch bei den Produkten dieses Herstellers erfolgt.

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!

[[ { _e: "Fachbereich", pos: [130, 15] }, { _e: "Studierender", pos: [5, 90] }, { _e: "Professor", pos: [260, 90] }, { _e: "Prüfung", pos: [130, 170], options:["weak"] }, ], [ {_r: "hat", _e:["Fachbereich", "Studierender"], card:["1","N"], pos: [40, 0]}, {_r: "hat", _e:["Fachbereich", "Professor"], card:["1","N"], pos: [300, 0]}, {_r: "legt ab", _e:["Studierender", "Prüfung"], card:["1","N"], pos: [40, 155]}, {_r: "prüft", _e:["Professor", "Prüfung"], card:["1","N"], pos: [300, 155]}, ]]

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).

Was ist wenn land keinen DEFAULT-Wert hat und NOT NULL ist und nun versucht wird, beim Einfügen kein Land zu setzen?

Land ist NULL
Land ist '' (leerer String)
Land ist 0
Es kommt ein Fehler

https://frage.space

Später beim INSERT-Kommando wird gezeigt, dass man nicht zwangsweise alle Spaltenwerte beim Einfügen einer neuen Zeile setzen muss. Die DEFAULT-Option legt für solche Fälle den Standardwert fest. Gibt es keinen definierten DEFAULT-Wert und wird der Wert nicht gesetzt, erhält die Zeile in der entsprechenden Spalte den Wert NULL. Ist auf der Spalte ein NOT NULL-Constraint definiert, kommt ein Fehler. Vor solchen Fehlern kann dann ein DEFAULT-Wert schützen. Wird mittels ALTER TABLE ... ADD COLUMN nachträglich eine neue Spalte mit DEFAULT-Wert einer existierenden Tabelle hinzugefügt, erhalten alle bisherigen Zeilen in der neuen Spalte diesen DEFAULT-Wert.

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  , ...);

In verschiedenen DBMS gibt es die Spaltenoption AUTO_INCREMENT (MySQL), AUTOINCREMENT (SQLite), GENERATED BY DEFAULT AS IDENTITY (SQL Standard, Oracle, DB2), IDENTITY (Microsoft SQL Server), o. ä. Spalten mit dieser Option müssen einen nummerischen Datentypen haben. Eingefügte Zeilen erhalten standardmäßig eine automatisch generierte laufende Nummer. Problematisch kann es werden, wenn nan beim Einfügen auch selbst einen Wert setzt, statt ihn automatisch generieren zu lassen. Der SQL Standard und DB2 unterstützen INTEGER GENERATED ALWAYS AS IDENTITY, damit immer (nicht nur als Default) eine automatische ID erzeugt wird.

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));

Was ist kein Constraint?

CHECK
PRIMARY KEY
FOREIGN KEY
DEFAULT
UNIQUE
NOT NULL

https://frage.space

Mit einem CHECK-Constraint lassen sich benutzerdefinierte Spalten-Constraints definieren. In Klammern hinter dem Stichwort CHECK steht ein beliebiger boolescher Ausdruck, der stets wahr sein muss. Beim Einfügen und Ändern von Zeilen überprüft das DBMS die Bedingung und lehnt die entsprechende Operation ab, wenn das CHECK-Constraint verletzt würde. In den gezeigten Beispielen muss in der E-Mail-Spalte immer ein @-Zeichen vorkommen, der Wertebereich der Sterne-Spalte wird eingeschränkt auf ganze Zahlen zwischen 1 und 5, und das Hochzeitsdatum muss immer echt später sein als das Geburtsdatum einer Person.

CREATE TABLE LIKE / AS

CREATE TABLE

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

CREATE TABLE LIKE

CREATE TABLE lkw (LIKE pkw);

CREATE TABLE AS

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

Alle drei hier gezeigten Befehle erstellen eine Tabelle. Bei der ersten gibt man die Spalten der Tabelle an, beim CREATE TABLE LIKE wird eine Tabelle mit den gleichen Spalten erstellt wie eine schon existierende Tabelle und CREATE TABLE AS speichert das Ergebnis einer SELECT-Anfrage in eine neu zu erstellende Tabelle. In den ersten beiden Kommandos ist die neue Tabelle leer, beim dritten Kommando können Daten in der neuen Tabelle sein. Mit CREATE TABLE T2 AS SELECT * FROM T1 kann man eine Tabelle komplett mit Struktur und Daten kopieren.

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)

Nur ein Teil der Spalten setzen:

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

Spaltennamen weglassen:

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.

Wenn nur ein Teil der Tabellenspalten gesetzt werden, werden die anderen Spalten auf ihre DEFAULT-Werte, den nächsten AUTO_INCREMENT / SERIAL-Wert oder auf NULL gesetzt. Lässt man die Spaltennamen vor dem Stichwort VALUES weg, müssen alle Spalten der Tabelle in der richtigen Reihenfolge gesetzt werden. Wenn in der Zukunft eine neue Spalte in die Produkttabelle hinzugefügt wird, würde das untere Kommando nicht mehr funktionieren.

UPDATE

UPDATE produkte SET preis = 5.99 WHERE produktnr = 88

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

Was wird geändert, wenn man kein WHERE-Prädikat angibt?

Nur die erste Zeile
Alle Zeilen
Gar nichts

https://frage.space

UPDATE produkte SET preis = preis+1

UPDATE produkte SET preis = preis * 1.1

Das UPDATE-Kommando besteht aus einem SET-Teil und einem WHERE-Teil. Im SET werden Komma-getrennte Zuweisungen angegeben, um die neuen Spaltenwerte zu setzen. Der WHERE-Teil besteht aus einer Bedingung, die für eine Zeile erfüllt sein muss, damit diese entsprechend geändert wird. Lässt man die WHERE-Klausel weg, steht dies für WHERE TRUE, man ändert also alle Zeilen.

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

Was ist der Unterschied zwischen TRUNCATE TABLE und DROP TABLE?

TRUNCATE ist schneller
TRUNCATE löscht die Tabelle aus dem Metadaten-Katalog der Datenbank
TRUNCATE löscht nur die Daten, die Tabelle ist noch da
Es gibt keinen

https://frage.space

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

Was macht dieses SELECT?

Selektion
Projektion ohne Duplikateliminierung
Kreuzprodukt
Weder noch

https://frage.space

Die SELECT-Klausel wird bei der Anfrageausführung erst sehr spät ausgewertet. In ihr findet die Projektion statt, um festzulegen, welche Spalten man letztendlich im Ergebnis sehen möchte. Man kann mehrere Spalten mit Kommata trennen, mit AS einen Alias definieren - also eine Umbenennung vornehmen - und man kann Funktionen aufrufen. In der FROM-Klausel stehen eine oder mehrere Tabellen, aus der die Daten stammen sollen.

SELECT * FROM <tabelle>

SELECT * FROM produkte

Mit SELECT * wird das ganze Tupel ausgegeben. In der Anfrage auf dieser Folio werden alle Spalten der Produkte-Tabelle angezeigt.

SELECT DISTINCT

SELECT hersteller 
FROM produkte

SELECT DISTINCT hersteller 
FROM produkte

In SQL wird standardmäßig keine Duplikateliminierung vorgenommen. Das Ergebnis einer Anfrage kann also - anders als in der relationalen Algebra - gleiche Zeilen doppelt beinhalten. Um eine Duplikateliminierung durchzuführen schreibt man SELECT DISTINCT statt SELECT.

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'

Was wird durch die untere Anfrage gefunden?

Masliriegel
Muesliriegel
Msliregel
Keines davon

https://frage.space

Strings müssen stets in 'einfache Anführungszeichen' geschrieben werden. 'Innerhalb eines Strings braucht''s zwei Anführungszeichen', um es zu escapen. Im LIKE-Prädikat wird eine Musterüberprüfung vorgenommen. % steht dabei für beliebige Zeichen und _ für genau ein beliebiges Zeichen.

NULL-Werte

SELECT * FROM produkte WHERE hersteller IS NULL

SELECT * FROM produkte WHERE hersteller IS NOT NULL

hersteller = NULL ist...

immer true
immer false
true, wenn hersteller NULL ist
false, wenn hersteller NULL ist

https://frage.space

Überprüfungen, ob der Wert einer Spalte NULL ist, muss mit IS NULL erfolgen. Das Prädikat IS NOT NULL trifft auf Zeilen zu, die in der entsprechenden Spalte keinen NULL-Wert haben.

Kreuzprodukt

SELECT * FROM produkte, hersteller

Join

⋈

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

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

61-62

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

Tabellen-Alias

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

Die JOIN ... ON-Syntax in der FROM-Klausel ist eine Alternative zum Join in der WHERE-Klausel. An ihr ist besser erkennbar, dass hier ein Join ausgeführt wird, es verhindert, dass man aus Versehen die Join-Bedingung vergisst und es trennt klar den Verbund von weiteren Prädikaten. Außerdem lässt sich mit der JOIN-Klausel auch einen äußeren Verbund auszuführen (siehe nächste Folie). Bei nicht eindeutigen Spaltennamen muss unbedingt ein Tabellenprefix vor die Spalte geschrieben werden. Um Anfragen kompakter zu gestalten, bietet es sich an Tabellenaliase in der FROM-Klausel einzuführen (hier: P und H).

LEFT/RIGHT/FULL [OUTER] JOIN

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

Ein äußerer Verbund lässt sich ausführen, indem statt dem JOIN-Schlüsselwort LEFT JOIN, RIGHT JOIN oder FULL JOIN geschrieben wird. Bei hier gezeigten linken äußeren Verbund sind alle Zeilen der linken Tabelle im Join-Ergebnis. Diejenigen, die keinen Join-Partner in der rechten Tabelle finden, erhalten in den entsprechenden Spalten einen NULL-Wert.

JOIN ... USING(...)

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

Welche Aussage ist falsch?

Die Anfrage entspricht einem äußeren Verbund
Die Anfrage entspricht einem natürlichen Verbund
Die Anfrage entspricht ON P.produktnr = B.produktnr
Die Anfrage funktioniert nur, wenn beide Tabellen die Spalte produktnr haben

https://frage.space

USING(produktnr) ist eine verkürzte Schreibweise für den Equi-Join auf der Spalte Produktnummer, also in dem Fall für ON P.produktnr = B.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

COUNT(*) (manche schreiben auch gerne COUNT(1)) zählt die Anzahl der Zeilen in der Gruppe. Da hier die Gruppe die ganze Tabelle ist, liefert dies die Anzahl der Zeilen in der Tabelle. COUNT mit Spaltenangaben zählt, wie viele Zeilen in den gegebenen Spalten keinen NULL-Wert haben. COUNT(DISTINCT ...) zählt die von NULL verschiedenen distinkten (unterschiedlichen) Werte in den gegebenen Spalten. In unserem Beispiel existieren die beiden Werte Calgonte und Monsterfood in der Hersteller-Spalte.

SUM / AVG / MIN / MAX

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

Den Aggregatfunktionen SUM, AVG, MIN und MAX wird als Argument eine Spalte (oder ein Ausdruck, z. B. SUM(preis * (1+steuersatz/100)) übergeben. Wir sehen hier im Ergebnis die Summe aller Preise, den Durchschnitt sowie den kleinsten und größten Preis. NULL-Werte werden in den Aggregatfunktionen übersprungen. Das heißt AVG(preis) berechnet sich aus der Summe der Preise, die nicht NULL sind, geteilt durch die Anzahl der Preise, die nicht NULL sind. Also: SUM(preis)/COUNT(preis)

GROUP BY

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

Wie viele Zeilen kommen hier heraus?

0
1
So viele, wie es verschiedene Werte in der hersteller-Spalte gibt
So viele, wie es Zeilen in der Produkte-Tabelle gibt

https://frage.space

In der GROUP BY-Klausel werden Spalten spezifiziert, nach deren Werten Gruppen gebildet werden. GROUP BY hersteller bedeutet, dass für jeden distinkten Wert in der hersteller-Spalte eine Gruppe gebildet wird. In unserem Beispiel sind dies drei. Für jede Gruppe kann nun der Wert in dieser GROUP BY-Spalte ausgegeben werden. Alle anderen Spalten dürfen nur innerhalb von Aggregatfunktionen in der SELECT-Liste vorkommen. Wir berechnen in dem Beispiel auf dieser Folie für jeden einzelnen Hersteller die Anzahl und Durchschnittspreise seiner Produkte.

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

67-68

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

Wenn zwei Produkte den gleichen Preis haben, welches kommt dann zuerst?

Das mit der kleinsten produktnr
Das, was zuerst eingefügt wurde
Das, was zuletzt eingefügt wurde
Keine Ahnung

https://frage.space

ORDER BY

"Tabellen haben keine Ordnung, Ergebnisse schon."

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

Gibt man mehrere Ausdrücke im ORDER BY an, wird zuerst nach dem ersten sortiert, dann nach dem zweiten, usw. NULLS FIRST bzw. NULLS LAST sorgt dafür, dass NULL-Werte in der zu sortierenden Spalte an den Anfang bzw. ans Ende sortiert werden. Hier kommt Calgonte im Alphabet vor Monsterfood, aber da es zwei Produkte von Monsterfood gibt, kommt wegen preis DESC das teuerste zuerst.

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

Mit der LIMIT-Klausel lässt sich die Ergebnismenge auf eine bestimmte Anzahl von Tupeln begrenzen. SELECT * FROM produkte LIMIT 5 würde irgendwelche fünf Produkte liefern. Die untere Anfrage auf dieser Folie liefert die fünf teuersten Produkte. SELECT * FROM produkte ORDER BY preis DESC LIMIT 1 liefert das teuerste Produkt. Haben zwei Produkte den gleichen und den teuersten Preis, wird irgendeines von diesen ausgegeben.

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(*)

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(*)

74-75

⋃

UNION

Aus welchen Ländern sind unsere Kunden und Hersteller?


SELECT land FROM kunden
UNION
SELECT land FROM hersteller

Was muss für die Spalten (hier: land) der beiden Teilanfragen gelten?

Sie müssen gleich heißen
Es müssen gleich viele sein
Beides
Weder noch

https://frage.space

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

Auf der vorherigen Folie haben wir gesehen, dass jedes Land im Ergebnis nur einmal vorkommt. Das liegt daran, dass im Anschluss an Mengenoperationen ohne das Stichtwort ALL eine Duplikateliminierung vorgenommen wird. Hier sehen wir wegen UNION ALL auch doppelte Zeilen.

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)

Was liefert diese Anfrage?

Hersteller, von denen es Produkte gibt
Produkte, die einen Hersteller haben
Hersteller, von denen es keine Produkte gibt
Produkte mit Hersteller NULL

https://frage.space

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')

Hier erspart man durch die IN-Liste Schreibarbeit, da man ansonsten WHERE hersteller = 'Holzkopf' OR hersteller = 'Monsterfood' schreiben müsste.

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)

Oben: Hersteller, von denen es Produkte gibt; unten: Hersteller, von denen es keine Produkte gibt. Würde man unten das WHERE hersteller IS NOT NULL weglassen, würden in der Sub-Anfrage NULL-Werte auftauchen und das Ergebnis der Anfrage wäre leer. Berechnungen und Vergleiche mit NULL-Werten liefern stets NULL, was bei Prädikatsauswertungen immer mit FALSE gedeutet wird.

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

Steht zwischen CASE und WHEN ein Ausdruck, kann man diesen Ausdruck ohne viel Schreibarbeit auf verschiedene Werte überprüfen. Der hier stehende Ausdruck ist äquivalent zu CASE WHEN preis = 0 THEN 'kostenlos' ELSE preis END

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

Wie viele Tage sind noch bis Weihnachten?

SELECT DATE '2020-12-25' - CURRENT_DATE

Wie kann man den Spalten dieser Anfrage sinnvolle Namen geben?

SELECT DATE '2020-12-25' - CURRENT_DATE anzahl_tage
SELECT DATE '2020-12-25' - CURRENT_DATE : anzahl_tage
SELECT DATE '2020-12-25' - CURRENT_DATE AS anzahl_tage
RENAME column DATE '2020-12-25' - CURRENT_DATE AS anzahl_tage

https://frage.space

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]

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

Datenbanken

Kapitel 4: SQL

2020-11-24

SQL...

Populäre RDBMS

Haben Sie schon einmal mit einem RDBMS gearbeitet?

https://frage.space

PostgreSQL

DBeaver

Sprachkomponenten

DDL: Data Definition Language

DML: Data Manipulation Language

DCL: Data Control Language

Mit welcher Art Kommando können Sie Daten von einer Tabelle eine andere kopieren?

https://frage.space

SQL ausprobieren

SQL Island

SQL Fiddle, DB Fiddle, Rextester

Sprachelemente

SQL-Schlüsselworte (Keywords)

Identifikatoren (Namen von Tabellen, Spalten, etc.)

Literale (Werte eines Datentyps)

Was wäre, wenn in der Anfrage 'Ute' nicht in Hochkommas stünde?

https://frage.space

Kommentare

DDL: CREATE, ALTER, DROP

CREATE TABLE

Datentypen

CREATE TABLE - Spaltenoptionen

Constraints (Integritätsbedingungen):

NULL / NOT NULL

NULL (Standard)

NOT NULL

UNIQUE-Constraint

Welchen Vorteil hat die untere Notationsform?

https://frage.space

Primärschlüssel-Constraint

Fremdschlüssel-Constraints

Zusammengesetzte Primär-/Fremdschlüssel

Referentielle Aktionen

Was wird hier beim Löschen auf NULL gesetzt?

https://frage.space

ON DELETE NO ACTION

DEFAULT-Werte für Spalten

Was ist wenn land keinen DEFAULT-Wert hat und NOT NULL ist und nun versucht wird, beim Einfügen kein Land zu setzen?

https://frage.space

Sequenzen / Autoincrement

In MySQL:

In PostgreSQL (aber auch MySQL):

CHECK-Constraint

Was ist kein Constraint?

https://frage.space

CREATE TABLE LIKE / AS

CREATE TABLE

CREATE TABLE LIKE

CREATE TABLE AS

ALTER / DROP

DML: INSERT, UPDATE, DELETE, ...

INSERT

UPDATE

Was wird geändert, wenn man kein WHERE-Prädikat angibt?

https://frage.space

DELETE / TRUNCATE TABLE

Was ist der Unterschied zwischen TRUNCATE TABLE und DROP TABLE?

https://frage.space

SELECT

SELECT <spalten> FROM <tabelle>

Was macht dieses SELECT?

https://frage.space

SELECT * FROM <tabelle>

SELECT DISTINCT

WHERE

LIKE-Prädikat

Was wird durch die untere Anfrage gefunden?

https://frage.space

NULL-Werte

hersteller = NULL ist...

https://frage.space

Kreuzprodukt

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

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

`CAST(ausdruck AS datentyp)`

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

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