Mit einer Anweisungen kann mehr - Friedrich-Schiller

5.3 Datenänderung/-zugriff mit SQL (DML)
Hinweis:
- DML-Anweisungen sind mengenorientiert
- Mit einer Anweisungen kann mehr als ein Tupel eingefügt, geändert,
gelöscht oder gelesen werden
Benutzungs- und Effizienzvorteile (Client-Server-Szenario!)
Anweisungen für Datenänderung:
- INSERT
- UPDATE
- DELETE
Anweisungen für Datenzugriff:
- SELECT
- CREATE / DROP VIEW (Konzeptuelle Ebene)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 168
5.3.1 INSERT
Befehl zum Einfügen von Datensätzen in eine Tabelle
- Erweiterung des Entityset eines Entitytyp (siehe Folie 57)
- Ausprägungsformen
• Einfügen von Tupeln als Konstanten
• Einfügen von Tupeln als Abfrageergebnis auf einer (anderen)
Relationen (SFW-Klausel, siehe Abschnitt 5.3.4 SELECT)
Voraussetzung für aufgeführte Beispiele (Test in MS Access):
- Tabelle Buch, Tabelle Ausleihe
- Anweisungen von Folie 152:
CREATE TABLE Buch (
InvNr INTEGER NOT NULL,
Titel VARCHAR(30),
ISBN CHAR(5),
Autor VARCHAR(40),
PRIMARY KEY (InvNr))
Datenbanksysteme
CREATE TABLE Ausleihe (
InvNr INTEGER NOT NULL,
Name VARCHAR(20),
PRIMARY KEY (InvNr),
FOREIGN KEY (InvNr)
REFERENCES Buch(InvNr))
Friedrich-Schiller-Universität Jena
Seite 169
5.3.1 INSERT – Tupel als Konstanten
Syntax:
INSERT
INTO Basisrelationenname [(Spaltenname_1, ..., Spaltenname_k)]
VALUES (Wert_11, ..., Wert_k1) [, ..., (Wert_1n, ..., Wert_kn)]
Beispiel 1: Einfügen eines Tupels in Relation "Buch"
INSERT INTO Buch (InvNr, Titel, ISBN, Autor)
VALUES (1201,'Objektbanken','3-111','Heuer')
Beispiel 2: Einfügen eines unvollständigen Tupels in Relation "Buch"
INSERT INTO Buch (InvNr, ISBN)
VALUES (4867,'3-221')
Werte für "Autor" und "Titel" werden auf NULL gesetzt!
Beispiel 3: Einfügen eines Tupels in Relation "Buch" ohne Inventarnummer
INSERT INTO Buch (Titel, ISBN, Autor)
VALUES ('Modula-4','3-222','Wirth')
NULL-Wert für InvNr nicht erlaubt (Primärschlüssel!) Fehlermeldung
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 170
5.3.1 INSERT – Tupel als Konstanten
Beispiel 4: Weglassen der Attributliste bei der INTO-Klausel
INSERT INTO Buch
VALUES (4710,'Modula-4','3-222','Wirth')
- Werte der VALUES-Klausel werden den Attributen in der Reihenfolge
zugeordnet, wie sie beim CREATE-Statement aufgeführt waren
• 4711 → InvNr, 'Modula-4' → Titel, '3-222' → ISBN, 'Wirth' → Autor
• Funktioniert nur, weil SQL Tabellenspalten nicht als (ungeordnete!)
Menge auffasst Abweichung von der "reinen" Lehre!
Beispiel 5: Einfügen eines Tupels in Relation "Ausleihe"
INSERT INTO Ausleihe
VALUES (0815,'Liebisch')
Anweisung scheitert, da keine zugehöriger Primärschlüsselwert (InvNr =
0815) in Relation "Buch" existiert (Fremdschlüssel-Beziehung verletzt!)
Beispiel 6: Einfügen von zwei Tupeln in Relation "Ausleihe"
INSERT INTO Ausleihe (InvNr, Name)
VALUES (1201,'Liebisch'), (4710,'Küspert')
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 171
5.3.1 INSERT – Tupel als SFW-Klausel
Syntax:
INSERT
INTO Basisrelationenname [(Spaltenname_1, ..., Spaltenname_k)]
<lesende SQL-Anfrage>
Wirkung:
- Ergebnis der lesenden SQL-Anfrage (SFW: Select-From-Where) wird in
die angegebene Relation eingefügt
• Abgeschlossenheit: SELECT-Anfrage liefert wieder eine Menge
• Mengenorientiertheit: INSERT-Operation kann Mengen verarbeiten
- Voraussetzung sind strukturell gleiche Relationen
• Identische Anzahl von Attributen
• Kompatibilität der Attribut-Wertebereiche
• Reihenfolge der Attribute wichtig oder explizite Benennung
- SQL-Anfrage kann beliebig komplex sein
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 172
5.3.1 INSERT – Tupel als SFW-Klausel
Beispiel 7: Verdopplung der Tupel in Relation "Ausleihe"
INSERT INTO Ausleihe
SELECT * FROM Ausleihe
Selektion aller Tupel (= *) aus Relation "Ausleihe" als Menge
Einfügen der Tupelmenge in Relation "Ausleihe"
Anweisung scheitert wegen Primärschlüssel-Verletzung
Beispiel 8: Kopieren von Inhalten verschiedener Tabellen
- Angenommen, es gibt zusätzlich eine Relation Ausleihe_alt mit
denjenigen Büchern, die schon länger als 2 Jahre ausgeliehen sind
- Inhalt dieser Relation soll nun, damit er nicht in Vergessenheit gerät,
wieder in Relation Ausleihe übernommen werden
INSERT INTO Ausleihe (InvNr, Name)
SELECT InvNr_alt, Name_alt FROM Ausleihe_Alt
Anweisung fügt alle Tupel aus Relation "Ausleihe_alt" in Relation
"Ausleihe" ein (Kopieren)
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 173
5.3.2 UPDATE
Syntax:
UPDATE Basisrelationenname
SET Spaltenname_1 = Ausdruck_1 [, Spaltenname_k = Ausdruck_k]
[WHERE Bedingung]
Wirkung:
- In allen Tupeln von Basisrelationenname,
- welche die (optionale) Bedingung erfüllen,
- werden die Attributwerte der Spaltenname_i wie angegeben ersetzt
- Ausdruck_i: Konstante, Attribut, SFW-Ausdruck (Subselect)
Beispiel 1: Der Titel des Buches mit der InvNr = 1201 ('Objektbanken') soll in
'Objektdatenbanken' geändert werden
UPDATE Buch
SET Titel = 'Objektdatenbanken'
WHERE InvNr = 1201
Anweisung ändert immer nur ein einzelnes Tupel, weil InvNr als
Primärschlüssel definiert ist
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 174
5.3.2 UPDATE
Beispiel 2: Alternative Anweisung für Beispiel 1
UPDATE Buch
SET Titel = 'Objektdatenbanken'
WHERE Titel = 'Objektbanken'
Anweisung ändert datenabhängig ein oder mehrere Tupel, je nach dem
wieviele Tupel den Titel 'Objektbanken' haben
Beispiel 3: Für alle Bücher mit InvNr > 1000 soll der Autor auf 'Dr. Seltsam'
gesetzt werden
UPDATE Buch
SET Autor = 'Dr. Seltsam'
WHERE InvNr > 1000
Explizites Mengen-Update
Beispiel 4: Gib allen Angestellten der Relation Angest(PNr, Name, Gehalt, ...)
eine Gehaltserhöhung um 1000,- EUR
UPDATE Angest
SET Gehalt = Gehalt + 1000
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 175
5.3.2 UPDATE
Beispiel 5: Für alle Bücher soll die InvNr auf 9999 gesetzt werden
UPDATE Buch
SET InvNr = 9999
WHERE-Klausel nicht benötigt, da alle Tupel der Relation zu ändern sind
Operation verletzt Konsistenz ("Duplicate key")
• Wird vom DBVS nicht ausgeführt / ungeschehen gemacht
• Nicht trivial für DBVS (Fehlerbehandlungsmaßnahme)
• Diskussion des Ausführungsergebnis in MS Access (Atomarität!)
Beispiel 6: Das Buch mit InvNr = 1201 soll den gleichen Titel bekommen wie
das Buch mit InvNr = 4710
UPDATE Buch
SET Titel = (SELECT Titel FROM Buch WHERE InvNr = 4710)
WHERE InvNr = 1201
Subselect holt den Titel zum Buch mit InvNr = 4710
Ergebnis wird als Titel für das Buch mit InvNr = 1201 gesetzt
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 176
5.3.3 DELETE
Syntax:
DELETE
FROM Basisrelationenname
[WHERE Bedingung]
Wirkung:
- Alle Tupel in Basisrelationenname, welche die (optionale) Bedingung
erfüllen, werden gelöscht
- Relationsschema bleibt unverändert (im Gegensatz zu DROP TABLE)
Beispiel 1:
- Das von Hr. Meyer ausgeliehene Buch mit InvNr = 4711 wurde
zurückgegeben und soll aus der Relation "Ausleihe" gelöscht werden
DELETE
FROM Ausleihe
WHERE InvNr = 4711
- Alternative Bedingung (Name = 'Meyer') löscht eventuell mehr
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 177
5.3.3 DELETE
Beispiel 2:
- Die Bibliothek hat die Hoffnung aufgegeben, die ausgeliehenen Bücher
jemals zurückzuerhalten
- Löschungs des gesamten Inhalts der Relation "Ausleihe"
DELETE
FROM Ausleihe
- Eigentlich müssten auch noch die entsprechenden Bücher aus Relation
"Buch" gelöscht werden, da "unwiderruflich verschwunden"
Beispiel 3: Kann eine DELETE-Anweisung zur Integritätsverletzung führen
(analog zu "Duplikate key" beim UPDATE)?
DELETE
FROM Buch
WHERE Titel = 'Objektdatenbanken'
Verletzung der Fremdschlüsselbeziehung zwischen Ausleihe und Buch
Fremdschlüsselwert 4710 für Ausleihe.InvNr würde ins "Leere zeigen"
Zurückweisung der Operation
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 178