SQL

Einleitung

SQL bildet einen Standard für Datendefinition, -manipulation, -management, und -zugriffsbeschränkungen. SQL basiert auf dem Konzept von relationalen Datenbanken. SQL ist eine Entwicklung von IBM und wurde erstmals 1986 vom ANSI standardisiert. Es existieren heute mehrere durch Jahreszahlen gekennzeichnete Richtlinien: SQL-89, SQL-92 (SQL2) und SQL-93 (SQL-3). Die verschiedenen Datenbankhersteller haben die SQL Normen ganz oder teilweise in ihren Produkten umgesetzt. MS Access unterstützt SQL-89, während die meisten Mainframe-Hersteller SQL-92 implementieren. Der Sprachumfang von SQL setzt sich aus drei Teilen zusammen: der Data Definition Language (DDL) und der Data Manipulation Language (DML). Der weitere Teil, mit dem eine Datenbank geschützt werden kann, nennt sich Data Control Language (DCL). Die DDL stellt alles zur Verfügung, was man benötigt um eine Datenbank zu definieren, die Struktur einer DB zu modifizieren oder eine DB zu löschen. Mit der DML spezifiziert man, was man aus einer DB auslesen bzw. was man einfügen möchte. Embedded SQL erlaubt es SQL-Statements innerhalb von Programmiersprachen zu verwenden. Sie werden innerhalb des Codes besonders gekennzeichnet. Bei der Compilierung werden diese gekennzeichneten Statements von einem Präcompiler in die entsprechende Sprache übersetzt, so dass reiner Quelltext entsteht. Diese 'statische' Übersetzung bezieht sich immer auf eine bestimmte Datenbankart. Dynamisches SQL erlaubt es SQL-Statements zur Laufzeit zu generieren. Ein Call Level Interface (CLI) ist in der Regel als Funktionsbibliothek realisiert. Constraints sind Einschränkungen für die Daten die in eine Tabelle eingegeben werden können. Constraints werden u.a. auch im Oracle-Kontext Integritätsregeln genannt.

Begriffe

Zentrales Element von relationalen Datenbanken (DB) sind Tabellen/Relationen, die die eigentlichen Daten enthalten. Objekte/Entities werden durch Relationenschemas beschrieben. Relationenschemas bestehen aus einer Anzahl Attributen. Eine Datenbank bzw. ein Datenbankschema besteht aus n Tabellen. Metadaten sind Daten, welche die Struktur der Daten innerhalb einer DB beschreiben. Sie werden im sogenannten data dictionary gespeichert. Die Struktur einer ganzen DB wird als konzeptionelle Sicht oder auch vollständiges logisches Modell bezeichnet. Das Schema gehört zu den Metadaten und ist eine benannte Kollektion zusammengehörigen Tabellen, wodurch ein Namensraum aufgespannt werden kann. Ein Katalog ist eine benannte Kollektion von Schemata, wodurch eine weitere Stufe des Namensraums aufgespannt wird. Ein Attribut einer Relation kann eine endliche Anzahl von Werten annehmen. Die Gesamtmenge dieser Werte wird als Domäne des Attributs bezeichnet. Constraints sind Regeln, die festlegen, welche Werte die Attribute einer Tabelle annehmen dürfen. Es gibt drei Arten von Constraints: Spalten-Constraints, Tabellen-Constraints und Zusicherungen (assertions), daß sind Constraints, die mehr als eine Tabelle betreffen. Ein Spalten-Constraint ist z.B. daß Check-constraint bzw. in Access Gültigkeitsregel.

Relationen/Tabellen

In der DB Theorie sind Tabellen Relationen bzw. eine Tabelle ist eine Relation. Die Spalten in Tabellen heißen Felder und die Zeilen Datensätze. Ein gemeinsames Feld in mehreren Tabellen verknüpft diese Tabellen. Eine Relation ist eine Teilmenge des kartesischen Produkts über den Wertebereich der Attribute eines Relationenschemas. Eine Relation zum Relationenschema R wird durch r(R) gekennzeichnet.

DB_Relation.jpg

