1 Structured Query Language - SQL · sowie die Open Source Produkte MySQL und PostgreSQL. ... DDL)...

1 Structured Query Language - SQL

Andreas Schmidt

1.1 Übersicht

Ende der 60er Jahre entwickelte E.F. Codd bei IBM das relationale Datenmodell [Cod70].Es ist streng formal aufgebaut, basierend auf der Relationenalgebra und dem Relatio-nenkalkül. Das Modell besticht sowohl durch seine Einfachheit, als auch durch seineExaktheit. Codd beschreibt in seinem Modell sowohl die strukturellen Eigenschaften,Integritätsaspekte als auch eine Reihe von Operationen aufden Daten.

Ein Großteil der sich heute produktiv im Einsatz befindlichen Datenbanksysteme basie-ren auf diesem Modell. Bekannte Vertreter sind etwa Oracle,der Microsoft SQL-Server,sowie die Open Source Produkte MySQL und PostgreSQL.

• SQL stellt die Anfrage und Datendefinitionssprache für relationale Datenbanksys-teme dar.

• SQL ist deskriptiv, d.h. der Anwender formuliert welche Informationen er habenmöchte, nicht jedoch, wie er an diese Informationen kommt.

• SQL ist mengenorientiert.

1.2 Grundlegende Konzepte des Relationenmodells

Bevor auf SQL als Anfrage- und Datendefinitionssprache für relationale Datenbankeneingegangen wird, werden im Folgenden einige elementare Kenntnisse des Relationen-modells vermittelt, die für das spätere Verständnis von SQLhilfreich sind. Eine ausführ-liche und detailierte Einführung in das Modell findet sich in[SKS01]. Die Einführungsoll anhand der beiden in Abbildung 1.1 dargestellten Relationencountry undcityerfolgen.

Abb. 1.1: Struktur relationaler Datenbanken

2 1 SQL

1.2.1 StrukturZentrale Komponente in Codds Modell ist dieRelation. Eine Relation besteht aus einerAnzahl von Tupeln, welche die Datensätze repräsentieren.

Die Struktur einer Relation (und somit auch der in ihr enthaltenen Tupel) wird durch dasRelationenschemavorgegeben. Ein Relationenschema besitzt einen eindeutigen Namenund wird durch eine Menge von Attributen beschrieben, die jeweils einen bestimmtenWertebereich (Domäne) besitzen. Es beschreibt somit eine Struktur, vergleichbar einemStructin C/C++ oder einemRecordin Pascal. Dabei ist aber zu beachten, dass die Werteder Attributeatomarsein müssen, d.h. keine mengenwertigen oder strukturierten Datenrepräsentieren dürfen.Die Gesamtheit aller Relationenschemata bildet dann das Datenbankschemata.Anschaulich wird eine Relation als Tabelle dargestellt. Die Attribute des Relationen-schemas werden zu den Spaltenbezeichnern, die Zeilen der Tabelle repräsentieren dieTupel und die Werte in den Tabellenfeldern entsprechen denen der Wertebereiche ihrerAttribute.Bei der Abbildung von Relationen auf Tabellen sind jedoch zwei Punkte zu beachten,die aus der Tatsache herrühren, dass es sich bei einer Relation um eine mathematischeMenge von Tupeln handelt:1. In einer Menge gibt es keine Reihenfolge, das heißt die Reihenfolge der Datensätze

(Zeilen) in den Tabellen ist willkürlich.2. Mengen erlauben keine Duplikate, d.h. es darf keine zwei Datensätze in einer Tabelle

geben, die identisch sind.

1.2.2 IntegritätDas Relationenmodell kennt weiterhin den Begriff des Primärschlüssels. Dabei handeltes sich um ein oder mehrere Attribute eines Relationenschemas, so dass es in dieserRelation keine zwei Tupel geben kann, die für dieses Attribut oder Attributkombinati-on identische Attributwerte besitzen. Mittels des Primärschlüssels ist es somit möglich,einzelne Tupel zu identifizieren. Dieser Sachverhalt wird im Relationenmodell dazu ge-nutzt um Beziehungen zwischen Datensätzen herzustellen. In Abbildung 1.1 sind dieAttribute, die den Primärschlüssel bilden unterstrichen.Da es in einer Menge von Datensätzen keine Duplikate gibt istgewährleistet, dass esfür jede Relation einen Primärschlüssel gibt. Spätestens durch die Hinzunahme allerAttribute eines Relationenschemas lässt sich ein Primärschlüssel konstruieren.Aufbauend auf dem Primärschlüssel existiert das Konzept des Fremdschlüssels mit demsich Beziehungen zwischen Datensätzen modellieren lassen. Ein Fremdschlüssel ist einAttribut oder eine Attributkombination, die auf den Primärschlüssel einer Relation1 ver-weist. Dabei muss gesichert sein, dass der Wert des Fremdschlüssels immer auf einexistierendes Tupel verweist, d.h. dass der Attributwert oder die Attributwertkombina-tion des Fremdschlüssels identisch mit einemexistierenden Primärschlüsselwertist.Dieser Sachverhalt wir alsreferentiellen Integritäteines Datenbanksystems bezeichnet.In Abbildung 1.1 sind zwei Fremdschlüsselbeziehungen zu sehen. Zum einen besitzt dieRelationcountry einen Fremdschlüssel, bestehend aus den Attributencode, capital

1 dabei kann es sich um eine beliebige andere- oder aber auch umdie selbe Relation handeln.

1.3 Organisation der Sprache 3

undprovince, welcher die Hauptstadt des Landes referenziert. Beispielhaft ist dies amDatensatz der Schweiz dargestellt. Zum zweiten besitzt dieRelationcity den Fremd-schlüsselcountry, welcher das Land identifiziert in dem sich die Stadt befindet. Dies istbeispielhaft anhand der Städte Frankreichs angedeutet.Weitere mögliche Integritätsbedingungen sind die Einschränkung des Wertebereichs füreinzelne Attribute, das Erzwingen von Einträgen für bestimmte Attribute und das Er-zwingen der Eindeutigkeit von Attributwerten innerhalb einer Relation.

1.2.3 OperationenCodd formuliert eine Reihe von Operationen auf den Relationen. Im einzelnen han-delt es sich dabei umRestriktion, Projektion, Kartesisches Produkt, Join, Vereinigung,DurchschnittundDifferenz.All diese Operationen besitzen eine oder mehrere Relationen als Eingabe und lieferneine Relation als Ausgabe zurück [Dat05].Dies bedeutet insbesondere, dass die Ausgabe einer Operation, stets wieder als Eingabeeiner anderen Operation dienen kann, was die Formulierung geschachtelter relationalerAusdrücke erlaubt.In Abschnitt 1.6.2 wird die Umsetzung obiger Operationen auf SQL-Statements be-schrieben.

1.3 Organisation der Sprache

SQL hat inzwischen eine Reihe von Standardisierungsrundendurchlaufen. Das Ameri-can National Standards Institute (ANSI) setzte 1986 den ersten Standard (SQL1 oderSQL-86). Weitere Standardisierungen fanden 1989 und 1992 (bekannt als SQL2 bzw.SQL-92) statt, sowie zuletzt die als SQL3 (SQL-99) bekannt gewordene Aktualisierungdes Standards im Jahr 1999. SQL ist weiterhin auch von der International Standard Or-ganisation (ISO) anerkannt.

Im SQL2 Standard werden drei Anweisungsklassen definiert, die auch die Grundlagefür die Strukturierung in diesem Kapitel bilden.• Datenmanipulationssprache (Data Manipulation Language,DML) : Diese Klasse

enthält Sprachelemente zur Bearbeitung von Daten. Operationen sind das Einfügen(insert ), Löschen (delete ), Modifizieren (update ) und Abfragen (select )von Daten.Die zur DML gehörenden Operatoren werden zur Laufzeit einer Datenbankanwen-dung eingesetzt, um auf die Daten in den zuvor mittels derDDL (s.u.) angelegtenTabellen zuzugreifen.

• Datendefinitionssprache (Data Definition Language, DDL): Die Sprachelementein dieser Klasse erlauben das Anlegen und Modifizieren der Datenstrukturen. Typi-sche Vertreter sind die Sprachelementecreate table , alter table , etc.Die Befehle dieser Klasse werden hauptsächlich zur Entwicklungszeit einer Daten-bankanwendung eingesetzt. Sie erlauben die Definition der Datenstrukturen und Ta-bellen, auf die dann zur Laufzeit mittels den Befehlen derDML lesend und schreibendzugegriffen wird.

4 1 SQL

• Datensteuerungssprache (Data Control Language, DCL): Die Sprachelemente indieser Klasse erlauben das Anlegen und Modifizieren von Benutzern und Rollen so-wie die Vergabe von Zugriffs- und Systemrechten. Typische Vertreter sind die Befehlecreate user zum Anlegen von neuen Benutzern sowiegrant undrevoke mitdenen bestimmte Berechtigungen vergeben und wieder entzogen werden können.

1.4 Datentypen

In Abschnitt 1.2.1 wurde gesagt, das jede Spalte einer Relation einen bestimmten Wer-tebereich hat. Diese Wertebereiche werden in SQL durch Datentypen festgelegt. Unab-hängig vom konkreten Datenbanksystem2 lassen sich die Datentypen in folgende Kate-gorien untergliedern:• Zeichenketten: Erlauben das Speichern von beliebigen Kombinationen von Zeichen.

Es wird zwischen variabel langen Datentypen (varchar(<size>), CLOB3) und Da-tentypen mit fester Länge (char(<size>)) unterschieden. Bei Datentypen mit festerLänge wird die Zeichenkette am Ende mit Leerzeichen aufgefüllt, bei Datentypenvariabler Länge wird nach dem letzten Zeichen abgeschnitten. Zum Teil kann ei-ne Obergrenze, wie viel Zeichen maximal gespeichert werdendürfen, angegebenwerden, teils sind die Grenzen fest vorgegeben. Weiterhin gibt es Varianten obigerTypen die mit verschiedenen nationalen Zeichensätzen umgehen können (z.B.nvar-char<size>).

• Numerisch: Es können sowohl ganze Zahlen als auch Fließkommazahlen gespeichertwerden. Die Typen unterscheiden sich in ihrer Genauigkeit und Stellenzahl. BeimDatentypfloatkann bei der Definition beispielsweise die Anzahl der Stellen vor undnach dem Komma mit angegeben werden.Vertreter numerischer Datentypen sindinteger, smallint, numeric, float, real unddou-ble precision.

• Zeitlich : Es gibt Datentypen zum Speichern von Datum (Date) und Uhrzeit (Time),mit und ohne Zeitzone. Weiterhin gibt es den DatentypInterval, der eine Zeitdaueraufnimmt.

• Binär : Der DatentypBLOB4 speichert beliebige binäre Zeichenketten. Die Größeliegt dabei meist im Gigabyte Bereich.

• Boolean: Datentyp zur Aufnahme der WerteTRUEundFALSE.• Bit-String : Datentyp zur Aufnahme binärer Daten, mit fester oder variabler Länge.

