DML (2) – Daten speichern

(Einführung in SQL: DML (2) - Daten speichern)

(Einführung in SQL: DML (2) - Daten speichern)

Dieser Teilbereich der Data Manipulation Language (DML) behandelt die Befehle, mit denen die Inhalte der Datenbank geändert werden: Neuaufnahme, Änderung, Löschung.

Bitte beachten Sie, dass mit den Befehlen INSERT, UPDATE, DELETE (fast) immer nur Daten genau einer Tabelle bearbeitet werden können – anders als beim SELECT-Befehl, der Daten mehrerer Tabellen zusammenfassen kann.

INSERT – Daten einfügen

Der INSERT-Befehl dient dem Erstellen von neuen Datensätzen. Es gibt ihn in zwei Versionen – zum einen durch die Angabe einzelner Werte, zum anderen mit Hilfe eines SELECT-Befehls.

In beiden Versionen müssen die Datentypen der Werte zu den Datentypen der Spalten passen. Man sollte nicht versuchen, einer Spalte, die eine Zahl erwartet, eine Zeichenkette zuzuweisen. Man wird nur selten das Ergebnis erhalten, welches man erwartet. Das Kapitel Funktionen erläutert im Abschnitt „Konvertierungen“ Möglichkeiten, wie Werte implizit (also automatisch) oder explizit durch CAST oder CONVERT angepasst werden können.

Einzeln mit VALUES

Wenn ein einzelner Datensatz durch die Angabe seiner Werte gespeichert werden soll, gilt folgende Syntax:

 INSERT INTO <tabellenname>
         [ ( <spaltenliste> ) ]
    VALUES ( <werteliste> )
    ;

Zu diesem Befehl gehören folgende Angaben:

• INSERT als Name des Befehls, INTO als feststehender Begriff
• <Tabellenname> als Name der Tabelle, die diesen Datensatz erhalten soll
• in Klammern ( ) gesetzt eine Liste von Spalten (Feldnamen), denen Werte zugewiesen werden
• der Begriff VALUES als Hinweis darauf, dass einzelne Werte angegeben werden
• in Klammern ( ) gesetzt eine Liste von Werten, die in den entsprechenden Spalten gespeichert werden sollen

Wenn eine Liste von Spalten fehlt, bedeutet das, dass alle Spalten dieser Tabelle in der Reihenfolge der Struktur mit Werten versehen werden müssen.

Aufgabe

So wird (wie im Skript der Beispieldatenbank) ein Eintrag in der Tabelle Mitarbeiter gespeichert:

INSERT INTO Mitarbeiter
       ( Personalnummer, Name, Vorname,
         Telefon, Email, Raum, Ist_Leiter, Abteilung_ID, Geburtsdatum )
VALUES ( '20002', 'Schmitz', 'Michael',
         '0231/5556187', 'michael.schmitz@unserefirma.de', '212', 'N', 2, '1959-08-25' );

Wenn Sie diesen Befehl mit der Tabellenstruktur vergleichen, werden Sie feststellen:

• Die Spalte ID fehlt. Dieser Wert wird von der Datenbank automatisch vergeben.
• Die Spalte Mobil fehlt. In dieser Spalte wird folglich ein NULL-Wert gespeichert.
• Die Reihenfolge der Spalten weicht von der Tabellendefinition ab; das ist also durchaus möglich.

In der Beschreibung der Beispieldatenbank werden sehr viele Spalten als „Pflicht“ festgelegt. Folgender Befehl wird deshalb zurückgewiesen:

 Fehler

INSERT INTO Mitarbeiter
       ( Personalnummer, Name, Vorname, Ist_Leiter, Abteilung_ID )
VALUES ( '17999', 'Liebich', 'Andrea', 'N', 17);

Ausgabe

validation error for column GEBURTSDATUM, value "*** null ***".

Die Spalte Geburtsdatum darf laut Definition nicht NULL sein. Eine Angabe fehlt in diesem Befehl: das wird als NULL interpretiert, also mit einer Fehlermeldung quittiert.

Mengen mit SELECT

Wenn eine Menge von Datensätzen mit Hilfe eines SELECT-Befehls gespeichert werden soll, gilt folgende Syntax:

 INSERT INTO <tabellenname>
         [ ( <spaltenliste> ) ]
     SELECT  <select-Ausdruck> 
    ;

