19.1 Einleitung

Für Relationale Datenbanksysteme hat sich schon vor vielen Jahren eine Abfragesprache durchgesetzt, die Structured Query Language (SQL). Ein Grund ist, dass sie mehr ist als nur eine Abfragesprache. Sie ist eine umfassende befehlsorientierte formale Sprache für die Einrichtung und den Betrieb Relationaler Datenbanken.

SQL ist befehlsorientiert, d.h. der Nutzer gibt mittels reservierter Wörter in einer bestimmten Syntax die Befehle ein. Einige Datenbanksysteme wie ACCESS bieten menügesteuerte Oberflächen an, durch die SQL-Befehle erzeugt werden.

Befehlsorientiert

SQL bezieht sich ganz auf Relationen, wie sie in Relationalen Datenbanken definiert sind. D.h., SQL verarbeitet nur Relationen und gibt, wenn es um Daten geht, nur Relationen oder Tabellen aus. Es ist deshalb auch attributbasiert im Sinne der obigen Kapitel: Attribute werden angelegt, Attributsausprägungen gespeichert, Abfragen erfolgen mit Hilfe von Attributsausprägungen.

Relationen, nichts als Relationen

SQL ist im Kernbereich standardisiert, trotzdem gibt es kleine Unterschiede zwischen den verschiedenen Datenbanksystemen. Es könnte somit sein, dass beim Nachvollzug der Beispiele kleine Anpassungen notwendig sind. Dabei hilft das Handbuch oder auch die Hilfefunktion des jeweiligen Datenbanksystems.

Anmerkung: Eine ausführlichere Darstellung von SQL findet sich auf www.staud.info ==> DATENBANKEN ==> SQL

Beschäftigt man sich mit SQL, stößt man auf verschiedene Bezeichnungen:

Standards

  • ANSI SQL. ANSI bedeutet American National Standards Institute. Dieses hat im Jahr 1986 zum ersten Mal einen Standard zu SQL veröffentlicht (SQL86), der 1989 (SQL89) und 1992 (SQL92) erneuert wurde. Die meisten Hersteller von Datenbanksystemen orientieren sich heute an ANSI SQL92.
  • Im Jahr 1999 wurde SQL99 (von ANSI und ISO1) vorgelegt.
  • Einzelne Datenbanksysteme erstellten für ihre SQL-Variante Ergänzungen für den Einsatz in Programmen. Bei Oracle ist dies PL/SQL (procedural language extension to SQL).
  • Einzelne Datenbanksystemhersteller, z.B. die Oracle Inc., nutzen eine Obermenge von SQL99 in ihrer SQL-Version.

Dieser Text ist am ANSI-Standard orientiert.

SQL ist die am weitesten verbreitete Sprache zur Definition und Manipulation von Daten in Datenbanken. Sie gehört zu den sog. Definitions- und Beschreibungssprachen (DDL, Data Definition Language), die Datenbanksysteme zur Verfügung stellen. Um die Datenbank mit Inhalten zu füllen, die dann auch abgefragt und ausgewertet werden können, bieten die Datenbanksysteme wiederum spezielle Datenmanipulationssprachen (DML, Data Manipulation Language) an.

Für eine formale Sprache wie SQL gibt es eine Syntax, das sind Regeln für die Bildung korrekter Befehle. Hier die wichtigsten:

Syntax von SQL

  • Jede SQL-Anweisung beginnt mit einem Befehlsnamen (der auch aus mehreren Wörtern bestehen kann), dem verschiedene Parameter und Eingabewerte folgen.
  • SQL unterscheidet bei den Befehlswörtern, den Relationenbezeichnungen und den Attributs-/Feldbezeichnungen keine Groß- und Kleinschreibung, bzw. setzt diese Eingaben in Kleinbuchstaben um.
  • Jede Befehlseingabe muss durch einen Strichpunkt abgeschlossen werden.
  • Kommentare bei der Eingabe von SQL-Befehlen werden zwischen die Zeichen /* und */ gesetzt.
  • Alphanumerische Eingaben in die Datenbank müssen in Hochkommata gesetzt werden, numerische Eingaben nicht.
  • Natürlich muss, wie bei den meisten formalen Sprachen, zwischen den reservierten Wörtern (mindestens) ein Leerzeichen gesetzt werden.

Reservierte Wörter: Wörter der formalen Sprache. Befehls-, Parameterbezeichnungen, usw. Sie heißen reserviert, weil sie nicht außerhalb der Befehlseingabe benutzt werden dürfen. Z.B. darf keine Relation TABLE genannt werden. Ebenso darf kein Attribut MIN (für Minimum) angelegt werden, wenn es eine Funktion MIN gibt.

Nachvollziehbares Beispiel

Diese Kurzeinführung in SQL erfolgt hauptsächlich entlang eines Beispiels, mit dem alle für das Einrichten und Abfragen einer Datenbank notwendigen Befehle vorgesellt werden. Das Beispiel kann Schritt für Schritt mit einem Datenbanksystem nachvollzogen werden. Es ist eine leicht vereinfachte Variante des aus den Anfangskapiteln bekannten Datenmodells (Markt für) Datenbanksysteme. Aus didaktischen und darstellungstechnischen Gründen wurden einige Attribute weggelassen, einige hinzugefügt und Bezeichnungen von Attributen verkürzt.

Schritt um Schritt

Die folgende Abbildung zeigt die grafische Fassung des Datenmodells. Es liegen eine n:m-Beziehung zwischen DBS und Haendler und eine 1:n-Beziehung zwischen DBS und Produzenten vor.


Abbildung 19.1-1:

Datenmodell (Markt für) Datenbanksysteme

Hier noch die textlichen Notationen:

