1 Structured Query Language - SQL
Werbung
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 Relationenkalkül. Das Modell besticht sowohl durch seine Einfachheit, als auch durch seine Exaktheit. Codd beschreibt in seinem Modell sowohl die strukturellen Eigenschaften, Integritätsaspekte als auch eine Reihe von Operationen auf den Daten. Ein Großteil der sich heute produktiv im Einsatz befindlichen Datenbanksysteme basieren 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 Datenbanksysteme dar. SQL ist deskriptiv, d.h. der Anwender formuliert welche Informationen er haben mö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 Datenbanken eingegangen wird, werden im Folgenden einige elementare Kenntnisse des Relationenmodells vermittelt, die für das spätere Verständnis von SQL hilfreich sind. Eine ausführliche und detailierte Einführung in das Modell findet sich in [SKS01]. Die Einführung soll anhand der beiden in Abbildung 1.1 dargestellten Relationen country und city erfolgen. Abb. 1.1: Struktur relationaler Datenbanken 2 1 SQL 1.2.1 Struktur Zentrale Komponente in Codds Modell ist die Relation . Eine Relation besteht aus einer Anzahl von Tupeln, welche die Datensätze repräsentieren. Die Struktur einer Relation (und somit auch der in ihr enthaltenen Tupel) wird durch das Relationenschema vorgegeben. Ein Relationenschema besitzt einen eindeutigen Namen und wird durch eine Menge von Attributen beschrieben, die jeweils einen bestimmten Wertebereich (Domäne) besitzen. Es beschreibt somit eine Struktur, vergleichbar einem Struct in C/C++ oder einem Record in Pascal. Dabei ist aber zu beachten, dass die Werte der Attribute atomar sein müssen, d.h. keine mengenwertigen oder strukturierten Daten repräsentieren dürfen. Die Gesamtheit aller Relationenschemata bildet dann das Datenbankschemata. Anschaulich wird eine Relation als Tabelle dargestellt. Die Attribute des Relationenschemas werden zu den Spaltenbezeichnern, die Zeilen der Tabelle repräsentieren die Tupel und die Werte in den Tabellenfeldern entsprechen denen der Wertebereiche ihrer Attribute. 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 mathematische Menge 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ät Das Relationenmodell kennt weiterhin den Begriff des Primärschlüssels. Dabei handelt es sich um ein oder mehrere Attribute eines Relationenschemas, so dass es in dieser Relation keine zwei Tupel geben kann, die für dieses Attribut oder Attributkombination identische Attributwerte besitzen. Mittels des Primärschlüssels ist es somit möglich, einzelne Tupel zu identifizieren. Dieser Sachverhalt wird im Relationenmodell dazu genutzt um Beziehungen zwischen Datensätzen herzustellen. In Abbildung 1.1 sind die Attribute, die den Primärschlüssel bilden unterstrichen. Da es in einer Menge von Datensätzen keine Duplikate gibt ist gewährleistet, dass es für jede Relation einen Primärschlüssel gibt. Spätestens durch die Hinzunahme aller Attribute eines Relationenschemas lässt sich ein Primärschlüssel konstruieren. Aufbauend auf dem Primärschlüssel existiert das Konzept des Fremdschlüssels mit dem sich Beziehungen zwischen Datensätzen modellieren lassen. Ein Fremdschlüssel ist ein Attribut oder eine Attributkombination, die auf den Primärschlüssel einer Relation1 verweist. Dabei muss gesichert sein, dass der Wert des Fremdschlüssels immer auf ein existierendes Tupel verweist, d.h. dass der Attributwert oder die Attributwertkombination des Fremdschlüssels identisch mit einem existierenden Primärschlüsselwert ist. Dieser Sachverhalt wir als referentiellen Integrität eines Datenbanksystems bezeichnet. In Abbildung 1.1 sind zwei Fremdschlüsselbeziehungen zu sehen. Zum einen besitzt die Relation country einen Fremdschlüssel, bestehend aus den Attributen code, capital 1 dabei kann es sich um eine beliebige andere- oder aber auch um die selbe Relation handeln. 1.3 Organisation der Sprache 3 und province, welcher die Hauptstadt des Landes referenziert. Beispielhaft ist dies am Datensatz der Schweiz dargestellt. Zum zweiten besitzt die Relation city den Fremdschlüssel country, welcher das Land identifiziert in dem sich die Stadt befindet. Dies ist beispielhaft anhand der Städte Frankreichs angedeutet. Weitere mögliche Integritätsbedingungen sind die Einschränkung des Wertebereichs für einzelne Attribute, das Erzwingen von Einträgen für bestimmte Attribute und das Erzwingen der Eindeutigkeit von Attributwerten innerhalb einer Relation. 1.2.3 Operationen Codd formuliert eine Reihe von Operationen auf den Relationen. Im einzelnen handelt es sich dabei um Restriktion, Projektion, Kartesisches Produkt, Join, Vereinigung, Durchschnitt und Differenz. All diese Operationen besitzen eine oder mehrere Relationen als Eingabe und liefern eine Relation als Ausgabe zurück [Dat05]. Dies bedeutet insbesondere, dass die Ausgabe einer Operation, stets wieder als Eingabe einer anderen Operation dienen kann, was die Formulierung geschachtelter relationaler Ausdrücke erlaubt. In Abschnitt 1.6.2 wird die Umsetzung obiger Operationen auf SQL-Statements beschrieben. 1.3 Organisation der Sprache SQL hat inzwischen eine Reihe von Standardisierungsrunden durchlaufen. Das American National Standards Institute (ANSI) setzte 1986 den ersten Standard (SQL1 oder SQL-86). Weitere Standardisierungen fanden 1989 und 1992 (bekannt als SQL2 bzw. SQL-92) statt, sowie zuletzt die als SQL3 (SQL-99) bekannt gewordene Aktualisierung des Standards im Jahr 1999. SQL ist weiterhin auch von der International Standard Organisation (ISO) anerkannt. Im SQL2 Standard werden drei Anweisungsklassen definiert, die auch die Grundlage fü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 Datenbankanwendung eingesetzt, um auf die Daten in den zuvor mittels der DDL (s.u.) angelegten Tabellen zuzugreifen. • Datendefinitionssprache (Data Definition Language, DDL): Die Sprachelemente in dieser Klasse erlauben das Anlegen und Modifizieren der Datenstrukturen. Typische Vertreter sind die Sprachelemente create table, alter table, etc. Die Befehle dieser Klasse werden hauptsächlich zur Entwicklungszeit einer Datenbankanwendung eingesetzt. Sie erlauben die Definition der Datenstrukturen und Tabellen, auf die dann zur Laufzeit mittels den Befehlen der DML lesend und schreibend zugegriffen wird. 4 • 1 SQL Datensteuerungssprache (Data Control Language, DCL): Die Sprachelemente in dieser Klasse erlauben das Anlegen und Modifizieren von Benutzern und Rollen sowie die Vergabe von Zugriffs- und Systemrechten. Typische Vertreter sind die Befehle create user zum Anlegen von neuen Benutzern sowie grant und revoke mit denen 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 Wertebereich hat. Diese Wertebereiche werden in SQL durch Datentypen festgelegt. Unabhängig vom konkreten Datenbanksystem2 lassen sich die Datentypen in folgende Kategorien untergliedern: • Zeichenketten: Erlauben das Speichern von beliebigen Kombinationen von Zeichen. Es wird zwischen variabel langen Datentypen (varchar(<size>), CLOB3 ) und Datentypen mit fester Länge (char(<size>)) unterschieden. Bei Datentypen mit fester Länge wird die Zeichenkette am Ende mit Leerzeichen aufgefüllt, bei Datentypen variabler Länge wird nach dem letzten Zeichen abgeschnitten. Zum Teil kann eine Obergrenze, wie viel Zeichen maximal gespeichert werden dürfen, angegeben werden, teils sind die Grenzen fest vorgegeben. Weiterhin gibt es Varianten obiger Typen die mit verschiedenen nationalen Zeichensätzen umgehen können (z.B. nvarchar<size>). • Numerisch: Es können sowohl ganze Zahlen als auch Fließkommazahlen gespeichert werden. Die Typen unterscheiden sich in ihrer Genauigkeit und Stellenzahl. Beim Datentyp float kann bei der Definition beispielsweise die Anzahl der Stellen vor und nach dem Komma mit angegeben werden. Vertreter numerischer Datentypen sind integer, smallint, numeric, float, real und double precision. • Zeitlich: Es gibt Datentypen zum Speichern von Datum (Date) und Uhrzeit (Time), mit und ohne Zeitzone. Weiterhin gibt es den Datentyp Interval, der eine Zeitdauer aufnimmt. • Binär: Der Datentyp BLOB4 speichert beliebige binäre Zeichenketten. Die Größe liegt dabei meist im Gigabyte Bereich. • Boolean: Datentyp zur Aufnahme der Werte TRUE und FALSE. • Bit-String: Datentyp zur Aufnahme binärer Daten, mit fester oder variabler Länge. Es gibt den speziellen Wert NULL für alle Datentypen. Der Wert NULL für ein Attribut sagt aus, dass für dieses Attribut kein Wert bekannt ist. Er ist auf keinen Fall mit dem Wert 0 für numerische Typen oder dem leeren String für Zeichenketten zu verwechseln. Eine Besonderheit ist, dass der Wert NULL von sich selbst verschieden ist, dh. es gilt NULL 6= NULL5 . 2 aus historischen Gründen unterscheiden sich die Datentypen der Hersteller zum Teil. Character Large Object 4 Binary Large Object 5 Dies bedeutet insbesondere auch, dass die Bedingung A = A nur für Attributwerte ungleich NULL wahr ist. 3 1.5 Syntax 5 Wie bereits erwähnt, findet man die hier beschrieben Datentypen nicht unbedingt so in den konkreten Datenbanksystemen wieder, sondern diese weichen von der Namensgebung und/oder der Semantik oft etwas vom Standard ab. Eine Abbildung der im SQL3 Standard vorgeschlagenen Datentypen auf konkreten Datentypen einer Reihe populärer Datenbanksysteme 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 vom System vorgegebenen Datentypen verwendet werden. Auf diesen Punkt soll hier jedoch nicht weiter eingegangen werden, der interessierte Leser sei auf [Tür03] verwiesen. 1.5 Syntax Die Syntax von SQL ist relativ einfach. Die Befehle der DML, DDL und DCL beginnen stets mit einem oder mehreren Schlüsselwörtern, welche den konkreten Befehl spezifizieren. Es können beliebige Einrückungen und Umbrüche innerhalb eines Befehls getätigt werden. Werden mehrere Befehle hintereinander geschrieben, so werden die einzelnen Befehle mit einen Strichpunkt (;) beendet. 1.5.1 Groß-/Kleinschreibung Schlüsselwörter können beliebig groß- oder kleingeschrieben werden. Genauso verhält es sich mit Bezeichnern für Tabellen6, Indexe, Attributen, etc. 1.5.2 Literale Werte für Zeichenketten und Datumsangaben werden in einfache Hochkommas (’), numerische Literale werden ohne Hochkommas geschrieben. Beispiel: insert into city (name, country, province, population) values (’Karlsruhe’, ’D’, ’Baden Württemberg’, 277011) 1.5.3 Kommentare SQL kennt zwei Arten von Kommentaren . Zum einen gibt es die aus vielen anderen Sprachen bekannten Kommentarklammern /*. . . */, die Kommentare über mehrere Zeilen enthalten können. Beispiel: /* : Smiff * Autor * Version : 1.22 */ 6 Bei MySQL spielt die Groß-/Kleinschreibung z.T. eine Rolle, da die einzelnen Tabellen auf Dateien mit dem Namen der Tabelle abgebildet werden. Unterscheidet das zugrunde liegende Betriebssystemen 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. In SQL 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 vorgestellt werden, d.h. der Teil, der sich mit Anfragen und Modifikationen an bestehenden Datenbanken beschäftigt. Die DML stellt die Umsetzung der Operationen der Relationenalgebra (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 der Mondial-Datenbank [Mon99] auf, die unter der angegebenen URL für Trainings- und Lehrzwecke frei verfügbar ist. Bevor nun im Folgenden mit der Einführung in die DML begonnen wird, werden zuerst noch die im Rahmen der Beispiele benutzten Tabellen aus der Mondial-Datenbank vorgestellt. 1.6.1 Mondial Datenbank Die gesamte Mondial-Datenbank umfasst ca. 25 Tabellen, von denen hier aber nur wenige benötigt werden. In Abbildung 1.2 ist ein Ausschnitt des Abhängigkeitsschema der Mondial-Datenbank dargestellt. Die Kästen repräsentieren die Tabellen mit ihren Attributen. Bei unterstrichenen Attributen handelt es sich um Primärschlüssel, grau unterlegte Attribute stellen Fremdschlüssel dar. Abb. 1.2: Auszug aus Abhängigkeitsschema der Mondial-Datenbank 1.6 Data Manipulation Language 7 In der oberen Hälfte der Abbildung sieht man die Tabellen province, country und city und ihre Beziehungen zueinander. Der Primärschlüssel der Tabelle city besteht aus den drei Attributen name, province und country. Teile des Primärschlüssels (province, country) bilden weiterhin einen Fremdschlüssel auf die Provinz, in der sich die Stadt befindet. Dadurch wird eine 1 : nBeziehung7 zwischen einer Provinz und den darin liegenden Städten realisiert. Die übrigen Attribute geben die Einwohnerzahl (population) und die Geo-Koordinaten der Stadt (longitude, latitude) an. Der Primärschlüssel der Tabelle province besteht aus den Attributen name und country. country wiederum ist ein Fremdschlüssel auf das Schlüsselattribut code der Tabelle country und stellt den Bezug zwischen einer Provinz und dem zugehörigen Land her. Tabelle country besitzt neben dem Primärschlüssel code einen dreiteiligen Fremdschlüssel (code, capital, province), der auf den entsprechenden Hauptstadt-Datensatz in der Tabelle city verweist. Hierbei handelt es sich um die Umsetzung einer 1 : 1Beziehung8 zwischen einem Land und der zugehörigen Hauptstadt. In der unteren Hälfte des Abhängigkeitsschemas sieht man die beiden Tabellen river und geo_river. Tabelle river benutzt den Namen des Flusses (Attribut name) als Primärschlüssel. Die nächsten 3 Attribute (river, lake, sea) sind jeweils Fremdschlüssel und geben an, ob der Fluss in einen anderen Fluss (river), einen See (lake) oder in ein Meer (sea) mündet. Die Beziehung zwischen Provinzen und Flüssen wird über die Tabelle geo_river hergestellt, 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 werden in relationalen Datenbanken zur Modellierung von n : m- Beziehungen eingesetzt. 7 Jede Stadt gehört zu genau einer Provinz, umgekehrt kann eine Provinz mehrere Städte haben. Dass eine Stadt maximal Hauptstadt eines Landes sein kann, wird durch die Formulierung eines unique-Constraints (siehe Abschnitt 1.7.1) erreicht. 8 8 1 SQL Während sich 1 : 1- und 1 : n-Beziehungen, wie zuvor gesehen, problemlos durch die Hinzunahme eines Fremdschlüssels9 modellieren lassen, wird bei n : m-Beziehungen eine zusätzliche Tabelle benötigt. Eine Beziehungstabelle besteht aus mindestens zwei Fremdschlüsseln, die auf die in Beziehung stehenden Tabellen verweisen. Konkret besteht die Tabelle geo_river aus einem Fremdschlüssel auf den betreffenden Fluss (Attribut river) und einem Fremdschlüssel auf die Provinz (Attribute province, country) die der Fluss durchfließt. Somit ist es möglich, dass ein Fluss mehrere Provinzen durchfließen kann, umgekehrt kann eine Provinz auch von mehreren Flüssen durchflossen werden. Ein Auszug aus Tabelle geo_river ist in nachfolgender Tabelle dargestellt. Im Folgenden sollen nun anhand obiger Tabellen die DML Befehle vorgestellt werden. Hinweis. Es wird empfohlen, die Beispiele an einem realen System nachzuvollziehen und mit ihnen etwas zu “spielen”. Dazu gibt es auf der Webseite [Smi05] einen web basierten Zugang zu einer Mondial-Datenbank, einschließlich einer Sammlung aller im Folgenden vorgestellten Statements10 . Damit lassen sich die Anfragen sofort am Datenbestand nachvollziehen und variieren, ohne lokal ein Datenbanksystem installieren zu müssen. 1.6.2 Select-Statement Als erstes soll nun im Folgenden das select-Statement eingeführt werden. Es ist für die Extraktion von Informationen aus der Datenbank zuständig. Das einfachste select-Statement ist die Ausgabe aller Daten einer Tabelle. Dazu wird in der select-Klausel mittels dem Wildcardzeichen * signalisiert, dass alle Spalten ausgegeben werden sollen. In der from-Klausel wird die Tabelle angegeben. Das Ergebnis sind dann alle Datensätze, die sich in der betreffenden Tabelle befinden. Name ... River Lake ... ... Mekong Orinoco select * from river Werra Weser Fulda Weser ... South China Sea 4500 Atlantic Ocean 2736 218 Pacific Ocean ... Length 292 Columbia River ... Sea ... ... ... 1952 ... 132 Datensätze Projektion. Sollen nicht alle Spalten einer Tabelle ausgegeben werden, so kann man in der select-Klausel die Attribute festlegen, die ausgegeben werden sollen. 9 der Fremdschlüssel kommt in die Tabelle, deren Datensätze jeweils mit maximal einem anderen Datensatz in Beziehung stehen. 10 nur select-Statement, Datenmanipulationen sind nicht erlaubt. 9 1.6 Data Manipulation Language select name, capital from country name capital Albania Tirane Greece Athens Macedonia Skopje ... ... 195 Datensätze Sortierung. Soll die Ausgabe sortiert werden, so wird die order-Klausel verwendet, welche ans Ende des select-Statements angefügt wird. Die Klausel nimmt eine Liste von Spalten entgegen, nach denen dann sortiert werden soll. Wird mehr als ein Attribut angegeben, 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 angegeben werden oder es können Spaltennummern (beginnend bei 1) angegeben werden, die sich auf die Position der Attribute in der select-Klausel beziehen. Standardmäßig wird aufsteigend (asc) sortiert, soll absteigend sortiert werden so ist (desc) hinter dem Attribut bzw. der Spaltennummer anzugeben. Im folgenden Beispiel werden der Name und die Länge der Flüsse aus Tabelle river absteigend sortiert nach der Länge der Flüsse ausgegeben. select name, length from 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. Will man jedoch nur Datensätze, die eine bestimmte Bedingung erfüllen ausgeben, so benötigt man die where-Klausel, welche die Formulierung von Bedingungen ermöglicht. Bedingungen werden auf den Attributen formuliert und können mit not, and, or zu komplexeren Bedingungen zusammengesetzt werden11. Zusätzlich können Klammern eingesetzt 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 einem anderen Attribut. Im folgenden Beispiel werden alle Flüsse ausgegeben, die in den Atlantischen Ozean (Literal ’Atlantic Ocean’) münden. 11 12 not hat dabei die höchste Bindungspriorität, gefolgt von and und or 6= 10 1 SQL name select from where order name, sea, length river sea=’Atlantic Ocean’ by 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 Mustervergleich. Dazu gibt es in SQL die Operatoren like und not like . Die beiden Sonderzeichen ’%’ und ’_’ stellen dabei so genannte Wildcards dar. ’%’ steht für eine beliebige Anzahl von Zeichen, ’_’ steht für exakt ein Zeichen. Die Bedingung stadtname like ’%burg’ liefert beispielsweise alle Städte zurück, die auf “burg” enden, so z.B. Hamburg, Salzburg, Sankt Petersburg. Neuere Versionen einzelner Datenbanksysteme erlauben zudem die Suche mittels regulä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 vertieft werden. Weiterhin gibt es noch den Operator between , der zur Bereichsüberprüfung eingesetzt werden kann. Beispiel: einwohner between 1000 and 20000 gibt alle Datensätze zurück, deren Einwohnerzahl zwischen 1000 und 20000 Einwohner liegt. Um ein Attribut gegenüber eine Menge von Werten zu testen kann man entweder mehrere Bedingungen mittels or verbinden oder den in-Operator benutzen. Beispiel: code in (’F’,’ES’,’I’,’GR’) gibt alle Länder zurück, deren code entweder ’F’, ’ES’, ’I’ oder ’GR’ ist. Der in-Operator wird uns später auch noch bei den Subqueries (Seite 14) begegnen, wo statt der Liste von Literalen ein vollständiges select-Statement stehen kann. Umgang mit NULL. Eine besondere Bedeutung spielt, wie in Abschnitt 1.4 bereits angesprochen, der Attributwert NULL. Da NULL auch von sich selbst verschieden ist, führt die Formulierung folgender Bedingung immer zu FALSE: 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 See münden und länger als 1000 Kilometer sind. 11 1.6 Data Manipulation Language name select from where and name,lake,length river lake is not null length > 1000 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 Ergebnis erscheinen. Wünscht man dies nicht, so kann dies durch Angabe des Schlüsselwortes distinct innerhalb der select-Klausel unterbunden werden. So führt die nahe liegende Lösung auf die Frage nach “allen Flüssen die Zuflüsse besitzen” nicht zum gewünschten Ergebnis, da viele Flüsse mehrere Zuflüsse haben und somit in der Ergebnismenge mehrfach auftreten. river Amudarja select from where order river river river is not null by 1 Amur Amur Busira ... 57 Datensätze Erst durch die Hinzunahme von distinct erscheint das erwünschte Ergebnis. river Amudarja select from where order distinct river river river is not null by 1 Amur Busira Dalaelv ... 27 Datensätze Spaltenaliase. Sollen die Überschriften der Spalten verändert werden, so werden Spaltenaliase eingesetzt Spaltenaliase werden in der select-Klausel hinter der jeweiligen Spalte und dem Schlüsselwort as angegeben. Das folgende Beispiel zeigt die Daten der Tabelle river mit deutschen Spaltenüberschriften. Der zweite Spaltenalias bestehen aus mehr als einem Bezeichner und muss deshalb in doppelten Anführungszeichen (") stehen. 12 1 SQL Flussname select name as Flussname, round(length/1.609) as "Länge (Meilen)" from river where length is not null order by 2 desc 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 (Umrechnung des Wertes der Spalte length von Kilometer nach Meilen)13 einen sinnvollen Namen zu geben. Die order-Klausel gibt schließlich an, dass die Ausgabe absteigend nach der 2. Spalte sortiert erfolgen soll. Aggregatsfunktionen. SQL stellt eine Reihe von Funktionen zur Verfügung, welche als Eingabe eine Tupelmenge (Menge von Datensätzen oder Menge von Einzelwerten) erwarten, als Ausgabe jedoch nur einen einzigen Wert zurückliefern. Im Unterschied zu normalen Funktionen (siehe Tabelle 1.6.2) arbeiten diese Funktionen somit tupel übergreifend und werden deshalb als Aggregatsfunktionen bezeichnet. SQL kennt folgende Aggregatsfunktionen: • count(<Attributbezeichner>) bzw. count(*) : Zählt die Anzahl der Elemente in der übergebenen Menge und liefert deren Anzahl zurück. Es können entweder komplette Datensätze (symbolisiert durch *) oder einzelne Attribute (Angabe des Attributnamens) an die Funktion übergeben werden. Im Fall, dass count(...) mit einem Attributnamen aufgerufen wird ist wichtig zu wissen, dass nur Nicht-NULL Werte gezählt werden. Sollen keine Duplikate gezählt werden, so kann mit count(distinct ...) vor dem Zählen eine Duplikatseleminierung durchgeführt werden. • sum(<Attributbezeichner>) : Summiert die einzelnen Werte der übergebenen Zahlenmenge auf. Das Attribut muss numerisch sein. • avg(<Attributbezeichner>) : Liefert den Durchschnitt der einzelnen Werte der übergebenen Zahlenmenge. Das Attribut muss numerisch sein. • stddev(<Attributbezeichner>)14 : Liefert die Standardabweichung der einzelnen Werte der übergebenen Zahlenmenge. Das Attribut muss numerisch sein. • min(<Attributbezeichner>) : Liefert den kleinsten Wert der einzelnen Werte der übergebenen Wertemenge. • max(<Attributbezeichner>) : Liefert den größten Wert der einzelnen Werte der übergebenen Wertemenge. Das folgende Beispiel ermittelt die Länge des längsten Flusses: 13 Die Grundrechenarten (+, −, ∗, /) sowie die Funktion round(...) sind in SQL standardmäßig verfügbar. Eine Übersicht über weitere Funktionen findet sich in Tabelle 1.1 auf Seite 21. 14 Beim MS SQL-Server heißt diese Funktion stdev 13 1.6 Data Manipulation Language Länge select max(length) as Länge from river 6518 1 Datensatz Gruppierungen. So wie die Aggregatsfunktionen im obigen Beispiel angewandt wurde, liefert sie für ein select-Statement genau einen Datensatz zurück. Indem wir die Datensätze zuvor gruppieren, kann die Aggregatsfunktion auf jede Gruppe einzeln angewandt werden. Um eine Gruppierung durchzuführen, benötigt man die group by-Klausel. Der Klausel folgt eine Liste von Attributen, nach denen gruppiert werden soll. Die Gruppierung erfolgt dann derart, dass alle Datensätzen, deren Werte bei den in der group byKlausel spezifizierten Attributen übereinstimmen, zusammengefasst werden. Das folgende Beispiel in Abbildung 1.3 zählt für jeden Fluss in der Datenbank, wieviele Zuflüsse er hat. Dazu werden in einem ersten (internen15) Schritt, alle Datensätze deren Werte in der Spalte river übereinstimmen in eine Gruppe zusammengefasst (Mitte). Anschließend wird dann für jede Gruppe gezählt, wieviele Datensätze in ihr existieren und diese Information, absteigend sortiert nach der Anzahl der gruppierten Datensätze sowie 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 ausgegeben 14 1 SQL Bedingungen auf Aggregaten. Als nächstes sollen jetzt auf den Aggregaten Bedingungen formuliert werden. So sollen beispielsweise nur die Flüsse ausgegeben werden, die drei oder mehr Zuflüsse besitzen. Schaut man sich das obige SQL-Statement an, so erkennt man, dass dies nicht in der where-Klausel passieren kann, da zum Zeitpunkt der Auswertung der where-Klausel das Aggregat noch nicht errechnet ist, sondern hier nur entschieden wird, welche Datensätze überhaupt der Gruppierung zugeführt werden (nur solche die in der Spalte river einen Wert stehen haben). Aus diesem Grund existiert die having-Klausel, welche es dann in einem nachgeschalteten Schritt erlaubt, Bedingungen auf den Ergebnissen von Aggregatsfunktionen zu formulieren. Im konkreten Beispiel sind das die Flüsse, für die die Spalte count(*) einen Wert größer zwei liefert, d.h. der Fluss drei oder mehr Zuflüsse hat. river select river, count(*) as "Anzahl Zuflüsse" from river where river is not null group by river having count(*) > 2 order by 2 desc 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 hinzugekommenen having-Klausel eine Bedingung formuliert wurde, in der eine Aggregatsfunktion vorkommt. Subquery. Mittels einer Subquery können mehrere SQL-Statements kombiniert werden. Dazu wird in der where-Klausel einfach statt eines Literals ein in Klammer stehendes select-Statement eingesetzt. So kann die Frage nach allen Flüssen, die eine überdurchschnittliche Länge haben, wie folgt 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 ermittelt. Anschließend wird dann die äußere Anfrage mit dem Ergebnis der inneren Anfrage ausgeführt. Bei der Benutzung der Operatoren <, >, =, <> ist es notwendig, dass das innere Statement genau einen Datensatz zurückliefert. Dies ist oft dann der Fall, wenn eine 1.6 Data Manipulation Language 15 Bedingung auf dem Primärschlüssel formuliert wird, bzw. wenn wie im Beispiel eine Aggregatsfunktion eingesetzt wird. Eine andere Möglichkeit besteht darin, ein Attribut gegenüber einer Menge von Werten zu vergleichen. Dazu wird der auf Seite 10 eingeführte in-Operator benutzt. Statt der konstanten Literale wird einfach ein select-Statement in die Klammer geschrieben. Das folgende Beispiel gibt alle Ländernamen aus, die von der Donau durchflossen werden. Dazu werden in einem ersten Schritt innerhalb der grau unterlegten Subquery alle Ländercodes extrahiert, durch welche die Donau entsprechend den Informationen aus der Tabelle geo_river (siehe Seite 7) fließt. Das äußere Statement nimmt diese Menge entgegen und gibt dann für alle Länder deren code sich in der von der Subquery ermittelten Menge befinden, den Landesnamen (name) aus. name Austria Bulgaria Germany Hungary ... 7 Datensätze Im obigen Beispiel konnte die innere Abfrage unabhängig von der äußeren Abfrage ausgeführt werden, man spricht in diesem Fall von einer unkorrelierten Subquery. Es ist jedoch auch möglich, die innere Abfrage mit der Äußeren zu verzahnen. Dies wird dadurch realisiert, indem in der where-Klausel der inneren Anfrage auf ein Attribut der äußeren Anfrage zugegriffen wird. In diesem Fall kann die innere Anfrage nicht mehr unabhängig von der Äußeren ausgeführt werden, sondern muss für jedes Tupel der äußeren Anfrage erneut ausgeführt werden. Man spricht in diesem Fall von einer korrelierten oder verzahnt geschachtelten Anfrage [HS00]. Das folgende Beispiel zeigt eine derart verzahnte Abfrage. Die Anfrage soll alle Flüsse zurü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 dient zur eindeutigen Unterscheidung der Attribute in den verschiedenen Tabellen. So gibt es beispielsweise sowohl in der Tabelle river, als auch in der Tabelle country ein Attribut name, die durch Voranstellen des Tabellennamens auseinander gehalten werden können. 16 1 SQL Es gilt allgemein als guter Stil mit der Punktnotation zu arbeiten, da man dadurch schneller erkennen kann, zu welcher Tabelle das jeweilige Attribut gehört. Die Notation wird auch von allen relationalen Datenbanksystemen unterstützt. Das eigentlich Interessante an der grau unterlegten Subquery befindet sich in der whereKlausel, die lautet: where geo_river.river = river.name In der Subquery ist das Attribut river.name jedoch unbekannt, da in der from-Klausel nur die Tabelle geo_river eingebunden wird. river.name stammt jedoch aus der äußeren Query (durch Pfeil angedeutet), in der die Tabelle river eingebunden ist und ist somit 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 (Attribut river.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 der where-Klausel mit dem Literal 3 verglichen. Ist der zurück gelieferte Wert größer oder gleich, so wird der Name des Flusses ausgegeben. 4. Anschließend wird von der äußeren Anfrage der nächste Datensatz gelesen und es wird mit Schritt (2) fortgefahren. Ähnlich wie der in-Operator funktioniert der exists -Operator. Im Unterschied zum in-Operator wird der exists-Operator ohne Attribut und Operator eingesetzt. Er überprüft lediglich, ob eine Subquery mindestens einen Datensatz zurückliefert. Ist das der Fall liefert exists TRUE zurück, andernfalls FALSE. Der exists-Operator wird fast ausschließlich mit korrelierten Subqueries eingesetzt. Beispielsweise lässt sich die letzte Anfrage nach den Flüssen, die durch drei oder mehr Länder fließen auch mittels des exists-Operators und einer having-Klausel in der grau 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 zwei select-Statement kombiniert werden können. Im einzelnen handelt es sich um folgende Operationen: • union : Vereinigung der Tupel beider Anfragen. 1.6 Data Manipulation Language • • 17 except : Es werden alle Tupel des ersten select-Statement ausgegeben, die nicht im Ergebnis des zweiten select-Statement stehen16. intersect : Es werden alle Tupel ausgegeben, die sich sowohl im Ergebnis der erstenals auch der zweiten SQL-Anweisung befinden17. Diese Operationen arbeiten als reine Mengenoperationen , d.h. es findet jeweils eine Duplikatseliminierung der Ergebnisrelation statt. Sollen Duplikate erhalten bleiben, so muss man auf die Operationen union all, except all18 bzw. intersect all18 zurückzugreifen. Bei den Operationen ist darauf zu achten, dass die Tupel der beiden Ergebnismengen eine kompatible Struktur besitzen. Kompatibel heißt in diesem Fall, dass die Anzahl Spalten in den beiden select-Klauseln identisch sein muss und die jeweiligen Datentypen 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 river where lake is not null) order by 1 Joins. Bisher kamen die angezeigten Ergebnisse stets aus einer Tabelle. Zwar kann mithilfe 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 anzuzeigenden Daten aus verschiedenen Tabellen stammen. Mit Hilfe von Joins können Daten aus verschiedenen Tabellen miteinander in Bezug gesetzt und zusammen ausgegeben werden. Betrachten wir noch einmal die Daten aus den Tabellen river, geo_river und country. Die Tabelle river enthält die Flussdaten, während die Tabelle country Daten zu den einzelnen Ländern bereithält. Die beiden Tabellen haben keinerlei Bezug zueinander. Der Bezug kann aber über die Tabelle geo_river hergestellt werden. Die Spalte geo_river.river ist ein Fremdschlüssel auf den Primärschlüssel der Tabelle river (Attribut name), während das Attribut geo_river.country ein Fremdschlüssel auf den Primärschlüssel der Tabelle country (Attribut code) darstellt. Abbildung 1.4 verdeutlicht diesen Zusammenhang graphisch. Will man beispielsweise wissen, durch welche Länder die einzelnen Flüsse fließen, so muss man die beiden Fremdschlüsselbeziehung der Tabelle geo_river verfolgen und dabei die Schlüssel-/Fremdschlüsselattribute jeweils gleichsetzen. Konkret muss im obigen Beispiel gelten: 16 Oracle, PostgreSQL nicht beim MS SQL-Server und MySQL nicht beim SQL-Server und MySQL 18 nur DB2 19 Numerische Datentypen sind untereinander kompatibel und alle zeichenkettenbasierten Datentypen sind ebenfalls untereinander kompatibel 17 18 1 SQL river.name = geo_river.river und geo_river.country = country.code (Linien links in Abbildung 1.4) (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 sich dann auch relativ einfach aus obiger Überlegung ableiten, indem in der from-Klausel einfach alle Tabellen aufgeführt werden, die für die Anfrage relevant sind und in der where-Klausel die Bedingungen, welche zwischen den Schlüsseln-/Fremdschlüsseln gelten müssen, formuliert werden. So lautet die Anfrage, die für alle Flüsse über 2000 Kilometer Länge, den Namen, die Lä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_river where river.name = geo_river.river and geo_river.country=country.code and river.length > 2000 order by 2 desc Fluss Länge Amazonas 6518 Brazil Amazonas 6518 Colombia Amazonas 6518 Peru Jangtse-Kiang 6100 China Parana 4700 Argentina ... ... Land ... 49 Datensätze distinct muss in diesem Fall benutzt werden, da in der Tabelle geo_river die Beziehungen zu den einzelnen Provinzen angegeben sind, hier aber nur die Länder interessieren. Ein Nachteil der Join-Syntax im SQL-89 Standard ist, dass man auf den ersten Blick nicht erkennen kann, was Schlüssel-/Fremdschlüsselvergleiche sind und wobei es sich um echte Bedingungen (fluss.laenge > 2000) handelt. 19 1.6 Data Manipulation Language Aus diesem Grund wurde im SQL-2 Standard von 1992 eine etwas andere Notation für Joins vorgeschlagen, die so genannte ANSI -Notation. Neu bei der ANSI-Notation ist die join-Klausel. Die Join Klausel tritt nach einer from- oder join-Klausel auf20, erwartet einen Tabellennamen und erlaubt anschließend (nach dem Schlüsselwort on) die Formulierung einer oder mehrerer Schlüssel/Fremdschlüsselbedingungen für die zuvor angegebenen Tabellen. So lautet etwa die zuvor gestellte Anfrage in ANSI-Notation: select distinct river.name as Fluss, river.length as Länge, country.name as Land from country join geo_river on country.code=geo_river.country join river on geo_river.river = river.name where river.length > 2000 order by 2 desc Fluss Länge Amazonas 6518 Brazil Amazonas 6518 Colombia Amazonas 6518 Peru Jangtse-Kiang 6100 China Parana 4700 Argentina ... ... Land ... 49 Datensätze Bei dieser Notation wird die Trennung zwischen der eigentlichen Bedingung und den Schlüssel-/Fremdschlüsselbedingungen im Anschluss an die beiden join-Klauseln deutlich. Neben dem klassischen Join mit Schlüssel-/Fremdschlüsselbedingung unterstützen die Datenbanken noch eine Reihe weitere Arten von Joins, die im Folgenden vorgestellt werden sollen. • Cross Join (Kartesisches Produkt): Jeder Datensatz der linken Tabelle (A) wird mit jedem Datensatz der rechten Tabelle (B) kombiniert. Das Ergebnis ist eine Ergebnistabelle mit n-Zeilen21 und m-Spalten22. Der Cross-Join kommt in seiner Reinform relativ selten vor Er entspricht dem klassischen Join ohne Formulierung von Schlüssel-/Fremdschlüsselbedingungen. Die folgende Abbildung zeigt exemplarisch die Konstruktion des kartesischen Produktes aus zwei Tabellen. Abb. 1.5: Kartesisches Produkt von 2 Tabellen 20 Die join-Klausel kann mehrfach auftreten, die erste join-Klausel folgt jedoch immer der from-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 die Datensätze aus der linken23 Tabelle ausgegeben werden24, für die es auf der rechten Seite keine passenden Datensätze gibt. Die Spalten aus der rechten Tabelle enthalten dann im Ergebnis NULL Werte. Ein Beispiel für einen Outer-Join ist folgende Anfrage: “Gib alle Länder zusammen mit ihren Flüssen aus” name select distinct country.name, geo_river.river from country left join geo_river on country.code=geo_river.country order by country.name, geo_river.river river ... ... Albania White Drin Algeria Andorra Angola Casai ... ... 319 Datensätze • • • Man kann sehen, dass “Algerien” und “Andorra” im Ergebnis auftauchen, obwohl für sie keine Flüsse in der Datenbank hinterlegt sind. Right Outer Join: Wie ein normaler Join, bei dem zusätzlich aber auch noch alle die Datensätze aus der rechten Tabelle ausgegeben werden24 für die es auf der linken Seite keine passenden Datensätze gibt. Die Spalten aus der linken Tabelle enthalten dann im Ergebnis NULL Werte. Full Outer Join: Wie ein normaler Join, bei dem zusätzlich alle Datensätze sowohl aus der linken- als auch der rechten Tabelle ausgegeben für die es keine passenden Datensätze gibt. Die Spalten aus der linken bzw. rechten Tabelle enthalten dann im Ergebnis NULL Werte. 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 zu vergeben. Aliasnamen werden in der from- und join-Klausel durch Anhängen des Aliasnamen direkt hinter den Tabellennamen vergeben. Anschließend kann im Statement der Aliasname anstelle des Tabellennamens verwendet werden. Notation: <tabellenname> <aliasname> Ein Vorteil liegt darin, dass man im Allgemeinen kurze und/oder aussagekräftige Aliasnamen vergeben kann und so zu einer kompakteren und übersichtlicheren Schreibweise bei Benutzung der Punkt-Notation kommt. Es gibt jedoch auch Situation, in der die Benutzung von Aliasen zwingend notwendig ist. Beispielsweise, wenn für alle Städte der USA Namensvettern in anderen Ländern gesucht werden sollen. Die benötigten Informationen liegen zwar alle in der Tabelle city. Allerdings ist es nicht so einfach die Anfrage zu formulieren, da die where-Klausel es lediglich erlaubt Bedingungen auf Datensatzebene25 zu formulieren. Um zum Ziel zu 23 im select-Statement zuerst genannten Tabelle sofern sie eine eventuell zusätzlich angegebene where-Klausel erfüllen. 25 d.h. es wird zu einem Zeitpunkt genau ein Datensatz untersucht. 24 21 1.6 Data Manipulation Language kommen muss man die Tabelle city mit sich selber vergleichen, d.h. es muss für jeden Datensatz aus der Tabelle city untersucht werden, ob es einen weiteren Datensatz in der selben Tabelle gibt, der den gleichen Namen (Attribut name) aber eine andere Länderkennung (Attribut country) hat. Das komplette Statement lautet dann folgendermaßen: name select from join on where and order c1.name, c2.country city c1 city c2 c1.name=c2.name c1.country <> c2.country c1.country=’USA’ by 1,2 country Albany AUS Alexandria ET Alexandria RO Birmingham GB ... ... 18 Datensätze Funktionen. SQL definiert eine Reihe von Funktionen die im Rahmen der DML Statements eingesetzt werden können. So gibt es numerische, zeichenkettenbasierte Funktionen, ebenso wie eine Reihe von Konvertierungs- und Datumsfunktionen. Die Funktionen können in der select-, where-, order- und having-Klausel eingesetzt werden. Da im Bereich der Funktionen noch etwas Wildwuchs in Bezug auf Standardisierung herrscht, sollen in Tabelle 1.1 lediglich einige exemplarische Funktionen aufgezählt werden, ohne jedoch auf deren genaue Syntax-/Aufrufkonventionen einzugehen. Der Leser sei an dieser Stelle auf die Literatur des konkreten Datenbanksystems verwiesen um einen umfassenden Überblick über die dort implementierten Funktionen 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 entfernen trim(...), 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 das Ergebnis einer relationalen Operationen stets wieder eine Relation ist, was insbesondere auch die Formulierung geschachtelter Anfragen erlaubt. Übertragen auf SQL bedeutet dies, dass in der from-Klausel auch ein select-Statement stehen kann. Einschränkend muss jedoch gesagt werden, dass an dieser Stelle keine korrelierten Abfragen (siehe Seite 15) erlaubt sind, d.h. das select-Statement in der from-Klausel darf keine Attribute aus der äußeren Query verwenden. Die Anfrage im folgenden Beispiel gibt alle Länder aus, deren Anteil der Stadtbevölkerung mindestens 50% der Gesamtbevölkerung beträgt. Dazu wird ein Join zwischen der Tabelle country und dem in Klammer stehenden select-Statement (Tabellenalias u) formuliert. name ratio Liechtenstein 0.89 Singapore 0.75 Australia 0.67 Qatar 0.61 South Korea 0.59 ... ... 8 Datensätze Das in der join-Klausel stehende select-Statement liefert pro Land (Spalte country) die berechnete Summe aller Einwohner der Städte (Spaltenalias population) zurück. Dieses Ergebnis wird dann mittels des Joins mit der Tabelle country um den ausgeschriebenen Landesnamen (Attribut country.name) ergänzt und nur die Länder ausgegeben, deren Anteil der Stadtbevölkerung (u.population) an der Gesamtbevölkerung (Spalte country.population) bei über 50% liegt. Diese Art von Anfrage wird auch als Instant View26 bezeichnet. Weiterhin ist es auch möglich, dass ein select-Statement in der select-, orderoder where-Klausel auftritt. Im Unterschied zum vorherigen Fall sind hierbei auch korrelierte Subqueries erlaubt, allerdings muss das Statement genau einen Wert zurückliefern. So kann die letzte Anfrage nach “allen Ländern mit mehr als 50% Stadtbevölkerung” auch folgendermaßen formuliert werden: 26 Ein View ist eine virtuelle, auf einer Anfrage basierenden Tabelle und wird auf Seite 29 vorgestellt. 1.6 Data Manipulation Language 23 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 der select-Klausel summiert dabei alle Einwohner in den Städten des aktuellen Landes (c.code) auf. Zur Formulierung der Bedingung, dass nur die Länder ausgegeben werden sollen, deren Anteil bei über 50% liegt, wird das in der select-Klausel stehende select-Statement in der where-Klausel wiederholt27. Soweit zu den verschiedenen, auf den Operationen der Relationenalgebra basierenden, Varianten des select-Statement. In den folgenden drei Abschnitten sollen jetzt, ebenfalls auf Basis der Mondial-Datenbank, die insert-, update- und delete-Statements vorgestellt werden. 1.6.3 Insert-Statement Das insert-Statement erlaubt das Einfügen von Tupeln in eine Tabelle. Es stehen zwei Varianten zur Auswahl: insert into <tabellenname> (<attributliste>) values (<werteliste>) Bei dieser ersten Variante wird genau ein Datensatz in die angegebene Tabelle eingetragen. Die Attributliste beinhaltet dabei Attribute aus der angegebenen Tabelle. Es ist möglich weniger Attribute anzugeben, als für die Tabelle definiert worden sind28 . Wichtig ist hierbei, dass die Anzahl der hinter der insert-Klausel stehenden Attribute identisch ist mit der Anzahl der Werte in der values-Klausel und die Datentypen übereinsteimmen. Das folgende Beispiel zeigt den Eintrag der Stadt Karlsruhe in die Tabelle city. 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 einzutragen. Dazu wird das insert-Statement mit einem select-Statement kombiniert. Die sich im select-Statement qualifizierenden Datensätze werden dann in die im 27 das geschachtelte select-Statement wird aber deshalb nicht mehrfach ausgeführt, sondern der Optimierer des Datenbanksystems erkennt, dass die Anfrage in der select-Klausel und where-Klausel identisch sind und führt sie deshalb nur einmal aus 28 Es können alle die Attribute weggelassen werden, die bei der Definition der Tabelle (siehe Seite 27) nicht als NOT NULL definiert wurden. 24 1 SQL insert-Statement angegebene Tabelle eingetragen. Hierbei müssen die in der insertKlausel angegebenen Attribute in Anzahl und Datentyp mit den Attributen des selectStatement übereinstimmen. insert into <tabellenname> (<attributliste>) (<select-Anweisung>) Das folgende Beispiel trägt alle Datensätze aus der Tabelle terra_stadt29 in die Tabelle city ein. insert into city (name, country, province, population, longitude, latitude) (select name, l_id, lt_id, einwohner, laenge, breite from terra_stadt) 1.6.4 Update-Statement Das update-Statement dient dazu, Datensätze die sich bereits in einer Tabelle befinden zu modifizieren. Dabei können ein oder mehrere Datensätze auf einmal modifiziert werden. Die Syntax ist wie folgt: update <tabellenname> set <attribut1>=<wert1> [,<attribut2>=<wert2>] ... where <bedingungen> Die Bedingungen entsprechen denen der where-Klausel des select-Statement. So setzt das folgende Statement die Koordinaten der Stadt Karlsruhe auf (8.4, 49.0): update city set longitude=8.4, latitude=49.0 where country=’D’ and province=’Baden Württemberg’ and name=’Karlsruhe’ Neben Literalen können auch Berechnungen oder korrelierte Subqueries (die genau einen Wert zurückliefern) eingesetzt werden. So erhöht das folgende Beispiel die Einwohnerzahl aller italienischen Städte um 5%: update city set population = round(population*1.05) where country=’I’ 29 Die Terra-Datenbank [DR90] wurde am Institut für Programmstrukturen und Datenorganisation der Universität Karlsruhe entwickelt und dort im Datenbankpraktikum eingesetzt. Die TerraDatenbank diente der Mondial-Datenbank als 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änder pro Kontinent, die Gesamtfläche der einzelnen Kontinente zu berechnen und in die Tabelle continent einzutragen30. 1.6.5 Delete-Statement Das delete-Statement ist für das Löschen einer oder mehrerer Datensätze einer Tabelle verantwortlich. Die allgemeine Syntax ist wie folgt: delete from <tabellenname> where <bedingungen> Die Bedingungen entsprechen denen der where-Klausel des select-Statement. So lautet der Befehl um alle Städte zu löschen, für die keine vollständigen Koordinateninformationen in der Tabelle city abgelegt sind: delete from city where longitude is null or latitude is null 1.7 Data Definition Language Im Gegensatz zur DML, bei der die Syntax größtenteils über die Herstellergrenzen hinweg identisch ist, ist dies bei der DDL nicht ganz der Fall. So besitzen die Produkte der einzelnen Hersteller zumeist unterschiedliche Bezeichnungen für die zur Verfügung stehenden Datentypen und ihre Syntax (selten auch die Semantik) weicht an einzelnen Stellen voneinander ab. Da auf Platzgründen nicht auf die verschiedenen konkreten Implementierungen eingegangen werden kann, wird hier stellvertretend die DDL der Oracle-Datenbanken vorgestellt. Die dabei vermittelten Konzepte sind aber auf die anderen relationalen Systeme übertragbar. 30 Bei der Tabelle encompasses 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 entsprechenden Manuals des jeweiligen Herstellers bzw. die einschlägigen Fachliteratur zurückzugreifen 31 . 1.7.1 Tabellen Primäre Aufgabe der DDL ist es, die Strukturen in welche die Datensätze abgelegt werden, vorzugeben. Die Datensätze werden in so genannten Tabellen abgelegt, welche die SQL-spezifische Umsetzung der Relationen des Relationenmodells darstellen. Eine Tabelle besitzt eine feste Anzahl an Attributen, die alle einen, für diese Tabelle eindeutigen, Bezeichner und Datentyp besitzen. Neben der Aufgabe der reinen Speicherung spielen konsistenzsichernde Maßnahmen zur Wahrung der Integrität (siehe auch Abschnitt 1.2.2) der Daten eine entscheidende Rolle. So können bei der Definition der Tabellen so genannte Constraints32 angegeben werden. Diese Constraints formulieren Bedingungen, die von den Datensätzen erfüllt werden müssen. Erfüllt ein Datensatz diese Bedingungen nicht so wird er von der Datenbank abgewiesen, bzw. die Ausführung der entsprechenden DML-Operation wird vom System abgelehnt. Constraints lassen sich folgenden Kategorien zuordnen: • Primärschlüssel (Primary Key): Definition eines oder mehrere Attribute einer Tabelle, deren Wert(e) den Datensatz eindeutig identifiziert. Der Wert des Primärschlüssels (oder eines Teils davon) darf niemals NULL sein. • Fremdschlüssel (Foreign Key): Definition eines oder mehrere Attribute einer Tabelle, die auf einen anderen Datensatz (in der gleichen oder einer anderen Tabelle) verweisen. Ist ein Fremdschlüssel definiert, so überwacht das Datenbanksystem, dass der Datensatz auf den verwiesen wird auch existiert. Dies wird als referentielle Integrität bezeichnet. Soll ein Datensatz gelöscht werden, auf den andere Datensätze mittels Fremdschlüssel verweisen, so wird dies vom Datenbanksystem abgelehnt, da sonst die referentielle Integrität verletzt würde. Es ist jedoch möglich für den Fremdschlüsseldatensatz ein spezielles Verhalten beim Löschen/Ändern des referenzierten Datensatzes zu definieren. So kann festgelegt werden, dass der verweisende Datensatz dann ebenfalls gelöscht werden soll (on delete cascade), bzw. dass der Wert des Fremdschlüssels auf NULL gesetzt werden soll (on delete set null). • Eindeutigkeit (unique): Definition eines oder mehrere Attribute einer Tabelle, deren Werte für alle Datensätze unterschiedlich sein müssen. Werden mehrere Attribute definiert, so müssen die Attributwertkombinationen unterschiedlich sein. • Nicht Null (not null): Wird eine Attribut als NOT NULL definiert, so muss jeder Datensatz für dieses Attribut einen Wert ungleich NULL besitzen. • Wertüberprüfung (check): Jedes Attribut hat einen bestimmten Datentyp der die möglichen Werte vorgibt (Zeichenketten bestimmter Länge, Zahlen mit vorgegebener Stellenzahl/Präzision, Datumstypen, etc.). Mittels des Check-Constraints kann 31 Es soll an dieser Stelle auch nicht verschwiegen werden, dass Kenntnisse des Datenbankdesigns eine noch wichtigere Rolle spielen. Ein empfehlenswertes Buch zum Thema Datenbankdesign ist [Her03]. 32 zu deutsch: Beschränkungen 1.7 Data Definition Language 27 dieser Wertebereich noch weiter eingeschränkt werden, indem beispielsweise mögliche diskrete Einzelwerte vorgegeben werden oder bei numerischen Typen ein Interval angegeben wird, in dem sich die möglichen Werte des Attributs befinden müssen. Im Folgenden sollen jetzt die wichtigsten Statements zum Anlegen/Modifiziern eines Datenbankschemas vorgestellt werden: Create Table. Das create table-Statement dient zum Anlegen einer neuen Tabelle. 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 Tabellen country und city der MondialDatenbank, einschließlich der Definition der Constraints, gezeigt werden33: Hinweis: Bei Oracle nutzt man statt dem Datentyp varchar, den oracle-spezifischen Datentyp varchar2. 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 erste create table-Statement legt die Tabelle country an. Das Attribut code wird als Primärschlüssel definiert, der durch eine maximal 3 Zeichen lange Zeichenkette repräsentiert wird. Der Wert des Attributs name kann aus maximal 32-Zeichen bestehen, zusätzlich wird gefordert, dass dieses Attribut immer einen Wert haben muss und weiterhin, dass dieser Wert für alle Datensätze in der Tabelle eindeutig sein muss, d.h. es darf beispielsweise keine zwei Datensätze mit dem Namen “Neuseeland” geben. 33 Hinweis: Wird mehr als ein Statement angegeben, so sind die einzelnen Statements durch einen Strichpunkt (;) voneinander zu trennen, damit der SQL-Parser erkennen kann wo die einzelnen Statements 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üssel auf eine Stadt in der Tabelle city. Da zum Zeitpunkt des Anlegens der Tabelle country, die Tabelle city noch nicht existiert, kann der Fremdschlüssel an dieser Stelle noch nicht formuliert werden, sondern dies wird nach dem Anlegen der Tabelle city durch ein alter table-Statement (s.u.) nachgeholt. Die letzten beiden Attribute der Tabelle (area, population) sind numerisch (Integer) und geben die Fläche des Landes sowie seine Einwohnerzahl an. Als zusätzliche Konsistenzbedingung ist hier angegeben, dass sowohl die Fläche als auch die Einwohnerzahl größer gleich 0 sein müssen. Das zweite create table-Statement zum Anlegen der Tabelle city ist ähnlich aufgebaut. Im Unterschied zur Tabelle country besteht der Primärschlüssel aber aus drei Attributen (name, province, country). Das Attribut country ist weiterhin ein Fremdschlüssel auf die Tabelle country. Da die Tabelle bereits existiert, kann der entsprechende Foreign Key Constraint auch sofort formuliert werden. Alter Table. Nachdem nun die Tabelle city angelegt worden ist, kann der Foreign Key Constraint, für die Hauptstadt eines Landes, gesetzt werden. Dies geschieht durch ein 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 einschließlich Constraints zur Tabelle city, als auch in einem zweiten alter tableStatement die Formulierung des Fremdschlüssels für die Hauptstadt eines Landes in der Tabelle country. Das dritte alter table-Statement ist dafür verantwortlich, dass jedes Land eine andere Hauptstadt hat. Dies wird dadurch realisiert, indem für die drei Attribute capital, province und code gefordert wird, dass sie unique sind, d.h. jede Attributwertkombination nur einmal vorkommen darf. In den beiden letzten Statements wird weiterhin von der Möglichkeit Gebrauch gemacht, dem Constraint einen Namen zu geben. Wird bei der Definition eines Constraints kein Name angegeben, so wird vom Datenbanksystem ein Name generiert Es empfiehlt sich jedoch einen (aussagekräftigen) Namen für ein Constraint anzugeben, da dieser im Falle der Verletzung einer Regel mit angegeben 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_capital foreign key (capital, province, code) references city(name, province, country) ); alter table country add ( constraint uq_country_capital unique (capital, province, code) ); 1.7 Data Definition Language 29 Das Löschen von Constraints erfolgt durch Angabe des Namens des Constraints. Das Löschen des eben angelegten Fremdschlüssels sieht demnach folgendermaßen aus: alter table country drop constraint fk_country_city_capital Im Folgenden noch zwei Beispiele für das Löschen einzelner Attribute einer Tabelle bzw. das Löschen einer kompletten Tabelle: alter table country drop column area; drop table city; Der Versuch eine Tabelle, die durch Fremdschlüssel referenziert wird, zu löschen scheitert. Entweder man löscht erst das Constraint oder man fügt die Schlüsselworte cascade constraints hinten an das drop table-Statement an, das vor dem Löschen der Tabelle 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 auf dem Ergebnis eines select-Statement anzulegen. Die Struktur der Tabelle wird durch die Attribute des select-Statement vorgegeben. Zusätzlich werden aber auch noch alle Datensätze, welche durch das select-Statement ausgewählt wurden in die neu angelegte Tabelle eingetragen. Das Statement ist demnach eine Mischung aus einem DDLund einem DML-Statement. Diese Feature ist speziell bei der Reorganisation bestehender Datenbanken äußerst hilfreich. Notation: create table <tabellenname> as <select-statement> 1.7.2 Views Ein View34 ist eine virtuelle Tabelle. Anders als bei einer Tabelle werden die zugrunde liegenden Daten aber nicht physikalisch gespeichert, sondern ihnen liegt eine Berechnungsvorschrift in Form eines select-Statement zugrunde. Views können in DMLStatements genauso wie Tabellen eingesetzt werden. Weiterhin lassen sich Views auch auf 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 liegenden Tabellen/Views extrahieren. Views besitzen eine Reihe von interessanten Einsatzgebieten, die im folgenden kurz angesprochen werden sollen [HS00]. 34 35 zu deutsch: Sicht und nicht zum Zeitpunkt des Anlegens des Views 30 1 SQL Views können in select-Statements genauso wie Tabellen eingesetzt werden. Dadurch lassen sich oft benötigte und/oder komplexe Anfragen in Form von Views vordefinieren, so dass konkrete Anfragen dann auf dem View aufsetzen können und somit die Anfrage entsprechend einfacher zu formulieren ist. • Views erlauben die Realisierung einer klaren Schnittstelle oberhalb der materialisierten Tabellen. Für verschiedene Benutzergruppen lassen sich so verschiedene Sichten einrichten, die genau auf deren Bedürfnisse zugeschnitten sind. • Durch die Formulierung von Bedingungen innerhalb des select-Statement, können Zugriffsbeschränkungen formuliert werden. Einzelne Spalten oder ganze Datensätze sind so beispielsweise für bestimmte Benutzergruppen ausgeblendet bzw. werden nur in aggregierter Form präsentiert. Die Regelung des Zugriffs einzelner Benutzer- oder ganzer Benutzergruppen auf Tabellen oder Views erfolgt durch die DCL und wird in Abschnitt 1.8 vorgestellt. Das folgende Beispiel zeigt das Löschen und die anschlieSSende Neudefinition des Views Hauptstaedte: • drop view hauptstaedte; create as select from join on and and view hauptstaedte city.* city country city.name = country.capital city.province = country.province city.country = country.code; Anschließend kann der View dann für beliebige Anfragen benutzt werden, wie z.B. hier bei der Frage nach allen Hauptstädten zwischen dem 20. Grad nördlicher- und dem 20. Grad südlicher Breite. select from where order * hauptstaedte latitude between -20 and 20 by name Views sind unter bestimmten Bedingungen auch änderbar, d.h. es können Daten neu eingetragen, modifiziert und auch wieder gelöscht werden. Die Änderungen werden dann an den dem View zugrunde liegenden Tabellen durchgeführt. Dies ist jedoch nicht immer möglich. So ist leicht einzusehen, dass bei einem View der eine Aggregatsfunktion wie avg(...) enthält, diese Spalte nicht änderbar sein kann, da die Änderung auf die n-Eingabedatensätze der Aggregatsfunktion propagiert werden müßte. Andere Operationen die i.a. Probleme bereiten sind die Duplikatseliminierung mit distinct, Mengenoperatoren wie union, intersect, etc., Joins, Subqueries in der select-Klausel und der Einsatz der group by-Klausel. Ist ein View änderbar, so kann durch die Angabe “with check option” am Ende der View-Definition noch festgelegt werden, dass nur solche Änderungen (neueintragen, modifizieren) vorgenommen werden dürfen, die anschließend im View auch sichtbar sind, sprich, der Datensatz muss die Bedingungen der where-Klausel erfüllen. 1.7 Data Definition Language 1.7.3 31 Indexe Ein Index ist ein Datenbankobjekt, das den Zugriff auf die Daten beschleunigen soll. So werden vom Datenbanksystem aus bereits Indexe auf Primär-, Fremdschlüssel- und Unique-Attribute gesetzt. Das Prinzip eines Index beruht darauf, dass bei der Suche nach einem oder mehreren Datensätzen nicht die gesamte Tabelle sequentiell in den Hauptspeicher geladen werden muss, was insbesondere bei großen Datenmengen eine sehr zeitaufwendige Operation ist, sondern es eine geeignete Datenstruktur gibt, die nach wenigen Schritten den Ort auf der Festplatte bestimmen kann, auf der sich der/die gesuchten Datensätze befinden. Durch das Anlegen von Indizes kann der Lesezugriff zwar beschleunigt werden, der schreibende Zugriff wird jedoch dabei langsamer, da nicht nur die eigentlichen Daten geä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 Syntax zum 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üllt viele Bücher. So spielt insbesondere auch die Kenntnis des internen Aufbaus und der Funktionsweise eines konkreten Datenbanksystems eine entscheidende Rolle um nichttriviale Optimierungen vorzunehmen. Der interessierte Leser sei hier auf [SB02] und [GUW99] verwiesen. 1.7.4 Automatisch generierte Primärschlüssel In der Mondial-Datenbank wird der Primärschlüssel jeweils aus einem oder mehreren Attributen des jeweiligen Objekttyps gebildet. So wird der Primärschlüssel einer Stadt (city) aus der Landes ID (country), dem Provinznamen (province) und dem Namen der Stadt name) gebildet. Als Beispiel für einen Primärschlüssel bestehend aus nur einem Attribut, sei die Tabelle Fluss angeführt, deren Primärschlüssel aus dem Name des Flusses (Attribut name) 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 weitere Attribute (z.B. die Koordinaten der Quelle) mit in den Primärschlüssel aufgenommen werden. • Ein Fremdschlüssel auf einen Datensatz dessen Primärschlüssel aus n-Attributen besteht, besteht ebenfalls aus n-Attributen. Um diese Probleme zu vermeiden führt man oft künstliche Schlüsselattribute ein, so genannte Surrogate Keys. Ein Schlüsselattribut hat in der Semantik der Anwendung keine Bedeutung und dient lediglich dazu eindeutige Schlüsselattributwerte bereitzustellen und damit auch Fremdschlüssel, bestehend aus lediglich einem Attribut, zu ermöglichen. 36 So gibt es beispielsweise sowohl in Rheinland Pfalz als auch in Bayern, Hessen und Sachsen einen “Schwarzbach”. 32 1 SQL Datenbanksysteme unterstützen den Entwickler bei der Generierung eindeutiger Attributwerte. So gibt es in Oracle so genannte Sequenzen, die fortlaufend neue numerische Werte 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 von seq_mountain.nextval liefert einen um eins erhöhten Wert zurück, der dann mittels des insert-Statement eingetragen wird. Der zuletzt zurückgelieferte Wert kann mittels seq_mountain.currval erfragt werden. Ein anderes verbreitetes Konzept ist, den Primärschlüssel bei der Definition als künstlichen Primärschlüssel zu charakterisieren, der vom Datenbanksystem automatisch mit eindeutigen Werten versorgt wird. Beispielsweise kann man in MySQL dem (numerischen) Primärschlüssel einer Tabelle die Eigenschaft auto_increment zuweisen. In diesem Fall wird beim Anlegen eines neuen Datensatzes für den Primärschlüssel kein Wert angegeben, 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 entsprechende Konzepte für eine Benutzer- und Rollenverwaltung bereitstellen muss, und zum zweiten auch die Koordination des Zugriffes mehrerer Benutzer auf den Datenbestand. 1.8 Data Control Language 33 Die Koordination des Zugriffs wird in Datenbanken mittels Transaktionen gelöst. Hierbei handelt es sich um ununterbrechbar ablaufende Operation, die die Datenbank durch eine Reihe von Einzelschritten von einem konsistenten Zustand in einen neuen, nicht notwendigerweise verschiedenen, konsistenten Zustand überführen. Während auf Transaktionen hier nicht weiter eingegangen wird37, sollen im Folgenden die grundlegenden Konzepte der Benutzer- und Zugriffsverwaltung vorgestellt werden. 1.8.1 Benutzer und Rollen Ein Datenbankmanagementsystem verwaltet im allgemeinen n-Benutzer und m-Datenbanken. In den meisten Systemen können Rollen definiert werden, mittels denen Benutzer mit gleichem Profil zusammenfasst werden können. Der Vorteil hierbei ist, dass die speziellen Rechte nicht jedem Benutzer direkt zugeteilt werden müssen, sondern zentral der Rolle zugeordnet werden können und dann die Benutzer diese Rolle zugewiesen bekommen. Ein Beispiel für das Anlegen einer neuen Rolle und eines neuen Benutzers in Oracle lautet wie folgt: create role student; create user student_123 identified by sm03r3br03d default tablespace users quota 20 M on users; Der Account student_123 hat das Passwort sm03r3br03d und kann maximal 20 Megabyte im Tablespace38 users an Informationen ablegen. Allerdings darf er sich zu diesem Zeitpunkt noch nicht mal am Server anmelden. Als nächstes wird deshalb der Rolle student das Recht eingeräumt sich am Server anzumelden (create session) und auch im eigenen Schema Tabellen anzulegen (create table). grant create session, create table to student Diese Rechte werden als Systemprivilegien bezeichnet. Oracle39 verfügt über ca. 140 verschiedenen Systemprivilegien, die eine feingranulare Rechtevergabe erlauben. Die nächste Anweisung im folgenden Codeblock weist dem Account student_123 die Rolle student zu, der damit automatisch alle Rechte der Rolle student erwirbt. Es ist jedoch auch möglich einem User direkt bestimmte Rechte zuzuordnen. grant student to student_123 37 eine detaillierte Vorstellung verschiedener Transaktionskonzepte findet sich in [GR92]. Bei Oracle werden alle Daten innerhalb von Tablespaces abgelegt. Ein Tablespace besteht aus einer oder mehreren Dateien auf Betriebssystemebene. Beim Anlegen eines Tablespaces wird der Platz auf der Festplatte durch die Dateien bereits fest allokiert, so dass er dann ausschließlich dem Datenbanksystem zur Verfügung steht. 39 Version 9i 38 34 1 SQL Um Benutzer und Rollen anzulegen, muss man entweder die Rolle dba besitzen, oder die entsprechenden Systemprivilegien (create role, create user) besitzen. Alle Tabellen und Datensätze die ein User anlegt, befinden sich in seinem privaten Schema, auf das andere Benutzer keinen Zugriff haben. Der User ist aber in der Lage, anderen Benutzern oder auch Rollen bestimmte Rechte auf seinen Daten einzuräumen. Diese Rechte werden als Objektprivilegien bezeichnet. So kann beispielsweise ein Benutzer der Rolle Student das Recht geben, die Daten in seiner Tabelle city zu lesen (Objektprivileg select) und neue Datensätze einzufügen (Objektprivileg insert). grant select, insert on city to student Es ist auch jederzeit mittels des revoke Befehls möglich, Rechte wieder zu entfernen. Um beispielsweise der Rolle student das Recht Datensätze einzufügen wieder zu nehmen, schreibt man einfach: revoke insert on city from student Analog können auch die Systemprivilegien widerrufen werden. Nachdem einem anderen User oder einer Rolle das Recht zugebilligt wurde auf die Daten einer Tabelle in einem fremden Schema zuzugreifen, bleibt nur noch die Frage zu klären, wie der Zugriff auf die fremden Daten erfolgt. Dies geschieht, indem dem Tabellennamen, der Name des Users dem die Tabelle gehört, vorangestellt wird. So erfolgt der lesende Zugriff auf die Tabelle city des Users mondial so: select * from mondial.city where einwohner < 20000 1.9 Übungsaufgaben 1. 2. 3. 4. 5. 6. 7. 8. Welche Städte liegen auf dem Äquator? Welche Organisationen haben ihren Sitz in Wien? Welche Hauptstädte sind zugleich Sitz von Organisationen? Welche Organisationen haben ihren Sitz in Asien (Ausgabe nach Name sortiert)? Wie heißt die größte Insel? Gib alle Länder ohne Berge aus. Gib die Namen aller Hauptstädte, sortiert nach Name aus. Gib die Namen und Einwohnerzahl aller Hauptstäde, absteigend sortiert nach Einwohnerzahl 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 aus 17.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 die Mondial-Datenbank40 von natürlichen Primärschlüsseln nach künstlichen Primärschlüsseln. 1.10 Frei verfügbare Software Es gibt eine Reihe von frei verfügbaren relationalen Datenbanken, Entwicklungswerkzeuge und Frontends dazu. Vor allem PostgreSQL und MySQL haben inzwischen eine große Verbreitung gefunden und sind mit kommerziellen Systemen durchaus vergleichbar. Da aber gerade im Umfeld der Frontends und Werkzeuge immer neue Produkte erscheinen und alte nicht weiter unterstützt werden, wird unter [Smi05] eine aktuell gehaltene Ü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 for Practioners. O’Reilly, 2005. [DR90] M. Dürr und K. Radermacher: Einsatz von Datenbanksystemen. Berlin, Heidelberg: Springer-Verlag, 1990. 40 am Beispiel der Tabellen country und city 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 Kaufmann, 1992 [GUW99] H. Garcia-Molina, J.D. Ullman, J.D. Widom: Database System Implementation, Prentice 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 Troubleshooting Techniques; 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 F LORID: The M ONDIAL Case Study, Universität Freiburg, Institut für Informatik, 1999 http://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.
