Local SQL  und Visual dBASE
Autor: Michael Rotteck


1.0   Was ist Local SQL

Alle heutigen modernen Entwicklungs-Tools besitzen die Möglichkeit SQL Befehle in ihren Programmen zu verwenden. SQL wird uns in Visual dBASE 7 immer wieder über den Weg laufen. Sei es für Abfragen mit dem SQL Designer oder bei der Erstellung einer Query, um auf eine Tabelle zugreifen zu können.

Wenn Sie nur mit der neuen OODML von Visual dBASE arbeiten wollen brauchen Sie nur folgende SQL Syntax zu kennen:

SELECT * FROM Meine_Tabelle

Dieser SQL Befehl genügt um eine Query zu erzeugen, die dann weiter mit OODML bearbeitet werden kann.

Das Thema SQL ist für Visual dBASE Anwender nicht ganz einfach einzuordnen. Obwohl SQL bereits in dBASE DOS zur Verfügung stand, wurde es von den meisten Anwendern nicht benutzt. Wollte man mit SQL arbeiten mußte jedesmal per Befehl in den SQL-Modus umgeschaltet und wieder zurückgeschaltet werden. Das Gleiche galt für Programmdateien. Hier mußte unterschieden werden zwischen einer dBASE-Programmdatei (PRG) und einer SQL-Programmdatei (PRS).

Mit Visual dBASE 5.5 wurde SQL in die dBASE-Sprache integriert. Es bestand die Möglichkeit Programme mit gemeinsamen dBASE- und SQL-Befehlen zu erstellen. Leider waren die Möglichkeiten nicht besonders groß, weil die 16-Bit BDE einen sehr eingeschränkten SQL-Befehlsumfang besaß.

Mit der 32 Bit BDE sind diese Einschränkungen aufgehoben.

Sie haben richtig gelesen. SQL ist nicht in Visual dBASE selbst integriert sondern in der BDE.

Borland selbst nennt die Implementierung von SQL in der BDE Local SQL. Dies hängt damit zusammen, daß nicht der komplette Befehlsumfang des Standards SQL 92 in die BDE implementiert wurde, sondern nur die Befehle, die für lokale Datenbanken (dBASE, Paradox) benötigt werden.

So ist z.B. der SQL 92 Befehl CREATE VIEW in Local SQL nicht vorhanden. SQL (engl. Structured Query Language) ist eine komplette Sammlung von Befehlen, die den Zugriff auf eine relationale Datenbank ermöglichen. SQL beruht auf einen definierten ANSI Standard. Zur Zeit sind zwei unterschiedliche Standards definiert, ANSI SQL 89 und ANSI SQL 92, der auch SQL 2 genannt wird.

Der nächste Standard - SQL 3 - ist bereits in Planung.

Welche SQL Befehle für eine bestimmte relationale Datenbank (auch SQL Server oder RDBMS genannt) zur Verfügung stehen ist abhängig davon, welche SQL Version der Hersteller für seine Datenbank implementiert hat.

Aus diesem Grund wird innerhalb von Visual dBASE zwischen Local SQL und externem SQL unterschieden.

Bei Local SQL erfolgt die Syntaxprüfung und die Ausführung durch die BDE. Bei externem SQL erfolgt die Syntaxprüfung und Ausführung durch den entsprechenden SQL Server.

Innerhalb von Visual dBASE wird durch unterschiedliche SQL-Schreibweise unterschieden ob es sich um Local SQL oder externem SQL handelt.

Local SQL:

SELECT * FROM Meine_Tabelle

Externes SQL:

SQLEXEC("SELECT * FROM Meine_Tabelle")

Die Schreibweise für externes SQL kann auch für dbf-Tabellen verwendet werden.

Beispiel:

Wir möchten die beiden Tabellen kunden und auftrag zu einer neuen Tabelle vereinen.

Local SQL:

SELECT * FROM kunden,auftrag WHERE kunden.kundennr = auftrag.kundennr SAVE TO antwort

Die Daten werden mit dem Befehl SAVE TO in die Tabelle antwort.dbf geschrieben.

Externes SQL:

SQLEXEC("SELECT * FROM kunden,auftrag WHERE kunden.kundennr = auftrag.kundennr","antwort")

Bei externem SQL kann für eine Abfrage eine Antworttabelle benannt werden, die in der Syntax durch ein Komma nach dem SQL-Befehl angegeben wird. In dieser Antworttabelle wird das Abfrageergebnis als dbf-Tabelle gespeichert.

Wenn Sie eine Anwendung mit Visual dBASE erstellen, die auf einen SQL Server zugreift, dann brauchen Sie nur dann externes SQL verwenden, wenn Sie einen SQL Befehl benutzen wollen, der in Local SQL nicht enthalten ist. Wichtig ist, daß Sie in der BDE für den SQL Server die richtigen Einstellungen vorgenommen haben.

SQL selbst ist keine Programmiersprache. SQL wird in einem Anwendungsprogramm oder einer Programmiersprache, hier Visual dBASE, eingebunden.

SQL ist eine mächtige Abfragesprache, wobei das Abfrageergebnis in einem Ergebnisfenster angezeigt wird.

Dieses Buch kann nicht alle Befehle der SQL Syntax aufzeigen. Dieses würde den Rahmen dieses Buches sprengen. Auf dem Büchermarkt gibt es eine Anzahl von Büchern, die sich ausschließlich mit SQL beschäftigen. Die folgenden Seiten sollen Ihnen aber aufzeigen, wie man mit SQL und Visual dBASE arbeitet und welche Möglichkeiten bestehen.

2.0   Wie arbeitet SQL

Die ersten Grundlagen für SQL wurden 1970 von E.F. Codd, der bei der IBM beschäftigt war, mit einem Artikel über das relationale Datenbankmodell veröffentlicht. SQL und relationale Datenbank (SQL Server) gehören zusammen.

Nur der SQL-Server greift direkt auf die physikalischen Datenbankdateien zu. Als Befehlssprache wird SQL benutzt. Nicht der SQL-Befehl bestimmt, wie auf die Daten zugegriffen wird, sondern der SQL-Server selbst. Man kann auch sagen, mit SQL wird die Anforderung beschrieben, der SQL-Server selbst bestimmt wie die Anforderung erfüllt wird. Der SQL-Server bestimmt selbst, ob er einen vorhandenen Index benutzt und bestimmt auch selbst, ob die Datensicherheit und Datenintegrität durch den SQL-Befehl gewährleistet wird.

Jeder SQL-Befehl wird daher in vier Phasen abgearbeitet:

1. Phase Parse

Syntaxprüfung des SQL-Befehles. Der SQL-Server prüft ob der Befehl den festgelegten Formulierungsregeln entspricht. Jeder Befehl wird dazu in seine Bestandteile zerlegt. Überprüfung ob die entsprechenden Tabellen und Spalten in der Datenbank enthalten sind.

2. Phase Optimize

Der SQL-Server sucht den schnellsten Weg, um die gestellte Aufgabe abzuarbeiten. Dazu werden zuerst alle View-Tabellennamen und View-Spaltennamen durch die tatsächlichen Tabellenbezeichner ersetzt. Der nächste Schritt ist die Auslesung des Datenbankzustandes aus den Systemtabellen. Mit diesen Daten werden die möglichen Zugriffswege gesucht. Der SQL-Server errechnet dann für die möglichen Zugriffswege die Ausführungszeit und wählt dann den schnellsten Zugriffsweg aus. Dabei entscheidet der SQL-Server selbst ob Indizes verwendet werden. Bei einer umfangreichen Abfrage über mehrere Tabellen muß auch die Verknüpfung unter den einzelnen Tabellen berücksichtigt werden. In vielen Fällen erstellt der SQL-Server dazu temporäre Tabellen.

3. Phase Generate

Die tatsächlich auszuführende Befehlsfolge wird in allen Einzelschritten vorbereitet und in binärer Form abgelegt.

4. Phase Execute

Der SQL-Befehl wird ausgeführt.

Anmerkung:

Die Phase 2, Optimierung des SQL-Statements, entscheidet mit seinen Eigenschaften und Fähigkeiten wie schnell ein SQL-Server ist.

