SQL - Structured Query Language

SQL - Structured Query Language
Herzlich willkommen !
Wilhelm Moser
1
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 des SQL-Server 6.5 in den Grundzügen
verstehen können
2
SQL - Structured Query Language
Wilhelm Moser
Kursinhalte
Allgemeines über Datenbanksysteme
Relationale Datenbanksysteme
Die Übungsdatenbank
SQL - Structured Query Language die Standard Abfragesprache
DML - Data Manipulation Language
SELECT
INSERT
UPDATE
DELETE
Daten abfragen
Daten einfügen
Daten verändern
Daten löschen
DDL - Data Definition Language
DCL - Data Control Language
3
SQL - Structured Query Language
Wilhelm Moser
Datenbanksysteme - allgemein
SQL (Structured Query Language) wurde von IBM Mitte der 70er-Jahre als Bestandteil eines
relationalen Datenbanksystems (R) entwickelt.
SQL ist eine Datenbanksprache keine Programmiersprache, sondern eine Schnittstelle
zwischen dem Datenbankbenutzer und einer Datenbank.
Relationale Datenbank Managment Systeme (RDBMS) stellen heute meist Standard SQL
Funktionalität zur Verfügung. INFORMIX, DB2, ORACLE, dBase, SQL-Server, OASIS........
RDBMS wurde um 1977 von L. Ellison (IBM) in der Programmiersprache C entwickelt. ORACLE
(1979) und SQL/DS (1982) waren die ersten Datenbanksysteme, die ein RDBMS und SQL
verwendeten. SQL wurde vom ANSI (American National Standard Institute) genormt und als
Datenbanksprache für relationale Datenbanken empfohlen.
SQL ist eine Sprache der 4. Generation (4GL). Sie beschreibt die Eigenschaften der
gewünschten Daten (also WAS die Daten sind) und nicht WIE man zu den Daten gelangt.
SQL ist mengenorientiert. Das bedeutet, daß bei jeder Datenmanipulation immer Sammlungen
von Datensätzen und nicht einzelne Datensätze angesprochen werden. (Selbst wenn das
Resultat nur ein Datensatz ist.)
4
SQL - Structured Query Language
Wilhelm Moser
Relationale Datenbanksysteme - Beziehungskiste
Daten können durch
„Verknüpfung“ von Tabellen
einfach in andere Tabellen
eingebunden, auf Richtigkeit
überprüft und verwaltet
werden.
Daten kommen nicht
doppelt in der Datenbank vor.
Durch Verweise auf andere Tabellen können sehr schnell gleichartige
Datensätze „gefiltert“ werden.
Mit „referentieller Integrität“ erreicht man Datenkontinuität. (Daten können
nur verändert oder gelöscht werden, wenn die Referenz-Datensätze
ebenfalls geändert werden können)
5
SQL - Structured Query Language
Wilhelm Moser
Datendarstellung - von Feldern & Sätzen
strukturierte Daten (oder wie finde ich was?)
Aufbauend auf die
binäre Speicherung
müssen die Daten
logisch strukturiert
werden:
Datenfeld FIRMA
Nimmt man alle
Datenfelder FIRMA
erhält man eine Liste
mit allen Firmennamen
Datensatz "Index"
Ein "Datensatz" ist die Menge von "Datenfeld"
6
SQL - Structured Query Language
Nimmt man einen
Datensatz "Index"
erhält man eine Zeile
mit allen Felddaten
einer Firma od Person.
Wilhelm Moser
Tabellen, Spalten & Recordsets bei RDMS
Die Tabelle ist die einzige Form der Datenaufbewahrung
Sie ist zweidimensional angelegt, hat also
Reihen (ROWS) und Spalten (COLUMNS)
Sie ist dynamisch, d.h. die Struktur kann
immer verändert werden.
Länge durch hinzufügen von Datensätzen (Recordsets) ändern,
Felddatentyp ändern
Felder hinzufügen oder löschen etc.
Die Daten werden nach Zugehörigkeit zu einem Begriff
z.B.: Firmenname („Schlüssel“ als eindeutiger Zeilenbezeichner) in
COLUMNS geordnet , Adresse, Straße, Telephonnummer......
Zur programmgesteuerten Bearbeitung erstellt man mit SQL sogenannte
Recordsets (Spiegelbilder = Kopien) der jeweils gewünschten Daten.
7
SQL - Structured Query Language
Wilhelm Moser
Indizes - Allgemeines über Suchhilfen
Ein Reisebüro möchte allen
[Kunden] mit [L_CODE] = AUT
ein Werbeprospekt schicken:
„Primär-Schlüssel“
1. Möglichkeit:
Alle Datensätze werden in dem
Feld [L_CODE] nach dem
Wert „AUT“ durchsucht ( sequentiell )
„Unique Index“
2. Möglichkeit
Zur Tabelle wird eine Indexdatei erstellt, die Land und Recordsetnummer speichert.
Die Verwaltung beim Hinzufügen oder Löschen der INDIZES übernimmt heute das RDBMS selbständig.
Empfehlung:
8
indizieren Sie nur notwendige Felder sonst wird das System zu langsam.
Erstellen Sie zumindest einen „Unique Index“ oder „Primary Key“
SQL - Structured Query Language
Wilhelm Moser
Das Entity-Relationship Model (ERM)
Relationen sind Beziehungen von Daten in Quell- und Zieldateien
1:1
1:n
Beziehung
Beziehung
[Lieferanten] -- [Adressendetails_Lieferanten]
[Lieferanten] >>[Artikel]
Eine 1:n-Beziehung ist der häufigste
Beziehungstyp. In einer 1:n-Beziehung können
einem Datensatz in Tabelle A mehrere
passende Datensätze in Tabelle B zugeordnet
sein, aber einem Datensatz in Tabelle B ist nie
mehr als ein Datensatz in Tabelle A zugeordnet.
m:n
Beziehung
[Artikel] [Bestelldetails] [Bestellungen]
In einer m:n-Beziehung können jedem Datensatz in Tabelle A mehrere Datensätze in Tabelle B zugeordnet sein und umgekehrt. Dies
ist nur möglich, indem eine dritte Tabelle (Verbindungstabelle) definiert wird, deren Primärschlüssel aus zwei Feldern besteht, den
Fremdschlüsseln aus den Tabellen A und B. Eine m:n-Beziehung besteht eigentlich aus zwei 1:n-Beziehungen mit einer dritten
Tabelle. Zwischen der Tabelle Bestellungen und der Tabelle Artikel besteht z.B. eine m:n-Beziehung, die durch Erstellung von zwei
1:n-Beziehungen zur Tabelle Bestelldetails definiert wird.
9
SQL - Structured Query Language
Wilhelm Moser
Was sind Abfragen ? - Allgemeines
1
SQL - Structured Query Language
Wilhelm Moser
Erstellen von Abfragen
1
SQL - Structured Query Language
Wilhelm Moser
Die Auswahlabfrage
1
SQL - Structured Query Language
Wilhelm Moser
Unser Übungsmodell - Reise.mdb
Als Übungsmodell habe ich ein REISEBÜRO gewählt und
die gängigste Datenbank am Client ist wohl MS-ACCESS.
Folgende Tabellen stehen zur Verfügung:
1
SQL - Structured Query Language
Wilhelm Moser
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 und
Datenbank- übergreifenden Datenaustausch
Applikationen sind nicht an eine spezielle Datenbank gebunden
Die Verbindung zur Datenbank ermöglichen spezielle Treiber wie
ODBC, DAO, ADO, RDO MS-Jet Engine.....
1
SQL - Structured Query Language
Wilhelm Moser
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
Datenstrukturen definieren
Datenfluß kontrollieren
1
DML
DDL
DCL
Data Manipulation Language
Data Definition Language
Data Control Language
SQL - Structured Query Language
Wilhelm Moser
DML - Data Manipulation Language - Übersicht
Ein Teil der SQL wird als
DML bezeichnet. Die
Funktionen zur Datenmanipulation sind:
Erfassen
Ändern
Löschen
Abfragen
SQL Sprachelemente sind nicht Case-Sensitive. Das gilt allerdings nicht für
Strings.
Bei Abfragen aus Programmiersprachen kann die Art der Darstellung des
Recordsets gesetzt werden:
OpenTable, OpenDynaset, OpenSnapshot
(OpenTableDef)
1
SQL - Structured Query Language
Wilhelm Moser
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
Durch Definition von Bedingungen ist die gezielte Auswahl möglich:
SELECT NAME, UMSATZ FROM KUNDEN
WHERE UMSATZ > 4000
1
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - Operanden & Operatoren Listing
Die Operanden
Konstante
Columns
Funktionen
Konstantenliste
1,‘HERMANN‘.....
kunden.name....
min(kunden.nr), 4+5, now.... (Achtung datenbankspezifisch)
(´JA´,´NEIN´,´UNBEKANNT´)....
Operatoren (Vergleich und logische)
1
=
<>
<=
>=
in
not in
between and
not between and
like
not like
is null
is not null
Gleich
Ungleich
Kleiner oder Gleich
Größer oder Gleich
Operand innerhalb einer Liste
Operand nicht innerhalb einer Liste
Operand zwischen 2 Werten
Operand außerhalb der 2 Werte
Operand so ähnlich wie: Vergleich mit Wildcards
Operand nicht ähnlich wie
Operand ist unbestimmt (NULL)
Operand ist bestimmt
and
or
logisches und
logisches oder
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - Operanden & Operatoren
Logisches and
SELECT NAME, ADRESSE FROM KUNDEN
WHERE L_CODE = 'AUT' and UMSATZ >= 4000
Logisches 0r
SELECT BEZ, L_CODE, SPRACHE FROM Reisegebiete
WHERE SPRACHE='DEUTSCH' Or SPRACHE='ENGLISCH'
Operand in
SELECT BEZ, L_CODE, SPRACHE FROM Reisegebiete
WHERE SPRACHE in ('DEUTSCH','ENGLISCH')
Operand between
SELECT Name, Adresse, Umsatz from Kunden
WHERE Umsatz between 4000 and 9999
1
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - Funktionen & Wildcards
Operand Like mit Wildcards wie auf Betriebssystemebene
SELECT * from Reisegebiete where Bez like 'M*I?'
Operand IS NULL
SELECT NAME, ADRESSE FROM KUNDEN
WHERE TELEFON IS NULL
Funktion HEUTE mit IS NULL
SELECT NAME, TELEFON, Format(Now(),"dd/mm/yy") AS HEUTE
FROM KUNDEN
WHERE TELEFON Is Null
2
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - Joins
Wir wollen die bevorzugten Reisegebiete von Andrea wissen. Derzeit müßten
wir wie folgt vorgehen:
1. In [KUNDEN] die Nummer von Andrea suchen
2. In [KUNDEN_GEBIETE] die Nummern aller Reisegebiete von Andrea suchen
3. In [REISE_GEBIETE] die Nummern der Reisegebiete aufsuchen.
Now lets JOIN Tables
1.
2.
3.
SELECT REISEGEBIETE.BEZ
FROM KUNDEN, KUNDEN_GEBIETE, REISEGEBIETE
WHERE KUNDEN.NAME = 'ANDREA'
AND KUNDEN_GEBIETE.G_NR = REISEGEBIETE.NR
AND KUNDEN.NR = KUNDEN_GEBIETE.K_NR
Durch diese Tabellenverknüpfung wird eine temporäre, relationale
Verbindung hergestellt ohne daß eine Referenz (2 Schlüsselfelder) besteht.
JOIN wird nicht angegeben.
2
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - Alias Namen & Inner Joins
Wozu ALIAS Namen ?
1.
2.
Bei Spalten
Bei Tabellen
schönere Feldbezeichnung
Verkürzung des Tabellennamens
Beispiel:
SELECT KUNDEN.NAME AS Vorname, INTERESSEN.BEZ as Interessen
FROM
(KUNDEN
INNER JOIN KUNDEN_INTERESSEN ON KUNDEN.NR
=KUNDEN_INTERESSEN.K_NR
)
INNER JOIN INTERESSEN ON KUNDEN_INTERESSEN.I_NR =
INTERESSEN.NR
Der Inner Join kombiniert Datensätze aus zwei Tabellen, sobald ein
gemeinsames Feld dieselben Werte enthält.
2
SQL - Structured Query Language
Wilhelm Moser
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.
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 mit dem Assistenten
und Unterabfragen: (dazu später)
SELECT DISTINCTROW KUNDEN.ADRESSE, KUNDEN.NAME
FROM KUNDEN
WHERE (((KUNDEN.ADRESSE) In (SELECT [ADRESSE] FROM [KUNDEN] As Tmp
GROUP BY [ADRESSE] HAVING Count(*)>1 )))
ORDER BY KUNDEN.ADRESSE;
2
SQL - Structured Query Language
Wilhelm Moser
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)
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
2
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - Subqueries - Unterabfragen
Ronnie beschließt eine Reise zu unternehmen und möchte in ein Land
reisen, das auch Andrea besucht(e).
Beispiel einer Unterabfrage:
SELECT G.BEZ
FROM REISEGEBIETE AS G, GEBIETE_INTERESSEN AS GI
WHERE 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 Operatoren
verknüpfen und sogar abhängige Subqueries erstellen.
2
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - Correlated Subqueries
Versuchen wir das Beispiel der zusammen wohnenden RONNIE &
JUDITH (Self Join) mit einer Unterabfrage zu lösen
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.
2
SQL - Structured Query Language
Wilhelm Moser
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.
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 „Logik“ zu bringen. Diese beiden Datenarten haben in einer
Spalte nichts verloren.
2
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - ORDER BY & GROUP BY
So, nun wird’s Easy - Order by läßt uns Daten sortieren
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;
2
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - DISTINCT Statement
Durch das Statement DISTINCT(ROW) läßt sich die Ausgabe doppelter
Zeilen vermeiden:
SELECT L_CODE FROM KUNDEN;
SELECT DISTINCT L_CODE FROM KUNDEN;
liefert:
AUT
AUT
DEU
AUT
ENG
2
AUT
DEU
ENG
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - INSERT INTO - Anfügeabfrage
Ermöglicht das Anfügen von Daten an eine bereits bestehende Tabelle.
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.
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - UPDATE - Aktualisierungsabfrage
Durch die UPDATE Anweisung können Daten eines bereits bestehenden Datensatzes
verändert werden.
UPDATE KUNDEN
SET 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.
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DML - DELETE - Löschabfrage
Mit dem DELETE Statement können benannte Datensätze (WHERE....) gelöscht
werden.
DELETE FROM KUNDEN WHERE NR = 3;
oder
DELETE 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.
3
SQL - Structured Query Language
Wilhelm Moser
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-DML angesprochen wird
Hier ein Leitfaden für die Lösung komplexer Aufgaben:
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
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)
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DDL - Data Definition Language
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.
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DDL - Tabellen - Allgemeines & Schlüssel
Tabellen besitzen Spalten und Zeilen. Die Spalten
müssen einem bestimmten Datentyp zugeordnet sein.
Eine Tabelle kann einen Primärschlüssel
und mehrere Fremdschlüssel haben.
Dies dient zur Konsistenzsicherung
in der Datenbank und ist oft fester
Bestandteil der Tabellendefinition.
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.
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DDL - Tabellen - CREATE TABLE
Nachdem man alle Tabellenstrukturen (auf dem Papier) konzipiert hat, 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
);
bei Access ist das mit dem INTO Statement möglich
SELECT [K_NR] AS A1, [I_NR] AS A2 INTO KUNDEN_INTERESSEN GROUP BY [K_NR], [I_NR];
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DDL - Tabellen - ALTER & DROP TABLE
Also, nachdem wir soweit gekommen sind ist das folgende ja wohl ein Klacks:
ADD
Spalten hinzufügen
ALTER TABLE KUNDEN ADD FAX CHAR(25) NOT NULL;
DROP
Spalten / Tabellen löschen
ALTER TABLE KUNDEN DROP GEBURTSDATUM;
DROP TABLE KUNDEN;
RENAME
Spalten umbenennen
ALTER TABLE KUNDEN RENAME ADRESSE STRASSE;
(nicht in Access)
MODIFY
Spalten ändern
ALTER TABLE KUNDEN MODIFY NAME CHAR(50) NOT NULL;
(nicht in Access)
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DDL - Indizes - Allgemeines
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:
3
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 - Structured Query Language
Wilhelm Moser
SQL-DDL - Indizes - Create & Drop
Auch bei Create Index kann man bei der Definition angeben, ob der Index
aufsteigend oder absteigend sortiert werden soll. (ORDER BY)
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_I2
ON KUNDEN
(L_Code, Geburtsdatum);
DROP INDEX KUNDEN_I1 ON KUNDEN;
3
SQL - Structured Query Language
Wilhelm Moser
SQL-DDL - Views - Allgemeines ( Server based )
Mit Views können logische Ansichtsfenster „über“ eine Tabelle gelegt werden.
VORTEILE:
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.
4
SQL - Structured Query Language
Wilhelm Moser
SQL-DDL - Views - Create & Drop View ( Server based )
View‘s entsprechen bei der dynamischen Frontendverwaltung von Access dem
QBE-Bereich zur Erstellung von Recordsets vom Typ Dynaset oder Snapshot.
Durch die umfassenderen Möglichkeiten von Access ist die VIEW in der ANSIStandard Form in Access nicht vorgesehen.
Hier zwei Beispiele für Server basierte View‘s in create & drop
CREATE VIEW KUNDENINT ( KUNDEN, INTERESSEN) AS
SELECT K.NAME, I.BEZ
FROMKUNDEN K, KUNDEN_INTERESSEN KI, INTERESSEN I
WHEREK.NR = KI.K_NR
AND KI.I_NR = I.NR;
DROP VIEW KUNDENINT;
4
SQL - Structured Query Language
Wilhelm Moser
SQL-DCL - Data Control Language
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.
4
SQL - Structured Query Language
Wilhelm Moser
SQL-DCL - COMMIT & ROLLBACK
Hier also ein Beispiel für Server based commit:
.
.
.
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.
4
SQL - Structured Query Language
Wilhelm Moser
SQL-DCL - Summary
Also ehrlich, für mich war das recherchierte Material so unergiebig, daß ich mich ruhigen
Gewissens auf einen Bereich zurückziehe - 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 folgenden
Auflistungen, Methoden und Eigenschaften.
Auflistungen
Methoden
Errors
Properties
Workspaces (Voreinstellung)
BeginTrans
CommitTrans
CompactDatabase
CreateDatabase
CreateWorkspace
Idle
OpenConnection
OpenDatabase
RegisterDatabase
RepairDatabase
Rollback
SetOption
Eigenschaften
DefaultPassword
DefaultType
DefaultUser
IniPath
LoginTimeout
SystemDB
Version
4


 nur in MS Jet-Arbeitsbereichen
 nur in ODBCDirect-Arbeitsbereichen






SQL - Structured Query Language
Wilhelm Moser
über moser_willi
4
SQL - Structured Query Language
Wilhelm Moser
SQL - Structured Query Language
aber das können Sie ja bereits...
Danke schön,
bezauberndes
Sie waren ein
Publikum
auf wiedersehen bei
weiteren interessanten Kursen...
4
SQL - Structured Query Language
Wilhelm Moser
SQL - Structured Query Language
PAU S E !
und nach der Pause ?
noch viel interessantere Dinge .....
4
SQL - Structured Query Language
Wilhelm Moser