Produzenten (#PName, Stadt, Web)

Diese Relation hält einige wichtige Informationen zu den Produzenten von Datenbanksystemen fest. Das Attribut Web soll die Webadresse des Unternehmens erfassen. PName ist der Produzentenname, Stadt der Name des Ortes, in dem der Hauptsitz des Unternehmens liegt.

Produzenten von Datenbanksystemen

DBS (#BezDBS, Typ, Plattform, LPreis, PrName, Datum)

Wie aus den Anfangskapiteln bekannt, beschreibt sie die auf dem Markt befindlichen Datenbanksysteme. LPreis steht für Listenpreis, PrName für den Namen des Produzenten des Datenbanksystems.

Datenbanksysteme

Angebot (#(BezDBS, FiName), MPreis)

Die Relation hält fest, welches Datenbanksystem von welchem Händler zu welchem Preis angeboten wird. MPreis bedeutet Marktpreis, es meint also den Preis, den der jeweilige Händler für das Datenbanksystem verlangt. BezDBS erfasst die Bezeichnung des Datenbanksystems. Dieses Attribut ist hier Fremdschlüssel. FiName bedeutet Firmenname des Händlers. Auch dieses Attribut ist hier Fremdschlüssel. Die beiden Attribute zusammen stellen die n:m-Verknüpfung zwischen Datenbanksystemen und Händlern dar.

Haendler (#FName, Ort, Straße, Rabatte)

Die Relation beschreibt abstrahiert die Händler, die auf dem Markt Datenbanksysteme wie Oracle, ACCESS, usw. anbieten. FName bedeutet Firmenname, die Bezeichnung des Unternehmens.

Hier nun noch eine letzte wichtige Anmerkung vor dem Praxisteil:

"Case sensitive" oder nicht

Wenn ein Softwaresystem Kleinbuchstaben und Großbuchstaben unterscheidet, nennt man es case sensitiv. Dann sind also PName und pname zwei unterschiedliche Bezeichner. SQL ist bezüglich der reservierten Wörter (Befehlsnamen, Parameter, usw.) und der Relationen- sowie Attributbezeichnungen NICHT case sensitiv.

Bezüglich der Eingaben (Attributsausprägungen) in die Felder ist SQL aber natürlich in vollem Umfang case sensitive. Diese werden bzgl. "klein und groß" genau so abgespeichert, wie sie eingegeben werden.

Für alle reservierten Wörter gilt: SQL ist nicht "case sensitive". Die hier gewählte Großschreibung einzelner reservierten Wörter erfolgt nur aus darstellungstechnischen Gründen.

19.2 Datenbanken anlegen und löschen

Alle folgenden SQL-Beispiele (Eingaben, Ausgaben, Masken) wurden, wenn nicht anders vermerkt, mit MySQL 8.0.2 im Frühjahr 2021 erstellt.

Mit XAMPP steht phpMyAdmin für die Arbeit mit MySQL 8.0.2 zur Verfügung. PhpMyAdmin stellt für den Umgang mit der Datenbank auch eine menübasiertes Interface zur Verfügung. Dieses wird hier nicht genutzt, sondern SQL, welches ganz und gar auf geschriebenen Befehlseingaben beruht.

Anlegen

Vor dem Anlegen der Relationen muss die Datenbank eingerichtet werden. Dies geschieht mit dem Befehl create database. Zwei Parameter sind nötig. Der erste:

Default character set

Damit wird der Zeichensatz festgelegt. Hier wurde latin1 gewählt. Der zweite Parameter ist:

collate

Dieser legt die Sortierung des Zeichensatzes fest. Der hier gewählte Parameter ist

Latin1_german1_ci

Vgl. zu den Zeichensätzen und ihrer Sortierung Kapitel 18. Die folgende Abbildung gibt den hier genutzten Befehl an.


Abbildung 19.2-1:

SQL-Befehl CREATE DATABASE

Falls Sie die Web- oder PDF-Version nutzen, können Sie die Eingabe hier direkt übernehmen:

create database Datenbanksysteme

default character set latin1

collate latin1_german1_ci;

Gelöscht wird eine Datenbank mit dem Befehl drop database und der nachfolgenden Nennung der Datenbankbezeichnung. Hier wäre dies:

Löschen einer Datenbank

drop database datenbanksysteme;

Danach taucht die Datenbank in der Liste am linken Bildschirmrand auf:


Abbildung 19.2-2:

Datenbankliste bei phpMyAdmin / mySQL

Gelöscht wird eine Datenbank mit dem Befehl drop database und der nachfolgenden Nennung der Datenbankbezeichnung. Hier wäre dies:

Löschen einer Datenbank

drop database datenbanksysteme;

19.3 Relationen anlegen und löschen

Der Befehl für das Einrichten neuer Relationen ist CREATE TABLE. Er muss als erstes angegeben werden. Danach folgt die Bezeichnung der Relation. Diese muss von allen in der Datenbank existierenden Bezeichnungen verschieden sein. Anschließend wird - in Klammern - die Liste der Attribute eingefügt. Innerhalb dieser Liste muss nach jedem Attribut ein Datentyp angegeben werden. Die hier verwendeten Datentypen von MySQL wurden in Abschnitt 18.2 vorgestellt. Zusätzlich können im CREATE TABLE-Befehl die Schlüssel, semantischen Integritätsbedingungen (hier unten: NOT NULL) sowie Voreinstellungen (default-Werte) eingegeben werden. Vgl. dazu die Beispiele unten.

Die Parameter sind also:

  • Bezeichnung der einzurichtenen Relation
  • Liste der Attribute mit zugehörigem Datentyp. Die einzelnen Attribute mit zugehörigem Datentyp sind durch Kommata getrennt. Zwischen Attributsnamen und Datentypnamen steht nur (mindestens) ein Leerzeichen.

Nochmals der Befehlsaufbau. Zuerst der Befehlsname:

create table

Danach die Bezeichnung der neuen Relation. Diese muss von allen in der Datenbank existierenden Relationennamen verschieden sein:

create table relationenname

Danach folgt - in Klammern - die Liste der Attribute.

create table relationenname (Attributliste mit Datentypen)

In der Attributliste besteht jeder Eintrag aus der Angabe eines Attributs (attr) und des zugehörigen Datentyps (dt):

create table relationenname (attr1 dt1, attr2 dt2, ..., attrn dtn)

Die Attribute können in beliebiger Reihenfolge angegeben werden, allerdings legt die beim Create Table-Befehl gewählte Reihenfolge fest, wie die Standardausgabe in Tabellenform später aussieht.

Reihenfolge

Es gibt auch die Möglichkeit, von einer existierenden Relation aus direkt mit dem CREATE-Befehl eine neue zu erstellen. Ein Beispiel hierzu ist in Abschnitt 19.8 („create table as“) angegeben.

Alternative Einrichtung

Im Folgenden die Befehle für die Erzeugung der vier Relationen. Beim Nachvollzug muss wegen der Fremdschlüssel die hier angegebene Reihenfolge der create-Befehle beachtet werden, da es sonst zu Fehlermeldungen kommt. Mehr dazu unten.

Hier der Befehl für die Erzeugung der Relation Produzenten. Die Datentypen wurden in Abschnitt 18.2 vorgestellt.

Relation Produzenten


Abbildung 19.3-1:

SQL-Befehl CREATE TABLE Produzenten

Create table produzenten (pname char(5), stadt char(11), web varchar(10) not null, primary key (pname));

Selbstverständlich kann jeder Befehl auch in einer Zeile stehen, die Umbrüche sind darstellungstechnisch bedingt. Die Liste der Attribute und zugehörigen Datentypen ist in Klammern gefasst und das Trennzeichen innerhalb der Liste ist ein Komma, wie immer bei SQL. Mit not null wird festgelegt, dass das Feld web ein Pflichtfeld ist, also bei jedem Eintrag eines Datensatzes beschrieben werden muss.

Der Primärschlüssel wird durch primary key (pname) angelegt. Für das Datenbanksystem bedeutet dies, dass bei jeder Eingabe auf Eindeutigkeit geachtet wird. Grundsätzlich können mehrere Schlüssel angegeben werden. Bei den dann entstehenden Sekundärschlüsseln (secondary key) achtet das Datenbanksystem dann ebenfalls auf Eindeutigkeit.

Primärschlüssel und Sekundärschlüssel

Die Relation DBS wird mit dem folgenden Befehl eingerichtet.

Relation DBS


Abbildung 19.3-2:

SQL-Befehl CREATE TABLE DBS

create table dbs (bezdbs char(10), typ varchar(10) not null, plattform varchar(10) default 'UNIX', lpreis decimal(7,2), pname char(5) references produzenten (pname), datum date, primary key (bezdbs));

Durch primary key (bezdbs) wird der Schlüssel der Relation festgelegt. Not null legt wiederum fest, dass in das Feld Typ bei jeder Tupeleingabe ein Eintrag verlangt wird.

primary key
not null

Durch pname char(5) references produzenten (pname) wird pname zum Fremdschlüssel in der 1:n-Beziehung zwischen DBS und Produzenten. Wegen dieser Fremdschlüsselbeziehung muss die Relation Produzenten vor DBS eingerichtet werden. Natürlich müssen zugehörige Fremdschlüssel (hier: pname in DBS) und Schlüssel (hier: pname in Produzenten) denselben Datentyp und denselben Aufbau haben.

Fremdschlüssel

"references"

Mit references und der nachfolgend angegebenen Relation wird über eine Schlüssel / Fremdschlüsselbeziehung die Verknüpfung zweier Relationen eingerichtet. Sie geht vom Fremdschlüssel der einen Relation aus und zielt auf den Primärschlüssel der anderen.

Mit default ‚UNIX’ wird bei diesem Attribut festgelegt, dass bei jedem neuen Tupel als Voreinstellung UNIX eingetragen wird. Dies bedeutet: Wird ein Tupel eingegeben, ohne das Feld plattform zu beschreiben, trägt das Datenbanksystem UNIX ein. Not null führt wiederum zu einem Pflichtfeld.

Voreinstellung

Für das Einrichten der Relation Haendler ist dieser Befehl nötig:

Relation Haendler


Abbildung 19.3-3:

SQL-Befehl CREATE TABLE Haendler

create table haendler (fname char(5), ort char(10), strasse char(14), rabatt decimal(2,0), primary key(fname));

Der Primärschlüssel dieser Relation bildet zusammen mit dem von DBS den Schlüssel von Angebot (nächster Abschnitt). Deshalb muss sein Aufbau (Datentyp character, Länge 5) auch im Fremdschlüssel vorliegen.

Die Relation Angebot bringt etwas Neues, einen zusammengesetzten Schlüssel und zwei Fremdschlüssel.

Relation Angebot


Abbildung 19.3-4:

SQL-Befehl CREATE TABLE Angebot

Zum Kopieren in der Web- und PDF-Version:

create table angebot (bezdbs char(10), finame char(5), mpreis decimal(8,2), primary key (bezdbs, finame), foreign key (bezdbs) references dbs(bezdbs), foreign key (finame) references haendler(fname));

Hinweis: Bezeichnungen von relational verknüpften Schlüsseln und Fremdschlüsseln müssen nicht gleich sein, vgl. hier haendler.fname und angebot.finame. Sie müssen aber denselben Datentyp mit derselben Länge aufweisen.

Mit primary key (bezdbs, fname) wird der zusammengesetzte Schlüssel angegeben. Die Kombination der zwei Einträge in die Schlüssel muss also immer eindeutig sein. Mit den foreign key - Parametern werden bezdbs und fname jeweils als Fremdschlüssel angelegt. Dabei muss die "Zielrelation" angegeben werden ("references") und natürlich vorhanden, d.h. vorher angelegt worden sein.

Zuerst die Relation mit dem Schlüssel, dann die mit den Fremdschlüsseln

Die Relation Angebot ist eine Verbindungsrelation für die Relationen DBS und Haendler. Sie enthält somit einen zusammengesetzten Schlüssel, bei dem jedes Schlüsselattribut Fremdschlüssel ist.

Die erfolgreiche Einrichtung der Relation zeigt sich auch in der Liste auf der linken Menüseite:

Hat's geklappt?



Ob auch der Aufbau der Relation korrekt erfolgte sieht man, wenn man – nach Anwählen der Relation - den Button Struktur drückt. Dies ist kein SQL-Befehl, sondern einer der MySQL-Arbeitsumgebung. Hier die Ausgaben für die vier Relationen:

Relation DBS



Relation Produzenten



Relation Haendler



Relation Angebot


Damit sind alle vier Relationen des kleinen Datenmodells eingerichtet. Neben der Einrichtung der Relationen mit ihren Attributen und Datentypen war auch das Anlegen der relationalen Verknüpfungen zu leisten. Es wurde also der Schritt vom Datenmodell zur Datenbank getan:

Vom Datenmodell zur Datenbank

Aus dem Datenmodell wurde die Datenbank, aus jeder Relation eine Datei, aus Attributen der Relationen wurden Felder der Datensätze (darunter Schlüssel und Fremdschlüssel), die relationalen Verknüpfungen wurden datenbanktechnisch angelegt.

Hin und wieder muss auch in dieser Phase des Anlegens der Datenbank eine Relation gelöscht werden, z.B. weil bei der Einrichtung ein Fehler passiert ist. Dies geschieht mit dem Befehl DROP TABLE. Die Relation ist dann ganz weg, nicht nur die Daten(!). Also zum Beispiel:

Relationen löschen

drop table dbs;

Bitte nicht verwechseln mit dem Befehl delete, der weiter unten vorgestellt wird. Er löscht die Daten, lässt aber die Relation weiter bestehen.

Sollen Relationen gelöscht werden, müssen, wie beim Einrichten der Relationen, die Schlüssel-/Fremdschlüsselbeziehungen beachtet werden. Bei jeder relationalen Verknüpfung muss zuerst die Relation mit dem Fremdschlüssel ("references") gelöscht werden.

19.4 Eingeben von Daten

Mit dem Befehl insert into werden Daten eingegeben. Bei jeder Eingabe wird ein Datensatz angelegt, der ganz oder teilweise beschreiben wird. Der Aufbau des Befehls ist wie folgt:

insert into


Abbildung 19.4-1:

SQL-Befehl INSERT INTO am Beispiel

Die eingegebenen Daten:

insert into produzenten values ('P3', 'Berlin', 'www.p3.com');

insert into produzenten values ('P1', 'Hamburg', 'www.p1.de');

insert into produzenten values ('P2', 'Frankfurt', 'www.p2.de');

insert into produzenten values ('P4', 'Rom', 'www.p4.it');

insert into produzenten values ('P5', 'Passau', 'www.p5.info');

Die Felderliste ist hier nicht nötig, weil alle Felder in der vorgegebenen Reihenfolge beschrieben werden.

Die Bedeutung der Parameter ist wie folgt:

  • Felderliste: Liste der Felder, die befüllt werden sollen. Diese kann weggelassen werden, falls alle Felder der Relation befüllt werden und falls die Einträge in der beim Create-Befehl angegebenen Reihenfolge angegeben werden.
  • Eingabewerte: Durch Kommata getrennte Liste der Einträge, die zu tätigen sind.

Hier hätte die Felderliste weggelassen werden können.

Damit schon jetzt, vor Erlernen des Select-Befehls (dem Befehl für die Abfrage von Daten) abgefragt werden kann, ob die Insert-Befehle funktioniert haben, hier die einfachste Form dieses Befehls: select * from relationenname. Mit ihm sieht man einfach alle Datensätze der Relation. Der Befehl wird gleich unten ausführlich besprochen.

Hier ein Beispiel:

select * from produzenten;


pname

stadt

web

P1

Hamburg

www.p1.de

P2

Frankfurt

www.p2.de

P3

Berlin

www.p3.com

P4

Rom

www.p4.it

P5

Passau

www.p5.inf


Die Spaltenüberschriften werden durch SQL grundsätzlich von den Attributsbezeichnungen abgeleitet. Unten wird gezeigt, wie eigene Spaltenüberschriften vergeben werden können.

Spaltenüberschriften

Folgendes ist bei den Insert-Befehlen noch zu beachten:

  • Bei der Eingabe von Dezimalzahlen muss immer der Punkt verwendet werden. Also 120.75 und nicht 120,75.
  • Die Datumseingabe ist meist in verschiedenen Formaten möglich. Z.B. geht "2.1.2009" oder auch "2.1.09".
  • Bei der Eingabe von Schlüsseln und Fremdschlüsseln muss genauso wie oben bei der Einrichtung der Relationen eine Reihenfolge beachtet werden. Die Einträge in die Fremdschlüssel, z.B. hier PName in DBS, können erst erfolgen, wenn die zugehörigen Schlüsselwerte, hier PName in Produzenten, vorhanden sind.

Bei Schlüssel- / Fremdschlüsselbeziehungen muss zuerst der Schlüssel eingegeben werden. Erst dann kann in der anderen Relation der Fremdschlüsseleintrag erfolgen. Der Grund ist einfach der, dass vom Fremdschlüssel auf den Schlüssel verwiesen wird.

Eingabe der Tupel in die Relation DBS

Das erste Beispiel zeigt die Eingabe ohne Felderliste. Sie ist möglich, weil alle Felder in der vorgegebenen Reihenfolge beschrieben werden:

insert into dbs

values ('DBS1', 'RDBS', 'UNIX', 9500.10, 'P1', '11.7.2019');

Mit Felderliste ergibt sich folgender Befehl:

insert into dbs

(bezdbs, typ, pname, datum)

values ('DBS12', 'RDBS', 'P1', '2019-05-11');

Für unser Beispiel wurden folgende Daten in Kurzform eingegeben. Dabei muss der erste Teil des Befehls (bis VALUES) nur einmal eingegeben werden, danach können alle „Datenklammern“ – durch Kommata getrennt – jeweils nacheinander angegeben werden:

insert into dbs (bezdbs, typ, plattform, lpreis, pname, datum) values

('DBS1', 'RDBS', 'UNIX', 9500.10, 'P1', '2019-07-20'),

('DBS2', 'RDBS', 'UNIX', 3000.20, 'P1', '2020-10-20'),

('DBS9', 'NoSQL', 'LINUX', 3000.20, 'P1', '2022-01-01'),

('DBS8', 'RDBS', 'LINUX', 1200.30, 'P2', '2021-08-20'),

('DBS7', 'OODBS', 'UNIX', 5000.40, 'P3', '2015-07-20'),

('DBS6', 'OODBS', 'WINDOWS', 1800.55, 'P2', '2022-06-20'),

('DBS5', 'OODBS', 'LINUX', 900.66, 'P1', '2021-04-20'),

('DBS4', 'NoSQL', 'WINDOWS', 2000.77, 'P4', '2019-02-20'),

('DBS3', 'RDBS', 'LINUX', 2301.00, 'P2', '2022-02-26'),

('DBS10', 'RDBS', 'WINDOWS', 600.88, 'P4', '2021-03-20'),

('DBS11', 'RDBS', 'WINDOWS', 800.90, 'P5', '2020-11-20');

Dies führt zu folgender Ausgabe mit dem Befehl Select:

select * from dbs;


bezdbs

typ

plattform

lpreis

pname

datum

DBS1

RDBS

UNIX

9500.10

P1

2019-07-20

DBS10

RDBS

WINDOWS

600.88

P4

2021-03-20

DBS11

RDBS

WINDOWS

800.90

P5

2020-11-20

DBS2

RDBS

UNIX

3000.20

P1

2020-10-20

DBS3

RDBS

LINUX

2301.00

P2

2022-02-26

DBS4

NoSQL

WINDOWS

2000.77

P4

2019-02-20

DBS5

OODBS

LINUX

900.66

P1

2021-04-20

DBS6

OODBS

WINDOWS

1800.55

P2

2022-06-20

DBS7

OODBS

UNIX

5000.40

P3

2015-07-20

DBS8

RDBS

LINUX

1200.30

P2

2021-08-20

DBS9

NoSQL

LINUX

3000.20

P1

2022-01-01


Der Fremdschlüssel ist immer zu befüllen ("referentielle Integrität") und sein Wert muss in der verknüpften Relation (hier: Produzenten) vorhanden sein.

Für die Relation Haendler wurden, wiederum in Kurzform, folgende Daten eingegeben:

Haendler

insert into haendler (fname, ort, strasse, rabatt) VALUES

('HAE1', 'Frankfurt', 'Hammerweg 5', 10),

('HAE5', 'Ravensburg', 'Hammerstraße 3', 20),

('HAE2', 'München', 'Hummelweg 99', 6),

('HAE3', 'Passau', 'Langstr. 1', 12),

('HAE6', 'Vilshofen', 'An der Donau 7', 5),

('HAE4', 'Stuttgart', 'Bahnhofstr. 20', 8);

Dies führt zu folgender Relation:

select * from haendler


fname

ort

strasse

rabatt

HAE1

Frankfurt

Hammerweg 5

10

HAE2

München

Hummelweg 99

6

HAE3

Passau

Langstr. 1

12

HAE4

Stuttgart

Bahnhofstr. 20

8

HAE5

Ravensburg

Hammerstraße 3

20

HAE6

Vilshofen

An der Donau 7

5


Selbstverständlich muss ein Schlüssel immer befüllt werden (Objektintegrität) und dies mit unterschiedlichen Einträgen.

Angebot

Etwas spannender gestaltet sich die Eingabe der Datensätze in die Relation Angebot. Hier liegt ein zusammengesetzter Schlüssel vor, bei dem jedes Schlüsselattribut Fremdschlüssel ist: (BezDBS, FName). BezDBS verknüpft mit der Relation DBS, FName verknüpft mit Haendler. Bei jeder Eingabe eines Datensatzes muss der Schlüssel vollständig sein. Das entspricht der Forderung nach der Objektintegrität. Vgl. dazu Abschnitt 5.9. Hier zwei Eingabebeispiele, eines mit Felderliste, eines ohne:

insert into angebot (bezdbs, finame)

values ('DBS3', 'HAE3');

insert into angebot

values ('DBS11', 'HAE4', 500.00);

Folgende Daten wurden in die Relation eingegeben:

Insert into angebot (bezdbs, finame, mpreis) values ('DBS3', 'HAE3', NULL),

('DBS11', 'HAE4', 500.00),

('DBS9', 'HAE1', 2200.50),

('DBS9', 'HAE2', 2500.00),

('DBS8', 'HAE5', 1000.00),

('DBS9', 'HAE4', 3150.00),

('DBS7', 'HAE4', 4500.00),

('DBS6', 'HAE1', 1700.50),

('DBS4', 'HAE2', 1800.00),

('DBS5', 'HAE3', NULL),

('DBS1', 'HAE3', 7100.00),

('DBS8', 'HAE6', 1150.00),

('DBS8', 'HAE2', 1140.00);

Dies führte zu folgender Relation:

select * from angebot


bezdbs

finame

mpreis

DBS1

HAE3

7100.00

DBS11

HAE4

500.00

DBS3

HAE3

NULL

DBS4

HAE2

1800.00

DBS5

HAE3

NULL

DBS6

HAE1

1700.50

DBS7

HAE4

4500.00

DBS8

HAE2

1140.00

DBS8

HAE5

1000.00

DBS8

HAE6

1150.00

DBS9

HAE1

2200.50

DBS9

HAE2

2500.00

DBS9

HAE4

3150.00

Hier nochmals der Hinweis: Durch die Fremdschlüsselbeziehungen lässt jedes Datenbanksystem hier bei den Fremdschlüsseln (BezDBS, FiName) nur Einträge zu, die in den referenzierten Relationen bereits vorliegen. Somit gilt: Zuerst die jeweiligen Schlüssel befüllen (in DBS bzw. Haendler), dann die zugehörigen Fremdschlüssel.

19.5 Abfragen der Daten mit Select

Grundform

Der in einer einfachen Form schon eingeführte Befehl SELECT dient der Abfrage der Datenbank, von einer Relation oder mehrerer. In diesem Kapitel betrachten wir die Abfrage einzelner Relationen. Die Grundform dieses Befehls ist in der folgenden Abbildung angegeben.


Abbildung 19.5-1:

SQL-Befehl SELECT - Grundform

Die Bedeutung der Parameter ist wie folgt:

  • Attributsliste: Diese gibt an, welche Attribute der Relation ausgegeben werden sollen ("Projektion", vgl. dazu den nächsten Abschnitt). Wird hier - wie oben - ein Stern angegeben, werden alle Attribute ausgegeben und zwar in der Reihenfolge wie sie in der Relation von links nach rechts angeordnet sind.
  • Das reservierte Wort FROM zusammen mit einem Relationennamen legt fest, welche Relation abgefragt wird.
  • Where-Klausel: Nach dem Relationennamen kann eine sog. Where-Klausel kommen, die durch das reservierte Wort WHERE eingeleitet wird. Mit ihr werden Tupel / Datensätze ausgewählt („Selektion“, vgl. unten).

Bei fast jeder Abfrage wünscht man nicht die Ausgabe aller Attribute einer Relation, sondern nur bestimmter. Diese Auswahl von Attributen wird Projektion genannt. Sie wird realisiert, indem beim Select-Befehl nach dem Befehlsnamen nicht ein Stern, sondern die Liste der auszugebenden Attribute angegeben wird. Die einzelnen Einträge in diese Liste müssen, wie in SQL üblich, durch Kommata getrennt werden.

Projektion

Im folgenden Beispiel werden von der Relation DBS nur die Attribute BezDBS und LPreis ausgegeben (über alle Tupel hinweg):

select bezdbs, lpreis from dbs;


bezdbs

lpreis

DBS1

9500.10

DBS10

600.88

DBS11

800.90

DBS2

3000.20

DBS3

2301.00

DBS4

2000.77

DBS5

900.66

DBS6

1800.55

DBS7

5000.40

DBS8

1200.30

DBS9

3000.20


Es ist auch möglich, Spaltenüberschriften zuzuordnen. Dies geschieht, indem nach dem Attributsnamen das Schlüsselwort AS folgt und nach diesem die Spaltenüberschrift. Beispiel:

Spaltenüberschriften durch „AS“

select bezdbs as Datenbank,

typ as DBTyp,

plattform as PLF,

lpreis as Listenpreis

from dbs;


Datenbank

DBTyp

PLF

Listenpreis

DBS1

RDBS

UNIX

9500.10

DBS10

RDBS

WINDOWS

600.88

DBS11

RDBS

WINDOWS

800.90

DBS2

RDBS

UNIX

3000.20

DBS3

RDBS

LINUX

2301.00

DBS4

NoSQL

WINDOWS

2000.77

DBS5

OODBS

LINUX

900.66

DBS6

OODBS

WINDOWS

1800.55

DBS7

OODBS

UNIX

5000.40

DBS8

RDBS

LINUX

1200.30

DBS9

NoSQL

LINUX

3000.20


Falls die Spaltenüberschrift mehrere Worte umfasst, muss das Anführungszeichen verwendet werden (vgl. unten).

Eine formale Sprache zur Auswertung von relationalen Datenbeständen benötigt eine Vielzahl von Operatoren:

Operatoren

  • Vergleichsoperatoren für den Vergleich von Attributsname und Attributsausprägung. Einer davon ist der Gleichheitsoperator, der oben schon eingeführt wurde.
  • Logische Operatoren für die Bildung von zusammengesetzten Ausdrücken in Where-Klauseln, wie oben am Beispiel AND und OR gezeigt.
  • Mathematische Operatoren für die Verarbeitung der Ausprägungen quantitativer Attribute.

Die folgende Tabelle zeigt, welche Vergleichsoperatoren es gibt, wie die Symbolik ist und gibt Beispiele an.

Vergleichsoperatoren

Vergleichsoperatoren

Operator

Symbol

Beispiel

Gleichheit

=

typ='RDBS'

Kleiner

gehalt < 10000

Größer

gehalt > 1000

Kleiner/gleich

<=

gewicht <= 90

Größer/gleich

>=

gewicht >= 90

Ungleich

 

typ <> 'RDBS'

Negation der Vergleichsoperatoren

!

!=

!<

!>


In SQL werden standardmäßig AND, OR und NOT zur Verfügung gestellt. Mit ihnen werden Ausdrücke (z.B. typ='RDBS') einer Where-Klausel miteinander kombiniert. Die Tabelle zeigt einige Beispiele.

Logische Operatoren

Logische Operatoren

Bezeichnung

Kurz -
bezeichnung

Beispiel

Logisches Und

AND

...where typ=’RDBS’ AND lpreis < 5000

Logisches Oder

OR

...where typ=’RDBS’ OR typ='OODBS'

Verneinung, Negation

NOT

...where NOT typ=’RDBS’


Schwierigkeiten macht oft die Negation. Der Operator NOT muss vor dem Ausdruck stehen. Zahlreiche Beispiele zu den logischen Operatoren folgen unten.

Mathematische Operatoren werden für Berechnungen benötigt, die mit den Attributausprägungen quantitativer Attribute möglich sind.

Mathematische Operatoren

Operator, Symbol und Beispiel

 

Symbol

Beispiel

Addition

+

lpreis + 10

Subtraktion

-

lpreis - 10

Division

/

lpreis/100

Multiplikation

*

(lpreis/100)*19

Potenzierung

**, ^

Anzahl**2


Selektion mit der Where-Klausel

Genauso wie man selten alle Attribute einer Relation ausgeben möchte, sollen meist auch nicht alle Tupel ausgegeben werden. Deshalb kann auch hier eine Auswahl erfolgen, die Selektion genannt wird. Sie erfolgt mit einem Bedingungsteil, der sog. Where-Klausel, die festlegt, welche Tupel gemeint sind. Wie oben schon gezeigt, wird die Where-Klausel nach der Angabe der Relation angefügt.

Die folgende Abbildung zeigt den grundsätzlichen Aufbau. Durch die Gleichsetzung von Attributsname (in der Abbildung attr1 und attr2) und Attributsausprägun­gen (in der Abbildung auspr) werden Tupelmengen festgelegt.


Abbildung 19.5-2:

Aufbau einer Where-Klausel

Z.B. werden durch typ=’RDBS’ die Tupel angefordert, die diesen Eintrag haben, was bedeutet, dass sie Datenbanksysteme des Typs "relational" erfassen. Hier die konkrete Umsetzung mit unseren Daten.

select * from dbs where typ='RDBS';


bezdbs

typ

plattform

lpreis

pname

datum

DBS1

RDBS

UNIX

9500.10

P1

2019-07-20

DBS10

RDBS

WINDOWS

600.88

P4

2021-03-20

DBS11

RDBS

WINDOWS

800.90

P5

2020-11-20

DBS2

RDBS

UNIX

3000.20

P1

2020-10-20

DBS3

RDBS

LINUX

2301.00

P2

2022-02-26

DBS8

RDBS

LINUX

1200.30

P2

2021-08-20


Ist ein zweiter Ausdruck vorhanden, kommt es auf den Operator an. Wenn ein UND-Operator vorliegt, müssen die Tupel der Zielmenge beide Bedingungen erfüllen.

...where typ='RDBS' AND lpreis<2000

AND: Schnittmenge

Hier erhält man also alle relationalen Datenbanksysteme, deren Listenpreis unter 2000 Euro liegt. Entsprechend beim logischen ODER: Die Tupel der Zielmenge erfüllen die eine oder die andere Bedingung. Im obigen Beispiel werden durch

...where typ='RDBS' OR typ='OODBS'

OR: Vereinigungsmenge

alle Datenbanksysteme ausgewählt, die entweder relational oder objektorientiert sind. Natürlich sind auch Where-Klauseln mit mehr als zwei Ausdrücken denkbar. Vgl. hierzu die folgenden Beispiele.

Die folgende Abbildung fasst an einem Beispiel die Aussagen zur Syntax des SQL-Befehls zusammen.


Abbildung 19.5-3:

Befehl SELECT mit Projektion und Selektion

Beispiele von SELECT-Abfragen

Hier nun einige Beispiele. Zuerst sollen alle Tupel aus der Relation Angebote aufgelistet werden, die sich auf DBS5 und DBS2 beziehen:

select * from angebot

where bezdbs='DBS8' or bezdbs='DBS9';


bezdbs

finame

mpreis

DBS8

HAE2

1140.00

DBS8

HAE5

1000.00

DBS8

HAE6

1150.00

DBS9

HAE1

2200.50

DBS9

HAE2

2500.00

DBS9

HAE4

3150.00


Nun alle Tupel aus DBS, die Relationale Datenbanksysteme mit einem Listenpreis kleiner 9000 Euro beschreiben:

select * from dbs

where typ='RDBS' AND lpreis < 2000;


bezdbs

typ

plattform

lpreis

pname

datum

DBS10

RDBS

WINDOWS

600.88

P4

2021-03-20

DBS11

RDBS

WINDOWS

800.90

P5

2020-11-20

DBS8

RDBS

LINUX

1200.30

P2

2021-08-20


Mit Hilfe des NOT-Operators erhalten wir beim nächsten Befehl alle Tupel, die sich nicht auf RDBS beziehen.

select * from dbs

where NOT typ='RDBS';


bezdbs

typ

plattform

lpreis

pname

datum

DBS4

NoSQL

WINDOWS

2000.77

P4

2019-02-20

DBS5

OODBS

LINUX

900.66

P1

2021-04-20

DBS6

OODBS

WINDOWS

1800.55

P2

2022-06-20

DBS7

OODBS

UNIX

5000.40

P3

2015-07-20

DBS9

NoSQL

LINUX

3000.20

P1

2022-01-01

Der NOT-Operator muss VOR dem Ausdruck eingefügt werden. Falls mehrere Ausdrücke negiert werden sollen, werden diese in Klammern gefasst und der NOT-Operator vor diese gesetzt (vgl. unten).

Nun eine Where-Klausel mit verschachtelten Ausdrücken. Es gilt die übliche Syntax: SQL beginnt die Abarbeitung mit der innersten Klammer und geht dann weiter nach "außen". Hier werden somit zuerst die Tupel bestimmt, die entweder Relationale Datenbanksysteme (RDBS) beschreiben oder die als Plattform LINUX aufweisen. Dann wird die Komplementärmenge davon ausgewählt.

select * from dbs

where NOT (typ='RDBS' or plattform='LINUX');


bezdbs

typ

plattform

lpreis

pname

datum

DBS4

NoSQL

WINDOWS

2000.77

P4

2019-02-20

DBS6

OODBS

WINDOWS

1800.55

P2

2022-06-20

DBS7

OODBS

UNIX

5000.40

P3

2015-07-20


Der folgende Befehl gibt alle Tupel zu relationalen oder objektorientierten Datenbanksystemen aus, die entweder unter Linux oder UNIX betrieben werden können. Die Klammern sind hier wichtig. Durch sie werden erst die beiden Vereinigungsmengen gebildet und dann mittels UND die Schnittmenge.

Where-Klausel mit AND und OR

select * from dbs

where (typ='RDBS' or typ='OODBS')

and (plattform='LINUX' or plattform='UNIX');


bezdbs

typ

plattform

lpreis

pname

datum

DBS1

RDBS

UNIX

9500.10

P1

2019-07-20

DBS2

RDBS

UNIX

3000.20

P1

2020-10-20

DBS3

RDBS

LINUX

2301.00

P2

2022-02-26

DBS5

OODBS

LINUX

900.66

P1

2021-04-20

DBS7

OODBS

UNIX

5000.40

P3

2015-07-20

DBS8

RDBS

LINUX

1200.30

P2

2021-08-20


Will man abfragen, welche Leereinträge bezüglich eines Attributs vorliegen, muss man dies ebenfalls mit der Where-Klausel und mit der Bedingung IS NULL tun. Hier eine Abfrage, die klärt, welche Tupel in Angebot einen Leereintrag beim Marktpreis (mpreis) haben:

Leereinträge

select * from angebot where mpreis is null;


bezdbs

finame

mpreis

DBS3

HAE3

NULL

DBS5

HAE3

NULL


Ein Datenbanksystem verwaltet Leereinträge explizit als solche mit dem Eintrag null value. Es ist also ein Unterschied, ob in einem Feld ein Leerzeichen oder ein null value steht bzw. - bei numerischen Datentypen - eine Null oder ein null value. Deshalb liefern folgende Abfragen nicht die Tupel mit Leereinträgen:

select * from dbs where typ='';

select * from dbs where typ=' ';

Und eine Abfrage wie ...

select * from angestellte where gehalt = 0;

liefert diejenigen, deren Gehalt auf Null steht, nicht die ohne Eintrag.

Spaltenüberschriften

SQL erlaubt auch die Vergabe von Spaltenüberschriften. Vergibt man nur ein Wort, muss man lediglich daran denken, keine Sonderzeichen zu verwenden, z.B. keine Bindestriche. Möchte man mehr als nur ein Wort verwenden, muss man Anführungszeichen benutzen. Im folgenden zwei Beispiele. Zuerst mit einem Wort je Spaltenüberschrift:

select bezdbs as DBSBezeichnung,

typ as DBSTyp,

lpreis as Listenpreis from dbs;


DBSBezeichnung

DBSTyp

Listenpreis

DBS1

RDBS

9500.10

DBS10

RDBS

600.88

DBS11

RDBS

800.90

DBS2

RDBS

3000.20

DBS3

RDBS

2301.00

DBS4

NoSQL

2000.77

DBS5

OODBS

900.66

DBS6

OODBS

1800.55

DBS7

OODBS

5000.40

DBS8

RDBS

1200.30

DBS9

NoSQL

3000.20


Dann mit mehreren Wörtern und mit Anführungszeichen:

select bezdbs as "Name des DBS",

typ as "Typ des DBS",

lpreis as "Listenpreis des Produzenten" from dbs;


Name des DBS

Typ des DBS

Listenpreis des Produzenten

DBS1

RDBS

9500.10

DBS10

RDBS

600.88

DBS11

RDBS

800.90

DBS2

RDBS

3000.20

DBS3

RDBS

2301.00

DBS4

NoSQL

2000.77

DBS5

OODBS

900.66

DBS6

OODBS

1800.55

DBS7

OODBS

5000.40

DBS8

RDBS

1200.30

DBS9

NoSQL

3000.20


Relationen haben per Definition keine gleichen Tupel. Trotzdem geschieht es natürlich im Rahmen einer Abfrage, dass gleiche Tupel entstehen, wie auch das folgende Beispiel zeigt. In einem solchen Fall unterdrückt der Parameter DISTINCT die Ausgabe identischer Tupel.

Unterdrückung gleicher Tupel

Folgende Anfrage könnte dem Beispiel zugrunde liegen: Welche Datenbanktypen gibt es in unserer Datenbank zum Markt für Datenbanksysteme?

select typ

as "Typen von Datenbanksystemen" from dbs;


Typen von Datenbanksystemen

RDBS

RDBS

RDBS

RDBS

RDBS

NoSQL

OODBS

OODBS

OODBS

RDBS

NoSQL


Auf diese Weise (Abfrage eines Nichtschlüsselattributs; NSA) ergeben sich natürlich gleiche Ausgabetupel. Der Parmeter DISTINCT bereinigt dies. Zu beachten ist seine Position vor der Attributliste.

Nur verschiedene

select distinct

typ as "Typen von Datenbanksystemen" from dbs;


Typen von Datenbanksystemen

RDBS

NoSQL

OODBS


Dies funktioniert auch bei mehr als einem Attribut. Im Folgenden möchten wir wissen, welche Datenbanksystemtypen auf welchen Plattformen in unserer Relation DBS erfasst sind.

select typ, plattform from dbs;


typ

plattform

RDBS

UNIX

RDBS

WINDOWS

RDBS

WINDOWS

RDBS

UNIX

RDBS

LINUX

NoSQL

WINDOWS

OODBS

LINUX

OODBS

WINDOWS

OODBS

UNIX

RDBS

LINUX

NoSQL

LINUX


Auch diese Abfrage führt zu gleichen Tupeln, die wiederum durch distinct unterdrückt werden können.

select distinct

typ as Typ,

plattform as Plattform from dbs;


Typ

Plattform

RDBS

UNIX

RDBS

WINDOWS

RDBS

LINUX

NoSQL

WINDOWS

OODBS

LINUX

OODBS

WINDOWS

OODBS

UNIX

NoSQL

LINUX


Die Varianten in der Anforderung der Spaltenüberschriften deuten die möglichen Alternativen an.

Geordnete Ausgabe

Oftmals möchte man die Tupel einer Relation geordnet nach den Ausprägungen eines Attributs ausgeben (oder mehrerer). Dies geschieht ganz einfach, indem man den Parameter order by an den Befehl anhängt. Die Sortierung kann aufsteigend (ASCending) oder absteigend (DESCending) erfolgen. Voreinstellung ist aufsteigend. Die folgende Abfrage der Relation DBS sortiert nach der Datenbankbezeichnung:

select distinct typ from dbs

order by typ ASC;


typ

NoSQL

OODBS

RDBS


Hier die absteigende Sortierung:

select distinct typ as Datenbanksystemtyp

from dbs

order by typ DESC;


Datenbanksystemtyp

RDBS

OODBS

NoSQL


Das folgende Beispiel zeigt alle Tupel der Relation Angebot, sortiert nach der Datenbankbezeichnung.

select bezdbs as Datenbanksystem,

fname as "Name der Firma",

mpreis as Marktpreis

from angebot order by bezdbs;


Datenbanksystem

Name der Firma

Marktpreis

DBS1

HAE3

7100.00

DBS11

HAE4

500.00

DBS3

HAE3

NULL

DBS4

HAE2

1800.00

DBS5

HAE3

NULL

DBS6

HAE1

1700.50

DBS7

HAE4

4500.00

DBS8

HAE2

1140.00

DBS8

HAE5

1000.00

DBS8

HAE6

1150.00

DBS9

HAE1

2200.50

DBS9

HAE2

2500.00

DBS9

HAE4

3150.00


Hier eine Sortierung nach zwei Attributen. Der Unterschied wird bei den Datenbanksystemen DBS8 und DBS9 deutlich.

select bezdbs as Datenbanksystem,

finame as "Name der Firma",

mpreis as Marktpreis

from angebot order by bezdbs asc, finame desc


Datenbanksystem

Name der Firma

Marktpreis

DBS1

HAE3

7100.00

DBS11

HAE4

500.00

DBS3

HAE3

NULL

DBS4

HAE2

1800.00

DBS5

HAE3

NULL

DBS6

HAE1

1700.50

DBS7

HAE4

4500.00

DBS8

HAE6

1150.00

DBS8

HAE5

1000.00

DBS8

HAE2

1140.00

DBS9

HAE4

3150.00

DBS9

HAE2

2500.00

DBS9

HAE1

2200.50


Es ist also auch möglich, mit dem einen Attribut aufsteigend und dem anderen absteigend zu sortieren.

Eine oft benötigte Eigenschaft von SQL ist, dass berechnete Werte direkt bei der Festlegung der auszugebenden Attribute (Spalten) angegeben werden können. In der folgenden Abfrage wird zum Listenpreis gleich noch der um 19% erhöhte Preis mitausgegeben, indem einfach in die Attributliste der entsprechende algebraische Ausdruck mit aufgenommen wird (lpreis*1.19). Außerdem sind hier auch die Parameter NOT, IS NULL und order by mit eingebaut:

Algebraische Ausdrücke in den Spalten

select bezdbs as Datenbankystem,

lpreis as Listenpreis,

lpreis*1.19 as "Listenpreis mit MWSt." from dbs

where not lpreis is null

order by lpreis;


Datenbankystem

Listenpreis

Listenpreis mit MWSt.

DBS10

600.88

715.0472

DBS11

800.90

953.0710

DBS5

900.66

1071.7854

DBS8

1200.30

1428.3570

DBS6

1800.55

2142.6545

DBS4

2000.77

2380.9163

DBS3

2301.00

2738.1900

DBS9

3000.20

3570.2380

DBS2

3000.20

3570.2380

DBS7

5000.40

5950.4760

DBS1

9500.10

11305.1190


Diese Werte werden nur für die Ausgabe berechnet, sie werden nicht abgespeichert. Falls man keine Spaltenüberschrift festlegt, nimmt das System dafür den algebraischen Ausdruck. Selbstverständlich können algebraische Ausdrücke auch bei der Auswahl der Tupel, also in der Where-Klausel, verwendet werden, wie das folgende Beispiel zeigt.

select bezdbs as Datenbanksysteme, lpreis as "Preis",

lpreis*1.19 as "Preis mit MWSt"

from dbs

where LPREIS*1.19 > 3000 order by lpreis desc;


Datenbanksysteme

Preis

Preis mit MWSt

DBS1

9500.10

11305.1190

DBS7

5000.40

5950.4760

DBS2

3000.20

3570.2380

DBS9

3000.20

3570.2380


Parameter Between

Angenommen es liegt ein Attribut vor, dessen Ausprägungen eine Rangordnung aufweisen, also z.B. Preis, Gehalt (beide quantitativ) oder Noten (rangskaliert). Dann kann man mit einem solchen Attribut Tupel anfordern, deren Attributausprägung über und unter einer festgelegten Grenze liegen. Z.B. so:

... lpreis > 2000 and lpreis < 4000 ...

In SQL können stattdessen die Schlüsselworte BETWEEN ...AND ... verwendet werden. Hier als Beispiel die Abfrage nach Datenbanksystemen, deren Preis zwischen 2000 und 5000 Euro liegen:

select bezdbs as Datenbanksystem, typ as Datenbanksystemtyp, lpreis as Listenpreis

from dbs

where lpreis BETWEEN 2000 AND 5000

order by lpreis;


Datenbanksystem

Datenbanksystemtyp

Listenpreis

DBS4

NoSQL

2000.77

DBS3

RDBS

2301.00

DBS9

NoSQL

3000.20

DBS2

RDBS

3000.20


Parameter IN

Oftmals ergeben sich bei Abfragen logische Ausdrücke der Art

.. typ='RDB' OR typ='IR' or typ='OODBS' or ..,

wenn Tupelmengen ausgegeben werden sollen, die auf andere Weise nicht spezifiziert werden können (hier am Beispiel der Relation DBS). Auch dies kann in SQL auf einfachere Weise erledigt werden, indem das reservierte Wort IN zusammen mit einer in Klammern gesetzten Liste von Attributsausprägungen als Spezifizierungsbedingung eingesetzt wird. Im nächsten Beispiel werden z.B. alle Angebote ausgegeben, bei denen eines der angeführten Datenbanksysteme vorkommt.

select * from angebot

where bezdbs IN ('DBS5', 'DBS3', 'DBS1', 'DBS11')

and not mpreis is null order by bezdbs;


bezdbs

finame

mpreis

DBS1

HAE3

7100.00

DBS11

HAE4

500.00


Soweit die Select-Abfragen in dieser einfachen Form. Weiter unten werden sie nochmals für die Verknüpfung von Relationen benötigt.

Select-Abfragen erzeugen Tabellen (oftmals erfüllen diese auch die Anforderungen an Relationen) nur für die Ausgabe oder für die Eingabe in ein weiterverarbeitendes Programm (Reportgenerator, Grafische Nutzeroberfläche). Sie erzeuen keine persistenten Daten.

Persistenz bedeutet im Datenbankgeschehen, dass Daten abgespeichert und über die Zeit gerettet werden. Dies geschieht mit den "normalen" Relationen (base relations), nicht aber mit diesen Auswertungen.

19.6 Gezieltes Löschen und Korrigieren

Die nächsten Beispiele werden mit der Datenbank KuchenBaecker durchgeführt. Im folgenden Kasten sind die Befehle zum Einrichten und Bestücken der Datenbank.

create database KuchenBaecker; //nur beim ersten Mal

drop table baeku; //nur bei Wiederholung

drop table baecker; //nur bei Wiederholung

drop table kuchen; //nur bei Wiederholung

create table kuchen (KNr int(2), BEZ char(15), KalStueck int(4), preis decimal(4,2), primary key (KNr));

create table Baecker (BNr int(2), Name char(15), primary key (BNr));

create table BaeKu (BNr int(2), KNr int(2), preis decimal(4,2), primary key(BNr, KNr));

 

delete from kuchen; //nur bei Wiederholung

insert into kuchen (Knr, Bez, preis) values (1, 'Obstschnitte', 1.70);

insert into kuchen values

(2, 'Apfelkuchen', 650, 1.70),

(3, 'Kirschkuchen', 720, 2.10),

(4, 'Kirschtorte', 720, 2.52),

(5, 'Mohnkuchen', 920, 2.38),

(6, 'Sahnetorte', 1300, 2.46);

 

delete from baecker; //nur bei Wiederholung

Insert into baecker values (10, 'Müller');

Insert into baecker values

(20, 'Huber'),

(30, 'Gonzales'),

(40, 'Mertens');

 

delete from baeku; //nur bei Wiederholung

Insert into baeku values

(20, 1, 1.50),

(40, 1, 1.60),

(10, 2, 2.00),

(20, 2, 1.90),

(30, 2, 2.10),

(40, 2, 2.20),

(30, 3, 2.70),

(40, 4, 3.10),

(40, 5, 3.00),

(20, 5, 4.00),

(30, 5, 5.00),

(40, 6, 4.00);

Hier die befüllten Relationen:

select * from kuchen


KNr

BEZ

KalStueck

preis

1

Obstschnitte

NULL

1.70

2

Apfelkuchen

650

1.70

3

Kirschkuchen

720

2.10

4

Kirschtorte

720

2.52

5

Mohnkuchen

920

2.38

6

Sahnetorte

1300

2.46

select * from baeku

BNr

KNr

preis

10

2

2.00

20

1

1.50

20

2

1.90

20

5

4.00

30

2

2.10

30

3

2.70

30

5

5.00

40

1

1.60

40

2

2.20

40

4

3.10

40

5

3.00

40

6

4.00

select * from baecker

BNr

Name

10

Müller

20

Huber

30

Gonzales

40

Mertens

19.6.1 Löschen mit DELETE FROM

Nicht verwechseln: drop table und delete from table

Der Befehl für das Löschen von Daten ist delete from. Nach der Befehlsbezeichnung wird noch der Relationennamen angegeben, also delete from relationenname. Damit werden alle Tupel der Relation gelöscht. Die Relation selbst bleibt erhalten. Normalerweise löscht man aber nicht alle Tupel, sondern nur ausgewählte. Deren Festlegung kann wie bei den Select-Befehlen mit einer Where-Klausel erfolgen. Als Beispiel dient die Relation Kuchen:

select bez as Bezeichnung,

kalstueck as "Kalorien je Stück",

preis as Preis from kuchen;


Bezeichnung

Kalorien je Stück

Preis

Obstschnitte

NULL

1.70

Apfelkuchen

650

1.70

Kirschkuchen

720

2.10

Kirschtorte

720

2.52

Mohnkuchen

920

2.38

Sahnetorte

1300

2.46


Da so etwas in unser Angebot nicht mehr passt, löschen wir alle Kuchen mit mehr als 800 Kilokalorien je Stück aus unserem Angebot:

delete from kuchen where kalstueck > 800;

select bez as Bezeichnung,

kalstueck as "Kalorien je Stück",

preis as Preis

from kuchen;


Bezeichnung

Kalorien je Stück

Preis

Obstschnitte

NULL

1.70

Apfelkuchen

650

1.70

Kirschkuchen

720

2.10

Kirschtorte

720

2.52


Und die „Kalorienbomben“ sind weg.

19.6.2 Daten korrigieren - Update ... set ...

Hier sollen nun zum ersten Mal in unserer "SQL-Exkursion" Daten geändert werden. Dies geschieht durch den Befehl UPDATE. Die Auswahl der zu ändernden Tupel geschieht wiederum durch eine Where-Klausel. Der Aufbau des Befehls ist in der folgenden Abbildung angegeben.


Abbildung 19.6-1:

Befehl UPDATE

attr: Attributsbezeichnung, attrauspr: Attributsausprägung

Das Befehlswort SET leitet den Abschnitt für die Festlegung der Datenänderung ein. Diese beginnt, indem dem Attribut eine neue Ausprägung zugewiesen wird. Also z.B. gehalt=10000, wenn das neue Gehalt diesen Wert hat oder Name = "Bergbauer", falls eine Namensänderung ansteht. Danach folgt die Where-Klausel, mit deren Hilfe festgelegt wird, welche Tupel geändert werden. Sie kann natürlich wieder beliebig komplex sein. Hier einige einfache Beispiele für den Abschnitt nach dem Befehlswort SET:

... set gehalt=10000 where PersNr=1007

... set Name = "Bergbauer" where Name="Müller"

... set gehalt = gehalt * 0.2 where abteilung = "IT"

Der letzte Befehl erhöht die Gehälter aller in der IT Beschäftigten um 20%.

Der folgende Befehl führt in der Datenbank Datenbanksysteme zwei Änderungen in der Relation DBS durch: DBS3 erhält einen neuen Listenpreis und eine neue Datumsangabe. Zuerst der alte Datensatz:

Beispiel

select * from dbs where bezdbs='DBS3';


bezdbs

typ

plattform

lpreis

pname

datum

DBS3

RDBS

LINUX

2301.00

P2

2022-02-26


Nun die Änderung der Daten. Dafür sind zwei Befehle nötig.

update dbs SET lpreis=2300 where bezdbs='DBS3';

update dbs SET datum='2019-02-27' where bezdbs='DBS3';

select * from dbs where bezdbs='DBS3';


bezdbs

typ

plattform

lpreis

pname

datum

DBS3

RDBS

LINUX

2300.00

P2

2019-02-27


Es ist auch möglich, in einem Befehl mehrere Änderungen vorzunehmen:

update dbs SET lpreis=2301, datum='2025-02-27'

where bezdbs='DBS3';

select * from dbs where bezdbs='DBS3';


bezdbs

typ

plattform

lpreis

pname

datum

DBS3

RDBS

LINUX

2301.00

P2

2025-02-27


Je nach Gestaltung der Where-Klausel werden auch mehrere Datensätze (Tupel) geändert. Im folgenden Befehl werden alle Preise in der Relation Kuchen um 20% erhöht. Zuerst der alte Datenbestand:

Alle auf einmal

select * from kuchen;


KNr

BEZ

KalStueck

preis

1

Obstschnitte

NULL

1.70

2

Apfelkuchen

650

1.70

3

Kirschkuchen

720

2.10

4

Kirschtorte

720

2.52


Nun die Preiserhöhung:

update kuchen set preis=preis*1.20;

select * from kuchen;


KNr

BEZ

KalStueck

preis

1

Obstschnitte

NULL

2.04

2

Apfelkuchen

650

2.04

3

Kirschkuchen

720

2.52

4

Kirschtorte

720

3.02

19.6.3 Maskierung mit LIKE

Da wir es bei den Einträgen in die Datenbank oft mit alphanumerischen Zeichenfolgen zu tun haben, ist auch Maskierung notwendig. Damit ist gemeint, dass man nur einen Teil einer alphanumerischen Zeichenfolge (einer Attributsausprägung) eingibt und sich die Tupel ausgeben lässt, die im entsprechenden Attribut diesen Teil aufweisen. Die dafür verwendeten Zeichen werden auch Platzhalter, Stellvertreterzeichen oder Joker genannt. In SQL sind folgende üblich:

Maskierung mit Platzhalter, Joker

  • Der Unterstrich ("_") für ein beliebiges einzelnes Zeichen
  • Das Prozentsymbol ("%") für keines oder eine beliebige Anzahl von Zeichen

Die Syntax ist so, dass das Schlüsselwort LIKE anstatt des Gleichheitszeichens eingesetzt wird, also z.B. ...

... name LIKE "Integer%"

falls nach allen Integer-Datentypen gesucht wird.

Für die folgenden Beispiele nutzen wir die Relation Haendler. Hier nochmals die Daten dieser Relation:

select * from haendler order by fname;


fname

ort

strasse

rabatt

HAE1

Frankfurt

Hammerweg 5

10

HAE2

München

Hummelweg 99

6

HAE3

Passau

Langstr. 1

12

HAE4

Stuttgart

Bahnhofstr. 20

8

HAE5

Ravensburg

Hammerstraße 3

20

HAE6

Vilshofen

An der Donau 7

5


Zuerst eine rechtsseitige Maskierung. Sie fordert alle Datensätze an, bei denen Hammer am Beginn des Feldes steht und beliebiges nachfolgt.

Hammer%

select * from haendler

where strasse like 'Hammer%';


fname

ort

strasse

rabatt

HAE1

Frankfurt

Hammerweg 5

10

HAE5

Ravensburg

Hammerstraße 3

20


Dann eine beidseitige Maskierung. Gesucht sind alle Händler, in deren Straßenbezeichnung „weg“ vorkommt.

%weg%

select * from haendler

where strasse like '%weg%' order by fname;


fname

ort

strasse

rabatt

HAE1

Frankfurt

Hammerweg 5

10

HAE2

München

Hummelweg 99

6


Vor der nächsten Abfrage tragen wir einen weiteren Händler ein:

insert into haendler values ('HAE9', 'Ostrach', 'Hummerweg 2', 5);

Damit sind folgende Daten in der Relation Haendler:

select * from haendler;


fname

ort

strasse

rabatt

HAE1

Frankfurt

Hammerweg 5

10

HAE2

München

Hummelweg 99

6

HAE3

Passau

Langstr. 1

12

HAE4

Stuttgart

Bahnhofstr. 20

8

HAE5

Ravensburg

Hammerstraße 3

20

HAE6

Vilshofen

An der Donau 7

5

HAE9

Ostrach

Hummerweg 2

5


Nun das zweite Maskierungszeichen, der Unterstrich. Er ersetzt genau ein Zeichen. Die rechte Maskierung ist wegen der Hausnummern nötig:

H_mmerweg%

select * from haendler

where strasse like 'H_mmerweg%' order by fname;


fname

ort

strasse

rabatt

HAE1

Frankfurt

Hammerweg 5

10

HAE9

Ostrach

Hummerweg 2

5


Das Feld Strasse hat die Länge 14, das ist genau die von Hammerstraße 3. Am Ende dieses Eintrags findet sich also kein Zeichen. Die folgende Abfrage dient nur dem Nachweis, dass das Prozentzeichen tatsächlich auch "kein Zeichen” umfasst:

Hammerstraße 3%

select * from haendler

where strasse like 'Hammerstraße 3%';


fname

ort

strasse

rabatt

HAE5

Ravensburg

Hammerstraße 3

20


Diese Unterscheidung (Unterstrich genau ein Zeichen, Prozentzeichen keines, eines oder viele) ist in der praktischen Arbeit wichtig. Sie erlaubt es, die Suche zu präzisieren.

19.7 Funktionen

Hier arbeiten wir wieder mit der Relation DBS von Datenbanksysteme:

select * from dbs


bezdbs

typ

plattform

lpreis

pname

datum

DBS1

RDBS

UNIX

9500.10

P1

2019-07-20

DBS10

RDBS

WINDOWS

600.88

P4

2021-03-20

DBS11

RDBS

WINDOWS

800.90

P5

2020-11-20

DBS2

RDBS

UNIX

3000.20

P1

2020-10-20

DBS3

RDBS

LINUX

2301.00

P2

2025-02-27

DBS4

NoSQL

WINDOWS

2000.77

P4

2019-02-20

DBS5

OODBS

LINUX

900.66

P1

2021-04-20

DBS6

OODBS

WINDOWS

1800.55

P2

2022-06-20

DBS7

OODBS

UNIX

5000.40

P3

2015-07-20

DBS8

RDBS

LINUX

1200.30

P2

2021-08-20

DBS9

NoSQL

LINUX

3000.20

P1

2022-01-01

19.7.1 Funktionen für Tupelmengen und Gruppenbildung

SQL bietet auch Funktionen zur Verarbeitung von Tupelmengen an. Natürlich können sich diese Verarbeitungsschritte nur auf numerische Attribute beziehen, die rechnerisch verarbeitet werden dürfen.

  • Summe: SUM(attributsname) berechnet die Summe über angegebenen die Attributsausprägungen
  • Durchschnitt: AVG(attributsname) berechnet den Durchschnitt
  • Minimum: MIN(attributsname) bestimmt den minimalen Wert
  • Maximum: MAX(attributsname) bestimmt den maximalen Wert
  • Anzahl: COUNT(*) bestimmt die Anzahl der Tupel in der Menge

Will man alle Tupel der Berechnung unterziehen, erscheint im Ergebnis genau ein Tupel:

Alle Tupel

select avg(lpreis) as "Durchschnittspreis",

max(lpreis) as "Höchster Preis",

min(lpreis) as "Niedrigster Preis",

count(*) as "Anzahl" from dbs;


Durchschnittspreis

Höchster Preis

Niedrigster Preis

Anzahl

2736.905455

9500.10

600.88

11


Sehr oft will man diese Werte aber für Teilmengen aller Tupel berechnen. Dann legt man nicht nur die gewünschten Funktionen fest, sondern auch die Tupelmengen.

Zur Festlegung der Tupelmengen und dann zur Ausgabe der berechneten Werte, muss man mit Hilfe des Parameters GROUP BY gruppieren. Der Aufbau ist so, dass vor dem Relationennamen in der Liste der auszugebenden Attribute die Funktion angegeben wird und danach das Schlüsselwort GROUP BY mit dem Attribut, nach dem gruppiert werden soll.

Mengenbildung durch GROUP BY

...funktion(attribut)

from relationenname GROUP BY attrbez

Dann werden jeweils die Tupel mit derselben Attributsausprägung in eine Gruppe genommen und die Funktion berechnet.

Im folgenden Beispiel werden für die Relation DBS alle Datenbanksysteme, die zum gleichen (Datenbanksystem)Typ gehören, zusammengefasst und mit ihrem durchschnittlichen Preis ausgeben. Mit count(*) kann man ausrechnen lassen, wieviele Tupel in der jeweiligen Tupelmenge sind. Der Stern bei der Funktion count ersetzt ein bei der Gruppenbildung verwendetes Attribut.

Beispiel mit count und avg

select typ as Typ,

count(*) as Anzahl,

avg(lpreis) as Durchschnittspreis

from dbs GROUP BY TYP;


Typ

Anzahl

Durchschnittspreis

NoSQL

2

2500.485000

OODBS

3

2567.203333

RDBS

6

2900.563333


Die vielen Dezimalstellen bei dem berechneten Wert können durch den Parameter format unterbunden werden:

 

select typ as Typ,

count(*) as Anzahl,

format(avg(lpreis),2) as Durchschnittspreis

from dbs GROUP BY TYP


Typ

Anzahl

Durchschnittspreis

NoSQL

2

2,500.49

OODBS

3

2,567.20

RDBS

6

2,900.56


Das folgende Beispiel ist etwas anspruchsvoller. Wieder wird nach dem Datenbanksystemtyp gruppiert und mit count(*) die Anzahl der Tupel in der jeweiligen Menge festgestellt. Außerdem wird für jede Teilmenge dann bestimmt, wie hoch der Durchschnittspreis ist, welchen Preis das teuerste und das billigste Buch hat.

Beispiel mit avg, max, min und count

select typ as "DBS-Typ",

format(avg(lpreis),2) as "Durchschnittspreis",

max(lpreis) as "Höchster Preis",

min(lpreis) as "Niedrigster Preis",

count(*) as "Anzahl" from dbs

GROUP BY TYP order by typ desc;


DBS-Typ

Durchschnittspreis

Höchster Preis

Niedrigster Preis

Anzahl

RDBS

2900.56

9500.10

600.88

6

OODBS

2567.20

5000.40

900.66

3

NoSQL

2500.48

3000.20

2000.77

2

Wichtig ist, dass bei der Festlegung der Spalten nur Attribute angegeben werden, die sich auf die Mengen als Ganzes beziehen. Es können hier also keine Attribute wie z.B. BezDBS angegeben werden, die zur Ausgabe von Werten für einzelne Tupel führen würden.

Hat man eine Gruppierung angefordert und möchte unter den entstehenden Tupelmengen auswählen, dann kann dies nicht mit der Where-Klausel geschehen, da diese sich immer auf einzelne Tupel bezieht. Deshalb wird in SQL hierfür ein anderes reserviertes Wort zur Verfügung gestellt, HAVING. Natürlich muss sich dann die notwendige Auswahlbedingung auch auf die Gruppe als Ganzes beziehen.

HAVING.

Die Syntax ist so, dass nach dem üblichen Befehl (bis GROUP BY attr­bez) der Parameter HAVING mit einer Bedingung eingefügt wird. Dann werden nur die Tupelmengen ausgegeben, die diese Bedingung erfüllen. Im folgenden werden z.B. nur die mit ihren Durchschnittspreisen ausgegeben, die mehr als ein Datenbanksystem enthalten.

Beispiel

select typ as "DBS-Typ",

format(avg(lpreis),2) as "Durchschnitt der Gruppe",

count(*) as Anzahl

from dbs

GROUP BY TYP HAVING count(*)>2;


DBS-Typ

Durchschnitt der Gruppe

Anzahl

OODBS

2567.20

3

RDBS

2900.56

6


Es gibt in SQL eine große Zahl von Funktionen zur Manipulation von einzelnen Attributsausprägungen. Diese gruppieren sich in mathematische Funktionen und Funktionen für Zeichenketten.

19.7.2 Mathematische Funktionen

Die wichtigsten mathematischen Funktionen

Bezeichnung

Aufgabe

abs

liefert den Absolutwert der (numerischen) Attributsausprägung

ceil

liefert den nächstgelegenen ganzen Integer-Wert

floor

liefert den größten Integer-Wert zurück, der kleiner oder gleich dem Parameterwert ist

mod

liefert den Modulowert

power

liefert den Exponentialwert

round

liefert den gerundeten Wert

sqrt

liefert die zweite Wurzel


Für diese Funktionen richten wir eine Datenbank mit der Bezeichnung Sport und eine Relation mit der Bezeichnung Fitness ein, die v.a Attribute mit numerischen Datentypen nutzt. Da es sich teilweise um natürliche Zahlen (ganze, positive) handelt, wird auch der Parameter unsigned gesetzt.

Im folgenden Text finden sich die Befehle zum Einrichten und Befüllen der Datenbank.

create database Sport;

//nur bei Wiederholung: drop table fitness;

create table fitness (Name char(10),

MitglNr tinyint (3) unsigned,

AlterM tinyint (2) unsigned,

Gewicht decimal (4,2) unsigned,

Punkte tinyint (2));

//nur bei Wiederholung; delete from fitness;

insert into fitness VALUES

('Rapunzel', 2, 18, 68.8, -10),

('Pfarr', 4, 27, 65.49, -5),

('Stilz', 5, 49, 64.01, 29),

('Rumpel', 6, 33, 63.55, 21),

('Maier', 7, 30, 60.20, -20),

('Metzger', 8, 22, 59.25, 30),

('Dürr', 9, 45, 55.45, 25),

('Groß', 1, 39, 62.10, -10),

('Storch', 10, 27, 45.50, 17),

('Rot', 3, 18, 49.44, 27),

('Schmalz', 11, 64, 62.24, -5);

Dies führt zu folgendem Datenbestand:

select * from fitness;


Name

MitglNr

AlterM

Gewicht

Punkte

Rapunzel

2

18

68.80

-10

Pfarr

4

27

65.49

-5

Stilz

5

49

64.01

29

Rumpel

6

33

63.55

21

Maier

7

30

60.20

-20

Metzger

8

22

59.25

30

Dürr

9

45

55.45

25

Groß

1

39

62.10

-10

Storch

10

27

45.50

17

Rot

3

18

49.44

27

Schmalz

11

64

62.24

-5


Ein Klick auf den MySQL-Button Struktur zeigt, dass es geklappt hat.

Blick ins Data Dictionary



Nun einige Abfragen mit diesen Funktionen. Der nachfolgende Befehl gibt den Wert des Attributs Punkte aus, berechnet deren Absolutwert, die nächste höhere ganze Zahl, die nächst tiefere und den gerundeten Wert.

select MitglNr, punkte as Punkte,

abs(punkte) as "Punkte absolut",

gewicht as Gewicht,

ceil(gewicht) as "Nächster Wert",

floor(gewicht) as "Unterer Wert",

round(gewicht,1) as "Gerundet"

from fitness order by mitglnr;


MitglNr

Punkte

Punkte absolut

Gewicht

Nächster Wert

Unterer Wert

Gerundet

1

-10

10

62.10

63

62

62.1

2

-10

10

68.80

69

68

68.8

3

27

27

49.44

50

49

49.4

4

-5

5

65.49

66

65

65.5

5

29

29

64.01

65

64

64.0

6

21

21

63.55

64

63

63.6

7

-20

20

60.20

61

60

60.2

8

30

30

59.25

60

59

59.3

9

25

25

55.45

56

55

55.5

10

17

17

45.50

46

45

45.5

11

-5

5

62.24

63

62

62.2


Das zweite Beispiel zeigt den Einsatz der Modulo-Funktion.

select mitglnr, mod(mitglnr, 3) from fitness;


mitglnr

mod(mitglnr, 3)

2

2

4

1

5

2

6

0

7

1

8

2

9

0

1

1

10

1

3

0

11

2


Erinnerung: Die Modulofunktion berechnet den ganzzahligen Rest bei der Division durch eine festgelegte ganze Zahl. Damit werden die Tupel in Gruppen eingeteilt. Die mit Rest 0, mit Rest 1, usw. Ein Anwendungsbereich im Zusammenhang mit Datenbanken sind Speichertechniken, vgl. die Ausführungen zur RAM-Datei in Abschnitt 21.5.

Das dritte Beispiel zeigt die Funktionen power und sqrt. Hier ist auch zu sehen, wie MySQL reagiert, wenn man versucht, aus einer negativen Zahl die Wurzel zu ziehen. "Null" meint hier null value (keine zulässige Ausprägung vorhanden) und nicht den Wert 0.

select punkte as Punkte,

power(punkte,2) as Quadrat,

format(sqrt(punkte),3) as Wurzel,

round(sqrt(punkte), 2)

as "Wurzel gerundet" from fitness


Punkte

Quadrat

Wurzel

Wurzel gerundet

-10

100

NULL

NULL

-5

25

NULL

NULL

29

841

5.385

5.39

21

441

4.582

4.58

-20

400

NULL

NULL

30

900

5.477

5.48

25

625

5.000

5.00

-10

100

NULL

NULL

17

289

4.123

4.12

27

729

5.196

5.20

-5

25

NULL

NULL


Auch in der Sortierung können diese Funktionen eingesetzt werden, wie das folgende Beispiel zeigt. Es macht auch deutlich, dass mit der Funktion power ebenfalls Wurzeln gezogen werden können.

select punkte AS Punkte,

format(sqrt(punkte),3) AS Quadratwurzel,

round(power(punkte, 0.5) , 2)

AS "Wurzel gerundet"

from fitness

where punkte > 0 order by sqrt(punkte);


Punkte

Quadratwurzel

Wurzel gerundet

17

4.123

4.12

21

4.582

4.58

25

5

5.00

27

5.196

5.20

29

5.385

5.39

30

5.477

5.48


Das abschließende Beispiel deutet die Möglichkeiten der Verschachtelung an. Die Klammern werden von innen nach außen abgearbeitet und sollten entsprechend gesetzt werden.

select

punkte as Punkte,

abs(punkte) as "Punkte absolut",

function(sqrt(abs(punkte)),3) as Wurzel,

round(sqrt(abs(punkte)), 2) as Gerundet,

ceil(round(sqrt(abs(punkte)), 2))

as "Nächste ganze Zahl",

mod(ceil(round(sqrt(abs(punkte)), 2)), 2)

as "Modulo 2"

from fitness order by abs(punkte);


Punkte

Punkte absolut

Wurzel

Gerundet

Nächste ganze Zahl

Modulo 2

-5

5

2.236

2.24

3

1

-5

5

2.236

2.24

3

1

-10

10

3.162

3.16

4

0

-10

10

3.162

3.16

4

0

17

17

4.123

4.12

5

1

-20

20

4.472

4.47

5

1

21

21

4.582

4.58

5

1

25

25

5

5.00

5

1

27

27

5.196

5.20

6

0

29

29

5.385

5.39

6

0

30

30

5.477

5.48

6

0

19.7.3 Funktionen für Zeichenketten

Die folgende Tabelle gibt die wichtigsten Funktionen für Zeichenketten an.

Funktionen für Zeichenketten

Bezeichnung

Aufgabe

length

liefert die Länge einer Zeichenkette zurück

lower

liefert die in Kleinbuchstaben konvertierte Zeichenfolge zurück

upper

liefert die in Großbuchstaben konvertierten Zeichenfolge zurück

rtrim

(right trim) liefert bei einer Zeichenfolge einen Ausdruck zurück, bei dem rechtsseitig die Leerzeichen gelöscht sind.

In MySQL nicht nötig, da hier keine rechtseitigen Leerzeichen eingefügt werden.

ltrim

(left trim)liefert bei einer Zeichenfolge einen Ausdruck zurück, bei dem linksseitig die Leerzeichen gelöscht sind.

string || string

ist der Verkettungsoperator für Zeichenfolgen nach ANSI-Standard. Funktioniert nicht bei MySQL. Hier ist der Operator concat dafür zuständig.

concat

Verkettungsoperator für Zeichenketten bei MySQL (concatenation).

substr

löscht einen Teil der Zeichenkette


Für die nächsten Beispiele wird weiter die Relation Fitness genutzt. Zuerst der Verkettungsoperar concat. Mit ihm können alphanumerische Zeichenfolgen nebeneinander (in einer Spalte) ausgegeben werden. Die erste Abfrage zeigt, was geschieht, wenn man die Felder einfach nur aneinander fügt:

select concat(name, MitglNr, AlterM, Gewicht) from fitness


concat(name, MitglNr, AlterM, Gewicht)

Rapunzel21868.80

Pfarr42765.49

Stilz54964.01

Rumpel63363.55

Maier73060.20

Metzger82259.25

Dürr94555.45

Groß13962.10

Storch102745.50

Rot31849.44

Schmalz116462.24


Kein sehr befriedigendes Ergebnis. Das geht deutlich besser, auch mit einer aussagekräftien Überschrift:

select concat(name, ' (MitglNr: ', MitglNr, ',

Alter: ', AlterM, ', Gewicht: ', Gewicht,')')

as "Mitglieder mit wichtigen Daten" from fitness


Mitglieder mit wichtigen Daten

Rapunzel (MitglNr: 2, Alter: 18, Gewicht: 68.80)

Pfarr (MitglNr: 4, Alter: 27, Gewicht: 65.49)

Stilz (MitglNr: 5, Alter: 49, Gewicht: 64.01)

Rumpel (MitglNr: 6, Alter: 33, Gewicht: 63.55)

Maier (MitglNr: 7, Alter: 30, Gewicht: 60.20)

Metzger (MitglNr: 8, Alter: 22, Gewicht: 59.25)

Dürr (MitglNr: 9, Alter: 45, Gewicht: 55.45)

Groß (MitglNr: 1, Alter: 39, Gewicht: 62.10)

Storch (MitglNr: 10, Alter: 27, Gewicht: 45.50)

Rot (MitglNr: 3, Alter: 18, Gewicht: 49.44)

Schmalz (MitglNr: 11, Alter: 64, Gewicht: 62.24)


Nun noch ein Beispiel mit der Relation DBS:

select concat(bezdbs, " ist vom Typ: ", typ)

as "Datenbanksysteme und ihr Typ" from dbs;


Datenbanksysteme und ihr Typ

DBS1 ist vom Typ: RDBS

DBS10 ist vom Typ: RDBS

DBS11 ist vom Typ: RDBS

DBS2 ist vom Typ: RDBS

DBS3 ist vom Typ: RDBS

DBS4 ist vom Typ: NoSQL

DBS5 ist vom Typ: OODBS

DBS6 ist vom Typ: OODBS

DBS7 ist vom Typ: OODBS

DBS8 ist vom Typ: RDBS

DBS9 ist vom Typ: NoSQL


Die nächste Abfrage demonstriert die Funktionen rtrim und length. In anderen SQL-Dialekten sind die Werte bei Länge DT und Länge real unterschiedlich. Hier nicht, MySQL verwaltet also tatsächlich keine angehängten Leerzeichen.

select name,

length(name) as "Länge DT",

length(rtrim(name))as "Länge real"

from fitness order by length(rtrim(name));


name

Länge DT

Länge real

Rot

3

3

Pfarr

5

5

Stilz

5

5

Maier

5

5

Dürr

5

5

Groß

5

5

Rumpel

6

6

Storch

6

6

Schmalz

7

7

Metzger

7

7

Rapunzel

8

8


Nun noch, ein zugegebenermaßen konstruiertes Beispiel zu ltrim. Damit prüfen wir, ob der Operator funktioniert.

Wir konstruieren zuerst Einträge mit linksseitigen Leerzeichen. Diese sind, wie Spalte 2 zeigt, dann auch tatsächlich da. Spalte 3 zeigt dann den Einsatz von ltrim, die linksseitigen Leerzeichen sind wieder weg. Ltrim funktioniert also. Dies klappt auch, wenn man Einträge in die Relation mit linksseitigen Lehrzeichen tätigt und diese dann ltrim unterwirft.

select name, concat(' ', name),
ltrim(concat(' ', name)) from fitness


name

concat(' ', name)

ltrim(concat(' ', name))

Rapunzel

Rapunzel

Rapunzel

Pfarr

Pfarr

Pfarr

Stilz

Stilz

Stilz

Rumpel

Rumpel

Rumpel

Maier

Maier

Maier

Metzger

Metzger

Metzger

Dürr

Dürr

Dürr

Groß

Groß

Groß

Storch

Storch

Storch

Rot

Rot

Rot

Schmalz

Schmalz

Schmalz


Die nächste Abfrage zeigt die Funktionen upper, lower und substr, mit der Teile von Zeichenketten ausgewählt werden können. z1b3 liefert die Zeichen 1 bis drei, z4b6 entsprechend die von 4 bis 6.

select lower(name) as LowName,

upper(name) as UpName,

substr(name,1,3) as z1b3,

substr(name,4,6) as z4b6

from fitness;


LowName

UpName

z1b3

z4b6

rapunzel

RAPUNZEL

Rap

unzel

pfarr

PFARR

Pfa

rr

stilz

STILZ

Sti

lz

rumpel

RUMPEL

Rum

pel

maier

MAIER

Mai

er

metzger

METZGER

Met

zger

dürr

DÜRR

Dür

r

groß

GROß

Gro

ß

storch

STORCH

Sto

rch

rot

ROT

Rot

 

schmalz

SCHMALZ

Sch

malz

19.8 Verknüpfen von Relationen


In den obigen Kapiteln wurde deutlich, dass ein relationales Datenmodell aus zahlreichen Relationen besteht und dass deren Zahl durch die Normalisierungsschritte noch vergößert wird. Das ist auch sinnvoll, weil dadurch Redundanzen beseitigt werden.

Verknüpfungen

Im Rahmen der Abfrage und Auswertung der Daten, z.B. für einen Report oder um Daten für eine Maske bereitzustellen, müssen die Daten verschiedener Relationen aber oft wieder zusammengefügt werden. Dies geschieht entlang der relationalen Verknüpfungen des Datenmodells, also entlang der Schlüssel-Fremdschlüssel-Beziehungen. Eine solche Zusammenführung wird Join (Verbund) genannt. Folgende Varianten gibt es üblicherweise in Datenbanksystemen:

  • Equijoin: Den Verbund, der die Ausprägungen des jeweiligen Schlüssels und Fremdschlüssels auf Gleichheit prüft und die Tupel entsprechend zusammenfügt. Er wird Equijoin (natural join, Join ON) genannt.
  • Den Verbund mit anderen Operatoren, z.B. dem Kleiner- oder Größer-Operator. Hier spricht man auch von Non-equijoins.
  • Den sog. Outer-Join, bei dem fehlende Einträge auf Schlüssel- oder Fremdschlüsselseite bewältigt werden (full outer join, left outer join, right outer join).
  • Den Self-join, d.h. einen Verbund einer Relation mit sich selbst.

Alle Joins werden durch die Where-Klausel realisiert. Im Unterschied zur einfachen oben vorgestellten Variante der Where-Klausel für die Auswahl von Tupeln (Attri­butsname=Attributsausprägung) werden hier zwei Attribute gegenübergestellt, typischerweise ein Fremdschlüssel und ein Schlüssel:

Joins durch
Where-Klausel

Attribut_1 op Attribut_2

Das Kürzel op steht für einen geeigneten Operator.

Mehr dazu in den folgenden Abschnitten.

19.8.1 Equjoin - Verbund über die Gleichheit von Attributsausprägungen

Der wichtigste Verbund ist der Equjoin. Er heißt so, weil bei ihm zwischen den Attributen, die der Verknüpfung dienen (typischerweise also Schlüssel und Fremdschlüssel) das Gleichheitszeichen steht. Im folgenden einige Beispiele dazu.

Zuerst ein Verbund entlang einer 1:n-Beziehung. Die Zusammenführung der Tupel mit den entsprechenden Einträgen in den Schlüsseln und Fremdschlüsseln geschieht nun durch einen SELECT-Befehl. Dabei werden in der WHERE-Bedingung zwei Attribute gleichgesetzt, eines aus der einen und eines aus der anderen Relation. Typischerweise sind dies ein Schlüssel und ein Fremdschlüssel.

1:n-Beziehung

Durch die Festlegung (vor dem Punkt der Relationenname, danach die Attributsbezeichnung)

dbs.pname = produzenten.pname

wird SQL z.B. veranlasst, alle Tupel aus den beiden Relationen aneinanderzufügen, bei denen die entsprechenden Attributsausprägungen übereinstimmen. Wird keine Entsprechung gefunden, wird auch nicht zusammengefügt.

Die nächste Abbildung zeigt den entsprechenden Select-Befehl. Zwei Dinge müssen beachtet werden:

  • Dort wo in den obigen Beispielen eine einzige Relation angegeben wurde (... from dbs ...) werden jetzt mehrere durch Komma getrennt angegeben, alle benötigten. Hier erst mal zwei, später mehr.
  • In der Where-Klausel werden je relationaler Verknüpfung zwei Attribute aus verschiedenen Relationen gleichgesetzt.


Abbildung 19.9-1:

Ein Join über eine 1:n-Beziehung

Das Ergebnis:


bezdbs

typ

pname

pname

stadt

DBS1

RDBS

P1

P1

Hamburg

DBS10

RDBS

P4

P4

Rom

DBS11

RDBS

P5

P5

Passau

DBS2

RDBS

P1

P1

Hamburg

DBS3

RDBS

P2

P2

Frankfurt

DBS4

NoSQL

P4

P4

Rom

DBS5

OODBS

P1

P1

Hamburg

DBS6

OODBS

P2

P2

Frankfurt

DBS7

OODBS

P3

P3

Berlin

DBS8

RDBS

P2

P2

Frankfurt

DBS9

NoSQL

P1

P1

Hamburg


Zum Kopieren in der Web- und PDF-Version:

select

dbs.bezdbs, dbs.typ, dbs.pname, produzenten.pname, produzenten.stadt

from dbs, produzenten

where dbs.pname=produzenten.pname;

Hier entstehen - wenn Entsprechungen in den Ausprägungen der Verknüpfungsattribute gefunden werden - unter Umständen große Ausgabetabellen. Die Verknüpfung geschieht im Übrigen nur temporär, nur für die Ausgabe der Tabelle. Die hier vorliegende Redundanz, wegen der ja ursprünglich zerlegt wurde, ist daran ersichtlich, dass die Angabe der Stadt bei jeder Nennung eines Produzenten erfolgt.

Temporäre, u.U. große Tabellen

Im Folgenden sollen nun entlang der n:m-Beziehung zwischen DBS - Angebot - Haendler die Relationen wieder zusammengefügt werden. Um dies zu erreichen, werden in der Where-Klausel zwei Verknüpfungen durchgeführt und mit dem Operator AND verbunden. Damit die Ausgabetabelle nicht zu breit wird, werden bestimmte Attribute ausgewählt.

n:m-Beziehung


Abbildung 19.9-2:

Ein Join über eine n:m-Beziehung

Das Ergebnis:


DB-Bez

Listenpreis

DB-A

H-A

Marktpreis

H-Ort

DBS1

9500.10

DBS1

HAE3

7100.00

Passau

DBS11

800.90

DBS11

HAE4

500.00

Stuttgart

DBS3

2301.00

DBS3

HAE3

NULL

Passau

DBS4

2000.77

DBS4

HAE2

1800.00

München

DBS5

900.66

DBS5

HAE3

NULL

Passau

DBS6

1800.55

DBS6

HAE1

1700.50

Frankfurt

DBS7

5000.40

DBS7

HAE4

4500.00

Stuttgart

DBS8

1200.30

DBS8

HAE2

1140.00

München

DBS8

1200.30

DBS8

HAE5

1000.00

Ravensburg

DBS8

1200.30

DBS8

HAE6

1150.00

Vilshofen

DBS9

3000.20

DBS9

HAE1

2200.50

Frankfurt

DBS9

3000.20

DBS9

HAE2

2500.00

München

DBS9

3000.20

DBS9

HAE4

3150.00

Stuttgart


Zum Kopieren in der Web- und PDF-Version (hier noch zusätzlich sortiert):

select dbs.bezdbs as "DB-Bez",

dbs.lpreis as Listenpreis,

angebot.bezdbs as "DB-A", angebot.finame as "H-A",

angebot.mpreis as Marktpreis,

haendler.ort as "H-Ort"

from dbs, angebot, haendler

where dbs.bezdbs=angebot.bezdbs

and

angebot.finame=haendler.fname

order by angebot.bezdbs, angebot.finame

Die entstehende Relation verknüpft somit die Informationen aus DBS und Haendler so, dass jede Firma mit allen ihren Datenbanksystemen erscheint. In den beiden Spalten DB-A (Datenbankname in Angebot) und H-A (Händlername in Angebot) in der Mitte ist die relationale Verknüpfung direkt erkennbar.

Die Redundanz der verknüpften Relation, wegen der ja ursprünglich im Rahmen der Normalisierung zerlegt wurde, ist an folgenden Stellen ersichtlich:

  • Für jeden Händler wird sein Ort mehrfach ausgegeben
  • Für jedes Datenbanksystem wird der Listenpreis mehrfach ausgegeben

Hat man, so wie in der obigen Abfrage, eine Projektion (Auswahl mehrerer Attribute) vorliegen, stellt sich die Frage, ob man wirklich bei jedem Attribut die Relationenbezeichnung angeben muss oder ob es eine kürzere Eingabemöglichkeit gibt.

Diese gibt es durch sog. Kurzbezeichnungen (correlation names) für die Relationen. Sie werden bei der Auflistung der Relationen nach dem Parameter FROM, jeweils nach dem Namen der Relation, angeführt. Z.B. also:

Kurzbezeichnungen für Relationen

...from dbs a, angebot b, haendler c ...

Somit hat in obigem Befehl dann DBS die Kurzbezeichnung "a", Angebot "b" und Haendler "c". Dies macht die Eingaben übersichtlicher, wie der nachfolgende Befehl zeigt. Er entspricht obigem Join entlang einer n:m-Beziehung.

select a.bezdbs as "DB-Bez", a.lpreis as Listenpreis,

b.bezdbs as "DB-A", b.fname as "H-A",

b.mpreis as Marktpreis, c.ort as "H-Ort"

from DBS a, Angebot b, Haendler c

where a.bezdbs=b.bezdbs

and b.fname=c.fname

order by b.bezdbs, b.fname;

Verschmelzen von vier Relationen.

Über vier Relationen geht die folgende Abfrage. Sie führt die Datenbanksysteme mit ihren Händlern und Produzenten zusammen.

4er-Join

select a.bezdbs as DBS, a.lpreis as Listenpreis,

b.mpreis as Marktpreis, c.fname as Händler,

d.pname as Produzent, d.stadt as Stadt

from

dbs a, angebot b, haendler c, produzenten d

where a.bezdbs=b.bezdbs

and b.finame=c.fname

and a.pname=d.pname

order by a.bezdbs, b.finame;


DBS

Listenpreis

Marktpreis

Händler

Produzent

Stadt

DBS1

9500.10

7100.00

HAE3

P1

Hamburg

DBS11

800.90

500.00

HAE4

P5

Passau

DBS3

2301.00

NULL

HAE3

P2

Frankfurt

DBS4

2000.77

1800.00

HAE2

P4

Rom

DBS5

900.66

NULL

HAE3

P1

Hamburg

DBS6

1800.55

1700.50

HAE1

P2

Frankfurt

DBS7

5000.40

4500.00

HAE4

P3

Berlin

DBS8

1200.30

1140.00

HAE2

P2

Frankfurt

DBS8

1200.30

1000.00

HAE5

P2

Frankfurt

DBS8

1200.30

1150.00

HAE6

P2

Frankfurt

DBS9

3000.20

2200.50

HAE1

P1

Hamburg

DBS9

3000.20

2500.00

HAE2

P1

Hamburg

DBS9

3000.20

3150.00

HAE4

P1

Hamburg

19.8.2 Outer Join

Oftmals sind nicht alle Schlüssel einer relationalen Verknüpfung als Fremdschlüssel "auf der anderen Seite" vorhanden und / oder umgekehrt. Zur Demonstration hier die Datenbank AngAbt (Angestellte – Abteilungen) mit den beiden Relationen Ang (Angestellte) und Abt (Abteilungen). Der Fremdschlüssel wurde nicht eingerichtet, sonst wäre der Eintrag der Fremdschlüssel in Ang ohne "Gegenstück" in Abt nicht möglich gewesen. Der folgende Kasten gibt die Befehle zum Einrichten der Datenbank an.

Fehlende Schlüssel / Fremdschlüssel

Zum Kopieren in der Web- und PDF-Version (hier noch zusätzlich sortiert):

create database AbtAng;

create table Abt (AbtBez char(2), AbtLeiter char(10), Standort char(10));

create table Ang (PersNr int(4), Name char(10), Vname char(8), AbtBez char(2));

Insert into Abt values

('PW', 'Sommer', 'Hamburg'),

('RE', 'Müller ', 'Passau'),

('VB', 'Rülp', 'Ulm'),

('CO', 'Czerny', 'Ulm'),

('LA', 'Dorr', 'Hamburg');

insert into Ang VALUES

(1030, 'May', 'Karl', 'IT'),

(1005, 'Sommer', 'Lisa', 'PW'),

(1040, 'Winter', 'Angelika', 'IT'),

(1090, 'Stepper', 'Rolf', 'PW'),

(1008, 'Rülp', 'Fred', 'VB'),

(1009, 'Baum', 'Ottilie', 'FE'),

(1017, 'Czerny', 'Rudi', 'CO');

Dies führt zu folgenden Relationen:

select * from abt

AbtBez

AbtLeiter

Standort

PW

Sommer

Hamburg

RE

Müller

Passau

VB

Rülp

Ulm

CO

Czerny

Ulm

LA

Dorr

Hamburg

PW: Personalwesen, RE: Rechnungswesen,
VB: Vertrieb, CO: Controlling, LA: Lager


select * from ang

PersNr

Name

Vname

AbtBez

1030

May

Karl

IT

1005

Sommer

Lisa

PW

1040

Winter

Angelika

IT

1090

Stepper

Rolf

PW

1008

Rülp

Fred

VB

1009

Baum

Ottilie

FE

1017

Czerny

Rudi

CO

1019

Dorr

Heiner

LA

Noch nicht genannte Abteilungen: FE: Forschung und Entwicklung,
IT: EDV-Abteilung


Nun der ganz normale EquiJoin. Er verknüpft nur die Tupel, für die es in beiden Relationen im Schlüssel bzw. Fremdschlüssel gleiche Ausprägungen gibt.

select a.persnr as PersNr, a.name as Name,

a.abtbez as "Abteilung von Ang",

b.abtbez as "Abteilung von Abt",

b.abtleiter as Abteilungsleiter

from ang a, abt b

where a.abtbez = b.abtbez

order by a.abtbez;


PersNr

Name

Abteilung von Ang

Abteilung von Abt

Abteilungsleiter

1017

Czerny

CO

CO

Czerny

1005

Sommer

PW

PW

Sommer

1090

Stepper

PW

PW

Sommer

1008

Rülp

VB

VB

Rülp


Es fehlen also die Tupel, für die es in der anderen Relation keine Entsprechung gab. Der Abteilung RE konnte kein Angestellter zugewiesen werden. Für die Angestellten May, Winter und Baum liegen die Abteilungen nicht vor.

Will man nun aber auch diese Tupel "ohne Partner" sehen, um zum Beispiel fehlende Fremdschlüssel zu ergänzen bzw. um Schlüsseleinträge zu identifizieren, für die es noch keinen Fremdschlüssel gibt, wählt man den sogenannten Outer Join.

Hinweis: Bei den Join-Befehlen haben die Ersteller der SQL-Varianten jeweils, neben dem Standard, noch weitere (komfortablere, leistungsstärkere) Lösungen hinzuprogrammiert. Deshalb lohnt hier ein Blick in die jeweiligen Handbücher!

Hier nun die Lösungen von MySQL. Ein Left Join bedeutet, dass die Tupel der in der Where-Klausel links stehenden Relation auch dann ausgegeben werden, wenn in der rechts stehenden Relation (hier Abt) keine passenden Ausprägungen vorhanden sind (hier IT und FE).

Left Join - Angestellte auch ohne Abteilungen

select * from

Ang LEFT JOIN Abt //Um welche Relationen geht es?

ON Ang.abtbez=Abt.abtbez //zu verknüpfende Attribute

order by Ang.abtbez; //Sortierung ist optional


PersNr

Name

Vname

AbtBez

AbtBez

AbtLeiter

Standort

1017

Czerny

Rudi

CO

CO

Czerny

Ulm

1009

Baum

Ottilie

FE

NULL

NULL

NULL

1040

Winter

Angelika

IT

NULL

NULL

NULL

1030

May

Karl

IT

NULL

NULL

NULL

1090

Stepper

Rolf

PW

PW

Sommer

Hamburg

1005

Sommer

Lisa

PW

PW

Sommer

Hamburg

1008

Rülp

Fred

VB

VB

Rülp

Ulm


Umgekehrt beim Right Join. Er bedeutet, dass die Tupel der in der Where-Klausel rechts stehenden Relation (hier Abt) auch dann ausgegeben werden, wenn in der links stehenden Relation (hier Ang) keine passenden Ausprägungen vorhanden sind (hier CO und LA).

Right Join - Abteilungen auch ohne Angestellte

select * from

Ang RIGHT JOIN Abt

ON Ang.abtbez=Abt.abtbez

order by Ang.abtbez;


PersNr

Name

Vname

AbtBez

AbtBez

AbtLeiter

Standort

NULL

NULL

NULL

NULL

LA

Dorr

Hamburg

NULL

NULL

NULL

NULL

RE

Müller

Passau

1017

Czerny

Rudi

CO

CO

Czerny

Ulm

1090

Stepper

Rolf

PW

PW

Sommer

Hamburg

1005

Sommer

Lisa

PW

PW

Sommer

Hamburg

1008

Rülp

Fred

VB

VB

Rülp

Ulm


UNION und der beidseitige Outer Join

Um diesen Befehl vorstellen zu können, wird eine neue Relation eingerichtet, Ang_F (frühere Angestellte). Sie hat denselben Aufbau wie die Relation Ang. Für ihre Einrichtung wird eine Variante des Befehls create table verwendet, mit dem der Relationenaufbau direkt von einer anderen Relation genommen werden kann. Hier die gesamte Einrichtung und Befüllung. Da wir nur die Struktur wollen, sorgen wir durch die Where-Klausel dafür, dass keine Tupel übernommen werden:

Neue Relation mit create table as

create table ang_f as

select * from ang where persnr >10000;

Nun noch einige Daten:

insert into ang_f values (2000, 'Voller', 'Ruth', 'RE');

insert into ang_f values (2010, 'Schlicht', 'Paul', 'IT');

insert into ang_f values (2020, 'Stur', 'Karl', 'CO');

select * from ang_f;


PersNr

Name

Vname

AbtBez

2000

Voller

Ruth

RE

2010

Schlicht

Paul

IT

2020

Stur

Karl

CO


Ziel des nächsten Schrittes ist es, die Daten dieser beiden Relationen Ang und Ang_F zusammenzubringen. Hier zur Erinnerung die Daten von Ang:

select * from ang


PersNr

Name

Vname

AbtBez

1030

May

Karl

IT

1005

Sommer

Lisa

PW

1040

Winter

Angelika

IT

1090

Stepper

Rolf

PW

1008

Rülp

Fred

VB

1009

Baum

Ottilie

FE

1017

Czerny

Rudi

CO


Mit dem Parameter UNION werden Vereinigungsmengen von Tupeln gebildet. Er kann nur angewandt werden, wenn die Tupelmengen genau dieselben Attribute (mit denselben Datentypen) haben. Insgesamt besteht eine Abfrage mit UNION aus zwei SELECT-Befehlen, die jeweils eine Tupelmenge identifizieren. Zwischen den SELECT-Befehlen steht dann UNION. In unserem Beispiel bringen wir die aktuellen und die früheren Mitarbeiter zusammen, z.B. für die Einladung zu einer Veranstaltung:

UNION

select * from ang

UNION

select * from ang_f;


PersNr

Name

Vname

AbtBez

1030

May

Karl

IT

1005

Sommer

Lisa

PW

1040

Winter

Angelika

IT

1090

Stepper

Rolf

PW

1008

Rülp

Fred

VB

1009

Baum

Ottilie

FE

1017

Czerny

Rudi

CO

2000

Voller

Ruth

RE

2010

Schlicht

Paul

IT

2020

Stur

Karl

CO


Im oberen Bereich sind die Tupel von Ang, unten die von Ang_F.

Eine Vereinigung zweier Relationen mit UNION ist natürlich auch möglich, wenn eine Where-Klausel vorliegt und eine Attributsliste benutzt wird. Es müssen aber in beiden Fällen dieselben Attribute sein.

select persnr, name, abtbez from ang

where abtbez = 'IT' or abtbez = 'CO'

UNION

select persnr, name, abtbez from ang_f

where abtbez = 'IT' or abtbez = 'CO'

order by abtbez;


persnr

name

abtbez

2020

Stur

CO

1017

Czerny

CO

2010

Schlicht

IT

1030

May

IT

1040

Winter

IT


Mit dem Befehl UNION kann nun ein ein vollständiger Outer Join zu den beiden Relationen Abt und Ang durchgeführt werden. Der folgende Befehl sammelt zuerst die Daten von Abt und Ang gemäß einem left join ein, dann nach einem right join. Anschließend werden diese Tupelmengen mittels UNION zusammengeführt.

Mit left join relationenname bzw. right join relationenname wird der jeweilige Verbund angefordert. Mit on attr1 = attr2 wird er durch Angabe der zur Verknüpfung dienenden Attribute präzisiert.

select * from ang LEFT JOIN abt

ON Ang.abtbez=Abt.abtbez

UNION

select * from ang RIGHT JOIN abt

ON Ang.abtbez=Abt.abtbez;


PersNr

Name

Vname

AbtBez

AbtBez

AbtLeiter

Standort

1005

Sommer

Lisa

PW

PW

Sommer

Hamburg

1090

Stepper

Rolf

PW

PW

Sommer

Hamburg

1008

Rülp

Fred

VB

VB

Rülp

Ulm

1017

Czerny

Rudi

CO

CO

Czerny

Ulm

1030

May

Karl

IT

NULL

NULL

NULL

1040

Winter

Angelika

IT

NULL

NULL

NULL

1009

Baum

Ottilie

FE

NULL

NULL

NULL

NULL

NULL

NULL

NULL

RE

Müller

Passau

NULL

NULL

NULL

NULL

LA

Dorr

Hamburg


Jetzt sind alle fehlenden Ausprägungen (NULL) erkennbar. Zu den Angestellten May, Winter und Baum fehlen die Abteilungen, zu den Abteilungen RE und LA fehlen Tupel von Angestellten.

19.8.3 SelfJoin - Eine Relation mit sich selbst verknüpfen

Manchmal ist es nötig, eine Relation mit sich selbst zu verknüpfen. Dazu muss die Relation in der FROM-Klausel zweimal mit verschiedenen Bezeichnungen eingeführt werden, was nur mit den oben eingeführten correlation names geht. Im Rahmen der Abfrage kann man sie dann wie zwei verschiedene Relationen behandeln.

SelfJoin

Im folgenden Beispiel wird jedes Datenbanksystem mit allen verknüpft, die einen niedrigeren Preis haben. Das ist dann auch ein Join, der kein EquiJoin ist (Non-Equjoin), der also nicht auf Gleichsetzung beruht. Grundsätzlich können in Joins alle Operatoren für Zeichenketten und numerische Werte eingesetzt werden, allerdings ist für viele kein Einsatzzweck zu finden.

Non-equijoin

Im folgenden Beispiel wird nun also in der Liste der Relationen DBS einmal mit A und nochmals mit B eingeführt. Damit ist folgendes möglich:

  • Die Attribute BezDBS und LPreis (Listenpreis) werden zweimal für die auszugebenden Tupel angefordert.
  • In der Where-Klausel werden A.lpreis und B.lpreis mit dem Kleiner-Operator verglichen.
  • Ausgegeben werden nur die Tupel, bei denen die Where-Bedingung erfüllt ist, bei denen also das zuerst angeführte Datenbanksystem teurer ist.

select a.bezdbs, a.lpreis as "A-Preis",

b.bezdbs, b.lpreis as "B-Preis"

from DBS A, DBS B

where A.LPREIS > B.LPREIS

order by a.bezdbs, b.bezdbs;

Dies führt zu folgendem Ergebnis:


bezdbs

A-Preis

bezdbs

B-Preis

DBS1

9500.10

DBS10

600.88

DBS1

9500.10

DBS11

800.90

DBS1

9500.10

DBS2

3000.20

DBS1

9500.10

DBS3

2301.00

DBS1

9500.10

DBS4

2000.77

DBS1

9500.10

DBS5

900.66

DBS1

9500.10

DBS6

1800.55

DBS1

9500.10

DBS7

5000.40

DBS1

9500.10

DBS8

1200.30

DBS1

9500.10

DBS9

3000.20

DBS11

800.90

DBS10

600.88

DBS2

3000.20

DBS10

600.88

DBS2

3000.20

DBS11

800.90

DBS2

3000.20

DBS3

2301.00

DBS2

3000.20

DBS4

2000.77

DBS2

3000.20

DBS5

900.66

DBS2

3000.20

DBS6

1800.55

DBS2

3000.20

DBS8

1200.30

DBS3

2301.00

DBS10

600.88

DBS3

2301.00

DBS11

800.90

DBS3

2301.00

DBS4

2000.77

DBS3

2301.00

DBS5

900.66

DBS3

2301.00

DBS6

1800.55

DBS3

2301.00

DBS8

1200.30

DBS4

2000.77

DBS10

600.88

DBS4

2000.77

DBS11

800.90

DBS4

2000.77

DBS5

900.66

DBS4

2000.77

DBS6

1800.55

DBS4

2000.77

DBS8

1200.30

DBS5

900.66

DBS10

600.88

DBS5

900.66

DBS11

800.90

DBS6

1800.55

DBS10

600.88

DBS6

1800.55

DBS11

800.90

DBS6

1800.55

DBS5

900.66

DBS6

1800.55

DBS8

1200.30

DBS7

5000.40

DBS10

600.88

DBS7

5000.40

DBS11

800.90

DBS7

5000.40

DBS2

3000.20

DBS7

5000.40

DBS3

2301.00

DBS7

5000.40

DBS4

2000.77

DBS7

5000.40

DBS5

900.66

DBS7

5000.40

DBS6

1800.55

DBS7

5000.40

DBS8

1200.30

DBS7

5000.40

DBS9

3000.20

DBS8

1200.30

DBS10

600.88

DBS8

1200.30

DBS11

800.90

DBS8

1200.30

DBS5

900.66

DBS9

3000.20

DBS10

600.88

DBS9

3000.20

DBS11

800.90

DBS9

3000.20

DBS3

2301.00

DBS9

3000.20

DBS4

2000.77

DBS9

3000.20

DBS5

900.66

DBS9

3000.20

DBS6

1800.55

DBS9

3000.20

DBS8

1200.30


DBS10 taucht in der ersten Spalte gar nicht auf, weil es das billigste Datenbanksystem ist. DBS1 dagegen, als teuerstes Datenbanksystem, ist mit allen übrigen verknüpft.

Welche Datenbanksysteme sind teurer als der Marktführer

Nun die eigentliche Fragestellung. Es geht hauptsächlich um Joins, insbesondere soll ein Beispiel für den SelfJoin gegeben werden.

Bevor es los geht, richten wir eine zusätzliche Relation mit der Bezeichnung Typen ein. Sie erfasst Typen von Datenbanksystemen (Marktsegmente), hier vereinfacht mit NoSQL-, objektorientierte (OODBS) und relationale Datenbanksysteme (RDBS) dargestellt. Erfasst wird für jedes Marktsegment der Marktführer (mafu) und der Anteil des Marktführers an den Gesamtverkäufen (anteil):

create table typen

(Bez varchar(10), mafu char(10), anteil decimal(2,0),

primary key (bez),

foreign key (mafu) references dbs(bezdbs));

Folgende Daten werden eingefügt:

insert into typen values

('NoSQL', 'DBS4', 80),

('OODBS', 'DBS6', 40),

('RDBS', 'DBS2', 35);

Es hat also DBS4 im Marktsegment der NoSQL-Datenbanken einen Marktanteil von 40% usw. Die Relation:

Select * from typen


Bez

mafu

anteil

NoSQL

DBS4

80

OODBS

DBS6

40

RDBS

DBS2

35


Nähern wir uns nun der eigentlichen Abfrage durch einige Vorbetrachtungen an. Zuerst indem wir nochmals einen Blick auf die in der Relation DBS erfassten Marktsegmente mit ihren Datenbanksystemen und Listenpreisen werfen:

select typ, bezdbs, lpreis from dbs

order by typ, bezdbs;


typ

bezdbs

lpreis

NoSQL

DBS4

2000.77

NoSQL

DBS9

3000.20

OODBS

DBS5

900.66

OODBS

DBS6

1800.55

OODBS

DBS7

5000.40

RDBS

DBS1

9500.10

RDBS

DBS10

600.88

RDBS

DBS11

800.90

RDBS

DBS2

3000.20

RDBS

DBS3

2301.00

RDBS

DBS8

1200.30


Die nächste „Vorbetrachtung“ liefert nun dank zweier VERBUND-Operationen nur die Systeme, die Marktführer sind und gibt deren Listenpreise an. Der erste JOIN verknüpft die Tupel über die Typen, der zweite vergleicht c.mafu mit a.bezdbs und lässt nur die übrig, die entsprechende Einträge in beiden Relationen haben.

Nur die Marktführer

Die Einschränkung auf die Marktführer wird also durch den zweiten Ausdruck der Where-Klausel erreicht.

Inhaltliche Einschränkung durch Join

select a.bezdbs, a.lpreis, c.bez

from dbs a, typen c

where a.typ=c.bez

and c.mafu=a.bezdbs;


bezdbs

lpreis

bez

DBS2

3000.20

RDBS

DBS4

2000.77

NoSQL

DBS6

1800.55

OODBS

Marktführer, ihr Listenpreis und ihr Marktsegment


Dies ist auch ein Beispiel dafür, wie ein JOIN, wenn er entsprechend – von der Semantik gesteuert – formuliert ist, durchaus auch zu einer geringen Zahl von Tupeln führen kann.

Damit ist die eigentliche Abfrage vorbereitet: Welche Systeme sind teurer als der jeweilige Marktführer? Zu ihrer Beantwortung müssen drei VERBUND-Operationen vorgenommen werden mit „dbs a, dbs b, typen c“:

Teurer als der Marktführer?

where a.typ=c.bez

verknüpft jedes System mit jedem, das vom selben Typ ist. Damit entstehen Tupel, die Attribute aus a und c verknüpfen.

AND c.mafu=b.bezdbs

verknüpft die oben erhaltenen Tupel mit b (das ja auch DBS ist) derge­stalt, dass jeweils die Angaben des Marktführers angehängt werden.

AND a.lpreis > b.lpreis

schränkt nun auf die ein, bei denen der Preis des Systems größers ist als der des Marktführers. Die gesamte Abfrage:

select a.bezdbs as Bez DBS, a.lpreis as "Preis 1",
b.lpreis as "Preis MaFü",

c.bez as Bez, c.mafu as Marktführer

from dbs a, dbs b, typen c

where a.typ=c.bez

and c.mafu=b.bezdbs

and a.lpreis > b.lpreis;


Bez DBS

Preis 1

Preis MaFü

Bez

Marktführer

DBS1

9500.10

3000.20

RDBS

DBS2

DBS7

5000.40

1800.55

OODBS

DBS6

DBS9

3000.20

2000.77

NoSQL

DBS4


Preis 1 ist der Preis des Datenbanksystems, das teurer als der Marktführer ist, Preis MaFü der des jeweiligen Marktführers.

Damit ist die Frage beantwortet: DBS1, DBS7 und DBS9 sind teurer als der jeweilige Marktführer.

Soweit eine Einführung in die wesentlichen Elemente von SQL. Sie sollte für die SQL-Beispiele des Buches ausreichen. Eine ausführlichere Anleitung findet sich auf ww.staud.info.

19.9 Transaktionen

Datenbanken werden nicht "einfach so" eingerichtet, befüllt und mit simplen Befehlen abgefragt, sondern sie dienen Anwendungen. Zum Beispiel einer Personalverwaltung, die unter anderem die monatliche Gehaltszahlung realisiert. Oder einer Software für die Lagerhaltung, die festhält, wo welches Produkt gelagert ist, wann etwas entnommen oder hineingetan wurde. Oder einer prozessorientierten integrierten Standardsoftware (ERP-Software genannt), bei der die Datenbank alle Daten zu den Geschäftsprozessen verwaltet.

Anwendungen benötigen ...

Bei allen diesen Anwendungen gibt es nicht nur einfache Aktionen auf der Datenbank (Daten abfragen, einfüllen, verändern, löschen), sondern zusammenhängende Folgen solcher Aktionen, für die mehrere Datenbankaktionen nötig sind. Zum Beispiel für Gehaltszahlungen:

...nicht nur einfache Aktionen auf den Daten

  • Alter der Person prüfen (oder Dauer der Unternehmenszugehörigkeit), falls dies gehaltsrelevant ist. Dafür ist ein Lesezugriff auf die Personaldatenbank nötig, evtl. verbunden mit Berechnungen (z.B. aktuelles Datum abzüglich Geburtsdatum).
  • Prüfen, ob sich die tarifliche Einordnung verändert hat. Auch dies erfordert einen Lesezugriff auf die Personaldatenbank.
  • Abfragen durch Zugriff auf die entsprechende Relation, ob Überstunden oder Minderstunden vorliegen.
  • Abfragen des Grundgehalts, Berechnung des realen Gehalts für den jeweiligen Monat.
  • Formulierung des Überweisungsauftrags mit allen erhobenen Daten.
  • Verbuchung der Gehaltszahlung in der unternehmenseigenen Datenbank.
  • Auslösung des Überweisungsauftrags beim gehaltsüberweisenden Kreditinstitut.

Eine solche Folge von Datenbankaktionen wird noch begleitet von weiteren Aktivitäten des Anwendungsprogramms, z.B. dem Öffnen bzw. Schließen des Netzzugangs (für die Überweisung). Kommt es nun in dieser Aktionsfolge zu einem Systemabsturz, zum Beispiel wegen ...

Absturz

  • eines Virus, der die Computer lahm legt,
  • eines Stromausfalles, bei dem die Ersatzlösung (Dieselgenerator für Notstrom) nicht funktioniert,
  • eines Blitzschlages, der den Rechner zerstört,
  • eines Hochwassers, das die Rechner beschädigt,
  • eines Erdbebens mit zerstörerischer Wirkung,
  • eines vorübergehenden Netzzusammenbruchs (falls das Unternehmen auf WebServices setzt),

dann bleibt die Aktionenfolge, die gerade abgearbeitet wurde, unvollständig. Dies zerstört im Regelfall die Konsistenz (Stimmigkeit, Korrektheit) der Datenbank.

Eine Datenbank ist konsistent, wenn sie frei von Widersprüchen und Fehlern ist. Betrachten wir anhand einiger Beispiele, wie solche Widersprüche entstehen können:

Konsistenz der Datenbank

  • Im obigen Beispiel Gehaltszahlung: Nach der Verbuchung der Gehaltszahlung, aber vor dem Auslösen der Überweisung kommt es zu einem Systemabsturz. Dann zeigt die Datenbank die Gehaltszahlung an, tatsächlich wurde sie aber nicht angestoßen.
  • In einer doppelten Buchführung muss die Summe aller Sollbuchungen und Habenbuchungen gleich sein. Dies wäre nicht mehr der Fall, wenn es gerade da zum Systemabsturz kam, als die Sollbuchung realisiert und die Habenbuchung noch nicht getätigt war.
  • Wenn unvollständige Daten entstehen. Beispielsweise bei dem Eintrag eines neuen Angestellten, wenn zwar der Name und der Vorname schon eingetragen sind, nicht aber die übrigen Angaben.
  • Ganz allgemein bei der Einbuchung einer relationalen Verknüpfung (1:1-, 1:n- oder n:m-Beziehung) in eine Datenbank, wenn z.B. der Systemabsturz erfolgt, nachdem der Fremdschlüsseleintrag getätigt, der zugehörige Schlüssel aber noch nicht eingetragen ist. Dies wäre eine Störung der referentiellen Integrität.
  • Ganz allgemein, wenn semantische Integritätsbedingungen durch den Absturz gestört werden. Wenn also z.B. die Regel, dass die Gehaltssumme der Abteilung 3.000.000 Euro nicht überschreiten darf, in den Daten nicht mehr eingehalten wird.
  • Wenn derselbe Kunde mit verschiedenen Adressen gespeichert wird, weil z.B. beim Einbuchen des neuen Auftrags (mit der neuen Adresse) vor der Löschung der alten Anschrift der Absturz erfolgte.

Zusammengefasst geht es also darum, die Daten korrekt, vollständig und frei von Verstößen gegen die semantischen Integritätsbedingungen zu halten.

Damit können wir nun den Begriff Transaktion definieren:

Eine Transaktion besteht aus einer oder mehreren zusammenhängenden Datenbankoperationen (Einfügen, Löschen, Verändern, Suchen).

Definition:
Transaktion

Dabei handelt es sich natürlich um in sich abgeschlossene Aktionsfolgen. Einfache Transaktionen bestehen im Ändern einzelner Attributswerte, höchstens einzelner Tupel. Komplexe Transaktionen führen umfangreichere Änderungen in mehreren Einzelschritten aus: „Transaktionen beschreiben das dynamische Geschehen rund um ein Datenbanksystem“ [Lang und Lockemann 1995, S.26]. Formuliert werden sie mithilfe einer Data Manipulation Language (DML), z.B. dem oben vorgestellten SQL. Die Datenbankoperationen, die eine Transaktion bilden, können entweder in ein Anwendungsprogramm eingebettet oder interaktiv mit der DML spezifiziert werden.

Eine Möglichkeit der Spezifizierung von Transaktionsgrenzen ist die Verwendung von Begin-Transaction- und End-Transaction-Anweisungen in einem Anwendungsprogramm [Elmasri und Navathe 2002, S.677].

Es gibt unterschiedliche Klassen von Transaktionen, ACID-Transaktionen, programmierte Transaktionen und Transaktionen im Dialog [Lang und Lockemann 1995, S.623ff]. Die wichtigsten sind die ACID-Transaktionen, für die folgende Eigenschaften gefordert werden:

  • Atomizität (atomicity). Damit ist gemeint, dass eine Transaktion entweder vollständig oder überhaupt nicht ausgeführt wird, sie ist eine "atomare" Verarbeitungseinheit. Der Grund für diese Eigenschaft ist klar: Teilweise ausgeführte Transaktionen verletzen in der Regel die Konsistenz der Datenbank. Kommt es zu einem Störung sfall, lässt sich also eine Transaktion aus irgendeinem Grund nicht vollständig abarbeiten, so müssen die beteiligten Daten auf den Ausgangszustand zurückgesetzt werden. D.h., die Transaktion wird erst dann für gültig erklärt und vollzogen, wenn sie erfolgreich und vollständig durchgeführt wurde, ansonsten wird ein Rollback durchgeführt.
  • Konsistenz (consistency). Eine solche Transaktion muss konsistenzbewahrend sein, ihre vollständige Ausführung muss die Datenbank von einem konsistenten Zustand in einen anderen ebenfalls konsistenten überführen. Gemeint ist die inhaltliche und referenzielle Integrität des Datenbestandes [Trelle 2014, S. 6].
  • Isolation (isolation). Eine solche Transaktion kann isoliert von den anderen Transaktionen ausgeführt werden. Mit anderen Worten: Die Ausführung einer solchen Transaktion sollte nicht von anderen, gleichzeitig ablaufenden Transaktionen gestört werden können. Sie laufen somit in einem simulierten Einbenutzerbetrieb ab. M.a.W.: Durch diese Eigenschaft wird verhindert, dass sich laufende Datenoperationen gegenseitig beeinflussen.
  • Dauerhaftigkeit (durability). Die von einer bestätigten Transaktion in die Datenbank geschriebenen Änderungen müssen in der Datenbank dauerhaft gespeichert werden.

Die ersten Buchstaben der englischen Bezeichnungen bilden das Wort ACID (engl. für Säure). Daher kommt die Bezeichnung ACID- Transaktionen. Diese kann nun wie folgt definiert werden:

Definition ACID-Transaktion: Eine ACID-Transaktion ist eine konsistenzerhaltende, atomare und isolierte Operation auf einer Datenbank mit persistenter Wirkung [Zehnder 1998, S.251].

Definition:
ACID-Transaktion

ACID-Transaktionen werden im allgemeinen interaktiv angestoßen, entstehen also spontan auf Grund von Umweltereignissen, z.B. wenn die Gehaltszahlungen anstehen oder wenn Lagerentnahmen realisiert werden müssen.

Programmierte Transaktionen sind solche, die in ihrem generellen Ablauf vorgeplant sind. Bei ihnen bleiben nur der Zeitpunkt ihres Aufrufs und ihre Parameterversorgung offen. Sie werden daher als Prozeduren (Transaktionsprozeduren) formuliert [Lang und Lockemann 1995, S.626].

Programmierte Transaktionen

Die Transaktionen im Dialog betreffen Arbeiten mit der Datenbank, die im Dialog erfolgen. Dabei wird zu Beginn des Dialogs automatisch eine Transaktion gestartet. Nach jedem Sichern oder Verwerfen wird die alte Transaktion abgeschlossen und automatisch eine neue gestartet. Diese Philosophie liegt der Transaktionssteuerung in SQL zugrunde, wobei SQL nur ACID-Transaktionen realisiert [Lang und Lockemann 1995, S.627].

Transaktionen im Dialog

Das Wiederherstellen des korrekten Zustandes - nach einem Zusammenbruch - ist Aufgabe des Transaktionsmanagers. Seine Aufgabe lässt sich ganz allgemein wie folgt beschreiben:

Transaktionsmanager

  • Beim Start der Transaktion: Feststellen, welche Daten die Transaktion nutzt. Festhalten des Anfangszustandes der Datenbank und falls nötig Sperren der Daten für andere Nutzer.
  • Falls es zu einem Absturz kommt: Wiederherstellen des alten Zustandes.
  • Bei erfolgreichem Abschluss der Transaktion: Herstellen des neuen Zustandes, Aufheben aller eventueller Sperren.

Es wurde oben schon angedeutet, bei der Forderung nach Isolation einer Transaktion: In einer Anwendung können mehrere Transaktionen parallel ablaufen. Dies können mehrere Nutzer sein, die parallel an der gemeinsamen Datenbank arbeiten, oder mehrere Anwendungsprogramme. Die Lösung dieser Problematik erfordert die Synchronisation von Datenzugriffen.

Parallele Transaktionen

Durchführung von Transaktionen und Triggern mit SQL

Bei der Durchführung von Transaktionen mit SQL muss folgendes realisiert werden: Eine Befehlsfolge wird durch SQL abgearbeitet, aber erst wirksam gemacht, wenn der letzte Befehl erfolgreich umgesetzt wurde. Dafür werden vom ersten Befehl an alle Transaktionsschritte in einem sog. Logfileprotokolliert, um sie gegebenenfalls rückgängig machen zu können. Außerdem werden alle Datensätze (manchmal auch ganze Relationen), die durch die Befehlsfolge der Transaktion bearbeitet werden, für die übrigen Nutzer der Datenbank gesperrt. Damit ist gesichert, dass andere Nutzer nicht während der Abarbeitung der Transaktionsbefehle die betroffenen Datensätze ändern.

Nun die konkreten Befehle, die für die Gestaltung von Transaktionen in SQL zur Verfügung stehen. Zuerst die "allgemeinen", nach ANSI-Standard, dann die spezifischen des Datenbanksystems Oracle.

Transaktionen nach dem ANSI-Standard:

  • BEGIN TRANSACTION zeigt den Beginn der Transaktion. Alle ab da folgenden SQL-Befehle werden protokolliert, bis die Transaktion entweder mit COMMIT oder ROLLBACK beendet wurde.
  • COMMIT WORK (Oracle: COMMIT). Dieser Befehl bewirkt, dass alle Veränderungen, die während der Transaktion vorgenommen wurden, auf das jeweilige Speichermedium geschrieben und damit dauerhaft (persistent) gemacht werden. Dies ist die erste und erfolgreiche Möglichkeit zur Beendigung einer Transaktion. Nach diesem Befehl sind auch alle eventuellen Sperren von Daten gegenüber anderen Nutzern aufgehoben.
  • ROLLBACK WORK (Oracle: ROLLBACK). Kommt es zum negativen Fall, zum vorzeitigen Absturz bzw. Abbruch der Transaktion, greift dieser Befehl. Er bewirkt, dass alle Veränderungen, die während der Transaktion vorgenommen wurden, wieder rückgängig gemacht werden. Dies ist die zweite Möglichkeit zur Beendigung einer Transaktion.
  • READ oder WRITE spezifizieren Lese- oder Schreibvorgänge auf einem Datenbankobjekt, die als Teil einer Transaktion ausgeführt werden [Elmasri und Navathe 2002, S. 683].
  • END TRANSACTION besagt, dass keine READ- und WRITE-Anweisungen mehr für diese Transaktion ausgeführt werden, d.h. sie kennzeichnet das Ende der Transaktion [Elmasri und Navathe 2002, S. 683].

Um zu sehen, wie die Einrichtung einer Transaktion konkret aussieht, hier die spezifischen Befehle des Datenbanksystems Oracle (nach dem sog. PL/SQL):

  • SAVEPOINT. Dieser Befehl erlaubt es, eine Stelle in der Transaktion festzulegen, zu der gegebenenfalls später ein Rollback (Wiederherstellen des alten Zustandes gemäß dem entsprechenden Befehl) möglich ist. Dies betrifft längere Transaktionen, d.h. Transaktionen mit zahlreichen Befehlen. Das Motiv für solch einen Befehl ist, die meist zu Beginn erfolgte Sperrung von Daten für andere Nutzer früher wieder aufheben zu können, nicht erst nach Abarbeitung der gesamten Transaktion. Es können auch mehrere savepoints gesetzt werden. Ist einer gesetzt, kann man zur Steuerung des Transaktionsverhaltens nicht nur die Befehle COMMIT und ROLLBACK einsetzen, sondern auch einen Rollback bis zum savepoint.

Im folgenden Beispiel (übersetzt nach [Oracle Corp. 2002, Seite 18.2]) gibt es die Relation Angestellte (u.a. mit den Attributen Gehalt und Nachname). Dabei gilt folgende semantische Integritätsbedingung: Die Summe aller Gehälter darf 300.000 Euro nicht überschreiten. Deshalb wird nach Eintragen der neuen Gehälter die neue Gehaltssumme erfragt. Falls diese zu hoch ist, wird zum savepoint bauer_gehalt ein Rollback durchgeführt und dann das Gehalt von Maier weniger erhöht. Das Beispiel zeigt auch den Einsatz mehrerer benannter savepoints.

Beispiel

Gehaltserhöhung Bauer

UPDATE angestellte

SET gehalt = 7000

where nachname = 'Bauer';

Erster Savepoint

SAVEPOINT bauer_gehalt;

Gehaltserhöhung Maier

UPDATE angestellte

SET salary = 12000

where nachname = 'Maier';

Zweiter Savepoint

SAVEPOINT maier_gehalt;

Überprüfung Gehaltssumme:

select SUM(gehalt) from angestellte;

Falls nötig: Rollback

ROLLBACK TO SAVEPOINT bauer_gehalt;

Evtl. niedrigeres Gehalt

UPDATE angestellte

SET gehalt = 11000

where nachname = 'Maier'

COMMIT;

Mit SET TRANSACTION kann man die Rahmenbedingungen einer Transaktion bezüglich der Lese- bzw. Schreibberechtigung (read only / read write), des "isolation level" und der Zuweisung zu einem spezifischen Rollbacksegement festlegen. Außerdem kann man der Transaktion einen Namen geben. Diese Festlegungen betreffen nur die eigene aktuelle Transaktion, nicht die anderer Nutzer und nicht andere Transaktionen. Falls der Befehl genutzt wird (dies ist nicht zwingend erforderlich), muss er als erster in der Transaktion kommen.

set transaction

Das Setzen von "read only" macht die Transaktion zu einer, die nur lesend auf die Datenbank zugreift und zwar so, dass alle ab Transaktionsbeginn folgenden Lesezugriffe die Daten so sehen, wie sie vor Beginn der Transaktion waren. Read-only - Transaktionen werden z.B. benötigt, wenn Berichtsprogramme ("Reports") laufen, die mehrere Relationen abfragen, die wiederum gleichzeitig von anderen Nutzern aktualisiert werden. Die diesbezügliche Standardeinstellung für Transaktionen in Oracle-SQL ist "read write". Damit dürfen die Befehle der Transaktion lesend und schreibend auf die Datenbank zugreifen.

Lese- bzw. Schreibberechtigung

Mit dem Parameter isolation level wird festgelegt, wie mit Transaktionen, die Veränderungen am Datenbestand umfassen, umgegangen wird. Er hat zwei Ausprägungen: serializable und read committed. "Serializable" bedeutet, dass die Transaktionen der aktuellen Sitzung den sog. "serializable transaction isolation mode” nach der Norm SQL92 nutzen: Falls eine solche Transaktion versucht, einen datenbearbeitenden Befehl (DML-Befehl) auszuführen, der Zeilen aktualisiert, die gerade von einer anderen nicht beendeten Transaktion aktualisiert werden, dann scheitert der DML-Befehl. "Read committed” bedeutet, dass die Transaktion in der jeweiligen Sitzung das voreingestellte "Oracle transaction behavior" nutzt. Hier gilt: Falls in der Transaktion ein datenbearbeitender Befehl vorkommt, der die Sperrung von Zeilen benötigt, die gerade von einer anderen Transaktion genutzt werden, dann wartet der Befehl, bis die Sperrung aufgehoben wird.

Isolation level

„Zuweisung zu einem spezifischen Rollbacksegement“ betrifft die innere Organisation des Rollbackvorgangs. Für diesen muss ja Speicherplatz reserviert werden ("Rollbacksegment"), auf dem die durch die Transaktion veränderten Daten zwischengespeichert werden bis zum Ende der Transaktion. Mit diesem Parameter ist es nun möglich, die Zuweisung zu einem Rollbacksegment zu steuern. Zum Beispiel indem man bewusst ein kleines Segment anwählt, das vom Transaktionsmanager im Arbeitsspeicher gehalten werden kann, wenn man weiß, dass keine lang laufenden Auswertungen dieselben Relationen betreffen.

Zuweisung zu einem spezifischen Rollbacksegement

Mit dem Parameter Namensvergabe kann der Transaktion eine Bezeichnung zugewiesen werden. Dies ist vor allem nötig, wenn es um die Arbeit mit verteilten Datenbanken geht, wo mehrere Transaktionen gleichzeitig aktiv sein können. Vgl. auch das Beispiel oben.

Namensvergabe

Bei den sogenannten Triggern handelt es sich um spezielle Transaktionen. Ein Trigger wird durch eine Aktion des Nutzers ausgelöst. Z.B. durch das Eintragen, Ändern oder Löschen von Daten. Oder, in einer Maske, durch die Beendigung des Eintrags in ein Feld. Dann kann ein Trigger den Sprung zum nächsten Eingabefeld realisieren.

Trigger

In [Abramson, Abbey und Corey 2004, S. 18] findet sich das Beispiels eines Triggers, durch den Gehaltserhöhungen überwacht werden können. Der Trigger leistet folgendes, nachdem das neue Gehalt eingetragen wurde (Auslöser ist dann das datenbezogene Ereignis "on update"):

  • Anlegen eines Datensatzes in einer Datei sal_audit ("Gehaltsveränderungsüberwachungsdatei").
  • Festhalten von Zeit und Datum der Transaktion (für die Gehaltsänderung).
  • Eintrag des Benutzernamens in die Spalte doer (Eintrager).
  • Altes Gehalt in die Spalte old_sal setzen.
  • Neues Gehalt in die Spalte new_sal setzen

Dies alles geschieht automatisch nach dem Eintrag des neuen Gehalts. Eine solche Datei kann dann z.B. regelmäßig von der Geschäftsleitung kontrolliert werden. Typischerweise existieren Trigger nicht alleine, sondern nur im Zusammenhang mit einer Relation der Datenbank (so auch bei Oracle). Wird die Relation gelöscht, verschwindet auch der Trigger [Abramson, Abbey und Corey 2004, S. 18].

Realisierung mit ORACLE-SQL

Betrachten wir zum Schluss noch die konkrete Realisierung von Triggern mit Oracle. Die folgenden Beispiele direkt aus dem SQL-Referenzwerk [Oracle Corp. 2002, S. 15-107f.] zeigen die Grundsyntax verschiedener Triggervarianten.

Ein DML-Trigger mit Before bezieht sich auf gesamte Aktionen der DML (Data Manipulation Language). Er wird von Oracle "gefeuert” bevor die entsprechende Aktion startet. Entsprechend ist die Festlegung durch SQL-Befehle:

DML-Trigger (er) mit BEFORE-Statement.

CREATE TRIGGER schema.trigger_name

BEFORE

DELETE OR INSERT OR UPDATE

ON schema.table_name

pl/sql_block

Die letzte Zeile gibt den ”Block” von Anweisungen an, der bei jedem Aufruf der obigen Befehle ausgeführt wird. D.h., das Datenbanksystem Oracle "feuert” ("fires”) solch einen Trigger immer bevor ein Delete-, Insert- oder Update-Befehl auf die Relation angewandt wird.

wann er ausgelöst wird.

Ein DML-Zeilentrigger mit Before wird von Oracle "gefeuert”, wenn ein einzelnes Tupel (Zeile, row) einer Relation verändert werden soll. Hierzu ein Beispiel. Der folgende Trigger wird vor jedem Insert- oder Update-Befehl für die Relation EMPLOYEE (Angestellte) der Datenbank hr (human ressources; Personalwesen) aktiv. Also dann, wenn ...

DML - Zeilentrigger mit BEFORE

  • ein Insert-Befehl Tupel (Zeilen) zu der Relation hinzufügt
  • ein Update-Befehl die Einträge in den Attributen salary oder job_id der Relation EMPLOYEE ändert

CREATE TRIGGER hr.salary_check

BEFORE INSERT OR UPDATE OF

salary, job_id ON hr.employees

FOR EACH ROW

WHEN (new.job_id <> ’AD_VP’)

pl/sql_block

Es ist ein sog. partial BEFORE row trigger. Der PL/SQL - Block könnte zum Beispiel festlegen, dass ein Angestelltengehalt in die vorbestimmten Bandbreite für solche Gehälter fallen muss. Das obige Beispiel hat auch eine Einschränkung, wodurch er daran gehindert wird, das Gehalt des Verwaltungsvizepräsidenten (administrative vice president - AD_VP) zu prüfen.

Das letzte Beispiel eines DDL-Trigger mit AFTER zeigt die Befehle zur Erstellung eines Triggers, der sich auf einen Befehl aus dem Bereich der Data Definition Language (DDL) bezieht, den Befehl Create. Er wird nach dem Ausführen eines Create-Befehls aktiv. Im PL/SQL-Block könnten zum Beispiel Befehle stehen, durch die eine Überwachung solcher Aktivitäten möglich wird.

DDL-Trigger mit AFTER

CREATE TRIGGER audit_db_object

AFTER CREATE

ON SCHEMA

pl/sql_block