datenbanken und sqldatenbanken und sql klaus kusche, 2013-was ist eine datenbank ??? (hier nur...

Datenbankenund 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 / Tabellenerzeugt, 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 DBMSvon 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 Endemehrerer 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-Befehleeines 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 Datenzum nächsten konsistenten Zustand zu kommen.

Das Transaktions-Konzept (2)

Eine Transaktion kann

● entweder erfolgreich abgeschlossen werden:

“COMMIT” =

“Speichere alle Änderungen dieser Transaktion endgültigund 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 Tabellenpassen 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)

Auslesenvon Daten

(incl. Suche, Sortierung, Gruppierung,Verknüpfung von Daten mehrerer Tabellen...)

SELECT ... (was?)FROM ... (aus welcher Tabelle?)WHERE ... (Suchbedingung)

(ev. plus ORDER BY ... GROUP BY ... usw.)

(auch: Kopieren von Daten in eine andere Tabelle)


Einfügen / Ändern / Löschenvon Daten

INSERT ... (was?) INTO ... (in welche Tabelle?)

UPDATE ... (welche Tabelle?) SET ... (welche Spalten auf welche Werte?)WHERE ... (in welchen Zeilen?)

DELETE FROM ... (in welcher Tabelle?)WHERE ... (welche Zeilen?)


Anlegenvon 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-Indexauf einer bestehenden Tabelle)


Ändernvon Tabellen

ALTER TABLE ... (Tabellenname)ADD ... (Spalte dazu)DROP ... (Spalte löschen)MODIFY ... (Spaltentyp usw. ändern)CHANGE ... (Spaltenname ändern)

ALTER INDEX ... (analog für Index)


Löschenvon 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 Detailsdes SELECT !

SELECT Schlüsselwörter usw. SELECT

DISTINCTWHEREAND ORINBETWEENLIKEORDER BYCOUNTGROUP BYHAVINGALIAS

JOINFULL JOINUNIONUNION ALLINTERSECTMINUSSUBSTRINGCASE...

Funktionen,Unterabfragen,Kaskadierungen, ...

Einfaches SELECT

SELECT * FROM SchuelerGib alle Zeilen und alle Spalten

der Tabelle Schueler aus.

SELECT Vorname, Nachname, GebDatFROM 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 SchuelerORDER 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 SchuelerORDER BY Klasse, Note

Zuerst nach Klasse, bei gleicher Klasse nach Note...

Abkürzungen für Tabellen: AS

SELECT S.Note, S.NachnameFROM Schueler AS SORDER 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 GeburtstagFROM Schueler

Spaltenüberschrift “Geburtstag” statt “GebDat”.

SELECT Semester, 1800 AS Semestergebuehr,Semester * 1800 AS GesamtbetragFROM Schueler

1. Spalte “Semester” = Semester aus der Tabelle, 2. Spalte “Semestergebuehr” = Wert fix 18003. 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 SchuelerBackupFROM Schueler

Macht eine Kopie SchuelerBackupder Tabelle Schueler.

SELECT DISTINCT Klasse INTO KlassenlisteFROM 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 S.Vorname, S.Nachname, 'Student'FROM Schueler AS S

UNIONSELECT L.Vorname, L.Nachname, 'Professor'

FROM Lehrer AS L

Vereinigt die Ergebnis-Zeilen mehrerer SELECT(mehrfache, gleiche Zeilen in der Vereinigungwerden nur einmal ausgegeben)

Spaltenanzahl & Spaltentyp aller SELECTmüssen gleich sein!

Ergebnisse kombinieren: Andere

Analog:

UNION ALLWie 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-WertenAchtung:

“Normale” Prüfungen liefernfür NULL-Werte immer “falsch”!

Sinnloses Beispiel:

SELECT Vorname, Nachname FROM SchuelerWHERE Note = 6.0 OR Note 6.0

liefert nicht alle Schüler, sondern nur die mit Note!

Für Schüler mit leerem Feld Notesind beide Prüfungen falsch!

Prüfung mit Liste von Werten: IN

