SQL in Visual FoxPro

Wer oder was ist eigentlich SQL?

SQL (Structured Query Language) ist eine Sprache zur Abfrage und Manipulation von relationalen Datenbanken. Die Sprache wurde in den 70er Jahren von IBM entwickelt und 1986 zum ANSI-Standard erhoben. 1987 folgte die Übernahme des Standards durch die ISO. 1989 wurde der SQL-Standard erstmals offiziell erweitert, wobei der 86er Standard als SQL89/Level 1 übernommen wurde. Die letzte offizielle Erweiterung des SQL-Standards ist von 1992 und wird SQL2 oder SQL92 genannt. An SQL3 wird derzeit gearbeitet, dabei sollen sowohl prozedurale als auch objektorientierte Erweiterungen der Sprache vorgesehen sein.

SQL ist eine mengenorientierte, nicht-prozedurale Sprache. Sie wird zu den Sprachen der 4. Generation gerechnet.

Mengenorientiert heißt: Gegenstand eines SQL-Befehls ist niemals ein einzelner Satz einer Tabelle, sondern immer mindestens eine komplette Tabelle. Ergebnis eines SQL-Befehls ist immer eine Aktion (z.B. Auswahl und Anzeige, aber auch Update) auf einer Anzahl von Sätzen, dem CurrentSetOfRecords (Cursor). Das die Anzahl der Sätze des Cursors auch mal 0 oder 1 sein kann, ist klar. Die Anzahl der Sätze, für die eine Aktion formuliert wird, hängt nicht von der Syntax des SQL-Befehls ab, sondern vom Inhalt der zu Grunde liegenden Daten.

Nicht-prozedural heißt: Die Reihenfolge der Klauseln eines SQL-Befehls ist im Grunde unerheblich und hat nichts mit der Reihenfolge der Verarbeitung zu tun. Mit einem SQL-Befehl teilen wir dem DBMS mit, was wir als Ergebnis haben wollen. Die Vorgehensweise, die das DBMS benutzt, um das Ergebnis zu produzieren, kann von uns nicht beeinflußt werden.

Was ist SQL nicht?

SQL ist keine vollständige Programmiersprache. Dazu fehlen SQL vor allem Variablen und Kontrollstrukturen. Deshalb wird SQL von allen DBMS-Herstellern um eigene Konstrukte erweitert. Die SQL3-Norm, die derzeit erarbeitet wird, soll diesem Mangel abhelfen und den entstandenen Wildwuchs beseitigen. Die Erfinder von SQL und die Standardisierungskomitees haben das Fehlen von prozeduralen Kontrollstrukturen allerdings nie als Mangel angesehen. Das Konzept war, SQL in andere 3-GL Sprachen einzubetten. Daraus entstand Embedded SQL, das als Schnittstelle zu COBOL, Fortran, Pascal usw. ebenfalls standardisiert ist.

Die Datenbasis

SQL ist für die Manipulation und Abfrage von relationalen Datenbanken entwickelt worden. In relationalen Datenbanken werden die Daten in Tabellen gespeichert. Eine Tabelle ist eine zweidimensionale Matrix. Die Zeilen dieser Matrix bilden die Datensätze, die Spalten sind die Datenfelder. Um eine Zeile eindeutig wiederfinden zu können, muß mindestens eines der Felder dieser Zeile einen eindeutigen Wert enthalten. Dieser Wert darf in dieser Spalte also nur einmal vorkommen. Eine Spalte, die garantiert keine doppelten Einträge enthält, eignet sich als Identifikationsschlüssel für den eindeutigen Zugriff auf die Tabellenzeilen. Wenn mehrere Identifikationsschlüssel vorliegen, wird einer ausgewählt, der den sogenannten Primärschlüssel bildet. Die anderen, ebenfalls geeigneten Schlüssel heißen Kandidatenschlüssel. Sie sind eben Kandidaten für das ehrenvolle Amt des Primärschlüssels.

Um zwei Tabellen bei Abfragen miteinander verbinden zu können, wird ein Identifikationsschlüssel der einen Tabelle als Spalte der anderen Tabelle übernommen. In dieser Tabelle bildet er damit einen Fremdschlüssel. Ein Fremdschlüssel in einer Tabelle ist also immer ein Primär- oder Kandidatenschlüssel einer anderen Tabelle.

Das relationale Konzept kennt keine festen Verbindungen zwischen Tabellen. Alle Verbindungen werden in den SQL-Befehlen direkt geknüpft. Ein SQL-Befehl muß also immer angeben, welche Tabellen an diesem Verarbeitungsschritt beteiligt sind und wie diese Tabellen miteinander zu verknüpfen sind.

Noch zwei Begriffe, die später wieder auftauchen werden: Eine Auswahl (Teilmenge) von Spalten einer Tabelle nennt man Projektion, eine Teilmenge von Zeilen nennt man Selektion.

Normalisierung

Die Tabellen einer relationalen Datenbank werden normalisiert, das heißt, sie werden einer feststehenden Prozedur unterzogen, solange, bis die Struktur der Tabellen genau vorgegebenen Kriterien entspricht. Ich will das Vorgehen bei der Normalisierung kurz anhand von Daten ähnlich der VFP-Beispielapplikation Tastrade erläutern.

Die bei einem typischen Bestellvorgang anfallenden unstrukturierten Daten werden zunächst in Tabellenform gebracht:

1. Normalform:

Der Schritt zur ersten Normalform heißt: Informationen separieren. Eine Spalte darf nicht mehrere Angaben gleichzeitig enthalten, wie dies in den Spalten „Was“ und „Einzelpreise“ der Fall ist. Wir nutzen die Gelegenheit um gleichzeitig Spalten, die zusammengesetzte Informationen enthalten, aufzuteilen.

2. Normalform

Zur zweiten Normalform gelangen wir, indem wir die Bestandteile des Identifikationsschlüssels einer Tabelle in der ersten Normalform auf funktionale Abhängigkeiten hin untersuchen. Diese Tabelle hat einen zusammengesetzten Schlüssel. Welche Informationen brauchen wir, um eine Zeile dieser Tabelle eindeutig zu bezeichnen:

1. den Kunden,
2. den Artikel und
3. das Bestelldatum

Eine funktionale Abhängigkeit liegt dann vor, wenn eine Spalte automatisch den Wert anderer Spalten festlegt. Zum Beispiel sind alle Spalten mit Adressangaben funktional von der Spalte „Kunde“ abhängig. Die Mengeneinheit und der Einzelpreis sind funktional vom Artikel abhängig. Die Menge und das gewünschte Lieferdatum sind vom Bestelldatum abhängig. Besonderheiten, etwa, daß ein Kunde unterschiedliche Adressen haben oder ein Artikel in verschiedenen Mengeneinheiten und zu verschiedenen Preisen ausgeliefert werden kann, oder daß ein und der selbe Kunde am gleichen Tag den selben Artikel mehrfach bestellen könnte, bleiben dabei zunächst unbeachtet. Dies sind Feinabstimmungen, die später an den normalisierten Tabellen vorgenommen werden.

Funktional voneinander abhängige Spalten werden zu neuen Tabellen zusammengefasst. Diese Tabellen werden, wenn nötig, mit Identifikationsschlüsseln versehen, die als Fremdschlüssel in anderen Tabellen dienen.

Kunden

Artikel

Bestellungen

3. Normalform

Die dritte Normalform (auch BCNF = Boyce Codd Normalform genannt) besagt, daß funktionale Abhängigkeiten nur noch zu Kandidatenschlüsseln bestehen dürfen. Das ist bei der Tabelle „Kunden“ offensichtlich nicht der Fall. Mit „Land“ und „Plz“ steht nämlich auch der Ort fest. „Land“ und „Plz“ zusammen ergeben für die Kundentabelle aber keinen Kandidatenschlüssel.