2.1   Aufbau des SQL-Befehles

Im Gegensatz zu anderen Programmiersprachen wird eine SQL-Anweisung nicht zeilenweise von oben nach unter abgearbeitet. Der Grund dafür ist, daß die beschriebenen vier Phasen abgearbeitet werden müssen.

In Visual dBASE muß ein SQL-Befehl in einer Zeile erstellt werden. Er darf nicht, wie dBASE-Befehle, aus mehreren Zeilen bestehen. Soll ein SQL-Befehl der Übersicht wegen auf mehrere Zeilen verteilt werden, dürfen keine Leerzeilen in dem SQL-Befehl enthalten sein. Das Zeichen “;” (Semikolon) darf für eine Folgezeile benutzt werden, wobei hier es wichtig ist, daß zwischen dem letzten Zeichen und dem Semikolon unbedingt ein Leerschritt enthalten sein muß.

Der SQL Syntax kann in Groß- oder Kleinbuchstaben erstellt wird. Der besseren Übersicht sollte man SQL-Befehle wie üblich in Großbuchstaben schreiben.

Ein SQL-Befehl für Abfragen fängt immer mit SELECT an.

Die SELECT Syntax:

SELECT [DISTINCT] <* | Spaltennamen>
FROM <Liste Tabellennamen>
[ WHERE <Suchbedingungen> ]
[ GROUP BY <Spaltennamen> ]
[ HAVING <Suchbedingungen> ]
[ UNION <Unterabfrage> ]
[ ORDER BY <Liste Spaltennamen> ]
[ SAVE TO <Tabellenname> ]
Nach dem SELECT können die Spaltennamen aufgeführt werden, die für eine Abfrage verwendet werden sollen. Will man alle Spaltennamen für die Abfrage verwenden genügt das Zeichen *.

SELECT kundennr,name,plz,ort
oder
SELECT *

Mit FROM werden die Tabellennamen aufgezählt, die für die Abfrage benutzt werden.

SELECT kundennr,name,plz,ort FROM Meine_Tabelle

Werden für die Abfrage mehrere Tabellen verwendet, muß der Tabellenname vor dem entsprechenden Spaltennamen eingegeben werden. Die Trennung zwischen Tabellennamen und Spaltennamen erfolgt mit dem Zeichen Punkt.

SELECT Kunden.kundennr,Kunden.name,Auftrag.auftragnr FROM kunden,auftrag

Ähnlich wie bei Visual dBASE können wir für die Tabellennamen einen Alias-Namen verwenden, der uns eine einfachere Schreibweise für die Tabellennamen erlaubt. In SQL nennt man den Alias-Namen ein Synonym. Dieser wird in der FROM Klausel nach dem Tabellennamen mit einem Leerschritt eingegeben.

SELECT K.kundennr,K.name,A.auftragnr FROM kunden K,auftrag A

Mit dieser SQL-Syntax würden wir Datenmüll produzieren. Wir benötigen jetzt noch einen Vergleichsoperator in unserer SQL-Abfrage. Mit diesen Vergleichsoperator stellen wir eine Verknüpfung über die Spaltennamen kundennr zwischen diesen beiden Tabellen her. Es muß nicht in beiden Tabellen der gleiche Spaltenname vorhanden sein. Dieser könnte in der einen Tabelle ganz anders lauten. Wichtig ist bei der Verknüpfung, daß die Spaltenwerte vom gleichen Typ sind.

SELECT K.kundennr,K.name,A.auftragnr FROM kunden K,auftrag A WHERE K.kundennr = A.kundennr

Möchten wir jetzt noch das Ergebnis nach der kundennr sortiert haben fügen wir eine ORDER BY Klausel ein.

SELECT K.kundennr,K.name,A.auftragnr FROM kunden K,auftrag A WHERE K.kundennr = A.kundennr ORDER BY K.kundennr

ORDER BY

ORDER BY führt immer einen Sortiervorgang durch. Ist ein einfacher Index für den angegebenen Spaltennamen vorhanden, wird dieser von ORDER BY verwendet. Dieses unterschiedliche Verhalten der ORDER BY Klausel hat Auswirkungen auf unsere Abfrageergebnisse.

Wird eine Abfrage (Query) auf eine Tabelle durchgeführt mit ORDER BY und für den Spaltennamen ist ein einfacher Index vorhanden, dann ist das Abfrageergebnis änderbar. Ist für den Spaltennamen kein einfacher Index vorhanden führt ORDER BY eine Sortierung durch und das Abfrageergebnis ist Read-Only. Eine Sortierung hat natürlich auch Auswirkungen auf die Performance. Die Verwendung eines Indizes ist immer schneller.

Bei einer ORDER BY Klausel muß nicht immer ein Spaltenname angegeben werden. Es kann auch die Spaltennummer verwendet werden.

SELECT * FROM kunden ORDER BY kundennr

In dieser Anweisung muß der Spaltenname angegeben werden, weil nach der SELECT Anweisung keine Spalten aufgeführt wurden.

SELECT kundennr,name,staat FROM kunden ORDER BY 1

Bei dieser Anweisung erfolgt die Sortierung nach der Spaltennummer 1, der kundennr.

Man sollte der besseren Lesbarkeit immer den entsprechenden Spaltennamen in der ORDER BY Klausel angeben. Möchte man aber nach Ergebnissen von Aggregatfunktionen eine Sortierung durchführen ist diese Möglichkeit der Angabe der Spaltennummer sehr nützlich.

DISTINCT

In einer SELECT-Anweisung kann mit DISTINCT bestimmt werden, daß doppelte Zeilenwerte in der Abfrage unterdrückt werden.

Bei Verwendung von DISTINCT sind einige Regeln zu beachten:

DISTINCT kann in jeder SELECT-Anweisung nur einmal verwendet werden.

DISTINCT wird nur dann ausgeführt, wenn in der SELECT-Anweisung ein einzelner Spaltenname angegeben wird. Werden mehrere Spaltennamen verwendet, hat der Befehl DISTINCT keine Wirkung.

Beispiel:

In der Tabelle Auftrag sind für einen Kunden mehrere Aufträge vorhanden. Möchte man wissen für welche Kunden Aufträge vorhanden sind, will aber die doppelte Anzeige von Kunden-Nummern unterdrücken, wird in der Abfrage DISTINCT verwendet.

SELECT DISTINCT kundennr FROM auftrag ORDER BY kundennr

Das Abfrageergebnis zeigt sortiert nach Kunden-Nummer, die Kunden an, bei denen Aufträge vorhanden sind.

Die gleiche Abfrage mit einem weiteren Spaltennamen, Auftrag-Nummer zeigt uns ein anderes Ergebnis an:

SELECT DISTINCT kundennr,auftragnr FROM auftrag ORDER BY kundennr

In dieser Abfrage werden durch den Spaltennamen auftragnr die Kunden mehrfach mit der entsprechenden Auftrags-Nummer angezeigt. DISTINCT hat bei dieser Abfrage keine Wirkung.

SQL-Befehle werden in Querys verwendet, können auch über das Befehlsfenster direkt eingegeben und in Programmen mit dBASE-Befehlen gemischt werden.

2.2   Besondere Schreibweisen in Local SQL

Wenn Sie sich im Kapitel 5 SQL Designer die SQL-Code-Beispiele angesehen haben, werden Sie feststellen, daß der SQL Designer bei den Tabellennamen immer das Verzeichnis und den Tabellentyp in die SQL-Anweisung geschrieben hat. Dies ist nicht notwendig. In den Desktop-Eigenschaften haben Sie ja bestimmt mit welchem Tabellentyp Sie arbeiten wollen. Das Verzeichnis wo sich die Tabellen befinden brauchen Sie nur dann in die SQL-Anweisung zu schreiben, wenn Sie Tabellen verknüpfen, die sich in unterschiedlichen Verzeichnissen befinden.

Verwenden Sie für eine SQL-Abfrage nur dBASE-Tabellen, brauchen Sie nur den Namen der Tabelle anzugeben.

