Datenbanksysteme

Kapitel 5: SQL – Data Manipulation Language Prof. Dr. Peter Chamoni

Mercator School of Management Lehrstuhl für Wirtschaftsinformatik, insb. Business Intelligence Dr. Peter Chamoni Prof. Dr. Peter Chamoni -Prof. Datenbanksysteme - Wintersemester 2014/2015

1

Gliederung

1 Grundlagen - Datenbanksysteme 2 SQL – Data Definition Language 3 Datenorganisation 4 Datenintegrität und Transaktionsverwaltung 5 SQL – Data Manipulation Language 6 Neue Konzepte der Datenbanktechnologie

Prof. Dr. Peter Chamoni – Datenbanksysteme

2

Gliederung

5 SQL – Data Manipulation Language 5.1 DML - Befehle für Mutationen

5.2 DML - Abfrage von Daten

Prof. Dr. Peter Chamoni – Datenbanksysteme

3

5.1 DML - Befehle für Mutationen Standardoperationen für Mutationen

•

INSERT Einfügen neuer Tupel in eine Relation

Einfügen von Zeilen in eine existierende Tabelle •

UPDATE Änderung von Tupeln einer Relation Ändern von Zeilen in einer existierenden Tabelle

•

DELETE Löschen von Tupeln aus einer Relation Löschen von Zeilen aus einer existierenden Tabelle

Prof. Dr. Peter Chamoni – Datenbanksysteme

4

5.1 DML - Befehle für Mutationen Einfügen neuer Tupel in eine Relation (I) Syntax

INSERT INTO [(Spaltenliste)] VALUES (Auswahlliste);

Relation Beispiel

Pilot (PilotID, Nachname, Vorname, GKID) Fügen Sie in die Relation Pilot folgenden Personaldatensatz ein. ´Günter, Neumann, PilotID 4444 und GKID 69´ . INSERT INTO Pilot VALUES (4444, 'Neumann', 'Günter', 69);

Prof. Dr. Peter Chamoni – Datenbanksysteme

5

5.1 DML - Befehle für Mutationen Einfügen neuer Tupel in eine Relation (II) Syntax

INSERT INTO [(Spaltenliste)] Anfrage;

Relation Beispiel

Pilot (PilotID, Nachname, Vorname, GKID) Fügen Sie in die Relation Pilot_Neu alle Piloten aus der Relation Pilot ein, die den Gehaltsklassen 60 oder 69 angehören.

INSERT INTO Pilot_Neu SELECT * FROM Pilot WHERE (GKID = 60) OR (GKID = 69);

Prof. Dr. Peter Chamoni – Datenbanksysteme

6

5.1 DML - Befehle für Mutationen Ändern von Tupeln einer Relation Syntax

UPDATE

SET , … [WHERE ];

Relation Beispiel

Pilot (PilotID, Nachname, Vorname, GKID) Ändern Sie die Gehaltsklasse von Michael Meier (PilotID=3333) in 23.

UPDATE Pilot SET GKID = 23 WHERE PilotID = 3333;

Prof. Dr. Peter Chamoni – Datenbanksysteme

7

5.1 DML - Befehle für Mutationen Löschen von Tupeln einer Relation Syntax

DELETE FROM [WHERE ];

Relation Beispiel

Pilot (PilotID, Nachname, Vorname, GKID) Löschen Sie das Tupel des Piloten Günter Neumann (PilotID=4444) aus der Relation Pilot. DELETE FROM Pilot WHERE PilotID = 4444;

Prof. Dr. Peter Chamoni – Datenbanksysteme

8

Gliederung

5 SQL – Data Manipulation Language 5.1 DML - Befehle für Mutationen

5.2 DML - Abfrage von Daten

Prof. Dr. Peter Chamoni – Datenbanksysteme

9

5.2 DML – Abfrage von Daten SQL - Relationenalgebra •

Teil des Relationalen Modells -

Definition von Operationen mit 1 bzw. 2 Eingabetabellen sowie 1 Ausgabetabelle

•

•

Operationen über 1 Tabelle -

Selektion Æ Auswahl bestimmter Zeilen einer Tabelle