Es gibt den speziellen WertNULL für alle Datentypen. Der WertNULL für ein Attributsagt aus, dass für dieses Attribut kein Wert bekannt ist. Er ist auf keinen Fall mit demWert 0 für numerische Typen oder dem leeren String für Zeichenketten zu verwechseln.Eine Besonderheit ist, dass der WertNULL von sich selbst verschieden ist, dh. es giltNULL 6= NULL5.

2 aus historischen Gründen unterscheiden sich die Datentypen der Hersteller zum Teil.3 CharacterLargeObject4 BinaryLargeObject5 Dies bedeutet insbesondere auch, dass die BedingungA = A nur für Attributwerte ungleichNULLwahr ist.

1.5 Syntax 5

Wie bereits erwähnt, findet man die hier beschrieben Datentypen nicht unbedingt so inden konkreten Datenbanksystemen wieder, sondern diese weichen von der Namensge-bung und/oder der Semantik oft etwas vom Standard ab. Eine Abbildung der im SQL3Standard vorgeschlagenen Datentypen auf konkreten Datentypen einer Reihe populärerDatenbanksysteme findet sich in [KK01].In SQL3 wurde weiterhin festgelegt, dass sich aus den vorgegebenen Datentypen eigene(auch strukturierte) Datentypen ableiten lassen. Diese können dann ebenso wie die vomSystem vorgegebenen Datentypen verwendet werden. Auf diesen Punkt soll hier jedochnicht weiter eingegangen werden, der interessierte Leser sei auf [Tür03] verwiesen.

1.5 Syntax

Die Syntax von SQL ist relativ einfach. Die Befehle derDML, DDL undDCL begin-nen stets mit einem oder mehreren Schlüsselwörtern, welcheden konkreten Befehl spe-zifizieren. Es können beliebige Einrückungen und Umbrüche innerhalb eines Befehlsgetätigt werden. Werden mehrere Befehle hintereinander geschrieben, so werden dieeinzelnen Befehle mit einen Strichpunkt (;) beendet.

1.5.1 Groß-/KleinschreibungSchlüsselwörter können beliebig groß- oder kleingeschrieben werden. Genauso verhältes sich mit Bezeichnern für Tabellen6, Indexe, Attributen, etc.

1.5.2 LiteraleWerte für Zeichenketten und Datumsangaben werden in einfache Hochkommas (’), nu-merische Literale werden ohne Hochkommas geschrieben.Beispiel:

insert into city (name, country, province, population)values (’Karlsruhe’, ’D’, ’Baden Württemberg’, 277011)

1.5.3 KommentareSQL kennt zwei Arten von Kommentaren . Zum einen gibt es die aus vielen ande-ren Sprachen bekannten Kommentarklammern/ * . . .* / , die Kommentare über mehrereZeilen enthalten können.Beispiel:

/ ** Autor : Smiff* Version : 1.22* /

6 Bei MySQLspielt die Groß-/Kleinschreibung z.T. eine Rolle, da die einzelnen Tabellen aufDateien mit dem Namen der Tabelle abgebildet werden. Unterscheidet das zugrunde liegendeBetriebssystemen zwischen Groß- und Kleinschreibung, so muss die Schreibweise konsistent sein.

6 1 SQL

Weiterhin gibt es noch Kommentare, die nur bis ans Ende der aktuellen Zeile reichen. InSQL werden diese durch zwei hintereinander geschriebene Minuszeichen eingeleitet.Beispiel:

-- ab hier kommt die Definition der Foreign Keys

1.6 Data Manipulation Language

Im folgenden Abschnitt soll nun die Data Manipulation Language (DML) von SQL vor-gestellt werden, d.h. der Teil, der sich mit Anfragen und Modifikationen an bestehendenDatenbanken beschäftigt. DieDML stellt die Umsetzung der Operationen der Relatio-nenalgebra (Abschnitt 1.2.3) dar.Die verschiedenen Konzepte werden zuerst kurz abstrakt dargelegt und dann anschlie-ßend an einem oder mehreren Beispielen verdeutlicht. Die Beispiele setzen alle auf derMondial-Datenbank[Mon99] auf, die unter der angegebenen URL für Trainings- undLehrzwecke frei verfügbar ist.Bevor nun im Folgenden mit der Einführung in dieDML begonnen wird, werden zu-erst noch die im Rahmen der Beispiele benutzten Tabellen ausderMondial-Datenbankvorgestellt.

1.6.1 Mondial DatenbankDie gesamteMondial-Datenbankumfasst ca. 25 Tabellen, von denen hier aber nur we-nige benötigt werden. In Abbildung 1.2 ist ein Ausschnitt des Abhängigkeitsschemader Mondial-Datenbankdargestellt. Die Kästen repräsentieren die Tabellen mit ihrenAttributen. Bei unterstrichenen Attributen handelt es sich um Primärschlüssel, grau un-terlegte Attribute stellen Fremdschlüssel dar.

Abb. 1.2: Auszug aus Abhängigkeitsschema derMondial-Datenbank

1.6 Data Manipulation Language 7

In der oberen Hälfte der Abbildung sieht man die Tabellenprovince , country undcity und ihre Beziehungen zueinander.

Der Primärschlüssel der Tabellecity besteht aus den drei Attributenname, provin-ce und country. Teile des Primärschlüssels (province, country) bilden weiterhin einenFremdschlüssel auf die Provinz, in der sich die Stadt befindet. Dadurch wird eine 1 :n-Beziehung7 zwischen einer Provinz und den darin liegenden Städten realisiert. Die übri-gen Attribute geben die Einwohnerzahl (population) und die Geo-Koordinaten der Stadt(longitude, latitude) an.Der Primärschlüssel der Tabelleprovince besteht aus den Attributennameundcoun-try. countrywiederum ist ein Fremdschlüssel auf das Schlüsselattributcodeder Tabellecountry und stellt den Bezug zwischen einer Provinz und dem zugehörigen Land her.

Tabellecountry besitzt neben dem Primärschlüsselcodeeinen dreiteiligen Fremd-schlüssel (code, capital, province), der auf den entsprechenden Hauptstadt-Datensatzin der Tabellecity verweist. Hierbei handelt es sich um die Umsetzung einer 1 : 1-Beziehung8 zwischen einem Land und der zugehörigen Hauptstadt.In der unteren Hälfte des Abhängigkeitsschemas sieht man die beiden Tabellenriverundgeo_river . Tabelleriver benutzt den Namen des Flusses (Attributname) alsPrimärschlüssel. Die nächsten 3 Attribute (river, lake, sea) sind jeweils Fremdschlüsselund geben an, ob der Fluss in einen anderen Fluss (river), einen See (lake) oder in einMeer (sea) mündet.Die Beziehung zwischen Provinzen und Flüssen wird über die Tabellegeo_riverhergestellt, die hier ausschnittsweise wiedergegeben wird:

River Country Province

. . . . . . . . .

Donau RO Braila

Donau RO Tulcea

Donau D Baden Württemberg

Donau A Lower Austria

Drin AL Albania

Elbe D Niedersachsen

Elbe D Hamburg

Fulda D Hessen

Gambia WAG Gambia

Gambia SN Tambacounda

. . . . . . . . .

447 Datensätze

Hierbei handelt es sich um eine so genannte Beziehungstabelle. Beziehungstabellen wer-den in relationalen Datenbanken zur Modellierung vonn : m- Beziehungen eingesetzt.

7 Jede Stadt gehört zu genau einer Provinz, umgekehrt kann eine Provinz mehrere Städte haben.8 Dass eine Stadt maximal Hauptstadt eines Landes sein kann, wird durch die Formulierung einesunique-Constraints(siehe Abschnitt 1.7.1) erreicht.

8 1 SQL

Während sich 1 : 1- und 1 :n-Beziehungen, wie zuvor gesehen, problemlos durch dieHinzunahme eines Fremdschlüssels9 modellieren lassen, wird bein : m-Beziehungeneine zusätzliche Tabelle benötigt. Eine Beziehungstabelle besteht aus mindestens zweiFremdschlüsseln, die auf die in Beziehung stehenden Tabellen verweisen. Konkret be-steht die Tabellegeo_river aus einem Fremdschlüssel auf den betreffenden Fluss(Attribut river) und einem Fremdschlüssel auf die Provinz (Attributeprovince, country)die der Fluss durchfließt. Somit ist es möglich, dass ein Fluss mehrere Provinzen durch-fließen kann, umgekehrt kann eine Provinz auch von mehreren Flüssen durchflossenwerden. Ein Auszug aus Tabellegeo_river ist in nachfolgender Tabelle dargestellt.Im Folgenden sollen nun anhand obiger Tabellen dieDML Befehle vorgestellt werden.

Hinweis. Es wird empfohlen, die Beispiele an einem realen System nachzuvollziehenund mit ihnen etwas zu “spielen”. Dazu gibt es auf der Webseite [Smi05] einen webbasierten Zugang zu einerMondial-Datenbank, einschließlich einer Sammlung aller imFolgenden vorgestellten Statements10. Damit lassen sich die Anfragen sofort am Daten-bestand nachvollziehen und variieren, ohne lokal ein Datenbanksystem installieren zumüssen.

1.6.2 Select-StatementAls erstes soll nun im Folgenden dasselect -Statement eingeführt werden. Es ist fürdie Extraktion von Informationen aus der Datenbank zuständig.Das einfachsteselect -Statement ist die Ausgabe aller Daten einer Tabelle. Dazu wirdin derselect -Klausel mittels dem Wildcardzeichen* signalisiert, dass alle Spaltenausgegeben werden sollen. In derfrom -Klausel wird die Tabelle angegeben. Das Er-gebnis sind dann alle Datensätze, die sich in der betreffenden Tabelle befinden.

select *from river

Name River Lake Sea Length

. . . . . . . . . . . . . . .

Mekong South China Sea 4500

Orinoco Atlantic Ocean 2736

Werra Weser 292

Fulda Weser 218

Columbia River Pacific Ocean 1952

. . . . . . . . . . . . . . .

132 Datensätze

Projektion. Sollen nicht alle Spalten einer Tabelle ausgegeben werden,so kann man inderselect -Klausel die Attribute festlegen, die ausgegeben werden sollen.

9 der Fremdschlüssel kommt in die Tabelle, deren Datensätze jeweils mit maximal einem anderenDatensatz in Beziehung stehen.10 nur select -Statement, Datenmanipulationen sind nicht erlaubt.


select name, capitalfrom country

name capital

Albania Tirane

Greece Athens

Macedonia Skopje

. . . . . .

195 Datensätze

Sortierung. Soll die Ausgabe sortiert werden, so wird dieorder -Klausel verwendet,welche ans Ende desselect -Statements angefügt wird. Die Klausel nimmt eine Listevon Spalten entgegen, nach denen dann sortiert werden soll.Wird mehr als ein Attributangegeben, so erfolgt die Sortierung der Priorität nach absteigend von links nach rechts.Es können entweder Namen der Attribute nach denen sortiert werden soll angegebenwerden oder es können Spaltennummern (beginnend bei 1) angegeben werden, die sichauf die Position der Attribute in derselect -Klausel beziehen. Standardmäßig wirdaufsteigend (asc ) sortiert, soll absteigend sortiert werden so ist (desc ) hinter demAttribut bzw. der Spaltennummer anzugeben.Im folgenden Beispiel werden der Name und die Länge der Flüsse aus Tabelleriverabsteigend sortiert nach der Länge der Flüsse ausgegeben.

