SELECT - Klaus Kusche

Datenbanken
und SQL
Klaus Kusche, 2013-
Was ist eine
Datenbank ???
(hier nur “Relationale” Datenbank:
Es gibt auch objektorientierte DB, Volltext-DB, ...)
Datenbank vs. DBMS
Unterscheide:
●
Datenbank = “Die Daten an sich”
==> Ansammlung von Tabellen
●
DBMS = “Data Base Management System”
==> Die Software, die diese Daten / Tabellen
erzeugt, verwaltet, durchsucht, ...
Bekannte DBMS
●
Oracle: Kommerziell, Marktführer
●
DB/2: Kommerziell, von IBM
●
MS SQL Server: Kommerziell, von Microsoft
●
MySQL (MariaDB): Open Source, primär für WWW
Eher Spielzeug, ist noch nicht in allen Punkten “echte” DB!
●
PostgreSQL: Open Source, “große” DB
De facto SQL-Standard-Referenzimplementierung!
●
SQLite: Open Source, “kleine” DB (z.B. in Firefox)
Als Library zu einem Programm, kein eigener Server!
Alles in einem File, im Betrieb oft komplett im RAM.
Nicht DBMS
●
Excel:
Fast alle Eigenschaften einer DB fehlen!
●
Access:
Von der Daten-Logik her fast eine Datenbank,
vom praktischen Betrieb her nicht...
Eigenschaften von DB & DBMS
“Wie unterscheidet sich ein DBMS
von Excel / Access?”
●
Datendarstellung
●
Größe
●
Zugriff
●
Transaktionen und Mehrbenutzer-Fähigkeit
●
Datensicherheit
Datendarstellung (1)
●
●
In ein oder mehreren Tabellen
Spalten sind nicht nummeriert,
sondern haben einen Namen (==> Reihenfolge egal)
und einen Typ (Int, Text, Datum, Bool, Binärdaten, ...)
Wird beides beim Erzeugen der Tabelle festgelegt!
●
Zeilen sind nicht nummeriert,
sondern ohne bestimmte Reihenfolge
(interne Reihenfolge ist beliebig, nicht nach außen sichtbar)
Jederzeit beliebig Einfügen und Löschen möglich!
Datendarstellung (2)
●
Zugriff auf bestimmte Zeilen mittels Inhalt, z.B.:
“Gib mir alle Zeilen, bei denen die Spalte
'Geburtsdatum' zwischen 1990 und 2000 liegt ...”
●
Reihenfolge / Sortierung nach außen
auf Wunsch auch nach Inhalt, z.B.:
“... und sortiere sie nach dem 'Nachnamen'!”
●
Damit beides schneller geht:
Vorab Deklaration bestimmter Spalten als
(Primär- / Sekundär-) Schlüssel bzw. Index
Typische Größen
●
●
●
Tabellen:
Ein paar Dutzend bis einige 1000 (SAP: > 20000 !)
Zeilen pro Tabelle:
Ganz wenige (z.B. Konfigurations-Tabellen)
bis viele Millionen (z.B. Konten, Bank-Buchungen)
Datenmenge insgesamt:
Einige KB bis einige TB
==> Alles im Speicher halten geht nicht!
==> Der Reihe nach durchsuchen dauert zu lange!
==> “Clevere” Speicherung auf Platte nötig!
==> Zugriff nur über DBMS, nicht direkt (mit Editor o.ä.) lesbar!
Zugriff (1)
●
●
Nicht direkt (mit Zeilen- & Spaltennummer)
auf einzelne Felder...
sondern als “Frage- und Antwort-Spiel”:
“Gib mir ...”, “Lösche ...”, “Füge ... neu dazu”, ...
●
Programmiersprache für die Fragen:
SQL
= Structured Query Language
(schaut ganz anders aus als normale Programmiersprachen,
teilweise fast wie Englisch lesbar)
Zugriff (2)
SQL erlaubt die Beschreibung u.a. von
●
Suche bzw. Zeilen- und Spaltenauswahl,
Pattern Matching (“Wildcards”)...
●
Sortierung und Gruppierung
●
Verknüpfung der Daten aus mehreren Tabellen
●
Berechnungen auf dem Ergebnis
(Min & Max oder Summe einer Spalte, Anzahl der Treffer, ...)
==> Das passiert alles schon im DBMS selbst!
==> Das Programm, die die Daten braucht / anzeigt / ...,
wird dadurch viel einfacher!
SQL
●
Entstanden um / vor 1980
1979: Erste Oracle-Version mit SQL
●
Genormt (ANSI, ISO) seit 1986
●
Heute meist verwendeter Standard:
SQL-92 oder SQL-99
●
Seitdem vor allem um XML erweitert,
aktuell SQL-11
Zugriff technisch (1)
Das DBMS ist ein eigener Server:
●
●
●
Läuft ständig, oft auf eigener Maschine
Horcht am Netz auf SQL-Requests
==> Remote-Zugriff möglich!
==> Meist Anmeldung (Benutzer & Passwort) nötig,
Benutzer mit unterschiedlichen Rechten!
Enthält u.a. SQL-Interpreter und -Optimizer:
"In welcher Reihenfolge muss ich wie
auf welche Tabellen zugreifen, um die Abfrage
möglichst schnell zu beantworten?"
Zugriff technisch (2)
Dazu gehören verschiedene Clients ...
●
... als Libraries für verschiedene Programmiersprachen
(PHP, C/C++, Java, ...):
Absetzen von SQL-Abfragen
aus anderen Programmen heraus
●
... als eigenständiges GUI- oder Commandline-Tool:
Händisches Eintippen von SQL-Befehlen,
Direktes Anzeigen des Ergebnisses als Tabelle.
Weitere Komponente eines DBMS:
●
Administrations-Tools: Backup, Export/Import, ...
Im praktischen Betrieb...
... gibt es viele Herausforderungen:
●
Wenn mehrere Benutzer gleichzeitig zugreifen:
Änderungen geraten sich gegenseitig in die Quere?!
(ein Benutzer sieht “halbe” neue Daten eines anderen?)
●
Bei Absturz, Stromausfall, ...:
Danach “halbe” Änderungen in der Datenbank?!
(manche Änderungen betreffen Millionen Zeilen!)
●
Bei Erkennen eines Problems am Ende
mehrerer zusammengehörender SQL-Befehle:
Was ist mit den schon erfolgreichen Befehlen davor?
Das Transaktions-Konzept (1)
Eine Transaktion umfasst
mehrere aufeinanderfolgende,
logisch zusammengehörende SQL-Befehle
eines Benutzers (einer Session, ...):
Alle Zugriffe / Änderungen, die nötig sind,
um eine Buchung, Bestellung, ... komplett auszuführen
(beginnend von den ursprünglichen Daten,
bis die neuen Daten alle vollständig eingetragen sind).
In EDV-Deutsch: Alle SQL-Befehle, die nötig sind,
um von einem konsistenten Zustand der Daten
zum nächsten konsistenten Zustand zu kommen.
Das Transaktions-Konzept (2)
Eine Transaktion kann
●
entweder erfolgreich abgeschlossen werden:
“COMMIT” =
“Speichere alle Änderungen dieser Transaktion endgültig
und mach sie für alle anderen sichtbar!”
●
oder komplett zurückgenommen werden:
“ROLLBACK” =
“Mach alle Änderungen dieser Transaktion rückgängig,
als ob es die Befehle nie gegeben hätte!”
Das ACID-Prinzip (1)
●
Änderungen sind atomar:
“Alles oder nichts”
==> Es sind nie “halbe” Transaktionen sichtbar!
●
Die Datenbank ist (c)konsistent:
Nach jeder Transaktion ist die Datenbank
in “sauberem” Zustand:
Die Daten in allen Tabellen
passen inhaltlich wieder zueinander.
Das ACID-Prinzip (2)
●
Jede Transaktion läuft isoliert:
... als ob sie die Datenbank für sich allein hätte:
Keine gegenseitige Beeinflussung!
Eine Transaktion sieht von Anfang bis Ende
denselben Datenbank-Inhalt und bekommt von anderen,
gleichzeitigen laufenden Transaktionen nichts mit.
●
Jedes erfolgreiche COMMIT ist beständig (durable):
Auch wenn die Datenbank gleich danach abstürzt
(oder die Platte kaputtgeht),
bleiben die Daten erhalten!
Wie geht das? (1)
Jedes “echte” DBMS verwaltet
zwei Platten-Speicherbereiche
(hoffentlich auf zwei verschiedenen Platten):
●
Die eigentliche Datenbank:
Enthält die gerade aktuellen Daten
(nicht unbedingt immer konsistent)
●
Den Log:
Enthält der Reihe nach alle Änderungen und Commits
(seit dem letzten Backup, jeweils Alt- und Neu-Daten)
Laufend geschrieben, COMMIT wartet bis der Log auf Platte ist!
Wie geht das? (2)
●
ROLLBACK:
“Schau im Log nach,
was du alles rückgängig machen musst!”
●
Plattencrash:
“Roll forward” = “Nimm das letzte Daten-Backup,
und hole alle Änderungen seitdem aus dem Log nach!”
●
Datenbank-Crash:
“Prüfe beim nächsten Start den Log:
Mach alle unfertigen Transaktionen rückgängig und prüfe,
ob alle fertigen Transaktionen komplett gespeichert sind!”
Was gehört zu
SQL ?
Arten von SQL-Befehlen (1)
Auslesen
von Daten
(incl. Suche, Sortierung, Gruppierung,
Verknüpfung von Daten mehrerer Tabellen...)
SELECT ...
FROM ...
WHERE ...
(was?)
(aus welcher Tabelle?)
(Suchbedingung)
(ev. plus ORDER BY ... GROUP BY ... usw.)
(auch: Kopieren von Daten in eine andere Tabelle)
Arten von SQL-Befehlen (2)
Einfügen / Ändern / Löschen
von Daten
INSERT ...
INTO ...
(was?)
(in welche Tabelle?)
UPDATE ...
SET ...
WHERE ...
(welche Tabelle?)
(welche Spalten auf welche Werte?)
(in welchen Zeilen?)
DELETE FROM ... (in welcher Tabelle?)
WHERE ... (welche Zeilen?)
Arten von SQL-Befehlen (3)
Anlegen
von Tabellen
CREATE TABLE ... (Tabellenname)
( ... )
(Spalten-Definitionen)
(auch: Festlegen der Schlüssel-Spalte usw.)
CREATE VIEW ...
(ähnlich für “virtuelle” Tabellen)
CREATE INDEX ... (Anlegen eines Such-Index
auf einer bestehenden Tabelle)
Arten von SQL-Befehlen (4)
Ändern
von Tabellen
ALTER TABLE ...
ADD ...
DROP ...
MODIFY ...
CHANGE ...
(Tabellenname)
(Spalte dazu)
(Spalte löschen)
(Spaltentyp usw. ändern)
(Spaltenname ändern)
ALTER INDEX ...
(analog für Index)
Arten von SQL-Befehlen (5)
Löschen
von Tabellen
DROP TABLE ... (Tabellenname)
(löscht die Tabelle mitsamt allen Daten)
DROP INDEX ... (analog für Index)
DROP VIEW ... (analog für View)
TRUNCATE TABLE ... (Tabellenname)
(löscht alle Daten, aber nicht die Tabelle selbst)
Das ist (fast) alles und was ist daran so schwer?
Die Details
des SELECT !
SELECT Schlüsselwörter usw.
SELECT
DISTINCT
WHERE
AND OR
IN
BETWEEN
LIKE
ORDER BY
COUNT
GROUP BY
HAVING
ALIAS
JOIN
FULL JOIN
UNION
UNION ALL
INTERSECT
MINUS
SUBSTRING
CASE
...
Funktionen,
Unterabfragen,
Kaskadierungen, ...
Einfaches SELECT
SELECT * FROM Schueler
Gib alle Zeilen und alle Spalten
der Tabelle Schueler aus.
SELECT Vorname, Nachname, GebDat
FROM Schueler
Wie oben, aber nur die angegebenen Spalten.
SELECT DISTINCT Klasse FROM Schueler
Nur Spalte Klasse, doppelte Einträge weglassen
= Liste aller Klassen.
Sortierte Ausgabe: ORDER BY
SELECT * FROM Schueler
ORDER BY Nachname
Nach Nachname sortiertes Ergebnis.
SELECT Vorname, Nachname, GebDat
FROM Schueler ORDER BY 3 DESC
Nach der 3. Ergebnis-Spalte (GebDat)
absteigend (“descending”) sortiertes Ergebnis.
SELECT * FROM Schueler
ORDER BY Klasse, Note
Zuerst nach Klasse, bei gleicher Klasse nach Note...
Abkürzungen für Tabellen: AS
SELECT S.Note, S.Nachname
FROM Schueler AS S
ORDER BY S.Note DESC
S wird als Abkürzung (Alias)
für die Tabelle Schueler definiert
S.Note heißt
“die Spalte Note der Tabelle S (= Schueler)”
Sinnvoll ...
●
bei Befehlen über mehrere Tabellen
●
damit der SQL-Interpreter die Spalten schneller findet
Spalten benennen und berechnen
SELECT Nachname, GebDat AS Geburtstag
FROM Schueler
Spaltenüberschrift “Geburtstag” statt “GebDat”.
SELECT Semester, 1800 AS Semestergebuehr,
Semester * 1800 AS Gesamtbetrag
FROM Schueler
1. Spalte “Semester” = Semester aus der Tabelle,
2. Spalte “Semestergebuehr” = Wert fix 1800
3. Spalte “Gesamtbetrag” = Wert aus 1. Spalte mal 1800
Fallunterscheidung: CASE (1)
●
Variante 1: Vergleich mit konstanten Werten
SELECT Vorname, Nachname,
CASE Semester
WHEN 1 THEN 'Anfänger'
WHEN 2 THEN 'Lehrbub'
WHEN 3 THEN 'Könner'
WHEN 4 THEN 'Halbgott'
ELSE 'Sitzenbleiber'
END
FROM Schueler ORDER BY 2
(oft 'm' ==> 'Herr', 'w' ==> 'Frau')
Fallunterscheidung: CASE (2)
●
Variante 2: Test mit Bedingungen
(werden von oben nach unten durchgeprüft)
SELECT Vorname, Nachname,
CASE
WHEN Note < 2.0 THEN 'Super'
WHEN Note >= 4.5 THEN 'War nix'
ELSE 'Geht so'
END
FROM Schueler ORDER BY 2
Ergebnisse speichern: INTO
(nicht in MySQL!)
SELECT * INTO SchuelerBackup
FROM Schueler
Macht eine Kopie SchuelerBackup
der Tabelle Schueler.
SELECT DISTINCT Klasse
INTO Klassenliste
FROM Schueler
Legt eine neue Tabelle Klassenliste an
(mit einer einzigen Spalte Klasse)
und befüllt sie mit dem Ergebnis des SELECT.
Ergebnisse kombinieren: UNION
SELECT
FROM
UNION
SELECT
FROM
S.Vorname, S.Nachname, 'Student'
Schueler AS S
L.Vorname, L.Nachname, 'Professor'
Lehrer AS L
Vereinigt die Ergebnis-Zeilen mehrerer SELECT
(mehrfache, gleiche Zeilen in der Vereinigung
werden nur einmal ausgegeben)
Spaltenanzahl & Spaltentyp aller SELECT
müssen gleich sein!
Ergebnisse kombinieren: Andere
Analog:
UNION ALL
Wie UNION, aber doppelte bleiben erhalten
INTERSECT
nur die Zeilen, die in beiden Ergebnissen vorkommen
MINUS (oder EXCEPT)
nur die Zeilen, die im ersten
und nicht im zweiten Ergebnis vorkommen
Achtung:
MySQL kennt INTERSECT und MINUS nicht!
Zeilen auswählen: WHERE (1)
SELECT * FROM Schueler
WHERE Klasse = 'AIK2'
Nur die Zeilen, bei denen Klasse den Wert “AIK2” hat.
WHERE kommt nach dem FROM
und vor dem ORDER BY.
Hinweise zu WHERE:
●
Die Vergleiche sind
= (nicht == !)
<> (bei manchen DB auch !=)
<, <=, >, >=
Zeilen auswählen: WHERE (2)
●
●
●
●
Zahlen-Konstanten mit . und ohne Anführungszeichen,
Texte in einfachen oder doppelten Anführungszeichen.
Datums- und Zeitwerte werden richtig verglichen.
Datums-Konstanten in einfachen Anführungszeichen,
Schreibweise je nach DB und Spracheinstellung
('yyyy-mm-dd' oder 'dd.mm.yyyy').
Mehrere Bedingungen:
AND, OR, XOR, NOT, ( ) für Vorrang
... WHERE Klasse = 'AIK2'
AND GebDat < '1994-01-01' ...
Zeilen auswählen: WHERE (3)
●
Statt Spaltennamen und Konstanten können
in Vergleichen auch Funktionen verwendet werden.
Verfügbare Funktionen sind aber DB-abhängig.
... WHERE LENGTH(Nachname) > 10 ...
... WHERE ROUND(Note, 0) = 5 ...
... WHERE MONTH(GebTag) = MONTH(NOW()) ...
Prüfung auf Bereiche: BETWEEN
... WHERE Note BETWEEN 3.5 AND 4.5
(oder auch ... xxx NOT BETWEEN ... )
(klappt auch für Datum und Zeit)
Achtung:
Laut Standard: Einschließlich der Randwerte
(nicht jede Datenbank hält sich daran...)
Suche von Texten: LIKE
... WHERE Klasse LIKE 'AIK_' ...
... WHERE Nachname LIKE 'Franz%' ...
... WHERE Klasse LIKE '%Bac%' ...
... WHERE Klasse NOT LIKE '%Bac%' ...
... WHERE Klasse LIKE 'InfBac[246]' ...
_ steht für genau ein beliebiges Zeichen
% steht für beliebig viele (auch keine) beliebige Zeichen
[...] steht für genau eines der angegebenen Zeichen
(in MySQL muss man das anders machen)
Prüfung auf “kein Wert”: IS NULL
... WHERE Note IS NULL ...
(oder auch ... WHERE Note IS NOT NULL ... )
Nur für Spalten,
in denen fehlende Werte (NULL) erlaubt sind:
Wählt die Zeilen aus,
in denen diese Spalte keinen Wert enthält.
Tücken von NULL-Werten
Achtung:
“Normale” Prüfungen liefern
für NULL-Werte immer “falsch”!
Sinnloses Beispiel:
SELECT Vorname, Nachname FROM Schueler
WHERE Note = 6.0 OR Note <> 6.0
liefert nicht alle Schüler, sondern nur die mit Note!
Für Schüler mit leerem Feld Note
sind beide Prüfungen falsch!
Prüfung mit Liste von Werten: IN
... WHERE Klasse
IN ( 'AIK2', 'PhyTA2', 'InfBac2' ) ...
Wählt die Zeilen aus, die in dieser Spalte
einen der angegebenen Werte haben.
Das kann eine fixe Liste oder
eine Unterabfrage mit einspaltigem Ergebnis sein:
... WHERE S.Klasse IN (
SELECT DISTINCT P.Klasse
FROM Stundenplan AS P
WHERE P.Lehrer = 'Kusche'
) ...
Prüfung auf “Treffer”: EXISTS
SELECT S.Vorname, S.Nachname
FROM Schueler AS S
WHERE EXISTS (
SELECT 1
(egal!)
FROM Stundenplan AS P
WHERE S.Klasse = P.Klasse
AND P.Lehrer = 'Schneider'
)
Wählt alle Zeilen aus, für die die Unterabfrage
ein nichtleeres Ergebnis (mindestens 1 Zeile) liefert.
Zeilenanzahl beschränken: LIMIT
SELECT * FROM Schueler LIMIT 3
Nur max. die ersten 3 Zeilen
SELECT Nachname, Note FROM Schueler
WHERE Klasse = 'AIK2'
ORDER BY Nachname LIMIT 10,5
5 Zeilen ab der 11. Zeile (z.B. für seitenweise Ausgabe)
Achtung:
●
Die Nummerierung beginnt bei 0, nicht 1 !
●
LIMIT kommt nach dem WHERE und dem ORDER BY
●
Manche Datenbanken kennen kein LIMIT, sondern SELECT TOP ... (anders)
Werte kombinieren (1)
Im SELECT kann eine Aggregatsfunktion stehen,
die die Werte einer Spalte aller Ergebnis-Zeilen
zusammenfasst:
SELECT COUNT(Nachname) FROM Schueler
WHERE Klasse = 'AIK2'
SELECT MAX(GebDat) FROM Schueler
SELECT AVG(Note) FROM Schueler
WHERE Klasse = 'AIK2'
SELECT SUM(Fehlstunden) FROM Schueler
==> Ergebnis: Eine Zeile mit einem Wert
Werte kombinieren (2)
Ein SELECT mit einer Zeile und einer Spalte Ergebnis
(meist: Aggregatsfunktion)
kann als Vergleichswert im WHERE verwendet werden:
SELECT Vorname, Nachname, Note
FROM Schueler
WHERE Klasse = 'AIK2'
AND Note < (
SELECT AVG(Note) FROM Schueler
WHERE Klasse = 'AIK2'
)
Zeilen zusammenfassen (1)
●
●
●
GROUP BY fasst mehrere Zeilen mit gleichem Wert
der angegebenen Spalte zu einer Zeile zusammen
Pro Gruppe wird eine Ergebnis-Zeile geliefert
Im SELECT dürfen nur angegeben werden:
- Spalten, nach denen gruppiert wurde
(die im GROUP BY stehen)
- Aggregatsfunktionen
●
●
Aggregatsfunktionen werden pro Gruppe gerechnet
Mit HAVING können die aggregierten Ergebniszeilen
mit einer Bedingung gefiltert werden
Zeilen zusammenfassen (2)
SELECT Klasse,
MIN(Note), AVG(Note), MAX(Note)
FROM Schueler
GROUP BY Klasse
ORDER BY 3 DESC
SELECT Klasse, Wochentag,
COUNT(*) AS Stundenanzahl
FROM Stundenplan
WHERE Klasse LIKE 'AIK_'
GROUP BY Klasse, Wochentag
HAVING COUNT(*) > 6
Zeilen zusammenfassen (3)
Im GROUP BY stehen oft auch Datumsfunktionen:
SELECT Klasse, MONTH(GebDat), COUNT(*)
FROM Schueler
GROUP BY Klasse, MONTH(GebDat)
ORDER BY 1, 2
SELECT YEAR(GebDat), MONTH(GebDat), COUNT(*)
FROM Schueler
GROUP BY YEAR(GebDat), MONTH(GebDat)
ORDER BY 1, 2
Tabellen verknüpfen: FROM (1)
●
●
●
Im FROM können mehrere Tabellen angegeben werden
Es werden zuerst alle möglichen Kombinationen
aus irgendeiner Zeile der ersten Tabelle
mit irgendeiner Zeile der zweiten Tabelle
gebildet (also m mal n viele Zeilen, “Kreuzprodukt”).
Dann werden die kombinierten (zusammengehängten)
Zeilen mit WHERE gefiltert.
(In der Praxis wird das optimiert!)
Tabellen verknüpfen: FROM (2)
●
Das WHERE sollte mindestens eine Bedingung haben,
die eine Spalte der linken Tabelle
mit einer Spalte der rechten Tabelle vergleicht
(meist mit = )
SELECT DISTINCT S.Nachname, P.Lehrer
FROM Schueler AS S,
Stundenplan AS P
WHERE S.Klasse = P.Klasse
AND DayOfWeek(S.GebTag) = P.Wochentag
ORDER BY 1
Tabellen verknüpfen: FROM (3)
Man kann auch eine Tabelle mit sich selbst verknüpfen
(“Self Join”):
SELECT S1.GebDat, S1.Nachname, S2.Nachname
FROM Schueler AS S1, Schueler AS S2
WHERE S1.Klasse = S2.Klasse
AND S1.GebTag = S2.GebTag
AND S1.Nachname < S2.Nachname
ORDER BY 1
Tabellen verknüpfen: FROM (4)
Auch das Gruppieren funktioniert:
SELECT DISTINCT P.Fach, P.Lehrer,
MIN(S.GebDat), MAX(S.GebDat)
FROM Schueler AS S,
Stundenplan AS P
WHERE S.Klasse = P.Klasse
GROUP BY P.Fach, P.Lehrer
ORDER BY P.Fach, P.Lehrer
(die Abfrage ist so nicht wirklich clever...)
Tabellen verknüpfen: JOIN (1)
Die üblichere Schreibweise für 2 Tabellen ist JOIN:
FROM Tabelle1
INNER JOIN Tabelle2
ON Spalte1 = Spalte2 ...
WHERE ...
●
INNER kann man ev. weglassen, ist oft Default
●
Mit mehreren JOIN ... ON auch für mehrere Tabellen
●
Die “Verknüpfungsbedingung”
zwischen den Tabellen gehört ins ON,
andere Bedingungen (auf nur 1 Tabelle) ins WHERE
Tabellen verknüpfen: JOIN (2)
SELECT DISTINCT S.Nachname, P.Lehrer
FROM Schueler AS S
INNER JOIN Stundenplan AS P
ON S.Klasse = P.Klasse
WHERE DayOfWeek(S.GebTag) = P.Wochentag
ORDER BY 1
Im ON sollte als erstes
eine Fremdschlüssel-Spalte einer Tabelle
gleich (=)
der Primärschlüssel-Spalte der anderen Tabelle
gesetzt werden (siehe später!)
Tabellen verknüpfen: JOIN (3)
Neben INNER JOIN gibt es auch noch
LEFT JOIN: Alle Zeilen der linken Tabelle, auch wenn
die rechte Tabelle keinen passenden Eintrag hat
oder die JOIN-Spalte links NULL enthält
RIGHT JOIN: Alle Zeilen der rechten Tabelle, auch wenn
die linke Tabelle keinen passenden Eintrag hat oder...
FULL JOIN: Alle Zeilen beider Tabellen,
Zeilen mit erfüllten Bedingung werden kombiniert
(nicht in MySQL!)
Gibt es in einer der Tabellen keine passende Zeile,
werden stattdessen NULL-Werte verwendet.
Tabellen verknüpfen: JOIN (4)
SELECT DISTINCT L.Nachname, P.Klasse
FROM Lehrer AS L
LEFT JOIN Stundenplan AS P
ON L.Nachname = P.Lehrer
ORDER BY 1, 2
(liefert auch je eine Zeile für diejenigen Lehrer,
die in keiner Klasse unterrichten,
die Spalte Klasse bleibt dann leer)
Unterabfragen (1)
... sind ein SELECT (in ( ) ) innerhalb eines SELECT.
Inneres SELECT wird zuerst ausgerechnet.
3 Fälle:
●
●
●
Unter-SELECT liefert 1 Zeile mit 1 Spalte
(z.B. Aggregatsfunktion wie AVG, Bsp. siehe dort!)
==> Verwendung als Wert in einem Vergleich usw.
Unter-SELECT liefert mehrere Zeilen mit 1 Spalte
==> Verwendung als Liste im WHERE ... IN ... (Bsp. dort)
Unter-SELECT liefert bel. viele Zeilen und Spalten
==> Verwendung als Tabelle im FROM oder JOIN
Unterabfragen (2)
SELECT S.Nachname, S.Klasse, T.Stundenanz
FROM Schueler AS S, (
SELECT P.Klasse, COUNT(*) AS Stundenanz
FROM Stundenplan AS P
WHERE P.Wochentag = DayOfWeek(Now())
GROUP BY P.Klasse
) AS T
WHERE S.Klasse = T.Klasse
ORDER BY 1
Views (1)
Views sind “virtuelle” bzw. “berechnete” Tabellen:
●
●
●
Views sind SELECT-Befehle,
die fix mit einem Namen
in der DB gespeichert werden
(als Befehl, ohne das SELECT auszurechnen!).
Views-Namen können
wie Tabellen-Namen verwendet werden.
Bei jeder Verwendung eines View
wird das SELECT frisch ausgerechnet
und das Ergebnis wie eine Tabelle verwendet.
(d.h. die Daten eines View werden nicht fertig in der DB gespeichert!)
Views (2)
Anlegen eines View:
CREATE VIEW LehrerlisteV
AS SELECT DISTINCT Klasse, Lehrer
FROM Stundenplan
==> LehrerlisteV verhält sich wie eine Tabelle
mit 2 Spalten: Klasse und Lehrer
(welche Klasse hat welche Lehrer?)
Ein View kann auch mehrere Tabellen kombinieren
(z.B. mit JOIN) und sogar andere Views enthalten.
Views (3)
Verwendung von Views:
Views machen SELECT's einfacher und strukturierter
==> Leichter lesbar!
SELECT S.Nachname, S.Vorname, S.Klasse
FROM Schueler AS S, LehrerlisteV AS L
WHERE L.Lehrer = 'Kusche'
AND L.Klasse = S.Klasse
ORDER BY 3, 1
Der erste Schritt beim Entwurf
einer Datenbank-Anwendung:
Die Strukturierung
der Daten
Spalten-Typen
INT, DOUBLE, BOOL, ENUM, ...
DECIMAL = Fixkommazahlen ohne Rundungsfehler:
Mit Dezimalziffern statt binär gespeichert / gerechnet
CHAR = Strings fixer Länge, rechts mit ' ' aufgefüllt:
Etwas schneller als VARCHAR, aber braucht mehr Platz
VARCHAR = Strings variabler Länge, mit Maximal-Länge:
Platzsparender, aber minimal langsamer als CHAR
BINARY = beliebige Bytes fixer Länge
DATE, DATETIME, ...
BLOB = “Binary Large OBject” (> 64 KB): Dokumente, Grafiken,
unstrukturierte Messdaten, Verschlüsseltes, usw. .
Wird separat gespeichert ==> Langsamer als VARCHAR usw.!
TEXT = ähnlich BLOB für große Texte
Spalten-Eigenschaften (1)
●
●
●
●
NULL oder NOT NULL:
Darf eine Spalte NULL-Werte enthalten
(in manchen Zeilen leer bleiben)?
UNIQUE:
Müssen die Werte der Spalte eindeutig sein
oder sind doppelte Werte erlaubt? (==> Fehler beim Einfügen!)
AUTO_INCREMENT:
Die Spalte wird automatisch nummeriert:
Jeder neue Zeile bekommt einen um 1 höheren Wert.
DEFAULT:
Wird beim INSERT kein Wert für die Spalte gesetzt,
so bekommt sie den angegebenen Standard-Wert.
Spalten-Eigenschaften (2)
Bei CHAR, VARCHAR, TEXT, ...:
●
Zeichensatz:
Latin-1, Unicode, ...
●
Sortierreihenfolge:
Binär (nach ASCII- bzw. Unicode-Wert) oder
Länderspezifisch (Umlaute korrekt einsortiert)?
Groß-/Kleinschreibung ignorieren oder beachten
(Kleinbuchstaben hinter Großbuchstaben)?
Spalten-Constraints
Man kann für die Werte einer Spalte beliebige
Bedingungen bzw. Einschränkungen festlegen.
Beispiele:
●
CHECK ((Note >= 1.0) AND (Note <= 6.0))
●
CHECK (Land IN ('De', 'At', 'Ch'))
==> Zeilen mit Werten,
die der Bedingung nicht entsprechen
können gar nicht eingefügt werden!
Spalten mit Index
Ein Index (= Suchverzeichnis) über eine Spalte hilft ...
●
●
Zeilen mit bestimmten Werten in dieser Spalte
schneller zu finden
(ohne Index: Alle Zeilen der Tabelle durchsuchen!)
Zeilen der Tabelle nach dieser Spalte
schneller sortiert auszugeben (gleich sortiert lesen)
(ohne Index: Nachträglich sortieren!)
Aber ein Index kostet ...
●
●
Viel Platz (oft: Alle Werte doppelt in Index & Tabelle)
Viel Aufwand (bei jedem Einfügen / Ändern / Löschen:
Index muss auch geändert werden!)
Primärschlüssel-Spalte
Primärschlüssel
= Eindeutige Identifizierung jeder Zeile
(nie zwei Zeilen mit gleichem Primärschlüssel!)
= Internes Such- und Ordnungskriterium
Immer NOT NULL und UNIQUE !
Meist zugleich Index
Meist ein “Kunstwert”, oft AUTO_INCREMENT:
Matrikelnummer, Artikelnummer, Vertragsnummer, ...
Primärschlüssel als Kombination mehrerer Spalten:
Bei vielen DB möglich, aber nur selten sinnvoll (Beispiel kommt später!)
==> Oft: Lieber eine zusätzliche Spalte mit neuem Kunstwert erfinden!
Fremdschlüssel-Spalte (1)
Fremdschlüssel
= Primärschlüssel einer anderen Tabelle
= Querverbindung / Verknüpfung zwischen Tabellen
(meist für =-Bedingungen in JOINs)
Ein Fremdschlüssel ist ein Spalten-Constraint
==> In die Spalte können nur Werte eingefügt werden, die
in der Ziel-Tabelle als Primärschlüsselwert vorkommen!
(und ev. NULL)
Das ist das “C” (“Konsistenz”) in “ACID”:
Die Fremdschlüssel-Beziehungen sind immer konsistent:
“Kein Fremdschlüssel-Wert ohne passenden Primärschlüssel-Wert!”
Fremdschlüssel-Spalte (2)
DB macht automatisch umgekehrte Konsistenzprüfung:
Beim Löschen / Ändern einer Zeile der Ziel-Tabelle:
Wenn der betroffene Primärschlüssel-Wert
noch irgendwo als Fremdschlüssel-Wert vorkommt
==> Entweder Löschen / Ändern überhaupt verhindern
==> Oder in allen verknüpften Tabellen die
betroffenen Zeilen automatisch auch ändern / löschen
==> Oder in allen verknüpften Tabellen automatisch den
betreffenden Fremdschlüssel-Wert auf NULL setzen
(deshalb sind Fremdschlüssel-Spalten automatisch auch Index-Spalten)
Fremdschlüssel-Spalte (3)
Beispiele:
Artikelnummer = Primärschlüssel in Artikel-Tabelle
= Fremdschlüssel in Bestellungen-Tabelle
Postleitzahl = Primärschlüssel in PLZ-Verzeichnis
= Fremdschlüssel in Adress- bzw. Kunden-Tabelle
Kundennummer = Primärschlüssel in Kunden-Tabelle
= Fremdschlüssel in Bestellungen-Tabelle
In seltenen Fällen Selbstbezug: “Rekursiver” Fremdschlüssel
= Primärschlüssel der eigenen Tabelle
Beispiel: Tabelle der Abteilungen einer Firma (Schlüssel Abteilung)
Spalte “übergeordnete Abteilung” muss gültige Abteilung sein!
Tabellen-Design (1)
In der Theorie:
1. / 2. / 3. Normalform
In der Praxis:
“Redundanzen vermeiden”
Redundante Informationen
= doppelt gespeicherte Informationen
= Informationen, die sowieso schon
aus anderen Informationen folgen
(z.B. aus 'Datum' ergibt sich 'Wochentag',
aus 'Klasse' ergibt sich 'Klassenlehrer')
Tabellen-Design (2)
Probleme dadurch:
●
Platzbedarf
●
Änderungsaufwand
(Kunde zieht um ==> Adresse an x Stellen ändern?)
●
Inkonsistenzen, Probleme beim Suchen
(Plz 88316 ==> einmal “Isny”, einmal “Isny i.A.”, einmal “Isny / Allgäu”)
Ziel meist:
●
Lieber mehrere, aber kleinere Tabellen
●
Nicht zu viele Indices auf einer Tabelle
Tabellen-Design (3)
1. Schritt:
Nicht mehrere Informationen
in eine einzige Spalte packen!
Schlecht: Je eine einzige Spalte für
●
Anrede, Titel, Vorname, Name
●
Komplette Adresse (Straße, Nummer, PLZ, Ort, Land)
●
Mehrere Noten (als Text mit ' ' verbunden)
Tabellen-Design (4)
2. Schritt:
Dieselbe Info nicht doppelt
in mehreren Tabellen
Schlecht: Komplette Kundendaten
in Verträgen, Schadensfällen, Rechnungen, ...
==> Eine separate Kunden-Tabelle,
sonst überall nur Kundennummer
Schlecht: Komplette Lehrer-Daten (Name, Adr, Tel, ...)
in Stundenplan, Notenliste, Terminkalender, ...
==> Eine Lehrer-Tabelle mit Lehrer-Kurzzeichen als Schlüssel,
in anderen Tabellen überall nur Kurzzeichen
Tabellen-Design (5)
3. Schritt:
Dieselbe Info nicht mehrmals
in einer Tabelle
Schlecht: Fächer und Lehrer bei jedem Schüler,
steht x Mal gleich in der Schüler-Tabelle
==> Fächer und Lehrer woanders pro Klasse speichern,
bei jedem Schüler nur die Klasse eintragen
Schlecht: Postleitzahl, Bundesland und Ort
stehen wiederholt bei jeder Adresse
==> Adressen enthalten nur die Postleitzahl,
eigene PLZ-Tabelle mit Ort und Bundesland
Tabellen-Design (6)
Tipp dazu:
Prüfen: Hat eine Tabelle Spalten, ...
●
●
deren Wert durch etwas anderes als den Primärschlüssel
schon eindeutig festgelegt ist?
die nur voneinander,
aber nicht vom Primärschlüssel abhängig sind?
==> Zusammengehörige Spalten
in eigene Tabelle auslagern!
Tabellen-Design (7)
Beispiele:
●
'Ort' hängt nur von 'Plz' ab (ist durch 'Plz' festgelegt),
nicht von 'Kunden-Nr' bzw. 'Matrikel-Nr'
==> 'Ort' gehört nicht in Kunden- oder Schüler-Tabelle,
sondern in eigene Tabelle mit Primärschlüssel 'Plz'!
●
'Fach', 'Lehrer' und 'Klasse' sind
nur voneinander abhängig, nicht von Schüler
==> Nicht alle drei bei jedem Schüler speichern,
sondern nur die Klasse.
==> Eigene Tabelle mit 'Fach', 'Lehrer' und 'Klasse',
Primärschlüssel ist 'Fach' + 'Klasse'.
Und wenn unser Design fertig ist...
... legen wir die Tabellen
mit der grafischen Administrations-Oberfläche
der Datenbank an.
Das geht alles auch mit SQL-Befehlen
(siehe SQL-Übersicht!),
aber das ist für uns zu aufwändig.
Bisher können wir nur Daten lesen,
aber wir müssen auch...
Daten einfügen /
ändern / löschen
INSERT einzelner Zeilen
INSERT INTO tabelle (spaltenliste)
VALUES (werteliste)
Beispiel:
INSERT INTO Lehrer
(Vorname, Nachname, TelNr)
VALUES ("Klaus", "Kusche", 36)
Für Null-, Default- und Autoincrement-Spalten
muss man keine Werte angeben, sonst alle.
INSERT + SELECT (1)
INSERT INTO tabelle (spaltenliste)
SELECT ...
●
●
Das SELECT-Ergebnis muss so viele Spalten haben
wie in spaltenliste angegeben,
und der Typ muss zusammenpassen
(Zuordnung von links nach rechts).
Es werden so viele Zeilen eingefügt,
wie das SELECT Zeilen liefert.
INSERT + SELECT (2)
Beispiel:
INSERT INTO Schueler
(Vorname, Nachname, Jahrgang, Klasse, ... )
SELECT Vorname, Nachname, 2013, "AIK3", ...
FROM Schueler
WHERE Jahrgang = 2012
AND Klasse = "AIK2"
UPDATE (1)
UPDATE tabelle
SET spalte = neuer_wert
WHERE zeilenauswahl
●
●
●
In allen Zeilen der Tabelle tabelle,
in denen das WHERE zutrifft,
wird in der Spalte spalte
der Wert neuer_wert gespeichert.
Mit spalte1 = neuer_wert1, spalte2 = neuer_wert2, ...
können mehrere Spalten der gewählten Zeilen
auf einmal geändert werden.
Nicht angegebene Spalten bleiben unverändert.
UPDATE (2)
●
neuer_wert kann eine Konstante
oder eine Rechnung oder ein Sub-SELECT sein
Beispiele:
UPDATE Schueler SET Note = 6
(Kein WHERE ==> trägt in allen Zeilen 6 ein!)
UPDATE Schueler SET Note = Note - 0.5
WHERE Note > 3
(mach alle Noten über 3 um eine halbe Note besser)
UPDATE Schueler
SET Jahrgang = 2013, Klasse = "AIK3"
WHERE Jahrgang = 2012 AND Klasse = "AIK2"
DELETE
DELETE FROM tabelle
WHERE zeilenauswahl
●
●
Löscht alle Zeilen aus der Tabelle tabelle,
für die das WHERE zutrifft.
Es gibt ein UPDATE und ein DELETE zusammen mit JOIN,
aber das lassen wir aus...
Beispiele:
DELETE FROM Lehrer
(löscht alle Zeilen ==> leere Tabelle bleibt übrig)
DELETE FROM Schueler WHERE Note >= 4.5
Transaktionen in MySQL
●
MySQL konnte jahrelang keine Transaktionen
●
Auch heute noch ist standardmäßig eingestellt:
AUTOCOMMIT = 1
Bedeutung:
Nach jedem SQL-Befehl automatisch sofort COMMIT
==> Jeder Befehl für sich ist eine eigene Transaktion.
Vorteile:
●
Max. 3-5 mal schneller als mit Transaktionen
●
Braucht deutlich weniger RAM & Platte
Nachteile von AUTOCOMMIT
●
Nicht parallelitätsfest:
Gleichzeitig laufende Transaktionen sehen
●
●
●
●
“Zwischenstand” zwischen
zusammengehörigen Befehlen
Ev. sogar “halbe” Daten eines großen Befehls
Keine ROLLBACK-Möglichkeit,
z.B. bei Erkennen eines Problems.
Weniger ausfallsicher, z.B. bei Stromausfall
in zusammengehörenden oder großen Befehlen.
Klassisches Beispiel: Konto-Umbuchung
Transaktionen verwenden (1)
●
Variante 1 (nur MySQL): SET AUTOCOMMIT = 0
==> Automatisch neues START TRANSACTION
nach jedem COMMIT oder ROLLBACK
●
Variante 2: Expliziter Befehl START TRANSACTION
(oder in MySQL, nicht Standard: BEGIN)
==> Alle Befehle zwischen dem START TRANSACTION
und dem nächsten COMMIT oder ROLLBACK
gehören zu einer einzigen
Transaktion.
Transaktionen verwenden (2)
Was gehört in eine Transaktion?
Alle aufeinanderfolgenden Befehle,
●
●
die logisch zusammengehören
(zu einer Umbuchung oder Änderung,
zum Eintragen neuer Daten in mehreren Tabellen, ...)
die entweder ganz oder gar nicht
ausgeführt werden sollen.
... und nicht mehr!
(wegen hohem Overhead, Blockieren von Tabellen, ...)
COMMIT & ROLLBACK
COMMIT:
Alle Befehle der aktuellen Transaktion
●
werden endgültig in der Datenbank gespeichert
●
werden erst jetzt für andere Befehle sichtbar
oder
ROLLBACK:
Alle Befehle der aktuellen Transaktion
●
werden spurlos rückgängig gemacht
(passiert automatisch bei Fehlern & Ausfällen)