Befinden sich die anderen Tabellen in der dritten Normalform? Wenn wir die unstrukturierte Information vom Anfang zum Maßstab nehmen, dann nicht. Denn dort ist das Datum der Lieferung funktional abhängig von Kunde und Bestelldatum. Kunde und Bestelldatum zusammen bilden aber keinen Kandidatenschlüssel für die Tabelle „Bestellungen“.

Um zur dritten Normalform zu kommen, teilen wir also nochmal auf:

Kunden

Orte

Artikel

Bestellungs-Kopfdaten

Bestellpositionen

Wir mussten der Tabelle „Orte“ keinen Identifikationsschlüssel hinzufügen, da mit „Land“+“PLZ“ ein, wenn auch zusammengesetzter Identifikationsschlüssel existiert. Bei der Aufteilung der Tabelle „Bestellungen“ hätten wir ebenso vorgehen können. Das hätte allerdings dazu geführt, daß „Kunde“ und „Datum“ als zusammengesetzter Fremdschlüssel in jeder Bestellposition vorgekommen wäre. Mit der Einführung des Primärschlüssels „Bestellung“ haben wir zugleich ermöglicht, daß ein Kunde an einem Tag mehrere Bestellungen aufgeben kann.

Abfragen durchführen: SQL-SELECT

In allen folgenden Beispielen verwende ich die Daten der Beispielapplikation zu Visual FoxPro „Tastrade“, auch wenn diese, z.B. die Tabelle Customer, teilweise nicht in der dritten Normalform vorliegen.

Der wichtigste und mächtigste SQL-Befehl ist der SQL-Select, mit dem Abfragen auf einen bestehenden Datenbestand durchgeführt werden. Manche Leute behaupten, daß man SQL verstanden hat, wenn man den SELECT verstanden hat. Untersuchen wir also als erstes den SQL-Select, dann haben wir das Schwierigste hinter uns.

Die Syntax des SQL-Selects ohne die VFP-spezifischen Zusätze und Einschränkungen ist folgende:

SELECT [ALL | DISTINCT] {Spalten|*}
FROM Tabelle [Alias] [[Jointyp] Tabelle [Alias]]...
[WHERE {Bedingung | Subquery}]
[GROUP BY Spalten [HAVING {Bedingung | Subquery}]]
[ORDER BY Spalten [ASC | DESC]...]

Bevor wir diese Syntax im einzelnen betrachten und die grundsätzliche Vorgehensweise des SQL-Selects untersuchen, zunächst einige Beispiele. Damit’s für diejenigen, die schon mit SQL-Befehlen arbeiten, nicht zu langweilig wird, habe ich VFP-Spezifika und ähnliches hier gleich mit eingeflochten.

Wenn wir eine Auflistung all unserer Kunden haben möchten, erreichen wir dies mit

SELECT *;
FROM Customer

Am Ergebnis dieses kleinen Programms läßt sich schon eine Menge erkennen:

• alle in der oben aufgeführten Syntax in eckigen Klammern stehenden Teile des SQL-Selects können weggelassen werden
• erforderlich ist das Schlüsselwort SELECT, eine Angabe zu den auszuwählenden Spalten (der Projektionsteil) und die Angabe mindestens einer Tabelle nach dem Schlüsselwort FROM
• die Angabe * anstelle einzelner Spalten wählt alle Spalten aller beteiligten Tabellen aus
• FoxPro legt das Ergebnis standardmäßig in einer temporären Tabelle (einem Cursor) namens „Abfrage“ ab, die dann in einem Browse-Fenster angezeigt wird. Diesem Cursor ist ein Arbeitsbereich zugeordnet, der, wie die anderen Arbeitsbereiche auch, angewählt und weiter verarbeitet werden kann. Der Cursor ist allerdings schreibgeschützt. Das erkennen Sie am einfachsten, indem Sie den Menüpunkt „Tabelle“ der VFP-Menüleiste aufklappen. Alle Menüpunkte, die den Inhalt der Tabelle verändern, sind deaktiviert.
• die Sätze werden in der Reihenfolge angezeigt, wie sie in der Ursprungstabelle vorliegen

Sortierung

Bestimmen wir zunächst mal eine andere Reihenfolge:

SELECT *;
FROM Customer;
ORDER BY Country, City

Mit der ORDER BY Klausel (Teile eines SQL-Befehls werden als Klausel bezeichnet) haben wir eine Sortierung nach Ländern und Städten angegeben. Die zuerst angegebene Spalte bestimmt die Grundreihenfolge. Wenn mehrere Sätze des Cursors in dieser Spalte den gleichen Wert aufweisen, so werden sie zusätzlich nach der zweiten Spalte sortiert, usw. Anstelle der Spaltennamen hätten wir auch die Spaltennummer angeben können: ORDER BY 9,6 liefert das gleiche Ergebnis. Bei dieser Spaltennummer handelt es sich um die Nummer der Spalte im Cursor, nicht in der Originaltabelle. Dies gilt übrigens auch für die Spaltennamen. Wie Spalten des Cursors umbenannt werden, zeige ich weiter unten.

Mit den Zusätzen ASC und DESC wird bestimmt, ob aufsteigend oder absteigend sortiert werden soll. Ein solcher Zusatz kann auf jeder Sortierstufe erfolgen. ORDER BY Country DESC, City ASC bestimmt, daß nach Ländern absteigen und innerhalb eines Landes nach Städten aufsteigen sortiert werden soll. ASC ist dabei die Default-Einstellung.

Selektion

In den seltensten Fällen wollen wir alle Zeilen einer Tabelle als Ergebnis des SQL-Selects sehen. Eine Auswahl von Zeilen wird mit der WHERE Klausel getroffen. Wir wollen auch nicht mehr alle Spalten sehen und geben deshalb die in das Ergebnis aufzunehmenden Spalten explizit an:

SELECT customer_id, company_name, contact_name, phone;
FROM Customer;
WHERE min_order_amt > 0;
AND discount > 0;
ORDER BY min_order_amt

Es können beliebig viele Selektionskriterien angegeben werden, die mit den logischen Operatoren AND und OR zu verknüpfen sind. Zur Negation kann der NOT-Operator verwendet werden. Die Verknüpfung der Selektionskriterien beruht auf der Boolschen Algebra. So liefert zum Beispiel der Ausdruck NOT ( FeldA > 0 OR FeldB > 0 ) die gleiche Zeilen wie NOT FeldA > 0 AND NOT FeldB > 0.

Neben den üblichen Vergleichsoperatoren wie gleich (= oder ==), kleiner (<), größer (>) und so weiter, gibt es einige SQL-spezifische Vergleichsoperatoren:

BETWEEN Wert1 AND Wert2

Damit können Sie abprüfen, ob ein Wert zwischen zwei anderen liegt. Die Grenzwerte sind inclusive zu sehen.

IN (Wert1, Wert2, Wert3 ...)

prüft, ob ein Feldwert in einer Wertegruppe vorkommt. Die Klammern müssen dabei gesetzt werden. IN kann auch mit Unterabfragen benutzt werden. Mehr zu Unterabfragen und dabei mögliche Vergleiche steht weiter unten.

LIKE

Dabei handelt es sich um einen Operator für Zeichenkettenvergleiche, wobei bestimmte Zeichen über zwei Wildcards (Joker, Abkürzungszeichen) „%“ und „_“ ausgeblendet werden können. Das Zeichen „%“ blendet dabei eine beliebige Anzahl (auch 0) aus, während „_“ genau 1 Zeichen unberücksichtigt läßt.

SELECT customer_id, company_name, contact_name, phone;
FROM Customer;
WHERE company_name LIKE „B%o%“

findet alle Kunden, deren Namen mit „B“ beginnt und irgendwo ein „o“ enthält (Achtung: Groß/Kleinschreibung wird beachtet), während bei