select name, lengthfrom river

order by length desc

name length

Amazonas 6518

Jangtse-Kiang 6100

Parana 4700

Mekong 4500

. . . . . .

132 Datensätze

Bedingungen. Bisher wurden immer alle Datensätze einer Tabelle ausgegeben. Willman jedoch nur Datensätze, die eine bestimmte Bedingung erfüllen ausgeben, so benö-tigt man diewhere -Klausel, welche die Formulierung von Bedingungen ermöglicht.

Bedingungen werden auf den Attributen formuliert und können mit not , and , or zukomplexeren Bedingungen zusammengesetzt werden11. Zusätzlich können Klammerneingesetzt werden, so dass sich beliebige Ausdrücke formulieren lassen.Als Operatoren stehen unter anderem die “üblichen Verdächtigen” (=, <> 12, <, <=,>, >= ) zur Verfügung. Der Vergleich erfolgt entweder mit einem Literal oder einemanderen Attribut.

Im folgenden Beispiel werden alle Flüsse ausgegeben, die inden Atlantischen Ozean(Literal ’Atlantic Ocean ’) münden.

11 not hat dabei die höchste Bindungspriorität, gefolgt vonand undor12 6=

10 1 SQL

select name, sea, lengthfrom river

where sea=’Atlantic Ocean’order by name

name sea length

Amazonas Atlantic Ocean 6518

Connecticut River Atlantic Ocean 660

Cuanza Atlantic Ocean

Cunene Atlantic Ocean

. . . . . . . . .

19 Datensätze

Eine weitere Möglichkeit ist die Formulierung von Bedingungen mittels Musterver-gleich. Dazu gibt es in SQL die Operatorenlike undnot like . Die beiden Sonder-zeichen ’%’ und ’_’ stellen dabei so genannte Wildcards dar. ’%’ steht für eine beliebigeAnzahl von Zeichen, ’_’ steht für exakt ein Zeichen.Die Bedingungstadtname like ’%burg’ liefert beispielsweise alle Städte zu-rück, die auf “burg” enden, so z.B. Hamburg, Salzburg, Sankt Petersburg.

Neuere Versionen einzelner Datenbanksysteme erlauben zudem die Suche mittelsregu-lären Ausdrücken[Fri00], einer mächtigen Sprache zur Formulierung von Textmustern.Diese Funktionalität ist jedoch nicht standardisiert und soll hier auch nicht weiter vertieftwerden.Weiterhin gibt es noch den Operatorbetween , der zur Bereichsüberprüfung eingesetztwerden kann.Beispiel:einwohner between 1000 and 20000

gibt alle Datensätze zurück, deren Einwohnerzahl zwischen1000 und 20000 Einwohnerliegt.Um ein Attribut gegenüber eine Menge von Werten zu testen kann man entweder meh-rere Bedingungen mittelsor verbinden oder denin -Operator benutzen.Beispiel:

code in (’F’,’ES’,’I’,’GR’)

gibt alle Länder zurück, derencodeentweder ’F’, ’ES’, ’I’ oder ’GR’ ist.Der in -Operator wird uns später auch noch bei denSubqueries(Seite 14) begegnen, wostatt der Liste von Literalen ein vollständigesselect -Statement stehen kann.

Umgang mit NULL. Eine besondere Bedeutung spielt, wie in Abschnitt 1.4 bereitsangesprochen, der AttributwertNULL. Da NULL auch von sich selbst verschieden ist,führt die Formulierung folgender Bedingung immer zuFALSE:

attributname = NULL

Aus diesem Grund stellt SQL zwei spezielle Operatoren zur Verfügung:• is null : testet ob der Wert eines Attributs NULL ist.• is not null : testet ob der Wert eines Attributs ungleich NULL ist.

Das folgende Beispiel wählt alle Flüsse aus, die in einen Seemünden und länger als1000 Kilometer sind.


select name,lake,lengthfrom river

where lake is not nulland length > 1000

name lake length

Kura Caspian Sea 1364

Rhein Bodensee 1320

Volga Caspian Sea 3531

Amudarja Ozero Aral 1415

. . . . . . . . .

6 Datensätze

Duplikatseliminierung. Je nach Anfrage ist es möglich, dass identische Zeilen im Er-gebnis erscheinen. Wünscht man dies nicht, so kann dies durch Angabe des Schlüssel-wortesdistinct innerhalb derselect -Klausel unterbunden werden.

So führt die nahe liegende Lösung auf die Frage nach “allen Flüssen die Zuflüsse be-sitzen” nicht zum gewünschten Ergebnis, da viele Flüsse mehrere Zuflüsse haben undsomit in der Ergebnismenge mehrfach auftreten.

select riverfrom river

where river is not nullorder by 1

river

Amudarja

Amur

Amur

Busira

. . .

57 Datensätze

Erst durch die Hinzunahme vondistinct erscheint das erwünschte Ergebnis.

select distinct riverfrom river

where river is not nullorder by 1

river

Amudarja

Amur

Busira

Dalaelv

. . .

27 Datensätze

Spaltenaliase.Sollen die Überschriften der Spalten verändert werden, so werdenSpal-tenaliaseeingesetzt Spaltenaliase werden in derselect -Klausel hinter der jeweiligenSpalte und dem Schlüsselwortas angegeben.

