[ 1 ] [ 2 ] [ 3 ] [ 4 ]

Session D-SQL1

Einführung in SQL unter VFP

Eugen Wirsing
Wizards & Builders GmbH

Ziele

    Überblick über die Möglichkeiten (Sprachumfang, Anwendungsbeispiele)

    Einfache Abfragen selbst formulieren können

    Problembewußtsein entwickeln für die Komplexität der Datenwelt

    Lust zum Ausprobieren  wecken

Einleitung

Mitte der 70er Jahre wurde bei IBM eine „Structured Query Language“ entwickelt .

Seitdem ist dieses „SQL“ Standardsprache für die Benutzung von relationalen Datenbeständen.

Alle namhaften Datenbankhersteller haben inzwischen SQL in ihre Programmiersprachen integriert. Manche Produkte bieten einen „Subset“ des Standards, andere ein „Superset“ also „mehr“ als die anderen. Alle aber weichen irgendwie vom Standard und voneinander ab. Das führt dazu, daß man von Produkt zu Produkt zwar die Grundsätze eines Programms übertragen kann, aber jedesmal sozusagen Dialektanpassungen vornehmen muß.

Eine erste Standardisierung durch die „ANSI“ wurde 1986 durchgeführt, jedoch erst 1987 veröffentlicht, so daß man gelegentlich ANSI-SQL-86 oder ANSI-SQL-87 hört.

Erweiterungen der Sprache wurden als SQL-89 und SQL-92 definiert.

Der nächste Standard sollte SQL3 heißen. Er wird abstrakte Datentypen sowie objektorientierte Konzepte beschreiben.

Die meisten Datenbanken unterstützen heute den Standard SQL-89 mehr oder weniger vollständig. Ebenso geben die meisten an, den sogenannten ‚Entry level“ von SQL-92 zu unterstützen.

Bis auf einige Ausnahmen gilt dies auch weitgehend für VFP 5.0.

Einige prominente Namen der Branche: IBM DB2 (UDB), Informix IDS, Oracle V7, Sybase ASE, Microsoft SQL-Server 6.5 (7.0), Software AG ADABAS D, NCR - Teradata, SNI

Kategorien innerhalb der Sprache

Abfrage und Manipulation (DML)

Welche Informationen stecken in meinen Daten?

Wie kann ich sie auswerten, pflegen, umformen? 

     Select, Update, Insert

Definition von Datenstrukturen (DDL)

Aufbau und Veränderung der Datenstrukturen.

Wie erstelle, verändere, lösche  ich eine Tabelle, eine Datenbank. Wie sorge ich für ‚Konsistenz‘ 

 Create (Alter) Table, Drop Table 
 Create Trigger, Delete Trigger

Ablaufkontrolle und Datensicherheit (DCL)

Ablauforganisation, Transaktionssteuerung 

 Begin Transaction, End Transaction, Commit, Rollback

Embedded SQL

Unter diesem Begriff versteht man die Einbettung der SQL-Sprache in eine proprietäre (prozedurale) Programmiersprache. Visual FoxPro ‚enthält‘ SQL, Oracle ‚spricht‘ PL/SQL bzw. ESQL etc.

Die Abfragesprache und ihre Syntax

Jede Abfrage enthält mindestens

  • eine Beschreibung der Ergebnistabelle und
  • die Quelle der Daten.

Das Schlüsselwort, mit dem jede Abfrage beginnt, heißt „select“.

Danach folgt die Beschreibung der Ergebnistabelle in Form einer Feldliste

Auf das Schlüsselwort „from“ folgt die Angabe der Datenquelle. Diese besteht immer aus einer oder mehreren Tabellen.

Die Feldliste kann aus Spaltennamen der Quelltabelle(n) und Ausdrücken bestehen.

Ausdrücke dürfen Konstanten, Variablen und Funktionen enthalten

Für „alle Felder der Quelltabelle“ schreibt man „*“.

Alle weiteren Angaben sind optional.

Die Ergebnisspalten können mit neuen Namen bezeichnet werden „as <Name>“.

