SQL- und NoSQL-Datenbanken

Einführung in SQL

Einfache Abfragen

Projektionen

Qualifizierte Abfragen

Abfragen mit Zeichenerkennung

Abfragen mit Aggregationsfunktionen

Zusammensetzen von Tabelleninhalten (Join)

Sortieren und Gruppieren von Tupeleinträgen

Vereinigung, Durchschnitt, Differenz und kartesisches Produkt

Geschachtelte Abfragen

Datenwerte einfügen, verändern oder löschen

Merkmale und Tabellen definieren

Datenschutz und Vergabe von Rechten

Tutorium in SQL

Am IBM Research Lab in San José in Kalifornien wurde Anfang der siebziger Jahre die Sprache SEQUEL definiert, um über eine «Structured English QUEry Language» für relationale Datenbanken zu verfügen. Die Sprache sollte einfach und verständlich sein und gleichzeitig die Forderung nach relationaler Vollständigkeit erfüllen. Anstelle von Operatoren aus der Relationenalgebra (Projektion, Selektion, Verbund u.a.) wurde die einfache Grundform SELECT FROM WHERE vorgeschlagen. Aus Tabellen (FROM) sollen demnach Merkmalswerte ausgewählt werden (SELECT), die eine vom Anwender definierte Selektionsbedingung (WHERE) erfüllen.

SEQUEL wurde aus juristischen Gründen auf Structured Query Language oder SQL umgetauft und international standardisiert (vgl. Abschnitt 1.3). Abfragen jeglicher Art basieren auf der folgenden formalen Beschreibung (Syntax):

SELECT merkmalsname [,merkmalsname ... ]
FROM   tabellenname [,tabellenname ... ]
[WHERE selektionsbedingung]; 

Die Schlüsselwörter SELECT, FROM und WHERE der Sprache SQL sind hier in Groβbuchstaben gegeben. Bei der Verwendung von SQL können die einzelnen Buchstaben dieser Schlüsselwörter beliebig in Groβ- oder Kleinschrift erscheinen; zwingend ist hingegen die buchstabengenaue Formulierung der Schlüsselwörter. Im folgenden Tutorium werden die Schlüsselwörter ausschlieβlich groβ geschrieben, damit die Grammatik von SQL besser zur Geltung kommt.

Alle Angaben, die bei der Syntaxdefinition in eckigen Klammern […] stehen, können weggelassen werden. Bei einer Grammatik einer formalen Sprache hat man sich geeinigt, fakultative Angaben durch eckige Klammern anzugeben.

Die kursiv geschriebenen Ausdrücke wie merkmalsname, tabellenname oder selektionsbedingung müssen vom Anwender spezifiziert werden. Er gibt an, welche Merkmale ihn bei einer Abfrage interessieren, aus welchen Tabellen diese Merkmalswerte stammen und welche Einschränkungen (selektionsbedingung) gelten.

Einfache Abfragen

Möchte man alle Mitarbeitenden aus der Tabelle MITARBEITER aus Abb. 3-7 auflisten, so geschieht das mit SQL wie folgt:

SELECT  M#, Name, Straβe, Ort, Unt(1)
FROM    MITARBEITER; 

Diese SQL-Abfrage zeigt alle Tupel aus der Mitarbeitertabelle mit ihren entsprechenden Merkmalswerten. Da solche Abfragen oft vorkommen, kann man in der SELECT-Klausel anstelle der Angabe sämtlicher Attribute einer Tabelle das Zeichen * verwenden:

SELECT  *(2)
FROM    MITARBEITER; 

Diese verkürzte Form einer Abfrage ergibt dieselbe Resultatstabelle wie die Anweisung (1). Die Ausdrücke (1) und (2) sind also äquivalent.

Die Verwendung des Sternsymbols in der SELECT-Klausel verlangt, die ursprüngliche Definition der SQL-Syntax wie folgt zu erweitern:

SELECT  { * | merkmalsname [,merkmalsname ... ] } 
FROM    tabellenname [,tabellenname ... ]
[WHERE  selektionsbedingung]; 

Nun steht in der SELECT-Klausel ein Ausdruck in geschweiften Klammern. Die Teilausdrücke in der geschweiften Klammer sind durch das Trennsymbol « | » separiert, was eine Auswahl vom SQL- Anwender verlangt: Entweder spezifiziert er in der SELECT-Klausel die gewünschten Merkmale durch das Sternsymbol oder er nennt die einzelnen Merkmale beim Namen.

Projektionen

Interessiert man sich bei den Mitarbeitenden lediglich um deren Mitarbeiternummer, dem Namen und Ort, so ergibt sich in SQL die folgende Abfrage:

SELECT  M#, Name, Ort(3)
FROM    MITARBEITER; 