WHERE company_name LIKE „B_o%“

der Name ebenfalls mit „B“ beginnen und an der dritten Stelle ein kleines „o“ enthalten muß.

Gruppierung

Mit der GROUP BY Klausel können Sie Sätze des Ergebniscursors zu einem Satz zusammenfassen. Mit dem Statement

SELECT country AS „Land“, COUNT(*) AS „Anzahl“;
FROM Customer;
GROUP BY country

erhalten Sie eine Zusammenstellung Ihrer Kundentabelle nach Ländern mit der jeweiligen Anzahl Ihrer dortigen Kunden. Die AS-Klausel dient dazu, den Spalten des Ergebniscursors einen anderen Namen zu geben. COUNT(*) besagt, daß in dieser Spalte die Ergebniszeilen gezählt werden sollen. Dabei wird die Gruppierung berücksichtigt, das heißt, COUNT(*) gibt die Anzahl der Sätze pro Gruppe an.

Mit der HAVING-Klausel kann ein Ergebniscursor weiter eingeschränkt werden. HAVING ist eine Selektionsklausel, die auf dem Ergebniscursor arbeitet, nicht auf den Herkunftstabellen. Wenn keine Gruppierung vorgenommen wird, wirkt HAVING allerdings genau wie eine WHERE-Klausel. WHERE und HAVING können auch ohne Gruppierung im gleichen SQL-Statement eingesetzt werden, um zum Beispiel Verknüpfungsbeding-ungen und Selektionsbedingungen zu unterscheiden. Mehr zu Tabellenverknüpfungen weiter unten. Um die obige Zusammenstellung für ein Vertriebsgebiet durchzuführen müssen Sie WHERE region = „WA“ angeben, HAVING region = „WA“ funktioniert nicht, da die Spalte „Region“ nicht im Ergebniscursor enthalten ist. Wenn Sie aber eine Zusammenstellung aller Verkaufsgebiete der USA mit mehr als einem Kunden haben wollen, so müssen Sie für die Selektion nach der Anzahl der Kunden die HAVING-Klausel benutzen, da die Anzahl ja erst im gruppierten Ergebniscursor zur Verfügung steht:

SELECT region, count(*) AS „Anzahl“;
FROM Customer;
WHERE country = „USA“;
group by region;
having Anzahl > 1

Funktionen

In SQL eingebaut sind fünf Funktionen, die auch Aggregatfunktionen genannt werden. Diese sind

MIN, MAX, SUM, AVG und COUNT, die wir eben schon benutzt haben. Diese Funktionen sind nur bei Gruppierung der Ergebnisdaten sinnvoll einzusetzen. Wenn Sie eine dieser Funktionen benutzen, ohne eine Gruppierung anzugeben, faßt SQL (zumindest in der VFP-Version) alle Ergebniszeilen zu einer Zeile zusammen.

SELECT;
min(min_order_amt) AS „Minimum“,;
max(min_order_amt) AS „Maximum“,;
avg(min_order_amt) AS „Schnitt“,;
avg(min_order_amt)*100/max(min_order_amt) AS „Prozent“;
FROM Customer;
WHERE min_order_amt > 0

Das vorstehende Select-Statement ermittelt den kleinsten, höchsten und durchschnittlichen Mindestbestellwert, wobei der Durchschnittswert noch als Prozentangabe umgerechnet wird.

Da keine Angaben zur Gruppierung erfolgt sind, werden alle Kunden, bei denen ein Mindestbestellwert eingetragen ist, zusammengefasst. Der Ergebniscursor hat also eine Zeile und 4 Spalten. In VFP können Sie solch überschaubare Ergebnismengen einfach in ein Array umleiten. Mit der Klausel INTO ARRAY aMinMax legen Sie das Ergebnis im Array aMinMax ab. Das Array wird aufgebaut, falls es noch nicht vorhanden ist. Im weiteren Programmablauf können Sie dann zum Beispiel auf den höchsten Mindestbestellwert in aMinMix(1,2) zugreifen.

Werfen Sie noch einmal einen Blick auf die Zeile, die den Prozentwert ermittelt. An dieser Stelle können Sie leider noch nicht mit den von Ihnen selbst vergebenen Spaltennamen „Minimum“ und „Maximum“ arbeiten, da sie hier noch nicht bekannt sind. Ein SQL-Statement wird nicht sequentiell abgearbeitet, die Reihenfolge der Zeilen im Statement ist im Grunde unbedeutend. Neben den Grundrechenarten und VFP-eigenen Funktionen können Sie auch UDFs zur Bildung abgeleiteter Werte benutzen. Der SELECT arbeitet nicht auf Ihren Arbeitsbereichen, sondern führt so etwas wie ein USE AGAIN durch. In der UDF können Sie also nicht davon ausgehen, den gerade von SELECT bearbeiteten Satz feststellen zu können. Übergeben Sie daher alle Werte, die die UDF braucht als Parameter!

Aggregatfunktionen dürfen laut SQL-Standard nicht verschachtelt werden. Daran hält sich auch VFP.

Aggregatfunktionen und Gruppierung gehören zusammen.

In der Gruppierungsklausel werden die Spalten festgelegt, nach denen gruppiert werden soll, die Funktionen arbeiten auf diesen Gruppen. Schauen wir uns dazu noch ein Statement an:

SELECT country, region, count(*) AS „Anzahl“;
FROM Customer;
GROUP BY country,region;
HAVING Anzahl > 1;
ORDER BY Anzahl DESC

Hier wird nach Ländern und Vertriebsgebieten gruppiert, das heißt, ein Land, das mehrere Vertriebsgebiete umfasst, taucht auch in der Zusammenstellung pro Vertriebsgebiet einmal auf. „Anzahl“ gibt demnach die Kunden pro Land an, wenn das Land nur ein Vertriebsgebiet hat. Bei Ländern mit mehreren Vertriebsgebieten steht dort die Anzahl der Kunden pro Vertriebsgebiet. Es werden nur die Länder ausgegeben, die mehr als einen Kunden aufweisen können. Von diesen Ländern fallen jetzt aber auch die Vertriebsgebiete unter den Tisch, in denen nur ein Kunde lebt.

Die gewählte Sortierung hat keinen Einfluß auf die Gruppierung. Der Ergebniscursor wird nach den angegebenen Feldern sortiert. Bei der ORDER BY Klausel können bereits die mit der AS Klausel angegebenen Spaltennamen verwandt werden.

Wenn Sie an sinnvollen Resultaten interessiert sind, sollten Sie beachten, daß auf alle Spalten, die nicht in der GROUP BY Klausel aufgeführt sind, im Projektionsteil eine der fünf Aggregatfunktionen angewendet wird. Was soll der SQL-Interpreter bei der Zusammenstellung mit den unterschiedlichen Kundennamen machen, wenn Sie formulieren:

SELECT company_name, country, region
...
GROUP BY country, region ???

Zumindest gibt er keine Fehlermeldung aus. Im Ergebniscursor enthält die Spalte „customer_name“ irgendeinen Wert aus der gleichen Spalte der Ursprungstabelle, meistens aus dem letzten Satz dieser Gruppe.

Daß der Company_Name im vorigen Select keinen Sinn macht, ist verständlich. Weniger intuitiv klar ist, daß

SELECT Company_Name, max(discount) from Customer && ACHTUNG: Falsch!!!!

nicht den gewünschten Effekt hat, nämlich den Kunden mit dem höchsten Rabattsatz zu ermitteln. Warum dieses Statement falsch formuliert ist, sollte aus dem bisher gesagten klargeworden sein - nur: wie macht man’s richtig? Mit den bisher bekannten Mitteln ist diese, so einfach klingende Aufgabe nicht zu lösen. Grundsätzlich sollte in Ihrem Kopf die Lampe „Unterabfrage“ angehen, wenn Sie einen Satz suchen, der nach irgendeinem Kriterium der Größte, der Kleinste, der Erste oder der Letzte ist. Bevor wir aber zu den Unterabfragen kommen, die man zur Lösung dieser Aufgabe braucht, noch ein Wort zum Zählen von Abfragezeilen.