Verwenden Sie Paradox-Tabellen oder mischen Sie für eine Abfrage DBF- und Paradox-Tabellen, dann müssen Sie den Tabellentyp mit angeben.

Beispiel, SQL-Abfrage mit dBASE- und Paradox-Tabellen.

SELECT K.kundennr,K.name,A.auftragnr FROM kunden.dbf K,auftrag.DB A
WHERE K.kundennr = A.kundennr

Alias-Namen verwenden

Wenn Sie für Ihre Abfrage Tabellen aus verschiedenen Datenbanken verwenden wollen, müssen Sie vor dem Tabellennamen den Alias-Namen eingeben, der durch das Zeichen Doppelpunkt am Anfang und Ende eingeschlossen wird. Es spielt für Visual dBASE keine Rolle, ob es sich dabei um dbf-Datenbanken oder um SQL-Server handelt.

Die Datenbanken müssen nur vorher geöffnet worden sein über ein Datenbankobjekt oder mit dem Befehl OPEN DATABASE.

SELECT * FROM :mugs:customer

Beispiel für eine Abfrage mit Interbase und dbf-Datenbank:

OPEN DATABASE intrbase1 OPEN DATABASE auftrag

intrbase1 ist der Alias-Name für den Interbase SQL-Server, auftrag der Alias-Name für die dbf-Datenbank. Beide Alias-Namen sind in der BDE eingetragen.

SELECT * FROM :intrbase1:customer C,:auftrag:kunde A WHERE C.cust_no = A.kundennr

Wichtig bei diesem Beispiel ist, daß die Feldtypen für die Verknüpfung gleich sind.

Spaltennamen mit Leerschritt und reservierte Local SQL Wörter

Spaltennamen in Tabellen sollten keine Leerschritte enthalten. Wenn diese doch vorhanden sind, hat das für die Schreibweise für Local SQL Auswirkungen.

Bei einer Aufzählung von Spalten oder bei einer WHERE Klausel müssen in diesem Fall die Spaltennamen in Hochkommas eingeschlossen werden und es muß der Tabellenname oder das Synonym vorangestellt werden.

SELECT * FROM state S WHERE S."state id" = "AA"
oder
SELECT S."state id",S.state FROM state S WHERE S."state id" = "AA"

Man kann das sehr schön ausprobieren an den mitgelieferten Beispiel-Tabellen, die sich im Verzeichnis \SAMPLES\MUGS befinden. Hier gibt es jede Menge Spaltennamen mit Leerschritt.

Die gleiche Schreibweise gilt auch, wenn Ihr Spaltenname einem reservierten Local SQL Wort entspricht, z.B. Spaltenname ist DATE.

Dateinamen mit Leerschritt und reservierte Local SQL Wörter

Wenn Sie Dateinamen verwenden, die einen Leerschritt enthalten, müssen Sie den Tabellennamen in der FROM Klausel mit Hochkommas einschließen.

SELECT * FROM "Meine Tabelle"

Wenn Sie einen Tabellennamen benutzen der einem reservierten Local SQL Wort entspricht, dann müssen Sie ebenfalls in der FORM Klausel den Tabellennamen in Hochkommas setzen.

Eine Liste aller reservierten Local SQL Wörter finden Sie in der Online Hilfe und in der Dokumentation.

SELECT * FROM "position"

Feste Vergleichswerte

Wenn es sich um einen Spaltennamen vom Typ Zeichen handelt, muß der Vergleichswert in Hochkommas eingeschlossen werden.

SELECT * FROM kunden WHERE kundennr = "1221"

Ist der Spaltenname vom Typ numerisch, wobei es keine Rolle spielt um was es sich für einen numerischen Typ handelt, wird der Vergleichswert ohne Hochkommas geschrieben. Möchten Sie einen Vergleich mit Dezimalstellen erstellen, muß die amerikanische Schreibweise verwendet werden.

SELECT * FROM kunden WHERE akt_umsatz = 1515.23

Erfolgt die Abfrage auf ein Datumsfeld, kann jetzt mit der Version 7.01 auch die deutsche Schreibweise für das Datum verwendet werden. Das Datum muß in Hochkommas stehen.

SELECT * FROM kunden WHERE kontakt = "18.01.98"

Benutzt die Abfrage ein logisches Feld muß True oder False verwendet werden.

SELECT * FROM kunden WHERE liquid = True

Parameter

Eine Abfrage kann anstelle fester Vergleichswerte auch einen Parameter als Vergleichswert benutzen. Das kann entweder eine Variable oder ein Wert aus einem ARRAY sein.

Die Parameter-Übergabe wird mit dem Zeichen Doppelpunkt definiert.

SELECT * FROM kunden K WHERE K.kundennr = :cKdnr
oder
SELECT * FROM kunden K WHERE K.kundennr = :Mein_Array[1,1]

Makro-Operator

Für die Angabe der Tabellennamen kann auch der Makro-Operator verwendet werden.

cTabelle = "kunden"
SELECT * FROM &cTabelle

Der Makro-Operator kann auch in der OODML für die dynamische Erzeugung einer Query verwendet werden.

q = New Query()
q.sql = "SELECT * FROM &cTabelle"
q.active = true
BROWSE

Für die Erstellung einer SQL-Anweisung für die Komponente Query im Formular Designer kann der Makro-Operator nicht verwendet werden.

3.0   Die SQL Kommandoklassen

SQL ist in folgende Befehlskategorien eingeteilt:

DDL Data Definition Language

Mit den Befehlen der DDL werden Tabellen angelegt, gelöscht, verändert, Indizes angelegt.

Local SQL unterstützt folgende Befehle:

ALTER TABLE

Ändern der Structure bei einer vorhandenen Tabelle

CREATE INDEX

Anlegen eines einfachen Index

CREATE TABLE

Tabelle neu anlegen

DROP INDEX

Löschen von vorhandenen Index

DROP TABLE

Tabelle löschen

Wie diese Local SQL-Befehle innerhalb von Visual dBASE verwendet werden, wird im Kapitel 4.7 Tabellen mit Local SQL erstellen beschrieben.

Wenn Sie einen SQL-Server mit Visual dBASE verwenden, stehen die entsprechenden DDL Befehle für diesen Server zu Verfügung und können von Visual dBASE genutzt werden.

CREATE VIEW

Erstellen einer speziellen Sicht auf Tabellen mit und ohne Verknüpfung

DROP VIEW

Vorhandene Sicht wird gelöscht

Diese Befehle können nur in Zusammenhang mit dem Interbase SQL-Server oder einen anderen SQL-Server verwendet werden.

DML Data Manipulation Language

Mit diesen Befehlen werden Daten hinzugefügt, verändert und gelöscht.

DELETE

Löscht Daten

INSERT

Fügt Daten in eine Tabelle ein

UPDATE

Aktualisiert einen Dateninhalt

Diese Befehle können mit weiteren SQL-Befehlen verknüpft werden.

DQL Data Query Language

Alle Anweisungen starten für eine Abfrage mit dem SELECT Befehl.

TCL Transaction Control Language

Die Transaktionsbefehle stellen die Sicherheit der Datenintegrität zur Verfügung. Entweder wird die Verarbeitung durchgeführt oder komplett abgebrochen.

COMMIT

Bestätigt die Aktionen innerhalb einer Transaktion

ROLLBACK

Verwirft alle Aktionen innerhalb einer Transaktion

Ob der Befehl COMMIT immer automatisch oder per Programm ausgeführt werden soll, wird in der BDE mit den entsprechenden SQL-Links für den SQL-Server eingestellt.

Diese Befehle sind nicht für Local SQL gültig.

DAL Database Administration Language

Diese SQL-Befehle sind für den Datenbankadministrator bestimmt und sind abhängig von der verwendeten Datenbank. Die einzelnen Befehle sind nicht in Local SQL enthalten.

ALTER DATABASE

Modifiziert bestehende Datenbank

ALTER PASSWORD

Ändert ein Passwort

CHECK DATABASE

Prüft die Datenintegrität einer Datenbank

CREATE DATABASE

Legt eine neue Datenbank an

DEINSTALL DATABASE

Meldet eine Datenbank beim SQL-Server ab, die Datenbank wird nicht gelöscht.