-

Projektion Æ Auswahl bestimmter Spalten einer Tabelle

Operationen über 2 Tabellen -

Kartesisches Produkt

-

Verbund / Join

-

Natürlicher Verbund

-

Mengenoperationen (Voraussetzung: Gleichförmigkeit der verknüpften Tabellen) Prof. Dr. Peter Chamoni – Datenbanksysteme

10

5.2 DML – Abfrage von Daten

Optional

Notwendig

Vereinfachtes Standardabfrageschema Projektion / Attributauswahl

SELECT * | Attributliste FROM Tab 1, Tab 2, …, Tab n

Kartesisches Produkt / Join

WHERE

Bedingung

Selektion einzelner Tupel

GROUP BY HAVING

Attributliste Bedingung

Bildung von Tupelgruppen, Aggregation, Selektion von Tupelgruppen

ORDER BY

Attributliste ASC|DESC

Sortierung

Prof. Dr. Peter Chamoni – Datenbanksysteme

5.2 DML – Abfrage von Daten Verarbeitung einer Datenbankabfrage (1) 5 1 2 3 4 6

Prof. Dr. Peter Chamoni – Datenbanksysteme

5.2 DML – Abfrage von Daten Verarbeitung einer Datenbankabfrage (2) 1.

Alle in der Tabellenliste angegebenen Relationen werden über das kartesische Produkt miteinander verknüpft.

2.

Aus dieser verknüpften Relation werden die Tupel ausgewählt, die die angegebene WHERE-Bedingung erfüllen.

3.

Gemäß der Attributliste am Anfang des SELECT-Befehls wird auf das bisherige Resultat eine Projektion auf die gegebenen Attribute vorgenommen.

4.

Nun wird eine Gruppierung gemäß der GROUP-BY-Klausel durchgeführt. Eine Gruppierung fasst dabei mehrere Tupel zu einem Ergebnistupel zusammen, so dass die Ergebnisrelation ggf. weniger Tupel enthält.

5.

Eine nachfolgende HAVING-Klausel führt jetzt auf das Ergebnis der Gruppierung nochmals eine Restriktion auf bestimmte Tupel durch.

6.

Die Ergebnisrelation wird nach den Vorgaben in der Ordnungsliste der ORDER-BYKlausel sortiert. Prof. Dr. Peter Chamoni – Datenbanksysteme

13

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen

•

Mengenoperationen Voraussetzung: Gleichförmigkeit der verknüpften Tabellen Gleiche Anzahl und gleicher Datentyp der Attribute

•

Verbund von Tabellen (JOIN) keine Voraussetzung

Prof. Dr. Peter Chamoni – Datenbanksysteme

14

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen - Mengenoperatoren Mengenoperatoren Mehrere SELECT-Befehle können durch Mengenoperatoren miteinander verknüpft werden: •

UNION (Vereinigung)

•

INTERSECT (Durchschnitt)

•

EXCEPT (Differenz)

Prof. Dr. Peter Chamoni – Datenbanksysteme

15

5.2 DML – Abfrage von Daten Mengenoperatoren – UNION Syntax

SELECT * FROM UNION SELECT * FROM ;

Beispiel

Stellen Sie die Vereinigungsmenge von Flug und Flug2 dar.

SELECT * FROM Flug UNION SELECT * FROM Flug2; Ergebnisrelation

„Flug ‰ Flug 2“

Prof. Dr. Peter Chamoni – Datenbanksysteme

16

5.2 DML – Abfrage von Daten Mengenoperatoren – EXCEPT Syntax

SELECT * FROM EXCEPT SELECT * FROM ;

Beispiel

Stellen Sie die Differenzmenge von Flug und Flug2 dar. SELECT * FROM Flug EXCEPT SELECT * FROM Flug2;

Ergebnisrelation

„Flug \ Flug 2“

In MS ACCESS und der Übungsdatenbank stehen die Mengenoperatoren INTERSECT und EXCEPT nicht zur Verfügung.

Prof. Dr. Peter Chamoni – Datenbanksysteme

17

5.2 DML – Abfrage von Daten Mengenoperatoren – INTERSECT Syntax