Ein (Spalten-)Attribut ist eine bestimmte Eigenschaft eines Feldes in einer Tabellenspalte. Wie sich jedermann/frau vorstellen kann, werden Tabellenspalten durch die Angabe des Datentyps charakterisiert. Beispielsweise können diese Datentypen vom Typ DATE, CHAR, VARCHAR, INTEGER...u.s.w. sein. Attribute von Feldern charaktierisieren aber über den Datentyp hinaus das Verhalten der Datensätze in einer Spalte. Z.B. kann hier festgelegt werden, daß z.B. eine Spalte vom Typ VARCHAR(20) nicht mehr in der Größe wachsen darf, auch wenn jemand einen längeren String ablegen möchte. Das Attribut, daß das Feld variabel ist, ist meist die Standardeinstellung. Ein weiteres Attribut ist z.B. die Festlegung, daß ein Feld in einer Tabellenspalte konstant sein muß. Im Normalfall geht man davon aus, daß alle Werte verändert werden können, es gibt jedoch manchmal die Forderung nach der Festlegung von Werten, z.B. nach einem Monatsabschluß bei der Finanzzbuchhaltung oder bei einer Umstellung der MwSt. im April 1998 und zu Beginn 2000. Ein weiteres Attribut kann sein, daß eine automatische Sortierung in auf-oder absteigender Reihenfolge vorgenommen wird. Ein Primärschlüssel (primary key) unterscheidet die Datensätze in einer Tabelle, d.h. jeder Datensatz ist anhand des Primärschlüssels eindeutig zu identifizieren. Es gibt auch zusammengesetzte Primärschlüssel, wobei die Attribute nicht leer sein dürfen. Ein Fremdschlüssel (foreign key) ist eine Spalte oder eine Gruppe von Spalten in einer Tabelle, die einen Primärschlüssel in einer anderen Tabelle referenziert. Ein Fremdschlüssel muß selbst nicht eindeutig sein, aber er muß einen eindeutigen Primärschlüssel referenzieren. Ein Sekundärschlüssel beinhaltet all die Attribute, die potentiellen Primärschlüsselcharakter haben, aber nicht als Primärschlüssel herangezogen wurden, sondern nur ergänzenden Charakter haben. Ein Surrogat Key ist ein künstlicher Primärschlüssel, der keine Bedeutung trägt, wie z.B. der Name als Primärschlüssel für eine Tabelle User.

1:n Relation

Zu Datensätzen in Tabelle A gibt es immer genau eine Entsprechung in Tabelle B. Zu Tabelle B gibt es keine Entsprechung, eine oder mehrere Entsprechungen in Tabelle A. Der Fremd-Key in der Detailtabelle ist der Primär-Key in der Mastertabelle.

1_zu_n_Relation.jpg

n:m Relation