DROP DATABASE

Löscht eine Datenbank

GRANT

Vergibt Zugriffsrechte auf die einzelnen SQL-Tabellen

INSTALL DATABASE

Meldet eine Datenbank beim SQL-Server an

REVOKE

Entfernt Zugriffsrechte

Dies ist nur eine Auflistung der wichtigsten Befehle. Weitere SQL-Server abhängige Befehle finden Sie in der entsprechenden Dokumentation.

4.0   Local SQL-Befehle

SQL- und Local SQL-Befehle sind unterteilt nach Befehlsgruppen und unterscheiden sich nach ihren Anwendungen und Ergebnissen.

Alle Beispiele verwenden die Tabellen KUNDEN.DBF, AUFTRAG.DBF und POS.DBF, die diesem Buch beiliegen.

Jeder SQL-Befehl erzeugt eine Antworttabelle, die Sie sich mit BROWSE oder EDIT im Befehlsfenster ansehen können.

4.1   Ausdrücke

In dBASE sind Ausdrücke Kalkulationsfelder. Auch Local SQL kann temporäre Spalten in Abfragen erzeugen und diese können auch angezeigt werden.

Durch den Zeichenverkettungsoperator “||” kann man mehrere Spalteninhalte in eine Ergebnisspalte zusammenfassen.

SELECT plz,Stadt FROM kunden

zeigt die beiden Spalten an.

SELECT kundennr,plz||" "||stadt FROM kunden

zeigt die beiden Spalten PLZ und STADT als eine gemeinsame Ergebnisspalte an. Der Spaltenname wird von Local SQL automatisch aus den beiden Spaltennamen gebildet.

Es können nur Spalten verkettet werden vom Typ Zeichen. Bei anderen Spaltentypen müssen diese über die CAST-Funktion nach Zeichen konvertiert werden.

Möchte man für die Ergebnisspalte selber einen aussagenden Spaltennamen definieren steht hierfür der Befehl AS zur Verfügung.

SELECT kundennr,plz||" "||stadt AS neu FROM kunden

Mit AS kann man für jeden Spaltennamen für die Anzeige eine neue Spaltenbezeichnung definieren.

SELECT kundennr AS KDNR,plz||" "||stadt AS neu FROM kunden

Ein Ausdruck (Kalkulationsfeld) kann auch das Ergebnis einer Berechnung sein.

SELECT kundennr,rbetrag,zbetrag,(rbetrag-zbetrag) AS obetrag FROM kunden

4.2   Berechnungen

Rechenfunktionen können auch definiert werden.

SELECT kundennr,akt_umsatz+100 FROM kunden

Bei Berechnungen können feste Werte oder Werte aus Spalten, die über eine Verknüpfung vorhanden sind, verwendet werden.

Bei Berechnungen werden die mathematischen Regeln Punkt vor Strich und die Klammerregeln verwendet.

SELECT kundennr,akt_umsatz+100*2 FROM kunden

ergibt ein anderes Ergebnis als

SELECT kundennr,(akt_umsatz+100)*2 FROM kunden

Mit Datumswerten können ebenfalls in Local SQL Berechnungen durchgeführt werden.

SELECT kundennr,kontakt+30 FROM kunden

ergibt eine Ergebnisspalte mit dem Wert Datum kontakt plus 30 Tage für ein neues Datum.

Möchte man die Differenz von zwei Datumwerten berechnen, müssen diese als Feldtyp Datum vorhanden sein.

SELECT kundennr,verk_datum-kontakt FROM kunden,auftrag
WHERE kunden.kundennr=auftrag.kundennr

Das Ergebnis ist die Differenz in Tagen.

Innerhalb von Local SQL stehen die 4 Grundrechenarten zur Verfügung:

+    Addition
-    Subtraktion
*    Multiplikation
/    Division

4.3   Auswahlbedingungen

SQL arbeitet mengenorientiert. Ohne eine Einschränkung erhalten wir alle Daten in unsere Antworttabelle. Dieses Verhalten ist nicht immer gewünscht. Um Daten für eine Abfrage einzuschränken, werden über die WHERE Klausel entsprechende Auswahlkriterien definiert.

In jedem SELECT, UPDATE, INSERT oder DELETE Befehl können Sie WHERE Klauseln definieren.

Für die WHERE Klauseln stehen in Local SQL verschiedene Operatoren zur Verfügung:

Vergleichsoperatoren

Der Vergleichsoperator vergleicht den Wert in einer Tabellenspalte mit der angegebenen Suchbedingung.

Folgende Operatoren stehen in Local SQL zur Verfügung:

=     Gleich
!=    Ungleich (entspricht NOT (a=b))
<>   Ungleich
>     Größer als
<     Kleiner als
!>    Nicht größer als
!<    Nicht kleiner als
>=   Größer/gleich als
<=   Kleiner/gleich als

BETWEEN Legt einen Wertebereich für untere und obere Grenze fest

IN Vergleicht, ob der jeweilige Spaltenwert in einer angegebenen Vergleichsmenge enthalten ist

IS NULL Vergleicht, ob der Spaltenname keinen Wert besitzt.

Beispiel: kein Wert in einem numerischen Feld, auch nicht die Zahl Null

LIKEVergleich auf teilweise Übereinstimmung in einer Zeichenkette. Im Suchbegriff dürfen Jokerzeichen verwendet werden.
%    steht für beliebige folgende Zeichen.
_(Unterstrich) Joker für ein einzelnes Zeichen.

Allgemein gilt, daß bei Vergleichen mit Zeichenketten die Groß-/Kleinschreibung beachtet wird.

Berechnung

Das Suchkriterium kann auch berechnet werden, wobei die 4 Grundrechenarten zur Verfügung stehen.

Logische Bedingungen

Einzelne Suchkriterien können über logische Verknüpfungen miteinander kombiniert werden. Zur Verfügung stehen:

AND    OR    NOT

Anmerkung: Die logischen Verknüpfungen werden nicht mit Punkt eingeschlossen.

Die Suchkriterien können über verschiedene Arten definiert werden:

Feste Werte

Einige Beispiele:

Eine bestimmte Kunden-Nummer auswählen:

SELECT kundennr,plz,strasse FROM kunden WHERE kundennr = "1221"

Kunden anzeigen, die am 03.04.90 den ersten Kontakt katten.

SELECT kundennr,name,kontakt FROM kunden WHERE kontakt = "03.04.90"

Kunden, deren Umsatz größer als 10.000 DM ist.

SELECT kundennr,name,akt_umsatz FROM kunden WHERE akt_umsatz > 10000

Berechnete Werte

Vergleichswerte können auch aus Berechnungen bestehen.

Das Feld akt_umsatz enthält den Bruttoumsatz. Angezeigt werden Kunden, deren Nettoumsatz kleiner 2.000 ist.

SELECT kundennr,name,akt_umsatz/1.16 AS Netto FROM kunden WHERE akt_umsatz/1.16 < 2000

Werte per Parameter

Der Vergleichswert kann über eine Variable als Parameter übergeben werden. Parameter werden mit dem Zeichen Doppelpunkt gekennzeichnet.

nUmsatz = 2000
SELECT kundennr,name,akt_umsatz/1.16 AS Netto FROM kunden WHERE akt_umsatz/1.16 <:numsatz

Vergleich über zwei Spaltenwerte

Anzeige aus zwei Tabellen (Kunden und Auftrag), wo die Kunden-Nummer identisch ist.

SELECT K.kundennr,K.name,A.auftragsnr FROM kunden K,auftrag A WHERE K.kundennr = A.kundennr

Unterabfragen

SQL bietet die Möglichkeit Unterabfragen (engl. Subqueries) zu verwenden. Das Result Set einer zweiten Abfrage bildet den Vergleichswert für die erste Abfrage.

SELECT K.kundennr,K.name FROM kunden K
WHERE K.kundennr = (SELECT A.kundennr FROM auftrag A WHERE A.auftragnr = "1001")

Wichtig: Das Ergebnis der Unterabfrage darf nur einen bestimmten Wert liefern.

Weitere Beispiele

Anzeige Kunden, die einen bestimmten Umsatzbereich erfüllen.