Das folgende Beispiel zeigt die Daten der Tabelleriver mit deutschen Spaltenüber-schriften. Der zweite Spaltenalias bestehen aus mehr als einem Bezeichner und mussdeshalb in doppelten Anführungszeichen (" ) stehen.

12 1 SQL

select name as Flussname,round(length/1.609) as

"Länge (Meilen)"from river

where length is not nullorder by 2 desc

Flussname Länge (Meilen)

Amazonas 4051

Jangtse-Kiang 3791

Parana 2921

Mekong 2797

. . . . . .

90 Datensätze

Man sieht weiterhin, dass es hiermit auch möglich ist, berechnete Spalten (Umrechnungdes Wertes der Spaltelengthvon Kilometer nach Meilen)13 einen sinnvollen Namen zugeben. Dieorder -Klausel gibt schließlich an, dass die Ausgabe absteigend nach der2. Spalte sortiert erfolgen soll.

Aggregatsfunktionen. SQL stellt eine Reihe von Funktionen zur Verfügung, welcheals Eingabe eine Tupelmenge (Menge von Datensätzen oder Menge von Einzelwerten)erwarten, als Ausgabe jedoch nur einen einzigen Wert zurückliefern. Im Unterschied zunormalen Funktionen (siehe Tabelle 1.6.2) arbeiten diese Funktionen somit tupel über-greifend und werden deshalb alsAggregatsfunktionenbezeichnet.SQL kennt folgende Aggregatsfunktionen:• count(<Attributbezeichner>) bzw.count( * ) : Zählt die Anzahl der Ele-

mente in der übergebenen Menge und liefert deren Anzahl zurück. Es können entwe-der komplette Datensätze (symbolisiert durch* ) oder einzelne Attribute (Angabe desAttributnamens) an die Funktion übergeben werden.Im Fall, dasscount(...) mit einem Attributnamen aufgerufen wird ist wichtig zuwissen, dass nur Nicht-NULLWerte gezählt werden.Sollen keine Duplikate gezählt werden, so kann mitcount(distinct ...) vordem Zählen eine Duplikatseleminierung durchgeführt werden.

• sum(<Attributbezeichner>) : Summiert die einzelnen Werte der übergebe-nen Zahlenmenge auf. Das Attribut muss numerisch sein.

• avg(<Attributbezeichner>) : Liefert den Durchschnitt der einzelnen Werteder übergebenen Zahlenmenge. Das Attribut muss numerisch sein.

• stddev(<Attributbezeichner>) 14 : Liefert die Standardabweichungder ein-zelnen Werte der übergebenen Zahlenmenge. Das Attribut muss numerisch sein.

• min(<Attributbezeichner>) : Liefert den kleinsten Wert der einzelnen Wer-te der übergebenen Wertemenge.

• max(<Attributbezeichner>) : Liefert den größten Wert der einzelnen Werteder übergebenen Wertemenge.

Das folgende Beispiel ermittelt die Länge des längsten Flusses:

13 Die Grundrechenarten (+,−,∗, /) sowie die Funktionround(...) sind in SQL standardmä-ßig verfügbar. Eine Übersicht über weitere Funktionen findet sich in Tabelle 1.1 auf Seite 21.14 BeimMS SQL-Serverheißt diese Funktionstdev


select max(length) as Längefrom river

Länge

6518

1 Datensatz

Gruppierungen. So wie die Aggregatsfunktionen im obigen Beispiel angewandt wur-de, liefert sie für einselect -Statement genau einen Datensatz zurück. Indem wir dieDatensätze zuvor gruppieren, kann die Aggregatsfunktion auf jede Gruppe einzeln an-gewandt werden.

Um eine Gruppierung durchzuführen, benötigt man diegroup by -Klausel. Der Klau-sel folgt eine Liste von Attributen, nach denen gruppiert werden soll. Die Gruppierungerfolgt dann derart, dass alle Datensätzen, deren Werte beiden in dergroup by -Klausel spezifizierten Attributen übereinstimmen, zusammengefasst werden.Das folgende Beispiel in Abbildung 1.3 zählt für jeden Flussin der Datenbank, wievieleZuflüsse er hat. Dazu werden in einem ersten (internen15) Schritt, alle Datensätze de-ren Werte in der Spalteriver übereinstimmen in eine Gruppe zusammengefasst (Mitte).Anschließend wird dann für jede Gruppe gezählt, wieviele Datensätze in ihr existierenund diese Information, absteigend sortiert nach der Anzahlder gruppierten Datensätzesowie anschlisSSend den Namen der Quellflüsse, ausgegeben (Rechts).

Abb. 1.3: Gruppieren nach Flüssen mit Aggregation

15 die mittlere Ausgabe dient lediglich der Verdeutlichung der Gruppierung, wird aber nicht aus-gegeben

14 1 SQL

Bedingungen auf Aggregaten.Als nächstes sollen jetzt auf den Aggregaten Bedingun-gen formuliert werden. So sollen beispielsweise nur die Flüsse ausgegeben werden, diedrei oder mehr Zuflüsse besitzen.Schaut man sich das obige SQL-Statement an, so erkennt man, dass dies nicht in derwhere -Klausel passieren kann, da zum Zeitpunkt der Auswertung der where -Klauseldas Aggregat noch nicht errechnet ist, sondern hier nur entschieden wird, welche Daten-sätze überhaupt der Gruppierung zugeführt werden (nur solche die in der Spalterivereinen Wert stehen haben).Aus diesem Grund existiert diehaving -Klausel, welche es dann in einem nachge-schalteten Schritt erlaubt, Bedingungen auf den Ergebnissen von Aggregatsfunktionenzu formulieren. Im konkreten Beispiel sind das die Flüsse, für die die Spaltecount( * )einen Wert größer zwei liefert, d.h. der Fluss drei oder mehrZuflüsse hat.

select river,count( * ) as "Anzahl Zuflüsse"

from riverwhere river is not nullgroup by river

having count( * ) > 2order by 2 desc

river Anzahl Zuflüsse

Zaire 7

Ob 4

Nile 3

Kwa 3

White Nile 3

Zambezi 3

6 Datensätze

Das Statement gleicht dem zuvor mit dem einzigen Unterschied, dass in der neu hin-zugekommenenhaving -Klausel eine Bedingung formuliert wurde, in der eine Aggre-gatsfunktion vorkommt.

Subquery. Mittels einer Subquery können mehrere SQL-Statements kombiniert wer-den. Dazu wird in derwhere -Klausel einfach statt eines Literals ein in Klammer ste-hendesselect -Statement eingesetzt.So kann die Frage nach allen Flüssen, die eine überdurchschnittliche Länge haben, wiefolgt beantwortet werden:

name length

Amazonas 6518

Jangtse-Kiang 6100

Parana 4700

Mekong 4500

. . . . . .

34 Datensätze

Zuerst wird hierbei das Ergebnis des inneren, grau unterlegten,select -Statement er-mittelt. Anschließend wird dann die äußere Anfrage mit dem Ergebnis der inneren An-frage ausgeführt.Bei der Benutzung der Operatoren<, >, =, <> ist es notwendig, dass das innereStatement genau einen Datensatz zurückliefert. Dies ist oft dann der Fall, wenn eine


Bedingung auf dem Primärschlüssel formuliert wird, bzw. wenn wie im Beispiel eineAggregatsfunktion eingesetzt wird.

Eine andere Möglichkeit besteht darin, ein Attribut gegenüber einer Menge von Wertenzu vergleichen. Dazu wird der auf Seite 10 eingeführtein -Operator benutzt. Statt derkonstanten Literale wird einfach einselect -Statement in die Klammer geschrieben.Das folgende Beispiel gibt alle Ländernamen aus, die von derDonau durchflossen wer-den. Dazu werden in einem ersten Schritt innerhalb der grau unterlegten Subquery alleLändercodes extrahiert, durch welche die Donau entsprechend den Informationen ausder Tabellegeo_river (siehe Seite 7) fließt. Das äußere Statement nimmt diese Men-ge entgegen und gibt dann für alle Länder derencodesich in der von der Subqueryermittelten Menge befinden, den Landesnamen (name) aus.

name

Austria

Bulgaria

Germany

Hungary

. . .

7 Datensätze

Im obigen Beispiel konnte die innere Abfrage unabhängig vonder äußeren Abfrageausgeführt werden, man spricht in diesem Fall von einerunkorrelierten Subquery. Esist jedoch auch möglich, die innere Abfrage mit der Äußeren zu verzahnen. Dies wirddadurch realisiert, indem in derwhere -Klausel der inneren Anfrage auf ein Attributder äußeren Anfrage zugegriffen wird. In diesem Fall kann die innere Anfrage nichtmehr unabhängig von der Äußeren ausgeführt werden, sondernmuss für jedes Tupelder äußeren Anfrage erneut ausgeführt werden. Man spricht in diesem Fall von einerkorrelierten oderverzahnt geschachteltenAnfrage [HS00].Das folgende Beispiel zeigt eine derart verzahnte Abfrage.Die Anfrage soll alle Flüssezurückliefern, die durch mehr als 3 Länder fließen.

name

Amazonas

Amudarja

Dnepr

Donau

. . .

12 Datensätze

Die zusätztlich hier neu eingeführte Punkt -Notation dientzur eindeutigen Unterschei-dung der Attribute in den verschiedenen Tabellen. So gibt esbeispielsweise sowohl inder Tabelleriver , als auch in der Tabellecountry ein Attributname, die durch Vor-anstellen des Tabellennamens auseinander gehalten werdenkönnen.

16 1 SQL

Es gilt allgemein als guter Stil mit der Punktnotation zu arbeiten, da man dadurch schnel-ler erkennen kann, zu welcher Tabelle das jeweilige Attribut gehört. Die Notation wirdauch von allen relationalen Datenbanksystemen unterstützt.Das eigentlich Interessante an der grau unterlegten Subquery befindet sich in derwhere -Klausel, die lautet:where geo_river.river = river.name

In der Subquery ist das Attributriver.namejedoch unbekannt, da in derfrom -Klauselnur die Tabellegeo_river eingebunden wird.river.namestammt jedoch aus der äu-ßeren Query (durch Pfeil angedeutet), in der die Tabelleriver eingebunden ist und istsomit deren Attribute auch in der inneren Anfrage sichtbar sind.

Die Abfrage läuft nun wie folgt ab:1. Die äußere Anfrage untersucht den ersten Datensatz, konkret einen Fluss.2. Der Name des Flusses (Attributriver.name) wird jetzt an die Subquery übergeben,

die daraufhin untersucht, wie viele Datensätze mit unterschiedlichem Landescode(count(distinct geo_river.country) ) für diesen Flussnamen existieren.

3. Die Anzahl der Datensätze wird an die äußere Anfrage zurückgegeben und in derwhere -Klausel mit dem Literal3 verglichen. Ist der zurück gelieferte Wert größeroder gleich, so wird der Name des Flusses ausgegeben.

4. Anschließend wird von der äußeren Anfrage der nächste Datensatz gelesen und eswird mit Schritt (2) fortgefahren.

Ähnlich wie derin -Operator funktioniert derexists -Operator. Im Unterschied zumin -Operator wird derexists -Operator ohne Attribut und Operator eingesetzt. Er über-prüft lediglich, ob eine Subquery mindestens einen Datensatz zurückliefert. Ist das derFall liefertexists TRUE zurück, andernfallsFALSE. Derexists -Operator wird fastausschließlich mit korrelierten Subqueries eingesetzt.

Beispielsweise lässt sich die letzte Anfrage nach den Flüssen, die durch drei oder mehrLänder fließen auch mittels desexists -Operators und einerhaving -Klausel in dergrau unterlegten korrelierten Unterabfrage formulieren:

name

Amazonas

Amudarja

Dnepr

Donau

. . .

12 Datensätze

Mengenoperationen.SQL stellt eine Reihe von Mengenoperationen zur Verfügung,mittels denen die Ergebnisse von zweiselect -Statement kombiniert werden können.Im einzelnen handelt es sich um folgende Operationen:• union : Vereinigung der Tupel beider Anfragen.


• except: Es werden alle Tupel des erstenselect -Statement ausgegeben, die nichtim Ergebnis des zweitenselect -Statement stehen16.

• intersect : Es werden alle Tupel ausgegeben, die sich sowohl im Ergebnis der ersten-als auch der zweiten SQL-Anweisung befinden17.

Diese Operationen arbeiten als reine Mengenoperationen , d.h. es findet jeweils eineDuplikatseliminierung der Ergebnisrelation statt. Sollen Duplikate erhalten bleiben, somuss man auf die Operationenunion all , except all 18 bzw.intersect all 18

zurückzugreifen.Bei den Operationen ist darauf zu achten, dass die Tupel der beiden Ergebnismengeneine kompatible Struktur besitzen. Kompatibel heißt in diesem Fall, dass die AnzahlSpalten in den beidenselect -Klauseln identisch sein muss und die jeweiligen Daten-typen der Attribute kompatibel19 sind.Das folgende Beispiel vereinigt alle Seen und Meere, die Zuflüsse haben.

(select sea as "Gewässer"from river

where sea is not null)union(select lake as "Gewässer"

from riverwhere lake is not null)

order by 1

Joins. Bisher kamen die angezeigten Ergebnisse stets aus einer Tabelle. Zwar kann mit-hilfe von Subqueries auf Daten mehrerer Tabellen zugegriffen werden, Anfragen wie:“Gib alle Flüsse mit ihren Längen und den Namen der durchflossenen Ländern aus”

konnten bisher aber nicht beantwortet werden, da hierbei die benötigten anzuzeigendenDaten aus verschiedenen Tabellen stammen.Mit Hilfe von Joins können Daten aus verschiedenen Tabellen miteinander in Bezuggesetzt und zusammen ausgegeben werden.

Betrachten wir noch einmal die Daten aus den Tabellenriver , geo_river undcountry . Die Tabelleriver enthält die Flussdaten, während die TabellecountryDaten zu den einzelnen Ländern bereithält. Die beiden Tabellen haben keinerlei Bezugzueinander.

Der Bezug kann aber über die Tabellegeo_river hergestellt werden. Die Spaltegeo_river.riverist ein Fremdschlüssel auf den Primärschlüssel der Tabelleriver (At-tribut name), während das Attributgeo_river.countryein Fremdschlüssel auf den Pri-märschlüssel der Tabellecountry (Attribut code) darstellt. Abbildung 1.4 verdeutlichtdiesen Zusammenhang graphisch. Will man beispielsweise wissen, durch welche Länderdie einzelnen Flüsse fließen, so muss man die beiden Fremdschlüsselbeziehung der Ta-belle geo_river verfolgen und dabei die Schlüssel-/Fremdschlüsselattribute jeweilsgleichsetzen. Konkret muss im obigen Beispiel gelten:

16 Oracle, PostgreSQLnicht beimMS SQL-ServerundMySQL17 nicht beim SQL-Server und MySQL18 nur DB219 Numerische Datentypen sind untereinander kompatibel und alle zeichenkettenbasierten Daten-typen sind ebenfalls untereinander kompatibel

18 1 SQL

river.name = geo_river.river (Linien links in Abbildung 1.4)und

geo_river.country = country.code (Linien rechts in Abbildung 1.4)

Abb. 1.4: Beziehung zwischen Flüssen und Ländern

Die im SQL-89 Standard festgelegte Syntax für einen Join (Theta -Notation) , lässt sichdann auch relativ einfach aus obiger Überlegung ableiten, indem in derfrom -Klauseleinfach alle Tabellen aufgeführt werden, die für die Anfrage relevant sind und in derwhere -Klausel die Bedingungen, welche zwischen den Schlüsseln-/Fremdschlüsselngelten müssen, formuliert werden.So lautet die Anfrage, die für alle Flüsse über 2000 Kilometer Länge, den Namen, dieLänge, sowie die Namen der durchflossenen Länder ausgeben soll, wie folgt:

select distinct river.name as Fluss,river.length as Länge,country.name as Land

from country, river, geo_riverwhere river.name = geo_river.river

and geo_river.country=country.codeand river.length > 2000

order by 2 desc

Fluss Länge Land

Amazonas 6518 Brazil

Amazonas 6518 Colombia

Amazonas 6518 Peru

Jangtse-Kiang 6100 China

Parana 4700 Argentina

. . . . . . . . .

49 Datensätze

distinct muss in diesem Fall benutzt werden, da in der Tabellegeo_river dieBeziehungen zu den einzelnen Provinzen angegeben sind, hier aber nur die Länder in-teressieren.Ein Nachteil der Join-Syntax im SQL-89 Standard ist, dass man auf den ersten Blicknicht erkennen kann, was Schlüssel-/Fremdschlüsselvergleiche sind und wobei es sichum echte Bedingungen (fluss.laenge > 2000 ) handelt.


Aus diesem Grund wurde im SQL-2 Standard von 1992 eine etwas andere Notation fürJoins vorgeschlagen, die so genannteANSI-Notation.

Neu bei der ANSI-Notation ist diejoin -Klausel. Die Join Klausel tritt nach einerfrom - oder join -Klausel auf20, erwartet einen Tabellennamen und erlaubt anschlie-ßend (nach dem Schlüsselworton) die Formulierung einer oder mehrerer Schlüssel-/Fremdschlüsselbedingungen für die zuvor angegebenen Tabellen. So lautet etwa diezuvor gestellte Anfrage in ANSI-Notation:

select distinct river.name as Fluss,river.length as Länge,country.name as Land

from countryjoin geo_river

on country.code=geo_river.countryjoin river

on geo_river.river = river.namewhere river.length > 2000order by 2 desc

Fluss Länge Land

Amazonas 6518 Brazil

Amazonas 6518 Colombia

Amazonas 6518 Peru

Jangtse-Kiang 6100 China

Parana 4700 Argentina

. . . . . . . . .

49 Datensätze

Bei dieser Notation wird die Trennung zwischen der eigentlichen Bedingung und denSchlüssel-/Fremdschlüsselbedingungen im Anschluss an die beidenjoin -Klauseln deut-lich.Neben dem klassischen Join mit Schlüssel-/Fremdschlüsselbedingung unterstützen dieDatenbanken noch eine Reihe weitere Arten von Joins, die im Folgenden vorgestelltwerden sollen.• Cross Join (Kartesisches Produkt): Jeder Datensatz der linken Tabelle (A) wird mit

jedem Datensatz der rechten Tabelle (B) kombiniert. Das Ergebnis ist eine Ergeb-nistabelle mitn-Zeilen21 und m-Spalten22. Der Cross-Join kommt in seiner Rein-form relativ selten vor Er entspricht dem klassischen Join ohne Formulierung vonSchlüssel-/Fremdschlüsselbedingungen. Die folgende Abbildung zeigt exemplarischdie Konstruktion des kartesischen Produktes aus zwei Tabellen.

Abb. 1.5: Kartesisches Produkt von 2 Tabellen

20 Die join -Klausel kann mehrfach auftreten, die erstejoin -Klausel folgt jedoch immer derfrom -Klausel.21 n = Anzahl_Datenstze(A)∗Anzahl_Datenstze(B)22 m= Anzahl_Attribute(A)+Anzahl_Attribute(B)

20 1 SQL

• Left Outer Join : Wie ein normaler Join, bei dem zusätzlich aber auch noch alle dieDatensätze aus der linken23 Tabelle ausgegeben werden24, für die es auf der rechtenSeite keine passenden Datensätze gibt. Die Spalten aus der rechten Tabelle enthal-ten dann im ErgebnisNULL Werte. Ein Beispiel für einen Outer-Join ist folgendeAnfrage:“Gib alle Länder zusammen mit ihren Flüssen aus”

select distinct country.name,geo_river.river

from countryleft join geo_river

on country.code=geo_river.countryorder by country.name, geo_river.river

name river

. . . . . .

Albania White Drin

Algeria

Andorra

Angola Casai

. . . . . .

319 Datensätze

Man kann sehen, dass “Algerien” und “Andorra” im Ergebnis auftauchen, obwohl fürsie keine Flüsse in der Datenbank hinterlegt sind.

• Right Outer Join : Wie ein normaler Join, bei dem zusätzlich aber auch noch alledie Datensätze aus der rechten Tabelle ausgegeben werden24 für die es auf der linkenSeite keine passenden Datensätze gibt. Die Spalten aus der linken Tabelle enthaltendann im ErgebnisNULLWerte.

• Full Outer Join : Wie ein normaler Join, bei dem zusätzlich alle Datensätze sowohlaus der linken- als auch der rechten Tabelle ausgegeben für die es keine passendenDatensätze gibt. Die Spalten aus der linken bzw. rechten Tabelle enthalten dann imErgebnisNULLWerte.

• Self Join: Wie ein normaler Join, allerdings wird zweimal die selbe Tabelle genutzt.Ein Beispiel dazu findet sich auf Seite 21.

Aliasnamen für Tabellen. Esgibt in SQL die Möglichkeit Aliasnamen für Tabellen zuvergeben. Aliasnamen werden in derfrom - und join -Klausel durch Anhängen desAliasnamen direkt hinter den Tabellennamen vergeben. Anschließend kann im State-ment der Aliasname anstelle des Tabellennamens verwendet werden.

Notation: <tabellenname> <aliasname>

Ein Vorteil liegt darin, dass man im Allgemeinen kurze und/oder aussagekräftige Alias-namen vergeben kann und so zu einer kompakteren und übersichtlicheren Schreibweisebei Benutzung der Punkt-Notation kommt.

Es gibt jedoch auch Situation, in der die Benutzung von Aliasen zwingend notwendigist.Beispielsweise, wenn für alle Städte der USA Namensvetternin anderen Ländern ge-sucht werden sollen. Die benötigten Informationen liegen zwar alle in der Tabellecity .Allerdings ist es nicht so einfach die Anfrage zu formulieren, da diewhere -Klausel eslediglich erlaubt Bedingungen auf Datensatzebene25 zu formulieren. Um zum Ziel zu

23 im select -Statement zuerst genannten Tabelle24 sofern sie eine eventuell zusätzlich angegebenewhere -Klausel erfüllen.25 d.h. es wird zu einem Zeitpunkt genau ein Datensatz untersucht.


kommen muss man die Tabellecity mit sich selber vergleichen, d.h. es muss für je-den Datensatz aus der Tabellecity untersucht werden, ob es einen weiteren Datensatzin der selben Tabelle gibt, der den gleichen Namen (Attributname) aber eine andereLänderkennung (Attributcountry) hat. Das komplette Statement lautet dann folgender-maßen:

select c1.name, c2.countryfrom city c1join city c2

on c1.name=c2.namewhere c1.country <> c2.country

and c1.country=’USA’order by 1,2

name country

Albany AUS

Alexandria ET

Alexandria RO

Birmingham GB

. . . . . .

18 Datensätze

Funktionen. SQL definiert eine Reihe von Funktionen die im Rahmen derDML State-ments eingesetzt werden können. So gibt es numerische, zeichenkettenbasierte Funktio-nen, ebenso wie eine Reihe von Konvertierungs- und Datumsfunktionen. Die Funktionenkönnen in derselect -, where -, order - undhaving -Klausel eingesetzt werden.

Da im Bereich der Funktionen noch etwas Wildwuchs in Bezug auf Standardisierungherrscht, sollen in Tabelle 1.1 lediglich einige exemplarische Funktionen aufgezählt wer-den, ohne jedoch auf deren genaue Syntax-/Aufrufkonventionen einzugehen. Der Lesersei an dieser Stelle auf die Literatur des konkreten Datenbanksystems verwiesen umeinen umfassenden Überblick über die dort implementiertenFunktionen zu erhalten.

Tabelle 1.1:Funktionen in SQL

Kategorie Vertreter

Numerische Funktionen

Trigonometrische Funktionen sin(...), cos(...), tan(...), asin(...), acos(...),

atan(...), . . .

Rundungsfunktionen round(...), ceil(...), floor(...), . . .

Logarithmische- und Exponentialfunktionen ln(...), log(...), exp(...), . . .

Potenz- und Wurzelfunktionen sqrt(...), power(...), pow(...), . . .

sonstige numerische Funktionen sign(...), abs(...), . . .

Zeichenketten basierte Funktionen

ASCII Funtionen ascii(...), ord(...), char(...), . . .

Groß-/Kleinschreibung upper(...), lower(...), initcap(...), . . .

Leerzeichen am Beginn/Ende der Zeichenkette entfernentrim(...), ltrim(...), rtrim(...), . . .

Zeichenketten durchsuchen/-ersetzen substr(...), instr(...), position(...),

replace(...), patindex(...), translate(...), . . .

sonstige Zeichenkettenfunktion concat(...), encrypt(...), format(...),

length(...), left(...), . . .

22 1 SQL

Kategorie Vertreter

Datumsfunktionen

Datumsinformationen: day(...), month(...), year(...), curdate(...), sysdate(), get_date(), . . .

Konvertierungsfunktionen

Zeichenkette -> Datum/Zeit to_date(...), extract(...), . . .

Zeichenkette -> Zahl to_number(...), extract(...), . . .

Unterabfragen/geschachtelte Ausdrücke.Auf Seite 3 wurde beschrieben, dass dasErgebnis einer relationalen Operationen stets wieder eineRelation ist, was insbesondereauch die Formulierung geschachtelter Anfragen erlaubt. Übertragen auf SQL bedeutetdies, dass in derfrom -Klausel auch einselect -Statement stehen kann. Einschrän-kend muss jedoch gesagt werden, dass an dieser Stelle keine korrelierten Abfragen (sie-he Seite 15) erlaubt sind, d.h. dasselect -Statement in derfrom -Klausel darf keineAttribute aus der äußeren Query verwenden.Die Anfrage im folgenden Beispiel gibt alle Länder aus, deren Anteil der Stadtbevöl-kerung mindestens 50% der Gesamtbevölkerung beträgt. Dazuwird ein Join zwischender Tabellecountry und dem in Klammer stehendenselect -Statement (Tabellena-liasu) formuliert.

name ratio

Liechtenstein 0.89

Singapore 0.75

Australia 0.67

Qatar 0.61

South Korea 0.59

. . . . . .

8 Datensätze

Das in derjoin -Klausel stehendeselect -Statement liefert pro Land (Spaltecoun-try) die berechnete Summe aller Einwohner der Städte (Spaltenaliaspopulation) zurück.Dieses Ergebnis wird dann mittels des Joins mit der Tabellecountry um den aus-geschriebenen Landesnamen (Attributcountry.name) ergänzt und nur die Länder aus-gegeben, deren Anteil der Stadtbevölkerung (u.population) an der Gesamtbevölkerung(Spaltecountry.population) bei über 50% liegt. Diese Art von Anfrage wird auch alsInstant View26 bezeichnet.Weiterhin ist es auch möglich, dass einselect -Statement in derselect -, order -oderwhere -Klausel auftritt. Im Unterschied zum vorherigen Fall sindhierbei auch kor-relierte Subqueries erlaubt, allerdings muss das Statement genau einen Wert zurücklie-fern. So kann die letzte Anfrage nach“allen Ländern mit mehr als 50% Stadtbevölke-rung” auch folgendermaßen formuliert werden:

26 Ein View ist eine virtuelle, auf einer Anfrage basierenden Tabelle und wird auf Seite 29 vorge-stellt.


name ratio

Liechtenstein 0.89

Singapore 0.75

Australia 0.67

Qatar 0.61

South Korea 0.59

United Kingdom 0.56

Antigua and Barbuda 0.55

Bahamas 0.54

8 Datensätze

Das korrelierte Subquery in derselect -Klausel summiert dabei alle Einwohner in denStädten des aktuellen Landes (c.code) auf. Zur Formulierung der Bedingung, dass nurdie Länder ausgegeben werden sollen, deren Anteil bei über 50% liegt, wird das in derselect -Klausel stehendeselect -Statement in derwhere -Klausel wiederholt27.

Soweit zu den verschiedenen, auf den Operationen der Relationenalgebra basierenden,Varianten desselect -Statement. In den folgenden drei Abschnitten sollen jetzt, eben-falls auf Basis derMondial-Datenbank, dieinsert -, update - unddelete -Statementsvorgestellt werden.

1.6.3 Insert-StatementDas insert -Statement erlaubt das Einfügen von Tupeln in eine Tabelle.Es stehenzwei Varianten zur Auswahl:

insert into <tabellenname> (<attributliste>)values (<werteliste>)

Bei dieser ersten Variante wird genau ein Datensatz in die angegebene Tabelle eingetra-gen.Die Attributliste beinhaltet dabei Attribute aus der angegebenen Tabelle. Es ist möglichweniger Attribute anzugeben, als für die Tabelle definiert worden sind28. Wichtig isthierbei, dass die Anzahl der hinter derinsert -Klausel stehenden Attribute identisch istmit der Anzahl der Werte in dervalues -Klausel und die Datentypen übereinsteimmen.Das folgende Beispiel zeigt den Eintrag der Stadt Karlsruhein die Tabellecity .

insert into city (name, country, province, population)values (’Karlsruhe’, ’D’, ’Baden Württemberg’, 277011)

Es ist jedoch auch möglich, mehrere Datensätze mittels einem insert -Statement ein-zutragen. Dazu wird dasinsert -Statement mit einemselect -Statement kombiniert.Die sich im select -Statement qualifizierenden Datensätze werden dann in die im

27 das geschachtelteselect -Statement wird aber deshalb nicht mehrfach ausgeführt, sondernder Optimierer des Datenbanksystems erkennt, dass die Anfrage in derselect -Klausel undwhere -Klausel identisch sind und führt sie deshalb nur einmal aus28 Es können alle die Attribute weggelassen werden, die bei derDefinition der Tabelle (sieheSeite 27) nicht alsNOT NULLdefiniert wurden.

24 1 SQL

insert -Statement angegebene Tabelle eingetragen. Hierbei müssen die in derinsert -Klausel angegebenen Attribute in Anzahl und Datentyp mit den Attributen desselect -Statement übereinstimmen.

insert into <tabellenname> (<attributliste>)(<select-Anweisung>)

Das folgende Beispiel trägt alle Datensätze aus der Tabelleterra_stadt 29 in dieTabellecity ein.

insert into city (name, country, province,population, longitude, latitude)

(select name, l_id, lt_id, einwohner, laenge, breitefrom terra_stadt)

1.6.4 Update-StatementDasupdate -Statement dient dazu, Datensätze die sich bereits in einerTabelle befin-den zu modifizieren. Dabei können ein oder mehrere Datensätze auf einmal modifiziertwerden.Die Syntax ist wie folgt:

update <tabellenname>set <attribut1>=<wert1>

[,<attribut2>=<wert2>]...

where <bedingungen>

Die Bedingungen entsprechen denen derwhere -Klausel desselect -Statement.

So setzt das folgende Statement die Koordinaten der Stadt Karlsruhe auf (8.4, 49.0):

update cityset longitude=8.4,

latitude=49.0where country=’D’

and province=’Baden Württemberg’and name=’Karlsruhe’

Neben Literalen können auch Berechnungen oder korrelierteSubqueries (die genaueinen Wert zurückliefern) eingesetzt werden. So erhöht dasfolgende Beispiel die Ein-wohnerzahl aller italienischen Städte um 5%:

update cityset population = round(population * 1.05)

where country=’I’

29 Die Terra-Datenbank[DR90] wurde amInstitut für Programmstrukturen und Datenorganisa-tion der Universität Karlsruhe entwickelt und dort im Datenbankpraktikum eingesetzt. DieTerra-Datenbankdiente derMondial-Datenbankals Vorbild und z.T auch als Datenquelle [Mon99].

1.7 Data Definition Language 25

Das letzte Beispiel nutzt eine korrelierte Subquery um aus der Gesamtfläche der Län-der pro Kontinent, die Gesamtfläche der einzelnen Kontinente zu berechnen und in dieTabellecontinent einzutragen30.

1.6.5 Delete-StatementDasdelete -Statement ist für das Löschen einer oder mehrerer Datensätze einer Ta-belle verantwortlich.

Die allgemeine Syntax ist wie folgt:

deletefrom <tabellenname>

where <bedingungen>

Die Bedingungen entsprechen denen derwhere -Klausel desselect -Statement.So lautet der Befehl um alle Städte zu löschen, für die keine vollständigen Koordinaten-informationen in der Tabellecity abgelegt sind:

deletefrom city

where longitude is nullor latitude is null

1.7 Data Definition Language

Im Gegensatz zurDML, bei der die Syntax größtenteils über die Herstellergrenzen hin-weg identisch ist, ist dies bei der DDL nicht ganz der Fall. Sobesitzen die Produkteder einzelnen Hersteller zumeist unterschiedliche Bezeichnungen für die zur Verfügungstehenden Datentypen und ihre Syntax (selten auch die Semantik) weicht an einzelnenStellen voneinander ab.Da auf Platzgründen nicht auf die verschiedenen konkreten Implementierungen einge-gangen werden kann, wird hier stellvertretend dieDDL der Oracle-Datenbanken vorge-stellt. Die dabei vermittelten Konzepte sind aber auf die anderen relationalen Systemeübertragbar.

30 Bei der Tabelleencompasses handelt es sich um eine Beziehungstabelle, welche angibt,welches Land mit welchem Prozentsatz zu einem bestimmten Kontinent gehört.

26 1 SQL

Bei der Entwicklung eines konkreten Datenbankschemas ist dann auf die entsprechen-den Manuals des jeweiligen Herstellers bzw. die einschlägigen Fachliteratur zurückzu-greifen31.

1.7.1 TabellenPrimäre Aufgabe derDDL ist es, die Strukturen in welche die Datensätze abgelegt wer-den, vorzugeben. Die Datensätze werden in so genannten Tabellen abgelegt, welche dieSQL-spezifische Umsetzung der Relationen des Relationenmodells darstellen.Eine Tabelle besitzt eine feste Anzahl an Attributen, die alle einen, für diese Tabelleeindeutigen, Bezeichner und Datentyp besitzen.Neben der Aufgabe der reinen Speicherung spielen konsistenzsichernde Maßnahmen zurWahrung der Integrität (siehe auch Abschnitt 1.2.2) der Daten eine entscheidende Rolle.So können bei der Definition der Tabellen so genannteConstraints32 angegeben werden.Diese Constraints formulieren Bedingungen, die von den Datensätzen erfüllt werdenmüssen. Erfüllt ein Datensatz diese Bedingungen nicht so wird er von der Datenbankabgewiesen, bzw. die Ausführung der entsprechendenDML-Operation wird vom Systemabgelehnt.Constraints lassen sich folgenden Kategorien zuordnen:• Primärschlüssel (Primary Key): Definition eines oder mehrere Attribute einer Ta-

belle, deren Wert(e) den Datensatz eindeutig identifiziert. Der Wert des Primärschlüs-sels (oder eines Teils davon) darf niemalsNULLsein.

• Fremdschlüssel (Foreign Key): Definition eines oder mehrere Attribute einer Ta-belle, die auf einen anderen Datensatz (in der gleichen odereiner anderen Tabelle)verweisen. Ist ein Fremdschlüssel definiert, so überwacht das Datenbanksystem, dassder Datensatz auf den verwiesen wird auch existiert. Dies wird alsreferentielle Inte-grität bezeichnet.Soll ein Datensatz gelöscht werden, auf den andere Datensätze mittels Fremdschlüs-sel verweisen, so wird dies vom Datenbanksystem abgelehnt,da sonst die referen-tielle Integrität verletzt würde. Es ist jedoch möglich fürden Fremdschlüsseldaten-satz ein spezielles Verhalten beim Löschen/Ändern des referenzierten Datensatzes zudefinieren. So kann festgelegt werden, dass der verweisendeDatensatz dann eben-falls gelöscht werden soll (on delete cascade ), bzw. dass der Wert des Fremd-schlüssels aufNULLgesetzt werden soll (on delete set null ).

• Eindeutigkeit (unique): Definition eines oder mehrere Attribute einer Tabelle, derenWerte für alle Datensätze unterschiedlich sein müssen. Werden mehrere Attributedefiniert, so müssen die Attributwertkombinationen unterschiedlich sein.

• Nicht Null (not null): Wird eine Attribut alsNOT NULLdefiniert, so muss jederDatensatz für dieses Attribut einen Wert ungleichNULLbesitzen.

• Wertüberprüfung (check): Jedes Attribut hat einen bestimmten Datentyp der diemöglichen Werte vorgibt (Zeichenketten bestimmter Länge,Zahlen mit vorgegebe-ner Stellenzahl/Präzision, Datumstypen, etc.). Mittels des Check-Constraints kann

31 Es soll an dieser Stelle auch nicht verschwiegen werden, dass Kenntnisse des Datenbankdesignseine noch wichtigere Rolle spielen. Ein empfehlenswertes Buch zum Thema Datenbankdesignist [Her03].32 zu deutsch: Beschränkungen


dieser Wertebereich noch weiter eingeschränkt werden, indem beispielsweise mögli-che diskrete Einzelwerte vorgegeben werden oder bei numerischen Typen ein Intervalangegeben wird, in dem sich die möglichen Werte des Attributs befinden müssen.

Im Folgenden sollen jetzt die wichtigsten Statements zum Anlegen/Modifiziern einesDatenbankschemas vorgestellt werden:

Create Table. Dascreate table -Statement dient zum Anlegen einer neuen Tabel-le. Neben der Struktur der Tabelle können auch die Constraints mit angegeben werden.

Syntax:

create table <tabellenname> (<attributname 1> <typ> [<constraints>],...<attributname n> <typ> [<constraints>][,<constraints>]

)

Im Folgenden soll das Anlegen der beiden Tabellencountry undcity derMondial-Datenbank, einschließlich der Definition der Constraints, gezeigt werden33:Hinweis: Bei Oracle nutzt man statt dem Datentypvarchar, den oracle-spezifischenDatentypvarchar2.

create table country (code varchar2(3),name varchar2(32) not null unique,capital varchar2(35),province varchar2(32),area int check (area >= 0),population int check (population >= 0),primary key(code)

);

create table city (name varchar2(35),country varchar2(4),province varchar2(32),population int check (population >= 0),primary key (name, province, country),foreign key (country)

references country(code));

Das erstecreate table -Statement legt die Tabellecountry an. Das Attributcodewird als Primärschlüssel definiert, der durch eine maximal 3Zeichen lange Zeichenketterepräsentiert wird. Der Wert des Attributsnamekann aus maximal 32-Zeichen bestehen,zusätzlich wird gefordert, dass dieses Attribut immer einen Wert haben muss und wei-terhin, dass dieser Wert für alle Datensätze in der Tabelle eindeutig sein muss, d.h. esdarf beispielsweise keine zwei Datensätze mit dem Namen “Neuseeland” geben.

33 Hinweis: Wird mehr als ein Statement angegeben, so sind die einzelnen Statements durch einenStrichpunkt (;) voneinander zu trennen, damit der SQL-Parser erkennen kann wo die einzelnenStatements enden.

28 1 SQL

Die nächsten beiden Attribute (capital, province) geben die Hauptstadt des Landes an.Sie bilden zusammen mit dem Primärschlüssel der Tabelle (code) einen Fremdschlüs-sel auf eine Stadt in der Tabellecity . Da zum Zeitpunkt des Anlegens der Tabellecountry , die Tabellecity noch nicht existiert, kann der Fremdschlüssel an dieserStelle noch nicht formuliert werden, sondern dies wird nachdem Anlegen der Tabellecity durch einalter table -Statement (s.u.) nachgeholt.Die letzten beiden Attribute der Tabelle (area, population) sind numerisch (Integer) undgeben die Fläche des Landes sowie seine Einwohnerzahl an. Als zusätzliche Konsis-tenzbedingung ist hier angegeben, dass sowohl die Fläche als auch die Einwohnerzahlgrößer gleich 0 sein müssen.Das zweitecreate table -Statement zum Anlegen der Tabellecity ist ähnlichaufgebaut. Im Unterschied zur Tabellecountry besteht der Primärschlüssel aber ausdrei Attributen (name, province, country). Das Attributcountryist weiterhin ein Fremd-schlüssel auf die Tabellecountry . Da die Tabelle bereits existiert, kann der entspre-chende Foreign Key Constraint auch sofort formuliert werden.

Alter Table. Nachdem nun die Tabellecity angelegt worden ist, kann der ForeignKey Constraint, für die Hauptstadt eines Landes, gesetzt werden. Dies geschieht durchein alter table -Statement , bei dem sowohl Attribute hinzugefügt und gelöscht,als auch neue Constraints eingerichtet und ebenfalls wieder gelöscht werden können.Das folgende Codefragment zeigt die Hinzunahme von zwei weiteren Attributen ein-schließlich Constraints zur Tabellecity , als auch in einem zweitenalter table -Statement die Formulierung des Fremdschlüssels für die Hauptstadt eines Landes in derTabellecountry . Das drittealter table -Statement ist dafür verantwortlich, dassjedes Land eine andere Hauptstadt hat. Dies wird dadurch realisiert, indem für die dreiAttribute capital, provinceundcodegefordert wird, dass sieuniquesind, d.h. jede At-tributwertkombination nur einmal vorkommen darf. In den beiden letzten Statementswird weiterhin von der Möglichkeit Gebrauch gemacht, dem Constraint einen Namenzu geben. Wird bei der Definition eines Constraints kein Nameangegeben, so wird vomDatenbanksystem ein Name generiert Es empfiehlt sich jedocheinen (aussagekräftigen)Namen für ein Constraint anzugeben, da dieser im Falle der Verletzung einer Regel mitangegeben wird und man so auf den ersten Blick erkennen kann,wo der Fehler liegt.

alter table city add (longitude float check ((longitude >= -180) and

(longitude <= 180)),latitude float check ((latitude >= -90) and

(latitude <= 90)));

alter table country add (constraint fk_country_city_capitalforeign key (capital, province, code)

references city(name, province, country));

alter table country add (constraint uq_country_capitalunique (capital, province, code)

);


Das Löschen von Constraints erfolgt durch Angabe des Namensdes Constraints. DasLöschen des eben angelegten Fremdschlüssels sieht demnachfolgendermaßen aus:

alter table countrydrop constraint fk_country_city_capital

Im Folgenden noch zwei Beispiele für das Löschen einzelner Attribute einer Tabellebzw. das Löschen einer kompletten Tabelle:

alter table countrydrop column area;

drop table city;

Der Versuch eine Tabelle, die durch Fremdschlüssel referenziert wird, zu löschen schei-tert. Entweder man löscht erst das Constraint oder man fügt die Schlüsselwortecascadeconstraints hinten an dasdrop table -Statement an, das vor dem Löschen derTabelle erst die auf sie verweisenden Fremdschlüssel-Constraints löscht.Beispiel:

drop table country cascade constraints

Es gibt bei vielen Datenbanksystemen noch die Möglichkeit,eine Tabelle basierend aufdem Ergebnis einesselect -Statement anzulegen. Die Struktur der Tabelle wird durchdie Attribute desselect -Statement vorgegeben. Zusätzlich werden aber auch noch alleDatensätze, welche durch dasselect -Statement ausgewählt wurden in die neu ange-legte Tabelle eingetragen. Das Statement ist demnach eine Mischung aus einemDDL-und einemDML-Statement. Diese Feature ist speziell bei der Reorganisation bestehen-der Datenbanken äußerst hilfreich.

Notation:

create table <tabellenname>as

<select-statement>

1.7.2 ViewsEin View34 ist eine virtuelle Tabelle. Anders als bei einer Tabelle werden die zugrundeliegenden Daten aber nicht physikalisch gespeichert, sondern ihnen liegt eine Berech-nungsvorschrift in Form einesselect -Statement zugrunde. Views können inDML-Statements genauso wie Tabellen eingesetzt werden. Weiterhin lassen sich Views auchauf Basis von bereits existierenden Views definieren.Die Datensätze der Views repräsentieren immer den aktuellen Zustand in der Datenbank,da sie die Daten zum Zeitpunkt der Benutzung35 des Views aus den zugrunde liegendenTabellen/Views extrahieren.

Views besitzen eine Reihe von interessanten Einsatzgebieten, die im folgenden kurzangesprochen werden sollen [HS00].

34 zu deutsch: Sicht35 und nicht zum Zeitpunkt des Anlegens des Views

30 1 SQL

• Views können inselect -Statements genauso wie Tabellen eingesetzt werden. Da-durch lassen sich oft benötigte und/oder komplexe Anfragenin Form von Views vor-definieren, so dass konkrete Anfragen dann auf dem View aufsetzen können und so-mit die Anfrage entsprechend einfacher zu formulieren ist.

• Views erlauben die Realisierung einer klaren Schnittstelle oberhalb der materialisier-ten Tabellen. Für verschiedene Benutzergruppen lassen sich so verschiedene Sichteneinrichten, die genau auf deren Bedürfnisse zugeschnittensind.

• Durch die Formulierung von Bedingungen innerhalb desselect -Statement, könnenZugriffsbeschränkungen formuliert werden. Einzelne Spalten oder ganze Datensätzesind so beispielsweise für bestimmte Benutzergruppen ausgeblendet bzw. werden nurin aggregierter Form präsentiert. Die Regelung des Zugriffs einzelner Benutzer- oderganzer Benutzergruppen auf Tabellen oder Views erfolgt durch dieDCL und wird inAbschnitt 1.8 vorgestellt.

Das folgende Beispiel zeigt das Löschen und die anschlieSSende Neudefinition desViewsHauptstaedte :

drop view hauptstaedte;

create view hauptstaedteas

select city. *from cityjoin country

on city.name = country.capitaland city.province = country.provinceand city.country = country.code;

Anschließend kann der View dann für beliebige Anfragen benutzt werden, wie z.B. hierbei der Frage nach allen Hauptstädten zwischen dem 20. Grad nördlicher- und dem20. Grad südlicher Breite.

select *from hauptstaedte

where latitude between -20 and 20order by name

Views sind unter bestimmten Bedingungen auch änderbar, d.h. es können Daten neu ein-getragen, modifiziert und auch wieder gelöscht werden. Die Änderungen werden dannan den dem View zugrunde liegenden Tabellen durchgeführt.Dies ist jedoch nicht immer möglich. So ist leicht einzusehen, dass bei einem View dereine Aggregatsfunktion wieavg(...) enthält, diese Spalte nicht änderbar sein kann,da die Änderung auf dien-Eingabedatensätze der Aggregatsfunktion propagiert werdenmüßte. Andere Operationen die i.a. Probleme bereiten sind die Duplikatseliminierungmit distinct , Mengenoperatoren wieunion , intersect , etc., Joins, Subqueriesin derselect -Klausel und der Einsatz dergroup by -Klausel.Ist ein View änderbar, so kann durch die Angabe “with check option ” am Endeder View-Definition noch festgelegt werden, dass nur solcheÄnderungen (neueintragen,modifizieren) vorgenommen werden dürfen, die anschließendim View auch sichtbarsind, sprich, der Datensatz muss die Bedingungen derwhere -Klausel erfüllen.


1.7.3 IndexeEin Index ist ein Datenbankobjekt, das den Zugriff auf die Daten beschleunigen soll.So werden vom Datenbanksystem aus bereits Indexe auf Primär-, Fremdschlüssel- undUnique-Attribute gesetzt.Das Prinzip eines Index beruht darauf, dass bei der Suche nach einem oder mehrerenDatensätzen nicht die gesamte Tabelle sequentiell in den Hauptspeicher geladen werdenmuss, was insbesondere bei großen Datenmengen eine sehr zeitaufwendige Operationist, sondern es eine geeignete Datenstruktur gibt, die nachwenigen Schritten den Ortauf der Festplatte bestimmen kann, auf der sich der/die gesuchten Datensätze befinden.Durch das Anlegen von Indizes kann der Lesezugriff zwar beschleunigt werden, derschreibende Zugriff wird jedoch dabei langsamer, da nicht nur die eigentlichen Datengeändert werden müssen, sondern eben auch die zugehörigen Zugriffsstrukturen.

In SQL wird ein Index auf ein oder mehrere Attribute einer Tabelle gelegt. Die Syntaxzum Anlegen eines Index lautet wie folgt:

create index <name>on <tabelle>(<attribut 1>, ..., <attribut n>)

Das Gebiet der Anfrageoptimierung in Datenbanksystemen ist äußerst komplex und fülltviele Bücher. So spielt insbesondere auch die Kenntnis des internen Aufbaus und derFunktionsweise eines konkreten Datenbanksystems eine entscheidende Rolle um nicht-triviale Optimierungen vorzunehmen. Der interessierte Leser sei hier auf [SB02] und[GUW99] verwiesen.

1.7.4 Automatisch generierte PrimärschlüsselIn derMondial-Datenbankwird der Primärschlüssel jeweils aus einem oder mehrerenAttributen des jeweiligen Objekttyps gebildet. So wird derPrimärschlüssel einer Stadt(city ) aus der Landes ID (country), dem Provinznamen (province) und dem Namen derStadtname) gebildet. Als Beispiel für einen Primärschlüssel bestehend aus nur einemAttribut, sei die TabelleFluss angeführt, deren Primärschlüssel aus dem Name desFlusses (Attributname) besteht.

