MS SQL Server - Einstieg in relationale Datenbanken und

www.comelio-medien.com
MS SQL Server –
Einstieg in relationale
Datenbanken und SQL
Marco Skulschus
Marcus Wiederstein
MS SQL Server
Einstieg in relationale Datenbanken und SQL
Marco Skulschus
Marcus Wiederstein
Webseite zum Buch:
http://www.comelio-medien.com/buch-katalog/e-books/sql
© Comelio Medien 2011
http://www.comelio-medien.com/buch-katalog/e-books/sql
Alle Rechte vorbehalten. Das Werk einschließlich aller seiner Teile ist urheberrechtlich geschützt. Jeder
Verwertung außerhalb der engen Grenzen des Urheberrechtsgesetzes ist ohne Zustimmung des Verlages unzulässig und strafbar. Das gilt insbesondere für die Vervielfältigung, Übersetzung, Mikroverfilmung und die Einspeicherung und Verbreitung in elektronischen Systemen.
© Comelio GmbH
Comelio GmbH
Goethestr. 34
D-13086 Berlin
Fon:+49 (0) 30-8 14 56 22-00
Fax:+49 (0) 30-8 14 56 22-10
www.comelio-medien.com
[email protected]
Umschlaggestaltung, Comelio-Grafiken, Layout & Satz: Nadine Kilian
ISBN 978-3-939701-52-1
http://www.comelio-medien.com/buch-katalog/e-books/sql
Inhaltsverzeichnis
Inhaltsverzeichnis
1. Grundlagen 1. 1. Beispiel-System MS SQL Server 1. 1. 1. Installation 1. 1. 2. Management Studio 1. 1. 3. Abfragen direkt ausführen 1. 2. Beispieldatenbank AdventureWorks 1. 2. 1. Personaldaten 1. 2. 2. Produktdaten 1. 2. 3. Verkaufsdaten 1. 3. Das relationale Modell 1. 3. 1. Grundbegriffe des relationalen Modells 1. 3. 2. Semantisches Modell 1. 3. 3. Eigenschaften von Daten 1. 3. 4. Klassifikation von Datentypen 1. 3. 5. Beziehungen zwischen Daten 1. 3. 6. Entity-Relationship-Modell 1. 3. 7. Normalisierung mit Normalformen 1. 3. 8. DB-Anomalien 1. 4. Das relationale Datenbank-System 1. 4. 1. Zentrale Begriffe 1. 4. 2. Sichten auf ein relationales Datenbanksystem 1. 4. 3. Anforderungen an ein DBMS 1. 4. 4. Bestandteile einer Tabelle 1. 4. 5. Inhalte einer relationalen Datenbank 1. 4. 6. Architektur-Muster beim Einsatz relationaler Datenbanken 1. 5. SQL – Structured Query Language 1. 5. 1. Sprachbestandteile 1. 5. 2. Ursprung: Relationale Algebra 11
11
11
14
16
17
17
18
19
19
19
20
21
23
24
27
29
34
35
35
37
38
38
39
40
42
43
43
2. Einfache Abfragen 2. 1. Grundstruktur von SELECT 2. 1. 1. Spaltenauswahl 2. 1. 2. Aliasnamen 2. 1. 3. Qualifizierte Spaltennamen 2. 2. Bedingungen 2. 2. 1. Einfache Bedingungen und Operatoren 2. 2. 2. Boolesche Operatoren 2. 2. 3. Mathematische Operatoren 2. 2. 4. Mengen-Operatoren 2. 3. Ergebnisse aufbereiten 2. 3. 1. Duplikate ein-/ausblenden 2. 3. 2. Ergebnisse sortieren 2. 3. 3. Standard-Aggregate 2. 3. 4. Gruppieren 47
47
48
48
49
50
50
52
55
57
60
60
61
62
64
http://www.comelio-medien.com/buch-katalog/e-books/sql
3
Inhaltsverzeichnis
3. Komplexe Abfragen 3. 1. Verknüpfungen 3. 1. 1. Manuelle Verknüpfungen 3. 1. 2. ANSI-SQL-Verknüpfungen 3. 2. Unterabfragen 3. 2. 1. Einfache Unterabfragen 3. 2. 2. Spaltenunterabfragen 3. 2. 3. Abgeleitete Tabellen 3. 2. 4. Korrelierte Unterabfragen 3. 2. 5. Operatoren für Unterabfragen 3. 3. Verzweigungen 3. 3. 1. CASE mit Selektor 3. 3. 2. Selektorlose CASE-Anweisung 68
68
68
71
77
77
79
80
83
85
87
87
89
4. Datenmanipulation 4. 1. Datenstrukturen anlegen 4. 1. 1. Tabellen grafisch anlegen 4. 1. 2. Tabellen mit SQL erstellen 4. 1. 3. Tabellen und ihre Eigenschaften ändern 4. 1. 4. Sichten 4. 1. 5. Datentypen 4. 1. 6. Indizes 4. 2. Daten bearbeiten 4. 2. 1. Vorbereitung 4. 2. 2. Einfügen 4. 2. 3. Aktualisieren 4. 2. 4. Löschen 4. 3. Objekte verwalten 4. 3. 1. Katalogsichten für Objekte 4. 3. 2. Funktionen 92
92
92
97
102
104
107
109
109
109
111
113
115
117
117
119
5. Grundlagen T-SQL 5. 1. T-SQL Blöcke 5. 1. 1. SQL als Programmiersprache 5. 1. 2. Variablen und Anweisungen 5. 2. Kontrollanweisungen 5. 2. 1. Fallunterscheidungen 5. 2. 2. Schleifen 5. 3. Fehlerbehandlung 5. 3. 1. Ausnahmen 5. 3. 2. Traditionelle Fehlerbehandlung 5. 4. Cursor 5. 4. 1. Deklaration 5. 4. 2. Verwendung 5. 4. 3. Beispiele 5. 5. Transaktionen 5. 5. 1. Einfache Transaktionen 5. 5. 2. Sicherungspunkte 5. 5. 3. Erweiterte Transaktionssteuerung 123
123
123
123
125
125
126
127
127
129
130
130
131
133
136
136
139
139
6. Programm-Module in der DB 6. 1. Prozeduren 142
142
4
http://www.comelio-medien.com/buch-katalog/e-books/sql
Inhaltsverzeichnis
6. 1. 1. Einführung 6. 1. 2. Prozedurarten 6. 1. 3. Parameter und Aufruf 6. 2. Funktionen 6. 2. 1. Skalare Funktionen 6. 2. 2. Tabellenwertfunktion 6. 3. Trigger 6. 3. 1. Grundlagen 6. 3. 2. DML-Trigger 6. 3. 3. DDL-Trigger 6. 3. 4. Weitere Optionen 7. Administration 7. 1. Sicherheit 7. 1. 1. Allgemeine Überlegungen zur Sicherheit 7. 1. 2. Datensicherheit 7. 1. 3. Zugriffskontrolle 7. 1. 4. Rollen 7. 1. 5. Benutzer verwalten 7. 1. 6. Rechte verwalten 7. 1. 7. Rechte kaskadierend weitergeben 7. 1. 8. Sicherheit von Prozeduren, Funktionen und Trigger 7. 2. Sicherung und Wiederherstellung 7. 2. 1. Datensicherung 7. 2. 2. Wiederherstellung von Datenbanken 7. 2. 3. DB-Zustand http://www.comelio-medien.com/buch-katalog/e-books/sql
142
145
147
150
151
152
156
156
157
161
164
166
166
166
168
168
171
171
173
174
176
179
179
181
182
5
Vorwort
http://www.comelio-medien.com/buch-katalog/e-books/sql
Vorwort
Vorwort
Herzlich willkommen zu einem Fachbuch von Comelio Medien, einem Unternehmensbereich der Comelio GmbH. Wir hoffen sehr, dass Sie mit der Darstellung und Aufbereitung dieses Einstiegs in SQL
und relationale Datenbanken am Beispiel von MS SQL Server zufrieden sind und die für Ihren Berufsalltag wesentlichen Themen finden.
Zu dieser Reihe
Dieses Buch gehört zu einer Gruppe von verschiedenen Büchern bei Comelio Medien im Bereich relationale Datenbanken und Microsoft SQL Server. Sie umfasst Themen, welche bei der Verwendung als
Programmierer und Administrator wichtig sind. Die Reihe enthält folgende Themen:
●● Abfragen von relationalen Datenbanken mit T-SQLund PL/SQL und Programmierung von Prozeduren, Funktionen und Triggern
ƒƒ MS SQL Server: T-SQL-Programmierung und Abfragen, ISBN 978-3-939701-02-6, http://
www.comelio-medien.com/buch-katalog/ms_sql_server/t-sql
ƒƒ Oracle PL/SQL, ISBN 978-3-939701-40-8 http://www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql
ƒƒ Oracle SQL, ISBN 978-3-939701-41-5, http://www.comelio-medien.com/buch-katalog/
oracle/oracle_sql
●● Erzeugen von XML aus Oracle- und MS SQL Server-Daten sowie deren Verarbeitung
ƒƒ MS SQL Server: XML und SOAP Webservices - ISBN 978-3-939701-03-3, http://www.comelio-medien.com/buch-katalog/ms_sql_server/xml
ƒƒ Oracle, PL/SQL und XML, ISBN 978-3-939701-10-1, http://www.comelio-medien.com/
buch-katalog/oracle/oracle_und_xml
Autoren
Die beiden Autoren Marco Skulschus und Marcus Wiederstein blicken bereits auf zahlreiche Bücher
zu Themen rund um Programmierung und auch Datenbanken zurück. Bei der Comelio GmbH sind sie
im Bereich Softwareentwicklung als Projektleiter und auch als Dozenten tätig und stellen mit Hilfe der
Veröffentlichungen ihre Erfahrungen im Bereich der Softwareentwicklung einem breiteren Publikum
zur Verfügung. Dies geschieht auch im Bereich Weiterbildung, der von der Comelio GmbH im gesamten
deutschsprachigen Raum angeboten wird.
Marco Skulschus (Jahrgang 1978) studierte Ökonomie in Wuppertal und Paris. Er setzt C# und den MS
SQL Server für Kundenprojekte ein. Nichtsdestoweniger ist er auch in anderen Themenbereichen wie
http://www.comelio-medien.com/buch-katalog/e-books/sql
7
Vorwort
Oracle/Java aktiv. Insbesondere der Themenbereich Datenbanken, Datenmodellierung und XML steht
an erster Stelle. Seine Spezialthemen sind Ontologien auf Basis von OWL (Web Ontology Language).
Marcus Wiederstein (Jahrgang 1971) studierte Elektrotechnik in Bochum und Dortmund. Er beschäftigt
sich nicht nur mit Softwareentwicklung auf Basis von Microsoft-Technologien, sondern auch mit der
Administration von Microsoft-Servern. Seine speziellen Interessengebiete sind die Sicherheit und die
Planung von sicheren Anwendungsstrukturen.
Marco Skulschus und Marcus Wiederstein haben zusammen bereits viele Bücher zu XML sowie zu Datenbanken herausgebracht. Darüber hinaus haben sie unterschiedliche Zertifizierungen von Microsoft
und Oracle erworben.
Aufbau des Buchs
1. Das erste Kapitel beschreibt zunächst Grundlagen zur Arbeitsumgebung. Es beschreibt bspw. kurz
die Installation der Datenbank, den Umgang mit dem grafischen Werkzeug Management Studio
sowie die Beispieldatenbank AdventureWorks. In der zweiten Hälfte des Kapitels lernen Sie die
wesentlichen Prinzipien von relationalen Datenbanken kennen. Diese umfassen die Themen der
Datenmodellierung mithilfe des Entity-Relationship-Modells, den Prozess der Normalisierung, den
Aufbau von relationalen Datenbanksystemen und eine allgemeine Einführung in das relationale
Modell.
2. Das zweite Kapitel stellt Standard-SQL am Beispiel vom MS SQL Server und vor allen Dingen der
AdventureWorks-Datenbank vor. Dabei beginnt es mit einfachen Standardabfragen und arbeitet
sich dann durch die typischen Bereiche wie Filtern, Sortieren und Gruppieren, die nicht nur mit
dem MS SQL Server, sondern mit jeder Datenbank möglich sind. Es stellt anschließend die verschiedenen Standard-SQL-Funktionen für Aggregate und damit für Datengruppierungen vor.
3. Das dritte Kapitel konzentriert sich auf die Darstellung von so genannten komplexen Abfragen.
Dies bedeutet zunächst, dass man die Daten nicht nur aus einer einzigen Tabelle abruft, sondern
mehrere Tabellen miteinander über ihre Primärschlüssel-Fremdschlüssel-Verknüpfung verbinden
muss. Hier stellt das Kapitel die traditionelle Variante den neuen, so genannten ANSI-SQL-Verknüpfungen gegenüber. Eine zweite Stufe hinsichtlich der Verwendung von komplexen Abfragen
ist dann der Einsatz von Unterabfragen. Hier folgt eine Darstellung von einfachen Unterabfragen,
Spaltenunterabfragen, abgeleiteten Tabellen und korrelierten Unterabfragen. Die verschiedenen
Techniken sind in vielen Datenbanken gleich oder wenigstens ähnlich nutzbar. Schließlich folgt
noch die Darstellung, wie man Fallunterscheidungen über die CASE-Anweisung in SQL realisiert
und wie zusätzliche Aggregate errechnet werden können. Darunter sind Rangfolgen, Untersummen und Würfel zu verstehen.
4. Das vierte Kapitel arbeitet die Bereiche der Einrichtung von Datenstrukturen und der Datenmanipulation durch. In einem ersten Teil erstellt man über SQL die Datenstrukturen für Tabellen und
Sichten. In einem zweiten, umfangreicheren Teil werden dann für verschiedene vereinfachte Tabellen der Beispieldatebank die typischen Bearbeitungsszenarien von Datenerfassung, -bearbeitung, -aktualisierung und -löschung vorgestellt.
5. Das fünfte Kapitel bietet schließlich eine übersichtliche Einführung in die SQL-Erweiterung von
MS SQL Server mit dem Namen Transact SQL (T-SQL). Zwar gibt es in einigen vorherigen Kapiteln
bereits verschiedene Beispiele, die mit einfachen Mitteln von T-SQL operieren, doch die Erstellung
von Variablen, die Verwendung und die Auswahl von geeigneten Datentypen, die Erstellung und
Nutzung von Cursorn sowie schließlich auch die Erstellung von Prozeduren und Funktionen ist den
8
http://www.comelio-medien.com/buch-katalog/e-books/sql
Vorwort
einzelnen Abschnitten dieses fünften Kapitels vorbehalten. T-SQL steht hier als Beispiel für andere
SQL-Erweiterungen wie sie in Oracle oder IBM DB2 zu finden sind.
6. Das sechste Kapitel erweitert die Möglichkeiten der Programmierung in der Datenbank und zeigt,
wie man für einfache Administration oder die Anwendungsentwicklung Module in der Datenbank
erstellt. Jeweils werden Funktionen, Prozeduren und Trigger theoretisch und mit praktischen Beispielen vorgestellt.
7. Das siebte Kapitel schließt das Buch dann mit einigen administrativen Themen ab. Hier sollen nicht
MS SQL Server-spezifische Themen behandelt werden, sondern Bereiche wie Benutzer, Rollen und
Rechte sollen stattdessen anhand von allgemeinen Beispielen präsentiert werden. Neben dem
Thema Sicherheit enthält dieses Kapitel auch Erläuterungen und entsprechende SQL-Skripte für
Daten-Sicherung und Wiederherstellung.
Beispieldateien
Das Beispiel-Datenbank-System ist MS SQL Server, von der auch eine Test-Version und eine kostenlose
so genannte „Express-Version“ vorliegen. Als Beispiel-Datenbank dient die sehr umfangreiche Datenbank AdventureWorks, welche im ersten Kapitel kurz eingeführt und vorgestellt wird. Die Datenbank
kann sehr leicht zusätzlich installiert und kostenlos aus dem Internet herunter geladen werden.
Die verschiedenen Abfragen und Programmdateien, welche in diesem Buch erstellt und diskutiert
werden, liegen ebenfalls im Internet zum Download bereit. Die einzelnen Quelltexte sind vollständig
dokumentiert und enthalten neben dem eigentlichen Quelltext auch in einem Kommentarbereich die
Ergebnisse. Dies ermöglicht es, die Dateien auch ohne Testen vollständig zu verwenden.
Die Beispiel-Skripte und auch Seminar-Folien für den Unterrichtseinsatz oder das Selbststudium finden
Sie unter http://www.comelio-medien.com/buch-katalog/e-books/sql.
Kontakt zu den Autoren
Sie erreichen das Sekretariat der Autoren unter der E-Mail-Adresse [email protected]. Von dort werden Ihre Emails dann weitergeleitet. Die Webseite des Verlags finden Sie unter der Adresse http://
www.comelio-medien.com, während Sie auf der Unternehmenswebseite folgende weitere Informationen finden:
●● SQL-Artikel: http://www.comelio-medien.com/comelio-blog
●● Kurzreferenzen: http://www.comelio-medien.com/leserservice/kurz-referenzen
Auch in der realen Welt ist der Verlag zu erreichen. Die Hauptzentrale befindet sich in Berlin, Deutschland.
Comelio GmbH, Goethestr. 34, D-13086 Berlin
http://www.comelio-medien.com/buch-katalog/e-books/sql
9