Wilhelm Moser SQL - Structured Query Language Herzlich ! Herzlich willkommen ! 1

Wilhelm Moser

SQL - Structured Query Language

Herzlich Herzlich willkommen ! !1

Wilhelm Moser SQL - Structured Query Language

Was werden Sie nach dem Kurs können ?

Datenbanken abfragen können

Komplizierte Auswahlabfragen erstellen können

Datenbankschnittstellen verstehen können

Datenbanken verwalten können

RDBMS - Systeme verstehen können

Die Arbeitsweise von Datenbankservern in den Grundzügen verstehen können

2


Kursinhalte

Allgemeines über DatenbanksystemeRelationale DatenbanksystemeDie Übungsdatenbank

SQL - Structured Query Language die Standard Abfragesprache

DML - Data Manipulation Language

SELECT Daten abfragenINSERT Daten einfügenUPDATE Daten verändernDELETE Daten löschen

DDL - Data Definition Language

3

DCL - Data Control Language


SQL - Structured Query Language - Einleitung I

SQL wurde vom ANSI (American National Standard Institute) genormt und als Standarddatenbanksprache empfohlen.

Leider sind dennoch verschiedene „Dialekte“ bei den Datenbanken vorhanden.

Vorteile des „Standards“

Praktisch alle Datenbanken können mit SQL abgefragt werden

Eine einheitliche Sprache ermöglich programmübergreifenden Datenaustausch

Applikationen sind nicht an eine spezielle Datenbank gebunden

Die Verbindung zur Datenbank ermöglichen spezielle Programme oder das Betriebssystem (ODBC, DAO, ADO, RDO MS-Jet Engine.....)

4


SQL - Structured Query Language - Einleitung II

SQL ist im Gegensatz zu anderen Datenbanksprachen nicht Recordset sondern mengenorientiert. Daher bezieht sich eine Abfrage NIE auf einzelne Datensätze sondern auf eine Menge von Datensätzen.

In einem Programm (z.B. MS-QUERY) oder einer Programmiersprache wird ein SQL-Befehl formuliert. Die Richtigkeit der Syntax wird überprüft und als Befehlsfolge an das RDBMS übergeben, das die Abfrage optimiert und ausführt.

Die Ergebnisse werden an den Aufrufer übergeben.

Die Aufgaben von SQL

Daten manipulieren DML Data Manipulation LanguageDatenstrukturen definieren DDL Data Definition Language

5

Datenfluß kontrollieren DCL Data Control Language


DML - Data Manipulation Language - Übersicht

Ein Teil der SQL wird als DML bezeichnet. Die Funktionen zur Daten-manipulation sind:

ErfassenÄndernLöschenAbfragen

SQL Sprachelemente sind nicht Case-Sensitive. Das gilt allerdings nicht für Strings.

6

Bei Abfragen aus Programmiersprachen kann die Art der Darstellung des Recordsets gesetzt werden: OpenTable, OpenDynaset, OpenSnapshot

(OpenTableDef)


SQL-DML - Statements - SELECT & WHERE

Die Hauptaufgabe einer Datenbank ist die Bereitstellung von erfaßten Daten nach bestimmten Kriterien.

Die einfachste Form ist die Abfrage aller vorhandenen Datensätze und Felder einer Tabelle:

SELECT * FROM KUNDEN

Natürlich können wir auch spezielle Felder anstelle des Asteriks angeben:

SELECT NAME, GEBURTSDATUM FROM KUNDEN

7

Durch Definition von Bedingungen ist die gezielte Auswahl möglich:

SELECT NAME, GEBURTSDATUM FROM KUNDEN WHERE GEBURTSDATUM > "01.01.1940"


SQL-DML - Operanden & Operatoren Listing

Die OperandenKonstante 1,‘HERMANN‘.....Columns kunden.name....Funktionen min(kunden.nr), 4+5, now.... (Achtung Datenbankspezifisch)Konstantenliste (´JA´,´NEIN´,´UNBEKANNT´)....