SELECT * FROM INTERSECT SELECT * FROM ;

Beispiel

Stellen Sie die Durchschnittsmenge von Flug und Flug2 dar. SELECT * FROM Flug INTERSECT SELECT * FROM Flug2;

Ergebnisrelation

„Flug ˆ Flug 2“

In MS ACCESS und der Übungsdatenbank stehen die Mengenoperatoren INTERSECT und EXCEPT nicht zur Verfügung.

Prof. Dr. Peter Chamoni – Datenbanksysteme

18

5.2 DML – Abfrage von Daten Mengenoperatoren Ausgangsrelationen

Flug2

Flug

Ergebnisrelationen

„Flug ‰ Flug 2“

„Flug \ Flug 2“

„Flug ˆ Flug 2“

Prof. Dr. Peter Chamoni – Datenbanksysteme

5.2 DML – Abfrage von Daten Allgemeine Informationen zu diesem Abschnitt (I) •

Die nachfolgenden Beispiele beziehen sich auf folgendes relationale Datenmodell

Prof. Dr. Peter Chamoni – Datenbanksysteme

19

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen Übersicht möglicher Verbundarten

Inner-Join •

Equi-Join

•

Theta-Join

•

Natural-Join

Left-Outer-Join

Right-Outer-Join

Full-Outer-Join

Cross-Join Prof. Dr. Peter Chamoni – Datenbanksysteme

21

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Cross-Join Cross-Join •

Beim Cross-Join wird das kartesische Produkt über zwei Relationen gebildet, d. h. jeder Datensatz der ersten Relation wird mit jedem Datensatz der zweiten Relation kombiniert.

•

Hinweis Für den praktischen Einsatz ist diese Reinform der Verknüpfungsregel unbrauchbar,

da keinerlei Beziehungen zwischen den Daten beachtet werden. Beispiel:

Stellen Sie das kartesische Produkt über die Relationen Passagier und Buchung dar. SELECT *

FROM Passagier, Buchung;

Prof. Dr. Peter Chamoni – Datenbanksysteme

22

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Inner-Join (I) Inner-Join (1) •

Beim Inner-Join (Equi-Join) wird das kartesische Produkt

über zwei Relationen gebildet, verbunden mit der Prüfung, ob ein oder mehrere gemeinsame Attribute den gleichen Wert haben. (Vergleichsoperator ´=´) •

In der Relationenalgebra wird mit „Verbund“ der „innere

Verbund“ bezeichnet! Beispiel

Innerer Verbund zwischen den beiden Relationen Pilot und Gehaltsklasse. SELECT * FROM Pilot, Gehaltsklasse WHERE Pilot.GKID = Gehaltsklasse.GKID; Prof. Dr. Peter Chamoni – Datenbanksysteme

23

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Inner-Join (II) Inner-Join (2) Umbenennung der Tabellen

SELECT * FROM Pilot AS P, Gehaltsklasse AS G WHERE P.GKID = G.GKID; oder

Änderung der JOIN-Syntax

SELECT * FROM Pilot

INNER JOIN Gehaltsklasse ON Pilot.GKID = Gehaltsklasse.GKID; oder SELECT *

Änderung der JOIN-Syntax

FROM Pilot JOIN Gehaltsklasse ON Pilot.GKID = Gehaltsklasse.GKID;

Prof. Dr. Peter Chamoni – Datenbanksysteme

24

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Inner-Join (III) Inner-Join (3) Beispiel

Stellen Sie den inneren Verbund zwischen den Relationen Passagier und Flug dar.

Relationen

Passagier (PassID, Name, Vorname) Buchung (↑PassID, ↑ FlugNr, ↑ Datum, Kategorie, Preis)

Flug (↑ FlugNr, Datum, Gate, PilotID) SELECT P.PassID, Name, B.FlugNr, B.Datum, F.Gate FROM Passagier AS P, Buchung AS B, Flug AS F WHERE P.PassID = B.PassID AND B.FlugNr = F.FlugNr AND B.Datum = F.Datum; Prof. Dr. Peter Chamoni – Datenbanksysteme