Das hat eine Reihe von Konsequenzen:• Es ist nicht möglich einen Fluss einzugeben der den selben Namen hat wie ein bereits

vorhandener Fluss36. Dieses Problem kann dadurch gelöst werden, indem weitereAttribute (z.B. die Koordinaten der Quelle) mit in den Primärschlüssel aufgenommenwerden.

• Ein Fremdschlüssel auf einen Datensatz dessen Primärschlüssel ausn-Attributen be-steht, besteht ebenfalls ausn-Attributen.

Um diese Probleme zu vermeiden führt man oft künstliche Schlüsselattribute ein, sogenannteSurrogate Keys. Ein Schlüsselattribut hat in der Semantik der Anwendung kei-ne Bedeutung und dient lediglich dazu eindeutige Schlüsselattributwerte bereitzustellenund damit auch Fremdschlüssel, bestehend aus lediglich einem Attribut, zu ermöglichen.

36 So gibt es beispielsweise sowohl in Rheinland Pfalz als auchin Bayern, Hessen und Sachseneinen “Schwarzbach”.

32 1 SQL

Datenbanksysteme unterstützen den Entwickler bei der Generierung eindeutiger Attri-butwerte. So gibt es inOracleso genannteSequenzen, die fortlaufend neue numerischeWerte erzeugen, die dann den betreffenden Spalten zugewiesen werden können.Beispiel:

create sequence seq_mountain;

create table mountain (id integer,name varchar2(32),height float,primary key (id)

);

insert into mountain (id, name, height)values(seq_mountain.nextval, ’Feldberg’,1493);

Jeder Aufruf vonseq_mountain.nextval liefert einen um eins erhöhten Wert zu-rück, der dann mittels desinsert -Statement eingetragen wird. Der zuletzt zurückge-lieferte Wert kann mittelsseq_mountain.currval erfragt werden.Ein anderes verbreitetes Konzept ist, den Primärschlüsselbei der Definition als künst-lichen Primärschlüssel zu charakterisieren, der vom Datenbanksystem automatisch miteindeutigen Werten versorgt wird. Beispielsweise kann manin MySQLdem (numeri-schen) Primärschlüssel einer Tabelle die Eigenschaftauto_incrementzuweisen. In die-sem Fall wird beim Anlegen eines neuen Datensatzes für den Primärschlüssel kein Wertangegeben, sondern dieser wird vom Datenbanksystem gesetzt.Beispiel:

create table mountain (id int auto_increment,name varchar(32),height float,primary key (id)

);

insert into mountain (name, height)values(’Feldberg’,1493);

Der zuletzt von der Datenbank vergebene Wert kann mittels

select last_insert_id()

erfragt werden.

