Post on 17-Sep-2018
transcript
Microsoft SQL
Server
Database-
Management Relationale DBMS
Michael Grube (MCSA und MCT) MG SOFTWARE ENTWICKLUNG
MGS (2016) for DBMS-Systems
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. Anforderungen
b. Konzeptioneller Entwurf
c. Logischer Entwurf
d. Physischer Entwurf
e. Test
f. 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. Datum Name Straße Ort Artikel Anzahl Preis
187 01.01.2016 Max Mustermann Musterstr. 1 12345 Musterort Stift 2 1,00 €
Nach der Anwendung der Ersten Normalform (1NF) sieht das Ergebnis wie folgt aus:
ReNr Datum Name Vorname Straße PLZ Ort Artikel Anzahl Preis
187 01.01.2016 Mustermann Max Musterstr. 1 12345 Musterort Stift 2 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 Datum Name Vorname Straße PLZ Ort Artikel Stk. Preis
187 01.01.2016 Muster Max Musterstr. 1 12345 Musterort Stift 2 1,00 €
Nach der Anwendung der Zweiten Normalform (2NF) sieht das Ergebnis wie folgt aus:
Neue Tabelle: „Rechnung“
Nr Datum KundenNr
187 01.01.2016 007
Neue Tabelle: „Kunde“
KundeNr Name Vorname Straße PLZ Ort
007 Mustermann Max Musterstr. 1 12345 Musterort
Neue Tabelle: „Rechnungsposition“
RePosNr ReNr ArtNr Anzahl
1 187 69 2
Neue Tabelle „Artikel“
ArtNr Artikel Preis
69 Stift 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 Name Vorname Straße PLZ Ort
007 Mustermann Max Musterstr. 1 12345 Musterort
Nach der Anwendung der Dritten Normalform (3NF) sieht das Ergebnis wie folgt aus:
Neue Tabelle: „Kunden“
KundenNr Name Vorname Straße PLZ
007 Mustermann Max Musterstr. 1 12345
Neue Tabelle: „Postleitzahl“
PLZ Ort
12345 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 nicht-
normalisierten 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 Vorname Gehalt Abteilung
Müller Daniel 2435 Einkauf
Meier Dennis 2090 Vertrieb
Schulze Holger 3410 Produktmanagement
Niebaum Michael 3675 Geschäftsführung
Richter Julia 1201 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 Gehalt
Müller 2435
Meier 2090
Schulze 3410
Niebaum 3675
Richter 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 Modell Preis
1 Auto A 65.000,00€
2 Auto B 72.000,00€
4 Auto A 80.000,00€
5 Auto D 80.000,00€
6 Auto E 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 und Operatoren:
Prädikate IN, BETWEEN, LIKE
Vergleichsoperatoren =, >, <, >=, <=, <>, !=, !>, !<
Logische Operatoren AND, OR, NOT
arithmetische Operatoren +, -, *, /, %
Verkettung +
SQL WHERE Beispiel
Gegeben sei folgende Tabelle namens „Mitarbeiter“:
Nachname Vorname Gehalt Abteilung
Heinrich Daniel 2435 Einkauf
Löffler Dennis 2090 Vertrieb
Schulz Holger 3410 Produktmanagement
Schröder Michael 3675 Geschäftsführung
Nussbaum Julia 1201 Empfang
MGS (2016) for DBMS-Systems
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 Vorname Gehalt Abteilung
Heinrich Daniel 2435 Einkauf
Schulz Holger 3410 Produktmanagement
Schröder Michael 3675 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 Vorname Gehalt Geschlecht
Gerhardt Sabine 2435 w
Müller Dennis 2090 m
Schulze Holger 3410 m
Opitz Heiko 3675 m
Meier Julia 1201 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 Vorname Gehalt Geschlecht
Gerhardt Sabine 2435 w
SQL WHERE OR Beispiel
Gegeben sei wieder die Tabelle namens „Mitarbeiter“:
Nachname Vorname Gehalt Geschlecht
Gerhardt Sabine 2435 w
Müller Dennis 2090 m
Schulze Holger 3410 m
Opitz Heiko 3675 m
Meier Julia 1201 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 Vorname Gehalt Geschlecht
Schulze Holger 3410 m
Opitz Heiko 3675 m
Meier Julia 1201 w
MGS (2016) for DBMS-Systems
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 Vorname Gehalt Geschlecht
Reinhardt Melinda 2435 w
Schönbaum Jakob 2090 m
Wegner Lutz 3410 m
Schulz Dorothea 1201 w
Richter Heiko 3675 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 Vorname Gehalt Geschlecht
Reinhardt Melinda 2435 w
Schulz Dorothea 1201 w
Richter Heiko 3675 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 Vorname Geburtstag
Wegner Lutz 12.12.1983
Müller Melanie 01.04.1978
Schulz Dorothea 13.02.1990
Richter Heiko 15.08.1995
Schröder Lukas 24.07.1980
SELECT NACHNAME, VORNAME, GEBURTSTAG FROM MITARBEITER WHERE GEBURTSTAG BETWEEN '19800101' AND '19930101'
Das Ergebnis würde wie folgt aussehen:
Nachname Vorname Geburtstag
Wegner Lutz 12.12.1983
Schulz Dorothea 13.02.1990
Schröder Lukas 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 Vorname Gehalt Geschlecht
Richard Juliana 1350 w
Wagner Jakob 5790 m
Rosenkreuz Max 4410 m
Lutter Juliane 1271 w
Poller Moritz 5034 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 Vorname Gehalt Geschlecht
Rosenkreuz Max 4410 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 Vorname Gehalt Geschlecht
Lutter Juliane 1271 w
MGS (2016) for DBMS-Systems
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 Modell Preis
1 Auto A 80.000,00€
2 Auto B 72.000,00€
3 Auto C 102.000,00€
4 Auto D 85.000,00€
5 Auto E 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 Modell Preis
2 Auto B 72.000,00€
5 Auto E 78.000,00€
1 Auto A 80.000,00€
4 Auto D 85.000,00€
3 Auto C 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 Modell Preis
2 Auto B 72.000,00€
4 Auto C 102.000,00€
1 Auto C 80.000,00€
4 Auto D 85.000,00€
5 Auto E 78.000,00€
MGS (2016) for DBMS-Systems
28
SQL GROUP BY Befehl
Durch das SQL GROUP BY–Statement ist es möglich eine Ergebnismenge zu gruppieren. Dieser SQL-
Befehl 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 KM-Stand Baujahr
Auto 1 30 000km 2002
Auto 2 10 000km 2010
Auto 3 20 000km 2010
Auto 4 30 000km 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 Count(Baujahr)
2010 2
MGS (2016) for DBMS-Systems
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 SELECT-
Anweisungen
Element Rolle
SELECT Legt fest, welche Spalten zurückzugeben sind
FROM Legt die abzufragende(n) Tabelle(n) fest
WHERE Filtert Zeilen nach einem Prädikat
GROUP BY Ordnet Zeilen nach Gruppen an
HAVING Filtert Gruppen nach einem Prädikat
ORDER BY Sortiert die Ausgabe
Die Reihenfolge, in der eine Abfrage geschrieben wird, ist nicht die Reihenfolge, in der sie von SQL
Server ausgewertet wird
5. SELECT
1. FROM
2. WHERE
3. GROUP
4. HAVING
6. 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 Modell Preis
1 Auto A 25.000,00€
2 Auto B 30.000,00€
3 Auto C 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 Modell Preis
1 Auto A 30.000,00€
2 Auto B 35.000,00€
3 Auto C 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 Modell Preis
1 Auto A 30.000,00€
2 Auto B 35.000,00€
3 Auto C 90.000,00€
Das Ergebnis sieht wie folgt aus: Preis
90.000,00€
MGS (2016) for DBMS-Systems
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 Modell Preis
1 Auto A 30.000,00€
2 Auto B 35.000,00€
3 Auto C 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 Modell Preis
1 Auto A 62.000,00€
2 Auto B 72.000,00€
3 Auto C NULL
4 Auto D 78.000,00€
Das Ergebnis sieht wie folgt aus: Preis
3
MGS (2016) for DBMS-Systems
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 matrikel_nr name vorlesung
---------------------------- ------------------------------
911574 Meier Java 878999 Kowa Datenbanken
676676 Schulz 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üssel-
Beziehungen 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 ON-Bedingung 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) �