COUNT

COUNT kann verschiedene Dinge. Mit COUNT(*) zählen Sie die Zeilen eines Abfrageergebnisses.

SELECT count(*) FROM Customer WHERE country = „USA“

ergibt also die Anzahl der Kunden in den USA. Ist die Abfrage gruppiert, so zählt COUNT(*) die Zeilen pro Gruppe, so wie im ersten Beispiel des Gruppierungsabschnitts. Sie können bei COUNT aber auch eine Spalte angeben:

SELECT country, count(region);
FROM Customer;
GROUP BY country

sollte also zählen, wieviele Vertriebsgebiete es pro Land gibt. Seltsamerweise wird Deutschland dabei jedoch mit 11 Vertriebsgebieten angegeben, obwohl in den Beispieldaten Deutschland nicht in Vertriebsgebiete unterteilt ist. COUNT(Spalte) gibt nämlich die Anzahl der von .NULL. verschiedenen Werte in der angegebenen Spalte wieder. Und das sind in der Tat 11, nämlich 11 Leerstrings - alle von .NULL. verschieden. Ersetzen Sie probehalber die Leerstrings in den Testdaten mit .NULL., so erhalten Sie im Ergebniscursor den Wert 0 für Deutschland.

Wie läßt sich nun aber feststellen, wieviele unterschiedliche Einträge in einer Spalte vorliegen? Ganz einfach: mit einer Syntax der COUNT-Funktion, die im Handbuch leider nicht erwähnt ist, aber trotzdem unterstützt wird. COUNT(DISTINCT Spalte) gibt in der Tat - so wie im SQL-Standard vorgesehen - an, wieviele unterschiedliche Einträge in einer Spalte vorhanden sind.

Unterabfragen (Subquerys)

Den Kunden mit dem höchsten Rabattsatz zu ermitteln gelingt uns nur mit einer Unterabfrage. Eine Unterabfrage ist ein Select innerhalb eines Selects. Die Ergebnisse der Unterabfrage werden als Vergleichswerte eines Selektionskriteriums herangezogen.

SELECT company_name;
FROM Customer;
WHERE discount =;
(SELECT MAX(discount) FROM Customer WHERE country = “USA“)

Dieses Statement ermittelt die Firmennamen der Kunden, die den maximalen Rabattsatz der US-Kunden haben.

Die Unterabfrage liefert einen Wert zurück. Dieser kann also gleich als Vergleichsoperand hergenommen werden. Wenn wir wüßten , daß der höchste Rabattsatz 10% ist, könnten wir schreiben

WHERE discount = 10

Wir kennen aber den höchsten Rabattsatz nicht (in diesem Beispiel). Also ermitteln wir ihn zuerst durch die Unterabfrage. Unterabfragen werden in dieser Form immer an Selektionskriterien geknüpft. Bei VFP dürfen dies auch nur WHERE-Kriterien sein, keine HAVING-Kriterien, obwohl der SQL-Standard das erlaubt. Unterabfragen können in VFP nicht geschachtelt werden. Der Haupt-Select kann max. 2 Unterabfragen enthalten.

Nebenbei: Unterabfragen sind ein SQL-Bereich, der von VFP recht stiefmütterlich behandelt wird. Es wäre wünschenswert, daß hier noch einige Möglichkeiten des Standards übernommen werden.

Vergleichsoperatoren für Unterabfragen

Bisher haben wir Unterabfragen so konstruiert, daß genau ein Wert zurückgeliefert wurde. Meistens ist das ein Minimal, Maximal oder Durchschnittswert. Solch ein Wert läßt sich über die normalen Vergleichsoperatoren einbinden. Was ist aber, wenn eine Unterabfrage mehrere Zeilen zurückliefert? Für einen solchen Vergleich lassen sich die Vergleichsoperatoren mit zusätzlichen Schlüsselwörtern kombinieren bzw. stehen eigene Vergleichsoperatoren zur Verfügung. Die verfügbaren Schlüsselwörter sind: ALL, ANY oder SOME, EXISTS und IN.

ALL: WHERE discount > ALL (SELECT .... )

Bei Verwendung von ALL muß der Vergleich für alle von der Unterabfrage zurückgegebenen Werte .T. ergeben.

ANY oder SOME: WHERE discount < ANY (SELECT .... )

Jetzt muß der Vergleich nur für mindestens einen von der Unterabfrage zurückgegebenen Wert stimmen.

EXISTS: WHERE EXISTS (SELECT .... )

Hier wird nichts verglichen, sondern nur abgeprüft, ob die Unterabfrage eine leere Ergebnismenge hat (.F.) oder nicht (.T.).

IN: WHERE customer_id IN (SELECT ... )

Die Wertegruppe, die mit IN durchsucht wird, kann aus einer Unterabfrage stammen.

Alle Multiple-Row-Subquerys (so nennt man Unterabfragen, die mehr als eine Zeile zurückliefern können), mit Ausnahme der durch EXISTS angebundenen, dürfen nur eine Spalte haben.

Abhängige Unterabfrage (Correlated Subquery)

In VFP 5.0 wird jetzt eine besondere Form von Unterabfragen zumindest teilweise unterstützt: die abhängige Unterabfrage. Diese Unterabfrage enthält ein Feld aus einer Tabelle der Hauptabfrage, das heißt das Ergebnis der Unterabfrage ist abhängig vom aktuellen Satz der Hauptabfrage. EinBeispiel dazu:

SELECT;
customer_id, company_name ;
FROM Customer ;
WHERE discount < ;
(SELECT max(discount) ;
FROM Orders;
WHERE Orders.customer_id == Customer.customer_id)

gibt alle Kunden aus, für die Bestellungen mit einem höheren Rabattsatz als im Kundenstamm vorgesehen vorliegen. In der WHERE-Klausel der Unterabfrage wird auf Customer.customer_id Bezug genommen, obwohl die Tabelle Customer in der Unterabfrage gar nicht aufgeführt ist. Mit abhängigen Unterabfragen können sehr kompakte SQL-Selects aufgebaut werden. Zu beachten ist dabei allerdings, daß dies immer zu Lasten der Performance geht, da die Unterabfrage ja für jeden Satz der Hauptabfrage neu durchgeführt werden muß. Vielleicht ist das auch der Grund für die Einschränkung in VFP, daß abhängige Unterabfragen nur zugelassen sind, wenn in der Hauptabfrage lediglich eine Tabelle aufgeführt ist. Da abhängige Unterabfragen im Handbuch nicht angesprochen werden (lediglich die neue Fehler-Nr. 1801 weist auf ihre Möglichkeit hin), denke ich aber, daß daran noch gebaut wird.

Verknüpfung von Tabellen

Beim Normalisieren (siehe oben) werden Informationen auf mehrere Tabellen verteilt. Die meisten Abfragen in einer normalisierten Datenbank werden demnach mehrere Tabellen betreffen. Wie werden Abfragen formuliert, bei denen mehrere Tabellen betrachtet werden müssen? Schaun wir uns zunächst den Projektionsteil an, also die Angabe, welche Spalten im Ergebniscursor auftauchen sollen. Für einen Bericht brauchen wir vielleicht alle Kunden mit Ihren Bestellungen. Im Projektionsteil des Select-Befehls geben wir an, welche Felder in den Ergebniscursor übernommen werden sollen. In der FROM-Klausel wird angegeben, aus welchen Tabellen die Felder zu lesen sind. Wenn die Feldnamen in den beteiligten Tabellen nicht eindeutig sind, müssen sie mit dem Tabellennamen qualifiziert werden.