Die Ergebnismenge kann

  • eingeschränkt „where“, “distinct“, „top n [%]“
  • sortiert „order by [ascending|descending}“ und
  • gruppiert werden „group by“

Gruppenergebnisse können gefiltert werden „having“

Mehrere Abfragen können durch „union [all]“ miteinander verknüpft werden, so daß sie eine gemeinsame Ergebnismenge erzeugen.

Die Ergebnismenge kann (bei VFP) in eine

  •          neue Tabelle (.DBF), „into table“
  •          eine temporäre Datei „into cursor“
  •          ein Array, „into array“oder in
  •          eine Textdatei „to <dateiname>[additive]“
  •          auf den Systemdrucker „to printer“ oder auf
  •          den Hintergrund des aktiven Fensters „to screen“ geschrieben werden.
  •          Ohne Zielangabe zeigt VFP das Ergebnis der Abfrage als „Browse“.

Abfragen aus einer Tabelle

Feldliste, Ausdrücke

Datenquelle = Tabelle Kunden:

Anrede Vorname Name Strasse Plz Ort Ust_ID Rabatt

Herr

Erwin

Schütte

Haingasse

61383

Hintertaunus

0

Frau

Anna

Heinrichs

Schnellstraße 310

10120

Dollhausen

4711

10

...

Feldliste enthält Spaltennamen der Quelldatei(en): 

 Select Anrede, Vorname, Name, Plz, Strasse, Ort from Kunden:

Anrede Vorname Name Strasse Plz Ort

Herr

Erwin

Schütte

Haingasse

61383

Hintertaunus

Frau

Anna

Heinrichs

Schnellstraße 310

10120

Dollhausen

Feldliste enthält Ausdruck: 

 Select Anrede, Vorname, Name, Strasse, Plz, Ort,  subst(Plz,1,2) as Versandgeb from Kunden

Anrede Vorname Name Strasse Plz Ort Versandgeb

Herr

Erwin

Schütte

Haingasse

61383

Hintertaunus

61

Frau

Anna

Heinrichs

Schnellstraße 310

10120

Dollhausen

10

„DISTINCT“

Mit diesem Zusatz wird SQL alle gleichartigen Treffer nur einmal in das Ergebnis aufnehmen:

Welche Postleitzahlen kommen in der Kundendatei vor: 

 Select Distinct Plz from Kunden

Plz

61383

10120

 

Bedingungsabhängige Inhalte 

 IIF(<Bedingung>,<Wert 1>, <(sonst) Wert 2>) as <Spaltenname>    
   (Kreuztabellen) 
 Select *, IIF(Anrede =“Frau“, „Parfüm“,“Rasierwasser“)  ; 
         as w_geschenk from Kunden

Restriktionen: Where 

 Select Anrede, Vorname, Name, Plz, Strasse, Ort,  subst(Plz,1,2) as Versandgeb 
 from Kunden 
 where subst(Plz,1,2) = „61“

Anrede Vorname Name Strasse Plz Ort Versandgeb

Herr

Erwin

Schütte

Haingasse

61383

Hintertaunus

61

Sortieren: Order by 

 Select Anrede, Vorname, Name, Plz, Strasse, Ort,  subst(Plz,1,2) as Versandgeb 
 from Kunden 
 order by Versandgeb

Anrede Vorname Name Strasse Plz Ort Versandgeb

Frau

Anna

Heinrichs

Schnellstraße 310

10120

Dollhausen

10

Herr

Erwin

Schütte

Haingasse

61383

Hintertaunus

61

Aggregationen: Sum(), Count(), Avg(), Max(), Min()

Tabelle best_det (Detailzeilen der Bestellungen)

BEST_ZEILE BEST_ID ART_ID EINH_PREIS MENGE

1

10000

17

27,0000

4,000

1

10001

25

9,8000

42,000

2

10001

40

12,8000

36,000

3

10001

59

38,5000

24,000

4

10001

64

23,0000

12,000

1

10002

31

8,0000

15,000

2

10002

39

12,6000

19,000

3