Operatoren (Vergleich und logische)= Gleich<> Ungleich<= Kleiner oder Gleich>= Größer oder Gleichin Operand innerhalb einer Listenot in Operand nicht innerhalb einer Listebetween and Operand zwischen 2 Wertennot between and Operand außerhalb der 2 Wertelike Operand so ähnlich wie: Vergleich mit Wildcardsnot like Operand nicht ähnlich wieis null Operand ist unbestimmt (NULL)is not null Operand ist bestimmt

and logisches undor logisches oder

8


SQL-DML - Operanden & Operatoren

Logisches and

SELECT NAME, ADRESSE FROM KUNDEN WHERE L_CODE = 'AUT' and Geburtsdatum >= '01.01.1940'

9

Logisches 0r

SELECT BEZ, L_CODE, SPRACHE FROM ReisegebieteWHERE SPRACHE='DEUTSCH' Or SPRACHE='ENGLISCH'

Operand in

SELECT BEZ, L_CODE, SPRACHE FROM ReisegebieteWHERE SPRACHE in ('DEUTSCH','ENGLISCH')

Operand between

SELECT Name, Adresse, Umsatz from Kunden WHERE Umsatz between 4000 and 9999


SQL-DML - Funktionen & Wildcards

Operand Like mit Wildcards

SELECT * from Reisegebiete where Bez like 'M*I?'

10

Operand IS NULL

SELECT NAME, ADRESSE FROM KUNDENWHERE TELEFON IS NULL

Funktion HEUTE mit IS NULL

SELECT NAME, TELEFON, Format(Now(),"dd/mm/yy") AS HEUTE FROM KUNDENWHERE TELEFON Is Null


SQL-DML - Inner Joins

Wir wollen die bevorzugten Reisegebiete von Andrea wissen. Derzeit müßten wir wie folgt vorgehen:

In [KUNDEN] die Nummer von Andrea suchenIn [KUNDEN_GEBIETE] die Nummern aller Reisegebiete von Andrea suchenIn [REISE_GEBIETE] die Nummern der Reisegebiete aufsuchen.

11

Now lets JOIN Tables

SELECT REISEGEBIETE.BEZ FROM KUNDEN, KUNDEN_GEBIETE, REISEGEBIETE WHERE KUNDEN.NAME = 'ANDREA' AND KUNDEN.NR = KUNDEN_GEBIETE.K_NRAND KUNDEN_GEBIETE.G_NR = REISEGEBIETE.NR

Durch diese Tabellenverknüpfung wird eine temporäre, relationale Verbindung hergestellt.


SQL-DML - Alias Namen

Wozu ALIAS Namen ?

12

1. Bei Spalten schönere Feldbezeichnung2. Bei Tabellen Verkürzung des Tabellennamens

Beispiel:

SELECT KUNDEN.NAME AS Vorname, INTERESSEN.BEZ as InteressenFROM (KUNDEN

INNER JOIN KUNDEN_INTERESSEN ON KUNDEN.NR =KUNDEN_INTERESSEN.K_NR

)INNER JOIN INTERESSEN ON KUNDEN_INTERESSEN.I_NR =

INTERESSEN.NR


SQL-DML - Self Join

Versuchen wir, alle Kunden, die zusammen wohnen, auszufiltern. In diesem Fall müssen wir die gleiche Tabelle 2 x in unsere Abfrage einbeziehen.

13

Beispiel:

SELECT K1.NAME, K2.NAME FROM KUNDEN K1, KUNDEN K2 WHERE K1.ADRESSE = K2.ADRESSE AND K1.NR <> K2.NR

In Access geht’s auch mit dem Assistenten mit Unterabfragen: (dazu später)

SELECT DISTINCTROW KUNDEN.ADRESSE, KUNDEN.NAMEFROM KUNDENWHERE (((KUNDEN.ADRESSE) In (SELECT [ADRESSE] FROM [KUNDEN] As Tmp GROUP BY [ADRESSE] HAVING Count(*)>1 )))ORDER BY KUNDEN.ADRESSE;


SQL-DML - Outer Join

Die Idee des Joins ist nicht die wahllose Verbindung von Tabellen, sondern die Recordset - spezifische. Wenn wir für alle Kunden das LAND angeben wollen, müssen wir KUNDEN und LÄNDER verbinden. In dem Fall wird Claudia nicht mehr angezeigt, da dort ein unbekanntes Land steht. (Was mit referentieller Integrität ja nicht passieren dürfte)