25

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Theta-Join Theta-Join •

Beim Theta-Join wird das kartesische Produkt über zwei Relationen gebildet, wobei für ein oder mehrere gemeinsame Attribute eine Bedingung gilt, für die ein beliebiger Vergleichsoperator verwendet werden kann.

Beachte:

Der Inner-Join ist somit ein Theta-Join, bei dem der Gleichheitsoperator in der Bedingung verwendet wird.

Prof. Dr. Peter Chamoni – Datenbanksysteme

26

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Natural-Join (I) Natural-Join (1) •

Der Natural-Join (Natürlicher Verbund) ist ein Inner-Join, bei dem die Duplikate von identischen Attributen eliminiert werden. Beispiel

Stellen Sie den natürlichen Verbund zwischen den Relationen Flug und Flugstrecke her.

Relationen

Flug (↑ FlugNr, Datum, Gate, PilotID) Flugstrecke (FlugNr, Flugdauer, Abflugort, Ankunftsort)

SELECT Flug.FlugNr, Datum, Gate, PilotID, Flugdauer, Abflugort, Ankunftsort FROM Flug, Flugstrecke WHERE Flug.FlugNr = Flugstrecke.FlugNr; Prof. Dr. Peter Chamoni – Datenbanksysteme

27

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Natural-Join (II) Natural-Join (2) SELECT Flug.*, Flugdauer, Abflugort, Ankunftsort

Beschreibung Ergebnisrelation!

FROM Flug, Flugstrecke WHERE Flug.FlugNr = Flugstrecke.FlugNr; d oder SELECT Flug.*, Flugdauer, Abflugort, Ankunftsort

Änderung der JOIN-Syntax

FROM Flug

INNER JOIN Flugstrecke ON Flug.FlugNr = Flugstrecke.FlugNr; oder SELECT Flug.*, Flugdauer, Abflugort, Ankunftsort

Änderung der JOIN-Syntax

FROM Flug JOIN Flugstrecke ON Flug.FlugNr = Flugstrecke.FlugNr; Prof. Dr. Peter Chamoni – Datenbanksysteme

28

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Natural-Join (III) Natural-Join (3) Beispiel

Stellen Sie den natürlichen Verbund zwischen den Relationen Passagier und Flug dar.

Relationen

Passagier (PassID, Name, Vorname) Buchung (↑PassID, ↑ FlugNr, ↑ Datum, Kategorie, Preis)

Flug (↑ FlugNr, Datum, Gate, PilotID) SELECT P.PassID, Name, Vorname, B.FlugNr, B.Datum, Kategorie, Preis,Gate, PilotID

FROM Passagier AS P, Buchung AS B, Flug AS F WHERE B.PassID = P.PassID AND B.FlugNr = F.FlugNr AND B.Datum = F.Datum; Prof. Dr. Prof Dr Peter Chamoni – Datenbanksysteme

29

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Natural-Join (IV) Natural-Join (4) – Fortsetzung des Beispiels Beispiel

Stellen Sie den natürlichen Verbund zwischen den Relationen Passagier und Flug dar.

Relationen

Passagier (PassID, Name, Vorname) Buchung (↑PassID, ↑ FlugNr, ↑ Datum, Kategorie, Preis)

Flug (↑ FlugNr, Datum, Gate, PilotID) Alternativ

SELECT P.*, B.*, F.*

Beschreibung Ergebnisrelation!

FROM Passagier AS P, Buchung AS B, Flug AS F WHERE B.PassID = P.PassID AND B.FlugNr = F.FlugNr AND B.Datum = F.Datum; Prof. Dr. Peter Chamoni – Datenbanksysteme

30

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Left-Outer-Join Left-Outer-Join •

Beim Left-Outer-Join (Linke Inklusionsverknüpfung) werden von der ersten Relation alle Tupel in die Ergebnismenge aufgenommen. Von der zweiten Relation werden nur die dazugehörigen Tupel übernommen.

•

Die Attributwerte der zweiten Relation bleiben leer (NULL), wenn kein entsprechendes Tupel vorhanden ist. Beispiel