SELECT kundennr,name,akt_umsatz FROM kunden WHERE akt_umsatz BETWEEN 1000 AND 5000

Anzeige Kunden, wo der Kontakt zwischen dem 01.01.1990 und 31.05.1990 statt fand.

SELECT kundennr,name,akt_umsatz,kontakt FROM kunden
WHERE kontakt BETWEEN "01.01.90" AND "31.05.90"

Anzeige Kunden, wo der Kontakt zwischen dem 01.01.1990 und 31.05.1990 statt fand und der Umsatz größer als 5.000 DM ist.

SELECT kundennr,name,akt_umsatz,kontakt FROM kunden
WHERE kontakt BETWEEN "01.01.90" AND "31.05.90" AND akt_umsatz > 5000

Anzeige Kunden, die in einem bestimmten Postleitzahlbereich vorhanden sind.

SELECT kundennr,name,plz FROM kunden WHERE plz BETWEEN "10000" AND "19999"

Man muß mit BETWEEN nicht den kompletten Wertebereich angeben. Bei diesem Beispiel werden alle Kunden angezeigt, die sich im Postleitzahlbereich von 20000 bis 22 befinden. Ein Kunde mit der Postleitzahl 220 wird nicht angezeigt.

SELECT kundennr,name,plz FROM kunden WHERE plz BETWEEN "2" AND "22"

Vergleich auf Spalteninhalte. Auswahl aller Kunden, die sich in der BRD und in der Schweiz befinden.

SELECT kundennr,name,staat FROM kunden WHERE staat = "BRD" OR staat = "Schweiz"

Das selbe Ergebnis erhalten wir mit dem IN-Befehl:

SELECT kundennr,name,staat FROM kunden WHERE staat IN ("BRD","Schweiz")

Anzeige von Kunden, wo noch kein Umsatz erzielt wurde.

SELECT kundennr,name,akt_umsatz FROM kunden WHERE akt_umsatz IS NULL

Abfragen mit logischen Feldern. Hier muß der Vergleichswert True oder False ausgeschrieben werden.

SELECT kundennr,name,liquid FROM kunden WHERE liquid = True

Anzeige aller Kunden, wo im Namen der Begriff "Tauch" enthalten ist.

SELECT kundennr,name FROM kunden WHERE name LIKE "%Tauch%"

Nächstes Beispiel, wo der Begriff "Tauch" am Anfang eines Namens enthalten ist.

SELECT kundennr,name FROM kunden WHERE name LIKE "Tauch%"

Beispiel, wo ein Begriff am Ende eines Namens enthalten ist, hier "glocke"

SELECT kundennr,name FROM kunden WHERE name LIKE "%glocke"

Beispiel, wenn die genaue Schreibweise in einem Begriff nicht bekannt ist, Verwendung des Jokerzeichens "_".

SELECT kundennr,name FROM kunden WHERE name LIKE "%g_ocke"

Bis auf Vergleichswerte, die sich auf logische Felder beziehen, können Sie alle Vergleiche mit NOT negieren.

NOT BETWEEN, IS NOT NULL, NOT LIKE.

Wie in der Mathematik verwendet SQL eine bestimmte Reihenfolge bei der Abarbeitung der Auswahlkriterien.

Die NOT-Anweisung hat Vorrang vor AND. Demgegenüber hat AND wiederum Vorrang vor OR. Gleiche Operatoren werden von links nach rechts abgearbeitet.

Durch setzen von Klammern kann bestimmt werden, welche Anweisung Vorrang hat.

4.4   String Funktionen

Bisher mußten wir bei Vergleichswerten, die Zeichenketten (Strings) enthielten, genau die Schreibweise beachten.

Local SQL bietet hier folgende Funtionen an:

UPPER() und LOWER()

UPPER() wandelt eine Zeichenkette in die Großschreibung und LOWER() in die Kleinschreibung um. Dabei wird unterschieden, ob diese beiden Funktionen in Zusammenhang mit einen Spaltennamen oder mit einen Suchbegriff verwendet werden. Wenn wir bei folgendem Beispiel nicht die genaue Schreibweise einhalten erhalten wir keine korrekte Antworttabelle:

SELECT * FROM kunden WHERE staat = "brd"

Die Antworttabelle enthält keine Datensätze. Der Suchbegriff ist in der Spalte in Großbuchstaben eingetragen.

Mit UPPER() wird der Suchbegriff in die Großschreibung umgewandelt und wir erhalten ein Ergebnis.

SELECT * FROM kunden WHERE staat = UPPER("brd")

Eine andere Funktion haben UPPER() und LOWER() wenn man sie mit einem Spaltennamen verwendet.

SELECT * FROM kunden WHERE LOWER(staat) = "brd"

Hier wird der Spaltenname mit LOWER() in Kleinschreibung umgewandelt und mit dem Suchbegriff verglichen. Dieser muß auch in der Kleinschreibung eingeben werden.

Hierbei werden alle Daten angezeigt, die der Schreibweise des Suchbegriffes entsprechen. Es ist dann gleichgültig ob in der Spalte Staat “brd” oder “BRD” oder “bRd” steht.

Mit UPPER() ist es genau umgekehrt. Hier wird in die Großschreibung umgewandelt.

SELECT * FROM kunden WHERE UPPER(staat) = "BRD"

Diese Möglichkeit der Umwandlung in Groß- und Kleinschreibung ist sehr nützlich in Verbindung mit LIKE. Hier kann nach Begriffen gesucht werden, wobei die Schreibweise ignoriert wird.

SELECT * FROM kunden WHERE LOWER(name) LIKE "%wasser%"

Es werden alle Daten angezeigt, in dem der Begriff “wasser” in der Spalte namen vorkommt, unabhägig der Schreibweise.

TRIM()

TRIM() gibt eine Zeichenkette zurück, die bis auf alle führenden und / oder angehängten Füllzeichen mit der angegebenen Zeichenkette identisch ist.

Beispiel:

Der Name in der Spalte kundenname ist eingeschlossen mit den Zeichen "" (“Borland”) und diese Zeichen sollen bei einer Abfrage nicht angezeigt werden.

SELECT kundennr,TRIM(BOTH '"' FROM name) FROM kunden

Mit BOTH werden die Zeichen " am Anfang und Ende in der Anzeige entfernt. Das Zeichen " wird in einfachen Hochkomma eingeschlossen.

SELECT kundennr,TRIM(LEADING '"' FROM name) FROM kunden

entfernt nur das Zeichen " am Anfang.

SELECT kundennr,TRIM(TRAILING '"' FROM name) FROM kunden

entfernt das Zeichen " am Ende.

SUBSTRING()

Nimmt eine Teil-Zeichenkette aus einer Zeichenkette heraus.

SELECT kundennr,SUBSTRING(name FROM 1 FOR 3) FROM kunden

Zeigt nur die ersten drei Zeichen des Spalteninhaltes name an.

SELECT kundennr,name FROM kunden WHERE SUBSTRING(name FROM 1 FOR 3) = "Die"

Zeigt nur alle Kunden an, deren Name an den ersten drei Stellen mit dem Zeichen “Die” anfängt.

4.5   Datum Funktionen

Mit dem Befehl EXTRACT() können aus einem Datum die Werte in eine Zahl umgewandelt werden.

Dafür stehen folgende Funktionen zur Verfügung:

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

SELECT kundennr,name,EXTRACT(YEAR FROM kontakt) FROM kunden

Bei diesem Beispiel, wird aus dem Datum der Spalte kontakt das Jahr in eine Zahl gewandelt.

4.6   Typumwandlung

Generell gilt, daß nur gleiche Datentypen in einer Suchbedingung verwendet werden dürfen. Über die CAST-Funktion stellt Local SQL eine begrenzte Typumwandlung zur Verfügung.

Folgende Tyumwandlungen lassen sich realisieren:

von NUMERIC nach CHARACTER oder DATE

von CHARACTER nach NUMERIC oder DATE

von DATE nach CHARACTER oder NUMERIC

Das folgende Beispiel wandelt die Kundennr (Zeichen) in eine Zahl um und stellt diese in der Abfrage als neue Spalte Neue_Kundennr dar.