Zu Tabelle B gibt es keine Entsprechung, eine oder mehrere Entsprechungen in Tabelle A. Zu Tabelle A gibt es keine Entsprechung, eine oder mehrere Entsprechungen in Tabelle B. Dies ist nur möglich, indem eine dritte Tabelle definiert wird (die als Verbindungstabelle bezeichnet 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. m_zu_n_Relation.jpg

ER- Diagramme

ER_Diagramme.jpg

Entity Integrität

Um die Entity-Integrität zu besitzen, muß eine Tabelle über einen Primärschlüssel verfügen, der jede Zeile in der Tabelle eindeutig identifiziert. Um die Entity-Integrität zu gewährleisten, muß man sicherstellen, daß die Spalte oder die Spalten, die den Primärschlüssel bilden, keine Nullwerte enthalten. Außerdem muß der Primärschlüssel das Constraint UNIQUE erfüllen.

Domänenintegrität

Damit ist gewährleistet, daß jeder Eintrag in einer Spalte einen gültigen Wert besitzt.

Referentielle Integrität

Die Regeln der referentielle Integrität verbieten verwaiste Fremdschlüssel, d.h. es können keine Zeilen einer Tabelle (Mastertabelle) gelöscht werden, die in einer anderen Tabelle (Detailtabelle) als Fremdschlüssel werden.

Index

Ein Index beschleunigt das Suchen und Sortieren von Datensätzen. Die Daten in einer Tabelle werden in der Regel in der Reihenfolge gespeichert wie sie eingegeben werden. Im Index wird die Position der Daten nachgeschlagen. Sie können Indizes auf ein einzelnes Feld oder auf mehrere Felder legen. Zusammengesetzte Indizes erlauben das Unterscheiden zwischen Datensätzen, deren Inhalt im ersten Feld möglicherweise identisch ist Ein Index sollte für Spalten nach denen oft gesucht oder sortiert wird ein Index angelegt werden, allerdings kostet die Verwaltung jeder zusätzlicher Indexe zusätzliche Rechenzeit.

Joins

Man verbindet zwei Tabellen in einem Join über eine connecting bzw. join column. Beispielsituation:

Inner joins between two tables returns rows of data that exist across all joined tables, excluding rows that may only exist in one of the tables but not the other table. Outer joins return every row that exists in the left (in a left outer join) or right (in a right outer join) joined table, while rendering NULL values on rows whose foreign key does not match a record in the other (right or left) table. The "left" table refers to the table to the left of the JOIN statement in the query, whereas the "right" table refers to the table to the right of the JOIN statement in the query.

Ein inner join kombiniert Datensätze aus zwei Tabellen, sobald ein gemeinsames Feld dieselben Werte enthält (unabhängig ob es primary oder foreign key Felder sind):

Left joins schließen alle Datensätze aus der ersten (linken) Tabelle von zwei Tabellen ein, auch wenn keine entsprechenden Werte für Datensätze in der zweiten (rechten) Tabelle vorhanden sind:

Right joins schließen alle Datensätze aus der zweiten (rechten) Tabelle von zwei Tabellen ein, auch wenn keine entsprechenden Werte für Datensätze in der ersten (linken) Tabelle vorhanden sind.

Man kann auch kombinierte Joins ausführen:

View

A view is an alternative way of looking at the data in one or more tables. Views are sometimes called virtual tables (virtuelle Tabellen) or derived tables (abgeleitete Tabellen). Im Gegensatz dazu werden 'echte' Tabellen oft base tables (Basistabellen) genannt. Ein View enthält keine Kopie der Daten, sondern die realen Daten.

Datentypen

SQL-92 unterstützt folgende Datentypen: INTEGER, SMALLINT, NUMERIC(<Decimal>,<Nachkomma>), DECIMAL, REAL, DOUBLE PRECISION, FLOAT, , , BIT, BIT VARYING, DATE, TIME, TIMESTAMP. Es gibt einen besonderen Wert, den NULL Wert.

CHAR(<LENGTH>)	s. CHARACTER
CHARACTER(<LENGTH>)
CHARACTER VARYING
VARCHAR(<LENGTH>)	s. CHARACTER VARYING

Operatoren

Berechnungsoperatoren

+ - * / ||

bei Access wird statt || der + Operator verwendet. 

logische Operatoren

= <> < > <= >= AND NOT

Funktionen

AVG() COUNT() MAX() MIN() SUM()

Strings

'<string>'

Sonderzeichen

Sonderzeichen werden mit einem Backslash gequoted.

Befehle

<Spaltenliste> := * | (<Ausdruck> [AS <Alias>] {, <Ausdruck> [ AS <Alias>] } )

Domäne erzeugen

<Tabelle erzeugen>

:=

CREATE DOMAIN <DomainName> <DomainType> \\CHECK ???;

Tabelle erzeugen

<Tabelle erzeugen>	:=	CREATE TABLE <TableName> (<Cols>);
<Cols>	:=	<Col> { ,<Col> } [,<Constraints>]
<Col>	:=	<ColumnName> <ColumnType> [<ColConstraint> <KeyInfo>]
<ColumnType>	:=	TEXT[(<Laenge>)]
<ColConstraint>	:=	'NOT NULL ' UNIQUE <CheckColConstraint>
<CheckColConstraint>	:=	CHECK ( <CheckColExpression> )
<KeyInfo>	:=	PRIMARY KEY
<Constraints>	:=	CONSTRAINT <ConstraintName> \\(<PrimaryKeyConstraint> <ForeignKeyConstraint> <CheckConstraint>)
<PrimaryKeyConstraint>	:=	PRIMARY KEY(<ColName>{,<ColName>)
<ForeignKeyConstraint>	:=	'FOREIGN KEY(<ColName>) \\REFERENCES <ForeignTableName> (<ForeignPrimaryKey>)\\[ON DELETE [CASCADE ' SET NULL]]
<CheckConstraint>	:=	CHECK ( <CheckExpression> )

PRIMARY KEY schließt NOT NULL mit ein

Beispiel

MySQL:

create table VUser (ID INT NOT NULL AUTO_INCREMENT, USER CHAR(30), 
                    PASSWORD CHAR(30),
                    PRIMARY KEY (ID));

MS-Access:
CREATE TABLE KUNDE 
(
  KUNDE_ID 	INTEGER 		PRIMARY KEY,
  VORNAME	CHARACTER(15),
  NAME	CHARACTER(20)	NOT NULL,
)

Spezialitäten

MS Access:

• PRIMARY KEY als Colattribut wird nicht unterstützt, sondern muß über Constraint erfolgen
• CHECK ... als Colattribut geht nicht und muß als Gültigkeitsregel für eine Spalte angelegt werden

Tabelle verändern