... WHERE KlasseIN ( 'AIK2', 'PhyTA2', 'InfBac2' ) ...

Wählt die Zeilen aus, die in dieser Spalteeinen 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 PWHERE P.Lehrer = 'Kusche'

) ...

Prüfung auf “Treffer”: EXISTS

SELECT S.Vorname, S.NachnameFROM Schueler AS SWHERE EXISTS (

SELECT 1 (egal!)FROM Stundenplan AS PWHERE S.Klasse = P.KlasseAND P.Lehrer = 'Schneider'

)Wählt alle Zeilen aus, für die die Unterabfrageein nichtleeres Ergebnis (mindestens 1 Zeile) liefert.

Zeilenanzahl beschränken: LIMIT

SELECT * FROM Schueler LIMIT 3Nur 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-Zeilenzusammenfasst:

SELECT COUNT(Nachname) FROM SchuelerWHERE Klasse = 'AIK2'

SELECT MAX(GebDat) FROM Schueler

SELECT AVG(Note) FROM SchuelerWHERE 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, NoteFROM SchuelerWHERE Klasse = 'AIK2'AND Note < (

SELECT AVG(Note) FROM SchuelerWHERE Klasse = 'AIK2'

)

Zeilen zusammenfassen (1)

● GROUP BY fasst mehrere Zeilen mit gleichem Wertder 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 Ergebniszeilenmit einer Bedingung gefiltert werden


SELECT Klasse, MIN(Note), AVG(Note), MAX(Note)

FROM SchuelerGROUP BY KlasseORDER BY 3 DESC

SELECT Klasse, Wochentag, COUNT(*) AS Stundenanzahl

FROM StundenplanWHERE Klasse LIKE 'AIK_'GROUP BY Klasse, WochentagHAVING COUNT(*) > 6


Im GROUP BY stehen oft auch Datumsfunktionen:

SELECT Klasse, MONTH(GebDat), COUNT(*)FROM SchuelerGROUP BY Klasse, MONTH(GebDat)ORDER BY 1, 2

SELECT YEAR(GebDat), MONTH(GebDat), COUNT(*)FROM SchuelerGROUP 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 Tabellegebildet (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 Tabellemit einer Spalte der rechten Tabelle vergleicht(meist mit = )

SELECT DISTINCT S.Nachname, P.LehrerFROM Schueler AS S,

Stundenplan AS PWHERE S.Klasse = P.Klasse

AND DayOfWeek(S.GebTag) = P.WochentagORDER 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 S2WHERE S1.Klasse = S2.Klasse

AND S1.GebTag = S2.GebTagAND 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.KlasseGROUP BY P.Fach, P.LehrerORDER 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 Tabelle1INNER JOIN Tabelle2ON 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


SELECT DISTINCT S.Nachname, P.LehrerFROM Schueler AS SINNER JOIN Stundenplan AS PON S.Klasse = P.KlasseWHERE DayOfWeek(S.GebTag) = P.WochentagORDER BY 1

Im ON sollte als erstes

eine Fremdschlüssel-Spalte einer Tabellegleich (=)

der Primärschlüssel-Spalte der anderen Tabelle

gesetzt werden (siehe später!)


Neben INNER JOIN gibt es auch noch

LEFT JOIN: Alle Zeilen der linken Tabelle, auch wenndie rechte Tabelle keinen passenden Eintrag hatoder die JOIN-Spalte links NULL enthält

RIGHT JOIN: Alle Zeilen der rechten Tabelle, auch wenndie 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.


SELECT DISTINCT L.Nachname, P.KlasseFROM Lehrer AS LLEFT JOIN Stundenplan AS PON L.Nachname = P.LehrerORDER 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.StundenanzFROM Schueler AS S, (

SELECT P.Klasse, COUNT(*) AS StundenanzFROM Stundenplan AS PWHERE P.Wochentag = DayOfWeek(Now())GROUP BY P.Klasse

) AS TWHERE S.Klasse = T.KlasseORDER BY 1

Views (1)

Views sind “virtuelle” bzw. “berechnete” Tabellen:

● Views sind SELECT-Befehle,die fix mit einem Namenin der DB gespeichert werden(als Befehl, ohne das SELECT auszurechnen!).

● Views-Namen können wie Tabellen-Namen verwendet werden.

● Bei jeder Verwendung eines Viewwird das SELECT frisch ausgerechnetund 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 LehrerlisteVAS SELECT DISTINCT Klasse, LehrerFROM Stundenplan

==> LehrerlisteV verhält sich wie eine Tabellemit 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.KlasseFROM Schueler AS S, LehrerlisteV AS LWHERE L.Lehrer = 'Kusche'

AND L.Klasse = S.KlasseORDER 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 / gerechnetCHAR = Strings fixer Länge, rechts mit ' ' aufgefüllt:

Etwas schneller als VARCHAR, aber braucht mehr PlatzVARCHAR = Strings variabler Länge, mit Maximal-Länge:

Platzsparender, aber minimal langsamer als CHARBINARY = beliebige Bytes fixer LängeDATE, 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 seinoder 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) oderLänderspezifisch (Umlaute korrekt einsortiert)?