Zu diesem Befehl gehören die folgenden Angaben:

• INSERT INTO <Tabellenname> (wie oben)
• in Klammern ( ) gesetzt eine Liste von Spalten (Feldnamen), sofern vorgesehen
• dazu ein vollständiger SELECT-Befehl, mit dem die passenden Inhalte geliefert werden

Da ein SELECT-Befehl auch ohne Bezug auf eine Tabelle nur mit konstanten Werten möglich ist, kann das obige Beispiel auch so formuliert werden:

INSERT INTO Mitarbeiter
       ( Personalnummer, Name, Vorname,
         Telefon, Email, Raum, Ist_Leiter, Abteilung_ID, Geburtsdatum )
  select '20002', 'Schmitz', 'Michael',
         '0231/5556187', 'michael.schmitz@unserefirma.de', '212', 'N', 2, '1959-08-25'
 /* from rdb%database (bei Firebird) */ ;
 /* from dual         (bei Oracle)   */ ;

Hinweis: Firebird und Oracle kennen diese Kurzform des SELECT-Befehls nicht; dort ist die als Kommentar jeweils eingefügte FROM-Klausel erforderlich.

Wichtig ist diese Art des INSERT-Befehls, wenn neue Datensätze aus vorhandenen anderen Daten abgeleitet werden wie im Skript der Beispieldatenbank:

Aufgabe

Für jeden Abteilungsleiter aus der Tabelle Mitarbeiter wird ein Eintrag in der Tabelle Dienstwagen gespeichert:

INSERT INTO Dienstwagen
          ( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
     SELECT 'DO-WB 42' || Abteilung_ID, 'elfenbein', 14, ID 
       FROM Mitarbeiter 
      WHERE Ist_Leiter = 'J';

Die Spalte ID wird automatisch zugewiesen. Alle anderen Spalten erhalten ausdrücklich Werte:

• Farbe und Fahrzeugtyp als Konstante
• dazu natürlich die ID des Mitarbeiters, dem der Dienstwagen zugeordnet wird
• und ein Kfz-Kennzeichen, das aus einem konstanten Teil mit der ID der Abteilung zusammengesetzt wird

Manche Datenbanken erlauben auch die Erstellung von Tabellen aus einem SELECT-Ausdruck wie bei MS-SQL (nächstes Beispiel) oder Teradata (anschließend):

 SELECT [ ( <spaltenliste> ) ]
    INTO <tabellenname>
 FROM <tabellenname>

  CREATE TABLE  <tabellennameA> AS
    SELECT [ ( <spaltenliste> ) ]
  FROM <tabellenname>

UPDATE – Daten ändern

Der UPDATE-Befehl dient zum Ändern einer Menge von Datensätzen in einer Tabelle:

 UPDATE <Tabellenname>
    SET <Feldänderungen>
 [WHERE <Bedingungsliste>];

Jede Änderung eines Feldes ist so einzutragen:

 <Feldname> = <Wert>,

Zu diesem Befehl gehören die folgenden Angaben:

• UPDATE als Name des Befehls
• <Tabellenname> als Name der Tabelle, in der die Daten zu ändern sind
• SET als Anfang der Liste von Änderungen
• <Feldname> als Name der Spalte, die einen neuen Inhalt erhalten soll, dazu das Gleichheitszeichen und der <Wert> als neuer Inhalt
• ein Komma als Hinweis, dass ein weiteres Feld zu ändern ist; vor der WHERE-Klausel oder dem abschließenden Semikolon muss das Komma entfallen
• die WHERE-Klausel mit Bedingungen, welche Datensätze zu ändern sind: einer oder eine bestimmte Menge

Die Struktur der WHERE-Klausel ist identisch mit derjenigen beim SELECT-Befehl. Wenn alle Datensätze geändert werden sollen, kann die WHERE-Bedingung entfallen; aber beachten Sie unbedingt:

Ohne WHERE-Bedingung wird wirklich alles sofort geändert.

An den Beispielen ist zu sehen, dass die Änderung aller Datensätze nur selten sinnvoll ist und meistens mit WHERE-Bedingung gearbeitet wird.

Wie beim INSERT-Befehl muss der Datentyp eines Wertes zum Datentyp der Spalte passen. Beispiele:

Aufgabe

Korrigiere die Schreibweise des Namens bei einem Mitarbeiter.

UPDATE Mitarbeiter
   SET Name = 'Mayer'
 WHERE Personalnummer = 20001;

Aufgabe

Ändere nach einer Eingemeindung PLZ und Ortsname für alle betroffenen Adressen.

UPDATE Versicherungsnehmer
   SET Ort = 'Leipzig',
       PLZ = '04178'
 WHERE PLZ = '04430';

Aufgabe

Erhöhe bei allen Schadensfällen die Schadenshöhe um 10 % (das ist natürlich keine sinnvolle Maßnahme):

UPDATE Schadensfall
   SET Schadenshoehe = Schadenshoehe * 1.1;

Aufgabe

Berichtige das Geburtsdatum für einen Versicherungsnehmer:

update Versicherungsnehmer
   set Geburtsdatum = '14.03.1963'
 where Name = 'Zenep' and Geburtsdatum = '13.02.1963';

Ausgabe

0 row(s) affected.

Nanu, keine Zeilen wurden geändert? Bei diesem Befehl wurde zur Kontrolle, welcher Datensatz geändert werden sollte, nicht nur der Nachname, sondern auch das bisher notierte Geburtsdatum angegeben – und dieses war falsch.

Daran ist zu sehen, dass der UPDATE-Befehl tatsächlich eine Menge von Datensätzen ändert: je nach WHERE-Klausel null, einen, mehrere oder alle Zeilen der Tabelle.

DELETE – Daten löschen

Der DELETE-Befehl löscht eine Menge von Datensätzen in einer Tabelle:

 DELETE FROM <Tabellenname>
     [ WHERE <Bedingungsliste> ] ;

Zu diesem Befehl gehören folgende Angaben:

• DELETE als Name des Befehls, FROM als feststehender Begriff
• <Tabellenname> als Name der Tabelle, aus der diese Datenmenge entfernt werden soll
• die WHERE-Klausel mit Bedingungen, welche Datensätze zu löschen sind: einer oder eine bestimmte Menge

Die Struktur der WHERE-Klausel ist identisch mit derjenigen beim SELECT-Befehl. Wenn alle Datensätze gelöscht werden sollen, kann die WHERE-Bedingung entfallen; aber beachten Sie unbedingt:

Ohne WHERE-Bedingung wird wirklich alles sofort gelöscht.

Beispiele:

Aufgabe

Der Mitarbeiter mit der Personalnummer 20001 ist ausgeschieden.

DELETE FROM Mitarbeiter
 WHERE Personalnummer = 20001;

Aufgabe

Die Abteilung 1 wurde ausgelagert, alle Mitarbeiter gehören nicht mehr zum Unternehmen.

DELETE FROM Mitarbeiter
 WHERE Abteilung_ID = 1;

Aufgabe

Dies leert den gesamten Inhalt der Tabelle, aber die Tabelle selbst bleibt mit ihrer Struktur erhalten.

DELETE FROM Schadensfall;

Achtung: Dies löscht ohne weitere Rückfrage alle Schadensfälle. Ein solcher Befehl sollte unbedingt nur nach einer vorherigen Datensicherung ausgeführt werden. Auch der Versuch ist „strafbar“ und führt zum sofortigen Datenverlust.

TRUNCATE – Tabelle leeren

Wenn Sie entgegen den oben genannten Hinweisen wirklich alle Datensätze einer Tabelle löschen wollen, können Sie (soweit vorhanden) anstelle von DELETE den TRUNCATE-Befehl benutzen. Damit werden (ohne Verbindung mit WHERE) immer alle Datensätze gelöscht; dies geschieht schneller und einfacher, weil auf das interne Änderungsprotokoll der Datenbank verzichtet wird. – Wegen solcher technischen Gründe könnte TRUNCATE auch als DDL-Befehl angesehen werden. Aus der Sicht des Anwenders gehört es zu DML, siehe auch die SQL-Dokumente 20nn. Weitere Gesichtspunkte stehen auf der Wikibooks-Diskussionsseite.

TRUNCATE TABLE Schadensfall;

Zusammenfassung

In diesem Kapitel lernten wir die SQL-Befehle kennen, mit denen der Datenbestand geändert wird:

• Mit INSERT + VALUES wird ein einzelner Datensatz eingefügt.
• Mit INSERT + SELECT wird eine Menge von Datensätzen mit Hilfe einer Abfrage eingefügt.
• Mit UPDATE wird eine Menge von Datensätzen geändert; die Menge wird durch WHERE festgelegt.
• Mit DELETE wird eine Menge von Datensätzen gelöscht; die Menge wird durch WHERE festgelegt.
• Mit TRUNCATE werden alle Datensätze einer Tabelle gelöscht.

Die WHERE-Bedingungen sind hier besonders wichtig, damit keine falschen Speicherungen erfolgen.

Übungen

Übung 1

Daten einzeln einfügen

Zur Lösung

Welche Angaben werden benötigt, wenn ein einzelner Datensatz in der Datenbank gespeichert werden soll?

Übung 2

Daten einzeln einfügen

Zur Lösung

Speichern Sie in der Tabelle Mitarbeiter einen neuen Datensatz und lassen Sie alle Spalten und Werte weg, die nicht benötigt werden.

Übung 3

Daten einfügen

Zur Lösung

Begründen Sie, warum der Spalte ID beim Einfügen in der Regel kein Wert zugewiesen wird.

Übung 4

Daten einfügen

Zur Lösung

Begründen Sie, warum die folgenden Befehle nicht ausgeführt werden können.

/* Mitarbeiter */
INSERT INTO Mitarbeiter
       ( ID, Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter, Abteilung_ID )
values ( 4, 'PD-348', 'Çiçek', 'Yasemin', '23.08.1984', 'J', 9 );

/* Dienstwagen 1 */
INSERT INTO Dienstwagen
       ( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
values ( 'DO-UF 1234', null, null, null );

/* Dienstwagen 2 */
INSERT INTO Dienstwagen
       ( Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID )
values ( 'HAM-AB 1234', 'rot', 7, null );

Übung 5

Daten ändern und löschen

Zur Lösung

Warum gibt es selten UPDATE- oder DELETE-Befehle ohne eine WHERE-Klausel?

Übung 6

Daten ändern

Zur Lösung

Schreiben Sie einen SQL-Befehl für folgende Änderung: Alle Mitarbeiter, die bisher noch keinen Mobil-Anschluss hatten, sollen unter einer einheitlichen Nummer erreichbar sein.

Lösungen

Lösung zu Übung 1

Daten einzeln einfügen

Zur Übung

• der INSERT-Befehl selbst
• INTO mit Angabe der Tabelle
• bei Bedarf in Klammern die Liste der Spalten, die mit Werten versehen werden
• VALUES zusammen mit (in Klammern) der Liste der zugeordneten Werte

Lösung zu Übung 2

Daten einzeln einfügen

Zur Übung

Beispielsweise so:

insert into Mitarbeiter
       ( Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter, Abteilung_ID )
values ( 'PD-348', 'Çiçek', 'Yasemin', '23.08.1984', 'J', 9 );

Lösung zu Übung 3

Daten einfügen

Zur Übung

Dieser soll von der Datenbank automatisch zugewiesen werden; er muss deshalb weggelassen oder mit NULL vorgegeben werden.

Lösung zu Übung 4

Daten einfügen

Zur Übung

• Mitarbeiter: Diese ID ist schon vergeben; sie muss aber eindeutig sein.
• Dienstwagen 1: Der Fahrzeugtyp ist eine Pflicht-Angabe; die Fahrzeugtyp_ID darf nicht null sein.
• Dienstwagen 2: Das Kennzeichen ist zu lang; es darf maximal eine Länge von 10 Zeichen haben.

Lösung zu Übung 5

Daten ändern und löschen

Zur Übung

Dies würde die gleiche Änderung bzw. die Löschung für alle Datensätze ausführen; das ist selten sinnvoll bzw. gewünscht.

Lösung zu Übung 6

Daten ändern

Zur Übung

update Mitarbeiter
   set Mobil = '(0177) 44 55 66 77'
 where Mobil is null or Mobil = '';