Soll eine Liste der Wohnorte der Mitarbeitenden zusammengestellt werden, genügt folgende Anweisung:

SELECT  Ort(4)
FROM    MITARBEITER; 

Als Resultatstabelle erhält man eine einspaltige Tabelle mit den Ortschaften Frenkendorf, Liestal, Basel und Liestal. Da SQL im Gegensatz zur Relationenalgebra Duplikate nicht eliminiert, muss bei der SELECT-Klausel das Wort DISTINCT eingefügt werden:

SELECT  DISTINCT Ort(5)
FROM    MITARBEITER; 

Die Abfrage (5) ergibt die gewünschte Tabelle mit den Ortschaften Frenkendorf, Liestal und Basel. Der Ort Liestal erscheint in der Resultatstabelle nur einmal, da Mehrfachnennungen durch DISTINCT eliminiert wurden. Korrekterweise muss hier angefügt werden, dass die Resultatstabelle der Abfrage (4) gar keine Tabelle im Sinne des Relationenmodells darstellt, da jede Relation per Definition eine Menge ist und die in Abschnitt 1.2 genannten Anforderungen erfüllen muss.

Mit der Einführung des neuen Schlüsselwortes DISTINCT muss die Syntax von SQL erweitert werden:

SELECT  [DISTINCT] { * | merkmalsname [,merkmalsname ... ] } 
FROM    tabellenname [,tabellenname ... ]
[WHERE  selektionsbedingung]; 

Projektionen können mit Selektionen kombiniert werden, worauf im nächsten Absatz näher eingegangen wird.

Qualifizierte Abfragen

Mit der Hilfe von Selektionsbedingungen lassen sich qualifizierte Abfragen durchführen. Interessiert man sich für die Mitarbeitenden, die in Liestal wohnen und in der Abeiltung A6 arbeiten, so lautet die entsprechende Abfrage in SQL:

SELECT * (6) 
FROM   MITARBEITER
WHERE  Ort = 'Liestal' AND Unt = 'A6';

Wie wir wissen, erhalten wir aus unserer Mitarbeitertabelle den Mitarbeiter Becker mit seinen Merkmalen (vgl. Abb. 3-8).

Selektionsbedingungen können die logischen Operationen AND (logisches Und), OR (logisches Oder) und NOT enthalten. Falls notwendig, müssen die Teilaussagen durch Klammern kenntlich gemacht werden, da ein logischer Ausdruck immer von links nach rechts ausgewertet wird. Zudem gilt die Regel, dass NOT vor AND und OR, und dass AND vor OR ausgewertet werden.

Möchte man in der Abfrage (6) lediglich die Mitarbeiternummer und den Namen aufgelistet haben, so kann die Selektion (6) mit einer Projektion zur folgenden Abfrage umformuliert werden:

SELECT M#, Name(7)
FROM   MITARBEITER
WHERE  Ort = 'Liestal' AND Unt = 'A6'; 

Der Abfrage (7) sieht man nicht direkt an, dass es sich hier um die Kombination einer Selektion mit einer Projektion handelt. Erst beim genaueren Hinsehen fällt auf, dass in der SELECT-Klausel nur eine Teilmenge der Merkmale aus MITARBEITER steht (Projektion) und in der WHERE-Klausel zusätzlich eine Einschränkung spezifiziert ist (Selektion).

Interessiert man sich für Mitarbeitende aus der Tabelle PERSONAL (vgl. Abb. 3-12), die mehr als 50000 Schweizer Franken verdienen im Jahr, genügt folgende Selektion:

SELECT *(8)
FROM   PERSONAL
WHERE  Lohn > 50000; 

Als Resultat erhält man die Tupel von Schweizer, Huber und Becker.

Verschiedene Vergleichsoperationen sind bei den Selektionsbedingungen zugelassen. Die bekannten Symbole sind «=» für Gleichheit, «<» für kleiner als, «>» für gröβer als, «<=» für kleiner oder gleich als, «>=» für gröβer oder gleich als und «<>» für ungleich.

Möchte man anstelle des Jahreslohns das monatliche Gehalt berechnen, kann man direkt in der SELECT-Klausel die entsprechende Berechnung vornehmen:

SELECT M#, Name, Lohn / 12 AS Monatsgehalt(9)
FROM   PERSONAL; 

Die Resultatstabelle hat nun die Spaltenüberschriften M#, Name und Monatsgehalt, da das Merkmal Lohn mit dem Schlüsselwort AS auf Monatsgehalt umbenannt wurde. Gleichzeitig wird die Berechnung des Monatsgehalts als Division des Jahreslohns durch 12 vorgenommen.

Die Syntax von SQL müsste nun in der SELECT-Klausel wiederum angepasst werden, indem man neben merkmalsname auch merkmalsberechnung (z.B. für Addition, Subtraktion, Multiplikation und Division) zulässt resp. Attribute umnennen kann (durch [AS neuermerkmalsname]).