14

Beispiel ohne Outer Join und ohne Claudia:

SELECT K.NAME,L.BEZ FROM KUNDEN K, LÄNDER L WHERE K.L_CODE=L.Code

Beispiel mit Outer Join und Claudia:

SELECT K.NAME,L.BEZ FROM KUNDEN K, LÄNDER L WHERE K.L_CODE=L.Code(+)

Soweit die Theorie. Die Syntax mag bei UNIX-INFORMIX Systemen aufgehen, der Dialekt von ACCESS läßt das jedenfalls nicht zu. Jedoch gibt’s dort sogenannte Left Joins & Right Joins


SQL-DML - Subqueries - Unterabfragen

Ronnie beschließt eine Reise zu unternehmen und möchte in ein Land reisen, das auch Andrea besucht(e).

15

Beispiel einer Unterabfrage:

SELECT G.BEZFROM REISEGEBIETE AS G, GEBIETE_INTERESSEN AS GIWHERE GI.G_NR=G.NR AND GI.I_NR In (SELECT KI1.I_NR FROM KUNDEN_INTERESSEN KI1, KUNDEN K1 WHERE K1.NAME='ANDREA' AND KI1.K_NR=K1.NR );

Natürlich kann man solche „Monster“ auch noch mit and Bedingungen verknüpfen und sogar abhängige Subqueries erstellen.


SQL-DML - Correlated Subqueries

Versuchen wir das Beispiel der zusammen wohnenden RONNIE & JUDITH (Self Join) mit einer Unterabfrage zu lösen

16

Beispiel einer abhängigen Unterabfrage:

SELECT K1.NAME FROM KUNDEN K1 WHERE EXISTS (SELECT K2.NAME

FROM KUNDEN K2 WHERE K1.ADRESSE = K2.ADRESSE AND K1.NR <> K2.NR );

Hier und nur im Zusammenhang mit Subqueries wird der Operator EXISTS verwendet, um Fehler der Subquery auszuschalten.Exists ist dann wahr, wenn die Subquery zumindest eine Zeile zurückliefert.


SQL-DML - UNION-Klausel - Mengenvereinigung

Mit UNION lassen sich beliebige mit SELECT erzeugte Mengen vereinigen. Die Spalten der einzelnen SELECTS müssen jedoch den selben DatenTyp besitzen.

17

Beispiel - eine Liste aller Interessen und aller Reisegebiete:

SELECT INTERESSEN.BEZ FROM INTERESSEN UNION SELECT REISEGEBIETE.BEZ FROM REISEGEBIETE;

Meiner Meinung nach ein gutes Mittel, um heillose Verwirrung in an sich strukturierte „Logistik“ zu bringen. Diese beiden Datenarten haben in einer Spalte nichts verloren.


SQL-DML - ORDER BY & GROUP BY

So, nun wird’s Easy - Order by läßt uns Daten sortieren

18

Wir können praktisch nach jeder Abfrage das Statement

...ORDER BY NAME ASC, ADRESSE DESC

anbringen, wobei ASCending und DESCending für auf- oder absteigende Sortierung eingesetzt wird.