* VORSICHT!!! Braucht sehr viel Platz !!!!!
SELECT;
Customer.customer_id, Customer.company_name,;
Orders.order_number, Orders.order_date;
FROM Customer, Orders

Wenn wir diesen Befehl so abschicken, werden wir eventuell Platzprobleme auf unserem lokalen Laufwerk bekommen. Ohne weitere Einschränkung werden nämlich alle Sätze der ersten Tabelle mit allen Sätzen der zweiten Tabelle verknüpft. Das Ergebnis ist natürlich unsinnig, vor allem aber riesig. Bei Verwendung der VFP 3 Beispieldaten ( 91 Kundensätze und 1078 Bestellungen ) erhalten wir einen Ergebniscursor, der 91 x 1078 = 98098 Sätze enthält.

Das Ergebnis dieser Alles-mit-Allem Verknüpfung, das sogenannte kartesische Produkt der beteiligten Tabellen, ist selten zu gebrauchen. Wir müssen die Verknüpfung auf sinnvolles einschränken. Das geschieht mit der Verknüpfungsbedingung, die gleich auf die FROM-Klausel folgt.

...
FROM Customer, Orders;
WHERE Customer.customer_id == Orders.customer_id

Die Verknüpfungsbedingung in unserem Beispiel gibt an, daß nur die Sätze miteinander verknüpft werden sollen, bei denen die Customer_id genau gleich ist.

Gesetzt den Fall, es interessiert uns auch noch, welche Produkte von den einzelnen Kunden gekauft wurden, so müssen wir eine dritte Tabelle einbeziehen, nämlich die Bestellpositionen

...
FROM Customer, Orders, OrdItems;
WHERE Customer.customer_id == Orders.customer_id;
AND Orders.order_id == OrdItems.order_id

Jetzt haben wir Zugriff auf jede einzelne Bestellposition. Drei Tabellen sind beteiligt, zwei Verknüpfungsbedingungen müssen angegeben werden, nämlich die Verknüpfungsbedingung zwischen Customer und Orders und die Verknüpfungsbedingung zwischen Orders und OrdItems, den Bestellpositionen. Im Ergebniscursor ist jeder Kunde über das Bindeglied Orders mit seinen Bestellpositionen verknüpft.

In den Bestellpositionen steht aber nur die Artikel-ID, nicht jedoch der Artikelname. Außerdem wollen wir das ganze auf eine oder mehrere Warengruppen einschränken können. Das Resultat dieser Überlegungen ist eine Verknüpfung über insgesamt fünf Tabellen:

SELECT;
Customer.customer_id, Customer.company_name,;
Orders.order_number, Orders.order_date,;
OrdItems.quantity,;
Products.Product_name;
FROM Customer, Orders, OrdItems, Products, Category;
WHERE Customer.customer_id == Orders.customer_id;
AND Orders.order_id == OrdItems.order_id;
AND OrdItems.product_id == Products.product_id;
AND Products.category_id == Category.category_id;
AND Category.category_id == “ 1“;
ORDER BY Customer.customer_id, Orders.order_date

Die WHERE-Klausel dieses Statements besteht aus vier Verknüpfungsbedingungen und einer Selektionsbedingung, nämlich Category.category_id == “ 1“. Der Unterschied zwischen Verknüpfungsbedingungen und Selektionsbedingungen ist nicht deutlich zu sehen, da beide in der WHERE-Klausel angegeben werden. Die Verknüpfungsbedingungen waren deshalb im SQL2-Standard auch in anderer Syntax angebbar, in VFP bisher aber noch nicht. Das, und auch das Manko des fehlenden OUTER JOIN, zu dem wir gleich kommen, ist in VFP 5 jetzt behoben. Der oben stehende Select sieht in der neuen Syntax jetzt folgendermaßen aus:

SELECT;
Customer.customer_id, Customer.company_name,;
Orders.order_number, Orders.order_date,;
OrdItems.quantity,;
Products.Product_name;
FROM Customer;
JOIN Orders ON Customer.customer_id == Orders.customer_id;
JOIN OrdItems ON Orders.order_id == OrdItems.order_id;
JOIN Products ON OrdItems.product_id == Products.product_id;
JOIN Category ON Products.category_id == Category.category_id;
WHERE Category.category_id == „ 1“;
ORDER BY Customer.customer_id, Orders.order_date

Kurz gesagt, die Kommata zwischen den Tabellennamen fallen weg, dafür wird das Schlüsselwort JOIN eingesetzt. Die Verknüpfungsbedingung wird direkt hinter der geJOINten Tabelle mit dem Schlüsselwort ON angegeben. Die Selektionsbedingungen werden nach wie vor mit der WHERE-Klausel eingeleitet.

Der Schreibaufwand ist ungefähr gleich geblieben, die Lesbarkeit ist jedoch deutlich höher.

INNER JOIN und LEFT | RIGHT OUTER JOIN

Vielleicht hat der ein- oder andere nur bis hierher durchgehalten, weil er wissen will, was es denn nun mit den linken und rechten OUTER JOINS auf sich hat. Nun sind wir fast soweit. Nur kurz noch was zum INNER JOIN. Ein INNER JOIN (auch CROSS JOIN) genannt ist alles das, was wir bisher besprochen haben. Alle diese Verknüpfungen basieren nämlich auf dem durch Bedingungen eingeschränkten kartesischen Produkt. Das läßt sich vielleicht mit den aus der Mengenlehre bekannten Grafiken deutlich machen.

Der INNER JOIN liefert als Ergebnismenge nur die Sätze, die den Verknüpfungsbedingungen entsprechen. Ein LEFT OUTER JOIN liefert zusätzlich alle restlichen Sätze der linken Tabelle. Ein RIGHT OUTER JOIN liefert entsprechend zu den Sätzen des INNER JOIN alle restlichen Sätze der rechten Tabelle. Wenn Sie jetzt einwenden: „Die Reihenfolge der Tabellen beim JOIN ist doch gleichgültig!“, dann ist das solange richtig, wie Sie eben LEFT oder RIGHT nicht angeben. Wenn Sie es angeben, müssen Sie auf die Reihenfolge achten.

Customer LEFT OUTER JOIN Orders

ist also das gleiche wie

Orders RIGHT OUTER JOIN Customer

Zusätzlich gibt es auch den FULL OUTER JOIN, der demnach die restlichen Sätze aus beiden geJOINten Tabellen zum INNER JOIN mitliefert. Verwechseln Sie das nicht mit dem kartesischen Produkt. Es wird kein Produkt gebildet. Die nicht im INNER JOIN enthaltenen Sätze aus der linken und/oder rechten Tabelle werden dem Ergebnis nur einmal hinzugefügt. Im Projektionsteil angegebene Felder aus anderen Tabellen werden dabei auf .NULL. gesetzt.

Wozu braucht man das? Versuchen Sie einmal mit VFP 3.0 einen Cursor mit allen Kunden und deren Bestellungen zu erzeugen, wobei auch die Kunden aufgeführt werden sollen, die bisher nichts bestellt haben. Das geht, und zwar mit einem UNION SELECT

SELECT;
Customer.customer_id, ;
Customer.company_name, ;
Orders.order_date;
FROM Customer, Orders ;
WHERE Customer.customer_id == Orders.customer_id;
UNION SELECT;
Customer.customer_id, ;
Customer.company_name, ;
{};
FROM Customer, Orders ;
WHERE Customer.customer_id NOT IN ;
(select DISTINCT customer_id from ORDERS);
ORDER BY 3

Mit UNION SELECT können Sie an ein Select-Statement ein weiteres anschließen, dessen Ergebnis im gleichen Cursor abgelegt wird. UNION SELECTs können auch mehrfach aneinandergehängt werden. Beachten Sie nur, daß Feldtypen und Größen in allen Selects gleich sein müssen. In diesem Beispiel werden zunächst alle Kunden ermittelt, für die bereits Bestellungen vorliegen, danach werden in den gleichen Ergebniscursor auch die Kunden (mit leerem Bestelldatum) eingetragen, für die es noch keine Bestellungen gibt. Diese in VFP 3 doch etwas umständliche Abfrage läßt sich in VFP 5 jetzt so schreiben:

SELECT;
Customer.customer_id, ;
Customer.company_name, ;
Orders.order_date;
FROM Customer;
LEFT OUTER JOIN Orders ON Customer.customer_id == Orders.customer_id;
ORDER BY order_date

Durch das LEFT OUTER JOIN werden dem Ergebniscursor wie gesagt alle Sätze aus Customer hinzugefügt, die der Verknüpfungsbedingung nicht entsprechen, für die es also keine Bestellung gibt. Das Schlüsselwort OUTER kann übrigens weggelassen werden, LEFT, RIGHT oder FULL reichen, um VFP mitzuteilen, daß es sich um einen OUTER JOIN handelt.

DISTINCT

Das DISTINCT in der Unterabfrage des UNION SELECT bedarf noch einer kurzen Erläuterung. Mit DISTINCT gleich hinter dem SELECT können Sie angeben, daß doppelte Einträge im Ergebniscursor unterdrückt werden sollen. Eine ähnliche Wirkung also, wie oben beim COUNT(DISTINCT Spalte) beschrieben.

LOKALER ALIAS

Auch die Verwendung eines lokalen Aliasnamens in SQL wurde noch nicht erwähnt. Jede Tabellenangabe in der FROM-Klausel kann mit einem Aliasnamen versehen werden, den Sie dann im gesamten Befehl verwenden müssen. Durch die Vergabe von unterschiedlichen lokalen Aliasnamen können Sie dieselbe Tabelle in einem Select auch mehrfach angeben, zum Beispiel um abzuprüfen, welche Kunden im selben Postleitzahlgebiet wohnen:

SELECT;
C1.company_name, C2.company_name, C1.country, C1.postal_code;
FROM Customer C1, Customer C2;
WHERE C1.customer_id <> C2.customer_id;
AND C1.country == C2.country;
AND C1.postal_code == C2.postal_code

Zusammenfassung

Wenn Sie sich die Abarbeitung eines SQL-Selects als sechsstufigen Prozess vorstellen (der intern natürlich optimiert wird), dürften Ihnen auch komplexere SQL-Abfragen keine großen Probleme mehr bereiten.

1. FROM
   Zunächst werden die geforderten Verknüpfungen gebildet und das kartesische Produkt aus allen beteiligten Tabellen wird erstellt.
2. JOIN ON bzw. WHERE
   Im zweiten Schritt werden alle Zeilen des kartesischen Produktes gestrichen, die nicht den Verknüpfungs- und Selektionsbedingungen entsprechen.
3. GROUP BY
   Danach wird die Gruppierung durchgeführt.
4. HAVING
   Alle Zeilen des gruppierten Ergebnisses, die nicht den Selektionsbedingungen von HAVING entsprechen, werden gestrichen.
5. SELECT
   Aus dem, was übrigbleibt werden alle Spalten gestrichen, die nicht im Projektionsteil angegeben sind.
6. ORDER BY
   Zum Schluß wird der Ergebniscursor sortiert.

VFP5-Spezifika

Auf einige neue VFP-spezifische Klauseln des SELECT-Befehls will ich noch eingehen. In VFP 5 ist es jetzt möglich, statt SELECT ALL (alle Sätze) oder DISTINCT (keine Duplikate) anzugeben, daß eine bestimmte maximale Anzahl von Sätzen oder ein Prozentsatz des Ergebnisses zurückgegeben wird. Mit SELECT TOP 10 erhalten Sie nur die ersten 10 Sätze des Ergebnisses, mit SELECT TOP 10 PERCENT werden nur die ersten 10 Prozent des Ergebnisses zurückgegeben. Bei Angabe von TOP ... muß eine ORDER BY Klausel vorhanden sein. Sätze, die in den Sortierfeldern übereinstimmen, werden nur einmal gezählt. So kann es durchaus vorkommen, daß trotz Angabe von SELECT TOP 10 ... ORDER BY ... Ihr Ergebniscursor mehr als 10 Sätze umfasst.

Auch in der FROM-Klausel hat sich was getan. Mit FORCE können Sie angeben, daß die Tabellen der FROM-Klausel genau in der angegebenen Reihenfolge miteinander verknüpft werden sollen. Ohne den FORCE-Zusatz, versucht VFP, die Verknüpfung zu optimieren. Unter bestimmten Umständen, wenn zum Beispiel die zuerst angegebene Verknüpfung eine sehr kleine Ergebnismenge hat, kann ein SELECT ohne Optimierung der Verknüpfung schneller sein, als mit.

Wenn Sie als Ziel INTO CURSOR ... angeben, können Sie jetzt durch NOFILTER erzwingen, daß tatsächlich eine temporäre Tabelle aufgebaut wird. Ein SELECT * FROM Customer WHERE ... INTO CURSOR ... erzeugt im Normalfall nämlich keine temporäre Tabelle sondern öffnet Customer lediglich ein zweites Mal und setzt einen Filter darauf, der der WHERE-Bedingung entspricht. Wenn Sie diesen „Cursor“ dann in einen weiteren Select einbeziehen, kommt es zu einer Fehlermeldung:

SELECT * FROM customer;
WHERE customer_id = "A";
INTO CURSOR Eins
SELECT * FROM Eins;
WHERE city = "Berlin";
INTO CURSOR Zwei

Die oben stehende Abfolge ergibt die Meldung, daß der erste Cursor doch bitte mit INTO TABLE erstellt werden soll.

SELECT * FROM customer;
WHERE customer_id = "A";
INTO CURSOR Eins NOFILTER

behebt dieses Problem jetzt. Dadurch, daß mit NOFILTER auf jeden Fall eine temporäre Tabelle erstellt wird, kann die Performance beeinträchtigt werden. Verwenden Sie NOFILTER also nur dann, wenn Sie den ersten Cursor in weiteren SELECTs verwenden wollen.

xBase oder SQL? Nein - xBase mit SQL!

Als FoxPro-Entwickler brauchen wir (Gott sei Dank) nicht mit Embedded SQL zu arbeiten. Die in FoxPro zur Verfügung stehenden SQL-Befehle sind nahtlos in die Sprache integriert. Wer schon mal mit Embedded SQL gearbeitet hat, wird es zu schätzen wissen, daß keine separaten Hostvariablen deklariert werden müssen. Jede FoxPro Variable, ja selbst Felder aus Tabellen, die nicht an dem aktuellen SQL-Statement beteiligt sind, können in die Vergleichsbedingungen des SQL-Statement eingetragen werden. Das Ergebnis eines SQL-Selects kann direkt in einem Browse-Fenster angezeigt oder in einem Array abgelegt werden. Ein mit SQL-Select aufgebauter Cursor oder View kann mit xBase-Befehlen weiterverarbeitet werden, usw. usw.

Die Integration der SQL-Sprache in Visual FoxPro ist so vollständig, daß vielen Entwicklern gar nicht klar ist, wann sie einen SQL- und wann einen xBase-Befehl benutzen. Deshalb hier mal eine Übersicht, gesondert nach Befehlsklassen.

In SQL werden 3 Befehlsklassen unterschieden:

Und jetzt die Gegenüberstellung: Was ist xBase, was SQL? Nicht in VFP realisierte SQL-Befehle stehen in Klammern.

Wenn Sie diese Gegenüberstellung betrachten, fällt Ihnen wahrscheinlich mehreres auf:

