DBMS Michael Grube - MG Software Entwicklung

Microsoft SQL
Server
DatabaseManagement
Relationale DBMS
Michael Grube (MCSA und MCT)
MG SOFTWARE ENTWICKLUNG
1
Inhaltsverzeichnis
Vorwort ................................................................................................................................................... 3
Der Datenbankentwurf und Lebenszyklus .............................................................................................. 4
Die Unterteilung beginnt mit .......................................................................................................... 4
Das Datenbankmodell ............................................................................................................................. 5
Beispiel für ein Relationales Datenbankmodell .............................................................................. 6
ER-Modell ................................................................................................................................................ 7
Attribute in einer Entität ......................................................................................................................... 8
Primärschlüssel (Primary Key) ................................................................................................................. 8
Eindeutiger Primärschlüssel ............................................................................................................ 9
Zusammengesetzter Primärschlüssel .............................................................................................. 9
Künstlicher Primärschlüssel............................................................................................................. 9
Fremdschlüssel (Foreign Key) .......................................................................................................... 9
1:1 Beziehung in relationalen Datenbanken ................................................................................. 10
1:n Beziehung in relationalen Datenbanken ................................................................................. 10
m:n Beziehung in relationalen Datenbanken ................................................................................ 10
Normalisierung ...................................................................................................................................... 11
Erste Normalform (1NF) ................................................................................................................ 12
Zweite Normalform (2NF) ............................................................................................................. 12
Dritte Normalform (3NF) ............................................................................................................... 13
Referentielle Datenintegrität ................................................................................................................ 16
Einfüge-Anomalie .......................................................................................................................... 16
Änderungs-Anomalie ..................................................................................................................... 16
Lösch-Anomalie ............................................................................................................................. 16
Datenbank Index ................................................................................................................................... 17
Welche Arten von Datenbank-Indizes existieren? ........................................................................ 17
SQL (Structed Query Language) ............................................................................................................ 19
SQL – eine Datenbanksprache ....................................................................................................... 19
SQL SELECT Befehl ......................................................................................................................... 19
SQL DISTINCT Befehl ...................................................................................................................... 20
SQL AND & OR Operatoren ........................................................................................................... 22
SQL IN Operator............................................................................................................................. 24
SQL BETWEEN Befehl .................................................................................................................... 25
SQL LIKE Befehl .............................................................................................................................. 25
SQL ORDER BY ............................................................................................................................... 27
SQL GROUP BY Befehl.................................................................................................................... 28
MGS (2016) for DBMS-Systems
2
SQL Abarbeitungsreihenfolge........................................................................................................ 29
Aggregieren ........................................................................................................................................... 29
SQL SUM() Funktion ...................................................................................................................... 29
SQL Min() Funktion ........................................................................................................................ 30
SQL Max() Frunktion ...................................................................................................................... 30
SQL Count() Frunktion ................................................................................................................... 31
Mengenoperationen und Unterabfragen.............................................................................................. 32
Union ............................................................................................................................................. 32
Union all......................................................................................................................................... 32
Intersect......................................................................................................................................... 32
Except ............................................................................................................................................ 33
ALL und ANY Operanten ................................................................................................................ 33
IN Operant / Unterabfragen .......................................................................................................... 34
Verknüpfen von Objekten (JOINS)......................................................................................................... 35
Wie funktioniert ein SQL Join? ...................................................................................................... 36
Datenmanipulation (Insert, Update, Delete) ........................................................................................ 37
INSERT ........................................................................................................................................... 37
UPDATE.......................................................................................................................................... 38
DELETE ........................................................................................................................................... 39
OUTPUT-Klausel............................................................................................................................. 40
MERGE-Klausel .............................................................................................................................. 42
MGS (2016) for DBMS-Systems
3
Vorwort
Bevor die Daten fließen …
Um relationale Datenbanken entwickeln zu können, sollte man zuvor die Überlegung anstreben, was
genau und in welcher Form gespeichert werden soll und wie Sicher muss das sein.
Die Entwicklung und die Verwaltung sind zwei verschiedene Dinge, die nur erschwert kombinierbar
sind.
Grundsätzlich ist der erste Entwurf einer Datenbank eine „Vorab“-Geschichte.
Die Datenbank weißt bestimmte Eigenschaften und Attribute auf, die wie schon erwähnt, zuvor
festgelegt werden. Im Nachhinein die Struktur zu ändern, kann unter Umständen zu Problemen
führen.
Aus diesem Grund werden die Datenbanken „fast“ immer auf die klassische Methode entworfen.
Früher nannte man das noch Reißbrett. Da im Bereich der IT heutzutage alles am PC entworfen wird,
was natürlich auch schneller geht, schleichen sich unter Umständen kleine Fehler mit ein, die auf die
klassische Art wahrscheinlich sofort aufgefallen wären.
Fehler die während der Entwicklung auffallen, sind zunächst nicht weiter tragisch und können
korrigiert werden. Sind aber erst einmal Daten gespeichert, ist eine nachträgliche Änderung nur
schwer Umsetzbar, da die vorhandenen Daten das Ändern unter Umständen nicht unterstützen.
Aber nicht nur die Fehler, sondern auch Änderungen der physischen Datenstruktur führt zu
Problemen.
Moderne Systeme, wie den Microsoft SQL Server® oder vergleichbare DBMS-Systeme können Fehler
erkennen, bevor sie entstehen. Aber auch hier gilt, dass System ist immer nur so Schlau wie die
Person, die das Ganze bedient. ☺
Als Liebhaber von Datenbanken bin ich im Namen Microsoft und deren Produkte deutschlandweit
Unterwegs und gebe Referate und Trainings in allen Schichten der Industrie und Seminarhäuser
greifen auf meine Erfahrung als Trainer zurück, welches ich sehr gerne in Anspruch nehme.
Mir macht es immer wieder Freude zu sehen, wie die Teilnehmer mehr und mehr Verständnis über
diese Speicherform, von Informationen, kennen lernen und dass auch in der Praxis richtig umsetzen.
In diesem Handout sind Beispiele, Grafiken und Texte erwähnt, die ich in diesem Zusammenhang
selbst recherchiert habe. Sollten Sie Texte und Grafiken erkennen, wundern Sie sich nicht. Ich habe
Namenhafte Seiten besucht, um Ihnen einen Überblick außerhalb fachlicher Literatur geben zu
können.
MGS (2016) for DBMS-Systems
4
Der Datenbankentwurf und Lebenszyklus
Die Entwicklung einer Datenbank wird in unterschiedlichen Phasen unterteilt und ist je nach
Software-Anforderung in Projektteilschritte unterteilt.
Zunächst findet jedoch die Analyse statt. Darin wird der Umfang der Software bestimmt und der
daraus resultierenden Datenbank. Die Datenbank an sich ein Lebenszyklus gemeinsam mit der
Software, die je nach Anforderungen ergänzt werden kann. Sind jedoch bestimmte Workflows
notwendig, ist ein Rückplanung (entfernen von Funktionen / Tabellen) nur noch bedingt möglich.
Daher bestimmt die genaue Analyse, die Meilensteine der Projektphasen, die maximalen Kosten, die
Datenbankstruktur und das Funktionen.
Die Unterteilung beginnt mit
a.
b.
c.
d.
e.
f.
Anforderungen
Konzeptioneller Entwurf
Logischer Entwurf
Physischer Entwurf
Test
Implementierung der Anwendung
Die Datenbank wird unter den folgenden Schemas konzipiert. Sie muss Vollständigkeit und
Korrektheit aufweisen, Minimalität und Modifizierbarkeit muss gegeben sein.
Die Datenbank kann in unterschiedlichsten Projektmodellen geplant werden.
Meistens wird das klassische „Wasserfallmodell“ eingesetzt. Sollte die Zeit das zulassen, werden wir
ein solches Beispiel erarbeiten.
Die Datenbank besteht aus Tabellen, Sichten (Abfragen), Funktionen und Prozeduren. Diese
Auflistung nennt man auch Objekte.
Tabellen dienen dazu, alle der Struktur entsprechenden Daten zu verwalten. Unabhängig davon, wie
Komplex und welche Datenmenge dahintersteckt.
Zu den wichtigsten Bestandteilen der Tabelle dienen die Datentypen und Integrität. Daher ist die
Normalisierung der Daten sehr wichtig. Sie dient dazu, die Integrität der Daten aufrecht zu halten,
sowie Redundanzen zu vermeiden. Dazu aber später mehr…
Sehen Sie sich das nachfolgende Kapitel über das Datenbankmodell an.
MGS (2016) for DBMS-Systems
5
Das Datenbankmodell
Ein Datenbankmodell ist die theoretische Grundlage für eine Datenbank und legt fest, auf welche
Art und Weise die Daten in dem Datenbanksystem gespeichert und bearbeitet werden können.
Ein Datenbankmodell von heute kann sehr komplex werden, da immer mehr Informationen
in einem Datenbankmodell abgebildet und modelliert werden müssen.
Die Wahl des richtigen Datenbankmodells ist heute wichtiger denn je, da die Datenmengen
in einem enormen Tempo anwachsen und die Anforderungen sich ständig ändern.
Aufbau eines Datenbankmodells
Das Datenbankmodell bildet das Fundament und besteht aus 3 wichtigen Faktoren:
Generische Datenstruktur
Generische Operatoren
Integritätsbedingungen
Das weitverbreitetste Datenbankmodell ist das relationale Datenbankmodell.
Der Vollständigkeit werden aber auch die anderen Datenbankmodelle erwähnt.
Hierarchisches Datenbankmodell
Netzwerkdatenbankmodell
Objektorientiertes Datenbankmodell
Das Relationale Datenbankmodell ist das am weitverbreitetste Modell, das in der
Datenbankentwicklung als Standard genutzt wird. Die Grundlage dieses Datenbankmodells ist die
Relation.
Sie stellt eine mathematische Beschreibung einer Tabelle und ihre Beziehung zu anderen möglichen
Tabellen dar. Die Operationen auf diese Relationen werden durch die relationale Algebra bestimmt.
Des Weiteren ist die relationale Algebra auch die Grundlage für die Datenbanksprache SQL.
Auch wenn die mathematische Gewichtung und die Abstraktion der Daten in diesem Modell sehr
stark ist, sind relationale Datenbankmodelle vergleichsweise sehr einfach und flexibel zu erstellen
und zu steuern.
MGS (2016) for DBMS-Systems
6
Eigenschaften vom Relationalen Datenbankmodell
Das relationale Datenbankmodell besteht aus drei wichtigen Faktoren:
Eine Datenbank kann man sich unter einer Ansammlung von Tabellen und Beziehungen
vorstellen, die miteinander verknüpft sind. Jede Zeile (auch Tupel genannt) in einer Tabelle
ist ein Datensatz (Record). Jedes Tupel besteht aus einer großen Reihe von Attributen
(Eigenschaften), den Spalten der Tabelle. Ein Relationsschema legt dabei die Anzahl und den
Typ der Attribute für eine Tabelle fest.
Des Weiteren können Verknüpfungen (Beziehungen) über sogenannte Primärschlüssel
hergestellt werden, um bestimme Attribute, die den gleichen Primärschlüssel oder in einer
Detailtabelle als Fremdschlüssel besitzen, abzufragen.
Beispiel für ein Relationales Datenbankmodell
Ein gutes Beispiel für ein Relationales Datenbankmodell ist ein Modell, das eine Beziehung
zwischen einem Kunden, seiner Rechnung, den Rechnungspositionen und den darin
enthaltenen Artikeln widerspiegelt:
Dieses Beispiel für ein Relationales Datenbankmodell kann nur durch eine korrekte Normalisierung
und deren Normalformen erstellt werden. Um dieses Beispiel genauer zu verstehen, sehen Sie
weiter unten das Thema „Normalisierung“ genauer an. Weiterhin muss man sich mit den Datentypen
und die Primär,- und Fremdschlüssel beschäftigen. Zunächst aber schauen wir auf das ER-Modell.
MGS (2016) for DBMS-Systems
7
ER-Modell
Das Entity-Relationship Modell – abgekürzt mit ER-Modell oder ERM – dient als Grundlage für einen
Datenbankentwurf. Bevor mittels SQL angefangen wird, Tabellen und Beziehungen anzulegen, wird
erst mal mittels ER-Modell geplant, wie die Datenbankstruktur aufgebaut und funktionieren soll.
Entity Relationship Modell erstellen – Aber warum?
Der Einsatz von ER-Modellen ist in der Praxis ein gängiger Standard für die Datenmodellierung, auch
wenn es unterschiedliche grafische Darstellungsformen von Datenbankmodellen gibt.
Mithilfe des Entity Relationship Modells soll eine Typisierung von Objekten, ihrer relationalen
Beziehungen untereinander und der über sie zu führenden Attribute, stattfinden.
Guten Datenbankentwickler können in kurzer Zeit sehr komplexe Entity Relationship Modelle
verstehen und umsetzen.
Entitäten, Attribute, Beziehungen – Entity-Relationship-Modell
Die Grundelemente eines jeden Entity-Relationship-Modells bilden:
Entitäten, Beziehungen und Attribute. Diese werden grafisch wie folgt dargestellt:
Um was genau es sich bei diesen Elementen handelt, klären die folgenden Punkte:
Eine Entität ist ein individuell identifizierbares Objekt der Wirklichkeit.
Eine Beziehung ist eine Verknüpfung / Zusammenhang zwischen zwei oder mehreren
Entitäten.
Ein Attribut ist eine Eigenschaft, die im Kontext zu einer Entität steht.
MGS (2016) for DBMS-Systems
8
Erklärung zum ER-Modell: Ein Mitarbeiter hat einen Namen. Ein Projekt hat einen Namen, ein Datum
und ein Budget. Ein Mitarbeiter kann mehrere Projekte leiten, aber nur ein Projekt kann von genau
einem Mitarbeiter geleitet werden. Diese Notation nennt man Chen-Notation und ist ein gängiger
Standard in der Praxis der Datenmodellierung. Diese Notation beinhaltet die Kardinalität, die näher
im Kapitel Beziehung in Datenbanken behandelt wird.
Entitäten in einer Datenbank
In einer Datenbank ist eine Entität ein konkretes Objekt bzw. ein konkreter Sachverhalt der sich
eindeutig von anderen Entitäten des gleichen Entitätstyps unterscheidet.
Ein Entität-Typ beschreibt die Ausprägungen eines Objektes oder Sachverhaltes durch die Angabe
von Attributen.
Er gibt demnach an, welche Eigenschaften eine konkrete Entität aufweist. Übertragen auf eine
Datenbank, ist eine Entität ein Tupel (Datensatz) einer Relation (Tabelle).
Die Relation stellt den Entität-Typ dar und deren Spalten die Attribute. Eine Eigenschaft entspricht
dem konkreten Attributwert.
Die einzelnen Entitäten werden in den unterschiedlichen Tabellen erfasst, sodass diese einen für den
Anwender definierten Ausschnitt aus der realen Welt darstellen.
Die Tabelle „Kunde“ enthält demnach Entitäten (= Tupel bzw. Datensätze) mit den Attributen Name
und weiteren persönlichen Angaben, die wiederum unterschiedliche Attributwerte annehmen.
Attribute in einer Entität
Jede Entität besitzt eine bestimmbare Anzahl an Attributen (Ausprägungen bzw. Eigenschaften), die
sich eindeutig von anderen Entitäten des gleichen Entitätstyps abgrenzen. Eine Eigenschaft ist ein
konkreter Attributwert, den ein zuvor definiertes Attribut annehmen kann. Die Attribute stellen
einen „Bauplan“ dar, der eine abstrakte Abbildung der Wirklichkeit ist.
Welche Arten von Attributen gibt es?
Die Attribute in einer Entität können unterschiedlich aufgebaut sein. Man unterscheidet zwischen
zusammengesetzte, mehrwertige und abgeleitete Attribute.
Wichtig ist, wenn man eine Entität modelliert, dass schon immer vorab die erste Normalform der
Normalisierung befolgt wird. So umgeht man spätere Modellierungsprobleme, wenn die zweite und
dritte Normalform anstehen.
Schauen wir uns die Attribute und die Keys entsprechend an. Einzelne oder zusammengesetzte Keys
bilden die Eindeutigkeit der Datensätze.
Primärschlüssel (Primary Key)
Der Primärschlüssel kommt in relationalen Datenbanken zum Einsatz und wird zur eindeutigen
Identifizierung eines Datensatzes verwendet. In einer normalisierten Datenbank besitzen alle
Tabellen einen Primärschlüssel.
Der Wert eines Primärschlüssels muss in einer Tabelle einmalig sein, da er jeden Datensatz eindeutig
kennzeichnet. Des Weiteren wird er häufig als Datenbank-Index verwendet, um die Daten auf der
Festplatte abzulegen.
MGS (2016) for DBMS-Systems
9
Welche Arten von Primärschlüssel gibt es?
Der Primärschlüssel einer Relation kann unterschiedlich aufgebaut sein. Man unterscheidet
zwischen eindeutige, zusammengesetzte und künstliche Primärschlüssel.
Eindeutiger Primärschlüssel
Hierbei handelt es sich um einen eindeutigen Schlüssel der in einer Spalte der Tabelle gespeichert
wird. Als Spalte kann ein Attribut des Datensatzes verwendet werden, das für jeden Eintrag in der
Tabelle einen einmaligen Wert annimmt. Als eindeutiges Primärschlüsselattribut könnte
beispielsweise die Sozialversicherungsnummer in einer Mitarbeitertabelle verwendet werden.
Zusammengesetzter Primärschlüssel
Ist ein Datensatz anhand eines Attributes nicht eindeutig identifizierbar, so kann der Primärschlüssel
auch aus einer Kombination mehrerer Attribute bestehen. Dabei muss sichergestellt werden, dass
jede dieser Kombinationen nur einmalig auftritt. Ein zusammengesetzter Primärschlüssel kann z.B.
der Vor- und Nachname, sowie das Geburtsdatum sein.
Künstlicher Primärschlüssel
Gibt es in einer Tabelle keine eindeutigen Spalten bzw. Kombinationen aus Spalten, so kann auch auf
einen künstlichen Schlüssel zurückgegriffen werden. Dieser ist auch als Surrogate Key bekannt und
wird als zusätzliche Spalte in einer Tabelle eingefügt. In der Praxis wird häufig eine fortlaufende
Ganzzahlenfolge verwendet, um einen Datensatz eindeutig identifizieren zu können.
Fremdschlüssel (Foreign Key)
Der Fremdschlüssel kann Bestandteil einer Tabelle in einer relationalen Datenbank sein. Dabei
handelt es sich um eine Schlüsselspalte, die auf einen Primärschlüssel einer anderen oder aber
derselben Tabelle verweist.
Welche Fremdschlüsselarten gibt es?
Es kann sich dabei um einen einfachen oder zusammengesetzten Schlüssel handeln. Das hängt davon
ab, wie der Primärschlüssel der referenzierten Tabelle aufgebaut ist.
Aufgrund der referentiellen Integrität, kann der Fremdschlüssel nur Werte annehmen die in der
Referenztabelle vorhanden sind. Zudem kann eine beliebige Anzahl von Datensätzen den gleichen
Fremdschlüsselwert aufweisen.
Beispiel für den Einsatz eines Fremdschlüssels
In einer normalisierten Tabelle die Kontakte verwaltet, kann beispielsweise zu einer Person ein
Unternehmen referenziert werden. In der Tabelle „Ansprechpartner“ wird „Susi Meier“ und ihre
Telefonnummer angelegt. Ihr Unternehmen wird aus der Tabelle „Unternehmen“ referenziert, das
wäre dann beispielsweise die „ABC GmbH“. Über die Referenz können bei einer Abfrage die Anschrift
und andere Fakten zum Unternehmen aus der Tabelle „Unternehmen“ abgerufen werden.
Beziehungen in Datenbanken
Zwischen Relationen (Tabellen/Entitäten) können Beziehungen in einer Datenbank bestehen.
Angenommen man hat eine Relation „Mütter“ und eine Relation „Kinder“ – denkbar wären nun vier
Möglichkeiten von Assoziationen / Beziehungen zwischen den Tabellen.
MGS (2016) for DBMS-Systems
10
Beziehungen zwischen Tabellen erstellen – So geht’s
In einem Datenbankmodell können folgende Beziehungen auftreten:
Jede Mutter hat exakt ein Kind.
Jede Mutter hat ein oder kein Kind.
Jede Mutter hat mindestens ein Kind.
Jede Mutter hat eine beliebige Anzahl von Kindern (Mehr als 1, dann spricht man von Geschwistern)
Kardinalität von Beziehungen in relationalen Datenbanken
Die Kardinalität von Beziehungen definiert wie viele Entitäten eines Entitätstyps mit genau einer
Entität des anderen am Beziehungstyp beteiligten Entitätstyps (und umgekehrt) in
Relation(Beziehung) stehen können oder müssen. Die Kardinalität von Beziehungen ist in
relationalen Datenbanken in folgenden Formen vorhanden: 1:1 Beziehung, 1:n Beziehung und m:n
Beziehung.
1:1 Beziehung in relationalen Datenbanken
In einer „eins zu eins“-Beziehung in relationalen Datenbanken ist jeder Datensatz in Tabelle A genau
einem Datensatz in Tabelle B zugeordnet und umgekehrt. Diese Art von Beziehung sollte in der
Modellierung vermieden werden, weil die meisten Informationen, die auf diese Weise in Beziehung
stehen, sich in einer Tabelle befinden können. Eine 1:1-Beziehung verwendet man nur, um eine
Tabelle aufgrund ihrer Komplexität zu teilen oder um einen Teil der Tabelle aus Gründen der
Zugriffsrechte zu isolieren.
1:n Beziehung in relationalen Datenbanken
Eine „eins zu viele“-Beziehung relationalen Datenbanken ist der häufigste Beziehungstyp. In einer
1:n-Beziehung können einem Datensatz in Tabelle A mehrere passende Datensätze in Tabelle B
zugeordnet sein, aber einem Datensatz in Tabelle B ist nie mehr als ein Datensatz in Tabelle A
zugeordnet.
m:n Beziehung in relationalen Datenbanken
Bei „viele zu viele“-Beziehung in relationalen Datenbanken können jedem Datensatz in Tabelle A
mehrere passende Datensätze in Tabelle B zugeordnet sein und umgekehrt. Diese Beziehungen
können nur über eine dritte Tabelle, eine Verbindungstabelle C, realisiert werden. Die
Verbindungstabelle C enthält in der Regel nur die Fremdschlüssel der beiden anderen Tabellen (A/B).
Der Primärschlüssel der Verbindungstabelle wird aus diesen beiden Fremdschlüsseln gebildet. Daraus
folgt, dass eine m:n Beziehung in Wirklichkeit zwei 1:n Beziehungen sind.
MGS (2016) for DBMS-Systems
11
Normalisierung
Unter Normalisierung eines relationalen Datenbankmodells versteht man die Aufteilung von
Attributen in mehrere Relationen (Tabellen) mithilfe der Normalisierungsregeln und deren
Normalformen, sodass eine Form entsteht, die keine vermeidbaren Redundanzen mehr enthält.
Warum wird eine Normalisierung durchgeführt?
Ziel der Normalisierung ist eine redundanzfreie Datenspeicherung zu erstellen. Redundanzfrei
bedeutet, dass Daten entfernt werden können, ohne dass es zu Informationsverlusten kommt.
Weiterhin soll die Normalisierung Anomalien entfernen. Im Normalisierungsprozess gibt es fünf
Normalformen, welche im Folgenden genauer erklärt werden.
Ziele der Normalisierung
Beseitigung von Redundanzen
Vermeidung von Anomalien (funktionelle und transitive Abhängigkeiten)
Erstellung eines klar strukturierten Datenbankmodells
Hier möchte ich auf Redundanzen, sowie 1-3 Normalform eingehen.
Redundanzen in Datenbanken
Redundanzen in Datenbanken sind ein Zeichen für ein schlechtes Datenbankdesign. Redundanzen
sind doppelte Informationen in einer Datenbank bzw. Datenbank-Tabelle. Man spricht von einer
redundanzfreien Datenbank, wenn alle doppelte Informationen entfernt werden können, ohne das
ein Informationsverlust stattfindet.
Wie kann ich Redundanzen vermeiden?
Redundanzen können mittels der Normalisierung entfernt werden. Die Normalisierung entfernt
doppelte Informationen, ohne das ein Informationsverlust in anderen Relationen stattfindet.
Wann lässt man Redundanzen zu?
Ab und zu kann eine Redundanz aber auch wahre Wunder wirken, wenn es um die Performance in
einer Datenbank geht. Besonders in anderen Fällen von relationalen Datenbanken wie im Data
Warehouse oder im Business Intelligence-Bereich werden ganz bewusst Redundanzen eingebaut, um
zeit- und performanceaufwändige SQL-Abfragen zu verbessern. In solchen Fällen spricht man von
der „Kontrollierten Redundanz“, die Mithilfe der Denormalisierung von Datenbanken erreicht wird.
MGS (2016) for DBMS-Systems
12
Erste Normalform (1NF)
Die Erste Normalform (1NF) ist dann gegeben, wenn alle Informationen in einer Tabelle atomar
vorliegen. Diesen Satz kann man in vielen Datenbank Büchern nachlesen, doch was bedeutet das
wirklich?
Es bedeutet, dass jede Information innerhalb einer Tabelle eine eigene Tabellenspalte bekommt
und zusammenhängende Informationen, wie zum Beispiel die Postleitzahl (PLZ) und der Ort, nicht in
einer Tabellenspalte vorliegen dürfen.
Erste Normalform Definition
Ein Relationstyp (Tabelle) befindet sich in der ersten Normalform (1NF), wenn die Wertebereiche
der Attribute des Relationstypen atomar sind.
Erste Normalform Beispiel
Gegeben sei die folgende Rechnungstabelle:
ReNr.
187
Datum
01.01.2016
Name
Max Mustermann
Straße
Musterstr. 1
Ort
12345 Musterort
Artikel
Stift
Anzahl
2
Preis
1,00 €
Nach der Anwendung der Ersten Normalform (1NF) sieht das Ergebnis wie folgt aus:
ReNr
187
Datum
01.01.2016
Name
Mustermann
Vorname
Max
Straße
Musterstr. 1
PLZ
12345
Ort
Musterort
Artikel
Stift
Anzahl
2
Preis
1,00 €
Die erste Normalform (1NF) ist dann erfüllt, wenn die Wertebereiche der Attribute des
Relationstypen atomar sind.
Zweite Normalform (2NF)
Die zweite Normalform ist ein wichtiger Schritt zu einer voll normalisierten relationalen Datenbank.
Sie prüft, ob eine vollständige funktionale oder nur eine funktionale Abhängigkeit von Werten zu
einer bestimmten Teilmenge existiert.
Die zweite Normalform wird meistens schon indirekt erreicht, wenn der Datenbankentwickler mit
der Erstellung eines ER-Modells beschäftigt ist. Die logische Aufspaltung von komplexen
Sachverhalten zwingt den Datenbankentwickler Geschäftsprozesse in Relationen abzubilden.
Gute Datenbankentwickler brauchen für die Zweite Normalform kein Modell auf dem Papier,
sondern können Geschäftsprozesse direkt mit dem Kunden besprechen und zeitnah in einer
Datenbankapplikation implementieren.
MGS (2016) for DBMS-Systems
13
Zweite Normalform Definition
Ein Relationstyp (Tabelle) befindet sich genau dann in der zweiten Normalform (2NF), wenn er sich
in der ersten Normalform (1NF) befindet und jedes Nichtschlüsselattribut von jedem
Schlüsselkandidaten voll funktional abhängig ist.
Zweite Normalform Beispiel
Gegeben sei wieder folgende Rechnungstabelle:
ReNr
187
Datum
01.01.2016
Name
Muster
Vorname
Max
Straße
Musterstr. 1
PLZ
12345
Ort
Musterort
Artikel
Stift
Stk.
2
Preis
1,00 €
Nach der Anwendung der Zweiten Normalform (2NF) sieht das Ergebnis wie folgt aus:
Neue Tabelle: „Rechnung“
Nr
187
Datum
01.01.2016
KundenNr
007
Neue Tabelle: „Kunde“
KundeNr
007
Name
Mustermann
Vorname
Max
Straße
Musterstr. 1
PLZ
12345
Ort
Musterort
Neue Tabelle: „Rechnungsposition“
RePosNr
1
ReNr
187
ArtNr
69
Anzahl
2
Neue Tabelle „Artikel“
ArtNr
69
Artikel
Stift
Preis
1,00 €
Da ein Name (Nachname) nicht eindeutig ist, wird jedem Kunden eine Kundennummer (KundeNr)
zugeordnet. Diese ist der Primärschlüssel der neuen Tabelle „Kunde“. Danach wird das gleiche mit
den Artikeln durchgeführt. Des Weiteren wird eine Rechnungspositionstabelle eingebaut, da eine
Rechnung von einem Kunden eine Vielzahl von Rechnungspositionen mit verschiedenen Artikeln
beinhalten kann.
Die Spalten, die von einem Schlüsselkandidaten nicht vollständig funktional abhängig sind, werden
in einer Untertabelle ausgelagert. Der Teil des Schlüsselkandidaten, von dem eine ausgelagerten
Spalte funktional abhängig ist, wird Primärschlüssel der neuen Tabelle. In der zweiten Normalform
werden auch die ersten Beziehungen in Datenbanken festgelegt.
Dritte Normalform (3NF)
Die Dritte Normalform ist das Ziel einer erfolgreichen Normalisierung in einem relationalen
Datenbankmodell.
Sie verhindert einerseits Anomalien und Redundanzen in Datensätzen und andererseits bietet sie
genügend Performance für SQL-Abfragen.
MGS (2016) for DBMS-Systems
14
Die Dritte Normalform ist oft ausreichend, um die perfekte Balance aus Redundanz, Performance
und Flexibilität für eine Datenbank zu gewährleisten.
Sehr gute Datenbankentwickler können mit der Dritten Normalform die perfekte Balance in ihrem
Datenmodell herstellen, um neue Probleme aus der realen Welt in ein relationales Datenbankmodell
einzupflegen.
Dritte Normalform Definition
Ein Relationstyp befindet sich genau dann in der dritten Normalform (3NF), wenn er sich in der
zweiten Normalform (2NF) befindet und kein Nichtschlüsselattribut transitiv von einem
Kandidatenschlüssel abhängt.
Dritte Normalform Beispiel
Gegeben sei die folgende Kundentabelle:
KundenNr
007
Name
Mustermann
Vorname
Max
Straße
Musterstr. 1
PLZ
12345
Ort
Musterort
Nach der Anwendung der Dritten Normalform (3NF) sieht das Ergebnis wie folgt aus:
Neue Tabelle: „Kunden“
KundenNr
007
Name
Mustermann
Vorname
Max
Straße
Musterstr. 1
PLZ
12345
Neue Tabelle: „Postleitzahl“
PLZ
12345
Ort
Musterort
In der Tabelle „Kunden“ sind die Attribute „Vorname“, „Straße“ und „PLZ“ abhängig vom Attribut
„Name“, nicht vom Primärschlüssel „KundenNr“. Außerdem ist „Ort“ abhängig von „PLZ“.
Die transitiv abhängigen Spalten werden in eine weitere Untertabelle ausgelagert, da sie nicht
direkt vom Schlüsselkandidaten abhängen, sondern nur indirekt.
Normalisierung und Abhängigkeiten
Die Normalisierung von Daten in einer Datenbank bringt funktionale Abhängigkeiten zwischen
diesen Informationen mit sich.
Jeder Relationstyp hat verschiedene Informationen in sich und besitzt damit auch unterschiedliche
Ausprägungen von funktionalen Abhängigkeiten.
Dabei wird zwischen der funktionalen, voll funktionalen und transitiven Abhängigkeit
unterschieden. Im folgenden Artikel stellen wir euch die jeweiligen Abhängigkeitsformen und deren
Ausprägung kurz vor.
Funktionale Abhängigkeit
Eine Funktionale Abhängigkeit zwischen Attribut Y und Attribut X liegt dann vor, wenn es zu jedem X
genau ein Y gibt.
MGS (2016) for DBMS-Systems
15
Voll funktionale Abhängigkeit
Eine vollständig funktionale Abhängigkeit liegt dann vor, wenn dass Nicht-Schlüsselattribut nicht nur
von einem Teil der Attribute eines zusammengesetzten Schlüsselkandidaten funktional abhängig ist,
sondern von allen Teilen eines Relationstyps. Die vollständig funktionale Abhängigkeit wird mit der
2. Normalform (2NF) erreicht.
Transitive Abhängigkeit
Eine transitive Abhängigkeit liegt dann vor, wenn Y von X funktional abhängig und Z von Y, so ist Z
von X funktional abhängig. Diese Abhängigkeit ist transitiv. Die transitive Abhängigkeit wird mit 3.
Normalform (3NF) erreicht.
MGS (2016) for DBMS-Systems
16
Referentielle Datenintegrität
Im Bereich der relationalen Datenbanken wird die referentielle Integrität dazu verwendet die
Konsistenz und die Integrität der Daten sicherzustellen. Dazu werden Regeln aufgestellt, wie und
unter welchen Bedingungen ein Datensatz in die Datenbank eingetragen wird.
Bei der referentiellen Integrität können Datensätze die einen Fremdschlüssel aufweisen nur dann
gespeichert werden, wenn der Wert des Fremdschlüssels einmalig in der referenzierten Tabelle
existiert. Im Falle, dass ein referenzierter Wert nicht vorhanden ist, kann der Datensatz nicht
gespeichert werden.
Warum wird die Referentielle Integrität benötigt?
Eine Datenbank kann schnell in einen inkonsistenten Zustand geraten. Im ungünstigsten Fall liegt
eine nicht-normalisierte Datenbank vor, die starke Redundanzen aufweist. Dabei können Anomalien
im Datenbestand auftreten, die verschiedene Formen annehmen. Man spricht hier von Einfüge-,
Lösch- und Änderungsanomalien. Tritt eine oder mehrerer dieser Anomalien auf, kann das zur
Verfälschung oder Löschung von Informationen führen.
Einfüge-Anomalie
Eine Einfüge-Anomalie tritt auf, wenn ein Datensatz gespeichert werden soll und dieser keine oder
kein eindeutigen Primärschlüsselwerte aufweist. Das Einfügen in eine Tabelle ist somit nicht möglich.
Informationen können nicht gespeichert werden und gehen womöglich verloren. Das kann zum
Beispiel der Fall sein, wenn für die Speicherung der Kundendaten zu Verifizierungszwecken die
Personalausweisnummer als Primärschlüssel verwendet wird, diese aber leider vom Sachbearbeiter
nicht erfasst werden konnte. Der Datensatz des Kunden kann nicht gespeichert werden.
Änderungs-Anomalie
Man spricht von einer Änderungs-Anomalie, wenn eine Entität redundant in einer oder sogar in
mehreren Tabellen enthalten ist und bei einer Aktualisierung nicht alle berücksichtigt werden.
Dadurch kommt es zur Inkonsistenz im Datenbestand. Es kann möglicherweise nicht mehr
nachvollzogen werden welcher Wert der gültige Datensatz ist. Dieser Sachverhalt lässt sich gut an
einer Auftragstabelle darstellen. Diese speichert neben der Auftragsnummer auch den Namen eines
Kunden und dessen Bestellung. Ein Kunde kann mehrere Bestellungen aufgegeben haben, wobei jede
Bestellung in einem Datensatz erfasst wird. Wird nun aufgrund eines Schreibfehlers nachträglich der
Name des Kunden „Reiher“ in „Reier“ bei einem Datensatz geändert, führt dies zu einem
inkonsistenten Datenbestand. Nach der Änderung liegen demnach Aufträge für scheinbar zwei
verschiedene Kunden vor und zwar für einen Kunden „Reiher“ und einen Kunden „Reier“.
Lösch-Anomalie
Enthalten die Datensätze einer Tabelle mehrere unabhängige Informationen, so kann es leicht zu
Lösch-Anomalien kommen. Da sich die Daten in einem nicht-normalisierten Zustand befinden, kann
durch Löschen eines Datensatzes ein Informationsverlust entstehen. Die Ursache liegt darin, dass in
einer Tabelle unterschiedliche Sachverhalte gespeichert werden. Am Beispiel einer nichtnormalisierten Mitarbeitertabelle soll dies kurz skizziert werden. In der Mitarbeitertabelle werden
die Personalnummer, der Name und die Abteilung gespeichert. Der Mitarbeiter „Krause“, der als
einziger in der Abteilung „Lager“ war, ist aus dem Unternehmen ausgetreten und wird daher aus der
MGS (2016) for DBMS-Systems
17
Datenbank gelöscht. Da die Abteilung in der gleichen Tabelle gespeichert wird, verschwindet das
„Lager“ aus der Datenbank, da „Herr Krause“ ja als einziger dieser Abteilung zugeordnet war.
Datenbank-Anomalien auflösen
Die beschriebenen Anomalien treten durch ein schlechtes Datenbank-Design auf. Daraus ergibt sich
auch die redundante Datenhaltung. Um diese zu vermeiden, müssen die Tabellen einer Datenbank
normalisiert werden. Die Normalisierung umfasst in der Praxis drei Stufen und sorgt für eine
redundanzfreie und nach Entitätstyp getrennte Datenhaltung.
Datenbank Index
Der Datenbankindex ist eine Datenstruktur mit deren Hilfe die Abfrageoptimierung gesteigert
werden kann. Mittels einer Indextabelle werden die Daten sortiert auf dem Datenträger abgelegt.
Der Index selbst stellt einen Zeiger dar, der entweder auf einen weiteren Index oder auf einen
Datensatz zeigt. Dadurch findet eine Trennung von Daten- und Index-Strukturen statt.
Welche Arten von Datenbank-Indizes existieren?
Bei Datenbanken unterscheidet man generell zwei Arten von Indizes. Zum einen gibt es gruppierte
Indizes (Clustered Index). Zum anderen gibt es nicht-gruppierte Indizes (Nonclustered Index). Ohne
Indizes auf einer Tabelle müsste die Datenbank die Informationen (Datensatz) sequentiell suchen,
was selbst mit modernster Hardware und Software viel Zeit beanspruchen kann.
Gruppierte Indizes (Clustered Index)
Bei der Verwendung eines gruppierten Index werden die Datensätze entsprechend der
Sortierreihenfolge ihres Index-Schlüssels gespeichert. Wird für eine Tabelle beispielsweise eine
Primärschlüssel-Spalte „ID“ angelegt, so stellt diese den Index-Schlüssel dar. Pro Tabelle kann nur ein
gruppierter Index erstellt werden. Dieser kann jedoch aus mehreren Spalten zusammengesetzt sein.
Nicht-gruppierte Indizes (Nonclustered Index)
Besitzt eine Tabelle einen gruppierten Index, so können weitere nicht-gruppierte Indizes angelegt
werden. Dabei zeigen die Einträge des Index auf den Speicherbereich des gesamten Datensatzes. Die
Verwendung eines nicht-gruppierten Index bietet sich an, wenn regelmäßig nach bestimmten
Werten in einer Spalte gesucht wird z.B. dem Namen eines Kunden.
Bei einer Abfrage wird nun zuerst nach dem Namen gesucht. Werden weitere Daten zum Kunden
benötigt, so können diese über den gruppierten Index, der mit dem Namen abgelegt wurde,
abgerufen werden. Bei einem nicht-gruppierten Index ist es nicht notwendig, dass dessen Werte
eindeutig sein müssen. Zudem kann auch dieser aus mehreren Spalten zusammengesetzt sein.
Darüber hinaus gibt es noch weitere sehr spezielle und zum Teil proprietäre Indizes, die in
bestimmten Datenbanken verwendet werden. Beispielsweise der Bitmap-Index, der im Data
Warehouse eingesetzt wird.
Vorteile von Datenbank Indizes
Der Einsatz von Indizes empfiehlt sich für Datenbanken die großen Datenmengen speichern und sehr
häufig abgefragt werden. Hier kommt es darauf an welche Informationen dabei eine zentrale Rolle
spielen.
MGS (2016) for DBMS-Systems
18
Welcher Index bei einer Abfrage tatsächlich verwendet wird, entscheidet in letzter Instanz der
Abfrageoptimierer der Datenbank. Dieser erstellt für eine Abfrage mehrere Ausführungspläne, um
die Kosten für die Abfrage zu ermitteln. Wird diese nun ausgeführt, wählt er den kostengünstigsten
Ausführungsplan. Dieser berücksichtigt nicht nur Indizes, sondern auch die Systemauslastung.
Nachteile eines Datenbank Index
Das Anlegen von Indexstrukturen führt zur Belegung von Plattenspeicher und kann bei einer großen
Anzahl von Indizes einen nicht unerheblichen Speicherverbrauch verursachen.
Ein weiterer Nachteil ist, dass der Einsatz von Indizes zu einem größeren Aufwand beim Schreiben
von Datensätzen führt. Das Datenbankmanagementsystem muss in diesem Fall auch den Index
berücksichtigen und diesen entsprechend laden. Hier gilt, je mehr Indizes eine Tabelle hat, desto
größer ist der Performance-Verlust beim Speichern neuer Datensätze.
Nachdem Sie nun eine Menge über die Datenbankstrukturen, Aufbau und Struktur der Tabellen
gelesen haben, schauen wir uns nun an, wie Sie den Inhalt der Datenbank auslesen können, sofern
Sie die Berechtigung dazu haben. Gerade der MS SQL Server liegt ein hohes Maß an Sicherheit
zugrunde.
MGS (2016) for DBMS-Systems
19
SQL (Structed Query Language)
Die Abkürzung SQL steht für „Structured Query Language“ und ist eine Datenbanksprache zur
Erstellung von Datenbankstrukturen in relationalen Datenbanken, sowie zum Bearbeiten und
Abfragen die darauf basierenden Datenbestände.
SQL – eine Datenbanksprache
Die Datenbanksprache SQL basiert auf der relationalen Algebra, ihre Syntax ist recht einfach
aufgebaut und semantisch an die englische Umgangssprache angelehnt.
In der Bezeichnung SQL ist das englische Wort “query” (Abfrage) enthalten. Mit Abfragen werden
Daten in der Datenbank abgerufen und dem Benutzer mittels einer Anwendersoftware bereitgestellt.
SQL SELECT Befehl
Der SQL SELECT Befehl ist der Grundstein für zahlreiche SQL-Abfragen, die auf eine Datenbank
ausgeführt werden können. Mithilfe des SQL Select Befehls ist es möglich, Daten aus einer oder
mehreren Tabellen über ein JOIN (Verbindung) abzufragen.
Ein SQL Select Befehl sollte niemals mit einem Stern, also SELECT * FROM Tabelle
ausgeführt werden, da dann alle Treffer der SQL SELECT Abfrage zurückgeliefert werden.
Ein Datenbankentwickler sollte seine Suchmenge immer eingrenzen und evtl. mit der
Funktion TOP, z.B. SELECT TOP 10 * FROM Tabelle1 eingrenzen.
SQL SELECT Syntax
Die SQL Syntax einer SELECT-Abfrage ist wie folgt aufgebaut:
SELECT Spaltenname1, Spaltenname2, Spaltenname3 FROM Tabellenname
Mithilfe des SELECT Befehls wird definiert, welche Spalten einer Tabelle nach der Ausführung der
Abfrage dargestellt werden sollen.
SQL SELECT Beispiel
Gegeben sei folgende Tabelle namens „Mitarbeiter“:
Nachname
Müller
Meier
Schulze
Niebaum
Richter
Vorname
Daniel
Dennis
Holger
Michael
Julia
Gehalt
2435
2090
3410
3675
1201
Abteilung
Einkauf
Vertrieb
Produktmanagement
Geschäftsführung
Empfang
Möchte man von dieser Tabelle nur den Nachnamen der Mitarbeiter sowie ihr Gehalt ausgeben, ist
folgender SQL – Befehl einzugeben:
SELECT Nachname, Gehalt FROM MITARBEITER
MGS (2016) for DBMS-Systems
20
Das Ergebnis der SQL Select Abfrage würde wie folgt aussehen:
Nachname
Müller
Meier
Schulze
Niebaum
Richter
Gehalt
2435
2090
3410
3675
1201
SQL DISTINCT Befehl
Der SQL DISTINCT Befehl wird in einer SQL Select Abfrage direkt hinter dem Select platziert. Mithilfe
des DISTINCT Befehls werden Redundanzen, die in einer Tabelle auftreten können, ausgeblendet und
die Werte werden jeweils nur einmal angezeigt.
Der Befehl Distinct kommt weniger in relationalen Datenbanken vor. Er wird häufiger in einem Data
Warehouse und der Report-Erstellung genutzt, also dort wo durchaus Redundanzen auftreten
können.
SQL DISTINCT Syntax
Die SQL Syntax einer Select-Distinct-Abfrage ist wie folgt aufgebaut:
SELECT DISTINCT Spaltenname FROM Tabellenname
Mithilfe des DISTINCT wird definiert, welche Spalte auf Redundanzen geprüft werden soll.
SQL DISTINCT Beispiel
Gegeben sei folgende Tabelle namens „PKW“:
PKWNR
1
2
4
5
6
Modell
Auto A
Auto B
Auto A
Auto D
Auto E
Preis
65.000,00€
72.000,00€
80.000,00€
80.000,00€
78.000,00€
Anhand der Tabelle können wir erkennen, dass sie nicht redundanzfrei ist. Um nun alle PKW
redundanzfrei anzuzeigen, wird der SQL Distinct Befehl wie folgt eingesetzt:
MGS (2016) for DBMS-Systems
21
SELECT DISTINCT Modell FROM PKW
Das Ergebnis würde wie folgt aussehen:
Modell
Auto A
Auto B
Auto D
Auto E
Obwohl das Modell „Auto A“ in der Tabelle PKW zweimal vorkommt, wird es in der SQL Abfrage
mittels Distinct-Befehl nur einmal angezeigt und gilt somit als redundanzfrei.
SQL WHERE Befehl
Mithilfe des SQL WHERE-Befehls werden in SQL Abfragen nur bestimmten Datensätze ausgewählt.
Der SQL WHERE-Befehl funktioniert im Prinzip wie ein Filter, der es ermöglicht, nur Datensätze
anzuzeigen, die bestimmte Kriterien erfüllen.
Soll ein SQL Statement eine bestimmte Bedingung erfüllen, muss eine WHERE-Bedingung eingebaut
und erfüllt werden, damit die Abfrage eine Ergebnismenge liefern kann.
SQL WHERE Syntax
Die SQL Syntax einer Select-Abfrage mit WHERE ist wie folgt aufgebaut:
SELECT Spaltenname FROM Tabellenname WHERE Spaltenname = Wert
Mithilfe des WHERE wird definiert, welche Bedingung positiv erfüllt werden muss, damit die richtige
Ergebnismenge geliefert wird. Die Ergebnismenge kann mithilfe der folgenden Vergleichsoperatoren
oder Prädikate, spezifiziert werden:
T-SQL-Sprachelemente: Prädikate und Operatoren
Elemente:
Prädikate
Prädikate und Operatoren:
IN, BETWEEN, LIKE
Vergleichsoperatoren
=, >, <, >=, <=, <>, !=, !>, !<
Logische Operatoren
AND, OR, NOT
arithmetische Operatoren
+, -, *, /, %
Verkettung
+
SQL WHERE Beispiel
Gegeben sei folgende Tabelle namens „Mitarbeiter“:
Nachname
Heinrich
Löffler
Schulz
Schröder
Nussbaum
MGS (2016) for DBMS-Systems
Vorname
Daniel
Dennis
Holger
Michael
Julia
Gehalt
2435
2090
3410
3675
1201
Abteilung
Einkauf
Vertrieb
Produktmanagement
Geschäftsführung
Empfang
22
Nun möchte man den Nachnamen der Mitarbeiter und deren Gehälter auflisten. Allerdings soll die
Spalte „Gehalt“ eine bestimmte Bedingung erfüllen. Es sollen alle Mitarbeiter und deren Gehälter
angezeigt werden, bei denen das Gehalt über 2095 Euro beträgt.
Das SQL Statement mit der WHERE-Bedingung würde wie folgt aussehen:
SELECT NACHNAME, VORNAME, GEHALT, ABTEILUNG FROM MITARBEITER WHERE GEHALT > 2095
Das Ergebnis würde wie folgt aussehen:
Nachname
Heinrich
Schulz
Schröder
Vorname
Daniel
Holger
Michael
Gehalt
2435
3410
3675
Abteilung
Einkauf
Produktmanagement
Geschäftsführung
In diesem Beispiel haben wir das „größer als“ – Zeichen verwendet – einen Vergleichsoperator. SQL
kann mit allen gängigen Vergleichsoperatoren (siehe oben) umgehen. Wir könnten also genauso gut
eine Abfrage erstellen, welche nur Datensätze anzeigt, bei denen das Gehalt „kleiner als“ 2095 Euro
beträgt.
SQL AND & OR Operatoren
Die SQL AND & OR Operatoren werden in SQL-Where Bedingungen eingebaut, um bestimme
Abfrageergebnisse ein- bzw. auszugrenzen. Der Einsatz der Operatoren folgt der booleschen Algebra,
die man aus dem Mathematikunterricht kennt und einfach zu verstehen.
Mithilfe des AND Operators werden SQL Bedingungen zusammengefasst. Der OR Operator sorgt für
eine Unterscheidung zwischen zwei oder mehreren SQL Bedingungen.
Wichtig ist zu beachten, dass ein OR Operator in der booleschen Algebra immer schwerer wiegt, als
ein AND Operator.
SQL AND Syntax
Die SQL Syntax einer Select-Abfrage mit WHERE und AND ist wie folgt aufgebaut:
SELECT Spaltenname FROM Tabellenname
WHERE Spaltenname1 = Wert1
AND Spaltenname2 = Wert2
SQL OR Syntax
Die SQL Syntax einer Select-Abfrage mit WHERE und OR ist wie folgt aufgebaut:
SELECT Spaltenname FROM Tabellenname
WHERE Spaltenname1 = Wert1
OR Spaltenname2 = Wert2
MGS (2016) for DBMS-Systems
23
SQL WHERE AND Beispiel
Gegeben sei eine Tabelle namens „Mitarbeiter“:
Nachname
Gerhardt
Müller
Schulze
Opitz
Meier
Vorname
Sabine
Dennis
Holger
Heiko
Julia
Gehalt
2435
2090
3410
3675
1201
Geschlecht
w
m
m
m
w
Nun möchte man alle Mitarbeiter, deren Gehalt und ihr Geschlecht anzeigen. Dabei sollen zwei
Bedingungen gelten: Es sollen alle Frauen, die mehr als 2000 Euro verdienen, angezeigt werden:
Das SQL-Statement mit der AND-Bedingung würde wie folgt aussehen:
SELECT NACHNAME, VORNAME, GEHALT, GESCHLECHT
FROM MITARBEITER
WHERE GESCHLECHT ='w'
AND GEHALT > 2000
Das Ergebnis würde wie folgt aussehen:
Nachname
Gerhardt
Vorname
Sabine
Gehalt
2435
Geschlecht
w
SQL WHERE OR Beispiel
Gegeben sei wieder die Tabelle namens „Mitarbeiter“:
Nachname
Gerhardt
Müller
Schulze
Opitz
Meier
Vorname
Sabine
Dennis
Holger
Heiko
Julia
Gehalt
2435
2090
3410
3675
1201
Geschlecht
w
m
m
m
w
Nun möchten man alle Mitarbeiter und deren Gehälter anzeigen. Dabei sollen zwei Bedingungen mit
einer OR-Bedingung verknüpft werden und gelten: Es sollen alle Mitarbeiter, die weniger als 2000
Euro und mehr als 3000 Euro verdienen, angezeigt werden:
Das SQL-Statement mit der OR-Bedingung würde wie folgt aussehen:
SELECT NACHNAME, VORNAME, GEHALT, GESCHLECHT FROM MITARBEITER
WHERE GEHALT < 2000
OR GEHALT > 3000
Das Ergebnis würde wie folgt aussehen:
Nachname
Schulze
Opitz
Meier
MGS (2016) for DBMS-Systems
Vorname
Holger
Heiko
Julia
Gehalt
3410
3675
1201
Geschlecht
m
m
w
24
SQL IN Operator
Der SQL IN Operator wird in SQL-Where Bedingungen eingebaut, um mehrere Abfrageergebnisse in
einer SQL-Anweisung zu bündeln. Damit kann der IN Operator leicht mehrere OR Operatoren
ersetzen und vereinfacht damit die Struktur von komplexen OR-Bedingungen.
Der SQL IN Operator kann auch Ergebnismengen mit NOT ausschließen. Um bestimmte
Ergebnismengen auszuschließen, muss das Wort NOT hinzugefügt werden.
SQL IN Syntax
Die SQL Syntax einer Select-Abfrage mit WHERE und IN ist wie folgt aufgebaut:
SELECT Spaltenname FROM Tabellenname WHERE Spaltenname IN ('Wert1','Wert2')
SQL IN Beispiel
Gegeben sei eine Tabelle namens „Mitarbeiter“:
Nachname
Reinhardt
Schönbaum
Wegner
Schulz
Richter
Vorname
Melinda
Jakob
Lutz
Dorothea
Heiko
Gehalt
2435
2090
3410
1201
3675
Geschlecht
w
m
m
w
m
Nun möchten wir alle Mitarbeiter mit dem folgenden Vornamen ermitteln: Heiko, Dorothea
und Melinda:
Das SQL-Statement mit der IN-Bedingung würde wie folgt aussehen:
SELECT NACHNAME, VORNAME, GEHALT, GESCHLECHT
FROM MITARBEITER
WHERE VORNAME
IN ('Heiko', 'Dorothea', 'Melinda')
Das Ergebnis würde wie folgt aussehen:
Nachname
Reinhardt
Schulz
Richter
Vorname
Melinda
Dorothea
Heiko
Gehalt
2435
1201
3675
Geschlecht
w
w
m
Wichtig: In einer SQL Select Abfrage kann der IN Operator nicht mit Wildcards gefüllt werden wie
beim LIKE Operator.
MGS (2016) for DBMS-Systems
25
SQL BETWEEN Befehl
Der SQL BETWEEN Befehl wird in SQL-Where Bedingungen eingebaut, um einen bestimmten Bereich
eines Abfrageergebnisses in einer SQL-Anweisung zu bündeln.
Oft wird der SQL BETWEEN Befehl für Datumsbereiche von zwei Datumsangaben genutzt, um alle
Informationen in diesem Bereich zu ermitteln.
SQL BETWEEN Syntax
SELECT Spaltenname FROM Tabellenname
WHERE Spaltenname
BETWEEN 'DATUM1' AND 'DATUM2'
SQL BETWEEN Beispiel
Gegeben sei eine Tabelle namens „Mitarbeiter“:
Nachname
Wegner
Müller
Schulz
Richter
Schröder
Vorname
Lutz
Melanie
Dorothea
Heiko
Lukas
Geburtstag
12.12.1983
01.04.1978
13.02.1990
15.08.1995
24.07.1980
SELECT NACHNAME, VORNAME, GEBURTSTAG
FROM MITARBEITER
WHERE GEBURTSTAG
BETWEEN '19800101' AND '19930101'
Das Ergebnis würde wie folgt aussehen:
Nachname
Wegner
Schulz
Schröder
Vorname
Lutz
Dorothea
Lukas
Geburtstag
12.12.1983
13.02.1990
24.07.1980
SQL LIKE Befehl
Der SQL LIKE Befehl ermöglicht eine Suche auf der Grundlage eines vorher definierten regulären
Musters anstelle eines festen Suchbegriffs (wie bei SQL IN) oder der Definition eines Bereichs (wie
bei SQL BETWEEN).
Oft wird der SQL Like Befehl in Texten bzw. Zeichenketten verwendet, um mit regulären Mustern
Ergebnisse zurückzuliefern.
SQL LIKE Befehl Syntax
Die SQL Syntax einer Select-Abfrage mit WHERE und LIKE kann wie folgt aufgebaut werden:
SELECT Spaltenname FROM Tabellenname WHERE Spaltenname LIKE 'MUSTER'
MGS (2016) for DBMS-Systems
26
Das ‚MUSTER‘ kann nachfolgenden Strukturen aufgebaut werden:
•
•
•
•
‚L_S‘: Alle Zeichenketten die mit einem ‚L‘ beginnen, inklusive einem Folgezeichen und mit
einem ‚S‘ enden.
‚BEST%‘: Alle Zeichenketten, die mit ‚BEST‘ beginnen.
‚%UNG‘: Alle Zeichenketten, die auf ‚UNG‘ enden.
‚%ST%‘: Alle Zeichenketten, die an irgendeiner Stelle das Muster ‚ST‘ enthalten.
SQL LIKE Befehl Beispiel
Gegeben sei eine Tabelle namens „Mitarbeiter“:
Nachname
Richard
Wagner
Rosenkreuz
Lutter
Poller
Vorname
Juliana
Jakob
Max
Juliane
Moritz
Gehalt
1350
5790
4410
1271
5034
Geschlecht
w
m
m
w
m
Nun wollen wir alle Mitarbeiter auswählen, wo der Vorname mit R beginnt, 8 Folgezeichen besitzt
und auf z endet:
Das SQL-Statement mit der LIKE-Bedingung würde wie folgt aussehen:
SELECT NACHNAME, VORNAME, GEHALT, GESCHLECHT
FROM MITARBEITER
WHERE NACHNAME LIKE 'R________z'
Das Ergebnis würde wie folgt aussehen:
Nachname
Rosenkreuz
Vorname
Max
Gehalt
4410
Geschlecht
m
Nun wollen wir alle Mitarbeiter auswählen, wo der Nachname mit Lu beginnt:
Das SQL-Statement mit der LIKE-Bedingung würde wie folgt aussehen:
SELECT NACHNAME, VORNAME, GEHALT, GESCHLECHT FROM MITARBEITER
WHERE NACHNAME LIKE 'Lu%'
Nachname
Lutter
MGS (2016) for DBMS-Systems
Vorname
Juliane
Gehalt
1271
Geschlecht
w
27
SQL ORDER BY
Der SQL ORDER BY-Befehl ermöglicht eine Sortierung auf der Grundlage einer vorher definierten
Sortierungsreihenfolge. Der SQL ORDER BY Befehl wird in vielen Anwendungen in Form von
Sortierungsfiltern oder Buttons zum Sortieren dargestellt.
SQL ORDER BY Syntax
Die SQL Syntax einer Select-Abfrage mit ORDER BY kann wie folgt aufgebaut werden:
SELECT Spaltenname FROM Tabellenname ORDER BY Spaltenname Sortierungsparameter
Der ‚Sortierungsparameter‘ kann nachfolgenden Strukturen aufgebaut werden:
•
•
ASC: Das Ergebnis wird aufsteigend sortiert
DESC: Das Ergebnis wird absteigend sortiert
SQL ORDER BY Beispiel
Gegeben sei folgende Tabelle namens „PKW“:
PKWNR
1
2
3
4
5
Modell
Auto A
Auto B
Auto C
Auto D
Auto E
Preis
80.000,00€
72.000,00€
102.000,00€
85.000,00€
78.000,00€
Nun wollen wir die Pkw’s nach ihrem Preis aufsteigend sortieren:
Das SQL-Statement mit der ORDER BY-Bedingung würde wie folgt aussehen:
SELECT PKWNR, MODELL, PREIS FROM PKW
ORDER BY PREIS ASC
Das Ergebnis würde wie folgt aussehen:
PKWNR
2
5
1
4
3
Modell
Auto B
Auto E
Auto A
Auto D
Auto C
Preis
72.000,00€
78.000,00€
80.000,00€
85.000,00€
102.000,00€
Nun wollen wir die Pkw’s nach ihrem Modell aufsteigend und nach dem Preis absteigend sortieren:
Das SQL-Statement mit der ORDER BY-Bedingung würde wie folgt aussehen:
SELECT PKWNR, MODELL, PREIS FROM PKW
ORDER BY MODELL ASC, PREIS DESC
Das Ergebnis würde wie folgt aussehen:
PKWNR
2
4
1
4
5
MGS (2016) for DBMS-Systems
Modell
Auto B
Auto C
Auto C
Auto D
Auto E
Preis
72.000,00€
102.000,00€
80.000,00€
85.000,00€
78.000,00€
28
SQL GROUP BY Befehl
Durch das SQL GROUP BY–Statement ist es möglich eine Ergebnismenge zu gruppieren. Dieser SQLBefehl wird häufig in Kombination mit den Aggregatfunktionen verwendet. Zu den
Aggregatfunktionen gehören die Befehle AVG, COUNT, MAX, MIN, SUM.
SQL GROUP BY Syntax
Die SQL GROUP BY Syntax einer Select-Abfrage mit GROUP BY ist wie folgt aufgebaut:
SELECT Spaltenname FROM Tabellenname [WHERE Bedingung] GROUP BY Spaltenname
Mithilfe des GROUP BY wird definiert, wie die Datenmenge gruppiert werden soll. Die
Ergebnismenge kann nach mehreren Spalten gruppiert werden
SQL GROUP BY Beispiel
Gegeben sei folgende Tabelle namens „Auto“:
Auto
Auto 1
Auto 2
Auto 3
Auto 4
KM-Stand
30 000km
10 000km
20 000km
30 000km
Baujahr
2002
2010
2010
2001
Wir sind nun ein Autoverkäufer und möchten wissen, wie viele Autos das Baujahr 2010 in unserem
Repertoire besitzen.
Das SQL Statement mit der GROUP BY-Bedingung würde wie folgt aussehen:
SELECT Baujahr, COUNT(Baujahr) AS Count(Baujahr)
FROM Auto
WHERE Baujahr = ‘2010‘
GROUP BY Baujahr
Das Ergebnis würde wie folgt aussehen:
Baujahr
2010
MGS (2016) for DBMS-Systems
Count(Baujahr)
2
29
SQL Abarbeitungsreihenfolge
Grundlegendes zur logischen Reihenfolge der Vorgänge in SELECT-Anweisungen
(Abarbeitungsreihenfolge)
•
•
•
Elemente einer SELECT-Anweisung
Logische Abfrageverarbeitung
Anwenden der logischen Reihenfolge von Vorgängen zum Schreiben von SELECTAnweisungen
Element
Rolle
SELECT
Legt fest, welche Spalten zurückzugeben sind
FROM
Legt die abzufragende(n) Tabelle(n) fest
WHERE
GROUP BY
HAVING
ORDER BY
Filtert Zeilen nach einem Prädikat
Ordnet Zeilen nach Gruppen an
Filtert Gruppen nach einem Prädikat
Sortiert die Ausgabe
Die Reihenfolge, in der eine Abfrage geschrieben wird, ist nicht die Reihenfolge, in der sie von SQL
Server ausgewertet wird
5.
1.
2.
3.
4.
6.
SELECT
FROM
WHERE
GROUP
HAVING
ORDER BY
Aggregieren
SQL SUM() Funktion
Die SQL SUM Funktion summiert die Werte einer Tabellenspalte und liefert sie zurück.
Die SQL SUM Funktion ist aus den Unternehmen nicht mehr wegzudenken, da besonders in den
Abteilungen des Vertriebs- und Finanz-Controlling Summenwerte mittels der SUM Funktion erstellt
werden müssen.
SQL SUM() Funktion Syntax
Die SQL SUM() Funktion kann in einer Select-Abfrage wie folgt eingebaut werden:
SELECT SUM(Spaltenname1) FROM Tabellenname
Mithilfe der SUM() Funktion werden die Werte der Tabellenspalte aufsummiert und angezeigt.
MGS (2016) for DBMS-Systems
30
SQL SUM() Funktion Beispiel
Gegeben sei folgende Tabelle namens „PKW“:
PKWNR
1
2
3
Modell
Auto A
Auto B
Auto C
Preis
25.000,00€
30.000,00€
28.000,00€
Nun wollen wir den Gesamtwert aller PKW’s feststellen. Das erfolgt mittels der SUM-Funktion.
Das SQL-Statement mit der SUM-Funktion würde wie folgt aussehen:
SELECT SUM(PREIS) FROM PKW
Preis
83.000,00€
SQL Min() Funktion
Die SQL MIN Funktion ermittelt den niedrigsten Wert einer Tabellenspalte und liefert ihn
zurück.
Die SQL MIN Funktion kann genutzt werden, um ein Extremum (Tiefpunkt) in
Tabellenspalten festzustellen. Im Reporting werden oft Höchstwerte um negative oder
positive Werte hervorzuheben.
SELECT MIN(Preis) FROM PKW
Gegeben sei folgende Tabelle namens „PKW“:
PKWNR
1
2
3
Modell
Auto A
Auto B
Auto C
Preis
30.000,00€
35.000,00€
90.000,00€
Das Ergebnis sieht wie folgt aus:
Preis
30.000,00€
SQL Max() Frunktion
Neben der Min() Funktion, die den kleinesten Wert aus einem Datensatz ermittelt, wird mit der
Max() Funktion, der höchste Wert ermittelt.
In unserem Beispiel sähe das wie folgt aus:
SELECT MAX(Preis) FROM PKW
PKWNR
1
2
3
Das Ergebnis sieht wie folgt aus:
Preis
90.000,00€
MGS (2016) for DBMS-Systems
Modell
Auto A
Auto B
Auto C
Preis
30.000,00€
35.000,00€
90.000,00€
31
SQL Avg() Funktion
Wie auch der Max() und Min() Funktion wird sehr häufig auch nach dem Mittelwert gefragt. Auch
dazu gibt es eine Funktion, die wie folgt beschrieben wird.
SELECT AVG(Preis) FROM PKW
Gegeben sei folgende Tabelle namens „PKW“:
PKWNR
1
2
3
Modell
Auto A
Auto B
Auto C
Preis
30.000,00€
35.000,00€
90.000,00€
Das Ergebnis sieht wie folgt aus:
Preis
51.666,66€
SQL Count() Frunktion
Die SQL COUNT-Funktion zählt(COUNT) die Anzahl von ausgewählten Datensätzen. Für diese
Aggregatfunktion gibt man einfach das Schlüsselwort COUNT an, inklusive der Spalte der zu
zählenden Datensätzen in den Klammern an.
Es werden alle Datensätze gezählt, deren Wert nicht NULL ist.
SQL Count wird oft verwendet um bestimme Datenquellen auf ihre Richtigkeit zu vergleichen.
In unserem Beispiel sähe das wie folgt aus:
SELECT Count(*) FROM PKW
PKWNR
1
2
3
4
Das Ergebnis sieht wie folgt aus:
Preis
3
MGS (2016) for DBMS-Systems
Modell
Auto A
Auto B
Auto C
Auto D
Preis
62.000,00€
72.000,00€
NULL
78.000,00€
32
Mengenoperationen und Unterabfragen
Mengenoperationen verbindet min. 2 Objekte miteinander und bilden ein gemeinschaftliches
Ergebnis.
Das folgenden 2 Tabellen werden verwendet
Tabelle student
Tabelle lehrender
matrikel_nr name vorlesung
---------------------------911574
Meier
Java
676676
Schulz Datenbanken
matrikel_nr
name
vorlesung
-----------------------------878999
Kowa
Datenbanken
665544
Müller XML
Union
UNION bildet die Vereinigung zweier Relationen indem Zeilen der ersten Menge oder des ersten
Operanden mit allen Zeilen der zweiten Menge zusammengefasst werden. Zeilen, die in der
Ergebnismenge zweimal vorkommen, werden zu einer einzigen Zeile zusammengefaßt. Die
Datentypen der Spalten müssen kompatibel sein, d.h. es muß entweder ein impliziter Cast (z.B. int
auf double) möglich sein, oder wenn dies nicht möglich ist, muß ein expliziter Cast erfolgen. - dies
bezieht sich auch auf die Anordnung der Spalten in der Abfrage.
SELECT name FROM student
UNION
SELECT name FROM lehrender
Das Ergebnis sieht wie folgt aus.
Meier
Schulz
Kowa
Müller
Union all
Der Operand UNION ALL liefert alle Werte, die von der linken und von der rechten Abfrage
zurückgegeben werden, gleichgültig, ob Duplikate vorhanden sind oder nicht. Wenn die Duplikate
nicht mitangezeigt werden sollen, ist UNION ALL in der SQL-Abfrage durch UNION zu ersetzen, der
die doppelten Datensätze und NULL-Werte schon von vorne an entfernt.
Intersect
INTERSECT überprüft die Zeilen der beiden Eingangsmengen und gibt nur jene Zeilen aus, die in der
linken vorkommt. Auch hier werden vor dem Erstellen der Ergebnismenge die redundanten Zeilen
ausgeschaltet. Gibt alle eindeutigen Werte zurück, die von den Abfragen auf der linken und rechten
Seite des INTERSECT-Operators zurückgegeben werden.
SELECT vorlesung FROM student
INTERSECT
SELECT vorlesung FROM lehrender
Das Ergebnis sieht wie folgt aus:
Datenbanken
MGS (2016) for DBMS-Systems
33
Except
EXCEPT gibt eindeutige Zeilen aus der linken Eingabeabfrage zurück, die nicht von der rechten
Eingabeabfrage ausgegeben werden.
SELECT vorlesung FROM student
EXCEPT
SELECT vorlesung FROM lehrender
Das Ergebnis sieht wie folgt aus:
Java
ALL und ANY Operanten
Sie werden in Suchbedingungen zusammen mit einem Vergleichsoperator, wie z.B. =, <, >,...
verwendet. Wird ein solcher Vergleichsoperator mit einem der Schlüsselwörter ANY oder ALL
kombiniert, dann ist als rechter Operand (Ausdruck) eine Unterabfrage zugelassen, die eine
Ergebnismenge zurückliefert. Wird auf ANY oder ALL verzichtet und nur ein Vergleichsoperator
verwendet, so darf der rechte Operand (Ausdruck) nur eine Unterabfrage sein, die nur einen
Datensatz zurückliefert. Linker und rechter Operand müssen Ausdrücke mit gleicher Struktur
(vereinigungskonform) sein: gleiche Anzahl an Spalten, gleiche Datentypen, gleiche Reihenfolge der
Spalten. Ist einer der Operanden (Ausdrücke) leer (NULL) bzw. eine leere Ergebnismenge, dann wird
die Bedingung im Sinne der dreiwertigen Logik zu UNKNOWN ausgewertet.
Beispiele:
Welche Kunden aus Köln tätigen mehr Umsatz als irgendein Kunde aus Bonn?
Kölner mit wenigstens so viel Umsatz wie der "geringste" Bonner.
SELECT *
FROM Kunden
WHERE Ort = 'Köln'
AND Umsatz >= ANY (SELECT *
FROM Kunden
WHERE Ort = 'Bonn');
Welche Kunden aus Köln tätigen mehr Umsatz als alle Kunden aus Bonn?
Kölner mit mehr Umsatz wie der "meiste" Bonner.
SELECT *
FROM Kunden
WHERE Ort = 'Köln'
AND Umsatz >= ALL (SELECT *
FROM Kunden
WHERE Ort = 'Bonn');
MGS (2016) for DBMS-Systems
34
IN Operant / Unterabfragen
Der IN-Operator prüft, ob ein zu vergleichender Ausdruck in einer Menge von Werten vorkommt.
Wenn ja, dann wird TRUE zurückgeliefert, wenn nicht, dann FALSE und wenn NULL-Werte in der
Vergleichsmenge vorkommen bzw. der zu vergleichende Ausdruck NULL ist, dann wird im Sinne der
dreiwertigen Logik UNKNOWN zurückgeliefert. Es gibt zwei verschiedene Syntax. Einmal kann mit
einer Liste konstanter Werte verglichen werden, zum anderen mit der Ergebnismenge einer
Unterabfrage.
Der IN-Operator kann in beliebigen Suchbedingungen verwendet werden und mit NOT negiert
werden.
Damit der Operator syntaktisch ausführbar ist, müssen einige Restriktionen eingehalten werden.
Wird eine Liste konstanter Werte verwendet, so darf diese Liste keine Duplikate enthalten und alle
Werte müssen vom gleichen Datentyp sein. Zudem muss immer gelten, dass der zu vergleichende
Ausdruck und die konstanten Werte bzw. die Spaltenliste der Unteranfrage über die gleiche Struktur
verfügen, was heißt, dass die Datentypen gleich sein müssen, die Anzahl der Spalten gleich sein muss
und auch die Reihenfolge der Spalten. Die Spalten müssen nicht gleich heißen. Im Grunde gilt hier
auch die Anforderung der Vereinigungskonformität.
Für Vergleiche mit einer Unteranfrage ist ein semantisch analoger Operator der EXISTS-Operator.
Während bei der Verwendung des unären EXISTS explizit auf die Korrelation geachtet werden muss,
ergibt sich beim IN-Operator die Korrelation automatisch aufgrund der Syntax des binären IN. Beim
IN werden automatisch Spalten der oberen und der unteren Anfrage in Beziehung gesetzt, weil die
oberen Spalten ja den zu vergleichenden Ausdruck darstellen und die unteren Spalten die Spalten der
SELECT-Liste sind.
Selektieren Sie alle Artikel, für die kein Auftrag vorliegt!
SELECT TNr, Bezeichnung
FROM Artikel
WHERE TNr NOT IN (SELECT TNr FROM Auftragspositionen);
Welche Artikel sind von Typ 'Fahrrad', 'Inliner', 'Skateboard'?
SELECT TNr, Bezeichnung
FROM Artikel
WHERE Artikel_Typ IN ('Fahrrad', 'Inliner', 'Skateboard');
Überraschendes Ergebnis
SELECT TNr, Bezeichnung
FROM Artikel
WHERE Artikel_Typ IN ('Fahrrad', 'Inliner', NULL);
Diese Abfrage liefert unabhängig von den tatsächlichen Werten in der Spalte Artikel_Typ keinen Wert
zurück, da der Vergleich mit einem NULL-Werte nach der dreiwertigen Logik dreiwertigen Logik den
Wert UNKNOWN ergibt.
MGS (2016) for DBMS-Systems
35
Verknüpfen von Objekten (JOINS)
SQL JOINS – Grundlagen eines SQL JOINS
In relationalen Datenbanksystemen werden Informationen aus einem oder mehrere
Anwendungssysteme systematisch gespeichert. Die Zusammengehörigkeit der Daten ergibt sich aus
dem logischen Datenbankdesign, die meist in Form eines Entity-Relationship-Modells dokumentiert
ist. Die Struktur der Datenbank leitet sich aus den einzelnen Typen dieses Modells ab.
Um Redundanzen beim Speichern zu vermeiden, werden die Informationen auf verschiedene
Tabellen verteilt. Zur Erhaltung der logischen Zusammengehörigkeit werden FremdschlüsselBeziehungen zwischen den Tabellen aufgebaut.
Muss das Datenbanksystem eine Anfrage verarbeiten, bei der Informationen aus mehreren Tabellen
benötigt werden, ist es erforderlich die einzelnen Datensätze der Tabellen wieder
zusammenzuführen. Dadurch werden die ursprünglichen Informationen wiederhergestellt. Dies wird
in SQL-Datenbanken mit Hilfe von SQL JOINS (Verbünden) umgesetzt. Mit einem SQL Join werden
mehrere Tabellen verknüpft, die in einer Beziehung zueinanderstehen.
MGS (2016) for DBMS-Systems
36
Wie funktioniert ein SQL Join?
Die Verbindung der Tabellen erfolgt mit speziellen Schlüsselwörtern. Neben dem Namen des
anzuwendenden Joins, man unterscheidet vier Join-Arten, muss zusätzlich eine ON-Bedingung
angegeben werden. Eine Ausnahme gibt es beim CROSS JOIN und beim NATURAL JOIN. In der ONBedingung werden die zu vergleichenden Spalten der beiden Tabellen angegeben.
Ein Vergleich erfolgt durch die Operatoren gleich, ungleich, kleiner, größer usw. Dabei können die
Namen der Spalten verschieden sein. Es ist jedoch darauf zu achten, dass sie die logische Beziehung
zwischen den Datensätzen widerspiegeln. Neben den zusätzlichen Schlüsselwörtern kann auch die
WHERE-Klausel verwendet werden, um die Ergebnismenge bezüglich anderer Merkmale
einzuschränken.
Welche Arten von SQL Joins gibt es?
Im SQL-Standard wird generell zwischen sechs Join-Typen unterschieden, die sich in Art und
Anwendung unterscheiden:
Cross-Joins
Inner Joins
Left Joins
Right Joins
Full Outer Joins
Self Join
Im folgenden Beispiel wird gezeigt, wie die Categories-Tabelle (Kategorien) und die Products-Tabelle
(Artikel) über das CategoryID-Feld (Kategorie-Nr.) verknüpft werden können:
SELECT CategoryName, ProductName FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID;
Im folgenden Beispiel wird gezeigt, wie Sie die Tabellen Categories und Products für das Feld
CategoryID verknüpfen. Die Abfrage erzeugt eine Liste aller Kategorien, einschließlich der
Kategorien, die keine Produkte enthalten:
SELECT CategoryName, ProductName
FROM Categories
LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID;
Durch die Verbindung der Objekte sind Ihnen keine Grenzen gesetzt. Durch Schlüsselkreys
verbundene Objekte lassen sich beliebig erweitern. Einzige Bedingung ist, dass der Datentyp der
verbundenen Objekte identisch ist.
MGS (2016) for DBMS-Systems
37
Datenmanipulation (Insert, Update, Delete)
Grundlegend verstehen wir im SQL, 4 Befehle. Den Select-Befehl haben wir bereits im oberen Bereich
kennen gelernt. Diese liefert Ihnen alle Daten, sofern Sie Berechtigung vorliegt.
WICHTIG:
Falls Sie bislang noch wenig Erfahrung mit Datenbank-Befehlen haben: Beachten Sie, dass es auf
dieser Ebene des Zugriffs keinen 'Rückgängig-Button' mehr gibt, wie Sie ihn vielleicht von Word oder
Excel gewohnt sind. Eine Datenbank stellt einen elementaren Datenspeicher dar,
INSERT/UPDATE/DELETE verändert diesen Speicher direkt. Und was Sie eingefügt, geändert oder
gelöscht haben, ist unwiderruflich eingefügt, geändert und gelöscht.
Falls es in Programmen wie Word einen Rückgängig-Button gibt, so setzt dies in irgendeiner Form
eine Datenbankstruktur voraus, welche sich die zuletzt durchgeführten Aktionen merkt und diese
zurücksetzt. Benötigen Sie auf der Ebene einer Datenbank eine Historie, so muss diese explizit in
Form eines Tabellenschemas erstellt und zu einer Zeile nicht nur die aktuellen Werte, sondern
zusätzlich das Änderungsdatum erfasst werden. Alternativ kann man eine zusätzliche Tabelle
erstellen und immer die drei letzten Versionen in dieser ablegen.
Der Insert-Befehl schreibt Daten der Tabellen-Regel hinein. Dazu bestehen mehrere Möglichkeiten.
Sie können bestimmte Werte in einer Tabelle einfügen, oder bestehend aus anderen Tabellen/Views
sich die Daten über den Select-Befehl auslesen.
INSERT
Einfaches Einfügen deren Feldreihenfolge bekannt sind.
Dazu ist es wichtig zu wissen, dass die Felder der Reihe nach eingefügt werden müssen.
INSERT INTO Tabellenname
VALUES (1, ‘Max Mustermann‘, NULL, ‘Musterstrasse 1a‘);
Mehrfaches Einfügen
INSERT INTO Tabellenname
VALUES (1, ‘Max Mustermann‘, NULL, ‘Musterstrasse 1a‘),
(2, ‘Martin Schulze‘, ‘Uwe‘, ‘Unter der Brücke 12‘);
Einfaches Einfügen deren Feldreihenfolge angegeben werden, weil nur diese Felder beschrieben
werden sollen.
INSERT INTO Tabellenname (AdressNr, Name, Zuname, Strasse)
VALUES (1, ‘Max Mustermann‘, NULL, ‘Musterstrasse 1a‘);
Selbstverständlich ist auch so ein mehrfaches Einfügen möglich.
Einfügen von Daten mit einer Identitätsspalte.
INSERT INTO Tabellenname (AdressNr, Name, Zuname, Strasse)
VALUES (NEWID(), ‘Max Mustermann‘, NULL, ‘Musterstrasse 1a‘);
Einfügen von Daten aus anderen Tabellen / Views
INSERT INTO Tabellenname (AdressNr, Name, Zuname, Strasse)
SELECT (AdressNr, Name, Vorname, Strasse
from Adressen
where AdressNr IN (1,2,5,47,52));
MGS (2016) for DBMS-Systems
38
UPDATE
Der Update-Befehl manipuliert ein oder mehrere Datensätze in einem Batch.
Zunächst wird die zu aktualisierende Tabelle angegeben. Nach SET folgt die Liste der zu
aktualisierenden Zellen, für die rechts vom Gleichheitszeichen der neue Wert angegeben wird. Im
ANSI-Standard kann anschließend eine JOIN-Verknüpfung folgen, durch welche die von der
Aktualisierung betroffenen Zeilen genauer eingeschränkt werden. Schließlich werden mit einer
WHERE-Klausel einzelne Zeilen ausgewählt.
Update einer Tabelle ohne Verknüpfung mit anderen Tabellen.
UPDATE <Tabelle>
SET <Name einer Spalte> = <Ausdruck aus Spalten,
Konstanten, Funktionen>
[, weitere Spaltennamen = Ausdruck]
WHERE <Bedingung>
Update einer Tabelle mit JOIN
UPDATE <Tabelle | View>
SET <Name einer Spalte> = <Ausdruck aus Spalten,
Konstanten, Funktionen>
[, weitere Spaltennamen = Ausdruck]
[FROM <Tabelle>
[INNER | LEFT | RIGHT] JOIN <Tabelle>
ON <Spalte-1 = Spalte-2>]
WHERE <Bedingung>
Beispiele
UPDATE ARTIKEL
SET Gesamt = 0
Einfachste Version einer Update-Anweisung: Eine Spalte wird mit einem neuen Wert, hier mit der
Konstanten 0 belegt, die Aktualisierung wird für alle Zeilen ausgeführt.
UPDATE ARTIKEL
SET Gesamt = A_PREIS
Jeder Zelle 'Gesamt' ist die Zelle 'A_PREIS' derselben Zeile zugeordnet. Also wird der Wert der
letzteren in die Zelle 'Gesamt' kopiert.
UPDATE ARTIKEL
SET Gesamt = A_PREIS * 1.19
Dasselbe wie zuvor, nun ergänzt um die Multiplikation mit einer Konstanten. Eine solche Spalte mag
bsp. den Bruttopreis eines Artikels enthalten.
UPDATE ARTIKEL
SET Gesamt = A_PREIS * 1.19
WHERE A_PREIS > 100
Nun wird die Menge der zu aktualisierenden Zeilen eingeschränkt auf, dass nicht alle, sondern nur
jene Zeilen aktualisiert werden, deren Preis vor der Aktualisierung größer 100 ist.
MGS (2016) for DBMS-Systems
39
Deterministische und nichtdeterministische Anweisungen: Bei UPDATE-Befehlen mit JOIN-Klausel
muss sichergestellt sein, dass der JOIN zu jeder zu aktualisierenden Zeile nur einen Ausdruck liefert.
Ist dies der Fall, so spricht man von deterministischen Anweisungen. Ansonsten handelt es sich um
eine nichtdeterministische Anweisung, deren Ergebnis nicht eindeutig definiert ist. Denn in diesem
Fall ist nicht vorhersehbar, welcher Ausdruck tatsächlich in die Zelle eingetragen wird. Dieses
Problem taucht hauptsächlich dann auf, wenn eine Spalte in der Grundtabelle aktualisiert werden
soll und im JOIN eine Detailtabelle herangezogen wird, so dass in der Detailtabelle kein, ein oder
mehrere Datensätze für die Grundzeile zur Verfügung stehen.
DELETE
Der DELETE-Befehl entfernt Daten aus der Tabelle / View.
Sofern Sie die Berechtigung haben, wird der SQL Server die Daten löschen. Bitte bedenken Sie an
dieser Stelle, dass es auch hier kein Rückgängig-Button gibt. Zunächst einmal kann der Datensatz
nicht ohne weiteres widerhergestellt werden. Um Datensätze, die versehentlich gelöscht wurden, ist
ein erhöhter administrativer Aufwand Notwendig.
Löscht den gesamten Inhalt der Tabelle Artikel
DELETE FROM Artikel;
Löscht den Inhalt der Tabelle Artikel mit der Artikelnummer 11, 12 und 13
DELETE FROM Artikel WHERE ArtikelNr IN (11, 12, 13);
Löschen von Artikel unter Berücksichtigung einer Unterabfrage.
Hier werden alle Artikel gelöscht die nicht in den Auftragspositionen vorhanden sind.
DELETE FROM Artikel WHERE ArtikelNr NOT IN (Select ArtikelNr from Auftragspositionen);
Weitere Beispiele
Verwenden von DELETE mit der TOP-Klausel
Im folgenden Beispiel werden 2.5 % der Zeilen aus der Tabelle Artikel gelöscht.
DELETE TOP (2.5) FROM Artikel
MGS (2016) for DBMS-Systems
40
OUTPUT-Klausel
Eine INSERT-, UPDATE- oder DELETE-Anweisung mit einer OUTPUT-Klausel gibt Zeilen
auch dann an den Client zurück, wenn bei der Anweisung ein Fehler auftritt und ein Rollback
ausgeführt wird. Wenn bei der Ausführung der Anweisung ein Fehler auftritt, sollte das
Ergebnis nicht verwendet werden.
DELETE Artikel
OUTPUT DELETED.*;
Gibt alle Zeilen aus der Delete – Tabelle zurück, die zuvor in Artikel gelöscht wurden.
In diesem Beispiel wird die ersten 5 Artikel aus der Auftragspositionen-Tabelle gelöscht, mit dem
Kriterium ArtikelNr = 5
DELETE TOP(5) Auftragspositionen
OUTPUT deleted.*
WHERE ArtikelNr = 7;
Verwenden Sie den READPAST-Tabellenhinweis in UPDATE- und DELETE-Anweisungen,
wenn es in Ihrem Szenario möglich ist, dass mehrere Anwendungen einen destruktiven
Lesevorgang aus einer Tabelle ausführen. So werden Sperrkonflikte verhindert, die entstehen,
wenn eine andere Anwendung bereits den ersten berechtigten Datensatz in der Tabelle liest.
DELETE TOP(5) Auftragspositionen WITH (READPAST)
OUTPUT deleted.*
WHERE ArtikelNr = 7;
MGS (2016) for DBMS-Systems
41
Ein weiteres Beispiel aus der Microsoft-MSDN
USE tempdb;
GO
CREATE TABLE dbo.table1
(
id INT,
employee VARCHAR(32)
);
GO
INSERT INTO dbo.table1 VALUES
(1, 'Fred')
,(2, 'Tom')
,(3, 'Sally')
,(4, 'Alice');
GO
DECLARE @MyTableVar TABLE
(
id INT,
employee VARCHAR(32)
);
PRINT 'table1, before delete'
SELECT * FROM dbo.table1;
DELETE FROM dbo.table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2;
PRINT 'table1, after delete'
SELECT * FROM dbo.table1;
PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar;
--Results
--table1, before delete
--id
employee
------------- -------------------------------1
Fred
--2
Tom
--3
Sally
--4
Alice
---table1, after delete
--id
employee
------------- -------------------------------1
Fred
--3
Sally
--@MyTableVar, after delete
--id
employee
------------- -------------------------------2
Tom
--4
Alice
MGS (2016) for DBMS-Systems
42
MERGE-Klausel
Führt Einfüge-, Update- oder Löschvorgänge in einer Zieltabelle anhand der Ergebnisse eines Joins
mit einer Quelltabelle aus. Sie können z. B. zwei Tabellen synchronisieren, indem Sie Zeilen in einer
Tabelle anhand von Unterschieden, die in der anderen Tabelle gefunden wurden, einfügen,
aktualisieren oder löschen.
WICHTIG:
Es ist wichtig, dass nur die Spalten aus der Zieltabelle angegeben werden, die für Abgleichszwecke
verwendet werden. Geben Sie also Spalten aus der Zieltabelle an, die mit der entsprechenden Spalte
der Quelltabelle abgeglichen werden. Versuchen Sie nicht, die Abfrageleistung zu optimieren, indem
Sie Zeilen in der Zieltabelle in der ON-Klausel herausfiltern, beispielsweise durch Angabe von AND
NOT target_table.column_x = value. Dadurch kann es zu unerwarteten und falschen Ergebnissen
kommen.
MERGE dbo.tblArtikelGruppen g
USING dbo.xGruppenMerge1 as m ON g.ArtGr = m.ArtGr
WHEN MATCHED AND (g.ArtGrText != m.ArtGrText)
THEN UPDATE SET g.ArtGrText = m.ArtGrText
WHEN NOT MATCHED BY TARGET
THEN INSERT (ArtGr, ArtGrText)
VALUES (m.ArtGr, m.ArtGrText)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Wie wende ich MERGE nun an? Auf den ersten Blick wirkt der MERGE Befehl ein wenig
“unübersichtlich”.
MERGE <Ziel>
USING <qQuelle>
ON <Bedingung>
WHEN MATCHED THEN <meist ein Update>
WHEN NOT MATCHED THEN <meist ein Insert>;
Wie muss ein MERGE Befehl gelesen werden? Also, wir haben hinter dem MERGE das
sogenannte “Ziel” und hinter dem USING die “Quelle”. Über das ON werden diese beiden
verbunden (join). Nun kommt das WHEN MATCHED THEN. Hier kommt unsere Aktion
hin, welche ausgeführt wird bei einer Erfüllung der Bedingung. Ok, und bei WHEN NOT
MATCHED kommt halt die Aktion, wenn die Bedingung nicht erfüllt wurde. Soweit ganz
einfach, oder?
Hier ein erstes einfaches Beispiel:
MERGE Ziel as t
USING Quelle as s
ON t.bk = s.bk
WHEN MATCHED THEN
update set t.bk = s.bk, t.attribute1 = s.attribute1, t.attribute2 = s.attribute2
WHEN NOT MATCHED THEN
insert (bk, attribute1, attribute2)
values (s.bk, s.attribute1, s.attribute2);
MGS (2016) for DBMS-Systems
43
Das war doch schon nicht schlecht. Nun ist es aber so, dass hier wenn immer der BK
(BusinessKey, Geschäftsschlüssel aus der DWH Lehre…) gleich ist auch ein Update
durchgeführt wird. Das sollte natürlich nur sein, wenn sich was an den Attributen geändert
hat! Eine erste Idee wäre nun die ON Klausel zu erweitern, aber dadurch würden wir unser
Ziel nicht erreichen, da dann ja der Vergleich auf den BK nicht mehr passt. Wir benötigen
eigentlich eine Zweiteilung der Bedingung, also wenn BK gleich und Attribute ungleich.
MERGE unterstützt dies, indem wir das “WHEN MATCHED” erweitern!
MERGE DimTarget as t
USING ELTSource as s
ON t.bk = s.bk
WHEN MATCHED AND (t.attribute1 != s.attribute1 or t.attribute2 != s.attribute2)
THEN
update set t.bk = s.bk, t.attribute1 = s.attribute1, t.attribute2 = s.attribute2
WHEN NOT MATCHED
THEN
insert (bk, attribute1, attribute2)
values (s.bk, s.attribute1, s.attribute2);
Resultat:
Der Vorteil der Verwendung von MERGE ist, dass Quelle und Ziel nur einmal durchlaufen werden
müssen für alle Operationen, da für alles nur ein Abfrageplan erstellt wird. Gerade bei größeren
Datenmengen kann die Verarbeitungsart deutlich beschleunigt werden! Hinzu kommt, dass die
Statements kürzer werden. Dennoch ist Obacht geboten, denn falsch verknüpfte Felder führen zu
unerwarteten Fehlern.
MGS (2016) for DBMS-Systems
44
Abschluss:
Ich möchte mit diesem kleinen Handout natürlich keine fachliche Literatur ersetzen. Betrachten Sie
diese Informationen hier als eine Art „Wissensermittlung DBMS Grundwissen“.
Falls Ihnen dieser kleine EX-Kurs im Rahmen relationale Datenbanken ein wenig mehr Durchblick
verschafft hat, freue ich mich über ein Feedback (NEUDEUTSCH like)
MGS (2016) for DBMS-Systems