<Tabelle verändern>	:=	ALTER TABLE <TableName> <Change>;
<Change>	:=	ADD <ColName> <ColType> {, ADD <ColName> <ColType>} \\RENAME <TableName> \\MODIFY <ColName> <ColType> \\DROP COLUMN <ColName> \\RENAME COLUMN <ColName>

Beispiel

MySQL:

ALTER TABLE <TableName> ADD K1SIZE INT, ADD K2SIZE INT;

Tabelle löschen

<Tabelle löschen>

:=

DROP TABLE <TableName>;

Beispiel

MySQL: DROP TABLE VUser;

View erzeugen

<View erzeugen>	:=	CREATE VIEW <ViewName> [<ViewSpaltenliste>] AS <SelectionList> FROM <Source>
<SelectionList>	:=	???
<Source>	:=	(<TableName> <ViewName>) <Condition>
<Condition>	:=

Die Viewspaltenliste muß nur angegeben werden, wenn die Spalten in der View andere Namen als in den Ausgangstabellen haben sollen. MS Acess:

• In MS Access gibt es diesen Befehl nicht. Eine Abfrage ist immer ein View.

Selektion

<Selection>	:=	'SELECT [DISTINCT] [<SpaltenExpressions> ]\\FROM <Tabellenliste> \\[WHERE <Prädikat>] \\[GROUP BY <Bedingung>] \\[HAVING <Bedingung>] \\[ORDER BY <Sortierfelder> [(ASC'DESC)]]
<SpaltenExpressions>>	:=	COUNT (<Spalte>) \\<Spalte> <MathematicalExpression> \\<Spalte> [AS <Alias>] {,<SpaltenExpression>}
<Spalte>	:=	* [<Tabellenname>.] <Spaltenname>
<Tabellenliste>	:=	<Tabellenname> [AS <Alias>] {, <Tabellenname> [AS <Alias>]}

SQL wertet diese Klauseln in der Reihenfolge FROM, WHERE, GROUP BY, HAVING und schließlich SELECT aus. ORDER BY wird nach SELECT ausgeführt d.h. in funktionaler Notation ausgedrückt ORDER BY(SELECT(HAVING(GROUP BY(WHERE(FROM...))))). Enthält die <Tabellenliste> mehr als eine Tabelle, wird das kartesische Produkt gebildet, d.h. jede Zeile der einen Tabelle mit der Zeile der anderen Tabelle kombiniert, d.h. bei n Zeilen in Tabelle 1 und m Zeilen in Tabelle 2 die resultierende virtuelle Tabelle n*m Zeilen enthält. DISTINCT verhindert doppelte Einträge. Es wirkt auf alle angegebenen Spalten. Aliasing mit AS eignet sich um Mehrdeutigkeiten zu vermeiden, oder wenn man beispielsweise zu Testzwecken die Abfrage auf eine andere Tabelle ausführen möchte. Im Rest der Selektion wird nur noch der <Alias> verwendet. MS Access: Die Syntax für das Erzeugen einer Tabelle aus einer Selektion lautet SELECT <SpaltenExpression> INTO <TabellenName> FROM ...

WHERE Prädikat