SELECT CAST((kundennr) AS NUMERIC(4)) AS Neue_Kundennr FROM kunden

Dieses Beispiel wandelt das Datum von Feld kontakt in ein Zeichenfeld um.

SELECT CAST((kontakt) AS CHARACTER(10)) AS Neues_Datum FROM kunden

Der Befehl CAST hilft Ihnen auch weiter, wenn Sie aus einem Feld vom Typ Zeitstempel nach einem Datum selektieren wollen. Die Darstellung in dem Feldtyp Zeitstempel ist Datum HH:MM:SS. Um nach das entsprechende Datum abfragen zu können, wird in der WHERE-Klausel das Feld Zeitstempel nach Zeichen umgewandelt.

SELECT * FROM Meine_Tabelle WHERE CAST((zeitstempel) AS CHARACTER(10)) = '28.08.1998'

Eine andere Möglichkeit für eine Darstellung und Abfrage auf eine Spalte vom Typ Zeitstempel wird mit diesem SELECT-Befehl aufgezeigt, wobei die Spalte zeitstempel in ein Datum umgewandelt wird.

SELECT CAST((zeitstempel) AS DATE) FROM Meine_Tabelle
WHERE CAST((zeitstempel) AS DATE) = "28.08.1998"

4.7   Mengenbezogene Operatoren

Local SQL unterscheidet zwei Arten von Operatoren, die Vergleichsoperatoren und die mengenbezogenen relationalen Operatoren.

Der mengenbezogene relationale Operator vergleicht den ersten Ausdruckswert mit einer bestimmten Menge von Werten, die aus einer weiteren SELECT-Anweisung stammen.

Diese verschachtelten SELECT-Anweisungen im WHERE-Abschnitt des SQL-Befehles werden auch als Subselect oder Subquery bezeichnet. Das Ergebnis aus dem Subselect wird Result Set genannt.

IN

Es wird geprüft, ob der Wert in der Subselect-Menge enthalten ist. Der IN-Operator kann nur mit dem Vergleichsoperator "=" verwendet werden.

SELECT * FROM kunden WHERE kundennr IN (SELECT kundennr FROM auftrag)

ANY

Der einzelne Ausdruck wird mit jedem Wert aus der Subselect-Menge verglichen. Beim ersten Treffer liefert ANY den Wert "True" zurück.

ALL

Der einzelne Ausdruck wird mit jedem Ausdruck aus der Subselect-Menge verglichen. Ist dabei jede dieser einzelnen Abfragen wahr, so ist auch das Endergebnis wahr.

EXISTS

EXISTS überprüft, ob eine bestimmte Zeile in einer Tabelle vorhanden ist. Der Ausdruck ist immer dann "True", wenn das Result Set des Subselects mindestens einen Datensatz enthält.

4.8   Aggregatfunktionen

Aggregatfunktionen führen Berechnungen aus, die als Ergebnis einen Gesamtwert anzeigen und beziehen sich immer auf einen Spaltennamen.

MIN
Minimalwert aller Werte

MAX
Maximalwert aller Werte

COUNT(*)
Anzahl aller vorhandenen Zeilen

COUNT(DISTINCT)
Anzahl der eindeutigen Zeilen, die jeweils einen anderen Wert aufweisen.

SUM
Summe aller Werte

AVG
Mittelwert aller Werte

SELECT MIN(akt_umsatz) FROM kunden

zeigt den kleinsten Umsatzwert von irgendeinen Kunden an.

SELECT MAX(akt_umsatz) FROM kunden

zeigt den größten Umsatzwert von irgendeinen Kunden an.

Einen Nachteil haben diese Aggregatfunktionen. Wenn uns interessiert, welcher Kunde den größten Umsatzwert besitzt erhalten wir bei folgender SELECT-Anweisung eine Fehlermeldung:

SELECT kundennr,name,MAX(akt_umsatz) FROM kunden

Aggregatfunktionen liefern bei einfachen SELECT-Anweisung nur einen Wert zurück. Die Angabe von weiteren Spalten ist nicht möglich.

Um ein aussagefähiges Ergebnis zu erhalten erstellen wir eine Abfrage, die entweder eine GROUP BY - Klausel oder eine Unterabfrage enthält.

SELECT kundennr,MAX(akt_umsatz) FROM kunden GROUP BY kundennr
oder
SELECT kundennr,name,akt_umsatz FROM kunden
WHERE akt_umsatz = (SELECT MAX(akt_umsatz) FROM kunden)

Bei der Abfrage mit der GROUP BY - Klausel erhalten wir für jeden Kunden den aktuellen Umsatzwert. Das Ergebnis dieser Abfrage würde aber nicht unserer Abfrageanforderung entsprechen. Die Abfrage mit einer Unterabfrage erzeugt das richtige Ergebnis.

Wenn Sie sich die Syntax genau ansehen, werden Sie feststellen, daß SQL es erlaubt zwei SELECT-Anweisungen für die gleiche Tabelle zu zulassen.

Auf der anderen Seite zeigen uns die Aggregatfunktionen mit einer SELECT-Anweisung alle statistischen Werte an:

SELECT MIN(akt_umsatz) AS Klein_Umsatz,MAX(akt_umsatz) AS Gross_Umsatz,SUM(akt_umsatz) AS Gesamt,AVG(akt_umsatz) AS Mittelwert FROM kunden

4.9   Zeilen gruppieren

Um Auswertungen nach bestimmten Kriterien zu gruppieren wird die GROUP BY Klausel verwendet.

In unserer Kunden-Tabelle befinden sich Kunden aus verschiedenen Ländern. Wenn man jetzt wissen will, wie hoch der Umsatz je Land ist, dann können wir das durch ein Gruppenkriterium bestimmen, in diesem Fall die Spalte staat.

SELECT staat,SUM(akt_umsatz) FROM kunden GROUP BY staat

Als Ergebnis erhalten wir eine Summe aller Umsätze, je Gruppe staat. GROUP BY sortiert auch gleichzeitig nach staat.

In unserer Kunden-Tabelle haben wir noch eine Spalte Land, mit der wir eine Unterteilung nach Bundesländern innerhalb eines Staates durchführen können. Durch Auflistung der Spaltennamen in der GROUP BY Klausel können wir diese Abfrage realisieren.

SELECT staat,land,SUM(akt_umsatz) FROM kunden GROUP BY staat,land

Interessieren uns jetzt nur die Umsätze für Deutschland, unterteilt nach Bundesländern, dann können wir über die WHERE Klausel diese Einschränkung bestimmen:

SELECT staat,land,SUM(akt_umsatz) FROM kunden WHERE staat = "BRD" GROUP BY staat,land

GROUP BY führt automatisch eine Sortierung gemäß den angegebenen Spalten in der GROUP BY Klausel durch. Möchte man das Ergebnis sortiert nach Summe akt_umsatz dargestellt haben, wird die ORDER BY Klausel verwendet und als Sortierkriterium die Spaltenzahl angegeben.

SELECT staat,land,SUM(akt_umsatz) FROM kunden
WHERE staat = "BRD" GROUP BY staat,land ORDER BY 3

Local SQL besitzt einige Einschränkungen, die beachtet werden müssen:

Die Gruppenspalte muß auch in der SELECT-Anweisung als auszugebende Spalte vorhanden sein. Andere Spalten dürfen nur in Zusammenhang mit einer Aggregatfunktion verwendet werden.

Aufstellung der zulässigen und nicht zulässigen GROUP BY Klauseln:

zulässig

SELECT staat FROM kunden GROUP BY staat

SELECT staat,land FROM kunden GROUP BY staat,land

SELECT staat,SUM(akt_umsatz),AVG(akt_umsatz) FROM kunden GROUP BY staat

nicht zulässig

SELECT staat,kundennr,name FROM kunden GROUP BY staat

HAVING Klausel

In den oberen Beispielen der GROUP BY Klausel haben wir bereits Einschränkungen über die WHERE Klausel durchgeführt. Eine WHERE Klausel darf aber nicht in einer GROUP BY Klausel enthalten sein. Um Einschränkungen in einer GROUP BY Klausel vorzunehmen muß die HAVING Klausel verwendet werden.

