Inhaltsverzeichnis Das Script für die Lehrveranstaltung Datenmanagement wurde im Wintersemester 2007/2008 komplett überarbeitet und neu strukturiert. Wir bitten darum, eventuelle Fehler im Script an Milan Karow ([email protected]) zu melden. Inhaltsverzeichnis 4 Structured Query Language 4.1 SQL als Standard . . . . . . . . . . . . . . . . . . . 4.2 Ziele . . . . . . . . . . . . . . . . . . . . . . . . . . 4.3 Bezeichner . . . . . . . . . . . . . . . . . . . . . . . 4.4 Werte . . . . . . . . . . . . . . . . . . . . . . . . . 4.4.1 Zeichenketten . . . . . . . . . . . . . . . . . 4.4.2 Zahlen . . . . . . . . . . . . . . . . . . . . . 4.4.3 Null-Werte . . . . . . . . . . . . . . . . . . 4.5 Datentypen . . . . . . . . . . . . . . . . . . . . . . 4.5.1 Numerische Datentypen . . . . . . . . . . . 4.5.2 Zeitbezogene Datentypen . . . . . . . . . . 4.5.3 Zeichenkettenbezogene Datentypen . . . . . 4.6 Erstellen von Tabellen (CREATE TABLE) . . . . 4.7 Ändern der Tabellenstruktur (ALTER TABLE) . . 4.8 Entfernen von Tabellen (DROP TABLE) . . . . . 4.9 Einfügen von Daten (INSERT) . . . . . . . . . . . 4.9.1 Direktes Einfügen . . . . . . . . . . . . . . 4.9.2 Einfügen aus anderen Tabellen . . . . . . . 4.10 Abfragen (SELECT) . . . . . . . . . . . . . . . . . 4.10.1 Einfache Abfragen . . . . . . . . . . . . . . 4.10.2 Formulierung von Bedingungen (WHERE) 4.10.3 Sortieren (ORDER BY) . . . . . . . . . . . 4.10.4 JOIN-Syntax . . . . . . . . . . . . . . . . . 4.10.5 Aggregation von Daten . . . . . . . . . . . 4.10.6 Gruppenbildung (GROUP BY) . . . . . . . 4.10.7 Gruppenbedingungen (HAVING) . . . . . . 4.10.8 Reihenfolge bei der Abfragenberechnung . . 4.10.9 Unterabfragen . . . . . . . . . . . . . . . . 4.11 Ändern von Daten (UPDATE) . . . . . . . . . . . 4.12 Löschen von Daten (DELETE) . . . . . . . . . . . 51 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 52 52 52 53 53 54 54 55 55 56 57 58 60 61 61 61 62 62 62 63 65 65 69 70 70 71 71 74 74 4 Structured Query Language 4 Structured Query Language 4.1 SQL als Standard Die Structured Query Language (SQL) ist eine Sprache zur Definition, Abfrage und Manipulation von Daten in relationalen Datenbanken. Sie wurde erstmals vom American National Standards Institute (ANSI) 1986 und ein Jahr später von der International Organisation for Standardization (ISO) standardisiert. 1992 wurde von der ISO die nächste Version des Standards veröffentlicht, die unter den Namen SQL-92 oder SQL2 bekannt ist. Alle aktuellen (relationalen) Datenbankmanagementsysteme ((R)DBMS) halten sich im Wesentlichen an diese Standardversion. Auch dieses Skript orientiert sich an SQL-92. Hersteller von DBMS implementieren in ihren Produkten häufig zusätzliche Funktionalitäten oder weichen geringfügig von dem Standard ab. Eine konkrete produktspezifische Variante der SQL-Sprache wird SQL-Dialekt genannt. In diesem Skript wird aus Gründen der Nachvollziehbarkeit und Praxistauglichkeit der Dialekt des MySQL Community Server 5.0 mit InnoDBEngine verwendet. Selbstverständlich wird hier der Dialekt und Funktionsumfang von MySQL nicht vollständig beschrieben. Weiterführende Informationen findet der interessierte Leser im MySQL Referenzhandbuch.1 Viele durch die unterschiedlichen Hersteller im Laufe der Zeit eingebrachte Erweiterungen zu SQL-92 wurden auch von ISO 1999 und 2003 standardisiert. Einige der Themenbereiche, die dort angesprochen werden, sind Objektorientierung, XML-Einbindung und rekursive Anfragen. Diese Themen sind jedoch nicht Gegenstand dieses Skripts. 4.2 Ziele SQL wurde entwickelt, um die Benutzer von DBMS bei folgenden Aufgaben zu unterstützen: • Erstellen von Datenbank- und Relationsstrukturen • Erstellen von Datenbank- und Relationsstrukturen Durchführung grundlegender Datenmanagementaufgaben, wie z.B. Hinzufügen, Modifikation und Löschen von Daten innerhalb der Datenbank • Ausführen von einfachen und komplexen Anfragen SQL hat eine relativ einfache Syntax und ist semantisch an die englische Sprache angelehnt. Es besteht hauptsächlich aus zwei Teilen: • Data Definition Language (DDL) - zum Definieren von Datenbankstrukturen und Steuerung der Datenzugriffsrechte • Data Manipulation Language (DML) - zum Auslesen und Aktualisieren von Daten SQL ist generell eine deklarative Sprache (im Gegensatz zu imperativen Sprachen, wie den Programmiersprachen C oder Java). Der Benutzer formuliert also im Code, welche Information (was) benötigt wird und nicht wie diese zu extrahieren und berechnen ist. 4.3 Bezeichner Bezeichner werden in SQL benutzt, um Objekte (wie z.B. Datenbanken, Tabellen, Spalten und Alias) innerhalb des DBMS zu identifizieren. Ein Standardbezeichner kann aus großen und kleinen lateinischen Buchstaben, Ziffern und dem Unterstrichzeichen bestehen und muss mit einem 1 MySQL Referenzhandbuch ist in verschiedenen Varianten unter http://dev.mysql.com/doc/ zu finden. 52 4 Structured Query Language Buchstaben anfangen. Darüber hinaus ist es in MySQL möglich, auch andere Zeichen zu verwenden, allerdings muss der Bezeichner in diesem Fall stets in Anführungszeichen gesetzt werden. Das Anführungszeichen ist hier der Backtick (zu finden eine Taste rechts vom ß, mit Umschalttaste). Das Anführungszeichen muss auch benutzt werden, wenn ein reserviertes Wort als Bezeichner genutzt werden soll. Reservierte Wörter sind solche, die in der Sprache SQL eine besondere Bedeutung haben (wie z.B. Befehlsklauseln, Datentypen, Funktionen und Operatoren)2 . Beispiele von Bezeichnerverwendung sind: EineTabelle Tabelle332 ‘Eine schöne Tabelle‘ ‘Lieferant/Artikel‘ MySQL unterstützt Namen, die aus einem oder mehreren Bezeichnern bestehen. Die Bestandteile eines mehrteiligen Namens müssen durch Punkte getrennt werden. Die ersten Bestandteile eines mehrteiligen Namens agieren als Qualifikationsmerkmal, das den Kontext beeinflusst, in dem der endgültige Bezeichner interpretiert wird. Spaltenreferenzierung col name tbl name.col name db name.tbl name.col name Bedeutung Die Spalte col name einer in der Anweisung verwendeten Tabelle hat diesen Namen. Die Spalte col name der Tabelle tbl name aus der Standarddatenbank. Die Spalte col name der Tabelle tbl name aus der Datenbank db name. Das Präfix tbl name oder db name.tbl name muss für eine Spaltenreferenzierung in einer Anweisung nicht angeben werden, sofern die Referenzierung eindeutig ist. Die Unterscheidung von Groß- und Kleinschreibung bei Bezeichnern ist generell vom Betriebssystem abhängig. Unter Microsoft Windows kann man annehmen, dass nicht zwischen Großund Kleinschreibung unterschieden wird. Allerdings sollte innerhalb eines Befehls eine durchgehend einheitliche und konsequente Schreibweise verwendet werden. 4.4 Werte 4.4.1 Zeichenketten Eine Zeichenkette (String) ist eine Abfolge von Zeichen, die in einfache Anführungszeichen gesetzt ist 3 . Innerhalb eines Strings haben bestimmte Sequenzen jeweils eine spezielle Bedeutung. Jede dieser Sequenzen beginnt mit einem Backslash. Dieser wird häufig als Escape-Zeichen bezeichnet. Es stehen unter anderem folgende Escape-Sequenzen zu Verfügung: 2 MySQL gestattet auch die Verwendung bestimmter Schlüsselwörter als Bezeichner ohne Anführungszeichen, da viele Benutzer sie in der Vergangenheit bereits eingesetzt haben. Beispiel: DATE, ENUM, TEXT, TIME 3 Doppelte Anführungszeichen können unter Umständen auch angewendet werden. 53 4 Structured Query Language \’ \“ \n \r \t \n \\ einfaches Anführungszeichen (’) doppeltes Anführungszeichen (“) Zeilenwechsel bzw. -vorschub Absatzschaltung Tabulator Zeilenwechsel bzw. -vorschub Backslash (umgekehrter Schrägstrich) Beispiel: ’Das ist eine Zeichenkette’ ’Ein \n \’String\’ \n ist auch eine Zeichenkette.’ 4.4.2 Zahlen Ganze Zahlen werden als Abfolge von Ziffern dargestellt. Fest- und Gleitkommazahlen verwenden den Punkt als Dezimaltrennzeichen. Bei allen Zahlentypen werden durch ein vorangestelltes Plusoder Minuszeichen negative bzw. positive Werte angezeigt. 4.4.3 Null-Werte Der Wert Null bedeutet keine Daten“. Die Groß-/Kleinschreibung wird bei Null nicht unter” schieden. Ein Null-Wert unterscheidet sich maßgeblich von Werten wie 0 für numerische Typen oder vom Leer-String ’ ’ für String-Typen: • In Spalten, die zum Primärschlüssel gehören, werden Null-Werte automatisch verboten. • Null-Werte können in jeder Spalte manuell verboten werden, in dem der Zusatz Not Null bei der Spaltendefinition verwendet wird. • Der Wert eines Ausdrucks der Form p ⊕ q, wobei ⊕ für ein Element der Operatormenge {<, >, =, <>, +, −, ∗, /} steht, beträgt dann Null, wenn mindestens eines der Argumente p oder q Null ist. Aus diesem Grund exisitieren mit IS NULL und IS NOT NULL in SQL spezielle Vergleichsfunktionen, welche einen Vergleich mit Null ermöglichen. • Die Gruppierungs- und Sortierfunktionen DISTINCT, GROUP BY und ORDER BY betrachten alle Null-Werte als gleich. • SQL benutzt eine dreiwertige Logik mit den Werten True, False und Null. Der logische Wert von zusammengesetzten logischen Ausdrücken wird dabei – wie in folgenden Tabellen dargestellt – bestimmt: p true true true false false false Null Null Null q true false Null true false Null true false Null p∧q true false Null false false false Null false Null p∨q true true true true false Null true Null Null 54 p true false Null NOT p false true Null 4 Structured Query Language • Aggregationsfunktionen wie COUNT(), MIN() und SUM() ignorieren Null-Werte. Eine Ausnahme bildet die Funktion COUNT(*), die Zeilen und nicht einzelne Spaltenwerte zählt. • MySQL behandelt Null-Werte für manche Datentypen abweichend. Wird beispielsweise Null in eine Integer-Spalte eingefügt, welche das AUTO INCREMENT-Attribut gesetzt hat, wird stattdessen die nächste Folgenummer eingesetzt. 4.5 Datentypen Jeder Spalte innerhalb einer Tabelle muss ein Datentyp zugewiesen werden. Es gibt grundsätzlich numerische, zeitbezogene und zeichenkettenbezogene Datentypen. 4.5.1 Numerische Datentypen Bei numerischen Datentypen wird weiter zwischen exakten Datentypen (ganze Zahlen, Festkommazahlen) und gerundeten Datentypen (Gleitkommazahlen) unterschieden. Exakte Datentypen haben eine feste Repräsentation. Sie bestehen aus Ziffern, einem optionalen Komma und einem optionalen Vorzeichen. Alle Berechnungen werden exakt durchgeführt und es gibt keine Rundungsfehler. Gleitkommazahlen dienen dagegen einer approximativen Darstellung reeller Zahlen. Sie stellen einen viel größeren Wertebereich zur Verfügung, haben dafür aber nur eine begrenzte Genauigkeit. Dadurch können bei Berechnungen Rundungsfehler entstehen und einige wichtige mathematische Rechenregeln werden außer Kraft gesetzt. Exakte Datentypen BOOLEAN: Dieser Datentyp dient der Darstellung zweier möglicher Wahrheitswerte (TRUE und FALSE).4 SMALLINT [UNSIGNED]: Repräsentiert einen verkürzten Bereich bereich von Ganzzahlen (Integer). Der vorzeichenbehaftete Bereich liegt zwischen -32768 und 32767. Der vorzeichenlose Bereich liegt zwischen 0 und 65535. INTEGER [UNSIGNED], INT [UNSIGNED]: Repräsentiert einen Ganzzahlenbereich zwischen -2147483648 und 2147483647 (vorzeichenbehaftet), bzw. zwischen 0 und 4294967295 (vorzeichenlos). {DECIMAL | NUMERIC}[(M[,D])] [UNSIGNED]: Exakte Festkommazahl. M ist die Gesamtzahl der Dezimalstellen (Genauigkeit), D die Anzahl der Stellen hinter dem Dezimalpunkt. Der Dezimalpunkt sowie das Zeichen ’-’ (für negative Zahlen) werden bei der Zählung für M nicht berücksichtigt. Wenn D 0 ist, haben die Werte keinen Dezimalpunkt und keine Nachkommastellen. Die maximale Anzahl der Stellen (M) beträgt bei DECIMAL 65, die maximale Anzahl unterstützter Dezimalstellen (D) 30. Wird D weggelassen, wird als Vorgabe 0 verwendet; fehlt die Angabe M, ist 10 der Standardwert. Sofern angegeben, verbietet UNSIGNED negative Werte. Berechnungen in den Grundrechenarten (+, -, *, /) erfolgen bei DECIMAL-Spalten stets mit einer Genauigkeit von 65 Stellen. Gerundete Datentypen 4 MySQL interpretiert BOOLEAN (auch BOOL) als TINYINT(1). Dabei sind TRUE und FALSE Aliase für 1 und 0. Aus diesem Grund wird BOOLEAN den numerischen Datentypen zugeordnet. 55 4 Structured Query Language FLOAT[(M,D)] [UNSIGNED] Kleine Gleitkommazahl (mit einfacher Genauigkeit). Darstellbar sind Werte aus der Menge [−3, 40 · 1038 ; −1, 18 · 10−38 ] ∪ {0} ∪ [1, 18 · 10−38 ; 3, 40 · 1038 ]. M ist die Gesamtzahl von Dezimalstellen, D die Anzahl der Stellen hinter dem Dezimalpunkt. Wenn M und D nicht angegeben werden, werden die Werte in diesem Rahmen gespeichert, was hardwareseitig unterstützt wird. Eine Gleitkommazahl mit einfacher Genauigkeit ist auf bis zu sieben Dezimalstellen genau. Sofern angegeben, verbietet UNSIGNED negative Werte. {DOUBLE PRECISION| DOUBLE | REAL}[(M,D)] [UNSIGNED] Gleitkommazahl normaler Größe (mit doppelter Genauigkeit). Darstellbar sind Werte aus der Menge [−1, 80 · 10308 ; −2, 23 · 10−308 ] ∪ {0} ∪ [2, 23 · 10−308 ; 1, 80 · 10308 ]. M ist die Gesamtzahl von Dezimalstellen, D die Anzahl der Stellen hinter dem Dezimalpunkt. Wenn M und D nicht angegeben werden, werden die Werte im Rahmen dessen gespeichert, was hardwareseitig unterstützt wird. Eine Gleitkommazahl mit einfacher Genauigkeit ist auf bis zu 15 Dezimalstellen genau. Sofern angegeben, verbietet UNSIGNED negative Werte. 4.5.2 Zeitbezogene Datentypen Zur Darstellung zeitbezogener Daten dienen die Datentypen DATETIME, DATE und TIME. DATETIME DATETIME dient der Repräsentation von Zeitpunkten, welche sowohl das Datum als auch die Uhrzeit umfasst. Der unterstützte Bereich liegt zwischen 1000-01-01 00:00:00 und 9999-12-31 23:59:59. Die Werte können u.a. in einem der folgenden Formate angegeben werden: • als String im Format ’YYYY-MM-DD HH:MM:SS’ • als String im Format ’YYYY-MM-DD’; für die Uhrzeit wird 00:00:00 angenommen • als Zahl in den Formaten YYYYMMDDHHMMSS oder YYMMDDHHMMSS • als Zahl in den Formaten YYYYMMDD oder YYMMDD; für die Uhrzeit wird 00:00:00 angenommen • als Ergebnis einer Funktion, die einen in entsprechenden Zeitwert zurückgibt, z.B. NOW() oder CURRENT DATE. DATE Dient der Repräsentation von Zeitpunkten, die nur durch ein Datum beschrieben werden. Der unterstützte Bereich liegt zwischen 1000-01-01 und 9999-12-31. Die Werte können u.a. in einem der folgenden Formate angegeben werden: • als String im Format ’YYYY-MM-DD’ • als Zahl in den Formaten YYYYMMDD oder YYMMDD • als Ergebnis einer Funktion, die einen in entsprechenden Zeitwert zurückgibt, z.B. NOW() oder CURRENT DATE. TIME Dient der Repräsentation von Zeitwerten, die entweder einen Zeitpunkt oder einen Zeitintervall in der Form HH:MM:SS beschreiben. Unterstützt wird der Bereich zwischen -838:59:59 und 838:59:59. Die Werte können u.a. in einem der folgenden Formate angegeben werden: • als String im Format ’D HH:MM:SS’, ’HH:MM:SS’, ’HH:MM’, ’D HH:MM’, ’D HH’ oder ’SS’. Dabei steht D für Tage und kann einen Wert zwischen 0 und 34 haben 56 4 Structured Query Language • als Zahl im Format HHMMSS • als Ergebnis einer Funktion, die einen Zeitwert zurückgibt, z.B. CURRENT TIME. 4.5.3 Zeichenkettenbezogene Datentypen Zeichenketten (Strings) sind Folgen von Zeichen, die grundsätzlich mit einem bestimmten Zeichensatz5 kodiert sind. Es stehen folgende Datentypen zur Verfügung: CHAR[(M)] Die Länge einer CHAR-Spalte ist auf den beim Anlegen der Tabelle deklarierten Wert M beschränkt. Dieser kann zwischen 0 und 255 liegen. Wenn CHAR-Werte gespeichert werden, werden sie nach rechts mit Leerzeichen bis auf die angegebene Länge aufgefüllt. Beim Abrufen von CHAR-Werten werden die am Ende stehenden Leerzeichen entfernt. Wird kein M angegeben, wird standardmäßig die Länge von einem Zeichen (M=1) angenommen. VARCHAR[(M)] Werte in VARCHAR-Spalten sind Strings variabler Länge M. Diese kann zwischen 0 und 65.535 liegen. Im Gegensatz zu CHAR werden VARCHAR-Werte nur mit so vielen Zeichen wie erforderlich zuzüglich 1-2 Bytes, welche die Länge angeben. Die folgende Tabelle veranschaulicht die Unterschiede zwischen den Typen CHAR und VARCHAR. Hierzu wird das jeweilige Ergebnis der Speicherung verschiedener String-Werte in CHAR(4)- und VARCHAR(4)-Spalten angezeigt: Wert ’’ ’ab’ ’abcd’ ’abcdefgh’ CHAR(4) ’ ’ ’ab ’ ’abcd’ ’abcd’ Speicherbedarf 4 Byte 4 Byte 4 Byte 4 Byte VARCHAR(4) ’’ ’ab’ ’abcd’ ’abcd’ Speicherbedarf 1 Byte 3 Byte 5 Byte 5 Byte TEXT Repräsentiert lange Zeichenketten, die mit einem bestimmten Zeichensatz kodiert sind. Die maximale Länge der Zeichenkette beträgt dabei 216 − 1. Wie viel Speicherplatz tatsächlich in Anspruch genommen wird, hängt von dem verwendeten Zeichensatz ab. Beispielsweise ist bei Verwendung des utf8-Unicode-Zeichensatzes darauf zu achten, dass einige Zeichen mehr als ein Byte Speicherplatz benötigen. BLOB Die Abkürzung BLOB steht für Binary Large Object und dient der Speicherung langer Zeichenketten, die keinen Zeichensatz zugewiesen haben. In diesem Fall spricht man von binären Strings (Byte-Strings), da ein Zeichen einem Byte gleichgestellt wird. Die Sortierung basiert auf den numerischen Werten der Bytes in den Spaltenwerten. In manchen Fällen kann es wünschenswert sein, Binärdaten – wie bspw. Mediendateien – in BLOB-Spalten zu speichern. ENUM(’value1’ [,’value2’] ...) ENUM6 (Enumeration) ist ein Datentyp, der nur solche String-Werte erlaubt, die beim Erstellen der Tabelle explizit in der Spaltendefinition aufgelistet wurden. Als Werte kommen unter bestimmten Umständen auch der Leer-String (’’) oder Null in Frage. Jeder Wert in der Auflistung bekommt einen mit 1 beginnenden nummerierten Index. Der Indexwert des als Fehlerwert verwendeten Leer-Strings ’’ ist 0. Es kann also bspw. folgende SELECT-Anweisung verwendet werden, um Datensätze zu ermitteln, bei denen ungültige ENUM-Werte zugewiesen wurden: 5 6 Ein Zeichensatz ist eine Zuordnung zwischen alphanumerischen Zeichen und Zahlen. Der Datentyp ENUM ist MySQL-spezifisch und ist nicht Bestandteil des ISO-Standards. 57 4 Structured Query Language SELECT ∗ FROM tbl_name WHERE enum_col = 0 ; Eine Spalte, die als ENUM(’ja’, ’nein’, ’vielleicht’) definiert ist, kann jeden der nachfolgend angegebenen Werte annehmen. Auch die Indizes der einzelnen Werte werden in der Tabelle angezeigt: Wert Null ’’ ’ja’ ’nein’ ’vielleicht’ Index Null 0 1 2 3 Eine Auflistung der erlaubten Werte darf maximal 65.535 Elemente enthalten. 4.6 Erstellen von Tabellen (CREATE TABLE) Relationen werden in einer relationalen Datenbank in Tabellen gespeichert. Um eine Tabelle zu erstellen und ihre Struktur zu definieren, wird der Befehl CREATE TABLE mit folgender Syntax verwendet: CREATE TABLE tbl_name ( create_definition , . . . ) −−c r e a t e d e f i n i t i o n : col_name data_type [ NOT NULL | NULL ] [ DEFAULT default_value ] [ AUTO_INCREMENT ] [ UNIQUE | PRIMARY KEY ] | PRIMARY KEY ( col_name , . . . ) | UNIQUE ( col_name , . . . ) | FOREIGN KEY ( col_name , . . . ) REFERENCES tbl_name ( col_name , . . . ) [ ON DELETE reference_option ] [ ON UPDATE reference_option ] −−r e f e r e n c e o p t i o n : CASCADE | SET NULL | NO ACTION Nach dem Schlüsselwort CREATE TABLE folgt der Bezeichner für die neue Tabelle und (in Klammern gesetzt) eine Liste von Spalten, gefolgt von zusätzlichen optionalen Definitionen von Primär- oder Fremdschlüsseln sowie UNIQUE-Indizes. Ein UNIQUE-Index verbietet wiederholende Werte innerhalb einer Spalte oder Spalten. Beispiel 1 CREATE TABLE Kategorie ( KategorieNr INT AUTO_INCREMENT PRIMARY KEY , Kategoriename VARCHAR ( 2 0 ) NOT NULL UNIQUE , Beschreibung TEXT , Abbildung BLOB ); In diesem Beispiel wird eine Tabelle mit der Bezeichnung Kategorie erstellt. Die neue Tabelle hat vier Spalten. Die Spalte KategorieNr ist vom Typ INT und wird zum Primärschlüssel 58 4 Structured Query Language deklariert. Der Zusatz AUTO INCREMENT7 schaltet eine zusätzliche Funktionalität ein, die beim Einfügen neuer Datensätze dafür sorgt, dass die Kategorienummer automatisch fortlaufend nummeriert wird. Der Kategoriename ist ein String mit variabler Länge, jedoch nicht größer als 20 Zeichen. Der Zusatz NOT NULL bewirkt, dass das Feld erforderlich ist, d.h. es dürfen keine Null-Werte eingetragen werden. Auf dieser Spalte wird auch ein UNIQUE-Index deklariert, der wiederholende Werte verbietet. Die Beschreibung ist eine große nicht-binäre Zeichenkette. In der Spalte Abbildung werden dagegen digitale Grafiken der Artikel in Form binärer Zeichenketten (Byte-Strings) gespeichert.8 Die letzten beiden Felder sind optional (da kein NOT NULL angegeben wurde). Beispiel 2 CREATE TABLE Artikel ( ArtikelNr INT NOT NULL AUTO_INCREMENT , Artikelname VARCHAR ( 4 0 ) DEFAULT NULL , KategorieNr INT NOT NULL , Einzelpreis DECIMAL ( 1 9 , 4 ) DEFAULT NULL , Lagerbestand SMALLINT DEFAULT NULL , PRIMARY KEY ( ArtikelNr ) , FOREIGN KEY ( KategorieNr ) REFERENCES Kategorie ( KategorieNr ) ON DELETE NO ACTION ON UPDATE NO ACTION ); In Beispiel 2 werden mit dem Zusatz DEFAULT Standardwerte für bestimmte Spalten definiert. Sie werden beim Einfügen neuer Datensätze dann angewendet, wenn kein expliziter Wert für diese Spalte angegeben wird. Zur Primärschlüssel-Definition wird hier eine andere Syntax als zuvor verwendet. Die Definition geschieht gesondert nach der Angabe aller Spalten mit dem Schlüsselwort PRIMARY KEY, gefolgt von einer Liste der Schlüsselspalten in Klammern. Diese Syntaxvariante muss verwendet werden, wenn der Primärschlüssel aus mehr als einer Spalte besteht. Nach der Definition des Primärschlüssels wird ein Fremdschlüssel definiert, was für sogenannte referentielle Integrität sorgt. In diesem Beispiel referenziert die Spalte KategorieNr die gleichnamige Spalte aus der Elterntabelle Kategorie. Es besteht noch zusätzlich die Möglichkeit, bestimmte Regeln zu definieren, die das Systemverhalten im Hinblick auf die FremdschlüsselBeziehung steuern. Es kann angegeben werden, was bei einem Versuch passieren soll, einen referenzierten Wert aus der Elterntabelle zu löschen (ON DELETE) oder einen solchen Wert zu ändern (ON UPDATE). Dabei gibt es grundsätzlich drei Möglichkeiten: 7 8 AUTO INCREMENT ist eine MySQL-Erweiterung zum Standard-SQL. Zu beachten ist, dass das Speichern von Grafikdaten in der Datenbank nicht unbedingt sinnvoll sein muss. In der Regel werden in Datenbanken lediglich Referenzen auf die Dateien im Dateisystem gespeichert, um die Größe der Datenbank im Rahmen zu halten. 59 4 Structured Query Language Option NO ACTION (Voreinstellung) CASCADE SET NULL Wirkung Ändern/Löschen referenzierter Datensätze in der Elterntabelle nicht möglich. Änderungen in der referenzierten Tabelle werden in dem referenzierenden Datensatz automatisch übernommen. Wird ein referenzierter Datensatz in der Elterntabelle gelöscht, so werden alle ihn referenzierenden Datensätze aus der Tabelle automatisch gelöscht. Wird ein referenzierter Datensatz in der Elterntabelle gelöscht oder geändert, so werden alle ihn referenzierende Werte aus der Tabelle mit Null-Werten ersetzt. Diese Option ergibt nur dann Sinn, wenn die Spaltendefinition Null-Werte zulässt. Wenn keine Option zur referentiellen Integrität angegeben wird, wird standardmäßig NO ACTION angewendet. 4.7 Ändern der Tabellenstruktur (ALTER TABLE) Wenn die Struktur einer bereits angelegten Tabelle geändert werden soll, wird dazu der Befehl ALTER TABLE mit folgender Syntax verwendet: ALTER TABLE tbl_name alter_specification [ , alter_specification ] ... −− a l t e r s p e c i f i c a t i o n : ADD [ COLUMN ] column_definition [ FIRST | AFTER col_name ] | ADD PRIMARY KEY ( col_name , . . . ) | ADD UNIQUE ( col_name , . . . ) | ADD FOREIGN KEY ( col_name , . . . ) REFERENCES tbl_name ( col_name , . . . ) [ ON DELETE reference_option ] [ ON UPDATE reference_option ] | CHANGE [ COLUMN ] old_col_name column_definition [ FIRST | AFTER col_name ] | DROP [ COLUMN ] col_name | DROP PRIMARY KEY | DROP FOREIGN KEY fk_symbol | DROP INDEX index_name | RENAME TO new_tbl_name Nach dem Schlüsselwort ALTER TABLE und dem Tabellennamen folgt eine Liste von durch Kommata getrennten Änderungsanweisungen. Beispiel ALTER TABLE Artikel ADD COLUMN Artikelbeschreibung TEXT AFTER Artikelname , CHANGE COLUMN Lagerbestand Lagerbestand SMALLINT NOT NULL , DROP COLUMN Einzelpreis ; Im Beispiel wird die Struktur der Tabelle Artikel geändert, indem eine neue Spalte Artikelbeschreibung des Typs TEXT hinzugefügt wird. Mit der Option FIRST bzw. AFTER col name hat man die Möglichkeit, die Position der neuen Spalte in der Tabelle anzugeben. Mit CHANGE 60 4 Structured Query Language COLUMN werden bestehende Spaltendefinitionen geändert. Hierbei ist anzumerken, dass direkt nach dem Namen der zu ändernden Spalte der neue Spaltenname und weitere Bestandteile einer vollständigen Spaltendefinition folgen. Wird der Spaltenname nicht geändert, so muss er zweimal hintereinander angegeben werden. Die Anweisung DROP COLUMN ermöglicht das Löschen von Spalten. Des Weiteren besteht die Möglichkeit Primär-, Fremdschlüssel und UNIQUE-Indices zu definieren sowie diese zu löschen. In den zwei letzteren Fällen muss der Name der entsprechenden Bedingung angegeben werden, der mit dem Befehl SHOW CREATE TABLE tbl name herauszufinden ist. Mit der Anweisung RENAME TO besteht die Möglichkeit, eine Tabelle umzubenennen. 4.8 Entfernen von Tabellen (DROP TABLE) Das Schema einer Relation und alle bereits eingegebenen Daten können mit dem DROP TABLEBefehl gelöscht werden. Somit wird eine Tabelle aus der Datenbank vollständig und endgültig entfernt: DROP TABLE tbl_name Beispiel DROP TABLE Kunde ; Im Beispiel wird die Tabelle Kunde vollständig aus dem System entfernt. Dabei gehen alle darin enthaltenen Daten verloren! 4.9 Einfügen von Daten (INSERT) Das Einfügen von Datensätzen in eine Tabelle geschieht unter Verwendung des INSERT-Befehls. Der Vorgang kann entweder durch direkte Angabe der einzufügenden Datensätze oder durch Einfügen von Datensätzen aus einer anderen Tabelle geschehen. 4.9.1 Direktes Einfügen Syntax INSERT INTO tbl_name [ ( col_name , . . . ) ] VALUES ( { expr | DEFAULT } , . . . ) , ( . . . ) , . . . Werden Spaltennamen (col name,...) angegeben, so werden Werte nur in die entsprechenden Spalten der Tabelle eingefügt. Dies ist nur möglich, wenn die nicht angegebenen Spalten einen Standardwert haben. Der Standardwert kann entweder explizit durch die DEFAULT-Option angegeben werden oder es wird bei Spalten, die Null-Werte zulassen, Null als Standardwert angenommen. Werden keine Spaltennamen angegeben, so bezieht sich das INSERT auf alle Spalten der Tabelle. Soll in eine Spalte, die nicht als NOT NULL definiert wurde, ein Null-Wert eingefügt werden, so geschieht das durch Eingabe des Wertes Null. Wenn mehrere Datensätze mit einem Befehl hinzugefügt werden sollen, so können die einzelnen VALUES-Gruppen durch Kommata getrennt angegeben werden. 61 4 Structured Query Language 4.9.2 Einfügen aus anderen Tabellen Syntax INSERT INTO tbl_name [ ( col_name , . . . ) ] SELECT . . . Mit dieser Syntaxvariante besteht die Möglichkeit, sich die hinzufügenden Werte von einer beliebigen SELECT-Anfrage liefern zu lassen. Natürlich müssen dabei die Spalten des Abfrageergebnisses den angegebenen (oder allen - wenn kein (col name,...) vorhanden) Spalten hinsichtlich der Tabellendefinition entsprechen. D.h. die Tabelle muss das SELECT-Ergebnis aufnehmen können. 4.10 Abfragen (SELECT) Um die in den Relationen gespeicherten Daten abzurufen, werden an das DBMS Abfragen (Queries) gestellt. Nach der Bearbeitung der Abfrage, liefert das System ein Ergebnis zurück. Das Ergebnis hat die Form einer Tabelle, d.h. es besteht aus benannten Spalten und in Zeilen organisierten Datensätzen. Jeder Spalte ist dabei auch ein bestimmter Typ zugewiesen. Die Durchführung der Abfragen ermöglicht der SELECT-Befehl. Syntax SELECT [ ALL | DISTINCT ] select_expr , . . . [ FROM table_references [ WHERE where_condition ] [ GROUP BY { col_name | expr } , . . . ] [ HAVING where_condition ] [ ORDER BY { col_name | expr } , . . . ] .. 4.10.1 Einfache Abfragen Die Grundform einer Abfrage in SQL wird durch die ’SELECT...FROM...WHERE’-Klausel gebildet. Hinter dem Schlüsselwort SELECT werden die Ergebnisspalten spezifiziert, die ausgegeben werden sollen (Projektion). Hinter dem Schlüsselwort FROM müssen die Namen aller Tabellen angegeben werden, deren Spalten ausgegeben werden sollen oder zur Formulierung der Bedingungen benötigt werden. Auf das Schlüsselwort WHERE folgend können Bedingungen angegeben werden, denen die Elemente der beteiligten Relationen genügen müssen, um Bestandteil der Lösungsmenge zu werden (Selektion). Da es möglich ist, dass nach Projektion und Selektion in der Lösungsmenge gleiche Datensätze mehrmals vorkommen, bietet SQL die Möglichkeit, durch die Angabe von DISTINCT hinter SELECT nur verschiedene Datensätze anzuzeigen. Wird ALL (oder nichts) anstatt DISTINCT angegeben, so bleibt die Lösungsmenge unverändert. Beispiel 1 SELECT Artikelname , Einzelpreis FROM Artikel WHERE ArtikelNr =1234; 62 4 Structured Query Language In Beispiel 1 werden solche Datensätze in der Tabelle Artikel gesucht, die den Wert 1234 in der Spalte ArtikelNr haben. Da in diesem Fall ArtikelNr Primärschlüssel ist, dürfen sich dessen Werte nicht wiederholen, es wird also nach genau einem Datensatz gesucht. Dabei wird hier nicht der vollständige Datensatz zurückgeliefert, sondern nur die Werte der Spalten Artikelname und Einzelpreis. Wird als select expr, ... ein * (Sternchen) angegeben, so werden alle verfügbaren Spalten des Ergebnisses zurückgeliefert. Sollen alle Datensätze (Zeilen) einer Tabelle ohne Einschränkung ausgegeben werden, kann auf die WHERE-Klausel verzichtet werden.9 In Beispiel 2 wird eine Abfrage gezeigt, die uneingeschränkt alle Daten aus der Tabelle Artikel zurück gibt. Beispiel 2 SELECT ∗ FROM Artikel ; select expr kann auch ein Ausdruck sein, der Tabellenspalten verwendet oder sogar ein solcher, der ohne Referenzierung einer Tabelle berechnet wird. Es kann sich als praktisch erweisen, dem Ausdruck einen Namen (sogenannten Alias) zu geben. Diese Möglichkeiten werden in Beispiel 3 gezeigt. Beispiel 3 SELECT Einzelpreis ∗2 AS ‘ Doppelter Preis ‘ , 2∗2+3 AS Berechnung FROM Artikel ; Auf das Schlüsselwort AS kann verzichtet werden. Wenn nur Tabellen-unabhängige Ausdrücke berechnet werden, kann die FROM-Klausel ebenfalls weggelassen werden. Beispiel 4 SELECT 1 Eins ; Das Ergebnis der Abfrage aus dem Beispiel 4 hat eine Spalte mit dem Namen Eins und einen Datensatz. Es wird einfach die Zahl 1 ausgegeben. 4.10.2 Formulierung von Bedingungen (WHERE) Hinter dem Schlüsselwort WHERE können Bedingungen in Form von Ausdrücken angegeben werden. Diese bestimmen die vorzunehmende Selektion und können unterschiedlicher Art sein. Zum einen sind einfache Vergleiche möglich, zum anderen besteht die Möglichkeit, neue Abfragen (sogenannte Unterabfragen oder Subqueries) in die Bedingungen zu integrieren. Solche Ausdrücke sind auch an anderen Stellen zulässig, wie z.B. hinter dem SELECT-Schlüsselwort oder (in beschränkter Form) in der HAVING-Klausel. In den folgenden Beispielen wird von der Relation Kunde mit den Attributen KundenCode, Firma und PLZ (Postleitzahl des Kundenwohnorts) ausgegangen. Für die Formulierung von Bedingungen gibt es in SQL unter anderem folgende Möglichkeiten: • einfacher Vergleich (=, <, >, <>, <=, >=) – WHERE Firma=’BAKER AG ’ – WHERE PLZ<>’48161 ’ 9 Es kann auch eine Bedingung gesetzt werden, die immer erfühlt ist, wie z.B. WHERE 1=1. Dies kann z.B. bei dynamischer SQL-Generierung praktisch sein. 63 4 Structured Query Language • Verknüpfung von Bedingungen mit AND, OR oder NOT – WHERE ( PLZ=’48161 ’ OR PLZ=’48149 ’ ) AND Firma<>’ERCIS ’ • der BETWEEN-Operator zur Definition eines Suchbereiches – WHERE Firma BETWEEN ’BAKER AG ’ AND ’ERCIS ’} Statt mit BETWEEN zu arbeiten kann auch die Ober- und Untergrenze des Bereichs separat überprüft werden: – WHERE Firma>=’BAKER AG ’ AND Firma<=’ERCIS ’ • der LIKE-Operator – WHERE Firma LIKE ’B_ker ’} Mit dem LIKE-Operator kann eine Ähnlichkeitsabfrage für alphanumerische Konstanten durchgeführt werden. Als Wildcards dienen der Unterstrich ( ) als Platzhalter für ein Zeichen und das Prozentzeichen (%) als Platzhalter für n Zeichen (n >= 0). Beispiel SELECT ∗ FROM Kunde WHERE Firma LIKE ’M%’ ; Es werden alle Kunden ausgegeben, deren Namen mit ’M’ beginnen. • die Operatoren IS NULL und IS NOT NULL – WHERE Firma IS NULL – WHERE PLZ IS NOT NULL • der IN-Operator – WHERE PLZ IN ( ’48149 ’ , ’48161 ’ , ’48143 ’ ) Es werden solche Datensätze in die Ergebnismenge übernommen, die einem der Einträge in der Liste entsprechen. – Als Liste für den IN-Operator kann auch eine SELECT-Query dienen: WHERE PLZ IN ( SELECT PLZ FROM Postleitzahlen WHERE Bundesland=’NRW ’ ) • der EXISTS-Operator in Verbindung mit einer Unterabfrage – WHERE EXISTS ( SELECT . . . FROM . . . WHERE . . . ) Diese Bedingung prüft, ob es für einen Datensatz ein Ergebnis in der Subquery gibt. • verschiedene String-Funktionen, z.B. CONCAT() – SELECT CONCAT ( ’Kundennummer : ’ , KundenCode , ’, Firma : ’ , Firma ) AS ‘ Kundencode und Firma ‘ FROM Kunde ; CONCAT(str1,str2,...) gibt den String zurück, der aus der Verkettung der Argumente entsteht. • verschiedene mathematische Funktionen, z.B. ROUND() 64 4 Structured Query Language – SELECT Artikelname , ROUND ( Einzelpreis ) FROM Artikel WHERE ROUND ( Einzelpreis ) >20; ROUND(X) gibt das Argument X gerundet auf den nächstgelegenen Integer zurück. • verschiedene Datumsfunktionen, z.B. YEAR() – SELECT BestellNr , YEAR ( Bestelldatum ) FROM Bestellung WHERE YEAR ( Bestelldatum )=2001; YEAR(date) gibt für ein Datum im Bereich zwischen 1000 und 9999 das Jahr als Zahl zurück. 4.10.3 Sortieren (ORDER BY) Wird eine Abfrage durch eine ORDER BY-Klausel abgeschlossen, so bewirkt dies eine Sortierung der Lösungsmenge anhand der Werte einer oder mehrerer vorgegebener Spalten. Für jeden Spaltennamen hinter ORDER BY kann angegeben werden, ob anhand dieser Spalte aufsteigend (ASC) oder absteigend (DESC) sortiert werden soll. Wird weder ASC noch DESC angegeben, so wird automatisch ASC, also aufsteigende Sortierung, angenommen. In dieser Klausel ist es möglich, die in der SELECT-Klausel definierten Aliase zu verwenden. Beispiel 6 SELECT Firma , PLZ FROM Kunde ORDER BY PLZ DESC , Firma ; In Beispiel 6 wird eine Kundenliste nach Postleitzahlen absteigend geordnet ausgegeben. Wohnen mehrere Kunden im gleichen Ort, werden sie namentlich aufsteigend geordnet ausgegeben. 4.10.4 JOIN-Syntax Da sich Informationen in stark normalisierten Datenbanken auf verschiedene Tabellen verteilen, ist es bei der Abfragen meist notwendig, diese wieder zu verknüpfen. Das wird durch einen Verbund (Join) erreicht, der die Tabellen temporär (d.h. für die Dauer der Anfrage) verbindet. Das Ergebnis eines Joins ist wie eine neue vollständige Tabelle anzusehen. Als Beispiel sind folgende zwei Tabellen gegeben: Tabelle Servicepunkt SPID VERTRID ---------- ---------1 1 2 1 3 2 4 NULL BEZEICH ------Punkt 1 Punkt 2 Punkt 3 Punkt 4 Tabelle Vertriebsregion VERTRID SUPERVERTRID NAME ---------- ------------ ---------- 65 4 Structured Query Language 1 2 3 NULL Region 1 1 Region 2 1 Region 3 Wenn mehrere Tabellen verknüpft werden, kann es vorkommen, dass sich gleichnamige Spalten in verschiedenen Tabellen befinden. Werden solche Spalten in einem Befehl referenziert, müssen qualifizierte Namen der Form tbl name.col name benutzt werden, um Eindeutigkeit zu gewährleisten. In solchen Fällen ist es oft nützlich, den Tabellen (kürzere) Aliasnamen zu vergeben. Dies geschieht durch Angabe des Aliases mit dem optionalen Wort AS hinter dem Tabellennamen in der FROM-Klausel, wie im folgenden Beispiel. . . . FROM servicepunkt AS alias_s , vertriebsregion alias_v CROSS JOIN Ein Cross Join bildet das kartesische Produkt (=Kreuzprodukt) zweier Tabellen. Es wird jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle kombiniert. Eine praktische Anwendung gibt es dafür jedoch eher selten. Es ist folgende Syntax zugelassen: SELECT ∗ FROM servicepunkt s CROSS JOIN vertriebsregion v ; #oder : SELECT ∗ FROM servicepunkt s , vertriebsregion v ; Ausgabe: SPID VERTRID BEZEICH VERTRID SUPERVERTRID NAME ---------- ---------- ------- ---------- ------------ -------1 1 Punkt 1 1 NULL Region 1 1 1 Punkt 1 2 1 Region 2 1 1 Punkt 1 3 1 Region 3 2 1 Punkt 2 1 NULL Region 1 2 1 Punkt 2 2 1 Region 2 2 1 Punkt 2 3 1 Region 3 3 2 Punkt 3 1 NULL Region 1 3 2 Punkt 3 2 1 Region 2 3 2 Punkt 3 3 1 Region 3 4 NULL Punkt 4 1 NULL Region 1 4 NULL Punkt 4 2 1 Region 2 4 NULL Punkt 4 3 1 Region 3 INNER JOIN Ein Inner Join verbindet genau die Zeilen von zwei Tabellen miteinander, für die eine explizit angegebene Bedingung erfüllt wird. Sollen mehrere Bedingungen gleichzeitig angewendet werden, werden diese mittels logischer Operatoren (AND, OR) verknüpft. Folgende Syntax ist zulässig: SELECT ∗ FROM servicepunkt s INNER JOIN vertriebsregion v ON s . VERTRID=v . VERTRID ; 66 4 Structured Query Language Die Bedingung im Teil hinter ON muss keine Äquivalenz sein - auch bspw. “größer als“oder “kleiner als“sind als Bedingung zulässig. Alternativ kann der Join über die WHERE-Klausel erfolgen: SELECT ∗ FROM servicepunkt s , vertriebsregion v WHERE s . VERTRID=v . VERTRID ; Ausgabe: SPID VERTRID BEZEICH VERTRID SUPERVERTRID NAME ---------- ---------- ------- ---------- ------------ -------1 1 Punkt 1 1 NULL Region 1 2 1 Punkt 2 1 NULL Region 1 3 2 Punkt 3 2 1 Region 2 Wenn als Bedingung die Äquivalenz bzgl. eines oder mehrerer Attribute definiert werden soll (also bspw. “’kunde.kundenID=bestellung.kundenID’) und die entsprechenden Spalten in beiden Tabellen den gleichen Namen und Datentyp besitzen, kann auch folgende Syntax verwendet werden: SELECT ∗ FROM servicepunkt s INNER JOIN vertriebsregion v USING ( VERTRID ) ; mit der Ausgabe: VERTRID SPID BEZEICH SUPERVERTRID NAME ---------- ---------- ------- ------------ -------1 1 Punkt 1 NULL Region 1 1 2 Punkt 2 NULL Region 1 2 3 Punkt 3 1 Region 2 Wie zu sehen ist, besteht der Unterschied darin, dass die (gleichnamigen) Spalten nicht doppelt zurückgegeben werden. NATURAL JOIN Wenn die JOIN-Bedingungen Äquivalenzen sind und die entsprechenden Spalten in beiden Tabellen den gleichen Namen und Datentyp besitzen und es keine anderen Spalten (also solche, die nicht Bestandteil der Bedingung des Joins sein sollen) gibt, die in beiden Tabellen die den gleichen Namen und Datentyp besitzen, so kann der obige INNER JOIN mit USING durch einen NATURAL JOIN ersetzt werden: SELECT ∗ FROM servicepunkt s NATURAL JOIN vertriebsregion v ; ergibt VERTRID SPID BEZEICH SUPERVERTRID NAME ---------- ---------- ------- ------------ -------1 1 Punkt 1 NULL Region 1 1 2 Punkt 2 NULL Region 1 2 3 Punkt 3 1 Region 2 67 4 Structured Query Language LEFT OUTER JOIN = LEFT JOIN Außer INNER JOINs gibt es auch so genannte OUTER JOINs. Ein LEFT (OUTER) JOIN kombiniert jede Zeile der ersten Tabelle mit den Zeilen der zweiten Tabelle, die die Bedingungen erfüllen oder mit Null-Werten, wenn keine passenden Zeilen der zweiten Tabelle vorhanden sind. So gibt es im Beispiel eine Vertriebsregion, in der sich keine Servicepunkte befinden. Wird eine Liste aller Regionen benötigt, auf der zusätzlich entsprechende Servicepunkte annotiert sind, so ist folgende Anfrage hilfreich: SELECT ∗ FROM vertriebsregion v LEFT JOIN servicepunkt s ON v . VERTRID=s . VERTRID ; Ausgabe: VERTRID SUPERVERTRID NAME SPID VERTRID BEZEICH ---------- ------------ -------- ---------- ---------- ------1 NULL Region 1 1 1 Punkt 1 1 NULL Region 1 2 1 Punkt 2 2 1 Region 2 3 2 Punkt 3 3 1 Region 3 NULL NULL NULL Sind nur die Regionen zu ermitteln, in der sich keine Servicepunkte befinden, liefert folgende Anfrage das gewünschte Ergebnis: SELECT ∗ FROM vertriebsregion v LEFT JOIN servicepunkt s ON v . VERTRID=s . VERTRID WHERE s . VERTRID IS NULL ; Ausgabe: VERTRID SUPERVERTRID NAME SPID VERTRID BEZEICH ---------- ------------ -------- ---------- ---------- ------3 1 Region 3 NULL NULL NULL RIGHT OUTER JOIN = RIGHT JOIN Ein RIGHT (OUTER) JOIN funktioniert genauso wie LEFT (OUTER) JOIN, nur werden hier alle Zeilen der zweiten (rechten) Tabelle mit passenden Zeilen der ersten (linken) Tabelle oder Null-Werten kombiniert. FULL OUTER JOIN = FULL JOIN10 Ein vollständiger Außenverbund kombiniert die Funktionsweise der beiden LEFT und RIGHT JOINs. Es werden die Zeilen der linken Tabelle mit denen der rechten verknüpft, die die angegebenen Bedingungen erfüllen. Außerdem werden die verbleibenden Zeilen sowohl der linken als auch der rechten mit Null-Werten verknüpft. Folgende Anfrage erstellt uns eine Liste von allen Vertriebsregionen und allen Servicepunkten mit ihrer Zuordnung, sofern vorhanden. 10 FULL (OUTER) JOIN wird von MySQL nicht unterstützt. 68 4 Structured Query Language SELECT ∗ FROM vertriebsregion v FULL JOIN servicepunkt s ON v . VERTRID=s . VERTRID ; Ausgabe: VERTRID SUPERVERTRID NAME SPID VERTRID BEZEICH ---------- ------------ -------- ---------- ---------- ------1 Region 1 1 1 Punkt 1 1 Region 1 2 1 Punkt 2 2 1 Region 2 3 2 Punkt 3 3 1 Region 3 NULL NULL NULL NULL NULL NULL 4 NULL Punkt 4 JOINS von mehreren Tabellen Wenn ein Verbund von mehr als zwei Tabellen gebildet wird, werden die Tabellen normalerweise von links nach rechts verknüpft. Da dies bei Outer Joins eine Rolle spielen kann, kann man diese Reihenfolge durch Setzen von Klammern verändern. Beim Ausführen von komplexeren Anfragen sorgt das DBMS (der Optimierer) dafür, dass der Vorgang möglichst kurz dauert. Es werden beispielsweise zuerst die Bedingungen betrachtet, die die Menge der Zeilen aus einer (oder aus mehreren) Tabelle(n) am meisten begrenzen. Dies hat zur Folge, dass später wesentlich weniger Zeilen in der Join-Phase verbunden werden. Eine Ausnahme von dieser Vorgehensweise bilden hier z.B. die Bedingungen, die sich im HAVINGTeil befinden. Diese werden erst nach der Verbunderstellung und Gruppenbildung angewendet. Aus diesem Grund sollten nur solche Ausdrücke in den HAVING-Teil gesetzt werden, die nicht innerhalb der WHERE-Klausel formuliert werden können, da sonst Performance-Nachteile entstehen. 4.10.5 Aggregation von Daten In manchen Situationen sind nicht die Werte der einzelnen Datensätze von Interesse, sondern solche, die eine Gruppe von Datensätzen zusammenfassend beschreiben. Um sie zu ermitteln, gibt es in SQL die folgenden fünf speziellen Aggregationsfunktionen: COUNT([DISTINCT] expr) COUNT(*) MIN(expr) MAX(expr) SUM(expr) AVG(expr) Ermittelt die Anzahl der gültigen Werte innerhalb einer Gruppe. Wird die Option DISTINCT verwendet, werden gleiche Werte nur einmal gezählt. Ermittelt die Anzahl der Datensätze im Ergebnis. Ermittelt den kleinsten Wert einer Gruppe von Werten. Ermittelt den größten Wert einer Gruppe von Werten. Ermittelt die Summe der Werte einer Gruppe von Werten. Ermittelt das arithmetische Mittel einer Gruppe von Werten. Grundsätzlich gilt die Regel, dass Null-Werte durch die Aggregationsfunktionen ignoriert werden. Eine Ausnahme ist COUNT(*), das alle Datensätze in der Gruppe – unabhängig der darin enthaltenen Werte – zählt. Beispiel 7 69 4 Structured Query Language SELECT MAX ( Einzelpreis ) FROM Artikel ; In Beispiel 7 wird der höchste Preis aller Artikel gesucht. Beispiel 8 SELECT COUNT ( DISTINCT PLZ ) FROM Kunde ; In Beispiel 8 wird die Anzahl unterschiedlicher Postleitzahlen aller Kunden gesucht. 4.10.6 Gruppenbildung (GROUP BY) Mit der GROUP BY-Klausel können die Zeilen eines (Zwischen-)Ergebnisses anhand der Werte einer oder mehrerer Spalten gruppiert werden. Die Gruppierung erfolgt so, dass die Spalten, nach denen die Gruppierung erfolgt, keine doppelten Werte mehr enthalten. Hinter GROUP BY erfolgt die Angabe eines oder mehrerer Spaltennamen. Wenn Gruppierung angewendet wird, dürfen hinter SELECT nur solche Spalten stehen, über die gruppiert wird, oder Ausdrücke, die genau einen Wert pro Gruppe liefern (s. Aggregationsfunktionen). Der Sinn hinter der Gruppenbildung liegt in der Anwendung von Aggregationsfunktionen, die nach Ausführung genau einen Wert pro Gruppe liefern. Beispiel 9 SELECT Land , COUNT ( ∗ ) FROM Kunde GROUP BY Land ; In Beispiel 9 wird eine Liste von Ländern, in denen Kunden angesiedelt sind, mit der Anzahl von Kunden in dem jeweiligen Land ausgegeben. 4.10.7 Gruppenbedingungen (HAVING) Durch die HAVING-Komponente erfolgt nach der Bildung der Gruppen mittels GROUP BY eine Auswahl der Gruppen, die den durch die HAVING-Bedingung gegebenen Anforderungen genügen. Der Unterschied zur Auswahl durch die Bedingungen hinter WHERE liegt darin, dass Tabellenzeilen, die den Bedingungen hinter WHERE nicht genügen, bei der Gruppenbildung durch GROUP BY nicht berücksichtigt werden, während durch die HAVING-Bedingung bereits gebildete Gruppen aus der Lösungsmenge ausgeschlossen werden können. In der HAVING-Bedingung werden im Allgemeinen Aggregationsfunktionen angewendet. Beispiel 10 SELECT PLZ , COUNT ( Firma ) as KundenProPLZ FROM Kunde WHERE NOT ( ( Firma=’Baker AG ’ ) AND ( PLZ=’48149 ’ ) ) GROUP BY PLZ HAVING KundenProPLZ >1; In Beispiel 10 werden Kunden, die die gleiche Postleitzahl haben, zusammengruppiert. Pro Postleitzahl wird anschließend die Anzahl der dazugehörigen Kunden ausgegeben. Bei der Abfrage wird der Kunde Maier aus 48149 (Münster) nicht berücksichtigt. 70 4 Structured Query Language 4.10.8 Reihenfolge bei der Abfragenberechnung 3.2.8 Reihenfolge bei der Abfragenberechnung Die Bearbeitung von Abfragen wird konzeptionell immer in einer bestimmten Reihenfolge abge- Die Bearbeitung vonauf Abfragen konzeptionell immer in einer bestimmten Reihenfolge wickelt. Diese wird folgenderwird Abbildung dargestellt: abgewickelt. Diese wird auf folgender Abbildung dargestellt. FROM: WHERE: Definiert die Ausgangstabellen Selektiert die Reihen, die der Bedingung genügen GROUP BY: Gruppiert Reihen auf der Basis gleicher Werte in Spalten HAVING: Selektiert Gruppen, die der Bedingung genügen SELECT: Selektiert Spalten ORDER BY: Sortiert Reihen auf der Basis von Spalten Zuerst werden die zu verknüpfenden Tabellen verbunden. Anschließend werden die WHEREZuerst werden die zu verknüpfenden Tabellen verbunden. Anschließend werden die WHEREBedingungen angewendet, die das Ergebnis beschränken. Danach werden Gruppen gebildet, angewendet, die das Ergebnis beschränken. werden Gruppen gebildet, in in Bedingungen dem Datensätze mit gleichen Wertekombinationen derDanach GROUP BY -Spalten zusammengefasst dem Datensätze mit-Klausel gleichen Wertekombinationen der dass GROUPunerwünschte BY-Spalten zusammengefasst werwerden. Die HAVING bewirkt als nächstes, Gruppen aussortiert den. Die HAVING-Klausel bewirkt dass unerwünschte werden. werden. Danach werden nur die als im nächstes, SELECT-Teil ausgewähltenGruppen Spalten aussortiert aus dem Ergebnis Danach werden nur die im SELECT-Teil ausgewählten Spalten aus dem Ergebnis ausgewählt und ausgewählt und anschließend erfolgt eine Sortierung anhand angegebener Kriterien. anschließend erfolgt eine Sortierung anhand angegebener Kriterien. 3.2.9 Unterabfragen 4.10.9 Unterabfragen Innerhalb eines SELECT-Befehls können sich weitere Abfragen befinden (d. h. es werden Innerhalb eines SELECT-Befehls können sich weitere Abfragen befinden (d. h. es werden mehrere mehrere SELECT...FROM...WHERE-Statements ineinandergeschachtelt). Bei den inneren SELECT...FROM...WHERE-Statements ineinandergeschachtelt). Bei den inneren Abfragen spricht Abfragen spricht man von Unterabfragen (Subqueries). Die äußere Abfrage wird dabei als man von Unterabfragen (Subqueries). Die äußere Abfrage wird dabei als Hauptabfrage bezeichHauptabfrage bezeichnet.selbst Jedekann Unterabfrage selbst kann als Hauptabfrage werden, net. Jede Unterabfrage als Hauptabfrage angesehen werden, wennangesehen sie Unterabfragen wenn sie Unterabfragen besitzt. Subqueries erlauben es, komplexe Abfragen strukturiert aufzubauen und eigenständige Lösungsteile zu isolieren. 71 23 4 Structured Query Language besitzt. Subqueries erlauben es, komplexe Abfragen strukturiert aufzubauen und eigenständige Lösungsteile zu isolieren. Grundsätzlich gibt es vier Arten von Unterabfragen bezüglich des Ergebnistyps: • Eine Skalarunterabfrage liefert genau eine Spalte und genau eine Zeile, d.h. einen einzelnen Wert zurück. Grundsätzlich kann eine solche Unterabfrage an allen Stellen verwendet werden, an denen einzelne Werte zulässig sind (Beispiele 11, 12 und 18 ). • Eine Spaltenunterabfrage liefert genau eine Spalte, aber mehrere Zeilen zurück. Diese Unterabfragen werden vor allem dort verwendet, wo ein Vergleich mit einer Liste von Werten durchgeführt wird, z.B. mit dem IN-Operator (Beispiel 13 ). • Eine Zeilenunterabfrage liefert mehrere Spalten, aber genau eine Zeile zurück. Sie findet in einfachen Vergleichen Anwendung, in denen mehrere Spalten involviert sind (zeilenbasierter Vergleich) (Beispiele 14 und 15 ). • Eine Tabellenunterabfrage liefert mehr als eine Spalte und mehr als eine Zeile zurück. Sie kann zum einen in einem zeilenbasierten Vergleich mit einer Liste von Zeilen (z.B. mit dem IN-Operator) eingesetzt werden, zum anderen können solche Unterabfragen in der FROM-Klausel an Stelle von Tabellen verwendet werden (Beispiele 16 und 17 ). Beispiel 11 SELECT ArtikelNr , LieferantenNr , Einkaufspreis FROM Liefernachweis WHERE Einkaufspreis=(SELECT MAX ( Einkaufspreis ) FROM Liefernachweis ) ; In Beispiel 11 werden Artikelnummer, Lieferantennummer und Preis von dem am teuersten eingekauften Artikel ausgegeben. Gibt es mehrere solche Artikel, werden alle ausgegeben. Hier wird eine Skalarunterabfrage im WHERE-Teil angewendet. Wie zu sehen ist, werden Unterabfragen stets in Klammern gesetzt. Beispiel 12 SELECT ArtikelNr , Einkaufspreis − ( SELECT AVG ( Einkaufspreis ) FROM Liefernachweis ) AS ‘ Abweichung vom Durchschnittspreis ‘ FROM Liefernachweis ; In Beispiel 12 werden für jeden Artikel seine Nummer und die Abweichung vom Durchschnittspreis aller Artikel ausgegeben. Es ist ein Beispiel einer Skalarunterabfrage im SELECT-Teil. Beispiel 13 SELECT ∗ FROM Artikel WHERE ArtikelNr IN ( SELECT ArtikelNr FROM Liefernachweis WHERE Einkaufspreis <10); Beispiel 13 stellt die Verwendung einer Spaltenunterabfrage mit dem IN-Operator dar. In der Unterabfrage werden zuerst die Nummern von denjenigen Artikeln ermittelt, deren Einkaufspreis weniger als 10 beträgt. Somit wird eine Liste von Werten gebildet. In der Hauptabfrage werden alle Informationen zu den Artikeln ausgegeben, deren Nummern sich in der Liste befinden. Beispiel 14 72 4 Structured Query Language SELECT ArtikelNr FROM Artikel WHERE ( Einzelpreis , Lagerbestand )=( SELECT MAX ( Einzelpreis ) , MIN ( Lagerbestand ) FROM Artikel ) ; In Beispiel 14 wird eine Zeilenunterabfrage in einem einfachen Vergleich verwendet. In der Unterabfrage werden zuerst der maximale Einzelpreis und der minimale Lagerbestand von allen Artikeln ermittelt. In der Hauptabfrage wird dann überprüft, ob es Artikel gibt, die gleichzeitig den maximalen Preis und minimalen Lagerbestand haben. Sind solche vorhanden, werden ihre Nummern ausgegeben. Die Unterabfrage liefert hier genau eine Zeile mit zwei Spalten. Der Vergleich erfolgt zeilenbasiert, da Einzelpreis und Lagerbestand hinter dem durch die Klammern zu einer Zeile zusammengefasst werden. Beispiel 14 ist somit semantisch äquivalent zum Beispiel 15: Beispiel 15 SELECT ArtikelNr FROM Artikel WHERE Einzelpreis=(SELECT MAX ( Einzelpreis ) FROM Artikel ) AND Lagerbestand=(SELECT MIN ( Lagerbestand ) FROM Artikel ) ; Beispiel 16 SELECT ∗ FROM Bestellposition WHERE ( ArtikelNr , LieferantenNr ) IN ( SELECT ArtikelNr , LieferantenNr FROM Liefernachweis WHERE Einkaufspreis <10 ); Beispiel 16 stellt die Verwendung einer Tabellenunterabfrage mit dem IN-Operator dar. In der Unterabfrage werden zuerst die Kombinationen von Artikeln und Lieferanten ermittelt, denen ein Einkaufspreis von weniger als 10 entspricht. Somit wird eine Liste von Zeilen gebildet. In der Hauptabfrage werden dann die Bestellpositionen ausgegeben, die den Kombinationen von Artikeln und Lieferanten aus der Liste entsprechen. Beispiel 18 SELECT ArtikelNr , LieferantenNr , Einkaufspreis FROM Liefernachweis ln1 WHERE Einkaufspreis=( SELECT MAX ( ln2 . Einkaufspreis ) FROM Liefernachweis ln2 WHERE ln1 . ArtikelNr = ln2 . ArtikelNr ); Im Beispiel 18 wird für jeden Artikel der Lieferant (oder Lieferanten) gesucht, der diesen Artikel zum höchsten Preis verkauft. Hier wird diese Aufgabe mit einer korrelierten Unterabfrage gelöst. Korrelierte Unterabfragen sind solche, die Tabellen aus der Hauptabfrage referenzieren. In einer Unterabfrage können alle Tabellen (oder Tabellenaliasse) der übergeordneten Abfragen verwendet werden. Im Beispiel 18 referenzieren beide Aliasse ln1 und ln2 die gleiche Tabelle Liefernachweis. Allerdings verwendet die Unterabfrage auch den Alias ln1, welcher in der Hauptabfrage vergeben wird. 73 4 Structured Query Language Korrelierte Unterabfragen sind aber mit Vorsicht zu verwenden, da sie oft sehr ineffizient und recht langsam sind. Insbesondere muss grundsätzlich eine solche Unterabfrage für jede Zeile der Hauptabfrage einzeln berechnet werden. Das Umschreiben der Abfrage als Join kann die Leistung unter Umständen verbessern. 4.11 Ändern von Daten (UPDATE) Das Ändern bestehender Datensätze geschieht mit dem UPDATE-Befehl. Es können die Werte einer oder mehrerer Spalten gleichzeitig geändert werden. Während des Änderungsvorgangs sind die alten Werte zugänglich; so ist es beispielsweise in der Tabelle Artikel möglich, die Preise aller Artikel um 1 zu erhöhen, ohne die Preise explizit angeben zu müssen. Es können Bedingungen an die Zeilen der Tabelle gestellt werden, für die Änderungen stattfinden sollen. Änderungen finden immer für alle Zeilen statt, die den Bedingungen hinter dem Schlüsselwort WHERE genügen. Wird keine WHERE-Klausel verwendet, so werden alle Datensätze der Tabelle geändert! Syntax: UPDATE tbl_name SET col_name1=expr1 [ , col_name2=expr2 . . . ] [ WHERE where_condition ] Beispiel 1 UPDATE Artikel SET Einzelpreis=Einzelpreis +1; In Beispiel 1 werden die Preise aller Artikel um 1 erhöht. Beispiel 2 UPDATE Kunde SET PLZ = ’48149 ’ , Straße = ’Leonardo - Campus 3’ , Region = ’Münsterland ’ , Ort = ’Münster ’ , Land = ’Deutschland ’ WHERE Firma = ’ERCIS ’ ; Im Beispiel 2 werden über mehrere Spalten verteilte Adressdaten des Kunden ERCIS aktualisiert. 4.12 Löschen von Daten (DELETE) Das Löschen von Datensätzen geschieht durch den DELETE-Befehl. Es können nur ganze Zeilen gelöscht werden. Es kann eine Bedingung angegeben werden, die die zu löschenden Zeilen erfüllen müssen. Wird keine Bedingung angegeben, so werden alle Datensätze aus einer Tabelle gelöscht! Die Tabellenstruktur bleibt dabei noch erhalten, kann jedoch mit dem Befehl DROP TABLE entfernt werden. Syntax: DELETE FROM tbl_name [ WHERE where_condition ] 74 4 Structured Query Language Beispiel 1 DELETE FROM Kunde WHERE Ort=’Münster ’ ; In Beispiel 1 werden alle Kunden, die in Münster ansässig sind, gelöscht. Beispiel 2 DELETE FROM Artikel ; In Beispiel 2 werden sämtliche Datensätze aus der Tabelle Artikel entfernt. Die Tabellenstruktur bleibt dabei noch erhalten, sodass neue Datensätze hinzugefügt werden können. 75