1. Reine xBase-Befehle der DDL zusammenzustellen, ist etwas schwierig. Bei den meisten xBase-Befehlen der DDL handelt es sich nicht um Befehle, die in einem Programm auftauchen, sondern um Aufrufe von interaktiven Tools.
2. Neuere Befehle wie „create database“ oder „create sql view“ wurden von vorneherein in der SQL-Syntax angelegt. Das ist auch nicht weiter verwunderlich, da eine xBase-Kompatibilität bei diesen VFP-Erweiterungen keinen hohen Stellenwert mehr hat.
3. Befehle der Data Control Language gibt es in VFP leider immer noch nicht.
4. Die Befehle der Data Manipulation Language zeigen die unterschiedliche Heransgehensweise von xBase und SQL am deutlichsten.

Bevor wir uns die Befehle der DataDefinitionLanguage anschauen, noch einige Hinweise zu den SQL-Befehlen INSERT, UPDATE und DELETE.

INSERT

Der INSERT-SQL Befehl in VFP hält sich in der im Handbuch zuerst angegebenen Syntax

INSERT INTO Tabelle (Feld1,Feld2,...) ;
VALUES (Wert1,Wert2,...)

an den SQL-Standard. Mit diesem Befehl wird genau ein Satz in die Tabelle geschrieben. Die zweite Syntaxvariante

INSERT INTO Tabelle FROM ARRAY ArrayName

kann dazu benutzt werden, mehrere Sätze in die Tabelle zu schreiben. Pro Arrayzeile wird ein neuer Tabellensatz gebildet, die Arrayspalten enthalten die Feldwerte und müssen in der gleichen Reihenfolge angeordnet sein, wie die Tabellenfelder. Die im SQL-Standard vorgesehene Syntax

INSERT INTO Tabelle
SELECT ....

die es ermöglicht, das Ergebnis einer Abfrage direkt an eine andere Tabelle anzuhängen, wird in VFP nicht unterstützt. Ein Ersatz dafür ist hier nur in zwei Schritten zu haben:

SELECT ....
INTO CURSOR CursorName
SELECT Tabelle
APPEND FROM DBF(“CursorName“)

UPDATE

Für den UPDATE Befehl ist in VFP nur eine Syntax vorgesehen.

UPDATE Tabelle
SET Spalte1 = Ausdruck1,
[Spalte2 = Ausdruck2 ...]
WHERE SelektionsBedingung

Die SelektionsBedingung kann auch hier maximal zwei Unterabfragen enthalten. Das eröffnet die einfache Möglichkeit, den Update einer Tabelle abhängig von den Daten anderer Tabellen durchzuführen. Um den maximalen und minimalen Bestellwert aller Kunden, die in den letzten zwei Jahren nichts mehr bestellt haben, neu zu setzen, reicht ein UPDATE Befehl

UPDATE Customer;
SET Max_Order_Amt = 1000,;
Min_Order_Amt = 0;
WHERE Customer_id NOT IN;
(SELECT DISTINCT customer_id FROM Orders;
WHERE YEAR(order_date) > YEAR(DATE()) - 2)

Auch abhängige Unterabfragen sind möglich.

DELETE

Auch das DELETE Kommando aus SQL hat seine Stärken dort, wo Daten einer Tabelle auf Grund von Inhalten anderer Tabellen gelöscht werden sollen. Nichts ist einfacher, als alle „Kunden“, die noch nie bei Ihnen bestellt haben, aus der Kundentabelle zu löschen:

DELETE FROM Customer;
WHERE Customer_id NOT IN;
(SELECT DISTINCT customer_id FROM Orders)

Datenbank-Wartung

Die meisten Entwickler werden in VFP zum Erstellen oder Ändern von Tabellen die visuellen Tools benutzen. Zur Wartung der Datenbank auf den Maschinen Ihrer Anwender können Sie die SQL-Befehle der DataDefinitionLanguage einsetzen.

CREATE DATABASE

Die Zusammenstellung der SQL-Befehle in der VFP-Hilfe unterschlägt, daß auch CREATE DATABASE ein SQL-Befehl ist. Ansonsten ist dazu wenig zu sagen. Schaun wir uns lieber den CREATE TABLE Befehl an.

CREATE TABLE

Ich erspare es mir, hier die gesamte Syntax des Befehls abzutippen. Lassen Sie uns lieber untersuchen, was CREATE TABLE ermöglicht und was nicht:

CREATE TABLE Tabelle1 NAME LangerTabellenName1;

(;
RecID I ,;
Feld1 C(20) CHECK !empty(Feld1) ERROR "Feld1 darf nicht leer sein!",;
Feld2 D NOT NULL CHECK Feld2 >= date() DEFAULT date(),;
Feld3 I NULL,;
PRIMARY KEY RecID TAG "PRIMARY",;
UNIQUE Feld1,;
CHECK Satz1Valid() ERROR "Dieser Satz ist nicht zulässig";
)

Dieses einfache Beispiel erstellt eine Tabelle Tabelle1.DBF mit dem Datenbank-internen Namen LangerTabellenName1. Die einzelnen Klauseln des Befehls werden durch Kommata voneiander getrennt, die ganze Definition wird in Klammern eingeschlossen. Vier Felder werden angelegt, wobei die CHECK-Klausel den Feldvalidierungsausdruck angibt und hinter ERROR die dazu gehörende Fehlermeldung steht. NULL oder Nicht NULL kann gesetzt werden, ebenso könnte die Felddefinition bereits angeben, ob für dieses Feld ein PRIMARY oder UNIQUE ( = Candidate) Index angelegt werden soll, oder ob das Feld als Fremdschlüssel zur Verknüpfung mit einer anderen Tabelle dient (FOREIGN KEY - siehe unten).

Eine Tabellenvalidierungsregel kann mit einer CHECK-Klausel ohne Feldangabe festgelegt werden. Dabei ist natürlich auch der Aufruf einer UDF, zum Beispiel aus den StoredProcedure der Datenbank möglich. Angaben zu Triggern sind hier nicht möglich. VFP hat zum Anlegen von Triggern den CREATE TRIGGER Befehl. Indexe vom Typ REGULAR, die in SQL mit CREATE INDEX erzeugt werden, können nur als FOREIGN KEY mit dem CREATE TABLE Befehl erzeugt werden. Andere, im Tabellendesigner einstellbare VFP-Spezifika lassen sich nicht angeben, zum Beispiel die Angaben zu Display und Field-Mapping (neu in VFP5).

Etwas seltsam wird das Verhalten des CREATE TABLES wenn Sie einen Fremdschlüssel anlegen wollen. Die Syntax:

FOREIGN KEY Feld3 TAG Fremd REFERENCES LangerTabellenName1

erzeugt einen Index vom Typ REGULAR auf Feld3. Die darauf basierende Relation ist demnach eine 1 : n Relation. Wenn Sie, wie im Handbuch beschrieben, mit

FOREIGN KEY Feld3 TAG Fremd NODUP REFERENCES LangerTabellenName1

einen Candidate-Index zum Aufbau einer 1 : 1 Relation definieren wollen, so erhalten Sie einen Syntaxfehler. Ein Workaround dazu: Geben Sie die Angaben für eine 1 : 1 Beziehung bei den Felddefinitionen mit, also zum Beispiel so:

Feld3 I UNIQUE REFERENCES LangerTabellenName1

Übrigens, ein Test auf eine in SQL-Syntax mögliche Klausel, nämlich

FOREIGN KEY Feld3 TAG F3 REFERENCES LangerTabellenName1 ON UPDATE CASCADE

ging anstandslos durch, bewirkte jedoch nicht die gewünschte Einstellung im RI-Fenster. Offensichtlich ist hier noch etwas im Bau.

CREATE CURSOR