In unserem Beispiel schränken wir das Ergebnis auf alle Kunden ein, die sich in Deutschland befinden, unterteilt nach Bundesländer. Eine weitere Einschränkung soll sein, daß nur die Werte angezeigt werden sollen, wo der Umsatz größer 5.000 DM als Summe je Bundesland ist.

SELECT staat,land,SUM(akt_umsatz) FROM kunden
WHERE staat = "BRD" GROUP BY staat,land HAVING SUM(akt_umsatz) > 5000

Die HAVING Klausel sollte nur dann eingesetzt werden, wenn es keine andere Möglichkeit für eine Einschränkung gibt. Der Nachteil bei der HAVING Klausel ist der, daß das Result Set erst aufgebaut wird und dann erst die Einschränkung über HAVING erfolgt.

4.10   Abfragen kombinieren UNION

Mit der UNION Klausel können Sie Abfragen kombinieren. UNION faßt die Ergebnisse aus zwei oder mehr SELECT-Anweisungen zusammen und entfernt dabei Duplikatzeilen.

SELECT kundennr FROM kunden UNION SELECT kundennr FROM Auftrag

Die Verwendung der UNION Klausel ist stark eingeschränkt durch folgende Regeln:

Jede SELECT-Anweisung muß dieselbe Spaltenanzahl enthalten. Die Datentypen der Spalten-namen müssen identisch sein, auch in ihrer Spaltenbreite. Die Namen der Spalten müssen dagegen nicht übereinstimmen.

Die Spaltennamen dürfen nicht vom Typ logisch sein.

Das Ergebnis einer UNION-Abfrage wird nach den Werten aller Spalten sortiert, die in den SELECT-Anweisungen angegeben sind. Sollen die Ergebniswerte nach einer anderen Spalte sortiert werden, kann in der letzten SELECT-Anweisung die ORDER BY Klausel verwendet werden.

SELECT SUM(akt_umsatz) FROM kunden UNION SELECT SUM(re_betrag) FROM auftrag

Das Ergebnis dieser Abfrage sind die beiden Summen aus den Tabellen, die in zwei Zeilen dargestellt werden.

Die UNION Klausel besitzt aber auch eine bessere Performance als eine WHERE Klausel.

SELECT * FROM kunden WHERE staat = BRD
UNION SELECT * FROM kunden WHERE staat = "U.S.A."

Diese SQL-Anweisung wird schneller abgearbeitet als eine vergleichbare WHERE Klausel.

SELECT * FROM kunden WHERE staat = "BRD" OR staat = "U.S.A."

4.11   JOIN Tabellen verknüpfen

Mit JOIN können Abfragen über mehrere Tabellen realisiert werden. Im Kapitel SQL Designer 5.2 Tabellen verknüpfen wurde bereits ausführlich auf die Möglichkeiten und Unterschiede von JOINs eingegangen, so daß eine nochmalige Beschreibung an dieser Stelle eine Wiederholung wäre.

Anhand eines Beispieles wollen wir nochmals kurz auf den Unterschied zwischen einen INNER JOIN und OUTER JOINs eingehen.

INNER JOIN

Bei einer Verknüpfung zwischen zwei Tabellen über einen INNER JOIN erfolgt die Verbindung dieser Tabellen über identische Werte einer gemeinsamen Spalte.

Zeige alle Kunden an, für die Aufträge vorhanden sind.

SELECT K.kundennr,K.name,A.auftragnr FROM kunden K,auftrag A WHERE K.kundennr = A.kundennr
oder
SELECT K.kundennr,K.name,A.auftragnr FROM kunden K
INNER JOIN auftrag A ON (K.kundennr = A.kundennr)

Beide SQL-Anweisungen liefern das gleiche Ergebnis und bilden einen INNER JOIN.

Die erste Variante entspricht dem SQL89-, die zweite Variante dem SQL92- Standard.

Anmerkung:

Bei Anwendung eines INNER JOINs mit dem InterBase SQL-Server ist die 1. Variante in der Performance besser.

Möchten wir jetzt zu den Aufträgen auch die einzelnen Positionen in der Abfrage sehen, würden die SQL-Befehle wie folgt lauten:

SELECT K.kundennr,K.name,A.auftragnr,P.pos FROM kunden K,auftrag A,pos P
WHERE K.kundennr = A.kundennr AND A.auftragnr = P.auftragnr
oder
SELECT K.kundennr,K.name,A.auftragnr,P.pos FROM kunden K
INNER JOIN auftrag A ON (K.kundennr = A.kundennr)
INNER JOIN pos P ON (A.auftragnr = P.auftragnr)

OUTER JOIN

Im Gegensatz zu einem INNER JOIN, wo eine Verbindung nur dann erfüllt ist, wenn durch die WHERE oder INNER JOIN Bedingung gleiche Werte in den Tabellen vorhanden sind, wird eine Verbindung auch dann hergestellt, wenn die Bedingung nicht erfüllt ist.

Beim OUTER JOIN wird zwischen einer Haupttabelle und einer nachgeordneten Tabelle unterschieden. Aus der Haupttabelle werden alle Daten übernommen auch wenn in der nachgeordneten Tabelle nicht das passende Gegenstück vorhanden ist.

In unserem ersten Beispiel mit dem INNER JOIN haben wir die Tabellen kunden und auftrag über die gemeinsame Kunden-Nummer, die in beiden Tabellen vorhanden ist, verbunden.

Das Ergebnis war, daß nur die Kunden angezeigt werden, für die es entsprechende Aufträge gibt in der Tabelle auftrag.

Möchte man beide Tabellen verbinden und als Ergebnis auch die Kunden angezeigt bekommen, wo es keine Aufträge gibt, verwendet man einen OUTER JOIN.

SELECT K.kundennr,K.name,A.auftragnr FROM kunden K
LEFT OUTER JOIN auftrag A ON (K.kundennr = A.kundennr)

LEFT OUTER JOIN bestimmt, welche Tabelle die Haupttabelle ist.

Self-Join

Es besteht die Möglichkeit logische Verknüpfungen innerhalb von Zeilen der gleichen Tabelle vorzunehmen. Diese Verknüpfungen nennt man Self-Joins. Dies erreicht man dadurch, daß man für die gleiche Tabelle zwei verschiedene Alias-Namen verwendet.

Das folgende Beispiel hat zwar keinen Sinn, soll nur die Möglichkeit aufzeigen.

SELECT A.kundennr,B.name FROM kunden A,kunden B WHERE A.kundennr = "1221"

4.12   Datensätze einfügen

Mit dem INSERT Befehl werden neue Datensätze in die Tabelle eingefügt.

Syntax:

INSERT INTO <Tabellenname> [ <Liste Spaltennamen> VALUES <Liste Werte> ] SELECT <Befehl>

Um einen neuen Datensatz in die Tabelle kunden einzufügen lautet der INSERT Befehl:

INSERT INTO kunden (kundennr,name,kontakt,akt_umsatz,liquid)
VALUES ("9999","Mustermann","11.11.1998",123.55,True)

Zu beachten ist, daß Zahlen nicht in Hochkomma eingeschlossen werden und das bei logischen Feldern True oder False ausgeschrieben werden müssen.

Die Values-Werte können auch über Parameter übergeben werden. Hierbei ist bei Feldern vom Typ numerisch mit oder ohne Dezimalstellen folgendes zu beachten:

Bei Parametern vom Typ numersich muß eine Dezimalstelle mit angegeben werden, da sonst eine Fehlermeldung erfolgt. Dies gilt nur bei Parameterübergabe. Wird der Values-Wert per Hand in die SQL-Syntax eingetragen gilt diese Besonderheit nicht.

Beispiel:

nUmsatz = 123.0 cKundennr = "9999" cName = "Mustermann"

INSERT INTO kunden (kundennr,name,akt_umsatz) VALUES (:cKundennr,:cName,:nUmsatz)

Mit dem INSERT Befehl können auch Datensätze von einer Tabelle in die andere kopiert werden.

INSERT INTO kunden SELECT * FROM kunden_alt