GRUPPIERUNG, Das Zusammenfassen von Daten in Recordsets.Welche Herkunftsländer bringen welchen Umsatz. (Gruppierung der Kunden nach Herkunftsländern.

SELECT KUNDEN.L_CODE, AVG(RB.KOSTEN) as A, SUM(RB.KOSTEN) as S

FROM KUNDEN, REISE_BUCHUNG RB WHERE KUNDEN.NR=RB.K_NR GROUP BY KUNDEN.L_CODE;


SQL-DML - DISTINCT Statement

Durch das Statement DISTINCT(ROW) läßt sich die Ausgabe doppelter Zeilen vermeiden:

19

SELECT L_CODE FROM KUNDEN; SELECT DISTINCT L_CODE FROM KUNDEN;

liefert:

AUT AUTAUT DEUDEU ENGAUTENG


SQL-DML - INSERT INTO - Anfügeabfrage

Insert into ermöglicht dem Programmierer von 4GL‘s die Erfassung von Daten in der Datenbank. Meist ist das nicht nötig, da praktisch alle Programmiersprachen über Recordsets und rs.add / rs.update diese Funktionen automatisch bereitstellen und die Arbeit mit Recordsets einfacher und bekannter ist.

20

INSERT INTO KUNDEN(Name,Adresse,L_Code,Telefon,Geburtsdatum, Umsatz)VALUES('RONNY','Palffygasse 8','AUT','111111','25.09.1966', 10000);

ACHTUNG: In diesem Fall darf das Feld NR, das einen Autowert enthält, nicht angegeben werden !!!!

Funktioniert mit MSQRY32.EXE, nicht aber direkt mit QBE-Access, erst nach erneutem Aufruf der gespeicherten Abfrage.Jede Kombination (Subqueries) ist möglich.


SQL-DML - UPDATE - Aktualisierungsabfrage

Update ermöglicht dem Programmierer von 4GL‘s die Ergänzung von Daten in der Datenbank. Meist ist das nicht nötig, da praktisch alle Programmiersprachen über Recordsets und rs.edit / rs.update diese Funktionen automatisch bereitstellen und die Arbeit mit Recordsets einfacher und bekannter ist.

21

UPDATE KUNDENSET ADRESSE = 'Hauptstraße 125', TELEFON = '1234567'WHERE KUNDEN.NAME = 'MARKUS'

ACHTUNG: In diesem Fall darf das Feld NR, das einen Autowert enthält, nicht angegeben werden !!!!

natürlich ist auch hier jede Kombination (Subqueries) möglich.


SQL-DML - DELETE - Löschabfrage

Update ermöglicht dem Programmierer von 4GL‘s die Ergänzung von Daten in der Datenbank. Meist ist das nicht nötig, da praktisch alle Programmiersprachen über Recordsets und rs.edit / rs.update diese Funktionen automatisch bereitstellen und die Arbeit mit Recordsets einfacher und bekannter ist.

22

DELETE FROM KUNDEN WHERE NR = 3;oderDELETE FROM KUNDEN WHERE NAME = 'MARKUS';

ACHTUNG: In diesem Fall darf das Feld NR, das einen Autowert enthält, angegeben werden !!!!

natürlich ist auch hier jede Kombination (Subqueries) möglich.


SQL-DML - Zusammenfassung

Das Thema ist so umfangreich, daß nur die ständige Beschäftigung die notwendige Routine mit sich bringen kann. Fallweise Tätigkeiten sind eher mühsam und bedürfen des oftmaligen Nachschlagens in der Syntax der jeweiligen Datenbank, die mit SQL-DDL angesprochen wird

Hier ein Leitfaden für die Problembehandlung:

1. Zuerst die Information suchen, die ausgegeben werden soll.

2. Zugehörige Tabellen und Spalten suchen.

3. Verbindung der Tabellen (graphisch) darstellen.

4. Gruppierungen formulieren

5. Suchordnung erstellen

23

Wenn irgend möglich, lassen Sie doch die QBE-Umgebung von ACCESS die Abfragen für Sie formulieren und konvertieren Sie diese dann in Ihre Programmierumgebung oder Ihren Abfragegenerator. ( Die Abfrageassistenten von ACCESS sind wirklich gut)


SQL-DDL - Data Definition Language

24

Data Definition Language ist das Tool zum Erstellen, Löschen und Ändern von Tabellen, Indizes und Ansichten (Views).

Meist werden diese Änderungen NICHT durch den Programmierer, sondern den Datenbankadministrator am Server (SQL, ORACLE, INFORMIX....) vorgenommen.

Aus gutem Grund. Sollen doch die Änderungen konstistent und daher möglichst zentral erfolgen.

Wir werden nur die minimalen Möglichkeiten behandeln, da jede Datenbank andere Dialekte und Möglichkeiten anbietet.


SQL-DDL - Tabellen - Allgemeines & Schlüssel

25

Tabellen besitzen Spalten und Zeilen.Die Spalten müssen einem bestimmten Datentyp zugeordnet sein.Eine Tabelle kann einen Primärschlüsselund mehrere Fremdschlüssel haben.Dies dient zur Konsistenzsicherungin der Datenbank und ist oft festerBestandteil der Tabellen-definition.

Eine Tabelle kann mehrere Fremdschlüssel haben.

Fremdschlüssel können NULL Werte enthalten.

Fremdschlüssel gelten als NULL wenn zumindest eine Spalte des Schlüssels NULL ist.

Eindeutige Schlüssel (Unique Keys) können mehrfach vorhanden sein.


SQL-DDL - Tabellen - CREATE TABLE

26

Nachdem man alle Tabellenstrukturen mit dem ERM oder eher RM konzipiert hat (Auf dem Papier), legt man die Tabellen physisch an.

Die On Delete Bedingung bewirkt das Löschen der entsprechenden Spalten dieser Tabelle, wenn dazugehörige Recordsets der Muttertabelle gelöscht werden.

Diese semantischen Restriktionen erzielen die Konsistenz der Datenbank und es wird verhindert, daß in der Tabelle KUNDEN_INTERESSEN Kundenummern vorhanden sind, die in der Tabelle KUNDEN nicht mehr existieren.

Beispiel: (Nur bei Server-Datenbanken nicht bei Access möglich - zu komplex)

Die Tabelle KUNDEN_INTERESSEN hat einen Primary Key und 2 Foreign Keys

CREATE TABLE KUNDEN_INTERESSEN( K_NR NUMBER NOT NULL, I_NR NUMBER NOT NULL, PRIMARY KEY (K_NR,I_NR), FOREIGN KEY KEY_KUNDEN (K_NR) REFERENCES KUNDEN ON DELETE RESTRICT, FOREIGN KEY KEY_INTERESSEN (I_NR) REFERENCES INTERESSEN ON DELETE RESTRICT);

AccessSELECT [K_NR] AS A1, [I_NR] AS A2 INTO KUNDEN_INTERESSEN GROUP BY [K_NR], [I_NR];


SQL-DDL - Tabellen - ALTER & DROP TABLE

27

Also, nachdem wir soweit gekommen sind ist das folgende ja wohl ein Klacks:

ADD Spalten hinzufügenALTER TABLE KUNDEN ADD FAX CHAR(25) NOT NULL;

DROP Spalten / Tabellen löschenALTER TABLE KUNDEN DROP GEBURTSDATUM;DROP TABLE KUNDEN;

RENAME Spalten umbenennenALTER TABLE KUNDEN RENAME ADRESSE STRASSE;(nicht in Access)

MODIFY Spalten ändernALTER TABLE KUNDEN MODIFY NAME CHAR(50) NOT NULL; (nicht in Access)


SQL-DDL - Indizes - Allgemeines

28

Stellen wir uns einen Index wie ein Telefonregister vor: A-Z ist unser INDEX.Um leichteren Zugriff zu haben müssen wir bei der Tabellenerstellung INDIZES angeben. Nach der Erstellung übernimmt die Verwaltung der Optimizer des RDBMS.

Der Datenbankadministrator muß allerdings für den richtigen Einsatz der INDIZES bei einem Projekt sorgen.

Regeln bei der Erstellung von INDIZES:

1. Pro Tabelle zumindest ein INDEX.

2. Pro Primary Key sollte ein Unique Index generiert werden.

3. Pro Foreign Key sollte ein Index generiert werden.

4. Oft gelesene Tabellen = viele Indizes.

5. Oft geschriebenen Tabellen = wenige Indizes.


SQL-DDL - Indizes - Create & Drop

29

Auch bei Create Index kann man bei der Definition angeben, ob der Index aufsteigend oder absteigend sortiert werden soll. (Default aufsteigend)

UNIQUE INDEX bedeutet, daß die Indexspalten eindeutig sein müssen.Der Name des INDEX muß in der Datenbank eindeutig sein.

CREATE UNIQUE INDEX KUNDEN_I1 ON KUNDEN (NAME);

CREATE INDEX KUNDEN_I2ON KUNDEN

(L_Code, Geburtsdatum);

DROP INDEX KUNDEN_I1 ON KUNDEN;


SQL-DDL - Views - Allgemeines ( Server based )

30

Mit Views können logische Ansichtsfenster „über“ eine Tabelle gelegt werden.

Der Anwender erhält verschiedene Sichtweisen der Tabelle.

Zugriffsschutz durch „Teilansicht“ der Tabelle

erlaubt eine vereinfachte Schreibweise für komplizierte Tabellenverknüpfungen

Views können wie Tabellen verwendet werden, speichern aber keine Daten.

EIGENSCHAFTEN von VIEW‘s:

referenziert Daten - Änderung der Tabelle = Änderung des View‘s

Die Daten einer View kann man nur ändern, wenn sie einer Tabelle zugeordnet sind, sonst nur LESEN.

Verbraucht minimalen Speicherplatz.


SQL-DDL - Views - Create & Drop View ( Server based )

31

View‘S entsprechen bei der dynamischen Frontendverwaltung von Access dem QBE-Bereich zur Erstellung von Recordsets vom Typ Dynaset oder Snapshot.

Durch die umfassenden Möglichkeiten von Access ist die VIEW in der ANSI-Standard Form in Access nicht vorgesehen.

Hier zwei Beispiele für Server basierte View‘s in create & drop

CREATE VIEW KUNDENINT ( KUNDEN, INTERESSEN) ASSELECT K.NAME, I.BEZFROMKUNDEN K, KUNDEN_INTERESSEN KI, INTERESSEN IWHEREK.NR = KI.K_NRAND KI.I_NR = I.NR;

DROP VIEW KUNDENINT;


SQL-DCL - Data Control Language

32

Wohl ein wichtiger Bereich für Transaktionen und Privilegien die im ACCESS Bereich die MS-Jet Engine oder ODBC zur Verfügung stellt.

Transaktionen

Befehlsfolgen werden in logische Bereiche (Transaktionen) zusammengefaßt.

Eine Transaktion beginnt mit dem SQL-Befehl und endet mit COMMIT oder ROLLBACK, dann beginnt die nächste Transaktion.

Auf simpel: üblicherweise werden Recordsets nach Bearbeitung en Block gespeichert, oder bei Fehlern überhaupt nicht (Stromausfall).

Gerade dieser Bereich hat sich in den letzten Monaten durch DAO, ODBC, RDO, ADO und MS-Jet Engine so verändert, daß Standards kaum mehr zu erkennen sind.

Die Jet-Engine verwendet z.B.: die Syntax BeginTrans & CommitTrans mit anderen Parametern bzw. Funktionsdefinitionen.


SQL-DCL - COMMIT & ROLLBACK

33

Hier also ein Beispiel für Server based commit:

DELETE FROM KUNDEN WHERE NR = 3;DELETE FROM KUNDEN_GEBIETE WHERE K_NR = 3;DELETE FROM KUNDEN_INTERESSEN WHERE K_NR = 3;COMMIT

Das bedeutet, daß erst bei erfolgreichem commit die Transaktion abgeschlossen wird.

Nun ja, ROLLBACK (zurückrollen) heißt dann wohl alles bis zum letzten commit aufrollen und rückgängig machen.


SQL-DCL - Summary

34

Beispiel für die Data Control Language sind die Fähigkeiten der MS-JET Engine (ODBC - mit dem man alle Datenbanken ansprechen kann funktioniert auf VB, ACCESS oder C++ Ebene ähnlich).

Das DB Engine-Objekt enthält die folgendenAuflistungen, Methoden und Eigenschaften.

Auflistungen

ErrorsPropertiesWorkspaces (Voreinstellung)

Eigenschaften

DefaultPasswordDefaultTypeDefaultUserIniPath LoginTimeoutSystemDB Version

Methoden

BeginTransCommitTransCompactDatabase CreateDatabase CreateWorkspace Idle OpenConnection OpenDatabaseRegisterDatabaseRepairDatabase RollbackSetOption

nur in MS Jet-Arbeitsbereichen nur in ODBCDirect-Arbeitsbereichen



35

aber das können Sie ja bereits...

Sie waren ein Publikum

Danke schön,bezauberndes

auf wiedersehen beiweiteren interessanten Kursen...



36

P A U S E !P A U S E ! und nach der Pause ?

noch viel interessantere Dinge .....

Documents

Wilhelm Moser SQL - Structured Query Language Herzlich ! Herzlich willkommen ! 1