Dient zur Erstellung temporärer Tabellen. Eine mit CREATE CURSOR erstellte Tabelle kann wie jede andere Tabelle mit Daten beschickt und indiziert werden. Auch eine Änderung der Tabellenstruktur mit ALTER TABLE (siehe unten) ist möglich. Eine temporäre Tabelle kann indiziert werden. Wenn die Tabelle geschlossen wird, wird sie mit allen dazu gehörenden Indexdateien automatisch gelöscht. Die Syntax von CREATE CURSOR stimmt in allen Angaben, die für temporäre Tabellen möglich sind, mit der Syntax von CREATE TABLE überein. Für temporäre Tabellen können jedoch keine Satzvalidierungsregeln, kein PRIMARY KEY und keine FOREIGN KEYs angegeben werden.

ALTER TABLE

Dieser SQL-Befehl dient dazu, eine vorhandene Tabelle programmgesteuert in ihrer Struktur zu verändern. Mit den Klauseln ADD, ALTER, DROP und RENAME COLUMN werden Spalten hinzugefügt, verändert, gelöscht und umbenannt. Mit SET CHECK und DROP CHECK werden Validierungsregeln gesetzt oder verworfen, und schließlich können mit ADD und DROP auch der PRIMARY KEY, UNIQUE (=Candidate) Keys und FOREIGN KEYs hinzugefügt oder gelöscht werden. Durch die Klausel SAVE bei DROP FOREIGN KEY können Sie verhindern, daß der angegebene Schlüssel aus der CDX-Datei entfernt wird. Gelöscht wird dann lediglich die im DBC hinterlegte persistente Beziehung, die auf diesem FOREIGN KEY basiert.

ALTER TABLE hat drei verschiedene Syntax-Strukturen. Wo sind die Unterschiede? Die erste Syntaxform dient zum Hinzufügen und Ändern von Spalten und unterscheidet sich von der zweiten hauptsächlich durch die Angabe von Feldtyp und Feldgröße. Beachten Sie, daß alle Feldeinstellungen neu angegeben werden müssen, wenn Sie Feldtyp und Feldgröße angeben. Die Wirkung von ALTER COLUMN mit Angabe von Feldtyp und Größe ist wie ein Löschen der alten Felddefinition und hinzufügen der neuen, wobei die Daten aus der alten Spalte in die neue kopiert werden. Alle Angaben zu DEFAULT-Werten, Validierung etc. für dieses Feld gehen verloren und müssen in der ALTER COLUMN Anweisung neu angegeben werden.

Die zweite Variante der ALTER TABLE Syntax dient zum Ändern von Spalten ohne Veränderung der kritischen Angaben Typ und Größe. Hierbei bleiben vorhandene Setzungen, die nicht neu angegeben werden, erhalten. Die dritte Variante schließlich dient zum Ändern von Setzungen für die Tabelle selbst, wobei auch Spalten gelöscht oder umbenannt werden können.

Mit der Klausel NOVALIDATE können Sie verhindern, daß bei der Änderung der Tabelle eine Validierung aller bereits in der Tabelle enthaltenen Sätze vorgenommen wird. Obwohl dies manchmal sinnvoll sein kann, ist Vorsicht geboten. Änderungen einer Tabelle mit NOVALIDATE können dazu führen, daß Ihre Datenbank anschließend inkonsistent ist.

CREATE SQL VIEW

Zum guten Schluß ein Befehl, der seit seinem Erscheinen in VFP 3 schon für einige Aufregung gesorgt hat. Endlich stehen Updatable Cursor zu Verfügung, das Ergebnis eines SQL-Selects ist also nicht mehr notwendig schreibgeschützt. Besser noch: Änderungen im View-Cursor und hinzugefügte Sätze können an die Tabellen weitergegeben werden, die dem View zu Grunde liegen. Auch eine Weitergabe über mehrere Stufen ist möglich, das heißt, wenn eine der Tabellen, aus denen ein View „A“ seine Daten bezieht, selbst ein View „B“ ist, so werden die Änderungen mit dem TABLEUPDATE()-Befehl für den View „A“ zunächst von „A“ an „B“ weitergegeben. Wird später ein TABLEUPDATE() für „B“ durchgeführt, so landen die in „A“ vorgenommenen Änderungen über diesen Umweg in den Basistabellen. Wer dieses Prinzip allerdings verstärkt nutzen will, sollte ein ausgefeiltes Konzept der notwendigen TABLEUPDATEs erstellen.

Aber zunächst einmal: Was ist ein View aus SQL-Sicht? Ein View ist eine virtuelle Tabelle, die eine durch einen SQL-Select definierte Sicht auf die Daten der Datenbank bietet. Die Definition des SQL-Selects wird in der Datenbank selbst unter einem eindeutigen Namen abgelegt. Beim Zugriff auf die mit dem Viewnamen identifizierte virtuelle Tabelle wird das hinterlegte SELECT-Statement ausgeführt und dessen Ergebnis in einer temporären Tabelle bereit gestellt.

Die Syntax zum Aufbau eines Views ist recht unscheinbar:

CREATE SQL VIEW ViewName;
AS SELECT ...

Daß es nicht einfach CREATE VIEW heißen kann, wie im SQL-Standard vorgesehen, liegt an den xBase-Altlasten. Dieser Befehl diente einmal zum Speichern der aktuellen Tabellenumgebung in einer .VUE-Datei.

CREATE SQL VIEW ohne Angabe des Select-Statements öffnet den View-Designer, der in VFP 5 einige Ergänzungen erfahren hat. So können jetzt Feldeigenschaften für Validierung, Display, Field-to-Class Mapping und für Remote-Views auch Data-Mapping im Viewdesigner angegeben werden und müssen nicht mehr nachträglich mit DBSETPROP() eingestellt werden.

Wenn Sie einen View aus Ihrem Programm heraus ohne Zuhilfenahme des Viewdesigners erstellen wollen, geben Sie das Select-Statement für den View im CREATE SQL VIEW Befehl an.

Der Zugriff auf einen View erfolgt in VFP mit dem für Tabellen üblichen USE-Befehl. Der Zusatz NODATA beim USE auf den View bewirkt, daß ein leerer Cursor mit der im View hinterlegten Struktur bereitgestellt wird. Diesen Cursor können Sie dann zu einem anderen Zeitpunkt, etwa, wenn alle Werte der Viewparameter bereitgestellt sind, durch ein REQUERY()-Kommando mit Daten beschicken.

Viewparameter sind variable Stellen in den WHERE und HAVING Klauseln des Views. Sie werden mit einem vorangestellten Fragezeichen gekennzeichnet, z.B. so:

SELECT *
FROM Customer;
WHERE customer_id == ?KundenNummer

Ein View mit dieser SELECT-Anweisung enthält maximal einen Satz, nämlich den Kundensatz, bei dem das Feld customer_id mit dem Inhalt der Variablen KundenNummer übereinstimmt. Wenn beim Zugriff auf diesen View die Variable KundenNummer nicht definiert ist, fragt VFP den benötigten Vergleichswert in einem eigenen Dialogfenster ab. Der Orders_View der Beispieldatenbank zu VFP enthält eine parametrisierte Zeile

AND Orders.order_date BETWEEN ?dDateFrom AND ?dDateTo

mit der die im View dargestellten Bestellungen nach Datum eingegrenzt werden können. Auch Eigenschaften von FORMs oder anderen Objekten können als Viewparameter dienen.

Views sind in VFP bedeutend flexibler einzusetzen, als im SQL-Standard vorgesehen. So können Views eine ORDER BY Klausel enthalten, Updatable Views können Unterabfragen enthalten und sich auf mehrere Tabellen beziehen, was im SQL-Standard nicht vorgesehen ist.

Das Thema Views und deren Einsatz gibt genug Stoff für einen gesonderten Vortrag ab, deshalb will ich es hier bei diesen allgemeinen Hinweisen belassen. Mit den in VFP 5 neu eingeführten Offline Views (nicht zu verwechseln mit Remote Views) befassen sich die Vorträge von Jürgen Wondzinski (deutsch) und Alan Schwartz (englisch).