MS SQL Server - Einstieg in relationale Datenbanken und
Werbung
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