SELECT * FROM Pilot LEFT OUTER JOIN Gehaltsklasse ON Pilot.GKID = Gehaltsklasse.GKID;

Prof. Dr. Peter Chamoni – Datenbanksysteme

31

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Right-Outer-Join Right-Outer-Join

•

Beim Right-Outer-Join (rechte Inklusionsverknüpfung) werden von der zweiten Relation alle Tupel in die Ergebnismenge aufgenommen. Von der ersten Relation werden nur die dazugehörigen Tupel übernommen.

•

Die Attributwerte der ersten Relation bleiben leer (NULL), wenn kein entsprechendes Tupel vorhanden ist. Beispiel

SELECT * FROM Pilot RIGHT OUTER JOIN Gehaltsklasse ON Pilot.GKID = Gehaltsklasse.GKID;

Prof. Dr. Peter Chamoni – Datenbanksysteme

32

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Full-Outer-Join Full-Outer-Join

•

Der Full-Outer-Join ist eine Kombination aus dem Left- und dem Right-Outer-Join. Er kommt dem ursprünglichen Cross-Join am nächsten.

•

Alle Datensätze beider Relationen werden in die Ergebnisrelation übernommen. Passen die Tupel beider Relationen lt. Vergleichsoperator zusammen, so werden diese verbunden.

•

Das Ergebnis der Abfrage entspricht einer nicht normalisierten Speicherung aller Daten in einer Tabelle. Beispiel

SELECT * FROM Pilot FULL OUTER JOIN Gehaltsklasse ON

Pilot.GKID = Gehaltsklasse.GKID;

Prof. Dr. Peter Chamoni – Datenbanksysteme

33

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Self-Join Self-Join •

Der Self-Join (Eigen-Verbund) ist eine Verbundform, bei dem nicht zwei verschiedene Tabellen miteinander verbunden werden, sondern zweimal

dieselbe. Beispiel

Interpretieren Sie das Attribut Abflugort in der Relation Flugstrecke als Basis und stellen Sie die Basis jedes Fluges dar. SELECT A.*, B.Abflugort AS Basis FROM Flugstrecke AS A, Flugstrecke AS B

WHERE A.FlugNr = B.FlugNr; Alternativ

SELECT A.*, B.Abflugort AS Basis FROM Flugstrecke AS A JOIN Flugstrecke AS B ON A.FlugNr = B.FlugNr; Prof. Dr. Peter Chamoni – Datenbanksysteme

34

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Anmerkungen (I) Ein Join ist und bleibt – in Abhängigkeit von der Tabellengröße – eine aufwendige Operation. Ist DB-Performance ein kritischer Faktor, so sollten die verwendeten Joins dahingehend untersucht werden, ob: •

sämtliche in den Kriterien (ON) verwendeten Spalten indiziert sind;

•

der Join tatsächlich nötig ist und nicht aus reiner Bequemlichkeit einer weiteren Abfrage vorgezogen wird;

•

die Tabellenreihenfolge optimal gewählt ist. Als Faustregel gilt: Immer mit der kleinsten Tabelle beginnen. Unterstützt das Datenbanksystem Unterabfragen, so kann es effizienter sein, statt der gesamten Tabelle nur eine durch Kriterien beschränkte Teilmenge der Datensätze im Join zu verwenden.

Prof. Dr. Peter Chamoni – Datenbanksysteme

35

5.2 DML – Abfrage von Daten Abfragen über mehrere Tabellen – Anmerkungen (II) Join vs. View •

Wird eine komplexe Join-Abfrage ständig aufgerufen, empfiehlt sich eher der Einsatz von Views.

•

Ein View kann durchaus auf einer beliebig komplexen SQL-Abfrage basieren. Er stellt das Ergebnis in Form einer Tabelle zur Verfügung. Vorteil:

Das DBMS kann das Ergebnis dieser Abfrage u.U. cachen und somit beim zweiten Zugriff schneller zur Verfügung stellen als ein Join. •

Beim Lesezugriff auf einen View gibt es i.d.R. keine Probleme, hingegen sind Schreiboperationen mit Vorsicht auszuführen.

Prof. Dr. Peter Chamoni – Datenbanksysteme

36