1.8 Data Control Language

Datenbanken sind Mehrbenutzersysteme. Dies bedeutet zum einen, dass das System ent-sprechende Konzepte für eine Benutzer- und Rollenverwaltung bereitstellen muss, undzum zweiten auch die Koordination des Zugriffes mehrerer Benutzer auf den Datenbe-stand.

1.8 Data Control Language 33

Die Koordination des Zugriffs wird in Datenbanken mittels Transaktionen gelöst. Hier-bei handelt es sich um ununterbrechbar ablaufende Operation, die die Datenbank durcheine Reihe von Einzelschritten von einem konsistenten Zustand in einen neuen, nichtnotwendigerweise verschiedenen, konsistenten Zustand überführen.Während auf Transaktionen hier nicht weiter eingegangen wird37, sollen im Folgendendie grundlegenden Konzepte der Benutzer- und Zugriffsverwaltung vorgestellt werden.

1.8.1 Benutzer und RollenEin Datenbankmanagementsystem verwaltet im allgemeinenn-Benutzer undm-Daten-banken. In den meisten Systemen könnenRollendefiniert werden, mittels denen Benut-zer mit gleichem Profil zusammenfasst werden können. Der Vorteil hierbei ist, dass diespeziellen Rechte nicht jedem Benutzer direkt zugeteilt werden müssen, sondern zen-tral der Rolle zugeordnet werden können und dann die Benutzer diese Rolle zugewiesenbekommen.