Groß-/Kleinschreibung ignorieren oder beachten(Kleinbuchstaben hinter Großbuchstaben)?

Spalten-Constraints

Man kann für die Werte einer Spalte beliebigeBedingungen bzw. Einschränkungen festlegen.

Beispiele:

● CHECK ((Note >= 1.0) AND (Note Zeilen mit Werten,

die der Bedingung nicht entsprechenkönnen gar nicht eingefügt werden!

Spalten mit Index

Ein Index (= Suchverzeichnis) über eine Spalte hilft ...

● Zeilen mit bestimmten Werten in dieser Spalteschneller 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!”


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 diebetroffenen Zeilen automatisch auch ändern / löschen

==> Oder in allen verknüpften Tabellen automatisch denbetreffenden Fremdschlüssel-Wert auf NULL setzen

(deshalb sind Fremdschlüssel-Spalten automatisch auch Index-Spalten)


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. NormalformIn 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 doppeltin 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 Ortstehen 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ächeder 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-Spaltenmuss man keine Werte angeben, sonst alle.

INSERT + SELECT (1)

INSERT INTO tabelle (spaltenliste)SELECT ...

● Das SELECT-Ergebnis muss so viele Spalten habenwie 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 SchuelerWHERE Jahrgang = 2012

AND Klasse = "AIK2"

UPDATE (1)

UPDATE tabelleSET spalte = neuer_wertWHERE 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.5WHERE 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"

DELETEDELETE FROM tabelleWHERE 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 = 1Bedeutung:

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 TRANSACTIONnach jedem COMMIT oder ROLLBACK

● Variante 2: Expliziter Befehl START TRANSACTION

(oder in MySQL, nicht Standard: BEGIN)

==> Alle Befehle zwischen dem START TRANSACTIONund 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 nichtausgeführt werden sollen.

... und nicht mehr! (wegen hohem Overhead, Blockieren von Tabellen, ...)

COMMIT & ROLLBACKCOMMIT:

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)

Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23Slide 24Slide 25Slide 26Slide 27Slide 28Slide 29Slide 30Slide 31Slide 32Slide 33Slide 34Slide 35Slide 36Slide 37Slide 38Slide 39Slide 40Slide 41Slide 42Slide 43Slide 44Slide 45Slide 46Slide 47Slide 48Slide 49Slide 50Slide 51Slide 52Slide 53Slide 54Slide 55Slide 56Slide 57Slide 58Slide 59Slide 60Slide 61Slide 62Slide 63Slide 64Slide 65Slide 66Slide 67Slide 68Slide 69Slide 70Slide 71Slide 72Slide 73Slide 74Slide 75Slide 76Slide 77Slide 78Slide 79Slide 80Slide 81Slide 82Slide 83Slide 84Slide 85Slide 86Slide 87Slide 88Slide 89Slide 90Slide 91Slide 92Slide 93Slide 94Slide 95Slide 96

datenbanken und sqldatenbanken und sql klaus kusche, 2013-was ist eine datenbank ??? (hier nur...

Documents