Werden statt dem * die Spaltennamen angegeben, so werden nur diese Spalten kopiert. Dabei ist zu beachten, daß die Spaltennamen von beiden Tabellen angegeben werden müssen. Diese müssen auch in der gleichen Reihenfolge sein.

Mit der WHERE Klausel können Bedingungen definiert werden.

INSERT INTO kunden (kundennr,name,kontakt)
SELECT kundennr,name,kontakt FROM kunden_alt WHERE kontakt < "11.11.1990"

Die Spaltennamen müssen in beiden Tabellen nicht immer gleich lauten, aber vom gleichen Typ sein. Die Spaltenlänge darf größer aber nicht kleiner sein.

INSERT INTO kunden (kundennr_neu,name_neu,kontakt_neu)
SELECT kundennr,name,kontakt FROM kunden_alt WHERE kontakt < "11.11.1990"

4.13   Datensätze ändern

Mit dem Befehl UPDATE können vorhandene Daten geändert werden.

Syntax:

UPDATE <Tabellenname> SET <Spaltenname = Wert>,[ <Spaltenname = Wert> ] WHERE <Bedingung>

Das Ändern von Daten kann für alle oder nur für einen bestimmten Datensatz gelten. Wenn nur ein Datensatz geändert werden soll, muß unbedingt über die WHERE Klausel die Bedingung festgelegt werden.

UPDATE kunden SET akt_umsatz = 0

Bei diesem Beispiel werden bei allen Kunden das Feld akt_umsatz auf Null gesetzt.

UPDATE kunden SET akt_umsatz = 10000, strasse = "Kleiner Weg 12" WHERE kundennr = "9999"

Hier wird bei dem Kunden mit der Kunden-Nummer 9999 die Felder akt_umsatz und strasse geändert.

Es besteht die Möglichkeit Updates auch aus Werten durch eine Unterabfrage zu realisieren.

UPDATE P SET city = (SELECT S.city FROM S WHERE S.sno = "S5") WHERE P.color = "Red"

Dieser Update setzt die Stadt für jeden roten Artikel auf den Wert, den die Stadt für Lieferant S5 hat.

4.14   Datensätze löschen

Mit dem Befehl DELETE werden einzelne Datensätze gelöscht.

Syntax:

DELETE FROM <Tabellenname> WHERE <Bedingung>

Wird beim Löschbefehl keine Bedingung eingegeben werden alle Datensätze gelöscht.

DELETE FROM kunden

löscht alle Datensätze.

DELETE FROM kunden WHERE kundennr = "9999"

löscht nur diesen Kunden.

Beim Löschen werden die Eigenschaften von dbf-Tabellen beachtet. Die Datensätze erhalten einen Löschflag und werden mit PACK unwiderruflich aus der Tabelle entfernt.

4.15   Suchen in Memofeldern

Mit LIKE kann nach Texten in Memofeldern gesucht werden. In der WHERE-Klausel wird der Spaltenname für das Memofeld angegeben. Die Suche nach Zeichenketten in Memofeldern ist im Prinzip das Gleiche wie bei der Suche in einer Spalte. Es gibt aber einen wichtigen Unterschied, der unbedingt zu beachten ist. Local SQL unterstützt bei Memofeldern nicht die String-Funktionen LOWER() und UPPER(). Bei der Suche in Memofeldern muß die genaue Schreibweise beachtet werden.

Das Memofeld ist für Local SQL eine Spalte mit einer variablen Länge. Aus diesem Grund würde eine Suche nach einem Begriff ohne das %-Zeichen, für beliebig folgende Zeichen, kein Suchergebnis liefern.

SELECT kundennr,name,notizen FROM kunden WHERE notizen LIKE "%Broschüre%"

Wird nach einem Begriff gesucht, wo die genaue Schreibweise nicht bekannt ist, muß eine OR-Anweisung verwendet werden.

SELECT kundennr,name,notizen FROM kunden
WHERE notizen LIKE "%Broschüre%" OR notizen LIKE "%broschüre%"

4.16   Schlußbetrachtung

Mit der Integration von Local SQL steht Ihnen eine mächtige Abfrage- und Datenmanipula-tions-Sprache zur Verfügung, die auf der einen Seite Ihnen erlaubt, mit einfachen Befehlen im Befehlsfenster Abfragen zu gestalten und Daten zu manipulieren. Auf der anderen Seite ist Local SQL bzw. SQL keine Programmiersprache. Die Visual dBASE OODML ist die Programmiersprache mit der Möglichkeit SQL einzubinden.

In den meisten Fällen ist Local SQL in der Befehlssyntax einfacher und leistungsfähiger als die alte dBASE DML. Auf der anderen Seite nicht immer schneller als die neue OODML.

Obwohl im Befehlsvorrat aus Kompatibilitätsgründen vorhanden, sollten Sie die alte DML von dBASE nicht mehr benutzen.

Ein alter dBASE DML Befehl wie LOCATE ist bedeutend langsamer gegenüber einen Local SQL Befehl. Die neue Methode applyLocate() ist wiederum schneller als ein Local SQL Befehl.

Obwohl jeder Zugriff auf Tabellen in VdB 7 über eine Query erfolgt, die wiederum eine Local SQL Syntax ist, sollten Sie folgendes beachten, wenn Sie eine Query direkt per SQL-Befehl oder aus einer Abfrage mit dem SQL Designer verwenden. Nicht immer sind die Antworttabellen aus einer Local SQL Abfrage beschreibbar. Die Antworttabelle kann auch Read-Only sein. In diesem Fall können Sie keine Daten ändern, löschen oder hinzufügen.

Die Einschränkungen bei Live Querys (Ergebnisdaten) treten auf wenn:

Wenn mehr als zwei Tabellen über Joins verbunden sind.

Wenn in der SELECT-Anweisung DISTINCT verwendet wurde.

Es dürfen keine Aggregatfunktionen in der SELECT Klausel verwendet werden.

Abfragen mit GROUP BY und HAVING Klausel dürfen nicht verwendet werden.

ORDER BY Klausel, die keinen einfachen Index benutzt.

Local SQL und dBASE-Indizes

Local SQL verwendet bei dBASE-Tabellen nur dann die Indizes, wenn es sich um einen einfachen Index für die entsprechende Tabellenspalte handelt. Der entsprechende Index wird verwendet, wenn in dem SQL-Befehl eine ORDER BY- oder WHERE-Klausel enthalten ist.

SELECT * FROM kunden ORDER BY kundennr

Da für die Spalte kundennr ein einfacher Index vorhanden ist, führt Local SQL keine Sor-tierung durch, sondern verwendet den Index. Das Ergebnis ist auch, daß die Antworttabelle beschreibbar ist. Führt ORDER BY eine Sortierung durch, ist die Antworttabelle Read-Only.

Wird bei einer WHERE-Klausel eine Bedingung für einen Spaltennamen definiert, für den es einen einfachen Index gibt, optimiert Local SQL automatisch die Abfrage und verwendet den Index.

SELECT * FROM kunden WHERE kundennr = "4711"

In diesem Fall wird die Tabelle nicht sequentiell gelesen, sondern der Index verwendet, so daß das Abfrageergebnis sofort vorhanden ist.

4.17   Beispiele

Diesem Artikel liegen drei Beispiel-Tabellen bei. Kunden.dbf, Auftrag.dbf und Pos.dbf. Diese drei Tabellen sind Grundlage für alle SQL Befehle in diesem Artikel, so daß die Ergebnisse der SQL Befehle nachvollzogen werden können.

Zum Download der Beispiel-Dateien hier klicken
(Zip-File 69 Kb)


Über den Autor: Michael Rotteck arbeitet seit 1983 mit dBASE und entwickelt hauptsächlich kaufmännische Anwendungen mit Visual dBASE mit lokalen Datenbanken (DBF) und relationalen Datenbanken im Client/Server-Umfeld.

Daneben werden für Visual dBASE Schulungen, Consulting und Support angeboten.

Michael Rotteck
EDV-Beratung
63329 Egelsbach
Tel. / Fax: 06103 / 49181
100277.441@CompuServe.com