SQL-Referenz Neue Tabelle anlegen Domäne anlegen CREATE TABLE tabellenname ( spaltendefinition, PRIMARY KEY (spaltenliste) UNIQUE (spaltenliste), FOREIGN KEY (spaltenname) REFERENCES tabellenname(spaltenname) [ON DELETE aktion] [ON UPDATE aktion] CHECK (bedingung)); CREATE DOMAIN domänenname AS datentyp [DEFAULT wert] [NOT NULL] [CHECK (bedingung)] [COLLATE sortierreihenfolge]; Domänendefinition ändern ALTER DOMAIN domänenname anweisung; Spaltendefinition spaltenname {datentyp | datendomäne} NOT NULL | DEFAULT wert | COLLATE sortierreihenfolge | CHECK(bedingung) | PRIMARY KEY | UNIQUE | REFERENCES tabellenname(spaltenname) [{ON UPDATE | ON DELETE} {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]; Domäne löschen DROP DOMAIN domänenname; Index definieren CREATE INDEX indexname ON tabellenname (spaltenliste); Beispiel: CREATE INDEX idx_name ON kunde (name); Integritätsregeln Primärschlüssel (PRIMARY KEY): Jeder Datensatz kann eindeutig angesprochen werden. Regeln für die Indexdefinition Indizes werden nur auf Felder angelegt, in denen häufig gesucht wird. Fremdschlüssel (FOREIGN KEY): Dient der eindeutigen Verknüpfung von Tabellen im relationalen Modell. Tabellen, die vor allem zur Speicherung von Informationen (z.B. LOG-Dateien) dienen und nicht abgefragt werden, benötigen in der Regel keinen Index. UNIQUE: verhindert, dass doppelte Werte gespeichert werden. Die Anlage von Indizes ist nur bei einer grossen Datenmenge in den jeweiligen Feldern sinnvoll. DEFAULT: Standardwert für ein Feld, verhindert NULL-Werte. Der Datentyp darf nicht TEXT/BLOB sein oder NULLWerte zulassen. Indizes sollten nur bei Feldern angewendet werden, in denen insbesondere einzelne Datensätze gesucht werden. Tabellendefintion ändern ALTER TABLE tabellenname ADD {spaltendefinition | constraint} | ALTER [COLUMN] spaltendefinition | DROP spaltenname | DROP CONSTRAINT constraintname; Zusammengesetzte Indizes sollten dann eingesetzt werden, wenn häufige Abfragen über die jeweils gleichen Spalten durchgeführt werden. Tabellen löschen Index löschen DROP TABLE tabellenname; DROP INDEX indexname; Zeichenerklärung: [ ]: { | }: optionaler Befehlsbestandteil alternative Befehlsbestandteile 1 Datensätze einfügen Abfragen mit Aggregatsfunktionen INSERT INTO tabellenname [(spalten liste)] VALUES (werte); COUNT(merkmalsname) Anzahl der Spaltenwerte SUM(merkmalsname) Summe der Spaltenwerte AVG(merkmalsname) Durchschnitt der Spaltenwerte MIN(merkmalsname) kleinster Spaltenwert MAX(merkmalsname) grösster Spaltenwert Datensätze aktualisieren UPDATE tabellenname SET spaltenname = wert [, spaltennamel = wert, ...] [WHERE auswahlbedingung]; Abfragen mit Zeichenerkennung auswahlbedingung mit dem Operator LIKE: merkmalsname = LIKE zeichenkette Datensätze löschen zeichenkette kann folgende Platzhalter enthalten: DELETE FROM tabellenname [WHERE auswahlbedingung]; % kein, ein oder beliebig viele Zeichen ein beliebiges einzelnes Zeichen Datensätze selektieren Unterabfragen SELECT [DISTINCT] spaltenliste FROM tabellenname [aliasname] [{INNER LEFT | RIGHT} JOIN tabellennamel [aliasnamel] ON (tabellenname.spaltenname = tabellenname.spaltenname)] [WHERE auswahlbedingung] [GROUP BY spaltenliste] [HAVING auswahlbedingung] [ORDER BY spaltenliste [ASC | DESC]]; Unterabfragen ermöglichen die Verwendung eines Abfrageergebnisses unmittelbar in einer Anweisung. SELECT spaltenliste FROM tabellenname WHERE spaltennanme Vergleichsoperator ( SELECT abfrage ) Unterschieden werden Unterabfragen, die einen Wert liefern und Unterabfragen, die mehr als einen Wert zurückliefern. Beispiel: SELECT name, vorname FROM adressen WHERE ort=’Stans’ ORDER BY name; Mengenoperationen in Unterabfragen Gruppieren, Sortieren Gruppieren: Beispiel: Bei Unterabfragen, die mehr als eine Zeile zurückgeben, wird mit Mengenoperationen als Vergleichsoperator selektiert. GROUP BY spaltenliste GROUP BY ort, plz; Die Spalten werden durch ein Komma getrennt, gruppiert wird in der Reihenfolge der angegebenen Spalten. Sortieren: Beispiel: vo ALL: Prüft, ob die angegebene Bedingung auf alle Datensätze zutrifft vo ANY: Prüft, ob die angegebene Bedingung auf irgendeinen Datensatz zutrifft. SORT BY spaltenliste SORT BY name, vorname; IN: Prüft, ob ein Wert in dem Ergebnis der Unterabfrage enthalten ist. Die Spalten werden durch ein Komma getrennt, sortiert wird in der Reihenfolge der angegebenen Spalten. EXISTS: Prüft, ob die Bedingung auf mindestens einen Datensatz zutrifft. Auswahlbedingung vo ist >, <, >=, oder <= Bedingungen können auf exakte Übereinstimmung oder auf Mindest- bzw. Höchswerte prüfen. Verwendung finden dabei Vergleichsoperationen (=, >, <) oder Funktionen (z.B. MIN). Die Anzahl der Bedingungen in einer Abfrage ist nicht begrenzt. Bedingungen werden mit AND (und) oder OR (oder) miteinander verknüpft. Beispiel: SELECT name FROM mitarbeiter WHERE gehalt > 5000 AND ort = "Buochs" Tabellen vereinigen SELECT spaltenliste FROM tabelle1 UNION SELECT spaltenliste FROM tabelle2; 2 View anlegen Benutzerrrechte entziehen CREATE VIEW viewname [(spaltenliste)] AS auswahlbedingung [WITH CHECK OPTION]; REVOKE [GRANT OPTION FOR] privilegienliste ON objekt FROM benutzerliste; Trigger anlegen CREATE TRIGGER triggername FOR tabellenname (BEFORE | AFTER} {DELETE | INSERT | UPDATE} AS anweisungen; XML xmlelement() Gibt einen XML-Wert zurück, der ein XML-Element darstellt. Trigger löschen xmlattributes() Erstellt XML-Attribute aus gegebenen Parametern. DROP TRIGGER triggername; xmlparse() Durchsucht ein XML-Dokument und gibt einen XML-Wert zurück. Prozedur anlegen CREATE PROCEDURE prozedurname parametername datentyp DECLARE VARIABLE variablenname datentyp BEGIN anweisungen END; Datenbanken, die XML unterstützen: MySQL, PostgreSQL, DB2, Oracle, SQL Server Prozedur löschen CHARACTER(n) CHARACTER VARYING(n) NATIONAL CHARACTER(n) NATIONAL CHARACTER VARYING(n) CHARACTER LARGE OBJECT(n) Datentypen Zeichenketten: DR0P PROCEDURE prozedurname; Transaktionen anweisungen {COMMIT | ROLLBACK}; Zahlen: INTEGER SMALLINT NUMERIC(n, m) DECIMAL(n, m) REAL DOUBLE PRECISION FLOAT(n) Benutzerrechte vergeben GRANT privilegienliste ON objekt TO benutzerliste [WITH GRANT OPTION]; Beispiel: Zeiten: GRANT DELETE ON TABLE kunde TO userXYZ; DATE TIME TIMESTAMP 3 ER-Modell Relationales Datenmodell Entität: Eigenständige Einheit im Datenmodell (z.B. Produkt) Eine Relation ist die Menge aller Tupel mit den gleichen Attributen. Ein Tupel ist die Menge aller Attribute, die ein Objekt beschreiben. Entitätstyp: Zusammenfassung von Entitäten gleicher Eigenschaften Primärschlüssel: Identifiziert einen Datensatz einer Tabelle eindeutig. Attribut: Die Eigenschaften einer Entität oder einer Beziehung werden durch Attribute beschrieben. Attribute haben jeweils einen Bezeichner. Fremdschlüssel: Die Verknüpfung von Relationen erfolgt über Werte. Ein Fremdschlüssel ist ein Attribut, das sich auf den Wert des Primärschlüssels in einer anderen Relation bezieht. Die Relation mit dem Primärschlüssel wird als Masterrelation, die Relation mit dem Fremdschlüssel als abhängige Relation bezeichnet. Beziehung : Entitäten können in Beziehung gesetzt werden, um deren Verhalten genauer zu beschreiben. Mit der Beschreibung der Beziehung wird die Verknüpfung im Datenmodell vorbereitet. Referenzielle Integrität: Ist die Forderung, dass für einen Wert des Fremdschlüssels immer auch ein Wert in der Masterrelation existiert. Normalisierung Grafische Darstellung von ER-Modellen Als Normalisierung bezeichnet man die Anwendung von Regeln zur Optimierung des Datenmodells und zur Eliminierung von Fehlern. Die Normalisierung erfolgt in mehreren Schritten. Entitäten werden in der UML-Notation als Klasse dargestellt. Die Attribute werden dieser Klasse zugeordnet. Die Beziehungen zwischen Entitäten werden im Modell mit Verbindungslinien dargestellt. Beispiel: 1. Normalform: Eine Relation befindet sich in der 1. Normalform, wenn keine Spalte mit gleichem Inhalt vorliegt (keine Wiederholungen) und Daten in einer Tabelle keine untergeordnete Relation bilden. Die Tabelle muss ein Schlüsselattribut besitzen, der eine Zeile einer Tabelle eindeutig identifiziert. 2. Normalform: 1. Normalform muss erfüllt sein. Jedes Nicht-Schlüsselattribut ist vom Primärschlüssel funktional abhängig. 3. Normalform: 2. Normalform muss erfüllt sein. Zwischen den Nicht-Schlüsselattributen der Tabelle bestehen keine Abhängigkeiten. Quelle: Throll, M., Bartosch, O. (2011) Einstieg in SQL, Galileo Press, Bonn 4