<Prädikat>	:=	<Vergleichsprädikat> <LIKEPrädikat> <INPrädikat> <NOTINPrädikat> <BEETWEENPrädikat> <NULLPrädikat> <ALLPrädikat> <SOMEPrädikat> <ANYPrädikat> <EXISTSPrädikat> <UNIQUEPrädikat> <OVERLAPSPrädikat> <MATCHPrädikat> <ISPrädikat> { <LogicalOperator> <Prädikat>} <Klammern>
<ANYPrädikat>	:=
<BETWEENPrädikat>	:=	<Spalte> BETWEEN <Value1> AND <Value2>
<INPrädikat>	:=	<Spalte> IN ( <Values> )
<ISPrädikat>	:=	<Spalte> IS [NOT] [ <Value> NULL ]
<LIKEPrädikat>	:=	<Spalte> ['NOT] LIKE ''<LikeString>' [ ESCAPE ''<EscapeChar>'' ]
<LIKEString>	:=	{ <Letter> %' '_ } % is the wildcard character, _ represents a single character
<LogicalOperator>	:=	AND OR
<NOTINPrädikat>	:=	<Spalte> NOT IN ( <Values> )
<Vergleichsprädikat>	:=	<Vergleichsoperand> <Vergleichsoperator> <Vergleichsoperand>
<Vergleichsoperator>	:=	=, <, >, <>, <=, >=
<Klammern>	:=	( )
<EXISTPrädikat>	:=	[NOT] EXISTS (<Selection>) (s. 6.6.2), wenn mindestens eins exisitiert.

Die Grenzwerte des BETWEENPrädikats gehören immer zum spezifizierten Wertebereich. Der erste Wert des BETWEENPrädikats darf nicht größer als der zweite sein.% steht für eine beliebige Zeichenfolge mit 0 bis n Zeichen, _ steht für ein einzelnes Zeichen.

Beispiele/Examples für Selektionen

Der folgende Befehl gibt alle Daten der Tabelle Adressen wieder:

SELECT * FROM Adressen;

Der Befehl gibt alle Daten des kartesische Produkt der Tabellen Kunde und Verkauf zurück:

SELECT * FROM KUNDE, VERKAUF;

Der folgende Befehl zeigt die Verwendung von Aliasen:

SELECT A.ID, B.Name FROM Tab1 AS A, Tab2 AS B WHERE A.ID = B.ID;

Beispiel für Exists:

select * from anwender AS A where not exists (select B.AnwenderID from bewertungen AS B where B.BewertungID = 3 and B.BefragungID = 2 and B.AnwenderID = A.ID)

Fehlende Foreign Keys:

SELECT * FROM `produktattribute` pa WHERE pa.variantennummer NOT IN (SELECT variantennummer FROM preise)

Selektionsparameter

MS Access:

<Selektionsparameter>

:=

PARAMETERS {[} <ParaName> {]} <Parametertype> {, <ParaName> <Parametertype>;\\<Selection>

Schema erzeugen

<Schema erzeugen>

:=

CREATE SCHEMA <SchemaName>;

Daten einfügen oder kopieren

Beispiel

MySQL:

INSERT INTO user (host, user, password) VALUES ('localhost', 'horst', password('horst'));

INSERT INTO menux (id,name,comment,parent_id) SELECT menu.ID, menu.Name, menu.Comment, menu.ParentID FROM menu;

Spezialitäten:

MS Access: MS Access wandelt die VALUES-Notation immer in eine SELECT-Notation um

Daten aktualisieren

<Daten aktualisieren>	:=	UPDATE <TabellenName> SET \\<Spaltenset> {,<Spaltenset>}\\[ WHERE <Prädikat> ];
<Spaltenset>	:=	<Spaltenname> = <Expression>

Beispiel

MySQL: UPDATE <TableName> SET <ColumnName>=3, <Columnname>='x' WHERE <TableName>.OID = 324534;

Daten löschen

<Daten löschen>	:=	DELETE FROM <TabellenName> \\[ WHERE <Prädikat> ];
<Daten löschen>	:=	'TRUNCATE TABLE' <TabellenName>;

Beispiel

MySQL: alle Daten aus einer Tabelle löschen DELETE FROM <TableName>;

Datenbankschutz

In SQL können folgende DB Objekte geschützt werden:

• Tabellen
• Spalten
• Sichten
• Domänen
• Zeichensätze
• Sortierfolgen
• Übersetzungstabellen

Datenmodellierung

Abbildung eines OO-Modells auf ein relationales Modell

eine Tabelle pro Klasse eine Spalte pro Attribut 1:1 Beziehungen durch foreign key bei Aggregation Daten mit in der Klassentabelle 1:n Beziehungen das referenzierte Objekt erhält Id des referenzierenden Objekts als foreign key m:n Beziehungen durch Hilfstabelle Vererbung: alle Unterklassen mit Basisklassen in einer Tabelle (flache Abbildung) ist performantt, evtl. viele leere Felder, Tabelle muß bei jeder Subclass geändert werden Vererbung: alle Unterklassen in separater Tabelle (hierachische Abbildung), neue Subclasses entkoppelt, jeder Zugriff erforder ein Join

Transaktionen

Änderungen werden innerhalb eines Transaktionskontext durchgeführt Änderungen nur innerhalb der Transaktion sichtbar Änderungen werden bei Commit für alle sichtbar Änderungen lassen sich durch ein Rollback zurücknehmen

pessimistisches Sperren

Objekt wird vor dem Schreiben bis zum Ende der Transaktion explizit gesperrt, keine Änderungskonflikte, Performance

optimistisches Sperren

Objekt wird nicht gesperrt Schreiben nur dann erfolgreich, wenn das Objekt seit dem Lesen nicht verändert wurde performant mehrfache Eingabe von Änderungen

Literatur

• Microsoft Access 97: Das Handbuch
• SQL für Dummies
• SQL.vsd