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.
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.
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 FROM 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 |
|
oder
|
q = New Query("SELECT * FROM &cTabelle") BROWSE |
|
Für die Erstellung einer SQL-Anweisung für die Komponente Query im Formular Designer kann der Makro-Operator nicht verwendet werden.
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.
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.
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 |
|
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
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.
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.
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.
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" |
|
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.
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 |
|
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." |
|
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" |
|
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" |
|
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.
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.
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%" |
|
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.
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 40 Kb)
Daneben werden für Visual dBASE Schulungen, Consulting und Support angeboten.
Michael Rotteck
EDV-Beratung
63329 Egelsbach
Tel. / Fax: 06103
/ 49181
100277.441_dBulletin_@CompuServe.com (abschaffen “_dBulletin_”).