Ein Beispiel für das Anlegen einer neuen Rolle und eines neuen Benutzers inOraclelautet wie folgt:

create role student;

create user student_123 identified by sm03r3br03ddefault tablespace usersquota 20 M on users;

Der Accountstudent_123 hat das Passwortsm03r3br03d und kann maximal 20Megabyte im Tablespace38 users an Informationen ablegen. Allerdings darf er sich zudiesem Zeitpunkt noch nicht mal am Server anmelden.Als nächstes wird deshalb der Rollestudent das Recht eingeräumt sich am Serveranzumelden (create session ) und auch im eigenen Schema Tabellen anzulegen(create table ).

grant create session,create table

to student

Diese Rechte werden alsSystemprivilegienbezeichnet. Oracle39 verfügt über ca. 140verschiedenen Systemprivilegien, die eine feingranulareRechtevergabe erlauben.Die nächste Anweisung im folgenden Codeblock weist dem Account student_123die Rollestudent zu, der damit automatisch alle Rechte der Rollestudent erwirbt.Es ist jedoch auch möglich einem User direkt bestimmte Rechte zuzuordnen.

grant studentto student_123

37 eine detaillierte Vorstellung verschiedener Transaktionskonzepte findet sich in [GR92].38 Bei Oraclewerden alle Daten innerhalb vonTablespacesabgelegt. Ein Tablespace besteht auseiner oder mehreren Dateien auf Betriebssystemebene. BeimAnlegen eines Tablespaces wird derPlatz auf der Festplatte durch die Dateien bereits fest allokiert, so dass er dann ausschließlich demDatenbanksystem zur Verfügung steht.39 Version 9i

34 1 SQL

Um Benutzer und Rollen anzulegen, muss man entweder die Rolle dbabesitzen, oderdie entsprechenden Systemprivilegien (create role , create user ) besitzen.

Alle Tabellen und Datensätze die ein User anlegt, befinden sich in seinem privaten Sche-ma, auf das andere Benutzer keinen Zugriff haben. Der User ist aber in der Lage, ande-ren Benutzern oder auch Rollen bestimmte Rechte auf seinen Daten einzuräumen. DieseRechte werden alsObjektprivilegienbezeichnet.

So kann beispielsweise ein Benutzer der RolleStudent das Recht geben, die Daten inseiner Tabellecity zu lesen (Objektprivilegselect ) und neue Datensätze einzufügen(Objektprivileginsert ).

grant select, inserton cityto student

Es ist auch jederzeit mittels desrevoke Befehls möglich, Rechte wieder zu entfernen.Um beispielsweise der Rollestudent das Recht Datensätze einzufügen wieder zunehmen, schreibt man einfach:

revoke inserton city

from student

Analog können auch die Systemprivilegien widerrufen werden.Nachdem einem anderen User oder einer Rolle das Recht zugebilligt wurde auf die Da-ten einer Tabelle in einem fremden Schema zuzugreifen, bleibt nur noch die Frage zuklären, wie der Zugriff auf die fremden Daten erfolgt.Dies geschieht, indem dem Tabellennamen, der Name des Usersdem die Tabelle ge-hört, vorangestellt wird. So erfolgt der lesende Zugriff auf die Tabellecity des Usersmondialso:

select *from mondial.city

where einwohner < 20000

1.9 Übungsaufgaben1. Welche Städte liegen auf dem Äquator?2. Welche Organisationen haben ihren Sitz in Wien?3. Welche Hauptstädte sind zugleich Sitz von Organisationen?4. Welche Organisationen haben ihren Sitz in Asien (Ausgabenach Name sortiert)?5. Wie heißt die größte Insel?6. Gib alle Länder ohne Berge aus.7. Gib die Namen aller Hauptstädte, sortiert nach Name aus.8. Gib die Namen und Einwohnerzahl aller Hauptstäde, absteigend sortiert nach Ein-

wohnerzahl aus.9. Gib die Stadt mit den meisten Einwohnern aus.10.Welcher Fluss fließt durch die meisten Länder?

1.10 Frei verfügbare Software 35

11.Wie lang ist die Außengrenze von Deutschland?12.Wieviele Inseln gehören zu den kleinen Antillen?13.Welche Inselgruppen bestehen aus sechs Inseln?14.Welches ist die Inselgruppe mit den meisten Inseln?15.Wieviele Protestanten gibt es auf der Welt?16.Gib die Religionen zusammen mit ihren Anhängern, absteigend sortiert nach Anzahl

Anhänger aus17.Welche Religion hat die geringste Anzahl Anhänger?18.Wie heißt der höchste Berg Amerikas?19.Ordne jedem See die am ehesten von der Größe her passende Insel zu und gib die

Paare aus.20.Transformiere dieMondial-Datenbank40von natürlichen Primärschlüsseln nach künst-

lichen Primärschlüsseln.

1.10 Frei verfügbare Software

Es gibt eine Reihe von frei verfügbaren relationalen Datenbanken, Entwicklungswerk-zeuge und Frontends dazu. Vor allemPostgreSQLundMySQLhaben inzwischen einegroße Verbreitung gefunden und sind mit kommerziellen Systemen durchaus vergleich-bar. Da aber gerade im Umfeld der Frontends und Werkzeuge immer neue Produkteerscheinen und alte nicht weiter unterstützt werden, wird unter [Smi05] eine aktuell ge-haltene Übersicht gegeben.

1.11 Bibliografie und Webliografie

Allgemeine Literatur

A. Heuer, G. Saake: Datenbanken: Konzepte und Sprachen; mitp-Verlag; 2. Auflage, 2000.Kevin Kline, Daniel Kline: SQL in a Nutshell; O’Reilly, 2001.A. Silberschatz, H. F. Korth, S. Sudarshan: Database Systems Concepts; McGraw-Hill, 4. Auflage,

2001

Spezielle Literatur

[Cod70] E.F. Codd: A Relational Model of Data for Large Shared Data Banks; CACM 13, No. 6,June 1970.

[Dat05] C.J. Date: Database In Depth - Relational Theory forPractioners. O’Reilly, 2005.

[DR90] M. Dürr und K. Radermacher: Einsatz von Datenbanksystemen. Berlin, Heidelberg:Springer-Verlag, 1990.

40 am Beispiel der Tabellencountry undcity

36 1 SQL

[Fri00] Jeffrey Friedl: Mastering Regular Expressions; O’Reilly, 2002.

[Gen04] J. Gennick: SQL kurz & gut. O’Reilly, 2004.

[GR92] J. Gray, A. Reuter: Transaction Processing : Concepts and Techniques; Morgan Kauf-mann, 1992

[GUW99] H. Garcia-Molina, J.D. Ullman, J.D. Widom: Database System Implementation, Pren-tice Hall, 1999

[Her03] M. J. Hernandez: Database Design for Mere Mortals, Addison-Wesley, 2003.

[HS00] A. Heuer, G. Saake: Datenbanken: Konzepte und Sprachen; mitp-Verlag; 2. Auflage,2000.

[KK01] Kevin Kline, Daniel Kline: SQL in a Nutshell; O’Reilly, 2001.

[Mil03] Cary Millsap: Optimizing Oracle Performance; O’Reilly, 2003

[SB02] D. Shasha, P. Bonnet: Database Tuning: Principles, Experiments, and TroubleshootingTechniques; Morgan Kaufmann, 2002

[SKS01] A. Silberschatz, H. F. Korth, S. Sudarshan: Database Systems Concepts; McGraw-Hill,4. Auflage, 2001

[Mon99] W. May: Information Extraction and Integration with FLORID: The MONDIAL CaseStudy, Universität Freiburg, Institut für Informatik, 1999http://www.dbis.informatik.uni-goettingen.de/Mondial/

[Smi05] A. Schmidt: SQL, http://www.smiffy.de/sql/, 2005

[Tür03] C. Türker: SQL:1999 & SQL:2003 – Objektrelationales SQL, SQLJ & SQL/XML.dpunkt.verlag, 2003.

1 Structured Query Language - SQL · sowie die Open Source Produkte MySQL und PostgreSQL. ... DDL)...

Documents

Transcript of 1 Structured Query Language - SQL · sowie die Open Source Produkte MySQL und PostgreSQL. ... DDL)...