Abfragen mit Zeichenerkennung

Der SQL-Standard verwendet zwei Symbole für das Erkennen von Zeichen (pattern matching):

  • Das %-Zeichen steht für eine beliebige Zeichenkette von einem oder mehreren Mit dem Schlüsselwort LIKE kann in der WHERE-Klausel nach unterschiedlichen Wörtern oder Teilwörtern gesucht werden.
  • Das Underscore-Zeichen «_» stellt einen einzelnen Buchstabenkandidaten Es kann ebenfalls mit dem LIKE-Schlüsselwort für die Suche von Textstrings verwendet werden.

Als Beispiel für die beiden Suchoptionen «%» und «_» soll das Merkmal Ort aus der Tabelle MITARBEITER dienen, um bestimmte Ortschaften gezielt suchen zu können. Interessiert man sich für die Mitarbeitenden, die in einer Ortschaft mit dem Anfangsbuchstaben B wohnen, so hilft das LIKE-Schlüsselwort mit dem %-Zeichen:

SELECT *(10)
FROM   MITARBEITER
WHERE  Ort LIKE 'B%'; 

Die Abfrage ergibt als Resultat ein einziges Tupel, nämlich den Mitarbeitenden Huber, der in Basel wohnt. Würde man nach Ortschaften mit LIKE ‘%a%’ suchen, würde man alle Wohnorte finden, die den Buchstaben «a» enthalten. In der Abfrage (10) würde man damit die Mitarbeiter Meier, Huber und Becker erhalten, da diese in Basel resp. Liestal wohnen.

Möchte man alle  Ortschaften in  der Tabelle MITARBEITER suchen, die fünf Buchstaben haben und mit B beginnen, so dient das Underscore-Symbol:

SELECT *(11)
FROM   MITARBEITER
WHERE  Ort LIKE 'B____'; 

Mit der Kombination NOT LIKE können in der WHERE-Klausel auch Wortkombinationen ausgeschlossen werden, um Resultatstupel zu erhalten.

Abfragen mit Aggregationsfunktionen

Wie bei der Einführung von SQL in Abschnitt 3.4.1 erwähnt, erlaubt die Sprache die folgenden Aggregationsfunktionen:

  • COUNT(merkmalsname) zählt  die  Anzahl  der  Werte  in  der Spalte
  • SUM(merkmalsname) gibt die Summe der Spaltenwerte zurück
  • AVG(merkmalsname) berechnet den Durchschnitt der Spaltenwerte
  • MIN (merkmalsname) evaluiert den kleinsten Wert innerhalb der Spalte
  • MAX(merkmalsname) nennt den gröβten Wert in der Spalte

Soll die Anzahl der Mitarbeitenden berechnet werden, geschieht dies durch:

SELECT COUNT(M#)(12) 
FROM   MITARBEITER; 

Die Resultatstabelle der Abfrage (9) ist degeneriert zu einer einspaltigen Tabelle mit einem einzigen Wert; im aktuellen Beispiel der Tabelle MITARBEITER ergibt die Zählfunktion den Wert 4 für die Mitarbeiter Schweizer, Meier, Huber und Becker.

Muss für die Budgetierung die gesamte Lohnsumme des Personalbestands bekannt sein, kann die Aggregationsfunktion SUM verwendet werden:

SELECT SUM(Lohn) AS Lohnsumme(13)
FROM   MITARBEITER; 

Auch hier ergibt sich eine degenerierte Tabelle mit dem Merkmal Lohnsumme und dem einzigen Wert von 270000 Schweizer Franken, als Summe der Jahresgehälter der Mitarbeitenden.

Zusammensetzen von Tabelleninhalten (Join)

Der Verbundsoperator wurde in Abschnitt 3.2.3 behandelt, die Realisierung in SQL in Abschnitt 3.4.1 gegeben. Ein Verbund oder Join kann mehr als zwei Tabellen betreffen. Allerdings müssen dann die entsprechenden Verbundsprädikate in der WHERE-Klausel korrekt spezifiziert sein. Als Beispiel eines mehrfachen Verbundes soll die Tabelle EINKAUF aus den drei Tabellen KÄUFER, WEIN und PRÄFERENZ zurückgewonnen werden (vgl. Abschnitt 2.4.5 resp. Abb. 2-22):

SELECT *(14)
FROM   KÄUFER, WEIN, PRÄFERENZ
WHERE  KÄUFER.Weinsorte = WEIN.Weinsorte AND WEIN.Jahrgang = PRÄFERENZ.Jahrgang; 

Das Resultat dieses Verbunds gibt exakt die Tabelle EINKAUF mit den vier Tupeln der Käufer Schweizer, Meier, Huber und Becker. Die FROM-Klausel in der Abfrage (14) listet drei Tabellen, die in der WHERE-Klausel durch zwei Verbundsprädikate kombiniert werden. Das erste Verbundsprädikat verbindet die Tabelle KÄUFER mit der Tabelle WEIN über das gemeinsame Merkmal Weinsorte, das zweite die Tabelle WEIN mit PRÄFERENZ über den Jahrgang. Würden die Verbundsprädikate weggelassen, so erhielte man eine fehlerhafte Einkaufsliste (kartesisches Produkt), bei der jeder Mitarbeitende alle Weine und Jahrgänge beanspruchen würde.

Für die Formulierung von komplizierten SQL-Abfragen drängt sich die Verwendung von Ersatznamen (Alias) auf. So kann die Abfrage (14) vereinfacht wie folgt formuliert werden:

SELECT *(15)
FROM   KÄUFER k, WEIN w, PRÄFERENZ p
WHERE  k.Weinsorte = w.Weinsorte AND w.Jahrgang = p.Jahrgang; 

Hier wird das Alias als Ersatz für den Tabellennamen verwendet. Dies ist vor allem dann wichtig, wenn ein und dieselbe Tabelle in der WHERE-Klausel mehrfach angesprochen werden soll. Ein Verbund einer Tabelle mit sich selbst ist zwar ein seltener Fall, doch muss dann auf die Verwendung von Ersatznamen zurückgegriffen werden.

Sortieren und Gruppieren von Tupeleinträgen

Gemäβ der Definition einer Tabelle ist die Reihenfolge der Tupel innerhalb einer Tabelle irrelevant (vgl. Abschnitt 1.2). Bei der Auswertung von Tabellen hingegen ist man oft interessiert, die Tupeleinträge oder Zeilen in einer bestimmten Reihenfolge aufgelistet zu erhalten. Dies ermöglicht das Schlüsselwort ORDER BY mit den möglichen Zusätzen ASC (ascending oder aufsteigend) resp. DESC (descending oder absteigend):

SELECT    *(16)
FROM      MITARBEITER
ORDER BY  Name ASC; 

Die Anweisung (16) erstellt eine Mitarbeitertabelle, wobei die Tupeleinträge nach aufsteigenden Namen sortiert sind. Somit erscheinen zuerst die Angaben von Becker, dann von Huber, Meier und Schweizer.

Manchmal ist es wichtig, innerhalb einer Resultatstabelle einzelne Teilgruppen zu bilden. Beispielsweise könnte man sich bei der Tabelle PERSONAL aus Abb. 3-12 für die Lohnsummen einzelner Abteilungen interessieren. Eine solche Gruppenbildung wird durch die Schlüsselworte GROUP BY ermöglicht:

SELECT    Unt, COUNT(M#) AS Anzahl, SUM(Lohn) AS Summe(17)
FROM      PERSONAL
GROUP BY  Unt
ORDER BY  Unt; 

Die Abfrage (17) ergibt eine Liste der Abteilungen mit der Nennung der jeweiligen Anzahl Mitarbeitenden sowie der Lohnsumme pro Abteilung; sie ist sortiert nach Abteilungen (Unt). Die Resultatstabelle sieht demnach wie folgt aus:

Möchte man anstelle der Abteilungsnummern die Namen der Abteilungen in der ersten Spalte genannt haben, so muss die Abfrage (17) um einen Verbund erweitert werden:

SELECT    Bezeichnung, COUNT(M#) AS Anzahl, SUM(Lohn) AS Summe(18)
FROM      PERSONAL, ABTEILUNG 
WHERE     PERSONAL.Unt = ABTEILUNG.A#
GROUP BY  Bezeichnung
ORDER BY  Bezeichnung; 

Entsprechend lautet nun die Resultatstabelle der Abfrage (18) wie folgt:

Die GROUP BY Anweisung kann durch das Schlüsselwort HAVING erweitert werden, falls bei Gruppierungen weitere Einschränkungen gelten sollen. Der Zusatz HAVING ist nur bei der Verwendung von Aggregationsfunktionen zulässig, da er sich nicht auf einzelne Tupel sondern auf Tupelgruppierungen bezieht. Möchte man eine Liste der Abteilungen mit der Anzahl Mitarbeitenden und der Lohnsumme, allerdings nur für gröβere Abteilungen mit mehr als einem Mitarbeitenden, so kann die Abfrage (18) wie folgt ergänzt werden:

SELECT    Bezeichnung, COUNT(M#) AS Anzahl, SUM(Lohn) AS Summe(19)
FROM      PERSONAL, ABTEILUNG 
WHERE     PERSONAL.Unt = ABTEILUNG.A#
GROUP BY  Bezeichnung HAVING Count(M#) > 1
ORDER BY  Bezeichnung; 

Das Resultat der Abfrage (19) ist eine einzige Zeile, nämlich die Finanzabteilung mit 2 Mitarbeitenden und der Lohnsumme 140000 Schweizer Franken.

Vereinigung, Durchschnitt, Differenz und kartesisches Produkt

Die mengenorientierten Operatoren der Relationenalgebra finden ihre Entsprechung im SQL-Standard. Möchte man z.B. die vereinigungsverträglichen Tabellen SPORTCLUB mit dem FOTOCLUB vereinen, so geschieht das in SQL mit dem Schlüsselwort UNION:

SELECT    *(20)
FROM      SPORTCLUB
   UNION
SELECT    *
FROM      FOTOCLUB;

In der Anweisung (20) werden die Mitglieder des Sportclubs und des Fotoclubs selektiert. Da die beiden Tabellen vereinigungsverträglich sind, enthält die Resultatstabelle alle Sport- und Fotoclubmitglieder, wobei Duplikate eliminiert werden.

Möchte man alle Sportclubmitglieder herausfinden, die nicht gleichzeitig im Fotoclub mitwirken, so erfolgt die Abfrage mit dem Differenzoperator (Schlüsselwort EXCEPT):

SELECT     *(21)
FROM       SPORTCLUB
    EXCEPT
SELECT     *
FROM       FOTOCLUB; 

Gemäβ Abschnitt 3.2.2 ist Huber sowohl im Sport- wie im Fotoclub engagiert und er wird deshalb von den Sportclubmitgliedern subtrahiert. Als Resultat dieser Subtraktion verbleiben die beiden Sportclubmitglieder Meier und Schweizer.

Bei vereinigungsverträglichen Tabellen können Durchschnitte gebildet werden. Interessiert man sich für die Mitglieder, die sowohl im Sportclub wie im Fotoclub mitwirken, so kommt das Schlüsselwort INTERSECT zum Zuge:

SELECT         *(22)
FROM           SPORTCLUB
     INTERSECT
SELECT         *
FROM           FOTOCLUB; 

Da sich nur das Mitglied Huber in beiden Clubs eingeschrieben hat, besteht die Resultatstabelle lediglich aus einem Tupeleintrag.

Zu den mengenorientierten Operatoren zählt das kartesische Produkt, bei dem beliebige Tabellen miteinander multipliziert werden. Wie in Abschnitt 3.2.3 erwähnt, macht die Multiplikation beliebiger Tabellen oft wenig Sinn. Werden die Tupeleinträge der Tabelle MITARBEITER mit den Informationen aus der Tabelle ABTEILUNG miteinander kombiniert, so erhält man gemäβ der Abb. 3-9 auch Kombinationen von Tupeln, die in der Realität nicht vorkommen. Das entsprechende SQL-Statement würde wie folgt lauten:

SELECT  *(23)
FROM    MITARBEITER, ABTEILUNG; 

Anstelle des karteischen Produkts wäre hier ein Verbund angesagt, der die Mitarbeitenden mit ihren zugehörigen Abteilungsinformationen zusammenstellen würde. Allerdings müsste für einen korrekten Verbund dann auch das Verbundsprädikat spezifiziert werden:

SELECT  *(24)
FROM    MITARBEITER, ABTEILUNG 
WHERE   Unt = A#; 

Mit den Abfragen (23) und (24) wird nochmals aufgezeigt, dass kartesisches Produkt und Verbund miteinander verwandt sind; der Verbund stellt ein eingeschränktes kartesisches Produkt dar.

Geschachtelte Abfragen

Es ist erlaubt und manchmal notwendig, innerhalb eines SQL-Statements einen weiteren SQL-Aufruf zu formulieren. Man spricht in diesem Zusammenhang von geschachtelten Abfragen. Solche Abfragen sind z.B. bei der Suche des Mitarbeitenden mit dem höchsten Lohn sinnvoll:

SELECT  M#, Name(25)
FROM    PERSONAL
WHERE   Lohn >= ALL (SELECT Lohn 
                     FROM PERSONAL); 

Die Anweisung (25) enthält innerhalb der WHERE-Klausel ein weiteres SQL-Statement, um die Gehälter aller Mitarbeitenden zu selektieren (innerer SQL-Ausdruck resp. Subquery). Im äuβeren SQL-Statement wird nochmals die Tabelle PERSONAL konsultiert, um denjenigen Mitarbeiter mit M# und Namen zu erhalten, der den höchsten Lohn erzielt. Das Schlüsselwort ALL bedeutet, dass die Bedingung «Lohn gröβer gleich …» für alle selektierten Lohnanteile (Resultat der Subquery) gelten muss.

Das Schlüsselwort SOME verlangt mindestens ein Element, das die entsprechende Bedingung erfüllt. Aus diesem Grunde erlaubt der SQL-Standard, anstelle des Schlüsselwortes SOME das reservierte Wort ANY zu verwenden, da es für den gelegentlichen Benutzer eventuell verständlicher ist.

Der Existenzquantor der Aussagenlogik («… es existiert ein Element, für welches gilt …») wird im SQL-Standard durch das Schlüsselwort EXISTS ausgedrückt. Dieses Schlüsselwort wird bei einer SQL-Auswertung auf «wahr» gesetzt, falls die nachfolgende Subquery mindestens ein Element resp. eine Zeile selektiert.

Als Beispiel einer Abfrage mit einem EXISTS-Schlüsselwort können wir die Projektzugehörigkeit ZUGEHÖRIGKEIT aus Abb. 2-18 heranziehen, die aufzeigt, welche Mitarbeitenden an welchen Projekten arbeiten. Interessieren wir uns für die Mitarbeiter, die keine Projektarbeit leisten, so lautet das SQL-Statement wie folgt:

SELECT  M#, Name, Straβe, Ort(26)
FROM    MITARBEITER m
WHERE   NOT EXISTS (SELECT * 
                    FROM ZUGEHÖRIGKEIT z 
                    WHERE m.M# = z.M#); 

In der äuβeren Anweisung (26) wird die vollständige Tabelle MITARBEITER benutzt, wie sie z.B. in der Abb. 2-19 gegeben ist. Aus dieser Tabelle werden die Namen und Adressen derjenigen Mitarbeitenden selektiert, die keine Projektzugehörigkeit haben. Dazu wird eine Subquery formuliert, um alle Mitarbeiter-Projekt-Zugehörigkeiten (Beziehungen) zu erhalten. Im Auschlussverfahren (NOT EXISTS) erhalten wir die gewünschten Mitarbeitenden, die keine Projektarbeit leisten.

In der Abfrage (26) wird nochmals ersichtlich, wie nützlich Ersatznamen (Alias) bei der Formulierung von SQL-Anweisungen sind. Um das Verbundsprädikat im inneren SQL-Statement auszudrücken, wird die Tabelle MITARBEITER mit dem neuen Namen m und die Tabelle ZUGEHÖRIGKEIT mit dem Namen z umbenannt. Das Verbundsprädikat m.M# = z.M# ersetzt demnach das umständlichere Prädikat MITARBEITER.M# = ZUGEHÖRIGKEIT.M#.

Datenwerte einfügen, verändern oder löschen

Wie wir wissen, ist SQL nicht nur eine Abfragesprache (query language), sondern auch eine Datenmanipulationssprache (Data Manipulation Language oder DML). Die entsprechenden Sprachelemente sind Einfüge- (INSERT), Änderungs- (UPDATE) und Löschoperationen (DELETE).

Soll der neue Mitarbeiter Müller in der Finanzabteilung A6 seine Tätigkeit aufnehmen, so muss die Tabelle MITARBEITER um ein Tupel erweitert werden:

INSERT INTO MITARBEITER(27)
VALUES ('M20', 'Müller', 'Riesweg', 'Olten', 'A6'); 

Möchte man einzelne Werte in der Tabelle PERSONAL aus Abb. 3-12 ändern, so geschieht dies mit dem UPDATE-Statement. Eine generelle Lohnanpassung von 5% für alle Mitarbeitenden erfolgt durch die Anweisung:

UPDATE  PERSONAL(28)
SET     Lohn = Lohn * 1.05; 

Die Anweisung (28) zeigt nochmals auf, dass Manipulationsoperationen mengenorientiert erfolgen können. Möchte man individuelle Lohnanpassungen vornehmen, müsste man die Anweisung (28) um eine WHERE-Klausel erweitern.

Die Löschanweisung ist insofern heikel, weil hier die Regeln der referenziellen Integrität berücksichtigt werden.  Ist beispielsweise eine fortgesetzte Löschregel (ON DELETE CASCADE, siehe Abschnitt 3.8) für die Tabelle ABTEILUNG vorgesehen, so erwirkt der folgende SQL-Befehl Löschvorgänge in der abhängigen Tabelle MITARBEITER:

DELETE(29)
FROM   ABTEILUNG
WHERE  Bezeichnung = 'Informatik'; 

Die Anweisung (29) löscht demnach nicht nur die Informatikabteilung in der Tabelle ABTEILUNG, sondern auch alle Mitarbeitenden der Tabelle MITARBEITER, die in dieser Abteilung arbeiten. Im Beispiel betrifft dieser Löschvorgang den Mitarbeiter Meier.

Merkmale und Tabellen definieren

Die Sprache SQL erlaubt, Merkmale und Tabellen zu definieren (Data Definition Language oder DDL). Als Datentypen gibt der SQL-Standard unterschiedliche Formate vor:

  • CHARACTER(n) oder CHAR(n) bedeutet eine Sequenz von Buchstaben fester Länge
  • CHARACTER VARYING oder VARCHAR erlaubt die Spezifikation von Buchstabenfolgen beliebiger Länge.
  • Numerische Daten werden mit den Datentypen NUMERIC oder DECIMAL festgelegt, wobei Angaben zur Gröβe und Genauigkeit spezifiziert werden müssen.
  • Ganze Zahlen lassen sich durch INTEGER oder SMALLINT
  • Der Datentyp DATE gibt Datumsangaben durch YEAR, MONTH und DAY Dabei gelangen unterschiedliche Formate zur Anwendung, so z.B. (yyyy,mm,dd) für Jahres-, Monats- und Tagesangaben (vgl. Abschnitt 6.3 über temporale Datenbanken).
  • Der Datentyp TIME liefert Zeitangaben in HOUR, MINUTE und
  • Der Datentyp TIMESTAMP ist eine Kombination des Typs DATE und TIME. Zusätzlich können die Präzision der Zeitangabe sowie die Zeitzone festgelegt
  • Daneben gibt es noch weitere Datentypen für Bit-Strings (BIT oder BIT VARYING) sowie für umfangreiche Objekte (CHARACTER LARGE OBJECT oder BINARY LARGE OBJECT). Zudem wird die Einbindung von XML (Extensible Markup Language, siehe Abschnitt 2) unterstützt.

Der Benutzer kann weitere Datentypen definieren, die ihm das Arbeiten mit relationalen Datenbanken erleichtern. Spielt beispielsweise das Geschlecht für eine Anwendung eine Rolle, lässt sich dies wie folgt festlegen:

CREATE DOMAIN Geschlecht AS VARCHAR (30)
     DEFAULT 'männlich'
     CHECK   (VALUE IN ('männlich','weiblich')); 

Der Wertevorrat des neuen Datentyps Geschlecht besteht aus den beiden Werten männlich und weiblich. Das entsprechende Schlüsselwort CHECK erlaubt, Integritätsbedingungen für den Wertevorrat zu formulieren (vgl. Abschnitt 3.8).

Falls keine Angaben bei einer Einfügeoperation betreffend des Geschlechts gemacht werden, soll das Geschlecht den Wert männlich enthalten (Schlüsselwort DEFAULT).

Vorgegebene oder selbst definierte Datentypen werden bei der Definition einer Tabelle benötigt. Das Grundgerüst einer Tabellendefinition sieht wie folgt aus:

CREATE TABLE tabellenname
     (merkmalsname datatype [UNIQUE] [NOT NULL] [, … ] 
     [PRIMARY KEY (merkmalsliste),]
     {[FOREIGN KEY (merkmalsliste) 
           REFERENCES  referenziertetabelle 
           [ON DELETE löschoption]]}); 

Bei der Definition einer Tabelle müssen somit die Merkmale (Attribute) mit ihren Wertebereichen (Datentypen) spezifiziert werden. Der Zusatz UNIQUE bedeutet, dass alle Werte des Merkmals eindeutig sind; Wiederholungen von Datenwerten werden vom Datenbanksystem verweigert. Das fakultative Schlüsselwort NOT NULL untersagt Nullwerte.

Neben der Angabe von Primärschlüsseln können auch Fremdschlüssel angegeben werden, falls auf weitere Tabellen referenziert wird. Regeln der referenziellen Integrität werden ebenfalls spezifiziert, so z.B. Löschregeln mit Nullsetzten, restriktives Löschen oder fortgesetzte Löschung (vgl. Abschnitt 3.8).

Die Definition der Tabelle MITARBEITER aus Abb. 3-7 lautet demnach:

CREATE TABLE MITARBEITER (31) 
        (M#          CHAR (6) UNIQUE,
         Name	     CHAR (20) NOT NULL,
         Strasse     VARCHAR (255),
         Ort         VARCHAR (255),
         Unt         CHAR (2), 
         PRIMARY KEY (M#), 
         FOREIGN KEY (Unt) REFERENCES ABTEILUNG(A#) ON DELETE SET NULL); 

Die Tabelle MITARBEITER enthält die Merkmale M#, Name, Straβe und Ort sowie das Fremdschlüsselmerkmal Unt (Unterstellung). Der Primärschlüssel der Tabelle ist die Mitarbeiternummer. Als Fremdschlüssel gilt das Merkmal Unt, welches die Tabelle ABTEILUNG mit dem Primärschlüssel A# referenziert. Bei einem Löschvorgang in der Tabelle ABTEILUNG sollen die betroffenen Fremdschlüsselwerte in der Mitarbeitertabelle auf Null gesetzt werden (ON DELETE SET NULL), damit die Angaben der Mitarbeiter nicht verloren gehen.

Mit dem SQL-Befehl ALTER TABLE können Tabellendefinitionen verändert werden. Der Befehl DROP TABLE erlaubt das Löschen von Tabellen, eventuell mit der Eliminierung aller Tupeleinträge.

Soll der Zugriff auf einzelne Merkmale einer Tabelle beschleunigt werden, lassen sich mit dem Befehl CREATE INDEX Zugriffsstrukturen definieren. DROP INDEX entfernt entsprechende Indices.

Datenschutz und Vergabe von Rechten

Die Definition von Sichten ermöglicht gemäβ Abschnitt 3.7, personenbezogene Angaben besser zu schützen. Die Definition einer Sicht erfolgt mit dem SQL-Befehl CREATE VIEW:

CREATE VIEW sichtenname AS
     SELECT merkmalsname [,merkmalsname … ] 
     FROM   tabellenname [,tabellenname … ] 
     [WHERE selektionsbedingung] ; 

Sichten erhalten einen Namen und können durch eine Selektion beliebiger Datenwerte festgelegt werden.

Möchte man die Lohnangaben in der Tabelle PERSONAL nicht allen Mitarbeitenden zukommen lassen, drängt sich eine Sicht mit dem Namen MITARBEITER auf:

CREATE VIEW MITARBEITER AS (32)
    SELECT  M#, Name, Ort, Unt 
    FROM    PERSONAL; 

Damit die Angehörigen der Personalabteilung nur einen eingeschränkten Zugriff auf die Lohnangaben erhalten, können verschiedene Lohnklassen und Zugriffsrechte definiert werden (vgl. Abb. 3-12):

CREATE VIEW GRUPPE_A AS	(33)
    SELECT  M#, Name, Lohn, Unt
    FROM    PERSONAL
    WHERE   Lohn BETWEEN 70000 AND 90000; 

Für die Lohnbandbreite zwischen 70000 und 90000 Schweizer Franken wird mit der Hilfe des Schlüsselwortes BETWEEN eine Sicht mit dem Namen GRUPPE_A definiert. Ausgewählte Mitarbeitende der Personalabteilung erhalten einen Zugriff auf diese Sicht, um Lohnangaben nachführen zu können.

Zugriffsrechte auf Tabellen oder Sichten können mit dem SQL-Befehl GRANT vergeben werden:

GRANT   { privilegienliste | ALL PRIVILEGES } 
ON      { tabellenname | viewname }
TO      { berechtigtenliste | PUBLIC } 
                [WITH GRANT OPTION]; 

Als privilegienliste gelten die SQL-Befehle SELECT, DELETE, INSERT und UPDATE. In der berechtigtenliste werden die Benutzer aufgeführt, die ein Zugriffsrecht erhalten sollen; das Schlüsselwort PUBLIC vergibt das entsprechende Recht an jedermann. Der Zusatz WITH GRANT OPTION erlaubt, dass der berechtigte Benutzer seine Rechte weitergeben kann.

Möchte man den lesenden Zugriff auf die Tabelle (resp. Sicht) MITARBEITER allen Angestellten ermöglichen, lautet der GRANT-Befehl:

GRANT	SELECT  (34)
ON      MITARBEITER
TO      PUBLIC; 

Soll der Zugriff und ein Änderungsrecht auf die Sicht GRUPPE_A selektiv an den Personalverantwortlichen mit der Identifikation ID37289 vergeben werden, geschieht das wie folgt:

GRANT	UPDATE (35)
ON      GRUPPE_A
TO      ID37289 WITH GRANT OPTION; 

Der Personalverantwortliche kann mit der GRANT OPTION seine Rechte z.B. während Abwesenheiten an Stellvertreter weitergeben.

Die Rücknahme von Rechten erfolgt mit dem Befehl REVOKE auf ähnliche Weise:

REVOKE	[GRANT OPTION FOR]
        { privilegienliste | ALL PRIVILEGES }
ON      { tabellenname | viewname }
FROM    { berechtigtenliste | PUBLIC } 
                 [RESTRICT | CASCADE]; 

Die Schlüsselworte RESTRICT und CASCADE erlauben bei der Rücknahme von Privilegien, nur die Rechte gemäβ der berechtigtenliste zurückzunehmen (RESTRICT) oder die weitergegebenen Rechte mit CASCADE ebenfalls einzufordern.

Verlässt der Mitarbeiter mit der Identifikation ID37289 die Firma, werden ihm sinnvollerweise sämtliche Rechte weggenommen:

REVOKE	ALL PRIVILEGES (36)
ON      GRUPPE_A,  MITARBEITER
FROM	ID37289 CASCADE; 
Designed & Developed by Minh Tue NGUYEN