10002

71

15,0000

15,000

1

10003

18

43,7000

12,000

1

10004

29

86,0000

35,000

2

10004

63

30,7000

6,000

Die folgenden Abfragen erzeugen jeweils genau ein Ergebnis.

Wert aller Bestellungen: 

 Select SUM(einh_preis * menge) from best_det

Die teuerste Bestellungsposition: 

 Select MAX(einh_preis * menge) from best_det

Der Durchschnittswert aller Bestellpositionen: 

 Select AVG(einh_preis * menge) from best_det

Gruppierung: Group by

Will man Werte bestimmter Gruppen aggregieren, so verwendet man den Befehl GROUP BY mit der Angabe des Gruppenbegriffs.

Wert aller Bestellungen je Bestellung: 

 Select SUM(einh_preis * menge) from best_det ; 
  Group by best_id

Die teuerste Bestellungsposition jeder Bestellung: 

 Select MAX(einh_preis * menge) from best_det; 
  Group by best_id

Der Durchschnittswert aller Positionen je Bestellung: 

 Select AVG(einh_preis * menge) from best_det; 
  Group by best_id

Die teuerste Bestellung? – Verschachtelung von Aggregationen ist leider in VFP nicht möglich: 

 Select MAX(SUM(einh_preis * menge)) from best_det ; 
  Group by best_id

Selektives Gruppieren: Having

Mit HAVING wird eine Restriktion auf die Gruppierungsergebnisse ausgeübt:

Wert aller Bestellungen je Bestellung, aber nur, wenn der Bestellwert 10 000 übersteigt : 

 Select best_id, SUM(einh_preis * menge) from best_det ; 
  Group by best_id ; 
  Having SUM(einh_preis * menge) > 10000

Vereinigung (Union)

Zwei oder mehr Teilabfragen werden zu einer Ergebnistabelle zusammengeführt.

Achtung:

    Die erste Abfrage bestimmt die Spaltenbreite des Ergebnisses.

    Die erste Abfrage bestimmt die Reihenfolge der Ergebnisspalten

    Die erste Abfrage bestimmt die Datentypen der Ergebnisspalten

    Die letzte Abfage bestimmt die Spaltennamen. 

 Select best_id, SUM(einh_preis * menge), ; 
  10.00 as rabatt from best_det ; 
  Group by best_id ; 
  Having SUM(einh_preis * menge) > 10000 ; 
    Union All ; 
  Select best_id, SUM(einh_preis * menge), ; 
  05.00 as rabatt from best_det ; 
  Group by best_id ; 
  Having SUM(einh_preis * menge) <= 10000

Verknüpfungen (Join)

Wenn in der ‚FROM‘-Klausel mehr als eine Tabelle genannt wird, verknüpft SQL im Ergebnis die genannten Tabellen miteinander.

Dazu werden die zu verknüpfenden Tabellen Satz für Satz anhand der ‚Join‘-Bedingungen miteinander verglichen und bei Übereinstimmung in das Ergebnis übernommen.

(Die Verknüpfung kann auch als Rstriktion mit der ‚WHERE‘-Klausel formuliert werden.)

 

  Preise07   Preise08
ART_ID STK_PREIS ART_ID STK_PREIS

1

18,0000

2

22,0000

2

19,0000

3

11,0000

3

10,0000

4

25,0000

5

21,3500

5

17,0000

6

25,0000

6

25,0000

7

30,0000

8

40,0000

8

40,0000

9

110,0000

9

97,0000

10

35,0000

10

31,0000

11

19,0000

11

21,0000

12

39,0000

12

38,0000

13

7,0000

13

6,0000

14

22,0000

14

23,2500

15

14,5000

15

15,5000

16

19,0000

16

17,4500

17

44,0000

17

39,0000

18

70,0000

18

62,5000

19

8,0000

20

81,0000

21

9,0000

21

10,0000

22

25,0000

22

21,0000

23

8,0000

23

9,0000

24

4,5000

 

25

14,0000

[ 1 ] [ 2 ] [ 3 ] [ 4 ]